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;

Quickly navigate and search through My Oracle Support using Keyword Bookmarks

Intro

When working with Oracle I often need to look up a Note or Patch in My Oracle Support.

You can use keyword bookmarks in Firefox and Chrome to search for items using the address bar (using a combination of a pre-defined keyword followed by the search term), which saves a lot of time compared to having to open up My Oracle Support, and then clicking into the search form and searching for whatever you need, or clicking into the SR or Patch tabs and running searches from there.

In fact, they’re not always searches – for example, if you know a My Oracle Support Note, you can easily navigate to that Note ID without having to search for it using this method – you just type in the keyword bookmark (e.g. n), followed by the Note ID, press enter, and you’re redirected to the page on My Oracle Support for that Note. There’s no need to search for it in My Oracle Support – you just go direct to the right place, bypassing any searching. The same applies to Bugs, Patches and Service Requests.

Additionally, the URL I use for Service Requests is handy because unlike the link you see in an email notification to say you’ve had an update on the Service Request, this URL takes you to a version of the Service Request page which does not have relative dates. For example, instead of it saying the SR was raised 5+ months ago and was update 2 days ago, it gives you actual dates instead, which I find a lot more useful.

Firefox

1 – Type CTRL + SHIFT + B to open the Bookmark Manager:

2 – Create a new folder for your keyword bookmarks by right-clicking on the relevant folder you want to put the new folder in, and click “New Folder”:

2017-10-25-firefox-01

3 – Give the folder a name

4 – Once done, right-click the new folder and click “New Bookmark”

2017-10-25-firefox-02

5 – Add in a new Bookmark as follows:

2017-10-25-firefox-03

6 – For each of the following:

Name Location Keyword
Google – Search https://www.google.co.uk/search?q=%s gs
Google – Image Search https://www.google.co.uk/search?q=%s&tbm=isch gi
Google – Map Search https://www.google.co.uk/maps/place/%s gm
MOS – Search https://support.oracle.com/epmos/faces/KMConsolidatedSearch?kmPgTpl:r1:0:mr1:s1:mSearch:bk_srchTxt%20No%20%s sch
MOS – Note https://support.oracle.com/rs?type=doc&id=%s n
MOS – SR https://support.oracle.com/epmos/faces/SrDetail?srDetailRelativeDateParam=false&srNumber=%s sr
MOS – Patch https://support.oracle.com/rs?type=patch&id=%s p
MOS – Bug https://support.oracle.com/rs?type=bug&id=%s b

7 – When finished, the bookmarks will be listed as follows

2017-10-25-firefox-04

As you can see from the Google examples above – the same principle can be applied to any URL which includes the key bit of info in the querystring.

8 – Click the “More” down arrow to access the keyword if you want to edit it at a later date:

2017-10-25-firefox-05

The Keyword field is now visible:

2017-10-25-firefox-06

Chrome

1 – Right-click the address bar and click “Edit Search Engines”

2 – The following opens:

2017-10-25-chrome-01

3 – Click the “Add” button and add in the details – for example:

2017-10-25-chrome-02

4 – Repeat for the following:

Name Location Keyword
Google – Search https://www.google.co.uk/search?q=%s gs
Google – Image Search https://www.google.co.uk/search?q=%s&tbm=isch gi
Google – Map Search https://www.google.co.uk/maps/place/%s gm
MOS – Search https://support.oracle.com/epmos/faces/KMConsolidatedSearch?kmPgTpl:r1:0:mr1:s1:mSearch:bk_srchTxt%20No%20%s sch
MOS – Note https://support.oracle.com/rs?type=doc&id=%s n
MOS – SR https://support.oracle.com/epmos/faces/SrDetail?srDetailRelativeDateParam=false&srNumber=%s sr
MOS – Patch https://support.oracle.com/rs?type=patch&id=%s p
MOS – Bug https://support.oracle.com/rs?type=bug&id=%s b

5 – They appear as follows:

2017-10-25-chrome-03

Using Keyword Bookmarks

1 – When in Firefox or Chrome, press CTRL + L to move into the browser’s address bar.

2 – To search for a My Oracle Support Note, type the keyword (e.g. n) followed by the note ID – e.g.

2017-10-25-address-bar

3 – Press enter to run the search or redirect to the relevant Note ID, Patch, Bug or Service Request.

These are quick to set up, and once you get used to them, they save a lot of time. You can then add in others you find useful for all sorts of other websites.

Email search problem in Outlook 2016

I’ve been using Outlook 2016 for the past 11 months.

One thing that’s annoyed me about it is the fact that the search facility has not worked. I’d press CTRL + SHIFT + F to open the Advanced Find option.

Then run a search, making sure to click the Browse button, and select to include all sub folders.

Anyway, this is more for a note for myself.

I found a fix via this post on lookeen.com, which included a comment by Rachel Harrell-Harper which fixed the issue for me:

  1. Open Control Panel > Programs > Programs and Features
  2. Highlight your Office product
  3. Click change at top
  4. Click quick repair.

 

SyntaxHighlighter and Bootstrap – line number alignment problem

I’m a fan of Alex Gorbatchev’s SyntaxHighlighter, which provides an easy way to include syntax highlighting in web pages.

I noticed that when using it in conjunction with the Bootstrap, that the line numbers appeared to be misaligned – here’s an example:

Misaligned line numbers

I had a look on the web, and found a solution via SyntaxHighter’s GitHub page, on an issue post about Lines misaligned with numbers.

The fix involves adding this CSS:

.syntaxhighlighter table td.gutter {
    vertical-align: middle !important;
}

As provided by Robert Butler.

Once applied, the issue was fixed: