Notes

############# Wrapper for P6 error_etl_job_track_file
# PROCEDURE error_etl_job_track_file(etl_job_track_file_sk_in IN integer, update_count OUT integer);
error_etl_job_track_file()
{

v_etl_job_track_file_error1=$(sqlplus -s ${ORA_METADATA_CONNECTION} << EOF
set pagesize 0 feedback off heading off echo off
set serveroutput on
declare
number_updates integer := NULL;
begin
${METADATA_USER}.etl_job_track_pkg.error_etl_job_track_file(${ETL_JOB_TRACK_FILE_SK}, number_updates);
dbms_output.put_line(number_updates);
exception
when others then dbms_output.put_line(SQLERRM);
end;
/
EOF
)

if [[ ${v_etl_job_track_file_error1} =~ "ERROR" ]] || [[ ${v_etl_job_track_file_error1} =~ "ORA" ]];
then
echo "ORA-Error trying to update record in the MD_ETL_JOB_TRACK_FILE table for file ${ETL_JOB_TRACK_FILE_SK}!" >> ${LOG_FILE}
send_email "Failed in error_etl_job_track_file: ${ETL_JOB_TRACK_FILE_SK} " "ORA MBIS Data Load Status: ORA-Error trying to update record in the MD_ETL_JOB_TRACK_FILE table for file${ETL_JOB_TRACK_FILE_SK}!"
exit 1
else
echo "Updating a record in the MD_ETL_JOB_TRACK_FILE was successfully: file ${ETL_JOB_TRACK_FILE_SK} failed." >> ${LOG_FILE}
fi

}


############# Wrapper for P7-10 complete_etl_job_track_phase
# PROCEDURE complete_etl_job_track_phase(etl_job_track_phase_sk_in IN integer, update_count OUT integer);
# PROCEDURE complete_etl_job_track(etl_job_track_sk_in IN integer, update_count OUT integer);
complete_etl_job_track_phase()
{
v_etl_job_track_phase=$(sqlplus -s ${ORA_METADATA_CONNECTION} << EOF
set pagesize 0 feedback off heading off echo off
set serveroutput on
declare
number_updates integer := NULL;
begin
${METADATA_USER}.etl_job_track_pkg.complete_etl_job_track_phase(${ETL_JOB_TRACK_PHASE_SK}, number_updates);
dbms_output.put_line(number_updates);
exception
when others then dbms_output.put_line(SQLERRM);
end;
/
EOF
)
if [[ ${v_etl_job_track_phase} =~ "ERROR" ]] || [[ ${v_etl_job_track_phase} =~ "ORA" ]];
then
echo "ORA-Error trying to update record in the MD_ETL_JOB_TRACK_PHASE table for phase ${ETL_JOB_TRACK_PHASE_SK}!" >> ${LOG_FILE}
error_etl_job_track
send_email "Failed in complete_etl_job_track_phase: ${ETL_JOB_TRACK_PHASE_SK} " "ORA MBIS Data Load Status: ORA-Error trying to update record in the MD_ETL_JOB_TRACK_FILE table for phase ${ETL_JOB_TRACK_PHASE_SK}!"
exit 1
else
echo "Updating a record in the MD_ETL_JOB_TRACK_FILE was successful at loading phase ${ETL_JOB_TRACK_PHASE_SK}" >> ${LOG_FILE}
fi

}

############# Wrapper for P8 error_etl_job_track_phase
# PROCEDURE error_etl_job_track_phase(etl_job_track_phase_sk_in IN integer, update_count OUT integer);
error_etl_job_track_phase()
{
v_etl_job_track_phase_error=$(sqlplus -s ${ORA_METADATA_CONNECTION} << EOF
set pagesize 0 feedback off heading off echo off
set serveroutput on
declare
number_updates integer := NULL;
begin
${METADATA_USER}.etl_job_track_pkg.error_etl_job_track_phase(${ETL_JOB_TRACK_PHASE_SK}, number_updates);
dbms_output.put_line(number_updates);
exception
when others then dbms_output.put_line(SQLERRM);
end;
/
EOF
)

if [[ ${v_etl_job_track_phase_error} =~ "ERROR" ]] || [[ ${v_etl_job_track_phase_error} =~ "ORA" ]];
then
echo "ORA-Error trying to update record in the MD_ETL_JOB_TRACK_PHASE ${ETL_JOB_TRACK_PHASE_SK}" >> ${LOG_FILE}
send_email "Failed in error_etl_job_track_phase: ${ETL_JOB_TRACK_PHASE_SK} " "ORA MBIS Data Load Status: ORA-Error trying to update record in the MD_ETL_JOB_TRACK_PHASE for ${ETL_JOB_TRACK_PHASE_SK}!"
exit 1
else
echo "Updating a record in the MD_ETL_JOB_TRACK_PHASE successfully that ETL_JOB_TRACK_PHASE_SK: ${ETL_JOB_TRACK_PHASE_SK} is failed. " >> ${LOG_FILE}
fi

}




