Introduction:
This blog has the SQL query that can be used to pull employee expenses data from Fusion Expense Module.
Cause of the issue:
Business wants a report that contains consolidated expense details of an employee from Fusion Expense module. The standard delivered reports under expense module does not have such report. So, business wants to create a custom report to pull the required data for audit purpose.
How do we solve:
Create a report in BI Publisher using below SQL query to extract the expense details.
Query:
SELECT EER.expense_report_num
,TO_CHAR (EER.report_submit_date,’MM/DD/YY’, ‘nls_date_language=American’) report_submit_date
,(SELECT FL.meaning
FROM fnd_lookups FL
WHERE lookup_type = ‘EXM_REPORT_STATUS’
AND lookup_code = EER.expense_status_code
) expense_status_code
,EER.purpose
,EER.expense_report_total
,EE.merchant_name
,(SELECT FL.meaning
FROM fnd_lookups FL
WHERE lookup_type = ‘EXM_EXPENSE_TYPE_CATEGORY’
AND lookup_code = EE.expense_type_category_code
) expense_type_category_code
,EET.name SUB_CATEGORY
,TO_CHAR (EER.final_approval_date,’MM/DD/YY’, ‘nls_date_language=American’) EXPENSE_APPROVAL_DATE
,PPA.person_number EMPLOYEE_NUMBER
,PPN.display_name EMPLOYEE_NAME
,GCC.segment2 NATURAL_ACCOUNT
,GCC.segment3 ACTIVITY_CODE
,GCC.segment4 COST_CENTER
,DECODE(EE.CARD_ID, NULL, ‘N’, ‘Y’) CCExpense_Flag
,DECODE( (NVL(INV_PAY.line_amount, 0) – NVL(INV_PAY.tax_amount, 0)), 0, EE.func_currency_amount, (NVL(INV_PAY.line_amount, 0) – NVL(INV_PAY.tax_amount, 0))) net_amount
,INV_PAY.tax_amount Tax
,EE.func_currency_amount Total_Amount_CAD
,(SELECT TO_CHAR (EERP.EVENT_DATE,’MM/DD/YY’, ‘nls_date_language=American’)
FROM EXM_EXP_REP_PROCESSING EERP
WHERE EERP.expense_report_id = EER.expense_report_id
AND UPPER(EERP.event) = ‘COMPLETE_AUDIT’
AND UPPER(EERP.expense_status_code) = ‘APPROVAL_COMPLETE’
) Expense_Audit_Date
,(SELECT TO_CHAR (EERP.EVENT_DATE,’MM/DD/YY’, ‘nls_date_language=American’)
FROM EXM_EXP_REP_PROCESSING EERP
WHERE EERP.expense_report_id = EER.expense_report_id
AND UPPER(EERP.event) = ‘PROCESS_REIMBURSEMENT’
AND UPPER(EERP.expense_status_code) = ‘INVOICED’
) Invoice_Generation_Date
,INV_PAY.period_name Accounting_Period
,TO_CHAR (INV_PAY.Payment_Date,’MM/DD/YY’, ‘nls_date_language=American’) Payment_Date
,INV_PAY.Payment_Amount
FROM exm_expense_reports EER
,exm_expenses EE
,per_all_people_f PPA
,per_person_names_f PPN
,exm_expense_types EET
,exm_expense_dists EED
,gl_code_combinations GCC
,gl_periods GP1
,gl_periods GP2
,(SELECT AIA.REFERENCE_KEY1 expense_report_id
,AILA.REFERENCE_KEY2 expense_id
,AIA.gl_date
,AILA.PERIOD_NAME
,AILA.AMOUNT LINE_AMOUNT
,SUM(NVL(ZL.unrounded_tax_amt,0)) tax_amount
,ACA.AMOUNT payment_amount
,ACA.CHECK_DATE payment_date
FROM ap_invoices_all AIA
,ap_invoice_lines_all AILA
,zx_lines ZL
,ap_invoice_payments_all AIPA
,ap_checks_all ACA
WHERE AIA.invoice_id = AILA.invoice_id
AND AIA.invoice_id = ZL.trx_id
AND AILA.line_number = ZL.trx_line_id
AND AIA.invoice_id = AIPA.invoice_id (+)
AND AIPA.check_id = ACA.check_id (+)
AND AIA.source = ‘EMP_EXPENSE_REPORT’
GROUP BY AIA.REFERENCE_KEY1
,AILA.REFERENCE_KEY2
,AIA.gl_date
,AILA.PERIOD_NAME
,AILA.AMOUNT
,ACA.AMOUNT
,ACA.CHECK_DATE) INV_PAY
WHERE 1=1
AND EER.expense_report_id = EE.expense_report_id
AND EER.REPORT_SUBMIT_DATE IS NOT NULL
AND EER.person_id = PPA.person_id
AND EER.person_id = PPN.person_id
AND PPN.name_type = ‘GLOBAL’
AND TRUNC(SYSDATE) BETWEEN PPN.effective_start_date AND NVL(PPN.effective_end_date, TRUNC(SYSDATE))
AND EE.expense_type_id = EET.expense_type_id
AND EER.expense_report_id = EED.expense_report_id
AND EE.expense_id = EED.expense_id
AND EED.code_combination_id = GCC.code_combination_id (+)
AND GP1.period_name = :p_period_from
AND GP1.period_set_name = ‘XX Calendar’
AND GP2.period_name = :p_period_to
AND GP2.period_set_name = ‘XX Calendar’
AND EER.report_submit_date BETWEEN GP1.start_date AND GP2.end_date
AND UPPER(EER.expense_status_code) <> ‘SAVED’
AND PPN.display_name = NVL(:p_person_name, PPN.display_name)
AND GCC.segment2 = NVL(:p_account, GCC.segment2)
AND GCC.segment3 = NVL(:p_activity_code, GCC.segment3)
AND GCC.segment4 = NVL(:p_cost_center, GCC.segment4)
AND EET.name = NVL(:p_sub_category, EET.name)
AND DECODE(EE.CARD_USAGE_ID, NULL, ‘N’, ‘Y’) = NVL(:p_ccc, DECODE(EE.CARD_USAGE_ID, NULL, ‘N’, ‘Y’))
AND EER.expense_report_id = TO_NUMBER(INV_PAY.expense_report_id (+))
AND EE.expense_id = TO_NUMBER(INV_PAY.expense_id (+))
ORDER BY PPA.person_number, EER.expense_report_num
