AP Invoices – GL and Project Balances

I can’t remember how I ended up writing this piece of SQL. I think I wanted to look at AP invoices linked to PA Projects, and compare the balances between the Expenditure Item on the Project, and the Sub-Ledger tables.

It seemed to make sense at the time!

-- invoice headers

    SELECT aia.invoice_id
         , aia.invoice_num
         , aia.doc_sequence_value voucher
         , aia.creation_date
         , aia.invoice_amount
         , (SELECT sum(peia.raw_cost)
              FROM pa.pa_expenditure_items_all peia
                 , ap.ap_invoice_distributions_all aida
             WHERE peia.document_distribution_id = aida.invoice_distribution_id
               AND peia.document_header_id = aia.invoice_id) pa_total
         , (SELECT sum(xal.accounted_dr) - sum(xal.accounted_cr)
              FROM xla.xla_ae_lines xal
                 , xla.xla_ae_headers xah 
                 , xla.xla_events xe 
                 , xla.xla_transaction_entities xte 
             WHERE xal.application_id = xah.application_id AND xal.ae_header_id = xah.ae_header_id
               AND xah.application_id = xe.application_id  AND xah.event_id = xe.event_id
               AND xe.application_id =  xte.application_id AND xe.entity_id = xte.entity_id
               AND xte.source_id_int_1 = aia.invoice_id) gl_total
      FROM ap.ap_invoices_all aia
     WHERE 1 = 1
       AND aia.doc_sequence_value IN (1111111)
       AND 1 = 1;
       
-- invoice headers and distributions
    SELECT DISTINCT
           aia.invoice_id
         , aia.invoice_num
         , aia.doc_sequence_value voucher
         , aia.creation_date
         , aia.invoice_amount
         , (SELECT sum(peia.raw_cost)
              FROM pa.pa_expenditure_items_all peia
                 , ap.ap_invoice_distributions_all aida
             WHERE peia.document_distribution_id = aida.invoice_distribution_id
               AND peia.document_header_id = aia.invoice_id
               AND aida.invoice_distribution_id = aida2.invoice_distribution_id) pa_total
         , (SELECT sum(xal.accounted_dr) - sum(xal.accounted_cr)
              FROM xla.xla_ae_lines xal
                 , xla.xla_ae_headers xah 
                 , xla.xla_events xe 
                 , xla.xla_transaction_entities xte 
             WHERE xal.application_id = xah.application_id AND xal.ae_header_id = xah.ae_header_id
               AND xah.application_id = xe.application_id  AND xah.event_id = xe.event_id
               AND xe.application_id =  xte.application_id AND xe.entity_id = xte.entity_id
               AND xte.source_id_int_1 = aia.invoice_id) gl_total
      FROM ap.ap_invoices_all aia
      JOIN ap.ap_invoice_distributions_all aida2 ON aia.invoice_id = aida2.invoice_id
     WHERE aia.doc_sequence_value IN (1111111);
     
-- differences
    SELECT invoice_id
         , invoice_num
         , voucher
         , creation_date
         , invoice_amount
         , SUM(pa_total) pa
         , SUM(gl_total) gl
      FROM (SELECT DISTINCT
                   aia.invoice_id
                 , aia.invoice_num
                 , aia.doc_sequence_value voucher
                 , aia.creation_date
                 , aia.invoice_amount
                 , (SELECT sum(peia.raw_cost)
                      FROM pa.pa_expenditure_items_all peia
                         , ap.ap_invoice_distributions_all aida
                     WHERE peia.document_distribution_id = aida.invoice_distribution_id
                       AND peia.document_header_id = aia.invoice_id
                       AND aida.invoice_distribution_id = aida2.invoice_distribution_id) pa_total
                 , (SELECT sum(xal.accounted_dr) - sum(xal.accounted_cr)
                      FROM xla.xla_ae_lines xal
                         , xla.xla_ae_headers xah 
                         , xla.xla_events xe 
                         , xla.xla_transaction_entities xte 
                     WHERE xal.application_id = xah.application_id AND xal.ae_header_id = xah.ae_header_id
                       AND xah.application_id = xe.application_id  AND xah.event_id = xe.event_id
                       AND xe.application_id =  xte.application_id AND xe.entity_id = xte.entity_id
                       AND xte.source_id_int_1 = aia.invoice_id) gl_total
              FROM ap.ap_invoices_all aia
              JOIN ap.ap_invoice_distributions_all aida2 ON aia.invoice_id = aida2.invoice_id
             WHERE aida2.project_id IS NOT NULL
--               AND aia.doc_sequence_value IN (1111111)
               AND aia.creation_date > '01-JAN-2016')
     HAVING SUM(pa_total) <> SUM(gl_total)
  GROUP BY invoice_id
         , invoice_num
         , voucher
         , creation_date
         , invoice_amount

Warning: Invalid argument supplied for foreach() in /home/oracle36/public_html/blog/wp-content/themes/bootville-lite/content.php on line 64

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 Invoices – Invoice Interface

            select aif.invoice_id
                 , aif.invoice_num
                 , air.reject_lookup_code
                 , pv.vendor_name
                 , aif.invoice_date
                 , aili.LINE_NUMBER
                 , aili.LINE_GROUP_NUMBER
                 , aili.LINE_TYPE_LOOKUP_CODE
                 , aili.amount
                 , aili.po_number
                 , aili.po_line_number
                 , aili.ITEM_DESCRIPTION
                 , aili.QUANTITY_INVOICED
                 , aili.unit_price
                 , aili.org_id
                 , aili.reference_1
              from AP_INVOICES_INTERFACE aif
              join AP_INVOICE_LINES_INTERFACE aili ON aif.invoice_id = aili.invoice_id
              join ap_suppliers pv on aif.vendor_id = pv.vendor_id
         left join AP_INTERFACE_REJECTIONS air on air.parent_id = aili.INVOICE_LINE_ID AND air.parent_table = 'AP_INVOICE_LINES_INTERFACE'
             where aif.vendor_id = 1111111
               and aif.invoice_id = 1111111
               AND aili.po_number LIKE '1%'
               AND aif.invoice_date > '01-AUG-2017'
          order by aif.invoice_date desc;

