AP Suppliers

Suppliers – Basic

-- ##############################################################################
--      SUPPLIERS - BASIC
-- ############################################################################*/
 
        SELECT bus_gp.NAME org
             , pv.segment1, pv.vendor_id
             , pv.vendor_name
             , pvsa.vendor_site_code site
             , pvsa.vendor_site_id
             , pvsa.org_id
             , pvsa.supplier_notif_method
             , pvsa.email_address email
             , pvsa.invoice_currency_code
             , pvsa.payment_currency_code
             , fu.description last_udated_by
             , pv.end_date_active header_end_date
             , pvsa.inactive_date site_end_date
          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 pvsa.vendor_site_code = :site_code
           AND pv.segment1 = :supplier_number
      ORDER BY bus_gp.NAME
             , pv.vendor_name
             , pvsa.vendor_site_code;

Suppliers – Contact Details

-- ##############################################################################
--      SUPPLIERS - INCLUDING CONTACT DETAILS
-- ############################################################################*/
 
    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 pv.end_date_active IS NULL
         AND pvsa.inactive_date IS NULL
         AND pv.vendor_name LIKE 'A%'
--         AND ((pvsa.email_address IS NOT NULL) OR (pvc.email_address IS NOT NULL))
    ORDER BY pv.vendor_name;

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 – Invoice and Purchase Order Count

-- ##############################################################################
--      SUPPLIERS - INVOICE AND PURCHASE ORDER 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
   WHERE NVL(pv.end_date_active, SYSDATE + 1) > SYSDATE
     AND NVL(pvsa.inactive_date, SYSDATE + 1) > SYSDATE
     AND pv.vendor_name LIKE 'Blue%'
     AND pv.vendor_type_lookup_code = 'VENDOR'
ORDER BY pv.vendor_name

Invoice Headers and 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;

Using Oracle SQL instead of ANSI SQL:

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

SELECT aia.invoice_id
     , aia.invoice_num
     , aia.invoice_amount
     , aia.invoice_date
     , ah.*
     , ah.hold_id
     , ah.release_lookup_code
     , ah.release_reason
     , ah.status_flag
     , ah.last_update_date
  FROM ap.ap_invoices_all aia
     , ap.ap_holds_all ah
 WHERE aia.invoice_id = ah.invoice_id
   AND aia.doc_sequence_value = 2439927
--   AND aha.hold_date > '01-OCT-2013'
   AND 1 = 1;

Invoice Headers 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;

Using Oracle SQL instead of ANSI SQL:

-- ##############################################################################
--      Invoices including POs
-- ############################################################################*/

SELECT aia.invoice_id
     , aia.invoice_num
     , 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
  FROM ap.ap_invoices_all aia
     , ap.ap_invoice_distributions_all aida
--     , ap.ap_terms_tl att
--     , applsys.fnd_user fu
--     , ap.ap_suppliers pv
--     , ap.ap_supplier_sites_all pvsa
     , po.po_headers_all pha
     , po.po_lines_all pla
     , po.po_distributions_all pda
 WHERE aia.invoice_id = aida.invoice_id
--   AND aia.terms_id = att.term_id
--   AND aia.created_by = fu.user_id
--   AND aia.vendor_id = pv.vendor_id
--   AND aia.vendor_site_id = pvsa.vendor_site_id
--   AND pv.vendor_id = pvsa.vendor_id
   AND aida.po_distribution_id = pda.po_distribution_id
   AND pda.po_line_id = pla.po_line_id
   AND pla.po_header_id = pha.po_header_id
--   AND pha.segment1 IN (1372292,1361420)
--   AND aia.invoice_id IN (373018)                                          -- ##   ID   ## --
--   AND aia.invoice_num = '373018'                                       -- ## NUMBER ## --
   and AIA.DOC_SEQUENCE_VALUE IN (2439927)                                    -- ## VOUCHER ## --
   AND 1 = 1;

