CMR_XCC_PPM_BURDEN

Details

Schema: FUSION
Object owner: CMR
Object type: VIEW

Columns

Name
SOURCE_HEADER_ID_1 SOURCE_HEADER_ID_2 SOURCE_LINE_ID_1 SOURCE_LINE_ID_2 SOURCE_LINE_ID_3 SOURCE_LINE_ID_4 SOURCE_LINE_ID_5 SOURCE_LINE_ID_6 TRANSACTION_TYPE_CODE RECEIPT_NUMBER PJC_PROJECT_ID PJC_TASK_ID PJC_RESOURCE_ID PJC_CONTRACT_ID PJC_CONTRACT_LINE_ID PJC_FUNDING_ALLOCATION_ID PJC_BILLABLE_FLAG PJC_CAPITALIZABLE_FLAG PJC_EXPENDITURE_TYPE_ID PJC_EXPENDITURE_ITEM_DATE PJC_ORGANIZATION_ID PJC_WORK_TYPE_ID PJC_RESERVED_ATTRIBUTE1 PJC_RESERVED_ATTRIBUTE2 PJC_RESERVED_ATTRIBUTE3 PJC_RESERVED_ATTRIBUTE4 PJC_RESERVED_ATTRIBUTE5 PJC_RESERVED_ATTRIBUTE6 PJC_RESERVED_ATTRIBUTE7 PJC_RESERVED_ATTRIBUTE8 PJC_RESERVED_ATTRIBUTE9 PJC_RESERVED_ATTRIBUTE10 PJC_CONTEXT_CATEGORY PJC_USER_DEF_ATTRIBUTE1 PJC_USER_DEF_ATTRIBUTE2 PJC_USER_DEF_ATTRIBUTE3 PJC_USER_DEF_ATTRIBUTE4 PJC_USER_DEF_ATTRIBUTE5 PJC_USER_DEF_ATTRIBUTE6 PJC_USER_DEF_ATTRIBUTE7 PJC_USER_DEF_ATTRIBUTE8 PJC_USER_DEF_ATTRIBUTE9 PJC_USER_DEF_ATTRIBUTE10 FUND_RESERVATION_STATUS LIQUIDATION_AMOUNT LIQUIDATION_BASE_AMOUNT RCV_TRANSACTION_ID

Name

SOURCE_HEADER_ID_1

SOURCE_HEADER_ID_2

SOURCE_LINE_ID_1

SOURCE_LINE_ID_2

SOURCE_LINE_ID_3

SOURCE_LINE_ID_4

SOURCE_LINE_ID_5

SOURCE_LINE_ID_6

TRANSACTION_TYPE_CODE

RECEIPT_NUMBER

PJC_PROJECT_ID

PJC_TASK_ID

PJC_RESOURCE_ID

PJC_CONTRACT_ID

PJC_CONTRACT_LINE_ID

PJC_FUNDING_ALLOCATION_ID

PJC_BILLABLE_FLAG

PJC_CAPITALIZABLE_FLAG

PJC_EXPENDITURE_TYPE_ID

PJC_EXPENDITURE_ITEM_DATE

PJC_ORGANIZATION_ID

PJC_WORK_TYPE_ID

PJC_RESERVED_ATTRIBUTE1

PJC_RESERVED_ATTRIBUTE2

PJC_RESERVED_ATTRIBUTE3

PJC_RESERVED_ATTRIBUTE4

PJC_RESERVED_ATTRIBUTE5

PJC_RESERVED_ATTRIBUTE6

PJC_RESERVED_ATTRIBUTE7

PJC_RESERVED_ATTRIBUTE8

PJC_RESERVED_ATTRIBUTE9

PJC_RESERVED_ATTRIBUTE10

PJC_CONTEXT_CATEGORY

PJC_USER_DEF_ATTRIBUTE1

PJC_USER_DEF_ATTRIBUTE2

