APPS.BSC_DBGEN_BSC_READER SQL Statements

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

Line: 28

SELECT indicator_type, config_type FROM BSC_KPIS_VL where INDICATOR= Ind;

Line: 55

SELECT indicator_type, config_type FROM BSC_KPIS_VL where INDICATOR= Ind;

Line: 86

SELECT indicator_type, config_type FROM BSC_KPIS_VL where INDICATOR= Ind;

Line: 114

    SELECT COUNT(M.MEASURE_COL) NUM_DATA_COLUMNS
    FROM BSC_SYS_MEASURES M, BSC_DB_MEASURE_BY_DIM_SET_V I
    WHERE I.MEASURE_ID = M.MEASURE_ID
    AND I.DIM_SET_ID = p_dim_set
    AND I.INDICATOR = p_indicator
    AND M.TYPE = 0 AND NVL(M.SOURCE, 'BSC') in ( 'BSC', 'PMF');

Line: 136

  SELECT ms.MEASURE_COL, ms.HELP, ms.MEASURE_GROUP_ID, ms.PROJECTION_ID, NVL(ms.MEASURE_TYPE, 1) MTYPE, sysm.source
  FROM BSC_DB_MEASURE_COLS_VL ms,
  BSC_SYS_MEASURES sysm
   WHERE ms.measure_col = sysm.measure_col
  ORDER BY MEASURE_COL;

Line: 269

PROCEDURE  InsertInDBMeasureCols(P_Measure_Col IN BSC_METADATA_OPTIMIZER_PKG.clsMeasureLov) IS

l_stmt VARCHAR2(1000);

Line: 276

   bsc_mo_helper_pkg.writeTmp( 'Inside InsertInDBMeasureCols, P_Measure_Col = ');

Line: 281

  l_stmt := 'DELETE FROM BSC_DB_MEASURE_COLS_TL WHERE MEASURE_COL = :1';

Line: 289

      INSERT INTO BSC_DB_MEASURE_COLS_TL (
      	  MEASURE_COL, LANGUAGE, SOURCE_LANG,
        HELP, MEASURE_GROUP_ID, PROJECTION_ID, MEASURE_TYPE)
		VALUES (P_Measure_Col.fieldName, BSC_METADATA_OPTIMIZER_PKG.gInstalled_Languages(i),  BSC_METADATA_OPTIMIZER_PKG.gLangCode,
			 P_Measure_Col.Description, P_Measure_Col.groupCode, P_Measure_Col.prjMethod,P_Measure_Col.measureType );

Line: 298

  bsc_mo_helper_pkg.writeTmp( 'Compl. InsertInDBMeasureCols');

Line: 304

  Fnd_File.Put_Line(Fnd_File.Log, 'Exception in InsertInDBMeasureCols '||g_error);

Line: 423

  InsertInDBMeasureCols( L_Measure_Col);

Line: 454

  UPDATE BSC_SYS_MEASURES
	SET S_COLOR_FORMULA = BSC_APPS.SET_PROPERTY_VALUE(S_COLOR_FORMULA, propertyName, propertyValue)
	WHERE UPPER(MEASURE_COL) =  upper(dataColumn)
	AND TYPE = 0 AND NVL(SOURCE, 'BSC') = 'BSC';

Line: 477

SELECT NVL(OPERATION, 'SUM') AS OPER,
 NVL(BSC_APPS.GET_PROPERTY_VALUE(S_COLOR_FORMULA, p1),'N') AS PAVGL,
 BSC_APPS.GET_PROPERTY_VALUE(S_COLOR_FORMULA, p2) AS PAVGLTOTAL,
 BSC_APPS.GET_PROPERTY_VALUE(S_COLOR_FORMULA, p3) AS PAVGLCOUNTER
 FROM BSC_SYS_MEASURES
 WHERE UPPER(MEASURE_COL) = UPPER(p4)
 AND TYPE = 0
 AND NVL(SOURCE, 'BSC') = 'BSC';

Line: 533

  SELECT BSC_INTERNAL_COLUMN_S.NEXTVAL INTO l_seq FROM DUAL;

