APPS.BOM_DELETE_ITEMS SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 83

PROCEDURE UPDATE_UNPROCESSED_ROWS
(
delete_group_id   IN NUMBER
)
IS
BEGIN
  UPDATE BOM_DELETE_ENTITIES
  SET PRIOR_process_flag = 1
  WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id
  AND PRIOR_process_flag = 2;

Line: 96

        func_name => 'UPDATE_UNPROCESSED_ROWS',
        stmt_num  => 1);

Line: 98

END UPDATE_UNPROCESSED_ROWS;

Line: 139

PROCEDURE POPULATE_DELETE
(
org_id      IN NUMBER,
last_login_id   IN NUMBER DEFAULT -1,
catalog_search_id IN NUMBER,
component_id    IN NUMBER,
delete_group_id   IN NUMBER,
delete_type   IN NUMBER,
del_grp_type    IN NUMBER,
process_type    IN NUMBER,
expiration_date   IN DATE) IS

invalid_parameter EXCEPTION;

Line: 169

  IF delete_type = 1 THEN
    process_flag := 1;

Line: 171

  ELSIF delete_type = 7 THEN
    process_flag := 2;

Line: 182

    var_text := 'delete group type';

Line: 198

  IF delete_type IN (1, 2, 3, 7) THEN
    del_stat_type := 1;

Line: 200

  ELSIF delete_type IN (4, 5, 6) THEN
    del_stat_type := -1;

Line: 203

    var_text := 'delete type';

Line: 218

      IF delete_type IN (1, 6, 7) THEN
        INSERT INTO BOM_DELETE_ENTITIES(
          DELETE_ENTITY_SEQUENCE_ID,
          DELETE_GROUP_SEQUENCE_ID,
          DELETE_ENTITY_TYPE,
          INVENTORY_ITEM_ID,
    ORGANIZATION_ID,
    ALTERNATE_DESIGNATOR,
    ITEM_DESCRIPTION,
    ITEM_CONCAT_SEGMENTS,
    DELETE_STATUS_TYPE,
    PRIOR_PROCESS_FLAG,
    PRIOR_COMMIT_FLAG,
    BILL_SEQUENCE_ID,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN)
        SELECT
          BOM_DELETE_ENTITIES_S.NEXTVAL,
    delete_group_id,
    1,
    MCSI.INVENTORY_ITEM_ID,
    MCSI.ORGANIZATION_ID,
    '',
    MCSI.DESCRIPTION,
    MIF.ITEM_NUMBER,
    del_stat_type,
    2,
    commit_flag,
    '',
    SYSDATE,
    userid,
    SYSDATE,
    userid,
    last_login_id
        FROM
          MTL_CATALOG_SEARCH_ITEMS MCSI,
          MTL_SYSTEM_ITEMS MSI,
          MTL_ITEM_FLEXFIELDS MIF
        WHERE MSI.INVENTORY_ITEM_ID = MCSI.INVENTORY_ITEM_ID
        AND MSI.ORGANIZATION_ID = MCSI.ORGANIZATION_ID
        AND MIF.ITEM_ID = MCSI.INVENTORY_ITEM_ID
        AND MIF.ORGANIZATION_ID = MCSI.ORGANIZATION_ID
        AND MCSI.ORGANIZATION_ID = org_id
        AND MSI.ENG_ITEM_FLAG = DECODE(delete_type, 1, item_eng_flag,
                                       7, item_eng_flag, MSI.ENG_ITEM_FLAG)
        AND MCSI.GROUP_HANDLE_ID = catalog_search_id
        AND MCSI.INVENTORY_ITEM_ID NOT IN(
          SELECT DISTINCT INVENTORY_ITEM_ID
          FROM BOM_DELETE_ENTITIES
          WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id
          AND ORGANIZATION_ID = org_id);

Line: 273

        DELETE MTL_CATALOG_SEARCH_ITEMS
        WHERE GROUP_HANDLE_ID = catalog_search_id;

