AP Payment Documents

-- ##############################################################################
--      PAYMENT DOCUMENTS
-- ##############################################################################
   
         select payment_document_name
              , payment_document_id
              , payment_instruction_id
              , internal_bank_account_id
              , cba.bank_account_name
              , cba.bank_account_num
              , cba.currency_code
	          , paper_stock_type
              , attached_remittance_stub_flag
              , number_of_lines_per_remit_stub
              , number_of_setup_documents
	          , ce_payment_documents.format_code
              , first_available_document_num
              , last_available_document_number
              , last_issued_document_number
              , manual_payments_only_flag
              , ce_payment_documents.attribute_category
              , inactive_date
              , decode(inactive_date, 'Y', 'N') status
	          , meaning paper_stock_type_meaning
	          , fmts.format_name 
	       from ce_payment_documents
	          , ce_lookups lookup
	          , iby_formats_vl fmts
              , ce_bank_accounts cba
          where lookup_type = 'CE_PAPER_STOCK_TYPES' 
	        and paper_stock_type = lookup.lookup_code 
	        and ce_payment_documents.format_code=fmts.format_code
            and internal_bank_account_id = cba.bank_account_id;

AP Payment Groups

          select vl.lookup_code
               , vl.meaning
               , vl.description
               , vl.creation_date
               , fu.user_name
               , fu.email_address
               , count(*) supplier_site_count
            from FND_LOOKUP_VALUES_VL vl
            join ap_supplier_sites_all pvsa ON pvsa.PAY_GROUP_LOOKUP_CODE = vl.lookup_code
            join fnd_user fu on vl.created_by = fu.user_id
           where vl.lookup_type = 'PAY GROUP'
        group by vl.lookup_code
               , vl.meaning
               , vl.description
               , vl.creation_date
               , fu.user_name
               , fu.email_address
        order by vl.meaning;

AP Invoices – Locked Invoices

If you’re using Oracle EBS Release 12, sometimes if the Invoice Validation job crashes, it can leave AP invoices hanging / locked, which means you can’t update them in any way. This SQL can find those invoices. You then need to apply a GDF to fix them – something along the lines of this:

R12:Payables:Generic Data Fix (GDF) Patch Number 20651268 – Invoices Locked by Invoice Validation Request That had Completed in Error (Doc ID 1072774.1)

-- When invoice validation goes wrong, they can become locked so nothing can be done to them.
-- This SQL can find those locked invoices

select * from ap.ap_invoices_all where validation_request_id is not null;

AP Suppliers

Sections:

  1. SUPPLIERS BASIC
  2. SUPPLIERS INC CONTACT INFO
  3. SUPPPLIER SITES NOT USED IN THE LAST 300 DAYS
  4. SUPPLIERS – SITE COUNT
  5. SUPPLIERS REPORT – PO and INV COUNT

SUPPLIERS BASIC

-- ##############################################################################
--        SUPPLIERS BASIC
-- ##############################################################################

        SELECT bus_gp.NAME org
             , pv.segment1
--             , pv.vendor_id
             , pv.vendor_type_lookup_code
             , pv.vendor_name
--             , (select count(*) from ap.ap_invoices_all aia where aia.vendor_id = pv.vendor_id) inv_ct
--             , (select count(*) from po.po_headers_all pha where pha.vendor_id = pv.vendor_id) po_ct
             , pv.end_date_active header_end_date
             , pvsa.purchasing_site_flag
             , pvsa.pay_site_flag
             , pvsa.inactive_date site_end_date
             , pvsa.vendor_site_code site
             , pvsa.address_line1 site_add_1
             , pvsa.address_line2 site_add_2
             , pvsa.address_line3 site_add_3
             , pvsa.city site_city
             , pvsa.state site_state
             , pvsa.zip site_zip
--             , pvsa.vendor_site_id
--             , pvsa.org_id
--             , pvsa.supplier_notif_method
             , pvsa.email_address email
             , pvsa.invoice_currency_code
             , pvsa.payment_currency_code
