VIEW: APPS.PA_COMMITMENTS_V1

Source

View Text - Preformatted

SELECT /*+ leading(PRJ) */ PPRD.PROJECT_ID , PPRD.TASK_ID , 'ORACLE_PURCHASING' , 'R' , PPRD.REQ_NUMBER , PPRD.REQ_DISTRIBUTION_ID , PPRD.REQUISITION_HEADER_ID , PPRD.ITEM_DESCRIPTION , PPRD.EXPENDITURE_ITEM_DATE , NULL , NULL , PPRD.REQ_LINE , PPRD.CREATION_DATE , TO_DATE(NULL) , PPRD.REQUESTOR_NAME , TO_CHAR(NULL) , PPRD.APPROVED_FLAG , TO_DATE(NULL) , PPRD.NEED_BY_DATE , PPRD.EXPENDITURE_ORGANIZATION_ID , PPRD.VENDOR_ID , PPRD.VENDOR_NAME , PPRD.EXPENDITURE_TYPE , PPRD.EXPENDITURE_CATEGORY , PPRD.REVENUE_CATEGORY , 'VI' , PPRD.UNIT , PPRD.UNIT_PRICE , PA_BURDEN_CMTS.GET_CMT_COMPILED_SET_ID(NULL,'CMT',PPRD.TASK_ID, PPRD.EXPENDITURE_ITEM_DATE,PPRD.EXPENDITURE_ORGANIZATION_ID,PPRD.EXPENDITURE_TYPE,'C') , PPRD.AMOUNT , PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL,'CMT',PPRD.TASK_ID, PPRD.EXPENDITURE_ITEM_DATE,PPRD.EXPENDITURE_TYPE, PPRD.EXPENDITURE_ORGANIZATION_ID,'C',PPRD.AMOUNT) , PPRD.QUANTITY , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , NULL , NULL , NULL , TO_DATE(NULL) , TO_NUMBER(NULL) , SYSDATE , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_DATE(NULL) , PPRD.denom_currency_code , PPRD.denom_amount , PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL,'CMT',PPRD.TASK_ID, PPRD.EXPENDITURE_ITEM_DATE,PPRD.EXPENDITURE_TYPE, PPRD.EXPENDITURE_ORGANIZATION_ID,'C',PPRD.denom_amount) , PPRD.acct_currency_code , PPRD.acct_rate_date , PPRD.acct_rate_type , PPRD.acct_exchange_rate , TO_CHAR(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_CHAR(NULL) , TO_NUMBER(NULL) , TO_CHAR(NULL) , PPRD.UNIT , pprd.resource_class FROM PA_PROJ_REQ_DISTRIBUTIONS_V1 PPRD , PA_PROJ_SUMM_TMP PRJ WHERE PPRD.PROJECT_ID = PRJ.PROJECT_ID UNION ALL SELECT /*+ leading(PRJ) */ PPPD.PROJECT_ID, PPPD.TASK_ID, 'ORACLE_PURCHASING', 'P', PPPD.PO_NUMBER, PPPD.PO_DISTRIBUTION_ID, PPPD.PO_HEADER_ID, PPPD.ITEM_DESCRIPTION, PPPD.EXPENDITURE_ITEM_DATE, NULL, NULL, PPPD.PO_LINE, PPPD.CREATION_DATE, PPPD.APPROVED_DATE, PPPD.REQUESTOR_NAME, PPPD.BUYER_NAME, PPPD.APPROVED_FLAG, PPPD.PROMISED_DATE, PPPD.NEED_BY_DATE , PPPD.EXPENDITURE_ORGANIZATION_ID, PPPD.VENDOR_ID, PPPD.VENDOR_NAME, PPPD.EXPENDITURE_TYPE, PPPD.EXPENDITURE_CATEGORY, PPPD.REVENUE_CATEGORY, 'VI', PPPD.UNIT, PPPD.UNIT_PRICE, PA_BURDEN_CMTS.GET_CMT_COMPILED_SET_ID(NULL,'CMT',PPPD.TASK_ID ,PPPD.EXPENDITURE_ITEM_DATE,PPPD.EXPENDITURE_ORGANIZATION_ID,PPPD.EXPENDITURE_TYPE,'C'), PPPD.acct_raw_cost, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL,'CMT',PPPD.TASK_ID, PPPD.EXPENDITURE_ITEM_DATE,PPPD.EXPENDITURE_TYPE, PPPD.EXPENDITURE_ORGANIZATION_ID,'C', PPPD.acct_raw_cost) , PPPD.cmt_qty, PPPD.QUANTITY_ORDERED, PPPD.AMOUNT_ORDERED, PPPD.ORIGINAL_QUANTITY_ORDERED, PPPD.ORIGINAL_AMOUNT_ORDERED, PPPD.QUANTITY_CANCELLED, PPPD.AMOUNT_CANCELLED, PPPD.QUANTITY_DELIVERED, PPPD.QUANTITY_INVOICED, PPPD.AMOUNT_INVOICED, PPPD.QUANTITY_OUTSTANDING_DELIVERY, PPPD.AMOUNT_OUTSTANDING_DELIVERY, PPPD.QUANTITY_OUTSTANDING_INVOICE, PPPD.AMOUNT_OUTSTANDING_INVOICE, PPPD.QUANTITY_OVERBILLED, PPPD.AMOUNT_OVERBILLED, NULL, NULL, NULL, TO_DATE(NULL), TO_NUMBER(NULL), SYSDATE, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_DATE(NULL), PPPD.denom_currency_code, PPPD.denom_raw_cost, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL,'CMT',PPPD.TASK_ID, PPPD.EXPENDITURE_ITEM_DATE,PPPD.EXPENDITURE_TYPE, PPPD.EXPENDITURE_ORGANIZATION_ID,'C', PPPD.denom_raw_cost), PPPD.acct_currency_code, PPPD.acct_rate_date, PPPD.acct_rate_type, PPPD.acct_exchange_rate, TO_CHAR(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_CHAR(NULL) , TO_NUMBER(NULL) , TO_CHAR(NULL) , PPPD.UNIT , pppd.resource_class FROM PA_PROJ_PO_DISTRIBUTIONS_V1 PPPD , PA_PROJ_SUMM_TMP PRJ WHERE PPPD.CMT_QTY <> 0 AND PPPD.PROJECT_ID = PRJ.PROJECT_ID UNION ALL SELECT /*+ leading(PRJ) */ PPPD.PROJECT_ID, PPPD.TASK_ID, 'ORACLE_PURCHASING', 'P', PPPD.PO_NUMBER, PPPD.PO_DISTRIBUTION_ID, PPPD.PO_HEADER_ID, PPPD.ITEM_DESCRIPTION, PPPD.EXPENDITURE_ITEM_DATE, NULL, NULL, PPPD.PO_LINE, PPPD.CREATION_DATE, PPPD.APPROVED_DATE, PPPD.REQUESTOR_NAME, PPPD.BUYER_NAME, PPPD.APPROVED_FLAG, PPPD.PROMISED_DATE, PPPD.NEED_BY_DATE , PPPD.EXPENDITURE_ORGANIZATION_ID, PPPD.VENDOR_ID, PPPD.VENDOR_NAME, PPPD.EXPENDITURE_TYPE, PPPD.EXPENDITURE_CATEGORY, PPPD.REVENUE_CATEGORY, 'VI', PPPD.UNIT, PPPD.UNIT_PRICE, PA_BURDEN_CMTS.GET_CMT_COMPILED_SET_ID(NULL,'CMT',PPPD.TASK_ID ,PPPD.EXPENDITURE_ITEM_DATE,PPPD.EXPENDITURE_ORGANIZATION_ID,PPPD.EXPENDITURE_TYPE,'C'), PPPD.acct_raw_cost, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL,'CMT',PPPD.TASK_ID, PPPD.EXPENDITURE_ITEM_DATE,PPPD.EXPENDITURE_TYPE, PPPD.EXPENDITURE_ORGANIZATION_ID,'C', PPPD.acct_raw_cost) , PPPD.cmt_qty, PPPD.QUANTITY_ORDERED, PPPD.AMOUNT_ORDERED, PPPD.ORIGINAL_QUANTITY_ORDERED, PPPD.ORIGINAL_AMOUNT_ORDERED, PPPD.QUANTITY_CANCELLED, PPPD.AMOUNT_CANCELLED, PPPD.QUANTITY_DELIVERED, PPPD.QUANTITY_INVOICED, PPPD.AMOUNT_INVOICED, PPPD.QUANTITY_OUTSTANDING_DELIVERY, PPPD.AMOUNT_OUTSTANDING_DELIVERY, PPPD.QUANTITY_OUTSTANDING_INVOICE, PPPD.AMOUNT_OUTSTANDING_INVOICE, PPPD.QUANTITY_OVERBILLED, PPPD.AMOUNT_OVERBILLED, NULL, NULL, NULL, TO_DATE(NULL), TO_NUMBER(NULL), SYSDATE, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_DATE(NULL), PPPD.denom_currency_code, PPPD.denom_raw_cost, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL,'CMT',PPPD.TASK_ID, PPPD.EXPENDITURE_ITEM_DATE,PPPD.EXPENDITURE_TYPE, PPPD.EXPENDITURE_ORGANIZATION_ID,'C', PPPD.denom_raw_cost), PPPD.acct_currency_code, PPPD.acct_rate_date, PPPD.acct_rate_type, PPPD.acct_exchange_rate, TO_CHAR(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_CHAR(NULL) , TO_NUMBER(NULL) , TO_CHAR(NULL) , PPPD.UNIT , pppd.resource_class FROM PA_PROJ_PO_DISTRIBUTIONS_V2 PPPD , PA_PROJ_SUMM_TMP PRJ WHERE PPPD.CMT_QTY <> 0 AND PPPD.PROJECT_ID = PRJ.PROJECT_ID UNION ALL SELECT /*+ leading(PRJ) */ PPAID.PROJECT_ID, PPAID.TASK_ID, 'ORACLE_PAYABLES', 'I', PPAID.INVOICE_NUMBER, PPAID.invoice_distribution_id, PPAID.INVOICE_ID, PPAID.DESCRIPTION, PPAID.EXPENDITURE_ITEM_DATE, NULL, NULL, PPAID.DISTRIBUTION_LINE_NUMBER, PPAID.INVOICE_DATE, TO_DATE(NULL), TO_CHAR(NULL), TO_CHAR(NULL), PPAID.APPROVED_FLAG, TO_DATE(NULL), TO_DATE(NULL), PPAID.EXPENDITURE_ORGANIZATION_ID, VENDOR_ID, PPAID.VENDOR_NAME, PPAID.EXPENDITURE_TYPE, PPAID.EXPENDITURE_CATEGORY, PPAID.REVENUE_CATEGORY, 'VI', TO_CHAR(NULL), TO_NUMBER(NULL), PA_BURDEN_CMTS.GET_CMT_COMPILED_SET_ID(NULL,'CMT',PPAID.TASK_ID, PPAID.EXPENDITURE_ITEM_DATE,PPAID.EXPENDITURE_ORGANIZATION_ID,PPAID.EXPENDITURE_TYPE,'C'), PPAID.AMOUNT, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL,'CMT',PPAID.TASK_ID ,PPAID.EXPENDITURE_ITEM_DATE,PPAID.EXPENDITURE_TYPE, PPAID.EXPENDITURE_ORGANIZATION_ID,'C',PPAID.AMOUNT) , PPAID.QUANTITY, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), NULL, NULL, NULL, TO_DATE(NULL), TO_NUMBER(NULL), SYSDATE, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_DATE(NULL), PPAID.denom_currency_code, PPAID.denom_amount, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL,'CMT',PPAID.TASK_ID ,PPAID.EXPENDITURE_ITEM_DATE,PPAID.EXPENDITURE_TYPE, PPAID.EXPENDITURE_ORGANIZATION_ID,'C',PPAID.denom_amount), PPAID.acct_currency_code, PPAID.acct_rate_date, PPAID.acct_rate_type, PPAID.acct_exchange_rate, PPAID.receipt_currency_code , PPAID.receipt_currency_amount , PPAID.receipt_exchange_rate , TO_NUMBER(NULL) , TO_CHAR(NULL) , TO_NUMBER(NULL) , TO_CHAR(NULL) , TO_CHAR(NULL) , ppaid.resource_class FROM PA_PROJ_AP_INV_DIST_V1 PPAID , PA_PROJ_SUMM_TMP PRJ WHERE PPAID.PROJECT_ID = PRJ.PROJECT_ID AND (denom_amount <> 0) UNION ALL SELECT /*+ leading(PRJ) */ PPAID.PROJECT_ID, PPAID.TASK_ID, 'ORACLE_PAYABLES', 'I', PPAID.INVOICE_NUMBER, PPAID.invoice_distribution_id, PPAID.INVOICE_ID, PPAID.DESCRIPTION, PPAID.EXPENDITURE_ITEM_DATE, NULL, NULL, PPAID.DISTRIBUTION_LINE_NUMBER, PPAID.INVOICE_DATE, TO_DATE(NULL), TO_CHAR(NULL), TO_CHAR(NULL), PPAID.APPROVED_FLAG, TO_DATE(NULL), TO_DATE(NULL), PPAID.EXPENDITURE_ORGANIZATION_ID, VENDOR_ID, PPAID.VENDOR_NAME, PPAID.EXPENDITURE_TYPE, PPAID.EXPENDITURE_CATEGORY, PPAID.REVENUE_CATEGORY, 'VI', TO_CHAR(NULL), TO_NUMBER(NULL), PA_BURDEN_CMTS.GET_CMT_COMPILED_SET_ID(NULL,'CMT',PPAID.TASK_ID, PPAID.EXPENDITURE_ITEM_DATE,PPAID.EXPENDITURE_ORGANIZATION_ID,PPAID.EXPENDITURE_TYPE,'C'), PPAID.AMOUNT, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL,'CMT',PPAID.TASK_ID ,PPAID.EXPENDITURE_ITEM_DATE,PPAID.EXPENDITURE_TYPE, PPAID.EXPENDITURE_ORGANIZATION_ID,'C',PPAID.AMOUNT) , PPAID.QUANTITY, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), NULL, NULL, NULL, TO_DATE(NULL), TO_NUMBER(NULL), SYSDATE, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_DATE(NULL), PPAID.denom_currency_code, PPAID.denom_amount, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL,'CMT',PPAID.TASK_ID ,PPAID.EXPENDITURE_ITEM_DATE,PPAID.EXPENDITURE_TYPE, PPAID.EXPENDITURE_ORGANIZATION_ID,'C',PPAID.denom_amount), PPAID.acct_currency_code, PPAID.acct_rate_date, PPAID.acct_rate_type, PPAID.acct_exchange_rate, PPAID.receipt_currency_code , PPAID.receipt_currency_amount , PPAID.receipt_exchange_rate , TO_NUMBER(NULL) , TO_CHAR(NULL) , TO_NUMBER(NULL) , TO_CHAR(NULL) , TO_CHAR(NULL) , ppaid.resource_class FROM PA_PROJ_AP_INV_DIST_V2 PPAID , PA_PROJ_SUMM_TMP PRJ WHERE PPAID.PROJECT_ID = PRJ.PROJECT_ID AND (denom_amount <> 0) UNION ALL SELECT /*+ leading(PRJ) */ CST_PROJMFG_CMT_VIEW.project_id , task_id , transaction_source , line_type , cmt_number , cmt_distribution_id , cmt_header_id , description , expenditure_item_date , pa_period , gl_period , cmt_line_number , cmt_creation_date , cmt_approved_date , cmt_requestor_name , cmt_buyer_name , cmt_approved_flag , cmt_promised_date , cmt_need_by_date , organization_id , vendor_id , vendor_name , expenditure_type , expenditure_category , revenue_category , system_linkage_function , uom_meaning , unit_price , cmt_ind_compiled_set_id , acct_raw_cost , acct_burdened_cost , tot_cmt_quantity , quantity_ordered , amount_ordered , original_quantity_ordered , original_amount_ordered , quantity_cancelled , amount_cancelled , quantity_delivered , quantity_invoiced , amount_invoiced , quantity_outstanding_delivery , amount_outstanding_delivery , quantity_outstanding_invoice , amount_outstanding_invoice , quantity_overbilled , amount_overbilled , original_txn_reference1 , original_txn_reference2 , original_txn_reference3 , last_update_date , last_updated_by , creation_date , created_by , last_update_login , request_id , program_application_id , program_id , program_update_date , denom_currency_code , denom_raw_cost , denom_burdened_cost , acct_currency_code , acct_rate_date , acct_rate_type , acct_exchange_rate , TO_CHAR(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , INVENTORY_ITEM_ID , INVENTORY_ITEM_NAME , WIP_RESOURCE_ID , WIP_RESOURCE_NAME , UNIT_OF_MEASURE , DECODE(NVL(wip_resource_id, -99), -99, 'MATERIAL_ITEMS', 'FINANCIAL_ELEMENTS') FROM CST_PROJMFG_CMT_VIEW , PA_PROJ_SUMM_TMP PRJ WHERE CST_PROJMFG_CMT_VIEW.PROJECT_ID = PRJ.PROJECT_ID UNION ALL SELECT /*+ leading(PRJ) */ PMPRD.PROJECT_ID , PMPRD.TASK_ID , 'ORACLE_PURCHASING' , 'R' , PMPRD.REQ_NUMBER , PMPRD.REQ_DISTRIBUTION_ID , PMPRD.REQUISITION_HEADER_ID , PMPRD.ITEM_DESCRIPTION , PMPRD.EXPENDITURE_ITEM_DATE , NULL , NULL , PMPRD.REQ_LINE , PMPRD.CREATION_DATE , TO_DATE(NULL) , PMPRD.REQUESTOR_NAME , TO_CHAR(NULL) , PMPRD.APPROVED_FLAG , TO_DATE(NULL) , PMPRD.NEED_BY_DATE , PMPRD.EXPENDITURE_ORGANIZATION_ID , PMPRD.VENDOR_ID , PMPRD.VENDOR_NAME , PMPRD.EXPENDITURE_TYPE , PMPRD.EXPENDITURE_CATEGORY , PMPRD.REVENUE_CATEGORY , 'VI' , PMPRD.UNIT_OF_MEASURE , PMPRD.UNIT_PRICE , PA_BURDEN_CMTS.GET_CMT_COMPILED_SET_ID(NULL,'CMT',PMPRD.TASK_ID, PMPRD.EXPENDITURE_ITEM_DATE,PMPRD.EXPENDITURE_ORGANIZATION_ID,PMPRD.EXPENDITURE_TYPE,'C') , PMPRD.AMOUNT , PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL,'CMT',PMPRD.TASK_ID, PMPRD.EXPENDITURE_ITEM_DATE,PMPRD.EXPENDITURE_TYPE, PMPRD.EXPENDITURE_ORGANIZATION_ID,'C',PMPRD.AMOUNT) , PMPRD.QUANTITY , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , NULL , NULL , NULL , TO_DATE(NULL) , TO_NUMBER(NULL) , SYSDATE , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_DATE(NULL) , PMPRD.denom_currency_code , PMPRD.denom_amount , PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL,'CMT',PMPRD.TASK_ID, PMPRD.EXPENDITURE_ITEM_DATE,PMPRD.EXPENDITURE_TYPE, PMPRD.EXPENDITURE_ORGANIZATION_ID,'C',PMPRD.denom_amount) , PMPRD.acct_currency_code , PMPRD.acct_rate_date , PMPRD.acct_rate_type , PMPRD.acct_exchange_rate , TO_CHAR(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , INVENTORY_ITEM_ID , INVENTORY_ITEM_NAME , WIP_RESOURCE_ID , WIP_RESOURCE_NAME , UOM_CODE , DECODE(NVL(wip_resource_id, -99), -99, 'MATERIAL_ITEMS', 'FINANCIAL_ELEMENTS') FROM PA_PJM_REQ_COMMITMENTS_TMP PMPRD , PA_PROJ_SUMM_TMP PRJ WHERE PMPRD.PROJECT_ID = PRJ.PROJECT_ID UNION ALL SELECT /*+ leading(PRJ) */ PMPPD.PROJECT_ID, PMPPD.TASK_ID, 'ORACLE_PURCHASING', 'P', PMPPD.PO_NUMBER, PMPPD.PO_DISTRIBUTION_ID, PMPPD.PO_HEADER_ID, PMPPD.ITEM_DESCRIPTION, PMPPD.EXPENDITURE_ITEM_DATE, NULL, NULL, PMPPD.PO_LINE, PMPPD.CREATION_DATE, PMPPD.APPROVED_DATE, PMPPD.REQUESTOR_NAME, PMPPD.BUYER_NAME, PMPPD.APPROVED_FLAG, PMPPD.promised_date, PMPPD.need_by_date, PMPPD.EXPENDITURE_ORGANIZATION_ID, PMPPD.VENDOR_ID, PMPPD.VENDOR_NAME, PMPPD.EXPENDITURE_TYPE, PMPPD.EXPENDITURE_CATEGORY, PMPPD.REVENUE_CATEGORY, 'VI', PMPPD.UNIT_OF_MEASURE, PMPPD.UNIT_PRICE, PA_BURDEN_CMTS.GET_CMT_COMPILED_SET_ID(NULL,'CMT',PMPPD.TASK_ID ,PMPPD.EXPENDITURE_ITEM_DATE,PMPPD.EXPENDITURE_ORGANIZATION_ID,PMPPD.EXPENDITURE_TYPE,'C'), pmppd.amount_outstanding_delivery, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL,'CMT',PMPPD.TASK_ID, PMPPD.EXPENDITURE_ITEM_DATE,PMPPD.EXPENDITURE_TYPE, PMPPD.EXPENDITURE_ORGANIZATION_ID,'C', pmppd.amount_outstanding_delivery) , pmppd.quantity_outstanding_delivery, PMPPD.QUANTITY_ORDERED, PMPPD.AMOUNT_ORDERED, PMPPD.ORIGINAL_QUANTITY_ORDERED, PMPPD.ORIGINAL_AMOUNT_ORDERED, PMPPD.QUANTITY_CANCELLED, PMPPD.AMOUNT_CANCELLED, PMPPD.QUANTITY_DELIVERED, PMPPD.QUANTITY_INVOICED, PMPPD.AMOUNT_INVOICED, PMPPD.QUANTITY_OUTSTANDING_DELIVERY, PMPPD.AMOUNT_OUTSTANDING_DELIVERY, PMPPD.QUANTITY_OUTSTANDING_INVOICE, PMPPD.AMOUNT_OUTSTANDING_INVOICE, PMPPD.QUANTITY_OVERBILLED, PMPPD.AMOUNT_OVERBILLED, NULL, NULL, NULL, TO_DATE(NULL), TO_NUMBER(NULL), SYSDATE, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_DATE(NULL), PMPPD.denom_currency_code, PMPPD.denom_amt_outstanding_delivery, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL,'CMT',PMPPD.TASK_ID, PMPPD.EXPENDITURE_ITEM_DATE,PMPPD.EXPENDITURE_TYPE, PMPPD.EXPENDITURE_ORGANIZATION_ID,'C', PMPPD.denom_amt_outstanding_delivery), PMPPD.acct_currency_code, PMPPD.acct_rate_date, PMPPD.acct_rate_type, PMPPD.acct_exchange_rate, TO_CHAR(NULL) , TO_NUMBER(NULL), TO_NUMBER(NULL) , INVENTORY_ITEM_ID , INVENTORY_ITEM_NAME , WIP_RESOURCE_ID , WIP_RESOURCE_NAME , UOM_CODE , DECODE(NVL(wip_resource_id, -99), -99, 'MATERIAL_ITEMS', 'FINANCIAL_ELEMENTS') FROM PA_PJM_PO_COMMITMENTS_TMP PMPPD , PA_PROJ_SUMM_TMP PRJ WHERE PMPPD.PROJECT_ID = PRJ.PROJECT_ID

