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;