--             , pv.attribute8
--             , pvsa.attribute8
--             , fu.description last_udated_by
          FROM ap.ap_suppliers pv
          JOIN ap.ap_supplier_sites_all pvsa ON pv.vendor_id = pvsa.vendor_id
          JOIN hr.hr_all_organization_units_tl bus_gp ON pvsa.org_id = bus_gp.organization_id
          JOIN applsys.fnd_user fu ON pvsa.last_updated_by = fu.user_id
         WHERE 1 = 1
           AND pv.segment1 IN ('1111111')
      ORDER BY bus_gp.NAME
             , pv.vendor_name
             , pvsa.vendor_site_code;

SUPPLIERS INC CONTACT INFO

-- ##############################################################################
--        SUPPLIERS INC CONTACT INFO
-- ##############################################################################

    SELECT   pv.segment1 supplier_num
           , pv.vendor_name
           , (SELECT COUNT(*) po_ct
                FROM po.po_headers_all pha
               WHERE pha.vendor_id = pv.vendor_id
                 AND pha.vendor_site_id = pvsa.vendor_site_id
                 AND pha.type_lookup_code = 'STANDARD'
                 AND pha.creation_date >= SYSDATE - 365) po_count_last_365_day
           , pv.creation_date
           , pv.vendor_type_lookup_code TYPE
           , pvsa.vendor_site_code site_name
           , pvsa.purchasing_site_flag flag_purch
           , pvsa.rfq_only_site_flag flag_rfq
           , pvsa.pay_site_flag flag_pay
           , pvsa.address_line1 site_add_1
           , pvsa.address_line2 site_add_2
           , pvsa.address_line3 site_add_3
           , pvsa.city site_city
           , pvsa.state site_state
           , pvsa.zip site_zip
           , pvsa.email_address site_email_address
           , '----------'
           , fu.description site_last_updated_by
           , pvsa.last_update_date site_last_updated_on
           , pvc.first_name contact_first_name
           , pvc.last_name contact_last_name
           , pvc.phone contact_phone
           , pvc.email_address contact_email
        FROM ap.ap_suppliers pv
        JOIN ap.ap_supplier_sites_all pvsa ON pv.vendor_id = pvsa.vendor_id
   LEFT JOIN apps.po_vendor_contacts pvc ON pvsa.vendor_site_id = pvc.vendor_site_id
        JOIN applsys.fnd_user fu ON pvsa.last_updated_by = fu.user_id
       WHERE 1 = 1
--         AND pv.end_date_active IS NULL
--         AND pvsa.inactive_date IS NULL
--         AND pv.vendor_name LIKE 'A%'
         AND pv.segment1 = '1111111'
--         AND ((pvsa.email_address IS NOT NULL) OR (pvc.email_address IS NOT NULL))
    ORDER BY pv.vendor_name;

SUPPPLIER SITES NOT USED IN THE LAST 300 DAYS

-- ##############################################################################
--        SUPPPLIER SITES NOT USED IN THE LAST 300 DAYS
-- ##############################################################################

SELECT   pv.vendor_name
       , pvsa.vendor_site_code
       , (SELECT COUNT(*)
            FROM po.po_headers_all pha
           WHERE pha.vendor_id = pv.vendor_id
             AND pha.vendor_site_id = pvsa.vendor_site_id
             AND pha.creation_date >= :dt) po_count
       , (SELECT MAX(pha.creation_date)
            FROM po.po_headers_all pha
           WHERE pha.vendor_id = pv.vendor_id
             AND pha.vendor_site_id = pvsa.vendor_site_id
             AND pha.creation_date >= :dt) latest_po_date
       , ROUND(SYSDATE
            - (SELECT MAX(pha.creation_date)
                 FROM po.po_headers_all pha
                WHERE pha.vendor_id = pv.vendor_id
                  AND pha.vendor_site_id = pvsa.vendor_site_id
                  AND pha.creation_date >= :dt)
          , 2) days_since_last_po
       , (SELECT COUNT(*)
            FROM ap.ap_invoices_all aia
           WHERE aia.vendor_id = pv.vendor_id
             AND aia.vendor_site_id = pvsa.vendor_site_id
             AND aia.creation_date >= :dt) inv_count
       , (SELECT MAX(aia.creation_date)
            FROM ap.ap_invoices_all aia
           WHERE aia.vendor_id = pv.vendor_id
             AND aia.vendor_site_id = pvsa.vendor_site_id
             AND aia.creation_date >= :dt) latest_inv_date
       , ROUND(SYSDATE
            - (SELECT MAX(aia.creation_date)
                 FROM ap.ap_invoices_all aia
                WHERE aia.vendor_id = pv.vendor_id
                  AND aia.vendor_site_id = pvsa.vendor_site_id
                  AND aia.creation_date >= :dt)
          , 2) days_since_last_inv
    FROM ap.ap_suppliers pv
    JOIN ap.ap_supplier_sites_all pvsa ON pv.vendor_id = pvsa.vendor_id
   WHERE NOT EXISTS(
            SELECT 'z'
              FROM po.po_headers_all pha
             WHERE pha.vendor_id = pv.vendor_id
               AND pha.vendor_site_id = pvsa.vendor_site_id
               AND creation_date > SYSDATE - 300)
     AND NOT EXISTS(
            SELECT 'z'
              FROM ap.ap_invoices_all aia
             WHERE aia.vendor_id = pv.vendor_id
               AND aia.vendor_site_id = pvsa.vendor_site_id
               AND creation_date > SYSDATE - 300)
     AND pv.end_date_active IS NULL
     AND pvsa.inactive_date IS NULL
     AND pv.vendor_name LIKE 'A%'
     AND pvsa.purchasing_site_flag = 'Y'
