PJC_PRJ_PO_DIST

Details

Schema: FUSION
Object owner: PJC
Object type: VIEW

Columns

Name
PO_NUMBER PO_REVISION RELEASE_NUMBER RELEASE_REVISION APPROVED_FLAG EVER_APPROVED_FLAG DOCUMENT_TYPE CREATION_DATE APPROVED_DATE PRINTED_DATE REQUESTOR_PERSON_ID REQUESTOR_NAME BUYER_PERSON_ID BUYER_NAME VENDOR_NAME VENDOR_ID PO_LINE ITEM_DESCRIPTION UNIT UNIT_PRICE QUANTITY_ORDERED AMOUNT_ORDERED ORIGINAL_QUANTITY_ORDERED ORIGINAL_AMOUNT_ORDERED QUANTITY_CANCELLED AMOUNT_CANCELLED QUANTITY_DELIVERED DENOM_AMOUNT_DELIVERED AMOUNT_DELIVERED QUANTITY_INVOICED AMOUNT_INVOICED QUANTITY_OUTSTANDING_DELIVERY AMOUNT_OUTSTANDING_DELIVERY QUANTITY_OUTSTANDING_INVOICE DENOM_CURRENCY_CODE DENOM_AMT_OUTSTANDING_INVOICE ACCT_CURRENCY_CODE AMOUNT_OUTSTANDING_INVOICE ACCT_RATE_DATE ACCT_RATE_TYPE ACCT_EXCHANGE_RATE QUANTITY_OVERBILLED AMOUNT_OVERBILLED PROJECT_NUMBER PROJECT_NAME TASK_NUMBER TASK_NAME EXPENDITURE_TYPE EXPENDITURE_CATEGORY REVENUE_CATEGORY EXPENDITURE_ITEM_DATE EXPENDITURE_ORGANIZATION PO_HEADER_ID PO_RELEASE_ID PO_LINE_ID PO_LINE_LOCATION_ID PO_DISTRIBUTION_ID PO_DISTRIBUTION_NUM PROJECT_ID PROJECT_TYPE_ID PROJECT_CURRENCY_CODE PROJ_CURR_CONV_DATE_TYPE_CODE PROJ_CURRENCY_CONV_DATE PROJ_CURR_RATE_TYPE TASK_ID EXPENDITURE_ORGANIZATION_ID PROMISED_DATE NEED_BY_DATE CMT_QTY DENOM_RAW_COST ACCT_RAW_COST RESOURCE_CLASS ORG_ID AWARD_SET_ID EXPENDITURE_TYPE_ID EXP_TYPE_COST_RATE_FLAG EXPENDITURE_CATEGORY_ID BILLABLE_FLAG CAPITALIZABLE_FLAG WORK_TYPE_ID CONTRACT_ID FUNDING_SOURCE_ID FUNDING_ALLOCATION_ID CONTRACT_LINE_ID

Name

PO_NUMBER

PO_REVISION

RELEASE_NUMBER

RELEASE_REVISION

APPROVED_FLAG

EVER_APPROVED_FLAG

DOCUMENT_TYPE

CREATION_DATE

APPROVED_DATE

PRINTED_DATE

REQUESTOR_PERSON_ID

REQUESTOR_NAME

BUYER_PERSON_ID

BUYER_NAME

VENDOR_NAME

VENDOR_ID

PO_LINE

ITEM_DESCRIPTION

UNIT

UNIT_PRICE

QUANTITY_ORDERED

AMOUNT_ORDERED

ORIGINAL_QUANTITY_ORDERED

ORIGINAL_AMOUNT_ORDERED

QUANTITY_CANCELLED

AMOUNT_CANCELLED

QUANTITY_DELIVERED

DENOM_AMOUNT_DELIVERED

AMOUNT_DELIVERED

QUANTITY_INVOICED

AMOUNT_INVOICED

QUANTITY_OUTSTANDING_DELIVERY

AMOUNT_OUTSTANDING_DELIVERY

QUANTITY_OUTSTANDING_INVOICE

DENOM_CURRENCY_CODE

DENOM_AMT_OUTSTANDING_INVOICE

ACCT_CURRENCY_CODE

AMOUNT_OUTSTANDING_INVOICE

ACCT_RATE_DATE

ACCT_RATE_TYPE

ACCT_EXCHANGE_RATE

QUANTITY_OVERBILLED

AMOUNT_OVERBILLED

PROJECT_NUMBER

PROJECT_NAME

TASK_NUMBER

TASK_NAME

EXPENDITURE_TYPE

EXPENDITURE_CATEGORY

REVENUE_CATEGORY

EXPENDITURE_ITEM_DATE

EXPENDITURE_ORGANIZATION

