#!/usr/bin/ksh
ORAUSER=$1
P_REQUEST_ID=$4
current_time=`date +%m%d%y.%H%M%S`
echo "Process started at `date +%m/%d/%y.%H:%M:%S`"

#Intialize Variables
export SHLIB_PATH=/usr/local/lib
ext=".pdf"
ps_ext=".ps"
P_PROJ_NO=$5
P_JOB_FROM=$6
P_JOB_TO=$7
PRINTER=$8
P_ORG_CODE=$9

USR=`echo $1|cut -f9 -d " "|cut -f2 -d '"'`
export USR
echo $USR

# Call the Procedure gb_xml_report_attachment.gb_insert_values to insert values

echo 'Before Calling SQLPLUS: gb_xml_report_attachment.gb_insert_values..'

echo 'Displaying all Parameters .....'

echo 'P_REQUEST_ID:-'
echo $P_REQUEST_ID

echo 'P_ORG_CODE :-'
echo $P_ORG_CODE

echo 'P_PROJ_NO :-'
echo $P_PROJ_NO

echo 'P_JOB_FROM :-'
echo $P_JOB_FROM

echo 'P_JOB_TO :-'
echo $P_JOB_TO

sqlplus -s $USR <<EOF
execute gb_xml_report_attachment.gb_insert_values ($P_REQUEST_ID,'$P_ORG_CODE','$P_PROJ_NO','$P_JOB_FROM','$P_JOB_TO');
EXIT;
EOF

NO_OF_COUNT=`sqlplus -s $USR<<EOF
set heading off
set feedback off
select COUNT (1) from gb_job_report_tbl;
EOF`

echo 'NO_OF_COUNT'
echo $NO_OF_COUNT

integer i=0
while ((i < $NO_OF_COUNT));
do
  (( i = i + 1));
  echo 'Entering into Loop..'
  
  echo 'Before Calling SQLPLUS: Fetching File ID..'
    
  FILE_ID=`sqlplus -s $USR<<EOF
  set heading off
  set feedback off
  select file_id from gb_job_report_tbl where record_id =$i AND conc_request_id=$P_REQUEST_ID;
  EXIT;
  EOF`
  
  echo 'File ID :-'
  echo $FILE_ID
  
  JOB_NO=`sqlplus -s $USR<<EOF
  set heading off
  set feedback off
  select job_no from gb_job_report_tbl where record_id =$i AND conc_request_id=$P_REQUEST_ID;
  EXIT;
  EOF`
  
  echo 'JOB No :-'
  echo $JOB_NO
  
  echo 'Before Calling SQLPLUS: gb_xml_report_attachment.gb_run_report..'
  
  #Run the Job Report Using the JOB_NO
  sqlplus -s $USR<<EOF
  set heading off
  set feedback off
  execute gb_xml_report_attachment.gb_run_report('$P_ORG_CODE','$P_PROJ_NO','$JOB_NO','$JOB_NO','$PRINTER');
  EXIT;
  EOF
   
  PS_FILE=$FILE_ID$ps_ext
  
  FNDGFU $USR 0 Y DOWNLOAD=`echo $FILE_ID` $FILENAME
  pdf2ps $FILENAME $PS_FILE
  lp -d $PRINTER $FILE_ID$ps_ext
  rm $FILENAME
  rm $PS_FILE
  rm *.log
  rm *.out
done

# Call the Procedure gb_xml_report_attachment.gb_delete_values to delete values
sqlplus -s $USR<<EOF
set heading off
set feedback off
execute gb_xml_report_attachment.gb_delete_values ($P_REQUEST_ID);
EXIT;
EOF

Before Calling SQLPLUS: gb_xml_report_attachment.gb_insert_values..
Displaying all Parameters .....
P_REQUEST_ID:-
37950775
P_ORG_CODE :-
BAR
P_PROJ_NO :-

P_JOB_FROM :-
1546148
P_JOB_TO :-
1546148

PL/SQL procedure successfully completed.

NO_OF_COUNT
6
/dv01/appdev/devappl/wgt/11.5.0/bin/gb_invoke_xml_attachments.prog[77]: syntax error at line 80 : `do' unmatched
/dv01/appdev/devappl/wgt/11.5.0/bin/gb_invoke_xml_attachments
Program exited with status 2