Pages

OracleEBSpro is purely for knowledge sharing and learning purpose, with the main focus on Oracle E-Business Suite Product and other related Oracle Technologies.

I'm NOT responsible for any damages in whatever form caused by the usage of the content of this blog.

I share my Oracle knowledge through this blog. All my posts in this blog are based on my experience, reading oracle websites, books, forums and other blogs. I invite people to read and suggest ways to improve this blog.


Tuesday, April 9, 2013

AR Receipt SQL Queries


All below Queries are based on Input Parameters- p_as_of_date & p_account_number

-- Query for Total On Account Receipt Amount
SELECT NVL(SUM(ps.amount_due_remaining), 0) total_onacct_receipts
  FROM hz_cust_accounts_all           cust_acct,
       ar_payment_schedules_all       ps,
       ar_receivable_applications_all arr,
       hz_cust_acct_sites_all         acct_site,
       hz_party_sites                 party_site,
       hz_locations                   loc,
       hz_cust_site_uses_all          site_uses,
       ar_cash_receipts_all           acr,
       ar_cash_receipt_history_all    crh,
       gl_code_combinations           cc
 WHERE TRUNC(ps.gl_date) <= :p_as_of_date
   AND ps.customer_id = cust_acct.cust_account_id
   AND cust_acct.account_number = :p_account_number
   AND ps.customer_id = cust_acct.cust_account_id
   AND acct_site.party_site_id = party_site.party_site_id
   AND loc.location_id = party_site.location_id
   AND ps.cash_receipt_id = acr.cash_receipt_id
   AND acr.cash_receipt_id = crh.cash_receipt_id
   AND crh.account_code_combination_id = cc.code_combination_id
   AND ps.trx_date <= :p_as_of_date
   AND ps.CLASS = 'PMT'
   AND ps.cash_receipt_id = arr.cash_receipt_id
   AND arr.status IN ('ACC')
   AND ps.status = 'OP'
   AND site_uses.site_use_code = 'BILL_TO'
   AND site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
   AND NVL(site_uses.status, 'A') = 'A'
   AND cust_acct.cust_account_id = acct_site.cust_account_id
   AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
   AND ps.customer_id = acct_site.cust_account_id
   AND ps.customer_site_use_id = site_uses.site_use_id HAVING
 NVL(SUM(arr.amount_applied), 0) > 0;

-- Query for Total Unapplied Receipt Amount 

SELECT NVL(SUM(arr.amount_applied), 0) total_unapp_receipts
  FROM hz_cust_accounts_all           cust_acct,
       ar_payment_schedules_all       ps,
       ar_receivable_applications_all arr,
       hz_cust_acct_sites_all         acct_site,
       hz_party_sites                 party_site,
       hz_locations                   loc,
       hz_cust_site_uses_all          site_uses,
       ar_cash_receipts_all           acr,
       ar_cash_receipt_history_all    crh,
       gl_code_combinations           cc
 WHERE TRUNC(ps.gl_date) <= :p_as_of_date
   AND ps.customer_id = cust_acct.cust_account_id
   AND cust_acct.account_number = :p_account_number
   AND ps.customer_id = cust_acct.cust_account_id
   AND acct_site.party_site_id = party_site.party_site_id
   AND loc.location_id = party_site.location_id
   AND ps.cash_receipt_id = acr.cash_receipt_id
   AND acr.cash_receipt_id = crh.cash_receipt_id
   AND crh.account_code_combination_id = cc.code_combination_id
   AND ps.trx_date <= :p_as_of_date
   AND ps.CLASS = 'PMT'
   AND ps.cash_receipt_id = arr.cash_receipt_id
   AND arr.status = 'UNAPP'
   AND ps.status = 'OP'
   AND site_uses.site_use_code = 'BILL_TO'
   AND site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
   AND NVL(site_uses.status, 'A') = 'A'
   AND cust_acct.cust_account_id = acct_site.cust_account_id
   AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
   AND ps.customer_id = acct_site.cust_account_id
   AND ps.customer_site_use_id = site_uses.site_use_id HAVING
 NVL(SUM(arr.amount_applied), 0) > 0;

-- Query for Total Uncleared Receipt Amount 

SELECT NVL(SUM(ps.amount_due_remaining), 0) total_uncleared_receipts
  FROM hz_cust_accounts_all           cust_acct,
       ar_payment_schedules_all       ps,
       ar_receivable_applications_all arr,
       hz_cust_acct_sites_all         acct_site,
       hz_party_sites                 party_site,
       hz_locations                   loc,
       hz_cust_site_uses_all          site_uses,
       ar_cash_receipt_history_all    crh,
       ar_cash_receipts_all           acr,
       gl_code_combinations           cc
 WHERE TRUNC(ps.gl_date) <= :p_as_of_date
   AND ps.customer_id = cust_acct.cust_account_id
   AND cust_acct.account_number = :p_account_number
   AND ps.customer_id = cust_acct.cust_account_id
   AND acct_site.party_site_id = party_site.party_site_id
   AND loc.location_id = party_site.location_id
   AND ps.cash_receipt_id = acr.cash_receipt_id
   AND acr.cash_receipt_id = crh.cash_receipt_id
   AND crh.account_code_combination_id = cc.code_combination_id
   AND ps.trx_date <= :p_as_of_date
   AND ps.CLASS = 'PMT'
   AND ps.cash_receipt_id = arr.cash_receipt_id
   AND arr.status = 'UNAPP'
   AND ps.status = 'OP'
   AND site_uses.site_use_code = 'BILL_TO'
   AND site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
   AND NVL(site_uses.status, 'A') = 'A'
   AND cust_acct.cust_account_id = acct_site.cust_account_id
   AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
   AND ps.customer_id = acct_site.cust_account_id
   AND ps.customer_site_use_id = site_uses.site_use_id
   AND ps.cash_receipt_id = crh.cash_receipt_id
   AND crh.status NOT IN ('CLEARED') HAVING
 NVL(SUM(arr.amount_applied), 0) > 0;

No comments:

Post a Comment