Invoices Eligible for Payments

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 sele3cted by Payment Process Request.

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 ;