[Home] [Help]
PACKAGE BODY: APPS.BSC_PORTLET_GRAPH
Source
1 PACKAGE BODY bsc_portlet_graph AS
2 /* $Header: BSCPGHB.pls 115.13 2003/02/12 14:26:33 adrao ship $ */
3
4 --
5 -- Package constants
6 --
7
8
9
10
11
12
13 --==========================================================================
14 -- PROCEDURE
15 -- Plug
16 --
17 -- PURPOSE
18 -- This procedure displays the Kpi graph portlet. If p_delete = 'Y',
19 -- it deletes the record from bsc_user_kpigraph_plugs table.
20 --
21 -- PARAMETERS
22 --
23 -- HISTORY
24 -- 08-MAR-2001 juwang Created.
25 --==========================================================================
26 PROCEDURE Plug(p_session_id IN pls_integer,
27 p_plug_id IN pls_integer,
28 p_display_name IN VARCHAR2 DEFAULT null,
29 p_delete IN VARCHAR2 DEFAULT 'N') IS
30
31 l_user_id pls_integer;
32 l_resp_id pls_integer;
33
34
35 l_ctm_url VARCHAR2(2000);
36 l_target_url VARCHAR2(2000);
37 l_tab_id NUMBER := -1;
38 l_kpi_id NUMBER := -1;
39 l_temp NUMBER := -1;
40
41 CURSOR c_kg_p IS
42 SELECT k.RESPONSIBILITY_ID,
43 bt.TAB_ID,
44 k.INDICATOR
45 FROM BSC_USER_KPIGRAPH_PLUGS k,
46 BSC_TAB_INDICATORS bt
47 WHERE k.USER_ID = l_user_id
48 AND k.PLUG_ID = p_plug_id
49 AND k.INDICATOR = bt.INDICATOR;
50
51
52 CURSOR c_plug IS
53 SELECT k.INDICATOR
54 FROM BSC_USER_KPIGRAPH_PLUGS k
55 WHERE k.USER_ID = l_user_id
56 AND k.PLUG_ID = p_plug_id;
57
58
59
60
61 CURSOR c_tab_kpi IS
62 SELECT bt.TAB_ID
63 FROM BSC_USER_KPIGRAPH_PLUGS k,
64 BSC_TAB_INDICATORS bt
65 WHERE k.USER_ID = l_user_id
66 AND k.PLUG_ID = p_plug_id
67 AND k.INDICATOR = bt.INDICATOR;
68
69 BEGIN
70
71 IF icx_sec.validatePlugSession(p_plug_id,p_session_id) THEN
72
73 l_user_id := icx_sec.getID(icx_sec.PV_USER_ID,'',p_session_id);
74
75 IF p_delete = 'Y' THEN
76 --------------------------------------------
77 -- should clean up bsc_user_kpigraph_plugs
78 --------------------------------------------
79 DELETE FROM bsc_user_kpigraph_plugs
80 WHERE user_id = l_user_id
81 AND plug_id = p_plug_id;
82
83 COMMIT;
84 RETURN;
85 ELSE -- p_delete = 'N'
86
87 --------------------------------------------
88 -- find the record in BSC_USER_KPIGRAPH_PLUGS
89 --------------------------------------------
90 OPEN c_kg_p;
91 FETCH c_kg_p INTO l_resp_id, l_tab_id, l_kpi_id;
92
93 IF c_kg_p%FOUND THEN -- the record is found
94
95 -- check if it has priviledge to view this kpi
96 IF (bsc_portlet_graph.has_access(p_plug_id)) THEN
97 l_ctm_url:= bsc_portlet_graph.get_customized_kpigraph_url(
98 p_session_id, p_plug_id, l_tab_id, l_kpi_id,
99 FALSE, l_resp_id, p_display_name);
100
101
102 l_target_url:= bsc_portlet_graph.get_portlet_kpigraph_url(
103 p_session_id, p_plug_id, l_tab_id, l_kpi_id,
104 l_resp_id);
105
106 ELSE -- no priviledge
107 l_ctm_url:= bsc_portlet_graph.get_customized_kpigraph_url(
108 p_session_id, p_plug_id, l_tab_id, l_kpi_id,
109 TRUE, bsc_portlet_util.VALUE_NOT_SET,
110 p_display_name);
111
112 l_target_url := bsc_portlet_util.get_bsc_url(p_session_id, p_plug_id, 'BscPorWarnNoPriv.jsp', NULL, TRUE);
113
114 END IF; -- (bsc_portlet_graph.has_access(p_plug_id))
115
116
117 ELSE
118 --------------------------------------------
119 -- no kpi selected, should be empty graph
120 --------------------------------------------
121 l_ctm_url:= bsc_portlet_graph.get_customized_kpigraph_url(
122 p_session_id, p_plug_id, l_tab_id, l_kpi_id,
123 TRUE, bsc_portlet_util.VALUE_NOT_SET, p_display_name);
124
125
126 ----------------------------------------------
127 -- bug# 1745058, make sure if no row in BSC_USER_KPIGRAPH_PLUGS
128 ----------------------------------------------
129 OPEN c_plug;
130 FETCH c_plug INTO l_temp;
131
132 IF c_plug%NOTFOUND THEN -- no row
133 l_target_url := bsc_portlet_util.get_bsc_url(p_session_id, p_plug_id, 'BscPorWarnEmpty.jsp', NULL, TRUE);
134
135 ELSE -- has customized already
136
137 ----------------------------------------------
138 -- bug# 1739823
139 -- check if this kpi is unchecked from the tab
140 ----------------------------------------------
141 OPEN c_tab_kpi;
142 FETCH c_tab_kpi INTO l_temp;
143 IF c_tab_kpi%FOUND THEN
144 l_target_url := bsc_portlet_util.get_bsc_url(p_session_id, p_plug_id, 'BscPorWarnEmpty.jsp', NULL, TRUE);
145
146 ELSE -- uncheck from the tab in builder
147 l_target_url := bsc_portlet_util.get_bsc_url(p_session_id, p_plug_id, 'BscPorWarnNoPriv.jsp', NULL, TRUE);
148
149
150 END IF; --c_tab_kpi%FOUND
151 CLOSE c_tab_kpi;
152
153
154 END IF; --c_plug%NOTFOUND
155 CLOSE c_plug;
156
157 END IF; -- c_kg_p%FOUND
158 CLOSE c_kg_p;
159
160 bsc_portlet_graph.draw_kpi_graph(
161 l_ctm_url,
162 l_target_url, p_session_id,
163 p_plug_id, p_display_name);
164
165
166
167
168 END IF; -- p_delete = 'Y'
169 END IF; -- icx_sec.validatePlugSession
170
171 EXCEPTION
172 WHEN OTHERS THEN
173 ROLLBACK;
174 htp.p(SQLERRM);
175 END Plug;
176
177
178
179
180
181
182 --==========================================================================+
183 -- PROCEDURE
184 -- has_access
185 --
186 -- PURPOSE
187 -- This procedure checks if the customized portlet has access to
188 -- this tab and indicator.
189 --
190 -- PARAMETERS
191 -- p_plug_id portlet id
192 --
193 -- HISTORY
194 -- 18-MAR-2001 juwang Created.
195 --==========================================================================
196 FUNCTION has_access(
197 p_plug_id IN NUMBER) RETURN BOOLEAN IS
198
199 l_kpi_id NUMBER := -1;
200
201 CURSOR c IS
202 SELECT bk.INDICATOR
203 FROM
204 fnd_user_resp_groups fg,
205 fnd_responsibility fr,
206 bsc_user_responsibility_v br,
207 bsc_user_kpigraph_plugs bp,
208 bsc_tab_indicators bti,
209 bsc_user_tab_access bta,
210 bsc_user_kpi_access bk
211 WHERE
212 bp.PLUG_ID = p_plug_id AND
213 bp.USER_ID = br.USER_ID AND
214 fg.USER_ID = bp.USER_ID AND
215 fg.RESPONSIBILITY_ID = bp.RESPONSIBILITY_ID AND
216 sysdate BETWEEN nvl(fg.START_DATE, sysdate) AND
217 nvl(fg.END_DATE, sysdate) AND
218 fr.RESPONSIBILITY_ID = fg.RESPONSIBILITY_ID AND
219 sysdate BETWEEN nvl(fr.START_DATE, sysdate) AND
220 nvl(fr.END_DATE, sysdate) AND
221 bp.RESPONSIBILITY_ID = br.RESPONSIBILITY_ID AND
222 bp.RESPONSIBILITY_ID = bta.RESPONSIBILITY_ID AND
223 bp.INDICATOR = bti.INDICATOR AND
224 bti.TAB_ID = bta.TAB_ID AND
225 SYSDATE BETWEEN bta.START_DATE AND
226 NVL(bta.END_DATE, SYSDATE) AND
227 bp.RESPONSIBILITY_ID = bk.RESPONSIBILITY_ID AND
228 bp.INDICATOR = bk.INDICATOR AND
229 SYSDATE BETWEEN NVL(bk.START_DATE(+), SYSDATE) AND
230 NVL(bk.END_DATE, SYSDATE);
231
232 BEGIN
233
234 OPEN c;
235 FETCH c INTO l_kpi_id;
236
237 IF c%FOUND THEN -- the record is found
238 CLOSE c;
239 RETURN TRUE;
240
241 ELSE
242 CLOSE c;
243 RETURN FALSE;
244
245 END IF; -- c%FOUND
246
247
248
249 END has_access;
250
251
252
253
254
255 --==========================================================================+
256 -- PROCEDURE
257 -- re_align_html_pieces
258 --
259 -- PURPOSE
260 -- bug fix for 1994245
261 -- PARAMETERS
262 --
263 --
264 --
265 --
266 -- HISTORY
267 -- 17-OCT-2001 juwang Created.
268 --==========================================================================
269
270 FUNCTION re_align_html_pieces(src IN utl_http.html_pieces) RETURN
271 utl_http.html_pieces
272 AS
273 dst utl_http.html_pieces;
274 buf RAW(2000);
275 src_row PLS_INTEGER;
276 src_pos PLS_INTEGER;
277 dst_row PLS_INTEGER;
278 len PLS_INTEGER;
279 cut_len PLS_INTEGER;
280 BEGIN
281
282 src_row := 1; src_pos := 1; dst_row := 1;
283 LOOP
284 -- fill bytes from the source till buf is full
285 BEGIN
286 LOOP
287 len := utl_raw.length(buf);
288 EXIT WHEN (len = 2000);
289 cut_len := 2000 - len;
290 IF (cut_len > (lengthb(src(src_row)) - src_pos + 1)) THEN
291 cut_len := lengthb(src(src_row)) - src_pos + 1;
292 END IF;
293 buf := utl_raw.concat(buf, utl_raw.substr(
294 utl_raw.cast_to_raw(src(src_row)), src_pos, cut_len));
295 src_pos := src_pos + cut_len;
296 IF (src_pos > lengthb(src(src_row))) THEN
297 src_row := src_row + 1;
298 src_pos := 1;
299 END IF;
300 END LOOP;
301 EXCEPTION
302 WHEN no_data_found THEN
303 EXIT WHEN utl_raw.length(buf) = 0;
304 END;
305
306 -- extract from buf at character boundary
307 len := lengthb(substr(utl_raw.cast_to_varchar2(buf), 1,
308 length(utl_raw.cast_to_varchar2(buf))));
309 dst(dst_row) := utl_raw.cast_to_varchar2(utl_raw.substr(buf, 1, len));
310 IF (len < utl_raw.length(buf)) THEN
311 buf := utl_raw.substr(buf, len + 1);
312 ELSE
313 buf := NULL;
314 END IF;
315 dst_row := dst_row + 1;
316 END LOOP;
317
318 RETURN dst;
319
320 END;
321
322
323
324 --==========================================================================+
325 -- PROCEDURE
326 -- draw_kpi_graph
327 --
328 -- PURPOSE
329 -- This procedure draws the contents of kpi graph portlet. If
330 -- p_customized is TRUE, draw the graph, otherwise (it is empty),
331 -- displays a message showing "You have not configured the portlet."
332 -- PARAMETERS
333 --
334 --
335 --
336 --
337 -- HISTORY
338 -- 08-MAR-2001 juwang Created.
339 --==========================================================================
340 PROCEDURE draw_kpi_graph(
341 p_ctm_url IN VARCHAR2,
342 p_target_url IN VARCHAR2,
343 p_session_id IN pls_integer,
344 p_plug_id IN pls_integer,
345 p_display_name IN VARCHAR2 DEFAULT NULL) IS
346
347
348 l_ask VARCHAR2(100);
349 l_display_name VARCHAR2(100);
350
351 l_html_pieces utl_http.html_pieces;
352 -- l_ret_status NUMBER := -1;
353 l_errmsg VARCHAR2(2000);
354 l_url VARCHAR2(2000);
355 l_k_url VARCHAR2(2000);
356 INIT_FAILED exception;
357 REQUEST_FAILED exception;
358 BEGIN
359
360 /* check if region name has been customized */
361 SELECT display_name into l_display_name
362 FROM icx_page_plugs
363 WHERE plug_id = p_plug_id;
364
365 IF ( l_display_name IS NULL ) THEN
366 l_ask := '';
367 ELSE
368 l_ask := l_display_name;
369 END IF;
370 -----------------------------------------------------------------
371 --l_html_pieces := bsc_portlet_graph.re_align_html_pieces(utl_http.request_pieces(url => p_target_url, max_pieces => 32000));
372 l_html_pieces := bsc_portlet_util.request_html_pieces(p_url => p_target_url);
373
374
375 -----------------------------------------------------------------
376
377 htp.p('<!-- My Balanced Scorecard Indicator Graph Plug -->');
378 htp.p('<table border=0 cellspacing=0 cellpadding=0 width=100%>');
379 htp.p('<tr><td>');
380
381 icx_plug_utilities.plugbanner(nvl(p_display_name, l_ask),
382 p_ctm_url,
383 'bscigrph.gif');
384
385 htp.p('</td></tr>');
386 htp.p('<tr><td>');
387 -----------------------------------------------------------------
388 -- inserting record for testing
389 -- l_errmsg := bsc_portlet_graph.set_customized_data_private(0, p_plug_id, 0, 2, 3001, 0, 0, l_ret_status);
390 -- htp.p('err code=' || l_ret_status);
394 htp.prn(l_html_pieces(i));
391 -----------------------------------------------------------------
392
393 FOR i in 1..l_html_pieces.count LOOP
395 END LOOP;
396
397
398 -----------------------------------------------------------------
399 -- outputing for tessting !!!
400 -- l_k_url := bsc_portlet_graph.get_kpi_url(p_session_id, p_plug_id);
401 -- htp.p('ctm url=' || p_ctm_url);
402 -- l_errmsg := icx_call.encrypt(p_display_name);
403 -- htp.p('target=' || p_target_url);
404 -----------------------------------------------------------------
405 htp.p('</td></tr>');
406 htp.p('</table>');
407
408 EXCEPTION
409
410 WHEN OTHERS THEN
411 show_err(p_ctm_url, l_ask, p_display_name);
412 END draw_kpi_graph;
413
414
415
416 --==========================================================================+
417 -- PROCEDURE
418 -- show_err
419 --
420 -- PURPOSE
421 -- This procedure shows the same content as BscPorWarnNetErr.jsp
422 --
423 -- PARAMETERS
424 --
425 --
426 --
427 --
428 -- HISTORY
429 -- 02-MAY-2001 juwang Created.
430 --==========================================================================
431 PROCEDURE show_err(
432 p_ctm_url IN VARCHAR2,
433 l_ask IN VARCHAR2,
434 p_display_name IN VARCHAR2 DEFAULT NULL) IS
435 BEGIN
436
437 htp.p('<table border=0 cellspacing=0 cellpadding=0 width=100%>');
438 htp.p('<tr><td>');
439 icx_plug_utilities.plugbanner(nvl(p_display_name, l_ask),
440 p_ctm_url, 'bscigrph.gif');
441
442 htp.p('</td></tr>');
443 htp.p('<tr><td>');
444
445 -- htp.p('OTHERS!!');
446 -- BscPorWarnNetErr.jsp
447 htp.p('<table width="100%" border="0" cellspacing="0" cellpadding="0">');
448 htp.p(' <tr>');
449 htp.p(' <td width="40" align="center"><img src="/OA_MEDIA/bscerroricon_pagetitle.gif" width="32" height="32"></td>');
450 htp.p(' <td valign=bottom><span style="COLOR: #cc0000; FONT-SIZE: 16pt">Error</span></td>');
451 htp.p(' </tr>');
452 htp.p(' <tr>');
453 htp.p(' <td width="40" align="center"><img src=/OA_MEDIA/bscpixel.gif height=1 width=10></td>');
454 htp.p(' <td style="BACKGROUND-COLOR: #cccc99" height=1><img src=/OA_MEDIA/bscpixel.gif height=1 width=10></td>');
455 htp.p(' </tr>');
456 htp.p(' <tr>');
457 htp.p(' <td valign="top" width="40" align="center" height="5"><img src=/OA_MEDIA/bscpixel.gif height=1 width=10></td>');
458 htp.p(' <td height="5"><span style="COLOR: #000000; FONT-SIZE: 10pt"><img src=/OA_MEDIA/bscpixel.gif height=1 width=10></span></td>');
459 htp.p(' </tr>');
460 htp.p(' <tr>');
461 htp.p('<td valign="top" width="40" align="center"> </td>');
462
463 htp.p(' <td>');
464 htp.p(' <p><b><span style="COLOR: #000000; FONT-SIZE: 10pt">Network Problems</span></b></p>');
465 htp.p(' </td>');
466 htp.p(' </tr>');
467 htp.p(' <tr>');
468 htp.p(' <td valign="top" width="40" align="center" height="5"><img src=/OA_MEDIA/bscpixel.gif height=1 width=10></td>');
469 htp.p(' <td height="5"><span style="COLOR: #000000; FONT-SIZE: 10pt"><img src=/OA_MEDIA/bscpixel.gif height=1 width=10></span></td>');
470 htp.p(' </tr>');
471 htp.p(' <tr>');
472 htp.p(' <td valign="top" width="40" align="center"> </td>');
473
474 htp.p(' <td><span style="COLOR: #000000; FONT-SIZE: 10pt">The server could be down or is not responding. Please try again later or contact your administrator if the problem persist.</span><br></td>');
475 htp.p(' </tr>');
476 htp.p('</table>');
477
478
479
480
481
482
483 htp.p('</td></tr>');
484 htp.p('</table>');
485 END show_err;
486
487
488
489
490
491 --==========================================================================+
492 -- PROCEDURE
493 -- launch_bsckpi_jsp
494 --
495 -- PURPOSE
496 -- This procedure replaces the current browser with bsc kpi page.
497 --
498 -- PARAMETERS
499 --
500 -- HISTORY
501 -- 08-MAR-2001 juwang Created.
502 --==========================================================================
503 PROCEDURE launch_bsckpi_jsp(
504 p_session_id IN pls_integer,
505 p_plug_id IN pls_integer) IS
506
507 l_kpi_url VARCHAR2(2000):= NULL;
508 e_reqserver_not_set exception;
509
510
511 BEGIN
512
513
514 IF icx_sec.validatePlugSession(p_plug_id,p_session_id) THEN
515
516 l_kpi_url:= bsc_portlet_util.get_bsc_url(p_session_id,
517 p_plug_id, 'BscInit.jsp', NULL, FALSE);
518
519 htp.p('<html><body onload="window.location.replace('''|| l_kpi_url || ''');">' || '</body></html>');
520 END IF; -- icx_sec.validatePlugSession
521
522 EXCEPTION
523
524 WHEN OTHERS THEN
525 htp.p(SQLERRM);
526 /*
527 WHEN E_REQSERVER_NOT_SET THEN
528 htp.p('por_redirect.reqserver '|| 'reqserver_not_set exception ' ||
529 l_progress || ' '|| sqlerrm);
530
531 WHEN OTHERS THEN
535
532 htp.p('por_redirect.reqserver '|| l_progress || ' '|| sqlerrm);
533 */
534 END launch_bsckpi_jsp;
536
537
538 --==========================================================================+
539 -- PROCEDURE
540 -- get_tab_url
541 --
542 -- PURPOSE
543 -- This procedure returns the url to the jsp for displaying
544 -- Balanced Scorecard Tab page. It also includes the necessary
545 -- parameters.
546 --
547 -- PARAMETERS
548 --
549 -- HISTORY
550 -- 08-MAR-2001 juwang Created.
551 --==========================================================================
552 FUNCTION get_tab_url(
553 p_cookie_value IN VARCHAR2,
554 p_encrypted_plug_id IN VARCHAR2
555 ) RETURN VARCHAR2 IS
556
557 l_session_id NUMBER := -1;
558 l_plug_id NUMBER := -1;
559 BEGIN
560
561 bsc_portlet_util.decrypt_plug_info(p_cookie_value,
562 p_encrypted_plug_id, l_session_id, l_plug_id);
563
564 RETURN get_tab_url(l_session_id, l_plug_id);
565
566 END get_tab_url;
567
568
569
570
571
572
573
574 --==========================================================================+
575 -- PROCEDURE
576 -- get_kpi_url
577 --
578 -- PURPOSE
579 -- This procedure returns the url to the jsp for displaying
580 -- Balanced Scorecard Tab page. It also includes the necessary
581 -- parameters.
582 --
583 -- PARAMETERS
584 --
585 -- HISTORY
586 -- 08-MAR-2001 juwang Created.
587 --==========================================================================
588 FUNCTION get_kpi_url(
589 p_cookie_value IN VARCHAR2,
590 p_encrypted_plug_id IN VARCHAR2
591 ) RETURN VARCHAR2 IS
592
593 l_session_id NUMBER := -1;
594 l_plug_id NUMBER := -1;
595 BEGIN
596
597 bsc_portlet_util.decrypt_plug_info(p_cookie_value,
598 p_encrypted_plug_id, l_session_id, l_plug_id);
599
600 RETURN get_kpi_url(l_session_id, l_plug_id);
601
602
603 END get_kpi_url;
604
605
606
607
608
609
610
611 --==========================================================================+
612 -- PROCEDURE
613 -- get_tab_url
614 --
615 -- PURPOSE
616 -- This procedure returns the url to the jsp for displaying
617 -- Balanced Scorecard Tab page. It also includes the necessary
618 -- parameters.
619 --
620 -- PARAMETERS
621 --
622 -- HISTORY
623 -- 08-MAR-2001 juwang Created.
624 --==========================================================================
625 FUNCTION get_tab_url(
626 p_session_id IN NUMBER,
627 p_plug_id IN NUMBER
628 ) RETURN VARCHAR2 IS
629
630
631 l_ext_params VARCHAR2(2000):= NULL;
632
633 l_tab_url VARCHAR2(2000):= NULL;
634 l_resp_id NUMBER := bsc_portlet_util.VALUE_NOT_SET;
635 l_tab_id NUMBER := bsc_portlet_util.VALUE_NOT_SET;
636 l_kpi_id NUMBER := bsc_portlet_util.VALUE_NOT_SET;
637
638
639 BEGIN
640
641 bsc_portlet_graph.get_customized_data_private(p_session_id,
642 p_plug_id, l_resp_id, l_tab_id, l_kpi_id);
643
644 l_ext_params := get_pluginfo_params(l_resp_id, p_session_id, p_plug_id) ||'&' || bsc_portlet_util.PR_TABCODE || '=' || l_tab_id;
645
646 l_tab_url := bsc_portlet_util.get_bsc_url(p_session_id, p_plug_id, 'BscInit.jsp', l_ext_params, FALSE);
647
648
649
650 RETURN l_tab_url;
651
652
653 EXCEPTION
654
655 WHEN OTHERS THEN
656 RETURN l_tab_url;
657
658 END get_tab_url;
659
660
661
662
663
664
665 --==========================================================================+
666 -- PROCEDURE
667 -- get_kpi_url
668 --
669 -- PURPOSE
670 -- This procedure returns the url to the jsp for displaying
671 -- Balanced Scorecard Kpi page. It also includes the necessary
672 -- parameters.
673 --
674 -- PARAMETERS
675 --
676 -- HISTORY
677 -- 08-MAR-2001 juwang Created.
678 --==========================================================================
679 FUNCTION get_kpi_url(
680 p_session_id IN NUMBER,
681 p_plug_id IN NUMBER
682 ) RETURN VARCHAR2 IS
683
684
685 l_ext_params VARCHAR2(2000):= NULL;
686
687 l_kpi_url VARCHAR2(2000):= NULL;
688 l_resp_id NUMBER := bsc_portlet_util.VALUE_NOT_SET;
689 l_tab_id NUMBER := bsc_portlet_util.VALUE_NOT_SET;
690 l_kpi_id NUMBER := bsc_portlet_util.VALUE_NOT_SET;
691
692
693
694 BEGIN
695
696 bsc_portlet_graph.get_customized_data_private(p_session_id,
697 p_plug_id, l_resp_id, l_tab_id, l_kpi_id);
698
699 l_ext_params := get_pluginfo_params(l_resp_id, p_session_id, p_plug_id) ||'&' || bsc_portlet_util.PR_KCODE || '=' || l_kpi_id;
700
704
701 l_kpi_url := bsc_portlet_util.get_bsc_url(p_session_id, p_plug_id, 'BscInit.jsp', l_ext_params, FALSE);
702
703
705 RETURN l_kpi_url;
706
707 EXCEPTION
708
709 WHEN OTHERS THEN
710 RETURN l_kpi_url;
711
712 END get_kpi_url;
713
714
715
716
717 --==========================================================================+
718 -- PROCEDURE
719 -- get_pluginfo_params
720 --
721 -- PURPOSE
722 -- This procedure builds the paramters list.
723 --
724 -- PARAMETERS
725 --
726 -- HISTORY
727 -- 08-MAR-2001 juwang Created.
728 --==========================================================================
729 FUNCTION get_pluginfo_params(
730 p_resp_id IN NUMBER,
731 p_session_id IN NUMBER,
732 p_plug_id IN NUMBER
733 ) RETURN VARCHAR2 IS
734
735
736 l_ext_params VARCHAR2(200):= NULL;
737
738
739 BEGIN
740
741 l_ext_params := bsc_portlet_util.PR_RESPID || '=' || p_resp_id;
742 /*
743 l_ext_params := bsc_portlet_util.PR_RESPID || '=' || p_resp_id || '&' ||
744 'pSessionId=' || p_session_id || '&' ||
745 'pPlugId=' || p_plug_id;
746 */
747
748 RETURN l_ext_params;
749
750
751
752 END get_pluginfo_params;
753
754
755
756
757 --==========================================================================+
758 -- PROCEDURE
759 -- get_portlet_kpigraph_url
760 --
761 -- PURPOSE
762 -- This procedure returns the url for the kpi graph
763 -- portlet. It also includes the necessary
764 -- parameters.
765 --
766 -- PARAMETERS
767 --
768 -- HISTORY
769 -- 08-MAR-2001 juwang Created.
770 --==========================================================================
771 FUNCTION get_portlet_kpigraph_url(
772 p_session_id IN pls_integer,
773 p_plug_id IN pls_integer,
774 p_tab_id IN NUMBER,
775 p_kpi_id IN NUMBER,
776 p_resp_id IN NUMBER) RETURN VARCHAR2 IS
777
778
779 l_url VARCHAR2(2000):= NULL;
780 l_ext_params VARCHAR2(100) := NULL;
781
782 BEGIN
783
784 l_ext_params := bsc_portlet_util.PR_RESPID || '=' || p_resp_id || '&' ||
785 bsc_portlet_util.PR_KCODE || '=' || p_kpi_id || '&' ||
786 'pPlugId=' || icx_call.encrypt3(p_plug_id);
787
788 -----------------------------------------------------
789 -- Now, we form the url by passing jsp name and extra params.
790 -- NOTE: do not use preferences's responsibility id.
791 -----------------------------------------------------
792 l_url := bsc_portlet_util.get_bsc_url(
793 p_session_id,
794 p_plug_id,
795 'BscPorKpi.jsp',
796 l_ext_params,
797 FALSE);
798
799
800 RETURN l_url;
801
802 EXCEPTION
803
804 WHEN OTHERS THEN
805 RETURN null;
806
807 END get_portlet_kpigraph_url;
808
809
810
811
812 --==========================================================================+
813 -- FUNCTION
814 -- get_customized_kpigraph_url
815 --
816 -- PURPOSE
817 -- This procedure returns the url for customize the kpi graph
818 -- portlet. It also includes the necessary
819 -- parameters.
820 --
821 -- PARAMETERS
822 --
823 -- HISTORY
824 -- 08-MAR-2001 juwang Created.
825 --==========================================================================
826 FUNCTION get_customized_kpigraph_url(
827 p_session_id IN pls_integer,
828 p_plug_id IN pls_integer,
829 p_tab_id IN NUMBER,
830 p_kpi_id IN NUMBER,
831 p_is_never_customized IN BOOLEAN,
832 p_resp_id IN NUMBER,
833 p_display_name IN VARCHAR2) RETURN VARCHAR2 IS
834
835 enc_disp_name VARCHAR2(1000):= NULL;
836 l_url VARCHAR2(2000):= NULL;
837 l_ext_params VARCHAR2(100) := NULL;
838
839 BEGIN
840
841 /*
842 l_ext_params := 'pSessionId=' || p_session_id || '&' ||
843 'pPlugId=' || p_plug_id;
844 */
845 l_ext_params := 'pPlugId=' || icx_call.encrypt3(p_plug_id);
846
847 -- it has been customized, resonsibility id is available
848 IF ( NOT p_is_never_customized ) THEN
849
850 l_ext_params := l_ext_params || '&' ||
851 bsc_portlet_util.PR_KCODE || '=' || p_kpi_id || '&' ||
852 bsc_portlet_util.PR_RESPID || '=' || p_resp_id;
853
854
855 END IF; -- (p_is_respid_used )
856
857 l_url := bsc_portlet_util.get_bsc_url(
858 p_session_id,
859 p_plug_id,
860 'BscGraphPortletCust.jsp',
861 l_ext_params,
862 FALSE);
863
864
865 RETURN l_url;
866
867 EXCEPTION
868
869 WHEN OTHERS THEN
870 RETURN null;
871
872 END get_customized_kpigraph_url;
873
874
878
875
876
877
879
880 --==========================================================================+
881 -- PROCEDURE
882 -- get_customized_data_private
883 --
884 -- PURPOSE
885 --
886 -- PARAMETERS
887 --
888 -- HISTORY
889 -- 08-MAR-2001 juwang Created.
890 --==========================================================================
891 PROCEDURE get_customized_data_private(
892 p_session_id IN pls_integer,
893 p_plug_id IN pls_integer,
894 p_o_resp_id OUT NOCOPY NUMBER,
895 p_o_tab_id OUT NOCOPY NUMBER,
896 p_o_kpi_id OUT NOCOPY NUMBER) IS
897
898 l_user_id NUMBER;
899
900 CURSOR c_kg_p IS
901 SELECT k.RESPONSIBILITY_ID,
902 bt.TAB_ID,
903 k.INDICATOR
904 FROM BSC_USER_KPIGRAPH_PLUGS k,
905 BSC_TAB_INDICATORS bt
906 WHERE k.USER_ID = l_user_id
907 AND k.PLUG_ID = p_plug_id
908 AND k.INDICATOR = bt.INDICATOR;
909
910 BEGIN
911
912 IF icx_sec.validatePlugSession(p_plug_id,p_session_id) THEN
913
914 l_user_id := icx_sec.getID(icx_sec.PV_USER_ID,'',p_session_id);
915 --------------------------------------------
916 -- find the record in BSC_USER_KPIGRAPH_PLUGS
917 --------------------------------------------
918 OPEN c_kg_p;
919 FETCH c_kg_p INTO p_o_resp_id, p_o_tab_id, p_o_kpi_id;
920
921 IF c_kg_p%FOUND THEN
922 RETURN;
923
924 ELSE
925 p_o_resp_id := bsc_portlet_util.VALUE_NOT_SET;
926 p_o_tab_id := bsc_portlet_util.VALUE_NOT_SET;
927 p_o_kpi_id := bsc_portlet_util.VALUE_NOT_SET;
928
929 END IF; --c_kg_p%FOUND
930
931
932 END IF; -- icx_sec.validatePlugSession(p_plug_id,p_session_id)
933
934 END get_customized_data_private;
935
936
937
938
939
940
941 --==========================================================================+
942 -- PROCEDURE
943 -- set_customized_data_private
944 --
945 -- PURPOSE
946 -- This procedure is used internally. It should not be used
947 -- by java program.
948 --
949 -- PARAMETERS
950 --
951 -- HISTORY
952 -- 08-MAR-2001 juwang Created.
953 --==========================================================================
954 FUNCTION set_customized_data_private(
955 p_user_id IN NUMBER,
956 p_plug_id IN NUMBER,
957 p_resp_id IN NUMBER,
958 p_kpi_id IN NUMBER,
959 p_createy_by IN NUMBER,
960 p_last_updated_by IN NUMBER,
961 p_porlet_name IN VARCHAR2,
962 p_o_ret_status OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
963
964 insert_err EXCEPTION;
965 update_err EXCEPTION;
966
967 l_errmesg VARCHAR2(2000) := bsc_portlet_util.MSGTXT_SUCCESS;
968 l_count NUMBER := 0;
969
970 BEGIN
971 SELECT count(*)
972 INTO l_count
973 FROM BSC_USER_KPIGRAPH_PLUGS k
974 WHERE
975 k.USER_ID = p_user_id AND
976 k.PLUG_ID = p_plug_id;
977
978 IF (l_count > 0) THEN -- record exists, need to update
979
980 UPDATE
981 BSC_USER_KPIGRAPH_PLUGS
982 SET
983 RESPONSIBILITY_ID = p_resp_id,
984 INDICATOR = p_kpi_id,
985 LAST_UPDATE_DATE = SYSDATE,
986 LAST_UPDATED_BY = p_last_updated_by
987 WHERE
988 USER_ID = p_user_id AND
989 PLUG_ID = p_plug_id;
990
991 IF SQL%ROWCOUNT = 0 THEN
992 RAISE update_err;
993 END IF;
994
995 ELSE -- record does not exist, insert it
996
997 INSERT INTO BSC_USER_KPIGRAPH_PLUGS (
998 USER_ID, PLUG_ID, RESPONSIBILITY_ID, INDICATOR,
999 CREATION_DATE, CREATED_BY,
1000 LAST_UPDATE_DATE, LAST_UPDATED_BY,
1001 LAST_UPDATE_LOGIN)
1002 VALUES (
1003 p_user_id, p_plug_id, p_resp_id, p_kpi_id,
1004 SYSDATE, p_createy_by,
1005 SYSDATE, p_last_updated_by,
1006 p_last_updated_by);
1007
1008 IF SQL%ROWCOUNT = 0 THEN
1009 RAISE insert_err;
1010 END IF;
1011
1012 END IF; -- (l_count > 0)
1013
1014 -- update display name !!!!
1015 bsc_portlet_util.update_portlet_name(p_user_id, p_plug_id, p_porlet_name);
1016
1017 -- everything works ok so we commit
1018 COMMIT;
1019 p_o_ret_status := bsc_portlet_util.CODE_RET_SUCCESS;
1020 RETURN bsc_portlet_util.MSGTXT_SUCCESS;
1021
1022 EXCEPTION
1023
1024 WHEN insert_err THEN
1025 ROLLBACK;
1026 p_o_ret_status := bsc_portlet_util.CODE_RET_ERROR;
1027 l_errmesg := 'Error inserting to BSC_USER_KPIGRAPH_PLUGS';
1028 RETURN l_errmesg;
1029
1030
1031 WHEN update_err THEN
1032 ROLLBACK;
1033 p_o_ret_status := bsc_portlet_util.CODE_RET_ERROR;
1034 l_errmesg := 'Error updating to BSC_USER_KPIGRAPH_PLUGS';
1035 RETURN l_errmesg;
1036
1037 WHEN OTHERS THEN
1038 ROLLBACK;
1039 p_o_ret_status := bsc_portlet_util.CODE_RET_ERROR;
1043
1040 l_errmesg := 'Error in bsc_portlet_graph.set_customized_data_private. SQLERRM = ' || SQLERRM;
1041 RETURN l_errmesg;
1042
1044
1045 END set_customized_data_private;
1046
1047
1048
1049
1050
1051 --==========================================================================+
1052 -- FUNCTION
1053 -- get_customization
1054 --
1055 -- PURPOSE
1056 -- This function is used by
1057 -- oracle.apps.bsc.iviewer.thinext.client.ThinDataExtractor
1058 -- class.
1059 -- PARAMETERS
1060 -- p_has_access : 1=>TRUE, 0->FALSE
1061 -- HISTORY
1062 -- 08-MAR-2001 juwang Created.
1063
1064 --==========================================================================
1065 FUNCTION get_customization(
1066 p_cookie_value IN VARCHAR2,
1067 p_encrypted_plug_id IN VARCHAR2,
1068 p_resp_id OUT NOCOPY NUMBER,
1069 p_tab_id OUT NOCOPY NUMBER,
1070 p_kpi_id OUT NOCOPY NUMBER,
1071 p_display_name OUT NOCOPY VARCHAR2,
1072 p_has_access OUT NOCOPY NUMBER) RETURN NUMBER IS
1073
1074 l_session_id NUMBER;
1075 l_plug_id NUMBER;
1076 l_user_id NUMBER;
1077
1078
1079 CURSOR c_kg_p IS
1080 SELECT k.RESPONSIBILITY_ID, k.INDICATOR, p.DISPLAY_NAME
1081 FROM bsc_user_kpigraph_plugs k,
1082 icx_page_plugs p
1083 WHERE
1084 p.PLUG_ID = l_plug_id AND
1085 k.PLUG_ID(+)= p.PLUG_ID AND
1086 k.USER_ID(+) = l_user_id;
1087
1088
1089 CURSOR c_tab IS
1090 SELECT bt.TAB_ID
1091 FROM bsc_tab_indicators bt
1092 WHERE
1093 bt.INDICATOR = p_kpi_id;
1094
1095
1096 CURSOR c_fm IS
1097 SELECT PROMPT
1098 FROM fnd_menu_entries_vl fme,
1099 icx_page_plugs ipp
1100 WHERE
1101 ipp.PLUG_ID = l_plug_id AND
1102 fme.menu_id = ipp.menu_id and
1103 fme.ENTRY_SEQUENCE = ipp.ENTRY_SEQUENCE;
1104
1105 BEGIN
1106
1107
1108 bsc_portlet_util.decrypt_plug_info(p_cookie_value,
1109 p_encrypted_plug_id, l_session_id, l_plug_id);
1110
1111 p_has_access := 0;
1112 IF icx_sec.validatePlugSession(l_plug_id, l_session_id) THEN
1113 l_user_id := icx_sec.getID(icx_sec.PV_USER_ID,'',l_session_id);
1114
1115 OPEN c_kg_p;
1116 FETCH c_kg_p INTO p_resp_id, p_kpi_id, p_display_name;
1117
1118 IF c_kg_p%FOUND THEN -- the record is found
1119
1120 -- get the tab id this indicator belongs
1121 OPEN c_tab;
1122 FETCH c_tab INTO p_tab_id;
1123 IF c_tab%NOTFOUND THEN
1124 p_tab_id := bsc_portlet_util.VALUE_NOT_SET;
1125 END IF; --c_tab%NOTFOUND
1126 CLOSE c_tab;
1127
1128
1129
1130 -- checks if display name is null
1131 IF (p_display_name IS NULL) THEN
1132 OPEN c_fm;
1133 FETCH c_fm INTO p_display_name;
1134 CLOSE c_fm;
1135 END IF; -- (p_display_name IS NULL)
1136
1137
1138 IF (bsc_portlet_graph.has_access(l_plug_id)) THEN
1139 p_has_access := 1;
1140 ELSE
1141 p_has_access := 0;
1142 END IF;
1143 CLOSE c_kg_p;
1144 RETURN bsc_portlet_util.CODE_RET_SUCCESS;
1145
1146 ELSE -- not found, no such plug i
1147 p_has_access := 0;
1148 CLOSE c_kg_p;
1149 RETURN bsc_portlet_util.CODE_RET_NOROW;
1150
1151 END IF; -- c_kg_p%FOUND
1152
1153 ELSE -- session expires
1154 RETURN bsc_portlet_util.CODE_RET_SESSION_EXP;
1155 END IF; -- icx_sec.validatePlugSession(l_plug_id, l_session_id)
1156
1157 END get_customization;
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169 --==========================================================================+
1170 -- FUNCTION
1171 -- set_customization
1172 --
1173 -- PURPOSE
1174 -- This function is used by
1175 -- oracle.apps.bsc.iviewer.thinext.client.ThinDataExtractor
1176 -- class.
1177 -- PARAMETERS
1178 --
1179 -- HISTORY
1180 -- 08-MAR-2001 juwang Created.
1181 --==========================================================================
1182
1183 FUNCTION set_customization(
1184 p_cookie_value IN VARCHAR2,
1185 p_encrypted_plug_id IN VARCHAR2,
1186 p_resp_id IN NUMBER,
1187 p_kpi_id IN NUMBER,
1188 p_portlet_name IN VARCHAR2,
1189 p_o_ret_status OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
1190
1191
1192 session_expire_err EXCEPTION;
1193 l_session_id NUMBER := -1;
1194 l_plug_id NUMBER := -1;
1195 l_user_id NUMBER := -1;
1196
1197 l_errmsg VARCHAR2(2000) := bsc_portlet_util.MSGTXT_SUCCESS;
1198
1199 BEGIN
1200
1201 bsc_portlet_util.decrypt_plug_info(p_cookie_value,
1202 p_encrypted_plug_id, l_session_id, l_plug_id);
1203
1204
1205 IF icx_sec.validatePlugSession(l_plug_id, l_session_id) THEN
1206
1207 l_user_id := icx_sec.getID(icx_sec.PV_USER_ID,'', l_session_id);
1211
1208 l_errmsg := bsc_portlet_graph.set_customized_data_private(l_user_id,
1209 l_plug_id, p_resp_id, p_kpi_id,
1210 l_user_id, l_user_id, p_portlet_name, p_o_ret_status);
1212 RETURN l_errmsg;
1213
1214 ELSE -- session expires now
1215
1216 RAISE session_expire_err;
1217
1218
1219 END IF; -- icx_sec.validatePlugSession
1220
1221 -- icx_plug_utilities.gotoMainMenu;
1222 -- htp.p(l_errmsg);
1223
1224 EXCEPTION
1225
1226 WHEN session_expire_err THEN
1227
1228 p_o_ret_status := bsc_portlet_util.CODE_RET_SESSION_EXP;
1229 l_errmsg := bsc_portlet_util.MSGTXT_SESSION_EXP;
1230 RETURN l_errmsg;
1231
1232
1233 WHEN OTHERS THEN
1234 return 'Error';
1235
1236 END set_customization;
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255 --==========================================================================+
1256 -- FUNCTION
1257 -- get_graph_image
1258 --
1259 -- PURPOSE
1260 -- This function passes the blob to caller
1261 --
1262 --
1263 -- PARAMETERS
1264 --
1265 -- HISTORY
1266 -- 08-MAR-2001 juwang Created.
1267 --==========================================================================
1268 FUNCTION get_graph_image(
1269 p_resp_id IN NUMBER,
1270 p_kpi_id IN NUMBER,
1271 p_graph_key IN VARCHAR2,
1272 p_fbody OUT NOCOPY BLOB,
1273 p_o_ret_status OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
1274
1275 insert_err EXCEPTION;
1276 update_err EXCEPTION;
1277
1278 l_db_gkey VARCHAR2(100); -- stores the key retrieved from db
1279 l_errmesg VARCHAR2(2000) := bsc_portlet_util.MSGTXT_SUCCESS;
1280 l_w NUMBER := 600;
1281 l_h NUMBER := 400;
1282 -- l_sq_img_id NUMBER;
1283
1284
1285
1286 CURSOR c_kg IS
1287 SELECT bsi.FILE_BODY
1288 FROM bsc_kpi_graphs k,
1289 bsc_sys_images bsi
1290 WHERE
1291 k.RESPONSIBILITY_ID = p_resp_id AND
1292 k.INDICATOR = p_kpi_id AND
1293 k.GRAPH_KEY = p_graph_key AND
1294 k.IMAGE_ID = bsi.IMAGE_ID;
1295
1296 BEGIN
1297
1298 OPEN c_kg;
1299 FETCH c_kg INTO p_fbody;
1300
1301 IF c_kg%FOUND THEN
1302 ---------------------------------------------------
1303 -- row exists in bsc_sys_images and bsc_kpi_graphs
1304 ---------------------------------------------------
1305 CLOSE c_kg;
1306 p_o_ret_status := bsc_portlet_util.CODE_RET_SUCCESS;
1307 l_errmesg := bsc_portlet_util.MSGTXT_SUCCESS;
1308 RETURN l_errmesg;
1309
1310 ELSE
1311 ---------------------------------------------------
1312 -- row does not exist
1313 ---------------------------------------------------
1314 CLOSE c_kg;
1315 p_o_ret_status := bsc_portlet_util.CODE_RET_NOROW;
1316 l_errmesg := bsc_portlet_util.MSGTXT_NOROW;
1317 RETURN l_errmesg;
1318
1319 END IF; -- c_kg%FOUND
1320
1321
1322
1323 EXCEPTION
1324
1325 WHEN OTHERS THEN
1326 IF c_kg%ISOPEN THEN
1327 CLOSE c_kg;
1328 END IF;
1329
1330 p_o_ret_status := bsc_portlet_util.CODE_RET_ERROR;
1331 RETURN l_errmesg;
1332
1333
1334
1335 END get_graph_image;
1336
1337
1338
1339
1340
1341 --==========================================================================+
1342 -- FUNCTION
1343 -- save_graphkey
1344 --
1345 -- PURPOSE
1346 -- This fucntion makes sure
1347 -- 1. The record by the given p_resp_id, p_kpi_id, p_graph_key
1348 -- exists in bsc_kpi_graphs and the record with given p_img_id
1349 -- exists in bsc_sys_images.
1350 -- PARAMETERS
1351 --
1352 -- HISTORY
1353 -- 08-MAR-2001 juwang Created.
1354 --==========================================================================
1355 FUNCTION save_graphkey(
1356 p_user_id IN NUMBER,
1357 p_resp_id IN NUMBER,
1358 p_kpi_id IN NUMBER,
1359 p_graph_key IN VARCHAR2,
1360 p_img_id OUT NOCOPY NUMBER,
1361 p_o_ret_status OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
1362
1363 insert_err EXCEPTION;
1364 update_err EXCEPTION;
1365
1366 l_gkey VARCHAR2(100);
1367 l_errmesg VARCHAR2(2000) := bsc_portlet_util.MSGTXT_SUCCESS;
1368 l_w NUMBER := 550;
1369 l_h NUMBER := 250;
1370
1371 CURSOR c_kg IS
1372 SELECT k.IMAGE_ID, k.GRAPH_KEY
1373 FROM bsc_kpi_graphs k,
1374 bsc_sys_images bsi
1375 WHERE
1376 k.RESPONSIBILITY_ID = p_resp_id AND
1377 k.INDICATOR = p_kpi_id AND
1378 k.IMAGE_ID = bsi.IMAGE_ID;
1379
1380 BEGIN
1381
1382 OPEN c_kg;
1383 FETCH c_kg INTO p_img_id, l_gkey;
1384
1385 IF c_kg%FOUND THEN
1389
1386 ---------------------------------------------------
1387 -- row exists in bsc_sys_images and bsc_kpi_graphs
1388 ---------------------------------------------------
1390 IF l_gkey = p_graph_key THEN
1391 ------------------------------------------------
1392 -- graph key is the same, no need to update
1393 -- make sure all the return vars are set.
1394 ------------------------------------------------
1395 CLOSE c_kg;
1396 p_o_ret_status := bsc_portlet_util.CODE_RET_SUCCESS;
1397 RETURN bsc_portlet_util.MSGTXT_SUCCESS;
1398 END IF;
1399
1400
1401 ------------------------------------------------
1402 -- graph key is different, need to update record
1403 ------------------------------------------------
1404 UPDATE bsc_kpi_graphs
1405 SET
1406 GRAPH_KEY = p_graph_key,
1407 LAST_UPDATE_DATE = SYSDATE,
1408 LAST_UPDATED_BY = p_user_id
1409 WHERE
1410 RESPONSIBILITY_ID = p_resp_id AND
1411 INDICATOR = p_kpi_id;
1412
1413
1414 IF SQL%ROWCOUNT = 0 THEN
1415 l_errmesg := 'Error updating bsc_kpi_graphs. RESPONSIBILITY_ID=' || p_resp_id || ', INDICATOR=' || p_kpi_id ;
1416 RAISE update_err;
1417 END IF;
1418
1419
1420 UPDATE bsc_sys_images
1421 SET
1422 FILE_NAME = p_graph_key,
1423 DESCRIPTION = p_graph_key,
1424 LAST_UPDATE_DATE = SYSDATE,
1425 LAST_UPDATED_BY = p_user_id
1426 WHERE
1427 IMAGE_ID = p_img_id;
1428
1429
1430 IF SQL%ROWCOUNT = 0 THEN
1431 l_errmesg := 'Error updating bsc_kpi_graphs. RESPONSIBILITY_ID=' || p_resp_id || ', INDICATOR=' || p_kpi_id ;
1432 RAISE update_err;
1433 END IF;
1434
1435
1436 ELSE
1437 ---------------------------------------------------
1438 -- Record does not exist both in bsc_kpi_graphs and bsc_sys_images
1439 ---------------------------------------------------
1440 /*
1441 SELECT bsc_sys_image_id_s.NEXTVAL
1442 INTO l_sq_img_id
1443 FROM dual;
1444 */
1445 INSERT INTO bsc_sys_images(
1446 IMAGE_ID, FILE_NAME, DESCRIPTION, FILE_BODY, WIDTH, HEIGHT,
1447 CREATION_DATE, CREATED_BY,
1448 LAST_UPDATE_DATE, LAST_UPDATED_BY,
1449 LAST_UPDATE_LOGIN)
1450 VALUES (
1451 bsc_sys_image_id_s.NEXTVAL,
1452 p_graph_key, p_graph_key, empty_blob(), l_w, l_h,
1453 SYSDATE, p_user_id,
1454 SYSDATE, p_user_id,
1455 p_user_id)
1456 RETURNING IMAGE_ID INTO p_img_id;
1457
1458 IF SQL%ROWCOUNT = 0 THEN
1459 l_errmesg := 'Error inserting into bsc_sys_images. FILE_NAME=' ||
1460 p_graph_key;
1461 RAISE insert_err;
1462 END IF;
1463
1464
1465 INSERT INTO bsc_kpi_graphs(
1466 RESPONSIBILITY_ID, INDICATOR, GRAPH_KEY, IMAGE_ID,
1467 CREATION_DATE, CREATED_BY,
1468 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN)
1469 VALUES(
1470 p_resp_id, p_kpi_id, p_graph_key, p_img_id,
1471 SYSDATE, p_user_id,
1472 SYSDATE, p_user_id, p_user_id);
1473
1474 IF SQL%ROWCOUNT = 0 THEN
1475 l_errmesg := 'Error inserting into bsc_kpi_graphs. RESPONSIBILITY_ID='|| p_resp_id || ', INDICATOR=' || p_kpi_id || ', GRAPH_KEY=' ||
1476 p_graph_key || ', IMAGE_ID' || p_img_id;
1477 RAISE insert_err;
1478 END IF;
1479
1480
1481
1482
1483
1484 END IF; -- c_kg%FOUND
1485
1486 ----------------------------
1487 -- success if it goes here.
1488 ----------------------------
1489 COMMIT;
1490 CLOSE c_kg;
1491 p_o_ret_status := bsc_portlet_util.CODE_RET_SUCCESS;
1492 RETURN bsc_portlet_util.MSGTXT_SUCCESS;
1493
1494
1495 EXCEPTION
1496
1497 WHEN insert_err THEN
1498 ROLLBACK;
1499 CLOSE c_kg;
1500 p_o_ret_status := bsc_portlet_util.CODE_RET_ERROR;
1501 RETURN l_errmesg;
1502
1503
1504 WHEN update_err THEN
1505 ROLLBACK;
1506 CLOSE c_kg;
1507 p_o_ret_status := bsc_portlet_util.CODE_RET_ERROR;
1508 RETURN l_errmesg;
1509
1510 WHEN OTHERS THEN
1511 ROLLBACK;
1512 CLOSE c_kg;
1513 p_o_ret_status := bsc_portlet_util.CODE_RET_ERROR;
1514 RETURN l_errmesg;
1515
1516
1517
1518 END save_graphkey;
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528 END bsc_portlet_graph;