Tips and Tricks with SQL Loader

Following are some of the tips and tricks that can be used with SQL Loader

1) Load text for a column which is having more than 4000 bytes.
Use following syntax

LOAD DATA
APPEND INTO TABLE sv_test
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
  long_text_columns char(40000)
)

2) How to use oracle functions with SQL Loader

LOAD DATA
APPEND INTO TABLE sv_test
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
  long_text_columns char(40000)   --- first 40000 characters
, column1  "trim(:column1)"       --- Trims and loads
, column2  "replace(:column2,'\n',chr(10))"  --- replace \n with new line
, column3  DATE "DD-MON-YYYY"   --- defining date style
, column4  "upper(:column4)"      --- changing to upper case
, column5  "lower(:column5)"      --- changing to lower case
, column6  constant "FIXED"      --- assigning a constant value
, column7  "sv_sequence.nextval"  --- defaulting a value from sequence
)

Shell Script program ....

userpass=`echo $1 | cut -f2 -d '"'`
user=`echo $userpass | cut -f1 -d '/'`
passwd=`echo $userpass | cut -f2 -d '/'`
reqid=`echo $1 | cut -f2 -d " " | cut -f2 -d "="`
usrid=`echo $1 | cut -f4 -d " " | cut -f2 -d "="`
file=`echo $1 | cut -f9 -d " " | cut -f2 -d '"'`
p_outfile_path="$APPLCSF/$APPLOUT/o$reqid.out"
##
echo file1=$file
echo reqid=$reqid
echo usrid=$usrid
echo p_outfile_path=$p_outfile_path
################################
get_directory_name()
{
sqlplus -s $userpass <<EXIT
set serverout on size 100000;
set pages 0
set feed off
set verify off
declare
l_err   varchar2(100);
l_name  varchar2(100);
Begin
mas_pa_bs_reverse_bill.get_dir_name('$file_lookup',l_name,l_err);
if l_err is not null then
   dbms_output.put_line('TRAN_ERROR:'||l_err);
elsif l_name is null then
   dbms_output.put_line('Unable to get $file_type name');
else
   dbms_output.put_line(l_name);
end if;
end;
/
EXIT
}
################################
file_type='inbound directory'
file_lookup='ASSET_IN'
#echo "\nFind $file_type name :lookup=$file_lookup"
inbound_dir=`get_directory_name`
if  echo $inbound_dir |grep "TRAN_ERROR-" > /dev/null
then
  echo "\nError ... $inbound_dir "
  exit 1
fi
################################
file_type='inbound err directory'
file_lookup='ASSET_ERR'
inbound_err_dir=`get_directory_name`
if  echo $inbound_err_dir |grep "TRAN_ERROR-" > /dev/null
then
  echo "\nError ... $inbound_err_dir "
  exit 1
fi
#################################
file_type='inbound archive directory'
file_lookup='ASSET_ARCH'
inbound_arch_dir=`get_directory_name`
if  echo $inbound_arch_dir |grep "TRAN_ERROR-" > /dev/null
then
  echo "\nError ... $inbound_arch_dir "
  exit 1
fi
################################
file_type='inbound temp directory'
file_lookup='ASSET_TEMP'
inbound_temp_dir=`get_directory_name`
if  echo $inbound_temp_dir |grep "TRAN_ERROR-" > /dev/null
then
  echo "\nError ... $inbound_temp_dir "
  exit 1
fi
##############################
echo "Deleting files"

rm -f $inbound_temp_dir/sqlload_1 $inbound_temp_dir/sqlload_2
rm -f $MAS_TOP/bin/MAS_ASSET_BULK_UPDATE1.ctl
##

echo "Replace Strings reqid, usrid in Ctl file"

######################
##cp "$MAS_TOP/bin/MAS_ASSET_BULK_UPDATE.ctl" "$MAS_TOP/bin/MAS_ASSET_BULK_UPDATE1.ctl"
##chmod 777 "$MAS_TOP/bin/MAS_ASSET_BULK_UPDATE1.ctl"
##########################
####  Replace Strings reqid, usrid in Ctl file
##echo "Process: Replace Strings usrid, data_file_name with correct values in Ctl file"
##sed -e "s/v_file_name/$file/g" -e "s/v_user_id/$usrid/g" "$MAS_TOP/bin/MAS_ASSET_BULK_UPDATE.ctl" > "$MAS_TOP/bin/MAS_ASSET_BULK_UPDATE1.ctl"

##if [ $? -ne 0 ]
##then
##  {
 ## echo "Error: while replacing usrid with $usrid in Ctl file"
 ## exit 0
 ## }
##fi
inbound_dir=$inbound_dir

echo "inbound_dir $inbound_dir"
echo "inbound_arch_dir $inbound_arch_dir"
echo "inbound_temp_dir $inbound_temp_dir"
##
x=`date +"%m%d%y%H%M%S"`
##for file in `ls $inbound_dir`
##do
echo "\n directory looking: $inbound_dir"
echo "\n file name $file"
    echo "\n Processing file    : $file "
    file_name=`echo $file | cut -d"." -f1`
    #echo "file_name $file_name"

    if [ ! -f $inbound_dir/$file ]; then
        echo "\n $file not found $inbound_dir/$file"
        else
        echo "\n Datafile $file found "
        dos2unix $inbound_dir/$file t.txt
        mv t.txt $inbound_dir/$file
        echo "\n Running Sql loader for file $file"
        sqlldr $userpass control=$MAS_TOP/bin/MAS_ASSET_BULK_UPDATE.ctl data=$inbound_dir/$file log=$inbound_temp_dir/$file_name.log.$x bad=$inbound_temp_dir/$file_name.bad discard=$inbound_temp_dir/$file_name.dis  > $inbound_temp_dir/sqlload_1 > $inbound_temp_dir/sqlload_2
        echo "\n sqlload done, check for any errors"
        if grep -i error $inbound_temp_dir/sqlload_2 > /dev/null
        then
             echo "\n*****************************************************************************"
             echo "\nError in Loading the file to the staging table for the file $file "
             echo "\nError->$sqlload"
             echo "\n*****************************************************************************"
        elif [ -f $inbound_temp_dir/$file_name.err ]
        then
            echo "\n*****************************************************************************"
            echo "\n *******  Error while Loading to the staging table for the file $file, please see the error file ******* "
            echo "\n*****************************************************************************"           echo "\nMoving the file $file to the error directory "
            mv $inbound_temp_dir/$file_name.err $inbound_err_dir/$file_name.err.$x
        elif [ -f $inbound_temp_dir/$file_name.bad ]
        then
            echo "\n******************************************************************************"
            echo "\n *******  Error while Loading to the staging table for the file $file, please see bad file ******* "
            echo "\n*******************************************************************************"
            echo "\nMoving the file $file and bad file to the error directory "
            mv $inbound_temp_dir/$file_name.bad $inbound_err_dir/$file_name.bad.$x
       
        elif [ -f $inbound_temp_dir/$file_name.dis ]
        then
            echo "\n*******************************************************************************"
            echo "\n *******  Error while Loading to the staging table for the file $file, please see discard file ******* "
            echo "\n*******************************************************************************"
            echo "\nMoving the file $file and disracrd file to the error directory "
            mv $inbound_temp_dir/$file_name.dis $inbound_err_dir/$file_name.dis.$x
        fi
            echo "Successfully loaded records from datafile $file"
            cat $inbound_temp_dir/$file_name.log.$x |grep "Total logical"
            ####
            cat $inbound_temp_dir/$file_name.log.$x |grep "Total logical" >> $p_outfile_path
            ####
 
            echo "\n No bad file found for datafile $file"
            echo "\n No Discard file found for datafile $file"           
            echo "\n\n"
            echo "\nMoving data file $file to the Archive Directory"
                    mv $inbound_dir/$file $inbound_arch_dir/$file.$x

        fi
##done
#       
echo "Request : $reqid Completed">$inbound_temp_dir/$reqid.log
echo "\n Transaction Load Done">>$inbound_temp_dir/$reqid.log
echo "\n Please check attached Log for Details !!!">>$inbound_temp_dir/$reqid.log
reqidlog=`echo l$reqid.req`
echo "\n Request log is $reqidlog"

Control file in multiple insert function.......

OPTIONS ( SKIP 1, ERRORS=1000000, DIRECT=FALSE)
LOAD DATA
--INFILE --"PATH"
--TRUNCATE
APPEND
INTO TABLE CGINV_MATL_RECPT
WHEN Transaction_type = 'REC'
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(  
Item                   position(1) "REPLACE(LTRIM(RTRIM(:Item)),chr(34),NULL)",
LOT_NUMBER        "REPLACE(LTRIM(RTRIM(:LOT_NUMBER)),chr(34),NULL)",
ISSUED_TO        "REPLACE(LTRIM(RTRIM(:ISSUED_TO)),chr(34),NULL)",
RECEIVED_BY        "REPLACE(LTRIM(RTRIM(:RECEIVED_BY)),chr(34),NULL)",
WO_NUMBER        "REPLACE(LTRIM(RTRIM(:WO_NUMBER)),chr(34),NULL)",
TO_CREW_NUMBER        "REPLACE(LTRIM(RTRIM(:TO_CREW_NUMBER)),chr(34),NULL)",
FROM_SUBINVENTORY    "REPLACE(LTRIM(RTRIM(:FROM_SUBINVENTORY)),chr(34),NULL)",
QTY            "REPLACE(LTRIM(RTRIM(:QTY)),chr(34),NULL)",
PO_NUMBER        "REPLACE(LTRIM(RTRIM(:PO_NUMBER)),chr(34),NULL)",
RMA_NUMBER        "REPLACE(LTRIM(RTRIM(:RMA_NUMBER)),chr(34),NULL)" ,
TRANSACTION_DATE    "TO_DATE(REPLACE(LTRIM(RTRIM(:TRANSACTION_DATE)),chr(34),NULL),'DD-MON-RR')",
CREW_NUMBER        "REPLACE(LTRIM(RTRIM(:CREW_NUMBER)),chr(34),NULL)",
FROM_WO_NUMBER        "REPLACE(LTRIM(RTRIM(:FROM_WO_NUMBER)),chr(34),NULL)",
TRANSACTION_TYPE    "REPLACE(LTRIM(RTRIM(:TRANSACTION_TYPE)),chr(34),NULL)" ,
SUBINVENTORY        "REPLACE(Substr(:SUBINVENTORY,1,0),chr(34),NULL)",
INVENTORY_ORG        "REPLACE(LTRIM(RTRIM(:INVENTORY_ORG)),chr(34),NULL)" ,
TIME_STAMP        "REPLACE(LTRIM(RTRIM(:TIME_STAMP)),chr(34),NULL)" ,   
Reason            "REPLACE(LTRIM(RTRIM(:REASON)),chr(34),NULL)",
PROCESS_FLAG            CONSTANT "N" ,
created_by             "fnd_global.USER_ID",
creation_date           SYSDATE ,
last_update_date        SYSDATE,
last_update_login    "fnd_global.login_id",          
last_updated_by        "fnd_global.USER_ID",
PO_RECPT_STG_ID        "CGINV_MATL_RECPT_S.NextVal"
)

