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 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 V0
  2. Bank Accounts Against Suppliers V1
  3. Bank Accounts Against Suppliers V2
  4. Bank Accounts Against Suppliers V3
  5. Bank Accounts Against Suppliers V4
  6. Bank Accounts Against Suppliers V5
  7. Count Of Setups Per Supplier
  8. Bank Accounts With NULL and UKS Currency Codes
  9. External Bank Accounts – Basic Details With Setup Info

Bank Accounts Against Suppliers V0

-- ##############################################################################
--        BANK ACCOUNTS AGAINST SUPPLIERS V0
-- ##############################################################################

SELECT sup.segment1
     , sup.vendor_name
     , sup.vendor_id
     , epa.org_id
     , ss.vendor_site_id
     , ss.vendor_site_code
     , eba.bank_account_num
     , iebb.branch_number branch
     , piu.creation_date
     , piu.order_of_preference priority
     , eba.ext_bank_account_id
  FROM ap_suppliers sup
     , ap_supplier_sites_all ss
     , iby_external_payees_all epa
     , iby_pmt_instr_uses_all  piu
     , iby_ext_bank_accounts   eba
     , iby_ext_bank_branches_v iebb
 WHERE sup.vendor_id     = ss.vendor_id
   AND ss.vendor_site_id = epa.supplier_site_id
   AND epa.ext_payee_id  = piu.ext_pmt_party_id      
   AND piu.instrument_id = eba.ext_bank_account_id
   AND eba.branch_id = iebb.branch_party_id
   AND eba.bank_account_num = 'XXXXXXXX'
   and 1 = 1;

Bank Accounts Against Suppliers V1

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

     select aps.vendor_name supplier
          , aps.segment1 supp_num
          , aps.vendor_id
          , apss.vendor_site_code site
          , apss.vendor_site_code_alt cogs_ref
          , '#' || ieba.bank_account_num acct_num
          , ieba.bank_account_name acct_name
          , ieb.bank_name bank
          , iebb.branch_number branch
          , ieba.last_update_date bank_account_updated
          , fu.user_name bank_account_updated_by
       from ap_suppliers aps
       join ap_supplier_sites_all apss   on aps.vendor_id = apss.vendor_id
       join iby_external_payees_all iepa on iepa.supplier_site_id = apss.vendor_site_id
       join iby_pmt_instr_uses_all ipiua on ipiua.ext_pmt_party_id = iepa.ext_payee_id
       join iby_ext_bank_accounts ieba   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_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.last_updated_by = fu.user_id
      where 1 = 1
--        and ieba.masked_bank_account_num = 'XXXXXXXX'
        and ieba.bank_account_num = '1111111'
        and 1 = 1;

Bank Accounts Against Suppliers V2

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

     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
          , 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 V3

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

               SELECT aps.vendor_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
                    , 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
                    , ieb.bank_number
                    , ieb.bank_party_id
                    , '####################################'
                    , iebb.branch_party_id
                    , iebb.branch_party_id
                    , '####################################'
                    , iebb.branch_number
                    , '####################################'
                    , ieba.ext_bank_account_id
                    , ieba.bank_account_num
                    , ieba.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 V4

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

 select distinct 
        aps.vendor_name
      , aps.creation_date supplier_created
      , aps.created_by supplier_cr_by
      , apss.vendor_site_code
      , apss.vendor_site_id
      , ieb.bank_name
      , iebb.bank_branch_name
      , iebb.branch_number
      , ieba.bank_account_num
      , ieba.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 V5

-- ##############################################################################
--        BANK ACCOUNTS AGAINST SUPPLIERS V5
-- ##############################################################################

-- https://sanjaimisra.blogspot.co.uk/2011/12/supplier-bank-account-details.html
   
SELECT 'Bank Account At Supplier Site Level' Bank_Account_Level
     , sup.segment1
     , sup.vendor_name
     , epa.org_id
     , ss.vendor_site_code
     , NULL   Party_Site_Code
     , eba.bank_account_num
     , piu.order_of_preference priority
     , eba.ext_bank_account_id
  FROM ap_suppliers sup
     , ap_supplier_sites_all       ss
     , iby_external_payees_all epa
     , iby_pmt_instr_uses_all  piu
     , iby_ext_bank_accounts   eba
 WHERE sup.vendor_id     = ss.vendor_id
   AND ss.vendor_site_id = epa.supplier_site_id
   AND epa.ext_payee_id  = piu.ext_pmt_party_id      
   AND piu.instrument_id = eba.ext_bank_account_id
   AND sup.segment1      = '25442'
UNION
SELECT 'Bank Account at Supplier Level'
     , sup.segment1
     , sup.vendor_name
     , epa.org_id
     , NULL
     , NULL
     , eba.bank_account_num
     , piu.order_of_preference priority
     , eba.ext_bank_account_id
  FROM ap_suppliers sup
     , iby_external_payees_all epa
     , iby_pmt_instr_uses_all  piu
     , iby_ext_bank_accounts   eba
 WHERE sup.party_id        = epa.payee_party_id
   AND epa.ext_payee_id    = piu.ext_pmt_party_id      
   AND piu.instrument_id   = eba.ext_bank_account_id
   AND sup.segment1        = '25442'
   AND supplier_site_id    IS NULL
   AND party_site_id       IS NULL
UNION
SELECT 'Bank Account at Address + Opearting Unit Level'
     , sup.segment1
     , sup.vendor_name
     , epa.org_id
     , NULL
     , psite.party_site_name
     , eba.bank_account_num
     , piu.order_of_preference priority
     , eba.ext_bank_account_id
  FROM ap_suppliers            sup
     , hz_party_sites          psite
     , iby_external_payees_all epa
     , iby_pmt_instr_uses_all  piu
     , iby_ext_bank_accounts   eba
 WHERE sup.party_id        = psite.party_id
   AND psite.party_site_id = epa.party_site_id
   AND epa.ext_payee_id    = piu.ext_pmt_party_id      
   AND piu.instrument_id   = eba.ext_bank_account_id
   AND sup.segment1        = '25442'
   AND supplier_site_id    IS NULL
   AND epa.org_id          IS NOT NULL
UNION
SELECT 'Bank Account at Address Level'
     , sup.segment1
     , sup.vendor_name
     , epa.org_id
     , NULL
     , psite.party_site_name
     , eba.bank_account_num
     , piu.order_of_preference priority
     , eba.ext_bank_account_id
  FROM ap_suppliers            sup
     , hz_party_sites          psite
     , iby_external_payees_all epa
     , iby_pmt_instr_uses_all  piu
     , iby_ext_bank_accounts   eba
 WHERE sup.party_id        = psite.party_id
   AND psite.party_site_id = epa.party_site_id
   AND epa.ext_payee_id    = piu.ext_pmt_party_id      
   AND piu.instrument_id   = eba.ext_bank_account_id
   AND sup.segment1        = '25442'
   AND supplier_site_id    IS NULL
   AND epa.org_id          IS NULL
 ORDER BY bank_account_num;

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

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;

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';