1. Tables and Views for SCM
  2. CML_TROP_CHARGE_PO_SCHED_V

CML_TROP_CHARGE_PO_SCHED_V

Details

  • Schema: FUSION

  • Object owner: CML

  • Object type: VIEW

Columns

Name

CMR_PO_LINE_LOCATION_ID

TRADE_OPERATION_CHARGE_ID

TRADE_OPERATION_NUMBER

TRADE_OPERATION_ID

CHARGE_LINE_NUMBER

STATUS_CODE

STATUS

CHARGE_ID

INVOICE_NUMBER

INVOICE_LINE_NUMBER

INVOICE_CREATION_DATE

TOTAL_AMOUNT

CURRENCY_CODE

CURRENCY_PRECISION

RECONCILIATION_STATUS

RECONCILIATION_STATUS_CODE

ABS_AMOUNT

BAL_AMOUNT

RUN_ID

Query

SQL_Statement

select CMR_PO_LINE_LOCATION_ID,trade_operation_charge_id,trade_operation_number,trade_operation_id,charge_line_number,fl2.meaning STATUS_CODE,status_code status, charge_id,

invoice_number,invoice_line_number,INVOICE_CREATION_DATE,total_amount,currency_code,currency_precision,fl.meaning RECONCILIATION_STATUS, temp.RECONCILIATION_STATUS RECONCILIATION_STATUS_CODE,abs_amount,(total_amount)-(abs_amount) bal_amount,

temp.run_id

from

