Tag Archives: Scripting

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

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!

Please like & share: