CML_POS_INVOICE_ALLOCATION_V

Details

• Schema: FUSION

• Object owner: CML

• Object type: VIEW

Columns

Name

TRADE_OPERATION_ID TRADE_OPERATION_CHARGE_ID CHARGE_LINE_NUMBER LINE_STATUS_CODE VENDOR_ID VENDOR_SITE_ID INVOICE_ID INVOICE_LINE_NUMBER CMR_PO_LINE_LOCATION_ID TROP_CHARGE_PO_SCHEDULE_ID ALLOCATION_PERCENTAGE TRACKED_QTY UOM_TYPE_CODE UOM_CODE PO_QTY_IN_PRIMARY_UOM PO_PRIMARY_UOM_CODE PO_QTY_IN_SECONDARY_UOM PO_SECONDARY_UOM_CODE PO_PRICE_IN_FUNC_CURRENCY MAT_REQ_BU_CURRENCY_CODE ACT_MAT_BU_CURR_CONV_RATE PER_UNIT_AMT_IN_INV_CURRENCY PER_UNIT_TAX_INV_INV_CURRENCY INVOICE_CURRENCY_CODE PER_UNIT_AMT_IN_FUNC_CURRENCY PER_UNIT_TAX_IN_FUNC_CURRENCY PER_UNIT_AMT_IN_MAT_BU_CURR PER_UNIT_TAX_IN_MAT_BU_CURR FUNC_CURRENCY_CODE SOURCE_DOC_UOM_CONV_FACTOR

Query

SQL_Statement

select ctoc.trade_operation_id ,ctoc.trade_operation_charge_id ,ctoc.charge_line_number ,ctoc.line_status_code ,ctoc.vendor_id ,ctoc.vendor_site_id ,ccia.invoice_id ,ccia.invoice_line_number ,ctcps.cmr_po_line_location_id ,ctcps.trop_charge_po_schedule_id ,ctcps.allocation_percentage ,ctcps.tracked_qty ,ctcps.uom_type_code ,ctcps.uom_code ,ctcps.po_qty_in_primary_uom ,ctcps.po_primary_uom_code ,ctcps.po_qty_in_secondary_uom ,ctcps.po_secondary_uom_code ,ctcps.po_price_in_func_currency ,ctcps.mat_req_bu_currency_code ,ctcps.act_mat_bu_curr_conv_rate ,ccia.invoice_amount*ctcps.allocation_percentage*.01/decode(ctcps.uom_type_code,'ASN',ctcps.tracked_qty*NVL(ctcps.po_uom_conv_factor,1),ctcps.tracked_qty) per_unit_amt_in_inv_currency ,ccia.invoice_tax*ctcps.allocation_percentage*.01/decode(ctcps.uom_type_code,'ASN',ctcps.tracked_qty*NVL(ctcps.po_uom_conv_factor,1),ctcps.tracked_qty) per_unit_tax_inv_inv_currency ,ccia.invoice_currency_code ,ccia.func_currency_amount*ctcps.allocation_percentage*.01/decode(ctcps.uom_type_code,'ASN',ctcps.tracked_qty*NVL(ctcps.po_uom_conv_factor,1),ctcps.tracked_qty) per_unit_amt_in_func_currency ,ccia.func_currency_tax*ctcps.allocation_percentage*.01/decode(ctcps.uom_type_code,'ASN',ctcps.tracked_qty*NVL(ctcps.po_uom_conv_factor,1),ctcps.tracked_qty) per_unit_tax_in_func_currency ,ccia.func_currency_amount*ctcps.allocation_percentage*.01*ctcps.act_mat_bu_curr_conv_rate/decode(ctcps.uom_type_code,'ASN',ctcps.tracked_qty*NVL(ctcps.po_uom_conv_factor,1),ctcps.tracked_qty) per_unit_amt_in_mat_bu_curr ,ccia.func_currency_tax*ctcps.allocation_percentage*.01*ctcps.act_mat_bu_curr_conv_rate/decode(ctcps.uom_type_code,'ASN',ctcps.tracked_qty*NVL(ctcps.po_uom_conv_factor,1),ctcps.tracked_qty) per_unit_tax_in_mat_bu_curr ,ccia.func_currency_code ,case when ctcps.uom_code = ctcps.po_primary_uom_code then 1 when ctcps.uom_code = ctcps.po_secondary_uom_code and ctcps.po_qty_in_primary_uom <> 0 then ctcps.po_qty_in_secondary_uom/ctcps.po_qty_in_primary_uom else -1 end source_doc_uom_conv_factor from cml_trade_operation_charges ctoc ,cml_charge_invoice_assocs ccia ,cml_trop_charge_po_schedules ctcps where ctoc.line_status_code in ('ALLOCATED', 'ALLOCATED_WARN', 'CANCELED','PEND_ASSOC') and ctoc.trade_operation_charge_id = ccia.trade_operation_charge_id and ccia.association_status in ('10','60') and ccia.active_flag = 'Y' and ctcps.trade_operation_charge_id = ctoc.trade_operation_charge_id