All Below Queries are based on Input Parameters- p_as_of_date & p_account_number
-- Query for Customer Transaction Balance
/* Formatted on 5/9/2017 2:31:38 PM (QP5 v5.114.809.3010) */
SELECT NVL (SUM (ps.amount_due_remaining), 0) invoice_balance
FROM ra_cust_trx_types_all rtt,
ra_customer_trx_all rta,
ra_cust_trx_line_gl_dist_all rgld,
gl_code_combinations cc,
hz_cust_accounts_all cust_acct,
ar_payment_schedules_all ps,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses_all site_uses
WHERE TRUNC (ps.gl_date) <= :p_as_of_date
AND cust_acct.account_number = :p_account_number
AND ps.customer_id = cust_acct.cust_account_id
AND ps.cust_trx_type_id = rtt.cust_trx_type_id
AND ps.trx_date <= :p_as_of_date
AND ps.CLASS NOT IN ('CM', 'PMT')
AND site_uses.site_use_code = 'BILL_TO'
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_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 rta.customer_trx_id = ps.customer_trx_id
AND rta.customer_trx_id = rgld.customer_trx_id
AND rgld.code_combination_id = cc.code_combination_id
AND rgld.account_class = 'REV'
-- Query for Credit Memo Balance
/* Formatted on 5/9/2017 2:31:51 PM (QP5 v5.114.809.3010) */
SELECT NVL (SUM (ps.amount_due_remaining), 0) cr_memo_balance
FROM ra_cust_trx_types_all rtt,
ra_customer_trx_all rta,
ra_cust_trx_line_gl_dist_all rgld,
gl_code_combinations cc,
hz_cust_accounts_all cust_acct,
ar_payment_schedules_all ps,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses_all site_uses
WHERE TRUNC (ps.gl_date) <= :p_as_of_date
AND cust_acct.account_number = :p_account_number
AND ps.customer_id = cust_acct.cust_account_id
AND ps.cust_trx_type_id = rtt.cust_trx_type_id
AND ps.trx_date <= :p_as_of_date
AND ps.CLASS = 'CM'
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 acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_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 rta.customer_trx_id = ps.customer_trx_id
AND rta.customer_trx_id = rgld.customer_trx_id
AND rgld.code_combination_id = cc.code_combination_id
AND rgld.account_class = 'REV'
-- Query for Total On Account Receipt Amount
/* Formatted on 5/9/2017 2:36:23 PM (QP5 v5.114.809.3010) */
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 5/9/2017 2:36:33 PM (QP5 v5.114.809.3010) */
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 5/9/2017 2:36:46 PM (QP5 v5.114.809.3010) */
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