############# Wrapper for P9 complete_etl_job_track
# PROCEDURE complete_etl_job_track(etl_job_track_sk_in IN integer, update_count OUT integer);
complete_etl_job_track()
{
v_etl_job_track_file_success1=$(sqlplus -s ${ORA_METADATA_CONNECTION} << EOF
set pagesize 0 feedback off heading off echo off
set serveroutput on
declare
number_updates integer := NULL;
begin
${METADATA_USER}.etl_job_track_pkg.complete_etl_job_track(${ETL_JOB_TRACK_SK}, number_updates);
dbms_output.put_line(number_updates);
exception
when others then dbms_output.put_line(SQLERRM);
end;
/
EOF
)

if [[ ${v_etl_job_track_file_success1} =~ "ERROR" ]] || [[ ${v_etl_job_track_file_success1} =~ "ORA" ]];
then
echo "ORA-Error trying to update record in the MD_ETL_JOB_TRACK table for ${ETL_JOB_TRACK_SK}" >> ${LOG_FILE}
send_email "Failed in complete_etl_job_track: ${ETL_JOB_TRACK_SK}" "ORA MBIS Data Load Status: ORA-Error trying to update record in the MD_ETL_JOB_TRACK table for ${ETL_JOB_TRACK_SK}!"
exit 1
else
echo "Updating a record in the MD_ETL_JOB_TRACK was successful for ${ETL_JOB_TRACK_SK}" >> ${LOG_FILE}
fi

}


############# Wrapper for P10 error_etl_job_track
# PROCEDURE error_etl_job_track(etl_job_track_sk_in IN integer, update_count OUT integer)
error_etl_job_track()
{
v_etl_job_track_error=$(sqlplus -s ${ORA_METADATA_CONNECTION} << EOF
set pagesize 0 feedback off heading off echo off
set serveroutput on
declare
number_updates integer := NULL;
begin
${METADATA_USER}.etl_job_track_pkg.error_etl_job_track(${ETL_JOB_TRACK_SK}, number_updates);
dbms_output.put_line(number_updates);
exception
when others then dbms_output.put_line(SQLERRM);
end;
/
EOF
)
if [[ ${v_etl_job_track_error} =~ "ERROR" ]] || [[ ${v_etl_job_track_error} =~ "ORA" ]];
then
echo "ORA-Error trying to update record in the MD_ETL_JOB_TRACK table for ${ETL_JOB_TRACK_SK}" >> ${LOG_FILE}
send_email "Failed in error_etl_job_track: ${ETL_JOB_TRACK_SK} " "ORA MBIS Data Load Status: ORA-Error trying to update record in the MD_ETL_JOB_TRACK table for ${ETL_JOB_TRACK_SK}!"
exit 1
else
echo "Updating a record in the MD_ETL_JOB_TRACK was successfully that ETL_JOB_TRACK_SK: ${ETL_JOB_TRACK_SK} is failed. " >> ${LOG_FILE}
fi

}

