Wednesday, March 23, 2016

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;

No comments:

Post a Comment

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...