AP Invoice and Payment Details Report

This report pulls detailed information for Accounts Payable including invoices, invoice lines, payment schedules, checks, operating unit, ledger, terms, and distribution codes.

SELECT aia.invoice_num,
       aia.invoice_date,
       aia.invoice_amount,
       aila.amount AS line_amount,
       pha.segment1 AS po_number,
       aila.line_type_lookup_code,
       aps.vendor_name,
       apss.vendor_site_code,
       aca.check_number AS \"Payment Number\",
       aipa.amount AS \"Check Amount\",
       apsa.amount_remaining AS \"Payment Left\",
       hou.name AS operating_unit,
       gl.name AS ledger_name,
       aila.line_number,
       apt.name AS Term_name,
       gcc.segment1 || '.' || gcc.segment2 AS distributed_code_combinations,
       DECODE(aia.PAYMENT_STATUS_FLAG,
              'N','UN-PAID',
              'P','Partial Paid',
              'Y','PAID') AS PAYMENT_STATUS_FLAG
FROM ap_invoices_all aia,
     ap_invoice_lines_all aila,
     ap_invoice_distributions_all aida,
     poz_suppliers aps,
     poz_supplier_sites_all apss,
     po_headers_all pha,
     gl_code_combinations gcc,
     ap_invoice_payments_all aipa,
     ap_checks_all aca,
     ap_payment_schedules_all apsa,
     ap_terms apt,
     hr_operating_units hou,
     gl_ledgers gl
WHERE 1=1
  AND aia.vendor_id = aps.vendor_id
  AND aia.vendor_site_id = apss.vendor_site_id
  AND aps.vendor_id = apss.vendor_id
  AND aia.po_header_id = pha.po_header_id(+)
  AND aia.invoice_id = aila.invoice_id
  AND aila.invoice_id = aida.invoice_id
  AND aila.line_number = aida.invoice_line_number
  AND aca.check_id(+) = aipa.check_id
  AND apsa.invoice_id = aia.invoice_id
  AND apt.term_id = aia.terms_id
  AND hou.organization_id = aia.org_id
  AND gl.ledger_id = aia.set_of_books_id
  AND aida.dist_code_combination_id = gcc.code_combination_id
  AND aipa.invoice_id(+) = aia.invoice_id;