SQL Query to get Invoice Status, Accounting Status, Approval Status and GL Segments in Oracle Fusion

 

You can use the below query to get Invoice Validation Status, Accounting Status, Approval Status and GL Segments in Oracle Fusion

 

SELECT
aia.invoice_id,
aia.invoice_num,
aia.invoice_currency_code,
aia.invoice_amount,
aia.invoice_date,
aia.source,
aia.invoice_type_lookup_code,
aia.description,
aia.approval_status,
ap_invoices_pkg.Get_approval_status(aia.invoice_id, aia.invoice_amount,
aia.payment_status_flag, aia.invoice_type_lookup_code)
invoice_approval_status,
ap_invoices_pkg.Get_posting_status(aia.invoice_id)
Accounting_status,
aila.overlay_dist_code_concat,
aila.default_dist_ccid
,
gl_flexfields_pkg.Get_description_sql(gcc.chart_of_accounts_id, 1, gcc.segment1)segment1_desc,
gcc.segment1
,
gl_flexfields_pkg.Get_description_sql(gcc.chart_of_accounts_id, 2, gcc.segment2)segment2_desc,
gcc.segment2
,
gl_flexfields_pkg.Get_description_sql(gcc.chart_of_accounts_id, 3, gcc.segment3)segment3_desc,
gcc.segment3
,
gl_flexfields_pkg.Get_description_sql(gcc.chart_of_accounts_id, 4, gcc.segment4)segment4_desc,
gcc.segment4
FROM   ap_invoices_all aia,
       ap_invoice_lines_all aila,
       gl_code_combinations gcc
WHERE  1 = 1
       AND aia.invoice_id = aila.invoice_id
       AND gcc.code_combination_id = aia.accts_pay_code_combination_id