INTO TABLE CGINV_INV_TRANS
WHEN Transaction_type = 'SISSUE'
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(  
Item                   position(1) "REPLACE(LTRIM(RTRIM(:Item)),chr(34),NULL)",
LOT_NUM        "REPLACE(LTRIM(RTRIM(:LOT_NUM)),chr(34),NULL)",
Issued_to        "REPLACE(LTRIM(RTRIM(:Issued_to)),chr(34),NULL)",
received_by        "REPLACE(LTRIM(RTRIM(:received_by)),chr(34),NULL)",
to_wo_num        "REPLACE(LTRIM(RTRIM(:to_wo_num)),chr(34),NULL)",
to_crew_number        "REPLACE(LTRIM(RTRIM(:to_crew_number)),chr(34),NULL)",
FROM_SUBINVENTORY    "REPLACE(LTRIM(RTRIM(:FROM_SUBINVENTORY)),chr(34),NULL)",
TRANSACTION_QTY        "REPLACE(LTRIM(RTRIM(:TRANSACTION_QTY)),chr(34),NULL)",
PO_NUMBER        "REPLACE(LTRIM(RTRIM(:PO_NUMBER)),chr(34),NULL)",               
RMA_NUMBER        "REPLACE(LTRIM(RTRIM(:RMA_NUMBER)),chr(34),NULL)" ,   
TRANSACTION_DATE    "TO_DATE(REPLACE(LTRIM(RTRIM(:TRANSACTION_DATE)),chr(34),NULL),'DD-MON-RR')" ,
from_crew_NUMber    "REPLACE(LTRIM(RTRIM(:from_crew_NUMber)),chr(34),NULL)" ,
FROM_wo_NUM    "REPLACE(LTRIM(RTRIM(:FROM_wo_NUM)),chr(34),NULL)",
Transaction_type    "REPLACE(LTRIM(RTRIM(:Transaction_type)),chr(34),NULL)" ,
Transfer_SUBINVENTORY        "REPLACE(LTRIM(RTRIM(:Transfer_SUBINVENTORY)),chr(34),NULL)" ,
ORG            "REPLACE(LTRIM(RTRIM(:ORG)),chr(34),NULL)" ,
TIME_STAMP        "REPLACE(LTRIM(RTRIM(:TIME_STAMP)),chr(34),NULL)" ,
Reason            "REPLACE(LTRIM(RTRIM(:Reason)),chr(34),NULL)",
PROJECT_NUM        "REPLACE(LTRIM(RTRIM(:PROJECT_NUM)),chr(34),NULL)",
TRANSFER_ORG            "REPLACE(LTRIM(RTRIM(:TRANSFER_ORG)),chr(34),NULL)",
PROCESS_FLAG            CONSTANT "N" ,
created_by             "fnd_global.USER_ID",
creation_date           SYSDATE ,
last_update_date        SYSDATE,
last_update_login    "fnd_global.login_id",          
last_updated_by        "fnd_global.USER_ID",
Transaction_ID         "CGINV_INV_TRANS_S.NextVal" 
)

INTO TABLE CGINV_INV_TRANS
WHEN Transaction_type = 'TRNS'
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(  
Item                   position(1) "REPLACE(LTRIM(RTRIM(:Item)),chr(34),NULL)",
LOT_NUM        "REPLACE(LTRIM(RTRIM(:LOT_NUM)),chr(34),NULL)",
Issued_to        "REPLACE(LTRIM(RTRIM(:Issued_to)),chr(34),NULL)",
received_by        "REPLACE(LTRIM(RTRIM(:received_by)),chr(34),NULL)",
to_wo_num        "REPLACE(LTRIM(RTRIM(:to_wo_num)),chr(34),NULL)",
to_crew_number        "REPLACE(LTRIM(RTRIM(:to_crew_number)),chr(34),NULL)",
FROM_SUBINVENTORY    "REPLACE(LTRIM(RTRIM(:FROM_SUBINVENTORY)),chr(34),NULL)",
TRANSACTION_QTY        "REPLACE(LTRIM(RTRIM(:TRANSACTION_QTY)),chr(34),NULL)",
PO_NUMBER        "REPLACE(LTRIM(RTRIM(:PO_NUMBER)),chr(34),NULL)",               
RMA_NUMBER        "REPLACE(LTRIM(RTRIM(:RMA_NUMBER)),chr(34),NULL)" ,   
TRANSACTION_DATE    "TO_DATE(REPLACE(LTRIM(RTRIM(:TRANSACTION_DATE)),chr(34),NULL),'DD-MON-RR')" ,
from_crew_NUMber    "REPLACE(LTRIM(RTRIM(:from_crew_NUMber)),chr(34),NULL)" ,
FROM_wo_NUM    "REPLACE(LTRIM(RTRIM(:FROM_wo_NUM)),chr(34),NULL)",
Transaction_type    "REPLACE(LTRIM(RTRIM(:Transaction_type)),chr(34),NULL)" ,
Transfer_SUBINVENTORY        "REPLACE(LTRIM(RTRIM(:Transfer_SUBINVENTORY)),chr(34),NULL)" ,
ORG            "REPLACE(LTRIM(RTRIM(:ORG)),chr(34),NULL)" ,
TIME_STAMP        "REPLACE(LTRIM(RTRIM(:TIME_STAMP)),chr(34),NULL)" ,
Reason            "REPLACE(LTRIM(RTRIM(:Reason)),chr(34),NULL)",
PROJECT_NUM        "REPLACE(LTRIM(RTRIM(:PROJECT_NUM)),chr(34),NULL)",
TRANSFER_ORG            "REPLACE(LTRIM(RTRIM(:TRANSFER_ORG)),chr(34),NULL)",
PROCESS_FLAG            CONSTANT "N" ,
created_by             "fnd_global.USER_ID",
creation_date           SYSDATE ,
last_update_date        SYSDATE,
last_update_login    "fnd_global.login_id",          
last_updated_by        "fnd_global.USER_ID",
Transaction_ID         "CGINV_INV_TRANS_S.NextVal" 
)

