Monday, 9 January 2017

Query to find open invoices with the Supplier and PO details

SELECT   i.invoice_num "Invoice Number",
         (SELECT MAX (pha.segment1) po_number
            FROM apps.ap_invoices_all aia,
                 apps.ap_invoice_lines_all aila,
                 apps.ap_invoice_distributions_all aida,
                 apps.po_headers_all pha,
                 apps.po_lines_all pla,
                 apps.po_distributions_all pda
           WHERE pha.po_header_id = pla.po_header_id
             AND pla.po_line_id = pda.po_line_id
             AND aida.po_distribution_id = pda.po_distribution_id
             AND aida.invoice_id = aia.invoice_id
             AND aia.invoice_id = aila.invoice_id
             AND aia.invoice_id = i.invoice_id
             AND aila.line_number = ail.line_number
             AND aia.vendor_id = i.vendor_id) "PO Number",
         v.segment1 "Supplier Number", v.vendor_name "Supplier Name",
         vs.vendor_site_code "Supplier Site", i.invoice_date "Invoice Date",
         i.description "Invoice Description",
         ail.description "Invoice Line Description", SUM (ail.amount)
                                                                     "Amount",
         DECODE (i.cancelled_date, NULL, 'NO', 'YES') "Cancel Status"
    FROM po_vendors v,
         po_vendor_sites_all vs,
         ap_invoices_all i,
         apps.ap_invoice_lines_all ail
   WHERE v.vendor_id = vs.vendor_id
     AND i.invoice_id = ail.invoice_id
     AND i.vendor_id = v.vendor_id
     AND i.vendor_site_id = vs.vendor_site_id
--and     i.invoice_num = '10190183'
     AND i.invoice_date BETWEEN '01-JAN-2015' AND '31-DEC-2015'
     AND EXISTS (
               SELECT 1
                 FROM apps.ap_invoice_distributions_all d
                WHERE d.invoice_id = i.invoice_id
                      AND d.match_status_flag = 'A')
GROUP BY v.vendor_name,
         vs.vendor_site_code,
         i.invoice_id,
         i.invoice_num,
         i.invoice_date,
         i.description,
         ail.description,
         ail.line_number,
         i.vendor_id,
         v.segment1,
         i.cancelled_date

ORDER BY v.vendor_name, i.invoice_num;
By
Sivachandaran S  

1 comment:

  1. Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com

    ReplyDelete