(select ctcps.CMR_PO_LINE_LOCATION_ID, ctoc.trade_operation_charge_id,cto.trade_operation_number,cto.trade_operation_id,ctoc.charge_line_number, cto.STATUS_CODE, cml_charges_tl.name charge_id,

temp_inv.invoice_number, temp_inv.invoice_line_number, temp_inv.INVOICE_CREATION_DATE,

decode(temp_inv.total_amount,null,nvl(sched_amts.entered_amount, 0)*nvl(ctoc.CHARGE_REQ_BU_CURR_CONV_RATE,0)

+nvl(sched_amts.NONRECOVERABLE_TAX,0)*ctcps.tracked_qty*nvl(ctoc.CHARGE_REQ_BU_CURR_CONV_RATE,0),temp_inv.total_amount) total_amount,

decode(curr1.currency_code,null,curr2.currency_code,curr1.currency_code) currency_code,

online_id.run_id run_id,

decode(curr1.precision,null,curr2.precision,curr1.precision) currency_precision

,nvl(ctcps.CML_RECONCILIATION_STATUS,10) RECONCILIATION_STATUS,(sched_amts.tracked_quantity*(decode(sched_amts.uom_type_code,'PRIMARY',1,'ASN',NVL(ctcps.po_uom_conv_factor,1),(1/rcpt.conv_factor)))-(rcpt.RECEIPT_QTY))/(sched_amts.tracked_quantity*(decode(sched_amts.uom_type_code,'PRIMARY',1,'ASN',NVL(ctcps.po_uom_conv_factor,1),(1/rcpt.conv_factor)))) bal_ratio,

(sched_amts.amt_per_unit)*(decode(sched_amts.uom_type_code,'PRIMARY',1,'ASN',NVL(1/ctcps.po_uom_conv_factor,1),rcpt.conv_factor))*(rcpt.RECEIPT_QTY) +nvl(sched_amts.NONRECOVERABLE_TAX,0)*decode(sched_amts.uom_type_code,'PRIMARY',1,'ASN',nvl(1/ctcps.po_uom_conv_factor,1),rcpt.conv_factor)*(rcpt.RECEIPT_QTY) as abs_amount

from cml_trop_charge_po_schedules ctcps,cml_trade_operation_charges ctoc, cml_trade_operations cto,

(

SELECT

SUM(ct.source_doc_qty) receipt_qty,

ct.cmr_po_line_location_id,

SUM(cpod.secondary_quantity)/SUM(cpod.quantity_ordered) conv_factor,

ctos.trade_operation_id

FROM

cmr_transactions ct,

cml_trade_operation_shipments ctos,

cmr_rcv_transactions crt,

cml_trade_operations cto,

cmr_purchase_order_dtls cpod

WHERE

ct.transaction_type IN (

SELECT

transaction_type_code

FROM

cmr_transaction_types_b

WHERE

transaction_group_name = 'RECEIVING'

)

AND ctos.trade_operation_id = cto.trade_operation_id

AND ctos.shipment_header_id = crt.shipment_header_id

AND ct.cmr_rcv_transaction_id = crt.cmr_rcv_transaction_id

AND cpod.cmr_po_distribution_id = ct.cmr_po_distribution_id

AND cpod.active_flag = 'Y'

GROUP BY

ct.cmr_po_line_location_id,

ctos.trade_operation_id

UNION ALL

SELECT

SUM(ct.source_doc_qty) receipt_qty,

ct.cmr_po_line_location_id,

SUM(cpod.secondary_quantity)/SUM(cpod.quantity_ordered) conv_factor,

- 1 AS trade_operation_id

FROM

cmr_transactions ct,

cmr_purchase_order_dtls cpod

WHERE

transaction_type IN (

SELECT

transaction_type_code

FROM

cmr_transaction_types_b

WHERE

transaction_group_name = 'RECEIVING'

)

AND cpod.cmr_po_distribution_id = ct.cmr_po_distribution_id

AND cpod.active_flag = 'Y'

GROUP BY

ct.cmr_po_line_location_id,

- 1

) rcpt,

cml_charges_b,

cml_charges_tl,

(select ctcps.uom_type_code,cpce.amount_per_unit amt_per_unit, ctcps.tracked_qty tracked_quantity,ctcps.trade_operation_charge_id trop_charge_id,ctoc.trade_operation_id,ctcps.cmr_po_line_location_id,ctcps.tracked_qty,ctcps.tracked_qty*cpce.amount_per_unit entered_amount,ctcps.charge_req_bu_curr_conv_rate,cpce.tax_per_unit NONRECOVERABLE_TAX

from cml_trop_charge_po_schedules ctcps,cml_posched_charge_estimates cpce,cml_trade_operation_charges ctoc

where cpce.trop_charge_po_schedule_id(+)=ctcps.trop_charge_po_schedule_id and ctcps.trade_operation_charge_id=ctoc.trade_operation_charge_id and cpce.active_flag(+)='Y' and ctoc.line_status_code in ('ALLOC_ERROR','ALLOCATED')) sched_amts,

(select ccia.trade_operation_charge_id,cildv.invoice_number, cildv.invoice_line_number, cildv.INVOICE_CREATION_DATE,

nvl(cildv.FUNCTIONAL_INVOICE_AMOUNT,0)+nvl(cildv.FUNCTIONAL_TAX_AMOUNT,0) total_amount,

cildv.FUNCTIONAL_CURRENCY_CODE FUNCTIONAL_CURRENCY_CODE from cml_charge_invoice_assocs ccia, cml_invoice_line_details_v cildv

where ccia.active_flag='Y'

and ccia.invoice_id=cildv.invoice_id

and ccia.invoice_line_number=cildv.invoice_line_number AND ccia.association_status NOT IN (50,60,80)

) temp_inv,FND_CURRENCIES_VL curr1,FND_CURRENCIES_VL curr2,

( select max(run_id) run_id,trade_operation_charge_id

from cml_online_allocation_reqs group by trade_operation_charge_id ) online_id

where

rcpt.CMR_PO_LINE_LOCATION_ID=ctcps.CMR_PO_LINE_LOCATION_ID

and rcpt.CMR_PO_LINE_LOCATION_ID=sched_amts.CMR_PO_LINE_LOCATION_ID

and rcpt.trade_operation_id = DECODE(cto.allocation_object,'SHIP_QTY',cto.trade_operation_id,-1)

and ctcps.CMR_PO_LINE_LOCATION_ID=sched_amts.CMR_PO_LINE_LOCATION_ID

and ctcps.trade_operation_charge_id=ctoc.trade_operation_charge_id

and ctoc.trade_operation_id=cto.trade_operation_id

and ctoc.trade_operation_charge_id=temp_inv.trade_operation_charge_id(+)

and ctoc.TRADE_OPERATION_CHARGE_ID=sched_amts.trop_charge_id

and ctoc.charge_id=cml_charges_b.charge_id(+)

and temp_inv.FUNCTIONAL_CURRENCY_CODE=curr1.currency_code(+)

and ctoc.REQ_BU_CURRENCY_CODE=curr2.currency_code(+)

and cml_charges_b.charge_id = cml_charges_tl.charge_id and cml_charges_tl.language = USERENV('LANG')

and online_id.trade_operation_charge_id(+)=ctoc.trade_operation_charge_id) temp, fnd_lookups fl, fnd_lookups fl2

where fl.lookup_type='ORA_CML_RECONCILIATION_STATUS' and

fl.lookup_code=temp.RECONCILIATION_STATUS

and fl2.lookup_type='ORA_CML_TROP_STATUS' and

fl2.lookup_code=temp.STATUS_CODE