-- add a distinct and keep to bare minimum to only return Invoice, PO and Supplier info

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
     , ap.ap_invoice_distributions_all aida
     , ap.ap_terms_tl att
     , applsys.fnd_user fu
     , apps.po_vendors pv
     , apps.po_vendor_sites_all pvsa
     , po.po_headers_all pha
     , po.po_lines_all pla
     , po.po_distributions_all pda
 WHERE aia.invoice_id = aida.invoice_id
   AND aia.terms_id = att.term_id
   AND aia.created_by = fu.user_id
   AND aia.vendor_id = pv.vendor_id
   AND aia.vendor_site_id = pvsa.vendor_site_id
   AND pv.vendor_id = pvsa.vendor_id
   AND aida.po_distribution_id = pda.po_distribution_id
   AND pda.po_line_id = pla.po_line_id
   AND pla.po_header_id = pha.po_header_id
   AND pha.segment1 IN (1372292,1361420)
--   AND aia.invoice_id IN (373018)                                          -- ##   ID   ## --
--   AND aia.invoice_num = '373018'                                       -- ## NUMBER ## --
--   and AIA.DOC_SEQUENCE_VALUE IN (2221144)                                    -- ## VOUCHER ## --
   AND 1 = 1;

Invoice Headers and 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 ('705907')
   AND 1 = 1;

Using Oracle SQL instead of ANSI SQL:

-- ##############################################################################
--      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
  from ap.ap_invoice_payments_all aipa
     , ap.ap_invoices_all aia
     , ap.ap_checks_all aca
     , ap.ap_suppliers pv
 WHERE aipa.invoice_id = aia.invoice_id
   AND aipa.check_id = aca.check_id
   AND aia.vendor_id = pv.vendor_id
--   AND aia.doc_sequence_value IN (1966859,1934769)         -- ## VOUCHER ## --
--   AND aia.invoice_id = 2127401
--   AND aipa.last_update_date > '09-APR-2015'
   AND DECODE(aipa.invoice_payment_type, 'PREPAY', aia.invoice_num, aca.check_number) IN ('10005561')
   AND 1 = 1;

Invoice Headers and Accounted and Validated Status 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 = 'Worra Lorra Cheese'
--   AND aia.invoice_id IN (252962,2088392,2165995,2500940,273058)                  -- ##    ID   ## --
--   AND aia.invoice_num IN ('69487161')              -- ## 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;

Using Oracle SQL instead of ANSI SQL:

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

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
     , nvl2(aia.cancelled_amount, 'Y', 'N') cancelled
