View: PA_PROJ_PO_DISTRIBUTIONS

Product:	PA - Projects
Description:	View for project-related uninvoiced purchase order distributions. The non-recoverable tax is added to the amount outstanding invoice values.
Implementation/DBA Data:	APPS.PA_PROJ_PO_DISTRIBUTIONS

View Text

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) ) ))
, (POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED
, 0))
, 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)
, (POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED
, 0)) )
, (POD.RATE * (POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED
, 0))) )
, POD.AMOUNT_ORDERED
, 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)
, POD.AMOUNT_ORDERED )
, (POD.RATE * POD.AMOUNT_ORDERED) )
, POD.AMOUNT_CANCELLED
, 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)
, POD.AMOUNT_CANCELLED )
, (POD.RATE * POD.AMOUNT_CANCELLED) )
, POD.AMOUNT_DELIVERED
, POD.AMOUNT_DELIVERED
, 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)
, POD.AMOUNT_DELIVERED )
, (POD.RATE * POD.AMOUNT_DELIVERED) )
, NVL(POD.AMOUNT_BILLED
, 0)
, NVL(POD.AMOUNT_BILLED
, 0)
, (POD.AMOUNT_ORDERED-NVL(POD.AMOUNT_CANCELLED
, 0) -NVL(POD.AMOUNT_DELIVERED
, 0))
, 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)
, (POD.AMOUNT_ORDERED-NVL(POD.AMOUNT_CANCELLED
, 0) -NVL(POD.AMOUNT_DELIVERED
, 0)))
, (POD.RATE * (POD.AMOUNT_ORDERED-NVL(POD.AMOUNT_CANCELLED
, 0) -NVL(POD.AMOUNT_DELIVERED
, 0))) )
, GREATEST(0
, (POD.AMOUNT_ORDERED-NVL(POD.AMOUNT_CANCELLED
, 0) -NVL(POD.AMOUNT_BILLED
, 0)))
, POH.CURRENCY_CODE
, GREATEST(0
, (POD.AMOUNT_ORDERED-NVL(POD.AMOUNT_CANCELLED
, 0) -NVL(POD.AMOUNT_BILLED
, 0))) * ( 1 + (NVL(POD.NONRECOVERABLE_TAX
, 0) / POD.AMOUNT_ORDERED))
, G.CURRENCY_CODE
, 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)
, GREATEST(0
, (POD.AMOUNT_ORDERED-NVL(POD.AMOUNT_CANCELLED
, 0) -NVL(POD.AMOUNT_BILLED
, 0))) * ( 1 + (NVL(POD.NONRECOVERABLE_TAX
, 0) / POD.AMOUNT_ORDERED)))
, (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))) )
, 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)
, GREATEST(0
, -1*(POD.AMOUNT_ORDERED-NVL(POD.AMOUNT_CANCELLED
, 0) -NVL(POD.AMOUNT_BILLED
, 0)))
, GREATEST(0
, -1*(POD.AMOUNT_ORDERED-NVL(POD.AMOUNT_CANCELLED
, 0) -NVL(POD.AMOUNT_BILLED
, 0)))
, 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
, 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 ))) CMT_QTY
, 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 ))) DENOM_RAW_COST
, 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)
, 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 ))))
, (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 ))) ) )
, DECODE(PLL.VALUE_BASIS
, 'RATE'
, DECODE(PA_PJC_CWK_UTILS.IS_CWK_TC_XFACE_ALLOWED(P.PROJECT_ID)
, 'Y'
, 'PEOPLE'
, 'FINANCIAL_ELEMENTS')
, '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 ('CLOSED'
, 'FINALLY CLOSED')
AND NVL(PLL.CLOSED_CODE
, 'OPEN') NOT IN ('CLOSED'
, '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 ('CLOSED'
, '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') = 'AMOUNT'
AND POD.PROJECT_ID IS NOT NULL UNION ALL 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) ) ))
, (POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED
, 0))
, 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)
, (POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED
, 0)) * ( PLL.PRICE_OVERRIDE ))
, (POD.RATE * (POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED
, 0)) * ( PLL.PRICE_OVERRIDE )) )
, POD.QUANTITY_ORDERED
, 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)
, POD.QUANTITY_ORDERED *( PLL.PRICE_OVERRIDE ))
, (POD.RATE * POD.QUANTITY_ORDERED *( PLL.PRICE_OVERRIDE )) )
, POD.QUANTITY_CANCELLED
, 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)
, POD.QUANTITY_CANCELLED *( PLL.PRICE_OVERRIDE ))
, (POD.RATE * POD.QUANTITY_CANCELLED *( PLL.PRICE_OVERRIDE )) )
, POD.QUANTITY_DELIVERED
, POD.QUANTITY_DELIVERED *( PLL.PRICE_OVERRIDE )
, 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)
, POD.QUANTITY_DELIVERED *( PLL.PRICE_OVERRIDE))
, (POD.RATE * POD.QUANTITY_DELIVERED *( PLL.PRICE_OVERRIDE)) )
, NVL(POD.QUANTITY_BILLED
, 0)
, NVL(POD.AMOUNT_BILLED
, 0)
, (POD.QUANTITY_ORDERED-NVL(POD.QUANTITY_CANCELLED
, 0) -NVL(POD.QUANTITY_DELIVERED
, 0))
, 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)
, (POD.QUANTITY_ORDERED-NVL(POD.QUANTITY_CANCELLED
, 0) -NVL(POD.QUANTITY_DELIVERED
, 0))*(PLL.PRICE_OVERRIDE))
, (POD.RATE * (POD.QUANTITY_ORDERED-NVL(POD.QUANTITY_CANCELLED
, 0) -NVL(POD.QUANTITY_DELIVERED
, 0))*(PLL.PRICE_OVERRIDE)) )
, GREATEST(0
, (POD.QUANTITY_ORDERED-NVL(POD.QUANTITY_CANCELLED
, 0) -NVL(POD.QUANTITY_BILLED
, 0)))
, POH.CURRENCY_CODE
, 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'
, 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)))
, (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))) )
, 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)
, GREATEST(0
, -1*(POD.QUANTITY_ORDERED-NVL(POD.QUANTITY_CANCELLED
, 0) -NVL(POD.QUANTITY_BILLED
, 0)))
, 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
, 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
, 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'
, 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)))
, (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
, 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)) ) )
, '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.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 POD.DISTRIBUTION_TYPE <> 'PREPAYMENT'
AND NVL(POH.CLOSED_CODE
, 'OPEN') NOT IN ('CLOSED'
, 'FINALLY CLOSED')
AND NVL(PLL.CLOSED_CODE
, 'OPEN') NOT IN ('CLOSED'
, '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 ('CLOSED'
, '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') <> 'AMOUNT'
AND POD.PROJECT_ID IS NOT NULL

Columns