CMR_XCC_EXTRACT_V

Details

• Schema: FUSION

• Object owner: CMR

• Object type: VIEW

Columns

Name

DATA_SET_ID SOURCE_HEADER_ID_1 SOURCE_HEADER_ID_2 TRANSACTION_NUMBER RECEIPT_NUMBER SOURCE_LINE_ID_1 SOURCE_LINE_ID_2 SOURCE_LINE_ID_3 SOURCE_LINE_ID_4 SOURCE_LINE_ID_5 SOURCE_LINE_ID_6 LINE_GROUP_CODE LINE_NUM LEDGER_ID BUSINESS_UNIT_ID DESTINATION_TYPE_CODE DOCUMENT_TYPE_CODE TRANSACTION_SOURCE_CODE TRANSACTION_SUBTYPE_CODE JE_SOURCE_CODE JE_CATEGORY_CODE BUDGET_DATE ACCOUNTING_DATE ENCUMBRANCE_TYPE_CODE ENTERED_CURRENCY UOM_CODE QUANTITY PRICE ENTERED_AMOUNT CONVERSION_TYPE_CODE CONVERSION_DATE LEDGER_AMOUNT LIQUIDATION_DATE LIQUIDATION_QUANTITY LIQUIDATION_AMOUNT LIQUIDATION_TRANS_TYPE_CODE LIQUIDATION_LINE_ID_1 LIQUIDATION_LINE_ID_2 LIQUIDATION_LINE_ID_3 LIQUIDATION_LINE_ID_4 LIQUIDATION_LINE_ID_5 LIQUIDATION_LINE_ID_6 STATISTICAL_AMOUNT BUDGET_CCID VENDOR_ID INVENTORY_ITEM_ID ORDER_TYPE_INFO CODE_COMBINATION_ID PJC_PROJECT_ID PJC_TASK_ID PJC_RESOURCE_ID PJC_CONTRACT_ID PJC_FUNDING_SOURCE 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 CONTROL_BUDGET_ID FUNDING_SOURCE_ID LIQUIDATION_AMOUNT_LEDGER_CUR PARENT_REVERSAL_ID BILL_TO_BUSINESS_UNIT_ID

Query

SQL_Statement

