[Home] [Help]
14: l_level_considered.delete;
15: p_dim_levels.delete;
16: g_count:=0;--to test infinite recursion
17: for i in 1..p_dim_level_list.count loop
18: if not bsc_aw_utility.in_array(l_level_considered,p_dim_level_list(i)) then
19: get_parent_children(p_dim_level_list(i),l_level_considered,p_dim_parent_child);
20: end if;
21: end loop;
22: --get the level info
46: if g_count>100000 then
47: log_n('Infinite loop detected in get_parent_children');
48: raise g_exception;
49: end if;
50: if bsc_aw_utility.in_array(p_level_considered,p_level) then
51: return;
52: end if;
53: p_level_considered(p_level_considered.count+1):=p_level;
54: l_parents.delete;
374: p_measure(i).agg_formula.sql_agg_formula:=l_agg_formula(i);
375: if upper(p_measure(i).agg_formula.sql_agg_formula)='AVERAGE' then /*database avg function is AVG */
376: p_measure(i).agg_formula.sql_agg_formula:='AVG';
377: end if;
378: p_measure(i).agg_formula.std_aggregation:=bsc_aw_utility.is_std_aggregation_function(l_agg_formula(i));--Y N
379: p_measure(i).agg_formula.avg_aggregation:=bsc_aw_utility.is_avg_aggregation_function(l_agg_formula(i));--Y N
380: p_measure(i).sql_aggregated:='N';/*default */
381: p_measure(i).forecast:=l_projection(i);
382: p_measure(i).forecast_method:=null;--for now, we dont implement projection in aw
375: if upper(p_measure(i).agg_formula.sql_agg_formula)='AVERAGE' then /*database avg function is AVG */
376: p_measure(i).agg_formula.sql_agg_formula:='AVG';
377: end if;
378: p_measure(i).agg_formula.std_aggregation:=bsc_aw_utility.is_std_aggregation_function(l_agg_formula(i));--Y N
379: p_measure(i).agg_formula.avg_aggregation:=bsc_aw_utility.is_avg_aggregation_function(l_agg_formula(i));--Y N
380: p_measure(i).sql_aggregated:='N';/*default */
381: p_measure(i).forecast:=l_projection(i);
382: p_measure(i).forecast_method:=null;--for now, we dont implement projection in aw
383: bsc_aw_utility.merge_property(p_measure(i).property,l_property(i),',');/*if BALANCE LAST VALUE, this can contain balance loaded Y/N column name */
379: p_measure(i).agg_formula.avg_aggregation:=bsc_aw_utility.is_avg_aggregation_function(l_agg_formula(i));--Y N
380: p_measure(i).sql_aggregated:='N';/*default */
381: p_measure(i).forecast:=l_projection(i);
382: p_measure(i).forecast_method:=null;--for now, we dont implement projection in aw
383: bsc_aw_utility.merge_property(p_measure(i).property,l_property(i),',');/*if BALANCE LAST VALUE, this can contain balance loaded Y/N column name */
384: end loop;
385: Exception when others then
386: log_n('Exception in get_dim_set_measures '||sqlerrm);
387: raise;
454: bsc_metadata.get_target_levels(p_kpi,p_target_dim_set.dim_set,l_levels);
455: --now for these levels, fill in the dim info
456: for i in 1..l_dim.count loop
457: for j in 1..l_dim(i).levels.count loop
458: if bsc_aw_utility.in_array(l_levels,l_dim(i).levels(j).level_name) then
459: p_target_dim_set.dim(i).levels(p_target_dim_set.dim(i).levels.count+1):=l_dim(i).levels(j);
460: end if;
461: end loop;
462: end loop;
480: p_target_dim_set.calendar.periodicity.delete;
481: --bsc_metadata.get_target_periodicity will only return lowest periodicity for targets
482: bsc_metadata.get_target_periodicity(p_kpi,p_target_dim_set.dim_set,l_periodicities);
483: for i in 1..l_calendar.periodicity.count loop
484: if bsc_aw_utility.in_array(l_periodicities,l_calendar.periodicity(i).periodicity) then
485: p_target_dim_set.calendar.periodicity(p_target_dim_set.calendar.periodicity.count+1):=l_calendar.periodicity(i);
486: p_target_dim_set.calendar.periodicity(p_target_dim_set.calendar.periodicity.count).lowest_level:='Y'; --these are lowest level for targets
487: end if;
488: end loop;
549: Begin
550: bsc_metadata.get_zero_code_levels(p_kpi,p_dim_set.dim_set,l_levels);
551: for i in 1..p_dim_set.dim.count loop
552: for j in 1..p_dim_set.dim(i).levels.count loop
553: if bsc_aw_utility.in_array(l_levels,p_dim_set.dim(i).levels(j).level_name) then
554: --we can say there is zero code only if there is zero code level for this level
555: --we can do this since check_dim_zero_code comes after get_dim_properties in bsc_aw_adapter_kpi
556: if p_dim_set.dim(i).levels(j).zero_code_level is not null then
557: p_dim_set.dim(i).levels(j).zero_code:='Y';
639: l_feed_periodicity.delete;
640: get_B_table_feed_periodicity(p_kpi,p_dim_set.dim_set,l_base_tables(i),l_feed_periodicity);
641: for j in 1..p_dim_set.calendar.periodicity.count loop
642: if p_dim_set.calendar.periodicity(j).lowest_level='Y'
643: and bsc_aw_utility.in_array(l_feed_periodicity,p_dim_set.calendar.periodicity(j).periodicity) then
644: l_ds_periodicity(l_ds_periodicity.count+1):=p_dim_set.calendar.periodicity(j);
645: end if;
646: end loop;
647: end if;
656: end loop;
657: end if;
658: if l_ds_periodicity.count=0 then --problem...
659: log('No target periodicity for B table '||l_base_tables(i)||' could be determined. Fatal...');
660: raise bsc_aw_utility.g_exception;
661: end if;
662: for j in 1..l_ds_periodicity.count loop
663: if g_debug then
664: log('Create datasource for B table '||l_base_tables(i)||' and periodicity '||l_ds_periodicity(j).periodicity);
749: l_ds_measures dbms_sql.varchar2_table;--data source measures , same as dim set measures
750: l_formula dbms_sql.varchar2_table;
751: l_level bsc_aw_adapter_kpi.level_r;
752: l_prj_table varchar2(100);
753: l_partition bsc_aw_utility.object_partition_r;
754: l_bt_copy bsc_aw_adapter_kpi.base_table_r;
755: Begin
756: bsc_metadata.get_base_table_levels(p_kpi,p_dim_set,p_base_table,l_bt_levels,l_bt_level_fks,l_bt_level_pks,l_bt_feed_levels);
757: bsc_metadata.get_base_table_measures(p_kpi,p_dim_set,p_base_table,l_ds_measures,l_formula);
912:
913: --------------------------
914: procedure init_all is
915: Begin
916: g_debug:=bsc_aw_utility.g_debug;
917: g_count:=0;
918: Exception when others then
919: null;
920: End;
920: End;
921:
922: procedure log(p_message varchar2) is
923: Begin
924: bsc_aw_utility.log(p_message);
925: Exception when others then
926: null;
927: End;
928: