APPS.ENI_DENORM_HRCHY SQL Statements

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

Line: 13

  SELECT CATEGORY_SET_ID INTO l_catset_id
  FROM MTL_DEFAULT_CATEGORY_SETS
  WHERE FUNCTIONAL_AREA_ID = g_func_area_id;

Line: 29

  SELECT 1 INTO l_count
  FROM ALL_OBJECTS
  WHERE OBJECT_NAME = 'ENI_OLTP_ITEM_STAR'
    AND OBJECT_TYPE = 'TABLE'
    AND OWNER = g_tab_schema;

Line: 42

FUNCTION GET_LAST_CATALOG_UPDATE_DATE RETURN DATE IS
  l_date DATE;

Line: 45

  SELECT MAX(LAST_UPDATE_DATE) INTO l_date
  FROM ENI_DENORM_HIERARCHIES
  WHERE OBJECT_ID = g_catset_id
    AND OBJECT_TYPE = 'CATEGORY_SET';

Line: 51

END GET_LAST_CATALOG_UPDATE_DATE;

Line: 57

PROCEDURE INSERT_INTO_STAGING(
      p_object_type     IN VARCHAR2,
      p_object_id       IN NUMBER,
      p_child_id        IN NUMBER,
      p_parent_id       IN NUMBER,
      p_mode_flag       IN VARCHAR2,
      x_return_status   OUT NOCOPY VARCHAR2,
      x_msg_count       OUT NOCOPY NUMBER,
      x_msg_data        OUT NOCOPY VARCHAR2,
      p_language_code   IN VARCHAR2 DEFAULT NULL) IS

  l_language_code VARCHAR2(4);

Line: 75

      FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Invalid Mode Flag');

Line: 84

      FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Invalid Object Type. Must be CATEGORY_SET');

Line: 93

      FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Object ID can not be NULL.');

Line: 102

      FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Child Category ID can not be NULL.');

Line: 111

    UPDATE ENI_DENORM_HRCHY_STG
    SET PARENT_ID = p_parent_id,
        MODE_FLAG = DECODE(p_mode_flag, 'A', DECODE(MODE_FLAG, 'D', 'M', 'A'), p_mode_flag)
    WHERE OBJECT_TYPE = p_object_type
      AND OBJECT_ID = p_object_id
      AND CHILD_ID = p_child_id
      AND BATCH_FLAG = 'NEXT_BATCH';

Line: 120

      INSERT INTO ENI_DENORM_HRCHY_STG (
        OBJECT_TYPE,
        OBJECT_ID,
        CHILD_ID,
        PARENT_ID,
        MODE_FLAG,
        BATCH_FLAG)
      VALUES (
        p_object_type,
        p_object_id,
        p_child_id,
        p_parent_id,
        p_mode_flag,
        'NEXT_BATCH');

Line: 141

    UPDATE ENI_DENORM_HRCHY_STG
    SET PARENT_ID = p_parent_id,
        MODE_FLAG = DECODE(MODE_FLAG, 'S', DECODE(p_mode_flag, 'E', 'S', 'S', 'U', p_mode_flag),
                                      'U', DECODE(p_mode_flag, 'E', 'U', 'S', 'S', p_mode_flag), p_mode_flag)
    WHERE OBJECT_TYPE = p_object_type
      AND OBJECT_ID = p_object_id
      AND CHILD_ID = p_child_id
      AND MODE_FLAG IN ('S', 'U', 'E', 'C')
      AND BATCH_FLAG = 'NEXT_BATCH';

Line: 152

      INSERT INTO ENI_DENORM_HRCHY_STG (
        OBJECT_TYPE,
        OBJECT_ID,
        CHILD_ID,
        PARENT_ID,
        MODE_FLAG,
        BATCH_FLAG)
      VALUES (
        p_object_type,
        p_object_id,
        p_child_id,
        p_parent_id,
        p_mode_flag,
        'NEXT_BATCH');

Line: 176

        SELECT 1 INTO l_count
        FROM MTL_CATEGORIES_TL
        WHERE CATEGORY_ID = p_child_id
          AND SOURCE_LANG = p_language_code;

Line: 188

            FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Invalid Category ID, Language combination');

Line: 197

      select userenv('LANG') into l_language_code from dual;

Line: 200

    UPDATE ENI_DENORM_HRCHY_STG
    SET PARENT_ID = p_parent_id,
        MODE_FLAG = DECODE(MODE_FLAG, 'S', 'S', 'U', 'U', 'E', 'E', p_mode_flag),
        LANGUAGE_CODE = DECODE(MODE_FLAG, 'C', DECODE(LANGUAGE_CODE, l_language_code, l_language_code, NULL), NULL)
    WHERE OBJECT_TYPE = p_object_type
      AND OBJECT_ID = p_object_id
      AND CHILD_ID = p_child_id
      AND MODE_FLAG IN ('S', 'U', 'E', 'C')
      AND BATCH_FLAG = 'NEXT_BATCH';

Line: 211

      INSERT INTO ENI_DENORM_HRCHY_STG (
        OBJECT_TYPE,
        OBJECT_ID,
        CHILD_ID,
        PARENT_ID,
        MODE_FLAG,
        BATCH_FLAG,
        LANGUAGE_CODE)
      VALUES (
        p_object_type,
        p_object_id,
        p_child_id,
        p_parent_id,
        p_mode_flag,
        'NEXT_BATCH',
        l_language_code);

Line: 236

    FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', SQLERRM);

Line: 239

END INSERT_INTO_STAGING;

Line: 246

    SELECT TL.LANGUAGE_CODE, C.CATEGORY_ID, B.DISABLE_DATE
    FROM MTL_CATEGORY_SET_VALID_CATS C, MTL_CATEGORIES_B B, FND_LANGUAGES TL
    WHERE C.CATEGORY_SET_ID = g_catset_id
      AND TL.INSTALLED_FLAG IN ('I', 'B')
      AND B.CATEGORY_ID = C.CATEGORY_ID
      AND NOT EXISTS (SELECT NULL FROM EGO_PROD_CAT_SALES_MARKET_AGV A
                      WHERE A.CATEGORY_SET_ID = g_catset_id
                      AND A.CATEGORY_ID = C.CATEGORY_ID
                      AND NVL(A.EXCLUDE_USER_VIEW, 'N') = 'Y');

