CE Sweep Transactions Generation Execution - draft

➜ Back to Library
Description
Categories: BI Publisher
Application: Cash Management
Source: Sweep Transactions Generation Execution Report (XML) - Not Supported: Reserved For Future Use
Short Name: CEZBAERR_XML
DB package: CE_CEZBAERR_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    	'B'			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,
	l2.meaning	trx_type,
	nvl(e.application_short_name,'CE')	application_short_name,
	bb.branch_party_id	bank_branch_id,
	sl.bank_Account_text	bank_Account_text,
	e.deal_type	deal_type,
	e.deal_num	deal_num,
	e.transaction_num	transaction_num,
	offset_deal_num	offset_deal_num,
	1 count,
	nvl(sl.currency_code,ba.currency_code)	C_CURRENCY_CODE,
	offset_transaction_num	offset_transaction_num,
	null counter_name,
	null subtype_name,
	null status,
	null cashflows_create,
	CE_CEZBAERR_XMLP_PKG.cf_line_countformula('B') CF_LINE_COUNT_MSG,
	CE_CEZBAERR_XMLP_PKG.cf_1formula('B') CF_ERROR_TYPE_MSG,
	CE_CEZBAERR_XMLP_PKG.c_prt_message_nameformula(null) C_PRT_MESSAGE_NAME
from
	ce_lookups	l2,
	ce_bank_accts_gt_v 	ba,
	ce_bank_branches_v 	bb,
	ce_Statement_headers	sh,
	ce_statement_lines	sl,
	ce_zba_Deal_messages	e
where
	l2.lookup_type = 'BANK_TRX_TYPE'
AND	l2.lookup_code = sl.trx_type
AND         :c_profile = 'XTR'
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	trunc(sh.statement_date) >= nvl(:LP_STAT_DATE_FROM,trunc(sh.statement_date))
AND	trunc(sh.statement_date) <= nvl(:LP_STAT_DATE_TO,trunc(sh.statement_date))
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	e.deal_status_flag = 'Y'
union all
select    	'B'	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,
	l2.meaning	trx_type,
	nvl(e.application_short_name,'CE')	application_short_name,
	bb.branch_party_id	bank_branch_id,
	sl.bank_Account_text	bank_Account_text,
	e.deal_type	deal_type,
	e.deal_num	deal_num,
	e.transaction_num	transaction_num,
	offset_deal_num	offset_deal_num,
	1 count,
	cpo.currency_code	C_CURRENCY_CODE,
	offset_transaction_num	offset_transaction_num,
	xle.name counter_name,
	trxn.transaction_sub_type_name subtype_name,
	l3.meaning status,
	l4.meaning cashflows_create,
	CE_CEZBAERR_XMLP_PKG.cf_line_countformula('B') CF_LINE_COUNT_MSG,
	CE_CEZBAERR_XMLP_PKG.cf_1formula('B') CF_ERROR_TYPE_MSG,
	CE_CEZBAERR_XMLP_PKG.c_prt_message_nameformula(null) C_PRT_MESSAGE_NAME
from
	ce_lookups	l2,
	ce_bank_accts_gt_v 	ba,
	ce_bank_branches_v 	bb,
	ce_Statement_headers	sh,
	ce_statement_lines	sl,
	ce_zba_Deal_messages	e,
	ce_cashpools			cpo,
	ce_payment_transactions bat,
	ce_trxns_subtype_codes	trxn,
	xle_firstparty_information_v xle,
	ce_lookups l3,
	ce_lookups l4
where
	l2.lookup_type = 'BANK_TRX_TYPE'
AND	l2.lookup_code = sl.trx_type
AND         :c_profile = 'CE'
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	trunc(sh.statement_date) >= nvl(:LP_STAT_DATE_FROM,trunc(sh.statement_date))
AND	trunc(sh.statement_date) <= nvl(:LP_STAT_DATE_TO,trunc(sh.statement_date))
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	e.deal_status_flag = 'Y'
AND e.cashpool_id = cpo.cashpool_id
AND e.transaction_num = bat.trxn_reference_number
AND cpo.trxn_subtype_code_id = trxn.trxn_subtype_code_id(+)
AND bat.destination_legal_entity_id = xle.legal_entity_id
AND l3.lookup_type = 'CE_BAT_STATUS'
AND l3.lookup_code = bat.trxn_status_code
AND l4.lookup_type = 'YES/NO'
AND l4.lookup_code = e.cashflows_created_flag
union all
select  	'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,
	l2.meaning	trx_type,
	nvl(e.application_short_name,'CE') application_short_name,
	bb.branch_party_id	bank_branch_id,
	sl.bank_account_text	bank_account_text,
	null deal_type,
	null deal_num,
	null transaction_num,
	null offset_deal_num,
	1 count,
	decode(:c_profile,'CE',nvl(cpo.currency_code,ba.currency_code),'XTR',nvl(sl.currency_code,ba.currency_code))	C_CURRENCY_CODE,
	null offset_transaction_num,
	null counter_name,
	null subtype_name,
	null status,
	null cashflows_create,
	CE_CEZBAERR_XMLP_PKG.cf_line_countformula('B') CF_LINE_COUNT_MSG,
	CE_CEZBAERR_XMLP_PKG.cf_1formula('B') CF_ERROR_TYPE_MSG,
	CE_CEZBAERR_XMLP_PKG.c_prt_message_nameformula(null) C_PRT_MESSAGE_NAME
FROM
	ce_lookups	l2,
	ce_bank_accts_gt_v 	ba,
	ce_bank_branches_v 	bb,
	ce_statement_headers	sh,
	ce_statement_lines	sl,
	ce_zba_deal_messages	e,
	ce_cashpools			cpo
WHERE
	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	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	trunc(sh.statement_date) >= nvl(:LP_STAT_DATE_FROM,trunc(sh.statement_date))
AND	trunc(sh.statement_date) <= nvl(:LP_STAT_DATE_TO,trunc(sh.statement_date))
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	e.deal_status_flag = 'N'
AND e.cashpool_id = cpo.cashpool_id(+)
UNION ALL
SELECT 	'A' 		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,
	NULL	trx_type,
	NVL(e.application_short_name,'CE')	application_short_name,
	bb.branch_party_id	bank_branch_id,
	NULL	bank_Account_text,
	null deal_type,
	null deal_num,
	null transaction_num,
	null offset_deal_num,
	1 count,
	null C_CURRENCY_CODE,
	null offset_transaction_num,
	null counter_name,
	null subtype_name,
	null status,
	null cashflows_create,
	CE_CEZBAERR_XMLP_PKG.cf_line_countformula('B') CF_LINE_COUNT_MSG,
	CE_CEZBAERR_XMLP_PKG.cf_1formula('B') CF_ERROR_TYPE_MSG,
	CE_CEZBAERR_XMLP_PKG.c_prt_message_nameformula(null) C_PRT_MESSAGE_NAME
FROM
	ce_bank_accts_gt_v	ba,
	ce_bank_branches_v 	bb,
	ce_statement_headers	sh,
	ce_zba_deal_messages	e
WHERE
	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	trunc(sh.statement_date) >= nvl(:LP_STAT_DATE_FROM,trunc(sh.statement_date))
AND trunc(sh.statement_date) <= nvl(:LP_STAT_DATE_TO,trunc(sh.statement_date))
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 e.deal_status_flag = 'N'
order by break_group, line_no
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 General Ledger Reconciliation CE Bank Statement Cashflow Creation Execution - draft CE Transactions Available for Reconciliation CE Bank Statement and Reconciliation CE Cash Leveling Execution - draft CE Bank Account Balances CE Bank Statement Import Execution CE Bank Statement Import Diagnostics CE AutoReconciliation Execution - draft
Blitz Report™

Blitz Report™ provides multiple benefits: