BOM interface in oracle apps r12

In this post , We will be discuss about BOM interface in oracle apps r12. BOM interface helps to upload the Bill Of Material (BOM) data from external source into the oracle apps r12. We can upload the mass BOM data through BOM interface in oracle apps. BOM interface helps to support the mass data conversion activities. We just need to prepare the BOM data in the format of BOM interface in oracle apps r12 and interface the BOM data in BOM interface tables.Here below is the detail plsql script for the bom interface in oracle apps which do helps to upload BOM data.

Important BOM interface tables  in oracle apps r12

1.bom_bill_of_mtls_interface

2.bom_inventory_comps_interface 

Detail PLSQL Script for BOM interface in oracle apps r12 

Declare
CURSOR cur_parent_bom_detail
      IS
         SELECT organization_code, item_number, revision, alternate_name
            FROM xx_bom_stag
            group by organization_code, item_number, revision, alternate_name;
      CURSOR cur_child_bom_detail (
         v_organization_code   VARCHAR2,
         v_item_number         VARCHAR2,
         v_revision            VARCHAR2,
         v_alternate_name      VARCHAR2
      )
      IS
  SELECT /*+ USE_HASH_AGGREGATION INDEX_RS_ASC (XXdata_loading_BOM_STAG
              XXdata_loading_BOM_STAG_2)*/ *
           FROM xx_bom_stag
          WHERE     organization_code = v_organization_code
                                    AND item_number = v_item_number
                                       AND revision =v_revision
                                    AND alternate_name =v_alternate_name;
      org_id                NUMBER;
      assem_item_id         NUMBER;
      v_supply_locator_id   NUMBER;
      supply_look_code      NUMBER;
      comp_item_id          NUMBER;
      v_erro_msg            VARCHAR2 (32767);
      CURSOR cur_get_org_id (v_org_code VARCHAR2)
      IS
         SELECT a.organization_id
           FROM mtl_parameters a, org_organization_definitions b
          WHERE a.master_organization_id = b.organization_id
            AND a.organization_code = v_org_code;
      CURSOR cur_get_item_id (v_org_id NUMBER, v_item_number VARCHAR2)
      IS
         SELECT inventory_item_id
           FROM mtl_system_items_b
          WHERE organization_id = v_org_id AND segment1 = v_item_number;
      CURSOR cur_supply_type (v_supply_type VARCHAR2)
      IS
         SELECT lookup_code
           FROM mfg_lookups
          WHERE UPPER (meaning) = UPPER (v_supply_type)
            AND lookup_type = 'WIP_SUPPLY';
   BEGIN
      BEGIN
         FOR parent_bom IN cur_parent_bom_detail
         LOOP
            BEGIN
               OPEN cur_get_org_id (parent_bom.organization_code);
               FETCH cur_get_org_id
                INTO org_id;
               IF cur_get_org_id%NOTFOUND
               THEN
                  org_id := NULL;
                  CLOSE cur_get_org_id;
               END IF;
               CLOSE cur_get_org_id;
               OPEN cur_get_item_id (org_id, parent_bom.item_number);
               FETCH cur_get_item_id
                INTO assem_item_id;
               IF cur_get_item_id%NOTFOUND
               THEN
                  assem_item_id := NULL;
                  CLOSE cur_get_item_id;
               END IF;
               CLOSE cur_get_item_id;
               ----------Insert bom_bill_of_mtls_interface -----------------
               INSERT INTO bom_bill_of_mtls_interface
                           (assembly_item_id, organization_id,
                            alternate_bom_designator,
                            organization_code,
                            item_number, last_update_date,
                            last_updated_by, creation_date, created_by,
                            last_update_login, transaction_type, process_flag
                           )
                    VALUES (assem_item_id, org_id,
                            parent_bom.alternate_name,
                            parent_bom.organization_code,
                            parent_bom.item_number, SYSDATE,
                            fnd_global.user_id, SYSDATE, fnd_global.user_id,
                            fnd_global.user_id, 'CREATE', 1
                           );
               -----------------Child Record Insertion -----------
               FOR child_bom IN
                  cur_child_bom_detail
                         (v_organization_code      => parent_bom.organization_code,
                          v_item_number            => parent_bom.item_number,
                          v_revision               => parent_bom.revision,
                          v_alternate_name         => parent_bom.alternate_name
                         )
               LOOP
                  BEGIN
                     OPEN cur_get_item_id (org_id,
                                           child_bom.comp_item_number);
                     FETCH cur_get_item_id
                      INTO comp_item_id;
                     IF cur_get_item_id%NOTFOUND
                     THEN
                        comp_item_id := NULL;
                        CLOSE cur_get_item_id;
                     END IF;
                     CLOSE cur_get_item_id;
                     OPEN cur_supply_type (child_bom.wip_supply_type);
                     FETCH cur_supply_type
                      INTO supply_look_code;
                     IF cur_supply_type%NOTFOUND
                     THEN
                        supply_look_code := NULL;
                        CLOSE cur_supply_type;END IF;
                     CLOSE cur_supply_type;
                     IF child_bom.location_name IS NOT NULL
                     THEN
                        BEGIN
                           SELECT inventory_location_id
                             INTO v_supply_locator_id
                             FROM mtl_item_locations_kfv
                            WHERE organization_id = org_id
                              AND subinventory_code =
                                                 child_bom.supply_subinventory
                              AND concatenated_segments =
                                                       child_bom.location_name;
                        EXCEPTION
                           WHEN NO_DATA_FOUND
                           THEN
                              v_supply_locator_id := NULL;
                           WHEN OTHERS
                           THEN
                              v_supply_locator_id := NULL;
                        END;
                     END IF;
                     INSERT INTO bom_inventory_comps_interface
                                 (component_item_id, assembly_item_id,
                                  organization_id, component_item_number,
                                  organization_code,
                                  assembly_item_number,
                                  alternate_bom_designator,
                                  item_num,
                                  operation_seq_num, effectivity_date,
                                  component_quantity,
                                  component_yield_factor,
                                  wip_supply_type,
                                  supply_subinventory,
                                  supply_locator_id, last_update_date,
                                  last_updated_by, creation_date,
                                  created_by, last_update_login,
                                  transaction_type, process_flag,
                                  component_remarks
                                 )
                          VALUES (comp_item_id, assem_item_id,
                                  org_id, child_bom.comp_item_number,
                                  child_bom.organization_code,
                                  child_bom.item_number,
                                  child_bom.alternate_name,
                                  child_bom.item_seq_num,
                                  child_bom.operation_seq_num, SYSDATE,
                                  child_bom.component_quantity,
                                  child_bom.component_yield_factor,
                                  supply_look_code,
                                  child_bom.supply_subinventory,
                                  v_supply_locator_id, SYSDATE,
                                  fnd_global.user_id, SYSDATE,
                                  fnd_global.user_id, fnd_global.user_id,
                                  'CREATE', 1,
                                  'BOM Migration'
                                 );
                     UPDATE xx_bom_stag
                        SET flag_process = 'P'
                      WHERE entry_id = child_bom.entry_id;
                     COMMIT;
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        v_erro_msg := SQLERRM;
                        UPDATE xx_bom_stag
                           SET flag_process = 'E',
                               error_message = v_erro_msg
                         WHERE entry_id = child_bom.entry_id;
                        COMMIT;
                  END;
                  COMMIT;
               END LOOP;
               COMMIT;
            END;
         END LOOP;
      END;
   END;