INTO TABLE CGINV_INV_TRANS
WHEN Transaction_type = 'SRETN'
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(  
Item                   position(1) "REPLACE(LTRIM(RTRIM(:Item)),chr(34),NULL)",
LOT_NUM        "REPLACE(LTRIM(RTRIM(:LOT_NUM)),chr(34),NULL)",
Issued_to        "REPLACE(LTRIM(RTRIM(:Issued_to)),chr(34),NULL)",
received_by        "REPLACE(LTRIM(RTRIM(:received_by)),chr(34),NULL)",
to_wo_num        "REPLACE(LTRIM(RTRIM(:to_wo_num)),chr(34),NULL)",
to_crew_number        "REPLACE(LTRIM(RTRIM(:to_crew_number)),chr(34),NULL)",
FROM_SUBINVENTORY    "REPLACE(LTRIM(RTRIM(:FROM_SUBINVENTORY)),chr(34),NULL)",
TRANSACTION_QTY        "REPLACE(LTRIM(RTRIM(:TRANSACTION_QTY)),chr(34),NULL)",
PO_NUMBER        "REPLACE(LTRIM(RTRIM(:PO_NUMBER)),chr(34),NULL)",               
RMA_NUMBER        "REPLACE(LTRIM(RTRIM(:RMA_NUMBER)),chr(34),NULL)" ,   
TRANSACTION_DATE    "TO_DATE(REPLACE(LTRIM(RTRIM(:TRANSACTION_DATE)),chr(34),NULL),'DD-MON-RR')" ,
from_crew_NUMber    "REPLACE(LTRIM(RTRIM(:from_crew_NUMber)),chr(34),NULL)" ,
FROM_wo_NUM    "REPLACE(LTRIM(RTRIM(:FROM_wo_NUM)),chr(34),NULL)",
Transaction_type    "REPLACE(LTRIM(RTRIM(:Transaction_type)),chr(34),NULL)" ,
Transfer_SUBINVENTORY        "REPLACE(LTRIM(RTRIM(:Transfer_SUBINVENTORY)),chr(34),NULL)" ,
ORG            "REPLACE(LTRIM(RTRIM(:ORG)),chr(34),NULL)" ,
TIME_STAMP        "REPLACE(LTRIM(RTRIM(:TIME_STAMP)),chr(34),NULL)" ,
Reason            "REPLACE(LTRIM(RTRIM(:Reason)),chr(34),NULL)",
PROJECT_NUM        "REPLACE(LTRIM(RTRIM(:PROJECT_NUM)),chr(34),NULL)",
TRANSFER_ORG            "REPLACE(LTRIM(RTRIM(:TRANSFER_ORG)),chr(34),NULL)",
PROCESS_FLAG            CONSTANT "N" ,
created_by             "fnd_global.USER_ID",
creation_date           SYSDATE ,
last_update_date        SYSDATE,
last_update_login    "fnd_global.login_id",          
last_updated_by        "fnd_global.USER_ID",
Transaction_ID         "CGINV_INV_TRANS_S.NextVal" 
)

INTO TABLE CGINV_INV_TRANS
WHEN Transaction_type = 'DEPLT'
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(  
Item                   position(1) "REPLACE(LTRIM(RTRIM(:Item)),chr(34),NULL)",
LOT_NUM                "REPLACE(LTRIM(RTRIM(:LOT_NUM)),chr(34),NULL)",
Issued_to        "REPLACE(LTRIM(RTRIM(:Issued_to)),chr(34),NULL)",
received_by        "REPLACE(LTRIM(RTRIM(:received_by)),chr(34),NULL)",
to_wo_num        "REPLACE(LTRIM(RTRIM(:to_wo_num)),chr(34),NULL)",
to_crew_number        "REPLACE(LTRIM(RTRIM(:to_crew_number)),chr(34),NULL)",
FROM_SUBINVENTORY    "REPLACE(LTRIM(RTRIM(:FROM_SUBINVENTORY)),chr(34),NULL)",
TRANSACTION_QTY        "REPLACE(LTRIM(RTRIM(:TRANSACTION_QTY)),chr(34),NULL)",
PO_NUMBER        "REPLACE(LTRIM(RTRIM(:PO_NUMBER)),chr(34),NULL)",               
RMA_NUMBER        "REPLACE(LTRIM(RTRIM(:RMA_NUMBER)),chr(34),NULL)" ,   
TRANSACTION_DATE    "TO_DATE(REPLACE(LTRIM(RTRIM(:TRANSACTION_DATE)),chr(34),NULL),'DD-MON-RR')" ,
from_crew_NUMber    "REPLACE(LTRIM(RTRIM(:from_crew_NUMber)),chr(34),NULL)" ,
FROM_wo_NUM    "REPLACE(LTRIM(RTRIM(:FROM_wo_NUM)),chr(34),NULL)",
Transaction_type    "REPLACE(LTRIM(RTRIM(:Transaction_type)),chr(34),NULL)" ,
Transfer_SUBINVENTORY        "REPLACE(LTRIM(RTRIM(:Transfer_SUBINVENTORY)),chr(34),NULL)" ,
ORG            "REPLACE(LTRIM(RTRIM(:ORG)),chr(34),NULL)" ,
TIME_STAMP        "REPLACE(LTRIM(RTRIM(:TIME_STAMP)),chr(34),NULL)" ,
Reason            "REPLACE(LTRIM(RTRIM(:Reason)),chr(34),NULL)",
PROJECT_NUM        "REPLACE(LTRIM(RTRIM(:PROJECT_NUM)),chr(34),NULL)",
TRANSFER_ORG            "REPLACE(LTRIM(RTRIM(:TRANSFER_ORG)),chr(34),NULL)",
PROCESS_FLAG            CONSTANT "N" ,
created_by             "fnd_global.USER_ID",
creation_date           SYSDATE ,
last_update_date        SYSDATE,
last_update_login    "fnd_global.login_id",          
last_updated_by        "fnd_global.USER_ID",
Transaction_ID         "CGINV_INV_TRANS_S.NextVal" 
)

INTO TABLE CGINV_INV_TRANS
WHEN Transaction_type = 'INTERORG'
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(  
Item                   position(1) "REPLACE(LTRIM(RTRIM(:Item)),chr(34),NULL)",
LOT_NUM                "REPLACE(LTRIM(RTRIM(:LOT_NUM)),chr(34),NULL)",
Issued_to        "REPLACE(LTRIM(RTRIM(:Issued_to)),chr(34),NULL)",
received_by        "REPLACE(LTRIM(RTRIM(:received_by)),chr(34),NULL)",
to_wo_num        "REPLACE(LTRIM(RTRIM(:to_wo_num)),chr(34),NULL)",
to_crew_number        "REPLACE(LTRIM(RTRIM(:to_crew_number)),chr(34),NULL)",
FROM_SUBINVENTORY    "REPLACE(LTRIM(RTRIM(:FROM_SUBINVENTORY)),chr(34),NULL)",
TRANSACTION_QTY        "REPLACE(LTRIM(RTRIM(:TRANSACTION_QTY)),chr(34),NULL)",
PO_NUMBER        "REPLACE(LTRIM(RTRIM(:PO_NUMBER)),chr(34),NULL)",               
RMA_NUMBER        "REPLACE(LTRIM(RTRIM(:RMA_NUMBER)),chr(34),NULL)" ,   
TRANSACTION_DATE    "TO_DATE(REPLACE(LTRIM(RTRIM(:TRANSACTION_DATE)),chr(34),NULL),'DD-MON-RR')" ,
from_crew_NUMber    "REPLACE(LTRIM(RTRIM(:from_crew_NUMber)),chr(34),NULL)" ,
FROM_wo_NUM    "REPLACE(LTRIM(RTRIM(:FROM_wo_NUM)),chr(34),NULL)",
Transaction_type    "REPLACE(LTRIM(RTRIM(:Transaction_type)),chr(34),NULL)" ,
Transfer_SUBINVENTORY        "REPLACE(LTRIM(RTRIM(:Transfer_SUBINVENTORY)),chr(34),NULL)" ,
ORG            "REPLACE(LTRIM(RTRIM(:ORG)),chr(34),NULL)" ,
TIME_STAMP        "REPLACE(LTRIM(RTRIM(:TIME_STAMP)),chr(34),NULL)" ,
Reason            "REPLACE(LTRIM(RTRIM(:Reason)),chr(34),NULL)",
PROJECT_NUM        "REPLACE(LTRIM(RTRIM(:PROJECT_NUM)),chr(34),NULL)",
TRANSFER_ORG            "REPLACE(REPLACE(LTRIM(RTRIM(:TRANSFER_ORG)),chr(34),NULL),chr(13),NULL)",
PROCESS_FLAG            CONSTANT "N" ,
created_by             "fnd_global.USER_ID",
creation_date           SYSDATE ,
last_update_date        SYSDATE,
last_update_login    "fnd_global.login_id",          
last_updated_by        "fnd_global.USER_ID",
Transaction_ID         "CGINV_INV_TRANS_S.NextVal"
)