AP Suppliers – Bank Accounts

Sections:

  1. BANK ACCOUNTS AGAINST SUPPLIERS V1
  2. BANK ACCOUNTS AGAINST SUPPLIERS V2
  3. BANK ACCOUNTS AGAINST SUPPLIERS V3
  4. BANK ACCOUNTS AGAINST SUPPLIERS V4
  5. COUNT OF SETUPS PER SUPPLIER
  6. BANK ACCOUNTS WITH NULL AND UKS CURRENCY_CODES
  7. EXTERNAL BANK ACCOUNTS – BASIC DETAILS WITH SETUP INFO

BANK ACCOUNTS AGAINST SUPPLIERS V1

-- ##############################################################################
--        BANK ACCOUNTS AGAINST SUPPLIERS V1
-- ##############################################################################

     SELECT aps.vendor_name "VERDOR NAME"
          , aps.vendor_id
          , aps.creation_date supplier_created
          , apss.vendor_site_code
          , apss.vendor_site_code_alt supplier_
          , apss.creation_date site_created
          , ieba.creation_date bank_created
          , (select count(*) from iby_ext_bank_accounts ieba2 where ieba2.bank_account_num = ieba.bank_account_num) account_count
          , (select min(creation_date) from iby_ext_bank_accounts ieba2 where ieba2.bank_account_num = ieba.bank_account_num) min_cr_date
          , fu.user_name bank_created_by
          , (select count(distinct apss2.vendor_site_id) from ap_supplier_sites_all apss2 where apss2.vendor_id = aps.vendor_id) site_count
          , NULL
          , ieba.BRANCH_ID
          , iebb.branch_party_id
          , ieba.BANK_ID
          , iao.ACCOUNT_OWNER_PARTY_ID
          , apss.vendor_site_id
          , apss.party_site_id
          , NULL
          , ieba.ext_bank_account_id
          , ieba.bank_account_num
          , ieba.bank_account_name
          , ieba.IBAN
          , ieba.CURRENCY_CODE
       FROM ap_suppliers aps
       JOIN ap_supplier_sites_all apss        ON aps.vendor_id = apss.vendor_id
       JOIN iby_account_owners iao            ON iao.account_owner_party_id = aps.party_id
       JOIN iby_ext_bank_accounts ieba        ON ieba.ext_bank_account_id = iao.ext_bank_account_id
       JOIN iby_pmt_instr_uses_all ipiua      ON ipiua.instrument_id = ieba.ext_bank_account_id
       JOIN iby_ext_bank_branches_v iebb      ON ieba.branch_id = iebb.branch_party_id
       JOIN iby_external_payees_all iepa      ON ipiua.EXT_PMT_PARTY_ID = iepa.EXT_PAYEE_ID AND iepa.payee_party_id = aps.party_id AND iepa.party_site_id  = apss.party_site_id
       JOIN iby_ext_banks_v ieb               ON ieb.bank_party_id = iebb.bank_party_id and ieba.bank_id = ieb.bank_party_id
       JOIN hz_parties hz_bank                ON hz_bank.party_id = ieb.bank_party_id
       JOIN hz_parties hz_branch              ON hz_branch.party_id = iebb.branch_party_id
       join fnd_user fu on ieba.created_by = fu.user_id
      WHERE 1 = 1
        AND aps.vendor_id = 1111111
--        AND apss.vendor_site_code_alt = '1111111'
--        AND ieba.created_by = 0
--        AND (select min(creation_date) from iby_ext_bank_accounts ieba2 where ieba2.bank_account_num = ieba.bank_account_num) = ieba.creation_date
--        AND ieba.bank_account_num = '00000000'
--        AND iao.ACCOUNT_OWNER_PARTY_ID = 1111111
        AND 1 = 1
        order by ieba.creation_date desc;

BANK ACCOUNTS AGAINST SUPPLIERS V2