PJC_USER_DEF_ATTRIBUTE3

PJC_USER_DEF_ATTRIBUTE4

PJC_USER_DEF_ATTRIBUTE5

PJC_USER_DEF_ATTRIBUTE6

PJC_USER_DEF_ATTRIBUTE7

PJC_USER_DEF_ATTRIBUTE8

PJC_USER_DEF_ATTRIBUTE9

PJC_USER_DEF_ATTRIBUTE10

FUND_RESERVATION_STATUS

LIQUIDATION_AMOUNT

LIQUIDATION_BASE_AMOUNT

RCV_TRANSACTION_ID

Query

SQL_Statement
SELECT TO_CHAR(CRE.ACCOUNTING_EVENT_ID) SOURCE_HEADER_ID_1, ' ' SOURCE_HEADER_ID_2, TO_CHAR(( CASE cre.EVENT_TYPE_CODE WHEN 'DELIVERY_TO_INVENTORY' THEN cre.ACCOUNTING_EVENT_ID WHEN 'CORRECTION_TO_DELIVERY_INV' THEN cre.ACCOUNTING_EVENT_ID WHEN 'RETURN_TO_RECEIVING_INV' THEN cre.ACCOUNTING_EVENT_ID ELSE crec.EVENT_COST_ID END )) SOURCE_LINE_ID_1, ' ' SOURCE_LINE_ID_2, ' ' SOURCE_LINE_ID_3, ' ' SOURCE_LINE_ID_4, ' ' SOURCE_LINE_ID_5, ' ' SOURCE_LINE_ID_6, cttb.xcc_transaction_type TRANSACTION_TYPE_CODE, crt.RECEIPT_NUMBER RECEIPT_NUMBER, CRT.PJC_PROJECT_ID, TO_NUMBER(CRT.PJC_TASK_ID) PJC_TASK_ID, TO_NUMBER(NULL) PJC_RESOURCE_ID, CRT.PJC_CONTRACT_ID, CRT.PJC_CONTRACT_LINE_ID, CRT.PJC_FUNDING_ALLOCATION_ID, NVL(CRT.PJC_BILLABLE_FLAG, 'N') PJC_BILLABLE_FLAG, NVL(CRT.PJC_CAPITALIZABLE_FLAG, 'N') PJC_CAPITALIZABLE_FLAG, CRT.PJC_EXPENDITURE_TYPE_ID, CRT.PJC_EXPENDITURE_ITEM_DATE, CRT.PJC_ORGANIZATION_ID, CRT.PJC_WORK_TYPE_ID, CRT.PJC_RESERVED_ATTRIBUTE1, CRT.PJC_RESERVED_ATTRIBUTE2, CRT.PJC_RESERVED_ATTRIBUTE3, CRT.PJC_RESERVED_ATTRIBUTE4, CRT.PJC_RESERVED_ATTRIBUTE5, CRT.PJC_RESERVED_ATTRIBUTE6, CRT.PJC_RESERVED_ATTRIBUTE7, CRT.PJC_RESERVED_ATTRIBUTE8, CRT.PJC_RESERVED_ATTRIBUTE9, CRT.PJC_RESERVED_ATTRIBUTE10, CRT.PJC_CONTEXT_CATEGORY, CRT.PJC_USER_DEF_ATTRIBUTE1, CRT.PJC_USER_DEF_ATTRIBUTE2, CRT.PJC_USER_DEF_ATTRIBUTE3, CRT.PJC_USER_DEF_ATTRIBUTE4, CRT.PJC_USER_DEF_ATTRIBUTE5, CRT.PJC_USER_DEF_ATTRIBUTE6, CRT.PJC_USER_DEF_ATTRIBUTE7, CRT.PJC_USER_DEF_ATTRIBUTE8, CRT.PJC_USER_DEF_ATTRIBUTE9, CRT.PJC_USER_DEF_ATTRIBUTE10, CRE.FUND_RESERVATION_STATUS, NVL(( CASE cre.EVENT_TYPE_CODE WHEN 'DELIVERY_TO_INVENTORY' THEN cre.exchange_rate WHEN 'CORRECTION_TO_DELIVERY_INV' THEN cre.exchange_rate WHEN 'RETURN_TO_RECEIVING_INV' THEN cre.exchange_rate ELSE crec.CURRENCY_CONVERSION_RATE END ),1) * (CASE WHEN cre.EVENT_CLASS_CODE = 'DELIVER_INV' THEN ENCUMBRANCE_REVERSAL_ACCT_AMT WHEN crec.EVENT_COST_SOURCE IN ('PO_PRICE','FD_PRICE') THEN ENCUMBRANCE_REVERSAL_ACCT_AMT ELSE 0 END ) LIQUIDATION_AMOUNT, CASE WHEN cre.EVENT_CLASS_CODE = 'DELIVER_INV' THEN cre.ENCUMBRANCE_REVERSAL_ENTR_AMT WHEN crec.EVENT_COST_SOURCE IN ('PO_PRICE','FD_PRICE') THEN cre.ENCUMBRANCE_REVERSAL_ENTR_AMT ELSE 0 END LIQUIDATION_BASE_AMOUNT, CRT.EXTERNAL_SYSTEM_REF_ID RCV_TRANSACTION_ID FROM CMR_RCV_EVENTS CRE, CMR_RCV_TRANSACTIONS CRT, CMR_RCV_EVENT_COSTS CREC, CMR_PURCHASE_ORDER_DTLS cpod, cmr_transaction_types_b cttb, cmr_rcv_event_types cret WHERE cre.ACCOUNTING_EVENT_ID =crec.ACCOUNTING_EVENT_ID(+) AND cre.CMR_PO_DISTRIBUTION_ID = crt.CMR_PO_DISTRIBUTION_ID AND cre.CMR_RCV_TRANSACTION_ID = crt.CMR_RCV_TRANSACTION_ID AND cre.CMR_PO_DISTRIBUTION_ID = cpod.CMR_PO_DISTRIBUTION_ID AND cpod.accrue_on_receipt_flag ='Y' AND cpod.active_flag ='Y' AND cret.event_type_code = cre.event_type_code AND cttb.transaction_type_code = cret.transaction_type AND cttb.xcc_transaction_type IS NOT NULL AND cttb.xcc_transaction_type <> 'RECEIPT_RETRO_PRICE' AND cre.BUDGETARY_CONTROL_FLAG='Y' UNION ALL SELECT TO_CHAR(CRE.ACCOUNTING_EVENT_ID) SOURCE_HEADER_ID_1, ' ' SOURCE_HEADER_ID_2, TO_CHAR(CREC.EVENT_COST_ID) SOURCE_LINE_ID_1, ' ' SOURCE_LINE_ID_2, ' ' SOURCE_LINE_ID_3, ' ' SOURCE_LINE_ID_4, ' ' SOURCE_LINE_ID_5, ' ' SOURCE_LINE_ID_6, cttb.xcc_transaction_type TRANSACTION_TYPE_CODE, crt.RECEIPT_NUMBER RECEIPT_NUMBER, CRT.PJC_PROJECT_ID, to_NUMBER(CRT.PJC_TASK_ID) PJC_TASK_ID, TO_NUMBER(NULL) PJC_RESOURCE_ID, CRT.PJC_CONTRACT_ID, CRT.PJC_CONTRACT_LINE_ID, CRT.PJC_FUNDING_ALLOCATION_ID, NVL(CRT.PJC_BILLABLE_FLAG, 'N') PJC_BILLABLE_FLAG, NVL(CRT.PJC_CAPITALIZABLE_FLAG, 'N') PJC_CAPITALIZABLE_FLAG, CRT.PJC_EXPENDITURE_TYPE_ID, CRT.PJC_EXPENDITURE_ITEM_DATE, CRT.PJC_ORGANIZATION_ID, CRT.PJC_WORK_TYPE_ID, CRT.PJC_RESERVED_ATTRIBUTE1, CRT.PJC_RESERVED_ATTRIBUTE2, CRT.PJC_RESERVED_ATTRIBUTE3, CRT.PJC_RESERVED_ATTRIBUTE4, CRT.PJC_RESERVED_ATTRIBUTE5, CRT.PJC_RESERVED_ATTRIBUTE6, CRT.PJC_RESERVED_ATTRIBUTE7, CRT.PJC_RESERVED_ATTRIBUTE8, CRT.PJC_RESERVED_ATTRIBUTE9, CRT.PJC_RESERVED_ATTRIBUTE10, CRT.PJC_CONTEXT_CATEGORY, CRT.PJC_USER_DEF_ATTRIBUTE1, CRT.PJC_USER_DEF_ATTRIBUTE2, CRT.PJC_USER_DEF_ATTRIBUTE3, CRT.PJC_USER_DEF_ATTRIBUTE4, CRT.PJC_USER_DEF_ATTRIBUTE5, CRT.PJC_USER_DEF_ATTRIBUTE6, CRT.PJC_USER_DEF_ATTRIBUTE7, CRT.PJC_USER_DEF_ATTRIBUTE8, CRT.PJC_USER_DEF_ATTRIBUTE9, CRT.PJC_USER_DEF_ATTRIBUTE10, CRE.FUND_RESERVATION_STATUS, NVL(CREC.CURRENCY_CONVERSION_RATE,1) * (CASE WHEN cre.EVENT_CLASS_CODE = 'DELIVER_INV' THEN ENCUMBRANCE_REVERSAL_ACCT_AMT ELSE CASE WHEN crec.EVENT_COST_SOURCE = 'PO_PRICE' THEN ENCUMBRANCE_REVERSAL_ACCT_AMT ELSE 0 END END ) LIQUIDATION_AMOUNT, CASE WHEN cre.EVENT_CLASS_CODE = 'DELIVER_INV' THEN cre.ENCUMBRANCE_REVERSAL_ENTR_AMT ELSE CASE WHEN crec.EVENT_COST_SOURCE = 'PO_PRICE' THEN cre.ENCUMBRANCE_REVERSAL_ENTR_AMT ELSE 0 END END LIQUIDATION_BASE_AMOUNT, CRT.EXTERNAL_SYSTEM_REF_ID RCV_TRANSACTION_ID FROM CMR_RCV_EVENTS CRE, CMR_RCV_TRANSACTIONS CRT, CMR_RCV_EVENT_COSTS CREC, cmr_purchase_order_dtls cpod, cmr_transaction_types_b cttb, cmr_rcv_event_types cret WHERE cre.ACCOUNTING_EVENT_ID =crec.ACCOUNTING_EVENT_ID AND cpod.cmr_po_distribution_id =crt.cmr_po_distribution_id AND cpod.po_line_location_id =crt.po_line_location_id AND crt.cmr_rcv_transaction_id = (SELECT MAX(crt1.cmr_rcv_transaction_id) FROM cmr_rcv_transactions crt1 WHERE crt1.cmr_po_distribution_id = crt.cmr_po_distribution_id AND crt1.po_line_location_id = crt.po_line_location_id AND crt1.transaction_type = 'DELIVER') AND cre.CMR_PO_DISTRIBUTION_ID = cpod.CMR_PO_DISTRIBUTION_ID AND cpod.accrue_on_receipt_flag ='Y' AND cpod.active_flag ='Y' AND cret.event_type_code = cre.event_type_code AND cttb.transaction_type_code = cret.transaction_type AND cttb.xcc_transaction_type = 'RECEIPT_RETRO_PRICE' AND cre.BUDGETARY_CONTROL_FLAG='Y' AND cre.XCC_DATA_SET_ID IS NOT NULL