View Text - HTML Formatted

SELECT /*+ LEADING(PRJ) */ PPRD.PROJECT_ID
, PPRD.TASK_ID
, 'ORACLE_PURCHASING'
, 'R'
, PPRD.REQ_NUMBER
, PPRD.REQ_DISTRIBUTION_ID
, PPRD.REQUISITION_HEADER_ID
, PPRD.ITEM_DESCRIPTION
, PPRD.EXPENDITURE_ITEM_DATE
, NULL
, NULL
, PPRD.REQ_LINE
, PPRD.CREATION_DATE
, TO_DATE(NULL)
, PPRD.REQUESTOR_NAME
, TO_CHAR(NULL)
, PPRD.APPROVED_FLAG
, TO_DATE(NULL)
, PPRD.NEED_BY_DATE
, PPRD.EXPENDITURE_ORGANIZATION_ID
, PPRD.VENDOR_ID
, PPRD.VENDOR_NAME
, PPRD.EXPENDITURE_TYPE
, PPRD.EXPENDITURE_CATEGORY
, PPRD.REVENUE_CATEGORY
, 'VI'
, PPRD.UNIT
, PPRD.UNIT_PRICE
, PA_BURDEN_CMTS.GET_CMT_COMPILED_SET_ID(NULL
, 'CMT'
, PPRD.TASK_ID
, PPRD.EXPENDITURE_ITEM_DATE
, PPRD.EXPENDITURE_ORGANIZATION_ID
, PPRD.EXPENDITURE_TYPE
, 'C')
, PPRD.AMOUNT
, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL
, 'CMT'
, PPRD.TASK_ID
, PPRD.EXPENDITURE_ITEM_DATE
, PPRD.EXPENDITURE_TYPE
, PPRD.EXPENDITURE_ORGANIZATION_ID
, 'C'
, PPRD.AMOUNT)
, PPRD.QUANTITY
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, SYSDATE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, PPRD.DENOM_CURRENCY_CODE
, PPRD.DENOM_AMOUNT
, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL
, 'CMT'
, PPRD.TASK_ID
, PPRD.EXPENDITURE_ITEM_DATE
, PPRD.EXPENDITURE_TYPE
, PPRD.EXPENDITURE_ORGANIZATION_ID
, 'C'
, PPRD.DENOM_AMOUNT)
, PPRD.ACCT_CURRENCY_CODE
, PPRD.ACCT_RATE_DATE
, PPRD.ACCT_RATE_TYPE
, PPRD.ACCT_EXCHANGE_RATE
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, PPRD.UNIT
, PPRD.RESOURCE_CLASS
FROM PA_PROJ_REQ_DISTRIBUTIONS_V1 PPRD
, PA_PROJ_SUMM_TMP PRJ
WHERE PPRD.PROJECT_ID = PRJ.PROJECT_ID UNION ALL SELECT /*+ LEADING(PRJ) */ PPPD.PROJECT_ID
, PPPD.TASK_ID
, 'ORACLE_PURCHASING'
, 'P'
, PPPD.PO_NUMBER
, PPPD.PO_DISTRIBUTION_ID
, PPPD.PO_HEADER_ID
, PPPD.ITEM_DESCRIPTION
, PPPD.EXPENDITURE_ITEM_DATE
, NULL
, NULL
, PPPD.PO_LINE
, PPPD.CREATION_DATE
, PPPD.APPROVED_DATE
, PPPD.REQUESTOR_NAME
, PPPD.BUYER_NAME
, PPPD.APPROVED_FLAG
, PPPD.PROMISED_DATE
, PPPD.NEED_BY_DATE
, PPPD.EXPENDITURE_ORGANIZATION_ID
, PPPD.VENDOR_ID
, PPPD.VENDOR_NAME
, PPPD.EXPENDITURE_TYPE
, PPPD.EXPENDITURE_CATEGORY
, PPPD.REVENUE_CATEGORY
, 'VI'
, PPPD.UNIT
, PPPD.UNIT_PRICE
, PA_BURDEN_CMTS.GET_CMT_COMPILED_SET_ID(NULL
, 'CMT'
, PPPD.TASK_ID
, PPPD.EXPENDITURE_ITEM_DATE
, PPPD.EXPENDITURE_ORGANIZATION_ID
, PPPD.EXPENDITURE_TYPE
, 'C')
, PPPD.ACCT_RAW_COST
, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL
, 'CMT'
, PPPD.TASK_ID
, PPPD.EXPENDITURE_ITEM_DATE
, PPPD.EXPENDITURE_TYPE
, PPPD.EXPENDITURE_ORGANIZATION_ID
, 'C'
, PPPD.ACCT_RAW_COST)
, PPPD.CMT_QTY
, PPPD.QUANTITY_ORDERED
, PPPD.AMOUNT_ORDERED
, PPPD.ORIGINAL_QUANTITY_ORDERED
, PPPD.ORIGINAL_AMOUNT_ORDERED
, PPPD.QUANTITY_CANCELLED
, PPPD.AMOUNT_CANCELLED
, PPPD.QUANTITY_DELIVERED
, PPPD.QUANTITY_INVOICED
, PPPD.AMOUNT_INVOICED
, PPPD.QUANTITY_OUTSTANDING_DELIVERY
, PPPD.AMOUNT_OUTSTANDING_DELIVERY
, PPPD.QUANTITY_OUTSTANDING_INVOICE
, PPPD.AMOUNT_OUTSTANDING_INVOICE
, PPPD.QUANTITY_OVERBILLED
, PPPD.AMOUNT_OVERBILLED
, NULL
, NULL
, NULL
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, SYSDATE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, PPPD.DENOM_CURRENCY_CODE
, PPPD.DENOM_RAW_COST
, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL
, 'CMT'
, PPPD.TASK_ID
, PPPD.EXPENDITURE_ITEM_DATE
, PPPD.EXPENDITURE_TYPE
, PPPD.EXPENDITURE_ORGANIZATION_ID
, 'C'
, PPPD.DENOM_RAW_COST)
, PPPD.ACCT_CURRENCY_CODE
, PPPD.ACCT_RATE_DATE
, PPPD.ACCT_RATE_TYPE
, PPPD.ACCT_EXCHANGE_RATE
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, PPPD.UNIT
, PPPD.RESOURCE_CLASS
FROM PA_PROJ_PO_DISTRIBUTIONS_V1 PPPD
, PA_PROJ_SUMM_TMP PRJ
WHERE PPPD.CMT_QTY <> 0
AND PPPD.PROJECT_ID = PRJ.PROJECT_ID UNION ALL SELECT /*+ LEADING(PRJ) */ PPPD.PROJECT_ID
, PPPD.TASK_ID
, 'ORACLE_PURCHASING'
, 'P'
, PPPD.PO_NUMBER
, PPPD.PO_DISTRIBUTION_ID
, PPPD.PO_HEADER_ID
, PPPD.ITEM_DESCRIPTION
, PPPD.EXPENDITURE_ITEM_DATE
, NULL
, NULL
, PPPD.PO_LINE
, PPPD.CREATION_DATE
, PPPD.APPROVED_DATE
, PPPD.REQUESTOR_NAME
, PPPD.BUYER_NAME
, PPPD.APPROVED_FLAG
, PPPD.PROMISED_DATE
, PPPD.NEED_BY_DATE
, PPPD.EXPENDITURE_ORGANIZATION_ID
, PPPD.VENDOR_ID
, PPPD.VENDOR_NAME
, PPPD.EXPENDITURE_TYPE
, PPPD.EXPENDITURE_CATEGORY
, PPPD.REVENUE_CATEGORY
, 'VI'
, PPPD.UNIT
, PPPD.UNIT_PRICE
, PA_BURDEN_CMTS.GET_CMT_COMPILED_SET_ID(NULL
, 'CMT'
, PPPD.TASK_ID
, PPPD.EXPENDITURE_ITEM_DATE
, PPPD.EXPENDITURE_ORGANIZATION_ID
, PPPD.EXPENDITURE_TYPE
, 'C')
, PPPD.ACCT_RAW_COST
, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL
, 'CMT'
, PPPD.TASK_ID
, PPPD.EXPENDITURE_ITEM_DATE
, PPPD.EXPENDITURE_TYPE
, PPPD.EXPENDITURE_ORGANIZATION_ID
, 'C'
, PPPD.ACCT_RAW_COST)
, PPPD.CMT_QTY
, PPPD.QUANTITY_ORDERED
, PPPD.AMOUNT_ORDERED
, PPPD.ORIGINAL_QUANTITY_ORDERED
, PPPD.ORIGINAL_AMOUNT_ORDERED
, PPPD.QUANTITY_CANCELLED
, PPPD.AMOUNT_CANCELLED
, PPPD.QUANTITY_DELIVERED
, PPPD.QUANTITY_INVOICED
, PPPD.AMOUNT_INVOICED
, PPPD.QUANTITY_OUTSTANDING_DELIVERY
, PPPD.AMOUNT_OUTSTANDING_DELIVERY
, PPPD.QUANTITY_OUTSTANDING_INVOICE
, PPPD.AMOUNT_OUTSTANDING_INVOICE
, PPPD.QUANTITY_OVERBILLED
, PPPD.AMOUNT_OVERBILLED
, NULL
, NULL
, NULL
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, SYSDATE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, PPPD.DENOM_CURRENCY_CODE
, PPPD.DENOM_RAW_COST
, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL
, 'CMT'
, PPPD.TASK_ID
, PPPD.EXPENDITURE_ITEM_DATE
, PPPD.EXPENDITURE_TYPE
, PPPD.EXPENDITURE_ORGANIZATION_ID
, 'C'
, PPPD.DENOM_RAW_COST)
, PPPD.ACCT_CURRENCY_CODE
, PPPD.ACCT_RATE_DATE
, PPPD.ACCT_RATE_TYPE
, PPPD.ACCT_EXCHANGE_RATE
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, PPPD.UNIT
, PPPD.RESOURCE_CLASS
FROM PA_PROJ_PO_DISTRIBUTIONS_V2 PPPD
, PA_PROJ_SUMM_TMP PRJ
WHERE PPPD.CMT_QTY <> 0
AND PPPD.PROJECT_ID = PRJ.PROJECT_ID UNION ALL SELECT /*+ LEADING(PRJ) */ PPAID.PROJECT_ID
, PPAID.TASK_ID
, 'ORACLE_PAYABLES'
, 'I'
, PPAID.INVOICE_NUMBER
, PPAID.INVOICE_DISTRIBUTION_ID
, PPAID.INVOICE_ID
, PPAID.DESCRIPTION
, PPAID.EXPENDITURE_ITEM_DATE
, NULL
, NULL
, PPAID.DISTRIBUTION_LINE_NUMBER
, PPAID.INVOICE_DATE
, TO_DATE(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, PPAID.APPROVED_FLAG
, TO_DATE(NULL)
, TO_DATE(NULL)
, PPAID.EXPENDITURE_ORGANIZATION_ID
, VENDOR_ID
, PPAID.VENDOR_NAME
, PPAID.EXPENDITURE_TYPE
, PPAID.EXPENDITURE_CATEGORY
, PPAID.REVENUE_CATEGORY
, 'VI'
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, PA_BURDEN_CMTS.GET_CMT_COMPILED_SET_ID(NULL
, 'CMT'
, PPAID.TASK_ID
, PPAID.EXPENDITURE_ITEM_DATE
, PPAID.EXPENDITURE_ORGANIZATION_ID
, PPAID.EXPENDITURE_TYPE
, 'C')
, PPAID.AMOUNT
, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL
, 'CMT'
, PPAID.TASK_ID
, PPAID.EXPENDITURE_ITEM_DATE
, PPAID.EXPENDITURE_TYPE
, PPAID.EXPENDITURE_ORGANIZATION_ID
, 'C'
, PPAID.AMOUNT)
, PPAID.QUANTITY
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, SYSDATE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, PPAID.DENOM_CURRENCY_CODE
, PPAID.DENOM_AMOUNT
, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL
, 'CMT'
, PPAID.TASK_ID
, PPAID.EXPENDITURE_ITEM_DATE
, PPAID.EXPENDITURE_TYPE
, PPAID.EXPENDITURE_ORGANIZATION_ID
, 'C'
, PPAID.DENOM_AMOUNT)
, PPAID.ACCT_CURRENCY_CODE
, PPAID.ACCT_RATE_DATE
, PPAID.ACCT_RATE_TYPE
, PPAID.ACCT_EXCHANGE_RATE
, PPAID.RECEIPT_CURRENCY_CODE
, PPAID.RECEIPT_CURRENCY_AMOUNT
, PPAID.RECEIPT_EXCHANGE_RATE
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, PPAID.RESOURCE_CLASS
FROM PA_PROJ_AP_INV_DIST_V1 PPAID
, PA_PROJ_SUMM_TMP PRJ
WHERE PPAID.PROJECT_ID = PRJ.PROJECT_ID
AND (DENOM_AMOUNT <> 0) UNION ALL SELECT /*+ LEADING(PRJ) */ PPAID.PROJECT_ID
, PPAID.TASK_ID
, 'ORACLE_PAYABLES'
, 'I'
, PPAID.INVOICE_NUMBER
, PPAID.INVOICE_DISTRIBUTION_ID
, PPAID.INVOICE_ID
, PPAID.DESCRIPTION
, PPAID.EXPENDITURE_ITEM_DATE
, NULL
, NULL
, PPAID.DISTRIBUTION_LINE_NUMBER
, PPAID.INVOICE_DATE
, TO_DATE(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, PPAID.APPROVED_FLAG
, TO_DATE(NULL)
, TO_DATE(NULL)
, PPAID.EXPENDITURE_ORGANIZATION_ID
, VENDOR_ID
, PPAID.VENDOR_NAME
, PPAID.EXPENDITURE_TYPE
, PPAID.EXPENDITURE_CATEGORY
, PPAID.REVENUE_CATEGORY
, 'VI'
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, PA_BURDEN_CMTS.GET_CMT_COMPILED_SET_ID(NULL
, 'CMT'
, PPAID.TASK_ID
, PPAID.EXPENDITURE_ITEM_DATE
, PPAID.EXPENDITURE_ORGANIZATION_ID
, PPAID.EXPENDITURE_TYPE
, 'C')
, PPAID.AMOUNT
, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL
, 'CMT'
, PPAID.TASK_ID
, PPAID.EXPENDITURE_ITEM_DATE
, PPAID.EXPENDITURE_TYPE
, PPAID.EXPENDITURE_ORGANIZATION_ID
, 'C'
, PPAID.AMOUNT)
, PPAID.QUANTITY
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, SYSDATE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, PPAID.DENOM_CURRENCY_CODE
, PPAID.DENOM_AMOUNT
, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL
, 'CMT'
, PPAID.TASK_ID
, PPAID.EXPENDITURE_ITEM_DATE
, PPAID.EXPENDITURE_TYPE
, PPAID.EXPENDITURE_ORGANIZATION_ID
, 'C'
, PPAID.DENOM_AMOUNT)
, PPAID.ACCT_CURRENCY_CODE
, PPAID.ACCT_RATE_DATE
, PPAID.ACCT_RATE_TYPE
, PPAID.ACCT_EXCHANGE_RATE
, PPAID.RECEIPT_CURRENCY_CODE
, PPAID.RECEIPT_CURRENCY_AMOUNT
, PPAID.RECEIPT_EXCHANGE_RATE
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, PPAID.RESOURCE_CLASS
FROM PA_PROJ_AP_INV_DIST_V2 PPAID
, PA_PROJ_SUMM_TMP PRJ
WHERE PPAID.PROJECT_ID = PRJ.PROJECT_ID
AND (DENOM_AMOUNT <> 0) UNION ALL SELECT /*+ LEADING(PRJ) */ CST_PROJMFG_CMT_VIEW.PROJECT_ID
, TASK_ID
, TRANSACTION_SOURCE
, LINE_TYPE
, CMT_NUMBER
, CMT_DISTRIBUTION_ID
, CMT_HEADER_ID
, DESCRIPTION
, EXPENDITURE_ITEM_DATE
, PA_PERIOD
, GL_PERIOD
, CMT_LINE_NUMBER
, CMT_CREATION_DATE
, CMT_APPROVED_DATE
, CMT_REQUESTOR_NAME
, CMT_BUYER_NAME
, CMT_APPROVED_FLAG
, CMT_PROMISED_DATE
, CMT_NEED_BY_DATE
, ORGANIZATION_ID
, VENDOR_ID
, VENDOR_NAME
, EXPENDITURE_TYPE
, EXPENDITURE_CATEGORY
, REVENUE_CATEGORY
, SYSTEM_LINKAGE_FUNCTION
, UOM_MEANING
, UNIT_PRICE
, CMT_IND_COMPILED_SET_ID
, ACCT_RAW_COST
, ACCT_BURDENED_COST
, TOT_CMT_QUANTITY
, QUANTITY_ORDERED
, AMOUNT_ORDERED
, ORIGINAL_QUANTITY_ORDERED
, ORIGINAL_AMOUNT_ORDERED
, QUANTITY_CANCELLED
, AMOUNT_CANCELLED
, QUANTITY_DELIVERED
, QUANTITY_INVOICED
, AMOUNT_INVOICED
, QUANTITY_OUTSTANDING_DELIVERY
, AMOUNT_OUTSTANDING_DELIVERY
, QUANTITY_OUTSTANDING_INVOICE
, AMOUNT_OUTSTANDING_INVOICE
, QUANTITY_OVERBILLED
, AMOUNT_OVERBILLED
, ORIGINAL_TXN_REFERENCE1
, ORIGINAL_TXN_REFERENCE2
, ORIGINAL_TXN_REFERENCE3
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, DENOM_CURRENCY_CODE
, DENOM_RAW_COST
, DENOM_BURDENED_COST
, ACCT_CURRENCY_CODE
, ACCT_RATE_DATE
, ACCT_RATE_TYPE
, ACCT_EXCHANGE_RATE
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, INVENTORY_ITEM_ID
, INVENTORY_ITEM_NAME
, WIP_RESOURCE_ID
, WIP_RESOURCE_NAME
, UNIT_OF_MEASURE
, DECODE(NVL(WIP_RESOURCE_ID
, -99)
, -99
, 'MATERIAL_ITEMS'
, 'FINANCIAL_ELEMENTS')
FROM CST_PROJMFG_CMT_VIEW
, PA_PROJ_SUMM_TMP PRJ
WHERE CST_PROJMFG_CMT_VIEW.PROJECT_ID = PRJ.PROJECT_ID UNION ALL SELECT /*+ LEADING(PRJ) */ PMPRD.PROJECT_ID
, PMPRD.TASK_ID
, 'ORACLE_PURCHASING'
, 'R'
, PMPRD.REQ_NUMBER
, PMPRD.REQ_DISTRIBUTION_ID
, PMPRD.REQUISITION_HEADER_ID
, PMPRD.ITEM_DESCRIPTION
, PMPRD.EXPENDITURE_ITEM_DATE
, NULL
, NULL
, PMPRD.REQ_LINE
, PMPRD.CREATION_DATE
, TO_DATE(NULL)
, PMPRD.REQUESTOR_NAME
, TO_CHAR(NULL)
, PMPRD.APPROVED_FLAG
, TO_DATE(NULL)
, PMPRD.NEED_BY_DATE
, PMPRD.EXPENDITURE_ORGANIZATION_ID
, PMPRD.VENDOR_ID
, PMPRD.VENDOR_NAME
, PMPRD.EXPENDITURE_TYPE
, PMPRD.EXPENDITURE_CATEGORY
, PMPRD.REVENUE_CATEGORY
, 'VI'
, PMPRD.UNIT_OF_MEASURE
, PMPRD.UNIT_PRICE
, PA_BURDEN_CMTS.GET_CMT_COMPILED_SET_ID(NULL
, 'CMT'
, PMPRD.TASK_ID
, PMPRD.EXPENDITURE_ITEM_DATE
, PMPRD.EXPENDITURE_ORGANIZATION_ID
, PMPRD.EXPENDITURE_TYPE
, 'C')
, PMPRD.AMOUNT
, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL
, 'CMT'
, PMPRD.TASK_ID
, PMPRD.EXPENDITURE_ITEM_DATE
, PMPRD.EXPENDITURE_TYPE
, PMPRD.EXPENDITURE_ORGANIZATION_ID
, 'C'
, PMPRD.AMOUNT)
, PMPRD.QUANTITY
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, SYSDATE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, PMPRD.DENOM_CURRENCY_CODE
, PMPRD.DENOM_AMOUNT
, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL
, 'CMT'
, PMPRD.TASK_ID
, PMPRD.EXPENDITURE_ITEM_DATE
, PMPRD.EXPENDITURE_TYPE
, PMPRD.EXPENDITURE_ORGANIZATION_ID
, 'C'
, PMPRD.DENOM_AMOUNT)
, PMPRD.ACCT_CURRENCY_CODE
, PMPRD.ACCT_RATE_DATE
, PMPRD.ACCT_RATE_TYPE
, PMPRD.ACCT_EXCHANGE_RATE
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, INVENTORY_ITEM_ID
, INVENTORY_ITEM_NAME
, WIP_RESOURCE_ID
, WIP_RESOURCE_NAME
, UOM_CODE
, DECODE(NVL(WIP_RESOURCE_ID
, -99)
, -99
, 'MATERIAL_ITEMS'
, 'FINANCIAL_ELEMENTS')
FROM PA_PJM_REQ_COMMITMENTS_TMP PMPRD
, PA_PROJ_SUMM_TMP PRJ
WHERE PMPRD.PROJECT_ID = PRJ.PROJECT_ID UNION ALL SELECT /*+ LEADING(PRJ) */ PMPPD.PROJECT_ID
, PMPPD.TASK_ID
, 'ORACLE_PURCHASING'
, 'P'
, PMPPD.PO_NUMBER
, PMPPD.PO_DISTRIBUTION_ID
, PMPPD.PO_HEADER_ID
, PMPPD.ITEM_DESCRIPTION
, PMPPD.EXPENDITURE_ITEM_DATE
, NULL
, NULL
, PMPPD.PO_LINE
, PMPPD.CREATION_DATE
, PMPPD.APPROVED_DATE
, PMPPD.REQUESTOR_NAME
, PMPPD.BUYER_NAME
, PMPPD.APPROVED_FLAG
, PMPPD.PROMISED_DATE
, PMPPD.NEED_BY_DATE
, PMPPD.EXPENDITURE_ORGANIZATION_ID
, PMPPD.VENDOR_ID
, PMPPD.VENDOR_NAME
, PMPPD.EXPENDITURE_TYPE
, PMPPD.EXPENDITURE_CATEGORY
, PMPPD.REVENUE_CATEGORY
, 'VI'
, PMPPD.UNIT_OF_MEASURE
, PMPPD.UNIT_PRICE
, PA_BURDEN_CMTS.GET_CMT_COMPILED_SET_ID(NULL
, 'CMT'
, PMPPD.TASK_ID
, PMPPD.EXPENDITURE_ITEM_DATE
, PMPPD.EXPENDITURE_ORGANIZATION_ID
, PMPPD.EXPENDITURE_TYPE
, 'C')
, PMPPD.AMOUNT_OUTSTANDING_DELIVERY
, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL
, 'CMT'
, PMPPD.TASK_ID
, PMPPD.EXPENDITURE_ITEM_DATE
, PMPPD.EXPENDITURE_TYPE
, PMPPD.EXPENDITURE_ORGANIZATION_ID
, 'C'
, PMPPD.AMOUNT_OUTSTANDING_DELIVERY)
, PMPPD.QUANTITY_OUTSTANDING_DELIVERY
, PMPPD.QUANTITY_ORDERED
, PMPPD.AMOUNT_ORDERED
, PMPPD.ORIGINAL_QUANTITY_ORDERED
, PMPPD.ORIGINAL_AMOUNT_ORDERED
, PMPPD.QUANTITY_CANCELLED
, PMPPD.AMOUNT_CANCELLED
, PMPPD.QUANTITY_DELIVERED
, PMPPD.QUANTITY_INVOICED
, PMPPD.AMOUNT_INVOICED
, PMPPD.QUANTITY_OUTSTANDING_DELIVERY
, PMPPD.AMOUNT_OUTSTANDING_DELIVERY
, PMPPD.QUANTITY_OUTSTANDING_INVOICE
, PMPPD.AMOUNT_OUTSTANDING_INVOICE
, PMPPD.QUANTITY_OVERBILLED
, PMPPD.AMOUNT_OVERBILLED
, NULL
, NULL
, NULL
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, SYSDATE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, PMPPD.DENOM_CURRENCY_CODE
, PMPPD.DENOM_AMT_OUTSTANDING_DELIVERY
, PA_BURDEN_CMTS.GET_CMT_BURDENED_COST(NULL
, 'CMT'
, PMPPD.TASK_ID
, PMPPD.EXPENDITURE_ITEM_DATE
, PMPPD.EXPENDITURE_TYPE
, PMPPD.EXPENDITURE_ORGANIZATION_ID
, 'C'
, PMPPD.DENOM_AMT_OUTSTANDING_DELIVERY)
, PMPPD.ACCT_CURRENCY_CODE
, PMPPD.ACCT_RATE_DATE
, PMPPD.ACCT_RATE_TYPE
, PMPPD.ACCT_EXCHANGE_RATE
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, INVENTORY_ITEM_ID
, INVENTORY_ITEM_NAME
, WIP_RESOURCE_ID
, WIP_RESOURCE_NAME
, UOM_CODE
, DECODE(NVL(WIP_RESOURCE_ID
, -99)
, -99
, 'MATERIAL_ITEMS'
, 'FINANCIAL_ELEMENTS')
FROM PA_PJM_PO_COMMITMENTS_TMP PMPPD
, PA_PROJ_SUMM_TMP PRJ
WHERE PMPPD.PROJECT_ID = PRJ.PROJECT_ID