Line: 279

      ELSIF delete_type IN (2, 4) THEN
        INSERT INTO BOM_DELETE_ENTITIES(
          DELETE_ENTITY_SEQUENCE_ID,
          DELETE_GROUP_SEQUENCE_ID,
          DELETE_ENTITY_TYPE,
          INVENTORY_ITEM_ID,
          ORGANIZATION_ID,
          ALTERNATE_DESIGNATOR,
          ITEM_DESCRIPTION,
          ITEM_CONCAT_SEGMENTS,
          DELETE_STATUS_TYPE,
          PRIOR_PROCESS_FLAG,
          PRIOR_COMMIT_FLAG,
          BILL_SEQUENCE_ID,
          LAST_UPDATE_DATE,
          LAST_UPDATED_BY,
          CREATION_DATE,
          CREATED_BY,
          LAST_UPDATE_LOGIN)
        SELECT
          BOM_DELETE_ENTITIES_S.NEXTVAL,
          delete_group_id,
          2,
          BOM.ASSEMBLY_ITEM_ID,
          BOM.ORGANIZATION_ID,
          BOM.ALTERNATE_BOM_DESIGNATOR,
          MCSI.DESCRIPTION,
          MIF.ITEM_NUMBER,
          del_stat_type,
          2,
          commit_flag,
          BOM.BILL_SEQUENCE_ID,
          SYSDATE,
          userid,
          SYSDATE,
          userid,
          last_login_id
        FROM
          MTL_CATALOG_SEARCH_ITEMS MCSI,
          BOM_BILL_OF_MATERIALS BOM,
          MTL_ITEM_FLEXFIELDS MIF
        WHERE MCSI.INVENTORY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
        AND MCSI.ORGANIZATION_ID = BOM.ORGANIZATION_ID
        AND MIF.ITEM_ID = MCSI.INVENTORY_ITEM_ID
        AND MIF.ORGANIZATION_ID = MCSI.ORGANIZATION_ID
        AND MCSI.ORGANIZATION_ID = org_id
        AND BOM.ASSEMBLY_TYPE = eng_flag
        AND MCSI.GROUP_HANDLE_ID = catalog_search_id
        AND BOM.BILL_SEQUENCE_ID NOT IN(
          SELECT NVL(BILL_SEQUENCE_ID, 0)
          FROM BOM_DELETE_ENTITIES
          WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id);

Line: 332

        DELETE MTL_CATALOG_SEARCH_ITEMS
        WHERE GROUP_HANDLE_ID = catalog_search_id;

Line: 338

      ELSIF delete_type IN (3, 5) THEN
        INSERT INTO BOM_DELETE_ENTITIES(
          DELETE_ENTITY_SEQUENCE_ID,
    DELETE_GROUP_SEQUENCE_ID,
    DELETE_ENTITY_TYPE,
    INVENTORY_ITEM_ID,
    ORGANIZATION_ID,
    ALTERNATE_DESIGNATOR,
    ITEM_DESCRIPTION,
    ITEM_CONCAT_SEGMENTS,
    DELETE_STATUS_TYPE,
    PRIOR_PROCESS_FLAG,
    PRIOR_COMMIT_FLAG,
    ROUTING_SEQUENCE_ID,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN)
        SELECT
          BOM_DELETE_ENTITIES_S.NEXTVAL,
    delete_group_id,
    3,
    BOR.ASSEMBLY_ITEM_ID,
    BOR.ORGANIZATION_ID,
    BOR.ALTERNATE_ROUTING_DESIGNATOR,
    MCSI.DESCRIPTION,
    MIF.ITEM_NUMBER,
    del_stat_type,
    2,
    commit_flag,
    BOR.ROUTING_SEQUENCE_ID,
    SYSDATE,
    userid,
    SYSDATE,
    userid,
    last_login_id
        FROM
          MTL_CATALOG_SEARCH_ITEMS MCSI,
          BOM_OPERATIONAL_ROUTINGS BOR,
          MTL_ITEM_FLEXFIELDS MIF
        WHERE MCSI.ORGANIZATION_ID = org_id
        AND MCSI.INVENTORY_ITEM_ID = BOR.ASSEMBLY_ITEM_ID
  AND MCSI.ORGANIZATION_ID = BOR.ORGANIZATION_ID
  AND MIF.ITEM_ID = MCSI.INVENTORY_ITEM_ID
  AND MIF.ORGANIZATION_ID = MCSI.ORGANIZATION_ID
  AND BOR.ROUTING_TYPE = eng_flag
  AND MCSI.GROUP_HANDLE_ID = catalog_search_id
  AND BOR.ROUTING_SEQUENCE_ID NOT IN(
          SELECT NVL(ROUTING_SEQUENCE_ID, 0)
          FROM BOM_DELETE_ENTITIES
          WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id
          AND ORGANIZATION_ID = org_id);

