tda.sql

New Text editor similar to notepad with new features...........

CREATE OR REPLACE PROCEDURE uploadTDA( TDAfiledir IN VARCHAR2, TDAfilename IN VARCHAR2, tdasummary OUT VARCHAR2 ) AS
TDAfile 			utl_file.file_type;
LOGFILE 			utl_file.file_type;
rejectedTDA			utl_file.file_type;

logfiledir          VARCHAR(100);
logfilename         VARCHAR(100);

rejectedTDAdir      VARCHAR(100);
rejectedTDAfilename VARCHAR(100);

validLines          NUMBER:=0;
rejectedLines       NUMBER:=0;
updatedTDAAccounts  NUMBER:=0;
rejectedTDAAccounts NUMBER:=0;

vaccount_num		VARCHAR2( 11 );
vcustomer_id		VARCHAR2( 18 );
vshort_name			VARCHAR2( 110 );
vsub_product_code	VARCHAR2( 10 );
vproduct_identifier VARCHAR2( 4 );
vcurrency			VARCHAR2( 4 );
vod_class			VARCHAR2( 4 );
vstatus				VARCHAR2( 4 );
vcsi				VARCHAR2( 4 );
varm_type			VARCHAR2( 4 );
varm_code			VARCHAR2( 5 );
vcr_balance         NUMBER( 29, 2 );
vlink_id     		VARCHAR2( 11 );
vwaiver_code		VARCHAR2( 2 );

vaccount_type		VARCHAR2( 10 );

vnewline			VARCHAR2( 200 );
vTDAdate            VARCHAR2( 10 );

century_prefix      VARCHAR2( 1 );
year_prefix         VARCHAR2( 2 );

upload_count        NUMBER:=1;

err_num             NUMBER;
err_msg             VARCHAR2(100);

start_time          NUMBER;
end_time            NUMBER;

PROCEDURE insert_into_acc_master AS
BEGIN


--utl_file.put_line( LOGFILE,'vaccount_num:'||vaccount_num||'vcustomer_id: '||vcustomer_id||'vshort_name:'||vshort_name||'vsub_product_code:'||vsub_product_code||'vproduct_identifier : '||vproduct_identifier||'vcurrency:'||vcurrency||'vod_class:'||vod_class||'vstatus:'||vstatus||'vcsi:'||vcsi||'varm_type : '||varm_type||'varm_code: '||varm_code||'vcr_balance: '||vcr_balance||'vlink_id:'||vlink_id||'vwaiver_code:'||vwaiver_code);

utl_file.put_line( LOGFILE,'vaccount_num:'||vaccount_num);

utl_file.put_line( LOGFILE,'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%');


INSERT INTO ACCOUNT_MASTER ( account_num, customer_id, short_name, sub_product_code,
								 product_identifier, currency, od_class, status, open_date,
								 close_date, csi, arm_type, arm_code, waiver_code, account_type )
								 VALUES ( vaccount_num, vcustomer_id, vshort_name, vsub_product_code,
								 vproduct_identifier, vcurrency, vod_class, vstatus,
								 TO_DATE( vTDAdate, 'yyyy/mm/dd'), '', vcsi, varm_type,
								 varm_code, vwaiver_code, vaccount_type );
EXCEPTION
	WHEN OTHERS THEN
		dbms_output.put_line( 'Error while inserting into account_master' );
		err_num := SQLCODE;
		err_msg := SQLERRM(-err_num);
		utl_file.put_line( LOGFILE, 'Error while inserting into account_master : '||err_msg );
END;

PROCEDURE insert_into_acc_lvl_details AS
BEGIN
utl_file.put_line( LOGFILE, 'Inserting account_level_details :'|| vaccount_num );

INSERT INTO ACCOUNT_LEVEL_DETAILS ( account_num, account_monthly_bal_first, account_monthly_bal_second,
									account_monthly_bal_third, account_monthly_bal_fourth, account_waiver_first,
									account_waiver_second, account_waiver_third, account_waiver_fourth,
									account_charges_first, account_charges_second, account_charges_third,
									account_charges_fourth, account_frequency, account_current )
									VALUES ( vaccount_num, 0, 0, 0, vcr_balance, '', '', '', '',
									0, 0, 0, 0, '', '');
