CE Bank Statement Import Validation - Israel

Description
Categories: BI Publisher, Financials
Application: Cash Management
Source: Bank Statement Import Validation - Israel
Short Name: CEBNKSTMTIMP
DB package: CE_BNK_STMT_IMP_RPT_PKG
SELECT		inline.BANK_ACCOUNT_NUM				BANK_ACCOUNT_NUM
			, inline.BANK_STMT_NUM			BANK_STMT_NUM
			, inline.BANK_STMT_DATE				BANK_STMT_DATE
			, inline.STATUS						STATUS
			, inline.control_begin_balance		BEGIN_BALANCE
	   		, inline.control_end_balance		CLOSE_BALANCE
FROM (
		SELECT  	&lc_uploaded_select_columns
	   				, TRUNC(statement_date)	BANK_STMT_DATE
	   				, &gc_status_current STATUS
	   				, control_begin_balance
	   				, control_end_balance 
		FROM   		ce_statement_headers_int uploaded
		WHERE  		&lc_uploaded_where_conditions
		AND         uploaded.statement_date = (SELECT MIN(inner_uploaded.statement_date)
		                                       FROM ce_statement_headers_int inner_uploaded
											   WHERE inner_uploaded.bank_branch_name = :gc_bank_branch_name
											   AND inner_uploaded.bank_account_num = uploaded.bank_account_num
											   GROUP BY inner_uploaded.bank_account_num )
		ORDER BY	statement_date
	 ) inline
UNION ALL
SELECT		inline.BANK_ACCOUNT_NUM				BANK_ACCOUNT_NUM
			, inline.statement_number			BANK_STMT_NUM
			, inline.statement_date				BANK_STMT_DATE
			, inline.STATUS						STATUS
			, inline.control_begin_balance		BEGIN_BALANCE
	   		, inline.control_end_balance		CLOSE_BALANCE
FROM 
	  (  SELECT  	&lc_latest_bank_acc_num 		BANK_ACCOUNT_NUM
 	   				, statement_number  			
			   		, statement_date 				
	   				, &gc_status_latest STATUS
			   		, control_begin_balance	
	   				, control_end_balance		
		FROM 		ce_statement_headers latest
					&lc_latest_bank_acc_from
		WHERE 		statement_date < (SELECT MIN(TRUNC(uploaded.statement_date))
 									  FROM 	 ce_statement_headers_int uploaded
									  WHERE  &lc_uploaded_where_conditions
									  AND	 uploaded.bank_account_num = &lc_latest_bank_acc_num
									  GROUP BY uploaded.bank_account_num  ) 
					&lc_latest_bank_acc_where
	  ) inline,
	  (  SELECT  	&lc_latest_bank_acc_num 		BANK_ACCOUNT_NUM 			
			   		, MAX(statement_date) 			statement_date				
		FROM 		ce_statement_headers latest
					&lc_latest_bank_acc_from
		WHERE 		statement_date < (SELECT MIN(TRUNC(uploaded.statement_date))
 									  FROM 	 ce_statement_headers_int uploaded
									  WHERE  &lc_uploaded_where_conditions
									  AND	 uploaded.bank_account_num = &lc_latest_bank_acc_num
									  GROUP BY uploaded.bank_account_num  ) 
					&lc_latest_bank_acc_where
		GROUP BY    &lc_latest_bank_acc_num
	  ) inline1
WHERE	inline.statement_date = inline1.statement_date 	
AND		inline.BANK_ACCOUNT_NUM = inline1.BANK_ACCOUNT_NUM
Parameter Name SQL text Validation
Statement Number
 
LOV Oracle
Bank Account Number
 
LOV Oracle
Bank Branch Name
 
LOV Oracle