-- ##############################################################################
--        BANK ACCOUNTS AGAINST SUPPLIERS V2
-- ##############################################################################

               SELECT aps.vendor_name "VERDOR NAME"
                    , aps.vendor_id
                    , aps.creation_date supplier_created
                    , aps.end_date_active suppler_end_date
                    , fu_supplier.user_name || ' (' || fu_supplier.description || ')' supplier_created_by
                    , aps.created_by supplier_cr_by
                    , '####################################'
                    , apss.vendor_site_code "VENDOR SITE CODE"
                    , apss.inactive_date site_end_date
                    , apss.vendor_site_id
                    , apss.party_site_id
                    , apss.creation_date site_created
                    , apss.VENDOR_SITE_CODE_ALT
                    , '####################################'
                    , ieba.bank_id
                    , ieb.bank_name "BANK NAME"
                    , ieb.BANK_NUMBER
                    , ieb.bank_party_id
                    , '####################################'
                    , iebb.branch_party_id
                    , iebb.BRANCH_PARTY_ID
                    , '####################################'
                    , iebb.branch_number "BRANCH NUMBER"
                    , '####################################'
                    , ieba.EXT_BANK_ACCOUNT_ID
                    , ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER"
                    , ieba.BANK_ACCOUNT_NAME "BANK ACCOUNT NAME"
                    , ieba.branch_id
                    , ieba.creation_date ieba_created
                    , fu_ieba.user_name ieba_cr_by
                    , ipiua.INSTRUMENT_PAYMENT_USE_ID
                    , ieba.IBAN
                    , ieba.currency_code
                    , '####################################'
                    , apss.creation_date supplier_site_created
                    , apss.created_by supplier_site_cr_by
                    , iao.creation_date iby_account_owners_created
                    , iao.created_by iby_account_owners_cr_by
                    , iao.ACCOUNT_OWNER_PARTY_ID
                    , hz_bank.creation_date bank_party_created
                    , hz_bank.created_by bank_party_cr_by
                    , fu_bank.user_name bank_created_by
                    , hz_branch.creation_date bank_site_party_created
                    , hz_branch.created_by bank_site_party_cr_by
                    , fu_branch.user_name branch_created_by
                    , '#####################################'
                    , ipiua.start_date
                    , ipiua.end_date
                 FROM ap_suppliers aps
             JOIN ap_supplier_sites_all apss        ON aps.vendor_id = apss.vendor_id
            JOIN iby_account_owners iao            ON iao.account_owner_party_id = aps.party_id
            JOIN iby_ext_bank_accounts ieba        ON ieba.ext_bank_account_id = iao.ext_bank_account_id
            JOIN iby_pmt_instr_uses_all ipiua      ON ipiua.instrument_id = ieba.ext_bank_account_id
            JOIN iby_ext_bank_branches_v iebb      ON ieba.branch_id = iebb.branch_party_id
            JOIN iby_external_payees_all iepa      ON ipiua.EXT_PMT_PARTY_ID = iepa.EXT_PAYEE_ID AND iepa.payee_party_id = aps.party_id AND iepa.party_site_id  = apss.party_site_id
            JOIN iby_ext_banks_v ieb               ON ieb.bank_party_id = iebb.bank_party_id and ieba.bank_id = ieb.bank_party_id
            JOIN hz_parties hz_bank                ON hz_bank.party_id = ieb.bank_party_id
            JOIN hz_parties hz_branch              ON hz_branch.party_id = iebb.branch_party_id
           JOIN fnd_user fu_bank                  ON hz_bank.created_by = fu_bank.user_id
            JOIN fnd_user fu_branch                ON hz_branch.created_by = fu_branch.user_id
            JOIN fnd_user fu_supplier              ON aps.created_by = fu_supplier.user_id
            JOIN fnd_user fu_ieba                  ON ieba.created_by = fu_ieba.user_id
                WHERE 1 = 1
--                  and iao.creation_date > '20-MAY-2017'
--                  AND aps.vendor_name = 'Cheezes For U'
--                  AND aps.segment1 = 1111111
--                  AND iebb.branch_number = '000000'
--                  AND apss.vendor_site_code_alt = '1111111'
--                  and aps.vendor_id = 1111111
                  and ieba.BANK_ACCOUNT_NAME is null
--                  and ieba.BANK_ID = 1111111
--                  and ieba.bank_account_num = '1111111'
--                  and ieba.BRANCH_ID = 1111111
--                  AND ieba.ext_bank_account_id IN (1111111)
--                  AND ieba.creation_date > '01-APR-2017'
--                  AND ieba.created_by = 0
              --    and aps.created_by != 0
              --    and hz_bank.creation_date > '01-JAN-2017'
              --    and hz_branch.creation_date > '01-JAN-2017'
                  AND 1 = 1
                  order by aps.creation_date desc;

BANK ACCOUNTS AGAINST SUPPLIERS V3

-- ##############################################################################
--        BANK ACCOUNTS AGAINST SUPPLIERS V3
-- ##############################################################################

     SELECT distinct 
        aps.vendor_name "VERDOR NAME"
      , aps.creation_date supplier_created
      , aps.created_by supplier_cr_by
      , apss.vendor_site_code "VENDOR SITE CODE"
      , apss.vendor_site_id
      , ieb.bank_name "BANK NAME"
      , iebb.bank_branch_name "BANK BRANCH NAME"
      , iebb.branch_number "BRANCH NUMBER"
      , ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER"
      , ieba.BANK_ACCOUNT_NAME "BANK ACCOUNT NAME"
      , iebb.branch_party_id
      , ieb.bank_party_id
      , apss.creation_date supplier_site_created
      , apss.created_by supplier_site_cr_by
      , ieba.creation_date iby_ext_bank_accounts_created
      , ieba.created_by iby_ext_bank_accounts_cr_by
      , iao.creation_date iby_account_owners_created
      , iao.created_by iby_account_owners_cr_by
      , hz_bank.creation_date bank_party_created
      , hz_bank.created_by bank_party_cr_by
      , fu_bank.user_name bank_created_by
      , hz_branch.creation_date bank_site_party_created
      , hz_branch.created_by bank_site_party_cr_by
      , fu_branch.user_name branch_created_by
   FROM ap_suppliers aps
      , ap_supplier_sites_all apss
      , iby_ext_bank_accounts ieba
      , iby_account_owners iao
      , iby_ext_banks_v ieb
      , iby_ext_bank_branches_v iebb
      , hz_parties hz_bank
      , hz_parties hz_branch
      , fnd_user fu_bank
      , fnd_user fu_branch
  WHERE aps.vendor_id = apss.vendor_id 
    and iao.account_owner_party_id = aps.party_id 
    and ieba.ext_bank_account_id = iao.ext_bank_account_id
    and ieb.bank_party_id = iebb.bank_party_id
    and ieba.branch_id = iebb.branch_party_id
    and ieba.bank_id = ieb.bank_party_id
    AND hz_bank.party_id = ieb.bank_party_id
    AND hz_branch.party_id = iebb.branch_party_id
    and hz_bank.created_by = fu_bank.user_id
    and hz_branch.created_by = fu_branch.user_id
  	AND 1 = 1