EXCEPTION
	WHEN OTHERS THEN
		dbms_output.put_line( 'Error while inserting into account_level_details' );
		err_num := SQLCODE;
		err_msg := SQLERRM(-err_num);
		utl_file.put_line( LOGFILE, 'Error while inserting into account_level_details : '||err_msg );
END;

PROCEDURE insert_into_deposit_master AS
BEGIN
INSERT INTO DEPOSIT_MASTER ( account_num, closing_balance, trn_date, link_id )
							VALUES ( vaccount_num, vcr_balance, TO_DATE( vTDAdate,'yyyy/mm/dd'), vlink_id );
EXCEPTION
	WHEN OTHERS THEN
		dbms_output.put_line( 'Error while inserting into deposit_master' );
		err_num := SQLCODE;
		err_msg := SQLERRM(-err_num);
		utl_file.put_line( LOGFILE, 'Error while inserting into deposit_master : '||err_msg );
END;

PROCEDURE update_am_waiver_code AS
BEGIN
	UPDATE ACCOUNT_MASTER SET waiver_code=vwaiver_code WHERE account_num=vaccount_num;
	utl_file.put_line( LOGFILE,'====== ACCOUNT_MASTER======='||vaccount_num||'*vwaiver_code:'||vwaiver_code);
EXCEPTION
	WHEN OTHERS THEN
		dbms_output.put_line( 'Error while updating waiver code in account_master' );
		err_num := SQLCODE;
		err_msg := SQLERRM(-err_num);
		utl_file.put_line( LOGFILE, 'Error while updating waiver code in account_master : '||err_msg );
END;

PROCEDURE update_am_status_and_closeDate AS
BEGIN
	UPDATE ACCOUNT_MASTER SET close_date=TO_DATE( vTDAdate,'yyyy/mm/dd'), status=vstatus WHERE account_num=vaccount_num;
utl_file.put_line( LOGFILE,'====== ACCOUNT_MASTER======='||vaccount_num||'*vstatus:'||vstatus||'::::'||TO_DATE( vTDAdate,'yyyy/mm/dd'));

EXCEPTION
	WHEN OTHERS THEN
		dbms_output.put_line( 'Error while updating status and close date in account_master' );
		err_num := SQLCODE;
		err_msg := SQLERRM(-err_num);
		utl_file.put_line( LOGFILE, 'Error while updating status and close date in account_master : '||err_msg );
END;

PROCEDURE update_acc_lvl_balance AS
BEGIN
	UPDATE ACCOUNT_LEVEL_DETAILS SET account_monthly_bal_fourth=vcr_balance WHERE account_num=vaccount_num;
utl_file.put_line( LOGFILE,'====== ACCOUNT_MASTER======='||vaccount_num||'*vcr_balance:'||vcr_balance);



EXCEPTION
	WHEN OTHERS THEN
		dbms_output.put_line( 'Error while updating balance in account_level_details' );
		err_num := SQLCODE;
		err_msg := SQLERRM(-err_num);
		utl_file.put_line( LOGFILE, 'Error while updating balance in account_level_details : '||err_msg );
END;

PROCEDURE update_deposit_master AS
BEGIN
	UPDATE DEPOSIT_MASTER SET link_id=vlink_id WHERE account_num=vaccount_num;
	utl_file.put_line( LOGFILE,'====== ACCOUNT_MASTER======='||vaccount_num||'*link_id:'||vlink_id);

EXCEPTION
	WHEN OTHERS THEN
		dbms_output.put_line( 'Error while updating link_id in deposit_master' );
		err_num := SQLCODE;
		err_msg := SQLERRM(-err_num);
		utl_file.put_line( LOGFILE, 'Error while updating link_id in deposit master : '||err_msg );
END;