Line: 561

  l_stmt2 VARCHAR2(1000):= 'SELECT distinct nvl(M.MEASURE_COL, C.COLUMN_NAME), NVL(M.OPERATION, ''SUM'') AS OPER,
  BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :1) AS PFORMULASOURCE,
  NVL(BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :2 ),''N'') AS PAVGL,
  BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :3) AS PAVGLTOTAL,
  BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :4) AS PAVGLCOUNTER,
  M.MEASURE_ID, nvl(cols.measure_type, 1) measure_type, m.source
  FROM BSC_SYS_MEASURES M, BSC_DB_TABLES_COLS C, BSC_DB_MEASURE_COLS_VL COLS
  WHERE  M.MEASURE_COL(+) = C.COLUMN_NAME
  AND COLS.measure_col(+) = c.column_name
  AND C.COLUMN_TYPE = ''A''
  AND C.TABLE_NAME LIKE ''BSC_S%'||p_fact||'_'||p_dim_set||'%'||'''
  AND M.TYPE(+) = 0';

Line: 640

   SELECT childlvl.level_Table_name child_lvl
        , parentlvl.level_Table_name parent_lvl
        , LEVEL lvl
     FROM bsc_sys_dim_level_rels rels, bsc_sys_dim_levels_b childlvl, bsc_sys_dim_levels_b parentlvl
    WHERE rels.parent_dim_level_id = parentlvl.dim_level_id
      AND rels.dim_level_id = childlvl.dim_level_id
    START WITH parent_dim_level_id = parentlvl.dim_level_id
      AND parentlvl.level_table_name = p_parent_level
  CONNECT BY rels.parent_dim_level_id||rels.relation_type  = PRIOR rels.dim_level_id||1
)
  SELECT parent_lvl, child_lvl
    FROM tree
 CONNECT BY PRIOR parent_lvl = child_lvl
     AND PRIOR lvl = lvl + 1
   START WITH child_lvl = p_child_level
     AND lvl =
        (
          SELECT MIN(lvl)
            FROM tree
           WHERE child_lvl = p_child_level
        );

Line: 683

  SELECT count(1)
  FROM BSC_SYS_DIM_LEVELS_B child_lvl, BSC_SYS_DIM_LEVELS_B parent_lvl, BSC_SYS_DIM_LEVEL_RELS Rels
  WHERE
  child_lvl.LEVEL_TABLE_NAME  = p_child_level
  AND parent_lvl.level_table_name= p_parent_level
  AND child_lvl.DIM_LEVEL_ID  = Rels.DIM_LEVEL_ID
  AND parent_lvl.DIM_LEVEL_ID = Rels.PARENT_DIM_LEVEL_ID
  AND Rels.RELATION_TYPE = 1;

Line: 714

  SELECT count(1)
  FROM BSC_SYS_DIM_LEVELS_B child_lvl, BSC_SYS_DIM_LEVELS_B parent_lvl, BSC_SYS_DIM_LEVEL_RELS Rels
  WHERE
  child_lvl.LEVEL_TABLE_NAME  = p_child_level
  AND parent_lvl.level_table_name= p_parent_level
  AND child_lvl.DIM_LEVEL_ID  = Rels.DIM_LEVEL_ID
  AND parent_lvl.DIM_LEVEL_ID = Rels.PARENT_DIM_LEVEL_ID
  AND Rels.RELATION_TYPE = 2;

Line: 801

PROCEDURE insert_parents(p_periodicity IN NUMBER, p_parents IN VARCHAR2, p_periodicity_list IN BSC_DBGEN_STD_METADATA.tab_clsPeriodicity) IS
l_parents_list DBMS_SQL.NUMBER_TABLE;

Line: 804

l_stmt VARCHAR2(1000) := 'INSERT INTO '||l_table_name||'(periodicity, source) values (:1, :2)';

Line: 823

    fnd_file.put_line(fnd_file.log, 'Exception in bsc_dbgen_bsc_reader.insert_parents '||g_error);

Line: 833

  SELECT SOURCE
  FROM BSC_SYS_PERIODICITIES_VL
  WHERE PERIODICITY_ID=p_periodicity;

Line: 849

      insert_parents(p_periodicity_list(i).periodicity_id, l_parents, p_periodicity_list);

Line: 854

  l_stmt := ' select distinct source from '||l_table_name||' connect by periodicity = prior source start with periodicity = :1';

Line: 899

  l_stmt := 'SELECT DISTINCT INDICATOR, NAME, PROTOTYPE_FLAG,
      INDICATOR_TYPE, CONFIG_TYPE, PERIODICITY_ID,
      SHARE_FLAG, SOURCE_INDICATOR
      FROM BSC_KPIS_VL ';

Line: 905

	  (SELECT to_number(fact_name) FROM BSC_DB_GEN_KPI_LIST WHERE process_id = :process_ID)';

Line: 957

FUNCTION Get_Facts_To_Delete(p_process_id IN NUMBER) return BSC_DBGEN_STD_METADATA.tab_clsFact IS
BEGIN
  return get_fact_info(p_process_id, 2);

Line: 1001

  l_stmt := 'SELECT DISTINCT M.MEASURE_COL FROM BSC_SYS_MEASURES M, BSC_DB_MEASURE_BY_DIM_SET_V I'
		|| ' WHERE I.MEASURE_ID = M.MEASURE_ID AND ('|| strWhereInIndics ||' )'||
		'  AND M.TYPE = 0  AND NVL(M.SOURCE, ''BSC'') in (''PMF'', ''BSC'') ';

Line: 1016

  l_stmt := 'SELECT DISTINCT M.MEASURE_ID, M.MEASURE_COL '
		||'FROM BSC_SYS_MEASURES M, BSC_DB_MEASURE_BY_DIM_SET_V I '||
		' WHERE I.MEASURE_ID = M.MEASURE_ID AND ('|| strWhereNotInIndics ||' ) '||
		'  AND M.TYPE = 0 AND NVL(M.SOURCE, ''BSC'') in (''BSC'', ''PMF'')';

Line: 1048

    l_stmt := 'SELECT DISTINCT INDICATOR FROM BSC_DB_MEASURE_BY_DIM_SET_V  '||
              ' WHERE ('|| strWhereInMeasures || ')';

Line: 1085

  select table_name, count(1) ct from bsc_db_calculations
where table_name like p_table
and calculation_type=4
group by table_name
having count(1)=
(
select max(ct) from(
select table_name, count(1) ct from bsc_db_calculations
where table_name like p_table
and calculation_type=4
group by table_name )
);

Line: 1113

select table_name from bsc_db_Tables_rels
where table_name like p_fact_pattern
and source_table_name not like p_s_pattern;

Line: 1129

  l_fact_list_temp := Get_Facts_To_Delete(p_process_id);

Line: 1176

  l_stmt2 VARCHAR2(1000):= 'SELECT M.MEASURE_COL, NVL(M.OPERATION, ''SUM'') AS OPER,
  BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :1) AS PFORMULASOURCE,
  NVL(BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :2 ),''N'') AS PAVGL,
  BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :3) AS PAVGLTOTAL,
  BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :4) AS PAVGLCOUNTER,
  M.source
  FROM BSC_SYS_MEASURES M, BSC_DB_MEASURE_BY_DIM_SET_V I
  WHERE I.MEASURE_ID = M.MEASURE_ID
  AND I.MEASURE_COL = M.MEASURE_COL
  AND I.DIM_SET_ID = :5
  AND I.INDICATOR = :6
  AND M.TYPE = 0
  AND NVL(M.SOURCE, ''BSC'') in(''PMF'', ''BSC'') ';

Line: 1368

  SELECT M.MEASURE_COL, NVL(M.OPERATION, 'SUM') AS OPER, m.source measure_source
    FROM BSC_SYS_MEASURES M, BSC_DB_MEASURE_BY_DIM_SET_V I
   WHERE I.MEASURE_ID = M.MEASURE_ID
     AND I.DIM_SET_ID = p_dim_set
     AND I.INDICATOR = to_number(p_fact)
     AND M.TYPE = 0
     AND NVL(M.SOURCE, 'BSC') in('PMF', 'BSC');

Line: 1377

 SELECT sysm.measure_id, nvl(COLS.MEASURE_GROUP_ID,-1) measure_group_id, nvl(COLS.PROJECTION_ID, 0) projection_id , NVL(COLS.MEASURE_TYPE, 1) MEASURE_TYPE
   FROM BSC_DB_MEASURE_COLS_VL COLS , BSC_SYS_MEASURES sysm
  WHERE sysm.measure_col = cols.measure_col(+)
    AND sysm.measure_col = p_col;

