AP Invoices

Sections:

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

Basic Invoice Details

-- ############################################################################## --      BASIC INVOICE DETAILS -- ############################################################################*/   SELECT aia.invoice_id id      , aia.invoice_num num      , aia.doc_sequence_value voucher      , aia.source      , aia.creation_date      , aia.last_update_date               , aia.invoice_date inv_date      , aia.invoice_type_lookup_code inv_type      , aia.cancelled_date      , aia.cancelled_by      , aia.cancelled_amount      , pv.vendor_name supplier      , pv.segment1      , pvsa.vendor_site_code site      , aia.invoice_amount amt      , fu.description cr_by --     , aia.payment_status_flag paid --     , nvl2(aia.cancelled_amount, 'Y', 'N') cancelled --     , (SELECT COUNT(*) from ap.ap_holds_all ah where ah.invoice_id = aia.invoice_id AND ah.release_lookup_code IS NULL) hold_count   FROM ap.ap_invoices_all aia   JOIN ap.ap_terms_tl att            ON aia.terms_id =       att.term_id   JOIN applsys.fnd_user fu           ON aia.created_by =     fu.user_id   JOIN apps.po_vendors pv            ON aia.vendor_id =      pv.vendor_id   JOIN apps.po_vendor_sites_all pvsa ON aia.vendor_site_id = pvsa.vendor_site_id AND pv.vendor_id = pvsa.vendor_id  WHERE 1 = 1 --   AND pv.vendor_name = 'Support Warehouse Ltd t/a Care Pack Sales' --   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;

Basic Invoice Details With Lines

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

Invoices Including Distributions And Projects

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

Invoices Matched To Purchase Orders

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

Invoice Details - Extra Info

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

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

Invoice Details - Matching Holds

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

AP Invoices - with Payment Numbers

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