CML_TOS_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 SHIPMENT_LINE_ID TROP_CHARGE_TO_SHIP_LINE_ID TO_ORGANIZATION_ID FROM_ORGANIZATION_ID ITEM_ID ALLOCATION_PERCENTAGE TRACKED_QTY UOM_CODE SHIPPED_QTY_IN_TO_UOM TO_UOM_CODE SHIPPED_QTY_IN_PRIMARY_UOM PRIMARY_UOM_CODE SHIPPED_QTY_IN_SECONDARY_UOM SECONDARY_UOM_CODE ESTIMATED_TRANSFER_PRICE CURRENCY_CODE MAT_REQ_BU_CURRENCY_CODE ACT_MAT_BU_CURR_CONV_RATE PER_UNIT_AMT_IN_INV_CURRENCY PER_UNIT_TAX_IN_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 ,ctctsl.shipment_line_id ,ctctsl.trop_charge_to_ship_line_id ,ctctsl.to_organization_id ,ctctsl.from_organization_id ,ctctsl.item_id ,ctctsl.allocation_percentage ,ctctsl.tracked_qty ,ctctsl.uom_code ,ctctsl.SHIPPED_QTY_IN_TO_UOM ,ctctsl.TO_UOM_CODE ,ctctsl.SHIPPED_QTY_IN_PRIMARY_UOM ,ctctsl.primary_uom_code ,ctctsl.SHIPPED_QTY_IN_SECONDARY_UOM ,ctctsl.secondary_uom_code ,ctctsl.ESTIMATED_TRANSFER_PRICE ,ctctsl.CURRENCY_CODE ,ctctsl.mat_req_bu_currency_code ,ctctsl.act_mat_bu_curr_conv_rate ,ccia.invoice_amount*ctctsl.allocation_percentage*.01/ctctsl.tracked_qty per_unit_amt_in_inv_currency ,ccia.invoice_tax*ctctsl.allocation_percentage*.01/ctctsl.tracked_qty per_unit_tax_in_inv_currency ,ccia.invoice_currency_code ,ccia.func_currency_amount*ctctsl.allocation_percentage*.01/ctctsl.tracked_qty per_unit_amt_in_func_currency ,ccia.func_currency_tax*ctctsl.allocation_percentage*.01/ctctsl.tracked_qty per_unit_tax_in_func_currency ,ccia.func_currency_amount*ctctsl.allocation_percentage*.01*ctctsl.act_mat_bu_curr_conv_rate/ctctsl.tracked_qty per_unit_amt_in_mat_bu_curr ,ccia.func_currency_tax*ctctsl.allocation_percentage*.01*ctctsl.act_mat_bu_curr_conv_rate/ctctsl.tracked_qty per_unit_tax_in_mat_bu_curr ,ccia.func_currency_code ,case when ctctsl.uom_code = ctctsl.to_uom_code then 1 when ctctsl.uom_code = ctctsl.primary_uom_code and ctctsl.SHIPPED_QTY_IN_TO_UOM <> 0 then ctctsl.SHIPPED_QTY_IN_PRIMARY_UOM/ctctsl.SHIPPED_QTY_IN_TO_UOM when ctctsl.uom_code = ctctsl.secondary_uom_code and ctctsl.SHIPPED_QTY_IN_TO_UOM <> 0 then ctctsl.SHIPPED_QTY_IN_SECONDARY_UOM/ctctsl.SHIPPED_QTY_IN_TO_UOM else -1 end source_doc_uom_conv_factor from cml_trade_operation_charges ctoc ,cml_charge_invoice_assocs ccia ,cml_trop_charge_to_ship_lines ctctsl 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 ctctsl.trade_operation_charge_id = ctoc.trade_operation_charge_id