CE Batches Available for Reconciliation - draft

➜ Back to Library
Description
Categories: BI Publisher
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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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
Batch Type 
 
 LOV Oracle
AP/AR Operating Unit 
 
 LOV Oracle
Bank Account Number 
 
 LOV Oracle
Order By 
 
 LOV Oracle
Download
 
Related Blitz Reports
GL Account Analysis (Distributions) CE Bank Statement and Reconciliation CE Transactions Available for Reconciliation CE Cleared Transactions AP Invoices and Lines AP Invoice Payments AR Unapplied Receipts Register AR Miscellaneous Receipts CE General Ledger Reconciliation
Blitz Report™

Blitz Report™ provides multiple benefits: