Oracle Bill of Material(BOM) in oracle apps through Oracle Interfaces.

 

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;