This query extracts all the expenses with their details submitted during the last month in oracle cloud HCM. It contains all the helpful columns like Employee name and number, Expense report number, Expense report payment status and expense description.
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
,hou.NAME BU_NAME
,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,
hr_operating_units hou
where papf.person_id=eer.person_id
and ppnf.person_id=papf.person_id
and ee.expense_report_id=eer.expense_report_id
AND hou.organization_id=eer.org_id
and ppnf.NAME_TYPE=’GLOBAL’
and trunc(EER.REPORT_SUBMIT_DATE)>=trunc(sysdate-30)
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