VIEW: APPS.PA_EXPENDITURES_V

Source

View Text - Preformatted

SELECT E.ROWID ROW_ID , E.EXPENDITURE_ID , E.EXPENDITURE_STATUS_CODE , E.EXPENDITURE_ENDING_DATE , E.EXPENDITURE_CLASS_CODE , E.INCURRED_BY_PERSON_ID , p.full_name incurred_by_employee_name , decode(p.current_employee_flag,'Y',employee_number,decode(CURRENT_NPW_FLAG,'Y',npw_number,NULL)) INCURRED_BY_EMPLOYEE_NUMBER , E.INCURRED_BY_ORGANIZATION_ID , pa_expenditures_utils.getorgtlname(E.INCURRED_BY_ORGANIZATION_ID ) , E.EXPENDITURE_GROUP , E.CONTROL_TOTAL_AMOUNT , E.ENTERED_BY_PERSON_ID , E.DESCRIPTION , E.INITIAL_SUBMISSION_DATE , E.denom_currency_code , E.acct_currency_code , E.acct_rate_date , E.acct_rate_type , E.acct_exchange_rate , 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.ORIG_EXP_TXN_REFERENCE1 , E.ORIG_USER_EXP_TXN_REFERENCE , E.VENDOR_ID , E.ORIG_EXP_TXN_REFERENCE2 , E.ORIG_EXP_TXN_REFERENCE3 , E.OVERRIDING_APPROVER_PERSON_ID , E.WF_STATUS_CODE , E.transfer_status_code , E.USER_BATCH_NAME , E.person_type , E.ORG_ID FROM PA_EXPENDITURES E, PER_ALL_PEOPLE_F p WHERE e.incurred_by_person_id = p.person_id(+) and ( p.rowid is NULL OR p.rowid in ( select max(p1.rowid) from PER_ALL_PEOPLE_F p1 where p.person_id = p1.person_id and decode (e.person_type,'CWK',p1.current_npw_flag,p1.current_employee_flag) = 'Y' and p1.effective_start_date <= e.expenditure_ending_date and p1.effective_end_date >= e.expenditure_ending_date - 6))

View Text - HTML Formatted

SELECT E.ROWID ROW_ID
, E.EXPENDITURE_ID
, E.EXPENDITURE_STATUS_CODE
, E.EXPENDITURE_ENDING_DATE
, E.EXPENDITURE_CLASS_CODE
, E.INCURRED_BY_PERSON_ID
, P.FULL_NAME INCURRED_BY_EMPLOYEE_NAME
, DECODE(P.CURRENT_EMPLOYEE_FLAG
, 'Y'
, EMPLOYEE_NUMBER
, DECODE(CURRENT_NPW_FLAG
, 'Y'
, NPW_NUMBER
, NULL)) INCURRED_BY_EMPLOYEE_NUMBER
, E.INCURRED_BY_ORGANIZATION_ID
, PA_EXPENDITURES_UTILS.GETORGTLNAME(E.INCURRED_BY_ORGANIZATION_ID )
, E.EXPENDITURE_GROUP
, E.CONTROL_TOTAL_AMOUNT
, E.ENTERED_BY_PERSON_ID
, E.DESCRIPTION
, E.INITIAL_SUBMISSION_DATE
, E.DENOM_CURRENCY_CODE
, E.ACCT_CURRENCY_CODE
, E.ACCT_RATE_DATE
, E.ACCT_RATE_TYPE
, E.ACCT_EXCHANGE_RATE
, 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.ORIG_EXP_TXN_REFERENCE1
, E.ORIG_USER_EXP_TXN_REFERENCE
, E.VENDOR_ID
, E.ORIG_EXP_TXN_REFERENCE2
, E.ORIG_EXP_TXN_REFERENCE3
, E.OVERRIDING_APPROVER_PERSON_ID
, E.WF_STATUS_CODE
, E.TRANSFER_STATUS_CODE
, E.USER_BATCH_NAME
, E.PERSON_TYPE
, E.ORG_ID
FROM PA_EXPENDITURES E
, PER_ALL_PEOPLE_F P
WHERE E.INCURRED_BY_PERSON_ID = P.PERSON_ID(+)
AND ( P.ROWID IS NULL OR P.ROWID IN ( SELECT MAX(P1.ROWID)
FROM PER_ALL_PEOPLE_F P1
WHERE P.PERSON_ID = P1.PERSON_ID
AND DECODE (E.PERSON_TYPE
, 'CWK'
, P1.CURRENT_NPW_FLAG
, P1.CURRENT_EMPLOYEE_FLAG) = 'Y'
AND P1.EFFECTIVE_START_DATE <= E.EXPENDITURE_ENDING_DATE
AND P1.EFFECTIVE_END_DATE >= E.EXPENDITURE_ENDING_DATE - 6))