APPS.BSC_DIM_LEVEL_FILTERS_PUB SQL Statements

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

Line: 39

 SELECT DISTINCT a.tab_id,
        a.dim_level_index,
        a.dim_level_value,
        b.dim_level_id,
        (SELECT level_view_name FROM bsc_sys_dim_levels_b WHERE dim_level_id =b.dim_level_id)level_view_name
  FROM  bsc_user_list_access a,
        bsc_sys_com_dim_levels b
  WHERE a.tab_id =b.tab_id
  AND   a.dim_level_index= b.dim_level_index
  AND   a.tab_id =p_tab_id
  AND   a.dim_level_value <>0
  AND   b.dim_level_id = p_dim_level_id
  ORDER BY A.dim_level_value;

Line: 213

   BSC_DIM_LEVEL_FILTERS_PVT.delete_filters(
                      p_tab_id         =>   p_tab_id
                     ,p_dim_level_id   =>   p_dim_level_id
                     ,p_commit         =>   FND_API.G_FALSE
                     ,x_return_status  =>   x_return_status
                     ,x_msg_count      =>   x_msg_count
                     ,x_msg_data       =>   x_msg_data
                     );

Line: 226

       BSC_DIM_LEVEL_FILTERS_PVT.insert_filters(
                     p_source_type     =>   1
                    ,p_source_code     =>   p_tab_id
                    ,p_dim_level_id    =>   p_dim_level_id
                    ,p_dim_level_value =>   l_level_value
                    ,p_commit          =>   FND_API.G_FALSE
                    ,x_return_status   =>   x_return_status
                    ,x_msg_count       =>   x_msg_count
                    ,x_msg_data        =>   x_msg_data
                    );

Line: 240

     BSC_DIM_LEVEL_FILTERS_PVT.insert_filters(
                     p_source_type     =>   1
                    ,p_source_code     =>   p_tab_id
                    ,p_dim_level_id    =>   p_dim_level_id
                    ,p_dim_level_value =>   0
                    ,p_commit          =>   FND_API.G_FALSE
                    ,x_return_status   =>   x_return_status
                    ,x_msg_count       =>   x_msg_count
                    ,x_msg_data        =>   x_msg_data
                    );

Line: 269

     BSC_DIM_LEVEL_FILTERS_PUB.update_tab_who_columns(
                         p_tab_id        =>   p_tab_id
                        ,x_return_status =>   x_return_status
                        ,x_msg_count     =>   x_msg_count
                        ,x_msg_data      =>   x_msg_data
                      );

Line: 343

   SELECT a.dim_level_id, a.parent_dim_level_id,  a.relation_col,
     (SELECT b.name FROM bsc_sys_dim_levels_vl b WHERE b.dim_level_id = a.parent_dim_level_id) parent_name,
     (SELECT c.level_view_name FROM bsc_sys_filters_views c WHERE a.parent_dim_level_id = c.dim_level_id AND c.source_type= 1
             AND rownum <2 AND c.source_code=p_tab_id) parent_filter_view
   FROM bsc_sys_dim_level_rels a
   WHERE a.dim_level_id = p_dim_level_id
     AND a.relation_type=1
     AND a.dim_level_id IN
        (SELECT dim_level_id
         FROM bsc_kpi_dim_level_properties WHERE indicator IN
             (SELECT indicator FROM bsc_tab_indicators WHERE tab_id = p_tab_id));

Line: 356

   SELECT a.dim_level_id child_dim_level_id
   FROM bsc_sys_dim_level_rels a
   WHERE a.parent_dim_level_id = p_dim_level_id
     AND a.relation_type=1
     AND a.dim_level_id IN
        (SELECT dim_level_id
         FROM bsc_kpi_dim_level_properties WHERE indicator IN
             (SELECT indicator FROM bsc_tab_indicators WHERE tab_id = p_tab_id));

Line: 366

   SELECT level_view_name
   FROM bsc_sys_filters_views
   WHERE source_type = 1
       AND source_code = p_tab_id
       AND dim_level_id = p_dim_level_id;

Line: 373

   SELECT DISTINCT kpi_measure_id, indicator
   FROM bsc_kpi_analysis_measures_b
   WHERE indicator IN (SELECT DISTINCT ti.indicator
                       FROM bsc_tab_indicators ti
                       WHERE ti.tab_id = p_tab_id);

