AP Invoices including POs and Receipts

SELECT aia.invoice_id

     , aia.invoice_num

     , aida.creation_date

     , aida.last_updated_by

     , aida.distribution_line_number dist_line

     , aida.line_type_lookup_code dist_line_type

     , aida.period_name dist_line_period

     , aida.match_status_flag

     , aida.posted_flag

     , aida.quantity_invoiced

     , aida.amount dist_amt

     , pha.segment1 po

     , pv.vendor_name supplier

     , rt.transaction_id rx_id

  FROM ap.ap_invoices_all aia

     , ap.ap_invoice_distributions_all aida

     , ap.ap_terms_tl att

     , applsys.fnd_user fu

     , po.po_vendors pv

     , po.po_vendor_sites_all pvsa

     , po.po_headers_all pha

     , po.po_lines_all pla

     , po.po_distributions_all pda

     , po.rcv_transactions rt

 WHERE aia.invoice_id = aida.invoice_id

   AND 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 aida.po_distribution_id = pda.po_distribution_id

   AND pda.po_line_id = pla.po_line_id

   AND pla.po_header_id = pha.po_header_id

   AND pda.po_distribution_id = rt.po_distribution_id

   AND NVL(rt.transaction_type, 'RECEIVE') = 'RECEIVE'

   AND aia.invoice_date < SYSDATE - 50        -- raised in the last 50 days