Validated Unpaid Invoices in Oracle AP

  SELECT hou.name OU_Name,

         i.invoice_num,

         v.vendor_name supplier_name,

         i.invoice_date,

         ps.due_date,

         i.amount_paid,

         --i.invoice_amount,ps.amount_remaining,

         SUM (i.invoice_amount) invoice_amount,

         SUM (ps.amount_remaining) amount_remaining

    FROM apps.ap_payment_schedules_all ps,

         apps.ap_invoices_all i,

         apps.po_vendors v,

         apps.po_vendor_sites_all vs,

         apps.hr_operating_units hou

   WHERE     i.invoice_id = ps.invoice_id

         AND i.vendor_id = v.vendor_id

         AND i.vendor_site_id = vs.vendor_site_id

         AND i.payment_status_flag = 'N'

         AND (NVL (ps.amount_remaining, 0) * NVL (i.exchange_rate, 1)) != 0

         AND DECODE (APPS.Ap_Invoices_Pkg.GET_APPROVAL_STATUS (

                        i.INVOICE_ID,

                        i.INVOICE_AMOUNT,

                        i.PAYMENT_STATUS_FLAG,

                        i.INVOICE_TYPE_LOOKUP_CODE),

                     'NEVER APPROVED', 'Never Validated',

                     'NEEDS REAPPROVAL', 'Needs Revalidation',

                     'Validated') = 'Validated'

         AND i.org_id = hou.organization_id

         AND ps.due_date > SYSDATE

GROUP BY hou.name,

         v.vendor_name,

         i.invoice_num,

         i.invoice_date,

         ps.due_date,

         i.invoice_amount,

         i.amount_paid,

         ps.amount_remaining

ORDER BY v.vendor_name, i.invoice_num;