Line: 257

    SELECT TL.DESCRIPTION CHILD_DESC, C.CATEGORY_ID CHILD_ID
    FROM MTL_CATEGORIES_TL TL,
      (SELECT
         CATEGORY_ID, LEVEL hrchy
       FROM MTL_CATEGORY_SET_VALID_CATS
       START WITH CATEGORY_ID = l_child_id AND CATEGORY_SET_ID = g_catset_id
       CONNECT BY PRIOR PARENT_CATEGORY_ID = CATEGORY_ID AND CATEGORY_SET_ID = g_catset_id) C
    WHERE C.CATEGORY_ID = TL.CATEGORY_ID
      AND TL.LANGUAGE = l_language
      AND NOT EXISTS (SELECT NULL FROM EGO_PROD_CAT_SALES_MARKET_AGV A
                      WHERE A.CATEGORY_SET_ID = g_catset_id
                      AND A.CATEGORY_ID = C.CATEGORY_ID
                      AND NVL(A.EXCLUDE_USER_VIEW, 'N') = 'Y')
		      ORDER BY hrchy ASC; -- Bug 4749088

Line: 291

    SELECT ATTR_GROUP_ID INTO l_attr_grp_id
    FROM EGO_FND_DSC_FLX_CTX_EXT
    WHERE APPLICATION_ID = 431
      AND DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_PRODUCT_CATEGORY_SET'
      AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'SalesAndMarketing';

Line: 303

  DELETE ENI_DENORM_HRCHY_PARENTS;

Line: 341

    INSERT INTO ENI_DENORM_HRCHY_PARENTS (
      OBJECT_TYPE,
      OBJECT_ID,
      ATTRIBUTE_GROUP_ID,
      CATEGORY_ID,
      LANGUAGE,
      CATEGORY_DESC,
      CONCAT_CAT_PARENTAGE,
      CATEGORY_LEVEL_NUM,
      DISABLE_DATE,
      CATEGORY_PARENT_ID,
      CREATED_BY,
      CREATION_DATE,
      LAST_UPDATED_BY,
      LAST_UPDATE_DATE,
      LAST_UPDATE_LOGIN,
      REQUEST_ID,
      PROGRAM_APPLICATION_ID,
      PROGRAM_UPDATE_DATE,
      PROGRAM_ID)
    VALUES (
      'CATEGORY_SET',
      g_catset_id,
      l_attr_grp_id,
      i.CATEGORY_ID,
      i.LANGUAGE_CODE,
      l_desc,
      RTRIM(l_concat_desc, '/'),
      l_eff_level,
      i.DISABLE_DATE,
      l_imm_par_id,
      l_user_id,
      SYSDATE,
      l_user_id,
      SYSDATE,
      l_user_id,
      l_conc_request_id,
      l_prog_appl_id,
      SYSDATE,
      l_conc_program_id);

Line: 385

  FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' records inserted into Denorm Hierarchy Parents table.');

Line: 415

    SELECT TL.LANGUAGE_CODE, S.CHILD_ID, S.MODE_FLAG, S.LANGUAGE_CODE STG_LANG
    FROM ENI_DENORM_HRCHY_STG S, FND_LANGUAGES TL
    WHERE S.OBJECT_ID = g_catset_id
      AND S.OBJECT_TYPE = 'CATEGORY_SET'
      AND TL.INSTALLED_FLAG IN ('I', 'B')
      AND S.MODE_FLAG <> 'D'
      AND S.BATCH_FLAG <> 'NEXT_BATCH';

Line: 424

    SELECT D.CATEGORY_ID, B.DISABLE_DATE FROM
      (SELECT C.CATEGORY_ID
       FROM MTL_CATEGORY_SET_VALID_CATS C
       WHERE NOT EXISTS (SELECT NULL FROM EGO_PROD_CAT_SALES_MARKET_AGV A
                         WHERE A.CATEGORY_SET_ID = g_catset_id
                           AND A.CATEGORY_ID = C.CATEGORY_ID
                           AND NVL(A.EXCLUDE_USER_VIEW, 'N') = 'Y')
       START WITH CATEGORY_ID = p_child AND CATEGORY_SET_ID = g_catset_id
       CONNECT BY PARENT_CATEGORY_ID = PRIOR CATEGORY_ID AND CATEGORY_SET_ID = g_catset_id) D, MTL_CATEGORIES_B B
    WHERE B.CATEGORY_ID = D.CATEGORY_ID;

Line: 436

    SELECT TL.DESCRIPTION CHILD_DESC, C.CATEGORY_ID CHILD_ID
    FROM MTL_CATEGORIES_TL TL,
      (SELECT
        CATEGORY_ID, LEVEL hrchy
       FROM MTL_CATEGORY_SET_VALID_CATS
       START WITH CATEGORY_ID = l_child_id AND CATEGORY_SET_ID = g_catset_id
       CONNECT BY PRIOR PARENT_CATEGORY_ID = CATEGORY_ID AND CATEGORY_SET_ID = g_catset_id) C
    WHERE C.CATEGORY_ID = TL.CATEGORY_ID
      AND TL.LANGUAGE = l_language
      AND NOT EXISTS (SELECT NULL FROM EGO_PROD_CAT_SALES_MARKET_AGV A
                      WHERE A.CATEGORY_SET_ID = g_catset_id
                      AND A.CATEGORY_ID = C.CATEGORY_ID
                      AND NVL(A.EXCLUDE_USER_VIEW, 'N') = 'Y')
      		      ORDER BY hrchy ASC; -- Bug 4749088

Line: 469

    SELECT ATTR_GROUP_ID INTO l_attr_grp_id
    FROM EGO_FND_DSC_FLX_CTX_EXT
    WHERE APPLICATION_ID = 431
      AND DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_PRODUCT_CATEGORY_SET'
      AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'SalesAndMarketing';

Line: 484

  DELETE FROM ENI_DENORM_HRCHY_PARENTS B
  WHERE NOT EXISTS (SELECT NULL FROM FND_LANGUAGES L
                    WHERE L.INSTALLED_FLAG IN ('I', 'B')
                      AND B.LANGUAGE = L.LANGUAGE_CODE);

Line: 490

  FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' records deleted.');

Line: 493

  FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleting all categories which are deleted from hierarchy OR excluded from user view.');

