[Home] [Help]
PACKAGE BODY: APPS.BIS_TREND_PLUG
Source
1 package body bis_trend_plug as
2 /* $Header: BISTRNDB.pls 120.3 2006/02/02 02:08:40 nbarik noship $ */
3 -- Added for ARU db drv auto generation
4 -- dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
5 -- dbdrv: checkfile(115.189=120.3):~PROD:~PATH:~FILE
6 /*
7 REM +===========================================================================+
8 REM | Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA |
9 REM | All rights reserved. |
10 REM +===========================================================================+
11 REM | FILENAME |
12 REM | BISTRNDB.pls |
13 REM | |
14 REM | DESCRIPTION |
15 REM | Package for Scheduling Portlets |
16 REM | |
17 REM | NOTES |
18 REM | |
19 REM | HISTORY |
20 REM | Date Developer Comments |
21 REM | 26-SEP-2002 nbarik Bug 1917856: Use ICX Date Format for upd_date |
22 REM | 28-SEP-2002 ansingh Bug 2599787: Reduce updates to |
23 REM | icx_portlet_customizations in initialiseRLPortlet|
24 REM | 24-MAR-2003 rcmuthuk Bug 2856247: Custom Views attached at function |
25 REM | not working due to wrong region code |
26 REM | 03-APR-2003 nkishore Bug 2869306: Substitute Time ViewBy with DimLevel |
27 REM | 14-APR-2003 gsanap Bug 2852195: get user_function_name for empty prompt |
28 REM | 11-Aug-2003 nbarik Bug 3088087 |
29 REM | 24-Nov-2003 ksadagop Bug 3265474 |
30 REM | 27-Nov-2003 ksadagop Bug 3281530 |
31 REM | 02-Dec-2003 ksadagop Enh 3252697--Fnd Cache Invalidation |
32 REM | 12-Jan-2004 nkishore BugFix 3360363 |
33 REM | 12-Jan-2004 nkishore BugFix 3417356 |
34 REM | 12-MAR-2004 mdamle Enh 3503753 - Site level custom. for links |
35 REM | 31-MAY-2004 nbarik Enhancement 3576963 - Drill Java Conversion |
36 REM | 14-JUN-2004 ansingh Enh#3690747: Portlet Personalization |
37 REM | 04-Aug-2004 ashgarg Enh#3813010:Moving Select Different Report page to OA +===========================================================================+
38 */
39
40 G_MAX_VARCHAR2 constant number := 2000;
41 G_GRAPH_BORDER constant number := 0;
42 G_GRAPH_HEIGHT constant number := 275;
43 G_GRAPH_WIDTH constant number := 450;
44 G_MAX_LOOP_COUNT constant number := 100;
45
46 g_css varchar2(1000) := FND_PROFILE.value('ICX_OA_HTML');
47 g_CSSDirectory varchar2(1000) := FND_WEB_CONFIG.TRAIL_SLASH(g_css);
48
49 images varchar2(1000) := FND_PROFILE.value('ICX_REPORT_IMAGES');
50 gvImageDirectory varchar2(1000) := FND_WEB_CONFIG.TRAIL_SLASH(images);
51
52
53
54 -- *****************************************
55 -- PROCEDURE TO extract a numbered gif file form the output of Web-served Reports
56 -- *****************************************
57
58 procedure get_graph_from_URL( p_trend_type IN VARCHAR2,
59 p_report_Fn_URL in varchar2,
60 x_img_html out NOCOPY varchar2
61 )
62 is
63 l_html varchar2(32000);
64 l_URL varchar2(32000);
65 l_opening_pos number;
66 l_len number;
67 l_dot_pos number;
68 l_img_src_str varchar2(32000);
69 l_searched_str varchar2(32000);
70 l_search_str varchar2(32000);
71 l_img_src_found varchar2(32000);
72 l_file_start_found varchar2(32000);
73 l_html_pieces utl_http.html_pieces;
74 l_loop_1_counter number := 1;
75 l_loop_2_counter number := 1;
76 l_loop_3_counter number := 1;
77
78 i number := 0;
79 k_trail number := 0;
80 j varchar2(32000);
81 j_front varchar2(32000);
82 j_trail varchar2(32000);
83 k number :=0;
84 l varchar2(32000);
85 m number :=0;
86 n number :=0;
87 o number :=0;
88 a number :=0;
89 b number :=0;
90 c number :=0;
91 diff number :=0;
92 str number ;
93
94 begin
95
96
97 l_html_pieces := utl_http.request_pieces( url => p_report_Fn_URL,
98 max_pieces => 32000);
99
100 -- original code for Graph
101 if p_trend_type = 'G' then
102
103 for m in l_html_pieces.first..l_html_pieces.last loop
104 n := instr(l_html_pieces(m),'img src ="http://');
105 if n > 0 then
106 k := n;
107 o := m;
108 exit;
109 end if;
110 end loop;
111
112
113 j := substr(l_html_pieces(o),k+10,k+2000);
114 j_front := j;
115
116 i := instr(j,'"></TD>');
117
118 if i > 0 then
119 l := substr(j,1,i-1);
120 else
121 j_trail := substr(l_html_pieces(o+1),1,2000);
122 k_trail := instr(j_trail,'"></TD>');
123 if k_trail > 0 then
124 l := substr(j_trail,1,k_trail-1);
125 l := j_front||l;
126 end if;
127 end if;
128
129 x_img_html := l;
130
131 --Code for Table
132
133 elsif p_trend_type = 'T' then
134
135
136 for m in l_html_pieces.first..l_html_pieces.last loop
137 n := instr(l_html_pieces(m),'<!-- Actual Table');
138 if n > 0 then
139 k := n;
140 o := m;
141 exit;
142 end if;
143 end loop;
144
145 m := 0;
146
147 for m in l_html_pieces.first..l_html_pieces.last loop
148 a := instr(l_html_pieces(m),'<!-- RELATED');
149 if a > 0 then
150 b := a;
151 c := m;
152 exit;
153 end if;
154 end loop;
155
156 diff := c - o;
157
158
159
160
161 j := substr(l_html_pieces(o),k,k+7000);
162
163 for str in o+1..c-1 loop
164 j := j||l_html_pieces(str);
165 end loop;
166
167
168 j_front := j;
169
170
171 i := instr(l_html_pieces(c),'<!-- RELATED');
172
173 if i > 0 then
174 j_trail := substr(l_html_pieces(c),1,i-1);
175 l := j_front||j_trail;
176
177 else
178 j_trail := substr(l_html_pieces(o+1),1,7000);
179 k_trail := instr(j_trail,'<!-- RELATED');
180 if k_trail > 0 then
181 l := substr(j_trail,1,k_trail-1);
182 l := j_front||l;
183 end if;
184 end if;
185
186 x_img_html := l;
187
188 end if;
189
190 exception
191 when others then
192 x_img_html := NULL;
193 htp.p('<! EXCEPTION '|| Sqlerrm ||'
194 URL is '|| p_report_Fn_URL ||'
195 >'
196 );
197 htp.p('<! html First piece is'
198 ||REPLACE(l_html_pieces(1),'>',' ')
199 ||'>');
200
201 end get_graph_from_URL;
202
203 -- mdamle 04/05/2001 - Get Graph and Table HTML from FND_LOBS
204 procedure Show(p_session_id in pls_integer default NULL,
205 p_plug_id in pls_integer default NULL,
206 p_display_name varchar2 default NULL,
207 p_delete in varchar2 default 'N')
208 is
209 l_title varchar2(80);
210 l_colour varchar2(30);
211 l_user_id pls_integer;
212 l_responsibility_id pls_integer;
213 l_fn_responsibility_id pls_integer;
214 l_function_id pls_integer;
215 l_img_html varchar2(32000);
216 l_plsql_agent_URL varchar2(32000);
217 l_Graph_URL varchar2(32000);
218 l_function_name fnd_form_functions_vl.user_function_name%TYPE;
219 l_function_description fnd_form_functions_vl.description%TYPE;
220 l_Report_Run_str varchar2(32000);
221
222 l_application_id pls_integer;
223
224 l_graph_width varchar2(255);
225 l_graph_height varchar2(255);
226 l_graph_size_HTML varchar2(255);
227
228 l_schedule_id number;
229 l_file_id pls_integer := null;
230 l_profile_defined boolean;
231 l_message varchar2(32000);
232 --l_html_pieces utl_http.html_pieces;
233 l_html_pieces bis_pmv_util.lob_varchar_pieces;
234 last_upd date;
235 l_report_url varchar2(32000);
236 l_parameters varchar2(32000);
237 l_customize_url varchar2(32000);
238 r_region varchar2(32000);
239 -- mdamle 04/27/01
240 l_front varchar2(32000);
241 l_trail varchar2(32000);
242 l_cssFound boolean;
243 l_file_content_type varchar2(256);
244 --ashgarg
245 l_resp_id varchar2(80);
246 l_sec_grp_id number;
247
248 -- jprabhud 09/24/02 - Enh. 2470068 DB Graph HTML - Reusing file Ids to store graphs
249 vGraphFileId varchar2(20);
250
251
252
253 begin
254
255 -- mdamle 08/08/01 - Fix Bug#1910032 - Improve Performance
256 -- 1) Use fnd_form_functions in join instead of fnd_form_functions_vl
257 -- 2) Remove the Cursor for retrieving Application Id - not used anymore
258 -- 3) Remove r_function, use function name from query
259
260 IF icx_sec.validatePlugSession(p_plug_id,p_session_id) THEN
261
262 -- mdamle 01/24/01
263 l_user_id := icx_sec.getID(icx_sec.PV_USER_ID,'',p_session_id);
264
265 IF p_delete = 'Y' THEN
266 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
267 -- Delete Portlet data and schedule info now that portlet is deleted
268 -- jprabhud 09/24/02 - Enh. 2470068 DB Graph HTML - Reusing file Ids to store graphs - passed in vGraphFileId
269 bis_rg_schedules_pvt.delete_portlet(p_plug_id, l_user_id,vGraphFileId);
270 if vGraphFileId is not null then
271 delete fnd_lobs where file_id = vGraphFileId;
272 end if;
273
274
275 else
276
277 -- mdamle 08/21/01 - Only one user_id, plug_id combination exists in bis_scheduler from now on
278 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
279 begin
280 SELECT sp.title
281 , sp.file_id
282 , ff.function_id
283 , s.function_name
284 , sp.last_update_date
285 , ff.web_html_call
286 , s.responsibility_id
287 , sp.schedule_id
288 , l.file_content_type
289 into
290 l_title
291 , l_file_id
292 , l_function_id
293 , l_function_name
294 , last_upd
295 , l_parameters
296 , l_fn_responsibility_id
297 , l_schedule_id
298 , l_file_content_type
299 FROM bis_scheduler s, bis_schedule_preferences sp, fnd_form_functions ff, fnd_lobs l
300 WHERE sp.USER_ID = l_user_id
301 AND sp.PLUG_ID = p_plug_id
302 AND sp.schedule_id = s.schedule_id
303 AND ff.function_name = s.function_name
304 AND sp.file_id = l.file_id;
305 exception
306 when others then null;
307 end;
308
309 if l_fn_responsibility_id is null then
310 l_fn_responsibility_id := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID,'',p_Session_id);
311 end if;
312
313 getRespInfo(l_User_Id, l_fn_responsibility_id, l_application_id, l_sec_grp_id);
314
315 -- mdamle 11/08/01 - Get region from utility function
316 -- - Form Function definition is changed
317 /*
318 r_region := substr( l_parameters, instr(l_parameters, '''')+1 );
319 r_region := substr( r_region, 1, instr(r_region,'''')-1 );
320 */
321 r_region := BIS_PMV_UTIL.getReportRegion(l_function_name);
322
323 if r_region is null and l_function_name is null then
324 --ashgarg changed it for moving select different reports to OA.jsp
325 --ashgarg Bug Fix:3823820
326 begin
327 select function_id into l_function_id
328 from fnd_form_functions
329 where function_name = 'BIS_PMV_SELECT_DIFF_REPORT';
330 exception
331 when others then l_function_id := null;
332 end;
333 if l_function_id is not null then
334 l_parameters := 'Region_Code='||
335 '&Function_Name='||
336 '&respID='||
337 '&pUserId='||l_User_Id
338 ||'&pPlugId='||p_Plug_Id
339 ||'&pScheduleOverride=Y'
340 ||'&pMode=Y';
341
342 l_CUSTOMIZE_URL := icx_sec.createRFURL( p_function_id => l_function_id
343 , p_session_id => p_session_id
344 , p_parameters => l_parameters
345 , p_application_id => l_application_id
346 , p_responsibility_id => l_fn_responsibility_id
347 , p_security_group_id => icx_sec.g_security_group_id);
348
349 /*l_CUSTOMIZE_URL := icx_plug_utilities.getPLSQLagent
350 || 'OracleApps.runFunction?c_function_id=' || l_function_id
351 ||'&n_session_id='||p_session_id
352 ||'&c_parameters='||BIS_PMV_UTIL.encode(l_parameters)
353 ||'&p_resp_appl_id='||l_application_id
354 ||'&p_responsibility_id='||l_resp_id
355 -- mdamle 01/08/2002
356 ||'&p_Security_group_id='||icx_sec.g_security_group_id;*/
357
358 end if;
359 /* l_CUSTOMIZE_URL :='OA_HTML/OA.jsp?akRegionCode=BISPMVRLREPORTSWORKBOOKSPAGE&akRegionApplicationId=191&retainAM=Y'||'&Region_Code='
360 ||'&Function_Name='
361 ||'&respID='
362 ||'&pUserId='||l_User_Id
363 ||'&pPlugId='||p_Plug_Id
364 -- mdamle 01/08/2002
365 ||'&pScheduleOverride=Y'
366 ||'&pMode=Y';*/
367 else
368 -- mdamle 10/30/01 - Converted PLSQL to JSP
369 /*
370 l_CUSTOMIZE_URL := icx_plug_utilities.getPLSQLagent
371 ||'bisviewer.parametersection?Region_Code='||r_region
372 ||'&Function_Name='||l_function_name
373 ||'&pResponsibilityId='||l_fn_responsibility_id
374 ||'&pUserId='||l_user_id
375 -- mdamle 05/09/01 - uncommented session_id
376 ||'&pSessionId='||p_session_id
377 ||'&pPlugId='||p_plug_id
378 ||'&pScheduleId='||l_schedule_id
379 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
380 ||'&pFileId='||l_File_id;
381 */
382
383 l_CUSTOMIZE_URL := icx_plug_utilities.getPLSQLagent
384 ||'bis_trend_plug.customizePortlet?Region_Code='||r_region
385 ||'&Function_Name='||l_function_name
386 ||'&pResponsibilityId='||l_fn_responsibility_id
387 ||'&pUserId='||l_user_id
388 -- mdamle 05/09/01 - uncommented session_id
389 ||'&pSessionId='||p_session_id
390 ||'&pPlugId='||p_plug_id
391 ||'&pScheduleId='||l_schedule_id
392 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
393 ||'&pFileId='||l_File_id
394 -- mdamle 01/08/2002
395 ||'&pScheduleOverride=Y';
396
397 end if;
398
399 htp.p('<!-- Graphics Plug -->');
400
401 icx_plug_utilities.plugbanner
402 ('<A href='||FND_WEB_CONFIG.trail_slash(icx_plug_utilities.getPLSQLagent)
403 ||'bis_trend_plug.view_report_from_portlet?pRegionCode='||r_region||'&pFunctionName='||l_function_name
404
405 || '&pScheduleId='||l_schedule_id
406 ||' target=_top><font color="#ffffff" face="Arial">'||l_title||BIS_PMV_UTIL.getAppendTitle(r_region)||'</font></A>'
407 ,l_customize_url
408 ,'FNDTREND.gif');
409
410 setTableStyles;
411
412 if (l_file_id is not null) then
413
414 -- TABLE / GRAPH IS AVAILABLE IN HTML
415 -- aleung, 7/13/01, use readFndLobs to get the html pieces
416 l_html_pieces := bis_pmv_util.readFndLobs(l_file_id);
417
418 -- mdamle 08/27/01 - Show gif for graph or html for table
419 if l_html_pieces.count > 0 then
420 if instr(lower(l_file_content_type),'image/gif') > 0 then
421 htp.p('<table width="100%" border="0" cellspacing="0" cellpadding="0">
422 <tr><td>');
423 htp.p('<img src='||FND_WEB_CONFIG.trail_slash(FND_WEB_CONFIG.PLSQL_Agent)
424 ||'bis_save_report.retrieve?file_id='||l_file_id);
425 htp.p('</td></tr></table>');
426
427 else
428
429
430 l_cssFound := false;
431 for i in 1..l_html_pieces.COUNT loop
432 -- mdamle 04/27/01 - Remove reference to css file
433 -- Instead include the required tags in the page.
434 if l_cssFound = false then
435 if instr(l_html_pieces(i), 'bismarli') > 0 then
436 l_front := substr(l_html_pieces(i),1,instr(l_html_pieces(i),'<LINK')-1);
437 l_trail := substr(l_html_pieces(i),instr(l_html_pieces(i),'css">')+5,length(l_html_pieces(i)));
438 htp.prn(l_front||l_trail);
439 l_cssFound := true;
440 else
441 htp.prn(l_html_pieces(i));
442 end if;
443 else
444 htp.prn(l_html_pieces(i));
445 end if;
446 end loop;
447 end if; -- file_content_type
448 end if;
449
450 -- 08/16/2001 - Show Portlet Status
451 showPortletStatus(l_html_pieces.count, last_upd);
452 /*
453 htp.p('<table width="100%" border="0" cellspacing="0" cellpadding="0">
454 <tr><td class=OraTipText>
455 <A href='||FND_WEB_CONFIG.trail_slash(icx_plug_utilities.getPLSQLagent)
456 ||'bis_trend_plug.view_report_from_portlet?pRegionCode='||r_region||'&pFunctionName='||l_function_name
457 || '&pScheduleId='||l_schedule_id
458 ||' target=_top>'||FND_MESSAGE.get_string('BIS','BIS_VIEW_REPORT')||'</A><br>
459 </td></tr></table>');
460 */
461 end if; -- file_id is not null
462 end if; -- p_delete
463 END IF; -- icx_sec.validatesession
464
465 exception
466 when others then
467 htp.header(5,SQLERRM);
468
469 end Show;
470
471 procedure setTableStyles is
472 begin
473
474 -- mdamle 04/27/01 - Override styles - remove text-indent - causing resizing problems in Netscape
475 -- gsanap 06/27/01 - modified tags added Border in the tags
476 -- Removed references to bismarli.css
477 -- mdamle 07/27/01 - Updated styles based on new bismarli.css
478 -- mdamle 08/20/01 - Replaced font 10pt with 9pt
479 htp.p('<STYLE TYPE="text/css">');
480
481 htp.p('.OraTable {BORDER-TOP: #cccc99 1px solid; BORDER-LEFT: #cccc99 1px solid;}');
482 htp.p('.OraTipText {COLOR: #336699; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt}');
483 htp.p('.OraTableTitle {BACKGROUND-COLOR: #ffffff; COLOR: #336699; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 14pt}');
484 htp.p('.OraTableControlBarText {BACKGROUND-COLOR: #cccc99; COLOR: #336699; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 11pt; FONT-WEIGHT: bold}');
485 htp.p('.OraTableColumnHeader {BACKGROUND-COLOR: #cccc99; COLOR: #336699; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt; FONT-WEIGHT: bold; TEXT-ALIGN: left; BORDER-BOTTOM: #f7f7e7 1px solid; BORDER-RIGHT: #f7f7e7 1px solid;}');
486 htp.p('.OraTableSortableColumnHeader {font-family:Arial,Helvetica,Geneva,sans-serif;font-size:9pt;font-weight:bold;text-align:left;background-color:#cccc99;color:#336699;cursor:hand;BORDER-BOTTOM: #f7f7e7 1px solid;BORDER-RIGHT: #f7f7e7 1px solid;}');
487 htp.p('A.OraTableSortableColumnHeader:link {BACKGROUND-COLOR: #cccc99; COLOR: #336699; CURSOR: hand; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt; FONT-WEIGHT: bold; TEXT-ALIGN: left; TEXT-DECORATION: none; }');
488 htp.p('A.OraTableSortableColumnHeader:active {BACKGROUND-COLOR: #cccc99; COLOR: #336699; CURSOR: hand; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt; FONT-WEIGHT: bold; TEXT-ALIGN: left; TEXT-DECORATION: none; }');
489 htp.p('A.OraTableSortableColumnHeader:visited {BACKGROUND-COLOR: #cccc99; COLOR: #336699; CURSOR: hand; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt; FONT-WEIGHT: bold; TEXT-ALIGN: left; TEXT-DECORATION: none; }');
490 htp.p('.OraTableRowHeader {BACKGROUND-COLOR: #cccc99;COLOR: #336699;FONT-FAMILY:Arial, Helvetica,Geneva,sans-serif;FONT-SIZE:9pt;FONT-WEIGHT:bold;TEXT-ALIGN:right;BORDER-BOTTOM: #f7f7e7 1px solid; BORDER-RIGHT: #f7f7e7 1px solid;}');
491 htp.p('.OraTableColumnFooter {BACKGROUND-COLOR: #cccc99; COLOR: #336699; FONT-FAMILY: Arial,Helvetica,Geneva,sans-serif;FONT-SIZE:9pt;FONT-WEIGHT:bold;PADDING-TOP:2px;TEXT-ALIGN:left;VERTICAL-ALIGN:top;BORDER-BOTTOM:#f7f7e7 1px solid;');
492 htp.p(' BORDER-RIGHT: #f7f7e7 1px solid;}');
493 htp.p('.OraTableTotal {BACKGROUND-COLOR: #cccc99; COLOR: #336699; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt; FONT-WEIGHT: bold; TEXT-ALIGN: right; BORDER-BOTTOM: #f7f7e7 1px solid; BORDER-RIGHT: #f7f7e7 1px solid;}');
494 htp.p('.OraTableTotalNumber {BACKGROUND-COLOR: #cccc99; COLOR: #000000; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt; FONT-WEIGHT: bold; TEXT-ALIGN: right; VERTICAL-ALIGN: baseline; BORDER-BOTTOM: #f7f7e7 1px solid;');
495 htp.p(' BORDER-RIGHT:');
496 htp.p(' #f7f7e7 1px solid;}');
497 htp.p('.OraTableTotalText {BACKGROUND-COLOR: #cccc99; COLOR: #000000; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt; FONT-WEIGHT: bold; TEXT-ALIGN: left; VERTICAL-ALIGN: baseline; BORDER-BOTTOM: #f7f7e7 1px solid; BORDER-RIGHT:');
498 htp.p(' #f7f7e7 1px solid;}');
499 htp.p('.OraTableCellText {BACKGROUND-COLOR: #f7f7e7; COLOR: #000000; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt; BORDER-BOTTOM: #cccc99 1px solid; BORDER-RIGHT: #cccc99 1px solid;}');
500 htp.p('.OraTableCellTextBand {BACKGROUND-COLOR: #ffffff; COLOR: #000000; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt; BORDER-BOTTOM: #cccc99 1px solid; BORDER-RIGHT: #cccc99 1px solid;}');
501 htp.p('.OraTableCellNumber {BACKGROUND-COLOR: #f7f7e7; COLOR: #000000; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt; TEXT-ALIGN: right; BORDER-BOTTOM: #cccc99 1px solid; BORDER-RIGHT: #cccc99 1px solid;}');
502 htp.p('.OraTableCellNumberBand {BACKGROUND-COLOR: #ffffff; COLOR: #000000; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt; TEXT-ALIGN: right; BORDER-BOTTOM: #cccc99 1px solid; BORDER-RIGHT: #cccc99 1px solid;}');
503 htp.p('.OraTableCellIconButton {BACKGROUND-COLOR: #f7f7e7; COLOR: #000000; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt; TEXT-ALIGN: center; BORDER-BOTTOM: #cccc99 1px solid; BORDER-RIGHT: #cccc99 1px solid;}');
504 htp.p('.OraTableCellIconButtonBand {BACKGROUND-COLOR: #ffffff; COLOR: #000000; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt; TEXT-ALIGN: center; BORDER-BOTTOM: #cccc99 1px solid; BORDER-RIGHT: #cccc99 1px solid;}');
505 htp.p('.OraTableCellSelect {BACKGROUND-COLOR: #f7f7e7; COLOR: #000000; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt; TEXT-ALIGN: center; BORDER-BOTTOM: #cccc99 1px solid; BORDER-RIGHT: #cccc99 1px solid;}');
506 htp.p('.OraTableCellSelectBand {BACKGROUND-COLOR: #ffffff; COLOR: #000000; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt; TEXT-ALIGN: center; BORDER-BOTTOM: #cccc99 1px solid; BORDER-RIGHT: #cccc99 1px solid;}');
507 htp.p('.OraTableVerticalGrid {BACKGROUND-COLOR: #cccc99; WIDTH: 1px}');
508 htp.p('.OraTableVerticalHeaderGrid {BACKGROUND-COLOR: #f7f7e7; WIDTH: 1px}');
509 htp.p('.OraTableHorizontalGrid {BACKGROUND-COLOR: #cccc99; WIDTH: 1px}');
510 htp.p('.OraTableHorizontalHeaderGrid {BACKGROUND-COLOR: #f7f7e7; WIDTH: 1px}');
511 htp.p('.OraTableShadowHeaderGrid {BACKGROUND-COLOR: #666633; WIDTH: 1px}');
512 htp.p('.OraTableHeaderLink {BACKGROUND-COLOR: #cccc99; COLOR: #336699; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt; FONT-WEIGHT: bold; TEXT-DECORATION: none; }');
513 htp.p('.OraTableAddTotal {font-family:Arial,Helvetica,Geneva,sans-serif;font-size:9pt;font-weight:bold;text-align:left;background-color:#cccc99;color:#336699;padding-top:2px;BORDER-BOTTOM: #f7f7e7 1px solid;BORDER-RIGHT: #f7f7e7 1px solid;}');
514 htp.p('.OraTableSortableColumnName {BACKGROUND-COLOR: #cccc99; COLOR: #336699; CURSOR: hand; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt; FONT-WEIGHT: bold; TEXT-ALIGN: left; TEXT-DECORATION: none; }');
515 -- 08/03/2001 - Added new tag for Right justified headers
516 htp.p('.OraTableSortableColumnNameNumber {BACKGROUND-COLOR: #cccc99; COLOR: #336699; CURSOR: hand; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt; FONT-WEIGHT: bold; TEXT-ALIGN: right; TEXT-DECORATION: none; }');
517 htp.p('.OraTableColumnHeaderNumber {BACKGROUND-COLOR: #cccc99; COLOR: #336699; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt; FONT-WEIGHT: bold; TEXT-ALIGN: right; BORDER-BOTTOM: #f7f7e7 1px solid; BORDER-RIGHT:');
518 htp.p(' #f7f7e7 1px solid;}');
519 htp.p('.OraHeader {COLOR: #336699; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 16pt}');
520 htp.p('.OraInstructionText {COLOR: #000000; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt}');
521 htp.p('.OraInstructionTextStrong {COLOR: #000000; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt; FONT-WEIGHT: bold}');
522 htp.p('.OraTableSortableColumnSpan {BACKGROUND-COLOR: #cccc99; COLOR: #336699; CURSOR: hand; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt; FONT-WEIGHT: bold; TEXT-ALIGN: center; TEXT-DECORATION: none; }');
523 htp.p('.OraTableColumnHeaderSpan {BACKGROUND-COLOR: #cccc99; COLOR: #336699; FONT-FAMILY: Arial, Helvetica, Geneva, sans-serif; FONT-SIZE: 9pt; FONT-WEIGHT: bold; TEXT-ALIGN: center; BORDER-BOTTOM: #f7f7e7 1px solid; BORDER-RIGHT:');
524 htp.p(' #f7f7e7 1px solid;}');
525
526 htp.p('</STYLE>');
527
528 end setTableStyles;
529
530 -- nbarik - 05/15/04 - Enhancement 3576963 - Drill Java Conversion
531 PROCEDURE VIEW_REPORT_FROM_PORTLET(
532 pRegionCode IN VARCHAR2
533 , pFunctionName IN VARCHAR2
534 , pScheduleId IN NUMBER
535 , pPageId IN VARCHAR2 DEFAULT NULL
536 , pObjectType IN VARCHAR2 DEFAULT NULL
537 , pResponsibilityId IN VARCHAR2 DEFAULT NULL
538 ) IS
539 l_jsp_params VARCHAR2(5000);
540 l_session_id VARCHAR2(80);
541 l_resp_id varchar2(80);
542 l_user_id varchar2(80);
543 l_application_id NUMBER;
544 l_function_id NUMBER;
545
546 CURSOR cFndResp (pRespId IN VARCHAR2) IS
547 SELECT application_id
548 FROM fnd_responsibility
549 WHERE responsibility_id = pRespId;
550
551 BEGIN
552 IF NOT icx_sec.ValidateSession THEN
553 RETURN;
554 END IF;
555
556 l_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
557 l_user_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
558 l_resp_id := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
559
560 IF cFNDResp%ISOPEN THEN
561 CLOSE cFNDResp;
562 END IF;
563 OPEN cFNDResp(l_resp_id);
564 FETCH cFNDResp INTO l_application_id;
565 CLOSE cFNDResp;
566
567 SELECT function_id
568 INTO l_function_id
569 FROM fnd_form_functions
570 WHERE function_name = 'BIS_PMV_DRILL_JSP';
571
572 l_jsp_params := 'pMode=7&pRegionCode=' || BIS_PMV_UTIL.encode(pRegionCode) || '&pFunction=' || BIS_PMV_UTIL.encode(pFunctionName) || '&pScheduleId=' || pScheduleId;
573 IF (pPageId IS NOT NULL) THEN
574 l_jsp_params := l_jsp_params || '&pPageId=' || pPageId;
575 END IF;
576 IF (pObjectType IS NOT NULL) THEN
577 l_jsp_params := l_jsp_params || '&pObjectType=' || pObjectType;
578 END IF;
579 IF (pResponsibilityId IS NOT NULL) THEN
580 l_jsp_params := l_jsp_params || '&pResponsibilityId=' || pResponsibilityId;
581 END IF;
582
583 OracleApps.runFunction (
584 c_function_id => l_function_id
585 , n_session_id => l_session_id
586 , c_parameters => l_jsp_params
587 , p_resp_appl_id => l_application_id
588 , p_responsibility_id => l_resp_id
589 , p_Security_group_id => icx_sec.g_security_group_id
590 );
591
592 EXCEPTION
593 WHEN OTHERS THEN
594 IF cFNDResp%ISOPEN THEN
595 CLOSE cFNDResp;
596 END IF;
597
598 END VIEW_REPORT_FROM_PORTLET;
599
600 /*
601 -- mdamle 11/7/2002 - Added pEnableForecastGraph
602 PROCEDURE VIEW_REPORT_FROM_PORTLET(pRegionCode in varchar2, pFunctionName in varchar2, pScheduleId in number,
603 pPageId IN VARCHAR2 default null,
604 pObjectType IN VARCHAR2 DEFAULT NULL,
605 pResponsibilityId IN VARCHAR2 DEFAULT NULL) is
606 CURSOR c_sched_attr IS
607 SELECT SESSION_VALUE,
608 SESSION_DESCRIPTION,
609 DEFAULT_VALUE,
610 DEFAULT_DESCRIPTION,
611 ATTRIBUTE_NAME,
612 DIMENSION,
613 PERIOD_DATE
614 FROM bis_user_attributes
615 WHERE schedule_id = pScheduleId;
616
617
618 CURSOR c_pageless_sched_attr(pUserId In VARCHAR2) IS
619 SELECT SESSION_VALUE,
620 SESSION_DESCRIPTION,
621 DEFAULT_VALUE,
622 DEFAULT_DESCRIPTION,
623 ATTRIBUTE_NAME,
624 DIMENSION,
625 PERIOD_DATE
626 FROM bis_user_attributes
627 WHERE schedule_id = pScheduleId
628 AND ((dimension IS NULL AND attribute_name not in (SELECT nvl(attribute_name,'-11')
629 FROM BIS_USER_ATTRIBUTES
630 WHERE page_id=pPageId
631 AND user_id=pUserId))
632 OR (dimension IS NOT NULL AND dimension not in (SELECT nvl(dimension,'-11')
633 FROM BIS_USER_ATTRIBUTES
634 WHERE page_id=pPageId
635 AND user_id=pUserId)));
636
637
638 CURSOR cFunctionParams (pFunctionName in varchar) is
639 select parameters
640 from fnd_form_functions
641 where function_name = pFunctionName;
642
643 --BugFix 2869306
644 CURSOR cPageTimeParams (cPageId varchar2, cUserId varchar2) is
645 select attribute_name
646 from bis_user_attributes
647 where user_id = cUserId
648 and page_id = cPageId
649 and dimension in ('TIME', 'EDW_TIME_M')
650 and substr(attribute_name, length(attribute_name)-length('_FROM')+1) = '_FROM';
651
652 --ksadagop BugFix 3265474
653 CURSOR cNonViewBy (cRegionCode varchar2, cSessionValue varchar2) is
654 select attribute2 from ak_region_items where region_code=cRegionCode and
655 attribute2=cSessionValue
656 AND attribute1 not in ('DIMENSION LEVEL', 'DIM LEVEL SINGLE VALUE', 'HIDE DIMENSION LEVEL');
657
658 vUserid varchar2(80);
659 vSessionId varchar2(80);
660 vResponsibilityId varchar2(80);
661 vEnableForecastGraph varchar2(1);
662 vParameters fnd_form_functions.parameters%TYPE;
663
664 -- jprabhud - 01/27/03 - Enh 2485974 Custom Views
665 vTitleCustomView fnd_form_functions.function_name%TYPE;
666 vReportFunctionName fnd_form_functions.function_name%TYPE;
667 vCustomView fnd_form_functions.function_name%TYPE;
668 vViewName fnd_form_functions.function_name%TYPE;
669 vFunctionName fnd_form_functions.function_name%TYPE;
670 vReportFunctionParameters fnd_form_functions.parameters%TYPE;
671 vRegionCode varchar2(300); --rcmuthuk 03/24/03 Bug#2856247
672 vSessionValue varchar2(240);
673 vSessionDesc varchar2(240);
674 --ksadagop BugFix 3265474
675 vViewBy varchar2(300);
676 vAttribute2 varchar2(300);
677 l_flag varchar2(1) := 'Y';
678
679
680 BEGIN -- validate session
681
682 if not icx_sec.ValidateSession then
683 return;
684 end if;
685
686 vSessionId := icx_sec.getID(icx_sec.PV_SESSION_ID);
687 vUserId := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
688
689 -- mdamle 11/07/2002 - Pass pEnableForecastGraph if needed.
690 if cFunctionParams%ISOPEN then
691 CLOSE cFunctionParams;
692 end if;
693 OPEN cFunctionParams(pFunctionName);
694 FETCH cFunctionParams INTO vParameters;
695 CLOSE cFunctionParams;
696
697 if vParameters is not null then
698 vEnableForecastGraph := BIS_PMV_UTIL.getParameterValue(vParameters, 'pEnableForecastGraph');
699 -- jprabhud - 01/27/03 - Enh 2485974 Custom Views
700 vTitleCustomView := BIS_PMV_UTIL.getParameterValue(vParameters, 'pTitleCustomView');
701
702 vReportFunctionName := BIS_PMV_UTIL.getParameterValue(vParameters, 'pReportFunctionName');
703 end if;
704
705 -- jprabhud - 01/27/03 - Enh 2485974 Custom Views
706 if vTitleCustomView is null then
707 if vReportFunctionName is not null then
708 if cFunctionParams%ISOPEN then
709 CLOSE cFunctionParams;
710 end if;
711 OPEN cFunctionParams(vReportFunctionName);
712 FETCH cFunctionParams INTO vReportFunctionParameters;
713 CLOSE cFunctionParams;
714 end if;
715 end if;
716
717 if vReportFunctionParameters is not null then
718 vCustomView := BIS_PMV_UTIL.getParameterValue(vReportFunctionParameters, 'pCustomView');
719 vRegionCode := BIS_PMV_UTIL.getParameterValue(vReportFunctionParameters, 'pRegionCode'); --rcmuthuk 03/24/03 Bug#2856247
720 end if;
721
722 -- rcmuthuk - 03/24/03 Bug#2856247
723 if vRegionCode is null then
724 vRegionCode := pRegionCode;
725 end if;
726
727 -- jprabhud - 01/27/03 - Enh 2485974 Custom Views
728 if vReportFunctionName is not null then
729 vFunctionName := vReportFunctionName;
730 else
731 vFunctionName := pFunctionName;
732 end if;
733
734 -- jprabhud - 01/27/03 - Enh 2485974 Custom Views
735 if vTitleCustomView is not null then
736 vViewName := vTitleCustomView;
737 else
738 vViewName := vCustomView;
739 end if;
740
741 IF pResponsibilityId IS NOT NULL THEN
742 vResponsibilityId := pResponsibilityId;
743 ELSE
744 BEGIN
745 SELECT responsibility_id
746 INTO vResponsibilityId
747 FROM bis_scheduler
748 WHERE schedule_Id = pScheduleId;
749 EXCEPTION
750 WHEN OTHERS THEN
751 NULL;
752 END;
753 END IF;
754
755
756 delete from bis_user_attributes
757 where user_id = vUserId
758 and function_name = vFunctionName -- jprabhud - 01/27/03 - Enh 2485974 Custom Views
759 and session_id = vSessionId
760 and schedule_id is null;
761
762 if (pPageId is not null) then
763 -- copy the page level paramters
764 insert into bis_user_attributes (USER_ID,
765 FUNCTION_NAME,
766 SESSION_ID,
767 SESSION_VALUE,
768 SESSION_DESCRIPTION,
769 DEFAULT_VALUE,
770 DEFAULT_DESCRIPTION,
771 ATTRIBUTE_NAME,
772 DIMENSION,
773 PERIOD_DATE)
774 SELECT
775 vUserId,
776 vFunctionName,-- jprabhud - 01/27/03 - Enh 2485974 Custom Views
777 vSessionId,
778 SESSION_VALUE,
779 SESSION_DESCRIPTION,
780 DEFAULT_VALUE,
781 DEFAULT_DESCRIPTION,
782 ATTRIBUTE_NAME,
783 DIMENSION,
784 PERIOD_DATE
785 FROM bis_user_attributes
786 WHERE user_id = vUserId
787 AND page_id =pPageId;
788
789 for c_rec in c_pageLess_sched_attr(vUserId) loop
790 vSessionValue := c_rec.SESSION_VALUE;
791 vSessionDesc := c_rec.SESSION_DESCRIPTION;
792 l_flag := 'Y';
793 --BugFix 2869306
794 if c_rec.ATTRIBUTE_NAME = 'VIEW_BY' then --BugFix 3281530.
795 --ksadagop BugFix 3265474
796 if cNonViewBy%ISOPEN then
797 CLOSE cNonViewBy;
798 end if;
799 OPEN cNonViewBy(vRegionCode, c_rec.SESSION_VALUE);
800 FETCH cNonViewBy INTO vAttribute2;
801 CLOSE cNonViewBy;
802 if vAttribute2 is null then
803 if (substr(c_rec.SESSION_VALUE, 1, length('TIME+')) = 'TIME+'
804 or substr(c_rec.SESSION_VALUE,1, length('EDW_TIME_M+')) = 'EDW_TIME_M+') then --BugFix 3281530.
805 if cPageTimeParams%ISOPEN then
806 CLOSE cPageTimeParams;
807 end if;
808 OPEN cPageTimeParams(pPageId, vUserId);
809 FETCH cPageTimeParams INTO vSessionValue;
810 CLOSE cPageTimeParams;
811 if vSessionValue is null then
812 vSessionValue := c_rec.SESSION_VALUE;
813 vSessionDesc := c_rec.SESSION_DESCRIPTION;
814 else
815 vSessionValue := substr(vSessionValue,1, length(vSessionValue)-length('_FROM'));
816 vSessionDesc := vSessionValue;
817 end if;
818 end if;
819 else
820 l_flag := 'N';
821 end if;
822 end if;
823 if(l_flag = 'Y') then --BugFix 3281530.
824 insert into bis_user_attributes (USER_ID,
825 FUNCTION_NAME,
826 SESSION_ID,
827 SESSION_VALUE,
828 SESSION_DESCRIPTION,
829 DEFAULT_VALUE,
830 DEFAULT_DESCRIPTION,
831 ATTRIBUTE_NAME,
832 DIMENSION,
833 PERIOD_DATE) values (vUserId,
834 vFunctionName, -- jprabhud - 01/27/03 - Enh 2485974 Custom Views
835 vSessionId,
836 vSessionValue,
837 vSessionDesc,
838 c_rec.DEFAULT_VALUE,
839 c_rec.DEFAULT_DESCRIPTION,
840 c_rec.ATTRIBUTE_NAME,
841 c_rec.DIMENSION,
842 c_rec.PERIOD_DATE);
843 end if;
844 end loop;
845
846 else
847 for c_rec in c_sched_attr loop
848 insert into bis_user_attributes (USER_ID,
849 FUNCTION_NAME,
850 SESSION_ID,
851 SESSION_VALUE,
852 SESSION_DESCRIPTION,
853 DEFAULT_VALUE,
854 DEFAULT_DESCRIPTION,
855 ATTRIBUTE_NAME,
856 DIMENSION,
857 PERIOD_DATE) values (vUserId,
858 vFunctionName, -- jprabhud - 01/27/03 - Enh 2485974 Custom Views
859 vSessionId,
860 c_rec.SESSION_VALUE,
861 c_rec.SESSION_DESCRIPTION,
862 c_rec.DEFAULT_VALUE,
863 c_rec.DEFAULT_DESCRIPTION,
864 c_rec.ATTRIBUTE_NAME,
865 c_rec.DIMENSION,
866 c_rec.PERIOD_DATE);
867 end loop;
868 commit;
869 end if;
870
871
872
873 bisviewer.showReport(pRegionCode=>vRegionCode
874 , pFunctionName=> vFunctionName -- jprabhud - 01/27/03 - Enh 2485974 Custom Views
875 , pSessionId=>vSessionId
876 , pUserId=>vUserId
877 , pResponsibilityId=>vResponsibilityId
878 , pFirstTime=>0
879 , pMode=>'DrillDown'
880 , pEnableForecastGraph=>vEnableForecastGraph
881 , pCustomView=>vViewName -- jprabhud - 01/27/03 - Enh 2485974 Custom Views
882 , pObjectType => pObjectType --Bug Fix 2997706
883 );
884
885 END VIEW_REPORT_FROM_PORTLET;
886 */
887
888 -- 08/16/2001 - Show Portlet Status
889 procedure showPortletStatus(p_report_available number, last_upd date) is
890 l_icx_date_format VARCHAR2(30);
891 begin
892 htp.p('<STYLE TYPE="text/css">');
893 -- mdamle 08/09/01 - Added border tag
894 -- mdamle 12/20/01 - Remove background color from last upd date
895 -- background-color:#cccc99;
896 htp.p('.OraPortletHeaderSub {font-family:Arial, Helvetica, Geneva, sans-serif; font-size:8pt;
897 text-align:right; color:#000000; vertical-align:baseline; BORDER-BOTTOM: #f7f7e7 1px solid;'||
898 'BORDER-RIGHT: #f7f7e7 1px solid;}');
899 htp.p('</STYLE>');
900
901 htp.p('<table width="100%" border="0" cellspacing="0" cellpadding="0">');
902
903 -- mdamle 08/16/01 - Add a retrieving portlet message
904 if p_report_available = 0 then
905 -- Add the retrieving portlet message
906 /*
907 htp.p('
908 <tr>
909 <td width="40" align="center"><img src="'||gvImageDirectory||'bisprocg.gif" width="32" height="32"></td>
910 <td nowrap class="OraHeader" valign=bottom>'||FND_MESSAGE.get_string('BIS','BIS_PROCESSING_PORTLET_HDR')||'</td>
911 </tr>');
912 htp.p('
913 <TR>
914 <td valign="top" width="40" align="center" height="1"><img src="'||gvImageDirectory||'bisspace.gif" height=1 width=10></td><TD bgcolor="#CCCC99" height="1"><IMG SRC="http://ap100jvm.us.oracle.com:8700/OA_MEDIA/bisspace.gif" height=1 width=1></TD>
915 </TR>');
916 htp.p('
917 <tr>
918 <td valign="top" width="40" align="center" height="5"><img src="'||gvImageDirectory||'bisspace.gif" height=1 width=10></td>
919 <td class="OraInstructionText" height="5"><img src="'||gvImageDirectory||'bisspace.gif" height=1 width=10></td>
920 </tr>');
921 */
922 htp.p('
923 <tr>
924 <td valign="top" width="40" align="center"> </td>
925 <td class="OraInstructionText">
926 <p><b> </b><span class="OraInstructionTextStrong">'||FND_MESSAGE.get_string('BIS','BIS_PROCESSING_PORTLET_DETAIL')||'</span></p>
927 </td>
928 </tr>');
929
930 else
931 -- Add last update date
932 l_icx_date_format := fnd_profile.value('ICX_DATE_FORMAT_MASK'); --Bug Fix 1917856 Use ICX Date Format
933 IF l_icx_date_format IS NOT NULL THEN
934 htp.p('<tr><td class=OraPortletHeaderSub >' ||TO_CHAR(last_upd,l_icx_date_format)||'</td></tr>');
935 ELSE
936 htp.p('<tr><td class=OraPortletHeaderSub >'
937 ||TO_CHAR(last_upd,'dd-Mon')
938 ||'</td></tr>');
939 END IF;
940 end if;
941
942 htp.p('</table>');
943
944 end showPortletStatus;
945
946 -- mdamle 10/30/01 - Converted plsql customize page to jsp
947 -- mdamle 11/1/2002 - Bug#2649477 - Support for ICX Patch
948 --PortletPersonalization: added pShowPortletSettings parameter -ansingh
949 procedure customizePortlet ( pResponsibilityId IN VARCHAR2 default NULL
950 ,pSessionId IN VARCHAR2 default NULL
951 ,Region_Code IN VARCHAR2 default NULL
952 ,Function_Name IN VARCHAR2 default NULL
953 ,pUserId IN VARCHAR2 default NULL
954 ,pPlugId IN VARCHAR2 default NULL
955 ,pScheduleId IN VARCHAR2 default NULL
956 ,pFileId IN VARCHAR2 default NULL
957 ,pScheduleOverride IN VARCHAR2 default 'N'
958 ,pShowPortletSettings IN VARCHAR2 DEFAULT NULL
959 ,pMsrId IN VARCHAR2 DEFAULT NULL
960 ,pComponentType IN VARCHAR2 DEFAULT NULL
961 ,pIsPrintable IN VARCHAR2 DEFAULT NULL
962 ,pReturnURL IN VARCHAR2 DEFAULT NULL
963 ) IS
964
965 vPageFunctionId NUMBER;
966 vPageURL varchar2(2000);
967
968 vParams varchar2(2000);
969 vRespId varchar2(80);
970 vApplicationId varchar2(80);
971 vSecGrpId varchar2(80);
972 vUserId varchar2(80);
973 vSessionId number;
974
975 begin
976
977 if not (icx_sec.validatesession) then
978 return;
979 end if;
980
981 vSessionId := icx_sec.getid(ICX_SEC.PV_SESSION_ID);
982 vUserId := icx_sec.getID(icx_sec.PV_USER_ID,'',vSessionId);
983
984 begin
985 select function_id into vPageFunctionId
986 from fnd_form_functions
987 where function_name = 'BIS_CUSTOMIZE_PORTLET_PAGE';
988 exception
989 when others then vPageFunctionId := null;
990 end;
991
992 vRespId := nvl(pResponsibilityId, icx_sec.getid(ICX_SEC.PV_RESPONSIBILITY_ID));
993 getRespInfo(vUserId, vRespId, vApplicationId, vSecGrpId);
994
995 icx_sec.updateSessionContext(function_name, null, vApplicationId, vRespId, vSecGrpId, vSessionId, null);
996
997 if vPageFunctionId is not null then
998
999 vParams := 'regionCode='||Region_Code||
1000 '&functionName='||function_name||
1001 '&pResponsibilityId='||vRespId||
1002 '&pUserId='||pUserId||
1003 '&pSessionId='||pSessionId||
1004 '&pPlugId='||pPlugId||
1005 '&pScheduleId='||pScheduleId||
1006 '&pFileId='||pFileId||
1007 '&pScheduleOverride='||pScheduleOverride||
1008 '&pShowPortletSettings='||pShowPortletSettings||
1009 '&pComponentType='||pComponentType||
1010 '&pMsrId='||pMsrId||
1011 '&pIsPrintable='||pIsPrintable||
1012 '&pReturnURL='||NVL(bis_pmv_util.encode(pReturnURL), '');
1013
1014
1015 OracleApps.runFunction(c_function_id => vPageFunctionId
1016 ,n_session_id => pSessionId
1017 ,c_parameters => vParams
1018 ,p_resp_appl_id => vApplicationId
1019 ,p_responsibility_id => vRespId
1020 ,p_Security_group_id => vSecGrpId
1021 );
1022
1023 end if;
1024
1025
1026 end customizePortlet;
1027
1028
1029 PROCEDURE initialiseRLPortlet (
1030 pUserID IN NUMBER,
1031 pReferencePath IN VARCHAR2,
1032 x_plug_id OUT NOCOPY NUMBER
1033 )
1034 IS
1035
1036 --l_plug_id NUMBER;
1037 l_responsibility_id NUMBER;
1038 lReturnStatus VARCHAR(1);
1039 lMsgData VARCHAR2(10);
1040 lMsgCount NUMBER;
1041 lScheduleId NUMBER;
1042 lFileId NUMBER;
1043 lTitle VARCHAR2(240);
1044 BEGIN
1045
1046 l_responsibility_id := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
1047
1048 SELECT icx_page_plugs_s.nextval INTO x_Plug_Id FROM DUAL;
1049
1050 -- bug 2842902 - removed user_id from where clause
1051 UPDATE icx_portlet_customizations
1052 SET plug_id = x_Plug_Id,
1053 -- title=lTitle,
1054 caching_key = TO_CHAR(TO_NUMBER(NVL(caching_key,'0'))+1)
1055 WHERE reference_path = pReferencePath ;
1056
1057 -- commit;
1058 END initialiseRLPortlet;
1059
1060 -- mdamle 10/29/2002 Bug#2560743 - Use previous page parameters for linked page
1061 procedure invokeBISRunFunction
1062 (function_id in number
1063 ,pFunctionName in VARCHAR2
1064 ,pWebHtmlCall IN VARCHAR2
1065 ,user_id in varchar2 default null
1066 ,responsibility_id in varchar2 default null
1067 ,responsibility_app_id in varchar2 default null
1068 ,session_id in varchar2 default null
1069 ,sec_grp_id in varchar2 default null
1070 -- jprabhud 03/04/2003 - Refresh Portal Page
1071 ,pSourcePageId in number default -1
1072 ,pParameters IN VARCHAR2 DEFAULT NULL
1073
1074 )
1075 IS
1076
1077 l_resp_id varchar2(80);
1078 l_resp_app_id varchar2(80);
1079 l_user_id varchar2(80);
1080 l_sec_grp_id varchar2(80);
1081 -- jprabhud 03/04/2003 - Refresh Portal Page
1082 jspParams varchar2(32767);
1083
1084 BEGIN
1085
1086 if not (icx_sec.validatesession) then
1087 return;
1088 end if;
1089
1090
1091 l_user_id := icx_sec.getID(icx_sec.PV_USER_ID,'',icx_sec.getid(ICX_SEC.PV_SESSION_ID));
1092
1093 if responsibility_id is not null then
1094 l_resp_id := responsibility_id;
1095 if responsibility_app_id is not null then
1096 l_resp_app_id := responsibility_app_id;
1097 end if;
1098 if sec_grp_id is not null then
1099 l_sec_grp_id := sec_grp_id;
1100 end if;
1101 end if;
1102
1103 if l_resp_id is null then
1104 l_resp_id := icx_sec.getid(ICX_SEC.PV_RESPONSIBILITY_ID);
1105 end if;
1106
1107 if l_resp_app_id is null or l_sec_grp_id is null then
1108 getRespInfo(l_user_id, l_resp_id, l_resp_app_id,l_sec_grp_id);
1109 end if;
1110
1111 jspParams := pParameters;
1112 if instr(pWebHtmlCall,'BIS_COMPONENT_PAGE')>0 then
1113 IF (jspParams IS NOT NULL) THEN
1114 jspParams := jspParams || '&';
1115 END IF;
1116 --nbarik - 04/20/04 - Enhancement 3378782 - Parameter Validation
1117 --jspParams := 'functionName='||pFunctionName;
1118 jspParams := jspParams || 'functionName='||pFunctionName;
1119 end if;
1120
1121 --jprabhud - 09/15/03 Toggle View Log -- added BIS_PMV_TOGGLE_VIEWLOG
1122 --jprabhud - 09/17/03 Toggle Perf View Log -- added BIS_PMV_TOGGLE_VIEWLOG_PERF
1123 --ksadagop - 12/02/03 Invalidate Fnd Cache -- added BIS_PMV_INVALIDATE_FNDCACHE
1124 if pFunctionName = 'BIS_PMV_PORTAL_REFRESH' or pFunctionName = 'BIS_PMV_INVALIDATE_AKCACHE'
1125 or pFunctionName ='BIS_PMV_TOGGLE_VIEWLOG' or pFunctionName ='BIS_PMV_TOGGLE_VIEWLOG_PERF' or pFunctionName = 'BIS_PMV_INVALIDATE_FNDCACHE' then
1126 if jspParams is not null then
1127 jspParams := jspParams || '&';
1128 end if;
1129 jspParams := jspParams||'pSourcePageId='||pSourcePageId;
1130 end if;
1131
1132 /* aleung, comment out for bug 3113469 & bug 3088087
1133 -- jprabhud 03/04/2003 - Refresh Portal Page
1134 l_function_name := getFunctionName(function_id);
1135 -- jprabhud 06/13/2003 - enh 2999555 Invalidate Ak Cache
1136 -- nbarik - 08/11/03 - Bug 3088087 - Move this inside if condition
1137 -- jspParams := 'functionName='||l_function_name;
1138 if l_function_name = 'BIS_PMV_PORTAL_REFRESH' or l_function_name = 'BIS_PMV_INVALIDATE_AKCACHE' then
1139 jspParams := 'functionName='||l_function_name||'&pSourcePageId='||pSourcePageId;
1140 --jspParams := '&pSourcePageId='||pSourcePageId;
1141 end if;
1142 */
1143
1144 --jprabhud - 11/13/03 - Bug 3253597
1145 BIS_PMV_UTIL.bis_run_function( l_resp_app_id,
1146 l_resp_id,
1147 l_sec_grp_id ,
1148 function_id ,
1149 jspParams );
1150 /*
1151 OracleApps.runFunction(c_function_id => function_id
1152 ,n_session_id => icx_sec.getid(ICX_SEC.PV_SESSION_ID)
1153 -- jprabhud 03/04/2003 - Refresh Portal Page
1154 ,c_parameters => jspParams
1155 ,p_resp_appl_id => l_resp_app_id
1156 ,p_responsibility_id => l_resp_id
1157 ,p_Security_group_id => l_sec_grp_id
1158 );
1159 */
1160 END;
1161
1162 -- mdamle 10/29/2002 Bug#2560743 - Use previous page parameters for linked page
1163 procedure invokeRFFunction
1164 (function_id in number
1165 ,user_id in varchar2 default null
1166 ,responsibility_id in varchar2 default null
1167 ,responsibility_app_id in varchar2 default null
1168 ,session_id in varchar2 default null
1169 ,sec_grp_id in varchar2 default null
1170 -- jprabhud 03/04/2003 - Refresh Portal Page
1171 ,pSourcePageId in number default -1
1172 ,pDrillDefaultParameters IN VARCHAR2 DEFAULT NULL
1173 )
1174 IS
1175 CURSOR cFunction (pFunctionId in number) is
1176 select function_name, web_html_call
1177 from fnd_form_functions
1178 where function_id = pFunctionId;
1179
1180 l_function_name fnd_form_functions.function_name%TYPE;
1181 lwebhtmlcall varchar2(2000);
1182 BEGIN
1183 --aleung, fix for bug 3113469 & bug 3088087
1184 if cFunction%ISOPEN then
1185 CLOSE cFunction;
1186 end if;
1187 OPEN cFunction(function_id);
1188 FETCH cFunction INTO l_function_name,lwebhtmlcall;
1189 CLOSE cFunction;
1190
1191 invokeBISRunFunction
1192 (function_id
1193 ,l_function_name
1194 ,lwebhtmlcall
1195 ,user_id
1196 ,responsibility_id
1197 ,responsibility_app_id
1198 ,session_id
1199 ,sec_grp_id
1200 -- jprabhud 03/04/2003 - Refresh Portal Page
1201 ,pSourcePageId
1202 --nbarik - 04/20/04 - Enhancement 3378782 - Parameter Validation
1203 --,null
1204 , pDrillDefaultParameters
1205 );
1206
1207 END invokeRFFunction;
1208
1209 --serao -08/30/2002- for user_customizations of the related link portlet-bug 2532340
1210
1211 PROCEDURE checkUsercustomizeRLPortlet (
1212 pUserId In NUMBER,
1213 pPlugId IN NUMBER,
1214 xCustomised OUT NOCOPY NUMBER
1215 ) IS
1216
1217 lUserId NUMBER;
1218
1219 CURSOR userRowInBisScheduler IS
1220 SELECT user_id
1221 FROM bis_schedule_preferences
1222 WHERE plug_id=pPlugId
1223 AND user_id =to_char(pUserId);
1224
1225 BEGIN
1226
1227 xCustomised := 0;
1228
1229 -- validate session ?
1230 -- first check if this user has customized the portlet
1231 if userRowInBisScheduler%ISOPEN then
1232 close userRowInBisScheduler;
1233 end if;
1234 open userRowInBisScheduler();
1235 fetch userRowInBisScheduler into lUserId;
1236 close userRowInBisScheduler;
1237
1238 IF lUserID IS NOT NULL THEN
1239 xCustomised := 1;
1240 END IF;
1241
1242 END checkUsercustomizeRLPortlet ;
1243
1244
1245 PROCEDURE createUserCustPlugRecord (
1246 pFunctionName IN VARCHAR2,
1247 pUserId In NUMBER,
1248 pPlugId IN NUMBER
1249 ) IS
1250
1251 l_responsibility_id NUMBER;
1252 lScheduleId NUMBER;
1253 lFileId NUMBER;
1254 lReturnStatus VARCHAR(1);
1255 lMsgData VARCHAR2(10);
1256 lMsgCount NUMBER;
1257
1258 BEGIN
1259
1260 -- validate sessionx
1261 l_responsibility_id := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
1262 -- add row in the bis_scheduler preferences table
1263 BIS_RG_SCHEDULES_PVT.CREATE_SCHEDULE_NO_COMMIT (
1264 p_plug_id => pPlugId,
1265 p_user_id => pUserId,
1266 p_function_name => pFunctionName,
1267 p_responsibility_id =>l_Responsibility_Id,
1268 x_schedule_id => lScheduleId,
1269 x_file_id => lFileId,
1270 x_return_status =>lReturnStatus,
1271 x_msg_data => lMsgData,
1272 x_msg_count => lMsgCount,
1273 p_live_portlet => 'Y'
1274 );
1275
1276 END createUserCustPlugRecord;
1277
1278 -- mdamle 11/1/2002 - Bug#2649477 - Support for ICX Patch
1279 procedure getRespInfo( pUserId in number
1280 , pRespId in number
1281 , pRespAppId out NOCOPY number
1282 , pSecGrpId out NOCOPY number) is
1283
1284 CURSOR cUserResp (pUserId in number, pRespId in number) is
1285 select responsibility_application_id, security_group_id
1286 from fnd_user_resp_groups
1287 where user_id = pUserId
1288 and responsibility_id = pRespId
1289 and rownum = 1;
1290
1291 begin
1292
1293 if cUserResp%ISOPEN then
1294 CLOSE cUserResp;
1295 end if;
1296 OPEN cUserResp(pUserId, pRespId);
1297 FETCH cUserResp INTO pRespAppId, pSecGrpId;
1298 CLOSE cUserResp;
1299
1300 end getRespInfo;
1301
1302 -- mdamle 11/1/2002 - Bug#2649477 - Support for ICX Patch
1303 function getFunctionId (pFunctionName varchar2) return number is
1304
1305 CURSOR cFunction (pFunctionName in varchar2) is
1306 select function_id
1307 from fnd_form_functions
1308 where function_name = pFunctionName;
1309
1310 l_function_id number;
1311 begin
1312
1313 if cFunction%ISOPEN then
1314 CLOSE cFunction;
1315 end if;
1316 OPEN cFunction(pFunctionName);
1317 FETCH cFunction INTO l_function_id;
1318 CLOSE cFunction;
1319
1320 return l_function_id;
1321
1322 end getFunctionId;
1323
1324 --BugFix 3417356
1325 FUNCTION getPageIdFromFunctionId (
1326 pPageFunctionId IN NUMBER
1327 ) RETURN NUMBER
1328 IS
1329 lDestPageId number;
1330
1331 BEGIN
1332 lDestPageId := 0 - pPageFunctionId;
1333 RETURN lDestPageId;
1334
1335 END getPageIdFromFunctionId;
1336
1337 /*
1338 FUNCTION getPageIdFromFunctionId (
1339 pPageFunctionId IN NUMBER
1340 ) RETURN NUMBER
1341 IS
1342
1343 CURSOR cPageInfo (pFunctionId in number) is
1344 select parameters, web_html_call
1345 from fnd_form_functions
1346 where function_id = pFunctionId;
1347
1348 cursor cMenuInfo (pMenuName varchar2) IS
1349 select menu_id
1350 from fnd_menus
1351 where menu_name=pMenuName;
1352
1353 lParameters fnd_form_functions.parameters%TYPE;
1354 lPageName varchar2(240);
1355 lDestPageId number;
1356 lMenuName varchar2(2000);
1357 lwebhtmlcall varchar2(2000);
1358 lSourceType varchar2(80);
1359
1360 BEGIN
1361
1362 if cPageInfo%ISOPEN then
1363 CLOSE cPageInfo;
1364 end if;
1365
1366 OPEN cPageInfo(pPageFunctionId);
1367 FETCH cPageInfo INTO lParameters,lwebhtmlcall;
1368 CLOSE cPageInfo;
1369
1370 if lParameters is not null then
1371 lPageName := BIS_PMV_UTIL.getParameterValue(upper(lParameters), 'PAGENAME');
1372 lSourceType := BIS_PMV_UTIL.getParameterValue(upper(lParameters), 'SOURCETYPE');
1373
1374 if (lPageName is not null and lSourceType is null) then
1375 lDestPageId := BIS_PMV_UTIL.getPortalPageId(lPageName);
1376 else
1377 if lPageName is not null then
1378 lMenuName := lPageName;
1379 else
1380 lMenuName := BIS_PMV_UTIL.getParameterValue(upper(lwebhtmlcall),'PAGENAME');
1381 end if;
1382
1383 if (lMenuName is not null) then
1384 if cMenuInfo%ISOPEN then
1385 CLOSE cMenuInfo;
1386 end if;
1387 begin
1388 OPEN cMenuInfo(lMenuName);
1389 FETCH cMenuInfo into ldestPageId;
1390 CLOSE cMenuInfo;
1391 exception when others then null;
1392 end;
1393 if (ldestpageid is not null) then
1394 ldestpageid := 0-ldestpageid;
1395 end if;
1396
1397 end if;
1398 end if;
1399 end if;
1400
1401 -- Fix for P1 2962792 : kiprabha
1402 -- Moved the RETURN out of the IF condition
1403 RETURN lDestPageId;
1404 END getPageIdFromFunctionId;
1405 */
1406
1407 PROCEDURE processLinkedPage(
1408 pSourcePageId in varchar2
1409 ,pDestFunctionId in number
1410 ,pSessionId IN VARCHAR2
1411 ,pUserId IN NUMBER
1412 ,xParamRegionCode OUT NOCOPY VARCHAR2
1413 ,xParamFunctionName OUT NOCOPY VARCHAR2
1414 , xParamGroup OUT NOCOPY BIS_PMV_PARAMETERS_PVT.parameter_group_tbl_type
1415 , xSourcePageId OUT NOCOPY NUMBER
1416 , xDestPageId OUT NOCOPY NUMBER
1417 -- nbarik - 04/20/04 - Enhancement 3378782 - Parameter Validation
1418 , x_DrillDefaultParameters OUT NOCOPY VARCHAR2
1419 ) IS
1420 CURSOR cPageInfo (pFunctionId in number) is
1421 select parameters
1422 from fnd_form_functions
1423 where function_id = pFunctionId;
1424
1425
1426 lParameters fnd_form_functions.parameters%TYPE;
1427 lPageName varchar2(240);
1428
1429 x_return_status varchar2(80);
1430 x_msg_count number;
1431 x_msg_data varchar2(80);
1432
1433 --jprabhud 11/13/03 - Bug 3253597
1434 n number := 0;
1435 BEGIN
1436
1437 xDestPageId := getPageIdFromFunctionId(pDestFunctionId);
1438
1439 --jprabhud 11/13/03 - Bug 3253597 - check if srcPageId has a comma(,) in it
1440 n := instr(pSourcePageId, ',');
1441 if n <= 0 then
1442 --jprabhud 11/13/03 - Bug 3253597
1443 xSourcePageId := pSourcePageId;
1444 -- If PageId is not null, then copy parameters from source page to dest page
1445 if xDestPageId is not null then
1446 --serao - 04/03, added sessionId
1447 BIS_PMV_PARAMETERS_PVT.copyParamtersBetweenPages(
1448 pSessionId,
1449 --jprabhud 11/13/03 - Bug 3253597
1450 xSourcePageId,
1451 xDestPageId,
1452 pUserId,
1453 xParamRegionCode ,
1454 xParamFunctionName ,
1455 xParamGroup ,
1456 -- nbarik - 04/20/04 - Enhancement 3378782 - Parameter Validation
1457 x_DrillDefaultParameters,
1458 x_return_status,
1459 x_msg_count,
1460 x_msg_data);
1461
1462 BIS_PMV_UTIL.update_portlets_bypage(xDestPageId);
1463
1464 end if;
1465 else
1466 xSourcePageId := 0;
1467 end if;
1468
1469 END processLinkedPage;
1470
1471 -- mdamle 10/31/2002 - Bug#2560743 - Use previous page parameters for linked page
1472 --jprabhud 11/13/03 - Bug 3253597
1473 procedure launchLinkedPage( pSourcePageId in varchar2 --number
1474 ,pDestFunctionId in number
1475 ,pRespId in varchar2 default null
1476 ,pRespAppId in varchar2 default null
1477 ,pSecGrpId in varchar2 default null) is
1478
1479 lSourcePageId NUMBER ;
1480 lUserId number;
1481 --serao -04/03, added sessionId
1482 lSessionId VARCHAR2(80);
1483 lDestPageID NUMBER;
1484 lParamGroup BIS_PMV_PARAMETERS_PVT.parameter_group_tbl_type;
1485 lParamRegionCode VARCHAR2(30);
1486 -- udua - 09.27.05 - R12 Mandatory Project - 4480009 [PMV Data-model Change].
1487 lParamFunctionName VARCHAR2(480);
1488 -- nbarik - 04/20/04 - Enhancement 3378782 - Parameter Validation
1489 l_DrillDefaultParameters VARCHAR2(3000);
1490 begin
1491
1492 if not (icx_sec.validatesession) then
1493 return;
1494 end if;
1495
1496 lSessionId := icx_sec.getId(ICX_SEC.PV_SESSION_ID);
1497 lUserId := icx_sec.getID(icx_sec.PV_USER_ID,'',lSessionId);
1498
1499 processLinkedPage(
1500 pSourcePageId
1501 ,pDestFunctionId
1502 ,lSessionId
1503 ,lUserId
1504 ,lParamRegionCode
1505 ,lParamFunctionName
1506 ,lParamGroup
1507 ,lSourcePageId
1508 ,lDestPageId
1509 -- nbarik - 04/20/04 - Enhancement 3378782 - Parameter Validation
1510 ,l_DrillDefaultParameters
1511 );
1512
1513 invokeRFFunction(function_id => pDestFunctionId,
1514 responsibility_id => pRespId,
1515 responsibility_app_id => pRespAppId,
1516 sec_grp_id => pSecGrpId,
1517 -- jprabhud 03/04/2003 - Refresh Portal Page
1518 --jprabhud 11/13/03 - Bug 3253597
1519 pSourcePageId => lSourcePageId,
1520 -- nbarik - 04/20/04 - Enhancement 3378782 - Parameter Validation
1521 pDrillDefaultParameters => l_DrillDefaultParameters);
1522
1523 end launchLinkedPage;
1524
1525 -- jprabhud 03/04/2003 - Refresh Portal Page
1526 function getFunctionName (pFunctionId number) return varchar2
1527 is
1528
1529 CURSOR cFunction (pFunctionId in number) is
1530 select function_name
1531 from fnd_form_functions
1532 where function_id = pFunctionId;
1533
1534 l_function_name fnd_form_functions.function_name%TYPE;
1535 begin
1536
1537 if cFunction%ISOPEN then
1538 CLOSE cFunction;
1539 end if;
1540 OPEN cFunction(pFunctionId);
1541 FETCH cFunction INTO l_function_name;
1542 CLOSE cFunction;
1543
1544 return l_function_name;
1545
1546 end getFunctionName;
1547
1548 -- gsanap 04/03/2003 - added to get user_function_name if prompt is null
1549 function getUserFunctionName (pFunctionId number) return varchar2
1550 is
1551
1552 CURSOR cFunction (pFunctionId in number) is
1553 select user_function_name
1554 from fnd_form_functions_tl
1555 where function_id = pFunctionId;
1556
1557 -- udua - 09.27.05 - R12 Mandatory Project - 4480009 [PMV Data-model Change].
1558 l_user_function_name varchar2(480);
1559 begin
1560
1561 if cFunction%ISOPEN then
1562 CLOSE cFunction;
1563 end if;
1564 OPEN cFunction(pFunctionId);
1565 FETCH cFunction INTO l_user_function_name;
1566 CLOSE cFunction;
1567
1568 return l_user_function_name;
1569
1570 end getUserFunctionName;
1571
1572 PROCEDURE processPageFromReport(
1573 pFunctionName In VARCHAR2
1574 ,pDestFunctionId IN NUMBER
1575 ,pSessionId IN VARCHAR2
1576 ,pUserId IN NUMBER
1577 , xDestPageID OUT NOCOPY NUMBER
1578 , xParamRegionCode OUT NOCOPY VARCHAR2
1579 , xParamFunctionName OUT NOCOPY VARCHAR2
1580 ,xParamGroup OUT NOCOPY BIS_PMV_PARAMETERS_PVT.parameter_group_tbl_type
1581 -- nbarik - 04/20/04 - Enhancement 3378782 - Parameter Validation
1582 ,x_DrillDefaultParameters OUT NOCOPY VARCHAR2
1583 ,x_return_status OUT NOCOPY varchar2
1584 ,x_msg_count OUT NOCOPY number
1585 ,x_msg_data OUT NOCOPY varchar2
1586 ) IS
1587
1588 BEGIN
1589
1590
1591 xDestPageID := getPageIdFromFunctionId(pDestFunctionId);
1592 IF xDestPageID IS NOT NULL THEN
1593 -- copy the parameters from the current report to the page
1594 BIS_PMV_PARAMETERS_PVT.copyParamsFromReportToPage(
1595 pFunctionName,
1596 pSessionId,
1597 pUserId,
1598 xDestPageID,
1599 xParamRegionCode,
1600 xParamFunctionName,
1601 xParamGroup ,
1602 -- nbarik - 04/20/04 - Enhancement 3378782 - Parameter Validation
1603 x_DrillDefaultParameters,
1604 x_return_status,
1605 x_msg_count,
1606 x_msg_data
1607 );
1608
1609 BIS_PMV_UTIL.update_portlets_bypage(xDestPageID);
1610 END IF;
1611
1612 END processPageFromReport;
1613
1614 PROCEDURE launchPageFromReport(
1615 pFunctionName IN VARCHAR2
1616 ,pDestFunctionId in number
1617 ,pRespId in varchar2 default null
1618 ,pRespAppId in varchar2 default null
1619 ,pSecGrpId in varchar2 default null
1620 ) IS
1621 lDestPageId number;
1622 lUserId number;
1623 x_return_status varchar2(80);
1624 x_msg_count number;
1625 x_msg_data varchar2(80);
1626 lSessionId VARCHAR2(80);
1627 lParamRegionCode VARCHAR2(30);
1628 -- udua - 09.27.05 - R12 Mandatory Project - 4480009 [PMV Data-model Change].
1629 lParamFunctionName VARCHAR2(480);
1630 lParamGroup BIS_PMV_PARAMETERS_PVT.parameter_group_tbl_type;
1631 -- nbarik - 04/20/04 - Enhancement 3378782 - Parameter Validation
1632 l_DrillDefaultParameters VARCHAR2(3000);
1633 BEGIN
1634
1635 if not (icx_sec.validatesession) then
1636 return;
1637 end if;
1638
1639 lSessionId := icx_sec.getId(ICX_SEC.PV_SESSION_ID);
1640 lUserId := icx_sec.getID(icx_sec.PV_USER_ID,'',lSessionId);
1641
1642 processPageFromReport(
1643 pFunctionName
1644 ,pDestFunctionId
1645 ,lSessionId
1646 ,lUserId
1647 ,lDestPageId
1648 ,lParamRegionCode
1649 ,lParamFunctionName
1650 ,lParamGroup
1651 -- nbarik - 04/20/04 - Enhancement 3378782 - Parameter Validation
1652 ,l_DrillDefaultParameters
1653 ,x_return_status
1654 ,x_msg_count
1655 ,x_msg_data
1656 );
1657
1658 -- Launch Dest page.
1659 invokeRFFunction(function_id => pDestFunctionId,
1660 responsibility_id => pRespId,
1661 responsibility_app_id => pRespAppId,
1662 sec_grp_id => pSecGrpId,
1663 -- nbarik - 04/20/04 - Enhancement 3378782 - Parameter Validation
1664 pDrillDefaultParameters => l_DrillDefaultParameters
1665 );
1666
1667 END launchPageFromReport;
1668
1669 PROCEDURE checkAndSetRL (
1670 pUserId IN VARCHAR2,
1671 pPlugId IN VARCHAR2,
1672 pFunctionName IN VARCHAR2
1673 ) IS
1674 lIsCustomised NUMBER;
1675 BEGIN
1676
1677
1678 checkUsercustomizeRLPortlet (
1679 pUserId => pUserId,
1680 pPlugId => pPlugId,
1681 xCustomised => lIsCustomised
1682 ) ;
1683
1684 --if not customised then add the links
1685 IF (lIsCustomised <> 1) THEN
1686
1687 createUserCustPlugRecord (
1688 pFunctionName => pFunctionName,
1689 pUserId => pUserId,
1690 pPlugId => pPlugId
1691 );
1692
1693 BIS_RL_PKG.add_rl_from_function( pFunctionName , pUserId , pPlugId);
1694 ELSE
1695 -- mdamle 03/12/04 - Enh 3503753 - Site level custom. for links
1696 -- Plug id exists in bis_schedule_preferences, but,
1697 -- check custom table also, just incase user has deleted links manually
1698 select count(*) into lIsCustomised from bis_custom_related_links where function_id = pPlugId and level_user_id = pUserId;
1699
1700 if lIsCustomised = 0 then
1701 BIS_RL_PKG.add_rl_from_function( pFunctionName , pUserId , pPlugId);
1702 end if;
1703
1704 END IF;
1705
1706 -- commit to be issued by caller
1707 END checkAndSetRL;
1708
1709 end bis_trend_plug;