View: PA_PROJ_PO_DISTRIBUTIONS_V

Product:	PA - Projects
Description:
Implementation/DBA Data:	APPS.PA_PROJ_PO_DISTRIBUTIONS_V

View Text

SELECT /*+ LEADING(POD) USE_NL(POD
, PLL
, PLT
, P
, T
, ET
, G) */ 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)
, POD.DELIVER_TO_PERSON_ID
, (SELECT REQ.FULL_NAME
FROM PER_ALL_PEOPLE_F REQ
WHERE REQ.PERSON_ID =POD.DELIVER_TO_PERSON_ID AND TRUNC(SYSDATE) BETWEEN NVL(REQ.EFFECTIVE_START_DATE
, TRUNC(SYSDATE))
AND NVL(REQ.EFFECTIVE_END_DATE
, TRUNC(SYSDATE)))
, POH.AGENT_ID
, (SELECT BUY.FULL_NAME
FROM PER_ALL_PEOPLE_F BUY
WHERE BUY.PERSON_ID = POH.AGENT_ID AND TRUNC(SYSDATE) BETWEEN BUY.EFFECTIVE_START_DATE
AND BUY.EFFECTIVE_END_DATE)
, (SELECT V.VENDOR_NAME
FROM PO_VENDORS V
WHERE V.VENDOR_ID =POH.VENDOR_ID)
, POH.VENDOR_ID
, POL.LINE_NUM
, POL.ITEM_DESCRIPTION
, DECODE(LT.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, POL.UNIT_MEAS_LOOKUP_CODE)
, TO_NUMBER(DECODE(LT.ORDER_TYPE_LOOKUP_CODE
, '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 (LT.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'
, PA_MULTI_CURRENCY.CONVERT_AMOUNT_SQL(POH.CURRENCY_CODE
, G.CURRENCY_CODE
, POD.RATE_DATE
, POH.RATE_TYPE
, NVL(POD.RATE
, 1)
, DECODE (NVL (LT.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, (POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED
, 0))
, 'QUANTITY'
, (POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED
, 0))* PLL.PRICE_OVERRIDE ))
, (POD.RATE * (DECODE (NVL (LT.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, (POD.AMOUNT_ORDERED - NVL(POD.AMOUNT_CANCELLED
, 0))
, 'QUANTITY'
, (POD.QUANTITY_ORDERED - NVL(POD.QUANTITY_CANCELLED
, 0))* PLL.PRICE_OVERRIDE )) ))
, DECODE (NVL (LT.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, POD.AMOUNT_ORDERED
, 'QUANTITY'
, 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)
, DECODE (NVL (LT.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, POD.AMOUNT_ORDERED
, 'QUANTITY'
, (POD.QUANTITY_ORDERED * PLL.PRICE_OVERRIDE)) )
, (POD.RATE * DECODE (NVL (LT.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, POD.AMOUNT_ORDERED
, 'QUANTITY'
, (POD.QUANTITY_ORDERED * PLL.PRICE_OVERRIDE))) )
, DECODE (NVL (LT.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, POD.AMOUNT_CANCELLED
, 'QUANTITY'
, 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)
, DECODE (NVL (LT.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, POD.AMOUNT_CANCELLED
, 'QUANTITY'
, (POD.QUANTITY_CANCELLED * PLL.PRICE_OVERRIDE)) )
, (POD.RATE * DECODE (NVL (LT.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, POD.AMOUNT_CANCELLED
, 'QUANTITY'
, (POD.QUANTITY_CANCELLED * PLL.PRICE_OVERRIDE))) )
, DECODE (NVL (LT.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, POD.AMOUNT_DELIVERED
, 'QUANTITY'
, POD.QUANTITY_DELIVERED )
, DECODE (NVL (LT.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, POD.AMOUNT_DELIVERED
, 'QUANTITY'
, (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)
, DECODE (NVL (LT.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, POD.AMOUNT_DELIVERED
, 'QUANTITY'
, (POD.QUANTITY_DELIVERED * PLL.PRICE_OVERRIDE)) )
, (POD.RATE * DECODE (NVL (LT.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, POD.AMOUNT_DELIVERED
, 'QUANTITY'
, (POD.QUANTITY_DELIVERED * PLL.PRICE_OVERRIDE))) )
, DECODE (NVL (LT.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, POD.AMOUNT_BILLED
, 'QUANTITY'
, POD.QUANTITY_BILLED )
, NVL(POD.AMOUNT_BILLED
, 0)
, DECODE (NVL (LT.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'
, PA_MULTI_CURRENCY.CONVERT_AMOUNT_SQL(POH.CURRENCY_CODE
, G.CURRENCY_CODE
, POD.RATE_DATE
, POH.RATE_TYPE
, NVL(POD.RATE
, 1)
, DECODE (NVL (LT.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))*(PLL.PRICE_OVERRIDE)))
, (POD.RATE * DECODE (NVL (LT.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))*(PLL.PRICE_OVERRIDE))))
, DECODE (NVL (LT.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 (LT.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'
, PA_MULTI_CURRENCY.CONVERT_AMOUNT_SQL(POH.CURRENCY_CODE
, G.CURRENCY_CODE
, POD.RATE_DATE
, POH.RATE_TYPE
, NVL(POD.RATE
, 1)
, DECODE (NVL (LT.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))))
, (POD.RATE * DECODE (NVL (LT.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)))) )
, 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 (LT.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 (LT.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
, POH.PO_HEADER_ID
, POR.PO_RELEASE_ID
, POL.PO_LINE_ID
, PLL.LINE_LOCATION_ID
, POD.PO_DISTRIBUTION_ID
, POD.PROJECT_ID
, POD.TASK_ID
, POD.EXPENDITURE_ORGANIZATION_ID
, PLL.PROMISED_DATE
, PLL.NEED_BY_DATE
, DECODE (NVL (LT.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'
, POD.PO_LINE_ID
, P.PROJECT_ID
, T.TASK_ID
, POD.PO_DISTRIBUTION_ID
, 0
, NULL
, NULL)))
, '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'
, POD.PO_LINE_ID
, P.PROJECT_ID
, T.TASK_ID
, POD.PO_DISTRIBUTION_ID
, 0
, NULL
, NULL)))) CMT_QTY
, DECODE (NVL (LT.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'
, POD.PO_LINE_ID
, P.PROJECT_ID
, T.TASK_ID
, POD.PO_DISTRIBUTION_ID
, 0
, NULL
, NULL))) * ( 1 + (NVL(POD.NONRECOVERABLE_TAX
, 0) / POD.AMOUNT_ORDERED))
, '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'
, POD.PO_LINE_ID
, P.PROJECT_ID
, T.TASK_ID
, POD.PO_DISTRIBUTION_ID
, 0
, NULL
, NULL))) * ((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)
, DECODE (NVL (LT.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'
, POD.PO_LINE_ID
, P.PROJECT_ID
, T.TASK_ID
, POD.PO_DISTRIBUTION_ID
, 0
, NULL
, NULL))) * ( 1 + (NVL(POD.NONRECOVERABLE_TAX
, 0) / POD.AMOUNT_ORDERED))
, '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'
, POD.PO_LINE_ID
, P.PROJECT_ID
, T.TASK_ID
, POD.PO_DISTRIBUTION_ID
, 0
, NULL
, NULL))) * ((PLL.PRICE_OVERRIDE) + (NVL(POD.NONRECOVERABLE_TAX
, 0) / POD.QUANTITY_ORDERED))))
, (POD.RATE * DECODE (NVL (LT.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'
, POD.PO_LINE_ID
, P.PROJECT_ID
, T.TASK_ID
, POD.PO_DISTRIBUTION_ID
, 0
, NULL
, NULL))) * ( 1 + (NVL(POD.NONRECOVERABLE_TAX
, 0) / POD.AMOUNT_ORDERED))
, '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'
, POD.PO_LINE_ID
, P.PROJECT_ID
, T.TASK_ID
, POD.PO_DISTRIBUTION_ID
, 0
, NULL
, NULL))) * ((PLL.PRICE_OVERRIDE) + (NVL(POD.NONRECOVERABLE_TAX
, 0) / POD.QUANTITY_ORDERED)))) )
, 'FINANCIAL_ELEMENTS'
FROM GL_SETS_OF_BOOKS G
, PO_HEADERS_ALL POH
, PO_RELEASES_ALL POR
, PO_DOCUMENT_TYPES PDT
, PO_LINES_ALL POL
, PO_LINE_TYPES LT
, PO_LINE_LOCATIONS_ALL PLL
, PA_EXPENDITURE_TYPES ET
, PA_TASKS T
, PO_DISTRIBUTIONS_ALL POD
, PA_PROJECTS P
WHERE 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 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 T.PROJECT_ID = P.PROJECT_ID
AND POD.TASK_ID = T.TASK_ID
AND POD.EXPENDITURE_TYPE = ET.EXPENDITURE_TYPE
AND POD.DESTINATION_TYPE_CODE = 'EXPENSE'
AND G.SET_OF_BOOKS_ID = POD.SET_OF_BOOKS_ID
AND NVL(LT.MATCHING_BASIS
, 'QUANTITY') IN ('QUANTITY'
, 'AMOUNT')

Columns