eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PA_PROJ_PO_DISTRIBUTIONS

Source

View Text - Preformatted


SELECT POH.SEGMENT1 , POH.REVISION_NUM , POR.RELEASE_NUM , POR.REVISION_NUM , DECODE(POR.RELEASE_NUM,NULL,DECODE(POH.AUTHORIZATION_STATUS, 'APPROVED','Y','N'),DECODE(POR.AUTHORIZATION_STATUS,'APPROVED','Y', 'N')) , DECODE(POD.REQ_DISTRIBUTION_ID,NULL,DECODE(POR.RELEASE_NUM,NULL, DECODE(POH.APPROVED_DATE,NULL,'N','Y'),DECODE(POR.APPROVED_DATE, NULL,'N','Y')),'Y') , PDT.TYPE_NAME , DECODE(POR.RELEASE_NUM,NULL,POH.CREATION_DATE,POR.CREATION_DATE) , DECODE(POR.RELEASE_NUM,NULL,POH.APPROVED_DATE,POR.APPROVED_DATE) , DECODE(POR.RELEASE_NUM,NULL,POH.PRINTED_DATE,POR.PRINTED_DATE) , REQ.PERSON_ID , REQ.FULL_NAME , BUY.PERSON_ID , BUY.FULL_NAME , V.VENDOR_NAME , V.VENDOR_ID , POL.LINE_NUM , POL.ITEM_DESCRIPTION , DECODE(PLL.VALUE_BASIS,'AMOUNT',NULL, POL.UNIT_MEAS_LOOKUP_CODE) , TO_NUMBER(DECODE(PLL.VALUE_BASIS,'AMOUNT',NULL, decode( nvl(pod.rate, '-99'), '-99',pa_multi_currency.convert_amount_sql(POH.CURRENCY_CODE, G.CURRENCY_CODE, POD.RATE_DATE, POH.RATE_TYPE, NVL(POD.RATE,1), PLL.PRICE_OVERRIDE ), (pod.rate * PLL.PRICE_OVERRIDE) ) )) , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', (pod.amount_ordered - NVL (pod.amount_cancelled, 0)), 'QUANTITY', ( pod.quantity_ordered - NVL (pod.quantity_cancelled, 0)) ) , decode( nvl(pod.rate, '-99'), '-99', DECODE(NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pa_multi_currency.convert_amount_sql (poh.currency_code, g.currency_code, pod.rate_date, poh.rate_type, NVL (pod.rate, 1), ( pod.amount_ordered - NVL (pod.amount_cancelled, 0 ) ) ), 'QUANTITY', pa_multi_currency.convert_amount_sql (poh.currency_code, g.currency_code, pod.rate_date, poh.rate_type, NVL (pod.rate, 1), ( pod.quantity_ordered - NVL (pod.quantity_cancelled, 0 ) ) * (pll.price_override) ) ) , PA_CURRENCY.round_trans_currency_amt(DECODE (NVL (lt.matching_basis, 'QUANTITY'), 'AMOUNT', (pod.rate * (POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED,0))), 'QUANTITY', (pod.rate * (POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED,0)) * (pll.price_override)) ),poh.currency_code) ) , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pod.amount_ordered, 'QUANTITY', pod.quantity_ordered ) , decode( nvl(pod.rate, '-99'), '-99', DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pa_multi_currency.convert_amount_sql (poh.currency_code, g.currency_code, pod.rate_date, poh.rate_type, NVL (pod.rate, 1), pod.amount_ordered ), 'QUANTITY', pa_multi_currency.convert_amount_sql (poh.currency_code, g.currency_code, pod.rate_date, poh.rate_type, NVL (pod.rate, 1), pod.quantity_ordered * (pll.price_override ) ) ), PA_CURRENCY.round_trans_currency_amt(DECODE (NVL (lt.matching_basis, 'QUANTITY'), 'AMOUNT', (pod.rate * POD.AMOUNT_ORDERED), 'QUANTITY', (pod.rate * POD.QUANTITY_ORDERED * (pll.price_override)) ),poh.currency_code) ) , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pod.amount_cancelled, 'QUANTITY', pod.quantity_cancelled ) , decode( nvl(pod.rate, '-99'), '-99', DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pa_multi_currency.convert_amount_sql (poh.currency_code, g.currency_code, pod.rate_date, poh.rate_type, NVL (pod.rate, 1), pod.amount_cancelled ), 'QUANTITY', pa_multi_currency.convert_amount_sql (poh.currency_code, g.currency_code, pod.rate_date, poh.rate_type, NVL (pod.rate, 1), pod.quantity_cancelled * (pll.price_override) ) ), PA_CURRENCY.round_trans_currency_amt(DECODE (NVL (lt.matching_basis, 'QUANTITY'), 'AMOUNT', (pod.rate * POD.AMOUNT_CANCELLED), 'QUANTITY', (pod.rate * POD.QUANTITY_CANCELLED * (pll.price_override)) ),poh.currency_code) ) , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pod.amount_delivered, 'QUANTITY', pod.quantity_delivered ) , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pod.amount_delivered, 'QUANTITY', pod.quantity_delivered * (pll.price_override) ) , decode( nvl(pod.rate, '-99'), '-99', DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pa_multi_currency.convert_amount_sql (poh.currency_code, g.currency_code, pod.rate_date, poh.rate_type, NVL (pod.rate, 1), pod.amount_delivered ), 'QUANTITY', pa_multi_currency.convert_amount_sql (poh.currency_code, g.currency_code, pod.rate_date, poh.rate_type, NVL (pod.rate, 1), pod.quantity_delivered * (pll.price_override) ) ), PA_CURRENCY.round_trans_currency_amt(DECODE (NVL (lt.matching_basis, 'QUANTITY'), 'AMOUNT', (pod.rate * POD.AMOUNT_DELIVERED), 'QUANTITY', (pod.rate * POD.QUANTITY_DELIVERED * (pll.price_override)) ),poh.currency_code) ) , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', NVL (pod.amount_billed, 0), 'QUANTITY', NVL (pod.quantity_billed, 0) ) , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', NVL (pod.amount_billed, 0), 'QUANTITY', NVL (pod.amount_billed, 0) ) , 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) ) ) , decode( nvl(pod.rate, '-99'), '-99', DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pa_multi_currency.convert_amount_sql (poh.currency_code, g.currency_code, pod.rate_date, poh.rate_type, NVL (pod.rate, 1), ( pod.amount_ordered - NVL (pod.amount_cancelled, 0 ) - NVL (pod.amount_delivered, 0 ) ) ), 'QUANTITY', pa_multi_currency.convert_amount_sql (poh.currency_code, g.currency_code, pod.rate_date, poh.rate_type, NVL (pod.rate, 1), ( pod.quantity_ordered - NVL (pod.quantity_cancelled, 0 ) - NVL (pod.quantity_delivered, 0 ) ) * (pll.price_override) ) ), PA_CURRENCY.round_trans_currency_amt(DECODE (NVL (lt.matching_basis, 'QUANTITY'), 'AMOUNT', (pod.rate * (POD.AMOUNT_ORDERED-NVL(POD.AMOUNT_CANCELLED,0) -NVL(POD.AMOUNT_DELIVERED,0))), 'QUANTITY', (pod.rate * (POD.QUANTITY_ORDERED-NVL(POD.QUANTITY_CANCELLED,0) -NVL(POD.QUANTITY_DELIVERED,0))* (pll.price_override)) ),poh.currency_code) ) , 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) ) ) ) , POH.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 ) ) ) , G.CURRENCY_CODE , decode( nvl(pod.rate, '-99'), '-99', DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pa_multi_currency.convert_amount_sql (poh.currency_code, g.currency_code, pod.rate_date, poh.rate_type, NVL (pod.rate, 1), 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', pa_multi_currency.convert_amount_sql (poh.currency_code, g.currency_code, pod.rate_date, poh.rate_type, NVL (pod.rate, 1), 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 ) ) ) ), PA_CURRENCY.round_trans_currency_amt(DECODE (NVL (lt.matching_basis, 'QUANTITY'), 'AMOUNT', (pod.rate * 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', (pod.rate * 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))) ),poh.currency_code) ) , decode(NVL(POH.CURRENCY_CODE,'-99'),NVL(G.CURRENCY_CODE,'-99'),TO_DATE(NULL),POD.RATE_DATE) , decode(NVL(POH.CURRENCY_CODE,'-99'),NVL(G.CURRENCY_CODE,'-99'),NULL,POH.RATE_TYPE) , decode(NVL(POH.CURRENCY_CODE,'-99'),NVL(G.CURRENCY_CODE,'-99'),TO_NUMBER(NULL),POD.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) ) ) ) , 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)) ) , P.SEGMENT1 , P.NAME , T.TASK_NUMBER , T.TASK_NAME , POD.EXPENDITURE_TYPE , ET.EXPENDITURE_CATEGORY , ET.REVENUE_CATEGORY_CODE , POD.EXPENDITURE_ITEM_DATE , O.NAME , POH.PO_HEADER_ID , POR.PO_RELEASE_ID , POL.PO_LINE_ID , PLL.LINE_LOCATION_ID , POD.PO_DISTRIBUTION_ID , P.PROJECT_ID , T.TASK_ID , O.ORGANIZATION_ID , PLL.PROMISED_DATE , PLL.NEED_BY_DATE , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', GREATEST (0, (pa_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.project_id, pod.task_id, pod.code_combination_id, 0, NULL, NULL, pll.matching_basis, NVL (pod.nonrecoverable_tax, 0 ), nvl(g.sla_ledger_cash_basis_flag,'N'), pod.accrue_on_receipt_flag ) ) ), 'QUANTITY', GREATEST (0, (pa_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.project_id, pod.task_id, pod.code_combination_id, 0, NULL, NULL, pll.matching_basis, NULL, nvl(g.sla_ledger_cash_basis_flag,'N'), pod.accrue_on_receipt_flag ) ) ) ) cmt_qty , DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', GREATEST (0, (pa_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.project_id, pod.task_id, pod.code_combination_id, 0, NULL, NULL, pll.matching_basis, NVL (pod.nonrecoverable_tax, 0 ), nvl(g.sla_ledger_cash_basis_flag,'N'), pod.accrue_on_receipt_flag ) ) ), 'QUANTITY', GREATEST (0, (pa_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.project_id, pod.task_id, pod.code_combination_id, 0, NULL, NULL, pll.matching_basis, NULL, nvl(g.sla_ledger_cash_basis_flag,'N'), pod.accrue_on_receipt_flag ) ) ) * ( (pll.price_override) + (NVL (pod.nonrecoverable_tax, 0) / pod.quantity_ordered) ) ) denom_raw_cost , decode( nvl(pod.rate, '-99'), '-99', DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', pa_multi_currency.convert_amount_sql (poh.currency_code, g.currency_code, pod.rate_date, poh.rate_type, NVL (pod.rate, 1), GREATEST (0, (pa_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.project_id, pod.task_id, pod.code_combination_id, 0, NULL, NULL, pll.matching_basis, NVL (pod.nonrecoverable_tax, 0 ), nvl(g.sla_ledger_cash_basis_flag,'N'), pod.accrue_on_receipt_flag ) ) ) ), 'QUANTITY', pa_multi_currency.convert_amount_sql (poh.currency_code, g.currency_code, pod.rate_date, poh.rate_type, NVL (pod.rate, 1), GREATEST (0, (pa_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.project_id, pod.task_id, pod.code_combination_id, 0, NULL, NULL, pll.matching_basis, NULL, nvl(g.sla_ledger_cash_basis_flag,'N'), pod.accrue_on_receipt_flag ) ) ) * ( (pll.price_override) + (NVL (pod.nonrecoverable_tax, 0) / pod.quantity_ordered ) ) ) ), PA_CURRENCY.round_trans_currency_amt(DECODE (NVL (lt.matching_basis, 'QUANTITY'), 'AMOUNT', (pod.rate * GREATEST(0,(PA_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.project_id, pod.task_id, pod.code_combination_id, 0, NULL, NULL, PLL.MATCHING_BASIS, NVL(POD.NONRECOVERABLE_TAX,0), nvl(g.sla_ledger_cash_basis_flag,'N'), pod.accrue_on_receipt_flag )))), 'QUANTITY', (pod.rate * GREATEST(0,(PA_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.project_id, pod.task_id, pod.code_combination_id, 0, NULL, NULL, PLL.MATCHING_BASIS, NVL(POD.NONRECOVERABLE_TAX,0), nvl(g.sla_ledger_cash_basis_flag,'N'), pod.accrue_on_receipt_flag ))* ( (pll.price_override) + (NVL (pod.nonrecoverable_tax, 0) / pod.quantity_ordered)))) ),poh.currency_code) ) ,DECODE (NVL (pll.matching_basis, 'QUANTITY'), 'AMOUNT', DECODE (PLL.VALUE_BASIS, 'RATE', DECODE (pa_pjc_cwk_utils.is_cwk_tc_xface_allowed (p.project_id), 'Y', 'PEOPLE', 'FINANCIAL_ELEMENTS' ), 'FINANCIAL_ELEMENTS' ), 'QUANTITY', 'FINANCIAL_ELEMENTS' ) ,PDT.ORG_ID ,POD.award_id award_set_id FROM GL_LEDGERS g, PO_HEADERS_ALL POH, PO_RELEASES_ALL POR, PO_DOCUMENT_TYPES_ALL_TL PDT, PO_VENDORS V, PO_LINES_ALL POL, PO_LINE_TYPES LT, PO_LINE_LOCATIONS_ALL PLL, PER_ALL_PEOPLE_F BUY, PER_ALL_PEOPLE_F REQ, HR_ORGANIZATION_UNITS O, PA_EXPENDITURE_TYPES ET, PA_TASKS T, PO_DISTRIBUTIONS_ALL POD, PA_PROJECTS_ALL P 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 NVL(POH.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED') AND NVL(PLL.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED') AND PLL.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED') AND POH.TYPE_LOOKUP_CODE IN ('STANDARD','BLANKET','PLANNED') AND POH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE AND PDT.DOCUMENT_TYPE_CODE IN ('PO','PA') AND PDT.ORG_ID = POH.ORG_ID AND PDT.LANGUAGE = USERENV('LANG') AND PLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+) AND NVL(POH.CANCEL_FLAG,'N') = 'N' AND DECODE(POR.RELEASE_NUM,NULL,'OPEN',NVL(POR.CLOSED_CODE,'OPEN')) NOT IN ('FINALLY CLOSED') AND DECODE(POR.RELEASE_NUM,NULL,'N',NVL(POR.CANCEL_FLAG,'N')) = 'N' 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.PROJECT_ID = P.PROJECT_ID AND POD.TASK_ID = T.TASK_ID AND POD.EXPENDITURE_ORGANIZATION_ID = O.ORGANIZATION_ID AND POD.EXPENDITURE_TYPE = ET.EXPENDITURE_TYPE 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.PROJECT_ID IS NOT NULL