--    and aps.creation_date > '01-JAN-2016'
--    AND lower(aps.vendor_name) LIKE '%cheese%'
--    AND ieba.ext_bank_account_id IN (1111111)
    and aps.vendor_id = 1111111
--    and aps.created_by != 0
--    and hz_bank.creation_date > '01-JAN-2017'
--    and hz_branch.creation_date > '01-JAN-2017'
	  AND 1 = 1
    order by aps.creation_date desc;

BANK ACCOUNTS AGAINST SUPPLIERS V4

-- ##############################################################################
--        BANK ACCOUNTS AGAINST SUPPLIERS V4
-- ##############################################################################

-- http://onlyappsr12.blogspot.co.uk/2010/07/r12-supplier-bank-accounts_5090.html

 SELECT hp.party_name supplier_name
      , sup.segment1 supplier_number
      , assa.vendor_site_code supplier_site
      , ieb.bank_account_num
      , ieb.bank_account_name
      , ieb.currency_code
      , party_bank.party_name bank_name
      , branch_prof.bank_or_branch_number bank_number
      , party_branch.party_name branch_name
      , branch_prof.bank_or_branch_number branch_number
  FROM hz_parties hp
     , ap_suppliers sup
     , hz_party_sites hps
     , ap_supplier_sites_all assa
     , iby_external_payees_all iep
     , iby_pmt_instr_uses_all ipi
     , iby_ext_bank_accounts ieb
     , hz_parties party_bank
     , hz_parties party_branch
     , hz_organization_profiles bank_prof
     , hz_organization_profiles branch_prof
WHERE hp.party_id = sup.party_id
AND hp.party_id = hps.party_id
AND hps.party_site_id = assa.party_site_id
AND assa.vendor_id = sup.vendor_id
AND iep.payee_party_id = hp.party_id
AND iep.party_site_id = hps.party_site_id
AND iep.supplier_site_id = assa.vendor_site_id
AND iep.ext_payee_id = ipi.ext_pmt_party_id
AND ipi.instrument_id = ieb.ext_bank_account_id
AND ieb.bank_id = party_bank.party_id
AND ieb.bank_id = party_branch.party_id
AND party_branch.party_id = branch_prof.party_id
AND party_bank.party_id = bank_prof.party_id
--AND sup.vendor_name = 'Cheezes For U'
--AND ieb.ext_bank_account_id IN (1111111)
--and sup.vendor_id = 1111111
and ieb.bank_account_num = '00000000'
ORDER BY 1,3;

COUNT OF SETUPS PER SUPPLIER

-- ##############################################################################
--        COUNT OF SETUPS PER SUPPLIER
-- ##############################################################################
     SELECT aps.vendor_name
          , aps.vendor_id
          , count(*) ct                
       FROM ap_suppliers aps
       JOIN ap_supplier_sites_all apss        ON aps.vendor_id = apss.vendor_id
       JOIN iby_account_owners iao            ON iao.account_owner_party_id = aps.party_id
       JOIN iby_ext_bank_accounts ieba        ON ieba.ext_bank_account_id = iao.ext_bank_account_id
       JOIN iby_pmt_instr_uses_all ipiua      ON ipiua.instrument_id = ieba.ext_bank_account_id
       JOIN iby_ext_bank_branches_v iebb      ON ieba.branch_id = iebb.branch_party_id
       JOIN iby_external_payees_all iepa      ON ipiua.EXT_PMT_PARTY_ID = iepa.EXT_PAYEE_ID AND iepa.payee_party_id = aps.party_id AND iepa.party_site_id  = apss.party_site_id
       JOIN iby_ext_banks_v ieb               ON ieb.bank_party_id = iebb.bank_party_id and ieba.bank_id = ieb.bank_party_id
       JOIN hz_parties hz_bank                ON hz_bank.party_id = ieb.bank_party_id
       JOIN hz_parties hz_branch              ON hz_branch.party_id = iebb.branch_party_id
      WHERE 1 = 1
        AND aps.vendor_name = 'Cheezes For U'
        AND 1 = 1
   group by aps.VENDOR_NAME
          , aps.vendor_id
   order by aps.VENDOR_NAME;

BANK ACCOUNTS WITH NULL AND UKS CURRENCY_CODES

-- ##############################################################################
--        BANK ACCOUNTS WITH NULL AND UKS CURRENCY_CODES
--        https://community.oracle.com/message/14390944#14390944
-- ##############################################################################

select distinct bank_account_num
from (
  select bank_account_num
       , count(case when currency_code is null then 1 end) over (partition by bank_account_num) null_count
       , count(case when currency_code = 'UKS' then 1 end) over (partition by bank_account_num) uks_count
    from iby_ext_bank_accounts ieba
    )
where null_count > 0 and uks_count > 0
order by bank_account_num;

EXTERNAL BANK ACCOUNTS – BASIC DETAILS WITH SETUP INFO

