Emergency UNIX and Linux Support

# *************************************************************
# * ModifyUsgAuditGroupTable.ksh.ksh - this script is used to update
#* the audit_group_table for the audit_group_id's which
#* are failed with internal error qty less than 1 error.
# 
# *************************************************************
# * Loading DB connection credentials
# *************************************************************
db_user=$DB_USER_NAME
db_pwd=$DB_PASSWORD
db_sid=$TWO_TASK



if [ $# -ne 1 ]
then
echo "\tUsage: ModifyUsgAuditGroupTable.ksh <yymmdd>"
exit 1
fi

TimeStamp=`date +%Y%m%d%H%M%S`
DirectoryName="AuditGroupLogs_$TimeStamp"

echo "********Started processing at $TimeStamp**********"

if [ !  -d  $DirectoryName ]
then
mkdir $DirectoryName
echo " log file path is : ./$DirectoryName/AuditUpdate_$TimeStamp.LOG";
else
echo " log file path is : ./$DirectoryName/AuditUpdate_$TimeStamp.LOG ";
fi

# *************************************************************
# *Extracting Failed acct numbers from BC log file and 
# * storing the acct nbr's in a file MUFailedAccts_$TimeStamp
# * in directory $DirectoryName
# *************************************************************

grep -i 'RTUServer Error:Count of Internal Error'  $1* |sed 's/.*-\(.*\),.*,.*,.*,.*/\1/g' > ./List.dat
sort -u ./List.dat -o ./List.dat

while read audit_group_id
do
echo "select  audit_group_nbr,intrl_drop_qty,intrl_error_qty  from usage_audit_group where  audit_group_nbr = '$audit_group_id' ;" >> ./$DirectoryName/SQLQuery_$TimeStamp.sql
done < ./List.dat

ret=`sqlplus -s  /nolog << EOF
connect $db_user/$db_pwd@$db_sid;
SPOOL  ./$DirectoryName/AuditGroupTableTableData_$TimeStamp.txt;
set pagesize 0
set feedback off 
set verify off 
set heading off 
set echo off 
set linesize 300 
@./$DirectoryName/SQLQuery_$TimeStamp.sql
SPOOL OFF;
EXIT;
EOF`

nawk '{{print $1,$2,$3}}'  ./$DirectoryName/AuditGroupTableTableData_$TimeStamp.txt | while read a b c
do
echo $a
echo $b 
echo $c
sqlplus $db_user/$db_pwd@$db_sid  $a $b $c <<EOF
SET HEADING OFF
SET SERVEROUTPUT ON
SET FEEDBACK OFF
DECLARE
CNT  NUMBER(5);
DROPCNT NUMBER(5);
ERRORCNT NUMBER(5);
BEGIN
SELECT COUNT(*) INTO CNT FROM USAGE_INTRNL_ERROR WHERE AUDIT_GROUP_ID='&1';

IF ( CNT != &3) THEN
  SELECT INTRL_DROP_QTY,INTRL_ERROR_QTY INTO DROPCNT,ERRORCNT 
     FROM USAGE_AUDIT_GROUP WHERE AUDIT_GROUP_NBR='&1'; 

     DROPCNT :=DROPCNT-CNT;

     UPDATE USAGE_AUDIT_GROUP 
      SET INTRL_DROP_QTY='DROPCNT' ,INTRL_ERROR_QTY='CNT'  
      WHERE  AUDIT_GROUP_NBR='&1' ;
COMMIT;

END IF;
END;
/
EXIT;
EOF
done

sqlplus $db_user/$db_pwd@$db_sid  $a $b $c <<EOF