APPS.FEM_DIS_HIER_PKG SQL Statements

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

Line: 65

  PROCEDURE Print_DSQL_Insert_B(
    p_module                     IN VARCHAR2,
    p_dis_hierarchy_table_name_b IN VARCHAR2,
    p_insert_b1                  IN VARCHAR2,
    p_insert_b2                  IN VARCHAR2,
    p_insert_b3                  IN VARCHAR2,
    p_insert_b4                  IN VARCHAR2,
    p_insert_b5                  IN VARCHAR2,
    p_insert_b6                  IN VARCHAR2,
    p_insert_b7                  IN VARCHAR2,
    p_insert_b8                  IN VARCHAR2,
    p_insert_b9                  IN VARCHAR2,
    p_insert_b10                 IN VARCHAR2,
    p_insert_b11                 IN VARCHAR2,
    p_insert_b12                 IN VARCHAR2,
    p_insert_b13                 IN VARCHAR2,
    p_insert_b14                 IN VARCHAR2,
    p_using1                     IN VARCHAR2,
    p_using2                     IN VARCHAR2,
    p_using3                     IN VARCHAR2,
    p_using4                     IN VARCHAR2,
    p_using5                     IN VARCHAR2,
    p_using6                     IN VARCHAR2,
    p_using7                     IN VARCHAR2,
    p_using8                     IN VARCHAR2,
    p_using9                     IN VARCHAR2,
    p_using10                    IN VARCHAR2,
    p_using11                    IN VARCHAR2
  ) IS
  BEGIN

    --
    -- Print Dynamic SQL Elements for INSERT INTO _B table to Debug Log
    --
    FEM_ENGINES_PKG.Tech_Message(
      p_severity => pc_log_level_statement,
      p_module   => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
      p_msg_text => p_insert_b1
    );

Line: 107

      p_module   => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
      p_msg_text => p_insert_b2
    );

Line: 113

      p_module   => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
      p_msg_text => p_insert_b3
    );

Line: 117

    IF p_insert_b4 IS NOT NULL THEN
      FEM_ENGINES_PKG.Tech_Message(
        p_severity => pc_log_level_statement,
        p_module   => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
        p_msg_text => p_insert_b4
      );

Line: 127

      p_module   => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
      p_msg_text => p_insert_b5
    );

Line: 133

      p_module   => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
      p_msg_text => p_insert_b6
    );

Line: 139

      p_module   => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
      p_msg_text => p_insert_b7
    );

Line: 145

      p_module   => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
      p_msg_text => p_insert_b8
    );

Line: 151

      p_module   => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
      p_msg_text => p_insert_b9
    );

Line: 157

      p_module   => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
      p_msg_text => p_insert_b10
    );

Line: 161

    IF p_insert_b11 IS NOT NULL THEN
      FEM_ENGINES_PKG.Tech_Message(
        p_severity => pc_log_level_statement,
        p_module   => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
        p_msg_text => p_insert_b11
      );

Line: 171

      p_module   => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
      p_msg_text => p_insert_b12
    );

Line: 177

      p_module   => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
      p_msg_text => p_insert_b13
    );

Line: 183

      p_module   => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
      p_msg_text => p_insert_b14
    );

