SQL*Loader Control file

-- File Name         : xinvcnv01a.ctl
-- File Type         : SQL*Loader Control file.
-- RICEW Object id   : XX_INV_01
-- Description       : This SQL*Loader file is used to load  data
--                     from flat file to the staging table XX_INV_ITEMS_STG
-- Maintenance History:
--
-- Date         Version          Name             Remarks
-- -----------  ---------   --------------   --------------------------------------------------------------------------
-- 18-Dec-2014   1.0         Madhu     Draft Version.
--
--  -------------------------------------------------------------------------------------------------------------------

LOAD DATA

TRUNCATE

INTO TABLE XX_INV_ITEMS_STG
when REC_TYPE ='CRC'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( STATUS                  CONSTANT 'NW'
 ,REC_TYPE                POSITION(1) CHAR
 ,RECORD_COUNT            CHAR
 ,DESCRIPTION             CHAR
 ,LAST_UPDATE_DATE        SYSDATE
 ,LAST_UPDATED_BY         "fnd_global.user_id"
 ,CREATION_DATE           SYSDATE
 ,CREATED_BY              "fnd_global.user_id"
 ,LAST_UPDATE_LOGIN       "fnd_global.login_id"
)

INTO TABLE XX_INV_ITEMS_STG
when REC_TYPE ='ITM'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
   STATUS          CONSTANT 'NW'
 , REC_TYPE    POSITION(1) CHAR
 , ITEM_NUMBER             CHAR
 , DESCRIPTION             CHAR
 , ORGANIZATION_CODE       CHAR
 , PRIMARY_UOM_CODE        CHAR
 , TEMPLATE_NAME           CHAR
 , MATERIAL_COST           CHAR
 , MATERIAL_SUB_ELEM       CHAR                      
 , MATERIAL_OH_RATE        CHAR              
 , MATERIAL_OH_SUB_ELEM    CHAR
 , CATEGORY_SET_NAME       CHAR                
 , CATEGORY_NAME           CHAR
 , RECORD_ID               "XX_INV_ITEMS_STG_S.NEXTVAL"
 , LAST_UPDATE_DATE        "SYSDATE"
 , LAST_UPDATED_BY         "fnd_global.user_id"
 , CREATION_DATE           "SYSDATE"
 , CREATED_BY              "fnd_global.user_id"
 , LAST_UPDATE_LOGIN       "fnd_global.login_id"                    
)