Line: 385

   SELECT COUNT(0) INTO  l_filter_count
   FROM bsc_sys_filters
   WHERE source_type = 1
     AND source_code=p_tab_id
     AND dim_level_id = p_dim_level_id;

Line: 423

       BSC_DIM_LEVEL_FILTERS_PVT.delete_filters_view (
                      p_tab_id         =>   p_tab_id
                     ,p_dim_level_id   =>   p_dim_level_id
                     ,p_commit         =>   FND_API.G_FALSE
                     ,x_return_status  =>   x_return_status
                     ,x_msg_count      =>   x_msg_count
                     ,x_msg_data       =>   x_msg_data
                     );

Line: 434

       BSC_DIM_LEVEL_FILTERS_PVT.delete_filters (
                      p_tab_id         =>   p_tab_id
                     ,p_dim_level_id   =>   p_dim_level_id
                     ,p_commit         =>   FND_API.G_FALSE
                     ,x_return_status  =>   x_return_status
                     ,x_msg_count      =>   x_msg_count
                     ,x_msg_data       =>   x_msg_data
                     );

Line: 564

   SELECT a.dim_level_id, a.parent_dim_level_id,  a.relation_col,
     (SELECT b.name FROM bsc_sys_dim_levels_vl b WHERE b.dim_level_id = a.parent_dim_level_id) parent_name,
     (SELECT c.level_view_name FROM bsc_sys_filters_views c WHERE a.parent_dim_level_id = c.dim_level_id AND c.source_type= 1
             AND rownum <2 AND c.source_code=p_tab_id) parent_filter_view
   FROM bsc_sys_dim_level_rels a
   WHERE a.dim_level_id = p_dim_level_id
     AND a.relation_type=1;

Line: 573

   SELECT level_view_name
   FROM bsc_sys_filters_views
   WHERE source_type = 1
     AND source_code = p_tab_id
     AND dim_level_id = p_dim_level_id;

Line: 580

   SELECT level_table_name
   FROM bsc_sys_dim_levels_b
   WHERE dim_level_id = p_dim_level_id;

Line: 585

   SELECT level_view_name
   FROM bsc_sys_dim_levels_b
   WHERE dim_level_id = p_dim_level_id;

Line: 613

    BSC_DIM_LEVEL_FILTERS_PVT.insert_filters_view(
        p_source_type        =>  1
       ,p_source_code        =>  p_tab_id
       ,p_dim_level_id       =>  p_dim_level_id
       ,p_level_table_name   =>  l_table
       ,p_level_view_name    =>  l_view
       ,p_commit             =>  FND_API.G_FALSE
       ,x_return_status      =>  x_return_status
       ,x_msg_count          =>  x_msg_count
       ,x_msg_data           =>  x_msg_data
       );

Line: 633

  SELECT COUNT(0) INTO l_filter_count
  FROM bsc_sys_filters
  WHERE source_type= 1
    AND source_code = p_tab_id
    AND dim_level_id = p_dim_level_id;

Line: 663

  SELECT COUNT(0) INTO l_cnt
  FROM user_objects
  WHERE object_name = l_view;

Line: 672

  l_sql := 'CREATE VIEW ' || l_view || ' AS (SELECT d.* FROM ' || l_sql_tables || ' WHERE ' || l_sql_where_cond || ')';

Line: 744

  SELECT COUNT(DISTINCT object_name) INTO l_view_count
  FROM user_objects
  WHERE object_name like p_dimension_table || '_V%';

Line: 754

    SELECT COUNT(0) INTO l_v_count
    FROM user_objects
    WHERE object_name = l_result;

Line: 828

   SELECT a.dim_level_id, a.parent_dim_level_id,  a.relation_col,
     (SELECT b.name FROM bsc_sys_dim_levels_vl b WHERE b.dim_level_id = a.parent_dim_level_id) parent_name,
     (SELECT c.level_view_name FROM bsc_sys_filters_views c WHERE a.parent_dim_level_id = c.dim_level_id AND c.source_type= 1
             AND rownum <2 AND c.source_code=p_tab_id) parent_filter_view
   FROM bsc_sys_dim_level_rels a
   WHERE a.dim_level_id = p_dim_level_id AND a.relation_type=1 ;