PO_HEADER_ID

PO_RELEASE_ID

PO_LINE_ID

PO_LINE_LOCATION_ID

PO_DISTRIBUTION_ID

PO_DISTRIBUTION_NUM

PROJECT_ID

PROJECT_TYPE_ID

PROJECT_CURRENCY_CODE

PROJ_CURR_CONV_DATE_TYPE_CODE

PROJ_CURRENCY_CONV_DATE

PROJ_CURR_RATE_TYPE

TASK_ID

EXPENDITURE_ORGANIZATION_ID

PROMISED_DATE

NEED_BY_DATE

CMT_QTY

DENOM_RAW_COST

ACCT_RAW_COST

RESOURCE_CLASS

ORG_ID

AWARD_SET_ID

EXPENDITURE_TYPE_ID

EXP_TYPE_COST_RATE_FLAG

EXPENDITURE_CATEGORY_ID

BILLABLE_FLAG

CAPITALIZABLE_FLAG

WORK_TYPE_ID

CONTRACT_ID

FUNDING_SOURCE_ID

FUNDING_ALLOCATION_ID

CONTRACT_LINE_ID

Query

SQL_Statement
SELECT POH.SEGMENT1 PO_NUMBER , POH.REVISION_NUM PO_REVISION , null RELEASE_NUMBER , null RELEASE_REVISION , DECODE( NVL(POH.DOCUMENT_STATUS,'OPEN'), 'PENDING APPROVAL','N', 'OPEN','Y', 'CLOSED FOR INVOICING','Y', 'CLOSED FOR RECEIVING','Y', 'ON HOLD','Y', 'PENDING CATALOG AUTHORING','N', 'PENDING ACKNOWLEDGMENT','N' ) APPROVED_FLAG , null EVER_APPROVED_FLAG , NULL DOCUMENT_TYPE , null CREATION_DATE , null APPROVED_DATE , null PRINTED_DATE , REQ.PERSON_ID REQUESTOR_PERSON_ID , REQ.FULL_NAME REQUESTOR_NAME , BUY.PERSON_ID BUYER_PERSON_ID , BUY.FULL_NAME BUYER_NAME , V.VENDOR_NAME VENDOR_NAME , V.VENDOR_ID VENDOR_ID , POL.LINE_NUM PO_LINE , POL.ITEM_DESCRIPTION ITEM_DESCRIPTION , DECODE(PLL.VALUE_BASIS,'AMOUNT',NULL, POL.UOM_CODE) UNIT , POL.UNIT_PRICE UNIT_PRICE , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', (pod.amount_ordered - NVL (pod.amount_cancelled, 0)), 'QUANTITY', ( pod.quantity_ordered - NVL (pod.quantity_cancelled, 0)) ) QUANTITY_ORDERED , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', (pod.amount_ordered - NVL (pod.amount_cancelled, 0)), 'QUANTITY', ( pod.quantity_ordered - NVL (pod.quantity_cancelled, 0)) * pol.unit_price ) AMOUNT_ORDERED , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pod.amount_ordered, 'QUANTITY', pod.quantity_ordered) ORIGINAL_QUANTITY_ORDERED , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pod.amount_ordered, 'QUANTITY', pod.quantity_ordered * (POL.UNIT_PRICE)) ORIGINAL_AMOUNT_ORDERED , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pod.amount_cancelled, 'QUANTITY', pod.quantity_cancelled) QUANTITY_CANCELLED , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pod.amount_cancelled, 'QUANTITY', pod.quantity_cancelled * pol.unit_price) AMOUNT_CANCELLED , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pod.amount_delivered, 'QUANTITY', pod.quantity_delivered) QUANTITY_DELIVERED , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pod.amount_delivered, 'QUANTITY', pod.quantity_delivered * (pll.price_override)) DENOM_AMOUNT_DELIVERED , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pod.amount_delivered, 'QUANTITY', pod.quantity_delivered * (pll.price_override)) AMOUNT_DELIVERED , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', NVL (pod.amount_billed, 0), 'QUANTITY', NVL (pod.quantity_billed, 0)) QUANTITY_INVOICED , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', NVL (pod.amount_billed, 0), 'QUANTITY', NVL (pod.amount_billed, 0)) AMOUNT_INVOICED , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', ( pod.amount_ordered - NVL (pod.amount_cancelled, 0) - NVL (pod.amount_delivered, 0)), 'QUANTITY', ( pod.quantity_ordered - NVL (pod.quantity_cancelled, 0) - NVL (pod.quantity_delivered, 0))) QUANTITY_OUTSTANDING_DELIVERY , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', ( pod.amount_ordered - NVL (pod.amount_cancelled, 0) - NVL (pod.amount_delivered, 0)), 'QUANTITY', ( pod.quantity_ordered - NVL (pod.quantity_cancelled, 0) - NVL (pod.quantity_delivered, 0)) pol.unit_price ) AMOUNT_OUTSTANDING_DELIVERY , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', GREATEST (0, ( pod.amount_ordered - NVL (pod.amount_cancelled, 0) - NVL (pod.amount_billed, 0))), 'QUANTITY', GREATEST (0, ( pod.quantity_ordered - NVL (pod.quantity_cancelled, 0) - NVL (pod.quantity_billed, 0)))) QUANTITY_OUTSTANDING_INVOICE , POH.CURRENCY_CODE DENOM_CURRENCY_CODE , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', GREATEST (0, ( pod.amount_ordered - NVL (pod.amount_cancelled, 0) - NVL (pod.amount_billed, 0))) ( 1 + (NVL (pod.nonrecoverable_tax, 0) / pod.amount_ordered)), 'QUANTITY', GREATEST (0, ( pod.quantity_ordered - NVL (pod.quantity_cancelled, 0) - NVL (pod.quantity_billed, 0))) * ( (pll.price_override) + (NVL (pod.nonrecoverable_tax, 0) / pod.quantity_ordered))) DENOM_AMT_OUTSTANDING_INVOICE , G.CURRENCY_CODE ACCT_CURRENCY_CODE , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', GREATEST (0, ( pod.amount_ordered - NVL (pod.amount_cancelled, 0) - NVL (pod.amount_billed, 0))) * ( 1 + (NVL (pod.nonrecoverable_tax, 0) / pod.amount_ordered)), 'QUANTITY', GREATEST (0, ( pod.quantity_ordered - NVL (pod.quantity_cancelled, 0) - NVL (pod.quantity_billed, 0))) * ( (pll.price_override) + (NVL (pod.nonrecoverable_tax, 0) / pod.quantity_ordered))) AMOUNT_OUTSTANDING_INVOICE , decode(NVL(POH.CURRENCY_CODE,'-99'),NVL(G.CURRENCY_CODE,'-99'),TO_DATE(NULL),POD.RATE_DATE) ACCT_RATE_DATE , decode(NVL(POH.CURRENCY_CODE,'-99'),NVL(G.CURRENCY_CODE,'-99'),NULL,POH.RATE_TYPE) ACCT_RATE_TYPE , decode(NVL(POH.CURRENCY_CODE,'-99'),NVL(G.CURRENCY_CODE,'-99'),TO_NUMBER(NULL),POD.RATE) ACCT_EXCHANGE_RATE , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', GREATEST (0, -1 * ( pod.amount_ordered - NVL (pod.amount_cancelled, 0) - NVL (pod.amount_billed, 0))), 'QUANTITY', GREATEST (0, -1 * ( pod.quantity_ordered - NVL (pod.quantity_cancelled, 0) - NVL (pod.quantity_billed, 0)))) QUANTITY_OVERBILLED , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', GREATEST (0, -1 * ( pod.amount_ordered - NVL (pod.amount_cancelled, 0) - NVL (pod.amount_billed, 0))), 'QUANTITY', GREATEST (0, -1 * ( pod.quantity_ordered - NVL (pod.quantity_cancelled, 0) - NVL (pod.quantity_billed, 0))) * (pll.price_override * NVL (pod.rate, 1))) AMOUNT_OVERBILLED , P.SEGMENT1 PROJECT_NUMBER , P.NAME PROJECT_NAME , T.TASK_NUMBER TASK_NUMBER , T.TASK_NAME TASK_NAME , ET.EXPENDITURE_TYPE_NAME EXPENDITURE_TYPE , CAT.EXPENDITURE_CATEGORY_NAME EXPENDITURE_CATEGORY , ET.REVENUE_CATEGORY_CODE REVENUE_CATEGORY , POD.PJC_EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE , O.NAME EXPENDITURE_ORGANIZATION , POH.PO_HEADER_ID PO_HEADER_ID , null PO_RELEASE_ID , POL.PO_LINE_ID PO_LINE_ID , PLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID , POD.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID , POD.DISTRIBUTION_NUM PO_DISTRIBUTION_NUM , P.PROJECT_ID PROJECT_ID , P.PROJECT_TYPE_ID PROJECT_TYPE_ID , P.PROJECT_CURRENCY_CODE PROJECT_CURRENCY_CODE , P.CURRENCY_CONV_DATE_TYPE_CODE PROJ_CURR_CONV_DATE_TYPE_CODE , P.CURRENCY_CONV_DATE PROJ_CURRENCY_CONV_DATE , P.CURRENCY_CONV_RATE_TYPE PROJ_CURR_RATE_TYPE , T.TASK_ID TASK_ID , O.ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID , PLL.PROMISED_DATE PROMISED_DATE , PLL.NEED_BY_DATE NEED_BY_DATE , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', GREATEST (0, (pjc_cmt_utils.get_rcpt_qty (pod.po_distribution_id, pod.amount_ordered, NVL (pod.amount_cancelled, 0), NVL (pod.amount_billed, 0), 'PO', pol.po_line_id, pod.pjc_project_id, pod.pjc_task_id, pod.code_combination_id, 0, NULL, NULL, pll.matching_basis, NVL (pod.nonrecoverable_tax, 0), pod.accrue_on_receipt_flag ) ) ), 'QUANTITY', GREATEST (0, (pjc_cmt_utils.get_rcpt_qty (pod.po_distribution_id, pod.quantity_ordered, NVL (pod.quantity_cancelled, 0), NVL (pod.quantity_billed, 0), 'PO', pol.po_line_id, pod.pjc_project_id, pod.pjc_task_id, pod.code_combination_id, 0, NULL, NULL, pll.matching_basis, NULL, pod.accrue_on_receipt_flag ) ) ) ) CMT_QTY , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', GREATEST (0, (pjc_cmt_utils.get_rcpt_qty (pod.po_distribution_id, pod.amount_ordered, NVL (pod.amount_cancelled, 0), NVL (pod.amount_billed, 0), 'PO', pol.po_line_id, pod.pjc_project_id, pod.pjc_task_id, pod.code_combination_id, 0, NULL, NULL, pll.matching_basis, NVL (pod.nonrecoverable_tax, 0), pod.accrue_on_receipt_flag ) ) ), 'QUANTITY', GREATEST (0, (pjc_cmt_utils.get_rcpt_qty (pod.po_distribution_id, pod.quantity_ordered, NVL (pod.quantity_cancelled, 0), NVL (pod.quantity_billed, 0), 'PO', pol.po_line_id, pod.pjc_project_id, pod.pjc_task_id, pod.code_combination_id, 0, NULL, NULL, pll.matching_basis, NULL, pod.accrue_on_receipt_flag ) ) ) * ( (pll.price_override) + (NVL (pod.nonrecoverable_tax, 0) / pod.quantity_ordered)) ) DENOM_RAW_COST , NULL ACCT_RAW_COST , 'FINANCIAL_ELEMENTS' RESOURCE_CLASS , POD.REQ_BU_ID ORG_ID , POD.award_id AWARD_SET_ID , POD.PJC_EXPENDITURE_TYPE_ID EXPENDITURE_TYPE_ID , ET.COST_RATE_FLAG EXP_TYPE_COST_RATE_FLAG , CAT.EXPENDITURE_CATEGORY_ID EXPENDITURE_CATEGORY_ID , POD.PJC_BILLABLE_FLAG BILLABLE_FLAG , POD.PJC_CAPITALIZABLE_FLAG CAPITALIZABLE_FLAG , POD.PJC_WORK_TYPE_ID WORK_TYPE_ID , POD.PJC_CONTRACT_ID CONTRACT_ID/Added for grants uptake/ , POD.PJC_RESERVED_ATTRIBUTE1 FUNDING_SOURCE_ID/Added for grants uptake/ , POD.PJC_FUNDING_ALLOCATION_ID FUNDING_ALLOCATION_ID/Added for grants uptake/ , POD.PJC_CONTRACT_LINE_ID CONTRACT_LINE_ID/Added for grants uptake/ FROM GL_LEDGERS g, PO_HEADERS_ALL POH, POZ_SUPPLIERS_V V, PO_LINES_ALL POL, PO_LINE_TYPES LT, PO_LINE_LOCATIONS_ALL PLL, PER_PERSON_NAMES_F_V BUY, PER_PERSON_NAMES_F_V REQ, HR_ORGANIZATION_V O, PJF_EXP_TYPES_VL ET, PJF_TASKS_V T, PO_DISTRIBUTIONS_ALL POD, PJF_PROJECTS_ALL_VL P, PJF_EXP_CATEGORIES_VL CAT WHERE POH.VENDOR_ID = V.VENDOR_ID (+) AND POH.AGENT_ID = BUY.PERSON_ID AND TRUNC(SYSDATE) BETWEEN BUY.EFFECTIVE_START_DATE AND BUY.EFFECTIVE_END_DATE AND POD.DISTRIBUTION_TYPE <> 'PREPAYMENT' AND POD.DELIVER_TO_PERSON_ID = REQ.PERSON_ID(+) AND TRUNC(SYSDATE) BETWEEN NVL(REQ.EFFECTIVE_START_DATE,TRUNC(SYSDATE)) AND NVL(REQ.EFFECTIVE_END_DATE,TRUNC(SYSDATE)) AND PLL.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED') AND POH.TYPE_LOOKUP_CODE IN ('STANDARD','BLANKET','PLANNED') AND NVL(POH.DOCUMENT_STATUS,'INCOMPLETE') IN ('PENDING APPROVAL','OPEN','CLOSED FOR INVOICING','CLOSED FOR RECEIVING','ON HOLD','PENDING CATALOG AUTHORING','PENDING ACKNOWLEDGMENT' ) AND NVL(PLL.SCHEDULE_STATUS,'INCOMPLETE') IN ('PENDING APPROVAL','OPEN','CLOSED FOR INVOICING','CLOSED FOR RECEIVING','ON HOLD','PENDING CATALOG AUTHORING','PENDING ACKNOWLEDGMENT' ) AND NVL(POH.CANCEL_FLAG,'N') = 'N' AND NVL(PLL.CANCEL_FLAG,'N') <> 'Y' AND POL.PO_HEADER_ID = POH.PO_HEADER_ID AND POL.LINE_TYPE_ID = LT.LINE_TYPE_ID AND POL.PO_LINE_ID = PLL.PO_LINE_ID AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID AND POD.PJC_PROJECT_ID = P.PROJECT_ID AND POD.PJC_TASK_ID = T.TASK_ID AND POD.PJC_ORGANIZATION_ID = O.ORGANIZATION_ID AND POD.PJC_EXPENDITURE_TYPE_ID = ET.EXPENDITURE_TYPE_ID AND POD.DESTINATION_TYPE_CODE = 'EXPENSE' AND G.LEDGER_ID = POD.SET_OF_BOOKS_ID AND NVL(PLL.MATCHING_BASIS,'QUANTITY') IN ('QUANTITY','AMOUNT') and POD.PJC_PROJECT_ID IS NOT NULL AND CAT.EXPENDITURE_CATEGORY_ID = ET.EXPENDITURE_CATEGORY_ID AND O.CLASSIFICATION_CODE = 'PA_EXPENDITURE_ORG' AND NVL(POD.PJC_EXPENDITURE_ITEM_DATE, TRUNC(SYSDATE)) BETWEEN NVL(O.EFFECTIVE_START_DATE,TRUNC(SYSDATE)) AND NVL(O.EFFECTIVE_END_DATE,TRUNC(SYSDATE)) AND ((pll.matching_basis = 'AMOUNT' AND pod.amount_ordered <> 0) or (pll.matching_basis = 'QUANTITY' and pod.quantity_ordered <> 0))