SQL_Statement

SELECT TO_CHAR(CRE.ACCOUNTING_EVENT_ID) SOURCE_HEADER_ID_1,

' ' SOURCE_HEADER_ID_2,

TO_CHAR((

CASE cre.EVENT_TYPE_CODE

WHEN 'DELIVERY_TO_INVENTORY'

THEN cre.ACCOUNTING_EVENT_ID

WHEN 'CORRECTION_TO_DELIVERY_INV'

THEN cre.ACCOUNTING_EVENT_ID

WHEN 'RETURN_TO_RECEIVING_INV'

THEN cre.ACCOUNTING_EVENT_ID

ELSE crec.EVENT_COST_ID

END )) SOURCE_LINE_ID_1,

' ' SOURCE_LINE_ID_2,

' ' SOURCE_LINE_ID_3,

' ' SOURCE_LINE_ID_4,

' ' SOURCE_LINE_ID_5,

' ' SOURCE_LINE_ID_6,

cttb.xcc_transaction_type TRANSACTION_TYPE_CODE,

crt.RECEIPT_NUMBER RECEIPT_NUMBER,

CRT.PJC_PROJECT_ID,

TO_NUMBER(CRT.PJC_TASK_ID) PJC_TASK_ID,

TO_NUMBER(NULL) PJC_RESOURCE_ID,

