Introduction
This Query will help to Fetch Records From AP Expense to GL
SQL Query:
SELECT aerha.invoice_num expense_invoice_number, papf.full_name employee_name,
aerha.SOURCE, aerha.description expense_header_description,
aerha.attribute_category, aerha.expense_status_code,
aerla.start_expense_date, aerla.distribution_line_number,
aerla.item_description line_description, aerla.amount,
aerla.merchant_name, gcc.concatenated_segments ACCOUNT, jh.period_name,
jh.NAME journal_name, jb.NAME batch_name, jh.je_source journal_source,
jh.je_category journal_category,
TO_CHAR (jh.default_effective_date, ‘DD-MON-YYYY’) gl_date,
TO_CHAR (jh.posted_date, ‘DD-MON-YYYY’) posted_date,
jl.description gl_description, jh.currency_code, ael.entered_dr,
ael.entered_cr, ael.accounted_dr, ael.accounted_cr
FROM apps.ap_expense_report_headers_all aerha,
apps.ap_expense_report_lines_all aerla,
apps.per_all_people_f papf,
apps.ap_invoices_all aia,
apps.ap_ae_lines_all ael,
apps.ap_invoice_distributions_all apid,
apps.gl_je_batches jb,
apps.gl_je_headers jh,
apps.gl_je_lines jl,
apps.gl_import_references gir,
apps.gl_code_combinations_kfv gcc
WHERE 1 = 1
AND aerha.report_header_id = aerla.report_header_id
AND aerha.employee_id = papf.person_id
AND aerha.invoice_num = aia.invoice_num
AND aia.invoice_id = apid.invoice_id
AND ael.source_table = ‘AP_INVOICE_DISTRIBUTIONS’
AND aerla.distribution_line_number = apid.distribution_line_number
AND aerla.web_parameter_id = apid.web_parameter_id
AND ael.source_id = apid.invoice_distribution_id
AND apid.invoice_id = aia.invoice_id
AND jb.je_batch_id = jh.je_batch_id
AND jl.code_combination_id = gcc.code_combination_id
AND jl.je_header_id = gir.je_header_id
AND jl.je_header_id = jh.je_header_id
AND jh.je_header_id = gir.je_header_id
AND jh.status = ‘P’
AND jl.je_line_num = gir.je_line_num
AND gir.gl_sl_link_id = ael.gl_sl_link_id
AND gir.gl_sl_link_table = ‘APECL’
AND aia.invoice_currency_code = jh.currency_code
AND TRUNC (aerha.creation_date) BETWEEN ’01-JAN-2021′ AND ’15-FEB-21′;
Got any queries?
Do drop a note by writing us at Venkatesh.b@doyensys.com or use the comment section below to ask your questions
