Tag Archives: SQL Loader

Oracle

Oracle E-Business Suite – Bill of Materials ATO Configurations Migration via SQL Loader

Leave a comment

In this case,  we are going to create scripts for migrating data from 11i Oracle EBS to version R12.  This process is including generating “meaningful” data with SQL , creation of custom staging table of bom.bom_ato_configurations , a bulk insert via SQL Loader using single control(.ctl) file and finally runing it in Linux. Let’s start ;

First of all we are creating custom staging table and indexes for ATO Configurations in R12 on custom schema  ;

CREATE TABLE XXTH.XXTH_BOM_ATO_CONFIGURATIONS
(
  config_item_id number, 
  organization_id number, 
  base_model_id number, 
  component_item_id number,  
  component_code varchar2(240 BYTE), 
  creation_date date,  
  component_quantity number, 
  last_referenced_date date, 
  cfm_routing_flag number, 
  organization_code varchar2(3 byte),
  config_item_code varchar2(150 byte),
  base_model_code varchar2(150 byte),
  component_item_code varchar2 (150 byte),
  component_code_name varchar2(2000 byte)
)
TABLESPACE APPS_TS_TX_DATA
PCTUSED    0
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING
/



CREATE INDEX XXTH.XXTH_BOM_ATO_CONF_NDX1 ON XXTH.XXTH_BOM_ATO_CONFIGURATIONS
(ORGANIZATION_CODE, CONFIG_ITEM_CODE, BASE_MODEL_CODE, COMPONENT_ITEM_CODE, COMPONENT_CODE_NAME)
LOGGING
TABLESPACE XXTH
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL
/



exit
/

Then we generate our more “meaningful” hierarchical material – item data associated with components via following SQL script;

select b.config_item_id, 
       b.organization_id, 
       b.base_model_id, 
       b.component_item_id, 
       b.component_code, 
       b.creation_date,  
       b.component_quantity, 
       b.last_referenced_date, 
       b.cfm_routing_flag,
       mp.organization_code,
       msib.segment1 as config_item_code,
       msib2.segment1 as base_model_code,
       msib3.segment1 as component_item_code,
       rtrim(x1.segment1||'-'||
             x2.segment1||'-'||
             x3.segment1 ,'--') as component_code_name 
  from bom.bom_ato_configurations b , 
       mtl_system_items_b msib ,
       mtl_system_items_b msib2, 
       mtl_system_items_b msib3,
       mtl_parameters mp ,
       mtl_system_items_b x1,
       mtl_system_items_b x2,
       mtl_system_items_b x3
 where b.organization_id = mp.organization_id 
   and mp.organization_id = msib.organization_id 
   and b.config_item_id = msib.inventory_item_id
   and mp.organization_id = msib2.organization_id 
   and b.base_model_id = msib2.inventory_item_id
   and mp.organization_id = msib3.organization_id 
   and b.component_item_id = msib3.inventory_item_id 
   and x1.inventory_item_id(+) = rtrim(ltrim( substr(b.component_code, 0,
                                    decode(instr (b.component_code,'-',1,1), 0,
                                       length(b.component_code),instr (b.component_code,'-',1,1))),'-') ,'-' )  
   and x1.organization_id(+)  = b.organization_id 
   and x2.inventory_item_id(+) =  rtrim(ltrim( substr(b.component_code,
                                    decode( instr(b.component_code,'-',1,1),0,1000, instr(b.component_code,'-',1,1)) ,
                                    decode(instr (b.component_code,'-',1,2),0,length(b.component_code)+1,
                                     instr(b.component_code,'-',1,2)) - instr (b.component_code,'-',1,1)),'-'))
   and x2.organization_id(+)   = b.organization_id 
   and x3.inventory_item_id(+) =  rtrim(ltrim( substr(b.component_code,
                                    decode(instr (b.component_code,'-',1,2),0,1000,instr (b.component_code,'-',1,2)),
                                    length(b.component_code)+1- instr (b.component_code,'-',1,2)),'-'))
   and x3.organization_id(+)  = b.organization_id