--     , DECODE(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
--     , ap_invoices_pkg.get_posting_status(aia.invoice_id) accounted
--     , ap_invoices_pkg.get_approval_status(aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) approval_status
--     , 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
     , ap.ap_terms_tl att
     , applsys.fnd_user fu
     , apps.po_vendors pv
     , apps.po_vendor_sites_all pvsa
 WHERE aia.terms_id = att.term_id
   AND aia.created_by = fu.user_id
   AND aia.vendor_id = pv.vendor_id
   AND aia.vendor_site_id = pvsa.vendor_site_id
   AND pv.vendor_id = pvsa.vendor_id
   AND pv.vendor_name = 'Worra Lorra Cheese'
--   AND aia.invoice_id IN (2109872)               -- ##    ID   ## --
--   AND aia.invoice_num IN ('69487161')              -- ## NUMBER  ## --
--   AND AIA.DOC_SEQUENCE_VALUE IN (2039086)         -- ## VOUCHER ## --
   AND 1 = 1;

Invoice Headers and Invoice Distributions

This SQL includes invoice distributions, and also Project details.

-- ##############################################################################
--      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 (2683928)
       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;

Using Oracle SQL instead of ANSI SQL:

-- ##############################################################################
--      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
     , aida.creation_date cr_dt
     , aida.last_update_date upd_dt
     , aida.amount dist_amt
     , aida.base_amount base_amt
     , aida.distribution_line_number dist_line
     , aida.invoice_distribution_id
     , aida.pa_addition_flag
     , ppa.segment1 proj
     , ppa.project_id
     , aida.period_name
     , aida.line_type_lookup_code   
     , aida.po_distribution_id
     , aida.accounting_event_id
     , aida.request_id
     , gcc.segment1, gcc.segment2, gcc.segment3, gcc.segment4, gcc.segment5, gcc.segment6
     , gcc.detail_posting_allowed_flag, gcc.detail_budgeting_allowed_flag, gcc.enabled_flag
     , gcc.end_date_active
     , gcc.last_update_date
     , aida.awt_gross_amount
     , aida.awt_withheld_amt
     , aia.pre_withholding_amount
--     , ap_invoices_pkg.get_amount_withheld (aia.invoice_id) amount_withheld
--     , '###############'
  FROM ap.ap_invoices_all aia
     , ap.ap_invoice_distributions_all aida
     , ap.ap_terms_tl att
     , applsys.fnd_user fu
     , ap.ap_suppliers pv
     , ap.ap_supplier_sites_all pvsa
     , gl.gl_code_combinations gcc
     , pa.pa_projects_all ppa
 WHERE aia.invoice_id = aida.invoice_id
   AND aia.terms_id = att.term_id
   AND aia.created_by = fu.user_id
   AND aia.vendor_id = pv.vendor_id
   AND aia.vendor_site_id = pvsa.vendor_site_id
   AND pv.vendor_id = pvsa.vendor_id
   AND aida.dist_code_combination_id = gcc.code_combination_id
   AND aida.project_id = ppa.project_id(+)
--   AND ppa.segment1 = 'P117684'
--   AND aia.invoice_id IN (460808)              -- ##   ID    ## --
--   AND aia.invoice_num = '91420'               -- ## NUMBER  ## --
--   AND AIA.DOC_SEQUENCE_VALUE IN (2439927)       -- ## VOUCHER ## --
   AND aida.invoice_distribution_id = 37143990 
   AND 1 = 1
ORDER BY aia.creation_date DESC;

Invoice Headers and Invoice Lines

This SQL includes AP Invoice Lines (which came with with Oracle E-Business Suite Release 12):

-- ##############################################################################
--      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 ('69487161')               -- ## NUMBER  ## --
--   AND aia.doc_sequence_value IN (1326821)         -- ## VOUCHER ## --
   AND 1 = 1;

Using Oracle SQL instead of ANSI SQL:

-- ##############################################################################
--      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.description
     , aila.tax_classification_code tax_code
  FROM ap.ap_invoices_all aia
     , ap.ap_invoice_lines_all aila
     , ap.ap_terms_tl att
     , applsys.fnd_user fu
     , apps.po_vendors pv
     , apps.po_vendor_sites_all pvsa
 WHERE aia.invoice_id = aila.invoice_id
   AND aia.terms_id = att.term_id
   AND aia.created_by = fu.user_id
   AND aia.vendor_id = pv.vendor_id
   AND aia.vendor_site_id = pvsa.vendor_site_id
   AND pv.vendor_id = pvsa.vendor_id
--   AND aia.invoice_id IN (2109872)               -- ##    ID   ## --
--   AND aia.invoice_num IN ('69487161')              -- ## NUMBER  ## --
   AND aia.doc_sequence_value IN (1966859,1934769,257578,2262677)         -- ## VOUCHER ## --
   AND 1 = 1;

Invoice Headers

This SQL lists basic AP invoice header 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 = 'A Whole Lotta Cheese'
--   AND aia.invoice_num = '55263'
   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;

Using Oracle SQL instead of ANSI SQL:

-- ##############################################################################
--      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
     , 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
--     , (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
     , ap.ap_terms_tl att
     , applsys.fnd_user fu
     , apps.po_vendors pv
     , apps.po_vendor_sites_all pvsa
 WHERE aia.terms_id = att.term_id
   AND aia.created_by = fu.user_id
   AND aia.vendor_id = pv.vendor_id
   AND aia.vendor_site_id = pvsa.vendor_site_id
   AND pv.vendor_id = pvsa.vendor_id
--   AND aia.creation_date > '13-APR-2015'
   AND aia.invoice_id IN (2095523,2139498,2127401)               -- ##    ID   ## --
--   AND aia.invoice_num IN ('54829','55263')              -- ## NUMBER  ## --
--   AND aia.doc_sequence_value IN (1966859,1934769)         -- ## VOUCHER ## --
   AND 1 = 1;