📄 tda.sql
字号:
year_prefix := '19';
END IF;
IF century_prefix = '1' THEN
year_prefix := '20';
END IF;*/
vTDAdate := year_prefix||SUBSTR(vnewline,3,2 )||'/'||SUBSTR(vnewline,5,2 )||'/'||SUBSTR(vnewline,7,4 );
utl_file.put_line(LOGFILE,'======EBBS==========='||vTDAdate);
EXCEPTION
WHEN NO_DATA_FOUND THEN
err_num := SQLCODE;
err_msg := SQLERRM(err_num);
utl_file.put_line( LOGFILE, 'TDA header not found : '||err_msg );
RETURN;
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SQLERRM(-err_num);
utl_file.put_line( LOGFILE, 'Error while reading TDA header : '||err_msg );
RETURN;
END;
LOOP
BEGIN
utl_file.get_line( TDAfile, vnewline );
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line( '************ TDA FILE IS EMPTY ****************' );
utl_file.put_line( LOGFILE, 'TDA file is empty' );
EXIT;
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SQLERRM(-err_num);
utl_file.put_line( LOGFILE, 'Error while trying to read TDA line : '||err_msg );
EXIT;
END;
upload_count := upload_count + 1;
IF vnewline IS NULL THEN
dbms_output.put_line( '************ FOUND AN EMPTY LINE ****************' );
utl_file.put_line( rejectedTDA, 'Line no. '||upload_count||' : Line is null' );
rejectedLines := rejectedLines + 1;
GOTO end_of_loop;
END IF;
IF LTRIM(RTRIM( vnewline )) IS NULL THEN
dbms_output.put_line( '************ FOUND AN EMPTY LINE ****************' );
utl_file.put_line( rejectedTDA, 'Line no. '||upload_count||' : Line is null' );
rejectedLines := rejectedLines + 1;
GOTO end_of_loop;
END IF;
IF LTRIM(RTRIM(SUBSTR(vnewline,1,2))) = 'IN' THEN
dbms_output.put_line( '************ REACHED END OF TDA FILE ****************' );
utl_file.put_line( LOGFILE, 'Reached end of TDA file' );
EXIT;
END IF;
IF LENGTH( vnewline ) < 192 THEN
dbms_output.put_line( '************ LINE LENGTH LESS THAN MIN LENGTH ****************' );
utl_file.put_line( rejectedTDA, 'Line no. '||upload_count||' : Line length less than min. length : '||vnewline );
rejectedLines := rejectedLines + 1;
GOTO end_of_loop;
END IF;
/*IF LENGTH( vnewline ) > 193 THEN
dbms_output.put_line( '************ LINE LENGTH GREATER THAN MAX LENGTH ****************' );
utl_file.put_line( rejectedTDA, 'Line no. '||upload_count||' : Line length greater than max. length : '||vnewline );
rejectedLines := rejectedLines + 1;
GOTO end_of_loop;
END IF;*/
--utl_file.put_line( LOGFILE,'--------here---------'||LENGTH( vnewline ));
IF LENGTH( vnewline ) >192 THEN
/*dbms_output.put_line( 'Coming inside' );*/
utl_file.put_line( LOGFILE,'im here');
BEGIN
utl_file.put_line( LOGFILE,'===Acc=='||vaccount_num);
vaccount_num := LTRIM(RTRIM( SUBSTR( vnewline, 1, 11 ) ));
IF vaccount_num IS NULL OR vaccount_num = '00000000000' THEN
dbms_output.put_line( '************ ACCOUNT NUMBER IS INVALID ****************' );
utl_file.put_line( rejectedTDA, 'Line no. '||upload_count||' : Line does not contain account no. : '||vnewline );
rejectedLines := rejectedLines + 1;
GOTO end_of_loop1;
END IF;
--utl_file.put_line( LOGFILE,'===Acc=='||SUBSTR( vnewline, 12, 109 ));
vshort_name := LTRIM(RTRIM( SUBSTR( vnewline, 12, 110 ) ));
IF vshort_name IS NULL THEN
vshort_name := '-';
END IF;
vsub_product_code := LTRIM(RTRIM( SUBSTR( vnewline, 122, 3 ) ));
IF vsub_product_code IS NULL THEN
vsub_product_code := '-';
END IF;
vproduct_identifier := LTRIM(RTRIM( SUBSTR( vnewline, 125, 4 ) ));
IF vproduct_identifier IS NULL THEN
vproduct_identifier := '-';
END IF;
vcurrency := LTRIM(RTRIM( SUBSTR(vnewline, 130, 3 ) ));
IF vcurrency IS NULL THEN
dbms_output.put_line( '************ CURRENCY IS NULL ****************' );
utl_file.put_line( rejectedTDA, 'Line no. '||upload_count||' : Line does not contain currency : '||vnewline );
rejectedLines := rejectedLines + 1;
GOTO end_of_loop1;
END IF;
vcustomer_id := LTRIM(RTRIM( SUBSTR( vnewline, 133, 18 ) ));
IF vcustomer_id IS NULL THEN
/*dbms_output.put_line( '************ CUSTOMER_ID IS NULL ****************' );*/
utl_file.put_line( rejectedTDA, 'Line no. '||upload_count||' : Line does not contain customer id : '||vnewline );
rejectedLines := rejectedLines + 1;
GOTO end_of_loop1;
END IF;
varm_code := LTRIM(RTRIM( SUBSTR(vnewline, 151, 3 ) ));
IF varm_code IS NULL THEN
varm_code := '-';
END IF;
varm_type := LTRIM(RTRIM( SUBSTR(vnewline, 154, 2 ) ));
IF varm_type IS NULL THEN
varm_type := '-';
END IF;
--utl_file.put_line( LOGFILE,TO_NUMBER( LTRIM(RTRIM(SUBSTR(vnewline, 155, 15)))||'.'||LTRIM(RTRIM(SUBSTR(vnewline, 180, 2))), '9G9999999999999D99' ));
vcr_balance := TO_NUMBER(LTRIM(RTRIM( SUBSTR(vnewline, 156, 25))));
vlink_id := LTRIM(RTRIM( SUBSTR(vnewline, 181, 11 ) ));
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,vlink_id||'::::::'||vcr_balance);
IF vlink_id IS NULL OR vlink_id = '00000000000' THEN
vlink_id := '';
END IF;
vwaiver_code := LTRIM(RTRIM( SUBSTR(vnewline, 193, 2 ) ));
IF vwaiver_code IS NULL THEN
vwaiver_code := '-';
END IF;
--utl_file.put_line( LOGFILE,':::::'||vwaiver_code);
vod_class := '-';
vcsi := '-';
vaccount_type := 'term';
vstatus :='-';
validLines := validLines + 1;
--utl_file.put_line( rejectedTDA,'===Acc=='||vcurrency);
/*utl_file.put_line( rejectedTDA,'===vcustomer_id=='||vcustomer_id);
utl_file.put_line( rejectedTDA,'===short_name=='||short_name);
utl_file.put_line( rejectedTDA,'===sub_product_code=='||sub_product_code);
utl_file.put_line( rejectedTDA,'===vcurrency=='||vcurrency);
utl_file.put_line( rejectedTDA,'===od_class=='||od_class);
utl_file.put_line( rejectedTDA,'===varm_type=='||varm_type);
utl_file.put_line( rejectedTDA,'===waiver_code=='||waiver_code);
utl_file.put_line( rejectedTDA,'===varm_code=='||varm_code);
utl_file.put_line( rejectedTDA,'===vlink_id =='||vlink_id );
utl_file.put_line( rejectedTDA,'===vcr_balance =='||vcr_balance ); */
/* ***************** start of print statements *******************
dbms_output.put_line( 'Account number : '||vaccount_num );
dbms_output.put_line( 'Customer_id : '||vcustomer_id );
dbms_output.put_line( 'Short name : '||vshort_name );
dbms_output.put_line( 'Sub-product code : '||vsub_product_code );
dbms_output.put_line( 'Product_identifier : '||vproduct_identifier );
dbms_output.put_line( 'Currency : '||vcurrency );
dbms_output.put_line( 'Od class : '||vod_class );
dbms_output.put_line( 'TDA Date : '||to_date( vTDAdate, 'yyyy/mm/dd') );
dbms_output.put_line( 'Csi : '||vcsi );
dbms_output.put_line( 'Arm-type : '||varm_type );
dbms_output.put_line( 'Arm-code : '||varm_code );
dbms_output.put_line( 'Waiver_code : '||vwaiver_code );
dbms_output.put_line( 'Link id : '||vlink_id );
dbms_output.put_line( 'Account type : '||vaccount_type );
dbms_output.put_line( 'Credit Balance : '||vcr_balance );
--****************** end of print statements ********************* */
BEGIN
TDAproc;
EXCEPTION
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SQLERRM(-err_num);
utl_file.put_line( LOGFILE, 'Error while calling TDAProc : '||err_msg );
END;
/*rec_count := rec_count + 1;
if rec_count = 1000 then
commit;
dbms_output.put_line( rec_count );
rec_count := 0;
end if;*/
/*if mod( upload_count, 20000) = 0 then
dbms_output.put_line( 'Uploaded '||upload_count||' accounts..' );
end if;*/
--test
/*
utl_file.put_line( LOGFILE,'vaccount_num :'||vaccount_num);
utl_file.put_line( LOGFILE,'vcustomer_id :'||vcustomer_id);
utl_file.put_line( LOGFILE,'vshort_name :'||vshort_name);
utl_file.put_line( LOGFILE,'product_id :'||vproduct_identifier);
utl_file.put_line( LOGFILE,'vcurrency :'||vcurrency);
utl_file.put_line( LOGFILE,'vod_class :'||vod_class);
utl_file.put_line( LOGFILE,'vstatus :'||vstatus);
utl_file.put_line( LOGFILE,'vTDAdate :'||vTDAdate);
utl_file.put_line( LOGFILE,'vcsi :'||vcsi);
utl_file.put_line( LOGFILE,'varm_type :'||varm_type);
utl_file.put_line( LOGFILE,'varm_code :'||varm_code);
utl_file.put_line( LOGFILE,'vwaiver_code :'||vwaiver_code);
utl_file.put_line( LOGFILE,'vaccount_type :'||vaccount_type);
utl_file.put_line( LOGFILE,'vsub_product_code :'||vsub_product_code);
utl_file.put_line( LOGFILE,'vlink_id :'||vlink_id);
--test
*/
COMMIT;
<<end_of_loop1>>
NULL;
EXCEPTION
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SQLERRM(-err_num);
utl_file.put_line( LOGFILE, 'Error while trying to parse : '||err_msg );
END;
END IF;
<<end_of_loop>>
NULL;
END LOOP;
COMMIT;
end_time := dbms_utility.get_time;
tdaSummary := 'TDA File Valid lines : '|| validLines ||'<BR>'||
' Invalid lines : '|| rejectedLines ||'<BR>'||
' Uploaded : '|| updatedTDAAccounts ||'<BR>'||
' Rejected : '|| rejectedTDAAccounts ||'<BR><BR>';
close_TDA_file;
dbms_output.put_line( '************ CLOSED TDA FILE ****************' );
utl_file.put_line( LOGFILE, ' ');
utl_file.put_line( LOGFILE, 'UPLOADED '|| updatedTDAAccounts || ' TDA ACCOUNTS');
utl_file.put_line( LOGFILE, ' ');
utl_file.put_line( LOGFILE, 'TIME TAKEN TO UPLOAD TDA FILE : '|| ROUND((end_time-start_time)/(100*60),3) ||' minutes' );
utl_file.put_line( LOGFILE, ' ');
utl_file.put_line( LOGFILE, 'Closed TDA file' );
close_rejTDA_file;
dbms_output.put_line( '************ CLOSED REJECTED TDA FILE ****************' );
utl_file.put_line( LOGFILE, 'Closed Rejeccted TDA file' );
dbms_output.put_line( '************ FINISHED UPLOADING ****************' );
dbms_output.put_line( 'TIME TAKEN TO UPLOAD TDA FILE : '|| ROUND((end_time-start_time)/(100*60),3) ||' minutes' );
utl_file.put_line( LOGFILE, 'TDA Upload Complete' );
utl_file.put_line( LOGFILE, '************************************' );
/*utl_file.put_line( logfile, 'Closing log file' );*/
close_log_file();
END;
/
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -