Code:
#!/bin/ksh
LOGIN=${1}
control_path="$XXGL_TOP/bin"
log_path="$XXGL_TOP/bin/log/"
bad_path="$XXGL_TOP/bin/bad/"
echo "version 1.0"
target_path="$XXGL_TOP/interface/sia/process/"
get_file_list_1=$log_path"sia_file_list.gfl1"
archive_path="$XXGL_TOP/interface/sia/archive/"
ln_file_count=0
SOURCE_ID="XXGL_siainterface"
LOG_F=${APPLCSF}/log/l$4.req
ln_fine_header=0
user_id=${2}
#ld_interface_date=`echo | date --date="${5}" +%Y%m%d`
ld_interface_date=`sqlplus $LOGIN <<- EOF | awk '/pccw/ {print $2} '
SELECT 'pccw' , to_char(to_date('${5}','YYYY/MM/DD HH24:mi:ss'),'YYYYMMDD') FROM dual;
EOF`
ln_file_name=`echo $ld_interface_date"_"${6}".csv"`
ln_ledger=`echo ${7}`
ln_source_system=`echo ${8}`
ln_input_directory=`echo ${9}`
eval dir=$9
#source_path="/data01/u01/applhl/int/SIA/IN"
#source_path="$HL_INT_HOME/SIA/IN"
source_path="${dir}"
#storage_path="/data01/u01/applhl/int/SIA/IN/ARCHIVE"
#new_source_path=${9}
echo $source_path
#source_path=echo $new_source_path/SIA/IN
cd $source_path
cd ..
#storage_path=`echo $source_path|sed s/in/archive/g`
storage_path=`pwd`"/archive"
echo $storage_path
echo ${ld_interface_date}
echo "***Start remove temporary log***"
sqlplus $LOGIN <<ENDOFSQL
connect $LOGIN
SET SERVEROUTPUT OFF
DECLARE
fl utl_file.file_type;
BEGIN
fl := utl_file.fopen ('$APPLPTMP','sia_interface_err_chk.txt', 'w', 32767);
utl_file.put_line (fl, '');
utl_file.fclose (fl) ;
END;
/
exit
ENDOFSQL
echo $source_path
cd ${source_path}
echo "Try to find any file exist in the source_path"
echo $ln_file_name
ls $ln_file_name > $get_file_list_1
# Count the number of files exist in source_path
if ( test -s $get_file_list_1 )
then
awk 'END{print NR}' $get_file_list_1 | read O_FILENUM
cat $get_file_list_1 | while read line
do
RZIP=`echo $line`
echo $RZIP | awk -F. '{print $1, $2}' | read head tail
echo $head"."$tail
DOWNLOADFILE=$head"."$tail
#ARC_FILE_D=`echo ${DOWNLOADFILE}`
echo "file found in source_path"
ln_file_exist_code=`sqlplus $LOGIN <<- EOF | awk '/pccw/ {print $2} '
SELECT 'pccw',count(*) from XXGL_INTERFACE_CONTROL where FILE_NAME = '$ln_file_name';
EOF`
#echo $ln_file_exist_code
if [ ${ln_file_exist_code} = 0 ]
then
#Get INTERFACE_CONTROL_ID
ln_control_id=`sqlplus $LOGIN <<- EOF | awk '/pccw/ {print $2} '
SELECT 'pccw', XXGL_INT_CTL_S.NEXTVAL FROM dual;
EOF`
#echo $ln_control_id
#Get GROUP_ID
ln_group_id=`sqlplus $LOGIN <<- EOF | awk '/pccw/ {print $2} '
#SELECT 'pccw', gl_interface_control_s.NEXTVAL FROM dual;
SELECT fnd_global.SECURITY_GROUP_ID from dual;
EOF`
#echo $ln_group_id
sqlplus $LOGIN <<ENDOFSQL
connect $LOGIN
SET SERVEROUTPUT OFF
DECLARE
ld_int_date date;
BEGIN
select to_date('${5}','YYYY/MM/DD hh24:mi:ss','NLS_DATE_LANGUAGE = American') into ld_int_date from dual;
insert into XXGL_INTERFACE_CONTROL values('${ln_control_id}','${DOWNLOADFILE}',ld_int_date,'${4}',${ln_group_id},NULL,'${ln_source_system}','GL','$2',SYSDATE);
END;
/
exit
ENDOFSQL
#char=`enca ${DOWNLOADFILE} |awk -F ';' '{print $2}'`
#echo "Interface file ${DOWNLOADFILE} Character set is ${char}"
#enca -L zh_CN -x UTF-8 ${DOWNLOADFILE}
show_error_start=1
is_err=0
cat ${DOWNLOADFILE} |awk -F , '{print NF,NR;}'|while read l_line_count l_line
do
if test $l_line = 1
then
if test $(($l_line_count)) = 2
then
a="a"
else
if test $(($show_error_start)) = 1
then
echo "+--------------------ERROR LINE DATA START-------------------------+"
show_error_start=2
fi
lc_show_err_line=`echo "NR==${l_line}{print}"`
lc_show_err=`awk ${lc_show_err_line} ${DOWNLOADFILE}`
echo ${lc_show_err}
is_err=1
fi
else
if test $(($l_line_count)) = 10
then
a="a"
else
if test $(($show_error_start)) = 1
then
echo "+--------------------ERROR LINE DATA START-------------------------+"
show_error_start=2
fi
lc_show_err_line=`echo "NR==${l_line}{print}"`
lc_show_err=`awk ${lc_show_err_line} ${DOWNLOADFILE}`
echo ${lc_show_err}
is_err=1
fi
fi
done
if test $(($is_err)) = 1
then
echo "+--------------------ERROR LINE DATA END-------------------------+"
echo "+-----------------------------ERROR------------------------------+"
echo "ERR-003:Incorrect file format"
echo "+-----------------------------ERROR------------------------------+"
MYSTATUS=`sqlplus -s $1 <<!
SET HEADING FEEDBACK OFF PAGESIZE 0
declare
l_result boolean;
l_session_id number;
begin
fnd_global.INITIALIZE(l_session_id, null, null, null,null, -1, null, null, null, null, $4, null,null,null,null,null,null,-1);
l_result := fnd_concurrent.set_completion_status('ERROR','Review log file for details.');
commit;
end;
/
exit;
!`
exit
fi
ln_file_count=ln_file_count+1
echo ${DOWNLOADFILE}
cp -p ${source_path}"/"${DOWNLOADFILE} ${target_path}
#echo $ARC_FILE_D
if [ ${ln_fine_header} = 0 ]
then
# set sqldll
ln_file_namep=`echo ${DOWNLOADFILE}|cut -f 1 -d .`
IN_FILE="'""${TARGET_PATH}${DOWNLOADFILE}""'"
LOAD_COM_NAME="${SOURCE_ID}"".ctl"
BAD_FILE_NAME=`echo ${DOWNLOADFILE}|cut -f 1 -d .`.${4}".bad"
LOG_FILE_NAME=`echo ${DOWNLOADFILE}|cut -f 1 -d .`.${4}".log"
OUT_FILE_NAME=`echo ${DOWNLOADFILE}|cut -f 1 -d .`.${4}".out"
EXCEPTLOT_FILE_NAME=`echo "HL_SIA_INTERFACE_EXCEPTION_LOG_"${ln_file_namep}".csv"`
# Create the control file for the interface source
# ln_control_id2=`sqlplus $LOGIN <<- EOF | awk '/pccw/ {print $2}'
# SELECT 'pccw',INTERFACE_CONTROL_ID FROM XXGL_INTERFACE_CONTROL where FILE_NAME = '$ln_file_name';
# EOF`
# echo $ln_control_id2
cat > ${log_path}${LOAD_COM_NAME} <<!
OPTIONS (SKIP=2)
LOAD DATA
INFILE ${IN_FILE}
APPEND
INTO TABLE XXGL_INT_CARPARK_STG
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
TRX_DATE,
CAR_PARK_NAME,
TYPE,
SUB_TYPE,
RECEIPT_METHOD,
EFFECTIVE_DATE,
TRX_COUNT,
INCOME,
NO_HRS,
UPD_DEL "substr(:UPD_DEL,1,1)",
INTERFACE_CONTROL_ID CONSTANT ${ln_control_id},
ROW_NUM RECNUM
)
!
# Test the return status of the Sqlloader control file
ifresult_d=`find ${log_path} -name ${LOAD_COM_NAME} -print`
# Test to see if a download file was created and is available
if [ "${ifresult_d:-0}" = 0 ]; then
# No SQL Loader control file exists, therefore #exit with error return before attempting loading
echo "There is no sqloader control file create in log directory"
#exit 1
fi
# Test to see if a download file was created and is available
if [ "${ifresult_d:-0}" = 0 ]; then
# No input file exists, therefore #exit with error return before attempting loading
echo "There is no sqloader control file create in log directory"
#exit 1
fi
# Load data into Oracle database
sqlldr ${LOGIN} \
control=${log_path}${LOAD_COM_NAME} \
bad=${bad_path}${BAD_FILE_NAME} \
log=${log_path}${LOG_FILE_NAME} > ${log_path}${OUT_FILE_NAME} \
errors = 999999 \
# Test for the existence of a bad file
ifresult_d=`find ${bad_path} -name ${BAD_FILE_NAME} -print`
# Test to see if a download file was created and is available
if [ "${ifresult_d:-0}" = 0 ] && [ "${ifresult_t:-0}" = 0 ]; then
# Check to see if there was an Oracle error message in the log file
ifresult_d=`grep 'ORA-' ${log_path}${LOG_FILE_NAME}`
if [ "${ifresult_d:-0}" = 0 ]; then
#mv ${TARGET_PATH}${DOWNLOADFILE} ${TEMP_PATH}$ARC_FILE_D
#rm ${log_path}${LOAD_COM_NAME}
#rm ${log_path}${LOG_FILE_NAME}
#rm ${log_path}${OUT_FILE_NAME}
echo "Successful completion"
#exit 0
ln_record_no=`echo | awk -F, '{if (NR==1) printf $2}' ${TARGET_PATH}${DOWNLOADFILE}`
echo "ln_record_no:"$ln_record_no
echo $9
shift
echo $9
sqlplus $LOGIN <<ENDOFSQL
connect $LOGIN
SET SERVEROUTPUT ON
DECLARE
ERRBUFF VARCHAR2(4000);
ERRCODE VARCHAR2(100);
P_FILE_NAME varchar2(250);
P_USER_ID varchar2(200);
fl utl_file.file_type ;
P_CONTROL_ID varchar2(20);
P_RECORD_NO VARCHAR2(10);
P_RESP_ID number:='$9';
BEGIN
fl := utl_file.fopen ('$APPLPTMP','sia_interface_err_chk.txt', 'a', 32767);
P_FILE_NAME := '${ln_file_namep}';
P_USER_ID := '${user_id}';
P_CONTROL_ID:='${ln_control_id}';
XXGL_INT_CARPARK_PKG.LOAD(
ERRBUFF => ERRBUFF,
ERRCODE => ERRCODE,
P_FILE_NAME => P_FILE_NAME,
P_USER_ID=>P_USER_ID,
P_FILE_ID=>'$ln_group_id',
P_CONTROL_ID => P_CONTROL_ID,
P_LEDGER =>'$6',
P_SOURCE_SYSTEM => '$7',
P_RECORD_NO => '$ln_record_no',
P_resp_id =>P_RESP_ID
);
end;
/
exit
ENDOFSQL
# Archive file
lc_import_status=`sqlplus $LOGIN <<- EOF | awk '/pccw/ {print $2} '
SELECT 'pccw',nvl(status,'N') from XXGL_INTERFACE_CONTROL where interface_control_id = '$ln_control_id';
EOF`
if [ ${lc_import_status} = 'Y' ]
then
#cp -p ${source_path}"/"${DOWNLOADFILE} ${storage_path}
#chmod 744 ${storage_path}"/"${DOWNLOADFILE}
mv ${source_path}"/"${DOWNLOADFILE} ${storage_path}
#cp -p ${target_path}${DOWNLOADFILE} ${archive_path}
rm ${target_path}${DOWNLOADFILE}
fi
else
echo "The SQL*Load did not finish successfully"
echo "The Oracle error message was : "${ifresult_d}
#exit 1
fi
else
#The BAD file must exist, therefore there were errors with SQL*Loader
echo "Error during SQL*Load, please refer to the EXCEPTION LOG files for more details"
echo "Interface File detailed information of the wrong line"
echo "ERROR Field LINE:"
echo "----------------------error line start data----------------------------"
cat ${bad_path}${BAD_FILE_NAME} >> ${LOG_F}
echo "----------------------error line end data------------------------------"
#cat ${log_path}${LOG_FILE_NAME} >> ${LOG_F}
#echo "SQL*Loader Complete with error"
#echo "==========Log File==========" >> ${log_path}${EXCEPTLOT_FILE_NAME}
#cat ${log_path}${LOG_FILE_NAME} >> ${log_path}${EXCEPTLOT_FILE_NAME}
#echo "" >> ${log_path}${EXCEPTLOT_FILE_NAME}
#echo "==========Bad File==========" >> ${log_path}${EXCEPTLOT_FILE_NAME}
#cat ${bad_path}${BAD_FILE_NAME} >> ${log_path}${EXCEPTLOT_FILE_NAME}
#lc_bad=`cat ${bad_path}${BAD_FILE_NAME} |awk -F , '{a=NF+a}END{print a;}'`
#l_line_count=`awk 'END{print NR}' ${bad_path}${BAD_FILE_NAME}`
# if test $((10*$l_line_count)) = $lc_bad
# then
echo "+-------------------------------------ERROR-------------------------------+"
echo "ERP-005 - <Field> exceed maximum field length"
echo "+-------------------ERROR-------------------+"
# else
# echo "+-------------------------------------ERROR-------------------------------+"
# echo "ERR-003:Incorrect file format"
# echo "+-------------------ERROR-------------------+"
# fi
#c_text="The SQL*Loader bad file created"
#read_sql_stmt "select * from XXBR_CE_STATEMENT_LINES_STG"
#read_sql_stmt "delete from XXBR_CE_STATEMENT_LINES_STG"
#read_sql_stmt "update fnd_concurrent_requests set phase_code = `C`, status_code = `G`, completion_text = `${c_text}` where request_id = $4"
# read_sql_stmt "commit"
#######################################################################################################
MYSTATUS=`sqlplus -s $1 <<!
SET HEADING FEEDBACK OFF PAGESIZE 0
declare
l_result boolean;
l_session_id number;
begin
fnd_global.INITIALIZE(l_session_id, null, null, null,null, -1, null, null, null, null, $4, null,null,null,null,null,null,-1);
l_result := fnd_concurrent.set_completion_status('ERROR','Review log file for details.');
commit;
end;
/
exit;
!`
#######################################################################################################
exit
fi
fi
else
echo "+-------------------ERROR-------------------+"
echo "ERR-002:Interface file has been uploaded before"
echo "+-------------------ERROR-------------------+"
#2012 - 3- 2 zhusixiang
#sqlplus $LOGIN <<ENDOFSQL
#connect $LOGIN
# SET SERVEROUTPUT ON
# DECLARE
#LC_RESP_APPL_ID VARCHAR2(20);
#LB_LAYOUT BOOLEAN;
#LC_REQ_ID VARCHAR2(20);
#ln_control number;
#begin
#ln_control:='$ln_control_id';
#INSERT INTO XXGL_INT_LOAD_ERR
#VALUES
# (ln_control,
# 0,
# 'ERR-002:Interface file has been uploaded before');
#COMMIT;
#SELECT APPLICATION_ID
# INTO LC_RESP_APPL_ID
# FROM FND_APPLICATION
# WHERE APPLICATION_SHORT_NAME = 'SQLGL';
#FND_GLOBAL.APPS_INITIALIZE(USER_ID => '$2',
# RESP_ID => '${10}' ,
# RESP_APPL_ID => LC_RESP_APPL_ID);
#LB_LAYOUT := FND_REQUEST.ADD_LAYOUT(TEMPLATE_APPL_NAME => 'XXGL',
# TEMPLATE_CODE => 'XXGLHCRVER',
# TEMPLATE_LANGUAGE => 'English',
# TEMPLATE_TERRITORY => NULL,
# OUTPUT_FORMAT => 'PDF');
#LC_REQ_ID := FND_REQUEST.SUBMIT_REQUEST(APPLICATION => 'XXGL',
# PROGRAM => 'XXGLHCRVER', --PROGRAM SHORT NAME
# DESCRIPTION => NULL,
# START_TIME => NULL,
# SUB_REQUEST => FALSE,
# ARGUMENT1 => '$ln_control_id');
#COMMIT;
#IF LB_LAYOUT = FALSE THEN
# FND_FILE.PUT_LINE(FND_FILE.LOG,
# 'Error-Report add layout is fail .fnd_request.add_layout return false');
#END IF;
#IF LC_REQ_ID = 0 THEN
# FND_FILE.PUT_LINE(FND_FILE.LOG,
# 'Error-Report submit request is fail .FND_REQUEST.SUBMIT_REQUEST return 0');
#END IF;
#END;
#/
#exit
#ENDOFSQL
#######################################################################################################
MYSTATUS=`sqlplus -s $1 <<!
SET HEADING FEEDBACK OFF PAGESIZE 0
declare
l_result boolean;
l_session_id number;
begin
fnd_global.INITIALIZE(l_session_id, null, null, null,null, -1, null, null, null, null, $4, null,null,null,null,null,null,-1);
l_result := fnd_concurrent.set_completion_status('ERROR','Review log file for details.');
commit;
end;
/
exit;
!`
#######################################################################################################
fi
done
if [ ${ln_file_count} = 0 ]
then
echo "No File copy to target_path program exit"
# Yan 11-Mar-2011.
# sqlplus -S $LOGIN <<ENDOFSQL
# connect $LOGIN
# SET SERVEROUTPUT ON
# DECLARE
# ln_output_message_id NUMBER;
# lv_errbuf VARCHAR2(1000);
# lv_retcode VARCHAR2(1);
# ln_user_id NUMBER;
# ln_resp_id NUMBER;
# ln_resp_appl_id NUMBER;
# ln_request_id NUMBER;
# b_flag BOOLEAN;
# CURSOR C_RECIPIENT IS
# SELECT FLEX_VALUE RECIPIENT
# FROM FND_FLEX_VALUE_SETS S, FND_FLEX_VALUES V
# WHERE S.FLEX_VALUE_SET_NAME = 'XXGL_SAS_NOTIFICATION'
# AND S.FLEX_VALUE_SET_ID = V.FLEX_VALUE_SET_ID
# AND NVL(V.ENABLED_FLAG, 'N') = 'Y'
# AND TRUNC(SYSDATE) BETWEEN
# NVL(V.START_DATE_ACTIVE, TO_DATE('01011900', 'ddmmyyyy')) AND
# NVL(V.END_DATE_ACTIVE, TO_DATE('31124712', 'ddmmyyyy'));
# BEGIN
# ln_output_message_id := xxfnd_output_messages_s.nextval;
# dbms_output.put_line('ln_output_message_id = ' || ln_output_message_id);
# BEGIN
# SELECT user_id
# INTO ln_user_id
# FROM fnd_user
# WHERE user_name = 'SYSADMIN';
# EXCEPTION
# WHEN OTHERS THEN
# dbms_output.put_line('Failed to get USER ID of SYSADMIN');
# END;
# BEGIN
# SELECT responsibility_id, application_id
# INTO ln_resp_id, ln_resp_appl_id
# FROM fnd_responsibility_tl
# WHERE responsibility_name = 'System Administrator'
# AND language = 'US';
# EXCEPTION
# WHEN OTHERS THEN
# dbms_output.put_line('Failed to get Responsibility and Application IDs.');
# END;
# INSERT INTO xxfnd_output_messages
# (output_message_id, source, message, creation_date, created_by,
# last_update_date, last_updated_by, last_update_login)
# VALUES
# (ln_output_message_id, 'SAS', 'No file found in SAS designated interface folder',
# sysdate, '${user_id}', sysdate, '${user_id}', '${user_id}');
# FND_GLOBAL.APPS_INITIALIZE(USER_ID => ln_user_id,
# RESP_ID => ln_resp_id,
# RESP_APPL_ID => ln_resp_appl_id);
# FOR REC IN C_RECIPIENT LOOP
# b_flag := fnd_request.add_notification (USER => rec.recipient);
# END LOOP;
# ln_request_id := fnd_request.submit_request(
# application => 'XXFND',
# program => 'XXFND_PRINT_OUTPUT_MESSAGES',
# description => 'HKP SAS Interface',
# start_time => NULL,
# sub_request => FALSE,
# argument1 => ln_output_message_id);
# dbms_output.put_line('ln_request_id = ' || ln_request_id);
# END;
# /
# exit
# ENDOFSQL
# Yan.
exit
fi
ttsresult_d=`grep '' $APPLPTMP/sia_interface_err_chk.txt`
if [ "${ttsresult_d:-0}" = 0 ]; then
echo ""
else
echo `cat $APPLPTMP/sia_interface_err_chk.txt`
#######################################################################################################
MYSTATUS=`sqlplus -s $1 <<!
SET HEADING FEEDBACK OFF PAGESIZE 0
declare
l_result boolean;
l_session_id number;
begin
fnd_global.INITIALIZE(l_session_id, null, null, null,null, -1, null, null, null, null, $4, null,null,null,null,null,null,-1);
l_result := fnd_concurrent.set_completion_status('ERROR','Review log file for details.');
commit;
end;
/
exit ;
!`
fi
#######################################################################################################
else
echo "+-------------------ERROR-------------------+"
echo "ERR-001:Interface file not found"
echo "+-------------------ERROR-------------------+"
MYSTATUS=`sqlplus -s $1 <<!
SET HEADING FEEDBACK OFF PAGESIZE 0
declare
l_result boolean;
l_session_id number;
begin
fnd_global.INITIALIZE(l_session_id, null, null, null,null, -1, null, null, null, null, $4, null,null,null,null,null,null,-1);
l_result := fnd_concurrent.set_completion_status('ERROR','Review log file for details.');
commit;
end;
/
exit ;
!`
fi