1. Tables and Views for Procurement
  2. PO_PJC_COMMITMENTS_ALL_V

PO_PJC_COMMITMENTS_ALL_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 "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') "APPROVED_FLAG",

PL.QUANTITY,

PL.UNIT_PRICE,

PL.UOM_CODE,

PD.DELIVER_TO_PERSON_ID,

PH.VENDOR_ID,

PH.AGENT_ID "BUYER_ID",

PH.REQ_BU_ID,

PH.CURRENCY_CODE,

PD.RECOVERABLE_INCLUSIVE_TAX,

PD.NONRECOVERABLE_INCLUSIVE_TAX "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 "HEADER_RATE_DATE",

PH.RATE "HEADER_RATE",

PD.RATE "DISTRIBUTION_RATE",

PD.RATE_DATE "DISTRIBUTION_RATE_DATE",

PO_BCEA_PROCESS_PVT.GET_OUTSTANDING_AMOUNT(PD.PO_DISTRIBUTION_ID) "OUTSTANDING_AMOUNT",

(PD.QUANTITY_ORDERED - PD.QUANTITY_CANCELLED - PD.QUANTITY_BILLED) "OUTSTANDING_QUANTITY_BILLED",

(PD.QUANTITY_ORDERED - PD.QUANTITY_CANCELLED - PD.QUANTITY_DELIVERED) "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) ) "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)) "OUTSTANDING_AMOUNT_DELIVERED",

NVL(PLL.MATCHING_BASIS,PL.MATCHING_BASIS) MATCHING_BASIS,

NVL(PLL.VALUE_BASIS,PL.ORDER_TYPE_LOOKUP_CODE) ORDER_TYPE_LOOKUP_CODE,

PH.PRC_BU_ID,

PH.SOLDTO_LE_ID,

PH.BILLTO_BU_ID,

PD.SET_OF_BOOKS_ID "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','INCOMPLETE','WITHDRAWN','REJECTED')

AND PD.PJC_PROJECT_ID = RCS.PROJECT_ID (+)

UNION ALL

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 "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,

'N' "APPROVED_FLAG",

PL.QUANTITY,

PL.UNIT_PRICE,

PL.UOM_CODE,

PD.DELIVER_TO_PERSON_ID,

PH.VENDOR_ID,

PH.AGENT_ID "BUYER_ID",

PH.REQ_BU_ID,

PH.CURRENCY_CODE,

PD.RECOVERABLE_INCLUSIVE_TAX,

PD.NONRECOVERABLE_INCLUSIVE_TAX "NONRECOVERABLE_INCL_TAX",

PD.RECOVERABLE_TAX,

PD.NONRECOVERABLE_TAX,

PD.QUANTITY_ORDERED,

0,

0,

NVL(PLL.CO_QUANTITY_CANCELLED,0),

0,

0,

0,

NVL(PLL.CO_AMOUNT_CANCELLED,0),

0,

PD.AMOUNT_ORDERED,

PLL.PRICE_OVERRIDE,

PH.RATE_TYPE,

PH.RATE_DATE "HEADER_RATE_DATE",

PH.RATE "HEADER_RATE",

PD.RATE "DISTRIBUTION_RATE",

PD.RATE_DATE "DISTRIBUTION_RATE_DATE",

(DECODE(PLL.VALUE_BASIS, 'QUANTITY', (PD.QUANTITY_ORDERED * PLL.PRICE_OVERRIDE) , PD.AMOUNT_ORDERED) +

(nvl(pd.nonrecoverable_tax,0) - nvl(pd.recoverable_inclusive_tax,0)) ) "OUTSTANDING_AMOUNT",

PD.QUANTITY_ORDERED - NVL(PD.CO_QUANTITY_CANCELLED,0) "OUTSTANDING_QUANTITY_BILLED",

PD.QUANTITY_ORDERED - NVL(PD.CO_QUANTITY_CANCELLED,0) "OUTSTANDING_QUANTITY_DELIVERED",

DECODE(PLL.VALUE_BASIS, 'QUANTITY', (PD.QUANTITY_ORDERED - NVL(PD.CO_QUANTITY_CANCELLED,0)) * PLL.PRICE_OVERRIDE ,

(PD.AMOUNT_ORDERED - NVL(PD.CO_AMOUNT_CANCELLED,0) ) ) "OUTSTANDING_AMOUNT_BILLED",

DECODE(PLL.VALUE_BASIS, 'QUANTITY', (PD.QUANTITY_ORDERED - NVL(PD.CO_QUANTITY_CANCELLED,0)) * PLL.PRICE_OVERRIDE ,

(PD.AMOUNT_ORDERED - NVL(PD.CO_AMOUNT_CANCELLED,0))) "OUTSTANDING_AMOUNT_DELIVERED",

NVL(PLL.MATCHING_BASIS,PL.MATCHING_BASIS) MATCHING_BASIS,

NVL(PLL.VALUE_BASIS,PL.ORDER_TYPE_LOOKUP_CODE) ORDER_TYPE_LOOKUP_CODE,

PH.PRC_BU_ID,

PH.SOLDTO_LE_ID,

PH.BILLTO_BU_ID,

PD.SET_OF_BOOKS_ID "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 PDT,

PO_DISTRIBUTIONS_DRAFT_ALL PD,

PO_LINES_DRAFT_ALL PL,

PO_HEADERS_ALL PH,

PO_LINE_LOCATIONS_DRAFT_ALL PLL,

RCS_PROJ_COST_COLL_STATUS_V RCS

WHERE PD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID

AND PD.PO_DISTRIBUTION_ID = PDT.PO_DISTRIBUTION_ID (+)

AND PLL.PO_LINE_ID = PL.PO_LINE_ID

AND PL.PO_HEADER_ID = PH.PO_HEADER_ID

AND (PDT.po_distribution_id is null OR PD.ENTITY_CHANGE_TYPE_CODE = 'I')

AND PD.PJC_PROJECT_ID = RCS.PROJECT_ID (+)