Item Cost with Material and Material Overhead Elements - Data Conversion

Dear Reader

In this post, we will see how we can convert the data for item costs (with the material and Material overhead elements)

Below is a sample script of the data which needs to be inserted into the interface tables

INSERT INTO cst_item_costs_interface

(

   inventory_item_id,

   organization_id,

   cost_type_id,

   inventory_asset_flag,

   item_cost,

   process_flag,

   transaction_type

)

values

(

   :inventory_item_id,

   :organization_id,

   :cost_type_id, --derive Pending cost type ID from cst_cost_types table

   1,

   :material_cost,

   1,

   'CREATE'

);

INSERT INTO cst_item_cst_dtls_interface

(

   inventory_item_id,

   organization_id,

   cost_type_id,

   level_type,

   operation_seq_num,

   item_cost,

   usage_rate_or_amount,

   cost_element_id,

   process_flag,

   transaction_type

)

values

(

   :inventory_item_id,

   :organization_id,

   :cost_type_id, --derive Pending cost type ID from cst_cost_types table

   1,

   1,

   :material_cost,

   :material_cost,

   1, --element ID = 1 for Material element of the cost

   1,

   'CREATE'

);

INSERT INTO cst_item_cst_dtls_interface

(

   inventory_item_id,

   organization_id,

   cost_type_id,

   level_type,

   operation_seq_num,

   resource_id,

   item_cost,

   usage_rate_or_amount,

   cost_element_id,

   process_flag,

   transaction_type

)

values

(

   :inventory_item_id,

   :organization_id,

   :cost_type_id, --derive Pending cost type ID from cst_cost_types table

   1,

   1,

   :resource_id,  --Overhead cost is always associated with a resource. Derive this from BOM_RESOURCES_V (maybe Freight or any custom resource you have)

   :material_ovhd_cost,

   :material_ovhd_cost,

   2, --element ID = 2 for Material Overhead element of the cost

   1,

   'CREATE'

);

Once the data is inserted into the table, launch the concurrent program "CSTPCIMP" to import this data into base tables

Once done, the data should appear in cst_item_costs and cst_item_cost_details table

Cheers
A

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