This Sql query helps in extracting all the Expenses submitted for a particular BU in oracle cloud HCM. This helps in reviewing the total expenses paid for a particular BU. This query includes columns like employee name, employee number, expense report number, expense currency, expense amount etc.
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 hou.NAME like ‘%ORG_NAME%’
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