Line: 495

  DELETE FROM ENI_DENORM_HRCHY_PARENTS B
  WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
    AND B.OBJECT_ID = g_catset_id
    AND EXISTS (SELECT NULL FROM ENI_DENORM_HRCHY_STG S
                WHERE S.OBJECT_TYPE = 'CATEGORY_SET'
                  AND S.OBJECT_ID = g_catset_id
                  AND S.CHILD_ID = B.CATEGORY_ID
                  AND S.MODE_FLAG IN ('D', 'S')
                  AND S.BATCH_FLAG <> 'NEXT_BATCH');

Line: 506

  FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' records deleted.');

Line: 508

  FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting/Updating into Denorm Hierarchy Parents table');

Line: 546

        UPDATE ENI_DENORM_HRCHY_PARENTS B
        SET CATEGORY_DESC = l_desc,
            CONCAT_CAT_PARENTAGE = RTRIM(l_concat_desc, '/'),
            CATEGORY_LEVEL_NUM = l_eff_level,
            DISABLE_DATE = j.DISABLE_DATE,
            CATEGORY_PARENT_ID = l_imm_par_id,
            LAST_UPDATED_BY = l_user_id,
            LAST_UPDATE_DATE = SYSDATE,
            LAST_UPDATE_LOGIN = l_user_id,
            REQUEST_ID = l_conc_request_id,
            PROGRAM_APPLICATION_ID = l_prog_appl_id,
            PROGRAM_UPDATE_DATE = SYSDATE,
            PROGRAM_ID = l_conc_program_id
        WHERE OBJECT_TYPE = 'CATEGORY_SET'
          AND OBJECT_ID = g_catset_id
          AND CATEGORY_ID = j.CATEGORY_ID
          AND LANGUAGE = i.LANGUAGE_CODE;

Line: 565

          INSERT INTO ENI_DENORM_HRCHY_PARENTS (
            OBJECT_TYPE,
            OBJECT_ID,
            ATTRIBUTE_GROUP_ID,
            CATEGORY_ID,
            LANGUAGE,
            CATEGORY_DESC,
            CONCAT_CAT_PARENTAGE,
            CATEGORY_LEVEL_NUM,
            DISABLE_DATE,
            CATEGORY_PARENT_ID,
            CREATED_BY,
            CREATION_DATE,
            LAST_UPDATED_BY,
            LAST_UPDATE_DATE,
            LAST_UPDATE_LOGIN,
            REQUEST_ID,
            PROGRAM_APPLICATION_ID,
            PROGRAM_UPDATE_DATE,
            PROGRAM_ID)
          VALUES (
            'CATEGORY_SET',
            g_catset_id,
            l_attr_grp_id,
            j.CATEGORY_ID,
            i.LANGUAGE_CODE,
            l_desc,
            RTRIM(l_concat_desc, '/'),
            l_eff_level,
            j.DISABLE_DATE,
            l_imm_par_id,
            l_user_id,
            SYSDATE,
            l_user_id,
            SYSDATE,
            l_user_id,
            l_conc_request_id,
            l_prog_appl_id,
            SYSDATE,
            l_conc_program_id);

Line: 611

  FND_FILE.PUT_LINE(FND_FILE.LOG, l_count || ' records inserted/updated into Denorm Hierarchy Parents table');

Line: 616

    UPDATE ENI_DENORM_HRCHY_STG
    SET BATCH_FLAG = 'NEXT_BATCH'
    WHERE BATCH_FLAG <> 'NEXT_BATCH'
      AND OBJECT_TYPE = 'CATEGORY_SET'
      AND OBJECT_ID = g_catset_id;

Line: 637

    SELECT
      T.PARENT_CATEGORY_ID     PARENT_ID,
      T.CATEGORY_ID            CHILD_ID,
      D.TOP_NODE_FLAG,
      D1.LEAF_NODE_FLAG
    FROM MTL_CATEGORY_SET_VALID_CATS T, ENI_DENORM_HIERARCHIES D, ENI_DENORM_HIERARCHIES D1
    WHERE T.CATEGORY_SET_ID = g_catset_id
      AND T.PARENT_CATEGORY_ID IS NOT NULL
      AND D.OBJECT_TYPE = 'CATEGORY_SET'
      AND D.OBJECT_ID = g_catset_id
      AND D.PARENT_ID = T.PARENT_CATEGORY_ID
      AND D.CHILD_ID = T.PARENT_CATEGORY_ID
      AND D1.OBJECT_TYPE = 'CATEGORY_SET'
      AND D1.OBJECT_ID = g_catset_id
      AND D1.PARENT_ID = T.CATEGORY_ID
      AND D1.CHILD_ID = T.CATEGORY_ID;

Line: 671

    SELECT HIERARCHY_ENABLED, VALIDATE_FLAG, STRUCTURE_ID INTO l_hrchy_enabled, l_validate_flag, l_struct_id
    FROM MTL_CATEGORY_SETS_B
    WHERE CATEGORY_SET_ID = g_catset_id;

Line: 682

  DELETE FROM ENI_DENORM_HIERARCHIES
  WHERE OBJECT_TYPE = 'CATEGORY_SET';

Line: 689

  DELETE FROM ENI_DENORM_HRCHY_STG
  WHERE OBJECT_TYPE = 'CATEGORY_SET'
    AND OBJECT_ID = g_catset_id;

Line: 694

  FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' records deleted from Staging table');

Line: 701

    FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserting Self Referencing Nodes');

Line: 703

    INSERT INTO ENI_DENORM_HIERARCHIES(
      PARENT_ID,
      IMM_CHILD_ID,
      CHILD_ID,
      OBJECT_TYPE,
      OBJECT_ID,
      TOP_NODE_FLAG,
      LEAF_NODE_FLAG,
      ITEM_ASSGN_FLAG,
      DBI_FLAG,
      OLTP_FLAG,
      CREATED_BY,
      CREATION_DATE,
      LAST_UPDATED_BY,
      LAST_UPDATE_DATE,
      LAST_UPDATE_LOGIN,
      REQUEST_ID,
      PROGRAM_APPLICATION_ID,
      PROGRAM_UPDATE_DATE,
      PROGRAM_ID)
    SELECT
      CATEGORY_ID,
      CATEGORY_ID,
      CATEGORY_ID,
      'CATEGORY_SET',
      g_catset_id,
      'Y' TOP_NODE_FLAG,
      'Y' LEAF_NODE_FLAG,
      'N' ITEM_ASSGN_FLAG,
      'Y' DBI_FLAG,
      'Y' OLTP_FLAG,
      l_user_id,
      SYSDATE,
      l_user_id,
      SYSDATE,
      l_user_id,
      l_conc_request_id,
      l_prog_appl_id,
      SYSDATE,
      l_conc_program_id
    FROM MTL_CATEGORIES_B
    WHERE STRUCTURE_ID = l_struct_id;

