Monday 8 January 2018

Bill of Material(BOM) Oracle apps Interface to Upload BOM in Oracle apps.


In this post , I will share you the PLSQL script to upload Oracle Bill of Material(BOM) in oracle apps through Oracle Interfaces. I am just sharing you sample script you need to do some alterations in your instance as per your requirement.


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;

4 comments:

12345 said...

Good Blog , Thanks for sharing this informative article.
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad

Rainbow Training Institute said...

Thank you for sharing such a nice and really very helpful article

Oracle Fusion HCM Online Training

Anonymous said...

Thanks for sharing This informative article. It would be helpful to all ,Keep sharing.
Oracle Fusion HCM Online Training

shaik shah said...

Goodblog, thanks for sharing this informative article. It would be helpful to all .your way of explaining in this good in this article.
Oracle Fusion HCM Online Training

Post a Comment

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *