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_REQ_DISTRIBUTIONS

Source

View Text - Preformatted


SELECT 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 , REQ.PERSON_ID , REQ.FULL_NAME , 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 , O.NAME , RH.REQUISITION_HEADER_ID , RL.REQUISITION_LINE_ID , RD.DISTRIBUTION_ID , P.PROJECT_ID , T.TASK_ID , O.ORGANIZATION_ID , 'FINANCIAL_ELEMENTS' , PDT.ORG_ID , RD.award_id award_set_id , PTE.CBS_ELEMENT_ID FROM GL_LEDGERS G, PER_ALL_PEOPLE_F REQ, PO_DOCUMENT_TYPES_ALL_TL PDT, PO_LINE_TYPES LT, PO_REQUISITION_LINES_ALL RL, PO_REQUISITION_HEADERS_ALL RH, PA_TASKS T, PA_TASKS_EXPEND_V PTE, HR_ORGANIZATION_UNITS O, PA_EXPENDITURE_TYPES ET, PO_REQ_DISTRIBUTIONS_ALL RD, PA_PROJECTS_ALL P WHERE RH.REQUISITION_HEADER_ID = RL.REQUISITION_HEADER_ID AND RH.AUTHORIZATION_STATUS <> 'INCOMPLETE' AND RH.TYPE_LOOKUP_CODE = 'PURCHASE' AND PDT.DOCUMENT_TYPE_CODE = 'REQUISITION' AND RH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE AND PDT.ORG_ID = RH.ORG_ID AND PDT.LANGUAGE = USERENV('LANG') AND RL.LINE_LOCATION_ID IS NULL AND RH.REVISION_NUM 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 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)) 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 RD.PROJECT_ID = P.PROJECT_ID AND RD.TASK_ID = PTE.TASK_ID AND T.TASK_ID = PTE.ACTUAL_TASK_ID AND RD.EXPENDITURE_ORGANIZATION_ID = O.ORGANIZATION_ID AND RD.EXPENDITURE_TYPE = ET.EXPENDITURE_TYPE AND G.LEDGER_ID = RD.SET_OF_BOOKS_ID AND NVL(LT.MATCHING_BASIS,'QUANTITY') IN ('QUANTITY', 'AMOUNT')

View Text - HTML Formatted

SELECT 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
, REQ.PERSON_ID
, REQ.FULL_NAME
, 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
, O.NAME
, RH.REQUISITION_HEADER_ID
, RL.REQUISITION_LINE_ID
, RD.DISTRIBUTION_ID
, P.PROJECT_ID
, T.TASK_ID
, O.ORGANIZATION_ID
, 'FINANCIAL_ELEMENTS'
, PDT.ORG_ID
, RD.AWARD_ID AWARD_SET_ID
, PTE.CBS_ELEMENT_ID
FROM GL_LEDGERS G
, PER_ALL_PEOPLE_F REQ
, PO_DOCUMENT_TYPES_ALL_TL PDT
, PO_LINE_TYPES LT
, PO_REQUISITION_LINES_ALL RL
, PO_REQUISITION_HEADERS_ALL RH
, PA_TASKS T
, PA_TASKS_EXPEND_V PTE
, HR_ORGANIZATION_UNITS O
, PA_EXPENDITURE_TYPES ET
, PO_REQ_DISTRIBUTIONS_ALL RD
, PA_PROJECTS_ALL P
WHERE RH.REQUISITION_HEADER_ID = RL.REQUISITION_HEADER_ID
AND RH.AUTHORIZATION_STATUS <> 'INCOMPLETE'
AND RH.TYPE_LOOKUP_CODE = 'PURCHASE'
AND PDT.DOCUMENT_TYPE_CODE = 'REQUISITION'
AND RH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
AND PDT.ORG_ID = RH.ORG_ID
AND PDT.LANGUAGE = USERENV('LANG')
AND RL.LINE_LOCATION_ID IS NULL
AND RH.REVISION_NUM 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 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))
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 RD.PROJECT_ID = P.PROJECT_ID
AND RD.TASK_ID = PTE.TASK_ID
AND T.TASK_ID = PTE.ACTUAL_TASK_ID
AND RD.EXPENDITURE_ORGANIZATION_ID = O.ORGANIZATION_ID
AND RD.EXPENDITURE_TYPE = ET.EXPENDITURE_TYPE
AND G.LEDGER_ID = RD.SET_OF_BOOKS_ID
AND NVL(LT.MATCHING_BASIS
, 'QUANTITY') IN ('QUANTITY'
, 'AMOUNT')