INTO TABLE CGINV_ONHAND_STG
WHEN Transaction_type = 'OHCONV'
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(  
Item                   position(1) "REPLACE(LTRIM(RTRIM(:Item)),chr(34),NULL)",
lot_number        "REPLACE(LTRIM(RTRIM(:lot_number)),chr(34),NULL)",
Issued_to        "REPLACE(LTRIM(RTRIM(:Issued_to)),chr(34),NULL)",
received_by        "REPLACE(LTRIM(RTRIM(:received_by)),chr(34),NULL)",
wo_number        "REPLACE(LTRIM(RTRIM(:wo_number)),chr(34),NULL)",
to_crew_number        "REPLACE(LTRIM(RTRIM(:to_crew_number)),chr(34),NULL)",
FROM_SUBINVENTORY    "REPLACE(LTRIM(RTRIM(:FROM_SUBINVENTORY)),chr(34),NULL)",
transaction_quantity    "REPLACE(LTRIM(RTRIM(:transaction_quantity)),chr(34),NULL)",
PO_NUMBER        "REPLACE(LTRIM(RTRIM(:PO_NUMBER)),chr(34),NULL)",               
RMA_NUMBER        "REPLACE(LTRIM(RTRIM(:RMA_NUMBER)),chr(34),NULL)" ,   
transaction_date    "TO_DATE(REPLACE(LTRIM(RTRIM(:transaction_date)),chr(34),NULL),'DD-MON-RR')" ,
crew_number             "REPLACE(LTRIM(RTRIM(:crew_number)),chr(34),NULL)",
From_work_order_number    "REPLACE(LTRIM(RTRIM(:From_work_order_number)),chr(34),NULL)",
Transaction_type    "REPLACE(LTRIM(RTRIM(:Transaction_type)),chr(34),NULL)" ,
SUBINVENTORY            "REPLACE(LTRIM(RTRIM(:SUBINVENTORY)),chr(34),NULL)" ,
inventory_org        "REPLACE(LTRIM(RTRIM(:inventory_org)),chr(34),NULL)" ,
TIME_STAMP        "REPLACE(LTRIM(RTRIM(:TIME_STAMP)),chr(34),NULL)" ,
Reason            "REPLACE(LTRIM(RTRIM(:Reason)),chr(34),NULL)",
PROCESS_FLAG            CONSTANT "N" ,
Transaction_ID        "CGINV_INV_TRANS_S.NextVal",
created_by              "fnd_global.USER_ID",
creation_date           SYSDATE ,
last_update_date        SYSDATE,
last_update_login       "fnd_global.login_id",          
last_updated_by         "fnd_global.USER_ID"
)

Payroll Process Contorl file ..

OPTIONS (SKIP=1)
LOAD DATA
INFILE '/oracle/DEVNEW/apps/apps_st/appl/gl/12.0.0/bin/PAYROLL1.csv' 
BADFILE '/oracle/DEVNEW/apps/apps_st/appl/gl/12.0.0/bin/PAYROLL1.bad'
DISCARDFILE '/oracle/DEVNEW/apps/apps_st/appl/gl/12.0.0/bin/PAYROLL1.dsc'
APPEND
INTO TABLE gl.GL_interface
fields terminated by ',' optionally enclosed by '"'
TRAILING NULLCOLS
    (STATUS,
     LEDGER_ID,
     ACCOUNTING_DATE date "DD-MM-YY",
     CURRENCY_CODE,
     CURRENCY_CONVERSION_RATE,
     DATE_CREATED date "DD-MM-YY" ,
     CREATED_BY,
     ACTUAL_FLAG,
     USER_JE_CATEGORY_NAME,
     USER_JE_SOURCE_NAME,
     USER_CURRENCY_CONVERSION_TYPE,
     SEGMENT1,
     SEGMENT2 "DECODE (:SEGMENT2,'0','000',:SEGMENT2)",
     SEGMENT8 "DECODE (:SEGMENT8,'0','000000',:SEGMENT8)",
     SEGMENT4 "DECODE (:SEGMENT4,'0','0000',:SEGMENT4)",
     SEGMENT5 "DECODE (:SEGMENT5,'0','00',:SEGMENT5)",
     SEGMENT6 "DECODE (:SEGMENT6,'0','0000',:SEGMENT6)",
     SEGMENT7 "DECODE (:SEGMENT7,'0','0000',:SEGMENT7)",
     ENTERED_DR,
     ENTERED_CR,
     REFERENCE1,
     REFERENCE4,
     REFERENCE5,
     REFERENCE10)

Item Child Load ctl file

OPTIONS (SKIP=1)
 LOAD DATA
 INFILE *
 APPEND INTO TABLE XXMTZ_INV_SYSTEM_ITEMS_STG
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 TRAILING NULLCOLS
 (
  ORGANIZATION_CODE           "REPLACE(REPLACE(LTRIM(RTRIM(:ORGANIZATION_CODE)),chr(34),NULL),chr(13),NULL)"
 ,TRANSACTION_TYPE            "REPLACE(REPLACE(LTRIM(RTRIM(:TRANSACTION_TYPE)),chr(34),NULL),chr(13),NULL)"
 ,SEGMENT1                    "REPLACE(REPLACE(LTRIM(RTRIM(:SEGMENT1)),chr(34),NULL),chr(13),NULL)"                              
 ,DESCRIPTION                 "REPLACE(REPLACE(LTRIM(RTRIM(:DESCRIPTION)),chr(34),NULL),chr(13),NULL)"
 ,PRIMARY_UOM_CODE            "TRIM(:PRIMARY_UOM_CODE)"
 ,INVENTORY_ITEM_STATUS_CODE  "TRIM(:INVENTORY_ITEM_STATUS_CODE)"
 ,TEMPLATE_NAME               "TRIM(:TEMPLATE_NAME)"
 ,SERIAL_CONTROL              "TRIM(:SERIAL_CONTROL)"
 ,LIST_PRICE                  "TO_NUMBER(REPLACE(REPLACE(LTRIM(RTRIM(:LIST_PRICE)),chr(34),NULL),chr(13),NULL))"
 ,USE_APPROVED_SUPPLIER_FLAG  "REPLACE(REPLACE(LTRIM(RTRIM(:USE_APPROVED_SUPPLIER_FLAG)),chr(34),NULL),chr(13),NULL)"
 ,INSPECTION_REQUIRED_FLAG    "REPLACE(REPLACE(LTRIM(RTRIM(:INSPECTION_REQUIRED_FLAG)),chr(34),NULL),chr(13),NULL)"
 ,PURCHASING_ITEM           "REPLACE(REPLACE(LTRIM(RTRIM(:PURCHASING_ITEM)),chr(34),NULL),chr(13),NULL)"
 ,COST_OF_SALES_ACCOUNT       "TRIM(:COST_OF_SALES_ACCOUNT)"
 ,SALES_ACCOUNT               "TRIM(:SALES_ACCOUNT)"
 ,EXPENSE_ACCOUNT             "TRIM(:EXPENSE_ACCOUNT)"
 ,RECEIVING_ROUTING_ID        "TRIM(:RECEIVING_ROUTING_ID)"
 ,LOT_CONTROL_CODE            "TRIM(:LOT_CONTROL_CODE)"
 ,BUYER_ID                    "TRIM(:BUYER_ID)"
 ,INVENTORY_PLANNING_CODE     "TRIM(:INVENTORY_PLANNING_CODE)"
 ,MAX_MINMAX_QUANTITY         "TRIM(:MAX_MINMAX_QUANTITY)"
 ,MIN_MINMAX_QUANTITY         "TRIM(:MIN_MINMAX_QUANTITY)"
 ,MINIMUM_ORDER_QUANTITY      "TRIM(:MINIMUM_ORDER_QUANTITY)"
 ,MAXIMUM_ORDER_QUANTITY      "TRIM(:MAXIMUM_ORDER_QUANTITY)"
 ,FIXED_LOT_MULTIPLIER        "TRIM(:FIXED_LOT_MULTIPLIER)"
 ,PREPROCESSING_LEAD_TIME     "TRIM(:PREPROCESSING_LEAD_TIME)"
 ,FULL_LEAD_TIME              "TRIM(:FULL_LEAD_TIME)"
 ,POSTPROCESSING_LEAD_TIME    "TRIM(:POSTPROCESSING_LEAD_TIME)"
 ,ATTRIBUTE1                  "TRIM(:ATTRIBUTE1)"
 ,ATTRIBUTE2                  "TRIM(:ATTRIBUTE2)"
 ,ATTRIBUTE3                  "TRIM(:ATTRIBUTE3)"
 ,ATTRIBUTE4                  "TRIM(:ATTRIBUTE4)"
 ,ATTRIBUTE5                  "TRIM(:ATTRIBUTE5)"
 ,ATTRIBUTE6                  "TRIM(:ATTRIBUTE6)"
 ,ATTRIBUTE7                  "TRIM(:ATTRIBUTE7)"
 ,ATTRIBUTE8                  "TRIM(:ATTRIBUTE8)"
 ,ATTRIBUTE9                  "TRIM(:ATTRIBUTE9)"
 ,ATTRIBUTE10                 "TRIM(:ATTRIBUTE10)"
 ,ATTRIBUTE11                 "TRIM(:ATTRIBUTE11)"
 ,ATTRIBUTE12                 "TRIM(:ATTRIBUTE12)"
 ,ATTRIBUTE13                 "TRIM(:ATTRIBUTE13)"
 ,ATTRIBUTE14                 "TRIM(:ATTRIBUTE14)"
 ,ATTRIBUTE15                 "TRIM(:ATTRIBUTE15)"
 ,CREATED_BY                  "FND_GLOBAL.USER_ID" 
 ,CREATION_DATE               "TRUNC (SYSDATE)"
 ,LAST_UPDATED_BY             "FND_GLOBAL.USER_ID"
 ,LAST_UPDATE_DATE            "TRUNC (SYSDATE)"
 ,LAST_UPDATE_LOGIN           "FND_GLOBAL.LOGIN_ID"
 ,REQUEST_ID                  "FND_GLOBAL.CONC_REQUEST_ID"
 ,RECORD_ID                   "XXMTZ_INV_SYSTEM_ITEMS_S.nextval"
 ,RECORD_STATUS                CONSTANT 'N'
 )

ITEM_MASTER_LOAD