############# Wrapper for P11 rollback_etl_job_track ${ETL_JOB_TRACK_ID}
# PROCEDURE rollback_etl_job_track(etl_job_track_sk_in IN integer, update_count OUT integer)
rollback_etl_job_track()
{
v_etl_job_track_rollback=$(sqlplus -s ${ORA_METADATA_CONNECTION} << EOF
set pagesize 0 feedback off heading off echo off
set serveroutput on
declare
number_updates integer := NULL;
begin
${METADATA_USER}.etl_job_track_pkg.rollback_etl_job_track(${ETL_JOB_TRACK_ID}, number_updates);
dbms_output.put_line(number_updates);
exception
when others then dbms_output.put_line(SQLERRM);
end;
/
EOF
)
if [[ ${v_etl_job_track_rollback} =~ "ERROR" ]] || [[ ${v_etl_job_track_rollback} =~ "ORA" ]];
then
echo "ORA-Error trying to update rollback_etl_job_track for ${ETL_JOB_TRACK_ID}=${v_etl_job_track_rollback}. rollback_etl_job_track can only be done for successfully loaded ones. " >> ${LOG_FILE}
send_email "Failed in rollback_etl_job_track for error_etl_job_track=${ETL_JOB_TRACK_ID} " "ORA MBIS Data rollback: ORA-Error trying to update record in the rollback_etl_job_track()!"
exit 1
else
echo "Roll back successfully for ETL_JOB_TRACK_ID: ${ETL_JOB_TRACK_ID}" >> ${LOG_FILE}
fi

}

############# Check SQL*Loader Log file for loading status
# check_nzloader_log_file ${return_value} ${nzloader_logfile}
check_nzloader_log_file()
{
rev=$1
nzloader_log=$2

# if [[ ${rev} =~ "successfully" ]] &&
if grep -q "number of bad records: 0" "${nzloader_log}"
then
# No error found in the log file
echo `date` " ${ETL_JOB_TRACK_FILE_SK} loaded to staging table successfully. " >> ${LOG_FILE}
########### Start update Metadata for successfully loading in staging.
create_etl_process_log "INFO" "${ETL_JOB_TRACK_FILE_SK} loaded successfully"
complete_etl_job_track_file
########### End update Metadata
else
# Errors found in the log file
echo `date` " ${FILE_PATH}/${DATEFILENAME}.csv.gz loaded to staging table failed with errors. Check the log file ${nzloader_log}. NZ returned ${rev} Exit!" >> ${LOG_FILE}
send_email "Failed on JOB_ID: ${ETL_JOB_TRACK_SK}, PHASE_ID: ${ETL_JOB_TRACK_PHASE_SK}, FILE_ID: ${ETL_JOB_TRACK_FILE_SK}"
"${FILE_PATH}/${DATEFILENAME}.csv.gz loaded to staging table failed with errors.
Check the log file ${nzloader_log}.
Exit this dataset loading!
"
#Clean up
cleanup ${SOURCEDATA} ${DAYORHOUR}

########### Start update Metadata for failed loading in staging
create_etl_process_log "ERROR" "Failed to load ${ETL_JOB_TRACK_FILE_SK}. ${FILE_PATH}/${DATEFILENAME}.csv.gz loaded to staging table failed with errors. Check the log file ${nzloader_log}."
error_etl_job_track_file
error_etl_job_track_phase
error_etl_job_track
########### End update Metadata for failed loading in staging
exit 1
fi
}


cleanup()
{
rm -f ctl*.*
rm -f *.csv
rm -f *.sql
if [ "${1}" = "curve" ]
then
echo "truncate table ${DB_OWNER}.MBIS_STG_${2}_AP_CURVE;
truncate table ${DB_OWNER}.MBIS_STG_${2}_AP_CURVE_STATS;
truncate table ${DB_OWNER}.MBIS_STG_${2}_AP_PRICING;
q" |
nzsql ${NZ_COMMON_CONNECTION}
else
echo "truncate table ${DB_OWNER}.MBIS_STG_DAILY_${1^^};
q " | nzsql ${NZ_COMMON_CONNECTION}
fi
}

# Sending email for the task status
# "send_email SUBJECT MSG"
send_email()
{
SUBJECT="${1}"
MSG="${2}"
if [ "${APP_EMAIL_ENABLED}" = "Y" ]
then
echo "Sending Email: [${APP_EMAIL} : ${SUBJECT} : ${MSG}]"
echo "${MSG}" | mailx -s "${SUBJECT}" -a "${LOG_FILE}" "${APP_EMAIL}"
echo "$?"
fi
}