Now we need to export this data to SQL Loader control file , i use TOAD’s sqlloader extension in this case , it can directly write output results to single .ctl file . But you can also manually create your own control file by refencing a exported .csv sheet.

sql_loader_bom_toad

I use EBS Vision Database so item and component names don’t look like a material! but your in production DB should not look like this . This script also creates .bad and .dsc file in the same directory that you can check if there is any error . SQL Loader control file is that one  ;

LOAD DATA
INFILE *
BADFILE './BOM_ATO_CONFIGURATIONS.BAD'
DISCARDFILE './BOM_ATO_CONFIGURATIONS.DSC'
INSERT INTO TABLE XXTH.XXTH_BOM_ATO_CONFIGURATIONS
Fields terminated by ";" Optionally enclosed by '|'
(
  CONFIG_ITEM_ID NULLIF (CONFIG_ITEM_ID="NULL"),
  ORGANIZATION_ID NULLIF (ORGANIZATION_ID="NULL"),
  BASE_MODEL_ID NULLIF (BASE_MODEL_ID="NULL"),
  COMPONENT_ITEM_ID NULLIF (COMPONENT_ITEM_ID="NULL"),
  COMPONENT_CODE,
  CREATION_DATE DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (CREATION_DATE="NULL"),
  COMPONENT_QUANTITY NULLIF (COMPONENT_QUANTITY="NULL"),
  LAST_REFERENCED_DATE DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (LAST_REFERENCED_DATE="NULL"),
  CFM_ROUTING_FLAG NULLIF (CFM_ROUTING_FLAG="NULL"),
  ORGANIZATION_CODE,
  CONFIG_ITEM_CODE,
  BASE_MODEL_CODE,
  COMPONENT_ITEM_CODE,
  COMPONENT_CODE_NAME
)
BEGINDATA
4524;207;143;1492;|143-1490-1492|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|CM55243|;|CN97444-OC42556-CM55243|
4524;207;143;251;|143-299-251|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|CM08830|;|CN97444-OC29315-CM08830|
4524;207;143;197;|143-313-197|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|CM94043|;|CN97444-OC55449-CM94043|
4524;207;143;185;|143-297-185|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|CM41684|;|CN97444-OC68020-CM41684|
4524;207;143;217;|143-347-217|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|CM56560|;|CN97444-OC55437-CM56560|
4524;207;143;257;|143-347-257|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|CM49954|;|CN97444-OC55437-CM49954|
4524;207;143;219;|143-347-219|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|CM59311|;|CN97444-OC55437-CM59311|
4524;207;143;215;|143-347-215|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|CM54321|;|CN97444-OC55437-CM54321|
4524;207;143;1490;|143-1490|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|OC42556|;|CN97444-OC42556|
4524;207;143;299;|143-299|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|OC29315|;|CN97444-OC29315|
4524;207;143;313;|143-313|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|OC55449|;|CN97444-OC55449|
4524;207;143;297;|143-297|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|OC68020|;|CN97444-OC68020|
4524;207;143;347;|143-347|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|OC55437|;|CN97444-OC55437|
4524;207;143;143;|143|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|CN97444|;|CN97444|
221170;209;220028;220035;|220028-220035|;|10/23/2009 13:22:02|;1;|10/23/2009 13:22:02|;NULL;|M2|;|HFAT001-Computer-Controlled|;|HFAT001|;|HFOC006|;|HFAT001-HFOC006|
221170;209;220028;220061;|220028-220035-220061|;|10/23/2009 13:22:02|;1;|10/23/2009 13:22:02|;NULL;|M2|;|HFAT001-Computer-Controlled|;|HFAT001|;|HF55004|;|HFAT001-HFOC006-HF55004|
221170;209;220028;220028;|220028|;|10/23/2009 13:22:02|;1;|10/23/2009 13:22:02|;NULL;|M2|;|HFAT001-Computer-Controlled|;|HFAT001|;|HFAT001|;|HFAT001|
221168;209;220080;220030;|220080-220030|;|10/23/2009 13:22:02|;1;|10/23/2009 13:22:02|;NULL;|M2|;|HFAT002-375HP|;|HFAT002|;|HFOC001|;|HFAT002-HFOC001|
221168;209;220080;220037;|220080-220030-220037|;|10/23/2009 13:22:02|;1;|10/23/2009 13:22:02|;NULL;|M2|;|HFAT002-375HP|;|HFAT002|;|HF50001|;|HFAT002-HFOC001-HF50001|
221168;209;220080;220075;|220080-220075|;|10/23/2009 13:22:02|;1;|10/23/2009 13:22:02|;NULL;|M2|;|HFAT002-375HP|;|HFAT002|;|HFOC008|;|HFAT002-HFOC008|
221168;209;220080;220077;|220080-220075-220077|;|10/23/2009 13:22:02|;1;|10/23/2009 13:22:02|;NULL;|M2|;|HFAT002-375HP|;|HFAT002|;|HF57001|;|HFAT002-HFOC008-HF57001|
221168;209;220080;220080;|220080|;|10/23/2009 13:22:02|;1;|10/23/2009 13:22:02|;NULL;|M2|;|HFAT002-375HP|;|HFAT002|;|HFAT002|;|HFAT002|
10541;1884;7033;7113;|7033-7047-7113|;|07/31/2003 12:46:34|;1;|07/31/2003 12:46:34|;NULL;|W1|;|WDAT001*10540|;|WDAT001|;|WD50152|;|WDAT001-WDOC104-WD50152|
10541;1884;7033;7107;|7033-7045-7107|;|07/31/2003 12:46:34|;1;|07/31/2003 12:46:34|;NULL;|W1|;|WDAT001*10540|;|WDAT001|;|WD50100|;|WDAT001-WDOC102-WD50100|
10541;1884;7033;7099;|7033-7041-7099|;|07/31/2003 12:46:34|;1;|07/31/2003 12:46:34|;NULL;|W1|;|WDAT001*10540|;|WDAT001|;|WD50007|;|WDAT001-WDOC100-WD50007|
10541;1884;7033;7047;|7033-7047|;|07/31/2003 12:46:34|;1;|07/31/2003 12:46:34|;NULL;|W1|;|WDAT001*10540|;|WDAT001|;|WDOC104|;|WDAT001-WDOC104|
10541;1884;7033;7045;|7033-7045|;|07/31/2003 12:46:34|;1;|07/31/2003 12:46:34|;NULL;|W1|;|WDAT001*10540|;|WDAT001|;|WDOC102|;|WDAT001-WDOC102|
10541;1884;7033;7041;|7033-7041|;|07/31/2003 12:46:34|;1;|07/31/2003 12:46:34|;NULL;|W1|;|WDAT001*10540|;|WDAT001|;|WDOC100|;|WDAT001-WDOC100|
10541;1884;7033;10539;|7033-7039|;|07/31/2003 12:46:34|;1;|07/31/2003 12:46:34|;NULL;|W1|;|WDAT001*10540|;|WDAT001|;|WDAT004*10538|;|WDAT001-WDAT004|
10541;1884;7033;10537;|7033-7037|;|07/31/2003 12:46:34|;1;|07/31/2003 12:46:34|;NULL;|W1|;|WDAT001*10540|;|WDAT001|;|WDAT003*10536|;|WDAT001-WDAT003|
10541;1884;7033;10535;|7033-7035|;|07/31/2003 12:46:34|;2;|07/31/2003 12:46:34|;NULL;|W1|;|WDAT001*10540|;|WDAT001|;|WDAT002*10534|;|WDAT001-WDAT002|
10541;1884;7033;7033;|7033|;|07/31/2003 12:46:34|;1;|07/31/2003 12:46:34|;NULL;|W1|;|WDAT001*10540|;|WDAT001|;|WDAT001|;|WDAT001|
10539;1884;7039;7397;|7039-7071-7397|;|07/31/2003 12:46:33|;1;|07/31/2003 12:46:33|;NULL;|W1|;|WDAT004*10538|;|WDAT004|;|WD51301|;|WDAT004-WDOC114-WD51301|
10539;1884;7039;7389;|7039-7069-7389|;|07/31/2003 12:46:33|;1;|07/31/2003 12:46:33|;NULL;|W1|;|WDAT004*10538|;|WDAT004|;|WD51205|;|WDAT004-WDOC115-WD51205|
10539;1884;7039;7377;|7039-7067-7377|;|07/31/2003 12:46:33|;1;|07/31/2003 12:46:33|;NULL;|W1|;|WDAT004*10538|;|WDAT004|;|WD51111|;|WDAT004-WDOC113-WD51111|
10539;1884;7039;7327;|7039-7063-7327|;|07/31/2003 12:46:33|;1;|07/31/2003 12:46:33|;NULL;|W1|;|WDAT004*10538|;|WDAT004|;|WD50910|;|WDAT004-WDOC111-WD50910|
10539;1884;7039;7303;|7039-7061-7303|;|07/31/2003 12:46:33|;1;|07/31/2003 12:46:33|;NULL;|W1|;|WDAT004*10538|;|WDAT004|;|WD50810|;|WDAT004-WDOC116-WD50810|
10539;1884;7039;7237;|7039-7057-7237|;|07/31/2003 12:46:33|;1;|07/31/2003 12:46:33|;NULL;|W1|;|WDAT004*10538|;|WDAT004|;|WD50610|;|WDAT004-WDOC109-WD50610|
10539;1884;7039;7189;|7039-7053-7189|;|07/31/2003 12:46:33|;1;|07/31/2003 12:46:33|;NULL;|W1|;|WDAT004*10538|;|WDAT004|;|WD50410|;|WDAT004-WDOC107-WD50410|
10539;1884;7039;7129;|7039-7049-7129|;|07/31/2003 12:46:33|;1;|07/31/2003 12:46:33|;NULL;|W1|;|WDAT004*10538|;|WDAT004|;|WD50207|;|WDAT004-WDOC105-WD50207|
10539;1884;7039;7109;|7039-7047-7109|;|07/31/2003 12:46:33|;1;|07/31/2003 12:46:33|;NULL;|W1|;|WDAT004*10538|;|WDAT004|;|WD50150|;|WDAT004-WDOC104-WD50150|
10539;1884;7039;7107;|7039-7045-7107|;|07/31/2003 12:46:33|;1;|07/31/2003 12:46:33|;NULL;|W1|;|WDAT004*10538|;|WDAT004|;|WD50100|;|WDAT004-WDOC102-WD50100|
10539;1884;7039;7101;|7039-7041-7101|;|07/31/2003 12:46:33|;1;|07/31/2003 12:46:33|;NULL;|W1|;|WDAT004*10538|;|WDAT004|;|WD50008|;|WDAT004-WDOC100-WD50008|

Now , bom_ato_configurations.ctl file generated from 11i enviroment is ready to migrate with R12 . After deploying our .ctl file to database server , we use this shell script on Redhat Linux to run sqlloader , you should change the directories on this script with your deployment directory :

sqlldr   apps/$AppsPWD control=./files/ctl/bom_ato_configurations.ctl

Finally , we finished Bill of Materials ATO Configurations Migration via SQL Loader process . We have a “meaningful” ATO Configuration data in our custom XXTH.XXTH_BOM_ATO_CONFIGURATIONS staging  table that we can use in Reports and API’s in R12 . Enjoy!