OPTIONS (SKIP=1)
 LOAD DATA
 INFILE *
 APPEND INTO TABLE xxmtz_inv_system_items_stg
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 TRAILING NULLCOLS
 (
  ORGANIZATION_CODE           "REPLACE(REPLACE(LTRIM(RTRIM(:ORGANIZATION_CODE)),chr(34),NULL),chr(13),NULL)"                   
 ,TRANSACTION_TYPE            "REPLACE(REPLACE(LTRIM(RTRIM(:TRANSACTION_TYPE)),chr(34),NULL),chr(13),NULL)"
 ,SEGMENT1                    "REPLACE(REPLACE(LTRIM(RTRIM(:SEGMENT1)),chr(34),NULL),chr(13),NULL)"                              
 ,DESCRIPTION                 "REPLACE(REPLACE(LTRIM(RTRIM(:DESCRIPTION)),chr(34),NULL),chr(13),NULL)"                                                            
 ,PRIMARY_UOM_CODE            "TRIM(:PRIMARY_UOM_CODE)"
 ,INVENTORY_ITEM_STATUS_CODE  "TRIM(:INVENTORY_ITEM_STATUS_CODE)"
 ,TEMPLATE_NAME               "TRIM(:TEMPLATE_NAME)"
 ,SERIAL_CONTROL              "TRIM(:SERIAL_CONTROL)"
 ,LIST_PRICE                  "TO_NUMBER(REPLACE(REPLACE(LTRIM(RTRIM(:LIST_PRICE)),chr(34),NULL),chr(13),NULL))"
 ,USE_APPROVED_SUPPLIER_FLAG  "REPLACE(REPLACE(LTRIM(RTRIM(:USE_APPROVED_SUPPLIER_FLAG)),chr(34),NULL),chr(13),NULL)"
 ,INSPECTION_REQUIRED_FLAG    "REPLACE(REPLACE(LTRIM(RTRIM(:INSPECTION_REQUIRED_FLAG)),chr(34),NULL),chr(13),NULL)"
 ,PURCHASING_ITEM           "REPLACE(REPLACE(LTRIM(RTRIM(:PURCHASING_ITEM)),chr(34),NULL),chr(13),NULL)"
 ,PROCESS_FLAG                "TRIM(:PROCESS_FLAG)"
 ,UNIT_HEIGHT                 "TRIM(:UNIT_HEIGHT)"
 ,UNIT_WIDTH                  "TRIM(:UNIT_WIDTH)"
 ,UNIT_LENGTH                 "TRIM(:UNIT_LENGTH)"
 ,DIMENSION_UOM_CODE          "TRIM(:DIMENSION_UOM_CODE)" 
 ,UNIT_WEIGHT                 "TRIM(:UNIT_WEIGHT)"
 ,WEIGHT_UOM_CODE             "TRIM(:WEIGHT_UOM_CODE)"
 ,LONG_DESCRIPTION          "REPLACE(REPLACE(LTRIM(RTRIM(:LONG_DESCRIPTION)),chr(34),NULL),chr(13),NULL)"
 --,ATTRIBUTE1                "TRIM(:ATTRIBUTE1)"
 ,CREATED_BY                  "FND_GLOBAL.USER_ID" 
 ,CREATION_DATE               "TRUNC (SYSDATE)"
 ,LAST_UPDATED_BY             "FND_GLOBAL.USER_ID"
 ,LAST_UPDATE_DATE            "TRUNC (SYSDATE)"
 ,LAST_UPDATE_LOGIN           "FND_GLOBAL.LOGIN_ID"
 ,REQUEST_ID                  "FND_GLOBAL.CONC_REQUEST_ID"
 ,RECORD_ID                   "XXMTZ_INV_SYSTEM_ITEMS_S.nextval"
 ,RECORD_STATUS                CONSTANT 'N'
 )

Payroll Process Contorl File

OPTIONS (SKIP=1)
LOAD DATA
INFILE '/oracle/DEVNEW/apps/apps_st/appl/gl/12.0.0/bin/PAYROLL1.csv'  
BADFILE '/oracle/DEVNEW/apps/apps_st/appl/gl/12.0.0/bin/PAYROLL1.bad'
DISCARDFILE '/oracle/DEVNEW/apps/apps_st/appl/gl/12.0.0/bin/PAYROLL1.dsc'
APPEND
INTO TABLE gl.GL_interface
fields terminated by ',' optionally enclosed by '"'
TRAILING NULLCOLS
    (STATUS,
     LEDGER_ID,
     ACCOUNTING_DATE date "DD-MM-YY",
     CURRENCY_CODE,
     CURRENCY_CONVERSION_RATE,
     DATE_CREATED date "DD-MM-YY" ,
     CREATED_BY,
     ACTUAL_FLAG,
     USER_JE_CATEGORY_NAME,
     USER_JE_SOURCE_NAME,
     USER_CURRENCY_CONVERSION_TYPE,
     SEGMENT1,
     SEGMENT2 "DECODE (:SEGMENT2,'0','000',:SEGMENT2)",
     SEGMENT8 "DECODE (:SEGMENT8,'0','000000',:SEGMENT8)",
     SEGMENT4 "DECODE (:SEGMENT4,'0','0000',:SEGMENT4)",
     SEGMENT5 "DECODE (:SEGMENT5,'0','00',:SEGMENT5)",
     SEGMENT6 "DECODE (:SEGMENT6,'0','0000',:SEGMENT6)",
     SEGMENT7 "DECODE (:SEGMENT7,'0','0000',:SEGMENT7)",
     ENTERED_DR,
     ENTERED_CR,
     REFERENCE1,
     REFERENCE4,
     REFERENCE5,
     REFERENCE10)