[Home] [Help]
PACKAGE BODY: APPS.BIS_GRAPH_REGION_HTML_FORMS
Source
1 PACKAGE BODY BIS_GRAPH_REGION_HTML_FORMS AS
2 /* $Header: BISCHRFB.pls 120.3 2006/03/27 16:28:42 nbarik noship $ */
3 --- Copyright (c) 2000 Oracle Corporation, Redwood Shores, CA, USA
4 --- All rights reserved.
5 ---
6 ---==========================================================================
7 --- filename
8 ---
9 --- bischrfb.pls
10 ---
11 --- description
12 --- package body file for displaying the three
13 --- html forms in which to
14 --- enter parameters to be stored for a php chart
15 ---
16 --- notes
17 ---
18 --- history
19 ---
20 --- 20-Jun-2000 Walid.Nasrallah created
21 --- 28-sep-2000 walid.nasrallah modified call to build_html_banner
22 --- 03-oct-2000 walid.nasrallah enabled preview by inserting calls to
23 --- bis_trend_plug.get_graph_from_URL
24 --- 04-oct-2000 walid.nasrallah enabled exclusion of designated reports
25 --- 05-Oct-2000 Walid.Nasrallah moved "WHO" column defintion to database
26 --- 10-Oct-2000 Walid.Nasrallah added comments to function definitions
27 --- added code to clear cookie at cancel
28 --- moved exception messages into html comments
29 --- invisible to end users
30 --- 11-Oct-2000 Walid.Nasrallah added call to fnd_global.apps_initialize
31 --- 12-Oct-2000 Walid.Nasrallah replaced apps_initialize with update
32 --- icx_page_plugs
33 --- 17-Oct-2000 Walid.Nasrallah added special case for HRI report preview
34 ---
35 --- 22-Jan-2001 Ganesh.Sanap Removed all the Code that was commented out
36 --- from the previous version
37 --- 05-Mar-2001 Maneesha.Damle Bug#1652353 Fix - Parameters LOV does not
38 --- update field on parent form in IE
39 --- 21-Mar-2001 Maneesha.Damle Wrapper routines to return Resp & Funcns
40 --- lists to java
41 --- 29-May-2001 Maneesha.Damle Added function hasFunctionAccess()
42 --- 31-May-2001 Maneesha.Damle New ICX Profile for OA_HTML, OA_MEDIA
43 --- 28-Jun-2001 Maneesha.Damle fixed bug in has_good_report
44 --- 12-Nov-2001 Maneesha.Damle Use FND security function to check function access
45 --- 08-Jan-2002 Maneesha.Damle Check for valid region in get_accessible_functions
46 ------======================================================================
47
48 --- *********************************************
49 --- global variables
50 --- *****************************************
51
52 g_help_target_name constant varchar2(200) := 'bistrnd';
53 g_and constant varchar2(5) := '&' || '&';
54 g_nbsp constant varchar2(200) := '&'||'nbsp;';
55 g_initialize constant varchar2(2000) := '12345678901234567890123456789012345';
56 g_sep constant varchar2(10) := BIS_GRAPH_REGION_UI.g_sep;
57
58 g_saved_appl_id pls_integer;
59
60 -- mdamle 05/31/2001 - New ICX Profile for OA_HTML, OA_MEDIA
61 -- g_images varchar2(1000) := FND_PROFILE.value('ICX_REPORT_IMAGES');
62 g_images varchar2(1000) := BIS_REPORT_UTIL_PVT.get_Images_Server;
63 g_ImageDirectory varchar2(1000) := FND_WEB_CONFIG.TRAIL_SLASH(g_images);
64
65 -- mdamle 05/31/2001 - New ICX Profile for OA_HTML, OA_MEDIA
66 -- g_css varchar2(1000) := FND_PROFILE.value('ICX_OA_HTML');
67
68 -- g_CSSDirectory varchar2(1000) := '/' || FND_WEB_CONFIG.TRAIL_SLASH(g_css);
69 g_CSSDirectory varchar2(1000) := BIS_REPORT_UTIL_PVT.get_html_server;
70
71 /*
72 --- *********************************************
73 --- Type declarations
74 --- *****************************************
75
76 TYPE t_resp_rec IS RECORD(
77 responsibility_name
78 fnd_responsibility_vl.responsibility_name%TYPE
79 , responsibility_id
80 fnd_responsibility.responsibility_id%TYPE
81 , application_id
82 fnd_responsibility.application_id%TYPE
83 , security_group_id
84 fnd_user_resp_groups.security_group_id%TYPE
85 );
86
87 TYPE t_resp_tbl_type IS TABLE OF t_resp_rec;
88
89
90 TYPE t_func_rec IS RECORD(menu_name
91 fnd_menu_entries_vl.prompt%TYPE
92 ,web_html_call
93 fnd_form_functions.web_html_call%TYPE
94 ,web_args
95 fnd_form_functions.web_html_call%TYPE
96 ,parameters
97 fnd_form_functions.parameters%TYPE
98 ,function_id
99 fnd_form_functions.function_id%TYPE
100 -- mdamle 03/21/2001
101 ,menu_id
102 fnd_menu_entries.menu_id%TYPE
103 );
104
105
106 TYPE t_func_tbl_type IS TABLE OF t_func_rec;
107
108 TYPE t_menu_tbl_type IS TABLE OF fnd_responsibility.menu_id%TYPE;
109
110 */
111 ---==========================================================================
112 --- FUNCTION has_good_report
113 ---
114 --- arguments:
115 --- IN: a record out of the fnd_form_functions table
116 --- OUT: a boolean
117 ---
118 --- action: Returns TRUE if the funciton described int he record
119 --- meets certain criteria. The criteria are meant to be
120 --- edited during code development. Currently, the criteria
121 --- correspond to fomr funcitrons which call three types of
122 --- reports: Reprot Generator reports, Oracle Reports reports
123 --- with normal parameter forms, and Oracel Reports reprots
124 --- with custom parameter fomrs written as PL/SQL packages
125 --- according to BIS specification.
126 ---
127 ---==========================================================================
128
129
130 -- 11/12/01 mdamle - Passing only web_html_call instead of the function record
131 FUNCTION has_good_report(pWebHTMLCall in varchar2)
132 return boolean
133 is
134 l_call varchar(4000);
135 l_dummy pls_integer;
136 begin
137
138 l_call := upper(pWebHTMLCall);
139
140 if (l_call = 'ORACLEOASIS.RUNREPORT') THEN --- Regular Oracle Reports function
141 return false;
142 elsif l_call like '%_PARAMETER%FORMVIEW%' THEN --- Oracle Reprots with custom package for parameter form
143 return false;
144 -- mdamle 06/28/01 - Changed from = to like
145 elsif upper(l_call) like 'BISVIEWER%' THEN --- BIS Rerpot Generator
146 return true;
147 -- nbarik - 10/04/05 - Bug Fix 4633433
148 elsif (trim(pWebHTMLCall) like '%page=/oracle/apps/bis/report/webui/BISReportPG%') THEN --- BIS Rerpot Generator
149 return true;
150 -- mdamle 12/28/01
151 elsif l_call like '%BIS_PM_PORTLET_TABLE_LAYOUT%' then
152 return true;
153 else --- ALL OTHERS (Discoverer workbooks, concurrent requests, etc.)
154 return false;
155 end if;
156
157 EXCEPTION
158 WHEN OTHERS THEN
159 RETURN FALSE;
160
161 end has_good_report;
162
163
164 PROCEDURE Review_Chart_Render
165 ( p_user_id in PLS_INTEGER
166 , p_parameter_string in VARCHAR2
167 )
168 is
169
170 l_plug_id PLS_INTEGER;
171 l_session_id PLS_INTEGER;
172 l_user_id PLS_INTEGER;
173 l_responsibility_id PLS_INTEGER;
174 l_function_id PLS_INTEGER;
175 l_application_id PLS_INTEGER;
176 l_security_group_id PLS_INTEGER;
177 c_call PLS_INTEGER;
178 c_dummy PLS_INTEGER;
179 l_page_title varchar2(240);
180 l_chart_title_prompt varchar2(240);
181 l_report_title_prompt varchar2(240);
182 l_back_btn_txt varchar2(240);
183 l_submit_btn_txt varchar2(240);
184 l_cancel_btn_txt varchar2(240);
185 l_report_name varchar2(240);
186 l_function_parameters varchar2(32000);
187 l_function_web_args varchar2(32000);
188 l_function_web_call varchar2(32000);
189 l_plsql_agent_URL varchar2(32000);
190 l_Report_Fn_URL varchar2(32000);
191 l_report_cache_url varchar2(32000);
192 l_img_html varchar2(32000);
193 l_profile_defined boolean;
194 l_record BIS_USER_TREND_PLUGS%ROWTYPE;
195
196 tmp_parm varchar2(240);
197
198 begin
199
200 l_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
201
202 --- Get saved data from cookie
203 BIS_GRAPH_REGION_UI.def_mode_get(l_session_id, l_record);
204
205 l_plug_id := icx_call.decrypt2(l_record.plug_id,l_session_id);
206
207 if icx_sec.validatePlugSession(l_plug_id, l_session_id)
208 then
209
210 l_user_id := icx_call.decrypt2(l_record.user_id,l_session_id);
211
212 l_function_id:=
213 icx_call.decrypt2(l_record.function_id,l_session_id);
214
215 l_page_title := icx_plug_utilities.getPlugTitle(l_plug_id);
216
217 fnd_message.set_name('BIS','BIS_BACK');
218 l_back_btn_txt := fnd_message.get;
219
220 fnd_message.set_name('BIS','BIS_SUBMIT');
221 l_submit_btn_txt := fnd_message.get;
222
223 fnd_message.set_name('BIS','BIS_CANCEL');
224 l_cancel_btn_txt := fnd_message.get;
225
226 fnd_message.set_name('BIS','BIS_CHART_TITLE');
227 l_chart_title_prompt := fnd_message.get;
228
229
230 SELECT
231 web_html_call
232 , parameters
233 INTO
234 l_function_web_call
235 , l_function_parameters
236 FROM fnd_form_functions
237 WHERE function_id = l_function_id;
238
239 fnd_profile.get_specific
240 (
241 name_z => 'ICX_REPORT_LINK',
242 user_id_z => l_user_id,
243 responsibility_id_z => l_record.responsibility_id,
244 application_id_z => l_application_id,
245 val_z => l_plsql_agent_URL,
246 defined_z => l_profile_defined
247 );
248
249 ---Parameters all obtained
250 -------------------------------------------------------------------
251
252
253
254 if (lower(l_function_web_call) = 'oracleoasis.runreport')
255 then
256
257 ----- CASE 1 : Direct RunReprot call
258
259 l_Report_Fn_URL := l_plsql_agent_URL
260 ||'OracleOASIS.RunReport?'
261 || 'session_id='
262 || l_session_id||'&'
263 || 'user_id='
264 || l_user_id||'&'
265 || 'responsibility_application_id='
266 ||l_application_id||'&'
267 || 'responsibility_id='
268 ||l_record.responsibility_id
269 ||'&'
270 || replace(l_function_parameters
271 ,'PARAMFORM=HTML'
272 ,'PARAMFORM=NO')
273 ||'*'
274 || replace(p_parameter_string,'=','~')||'*]'
275 ;
276
277
278 ---- Run the report and obtain the file name containing the graphic.
279
280 bis_trend_plug.get_graph_from_URL('G',l_report_Fn_URL, l_img_html);
281
282
283
284 else
285
286 if instr(l_function_web_call,'(') > 0
287 then
288 l_function_web_args := substr(l_function_web_call
289 , instr(l_function_web_call,'(') + 1
290 , instr(l_function_web_call,')') - 1
291 );
292
293 l_function_web_call := substr(l_function_web_call
294 , 1
295 , instr(l_function_web_call,'(') - 1
296 );
297
298
299 end if;
300
301 if (lower(l_function_web_call) = 'bisviewer.showreport')
302
303 ----- CASE 2 : BIS Report Generator
304
305 then
306
307
308 --- WFN Debug
309 --- for report generator this logic won't work
310
311 null;
312
313
314
315
316 --- END OF CASE 2 - BIS Report Generator
317
318 else
319 ----- CASE 3 : Custom Package
320
321 ---- Normally, the report name is the string after the last
322 ---- underscore character in the function name
323 ---- However, for HR reprots, the report name is in the arguments.
324
325
326 IF substr(l_function_web_call,1,3)='HRI' THEN
327 l_report_name := substr(l_function_web_args ,instr(l_function_web_args,'''',1)+1,8);
328 ELSE
329 l_report_name := substr( l_function_web_call, instr(l_function_web_call,'_',-1)+1, length(l_function_web_call) );
330 END IF;
331
332
333 l_Report_Fn_URL := l_plsql_agent_URL
334 ||'OracleOASIS.RunReport?report='
335 || l_report_name
336 ||'&'
337 || 'session_id='||icx_sec.getID(icx_sec.PV_SESSION_ID)||'&'
338 || 'user_id='||l_user_id||'&'
339 || 'responsibility_application_id='||l_application_id||'&'
340 || 'responsibility_id='
341 ||l_record.responsibility_id
342 ||'&'
343 || 'PARAMETERS='|| replace(p_parameter_string,'=','~')||'*]';
344
345
346
347 ---- Run the report and obtain the file name containing the graphic.
348
349
350 bis_trend_plug.get_graph_from_URL('G',l_report_Fn_URL, l_img_html);
351
352
353
354 --- END OF CASE 3 - Custom Package
355 end if;
356 end if;
357
358 htp.htmlOpen;
359 htp.headOpen;
360 htp.title(l_page_title);
361 BIS_UTILITIES_PVT.putstyle;
362 htp.linkRel( crel => 'stylesheet', curl => g_CSSDirectory || 'bismarli.css');
363 BIS_HTML_UTILITIES_PVT.Build_HTML_Banner(l_page_title,G_HELP_TARGET_NAME);
364 htp.headClose;
365
366 htp.p('<body onLoad="
367 document.cookie='''
368 ||
369 BIS_GRAPH_REGION_UI.g_cookie_name
370 ||
371 '=nonsense ; domain='
372 || BIS_GRAPH_REGION_UI.g_domain
373 ||';'';
374 ">');
375
376 g_graph_title := l_record.chart_user_title;
377
378 htp.centerOpen;
382 htp.formHidden( 'p_where'
379 htp.p('<table width=95%>'); --- Main
380 htp.formOpen('BIS_GRAPH_REGION_UI.REVIEW_CHART_Action','POST','','','NAME="saveForm"');
381
383 ,icx_call.encrypt2(bis_report_util_pvt.get_home_URL)
384 );
385 htp.formHidden( 'p_plug_id'
386 ,l_record.plug_id
387 );
388 htp.formHidden( 'p_user_id'
389 ,l_record.user_id
390 );
391 htp.formHidden('p_function_id'
392 ,l_record.function_id
393 );
394 htp.formHidden('p_responsibility_id'
395 ,l_record.responsibility_id
396 );
397 htp.formHidden('p_chart_user_title'
398 ,l_record.chart_user_title
399 );
400 htp.formHidden('p_parameter_string'
401 ,p_parameter_string
402 );
403 htp.p('<tr><td colspan=2><table border=0 cellpadding=0 width=100%>');
404 --- Instructions
405 fnd_message.set_name('BIS','BIS_TREND_PLUG_CZ3_SUMMARY');
406 htp.tablerowopen(cvalign => 'bottom');
407 htp.tabledata(htf.bold(fnd_message.get));
408 htp.tablerowclose;
409 htp.p('<tr height = 2 bgcolor=#666666><td><img src="/OA_MEDIA/BISPX666.gif"></td></tr>');
410 htp.tablerowopen(cvalign => 'top');
411 fnd_message.set_name('BIS','BIS_TREND_PLUG_CZ3_INSTR');
412 fnd_message.set_token('BACK','"'||l_back_btn_txt||'"');
413 htp.tabledata(fnd_message.get);
414 htp.tablerowclose;
415
416 htp.p('</table></td></tr>'); --instructions
417
418 htp.p('<tr><td align=left width=3%> </td>');
419 htp.p('<td align=left>');
420 htp.p('<table border=0 cellpadding=0 width=100%>'); -- Report Name
421 htp.tablerowopen(cvalign => 'bottom');
422 htp.tabledata(htf.bold(l_chart_title_prompt
423 ||': '
424 ||l_record.chart_user_title
425 )
426 );
427
428 htp.tablerowclose;
429 htp.p('<tr height = 1 bgcolor=#666666><td>'
430 ||'<img src="/OA_MEDIA/BISPX666.gif">'
431 ||'</td></tr>');
432 htp.p('</table></td></tr>'); -- Report Name
433
434 if ( l_img_html IS NOT NULL)
435 THEN
436 IF substr(l_img_html,1,4) <>'http'
437 THEN
438 fnd_profile.get_specific
439 (
440 name_z => 'ICX_REPORT_CACHE',
441 user_id_z => l_user_id,
442 responsibility_id_z => l_responsibility_id,
443 application_id_z => l_application_id,
444 val_z => l_Report_Cache_URL,
445 defined_z => l_profile_defined);
446 if l_profile_defined
447 THEN l_img_html := l_report_cache_url || l_img_html;
448 END IF;
449 END IF;
450
451 ---Ganesh this is where the graphs image is printed
452
453 htp.p('<tr><td> </td> <td align=center>'
454 || ' <img src='
455 ||l_img_html
456 ||'>'
457 );
458 ELSE --- null l_img_html
459 htp.p('<! Review_Chart_Render: REPORT URL IS '|| l_report_fn_url);
460 END IF;
461
462
463
464 htp.p('<tr><td> </td> <td align=right>');
465 htp.p('<table> <tr><td align=center>'); ---buttons
466 icx_plug_utilities.buttonBoth(l_cancel_btn_txt
467 ,'Javascript:history.go(-3)'
468 ,'');
469 htp.p('</td><td colspan="2" nowrap=1>');
470 htp.p('<table width="100%">'); -- inner buttons
471 htp.p('<tr><td width="50%" align="right">');
472 icx_plug_utilities.buttonLeft(l_back_btn_txt
473 ,'Javascript:history.go(-1)'
474 ,'');
475 htp.p('</td><td align="left" width="50%">');
476
477 icx_plug_utilities.buttonBoth(l_Submit_btn_txt
478 ,'Javascript:document.saveForm.submit()'
479 ,'');
480 htp.p('</td></tr></table>'); -- inner buttons
481 htp.p('</td></tr></table>'); -- all buttons
482
483 htp.p('</td></tr></table>'); -- Main
484 htp.centerClose;
485 htp.bodyClose;
486 end if;
487
488 exception
489 when others then
490 htp.p('<! Review_Chart_Render:EXCEPTION
491 '
492 ||SQLERRM
493 ||'
494 >'
495 );
496 end Review_Chart_Render;
497
498 function get_graph_title return varchar2 is
499 tmp_g_title varchar2(200);
500 begin
501 tmp_g_title := g_graph_title;
502 return tmp_g_title;
503 end;
504
505 -- mdamle 05/29/2001 - Added function to check if user has access to this function
506 -- mdamle 07/03/2001 - Added pCheckPMVSpecific flag
507 function hasFunctionAccess(pUserId in varchar2
508 , pFunctionName in varchar2
509 , pCheckPMVSpecific in varchar2 default 'Y') return boolean IS
510
511 cursor p_resps is
512 -- 11/12/01 mdamle - Use FND security function to check function access
513 -- Get menu_id
514 select a.menu_id
515 from fnd_responsibility a,
516 fnd_user_resp_groups b
517 where b.user_id = pUserId
518 and a.version = 'W'
519 and b.responsibility_id = a.responsibility_id
520 and b.start_date <= sysdate
521 and (b.end_date is null or b.end_date >= sysdate)
522 and a.start_date <= sysdate
523 and (a.end_date is null or a.end_date >= sysdate);
524
525 l_function_table t_func_tbl_type;
526 l_default_resp_Id number;
527 l_foundResp boolean;
528 p_Resps_rec p_Resps%ROWTYPE;
529 l_function_id number;
530 l_web_html_call fnd_form_functions.web_html_call%TYPE;
531
532 begin
533 begin
534 select function_id, web_html_call
535 into l_function_id, l_web_html_call
536 from fnd_form_functions
537 where function_name = pFunctionName;
538 exception
539 when others then l_function_id := null;
540 end;
541
542 if l_function_id is null then
543 l_foundResp := false;
544 else
545 -- First, get a list of all valid responsibilities for this user
546 l_foundResp := false;
547 open p_Resps;
548 <<resp_loop>>
549 loop
550 fetch p_Resps into p_Resps_rec;
551 EXIT WHEN p_Resps%NOTFOUND;
552
553 l_foundResp := fnd_function.is_function_on_menu(p_Resps_rec.menu_id, l_function_id);
554 if l_foundResp = true then
555 if (has_good_report(l_web_html_call) = false) and (pCheckPMVSpecific = 'Y') then
556 l_foundResp := false;
557 end if;
558
559 exit resp_loop;
560 end if;
561 end loop;
562 close p_Resps;
563 end if;
564
565 return l_foundResp;
566
567 end hasFunctionAccess;
568
569
570 END BIS_GRAPH_REGION_HTML_FORMS;