1 PACKAGE BSC_UPDATE_COLOR AS
2 /* $Header: BSCDCOLS.pls 120.2 2007/06/14 17:42:42 nkishore ship $ */
3 --
4 -- Global Constants
5 --
6 GREEN CONSTANT NUMBER(11) := 24865;
7 YELLOW CONSTANT NUMBER(11) := 49919;
8 RED CONSTANT NUMBER(11) := 192;
9 GRAY CONSTANT NUMBER(11) := 8421504;
10
11 --
12 -- Procedures and Fuctions
13 --
14
15 /*===========================================================================+
16 |
17 | Name: Color_Indic_Dim_Combination
18 |
19 | Description: This function calculate the colors for the given KPI Measure.
20 | The color are stored in bsc_sys_kpi_colors table.
21 |
22 | Returns: If any error ocurrs, this function add the error message
23 | to the error stack and return FALSE. Otherwise return
24 | TRUE
25 |
26 | Notes:
27 |
28 +============================================================================*/
29 FUNCTION Color_Indic_Dim_Combination(
30 x_indic_code IN NUMBER,
31 x_kpi_measure_id IN NUMBER,
32 x_calc_color_flag IN BOOLEAN,
33 x_indic_pl_flag IN BOOLEAN,
34 x_indic_initiatives_flag IN BOOLEAN,
35 x_indic_precalculated_flag IN BOOLEAN,
36 x_tab_id IN NUMBER,
37 x_dim_combination IN BSC_UPDATE_UTIL.t_array_of_number,
38 x_num_families IN NUMBER,
39 x_periodicity_id IN NUMBER,
40 x_comp_level_pk_col IN VARCHAR2,
41 x_dim_set_id IN NUMBER,
42 x_color_by_total IN NUMBER,
43 x_measure_formula IN VARCHAR2,
44 x_current_fy IN NUMBER,
45 x_aw_flag IN BOOLEAN -- AW_INTEGRATION: need this new parameter
46 )
47 RETURN BOOLEAN;
48
49
50 /*===========================================================================+
51 |
52 | Name: Color_Indicator
53 |
54 | Description: This function calculate the colors for the given indicator.
55 | The coor are stored in bsc_sys_kpi_colors table.
56 |
57 | Parameters: x_indic_code - indicator code
58 |
59 | Returns: If any error ocurrs, this function add the error message
60 | to the error stack and return FALSE. Otherwise return
61 | TRUE
62 |
63 | Notes:
64 |
65 +============================================================================*/
66 FUNCTION Color_Indicator(
67 x_indic_code IN NUMBER
68 ) RETURN BOOLEAN;
69
70
71 --LOCKING: new function
72 FUNCTION Color_Indicator_AT(
73 x_indic_code IN NUMBER
74 ) RETURN BOOLEAN;
75
76
77 /*===========================================================================+
78 |
79 | Name: Create_Temp_Tab_Tables
80 |
81 | Description: This function creates some TAB temporal tables to be used
82 | by the coloring process of all indicators. These tables are
83 | BSC_TMP_TAB_DEF and BSC_TMP_TAB_COM
84 |
85 | Returns: If any error ocurrs, this function add the error message
86 | to the error stack and return FALSE. Otherwise return
87 | TRUE
88 |
89 | Notes:
90 |
91 +============================================================================*/
92 FUNCTION Create_Temp_Tab_Tables RETURN BOOLEAN;
93
94 --LOCKING: new function
95 FUNCTION Create_Temp_Tab_Tables_AT RETURN BOOLEAN;
96
97
98 /*===========================================================================+
99 |
100 | Name: Drop_Temp_Tab_Tables
101 |
102 | Description: This function drops some TAB temporal tables used
103 | by the coloring process of all indicators.
104 |
105 | Returns: If any error ocurrs, this function add the error message
106 | to the error stack and return FALSE. Otherwise return
107 | TRUE
108 |
109 | Notes:
110 |
111 +============================================================================*/
112 FUNCTION Drop_Temp_Tab_Tables RETURN BOOLEAN;
113
114
115 /*===========================================================================+
116 |
117 | Name: Get_Condition_On_Color_Table
118 |
119 | Description: This function return in the parameter x_condition a condition
120 | on the table that is used the get the right records to calculate
121 | the color of the indicator.
122 |
123 | Parameters: x_indic_code - indicator code
124 | x_dim_set_id - default dimension set
125 | x_table_name - table name used to calculate the colors
126 | x_dim_combination - dimension combination (dimension index within the family)
127 | x_dim_com_keys - dimension combination (key names)
128 | - Arrays are from 0 to x_num_families - 1
129 | x_num_families - number of families of the list
130 | x_comp_level_pk_col - key name of comparison drill
131 | x_color_by_total - flag to indicate if the color is calculated with totals
132 | - even if the indicator enters in comparison
133 | x_condition - to retunr the condition
134 |
135 | Returns: If any error ocurrs, this function add the error message
136 | to the error stack and return FALSE. Otherwise return
137 | TRUE
138 |
139 | Notes:
140 |
141 +============================================================================*/
142 FUNCTION Get_Condition_On_Color_Table(
143 x_indic_code IN NUMBER,
144 x_aw_flag IN BOOLEAN, -- AW_INTEGRATION: new parameter
145 x_indic_pl_flag IN BOOLEAN,
146 x_indic_precalculated_flag IN BOOLEAN,
147 x_dim_set_id IN NUMBER,
148 x_table_name IN VARCHAR2,
149 x_dim_combination IN BSC_UPDATE_UTIL.t_array_of_number,
150 x_dim_com_keys IN BSC_UPDATE_UTIL.t_array_of_varchar2,
151 x_num_families IN NUMBER,
152 x_comp_level_pk_col IN VARCHAR2,
153 x_color_by_total IN NUMBER,
154 x_condition OUT NOCOPY VARCHAR2,
155 x_bind_vars_values OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
156 x_num_bind_vars OUT NOCOPY NUMBER ,
157 x_aw_limit_tbl IN OUT NOCOPY BIS_PMV_PAGE_PARAMETER_TBL --AW_INTEGRATION: new parameter
158 ) RETURN BOOLEAN;
159
160 /*===========================================================================+
161 |
162 | Name: Get_Table_Used_To_Color
163 |
164 | Description: This function get the table used by the indicator in the
165 | given specification.
166 |
167 | Parameters: x_indic_code - indicator code
168 | x_peridiocity - selected periodicity
169 | x_dim_set_id - selected dimension set
170 | x_comp_level_pk_col - pk column name of drill in comparison
171 | - It's NULL if the indicator doesnt enter
172 | - in comparison
173 | x_color_by_total - The color is calculated with totals
174 | - even if the indicator enter in comparison
175 | x_selected_dim_keys - array with the selected drills.
176 | This array is from 0 to x_num_selected_dim_keys - 1
177 | x_num_selected_dim_keys - number of selected drills
178 |
179 | Returns: If any error ocurrs, this function add the error message
180 | to the error stack and return NULL. Otherwise return
181 | the table name.
182 |
183 | Notes:
184 |
185 +============================================================================*/
186 FUNCTION Get_Table_Used_To_Color(
187 x_indic_code IN NUMBER,
188 x_periodicity_id IN NUMBER,
189 x_dim_set_id IN NUMBER,
190 x_comp_level_pk_col IN VARCHAR2,
191 x_color_by_total IN NUMBER,
192 x_selected_dim_keys IN BSC_UPDATE_UTIL.t_array_of_varchar2,
193 x_num_selected_dim_keys IN NUMBER,
194 x_level_comb OUT NOCOPY VARCHAR2
195 ) RETURN VARCHAR2;
196
197
198 /*===========================================================================+
199 |
200 | Name: Insert_Tab_Combinations
201 |
202 | Description: This function insert into temporal table BSC_TMP_TAB_COM
203 | the different combinations of dimensions for the given tab.
204 |
205 | Returns: If any error ocurrs, this function add the error message
206 | to the error stack and return FALSE. Otherwise return
207 | TRUE
208 |
209 | Notes:
210 |
211 +============================================================================*/
212 FUNCTION Insert_Tab_Combinations(
213 x_tab_id IN NUMBER,
214 x_num_dimensions_by_family IN BSC_UPDATE_UTIL.t_array_of_number,
215 x_max_family_index IN NUMBER
216 ) RETURN BOOLEAN;
217
218 /*===========================================================================+
219 |
220 | Name: get_KPI_property_value
221 |
222 | Description: This function return the property vaue for a given kpi and
223 | property_code
224 |
225 | Returns: It return the property value
226 | Notes:
227 |
228 +============================================================================*/
229 FUNCTION Get_KPI_Property_Value(x_indicator number,
230 x_property_code varchar2,
231 x_default_value number ) RETURN NUMBER;
232
233
234 TYPE t_objective_color_rec IS RECORD (
235 tab_id bsc_tabs_b.tab_id%TYPE,
236 objective_id bsc_kpis_b.indicator%TYPE,
237 obj_pl_flag BOOLEAN,
238 obj_initiatives_flag BOOLEAN,
239 obj_precalculated_flag BOOLEAN,
240 periodicity_id bsc_kpis_b.periodicity_id%TYPE,
241 current_fy NUMBER,
242 aw_flag BOOLEAN,
243 sim_flag BOOLEAN
244 );
245
246 --BugFix 6110361
247 TYPE t_key_rec IS RECORD (
248 dimvalues VARCHAR2(100),
249 period NUMBER,
250 vreal NUMBER,
251 vprev NUMBER,
252 trend NUMBER
253 );
254
255 TYPE t_key_tbl_type IS TABLE OF t_key_rec INDEX BY BINARY_INTEGER;
256
257 END BSC_UPDATE_COLOR;
[Home] [Help]