CE Batches Available for Reconciliation - draft

Description
Categories: BI Publisher, Financials
Application: Cash Management
Source: Batches Available for Reconciliation (XML) - Not Supported: Reserved For Future Use
Short Name: CEXAVBCH_XML
DB package: CE_CEXAVBCH_XMLP_PKG
SELECT 		avbt.trx_type				type,
		avbt.trx_type				break_type,
		avbt.type_meaning				type_dsp,		
		avbt.trx_date				trx_date,
		to_date(NULL)				maturity_date,
		to_number(NULL)				bank_account_amount,
		avbt.trx_number				trx_number,
		NULL					payment_method,
		avbt.currency_code				currency_code,
		to_number(NULL)				amount,
		to_number(NULL)				account_amount,
		10					transaction_order,
		avbt.batch_id				batch_id,
		avbt.batch_number				batch_number,
		avbt.remittance_number			remittance_number,
		to_number(null),
		sp.name					ORG_NAME, 
	CE_CEXAVBCH_XMLP_PKG.c_account_amountformula(avbt.trx_type, avbt.batch_id) C_ACCOUNT_AMOUNT, 
	CE_CEXAVBCH_XMLP_PKG.c_amountformula(avbt.trx_type, avbt.batch_id) C_AMOUNT, 
	CE_CEXAVBCH_XMLP_PKG.c_bank_account_amountformula(avbt.trx_type, avbt.batch_id) C_BANK_ACCOUNT_AMOUNT,
	CE_CEXAVBCH_XMLP_PKG.C_BATCH_AMOUNT_p C_BATCH_AMOUNT,
	CE_CEXAVBCH_XMLP_PKG.C_BATCH_BANK_ACCOUNT_AMOUNT_p C_BATCH_BANK_ACCOUNT_AMOUNT
FROM 	CE_222_BATCHES_V	avbt, ce_security_profiles_gt sp
	WHERE	avbt.bank_account_id =:P_BANK_ACCOUNT AND
 		avbt.trx_type  = 'RECEIPT'				AND
		:P_QUERY_RECEIPTS = 'Y'				AND
		avbt.org_id = sp.organization_id				and
		sp.organization_type = 'OPERATING_UNIT'		and
		avbt.org_id = nvl(:P_ORG_ID, avbt.org_id )		and
		EXISTS ( SELECT	 null 
	                FROM	 		     	 ce_222_transactions_v avtr
	                WHERE	avbt.batch_id = avtr.batch_id	and 			avbt.bank_account_id = avtr.bank_account_id					              )
UNION ALL
SELECT 		avbt.trx_type				type,
		avbt.trx_type				break_type,
		avbt.type_meaning				type_dsp,		
		avbt.trx_date				trx_date,
		to_date(NULL)				maturity_date,
		to_number(NULL)				bank_account_amount,
		avbt.trx_number				trx_number,
		NULL					payment_method,
		avbt.currency_code				currency_code,
		to_number(NULL)				amount,
		to_number(NULL)				account_amount,
		20					transaction_order,
		avbt.batch_id				batch_id,
		avbt.batch_number				batch_number,
		avbt.remittance_number			remittance_number,
		to_number(null),
		sp.name					ORG_NAME, 
	CE_CEXAVBCH_XMLP_PKG.c_account_amountformula(avbt.trx_type, avbt.batch_id) C_ACCOUNT_AMOUNT, 
	CE_CEXAVBCH_XMLP_PKG.c_amountformula(avbt.trx_type, avbt.batch_id) C_AMOUNT, 
	CE_CEXAVBCH_XMLP_PKG.c_bank_account_amountformula(avbt.trx_type, avbt.batch_id) C_BANK_ACCOUNT_AMOUNT,
	CE_CEXAVBCH_XMLP_PKG.C_BATCH_AMOUNT_p C_BATCH_AMOUNT,
	CE_CEXAVBCH_XMLP_PKG.C_BATCH_BANK_ACCOUNT_AMOUNT_p C_BATCH_BANK_ACCOUNT_AMOUNT
FROM 	CE_200_BATCHES_V	avbt, ce_security_profiles_gt sp
	WHERE	avbt.bank_account_id =:P_BANK_ACCOUNT AND
 		avbt.trx_type  = 'PAYMENT'				AND
		:P_QUERY_PAYMENTS = 'Y'				AND
		avbt.org_id = sp.organization_id				and
		sp.organization_type = 'OPERATING_UNIT'		and
		avbt.org_id = nvl(:P_ORG_ID, avbt.org_id )		and
		EXISTS( select null from ce_200_transactions_v avtr
	               where avbt.bank_account_id = avtr.bank_account_id and
		        avbt.batch_id = avtr.batch_id		AND
	                avbt.batch_number = avtr.batch_name	AND 
		       NVL(avtr.status, 'NEGOTIABLE') !=  'VOIDED')
&C_ORDER_BY
Parameter Name SQL text Validation
Order By
 
LOV Oracle
Bank Account Number
 
LOV Oracle
AP/AR Operating Unit
 
LOV Oracle
Batch Type
 
LOV Oracle