Line: 1440

    SELECT kpi.PERIODICITY_ID, NVL(TARGET_LEVEL, 1) AS TARGET_LEVEL, s.calendar_id
    FROM BSC_KPI_PERIODICITIES kpi, bsc_sys_periodicities s
    WHERE
    kpi.periodicity_id = s.periodicity_id
    AND kpi.INDICATOR = to_number(p_fact)
	ORDER BY PERIODICITY_ID;

Line: 1512

  SELECT DISTINCT LEVEL_PK_COL, NAME, 1  TAR_LEVEL
  FROM BSC_SYS_DIM_LEVELS_VL WHERE LEVEL_TABLE_NAME = p_missing_level;

Line: 1522

  l_stmt := 'SELECT DISTINCT kpidim.DIM_LEVEL_INDEX, kpidim.LEVEL_TABLE_NAME, kpidim.LEVEL_PK_COL, kpidim.NAME, NVL(kpidim.TARGET_LEVEL,1) AS TAR_LEVEL' ||
   	' , sysdim.dim_level_id, kpidim.parent_level_rel FROM BSC_KPI_DIM_LEVELS_VL kpidim, BSC_SYS_DIM_LEVELS_B sysdim
	   WHERE
	   kpidim.level_table_name = sysdim.level_table_name
	   AND kpidim.INDICATOR = :1 AND kpidim.DIM_SET_ID = :2  AND kpidim.STATUS = 2';

Line: 1626

  select parent_lvl.level_table_name parent_level, lvl.level_table_name child_level, 'CODE' parent_pk, parent_lvl.level_pk_col child_fk, level
  from
    bsc_sys_dim_level_rels rels,
    bsc_sys_dim_levels_b lvl,
    bsc_sys_dim_levels_b parent_lvl
  where
    lvl.dim_level_id = rels.dim_level_id and
    rels.parent_dim_level_id = parent_lvl.dim_level_id and
    rels.relation_type <> 2 and
    level <= p_num_levels
  connect by rels.dim_level_id= PRIOR rels.parent_dim_level_id
  and rels.relation_type<>2
  and rels.dim_level_id <> rels.parent_dim_level_id
  start with rels.dim_level_id in
   (select dim_level_id from bsc_sys_dim_levels_b where level_table_name = p_level_name)
  order by level;

Line: 1667

  select parent_lvl.level_table_name parent_level, lvl.level_table_name child_level, 'CODE' parent_pk, rels.relation_col child_fk, level
  from
    bsc_sys_dim_level_rels rels,
    bsc_sys_dim_levels_b lvl,
    bsc_sys_dim_levels_b parent_lvl
  where
    lvl.dim_level_id = rels.dim_level_id and
    rels.parent_dim_level_id = parent_lvl.dim_level_id and
    rels.relation_type <> 2 and
    level <= p_num_levels
  connect by PRIOR rels.dim_level_id||rels.relation_type = rels.parent_dim_level_id||1
  and rels.dim_level_id <> rels.parent_dim_level_id
  start with rels.parent_dim_level_id in
    (select dim_level_id from bsc_sys_dim_levels_b where level_table_name = p_level_name)
  order by level;

Line: 1704

 SELECT dim_level_id , level_pk_col
 FROM bsc_sys_dim_levels_b
 WHERE level_table_name = p_level;

Line: 1738

  l_stmt := 'SELECT distinct dim.INDICATOR, dim.DIM_SET_ID, kpi.name , ''BSC''
             FROM BSC_KPI_DIM_LEVELS_B dim, BSC_KPIS_VL kpi
			 WHERE dim.indicator = kpi.indicator and ('|| l_stmt||') order by indicator, dim_set_id';

Line: 1771

    SELECT DISTINCT DIM_SET_ID
     FROM BSC_DB_DATASET_DIM_SETS_V
    WHERE INDICATOR = to_number(p_fact)
 ORDER BY DIM_SET_ID;

Line: 1798

select distinct mv_name from bsc_kpi_data_tables
where indicator = to_number(p_fact)
and dim_set_id = p_dim_set
and mv_name not like 'BSC_S_%ZMV';

Line: 1819

select distinct dim_level_id, level_table_name, level_pk_col from bsc_db_tables_cols cols, bsc_sys_dim_levels_b lvl
    where
    cols.table_name like p_s_table
    and cols.column_type='P'
    and cols.column_name = lvl.level_pk_col;

