AR Receipt Queries

AR Receipt Queries

                                        Query for Total On Account Receipt Amount

/* Formatted on 12/8/2014 3:12:25 PM (QP5 v5.115.810.9015) */
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 Un-applied Receipt Amount

/* Formatted on 12/8/2014 3:12:36 PM (QP5 v5.115.810.9015) */
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

/* Formatted on 12/8/2014 3:12:52 PM (QP5 v5.115.810.9015) */
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;