###################################################################################
# Shell script. to load invoice conversion ASCII file data into staging table      #
#                                                                                 #
#   History:                                                                      #
#   Initial Coding By : Hari Seri                                                 #
###################################################################################

START_DATE=`date '+%d/%m/%y %H:%M:%S'`
echo "Start time for loading invoices: $START_DATE"

USER_PASSWD="$1/$2@$3"
RUN_DATE=`date '+%m%d%y_%H%M%S'`

# this stucture for XXON USER
CTL_FILE=$XXON_TOP/invoices-RUN_DATE.ctl
LOG_FILE=$XXON_TOP/invoices-RUN_DATE.log
BAD_FILE=$XXON_TOP/invoices-RUN_DATE.bad
DSC_FILE=$XXON_TOP/invoices-RUN_DATE.dsc
DATA_FILE=$XXON_TOP/testinv.txt

# this stucture for CONV XXON USER
#CTL_FILE=/u002/app/xxon/invoices-$RUN_DATE.ctl
#LOG_FILE=/u002/app/xxon/invoices-$RUN_DATE.log
#BAD_FILE=/u002/app/xxon/invoices-$RUN_DATE.bad
#DSC_FILE=/u002/app/xxon/invoices-$RUN_DATE.dsc
#DATA_FILE=/u002/app/xxon/testinv.txt

