[Home] [Help]
| Product: | PA - Projects |
|---|---|
| Description: | Expenditure data from Oracle Projects for Oracle Personal Time and Expense |
| Implementation/DBA Data: | Not implemented in this database |
SELECT E.EXPENDITURE_ID
, E.EXPENDITURE_ENDING_DATE
, E.EXPENDITURE_STATUS_CODE
, I.EXPENDITURE_ITEM_ID
, E.INCURRED_BY_PERSON_ID
, E.PTE_REFERENCE
, TO_NUMBER(SUBSTR(I.ORIG_TRANSACTION_REFERENCE
, 1
, INSTR(I.ORIG_TRANSACTION_REFERENCE
, ':')-1))
, DECODE(I.ADJUSTED_EXPENDITURE_ITEM_ID
, NULL
, NULL
, TO_NUMBER(SUBSTR(I2.ORIG_TRANSACTION_REFERENCE
, 1
, INSTR(I2.ORIG_TRANSACTION_REFERENCE
, ':')-1)))
, I.TRANSACTION_SOURCE
, I.QUANTITY
, I.EXPENDITURE_ITEM_DATE
, I.EXPENDITURE_TYPE
, R.ROUTING_COMMENT
, E.DESCRIPTION
, C.EXPENDITURE_COMMENT
, I.ATTRIBUTE1
, I.ATTRIBUTE2
, I.ATTRIBUTE3
, I.ATTRIBUTE4
, I.ATTRIBUTE5
, I.ATTRIBUTE6
, I.ATTRIBUTE7
, I.ATTRIBUTE8
, I.ATTRIBUTE9
, I.ATTRIBUTE10
, I.ATTRIBUTE_CATEGORY
, P.SEGMENT1
, T.TASK_NUMBER
FROM PA_EXPENDITURES E
, PA_EXPENDITURE_ITEMS I
, PA_EXPENDITURE_ITEMS I2
, PA_EXPENDITURE_COMMENTS C
, PA_PROJECTS P
, PA_TASKS T
, PA_ROUTINGS R
WHERE T.PROJECT_ID = P.PROJECT_ID
AND T.TASK_ID = I.TASK_ID
AND (R.START_DATE = (SELECT MAX(START_DATE)
FROM PA_ROUTINGS RR
WHERE RR.EXPENDITURE_ID = E.EXPENDITURE_ID) OR NOT EXISTS ( SELECT NULL
FROM PA_ROUTINGS RRR
WHERE RRR.EXPENDITURE_ID = E.EXPENDITURE_ID))
AND R.EXPENDITURE_ID (+) = E.EXPENDITURE_ID
AND I2.EXPENDITURE_ITEM_ID (+) = I.ADJUSTED_EXPENDITURE_ITEM_ID
AND C.LINE_NUMBER(+) = 10
AND I.EXPENDITURE_ITEM_ID = C.EXPENDITURE_ITEM_ID (+)
AND I.EXPENDITURE_ID = E.EXPENDITURE_ID
AND E.EXPENDITURE_STATUS_CODE IN ('SUBMITTED'
, 'APPROVED'
, 'REJECTED')
AND E.PTE_REFERENCE IS NOT NULL