Line: 747

    FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserting Self Referencing Nodes');

Line: 751

    'INSERT INTO ENI_DENORM_HIERARCHIES(
      PARENT_ID,
      IMM_CHILD_ID,
      CHILD_ID,
      OBJECT_TYPE,
      OBJECT_ID,
      TOP_NODE_FLAG,
      LEAF_NODE_FLAG,
      ITEM_ASSGN_FLAG,
      DBI_FLAG,
      OLTP_FLAG,
      CREATED_BY,
      CREATION_DATE,
      LAST_UPDATED_BY,
      LAST_UPDATE_DATE,
      LAST_UPDATE_LOGIN,
      REQUEST_ID,
      PROGRAM_APPLICATION_ID,
      PROGRAM_UPDATE_DATE,
      PROGRAM_ID)
    SELECT
      T.CATEGORY_ID,
      T.CATEGORY_ID,
      T.CATEGORY_ID,
      ''CATEGORY_SET'',
      :g_catset_id,
      DECODE(:l_hrchy_enabled, ''Y'', DECODE(T.PARENT_CATEGORY_ID, NULL, ''Y'', ''N''), ''Y''),
      NVL((SELECT ''N'' FROM MTL_CATEGORY_SET_VALID_CATS X
           WHERE X.CATEGORY_SET_ID = T.CATEGORY_SET_ID
             AND X.PARENT_CATEGORY_ID = T.CATEGORY_ID
            AND ROWNUM = 1), ''Y'') LEAF_NODE_FLAG,
      ''N'',
      ''Y'',
      ''Y'',
      :l_user_id,
      SYSDATE,
      :l_user_id,
      SYSDATE,
      :l_user_id,
      :l_conc_request_id,
      :l_prog_appl_id,
      SYSDATE,
      :l_conc_program_id
    FROM MTL_CATEGORY_SET_VALID_CATS T
    WHERE T.CATEGORY_SET_ID = :g_catset_id';

Line: 803

  FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserted '||l_count||' Self-referencing records');

Line: 809

    FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserting Hierarchical records');

Line: 812

      INSERT INTO ENI_DENORM_HIERARCHIES (
        PARENT_ID,
        IMM_CHILD_ID,
        CHILD_ID,
        OBJECT_TYPE,
        OBJECT_ID,
        TOP_NODE_FLAG,
        LEAF_NODE_FLAG,
        ITEM_ASSGN_FLAG,
        DBI_FLAG,
        OLTP_FLAG,
        CREATED_BY,
        CREATION_DATE,
        LAST_UPDATED_BY,
        LAST_UPDATE_DATE,
        LAST_UPDATE_LOGIN,
        REQUEST_ID,
        PROGRAM_APPLICATION_ID,
        PROGRAM_UPDATE_DATE,
        PROGRAM_ID)
      SELECT
        i.PARENT_ID,
        i.CHILD_ID,
        A.CATEGORY_ID,
        'CATEGORY_SET',
        g_catset_id,
        i.TOP_NODE_FLAG,
        i.LEAF_NODE_FLAG,
        'N',
        'Y',
        'Y',
        l_user_id,
        SYSDATE,
        l_user_id,
        SYSDATE,
        l_user_id,
        l_conc_request_id,
        l_prog_appl_id,
        SYSDATE,
        l_conc_program_id
      FROM MTL_CATEGORY_SET_VALID_CATS A
      START WITH A.CATEGORY_ID = i.CHILD_ID AND A.CATEGORY_SET_ID = g_catset_id
      CONNECT BY A.PARENT_CATEGORY_ID = PRIOR A.CATEGORY_ID AND A.CATEGORY_SET_ID = g_catset_id;

Line: 859

    FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserted '||l_count||' Hierarchical records');

Line: 865

  UPDATE ENI_DENORM_HIERARCHIES B
  SET ITEM_ASSGN_FLAG = 'Y'
  WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
    AND B.OBJECT_ID = g_catset_id
    AND EXISTS (SELECT NULL
                FROM MTL_ITEM_CATEGORIES C
                WHERE C.CATEGORY_SET_ID = g_catset_id
                  AND C.CATEGORY_ID = B.CHILD_ID);

Line: 876

  FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records Updated for Item Assignment Flag');

Line: 883

    SELECT 1 INTO l_count
    FROM MTL_SYSTEM_ITEMS_B IT
    WHERE ROWNUM = 1
      AND NOT EXISTS (SELECT NULL FROM MTL_ITEM_CATEGORIES C
                      WHERE C.CATEGORY_SET_ID = g_catset_id
                        AND C.INVENTORY_ITEM_ID = IT.INVENTORY_ITEM_ID
                        AND C.ORGANIZATION_ID = IT.ORGANIZATION_ID);

Line: 900

  FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting Unassigned Node');

Line: 901

  INSERT INTO ENI_DENORM_HIERARCHIES (
    PARENT_ID,
    IMM_CHILD_ID,
    CHILD_ID,
    OBJECT_TYPE,
    OBJECT_ID,
    TOP_NODE_FLAG,
    LEAF_NODE_FLAG,
    ITEM_ASSGN_FLAG,
    DBI_FLAG,
    OLTP_FLAG,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATE_LOGIN,
    REQUEST_ID,
    PROGRAM_APPLICATION_ID,
    PROGRAM_UPDATE_DATE,
    PROGRAM_ID)
  VALUES(
    -1,
    -1,
    -1,
    'CATEGORY_SET',
    g_catset_id,
    'Y',
    'Y',
    DECODE(l_count, 1, 'Y', 'N'),
    'Y',
    'N',
    l_user_id,
    SYSDATE,
    l_user_id,
    SYSDATE,
    l_user_id,
    l_conc_request_id,
    l_prog_appl_id,
    SYSDATE,
    l_conc_program_id);

Line: 969

  SELECT DISTINCT TOP_NODE_ID
  FROM ENI_DENORM_HRCHY_STG
  WHERE BATCH_FLAG <> 'NEXT_BATCH'
    AND OBJECT_TYPE = 'CATEGORY_SET'
    AND OBJECT_ID = g_catset_id
    AND MODE_FLAG IN ('A', 'M');  -- modified for sales and marketing enhancement

