function_out()
{
function=$1
location_id=$2
sales=$3
echo $function
sqlplus -S xxxx/yyyy@xxxxdbt <<EOF
        SET head off
        SET serveroutput on
        spool chkbits.txt
        select PUT_UNIX.$function('$location_id',$sales) from dual;
        spool off
exit
EOF
}
sqlplus -S xxxx/yyyy@xxxxdbt <<EOF
set echo off
set heading off
set feedback off
set pagesize 0
spool queryout.txt
SELECT PROCESS_CODE FROM scheduled_process_queue where PROCESS_CODE like '%INFO.SDCNT%';
spool off
exit
EOF
while IFS='[, ]' read job location_id sales; do
count=`sqlplus -S xxxx/yyyy@xxxxdbt <<EOF
set echo off
set heading off
set feedback off
Set pagesize 0
SET head off
spool output.txt
select count(*)  from total_count where location_id='$location_id' and sales=$sales;
spool off
exit
EOF`
count=`cat output.txt`
if [[ $count > 0 ]];
then
sqlplus -S xxxx/yyyy@xxxxdbt <<EOF
delete from total_count where location_id='$location_id' and sales=$sales;
commit;
exit
EOF
echo "count is not  gretaehr than zero"
else
echo "count is zero"
fi
case $location_id in
DRUG|GROC)
function_out PUT_GROC_DRUG_COUNT $location_id $sales;;
CLUB|MASS|DECA|LIQ|PETS)
function_out PUT_OTHER_location_COUNT $location_id $sales;;
CONV)
function_out PUT_OTHER_location_COUNTi $location_id $sales;;
*)echo "$location_id location_id is not in the database";;
esac
out=`cat chkbits.txt`
if [ $out = 0 ]
then
echo "Check TRC & lenap Function "
sqlplus -S xxxx/yyyy@xxxxdbt <<EOF
SET head off
SET serveroutput on
spool bits1.txt
select PUT_UNIX.PUT_UNIX.PUT_TRC_COUNT('$location_id',$sales) from dual;
spool off
spool bits2.txt
select PUT_UNIX.PUT_UNIX.PUT_lenap_COUNT('$location_id',$sales) from dual;
spool off
exit
EOF
else
echo "failire"
fi
if [[ $out = 0 && $bits1 = 0 && $bits2 = 0 ]]
then
mailx -s "Alert Email for KMDS JOBS Suceess for location_id $location_id $sales" arunkumar@gmail.com  < /dev/null
else
mailx -s "Alert Email for KMDS JOBS Failed for location_id $location_id $sales"  arunkumar@gmail.com < /dev/null
fi
rm output.txt
rm bits1.txt
rm bits2.txt
rm chkbits.txt
done < out.txt
rm out.txt