CRT.PJC_CONTRACT_ID,

CRT.PJC_CONTRACT_LINE_ID,

CRT.PJC_FUNDING_ALLOCATION_ID,

NVL(CRT.PJC_BILLABLE_FLAG, 'N') PJC_BILLABLE_FLAG,

NVL(CRT.PJC_CAPITALIZABLE_FLAG, 'N') PJC_CAPITALIZABLE_FLAG,

CRT.PJC_EXPENDITURE_TYPE_ID,

CRT.PJC_EXPENDITURE_ITEM_DATE,

CRT.PJC_ORGANIZATION_ID,

CRT.PJC_WORK_TYPE_ID,

CRT.PJC_RESERVED_ATTRIBUTE1,

CRT.PJC_RESERVED_ATTRIBUTE2,

CRT.PJC_RESERVED_ATTRIBUTE3,

CRT.PJC_RESERVED_ATTRIBUTE4,

CRT.PJC_RESERVED_ATTRIBUTE5,

CRT.PJC_RESERVED_ATTRIBUTE6,

CRT.PJC_RESERVED_ATTRIBUTE7,

CRT.PJC_RESERVED_ATTRIBUTE8,

CRT.PJC_RESERVED_ATTRIBUTE9,

CRT.PJC_RESERVED_ATTRIBUTE10,

