CML_PO_SCHED_RCNCILE_DTLS

Details

Schema: FUSION
Object owner: CML
Object type: VIEW

Columns

Name
CMR_PO_LINE_LOCATION_ID PO_NUMBER LINE_NUMBER SHIPMENT_NUMBER ORDERED_QTY TRACKED_QTY RECEIPT_QTY QTY_DIFF UOM_TYPE_CODE UOM_CODE SHED_AGE ALLOC_AMOUNT ABS_AMOUNT BAL_AMOUNT BAL_PERCENT CURRENCY_NAME CURRENCY_PRECISION RECONCILE_STATUS TROP_STATUS TROP_STATUS_CODE BU_NAME BU_ID PARTY_NAME VENDOR_SITE_ID VENDOR_SITE_CODE ORGANIZATION_NAME ORGANIZATION_ID CLOSE_STATUS_FLAG TRADE_OPERATION_ID TRADE_OPERATION_NUMBER TRADE_OPERATION_NAME RECONCILE_STATUS_CODE ITEM_NUMBER

Name

CMR_PO_LINE_LOCATION_ID

PO_NUMBER

LINE_NUMBER

SHIPMENT_NUMBER

ORDERED_QTY

TRACKED_QTY

RECEIPT_QTY

QTY_DIFF

UOM_TYPE_CODE

UOM_CODE

SHED_AGE

ALLOC_AMOUNT

ABS_AMOUNT

BAL_AMOUNT

BAL_PERCENT

CURRENCY_NAME

CURRENCY_PRECISION

RECONCILE_STATUS

TROP_STATUS

TROP_STATUS_CODE

BU_NAME

BU_ID

PARTY_NAME

VENDOR_SITE_ID

VENDOR_SITE_CODE

ORGANIZATION_NAME

ORGANIZATION_ID

CLOSE_STATUS_FLAG

TRADE_OPERATION_ID

TRADE_OPERATION_NUMBER

TRADE_OPERATION_NAME

RECONCILE_STATUS_CODE

ITEM_NUMBER

Query

