procedure BOM_EXPLORER_CUST2(
      x_group_id    in number,
      x_SORT_ORDER    VARCHAR2   DEFAULT  '0001',
      X_TOP_ITEM_ID IN NUMBER,
      X_INV_ITEM_ID IN NUMBER,
      x_top_sequence_id in number,
      X_UNIT_QTY    NUMBER,
      x_plan_level     number default 0,
      x_OPERATION_SEQ_NUM number) is

X_ORG_ID    number:=4;
P_EXIST_BOM number;
X_EFFECTIVE_DATE    date:=sysdate;
   P_EXPLODE_QTY     NUMBER:=X_UNIT_QTY;
   v_OPERATION_SEQ_NUM number;
   --P_LOSS_RATE       NUMBER:=X_LOSS_RATE;
   CURSOR FIND_BOM_DATA IS
     SELECT  bbom.bill_sequence_id,
             BIC.COMPONENT_ITEM_ID        COMPONENT_ITEM_ID,
                NVL(BIC.COMPONENT_QUANTITY,0)    COMPONENT_QUANTITY,
              NVL(BIC.COMPONENT_QUANTITY,0)*P_EXPLODE_QTY        ROLL_COMQTY,
              BIC.OPERATION_SEQ_NUM            OPERATION_SEQ_NUM,
              BIC.ITEM_NUM                    ITEM_NUM,
             NVL(BIC.ATTRIBUTE5,0)           ATTRIBUTE5,
             BIC.COMPONENT_SEQUENCE_ID,
                NVL(MSI.ITEM_TYPE,' ')    ITEM_TYPE,
             NVL(BIC.SUPPLY_SUBINVENTORY,MSI.WIP_SUPPLY_SUBINVENTORY)
                SUBINVENTORY_CODE,
               MSI.PRIMARY_UOM_CODE PRIMARY_UOM_CODE,
             MSI.SEGMENT1        COMPONENT_ITEM_NUMBER,
              MSI.DESCRIPTION        COMPONENT_DESC,
             BIC.EFFECTIVITY_DATE,
             msi.wip_supply_type,
             BIC.DISABLE_DATE,
             bic.attribute2,
             BIC.COMPONENT_REMARKS
       FROM  apps.BOM_BILL_OF_MATERIALS BBOM,
              apps.BOM_INVENTORY_COMPONENTS BIC,
             apps.MTL_SYSTEM_ITEMS MSI
       WHERE BBOM.ASSEMBLY_ITEM_ID = X_INV_ITEM_ID AND
             BBOM.ORGANIZATION_ID = X_ORG_ID AND
              BBOM.ALTERNATE_BOM_DESIGNATOR IS NULL AND
                --BBOM.ASSEMBLY_TYPE = X_ENG_OR_BOM AND
              BIC.BILL_SEQUENCE_ID = BBOM.COMMON_BILL_SEQUENCE_ID AND
              (((TRUNC(BIC.EFFECTIVITY_DATE)<=TRUNC(X_EFFECTIVE_DATE)) AND
             (TRUNC(X_EFFECTIVE_DATE)<TRUNC(BIC.DISABLE_DATE))) OR
             BIC.DISABLE_DATE IS NULL) AND
             (BIC.IMPLEMENTATION_DATE IS NOT NULL) AND
             MSI.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID AND
             MSI.ORGANIZATION_ID = BBOM.ORGANIZATION_ID AND
             MSI.ENABLED_FLAG='Y'
       --ORDER BY BIC.ITEM_NUM,BIC.OPERATION_SEQ_NUM;
       ORDER BY -nvl(BIC.COMPONENT_QUANTITY,0),BIC.OPERATION_SEQ_NUM;

      P_PARENT_ITEM_ID    NUMBER:=X_INV_ITEM_ID;
      P_COUNT            NUMBER:=0;
      v_plan_level      number;
      P_PARENT_SORT_ORDER        VARCHAR2(500):=X_SORT_ORDER;
      PP_SORT_ORDER        VARCHAR2(500);
      BEGIN
         v_plan_level:=x_plan_level+1;
         P_PARENT_ITEM_ID:=X_INV_ITEM_ID;

         FOR C1 IN FIND_BOM_DATA LOOP
             P_COUNT:=P_COUNT+1;
             BEGIN
                  PP_SORT_ORDER:=P_PARENT_SORT_ORDER||LPAD(TO_CHAR(P_COUNT),4,'0');
               EXCEPTION
             WHEN OTHERS THEN
                 DBMS_OUTPUT.PUT_LINE(TO_CHAR(X_ORG_ID)||':'||TO_CHAR(X_TOP_ITEM_ID)||':'||
                 TO_CHAR(X_INV_ITEM_ID));
                 EXIT;
             END;
             if X_INV_ITEM_ID=X_TOP_ITEM_ID then
                v_OPERATION_SEQ_NUM:=C1.OPERATION_SEQ_NUM;
             else
                v_OPERATION_SEQ_NUM:=x_OPERATION_SEQ_NUM;
             end if;

               SELECT COUNT(*) INTO P_EXIST_BOM
               FROM apps.BOM_BILL_OF_MATERIALS
               WHERE ASSEMBLY_ITEM_ID=C1.COMPONENT_ITEM_ID
               AND ORGANIZATION_ID=X_ORG_ID;
               IF P_EXIST_BOM <> 0 THEN
                  BOM_EXPLORER_CUST2(
                        X_GROUP_ID,
                        PP_SORT_ORDER,
                         X_TOP_ITEM_ID,
                        C1.COMPONENT_ITEM_ID,
                        x_top_sequence_id,
                        C1.COMPONENT_QUANTITY*X_UNIT_QTY,
                        v_plan_level,
                        v_OPERATION_SEQ_NUM);
              end if;
            INSERT INTO ZD_BOM_SMALL_EXPL_TEMP(
                  organization_id,
                  TOP_ITEM_ID,
                   ASSEMBLY_ITEM_ID,
                   COMPONENT_ITEM_ID,
                   top_BILL_SEQUENCE_ID,
                   BILL_SEQUENCE_ID,
                   ITEM_NUM,
                   plan_level,
                   OPERATION_SEQ_NUM,
                   COMPONENT_QUANTITY,
                   extended_QUANTITY,
                   PRIMARY_UOM_CODE,
                   attribute1,
                   DESCRIPTION,
                    GROUP_ID,
                   SORT_ORDER,
                   attribute2,
                   EFFECTIVITY_DATE,
                   DISABLE_DATE  ,
                   COMPONENT_SEQUENCE_ID,
                   attribute9
                )
              values
              (x_org_id,
               X_TOP_ITEM_ID,
               X_INV_ITEM_ID,
               c1.COMPONENT_ITEM_ID,
               x_top_sequence_id,
               c1.bill_sequence_id,

               C1.ITEM_NUM,
               v_plan_level,
               v_OPERATION_SEQ_NUM,
               C1.COMPONENT_QUANTITY,
               C1.COMPONENT_QUANTITY*X_UNIT_QTY,
               C1.PRIMARY_UOM_CODE,
               C1.COMPONENT_ITEM_NUMBER,
               C1.COMPONENT_DESC,
               x_group_id,
               PP_SORT_ORDER,
               c1.attribute2,
               c1.EFFECTIVITY_DATE,
               c1.DISABLE_DATE  ,
               c1.COMPONENT_SEQUENCE_ID,
               C1.COMPONENT_REMARKS);

        end loop;
  end;