View Text - HTML Formatted

SELECT POH.SEGMENT1
, POH.REVISION_NUM
, POR.RELEASE_NUM
, POR.REVISION_NUM
, DECODE(POR.RELEASE_NUM
, NULL
, DECODE(POH.AUTHORIZATION_STATUS
, 'APPROVED'
, 'Y'
, 'N')
, DECODE(POR.AUTHORIZATION_STATUS
, 'APPROVED'
, 'Y'
, 'N'))
, DECODE(POD.REQ_DISTRIBUTION_ID
, NULL
, DECODE(POR.RELEASE_NUM
, NULL
, DECODE(POH.APPROVED_DATE
, NULL
, 'N'
, 'Y')
, DECODE(POR.APPROVED_DATE
, NULL
, 'N'
, 'Y'))
, 'Y')
, PDT.TYPE_NAME
, DECODE(POR.RELEASE_NUM
, NULL
, POH.CREATION_DATE
, POR.CREATION_DATE)
, DECODE(POR.RELEASE_NUM
, NULL
, POH.APPROVED_DATE
, POR.APPROVED_DATE)
, DECODE(POR.RELEASE_NUM
, NULL
, POH.PRINTED_DATE
, POR.PRINTED_DATE)
, REQ.PERSON_ID
, REQ.FULL_NAME
, BUY.PERSON_ID
, BUY.FULL_NAME
, V.VENDOR_NAME
, V.VENDOR_ID
, POL.LINE_NUM
, POL.ITEM_DESCRIPTION
, DECODE(PLL.VALUE_BASIS
, 'AMOUNT'
, NULL
, POL.UNIT_MEAS_LOOKUP_CODE)
, TO_NUMBER(DECODE(PLL.VALUE_BASIS
, 'AMOUNT'
, NULL
, DECODE( NVL(POD.RATE
, '-99')
, '-99'
, PA_MULTI_CURRENCY.CONVERT_AMOUNT_SQL(POH.CURRENCY_CODE
, G.CURRENCY_CODE
, POD.RATE_DATE
, POH.RATE_TYPE
, NVL(POD.RATE
, 1)
, PLL.PRICE_OVERRIDE )
, (POD.RATE * PLL.PRICE_OVERRIDE) ) ))
, DECODE (NVL (PLL.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, (POD.AMOUNT_ORDERED - NVL (POD.AMOUNT_CANCELLED
, 0))
, 'QUANTITY'
, ( POD.QUANTITY_ORDERED - NVL (POD.QUANTITY_CANCELLED
, 0)) )
, DECODE( NVL(POD.RATE
, '-99')
, '-99'
, DECODE(NVL (PLL.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, PA_MULTI_CURRENCY.CONVERT_AMOUNT_SQL (POH.CURRENCY_CODE
, G.CURRENCY_CODE
, POD.RATE_DATE
, POH.RATE_TYPE
, NVL (POD.RATE
, 1)
, ( POD.AMOUNT_ORDERED - NVL (POD.AMOUNT_CANCELLED
, 0 ) ) )
, 'QUANTITY'
, PA_MULTI_CURRENCY.CONVERT_AMOUNT_SQL (POH.CURRENCY_CODE
, G.CURRENCY_CODE
, POD.RATE_DATE
, POH.RATE_TYPE
, NVL (POD.RATE
, 1)
, ( POD.QUANTITY_ORDERED - NVL (POD.QUANTITY_CANCELLED
, 0 ) ) * (PLL.PRICE_OVERRIDE) ) )
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(DECODE (NVL (LT.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, (POD.RATE * (POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED
, 0)))
, 'QUANTITY'
, (POD.RATE * (POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED
, 0)) * (PLL.PRICE_OVERRIDE)) )
, POH.CURRENCY_CODE) )
, DECODE (NVL (PLL.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, POD.AMOUNT_ORDERED
, 'QUANTITY'
, POD.QUANTITY_ORDERED )
, DECODE( NVL(POD.RATE
, '-99')
, '-99'
, DECODE (NVL (PLL.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, PA_MULTI_CURRENCY.CONVERT_AMOUNT_SQL (POH.CURRENCY_CODE
, G.CURRENCY_CODE
, POD.RATE_DATE
, POH.RATE_TYPE
, NVL (POD.RATE
, 1)
, POD.AMOUNT_ORDERED )
, 'QUANTITY'
, PA_MULTI_CURRENCY.CONVERT_AMOUNT_SQL (POH.CURRENCY_CODE
, G.CURRENCY_CODE
, POD.RATE_DATE
, POH.RATE_TYPE
, NVL (POD.RATE
, 1)
, POD.QUANTITY_ORDERED * (PLL.PRICE_OVERRIDE ) ) )
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(DECODE (NVL (LT.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, (POD.RATE * POD.AMOUNT_ORDERED)
, 'QUANTITY'
, (POD.RATE * POD.QUANTITY_ORDERED * (PLL.PRICE_OVERRIDE)) )
, POH.CURRENCY_CODE) )
, DECODE (NVL (PLL.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, POD.AMOUNT_CANCELLED
, 'QUANTITY'
, POD.QUANTITY_CANCELLED )
, DECODE( NVL(POD.RATE
, '-99')
, '-99'
, DECODE (NVL (PLL.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, PA_MULTI_CURRENCY.CONVERT_AMOUNT_SQL (POH.CURRENCY_CODE
, G.CURRENCY_CODE
, POD.RATE_DATE
, POH.RATE_TYPE
, NVL (POD.RATE
, 1)
, POD.AMOUNT_CANCELLED )
, 'QUANTITY'
, PA_MULTI_CURRENCY.CONVERT_AMOUNT_SQL (POH.CURRENCY_CODE
, G.CURRENCY_CODE
, POD.RATE_DATE
, POH.RATE_TYPE
, NVL (POD.RATE
, 1)
, POD.QUANTITY_CANCELLED * (PLL.PRICE_OVERRIDE) ) )
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(DECODE (NVL (LT.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, (POD.RATE * POD.AMOUNT_CANCELLED)
, 'QUANTITY'
, (POD.RATE * POD.QUANTITY_CANCELLED * (PLL.PRICE_OVERRIDE)) )
, POH.CURRENCY_CODE) )
, DECODE (NVL (PLL.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, POD.AMOUNT_DELIVERED
, 'QUANTITY'
, POD.QUANTITY_DELIVERED )
, DECODE (NVL (PLL.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, POD.AMOUNT_DELIVERED
, 'QUANTITY'
, POD.QUANTITY_DELIVERED * (PLL.PRICE_OVERRIDE) )
, DECODE( NVL(POD.RATE
, '-99')
, '-99'
, DECODE (NVL (PLL.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, PA_MULTI_CURRENCY.CONVERT_AMOUNT_SQL (POH.CURRENCY_CODE
, G.CURRENCY_CODE
, POD.RATE_DATE
, POH.RATE_TYPE
, NVL (POD.RATE
, 1)
, POD.AMOUNT_DELIVERED )
, 'QUANTITY'
, PA_MULTI_CURRENCY.CONVERT_AMOUNT_SQL (POH.CURRENCY_CODE
, G.CURRENCY_CODE
, POD.RATE_DATE
, POH.RATE_TYPE
, NVL (POD.RATE
, 1)
, POD.QUANTITY_DELIVERED * (PLL.PRICE_OVERRIDE) ) )
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(DECODE (NVL (LT.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, (POD.RATE * POD.AMOUNT_DELIVERED)
, 'QUANTITY'
, (POD.RATE * POD.QUANTITY_DELIVERED * (PLL.PRICE_OVERRIDE)) )
, POH.CURRENCY_CODE) )
, DECODE (NVL (PLL.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, NVL (POD.AMOUNT_BILLED
, 0)
, 'QUANTITY'
, NVL (POD.QUANTITY_BILLED
, 0) )
, DECODE (NVL (PLL.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, NVL (POD.AMOUNT_BILLED
, 0)
, 'QUANTITY'
, NVL (POD.AMOUNT_BILLED
, 0) )
, 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) ) )
, DECODE( NVL(POD.RATE
, '-99')
, '-99'
, DECODE (NVL (PLL.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, PA_MULTI_CURRENCY.CONVERT_AMOUNT_SQL (POH.CURRENCY_CODE
, G.CURRENCY_CODE
, POD.RATE_DATE
, POH.RATE_TYPE
, NVL (POD.RATE
, 1)
, ( POD.AMOUNT_ORDERED - NVL (POD.AMOUNT_CANCELLED
, 0 ) - NVL (POD.AMOUNT_DELIVERED
, 0 ) ) )
, 'QUANTITY'
, PA_MULTI_CURRENCY.CONVERT_AMOUNT_SQL (POH.CURRENCY_CODE
, G.CURRENCY_CODE
, POD.RATE_DATE
, POH.RATE_TYPE
, NVL (POD.RATE
, 1)
, ( POD.QUANTITY_ORDERED - NVL (POD.QUANTITY_CANCELLED
, 0 ) - NVL (POD.QUANTITY_DELIVERED
, 0 ) ) * (PLL.PRICE_OVERRIDE) ) )
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(DECODE (NVL (LT.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, (POD.RATE * (POD.AMOUNT_ORDERED-NVL(POD.AMOUNT_CANCELLED
, 0) -NVL(POD.AMOUNT_DELIVERED
, 0)))
, 'QUANTITY'
, (POD.RATE * (POD.QUANTITY_ORDERED-NVL(POD.QUANTITY_CANCELLED
, 0) -NVL(POD.QUANTITY_DELIVERED
, 0))* (PLL.PRICE_OVERRIDE)) )
, POH.CURRENCY_CODE) )
, 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) ) ) )
, POH.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 ) ) )
, G.CURRENCY_CODE
, DECODE( NVL(POD.RATE
, '-99')
, '-99'
, DECODE (NVL (PLL.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, PA_MULTI_CURRENCY.CONVERT_AMOUNT_SQL (POH.CURRENCY_CODE
, G.CURRENCY_CODE
, POD.RATE_DATE
, POH.RATE_TYPE
, NVL (POD.RATE
, 1)
, 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'
, PA_MULTI_CURRENCY.CONVERT_AMOUNT_SQL (POH.CURRENCY_CODE
, G.CURRENCY_CODE
, POD.RATE_DATE
, POH.RATE_TYPE
, NVL (POD.RATE
, 1)
, 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 ) ) ) )
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(DECODE (NVL (LT.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, (POD.RATE * 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'
, (POD.RATE * 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))) )
, POH.CURRENCY_CODE) )
, DECODE(NVL(POH.CURRENCY_CODE
, '-99')
, NVL(G.CURRENCY_CODE
, '-99')
, TO_DATE(NULL)
, POD.RATE_DATE)
, DECODE(NVL(POH.CURRENCY_CODE
, '-99')
, NVL(G.CURRENCY_CODE
, '-99')
, NULL
, POH.RATE_TYPE)
, DECODE(NVL(POH.CURRENCY_CODE
, '-99')
, NVL(G.CURRENCY_CODE
, '-99')
, TO_NUMBER(NULL)
, POD.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) ) ) )
, 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)) )
, P.SEGMENT1
, P.NAME
, T.TASK_NUMBER
, T.TASK_NAME
, POD.EXPENDITURE_TYPE
, ET.EXPENDITURE_CATEGORY
, ET.REVENUE_CATEGORY_CODE
, POD.EXPENDITURE_ITEM_DATE
, O.NAME
, POH.PO_HEADER_ID
, POR.PO_RELEASE_ID
, POL.PO_LINE_ID
, PLL.LINE_LOCATION_ID
, POD.PO_DISTRIBUTION_ID
, P.PROJECT_ID
, T.TASK_ID
, O.ORGANIZATION_ID
, PLL.PROMISED_DATE
, PLL.NEED_BY_DATE
, DECODE (NVL (PLL.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, GREATEST (0
, (PA_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.PROJECT_ID
, POD.TASK_ID
, POD.CODE_COMBINATION_ID
, 0
, NULL
, NULL
, PLL.MATCHING_BASIS
, NVL (POD.NONRECOVERABLE_TAX
, 0 )
, NVL(G.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')
, POD.ACCRUE_ON_RECEIPT_FLAG ) ) )
, 'QUANTITY'
, GREATEST (0
, (PA_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.PROJECT_ID
, POD.TASK_ID
, POD.CODE_COMBINATION_ID
, 0
, NULL
, NULL
, PLL.MATCHING_BASIS
, NULL
, NVL(G.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')
, POD.ACCRUE_ON_RECEIPT_FLAG ) ) ) ) CMT_QTY
, DECODE (NVL (PLL.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, GREATEST (0
, (PA_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.PROJECT_ID
, POD.TASK_ID
, POD.CODE_COMBINATION_ID
, 0
, NULL
, NULL
, PLL.MATCHING_BASIS
, NVL (POD.NONRECOVERABLE_TAX
, 0 )
, NVL(G.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')
, POD.ACCRUE_ON_RECEIPT_FLAG ) ) )
, 'QUANTITY'
, GREATEST (0
, (PA_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.PROJECT_ID
, POD.TASK_ID
, POD.CODE_COMBINATION_ID
, 0
, NULL
, NULL
, PLL.MATCHING_BASIS
, NULL
, NVL(G.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')
, POD.ACCRUE_ON_RECEIPT_FLAG ) ) ) * ( (PLL.PRICE_OVERRIDE) + (NVL (POD.NONRECOVERABLE_TAX
, 0) / POD.QUANTITY_ORDERED) ) ) DENOM_RAW_COST
, DECODE( NVL(POD.RATE
, '-99')
, '-99'
, DECODE (NVL (PLL.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, PA_MULTI_CURRENCY.CONVERT_AMOUNT_SQL (POH.CURRENCY_CODE
, G.CURRENCY_CODE
, POD.RATE_DATE
, POH.RATE_TYPE
, NVL (POD.RATE
, 1)
, GREATEST (0
, (PA_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.PROJECT_ID
, POD.TASK_ID
, POD.CODE_COMBINATION_ID
, 0
, NULL
, NULL
, PLL.MATCHING_BASIS
, NVL (POD.NONRECOVERABLE_TAX
, 0 )
, NVL(G.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')
, POD.ACCRUE_ON_RECEIPT_FLAG ) ) ) )
, 'QUANTITY'
, PA_MULTI_CURRENCY.CONVERT_AMOUNT_SQL (POH.CURRENCY_CODE
, G.CURRENCY_CODE
, POD.RATE_DATE
, POH.RATE_TYPE
, NVL (POD.RATE
, 1)
, GREATEST (0
, (PA_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.PROJECT_ID
, POD.TASK_ID
, POD.CODE_COMBINATION_ID
, 0
, NULL
, NULL
, PLL.MATCHING_BASIS
, NULL
, NVL(G.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')
, POD.ACCRUE_ON_RECEIPT_FLAG ) ) ) * ( (PLL.PRICE_OVERRIDE) + (NVL (POD.NONRECOVERABLE_TAX
, 0) / POD.QUANTITY_ORDERED ) ) ) )
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(DECODE (NVL (LT.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, (POD.RATE * GREATEST(0
, (PA_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.PROJECT_ID
, POD.TASK_ID
, POD.CODE_COMBINATION_ID
, 0
, NULL
, NULL
, PLL.MATCHING_BASIS
, NVL(POD.NONRECOVERABLE_TAX
, 0)
, NVL(G.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')
, POD.ACCRUE_ON_RECEIPT_FLAG ))))
, 'QUANTITY'
, (POD.RATE * GREATEST(0
, (PA_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.PROJECT_ID
, POD.TASK_ID
, POD.CODE_COMBINATION_ID
, 0
, NULL
, NULL
, PLL.MATCHING_BASIS
, NVL(POD.NONRECOVERABLE_TAX
, 0)
, NVL(G.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')
, POD.ACCRUE_ON_RECEIPT_FLAG ))* ( (PLL.PRICE_OVERRIDE) + (NVL (POD.NONRECOVERABLE_TAX
, 0) / POD.QUANTITY_ORDERED)))) )
, POH.CURRENCY_CODE) )
, DECODE (NVL (PLL.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, DECODE (PLL.VALUE_BASIS
, 'RATE'
, DECODE (PA_PJC_CWK_UTILS.IS_CWK_TC_XFACE_ALLOWED (P.PROJECT_ID)
, 'Y'
, 'PEOPLE'
, 'FINANCIAL_ELEMENTS' )
, 'FINANCIAL_ELEMENTS' )
, 'QUANTITY'
, 'FINANCIAL_ELEMENTS' )
, PDT.ORG_ID
, POD.AWARD_ID AWARD_SET_ID
FROM GL_LEDGERS G
, PO_HEADERS_ALL POH
, PO_RELEASES_ALL POR
, PO_DOCUMENT_TYPES_ALL_TL PDT
, PO_VENDORS V
, PO_LINES_ALL POL
, PO_LINE_TYPES LT
, PO_LINE_LOCATIONS_ALL PLL
, PER_ALL_PEOPLE_F BUY
, PER_ALL_PEOPLE_F REQ
, HR_ORGANIZATION_UNITS O
, PA_EXPENDITURE_TYPES ET
, PA_TASKS T
, PO_DISTRIBUTIONS_ALL POD
, PA_PROJECTS_ALL P
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 NVL(POH.CLOSED_CODE
, 'OPEN') NOT IN ('FINALLY CLOSED')
AND NVL(PLL.CLOSED_CODE
, 'OPEN') NOT IN ('FINALLY CLOSED')
AND PLL.SHIPMENT_TYPE IN ('STANDARD'
, 'BLANKET'
, 'SCHEDULED')
AND POH.TYPE_LOOKUP_CODE IN ('STANDARD'
, 'BLANKET'
, 'PLANNED')
AND POH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
AND PDT.DOCUMENT_TYPE_CODE IN ('PO'
, 'PA')
AND PDT.ORG_ID = POH.ORG_ID
AND PDT.LANGUAGE = USERENV('LANG')
AND PLL.PO_RELEASE_ID = POR.PO_RELEASE_ID(+)
AND NVL(POH.CANCEL_FLAG
, 'N') = 'N'
AND DECODE(POR.RELEASE_NUM
, NULL
, 'OPEN'
, NVL(POR.CLOSED_CODE
, 'OPEN')) NOT IN ('FINALLY CLOSED')
AND DECODE(POR.RELEASE_NUM
, NULL
, 'N'
, NVL(POR.CANCEL_FLAG
, 'N')) = 'N'
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.PROJECT_ID = P.PROJECT_ID
AND POD.TASK_ID = T.TASK_ID
AND POD.EXPENDITURE_ORGANIZATION_ID = O.ORGANIZATION_ID
AND POD.EXPENDITURE_TYPE = ET.EXPENDITURE_TYPE
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.PROJECT_ID IS NOT NULL