UNIX for Dummies Questions & Answers

#!/bin/ksh -x

SOURCE_NAME="$1"

if [ $SOURCE_NAME = "ss" ] 
then
# recursive call for all us_stars sub sources
        $PCR_HOME/bin/update_load_status_dly.sh ss
fi

LOAD_TABLE_NAME=""
WHERE_CLAUSE="where TRADE_DATE= "

case $SOURCE_NAME in
aa) LOAD_TABLE_NAME="aa_TRADE_DLY";;

*)     exit 0;;
esac


sqlplus -s $user/$PASSWORD$ORACLE_SID <<END

       col cd new_value wsd noprint
       col md new_value mtd noprint

DECLARE

BEGIN
       select to_char(max(week_start_date),'dd-mon-yyyy') cd
       from temp
       where run_type='DAILY'
       and loading_stage=0;

       select to_char(max(week_start_date),'dd-mon-yyyy') md
       from temp
       where run_type='DAILY'
       and loading_stage=0;

       if (pcrstage.is_str_source ($SOURCE_NAME))
       then
       update table1 s
       set record_count = record_count + (select count(*)
                     from $LOAD_TABLE_NAME  $WHERE_CLAUSE to_date       ('&mtd','dd-mon-yyyy'))
       where run_type='DAILY'
       and loading_stage=0
       and source='$SOURCE_NAME'
       and week_start_date=to_date('&wsd','dd-mon-yyyy');
       
        else
       update temp1 s
       set record_count = (select count(*)
                     from $LOAD_TABLE_NAME  $WHERE_CLAUSE to_date('&mtd','dd-mon-yyyy'))
       where run_type='DAILY'
       and loading_stage=0
       and source='$SOURCE_NAME'
       and week_start_date=to_date('&wsd','dd-mon-yyyy');
       end if;

       commit;
END;
       
END
       
exit;