PO_PJC_COMMITMENTS_V

Details

• Schema: FUSION

• Object owner: PO

• Object type: VIEW

Columns

Name

DISTRIBUTION_NUM PO_DISTRIBUTION_ID LINE_LOCATION_ID PO_LINE_ID PO_HEADER_ID LINE_NUM REVISION_NUM DOCUMENT_NUMBER CATEGORY_ID ITEM_DESCRIPTION ITEM_ID CREATION_DATE NEED_BY_DATE PROMISED_DATE APPROVED_DATE CLOSED_DATE APPROVED_FLAG QUANTITY UNIT_PRICE UOM_CODE DELIVER_TO_PERSON_ID VENDOR_ID BUYER_ID REQ_BU_ID CURRENCY_CODE RECOVERABLE_INCLUSIVE_TAX NONRECOVERABLE_INCL_TAX RECOVERABLE_TAX NONRECOVERABLE_TAX QUANTITY_ORDERED QUANTITY_DELIVERED QUANTITY_BILLED QUANTITY_CANCELLED QUANTITY_RECEIVED AMOUNT_BILLED AMOUNT_RECEIVED AMOUNT_CANCELLED AMOUNT_DELIVERED AMOUNT_ORDERED PRICE_OVERRIDE RATE_TYPE HEADER_RATE_DATE HEADER_RATE DISTRIBUTION_RATE DISTRIBUTION_RATE_DATE OUTSTANDING_AMOUNT OUTSTANDING_QUANTITY_BILLED OUTSTANDING_QUANTITY_DELIVERED OUTSTANDING_AMOUNT_BILLED OUTSTANDING_AMOUNT_DELIVERED MATCHING_BASIS ORDER_TYPE_LOOKUP_CODE PRC_BU_ID SOLDTO_LE_ID BILLTO_BU_ID LEDGER_ID BUDGET_DATE FUNDS_STATUS REQ_HEADER_REFERENCE_NUM REQ_LINE_REFERENCE_NUM REQ_DISTRIBUTION_ID CODE_COMBINATION_ID ACCRUE_ON_RECEIPT_FLAG DESTINATION_TYPE_CODE CONSIGNMENT_LINE_FLAG PJC_CONTEXT_CATEGORY PJC_PROJECT_ID PJC_TASK_ID PJC_EXPENDITURE_TYPE_ID PJC_EXPENDITURE_ITEM_DATE PJC_ORGANIZATION_ID PJC_BILLABLE_FLAG PJC_CAPITALIZABLE_FLAG PJC_WORK_TYPE_ID PJC_CONTRACT_ID PJC_CONTRACT_LINE_ID PJC_FUNDING_ALLOCATION_ID 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 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 PROJECT_TYPE_ID COST_COLL_EXCLUDED

Query

SQL_Statement

SELECT PD.DISTRIBUTION_NUM, PD.PO_DISTRIBUTION_ID, PD.LINE_LOCATION_ID, PD.PO_LINE_ID, PD.PO_HEADER_ID, PL.LINE_NUM, PH.REVISION_NUM, PH.SEGMENT1 AS "DOCUMENT_NUMBER", PL.CATEGORY_ID, PL.ITEM_DESCRIPTION, PL.ITEM_ID, PD.CREATION_DATE, PLL.NEED_BY_DATE, PLL.PROMISED_DATE, PH.APPROVED_DATE, PH.CLOSED_DATE, NVL(APPROVED_FLAG, 'N') AS "APPROVED_FLAG", PL.QUANTITY, PL.UNIT_PRICE, PL.UOM_CODE, PD.DELIVER_TO_PERSON_ID, PH.VENDOR_ID, PH.AGENT_ID AS "BUYER_ID", PH.REQ_BU_ID, PH.CURRENCY_CODE, PD.RECOVERABLE_INCLUSIVE_TAX, PD.NONRECOVERABLE_INCLUSIVE_TAX AS "NONRECOVERABLE_INCL_TAX", PD.RECOVERABLE_TAX, PD.NONRECOVERABLE_TAX, PD.QUANTITY_ORDERED, PD.QUANTITY_DELIVERED, PD.QUANTITY_BILLED, PLL.QUANTITY_CANCELLED, PLL.QUANTITY_RECEIVED, PD.AMOUNT_BILLED, PLL.AMOUNT_RECEIVED, PLL.AMOUNT_CANCELLED, PD.AMOUNT_DELIVERED, PD.AMOUNT_ORDERED, PLL.PRICE_OVERRIDE, PH.RATE_TYPE, PH.RATE_DATE AS "HEADER_RATE_DATE", PH.RATE AS "HEADER_RATE", PD.RATE AS "DISTRIBUTION_RATE", PD.RATE_DATE AS "DISTRIBUTION_RATE_DATE", PO_BCEA_PROCESS_PVT.GET_OUTSTANDING_AMOUNT(PD.PO_DISTRIBUTION_ID) AS "OUTSTANDING_AMOUNT", (PD.QUANTITY_ORDERED - PD.QUANTITY_CANCELLED - PD.QUANTITY_BILLED) AS "OUTSTANDING_QUANTITY_BILLED", (PD.QUANTITY_ORDERED - PD.QUANTITY_CANCELLED - PD.QUANTITY_DELIVERED) AS "OUTSTANDING_QUANTITY_DELIVERED", DECODE(PLL.VALUE_BASIS, 'QUANTITY', (PD.QUANTITY_ORDERED - PD.QUANTITY_CANCELLED - PD.QUANTITY_BILLED) * PLL.PRICE_OVERRIDE , (PD.AMOUNT_ORDERED - PD.AMOUNT_CANCELLED - PD.AMOUNT_BILLED) ) AS "OUTSTANDING_AMOUNT_BILLED", DECODE(PLL.VALUE_BASIS, 'QUANTITY', (PD.QUANTITY_ORDERED - PD.QUANTITY_CANCELLED - PD.QUANTITY_DELIVERED) * PLL.PRICE_OVERRIDE , (PD.AMOUNT_ORDERED - PD.AMOUNT_CANCELLED - PD.AMOUNT_DELIVERED)) AS "OUTSTANDING_AMOUNT_DELIVERED", NVL(PLL.MATCHING_BASIS,PL.MATCHING_BASIS), NVL(PLL.VALUE_BASIS,PL.ORDER_TYPE_LOOKUP_CODE), PH.PRC_BU_ID, PH.SOLDTO_LE_ID, PH.BILLTO_BU_ID, PD.SET_OF_BOOKS_ID AS "LEDGER_ID", PD.BUDGET_DATE, PD.FUNDS_STATUS, PD.REQ_HEADER_REFERENCE_NUM, PD.REQ_LINE_REFERENCE_NUM, PD.REQ_DISTRIBUTION_ID, PD.CODE_COMBINATION_ID, PLL.ACCRUE_ON_RECEIPT_FLAG, PD.DESTINATION_TYPE_CODE, PL.CONSIGNMENT_LINE_FLAG, PD.PJC_CONTEXT_CATEGORY, PD.PJC_PROJECT_ID, PD.PJC_TASK_ID, PD.PJC_EXPENDITURE_TYPE_ID, PD.PJC_EXPENDITURE_ITEM_DATE, PD.PJC_ORGANIZATION_ID, PD.PJC_BILLABLE_FLAG, PD.PJC_CAPITALIZABLE_FLAG, PD.PJC_WORK_TYPE_ID, PD.PJC_CONTRACT_ID, PD.PJC_CONTRACT_LINE_ID, PD.PJC_FUNDING_ALLOCATION_ID, PD.PJC_USER_DEF_ATTRIBUTE1, PD.PJC_USER_DEF_ATTRIBUTE2, PD.PJC_USER_DEF_ATTRIBUTE3, PD.PJC_USER_DEF_ATTRIBUTE4, PD.PJC_USER_DEF_ATTRIBUTE5, PD.PJC_USER_DEF_ATTRIBUTE6, PD.PJC_USER_DEF_ATTRIBUTE7, PD.PJC_USER_DEF_ATTRIBUTE8, PD.PJC_USER_DEF_ATTRIBUTE9, PD.PJC_USER_DEF_ATTRIBUTE10, PD.PJC_RESERVED_ATTRIBUTE1, PD.PJC_RESERVED_ATTRIBUTE2, PD.PJC_RESERVED_ATTRIBUTE3, PD.PJC_RESERVED_ATTRIBUTE4, PD.PJC_RESERVED_ATTRIBUTE5, PD.PJC_RESERVED_ATTRIBUTE6, PD.PJC_RESERVED_ATTRIBUTE7, PD.PJC_RESERVED_ATTRIBUTE8, PD.PJC_RESERVED_ATTRIBUTE9, PD.PJC_RESERVED_ATTRIBUTE10, RCS.PROJECT_TYPE_ID, RCS.COST_COLL_EXCLUDED FROM PO_DISTRIBUTIONS_ALL PD, PO_LINES_ALL PL, PO_HEADERS_ALL PH, PO_LINE_LOCATIONS_ALL PLL, RCS_PROJ_COST_COLL_STATUS_V RCS WHERE PD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND PLL.PO_LINE_ID = PL.PO_LINE_ID AND PL.PO_HEADER_ID = PH.PO_HEADER_ID AND NVL(PLL.SCHEDULE_STATUS,'INCOMPLETE') NOT IN ('FINALLY CLOSED') AND PD.PJC_PROJECT_ID = RCS.PROJECT_ID (+)