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;

Leave a Comment

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

*
*