SQL_Statement

SELECT

POH.SEGMENT1 PO_NUMBER

, POH.REVISION_NUM PO_REVISION

, null RELEASE_NUMBER

, null RELEASE_REVISION

, DECODE( NVL(POH.DOCUMENT_STATUS,'OPEN'),

'PENDING APPROVAL','N',

'OPEN','Y',

'CLOSED FOR INVOICING','Y',

'CLOSED FOR RECEIVING','Y',

'ON HOLD','Y',

'PENDING CATALOG AUTHORING','N',

'PENDING ACKNOWLEDGMENT','N' ) APPROVED_FLAG

, null EVER_APPROVED_FLAG

, NULL DOCUMENT_TYPE

, null CREATION_DATE

, null APPROVED_DATE

, null PRINTED_DATE

, REQ.PERSON_ID REQUESTOR_PERSON_ID

, REQ.FULL_NAME REQUESTOR_NAME

, BUY.PERSON_ID BUYER_PERSON_ID

, BUY.FULL_NAME BUYER_NAME

, V.VENDOR_NAME VENDOR_NAME

, V.VENDOR_ID VENDOR_ID

, POL.LINE_NUM PO_LINE

, POL.ITEM_DESCRIPTION ITEM_DESCRIPTION

, DECODE(PLL.VALUE_BASIS,'AMOUNT',NULL, POL.UOM_CODE) UNIT