Line: 391

        DELETE MTL_CATALOG_SEARCH_ITEMS
        WHERE GROUP_HANDLE_ID = catalog_search_id;

Line: 406

    IF delete_type = 4 THEN

      -- COMPONENT ID: NULL, EXPIRATION DATE: NOT NULL

      IF component_id <= 0 THEN
        INSERT INTO BOM_DELETE_ENTITIES(
          DELETE_ENTITY_SEQUENCE_ID,
    DELETE_GROUP_SEQUENCE_ID,
    DELETE_ENTITY_TYPE,
    INVENTORY_ITEM_ID,
    ORGANIZATION_ID,
    ALTERNATE_DESIGNATOR,
    ITEM_DESCRIPTION,
    ITEM_CONCAT_SEGMENTS,
    PRIOR_PROCESS_FLAG,
    PRIOR_COMMIT_FLAG,
    BILL_SEQUENCE_ID,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN)
        SELECT
          BOM_DELETE_ENTITIES_S.NEXTVAL,
    delete_group_id,
    2,
    BOM.ASSEMBLY_ITEM_ID,
    BOM.ORGANIZATION_ID,
    BOM.ALTERNATE_BOM_DESIGNATOR,
    MSI.DESCRIPTION,
    MIF.ITEM_NUMBER,
    1,
    commit_flag,
    BOM.BILL_SEQUENCE_ID,
    SYSDATE,
    userid,
    SYSDATE,
    userid,
    last_login_id
        FROM
          BOM_BILL_OF_MATERIALS BOM,
          MTL_SYSTEM_ITEMS MSI,
          MTL_ITEM_FLEXFIELDS MIF
        WHERE MSI.INVENTORY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
        AND MSI.ORGANIZATION_ID = BOM.ORGANIZATION_ID
        AND MIF.ITEM_ID = BOM.ASSEMBLY_ITEM_ID
        AND MIF.ORGANIZATION_ID = BOM.ORGANIZATION_ID
        AND BOM.BILL_SEQUENCE_ID IN(
          SELECT DISTINCT BOM.BILL_SEQUENCE_ID
          FROM
            BOM_BILL_OF_MATERIALS BOM,
            BOM_INVENTORY_COMPONENTS BIC
          WHERE BOM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
          AND BOM.ORGANIZATION_ID = org_id
          AND BOM.ASSEMBLY_TYPE = eng_flag
          AND expiration_date >= BIC.DISABLE_DATE
          AND BOM.BILL_SEQUENCE_ID NOT IN(
            SELECT NVL(BILL_SEQUENCE_ID, 0)
            FROM BOM_DELETE_ENTITIES
            WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id));

