VIEW: APPS.PA_EXPENDITURES_ROUTINGS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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