Line: 188

      p_module   => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_b,
      p_msg_text => 'USING '  ||
                    p_using1  || ', ' ||
                    p_using2  || ', ' ||
                    p_using3  || ', ''' ||
                    p_using4  || ''', ' ||
                    p_using5  || ', ' ||
                    p_using6  || ', ' ||
                    p_using7  || ', ' ||
                    p_using8  || ', ' ||
                    p_using9  || ', ' ||
                    p_using10 || ', ' ||
                    p_using11 || '
                    '
    );

Line: 204

  END Print_DSQL_Insert_B;

Line: 226

  PROCEDURE Print_DSQL_Insert_TL(
    p_module                      IN VARCHAR2,
    p_dis_hierarchy_table_name_tl IN VARCHAR2,
    p_insert_tl1                  IN VARCHAR2,
    p_insert_tl2                  IN VARCHAR2,
    p_insert_tl3                  IN VARCHAR2,
    p_insert_tl4                  IN VARCHAR2,
    p_insert_tl5                  IN VARCHAR2,
    p_using1                      IN VARCHAR2,
    p_using2                      IN VARCHAR2,
    p_using3                      IN VARCHAR2
  ) IS
  BEGIN

    --
    -- Print Dynamic SQL Elements for INSERT INTO _TL table to Debug Log
    --
    FEM_ENGINES_PKG.Tech_Message(
      p_severity => pc_log_level_statement,
      p_module   => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_tl,
      p_msg_text => p_insert_tl1
    );

Line: 251

      p_module   => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_tl,
      p_msg_text => p_insert_tl2
    );

Line: 257

      p_module   => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_tl,
      p_msg_text => p_insert_tl3
    );

Line: 263

      p_module   => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_tl,
      p_msg_text => p_insert_tl4
    );

Line: 269

      p_module   => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_tl,
      p_msg_text => p_insert_tl5
    );

Line: 275

      p_module   => p_module || '.dsql_insert_into_' || p_dis_hierarchy_table_name_tl,
      p_msg_text => 'USING ' ||
                    p_using1 || ', ' ||
                    p_using2 || ', ' ||
                    p_using3 || '
                    '
    );

Line: 283

  END Print_DSQL_Insert_TL;

Line: 348

    v_insert_b_column_list     VARCHAR2(4000);

Line: 349

    v_insert_b_display_code1   VARCHAR2(4000);

Line: 350

    v_insert_b_display_code2   VARCHAR2(4000);

Line: 351

    v_insert_b_with_o          VARCHAR2(4000);

Line: 352

    v_insert_b_with_p1         VARCHAR2(4000);

Line: 353

    v_insert_b_with_p2         VARCHAR2(4000);

Line: 354

    v_insert_b_with_p3         VARCHAR2(4000);

Line: 355

    v_insert_b_subquery_r1     VARCHAR2(4000);

Line: 356

    v_insert_b_subquery_r2     VARCHAR2(4000);

Line: 357

    v_insert_b_subquery_h11    VARCHAR2(4000);

Line: 358

    v_insert_b_subquery_h11_o  VARCHAR2(4000);

Line: 359

    v_insert_b_subquery_h12    VARCHAR2(4000);

Line: 360

    v_insert_b_exclude_nonleaf VARCHAR2(4000);

Line: 361

    v_insert_b_exclude_nonleaf_o VARCHAR2(4000);

Line: 362

    v_insert_b_subquery_q1     VARCHAR2(4000);

Line: 363

    v_insert_b_subquery_q2     VARCHAR2(4000);

Line: 365

    v_insert_b_column_list_l     NUMBER;

Line: 366

    v_insert_b_display_code1_l   NUMBER;

Line: 367

    v_insert_b_display_code2_l   NUMBER;

Line: 368

    v_insert_b_with_o_l          NUMBER;

Line: 369

    v_insert_b_with_p1_l         NUMBER;

Line: 370

    v_insert_b_with_p2_l         NUMBER;

Line: 371

    v_insert_b_with_p3_l         NUMBER;

Line: 372

    v_insert_b_subquery_r1_l     NUMBER;

Line: 373

    v_insert_b_subquery_r2_l     NUMBER;

Line: 374

    v_insert_b_subquery_h11_l    NUMBER;

Line: 375

    v_insert_b_subquery_h11_o_l  NUMBER;

Line: 376

    v_insert_b_subquery_h12_l    NUMBER;

Line: 377

    v_insert_b_exclude_nonleaf_l NUMBER;

Line: 378

    v_insert_b_exclude_nonleaf_o_l NUMBER;

Line: 379

    v_insert_b_subquery_q1_l     NUMBER;

Line: 380

    v_insert_b_subquery_q2_l     NUMBER;

Line: 382

    v_insert_tl_column_list  VARCHAR2(4000);

Line: 383

    v_insert_tl_name1        VARCHAR2(4000);

Line: 384

    v_insert_tl_name2        VARCHAR2(4000);

Line: 385

    v_insert_tl_description1 VARCHAR2(4000);

Line: 386

    v_insert_tl_description2 VARCHAR2(4000);

Line: 388

    v_insert_tl_column_list_l  NUMBER;

Line: 389

    v_insert_tl_name1_l        NUMBER;

Line: 390

    v_insert_tl_name2_l        NUMBER;

Line: 391

    v_insert_tl_description1_l NUMBER;

Line: 392

    v_insert_tl_description2_l NUMBER;

Line: 394

    v_deleted_b   NUMBER;

Line: 395

    v_deleted_tl  NUMBER;

Line: 396

    v_inserted_b1 NUMBER;

Line: 397

    v_inserted_b2 NUMBER;

Line: 398

    v_inserted_tl NUMBER;

Line: 438

    SELECT
      V.DIMENSION_ID,
      V.DIMENSION_NAME,
      X.MEMBER_B_TABLE_NAME,
      X.MEMBER_TL_TABLE_NAME,
      X.MEMBER_VL_OBJECT_NAME,
      X.MEMBER_COL,
      X.MEMBER_DISPLAY_CODE_COL,
      X.MEMBER_NAME_COL,
      X.MEMBER_DESCRIPTION_COL,
      X.HIERARCHY_TABLE_NAME,
      X.ATTRIBUTE_TABLE_NAME,
      X.COMPOSITE_DIMENSION_FLAG,
      X.VALUE_SET_REQUIRED_FLAG
    INTO
      v_dimension_id,
      v_dimension_name,
      v_member_b_table_name,
      v_member_tl_table_name,
      v_member_vl_object_name,
      v_member_col,
      v_member_display_code_col,
      v_member_name_col,
      v_member_description_col,
      v_hierarchy_table_name,
      v_attribute_table_name,
      v_composite_dimension_flag,
      v_value_set_required_flag
    FROM
      FEM_DIMENSIONS_VL V,
      FEM_XDIM_DIMENSIONS X
    WHERE
      X.DIMENSION_ID = V.DIMENSION_ID AND
      V.DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label;

Line: 477

      SELECT MEANING
      INTO v_dummy_parent_name
      FROM FEM_LOOKUPS
      WHERE LOOKUP_TYPE = 'FEM_DIS_DUMMY_PARENT'
      AND   LOOKUP_CODE = 'DUMMY_PARENT_NAME';

Line: 594

                SELECT 1
                FROM FEM_HIER_VALUE_SETS VS
                WHERE
                  VS.HIERARCHY_OBJ_ID = HIER.HIERARCHY_OBJ_ID AND
                  VS.VALUE_SET_ID = M.VALUE_SET_ID
              ) AND';

Line: 718

      Construct Dynamic SQL Elements for INSERT INTO _B table
    *************************************************************************/
    --
    -- Construct Dynamic SQL Elements for INSERT INTO _B table (column list)
    --
    v_insert_b_column_list := '
INSERT INTO ' || v_dis_hierarchy_table_name_b || ' (
  OBJECT_ID,
  OBJECT_DEFINITION_ID,' ||
  v_vs_column_list || '
  LEVEL1_ID,
  LEVEL2_ID,
  LEVEL3_ID,
  LEVEL4_ID,
  LEVEL5_ID,
  LEVEL6_ID,
  LEVEL7_ID,
  LEVEL8_ID,
  LEVEL9_ID,
  LEVEL10_ID,
  LEVEL11_ID,
  LEVEL12_ID,
  LEVEL13_ID,
  LEVEL14_ID,
  LEVEL15_ID,
  LEVEL16_ID,
  LEVEL17_ID,
  LEVEL18_ID,
  LEVEL19_ID,
  LEVEL20_ID,
  LEVEL1_DISPLAY_ORDER_NUM,
  LEVEL2_DISPLAY_ORDER_NUM,
  LEVEL3_DISPLAY_ORDER_NUM,
  LEVEL4_DISPLAY_ORDER_NUM,
  LEVEL5_DISPLAY_ORDER_NUM,
  LEVEL6_DISPLAY_ORDER_NUM,
  LEVEL7_DISPLAY_ORDER_NUM,
  LEVEL8_DISPLAY_ORDER_NUM,
  LEVEL9_DISPLAY_ORDER_NUM,
  LEVEL10_DISPLAY_ORDER_NUM,
  LEVEL11_DISPLAY_ORDER_NUM,
  LEVEL12_DISPLAY_ORDER_NUM,
  LEVEL13_DISPLAY_ORDER_NUM,
  LEVEL14_DISPLAY_ORDER_NUM,
  LEVEL15_DISPLAY_ORDER_NUM,
  LEVEL16_DISPLAY_ORDER_NUM,
  LEVEL17_DISPLAY_ORDER_NUM,
  LEVEL18_DISPLAY_ORDER_NUM,
  LEVEL19_DISPLAY_ORDER_NUM,
  LEVEL20_DISPLAY_ORDER_NUM,
  LEVEL1_DISPLAY_CODE,
  LEVEL2_DISPLAY_CODE,
  LEVEL3_DISPLAY_CODE,
  LEVEL4_DISPLAY_CODE,
  LEVEL5_DISPLAY_CODE,
  LEVEL6_DISPLAY_CODE,
  LEVEL7_DISPLAY_CODE,
  LEVEL8_DISPLAY_CODE,
  LEVEL9_DISPLAY_CODE,
  LEVEL10_DISPLAY_CODE,
  LEVEL11_DISPLAY_CODE,
  LEVEL12_DISPLAY_CODE,
  LEVEL13_DISPLAY_CODE,
  LEVEL14_DISPLAY_CODE,
  LEVEL15_DISPLAY_CODE,
  LEVEL16_DISPLAY_CODE,
  LEVEL17_DISPLAY_CODE,
  LEVEL18_DISPLAY_CODE,
  LEVEL19_DISPLAY_CODE,
  LEVEL20_DISPLAY_CODE,
  CREATION_DATE,
  CREATED_BY,
  LAST_UPDATE_DATE,
  LAST_UPDATED_BY,
  LAST_UPDATE_LOGIN
)
SELECT
  Q.*,';

Line: 799

      p_module   => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
      p_msg_text => 'v_insert_b_column_list_l='||LENGTH(v_insert_b_column_list)
    );

Line: 806

    v_insert_b_display_code1 := '
  (
    SELECT B.' || v_member_display_code_col || '
    FROM ' || v_member_b_table_name || ' B
    WHERE B.' || v_member_col || ' = Q.LEVEL1_ID ' ||
    v_vs_where_display_code || '
  ),
  DECODE(Q.LEVEL2_ID, ' || pc_dummy_member_id || ', ''' || v_dummy_parent_name || ''',
    (
      SELECT B.' || v_member_display_code_col || '
      FROM ' || v_member_b_table_name || ' B
      WHERE B.' || v_member_col || ' = Q.LEVEL2_ID ' ||
      v_vs_where_display_code || '
    )
  ),
  (
    SELECT B.' || v_member_display_code_col || '
    FROM ' || v_member_b_table_name || ' B
    WHERE B.' || v_member_col || ' = Q.LEVEL3_ID ' ||
    v_vs_where_display_code || '
  ),
  (
    SELECT B.' || v_member_display_code_col || '
    FROM ' || v_member_b_table_name || ' B
    WHERE B.' || v_member_col || ' = Q.LEVEL4_ID ' ||
    v_vs_where_display_code || '
  ),
  (
    SELECT B.' || v_member_display_code_col || '
    FROM ' || v_member_b_table_name || ' B
    WHERE B.' || v_member_col || ' = Q.LEVEL5_ID ' ||
    v_vs_where_display_code || '
  ),
  (
    SELECT B.' || v_member_display_code_col || '
    FROM ' || v_member_b_table_name || ' B
    WHERE B.' || v_member_col || ' = Q.LEVEL6_ID ' ||
    v_vs_where_display_code || '
  ),
  (
    SELECT B.' || v_member_display_code_col || '
    FROM ' || v_member_b_table_name || ' B
    WHERE B.' || v_member_col || ' = Q.LEVEL7_ID ' ||
    v_vs_where_display_code || '
  ),
  (
    SELECT B.' || v_member_display_code_col || '
    FROM ' || v_member_b_table_name || ' B
    WHERE B.' || v_member_col || ' = Q.LEVEL8_ID ' ||
    v_vs_where_display_code || '
  ),
  (
    SELECT B.' || v_member_display_code_col || '
    FROM ' || v_member_b_table_name || ' B
    WHERE B.' || v_member_col || ' = Q.LEVEL9_ID ' ||
    v_vs_where_display_code || '
  ),
  (
    SELECT B.' || v_member_display_code_col || '
    FROM ' || v_member_b_table_name || ' B
    WHERE B.' || v_member_col || ' = Q.LEVEL10_ID ' ||
    v_vs_where_display_code || '
  ),';

Line: 872

      p_module   => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
      p_msg_text => 'v_insert_b_display_code1_l='||LENGTH(v_insert_b_display_code1)
    );

Line: 879

    v_insert_b_display_code2 := '
  (
    SELECT B.' || v_member_display_code_col || '
    FROM ' || v_member_b_table_name || ' B
    WHERE B.' || v_member_col || ' = Q.LEVEL11_ID ' ||
    v_vs_where_display_code || '
  ),
  (
    SELECT B.' || v_member_display_code_col || '
    FROM ' || v_member_b_table_name || ' B
    WHERE B.' || v_member_col || ' = Q.LEVEL12_ID ' ||
    v_vs_where_display_code || '
  ),
  (
    SELECT B.' || v_member_display_code_col || '
    FROM ' || v_member_b_table_name || ' B
    WHERE B.' || v_member_col || ' = Q.LEVEL13_ID ' ||
    v_vs_where_display_code || '
  ),
  (
    SELECT B.' || v_member_display_code_col || '
    FROM ' || v_member_b_table_name || ' B
    WHERE B.' || v_member_col || ' = Q.LEVEL14_ID ' ||
    v_vs_where_display_code || '
  ),
  (
    SELECT B.' || v_member_display_code_col || '
    FROM ' || v_member_b_table_name || ' B
    WHERE B.' || v_member_col || ' = Q.LEVEL15_ID ' ||
    v_vs_where_display_code || '
  ),
  (
    SELECT B.' || v_member_display_code_col || '
    FROM ' || v_member_b_table_name || ' B
    WHERE B.' || v_member_col || ' = Q.LEVEL16_ID ' ||
    v_vs_where_display_code || '
  ),
  (
    SELECT B.' || v_member_display_code_col || '
    FROM ' || v_member_b_table_name || ' B
    WHERE B.' || v_member_col || ' = Q.LEVEL17_ID ' ||
    v_vs_where_display_code || '
  ),
  (
    SELECT B.' || v_member_display_code_col || '
    FROM ' || v_member_b_table_name || ' B
    WHERE B.' || v_member_col || ' = Q.LEVEL18_ID ' ||
    v_vs_where_display_code || '
  ),
  (
    SELECT B.' || v_member_display_code_col || '
    FROM ' || v_member_b_table_name || ' B
    WHERE B.' || v_member_col || ' = Q.LEVEL19_ID ' ||
    v_vs_where_display_code || '
  ),
  (
    SELECT B.' || v_member_display_code_col || '
    FROM ' || v_member_b_table_name || ' B
    WHERE B.' || v_member_col || ' = Q.LEVEL20_ID ' ||
    v_vs_where_display_code || '
  ),
  SYSDATE CREATION_DATE,
  :pv_user_id CREATED_BY,
  SYSDATE LAST_UPDATE_DATE,
  :pv_user_id LAST_UPDATED_BY,
  :pv_login_id LAST_UPDATE_LOGIN
FROM (
  WITH'; -- Q

Line: 950

      p_module   => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
      p_msg_text => 'v_insert_b_display_code2_l='||LENGTH(v_insert_b_display_code2)
    );

Line: 959

    v_insert_b_with_o := '
  O AS (
    SELECT
      H.HIERARCHY_OBJ_DEF_ID,
      ' || pc_dummy_display_order_num || ' DUMMY_DISPLAY_ORDER_NUM,
      ' || pc_dummy_member_id || ' DUMMY_MEMBER_ID,' ||
      v_vs_column_orphan || '
      H.CHILD_ID,
      M.' || v_member_col || ' MEMBER_ID
    FROM
      FEM_HIERARCHIES HIER,
      FEM_OBJECT_DEFINITION_B DEF,
      FEM_OBJECT_CATALOG_B CAT, ' ||
      v_attribute_from_orphan || '
      ' || v_member_b_table_name || ' M,
      ' || v_hierarchy_table_name || ' H ' ||
      v_from_composite_only || '
    WHERE
      HIER.MULTI_VALUE_SET_FLAG = ''N'' AND
      HIER.HIERARCHY_TYPE_CODE = ''RECONCILIATION'' AND
      HIER.DIMENSION_ID = :dimension_id AND
      HIER.PERSONAL_FLAG = ''N'' AND
      CAT.OBJECT_ID = HIER.HIERARCHY_OBJ_ID AND
      DEF.OBJECT_ID = CAT.OBJECT_ID AND
      H.HIERARCHY_OBJ_DEF_ID = DEF.OBJECT_DEFINITION_ID AND ' ||
      v_vs_where_root || '
      H.PARENT_ID = H.CHILD_ID AND
      H.PARENT_DEPTH_NUM = 1 AND ' ||
      v_vs_where_hier || '
      NOT EXISTS (
        SELECT 1
        FROM
          FEM_HIERARCHIES HIER2,
          FEM_OBJECT_DEFINITION_B DEF2,
          FEM_OBJECT_CATALOG_B CAT2,
          ' || v_hierarchy_table_name || ' H2
        WHERE
          HIER2.MULTI_VALUE_SET_FLAG = ''N'' AND
          HIER2.HIERARCHY_TYPE_CODE = ''RECONCILIATION'' AND
          HIER2.DIMENSION_ID = :dimension_id AND
          HIER2.PERSONAL_FLAG = ''N'' AND
          CAT2.OBJECT_ID = HIER2.HIERARCHY_OBJ_ID AND
          DEF2.OBJECT_ID = CAT2.OBJECT_ID AND
          H2.HIERARCHY_OBJ_DEF_ID = DEF2.OBJECT_DEFINITION_ID AND ' ||
          v_vs_where_member || '
          H2.CHILD_ID = M.' || v_member_col || '
      )' ||
      v_attribute_where_orphan ||
      v_where_composite_only || '
    ),';

Line: 1012

      p_module   => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
      p_msg_text => 'v_insert_b_with_o_l='||LENGTH(v_insert_b_with_o)
    );

Line: 1031

    v_insert_b_with_p1 := '
  P AS (
    SELECT
      R.*,
      (
        SELECT H4.DISPLAY_ORDER_NUM
        FROM ' || v_hierarchy_table_name || ' H4
        WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
        v_vs_where_display_order_num || '
        AND H4.PARENT_ID = R.L1
        AND H4.CHILD_ID = R.L1
      ) N1,
      (
        SELECT H4.DISPLAY_ORDER_NUM
        FROM ' || v_hierarchy_table_name || ' H4
        WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
        v_vs_where_display_order_num || '
        AND H4.PARENT_ID = R.L1
        AND H4.CHILD_ID = R.L2
      ) N2,
      (
        SELECT H4.DISPLAY_ORDER_NUM
        FROM ' || v_hierarchy_table_name || ' H4
        WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
        v_vs_where_display_order_num || '
        AND H4.PARENT_ID = R.L2
        AND H4.CHILD_ID = R.L3
      ) N3,
      (
        SELECT H4.DISPLAY_ORDER_NUM
        FROM ' || v_hierarchy_table_name || ' H4
        WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
        v_vs_where_display_order_num || '
        AND H4.PARENT_ID = R.L3
        AND H4.CHILD_ID = R.L4
      ) N4,
      (
        SELECT H4.DISPLAY_ORDER_NUM
        FROM ' || v_hierarchy_table_name || ' H4
        WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
        v_vs_where_display_order_num || '
        AND H4.PARENT_ID = R.L4
        AND H4.CHILD_ID = R.L5
      ) N5,
      (
        SELECT H4.DISPLAY_ORDER_NUM
        FROM ' || v_hierarchy_table_name || ' H4
        WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
        v_vs_where_display_order_num || '
        AND H4.PARENT_ID = R.L5
        AND H4.CHILD_ID = R.L6
      ) N6,';

Line: 1086

      p_module   => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
      p_msg_text => 'v_insert_b_with_p1_l='||LENGTH(v_insert_b_with_p1)
    );

Line: 1094

    v_insert_b_with_p2:= '
      (
        SELECT H4.DISPLAY_ORDER_NUM
        FROM ' || v_hierarchy_table_name || ' H4
        WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
        v_vs_where_display_order_num || '
        AND H4.PARENT_ID = R.L6
        AND H4.CHILD_ID = R.L7
      ) N7,
      (
        SELECT H4.DISPLAY_ORDER_NUM
        FROM ' || v_hierarchy_table_name || ' H4
        WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
        v_vs_where_display_order_num || '
        AND H4.PARENT_ID = R.L7
        AND H4.CHILD_ID = R.L8
      ) N8,
      (
        SELECT H4.DISPLAY_ORDER_NUM
        FROM ' || v_hierarchy_table_name || ' H4
        WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
        v_vs_where_display_order_num || '
        AND H4.PARENT_ID = R.L8
        AND H4.CHILD_ID = R.L9
      ) N9,
      (
        SELECT H4.DISPLAY_ORDER_NUM
        FROM ' || v_hierarchy_table_name || ' H4
        WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
        v_vs_where_display_order_num || '
        AND H4.PARENT_ID = R.L9
        AND H4.CHILD_ID = R.L10
      ) N10,
      (
        SELECT H4.DISPLAY_ORDER_NUM
        FROM ' || v_hierarchy_table_name || ' H4
        WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
        v_vs_where_display_order_num || '
        AND H4.PARENT_ID = R.L10
        AND H4.CHILD_ID = R.L11
      ) N11,
      (
        SELECT H4.DISPLAY_ORDER_NUM
        FROM ' || v_hierarchy_table_name || ' H4
        WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
        v_vs_where_display_order_num || '
        AND H4.PARENT_ID = R.L11
        AND H4.CHILD_ID = R.L12
      ) N12,
      (
        SELECT H4.DISPLAY_ORDER_NUM
        FROM ' || v_hierarchy_table_name || ' H4
        WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
        v_vs_where_display_order_num || '
        AND H4.PARENT_ID = R.L12
        AND H4.CHILD_ID = R.L13
      ) N13,';

Line: 1154

      p_module   => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
      p_msg_text => 'v_insert_b_with_p2_l='||LENGTH(v_insert_b_with_p2)
    );

Line: 1162

    v_insert_b_with_p3 := '
      (
        SELECT H4.DISPLAY_ORDER_NUM
        FROM ' || v_hierarchy_table_name || ' H4
        WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
        v_vs_where_display_order_num || '
        AND H4.PARENT_ID = R.L13
        AND H4.CHILD_ID = R.L14
      ) N14,
      (
        SELECT H4.DISPLAY_ORDER_NUM
        FROM ' || v_hierarchy_table_name || ' H4
        WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
        v_vs_where_display_order_num || '
        AND H4.PARENT_ID = R.L14
        AND H4.CHILD_ID = R.L15
      ) N15,
      (
        SELECT H4.DISPLAY_ORDER_NUM
        FROM ' || v_hierarchy_table_name || ' H4
        WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
        v_vs_where_display_order_num || '
        AND H4.PARENT_ID = R.L15
        AND H4.CHILD_ID = R.L16
      ) N16,
      (
        SELECT H4.DISPLAY_ORDER_NUM
        FROM ' || v_hierarchy_table_name || ' H4
        WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
        v_vs_where_display_order_num || '
        AND H4.PARENT_ID = R.L16
        AND H4.CHILD_ID = R.L17
      ) N17,
      (
        SELECT H4.DISPLAY_ORDER_NUM
        FROM ' || v_hierarchy_table_name || ' H4
        WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
        v_vs_where_display_order_num || '
        AND H4.PARENT_ID = R.L17
        AND H4.CHILD_ID = R.L18
      ) N18,
      (
        SELECT H4.DISPLAY_ORDER_NUM
        FROM ' || v_hierarchy_table_name || ' H4
        WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
        v_vs_where_display_order_num || '
        AND H4.PARENT_ID = R.L18
        AND H4.CHILD_ID = R.L19
      ) N19,
      (
        SELECT H4.DISPLAY_ORDER_NUM
        FROM ' || v_hierarchy_table_name || ' H4
        WHERE H4.HIERARCHY_OBJ_DEF_ID = R.HIERARCHY_OBJ_DEF_ID ' ||
        v_vs_where_display_order_num || '
        AND H4.PARENT_ID = R.L19
        AND H4.CHILD_ID = R.L20
      ) N20
    FROM ('; -- R

Line: 1223

      p_module   => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
      p_msg_text => 'v_insert_b_with_p3_l='||LENGTH(v_insert_b_with_p3)
    );

Line: 1230

    v_insert_b_subquery_r1 := '
      SELECT
        H1.HIERARCHY_OBJ_DEF_ID, ' ||
        v_vs_column_subquery_r || '
        CAST(
          SUBSTR(
            SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 1)+' || pc_delimiter_length || ',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 2)-
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 1)-' || pc_delimiter_length || '
          ) AS NUMBER
        ) L1,
        CAST(
          SUBSTR(
            SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 2)+' || pc_delimiter_length ||',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 3)-
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 2)-' || pc_delimiter_length || '
          ) AS NUMBER
        ) L2,
        CAST(
          SUBSTR(
            SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 3)+' || pc_delimiter_length || ',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 4)-
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 3)-' || pc_delimiter_length || '
          ) AS NUMBER
        ) L3,
        CAST(
          SUBSTR(
            SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 4)+' || pc_delimiter_length || ',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 5)-
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 4)-' || pc_delimiter_length || '
          ) AS NUMBER
        ) L4,
        CAST(
          SUBSTR(
            SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 5)+' || pc_delimiter_length || ',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 6)-
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 5)-' || pc_delimiter_length || '
          ) AS NUMBER
        ) L5,
        CAST(
          SUBSTR(
            SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 6)+' || pc_delimiter_length || ',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 7)-
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 6)-' || pc_delimiter_length || '
          ) AS NUMBER
        ) L6,
        CAST(
          SUBSTR(
            SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 7)+' || pc_delimiter_length || ',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 8)-
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 7)-' || pc_delimiter_length || '
          ) AS NUMBER
        ) L7,
        CAST(
          SUBSTR(
            SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 8)+' || pc_delimiter_length || ',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 9)-
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 8)-' || pc_delimiter_length || '
          ) AS NUMBER
        ) L8,
        CAST(
          SUBSTR(
            SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 9)+' || pc_delimiter_length || ',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 10)-
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 9)-' || pc_delimiter_length || '
          ) AS NUMBER
        ) L9,
        CAST(
          SUBSTR(
            SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 10)+' || pc_delimiter_length || ',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 11)-
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 10)-' || pc_delimiter_length || '
          ) AS NUMBER
        ) L10,';

Line: 1317

      p_module   => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
      p_msg_text => 'v_insert_b_subquery_r1_l='||LENGTH(v_insert_b_subquery_r1)
    );

Line: 1324

    v_insert_b_subquery_r2 := '
        CAST(
          SUBSTR(
            SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 11)+' || pc_delimiter_length || ',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 12)-
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 11)-' || pc_delimiter_length || '
          ) AS NUMBER
        ) L11,
        CAST(
          SUBSTR(
            SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 12)+' || pc_delimiter_length || ',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 13)-
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 12)-' || pc_delimiter_length || '
          ) AS NUMBER
        ) L12,
        CAST(
          SUBSTR(
            SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 13)+' || pc_delimiter_length || ',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 14)-
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 13)-' || pc_delimiter_length || '
          ) AS NUMBER
        ) L13,
        CAST(
          SUBSTR(
            SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 14)+' || pc_delimiter_length || ',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 15)-
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 14)-' || pc_delimiter_length || '
          ) AS NUMBER
        ) L14,
        CAST(
          SUBSTR(
            SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 15)+' || pc_delimiter_length || ',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 16)-
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 15)-' || pc_delimiter_length || '
          ) AS NUMBER
        ) L15,
        CAST(
          SUBSTR(
            SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 16)+' || pc_delimiter_length || ',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 17)-
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 16)-' || pc_delimiter_length || '
          ) AS NUMBER
        ) L16,
        CAST(
          SUBSTR(
            SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 17)+' || pc_delimiter_length || ',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 18)-
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 17)-' || pc_delimiter_length || '
          ) AS NUMBER
        ) L17,
        CAST(
          SUBSTR(
            SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 18)+' || pc_delimiter_length || ',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 19)-
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 18)-' || pc_delimiter_length || '
          ) AS NUMBER
        ) L18,
        CAST(
          SUBSTR(
            SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 19)+' || pc_delimiter_length || ',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 20)-
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 19)-' || pc_delimiter_length || '
          ) AS NUMBER
        ) L19,
        CAST(
          SUBSTR(
            SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 20)+' || pc_delimiter_length || ',
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 21)-
            INSTR(SYS_CONNECT_BY_PATH(H1.CHILD_ID, ''' || pc_delimiter || ''') || ''' || pc_delimiter || ''', ''' || pc_delimiter || ''', 1, 20)-' || pc_delimiter_length || '
          ) AS NUMBER
        ) L20';

Line: 1408

      p_module   => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
      p_msg_text => 'v_insert_b_subquery_r2_l='||LENGTH(v_insert_b_subquery_r2)
    );

Line: 1417

    v_insert_b_subquery_h11 := '
     FROM ( -- H1
        SELECT
          H.HIERARCHY_OBJ_DEF_ID,
          H.DISPLAY_ORDER_NUM, ' ||
          v_vs_column_parent_union1 || '
          CASE
            WHEN H.PARENT_ID = H.CHILD_ID AND H.PARENT_DEPTH_NUM = 1 THEN NULL
            ELSE H.PARENT_ID
          END PARENT_ID, ' ||
          v_vs_column_child_union1 || '
          H.CHILD_ID
        FROM
          FEM_HIERARCHIES HIER,
          FEM_OBJECT_DEFINITION_B DEF,
          FEM_OBJECT_CATALOG_B CAT,
          ' || v_hierarchy_table_name || ' H ' ||
          v_from_composite_only || '
        WHERE
          HIER.MULTI_VALUE_SET_FLAG = ''N'' AND
          HIER.HIERARCHY_TYPE_CODE = :hier_type_code AND
          HIER.DIMENSION_ID = :dimension_id AND
          HIER.PERSONAL_FLAG = ''N'' AND
          CAT.OBJECT_ID = HIER.HIERARCHY_OBJ_ID AND
          DEF.OBJECT_ID = CAT.OBJECT_ID AND
          H.HIERARCHY_OBJ_DEF_ID = DEF.OBJECT_DEFINITION_ID AND
          ((' || v_vs_where_root || 'H.PARENT_ID = H.CHILD_ID AND H.PARENT_DEPTH_NUM = 1) OR
          H.CHILD_DEPTH_NUM - H.PARENT_DEPTH_NUM = 1) ' ||
          v_where_composite_only;

Line: 1449

      p_module   => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
      p_msg_text => 'v_insert_b_subquery_h11_l='||LENGTH(v_insert_b_subquery_h11)
    );

Line: 1458

    v_insert_b_subquery_h11_o := '
      FROM
        FEM_HIERARCHIES HIER,
        FEM_OBJECT_DEFINITION_B DEF,
        FEM_OBJECT_CATALOG_B CAT,
        ' || v_hierarchy_table_name || ' H1 ' ||
        v_from_composite_only || '
      WHERE
        HIER.MULTI_VALUE_SET_FLAG = ''N'' AND
        HIER.HIERARCHY_TYPE_CODE = :hier_type_code AND
        HIER.DIMENSION_ID = :dimension_id AND
        HIER.PERSONAL_FLAG = ''N'' AND
        CAT.OBJECT_ID = HIER.HIERARCHY_OBJ_ID AND
        DEF.OBJECT_ID = CAT.OBJECT_ID AND
        H1.HIERARCHY_OBJ_DEF_ID = DEF.OBJECT_DEFINITION_ID AND
        ((' || v_vs_where_root_o || 'H1.PARENT_ID = H1.CHILD_ID AND H1.PARENT_DEPTH_NUM = 1) OR
        H1.CHILD_DEPTH_NUM - H1.PARENT_DEPTH_NUM = 1) ' ||
        v_where_composite_only;

Line: 1479

      p_module   => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
      p_msg_text => 'v_insert_b_subquery_h11_o_l='||LENGTH(v_insert_b_subquery_h11_o)
    );

Line: 1498

    v_insert_b_subquery_h12 := '
        UNION ALL
        SELECT DISTINCT
          O.HIERARCHY_OBJ_DEF_ID,
          O.DUMMY_DISPLAY_ORDER_NUM, ' ||
          v_vs_column_parent_union2 || '
          O.CHILD_ID PARENT_ID, ' ||
          v_vs_column_child_union2 || '
          O.DUMMY_MEMBER_ID CHILD_ID
        FROM O
        UNION ALL
        SELECT
          O.HIERARCHY_OBJ_DEF_ID,
          O.DUMMY_DISPLAY_ORDER_NUM, ' ||
          v_vs_column_parent_union3 || '
          O.DUMMY_MEMBER_ID PARENT_ID, ' ||
          v_vs_column_child_union3 || '
          O.MEMBER_ID CHILD_ID
        FROM O';

Line: 1520

      p_module   => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
      p_msg_text => 'v_insert_b_subquery_h12_l='||LENGTH(v_insert_b_subquery_h12)
    );

Line: 1532

    v_insert_b_exclude_nonleaf := '
      ) H1
      WHERE
        H1.CHILD_ID <> ' || pc_dummy_member_id || ' AND
        NOT EXISTS (
          SELECT 1
          FROM
            FEM_HIERARCHIES HIER3,
            FEM_OBJECT_DEFINITION_B DEF3,
            FEM_OBJECT_CATALOG_B CAT3,
           ' || v_hierarchy_table_name || ' H3
          WHERE
            HIER3.MULTI_VALUE_SET_FLAG = ''N'' AND
            HIER3.HIERARCHY_TYPE_CODE = :hier_type_code AND
            HIER3.DIMENSION_ID = :dimension_id AND
            HIER3.PERSONAL_FLAG = ''N'' AND
            CAT3.OBJECT_ID = HIER3.HIERARCHY_OBJ_ID AND
            DEF3.OBJECT_ID = CAT3.OBJECT_ID AND
            H3.HIERARCHY_OBJ_DEF_ID = DEF3.OBJECT_DEFINITION_ID AND
            H3.CHILD_DEPTH_NUM - H3.PARENT_DEPTH_NUM = 1 AND
            H1.HIERARCHY_OBJ_DEF_ID = H3.HIERARCHY_OBJ_DEF_ID AND ' ||
            v_vs_where_exclude_nonleaf || '
            H1.CHILD_ID =
              CASE
                WHEN H3.PARENT_ID = H3.CHILD_ID AND H3.PARENT_DEPTH_NUM = 1 THEN NULL
                ELSE H3.PARENT_ID
              END
        )
      START WITH
        H1.PARENT_ID IS NULL
      CONNECT BY
        H1.HIERARCHY_OBJ_DEF_ID = PRIOR H1.HIERARCHY_OBJ_DEF_ID AND ' ||
        v_vs_where_connect_by || '
        H1.PARENT_ID = PRIOR H1.CHILD_ID
      ) R
    )'; -- P

Line: 1571

      p_module   => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
      p_msg_text => 'v_insert_b_exclude_nonleaf_l='||LENGTH(v_insert_b_exclude_nonleaf)
    );

Line: 1584

    v_insert_b_exclude_nonleaf_o := '
        AND H1.CHILD_ID <> ' || pc_dummy_member_id || ' AND
        NOT EXISTS (
          SELECT 1
          FROM
            FEM_HIERARCHIES HIER3,
            FEM_OBJECT_DEFINITION_B DEF3,
            FEM_OBJECT_CATALOG_B CAT3,
           ' || v_hierarchy_table_name || ' H3
          WHERE
            HIER3.MULTI_VALUE_SET_FLAG = ''N'' AND
            HIER3.HIERARCHY_TYPE_CODE = :hier_type_code AND
            HIER3.DIMENSION_ID = :dimension_id AND
            HIER3.PERSONAL_FLAG = ''N'' AND
            CAT3.OBJECT_ID = HIER3.HIERARCHY_OBJ_ID AND
            DEF3.OBJECT_ID = CAT3.OBJECT_ID AND
            H3.HIERARCHY_OBJ_DEF_ID = DEF3.OBJECT_DEFINITION_ID AND
            H3.CHILD_DEPTH_NUM - H3.PARENT_DEPTH_NUM = 1 AND
            H1.HIERARCHY_OBJ_DEF_ID = H3.HIERARCHY_OBJ_DEF_ID AND ' ||
            v_vs_where_exclude_nonleaf || '
            H1.CHILD_ID = H3.PARENT_ID AND
            NOT (H3.PARENT_ID = H3.CHILD_ID AND H3.PARENT_DEPTH_NUM = 1)
        )
      START WITH
        H1.PARENT_ID = H1.CHILD_ID AND H1.PARENT_DEPTH_NUM = 1
      CONNECT BY
        H1.HIERARCHY_OBJ_DEF_ID = PRIOR H1.HIERARCHY_OBJ_DEF_ID AND ' ||
        v_vs_where_connect_by || '
        H1.PARENT_ID = PRIOR H1.CHILD_ID
        AND H1.SINGLE_DEPTH_FLAG = ''Y'' AND H1.CHILD_DEPTH_NUM <> 1
      ) R
    )'; -- P

Line: 1619

      p_module   => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
      p_msg_text => 'v_insert_b_exclude_nonleaf_o_l='||LENGTH(v_insert_b_exclude_nonleaf_o)
    );

Line: 1627

    v_insert_b_subquery_q1 := '
  SELECT
    D.OBJECT_ID,
    D.OBJECT_DEFINITION_ID, ' ||
    v_vs_column_subquery_q || '
    P.L1 LEVEL1_ID,
    COALESCE(P.L2,P.L1) LEVEL2_ID,
    COALESCE(P.L3,P.L2,P.L1) LEVEL3_ID,
    COALESCE(P.L4,P.L3,P.L2,P.L1) LEVEL4_ID,
    COALESCE(P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL5_ID,
    COALESCE(P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL6_ID,
    COALESCE(P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL7_ID,
    COALESCE(P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL8_ID,
    COALESCE(P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL9_ID,
    COALESCE(P.L10,P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL10_ID,
    COALESCE(P.L11,P.L10,P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL11_ID,
    COALESCE(P.L12,P.L11,P.L10,P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL12_ID,
    COALESCE(P.L13,P.L12,P.L11,P.L10,P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL13_ID,
    COALESCE(P.L14,P.L13,P.L12,P.L11,P.L10,P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL14_ID,
    COALESCE(P.L15,P.L14,P.L13,P.L12,P.L11,P.L10,P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL15_ID,
    COALESCE(P.L16,P.L15,P.L14,P.L13,P.L12,P.L11,P.L10,P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL16_ID,
    COALESCE(P.L17,P.L16,P.L15,P.L14,P.L13,P.L12,P.L11,P.L10,P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL17_ID,
    COALESCE(P.L18,P.L17,P.L16,P.L15,P.L14,P.L13,P.L12,P.L11,P.L10,P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL18_ID,
    COALESCE(P.L19,P.L18,P.L17,P.L16,P.L15,P.L14,P.L13,P.L12,P.L11,P.L10,P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL19_ID,
    COALESCE(P.L20,P.L19,P.L18,P.L17,P.L16,P.L15,P.L14,P.L13,P.L12,P.L11,P.L10,P.L9,P.L8,P.L7,P.L6,P.L5,P.L4,P.L3,P.L2,P.L1) LEVEL20_ID,';

Line: 1655

      p_module   => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
      p_msg_text => 'v_insert_b_subquery_q1_l='||LENGTH(v_insert_b_subquery_q1)
    );

Line: 1663

    v_insert_b_subquery_q2 := '
    P.N1 LEVEL1_DISPLAY_ORDER_NUM,
    COALESCE(P.N2,P.N1) LEVEL2_DISPLAY_ORDER_NUM,
    COALESCE(P.N3,P.N2,P.N1) LEVEL3_DISPLAY_ORDER_NUM,
    COALESCE(P.N4,P.N3,P.N2,P.N1) LEVEL4_DISPLAY_ORDER_NUM,
    COALESCE(P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL5_DISPLAY_ORDER_NUM,
    COALESCE(P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL6_DISPLAY_ORDER_NUM,
    COALESCE(P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL7_DISPLAY_ORDER_NUM,
    COALESCE(P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL8_DISPLAY_ORDER_NUM,
    COALESCE(P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL9_DISPLAY_ORDER_NUM,
    COALESCE(P.N10,P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL10_DISPLAY_ORDER_NUM,
    COALESCE(P.N11,P.N10,P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL11_DISPLAY_ORDER_NUM,
    COALESCE(P.N12,P.N11,P.N10,P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL12_DISPLAY_ORDER_NUM,
    COALESCE(P.N13,P.N12,P.N11,P.N10,P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL13_DISPLAY_ORDER_NUM,
    COALESCE(P.N14,P.N13,P.N12,P.N11,P.N10,P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL14_DISPLAY_ORDER_NUM,
    COALESCE(P.N15,P.N14,P.N13,P.N12,P.N11,P.N10,P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL15_DISPLAY_ORDER_NUM,
    COALESCE(P.N16,P.N15,P.N14,P.N13,P.N12,P.N11,P.N10,P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL16_DISPLAY_ORDER_NUM,
    COALESCE(P.N17,P.N16,P.N15,P.N14,P.N13,P.N12,P.N11,P.N10,P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL17_DISPLAY_ORDER_NUM,
    COALESCE(P.N18,P.N17,P.N16,P.N15,P.N14,P.N13,P.N12,P.N11,P.N10,P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL18_DISPLAY_ORDER_NUM,
    COALESCE(P.N19,P.N18,P.N17,P.N16,P.N15,P.N14,P.N13,P.N12,P.N11,P.N10,P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL19_DISPLAY_ORDER_NUM,
    COALESCE(P.N20,P.N19,P.N18,P.N17,P.N16,P.N15,P.N14,P.N13,P.N12,P.N11,P.N10,P.N9,P.N8,P.N7,P.N6,P.N5,P.N4,P.N3,P.N2,P.N1) LEVEL20_DISPLAY_ORDER_NUM
  FROM
    P,
    FEM_HIERARCHIES H,
    FEM_OBJECT_DEFINITION_B D,
    FEM_OBJECT_CATALOG_B C
  WHERE
    D.OBJECT_DEFINITION_ID = P.HIERARCHY_OBJ_DEF_ID AND
    H.DIMENSION_ID = :dimension_id AND
    D.OBJECT_ID = H.HIERARCHY_OBJ_ID AND
    C.OBJECT_ID = D.OBJECT_ID
) Q';

Line: 1698

      p_module   => v_module || '.length_insert_into_' || v_dis_hierarchy_table_name_b,
      p_msg_text => 'v_insert_b_subquery_q2_l='||LENGTH(v_insert_b_subquery_q2)
    );

Line: 1704

      Construct Dynamic SQL Elements for INSERT INTO _TL table
    *************************************************************************/
    --
    -- Construct Dynamic SQL Elements for INSERT INTO _TL table (Part 1)
    --
    v_insert_tl_column_list := '
INSERT INTO ' || v_dis_hierarchy_table_name_tl || ' (
  OBJECT_ID,
  OBJECT_DEFINITION_ID, ' ||
  v_vs_column_list || '
  LEVEL1_ID,
  LEVEL2_ID,
  LEVEL3_ID,
  LEVEL4_ID,
  LEVEL5_ID,
  LEVEL6_ID,
  LEVEL7_ID,
  LEVEL8_ID,
  LEVEL9_ID,
  LEVEL10_ID,
  LEVEL11_ID,
  LEVEL12_ID,
  LEVEL13_ID,
  LEVEL14_ID,
  LEVEL15_ID,
  LEVEL16_ID,
  LEVEL17_ID,
  LEVEL18_ID,
  LEVEL19_ID,
  LEVEL20_ID,
  LANGUAGE,
  SOURCE_LANG,
  OBJECT_NAME,
  OBJECT_DEFINITION_NAME,
  LEVEL1_NAME,
  LEVEL2_NAME,
  LEVEL3_NAME,
  LEVEL4_NAME,
  LEVEL5_NAME,
  LEVEL6_NAME,
  LEVEL7_NAME,
  LEVEL8_NAME,
  LEVEL9_NAME,
  LEVEL10_NAME,
  LEVEL11_NAME,
  LEVEL12_NAME,
  LEVEL13_NAME,
  LEVEL14_NAME,
  LEVEL15_NAME,
  LEVEL16_NAME,
  LEVEL17_NAME,
  LEVEL18_NAME,
  LEVEL19_NAME,
  LEVEL20_NAME,
  LEVEL1_DESCRIPTION,
  LEVEL2_DESCRIPTION,
  LEVEL3_DESCRIPTION,
  LEVEL4_DESCRIPTION,
  LEVEL5_DESCRIPTION,
  LEVEL6_DESCRIPTION,
  LEVEL7_DESCRIPTION,
  LEVEL8_DESCRIPTION,
  LEVEL9_DESCRIPTION,
  LEVEL10_DESCRIPTION,
  LEVEL11_DESCRIPTION,
  LEVEL12_DESCRIPTION,
  LEVEL13_DESCRIPTION,
  LEVEL14_DESCRIPTION,
  LEVEL15_DESCRIPTION,
  LEVEL16_DESCRIPTION,
  LEVEL17_DESCRIPTION,
  LEVEL18_DESCRIPTION,
  LEVEL19_DESCRIPTION,
  LEVEL20_DESCRIPTION,
  CREATION_DATE,
  CREATED_BY,
  LAST_UPDATE_DATE,
  LAST_UPDATED_BY,
  LAST_UPDATE_LOGIN
)
SELECT
  B.OBJECT_ID,
  B.OBJECT_DEFINITION_ID, ' ||
  v_vs_column_member_b_table || '
  B.LEVEL1_ID,
  B.LEVEL2_ID,
  B.LEVEL3_ID,
  B.LEVEL4_ID,
  B.LEVEL5_ID,
  B.LEVEL6_ID,
  B.LEVEL7_ID,
  B.LEVEL8_ID,
  B.LEVEL9_ID,
  B.LEVEL10_ID,
  B.LEVEL11_ID,
  B.LEVEL12_ID,
  B.LEVEL13_ID,
  B.LEVEL14_ID,
  B.LEVEL15_ID,
  B.LEVEL16_ID,
  B.LEVEL17_ID,
  B.LEVEL18_ID,
  B.LEVEL19_ID,
  B.LEVEL20_ID,
  L.LANGUAGE_CODE,';

Line: 1813

    v_insert_tl_name1 := '
  (
    SELECT SL.LANGUAGE_CODE
    FROM FND_LANGUAGES SL
    WHERE SL.INSTALLED_FLAG = ''B''
  ),
  (
    SELECT TL.OBJECT_NAME
    FROM FEM_OBJECT_CATALOG_TL TL
    WHERE TL.OBJECT_ID = B.OBJECT_ID
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.DISPLAY_NAME
    FROM FEM_OBJECT_DEFINITION_TL TL
    WHERE TL.OBJECT_DEFINITION_ID = B.OBJECT_DEFINITION_ID
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.' || v_member_name_col || '
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL1_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  DECODE(B.LEVEL2_ID, ' || pc_dummy_member_id || ', ''' || v_dummy_parent_name || ''',
    (
      SELECT TL.' || v_member_name_col || '
      FROM ' || v_member_tl_table_name || ' TL
      WHERE TL.' || v_member_col || ' = B.LEVEL2_ID ' ||
      v_vs_where_tl || '
      AND   TL.LANGUAGE = L.LANGUAGE_CODE
    )
  ),
  (
    SELECT TL.' || v_member_name_col || '
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL3_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.' || v_member_name_col || '
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL4_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.' || v_member_name_col || '
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL5_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.' || v_member_name_col || '
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL6_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.' || v_member_name_col || '
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL7_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.' || v_member_name_col || '
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL8_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.' || v_member_name_col || '
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL9_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.' || v_member_name_col || '
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL10_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),';

Line: 1907

    v_insert_tl_name2 := '
  (
    SELECT TL.' || v_member_name_col || '
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL11_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.' || v_member_name_col || '
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL12_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.' || v_member_name_col || '
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL13_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.' || v_member_name_col || '
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL14_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.' || v_member_name_col || '
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL15_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.' || v_member_name_col || '
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL16_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.' || v_member_name_col || '
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL17_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.' || v_member_name_col || '
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL18_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.' || v_member_name_col || '
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL19_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.' || v_member_name_col || '
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL20_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),';

Line: 1982

    v_insert_tl_description1 := '
  (
    SELECT TL.DESCRIPTION
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL1_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  DECODE(B.LEVEL2_ID, ' || pc_dummy_member_id || ', ''' || v_dummy_parent_name || ''',
    (
      SELECT TL.DESCRIPTION
      FROM ' || v_member_tl_table_name || ' TL
      WHERE TL.' || v_member_col || ' = B.LEVEL2_ID ' ||
      v_vs_where_tl || '
      AND   TL.LANGUAGE = L.LANGUAGE_CODE
    )
  ),
  (
    SELECT TL.DESCRIPTION
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL3_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.DESCRIPTION
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL4_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.DESCRIPTION
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL5_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.DESCRIPTION
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL6_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.DESCRIPTION
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL7_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.DESCRIPTION
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL8_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.DESCRIPTION
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL9_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.DESCRIPTION
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL10_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),';

Line: 2059

    v_insert_tl_description2 := '
  (
    SELECT TL.DESCRIPTION
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL11_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.DESCRIPTION
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL12_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.DESCRIPTION
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL13_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.DESCRIPTION
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL14_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.DESCRIPTION
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL15_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.DESCRIPTION
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL16_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.DESCRIPTION
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL17_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.DESCRIPTION
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL18_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.DESCRIPTION
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL19_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  (
    SELECT TL.DESCRIPTION
    FROM ' || v_member_tl_table_name || ' TL
    WHERE TL.' || v_member_col || ' = B.LEVEL20_ID ' ||
    v_vs_where_tl || '
    AND   TL.LANGUAGE = L.LANGUAGE_CODE
  ),
  SYSDATE CREATION_DATE,
  :pv_user_id CREATED_BY,
  SYSDATE LAST_UPDATE_DATE,
  :pv_user_id LAST_UPDATED_BY,
  :pv_login_id LAST_UPDATE_LOGIN
FROM
  ' || v_dis_hierarchy_table_name_b || ' B,
  FND_LANGUAGES L
WHERE
  L.INSTALLED_FLAG IN (''B'', ''I'')';

Line: 2150

      p_module   => v_module || '.delete_from_b',
      p_msg_text => 'Deleting from _B table'
    );

Line: 2155

DELETE FROM ' || v_dis_hierarchy_table_name_b;

Line: 2157

    v_deleted_b := SQL%ROWCOUNT;

Line: 2165

      p_module   => v_module || '.insert_into_b_open_attr_enabled_dim',
      p_msg_text => 'Inserting into _B table for Open hierarchy (attribute enabled dimension)'
    );

Line: 2169

    Print_DSQL_Insert_B(
      v_module,
      v_dis_hierarchy_table_name_b,
      v_insert_b_column_list,
      v_insert_b_display_code1,
      v_insert_b_display_code2,
      NULL,
      v_insert_b_with_p1,
      v_insert_b_with_p2,
      v_insert_b_with_p3,
      v_insert_b_subquery_r1,
      v_insert_b_subquery_r2,
      v_insert_b_subquery_h11_o,
      NULL,
      v_insert_b_exclude_nonleaf_o,
      v_insert_b_subquery_q1,
      v_insert_b_subquery_q2,
      pv_user_id,
      pv_user_id,
      pv_login_id,
      'OPEN',
      v_dimension_id,
      v_dimension_id,
      'OPEN',
      v_dimension_id,
      NULL,
      NULL,
      NULL
    );

Line: 2200

      v_insert_b_column_list ||
      v_insert_b_display_code1 ||
      v_insert_b_display_code2 ||
      '' ||
      v_insert_b_with_p1 ||
      v_insert_b_with_p2 ||
      v_insert_b_with_p3 ||
      v_insert_b_subquery_r1 ||
      v_insert_b_subquery_r2 ||
      v_insert_b_subquery_h11_o ||
      '' ||
      v_insert_b_exclude_nonleaf_o ||
      v_insert_b_subquery_q1 ||
      v_insert_b_subquery_q2
    USING
      pv_user_id,
      pv_user_id,
      pv_login_id,
      'OPEN',
      v_dimension_id,
      'OPEN',
      v_dimension_id,
      v_dimension_id;

Line: 2224

    v_inserted_b1 := SQL%ROWCOUNT;

Line: 2231

        p_module   => v_module || '.insert_into_b_recon_attr_enabled_dim',
        p_msg_text => 'Inserting into _B table for Reconciliation hierarchy (attribute enabled dimension)'
      );

Line: 2235

      Print_DSQL_Insert_B(
        v_module,
        v_dis_hierarchy_table_name_b,
        v_insert_b_column_list,
        v_insert_b_display_code1,
        v_insert_b_display_code2,
        v_insert_b_with_o,
        v_insert_b_with_p1,
        v_insert_b_with_p2,
        v_insert_b_with_p3,
        v_insert_b_subquery_r1,
        v_insert_b_subquery_r2,
        v_insert_b_subquery_h11,
        v_insert_b_subquery_h12,
        v_insert_b_exclude_nonleaf,
        v_insert_b_subquery_q1,
        v_insert_b_subquery_q2,
        pv_user_id,
        pv_user_id,
        pv_login_id,
        v_dimension_id,
        v_dimension_id,
        v_dimension_id,
        'RECONCILIATION',
        v_dimension_id,
        'RECONCILIATION',
        v_dimension_id,
        v_dimension_id
      );

Line: 2266

        v_insert_b_column_list ||
        v_insert_b_display_code1 ||
        v_insert_b_display_code2 ||
        v_insert_b_with_o ||
        v_insert_b_with_p1 ||
        v_insert_b_with_p2 ||
        v_insert_b_with_p3 ||
        v_insert_b_subquery_r1 ||
        v_insert_b_subquery_r2 ||
        v_insert_b_subquery_h11 ||
        v_insert_b_subquery_h12 ||
        v_insert_b_exclude_nonleaf ||
        v_insert_b_subquery_q1 ||
        v_insert_b_subquery_q2
      USING
        pv_user_id,
        pv_user_id,
        pv_login_id,
        v_dimension_id,
        v_dimension_id,
        v_dimension_id,
        'RECONCILIATION',
        v_dimension_id,
        'RECONCILIATION',
        v_dimension_id,
        v_dimension_id;

Line: 2298

        p_module   => v_module || '.insert_into_b_recon_attr_disabled_dim',
        p_msg_text => 'Inserting into _B table for Reconciliation hierarchy (attribute disabled dimension)'
      );

Line: 2302

      Print_DSQL_Insert_B(
        v_module,
        v_dis_hierarchy_table_name_b,
        v_insert_b_column_list,
        v_insert_b_display_code1,
        v_insert_b_display_code2,
        v_insert_b_with_o,
        v_insert_b_with_p1,
        v_insert_b_with_p2,
        v_insert_b_with_p3,
        v_insert_b_subquery_r1,
        v_insert_b_subquery_r2,
        v_insert_b_subquery_h11,
        v_insert_b_subquery_h12,
        v_insert_b_exclude_nonleaf,
        v_insert_b_subquery_q1,
        v_insert_b_subquery_q2,
        pv_user_id,
        pv_user_id,
        pv_login_id,
        v_dimension_id,
        v_dimension_id,
        'RECONCILIATION',
        v_dimension_id,
        'RECONCILIATION',
        v_dimension_id,
        v_dimension_id,
        NULL
      );

Line: 2333

        v_insert_b_column_list ||
        v_insert_b_display_code1 ||
        v_insert_b_display_code2 ||
        v_insert_b_with_o ||
        v_insert_b_with_p1 ||
        v_insert_b_with_p2 ||
        v_insert_b_with_p3 ||
        v_insert_b_subquery_r1 ||
        v_insert_b_subquery_r2 ||
        v_insert_b_subquery_h11 ||
        v_insert_b_subquery_h12 ||
        v_insert_b_exclude_nonleaf ||
        v_insert_b_subquery_q1 ||
        v_insert_b_subquery_q2
      USING
        pv_user_id,
        pv_user_id,
        pv_login_id,
        v_dimension_id,
        v_dimension_id,
        'RECONCILIATION',
        v_dimension_id,
        'RECONCILIATION',
        v_dimension_id,
        v_dimension_id;

Line: 2361

    v_inserted_b2 := SQL%ROWCOUNT;

Line: 2374

        p_module   => v_module || '.delete_from_tl',
        p_msg_text => 'Deleting from _TL table'
      );

Line: 2379

DELETE FROM ' || v_dis_hierarchy_table_name_tl;

Line: 2381

      v_deleted_tl := SQL%ROWCOUNT;

Line: 2388

        p_module   => v_module || '.delete_from_tl',
        p_msg_text => 'Inserting into _TL table'
      );

Line: 2392

      Print_DSQL_Insert_TL(
        v_module,
        v_dis_hierarchy_table_name_tl,
        v_insert_tl_column_list,
        v_insert_tl_name1,
        v_insert_tl_name2,
        v_insert_tl_description1,
        v_insert_tl_description2,
        pv_user_id,
        pv_user_id,
        pv_login_id
      );

Line: 2406

        v_insert_tl_column_list ||
        v_insert_tl_name1 ||
        v_insert_tl_name2 ||
        v_insert_tl_description1 ||
        v_insert_tl_description2
      USING
        pv_user_id,
        pv_user_id,
        pv_login_id;

Line: 2416

      v_inserted_tl := SQL%ROWCOUNT;

Line: 2426

      SELECT DISTINCT
        CAT.OBJECT_NAME,
        CAT.OBJECT_ID
      FROM
        FEM_HIERARCHIES HIER,
        FEM_OBJECT_DEFINITION_VL DEF,
        FEM_OBJECT_CATALOG_VL CAT,
       ' || v_hierarchy_table_name || ' H
      WHERE
        HIER.MULTI_VALUE_SET_FLAG = ''Y'' AND
        HIER.DIMENSION_ID = :dimension_id AND
        HIER.PERSONAL_FLAG = ''N'' AND
        CAT.OBJECT_ID = HIER.HIERARCHY_OBJ_ID AND
        DEF.OBJECT_ID = CAT.OBJECT_ID AND
        H.HIERARCHY_OBJ_DEF_ID = DEF.OBJECT_DEFINITION_ID AND ' ||
        v_vs_where_root || '
        H.PARENT_ID = H.CHILD_ID AND H.PARENT_DEPTH_NUM = 1'
      BULK COLLECT INTO
        v_object_name_array,
        v_object_id_array
      USING v_dimension_id;

Line: 2488

      p_msg_text => 'v_deleted_b='    || v_deleted_b   || ', ' ||
                    'v_inserted_b1='  || v_inserted_b1  || ', ' ||
                    'v_inserted_b2='  || v_inserted_b2  || ', ' ||
                    'v_deleted_tl='   || v_deleted_tl  || ', ' ||
                    'v_inserted_tl='  || v_inserted_tl
    );

Line: 2537

      v_insert_b_column_list_l     := LENGTH(v_insert_b_column_list_l);

Line: 2538

      v_insert_b_display_code1_l   := LENGTH(v_insert_b_display_code1_l);

Line: 2539

      v_insert_b_display_code2_l   := LENGTH(v_insert_b_display_code2_l);

Line: 2540

      v_insert_b_with_p1_l         := LENGTH(v_insert_b_with_p1_l);

Line: 2541

      v_insert_b_with_p2_l         := LENGTH(v_insert_b_with_p2_l);

Line: 2542

      v_insert_b_with_p3_l         := LENGTH(v_insert_b_with_p3_l);

Line: 2543

      v_insert_b_subquery_r1_l     := LENGTH(v_insert_b_subquery_r1_l);

Line: 2544

      v_insert_b_subquery_r2_l     := LENGTH(v_insert_b_subquery_r2_l);

Line: 2545

      v_insert_b_subquery_h11_o_l  := LENGTH(v_insert_b_subquery_h11_o_l);

Line: 2546

      v_insert_b_subquery_h11_l    := LENGTH(v_insert_b_subquery_h11_l);

Line: 2547

      v_insert_b_subquery_h12_l    := LENGTH(v_insert_b_subquery_h12_l);

Line: 2548

      v_insert_b_exclude_nonleaf_o_l := LENGTH(v_insert_b_exclude_nonleaf_o_l);

Line: 2549

      v_insert_b_exclude_nonleaf_l := LENGTH(v_insert_b_exclude_nonleaf_l);

Line: 2550

      v_insert_b_subquery_q1_l     := LENGTH(v_insert_b_subquery_q1_l);

Line: 2551

      v_insert_b_subquery_q2_l     := LENGTH(v_insert_b_subquery_q2_l);

Line: 2553

      v_insert_tl_column_list_l  := LENGTH(v_insert_tl_column_list);

Line: 2554

      v_insert_tl_name1_l        := LENGTH(v_insert_tl_name1);

Line: 2555

      v_insert_tl_name2_l        := LENGTH(v_insert_tl_name2);

Line: 2556

      v_insert_tl_description1_l := LENGTH(v_insert_tl_description1);

Line: 2557

      v_insert_tl_description2_l := LENGTH(v_insert_tl_description2);

Line: 2562

        p_msg_text => 'v_insert_b_column_list_l='|| v_insert_b_column_list_l || ', ' ||
                      'v_insert_b_display_code1_l='|| v_insert_b_display_code1_l || ', ' ||
                      'v_insert_b_display_code2_l='|| v_insert_b_display_code2_l || ', ' ||
                      'v_insert_b_with_p1_l='|| v_insert_b_with_p1_l || ', ' ||
                      'v_insert_b_with_p2_l='|| v_insert_b_with_p2_l || ', ' ||
                      'v_insert_b_with_p3_l='|| v_insert_b_with_p3_l || ', ' ||
                      'v_insert_b_subquery_r1_l='|| v_insert_b_subquery_r1_l || ', ' ||
                      'v_insert_b_subquery_r2_l='|| v_insert_b_subquery_r2_l || ', ' ||
                      'v_insert_b_subquery_h11_o_l='|| v_insert_b_subquery_h11_o_l || ', ' ||
                      'v_insert_b_subquery_h11_l='|| v_insert_b_subquery_h11_l || ', ' ||
                      'v_insert_b_subquery_h12_l='|| v_insert_b_subquery_h12_l || ', ' ||
                      'v_insert_b_exclude_nonleaf_o_l='|| v_insert_b_exclude_nonleaf_o_l || ', ' ||
                      'v_insert_b_exclude_nonleaf_l='|| v_insert_b_exclude_nonleaf_l || ', ' ||
                      'v_insert_b_subquery_q1_l='|| v_insert_b_subquery_q1_l || ', ' ||
                      'v_insert_b_subquery_q2_l='|| v_insert_b_subquery_q2_l || ', ' ||
                      'v_insert_tl_column_list_l=' ||v_insert_tl_column_list_l  ||', ' ||
                      'v_insert_tl_name1_l=' ||v_insert_tl_name1_l  ||', ' ||
                      'v_insert_tl_name2_l=' ||v_insert_tl_name2_l  ||', ' ||
                      'v_insert_tl_description1_l=' ||v_insert_tl_description1_l  ||', ' ||
                      'v_insert_tl_description2_l=' ||v_insert_tl_description2_l
      );

Line: 2629

      SELECT *
      FROM FEM_DIS_DIMENSIONS_V
      WHERE DIMENSION_VARCHAR_LABEL <> 'ALL';

Line: 2634

      SELECT *
      FROM FND_CONCURRENT_REQUESTS
      WHERE PARENT_REQUEST_ID = p_parent_request_id;

Line: 2703

      SELECT 1 INTO v_dummy_number
      FROM FEM_DIS_DIMENSIONS_V
      WHERE DIMENSION_VARCHAR_LABEL = p_dimension_varchar_label;