VIEW: APPS.PA_PROJ_REQ_DISTRIBUTIONS_V

Source

View Text - Preformatted

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')

View Text - HTML Formatted

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')