-- ##############################################################################
--        EXTERNAL BANK ACCOUNTS - BASIC DETAILS WITH SETUP INFO
-- ##############################################################################

     select ieba.ext_bank_account_id
               , ieba.bank_account_num
               , ieba.bank_account_name
               , ieba.currency_code
               , ieba.iban
               , ieba.branch_id
               , ieba.BANK_ID
               , ieba.creation_date cr_dt
               , cr.user_name cr_by
               , ieba.last_update_date up_dt
               , up.user_name up_by
               , ieba.start_date
               , ieba.end_date
            from iby_ext_bank_accounts ieba
            join fnd_user cr on ieba.created_by = cr.user_id
            join fnd_user up on ieba.last_updated_by = up.user_id
           where 1 = 1
             AND ieba.BANK_ACCOUNT_NUM = '00000000'
        ORDER BY ieba.bank_account_name
               , ieba.bank_account_num;

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 Payment Process Requests and Payment Templates

Sections:

  1. Payment Process Request Templates
  2. Payment Process Request Templates – with links to Payment Runs
  3. Payment Runs
  4. Payment Runs – Group by Payment Process Profiles
  5. Payment Runs – Group by Payment Process Templates
  6. Payment Documents

Payment Process Request Templates

-- ##############################################################################
--      PPR TEMPLATES, PAYMENT PROCESS PROFILES, PAYMENT INSTRUCTION FORMATS AND XML PUBLISHER TEMPLATES
-- ##############################################################################

-- ppr templates
-- ppr > payment process profile (ppp)
--   contains things like outbound directory, file prefix and file extension
-- ppp > payment instruction format (pif)
--   links to type and data extract
--   links to xml publisher template, which generates the output format for the payment file

     select apt.template_name ppr_template
          , (select count(*) from ap_inv_selection_criteria_all aisc where apt.template_id = aisc.template_id and aisc.status = 'SELECTED') ppr_count
          , apt.description
          , apt.payment_method_code
          , '#' || cba.bank_account_num bank_acct
          , cbv.bank_name bank
          , cba.bank_account_name bank_acct_name
          , cbbv.branch_number branch
          , cbbv.bank_branch_name
          , cpd.payment_document_name payment_doc
          , iappt.payment_profile_name payment_process_profile
          , apt.payment_exchange_rate_type xchng_rate
          , isppv.system_profile_description ppp_description
          , isppv.outbound_pmt_file_directory ppp_out_directory
          , isppv.outbound_pmt_file_extension ppp_extn
          , isppv.outbound_pmt_file_prefix ppp_prefix
          , ift.format_name payment_instruction_format
          , flv_pif.meaning pif_type
          , iet.extract_desc pif_data_extract
          , xtv.template_name xml_template
       from ap_payment_templates apt
       join ce_bank_accounts cba on apt.bank_account_id = cba.bank_account_id
       join ce_banks_v cbv on cba.bank_id = cbv.bank_party_id
       join ce_bank_branches_v cbbv on cbbv.branch_party_id = cba.bank_branch_id
       join iby_acct_pmt_profiles_b iapp on iapp.payment_profile_id = apt.payment_profile_id
       join iby_acct_pmt_profiles_tl iappt on iappt.payment_profile_id = iapp.payment_profile_id
       join iby_sys_pmt_profiles_vl isppv on isppv.system_profile_code = iapp.system_profile_code
       join iby_formats_b ifb on ifb.format_code = isppv.payment_format_code
       join iby_formats_tl ift on ifb.format_code = ift.format_code
  left join ce_payment_documents cpd on cpd.payment_document_id = apt.payment_document_id
  left join xdo_templates_vl xtv on ifb.format_template_code = xtv.template_code
  left join fnd_lookup_values flv_pif on flv_pif.lookup_code = ifb.format_type_code and flv_pif.lookup_type = 'IBY_FORMAT_TYPES'
  left join iby_extracts_b ieb on ieb.extract_id = ifb.extract_id
  left join iby_extracts_tl iet on iet.extract_id = ieb.extract_id;

Payment Process Request Templates – with links to Payment Runs

-- ##############################################################################
--      PPR TEMPLATES, PAYMENT PROCESS PROFILES, PAYMENT INSTRUCTION FORMATS AND XML PUBLISHER TEMPLATES
--      WITH LINKS TO PAYMENT RUNS
-- ##############################################################################

     select apt.template_name
--          , (select count(*) from ap_inv_selection_criteria_all aisc where apt.template_id = aisc.template_id and aisc.status = 'selected') ppr_count
          , apt.description
          , apt.payment_method_code
--          , cba.bank_account_name
          , cba.bank_account_num
--          , cbv.bank_name
--          , cbbv.branch_number
--          , cbbv.bank_branch_name
          , iappt.payment_profile_name payment_process_profile
          , isppv.system_profile_description ppp_description
          , isppv.outbound_pmt_file_directory ppp_out_directory
          , isppv.outbound_pmt_file_extension ppp_extn
          , isppv.outbound_pmt_file_prefix ppp_prefix
          , ift.format_name payment_instruction_format
          , flv_pif.meaning pif_type
          , iet.extract_desc pif_data_extract
          , xtv.template_name xml_template
          , aisc.checkrun_name
          , aisc.creation_date
          , fu.user_name
          , aisc.check_date
          , aisc.pay_thru_date
          , aisc.pay_group_option
       from ap_payment_templates apt
       join ce_bank_accounts cba on apt.bank_account_id = cba.bank_account_id
       join ce_banks_v cbv on cba.bank_id = cbv.bank_party_id
       join ce_bank_branches_v cbbv on cbbv.branch_party_id = cba.bank_branch_id
       join iby_acct_pmt_profiles_b iapp on iapp.payment_profile_id = apt.payment_profile_id
       join iby_acct_pmt_profiles_tl iappt on iappt.payment_profile_id = iapp.payment_profile_id
       join iby_sys_pmt_profiles_vl isppv on isppv.system_profile_code = iapp.system_profile_code
       join iby_formats_b ifb on ifb.format_code = isppv.payment_format_code
       join iby_formats_tl ift on ifb.format_code = ift.format_code
  left join xdo_templates_vl xtv on ifb.format_template_code = xtv.template_code
  left join fnd_lookup_values flv_pif on flv_pif.lookup_code = ifb.format_type_code and flv_pif.lookup_type = 'iby_format_types'
  left join iby_extracts_b ieb on ieb.extract_id = ifb.extract_id
  left join iby_extracts_tl iet on iet.extract_id = ieb.extract_id
       join ap_inv_selection_criteria_all aisc on apt.template_id = aisc.template_id
       join fnd_user fu on fu.user_id = aisc.created_by
      where aisc.creation_date > '01-NOV-2017'
        and aisc.status = 'SELECTED';

