# bjvfeed_z.shl 	Batch Journal Voucher Feed Z - Generic JV Load
# **Use this information and these scripts at your own risk. As a condition of using these scripts and information from this site, you agree to hold harmless both the University of Arkansas Cooperative Extension Service and Bruce Knox for any problems that they may cause or other situations that may arise from their use, and that neither the Extension Service nor I will be held liable for those consequences. The scripts and information are provided "as is" without warranty, implied or otherwise. Limitation of liability will be the amount paid to the University of Arkansas specifically for this information. (It was free:) 
# The procedure for Interfacing Batch Journal Voucher starts with downloading the file
# jvload.csv from the File Server

# this AIX(UNIX) shell script (bjvfeed_z.shl)
# FTP jvload.csv to the Oracle Server /home/uni/jvload.csv

# sqlldr or sqlload then appends the new bjvfeed_z file onto bjvfinc

# FTP jvload.csv to the Oracle Server /home/uni/jvload.csv 

cd /home/uni

rm -f jvload.csv
rm -f bjvfeed_z.dat

echo
echo Please Enter Your Network password
echo
if [[ "$USER" = bknox ]]
then
ftp -n -i 170.94.62.15<<eof
user .$USER.office1.loc.div
ascii
cd / 
cd apps
cd prepost
get jvload.csv
quit

eof

else
ftp -n -i 170.94.62.15<<eof
user .$USER.office2.loc.div
ascii
cd / 
cd apps
cd prepost
get jvload.csv
quit

eof

fi

chmod 666 jvload.csv

# test for file exist
if [[ -f jvload.csv ]]
then
print FTP Completed
else
print FTP Failed
exit 1
fi

# delete any Hex 1A/Octal 032/(Ctrl+Z) or Hex 09/Octal 011/(Tab) from the extract file
cat jvload.csv | tr -d '\032' | tr -d '\011' > jvload_step1.csv
# replace string ',,,,,,' with NULL
sed -e "s/,,,,,,//g" jvload_step1.csv | tr -s '\n' > bjvfeed_z.dat
rm -f jvload_step1.csv

chmod 666 bjvfeed_z.dat

# Process the New jvload.csv data by loading bjvfeed_z.dat

rm -f bjvfeed_z.bad
rm -f bjvfeed_z.log
rm -f bjvfeed_z.dsc

echo
print You will be Updating $ORACLE_SID with this procedure.
echo
print Ctrl+C to Cancel
echo
echo "Load Transactions for Batch Journal Voucher Into Banner"
echo
echo "Enter your Banner Username and Password"
echo
echo "Username: \c"
read UID; export UID
echo "Password: \c"
stty -echo
read PWD; export PWD
stty echo
echo
echo
echo "Enter the Document Description (no single or double qoutes)"
echo ": \c"
read doc_desc; export doc_desc
echo
echo "Enter the Transaction Date to be used for this Batch JV (MMDDYYYY) "
echo ": \c"
read transdate; export transdate

doc_desc2=`echo $doc_desc | tr -s '[:space:]' '[#*]'`

sqlldr USERID=$UID/$PWD control=bjvfeed_z.ctl log=bjvfeed_z.log DISCARD=bjvfeed_z.dsc

# Check for LOADER errors -- email Table Load Failure to Programmer
if [[ $? = 0 ]] 
then
 echo
else
 echo "JV Load Batch Journal Voucher Failed"
 mail -s "Table Load Failed bjvfeed_z 1" bknox@uaex.edu < bjvfeed_z.log
 rm -f /home/uni/bjvfeed_z.dat
 exit 1
fi

# Check for Bad Records Sent to bjvfeed_z.bad
if [ -s "/home/uni/bjvfeed_z.bad" ]
then
  echo 'Check for Rejected Records bjvfeed_z.log and bjvfeed_z.bad'
  cat bjvfeed_z.bad bjvfeed_z.log > bjvfeed_z.err
  mail -s "Table Load Bad Records bjvfeed_z 2" -c bknox@uaex.edu $USER@edison.uaex.edu < bjvfeed_z.err
  chmod 666 bjvfeed_z.bad
  rm -f bjvfeed_z.err
fi

chmod 666 bjvfeed_z.log

echo $PWD | sqlplus -s $UID @/home/uni/bjvfeed_z.sql $transdate $doc_desc2 

# Check for SQL*Plus errors -- email Table Load Failure to Programmer
if [[ $? = 0 ]] 
then
 echo "SQL*Plus Success"
else
 echo "JV Load Batch Journal Voucher Failed"
 mail -s "Table Load Failed bjvfeed_z 3" bknox@uaex.edu < bjvfeed_z.log
 rm -f /home/uni/bjvfeed_z.dat
 exit 1
fi

echo
echo "JV Load Batch Journal Voucher Complete" 
exit