CE AutoReconciliation Execution - draft

➜ Back to Library
Description
Categories: BI Publisher
Application: Cash Management
Source: AutoReconciliation Execution Report (XML) - Not Supported: Reserved For Future Use
Short Name: CEXINERR_XML
DB package: CE_CEXINERR_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 
	l1.meaning				error_type,
	'D'				break_group,
	sh.statement_number			statement_no,
	sh.doc_sequence_value                                   doc_sequence_value,
	sl.line_number			line_no,
	sh.statement_date			statement_date,
	sl.trx_date				trx_date,
	ba.bank_account_num			bank_acc, 
	ba.bank_account_name		bank_account_name,
	ba.currency_code			acc_currency,
	NULL				message_name,
	nvl(sl.amount,0)			amount,
	acr.currency_code		 	C_CURRENCY_CODE,
	l2.meaning				trx_type,
	acr.receipt_number			trx_no,
	'CE'				application_short_name,
	bb.branch_party_id			bank_branch_id,
	CE_CEXINERR_XMLP_PKG.c_prt_message_nameformula(NULL) C_PRT_MESSAGE_NAME
FROM        ce_lookups			l1,
	ce_lookups			l2,
	ce_bank_accts_gt_v			ba,
	ce_bank_branches_v			bb,
                   ce_statement_headers		sh,
                   ce_statement_lines			sl,
	ar_cash_receipts_all	acr,
                   ce_222_reconciled_v cr
