ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 最近做的一个AR Lockbox interface的主程序(shell)

最近做的一个AR Lockbox interface的主程序(shell)

原创 Linux操作系统 作者:asiaidea 时间:2011-05-16 15:17:52 0 删除 编辑

总体流程:

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 <$C_INBND_DIR/log/$email_filename
    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 <$C_INBND_DIR/log/$email_filename
    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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-11-04

  • 博文量
    140
  • 访问量
    479419