SQL_Statement
with rcpt as (SELECT SUM(ct.source_doc_qty) RECEIPT_QTY, ct.cmr_po_line_location_id, ctos.trade_operation_id FROM cmr_transactions ct, cml_trade_operation_shipments ctos, cmr_rcv_transactions crt, cml_trade_operations cto 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 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, -1 as trade_operation_id FROM cmr_transactions ct WHERE transaction_type IN ( SELECT transaction_type_code FROM cmr_transaction_types_b WHERE transaction_group_name = 'RECEIVING' ) GROUP BY ct.cmr_po_line_location_id), crce AS (SELECT crcee.cmr_po_line_location_id, trop_charge_line_id , MAX(unit_amount) UNIT_AMOUNT, MAX(unit_tax) UNIT_TAX, currency_code FROM cml_rcv_charge_estimates crcee WHERE NVL(active_flag,'Y') ='Y' GROUP BY crcee.cmr_po_line_location_id, trop_charge_line_id, currency_code ), PO_LINE_LOC_STATUS AS (SELECT CMR_PO_LINE_LOCATION_ID,PO_NUMBER,LINE_NUMBER,SHIPMENT_NUMBER,MAX(EVENT_DATE) EVENT_DATE,VENDOR_ID,VENDOR_SITE_ID,DELIVER_TO_BUSINESS_UNIT_ID,DELIVER_TO_INVENTORY_ORG_ID,INVENTORY_ITEM_ID,DEFAULT_INVENTORY_ORG_ID, DECODE(SUM(DECODE(CPOD.event_type,'PO Close',0,1)),0,'Y','N') CLOSE_STATUS_FLAG, (SUM(secondary_quantity)/SUM(quantity_ordered)) conversion_factor FROM cmr_purchase_order_dtls cpod WHERE CPOD.ACTIVE_FLAG='Y' AND quantity_ordered > 0 GROUP BY CMR_PO_LINE_LOCATION_ID,PO_NUMBER,LINE_NUMBER,SHIPMENT_NUMBER,VENDOR_ID,VENDOR_SITE_ID,DELIVER_TO_BUSINESS_UNIT_ID,DELIVER_TO_INVENTORY_ORG_ID,INVENTORY_ITEM_ID,DEFAULT_INVENTORY_ORG_ID ), crca AS (SELECT crcaa.cmr_po_line_location_id, trop_charge_line_id , MAX(unit_amount) UNIT_AMOUNT, MAX(unit_tax) UNIT_TAX, currency_code FROM cml_rcv_charge_actuals crcaa WHERE NVL(active_flag,'Y') ='Y' GROUP BY crcaa.cmr_po_line_location_id, trop_charge_line_id, currency_code ), rec_status AS (SELECT ctcps.CMR_PO_LINE_LOCATION_ID, CASE WHEN MAX(NVL(CML_RECONCILIATION_STATUS,10))<>MIN(NVL(CML_RECONCILIATION_STATUS,10)) THEN CASE WHEN MAX(CML_RECONCILIATION_STATUS) =40 AND MIN(NVL(CML_RECONCILIATION_STATUS,10))=10 THEN 10 ELSE 25 END ELSE CASE WHEN MAX(CML_RECONCILIATION_STATUS) =20 AND MIN(CML_RECONCILIATION_STATUS) =20 AND (ctcps.tracked_qtyDECODE(ctcps.UOM_TYPE_CODE,'PRIMARY',1,'ASN',NVL(ctcps.po_uom_conv_factor,1),(1/PO_LINE_LOC_STATUS.conversion_factor)))-((rcpt.RECEIPT_QTY))<>0 THEN 25 ELSE MAX(NVL(CML_RECONCILIATION_STATUS,10)) END END reconcile_status, cto.trade_operation_id trade_operation_id FROM cml_trop_charge_po_schedules ctcps, crce, rcpt, PO_LINE_LOC_STATUS, cml_trade_operation_charges ctoc, cml_trade_operations cto WHERE rcpt.CMR_PO_LINE_LOCATION_ID=ctcps.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=PO_LINE_LOC_STATUS.CMR_PO_LINE_LOCATION_ID(+) AND ctcps.CMR_PO_LINE_LOCATION_ID =crce.CMR_PO_LINE_LOCATION_ID(+) AND ctcps.trade_operation_charge_id=crce.TROP_CHARGE_LINE_ID(+) AND ctoc.trade_operation_charge_id=ctcps.trade_operation_charge_id AND ctoc.trade_operation_id =cto.trade_operation_id AND ctoc.line_status_code IN ('ALLOC_ERROR','ALLOCATED','ALLOCATED_WARN') GROUP BY ctcps.CMR_PO_LINE_LOCATION_ID , ctcps.ordered_qty, ctcps.tracked_qty, rcpt.RECEIPT_QTY, ctcps.uom_type_code, ctcps.po_uom_conv_factor, ctcps.uom_code, PO_LINE_LOC_STATUS.CLOSE_STATUS_FLAG, PO_LINE_LOC_STATUS.conversion_factor, cto.trade_operation_id, CTO.TRADE_OPERATION_NUMBER, CTO.NAME, CTO.STATUS_CODE ) SELECT CMR_PO_LINE_LOCATION_ID, PO_NUMBER, LINE_NUMBER, SHIPMENT_NUMBER, ORDERED_QTY, TRACKED_QTY, RECEIPT_QTY, QTY_DIFF, UOM_TYPE_CODE, UOM_CODE , SHED_AGE, ALLOC_AMOUNT, ABS_AMOUNT, BAL_AMOUNT, (QTY_DIFF/TRACKED_QTY)100 BAL_PERCENT, CURRENCY_NAME, CURRENCY_PRECISION, fl.meaning RECONCILE_STATUS, fl2.meaning TROP_STATUS, temp_results.STATUS_CODE TROP_STATUS_CODE, BU_NAME, BU_ID, PARTY_NAME, VENDOR_SITE_ID, VENDOR_SITE_CODE, ORGANIZATION_NAME, ORGANIZATION_ID, CLOSE_STATUS_FLAG , TRADE_OPERATION_ID, TRADE_OPERATION_NUMBER, TRADE_OPERATION_NAME, RECONCILE_STATUS RECONCILE_STATUS_CODE, ITEM_NUMBER FROM (SELECT ctcps.CMR_PO_LINE_LOCATION_ID, PO_LINE_LOC_STATUS.po_number, PO_LINE_LOC_STATUS.line_number, PO_LINE_LOC_STATUS.SHIPMENT_NUMBER, ctcps.ordered_qty, (ctcps.tracked_qtyDECODE(ctcps.uom_type_code,'PRIMARY',1,'ASN',NVL(ctcps.po_uom_conv_factor,1),(1/PO_LINE_LOC_STATUS.conversion_factor))) TRACKED_QTY, rcpt.RECEIPT_QTY RECEIPT_QTY, ctcps.uom_type_code, ctcps.uom_code, (ctcps.tracked_qtyDECODE(ctcps.uom_type_code,'PRIMARY',1,'ASN',NVL(ctcps.po_uom_conv_factor,1),(1/PO_LINE_LOC_STATUS.conversion_factor))-(rcpt.RECEIPT_QTY)) qty_diff, ROUND(sysdate -TRUNC(PO_LINE_LOC_STATUS.event_date)) shed_age, (ctcps.tracked_qty/DECODE(ctcps.uom_type_code,'PRIMARY',1,'ASN',NVL(1/ctcps.po_uom_conv_factor,1),PO_LINE_LOC_STATUS.conversion_factor))SUM( CASE WHEN (crca.UNIT_AMOUNT IS NOT NULL OR crca.unit_tax IS NOT NULL) THEN (NVL(crca.UNIT_AMOUNT,0)+NVL(crca.unit_tax,0))NVL(ctoc.CHARGE_REQ_BU_CURR_CONV_RATE,0) ELSE (NVL(crce.UNIT_AMOUNT,0)+NVL(crce.unit_tax,0))NVL(ctoc.CHARGE_REQ_BU_CURR_CONV_RATE,0) END ) alloc_amount, (rcpt.RECEIPT_QTY)SUM( CASE WHEN (crca.UNIT_AMOUNT IS NOT NULL OR crca.unit_tax IS NOT NULL) THEN (NVL(crca.UNIT_AMOUNT,0)+NVL(crca.unit_tax,0))NVL(ctoc.CHARGE_REQ_BU_CURR_CONV_RATE,0) ELSE (NVL(crce.UNIT_AMOUNT,0)+NVL(crce.unit_tax,0))NVL(ctoc.CHARGE_REQ_BU_CURR_CONV_RATE,0) END ) abs_amount, ((ctcps.tracked_qty/DECODE(ctcps.UOM_TYPE_CODE,'PRIMARY',1,'ASN',NVL(1/ctcps.po_uom_conv_factor,1),PO_LINE_LOC_STATUS.conversion_factor))SUM( CASE WHEN (crca.UNIT_AMOUNT IS NOT NULL OR crca.unit_tax IS NOT NULL) THEN (NVL(crca.UNIT_AMOUNT,0)+NVL(crca.unit_tax,0)) ELSE NVL(crce.UNIT_AMOUNT,0) +NVL(crce.unit_tax,0) END )) -((rcpt.RECEIPT_QTY)SUM( CASE WHEN (crca.UNIT_AMOUNT IS NOT NULL OR crca.unit_tax IS NOT NULL) THEN (NVL(crca.UNIT_AMOUNT,0)+NVL(crca.unit_tax,0)) ELSE NVL(crce.UNIT_AMOUNT,0) +NVL(crce.unit_tax,0) END )) bal_amount, DECODE(MAX(curr1.CURRENCY_CODE),NULL,MAX(curr2.CURRENCY_CODE),MAX(curr1.CURRENCY_CODE))CURRENCY_NAME, DECODE(MAX(curr1.PRECISION),NULL,MAX(curr2.PRECISION),MAX(curr1.PRECISION))CURRENCY_PRECISION, rec_status.reconcile_status, BU.NAME BU_NAME, BU.ORGANIZATION_ID BU_ID, hp.PARTY_NAME, pssam.VENDOR_SITE_ID, pssam.VENDOR_SITE_CODE, INV_ORG.ORGANIZATION_NAME, INV_ORG.ORGANIZATION_ID, PO_LINE_LOC_STATUS.CLOSE_STATUS_FLAG, DECODE(ctcps.UOM_TYPE_CODE,'PRIMARY',1,'ASN',NVL(ctcps.po_uom_conv_factor,1),(1/PO_LINE_LOC_STATUS.conversion_factor)) conversion_factor, cto.trade_operation_id trade_operation_id, cto.TRADE_OPERATION_NUMBER TRADE_OPERATION_NUMBER, CTO.NAME TRADE_OPERATION_NAME, CTO.STATUS_CODE, ItemPEO.ITEM_NUMBER FROM cml_trop_charge_po_schedules ctcps, rec_status, crca, crce, rcpt, PO_LINE_LOC_STATUS, HZ_PARTIES hp, POZ_SUPPLIERS ps, POZ_SUPPLIER_SITES_ALL_M pssam, HR_ALL_ORGANIZATION_UNITS_F_VL BU, HR_ORG_UNIT_CLASSIFICATIONS_F CLS, RCS_ORG_DEFINITIONS_LW_V INV_ORG, FND_CURRENCIES_VL curr1, FND_CURRENCIES_VL curr2, cml_trade_operation_charges ctoc, cml_trade_operations cto, EGP_SYSTEM_ITEMS_B ItemPEO WHERE rcpt.CMR_PO_LINE_LOCATION_ID =ctcps.CMR_PO_LINE_LOCATION_ID AND rcpt.trade_operation_id = DECODE(cto.ALLOCATION_OBJECT,'SHIP_QTY',cto.TRADE_OPERATION_ID,-1) AND PO_LINE_LOC_STATUS.CMR_PO_LINE_LOCATION_ID =ctcps.CMR_PO_LINE_LOCATION_ID AND rec_status.CMR_PO_LINE_LOCATION_ID =ctcps.CMR_PO_LINE_LOCATION_ID AND rec_status.trade_operation_id = cto.trade_operation_id AND ctcps.CMR_PO_LINE_LOCATION_ID =crca.CMR_PO_LINE_LOCATION_ID(+) AND ctcps.trade_operation_charge_id =crca.TROP_CHARGE_LINE_ID(+) AND crca.CURRENCY_CODE =curr1.CURRENCY_CODE(+) AND ctcps.CMR_PO_LINE_LOCATION_ID =crce.CMR_PO_LINE_LOCATION_ID(+) AND ctcps.trade_operation_charge_id =crce.TROP_CHARGE_LINE_ID(+) AND crce.CURRENCY_CODE =curr2.CURRENCY_CODE(+) AND (ctcps.ordered_qtyDECODE(ctcps.uom_type_code,'PRIMARY',1,'ASN',1,(1/PO_LINE_LOC_STATUS.conversion_factor)) <> rcpt.RECEIPT_QTY OR (ctcps.ordered_qtyDECODE(ctcps.uom_type_code,'PRIMARY',1,'ASN',1,(1/PO_LINE_LOC_STATUS.conversion_factor)) =(rcpt.RECEIPT_QTY) AND RECONCILE_STATUS IN(25,40))) AND (TRACKED_QTY <> rcpt.RECEIPT_QTY or (TRACKED_QTY = rcpt.RECEIPT_QTY and RECONCILE_STATUS IN (20,25,40))) AND PO_LINE_LOC_STATUS.VENDOR_ID = ps.VENDOR_ID AND PO_LINE_LOC_STATUS.VENDOR_SITE_ID = pssam.VENDOR_SITE_ID AND ps.PARTY_ID = hp.PARTY_ID AND PO_LINE_LOC_STATUS.DELIVER_TO_BUSINESS_UNIT_ID=BU.ORGANIZATION_ID AND PO_LINE_LOC_STATUS.DELIVER_TO_INVENTORY_ORG_ID=INV_ORG.ORGANIZATION_ID AND ctoc.trade_operation_charge_id =ctcps.trade_operation_charge_id AND ctoc.trade_operation_id =cto.trade_operation_id AND ctoc.line_status_code IN ('ALLOC_ERROR','ALLOCATED','ALLOCATED_WARN') AND crce.cmr_po_line_location_id =rcpt.cmr_po_line_location_id AND trunc(SYSDATE) BETWEEN bu.effective_start_date AND bu.effective_end_date AND cls.organization_id = bu.organization_id AND cls.classification_code = 'FUN_BUSINESS_UNIT' AND PO_LINE_LOC_STATUS.INVENTORY_ITEM_ID = ItemPEO.INVENTORY_ITEM_ID(+) AND PO_LINE_LOC_STATUS.DEFAULT_INVENTORY_ORG_ID = ItemPEO.ORGANIZATION_ID(+) GROUP BY ctcps.CMR_PO_LINE_LOCATION_ID, ctcps.ordered_qty, ctcps.tracked_qty, rcpt.RECEIPT_QTY, ctcps.uom_type_code, ctcps.uom_code, ctcps.po_uom_conv_factor, PO_LINE_LOC_STATUS.event_date, rec_status.reconcile_status, PO_LINE_LOC_STATUS.po_number, PO_LINE_LOC_STATUS.line_number, PO_LINE_LOC_STATUS.SHIPMENT_NUMBER, BU.NAME, BU.ORGANIZATION_ID, pssam.VENDOR_SITE_ID, pssam.VENDOR_SITE_CODE, hp.PARTY_NAME, INV_ORG.ORGANIZATION_NAME, INV_ORG.ORGANIZATION_ID, PO_LINE_LOC_STATUS.CLOSE_STATUS_FLAG, PO_LINE_LOC_STATUS.conversion_factor, cto.trade_operation_id, CTO.TRADE_OPERATION_NUMBER, CTO.NAME, CTO.STATUS_CODE, ItemPEO.ITEM_NUMBER ) temp_results, fnd_lookups fl, fnd_lookups fl2 WHERE fl.lookup_type='ORA_CML_RECONCILIATION_STATUS' AND fl.lookup_code =temp_results.RECONCILE_STATUS AND fl2.lookup_type ='ORA_CML_TROP_STATUS' AND fl2.lookup_code =temp_results.STATUS_CODE AND RECEIPT_QTY > 0