Line: 1857

  select count(1) into l_count from bsc_db_tables_cols where table_name=p_b_table and column_name=p_column;

Line: 1867

select source_formula
from bsc_db_tables_cols
where table_name=p_s_table
and column_type='A'
and column_name=p_column;

Line: 1886

  select count(1) into l_count from bsc_db_tables_rels
  where table_name = p_s_table and source_table_name=p_b_table;

Line: 1901

  select distinct s_table.column_name
       , s_table.source_formula
       , measures.MEASURE_GROUP_ID
	   , measures.PROJECTION_ID
	   , NVL(measures.MEASURE_TYPE, 1) MEASURE_TYPE
	   , sysm.MEASURE_ID
   From  bsc_db_tables_cols s_table,
         bsc_db_tables_cols b_table,
         BSC_DB_MEASURE_COLS_VL measures,
         BSC_SYS_MEASURES sysm
  where  s_table.table_name = p_s_table
    and b_table.table_name = p_base_table
    and s_table.column_name = b_table.column_name
    and s_table.column_type = 'A'
    and measures.measure_col = s_table.column_name
    AND measures.measure_col = sysm.measure_col;

Line: 1919

  select distinct s_table.column_name, s_table.source_formula,
         measures.MEASURE_GROUP_ID, measures.PROJECTION_ID, NVL(measures.MEASURE_TYPE, 1) MEASURE_TYPE,
         sysm.MEASURE_ID
    from
         bsc_db_tables_cols s_table,
         bsc_db_tables_cols b_table,
         BSC_DB_MEASURE_COLS_VL measures,
         BSC_SYS_MEASURES sysm
   where s_table.table_name =p_s_table
    and b_table.table_name = p_base_table
    and s_table.column_name = b_table.column_name
    and s_table.column_type = 'A'
    and measures.measure_col = s_table.column_name
    AND measures.measure_col = sysm.measure_col
  union all
   select distinct s_table.column_name, s_table.source_formula,
         measures.MEASURE_GROUP_ID, measures.PROJECTION_ID, NVL(measures.MEASURE_TYPE, 1) MEASURE_TYPE,
         sysm.MEASURE_ID
    from
         bsc_db_tables_cols s_table,
         bsc_db_tables_cols b_table,
         BSC_DB_MEASURE_COLS_VL measures,
         BSC_SYS_MEASURES sysm
   where s_table.table_name =p_s_table
    and b_table.table_name(+) = p_base_table
    and s_table.column_name = b_table.column_name(+)
    and s_table.column_name like 'BSCIC%'
    and s_table.column_type = 'A'
    and measures.measure_col = s_table.column_name
    AND measures.measure_col = sysm.measure_col(+);

Line: 1954

  SELECT table_name FROM
  BSC_DB_TABLES_RELS rels
  WHERE table_name like 'BSC_S%'||p_fact||'_'||p_dim_set||'%'
  AND source_table_name not like 'BSC_S%'
  AND p_base_table IN (select table_name from bsc_db_tables_rels rels2 connect by table_name=prior source_table_name start with table_name = rels.table_name);

Line: 2016

SELECT periodicity_id from bsc_db_tables where table_name=p_table;

Line: 2033

SELECT db_column_name FROM bsc_sys_periodicities
WHERE periodicity_id = p_periodicity_id AND calendar_id = p_calendar_id;

Line: 2051

select table_name, count(1) ct from bsc_db_calculations
where table_name like 'BSC_S_%'||p_fact||'_'||p_dim_set||'%'
and calculation_type=4
group by table_name
having count(1)= p_num_zero_code;

Line: 2061

select
a.DIM_LEVEL_ID,a.LEVEL_TABLE_NAME,a.level_pk_col
from
bsc_sys_dim_levels_b a,
(select a.LEVEL_TABLE_NAME,0 RELATION_TYPE
from
bsc_kpi_dim_levels_b a
where
a.indicator=p_kpi
and a.PARENT_LEVEL_INDEX is null
union all
select a.LEVEL_TABLE_NAME,e.RELATION_TYPE
from
bsc_kpi_dim_levels_b a,
bsc_kpi_dim_levels_b b,
bsc_sys_dim_levels_b c,
bsc_sys_dim_levels_b d,
bsc_sys_dim_level_rels e
where
a.indicator=p_kpi
and b.indicator=p_kpi
and a.PARENT_LEVEL_INDEX=b.DIM_LEVEL_INDEX
and a.LEVEL_TABLE_NAME=c.LEVEL_TABLE_NAME
and b.LEVEL_TABLE_NAME=d.LEVEL_TABLE_NAME
and e.DIM_LEVEL_ID=c.DIM_LEVEL_ID
and e.PARENT_DIM_LEVEL_ID=d.DIM_LEVEL_ID) b
where b.LEVEL_TABLE_NAME=a.LEVEL_TABLE_NAME
and b.relation_type<>1;

Line: 2116

select distinct rels.table_name
from bsc_db_Tables_rels rels,
bsc_db_tables src
where
rels.source_table_name = src.table_name
and src.table_type=0
and rels.table_name like 'BSC_B%'
connect by rels.table_name=prior rels.source_table_name
start with rels.table_name in -- lowest level S tables
( SELECT table_name FROM
  BSC_DB_TABLES_RELS rels
  WHERE table_name like p_prefix||p_fact||'_'||p_dim_set||'%'
  AND source_table_name not like 'BSC_S%'
) ;

Line: 2154

select sysdim.dim_level_id, source_type, source_code
   from bsc_kpi_dim_levels_b kpidim,
   bsc_sys_dim_levels_b sysdim,
   bsc_sys_filters_views filters
   where
   kpidim.level_table_name = sysdim.level_table_name
   and kpidim.level_view_name <> sysdim.level_view_name
   and sysdim.dim_level_id = filters.dim_level_id
   and filters.level_view_name = kpidim.level_view_name
   and kpidim.indicator = to_number(p_fact)
   and kpidim.level_table_name = p_level;

Line: 2167

  l_stmt := '(select dim_level_value from bsc_sys_filters where ';

Line: 2187

 SELECT level_view_name
   FROM bsc_kpi_dim_levels_b
  WHERE indicator=to_number(p_fact)
    AND level_table_name = p_level;

Line: 2196

    l_level_view  := '(select code from '||l_level_view ||')';

Line: 2210

select p.periodicity_id from
bsc_sys_periodicities p, bsc_kpis_vl k
where
p.yearly_flag =1
and p.calendar_id=k.calendar_id
and k.indicator = to_number(p_fact);

Line: 2237

select c.fiscal_year
from bsc_sys_calendars_b c, bsc_kpis_vl k
where c.calendar_id = k.calendar_id
and k.indicator = to_number(p_fact);

Line: 2260

select current_period
  from bsc_db_tables
 where table_name=p_table_name;

Line: 2274

select current_year
  from bsc_sys_calendars_b cal
     , bsc_sys_periodicities per
     , bsc_db_tables dbtbl
 where dbtbl.table_name = p_table_name
   and dbtbl.periodicity_id = per.periodicity_id
   and per.calendar_id = cal.calendar_id;

Line: 2294

  select current_period from bsc_kpi_periodicities
  where indicator=to_number(p_fact)
    and periodicity_id=p_periodicity;

Line: 2300

  select source, db_column_name, calendar_id from bsc_sys_periodicities
  where periodicity_id=pp_periodicity;

Line: 2304

  select periodicity_id from bsc_kpi_periodicities
  where indicator = to_number(p_fact);

Line: 2364

  l_stmt := 'select '||l_db_col  ||' from bsc_db_calendar where calendar_id=:1 and '||
  			get_db_calendar_column(l_cal_id, l_kpi_periodicity) ||' = :2 and year = :3';

Line: 2410

SELECT kpi.indicator
  FROM BSC_KPIS_VL KPI,
       BSC_KPI_PROPERTIES PROP
 WHERE KPI.INDICATOR = PROP.INDICATOR
   AND PROP.PROPERTY_CODE = BSC_METADATA_OPTIMIZER_PKG.IMPL_TYPE
   AND PROP.PROPERTY_VALUE = '2';

Line: 2435

SELECT DISTINCT mv_name
  FROM bsc_kpi_data_tables
 WHERE indicator = to_number(p_fact)
   AND dim_set_id = p_dim_set
   AND mv_name like 'BSC%ZMV';

