R12: Iexpense Expense Reports Join To Ap Invoices

Following joins will helps to understand exact relation between iExpenses and AP invoices.

1
2
3
4
5
6

SELECT aerh.vouchno,
aerh.report_header_id
FROM ap_expense_report_headers_all aerh,
ap_invoices_all ap
WHERE aerh.expense_status_code = 'INVOICED'
AND ap.invoice_id= aerh.vouchno;

then you need to join ap_invoices_all with invoices_id with ap_invoice_lines and remaining as usual.

1. The column vouchno in the ap_expense_report_headers_all can be mapped to invoice_id in the ap_invoices_all table. We cannot be sure about the distribution_line_number.

2. REFERENCE_KEY1 and REFERENCE_KEY2 in the ap_invoice_lines_table can be used to map with the ap_expense_report_lines_all by report_header_id and report_line_id respectively

3. REFERENCE_KEY1 in the ap_invoices_all table can be used to map with the ap_expense_report_headers_all by report_header_id.
Ref: Support Doc Id 1914546.1

Thanks Yogesh