Payment Runs

-- ##############################################################################
--      PAYMENT RUNS 
-- ##############################################################################

	-- details linking from payment run, back to invoices

	  select aisca.checkrun_name
		   , aisca.check_date
		   , aisca.creation_date
		   , aisca.pay_thru_date
		   , aisca.status
		   , aisca.checkrun_id
		   , aisca.vendor_id
		   , apt.template_name
		   , iapp.system_profile_code
		   , aca.amount
		   , aca.bank_account_name
		   , aca.check_number
		   , aca.currency_code
		   , aca.vendor_name
		   , aca.remit_to_supplier_name
		   , aca.remit_to_supplier_site
		   , aca.payment_method_code
		   , aia.invoice_id
		   , aia.invoice_num
		   , aia.invoice_amount
		from ap_inv_selection_criteria_all aisca
		join iby_acct_pmt_profiles_b iapp on iapp.payment_profile_id = aisca.payment_profile_id
		join ap_payment_templates apt on apt.template_id = aisca.template_id
		join ap_checks_all aca on aca.checkrun_id = aisca.checkrun_id
		join ap_invoice_payments_all aipa on aipa.check_id = aca.check_id
		join ap_invoices_all aia on aia.invoice_id = aipa.invoice_id
	   where aisca.creation_date > '01-MAR-2017'
	order by aisca.creation_date desc;

Payment Runs – Group by Payment Process Profiles

-- ##############################################################################
--      PAYMENT RUNS GROUPED BY PAYMENT PROFILES
-- ##############################################################################
   
     select iappt.payment_profile_name
          , max(aisc.creation_date) last_used
          , count(*) ct
       from apps.ap_payment_templates apt
          , apps.iby_acct_pmt_profiles_b iapp
          , apps.ap_inv_selection_criteria_all aisc
          , apps.iby_acct_pmt_profiles_tl iappt
          , apps.iby_acct_pmt_profiles_b iappb
          , apps.iby_sys_pmt_profiles_vl isppv
          , apps.iby_formats_tl ift
      where 1 = 1
        and apt.payment_profile_id = iapp.payment_profile_id
        and apt.template_id = aisc.template_id
        and iapp.payment_profile_id = aisc.payment_profile_id
        and aisc.payment_profile_id = iappt.payment_profile_id
        and iappt.payment_profile_id = iappb.payment_profile_id
        and iappb.system_profile_code = isppv.system_profile_code
        and trunc(sysdate) <= trunc(nvl(apt.inactive_date,sysdate))
        and trunc(sysdate) <= trunc(nvl(iapp.inactive_date,sysdate))
--        and aisc.creation_date between '01-JAN-2017' and '21-SEP-2017'
        and aisc.status = 'SELECTED'
   group by iappt.payment_profile_name;

Payment Runs - Group by Payment Process Templates

-- ##############################################################################
--      PAYMENT RUNS GROUPED BY PAYMENT TEMPLATES
-- ##############################################################################
   
     select apt.template_name
          , max(aisc.creation_date) last_used
          , count(distinct aisc.checkrun_id) ppr_count
       from ap_payment_templates apt
	  join iby_acct_pmt_profiles_b iapp on apt.payment_profile_id = iapp.payment_profile_id
       join ap_inv_selection_criteria_all aisc on apt.template_id = aisc.template_id and iapp.payment_profile_id = aisc.payment_profile_id
      where 1 = 1
        and trunc(sysdate) <= trunc(nvl(apt.inactive_date,sysdate))
        and trunc(sysdate) <= trunc(nvl(iapp.inactive_date,sysdate))
        and aisc.creation_date between '01-MAR-2017' and '20-NOV-2017'
        and aisc.status = 'SELECTED'
   group by apt.template_name
   order by 3 desc;

-- including invoice count

     select apt.template_name
          , max(aisc.creation_date) last_used
          , count(distinct aisc.checkrun_id) ppr_count
          , count(distinct aia.invoice_id) invoice_count
       from ap_payment_templates apt
	  join iby_acct_pmt_profiles_b iapp on apt.payment_profile_id = iapp.payment_profile_id
       join ap_inv_selection_criteria_all aisc on apt.template_id = aisc.template_id and iapp.payment_profile_id = aisc.payment_profile_id
	  join ap_checks_all aca on aca.checkrun_id = aisc.checkrun_id
	  join ap_invoice_payments_all aipa on aipa.check_id = aca.check_id
	  join ap_invoices_all aia on aia.invoice_id = aipa.invoice_id
      where 1 = 1
        and trunc(sysdate) <= trunc(nvl(apt.inactive_date,sysdate))
        and trunc(sysdate) <= trunc(nvl(iapp.inactive_date,sysdate))