SELECT cre.XCC_DATA_SET_ID DATA_SET_ID, TO_CHAR(cre.ACCOUNTING_EVENT_ID) SOURCE_HEADER_ID_1, ' ' SOURCE_HEADER_ID_2, DECODE(cre.EVENT_TYPE_CODE, 'RETRO_PRICE_ADJUSTMENT', cpod.po_number, crt.RECEIPT_NUMBER) TRANSACTION_NUMBER, crt.RECEIPT_NUMBER RECEIPT_NUMBER, TO_CHAR(( CASE WHEN cre.EVENT_TYPE_CODE IN ('DELIVERY_TO_INVENTORY','RETURN_TO_RECEIVING_INV','CORRECTION_TO_DELIVERY_INV','DELIVERY_TO_WORK_ORDER','CORRECTION_TO_DELIVERY_WO','RETURN_TO_RECEIVING_WO') 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, NULL LINE_GROUP_CODE, TO_NUMBER(NULL) LINE_NUM, cre.LEDGER_ID LEDGER_ID, cpod.requisitioning_bu_id BUSINESS_UNIT_ID, /* cre.BUSINESS_UNIT_ID BUSINESS_UNIT_ID, Req BU not PCBU bug#26042360 */ ( CASE WHEN cre.EVENT_CLASS_CODE = 'DELIVER_INV' THEN NVL(cre.DESTINATION_TYPE_CODE,'INVENTORY') ELSE crec.DESTINATION_TYPE_CODE END ) DESTINATION_TYPE_CODE, NULL DOCUMENT_TYPE_CODE, NULL TRANSACTION_SOURCE_CODE, cttb.xcc_transaction_type TRANSACTION_SUBTYPE_CODE, NULL JE_SOURCE_CODE, NULL JE_CATEGORY_CODE, NVL(cre.root_deliver_txn_date,cre.transaction_date) BUDGET_DATE, /*Bug 30999568 column required required to pass the budget date*/ cre.GL_DATE ACCOUNTING_DATE, NULL ENCUMBRANCE_TYPE_CODE, ( CASE cpod.DESTINATION_TYPE_CODE WHEN 'INVENTORY' THEN cre.entered_currency_code ELSE cpod.CURRENCY_CODE END ) ENTERED_CURRENCY, to_char(cpod.uom_code) UOM_CODE, cre.SOURCE_DOC_QTY QUANTITY, ( CASE WHEN cre.EVENT_CLASS_CODE = 'DELIVER_INV' THEN cre.po_unit_price ELSE cpod.price END ) PRICE, (CASE WHEN cre.EVENT_CLASS_CODE = 'DELIVER_INV' THEN NVL(cre.po_unit_price,0) + nvl(cre.nonrecoverable_tax,0) ELSE /* Pre Foreign code with foreign currency PO */ CASE WHEN (crec.currency_code <> cpod.currency_code) and (crec.currency_code = cpod.DELIVER_TO_BU_FUNC_CURR_CODE) THEN NVL(crec.EVENT_UNIT_COST,0)/DECODE(cre.exchange_rate,0,1,NVL(cre.exchange_rate,1)) ELSE NVL(crec.EVENT_UNIT_COST,0) /* Post Foreign code with foreign currency PO covers all cases */ END END) * NVL(cre.SOURCE_DOC_QTY,1) ENTERED_AMOUNT, NULL CONVERSION_TYPE_CODE, TO_DATE(NULL) CONVERSION_DATE, (CASE WHEN cre.EVENT_CLASS_CODE = 'DELIVER_INV' THEN NVL(cre.exchange_rate,1) * ( NVL(cre.po_unit_price,0) + nvl(cre.nonrecoverable_tax,0) ) ELSE NVL(crec.EVENT_UNIT_COST,0) * NVL(crec.CURRENCY_CONVERSION_RATE,1) /* CREC rate is NULL in pre-foreign code */ END) * NVL(cre.SOURCE_DOC_QTY,1) LEDGER_AMOUNT, TO_DATE(NULL) LIQUIDATION_DATE, TO_NUMBER(NULL) LIQUIDATION_QUANTITY, CASE when pxdv.PO_LINE_ID IS NULL THEN NULL ELSE CASE cre.EVENT_CLASS_CODE WHEN 'DELIVER_INV' THEN cre.ENCUMBRANCE_REVERSAL_ENTR_AMT ELSE CASE crec.EVENT_COST_SOURCE WHEN 'PO_PRICE' THEN cre.ENCUMBRANCE_REVERSAL_ENTR_AMT WHEN 'FD_PRICE' THEN cre.ENCUMBRANCE_REVERSAL_ENTR_AMT ELSE 0 END END END LIQUIDATION_AMOUNT, 'PURCHASE_ORDER' LIQUIDATION_TRANS_TYPE_CODE, TO_CHAR(pxdv.PO_LINE_ID) LIQUIDATION_LINE_ID_1, TO_CHAR(pxdv.PO_LINE_LOCATION_ID) LIQUIDATION_LINE_ID_2, TO_CHAR(pxdv.po_distribution_id ) LIQUIDATION_LINE_ID_3, TO_CHAR(pxdv.PO_VERSION_ID) LIQUIDATION_LINE_ID_4, ' ' LIQUIDATION_LINE_ID_5, ' ' LIQUIDATION_LINE_ID_6, TO_NUMBER(NULL) STATISTICAL_AMOUNT, TO_NUMBER(NULL) BUDGET_CCID, cpod.VENDOR_ID, cpod.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, NULL ORDER_TYPE_INFO, cpod.charge_account_id CODE_COMBINATION_ID, 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_RESERVED_ATTRIBUTE1 PJC_FUNDING_SOURCE , 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, TO_NUMBER(NULL) CONTROL_BUDGET_ID, crt.PJC_RESERVED_ATTRIBUTE1 FUNDING_SOURCE_ID, CASE when pxdv.PO_LINE_ID IS NULL THEN NULL ELSE CASE cre.EVENT_CLASS_CODE WHEN 'DELIVER_INV' THEN cre.ENCUMBRANCE_REVERSAL_ACCT_AMT ELSE CASE crec.EVENT_COST_SOURCE WHEN 'PO_PRICE' THEN cre.ENCUMBRANCE_REVERSAL_ACCT_AMT WHEN 'FD_PRICE' THEN cre.ENCUMBRANCE_REVERSAL_ENTR_AMT ELSE 0 END END END LIQUIDATION_AMOUNT_LEDGER_CUR, crt.parent_transaction_id PARENT_REVERSAL_ID, cre.bill_to_business_unit_id BILL_TO_BUSINESS_UNIT_ID FROM CMR_RCV_EVENTS cre, CMR_RCV_EVENT_COSTS crec, CMR_RCV_TRANSACTIONS crt, CMR_PURCHASE_ORDER_DTLS cpod , PO_XCC_DISTRIBUTIONS_V pxdv, 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.po_header_id =pxdv.po_header_id(+) AND cpod.po_line_id =pxdv.po_line_id(+) AND cpod.po_line_location_id = PXDV.PO_LINE_LOCATION_ID(+) AND cpod.EXTERNAL_SYSTEM_REF_ID = PXDV.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' AND CRE.FUND_RESERVATION_STATUS IN ('NOT_RESERVED_FAILED','NOT_ATTEMPTED') UNION ALL SELECT cre.XCC_DATA_SET_ID DATA_SET_ID, TO_CHAR(cre.ACCOUNTING_EVENT_ID) SOURCE_HEADER_ID_1, ' ' SOURCE_HEADER_ID_2, cpod.PO_NUMBER TRANSACTION_NUMBER, crt.RECEIPT_NUMBER RECEIPT_NUMBER, 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, NULL LINE_GROUP_CODE, TO_NUMBER(NULL) LINE_NUM, cre.LEDGER_ID LEDGER_ID, cpod.requisitioning_bu_id BUSINESS_UNIT_ID, /* cre.BUSINESS_UNIT_ID BUSINESS_UNIT_ID, Req BU not PCBU bug#26042360 */ crec.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE, NULL DOCUMENT_TYPE_CODE, NULL TRANSACTION_SOURCE_CODE, cttb.xcc_transaction_type TRANSACTION_SUBTYPE_CODE, NULL JE_SOURCE_CODE, NULL JE_CATEGORY_CODE, NVL(pxdv.budget_date ,cre.transaction_date) BUDGET_DATE, /*Bug 31352176 - required to pass the budget date*/ cre.GL_DATE ACCOUNTING_DATE, NULL ENCUMBRANCE_TYPE_CODE, cpod.CURRENCY_CODE ENTERED_CURRENCY, to_char(cpod.uom_code) UOM_CODE, cre.UNINV_DEL_QTY QUANTITY, (nvl(crec.EVENT_UNIT_COST,0) - nvl(crec.EVENT_PRIOR_UNIT_COST,0)) PRICE, ((nvl(crec.EVENT_UNIT_COST,0) - nvl(crec.EVENT_PRIOR_UNIT_COST,0))) * NVL(cre.UNINV_DEL_QTY,0) ENTERED_AMOUNT, /* One case where UnInvoice del qty IS NULL means no delier txn and retro came bug#22253211 */ NULL CONVERSION_TYPE_CODE, TO_DATE(NULL) CONVERSION_DATE, NVL(crec.CURRENCY_CONVERSION_RATE,1) * (nvl(crec.EVENT_UNIT_COST,0) - nvl(crec.EVENT_PRIOR_UNIT_COST,0)) * NVL(cre.UNINV_DEL_QTY,0) LEDGER_AMOUNT, /* One case where UnInvoice del qty IS NULL means no delier txn and retro came bug#22253211 */ TO_DATE(NULL) LIQUIDATION_DATE, TO_NUMBER(NULL) LIQUIDATION_QUANTITY, CASE when pxdv.PO_LINE_ID IS NULL then NULL ELSE CASE crec.EVENT_COST_SOURCE WHEN 'PO_PRICE' THEN NVL(cre.ENCUMBRANCE_REVERSAL_ENTR_AMT ,0) /* if NULL passing zero is best for retro bug#22253211 */ ELSE 0 END END LIQUIDATION_AMOUNT, 'PURCHASE_ORDER' LIQUIDATION_TRANS_TYPE_CODE, TO_CHAR(PXDV.PO_LINE_ID) LIQUIDATION_LINE_ID_1, TO_CHAR(PXDV. PO_LINE_LOCATION_ID) LIQUIDATION_LINE_ID_2, TO_CHAR(PXDV.PO_distribution_ID) LIQUIDATION_LINE_ID_3, TO_CHAR(pxdv.PO_VERSION_ID) LIQUIDATION_LINE_ID_4, ' ' LIQUIDATION_LINE_ID_5, ' ' LIQUIDATION_LINE_ID_6, TO_NUMBER(NULL) STATISTICAL_AMOUNT, TO_NUMBER(NULL) BUDGET_CCID, cpod.VENDOR_ID, cpod.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, NULL ORDER_TYPE_INFO, cpod.charge_account_id CODE_COMBINATION_ID, 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_RESERVED_ATTRIBUTE1 PJC_FUNDING_SOURCE , 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, TO_NUMBER(NULL) CONTROL_BUDGET_ID, crt.PJC_RESERVED_ATTRIBUTE1 FUNDING_SOURCE_ID, case when pxdv.PO_LINE_ID IS NULL then NULL ELSE CASE crec.EVENT_COST_SOURCE WHEN 'PO_PRICE' THEN CRE.ENCUMBRANCE_REVERSAL_ACCT_AMT ELSE 0 END END LIQUIDATION_AMOUNT_LEDGER_CUR, -1 PARENT_REVERSAL_ID, cre.bill_to_business_unit_id BILL_TO_BUSINESS_UNIT_ID FROM CMR_RCV_EVENTS cre, CMR_RCV_EVENT_COSTS crec, CMR_RCV_TRANSACTIONS crt, CMR_PURCHASE_ORDER_DTLS cpod, PO_XCC_DISTRIBUTIONS_V pxdv, 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 /* bug perf - 36384616 */ 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') /* bug 36387112 */ AND cret.event_type_code = cre.event_type_code AND cttb.transaction_type_code = cret.transaction_type AND cre.CMR_PO_DISTRIBUTION_ID = cpod.CMR_PO_DISTRIBUTION_ID AND cpod.po_header_id =pxdv.po_header_id(+) AND cpod.po_line_id =pxdv.po_line_id(+) AND cpod.po_line_location_id = PXDV.PO_LINE_LOCATION_ID(+) AND cpod.EXTERNAL_SYSTEM_REF_ID = PXDV.PO_distribution_ID(+) AND cpod.accrue_on_receipt_flag ='Y' AND cpod.active_flag ='Y' AND cttb.xcc_transaction_type ='RECEIPT_RETRO_PRICE' AND cre.BUDGETARY_CONTROL_FLAG ='Y' AND cre.XCC_DATA_SET_ID is NOT NULL AND CRE.FUND_RESERVATION_STATUS IN ('NOT_RESERVED_FAILED','NOT_ATTEMPTED') UNION ALL SELECT cre.XCC_DATA_SET_ID DATA_SET_ID, TO_CHAR(cre.ACCOUNTING_EVENT_ID) SOURCE_HEADER_ID_1, ' ' SOURCE_HEADER_ID_2, DECODE(cre.EVENT_TYPE_CODE, '', cre.shipment_number, cre.sla_transaction_number) TRANSACTION_NUMBER, DECODE(cre.EVENT_TYPE_CODE, '', cre.shipment_number, cre.sla_transaction_number) RECEIPT_NUMBER, 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, NULL LINE_GROUP_CODE, TO_NUMBER(NULL) LINE_NUM, cre.LEDGER_ID LEDGER_ID, header.REQ_BU_ID BUSINESS_UNIT_ID, /* Bug#26042360 REQ BU not PCBU*/ 'EXPENSE' DESTINATION_TYPE_CODE, NULL DOCUMENT_TYPE_CODE, NULL TRANSACTION_SOURCE_CODE, cttb.XCC_TRANSACTION_SUBTYPE_CODE TRANSACTION_SUBTYPE_CODE, NULL JE_SOURCE_CODE, NULL JE_CATEGORY_CODE, NVL(cre.root_deliver_txn_date,cre.transaction_date) BUDGET_DATE, /*Bug 30999568 column required required to pass the budget date*/ cre.gl_date ACCOUNTING_DATE, NULL ENCUMBRANCE_TYPE_CODE, crec.CURRENCY_CODE ENTERED_CURRENCY, to_char(cre.SOURCE_DOC_UOM_CODE) UOM_CODE, cre.SOURCE_DOC_QTY QUANTITY, NULL PRICE, nvl(crec.EVENT_UNIT_COST,0) * NVL(cre.SOURCE_DOC_QTY,1) ENTERED_AMOUNT, /* this is used to book actual expenditr check with XCC */ NULL CONVERSION_TYPE_CODE, TO_DATE(NULL) CONVERSION_DATE, NVL(crec.currency_conversion_rate,1) * nvl(crec.EVENT_UNIT_COST,0) * NVL(cre.SOURCE_DOC_QTY,1) LEDGER_AMOUNT, /* always in Ledger currency only */ TO_DATE(NULL) LIQUIDATION_DATE, TO_NUMBER(NULL) LIQUIDATION_QUANTITY, cre.ENCUMBRANCE_REVERSAL_ENTR_AMT LIQUIDATION_AMOUNT, 'REQUISITION' LIQUIDATION_TRANS_TYPE_CODE, /* Confimr with XCC*/ TO_CHAR(lines.REQUISITION_LINE_ID) LIQUIDATION_LINE_ID_1, TO_CHAR(dists.DISTRIBUTION_ID) LIQUIDATION_LINE_ID_2, TO_CHAR(NULL) LIQUIDATION_LINE_ID_3, ' ' LIQUIDATION_LINE_ID_4, ' ' LIQUIDATION_LINE_ID_5, ' ' LIQUIDATION_LINE_ID_6, TO_NUMBER(NULL) STATISTICAL_AMOUNT, TO_NUMBER(NULL) BUDGET_CCID, TO_NUMBER(NULL) VENDOR_ID, cre.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, NULL ORDER_TYPE_INFO, ctod.charge_account_id CODE_COMBINATION_ID, ctod.PJC_PROJECT_ID, TO_NUMBER(ctod.PJC_TASK_ID) PJC_TASK_ID, TO_NUMBER(NULL) PJC_RESOURCE_ID, ctod.PJC_CONTRACT_ID, ctod.PJC_RESERVED_ATTRIBUTE1 PJC_FUNDING_SOURCE , ctod.PJC_CONTRACT_LINE_ID, ctod.PJC_FUNDING_ALLOCATION_ID, NVL(ctod.PJC_BILLABLE_FLAG, 'N') PJC_BILLABLE_FLAG, NVL(ctod.PJC_CAPITALIZABLE_FLAG, 'N') PJC_CAPITALIZABLE_FLAG, ctod.PJC_EXPENDITURE_TYPE_ID, ctod.PJC_EXPENDITURE_ITEM_DATE, ctod.PJC_ORGANIZATION_ID, ctod.PJC_WORK_TYPE_ID, TO_NUMBER(NULL) CONTROL_BUDGET_ID, ctod.PJC_RESERVED_ATTRIBUTE1 FUNDING_SOURCE_ID, cre.ENCUMBRANCE_REVERSAL_ACCT_AMT LIQUIDATION_AMOUNT_LEDGER_CUR, TO_NUMBER(NULL) PARENT_REVERSAL_ID, cre.bill_to_business_unit_id BILL_TO_BUSINESS_UNIT_ID FROM CMR_RCV_EVENTS cre, CMR_RCV_EVENT_COSTS crec, cst_transfer_order_dists ctod, cmr_transaction_types_b cttb, cmr_rcv_event_types cret, por_requisition_headers_all header, por_requisition_lines_all lines, por_req_distributions_all dists where dists.DISTRIBUTION_ID = ctod.req_distribution_id and dists.REQUISITION_LINE_ID = lines.REQUISITION_LINE_ID and lines.requisition_header_id = header.requisition_header_id and cre.ACCOUNTING_EVENT_ID =crec.ACCOUNTING_EVENT_ID AND cre.transfer_order_dist_id = ctod.distribution_id AND cret.event_type_code = cre.event_type_code AND cttb.transaction_type_code = cret.transaction_type and cre.event_class_code = 'TRO_RECEIPTS' AND cttb.xcc_transaction_type IS NOT NULL AND cre.BUDGETARY_CONTROL_FLAG ='Y' AND CRE.FUND_RESERVATION_STATUS IN ('NOT_RESERVED_FAILED','NOT_ATTEMPTED')