Unpaid Invoices by Date

This query was very useful to me. It can give coun of unpaid invoices for each pay group and payment method by due date. This query can give me an idea of how many invoices will be processed by payments run.

This query can serve as an example to LISTAGG function too.

SELECT dt , listagg (payment_method_lookup_code || ' ' || pay_group_lookup_code || ' = ' || unpaid_invoices, '; ') within GROUP (ORDER BY payment_method_lookup_code) xx FROM (SELECT TO_CHAR(due_date,'YYYY-MM-DD') dt , aps.payment_method_lookup_code , ai.pay_group_lookup_code , COUNT(1) unpaid_invoices FROM ap_payment_schedules_all aps , ap_invoices_all ai WHERE ai.invoice_id = aps.invoice_id AND ai.payment_status_flag = 'N' AND ai.cancelled_date IS NULL AND aps.due_date + 0 BETWEEN to_date('1-mar-2011','dd-mon-yyyy') AND to_date('4-jun-2012','dd-mon-yyyy') GROUP BY TO_CHAR(due_date,'YYYY-MM-DD') , aps.payment_method_lookup_code , ai.pay_group_lookup_code ) GROUP BY dt; --