ORACLE MASTERMINDS

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