View: PA_EXPENDITURES_ROUTINGS_V

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

View Text

SELECT EX.ROWID ROW_ID
, EX.EXPENDITURE_ID EXPENDITURE_ID
, EX.EXPENDITURE_STATUS_CODE EXPENDITURE_STATUS_CODE
, EX.EXPENDITURE_ENDING_DATE EXPENDITURE_ENDING_DATE
, EX.EXPENDITURE_CLASS_CODE EXPENDITURE_CLASS_CODE
, EX.INCURRED_BY_PERSON_ID INCURRED_BY_PERSON_ID
, EM.FULL_NAME FULL_NAME
, NVL( EM.EMPLOYEE_NUMBER
, EM.NPW_NUMBER ) EMPLOYEE_NUMBER
, RO.ROUTED_TO_PERSON_ID ROUTED_TO_PERSON_ID
, RO.ROUTING_COMMENT ROUTING_COMMENT
, RS.MEANING ROUTING_STATUS
, ES.MEANING EXPENDITURE_STATUS
, EX.DESCRIPTION DESCRIPTION
, EX.CREATED_BY CREATED_BY
, EX.CREATION_DATE CREATION_DATE
, EX.LAST_UPDATED_BY LAST_UPDATED_BY
, EX.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, EX.LAST_UPDATE_DATE LAST_UPDATE_DATE
, EX.INCURRED_BY_ORGANIZATION_ID INCURRED_BY_ORGANIZATION_ID
, EX.ENTERED_BY_PERSON_ID ENTERED_BY_PERSON_ID
, EX.INITIAL_SUBMISSION_DATE INITIAL_SUBMISSION_DATE
, EX.EXPENDITURE_GROUP EXPENDITURE_GROUP
, EX.CONTROL_TOTAL_AMOUNT CONTROL_TOTAL_AMOUNT
, EX.REQUEST_ID REQUEST_ID
, EX.PROGRAM_ID PROGRAM_ID
, EX.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, EX.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, EX.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, EX.ATTRIBUTE1 ATTRIBUTE1
, EX.ATTRIBUTE2 ATTRIBUTE2
, EX.ATTRIBUTE3 ATTRIBUTE3
, EX.ATTRIBUTE4 ATTRIBUTE4
, EX.ATTRIBUTE5 ATTRIBUTE5
, EX.ATTRIBUTE6 ATTRIBUTE6
, EX.ATTRIBUTE7 ATTRIBUTE7
, EX.ATTRIBUTE8 ATTRIBUTE8
, EX.ATTRIBUTE9 ATTRIBUTE9
, EX.ATTRIBUTE10 ATTRIBUTE10
, EX.PTE_REFERENCE PTE_REFERENCE
, ROUND(SUM(DECODE(EX.EXPENDITURE_CLASS_CODE
, 'OE'
, NVL(EI.RAW_COST
, (PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT (PA_UTILS.GETEXPTYPECOSTRATE(EI.EXPENDITURE_TYPE
, EI.EXPENDITURE_ITEM_DATE) * NVL(EI.QUANTITY
, 0)
, EI.PROJECT_CURRENCY_CODE)))
, 'OT'
, NVL(EI.QUANTITY
, 0)
, 0))
, 2) AMOUNT
, ROUND(((SUM(DECODE(BILLABLE_FLAG
, 'Y'
, DECODE(EX.EXPENDITURE_CLASS_CODE
, 'OE'
, NVL(EI.RAW_COST
, (PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT (PA_UTILS.GETEXPTYPECOSTRATE(EI.EXPENDITURE_TYPE
, EI.EXPENDITURE_ITEM_DATE) * NVL(EI.QUANTITY
, 0)
, EI.PROJECT_CURRENCY_CODE)))
, 'OT'
, NVL(QUANTITY
, 0)
, 0))) / ROUND(DECODE(SUM(DECODE(EX.EXPENDITURE_CLASS_CODE
, 'OE'
, NVL(EI.RAW_COST
, (PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT (PA_UTILS.GETEXPTYPECOSTRATE(EI.EXPENDITURE_TYPE
, EI.EXPENDITURE_ITEM_DATE) * NVL(EI.QUANTITY
, 0)
, EI.PROJECT_CURRENCY_CODE)))
, 'OT'
, NVL(QUANTITY
, 0)
, 0))
, 0
, 1
, SUM(DECODE(EX.EXPENDITURE_CLASS_CODE
, 'OE'
, NVL(EI.RAW_COST
, (PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT (PA_UTILS.GETEXPTYPECOSTRATE(EI.EXPENDITURE_TYPE
, EI.EXPENDITURE_ITEM_DATE) * NVL(EI.QUANTITY
, 0)
, EI.PROJECT_CURRENCY_CODE)))
, 'OT'
, NVL(QUANTITY
, 0)
, 0)))
, 2)) * 100)
, 2) BILLABLE_PCT
, PERSON_TYPE
, EX.ORG_ID
FROM PA_EXPENDITURES EX
, PER_PEOPLE_F EM
, PA_ROUTINGS RO
, PA_LOOKUPS RS
, PA_LOOKUPS ES
, PA_EXPENDITURE_ITEMS EI
WHERE EX.INCURRED_BY_PERSON_ID = EM.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN EM.EFFECTIVE_START_DATE
AND EM.EFFECTIVE_END_DATE
AND (EM.EMPLOYEE_NUMBER IS NOT NULL OR EM.NPW_NUMBER IS NOT NULL)
AND EX.EXPENDITURE_ID = EI.EXPENDITURE_ID
AND EX.EXPENDITURE_STATUS_CODE = 'SUBMITTED'
AND EX.EXPENDITURE_CLASS_CODE IN ('OT'
, 'OE')
AND RO.EXPENDITURE_ID = EX.EXPENDITURE_ID
AND RO.END_DATE IS NULL
AND ES.LOOKUP_CODE = EX.EXPENDITURE_STATUS_CODE
AND ES.LOOKUP_TYPE = 'EXPENDITURE STATUS'
AND RS.LOOKUP_CODE = RO.ROUTING_STATUS_CODE
AND RS.LOOKUP_TYPE = 'ROUTING STATUS CODE' GROUP BY EX.ROWID
, EX.EXPENDITURE_ID
, EX.EXPENDITURE_STATUS_CODE
, EX.EXPENDITURE_ENDING_DATE
, EX.EXPENDITURE_CLASS_CODE
, EX.INCURRED_BY_PERSON_ID
, EM.FULL_NAME
, NVL(EM.EMPLOYEE_NUMBER
, EM.NPW_NUMBER)
, RO.ROUTED_TO_PERSON_ID
, RO.ROUTING_COMMENT
, RS.MEANING
, ES.MEANING
, EX.DESCRIPTION
, EX.CREATED_BY
, EX.CREATION_DATE
, EX.LAST_UPDATED_BY
, EX.LAST_UPDATE_LOGIN
, EX.LAST_UPDATE_DATE
, EX.INCURRED_BY_ORGANIZATION_ID
, EX.ENTERED_BY_PERSON_ID
, EX.INITIAL_SUBMISSION_DATE
, EX.EXPENDITURE_GROUP
, EX.CONTROL_TOTAL_AMOUNT
, EX.REQUEST_ID
, EX.PROGRAM_ID
, EX.PROGRAM_APPLICATION_ID
, EX.PROGRAM_UPDATE_DATE
, EX.ATTRIBUTE_CATEGORY
, EX.ATTRIBUTE1
, EX.ATTRIBUTE2
, EX.ATTRIBUTE3
, EX.ATTRIBUTE4
, EX.ATTRIBUTE5
, EX.ATTRIBUTE6
, EX.ATTRIBUTE7
, EX.ATTRIBUTE8
, EX.ATTRIBUTE9
, EX.ATTRIBUTE10
, EX.PTE_REFERENCE
, EX.PERSON_TYPE
, EX.ORG_ID

Columns