VIEW: APPS.PA_ONLINE_EXP_V

Source

View Text - Preformatted

SELECT E.ROWID ROW_ID , E.EXPENDITURE_ID , E.EXPENDITURE_STATUS_CODE , L1.MEANING EXPENDITURE_STATUS , E.EXPENDITURE_ENDING_DATE , E.EXPENDITURE_CLASS_CODE , pa_online_exp.Get_Exp_Trans(E.EXPENDITURE_CLASS_CODE) , pa_online_exp.Get_Exp_Src(E.EXPENDITURE_CLASS_CODE, E.EXPENDITURE_GROUP, E.PTE_REFERENCE) , pa_online_exp.Get_Exp_Src_Code(E.EXPENDITURE_CLASS_CODE, E.EXPENDITURE_GROUP, E.PTE_REFERENCE) , E.INCURRED_BY_PERSON_ID , P.FULL_NAME INCURRED_BY_EMPLOYEE_NAME , NVL( P.EMPLOYEE_NUMBER, P.NPW_NUMBER ) INCURRED_BY_EMPLOYEE_NUMBER , E.INCURRED_BY_ORGANIZATION_ID , O.NAME ORGANIZATION_NAME , E.EXPENDITURE_GROUP , E.CONTROL_TOTAL_AMOUNT , E.ENTERED_BY_PERSON_ID , P1.FULL_NAME ENTERED_BY_EMPLOYEE_NAME , E.DESCRIPTION , E.INITIAL_SUBMISSION_DATE , E.LAST_UPDATE_LOGIN , E.LAST_UPDATE_DATE , E.LAST_UPDATED_BY , E.CREATION_DATE , E.CREATED_BY , E.ATTRIBUTE_CATEGORY , E.ATTRIBUTE1 , E.ATTRIBUTE2 , E.ATTRIBUTE3 , E.ATTRIBUTE4 , E.ATTRIBUTE5 , E.ATTRIBUTE6 , E.ATTRIBUTE7 , E.ATTRIBUTE8 , E.ATTRIBUTE9 , E.ATTRIBUTE10 , E.PTE_REFERENCE , pa_online_exp.Get_wte_reference(E.EXPENDITURE_GROUP,E.EXPENDITURE_CLASS_CODE) , nvl(pa_online_exp.Get_Exp_Total_Hours(E.EXPENDITURE_ID,E.EXPENDITURE_CLASS_CODE),0) , nvl(pa_online_exp.Get_Exp_Total_Cost(E.EXPENDITURE_ID,E.EXPENDITURE_CLASS_CODE),0) , E.ORG_ID FROM HR_ORGANIZATION_UNITS O, PER_PEOPLE_F P, PER_PEOPLE_F P1, PA_LOOKUPS L1, PA_EXPENDITURES E WHERE L1.LOOKUP_CODE = E.EXPENDITURE_STATUS_CODE AND L1.LOOKUP_TYPE = 'EXPENDITURE STATUS' AND TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE (+) AND P.EFFECTIVE_END_DATE (+) AND TRUNC(SYSDATE) BETWEEN P1.EFFECTIVE_START_DATE (+) AND P1.EFFECTIVE_END_DATE (+) AND E.INCURRED_BY_PERSON_ID = P.PERSON_ID (+) AND E.ENTERED_BY_PERSON_ID = P1.PERSON_ID (+) AND E.INCURRED_BY_ORGANIZATION_ID = O.ORGANIZATION_ID AND (E.EXPENDITURE_CLASS_CODE = 'OE' OR E.EXPENDITURE_CLASS_CODE = 'OT' OR E.EXPENDITURE_CLASS_CODE = 'PE' OR E.EXPENDITURE_CLASS_CODE = 'PT')

View Text - HTML Formatted

SELECT E.ROWID ROW_ID
, E.EXPENDITURE_ID
, E.EXPENDITURE_STATUS_CODE
, L1.MEANING EXPENDITURE_STATUS
, E.EXPENDITURE_ENDING_DATE
, E.EXPENDITURE_CLASS_CODE
, PA_ONLINE_EXP.GET_EXP_TRANS(E.EXPENDITURE_CLASS_CODE)
, PA_ONLINE_EXP.GET_EXP_SRC(E.EXPENDITURE_CLASS_CODE
, E.EXPENDITURE_GROUP
, E.PTE_REFERENCE)
, PA_ONLINE_EXP.GET_EXP_SRC_CODE(E.EXPENDITURE_CLASS_CODE
, E.EXPENDITURE_GROUP
, E.PTE_REFERENCE)
, E.INCURRED_BY_PERSON_ID
, P.FULL_NAME INCURRED_BY_EMPLOYEE_NAME
, NVL( P.EMPLOYEE_NUMBER
, P.NPW_NUMBER ) INCURRED_BY_EMPLOYEE_NUMBER
, E.INCURRED_BY_ORGANIZATION_ID
, O.NAME ORGANIZATION_NAME
, E.EXPENDITURE_GROUP
, E.CONTROL_TOTAL_AMOUNT
, E.ENTERED_BY_PERSON_ID
, P1.FULL_NAME ENTERED_BY_EMPLOYEE_NAME
, E.DESCRIPTION
, E.INITIAL_SUBMISSION_DATE
, E.LAST_UPDATE_LOGIN
, E.LAST_UPDATE_DATE
, E.LAST_UPDATED_BY
, E.CREATION_DATE
, E.CREATED_BY
, E.ATTRIBUTE_CATEGORY
, E.ATTRIBUTE1
, E.ATTRIBUTE2
, E.ATTRIBUTE3
, E.ATTRIBUTE4
, E.ATTRIBUTE5
, E.ATTRIBUTE6
, E.ATTRIBUTE7
, E.ATTRIBUTE8
, E.ATTRIBUTE9
, E.ATTRIBUTE10
, E.PTE_REFERENCE
, PA_ONLINE_EXP.GET_WTE_REFERENCE(E.EXPENDITURE_GROUP
, E.EXPENDITURE_CLASS_CODE)
, NVL(PA_ONLINE_EXP.GET_EXP_TOTAL_HOURS(E.EXPENDITURE_ID
, E.EXPENDITURE_CLASS_CODE)
, 0)
, NVL(PA_ONLINE_EXP.GET_EXP_TOTAL_COST(E.EXPENDITURE_ID
, E.EXPENDITURE_CLASS_CODE)
, 0)
, E.ORG_ID
FROM HR_ORGANIZATION_UNITS O
, PER_PEOPLE_F P
, PER_PEOPLE_F P1
, PA_LOOKUPS L1
, PA_EXPENDITURES E
WHERE L1.LOOKUP_CODE = E.EXPENDITURE_STATUS_CODE
AND L1.LOOKUP_TYPE = 'EXPENDITURE STATUS'
AND TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE (+)
AND P.EFFECTIVE_END_DATE (+)
AND TRUNC(SYSDATE) BETWEEN P1.EFFECTIVE_START_DATE (+)
AND P1.EFFECTIVE_END_DATE (+)
AND E.INCURRED_BY_PERSON_ID = P.PERSON_ID (+)
AND E.ENTERED_BY_PERSON_ID = P1.PERSON_ID (+)
AND E.INCURRED_BY_ORGANIZATION_ID = O.ORGANIZATION_ID
AND (E.EXPENDITURE_CLASS_CODE = 'OE' OR E.EXPENDITURE_CLASS_CODE = 'OT' OR E.EXPENDITURE_CLASS_CODE = 'PE' OR E.EXPENDITURE_CLASS_CODE = 'PT')