Monday 30 May 2011

Mandatory columns needed to insert data into BOM Interface tables.

Here is a snippet of code which tells about the mandatory columns needed to insert the data into BOM Interface tables.


There are two BOM Interface tables, which needs to be populated in order to create BOM.

                 BOM_BILL_OF_MTLS_INTERFACE
                 BOM_INVENTORY_COMPS_INTERFACE
Insert command :

            INSERT INTO bom_bill_of_mtls_interface(
                          assembly_item_id
                              ,organization_id
                              ,bill_sequence_id
                              ,item_number
                              ,item_description
                              ,last_update_date
                              ,last_updated_by
                              ,creation_date
                              ,created_by
                              ,last_update_login
                              ,transaction_type
                              ,process_flag
                               )

            VALUES( <assembly_item_id>
                             ,<organization_id>
                             ,<bill_sequence_id>
                             ,<assembly_item_number>
                             ,<description>
                             ,SYSDATE
                              ,<user_id>
                              ,SYSDATE
                              ,user_id
                              ,user_id
                         ,<CREATE or UPDATE> -- If it is update, then bill sequence id is required, else NULL
                              ,1
                             );  
            

             INSERT INTO BOM_INVENTORY_COMPS_INTERFACE
                                (  component_item_id
                                    ,assembly_item_id
                                    ,organization_id
                                    ,item_num
                                    ,operation_seq_num
                                    ,effectivity_date
                                    ,transaction_type
                                    ,process_flag
                                    ,bill_sequence_id
                                    ,component_sequence_id
                                    ,item_description
                                    ,basis_type
                                    ,component_quantity
                                    ,component_yield_factor
                                    ,implementation_date
                                    ,supply_subinventory
                                    ,wip_supply_type
                                    ,so_basis
                                    ,check_atp
                                    ,planning_factor
                                    ,low_quantity
                                    ,high_quantity
                                    ,last_update_date
                                    ,last_updated_by
                                    ,creation_date
                                    ,created_by
                                    ,last_update_login
                                    ,component_remarks
                                     )
                              values
                                    (
                                     <component_item_id>
                                    ,<assembly_item_id>
                                    ,<organization_id>
                                    ,<item_seq_num>
                                    ,<operation_sequence>
                                    ,SYSDATE
                                    ,<CREATE or UPDATE> -- If it is update, then Component sequence id is required, else NULL
                                    ,1
                                    ,<bill_sequence_id>  -- If transaction type is UPDATE, then this value is required else NULL
                                    ,<comp_sequence_id> -- If transaction type is UPDATE, then this value is required else NULL
                                    ,<com_description>
                                    ,DECODE(basis_type,1,NULL,2)
                                    ,<component_quantity>
                                    ,<component_yield_factor>
                                    ,<implementation_date>
                                    ,<supply_subinventory>
                                    ,<l_wip_supply_type> -- lookup code from MFG_LOOKUPS where lookup_type = 'WIP_SUPPLY'
                                    ,<so_basis>
                                    ,DECODE(check_atp,'Y',1,'N',2)
                                    ,<planning_factor>
                                    ,<low_quantity>
                                    ,<high_quantity>
                                    ,SYSDATE
                                    ,user_id
                                    ,SYSDATE
                                    ,user_id
                                    ,user_id
                                   ,'BOM Migration'
                                    );


Once data is successfully loaded, then call the standard oracle concurrent program : Bill and Routing Interface to upload the data from interface tables to Base tables.

Hope this helps. Please feel free to comments.

1 comment:

  1. how to find out the mandatory fields for particular interface table

    ReplyDelete