总体流程:
1. 从某一目录下获取data file,并进行分割处理(按Transmission Header)
2.从系统中取出相应信息，更正data file里的部分信息
3.调用process lockboxes 导入data file 至interface
4.调用process lockboxes 生成receipt.
5.如有错误，发邮件给用户
#!/bin/ksh
# SYSTEM : R12
# Script : c_ar_lbx_can
# Author : Tony Liu
# Date : 18-Apr-2011
#########################################################
# Initialize directory paths into variables
#########################################################
LOG_DIR=$C_APPS_TOP/log
DATA_DIR=$C_INBND_STG #need to confirm
BIN_DIR=$C_APPS_TOP/bin
SQL_DIR=$C_APPS_TOP/sql
TMP_DIR=$DATA_DIR
ARCH_DIR=$C_INBND_ARC
OUT_DIR=$C_APPS_TOP/out
#Added by Tony Liu on 20-Apr-2011
ARCHDT=dat.`date +%Y%m%d\%H\%M`
#########################################################
# Get ORG_ID and USERNAME #
#########################################################
ORG_ID=`echo $*|awk '{print $9}'`
ORG_ID=`echo $ORG_ID|sed 's/"//g'`
for field in `echo $1`
do
param_name=`echo $field | cut -d'=' -f1`
param_value=`echo $field | cut -d'=' -f2`
if [ "$param_name" = "FCP_USERNAME" ]
then
FCP_USERNAME=`echo $param_value| sed 's/"//g' `
fi
done
################################################################
# Get other input parameters --Added by Tony Liu on 20-Apr-2011#
################################################################
PROC_FLAG=`echo $1 | cut -d" " -f10 | cut -d"\"" -f2`
echo "Re-processing Flag: " $PROC_FLAG
PROC_FILE=`echo $1 | cut -d" " -f11 | cut -d"\"" -f2`
echo "File name: " $PROC_FILE
REQ_ID=`echo $1 | cut -d" " -f2 | cut -d'=' -f2`
echo "Request ID: " $REQ_ID
#########################################################
# Get mail group --Added by Tony Liu on 11-May-2011 #
#########################################################
#get it mail group
IT_MAILGROUP=`sqlplus -s /nolog < connect $FCP_LOGIN
SET pagesize 100;
SET linesize 85;
SET heading off;
SET feedback off;
SET echo off;
SELECT attribute1
FROM fnd_lookup_values
WHERE lookup_type = 'C_FND_ERROR_EMAIL_ADDR'
AND attribute_category = 'C_FND_ERROR_EMAIL_ADDR'
AND lookup_code = 'CANADA AUTO LOCKBOX-IT';
EXIT;
ENDSQL`
#IT_MAILGROUP=`echo $IT_MAILGROUP|cut -d " " -f2|sed 's/^[ \t]*//g'`
echo "Current IT Mail Group: "$IT_MAILGROUP
#get biz mail group
BIZ_MAILGROUP=`sqlplus -s /nolog < connect $FCP_LOGIN
SET pagesize 100;
SET linesize 85;
SET heading off;
SET feedback off;
SET echo off;
SELECT attribute1
FROM fnd_lookup_values
WHERE lookup_type = 'C_FND_ERROR_EMAIL_ADDR'
AND attribute_category = 'C_FND_ERROR_EMAIL_ADDR'
AND lookup_code = 'CANADA AUTO LOCKBOX-BIZ';
EXIT;
ENDSQL`
#BIZ_MAILGROUP=`echo $BIZ_MAILGROUP|cut -d " " -f2|sed 's/^[ \t]*//g'`
echo "Current Business Mail Group: "$BIZ_MAILGROUP
#########################################################
# Error handler --Added by Tony Liu on 20-Apr-2011 #
#########################################################
log_error()
{
sqlplus -s /nolog <connect $FCP_LOGIN
SET pagesize 100;
SET linesize 85;
SET heading off;
SET feedback off;
DECLARE
vl_error_msg varchar2(32767);
vl_conc_name varchar2(100);
vl_request_id number;
vl_user_id number;
vl_resp_id number;
vl_resp_name varchar2(100);
vl_user_name varchar2(8);
vl_resp_appl_id number;
BEGIN
vl_request_id := $REQ_ID;
--get conc name
SELECT fcp.user_concurrent_program_name,
frt.responsibility_id,
frt.responsibility_name,
fu.user_name,
fu.user_id,
fcr.responsibility_application_id
INTO vl_conc_name,
vl_resp_id,
vl_resp_name,
vl_user_name,
vl_user_id,
vl_resp_appl_id
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_responsibility_tl frt,
fnd_user fu
WHERE fcr.request_id = vl_request_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.responsibility_id = frt.responsibility_id
AND fcr.requested_by = fu.user_id
AND frt.language = 'US'
AND fcp.language = 'US';
--init env
fnd_global.apps_initialize(user_id => vl_user_id,
resp_id => vl_resp_id,
resp_appl_id => vl_resp_appl_id);
--format error message
vl_error_msg := replace('$ERROR_MSG','@',chr(13));
vl_error_msg := 'Concurrent program: '||vl_conc_name||chr(13)||
'Request ID: '||vl_request_id||chr(13)||
'Responsibility: '||vl_resp_name||chr(13)||
'Requested by: '||vl_user_name||chr(13)||
'Execution Datatime: '||sysdate||chr(13)||
'Error message is: '||chr(13)||
vl_error_msg;
--log error
c_fnd_error_handler.log_error
(pi_pkg_proc_name => 'c_ar_lbx_can',
pi_process_name => 'CANADA AUTO LOCKBOX-$1',
pi_error_type => c_fnd_error_handler.pvg_validation_error,
pi_msg => vl_error_msg
);
END;
/
ENDSQL
}
#########################################################
# Send mail with body --Added by Tony Liu on 11-May-2011#
# $1: mail subject #
# $2: mail body #
# $3: Mail class(IT/Business) #
#########################################################
send_mail_with_body()
{
body_text="Program: Canada auto lockbox inbound program.\n"
body_text=${body_text}"Request ID: "${REQ_ID}".\n"
body_text=${body_text}"Below are error message:\n"
body_text=${body_text}$2
if test "$3" = "IT"
then
echo "Send notification mail to IT group"
echo -e $body_text | mailx -s "$1" $IT_MAILGROUP
else
echo "Send notification mail to Business group"
echo -e $body_text | mailx -s "$1" $BIZ_MAILGROUP
fi
}
###############################################################
# Send mail with attachment --Added by Tony Liu on 11-May-2011#
# $1: request id #
# $2: LOG/OUTPUT #
# $3: Mail class(IT/Business) #
# $4: Mail subject #
# $5: Mail body #
###############################################################
send_mail_with_attachment()
{
if test "$2" = "LOG"
then
email_filename="l"$1".req"
cp $APPLCSF/log/$email_filename $C_INBND_DIR/log/$email_filename
else
email_filename="o"$1".out"
cp $APPLCSF/out/$email_filename $C_INBND_DIR/log/$email_filename
fi
if [ $? -gt 0 ]
then
echo "Log/Output file copy unsuccessfully"
else
if test "$3" ="IT"
then
sleep 10
#echo -e $5 | mailx -s "$4" $IT_MAILGROUP uuencode $C_INBND_DIR/log/$email_filename $email_filename | mailx -s "$4" $IT_MAILGROUP
echo "Log/Output file send out successfully"
else
sleep 10
#echo -e $5 | mailx -s "$4" $BIZ_MAILGROUP uuencode $C_INBND_DIR/log/$email_filename $email_filename | mailx -s "$4" $BIZ_MAILGROUP
echo "Log/Output file send out successfully"
fi
fi
}
#####################################################################
# Submit standard lockbox program --Added by Tony Liu on 10-May-2011#
#####################################################################
submit_request()
{
sqlplus -s /nolog <connect $FCP_LOGIN
SET pagesize 100;
SET linesize 85;
SET heading off;
SET feedback on;
SET serverout on;
DECLARE
vl_import_request_id number;
vl_import_result boolean;
vl_import_phase varchar2(100);
vl_import_status varchar2(100);
vl_import_dev_phase varchar2(100);
vl_import_dev_status varchar2(100);
vl_import_message varchar2(100);
vl_post_request_id number;
vl_post_result boolean;
vl_post_phase varchar2(100);
vl_post_status varchar2(100);
vl_post_dev_phase varchar2(100);
vl_post_dev_status varchar2(100);
vl_post_message varchar2(100);
vl_transmission_name varchar2(100);
vl_format_id number;
vl_lockbox_id number;
vl_transmission_id number;
vl_transmission_request_id number;
vl_user_id number;
vl_resp_id number;
vl_appl_id number;
vl_flag number :=0;
vl_mail_request_id number;
vl_mail_result boolean;
vl_mail_phase varchar2(100);
vl_mail_status varchar2(100);
vl_mail_dev_phase varchar2(100);
vl_mail_dev_status varchar2(100);
vl_mail_message varchar2(100);
BEGIN
--init
BEGIN
SELECT requested_by user_id,
responsibility_id resp_id,
responsibility_application_id appl_id
INTO vl_user_id,
vl_resp_id,
vl_appl_id
FROM fnd_concurrent_requests
WHERE request_id = $1;
fnd_global.apps_initialize(vl_user_id,vl_resp_id,vl_appl_id);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Initialization is failed');
vl_flag := 1;
END;
--Get transmission name
BEGIN
SELECT to_char(sysdate, 'YYYYMMDDHH24MISS')
INTO vl_transmission_name
FROM dual;
dbms_output.put_line('Transmission Name: '||vl_transmission_name);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Can not get transmission name.');
vl_flag := 1;
END;
--get format id
BEGIN
SELECT transmission_format_id
INTO vl_format_id
FROM ar_transmission_formats
WHERE upper(format_name) like '%CANADA RECEIPTS%'
AND rownum = 1;
dbms_output.put_line('Transmission Format ID: '||vl_format_id);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Can not get transmission format ID.');
vl_flag := 1;
END;
--get lockbox id
BEGIN
SELECT ara.lockbox_id
INTO vl_lockbox_id
FROM ar_lockboxes_all ara,
c_apps.c_ar_lbx_can_number_tmp mclt
WHERE ltrim(trim(ara.lockbox_number),'0') = ltrim(trim(mclt.lockbox_number),'0')
AND status = 'A'
AND rownum = 1;
dbms_output.put_line('Lockbox ID: '||vl_lockbox_id);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Can not get Lockbox ID.');
vl_flag := 1;
END;
if vl_flag = 0 then
dbms_output.put_line('Submit Process lockboxes - Import and Validation:');
vl_import_request_id := fnd_request.submit_request('AR', -- application short name
'ARLPLB', -- program short name
NULL, -- program name
NULL, -- start date
FALSE, -- sub-request
'Y', -- NEW_TRANSMISSION -- argument1
null, -- LB_TRANSMISSION_ID
null, -- ORIG_REQUEST_ID
vl_transmission_name, -- TRANSMISSION_NAME
'Y', -- SUBMIT_IMPORT
'/d51/appldev/dvoa082/share/Data/inbound/stage/moto_lbx_can_post.dat', -- DATA_FILE
'c_ar_lbx_can_load', -- CTRL_FILE
vl_format_id, -- TRANSMISSION_FORMAT_ID
'Y', -- SUBMIT_VALIDATION
'N', -- PAY_UNRELATED_INVOICES
vl_lockbox_id, -- LOCKBOX_ID
null, -- GL_DATE
'A', -- REPORT_FORMAT
'N', -- COMPLETE_BATCHES_ONLY
'N', -- SUBMIT_POSTBATCH
'N', -- ALTERNATE_NAME_SEARCH
NULL, -- IGNORE_INVALID_TXN_NUM
NULL, -- USSGL_TRANSACTION_CODE
$2, -- ORG_ID
'L',--SOURCE TYPE
NULL);
COMMIT;
If (vl_import_request_id = 0) then
dbms_output.put_line('Error:Can not Submit Process Lockboxes Program(import and validation)');
else
dbms_output.put_line('Submit successfully: '||vl_import_request_id);
vl_import_result:=fnd_concurrent.wait_for_request(vl_import_request_id,
1,
0,
vl_import_phase,
vl_import_status,
vl_import_dev_phase,
vl_import_dev_status,
vl_import_message);
end if;
If not vl_import_result then
dbms_output.put_line('Error:No Status returned for the request Id: '||vl_import_request_id);
else
if upper(vl_import_status)!='NORMAL' or upper(vl_import_dev_status) !='NORMAL' THEN
dbms_output.put_line('Error: Request ('||vl_import_request_id||') is failed,please check it');
ELSE
--get transmission information for second submit
BEGIN
SELECT t.transmission_id
, t.transmission_request_id
INTO vl_transmission_id, vl_transmission_request_id
FROM ar_transmissions_all t
WHERE t.transmission_name = vl_transmission_name;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error:Can not get transmission information');
vl_flag := 1;
END;
if vl_flag = 0 then
dbms_output.put_line('Submit Process lockboxes - Post Quick Cash:');
vl_post_request_id := fnd_request.submit_request('AR', -- application short name
'ARLPLB', -- program short name
NULL, -- program name
NULL, -- start date
FALSE, -- sub-request
'N', -- NEW_TRANSMISSION -- argument1
vl_transmission_id, -- LB_TRANSMISSION_ID
vl_transmission_request_id, -- ORIG_REQUEST_ID
vl_transmission_name, -- TRANSMISSION_NAME
'N', -- SUBMIT_IMPORT
NULL, -- DATA_FILE
NULL, -- CNTRL_FILE
vl_format_id, -- TRANSMISSION_FORMAT_ID
'N', -- SUBMIT_VALIDATION
'N', -- PAY_UNRELATED_INVOICES
vl_lockbox_id, -- LOCKBOX_ID
NULL, -- GL_DATE
'A', -- REPORT_FORMAT
'N', -- COMPLETE_BATCHES_ONLY
'Y', -- SUBMIT_POSTBATCH
'N', -- ALTERNATE_NAME_SEARCH
NULL, -- IGNORE_INVALID_TXN_NUM
NULL, -- USSGL_TRANSACTION_CODE
$2, -- ORG_ID
'L',--SOURCE TYPE
NULL
);
COMMIT;
If (vl_post_request_id = 0) then
dbms_output.put_line('Error:Can not Submit Process Lockboxes Program(post quick cash)');
else
dbms_output.put_line('Submit successfully: '||vl_post_request_id);
vl_post_result:=fnd_concurrent.wait_for_request(vl_post_request_id,
1,
0,
vl_post_phase,
vl_post_status,
vl_post_dev_phase,
vl_post_dev_status,
vl_post_message);
If not vl_post_result then
dbms_output.put_line('Error:No Status returned for the request Id: '||vl_post_request_id);
else
if upper(vl_post_status)!='NORMAL' or upper(vl_post_dev_status) !='NORMAL' THEN
dbms_output.put_line('Error: Request ('||vl_post_request_id||') is failed,please check it');
end if;
end if;
--Send mail
vl_mail_request_id := fnd_request.submit_request('C_APPS', -- application short name
'C_AR_GBL_LOCKBOX_MAIL', -- program short name
NULL, -- program name
NULL, -- start date
FALSE, -- sub-request
vl_post_request_id,
'OUTPUT',
'Notification mail from Canada Auto lockbox(Quick Cash)-Request ID is: '||vl_post_request_id,
'rcd463@motorola.com'
);
COMMIT;
end if;
end if;
end if;
end if;
end if;
END;
/
ENDSQL
}
######################################################################
# Move files from Inbound folder to Working folder #
# ######################################################################
ERROR_MSG=" "
if test "$PROC_FLAG" = "N"
then
RELFTP_DIR=$C_INBND_DIR
#check for existence data file
if [ -f $RELFTP_DIR/moto_trns823_can* ]
then
echo ""
for i in `ls -rt $RELFTP_DIR/moto_trns823_can* 2>/dev/null `
do
echo "Source File:" `basename $i`
filename=`basename $i`
cp $i $DATA_DIR/${filename}.dat
# archive source file
cp $i $ARCH_DIR/${filename}.$ARCHDT
echo "Data Filename: "${filename}.dat
echo "Archive Filename: "${filename}.$ARCHDT
#check for an empty file
wcount=`cat $ARCH_DIR/${filename}.$ARCHDT | wc -c`
if [ $wcount = 0 ]
then
IF_EMPTY="Y"
echo "processed file is empty."
#delete file
rm $DATA_DIR/${filename}.dat
#log error to CEHU
ERROR_TEMP_MSG="Processed file is empty: ${filename}.\n"
ERROR_MSG=$ERROR_MSG$ERROR_TEMP_MSG
fi
done
if [ "$IF_EMPTY" = "Y" ]
then
MAIL_CLASS="BIZ"
#log_error $MAIL_CLASS
send_mail_with_body "Notification Mail from Canada Auto Lockbox(Request ID: ${REQ_ID})" "$ERROR_MSG" "$MAIL_CLASS"
fi
else
echo "No file exist in Inbound folder."
#log error to CEHU
ERROR_MSG="No file exist in Inbound folder."
MAIL_CLASS="BIZ"
#log_error $MAIL_CLASS
send_mail_with_body "Notification Mail from Canada Auto Lockbox(Request ID: ${REQ_ID})" "$ERROR_MSG" "$MAIL_CLASS"
exit 1
fi
else
#check for existence data file
if [ -f $ARCH_DIR/"$PROC_FILE" ]
then
# cat the file under archive folder to working folder
cat $ARCH_DIR/$PROC_FILE > $DATA_DIR/$PROC_FILE
# Copy the source files to archive directory for archive
cp $ARCH_DIR/$PROC_FILE $ARCH_DIR/$PROC_FILE.$ARCHDT
if [ $? -gt 0 ]
then
echo "Archive file copy unsuccessfully"
fi
# check for an empty file
wcount=`cat $ARCH_DIR/$PROC_FILE.$ARCHDT | wc -c`
if [ $wcount = 0 ]
then
echo "Re-processed file is empty."
#Delete file
rm $DATA_DIR/$PROC_FILE
#log error to CEHU
ERROR_MSG="Re-processed file is empty: "$PROC_FILE
MAIL_CLASS="BIZ"
#log_error $MAIL_CLASS
send_mail_with_body "Notification Mail from Canada Auto Lockbox(Request ID: ${REQ_ID})" "$ERROR_MSG" "$MAIL_CLASS"
exit 1
fi
echo 'Filename for re-process: '$PROC_FILE
echo "Archive Filename: " $PROC_FILE.$ARCHDT
echo 'Re-process Flag: '$PROC_FLAG
else
echo 'Filename for re-process: '$PROC_FILE
echo 'Re-process Flag: '$PROC_FLAG
echo "$PROC_FILE does not exist."
#log error to CEHU
ERROR_MSG="Re-processed file does not exist: "$PROC_FILE
MAIL_CLASS="BIZ"
#log_error $MAIL_CLASS
send_mail_with_body "Notification Mail from Canada Auto Lockbox(Request ID: ${REQ_ID})" "$ERROR_MSG" "$MAIL_CLASS"
exit 1
fi
fi
# Split Process added by Anand.B Proj No 37408
# Begin Split Process
#########################################################
# Split Process #
#########################################################
SPLIT_APPL_FILE=moto_trns823_can; export APPL_FILE
SPLIT_TMP_FILE=moto_trns823_can_tmp
SPLIT_PROCESS_FILE=moto_split_trns823_can
SPLIT_DATA_FILE=moto_lbx_can
SPLIT_STAMP=`date +\%m\%d\%H\%M`
SPLIT_TIME_STAMP=`date +\%Y\%m\%d\%H\%M\%S`
SPLIT_SEQ_FILE=$DATA_DIR/moto_lbx_seqnumber_can.txt
SPLIT_LOG_FILE=moto_lbx_can_split_log.$SPLIT_TIME_STAMP
nextseq()
# Get the seq number and increment by one.
{
NEXTNO=`tail -1 ${SPLIT_SEQ_FILE}`
templen1=`echo "$NEXTNO"| wc -c`
lenvar1=`expr ${templen1} - 1`
if [ $lenvar1 -eq 0 ]
then
NEXTNO=0
else
NEXTNO=`expr ${NEXTNO} + 1`
fi
templen=`echo "$NEXTNO"| wc -c`
lenvar=`expr ${templen} - 1`
case $lenvar in
1) NEXTNO="000"$NEXTNO;;
2) NEXTNO="00"$NEXTNO;;
3) NEXTNO="0"$NEXTNO;;
5) cat /dev/null > $SPLIT_SEQ_FILE
NEXTNO="0001"
;;
esac
echo ${NEXTNO} >> $SPLIT_SEQ_FILE
}
# main ()
echo Start time SPLT823 - `date` >> $LOG_DIR/$SPLIT_LOG_FILE
echo Start time SPLT823 - `date`
rm -f $TMP_DIR/$SPLIT_TMP_FILE
echo "rm-f split_tmp_file executed"
for i in `ls -rt $DATA_DIR/$SPLIT_APPL_FILE* 2>/dev/null`
do
if test -s $i
then
echo "cp $i to Archive directory done" >> $LOG_DIR/$SPLIT_LOG_FILE
echo "cp $i to Archive directory done"
cp $i $ARCH_DIR/`basename $i`.arc
if [ $? -ne 0 ]
then
echo "Error copying $i to Archive directory " >> $LOG_DIR/$SPLIT_LOG_FILE
echo "Error copying $i to Archive directory "
fi
# create a single file by concatenating the input files
# produced by Harbinger.
cat $i >> $TMP_DIR/$SPLIT_TMP_FILE
rm $i
fi
done
ls -l $TMP_DIR/$SPLIT_TMP_FILE >> $LOG_DIR/$SPLIT_LOG_FILE
csplit -s -k -f $TMP_DIR/$SPLIT_PROCESS_FILE. $TMP_DIR/$SPLIT_TMP_FILE '/^1 /' '{150}'
ls -l $TMP_DIR/$SPLIT_PROCESS_FILE.* >> $LOG_DIR/$SPLIT_LOG_FILE
for i in `ls $TMP_DIR/$SPLIT_PROCESS_FILE.*`
do
if test -s $i
then
nextseq
cp $i $TMP_DIR/$SPLIT_DATA_FILE.$NEXTNO.dat
#Removed by Tony: TMP_DIR and DATA_DIR are same folder
#cp $TMP_DIR/$SPLIT_DATA_FILE.$NEXTNO.dat $DATA_DIR
if [ $? -ne 0 ]
then
echo "Error occurred transferring moto_lbx_can$SEQNO.dat"
echo "Error occurred transferring moto_lbx_can$SEQNO.dat" \
>> $LOG_DIR/$SPLIT_LOG_FILE
mv $i $LOG_DIR/$SPLIT_DATA_FILE.$NEXTNO.$SPLIT_STAMP
echo "Unable to move files to $APPLCSF/arc/inbound/lockbox directory"
else
rm -f $i
fi
#Removed by Tony: Because TMP_DIR is data folder
#rm -f $TMP_DIR/$SPLIT_DATA_FILE.$NEXTNO.dat
fi
done
rm -f $DATA_DIR/$SPLIT_APPL_FILE
rm -f $TMP_DIR/$SPLIT_TMP_FILE
touch $DATA_DIR/$SPLIT_APPL_FILE
touch $TMP_DIR/$SPLIT_TMP_FILE
echo End time SPLT823 - `date` >> $LOG_DIR/$SPLIT_LOG_FILE
echo End time SPLT823 - `date`
# End of Split Process
#########################################################
# Retrieve the Data Files #
#########################################################
echo "Retrieve the Data Files"
CNT=0
DATA_FILE_PATTERN=moto_lbx_can.*
echo "data_file pattern is "$DATA_FILE_PATTERN
FILELIST=" "
#########################################################
# Get Main Request ID #
#########################################################
#Modified by Tony Liu on 22-Apr-2011 BEGIN
#Directly use input parameters:REQ_ID
V_REQUEST_ID=$REQ_ID
echo "Main Request ID "$V_REQUEST_ID
#Modified by Tony Liu on 22-Apr-2011 END
#########################################################
# For Each Data File Archive It, Perform. Lockbox #
# Process, and Remove File. #
#########################################################
echo "Retrieving the Data Files" >> $LOG_DIR/$LOG_FILE
cd $DATA_DIR
for i in `ls $DATA_FILE_PATTERN`
do
echo "\nFile in process is :" $i >> $LOG_DIR/$LOG_FILE
CNT=`expr $CNT + 1`
echo "Count is: " $CNT >> $LOG_DIR/$LOG_FILE
FILELIST="$FILELIST $i"
echo "File is: "$FILELIST >> $LOG_DIR/$LOG_FILE
#cd $DATA_DIR
#########################################################
# Archive Data File #
#########################################################
echo "Archiving Data Files "$i >> $LOG_DIR/$LOG_FILE
PRE_FILE=moto_lbx_can_pre.dat
echo "pre_file is :" $PRE_FILE
PRE_ARCH_FILE="$i."`date '+%Y%m%d%T'`".arc"
cp $i $TMP_DIR/$PRE_FILE
cp $i $ARCH_DIR/$PRE_ARCH_FILE
#cd $TMP_DIR
chmod 777 $TMP_DIR/$PRE_FILE
#TMP_VAR=temporary_file.txt
echo "Archive Pre-Data File complete"
########################################################
# Insert Transmission Total Amount #
########################################################
echo "Insert Transmission Total"
CONTROL_FILE_PRE=c_ar_lbx_can_tmp.ctl
LOGFILE_PRE=moto_lbx_can_pre.log
DISCARD_FILE_PRE=moto_lbx_can_pre.dsc
BAD_FILE_PRE=moto_lbx_can_pre.bad
cat > $SQL_DIR/c_ar_lbx_can_tmp_clean.sql <DELETE FROM c_apps.c_ar_lbx_can_tmp;
COMMIT;
EXIT;
END5
sqlplus -s /nolog <connect $FCP_LOGIN
@$SQL_DIR/c_ar_lbx_can_tmp_clean.sql
EXIT
ENDSQL
rm $SQL_DIR/c_ar_lbx_can_tmp_clean.sql
cd $SQL_DIR
#################################
#Load data file to staging table#
#################################
echo "Load data to staging table:c_ar_lbx_can_tmp "
sqlldr \
CONTROL=$BIN_DIR/$CONTROL_FILE_PRE, \
DATA=$TMP_DIR/$PRE_FILE, \
LOG=$LOG_DIR/$LOGFILE_PRE, \
DISCARD=$LOG_DIR/$DISCARD_FILE_PRE, \
BAD=$LOG_DIR/$BAD_FILE_PRE <$FCP_LOGIN
END
#Added by Tony Liu on 22-Apr-2011, send mail to IT mailgroup when sql loader failed. BEGIN
if [ "$?" != "0" ]
then
echo "SQL LOAD has error, please check the file and re-run the program"
ERROR_MSG="SQL Loader has error when load pre-file to staging table:C_AR_LBX_CAN_TMP,Plase check"
MAIL_CLASS="IT"
log_error $MAIL_CLASS
#exit 1
fi
#Added by Tony Liu on 22-Apr-2011, send mail to IT mailgroup when sql loader failed. END
echo "Loaded c_ar_lbx_can_tmp table " >> $LOG_DIR/$LOG_FILE
echo "Spool and revise data in staging table"
sqlplus -s /nolog <connect $FCP_LOGIN
@$SQL_DIR/c_ar_lbx_call_can_convert.sql $ORG_ID
EXIT
ENDSQL
POST_FILE=moto_lbx_can_post.dat
cp $TMP_DIR/can_trnx_tot.txt $DATA_DIR/$POST_FILE
chmod 777 $DATA_DIR/$POST_FILE
echo "Post data file generated successfully"
#########################################################
# Lockbox Value #
#########################################################
echo "Retrieve Lockbox Number "
echo "Retrieving Lockbox Number " >> $LOG_DIR/$LOG_FILE
BFILE="$C_APPS_TOP/log/moto_lbx_can_bad."`date '+%Y%m%d%T'`
CONTROL_FILE_NUM=c_ar_lbx_can_number.ctl
LOGFILE_NUM=moto_lbx_can_num.log
DISCARD_FILE_NUM=moto_lbx_can_num.dsc
BAD_FILE_NUM=moto_lbx_can_num.bad
#################################
#Load data file to staging table#
#################################
sqlldr \
CONTROL=$BIN_DIR/c_ar_lbx_can_number.ctl, \
DATA=$TMP_DIR/moto_lbx_can_pre.dat, \
LOG=$LOG_DIR/moto_lbx_can_num.log, \
DISCARD=$LOG_DIR/moto_lbx_can_num.dsc, \
BAD=$LOG_DIR/moto_lbx_can_num.bad <$FCP_LOGIN
END
#Added by Tony Liu on 22-Apr-2011, exit when sql loader failed. BEGIN
if [ "$?" != "0" -a "$?" != "2" ]
then
echo "SQL LOAD has error, please check the file and re-run the program"
ERROR_MSG="SQL Loader has error when load pre-file to staging table:C_AR_LBX_CAN_NUMBER_TMP,Plase check"
MAIL_CLASS="IT"
log_error $MAIL_CLASS
#exit 1
fi
#Added by Tony Liu on 22-Apr-2011, exit when sql loader failed. END
echo "Loaded c_ar_lbx_can_number_tmp table " >> $LOG_DIR/$LOG_FILE
#############################################################
# Execute AutoLockbox Process #
#############################################################
echo "Execute Standard Process Lockboxes"
echo "Executing Lockbox Concurrent Request" >> $LOG_DIR/$LOG_FILE
submit_request $REQ_ID $ORG_ID
#############################################################
# Move updated datafile to Archive Directory #
#############################################################
echo "Remove Data Files"
echo "Removing Data File "$i >> $LOG_DIR/$LOG_FILE
TIME_STAMP=`date +\%Y\%m\%d\%H\%M\%S`
cd $DATA_DIR
mv $DATA_DIR/$POST_FILE $ARCH_DIR/$POST_FILE.$TIME_STAMP.arc
if [ -f $i ]
then
rm $i
fi
echo "Lockbox Process Completed"
echo "Lockbox Process Completed" >> $LOG_DIR/$LOG_FILE
echo "Count"$CNT
done
#remove all temp files
rm $DATA_DIR/moto_split_trns823_can*
rm $DATA_DIR/moto_trns823_can*
rm $DATA_DIR/can_trnx_tot.txt
rm $DATA_DIR/moto_lbx_seqnumber_can.txt
rm $DATA_DIR/moto_lbx_can_pre.dat
##############################################################
# if no data files found then #
##############################################################
if [ $CNT -eq 0 ]
then
echo "No files found." >> $LOG_DIR/$LOG_FILE
echo "Log file "$LOG_DIR/$LOG_FILE
echo "No files found"
fi
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10359218/viewspace-695483/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/10359218/viewspace-695483/
扫一扫
229
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
