View: PA_PROJ_REQ_DISTRIBUTIONS_V

Product:	PA - Projects
Description:	View that displays project-related distributions in Oracle Purchasing that have not yet been purchased as a purchase order.
Implementation/DBA Data:	APPS.PA_PROJ_REQ_DISTRIBUTIONS_V

View Text

SELECT /*+ ORDERED USE_NL(P
, RD
, RL) */ RH.SEGMENT1
, PDT.TYPE_NAME
, DECODE(NVL(RH.AUTHORIZATION_STATUS
, 'NOT APPROVED')
, 'APPROVED'
, 'Y'
, 'N')
, RL.LINE_NUM
, RL.ITEM_DESCRIPTION
, RL.NEED_BY_DATE
, RL.CREATION_DATE
, RL.SUGGESTED_VENDOR_NAME
, RL.VENDOR_ID
, RL.TO_PERSON_ID
, ( SELECT REQ.FULL_NAME
FROM PER_ALL_PEOPLE_F REQ
WHERE REQ.PERSON_ID = RL.TO_PERSON_ID
AND TRUNC(SYSDATE) BETWEEN NVL(REQ.EFFECTIVE_START_DATE
, TRUNC(SYSDATE))
AND NVL(REQ.EFFECTIVE_END_DATE
, TRUNC(SYSDATE)))
, NVL(RL.CURRENCY_CODE
, G.CURRENCY_CODE)
, DECODE (NVL (LT.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, PA_MULTI_CURRENCY.CONVERT_AMOUNT_SQL(G.CURRENCY_CODE
, NVL(RL.CURRENCY_CODE
, G.CURRENCY_CODE)
, RL.RATE_DATE
, RL.RATE_TYPE
, NVL(RL.RATE
, 1)
, (NVL(RD.REQ_LINE_AMOUNT
, 0)+ NVL(RD.NONRECOVERABLE_TAX
, 0)))
, 'QUANTITY'
, NVL(RL.CURRENCY_UNIT_PRICE
, RL.UNIT_PRICE)*RD.REQ_LINE_QUANTITY + PA_MULTI_CURRENCY.CONVERT_AMOUNT_SQL(G.CURRENCY_CODE
, NVL(RL.CURRENCY_CODE
, G.CURRENCY_CODE)
, RL.RATE_DATE
, RL.RATE_TYPE
, NVL(RL.RATE
, 1)
, NVL(RD.NONRECOVERABLE_TAX
, 0)))
, G.CURRENCY_CODE
, DECODE(NVL(RL.CURRENCY_CODE
, '-99')
, NVL(G.CURRENCY_CODE
, '-99')
, TO_DATE(NULL)
, RL.RATE_DATE)
, DECODE(NVL(RL.CURRENCY_CODE
, '-99')
, NVL(G.CURRENCY_CODE
, '-99')
, NULL
, RL.RATE_TYPE)
, DECODE(NVL(RL.CURRENCY_CODE
, '-99')
, NVL(G.CURRENCY_CODE
, '-99')
, TO_NUMBER(NULL)
, RL.RATE)
, DECODE(LT.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, RL.UNIT_MEAS_LOOKUP_CODE)
, TO_NUMBER(DECODE(LT.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, RL.UNIT_PRICE))
, RD.REQ_LINE_QUANTITY
, DECODE (NVL (LT.MATCHING_BASIS
, 'QUANTITY')
, 'AMOUNT'
, NVL(RD.REQ_LINE_AMOUNT
, 0) + NVL(RD.NONRECOVERABLE_TAX
, 0)
, 'QUANTITY'
, (RD.REQ_LINE_QUANTITY * RL.UNIT_PRICE) + NVL(RD.NONRECOVERABLE_TAX
, 0))
, P.SEGMENT1
, P.NAME
, T.TASK_NUMBER
, T.TASK_NAME
, RD.EXPENDITURE_TYPE
, ET.EXPENDITURE_CATEGORY
, ET.REVENUE_CATEGORY_CODE
, RD.EXPENDITURE_ITEM_DATE
, RH.REQUISITION_HEADER_ID
, RL.REQUISITION_LINE_ID
, RD.DISTRIBUTION_ID
, RD.PROJECT_ID
, RD.TASK_ID
, RD.EXPENDITURE_ORGANIZATION_ID
, 'FINANCIAL_ELEMENTS'
FROM PA_PROJECTS P
, PO_REQ_DISTRIBUTIONS_ALL RD
, PO_REQUISITION_LINES_ALL RL
, GL_SETS_OF_BOOKS G
, PO_DOCUMENT_TYPES PDT
, PO_LINE_TYPES LT
, PO_REQUISITION_HEADERS_ALL RH
, PA_TASKS T
, PA_EXPENDITURE_TYPES ET
WHERE RH.REQUISITION_HEADER_ID = RL.REQUISITION_HEADER_ID
AND RH.TYPE_LOOKUP_CODE = 'PURCHASE'
AND PDT.DOCUMENT_TYPE_CODE = 'REQUISITION'
AND RH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
AND RL.LINE_LOCATION_ID IS NULL
AND NVL(RL.CLOSED_CODE
, 'OPEN') <> 'FINALLY CLOSED'
AND NVL(RL.CANCEL_FLAG
, 'N') = 'N'
AND NVL(RL.MODIFIED_BY_AGENT_FLAG
, 'N') = 'N'
AND RL.SOURCE_TYPE_CODE = 'VENDOR'
AND RL.LINE_TYPE_ID = LT.LINE_TYPE_ID
AND RL.DESTINATION_TYPE_CODE = 'EXPENSE'
AND RD.REQUISITION_LINE_ID = RL.REQUISITION_LINE_ID
AND T.PROJECT_ID = P.PROJECT_ID
AND RD.TASK_ID = T.TASK_ID
AND RD.PROJECT_ID = T.PROJECT_ID
AND RD.EXPENDITURE_TYPE = ET.EXPENDITURE_TYPE
AND G.SET_OF_BOOKS_ID = RD.SET_OF_BOOKS_ID
AND NVL(LT.MATCHING_BASIS
, 'QUANTITY') IN ('AMOUNT'
, 'QUANTITY')

Columns