PROCEDURE TDAproc AS
BEGIN
	/*if vcr_balance = 0 then
		vstatus := '07';
		update_am_status_and_closeDate;
	else
		vstatus := '01';
		update_am_waiver_code;
	end if;*/

	update_am_waiver_code;---3-

	IF SQL%ROWCOUNT = 0 THEN
		/*insert_into_acc_master;
		insert_into_acc_lvl_details;
		insert_into_deposit_master;*/
		GOTO end_of_block;
	ELSE
		update_acc_lvl_balance;---1--
	END IF;

	IF SQL%ROWCOUNT = 0 THEN
		/*insert_into_acc_lvl_details;
		insert_into_deposit_master;*/
		GOTO end_of_block;
	ELSE
		update_deposit_master;--2-
	END IF;

	<<end_of_block>>
		updatedTDAAccounts := updatedTDAAccounts + 1;
		RETURN;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		err_num := SQLCODE;
		err_msg := SQLERRM(err_num);
		utl_file.put_line( LOGFILE, 'No data found : '||err_msg );
		rejectedTDAAccounts := rejectedTDAAccounts + 1;
		dbms_output.put_line( ' No data found....' );
	WHEN OTHERS THEN
		err_num := SQLCODE;
		err_msg := SQLERRM(-err_num);
		utl_file.put_line( LOGFILE, 'Error : '||err_msg );
		rejectedTDAAccounts := rejectedTDAAccounts + 1;
		dbms_output.put_line( ' Exception occured ....' );
END;

PROCEDURE close_TDA_file AS
BEGIN
	utl_file.fclose( TDAfile );
EXCEPTION
	WHEN OTHERS THEN
		err_num := SQLCODE;
		err_msg := SQLERRM(-err_num);
		utl_file.put_line( LOGFILE, ' Error while closing TDA file : '||err_msg );
END;

PROCEDURE close_rejTDA_file AS
BEGIN
	utl_file.fclose( rejectedTDA );
EXCEPTION
	WHEN OTHERS THEN
		err_num := SQLCODE;
		err_msg := SQLERRM(-err_num);
		utl_file.put_line( LOGFILE, ' Error while closing rejected TDA file : '||err_msg );
END;

PROCEDURE close_log_file AS
BEGIN
	utl_file.fclose( LOGFILE );
EXCEPTION
	WHEN OTHERS THEN
	dbms_output.put_line( 'Error while closing TDA Log file' );
END;

BEGIN
	/*dbms_output.enable( 1000000 );*/
	dbms_output.DISABLE();

	tdaSummary := ' ';

	BEGIN
		logfilename := 'TDALog.txt';

		rejectedTDAfilename := 'RejectedTDA.txt';

		LOGFILE := utl_file.fopen( TDAfiledir, logfilename, 'w' );
		dbms_output.put_line( 'Log file opened successfully' );

		TDAfile := utl_file.fopen( TDAfiledir, TDAfilename, 'r' );
		dbms_output.put_line( 'TDA file opened successfully' );

		rejectedTDA := utl_file.fopen( TDAfiledir, rejectedTDAfilename, 'w' );
		dbms_output.put_line( 'RejectedTDA file opened successfully' );

		utl_file.put_line( LOGFILE, 'Log file opened' );
		utl_file.put_line( LOGFILE, 'TDA file opened' );
		utl_file.put_line( LOGFILE, 'Rejected TDA file opened' );

	EXCEPTION
		WHEN utl_file.invalid_mode THEN
			err_num := SQLCODE;
			err_msg := SQLERRM(-err_num);
			utl_file.put_line( LOGFILE, 'utl_file.invalid_mode : '||err_msg );
			dbms_output.put_line( 'Invalid mode' );
		WHEN utl_file.invalid_path THEN
			err_num := SQLCODE;
			err_msg := SQLERRM(-err_num);
			utl_file.put_line( LOGFILE, 'utl_file.invalid_path : '||err_msg );
			dbms_output.put_line( 'Invalid path' );
		WHEN utl_file.invalid_operation THEN
			err_num := SQLCODE;
			err_msg := SQLERRM(-err_num);
			utl_file.put_line( LOGFILE, 'utl_file.invalid_operation : '||err_msg );
			dbms_output.put_line( 'Invalid operation' );
		WHEN OTHERS THEN
			err_num := SQLCODE;
			err_msg := SQLERRM(-err_num);
			utl_file.put_line( LOGFILE, 'File open error : '||err_msg );
			dbms_output.put_line( 'File open error' );
			/*return;*/
	END;

	dbms_output.put_line( '************   STARTING TO UPLOAD  ****************' );
	start_time := dbms_utility.get_time;

	/* Read the header and get the date of the file */
	BEGIN
		utl_file.get_line( TDAfile, vnewline );
		
		--added by durai fot EBBS int-
		/*
		century_prefix := SUBSTR( vnewline, 3, 1 );
		IF century_prefix = '0' THEN