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
(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
FROM ap_expense_report_headers_all aerh,
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;