Receivables Useful Query

1)develop a query to list customer details

cust name,cust no,profile class,primary bill_to_address
(country,address,city,state,postal code),primary ship_to_address(country,address,city,state,po)


/* Formatted on 9/4/2014 2:18:11 PM (QP5 v5.115.810.9015) */
SELECT hp1.party_name,
       hp1.party_id,
       hca.account_number,
       hcpc.name,
       hcsua.site_use_code,
          hl.country
       || ' '
       || hl.address1
       || ' '
       || hl.city
       || ' '
       || hl.state
       || ' '
       || hl.postal_code
          "Address"
FROM hz_parties hp1,
     hz_cust_accounts hca,
     hz_party_sites hps,
     hz_cust_profile_classes hcpc,
     hz_customer_profiles hcp,
     hz_locations hl,
     hz_cust_acct_sites_all hcasa,
     hz_cust_site_uses_all hcsua
WHERE     1 = 1
      AND hca.account_number = 3896
      AND hcp.profile_class_id = hcpc.profile_class_id(+)
      AND hca.cust_account_id = hcp.cust_account_id
      AND hp1.party_id = hca.party_id
      --and hca.cust_account_id=hcasa.cust_account_id
      AND hps.party_site_id = hcasa.party_site_id
      AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
      AND hp1.party_id = hps.party_id
      AND hl.location_id = hps.location_id
      AND hcsua.primary_flag = 'Y'
      AND hcp.site_use_id IS NULL
      AND hcasa.org_id = 204
;



2)to extract customer invoice details,cust name,total invoice relevant to customer

cust no,cust name,date of invoice generated,amount of invoice,
amount of invoice last paid by the customer,credit limit,balance amount unpaid


/* Formatted on 9/4/2014 2:18:30 PM (QP5 v5.115.810.9015) */
SELECT hca.account_number,
       hp.party_name,
       rcta.trx_date,
       (SELECT SUM (rctla.extended_amount)
        FROM ra_customer_trx_lines_all rctla
        WHERE rcta.customer_trx_id = rctla.customer_trx_id)
          "INVOICE AMOUNT",
       hcpa.overall_credit_limit,
       apsa.amount_due_remaining,
       acra.amount
FROM hz_parties hp,
     hz_cust_accounts hca,
     hz_customer_profiles hcp,
     hz_cust_profile_amts hcpa,
     ra_customer_trx_all rcta--, ra_customer_trx_lines_all rctla
     ,
     ar_cash_receipts_all acra,
     ar_receivable_applications_all araa,
     ar_payment_schedules_all apsa
WHERE     1 = 1
      AND hp.party_id = hca.party_id
      AND hca.cust_account_id = hcp.cust_account_id
      AND hcp.site_use_id IS NULL
      AND hcpa.cust_account_profile_id = hcp.cust_account_profile_id
      AND hcpa.currency_code = 'USD'
      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 rcta.trx_number = '11792'
      AND acra.cash_receipt_id =
            (SELECT MAX (cash_receipt_id)
             FROM ar_receivable_applications_all araa_2
             WHERE araa_2.applied_customer_trx_id = apsa.customer_trx_id)
;
3)to list all the invoices whicha r pending to b 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 invoive,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;