AR Remittance Batch Management

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Remittance Batch Management Report (XML)
Short Name: ARXAPRMB_XML
DB package: AR_ARXAPRMB_XMLP_PKG
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
Parameter Name SQL text Validation
Ledger Id
 
LOV Oracle
Remittance Bank Account
 
LOV Oracle
Remittance Bank Branch
 
LOV Oracle
Remittance Bank
 
LOV Oracle
Remittance Method
 
LOV Oracle
Include Formatted Batches
 
LOV Oracle
Batch Name High
 
LOV Oracle
Batch Name Low
 
LOV Oracle
Deposit Number High
 
LOV Oracle
Deposit Number Low
 
LOV Oracle
Remittance Date To
 
Date
Remittance Date From
 
Date
Summary Or Detailed
 
LOV Oracle
Status
 
LOV Oracle
Order By
 
LOV Oracle