--        and aisc.creation_date between '01-MAR-2017' and '20-NOV-2017'
        and aisc.status = 'SELECTED'
   group by apt.template_name
   order by 3 desc;

-- count with bank account details

          select apt.template_name
               , cba.bank_account_name
               , cba.bank_account_num
               , cbv.bank_name
               , cbbv.branch_number
               , cbbv.bank_branch_name
               , max(aisc.creation_date) last_used
               , count(distinct aisc.checkrun_id) payment_run_count
            from apps.ap_payment_templates apt
       left join apps.iby_acct_pmt_profiles_b iapp on apt.payment_profile_id = iapp.payment_profile_id
       left join apps.ap_inv_selection_criteria_all aisc on apt.template_id = aisc.template_id
       left join ce_bank_accounts cba on apt.bank_account_id = cba.bank_account_id
       left join ce_banks_v cbv on cba.bank_id = cbv.bank_party_id
       left join ce_bank_branches_v cbbv on cbbv.branch_party_id = cba.bank_branch_id
           where 1 = 1
--             and trunc(sysdate) <= trunc(nvl(apt.inactive_date,sysdate))
--             and trunc(sysdate) <= trunc(nvl(iapp.inactive_date,sysdate))
--             and iapp.payment_profile_id = aisc.payment_profile_id
     --        and aisc.creation_date between '01-JUN-2009' and '20-NOV-2017'
--             and aisc.status = 'SELECTED'
        group by apt.template_name
               , cba.bank_account_name
               , cba.bank_account_num
               , cbv.bank_name
               , cbbv.branch_number
               , cbbv.bank_branch_name
        order by 3 desc;

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 Invoices – Tax Info

Sections:

  1. BASIC INVOICE DETAILS WITH LINES V1
  2. BASIC INVOICE DETAILS WITH LINES V2
  3. BASIC INVOICE DETAILS WITH LINES V3
  4. SELF ASSESSED TAX LINES

A number of SQL statements to drill down to tax info associated with AP invoices. The key tax table is zx_lines_v.

BASIC INVOICE DETAILS WITH LINES V1

-- ##############################################################################
--      BASIC INVOICE DETAILS WITH LINES V1
-- ##############################################################################

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
     , 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
     , 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
     , 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
     , AILA.TAX
     , AILA.TAX_RATE
     , AILA.TAX_REGIME_CODE
     , AILA.TAX_STATUS_CODE
     , AILA.PERIOD_NAME
     , '------ ZX_LINES_V -----'
     , zlv.tax_full_name
     , zlv.tax_rate
     , zlv.tax_rate_code
     , zlv.line_amt
     , zlv.taxable_amt
     , ZLV.tax_amt
     , zlv.tax_line_id
--     , '###################'
--     , AILA.*
--     , (SELECT DISTINCT gps.period_name from gl.gl_period_statuses gps WHERE gps.application_id = 200 AND aia.creation_date BETWEEN gps.start_date AND gps.end_date) period
--     , '###################'
  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
  JOIN apps.zx_lines_v zlv           ON aia.invoice_id =     zlv.trx_id AND zlv.application_id = 200 AND zlv.EVENT_CLASS_CODE = 'STANDARD INVOICES' AND zlv.ENTITY_CODE = 'AP_INVOICES' AND ZLV.TRX_LINE_NUMBER = AILA.LINE_NUMBER AND zlv.trx_id = aia.invoice_id
 WHERE 1 = 1
--   AND aia.invoice_id IN (5876507)                   -- #--       ID   ## --
--   AND zlv.tax_line_id = 18776263
--   AND aia.creation_date BETWEEN '01-SEP-2016' AND '30-SEP-2016'
--   AND (SELECT DISTINCT gps.period_name from gl.gl_period_statuses gps WHERE gps.application_id = 200 AND aia.creation_date BETWEEN gps.start_date AND gps.end_date) = 'JAN-16'
--   AND aila.tax_classification_code <> zlv.tax_rate_code
--   AND aia.invoice_num IN ('123456')                     -- ## NO ## --
--   AND zlv.cancel_flag = 'N'
--   AND aia.invoice_num IN ('123456')                     -- ## NO ## --
   AND zlv.CANCEL_FLAG = 'Y'
   AND aila.period_name = 'JAN-16'
   AND zlv.tax_rate_code = 'T'
--   AND aia.doc_sequence_value IN (123456)         -- ## VOUCHER ## --
   AND 1 = 1;

BASIC INVOICE DETAILS WITH LINES V2

-- ##############################################################################
--      BASIC INVOICE DETAILS WITH LINES V2
-- ##############################################################################

SELECT aia.invoice_id id
     , aia.invoice_num num
     , aia.creation_date
--     , aia.cancelled_date
     , aia.invoice_type_lookup_code inv_type
     , 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
     , 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
     , aia.payment_status_flag paid
     , pv.vendor_name supplier
     , pvsa.vendor_site_code site
     , aia.invoice_amount
     , aila.tax_classification_code
     , AILA.PERIOD_NAME
     , zlv.tax_full_name
     , zlv.tax_rate
     , zlv.tax_rate_code
     , zlv.line_amt
     , zlv.taxable_amt
     , ZLV.tax_amt
     , '##############'
     , zlv.*
  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
  JOIN apps.zx_lines_v zlv           ON aia.invoice_id =     zlv.trx_id AND zlv.application_id = 200 AND zlv.EVENT_CLASS_CODE = 'STANDARD INVOICES' AND zlv.ENTITY_CODE = 'AP_INVOICES' AND ZLV.TRX_LINE_NUMBER = AILA.LINE_NUMBER AND zlv.trx_id = aia.invoice_id
 WHERE 1 = 1
   AND AILA.PERIOD_NAME = 'APR-16'