ORDER BY 1
       , 2;

SUPPLIERS – SITE COUNT

-- ##############################################################################
--        SUPPLIERS - SITE COUNT
-- ##############################################################################

SELECT   pv.vendor_name
       , pv.vendor_type_lookup_code
       , pv.segment1 supplier_number
       , COUNT(pvsa.vendor_site_id) site_count
    FROM ap.ap_suppliers pv
    JOIN ap.ap_supplier_sites_all pvsa ON pv.vendor_id = pvsa.vendor_id
   WHERE NVL(pv.end_date_active, SYSDATE + 1) > SYSDATE
     AND NVL(pvsa.inactive_date, SYSDATE + 1) > SYSDATE
     AND pv.vendor_type_lookup_code = 'VENDOR'
GROUP BY pv.vendor_name
       , pv.vendor_type_lookup_code
       , pv.segment1
ORDER BY pv.vendor_name;

SUPPLIERS REPORT – PO and INV COUNT

-- ##############################################################################
--        SUPPLIERS REPORT - PO and INV COUNT
-- ##############################################################################

  SELECT pv.vendor_name
       , pv.vendor_type_lookup_code
       , pv.segment1 supplier_number
       , pvsa.vendor_site_code
       , pvsa.supplier_notif_method
       , pvsa.email_address email_po_address
       , pv.creation_date header_creation_date
       , pvsa.creation_date site_creation_date
       , pvsa.purchasing_site_flag
       , pvsa.pay_site_flag
       , pvsa.address_line1
       , pvsa.address_line2
       , pvsa.address_line3
       , pvsa.city
       , pvsa.state
       , pvsa.zip
       , (SELECT COUNT(*)
            FROM po.po_headers_all pha
           WHERE pha.vendor_id = pv.vendor_id
             AND pha.vendor_site_id = pvsa.vendor_site_id
             AND pha.creation_date >= :dt) po_count
       , (SELECT MAX(pha.creation_date)
            FROM po.po_headers_all pha
           WHERE pha.vendor_id = pv.vendor_id
             AND pha.vendor_site_id = pvsa.vendor_site_id
             AND pha.creation_date >= :dt) latest_po_date
       , ROUND(SYSDATE
            - (SELECT MAX(pha.creation_date)
                 FROM po.po_headers_all pha
                WHERE pha.vendor_id = pv.vendor_id
                  AND pha.vendor_site_id = pvsa.vendor_site_id
                  AND pha.creation_date >= :dt)
          , 2) days_since_last_po
       , (SELECT COUNT(*)
            FROM ap.ap_invoices_all aia
           WHERE aia.vendor_id = pv.vendor_id
             AND aia.vendor_site_id = pvsa.vendor_site_id
             AND aia.creation_date >= :dt) inv_count
       , (SELECT MAX(aia.creation_date)
            FROM ap.ap_invoices_all aia
           WHERE aia.vendor_id = pv.vendor_id
             AND aia.vendor_site_id = pvsa.vendor_site_id
             AND aia.creation_date >= :dt) latest_inv_date
       , ROUND(SYSDATE
            - (SELECT MAX(aia.creation_date)
                 FROM ap.ap_invoices_all aia
                WHERE aia.vendor_id = pv.vendor_id
                  AND aia.vendor_site_id = pvsa.vendor_site_id
                  AND aia.creation_date >= :dt)
          , 2) days_since_last_inv
    FROM ap.ap_suppliers pv
    JOIN ap.ap_supplier_sites_all pvsa ON pv.vendor_id = pvsa.vendor_id
     AND NVL(pv.end_date_active, SYSDATE + 1) > SYSDATE
     AND NVL(pvsa.inactive_date, SYSDATE + 1) > SYSDATE
     AND pv.vendor_name LIKE '1%'
