AR Deposited Cash Report - Open 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_ARXSOC_XMLP_PKG.cr_data_foundformula(cr.currency_code) CR_DATA_FOUND, 
	AR_ARXSOC_XMLP_PKG.ca_summary_labelformula(cr.currency_code) ca_summary_label, 
	AR_ARXSOC_XMLP_PKG.ca_difference_amountformula(:c_rcpt_control_amount_B, :ca_actual_amount) ca_difference_amount, 
	AR_ARXSOC_XMLP_PKG.ca_applied_amountformula(:ca_applied_amount_B, :ca_misc_amount) ca_applied_amount, 
	AR_ARXSOC_XMLP_PKG.f_all_amountsformula(cr.amount, cr.status, cr.type, cr.reversal_category, cr.cash_receipt_id) F_ALL_AMOUNTS,
	AR_ARXSOC_XMLP_PKG.pa_actual_amount_p pa_actual_amount,
	AR_ARXSOC_XMLP_PKG.pa_unidentified_amount_p pa_unidentified_amount,
	AR_ARXSOC_XMLP_PKG.pa_misc_amount_p pa_misc_amount,
	AR_ARXSOC_XMLP_PKG.pa_nsf_amount_p pa_nsf_amount,
	AR_ARXSOC_XMLP_PKG.pa_applied_count_p pa_applied_count,
	AR_ARXSOC_XMLP_PKG.pa_unapplied_count_p pa_unapplied_count,
	AR_ARXSOC_XMLP_PKG.pa_unidentified_count_p pa_unidentified_count,
	AR_ARXSOC_XMLP_PKG.pa_misc_count_p pa_misc_count
from ar_cash_receipts cr, ar_cash_receipt_history crh
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'
union all
select  b.currency_code, 
            b.deposit_date,
            -1,
             null,
             0,
             b.control_amount,
             null,
             null,
             null, 
	AR_ARXSOC_XMLP_PKG.cr_data_foundformula(b.currency_code) CR_DATA_FOUND, 
	AR_ARXSOC_XMLP_PKG.ca_summary_labelformula(b.currency_code) ca_summary_label, 
	AR_ARXSOC_XMLP_PKG.ca_difference_amountformula(:c_rcpt_control_amount_B, :ca_actual_amount) ca_difference_amount, 
	AR_ARXSOC_XMLP_PKG.ca_applied_amountformula(:ca_applied_amount_B, :ca_misc_amount) ca_applied_amount, 
	AR_ARXSOC_XMLP_PKG.f_all_amountsformula(NULL, NULL, NULL, NULL, NULL) F_ALL_AMOUNTS,
	AR_ARXSOC_XMLP_PKG.pa_actual_amount_p pa_actual_amount,
	AR_ARXSOC_XMLP_PKG.pa_unidentified_amount_p pa_unidentified_amount,
	AR_ARXSOC_XMLP_PKG.pa_misc_amount_p pa_misc_amount,
	AR_ARXSOC_XMLP_PKG.pa_nsf_amount_p pa_nsf_amount,
	AR_ARXSOC_XMLP_PKG.pa_applied_count_p pa_applied_count,
	AR_ARXSOC_XMLP_PKG.pa_unapplied_count_p pa_unapplied_count,
	AR_ARXSOC_XMLP_PKG.pa_unidentified_count_p pa_unidentified_count,
	AR_ARXSOC_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