CRT.PJC_CONTEXT_CATEGORY,

CRT.PJC_USER_DEF_ATTRIBUTE1,

CRT.PJC_USER_DEF_ATTRIBUTE2,

CRT.PJC_USER_DEF_ATTRIBUTE3,

CRT.PJC_USER_DEF_ATTRIBUTE4,

CRT.PJC_USER_DEF_ATTRIBUTE5,

CRT.PJC_USER_DEF_ATTRIBUTE6,

CRT.PJC_USER_DEF_ATTRIBUTE7,

CRT.PJC_USER_DEF_ATTRIBUTE8,

CRT.PJC_USER_DEF_ATTRIBUTE9,

CRT.PJC_USER_DEF_ATTRIBUTE10,

CRE.FUND_RESERVATION_STATUS,

NVL((

CASE cre.EVENT_TYPE_CODE

WHEN 'DELIVERY_TO_INVENTORY'

THEN cre.exchange_rate

WHEN 'CORRECTION_TO_DELIVERY_INV'

THEN cre.exchange_rate

WHEN 'RETURN_TO_RECEIVING_INV'

THEN cre.exchange_rate

ELSE crec.CURRENCY_CONVERSION_RATE

END ),1) *

(CASE

WHEN cre.EVENT_CLASS_CODE = 'DELIVER_INV'

THEN ENCUMBRANCE_REVERSAL_ACCT_AMT

WHEN crec.EVENT_COST_SOURCE IN ('PO_PRICE','FD_PRICE')

THEN ENCUMBRANCE_REVERSAL_ACCT_AMT

ELSE 0

END

) LIQUIDATION_AMOUNT,