Line: 2461

  l_stmt := 'SELECT DISTINCT LEVEL_TABLE_NAME, DIM_LEVEL_INDEX ' ||
   	' FROM BSC_KPI_DIM_LEVELS_VL WHERE INDICATOR = :1 AND STATUS = 2';

Line: 2484

SELECT short_name
  FROM bsc_sys_dim_levels_b
 WHERE level_table_name = p_dim_level_table_name;

Line: 2503

SELECT nvl(sysm.measure_col, tmp.value_v) measure_col
FROM bsc_sys_measures sysm, bsc_tmp_big_in_cond tmp
where tmp.session_id = p_session_id
and tmp.variable_id = p_variable_id
and tmp.value_v = sysm.short_name(+)
order by tmp.value_n;

Line: 2510

  DELETE bsc_tmp_big_in_cond WHERE session_id=l_session_id and variable_id=l_variable_id;

Line: 2515

    INSERT INTO BSC_TMP_BIG_IN_COND (session_id, variable_id, value_n, value_v)
    VALUES (l_session_id, l_variable_id, l_index/*l_counter*/, p_short_names(l_index));

Line: 2542

SELECT nvl(sysd.level_table_name, tmp.value_v) level_table_name
FROM bsc_sys_dim_levels_b sysd, bsc_tmp_big_in_cond tmp
where tmp.session_id = p_session_id
and tmp.variable_id = p_variable_id
and tmp.value_v = sysd.short_name(+)
order by tmp.value_n;

Line: 2550

  DELETE bsc_tmp_big_in_cond WHERE session_id=l_session_id and variable_id=l_variable_id;

Line: 2554

    INSERT INTO BSC_TMP_BIG_IN_COND (session_id, variable_id, value_n, value_v)
    VALUES (l_session_id, l_variable_id, l_index/*l_counter*/, p_short_names(l_index));

Line: 2574

SELECT property_value
  FROM
       BSC_KPI_PROPERTIES
 WHERE INDICATOR = p_fact
   AND PROPERTY_CODE = BSC_METADATA_OPTIMIZER_PKG.IMPL_TYPE;

Line: 2589

    SELECT fnd_profile.value('BSC_ADVANCED_SUMMARIZATION_LEVEL') into l_mv_level from dual;

Line: 2605

 select count(1) into l_count
 from bsc_kpi_dim_levels_b lvl,
 bsc_kpi_properties prop,
 bsc_kpis_vl kpis
 where kpis.indicator= lvl.indicator
 and kpis.indicator=prop.indicator
 and lvl.level_table_name=p_level_name
 and prop.property_code='IMPLEMENTATION_TYPE'
 and prop.property_value=2
 and kpis.prototype_flag not in (2,3,4);

Line: 2641

  delete bsc_tmp_big_in_cond where session_id=l_session_id and variable_id=1;

Line: 2643

    insert into bsc_tmp_big_in_cond (session_id, variable_id, value_n) values (l_session_id, 1,
        to_number(BSC_DBGEN_METADATA_READER.g_assume_production_facts(i)));

Line: 2646

  select count(1) into l_count
  from bsc_kpi_dim_levels_b lvl,
  bsc_kpi_properties prop,
  bsc_kpis_vl kpis
  where kpis.indicator= lvl.indicator
  and kpis.indicator=prop.indicator
  and lvl.level_table_name=p_level_name
  and prop.property_code='IMPLEMENTATION_TYPE'
  and prop.property_value=2
  and kpis.prototype_flag<>2
  and kpis.indicator in
  (select value_n from bsc_tmp_big_in_cond where session_id=l_session_id
   and variable_id=1);

Line: 2674

  select parent_lvl.level_table_name parent_level, lvl.level_table_name child_level, 'CODE' parent_pk,
  -- bug 5168537
  --rels.relation_col child_fk,
  parent_lvl.level_pk_col child_fk,
  level
  from
    bsc_sys_dim_level_rels rels,
    bsc_sys_dim_levels_b lvl,
    bsc_sys_dim_levels_b parent_lvl
  where
    lvl.dim_level_id = rels.dim_level_id and
    rels.parent_dim_level_id = parent_lvl.dim_level_id and
    rels.relation_type <> 2 and
    level <= p_num_levels
  connect by rels.dim_level_id= PRIOR rels.parent_dim_level_id
  and rels.relation_type<>2
  and rels.dim_level_id <> rels.parent_dim_level_id
  start with rels.dim_level_id in
   (select dim_level_id from bsc_sys_dim_levels_b where level_table_name = p_level_name)
  order by level;

