function query_acode {

        typeset scope=query_acode
        typeset tmpbpfile=/tmp/bpfile
        typeset tmpfile=/tmp/acode_$DATE1

        export bpfile=$PRODLOGS/to_print.$TIMESTAMP

        /bin/printf "$bname : Logging into oracle\n" >>$logfile
        /bin/printf "$bname : Strating ACODE query $TIMESTAMP\n" 2>&1 >>$logfile

for invoice in `cat /home/production/bin/$FILENAME`

        #run query
        #check valid file
        #print
        #update table
{
sqlplus -s $CONNECTID <<EOF
spool $bpfile
set heading off
set feedback off
set pagesize 1000
set linesize 1000
set echo off
set verify off
select name,acode,a.external_id,page_count,language_code,dept
from emp_details b, external_id_employees a
where a.account_no=b.account_no
and b.dept in ('J01','X01','X02','X03','X09','X04','X05','X06','X45')
and acode in
(select acode from employees where acode = $invoice
and ps=0 and fs=2 ) and external_id_type=9999
and language_code not in (99)
order by a.external_id;

spool off
EOF
}
if [ -s $bpfile ];then
        /bin/cp $bpfile $tmpfile
        /bin/sed -e '/^$/d' \
                 -e 's/[ ] [ ] */ /g' $bpfile >$tmpfile
        /bin/mv $tmpfile $bpfile
        # check file exists , set the printer and lp & update table

        for record in `cat $bpfile`

        {
                acode1=`awk '{print $2 }' $bpfile`
                ename=`awk ' {print $1 }' $bpfile`
                dept1=`awk '{ print $6 }' $bpfile|cut -c 1 `
                

        if [ "$dept1" = "J" ];then
                if [ -f $PRODREADY/$filename ];then
                        /bin/lp -d $PRINTER  $PROD/$filename
                        [ $? = 0 ] && update_invoices $acode1
                else
                        /bin/printf "$bname : Acode file for $acode1 does not exist\n" >>$logfile
                        /bin/printf "$bname:\t Check format status of Employees $acode1\n"
                fi
        else

                if [ -f $PRODREADY/$filename ];then
                        lp -d $PRINTER $PROD/$filename
                        [ $? = 0 ] && update_invoices $acode1
                else
                        /bin/printf "$bname : acode file for $acode1 does not exist\n" >>$logfile
                        /bin/printf "$bname:\t Check format status of Invoice $acode1\n"

                fi
        fi

        }


else
        /bin/printf "$bname: No information in  database for  $acode1 \n" >>$logfile
fi



}
[[ -n "$TRACE_FUNC" || -n "$TRACE_FUNC_query_acode" ]] &&
typeset -ft query_acode