In this article we will see Oracle applications r12 Expense report tables as well as the SQL query to get the Employee’s Expense report data. In comparison to Oracle Apps Cloud, the Expense Reports tables have been altered in Oracle Cloud. The Oracle Apps R12 Expense Report Tables and SQL query to get the Employee Expense Reports Table are listed below.
Expense Reports Main table in Oracle Apps R12
- AP_CREDIT_CARD_TRXNS_ALL
- AP_EXPENSE_REPORT_HEADERS_ALL
- AP_EXPENSE_REPORT_LINES_ALL
- AP_EXP_REPORT_DISTS_ALL
(1) AP_CREDIT_CARD_TRXNS_ALL: Table to keep track of corporate credit card transactions sent by banks. When the user generates credit card expenditure lines, these lines are preserved as expense lines.
(2) AP_EXPENSE_REPORT_HEADERS_ALL: This table stores information about expense report headers.
(3) AP_EXPENSE_REPORT_LINES_ALL: This table stores information on expense report lines.
(4) AP_EXP_REPORT_DISTS_ALL: This table stores information about expense report distribution. It lists the accounts that correspond to each expense report line.
Query to get Expense claim in Oracle Apps R12
SELECT aerh.report_header_id
,aerh.employee_id
,aerh.total
,aerh.invoice_num
,aerh.description
,aerh.report_submitted_date
,aerh.expense_status_code
,aerl.start_expense_date
,aerd.project_id
,aerd.task_id
,ppv.segment1 project_number
,tasks.task_number
,aerl.distribution_line_number line_num
, papf.full_name
FROM ap_expense_report_headers_all aerh,
ap_expense_report_lines_all aerl,
ap_exp_report_dists_all aerd,
pa_projects_all ppv,
pa_tasks tasks,
hr_all_organization_units org,
per_all_people_f papf
WHERE aerh.report_header_id = &expense_report_header_id
AND aerh.org_id = org.organization_id
AND aerh.report_header_id = aerl.report_header_id
AND aerl.report_line_id = aerd.report_line_id
AND aerd.task_id = tasks.task_id
AND aerd.project_id = ppv.project_id
AND papf.person_id = aerh.employee_id
and sysdate between papf.effective_start_date and papf.effective_end_date
ORDER BY aerh.report_header_id, distribution_line_number;