ORDER BY pv.vendor_name
       , pvsa.vendor_site_code;

AP Suppliers – Currencies

    SELECT pv.segment1 supno
         , pv.vendor_name
         , pvsa.vendor_site_code site
         , pvsa.invoice_currency_code
         , pvsa.invoice_currency_code
         , pvsa.email_address email_po_add
         , pvsa.purchasing_site_flag
         , pvsa.pay_site_flag
         , pvsa.address_line1
         , pvsa.address_line2
         , pvsa.address_line3
         , pvsa.city
         , pvsa.state
         , pvsa.zip
         , pvsa.area_code
         , pvsa.phone
         , pvsa.fax
         , pvsa.fax_area_code
         , pvsa.payment_method_lookup_code
         , pvsa.invoice_currency_code
         , pvsa.payment_currency_code
      FROM ap.ap_suppliers pv
      JOIN ap.ap_supplier_sites_all pvsa on pv.vendor_id = pvsa.vendor_id
     WHERE NVL(pv.end_date_active, SYSDATE + 1) > SYSDATE
       AND NVL(pvsa.inactive_date, SYSDATE + 1) > SYSDATE
       AND pvsa.payment_currency_code = pvsa.invoice_currency_code
       AND pvsa.purchasing_site_flag = 'Y'
       AND pvsa.pay_site_flag = 'Y'
       AND pvsa.invoice_currency_code <> 'GBP'
  ORDER BY pv.vendor_name;

AP Invoices

Sections:

  1. Basic Invoice Details
  2. Basic Invoice Details With Lines
  3. Invoices Including Distributions And Projects
  4. Invoices Matched To Purchase Orders
  5. Invoice Details – Extra Info
  6. Invoice Details – Matching Holds
  7. AP Invoices – with Payment Numbers

Basic Invoice Details

-- ##############################################################################
--      BASIC INVOICE DETAILS
-- ############################################################################*/

SELECT aia.invoice_id id
     , aia.invoice_num num
     , aia.doc_sequence_value voucher
     , aia.source
     , aia.creation_date
     , aia.last_update_date          
     , aia.invoice_date inv_date
     , aia.invoice_type_lookup_code inv_type
     , aia.cancelled_date
     , aia.cancelled_by
     , aia.cancelled_amount
     , pv.vendor_name supplier
     , pv.segment1
     , pvsa.vendor_site_code site
     , aia.invoice_amount amt
     , fu.description cr_by
--     , aia.payment_status_flag paid
--     , nvl2(aia.cancelled_amount, 'Y', 'N') cancelled
--     , (SELECT COUNT(*) from ap.ap_holds_all ah where ah.invoice_id = aia.invoice_id AND ah.release_lookup_code IS NULL) hold_count
  FROM ap.ap_invoices_all aia
  JOIN ap.ap_terms_tl att            ON aia.terms_id =       att.term_id
  JOIN applsys.fnd_user fu           ON aia.created_by =     fu.user_id
  JOIN apps.po_vendors pv            ON aia.vendor_id =      pv.vendor_id
  JOIN apps.po_vendor_sites_all pvsa ON aia.vendor_site_id = pvsa.vendor_site_id AND pv.vendor_id = pvsa.vendor_id
 WHERE 1 = 1
