Thursday, 26 December 2013

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

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