Line: 990

    SELECT VALIDATE_FLAG, STRUCTURE_ID INTO l_validate_flag, l_struct_id
    FROM MTL_CATEGORY_SETS_B
    WHERE CATEGORY_SET_ID = g_catset_id;

Line: 1001

    INSERT INTO ENI_DENORM_HIERARCHIES(
      PARENT_ID,
      IMM_CHILD_ID,
      CHILD_ID,
      OBJECT_TYPE,
      OBJECT_ID,
      TOP_NODE_FLAG,
      LEAF_NODE_FLAG,
      ITEM_ASSGN_FLAG,
      DBI_FLAG,
      OLTP_FLAG,
      CREATED_BY,
      CREATION_DATE,
      LAST_UPDATED_BY,
      LAST_UPDATE_DATE,
      LAST_UPDATE_LOGIN,
      REQUEST_ID,
      PROGRAM_APPLICATION_ID,
      PROGRAM_UPDATE_DATE,
      PROGRAM_ID)
    SELECT
      B.CATEGORY_ID,
      B.CATEGORY_ID,
      B.CATEGORY_ID,
      'CATEGORY_SET',
      g_catset_id,
      'Y' TOP_NODE_FLAG,
      'Y' LEAF_NODE_FLAG,
      'N' ITEM_ASSGN_FLAG,
      'Y' DBI_FLAG,
      'Y' OLTP_FLAG,
      l_user_id,
      SYSDATE,
      l_user_id,
      SYSDATE,
      l_user_id,
      l_conc_request_id,
      l_prog_appl_id,
      SYSDATE,
      l_conc_program_id
    FROM MTL_CATEGORIES_B B
    WHERE B.STRUCTURE_ID = l_struct_id
      AND NOT EXISTS (SELECT NULL FROM ENI_DENORM_HIERARCHIES H
                      WHERE H.OBJECT_TYPE = 'CATEGORY_SET'
                        AND H.OBJECT_ID = g_catset_id
                        AND H.PARENT_ID = B.CATEGORY_ID
                        AND H.CHILD_ID = B.CATEGORY_ID);

Line: 1050

    UPDATE ENI_DENORM_HRCHY_STG T
    SET (TOP_NODE_ID, CHILD_LEVEL)=
                (SELECT X.CATEGORY_ID, LEVEL
                 FROM MTL_CATEGORY_SET_VALID_CATS X
                 WHERE X.PARENT_CATEGORY_ID IS NULL
                 START WITH X.CATEGORY_ID = T.CHILD_ID AND X.CATEGORY_SET_ID = g_catset_id
                 CONNECT BY X.CATEGORY_ID = PRIOR X.PARENT_CATEGORY_ID AND X.CATEGORY_SET_ID = g_catset_id),
      BATCH_FLAG = 'CURRENT_BATCH'
    WHERE OBJECT_TYPE = 'CATEGORY_SET'
      AND T.OBJECT_ID = g_catset_id;

Line: 1073

    FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleting Nodes from Denorm Table, which are deleted from Hierarchy');

Line: 1076

    DELETE FROM ENI_DENORM_HIERARCHIES B  -- changed the statement due to performance reasons
    WHERE OBJECT_TYPE = 'CATEGORY_SET'
      AND OBJECT_ID = g_catset_id
      AND EXISTS (SELECT NULL
                  FROM ENI_DENORM_HRCHY_STG S
                  WHERE S.OBJECT_TYPE = B.OBJECT_TYPE
                    AND S.OBJECT_ID = B.OBJECT_ID
                    AND S.CHILD_ID = B.CHILD_ID
                    AND S.MODE_FLAG = 'D'
                    AND S.BATCH_FLAG = 'CURRENT_BATCH');

Line: 1088

    FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records deleted from denorm table');

Line: 1091

    FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting Self-referencing nodes for new nodes');

Line: 1092

    INSERT INTO ENI_DENORM_HIERARCHIES(
      PARENT_ID,
      IMM_CHILD_ID,
      CHILD_ID,
      OBJECT_TYPE,
      OBJECT_ID,
      TOP_NODE_FLAG,
      LEAF_NODE_FLAG,
      ITEM_ASSGN_FLAG,
      DBI_FLAG,
      OLTP_FLAG,
      CREATED_BY,
      CREATION_DATE,
      LAST_UPDATED_BY,
      LAST_UPDATE_DATE,
      LAST_UPDATE_LOGIN,
      REQUEST_ID,
      PROGRAM_APPLICATION_ID,
      PROGRAM_UPDATE_DATE,
      PROGRAM_ID)
    SELECT
      S.CHILD_ID,
      CHILD_ID,
      CHILD_ID,
      'CATEGORY_SET',
      g_catset_id,
      DECODE(CHILD_ID, TOP_NODE_ID, 'Y', 'N'), -- Bug# 3047381, removed use of SIGN function
      'N',
      'N',
      'Y',
      'Y',
      l_user_id,
      SYSDATE,
      l_user_id,
      SYSDATE,
      l_user_id,
      l_conc_request_id,
      l_prog_appl_id,
      SYSDATE,
      l_conc_program_id
    FROM ENI_DENORM_HRCHY_STG S
    WHERE S.OBJECT_TYPE = 'CATEGORY_SET'
      AND S.OBJECT_ID = g_catset_id
      AND S.MODE_FLAG = 'A'
      AND S.BATCH_FLAG = 'CURRENT_BATCH';

Line: 1139

    FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records inserted as Self-referencing nodes');

