Thursday, 26 December 2013

AP Invoices Matching Hold Details

/*##############################################################################
#      INVOICE - MATCH / HOLD INFORMATION
/*############################################################################*/
SELECT aia.invoice_id
     , aia.invoice_num
     , aia.invoice_amount
     , aia.invoice_date
     , '----------'
     , pha.segment1
     , '----------'
     , aha.hold_date
     , aha.release_reason
     , aha.last_update_date
     , aha.hold_lookup_code
     , aha.hold_reason
     , '----------'
     , pda.quantity_billed
  FROM ap.ap_invoices_all aia
     , ap.ap_invoice_distributions_all aida
     , po.po_headers_all pha
     , po.po_distributions_all pda
     , ap.ap_holds_all aha
     , po.po_vendors pv                -- use ap.ap_suppliers for r12
     , po.po_vendor_sites_all pvsa        -- use ap.ap_supplier_sites_all for r12
 WHERE aia.invoice_id = aida.invoice_id
   AND aida.po_distribution_id = pda.po_distribution_id
   AND pda.po_header_id = pha.po_header_id
   AND aha.invoice_id(+) = aia.invoice_id
   AND aia.vendor_site_id = pvsa.vendor_site_id
   AND pvsa.vendor_id = pv.vendor_id
   AND aia.invoice_num = '123456';

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete