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';