[Home] [Help]
PACKAGE BODY: APPS.BSC_BIS_WRAPPER_PVT
Source
1 PACKAGE BODY BSC_BIS_WRAPPER_PVT AS
2 /* $Header: BSCVBISB.pls 120.0 2005/06/01 16:30:01 appldev noship $ */
3
4 /************************************************************************************
5 ************************************************************************************/
6
7 FUNCTION is_measure_dbi(
8 l_measure_shortname IN VARCHAR2
9 ) RETURN BOOLEAN
10 IS
11 l_region_code VARCHAR2(2000);
12 l_func_name VARCHAR2(2000);
13 l_param_region_code VARCHAR2(2000);
14
15 CURSOR c_akitems IS
16 SELECT nested_region_code FROM ak_region_items
17 WHERE region_code = l_region_code AND item_style = 'NESTED_REGION';
18 BEGIN
19 bsc_jv_pmf.get_pmf_measure(
20 p_measure_shortname => l_measure_shortname
21 ,x_region_code => l_region_code
22 ,x_function_name => l_func_name
23 );
24
25 IF l_region_code IS NULL THEN
26 RETURN FALSE;
27 END IF;
28
29 OPEN c_akitems;
30 FETCH c_akitems INTO l_param_region_code;
31 CLOSE c_akitems;
32
33 RETURN l_param_region_code IS NOT NULL;
34 EXCEPTION
35 WHEN OTHERS THEN
36 IF (c_akitems%ISOPEN) THEN
37 CLOSE c_akitems;
38 END IF;
39 END is_measure_dbi;
40
41 /************************************************************************************
42 ************************************************************************************/
43
44 FUNCTION Get_Actual_Value(
45 p_kpi_code IN NUMBER,
46 p_analysis_option0 IN NUMBER,
47 p_analysis_option1 IN NUMBER,
48 p_analysis_option2 IN NUMBER,
49 p_series_id IN NUMBER,
50 p_user_id IN VARCHAR2,
51 p_responsibility_id IN VARCHAR2
52 ) RETURN VARCHAR2 IS
53
54 l_value VARCHAR2(2000);
55
56 BEGIN
57 l_value := NULL;
58
59 SELECT actual_data INTO l_value
60 FROM bsc_bis_measures_data
61 WHERE user_id = p_user_id AND
62 responsibility_id = p_responsibility_id AND
63 indicator = p_kpi_code AND
64 analysis_option0 = p_analysis_option0 AND
65 analysis_option1 = p_analysis_option1 AND
66 analysis_option2 = p_analysis_option2 AND
67 series_id = p_series_id;
68
69 RETURN l_value;
70
71 EXCEPTION
72 WHEN OTHERS THEN
73 RETURN l_value ;
74
75 END Get_Actual_Value;
76
77 /************************************************************************************
78 ************************************************************************************/
79
80 PROCEDURE Get_Actual_Value_From_PMV(
81 p_kpi_info_rec IN BSC_BIS_WRAPPER_PUB.Kpi_Info_Rec_Type,
82 p_user_id IN VARCHAR2,
83 p_responsibility_id IN VARCHAR2,
84 p_dimension_levels IN BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Tbl_Type,
85 p_time_level_from IN BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Type,
86 p_time_level_to IN BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Type,
87 p_time_comparison_type IN VARCHAR2,
88 p_viewby_level IN VARCHAR2,
89 x_actual_value OUT NOCOPY VARCHAR2,
90 x_compareto_value OUT NOCOPY VARCHAR2,
91 x_return_status OUT NOCOPY VARCHAR2,
92 x_msg_count OUT NOCOPY NUMBER,
93 x_msg_data OUT NOCOPY VARCHAR2
94 ) IS
95 -- Measure Info dimensions
96 l_measure_id NUMBER;
97 l_measure_dimensions BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Tbl_Type;
98
99 -- parameters to get actual from pmv
100 l_region_code VARCHAR2(2000) := NULL;
101 l_function_name VARCHAR2(2000) := NULL;
102 l_time_parameter BIS_PMV_ACTUAL_PVT.TIME_PARAMETER_REC_TYPE;
103 l_parameters BIS_PMV_ACTUAL_PVT.PARAMETER_TBL_TYPE;
104 l_time_parameter_tmp BIS_PMV_ACTUAL_PVT.TIME_PARAMETER_REC_TYPE;
105 l_parameters_tmp BIS_PMV_ACTUAL_PVT.PARAMETER_TBL_TYPE;
106 l_actual_attribute_code VARCHAR2(2000) := NULL;
107 l_compareto_attribute_code VARCHAR2(2000) := NULL;
108
109 -- Others
110 l_i NUMBER;
111 l_j NUMBER;
112 l_dimensionX_short_name VARCHAR2(2000);
113 l_dimension_level BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Type;
114 l_total_dimlevel_short_name VARCHAR2(2000);
115 l_total_dimlevel_value_id NUMBER;
116 l_total_dimlevel_value_name VARCHAR2(2000);
117 l_dim_level_index NUMBER;
118 l_act_tbl BIS_PMV_ACTUAL_PVT.ACTUAL_VALUE_TBL_TYPE;
119 l_actual_value NUMBER := NULL;
120 l_compareto_value NUMBER := NULL;
121 l_sum_actual_value NUMBER := NULL;
122 l_sum_compareto_value NUMBER := NULL;
123
124 BEGIN
125 FND_MSG_PUB.initialize;
126
127 x_actual_value := NULL;
128 x_compareto_value := NULL;
129
130 --dbms_output.put_line('Begin BSC_BIS_WRAPPER_PVT.Get_Actual_Value_From_PMV' );
131
132 -- Get pmf measure info
133 l_measure_id := p_kpi_info_rec.measure_id;
134 l_function_name := p_kpi_info_rec.function_name;
135 l_region_code := p_kpi_info_rec.region_code;
136 l_actual_attribute_code := p_kpi_info_rec.actual_attribute_code;
137 l_compareto_attribute_code := p_kpi_info_rec.compareto_attribute_code;
138 l_measure_dimensions(1).dimension_short_name := p_kpi_info_rec.dim1_short_name;
139 l_measure_dimensions(2).dimension_short_name := p_kpi_info_rec.dim2_short_name;
140 l_measure_dimensions(3).dimension_short_name := p_kpi_info_rec.dim3_short_name;
141 l_measure_dimensions(4).dimension_short_name := p_kpi_info_rec.dim4_short_name;
142 l_measure_dimensions(5).dimension_short_name := p_kpi_info_rec.dim5_short_name;
143 l_measure_dimensions(6).dimension_short_name := p_kpi_info_rec.dim6_short_name;
144 l_measure_dimensions(7).dimension_short_name := p_kpi_info_rec.dim7_short_name;
145
146 --dbms_output.put_line('*l_function_name='||l_function_name);
147 --dbms_output.put_line('*l_region_code='||l_region_code);
148 --dbms_output.put_line('*l_actual_attribute_code='||l_actual_attribute_code);
149 --dbms_output.put_line('*l_compareto_attribute_code='||l_compareto_attribute_code);
150 --dbms_output.put_line('*l_dimension1_short_name='||l_measure_dimensions(1).dimension_short_name);
151 --dbms_output.put_line('*l_dimension2_short_name='||l_measure_dimensions(2).dimension_short_name);
152 --dbms_output.put_line('*l_dimension3_short_name='||l_measure_dimensions(3).dimension_short_name);
153 --dbms_output.put_line('*l_dimension4_short_name='||l_measure_dimensions(4).dimension_short_name);
154 --dbms_output.put_line('*l_dimension5_short_name='||l_measure_dimensions(5).dimension_short_name);
155 --dbms_output.put_line('*l_dimension6_short_name='||l_measure_dimensions(6).dimension_short_name);
156 --dbms_output.put_line('*l_dimension7_short_name='||l_measure_dimensions(7).dimension_short_name);
157
158 IF p_time_level_to.dimension_short_name IS NOT NULL THEN
159 -- By design if p_time_level_to exists then p_time_level_from exists
160 l_time_parameter.time_parameter_name := p_time_level_to.dimension_short_name||'+'||
161 p_time_level_to.level_short_name;
162 l_time_parameter.time_from_id := p_time_level_from.level_value_id;
163 l_time_parameter.time_from_value := p_time_level_from.level_value_name;
164 l_time_parameter.time_to_id := p_time_level_to.level_value_id;
165 l_time_parameter.time_to_value := p_time_level_to.level_value_name;
166
167 --dbms_output.put_line('*l_time_parameter.time_parameter_name='||l_time_parameter.time_parameter_name);
168 --dbms_output.put_line('*l_time_parameter.time_from_id='||l_time_parameter.time_from_id);
169 --dbms_output.put_line('*l_time_parameter.time_from_value='||l_time_parameter.time_from_value);
170 --dbms_output.put_line('*l_time_parameter.time_to_id='||l_time_parameter.time_to_id);
171 --dbms_output.put_line('*l_time_parameter.time_to_value='||l_time_parameter.time_to_value);
172 END IF;
173
174 -- For dimensions other than TIME we use the default dimension level (or total dimension level)
175 -- and pass 'All' as value.
176 l_j := 1;
177 FOR l_i IN 1 .. 7 LOOP
178 l_dimensionX_short_name := l_measure_dimensions(l_i).dimension_short_name;
179 IF (l_dimensionX_short_name IS NOT NULL) AND (NOT Is_Time_Dimension(l_dimensionX_short_name)) THEN
180 l_dim_level_index := Get_Dimension_Level_Index(l_dimensionX_short_name, p_dimension_levels);
181 IF l_dim_level_index = 0 THEN
182 -- A dimension level was not specified for this dimension
183 -- Then use the total dimension level
184 Get_Total_DimLevel_Info(
185 p_dimension_short_name => l_dimensionX_short_name,
186 x_total_dimlevel_short_name => l_total_dimlevel_short_name,
187 x_total_dimlevel_value_id => l_total_dimlevel_value_id,
188 x_total_dimlevel_value_name => l_total_dimlevel_value_name
189 );
190
191 l_parameters(l_j).parameter_name := l_dimensionX_short_name||'+'||l_total_dimlevel_short_name;
192 l_parameters(l_j).parameter_id := NULL; -- All
193 l_parameters(l_j).parameter_value := NULL; -- All
194 ELSE
195 -- A dimension level was specified for this dimension
196 l_dimension_level := p_dimension_levels(l_dim_level_index);
197 l_parameters(l_j).parameter_name := l_dimensionX_short_name||'+'||l_dimension_level.level_short_name;
198 l_parameters(l_j).parameter_id := l_dimension_level.level_value_id;
199 l_parameters(l_j).parameter_value := l_dimension_level.level_value_name;
200 END IF;
201
202 --dbms_output.put_line('*l_parameters('||l_j||').parameter_name='||l_parameters(l_j).parameter_name);
203 --dbms_output.put_line('*l_parameters('||l_j||').parameter_id='||l_parameters(l_j).parameter_id);
204 --dbms_output.put_line('*l_parameters('||l_j||').parameter_value='||l_parameters(l_j).parameter_value);
205
206 l_j := l_j + 1;
207 END IF;
208 END LOOP;
209
210 IF p_time_comparison_type IS NULL THEN
211 -- No need to retrieve compareto_value
212 l_compareto_attribute_code := NULL;
213 ELSE
214 -- Need to retrieve compareto_value, for that reason it passes a new parameter to PMV
215 l_parameters(l_j).parameter_name := p_time_comparison_type;
216 l_parameters(l_j).parameter_id := p_time_comparison_type;
217 l_parameters(l_j).parameter_value := p_time_comparison_type;
218
219 --dbms_output.put_line('*l_parameters('||l_j||').parameter_name='||l_parameters(l_j).parameter_name);
220 --dbms_output.put_line('*l_parameters('||l_j||').parameter_id='||l_parameters(l_j).parameter_id);
221 --dbms_output.put_line('*l_parameters('||l_j||').parameter_value='||l_parameters(l_j).parameter_value);
222
223 l_j := l_j + 1;
224 END IF;
225
226 --dbms_output.put_line(substr('*l_actual_attribute_code='||l_actual_attribute_code,1,255));
227 --dbms_output.put_line(substr('*l_compareto_attribute_code='||l_compareto_attribute_code,1,255));
228 --dbms_output.put_line(substr('*p_viewby_level='||p_viewby_level,1,255));
229
230 -- Call the PMV API to get the actual, pass pageID as Null
231 bis_pmv_actual_pub.get_actual_value(
232 p_region_code => l_region_code
233 ,p_function_name => l_function_name
234 ,p_user_id => p_user_id
235 ,p_responsibility_id => p_responsibility_id
236 ,p_time_parameter => l_time_parameter
237 ,p_parameters => l_parameters
238 ,p_param_ids => 'N'
239 ,p_actual_attribute_code => l_actual_attribute_code
240 ,p_compareto_attribute_code => l_compareto_attribute_code
241 ,p_ranking_level => p_viewby_level
242 ,x_actual_value => l_act_tbl
243 ,x_return_status => x_return_status
244 ,x_msg_count => x_msg_count
245 ,x_msg_data => x_msg_data
246 );
247
248 IF l_act_tbl.count > 0 THEN
249 l_actual_value := l_act_tbl(1).actual_grandtotal_value;
250 l_compareto_value := l_act_tbl(1).compareto_grandtotal_value;
251 END IF;
252
253 FOR i in 1..l_act_tbl.count LOOP
254 IF l_sum_actual_value IS NULL AND l_act_tbl(i).actual_value IS NOT NULL THEN
255 l_sum_actual_value := l_act_tbl(i).actual_value;
256 ELSIF l_act_tbl(i).actual_value IS NOT NULL THEN
257 l_sum_actual_value := l_sum_actual_value + l_act_tbl(i).actual_value;
258 END IF;
259
260 IF l_sum_compareto_value IS NULL AND l_act_tbl(i).compare_to_value IS NOT NULL THEN
261 l_sum_compareto_value := l_act_tbl(i).compare_to_value;
262 ELSIF l_act_tbl(i).compare_to_value IS NOT NULL THEN
263 l_sum_compareto_value := l_sum_compareto_value + l_act_tbl(i).compare_to_value;
264 END IF;
265 END LOOP;
266
267 IF l_actual_value IS NULL THEN
268 l_actual_value := l_sum_actual_value;
269 END IF;
270
271 IF l_compareto_value IS NULL THEN
272 l_compareto_value := l_sum_compareto_value;
273 END IF;
274
275 x_actual_value := l_actual_value;
276 x_compareto_value := l_compareto_value;
277
278 --dbms_output.put_line('End BSC_BIS_WRAPPER_PVT.Get_Actual_Value_From_PMV' );
279
280 EXCEPTION
281 WHEN FND_API.G_EXC_ERROR THEN
282 x_return_status := FND_API.G_RET_STS_ERROR;
283 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
284 ,p_data => x_msg_data);
285 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
286 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
287 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
288 ,p_data => x_msg_data);
289 WHEN NO_DATA_FOUND THEN
290 x_return_status := FND_API.G_RET_STS_ERROR;
291 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
292 ,p_data => x_msg_data);
293 WHEN OTHERS THEN
294 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
295 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
296 ,p_data => x_msg_data);
297 --dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
298 END Get_Actual_Value_From_PMV;
299
300 /************************************************************************************
301 ***********************************************************************************/
302
303 PROCEDURE Get_AO_Defaults(
304 p_kpi_code IN NUMBER,
305 x_analysis_option0 OUT NOCOPY NUMBER,
306 x_analysis_option1 OUT NOCOPY NUMBER,
307 x_analysis_option2 OUT NOCOPY NUMBER,
308 x_series_id OUT NOCOPY NUMBER
309 ) IS
310
311 h_num_ag NUMBER;
312
313 BEGIN
314 -- Initialzie Variables
315 x_analysis_option0 := 0;
316 x_analysis_option1 := 0;
317 x_analysis_option2 := 0;
318 x_series_id := 0;
319
320 -- Get Analysis Option Defaults
321 SELECT DISTINCT DF.A0_DEFAULT,DF.A1_DEFAULT,DF.A2_DEFAULT,MS.SERIES_ID
322 INTO x_analysis_option0,x_analysis_option1,x_analysis_option2,x_series_id
323 FROM BSC_DB_COLOR_AO_DEFAULTS_V DF,
324 BSC_KPI_ANALYSIS_MEASURES_B MS
325 WHERE
326 DEFAULT_VALUE =1 AND
327 DF.INDICATOR = MS.INDICATOR AND
328 DF.A0_DEFAULT = MS.ANALYSIS_OPTION0 AND
329 DF.A1_DEFAULT = MS.ANALYSIS_OPTION1 AND
330 DF.A2_DEFAULT = MS.ANALYSIS_OPTION2 AND
331 DF.INDICATOR =p_kpi_code;
332
333 -- Print out NOCOPY
334 --dbms_output.put_line('p_kpi_code=' || p_kpi_code);
335 --dbms_output.put_line('x_analysis_option0=' || x_analysis_option0);
336 --dbms_output.put_line('x_analysis_option1=' || x_analysis_option1);
337 --dbms_output.put_line('x_analysis_option2=' || x_analysis_option2);
338 --dbms_output.put_line('x_series_id=' || x_series_id);
339
340 END Get_AO_Defaults;
341
342 /************************************************************************************
343 ************************************************************************************/
344
345 FUNCTION Get_Current_Period(
346 p_kpi_code IN NUMBER,
347 p_analysis_option0 IN NUMBER,
348 p_analysis_option1 IN NUMBER,
349 p_analysis_option2 IN NUMBER,
350 p_series_id IN NUMBER
351 ) RETURN VARCHAR2 IS
352
353 -- parameters to get current period info
354 l_dim_source VARCHAR2(2000);
355 l_org_dim_level_short_name VARCHAR2(2000);
356 l_org_dim_level_value_id VARCHAR2(2000);
357 l_current_period_id VARCHAR2(2000);
358 l_current_period_name VARCHAR2(2000);
359
360 l_dimset_id NUMBER;
361 l_time_dimension_level BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Type;
362
363 l_return_status VARCHAR2(32000);
364 l_msg_count VARCHAR2(32000);
365 l_msg_data VARCHAR2(32000);
366
367 BEGIN
368 l_current_period_name := NULL;
369
370 -- Get the dimension set
371 Get_DimensionSet_Id(
372 p_kpi_code => p_kpi_code,
373 p_analysis_option0 => p_analysis_option0,
374 p_analysis_option1 => p_analysis_option1,
375 p_analysis_option2 => p_analysis_option2,
376 p_series_id => p_series_id,
377 x_dimset_id => l_dimset_id
378 );
379
380 --dbms_output.put_line('p_kpi_code=' || p_kpi_code);
381 --dbms_output.put_line('Get_Current_Period x_analysis_option0=' || p_analysis_option0);
382 --dbms_output.put_line('Get_Current_Period x_analysis_option1=' || p_analysis_option1);
383 --dbms_output.put_line('Get_Current_Period x_analysis_option2=' || p_analysis_option2);
384 --dbms_output.put_line('Get_Current_Period x_dimset_id = ' || l_dimset_id);
385
386
387 Get_Default_Time_Level(
388 p_kpi_code => p_kpi_code,
389 p_dimset_id => l_dimset_id,
390 x_time_dimension_level => l_time_dimension_level
391 );
392
393 --dbms_output.put_line('p_kpi_code=' || p_kpi_code);
394 --dbms_output.put_line('Get_Default_Time_Level=' || l_time_dimension_level.dimension_short_name);
395
396
397 -- For now if Time dimension exist the color is calculated for the
398 -- period corresponding to SYSDATE
399
400 IF l_time_dimension_level.dimension_short_name IS NOT NULL THEN
401 IF l_time_dimension_level.dimension_short_name = 'TIME' THEN
402 -- OLTP
403 l_dim_source := 'OLTP';
404 l_org_dim_level_short_name := 'TOTAL_ORGANIZATIONS';
405 l_org_dim_level_value_id := '-1';
406 ELSE
407 -- EDW
408 l_dim_source := 'EDW';
409 l_org_dim_level_short_name := NULL;
410 l_org_dim_level_value_id := NULL;
411 END IF;
412
413 --dbms_output.put_line('l_dim_source=' || l_dim_source);
414 --dbms_output.put_line('l_org_dim_level_short_name=' || l_org_dim_level_short_name);
415 --dbms_output.put_line('l_org_dim_level_value_id=' || l_org_dim_level_value_id);
416
417
418 Get_Period_Info(
419 p_time_dim_level_short_name => l_time_dimension_level.level_short_name,
420 p_source => l_dim_source,
421 p_org_dim_level_short_name => l_org_dim_level_short_name,
422 p_org_dim_level_value_id => l_org_dim_level_value_id,
423 p_period_date => SYSDATE, --TO_DATE('04-15-2001','MM-DD-YYYY'),
424 x_period_id => l_current_period_id,
425 x_period_name => l_current_period_name,
426 x_return_status => l_return_status,
427 x_msg_count => l_msg_count,
428 x_msg_data => l_msg_data
429 );
430 END IF;
431 --if(l_current_period_name is null) then
432 --dbms_output.put_line('l_current_period_name is NULL= '|| l_current_period_name);
433 --else
434 --dbms_output.put_line('l_current_period_name = '|| l_current_period_name);
435 --end if;
436 RETURN l_current_period_name;
437
438 EXCEPTION
439 WHEN OTHERS THEN
440 RETURN l_current_period_name;
441
442 END Get_Current_Period;
443
444 /************************************************************************************
445 ************************************************************************************/
446
447 FUNCTION Get_DBI_Current_Period(
448 p_time_dimension_level IN BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Type,
449 p_as_of_date IN VARCHAR2
450
451 ) RETURN VARCHAR2 IS
452
453 -- parameters to get current period info
454 l_dim_source VARCHAR2(2000);
455 l_org_dim_level_short_name VARCHAR2(2000);
456 l_org_dim_level_value_id VARCHAR2(2000);
457 l_current_period_id VARCHAR2(2000);
458 l_current_period_name VARCHAR2(2000);
459 l_as_of_date_format VARCHAR2(30) := 'DD-MON-RRRR';
460
461 l_return_status VARCHAR2(32000);
462 l_msg_count VARCHAR2(32000);
463 l_msg_data VARCHAR2(32000);
464
465 BEGIN
466 l_current_period_name := NULL;
467
468 IF p_time_dimension_level.dimension_short_name IS NOT NULL THEN
469 IF p_time_dimension_level.dimension_short_name = 'TIME' THEN
470 -- OLTP
471 l_dim_source := 'OLTP';
472 l_org_dim_level_short_name := 'TOTAL_ORGANIZATIONS';
473 l_org_dim_level_value_id := '-1';
474 ELSE
475 -- EDW
476 l_dim_source := 'EDW';
477 l_org_dim_level_short_name := NULL;
478 l_org_dim_level_value_id := NULL;
479 END IF;
480
481 --dbms_output.put_line('l_dim_source=' || l_dim_source);
482 --dbms_output.put_line('l_org_dim_level_short_name=' || l_org_dim_level_short_name);
483 --dbms_output.put_line('l_org_dim_level_value_id=' || l_org_dim_level_value_id);
484
485
486 Get_Period_Info(
487 p_time_dim_level_short_name => p_time_dimension_level.level_short_name,
488 p_source => l_dim_source,
489 p_org_dim_level_short_name => l_org_dim_level_short_name,
490 p_org_dim_level_value_id => l_org_dim_level_value_id,
491 p_period_date => TO_DATE(p_as_of_date,l_as_of_date_format),
492 x_period_id => l_current_period_id,
493 x_period_name => l_current_period_name,
494 x_return_status => l_return_status,
495 x_msg_count => l_msg_count,
496 x_msg_data => l_msg_data
497 );
498 END IF;
499 --if(l_current_period_name is null) then
500 --dbms_output.put_line('l_current_period_name is NULL= '|| l_current_period_name);
501 --else
502 --dbms_output.put_line('l_current_period_name = '|| l_current_period_name);
503 --end if;
504 RETURN l_current_period_name;
505
506 EXCEPTION
507 WHEN OTHERS THEN
508 RETURN l_current_period_name;
509
510 END Get_DBI_Current_Period;
511
512 PROCEDURE Get_DataSet_Id (
513 p_kpi_code IN NUMBER,
514 p_analysis_option0 IN NUMBER,
515 p_analysis_option1 IN NUMBER,
516 p_analysis_option2 IN NUMBER,
517 p_series_id IN NUMBER,
518 x_dataset_id OUT NOCOPY NUMBER
519 ) IS
520 BEGIN
521
522 SELECT
523 dataset_id
524 INTO
525 x_dataset_id
526 FROM
527 bsc_db_dataset_dim_sets_v
528 WHERE
529 indicator = p_kpi_code AND
530 A0 = p_analysis_option0 AND
531 A1 = p_analysis_option1 AND
532 A2 = p_analysis_option2 AND
533 series_id = p_series_id;
534
535 END Get_DataSet_Id;
536
537 /************************************************************************************
538 -- bug 2677766- Add x_format_id
539 ************************************************************************************/
540
541 PROCEDURE Get_DataSet_Info (
542 p_dataset_id IN NUMBER,
543 x_source OUT NOCOPY VARCHAR2,
544 x_measure_id1 OUT NOCOPY NUMBER,
545 x_operation OUT NOCOPY VARCHAR2,
546 x_measure_id2 OUT NOCOPY NUMBER,
547 x_color_method OUT NOCOPY NUMBER,
548 x_measure_col1 OUT NOCOPY VARCHAR2,
549 x_measure_operation1 OUT NOCOPY VARCHAR2,
550 x_measure_short_name OUT NOCOPY VARCHAR2,
551 x_measure_col2 OUT NOCOPY VARCHAR2,
552 x_measure_operation2 OUT NOCOPY VARCHAR2,
553 x_format_id OUT NOCOPY NUMBER
554 ) IS
555 BEGIN
556
557 SELECT
558 d.source,
559 d.measure_id1,
560 d.operation,
561 d.measure_id2,
562 d.color_method,
563 m1.measure_col,
564 m1.operation,
565 m1.short_name,
566 m2.measure_col,
567 m2.operation,
568 d.format_id
569 INTO
570 x_source,
571 x_measure_id1,
572 x_operation,
573 x_measure_id2,
574 x_color_method,
575 x_measure_col1,
576 x_measure_operation1,
577 x_measure_short_name,
578 x_measure_col2,
579 x_measure_operation2,
580 x_format_id
581 FROM
582 bsc_sys_datasets_b d,
583 bsc_sys_measures m1,
584 bsc_sys_measures m2
585 WHERE
586 d.dataset_id = p_dataset_id AND
587 d.measure_id1 = m1.measure_id(+) AND
588 d.measure_id2 = m2.measure_id(+);
589
590 END Get_DataSet_Info;
591
592 /************************************************************************************
593 ************************************************************************************/
594
595 FUNCTION Get_Dimension_Short_Name(
596 p_dimension_id IN NUMBER
597 ) RETURN VARCHAR2 IS
598
599 l_sql VARCHAR2(2000);
600 l_short_name VARCHAR2(2000) := NULL;
601
602 BEGIN
603
604 IF p_dimension_id IS NOT NULL THEN
605 SELECT short_name INTO l_short_name
606 FROM bis_dimensions
607 WHERE dimension_id = p_dimension_id;
608 END IF;
609
610 RETURN l_short_name;
611
612 END Get_Dimension_Short_Name;
613
614 /************************************************************************************
615 ************************************************************************************/
616
617 PROCEDURE Get_DimensionSet_Id (
618 p_kpi_code IN NUMBER,
619 p_analysis_option0 IN NUMBER,
620 p_analysis_option1 IN NUMBER,
621 p_analysis_option2 IN NUMBER,
622 p_series_id IN NUMBER,
623 x_dimset_id OUT NOCOPY NUMBER
624 ) IS
625 BEGIN
626
627 SELECT
628 dim_set_id
629 INTO
630 x_dimset_id
631 FROM
632 bsc_db_dataset_dim_sets_v
633 WHERE
634 indicator = p_kpi_code AND
635 A0 = p_analysis_option0 AND
636 A1 = p_analysis_option1 AND
637 A2 = p_analysis_option2 AND
638 series_id = p_series_id;
639
640 END Get_DimensionSet_Id;
641
642 /************************************************************************************
643 ************************************************************************************/
644
645 PROCEDURE Get_Period_Info(
646 p_time_dim_level_short_name IN VARCHAR2,
647 p_source IN VARCHAR2,
648 p_org_dim_level_short_name IN VARCHAR2,
649 p_org_dim_level_value_id IN VARCHAR2,
650 p_period_date IN DATE DEFAULT SYSDATE,
651 x_period_id OUT NOCOPY VARCHAR2,
652 x_period_name OUT NOCOPY VARCHAR2,
653 x_return_status OUT NOCOPY VARCHAR2,
654 x_msg_count OUT NOCOPY NUMBER,
655 x_msg_data OUT NOCOPY VARCHAR2
656 ) IS
657
658 l_select_string VARCHAR2(32000);
659 l_view_name VARCHAR2(2000);
660 l_id_name VARCHAR2(2000);
661 l_value_name VARCHAR2(2000);
662
663 l_total_time_level_name VARCHAR2(2000);
664 l_sql VARCHAR2(32000);
665
666 l_curr_date VARCHAR2(2000);
667 l_start_date DATE;
668 l_end_date DATE;
669 l_time_lvl_dep_on_org NUMBER(3);
670 l_is_dep_on_org BOOLEAN := FALSE;
671
672 TYPE tcursor IS REF CURSOR;
673 l_cursor tcursor;
674
675 BEGIN
676 FND_MSG_PUB.initialize;
677
678 l_curr_date := 'TO_DATE('''||TO_CHAR(p_period_date,'DD-MON-YYYY HH24:MI:SS')||''',''DD-MON-YYYY HH24:MI:SS'')';
679
680 x_period_id := NULL;
681 x_period_name := NULL;
682
683 BIS_PMF_GET_DIMLEVELS_PUB.GET_DIMLEVEL_SELECT_STRING (
684 p_DimLevelShortName => p_time_dim_level_short_name
685 ,p_bis_source => p_source
686 ,x_Select_String => l_select_string
687 ,x_table_name => l_view_name
688 ,x_id_name => l_id_name
689 ,x_value_name => l_value_name
690 ,x_return_status => x_return_status
691 ,x_msg_count => x_msg_count
692 ,x_msg_data => x_msg_data
693 );
694
695 --dbms_output.put_line('l_select_string' || l_select_string);
696 --dbms_output.put_line('l_view_name' || l_view_name);
697 --dbms_output.put_line('l_id_name' || l_id_name);
698 --dbms_output.put_line('x_return_status' || x_return_status);
699 --dbms_output.put_line('x_msg_count' || x_msg_count);
700 --dbms_output.put_line('x_msg_data' || x_msg_data);
701
702 IF x_msg_count > 0 THEN
703 RETURN;
704 END IF;
705
706 IF p_source = 'EDW' THEN
707 l_total_time_level_name := 'EDW_TIME_A';
708 ELSE
709 l_total_time_level_name := 'TOTAL_TIME';
710 END IF;
711
712 -- For total time level there is only one record in the dimension view
713 -- which is the one we want.
714
715 IF (p_time_dim_level_short_name <> l_total_time_level_name) THEN
716
717 l_sql := 'SELECT DISTINCT '||l_id_name||', '||l_value_name||', start_date, end_date'||
718 ' FROM '||l_view_name;
719
720 -- No total time level
721 -- In this case we compare l_curr_date with start_date and end_date
722 -- to get the current period
723
724 l_sql := l_sql||
725 ' WHERE TRUNC('||l_curr_date||') BETWEEN '||
726 ' NVL(start_date, TRUNC('||l_curr_date||')) AND NVL(end_date, TRUNC('||l_curr_date||'))';
727
728 IF p_source = 'OLTP' THEN --AND SUBSTR(p_time_dim_level_short_name, 1, 2) <> 'HR'
729 --fix bug # 2372091, PMF API changes, BISVUTLB.pls
730 -- For OLTP we need an additional condition on organization id
731
732 l_time_lvl_dep_on_org := BIS_UTILITIES_PUB.is_time_dependent_on_org(p_time_lvl_short_name => p_time_dim_level_short_name);
733
734 IF (p_time_dim_level_short_name IS NOT NULL AND l_time_lvl_dep_on_org = 1) THEN
735 l_is_dep_on_org := TRUE;
736 END IF;
737
738 IF (l_is_dep_on_org) THEN --fix bug # 2372091
739 l_sql := l_sql||
740 ' AND ORGANIZATION_ID = '''||p_org_dim_level_value_id||''''||
741 ' AND NVL(ORGANIZATION_TYPE, ''%'') LIKE '''||p_org_dim_level_short_name||'''';
742 END IF;
743
744
745 ELSE
746 -- Bug 1797680
747 IF p_source = 'EDW' THEN
748 -- In this case we need to filter out NOCOPY codes 0 and -1 which are special codes in EDW dimension tables
749 l_sql := l_sql||
750 ' AND '||l_id_name||' NOT IN (''-1'', ''0'')';
751 END IF;
752 END IF;
753 l_sql := l_sql||
754 ' ORDER BY ABS(NVL(TRUNC(end_date), TRUNC('||l_curr_date||'))- NVL(TRUNC(start_date), TRUNC('||l_curr_date||')))';
755
756 ELSE
757 l_sql := 'SELECT DISTINCT '||l_id_name||', '||l_value_name||', '||l_curr_date||' AS start_date, '||l_curr_date||' AS end_date'||
758 ' FROM '||l_view_name;
759
760 END IF;
761
762 --dbms_output.put_line(substr('Value of l_sql='||l_sql,1,255));
763
764 -- Query is supposed to return just one record. However we take the first one.
765 OPEN l_cursor FOR l_sql;
766 -- Bug#2372091 FETCH l_cursor INTO x_period_id, x_period_name;
767 FETCH l_cursor INTO x_period_id, x_period_name,l_start_date,l_end_date;
768 CLOSE l_cursor;
769
770 EXCEPTION
771 WHEN FND_API.G_EXC_ERROR THEN
772 x_return_status := FND_API.G_RET_STS_ERROR;
773 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
774 ,p_data => x_msg_data);
775 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
776 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
777 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
778 ,p_data => x_msg_data);
779 WHEN NO_DATA_FOUND THEN
780 x_return_status := FND_API.G_RET_STS_ERROR;
781 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
782 ,p_data => x_msg_data);
783 WHEN OTHERS THEN
784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
785 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
786 ,p_data => x_msg_data);
787 END Get_Period_Info;
788
789 /************************************************************************************
790 ************************************************************************************/
791
792 PROCEDURE Get_Kpi_Info(
793 p_kpi_code IN NUMBER,
794 p_analysis_option0 IN NUMBER,
795 p_analysis_option1 IN NUMBER,
796 p_analysis_option2 IN NUMBER,
797 p_series_id IN NUMBER,
798 x_kpi_info_rec OUT NOCOPY BSC_BIS_WRAPPER_PUB.Kpi_Info_Rec_Type
799 ) IS
800
801 l_kpi_info_rec BSC_BIS_WRAPPER_PUB.Kpi_Info_Rec_Type;
802
803 l_measure_id1 NUMBER;
804 l_operation VARCHAR2(2000);
805 l_measure_id2 NUMBER;
806 l_color_method NUMBER;
807 l_measure_col1 VARCHAR2(2000);
808 l_measure_operation1 VARCHAR2(2000);
809 l_measure_col2 VARCHAR2(2000);
810 l_measure_operation2 VARCHAR2(2000);
811
812 l_return_status VARCHAR2(2000);
813 l_msg_count NUMBER;
814 l_msg_data VARCHAR2(32000);
815
816 BEGIN
817
818 l_kpi_info_rec.kpi_code := p_kpi_code;
819 l_kpi_info_rec.analysis_option0 := p_analysis_option0;
820 l_kpi_info_rec.analysis_option1 := p_analysis_option1;
821 l_kpi_info_rec.analysis_option2 := p_analysis_option2;
822 l_kpi_info_rec.series_id := p_series_id;
823
824 -- Get dataset id
825 Get_DataSet_Id(
826 p_kpi_code => l_kpi_info_rec.kpi_code,
827 p_analysis_option0 => l_kpi_info_rec.analysis_option0,
828 p_analysis_option1 => l_kpi_info_rec.analysis_option1,
829 p_analysis_option2 => l_kpi_info_rec.analysis_option2,
830 p_series_id => l_kpi_info_rec.series_id,
831 x_dataset_id => l_kpi_info_rec.dataset_id
832 );
833
834 --dbms_output.put_line('*l_kpi_info_rec.dataset_id='||l_kpi_info_rec.dataset_id);
835
836 -- Get dataset info
837 Get_DataSet_Info (
838 p_dataset_id => l_kpi_info_rec.dataset_id,
839 x_source => l_kpi_info_rec.dataset_source,
840 x_measure_id1 => l_measure_id1,
841 x_operation => l_operation,
842 x_measure_id2 => l_measure_id2,
843 x_color_method => l_color_method,
844 x_measure_col1 => l_measure_col1,
845 x_measure_operation1 => l_measure_operation1,
846 x_measure_short_name => l_kpi_info_rec.measure_short_name,
847 x_measure_col2 => l_measure_col2,
848 x_measure_operation2 => l_measure_operation2,
849 x_format_id => l_kpi_info_rec.format_id
850 );
851
852 --dbms_output.put_line('*l_kpi_info_rec.dataset_source='||l_kpi_info_rec.dataset_source);
853 --dbms_output.put_line('*l_kpi_info_rec.measure_short_name='||l_kpi_info_rec.measure_short_name);
854 --dbms_output.put_line('*l_kpi_info_rec.format_id='||l_kpi_info_rec.format_id);
855
856 IF l_kpi_info_rec.dataset_source = 'PMF' THEN
857 IF is_measure_dbi(l_kpi_info_rec.measure_short_name) THEN
858 l_kpi_info_rec.measure_dbi_flag := 'Y';
859 ELSE
860 l_kpi_info_rec.measure_dbi_flag := 'F';
861 END IF;
862
863 --dbms_output.put_line('*l_kpi_info_rec.measure_dbi_flag='||l_kpi_info_rec.measure_dbi_flag);
864
865 -- Get pmf measure info
866 Get_Pmf_Measure_Info(
867 p_Measure_ShortName => l_kpi_info_rec.measure_short_name,
868 x_measure_id => l_kpi_info_rec.measure_id,
869 x_function_name => l_kpi_info_rec.function_name,
870 x_region_code => l_kpi_info_rec.region_code,
871 x_attribute_code => l_kpi_info_rec.actual_attribute_code,
872 x_compareto_attribute_code => l_kpi_info_rec.compareto_attribute_code,
873 x_dimension1_short_name => l_kpi_info_rec.dim1_short_name,
874 x_dimension2_short_name => l_kpi_info_rec.dim2_short_name,
875 x_dimension3_short_name => l_kpi_info_rec.dim3_short_name,
876 x_dimension4_short_name => l_kpi_info_rec.dim4_short_name,
877 x_dimension5_short_name => l_kpi_info_rec.dim5_short_name,
878 x_dimension6_short_name => l_kpi_info_rec.dim6_short_name,
879 x_dimension7_short_name => l_kpi_info_rec.dim7_short_name
880 );
881
882 --dbms_output.put_line('*l_kpi_info_rec.function_name='||l_kpi_info_rec.function_name);
883 --dbms_output.put_line('*l_kpi_info_rec.region_code='||l_kpi_info_rec.region_code);
884 --dbms_output.put_line('*l_kpi_info_rec.actual_attribute_code='||l_kpi_info_rec.actual_attribute_code);
885 --dbms_output.put_line('*l_kpi_info_rec.compareto_attribute_code='||l_kpi_info_rec.compareto_attribute_code);
886 --dbms_output.put_line('*l_kpi_info_rec.dimension1_short_name='||l_kpi_info_rec.dimension1_short_name);
887 --dbms_output.put_line('*l_kpi_info_rec.dimension2_short_name='||l_kpi_info_rec.dimension2_short_name);
888 --dbms_output.put_line('*l_kpi_info_rec.dimension3_short_name='||l_kpi_info_rec.dimension3_short_name);
889 --dbms_output.put_line('*l_kpi_info_rec.dimension4_short_name='||l_kpi_info_rec.dimension4_short_name);
890 --dbms_output.put_line('*l_kpi_info_rec.dimension5_short_name='||l_kpi_info_rec.dimension5_short_name);
891 --dbms_output.put_line('*l_kpi_info_rec.dimension6_short_name='||l_kpi_info_rec.dimension6_short_name);
892 --dbms_output.put_line('*l_kpi_info_rec.dimension7_short_name='||l_kpi_info_rec.dimension7_short_name);
893
894 -- Get the dimension set
895 Get_DimensionSet_Id(
896 p_kpi_code => l_kpi_info_rec.kpi_code,
897 p_analysis_option0 => l_kpi_info_rec.analysis_option0,
898 p_analysis_option1 => l_kpi_info_rec.analysis_option1,
899 p_analysis_option2 => l_kpi_info_rec.analysis_option2,
900 p_series_id => l_kpi_info_rec.series_id,
901 x_dimset_id => l_kpi_info_rec.dimset_id
902 );
903
904 --dbms_output.put_line('*l_kpi_info_rec.dimset_id='||l_kpi_info_rec.dimset_id);
905 END IF;
906
907 x_kpi_info_rec := l_kpi_info_rec;
908
909 END Get_Kpi_Info;
910
911 /************************************************************************************
912 ************************************************************************************/
913
914 PROCEDURE Get_Pmf_Measure_Info (
915 p_Measure_ShortName IN VARCHAR2,
916 x_measure_id OUT NOCOPY NUMBER,
917 x_function_name OUT NOCOPY VARCHAR2,
918 x_region_code OUT NOCOPY VARCHAR2,
919 x_attribute_code OUT NOCOPY VARCHAR2,
920 x_compareto_attribute_code OUT NOCOPY VARCHAR2,
921 x_dimension1_short_name OUT NOCOPY VARCHAR2,
922 x_dimension2_short_name OUT NOCOPY VARCHAR2,
923 x_dimension3_short_name OUT NOCOPY VARCHAR2,
924 x_dimension4_short_name OUT NOCOPY VARCHAR2,
925 x_dimension5_short_name OUT NOCOPY VARCHAR2,
926 x_dimension6_short_name OUT NOCOPY VARCHAR2,
927 x_dimension7_short_name OUT NOCOPY VARCHAR2
928 ) IS
929
930 l_return_status VARCHAR2(32000);
931 l_msg_count VARCHAR2(32000);
932 l_msg_data VARCHAR2(32000);
933 l_Measure_Short_Name VARCHAR2(30);
934 l_Measure_Name bsc_sys_datasets_tl.name%TYPE;
935 l_Description bsc_sys_datasets_tl.help%TYPE;
936 l_Dimension1_ID NUMBER;
937 l_Dimension2_ID NUMBER;
938 l_Dimension3_ID NUMBER;
939 l_Dimension4_ID NUMBER;
940 l_Dimension5_ID NUMBER;
941 l_Dimension6_ID NUMBER;
942 l_Dimension7_ID NUMBER;
943 l_Unit_Of_Measure_Class VARCHAR2(10);
944 l_actual_data_source_type VARCHAR2(30);
945 l_actual_data_source VARCHAR2(240);
946 l_function_name VARCHAR2(240);
947 l_comparison_source VARCHAR2(240);
948 l_increase_in_measure VARCHAR2(1);
949
950 l_index NUMBER;
951 l_region_code VARCHAR2(240);
952
953 BEGIN
954
955 BIS_PMF_DEFINER_WRAPPER_PVT.Retrieve_Performance_Measure(
956 p_Measure_Short_Name => p_Measure_ShortName
957 ,x_return_status => l_return_status
958 ,x_msg_count => l_msg_count
959 ,x_msg_data => l_msg_data
960 ,x_Measure_ID => x_measure_id
961 ,x_Measure_Short_Name => l_Measure_Short_Name
962 ,x_Measure_Name => l_Measure_Name
963 ,x_Description => l_Description
964 ,x_Dimension1_ID => l_Dimension1_ID
965 ,x_Dimension2_ID => l_Dimension2_ID
966 ,x_Dimension3_ID => l_Dimension3_ID
967 ,x_Dimension4_ID => l_Dimension4_ID
968 ,x_Dimension5_ID => l_Dimension5_ID
969 ,x_Dimension6_ID => l_Dimension6_ID
970 ,x_Dimension7_ID => l_Dimension7_ID
971 ,x_Unit_Of_Measure_Class => l_Unit_Of_Measure_Class
972 ,x_actual_data_source_type => l_actual_data_source_type
973 ,x_actual_data_source => l_actual_data_source
974 ,x_region_code => x_region_code
975 ,x_attribute_code => x_attribute_code
976 ,x_function_name => x_function_name
977 ,x_comparison_source => l_comparison_source
978 ,x_increase_in_measure => l_increase_in_measure
979 );
980
981 x_compareto_attribute_code := SUBSTR(l_comparison_source,(INSTR(l_comparison_source,'.',1,1)+1));
982
983 x_dimension1_short_name := Get_Dimension_Short_Name(l_Dimension1_ID);
984 x_dimension2_short_name := Get_Dimension_Short_Name(l_Dimension2_ID);
985 x_dimension3_short_name := Get_Dimension_Short_Name(l_Dimension3_ID);
986 x_dimension4_short_name := Get_Dimension_Short_Name(l_Dimension4_ID);
987 x_dimension5_short_name := Get_Dimension_Short_Name(l_Dimension5_ID);
988 x_dimension6_short_name := Get_Dimension_Short_Name(l_Dimension6_ID);
989 x_dimension7_short_name := Get_Dimension_Short_Name(l_Dimension7_ID);
990
991 END Get_Pmf_Measure_Info;
992
993 /************************************************************************************
994 ************************************************************************************/
995
996 FUNCTION Get_Target_Value(
997 p_kpi_code IN NUMBER,
998 p_analysis_option0 IN NUMBER,
999 p_analysis_option1 IN NUMBER,
1000 p_analysis_option2 IN NUMBER,
1001 p_series_id IN NUMBER,
1002 p_user_id IN VARCHAR2,
1003 p_responsibility_id IN VARCHAR2
1004 ) RETURN VARCHAR2 IS
1005
1006 l_value VARCHAR2(2000);
1007
1008 BEGIN
1009 l_value := NULL;
1010
1011 SELECT budget_data INTO l_value
1012 FROM bsc_bis_measures_data
1013 WHERE user_id = p_user_id AND
1014 responsibility_id = p_responsibility_id AND
1015 indicator = p_kpi_code AND
1016 analysis_option0 = p_analysis_option0 AND
1017 analysis_option1 = p_analysis_option1 AND
1018 analysis_option2 = p_analysis_option2 AND
1019 series_id = p_series_id;
1020
1021 RETURN l_value;
1022
1023 EXCEPTION
1024 WHEN OTHERS THEN
1025 RETURN l_value ;
1026
1027 END Get_Target_Value;
1028
1029 /************************************************************************************
1030 ************************************************************************************/
1031
1032 PROCEDURE Get_Target_Value_From_PMF(
1033 p_kpi_info_rec IN BSC_BIS_WRAPPER_PUB.Kpi_Info_Rec_Type,
1034 p_user_id IN VARCHAR2,
1035 p_responsibility_id IN VARCHAR2,
1036 p_dimension_levels IN BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Tbl_Type,
1037 p_time_level IN BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Type,
1038 x_target_value OUT NOCOPY VARCHAR2,
1039 x_return_status OUT NOCOPY VARCHAR2,
1040 x_msg_count OUT NOCOPY NUMBER,
1041 x_msg_data OUT NOCOPY VARCHAR2
1042 ) IS
1043 -- Measure Info
1044 l_measure_id NUMBER;
1045 l_measure_dimensions BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Tbl_Type;
1046
1047 l_region_code VARCHAR2(2000) := NULL;
1048 l_function_name VARCHAR2(2000) := NULL;
1049 l_actual_attribute_code VARCHAR2(2000) := NULL;
1050 l_compareto_attribute_code VARCHAR2(2000) := NULL;
1051
1052 -- Parameter to get target from pmf
1053 l_plan_id NUMBER;
1054 l_target_level_rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
1055 l_target_rec BIS_TARGET_PUB.TARGET_REC_TYPE;
1056 l_target_level_rec_x BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
1057 l_target_rec_x BIS_TARGET_PUB.TARGET_REC_TYPE;
1058 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1059
1060 -- Others
1061 l_i NUMBER;
1062 l_dimensionX_short_name VARCHAR2(2000);
1063 l_dimension_level BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Type;
1064 l_total_dimlevel_short_name VARCHAR2(2000);
1065 l_total_dimlevel_value_id NUMBER;
1066 l_total_dimlevel_value_name VARCHAR2(2000);
1067 l_dim_level_index NUMBER;
1068
1069 BEGIN
1070 FND_MSG_PUB.initialize;
1071
1072 x_target_value := NULL;
1073
1074 --dbms_output.put_line('Begin BSC_BIS_WRAPPER_PVT.Get_Target_Value_From_PMF' );
1075
1076 -- Get pmf measure info
1077 l_measure_id := p_kpi_info_rec.measure_id;
1078 l_function_name := p_kpi_info_rec.function_name;
1079 l_region_code := p_kpi_info_rec.region_code;
1080 l_actual_attribute_code := p_kpi_info_rec.actual_attribute_code;
1081 l_compareto_attribute_code := p_kpi_info_rec.compareto_attribute_code;
1082 l_measure_dimensions(1).dimension_short_name := p_kpi_info_rec.dim1_short_name;
1083 l_measure_dimensions(2).dimension_short_name := p_kpi_info_rec.dim2_short_name;
1084 l_measure_dimensions(3).dimension_short_name := p_kpi_info_rec.dim3_short_name;
1085 l_measure_dimensions(4).dimension_short_name := p_kpi_info_rec.dim4_short_name;
1086 l_measure_dimensions(5).dimension_short_name := p_kpi_info_rec.dim5_short_name;
1087 l_measure_dimensions(6).dimension_short_name := p_kpi_info_rec.dim6_short_name;
1088 l_measure_dimensions(7).dimension_short_name := p_kpi_info_rec.dim7_short_name;
1089
1090 --dbms_output.put_line('*l_function_name='||l_function_name);
1091 --dbms_output.put_line('*l_region_code='||l_region_code);
1092 --dbms_output.put_line('*l_actual_attribute_code='||l_actual_attribute_code);
1093 --dbms_output.put_line('*l_compareto_attribute_code='||l_compareto_attribute_code);
1094 --dbms_output.put_line('*l_measure_id='||l_measure_id);
1095 --dbms_output.put_line('*l_dimension1_short_name='||l_measure_dimensions(1).dimension_short_name);
1096 --dbms_output.put_line('*l_dimension2_short_name='||l_measure_dimensions(2).dimension_short_name);
1097 --dbms_output.put_line('*l_dimension3_short_name='||l_measure_dimensions(3).dimension_short_name);
1098 --dbms_output.put_line('*l_dimension4_short_name='||l_measure_dimensions(4).dimension_short_name);
1099 --dbms_output.put_line('*l_dimension5_short_name='||l_measure_dimensions(5).dimension_short_name);
1100 --dbms_output.put_line('*l_dimension6_short_name='||l_measure_dimensions(6).dimension_short_name);
1101 --dbms_output.put_line('*l_dimension7_short_name='||l_measure_dimensions(7).dimension_short_name);
1102
1103 FOR l_i IN 1 .. 7 LOOP
1104 l_dimensionX_short_name := l_measure_dimensions(l_i).dimension_short_name;
1105 IF (l_dimensionX_short_name IS NOT NULL) THEN
1106 IF l_dimensionX_short_name = p_time_level.dimension_short_name THEN
1107 l_measure_dimensions(l_i).level_short_name := p_time_level.level_short_name;
1108 l_measure_dimensions(l_i).level_value_id := p_time_level.level_value_id;
1109 ELSE
1110 l_dim_level_index := Get_Dimension_Level_Index(l_dimensionX_short_name, p_dimension_levels);
1111 IF l_dim_level_index = 0 THEN
1112 -- A dimension level was not specified for this dimension
1113 -- Then use the total dimension level
1114 Get_Total_DimLevel_Info(
1115 p_dimension_short_name => l_dimensionX_short_name,
1116 x_total_dimlevel_short_name => l_total_dimlevel_short_name,
1117 x_total_dimlevel_value_id => l_total_dimlevel_value_id,
1118 x_total_dimlevel_value_name => l_total_dimlevel_value_name
1119 );
1120
1121 l_measure_dimensions(l_i).level_short_name := l_total_dimlevel_short_name;
1122 l_measure_dimensions(l_i).level_value_id := l_total_dimlevel_value_id;
1123
1124 ELSE
1125 l_dimension_level := p_dimension_levels(l_dim_level_index);
1126 IF l_dimension_level.level_value_id IS NULL THEN
1127 -- There is no an specific value for the dimension level
1128 -- Then use the total dimension level
1129 Get_Total_DimLevel_Info(
1130 p_dimension_short_name => l_dimensionX_short_name,
1131 x_total_dimlevel_short_name => l_total_dimlevel_short_name,
1132 x_total_dimlevel_value_id => l_total_dimlevel_value_id,
1133 x_total_dimlevel_value_name => l_total_dimlevel_value_name
1134 );
1135
1136 l_measure_dimensions(l_i).level_short_name := l_total_dimlevel_short_name;
1137 l_measure_dimensions(l_i).level_value_id := l_total_dimlevel_value_id;
1138 ELSE
1139 -- Exists an specific value for an specific level in the dimension
1140 l_measure_dimensions(l_i).level_short_name := l_dimension_level.level_short_name;
1141 l_measure_dimensions(l_i).level_value_id := l_dimension_level.level_value_id;
1142 END IF;
1143 END IF;
1144 END IF;
1145 END IF;
1146 END LOOP;
1147
1148 -- Set Target parameters:
1149 l_target_level_rec.dimension1_level_short_name := l_measure_dimensions(1).level_short_name;
1150 l_target_rec.dim1_level_value_id := l_measure_dimensions(1).level_value_id;
1151 l_target_level_rec.dimension2_level_short_name := l_measure_dimensions(2).level_short_name;
1152 l_target_rec.dim2_level_value_id := l_measure_dimensions(2).level_value_id;
1153 l_target_level_rec.dimension3_level_short_name := l_measure_dimensions(3).level_short_name;
1154 l_target_rec.dim3_level_value_id := l_measure_dimensions(3).level_value_id;
1155 l_target_level_rec.dimension4_level_short_name := l_measure_dimensions(4).level_short_name;
1156 l_target_rec.dim4_level_value_id := l_measure_dimensions(4).level_value_id;
1157 l_target_level_rec.dimension5_level_short_name := l_measure_dimensions(5).level_short_name;
1158 l_target_rec.dim5_level_value_id := l_measure_dimensions(5).level_value_id;
1159 l_target_level_rec.dimension6_level_short_name := l_measure_dimensions(6).level_short_name;
1160 l_target_rec.dim6_level_value_id := l_measure_dimensions(6).level_value_id;
1161 l_target_level_rec.dimension7_level_short_name := l_measure_dimensions(7).level_short_name;
1162 l_target_rec.dim7_level_value_id := l_measure_dimensions(7).level_value_id;
1163
1164 --dbms_output.put_line('*l_target_level_rec.dimension1_level_short_name='||l_target_level_rec.dimension1_level_short_name);
1165 --dbms_output.put_line('*l_target_rec.dim1_level_value_id='||l_target_rec.dim1_level_value_id);
1166 --dbms_output.put_line('*l_target_level_rec.dimension2_level_short_name='||l_target_level_rec.dimension2_level_short_name);
1167 --dbms_output.put_line('*l_target_rec.dim2_level_value_id='||l_target_rec.dim2_level_value_id);
1168 --dbms_output.put_line('*l_target_level_rec.dimension3_level_short_name='||l_target_level_rec.dimension3_level_short_name);
1169 --dbms_output.put_line('*l_target_rec.dim3_level_value_id='||l_target_rec.dim3_level_value_id);
1170 --dbms_output.put_line('*l_target_level_rec.dimension4_level_short_name='||l_target_level_rec.dimension4_level_short_name);
1171 --dbms_output.put_line('*l_target_rec.dim4_level_value_id='||l_target_rec.dim4_level_value_id);
1172 --dbms_output.put_line('*l_target_level_rec.dimension5_level_short_name='||l_target_level_rec.dimension5_level_short_name);
1173 --dbms_output.put_line('*l_target_rec.dim5_level_value_id='||l_target_rec.dim5_level_value_id);
1174 --dbms_output.put_line('*l_target_level_rec.dimension6_level_short_name='||l_target_level_rec.dimension6_level_short_name);
1175 --dbms_output.put_line('*l_target_rec.dim6_level_value_id='||l_target_rec.dim6_level_value_id);
1176 --dbms_output.put_line('*l_target_level_rec.dimension7_level_short_name='||l_target_level_rec.dimension7_level_short_name);
1177 --dbms_output.put_line('*l_target_rec.dim7_level_value_id='||l_target_rec.dim7_level_value_id);
1178
1179 -- Get the target for Standard Plan
1180 SELECT plan_id INTO l_plan_id
1181 FROM bisbv_business_plans
1182 WHERE short_name = 'STANDARD';
1183
1184 --dbms_output.put_line('*l_plan_id='||l_plan_id);
1185
1186 l_target_rec.plan_id := l_plan_id;
1187
1188 -- Call the PMF API to get the target
1189 l_target_level_rec.measure_short_name := p_kpi_info_rec.measure_short_name;
1190
1191
1192 BIS_TARGET_PUB.RETRIEVE_TARGET_FROM_SHNMS (
1193 p_api_version => 1.0
1194 ,p_target_level_rec => l_target_level_rec
1195 ,p_Target_Rec => l_target_rec
1196 ,x_Target_Level_Rec => l_target_level_rec_x
1197 ,x_Target_Rec => l_target_rec_x
1198 ,x_return_status => x_return_status
1199 ,x_error_Tbl => l_error_tbl
1200 );
1201
1202 l_target_level_rec := l_target_level_rec_x;
1203 l_target_rec := l_target_rec_x;
1204
1205 x_target_value := l_target_rec.target;
1206
1207 --dbms_output.put_line('###########################');
1208 --dbms_output.put_line('l_target_level_rec.Measure_ID='||l_target_level_rec.Measure_ID);
1209 --dbms_output.put_line('l_target_level_rec.Measure_Short_Name='||l_target_level_rec.Measure_Short_Name);
1210 --dbms_output.put_line('l_target_level_rec.Measure_Name='||l_target_level_rec.Measure_Name);
1211 --dbms_output.put_line('l_target_level_rec.Target_Level_ID='||l_target_level_rec.Target_Level_ID);
1212 --dbms_output.put_line('l_target_level_rec.Target_Level_Short_Name='||l_target_level_rec.Target_Level_Short_Name);
1213 --dbms_output.put_line('l_target_level_rec.Target_Level_Name='||l_target_level_rec.Target_Level_Name);
1214 --dbms_output.put_line('l_target_level_rec.Description='||l_target_level_rec.Description);
1215 --dbms_output.put_line('l_target_level_rec.Org_Level_ID='||l_target_level_rec.Org_Level_ID);
1216 --dbms_output.put_line('l_target_level_rec.Org_Level_Short_Name='||l_target_level_rec.Org_Level_Short_Name);
1217 --dbms_output.put_line('l_target_level_rec.Org_Level_Name='||l_target_level_rec.Org_Level_Name);
1218 --dbms_output.put_line('l_target_level_rec.Time_Level_ID='||l_target_level_rec.Time_Level_ID);
1219 --dbms_output.put_line('l_target_level_rec.Time_Level_Short_Name='||l_target_level_rec.Time_Level_Short_Name);
1220 --dbms_output.put_line('l_target_level_rec.Time_Level_Name='||l_target_level_rec.Time_Level_Name);
1221 --dbms_output.put_line('l_target_level_rec.Dimension1_Level_ID='||l_target_level_rec.Dimension1_Level_ID);
1222 --dbms_output.put_line('l_target_level_rec.Dimension1_Level_Short_Name='||l_target_level_rec.Dimension1_Level_Short_Name);
1223 --dbms_output.put_line('l_target_level_rec.Dimension1_Level_Name='||l_target_level_rec.Dimension1_Level_Name);
1224 --dbms_output.put_line('l_target_level_rec.Dimension2_Level_ID='||l_target_level_rec.Dimension2_Level_ID);
1225 --dbms_output.put_line('l_target_level_rec.Dimension2_Level_Short_Name='||l_target_level_rec.Dimension2_Level_Short_Name);
1226 --dbms_output.put_line('l_target_level_rec.Dimension2_Level_Name='||l_target_level_rec.Dimension2_Level_Name);
1227 --dbms_output.put_line('l_target_level_rec.Dimension3_Level_ID='||l_target_level_rec.Dimension3_Level_ID);
1228 --dbms_output.put_line('l_target_level_rec.Dimension3_Level_Short_Name='||l_target_level_rec.Dimension3_Level_Short_Name);
1229 --dbms_output.put_line('l_target_level_rec.Dimension3_Level_Name='||l_target_level_rec.Dimension3_Level_Name);
1230 --dbms_output.put_line('l_target_level_rec.Dimension4_Level_ID='||l_target_level_rec.Dimension4_Level_ID);
1231 --dbms_output.put_line('l_target_level_rec.Dimension4_Level_Short_Name='||l_target_level_rec.Dimension4_Level_Short_Name);
1232 --dbms_output.put_line('l_target_level_rec.Dimension4_Level_Name='||l_target_level_rec.Dimension4_Level_Name);
1233 --dbms_output.put_line('l_target_level_rec.Dimension5_Level_ID='||l_target_level_rec.Dimension5_Level_ID);
1234 --dbms_output.put_line('l_target_level_rec.Dimension5_Level_Short_Name='||l_target_level_rec.Dimension5_Level_Short_Name);
1235 --dbms_output.put_line('l_target_level_rec.Dimension5_Level_Name='||l_target_level_rec.Dimension5_Level_Name);
1236 --dbms_output.put_line('l_target_level_rec.Dimension6_Level_ID='||l_target_level_rec.Dimension6_Level_ID);
1237 --dbms_output.put_line('l_target_level_rec.Dimension6_Level_Short_Name='||l_target_level_rec.Dimension6_Level_Short_Name);
1238 --dbms_output.put_line('l_target_level_rec.Dimension6_Level_Name='||l_target_level_rec.Dimension6_Level_Name);
1239 --dbms_output.put_line('l_target_level_rec.Dimension7_Level_ID='||l_target_level_rec.Dimension7_Level_ID);
1240 --dbms_output.put_line('l_target_level_rec.Dimension7_Level_Short_Name='||l_target_level_rec.Dimension7_Level_Short_Name);
1241 --dbms_output.put_line('l_target_level_rec.Dimension7_Level_Name='||l_target_level_rec.Dimension7_Level_Name);
1242 --dbms_output.put_line('l_target_level_rec.Report_Function_ID='||l_target_level_rec.Report_Function_ID);
1243 --dbms_output.put_line('l_target_level_rec.Report_Function_Name='||l_target_level_rec.Report_Function_Name);
1244 --dbms_output.put_line('l_target_level_rec.Report_User_Function_Name='||l_target_level_rec.Report_User_Function_Name);
1245 --dbms_output.put_line('l_target_level_rec.Unit_Of_Measure='||l_target_level_rec.Unit_Of_Measure);
1246 --dbms_output.put_line('l_target_level_rec.Source='||l_target_level_rec.Source);
1247 --dbms_output.put_line('###########################');
1248 --dbms_output.put_line('l_target_rec.Target_ID='||l_target_rec.Target_ID);
1249 --dbms_output.put_line('l_target_rec.Target_Level_ID='||l_target_rec.Target_Level_ID);
1250 --dbms_output.put_line('l_target_rec.Target_Level_Short_Name='||l_target_rec.Target_Level_Short_Name);
1251 --dbms_output.put_line('l_target_rec.Target_Level_Name='||l_target_rec.Target_Level_Name);
1252 --dbms_output.put_line('l_target_rec.Plan_ID='||l_target_rec.Plan_ID);
1253 --dbms_output.put_line('l_target_rec.Plan_Short_Name='||l_target_rec.Plan_Short_Name);
1254 --dbms_output.put_line('l_target_rec.Plan_Name='||l_target_rec.Plan_Name);
1255 --dbms_output.put_line('l_target_rec.Org_level_value_id='||l_target_rec.Org_level_value_id);
1256 --dbms_output.put_line('l_target_rec.Org_level_value_name='||l_target_rec.Org_level_value_name);
1257 --dbms_output.put_line('l_target_rec.Time_level_Value_id='||l_target_rec.Time_level_Value_id);
1258 --dbms_output.put_line('l_target_rec.Time_level_Value_name='||l_target_rec.Time_level_Value_name);
1259 --dbms_output.put_line('l_target_rec.Dim1_Level_Value_ID='||l_target_rec.Dim1_Level_Value_ID);
1260 --dbms_output.put_line('l_target_rec.Dim1_Level_Value_Name='||l_target_rec.Dim1_Level_Value_Name);
1261 --dbms_output.put_line('l_target_rec.Dim2_Level_Value_ID='||l_target_rec.Dim2_Level_Value_ID);
1262 --dbms_output.put_line('l_target_rec.Dim2_Level_Value_Name='||l_target_rec.Dim2_Level_Value_Name);
1263 --dbms_output.put_line('l_target_rec.Dim3_Level_Value_ID='||l_target_rec.Dim3_Level_Value_ID);
1264 --dbms_output.put_line('l_target_rec.Dim3_Level_Value_Name='||l_target_rec.Dim3_Level_Value_Name);
1265 --dbms_output.put_line('l_target_rec.Dim4_Level_Value_ID='||l_target_rec.Dim4_Level_Value_ID);
1266 --dbms_output.put_line('l_target_rec.Dim4_Level_Value_Name='||l_target_rec.Dim4_Level_Value_Name);
1267 --dbms_output.put_line('l_target_rec.Dim5_Level_Value_ID='||l_target_rec.Dim5_Level_Value_ID);
1268 --dbms_output.put_line('l_target_rec.Dim5_Level_Value_Name='||l_target_rec.Dim5_Level_Value_Name);
1269 --dbms_output.put_line('l_target_rec.Dim6_Level_Value_ID='||l_target_rec.Dim6_Level_Value_ID);
1270 --dbms_output.put_line('l_target_rec.Dim6_Level_Value_Name='||l_target_rec.Dim6_Level_Value_Name);
1271 --dbms_output.put_line('l_target_rec.Dim7_Level_Value_ID='||l_target_rec.Dim7_Level_Value_ID);
1272 --dbms_output.put_line('l_target_rec.Dim7_Level_Value_Name='||l_target_rec.Dim7_Level_Value_Name);
1273 --dbms_output.put_line('l_target_rec.Target='||l_target_rec.Target);
1274 --dbms_output.put_line('l_target_rec.Range1_low='||l_target_rec.Range1_low);
1275 --dbms_output.put_line('l_target_rec.Range1_high='||l_target_rec.Range1_high);
1276 --dbms_output.put_line('l_target_rec.Range2_low='||l_target_rec.Range2_low);
1277 --dbms_output.put_line('l_target_rec.Range2_high='||l_target_rec.Range2_high);
1278 --dbms_output.put_line('l_target_rec.Range3_low='||l_target_rec.Range3_low);
1279 --dbms_output.put_line('l_target_rec.Range3_high='||l_target_rec.Range3_high);
1280 --dbms_output.put_line('############## ERRORS ##########');
1281 --FOR l_i IN 1 .. l_error_tbl.COUNT LOOP
1282 -- dbms_output.put_line('Error_Description='||l_error_tbl(l_i).Error_Description);
1283 --END LOOP;
1284
1285 --dbms_output.put_line('End BSC_BIS_WRAPPER_PVT.Get_Target_Value_From_PMF' );
1286
1287 EXCEPTION
1288 WHEN FND_API.G_EXC_ERROR THEN
1289 x_return_status := FND_API.G_RET_STS_ERROR;
1290 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1291 ,p_data => x_msg_data);
1292 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1294 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1295 ,p_data => x_msg_data);
1296 WHEN NO_DATA_FOUND THEN
1297 x_return_status := FND_API.G_RET_STS_ERROR;
1298 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1299 ,p_data => x_msg_data);
1300 WHEN OTHERS THEN
1301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1302 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1303 ,p_data => x_msg_data);
1304 END Get_Target_Value_From_PMF;
1305
1306 /************************************************************************************
1307 ************************************************************************************/
1308
1309 PROCEDURE Get_Total_DimLevel_Info(
1310 p_dimension_short_name IN VARCHAR2,
1311 x_total_dimlevel_short_name OUT NOCOPY VARCHAR2,
1312 x_total_dimlevel_value_id OUT NOCOPY NUMBER,
1313 x_total_dimlevel_value_name OUT NOCOPY VARCHAR2
1314 ) IS
1315
1316 TYPE RefCurTyp IS REF CURSOR;
1317 cv RefCurTyp;
1318
1319 l_sql VARCHAR2(32000);
1320 l_source VARCHAR2(2000);
1321 l_length NUMBER;
1322
1323 l_select_string VARCHAR2(32000);
1324 l_view_name VARCHAR2(2000);
1325 l_id_name VARCHAR2(2000);
1326 l_value_name VARCHAR2(2000);
1327
1328 l_return_status VARCHAR2(2000);
1329 l_msg_count NUMBER;
1330 l_msg_data VARCHAR2(32000);
1331
1332 BEGIN
1333 IF p_dimension_short_name IS NULL THEN
1334 RETURN;
1335 END IF;
1336
1337 l_sql := 'SELECT DISTINCT l.source'||
1338 ' FROM bis_levels l, bis_dimensions d'||
1339 ' WHERE d.dimension_id = l.dimension_id AND d.short_name = :1';
1340
1341 OPEN cv FOR l_sql USING p_dimension_short_name;
1342 FETCH cv INTO l_source;
1343 IF cv%NOTFOUND THEN
1344 l_source := 'OLTP';
1345 END IF;
1346 CLOSE cv;
1347
1348 IF (l_source = 'EDW') THEN
1349 l_length := length(p_dimension_short_name);
1350 x_total_dimlevel_short_name := substr(p_dimension_short_name,1,(l_length-1) );
1351 x_total_dimlevel_short_name := x_total_dimlevel_short_name||'A';
1352 END IF;
1353 IF (l_source = 'OLTP') THEN
1354 -- This is not always true
1355 -- x_total_dimlevel_short_name := 'TOTAL_'||p_dimension_short_name;
1356
1357 -- The total dimension level starts with TOTAL
1358 l_sql := 'SELECT DISTINCT l.short_name'||
1359 ' FROM bis_levels l, bis_dimensions d'||
1360 ' WHERE d.dimension_id = l.dimension_id AND'||
1361 ' d.short_name = :1 AND l.short_name LIKE ''TOTAL%''';
1362
1363 OPEN cv FOR l_sql USING p_dimension_short_name;
1364 FETCH cv INTO x_total_dimlevel_short_name;
1365 CLOSE cv;
1366 END IF;
1367
1368 BIS_PMF_GET_DIMLEVELS_PUB.GET_DIMLEVEL_SELECT_STRING (
1369 p_DimLevelShortName => x_total_dimlevel_short_name
1370 ,p_bis_source => l_source
1371 ,x_Select_String => l_select_string
1372 ,x_table_name => l_view_name
1373 ,x_id_name => l_id_name
1374 ,x_value_name => l_value_name
1375 ,x_return_status => l_return_status
1376 ,x_msg_count => l_msg_count
1377 ,x_msg_data => l_msg_data
1378 );
1379
1380 IF l_msg_count > 0 THEN
1381 RETURN;
1382 END IF;
1383
1384 l_sql := 'SELECT DISTINCT '||l_id_name||', '||l_value_name||
1385 ' FROM '||l_view_name;
1386
1387 -- Query is supposed to return just one record. However we take the first one.
1388 OPEN cv FOR l_sql;
1389 FETCH cv INTO x_total_dimlevel_value_id, x_total_dimlevel_value_name;
1390 CLOSE cv;
1391
1392 END Get_Total_DimLevel_Info;
1393
1394 /************************************************************************************
1395 ************************************************************************************/
1396
1397 FUNCTION Get_Dimension_Level_Index(
1398 p_dimension_short_name IN VARCHAR2
1399 ,p_dimension_levels IN BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Tbl_Type
1400 ) RETURN NUMBER IS
1401 l_index NUMBER := 0;
1402 BEGIN
1403
1404 IF p_dimension_short_name IS NULL THEN
1405 RETURN l_index;
1406 END IF;
1407
1408 FOR l_index IN 1 .. p_dimension_levels.COUNT LOOP
1409 IF p_dimension_short_name = p_dimension_levels(l_index).dimension_short_name THEN
1410 RETURN l_index;
1411 END IF;
1412 END LOOP;
1413
1414 l_index := 0;
1415 RETURN l_index;
1416
1417 END Get_Dimension_Level_Index;
1418
1419 /************************************************************************************
1420 ************************************************************************************/
1421
1422 PROCEDURE Get_Default_Time_Level(
1423 p_kpi_code IN NUMBER
1424 ,p_dimset_id IN NUMBER
1425 ,x_time_dimension_level OUT NOCOPY BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Type
1426 ) IS
1427
1428 TYPE RefCurTyp IS REF CURSOR;
1429 cv RefCurTyp;
1430 l_sql VARCHAR2(32000);
1431 l_dim_level BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Type;
1432
1433 BEGIN
1434
1435 -- By design, the ViewBy dimension Level is define in the column BKL.DEFAULT_VALUE with the value 'C'
1436 -- The other Default Dimension Levels are define in the column BKL.DEFAULT_VALUE with the value 'LD'
1437
1438 l_sql := 'SELECT BID.SHORT_NAME DIMENSION_SHORTNAME, '||
1439 ' BKL.LEVEL_SHORTNAME '||
1440 ' FROM BSC_KPI_DIM_LEVELS_VL BKL,'||
1441 ' BIS_LEVELS BIL,'||
1442 ' BIS_DIMENSIONS BID'||
1443 ' WHERE BKL.INDICATOR = :1 AND'||
1444 ' BKL.DIM_SET_ID = :2 AND'||
1445 ' BKL.LEVEL_SOURCE = ''PMF'' AND'||
1446 ' (BKL.DEFAULT_VALUE = ''C'' OR BKL.DEFAULT_VALUE = ''LD'' ) AND'||
1447 ' BKL.LEVEL_SHORTNAME = BIL.SHORT_NAME AND'||
1448 ' BIL.DIMENSION_ID = BID.DIMENSION_ID'||
1449 ' ORDER BY DIMENSION_SHORTNAME, BKL.DIM_LEVEL_INDEX';
1450
1451 OPEN cv FOR l_sql USING p_kpi_code, p_dimset_id;
1452 LOOP
1453 FETCH cv INTO l_dim_level.dimension_short_name, l_dim_level.level_short_name;
1454 EXIT WHEN cv%NOTFOUND;
1455
1456 IF Is_Time_Dimension(l_dim_level.dimension_short_name) THEN
1457 x_time_dimension_level := l_dim_level;
1458 CLOSE cv;
1459 RETURN;
1460 END IF;
1461 END LOOP;
1462 CLOSE cv;
1463
1464 END Get_Default_Time_Level;
1465
1466 /************************************************************************************
1467 ************************************************************************************/
1468
1469 PROCEDURE Get_Default_Dimension_Levels(
1470 p_kpi_code IN NUMBER,
1471 p_dimset_id IN NUMBER,
1472 p_page_parameters IN BSC_BIS_WRAPPER_PUB.Page_Parameter_Rec_Tbl_Type,
1473 x_default_dimension_levels OUT NOCOPY BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Tbl_Type,
1474 x_default_time_level_from OUT NOCOPY BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Type,
1475 x_default_time_level_to OUT NOCOPY BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Type
1476 ) IS
1477
1478 TYPE RefCurTyp IS REF CURSOR;
1479 cv RefCurTyp;
1480 l_sql VARCHAR2(32000);
1481 i NUMBER := 1;
1482
1483 l_default_dimension_levels BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Tbl_Type;
1484 l_default_time_level_from BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Type;
1485 l_default_time_level_to BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Type;
1486
1487 l_dimension_short_name VARCHAR2(2000);
1488 l_level_short_name VARCHAR2(2000);
1489 l_default_flag NUMBER;
1490
1491 l_page_parameter_name VARCHAR2(32000);
1492 l_page_parameter BSC_BIS_WRAPPER_PUB.Page_Parameter_Rec_Type;
1493 l_dimension_level BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Type;
1494 l_dimension_level_index NUMBER;
1495
1496 -- parameters to get current period info
1497 l_dim_source VARCHAR2(2000);
1498 l_org_dim_level_short_name VARCHAR2(2000);
1499 l_org_dim_level_value_id VARCHAR2(2000);
1500 l_current_period_id VARCHAR2(2000);
1501 l_current_period_name VARCHAR2(2000);
1502
1503 l_return_status VARCHAR2(2000);
1504 l_msg_count NUMBER;
1505 l_msg_data VARCHAR2(32000);
1506
1507 BEGIN
1508
1509 -- By design, the ViewBy dimension Level is define in the column BKL.DEFAULT_VALUE with the value 'C'
1510 -- The other Default Dimension Levels are define in the column BKL.DEFAULT_VALUE with the value 'LD'
1511
1512 --dbms_OUTPUT.PUT_LINE('Begin BSC_BIS_WRAPPER_PVT.Get_Default_Dimension_Levels' );
1513
1514 l_sql := 'SELECT BID.SHORT_NAME DIMENSION_SHORTNAME, '||
1515 ' BKL.LEVEL_SHORTNAME, '||
1516 ' DECODE(BKL.DEFAULT_VALUE,''C'', 1, ''LD'', 1, 0) DEFAULT_FLAG '||
1517 ' FROM BSC_KPI_DIM_LEVELS_VL BKL,'||
1518 ' BIS_LEVELS BIL,'||
1519 ' BIS_DIMENSIONS BID'||
1520 ' WHERE BKL.INDICATOR = :1 AND'||
1521 ' BKL.DIM_SET_ID = :2 AND'||
1522 ' BKL.LEVEL_SOURCE = ''PMF'' AND'||
1523 ' BKL.LEVEL_SHORTNAME = BIL.SHORT_NAME AND'||
1524 ' BIL.DIMENSION_ID = BID.DIMENSION_ID';
1525
1526 OPEN cv FOR l_sql USING p_kpi_code, p_dimset_id;
1527 LOOP
1528 FETCH cv INTO l_dimension_short_name, l_level_short_name, l_default_flag;
1529 EXIT WHEN cv%NOTFOUND;
1530
1531 --dbms_output.put_line('-* l_dimension_short_name='||l_dimension_short_name);
1532 --dbms_output.put_line('-* l_level_short_name='||l_level_short_name);
1533 --dbms_output.put_line('-* l_default_flag='||l_default_flag);
1534
1535 l_page_parameter_name := l_dimension_short_name||'+'||l_level_short_name;
1536 IF Is_Time_Dimension(l_dimension_short_name) THEN
1537 l_page_parameter_name := l_page_parameter_name||'_FROM';
1538 END IF;
1539
1540 Get_Page_Parameter(
1541 p_page_parameters => p_page_parameters,
1542 p_page_parameter_name => l_page_parameter_name,
1543 x_page_parameter => l_page_parameter
1544 );
1545
1546 IF l_page_parameter.parameter_name IS NULL THEN
1547 IF l_default_flag = 1 THEN
1548 -- There is no page parameter for this dimension level and the dimension level
1549 -- is the default dimension level.
1550 -- So we need to add this parameter, but we need to check that no other
1551 -- dimension level of the same dimension has been assigned before.
1552 -- The only case is when another dimension level of the same dimension exists
1553 -- in the page parameters.
1554 l_dimension_level_index := Get_Dimension_Level_Index(l_dimension_short_name,
1555 l_default_dimension_levels);
1556
1557 IF l_dimension_level_index = 0 THEN
1558 -- The dimension level has not been assigned before
1559 l_dimension_level.dimension_short_name := l_dimension_short_name;
1560 l_dimension_level.level_short_name := l_level_short_name;
1561 l_dimension_level.level_value_id := NULL;
1562 l_dimension_level.level_value_name := NULL;
1563
1564 IF Is_Time_Dimension(l_dimension_short_name) THEN
1565 l_default_time_level_from := l_dimension_level;
1566 l_default_time_level_to := l_dimension_level;
1567 ELSE
1568 l_default_dimension_levels(i) := l_dimension_level;
1569 i := i + 1;
1570 END IF;
1571 END IF;
1572 END IF;
1573 ELSE
1574 -- The dimension level is used by the kpi and also is a page parameter
1575 -- It needs to use that dimension level with the specific value
1576 IF Is_Time_Dimension(l_dimension_short_name) THEN
1577 -- l_page_parameter contains the time _FROM parameter
1578 l_default_time_level_from.dimension_short_name := l_dimension_short_name;
1579 l_default_time_level_from.level_short_name := l_level_short_name;
1580 l_default_time_level_from.level_value_id := l_page_parameter.value_id;
1581 l_default_time_level_from.level_value_name := l_page_parameter.value_name;
1582
1583 -- If exists the time_from parameter must exist time_to parameter
1584 l_page_parameter_name := l_dimension_short_name||'+'||l_level_short_name||'_TO';
1585 Get_Page_Parameter(
1586 p_page_parameters => p_page_parameters,
1587 p_page_parameter_name => l_page_parameter_name,
1588 x_page_parameter => l_page_parameter
1589 );
1590
1591 l_default_time_level_to.dimension_short_name := l_dimension_short_name;
1592 l_default_time_level_to.level_short_name := l_level_short_name;
1593 l_default_time_level_to.level_value_id := l_page_parameter.value_id;
1594 l_default_time_level_to.level_value_name := l_page_parameter.value_name;
1595 ELSE
1596 l_dimension_level.dimension_short_name := l_dimension_short_name;
1597 l_dimension_level.level_short_name := l_level_short_name;
1598 l_dimension_level.level_value_id := l_page_parameter.value_id;
1599 l_dimension_level.level_value_name := l_page_parameter.value_name;
1600
1601 -- If already exists, it needs to overwrite it. If not then add it.
1602 l_dimension_level_index := Get_Dimension_Level_Index(l_dimension_short_name,
1603 l_default_dimension_levels);
1604 IF l_dimension_level_index = 0 THEN
1605 l_default_dimension_levels(i) := l_dimension_level;
1606 i := i + 1;
1607 ELSE
1608 l_default_dimension_levels(l_dimension_level_index) := l_dimension_level;
1609 END IF;
1610 END IF;
1611 END IF;
1612
1613 END LOOP;
1614 CLOSE cv;
1615
1616 -- If the time level exists, and there is no value specified by a page parameter
1617 -- then we need to set it with the current period
1618 IF (l_default_time_level_from.dimension_short_name IS NOT NULL) AND
1619 (l_default_time_level_from.level_value_id IS NULL) THEN
1620 IF l_default_time_level_from.dimension_short_name = 'TIME' THEN
1621 -- OLTP
1622 l_dim_source := 'OLTP';
1623 l_org_dim_level_short_name := 'TOTAL_ORGANIZATIONS';
1624 l_org_dim_level_value_id := '-1';
1625 ELSE
1626 -- EDW
1627 l_dim_source := 'EDW';
1628 l_org_dim_level_short_name := NULL;
1629 l_org_dim_level_value_id := NULL;
1630 END IF;
1631
1632 Get_Period_Info(
1633 p_time_dim_level_short_name => l_default_time_level_from.level_short_name,
1634 p_source => l_dim_source,
1635 p_org_dim_level_short_name => l_org_dim_level_short_name,
1636 p_org_dim_level_value_id => l_org_dim_level_value_id,
1637 p_period_date => SYSDATE, --TO_DATE('04-15-2001','MM-DD-YYYY'),
1638 x_period_id => l_current_period_id,
1639 x_period_name => l_current_period_name,
1640 x_return_status => l_return_status,
1641 x_msg_count => l_msg_count,
1642 x_msg_data => l_msg_data
1643 );
1644
1645 l_default_time_level_from.level_value_id := l_current_period_id;
1646 l_default_time_level_from.level_value_name := l_current_period_name;
1647 l_default_time_level_to.level_value_id := l_current_period_id;
1648 l_default_time_level_to.level_value_name := l_current_period_name;
1649 END IF;
1650
1651
1652 x_default_dimension_levels := l_default_dimension_levels;
1653 x_default_time_level_from := l_default_time_level_from;
1654 x_default_time_level_to := l_default_time_level_to;
1655
1656 -- dbms_output.put_line('End BSC_BIS_WRAPPER_PVT.Get_Default_Dimension_Levels');
1657 --FOR i IN 1..x_default_dimension_levels.COUNT LOOP
1658 -- dbms_output.put_line('x_default_dimension_levels(i).dimension_short_name='||x_default_dimension_levels(i).dimension_short_name);
1659 -- dbms_output.put_line('x_default_dimension_levels(i).level_short_name='||x_default_dimension_levels(i).level_short_name);
1660 -- dbms_output.put_line('x_default_dimension_levels(i).level_value_id='||x_default_dimension_levels(i).level_value_id);
1661 -- dbms_output.put_line('x_default_dimension_levels(i).level_value_name='||x_default_dimension_levels(i).level_value_nane);
1662 --END LOOP;
1663 --dbms_output.put_line('x_default_time_level_from.dimension_short_name='||x_default_time_level_from.dimension_short_name);
1664 --dbms_output.put_line('x_default_time_level_from.level_short_name='||x_default_time_level_from.level_short_name);
1665 --dbms_output.put_line('x_default_time_level_from.level_value_id='||x_default_time_level_from.level_value_id);
1666 --dbms_output.put_line('x_default_time_level_from.level_value_name='||x_default_time_level_from.level_value_name);
1667 --dbms_output.put_line('x_default_time_level_to.dimension_short_name='||x_default_time_level_to.dimension_short_name);
1668 --dbms_output.put_line('x_default_time_level_to.level_short_name='||x_default_time_level_to.level_short_name);
1669 --dbms_output.put_line('x_default_time_level_to.level_value_id='||x_default_time_level_to.level_value_id);
1670 --dbms_output.put_line('x_default_time_level_to.level_value_name='||x_default_time_level_to.level_value_name);
1671
1672 END Get_Default_Dimension_Levels;
1673
1674 /************************************************************************************
1675 ************************************************************************************/
1676
1677 FUNCTION Is_Time_Dimension(
1678 p_dimension_short_name IN VARCHAR2
1679 ) RETURN BOOLEAN IS
1680
1681 BEGIN
1682
1683 IF (p_dimension_short_name = 'TIME') OR (p_dimension_short_name = 'EDW_TIME_M') THEN
1684 RETURN TRUE;
1685 ELSE
1686 RETURN FALSE;
1687 END IF;
1688
1689 END Is_Time_Dimension;
1690
1691 /************************************************************************************
1692 ************************************************************************************/
1693
1694 PROCEDURE Populate_Measure_Data(
1695 p_tab_id IN NUMBER,
1696 p_page_id IN VARCHAR2,
1697 p_user_id IN VARCHAR2,
1698 p_responsibility_id IN VARCHAR2,
1699 p_caching_key IN VARCHAR2,
1700 x_return_status OUT NOCOPY VARCHAR2,
1701 x_msg_count OUT NOCOPY NUMBER,
1702 x_msg_data OUT NOCOPY VARCHAR2
1703 ) IS
1704
1705 TYPE tCursor IS REF CURSOR;
1706
1707 cv tCursor;
1708 l_sql VARCHAR2(32000);
1709
1710 cv_caching tCursor;
1711 l_caching_sql VARCHAR2(32000);
1712 l_insert_sql VARCHAR2(32000);
1713 l_update_sql VARCHAR2(32000);
1714
1715 l_kpi_code NUMBER;
1716 l_analysis_option0 NUMBER;
1717 l_analysis_option1 NUMBER;
1718 l_analysis_option2 NUMBER;
1719 l_series_id NUMBER;
1720
1721 l_kpi_info_rec BSC_BIS_WRAPPER_PUB.Kpi_Info_Rec_Type;
1722 l_kpi_info_tbl BSC_BIS_WRAPPER_PUB.Kpi_Info_Rec_Tbl_Type;
1723
1724 l_caching_key VARCHAR2(200);
1725 i NUMBER := 1;
1726
1727 l_return_status VARCHAR2(2000);
1728 l_msg_count NUMBER;
1729 l_msg_data VARCHAR2(32000);
1730
1731 l_db_user_id NUMBER;
1732 l_sysdate DATE := SYSDATE;
1733
1734 l_page_parameters BSC_BIS_WRAPPER_PUB.Page_Parameter_Rec_Tbl_Type;
1735
1736 BEGIN
1737 FND_MSG_PUB.initialize;
1738
1739 -- Get the database user id
1740 -- Ref: bug#3482442 In corner cases this query can return more than one
1741 -- row and it will fail. AUDSID is not PK. After meeting with
1742 -- Vinod and Kris and Venu, we should use FNG_GLOBAL.user_id
1743 l_db_user_id := BSC_APPS.fnd_global_user_id;
1744
1745 --dbms_output.put_line('*l_db_user_id='||l_db_user_id);
1746
1747 --Delete from BSC_BIS_MEASURES_DATA kpis that belong to the given tab, which has
1748 --been deleted, has been changed or user has not access.
1749
1750 DELETE FROM
1751 bsc_bis_measures_data m
1752 WHERE
1753 m.user_id = p_user_id AND
1754 m.responsibility_id = p_responsibility_id AND
1755 m.indicator IN (
1756 SELECT
1757 tk.indicator
1758 FROM
1759 bsc_tab_indicators tk
1760 WHERE
1761 tk.tab_id = p_tab_id
1762 ) AND (
1763 m.indicator NOT IN (
1764 SELECT
1765 a.indicator
1766 FROM
1767 bsc_user_kpi_access a
1768 WHERE
1769 a.responsibility_id = p_responsibility_id
1770 ) OR
1771 m.indicator = (
1772 SELECT
1773 k.indicator
1774 FROM
1775 bsc_kpis_b k
1776 WHERE
1777 k.indicator = m.indicator AND (
1778 k.prototype_flag = 2 OR
1779 k.last_update_date > m.last_update_date)
1780 ));
1781
1782 --dbms_output.put_line('*after delete');
1783
1784 -- Populate BSC_BIS_MEASURES_DATA for the default analysis combination
1785 -- of each kpi which user has access and belongs to the given tab.
1786
1787 l_sql := 'SELECT tk.indicator'||
1788 ' FROM bsc_tab_indicators tk, bsc_user_kpi_access ka'||
1789 ' WHERE tk.tab_id = :1 AND tk.indicator = ka.indicator AND ka.responsibility_id = :2';
1790
1791 l_caching_sql := 'SELECT caching_key'||
1792 ' FROM bsc_bis_measures_data'||
1793 ' WHERE user_id = :1 AND responsibility_id = :2 AND indicator = :3 AND'||
1794 ' analysis_option0 = :4 AND analysis_option1 = :5 AND analysis_option2 = :6 AND'||
1795 ' series_id = :7';
1796
1797 l_update_sql := 'UPDATE bsc_bis_measures_data'||
1798 ' SET actual_data = :a, budget_data = :b, caching_key = :c,'||
1799 ' last_updated_by = :d, last_update_date = :e'||
1800 ' WHERE user_id = :f AND responsibility_id = :g AND indicator = :h AND'||
1801 ' analysis_option0 = :i AND analysis_option1 = :j AND analysis_option2 = :k AND'||
1802 ' series_id = :l';
1803
1804 l_insert_sql := 'INSERT INTO bsc_bis_measures_data (user_id, responsibility_id, indicator,'||
1805 ' analysis_option0, analysis_option1, analysis_option2, series_id,'||
1806 ' caching_key, actual_data, budget_data, created_by, creation_date,'||
1807 ' last_updated_by, last_update_date, last_update_login)'||
1808 ' VALUES (:a, :b, :c, :d, :e, :f, :g, :h, :i, :j, :k, :l, :m, :n, :o)';
1809
1810 -- Get page parameters
1811 IF p_page_id IS NOT NULL THEN
1812 Get_Page_Parameters(
1813 p_user_id => p_user_id,
1814 p_page_id => p_page_id,
1815 x_page_parameters => l_page_parameters,
1816 x_return_status => l_return_status,
1817 x_msg_count => l_msg_count,
1818 x_msg_data => l_msg_data
1819 );
1820 END IF;
1821
1822 OPEN cv FOR l_sql USING p_tab_id, p_responsibility_id;
1823 LOOP
1824 FETCH cv INTO l_kpi_code;
1825 EXIT WHEN cv%NOTFOUND;
1826
1827 --dbms_output.put_line('*l_kpi_code='||l_kpi_code);
1828
1829 -- Get the default analysis option combination
1830 BSC_BIS_WRAPPER_PVT.Get_AO_Defaults(
1831 p_kpi_code => l_kpi_code,
1832 x_analysis_option0 => l_analysis_option0,
1833 x_analysis_option1 => l_analysis_option1,
1834 x_analysis_option2 => l_analysis_option2,
1835 x_series_id => l_series_id);
1836
1837 --dbms_output.put_line('*l_analysis_option0='||l_analysis_option0);
1838 --dbms_output.put_line('*l_analysis_option1='||l_analysis_option1);
1839 --dbms_output.put_line('*l_analysis_option2='||l_analysis_option2);
1840 --dbms_output.put_line('*l_series_id='||l_series_id);
1841
1842
1843 IF (l_kpi_code IS NOT NULL) AND (l_analysis_option0 IS NOT NULL) AND
1844 (l_analysis_option1 IS NOT NULL) AND (l_analysis_option2 IS NOT NULL) AND
1845 (l_series_id IS NOT NULL) THEN
1846
1847 -- Get the caching key
1848 l_caching_key := NULL;
1849 OPEN cv_caching FOR l_caching_sql USING p_user_id, p_responsibility_id, l_kpi_code,
1850 l_analysis_option0, l_analysis_option1, l_analysis_option2, l_series_id;
1851 FETCH cv_caching INTO l_caching_key;
1852 IF cv_caching%FOUND THEN
1853 --dbms_output.put_line('*l_caching_key='||l_caching_key);
1854
1855 -- The record exists --> Calculate data and update if caching key is different
1856 IF (l_caching_key IS NULL) OR (l_caching_key <> p_caching_key) THEN
1857 Get_Kpi_Info(
1858 p_kpi_code => l_kpi_code,
1859 p_analysis_option0 => l_analysis_option0,
1860 p_analysis_option1 => l_analysis_option1,
1861 p_analysis_option2 => l_analysis_option2,
1862 p_series_id => l_series_id,
1863 x_kpi_info_rec => l_kpi_info_rec
1864 );
1865
1866 l_kpi_info_rec.insert_update_flag := 'U';
1867
1868 l_kpi_info_tbl(i) := l_kpi_info_rec;
1869 i := i + 1;
1870
1871 END IF;
1872 ELSE
1873 -- The record does not exists --> Calculate data and Insert
1874 Get_Kpi_Info(
1875 p_kpi_code => l_kpi_code,
1876 p_analysis_option0 => l_analysis_option0,
1877 p_analysis_option1 => l_analysis_option1,
1878 p_analysis_option2 => l_analysis_option2,
1879 p_series_id => l_series_id,
1880 x_kpi_info_rec => l_kpi_info_rec
1881 );
1882
1883 l_kpi_info_rec.insert_update_flag := 'I';
1884
1885 l_kpi_info_tbl(i) := l_kpi_info_rec;
1886 i := i + 1;
1887
1888 END IF;
1889 CLOSE cv_caching;
1890 END IF;
1891 END LOOP;
1892 CLOSE cv;
1893
1894 -- Calculate Kpis Data
1895 Get_Kpis_Data_From_PMF_PMV(
1896 p_user_id => p_user_id,
1897 p_responsibility_id => p_responsibility_id,
1898 p_page_parameters => l_page_parameters,
1899 p_page_id => p_page_id,
1900 p_kpi_info_tbl => l_kpi_info_tbl,
1901 x_return_status => l_return_status,
1902 x_msg_count => l_msg_count,
1903 x_msg_data => l_msg_data
1904 );
1905
1906 -- Insert/Update
1907 FOR i IN 1..l_kpi_info_tbl.COUNT LOOP
1908 IF l_kpi_info_tbl(i).insert_update_flag = 'U' THEN
1909 -- Update actual and target in BSC_BIS_MEASURES_DATA
1910 EXECUTE IMMEDIATE l_update_sql USING l_kpi_info_tbl(i).actual_value,
1911 l_kpi_info_tbl(i).target_value, p_caching_key, l_db_user_id,
1912 l_sysdate, p_user_id, p_responsibility_id, l_kpi_info_tbl(i).kpi_code,
1913 l_kpi_info_tbl(i).analysis_option0, l_kpi_info_tbl(i).analysis_option1,
1914 l_kpi_info_tbl(i).analysis_option2, l_kpi_info_tbl(i).series_id;
1915
1916 --dbms_output.put_line('*update executed');
1917 ELSE
1918 -- Insert actual and target in BSC_BIS_MEASURES_DATA
1919 EXECUTE IMMEDIATE l_insert_sql USING p_user_id, p_responsibility_id,
1920 l_kpi_info_tbl(i).kpi_code, l_kpi_info_tbl(i).analysis_option0,
1921 l_kpi_info_tbl(i).analysis_option1, l_kpi_info_tbl(i).analysis_option2,
1922 l_kpi_info_tbl(i).series_id, p_caching_key,
1923 l_kpi_info_tbl(i).actual_value, l_kpi_info_tbl(i).target_value,
1924 l_db_user_id, l_sysdate, l_db_user_id, l_sysdate, l_db_user_id;
1925
1926 --dbms_output.put_line('*insert executed');
1927 END IF;
1928
1929 --dbms_output.put_line('--------------------------------------------');
1930 --dbms_output.put_line('l_kpi_info_tbl('||i||').kpi_code='||l_kpi_info_tbl(i).kpi_code);
1931 --dbms_output.put_line('l_kpi_info_tbl('||i||').analysis_option0='||l_kpi_info_tbl(i).analysis_option0);
1932 --dbms_output.put_line('l_kpi_info_tbl('||i||').analysis_option1='||l_kpi_info_tbl(i).analysis_option1);
1933 --dbms_output.put_line('l_kpi_info_tbl('||i||').analysis_option2='||l_kpi_info_tbl(i).analysis_option2);
1934 --dbms_output.put_line('l_kpi_info_tbl('||i||').series_id='||l_kpi_info_tbl(i).series_id);
1935 --dbms_output.put_line('l_kpi_info_tbl('||i||').dataset_id='||l_kpi_info_tbl(i).dataset_id);
1936 --dbms_output.put_line('l_kpi_info_tbl('||i||').dataset_source='||l_kpi_info_tbl(i).dataset_source);
1937 --dbms_output.put_line('l_kpi_info_tbl('||i||').measure_short_name='||l_kpi_info_tbl(i).measure_short_name);
1938 --dbms_output.put_line('l_kpi_info_tbl('||i||').measure_dbi_flag='||l_kpi_info_tbl(i).measure_dbi_flag);
1939 --dbms_output.put_line('l_kpi_info_tbl('||i||').measure_id='||l_kpi_info_tbl(i).measure_id);
1940 --dbms_output.put_line('l_kpi_info_tbl('||i||').region_code='||l_kpi_info_tbl(i).region_code);
1941 --dbms_output.put_line('l_kpi_info_tbl('||i||').function_name='||l_kpi_info_tbl(i).function_name);
1942 --dbms_output.put_line('l_kpi_info_tbl('||i||').actual_attribute_code='||l_kpi_info_tbl(i).actual_attribute_code);
1943 --dbms_output.put_line('l_kpi_info_tbl('||i||').compareto_attribute_code='||l_kpi_info_tbl(i).compareto_attribute_code);
1944 --dbms_output.put_line('l_kpi_info_tbl('||i||').format_id='||l_kpi_info_tbl(i).format_id);
1945 --dbms_output.put_line('l_kpi_info_tbl('||i||').dimset_id='||l_kpi_info_tbl(i).dimset_id);
1946 --dbms_output.put_line('l_kpi_info_tbl('||i||').dim1_short_name='||l_kpi_info_tbl(i).dim1_short_name);
1947 --dbms_output.put_line('l_kpi_info_tbl('||i||').dim2_short_name='||l_kpi_info_tbl(i).dim2_short_name);
1948 --dbms_output.put_line('l_kpi_info_tbl('||i||').dim3_short_name='||l_kpi_info_tbl(i).dim3_short_name);
1949 --dbms_output.put_line('l_kpi_info_tbl('||i||').dim4_short_name='||l_kpi_info_tbl(i).dim4_short_name);
1950 --dbms_output.put_line('l_kpi_info_tbl('||i||').dim5_short_name='||l_kpi_info_tbl(i).dim5_short_name);
1951 --dbms_output.put_line('l_kpi_info_tbl('||i||').dim6_short_name='||l_kpi_info_tbl(i).dim6_short_name);
1952 --dbms_output.put_line('l_kpi_info_tbl('||i||').dim7_short_name='||l_kpi_info_tbl(i).dim7_short_name);
1953 --dbms_output.put_line('l_kpi_info_tbl('||i||').actual_value='||l_kpi_info_tbl(i).actual_value);
1954 --dbms_output.put_line('l_kpi_info_tbl('||i||').target_value='||l_kpi_info_tbl(i).target_value);
1955 --dbms_output.put_line('l_kpi_info_tbl('||i||').insert_update_flag='||l_kpi_info_tbl(i).insert_update_flag);
1956 END LOOP;
1957
1958 COMMIT;
1959
1960 EXCEPTION
1961 WHEN FND_API.G_EXC_ERROR THEN
1962 ROLLBACK;
1963 x_return_status := FND_API.G_RET_STS_ERROR;
1964 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1965 ,p_data => x_msg_data);
1966 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1967 ROLLBACK;
1968 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1969 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1970 ,p_data => x_msg_data);
1971 WHEN NO_DATA_FOUND THEN
1972 ROLLBACK;
1973 x_return_status := FND_API.G_RET_STS_ERROR;
1974 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1975 ,p_data => x_msg_data);
1976 WHEN OTHERS THEN
1977 ROLLBACK;
1978 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1979 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1980 ,p_data => x_msg_data);
1981 --dbms_output.put_line('*sqlerrm='||sqlerrm);
1982 END Populate_Measure_Data;
1983
1984 /************************************************************************************
1985 ************************************************************************************/
1986
1987 PROCEDURE Get_Page_Parameters(
1988 p_user_id IN VARCHAR2,
1989 p_page_id IN VARCHAR2,
1990 x_page_parameters OUT NOCOPY BSC_BIS_WRAPPER_PUB.Page_Parameter_Rec_Tbl_Type,
1991 x_return_status OUT NOCOPY VARCHAR2,
1992 x_msg_count OUT NOCOPY NUMBER,
1993 x_msg_data OUT NOCOPY VARCHAR2
1994 ) IS
1995
1996 l_page_session_rec BIS_PMV_PARAMETERS_PUB.page_session_rec_type;
1997 l_page_param_tbl BIS_PMV_PARAMETERS_PUB.parameter_tbl_type;
1998 i NUMBER;
1999
2000 TYPE CursorType IS REF CURSOR;
2001 l_cursor CursorType;
2002 l_sql VARCHAR2(32000);
2003
2004 l_parameter_name VARCHAR2(32000);
2005 l_parameter_value VARCHAR2(32000);
2006 l_parameter_description VARCHAR2(32000);
2007
2008 l_dimension VARCHAR2(100) := 'TIME_COMPARISON_TYPE';
2009 l_attribute_name VARCHAR2(100) := 'AS_OF_DATE';
2010
2011 BEGIN
2012
2013 FND_MSG_PUB.Initialize;
2014
2015 l_page_session_rec.user_id := p_user_id;
2016 l_page_session_rec.page_id := p_page_id;
2017
2018 BIS_PMV_PARAMETERS_PUB.RETRIEVE_PAGE_PARAMETERS(
2019 p_page_session_rec => l_page_session_rec
2020 ,x_page_param_tbl => l_page_param_tbl
2021 ,x_return_status => x_return_status
2022 ,x_msg_count => x_msg_count
2023 ,x_msg_data => x_msg_data);
2024
2025 FOR i IN 1..l_page_param_tbl.COUNT LOOP
2026 x_page_parameters(i).parameter_name := l_page_param_tbl(i).parameter_name;
2027 x_page_parameters(i).value_id := RTRIM(LTRIM(l_page_param_tbl(i).parameter_value, ''''), '''');
2028 x_page_parameters(i).value_name := l_page_param_tbl(i).parameter_description;
2029 END LOOP;
2030
2031 -- This is a workaround to get TIME_COMPARISON_PARAMETER. There is a open bug#2609475
2032 -- to PMV in order to include it in the BIS_PMV_PARAMETERS_PUB.RETRIEVE_PAGE_PARAMETERS
2033 i := x_page_parameters.COUNT + 1;
2034 l_sql := 'SELECT attribute_name, session_value, session_description'||
2035 ' FROM bis_user_attributes'||
2036 ' WHERE user_id = :1 AND page_id = :2 AND dimension = :3';
2037 OPEN l_cursor FOR l_sql USING p_user_id, p_page_id, l_dimension;
2038 FETCH l_cursor INTO l_parameter_name, l_parameter_value, l_parameter_description;
2039 IF l_cursor%FOUND THEN
2040 x_page_parameters(i).parameter_name := l_parameter_name;
2041 x_page_parameters(i).value_id := RTRIM(LTRIM(l_parameter_value, ''''), '''');
2042 x_page_parameters(i).value_name := l_parameter_description;
2043 END IF;
2044 CLOSE l_cursor;
2045
2046 -- bug 2666292
2047 i := x_page_parameters.COUNT + 1;
2048 l_sql := 'SELECT attribute_name, session_value, session_description'||
2049 ' FROM bis_user_attributes'||
2050 ' WHERE user_id = :1 AND page_id = :2 AND attribute_name = :3';
2051 OPEN l_cursor FOR l_sql USING p_user_id, p_page_id, l_attribute_name;
2052 FETCH l_cursor INTO l_parameter_name, l_parameter_value, l_parameter_description;
2053 IF l_cursor%FOUND THEN
2054 x_page_parameters(i).parameter_name := l_parameter_name;
2055 x_page_parameters(i).value_id := RTRIM(LTRIM(l_parameter_value, ''''), '''');
2056 x_page_parameters(i).value_name := l_parameter_description;
2057 END IF;
2058
2059 CLOSE l_cursor;
2060 -- FOR i IN 1..x_page_parameters.COUNT LOOP
2061 -- dbms_output.put_line('*x_page_parameters.parameter_name='||x_page_parameters(i).parameter_name);
2062 -- dbms_output.put_line('*x_page_parameters.value_id='||x_page_parameters(i).value_id);
2063 -- dbms_output.put_line('*x_page_parameters.value_name='||x_page_parameters(i).value_name);
2064 -- END LOOP;
2065
2066 EXCEPTION
2067 WHEN FND_API.G_EXC_ERROR THEN
2068 rollback;
2069 x_return_status := FND_API.G_RET_STS_ERROR;
2070 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2071 ,p_data => x_msg_data);
2072 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2073 rollback;
2074 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2075 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2076 ,p_data => x_msg_data);
2077 WHEN NO_DATA_FOUND THEN
2078 rollback;
2079 x_return_status := FND_API.G_RET_STS_ERROR;
2080 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2081 ,p_data => x_msg_data);
2082 WHEN OTHERS THEN
2083 rollback;
2084 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2085 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2086 ,p_data => x_msg_data);
2087
2088 END Get_Page_Parameters;
2089
2090 /************************************************************************************
2091 ************************************************************************************/
2092
2093 PROCEDURE Get_Page_Parameter(
2094 p_page_parameters IN BSC_BIS_WRAPPER_PUB.Page_Parameter_Rec_Tbl_Type,
2095 p_page_parameter_name IN VARCHAR2,
2096 x_page_parameter OUT NOCOPY BSC_BIS_WRAPPER_PUB.Page_Parameter_Rec_Type
2097 ) IS
2098 i NUMBER;
2099 BEGIN
2100
2101 FOR i IN 1..p_page_parameters.COUNT LOOP
2102 IF p_page_parameters(i).parameter_name = p_page_parameter_name THEN
2103 x_page_parameter := p_page_parameters(i);
2104 END IF;
2105 END LOOP;
2106
2107 END Get_Page_Parameter;
2108
2109 /************************************************************************************
2110 ************************************************************************************/
2111
2112 FUNCTION Get_Time_Comparison_Parameter(
2113 p_page_parameters IN BSC_BIS_WRAPPER_PUB.Page_Parameter_Rec_Tbl_Type
2114 ) RETURN VARCHAR2 IS
2115 i NUMBER;
2116 BEGIN
2117 FOR i IN 1..p_page_parameters.COUNT LOOP
2118 IF INSTR(p_page_parameters(i).parameter_name, 'TIME_COMPARISON_TYPE+') > 0 THEN
2119 RETURN p_page_parameters(i).parameter_name;
2120 END IF;
2121 END LOOP;
2122
2123 RETURN NULL;
2124
2125 END Get_Time_Comparison_Parameter;
2126
2127 /************************************************************************************
2128 ************************************************************************************/
2129
2130 PROCEDURE Get_Kpis_Data_From_PMF_PMV(
2131 p_user_id IN VARCHAR2,
2132 p_responsibility_id IN VARCHAR2,
2133 p_page_parameters IN BSC_BIS_WRAPPER_PUB.Page_Parameter_Rec_Tbl_Type,
2134 p_page_id IN VARCHAR2,
2135 p_kpi_info_tbl IN OUT NOCOPY BSC_BIS_WRAPPER_PUB.Kpi_Info_Rec_Tbl_Type,
2136 x_return_status OUT NOCOPY VARCHAR2,
2137 x_msg_count OUT NOCOPY NUMBER,
2138 x_msg_data OUT NOCOPY VARCHAR2
2139 ) IS
2140
2141 i NUMBER;
2142 j NUMBER;
2143 k NUMBER;
2144 l_ak_regions BSC_BIS_WRAPPER_PUB.t_array_of_varchar2;
2145 l_num_ak_regions NUMBER := 0;
2146
2147 l_return_status VARCHAR2(2000);
2148 l_msg_count NUMBER;
2149 l_msg_data VARCHAR2(32000);
2150
2151 l_viewby_level VARCHAR2(200);
2152 l_default_dimension_levels BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Tbl_Type;
2153 l_default_time_level_from BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Type;
2154 l_default_time_level_to BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Type;
2155 l_time_comparison_type VARCHAR2(32000);
2156
2157 l_actual_value VARCHAR2(2000);
2158 l_target_value VARCHAR2(2000);
2159 l_compareto_value VARCHAR2(2000);
2160
2161 l_time_parameter BIS_PMV_ACTUAL_PVT.TIME_PARAMETER_REC_TYPE;
2162 l_parameters BIS_PMV_ACTUAL_PVT.PARAMETER_TBL_TYPE;
2163 l_measure_attribute_codes BIS_PMV_ACTUAL_PVT.MEASURE_ATTR_CODES_TYPE;
2164 l_ranking_level VARCHAR2(200);
2165 l_measure_tbl BIS_PMV_ACTUAL_PVT.ACTUAL_VALUE_TBL_TYPE;
2166
2167 l_fnd_miss_num NUMBER := 9.99E125;
2168
2169 l_sum_actual_value NUMBER := NULL;
2170 l_total_actual_value NUMBER := NULL;
2171 l_f_actual_value VARCHAR2(200) := NULL;
2172
2173 BEGIN
2174
2175 FND_MSG_PUB.initialize;
2176
2177 FOR i IN 1..p_kpi_info_tbl.COUNT LOOP
2178 --dbms_output.put_line('--------------------------------------------------------');
2179 --dbms_output.put_line('*p_kpi_info_tbl(i).kpi_code='||p_kpi_info_tbl(i).kpi_code);
2180
2181 IF p_kpi_info_tbl(i).dataset_source = 'PMF' THEN
2182 IF (p_page_id IS NOT NULL) AND (p_page_parameters.COUNT > 0) AND
2183 (p_kpi_info_tbl(i).measure_dbi_flag = 'Y') THEN
2184 -- We come from a DBI portal page with page parameter portlet
2185 -- We can use better method to calculate the actuals
2186 -- in one PMV API call.
2187
2188 -- Add the AK region of the measure to the array of AK Regions
2189 -- The actual for those AK region will be calculated later.
2190
2191 IF p_kpi_info_tbl(i).region_code IS NOT NULL THEN
2192 IF NOT Item_Belong_To_Array_Varchar2(p_kpi_info_tbl(i).region_code, l_ak_regions, l_num_ak_regions) THEN
2193 l_num_ak_regions := l_num_ak_regions + 1;
2194 l_ak_regions(l_num_ak_regions) := p_kpi_info_tbl(i).region_code;
2195 END IF;
2196 END IF;
2197
2198 ELSE
2199 -- We do not come from a portal page or the portal page
2200 -- does not have page parameters or the measure is not dbi.
2201 -- We ned to calculate the data of the Kpi one by one
2202 -- because every Kpi has different defaults and view by
2203 -- and they are not going to be overwritten.
2204
2205 l_return_status := NULL;
2206 l_msg_count := NULL;
2207 l_msg_data := NULL;
2208
2209 Get_Kpi_view_by(
2210 p_kpi_code => p_kpi_info_tbl(i).kpi_code,
2211 p_dimset_id => p_kpi_info_tbl(i).dimset_id,
2212 p_page_parameters => p_page_parameters,
2213 x_viewby_level => l_viewby_level,
2214 x_return_status => l_return_status,
2215 x_msg_count => l_msg_count,
2216 x_msg_data => l_msg_data
2217 );
2218
2219 --dbms_output.put_line('*l_viewby_level='||l_viewby_level);
2220
2221 -- Get the default dimension levels of the dimension set
2222 -- Also overwrite the dimension levels according to the page level parameters if they exists
2223 Get_Default_Dimension_Levels(
2224 p_kpi_code => p_kpi_info_tbl(i).kpi_code,
2225 p_dimset_id => p_kpi_info_tbl(i).dimset_id,
2226 p_page_parameters => p_page_parameters,
2227 x_default_dimension_levels => l_default_dimension_levels,
2228 x_default_time_level_from => l_default_time_level_from,
2229 x_default_time_level_to => l_default_time_level_to
2230 );
2231
2232 l_time_comparison_type := Get_Time_Comparison_Parameter(p_page_parameters);
2233 --dbms_output.put_line('*l_time_comparison_type='||l_time_comparison_type);
2234
2235 -- Get actual and compareto value from PMV
2236 --dbms_output.put_line('*CALL TO PMV API (One by one)');
2237 Get_Actual_Value_From_PMV(
2238 p_kpi_info_rec => p_kpi_info_tbl(i),
2239 p_user_id => p_user_id,
2240 p_responsibility_id => p_responsibility_id,
2241 p_dimension_levels => l_default_dimension_levels,
2242 p_time_level_from => l_default_time_level_from,
2243 p_time_level_to => l_default_time_level_to,
2244 p_time_comparison_type => l_time_comparison_type,
2245 p_viewby_level => l_viewby_level,
2246 x_actual_value => l_actual_value,
2247 x_compareto_value => l_compareto_value,
2248 x_return_status => l_return_status,
2249 x_msg_count => l_msg_count,
2250 x_msg_data => l_msg_data
2251 );
2252
2253 --dbms_output.put_line('*l_actual_value='||l_actual_value);
2254 --dbms_output.put_line('*l_compareto_value='||l_compareto_value);
2255 --dbms_output.put_line('*l_return_status='||l_return_status);
2256 --dbms_output.put_line('*l_msg_count='||l_msg_count);
2257 --dbms_output.put_line('*l_msg_data='||l_msg_data);
2258
2259 IF l_time_comparison_type IS NULL THEN
2260 -- Bring the target from PMF
2261 l_return_status := NULL;
2262 l_msg_count := NULL;
2263 l_msg_data := NULL;
2264
2265 Get_Target_Value_From_PMF(
2266 p_kpi_info_rec => p_kpi_info_tbl(i),
2267 p_user_id => p_user_id,
2268 p_responsibility_id => p_responsibility_id,
2269 p_dimension_levels => l_default_dimension_levels,
2270 p_time_level => l_default_time_level_to,
2271 x_target_value => l_target_value,
2272 x_return_status => l_return_status,
2273 x_msg_count => l_msg_count,
2274 x_msg_data => l_msg_data
2275 );
2276
2277 --dbms_output.put_line('*l_target_value='||l_target_value);
2278 --dbms_output.put_line('*l_return_status='||l_return_status);
2279 --dbms_output.put_line('*l_msg_count='||l_msg_count);
2280 --dbms_output.put_line('*l_msg_data='||l_msg_data);
2281 ELSE
2282 -- Target is the compareto value
2283 l_target_value := l_compareto_value;
2284 END IF; -- if time is comparison
2285
2286 -- Bug#2655393 For target value is None, PMF return l_fnd_miss_num
2287 IF (l_target_value = l_fnd_miss_num ) THEN
2288 l_target_value := NULL;
2289 END IF;
2290
2291 --bug 2677766
2292 IF p_kpi_info_tbl(i).format_id <= 2 THEN --%
2293 IF l_actual_value IS NOT NULL THEN
2294 l_actual_value := l_actual_value/100;
2295 END IF;
2296 IF l_target_value IS NOT NULL THEN
2297 l_target_value := l_target_value/100;
2298 END IF;
2299 END IF;
2300
2301 -- Store actual and target in p_kpi_info_tbl
2302 p_kpi_info_tbl(i).actual_value := l_actual_value;
2303 p_kpi_info_tbl(i).target_value := l_target_value;
2304
2305 END IF;
2306 END IF;
2307 END LOOP;
2308
2309 -- Calculate Kpi data of measures sharing same AK region
2310 IF l_ak_regions.COUNT > 0 THEN
2311 -- Get ranking parameter
2312 BIS_PMV_PORTAL_UTIL_PUB.Get_Ranking_Parameter(
2313 p_page_id => p_page_id
2314 ,p_user_id => p_user_id
2315 ,x_ranking_param => l_ranking_level
2316 ,x_return_Status => l_return_status
2317 ,x_msg_count => l_msg_count
2318 ,x_msg_data => l_msg_data
2319 );
2320 --dbms_output.put_line('*l_ranking_level='||l_ranking_level);
2321 END IF;
2322
2323 FOR j IN 1..l_ak_regions.COUNT LOOP
2324 --dbms_output.put_line('------------------------------------');
2325 --dbms_output.put_line('*l_ak_regions(j)='||l_ak_regions(j));
2326
2327 l_return_status := NULL;
2328 l_msg_count := NULL;
2329 l_msg_data := NULL;
2330
2331 -- Init the array of measure attribute codes
2332 l_measure_attribute_codes.DELETE;
2333 k := 1;
2334 FOR i IN 1..p_kpi_info_tbl.COUNT LOOP
2335 IF (p_kpi_info_tbl(i).dataset_source = 'PMF') AND (p_kpi_info_tbl(i).measure_dbi_flag = 'Y') AND
2336 (p_kpi_info_tbl(i).region_code = l_ak_regions(j)) THEN
2337
2338 IF p_kpi_info_tbl(i).actual_attribute_code IS NOT NULL THEN
2339 l_measure_attribute_codes(k) := p_kpi_info_tbl(i).actual_attribute_code;
2340 k := k + 1;
2341 END IF;
2342
2343 IF p_kpi_info_tbl(i).compareto_attribute_code IS NOT NULL THEN
2344 l_measure_attribute_codes(k) := p_kpi_info_tbl(i).compareto_attribute_code;
2345 k := k + 1;
2346 END IF;
2347 END IF;
2348 END LOOP;
2349
2350 -- p_funtion_name is not requiered to be passed.
2351 -- The logic assures that: there is a page_id, we come from a DBI page
2352 -- with page parameters.
2353 -- No need to pass time parameter
2354 -- No need to pass parameter
2355
2356 IF l_measure_attribute_codes.COUNT > 0 THEN
2357 --dbms_output.put_line('*CALL TO PMV API (several measures)');
2358
2359 BIS_PMV_ACTUAL_PUB.Get_Actual_Value(
2360 p_region_code => l_ak_regions(j)
2361 ,p_user_id => p_user_id
2362 ,p_page_id => p_page_id
2363 ,p_responsibility_id => p_responsibility_id
2364 ,p_time_parameter => l_time_parameter
2365 ,p_parameters => l_parameters
2366 ,p_measure_attribute_codes => l_measure_attribute_codes
2367 ,p_ranking_level => l_ranking_level
2368 ,x_measure_tbl => l_measure_tbl
2369 ,x_return_status => l_return_status
2370 ,x_msg_count => l_msg_count
2371 ,x_msg_data => l_msg_data
2372 );
2373
2374 -- Get the grand total per each measure_attribute_code
2375 -- and stored in the proper place in p_kpi_info_tbl
2376 FOR k IN 1..l_measure_attribute_codes.COUNT LOOP
2377 l_total_actual_value := NULL;
2378 l_sum_actual_value := NULL;
2379
2380 FOR m IN 1..l_measure_tbl.COUNT LOOP
2381 IF l_measure_tbl(m).measure_attribute_code = l_measure_attribute_codes(k) THEN
2382 l_total_actual_value := l_measure_tbl(m).actual_grandtotal_value;
2383 IF l_total_actual_value IS NULL THEN
2384 -- No grand total then we need to calculated
2385 IF l_sum_actual_value IS NULL AND l_measure_tbl(m).actual_value IS NOT NULL THEN
2386 l_sum_actual_value := l_measure_tbl(m).actual_value;
2387 ELSIF l_measure_tbl(m).actual_value IS NOT NULL THEN
2388 l_sum_actual_value := l_sum_actual_value + l_measure_tbl(m).actual_value;
2389 END IF;
2390 ELSE
2391 -- Grand total exist, then we can exit the loop for this
2392 -- measure attribute code
2393 EXIT;
2394 END IF;
2395 END IF;
2396 END LOOP;
2397
2398 IF l_total_actual_value IS NULL THEN
2399 l_total_actual_value := l_sum_actual_value;
2400 END IF;
2401
2402 l_actual_value := l_total_actual_value;
2403
2404 -- Bug#2655393 For target value is None, PMF return l_fnd_miss_num
2405 IF (l_actual_value = l_fnd_miss_num ) THEN
2406 l_actual_value := NULL;
2407 END IF;
2408
2409 -- Stored the actual in the proper kpis in p_kpi_info_tbl
2410 FOR i IN 1..p_kpi_info_tbl.COUNT LOOP
2411 IF (p_kpi_info_tbl(i).dataset_source = 'PMF') AND
2412 (p_kpi_info_tbl(i).measure_dbi_flag = 'Y') AND
2413 (p_kpi_info_tbl(i).region_code = l_ak_regions(j)) THEN
2414
2415 l_f_actual_value := l_actual_value;
2416
2417 IF p_kpi_info_tbl(i).format_id <= 2 THEN --%
2418 IF l_f_actual_value IS NOT NULL THEN
2419 l_f_actual_value := l_f_actual_value/100;
2420 END IF;
2421 END IF;
2422
2423 IF p_kpi_info_tbl(i).actual_attribute_code = l_measure_attribute_codes(k) THEN
2424 p_kpi_info_tbl(i).actual_value := l_f_actual_value;
2425 END IF;
2426
2427 IF p_kpi_info_tbl(i).compareto_attribute_code = l_measure_attribute_codes(k) THEN
2428 p_kpi_info_tbl(i).target_value := l_f_actual_value;
2429 END IF;
2430 END IF;
2431 END LOOP;
2432 END LOOP;
2433 END IF;
2434 END LOOP;
2435
2436 EXCEPTION
2437 WHEN FND_API.G_EXC_ERROR THEN
2438 x_return_status := FND_API.G_RET_STS_ERROR;
2439 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2440 ,p_data => x_msg_data);
2441 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2443 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2444 ,p_data => x_msg_data);
2445 WHEN NO_DATA_FOUND THEN
2446 x_return_status := FND_API.G_RET_STS_ERROR;
2447 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2448 ,p_data => x_msg_data);
2449 WHEN OTHERS THEN
2450 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2451 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2452 ,p_data => x_msg_data);
2453 END Get_Kpis_Data_From_PMF_PMV;
2454
2455 /************************************************************************************
2456 ************************************************************************************/
2457
2458 PROCEDURE Get_Kpi_view_by(
2459 p_kpi_code IN NUMBER,
2460 p_dimset_id IN VARCHAR2,
2461 p_page_parameters IN BSC_BIS_WRAPPER_PUB.Page_Parameter_Rec_Tbl_Type,
2462 x_viewby_level OUT NOCOPY VARCHAR2,
2463 x_return_status OUT NOCOPY VARCHAR2,
2464 x_msg_count OUT NOCOPY NUMBER,
2465 x_msg_data OUT NOCOPY VARCHAR2
2466 ) IS
2467
2468 TYPE RefCurTyp IS REF CURSOR;
2469 cv RefCurTyp;
2470
2471 l_sql VARCHAR2(32000);
2472 l_level_view_by BSC_BIS_WRAPPER_PUB.Dim_level_Rec_Type;
2473
2474 l_dimension_short_name VARCHAR2(2000);
2475 l_level_short_name VARCHAR2(2000);
2476 l_page_parameter_name VARCHAR2(32000);
2477 l_page_parameter BSC_BIS_WRAPPER_PUB.Page_Parameter_Rec_Type;
2478
2479 BEGIN
2480 x_viewby_level := NULL;
2481
2482 -- Get the defualt view by of the Kpi
2483 l_sql := 'SELECT BID.SHORT_NAME DIMENSION_SHORTNAME, '||
2484 ' BKL.LEVEL_SHORTNAME '||
2485 ' FROM BSC_KPI_DIM_LEVELS_VL BKL,'||
2486 ' BIS_LEVELS BIL,'||
2487 ' BIS_DIMENSIONS BID'||
2488 ' WHERE BKL.INDICATOR = :1 AND'||
2489 ' BKL.DIM_SET_ID = :2 AND'||
2490 ' BKL.LEVEL_SOURCE = ''PMF'' AND'||
2491 ' (BKL.DEFAULT_VALUE = ''C'') AND'||
2492 ' BKL.LEVEL_SHORTNAME = BIL.SHORT_NAME AND'||
2493 ' BIL.DIMENSION_ID = BID.DIMENSION_ID'||
2494 ' ORDER BY DIMENSION_SHORTNAME, BKL.DIM_LEVEL_INDEX';
2495
2496 l_level_view_by.dimension_short_name := NULL;
2497 l_level_view_by.level_short_name := NULL;
2498
2499 OPEN cv FOR l_sql USING p_kpi_code, p_dimset_id;
2500 FETCH cv INTO l_level_view_by.dimension_short_name, l_level_view_by.level_short_name;
2501 IF cv%NOTFOUND THEN
2502 l_level_view_by.dimension_short_name := NULL;
2503 l_level_view_by.level_short_name := NULL;
2504 END IF;
2505 CLOSE cv;
2506
2507 IF l_level_view_by.dimension_short_name IS NOT NULL THEN
2508 -- Get other dimensions levels used in the Kpi with the same dimension of the view by level
2509 -- The view by level can be overwritten by one of them if the page parameters match
2510 -- with one of them.
2511 l_sql := 'SELECT BID.SHORT_NAME DIMENSION_SHORTNAME, '||
2512 ' BKL.LEVEL_SHORTNAME'||
2513 ' FROM BSC_KPI_DIM_LEVELS_VL BKL,'||
2514 ' BIS_LEVELS BIL,'||
2515 ' BIS_DIMENSIONS BID'||
2516 ' WHERE BKL.INDICATOR = :1 AND'||
2517 ' BKL.DIM_SET_ID = :2 AND'||
2518 ' BKL.LEVEL_SOURCE = ''PMF'' AND'||
2519 ' BKL.LEVEL_SHORTNAME = BIL.SHORT_NAME AND'||
2520 ' BIL.DIMENSION_ID = BID.DIMENSION_ID AND'||
2521 ' BID.SHORT_NAME = :3';
2522
2523 OPEN cv FOR l_sql USING p_kpi_code, p_dimset_id, l_level_view_by.dimension_short_name;
2524 LOOP
2525 FETCH cv INTO l_dimension_short_name, l_level_short_name;
2526 EXIT WHEN cv%NOTFOUND;
2527
2528 l_page_parameter_name := l_dimension_short_name||'+'||l_level_short_name;
2529 IF Is_Time_Dimension(l_dimension_short_name) THEN
2530 l_page_parameter_name := l_page_parameter_name||'_FROM';
2531 END IF;
2532
2533 Get_Page_Parameter(
2534 p_page_parameters => p_page_parameters,
2535 p_page_parameter_name => l_page_parameter_name,
2536 x_page_parameter => l_page_parameter
2537 );
2538
2539 IF l_page_parameter.parameter_name IS NOT NULL THEN
2540 -- The dimension level is used by the kpi and also is a page parameter
2541 -- also is of the same dimension f the defualt view by of the kpi.
2542 -- It will overwrite the default view by of the kpi.
2543 l_level_view_by.dimension_short_name := l_dimension_short_name;
2544 l_level_view_by.level_short_name := l_level_short_name;
2545 END IF;
2546 END LOOP;
2547 CLOSE cv;
2548 END IF;
2549
2550 x_viewby_level := l_level_view_by.dimension_short_name || '+' || l_level_view_by.level_short_name;
2551 --dbms_output.put_line('*x_viewby_level='||x_viewby_level);
2552
2553 EXCEPTION
2554 WHEN FND_API.G_EXC_ERROR THEN
2555 x_return_status := FND_API.G_RET_STS_ERROR;
2556 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2557 ,p_data => x_msg_data);
2558 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2559 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2560 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2561 ,p_data => x_msg_data);
2562 WHEN NO_DATA_FOUND THEN
2563 x_return_status := FND_API.G_RET_STS_ERROR;
2564 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2565 ,p_data => x_msg_data);
2566 WHEN OTHERS THEN
2567 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2568 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2569 ,p_data => x_msg_data);
2570 END Get_Kpi_view_by;
2571
2572 /************************************************************************************
2573 ***********************************************************************************/
2574
2575 PROCEDURE get_bsc_format_id(
2576 p_measure_shortname IN VARCHAR2
2577 ,x_bsc_format_id OUT NOCOPY NUMBER
2578 )
2579 IS
2580 l_return_status VARCHAR2(32000);
2581 l_msg_count VARCHAR2(32000);
2582 l_msg_data VARCHAR2(32000);
2583 l_measure_name bsc_sys_datasets_tl.name%TYPE;
2584 l_measure_id NUMBER;
2585 l_description bsc_sys_datasets_tl.help%TYPE;
2586 l_dimension1_id NUMBER;
2587 l_dimension2_id NUMBER;
2588 l_dimension3_id NUMBER;
2589 l_dimension4_id NUMBER;
2590 l_dimension5_id NUMBER;
2591 l_dimension6_id NUMBER;
2592 l_dimension7_id NUMBER;
2593 l_unit_of_measure_class VARCHAR2(10);
2594 l_actual_data_source_type VARCHAR2(30);
2595 l_actual_data_source VARCHAR2(240);
2596 l_comparison_source VARCHAR2(240);
2597 l_increase_in_measure VARCHAR2(1);
2598 l_region_code VARCHAR2(240);
2599 l_attribute_code VARCHAR2(240);
2600 l_function_name VARCHAR2(240);
2601 l_measure_short_name VARCHAR2(240);
2602 l_display_format VARCHAR2(240);
2603 l_display_type VARCHAR2(240);
2604
2605 CURSOR c_akitems IS
2606 SELECT attribute7, attribute14 FROM ak_region_items
2607 WHERE region_code = l_region_code AND attribute_code = l_attribute_code;
2608 BEGIN
2609 BIS_PMF_DEFINER_WRAPPER_PVT.retrieve_performance_measure(
2610 p_measure_short_name => p_measure_shortname
2611 ,x_return_status => l_return_status
2612 ,x_msg_count => l_msg_count
2613 ,x_msg_data => l_msg_data
2614 ,x_measure_id => l_measure_id
2615 ,x_measure_short_name => l_measure_short_name
2616 ,x_measure_name => l_measure_name
2617 ,x_description => l_description
2618 ,x_dimension1_id => l_dimension1_id
2619 ,x_dimension2_id => l_dimension2_id
2620 ,x_dimension3_id => l_dimension3_id
2621 ,x_dimension4_id => l_dimension4_id
2622 ,x_dimension5_id => l_dimension5_id
2623 ,x_dimension6_id => l_dimension6_id
2624 ,x_dimension7_id => l_dimension7_id
2625 ,x_unit_of_measure_class => l_unit_of_measure_class
2626 ,x_actual_data_source_type => l_actual_data_source_type
2627 ,x_actual_data_source => l_actual_data_source
2628 ,x_region_code => l_region_code
2629 ,x_attribute_code => l_attribute_code
2630 ,x_function_name => l_function_name
2631 ,x_comparison_source => l_comparison_source
2632 ,x_increase_in_measure => l_increase_in_measure
2633 );
2634
2635 IF l_region_code IS NOT NULL AND l_attribute_code IS NOT NULL THEN
2636 OPEN c_akitems;
2637 FETCH c_akitems INTO l_display_format, l_display_type;
2638 CLOSE c_akitems;
2639
2640 get_bsc_format_id(
2641 p_display_format => l_display_format
2642 ,p_display_type => l_display_type
2643 ,x_bsc_format_id => x_bsc_format_id
2644 );
2645 ELSE
2646 x_bsc_format_id := NULL;
2647 END IF;
2648
2649 EXCEPTION
2650 WHEN OTHERS THEN
2651 IF (c_akitems%ISOPEN) THEN
2652 CLOSE c_akitems;
2653 END IF;
2654 END get_bsc_format_id;
2655
2656 /*
2657 FORMAT_ID NAME FORMAT
2658 --------- ---------- ---------------
2659 0 FmtPercent #,##0%
2660 1 FmtPercen1 #,##0.0%
2661 2 FmtPercen2 #,##0.00%
2662 5 FmtNumber #,###,##0
2663 6 FmtNumber1 #,###,##0.0
2664 7 FmtNumber2 #,###,##0.00
2665 8 FmtNumber3 #,###,##0.000
2666 */
2667
2668 /************************************************************************************
2669 ***********************************************************************************/
2670
2671 PROCEDURE get_bsc_format_id(
2672 p_display_type IN VARCHAR2
2673 ,p_display_format IN VARCHAR2
2674 ,x_bsc_format_id OUT NOCOPY NUMBER
2675 )
2676 IS
2677 l_num_decimal_places NUMBER;
2678 BEGIN
2679 IF p_display_format IS NULL THEN
2680 IF p_display_type = 'IP' THEN
2681 x_bsc_format_id := 0;
2682 ELSIF p_display_type = 'FP' THEN
2683 x_bsc_format_id := 1;
2684 ELSE
2685 x_bsc_format_id := 5;
2686 END IF;
2687 ELSIF p_display_type = 'IP' OR p_display_type = 'FP' THEN
2688 l_num_decimal_places := get_num_decimal_places(p_display_format);
2689
2690 IF l_num_decimal_places = 0 THEN
2691 x_bsc_format_id := 0;
2692 ELSIF l_num_decimal_places = 1 THEN
2693 x_bsc_format_id := 1;
2694 ELSE
2695 x_bsc_format_id := 2;
2696 END IF;
2697 ELSE
2698 l_num_decimal_places := get_num_decimal_places(p_display_format);
2699
2700 IF l_num_decimal_places = 0 THEN
2701 x_bsc_format_id := 5;
2702 ELSIF l_num_decimal_places = 1 THEN
2703 x_bsc_format_id := 6;
2704 ELSIF l_num_decimal_places = 2 THEN
2705 x_bsc_format_id := 7;
2706 ELSE
2707 x_bsc_format_id := 8;
2708 END IF;
2709 END IF;
2710 END;
2711
2712 /************************************************************************************
2713 ***********************************************************************************/
2714
2715 FUNCTION get_num_decimal_places(
2716 p_display_format IN VARCHAR2
2717 ) RETURN NUMBER
2718 IS
2719 l_display_format VARCHAR2(200);
2720 l_fraction_part VARCHAR2(100);
2721 l_position NUMBER;
2722 BEGIN
2723 l_display_format := trim(replace(p_display_format, 'D', '.'));
2724 l_position := instr(l_display_format, '.');
2725
2726 IF (l_position > 0) THEN
2727 l_fraction_part := substr(l_display_format, l_position + 1);
2728
2729 IF l_fraction_part IS NOT NULL THEN
2730 RETURN length(l_fraction_part);
2731 ELSE
2732 RETURN 0;
2733 END IF;
2734 ELSE
2735 RETURN 0;
2736 END IF;
2737 END get_num_decimal_places;
2738
2739 /************************************************************************************
2740 ***********************************************************************************/
2741
2742 FUNCTION Item_Belong_To_Array_Varchar2(
2743 p_item IN VARCHAR2,
2744 p_array IN BSC_BIS_WRAPPER_PUB.t_array_of_varchar2,
2745 p_num_items IN NUMBER
2746 ) RETURN BOOLEAN IS
2747
2748 h_i NUMBER;
2749
2750 BEGIN
2751 FOR h_i IN 1 .. p_num_items LOOP
2752 IF p_array(h_i) = p_item THEN
2753 RETURN TRUE;
2754 END IF;
2755 END LOOP;
2756
2757 RETURN FALSE;
2758
2759 END Item_Belong_To_Array_Varchar2;
2760
2761 /************************************************************************************
2762 / This API is used in Enh 3579794, for saving actual and buget(compareTo)
2763 / actual and budget values are obtained using BIS JAVA APIS.
2764 ************************************************************************************/
2765 PROCEDURE Post_Measure_Data(
2766 p_user_id IN VARCHAR2,
2767 p_responsibility_id IN VARCHAR2,
2768 p_caching_key IN VARCHAR2,
2769 p_kpi_info_tbl IN BSC_BIS_WRAPPER_PUB.Kpi_Info_Rec_Tbl_Type,
2770 x_return_status OUT NOCOPY VARCHAR2,
2771 x_msg_count OUT NOCOPY NUMBER,
2772 x_msg_data OUT NOCOPY VARCHAR2
2773 ) IS
2774
2775 TYPE tCursor IS REF CURSOR;
2776
2777 cv tCursor;
2778 l_sql VARCHAR2(32000);
2779
2780 cv_caching tCursor;
2781 l_caching_sql VARCHAR2(32000);
2782 l_insert_sql VARCHAR2(32000);
2783 l_update_sql VARCHAR2(32000);
2784
2785 l_caching_key VARCHAR2(200);
2786 i NUMBER := 1;
2787
2788 l_return_status VARCHAR2(2000);
2789 l_msg_count NUMBER;
2790 l_msg_data VARCHAR2(32000);
2791
2792 l_db_user_id NUMBER;
2793 l_sysdate DATE := SYSDATE;
2794
2795 BEGIN
2796 FND_MSG_PUB.initialize;
2797
2798 -- Get the database user id
2799 -- Ref: bug#3482442 In corner cases this query can return more than one
2800 -- row and it will fail. AUDSID is not PK. After meeting with
2801 -- Vinod and Kris and Venu, we should use FNG_GLOBAL.user_id
2802 l_db_user_id := BSC_APPS.fnd_global_user_id;
2803
2804 --dbms_output.put_line('*l_db_user_id='||l_db_user_id);
2805
2806 -- Populate BSC_BIS_MEASURES_DATA for the default analysis combination
2807 -- of each kpi which user has access and belongs to the given tab.
2808
2809 l_sql := 'SELECT tk.indicator'||
2810 ' FROM bsc_tab_indicators tk, bsc_user_kpi_access ka'||
2811 ' WHERE tk.tab_id = :1 AND tk.indicator = ka.indicator AND ka.responsibility_id = :2';
2812
2813 l_caching_sql := 'SELECT caching_key'||
2814 ' FROM bsc_bis_measures_data'||
2815 ' WHERE user_id = :1 AND responsibility_id = :2 AND indicator = :3 AND'||
2816 ' analysis_option0 = :4 AND analysis_option1 = :5 AND analysis_option2 = :6 AND'||
2817 ' series_id = :7';
2818
2819 l_update_sql := 'UPDATE bsc_bis_measures_data'||
2820 ' SET actual_data = :a, budget_data = :b, caching_key = :c,'||
2821 ' last_updated_by = :d, last_update_date = :e'||
2822 ' WHERE user_id = :f AND responsibility_id = :g AND indicator = :h AND'||
2823 ' analysis_option0 = :i AND analysis_option1 = :j AND analysis_option2 = :k AND'||
2824 ' series_id = :l';
2825
2826 l_insert_sql := 'INSERT INTO bsc_bis_measures_data (user_id, responsibility_id, indicator,'||
2827 ' analysis_option0, analysis_option1, analysis_option2, series_id,'||
2828 ' caching_key, actual_data, budget_data, created_by, creation_date,'||
2829 ' last_updated_by, last_update_date, last_update_login)'||
2830 ' VALUES (:a, :b, :c, :d, :e, :f, :g, :h, :i, :j, :k, :l, :m, :n, :o)';
2831
2832 -- Insert/Update
2833 FOR i IN 1..p_kpi_info_tbl.COUNT LOOP
2834
2835 IF (p_kpi_info_tbl(i).kpi_code IS NOT NULL) AND (p_kpi_info_tbl(i).analysis_option0 IS NOT NULL) AND
2836 (p_kpi_info_tbl(i).analysis_option1 IS NOT NULL) AND (p_kpi_info_tbl(i).analysis_option2 IS NOT NULL) AND
2837 (p_kpi_info_tbl(i).series_id IS NOT NULL) THEN
2838
2839 --Delete from BSC_BIS_MEASURES_DATA kpis that belong to the given tab, which has
2840 --been deleted, has been changed or user has not access.
2841
2842 DELETE FROM
2843 bsc_bis_measures_data m
2844 WHERE
2845 m.user_id = p_user_id AND
2846 m.responsibility_id = p_responsibility_id AND
2847 m.indicator = p_kpi_info_tbl(i).kpi_code
2848 AND (
2849 m.indicator NOT IN (
2850 SELECT
2851 a.indicator
2852 FROM
2853 bsc_user_kpi_access a
2854 WHERE
2855 a.responsibility_id = p_responsibility_id
2856 ) OR
2857 m.indicator = (
2858 SELECT
2859 k.indicator
2860 FROM
2861 bsc_kpis_b k
2862 WHERE
2863 k.indicator = m.indicator AND (
2864 k.prototype_flag = 2 OR
2865 k.last_update_date > m.last_update_date)
2866 ));
2867
2868 --dbms_output.put_line('*after delete');
2869
2870 -- Get the caching key
2871 l_caching_key := NULL;
2872 OPEN cv_caching FOR l_caching_sql USING p_user_id, p_responsibility_id, p_kpi_info_tbl(i).kpi_code,
2873 p_kpi_info_tbl(i).analysis_option0, p_kpi_info_tbl(i).analysis_option1,
2874 p_kpi_info_tbl(i).analysis_option2, p_kpi_info_tbl(i).series_id;
2875 FETCH cv_caching INTO l_caching_key;
2876 IF cv_caching%FOUND THEN
2877 --dbms_output.put_line('*l_caching_key='||l_caching_key);
2878 -- The record exists --> Calculate data and update if caching key is different
2879 -- Update actual and target in BSC_BIS_MEASURES_DATA
2880 EXECUTE IMMEDIATE l_update_sql USING p_kpi_info_tbl(i).actual_value,
2881 p_kpi_info_tbl(i).target_value, p_caching_key, l_db_user_id,
2882 l_sysdate, p_user_id, p_responsibility_id, p_kpi_info_tbl(i).kpi_code,
2883 p_kpi_info_tbl(i).analysis_option0, p_kpi_info_tbl(i).analysis_option1,
2884 p_kpi_info_tbl(i).analysis_option2, p_kpi_info_tbl(i).series_id;
2885
2886 --dbms_output.put_line('*update executed');
2887
2888 ELSE
2889 -- The record does not exists --> Calculate data and Insert
2890 -- Insert actual and target in BSC_BIS_MEASURES_DATA
2891 EXECUTE IMMEDIATE l_insert_sql USING p_user_id, p_responsibility_id,
2892 p_kpi_info_tbl(i).kpi_code, p_kpi_info_tbl(i).analysis_option0,
2893 p_kpi_info_tbl(i).analysis_option1, p_kpi_info_tbl(i).analysis_option2,
2894 p_kpi_info_tbl(i).series_id, p_caching_key,
2895 p_kpi_info_tbl(i).actual_value, p_kpi_info_tbl(i).target_value,
2896 l_db_user_id, l_sysdate, l_db_user_id, l_sysdate, l_db_user_id;
2897
2898 --dbms_output.put_line('*insert executed');
2899
2900 END IF;
2901 CLOSE cv_caching;
2902 END IF;
2903
2904 --dbms_output.put_line('--------------------------------------------');
2905 --dbms_output.put_line('l_kpi_info_tbl('||i||').kpi_code='||l_kpi_info_tbl(i).kpi_code);
2906 --dbms_output.put_line('l_kpi_info_tbl('||i||').analysis_option0='||l_kpi_info_tbl(i).analysis_option0);
2907 --dbms_output.put_line('l_kpi_info_tbl('||i||').analysis_option1='||l_kpi_info_tbl(i).analysis_option1);
2908 --dbms_output.put_line('l_kpi_info_tbl('||i||').analysis_option2='||l_kpi_info_tbl(i).analysis_option2);
2909 --dbms_output.put_line('l_kpi_info_tbl('||i||').series_id='||l_kpi_info_tbl(i).series_id);
2910 --dbms_output.put_line('l_kpi_info_tbl('||i||').dataset_id='||l_kpi_info_tbl(i).dataset_id);
2911 --dbms_output.put_line('l_kpi_info_tbl('||i||').dataset_source='||l_kpi_info_tbl(i).dataset_source);
2912 --dbms_output.put_line('l_kpi_info_tbl('||i||').measure_short_name='||l_kpi_info_tbl(i).measure_short_name);
2913 --dbms_output.put_line('l_kpi_info_tbl('||i||').measure_dbi_flag='||l_kpi_info_tbl(i).measure_dbi_flag);
2914 --dbms_output.put_line('l_kpi_info_tbl('||i||').measure_id='||l_kpi_info_tbl(i).measure_id);
2915 --dbms_output.put_line('l_kpi_info_tbl('||i||').region_code='||l_kpi_info_tbl(i).region_code);
2916 --dbms_output.put_line('l_kpi_info_tbl('||i||').function_name='||l_kpi_info_tbl(i).function_name);
2917 --dbms_output.put_line('l_kpi_info_tbl('||i||').actual_attribute_code='||l_kpi_info_tbl(i).actual_attribute_code);
2918 --dbms_output.put_line('l_kpi_info_tbl('||i||').compareto_attribute_code='||l_kpi_info_tbl(i).compareto_attribute_code);
2919 --dbms_output.put_line('l_kpi_info_tbl('||i||').format_id='||l_kpi_info_tbl(i).format_id);
2920 --dbms_output.put_line('l_kpi_info_tbl('||i||').dimset_id='||l_kpi_info_tbl(i).dimset_id);
2921 --dbms_output.put_line('l_kpi_info_tbl('||i||').dim1_short_name='||l_kpi_info_tbl(i).dim1_short_name);
2922 --dbms_output.put_line('l_kpi_info_tbl('||i||').dim2_short_name='||l_kpi_info_tbl(i).dim2_short_name);
2923 --dbms_output.put_line('l_kpi_info_tbl('||i||').dim3_short_name='||l_kpi_info_tbl(i).dim3_short_name);
2924 --dbms_output.put_line('l_kpi_info_tbl('||i||').dim4_short_name='||l_kpi_info_tbl(i).dim4_short_name);
2925 --dbms_output.put_line('l_kpi_info_tbl('||i||').dim5_short_name='||l_kpi_info_tbl(i).dim5_short_name);
2926 --dbms_output.put_line('l_kpi_info_tbl('||i||').dim6_short_name='||l_kpi_info_tbl(i).dim6_short_name);
2927 --dbms_output.put_line('l_kpi_info_tbl('||i||').dim7_short_name='||l_kpi_info_tbl(i).dim7_short_name);
2928 --dbms_output.put_line('l_kpi_info_tbl('||i||').actual_value='||l_kpi_info_tbl(i).actual_value);
2929 --dbms_output.put_line('l_kpi_info_tbl('||i||').target_value='||l_kpi_info_tbl(i).target_value);
2930 --dbms_output.put_line('l_kpi_info_tbl('||i||').insert_update_flag='||l_kpi_info_tbl(i).insert_update_flag);
2931 END LOOP;
2932
2933 COMMIT;
2934
2935 EXCEPTION
2936 WHEN FND_API.G_EXC_ERROR THEN
2937 ROLLBACK;
2938 x_return_status := FND_API.G_RET_STS_ERROR;
2939 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2940 ,p_data => x_msg_data);
2941 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2942 ROLLBACK;
2943 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2944 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2945 ,p_data => x_msg_data);
2946 WHEN NO_DATA_FOUND THEN
2947 ROLLBACK;
2948 x_return_status := FND_API.G_RET_STS_ERROR;
2949 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2950 ,p_data => x_msg_data);
2951 WHEN OTHERS THEN
2952 ROLLBACK;
2953 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2954 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2955 ,p_data => x_msg_data);
2956 --dbms_output.put_line('*sqlerrm='||sqlerrm);
2957 END Post_Measure_Data;
2958
2959 /************************************************************************************
2960 / Wrapper API is used in Enh 3579794, for saving actual and buget(compareTo)
2961 / actual and budget values are obtained using BIS JAVA APIS.
2962 ************************************************************************************/
2963 PROCEDURE Populate_Measure_Data(
2964 p_user_id IN VARCHAR2,
2965 p_responsibility_id IN VARCHAR2,
2966 p_caching_key IN VARCHAR2,
2967 p_kpi_code IN NUMBER,
2968 p_analysis_option0 IN NUMBER,
2969 p_analysis_option1 IN NUMBER,
2970 p_analysis_option2 IN NUMBER,
2971 p_series_id IN NUMBER,
2972 p_actual_value IN VARCHAR2,
2973 p_target_value IN VARCHAR2,
2974 x_return_status OUT NOCOPY VARCHAR2,
2975 x_msg_count OUT NOCOPY NUMBER,
2976 x_msg_data OUT NOCOPY VARCHAR2
2977 ) IS
2978
2979 l_return_status VARCHAR2(2000);
2980 l_msg_count NUMBER;
2981 l_msg_data VARCHAR2(32000);
2982 l_kpi_info_tbl BSC_BIS_WRAPPER_PUB.Kpi_Info_Rec_Tbl_Type;
2983
2984 BEGIN
2985 FND_MSG_PUB.initialize;
2986
2987 l_kpi_info_tbl(1).KPI_CODE := p_kpi_code;
2988 l_kpi_info_tbl(1).ANALYSIS_OPTION0 := p_analysis_option0;
2989 l_kpi_info_tbl(1).ANALYSIS_OPTION1 := p_analysis_option1;
2990 l_kpi_info_tbl(1).ANALYSIS_OPTION2 := p_analysis_option2;
2991 l_kpi_info_tbl(1).SERIES_ID := p_series_id;
2992 l_kpi_info_tbl(1).ACTUAL_VALUE := p_actual_value;
2993 l_kpi_info_tbl(1).TARGET_VALUE := p_target_value;
2994
2995 Post_Measure_Data(p_user_id,
2996 p_responsibility_id,
2997 p_caching_key,
2998 l_kpi_info_tbl,
2999 x_return_status,
3000 x_msg_count,
3001 x_msg_data);
3002 COMMIT;
3003
3004 EXCEPTION
3005 WHEN FND_API.G_EXC_ERROR THEN
3006 ROLLBACK;
3007 x_return_status := FND_API.G_RET_STS_ERROR;
3008 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
3009 ,p_data => x_msg_data);
3010 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3011 ROLLBACK;
3012 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3013 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
3014 ,p_data => x_msg_data);
3015 WHEN NO_DATA_FOUND THEN
3016 ROLLBACK;
3017 x_return_status := FND_API.G_RET_STS_ERROR;
3018 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
3019 ,p_data => x_msg_data);
3020 WHEN OTHERS THEN
3021 ROLLBACK;
3022 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3023 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
3024 ,p_data => x_msg_data);
3025 --dbms_output.put_line('*sqlerrm='||sqlerrm);
3026 END Populate_Measure_Data;
3027
3028 END BSC_BIS_WRAPPER_PVT;