CASE WHEN cre.EVENT_CLASS_CODE = 'DELIVER_INV' THEN cre.ENCUMBRANCE_REVERSAL_ENTR_AMT

WHEN crec.EVENT_COST_SOURCE IN ('PO_PRICE','FD_PRICE') THEN cre.ENCUMBRANCE_REVERSAL_ENTR_AMT

ELSE 0

END LIQUIDATION_BASE_AMOUNT,

CRT.EXTERNAL_SYSTEM_REF_ID RCV_TRANSACTION_ID

FROM CMR_RCV_EVENTS CRE,

CMR_RCV_TRANSACTIONS CRT,

CMR_RCV_EVENT_COSTS CREC,

CMR_PURCHASE_ORDER_DTLS cpod,

cmr_transaction_types_b cttb,

cmr_rcv_event_types cret

WHERE cre.ACCOUNTING_EVENT_ID =crec.ACCOUNTING_EVENT_ID(+)

AND cre.CMR_PO_DISTRIBUTION_ID = crt.CMR_PO_DISTRIBUTION_ID

AND cre.CMR_RCV_TRANSACTION_ID = crt.CMR_RCV_TRANSACTION_ID

AND cre.CMR_PO_DISTRIBUTION_ID = cpod.CMR_PO_DISTRIBUTION_ID

AND cpod.accrue_on_receipt_flag ='Y'

AND cpod.active_flag ='Y'

AND cret.event_type_code = cre.event_type_code

AND cttb.transaction_type_code = cret.transaction_type

AND cttb.xcc_transaction_type IS NOT NULL

AND cttb.xcc_transaction_type <> 'RECEIPT_RETRO_PRICE'

AND cre.BUDGETARY_CONTROL_FLAG='Y'

UNION ALL

SELECT TO_CHAR(CRE.ACCOUNTING_EVENT_ID) SOURCE_HEADER_ID_1,

' ' SOURCE_HEADER_ID_2,

TO_CHAR(CREC.EVENT_COST_ID) SOURCE_LINE_ID_1,

' ' SOURCE_LINE_ID_2,

' ' SOURCE_LINE_ID_3,

' ' SOURCE_LINE_ID_4,

' ' SOURCE_LINE_ID_5,

' ' SOURCE_LINE_ID_6,

cttb.xcc_transaction_type TRANSACTION_TYPE_CODE,

crt.RECEIPT_NUMBER RECEIPT_NUMBER,

CRT.PJC_PROJECT_ID,

to_NUMBER(CRT.PJC_TASK_ID) PJC_TASK_ID,

TO_NUMBER(NULL) PJC_RESOURCE_ID,

CRT.PJC_CONTRACT_ID,

CRT.PJC_CONTRACT_LINE_ID,

CRT.PJC_FUNDING_ALLOCATION_ID,

NVL(CRT.PJC_BILLABLE_FLAG, 'N') PJC_BILLABLE_FLAG,

NVL(CRT.PJC_CAPITALIZABLE_FLAG, 'N') PJC_CAPITALIZABLE_FLAG,

CRT.PJC_EXPENDITURE_TYPE_ID,

CRT.PJC_EXPENDITURE_ITEM_DATE,

CRT.PJC_ORGANIZATION_ID,

CRT.PJC_WORK_TYPE_ID,

CRT.PJC_RESERVED_ATTRIBUTE1,