Line: 467

        INSERT INTO BOM_DELETE_SUB_ENTITIES(
          DELETE_ENTITY_SEQUENCE_ID,
    COMPONENT_SEQUENCE_ID,
    OPERATION_SEQ_NUM,
    EFFECTIVITY_DATE,
    COMPONENT_ITEM_ID,
    COMPONENT_CONCAT_SEGMENTS,
    ITEM_NUM,
    DISABLE_DATE,
    DESCRIPTION,
    DELETE_STATUS_TYPE,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN)
        SELECT
          BDE.DELETE_ENTITY_SEQUENCE_ID,
    BIC.COMPONENT_SEQUENCE_ID,
    BIC.OPERATION_SEQ_NUM,
    BIC.EFFECTIVITY_DATE,
    BIC.COMPONENT_ITEM_ID,
    MIF.ITEM_NUMBER,
    BIC.ITEM_NUM,
    BIC.DISABLE_DATE,
    MSI.DESCRIPTION,
    1,
    SYSDATE,
    userid,
    SYSDATE,
    userid,
    last_login_id
        FROM
          BOM_INVENTORY_COMPONENTS BIC,
          BOM_DELETE_ENTITIES BDE,
          BOM_BILL_OF_MATERIALS BOM,
          MTL_SYSTEM_ITEMS MSI,
          MTL_ITEM_FLEXFIELDS MIF
        WHERE BOM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
        AND BDE.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
        AND MSI.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID
        AND MSI.ORGANIZATION_ID = BOM.ORGANIZATION_ID
        AND MIF.ITEM_ID = BIC.COMPONENT_ITEM_ID
        AND MIF.ORGANIZATION_ID = BOM.ORGANIZATION_ID
        AND BOM.ASSEMBLY_TYPE = eng_flag
        AND BDE.DELETE_GROUP_SEQUENCE_ID = delete_group_id
        AND expiration_date >= BIC.DISABLE_DATE
        AND BIC.COMPONENT_SEQUENCE_ID NOT IN(
          SELECT NVL(COMPONENT_SEQUENCE_ID, 0)
          FROM
            BOM_DELETE_SUB_ENTITIES BDSE,
            BOM_DELETE_ENTITIES BDE
          WHERE BDE.DELETE_GROUP_SEQUENCE_ID = delete_group_id
          AND BDE.DELETE_ENTITY_SEQUENCE_ID = BDSE.DELETE_ENTITY_SEQUENCE_ID);

Line: 535

        INSERT INTO BOM_DELETE_ENTITIES(
          DELETE_ENTITY_SEQUENCE_ID,
          DELETE_GROUP_SEQUENCE_ID,
          DELETE_ENTITY_TYPE,
          INVENTORY_ITEM_ID,
          ORGANIZATION_ID,
          ALTERNATE_DESIGNATOR,
          ITEM_DESCRIPTION,
          ITEM_CONCAT_SEGMENTS,
          PRIOR_PROCESS_FLAG,
          PRIOR_COMMIT_FLAG,
          BILL_SEQUENCE_ID,
          LAST_UPDATE_DATE,
          LAST_UPDATED_BY,
          CREATION_DATE,
          CREATED_BY,
          LAST_UPDATE_LOGIN)
        SELECT
          BOM_DELETE_ENTITIES_S.NEXTVAL,
          delete_group_id,
          2,
          BOM.ASSEMBLY_ITEM_ID,
          BOM.ORGANIZATION_ID,
          BOM.ALTERNATE_BOM_DESIGNATOR,
          MSI.DESCRIPTION,
          MIF.ITEM_NUMBER,
          1,
          commit_flag,
          BOM.BILL_SEQUENCE_ID,
          SYSDATE,
          userid,
          SYSDATE,
          userid,
          last_login_id
        FROM
          BOM_BILL_OF_MATERIALS BOM,
          MTL_SYSTEM_ITEMS MSI,
          MTL_ITEM_FLEXFIELDS MIF
        WHERE MSI.INVENTORY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
        AND MSI.ORGANIZATION_ID = BOM.ORGANIZATION_ID
        AND MIF.ITEM_ID = BOM.ASSEMBLY_ITEM_ID
        AND MIF.ORGANIZATION_ID = BOM.ORGANIZATION_ID
        AND BOM.BILL_SEQUENCE_ID IN(
          SELECT DISTINCT BOM.BILL_SEQUENCE_ID
          FROM
            BOM_BILL_OF_MATERIALS BOM,
            BOM_INVENTORY_COMPONENTS BIC
          WHERE BOM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
          AND BOM.ORGANIZATION_ID = org_id
          AND BOM.ASSEMBLY_TYPE = eng_flag
          AND BIC.COMPONENT_ITEM_ID = component_id
          AND NVL(expiration_date, NVL(BIC.DISABLE_DATE, TRUNC(SYSDATE))) >=
              NVL(BIC.DISABLE_DATE, NVL(expiration_date+1, TRUNC(SYSDATE)))
          AND BOM.BILL_SEQUENCE_ID NOT IN(
            SELECT NVL(BILL_SEQUENCE_ID, 0)
            FROM BOM_DELETE_ENTITIES
            WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id));