--   AND pv.vendor_name = 'Cheezes For U'
--   AND aia.invoice_num = '123456'
   AND aia.invoice_id IN (2139498, 2095523)      -- ## ID ## --
--   AND aia.invoice_num IN ('SSE-1604-003')     -- ## NO  ## --
--   AND AIA.doc_sequence_value IN (2683928)     -- ## VOUCHER ## --
--   AND aia.last_updated_by = 67137
--   AND aia.creation_date BETWEEN '26-NOV-2015' AND '27-NOV-2015'
--   AND aia.invoice_date BETWEEN '01-MAY-2007' AND '01-JUL-2007'
--   AND aia.creation_date > '12-AUG-2015'
--   AND pv.segment1 IN ('9450242','9485283','9522614')
--   AND aia.last_update_date > '23-JUL-2015'
   AND 1 = 1;

Basic Invoice Details With Lines

-- ##############################################################################
--      BASIC INVOICE DETAILS WITH LINES
-- ############################################################################*/

SELECT aia.invoice_id id
     , aia.invoice_num num
     , aia.doc_sequence_value voucher
     , aia.creation_date
     , aia.last_update_date          
     , aia.invoice_date inv_date
     , aia.invoice_type_lookup_code inv_type
     , pv.vendor_name supplier
     , pvsa.vendor_site_code site
     , aia.invoice_amount amt
     , fu.description cr_by          
     , aia.payment_status_flag paid
     , aila.line_number
     , aila.line_type_lookup_code
     , aila.amount
     , aila.original_amount
     , aila.description
     , aila.tax_classification_code tax_code
  FROM ap.ap_invoices_all aia
  JOIN ap.ap_invoice_lines_all aila  ON aia.invoice_id =     aila.invoice_id 
  JOIN ap.ap_terms_tl att            ON aia.terms_id =       att.term_id 
  JOIN applsys.fnd_user fu           ON aia.created_by =     fu.user_id
  JOIN apps.po_vendors pv            ON aia.vendor_id =      pv.vendor_id
  JOIN apps.po_vendor_sites_all pvsa ON aia.vendor_site_id = pvsa.vendor_site_id AND pv.vendor_id = pvsa.vendor_id
 WHERE 1 = 1
   AND aia.invoice_id IN (1966606)                   -- ##    ID   ## --
--   AND aia.invoice_num IN ('123456')               -- ## NUMBER  ## --
--   AND aia.doc_sequence_value IN (123456)         -- ## VOUCHER ## --
   AND 1 = 1;

Invoices Including Distributions And Projects

-- ##############################################################################
--      INVOICES INCLUDING DISTRIBUTIONS AND PROJECTS
-- ############################################################################*/

    SELECT aia.invoice_id inv_id
         , aia.invoice_num inv_num
         , aia.doc_sequence_value vchr
         , aia.invoice_amount inv_amt
         , aia.invoice_type_lookup_code
         , ppa.segment1 project
         , pt.task_number
         , pv.vendor_name supplier
         , gcc.segment1 || '*' || gcc.segment2 || '*' || gcc.segment3 || '*' || gcc.segment4 || '*' || gcc.segment5 || '*' || gcc.segment6 account
         , gcc.enabled_flag
         , gcc.detail_posting_allowed_flag
         , gcc.detail_budgeting_allowed_flag
         , gcc.last_update_date last_update_gcc
         , aida.distribution_line_number
         , aida.creation_date
         , aida.invoice_distribution_id
         , aida.posted_flag
         , aida.last_update_date
         , aida.last_updated_by
         , aida.line_type_lookup_code
         , aida.period_name
         , aida.amount
         , (REPLACE(REPLACE(aida.description,CHR(10),''),CHR(13),' ')) distrib_description
         , aida.match_status_flag
         , aida.quantity_invoiced
         , aida.encumbered_flag
         , aida.pa_addition_flag
         , '#######################'
         , aia.creation_date inv_created
         , aida.creation_date dist_created
         , aida.expenditure_item_date
      FROM ap.ap_invoices_all aia
      JOIN ap.ap_invoice_distributions_all aida ON aia.invoice_id =                aida.invoice_id
      JOIN ap.ap_terms_tl att                   ON aia.terms_id =                  att.term_id
      JOIN applsys.fnd_user fu                  ON aia.created_by =                fu.user_id
      JOIN ap.ap_suppliers pv                   ON aia.vendor_id =                 pv.vendor_id
      JOIN ap.ap_supplier_sites_all pvsa        ON aia.vendor_site_id =            pvsa.vendor_site_id AND pv.vendor_id = pvsa.vendor_id
      JOIN gl.gl_code_combinations gcc          ON aida.dist_code_combination_id = gcc.code_combination_id
      JOIN ap.ap_suppliers pv                   ON aia.vendor_id =                 pv.vendor_id
 LEFT JOIN pa.pa_projects_all ppa               ON aida.project_id =               ppa.project_id
 LEFT JOIN pa.pa_tasks pt                       ON aida.task_id =                  pt.task_id
     WHERE 1 = 1