Line: 836

   SELECT level_view_name
   FROM bsc_sys_dim_levels_b
   WHERE dim_level_id = p_dim_level_id;

Line: 883

      x_sql := 'SELECT TO_CHAR(d.code) ID, d.name VALUE FROM ' || l_sql_tables || ' WHERE ' || l_sql_where_cond || ' ORDER BY VALUE ';

Line: 953

   SELECT level_view_name
   FROM bsc_sys_filters_views
   WHERE source_type=1
     AND source_code = p_tab_id
     AND dim_level_id = p_dim_level_id;

Line: 973

  l_dummy_sql := 'SELECT NULL ID, NULL VALUE FROM DUAL WHERE ROWNUM<1';

Line: 994

      x_sql := 'SELECT TO_CHAR(f.dim_level_value) ID, ';

Line: 995

      x_sql := x_sql || '(SELECT v.name FROM ' || l_sql_view || ' v ';

Line: 1089

  SELECT level_view_name
     FROM bsc_sys_filters_views
     WHERE source_type = 1
       AND source_code = p_tab_id
       AND dim_level_id = p_ch_level_id;

Line: 1096

  SELECT level_view_name
  FROM bsc_sys_filters_views
  WHERE source_type = 1
    AND source_code = p_tab_id
    AND dim_level_id = p_pa_level_id;

Line: 1103

  SELECT relation_col
  FROM bsc_sys_dim_level_rels
  WHERE dim_level_id = p_ch_level_id AND parent_dim_level_id = p_pa_level_id;

Line: 1147

      l_cur_sql := 'SELECT d.code FROM ' || l_ch_view || ' d, ' || l_pa_view || ' p WHERE d.' || l_rel_col || '=p.code(+) AND p.code IS NULL';

Line: 1157

      SELECT COUNT(0) INTO l_filter_count
      FROM bsc_sys_filters
      WHERE source_type= 1
        AND source_code = p_tab_id
        AND dim_level_id = p_ch_level_id;

Line: 1164

         DELETE
         FROM bsc_sys_filters
         WHERE source_type = 1
           AND source_code = p_tab_id
           AND dim_level_id = p_ch_level_id;

Line: 1178

            DELETE
            FROM bsc_sys_filters
            WHERE source_type = 1
              AND source_code = p_tab_id
              AND dim_level_id = p_ch_level_id
              AND dim_level_value = l_code;

Line: 1193

      SELECT COUNT(0) INTO l_filter_count
      FROM bsc_sys_filters
      WHERE source_type= 1
        AND source_code = p_tab_id
        AND dim_level_id = p_ch_level_id;

Line: 1201

        DELETE
        FROM bsc_sys_filters
        WHERE source_type = 1
          AND source_code = p_tab_id
          AND dim_level_id = p_ch_level_id;

Line: 1273

PROCEDURE update_tab_who_columns
(
 p_tab_id               IN               NUMBER
,p_commit               IN               VARCHAR2 := FND_API.G_FALSE
,x_return_status        OUT       NOCOPY VARCHAR2
,x_msg_count            OUT       NOCOPY NUMBER
,x_msg_data             OUT       NOCOPY VARCHAR2
)
IS

 l_user_id      VARCHAR2(100);

Line: 1291

    SELECT COUNT(0)  INTO l_row_cnt
    FROM bsc_tabs_b
    WHERE tab_id = p_tab_id;

Line: 1300

      UPDATE bsc_tabs_b
      SET last_updated_by = l_user_id,
          last_update_date = SYSDATE,
          last_update_login = l_login_id
      WHERE tab_id = p_tab_id;

Line: 1339

            x_msg_data      :=  x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.update_tab_who_columns ';

Line: 1341

            x_msg_data      :=  SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.update_tab_who_columns ';

Line: 1350

            x_msg_data      :=  x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.update_tab_who_columns ';

Line: 1352

            x_msg_data      :=  SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.update_tab_who_columns ';

Line: 1356

END update_tab_who_columns;

