Query to find invoices which are pending to be paid for more than 60 days invoices paid during past 6 months

query to find  invoices which are  pending to be paid for more than 60 days from sysdate

cust no,cust name,invoice no,date of invoice,amount,

period of invoice pending for 30-60 days,period of invoice pending for 60-120 days,

period of invoice pending for more than 120 days

/* Formatted on 9/4/2014 2:20:12 PM (QP5 v5.115.810.9015) */

SELECT hca.account_number,

       hp.party_name,

       rcta.trx_number,

       rcta.trx_date,

       SUM (rctla.extended_amount),

       CASE

          WHEN TRUNC (SYSDATE - rcta.trx_date) BETWEEN 30 AND 60

          THEN

             rcta.trx_number

       END

          "Pending for 30 - 60 days",

       CASE

          WHEN TRUNC (SYSDATE - rcta.trx_date) BETWEEN 60 AND 120

          THEN

             rcta.trx_number

       END

          "Pending for 60 - 120 days",

       CASE

          WHEN TRUNC (SYSDATE - rcta.trx_date) > 120 THEN rcta.trx_number

       END

          "Pending for more than 120 days"

FROM hz_parties hp,

     hz_cust_accounts hca,

     ra_customer_trx_all rcta,

     ra_customer_trx_lines_all rctla

WHERE     hp.party_id = hca.party_id

      AND rcta.org_id = 204

     

AND hca.cust_account_id = rcta.sold_to_customer_id

      AND rcta.customer_trx_id = rctla.customer_trx_id

      AND hca.account_number = 3896

GROUP BY hca.account_number, hp.party_name, rcta.trx_number, rcta.trx_date;

-

4)to list all the invoices paid during past 6 months

cust no,cust name,invoice no,receipt no,date of invoice,date of payment,amount

/* Formatted on 9/4/2014 2:20:24 PM (QP5 v5.115.810.9015) */

SELECT hca.account_number,

       hp.party_name,

       rcta.trx_number,

       acra.receipt_number,

       rcta.trx_date,

       acra.creation_date,

       acra.amount

FROM hz_parties hp,

     hz_cust_accounts hca,

     ra_customer_trx_all rcta--, ra_customer_trx_lines_all rctla

     ,

     ar_cash_receipts_all acra,

     ar_payment_schedules_all apsa,

     ar_receivable_applications_all araa,

     ar_lookups al

WHERE     1 = 1

      AND hp.party_id = hca.party_id

      AND rcta.sold_to_customer_id = hca.cust_account_id

      AND rcta.org_id = 204

      --and rcta.customer_trx_id = rctla.customer_trx_id

      AND rcta.customer_trx_id = apsa.customer_trx_id

      AND araa.applied_customer_trx_id = apsa.customer_trx_id

      AND acra.cash_receipt_id = araa.cash_receipt_id

      AND acra.status = al.lookup_code

      AND al.lookup_type = 'PAYMENT_TYPE'

      AND rcta.trx_number = '11792'

      AND TRUNC (MONTHS_BETWEEN (SYSDATE, araa.creation_date)) <= 6;