1 Package Body BIS_PMV_UTIL as
2 /* $Header: BISPMVUB.pls 120.4.12010000.2 2008/08/12 07:36:02 bijain ship $ */
3 -- dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
4 -- dbdrv: checkfile(120.4.12000000.2=120.5):~PROD:~PATH:~FILE
5 -- Purpose: Briefly explain the functionality of the package body
6 --
7 -- MODIFICATION HISTORY
8 -- Person Date Comments
9 -- aleung 7/13/01 initial creation
10 -- amkulkar 7/18/01 Added function to sort attribute codes and values
11 -- mdamle 11/08/01 Added getReportRegion function
12 -- mdamle 12/05/01 Added getFormattedDate
13 -- mdamle 12/12/01 Added getParameterValue
14 -- mdamle 12/19/01 Added getDefaultResponsibility
15 -- nbarik 03/09/02 Bug Fix 2503143 Added getICXCurrentDateTime
16 -- nbarik 01/10/03 Enhancement : 2638594 - Portlet Builder
17 -- Added function getRegionApplicationId
18 -- nbarik 04/07/03 Bug Fix 2871424 - strip the quotes in bind variables
19 -- nkishore 04/25/03 BugFix 2823330 Get lastrefreshDate
20 -- ansingh 06/09/03 BugFix 2995675 Staling of RL Portlets based on PlugId
21 -- nbarik 08/21/03 Bug Fix 3099831 - Add hasFunctionAccess
22 -- mdamle 06/18/04 Return -1 if region not found - getRegionApplicationId()
23 -- mdamle 08/04/04 Added getRegionDataSourceType
24 -- smargand 09/21/04 Bug Fix: 3902169 -- Live Portlet fix
25 -- --------- ------- ------------------------------------------
26 function readFndLobs (pFileId in number) return lob_varchar_pieces is
27 loc BLOB;
28 amount binary_integer := 0;
29 maxamount binary_integer := 8000;
30 offset integer := 1;
31 output varchar2(32000);
32 vIndex number := 1;
33 vLobPieces lob_varchar_pieces;
34 begin
35 -- get file location
36 begin
37 select file_data
38 into loc
39 from FND_LOBS
40 where file_id = pFileId;
41 exception
42 when others then
43 htp.p(SQLERRM);
44 end;
45
46 amount := DBMS_LOB.getlength(loc);
47 -- read html codes from that location
48 while amount > maxamount
49 loop
50 DBMS_LOB.READ(loc, maxamount, offset, output);
51 vLobPieces(vIndex) := utl_raw.cast_to_varchar2(output);
52 vIndex := vIndex + 1;
53 amount := amount - maxamount;
54 offset := offset + maxamount;
55 end loop;
56
57 if amount > 0 then
58 DBMS_LOB.READ(loc, amount, offset, output);
59 vLobPieces(vIndex) := utl_raw.cast_to_varchar2(output);
60 end if;
61
62 return vLobPieces;
63
64 exception
65 when others then
66 htp.print('In bis_pmv_util.readFndLobs: '||SQLERRM);
67 end readFndLobs;
68 --
69 --ashgarg Bug Fix: 4526317
70 PROCEDURE RETRIEVE_DATA
71 (document_id IN NUMBER
72 ,document IN OUT NOCOPY VARCHAR2
73 )
74 IS
75 l_Document VARCHAR2(32000);
76 l_html_pieces BIS_PMV_UTIL.lob_varchar_pieces;
77 l_count NUMBER;
78
79 BEGIN
80
81 select count(*)
82 into l_count
83 from fnd_lobs
84 where file_id = document_id;
85
86 if l_count > 0 then
87 l_html_pieces := BIS_PMV_UTIL.readfndlobs(document_id);
88 FOR l_count IN 1..l_html_pieces.COUNT LOOP
89 l_document := l_document || l_html_pieces(l_count);
90 END LOOP;
91 document := l_document;
92 end if;
93
94 END;
95 --
96 procedure sortAttributeCode
97 (p_attributeCode_tbl in OUT NOCOPY BISVIEWER.t_char
98 ,p_attributeValue_tbl in OUT NOCOPY BISVIEWER.t_char
99 ,x_return_status OUT NOCOPY VARCHAR2
100 ,x_msg_count OUT NOCOPY NUMBER
101 ,x_msg_data OUT NOCOPY VARCHAR2
102 )
103 IS
104 l_count NUMBER;
105 l_length_tbl BISVIEWER.t_num;
106 l_temp_attr VARCHAR2(32000);
107 l_temp_value VARCHAR2(32000);
108 l_temp_length NUMBER;
109
110 BEGIN
111 --First get the lengths of all the attribute codes in an array.
112 --This is what we will be sorting in descending order.
113 IF (p_attributeCode_tbl.COUNT > 0) THEN
114 FOR l_count IN 1..p_attributeCode_tbl.COUNT LOOP
115 l_length_tbl(l_count) := length(p_attributeCode_tbl(l_count));
116 END LOOP;
117 END IF;
118 --Now that we have the lengths of all these Attribute Codes let's sort
119 --them in descending order
120 FOR i IN l_length_tbl.FIRST+1..l_length_Tbl.LAST LOOP
121 l_temp_attr := p_attributeCode_tbl(i);
122 l_temp_value := p_attributeValue_tbl(i);
123 l_temp_length := l_length_tbl(i);
124 FOR j IN REVERSE l_length_Tbl.FIRST..(i-1) LOOP
125 if l_length_tbl(j) < l_temp_length THEN
126 l_length_tbl(j+1) := l_length_tbl(j);
127 l_length_tbl(j) := l_temp_length;
128 p_attributeCode_tbl(j+1) := p_attributeCode_tbl(j);
129 p_attributeCode_tbl(j) := l_temp_attr;
130 p_attributeValue_tbl(j+1) := p_attributeValue_tbl(j);
131 p_attributeValue_tbl(j) := l_temp_value;
132 end if;
133 END LOOP;
134 END LOOP;
135 END;
136
137 procedure getCurrentDateTime (x_current_date_time out NOCOPY varchar2,
138 x_current_date out NOCOPY varchar2,
139 x_current_hour out NOCOPY varchar2,
140 x_current_minute out NOCOPY varchar2) is
141 begin
142
143 x_current_date_time := fnd_date.date_to_charDT(SYSDATE);
144
145 x_current_date := fnd_date.date_to_chardate(SYSDATE);
146
147 select to_char(SYSDATE, 'HH24')
148 into x_current_hour
149 from dual;
150
151 select to_char(SYSDATE, 'MI')
152 into x_current_minute
153 from dual;
154
155 end getCurrentDateTime;
156
157 --Bug Fix 2503143 nbarik 03/sep/2002
158 PROCEDURE getICXCurrentDateTime( p_icx_date_format IN VARCHAR2,
159 x_current_date_time OUT NOCOPY VARCHAR2,
160 x_current_date OUT NOCOPY VARCHAR2,
161 x_current_hour OUT NOCOPY VARCHAR2,
162 x_current_minute OUT NOCOPY VARCHAR2) IS
163 l_date DATE;
164 l_default_date_format VARCHAR2(15) := 'DD-MON-RR';
165 BEGIN
166 -- get the date once
167 l_date := SYSDATE;
168 IF (p_icx_date_format IS NOT NULL) THEN
169 x_current_date_time := to_char(l_date, p_icx_date_format || ' HH24:MI:SS');
170 x_current_date := to_char(l_date, p_icx_date_format);
171 ELSE
172 x_current_date_time := to_char(l_date, l_default_date_format || ' HH24:MI:SS');
173 x_current_date := to_char(l_date, l_default_date_format);
174 END IF;
175
176 x_current_hour := to_char(l_date, 'HH24');
177
178 x_current_minute := to_char(l_date, 'MI');
179
180 END getICXCurrentDateTime;
181
182
183 function getAppendTitle(pRegionCode in varchar2) return varchar2 is
184 l_append_title varchar2(2000);
185 l_function varchar2(2000);
186 begin
187 select name
188 into l_function
189 from ak_regions_vl
190 where region_code = pRegionCode;
191
192 l_function := substr(l_function, instr(l_function, '[')+1, instr(l_function, ']')-instr(l_function,'[')-1);
193 if l_function is not null then
194 l_function := 'select '||l_function||' from dual';
195 begin
196 execute immediate l_function into l_append_title;
197 exception
198 when others then
199 null;
200 end;
201 end if;
202 return l_append_title;
203 exception
204 when others then
205 return null;
206 end getAppendTitle;
207
208 procedure getReportTitle
209 (pFunctionName IN VARCHAR2
210 ,pRegionCode IN VARCHAR2 default null
211 ,pRegionName IN VARCHAR2 default null
212 ,xTitleString OUT NOCOPY VARCHAR2
213 ,xBrowserTitle OUT NOCOPY VARCHAR2
214 )
215 IS
216 l_report_Title VARCHAR2(32000);
217 l_report_currency VARCHAR2(32000);
218 BEGIN
219 l_Report_Title := BIS_REPORT_UTIL_PVT.get_report_title(pFunctionName)||getAppendTitle(pRegionCode);
220 l_report_currency := BIS_REPORT_UTIL_PVT.get_report_currency;
221 xBrowserTitle := l_Report_Title;
222 --replaced showTitleDateCurrency with showTitleWithoutDateCurrency gsanap 6/19/02
223 BIS_REPORT_UTIL_PVT.showTitleWithoutDateCurrency(l_report_title, l_report_currency, xTitleString);
224 --BIS_REPORT_UTIL_PVT.showTitleDateCurrency(l_report_title, l_report_currency, xTitleString);
225 END getReportTitle;
226
227 FUNCTION getHierarchyElementId(pElementShortName in varchar2,
228 pDimensionShortName in varchar2) return varchar2
229 IS
230 vHierSQL varchar2(2000);
231 vElementId number;
232 vLongName varchar2(2000);
233
234 BEGIN
235 /*
236 vHierSQL := ' select distinct ih.elementid, ih.longname'||
237 ' from cmpwbdimension_v d, cmpitemhierarchy_v ih, '||
238 ' cmplevelrelationship_v lr1'||
239 ' , cmplevel_v l '||
240 ' where ih.name = :1 and d.name = :2 '||
241 ' and ih.elementid = lr1.HIERARCHY '||
242 ' and lr1.CHILDLEVEL = l.ELEMENTID '||
243 ' and d.elementid = ih.owndimension ';
244 */
245 vHierSQL := ' select distinct hier.hier_id, hier.hier_long_name' ||
246 ' from edw_hierarchies_md_v hier, EDW_HIERARCHY_LEVEL_MD_V hierlvl' ||
247 ' where hier.hier_id = hierlvl.hier_id' ||
248 ' and hier.hier_name = :1 and hier.dim_name = :2';
249
250 EXECUTE IMMEDIATE vHierSQL INTO vElementId, vLongName
251 USING pElementShortName, pDimensionShortName ;
252 RETURN vElementId;
253 EXCEPTION
254 WHEN OTHERS THEN
255 --Supress the display of the error message if there is no hierarchy
256 --bisviewer.displayError(235,SQLCODE,SQLERRM);
257 return 0;
258 END getHierarchyElementId;
259
260 FUNCTION getDimensionForAttribute(pAttributecode in varchar2,
261 pRegionCode in varchar2) RETURN VARCHAR2
262 IS
263 CURSOR cAttr2 IS
264 SELECT attribute2 FROM
265 ak_region_items
266 WHERE region_code=rtrim(ltrim(pRegionCode)) and
267 (ltrim(rtrim(pAttributeCode)) in (attribute_code, attribute_code||'_FROM',
268 attribute_code||'_TO', attribute_code||'_HIERARCHY'));
269 l_attribute2 varchar2(32000);
270 BEGIN
271 OPEN cAttr2;
272 FETCH cAttr2 INTO l_attribute2;
273 IF cAttr2%NOTFOUND then
274 l_attribute2 := pAttributeCode;
275 END IF;
276 CLOSE cAttr2;
277 RETURN l_attribute2;
278 EXCEPTION
279 WHEN OTHERS THEN
280 bisviewer.displayError(235,SQLCODE,SQLERRM);
281 return null;
282 END getDimensionForAttribute;
283
284 FUNCTION getAttributeForDimension(pDimension in varchar2,
285 pRegionCode in varchar2) RETURN VARCHAR2
286 IS
287 CURSOR cAttr2 IS
288 SELECT attribute_code FROM
289 ak_region_items
290 WHERE region_code=rtrim(ltrim(pRegionCode)) and
291 attribute2=ltrim(rtrim(pDimension));
292 l_attribute_code varchar2(32000);
293 BEGIN
294 OPEN cAttr2;
295 FETCH cAttr2 INTO l_attribute_code;
296 IF cAttr2%NOTFOUND then
297 l_attribute_code := pDimension;
298 END IF;
299 CLOSE cAttr2;
300 RETURN l_attribute_code;
301 EXCEPTION
302 WHEN OTHERS THEN
303 bisviewer.displayError(235,SQLCODE,SQLERRM);
304 return null;
305 END getAttributeForDimension;
306
307 function encode (p_url in varchar2,
308 p_charset in varchar2 default null) return varchar2
309 is
310 -- mdamle 11/1/2002 - Removed *
311 c_unreserved constant varchar2(72) :=
312 '-_.!~*''()ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
313 l_client_nls_lang varchar2(200);
314 l_client_charset varchar2(200);
315 l_db_charset varchar2(200);
316 l_tmp varchar2(32767) := '';
317 l_onechar varchar2(4);
318 l_str varchar2(48);
319 l_byte_len integer;
320 l_do_convert boolean := null;
321 i integer;
322 begin
323 if p_url is NULL then
324 return NULL;
325 end if;
326
327 l_do_convert := true;
328 if p_charset is null
329 then
330 l_client_charset := g_db_charset;
331 l_client_nls_lang := g_db_nls_lang;
332 else
333 i := instr(p_charset, '.');
334 if i <> 0 then
335 l_client_charset := substr(p_charset, i+1);
336 l_client_nls_lang := p_charset;
337 else
338 l_client_charset := p_charset;
339 l_client_nls_lang := 'AMERICAN_AMERICA.' || p_charset;
340 end if;
341 end if;
342
343 for i in 1 .. length(p_url) loop
344 l_onechar := substr(p_url,i,1);
345
346 if instr(c_unreserved, l_onechar) > 0 then
347
348 /* if this character is excluded from encoding */
349 l_tmp := l_tmp || l_onechar;
350 elsif l_onechar = ' ' then
351 /* spaces are encoded using the plus "%20" sign */
352 l_tmp := l_tmp || '%20';
353 else
354 if (l_do_convert) then
355
356
357 /*
358 * This code to be called ONLY in case when client and server
359 * charsets are different. The performance of this code is
360 * significantly slower than "else" portion of this statement.
361 * But in this case it is guarenteed to be working in
362 * any configuration where the byte-length of the charset
363 * is different between client and server (e.g. UTF-8 to SJIS).
364 */
365
366 /*
367 * utl_raw.convert only takes a qualified NLS_LANG value in
368 * <langauge>_<territory>.<charset> format for target and
369 * source charset parameters. Need to use l_client_nls_lang
370 * and g_db_nls_lang here.
371 */
372 l_str := utl_raw.convert(utl_raw.cast_to_raw(l_onechar),
373 l_client_nls_lang,
374 g_db_nls_lang);
375 l_byte_len := length(l_str);
376 if l_byte_len = 2 then
377 l_tmp := l_tmp
378 || '%' || l_str;
379 elsif l_byte_len = 4 then
380 l_tmp := l_tmp
381 || '%' || substr(l_str,1,2)
382 || '%' || substr(l_str,3,2);
383 elsif l_byte_len = 6 then
384 l_tmp := l_tmp
385 || '%' || substr(l_str,1,2)
386 || '%' || substr(l_str,3,2)
387 || '%' || substr(l_str,5,2);
388 elsif l_byte_len = 8 then
389 l_tmp := l_tmp
390 || '%' || substr(l_str,1,2)
391 || '%' || substr(l_str,3,2)
392 || '%' || substr(l_str,5,2)
393 || '%' || substr(l_str,7,2);
394 else /* maximum precision exceeded */
395 raise PROGRAM_ERROR;
396 end if;
397 else
398
399 /*
400 * This is the "simple" encoding when no charset translation
401 * is needed, so it is relatively fast.
402 */
403 l_byte_len := lengthb(l_onechar);
404 if l_byte_len = 1 then
405 l_tmp := l_tmp || '%' ||
406 substr(to_char(ascii(l_onechar),'FM0X'),1,2);
407 elsif l_byte_len = 2 then
408 l_str := to_char(ascii(l_onechar),'FM0XXX');
409 l_tmp := l_tmp
410 || '%' || substr(l_str,1,2)
411 || '%' || substr(l_str,3,2);
412 elsif l_byte_len = 3 then
413 l_str := to_char(ascii(l_onechar),'FM0XXXXX');
414 l_tmp := l_tmp
415 || '%' || substr(l_str,1,2)
416 || '%' || substr(l_str,3,2)
417 || '%' || substr(l_str,5,2);
418 elsif l_byte_len = 4 then
419 l_str := to_char(ascii(l_onechar),'FM0XXXXXXX');
420 l_tmp := l_tmp
421 || '%' || substr(l_str,1,2)
422 || '%' || substr(l_str,3,2)
423 || '%' || substr(l_str,5,2)
424 || '%' || substr(l_str,7,2);
425 else /* maximum precision exceeded */
426 raise PROGRAM_ERROR;
427 end if;
428 end if;
429 end if;
430 end loop;
431 return l_tmp;
432 end encode;
433
434 function decode1 (p_url in varchar2,
435 p_charset in varchar2 default null)
436 return varchar2
437 is
438 l_client_nls_lang varchar2(200);
439 l_raw raw(32767);
440 l_char varchar2(4);
441 l_hex varchar2(8);
442 l_len integer;
443 i integer := 1;
444 begin
445 /*
446 * Set a source charset for code conversion.
447 * utl_raw.convert() only accepts <lang>_<territory>.<charset> format
448 * to specify source and destination charset and need to add a dummy
449 * 'AMERICAN_AMERICA' string if a give charset dose not have <lang>_
450 * <territory> information.
451 */
452 if instr(p_charset, '.') = 0 then
453 l_client_nls_lang := 'AMERICAN_AMERICA.' || p_charset;
454 else
455 l_client_nls_lang := p_charset;
456 end if;
457
458 l_len := length(p_url);
459
460 while i <= l_len
461 loop
462 l_char := substr(p_url, i, 1);
463 if l_char = '+' then
464 /* convert to a hex number of space characters */
465 l_hex := '20';
466 i := i + 1;
467 elsif l_char = '%' then
468 /* process hex encoded characters. just remove a % character */
469 l_hex := substr(p_url, i+1, 2);
470 i := i + 3;
471 else
472 /* convert to hex numbers for all other characters */
473 l_hex := to_char(ascii(l_char), 'FM0X');
474 i := i + 1;
475 end if;
476 /* convert a hex number to a raw datatype */
477 l_raw := l_raw || hextoraw(l_hex);
478 end loop;
479
480 /*
481 * convert a raw data from the source charset to the database charset,
482 * then cast it to a varchar2 string.
483 */
484 return utl_raw.cast_to_varchar2(
485 utl_raw.convert(l_raw, g_db_nls_lang, l_client_nls_lang));
486 end decode1;
487
488 -- mdamle 11/08/2001
489 function getReportRegion(pFunctionName IN VARCHAR2) return varchar2 IS
490
491 l_paramRegionCode VARCHAR2(30);
492 l_callRegionCode VARCHAR2(30);
493 l_parameters VARCHAR2(2000);
494 l_region VARCHAR2(30);
495 l_index NUMBER;
496 l_type VARCHAR2(30);
497 l_web_html_call VARCHAR2(240);
498 l_ref_function_name fnd_form_functions.function_name%TYPE;
499
500 cursor c_form_func(cpFunctionName varchar2) is
501 select web_html_call,parameters,type
502 from fnd_form_functions
503 where function_name = cpFunctionName;
504
505 BEGIN
506
507 -- mdamle 12/27/2001 - Region Code is specified in web_html_call when type = WWW
508 -- - Region Code may be specified in parameters when type = DBPORTLET / WEBPORTLET
509 if c_form_func%ISOPEN then
510 close c_form_func;
511 end if;
512 open c_form_func(pFunctionName);
513 fetch c_form_func into l_web_html_call, l_parameters, l_type;
514 close c_form_func;
515 /*
516 begin
517 select web_html_call
518 ,parameters
519 ,type
520 into l_web_html_call, l_parameters, l_type
521 from fnd_form_functions
522 where function_name = pFunctionName;
523 exception
524 WHEN OTHERS THEN NULL;
525 end;
526 */
527 if l_type = 'WWW' then
528 l_region := substr( substr( l_web_html_call, instr(l_web_html_call, '''')+1 ), 1, instr(substr( l_web_html_call, instr(l_web_html_call, '''')+1 ),'''')-1 );
529 if l_region is null then
530 -- Try parameters
531 l_region := getParameterValue(l_parameters, 'pRegionCode');
532 end if;
533 elsif l_type = 'WEBPORTLET' then
534 l_region := getParameterValue(l_parameters, 'pRegionCode');
535 else
536 -- Type = DBPORTLET
537 l_region := getParameterValue(l_parameters, 'pRegionCode');
538
539 -- Check if portlet is pointing to another function
540 -- Get region code from that function
541 l_ref_function_name := getParameterValue(l_parameters, 'pFunctionName');
542 if l_ref_function_name is null then
543 l_ref_function_name := getParameterValue(l_parameters, 'FUNCTION_NAME');
544 end if;
545
546 if l_ref_function_name is not null and l_ref_function_name <> '' then
547 if c_form_func%ISOPEN then
548 close c_form_func;
549 end if;
550 open c_form_func(l_ref_function_name);
551 fetch c_form_func into l_web_html_call, l_parameters, l_type;
552 close c_form_func;
553 /*
554 begin
555 select web_html_call
556 ,parameters
557 ,type
558 into l_web_html_call, l_parameters, l_type
559 from fnd_form_functions
560 where function_name = l_ref_function_name;
561 exception
562 WHEN OTHERS THEN NULL;
563 end;
564 */
565 if l_type = 'WWW' then
566 l_region := substr( substr( l_web_html_call, instr(l_web_html_call, '''')+1 ), 1, instr(substr( l_web_html_call, instr(l_web_html_call, '''')+1 ),'''')-1 );
567 if l_region is null then
568 -- Try parameters
569 l_region := getParameterValue(l_parameters, 'pRegionCode');
570 end if;
571 end if;
572 end if; -- l_ref_function_name is not null
573 end if;
574
575 return l_region;
576
577 END getReportRegion;
578
579 -- mdamle 12/05/01
580 -- This routine is used mainly because formatted date is not returned
581 -- correctly when called from java sql
582 function getFormattedDate(pInputDate in date, pFormatMask in varchar2) return varchar2 is
583 begin
584 return to_char(pInputDate, pFormatMask);
585 exception
586 when others then
587 return to_char(pInputDate);
588 end getFormattedDate;
589
590 -- mdamle 12/12/01 - This routine will return the value of a parameter (given the param name) within the parameter string
591 -- defined in form function
592 function getParameterValue(pParameters IN VARCHAR2, pParameterKey IN VARCHAR2) return varchar2 is
593 l_value varchar2(1000);
594 l_index1 number;
595
596 l_value_begin number;
597 l_value_end number;
598
599 begin
600 -- mdamle 11/15/2002 - Ignore case
601 l_index1 := instr(lower(pParameters), lower(pParameterKey)||'=');
602
603 if l_index1 > 0 then
604 l_value_begin := l_index1 + length(pParameterKey||'=');
605
606 l_value_end := instr(pParameters, '&', l_value_begin);
607
608 if l_value_end > 0 then
609 l_value := substr(pParameters, l_value_begin, l_value_end - l_value_begin);
610 else
611 l_value := substr(pParameters, l_value_begin);
612 end if;
613
614 else
615 l_value := '';
616 end if;
617
618 return l_value;
619
620 end getParameterValue;
621
622 -- mdamle 12/19/2001
623 --Rewrote the whole thing for performance improvement.
624 function getDefaultResponsibility(pUserId in varchar2
625 , pFunctionName in varchar2
626 , pCheckPMVSpecific in varchar2 default 'N')
627 return varchar2 IS
628 l_resp_id fnd_responsibility.responsibility_id%TYPE;
629 l_default_resp_id fnd_responsibility.responsibility_id%TYPE;
630 l_appl_id fnd_responsibility.application_id%TYPE;
631 l_function_name fnd_form_functions.function_name%TYPE;
632 l_menu_id fnd_responsibility.menu_id%TYPE;
633 l_user_id fnd_user.user_id%TYPE;
634 CURSOR CM IS
635 SELECT 1
636 FROM FND_MENU_ENTRIES MEV, FND_RESP_FUNCTIONS RF, FND_FORM_FUNCTIONS FF
637 WHERE MEV.MENU_ID = l_menu_id
638 AND RF.RESPONSIBILITY_ID (+) = l_resp_id
639 AND RF.RULE_TYPE(+) = DECODE(MEV.FUNCTION_ID, NULL, 'M', 'F')
640 AND RF.ACTION_ID(+) <> DECODE(DECODE(MEV.FUNCTION_ID, NULL, 'M', 'F'), 'F', MEV.FUNCTION_ID,
641 'M', MEV.SUB_MENU_ID, null)
642 AND RF.ACTION_ID IS NULL
643 AND MEV.FUNCTION_ID = FF.FUNCTION_ID
644 AND FF.function_name = l_function_name
645 AND RF.APPLICATION_ID(+) = l_appl_id
646 UNION
647 SELECT 1
648 FROM FND_MENU_ENTRIES MEV, FND_RESP_FUNCTIONS RF, FND_FORM_FUNCTIONS FF
649 WHERE MEV.MENU_ID IN(
650 SELECT SUB_MENU_ID
651 FROM FND_MENU_ENTRIES
652 WHERE SUB_MENU_ID IS NOT NULL
653 CONNECT BY MENU_ID = PRIOR SUB_MENU_ID START WITH MENU_ID =l_menu_id)
654 AND RF.RESPONSIBILITY_ID (+) = l_resp_id
655 AND RF.RULE_TYPE(+) = DECODE(MEV.FUNCTION_ID, NULL, 'M', 'F')
656 AND RF.ACTION_ID(+) <> DECODE(DECODE(MEV.FUNCTION_ID, NULL, 'M', 'F'), 'F', MEV.FUNCTION_ID,
657 'M', MEV.SUB_MENU_ID, null)
658 AND RF.ACTION_ID IS NULL
659 AND MEV.FUNCTION_ID = FF.FUNCTION_ID
660 AND FF.function_name = l_function_name
661 AND RF.APPLICATION_ID(+) = l_appl_id;
662 CURSOR c_resp IS
663 select a.responsibility_id resp_id , a.menu_id menu_id, a.application_id appl_id
664 from fnd_responsibility a,
665 fnd_user_resp_groups b
666 where b.user_id = l_user_id
667 and a.version = 'W'
668 and b.responsibility_id = a.responsibility_id
669 and b.start_date <= sysdate
670 and (b.end_date is null or b.end_date >= sysdate)
671 and a.start_date <= sysdate
672 and (a.end_date is null or a.end_date >= sysdate)
673 and b.responsibility_application_id=a.application_id;
674 BEGIN
675 l_function_name := pFunctionName;
676 l_user_id := pUserId;
677 for c_rec in c_resp loop
678 l_resp_id := c_rec.resp_id;
679 l_menu_id := c_rec.menu_id;
680 l_Appl_id := c_rec.appl_id;
681 IF (CM%ISOPEN) THEN
682 CLOSE CM;
683 END IF;
684 OPEN cM;
685 FETCH CM INTO l_menu_id;
686 IF (CM%FOUND) THEN
687 l_default_resp_id := c_rec.resp_id;
688 CLOSE CM;
689 EXIT;
690 END IF;
691 CLOSE CM;
692 end loop;
693 return l_default_resp_id;
694 end getDefaultResponsibility;
695
696 procedure stale_portlet
697 (puserid in varchar2
698 ,pfunctionname in varchar2
699 ,pPlugId in varchar2 default null
700 )
701 IS
702 cursor c_username is
703 select user_name
704 from fnd_user
705 where user_id=puserid;
706 l_user_name varchar2(200);
707 BEGIN
708 /*OPEN c_username;
709 FETCH c_username INTO l_user_name;
710 CLOSE c_username;
711 --Call the ICX provided API to stale this portlet
712 icx_portlet.updateCacheByUserFunc(l_user_name, pfunctionname);*/
713 --UntIl we get the correct API from ICX we are going to do it ourselves.
714
715 update icx_portlet_customizations
716 set caching_key = TO_CHAR(TO_NUMBER(NVL(caching_key,'0'))+1)
717 where user_id = puserid and
718 plug_id = pPlugId;
719 EXCEPTION
720 WHEN OTHERS THEN
721 NULL;
722 end;
723
724 --temporary API . will replace once we get the correct API from Teresa.
725 PROCEDURE update_portlets_bypage(p_page_id in varchar2) IS
726 l_append_title varchar2(2000);
727 l_function varchar2(2000);
728 BEGIN
729 l_function := 'update icx_portlet_customizations set caching_key = TO_CHAR(TO_NUMBER(NVL(caching_key,''0''))+1) where reference_path in (select name from wwpob_portlet_instance$ where page_id=:1)';
730
731 execute immediate l_function using p_page_id;
732
733 EXCEPTION
734 WHEN OTHERS
735 THEN
736 NULL;
737 END;
738 function get_render_type
739 (p_region_code in varchar2
740 ,p_user_id in varchar2
741 ,p_responsibility_id in varchar2)
742 return varchar2
743 is
744 CURSOR c_akregion IS
745 SELECT attribute9
746 FROM ak_regions
747 WHERE region_code = p_region_code;
748 l_render_type varchar2(2000);
749 l_subtotal varchar2(2000);
750 BEGIN
751 l_render_type := fnd_profile.value_specific('PMV_RENDER_TYPE', p_user_id
752 ,p_responsibility_id, 191);
753 IF (c_akregion%ISOPEN) then
754 CLOSE c_akregion;
755 END IF;
756 open c_akregion;
757 FETCH c_akregion INTO l_subtotal;
758 CLOSE c_akregion;
759 IF (nvl(l_subtotal,'N') = 'Y') then
760 l_render_Type := 'HTML';
761 END IF;
762 RETURN l_render_type;
763 END;
764
765 -- mdamle 10/31/2002 - Bug#2560743 - Use previous page parameters for linked page
766 function getPortalPageId(pPageName in varchar2) return number IS
767
768 lPageId number;
769 lSQL varchar2(2000);
770 begin
771 lSQL := 'select bis_portlet_trends.getPortalPageId(:1) from dual';
772 begin
773 execute immediate lSQL into lPageId using pPageName ;
774 exception
775 when others then lPageId := null;
776 end;
777
778 return lPageId;
779
780 end getPortalPageId;
781
782 -- nbarik 01/10/03 Portlet Builder
783 FUNCTION getRegionApplicationId(pRegionCode IN VARCHAR2) RETURN NUMBER IS
784
785 CURSOR region_app_id_cursor(cp_region_code VARCHAR2) IS
786 SELECT region_application_id FROM ak_regions
787 WHERE region_code = cp_region_code;
788
789 l_region_app_id NUMBER;
790
791 BEGIN
792
793 IF region_app_id_cursor%ISOPEN THEN
794 CLOSE region_app_id_cursor;
795 END IF;
796
797 OPEN region_app_id_cursor(pRegionCode);
798 FETCH region_app_id_cursor INTO l_region_app_id;
799 IF region_app_id_cursor%NOTFOUND THEN
800 l_region_app_id := -1;
801 END IF;
802 CLOSE region_app_id_cursor;
803
804 RETURN l_region_app_id;
805 EXCEPTION
806 WHEN others THEN
807 IF region_app_id_cursor%ISOPEN THEN
808 CLOSE region_app_id_cursor;
809 END IF;
810
811 END getRegionApplicationId;
812
813 PROCEDURE stale_portlet_by_refPath (
814 pReferencePath IN VARCHAR2)
815 IS
816 BEGIN
817 UPDATE icx_portlet_customizations
818 SET caching_key = TO_CHAR(TO_NUMBER(NVL(caching_key,'0'))+1)
819 WHERE reference_path = pReferencePath;
820 END stale_portlet_by_refPath;
821
822
823 --BugFix 2995675: Stale the portlet by updating the caching key
824 --ansingh
825 PROCEDURE STALE_PORTLET_BY_PLUGID (pPlugId IN VARCHAR2)
826 IS
827 BEGIN
828
829 UPDATE icx_portlet_customizations
830 SET caching_key = TO_CHAR(TO_NUMBER(NVL(caching_key,'0'))+1)
831 WHERE plug_id = pPlugId;
832
833
834 -- P1 BUG 3902169
835 COMMIT ;
836
837 EXCEPTION
838 WHEN OTHERS THEN
839 null ;
840
841 END STALE_PORTLET_BY_PLUGID;
842
843
844 PROCEDURE SETUP_BIND_VARIABLES
845 (p_bind_variables in varchar2,
846 x_bind_var_tbl out NOCOPY BISVIEWER.t_char)
847 is
848 l_startIndex NUMBER;
849 l_endIndex NUMBER;
850 l_bind_var VARCHAR2(32000);
851 l_tab_index NUMBER := 1;
852 l_bind_col VARCHAR2(2000);
853 Begin
854 l_startIndex := 2;
855 loop
856 if (instr(p_bind_variables, '~', l_startIndex , 1) > 0) then
857 l_endIndex := instr(p_bind_variables,'~', l_startIndex, 1);
858 else
859 l_endIndex := length(p_bind_variables)+1;
860 end if;
861 l_bind_var := substr(p_bind_variables, l_startIndex, l_endIndex-l_startIndex);
862 -- nbarik - 04/07/03 - Bug Fix 2871424 - some of the bind values have single quotes - so strip the quotes
863 IF INSTR(l_bind_var, '''', 1)=1 AND INSTR(l_bind_var, '''', -1)=LENGTH(l_bind_var) THEN
864 l_bind_var := SUBSTR(l_bind_var, 2, LENGTH(l_bind_var)-2);
865 END IF;
866 x_bind_var_tbl(l_tab_index) := l_bind_var;
867 l_tab_index := l_tab_index +1;
868 l_startIndex := l_endIndex+1;
869 if (l_startIndex > length(p_bind_variables) or
870 l_endIndex <= 1 ) then
871 exit;
872 end if;
873 --Extra Precaution
874 if (l_tab_index > 1500) then
875 exit;
876 end if;
877 end loop;
878 if (substr(p_bind_variables,length(p_bind_variables),1) = '~' and
879 length(p_bind_variables) > 1)
880 then
881 --l_tab_index := l_tab_index+1;
882 x_bind_var_tbl(l_tab_index) := null;
883 end if;
884 END SETUP_BIND_VARIABLES;
885
886 --The api has been deprecated. But to be on the safer side has not been deleted.
887 FUNCTION GET_LAST_REFRESH_DATE(pObjectType varchar2, pFunctionName in varchar2) return varchar2 is
888 BEGIN
889 return GET_LAST_REFRESH_DATE(pObjectType, pFunctionName,'');
890 END GET_LAST_REFRESH_DATE;
891
892 --BugFix 2823330 Get Formatted Last Refresh Date
893 FUNCTION GET_LAST_REFRESH_DATE(pObjectType varchar2, pFunctionName in varchar2,pRFUrl in varchar2) return varchar2 is
894 l_last_refresh_date DATE;
895 l_last_date varchar2(200);
896 BEGIN
897 -- ashgarg Bug Fix: 4227468
898 --l_last_refresh_date := BIS_SUBMIT_REQUESTSET.get_last_refreshdate(pObjectType, null, pFunctionName);
899 --l_last_date := to_char(l_last_refresh_date, fnd_profile.value_specific('ICX_DATE_FORMAT_MASK'));
900 l_last_date := BIS_SUBMIT_REQUESTSET.get_last_refreshdate_url(pObjectType,null,pFunctionName,'N',pRFUrl);
901 return l_last_date;
902
903 exception
904 when others then
905 null;
906 END GET_LAST_REFRESH_DATE;
907
908 FUNCTION GET_LAST_REFRESH_DATE_URL(pObjectType in varchar2, pFunctionName in varchar2) return varchar2 IS
909 BEGIN
910 return GET_LAST_REFRESH_DATE_URL(pObjectType, pFunctionName,'');
911 END GET_LAST_REFRESH_DATE_URL;
912
913 FUNCTION GET_LAST_REFRESH_DATE_URL(pObjectType in varchar2, pFunctionName in varchar2,pRFUrl in varchar2)
914 return varchar2
915 IS
916 l_last_refresh_string varchar2(32000);
917 BEGIN
918 -- ashgarg Bug Fix: 4227468
919 --l_last_refresh_string := BIS_SUBMIT_REQUESTSET.get_last_refreshdate_url(pObjectType,null,pFunctionName);
920 l_last_refresh_string := BIS_SUBMIT_REQUESTSET.get_last_refreshdate_url(pObjectType,null,pFunctionName,'Y',pRFUrl);
921 return l_last_refresh_string;
922 EXCEPTION
923 WHEN OTHERS THEN NULL;
924 END;
925 FUNCTION hasFunctionAccess(pUserId IN VARCHAR2, pFunctionName IN VARCHAR2, pPMVSpecific IN VARCHAR2) RETURN VARCHAR2
926 IS
927 l_flag varchar2(1) := 'N';
928 BEGIN
929 IF BIS_GRAPH_REGION_HTML_FORMS.hasFunctionAccess(pUserId, pFunctionName, pPMVSpecific) THEN
930 l_flag := 'Y';
931 END IF;
932 RETURN l_flag;
933 END hasFunctionAccess;
934
935 --bug 3122867 - 09/05
936 PROCEDURE bis_run_function(
937 pApplication_id IN VARCHAR2,
938 pResponsibility_id IN VARCHAR2,
939 pSecurity_group_id IN VARCHAR2,
940 pFunction_id IN VARCHAR2,
941 pParameters IN VARCHAR2 DEFAULT NULL
942 ) IS
943 lUrl VARCHAR2(2000);
944 BEGIN
945
946 lUrl := icx_portlet.createExecLink(
947 p_application_id => pApplication_id,
948 p_responsibility_id => pResponsibility_id,
949 p_security_group_id => pSecurity_group_id,
950 p_function_id => pFunction_id,
951 p_parameters => pParameters,
952 p_target => NULL,
953 p_link_name => NULL,
954 p_url_only => 'Y'
955 );
956
957 owa_util.redirect_url(lUrl) ;
958
959 END bis_run_function;
960
961 -- nbarik 03/01/2004
962 -- udua 07/25/2005 - Changed API name and behavior.
963 FUNCTION getParamPortletFuncName(pPageFunctionName IN VARCHAR2)
964 RETURN VARCHAR2
965 IS
966 CURSOR c_form_function IS
967 SELECT web_html_call, parameters, type
968 FROM fnd_form_functions
969 WHERE function_name = pPageFunctionName;
970
971 CURSOR c_paramPortlet_funcName(p_menu_name VARCHAR2) IS
972 SELECT FF.FUNCTION_NAME
973 FROM FND_MENU_ENTRIES_VL MEV, FND_FORM_FUNCTIONS FF
974 WHERE MEV.MENU_ID IN (
975 SELECT SUB_MENU_ID
976 FROM FND_MENU_ENTRIES
977 WHERE SUB_MENU_ID IS NOT NULL
978 CONNECT BY MENU_ID = PRIOR SUB_MENU_ID START WITH MENU_ID = (SELECT MENU_ID FROM FND_MENUS
979 WHERE MENU_NAME=p_menu_name)
980 )
981 AND MEV.FUNCTION_ID = FF.FUNCTION_ID
982 AND BIS_PMV_UTIL.getParameterValue(FF.PARAMETERS, 'pRequestType') = 'P';
983
984 CURSOR c_mds_paramPortlet_funcName(p_doc_id NUMBER) IS
985 select att_value
986 from jdr_attributes a, (select att_comp_docid, att_comp_seq from jdr_attributes
987 where att_comp_docId in (select comp_docid from JDR_COMPONENTS
988 where comp_element like 'oa:pageLayout'
989 and comp_id = 'BisPage')
990 and att_name = 'user:akAttribute3'
991 and att_value = 'PARAMETER_PORTLET') b
992 where a.att_comp_docId = b.att_comp_docid
993 and a.att_comp_seq = b.att_comp_seq
994 and a.att_name = 'user:akAttribute1'
995 and a.att_value is not null
996 and a.att_comp_docId=p_doc_id;
997
998 l_parameters VARCHAR2(2000);
999 -- udua - 09.27.05 - R12 Mandatory Project - 4480009 [PMV Data-model Change].
1000 l_region VARCHAR2(480);
1001 l_index NUMBER;
1002 l_type VARCHAR2(30);
1003 l_web_html_call VARCHAR2(240);
1004 l_menu_type VARCHAR2(30);
1005 l_menu_name VARCHAR2(2000);
1006 l_doc_id NUMBER;
1007
1008 BEGIN
1009 IF c_form_function%ISOPEN THEN
1010 CLOSE c_form_function;
1011 END IF;
1012 OPEN c_form_function;
1013 FETCH c_form_function INTO l_web_html_call, l_parameters, l_type;
1014 CLOSE c_form_function;
1015 IF (l_type = 'JSP' AND l_web_html_call LIKE 'OA.jsp?akRegionCode=BIS_COMPONENT_PAGE%') THEN
1016 l_menu_type := getParameterValue(l_parameters, 'sourceType');
1017 l_menu_name := getParameterValue(l_parameters, 'pageName');
1018 IF (l_menu_type = 'FND_MENU') THEN
1019 IF c_paramPortlet_funcName%ISOPEN THEN
1020 CLOSE c_paramPortlet_funcName;
1021 END IF;
1022 OPEN c_paramPortlet_funcName(l_menu_name);
1023 FETCH c_paramPortlet_funcName INTO l_region;
1024 CLOSE c_paramPortlet_funcName;
1025 ELSIF (l_menu_type = 'MDS') THEN
1026 l_doc_id := getDocumentID(l_menu_name);
1027 IF c_mds_paramPortlet_funcName%ISOPEN THEN
1028 CLOSE c_mds_paramPortlet_funcName;
1029 END IF;
1030 OPEN c_mds_paramPortlet_funcName(l_doc_id);
1031 FETCH c_mds_paramPortlet_funcName INTO l_region;
1032 CLOSE c_mds_paramPortlet_funcName;
1033 END IF;
1034 END IF;
1035 RETURN l_region;
1036 END getParamPortletFuncName;
1037
1038 --
1039 -- Retrieves the document id for the specified fully qualified path name.
1040 -- The pathname must begin with a '/' and should look something like:
1041 -- /oracle/apps/bis/mydocument
1042 --
1043 -- Parameters:
1044 -- fullPathName - the fully qualified name of the document
1045 --
1046 -- Returns:
1047 -- Returns the ID of the path or -1 if no such path exists
1048 --
1049 FUNCTION getDocumentID(
1050 pFullPathName VARCHAR2) RETURN NUMBER
1051 IS
1052 l_full_path VARCHAR2(512);
1053 l_name VARCHAR2(60);
1054 --l_type VARCHAR2(30);
1055 l_owner_id NUMBER := 0;
1056 l_doc_id NUMBER := -1;
1057 l_end_index NUMBER;
1058 l_finished BOOLEAN := FALSE;
1059 BEGIN
1060 -- remove the first forward slash
1061 l_full_path := substr(pFullPathName, instr(pFullPathName, '/') + 1);
1062 LOOP
1063 -- Retrieve the first portion of the path name. For example, if the
1064 -- fullPath is /oracle/apps/bis/mydocument, then l_name will
1065 -- be 'oracle'.
1066 l_end_index := instr(l_full_path, '/');
1067 IF l_end_index = 0 THEN
1068 l_end_index := length(l_full_path);
1069 l_name := substr(l_full_path, 1, l_end_index);
1070 l_finished := TRUE;
1071 ELSE
1072 l_name := substr(l_full_path, 1, l_end_index - 1);
1073 l_full_path := substr(l_full_path, l_end_index + 1);
1074 END IF;
1075 SELECT path_docid --, path_type
1076 INTO l_doc_id --, l_type
1077 FROM jdr_paths
1078 WHERE path_name = l_name AND path_owner_docid = l_owner_id;
1079 IF (l_finished) THEN
1080 return l_doc_id;
1081 END IF;
1082 l_owner_id := l_doc_id;
1083 END LOOP;
1084
1085 EXCEPTION
1086 WHEN NO_DATA_FOUND THEN
1087 return -1;
1088 END;
1089
1090 -- nbarik - 04/20/04 - Enhancement 3378782 - Parameter Validation
1091 FUNCTION getRoleIds(pPrivileges IN VARCHAR2) RETURN BISVIEWER.t_char IS
1092 l_sql VARCHAR2(3000);
1093 l_privileges VARCHAR2(2000);
1094 l_bind_values BISVIEWER.t_char;
1095 l_roleIds VARCHAR2(2000);
1096 l_cursor INTEGER;
1097 ignore INTEGER;
1098 l_bind_col varchar2(200);
1099 l_menu_id NUMBER;
1100 l_menu_id_tbl BISVIEWER.t_char;
1101 l_count NUMBER := 1;
1102 BEGIN
1103 l_sql := 'SELECT DISTINCT menu_id FROM fnd_menu_entries where function_id in (';
1104 l_privileges := replace(pPrivileges, ',', '~');
1105 SETUP_BIND_VARIABLES(
1106 p_bind_variables => l_privileges,
1107 x_bind_var_tbl => l_bind_values
1108 );
1109 IF (l_bind_values IS NOT NULL AND l_bind_values.COUNT > 0) THEN
1110 FOR i IN l_bind_values.FIRST..l_bind_values.LAST LOOP
1111 l_sql := l_sql || ':' || i;
1112 IF (i <> l_bind_values.COUNT) THEN
1113 l_sql := l_sql || ',';
1114 END IF;
1115 END LOOP;
1116 l_sql := l_sql || ')';
1117 END IF;
1118 l_cursor := dbms_sql.open_cursor;
1119 dbms_sql.parse(l_cursor, l_sql, DBMS_SQL.native);
1120 IF (l_bind_values.COUNT > 0) THEN
1121 FOR i IN l_bind_values.FIRST..l_bind_values.LAST LOOP
1122 l_bind_col := ':'|| i;
1123 dbms_sql.bind_variable(l_cursor, l_bind_col, l_bind_values(i));
1124 END LOOP;
1125 END IF;
1126 dbms_sql.define_column(l_cursor, 1, l_menu_id);
1127 ignore := DBMS_SQL.EXECUTE(l_cursor);
1128 LOOP
1129 IF DBMS_SQL.FETCH_ROWS(l_cursor)>0 THEN
1130 -- get column values of the row
1131 DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_menu_id);
1132 l_menu_id_tbl(l_count) := l_menu_id;
1133 l_count := l_count + 1;
1134 ELSE
1135 -- No more rows
1136 EXIT;
1137 END IF;
1138 END LOOP;
1139 DBMS_SQL.CLOSE_CURSOR(l_cursor);
1140 RETURN l_menu_id_tbl;
1141 EXCEPTION
1142 WHEN OTHERS THEN
1143 IF DBMS_SQL.IS_OPEN(l_cursor) THEN
1144 DBMS_SQL.CLOSE_CURSOR(l_cursor);
1145 END IF;
1146 END getRoleIds;
1147
1148 -- nbarik - 04/20/04 - Enhancement 3378782 - Parameter Validation
1149 PROCEDURE getDelegations(
1150 pRoleIdsTbl IN BISVIEWER.t_char
1151 , pParamName IN VARCHAR2
1152 , pParameterView IN VARCHAR2
1153 , pAsOfDate IN DATE
1154 , xDelegatorIdTbl OUT NOCOPY BISVIEWER.t_char
1155 , xDelegatorValueTbl OUT NOCOPY BISVIEWER.t_char
1156 ) IS
1157 l_sql VARCHAR2(5000);
1158 l_cursor INTEGER;
1159 ignore INTEGER;
1160 l_bind_col varchar2(200);
1161 l_count NUMBER := 1;
1162 l_delegation_id VARCHAR2(256);
1163 l_delegation_value VARCHAR2(2000);
1164 l_employeeId NUMBER;
1165 l_delegation_param VARCHAR2(150);
1166 l_index NUMBER;
1167 l_error_message VARCHAR2(3000);
1168 BEGIN
1169 l_sql := 'SELECT distinct delegations.instance_pk1_value, parameter_view.value FROM fnd_grants delegations, fnd_objects parameter_object, '
1170 || pParameterView || ' parameter_view WHERE delegations.GRANTEE_KEY = :1 and parameter_object.obj_name = :2 '
1171 || 'and trunc(:3) between trunc(delegations.start_date) and trunc(delegations.end_date) and delegations.instance_pk1_value = to_char(parameter_view.id)'
1172 || 'and delegations.object_id = parameter_object.object_id and delegations.menu_id in ( ';
1173 IF (pRoleIdsTbl IS NOT NULL AND pRoleIdsTbl.COUNT > 0) THEN
1174 FOR i IN pRoleIdsTbl.FIRST..pRoleIdsTbl.LAST LOOP
1175 l_sql := l_sql || ':' || (i+3);
1176 IF (i <> pRoleIdsTbl.COUNT) THEN
1177 l_sql := l_sql || ',';
1178 END IF;
1179 END LOOP;
1180 l_sql := l_sql || ')';
1181 END IF;
1182 l_employeeId := FND_GLOBAL.EMPLOYEE_ID;
1183 l_index := INSTR(pParamName, '+');
1184 l_delegation_param := substr(pParamName, 1, l_index-1);
1185 l_cursor := dbms_sql.open_cursor;
1186 dbms_sql.parse(l_cursor, l_sql, DBMS_SQL.native);
1187 dbms_sql.bind_variable(l_cursor, ':1', l_employeeId || '');
1188 dbms_sql.bind_variable(l_cursor, ':2', l_delegation_param);
1189 dbms_sql.bind_variable(l_cursor, ':3', pAsOfDate);
1190 IF (pRoleIdsTbl.COUNT > 0) THEN
1191 FOR i IN pRoleIdsTbl.FIRST..pRoleIdsTbl.LAST LOOP
1192 l_bind_col := ':'|| (i+3);
1193 dbms_sql.bind_variable(l_cursor, l_bind_col, pRoleIdsTbl(i));
1194 END LOOP;
1195 END IF;
1196 dbms_sql.define_column(l_cursor, 1, l_delegation_id, 256);
1197 dbms_sql.define_column(l_cursor, 2, l_delegation_value, 2000);
1198 ignore := DBMS_SQL.EXECUTE(l_cursor);
1199 LOOP
1200 IF DBMS_SQL.FETCH_ROWS(l_cursor)>0 THEN
1201 -- get column values of the row
1202 DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_delegation_id);
1203 DBMS_SQL.COLUMN_VALUE(l_cursor, 2, l_delegation_value);
1204 xDelegatorIdTbl(l_count) := l_delegation_id;
1205 xDelegatorValueTbl(l_count) := l_delegation_value;
1206 l_count := l_count + 1;
1207 ELSE
1208 -- No more rows
1209 EXIT;
1210 END IF;
1211 END LOOP;
1212 DBMS_SQL.CLOSE_CURSOR(l_cursor);
1213 EXCEPTION
1214 WHEN OTHERS THEN
1215 IF DBMS_SQL.IS_OPEN(l_cursor) THEN
1216 DBMS_SQL.CLOSE_CURSOR(l_cursor);
1217 END IF;
1218 END getDelegations;
1219
1220 --=============================================================================
1221 -- gbhaloti 05/25/04 Generic Report Designer
1222 FUNCTION getPortletType(pType IN VARCHAR2, pParameters IN VARCHAR2) RETURN VARCHAR2 IS
1223 l_request_type CHAR;
1224 BEGIN
1225 /*
1226 IF (upper(pType) = 'JSP' OR upper(pType) = 'WWW') THEN
1227 RETURN fnd_message.get_string('BIS', 'BIS_REPORT_TITLE');
1228 ELSE
1229 IF (upper(pType) = 'WEBPORTLET') THEN
1230 l_request_type := getParameterValue(pParameters, 'pRequestType');
1231 CASE l_request_type
1232 WHEN 'T' THEN RETURN fnd_message.get_string('BIS', 'BIS_TREND_TABLE');
1233 WHEN 'G' THEN RETURN fnd_message.get_string('BIS', 'BIS_TREND_GRAPH');
1234 WHEN 'P' THEN RETURN fnd_message.get_string('BIS', 'BIS_PARAMETERS');
1235 ELSE RETURN NULL;
1236 END CASE;
1237 ELSE
1238 RETURN NULL;
1239 END IF;
1240 END IF;
1241 EXCEPTION
1242 WHEN OTHERS THEN
1243 */
1244 RETURN null;
1245
1246 END getPortletType;
1247
1248 --=============================================================================
1249 -- gbhaloti 05/25/04 Generic Report Designer
1250 FUNCTION getPortletTypeCode(pType IN VARCHAR2, pParameters IN VARCHAR2) RETURN CHAR IS
1251 l_request_type CHAR;
1252 BEGIN
1253 IF (upper(pType) = 'JSP' OR upper(pType) = 'WWW') THEN
1254 RETURN 'R';
1255 ELSE
1256 IF (upper(pType) = 'WEBPORTLET') THEN
1257 RETURN getParameterValue(pParameters, 'pRequestType');
1258 ELSE
1259 RETURN NULL;
1260 END IF;
1261 END IF;
1262 EXCEPTION
1263 WHEN OTHERS THEN
1264 RETURN null;
1265
1266 END getPortletTypeCode;
1267
1268 --=============================================================================
1269 -- gbhaloti 05/25/04 Generic Report Designer
1270 FUNCTION getRegionCode(pType IN VARCHAR2, pParameters IN VARCHAR2, webHtmlCall IN VARCHAR2, functionName IN VARCHAR2) RETURN CHAR IS
1271 l_request_type CHAR;
1272 BEGIN
1273 /*
1274 CASE pType
1275 WHEN 'JSP' THEN RETURN getParameterValue(webHtmlCall, 'regionCode');
1276 WHEN 'WWW' THEN RETURN nvl(trim(getParameterValue(pParameters, 'pRegionCode')), getReportRegion(functionName));
1277 WHEN 'WEBPORTLET' THEN RETURN getParameterValue(pParameters, 'pRegionCode');
1278 ELSE RETURN NULL;
1279 END CASE;
1280 EXCEPTION
1281 WHEN OTHERS THEN
1282 */
1283 RETURN null;
1284
1285 END getRegionCode;
1286 --=============================================================================
1287 -- gbhaloti 05/25/04 Generic Report Designer
1288 FUNCTION getRegionApplicationName(pRegionCode IN VARCHAR2) RETURN VARCHAR2 IS
1289
1290 CURSOR region_app_name_cursor(cp_region_code VARCHAR2) IS
1291 SELECT application_name FROM ak_regions R, fnd_application_vl A
1292 WHERE R.region_code = cp_region_code AND R.region_application_id = A.application_id;
1293
1294 l_code VARCHAR2(3);
1295
1296 CURSOR app_name_from_table is
1297 SELECT application_name
1298 FROM fnd_application_vl app
1299 WHERE app.application_short_name = l_code;
1300
1301 l_region_app_name VARCHAR2(2000);
1302
1303 BEGIN
1304
1305 IF region_app_name_cursor%ISOPEN THEN
1306 CLOSE region_app_name_cursor;
1307 END IF;
1308
1309 OPEN region_app_name_cursor(pRegionCode);
1310 FETCH region_app_name_cursor INTO l_region_app_name;
1311 IF region_app_name_cursor%NOTFOUND THEN
1312 IF app_name_from_table%ISOPEN THEN
1313 CLOSE app_name_from_table;
1314 END IF;
1315
1316 l_code := 'FND';
1317 OPEN app_name_from_table;
1318 FETCH app_name_from_table INTO l_region_app_name;
1319 CLOSE app_name_from_table;
1320 END IF;
1321 CLOSE region_app_name_cursor;
1322
1323 RETURN l_region_app_name;
1324 EXCEPTION
1325 WHEN others THEN
1326 IF region_app_name_cursor%ISOPEN THEN
1327 CLOSE region_app_name_cursor;
1328 END IF;
1329
1330 END getRegionApplicationName;
1331 --==============================================================================
1332
1333 FUNCTION getRegionDataSourceType(pRegionCode IN VARCHAR2) RETURN VARCHAR2 IS
1334
1335 CURSOR source_type_cursor(cp_region_code VARCHAR2) IS
1336 SELECT attribute10 FROM ak_regions
1337 WHERE region_code = cp_region_code;
1338
1339 l_source_type VARCHAR2(150);
1340
1341 BEGIN
1342
1343 IF source_type_cursor%ISOPEN THEN
1344 CLOSE source_type_cursor;
1345 END IF;
1346
1347 OPEN source_type_cursor(pRegionCode);
1348 FETCH source_type_cursor INTO l_source_type;
1349 IF source_type_cursor%NOTFOUND THEN
1350 l_source_type := NULL;
1351 END IF;
1352 CLOSE source_type_cursor;
1353
1354 RETURN l_source_type;
1355 EXCEPTION
1356 WHEN others THEN
1357 IF source_type_cursor%ISOPEN THEN
1358 CLOSE source_type_cursor;
1359 END IF;
1360
1361 END getRegionDataSourceType;
1362
1363 -- msaran 08/31/2005 eliminate mod_plsql
1364 PROCEDURE readBinaryFile (p_file_id IN VARCHAR2, content_type OUT NOCOPY VARCHAR2, data OUT NOCOPY BLOB) IS
1365 l_file_id VARCHAR2(100);
1366 BEGIN
1367 l_file_id := icx_call.decrypt(p_file_id);
1368 select file_content_type, file_data
1369 into content_type, data
1370 from fnd_lobs
1371 where file_id = l_file_id;
1372 END readBinaryFile;
1373
1374
1375 END BIS_PMV_UTIL;
[Home] [Help]