Invoices Eligible for Payments Query in oracle apps

                                This query can give count of invoices by batch, payment method and payment group that can be or can not be selected for payments. If an invoices is on hold or not validated or not due for payment, it will not be selected by Payment Process Request.

 /* Formatted on 9/1/2015 11:05:52 AM (QP5 v5.240.12305.39446) */
  SELECT pay_group_lookup_code,
         payment_method_code,
         batch_name,
         holds,
         wfapproval_status,
         validated,
         due_date,
         COUNT (1)
    FROM (SELECT pay_group_lookup_code,
                 aps.payment_method_code,
                 wfapproval_status,
                 NVL (
                    (SELECT 'N'
                       FROM ap_invoice_distributions_all aid
                      WHERE     aid.invoice_id = aia.invoice_id
                            AND match_status_flag <> 'A'
                            AND ROWNUM = 1),
                    'Y')
                    validated,
                 (SELECT batch_name
                    FROM ap_batches_all ab
                   WHERE ab.batch_id = aia.batch_id)
                    batch_name,
                 NVL (
                    (SELECT 'Y'
                       FROM ap_holds_all
                      WHERE     invoice_id = aia.invoice_id
                            AND release_lookup_code IS NULL
                            AND ROWNUM = 1),
                    'N')
                    holds,
                 TRUNC (aps.due_date) due_date
            FROM ap_invoices_all aia, ap_payment_schedules_all aps
           WHERE     aia.payment_status_flag = 'N'
                 AND aia.cancelled_date IS NULL
                 AND aia.invoice_id = aps.invoice_id) a
GROUP BY pay_group_lookup_code,
         payment_method_code,
         batch_name,
         holds,
         wfapproval_status,
         validated,
         due_date
ORDER BY pay_group_lookup_code,
         payment_method_code,
         batch_name,
         holds,
         wfapproval_status,
         validated,
         due_date,
         8 DESC;