--   AND aia.invoice_num IN ('123456')                     -- ## NO ## --
   AND zlv.tax_rate_code = 'T'
   AND zlv.cancel_flag = 'N'
   AND 1 = 1;

BASIC INVOICE DETAILS WITH LINES V3

-- ##############################################################################
--      BASIC INVOICE DETAILS WITH LINES V3
-- ##############################################################################

SELECT aia.invoice_id id
     , aia.invoice_num num
     , aia.invoice_type_lookup_code inv_type
     , aia.cancelled_date
     , aia.invoice_date
--     , apps.ap_invoices_pkg.get_posting_status(aia.invoice_id) accounted
     , aia.payment_status_flag paid
     , pv.vendor_name supplier
     , pvsa.vendor_site_code site
     , aia.invoice_amount
     , aila.tax_classification_code
     , AILA.PERIOD_NAME
     , zlv.tax_full_name
--     , zlv.tax_rate
     , zlv.tax_rate_code
     , zlv.tax_regime_code
     , sum(zlv.line_amt) sum_line_amt
     , sum(zlv.tax_amt) sum_taxable_amt
  FROM ap.ap_invoices_all aia
  JOIN ap.ap_invoice_lines_all aila  ON aia.invoice_id =     aila.invoice_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
  JOIN apps.zx_lines_v zlv           ON aia.invoice_id =     zlv.trx_id AND zlv.application_id = 200 AND zlv.EVENT_CLASS_CODE = 'STANDARD INVOICES' AND zlv.ENTITY_CODE = 'AP_INVOICES' AND ZLV.TRX_LINE_NUMBER = AILA.LINE_NUMBER AND zlv.trx_id = aia.invoice_id
 WHERE 1 = 1
   AND aila.period_name = 'APR-16'
--   AND aia.invoice_num IN ('8.87-290515')                     -- ## NO ## --
   AND aia.creation_date BETWEEN '01-APR-2016' AND '30-APR-2016'
--   AND aia.invoice_id IN (5685540)
--   AND aia.invoice_num = '123456'
   AND zlv.tax_rate_code = 'T'
   AND zlv.tax_regime_code = 'GB-Tax'
   AND zlv.cancel_flag = 'N'
   AND aia.cancelled_date IS NULL
   AND aia.legal_entity_id = 123
GROUP BY aia.invoice_id
     , aia.invoice_num
     , aia.invoice_type_lookup_code
     , pv.vendor_name
     , pvsa.vendor_site_code
     , aia.invoice_amount
     , aila.tax_classification_code
     , aila.period_name
     , zlv.tax_full_name
     , aia.invoice_date
     , zlv.tax_regime_code
--     , zlv.tax_rate
     , zlv.tax_rate_code
     , aia.cancelled_date
--     , apps.ap_invoices_pkg.get_posting_status(aia.invoice_id)
     , aia.payment_status_flag;
     
SELECT sum(zlv.line_amt) sum_line_amt
     , sum(zlv.tax_amt) sum_taxable_amt
  FROM ap.ap_invoices_all aia
  JOIN ap.ap_invoice_lines_all aila  ON aia.invoice_id =     aila.invoice_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
  JOIN apps.zx_lines_v zlv           ON aia.invoice_id =     zlv.trx_id AND zlv.application_id = 200 AND zlv.EVENT_CLASS_CODE = 'STANDARD INVOICES' AND zlv.ENTITY_CODE = 'AP_INVOICES' AND ZLV.TRX_LINE_NUMBER = AILA.LINE_NUMBER AND zlv.trx_id = aia.invoice_id
 WHERE 1 = 1
   AND aila.period_name = 'JUL-16'
   AND zlv.tax_rate_code = 'F'
   AND zlv.tax_regime_code = 'GB-Tax'
   AND zlv.cancel_flag = 'N'
--   AND aia.cancelled_date IS NULL
   AND aia.legal_entity_id = 103;

SELF ASSESSED TAX LINES

-- ##############################################################################
--      SELF ASSESSED TAX LINES
-- ##############################################################################

select * 
  from zx_lines_summary_v tax
 WHERE 1 = 1
--   AND tax.trx_number = '256618' 
--   AND tax.TRX_ID = 322971
   AND tax.entity_code = 'AP_INVOICES'
   AND tax.EVENT_CLASS_CODE = 'STANDARD INVOICES'
   AND tax.SELF_ASSESSED_FLAG = 'Y';
   
SELECT distinct
        aia.invoice_id id
      , aia.invoice_num num
      , pha.segment1 po
      , pha.authorization_status
      , aia.creation_date
      , aia.invoice_amount amt
      , aia.invoice_type_lookup_code inv_type
      , pv.vendor_name supplier
      , pvsa.vendor_site_code site
      , 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
  from zx_lines_summary_v tax
  JOIN ap.ap_invoices_all aia ON tax.trx_id = aia.invoice_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 ap.ap_invoice_distributions_all aida ON aia.invoice_id = aida.invoice_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
 WHERE 1 = 1
--   AND tax.trx_number = '123456' 
--   AND tax.TRX_ID = 322971
   AND tax.entity_code = 'AP_INVOICES'
   AND tax.EVENT_CLASS_CODE = 'STANDARD INVOICES'
   AND tax.SELF_ASSESSED_FLAG = 'Y';

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;