Line: 603

        INSERT INTO BOM_DELETE_SUB_ENTITIES(
          DELETE_ENTITY_SEQUENCE_ID,
          COMPONENT_SEQUENCE_ID,
          OPERATION_SEQ_NUM,
          EFFECTIVITY_DATE,
          COMPONENT_ITEM_ID,
          COMPONENT_CONCAT_SEGMENTS,
          ITEM_NUM,
          DISABLE_DATE,
          DESCRIPTION,
          DELETE_STATUS_TYPE,
          LAST_UPDATE_DATE,
          LAST_UPDATED_BY,
          CREATION_DATE,
          CREATED_BY,
          LAST_UPDATE_LOGIN)
        SELECT
          BDE.DELETE_ENTITY_SEQUENCE_ID,
          BIC.COMPONENT_SEQUENCE_ID,
          BIC.OPERATION_SEQ_NUM,
          BIC.EFFECTIVITY_DATE,
          BIC.COMPONENT_ITEM_ID,
          MIF.ITEM_NUMBER,
          BIC.ITEM_NUM,
          BIC.DISABLE_DATE,
          MSI.DESCRIPTION,
          1,
          SYSDATE,
          userid,
          SYSDATE,
          userid,
          last_login_id
        FROM
          BOM_INVENTORY_COMPONENTS BIC,
          BOM_DELETE_ENTITIES BDE,
          BOM_BILL_OF_MATERIALS BOM,
          MTL_SYSTEM_ITEMS MSI,
          MTL_ITEM_FLEXFIELDS MIF
        WHERE BOM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
        AND BDE.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
        AND MSI.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID
        AND MSI.ORGANIZATION_ID = BOM.ORGANIZATION_ID
        AND MIF.ITEM_ID = BIC.COMPONENT_ITEM_ID
        AND MIF.ORGANIZATION_ID = BOM.ORGANIZATION_ID
        AND BOM.ASSEMBLY_TYPE = eng_flag
        AND BDE.DELETE_GROUP_SEQUENCE_ID = delete_group_id
        AND BIC.COMPONENT_ITEM_ID = component_id
        AND NVL(expiration_date, NVL(BIC.DISABLE_DATE,TRUNC(SYSDATE))) >=
            NVL(BIC.DISABLE_DATE, NVL(expiration_date + 1, TRUNC(SYSDATE)))
        AND BIC.COMPONENT_SEQUENCE_ID NOT IN(
          SELECT NVL(COMPONENT_SEQUENCE_ID, 0)
          FROM
            BOM_DELETE_SUB_ENTITIES BDSE,
            BOM_DELETE_ENTITIES BDE
          WHERE BDE.DELETE_GROUP_SEQUENCE_ID = delete_group_id
          AND BDE.DELETE_ENTITY_SEQUENCE_ID = BDSE.DELETE_ENTITY_SEQUENCE_ID);

Line: 664

      ELSIF delete_type = 5 THEN
        INSERT INTO BOM_DELETE_ENTITIES(
          DELETE_ENTITY_SEQUENCE_ID,
          DELETE_GROUP_SEQUENCE_ID,
          DELETE_ENTITY_TYPE,
          INVENTORY_ITEM_ID,
          ORGANIZATION_ID,
          ALTERNATE_DESIGNATOR,
          ITEM_DESCRIPTION,
          ITEM_CONCAT_SEGMENTS,
          PRIOR_PROCESS_FLAG,
          PRIOR_COMMIT_FLAG,
          ROUTING_SEQUENCE_ID,
          LAST_UPDATE_DATE,
          LAST_UPDATED_BY,
          CREATION_DATE,
          CREATED_BY,
          LAST_UPDATE_LOGIN)
        SELECT
          BOM_DELETE_ENTITIES_S.NEXTVAL,
          delete_group_id,
          3,
          BOR.ASSEMBLY_ITEM_ID,
          BOR.ORGANIZATION_ID,
          BOR.ALTERNATE_ROUTING_DESIGNATOR,
          MSI.DESCRIPTION,
          MIF.ITEM_NUMBER,
          1,
          commit_flag,
          BOR.ROUTING_SEQUENCE_ID,
          SYSDATE,
          userid,
          SYSDATE,
          userid,
          last_login_id
        FROM
          BOM_OPERATIONAL_ROUTINGS BOR,
          MTL_SYSTEM_ITEMS MSI,
          MTL_ITEM_FLEXFIELDS MIF
        WHERE BOR.ORGANIZATION_ID = org_id
        AND MSI.INVENTORY_ITEM_ID = BOR.ASSEMBLY_ITEM_ID
        AND MSI.ORGANIZATION_ID = BOR.ORGANIZATION_ID
        AND MIF.ITEM_ID = BOR.ASSEMBLY_ITEM_ID
        AND MIF.ORGANIZATION_ID = BOR.ORGANIZATION_ID
        AND BOR.ROUTING_TYPE = eng_flag
        AND BOR.ROUTING_SEQUENCE_ID IN(
          SELECT DISTINCT BOS.ROUTING_SEQUENCE_ID
          FROM
            BOM_OPERATIONAL_ROUTINGS BOR,
            BOM_OPERATION_SEQUENCES BOS
          WHERE BOR.ROUTING_SEQUENCE_ID = BOS.ROUTING_SEQUENCE_ID
          AND BOS.DISABLE_DATE <= expiration_date
          AND BOS.ROUTING_SEQUENCE_ID NOT IN(
            SELECT NVL(ROUTING_SEQUENCE_ID, 0)
            FROM BOM_DELETE_ENTITIES
            WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id));

