#########################################################################
#
# Application : AOL
# Module : GL
# File : XXXGLINTPRT.prog
# Version : %R%.%L%
# Last Update : %E%.%U%
# Description : This host program will call sqlldr routine to load .csv file to
# GL Interface Staging table for Japan.
# If the load is successful then the original file will be renamed
# by suffixing the concurrent request id and date time stamp and put
# to bkup directory, or else, the file will be renamed and copied
# to bad directory alongwith the log generated by sqlldr builtin.
# Parameters : V_LOGIN
# V_USER_ID
# V_USER_NAME
# V_REQUEST_ID
# v_directory
# v_resp_name
# v_run_option
# v_appl_sname
# v_debug_flag
# v_file_name
# v_in_file_loc
#
# Exec Method : connect to sqlplus as < APPS> user and execute this script.
#
#
# Change History
# --------------
#
# Date Author Ver. Change Description
# ----------- ----------------- ---- ------------------------------------
# 23-Oct-2012 Ashok 1.0 Created
#########################################################################
# Accept the parameters
v_login=$1
v_user_id=$2
v_user_name=$3
v_request_id=$4
v_directory=$5
v_resp_name=$6
v_run_option=$7
v_appl_sname=$8
v_debug_flag=$9
shift
v_file_name=$9
shift
v_in_file_loc=$9
shift
v_in_utp_file_loc=$9
v_suffix=_$v_request_id`date +_%y%m%d_%H:%M`
v_control_file="$v_directory/bin/XXX_GLINT_CBS.ctl"
# Check the output file format
v_user_name8=`echo $v_user_name | cut -c 1-8`
v_outputfile=`echo $v_directory`/bkup/$v_user_name8.$v_request_id
#-------------------------------------------------------------------------
proc_call_sql()
{
v_ret_code=$1
v_reqst_id=$2
echo ' RESP NAME :' $v_resp_name
echo ' USER NAME :' $v_user_name
echo ' REQUEST ID :' $v_reqst_id
echo ' RETURN CODE :' $v_ret_code
if test $v_ret_code -eq 0
then
echo '\n Load_Records() returned Success. Submitting Concurent Request to start the validation process.\n'
# CONCSUB $v_login SQLAP "$v_resp_name" $v_user_name WAIT=N CONCURRENT "EXELAPAC" "EXELAPAC_ARINTV" "$v_reqst_id"
elif test $v_ret_code -eq 2
then
echo '\n Load_Records() returned Warning. Please check the exception records (at section : between Stat of bad file and End of bad file).'
echo '\n Load_Records() returned Warning. Submitting Concurent Request to start the validation process.\n'
else
echo '\n Load_Records() returned Error. Submitting Concurent Request to start the validation process.\n'
fi
}
#-------------------------------------------------------------------------
load_records()
{
echo "\nProcedure load_records executes and starts loading..."
echo "-+-Start of Loader Program -----------------------+-"
echo " Control File :"$v_control_file
echo " Log file :"$v_in_file_loc/$v_logfile
echo " Data file :"$v_in_file_loc/$v_datafile
#Call the sql loader to load the records into the temporary table.
sqlldr userid=$v_login control=$v_control_file log=$v_in_file_loc/$v_logfile bad=$v_in_file_loc/$v_badfile data=$v_in_file_loc/$v_datafile errors=999999 silent=HEADER silent=FEEDBACK
retcode=$?
echo "\n Displaying log file : "$v_logfile
echo "\n-+-Start of log file -----------------------------+-"
cat $v_in_file_loc/$v_logfile
echo "\n-+-End of log file -------------------------------+-\n"
echo 'Return value from Sqlldr : ' $retcode
# Check for loading with status 'Success'
if test $retcode -eq 0
then
echo "\n Successfully Loaded All Record(s)"
echo "\n The datafile $v_datafile is sent to the $v_in_file_loc/bkup"
mv $v_in_file_loc/$v_datafile $v_in_file_loc/bkup/$v_datafile
rm $v_in_file_loc/$v_logfile
echo "\n-+-End of Loader Program -------------------------+-\n"
echo $v_user_name
echo $v_request_id
#Added by Muthu 02-OCT-08
$ORACLE_HOME/bin/sqlplus -s $v_login <<- EOF
UPDATE XXX_GLINT_TM300i
SET user_name='$v_user_name'
WHERE request_id=$v_request_id;
SELECT 'Records Updated in Temporary Table for request'||$v_request_id from dual;
Commit;
exit
EOF
return $retcode
else
echo "\n Bad Rows found while loading."
echo "\n The datafile $v_datafile is sent to the $v_directory/bad"
#mv $v_in_file_loc/$v_datafile $v_directory/bad/$v_datafile
echo "\n The logfile $v_logfile is sent to the $v_directory/bad"
mv $v_in_file_loc/$v_logfile $v_directory/bad/$v_logfile
mv $v_in_file_loc/$v_datafile $v_in_file_loc/bkup/$v_datafile
if test $retcode -eq 1
then
echo "\n Sqlldr has returnd Error code, no record(s) will be processed."
elif test $retcode -eq 2
then
echo $v_user_name
echo $v_request_id
$ORACLE_HOME/bin/sqlplus -s $v_login <<- EOF
UPDATE XXX_GLINT_TM300i
SET process_flag='B',
error_description = 'This record is part of a file that had bad record(s) and hence will not be processed further. Please delete this record. Refer to log file of EXEL AU AP REGULAR INVOICE INTERFACE to identify the bad record(s).'
WHERE request_id=$v_request_id;
SELECT 'Records Updated in Temporary Table for request'||$v_request_id "MSG" from dual;
Commit;
exit
EOF
echo "\n Sqlldr has return Warning code, only successfully loaded record(s) will be processed."
fi
# Check for the existence of bad file
echo "\n Searching for Bad file (if Sqlldr has encountered Bad Rows while import)"
if test -f $v_in_file_loc/$v_badfile
then
echo "\n Bad Rows found while loading."
echo "\n Displaying bad file : "$v_badfile
echo "\n-+-Start of bad file -----------------------------+-\n"
cat $v_in_file_loc/$v_badfile
echo "\n-+-End of bad file -------------------------------+-\n"
#rm $v_in_file_loc/$v_badfile
mv $v_in_file_loc/$v_badfile $v_directory/bad/$v_badfile
echo "\n-+-End of loader Program--------------------------+-\n"
fi
return $retcode
fi
}
#-------------------------------------------------------------------------
# Main Program
#=========================================================================
echo 'Program Initiated'
echo '-+------------------------------------------------+-'
if test $v_run_option = "L"
then
echo 'Source file location : '$v_in_utp_file_loc
echo $v_in_utp_file_loc/$v_file_name.txt
if test -f $v_in_utp_file_loc/$v_file_name.txt
then
echo 'Source file name : '$v_file_name.txt
cp $v_in_utp_file_loc/$v_file_name.txt $v_in_file_loc/$v_file_name$v_suffix.txt
v_datafile=$v_file_name$v_suffix.txt
v_logfile=$v_file_name$v_suffix.log
v_badfile=$v_file_name$v_suffix.bad
echo 'Source file renamed to: '$v_datafile
echo 'Loading records from : '$v_datafile
rm $v_in_file_loc/$v_file_name.txt
load_records
ret_load=$?
if test $ret_load -eq 1
then
echo '\n Load_Records() returned Error.'
echo '\n Calling PL/SQL Script \n'
else
echo '\n Calling PL/SQL Script \n'
fi
else
echo '\nFile not found at following path \n'
echo $v_in_utp_file_loc/$v_file_name.txt
echo '\n Calling PL/SQL Script \n'
ret_load=1
fi
proc_call_sql "$ret_load" "$v_request_id"
elif test $v_run_option = "P"
then
ret_load=0
v_request_id=-1
echo ' '
echo 'Calling PL/SQL Script \n'
proc_call_sql "$ret_load" " "
fi
echo ' '
echo "Program ended successfully. \n"
# End of Unix Program
#
# Application : AOL
# Module : GL
# File : XXXGLINTPRT.prog
# Version : %R%.%L%
# Last Update : %E%.%U%
# Description : This host program will call sqlldr routine to load .csv file to
# GL Interface Staging table for Japan.
# If the load is successful then the original file will be renamed
# by suffixing the concurrent request id and date time stamp and put
# to bkup directory, or else, the file will be renamed and copied
# to bad directory alongwith the log generated by sqlldr builtin.
# Parameters : V_LOGIN
# V_USER_ID
# V_USER_NAME
# V_REQUEST_ID
# v_directory
# v_resp_name
# v_run_option
# v_appl_sname
# v_debug_flag
# v_file_name
# v_in_file_loc
#
# Exec Method : connect to sqlplus as < APPS> user and execute this script.
#
#
# Change History
# --------------
#
# Date Author Ver. Change Description
# ----------- ----------------- ---- ------------------------------------
# 23-Oct-2012 Ashok 1.0 Created
#########################################################################
# Accept the parameters
v_login=$1
v_user_id=$2
v_user_name=$3
v_request_id=$4
v_directory=$5
v_resp_name=$6
v_run_option=$7
v_appl_sname=$8
v_debug_flag=$9
shift
v_file_name=$9
shift
v_in_file_loc=$9
shift
v_in_utp_file_loc=$9
v_suffix=_$v_request_id`date +_%y%m%d_%H:%M`
v_control_file="$v_directory/bin/XXX_GLINT_CBS.ctl"
# Check the output file format
v_user_name8=`echo $v_user_name | cut -c 1-8`
v_outputfile=`echo $v_directory`/bkup/$v_user_name8.$v_request_id
#-------------------------------------------------------------------------
proc_call_sql()
{
v_ret_code=$1
v_reqst_id=$2
echo ' RESP NAME :' $v_resp_name
echo ' USER NAME :' $v_user_name
echo ' REQUEST ID :' $v_reqst_id
echo ' RETURN CODE :' $v_ret_code
if test $v_ret_code -eq 0
then
echo '\n Load_Records() returned Success. Submitting Concurent Request to start the validation process.\n'
# CONCSUB $v_login SQLAP "$v_resp_name" $v_user_name WAIT=N CONCURRENT "EXELAPAC" "EXELAPAC_ARINTV" "$v_reqst_id"
elif test $v_ret_code -eq 2
then
echo '\n Load_Records() returned Warning. Please check the exception records (at section : between Stat of bad file and End of bad file).'
echo '\n Load_Records() returned Warning. Submitting Concurent Request to start the validation process.\n'
else
echo '\n Load_Records() returned Error. Submitting Concurent Request to start the validation process.\n'
fi
}
#-------------------------------------------------------------------------
load_records()
{
echo "\nProcedure load_records executes and starts loading..."
echo "-+-Start of Loader Program -----------------------+-"
echo " Control File :"$v_control_file
echo " Log file :"$v_in_file_loc/$v_logfile
echo " Data file :"$v_in_file_loc/$v_datafile
#Call the sql loader to load the records into the temporary table.
sqlldr userid=$v_login control=$v_control_file log=$v_in_file_loc/$v_logfile bad=$v_in_file_loc/$v_badfile data=$v_in_file_loc/$v_datafile errors=999999 silent=HEADER silent=FEEDBACK
retcode=$?
echo "\n Displaying log file : "$v_logfile
echo "\n-+-Start of log file -----------------------------+-"
cat $v_in_file_loc/$v_logfile
echo "\n-+-End of log file -------------------------------+-\n"
echo 'Return value from Sqlldr : ' $retcode
# Check for loading with status 'Success'
if test $retcode -eq 0
then
echo "\n Successfully Loaded All Record(s)"
echo "\n The datafile $v_datafile is sent to the $v_in_file_loc/bkup"
mv $v_in_file_loc/$v_datafile $v_in_file_loc/bkup/$v_datafile
rm $v_in_file_loc/$v_logfile
echo "\n-+-End of Loader Program -------------------------+-\n"
echo $v_user_name
echo $v_request_id
#Added by Muthu 02-OCT-08
$ORACLE_HOME/bin/sqlplus -s $v_login <<- EOF
UPDATE XXX_GLINT_TM300i
SET user_name='$v_user_name'
WHERE request_id=$v_request_id;
SELECT 'Records Updated in Temporary Table for request'||$v_request_id from dual;
Commit;
exit
EOF
return $retcode
else
echo "\n Bad Rows found while loading."
echo "\n The datafile $v_datafile is sent to the $v_directory/bad"
#mv $v_in_file_loc/$v_datafile $v_directory/bad/$v_datafile
echo "\n The logfile $v_logfile is sent to the $v_directory/bad"
mv $v_in_file_loc/$v_logfile $v_directory/bad/$v_logfile
mv $v_in_file_loc/$v_datafile $v_in_file_loc/bkup/$v_datafile
if test $retcode -eq 1
then
echo "\n Sqlldr has returnd Error code, no record(s) will be processed."
elif test $retcode -eq 2
then
echo $v_user_name
echo $v_request_id
$ORACLE_HOME/bin/sqlplus -s $v_login <<- EOF
UPDATE XXX_GLINT_TM300i
SET process_flag='B',
error_description = 'This record is part of a file that had bad record(s) and hence will not be processed further. Please delete this record. Refer to log file of EXEL AU AP REGULAR INVOICE INTERFACE to identify the bad record(s).'
WHERE request_id=$v_request_id;
SELECT 'Records Updated in Temporary Table for request'||$v_request_id "MSG" from dual;
Commit;
exit
EOF
echo "\n Sqlldr has return Warning code, only successfully loaded record(s) will be processed."
fi
# Check for the existence of bad file
echo "\n Searching for Bad file (if Sqlldr has encountered Bad Rows while import)"
if test -f $v_in_file_loc/$v_badfile
then
echo "\n Bad Rows found while loading."
echo "\n Displaying bad file : "$v_badfile
echo "\n-+-Start of bad file -----------------------------+-\n"
cat $v_in_file_loc/$v_badfile
echo "\n-+-End of bad file -------------------------------+-\n"
#rm $v_in_file_loc/$v_badfile
mv $v_in_file_loc/$v_badfile $v_directory/bad/$v_badfile
echo "\n-+-End of loader Program--------------------------+-\n"
fi
return $retcode
fi
}
#-------------------------------------------------------------------------
# Main Program
#=========================================================================
echo 'Program Initiated'
echo '-+------------------------------------------------+-'
if test $v_run_option = "L"
then
echo 'Source file location : '$v_in_utp_file_loc
echo $v_in_utp_file_loc/$v_file_name.txt
if test -f $v_in_utp_file_loc/$v_file_name.txt
then
echo 'Source file name : '$v_file_name.txt
cp $v_in_utp_file_loc/$v_file_name.txt $v_in_file_loc/$v_file_name$v_suffix.txt
v_datafile=$v_file_name$v_suffix.txt
v_logfile=$v_file_name$v_suffix.log
v_badfile=$v_file_name$v_suffix.bad
echo 'Source file renamed to: '$v_datafile
echo 'Loading records from : '$v_datafile
rm $v_in_file_loc/$v_file_name.txt
load_records
ret_load=$?
if test $ret_load -eq 1
then
echo '\n Load_Records() returned Error.'
echo '\n Calling PL/SQL Script \n'
else
echo '\n Calling PL/SQL Script \n'
fi
else
echo '\nFile not found at following path \n'
echo $v_in_utp_file_loc/$v_file_name.txt
echo '\n Calling PL/SQL Script \n'
ret_load=1
fi
proc_call_sql "$ret_load" "$v_request_id"
elif test $v_run_option = "P"
then
ret_load=0
v_request_id=-1
echo ' '
echo 'Calling PL/SQL Script \n'
proc_call_sql "$ret_load" " "
fi
echo ' '
echo "Program ended successfully. \n"
# End of Unix Program
Hi Can you even provide the ctl file also
ReplyDelete