BOM Explode Script in Oracle Apps

Script to Explode BOM in Oracle Apps R12

DECLARE
   l_model_item          VARCHAR2 (100)  := 'YV01S106';
   l_organization_code   VARCHAR2 (100)  := 'P01';
   l_model_item_id       NUMBER;
   l_organization_id     NUMBER;
   l_cnt                 NUMBER          DEFAULT 0;
   l_err_msg             VARCHAR2 (240);
   l_err_code            NUMBER          DEFAULT 0;
   l_verify_flag         NUMBER          DEFAULT 0;
   l_online_flag         NUMBER          DEFAULT 2;
   l_item_id             NUMBER          DEFAULT 0;
   l_org_id              NUMBER          DEFAULT 0;
   l_alternate           VARCHAR2 (240)  DEFAULT NULL;
   l_list_id             NUMBER          DEFAULT 0;
   l_order_by            NUMBER          DEFAULT 1;
   l_grp_id              NUMBER          DEFAULT 0;
   l_session_id          NUMBER          DEFAULT 0;
   l_req_id              NUMBER          DEFAULT 0;
   l_prgm_appl_id        NUMBER          DEFAULT -1;
   l_prgm_id             NUMBER          DEFAULT -1;
   l_levels_to_explode   NUMBER          DEFAULT 100;
   l_bom_or_eng          NUMBER          DEFAULT 1;
   l_impl_flag           NUMBER          DEFAULT 1;
   l_plan_factor_flag    NUMBER          DEFAULT 2;
   l_incl_lt_flag        NUMBER          DEFAULT 2;
   l_explode_option      NUMBER          DEFAULT 2;
   l_module              NUMBER          DEFAULT 2;
   l_cst_type_id         NUMBER          DEFAULT 0;
   l_std_comp_flag       NUMBER          DEFAULT 0;
   l_rel_date            VARCHAR2 (240);
   l_comp_code           VARCHAR2 (240)  DEFAULT NULL;
   l_expl_qty            NUMBER          DEFAULT 1;
   lc_unit_number        VARCHAR2 (2000);
   ln_release_option     NUMBER          := 0;

   CURSOR lcu_fg_items
   IS
      SELECT *
        FROM xx_bom_no_of_ckts_t;
BEGIN
   FOR i IN lcu_fg_items
   LOOP
      --Retreive item_id and  organization_id
      SELECT msi.inventory_item_id, msi.organization_id
        INTO l_model_item_id, l_organization_id
        FROM mtl_system_items msi, org_organization_definitions ood
       WHERE msi.segment1 = i.item_code
         AND msi.organization_id = ood.organization_id
         AND ood.organization_code = i.org_code;

      --BOM Date
      l_rel_date := TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS');

      -- l_grp_id is a unique identifier for this run of the exploder
      SELECT bom_explosion_temp_s.NEXTVAL
        INTO l_grp_id
        FROM DUAL;

      -- determine maximum levels to explode from bom_explosions
      SELECT maximum_bom_level
        INTO l_levels_to_explode
        FROM bom_parameters
       WHERE organization_id = l_organization_id;

      --calling apps standard userexit for explosion of star item
      apps.bompexpl.exploder_userexit (l_verify_flag,
                                       l_organization_id,
                                       l_order_by,
                                       l_grp_id,
                                       l_session_id,
                                       l_levels_to_explode,
                                       l_bom_or_eng,
                                       l_impl_flag,
                                       l_plan_factor_flag,
                                       l_explode_option,
                                       l_module,
                                       l_cst_type_id,
                                       l_std_comp_flag,
                                       l_expl_qty,
                                       l_model_item_id,
                                       l_alternate,
                                       l_comp_code,
                                       l_rel_date,
                                       lc_unit_number,
                                       ln_release_option,
                                       l_err_msg,
                                       l_err_code
                                      );

      IF (l_err_code <> 0)
      THEN
         DBMS_OUTPUT.put_line (   'Error in BOM Explosion API '
                               || l_err_code
                               || '::'
                               || l_err_msg
                              );
         ROLLBACK;
      ELSE
         DBMS_OUTPUT.put_line ('Group Id is : ' || l_grp_id);

         --Retrieve number of records
         SELECT COUNT (*)
           INTO l_cnt
           FROM bom_explosion_temp
          WHERE GROUP_ID = l_grp_id;

         DBMS_OUTPUT.put_line ('Number of Records are : ' || l_cnt);
         DBMS_OUTPUT.put_line (   'Number of levels exploded are : '
                               || l_levels_to_explode
                              );
         COMMIT;
      END IF;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (   'EXCEPTION IN BOM XPLODE:'
                            || SQLCODE
                            || '::'
                            || SQLERRM
                           );
END;