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;

Leave a Comment

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

*
*