SQL LOADER

#!/bin/bash

call_sql_load()
{
echo "*---------------------------------------*"
echo "|      Call to SQL Loader               |"
echo "*---------------------------------------*"

LABEL="In SQL Loader function"

echo $EXIT_STATUS
 
  a=`echo . /home/aw14ebs/EBSapps.env run`

  cd $P_DATAFILEPATH
  if [ ! -r $P_DATAFILENAME ]
  then
      echo "Error! File $P_DATAFILENAME is not readable or not present in $P_DATAFILEPATH"
      EXIT_STATUS=1
  else 
      DATAFILE=${P_DATAFILENAME}
      echo "File $P_DATAFILENAME"
      echo "data File $DATAFILE"
      LOAD_FILE=${P_DATAFILEPATH}/${P_DATAFILENAME}
      echo $LOAD_FILE
      ARCH_FILE=${P_ARCHFILEPATH}/${DATAFILE}_${REQUEST_ID}
      LOAD_BADFILE=${P_ARCHFILEPATH}/${DATAFILE}_${REQUEST_ID}.BAD
      LOAD_LOGFILE=${P_ARCHFILEPATH}/${DATAFILE}_${REQUEST_ID}.LOG
      LOAD_DSCFILE=${P_ARCHFILEPATH}/${DATAFILE}_${REQUEST_ID}.DSC
     
     
      LOAD_CTL=${P_CTLFILEPTAH}/${P_CTLFILENAME}
      echo "ctl file: $LOAD_CTL"

    echo "----------------------------------------"
    echo "  SQL Loader Parameters                 "
    echo "----------------------------------------"
    echo "  Data File         : ${LOAD_FILE}"
    echo "  Archive File      : ${ARCH_FILE}"
    echo "  Bad File          : ${LOAD_BADFILE}"
    echo "  Discard File      : ${LOAD_DSCFILE}"
    echo "  Log File          : ${LOAD_LOGFILE}"
    echo "----------------------------------------"

      if [ -r ${LOAD_FILE} ]
      then
         LABEL="SQL Loader ${LOAD_CTL}"
         echo ${LOAD_FILE} ${LOAD_CTL} ${LOAD_LOGFILE} ${LOAD_BADFILE} ${LOAD_DSCFILE}
         RETVAL=`sqlldr userid=${APPSUSPWD} \
                                        data=${LOAD_FILE} \
                                        control=${LOAD_CTL} \
                                        log=${LOAD_LOGFILE} \
                                        bad=${LOAD_BADFILE} \
                                        discard=${LOAD_DSCFILE}
                                        silent=feedback`
         RETVAL=$?

         if [ $RETVAL -ne 0 ]
         then
     echo "-------------------------------------------------------"
            echo "Error! SQL*Loader failed"           
     echo "-------------------------------------------------------"
            EXIT_STATUS=1
         else
            mv $LOAD_FILE $ARCH_FILE
           
     echo "-------------------------------------------------------"
     echo "SQL*Loader Completed... view Log file for more details."    
     echo "-------------------------------------------------------"

     REJECTED_ROWS=`grep "Total logical records rejected:" $LOAD_LOGFILE`
            echo "Check for rejected records during load : ${REJECTED_ROWS}"
         fi
      else
         LABEL="Check Data File Readable"
         echo "Error ! $LOAD_FILE does not exist or is not readable"
         EXIT_STATUS=1
      fi
   fi


}
# ----------------------
# Supplied Parameters
# ----------------------
# Following parameters supplied by concurrent manager:

NUMPARAMS=$#

PROG_NAME=$0
APPSUSPWD=$1
FND_USERID=$2
FND_USERNAME=$3
REQUEST_ID=$4

P_CTLFILENAME=$5       
P_CTLFILEPTAH=$6 
P_DATAFILENAME=$7
P_DATAFILEPATH=$8
P_ARCHFILEPATH=$9


# ---------------
# Environment
# ---------------
EXIT_STATUS=0

# ----------------------------------------------
# Username, Request ID and program specific
# parameters are listed
# ----------------------------------------------

echo "Parameters supplied"
echo "*----------------------------------------*"
echo "  Username             : "$FND_USERNAME
echo "  Request ID           : "$REQUEST_ID
echo "  control File Name    : "$P_CTLFILENAME
echo "  control File Path    : "$P_CTLFILEPTAH
echo "  Data File Name       : "$P_DATAFILENAME
echo "  Data File Path       : "$P_DATAFILEPATH
echo "  Archive File Path    : "$P_ARCHFILEPATH
echo "*-----------------------------------------*"
echo ""

#typeset -l APPS_US=`echo $APPSUSPWD | cut -d/ -f1`
#typeset -l APPS_PW=`echo $APPSUSPWD | cut -d/ -f2`

LABEL="Check Parameters"

#if [ $NUMPARAMS -ne 10 ]
#then
 #  echo "Error ! Incorrect number of parameters supplied"
#   EXIT_STATUS=1
#else
   echo "*--------------------------------------------------------------------*"
   echo "|                         MAIN PROCESSING ....                           |"
   echo "*--------------------------------------------------------------------*"

   call_sql_load

   if [ $EXIT_STATUS -ne 0 ]
   then
     echo "ERROR: At least 1 error occurred during sql load. Check log details"
   fi
#fi
# ----------------------------------
# Exit with the EXIT_STATUS variable
# ----------------------------------
if [ $EXIT_STATUS -ne 0 ]
then
   echo "ERROR: At least 1 error occurred during processing. Check log details"
fi
exit $EXIT_STATUS