SQL_Statement

with rcpt as

(SELECT

SUM(ct.source_doc_qty) RECEIPT_QTY,

ct.cmr_po_line_location_id,

ctos.trade_operation_id

FROM

cmr_transactions ct,

cml_trade_operation_shipments ctos,

cmr_rcv_transactions crt,

cml_trade_operations cto

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

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,

-1 as trade_operation_id

FROM

cmr_transactions ct

WHERE

transaction_type IN (

SELECT

transaction_type_code

FROM

cmr_transaction_types_b

WHERE

transaction_group_name = 'RECEIVING'

)

GROUP BY

ct.cmr_po_line_location_id),

crce AS

(SELECT crcee.cmr_po_line_location_id,

trop_charge_line_id ,

MAX(unit_amount) UNIT_AMOUNT,

MAX(unit_tax) UNIT_TAX,

currency_code

FROM cml_rcv_charge_estimates crcee

WHERE NVL(active_flag,'Y') ='Y'

GROUP BY crcee.cmr_po_line_location_id,

trop_charge_line_id,

currency_code

),

PO_LINE_LOC_STATUS AS

(SELECT CMR_PO_LINE_LOCATION_ID,PO_NUMBER,LINE_NUMBER,SHIPMENT_NUMBER,MAX(EVENT_DATE) EVENT_DATE,VENDOR_ID,VENDOR_SITE_ID,DELIVER_TO_BUSINESS_UNIT_ID,DELIVER_TO_INVENTORY_ORG_ID,INVENTORY_ITEM_ID,DEFAULT_INVENTORY_ORG_ID,

DECODE(SUM(DECODE(CPOD.event_type,'PO Close',0,1)),0,'Y','N') CLOSE_STATUS_FLAG,

(SUM(secondary_quantity)/SUM(quantity_ordered)) conversion_factor

FROM cmr_purchase_order_dtls cpod

WHERE CPOD.ACTIVE_FLAG='Y'

AND quantity_ordered > 0

GROUP BY CMR_PO_LINE_LOCATION_ID,PO_NUMBER,LINE_NUMBER,SHIPMENT_NUMBER,VENDOR_ID,VENDOR_SITE_ID,DELIVER_TO_BUSINESS_UNIT_ID,DELIVER_TO_INVENTORY_ORG_ID,INVENTORY_ITEM_ID,DEFAULT_INVENTORY_ORG_ID

),