Line: 1383

  SELECT a.default_key_value,
         a.level_shortname,
         a.level_view_name
  FROM   bsc_kpi_dim_levels_vl a
  WHERE  a.indicator IN (SELECT b.indicator
                           FROM bsc_tab_indicators b
                          WHERE b.tab_id = p_tab_id)
    AND  a.level_shortname IN ( SELECT d.short_name
                                  FROM bsc_sys_dim_levels_b d
                                 WHERE d.dim_level_id = p_dim_level_id);

Line: 1395

   SELECT a.dim_level_id, a.parent_dim_level_id,  a.relation_col
   FROM bsc_sys_dim_level_rels a
   WHERE a.dim_level_id = p_dim_level_id
     AND a.relation_type=1
     AND EXISTS ( SELECT indicator
                    FROM bsc_kpi_dim_level_properties b
                   WHERE b.dim_level_id = a.parent_dim_level_id
                     AND indicator IN (SELECT indicator
                                         FROM bsc_tab_indicators WHERE tab_id = p_tab_id
                                      )
                    );

Line: 1408

   SELECT a.dim_level_id child_dim_level_id
   FROM bsc_sys_dim_level_rels a
   WHERE a.parent_dim_level_id = p_dim_level_id
     AND a.relation_type=1
     AND EXISTS (SELECT b.dim_level_id
                 FROM bsc_kpi_dim_level_properties b
                 WHERE b.indicator IN (SELECT indicator FROM bsc_tab_indicators WHERE tab_id = p_tab_id)
                   and b.dim_level_id = a.dim_level_id );

Line: 1567

  SELECT * FROM bsc_sys_dim_levels_vl WHERE dim_level_id = cp_dim_level_id;

Line: 1570

  SELECT *
  FROM  bsc_kpi_dim_levels_vl
  WHERE level_shortname = cp_level_shortname AND indicator IN (SELECT indicator
                                         FROM bsc_tab_indicators WHERE tab_id = p_tab_id
                                        );

Line: 1577

   SELECT a.dim_level_id, a.parent_dim_level_id,  a.relation_col
   FROM bsc_sys_dim_level_rels a
   WHERE a.dim_level_id = p_parent_level_id
        AND a.relation_type=1
        AND EXISTS ( SELECT indicator
                     FROM bsc_kpi_dim_level_properties b
                     WHERE b.dim_level_id = a.parent_dim_level_id
                       AND indicator IN (SELECT indicator
                                         FROM bsc_tab_indicators WHERE tab_id = p_tab_id
                                        )
                    );

Line: 1618

             l_sql := 'SELECT  DISTINCT ' || l_rel_col || ' FROM ' || l_ch_level_view || ' WHERE CODE IN (' || p_level_vals_list || ' )';

Line: 1660

             l_sql := 'SELECT  DISTINCT ' || l_rel_col || ' FROM ' || l_ch_level_view || ' WHERE CODE IN (' || p_level_vals_list || ' )';

Line: 1780

  SELECT short_name FROM bsc_sys_dim_levels_vl WHERE dim_level_id = cp_dim_level_id;

Line: 1783

  SELECT *
  FROM  bsc_kpi_dim_levels_vl
  WHERE level_shortname = cp_level_shortname AND indicator IN (SELECT indicator
                                         FROM bsc_tab_indicators WHERE tab_id = p_tab_id
                                        );

Line: 1790

   SELECT a.dim_level_id child_dim_level_id,
          (SELECT b.short_name FROM bsc_sys_dim_levels_vl b WHERE b.dim_level_id = a.dim_level_id and rownum < 2) child_short_name
   FROM bsc_sys_dim_level_rels a
   WHERE a.parent_dim_level_id = p_dim_level_id
     AND a.relation_type=1
     AND a.dim_level_id IN
        (SELECT dim_level_id
         FROM bsc_kpi_dim_level_properties WHERE indicator IN
             (SELECT indicator FROM bsc_tab_indicators WHERE tab_id = p_tab_id));

Line: 1832

           l_sql := 'SELECT  DISTINCT CODE FROM ' || l_ch_level_view || ' WHERE ' ||  l_rel_col || ' IN (' || p_level_vals_list || ' )';

Line: 1883

             l_sql := 'SELECT  DISTINCT CODE FROM ' || l_ch_level_view || ' WHERE ' ||  l_rel_col || ' IN (' || p_level_vals_list || ' )';