[Home] [Help]
PACKAGE BODY: APPS.BSC_PORTLET_UTIL
Source
1 PACKAGE BODY bsc_portlet_util AS
2 /* $Header: BSCPUTLB.pls 120.0.12000000.2 2007/01/30 08:49:55 nkishore ship $ */
3
4 FUNCTION is_alignment_needed RETURN BOOLEAN;
5
6 --==========================================================================+
7 -- FUNCTION
8 -- get_jsp_server
9 --
10 -- PURPOSE
11 -- Returns jsp host.
12 -- Ex: ap100jvm.us.oracle.com
13 --
14 -- PARAMETERS
15
16 -- HISTORY
17 -- 05-JULY-01 juwang Created.
18 -- 12-SEP-01 juwang Created.
19 --==========================================================================
20
21 FUNCTION get_jsp_server RETURN VARCHAR2
22 IS
23 ws_url VARCHAR2(2000);
24 hostname VARCHAR2(2000);
25 index1 NUMBER;
26 index2 NUMBER;
27
28 BEGIN
29 ws_url := FND_WEB_CONFIG.JSP_AGENT; /* 'http://serv:port/OA_HTML/' */
30 if ( ws_url is null ) then
31 return null;
32 else
33 index1 := INSTRB(ws_url, '//', 1) + 2; /* skip 'http://' */
34 index2 := INSTRB(ws_url, '/', index1); /* 'http://serv:port/' */
35 hostname := SUBSTRB(ws_url, 1, index2);
36
37 --dbms_output.put_line('get_jsp_server= ' || hostname);
38 return hostname;
39 end if;
40 END get_jsp_server;
41
42
43
44
45 --==========================================================================+
46 -- FUNCTION
47 -- get_webdb_host
48 --
49 -- PURPOSE
50 -- Returns web db host.
51 -- Ex: ap100jvm.us.oracle.com
52 --
53 -- PARAMETERS
54
55 -- HISTORY
56 -- 05-JULY-01 juwang Created.
57 --==========================================================================
58
59 FUNCTION get_webdb_host RETURN VARCHAR2
60 IS
61 ws_url VARCHAR2(2000);
62 hostname VARCHAR2(2000);
63 index1 NUMBER;
64 index2 NUMBER;
65
66 BEGIN
67
68 ws_url := FND_WEB_CONFIG.WEB_SERVER; -- ex : 'http://ap100jvm.us.oracle.com:8724/';
69
70
71 index1 := INSTRB(ws_url, '//', 1) + 2; -- skip 'http://'
72 index2 := INSTRB(ws_url, ':', index1);
73
74
75 IF index2 = 0 THEN -- ex : 'http://ap100jvm.us.oracle.com/';
76 hostname := SUBSTRB(ws_url, index1, length(ws_url)-index1);
77 ELSE
78 hostname := SUBSTRB(ws_url, index1, index2-index1);
79 END IF;
80
81
82 RETURN hostname;
83 ---------------------------------------------------------------------
84 -- testing
85 -- dbms_output.put_line('FND_WEB_CONFIG.WEB_SERVER= ' || ws_url);
86 -- dbms_output.put_line('second pos= ' || index2);
87 -- dbms_output.put_line('host name= ' || hostname);
88 ---------------------------------------------------------------------
89
90
91 END get_webdb_host;
92
93
94 --==========================================================================+
95 -- FUNCTION
96 -- get_webdb_port
97 --
98 -- PURPOSE
99 -- Returns web db port.
100 -- Ex: 8724
101 --
102 -- PARAMETERS
103
104 -- HISTORY
105 -- 05-JULY-01 juwang Created.
106 --==========================================================================
107
108 FUNCTION get_webdb_port RETURN VARCHAR2
109 IS
110 ws_url VARCHAR2(2000);
111 portno VARCHAR2(500);
112 index1 NUMBER;
113 index2 NUMBER;
114
115 BEGIN
116
117 ws_url := FND_WEB_CONFIG.WEB_SERVER; -- ex : 'http://ap100jvm.us.oracle.com:8724/';
118
119
120 index1 := INSTRB(ws_url, '//', 1) + 2; -- skip 'http://'
121 index2 := INSTRB(ws_url, ':', index1);
122
123
124 IF index2 = 0 THEN -- ex : 'http://ap100jvm.us.oracle.com/';
125 portno := '80';
126 ELSE
127 portno := SUBSTRB(ws_url, index2+1, length(ws_url)-index2-1);
128 END IF;
129
130 RETURN portno;
131 ---------------------------------------------------------------------
132 -- testing
133 -- dbms_output.put_line('FND_WEB_CONFIG.WEB_SERVER= ' || ws_url);
134 -- dbms_output.put_line('second pos= ' || index2);
135 -- dbms_output.put_line('host name= ' || hostname);
136 -- dbms_output.put_line('port name= ' || portno);
137 ---------------------------------------------------------------------
138
139
140 END get_webdb_port;
141
142
143 --==========================================================================+
144 -- FUNCTION
145 -- get_bsc_url
146 --
147 -- PURPOSE
148 -- This procedure returns the url as the following format
149 -- http://[apache_host]:[apache_port]/jsp/bsc/[p_jsp_name]?
150 -- [base_params]&[p_ext_params].
151 -- If the given jsp name, p_jsp_name, is not specified,
152 -- BscInit.jsp will be the defaulted jsp file name.
153 -- If [p_extra_params is not specified, the format will be
154 -- http://[apache_host]:[apache_port]/jsp/bsc/[p_jsp_name]?[base_params]
155 -- Ex: http://ap100jvm.us.oracle.com:8792/jsp/bsc/BscInit.jsp
156 --
157 -- PARAMETERS
158 -- p_session_id : Portlet session id
159 -- p_jsp_name : Jsp name
160 -- p_ext_params : Extra parameters list delimitered by '&'.
161 -- p_is_respid_used : append responsibilityId=l_resp_id at url
162 -- parmaters list if p_is_respid_used equals TRUE.
163 -- HISTORY
164 -- 08-MAR-2001 juwang Created.
165 --==========================================================================
166
167 FUNCTION get_bsc_url(
168 p_session_id IN NUMBER,
169 p_plug_id IN NUMBER,
170 p_jsp_name IN VARCHAR2,
171 p_ext_params IN VARCHAR2,
172 p_is_respid_used IN BOOLEAN
173 ) RETURN VARCHAR2 IS
174
175
176 l_webdb_host VARCHAR2(500) := NULL;
177 l_webdb_port VARCHAR2(500) := NULL;
178 l_resp_id VARCHAR2(30) := NULL;
179 l_init_path VARCHAR2(500) := NULL;
180 l_dbcFilePath VARCHAR2(128) := NULL;
181 l_sessionCookieValue VARCHAR2(128) := NULL;
182 l_parameter VARCHAR2(500) := NULL;
183 l_url VARCHAR2(2000) := NULL;
184 l_jsp_name VARCHAR2(50) := NULL;
185
186 BEGIN
187
188 -----------------------------------------------------------------
189 -- Check the paramters passed are correct.
190 -----------------------------------------------------------------
191
192 IF ( p_jsp_name IS NULL ) THEN
193 l_jsp_name := 'BscInit.jsp';
194 ELSE
195 l_jsp_name := p_jsp_name;
196 END IF; -- (p_jsp_name IS NULL)
197
198 -----------------------------------------------------------------
199 -- Retrive information from profile options.
200 -----------------------------------------------------------------
201
202 -- it is a valid session
203 l_webdb_host := bsc_portlet_util.get_webdb_host;
204 l_webdb_port := bsc_portlet_util.get_webdb_port;
205
206 l_init_path := bsc_portlet_util.get_bsc_jsp_path || l_jsp_name;
207
208 -----------------------------------------------------------------
209 -- A valid session
210 -----------------------------------------------------------------
211
212 l_dbcFilePath := FND_WEB_CONFIG.DATABASE_ID;
213
214 -- !!! need to find out NOCOPY p_ticket
215 l_sessionCookieValue := ICX_CALL.ENCRYPT3(ICX_SEC.getsessioncookie(ICX_CALL.ENCRYPT3(p_session_id)));
216 -- l_sessionCookieValue := ICX_CALL.ENCRYPT3(p_session_id);
217
218
219 -----------------------------------------------------------------
220 -- Construct the parameter string
221 -----------------------------------------------------------------
222
223
224 l_parameter := 'DBC_FILE=' || l_dbcFilePath || '&' ||
225 'webHost=' || l_webdb_host || '&' ||
226 'webPort=' || l_webdb_port || '&' ||
227 'SessionCookieValue=' || l_sessionCookieValue;
228
229
230 IF ( p_is_respid_used ) THEN
231 l_resp_id := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID,'',p_session_id);
232
233 l_parameter := l_parameter || '&' ||
234 bsc_portlet_util.PR_RESPID || '=' || l_resp_id;
235 END IF; -- (p_is_respid_used )
236
237
238 IF (p_ext_params IS NOT NULL) THEN
239 l_parameter := l_parameter || '&' || p_ext_params;
240
241 END IF; -- (p_ext_params IS NOT NULL)
242 l_url:= l_init_path || '?' || l_parameter;
243
244 RETURN l_url;
245
246 EXCEPTION
247
248
249 WHEN OTHERS THEN
250 RETURN l_init_path;
251
252 END get_bsc_url;
253
254
255
256
257 --==========================================================================
258 -- FUNCTION
259 -- get_bsc_jsp_path
260 --
261 -- PURPOSE
262 -- This procedure returns the url path to Balanced Scorecard
263 -- jsp files located.
264 -- Ex: http://ap100jvm.us.oracle.com:8791/OA_HTML/jsp/bsc/
265 --
266 -- PARAMETERS
267 --
268 -- HISTORY
269 -- 08-MAR-2001 juwang Created.
270 --==========================================================================
271
272 FUNCTION get_bsc_jsp_path RETURN VARCHAR2 IS
273
274 l_servlet_agent varchar2(500) :=NULL;
275
276 l_jsp_path VARCHAR2(500) := NULL;
277 l_url VARCHAR2(500):=NULL;
278
279 BEGIN
280
281 -- for testing oa frame work purpose
282 --l_servlet_agent := fnd_profile.value('APPS_FRAMEWORK_AGENT') || 'OA_HTML/' ;
283
284 -------------------------------------------------------------
285 -- Uses 'APPS_WEB_AGENT' only if 'APPS_SERVLET_AGENT' is null
286 -------------------------------------------------------------
287
288 l_servlet_agent := FND_WEB_CONFIG.JSP_AGENT; -- 'http://serv:port/OA_HTML/'
289 l_jsp_path := 'jsp/bsc/';
290
291 if ( l_servlet_agent is null ) then -- 'APPS_SERVLET_AGENT' is null
292 l_servlet_agent := FND_WEB_CONFIG.WEB_SERVER;
293 l_jsp_path := 'OA_HTML/jsp/bsc/';
294 end if;
295
296
297 l_url := l_servlet_agent || l_jsp_path;
298
299 RETURN l_url;
300
301 EXCEPTION
302
303 WHEN OTHERS THEN
304 RETURN l_jsp_path;
305
306 END get_bsc_jsp_path;
307
308
309 --==========================================================================
310 -- FUNCTION
311 -- update_portlet_name
312 --
313 -- PURPOSE
314 --
315 -- PARAMETERS
316 --
317 -- HISTORY
318 -- 12-MAR-2001 juwang Created.
319 --==========================================================================
320
321 PROCEDURE update_portlet_name(
322 p_user_id IN NUMBER,
323 p_plug_id IN pls_integer,
324 p_display_name IN VARCHAR2
325 ) IS
326
327 BEGIN
328
329 UPDATE
330 icx_page_plugs
331 SET
332 DISPLAY_NAME = p_display_name,
333 LAST_UPDATE_DATE = SYSDATE,
334 LAST_UPDATED_BY = p_user_id
335
336 WHERE
337 PLUG_ID = p_plug_id;
338
339
340
341 END update_portlet_name;
342
343
344 --==========================================================================
345 -- FUNCTION
346 -- update_portlet_name
347 --
348 -- PURPOSE
349 --
350 -- PARAMETERS
351 --
352 -- HISTORY
353 -- 12-MAR-2001 juwang Created.
354 --==========================================================================
355
356 PROCEDURE gotoMainMenu(
357 p_cookie_value IN VARCHAR2,
358 p_encrypted_plug_id IN VARCHAR2) IS
359
360 l_session_id NUMBER := -1;
361 l_plug_id NUMBER := -1;
362
363 BEGIN
364 bsc_portlet_util.decrypt_plug_info(p_cookie_value,
365 p_encrypted_plug_id, l_session_id, l_plug_id);
366
367
368 IF icx_sec.validateSessionPrivate(c_session_id =>l_session_id) THEN
369 icx_plug_utilities.gotoMainMenu;
370 END IF;
371
372
373 icx_plug_utilities.gotoMainMenu;
374 END gotoMainMenu;
375
376
377
378
379
380 --==========================================================================+
381 -- PROCEDURE
382 -- decrypt_plug_info
383 --
384 -- PURPOSE
385 -- This procedure decrypts the session id and
386 -- plug id.
387 -- PARAMETERS
388 --
389 -- HISTORY
390 -- 15-MAR-2001 juwang Created.
391 --==========================================================================
392
393 PROCEDURE decrypt_plug_info(
394 p_cookie_value IN VARCHAR2,
395 p_encrypted_plug_id IN VARCHAR2,
396 p_session_id OUT NOCOPY NUMBER,
397 p_plug_id OUT NOCOPY NUMBER) IS
398 BEGIN
399
400
401 p_session_id := icx_call.decrypt3(p_cookie_value);
402 p_plug_id := icx_call.decrypt3(p_encrypted_plug_id);
403
404 END decrypt_plug_info;
405
406
407
408 --==========================================================================+
409 -- PROCEDURE
410 -- request_html_pieces
411 --
412 -- PURPOSE
413 -- bug fix for 2235651
414 --
415 -- PARAMETERS
416 --
417 -- HISTORY
418 -- 17-OCT-2001 juwang Created.
419 --==========================================================================
420
421 FUNCTION request_html_pieces(
422 p_url IN VARCHAR2,
423 p_proxy IN VARCHAR2 DEFAULT NULL
424 ) RETURN utl_http.html_pieces IS
425 l_wallet_path VARCHAR2(2000);
426 l_wallet_password VARCHAR2(2000);
427 l_pieces utl_http.html_pieces;
428 BEGIN
429 IF INSTR(UPPER(p_url), 'HTTPS://') > 0 THEN
430 --l_wallet_path := ''; --fnd_profile.value('BSC_WALLET_PATH');
431 --l_wallet_password := '';--fnd_profile.value('BSC_WALLET_PASSWORD');
432 l_pieces := utl_http.request_pieces(
433 url => p_url,
434 max_pieces => 32000,
435 proxy => p_proxy,
436 wallet_path => '',
437 wallet_password => '');
438 ELSE
439 l_pieces := utl_http.request_pieces(
440 url => p_url,
441 max_pieces => 32000,
442 proxy => p_proxy);
443 END IF;
444
445 -- Check if the HTML pieces need to be realigned
446 IF is_alignment_needed THEN
447 RETURN re_align_html_pieces(l_pieces);
448 ELSE
449 RETURN l_pieces;
450 END IF;
451 END request_html_pieces;
452
453
454
455 --==========================================================================+
456 -- PROCEDURE
457 -- re_align_html_pieces
458 --
459 -- PURPOSE
460 -- bug fix for 1994245
461 --
462 -- PARAMETERS
463 --
464 -- HISTORY
465 -- 17-OCT-2001 juwang Created.
466 -- 08-JAN-03 Adeulgao fixed Bug #2728074
467 -- 13-JAN-03 Pradeep fixed Bug #2732070
468 --==========================================================================
469
470 ----------------------------------------------------------------------------
471 -- !!! Do not use in 9i !!!
472 FUNCTION re_align_html_pieces(src IN utl_http.html_pieces) RETURN
473 utl_http.html_pieces
474 AS
475 dst utl_http.html_pieces;
476 buf RAW(2000);
477 src_row PLS_INTEGER;
478 src_pos PLS_INTEGER;
479 dst_row PLS_INTEGER;
480 len PLS_INTEGER;
481 cut_len PLS_INTEGER;
482 BEGIN
483
484 src_row := 1; src_pos := 1; dst_row := 1;
485 LOOP
486 -- fill bytes from the source till buf is full
487 BEGIN
488 LOOP
489 len := utl_raw.length(buf);
490 EXIT WHEN (len = 2000);
491 cut_len := 2000 - nvl(len,0); -- when buff is NULL len becomes NULL which causes the infinite loop
492 IF (cut_len > (lengthb(src(src_row)) - src_pos + 1)) THEN
493 cut_len := lengthb(src(src_row)) - src_pos + 1;
494 END IF;
495 buf := utl_raw.concat(buf, utl_raw.substr(
496 utl_raw.cast_to_raw(src(src_row)), src_pos, cut_len));
497 src_pos := src_pos + cut_len;
498 IF (src_pos > lengthb(src(src_row))) THEN
499 src_row := src_row + 1;
500 src_pos := 1;
501 END IF;
502 END LOOP;
503 EXCEPTION
504 WHEN no_data_found THEN
505 EXIT WHEN utl_raw.length(buf) IS NULL;
506 END;
507
508 -- extract from buf at character boundary
509 len := lengthb(substr(utl_raw.cast_to_varchar2(buf), 1,
510 length(utl_raw.cast_to_varchar2(buf))));
511
512 EXIT WHEN nvl(len,0) = 0; -- bug#2765446
513
514 dst(dst_row) := utl_raw.cast_to_varchar2(utl_raw.substr(buf, 1, len));
515 IF (len < utl_raw.length(buf)) THEN
516 buf := utl_raw.substr(buf, len + 1);
517 ELSE
518 buf := NULL;
519 END IF;
520 dst_row := dst_row + 1;
521 END LOOP;
522
523 RETURN dst;
524
525 END;
526
527 PROCEDURE test_https(url IN VARCHAR2) IS
528 l_pieces utl_http.html_pieces;
529 BEGIN
530 l_pieces := request_html_pieces('https://' || url || '/');
531 htp.p(l_pieces.count || ' pieces retreived.');
532 EXCEPTION
533 WHEN OTHERS THEN
534 htp.p('ERROR');
535 END test_https;
536
537
538 ----------------------------------------------------------------------------
539 -- Alignment of HTML pieces is needed in 8i only
540 FUNCTION is_alignment_needed RETURN BOOLEAN IS
541 l_count NUMBER;
542 BEGIN
543 SELECT count(*) INTO l_count
544 FROM v$instance VI
545 WHERE VI.instance_role = 'PRIMARY_INSTANCE'
546 AND trim(VI.version) like '8.%';
547
548 IF l_count > 0 THEN
549 RETURN TRUE;
550 ELSE
551 RETURN FALSE;
552 END IF;
553 END is_alignment_needed;
554
555
556
557 --==========================================================================+
558 -- PROCEDURE
559 -- getValue
560 --
561 -- PURPOSE
562 -- For example, given
563 -- p_key => p2
564 -- p_parameters => p1=v1&p2=v2&p3=v3&p4=v4
565 -- This function will return
566 -- v2
567 -- If either p_key is null or p_parameters is null, return null
568 -- PARAMETERS
569 --
570 -- HISTORY
571 -- 11-DEC-2001 juwang Created.
572 --==========================================================================
573 FUNCTION getValue(
574 p_key IN VARCHAR2
575 ,p_parameters IN VARCHAR2
576 ,p_delimiter IN VARCHAR2 := '&'
577 ) RETURN VARCHAR2
578
579 IS
580 l_key VARCHAR2(2000);
581 l_parameters VARCHAR2(2000);
582 l_key_start NUMBER;
583 l_value_start NUMBER;
584 l_amp_start NUMBER;
585
586 l_val VARCHAR2(2000);
587 BEGIN
588 IF ( (p_key IS NULL) or (p_parameters IS NULL)) THEN
589
590 RETURN NULL;
591 END IF;
592
593 l_key := UPPER(p_key);
594 l_parameters := UPPER(p_parameters);
595 -- dbms_output.put_line('p_parameters='|| p_parameters);
596 -- first occurance
597 l_key_start := INSTRB(l_parameters, RTRIM(l_key)|| '=', 1);
598 -- dbms_output.put_line('l key start='||l_key_start);
599 IF (l_key_start = 0) THEN -- key not found
600 RETURN NULL;
601 END IF;
602
603 -- get the starting position of v2 in "p2=v2"
604 l_value_start := l_key_start + LENGTHB(p_key)+1; -- including c_eq
605 l_amp_start := INSTRB(p_parameters, p_delimiter, l_value_start);
606
607
608 IF (l_amp_start = 0) THEN -- the last one or key not found
609 l_val := SUBSTRB(p_parameters, l_value_start);
610 ELSE
611 l_val := SUBSTRB(p_parameters, l_value_start, (l_amp_start - l_value_start));
612 END IF;
613 RETURN l_val;
614
615
616 EXCEPTION
617 WHEN OTHERS THEN
618 RETURN NULL;
619
620 END getValue;
621
622
623 END bsc_portlet_util;