Thursday 26 December 2013

AP Invoice with Accounting Details

/*##############################################################################
#        BASIC INVOICE DETAILS AND WHETHER INVOICE HAS BEEN ACCOUNTED
         accounting info is in this table: ap_accounting_events_all
/*############################################################################*/
  
SELECT aia.invoice_id
     , aia.invoice_num
     , aia.doc_sequence_value voucher_num
     , aia.invoice_date inv_date
     , aia.doc_sequence_value
     , aaea.event_type_code
     , aia.source
     , aia.invoice_type_lookup_code inv_type
     , att.name pay_terms
     , aia.invoice_currency_code inv_curr
     , aia.payment_currency_code pay_curr
     , aia.creation_date inv_cr_date
     , fu.description cr_by
     , aia.invoice_amount total_inv_amt
     , aia.approved_amount basic_amt
     , aia.validated_tax_amount tax_amt
     , aia.description
     , aia.payment_status_flag paid
     , aia.vat_code
     , pv.vendor_name supplier
     , pvsa.vendor_site_code supplier_site
     , aaea.event_status_code accounted_status
     , aaea.creation_date accounted_date
     , aaea.request_id accounted_job_id
  FROM ap.ap_invoices_all aia
     , ap.ap_accounting_events_all aaea
     , 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.invoice_id = aaea.source_id(+)
   AND aia.invoice_date < SYSDATE - 50        -- raised in the last 50 days
   AND 1 = 1;

No comments:

Post a Comment