SQL*Loader

-- File Name         : xbomcnv01a.ctl
-- File Type         : SQL*Loader Control file.
-- RICEW Object id   : XX_BOM_01
-- Description       : This SQL*Loader file is used to load  data
--                     from flat file to the staging table XX_BOM_IMPORT_STG
-- Maintenance History:
-- 
-- Date            Version     Name             Remarks
-- -----------     ---------   --------------   --------------------------------------------------------
-- 01-March-214   1.0         Madhu Dhare  Initial Version.
--
-- ===================================================================================================================

LOAD DATA

TRUNCATE

INTO TABLE XX_BOM_IMPORT_STG
when REC_TYPE ='CRC'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' 
TRAILING NULLCOLS
( STATUS                  CONSTANT 'NW'
 ,REC_TYPE                POSITION(1) CHAR
 ,RECORD_COUNT            CHAR
 ,RECORD_ID               "XX_BOM_IMPORT_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"
)

INTO TABLE XX_BOM_IMPORT_STG
when REC_TYPE = 'HDR'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' 
TRAILING NULLCOLS
(
   STATUS          CONSTANT 'NW'
 , REC_TYPE    POSITION(1)  CHAR
 , ORG_CODE          CHAR
 , ASSEMBLY_ITEM_NUMBER    CHAR    
 , REVISION                 CHAR
 , COMPONENT_ITEM_NUMBER    CHAR
 , COMPONENT_QUANTITY       CHAR
 , EFFECTIVITY_DATE         DATE
 , SUPPLY_SUBINVENTORY      CHAR
 , SUPPLY_LOCATOR_ID        CHAR
 , RECORD_ID                "XX_BOM_IMPORT_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"                     
)

INTO TABLE XX_BOM_IMPORT_STG
when REC_TYPE = 'DTL'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' 
TRAILING NULLCOLS
(
   STATUS          CONSTANT 'NW'
 , REC_TYPE    POSITION(1)  CHAR
 , ORG_CODE          CHAR
 , ASSEMBLY_ITEM_NUMBER    CHAR    
 , REVISION                 CHAR
 , COMPONENT_ITEM_NUMBER    CHAR
 , COMPONENT_QUANTITY       CHAR
 , EFFECTIVITY_DATE         DATE
 , SUPPLY_SUBINVENTORY      CHAR
 , SUPPLY_LOCATOR_ID        CHAR
 , RECORD_ID                "XX_BOM_IMPORT_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"                     
)