BOM Routings Data Conversion Sample Script

Dear Readers

Sorry for not having posted anything for a few days, was buried under some work so got time off today to write another post

In this post we will see a sample script for BOM Routing conversion.

The script is as below and is pretty simple and self-explanatory. I hope this helps you

INSERT INTO bom_op_routings_interface

(

   assembly_item_id,

   process_revision,

   process_flag,

   transaction_type,

   routing_type,

   organization_id

)

values

(

   83435,      --inventory item id of the assembly item for which routing is to be made

   null,

   1,          --1 (Pending), 3 (Assign/Validation Failed), 4 (Import Failed) , 7 (Import Succeeded).

   'CREATE',

   1,          --1) manufacturing, 2) engineering

   201

);

INSERT INTO bom_op_sequences_interface

(

   assembly_item_id,

   operation_seq_num,

   department_id,

   department_code,

   process_flag,

   transaction_type,

   organization_id,

   effectivity_date

)

values

(

   83435,      --inventory item id of the assembly item for which routing is to be made

   1,          --first sequence in routing

   1,          --Department ID from BOM_DEPARTMENTS_V

   'DEP1',     --Department code from BOM_DEPARTMENTS_V

   1,

   'CREATE',

   201,

   Trunc(SYSDATE)

);

INSERT INTO bom_op_resources_interface

(

   resource_seq_num,

   resource_id,

   resource_code,

   usage_rate_or_amount,

   assigned_units,

   assembly_item_id,

   operation_seq_num,

   process_flag,

   transaction_type,

   effectivity_date,

   organization_id ,

   schedule_flag,

   schedule_seq_num

)

values

(

   1,             --first resource needed in above OP sequence

   2001,          --Resource ID from BOM_RESOURCES_V

   'ELECTRICAL',  --Resource Code from BOM_RESOURCES_V

   1,

   1,

   83435,         --inventory item id of the assembly item for which routing is to be made

   1,             --Operation sequence (inserted above) for which this resource is needed

   1,

   'CREATE',

   Trunc(SYSDATE),

   201,

   2,

   NULL

);

insert into bom_op_sequences_interface

(

   assembly_item_id,

   operation_seq_num,

   department_id,

   department_code,

   process_flag,

   transaction_type,

   organization_id,

   effectivity_date

)

values

(

   83435,            --inventory item id of the assembly item for which routing is to be made 

   2,                --second sequence in routing                                              

   2,                --Department ID from BOM_DEPARTMENTS_V                                   

   'DEP2',           --Department code from BOM_DEPARTMENTS_V                                  

   1,

   'CREATE',

   201,

   Trunc(SYSDATE)

);

INSERT INTO bom_op_resources_interface

(

   resource_seq_num,

   resource_id,

   resource_code,

   usage_rate_or_amount,

   assigned_units,

   assembly_item_id,

   operation_seq_num,

   process_flag,

   transaction_type,

   effectivity_date,

   organization_id ,

   schedule_flag,

   schedule_seq_num

)

values

(

   1,                   --first resource needed in above OP sequence                           

   2006,                --Resource ID from BOM_RESOURCES_V                                     

   'SKILLED',           --Resource Code from BOM_RESOURCES_V                                   

   3,                                                                                     

   1,                                                                                 

   83435,               --inventory item id of the assembly item for which routing is to be made

   2,                   --Operation sequence (inserted above) for which this resource is needed

   1,

   'CREATE',

   Trunc(SYSDATE),

   201,

   2,

   NULL

);

Cheers
A