--       AND aia.doc_sequence_value IN (1234567)
       AND aia.invoice_id = 1966606
--       AND ABS(aida.amount) = 12.13
       AND ppa.segment1 = 'ABC12345'
--       AND aia.invoice_id IN (2110097, 2110217, 2111004, 2111694, 2111857)
--       AND aida.invoice_distribution_id IN (40573879,40573874,40573875)
       AND 1 = 1;

Invoices Matched To Purchase Orders

-- ##############################################################################
--      INVOICES MATCHED TO PURCHASE ORDERS
-- ############################################################################*/

    SELECT aia.invoice_id
         , aia.invoice_num
         , aia.doc_sequence_value
         , aida.pa_addition_flag
         , aida.invoice_distribution_id
         , aida.creation_date
         , aida.last_updated_by
         , aida.distribution_line_number dist_line
         , aida.line_type_lookup_code dist_line_type
         , aida.period_name dist_line_period
         , aida.po_distribution_id
         , aida.match_status_flag
         , aida.posted_flag
         , aida.quantity_invoiced
         , aida.amount dist_amt
         , pha.segment1 po
         , pha.po_header_id
         , pha.last_update_date po
         , pda.last_update_date pda
         , pv.vendor_name supplier
         , ppa.segment1 project
      FROM ap.ap_invoices_all aia
      JOIN ap.ap_invoice_distributions_all aida ON aia.invoice_id =          aida.invoice_id
      JOIN ap.ap_terms_tl att                   ON aia.terms_id =            att.term_id
      JOIN applsys.fnd_user fu                  ON aia.created_by =          fu.user_id
      JOIN ap.ap_suppliers pv                   ON aia.vendor_id =           pv.vendor_id
      JOIN ap.ap_supplier_sites_all pvsa        ON aia.vendor_site_id =      pvsa.vendor_site_id AND pv.vendor_id = pvsa.vendor_id
      JOIN po.po_distributions_all pda          ON aida.po_distribution_id = pda.po_distribution_id
      JOIN po.po_lines_all pla                  ON pda.po_line_id =          pla.po_line_id
      JOIN po.po_headers_all pha                ON pla.po_header_id =        pha.po_header_id
 LEFT JOIN pa.pa_projects_all ppa               ON pda.project_id =          ppa.project_id
     WHERE 1 = 1
    --   AND pha.segment1 IN (1508482)
       AND aia.invoice_id IN (1966606)                                      -- ##   ID   ## --
       AND ppa.segment1 = 'ABC12345'
    --   AND aia.invoice_num = '373018'                                      -- ## NUMBER ## --
    --   and AIA.DOC_SEQUENCE_VALUE IN (2735587)                             -- ## VOUCHER ## --
    --   AND aida.invoice_distribution_id IN (37543846,37543847,37543849,37543851)
       AND 1 = 1;

