This Sql query extracts all the expenses submitted by an employee in oracle cloud HCM. It shows the Expense report level details like the expense report number, employee name and number but also drills down to the expense level details like expense description and amount. This extract is particularily helpful when extracting the status of all the expenses an employee has gotten reimbursed.
select to_char(EER.EXPENSE_REPORT_ID) EXPENSE_REPORT_ID
,to_char(EER.EXPENSE_REPORT_NUM) EXPENSE_REPORT_NUM
,to_char(EER.EXPENSE_REPORT_DATE,’DD-MM-YYYY’) EXPENSE_REPORT_DATE
,to_char(EER.REPORT_SUBMIT_DATE,’DD-MM-YYYY’) EXPENSE_SUBMIT_DATE
,ppnf.DISPLAY_NAME
,papf.PERSON_NUMBER
,ee.DESCRIPTION
,ee.REIMBURSABLE_AMOUNT
,ee.RECEIPT_CURRENCY_CODE
,ee.EXPENSE_SOURCE
,ee.EXPENSE_TYPE_CATEGORY_CODE
,EER.EXPENSE_REPORT_TOTAL
,EER.PAYMENT_METHOD_CODE
,EER.EXPENSE_STATUS_CODE
from exm_expense_reports eer,
per_all_people_f papf,
per_person_names_f ppnf,
EXM_EXPENSES ee
where papf.person_id=eer.person_id
and ppnf.person_id=papf.person_id
and ee.expense_report_id=eer.expense_report_id
and UPPER(ppnf.DISPLAY_NAME) like ‘%JOHN%’
and ppnf.NAME_TYPE=’GLOBAL’
and trunc(sysdate) between trunc(papf.EFFECTIVE_START_DATE) and trunc(papf.EFFECTIVE_END_DATE)
and trunc(sysdate) between trunc(ppnf.EFFECTIVE_START_DATE) and trunc(ppnf.EFFECTIVE_END_DATE)
ORDER BY EER.EXPENSE_REPORT_DATE,EER.EXPENSE_REPORT_NUM