Line: 721

        INSERT INTO BOM_DELETE_SUB_ENTITIES(
          DELETE_ENTITY_SEQUENCE_ID,
          OPERATION_SEQUENCE_ID,
          OPERATION_SEQ_NUM,
          EFFECTIVITY_DATE,
          DISABLE_DATE,
          DESCRIPTION,
          OPERATION_DEPARTMENT_CODE,
          DELETE_STATUS_TYPE,
          LAST_UPDATE_DATE,
          LAST_UPDATED_BY,
          CREATION_DATE,
          CREATED_BY,
          LAST_UPDATE_LOGIN)
        SELECT
          BDE.DELETE_ENTITY_SEQUENCE_ID,
          BOS.OPERATION_SEQUENCE_ID,
          BOS.OPERATION_SEQ_NUM,
          BOS.EFFECTIVITY_DATE,
          BOS.DISABLE_DATE,
          BOS.OPERATION_DESCRIPTION,
          BD.DEPARTMENT_CODE,
          1,
          SYSDATE,
          userid,
          SYSDATE,
          userid,
          last_login_id
        FROM
          BOM_OPERATION_SEQUENCES BOS,
          BOM_DELETE_ENTITIES BDE,
          BOM_OPERATIONAL_ROUTINGS BOR,
          BOM_DEPARTMENTS BD
        WHERE BOR.ROUTING_SEQUENCE_ID = BOS.ROUTING_SEQUENCE_ID
        AND BDE.ROUTING_SEQUENCE_ID = BOS.ROUTING_SEQUENCE_ID
        AND BOR.ROUTING_TYPE = eng_flag
        AND BDE.DELETE_GROUP_SEQUENCE_ID = delete_group_id
        AND BOS.DEPARTMENT_ID = BD.DEPARTMENT_ID
        AND BOS.DISABLE_DATE <= expiration_date
        AND BOS.OPERATION_SEQUENCE_ID NOT IN(
          SELECT NVL(OPERATION_SEQUENCE_ID, 0)
          FROM
            BOM_DELETE_SUB_ENTITIES BDSE,
            BOM_DELETE_ENTITIES BDE
          WHERE BDE.DELETE_GROUP_SEQUENCE_ID = delete_group_id
          AND BDE.DELETE_ENTITY_SEQUENCE_ID = BDSE.DELETE_ENTITY_SEQUENCE_ID);

Line: 769

        var_text := 'delete type';

Line: 783

  Because of the New Functionality to delete Items from single org,
Organization Hierarchy and all Organizations, The following code is
not required as we need to give an error whenever item is being deleted
from master org when it still exists in child Organizations ,
 instead of deleting items from all the child Organizations also
Commenting the Following Code
*/