, POL.UNIT_PRICE UNIT_PRICE

, DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', (pod.amount_ordered - NVL (pod.amount_cancelled, 0)), 'QUANTITY', ( pod.quantity_ordered - NVL (pod.quantity_cancelled, 0)) ) QUANTITY_ORDERED

, DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', (pod.amount_ordered - NVL (pod.amount_cancelled, 0)), 'QUANTITY', ( pod.quantity_ordered - NVL (pod.quantity_cancelled, 0)) * pol.unit_price ) AMOUNT_ORDERED

, DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pod.amount_ordered, 'QUANTITY', pod.quantity_ordered) ORIGINAL_QUANTITY_ORDERED

, DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pod.amount_ordered, 'QUANTITY', pod.quantity_ordered * (POL.UNIT_PRICE)) ORIGINAL_AMOUNT_ORDERED

, DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pod.amount_cancelled, 'QUANTITY', pod.quantity_cancelled) QUANTITY_CANCELLED

, DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pod.amount_cancelled, 'QUANTITY', pod.quantity_cancelled * pol.unit_price) AMOUNT_CANCELLED

, DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pod.amount_delivered, 'QUANTITY', pod.quantity_delivered) QUANTITY_DELIVERED

, DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pod.amount_delivered, 'QUANTITY', pod.quantity_delivered * (pll.price_override)) DENOM_AMOUNT_DELIVERED

, DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pod.amount_delivered, 'QUANTITY', pod.quantity_delivered * (pll.price_override)) AMOUNT_DELIVERED

, DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', NVL (pod.amount_billed, 0), 'QUANTITY', NVL (pod.quantity_billed, 0)) QUANTITY_INVOICED

, DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', NVL (pod.amount_billed, 0), 'QUANTITY', NVL (pod.amount_billed, 0)) AMOUNT_INVOICED

, DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', ( pod.amount_ordered - NVL (pod.amount_cancelled, 0) - NVL (pod.amount_delivered, 0)), 'QUANTITY', ( pod.quantity_ordered - NVL (pod.quantity_cancelled, 0) - NVL (pod.quantity_delivered, 0))) QUANTITY_OUTSTANDING_DELIVERY

, DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', ( pod.amount_ordered - NVL (pod.amount_cancelled, 0) - NVL (pod.amount_delivered, 0)), 'QUANTITY', ( pod.quantity_ordered - NVL (pod.quantity_cancelled, 0) - NVL (pod.quantity_delivered, 0)) *pol.unit_price ) AMOUNT_OUTSTANDING_DELIVERY

, DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', GREATEST (0, ( pod.amount_ordered - NVL (pod.amount_cancelled, 0) - NVL (pod.amount_billed, 0))), 'QUANTITY', GREATEST (0, ( pod.quantity_ordered - NVL (pod.quantity_cancelled, 0) - NVL (pod.quantity_billed, 0)))) QUANTITY_OUTSTANDING_INVOICE

, POH.CURRENCY_CODE DENOM_CURRENCY_CODE

, DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', GREATEST (0, ( pod.amount_ordered - NVL (pod.amount_cancelled, 0) - NVL (pod.amount_billed, 0))) * ( 1 + (NVL (pod.nonrecoverable_tax, 0) / pod.amount_ordered)), 'QUANTITY', GREATEST (0, ( pod.quantity_ordered - NVL (pod.quantity_cancelled, 0) - NVL (pod.quantity_billed, 0))) * ( (pll.price_override) + (NVL (pod.nonrecoverable_tax, 0) / pod.quantity_ordered))) DENOM_AMT_OUTSTANDING_INVOICE

, G.CURRENCY_CODE ACCT_CURRENCY_CODE

, DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', GREATEST (0, ( pod.amount_ordered - NVL (pod.amount_cancelled, 0) - NVL (pod.amount_billed, 0))) * ( 1 + (NVL (pod.nonrecoverable_tax, 0) / pod.amount_ordered)), 'QUANTITY', GREATEST (0, ( pod.quantity_ordered - NVL (pod.quantity_cancelled, 0) - NVL (pod.quantity_billed, 0))) * ( (pll.price_override) + (NVL (pod.nonrecoverable_tax, 0) / pod.quantity_ordered))) AMOUNT_OUTSTANDING_INVOICE

, decode(NVL(POH.CURRENCY_CODE,'-99'),NVL(G.CURRENCY_CODE,'-99'),TO_DATE(NULL),POD.RATE_DATE) ACCT_RATE_DATE

, decode(NVL(POH.CURRENCY_CODE,'-99'),NVL(G.CURRENCY_CODE,'-99'),NULL,POH.RATE_TYPE) ACCT_RATE_TYPE

, decode(NVL(POH.CURRENCY_CODE,'-99'),NVL(G.CURRENCY_CODE,'-99'),TO_NUMBER(NULL),POD.RATE) ACCT_EXCHANGE_RATE

, DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', GREATEST (0, -1 * ( pod.amount_ordered - NVL (pod.amount_cancelled, 0) - NVL (pod.amount_billed, 0))), 'QUANTITY', GREATEST (0, -1 * ( pod.quantity_ordered - NVL (pod.quantity_cancelled, 0) - NVL (pod.quantity_billed, 0)))) QUANTITY_OVERBILLED

, DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', GREATEST (0, -1 * ( pod.amount_ordered - NVL (pod.amount_cancelled, 0) - NVL (pod.amount_billed, 0))), 'QUANTITY', GREATEST (0, -1 * ( pod.quantity_ordered - NVL (pod.quantity_cancelled, 0) - NVL (pod.quantity_billed, 0))) * (pll.price_override * NVL (pod.rate, 1))) AMOUNT_OVERBILLED

, P.SEGMENT1 PROJECT_NUMBER

, P.NAME PROJECT_NAME

, T.TASK_NUMBER TASK_NUMBER

, T.TASK_NAME TASK_NAME

, ET.EXPENDITURE_TYPE_NAME EXPENDITURE_TYPE

, CAT.EXPENDITURE_CATEGORY_NAME EXPENDITURE_CATEGORY

, ET.REVENUE_CATEGORY_CODE REVENUE_CATEGORY

, POD.PJC_EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE

, O.NAME EXPENDITURE_ORGANIZATION

, POH.PO_HEADER_ID PO_HEADER_ID

, null PO_RELEASE_ID

, POL.PO_LINE_ID PO_LINE_ID

, PLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID

, POD.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID

, POD.DISTRIBUTION_NUM PO_DISTRIBUTION_NUM

, P.PROJECT_ID PROJECT_ID

, P.PROJECT_TYPE_ID PROJECT_TYPE_ID

, P.PROJECT_CURRENCY_CODE PROJECT_CURRENCY_CODE

, P.CURRENCY_CONV_DATE_TYPE_CODE PROJ_CURR_CONV_DATE_TYPE_CODE

, P.CURRENCY_CONV_DATE PROJ_CURRENCY_CONV_DATE

, P.CURRENCY_CONV_RATE_TYPE PROJ_CURR_RATE_TYPE

, T.TASK_ID TASK_ID

, O.ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID

, PLL.PROMISED_DATE PROMISED_DATE

, PLL.NEED_BY_DATE NEED_BY_DATE

, DECODE (NVL (pll.matching_basis, 'QUANTITY'),

'AMOUNT', GREATEST

(0,

(pjc_cmt_utils.get_rcpt_qty (pod.po_distribution_id,

pod.amount_ordered,

NVL (pod.amount_cancelled, 0),

NVL (pod.amount_billed, 0),

'PO',

pol.po_line_id,

pod.pjc_project_id,

pod.pjc_task_id,

pod.code_combination_id,

0,

NULL,

pll.matching_basis,

NVL (pod.nonrecoverable_tax, 0),

pod.accrue_on_receipt_flag

)

),

'QUANTITY', GREATEST

(0,

(pjc_cmt_utils.get_rcpt_qty (pod.po_distribution_id,

pod.quantity_ordered,

NVL (pod.quantity_cancelled, 0),

NVL (pod.quantity_billed, 0),

'PO',

pol.po_line_id,

pod.pjc_project_id,

pod.pjc_task_id,

pod.code_combination_id,

0,

NULL,

pll.matching_basis,

NULL,

pod.accrue_on_receipt_flag

)

) CMT_QTY

, DECODE

(NVL (pll.matching_basis, 'QUANTITY'),

'AMOUNT', GREATEST

(0,

(pjc_cmt_utils.get_rcpt_qty (pod.po_distribution_id,

pod.amount_ordered,

NVL (pod.amount_cancelled, 0),

NVL (pod.amount_billed, 0),

'PO',

pol.po_line_id,

pod.pjc_project_id,

pod.pjc_task_id,

pod.code_combination_id,

0,

NULL,

pll.matching_basis,

NVL (pod.nonrecoverable_tax, 0),

pod.accrue_on_receipt_flag

)

),

'QUANTITY', GREATEST

(0,

(pjc_cmt_utils.get_rcpt_qty (pod.po_distribution_id,

pod.quantity_ordered,

NVL (pod.quantity_cancelled, 0),

NVL (pod.quantity_billed, 0),

'PO',

pol.po_line_id,

pod.pjc_project_id,

pod.pjc_task_id,

pod.code_combination_id,

0,

NULL,

pll.matching_basis,

NULL,

pod.accrue_on_receipt_flag

)

* ( (pll.price_override) + (NVL (pod.nonrecoverable_tax, 0) / pod.quantity_ordered))

) DENOM_RAW_COST

, NULL ACCT_RAW_COST

, 'FINANCIAL_ELEMENTS' RESOURCE_CLASS

, POD.REQ_BU_ID ORG_ID

, POD.award_id AWARD_SET_ID

, POD.PJC_EXPENDITURE_TYPE_ID EXPENDITURE_TYPE_ID

, ET.COST_RATE_FLAG EXP_TYPE_COST_RATE_FLAG

, CAT.EXPENDITURE_CATEGORY_ID EXPENDITURE_CATEGORY_ID

, POD.PJC_BILLABLE_FLAG BILLABLE_FLAG

, POD.PJC_CAPITALIZABLE_FLAG CAPITALIZABLE_FLAG

, POD.PJC_WORK_TYPE_ID WORK_TYPE_ID

, POD.PJC_CONTRACT_ID CONTRACT_ID/*Added for grants uptake*/

, POD.PJC_RESERVED_ATTRIBUTE1 FUNDING_SOURCE_ID/*Added for grants uptake*/

, POD.PJC_FUNDING_ALLOCATION_ID FUNDING_ALLOCATION_ID/*Added for grants uptake*/

, POD.PJC_CONTRACT_LINE_ID CONTRACT_LINE_ID/*Added for grants uptake*/

FROM

GL_LEDGERS g,

PO_HEADERS_ALL POH,

POZ_SUPPLIERS_V V,

PO_LINES_ALL POL,

PO_LINE_TYPES LT,

PO_LINE_LOCATIONS_ALL PLL,

PER_PERSON_NAMES_F_V BUY,

PER_PERSON_NAMES_F_V REQ,

HR_ORGANIZATION_V O,

PJF_EXP_TYPES_VL ET,

PJF_TASKS_V T,

PO_DISTRIBUTIONS_ALL POD,

PJF_PROJECTS_ALL_VL P,

PJF_EXP_CATEGORIES_VL CAT

WHERE POH.VENDOR_ID = V.VENDOR_ID (+)

AND POH.AGENT_ID = BUY.PERSON_ID

AND TRUNC(SYSDATE)

BETWEEN BUY.EFFECTIVE_START_DATE

AND BUY.EFFECTIVE_END_DATE

AND POD.DISTRIBUTION_TYPE <> 'PREPAYMENT'

AND POD.DELIVER_TO_PERSON_ID = REQ.PERSON_ID(+)

AND TRUNC(SYSDATE)

BETWEEN NVL(REQ.EFFECTIVE_START_DATE,TRUNC(SYSDATE))

AND NVL(REQ.EFFECTIVE_END_DATE,TRUNC(SYSDATE))

AND PLL.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')

AND POH.TYPE_LOOKUP_CODE IN ('STANDARD','BLANKET','PLANNED')

AND NVL(POH.DOCUMENT_STATUS,'INCOMPLETE') IN ('PENDING APPROVAL','OPEN','CLOSED FOR INVOICING','CLOSED FOR RECEIVING','ON HOLD','PENDING CATALOG AUTHORING','PENDING ACKNOWLEDGMENT' )

AND NVL(PLL.SCHEDULE_STATUS,'INCOMPLETE') IN ('PENDING APPROVAL','OPEN','CLOSED FOR INVOICING','CLOSED FOR RECEIVING','ON HOLD','PENDING CATALOG AUTHORING','PENDING ACKNOWLEDGMENT' )

AND NVL(POH.CANCEL_FLAG,'N') = 'N'

AND NVL(PLL.CANCEL_FLAG,'N') <> 'Y'

AND POL.PO_HEADER_ID = POH.PO_HEADER_ID

AND POL.LINE_TYPE_ID = LT.LINE_TYPE_ID

AND POL.PO_LINE_ID = PLL.PO_LINE_ID

AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID

AND POD.PJC_PROJECT_ID = P.PROJECT_ID

AND POD.PJC_TASK_ID = T.TASK_ID

AND POD.PJC_ORGANIZATION_ID = O.ORGANIZATION_ID

AND POD.PJC_EXPENDITURE_TYPE_ID = ET.EXPENDITURE_TYPE_ID

AND POD.DESTINATION_TYPE_CODE = 'EXPENSE'

AND G.LEDGER_ID = POD.SET_OF_BOOKS_ID

AND NVL(PLL.MATCHING_BASIS,'QUANTITY') IN ('QUANTITY','AMOUNT')

and POD.PJC_PROJECT_ID IS NOT NULL

AND CAT.EXPENDITURE_CATEGORY_ID = ET.EXPENDITURE_CATEGORY_ID

AND O.CLASSIFICATION_CODE = 'PA_EXPENDITURE_ORG'

AND NVL(POD.PJC_EXPENDITURE_ITEM_DATE, TRUNC(SYSDATE)) BETWEEN NVL(O.EFFECTIVE_START_DATE,TRUNC(SYSDATE)) AND NVL(O.EFFECTIVE_END_DATE,TRUNC(SYSDATE))

AND ((pll.matching_basis = 'AMOUNT' AND pod.amount_ordered <> 0) or (pll.matching_basis = 'QUANTITY' and pod.quantity_ordered <> 0))