WHERE     l1.lookup_type = 'ABR_REPORT_EXCEPTIONS'
AND	l1.lookup_code = 'MISC_RECEIPT_CREATED' 
AND	l2.lookup_type = 'BANK_TRX_TYPE'
AND 	l2.lookup_code = sl.trx_type
AND	bb.branch_party_id = ba.bank_branch_id
AND           bb.branch_party_id =  :P_BANK_BRANCH_ID
AND           ba.bank_account_id = sh.bank_account_id
AND           sh.statement_header_id = sl.statement_header_id
AND	ba.bank_account_id = DECODE(:P_BANK_ACCOUNT_ID, NULL, ba.bank_account_id, :P_BANK_ACCOUNT_ID)
AND     to_char(sh.statement_date,'YYYY/MM/DD') >= nvl(to_char(:P_STAT_DATE_FROM,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') )
AND     to_char(sh.statement_date,'YYYY/MM/DD') <= nvl(to_char(:P_STAT_DATE_TO,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') ) 
AND       	sh.statement_number >= nvl(:P_STAT_NUMBER_FROM,sh.statement_number)
AND	sh.statement_number <= nvl(:P_STAT_NUMBER_TO,sh.statement_number)    
and acr.cash_receipt_id = cr.cash_receipt_id
and cr.statement_line_id = sl.statement_line_id
and acr.comments = 'Created by Auto Bank Rec'
UNION ALL
SELECT       
	l1.description			error_type,
	'C'				break_group,
	sh.statement_number			statement_no,
	sh.doc_sequence_value                                   doc_sequence_value,
	sl.line_number			line_no,
	sh.statement_date			statement_date,
	sl.trx_date				trx_date,
	ba.bank_account_num			bank_acc, 
	ba.bank_account_name		bank_account_name,
	ba.currency_code			acc_currency,
	e.message_name    			message_name,
	nvl(sl.amount,0)			amount,
	nvl(sl.currency_code,ba.currency_code) 	C_CURRENCY_CODE,
	l2.meaning				trx_type,
	sl.bank_trx_number			trx_no,
	NVL(e.application_short_name,'CE')	application_short_name,
	bb.branch_party_id			bank_branch_id,
	CE_CEXINERR_XMLP_PKG.c_prt_message_nameformula(e.message_name) C_PRT_MESSAGE_NAME
FROM        ce_lookups				l1,
	ce_lookups			l2,
	ce_bank_accts_gt_v			ba,
	ce_bank_branches_v			bb,
                   ce_statement_headers		sh,
                   ce_statement_lines			sl,
	ce_reconciliation_errors		e
WHERE      l2.lookup_type = 'BANK_TRX_TYPE'
AND 	l2.lookup_code = sl.trx_type
AND	l1.lookup_type = 'ABR_REPORT_EXCEPTIONS'
AND	l1.lookup_code='RECONCILIATION'
AND	bb.branch_party_id = ba.bank_branch_id
AND           bb.branch_party_id =  :P_BANK_BRANCH_ID
AND           ba.bank_account_id = sh.bank_account_id
AND	ba.account_classification = 'INTERNAL'
AND           sh.statement_header_id = sl.statement_header_id
AND           sh.statement_header_id = e.statement_header_id
AND           sl.statement_line_id = e.statement_line_id
AND           e.statement_line_id is not null
and           sl.status != 'EXTERNAL'
AND	ba.bank_account_id = DECODE(:P_BANK_ACCOUNT_ID, NULL, ba.bank_account_id, :P_BANK_ACCOUNT_ID)
AND     to_char(sh.statement_date,'YYYY/MM/DD') >= nvl(to_char(:P_STAT_DATE_FROM,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') )
AND     to_char(sh.statement_date,'YYYY/MM/DD') <= nvl(to_char(:P_STAT_DATE_TO,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') ) 
AND       	sh.statement_number >= nvl(:P_STAT_NUMBER_FROM,sh.statement_number)
AND	sh.statement_number <= nvl(:P_STAT_NUMBER_TO,sh.statement_number)    
UNION ALL
SELECT       
	l1.description			error_type,
	'C'				break_group,
	sh.statement_number			statement_no,
	sh.doc_sequence_value                                   doc_sequence_value,
	0				line_no,
	sh.statement_date			statement_date,
	to_date(NULL)			trx_date,
	ba.bank_account_num			bank_acc, 
	ba.bank_account_name		bank_account_name,
	ba.currency_code			acc_currency,
	e.message_name    			message_name,
	to_number(NULL)			amount,
	ba.currency_code		 	C_CURRENCY_CODE,
	NULL				trx_type,
	NULL				trx_no,
	NVL(e.application_short_name,'CE')	application_short_name,
	bb.branch_party_id			bank_branch_id,
	CE_CEXINERR_XMLP_PKG.c_prt_message_nameformula(e.message_name) C_PRT_MESSAGE_NAME
FROM        ce_lookups				l1,
	ce_bank_accts_gt_v			ba,
	ce_bank_branches_v			bb,
                   ce_statement_headers		sh,
	ce_reconciliation_errors		e
WHERE      l1.lookup_type = 'ABR_REPORT_EXCEPTIONS'
AND	l1.lookup_code='RECONCILIATION'
AND	bb.branch_party_id = ba.bank_branch_id
AND           bb.branch_party_id =  :P_BANK_BRANCH_ID
AND           ba.bank_account_id = sh.bank_account_id
AND	ba.account_classification = 'INTERNAL'
AND           sh.statement_header_id = e.statement_header_id
AND           e.statement_line_id is null
AND	ba.bank_account_id = DECODE(:P_BANK_ACCOUNT_ID, NULL, ba.bank_account_id, :P_BANK_ACCOUNT_ID)
AND     to_char(sh.statement_date,'YYYY/MM/DD') >= nvl(to_char(:P_STAT_DATE_FROM,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') )
AND     to_char(sh.statement_date,'YYYY/MM/DD') <= nvl(to_char(:P_STAT_DATE_TO,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') ) 
AND       	sh.statement_number >= nvl(:P_STAT_NUMBER_FROM,sh.statement_number)
AND	sh.statement_number <= nvl(:P_STAT_NUMBER_TO,sh.statement_number)    
UNION ALL
(SELECT       
	l.description			error_type,
	'A'				break_group,
	e.statement_number			statement_no,
                  to_number(NULL)			doc_sequence_value,
	1				line_no,
	sh.statement_date			statement_date,
	sh.statement_date			trx_date,
	e.bank_account_num			bank_acc,
	ba.bank_account_name		bank_account_name,
	ba.currency_code			acc_currency,
	e.message_name			message_name,
	0				amount,
	' '				C_CURRENCY_CODE,
	' '				trx_type,
	' '				trx_no,
	NVL(e.application_short_name,'CE')	application_short_name,
	bb.branch_party_id			bank_branch_id,
	CE_CEXINERR_XMLP_PKG.c_prt_message_nameformula(e.message_name) C_PRT_MESSAGE_NAME
FROM         ce_lookups			l,
                   ce_statement_headers_int	sh,
                   ce_header_interface_errors	e,
	ce_bank_branches_v		bb,
	ce_bank_accts_gt_v		ba
WHERE      l.lookup_type = 'ABR_REPORT_EXCEPTIONS'
AND	l.lookup_code='STATEMENT'		
AND 	sh.bank_account_num = e.bank_account_num
AND           sh.statement_number = e.statement_number
AND	sh.bank_branch_name = bb.bank_branch_name
AND	e.bank_account_num = NVL(ba.bank_account_num, e.bank_account_num)
AND	ba.bank_branch_id = bb.branch_party_id
AND	bb.bank_branch_name = DECODE(:C_BANK_BRANCH_NAME_DSP, :C_ALL_TRANSLATION, bb.bank_branch_name, :C_BANK_BRANCH_NAME_DSP)
AND	ba.bank_account_id = NVL(:P_BANK_ACCOUNT_ID, ba.bank_account_id)
AND	ba.account_classification = 'INTERNAL'
AND     to_char(sh.statement_date,'YYYY/MM/DD') >= nvl(to_char(:P_STAT_DATE_FROM,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') )
AND     to_char(sh.statement_date,'YYYY/MM/DD') <= nvl(to_char(:P_STAT_DATE_TO,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') ) 
AND	sh.statement_number >= nvl(:P_STAT_NUMBER_FROM,sh.statement_number)
AND	sh.statement_number <= nvl(:P_STAT_NUMBER_TO,sh.statement_number)    
UNION
SELECT       
	l.description			error_type,
	'A'				break_group,
	e.statement_number			statement_no,
                  to_number(NULL)			doc_sequence_value,
	1				line_no,
	sh.statement_date			statement_date,
	sh.statement_date			trx_date,
	e.bank_account_num			bank_acc,
	ba.bank_account_name		bank_account_name,
	ba.currency_code			acc_currency,
	e.message_name			message_name,
	0				amount,
	' '				C_CURRENCY_CODE,
	' '				trx_type,
	' '				trx_no,
	NVL(e.application_short_name,'CE')	application_short_name,
	bb.branch_party_id			bank_branch_id,
	CE_CEXINERR_XMLP_PKG.c_prt_message_nameformula(e.message_name) C_PRT_MESSAGE_NAME
FROM         ce_lookups			l,
                   ce_statement_headers	sh,
                   ce_header_interface_errors	e,
	ce_bank_branches_v		bb,
	ce_bank_accts_gt_v		ba
WHERE      l.lookup_type = 'ABR_REPORT_EXCEPTIONS'
AND	l.lookup_code='STATEMENT'		
AND 	e.bank_account_num = ba.bank_account_num
AND           e.statement_number = sh.statement_number
AND           sh.bank_account_id = ba.bank_account_id
AND	ba.bank_branch_id = bb.branch_party_id
AND	bb.bank_branch_name = DECODE(:C_BANK_BRANCH_NAME_DSP, :C_ALL_TRANSLATION, bb.bank_branch_name, :C_BANK_BRANCH_NAME_DSP)
AND	ba.bank_account_id = NVL(:P_BANK_ACCOUNT_ID, ba.bank_account_id)
AND	ba.account_classification = 'INTERNAL'
AND     to_char(sh.statement_date,'YYYY/MM/DD') >= nvl(to_char(:P_STAT_DATE_FROM,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') )
AND     to_char(sh.statement_date,'YYYY/MM/DD') <= nvl(to_char(:P_STAT_DATE_TO,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') ) 
AND	sh.statement_number >= nvl(:P_STAT_NUMBER_FROM,sh.statement_number)
AND	sh.statement_number <= nvl(:P_STAT_NUMBER_TO,sh.statement_number) )
UNION ALL
SELECT	
	l.description				error_type,
	'B'					break_group,
	e.statement_number				statement_no,
 	to_number(NULL)				doc_sequence_value,
	e.line_number				line_no,
	sh.statement_date				statement_date,
	sl.trx_date					trx_date,
	e.bank_account_num				bank_acc,
	ba.bank_account_name			bank_account_name,
	ba.currency_code				acc_currency,
	e.message_name				message_name,
	nvl(sl.amount,0)				amount,
	NVL(curr.currency_code, ba.CURRENCY_CODE)	C_CURRENCY_CODE,
	' '					trx_type,
	' '					trx_no,
	NVL(e.application_short_name,'CE')		application_short_name,
	bb.branch_party_id				bank_branch_id,
	CE_CEXINERR_XMLP_PKG.c_prt_message_nameformula(e.message_name) C_PRT_MESSAGE_NAME
FROM     	fnd_currencies				curr,
	ce_lookups				l,
	ce_statement_headers_int			sh,
	ce_statement_lines_interface			sl,
	ce_line_interface_errors			e,
	ce_bank_branches_v				bb,
	ce_bank_accts_gt_v				ba
WHERE     curr.currency_code(+) = sl.currency_code 
AND	l.lookup_type = 'ABR_REPORT_EXCEPTIONS'
AND	l.lookup_code='LINE'
AND	sh.bank_account_num = sl.bank_account_num
AND	sh.statement_number = sl.statement_number	
AND	sl.line_number = e.line_number	
AND  	sl.statement_number = e.statement_number
AND	sl.bank_account_num = e.bank_account_num
AND	bb.bank_branch_name = sh.bank_branch_name
AND	bb.branch_party_id = ba.bank_branch_id
AND	e.bank_account_num = NVL(ba.bank_account_num, e.bank_account_num)
AND	bb.bank_branch_name = DECODE(:C_BANK_BRANCH_NAME_DSP, :C_ALL_TRANSLATION, bb.bank_branch_name, :C_BANK_BRANCH_NAME_DSP)
AND	ba.bank_account_id = NVL(:P_BANK_ACCOUNT_ID, ba.bank_account_id)
AND	ba.account_classification = 'INTERNAL'
AND     to_char(sh.statement_date,'YYYY/MM/DD') >= nvl(to_char(:P_STAT_DATE_FROM,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') )
AND     to_char(sh.statement_date,'YYYY/MM/DD') <= nvl(to_char(:P_STAT_DATE_TO,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') ) 
AND	sh.statement_number >= nvl(:P_STAT_NUMBER_FROM,sh.statement_number)
AND	sh.statement_number <= nvl(:P_STAT_NUMBER_TO,sh.statement_number)    
ORDER BY	5
Parameter Name SQL text Validation
Bank Branch Name 
 
 LOV Oracle
Bank Account Number 
 
 LOV Oracle
Statement Number From 
 
 LOV Oracle
Statement Number To 
 
 LOV Oracle
Statement Date From 
 
 Date
Statement Date To 
 
 Date
Download
 
Related Blitz Reports
CE Transactions Available for Reconciliation CE Bank Statement and Reconciliation CE Bank Statement Import Execution CE Cash in Transit EC: CE Cash in Transit CE General Ledger Reconciliation CE Bank Statement Import Diagnostics CE Cleared Transactions CE Sweep Transactions Generation Execution - draft
Blitz Report™

Blitz Report™ provides multiple benefits: