[Home] [Help]
PACKAGE BODY: APPS.BSC_PORTLET_UI_WRAPPER
Source
1 PACKAGE BODY BSC_PORTLET_UI_WRAPPER AS
2 /* $Header: BSCPORWB.pls 120.4 2007/02/08 14:31:26 ppandey ship $ */
3
4 G_PKG_NAME varchar2(30) := 'BSC_PORTLET_UI_WRAPPER';
5
6 /************************************************************************************
7 ************************************************************************************/
8
9 FUNCTION Encode_String(
10 p_string IN VARCHAR2
11 ,p_escape IN VARCHAR2 := '%'
12 ,p_reserved IN VARCHAR2 := '%=&;'
13 ,p_encoded IN VARCHAR2 := 'PEAS'
14 ) RETURN VARCHAR2 IS
15 l_string VARCHAR2(32000);
16 l_char VARCHAR(5);
17 l_offset INTEGER;
18 BEGIN
19 IF p_string IS NULL THEN
20 RETURN NULL;
21 END IF;
22
23 FOR i IN 1..length(p_string) LOOP
24 l_char := substr(p_string, i, 1);
25 l_offset := instr(p_reserved, l_char);
26
27 IF l_offset > 0 THEN
28 l_string := l_string || p_escape || substr(p_encoded, l_offset, 1);
29 ELSE
30 l_string := l_string || l_char;
31 END IF;
32 END LOOP;
33
34 RETURN l_string;
35 END Encode_String;
36
37 /************************************************************************************
38 ************************************************************************************/
39
40 FUNCTION Clean_String(
41 p_string IN VARCHAR2
42 ) RETURN VARCHAR2 IS
43
44 l_string VARCHAR2(32700);
45
46 BEGIN
47 -- Clean enclosing single quotes
48 l_string := RTRIM(LTRIM(p_string, ''''), '''');
49
50 RETURN l_string;
51
52 END Clean_String;
53
54 /************************************************************************************
55 ************************************************************************************/
56
57 PROCEDURE Get_Page_Params(
58 p_user_id IN NUMBER
59 ,p_page_id IN VARCHAR2
60 ,x_page_params OUT NOCOPY VARCHAR2
61 ,x_return_status OUT NOCOPY VARCHAR2
62 ,x_msg_count OUT NOCOPY NUMBER
63 ,x_msg_data OUT NOCOPY VARCHAR2
64 ) IS
65
66 l_page_session_rec BIS_PMV_PARAMETERS_PUB.page_session_rec_type;
67 l_page_param_tbl BIS_PMV_PARAMETERS_PUB.parameter_tbl_type;
68 i NUMBER;
69
70 TYPE CursorType IS REF CURSOR;
71 l_cursor CursorType;
72 l_sql VARCHAR2(32000);
73
74 l_parameter_name VARCHAR2(32000);
75 l_parameter_value VARCHAR2(32000);
76 l_parameter_description VARCHAR2(32000);
77
78 l_dimension VARCHAR2(100) := 'TIME_COMPARISON_TYPE';
79 l_attribute_name VARCHAR2(100) := 'AS_OF_DATE';
80
81 BEGIN
82
83 FND_MSG_PUB.Initialize;
84
85 l_page_session_rec.user_id := TO_CHAR(p_user_id);
86 l_page_session_rec.page_id := p_page_id;
87
88 BIS_PMV_PARAMETERS_PUB.RETRIEVE_PAGE_PARAMETERS(
89 p_page_session_rec => l_page_session_rec
90 ,x_page_param_tbl => l_page_param_tbl
91 ,x_return_status => x_return_status
92 ,x_msg_count => x_msg_count
93 ,x_msg_data => x_msg_data);
94
95 x_page_params := NULL;
96 FOR i IN 1..l_page_param_tbl.COUNT LOOP
97 IF x_page_params IS NOT NULL THEN
98 x_page_params := x_page_params || '&';
99 END IF;
100
101 x_page_params := x_page_params ||
102 Encode_String(l_page_param_tbl(i).parameter_name) || '=' ||
103 Encode_String(Clean_String(l_page_param_tbl(i).parameter_value)) || ';' ||
104 Encode_String(l_page_param_tbl(i).parameter_description);
105 END LOOP;
106
107 -- This is a workaround to get TIME_COMPARISON_PARAMETER. There is a open bug#2609475
108 -- to PMV in order to include it in the BIS_PMV_PARAMETERS_PUB.RETRIEVE_PAGE_PARAMETERS
109 l_sql := 'SELECT attribute_name, session_value, session_description'||
110 ' FROM bis_user_attributes'||
111 ' WHERE user_id = :1 AND page_id = :2 AND dimension = :3';
112 OPEN l_cursor FOR l_sql USING p_user_id, p_page_id, l_dimension;
113 FETCH l_cursor INTO l_parameter_name, l_parameter_value, l_parameter_description;
114 IF l_cursor%FOUND THEN
115 IF x_page_params IS NOT NULL THEN
116 x_page_params := x_page_params || '&';
117 END IF;
118
119 x_page_params := x_page_params ||
120 Encode_String(l_parameter_name) || '=' ||
121 Encode_String(Clean_String(l_parameter_value)) || ';' ||
122 Encode_String(l_parameter_description);
123
124 END IF;
125 CLOSE l_cursor;
126
127 l_sql := 'SELECT attribute_name, session_value, session_description'||
128 ' FROM bis_user_attributes'||
129 ' WHERE user_id = :1 AND page_id = :2 AND attribute_name = :3';
130 OPEN l_cursor FOR l_sql USING p_user_id, p_page_id, l_attribute_name;
131 FETCH l_cursor INTO l_parameter_name, l_parameter_value, l_parameter_description;
132 IF l_cursor%FOUND THEN
133 IF x_page_params IS NOT NULL THEN
134 x_page_params := x_page_params || '&';
135 END IF;
136
137 x_page_params := x_page_params ||
138 Encode_String(l_parameter_name) || '=' ||
139 Encode_String(Clean_String(l_parameter_value)) || ';' ||
140 Encode_String(l_parameter_description);
141
142 END IF;
143 CLOSE l_cursor;
144
145 EXCEPTION
146 WHEN FND_API.G_EXC_ERROR THEN
147 rollback;
148 x_return_status := FND_API.G_RET_STS_ERROR;
149 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
150 ,p_data => x_msg_data);
151 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
152 rollback;
153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
154 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
155 ,p_data => x_msg_data);
156 WHEN NO_DATA_FOUND THEN
157 rollback;
158 x_return_status := FND_API.G_RET_STS_ERROR;
159 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
160 ,p_data => x_msg_data);
161 WHEN OTHERS THEN
162 rollback;
163 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
164 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
165 ,p_data => x_msg_data);
166
167 END Get_Page_Params;
168
169 /************************************************************************************
170 ************************************************************************************/
171
172 PROCEDURE Get_Ranking_Parameter (
173 p_page_id IN VARCHAR2
174 ,p_user_id IN NUMBER
175 ,x_ranking_param OUT NOCOPY VARCHAR2
176 ,x_return_status OUT NOCOPY VARCHAR2
177 ,x_msg_count OUT NOCOPY NUMBER
178 ,x_msg_data OUT NOCOPY VARCHAR2
179 ) IS
180
181 BEGIN
182
183 FND_MSG_PUB.Initialize;
184
185 BIS_PMV_PORTAL_UTIL_PUB.GET_RANKING_PARAMETER(
186 p_page_id => p_page_id,
187 p_user_id => TO_CHAR(p_user_id)
188 ,x_ranking_param => x_ranking_param
189 ,x_return_Status => x_return_status
190 ,x_msg_count => x_msg_count
191 ,x_msg_data => x_msg_data
192 );
193
194 EXCEPTION
195 WHEN FND_API.G_EXC_ERROR THEN
196 rollback;
197 x_return_status := FND_API.G_RET_STS_ERROR;
198 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
199 ,p_data => x_msg_data);
200 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
201 rollback;
202 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
203 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
204 ,p_data => x_msg_data);
205 WHEN NO_DATA_FOUND THEN
206 rollback;
207 x_return_status := FND_API.G_RET_STS_ERROR;
208 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
209 ,p_data => x_msg_data);
210 WHEN OTHERS THEN
211 rollback;
212 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
213 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
214 ,p_data => x_msg_data);
215
216 END Get_Ranking_Parameter;
217
218 /************************************************************************************
219 ************************************************************************************/
220
221 PROCEDURE Validate_Responsibility(
222 p_user_id IN NUMBER
223 ,p_resp_id IN NUMBER
224 ,x_valid OUT NOCOPY VARCHAR2
225 ,x_return_status OUT NOCOPY VARCHAR2
226 ,x_msg_count OUT NOCOPY NUMBER
227 ,x_msg_data OUT NOCOPY VARCHAR2
228 ) IS
229
230 l_count NUMBER := 0;
231
232 BEGIN
233 x_valid := 'Y';
234
235 -- This part validates that the login user has access to the
236 -- responsibility associated to the portlet
237
238 SELECT
239 count(*)
240 INTO
241 l_count
242 FROM
243 FND_USER_RESP_GROUPS fnd,
244 FND_RESPONSIBILITY fr
245 WHERE
246 fnd.USER_ID = p_user_id AND
247 fnd.RESPONSIBILITY_ID = p_resp_id AND
248 fnd.RESPONSIBILITY_ID = fr.RESPONSIBILITY_ID AND
249 SYSDATE BETWEEN fr.START_DATE AND NVL(fr.END_DATE, SYSDATE) AND
250 SYSDATE BETWEEN fnd.START_DATE AND NVL(fnd.END_DATE, SYSDATE);
251
252 IF (l_count = 0) THEN
253 x_valid := 'N';
254 RETURN;
255 END IF;
256
257 /* BUG 3579794 -- don't limit resp to AppId 271
258 -- Now validate that the user/responsibility is still valid in BSC
259 SELECT
260 count(*)
261 INTO
262 l_count
263 FROM
264 BSC_USER_RESPONSIBILITY_V
265 WHERE
266 user_id = p_user_id AND
267 responsibility_id = p_resp_id;
268
269 IF (l_count = 0) THEN
270 x_valid := 'N';
271 RETURN;
272 END IF; */
273
274
275 EXCEPTION
276 WHEN FND_API.G_EXC_ERROR THEN
277 rollback;
278 x_return_status := FND_API.G_RET_STS_ERROR;
279 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
280 ,p_data => x_msg_data);
281 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
282 rollback;
283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
284 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
285 ,p_data => x_msg_data);
286 WHEN NO_DATA_FOUND THEN
287 rollback;
288 x_return_status := FND_API.G_RET_STS_ERROR;
289 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
290 ,p_data => x_msg_data);
291 WHEN OTHERS THEN
292 rollback;
293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
294 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
295 ,p_data => x_msg_data);
296 END Validate_Responsibility;
297
298 /************************************************************************************
299 ************************************************************************************/
300
301 PROCEDURE Show_Info_Page(
302 p_info_key IN VARCHAR2
303 ) IS
304 l_url VARCHAR2(2000);
305 l_session_id VARCHAR2(80);
306 l_transaction_id NUMBER;
307 l_dbc VARCHAR2(2000);
308 l_language_code VARCHAR2(2000);
309 BEGIN
310
311 IF icx_sec.validateSession THEN
312
313 l_session_id := ICX_SEC.g_session_id; --pass g_session_id
314 l_transaction_id := icx_sec.createTransaction(l_session_id);
315 l_dbc := FND_WEB_CONFIG.DATABASE_ID;
316 l_language_code := icx_sec.g_language_code;
317
318 l_url := RTRIM(LTRIM(fnd_profile.value('APPS_FRAMEWORK_AGENT')));
319 IF SUBSTRB(l_url, -1, 1) <> '/' THEN
320 l_url := l_url||'/';
321 END IF;
322 l_url := l_url||'OA_HTML/OA.jsp'||
323 '?akRegionCode=BSC_PORTLET_INFO_PGE'||'&'||'akRegionApplicationId=271'||
324 '&'||'dbc='||l_dbc||'&'||'transactionid='||l_transaction_id||'&'||'language_code='||l_language_code||
325 '&'||'retainAM=Y'||'&'||'infoKey='||bis_utilities_pvt.escape_html(p_info_key);
326
327 htp.p('<html><body onload="window.location.replace('''||l_url|| ''');">' ||
328 '</body></html>');
329
330 END IF;
331
332 EXCEPTION
333 WHEN OTHERS THEN
334 htp.p(SQLERRM);
335
336 END Show_Info_Page;
337
338 /************************************************************************************
339 ************************************************************************************/
340 PROCEDURE Show_Custom_View_Image(
341 p_tab_code IN VARCHAR2,
342 p_tab_view IN VARCHAR2,
343 p_resp_id IN VARCHAR2,
344 p_mime_type IN VARCHAR2 := 'image/gif'
345 ) IS
346 doc blob;
347 BEGIN
348 IF icx_sec.validateSession THEN
349 SELECT bsi.file_body
350 INTO doc
351 FROM bsc_sys_images bsi, bsc_sys_images_map_vl bsim, bsc_user_tab_access bta
352 WHERE bsim.image_id = bsi.image_id AND bsim.source_type = 1
353 AND bsim.source_code = p_tab_code AND bsim.type = p_tab_view
354 AND bsim.source_code = bta.tab_id AND bta.responsibility_id = p_resp_id;
355
356 owa_util.mime_header(p_mime_type, FALSE);
357 htp.p('Content-length: ' || dbms_lob.getlength(doc));
358 owa_util.http_header_close;
359 wpg_docload.download_file(doc);
360 END IF;
361 EXCEPTION
362 WHEN no_data_found THEN
363 htp.htmlOpen;
364 htp.headOpen;
365 htp.title('Custom View Not Found');
366 htp.headClose;
367 htp.bodyOpen;
368 htp.hr;
369 htp.header(nsize=>1, cheader=>'Custom View Not Found');
370 htp.hr;
371 htp.bodyClose;
372 htp.htmlClose;
373 END Show_Custom_View_Image;
374
375
376 /************************************************************************************
377 ************************************************************************************/
378
379 PROCEDURE Apply_CustomView_Parameters (
380 p_user_id IN VARCHAR2,
381 p_reference_path IN VARCHAR2,
382 p_resp_id IN VARCHAR2,
383 p_tab_id IN VARCHAR2,
384 p_view_id IN VARCHAR2,
385 p_portlet_name IN VARCHAR2,
386 x_return_status OUT NOCOPY VARCHAR2,
387 x_msg_count OUT NOCOPY NUMBER,
388 x_msg_data OUT NOCOPY VARCHAR2
389 ) IS
390
391 l_count NUMBER;
392 l_parameters VARCHAR2(2000);
393
394 l_sql VARCHAR2(32000);
395 TYPE CursorType IS REF CURSOR;
396 l_cursor CursorType;
397
398 l_plug_id NUMBER;
399
400 BEGIN
401
402 FND_MSG_PUB.Initialize;
403
404 l_count := 0;
405
406 -- Validate that all parameter are not null
407 IF p_user_id IS NULL OR p_reference_path IS NULL OR p_resp_id IS NULL OR
408 p_tab_id IS NULL OR p_view_id IS NULL OR p_portlet_name IS NULL THEN
409 FND_MESSAGE.SET_NAME('BSC','BSC_CV_POR_CUST_INVALID_PARAMS');
410 FND_MSG_PUB.ADD;
411 RAISE FND_API.G_EXC_ERROR;
412 END IF;
413
414 -- Get the plug_id. We need to continue using it because
415 -- it is part of the key of the table.
416 l_sql := 'SELECT plug_id FROM bsc_user_kpigraph_plugs'||
417 ' WHERE reference_path = :1';
418 OPEN l_cursor FOR l_sql USING p_reference_path;
419 FETCH l_cursor INTO l_plug_id;
420 IF l_cursor%NOTFOUND THEN
421 SELECT ICX_PAGE_PLUGS_S.NEXTVAL
422 INTO l_plug_id
423 FROM sys.dual;
424 END IF;
425 CLOSE l_cursor;
426
427
428 SELECT
429 COUNT(*)
430 INTO
431 l_count
432 FROM
433 bsc_user_kpigraph_plugs
434 WHERE
435 -- user_id = p_user_id AND -- BUG 4136961, user level customization is not supported.
436 reference_path = p_reference_path;
437
438 l_parameters := 'pTabId='||p_tab_id||'&'||'pViewId='||p_view_id;
439
440 IF l_count > 0 THEN
441 -- Update record
442 UPDATE
443 bsc_user_kpigraph_plugs
444 SET
445 responsibility_id = p_resp_id,
446 parameter_string = l_parameters,
447 last_update_date = SYSDATE,
448 last_updated_by = p_user_id
449 WHERE
450 -- user_id = p_user_id AND -- BUG 4136961, user level customization is not supported.
451 reference_path = p_reference_path;
452 ELSE
453 -- Insert
454 INSERT INTO bsc_user_kpigraph_plugs (
455 user_id,
456 plug_id,
457 reference_path,
458 responsibility_id,
459 indicator,
460 parameter_string,
461 creation_date,
462 created_by,
463 last_update_date,
464 last_updated_by,
465 last_update_login
466 ) VALUES (
467 -1, -- BUG 4136961, user level customization is not supported.
468 l_plug_id,
469 p_reference_path,
470 p_resp_id,
471 0,
472 l_parameters,
473 SYSDATE,
474 p_user_id,
475 SYSDATE,
476 p_user_id,
477 p_user_id
478 );
479 END IF;
480
481 -- Update display name
482 UPDATE icx_portlet_customizations
483 SET title = p_portlet_name, caching_key = to_char(to_number(caching_key)+1)
484 WHERE reference_path = p_reference_path;
485
486 COMMIT;
487
488 EXCEPTION
489 WHEN FND_API.G_EXC_ERROR THEN
490 rollback;
491 x_return_status := FND_API.G_RET_STS_ERROR;
492 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
493 ,p_data => x_msg_data);
494 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
495 rollback;
496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
497 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
498 ,p_data => x_msg_data);
499 WHEN NO_DATA_FOUND THEN
500 rollback;
501 x_return_status := FND_API.G_RET_STS_ERROR;
502 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
503 ,p_data => x_msg_data);
504 WHEN OTHERS THEN
505 rollback;
506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
507 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
508 ,p_data => x_msg_data);
509 END Apply_CustomView_Parameters;
510
511 /************************************************************************************
512 ************************************************************************************/
513
514
515
516 PROCEDURE Apply_Graph_Parameters (
517 p_user_id IN VARCHAR2,
518 p_reference_path IN VARCHAR2,
519 p_resp_id IN VARCHAR2,
520 p_tab_id IN VARCHAR2,
521 p_kpi_code IN VARCHAR2,
522 p_view_id IN VARCHAR2,
523 p_portlet_name IN VARCHAR2,
524 x_return_status OUT NOCOPY VARCHAR2,
525 x_msg_count OUT NOCOPY NUMBER,
526 x_msg_data OUT NOCOPY VARCHAR2
527 ) IS
528
529 l_count NUMBER;
530 l_parameters VARCHAR2(2000);
531
532 l_sql VARCHAR2(32000);
533 TYPE CursorType IS REF CURSOR;
534 l_cursor CursorType;
535
536 l_plug_id NUMBER;
537
538 BEGIN
539
540 FND_MSG_PUB.Initialize;
541
542 l_count := 0;
543
544 -- Validate that all parameter are not null
545 IF p_user_id IS NULL OR p_reference_path IS NULL OR p_resp_id IS NULL OR
546 p_tab_id IS NULL OR p_kpi_code IS NULL OR p_portlet_name IS NULL THEN
547 FND_MESSAGE.SET_NAME('BSC','BSC_CV_POR_CUST_INVALID_PARAMS');
548 FND_MSG_PUB.ADD;
549 RAISE FND_API.G_EXC_ERROR;
550 END IF;
551
552 -- Get the plug_id. We need to continue using it because
553 -- it is part of the key of the table.
554 l_sql := 'SELECT plug_id FROM bsc_user_kpigraph_plugs'||
555 ' WHERE reference_path = :1';
556 OPEN l_cursor FOR l_sql USING p_reference_path;
557 FETCH l_cursor INTO l_plug_id;
558 IF l_cursor%NOTFOUND THEN
559 SELECT ICX_PAGE_PLUGS_S.NEXTVAL
560 INTO l_plug_id
561 FROM sys.dual;
562 END IF;
563 CLOSE l_cursor;
564
565
566 SELECT
567 COUNT(*)
568 INTO
569 l_count
570 FROM
571 bsc_user_kpigraph_plugs
572 WHERE
573 -- user_id = p_user_id AND -- BUG 4136961, user level customization is not supported.
574 reference_path = p_reference_path;
575
576 l_parameters := 'pTabId='||p_tab_id;
577
578 IF l_count > 0 THEN
579 -- Update record
580 UPDATE
581 bsc_user_kpigraph_plugs
582 SET
583 responsibility_id = p_resp_id,
584 indicator = p_kpi_code,
585 parameter_string = l_parameters,
586 last_update_date = SYSDATE,
587 last_updated_by = p_user_id
588 WHERE
589 -- user_id = p_user_id AND -- BUG 4136961, user level customization is not supported.
590 reference_path = p_reference_path;
591 ELSE
592 -- Insert
593 INSERT INTO bsc_user_kpigraph_plugs (
594 user_id,
595 plug_id,
596 reference_path,
597 responsibility_id,
598 indicator,
599 parameter_string,
600 creation_date,
601 created_by,
602 last_update_date,
603 last_updated_by,
604 last_update_login
605 ) VALUES (
606 -1, -- BUG 4136961, user level customization is not supported.
607 l_plug_id,
608 p_reference_path,
609 p_resp_id,
610 p_kpi_code,
611 l_parameters,
612 SYSDATE,
613 p_user_id,
614 SYSDATE,
615 p_user_id,
616 p_user_id
617 );
618
619 UPDATE icx_portlet_customizations
620 SET plug_id = l_plug_id
621 WHERE reference_path = p_reference_path;
622
623 END IF;
624
625 -- Update display name
626 UPDATE icx_portlet_customizations
627 SET title = p_portlet_name, caching_key = to_char(to_number(caching_key)+1)
628 WHERE reference_path = p_reference_path;
629
630 COMMIT;
631
632 EXCEPTION
633 WHEN FND_API.G_EXC_ERROR THEN
634 rollback;
635 x_return_status := FND_API.G_RET_STS_ERROR;
636 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
637 ,p_data => x_msg_data);
638 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
639 rollback;
640 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
641 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
642 ,p_data => x_msg_data);
643 WHEN NO_DATA_FOUND THEN
644 rollback;
645 x_return_status := FND_API.G_RET_STS_ERROR;
646 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
647 ,p_data => x_msg_data);
648 WHEN OTHERS THEN
649 rollback;
650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
651 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
652 ,p_data => x_msg_data);
653 END Apply_Graph_Parameters;
654
655 /************************************************************************************
656 ************************************************************************************/
657
658 PROCEDURE Apply_Kpi_List_Parameters(
659 p_user_id IN NUMBER,
660 p_plug_id IN NUMBER,
661 p_reference_path IN VARCHAR2,
662 p_resp_id IN NUMBER,
663 p_details_flag IN NUMBER,
664 p_group_flag IN NUMBER,
665 p_kpi_measure_details_flag IN NUMBER,
666 p_createy_by IN NUMBER,
667 p_last_updated_by IN NUMBER,
668 p_porlet_name IN VARCHAR2,
669 p_number_array IN BSC_NUM_LIST,
670 p_o_ret_status OUT NOCOPY NUMBER,
671 x_return_status OUT NOCOPY VARCHAR2,
672 x_msg_count OUT NOCOPY NUMBER,
673 x_msg_data OUT NOCOPY VARCHAR2
674 ) IS
675
676 l_count NUMBER;
677
678 l_sql VARCHAR2(32000);
679 TYPE CursorType IS REF CURSOR;
680 l_cursor CursorType;
681
682 l_plug_id NUMBER;
683
684 l_errmsg VARCHAR2(2000) := bsc_portlet_util.MSGTXT_SUCCESS;
685 BEGIN
686
687 FND_MSG_PUB.Initialize;
688
689 l_count := 0;
690
691 -- Validate that all parameter are not null
692
693 --DBMS_OUTPUT.PUT_LINE('Before null check');
694
695 IF p_user_id IS NULL OR p_reference_path IS NULL OR p_resp_id IS NULL OR
696 p_details_flag IS NULL OR p_group_flag IS NULL OR p_porlet_name IS NULL OR p_number_array IS NULL
697 THEN
698 FND_MESSAGE.SET_NAME('BSC','BSC_CV_POR_CUST_INVALID_PARAMS');
699 FND_MSG_PUB.ADD;
700 RAISE FND_API.G_EXC_ERROR;
701 END IF;
702
703 -- Get the plug_id. We need to continue using it because
704 -- it is part of the key of the table.
705 l_sql := 'SELECT plug_id from bsc_user_kpilist_plugs'||
706 ' WHERE reference_path = :1';
707
708 l_plug_id := p_plug_id;
709
710 OPEN l_cursor FOR l_sql USING p_reference_path;
711 FETCH l_cursor INTO l_plug_id;
712 IF l_cursor%NOTFOUND THEN
713 SELECT ICX_PAGE_PLUGS_S.NEXTVAL
714 INTO l_plug_id
715 FROM sys.dual;
716 END IF;
717 CLOSE l_cursor;
718
719 --DBMS_OUTPUT.PUT_LINE('l_plug_id-->'||l_plug_id);
720
721 -- Temp error message
722 l_errmsg := BSC_PORTLET_KPILISTCUST.set_customized_data_private_n(
723 p_user_id , l_plug_id , p_reference_path , p_resp_id ,
724 p_details_flag , p_group_flag , p_kpi_measure_details_flag, p_createy_by , p_last_updated_by ,
725 p_porlet_name , p_number_array , p_o_ret_status );
726
727 COMMIT;
728
729 EXCEPTION
730 WHEN FND_API.G_EXC_ERROR THEN
731 rollback;
732 x_return_status := FND_API.G_RET_STS_ERROR;
733 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
734 ,p_data => x_msg_data);
735 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
736 rollback;
737 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
738 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
739 ,p_data => x_msg_data);
740 WHEN NO_DATA_FOUND THEN
741 rollback;
742 x_return_status := FND_API.G_RET_STS_ERROR;
743 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
744 ,p_data => x_msg_data);
745 WHEN OTHERS THEN
746 rollback;
747 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
748 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
749 ,p_data => x_msg_data);
750 END Apply_Kpi_List_Parameters;
751
752 /************************************************************************************
753 ************************************************************************************/
754
755 PROCEDURE checkUpdateCustView(
756 p_commit IN VARCHAR2,
757 p_user_id IN VARCHAR2,
758 p_reference_path IN VARCHAR2,
759 p_tab_id IN VARCHAR2,
760 p_view_id IN VARCHAR2,
761 p_resp_id IN VARCHAR2,
762 x_return_status OUT NOCOPY VARCHAR2
763 ) IS
764
765 last_update_date_from_designer DATE := null;
766 last_update_date_from_portlet DATE := null;
767 l_parameters VARCHAR2(2000);
768 l_commit VARCHAR2(10);
769
770 BEGIN
771
772
773 IF(p_commit IS NULL ) THEN
774 l_commit := FND_API.G_FALSE;
775 ELSE
776 l_commit := p_commit;
777 END IF;
778
779
780 SELECT
781 last_update_date
782 INTO
783 last_update_date_from_designer
784 FROM
785 BSC_TAB_VIEWS_B
786 WHERE
787 tab_id = p_tab_id AND
788 tab_view_id = p_view_id;
789
790
791 SELECT
792 last_update_date
793 INTO
794 last_update_date_from_portlet
795 FROM
796 bsc_user_kpigraph_plugs
797 WHERE
798 user_id = p_user_id AND
799 reference_path = p_reference_path ;
800
801
802 IF(last_update_date_from_designer IS NOT NULL AND last_update_date_from_portlet IS NOT NULL AND p_resp_id IS NOT NULL) THEN
803
804 IF(last_update_date_from_designer > last_update_date_from_portlet) THEN
805
806 l_parameters := 'pTabId='||p_tab_id||'&'||'pViewId='||p_view_id;
807
808 --Update bsc_user_kpigraph_plugs
809 UPDATE
810 bsc_user_kpigraph_plugs
811 SET
812 responsibility_id = p_resp_id,
813 parameter_string = l_parameters,
814 last_update_date = SYSDATE,
815 last_updated_by = p_user_id
816 WHERE
817 user_id = p_user_id AND
818 reference_path = p_reference_path;
819
820
821 --Upadte icx customizations
822 UPDATE icx_portlet_customizations
823 SET caching_key = to_char(to_number(NVL(caching_key, 0))+1)
824 WHERE reference_path = p_reference_path;
825
826 IF(l_commit = FND_API.G_TRUE) THEN
827 COMMIT;
828 END IF;
829
830 END IF;
831 END IF;
832
833 EXCEPTION
834 WHEN NO_DATA_FOUND THEN
835 rollback;
836 x_return_status := FND_API.G_RET_STS_ERROR;
837
838 WHEN OTHERS THEN
839 rollback;
840 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
841
842 END checkUpdateCustView;
843 /************************************************************************************
844 ************************************************************************************/
845
846 FUNCTION Get_CustView_Measure_Name(
847 p_region_code IN VARCHAR
848 ,p_dataset_id IN NUMBER
849 ) RETURN VARCHAR2 IS
850
851 l_meas_disp_name AK_REGION_ITEMS_VL.attribute_label_long%TYPE;
852 l_region_code VARCHAR2(200) := NULL;
853
854 BEGIN
855 IF ( p_region_code = 'NULL' ) THEN
856 l_region_code := NULL;
857 ELSE
858 l_region_code := p_region_code;
859 END IF;
860 BSC_CUSTOM_VIEW_UI_WRAPPER.Get_Measure_Display_Name(l_region_code, p_dataset_id, l_meas_disp_name);
861 RETURN l_meas_disp_name;
862
863 EXCEPTION
864 WHEN OTHERS THEN
865 RETURN 'NULL';
866 END Get_CustView_Measure_Name;
867
868 END BSC_PORTLET_UI_WRAPPER;