# Create a new control file
cat > ${CTL_FILE} << !                      
LOAD DATA INFILE '${DATA_FILE}'
APPEND INTO TABLE xxon_ar_invoice_iface
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
TRX_NUMBER    "ltrim(rtrim(:TRX_NUMBER))",
 CUST_TRX_TYPE_NAME   "ltrim(rtrim(:CUST_TRX_TYPE_NAME))",
 TRX_DATE      "ltrim(rtrim(TO_DATE(:TRX_DATE,'YYYYMMDD')))",
 orig_system_batch_name  "ltrim(rtrim(:orig_system_batch_name))",
 DESCRIPTION    "ltrim(rtrim(:DESCRIPTION))",
 TERM_NAME   "ltrim(rtrim(:TERM_NAME))",
 BATCH_SOURCE_NAME  "ltrim(rtrim(:BATCH_SOURCE_NAME))",
 ORIG_SYSTEM_BILL_CUSTOMER_REF  "ltrim(rtrim(:ORIG_SYSTEM_BILL_CUSTOMER_REF))",
 ORIG_SYSTEM_BILL_ADDRESS_REF "ltrim(rtrim(:ORIG_SYSTEM_BILL_ADDRESS_REF))",
 ORIG_SYSTEM_SHIP_CUSTOMER_REF  "ltrim(rtrim(:ORIG_SYSTEM_SHIP_CUSTOMER_REF))",
 ORIG_SYSTEM_SHIP_ADDRESS_REF   "ltrim(rtrim(:ORIG_SYSTEM_SHIP_ADDRESS_REF))",
 LINE_TYPE    "ltrim(rtrim(:LINE_TYPE))",
 MTL_SYSTEM_ITEMS_SEG1  "ltrim(rtrim(:MTL_SYSTEM_ITEMS_SEG1))",
 QUANTITY_ORDERED  "ltrim(rtrim(:QUANTITY_ORDERED))",
 QUANTITY   "ltrim(rtrim(:QUANTITY))",
 UOM_CODE   "ltrim(rtrim(:UOM_CODE))",
 UNIT_SELLING_PRICE       "ltrim(rtrim(:UNIT_SELLING_PRICE))",
 UNIT_STANDARD_PRICE  "ltrim(rtrim(:UNIT_STANDARD_PRICE))",
 CURRENCY_CODE      "ltrim(rtrim(:CURRENCY_CODE))",
 CONVERSION_TYPE    "ltrim(rtrim(:CONVERSION_TYPE))",
 CONVERSION_RATE    "ltrim(rtrim(:CONVERSION_RATE))",
 CONVERSION_DATE    "ltrim(rtrim(TO_DATE(:CONVERSION_DATE,'YYYYMMDD')))",
 SHIP_DATE_ACTUAL  "ltrim(rtrim(TO_DATE(:SHIP_DATE_ACTUAL,'YYYYMMDD')))",
 SHIP_VIA   "ltrim(rtrim(:SHIP_VIA))",
 PRIMARY_SALESREP_NAME    "ltrim(rtrim(:PRIMARY_SALESREP_NAME))",
 SALES_ORDER        "ltrim(rtrim(:SALES_ORDER))",
 SALES_ORDER_LINE      "ltrim(rtrim(:SALES_ORDER_LINE))",
 SALES_ORDER_DATE  "ltrim(rtrim(:SALES_ORDER_DATE))",
 PURCHASE_ORDER     "ltrim(rtrim(:PURCHASE_ORDER))",
 PURCHASE_ORDER_DATE  "ltrim(rtrim(:PURCHASE_ORDER_DATE))",
 GL_DATE    "ltrim(rtrim(TO_DATE(:GL_DATE,'YYYYMMDD')))",
 ORG_NAME     "ltrim(rtrim(:ORG_NAME))",
 ORG_ID    "ltrim(rtrim(:ORG_ID))",
 SET_OF_BOOKS_ID    "ltrim(rtrim(:SET_OF_BOOKS_ID))",
 INTERFACE_LINE_CONTEXT  "ltrim(rtrim(:INTERFACE_LINE_CONTEXT))",
 INTERFACE_LINE_ATTRIBUTE1      "ltrim(rtrim(:INTERFACE_LINE_ATTRIBUTE1))",
 INTERFACE_LINE_ATTRIBUTE2      "ltrim(rtrim(:INTERFACE_LINE_ATTRIBUTE2))",
 INTERFACE_LINE_ATTRIBUTE3      "ltrim(rtrim(:INTERFACE_LINE_ATTRIBUTE3))",
 INTERFACE_LINE_ATTRIBUTE4      "ltrim(rtrim(:INTERFACE_LINE_ATTRIBUTE4))",
 INTERFACE_LINE_ATTRIBUTE5      "ltrim(rtrim(:INTERFACE_LINE_ATTRIBUTE5))",
 INTERFACE_LINE_ATTRIBUTE6      "ltrim(rtrim(:INTERFACE_LINE_ATTRIBUTE6))",
 INTERFACE_LINE_ATTRIBUTE7      "ltrim(rtrim(:INTERFACE_LINE_ATTRIBUTE7))",
 INTERFACE_LINE_ATTRIBUTE8      "ltrim(rtrim(:INTERFACE_LINE_ATTRIBUTE8))",
 INTERFACE_LINE_ATTRIBUTE9      "ltrim(rtrim(:INTERFACE_LINE_ATTRIBUTE9))",
 INTERFACE_LINE_ATTRIBUTE10     "ltrim(rtrim(:INTERFACE_LINE_ATTRIBUTE10))",
 INTERFACE_LINE_ATTRIBUTE11     "ltrim(rtrim(:INTERFACE_LINE_ATTRIBUTE11))",
 INTERFACE_LINE_ATTRIBUTE12     "ltrim(rtrim(:INTERFACE_LINE_ATTRIBUTE12))",
 INTERFACE_LINE_ATTRIBUTE13 "ltrim(rtrim(:INTERFACE_LINE_ATTRIBUTE13))",
 INTERFACE_LINE_ATTRIBUTE14 "ltrim(rtrim(:INTERFACE_LINE_ATTRIBUTE14))",
 INTERFACE_LINE_ATTRIBUTE15 "ltrim(rtrim(:INTERFACE_LINE_ATTRIBUTE15))",
 AMOUNT    "ltrim(rtrim(:AMOUNT))",
 ATTRIBUTE1        "ltrim(rtrim(:ATTRIBUTE1))",
 ATTRIBUTE2        "ltrim(rtrim(:ATTRIBUTE2))",
 ATTRIBUTE3        "ltrim(rtrim(:ATTRIBUTE3))",
 ATTRIBUTE4        "ltrim(rtrim(:ATTRIBUTE4))",
 ATTRIBUTE5        "ltrim(rtrim(:ATTRIBUTE5))",
 ATTRIBUTE6        "ltrim(rtrim(:ATTRIBUTE6))",
 ATTRIBUTE7        "ltrim(rtrim(:ATTRIBUTE7))",
 ATTRIBUTE8        "ltrim(rtrim(:ATTRIBUTE8))",
 ATTRIBUTE9        "ltrim(rtrim(:ATTRIBUTE9))",
 ATTRIBUTE10       "ltrim(rtrim(:ATTRIBUTE10))",
 ATTRIBUTE11       "ltrim(rtrim(:ATTRIBUTE11))",
 ATTRIBUTE12       "ltrim(rtrim(:ATTRIBUTE12))",
 ATTRIBUTE13   "ltrim(rtrim(:ATTRIBUTE13))",
 ATTRIBUTE14   "ltrim(rtrim(:ATTRIBUTE14))",
 ATTRIBUTE15     "ltrim(rtrim(:ATTRIBUTE15))",
 HEADER_ATTRIBUTE1  "ltrim(rtrim(:HEADER_ATTRIBUTE1))",    
 HEADER_ATTRIBUTE2  "ltrim(rtrim(:HEADER_ATTRIBUTE2))",    
 HEADER_ATTRIBUTE3  "ltrim(rtrim(:HEADER_ATTRIBUTE3))",    
 HEADER_ATTRIBUTE4  "ltrim(rtrim(:HEADER_ATTRIBUTE4))",    
 HEADER_ATTRIBUTE5  "ltrim(rtrim(:HEADER_ATTRIBUTE5))",    
 HEADER_ATTRIBUTE6  "ltrim(rtrim(:HEADER_ATTRIBUTE6))",    
 HEADER_ATTRIBUTE7  "ltrim(rtrim(:HEADER_ATTRIBUTE7))"
 )
!

sqlldr $USER_PASSWD control=$CTL_FILE data=$DATA_FILE log=$LOG_FILE bad=$BAD_FILE discard=$DSC_FILE BINDSIZE=9000000 ROWS=1500 errors=35000 parallel=TRUE

END_DATE=`date '+%d/%m/%y %H:%M:%S'`
echo "Start time for loading Invoices: $START_DATE"
echo "  End Time for loading Invoices: $END_DATE"