Line: 1146

    FOR i IN (SELECT * FROM ENI_DENORM_HRCHY_STG
              WHERE OBJECT_TYPE = 'CATEGORY_SET'
                AND OBJECT_ID = g_catset_id
                AND MODE_FLAG = 'M'
                AND BATCH_FLAG = 'CURRENT_BATCH'
                ORDER BY CHILD_LEVEL DESC) LOOP  -- Bug# 3047381, removed TOP_NODE_ID ASC from order by clause

      DELETE FROM ENI_DENORM_HIERARCHIES B
      WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
        AND B.OBJECT_ID = g_catset_id
        AND EXISTS (SELECT NULL FROM ENI_DENORM_HIERARCHIES T  -- all records with child = i.child_id or children of i.child_id
                    WHERE T.OBJECT_TYPE = B.OBJECT_TYPE
                      AND T.OBJECT_ID = B.OBJECT_ID
                      AND B.CHILD_ID = T.CHILD_ID
                      AND T.PARENT_ID = i.CHILD_ID)
        AND NOT EXISTS (SELECT NULL FROM ENI_DENORM_HIERARCHIES D  -- Hierarchy below the i.child_id must not be deleted
                      WHERE D.OBJECT_TYPE = B.OBJECT_TYPE
                          AND D.OBJECT_ID = B.OBJECT_ID
                          AND B.PARENT_ID = D.CHILD_ID
                        AND D.PARENT_ID = i.CHILD_ID)
        AND NOT EXISTS (-- Find New Parents, All Records Which Are A Part Of New Hierarchy
                        SELECT NULL
                        FROM MTL_CATEGORY_SET_VALID_CATS C
                        WHERE C.PARENT_CATEGORY_ID IS NOT NULL
                          AND C.PARENT_CATEGORY_ID = B.PARENT_ID
                          AND C.CATEGORY_ID = B.IMM_CHILD_ID
                        START WITH C.CATEGORY_ID = i.PARENT_ID AND C.CATEGORY_SET_ID = g_catset_id
                        CONNECT BY C.CATEGORY_ID = PRIOR C.PARENT_CATEGORY_ID AND C.CATEGORY_SET_ID = g_catset_id);

Line: 1178

    FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records deleted');

Line: 1180

    FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting new relations');

Line: 1187

          SELECT CHILD_ID , CHILD_LEVEL
            INTO l_affected_child , l_affected_level
          FROM
            (SELECT CHILD_ID, CHILD_LEVEL
             FROM ENI_DENORM_HRCHY_STG T
             WHERE OBJECT_TYPE = 'CATEGORY_SET'
               AND OBJECT_ID = g_catset_id
               AND TOP_NODE_ID = i.TOP_NODE_ID
               AND BATCH_FLAG = 'CURRENT_BATCH'
               AND MODE_FLAG IN ('A', 'M')  -- modified for sales and marketing enhancement
             ORDER BY CHILD_LEVEL DESC)
          WHERE ROWNUM=1;

Line: 1207

        INSERT INTO ENI_DENORM_HIERARCHIES (
          PARENT_ID,
          IMM_CHILD_ID,
          CHILD_ID,
          OBJECT_TYPE,
          OBJECT_ID,
          TOP_NODE_FLAG,
          LEAF_NODE_FLAG,
          ITEM_ASSGN_FLAG,
          DBI_FLAG,
          OLTP_FLAG,
          CREATED_BY,
          CREATION_DATE,
          LAST_UPDATED_BY,
          LAST_UPDATE_DATE,
          LAST_UPDATE_LOGIN,
          REQUEST_ID,
          PROGRAM_APPLICATION_ID,
          PROGRAM_UPDATE_DATE,
          PROGRAM_ID)
        SELECT
          A.PARENT_ID,
          A.IMM_CHILD_ID,
          B.CHILD_ID,
          'CATEGORY_SET',
          g_catset_id,
          DECODE(A.PARENT_ID, i.TOP_NODE_ID, 'Y', 'N'), -- Bug# 3047381, removed use of SIGN function
          'N',
          'N',
          'Y',
          'Y',
          l_user_id,
          SYSDATE,
          l_user_id,
          SYSDATE,
          l_user_id,
          l_conc_request_id,
          l_prog_appl_id,
          SYSDATE,
          l_conc_program_id
        FROM
          (SELECT PARENT_CATEGORY_ID PARENT_ID, CATEGORY_ID IMM_CHILD_ID
           FROM MTL_CATEGORY_SET_VALID_CATS
           WHERE PARENT_CATEGORY_ID IS NOT NULL
           START WITH CATEGORY_ID = l_affected_child AND CATEGORY_SET_ID = g_catset_id
           CONNECT BY CATEGORY_ID = PRIOR PARENT_CATEGORY_ID AND CATEGORY_SET_ID = g_catset_id) A,
          (SELECT CATEGORY_ID CHILD_ID
           FROM MTL_CATEGORY_SET_VALID_CATS A
           START WITH CATEGORY_ID = l_affected_child AND A.CATEGORY_SET_ID = g_catset_id
           CONNECT BY PARENT_CATEGORY_ID = PRIOR CATEGORY_ID AND A.CATEGORY_SET_ID = g_catset_id) B
        WHERE NOT EXISTS (SELECT NULL FROM ENI_DENORM_HIERARCHIES P
                          WHERE P.OBJECT_TYPE = 'CATEGORY_SET'
                            AND P.OBJECT_ID = g_catset_id
                            AND P.PARENT_ID = A.PARENT_ID
                            AND P.IMM_CHILD_ID = A.IMM_CHILD_ID
                            AND P.CHILD_ID = B.CHILD_ID);

Line: 1267

        UPDATE ENI_DENORM_HRCHY_STG SET BATCH_FLAG = 'PROCESSED'
        WHERE OBJECT_TYPE = 'CATEGORY_SET'
          AND OBJECT_ID = g_catset_id
          AND CHILD_ID = l_affected_child
          AND BATCH_FLAG = 'CURRENT_BATCH';

Line: 1276

    FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records inserted');

Line: 1283

    'UPDATE ENI_DENORM_HIERARCHIES B
    SET LEAF_NODE_FLAG = DECODE(B.LEAF_NODE_FLAG, ''N'', ''Y'', ''N'') ,
        LAST_UPDATE_DATE = SYSDATE,
        LAST_UPDATED_BY = :l_user_id,
        LAST_UPDATE_LOGIN = :l_user_id,
        REQUEST_ID = :l_conc_request_id,
        PROGRAM_APPLICATION_ID = :l_prog_appl_id,
        PROGRAM_UPDATE_DATE = SYSDATE,
        PROGRAM_ID = :l_conc_program_id
    WHERE B.OBJECT_TYPE = ''CATEGORY_SET''
      AND B.OBJECT_ID = :g_catset_id
      AND B.CHILD_ID <> -1
      AND B.LEAF_NODE_FLAG <> NVL((SELECT ''N''
                                   FROM MTL_CATEGORY_SET_VALID_CATS C
                                   WHERE C.CATEGORY_SET_ID = :g_catset_id
                                     AND B.IMM_CHILD_ID = C.PARENT_CATEGORY_ID
                                     AND ROWNUM = 1), ''Y'')';

Line: 1305

    FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records Updated for Leaf Node Flag');

