BOM Explode API

DECLARE

  v_item             VARCHAR2(240) := 'AS54888';    -- item to explode

  v_org               VARCHAR2(4)      := 'A11';             -- org in which item is exploded

  v_cnt                NUMBER        := 0;

  v_err_msg       VARCHAR2(240);

  v_err_code     NUMBER        := 0;

  v_item_id        NUMBER        := 1050206;           -- set to inventory_item_id of item to explode

  v_org_id          NUMBER        := 0;    -- set to organization_id of item to explode

  v_order_by     NUMBER        := 1;    -- DEFAULT 1

  v_grp_id          NUMBER        := 0;    

  v_session_id   NUMBER        := 0;    -- DEFAULT 0

  v_levels_to_explode NUMBER        := 1;    -- DEFAULT 1

  v_bom_or_eng          NUMBER        := 1;    -- DEFAULT 1

  v_explode_option     NUMBER        := 1;    -- DEFAULT 2

  v_rev_date          date :=null;--VARCHAR2(240);         

BEGIN

  v_rev_date := TO_CHAR(SYSDATE);

  -- Find org_id

   SELECT mp.organization_id

     INTO v_org_id

     FROM MTL_PARAMETERS mp

    WHERE mp.organization_code = v_org;

 -- Find item_id

  -- v_grp_id is a unique identifier for this run of the exploder

   SELECT bom_explosion_temp_s.nextval

     INTO v_grp_id

     FROM dual;

  -- determine maximum levels to explode from bom_explosions

   SELECT maximum_bom_level

     INTO v_levels_to_explode

     FROM bom_parameters

    WHERE organization_id = v_org_id;

                 bompexpl.exploder_userexit

                                   (org_id                 => v_org_id,

                                    order_by               => v_order_by,

                                    grp_id                 => v_grp_id,

                                    session_id             => fnd_global.session_id,

                                    levels_to_explode      => v_levels_to_explode,

                                    bom_or_eng             => v_bom_or_eng,

                                    explode_option         => v_explode_option,

                                    item_id                => v_item_id,

                                    rev_date               => v_rev_date,

                                    err_msg                => v_err_msg,

                                    ERROR_CODE             => v_err_code

                                   );

  IF ( v_err_code <> 0 ) THEN

    ROLLBACK;

    dbms_output.put_line('ERROR: ' || v_err_msg);

  ELSE

    SELECT COUNT(*) INTO v_cnt FROM bom_explosion_temp WHERE group_id=v_grp_id;

   

    dbms_output.put_line('Count=' || v_cnt);

    COMMIT;

    dbms_output.put_line('.');

    dbms_output.put_line('Group Id=' || v_grp_id);

    dbms_output.put_line('Org =' || v_org);

    --dbms_output.put_line('Item =' || v_item);

    dbms_output.put_line('Ord Id =' || v_org_id);

    dbms_output.put_line('Item Id=' || v_item_id);

    dbms_output.put_line('Levels =' || v_levels_to_explode);

  END IF;

END;

/