Line: 2724

  select parent_lvl.level_table_name parent_level, lvl.level_table_name child_level, 'CODE' parent_pk, rels.relation_col child_fk, level
  from
    bsc_sys_dim_level_rels rels,
    bsc_sys_dim_levels_b lvl,
    bsc_sys_dim_levels_b parent_lvl
  where
    lvl.dim_level_id = rels.dim_level_id and
    rels.parent_dim_level_id = parent_lvl.dim_level_id and
    rels.relation_type <> 2 and
    level <= p_num_levels
  connect by PRIOR rels.dim_level_id||rels.relation_type = rels.parent_dim_level_id||1
  and rels.dim_level_id <> rels.parent_dim_level_id
  start with rels.parent_dim_level_id in
    (select dim_level_id from bsc_sys_dim_levels_b where level_table_name = p_level_name)
  order by level;

Line: 2763

select periodicity_id from bsc_db_tables where table_name in
(
select distinct table_name from bsc_db_tables_rels rels
where
rels.table_name like 'BSC_S%'||p_fact||'_'||p_dim_set||'%'
and rels.source_table_name not like 'BSC_S%'||p_fact||'_'||p_dim_set||'%'
connect by prior rels.table_name=rels.source_table_name
and rels.relation_type<>2
start with rels.source_table_name = p_b_table
);

Line: 2792

function get_last_update_date_for_fact(p_fact in varchar2) return date is
cursor cdate is
select last_update_date from bsc_kpis_vl
where indicator=p_fact;

Line: 2814

select distinct table_name from bsc_db_tables_rels
where instr(table_name, p_pattern)=1
and source_table_name not like p_pattern||'%'
connect by  source_table_name = prior table_name
start with source_table_name = p_b_table_name;

Line: 2825

   SELECT table_name child_lvl
        , source_Table_name parent_lvl
        , LEVEL lvl
     FROM bsc_db_tables_rels rels
    START WITH source_table_name = p_parent_level
  CONNECT BY source_table_name  = PRIOR table_name
)
  SELECT parent_lvl, child_lvl, lvl
    FROM tree
 CONNECT BY PRIOR parent_lvl = child_lvl
     AND PRIOR lvl = lvl + 1
   START WITH child_lvl = p_child_level
     AND lvl =
        (
          SELECT MIN(lvl)
            FROM tree
           WHERE child_lvl = p_child_level
        )
   union
   select source_table_name,table_name , -1 from bsc_db_Tables_rels
    where table_name=p_parent_level
    order by lvl ;

Line: 2849

select column_name, source_column source_column_name from bsc_db_Tables_cols
where table_name = p_table_name
and column_type in(p_col_type1, p_col_type2);

Line: 2910

    execute immediate 'insert into '||l_col_maps_table||'(table_name, source_table_name, column_name, source_column_name) '
      ||' values (:1, :2, :3, :4)' using l_var1(i), l_var2(i), l_var3(i), l_var4(i);

Line: 2913

  l_stmt := 'SELECT column_name FROM '||l_col_maps_table||' WHERE TABLE_NAME LIKE :1
             CONNECT BY  TABLE_NAME = PRIOR SOURCE_TABLE_NAME
             AND  COLUMN_NAME = PRIOR SOURCE_COLUMN_NAME
             START WITH TABLE_NAME = :2';

Line: 2925

  l_stmt :=  'SELECT  column_name FROM '||l_col_maps_table||
             ' WHERE TABLE_NAME= :1
               CONNECT BY  PRIOR TABLE_NAME =  SOURCE_TABLE_NAME
               AND  PRIOR COLUMN_NAME =  SOURCE_COLUMN_NAME
               START WITH SOURCE_TABLE_NAME= :2 AND SOURCE_COLUMN_NAME =:3';

Line: 2949

select properties from bsc_db_tables where table_name=p_table_name;

Line: 2962

    update bsc_db_tables
    set properties = l_property_value
    where table_name = p_table_name;

Line: 2973

    update bsc_db_tables
    set properties = properties||l_property_value
    where table_name = p_table_name;

Line: 2992

  update bsc_db_tables
    set properties = l_final_value
    where table_name = p_table_name;