Line: 1320

   USING ( SELECT category_id, category_set_id, DECODE(PARENT_CATEGORY_ID, null, 'Y', 'N') NEW_TOP_NODE
            FROM MTL_CATEGORY_SET_VALID_CATS) C
     ON (   B.OBJECT_ID = C.CATEGORY_SET_ID
        AND B.PARENT_ID = C.CATEGORY_ID
        AND B.OBJECT_TYPE = 'CATEGORY_SET'
        AND B.OBJECT_ID =  g_catset_id
        )
  WHEN matched THEN
  UPDATE SET TOP_NODE_FLAG =  DECODE(TOP_NODE_FLAG, 'N', 'Y', 'N'),--C.new_top_node,
        LAST_UPDATE_DATE = SYSDATE,
        LAST_UPDATED_BY = l_user_id,
        LAST_UPDATE_LOGIN = l_user_id,
        REQUEST_ID = l_conc_request_id,
        PROGRAM_APPLICATION_ID = l_prog_appl_id,
        PROGRAM_UPDATE_DATE = SYSDATE,
        PROGRAM_ID = l_conc_program_id
 WHERE  C.new_top_node <> B.TOP_NODE_FLAG;

Line: 1339

    UPDATE (
            SELECT B.PARENT_ID, DECODE(C.PARENT_CATEGORY_ID, NULL, 'Y', 'N') NEW_TOP_NODE, B.TOP_NODE_FLAG,
              B.LAST_UPDATE_DATE, B.LAST_UPDATED_BY, B.LAST_UPDATE_LOGIN, B.REQUEST_ID, B.PROGRAM_APPLICATION_ID,
              B.PROGRAM_UPDATE_DATE, B.PROGRAM_ID
            FROM ENI_DENORM_HIERARCHIES B, MTL_CATEGORY_SET_VALID_CATS C
            WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
              AND B.OBJECT_ID = g_catset_id
              AND C.CATEGORY_SET_ID = B.OBJECT_ID
              AND C.CATEGORY_ID = B.PARENT_ID)
      SET
        TOP_NODE_FLAG = DECODE(TOP_NODE_FLAG, 'N', 'Y', 'N'),
        LAST_UPDATE_DATE = SYSDATE,
        LAST_UPDATED_BY = l_user_id,
        LAST_UPDATE_LOGIN = l_user_id,
        REQUEST_ID = l_conc_request_id,
        PROGRAM_APPLICATION_ID = l_prog_appl_id,
        PROGRAM_UPDATE_DATE = SYSDATE,
        PROGRAM_ID = l_conc_program_id
    WHERE NEW_TOP_NODE <> TOP_NODE_FLAG;

Line: 1361

    FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records Updated for Top Node Flag');

Line: 1368

  UPDATE ENI_DENORM_HIERARCHIES B
  SET
    ITEM_ASSGN_FLAG = 'Y',
    LAST_UPDATE_DATE = SYSDATE,
    LAST_UPDATED_BY = l_user_id,
    LAST_UPDATE_LOGIN = l_user_id,
    REQUEST_ID = l_conc_request_id,
    PROGRAM_APPLICATION_ID = l_prog_appl_id,
    PROGRAM_UPDATE_DATE = SYSDATE,
    PROGRAM_ID = l_conc_program_id
  WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
    AND B.OBJECT_ID = g_catset_id
    AND B.CHILD_ID <> -1
    AND B.ITEM_ASSGN_FLAG <> 'Y'
    AND EXISTS (SELECT NULL
                FROM MTL_ITEM_CATEGORIES C
                WHERE C.CATEGORY_SET_ID = g_catset_id
                  AND C.CATEGORY_ID = B.CHILD_ID);

Line: 1390

  UPDATE ENI_DENORM_HIERARCHIES B
  SET
    ITEM_ASSGN_FLAG = 'N',
    LAST_UPDATE_DATE = SYSDATE,
    LAST_UPDATED_BY = l_user_id,
    LAST_UPDATE_LOGIN = l_user_id,
    REQUEST_ID = l_conc_request_id,
    PROGRAM_APPLICATION_ID = l_prog_appl_id,
    PROGRAM_UPDATE_DATE = SYSDATE,
    PROGRAM_ID = l_conc_program_id
  WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
    AND B.OBJECT_ID = g_catset_id
    AND B.CHILD_ID <> -1
    AND B.ITEM_ASSGN_FLAG <> 'N'
    AND NOT EXISTS (SELECT NULL
                    FROM MTL_ITEM_CATEGORIES C
                    WHERE C.CATEGORY_SET_ID = g_catset_id
                      AND C.CATEGORY_ID = B.CHILD_ID);

Line: 1410

  FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records Updated for Item Assignment Flag');

Line: 1425

    UPDATE ENI_DENORM_HRCHY_STG
    SET BATCH_FLAG = 'NEXT_BATCH'
    WHERE BATCH_FLAG <> 'NEXT_BATCH'
      AND OBJECT_TYPE = 'CATEGORY_SET'
      AND OBJECT_ID = g_catset_id;

Line: 1512

      SELECT 1 INTO l_cnt
      FROM ENI_DENORM_HIERARCHIES
      WHERE OBJECT_TYPE = 'CATEGORY_SET'
        AND ROWNUM = 1;

Line: 1527

          SELECT 1 INTO l_cnt
          FROM ENI_DENORM_HRCHY_PARENTS
          WHERE OBJECT_TYPE = 'CATEGORY_SET'
            AND ROWNUM = 1;

Line: 1545

      DELETE FROM ENI_DENORM_HRCHY_STG
      WHERE BATCH_FLAG <> 'NEXT_BATCH'
        AND OBJECT_TYPE = 'CATEGORY_SET'
        AND OBJECT_ID = g_catset_id;

Line: 1653

    ENI_UPD_ASSGN.UPDATE_ASSGN_FLAG(
          p_new_category_id => p_new_category_id,
          p_old_category_id => p_old_category_id,
          x_return_status => l_return_status,
          x_msg_count => l_msg_count,
          x_msg_data => l_msg_data);

Line: 1730

  UPDATE ENI_DENORM_HIERARCHIES B
  SET
    ITEM_ASSGN_FLAG = 'Y',
    LAST_UPDATE_DATE = SYSDATE,
    LAST_UPDATED_BY = l_user_id,
    LAST_UPDATE_LOGIN = l_user_id,
    REQUEST_ID = l_conc_request_id,
    PROGRAM_APPLICATION_ID = l_prog_appl_id,
    PROGRAM_UPDATE_DATE = SYSDATE,
    PROGRAM_ID = l_conc_program_id
  WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
    AND B.OBJECT_ID = g_catset_id
    AND B.ITEM_ASSGN_FLAG = 'N'
    AND EXISTS (SELECT NULL
                FROM MTL_ITEM_CATEGORIES C
                WHERE C.CATEGORY_SET_ID = g_catset_id
                  AND C.CATEGORY_ID = B.CHILD_ID);

Line: 1749

  UPDATE ENI_DENORM_HIERARCHIES B
  SET
    ITEM_ASSGN_FLAG = 'N',
    LAST_UPDATE_DATE = SYSDATE,
    LAST_UPDATED_BY = l_user_id,
    LAST_UPDATE_LOGIN = l_user_id,
    REQUEST_ID = l_conc_request_id,
    PROGRAM_APPLICATION_ID = l_prog_appl_id,
    PROGRAM_UPDATE_DATE = SYSDATE,
    PROGRAM_ID = l_conc_program_id
  WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
    AND B.OBJECT_ID = g_catset_id
    AND B.ITEM_ASSGN_FLAG = 'Y'
    AND B.CHILD_ID <> -1
    AND NOT EXISTS (SELECT NULL
                    FROM MTL_ITEM_CATEGORIES C
                    WHERE C.CATEGORY_SET_ID = g_catset_id
                      AND C.CATEGORY_ID = B.CHILD_ID);

Line: 1775

    SELECT 1 INTO l_count
    FROM MTL_SYSTEM_ITEMS_B IT
    WHERE ROWNUM = 1
      AND NOT EXISTS (SELECT NULL FROM MTL_ITEM_CATEGORIES C
                      WHERE C.CATEGORY_SET_ID = g_catset_id
                        AND C.INVENTORY_ITEM_ID = IT.INVENTORY_ITEM_ID
                        AND C.ORGANIZATION_ID = IT.ORGANIZATION_ID);

Line: 1786

  UPDATE ENI_DENORM_HIERARCHIES B
  SET
    ITEM_ASSGN_FLAG = DECODE(l_count, 0, 'N', 'Y'),
    LAST_UPDATE_DATE = SYSDATE,
    LAST_UPDATED_BY = l_user_id,
    LAST_UPDATE_LOGIN = l_user_id,
    REQUEST_ID = l_conc_request_id,
    PROGRAM_APPLICATION_ID = l_prog_appl_id,
    PROGRAM_UPDATE_DATE = SYSDATE,
    PROGRAM_ID = l_conc_program_id
  WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
    AND B.OBJECT_ID = g_catset_id
    AND B.ITEM_ASSGN_FLAG = DECODE(l_count, 0, 'Y', 'N')
    AND B.CHILD_ID = -1
    AND B.PARENT_ID = -1;

Line: 1876

    SELECT HIERARCHY_ENABLED, STRUCTURE_ID INTO l_hrchy_enabled, l_struct_id
    FROM MTL_CATEGORY_SETS_B
    WHERE CATEGORY_SET_ID   = g_catset_id
      AND HIERARCHY_ENABLED = 'Y';

Line: 1887

     SELECT csvl.category_set_name
       INTO l_product_catalog
       FROM mtl_default_category_sets mdcs
           ,mtl_category_sets_vl csvl
      WHERE csvl.category_set_id = mdcs.category_set_id AND mdcs.functional_area_id=11;

Line: 1916

    'INSERT INTO eni_icat_cdenorm_hierarchies ( ' ||
           '  category_id_level1 ' ||
           ' ,category_id_level2 ' ||
           ' ,category_id_level3 ' ||
           ' ,category_id_level4 ' ||
           ' ,category_id_level5 ';

Line: 1934

           ' ,last_updated_by    ' ||
           ' ,last_update_date   ' ||
           ' ,last_update_login  ' ||
           ' ,request_id         ' ||
           ' ,program_application_id ' ||
           ' ,program_update_date' ||
           ' ,program_id) ';

Line: 1944

           '( SELECT ' ||
         '  ENI_DENORM_HRCHY.split_category_codes(catstr,1,''' || g_delimiter || ''') ' ||
         ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,2,''' || g_delimiter || ''') ' ||
         ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,3,''' || g_delimiter || ''') ' ||
         ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,4,''' || g_delimiter || ''') ' ||
         ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,5,''' || g_delimiter || ''') ';

Line: 1969

         ' (SELECT (sys_connect_by_path(vcats.category_id,''' || g_delimiter ||
         ''') ||''' || g_delimiter || ''') catstr ' ||
         '        , vcats.category_id' ||
           '  FROM MTL_CATEGORY_SET_VALID_CATS vcats ' ||
           ' WHERE CATEGORY_SET_ID = :g_catset_id    ' ||
         '/* AND CATEGORY_ID NOT IN (SELECT        ' ||
         '   Nvl(vcats1.PARENT_CATEGORY_ID,-99) FROM MTL_CATEGORY_SET_VALID_CATS vcats1 WHERE vcats1.CATEGORY_SET_ID = :g_catset_id2) */' ||
           ' START WITH PARENT_CATEGORY_ID IS NULL   ' ||
         ' AND CATEGORY_SET_ID = :g_catset_id3     ' ||
           ' CONNECT BY PRIOR CATEGORY_ID = PARENT_CATEGORY_ID ' ||
         ' AND PRIOR    CATEGORY_SET_ID = CATEGORY_SET_ID ))' ;

Line: 1988

     FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserted [' || SQL%ROWCOUNT ||'] rows into denorm table.');

Line: 1993

  FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert the UNASSIGNED product category row.');

Line: 1995

  INSERT INTO eni_icat_cdenorm_hierarchies (
     category_id_level1
    ,category_id_level2
    ,category_id_level3
    ,category_id_level4
    ,category_id_level5
    ,category_id_level6
    ,category_id_level7
    ,category_id_level8
    ,category_id_level9
    ,category_id_level10
    ,leaf_category_id
    ,created_by
    ,creation_date
    ,last_updated_by
    ,last_update_date
    ,last_update_login
    ,request_id
    ,program_application_id
    ,program_update_date
    ,program_id)
  VALUES (
   -1,-1,-1,-1,-1,-1,-1,-1,-1,-1
  ,-1
  ,l_user_id
  ,SYSDATE
  ,l_user_id
  ,SYSDATE
  ,l_user_id
  ,l_conc_request_id
  ,l_prog_appl_id
  ,SYSDATE
  ,l_conc_program_id
  );