Mandatory columns needed to insert data into BOM Interface tables


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.

1 comment:

  1. Hi Bhaskar How are you doing ? Can you send me the interfaces scripts from Oracle BOM and WIP release R12 ?

    I need load bills of materiais, routings,resources,etc. Please contact me,

    ReplyDelete

How to find all cancel Requisitions

SELECT prha . *   FROM po_Requisition_headers_all prha , po_action_history pah   WHERE      1 = 1        AND pah . object_id ...