AR Deposited Cash Report - Open Detail

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Deposited Cash Report - Open Detail (XML)
Short Name: ARXSOC_XML
DB package: AR_ARXSOC_XMLP_PKG
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
Parameter Name SQL text Validation
Ledger Id
 
LOV Oracle
Bank Account Name High
 
LOV Oracle
Bank Account Name Low
 
LOV Oracle
Deposit Date High
 
Date
Deposit Date Low
 
Date
Order By
 
LOV Oracle
Ask a question