/*
  stmt_num := 6;

Line: 793

  IF delete_type IN (1, 7) THEN
  DECLARE
    CURSOR GetMasterOrg IS
    SELECT DECODE(ORGANIZATION_ID, MASTER_ORGANIZATION_ID, 'Y', 'N')
           master_org_flag
    FROM MTL_PARAMETERS
    WHERE ORGANIZATION_ID = org_id;

Line: 803

        IF delete_type = 1 THEN
          UPDATE_UNPROCESSED_ROWS(delete_group_id);

Line: 810

        SELECT DELETE_ENTITY_SEQUENCE_ID, INVENTORY_ITEM_ID
        FROM BOM_DELETE_ENTITIES
        WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id
        AND PRIOR_PROCESS_FLAG = 2;

Line: 818

          INSERT INTO BOM_DELETE_ENTITIES(
            DELETE_ENTITY_SEQUENCE_ID,
            DELETE_GROUP_SEQUENCE_ID,
            DELETE_ENTITY_TYPE,
            INVENTORY_ITEM_ID,
            ORGANIZATION_ID,
            ITEM_DESCRIPTION,
            ITEM_CONCAT_SEGMENTS,
            DELETE_STATUS_TYPE,
            PRIOR_PROCESS_FLAG,
            PRIOR_COMMIT_FLAG,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            CREATION_DATE,
            CREATED_BY)
          SELECT
            BOM_DELETE_ENTITIES_S.NEXTVAL,
            delete_group_id,
            1,
            MSI.INVENTORY_ITEM_ID,
            MSI.ORGANIZATION_ID,
            MSI.DESCRIPTION,
            MIF.ITEM_NUMBER,
            1,
            process_flag,
            commit_flag,
            SYSDATE,
            userid,
            SYSDATE,
            userid
          FROM
            MTL_SYSTEM_ITEMS MSI,
            MTL_ITEM_FLEXFIELDS MIF
          WHERE MSI.INVENTORY_ITEM_ID = GetDelEntitiesREC.INVENTORY_ITEM_ID
          AND MSI.ENG_ITEM_FLAG = item_eng_flag
          AND MIF.ITEM_ID = MSI.INVENTORY_ITEM_ID
          AND MIF.ORGANIZATION_ID = MSI.ORGANIZATION_ID
          AND MSI.ORGANIZATION_ID IN(
            SELECT ORGANIZATION_ID
            FROM MTL_PARAMETERS
            WHERE MASTER_ORGANIZATION_ID = org_id
            AND ORGANIZATION_ID <> MASTER_ORGANIZATION_ID)
            AND MSI.ORGANIZATION_ID NOT IN(
              SELECT DISTINCT ORGANIZATION_ID
              FROM BOM_DELETE_ENTITIES
              WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id
              AND INVENTORY_ITEM_ID = GetDelEntitiesREC.INVENTORY_ITEM_ID);

Line: 866

        IF delete_type = 1 THEN
          UPDATE_UNPROCESSED_ROWS(delete_group_id);

Line: 876

  IF delete_type IN (6, 7) THEN
  DECLARE
    CURSOR BR_GetDelEntities IS
    SELECT DELETE_ENTITY_SEQUENCE_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID
    FROM BOM_DELETE_ENTITIES
    WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id
    AND PRIOR_PROCESS_FLAG = 2;

Line: 888

      INSERT INTO BOM_DELETE_ENTITIES(
        DELETE_ENTITY_SEQUENCE_ID,
        DELETE_GROUP_SEQUENCE_ID,
        DELETE_ENTITY_TYPE,
        INVENTORY_ITEM_ID,
        ORGANIZATION_ID,
        ALTERNATE_DESIGNATOR,
        ITEM_DESCRIPTION,
        ITEM_CONCAT_SEGMENTS,
        DELETE_STATUS_TYPE,
        PRIOR_PROCESS_FLAG,
        PRIOR_COMMIT_FLAG,
        BILL_SEQUENCE_ID,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY)
      SELECT
        BOM_DELETE_ENTITIES_S.NEXTVAL,
        delete_group_id,
        2,
        BOM.ASSEMBLY_ITEM_ID,
        BOM.ORGANIZATION_ID,
        BOM.ALTERNATE_BOM_DESIGNATOR,
        MSI.DESCRIPTION,
        MIF.ITEM_NUMBER,
        1,
        1,
        commit_flag,
        BOM.BILL_SEQUENCE_ID,
        SYSDATE,
        userid,
        SYSDATE,
        userid
      FROM
        BOM_BILL_OF_MATERIALS BOM,
        MTL_SYSTEM_ITEMS MSI,
        MTL_ITEM_FLEXFIELDS MIF
      WHERE ASSEMBLY_ITEM_ID = BR_GetDelEntitiesREC.INVENTORY_ITEM_ID
      AND BOM.ORGANIZATION_ID  = BR_GetDelEntitiesREC.ORGANIZATION_ID
      AND MSI.INVENTORY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
      AND MSI.ORGANIZATION_ID = BOM.ORGANIZATION_ID
      AND MIF.ITEM_ID = BOM.ASSEMBLY_ITEM_ID
      AND MIF.ORGANIZATION_ID = BOM.ORGANIZATION_ID
      AND BOM.ASSEMBLY_TYPE = eng_flag
      AND BOM.BILL_SEQUENCE_ID NOT IN(
        SELECT NVL(BILL_SEQUENCE_ID, 0)
        FROM BOM_DELETE_ENTITIES
        WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id);

Line: 940

      INSERT INTO BOM_DELETE_ENTITIES(
        DELETE_ENTITY_SEQUENCE_ID,
        DELETE_GROUP_SEQUENCE_ID,
        DELETE_ENTITY_TYPE,
        INVENTORY_ITEM_ID,
        ORGANIZATION_ID,
        ALTERNATE_DESIGNATOR,
        ITEM_DESCRIPTION,
        ITEM_CONCAT_SEGMENTS,
        DELETE_STATUS_TYPE,
        PRIOR_PROCESS_FLAG,
        PRIOR_COMMIT_FLAG,
        ROUTING_SEQUENCE_ID,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY)
      SELECT
        BOM_DELETE_ENTITIES_S.NEXTVAL,
        delete_group_id,
        3,
        BOR.ASSEMBLY_ITEM_ID,
        BOR.ORGANIZATION_ID,
        BOR.ALTERNATE_ROUTING_DESIGNATOR,
        MSI.DESCRIPTION,
        MIF.ITEM_NUMBER,
        1,
        1,
        commit_flag,
        BOR.ROUTING_SEQUENCE_ID,
        SYSDATE,
        userid,
        SYSDATE,
        userid
      FROM
        BOM_OPERATIONAL_ROUTINGS BOR,
        MTL_SYSTEM_ITEMS MSI,
        MTL_ITEM_FLEXFIELDS MIF
      WHERE ASSEMBLY_ITEM_ID = BR_GetDelEntitiesREC.INVENTORY_ITEM_ID
      AND BOR.ORGANIZATION_ID  = BR_GetDelEntitiesREC.ORGANIZATION_ID
      AND MSI.INVENTORY_ITEM_ID = BOR.ASSEMBLY_ITEM_ID
      AND MSI.ORGANIZATION_ID = BOR.ORGANIZATION_ID
      AND MIF.ITEM_ID = BOR.ASSEMBLY_ITEM_ID
      AND MIF.ORGANIZATION_ID = BOR.ORGANIZATION_ID
      AND BOR.ROUTING_TYPE = eng_flag
      AND BOR.ROUTING_SEQUENCE_ID NOT IN(
        SELECT NVL(ROUTING_SEQUENCE_ID, 0)
        FROM BOM_DELETE_ENTITIES
        WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id);

Line: 993

      IF delete_type = 6 THEN
        DELETE
        FROM BOM_DELETE_ENTITIES
        WHERE DELETE_ENTITY_SEQUENCE_ID =
              BR_GetDelEntitiesREC.DELETE_ENTITY_SEQUENCE_ID;

Line: 1004

       IF delete_type = 7 THEN
         UPDATE_UNPROCESSED_ROWS(delete_group_id);

Line: 1009

  ELSIF delete_type IN (2, 3, 4, 5) THEN
    UPDATE_UNPROCESSED_ROWS(delete_group_id);

Line: 1012

  ELSIF delete_type = 1 THEN
    null;

Line: 1015

    var_text := 'delete type';

Line: 1021

    FND_MESSAGE.SET_NAME('BOM','BOM_DELETE_ITEMS_PARAM');

Line: 1028

      func_name => 'P0PULATE_DELETE',
      stmt_num  => stmt_num);

Line: 1031

END POPULATE_DELETE;