1 package body BSC_PMF_UTILITIES_PVT as
2 /* $Header: BSCVUTIB.pls 120.1 2005/10/25 01:11:39 kyadamak noship $ */
3
4 G_PKG_NAME CONSTANT varchar2(30) := 'BSC_PMF_UTILITIES_PVT';
5
6 --: This function validates if the data set and dimension set associated to the
7 --: analysis option combination is valid or not.
8 --: Valid means that the measure and dimensions are available in the system.
9 --: It returns 1 in case it is valid. Otherwise returns 0.
10 function Validate_Analysis_Option(
11 p_indicator IN number
12 ,p_analysis_option0 IN number
13 ,p_analysis_option1 IN number
14 ,p_analysis_option2 IN number
15 ,p_series_id IN number
16 ) return number is
17
18 TYPE Recdc_value IS REF CURSOR;
19 dc_value Recdc_value;
20
21 l_sql varchar2(5000);
22 l_short_name varchar2(30);
23 l_source varchar2(10);
24 l_num_dim_levels number;
25 l_num_valid_dim_levels number;
26
27 l_return_value number;
28
29 begin
30
31 -- Validate measure short name
32 l_sql := 'select i.short_name, nvl(m.source, ''BSC'')'||
33 ' from bsc_kpi_analysis_measures_b k, bsc_sys_datasets_b d, bsc_sys_measures m, bis_indicators i'||
34 ' where k.indicator = :1 and k.analysis_option0 = :2 and'||
35 ' k.analysis_option1 = :3 and k.analysis_option2 = :4 and'||
36 ' k.series_id = :5 and k.dataset_id = d.dataset_id and'||
37 ' d.measure_id1 = m.measure_id and m.short_name = i.short_name (+)';
38
39 open dc_value for l_sql using p_indicator,p_analysis_option0,p_analysis_option1,p_analysis_option2,p_series_id;
40 fetch dc_value into l_short_name, l_source;
41 close dc_value;
42
43 if l_source = 'PMF' then
44 -- The measure is from PMF
45 if l_short_name IS NULL then
46 -- The measure short name does not exist in BIS_INDICATOR
47 l_return_value := 0;
48 else
49 -- The measure is valid. We need to validate that all the dimensions levels
50 -- associated to this measure in the corresponding dimension set are valid.
51
52 -- Get the number of dimension levels for the dimension set
53 l_sql := 'select count(1)'||
54 ' from (select k.level_shortname'||
55 ' from bsc_kpi_analysis_options_b a, bsc_kpi_dim_levels_vl k'||
56 ' where a.indicator = :1 and a.analysis_group_id = 0 and'||
57 ' a.option_id = :2 and a.parent_option_id = 0 and'||
58 ' a.grandparent_option_id = 0 and a.indicator = k.indicator and'||
59 ' a.dim_set_id = k.dim_set_id and k.level_source = ''PMF'')';
60 open dc_value for l_sql using p_indicator,p_analysis_option0;
61 fetch dc_value into l_num_dim_levels;
62 close dc_value;
63
64 -- Get the number of valid dimension levels forthe dimension set
65 l_sql := 'select count(*)'||
66 ' from (select k.level_shortname'||
67 ' from bsc_kpi_analysis_options_b a, bsc_kpi_dim_levels_vl k,'||
68 ' bisfv_dimension_levels dl, bisfv_performance_measures pm'||
69 ' where a.indicator = :1 and a.analysis_group_id = 0 and'||
70 ' a.option_id = :2 and a.parent_option_id = 0 and'||
71 ' a.grandparent_option_id = 0 and a.indicator = k.indicator and'||
72 ' a.dim_set_id = k.dim_set_id and k.level_source = ''PMF'' and'||
73 ' k.level_shortname = dl.dimension_level_short_name and'||
74 ' pm.measure_short_name = :3 and ('||
75 ' dl.dimension_short_name = pm.dimension1_short_name or'||
76 ' dl.dimension_short_name = pm.dimension2_short_name or'||
77 ' dl.dimension_short_name = pm.dimension3_short_name or'||
78 ' dl.dimension_short_name = pm.dimension4_short_name or'||
79 ' dl.dimension_short_name = pm.dimension5_short_name or'||
80 ' dl.dimension_short_name = pm.dimension6_short_name or'||
81 ' dl.dimension_short_name = pm.dimension7_short_name))';
82 open dc_value for l_sql using p_indicator,p_analysis_option0,l_short_name;
83 fetch dc_value into l_num_valid_dim_levels;
84 close dc_value;
85
86 if l_num_dim_levels <> l_num_valid_dim_levels then
87 l_return_value := 0;
88 else
89 l_return_value := 1;
90 end if;
91
92 end if;
93 else
94 -- The measure is a BSC measure, so it is ok
95 l_return_value := 1;
96 end if;
97
98 return l_return_value;
99
100 EXCEPTION
101 WHEN OTHERS THEN
102 return -1;
103
104 end Validate_Analysis_Option;
105
106
107 /************************************************************************************
108 ************************************************************************************/
109
110 end BSC_PMF_UTILITIES_PVT;
[Home] [Help]