1 PACKAGE BODY BSC_CAUSE_EFFECT_UI_WRAPPER AS
2 /* $Header: BSCCAEWB.pls 115.11 2003/02/12 14:25:34 adrao ship $ */
3
4 G_PKG_NAME varchar2(30) := 'BSC_CAUSE_EFFECT_UI_WRAPPER';
5
6 G_REPORT_NOT_DEFINED varchar2(30) := 'REPORT_NOT_DEFINED';
7 G_USER_NOT_AUTHORIZED_REPORT varchar2(30) := 'USER_NOT_AUTHORIZED_REPORT';
8 G_REPORT_LINK_DISABLED varchar2(30) := 'REPORT_LINK_DISABLED';
9
10
11 /************************************************************************************
12 ************************************************************************************/
13
14 PROCEDURE Apply_Cause_Effect_Rels(
15 p_indicator IN NUMBER
16 ,p_level IN VARCHAR2
17 ,p_causes_lst IN VARCHAR2
18 ,p_effects_lst IN VARCHAR2
19 ,x_return_status OUT NOCOPY VARCHAR2
20 ,x_msg_count OUT NOCOPY NUMBER
21 ,x_msg_data OUT NOCOPY VARCHAR2
22 ) IS
23
24 l_Bsc_Cause_Effect_Rel_Rec BSC_CAUSE_EFFECT_REL_PUB.Bsc_Cause_Effect_Rel_Rec;
25 l_commit VARCHAR2(10);
26
27 l_causes_lst VARCHAR2(32700);
28 l_causes t_array_of_varchar2;
29 l_num_causes NUMBER;
30
31 l_effects_lst VARCHAR2(32700);
32 l_effects t_array_of_varchar2;
33 l_num_effects NUMBER;
34
35 l_invalid_indicators VARCHAR2(32700);
36 l_i NUMBER;
37
38 l_cause_indicator NUMBER;
39 l_effect_indicator NUMBER;
40
41 BEGIN
42
43 FND_MSG_PUB.Initialize;
44
45 -- Get and array with the causes
46
47 l_num_causes := 0;
48 IF p_causes_lst IS NOT NULL THEN
49 l_causes_lst := p_causes_lst;
50
51 -- take off the trailing ;
52 IF SUBSTR(l_causes_lst, -1, 1) = ';' THEN
53 l_causes_lst := SUBSTR(l_causes_lst, 1, LENGTH(l_causes_lst)-1);
54 END IF;
55
56 l_num_causes := Decompose_Varchar2_List(l_causes_lst, l_causes, ';');
57 END IF;
58
59
60 -- Get and array with the effects
61 l_num_effects := 0;
62 IF p_effects_lst IS NOT NULL THEN
63 l_effects_lst := p_effects_lst;
64
65 -- take off the trailing ;
66 IF SUBSTR(l_effects_lst, -1, 1) = ';' THEN
67 l_effects_lst := SUBSTR(l_effects_lst, 1, LENGTH(l_effects_lst)-1);
68 END IF;
69
70 l_num_effects := Decompose_Varchar2_List(l_effects_lst, l_effects, ';');
71 END IF;
72
73
74 -- Import PMF measures into datasets if they do not exists
75 IF p_level = 'DATASET' THEN
76 FOR l_i IN 1..l_num_causes LOOP
77 IF NOT Exists_Measure_Dataset(l_causes(l_i)) THEN
78 BSC_PMF_UI_WRAPPER.Create_Measure(
79 p_short_name => l_causes(l_i)
80 ,x_return_status => x_return_status
81 ,x_msg_count => x_msg_count
82 ,x_msg_data => x_msg_data);
83 END IF;
84 END LOOP;
85
86 FOR l_i IN 1..l_num_effects LOOP
87 IF NOT Exists_Measure_Dataset(l_effects(l_i)) THEN
88 BSC_PMF_UI_WRAPPER.Create_Measure(
89 p_short_name => l_effects(l_i)
90 ,x_return_status => x_return_status
91 ,x_msg_count => x_msg_count
92 ,x_msg_data => x_msg_data);
93 END IF;
94 END LOOP;
95 END IF;
96
97
98 -- Validate that there are no indicators used as cause and effect at the same time
99 l_invalid_indicators := NULL;
100 FOR l_i IN 1..l_num_effects LOOP
101 IF Item_Belong_To_Array_Varchar2(l_effects(l_i), l_causes, l_num_causes) THEN
102 IF l_invalid_indicators IS NOT NULL THEN
103 l_invalid_indicators := l_invalid_indicators||', ';
104 END IF;
105 l_invalid_indicators := l_invalid_indicators||Get_Indicator_Name(l_effects(l_i), p_level);
106 END IF;
107 END LOOP;
108
109
110 IF l_invalid_indicators IS NOT NULL THEN
111 FND_MESSAGE.SET_NAME('BSC','BSC_CAE_USED_AT_SAME_TIME');
112 FND_MESSAGE.SET_TOKEN('LIST', l_invalid_indicators);
113 FND_MSG_PUB.ADD;
114 RAISE FND_API.G_EXC_ERROR;
115 END IF;
116
117
118 -- Delete existing cause and effect relations for this indicator
119 l_commit := FND_API.G_FALSE;
120 BSC_CAUSE_EFFECT_REL_PUB.Delete_All_Cause_Effect_Rels(l_commit
121 ,p_indicator
122 ,p_level
123 ,x_return_status
124 ,x_msg_count
125 ,x_msg_data);
126
127
128 -- Save causes
129 FOR l_i IN 1..l_num_causes LOOP
130 IF p_level = 'DATASET' THEN
131 l_cause_indicator := Get_Dataset_Id(l_causes(l_i));
132 ELSE
133 l_cause_indicator := TO_NUMBER(l_causes(l_i));
134 END IF;
135
136 l_Bsc_Cause_Effect_Rel_Rec.Cause_Indicator := l_cause_indicator;
137 l_Bsc_Cause_Effect_Rel_Rec.Cause_Level := p_level;
138 l_Bsc_Cause_Effect_Rel_Rec.Effect_Indicator := p_indicator;
139 l_Bsc_Cause_Effect_Rel_Rec.Effect_Level := p_level;
140
141 BSC_CAUSE_EFFECT_REL_PUB.Create_Cause_Effect_Rel(l_commit
142 ,l_Bsc_Cause_Effect_Rel_Rec
143 ,x_return_status
144 ,x_msg_count
145 ,x_msg_data);
146 END LOOP;
147
148
149 --Save effects
150 FOR l_i IN 1..l_num_effects LOOP
151 IF p_level = 'DATASET' THEN
152 l_effect_indicator := Get_Dataset_Id(l_effects(l_i));
153 ELSE
154 l_effect_indicator := TO_NUMBER(l_effects(l_i));
155 END IF;
156
157 l_Bsc_Cause_Effect_Rel_Rec.Cause_Indicator := p_indicator;
158 l_Bsc_Cause_Effect_Rel_Rec.Cause_Level := p_level;
159 l_Bsc_Cause_Effect_Rel_Rec.Effect_Indicator := l_effect_indicator;
160 l_Bsc_Cause_Effect_Rel_Rec.Effect_Level := p_level;
161
162 BSC_CAUSE_EFFECT_REL_PUB.Create_Cause_Effect_Rel(l_commit
163 ,l_Bsc_Cause_Effect_Rel_Rec
164 ,x_return_status
165 ,x_msg_count
166 ,x_msg_data);
167 END LOOP;
168
169
170 COMMIT;
171
172
173 EXCEPTION
174 WHEN FND_API.G_EXC_ERROR THEN
175 rollback;
176 x_return_status := FND_API.G_RET_STS_ERROR;
177 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
178 ,p_data => x_msg_data);
179 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
180 rollback;
181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
182 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
183 ,p_data => x_msg_data);
184 WHEN NO_DATA_FOUND THEN
185 rollback;
186 x_return_status := FND_API.G_RET_STS_ERROR;
187 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
188 ,p_data => x_msg_data);
189 WHEN OTHERS THEN
190 rollback;
191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
192 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
193 ,p_data => x_msg_data);
194
195 END Apply_Cause_Effect_Rels;
196
197 /************************************************************************************
198 ************************************************************************************/
199
200 FUNCTION Exists_Measure_Dataset(
201 p_measure_short_name IN VARCHAR2
202 ) RETURN BOOLEAN IS
203 l_count NUMBER := 0;
204 BEGIN
205 SELECT count(*)
206 INTO l_count
207 FROM bsc_sys_datasets_b
208 WHERE measure_id1 = (
209 SELECT measure_id
210 FROM bsc_sys_measures
211 WHERE short_name = p_measure_short_name);
212
213 IF l_count > 0 THEN
214 RETURN TRUE;
215 ELSE
216 RETURN FALSE;
217 END IF;
218
219 END Exists_Measure_Dataset;
220
221 /************************************************************************************
222 ************************************************************************************/
223
224 FUNCTION Get_Dataset_Id(
225 p_measure_short_name IN VARCHAR2
226 ) RETURN NUMBER IS
227 l_dataset_id NUMBER;
228 BEGIN
229 SELECT dataset_id INTO l_dataset_id
230 FROM bsc_sys_datasets_b
231 WHERE measure_id1 = (
232 SELECT measure_id
233 FROM bsc_sys_measures
234 WHERE short_name = p_measure_short_name);
235
236 RETURN l_dataset_id;
237 END Get_Dataset_Id;
238
239 /************************************************************************************
240 ************************************************************************************/
241
242 FUNCTION Decompose_Numeric_List(
243 x_string IN VARCHAR2,
244 x_number_array IN OUT NOCOPY t_array_of_number,
245 x_separator IN VARCHAR2
246 ) RETURN NUMBER IS
247
248 h_num_items NUMBER := 0;
249
250 h_sub_string VARCHAR2(32700);
251 h_position NUMBER;
252
253 BEGIN
254
255 IF x_string IS NOT NULL THEN
256 h_sub_string := x_string;
257 h_position := INSTR(h_sub_string, x_separator);
258
259 WHILE h_position <> 0 LOOP
260 h_num_items := h_num_items + 1;
261 x_number_array(h_num_items) := TO_NUMBER(RTRIM(LTRIM(SUBSTR(h_sub_string, 1, h_position - 1))));
262
263 h_sub_string := SUBSTR(h_sub_string, h_position + 1);
264 h_position := INSTR(h_sub_string, x_separator);
265 END LOOP;
266
267 h_num_items := h_num_items + 1;
268 x_number_array(h_num_items) := TO_NUMBER(RTRIM(LTRIM(h_sub_string)));
269
270 END IF;
271
272 RETURN h_num_items;
273
274 END Decompose_Numeric_List;
275
276 /************************************************************************************
277 ************************************************************************************/
278
279 FUNCTION Decompose_Varchar2_List(
280 x_string IN VARCHAR2,
281 x_array IN OUT NOCOPY t_array_of_varchar2,
282 x_separator IN VARCHAR2
283 ) RETURN NUMBER IS
284
285 h_num_items NUMBER := 0;
286
287 h_sub_string VARCHAR2(32700);
288 h_position NUMBER;
289
290 BEGIN
291
292 IF x_string IS NOT NULL THEN
293 h_sub_string := x_string;
294 h_position := INSTR(h_sub_string, x_separator);
295
296 WHILE h_position <> 0 LOOP
297 h_num_items := h_num_items + 1;
298 x_array(h_num_items) := RTRIM(LTRIM(SUBSTR(h_sub_string, 1, h_position - 1)));
299
300 h_sub_string := SUBSTR(h_sub_string, h_position + 1);
301 h_position := INSTR(h_sub_string, x_separator);
302 END LOOP;
303
304 h_num_items := h_num_items + 1;
305 x_array(h_num_items) := RTRIM(LTRIM(h_sub_string));
306
307 END IF;
308
309 RETURN h_num_items;
310
311 END Decompose_Varchar2_List;
312
313 /************************************************************************************
314 ************************************************************************************/
315
316 FUNCTION Item_Belong_To_Array_Number(
317 x_item IN NUMBER,
318 x_array IN t_array_of_number,
319 x_num_items IN NUMBER
320 ) RETURN BOOLEAN IS
321
322 h_i NUMBER;
323
324 BEGIN
325 FOR h_i IN 1 .. x_num_items LOOP
326 IF x_array(h_i) = x_item THEN
327 RETURN TRUE;
328 END IF;
329 END LOOP;
330
331 RETURN FALSE;
332
333 END Item_Belong_To_Array_Number;
334
335 /************************************************************************************
336 ************************************************************************************/
337
338 FUNCTION Item_Belong_To_Array_Varchar2(
339 x_item IN VARCHAR2,
340 x_array IN t_array_of_varchar2,
341 x_num_items IN NUMBER
342 ) RETURN BOOLEAN IS
343
344 h_i NUMBER;
345
346 BEGIN
347 FOR h_i IN 1 .. x_num_items LOOP
348 IF x_array(h_i) = x_item THEN
349 RETURN TRUE;
350 END IF;
351 END LOOP;
352
353 RETURN FALSE;
354
355 END Item_Belong_To_Array_Varchar2;
356
357 /************************************************************************************
358 ************************************************************************************/
359
360 FUNCTION Get_Indicator_Name(
361 p_indicator IN VARCHAR2,
362 p_level IN VARCHAR2
363 ) RETURN VARCHAR2 IS
364
365 l_sql VARCHAR2(32000);
366 TYPE CursorType IS REF CURSOR;
367 l_cursor CursorType;
368 l_name VARCHAR2(200);
369
370 BEGIN
371 l_name := NULL;
372
373 IF p_level = 'KPI' THEN
374 l_sql := 'SELECT name FROM bsc_kpis_vl WHERE indicator = :i';
375 ELSE
376 l_sql := 'SELECT name FROM bsc_sys_datasets_vl'||
377 ' WHERE measure_id1 = (SELECT measure_id FROM bsc_sys_measures'||
378 ' WHERE short_name = :i)';
379 END IF;
380
381 OPEN l_cursor FOR l_sql USING p_indicator;
382 FETCH l_cursor INTO l_name;
383 CLOSE l_cursor;
384
385 RETURN l_name;
386
387 END Get_Indicator_Name;
388
389 /************************************************************************************
390 ************************************************************************************/
391
392 PROCEDURE Get_Indicator_Link(
393 p_user_id IN NUMBER
394 ,p_indicator IN NUMBER
395 ,p_level IN VARCHAR2
396 ,p_page_id IN VARCHAR2 DEFAULT NULL
397 ,p_page_dim_params IN VARCHAR2 DEFAULT NULL
398 ,p_page_time_param IN VARCHAR2 DEFAULT NULL
399 ,p_view_by_param IN VARCHAR2 DEFAULT NULL
400 ,x_indicator_link OUT NOCOPY VARCHAR2
401 ,x_return_status OUT NOCOPY VARCHAR2
402 ,x_msg_count OUT NOCOPY NUMBER
403 ,x_msg_data OUT NOCOPY VARCHAR2
404 ) IS
405
406 l_measure_short_name VARCHAR2(30);
407
408 l_measure_rec BIS_MEASURE_PUB.Measure_rec_type;
409 x_measure_rec BIS_MEASURE_PUB.Measure_rec_type;
410 l_region_code VARCHAR2(240);
411 l_function_name VARCHAR2(240);
412
413 l_page_time_param time_parameter_rec_type;
414 l_page_dim_params dim_parameter_tbl_type;
415
416 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
417 l_sql VARCHAR2(32000);
418 TYPE CursorType IS REF CURSOR;
419 l_cursor CursorType;
420
421 l_function_url_param VARCHAR2(32000);
422 l_dim_url_params VARCHAR2(32000);
423 l_time_url_param VARCHAR2(32000);
424 l_viewby_url_param VARCHAR2(32000);
425 l_position NUMBER;
426 l_rep_dim VARCHAR2(32000);
427 l_rep_dimension VARCHAR2(32000);
428 l_rep_dimension_level VARCHAR2(32000);
429 l_index NUMBER;
430 l_ranking_parameter VARCHAR2(32000);
431
432 BEGIN
433
434 FND_MSG_PUB.Initialize;
435
436 x_indicator_link := NULL;
437
438 IF p_level = 'DATASET' THEN
439 -- For now we suppose that id the measure is at dataset level, it is a PMF measure
440
441 -- Get measure short name
442 SELECT short_name INTO l_measure_short_name
443 FROM bsc_sys_measures
444 WHERE measure_id = (SELECT measure_id1 FROM bsc_sys_datasets_b WHERE dataset_id = p_indicator);
445
446 -- Get the function name and region of the report of the measure
447 l_measure_rec.Measure_Short_Name := l_measure_short_name;
448 BIS_MEASURE_PUB.Retrieve_Measure(
449 p_api_version => 1.0
450 , p_Measure_Rec => l_measure_rec
451 , p_all_info => fnd_api.G_TRUE
452 , x_Measure_Rec => x_measure_rec
453 , x_return_status => x_return_status
454 , x_error_Tbl => l_error_tbl);
455
456 IF (NVL(x_measure_rec.Enable_Link, 'N') <> 'Y') THEN
457 -- Link is disbled
458 x_indicator_link := G_REPORT_LINK_DISABLED;
459 RETURN;
460 END IF;
461
462
463 l_function_name := x_measure_rec.Function_Name;
464 l_region_code := SUBSTR(x_measure_rec.Actual_Data_Source,1, (INSTR(x_measure_rec.Actual_Data_Source,'.',1,1)-1));
465
466 IF l_function_name IS NULL THEN
467 -- We need the function name to get the link of the measure
468 x_indicator_link := G_REPORT_NOT_DEFINED;
469 RETURN;
470 END IF;
471
472 -- Validate user has access to the report
473 -- NOTE: There was an enhancement to PMV for this but they said that we need
474 -- to code it because the only think we need to check is if the user has access to the function
475 -- (fnd apis)
476 --IF NOT BIS_GRAPH_REGION_HTML_FORMS.hasFunctionAccess(TO_CHAR(p_user_id), l_function_name) THEN
477 IF NOT has_Function_Access(p_user_id, l_function_name) THEN
478 x_indicator_link := G_USER_NOT_AUTHORIZED_REPORT;
479 RETURN;
480 END IF;
481
482 IF p_page_id IS NOT NULL THEN
483 -- If the page id is passed we used it and let the PMV api to handle the dimension parameters and
484 -- time parameter
485 l_time_url_param := NULL;
486 l_dim_url_params := NULL;
487 ELSE
488 -- We set the report URL with the given dimension levels values
489
490 -- Decompose page dimension parameters and time parameter
491 Decompose_Page_Parameters(
492 p_page_dim_params => p_page_dim_params
493 , p_page_time_param => p_page_time_param
494 , x_page_dim_parameters => l_page_dim_params
495 , x_page_time_param => l_page_time_param);
496
497 -- Get the dimension and dimension levels used in the report
498 -- NOTE: There was an enhancement request to PMV for this. We were validating
499 -- to set only the parameters that applies to the report, but they did not
500 -- gave the API. So, We will pass all the dimension level parameters
501 -- and hope that the PMV API handle the levels that does not apply to the report.
502 -- (See previous version code to know how it was working)
503 l_time_url_param := NULL;
504 l_dim_url_params := NULL;
505
506
507 IF p_page_time_param IS NOT NULL THEN
508 l_time_url_param := l_page_time_param.dimension||'+'||
509 l_page_time_param.dimension_level||'_FROM='||l_page_time_param.time_from||'&'||
510 l_page_time_param.dimension||'+'||
511 l_page_time_param.dimension_level||'_TO='||l_page_time_param.time_to;
512 END IF;
513
514 IF p_page_dim_params IS NOT NULL THEN
515 FOR l_index IN 1..l_page_dim_params.COUNT LOOP
516 IF l_dim_url_params IS NOT NULL THEN
517 l_dim_url_params := l_dim_url_params||'&';
518 END IF;
519 l_dim_url_params := l_dim_url_params||l_page_dim_params(l_index).dimension||'+'||
520 l_page_dim_params(l_index).dimension_level||'='||
521 l_page_dim_params(l_index).dimension_level_value;
522 END LOOP;
523 END IF;
524 END IF;
525
526 -- Get possible dimension level used for view by in the report
527 -- NOTE: There was a enhancement request to PMV for this. It was to validate that the view by
528 -- parameter applis to the report. Because they are not giving the API we just pass the view by
529 -- and hope that the report handle the situation when the view by is invalid.
530 l_viewby_url_param := NULL;
531 IF p_view_by_param IS NOT NULL THEN
532 l_viewby_url_param := 'VIEW_BY='||p_view_by_param;
533 ELSE
534 IF p_page_id IS NOT NULL THEN
535 -- Get the ranking parameter of the page
536 BSC_PORTLET_UI_WRAPPER.Get_Ranking_Parameter(
537 p_page_id => p_page_id
538 ,p_user_id => p_user_id
539 ,x_ranking_param => l_ranking_parameter
540 ,x_return_status => x_return_status
541 ,x_msg_count => x_msg_count
542 ,x_msg_data => x_msg_data);
543
544 IF l_ranking_parameter IS NOT NULL THEN
545 l_viewby_url_param := 'VIEW_BY='||l_ranking_parameter;
546 END IF;
547 END IF;
548
549 IF l_viewby_url_param IS NULL THEN
550 -- No view by was provided as parameter,
551 -- No page id (no portlet context) or no ranking parameter
552 -- We need to pass a view by. It will pass the first possible 'view by' of the report
553
554 l_sql := 'SELECT attribute2'||
555 ' FROM ak_region_items'||
556 ' WHERE region_code = :1'||
557 ' AND attribute1 IN (''DIMENSION LEVEL'', ''DIM LEVEL SINGLE VALUE'','||
558 ' ''VIEW BY PARAMETER'')'||
559 ' ORDER BY display_sequence';
560 OPEN l_cursor FOR l_sql USING l_region_code;
561 FETCH l_cursor INTO l_rep_dim;
562 IF l_cursor%FOUND THEN
563 l_viewby_url_param := 'VIEW_BY='||l_rep_dim;
564 ELSE
565 -- I cannot do anything to get a view by parameter
566 l_viewby_url_param := 'VIEW_BY=';
567 END IF;
568 CLOSE l_cursor;
569 END IF;
570 END IF;
571
572 l_function_url_param := 'pFunctionName='||l_function_name;
573
574 -- Build the Report URL
575 x_indicator_link := FND_WEB_CONFIG.PLSQL_AGENT||
576 'BISVIEWER_PUB.showReport?'||
577 'pUrlString='||bis_utilities_pub.encode(l_function_url_param);
578 IF l_dim_url_params IS NOT NULL THEN
579 x_indicator_link := x_indicator_link||bis_utilities_pub.encode('&'||l_dim_url_params);
580 END IF;
581 IF l_time_url_param IS NOT NULL THEN
582 x_indicator_link := x_indicator_link||bis_utilities_pub.encode('&'||l_time_url_param);
583 END IF;
584 IF l_viewby_url_param IS NOT NULL THEN
585 x_indicator_link := x_indicator_link||bis_utilities_pub.encode('&'||l_viewby_url_param);
586 END IF;
587 IF p_page_id IS NOT NULL THEN
588 x_indicator_link := x_indicator_link||'&'||'pPageId='||p_page_id;
589 END IF;
590 -- Bug#2657344, need to pass in pUserId=<p_user_ud>
591 IF p_user_id IS NOT NULL THEN
592 x_indicator_link := x_indicator_link||'&'||'pUserId='||p_user_id;
593 END IF;
594
595 ELSE
596 -- It is a KPI. This is implementation phase II
597 NULL;
598 END IF;
599
600 EXCEPTION
601 WHEN FND_API.G_EXC_ERROR THEN
602 rollback;
603 x_return_status := FND_API.G_RET_STS_ERROR;
604 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
605 ,p_data => x_msg_data);
606 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
607 rollback;
608 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
609 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
610 ,p_data => x_msg_data);
611 WHEN NO_DATA_FOUND THEN
612 rollback;
613 x_return_status := FND_API.G_RET_STS_ERROR;
614 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
615 ,p_data => x_msg_data);
616 WHEN OTHERS THEN
617 rollback;
618 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
619 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
620 ,p_data => x_msg_data);
621
622 END Get_Indicator_Link;
623
624 /************************************************************************************
625 ************************************************************************************/
626
627 PROCEDURE Decompose_Page_Parameters(
628 p_page_dim_params IN VARCHAR2
629 , p_page_time_param IN VARCHAR2
630 , x_page_dim_parameters OUT NOCOPY dim_parameter_tbl_type
631 , x_page_time_param OUT NOCOPY time_parameter_rec_type
632 ) IS
633
634 l_num_dim_params NUMBER;
635 l_dim_params t_array_of_varchar2;
636 l_i NUMBER;
637
638 l_sub_string VARCHAR2(32000);
639 l_position NUMBER;
640 l_index NUMBER;
641 l_dim_parameter_rec dim_parameter_rec_type;
642
643 BEGIN
644 -- p_page_dim_params is : dim1+dimlevel1+dimlevelvalue1;dim2+dimlevel2+dimlevelvalue2;...
645
646 IF p_page_dim_params IS NOT NULL THEN
647 l_num_dim_params := Decompose_Varchar2_List(p_page_dim_params, l_dim_params, ';');
648
649 l_index := 1;
650
651 FOR l_i IN 1..l_num_dim_params LOOP
652 -- dimension+dimlevel+dimlevelvalue must exist together
653 l_sub_string := l_dim_params(l_i);
654 l_position := INSTR(l_sub_string, '+');
655
656 l_dim_parameter_rec.dimension := RTRIM(LTRIM(SUBSTR(l_sub_string, 1, l_position - 1)));
657
658 l_sub_string := SUBSTR(l_sub_string, l_position + 1);
659 l_position := INSTR(l_sub_string, '+');
660 l_dim_parameter_rec.dimension_level := RTRIM(LTRIM(SUBSTR(l_sub_string, 1, l_position - 1)));
661
662 l_sub_string := SUBSTR(l_sub_string, l_position + 1);
663 l_position := INSTR(l_sub_string, '+');
664 IF l_position <> 0 THEN
665 l_dim_parameter_rec.dimension_level_value := RTRIM(LTRIM(SUBSTR(l_sub_string, 1, l_position - 1)));
666 ELSE
667 l_dim_parameter_rec.dimension_level_value := RTRIM(LTRIM(l_sub_string));
668 END IF;
669
670 x_page_dim_parameters(l_index) := l_dim_parameter_rec;
671 l_index := l_index+1;
672
673 END LOOP;
674 END IF;
675
676 IF p_page_time_param IS NOT NULL THEN
677 -- p_page_time_param is like TIME+QUARTER+Q1-02+Q2-02
678
679 l_sub_string := p_page_time_param;
680 l_position := INSTR(l_sub_string, '+');
681 x_page_time_param.dimension := RTRIM(LTRIM(SUBSTR(l_sub_string, 1, l_position - 1)));
682
683 l_sub_string := SUBSTR(l_sub_string, l_position + 1);
684 l_position := INSTR(l_sub_string, '+');
685 x_page_time_param.dimension_level := RTRIM(LTRIM(SUBSTR(l_sub_string, 1, l_position - 1)));
686
687 l_sub_string := SUBSTR(l_sub_string, l_position + 1);
688 l_position := INSTR(l_sub_string, '+');
689 x_page_time_param.time_from := RTRIM(LTRIM(SUBSTR(l_sub_string, 1, l_position - 1)));
690
691 l_sub_string := SUBSTR(l_sub_string, l_position + 1);
692 l_position := INSTR(l_sub_string, '+');
693 x_page_time_param.time_to := RTRIM(LTRIM(l_sub_string));
694
695 END IF;
696
697 END Decompose_Page_Parameters;
698
699 /************************************************************************************
700 ************************************************************************************/
701
702 FUNCTION Get_Page_Dim_Param_Index(
703 p_page_dim_params IN dim_parameter_tbl_type
704 , p_dimension IN VARCHAR2
705 , p_dimension_level IN VARCHAR2
706 ) RETURN NUMBER IS
707
708 l_index NUMBER;
709
710 BEGIN
711
712 FOR l_index IN 1..p_page_dim_params.COUNT LOOP
713 IF (p_page_dim_params(l_index).dimension = p_dimension) AND
714 (p_page_dim_params(l_index).dimension_level = p_dimension_level) THEN
715 RETURN l_index;
716 END IF;
717 END LOOP;
718
719 l_index := 0;
720 RETURN l_index;
721
722 END Get_Page_Dim_Param_Index;
723
724 /************************************************************************************
725 ************************************************************************************/
726
727 FUNCTION has_Function_Access(
728 p_user_id IN NUMBER
729 , p_function_name IN VARCHAR2
730 ) RETURN BOOLEAN IS
731
732 CURSOR c_function IS
733 SELECT
734 function_id
735 FROM
736 fnd_form_functions
737 WHERE
738 function_name = p_function_name;
739
740 l_function_id NUMBER;
741
742 CURSOR c_menus IS
743 SELECT
744 a.menu_id
745 FROM
746 fnd_responsibility_vl a,
747 fnd_user_resp_groups b
748 WHERE
749 b.user_id = p_user_id AND
750 a.version = 'W' AND
751 b.responsibility_id = a.responsibility_id AND
752 b.start_date <= SYSDATE AND
753 (b.end_date IS NULL OR b.end_date >= SYSDATE) AND
754 a.start_date <= sysdate AND
755 (a.end_date IS NULL OR a.end_date >= SYSDATE);
756
757 l_menu_id NUMBER;
758 l_access BOOLEAN := FALSE;
759
760 BEGIN
761 -- Get the function id
762 OPEN c_function;
763 FETCH c_function INTO l_function_id;
764 CLOSE c_function;
765
766 IF l_function_id IS NULL THEN
767 RETURN FALSE;
768 END IF;
769
770
771 OPEN c_menus;
772 LOOP
773 FETCH c_menus INTO l_menu_id;
774 EXIT WHEN c_menus%NOTFOUND;
775
776 l_access := fnd_function.is_function_on_menu(l_menu_id, l_function_id);
777 IF l_access THEN
778 CLOSE c_menus;
779 RETURN l_access;
780 END IF;
781
782 END LOOP;
783 CLOSE c_menus;
784
785 RETURN l_access;
786
787 END has_Function_Access;
788
789 /************************************************************************************
790 ************************************************************************************/
791
792 END BSC_CAUSE_EFFECT_UI_WRAPPER;
[Home] [Help]