Introduction:
This blog has the SQL query that can be used to pull Travel expense data from Fusion Expense Module.
Cause of the issue:
Business wants a report that contains travel expense details from Fusion Expense module. The standard delivered reports under expense module does not have such report. Therefore, 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 GCC.segment4 COST_CENTER
,gl_flexfields_pkg.get_description_sql(GCC.chart_of_accounts_id, 4, GCC.segment4) COST_CENTER_NAME
,PPA.person_number EMPLOYEE_NUMBER
,PPN.display_name EMPLOYEE_NAME
,EER.expense_report_num
,EER.purpose
,INV_PAY.period_name ACCOUNTING_PERIOD
,TO_CHAR(EE.start_date,’MM/DD/YY’, ‘nls_date_language=American’) EXPENSE_DATE
,EET.name SUB_CATEGORY
,NULL CHECK_IN_DEPARTURE_DATE
,destination_from DEPARTURE_FROM
,destination_to DEPARTURE_TO
,TO_CHAR(EE.checkout_date,’MM/DD/YY’, ‘nls_date_language=American’) CHECK_OUT_TRAVEL_RETURN_DATE
,EE.agency_name
,EE.merchant_name
,EE.travel_type FLIGHT_TYPE
,EE.ticket_class_code FLIGHT_CLASS
,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_EXPENSE_AMOUNT
,NULL HOTEL_NIGHTS
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.segment4 = NVL(:p_cost_center, GCC.segment4)
AND GCC.segment2 = NVL(:p_account, GCC.segment2)
AND EET.name = NVL(:p_sub_category, EET.name)
AND UPPER(EET.name) LIKE ‘%TRAVEL%’
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
