AR Remittance Batch Management

SELECT  decode(batch.batch_applied_status,
                        'COMPLETED_CREATION',1,
                        'COMPLETED_APPROVAL',2,
                        'STARTED_CREATION',3,
                        'STARTED_APPROVAL',4,
                        'STARTED_FORMAT',5,
                        10)                         status_order,
        decode(:P_SORT_BY_T,
                     'REMITTANCE ACCOUNT', cba.bank_account_name,
                      null)                         dcol_A,
        decode(:P_SORT_BY_T,
                    'REMITTANCE ACCOUNT', batch.remit_method_code,
                    null)                           dcol_B,
        status_lc.meaning                batch_status,
        batch.batch_applied_status status_code,
        batch.name                           batch_name,
        batch.batch_date                 batch_date,
        batch.bank_deposit_number      reference,
        rbank.bank_name,
        rbank.bank_branch_name,
        cba.bank_account_name  remit_acct,
        rmethod_lc.meaning            remit_mtd,
        mtd.name                               pmt_method,
        batch.gl_date                        gl_date,
        batch.currency_code           currency_code,
        batch.batch_id                      batch_id,
	AR_ARXAPRMB_XMLP_PKG.amountformula(batch.batch_applied_status, batch.batch_id) amount,
	--&currency_code DUMMY_CURRENCY,
	batch.currency_code DUMMY_CURRENCY,
	AR_ARXAPRMB_XMLP_PKG.det_batch_statusformula(status_lc.meaning) DET_BATCH_STATUS
FROM    ar_batches         batch,
        ar_receipt_methods mtd,
        ce_bank_branches_v   rbank,
        ce_bank_acct_uses  racct,
        ce_bank_accounts   cba,
        ar_lookups                status_lc,
        ar_lookups                rmethod_lc
WHERE    mtd.receipt_method_id(+) = batch.receipt_method_id
AND          status_lc.lookup_type(+)	= 'BATCH_APPLIED_STATUS'
AND	status_lc.lookup_code(+)	= batch.batch_applied_status
AND	rmethod_lc.lookup_code(+)	= batch.remit_method_code
AND	rmethod_lc.lookup_type(+)	= 'REMITTANCE_METHOD'
AND	batch.remit_method_code =
                                   nvl(:P_REMITTANCE_METHOD,batch.remit_method_code)
AND	batch.type			= 'REMITTANCE'
AND	racct.bank_acct_use_id	   = batch.remit_bank_acct_use_id
AND         racct.bank_account_id                   = cba.bank_account_id
AND	rbank.branch_party_id		=  cba.bank_branch_id
AND	batch.remit_bank_acct_use_id = nvl(:P_REMIT_BANK_ACCOUNT,
                                                                                   batch.remit_bank_acct_use_id)
AND	rbank.bank_branch_name = nvl(:P_REMIT_BANK_BRANCH,rbank.bank_branch_name)
AND	rbank.bank_name = nvl(:P_REMIT_BANK,rbank.bank_name)
AND	 batch.name between nvl(:p_batch_name_low,batch.name)
	  and nvl(:p_batch_name_high,batch.name)
AND	(
	 batch.bank_deposit_number between
		nvl(:p_depno_low,batch.bank_deposit_number)
	                  and  nvl(:p_depno_high,batch.bank_deposit_number)
	or 	(
	 	batch.bank_deposit_number is null
                     	 and :p_depno_low is null
                       	and :p_depno_high is null
		)
	)
AND     batch.batch_date                between
    	  nvl(:p_rem_date_from,to_date('01-01-0001','DD-MM-YYYY'))
             and
             nvl(:p_rem_date_to,to_date('12-12-4000','DD-MM-YYYY'))
AND     (
                batch.batch_applied_status != 'COMPLETED_FORMAT'
                or
                nvl(:P_INCLUDE_FORMATTED,'Y') = 'Y'
               )
&LP_STATUS
ORDER BY 6,7,8,11,12,14