CRT.PJC_RESERVED_ATTRIBUTE2,

CRT.PJC_RESERVED_ATTRIBUTE3,

CRT.PJC_RESERVED_ATTRIBUTE4,

CRT.PJC_RESERVED_ATTRIBUTE5,

CRT.PJC_RESERVED_ATTRIBUTE6,

CRT.PJC_RESERVED_ATTRIBUTE7,

CRT.PJC_RESERVED_ATTRIBUTE8,

CRT.PJC_RESERVED_ATTRIBUTE9,

CRT.PJC_RESERVED_ATTRIBUTE10,

CRT.PJC_CONTEXT_CATEGORY,

CRT.PJC_USER_DEF_ATTRIBUTE1,

CRT.PJC_USER_DEF_ATTRIBUTE2,

CRT.PJC_USER_DEF_ATTRIBUTE3,

CRT.PJC_USER_DEF_ATTRIBUTE4,

CRT.PJC_USER_DEF_ATTRIBUTE5,

CRT.PJC_USER_DEF_ATTRIBUTE6,

CRT.PJC_USER_DEF_ATTRIBUTE7,

CRT.PJC_USER_DEF_ATTRIBUTE8,

CRT.PJC_USER_DEF_ATTRIBUTE9,

CRT.PJC_USER_DEF_ATTRIBUTE10,

CRE.FUND_RESERVATION_STATUS,

NVL(CREC.CURRENCY_CONVERSION_RATE,1) *

(CASE

WHEN cre.EVENT_CLASS_CODE = 'DELIVER_INV' THEN ENCUMBRANCE_REVERSAL_ACCT_AMT

ELSE

CASE WHEN crec.EVENT_COST_SOURCE = 'PO_PRICE' THEN ENCUMBRANCE_REVERSAL_ACCT_AMT

ELSE 0

END

) LIQUIDATION_AMOUNT,

CASE WHEN cre.EVENT_CLASS_CODE = 'DELIVER_INV'

THEN cre.ENCUMBRANCE_REVERSAL_ENTR_AMT

ELSE

CASE WHEN crec.EVENT_COST_SOURCE = 'PO_PRICE'

THEN cre.ENCUMBRANCE_REVERSAL_ENTR_AMT

ELSE 0

END

END LIQUIDATION_BASE_AMOUNT,

CRT.EXTERNAL_SYSTEM_REF_ID RCV_TRANSACTION_ID

FROM CMR_RCV_EVENTS CRE,

CMR_RCV_TRANSACTIONS CRT,

CMR_RCV_EVENT_COSTS CREC,

cmr_purchase_order_dtls cpod,

cmr_transaction_types_b cttb,

cmr_rcv_event_types cret

WHERE cre.ACCOUNTING_EVENT_ID =crec.ACCOUNTING_EVENT_ID

AND cpod.cmr_po_distribution_id =crt.cmr_po_distribution_id

AND cpod.po_line_location_id =crt.po_line_location_id

AND crt.cmr_rcv_transaction_id = (SELECT MAX(crt1.cmr_rcv_transaction_id) FROM cmr_rcv_transactions crt1 WHERE crt1.cmr_po_distribution_id = crt.cmr_po_distribution_id AND crt1.po_line_location_id = crt.po_line_location_id AND crt1.transaction_type = 'DELIVER')

AND cre.CMR_PO_DISTRIBUTION_ID = cpod.CMR_PO_DISTRIBUTION_ID

AND cpod.accrue_on_receipt_flag ='Y'

AND cpod.active_flag ='Y'

AND cret.event_type_code = cre.event_type_code

AND cttb.transaction_type_code = cret.transaction_type

AND cttb.xcc_transaction_type = 'RECEIPT_RETRO_PRICE'

AND cre.BUDGETARY_CONTROL_FLAG='Y'

AND cre.XCC_DATA_SET_ID IS NOT NULL