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;

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*