crca AS

(SELECT crcaa.cmr_po_line_location_id,

trop_charge_line_id ,

MAX(unit_amount) UNIT_AMOUNT,

MAX(unit_tax) UNIT_TAX,

currency_code

FROM cml_rcv_charge_actuals crcaa

WHERE NVL(active_flag,'Y') ='Y'

GROUP BY crcaa.cmr_po_line_location_id,

trop_charge_line_id,

currency_code

),

rec_status AS

(SELECT ctcps.CMR_PO_LINE_LOCATION_ID,

CASE

WHEN MAX(NVL(CML_RECONCILIATION_STATUS,10))<>MIN(NVL(CML_RECONCILIATION_STATUS,10))

THEN

CASE

WHEN MAX(CML_RECONCILIATION_STATUS) =40

AND MIN(NVL(CML_RECONCILIATION_STATUS,10))=10

THEN 10

ELSE 25

END

ELSE

CASE

WHEN MAX(CML_RECONCILIATION_STATUS) =20

AND MIN(CML_RECONCILIATION_STATUS) =20

AND (ctcps.tracked_qty*DECODE(ctcps.UOM_TYPE_CODE,'PRIMARY',1,'ASN',NVL(ctcps.po_uom_conv_factor,1),(1/PO_LINE_LOC_STATUS.conversion_factor)))-((rcpt.RECEIPT_QTY))<>0

THEN 25

ELSE MAX(NVL(CML_RECONCILIATION_STATUS,10))

END

END reconcile_status,

cto.trade_operation_id trade_operation_id

FROM cml_trop_charge_po_schedules ctcps,

crce,

rcpt,

PO_LINE_LOC_STATUS,

cml_trade_operation_charges ctoc,

cml_trade_operations cto

WHERE rcpt.CMR_PO_LINE_LOCATION_ID=ctcps.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=PO_LINE_LOC_STATUS.CMR_PO_LINE_LOCATION_ID(+)

AND ctcps.CMR_PO_LINE_LOCATION_ID =crce.CMR_PO_LINE_LOCATION_ID(+)

AND ctcps.trade_operation_charge_id=crce.TROP_CHARGE_LINE_ID(+)

AND ctoc.trade_operation_charge_id=ctcps.trade_operation_charge_id

AND ctoc.trade_operation_id =cto.trade_operation_id

AND ctoc.line_status_code IN ('ALLOC_ERROR','ALLOCATED','ALLOCATED_WARN')

GROUP BY ctcps.CMR_PO_LINE_LOCATION_ID ,

ctcps.ordered_qty,

ctcps.tracked_qty,

rcpt.RECEIPT_QTY,

ctcps.uom_type_code,

ctcps.po_uom_conv_factor,

ctcps.uom_code,

PO_LINE_LOC_STATUS.CLOSE_STATUS_FLAG,

PO_LINE_LOC_STATUS.conversion_factor,

cto.trade_operation_id,

CTO.TRADE_OPERATION_NUMBER,

CTO.NAME,

CTO.STATUS_CODE

)

SELECT CMR_PO_LINE_LOCATION_ID,

PO_NUMBER,

LINE_NUMBER,

SHIPMENT_NUMBER,

ORDERED_QTY,

TRACKED_QTY,

RECEIPT_QTY,

QTY_DIFF,

UOM_TYPE_CODE,

UOM_CODE ,

SHED_AGE,

ALLOC_AMOUNT,

ABS_AMOUNT,

BAL_AMOUNT,

(QTY_DIFF/TRACKED_QTY)*100 BAL_PERCENT,

CURRENCY_NAME,

CURRENCY_PRECISION,

fl.meaning RECONCILE_STATUS,

fl2.meaning TROP_STATUS,

temp_results.STATUS_CODE TROP_STATUS_CODE,

BU_NAME,

BU_ID,

PARTY_NAME,

VENDOR_SITE_ID,

VENDOR_SITE_CODE,

ORGANIZATION_NAME,

ORGANIZATION_ID,

CLOSE_STATUS_FLAG ,

TRADE_OPERATION_ID,

TRADE_OPERATION_NUMBER,

TRADE_OPERATION_NAME,

RECONCILE_STATUS RECONCILE_STATUS_CODE,

ITEM_NUMBER

FROM

(SELECT ctcps.CMR_PO_LINE_LOCATION_ID,

PO_LINE_LOC_STATUS.po_number,

PO_LINE_LOC_STATUS.line_number,

PO_LINE_LOC_STATUS.SHIPMENT_NUMBER,

ctcps.ordered_qty,

(ctcps.tracked_qty*DECODE(ctcps.uom_type_code,'PRIMARY',1,'ASN',NVL(ctcps.po_uom_conv_factor,1),(1/PO_LINE_LOC_STATUS.conversion_factor))) TRACKED_QTY,

rcpt.RECEIPT_QTY RECEIPT_QTY,

ctcps.uom_type_code,

ctcps.uom_code,

(ctcps.tracked_qty*DECODE(ctcps.uom_type_code,'PRIMARY',1,'ASN',NVL(ctcps.po_uom_conv_factor,1),(1/PO_LINE_LOC_STATUS.conversion_factor))-(rcpt.RECEIPT_QTY)) qty_diff,

ROUND(sysdate -TRUNC(PO_LINE_LOC_STATUS.event_date)) shed_age,

(ctcps.tracked_qty/DECODE(ctcps.uom_type_code,'PRIMARY',1,'ASN',NVL(1/ctcps.po_uom_conv_factor,1),PO_LINE_LOC_STATUS.conversion_factor))*SUM(

CASE

WHEN (crca.UNIT_AMOUNT IS NOT NULL

OR crca.unit_tax IS NOT NULL)

THEN (NVL(crca.UNIT_AMOUNT,0)+NVL(crca.unit_tax,0))*NVL(ctoc.CHARGE_REQ_BU_CURR_CONV_RATE,0)

ELSE (NVL(crce.UNIT_AMOUNT,0)+NVL(crce.unit_tax,0))*NVL(ctoc.CHARGE_REQ_BU_CURR_CONV_RATE,0)

END ) alloc_amount,

(rcpt.RECEIPT_QTY)*SUM(

CASE

WHEN (crca.UNIT_AMOUNT IS NOT NULL

OR crca.unit_tax IS NOT NULL)

THEN (NVL(crca.UNIT_AMOUNT,0)+NVL(crca.unit_tax,0))*NVL(ctoc.CHARGE_REQ_BU_CURR_CONV_RATE,0)

ELSE (NVL(crce.UNIT_AMOUNT,0)+NVL(crce.unit_tax,0))*NVL(ctoc.CHARGE_REQ_BU_CURR_CONV_RATE,0)

END ) abs_amount,

((ctcps.tracked_qty/DECODE(ctcps.UOM_TYPE_CODE,'PRIMARY',1,'ASN',NVL(1/ctcps.po_uom_conv_factor,1),PO_LINE_LOC_STATUS.conversion_factor))*SUM(

CASE

WHEN (crca.UNIT_AMOUNT IS NOT NULL

OR crca.unit_tax IS NOT NULL)

THEN (NVL(crca.UNIT_AMOUNT,0)+NVL(crca.unit_tax,0))

ELSE NVL(crce.UNIT_AMOUNT,0) +NVL(crce.unit_tax,0)

END )) -((rcpt.RECEIPT_QTY)*SUM(

CASE

WHEN (crca.UNIT_AMOUNT IS NOT NULL

OR crca.unit_tax IS NOT NULL)

THEN (NVL(crca.UNIT_AMOUNT,0)+NVL(crca.unit_tax,0))

ELSE NVL(crce.UNIT_AMOUNT,0) +NVL(crce.unit_tax,0)

END )) bal_amount,

DECODE(MAX(curr1.CURRENCY_CODE),NULL,MAX(curr2.CURRENCY_CODE),MAX(curr1.CURRENCY_CODE))CURRENCY_NAME,

DECODE(MAX(curr1.PRECISION),NULL,MAX(curr2.PRECISION),MAX(curr1.PRECISION))CURRENCY_PRECISION,

rec_status.reconcile_status,

BU.NAME BU_NAME,

BU.ORGANIZATION_ID BU_ID,

hp.PARTY_NAME,

pssam.VENDOR_SITE_ID,

pssam.VENDOR_SITE_CODE,

INV_ORG.ORGANIZATION_NAME,

INV_ORG.ORGANIZATION_ID,

PO_LINE_LOC_STATUS.CLOSE_STATUS_FLAG,

DECODE(ctcps.UOM_TYPE_CODE,'PRIMARY',1,'ASN',NVL(ctcps.po_uom_conv_factor,1),(1/PO_LINE_LOC_STATUS.conversion_factor)) conversion_factor,

cto.trade_operation_id trade_operation_id,

cto.TRADE_OPERATION_NUMBER TRADE_OPERATION_NUMBER,

CTO.NAME TRADE_OPERATION_NAME,

CTO.STATUS_CODE,

ItemPEO.ITEM_NUMBER

FROM cml_trop_charge_po_schedules ctcps,

rec_status,

crca,

crce,

rcpt,

PO_LINE_LOC_STATUS,

HZ_PARTIES hp,

POZ_SUPPLIERS ps,

POZ_SUPPLIER_SITES_ALL_M pssam,

HR_ALL_ORGANIZATION_UNITS_F_VL BU,

HR_ORG_UNIT_CLASSIFICATIONS_F CLS,

RCS_ORG_DEFINITIONS_LW_V INV_ORG,

FND_CURRENCIES_VL curr1,

FND_CURRENCIES_VL curr2,

cml_trade_operation_charges ctoc,

cml_trade_operations cto,

EGP_SYSTEM_ITEMS_B ItemPEO

WHERE rcpt.CMR_PO_LINE_LOCATION_ID =ctcps.CMR_PO_LINE_LOCATION_ID

AND rcpt.trade_operation_id = DECODE(cto.ALLOCATION_OBJECT,'SHIP_QTY',cto.TRADE_OPERATION_ID,-1)

AND PO_LINE_LOC_STATUS.CMR_PO_LINE_LOCATION_ID =ctcps.CMR_PO_LINE_LOCATION_ID

AND rec_status.CMR_PO_LINE_LOCATION_ID =ctcps.CMR_PO_LINE_LOCATION_ID

AND rec_status.trade_operation_id = cto.trade_operation_id

AND ctcps.CMR_PO_LINE_LOCATION_ID =crca.CMR_PO_LINE_LOCATION_ID(+)

AND ctcps.trade_operation_charge_id =crca.TROP_CHARGE_LINE_ID(+)

AND crca.CURRENCY_CODE =curr1.CURRENCY_CODE(+)

AND ctcps.CMR_PO_LINE_LOCATION_ID =crce.CMR_PO_LINE_LOCATION_ID(+)

AND ctcps.trade_operation_charge_id =crce.TROP_CHARGE_LINE_ID(+)

AND crce.CURRENCY_CODE =curr2.CURRENCY_CODE(+)

AND (ctcps.ordered_qty*DECODE(ctcps.uom_type_code,'PRIMARY',1,'ASN',1,(1/PO_LINE_LOC_STATUS.conversion_factor)) <> rcpt.RECEIPT_QTY OR (ctcps.ordered_qty*DECODE(ctcps.uom_type_code,'PRIMARY',1,'ASN',1,(1/PO_LINE_LOC_STATUS.conversion_factor)) =(rcpt.RECEIPT_QTY) AND RECONCILE_STATUS IN(25,40)))

AND (TRACKED_QTY <> rcpt.RECEIPT_QTY or (TRACKED_QTY = rcpt.RECEIPT_QTY and RECONCILE_STATUS IN (20,25,40)))

AND PO_LINE_LOC_STATUS.VENDOR_ID = ps.VENDOR_ID

AND PO_LINE_LOC_STATUS.VENDOR_SITE_ID = pssam.VENDOR_SITE_ID

AND ps.PARTY_ID = hp.PARTY_ID

AND PO_LINE_LOC_STATUS.DELIVER_TO_BUSINESS_UNIT_ID=BU.ORGANIZATION_ID

AND PO_LINE_LOC_STATUS.DELIVER_TO_INVENTORY_ORG_ID=INV_ORG.ORGANIZATION_ID

AND ctoc.trade_operation_charge_id =ctcps.trade_operation_charge_id

AND ctoc.trade_operation_id =cto.trade_operation_id

AND ctoc.line_status_code IN ('ALLOC_ERROR','ALLOCATED','ALLOCATED_WARN')

AND crce.cmr_po_line_location_id =rcpt.cmr_po_line_location_id

AND trunc(SYSDATE) BETWEEN bu.effective_start_date AND bu.effective_end_date

AND cls.organization_id = bu.organization_id

AND cls.classification_code = 'FUN_BUSINESS_UNIT'

AND PO_LINE_LOC_STATUS.INVENTORY_ITEM_ID = ItemPEO.INVENTORY_ITEM_ID(+)

AND PO_LINE_LOC_STATUS.DEFAULT_INVENTORY_ORG_ID = ItemPEO.ORGANIZATION_ID(+)

GROUP BY ctcps.CMR_PO_LINE_LOCATION_ID,

ctcps.ordered_qty,

ctcps.tracked_qty,

rcpt.RECEIPT_QTY,

ctcps.uom_type_code,

ctcps.uom_code,

ctcps.po_uom_conv_factor,

PO_LINE_LOC_STATUS.event_date,

rec_status.reconcile_status,

PO_LINE_LOC_STATUS.po_number,

PO_LINE_LOC_STATUS.line_number,

PO_LINE_LOC_STATUS.SHIPMENT_NUMBER,

BU.NAME,

BU.ORGANIZATION_ID,

pssam.VENDOR_SITE_ID,

pssam.VENDOR_SITE_CODE,

hp.PARTY_NAME,

INV_ORG.ORGANIZATION_NAME,

INV_ORG.ORGANIZATION_ID,

PO_LINE_LOC_STATUS.CLOSE_STATUS_FLAG,

PO_LINE_LOC_STATUS.conversion_factor,

cto.trade_operation_id,

CTO.TRADE_OPERATION_NUMBER,

CTO.NAME,

CTO.STATUS_CODE,

ItemPEO.ITEM_NUMBER

) temp_results,

fnd_lookups fl,

fnd_lookups fl2

WHERE fl.lookup_type='ORA_CML_RECONCILIATION_STATUS'

AND fl.lookup_code =temp_results.RECONCILE_STATUS

AND fl2.lookup_type ='ORA_CML_TROP_STATUS'

AND fl2.lookup_code =temp_results.STATUS_CODE

AND RECEIPT_QTY > 0