[Home] [Help]
PACKAGE BODY: APPS.BSC_PORTLET_KPILISTCUST
Source
1 package body BSC_PORTLET_KPILISTCUST as
2 /* $Header: BSCPKCB.pls 120.4 2007/02/08 14:11:56 ppandey ship $ */
3
4 /* Customization table
5 ICX_PORTLET_CUSTOMIZATIONS -- This will be available for anyone to use
6 REFERENCE_PATH NOT NULL VARCHAR2(100)
7 PLUG_ID NUMBER -- Not use in this portlet
8 APPLICATION_ID NUMBER
9 RESPONSIBILITY_ID NUMBER
10 SECURITY_GROUP_ID NUMBER
11 CACHING_KEY VARCHAR2(55)
12 TITLE VARCHAR2(100)
13
14 */
15
16
17
18
19 ------------------------------------------------------------------
20 -- juwang's code goes from here
21 ------------------------------------------------------------------
22
23
24 --==========================================================================+
25 -- PROCEDURE
26 -- get_pluginfo_params
27 --
28 -- PURPOSE
29 -- This procedure builds the paramters list.
30 --
31 -- PARAMETERS
32 --
33 -- HISTORY
34 -- 08-MAR-2001 juwang Created.
35 --==========================================================================
36 FUNCTION get_pluginfo_params(
37 p_resp_id IN NUMBER) RETURN VARCHAR2 IS
38
39
40 l_ext_params VARCHAR2(100):= NULL;
41
42
43 BEGIN
44
45 l_ext_params := bsc_portlet_util.PR_RESPID || '=' || p_resp_id;
46 RETURN l_ext_params;
47
48
49
50 END get_pluginfo_params;
51
52
53
54
55
56
57
58
59
60 --==========================================================================+
61 -- PROCEDURE
62 -- get_resp_id
63 --
64 -- PURPOSE
65 --
66 -- PARAMETERS
67 --
68 -- HISTORY
69 -- 08-MAR-2001 juwang Created.
70 --==========================================================================
71 /*
72 FUNCTION get_resp_id(
73 p_session_id IN pls_integer,
74 p_plug_id IN pls_integer) RETURN NUMBER IS
75
76 l_resp_id NUMBER := bsc_portlet_util.VALUE_NOT_SET;
77
78 CURSOR c_kg_p IS
79 SELECT p.RESPONSIBILITY_ID
80 FROM icx_portlet_customizations p
81 WHERE
82 p.PLUG_ID = p_plug_id;
83
84
85 BEGIN
86
87 IF icx_sec.validateSessionPrivate(p_session_id) THEN
88
89 OPEN c_kg_p;
90 FETCH c_kg_p INTO l_resp_id;
91
92 CLOSE c_kg_p;
93
94 END IF; -- icx_sec.validateSessionPrivate(p_session_id)
95
96 RETURN l_resp_id;
97 END get_resp_id;
98 */
99
100
101 --==========================================================================+
102 -- PROCEDURE
103 -- insert_row
104 --
105 -- PURPOSE
106 -- This procedure is used internally. It is used to insert
107 -- table: BSC_USER_KPILIST_PLUGS
108 --
109 -- PARAMETERS
110 --
111 -- HISTORY
112 -- 08-MAR-2001 juwang Created.
113 --==========================================================================
114
115 PROCEDURE insert_row (
116 p_plug_id IN NUMBER,
117 p_details_flag IN NUMBER,
118 p_group_flag IN NUMBER,
119 p_kpi_measure_details_flag IN NUMBER,
120 p_last_update_date IN DATE,
121 p_last_updated_by IN NUMBER
122 ) IS
123 insert_err EXCEPTION;
124 BEGIN
125 INSERT INTO BSC_USER_KPILIST_PLUGS (
126 PLUG_ID,
127 DETAILS_FLAG,
128 GROUP_FLAG,
129 KPI_MEASURE_DETAILS_FLAG,
130 CREATION_DATE,
131 CREATED_BY,
132 LAST_UPDATE_DATE,
133 LAST_UPDATED_BY,
134 LAST_UPDATE_LOGIN
135 ) VALUES (
136 p_plug_id,
137 p_details_flag,
138 p_group_flag,
139 p_kpi_measure_details_flag,
140 p_last_update_date,
141 p_last_updated_by,
142 p_last_update_date,
143 p_last_updated_by,
144 p_last_updated_by
145 );
146
147 IF SQL%ROWCOUNT = 0 THEN
148 RAISE insert_err;
149 END IF;
150
151 END insert_row;
152
153
154 --==========================================================================+
155 -- PROCEDURE
156 -- insert_row - signature with "p_reference_path IN VARCHAR2"
157 --
158 -- PURPOSE
159 -- This procedure is used internally. It is used to insert
160 -- table: BSC_USER_KPILIST_PLUGS
161 --
162 -- PARAMETERS
163 --
164 -- HISTORY
165 -- 08-MAR-2003 Aditya Created.
166 --==========================================================================
167
168 PROCEDURE insert_row (
169 p_plug_id IN NUMBER,
170 p_reference_path IN VARCHAR2,
171 p_details_flag IN NUMBER,
172 p_group_flag IN NUMBER,
173 p_kpi_measure_details_flag IN NUMBER,
174 p_last_update_date IN DATE,
175 p_last_updated_by IN NUMBER
176 ) IS
177 insert_err EXCEPTION;
178 BEGIN
179 INSERT INTO BSC_USER_KPILIST_PLUGS (
180 PLUG_ID,
181 DETAILS_FLAG,
182 GROUP_FLAG,
183 KPI_MEASURE_DETAILS_FLAG,
184 CREATION_DATE,
185 CREATED_BY,
186 LAST_UPDATE_DATE,
187 LAST_UPDATED_BY,
188 LAST_UPDATE_LOGIN,
189 REFERENCE_PATH
190 ) VALUES (
191 p_plug_id,
192 p_details_flag,
193 p_group_flag,
194 p_kpi_measure_details_flag,
195 p_last_update_date,
196 p_last_updated_by,
197 p_last_update_date,
198 p_last_updated_by,
199 p_last_updated_by,
200 p_reference_path
201 );
202
203 IF SQL%ROWCOUNT = 0 THEN
204 RAISE insert_err;
205 END IF;
206
207 END insert_row;
208
209
210
211 --==========================================================================+
212 -- PROCEDURE
213 -- update_row
214 --
215 -- PURPOSE
216 -- This procedure is used internally. It is used to update
217 -- table: BSC_USER_KPILIST_PLUGS
218 --
219 -- PARAMETERS
220 --
221 -- HISTORY
222 -- 08-MAR-2001 juwang Created.
223 --==========================================================================
224
225 PROCEDURE update_row (
226 p_plug_id IN NUMBER,
227 p_details_flag IN NUMBER,
228 p_group_flag IN NUMBER,
229 p_kpi_measure_details_flag IN NUMBER,
230 p_last_update_date IN DATE,
231 p_last_updated_by IN NUMBER
232 ) IS
233
234 update_err EXCEPTION;
235 BEGIN
236 UPDATE
237 BSC_USER_KPILIST_PLUGS
238 SET
239 DETAILS_FLAG = p_details_flag,
240 GROUP_FLAG = p_group_flag,
241 KPI_MEASURE_DETAILS_FLAG = p_kpi_measure_details_flag,
242 LAST_UPDATE_DATE = SYSDATE,
243 LAST_UPDATED_BY = p_last_updated_by,
244 LAST_UPDATE_LOGIN = p_last_updated_by
245 WHERE
246 PLUG_ID = p_plug_id;
247
248 IF SQL%ROWCOUNT = 0 THEN
249 RAISE update_err;
250 END IF;
251 END update_row;
252
253 --==========================================================================+
254 -- PROCEDURE
255 -- update_row - updated with reference path
256 --
257 -- PURPOSE
258 -- This procedure is used internally. It is used to update
259 -- table: BSC_USER_KPILIST_PLUGS
260 --
261 -- PARAMETERS
262 --
263 -- HISTORY
264 -- 08-MAR-2003 Aditya Created with a new signature.
265 --==========================================================================
266
267 PROCEDURE update_row (
268 p_plug_id IN NUMBER,
269 p_reference_path IN VARCHAR2,
270 p_details_flag IN NUMBER,
271 p_group_flag IN NUMBER,
272 p_kpi_measure_details_flag IN NUMBER,
273 p_last_update_date IN DATE,
274 p_last_updated_by IN NUMBER
275 ) IS
276
277 update_err EXCEPTION;
278 BEGIN
279 UPDATE
280 BSC_USER_KPILIST_PLUGS
281 SET
282 DETAILS_FLAG = p_details_flag,
283 GROUP_FLAG = p_group_flag,
284 KPI_MEASURE_DETAILS_FLAG = p_kpi_measure_details_flag,
285 LAST_UPDATE_DATE = SYSDATE,
286 LAST_UPDATED_BY = p_last_updated_by,
287 LAST_UPDATE_LOGIN = p_last_updated_by
288 WHERE
289 PLUG_ID = p_plug_id
290 AND REFERENCE_PATH = p_reference_path;
291
292 IF SQL%ROWCOUNT = 0 THEN
293 RAISE update_err;
294 END IF;
295 END update_row;
296
297
298
299 --==========================================================================+
300 -- PROCEDURE
301 -- update_icx_portlet_cust - changed signature for reference_path
302 --
303 -- PURPOSE
304 -- This procedure is used internally. It is used to update
305 -- table: ICX_PORTLET_CUSTOMIZATIONS
306 --
307 -- PARAMETERS
308 --
309 -- HISTORY
310 -- 08-MAR-2003 Aditya Created.
311 --==========================================================================
312
313 PROCEDURE update_icx_portlet_cust (
314 p_plug_id IN NUMBER,
315 p_reference_path IN VARCHAR2,
316 p_resp_id IN NUMBER,
317 p_portlet_name IN VARCHAR
318 ) IS
319
320 update_err EXCEPTION;
321 BEGIN
322 -- Added by Aditya for bug #2891539
323 -- Remove cacheing everytime the portlet is cust.
324
325 UPDATE
326 ICX_PORTLET_CUSTOMIZATIONS
327 SET
328 CACHING_KEY = TO_CHAR(TO_NUMBER(NVL(caching_key, 0))+1)
329 , RESPONSIBILITY_ID = p_resp_id
330 , TITLE = p_portlet_name
331 , PLUG_ID = p_plug_id
332 WHERE
333 REFERENCE_PATH = p_reference_path;
334
335 IF SQL%ROWCOUNT = 0 THEN
336 RAISE update_err;
337 END IF;
338 END update_icx_portlet_cust;
339
340 --==========================================================================+
341 -- PROCEDURE
342 -- update_icx_portlet_cust
343 --
344 -- PURPOSE
345 -- This procedure is used internally. It is used to update
346 -- table: ICX_PORTLET_CUSTOMIZATIONS
347 --
348 -- PARAMETERS
349 --
350 -- HISTORY
351 -- 08-MAR-2001 juwang Created.
352 --==========================================================================
353
354 PROCEDURE update_indicators (
355 p_resp_id IN NUMBER,
356 p_last_update_date IN DATE,
357 p_last_updated_by IN NUMBER,
358 p_number_array IN BSC_NUM_LIST
359 ) IS
360
361 l_kpi_id Number;
362 l_paramlist_id Number;
363
364
365 CURSOR c IS
366 SELECT BK.INDICATOR
367 FROM
368 BSC_KPIS_B BK,
369 BSC_TAB_INDICATORS BTI,
370 BSC_USER_TAB_ACCESS BTA,
371 BSC_USER_KPI_ACCESS BA
372 WHERE
373 BA.RESPONSIBILITY_ID = p_resp_id AND
374 BA.INDICATOR IN (
375 SELECT
376 t.COLUMN_VALUE
377 FROM TABLE(CAST(p_number_array AS BSC_NUM_LIST)) t) AND
378 BA.INDICATOR = BK.INDICATOR AND
379 BTI.INDICATOR = BK.INDICATOR AND
380 BTA.TAB_ID = BTI.TAB_ID AND
381 BTA.RESPONSIBILITY_ID = BA.RESPONSIBILITY_ID AND
382 SYSDATE BETWEEN NVL(BTA.START_DATE(+), SYSDATE) AND
383 NVL(BTA.END_DATE(+), SYSDATE) AND
384 BK.PROTOTYPE_FLAG <> 2 AND
385 not exists (
386 SELECT BUP.INDICATOR
387 FROM BSC_USER_PARAMETERS_B BUP
388 WHERE BUP.INDICATOR = BA.INDICATOR AND
389 BUP. VIEW_TYPE = 1
390 )
391 ORDER BY BK.INDICATOR;
392
393
394
395 BEGIN
396
397
398 OPEN c;
399 LOOP
400 FETCH c INTO l_kpi_id;
401 EXIT WHEN c%NOTFOUND;
402 BSC_USER_PARAMETERS_PKG.INSERT_ROW(l_paramlist_id,
403 NULL,
404 BSC_PORTLET_KPILISTCUST.APPLICATION_ID,
405 l_kpi_id,
406 1,1,
407 NULL,NULL,NULL,NULL,NULL,
408 NULL,NULL,NULL,NULL,NULL,
409 NULL,NULL,NULL,NULL,NULL,
410 p_last_update_date,
411 p_last_updated_by,
412 p_last_update_date,
413 p_last_updated_by,
414 p_last_updated_by);
415 -- dbms_output.put_line('kpi =' || TO_CHAR(l_kpi_id));
416 END LOOP;
417 CLOSE c;
418 END update_indicators;
419
420
421
422 --==========================================================================+
423 -- PROCEDURE
424 -- insert_param_id
425 --
426 -- PURPOSE
427 -- This procedure is used internally. It is used to update
428 -- table: ICX_PORTLET_CUSTOMIZATIONS
429 --
430 -- PARAMETERS
431 --
432 -- HISTORY
433 -- 08-MAR-2001 juwang Created.
434 --==========================================================================
435
436 PROCEDURE insert_param_id(
437 p_plug_id IN NUMBER,
438 p_resp_id IN NUMBER,
439 p_last_update_date IN DATE,
440 p_last_updated_by IN NUMBER,
441 p_number_array IN BSC_NUM_LIST
442 ) IS
443
444
445
446 BEGIN
447
448 DELETE from bsc_user_kpilist_kpis
449 WHERE plug_id = p_plug_id;
450
451 INSERT INTO bsc_user_kpilist_kpis (
452 PLUG_ID,
453 PARAM_LIST_ID,
454 CREATION_DATE,
455 CREATED_BY,
456 LAST_UPDATE_DATE,
457 LAST_UPDATED_BY,
458 LAST_UPDATE_LOGIN
459 ) SELECT
460 p_plug_id,
461 bup.PARAM_LIST_ID,
462 p_last_update_date,
463 p_last_updated_by,
464 p_last_update_date,
465 p_last_updated_by,
466 p_last_updated_by
467 FROM bsc_user_parameters_b bup,
468 BSC_KPIS_B BK,
469 BSC_TAB_INDICATORS BTI,
470 BSC_USER_TAB_ACCESS BTA,
471 BSC_USER_KPI_ACCESS BA
472 WHERE
473 BA.RESPONSIBILITY_ID = p_resp_id AND
474 BA.INDICATOR IN (
475 SELECT
476 t.COLUMN_VALUE
477 FROM TABLE(CAST(p_number_array AS BSC_NUM_LIST)) t) AND
478 BA.INDICATOR = BK.INDICATOR AND
479 BTI.INDICATOR = BK.INDICATOR AND
480 BTA.TAB_ID = BTI.TAB_ID AND
481 BTA.RESPONSIBILITY_ID = BA.RESPONSIBILITY_ID AND
482 SYSDATE BETWEEN NVL(BTA.START_DATE(+), SYSDATE) AND
483 NVL(BTA.END_DATE(+), SYSDATE) AND
484 BK.PROTOTYPE_FLAG <> 2 AND
485 bup.INDICATOR = bk.INDICATOR AND
486 bup. VIEW_TYPE = 1;
487
488 -- bup.PARAM_LIST_ID NOT IN (
489 -- SELECT ek.PARAM_LIST_ID
490 -- FROM bsc_user_kpilist_kpis ek
491 -- WHERE ek.plug_id = p_plug_id
492 -- );
493 END insert_param_id;
494
495
496
497
498
499 --==========================================================================+
500 -- PROCEDURE
501 -- set_customized_data_private_n -Signature changed by Aditya
502 --
503 -- PURPOSE
504 -- This functions is use internally for Web Provider KpiList Portlet
505 --
506 -- PARAMETERS
507 --
508 -- HISTORY
509 -- 08-MAR-2003 Aditya Created. - Modified the signature
510 --==========================================================================
511 FUNCTION set_customized_data_private_n(
512 p_user_id IN NUMBER,
513 p_plug_id IN NUMBER,
514 p_reference_path IN VARCHAR2,
515 p_resp_id IN NUMBER,
516 p_details_flag IN NUMBER,
517 p_group_flag IN NUMBER,
518 p_kpi_measure_details_flag IN NUMBER,
522 p_number_array IN BSC_NUM_LIST,
519 p_createy_by IN NUMBER,
520 p_last_updated_by IN NUMBER,
521 p_porlet_name IN VARCHAR2,
523 p_o_ret_status OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
524
525 insert_err EXCEPTION;
526 update_err EXCEPTION;
527
528 l_errmesg VARCHAR2(2000) := bsc_portlet_util.MSGTXT_SUCCESS;
529 l_count NUMBER := 0;
530
531 BEGIN
532 SELECT count(*)
533 INTO l_count
534 FROM BSC_USER_KPILIST_PLUGS k
535 WHERE
536 k.PLUG_ID = p_plug_id;
537
538 --DBMS_OUTPUT.PUT_LINE('set_cust_n');
539 --DBMS_OUTPUT.PUT_LINE('l_count-->'||l_count);
540
541 ---------------------------------
542 -- update bsc_user_kpilist_plugs
543 ---------------------------------
544 IF (l_count > 0) THEN -- record exists, need to update
545 update_row(p_plug_id, p_reference_path ,p_details_flag, p_group_flag, p_kpi_measure_details_flag, sysdate, p_last_updated_by);
546 ELSE -- record does not exist, insert it
547 insert_row(p_plug_id, p_reference_path, p_details_flag, p_group_flag, p_kpi_measure_details_flag, sysdate, p_last_updated_by);
548 END IF; -- (l_count > 0)
549
550 -------------------------------------
551 -- update ICX_PORTLET_CUSTOMIZATIONS
552 -------------------------------------
553 --DBMS_OUTPUT.PUT_LINE('update_icx_portlet_cust');
554 update_icx_portlet_cust(p_plug_id, p_reference_path, p_resp_id, p_porlet_name);
555 --DBMS_OUTPUT.PUT_LINE('update_indicators');
556 update_indicators(p_resp_id, sysdate, p_last_updated_by, p_number_array);
557 --DBMS_OUTPUT.PUT_LINE('insert_param_id');
558 insert_param_id(p_plug_id, p_resp_id, sysdate, p_last_updated_by, p_number_array);
559
560
561 -- everything works ok so we commit
562 COMMIT;
563 p_o_ret_status := bsc_portlet_util.CODE_RET_SUCCESS;
564 RETURN bsc_portlet_util.MSGTXT_SUCCESS;
565
566 EXCEPTION
567
568 WHEN insert_err THEN
569 ROLLBACK;
570 p_o_ret_status := bsc_portlet_util.CODE_RET_ERROR;
571 l_errmesg := 'Error inserting to BSC_USER_KPILIST_PLUGS';
572 RETURN l_errmesg;
573
574
575 WHEN update_err THEN
576 ROLLBACK;
577 p_o_ret_status := bsc_portlet_util.CODE_RET_ERROR;
578 l_errmesg := 'Error updating to BSC_USER_KPILIST_PLUGS';
579 RETURN l_errmesg;
580
581 WHEN OTHERS THEN
582 ROLLBACK;
583 p_o_ret_status := bsc_portlet_util.CODE_RET_ERROR;
584 l_errmesg := 'Error in bsc_portlet_graph.set_customized_data_private. SQLERRM = ' || SQLERRM;
585 RETURN l_errmesg;
586
587
588
589 END set_customized_data_private_n;
590
591
592
593 --==========================================================================+
594 -- FUNCTION
595 -- get_customization
596 --
597 -- PURPOSE
598 -- This function is used by
599 -- oracle.apps.bsc.iviewer.thinext.client.OPortalDataExtractor
600 -- class.
601 -- PARAMETERS
602 -- p_has_selected_kpi : 1=>TRUE, 0->FALSE
603 -- HISTORY
604 -- 08-MAR-2001 juwang Created.
605
606 --==========================================================================
607 FUNCTION get_customization(
608 p_cookie_value IN VARCHAR2,
609 p_encrypted_plug_id IN VARCHAR2,
610 p_portlet_id IN NUMBER,
611 p_resp_id OUT NOCOPY NUMBER,
612 p_plug_id OUT NOCOPY NUMBER,
613 p_user_id OUT NOCOPY NUMBER,
614 p_details_flag OUT NOCOPY NUMBER,
615 p_group_flag OUT NOCOPY NUMBER,
616 p_display_name OUT NOCOPY VARCHAR2,
617 p_has_selected_kpi OUT NOCOPY NUMBER,
618 p_kpi_measure_details_flag OUT NOCOPY NUMBER) RETURN NUMBER IS
619
620 l_session_id NUMBER;
621 l_num_sel_kpis NUMBER;
622
623 -- bug fix 2072699
624
625 CURSOR c_kg_p IS
626 SELECT p.RESPONSIBILITY_ID, NVL(k.DETAILS_FLAG, 0), NVL(k.GROUP_FLAG, 0), p.TITLE, NVL(k.kpi_measure_details_flag, 0)
627 FROM bsc_user_kpilist_plugs k,
628 icx_portlet_customizations p
629 WHERE
630 p.PLUG_ID = p_plug_id AND
631 k.PLUG_ID(+)= p.PLUG_ID;
632
633
634 CURSOR c_fm IS
635 SELECT USER_FUNCTION_NAME
636 FROM FND_FORM_FUNCTIONS_VL
637 WHERE FUNCTION_ID = p_portlet_id;
638
639
640 BEGIN
641
642
643 bsc_portlet_util.decrypt_plug_info(p_cookie_value,
644 p_encrypted_plug_id, l_session_id, p_plug_id);
645
646
647 IF icx_sec.validateSessionPrivate(l_session_id) THEN
648 p_user_id := icx_sec.getID(icx_sec.PV_USER_ID,'', l_session_id);
649
650
651 OPEN c_kg_p;
652 FETCH c_kg_p INTO p_resp_id, p_details_flag, p_group_flag, p_display_name, p_kpi_measure_details_flag;
653
654 IF c_kg_p%FOUND THEN -- the record is found,
655
656
657 -- checks if display name is null
658 IF (p_display_name IS NULL) THEN
659 OPEN c_fm;
660 FETCH c_fm INTO p_display_name;
661 CLOSE c_fm;
662 END IF; -- (p_display_name IS NULL)
663 CLOSE c_kg_p;
664
665 -- checks if there is any selected kpis
666 SELECT COUNT(*) INTO l_num_sel_kpis
667 FROM bsc_user_kpilist_kpis
668 WHERE plug_id = p_plug_id;
669
670
671 IF (l_num_sel_kpis = 0 ) THEN
672 p_has_selected_kpi := 0;
673 ELSE
674 p_has_selected_kpi := 1;
675 END IF; -- (l_num_sel_kpis = 0 )
676 RETURN bsc_portlet_util.CODE_RET_SUCCESS;
677
678 ELSE -- not found, no such plug i
679 CLOSE c_kg_p;
680 RETURN bsc_portlet_util.CODE_RET_NOROW;
681
682 END IF; -- c_kg_p%FOUND
683
684 ELSE -- session expires
685 RETURN bsc_portlet_util.CODE_RET_SESSION_EXP;
686 END IF; -- icx_sec.validateSessionPrivate(l_session_id)
687 EXCEPTION
688 WHEN OTHERS THEN
689 --close the open cursors if any.
690 IF(c_kg_p%ISOPEN) THEN
691 CLOSE c_kg_p;
692 END IF;
693 IF(c_fm%ISOPEN) THEN
694 CLOSE c_fm;
695 END IF;
696 END get_customization;
697
698
699
700
701 end BSC_PORTLET_KPILISTCUST;