[Home] [Help]
2907: p_num_filter_first_level:=0;
2908: --logic taken from BSC_UPDATE_CALC.apply_filters(x_table_name); 115.21 2003/03/25
2909: begin
2910: SELECT indicator, dim_set_id into l_indicator_id,l_dim_set_id
2911: FROM bsc_kpi_data_tables_v WHERE (table_name = p_table OR
2912: table_name = (SELECT DISTINCT table_name FROM bsc_db_calculations WHERE
2913: parameter1 = p_table AND calculation_type = 5)) and rownum=1;
2914: exception when NO_DATA_FOUND then
2915: l_indicator_id:=null;
3613: --------------------------------------------------
3614: --for S tables
3615: cursor c0 (p_table varchar2)
3616: is select distinct bsc_db_tables_cols.column_name,bsc_kpi_dim_levels_b.dim_level_index from
3617: bsc_kpi_data_tables,
3618: bsc_db_tables_cols,
3619: bsc_kpi_dim_levels_b
3620: where
3621: bsc_kpi_data_tables.table_name=bsc_db_tables_cols.table_name
3617: bsc_kpi_data_tables,
3618: bsc_db_tables_cols,
3619: bsc_kpi_dim_levels_b
3620: where
3621: bsc_kpi_data_tables.table_name=bsc_db_tables_cols.table_name
3622: and bsc_db_tables_cols.table_name =p_table
3623: and bsc_db_tables_cols.column_type='P'
3624: and bsc_kpi_data_tables.indicator=bsc_kpi_dim_levels_b.indicator
3625: and bsc_db_tables_cols.column_name=bsc_kpi_dim_levels_b.level_pk_col
3620: where
3621: bsc_kpi_data_tables.table_name=bsc_db_tables_cols.table_name
3622: and bsc_db_tables_cols.table_name =p_table
3623: and bsc_db_tables_cols.column_type='P'
3624: and bsc_kpi_data_tables.indicator=bsc_kpi_dim_levels_b.indicator
3625: and bsc_db_tables_cols.column_name=bsc_kpi_dim_levels_b.level_pk_col
3626: and bsc_kpi_data_tables.dim_set_id=bsc_kpi_dim_levels_b.dim_set_id
3627: order by bsc_kpi_dim_levels_b.dim_level_index;
3628: --
3622: and bsc_db_tables_cols.table_name =p_table
3623: and bsc_db_tables_cols.column_type='P'
3624: and bsc_kpi_data_tables.indicator=bsc_kpi_dim_levels_b.indicator
3625: and bsc_db_tables_cols.column_name=bsc_kpi_dim_levels_b.level_pk_col
3626: and bsc_kpi_data_tables.dim_set_id=bsc_kpi_dim_levels_b.dim_set_id
3627: order by bsc_kpi_dim_levels_b.dim_level_index;
3628: --
3629: --for the B tables
3630: cursor c1 (p_table varchar2,p_owner varchar2)
3663: close c0;
3664: p_number_fk:=p_number_fk-1;
3665: --
3666: if p_number_fk=0 then
3667: --either there are no fk or this could be a table without entries in bsc_kpi_data_tables...like B tables
3668: if g_debug then
3669: write_to_log_file_n('is select distinct bsc_db_tables_cols.column_name,all_tab_columns.column_id...');
3670: end if;
3671: l_owner:=bsc_im_utils.get_table_owner(l_table_name);
3836: l_mv_object BSC_IM_UTILS.varchar_tabletype;
3837: l_number_mv_object number;
3838: ---------------------------------------------------
3839:
3840: -- overcome issue with large data in bsc_kpi_data_tables, use db_tables_rels instead
3841: --cursor c1 (p_indicator number)
3842: --is select distinct upper(table_name),periodicity_id from bsc_kpi_data_tables where indicator=p_indicator
3843: --and table_name is not null;
3844:
3838: ---------------------------------------------------
3839:
3840: -- overcome issue with large data in bsc_kpi_data_tables, use db_tables_rels instead
3841: --cursor c1 (p_indicator number)
3842: --is select distinct upper(table_name),periodicity_id from bsc_kpi_data_tables where indicator=p_indicator
3843: --and table_name is not null;
3844:
3845: cursor c1 (p_indicator number)is
3846: select distinct upper(table_name), substr(table_name, instr(table_name, '_', -1, 1)+1) periodicity_id from bsc_db_tables_rels
3852: cursor c2 (p_indicator number)
3853: is select distinct rel.source_table_name, tab.periodicity_id
3854: from bsc_db_tables_rels rel, bsc_db_tables tab
3855: where rel.table_name in
3856: (select distinct upper(table_name) from bsc_kpi_data_tables where indicator=p_indicator)
3857: and rel.source_table_name like 'BSC_SB_%'
3858: and rel.source_table_name=tab.table_name ;
3859: */
3860: ---------------------------------------------------
3859: */
3860: ---------------------------------------------------
3861: Begin
3862: if g_debug then
3863: l_stmt:='select distinct upper(table_name),periodicity_id from bsc_kpi_data_tables where indicator=:1 '||
3864: 'and table_name is not null';
3865: write_to_log_file_n(l_stmt||' '||p_indicator_id);
3866: end if;
3867: p_number_s_tables:=1;
3877: if g_debug then
3878: l_stmt:='select distinct rel.source_table_name, tab.periodicity_id '||
3879: 'from bsc_db_tables_rels rel, bsc_db_tables tab '||
3880: 'where rel.table_name in '||
3881: '(select distinct upper(table_name) from bsc_kpi_data_tables where indicator=:1) '||
3882: 'and rel.source_table_name like ''BSC_SB_%'' '||
3883: 'and rel.source_table_name=tab.table_name ';
3884: write_to_log_file_n(l_stmt||' '||p_indicator_id);
3885: end if;
3893: close c2;
3894: */
3895: p_number_s_tables:=p_number_s_tables-1;
3896: if g_debug then
3897: write_to_debug_n('S tables and SB from bsc_kpi_data_tables are ');
3898: for i in 1..p_number_s_tables loop
3899: write_to_debug(p_s_tables(i)||' '||p_s_periodicity(i));
3900: end loop;
3901: end if;
6231: cv CurTyp;
6232: -----------------------------------
6233: --Fix bug#3899842: use distinct
6234: --cursor c1 (p_mv_name varchar2)
6235: --is select distinct table_name from bsc_kpi_data_tables where mv_name=p_mv_name;
6236: cursor c1 (p_mv_name_pattern varchar2)
6237: is select distinct table_name from bsc_db_tables_rels
6238: where substr(table_name, 1, length(p_mv_name_pattern))= p_mv_name_pattern;
6239: -----------------------------------
6240: l_pattern varchar2(100);
6241: Begin
6242: p_number_s_tables:=1;
6243: if g_debug then
6244: l_stmt:='select table_name from bsc_kpi_data_tables where mv_name=:1';
6245: write_to_log_file_n(l_stmt||' using '||p_mv);
6246: end if;
6247: --open c1(p_mv);
6248: l_pattern := substr(p_mv, 1, instr(p_mv, '_', -1, 1));