# !/bin/ksh
destination_directory=${5}
file_source=${6}
userpass=apps/${APPSPW}@${ORACLE_INSTANCE} 
LFILE='us_test_datanew.out'
file_name=${LFILE}
echo " in file ${destination_directory}/${LFILE}"

# generate the ctl file based on the file type and execute SQLLoader to load file into staging table.

#for LFILE in 'ls ${destination_directory}/${file_name}.dat' 
#do
#        remove any  from input files - should not exist if file is transferred ftp-ascii but just incase
#       sed 's///' ${destination_directory}/${LFILE} > ${LFILE}.tmp
#       sed 's///' ${file_source}  > ${LFILE}.tmp
#       mv ${LFILE}.tmp ${LFILE}
        # Wrapback File
        echo "LOAD DATA" > ${LFILE}.ctl
        echo "infile '${destination_directory}/${LFILE}'"
        echo "INFILE '${destination_directory}/${LFILE}'" >> ${LFILE}.ctl
        echo "APPEND INTO TABLE gcca.GCCA_GERS_HDR" >> ${LFILE}.ctl
        echo "WHEN (01) = 'HDR'" >> ${LFILE}.ctl
        echo "fields terminated by '^' optionally enclosed by '"'" >> ${LFILE}.ctl
        echo "TRAILING NULLCOLS" >> ${LFILE}.ctl
        echo "(" >> ${LFILE}.ctl
        echo "  record_type          POSITION(1)CHAR TERMINATED BY '^'" >> ${LFILE}.ctl
        echo ", key_id               CHAR TERMINATED BY '^' "ltrim(rtrim(:key_id))"" >> ${LFILE}.ctl
        echo ", employee_id                     CHAR TERMINATED BY '^' "ltrim(rtrim(:employee_id))"" >> ${LFILE}.ctl
        echo ", last_name                       CHAR TERMINATED BY '^' "ltrim(rtrim(:last_name))"" >> ${LFILE}.ctl
        echo ", first_name                      CHAR TERMINATED BY '^' "ltrim(rtrim(:FIRST_name))"" >> ${LFILE}.ctl
        echo ", mi                              CHAR TERMINATED BY '^' "ltrim(rtrim(:MI))"" >> ${LFILE}.ctl
        echo ", total_payment_sign              CHAR TERMINATED BY '^' "ltrim(rtrim(:total_payment_sign))"" >> ${LFILE}.ctl
        echo ", total_payment                   CHAR TERMINATED BY '^' "ltrim(rtrim(:total_payment))"" >> ${LFILE}.ctl
        echo ", employee_payment_sign           CHAR TERMINATED BY '^' "ltrim(rtrim(:employee_payment_sign))"" >> ${LFILE}.ctl
        echo ", employee_payment                CHAR TERMINATED BY '^' "ltrim(rtrim(:employee_payment))"" >> ${LFILE}.ctl
        echo ", total_company_paid_sign         CHAR TERMINATED BY '^' "ltrim(rtrim(:total_company_paid_sign))"" >> ${LFILE}.ctl
        echo ", total_company_paid              CHAR TERMINATED BY '^' "ltrim(rtrim(:total_company_paid))"" >> ${LFILE}.ctl
        echo ", tax_amount_total_sign           CHAR TERMINATED BY '^' "ltrim(rtrim(:tax_amount_total_sign))"" >> ${LFILE}.ctl
        echo ", tax_amount_total_in_country     CHAR TERMINATED BY '^' "ltrim(rtrim(:tax_amount_total_in_country))"" >> ${LFILE}.ctl
        echo ", tax_amount_out_of_country_sign  CHAR TERMINATED BY '^' "ltrim(rtrim(:tax_amount_out_of_country_sign))"" >> ${LFILE}.ctl
        echo ", tax_amount_out_of_country       CHAR TERMINATED BY '^' "ltrim(rtrim(:tax_amount_out_of_country))"" >> ${LFILE}.ctl
        echo ", claim_date                      DATE  "YYYY/MM/DD" TERMINATED BY '^'" >> ${LFILE}.ctl
        echo ", CHART_OF_ACCTID                 CHAR TERMINATED BY '^' "ltrim(rtrim(:CHART_OF_ACCTID))"" >> ${LFILE}.ctl
        echo ", no_of_detail_records            CHAR TERMINATED BY '^' "ltrim(rtrim(:no_of_detail_records))"" >> ${LFILE}.ctl
        echo ", no_of_crd_records               CHAR TERMINATED BY '^' "ltrim(rtrim(:no_of_crd_records))"" >> ${LFILE}.ctl
        echo ", employee_email_address          CHAR TERMINATED BY '^' "ltrim(rtrim(:employee_email_address))"" >> ${LFILE}.ctl
        echo ", tbd                             CHAR TERMINATED BY '^' "ltrim(rtrim(:tbd))"" >> ${LFILE}.ctl
        echo ", ADJUSTED_AMOUNT                 CHAR TERMINATED BY '^' "ltrim(rtrim(:ADJUSTED_AMOUNT))"" >> ${LFILE}.ctl
        echo ", SOURCE                          '${file_source'" >> ${LFILE}.ctl
        echo ", Load_date                       "TRUNC(SYSDATE)"" >> ${LFILE}.ctl
        echo ")" >> ${LFILE}.ctl 
        echo "INTO TABLE gcca.GCCA_GERS_CREDIT_CARD" >> ${LFILE}.ctl
        echo "WHEN (01) = 'CRD'" >> ${LFILE}.ctl
        echo "(" >> ${LFILE}.ctl
        echo "  record_type           POSITION(1) CHAR TERMINATED BY '^'" >> ${LFILE}.ctl
        echo ", key_id                CHAR TERMINATED BY '^' "ltrim(rtrim(:key_id))"" >> ${LFILE}.ctl
        echo ", card_payment_sign     CHAR TERMINATED BY '^' "ltrim(rtrim(:card_payment_sign))"" >> ${LFILE}.ctl
        echo ", card_payment          CHAR TERMINATED BY '^' "ltrim(rtrim(:card_payment))"" >> ${LFILE}.ctl
        echo ", card_number           CHAR TERMINATED BY '^' "ltrim(rtrim(:card_number))"" >> ${LFILE}.ctl
        echo ", BILL_CURRENCY         CHAR TERMINATED BY '^' "ltrim(rtrim(:BILL_CURRENCY))"" >> ${LFILE}.ctl
        echo ", SOURCE                          '${file_source'" >> ${LFILE}.ctl
        echo ", Load_date                       "TRUNC(SYSDATE)"" >> ${LFILE}.ctl
        echo ")" >> ${LFILE}.ctl 
        echo "INTO TABLE gcca.GCCA_GERS_DTL" >> ${LFILE}.ctl
        echo "WHEN (01) = 'DTL'">> ${LFILE}.ctl
        echo "(" >> ${LFILE}.ctl
        echo "  record_type           POSITION(1) CHAR TERMINATED BY '^'" >> ${LFILE}.ctl
        echo ", key_id                CHAR TERMINATED BY '^' "ltrim(rtrim(:key_id))"" >> ${LFILE}.ctl
        echo ", payment_method           CHAR TERMINATED BY '^' "ltrim(rtrim(:payment_method))"" >> ${LFILE}.ctl
        echo ", chart_of_account         CHAR TERMINATED BY '^' "ltrim(rtrim(:chart_of_account))"" >> ${LFILE}.ctl
        echo ", sign_amount_allocated    CHAR TERMINATED BY '^' "ltrim(rtrim(:sign_amount_allocated))"" >> ${LFILE}.ctl
        echo ", amount_allocated         CHAR TERMINATED BY '^' "ltrim(rtrim(:amount_allocated))"" >> ${LFILE}.ctl
        echo ", expense_type             CHAR TERMINATED BY '^' "ltrim(rtrim(:expense_type))"" >> ${LFILE}.ctl
        echo ", expense_date             DATE  "YYYY/MM/DD" TERMINATED BY '^'" >> ${LFILE}.ctl
        echo ", expense_type_description CHAR TERMINATED BY '^' "ltrim(rtrim(:expense_type_description))"" >> ${LFILE}.ctl
        echo ", country                  CHAR TERMINATED BY '^' "ltrim(rtrim(:country))"" >> ${LFILE}.ctl
        echo ", tax_code_1               CHAR TERMINATED BY '^' "ltrim(rtrim(:tax_code_1))"" >> ${LFILE}.ctl
        echo ", tax_per_1                CHAR TERMINATED BY '^' "ltrim(rtrim(:tax_per_1))"" >> ${LFILE}.ctl
        echo ", tax_sign_1               CHAR TERMINATED BY '^' "ltrim(rtrim(:tax_sign_1))"" >> ${LFILE}.ctl
        echo ", tax_value_1              CHAR TERMINATED BY '^' "ltrim(rtrim(:tax_value_1))"" >> ${LFILE}.ctl
        echo ", tax_code_2               CHAR TERMINATED BY '^' "ltrim(rtrim(:tax_code_1))"" >> ${LFILE}.ctl
        echo ", tax_per_2                CHAR TERMINATED BY '^' "ltrim(rtrim(:tax_per_1))"" >> ${LFILE}.ctl
        echo ", tax_sign_2               CHAR TERMINATED BY '^' "ltrim(rtrim(:tax_sign_1))"" >> ${LFILE}.ctl
        echo ", tax_value_2              CHAR TERMINATED BY '^' "ltrim(rtrim(:tax_value_1))"" >> ${LFILE}.ctl
        echo ", tax_code_3               CHAR TERMINATED BY '^' "ltrim(rtrim(:tax_code_1))"" >> ${LFILE}.ctl
        echo ", tax_per_3                CHAR TERMINATED BY '^' "ltrim(rtrim(:tax_per_1))"" >> ${LFILE}.ctl
        echo ", tax_sign_3               CHAR TERMINATED BY '^' "ltrim(rtrim(:tax_sign_1))"" >> ${LFILE}.ctl
        echo ", tax_value_3              CHAR TERMINATED BY '^' "ltrim(rtrim(:tax_value_1))"" >> ${LFILE}.ctl
        echo ", tax_code_4               CHAR TERMINATED BY '^' "ltrim(rtrim(:tax_code_1))"" >> ${LFILE}.ctl
        echo ", tax_per_4                CHAR TERMINATED BY '^' "ltrim(rtrim(:tax_per_1))"" >> ${LFILE}.ctl
        echo ", tax_sign_4               CHAR TERMINATED BY '^' "ltrim(rtrim(:tax_sign_1))"" >> ${LFILE}.ctl
        echo ", tax_value_4              CHAR TERMINATED BY '^' "ltrim(rtrim(:tax_value_1))"" >> ${LFILE}.ctl
        echo ", card_number              CHAR TERMINATED BY '^' "ltrim(rtrim(:card_number))"" >> ${LFILE}.ctl
        echo ", bill_currency            CHAR TERMINATED BY '^' "ltrim(rtrim(:bill_currency))"" >> ${LFILE}.ctl
        echo ", filler1                  CHAR TERMINATED BY '^' "ltrim(rtrim(:filler1))"" >> ${LFILE}.ctl
        echo ", filler2                  CHAR TERMINATED BY '^' "ltrim(rtrim(:filler1))"" >> ${LFILE}.ctl
        echo ", filler3                  CHAR TERMINATED BY '^' "ltrim(rtrim(:filler1))"" >> ${LFILE}.ctl
        echo ", inv_dist_seq_num         CHAR TERMINATED BY '^' "GCCA.GCCA_GERS_DTL_SEQ.nextval"" >> ${LFILE}.ctl
        echo ", SOURCE                          '${file_source'" >> ${LFILE}.ctl
        echo ", Load_date                       "TRUNC(SYSDATE)"" >> ${LFILE}.ctl
        echo ")" >> ${LFILE}.ctl
        echo ")" >> ${LFILE}.ctl 
        echo "INTO TABLE gcca.GCCA_GERS_CREDIT_CARD" >> ${LFILE}.ctl
        echo "WHEN (01) = 'TRL'" >> ${LFILE}.ctl
        echo "(" >> ${LFILE}.ctl
        echo "  record_type           POSITION(1) CHAR TERMINATED BY '^'" >> ${LFILE}.ctl
        echo ", NUMBER_OFHDR_RECORDS  CHAR TERMINATED BY '^' "ltrim(rtrim(:NUMBER_OFHDR_RECORDS))"" >> ${LFILE}.ctl
        echo ", SOURCE                          '${file_source'" >> ${LFILE}.ctl
        echo ", Load_date                       "TRUNC(SYSDATE)"" >> ${LFILE}.ctl
        echo ")" >> ${LFILE}.ctl 
               
        chmod 777 ${LFILE}*

echo "after sqlloader built"
sqlldr userid=${userpass} \
          control=${LFILE}.ctl \
          data=${destination_directory}/${LFILE} \
          log=${LFILE}.log \
          bad=${LFILE}.bad
return_code=$?

# Check to see that SQL*Loader worked correctly.
#

if test $return_code -ne 0
then
  echo
  echo "There was a serious error that prevented your Data file from"
  echo "being loaded."
  echo
  exit $return_code
else
            echo
            echo "The file loaded successfully"
            echo
fi
#done