-- distinct Invoices matched to POs

    SELECT DISTINCT
           aia.invoice_id
         , aia.invoice_num
         , pha.segment1 po
         , pha.po_header_id
         , pv.vendor_name supplier
      FROM ap.ap_invoices_all aia
      JOIN ap.ap_invoice_distributions_all aida ON aia.invoice_id =          aida.invoice_id
      JOIN ap.ap_terms_tl att                   ON aia.terms_id =            att.term_id
      JOIN applsys.fnd_user fu                  ON aia.created_by =          fu.user_id
      JOIN ap.ap_suppliers pv                   ON aia.vendor_id =           pv.vendor_id
      JOIN ap.ap_supplier_sites_all pvsa        ON aia.vendor_site_id =      pvsa.vendor_site_id AND pv.vendor_id = pvsa.vendor_id
      JOIN po.po_distributions_all pda          ON aida.po_distribution_id = pda.po_distribution_id
      JOIN po.po_lines_all pla                  ON pda.po_line_id =          pla.po_line_id
      JOIN po.po_headers_all pha                ON pla.po_header_id =        pha.po_header_id
 LEFT JOIN pa.pa_projects_all ppa               ON pda.project_id =          ppa.project_id
     WHERE 1 = 1
    --   AND pha.segment1 IN (1508482)
       AND aia.invoice_id IN (1966606)                                      -- ##   ID   ## --
       AND ppa.segment1 = 'ABC12345'
    --   AND aia.invoice_num = '373018'                                      -- ## NUMBER ## --
    --   and AIA.DOC_SEQUENCE_VALUE IN (2735587)                             -- ## VOUCHER ## --
    --   AND aida.invoice_distribution_id IN (37543846,37543847,37543849,37543851)
       AND 1 = 1;

-- another basic overview

-- basic matching summary

select pha.segment1
	 , pla.unit_price
	 , pla.quantity
	 , pla.base_unit_price
	 , pda.quantity_billed
	 , pda.amount_billed
	 , pda.po_distribution_id
	 , aida.invoice_distribution_id
	 , aida.dist_match_type
	 , aida.amount
	 , aida.base_amount
	 , aida.quantity_invoiced
  from po_headers_all pha
  join po_lines_all pla on pha.po_header_id = pla.po_header_id
  join po_distributions_all pda on pla.po_header_id = pda.po_header_id and pla.po_line_id = pda.po_line_id
  join ap_invoice_distributions_all aida on aida.po_distribution_id = pda.po_distribution_id
 where pha.segment1 = 'PO123456'
   and pla.line_num = 3
   and INVOICE_DISTRIBUTION_ID = 4291526;

Invoice Details – Extra Info

If you can log into your database as the APPS user, or something like that, this can be useful to finding out the approval status, accounted status and things like that.

-- ##############################################################################
--      INVOICE DETAILS - EXTRA INFO
-- ############################################################################*/

SELECT aia.invoice_id id
     , aia.invoice_num num
     , aia.doc_sequence_value voucher
     , aia.creation_date
     , aia.last_update_date
     , aia.invoice_date inv_date
     , aia.invoice_type_lookup_code inv_type
     , aia.invoice_date
     , aia.validation_request_id
     , aia.validation_worker_id
     , pv.vendor_name supplier
     , pvsa.vendor_site_code site
     , aia.invoice_amount amt
     , fu.description cr_by          
     , aia.payment_status_flag paid
     , nvl2(aia.cancelled_amount, 'Y', 'N') cancelled
     , aia.cancelled_date
     , DECODE(apps.ap_invoices_utility_pkg.get_approval_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code), 'FULL' , 'Fully Applied', 'NEVER APPROVED' , 'Never Validated', 'NEEDS REAPPROVAL', 'Needs Revalidation', 'CANCELLED' , 'Cancelled', 'UNPAID' , 'Unpaid', 'AVAILABLE' , 'Available', 'UNAPPROVED' , 'Unvalidated', 'APPROVED' , 'Validated', 'PERMANENT' , 'Permanent Prepayment', NULL) validation_status_v1 -- http://m-burhan.blogspot.co.uk/2012/06/function-which-provide-ap-validation.html
     , DECODE(apps.ap_invoices_pkg.get_approval_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code), 'FULL', 'Fully Applied', 'UNAPPROVED' , 'Unvalidated', 'NEEDS REAPPROVAL', 'Needs Revalidation', 'APPROVED', 'Validated', 'NEVER APPROVED', 'Never Validated', 'CANCELLED', 'Cancelled', 'UNPAID', 'Unpaid', 'AVAILABLE', 'Available') validation_status_v2 -- https://community.oracle.com/thread/3573183
     , apps.ap_invoices_pkg.get_posting_status(aia.invoice_id) accounted
     , apps.ap_invoices_pkg.get_approval_status(aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) approval_status
     , apps.ap_invoices_pkg.get_amount_withheld (aia.invoice_id) amount_withheld
