AR Deposited Cash Report - Applied Detail

select
  cr.currency_code Currency_B,
  cr.deposit_date deposit_date_B,
  cr.cash_receipt_id cash_receipt_id_B,
  cr.status cr_status_B,
  cr.amount amount_B,
  decode( crh.batch_id, 
	null, cr.amount, 
	0 ) rcpt_control_amount_B,
  cr.status cr_status_BB,
  cr.type   cr_type_B,
  cr.reversal_category reversal_category_B, 
	AR_ARXSOC2_XMLP_PKG.cr_data_foundformula(cr.currency_code) CR_DATA_FOUND, 
	AR_ARXSOC2_XMLP_PKG.ca_summary_labelformula(cr.currency_code) ca_summary_label, 
	AR_ARXSOC2_XMLP_PKG.ca_difference_amountformula(:c_rcpt_control_amount_B, :ca_actual_amount) ca_difference_amount, 
	AR_ARXSOC2_XMLP_PKG.ca_unapplied_amountformula(:ca_unapplied_amount_B, :ca_on_account_amount, :ca_unidentified_amount) ca_unapplied_amount, 
	AR_ARXSOC2_XMLP_PKG.f_all_amountsformula(cr.status, cr.amount, cr.type, cr.reversal_category,cr.cash_receipt_id) F_ALL_AMOUNTS,
	AR_ARXSOC2_XMLP_PKG.pa_actual_amount_p pa_actual_amount,
	AR_ARXSOC2_XMLP_PKG.pa_unidentified_amount_p pa_unidentified_amount,
	AR_ARXSOC2_XMLP_PKG.pa_misc_amount_p pa_misc_amount,
	AR_ARXSOC2_XMLP_PKG.pa_nsf_amount_p pa_nsf_amount,
	AR_ARXSOC2_XMLP_PKG.pa_applied_count_p pa_applied_count,
	AR_ARXSOC2_XMLP_PKG.pa_unapplied_count_p pa_unapplied_count,
	AR_ARXSOC2_XMLP_PKG.pa_misc_count_p pa_misc_count
    from ar_cash_receipts cr, 
         ar_cash_receipt_history crh,
         ce_bank_accounts cba,           ce_bank_acct_uses ba
where nvl(:p_bank_account_name_low , '!') != 
            nvl(:p_bank_account_name_high, '@')
AND    &p_bank_count  != 0 
&lp_date_low
&lp_date_high
and nvl(cr.confirmed_flag,'Y') = 'Y'
and  deposit_date is NOT NULL
and  cr.cash_receipt_id = crh.cash_receipt_id
and  crh.first_posted_record_flag  = 'Y'
and cr.remit_bank_acct_use_id = ba.bank_acct_use_id
and cba.bank_account_id = ba.bank_account_id
&lp_bank_account_name_low
&lp_bank_account_name_high
union all
select 	b.currency_code,
	b.deposit_date,
	-1,
	null,
	0, 
	b.control_amount,	
	null,
	null,
	null, 
	AR_ARXSOC2_XMLP_PKG.cr_data_foundformula(b.currency_code) CR_DATA_FOUND, 
	AR_ARXSOC2_XMLP_PKG.ca_summary_labelformula(b.currency_code) ca_summary_label, 
	AR_ARXSOC2_XMLP_PKG.ca_difference_amountformula(:c_rcpt_control_amount_B, :ca_actual_amount) ca_difference_amount, 
	AR_ARXSOC2_XMLP_PKG.ca_unapplied_amountformula(:ca_unapplied_amount_B, :ca_on_account_amount, :ca_unidentified_amount) ca_unapplied_amount, 
	AR_ARXSOC2_XMLP_PKG.f_all_amountsformula(null, 0, null, null, -1) F_ALL_AMOUNTS,
	AR_ARXSOC2_XMLP_PKG.pa_actual_amount_p pa_actual_amount,
	AR_ARXSOC2_XMLP_PKG.pa_unidentified_amount_p pa_unidentified_amount,
	AR_ARXSOC2_XMLP_PKG.pa_misc_amount_p pa_misc_amount,
	AR_ARXSOC2_XMLP_PKG.pa_nsf_amount_p pa_nsf_amount,
	AR_ARXSOC2_XMLP_PKG.pa_applied_count_p pa_applied_count,
	AR_ARXSOC2_XMLP_PKG.pa_unapplied_count_p pa_unapplied_count,
	AR_ARXSOC2_XMLP_PKG.pa_misc_count_p pa_misc_count
from 	ar_batches b
 where 	nvl(:p_bank_account_name_low , '!') != 
                  nvl(:p_bank_account_name_high, '@')
 and 	&p_bank_count != 0
&lp_date_low 
&lp_date_high
and deposit_date is not null
order by 1, 2