--     , (SELECT COUNT(*) from ap.ap_holds_all ah where ah.invoice_id = aia.invoice_id AND ah.release_lookup_code IS NULL) hold_count
  FROM ap.ap_invoices_all aia
  JOIN ap.ap_terms_tl att            ON aia.terms_id = att.term_id
  JOIN applsys.fnd_user fu           ON aia.created_by = fu.user_id
  JOIN apps.po_vendors pv            ON aia.vendor_id = pv.vendor_id
  JOIN apps.po_vendor_sites_all pvsa ON aia.vendor_site_id = pvsa.vendor_site_id AND pv.vendor_id = pvsa.vendor_id 
 WHERE 1 = 1
--   AND pv.vendor_name = 'Cheezes For U'
--   AND aia.invoice_id IN (252962,2088392,2165995,2500940,273058)                  -- ##    ID   ## --
--   AND aia.invoice_num IN ('12345678')              -- ## NUMBER  ## --
--   AND AIA.DOC_SEQUENCE_VALUE IN (2317661,2353095)          -- ## VOUCHER ## --
   AND DECODE(apps.ap_invoices_utility_pkg.get_approval_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code), 'FULL' , 'Fully Applied', 'NEVER APPROVED' , 'Never Validated', 'NEEDS REAPPROVAL', 'Needs Revalidation', 'CANCELLED' , 'Cancelled', 'UNPAID' , 'Unpaid', 'AVAILABLE' , 'Available', 'UNAPPROVED' , 'Unvalidated', 'APPROVED' , 'Validated', 'PERMANENT' , 'Permanent Prepayment', NULL) = 'Needs Revalidation'
   AND 1 = 1;

Invoice Details – Matching Holds

-- ##############################################################################
--      INVOICE - MATCH / HOLD INFORMATION
-- ############################################################################*/

SELECT aia.invoice_id
     , aia.invoice_num
     , aia.doc_sequence_value inv_voucher
     , aia.invoice_amount
     , aia.invoice_date
     , ah.hold_lookup_code
     , ah.hold_reason
     , ah.hold_date
     , ah.creation_date hold_created
     , ah.last_update_date hold_updated
     , ah.release_lookup_code
     , ah.release_reason
  FROM ap.ap_invoices_all aia
  JOIN ap.ap_holds_all ah ON aia.invoice_id = ah.invoice_id
 WHERE 1 = 1
   AND ah.hold_lookup_code IN ('CANT FUNDS CHECK')
--   AND aia.doc_sequence_value = 2445122
--   AND aha.hold_date > '01-OCT-2013'
   AND 1 = 1;

AP Invoices – with Payment Numbers

-- ##############################################################################
--      INVOICE DETAILS AND PAYMENT NUMBERS (AP_INVOICE_PAYMENT_HISTORY_V)
-- ############################################################################*/

SELECT aia.invoice_id
     , aia.invoice_num
     , aia.doc_sequence_value voucher
     , pv.vendor_name supplier
     , pv.segment1 supplier_num
     , DECODE(aipa.invoice_payment_type, 'PREPAY', aia.invoice_num, aca.check_number) document_number
     , aipa.amount
     , aipa.accounting_date
     , aipa.period_name
     , aipa.posted_flag
     , aipa.check_id
     , aipa.creation_date
  FROM ap.ap_invoice_payments_all aipa
  JOIN ap.ap_invoices_all aia ON aipa.invoice_id = aia.invoice_id
  JOIN ap.ap_checks_all aca   ON aipa.check_id =   aca.check_id
  JOIN ap.ap_suppliers pv     ON aia.vendor_id =   pv.vendor_id
 WHERE 1 = 1
--   AND aia.doc_sequence_value IN (257578)         -- ## VOUCHER ## --
   AND aia.invoice_id = 2809692
--   AND aipa.last_update_date > '09-APR-2015'
--   AND DECODE(aipa.invoice_payment_type, 'PREPAY', aia.invoice_num, aca.check_number) IN ('1234567')
   AND 1 = 1;