[Home] [Help]
PACKAGE BODY: APPS.BIS_RG_SCHEDULES_PVT
Source
1 PACKAGE BODY BIS_RG_SCHEDULES_PVT as
2 /* $Header: BISVSCHB.pls 120.2.12000000.3 2007/02/06 07:47:47 akoduri ship $ */
3 -- dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
4 -- dbdrv: checkfile(115.62=120.2.12000000.3):~PROD:~PATH:~FILE
5 ----------------------------------------------------------------------------
6 -- PACKAGE: BIS_SCHEDULE_PVT
7 -- --
8 -- DESCRIPTION: Private package to create records in BIS_SCHEDULER
9 --
10 -- --
11 -- MODIFICATIONS --
12 -- Date User Modification
13 -- XX-XXX-XX XXXXXXXX Modifications made, which procedures changed & --
14 -- list bug number, if fixing a bug. --
15 -- --
16 -- 02-25-00 amkulkar Initial creation --
17 -- 5/12/01 aleung need to duplicate records(but schedule id is null)
18 -- for drilldown from table porlet to work --
19 -- 07/03/01 mdamle Scheduling Enhancements --
20 -- 08/01/01 mdamle Check for expired default schedule Bug# 1925970 --
21 -- 08/20/01 mdamle Scheduling Enhancements - Phase II - unsubscribe--
22 -- 09/04/01 mdamle Scheduling Enhancements - Phase II - purge --
23 -- 09/04/01 mdamle Scheduling Enhancements - Phase II - Multiple --
24 -- Preferences per schedule --
25 -- 09/13/01 mdamle Fixed Bug#1994876 --
26 -- 09/19/01 mdamle Remove background purge of email data --
27 -- 09/19/01 mdamle Trap File_ID creation error --
28 -- 09/21/01 mdamle Fixed Bug#1999207 --
29 -- 09/21/01 mdamle Fixed Bug#1999262 --
30 -- 10/25/01 mdamle Update Title in ICX_PORTLET_CUSTOMIZATIONS --
31 -- 12/04/01 mdamle Title in ICX_PORTLET_CUSTOMIZATIONS is link --
32 -- 12/12/01 mdamle Changes for Live Portlet --
33 -- 01/03/02 mdamle Added plug_id to TL --
34 -- 01/16/02 mdamle External Source Id changes --
35 -- 01/25/02 mdamle Fix profile option bug for getUserType --
36 -- 11/29/02 nkishore Added creating_schedule, updating_schedule --
37 -- 03/12/03 rcmuthuk Bug Fix:2807197 - added regionCode and functionName params to redirect URL --
38 -- 04/25/03 rcmuthuk Bug Fix:2799113 - Changed order of parmPrint param --
39 -- 06/05/03 nkishore BugFix 2972706 -- Encode Report Title --
40 -- 09/08/03 nkishore BugFix 3127079 -- Call FND_MSG.COUNT_AND_GET --
41 -- 10/10/03 ksadagop Bug Fix:3182441 -- Encoded header and reportTitle --
42 -- 01/19/04 nkishore Save Report to PDF --
43 -- 01/25/07 akoduri Bug#5752469 Issue with Cancel & Apply buttons d--
44 -- in portal --
45 -- 02/06/07 akoduri GSCC Error while building R12 ARU --
46 ----------------------------------------------------------------------------
47
48 -- mdamle 07/03/01 - Scheduling Enhancements
49 gvRoleName varchar2(20) := 'BIS_SCHEDULE_';
50
51 -- copied from bisviewer_pmf for bug 5031067
52 procedure scheduleReports(
53 pRegionCode in varchar2,
54 pFunctionName in varchar2,
55 pUserId in varchar2,
56 pSessionId in varchar2,
57 pResponsibilityId in varchar2,
58 pReportTitle in varchar2 default NULL,
59 pApplicationId in varchar2 default NULL,
60 pParmPrint in varchar2 default NULL,
61 pRequestType in varchar2 default 'R',
62 pPlugId in varchar2 default NULL,
63 pGraphType in varchar2 default NULL
64 )
65 is
66 vScheduleURL varchar2(5000);
67 --l_customize_URL varchar2(32000);
68 l_customize_id pls_integer;
69 l_fn_Responsibility_id number;
70 l_application_id number;
71 l_user_id number;
72 l_rowid varchar2(1000);
73
74 l_form_func_name varchar2(1000) := 'BIS_SCHEDULE_PAGE';
75 l_form_func_call varchar2(1000) := 'bissched.jsp';
76
77 vParams varchar2(2000);
78
79 CURSOR cFndResp (pRespId in varchar2) is
80 select application_id
81 from fnd_responsibility
82 where responsibility_id=pRespId;
83
84 begin
85 l_user_id := pUserId;
86
87 l_fn_responsibility_id := nvl(pResponsibilityId, icx_sec.getid(ICX_SEC.PV_RESPONSIBILITY_ID));
88 if pApplicationId is null then
89 if cFNDResp%ISOPEN then
90 CLOSE cFNDResp;
91 end if;
92 OPEN cFNDResp(l_fn_responsibility_id);
93 FETCH cFNDResp INTO l_application_id;
94 CLOSE cFNDResp;
95 else
96 l_application_id := pApplicationId;
97 end if;
98
99 /*
100 begin
101 select application_id into l_application_id
102 from fnd_responsibility
103 where responsibility_id=l_fn_responsibility_id;
104 end;
105
106 if pRequestType <> 'R' then
107 l_form_func_name := 'BIS_SCHEDULE_CONFIRM_PAGE';
108 l_form_func_call := 'bisschcf.jsp';
109 end if;
110 */
111
112 begin
113 select function_id
114 into l_customize_id
115 from fnd_form_functions
116 where function_name = l_form_func_name;
117 exception
118 when no_data_found then
119 l_customize_id := null;
120 end;
121
122 if l_customize_id is null then
123 begin
124 select FND_FORM_FUNCTIONS_S.NEXTVAL into l_customize_id from dual;
125
126 --aleung, 5/14/01, for gsi1av envrionment, their fnd_form_functions_pkg.insert_row has more parameters
127
128 fnd_form_functions_pkg.INSERT_ROW(
129 X_ROWID => l_rowid,
130 X_FUNCTION_ID => l_customize_id,
131 X_WEB_HOST_NAME => null,
132 X_WEB_AGENT_NAME => null,
133 X_WEB_HTML_CALL => l_form_func_call,
134 X_WEB_ENCRYPT_PARAMETERS => null,
135 X_WEB_SECURED => null,
136 X_WEB_ICON => null,
137 X_OBJECT_ID => null,
138 X_REGION_APPLICATION_ID => null,
139 X_REGION_CODE => null,
140 X_FUNCTION_NAME => l_form_func_name,
141 X_APPLICATION_ID => l_application_id,
142 X_FORM_ID => null,
143 X_PARAMETERS => null,
144 X_TYPE => 'JSP',
145 X_USER_FUNCTION_NAME => 'BIS SCHEDULE',
146 X_DESCRIPTION => null,
147 X_CREATION_DATE => sysdate,
148 X_CREATED_BY => l_user_id,
149 X_LAST_UPDATE_DATE => sysdate,
150 X_LAST_UPDATED_BY => l_user_id,
151 X_LAST_UPDATE_LOGIN => l_user_id);
152
153 /*
154 fnd_form_functions_pkg.insert_row (l_rowid,
155 l_customize_id, null,null,
156 l_form_func_call,
157 null,null,null,l_form_func_name,
158 l_application_id,null,null,'JSP','BIS SCHEDULE',
159 null,sysdate,l_user_id,sysdate,l_user_id,l_user_id);
160 */
161
162 exception
163 when others then
164 null;
165 end;
166 end if;
167
168 /*
169 vScheduleURL := 'OracleApps.RF?F='||icx_call.encrypt2(l_application_id||'*'||l_fn_responsibility_id||'*'||icx_sec.g_security_group_id||'*'||l_customize_id||'**]',
170 icx_sec.getID(icx_sec.PV_SESSION_ID))
171 ||'&P='||icx_call.encrypt2('regionCode='||bis_pmv_util.encode(pRegionCode)
172 ||'&functionName='||bis_pmv_util.encode(pFunctionName)
173 ||'&parmPrint='||bis_pmv_util.encode(pParmPrint)
174 ||'&requestType='||pRequestType
175 ||'&plugId='||pPlugId
176 ||'&reportTitle='||bis_pmv_util.encode(pReportTitle)
177 ||'&graphType='||pGraphType,icx_sec.getID(icx_sec.PV_SESSION_ID));
178 */
179
180 /*fnd_profile.get(name=>'APPS_SERVLET_AGENT',
181 val => vScheduleURL);
182 vScheduleURL := FND_WEB_CONFIG.trail_slash(vScheduleURL)||
183 'bissched.jsp?dbc=' || FND_WEB_CONFIG.DATABASE_ID
184 ||'&sessionid='||icx_call.encrypt3(icx_sec.getID(icx_sec.PV_SESSION_ID))
185 ||'&responsibilityId='||pResponsibilityId
186 ||'®ionCode='||bis_pmv_util.encode(pRegionCode)
187 ||'&functionName='||bis_pmv_util.encode(pFunctionName)
188 ||'&parmPrint='||bis_pmv_util.encode(pParmPrint)
189 ||'&requestType='||pRequestType
190 ||'&plugId='||pPlugId
191 ||'&graphType='||pGraphType;*/
192
193 -- owa_util.redirect_url(vScheduleURL);
194
195 -- mdamle 11/01/2002 - Added encode
196 vParams := 'regionCode='|| pRegionCode
197 ||'&functionName='||pFunctionName
198 ||'&parmPrint='||bis_pmv_util.encode(pParmPrint)
199 ||'&requestType='||pRequestType
200 ||'&plugId='||pPlugId
201 ||'&reportTitle='||pReportTitle
202 ||'&graphType='||pGraphType;
203
204 OracleApps.runFunction(c_function_id => l_customize_id
205 ,n_session_id => icx_sec.getID(icx_sec.PV_SESSION_ID)
206 ,c_parameters => vParams
207 ,p_resp_appl_id => l_application_id
208 ,p_responsibility_id => l_fn_responsibility_id
209 ,p_Security_group_id => icx_sec.g_Security_group_id
210 );
211
212 end scheduleReports;
213 --jprabhud 09/24/02 - Enh 2470068 Storing of Graphs to FND_LOBS
214 procedure deleteReportGraphsLobs(p_user_id in varchar2
215 ,p_schedule_id in varchar2
216 ,p_function_name in varchar2);
217
218 PROCEDURE CREATE_SCHEDULE
219 (p_plug_id IN NUMBER DEFAULT NULL
220 ,p_user_id IN VARCHAR2
221 ,p_function_name IN VARCHAR2
222 ,p_responsibility_id IN VARCHAR2
223 ,p_title IN VARCHAR2 DEFAULT NULL
224 ,p_graph_type IN NUMBER DEFAULT NULL
225 ,p_concurrent_request_id IN NUMBER DEFAULT NULL
226 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
227 ,p_file_id IN OUT NOCOPY NUMBER
228 ,p_request_type IN VARCHAR2 DEFAULT NULL
229 ,x_schedule_id OUT NOCOPY NUMBER
230 ,x_return_Status OUT NOCOPY VARCHAR2
231 ,x_msg_Data OUT NOCOPY VARCHAR2
232 ,x_msg_count OUT NOCOPY NUMBER
233 -- mdamle 12/12/01 - Changes for Live Portlet
234 ,p_live_portlet IN VARCHAR2 DEFAULT 'N'
235 --jprabhud - 12/20/02 - NLS Bug 2320171 Graph Fonts and Mutli-Byte characters
236 ,p_context_values IN VARCHAR2 DEFAULT NULL
237 )
238 IS
239 l_schedule_id NUMBER;
240 l_request_Type VARCHAR2(1) := 'G';
241 BEGIN
242
243
244 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
245 -- Added the plug_id condition
246 IF p_plug_id is null then
247 l_request_type := 'R';
248 else
249 l_request_type := p_request_type;
250 END IF;
251
252 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Purge Portlet Data
253 /*
254 if p_plug_id is not null then
255 delete_portlet(p_plug_id, p_user_id);
256 end if;
257 */
258
259 SELECT bis_scheduler_s.nextval INTO l_schedule_id FROM dual;
260 INSERT INTO BIS_SCHEDULER
261 (schedule_id
262 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
263 --,plug_id
264 ,user_id
265 ,function_name
266 ,responsibility_id
267 -- ,title
268 -- ,graph_type
269 ,concurrent_request_id
270 -- ,file_id
271 ,creation_Date
272 ,last_update_date
273 ,created_By
274 ,last_update_login
275 )
276 VALUES
277 (l_schedule_id
278 -- ,p_plug_id
279 ,p_user_id
280 ,p_function_name
281 ,p_responsibility_id
282 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
283 -- ,p_title
284 -- ,p_graph_type
285 ,p_concurrent_request_id
286 -- ,get_file_id(l_Request_Type)
287 ,SYSDATE
288 ,SYSDATE
289 ,0
290 ,0
291 );
292
293 -- mdamle 09/04/01, 12/12/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
294 -- jprabhud 09/24/02 - Enh. 2470068 DB Graph HTML - Reusing file Ids to store graphs - passed in function name
295 --jprabhud - 12/20/02 - NLS Bug 2320171 Graph Fonts and Mutli-Byte characters - passed in p_context_values
296 create_schedule_preferences(l_schedule_id, p_user_id, p_plug_id, p_title, p_graph_type, l_request_type, p_file_id, p_live_portlet,p_function_name,p_context_values);
297
298 commit;
299 x_schedule_id := l_schedule_id;
300 x_return_status := FND_API.G_RET_STS_SUCCESS;
301 /* BugFix 3127079
302 FND_MSG_PUB.COUNT_AND_GET
303 (p_count => x_msg_count
304 ,p_data => x_msg_Data
305 );*/
306
307 EXCEPTION
308 WHEN OTHERS THEN
309 --WHEN FND_API.G_EXC_ERROR
310 x_return_status := FND_API.G_RET_STS_ERROR;
311 FND_MSG_PUB.COUNT_AND_GET
312 (p_count => x_msg_count
313 ,p_data => x_msg_data
314 );
315 END;
316
317 /* serao, 06/03, This creates a schedule for the create schedule call with the same signature but does not do a commit.
318 Called by Related Links Functionality
319 */
320 PROCEDURE CREATE_SCHEDULE_NO_COMMIT
321 (p_plug_id IN NUMBER DEFAULT NULL
322 ,p_user_id IN VARCHAR2
323 ,p_function_name IN VARCHAR2
324 ,p_responsibility_id IN VARCHAR2
325 ,p_title IN VARCHAR2 DEFAULT NULL
326 ,p_graph_type IN NUMBER DEFAULT NULL
327 ,p_request_type IN VARCHAR2 DEFAULT NULL
328 ,x_schedule_id OUT NOCOPY NUMBER
329 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
330 ,x_file_id OUT NOCOPY NUMBER
331 ,x_return_Status OUT NOCOPY VARCHAR2
332 ,x_msg_Data OUT NOCOPY VARCHAR2
333 ,x_msg_count OUT NOCOPY NUMBER
334 -- mdamle 12/12/01 - Changes for Live Portlet
335 ,p_live_portlet IN VARCHAR2 DEFAULT 'N'
336 )
337 IS
338 l_schedule_id NUMBER;
339 l_Request_Type VARCHAR2(1);
340 BEGIN
341
342 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
343 -- Added the plug_id condition
344 IF p_plug_id is null then
345 l_request_type := 'R';
346 else
347 l_request_type := p_request_type;
348 END IF;
349
350 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Purge Portlet Data
351 /*
352 if p_plug_id is not null then
353 delete_portlet(p_plug_id, p_user_id);
354 end if;
355 */
356 SELECT bis_scheduler_s.nextval INTO l_schedule_id FROM dual;
357 INSERT INTO BIS_SCHEDULER
358 (schedule_id
359 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
360 -- ,plug_id
361 ,user_id
362 ,function_name
363 ,responsibility_id
364 -- ,title
368 ,creation_Date
365 -- ,graph_type
366 ,concurrent_request_id
367 -- ,file_id
369 ,last_update_date
370 ,created_By
371 ,last_update_login
372 )
373 VALUES
374 (l_schedule_id
375 -- ,p_plug_id
376 ,p_user_id
377 ,p_function_name
378 ,p_responsibility_id
379 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
380 -- ,p_title
381 -- ,p_graph_type
382 ,NULL--current_request_id
383 -- ,get_File_id(l_request_Type)
384 ,SYSDATE
385 ,SYSDATE
386 ,0
387 ,0
388 );
389
390
391 -- mdamle 09/04/01, 12/12/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
392 create_schedule_preferences(l_schedule_id, p_user_id, p_plug_id, p_title, p_graph_type, l_request_type, x_file_id, p_live_portlet);
393
394 x_schedule_id := l_schedule_id;
395 END CREATE_SCHEDULE_NO_COMMIT;
396
397 PROCEDURE CREATE_SCHEDULE
398 (p_plug_id IN NUMBER DEFAULT NULL
399 ,p_user_id IN VARCHAR2
400 ,p_function_name IN VARCHAR2
401 ,p_responsibility_id IN VARCHAR2
402 ,p_title IN VARCHAR2 DEFAULT NULL
403 ,p_graph_type IN NUMBER DEFAULT NULL
404 ,p_request_type IN VARCHAR2 DEFAULT NULL
405 ,x_schedule_id OUT NOCOPY NUMBER
406 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
407 ,x_file_id OUT NOCOPY NUMBER
408 ,x_return_Status OUT NOCOPY VARCHAR2
409 ,x_msg_Data OUT NOCOPY VARCHAR2
410 ,x_msg_count OUT NOCOPY NUMBER
411 -- mdamle 12/12/01 - Changes for Live Portlet
412 ,p_live_portlet IN VARCHAR2 DEFAULT 'N'
413 ) IS
414
415 BEGIN
416
417 CREATE_SCHEDULE_NO_COMMIT(
418 p_plug_id
419 ,p_user_id
420 ,p_function_name
421 ,p_responsibility_id
422 ,p_title
423 ,p_graph_type
424 ,p_request_type
425 ,x_schedule_id
426 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
427 ,x_file_id
428 ,x_return_Status
429 ,x_msg_Data
430 ,x_msg_count
431 -- mdamle 12/12/01 - Changes for Live Portlet
432 ,p_live_portlet
433 );
434 COMMIT;
435
436 END CREATE_SCHEDULE;
437
438 PROCEDURE CREATE_SCHEDULE
439 (p_plug_id IN NUMBER DEFAULT NULL
440 ,p_user_id IN VARCHAR2
441 ,p_function_name IN VARCHAR2
442 ,p_responsibility_id IN VARCHAR2
443 ,p_title IN VARCHAR2 DEFAULT NULL
444 ,p_graph_type IN NUMBER DEFAULT NULL
445 ,p_concurrent_request_id IN NUMBER DEFAULT NULL
446 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
447 ,p_file_id IN OUT NOCOPY NUMBER
448 ,p_session_id IN VARCHAR2 DEFAULT NULL
449 ,p_report_region_code IN VARCHAR2 DEFAULT NULL
450 ,p_request_type IN VARCHAR2 DEFAULT NULL
451 ,x_Schedule_id OUT NOCOPY NUMBER
452 ,x_return_Status OUT NOCOPY VARCHAR2
453 ,x_msg_Data OUT NOCOPY VARCHAR2
454 ,x_msg_count OUT NOCOPY NUMBER
455 -- mdamle 12/12/01 - Changes for Live Portlet
456 ,p_live_portlet IN VARCHAR2 DEFAULT 'N'
457 --jprabhud - 12/20/02 - NLS Bug 2320171 Graph Fonts and Mutli-Byte characters
458 ,p_context_values IN VARCHAR2 DEFAULT NULL
459 )
460 IS
461 l_schedule_id NUMBER;
462 BEGIN
463
464 BIS_RG_SCHEDULES_PVT.CREATE_SCHEDULE
465 (p_plug_id
466 ,p_user_id
467 ,p_function_name
468 ,p_responsibility_id
469 ,p_title
470 ,p_graph_Type
471 ,p_concurrent_request_id
472 ,p_file_id
473 ,p_request_type
474 ,l_schedule_id
475 ,x_return_status
476 ,x_msg_Data
477 ,x_msg_count
478 -- mdamle 12/12/01 - Changes for Live Portlet
479 ,p_live_portlet
480 --jprabhud - 12/20/02 - NLS Bug 2320171 Graph Fonts and Mutli-Byte characters
481 ,p_context_values
482 );
483 x_Schedule_id := l_schedule_id;
484 BEGIN
485 UPDATE BIS_USER_ATTRIBUTES
486 SET schedule_id = l_schedule_id
487 WHERE session_id=p_Session_id AND
488 function_name = p_function_name AND
489 user_id = p_user_id AND
490 schedule_id is null;
491
492 /* -- aleung, 10/19/01, no need to do this, should retrieve data according to schedule id
493 -- aleung, 5/12/01, need to duplicate records(but schedule id is null)
494 -- for drilldown from table porlet to work
495 insert into bis_user_attributes (USER_ID,
496 FUNCTION_NAME,
497 SESSION_ID,
498 SESSION_VALUE,
499 SESSION_DESCRIPTION,
500 DEFAULT_VALUE,
501 DEFAULT_DESCRIPTION,
502 ATTRIBUTE_NAME,
503 DIMENSION,
504 PERIOD_DATE)
505 select USER_ID,
506 FUNCTION_NAME,
507 SESSION_ID,
511 DEFAULT_DESCRIPTION,
508 SESSION_VALUE,
509 SESSION_DESCRIPTION,
510 DEFAULT_VALUE,
512 ATTRIBUTE_NAME,
513 DIMENSION,
514 PERIOD_DATE
515 from bis_user_attributes
516 where schedule_id = l_schedule_id;
517 */
518
519 END;
520 COMMIT;
521 EXCEPTION
522 WHEN OTHERS THEN
523 NULL;
524 END;
525
526 --Customize UI Enhancement
527 PROCEDURE CREATING_SCHEDULE
528 (p_plug_id IN NUMBER DEFAULT NULL
529 ,p_user_id IN VARCHAR2
530 ,p_function_name IN VARCHAR2
531 ,p_responsibility_id IN VARCHAR2
532 ,p_title IN VARCHAR2 DEFAULT NULL
533 ,p_graph_type IN NUMBER DEFAULT NULL
534 ,p_concurrent_request_id IN NUMBER DEFAULT NULL
535 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
536 ,p_file_id IN OUT NOCOPY NUMBER
537 ,p_session_id IN VARCHAR2 DEFAULT NULL
538 ,p_report_region_code IN VARCHAR2 DEFAULT NULL
539 ,p_request_type IN VARCHAR2 DEFAULT NULL
540 ,x_Schedule_id OUT NOCOPY NUMBER
541 ,x_return_Status OUT NOCOPY VARCHAR2
542 ,x_msg_Data OUT NOCOPY VARCHAR2
543 ,x_msg_count OUT NOCOPY NUMBER
544 --jprabhud - 12/20/02 - NLS Bug 2320171 Graph Fonts and Mutli-Byte characters
545 ,p_context_values IN VARCHAR2 DEFAULT NULL
546 )
547 IS
548 l_schedule_id NUMBER;
549 l_request_Type VARCHAR2(1) := 'G';
550 BEGIN
551
552 IF p_plug_id is null then
553 l_request_type := 'R';
554 else
555 l_request_type := p_request_type;
556 END IF;
557 SELECT bis_scheduler_s.nextval INTO l_schedule_id FROM dual;
558 INSERT INTO BIS_SCHEDULER
559 (schedule_id
560 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
561 --,plug_id
562 ,user_id
563 ,function_name
564 ,responsibility_id
565 -- ,title
566 -- ,graph_type
567 ,concurrent_request_id
568 -- ,file_id
569 ,creation_Date
570 ,last_update_date
571 ,created_By
572 ,last_update_login
573 )
574 VALUES
575 (l_schedule_id
576 -- ,p_plug_id
577 ,p_user_id
578 ,p_function_name
579 ,p_responsibility_id
580 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
581 -- ,p_title
582 -- ,p_graph_type
583 ,p_concurrent_request_id
584 -- ,get_file_id(l_Request_Type)
585 ,SYSDATE
586 ,SYSDATE
587 ,0
588 ,0
589 );
590
591 -- mdamle 09/04/01, 12/12/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
592 --jprabhud - 12/20/02 - NLS Bug 2320171 Graph Fonts and Mutli-Byte characters - passed in p_context_values
593 create_schedule_preferences(l_schedule_id, p_user_id, p_plug_id, p_title, p_graph_type, l_request_type, p_file_id, 'N', p_function_name,p_context_values);
594 commit;
595
596 x_Schedule_id := l_schedule_id;
597 BEGIN
598 insert into bis_user_attributes (USER_ID,
599 FUNCTION_NAME,
600 SESSION_ID,
601 SESSION_VALUE,
602 SESSION_DESCRIPTION,
603 DEFAULT_VALUE,
604 DEFAULT_DESCRIPTION,
605 ATTRIBUTE_NAME,
606 DIMENSION,
607 PERIOD_DATE,
608 SCHEDULE_ID)
609 select USER_ID,
610 FUNCTION_NAME,
611 SESSION_ID,
612 SESSION_VALUE,
613 SESSION_DESCRIPTION,
614 DEFAULT_VALUE,
615 DEFAULT_DESCRIPTION,
616 ATTRIBUTE_NAME,
617 DIMENSION,
618 PERIOD_DATE,
619 l_schedule_id
620 from bis_user_attributes
621 WHERE session_id=p_Session_id AND
622 function_name = p_function_name AND
623 user_id = p_user_id AND
624 schedule_id is null;
625 END;
626 x_return_status := FND_API.G_RET_STS_SUCCESS;
627 FND_MSG_PUB.COUNT_AND_GET
628 (p_count => x_msg_count
629 ,p_data => x_msg_Data
630 );
631
632
633 EXCEPTION
634 WHEN OTHERS THEN
635 x_return_status := FND_API.G_RET_STS_ERROR;
636 FND_MSG_PUB.COUNT_AND_GET
637 (p_count => x_msg_count
638 ,p_data => x_msg_data
639 );
640 END;
641
642 PROCEDURE UPDATE_SCHEUDLE
643 (p_schedule_id IN NUMBER
644 ,p_user_id IN VARCHAR2 DEFAULT NULL
645 ,p_function_name IN VARCHAR2 DEFAULT NULL
646 ,p_title IN VARCHAR2 DEFAULT NULL
647 ,p_graph_type IN NUMBER DEFAULT NULL
648 ,p_concurrent_Request_id IN NUMBER DEFAULT NULL
649 ,p_file_id IN NUMBER DEFAULT NULL
650 ,x_return_Status OUT NOCOPY VARCHAR2
654 ,p_parameters IN VARCHAR2 default null
651 ,x_msg_data OUT NOCOPY VARCHAR2
652 ,x_msg_count OUT NOCOPY NUMBER
653 -- mdamle 07/03/01 - Scheduling Enhancements
655 )
656 IS
657 BEGIN
658 UPDATE BIS_SCHEDULER
659 SET user_id = p_user_id
660 ,function_name = p_function_name
661 ,title = p_title
662 ,graph_type = p_graph_type
663 ,concurrent_request_id=p_concurrent_request_id
664 ,file_id = p_file_id
665 ,last_update_date=SYSDATE
666 -- mdamle 07/03/01 - Scheduling Enhancements
667 ,parameter_string = p_parameters
668 WHERE schedule_id=p_schedule_id;
669
670 EXCEPTION
671 WHEN OTHERS THEN
672 NULL; -- Add proper Error Handling
673 END;
674 PROCEDURE UPDATE_SCHEDULE
675 (p_schedule_id IN NUMBER
676 ,p_concurrent_Request_id IN NUMBER
677 ,x_return_Status OUT NOCOPY VARCHAR2
678 ,x_msg_Data OUT NOCOPY VARCHAR2
679 ,x_msg_count OUT NOCOPY NUMBER
680 -- mdamle 07/03/01 - Scheduling Enhancements
681 ,p_parameters IN VARCHAR2 default null
682 ,p_commit IN VARCHAR2 DEFAULT 'Y'
683 )
684 IS
685 BEGIN
686 -- mdamle 07/03/01 - Scheduling Enhancements
687 -- Added parameters field to update
688 UPDATE bis_scheduler
689 SET concurrent_request_id = p_concurrent_request_id
690 , parameter_string = p_parameters
691 WHERE schedule_id = p_schedule_id;
692
693 if (p_commit = 'Y') then
694 COMMIT;
695 end if;
696
697
698 EXCEPTION
699 WHEN OTHERS THEN
700 NULL; -- Add proper error handling
701 END;
702
703 --nkishore Customize UI Enhancement
704 PROCEDURE UPDATING_SCHEDULE
705 (p_schedule_id IN NUMBER
706 ,p_user_id IN VARCHAR2
707 ,p_function_name IN VARCHAR2
708 ,p_session_id IN VARCHAR2
709 ,x_return_Status OUT NOCOPY VARCHAR2
710 ,x_msg_data OUT NOCOPY VARCHAR2
711 ,x_msg_count OUT NOCOPY NUMBER
712 )
713 IS
714 BEGIN
715
716 DELETE FROM bis_user_attributes
717 WHERE schedule_id = p_schedule_id
718 AND function_name = p_function_name;
719
720 UPDATE BIS_USER_ATTRIBUTES
721 SET schedule_id = p_schedule_id
722 WHERE session_id=p_Session_id AND
723 function_name = p_function_name AND
724 user_id = p_user_id AND
725 schedule_id is null;
726
727 x_return_status := FND_API.G_RET_STS_SUCCESS;
728 FND_MSG_PUB.COUNT_AND_GET
729 (p_count => x_msg_count
730 ,p_data => x_msg_Data
731 );
732
733
734 EXCEPTION
735 WHEN OTHERS THEN
736 x_return_status := FND_API.G_RET_STS_ERROR;
737 FND_MSG_PUB.COUNT_AND_GET
738 (p_count => x_msg_count
739 ,p_data => x_msg_data
740 );
741 END;
742
743 FUNCTION GET_FILE_ID
744 (p_request_type IN VARCHAR2)
745 RETURN NUMBER
746 IS
747 l_File_id NUMBER;
748 l_content_type VARCHAR2(32000);
749 BEGIN
750 --CREATE AN EMPTY FILE;
751 --Save Report to PDF add application/pdf
752 IF (p_request_Type = 'G') THEN
753 l_content_type := 'IMAGE/GIF';
754 ELSIF (p_request_Type = 'PDF') THEN
755 l_content_type := 'application/pdf';
756 ELSE
757 l_content_type := 'TEXT/HTML';
758 END IF;
759 l_file_id := BIS_SAVE_REPORT.CREATEENTRY
760 ('BIS_REPORT'
761 ,l_Content_type
762 ,null
763 ,null);
764
765 if l_file_id is null then
766 l_file_id := 0;
767 end if;
768
769 RETURN l_file_id;
770 END;
771 PROCEDURE IS_SCHEDULED
772 (p_user_id IN VARCHAR2
773 ,p_function_name IN VARCHAR2
774 ,p_report_region_code IN VARCHAR2 DEFAULT NULL
775 ,p_session_id IN VARCHAR2 DEFAULT NULL
776 ,p_parameter1 IN VARCHAR2 DEFAULT NULL
777 ,p_parameter2 IN VARCHAR2 DEFAULT NULL
778 ,p_parameter3 IN VARCHAR2 DEFAULT NULL
779 ,p_parameter4 IN VARCHAR2 DEFAULT NULL
780 ,p_parameter5 IN VARCHAR2 DEFAULT NULL
781 ,p_parameter6 IN VARCHAR2 DEFAULT NULL
782 ,p_parameter7 IN VARCHAR2 DEFAULT NULL
783 ,p_parameter8 IN VARCHAR2 DEFAULT NULL
784 ,p_parameter9 IN VARCHAR2 DEFAULT NULL
785 ,p_parameter10 IN VARCHAR2 DEFAULT NULL
786 ,p_parameter11 IN VARCHAR2 DEFAULT NULL
787 ,p_parameter12 IN VARCHAR2 DEFAULT NULL
788 ,p_parameter13 IN VARCHAR2 DEFAULT NULL
789 ,p_parameter14 IN VARCHAR2 DEFAULT NULL
790 ,p_parameter15 IN VARCHAR2 DEFAULT NULL
791 ,p_timetoparameter IN VARCHAR2 DEFAULT NULL
792 ,p_timefromparameter IN VARCHAR2 DEFAULT NULL
793 ,p_viewby IN VARCHAR2 DEFAULT NULL
794 ,x_Scheduled OUT NOCOPY VARCHAR2
795 )
796 IS
797 CURSOR c_attrs IS
798 SELECT attribute_name, session_value
799 FROM bis_user_attributes
800 WHERE user_id=p_user_id AND
801 function_name = p_function_name AND
802 schedule_id IS NOT NULL;
803 l_scheduled varchar2(1);
804
805 BEGIN
806 FOR c_rec IN c_attrs LOOP
810 end if;
807 if (p_parameter1 = c_rec.session_value) then
808 l_scheduled := 'Y';
809 GOTO skip_this;
811 if (p_parameter2 = c_rec.session_value) then
812 l_scheduled := 'Y';
813 GOTO skip_this;
814 end if;
815 if (p_parameter3 = c_rec.session_value) then
816 l_scheduled := 'Y';
817 GOTO skip_this;
818 end if;
819 if (p_parameter4 = c_rec.session_value) then
820 l_scheduled := 'Y';
821 GOTO skip_this;
822 end if;
823 if (p_parameter5 = c_rec.session_value) then
824 l_scheduled := 'Y';
825 GOTO skip_this;
826 end if;
827 if (p_parameter6 = c_rec.session_value) then
828 l_scheduled := 'Y';
829 GOTO skip_this;
830 end if;
831 if (p_parameter7 = c_rec.session_value) then
832 l_scheduled := 'Y';
833 GOTO skip_this;
834 end if;
835 if (p_parameter8 = c_rec.session_value) then
836 l_scheduled := 'Y';
837 GOTO skip_this;
838 end if;
839 if (p_parameter9 = c_rec.session_value) then
840 l_scheduled:= 'Y';
841 GOTO skip_this;
842 end if;
843 if (p_parameter10 = c_rec.session_value) then
844 l_scheduled := 'Y';
845 GOTO skip_this;
846 end if;
847 if (p_parameter11 = c_rec.session_value) then
848 l_scheduled := 'Y';
849 GOTO skip_this;
850 end if;
851 if (p_parameter12 = c_rec.session_value) then
852 l_scheduled := 'Y';
853 GOTO skip_this;
854 end if;
855 if (p_parameter13 = c_rec.session_value) then
856 l_scheduled := 'Y';
857 GOTO skip_this;
858 end if;
859 if (p_parameter14 = c_rec.session_value) then
860 l_scheduled := 'Y';
861 GOTO skip_this;
862 end if;
863 if (p_parameter15 = c_rec.session_value) then
864 l_scheduled := 'Y';
865 GOTO skip_this;
866 end if;
867 if (p_timetoparameter = c_rec.session_value) then
868 l_scheduled := 'Y';
869 GOTO skip_this;
870 end if;
871 if (p_timefromparameter = c_rec.session_value) then
872 l_scheduled := 'Y';
873 GOTO skip_this;
874 end if;
875 l_scheduled := 'N';
876 <<skip_this>>
877 NULL;
878 END LOOP;
879 x_scheduled := l_scheduled;
880 END
881 ;
882 PROCEDURE SAVE_COMPONENT
883 (p_file_id IN NUMBER
884 ,p_Request_type IN VARCHAR2
885 )
886 IS
887 l_html_pieces utl_http.html_pieces;
888 l_url varchar2(32000);
889 m number;
890 n number;
891 o number;
892 k number;
893 j varchar2(32000);
894 j_front varchar2(32000);
895 j_trail varchar2(32000);
896 k_trail number := 0;
897 l varchar2(32000);
898 i number := 0;
899 l_Apps_web_agent varchar2(32000);
900 l_Ampersand varchar2(1) := '?';
901 BEGIN
902 /*l_apps_web_Agent := FND_WEB_CONFIG.TRAIL_SLASH(fnd_profile.value('APPS_WEB_AGENT'));
903 l_url :=l_apps_web_Agent||'BIS_SAVE_REPORT.retrieve'||l_ampersand||'file_id='||p_file_id;
904 l_html_pieces := utl_http.request_pieces(url => l_url,
905 max_pieces => 32000);
906 if (p_request_type = 'G') then
907 for m in l_html_pieces.first..l_html_pieces.last loop
908 n := instr(l_html_pieces(m),'"OA_HTML/BisChart.jsp'||l_ampersand||'index=1');
909 if (n > 0) then
910 k := n;
911 o := m;
912 exit;
913 end if;
914 end loop;
915 j := substr(l_html_pieces(0),k+10,k+2000);
916 j_front := j;
917 i := instr(j,'"></TD>');
918 if (i >0) then
919 l := substr(j,1,i-1);
920 else
921 j_trail := substr(l_html_pieces(0+1),1,2000);
922 k_trail := instr(j_trail, '"></TD>');
923 if (k_trail >0) then
924 l:=substr(j_trail,1,k_trail-1);
925 l := j_front||l;
926 end if;
927 end if;
928 BIS_SAVE_REPORT.INITWRITE(p_File_id, l);
929 end if;
930 */
931 null;
932 END;
933
934 PROCEDURE UPDATE_LAST_UPDATE
935 (p_schedule_id IN NUMBER
936 ,x_return_Status OUT NOCOPY VARCHAR2
937 ,x_msg_Data OUT NOCOPY VARCHAR2
938 ,x_msg_count OUT NOCOPY NUMBER
939 )
940 IS
941 BEGIN
942 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
943 UPDATE bis_schedule_preferences
944 SET last_update_date = SYSDATE
945 WHERE schedule_id = p_schedule_id;
946
947 COMMIT;
948 EXCEPTION
949 WHEN OTHERS THEN
950 NULL; -- Add proper error handling
951 END;
952
953 -- mdamle 07/03/2001 - Scheduling Enhancements
954 -- Returns Advanced / Basic user
955 FUNCTION getUserType return varchar2
956 IS
957 userType varchar2(1) default 'B';
958 BEGIN
959
960 userType := fnd_profile.value('PMV_USER_TYPE');
961
962 if userType is null or userType = '' then
963 userType := 'B';
964 end if;
965
966 return userType;
970
967
968 END getUserType;
969
971 -- mdamle 08/20/2001 - Add users to a role
972 procedure addUserToRole(
973 pUserId IN number
974 ,pRole IN varchar2) is
975
976 vUserName varchar2(100);
977 vUserExists number default 0;
978 begin
979
980 begin
981 select user_name
982 into vUserName
983 from fnd_user
984 where user_id = pUserId;
985 end;
986
987 -- Check if user is not already part of the role
988 select count(1)
989 into vUserExists
990 from WF_LOCAL_USER_ROLES ur, fnd_user u
991 where u.user_id = pUserId
992 and ur.user_name = u.user_name
993 and role_name = pRole;
994
995 if vUserExists = 0 then
996 wf_directory.adduserstoadhocrole(pRole, vUserName);
997 end if;
998
999 end addUserToRole;
1000
1001 -- 07/03/01 mdamle - Scheduling Enhancements
1002 FUNCTION getDefaultSchedule
1003 (pRegionCode IN VARCHAR2
1004 ,pViewBy IN VARCHAR2 default NULL
1005 ) return varchar2
1006 IS
1007 vSchedule VARCHAR2(20) default '';
1008 BEGIN
1009
1010 -- Get default schedule based on dimension level in view by
1011 if pViewBy is not null then
1012 begin
1013 select nvl(attribute8, '')
1014 into vSchedule
1015 from ak_region_items
1016 where region_code = pRegionCode
1017 and attribute2 = pViewBy;
1018 exception
1019 when others then vSchedule := '';
1020 end;
1021 end if;
1022
1023 -- If dimension level schedule has not been set up, look for report
1024 -- level schedule
1025
1026 if vSchedule = '' or vSchedule is null then
1027 begin
1028 select nvl(attribute3, '')
1029 into vSchedule
1030 from ak_regions
1031 where region_code = pRegionCode;
1032 exception
1033 when others then vSchedule := '';
1034 end;
1035 end if;
1036
1037 return vSchedule;
1038
1039 END getDefaultSchedule;
1040
1041 -- 07/03/01 mdamle - Scheduling Enhancements
1042 PROCEDURE showDefaultSchedulePage
1043 (pRegionCode IN VARCHAR2
1044 ,pFunctionName IN VARCHAR2
1045 ,pResponsibilityId IN VARCHAR2
1046 ,pApplicationId IN VARCHAR2
1047 ,pSessionId IN VARCHAR2
1048 ,pUserId IN VARCHAR2
1049 ,pViewBy IN VARCHAR2
1050 ,pReportTitle IN VARCHAR2 default NULL
1051 ,pRequestType IN VARCHAR2 default NULL
1052 ,pPlugId IN VARCHAR2 default NULL
1053 ,pParmPrint IN VARCHAR2 default NULL
1054 ,pGraphType IN VARCHAR2 default NULL
1055 ) IS
1056 lPageFunctionId NUMBER;
1057 lSessionId NUMBER;
1058 lRespId VARCHAR2(80);
1059 lApplicationId VARCHAR2(80);
1060 lParams VARCHAR2(2000);
1061 lsecurityGroupId NUMBER;
1062 lWebHtmlCall fnd_form_functions.web_html_call%TYPE;
1063 BEGIN
1064 getSchedulePageDetails(
1065 pRegionCode => pRegionCode
1066 ,pFunctionName => pFunctionName
1067 ,pResponsibilityId => pResponsibilityId
1068 ,pApplicationId => pApplicationId
1069 ,pSessionId => pSessionId
1070 ,pUserId => pUserId
1071 ,pViewBy => pViewBy
1072 ,pReportTitle => pReportTitle
1073 ,pRequestType => pRequestType
1074 ,pPlugId => pPlugId
1075 ,pParmPrint => pParmPrint
1076 ,pGraphType => pGraphType
1077 ,xPageFunctionId => lPageFunctionId
1078 ,xSessionId => lSessionId
1079 ,xRespId => lRespId
1080 ,xApplicationId => lApplicationId
1081 ,xParams => lParams
1082 ,xsecurityGroupId => lsecurityGroupId
1083 ,xWebHtmlCall => lWebHtmlCall
1084 );
1085
1086 IF lPageFunctionId IS NOT NULL THEN
1087 OracleApps.runFunction(c_function_id => lPageFunctionId
1088 ,n_session_id => lSessionId
1089 ,c_parameters => lParams
1090 ,p_resp_appl_id => lApplicationId
1091 ,p_responsibility_id => lRespId
1092 ,p_Security_group_id => lsecurityGroupId
1093 );
1094 END IF;
1095
1096 END showDefaultSchedulePage;
1097
1098
1099 -- 07/03/01 mdamle - Scheduling Enhancements
1100 PROCEDURE getSchedulePageDetails
1101 (pRegionCode IN VARCHAR2
1102 ,pFunctionName IN VARCHAR2
1103 ,pResponsibilityId IN VARCHAR2
1104 ,pApplicationId IN VARCHAR2
1105 ,pSessionId IN VARCHAR2
1106 ,pUserId IN VARCHAR2
1107 ,pViewBy IN VARCHAR2
1108 ,pReportTitle IN VARCHAR2 default NULL
1109 ,pRequestType IN VARCHAR2 default NULL
1110 ,pPlugId IN VARCHAR2 default NULL
1111 ,pParmPrint IN VARCHAR2 default NULL
1112 ,pGraphType IN VARCHAR2 default NULL
1113 ,xPageFunctionId OUT NOCOPY NUMBER
1114 ,xSessionId OUT NOCOPY NUMBER
1115 ,xRespId OUT NOCOPY VARCHAR2
1116 ,xApplicationId OUT NOCOPY VARCHAR2
1117 ,xParams OUT NOCOPY VARCHAR2
1118 ,xsecurityGroupId OUT NOCOPY NUMBER
1119 ,xWebHtmlCall OUT NOCOPY VARCHAR2
1120 )
1121 IS
1125 vPageFunctionId number;
1122 vSchedule varchar2(30);
1123 vReportName varchar2(240);
1124 vPageURL varchar2(5000);
1126 vShowAdvancedPage boolean default false;
1127 vDupScheduleId number;
1128 vParameters varchar2(5000) default '';
1129 vNextRun varchar2(30) default '';
1130 vLastRun varchar2(30) default '';
1131 vSubscribedSchedule number;
1132 vUserExists number default 0;
1133
1134 vRespId varchar2(80);
1135 vApplicationId varchar2(80);
1136 vParams varchar2(2000);
1137 vRequestType VARCHAR2(100);
1138 lWebHtmlCall fnd_form_functions.web_html_call%TYPE;
1139
1140 CURSOR cFndResp (pRespId in varchar2) is
1141 select application_id
1142 from fnd_responsibility
1143 where responsibility_id=pRespId;
1144
1145 BEGIN
1146
1147 vRespId := pResponsibilityId ;
1148 vRequestType := nvl(pRequestType, 'T');
1149 if (vRequestType = 'null') then
1150 vRequestType := 'T';
1151 end if;
1152
1153 if pApplicationId is null then
1154 if cFNDResp%ISOPEN then
1155 CLOSE cFNDResp;
1156 end if;
1157 OPEN cFNDResp(vRespId);
1158 FETCH cFNDResp INTO vApplicationId;
1159 CLOSE cFNDResp;
1160 else
1161 vApplicationId := pApplicationId;
1162 end if;
1163
1164 -- mdamle 07/30/01 - Use the default only if Report Title is blank
1165 if pReportTitle is null or pReportTitle = '' then
1166 vReportName := BIS_REPORT_UTIL_PVT.Get_Report_Title(pFunctionName);
1167 else
1168 vReportName := pReportTitle;
1169 end if;
1170
1171 vSchedule := getDefaultSchedule(pRegionCode, pViewBy);
1172
1173 -- mdamle 08/20/2001 - Unsubscribe from Schedule Page
1174 vParameters := getParameterString(pFunctionName, pUserId, pSessionId);
1175 vDupScheduleId := getDuplicateSchedule(pFunctionName, vParameters, vSchedule, pPlugId);
1176
1177 if vDupScheduleId is not null then
1178 -- Check if user has already subscribed to this report
1179 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
1180 select count(1)
1181 into vUserExists
1182 from bis_schedule_preferences
1183 where schedule_id = vDupScheduleId
1184 and user_id = pUserId
1185 and nvl(plug_id, 0) = nvl(pPlugId, 0);
1186
1187 if vUserExists > 0 then
1188 vSubscribedSchedule := vDupScheduleId;
1189 end if;
1190
1191 end if;
1192
1193 if getUserType = 'B' then
1194
1195 -- SHOW BASIC USER PAGE
1196
1197 if vSchedule = '' or vSchedule is null then
1198 -- If Schedule has not been setup, then let the user apply own options
1199 vShowAdvancedPage := true;
1200 else
1201
1202 -- Schedule has been setup
1203 begin
1204 select function_id,web_html_call
1205 into vPageFunctionId,lWebHtmlCall
1206 from fnd_form_functions
1207 where function_name = 'BIS_BU_SCHEDULE_PAGE';
1208 exception
1209 when others then vPageFunctionId := null;
1210 end;
1211 end if;
1212 end if;
1213
1214 if ((getUserType = 'A') or (vShowAdvancedPage = true)) then
1215
1216 -- SHOW ADVANCED USER PAGE
1217
1218 begin
1219 select function_id,web_html_call
1220 into vPageFunctionId,lWebHtmlCall
1221 from fnd_form_functions
1222 where function_name = 'BIS_AU_SCHEDULE_PAGE';
1223 exception
1224 when others then vPageFunctionId := null;
1225 end;
1226 end if;
1227
1228 if vPageFunctionId is not null then
1229
1230 if vSchedule = '' or vSchedule is null then
1231 -- If Schedule has not been setup for this report
1232 -- made into local call for bug 5031067
1233 select function_id, web_html_call
1234 into vPageFunctionId, lWebHtmlCall
1235 from fnd_form_functions
1236 where function_name = 'BIS_SCHEDULE_PAGE';
1237 vParams := 'regionCode='|| pRegionCode
1238 ||'&functionName='||pFunctionName
1239 ||'&parmPrint='||bis_pmv_util.encode(pParmPrint)
1240 ||'&requestType='||pRequestType
1241 ||'&plugId='||pPlugId
1242 ||'&reportTitle='||bis_pmv_util.encode(pReportTitle)
1243 ||'&graphType='||pGraphType
1244 ||'&sessionId='||pSessionId
1245 ||'&userId='||pUserId
1246 ||'&respId='||vRespId
1247 ||'&appId='||vApplicationId;
1248
1249 xPageFunctionId := vPageFunctionId;
1250 xSessionId := pSessionId;
1251 xRespId := pResponsibilityId;
1252 xApplicationId := pApplicationId;
1253 xParams := vParams;
1254 xsecurityGroupId := icx_sec.g_security_group_id;
1255 xWebHtmlCall := lWebHtmlCall;
1256
1257 else
1258
1259 -- If a schedule has been setup, get the last run date, and next run date
1260 if vDupScheduleId is not null then
1261 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
1262 -- Fixed the SQL
1266 from fnd_concurrent_requests
1263 -- Get the last run date from the last completed request
1264 select to_char(max(requested_start_date), 'Mon DD, YYYY HH:MI AM')
1265 into vLastRun
1267 where phase_code = 'C'
1268 start with request_id = (select s.concurrent_request_id from bis_scheduler s where schedule_id = vDupScheduleId)
1269 connect by prior request_id = parent_request_id;
1270
1271 -- Get the next run date from the next pending request
1272 select to_char(requested_start_date, 'Mon DD, YYYY HH:MI AM')
1273 into vNextRun
1274 from fnd_concurrent_requests
1275 where phase_code IN ('P', 'R')
1276 start with request_id = (select s.concurrent_request_id from bis_scheduler s where schedule_id = vDupScheduleId)
1277 connect by prior request_id = parent_request_id;
1278
1279 end if;
1280 -- rcmuthuk Bug Fix:2799113. Moved parmPrint param to last.
1281 -- ksadagop Bug Fix:3182441. Encoded header and reportTitle
1282 vParams := 'header='||bis_pmv_util.encode(vReportName)||
1283 '&schedule='||vSchedule||
1284 '®ionCode='||pRegionCode||
1285 '&functionName='||pFunctionName||
1286 '&sessionId='||pSessionId||
1287 '&userId='||pUserId||
1288 '&respId='||vRespId||
1289 '&appId='||vApplicationId||
1290 '&reportTitle='||bis_pmv_util.encode(vReportName)||
1291 '&requestType='||vRequestType||
1292 '&plugId='||pPlugId||
1293 '&nextRun='||vNextRun||
1294 '&lastRun='||vLastRun||
1295 '&subscribedSchedule='||vSubscribedSchedule||
1296 '&graphType='||pGraphType||
1297 '&parmPrint='||pParmPrint;
1298
1299 xPageFunctionId := vPageFunctionId;
1300 xSessionId := pSessionId;
1301 xRespId := vRespId;
1302 xApplicationId := vApplicationId;
1303 xParams := vParams;
1304 xsecurityGroupId := icx_sec.g_security_group_id;
1305 xWebHtmlCall := lWebHtmlCall;
1306 end if;
1307 end if;
1308
1309 END getSchedulePageDetails;
1310
1311
1312 -- mdamle 07/03/01 - Scheduling Enhancements
1313 FUNCTION getDuplicateSchedule
1314 (pFunctionName IN VARCHAR2
1315 ,pParameters IN VARCHAR2
1316 ,pSchedule IN VARCHAR2
1317 ,pPlugId IN VARCHAR2 default NULL
1318 ) return number
1319 IS
1320
1321 vScheduleId number;
1322 BEGIN
1323
1324 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
1325 /*
1326 if pPlugId is not null then
1327 -- Check by plug_id as well
1328 -- Will not be able to subscribe to a request of a different plug
1329 -- mdamle 08/20/2001 - Fixed SQL
1330 begin
1331 select max(schedule_id)
1332 into vScheduleId
1333 from bis_scheduler s, fnd_concurrent_requests cr, fnd_conc_release_classes rc
1334 where s.function_name = pFunctionName
1335 and s.parameter_string = pParameters
1336 and s.concurrent_request_id = cr.request_id
1337 and cr.release_class_id = rc.release_class_id
1338 and s.plug_id = pPlugId
1339 and (rc.date2 is null or rc.date2 > sysdate)
1340 and 0 < (select count(*)
1341 from fnd_concurrent_requests
1342 where phase_code = 'P'
1343 start with request_id = cr.request_id
1344 connect by prior request_id = parent_request_id);
1345
1346 exception
1347 when others then vScheduleId := null;
1348 end;
1349
1350 else
1351 -- Check for :
1352 -- same function name
1353 -- same parameters
1354 -- same schedule
1355 -- end date/time of the schedule is > today's date/time
1356 -- there exists a pending request for this schedule
1357 -- mdamle 08/20/2001 - Fixed SQL
1358 begin
1359 select max(schedule_id)
1360 into vScheduleId
1361 from bis_scheduler s, fnd_concurrent_requests cr, fnd_conc_release_classes rc
1362 where s.function_name = pFunctionName
1363 and s.parameter_string = pParameters
1364 and s.concurrent_request_id = cr.request_id
1365 and cr.release_class_id = rc.release_class_id
1366 and (rc.date2 is null or rc.date2 > sysdate)
1367 and 0 < (select count(*)
1368 from fnd_concurrent_requests
1369 where phase_code = 'P'
1370 start with request_id = cr.request_id
1371 connect by prior request_id = parent_request_id);
1372 exception
1373 when others then vScheduleId := null;
1374 end;
1375 end if;
1376 */
1377
1378 begin
1379 select max(schedule_id)
1380 into vScheduleId
1381 from bis_scheduler s, fnd_concurrent_requests cr, fnd_conc_release_classes rc
1382 where s.function_name = pFunctionName
1383 -- mdamle 09/21/01 - Fixed Bug#1999262 - Added nvl
1384 and nvl(s.parameter_string, ' ') = nvl(pParameters, ' ')
1385 and s.concurrent_request_id = cr.request_id
1386 and cr.release_class_id = rc.release_class_id
1387 and (rc.date2 is null or rc.date2 > sysdate)
1388 and 0 < (select count(*)
1389 from fnd_concurrent_requests
1390 where phase_code IN ('P', 'R')
1391 start with request_id = cr.request_id
1392 connect by prior request_id = parent_request_id);
1393 exception
1394 when others then vScheduleId := null;
1398 return vScheduleId;
1395 end;
1396
1397
1399
1400 END getDuplicateSchedule;
1401
1402 PROCEDURE subscribeToReport
1403 (pRegionCode IN VARCHAR2
1404 ,pFunctionName IN VARCHAR2
1405 ,pResponsibilityId IN VARCHAR2
1406 ,pApplicationId IN VARCHAR2
1407 ,pSessionId IN VARCHAR2
1408 ,pUserId IN VARCHAR2
1409 ,pSchedule IN VARCHAR2
1410 ,pRequestType IN VARCHAR2
1411 ,pReportTitle IN VARCHAR2
1412 ,pPlugId IN VARCHAR2 default NULL
1413 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
1414 ,pGraphType IN VARCHAR2
1415 )
1416 IS
1417 vScheduleId number;
1418 vReturnStatus varchar2(100);
1419 vMsgData varchar2(240);
1420 vMsgCount number;
1421 vAppsShortName varchar2(3) := 'BIS';
1422 vProgramName varchar2(25) := 'BIS_REPORT_SCHEDULER';
1423 vProgramDesc varchar2(30) := 'Report Generator Scheduler';
1424 vRoleName varchar2(30);
1425 vReportName varchar2(240);
1426 vPageURL varchar2(5000);
1427 vPageFunctionId number;
1428 vResult boolean;
1429 vRequestId number;
1430 vParameters varchar2(5000) default '';
1431 vUserExists number default 0;
1432 vValidSchedule number;
1433 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
1434 vFileId number;
1435 vRoleDisplayName varchar2(30);
1436
1437 vParams varchar2(2000);
1438 vRespId varchar2(80);
1439 vApplicationId varchar2(80);
1440
1441 CURSOR cFndResp (pRespId in varchar2) is
1442 select application_id
1443 from fnd_responsibility
1444 where responsibility_id=pRespId;
1445
1446 BEGIN
1447
1448 if not icx_sec.ValidateSession then
1449 return;
1450 end if;
1451
1452 vRespId := nvl(pResponsibilityId, icx_sec.getid(ICX_SEC.PV_RESPONSIBILITY_ID));
1453 if pApplicationId is null then
1454 if cFNDResp%ISOPEN then
1455 CLOSE cFNDResp;
1456 end if;
1457 OPEN cFNDResp(vRespId);
1458 FETCH cFNDResp INTO vApplicationId;
1459 CLOSE cFNDResp;
1460 else
1461 vApplicationId := pApplicationId;
1462 end if;
1463
1464 -- mdamle 07/30/01 - Use the default only if Report Title is blank
1465 if pReportTitle = '' or pReportTitle is null then
1466 vReportName := BIS_REPORT_UTIL_PVT.Get_Report_Title(pFunctionName);
1467 else
1468 vReportName := pReportTitle;
1469 end if;
1470
1471 -- mdamle 08/01/01 - Check for expired default schedule Bug# 1925970
1472 vValidSchedule := 0;
1473 begin
1474 select count(*)
1475 into vValidSchedule
1476 from fnd_conc_release_classes rc
1477 where rc.release_class_name = pSchedule
1478 and (rc.date2 is null or rc.date2 > sysdate);
1479 end;
1480
1481 if vValidSchedule = 0 then
1482 -- default Schedule has expired
1483 -- Show Error Page
1484 begin
1485 select function_id into vPageFunctionId
1486 from fnd_form_functions
1487 where function_name = 'BIS_INFORMATION_PAGE';
1488 exception
1489 when others then vPageFunctionId := null;
1490 end;
1491
1492 if vPageFunctionId is not null then
1493 /*
1494 vPageURL := 'OracleApps.RF?F='||icx_call.encrypt2(''||'*'||pResponsibilityId||'*'||icx_sec.g_security_group_id||'*'||vPageFunctionId||'**]',
1495 pSessionId)
1496 ||'&P='||icx_call.encrypt2('header='||bis_pmv_util.encode(vReportName)||
1497 '&mainMessage=BIS_SCHED_EXPIRED_ERR'||
1498 '&detailMessage=BIS_SCHED_EXPIRED_ERR_DETAIL');
1499
1500 owa_util.redirect_url(vPageURL);
1501 */
1502
1503 vParams := 'header='||vReportName||
1504 '&mainMessage=BIS_SCHED_EXPIRED_ERR'||
1505 '&detailMessage=BIS_SCHED_EXPIRED_ERR_DETAIL';
1506
1507 OracleApps.runFunction(c_function_id => vPageFunctionId
1508 ,n_session_id => pSessionId
1509 ,c_parameters => vParams
1510 ,p_resp_appl_id => vApplicationId
1511 ,p_responsibility_id => vRespId
1512 ,p_Security_group_id => icx_sec.g_security_group_id
1513 );
1514
1515 end if;
1516
1517 else
1518
1519 -- Get Parameter String
1520 vParameters := getParameterString(pFunctionName, pUserId, pSessionId);
1521
1522 -- Check if duplicate schedule exists
1523 vScheduleId := getDuplicateSchedule(pFunctionName, vParameters, pSchedule, pPlugId);
1524 if vScheduleId is not null then
1525 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
1526 -- Create a new record in bis_schedule_preferences
1527 -- Check if another subscriber has the same preferences, if so, then reuse FileId
1528
1529 begin
1530 select file_id
1531 into vFileId
1532 from bis_schedule_preferences
1533 where schedule_id = vScheduleId
1534 and nvl(title, ' ') = nvl(vReportName, ' ')
1535 and request_type = pRequestType
1539 end;
1536 and nvl(graph_type, ' ') = nvl(pGraphType, ' ');
1537 exception
1538 when no_data_found then vFileId := null;
1540
1541 if vFileId is null then
1542 vFileId := get_File_Id(pRequestType);
1543
1544 -- mdamle 09/19/01 - Trap File_ID creation error
1545 if vFileId is null or vFileId = 0 then
1546 fndLobsError(vRespId, pSessionId, vReportName);
1547 else
1548 vRoleName := gvRoleName||vFileId;
1549 vRoleDisplayName := fnd_message.get_string('BIS', 'BIS_REPORT_SUBSCRIBERS') || ' - ' || vFileId;
1550 wf_directory.createadhocrole(vRoleName, vRoleDisplayName) ;
1551 end if;
1552 else
1553 vRoleName := gvRoleName||vFileId;
1554 end if;
1555
1556 -- mdamle 09/19/01 - Trap File_ID creation error
1557 if vFileId is not null and vFileId > 0 then
1558 -- jprabhud 09/24/02 - Enh. 2470068 DB Graph HTML - Reusing file Ids to store graphs - passed in function name
1559 create_schedule_preferences(vScheduleId, pUserId, pPlugId, vReportName, pGraphType, pRequestType, vFileId, pFunctionName);
1560 commit;
1561 end if;
1562
1563 else
1564
1565 -- Create a new schedule
1566 -- mdamle 09/13/01 - Fixed Bug#1994876 - Corrected Parameter order
1567 bis_rg_schedules_pvt.create_schedule
1568 (pPlugId
1569 ,pUserId
1570 ,pFunctionName
1571 ,vRespId
1572 ,vReportName
1573 ,pGraphType
1574 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
1575 ,null
1576 ,vFileId
1577 ,pSessionId
1578 ,pRegionCode
1579 ,pRequestType
1580 ,vScheduleId
1581 ,vReturnStatus
1582 ,vMsgData
1583 ,vMsgCount
1584 --jprabhud - 12/20/02 - NLS Bug 2320171 Graph Fonts and Mutli-Byte characters
1585 ,'N',null);
1586
1587 -- mdamle 09/19/01 - Trap File_ID creation error
1588 if vFileId is null or vFileId = 0 then
1589 fndLobsError(vRespId, pSessionId, vReportName);
1590 else
1591 -- Submit a new request
1592 /*
1593 if pApplicationId is null then
1594 select application_id
1595 into vApplicationId
1596 from fnd_responsibility
1597 where responsibility_id = pResponsibilityId;
1598 else
1599 vApplicationId := pApplicationId;
1600 end if;
1601 */
1602
1603 -- mdamle 07/26/01 - Initialize Apps session for CM operation
1604 fnd_global.APPS_INITIALIZE(TO_NUMBER(pUserId),TO_NUMBER(vRespId),TO_NUMBER(vApplicationId));
1605
1606
1607 vResult := fnd_request.set_rel_class_options(application=>'FND', class_name=>pSchedule);
1608
1609 vRequestId := fnd_request.submit_request
1610 (application=>vAppsShortName
1611 ,program=>vProgramName
1612 ,description=>vProgramDesc
1613 ,argument1=>vScheduleId
1614 ,argument2=>pRegionCode
1615 ,argument3=>pFunctionName
1616 ,argument4=>pRequestType
1617 ,argument5=>pUserId
1618 ,argument6=>'JSP');
1619
1620 -- Update the Schedule
1621 if vRequestId > 0 then
1622 update_schedule( vScheduleId
1623 ,vRequestId
1624 ,vReturnStatus
1625 ,vMsgData
1626 ,vMsgCount
1627 ,vParameters);
1628
1629 end if;
1630
1631 -- Create a new role for this schedule
1632 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
1633 vRoleName := gvRoleName||vFileId;
1634 vRoleDisplayName := fnd_message.get_string('BIS', 'BIS_REPORT_SUBSCRIBERS') || ' - ' || vFileId;
1635 wf_directory.createadhocrole(vRoleName, vRoleDisplayName) ;
1636
1637 end if;
1638 end if;
1639
1640 if vFileId is not null and vFileId > 0 then
1641 if pPlugId is null then
1642 -- Add users to Role
1643 addUserToRole(pUserId, vRoleName);
1644
1645 -- Show Confirmation Page
1646 begin
1647 select function_id into vPageFunctionId
1648 from fnd_form_functions
1649 where function_name = 'BIS_CONFIRMATION_PAGE';
1650 exception
1651 when others then vPageFunctionId := null;
1652 end;
1653
1654 if vPageFunctionId is not null then
1655 /*
1656 vPageURL := 'OracleApps.RF?F='||icx_call.encrypt2(pApplicationId||'*'||pResponsibilityId||'*'||icx_sec.g_security_group_id||'*'||vPageFunctionId||'**]',
1657 pSessionId)
1658 ||'&P='||icx_call.encrypt2('header='||bis_pmv_util.encode(vReportName)||
1659 '&mainMessage=BIS_SUBSCRIBE_CONF'||
1660 '&detailMessage=BIS_SUBSCRIBE_CONF_DETAIL');
1661 owa_util.redirect_url(vPageURL);
1662 */
1663 -- rcmuthuk Bug Fix:2807197 added regCode, functName params
1664 vParams := 'header='||vReportName||
1665 '&mainMessage=BIS_SUBSCRIBE_CONF'||
1666 '&detailMessage=BIS_SUBSCRIBE_CONF_DETAIL'||
1667 '®ionCode='||pRegionCode||
1668 '&functionName='||pFunctionName;
1669
1670
1671 OracleApps.runFunction(c_function_id => vPageFunctionId
1672 ,n_session_id => pSessionId
1673 ,c_parameters => vParams
1677 );
1674 ,p_resp_appl_id => vApplicationId
1675 ,p_responsibility_id => vRespId
1676 ,p_Security_group_id => icx_sec.g_security_group_id
1678
1679 end if;
1680 else
1681 -- If coming from Portal, go to Home Page
1682 vPageURL := FND_WEB_CONFIG.TRAIL_SLASH(fnd_profile.value('ICX_REPORT_LINK')) || 'oraclemyPage.home';
1683 owa_util.redirect_url(vPageURL);
1684 end if;
1685 end if;
1686 end if;
1687
1688 END subscribeToReport;
1689
1690 function getParameterString
1691 (pFunctionName IN VARCHAR2
1692 ,pUserId IN VARCHAR2
1693 ,pSessionId IN VARCHAR2
1694 ) return varchar2
1695 IS
1696
1697 vParameters varchar2(5000) default '';
1698 vSessionValue varchar2(160);
1699
1700
1701 cursor cParameterValues (cpFunctionName varchar2, cpSessionId varchar2, cpUserId varchar2) is
1702 select session_value
1703 from bis_user_attributes
1704 where session_id = cpSessionId
1705 and user_id = cpUserId
1706 and function_name = cpFunctionName
1707 and schedule_id is null
1708 order by attribute_name;
1709
1710
1711 BEGIN
1712
1713 open cParameterValues(pFunctionName, pSessionId, pUserId);
1714 loop
1715 fetch cParameterValues into vSessionValue;
1716 exit when cParameterValues%NOTFOUND;
1717 if vParameters = '' then
1718 vParameters := vParameters || vSessionValue;
1719 else
1720 vParameters := vParameters || '+' || vSessionValue;
1721 end if;
1722 end loop;
1723
1724 return vParameters;
1725
1726 END getParameterString;
1727
1728
1729 PROCEDURE unSubscribeFromReport
1730 (pScheduleID IN number
1731 ,pPlugId IN number default NULL
1732 ) IS
1733
1734 vRoleName varchar2(30);
1735 vUserName varchar2(100);
1736 vUserId number;
1737 vPageURL varchar2(5000);
1738 vPageFunctionId number;
1739 vReportName varchar2(240) default '';
1740 vUserExists number;
1741 vCount number := 0;
1742
1743 cursor cUserFiles (cpScheduleId number, cpPlugId number) is
1744 select distinct file_id
1745 from bis_schedule_preferences
1746 where schedule_id = cpScheduleId
1747 and user_id = vUserId
1748 and nvl(plug_id, 0) = nvl(cpPlugID, 0);
1749
1750 -- jprabhud 09/24/02 - Enh 2470068 Storing of Graphs to FND_LOBS
1751 vGraphFileId varchar2(20);
1752 vAttrName varchar2(20);
1753 vFunctionName fnd_form_functions.function_name%TYPE;
1754
1755 vParams varchar2(2000);
1756 vRespId varchar2(80);
1757 vApplicationId varchar2(80);
1758
1759 CURSOR cFndResp (pRespId in varchar2) is
1760 select application_id
1761 from fnd_responsibility
1762 where responsibility_id=pRespId;
1763
1764 BEGIN
1765
1766 if not icx_sec.ValidateSession then
1767 return;
1768 end if;
1769
1770 vRespId := icx_sec.getid(ICX_SEC.PV_RESPONSIBILITY_ID);
1771 if cFNDResp%ISOPEN then
1772 CLOSE cFNDResp;
1773 end if;
1774 OPEN cFNDResp(vRespId);
1775 FETCH cFNDResp INTO vApplicationId;
1776 CLOSE cFNDResp;
1777
1778 vUserId := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
1779
1780 begin
1781 select user_name
1782 into vUserName
1783 from fnd_user
1784 where user_id = vUserId;
1785 exception
1786 when others then vUserName := null;
1787 end;
1788
1789
1790 for c1 in cUserFiles(pScheduleId, pPlugId) loop
1791 vRoleName := gvRoleName||c1.file_id;
1792
1793 if vUserName is not null then
1794 -- Check if user is part of the role
1795 select count(1)
1796 into vUserExists
1797 from WF_LOCAL_USER_ROLES
1798 where user_name = vUserName
1799 and role_name = vRoleName;
1800
1801 if vUserExists > 0 then
1802 wf_directory.removeusersfromadhocrole(vRoleName, vUserName);
1803 end if;
1804
1805 -- If no more subscribers to this file, then delete this role as well
1806 select count(1)
1807 into vUserExists
1808 from WF_LOCAL_USER_ROLES
1809 where role_name = vRoleName;
1810
1811 if vUserExists = 0 then
1812 delete wf_local_roles
1813 where name = gvRoleName||c1.file_id;
1814 end if;
1815 end if;
1816
1817 select distinct title
1818 into vReportName
1819 from bis_schedule_preferences
1820 where schedule_id = pScheduleId
1821 and file_id = c1.file_id;
1822
1823 delete_schedule_preferences(pScheduleId, vUserId, pPlugId);
1824
1825 end loop;
1826
1827 -- mdamle 08/21/2001 - Purge Data
1828 select count(*) into vCount
1829 from bis_schedule_preferences
1830 where schedule_id = pScheduleId;
1831
1832 if vCount = 0 then
1833
1834 -- jprabhud 09/24/02 - Enh 2470068 Storing of Graphs to FND_LOBS
1835 select function_name
1836 into vFunctionName
1837 from bis_scheduler
1838 where schedule_id = pScheduleId;
1839
1840 BIS_PMV_PARAMETERS_PVT.RETRIEVE_GRAPH_FILEID(vUserId,pScheduleId,'GRAPH_FILE_ID',vFunctionName,vGraphFileId);
1844 deleteReportGraphsLobs(vUserId,pScheduleId,vFunctionName);
1841 if vGraphFileId is not null then
1842 delete fnd_lobs where file_id = vGraphFileId;
1843 else
1845 end if;
1846
1847 delete_schedule(pScheduleId);
1848
1849
1850 end if;
1851
1852 -- Show Confirmation Page
1853 begin
1854 select function_id into vPageFunctionId
1855 from fnd_form_functions
1856 where function_name = 'BIS_CONFIRMATION_PAGE';
1857 exception
1858 when others then vPageFunctionId := null;
1859 end;
1860
1861 if vPageFunctionId is not null then
1862 /*
1863 vPageURL := 'OracleApps.RF?F='||icx_call.encrypt2('*'||'*'||icx_sec.g_security_group_id||'*'||vPageFunctionId||'**]',
1864 icx_sec.getID(icx_sec.PV_SESSION_ID))
1865 ||'&P='||icx_call.encrypt2('header='||bis_pmv_util.encode(vReportName)||
1866 '&mainMessage=BIS_UNSUBSCRIBE_CONF'||
1867 '&detailMessage=BIS_UNSUBSCRIBE_CONF_DETAIL');
1868
1869 owa_util.redirect_url(vPageURL);
1870 */
1871
1872 vParams := 'header='||vReportName||
1873 '&mainMessage=BIS_UNSUBSCRIBE_CONF'||
1874 '&detailMessage=BIS_UNSUBSCRIBE_CONF_DETAIL';
1875
1876 OracleApps.runFunction(c_function_id => vPageFunctionId
1877 ,n_session_id => icx_sec.getID(icx_sec.PV_SESSION_ID)
1878 ,c_parameters => vParams
1879 ,p_resp_appl_id => vApplicationId
1880 ,p_responsibility_id => vRespId
1881 ,p_Security_group_id => icx_sec.g_security_group_id
1882 );
1883
1884 end if;
1885
1886
1887 END unSubscribeFromReport;
1888
1889 PROCEDURE scheduleFunction
1890 (pRegionCode IN VARCHAR2
1891 ,pFunctionName IN VARCHAR2
1892 ,pResponsibilityId IN VARCHAR2
1893 ,pApplicationId IN VARCHAR2
1894 ,pSessionId IN VARCHAR2
1895 ,pUserId IN VARCHAR2
1896 ,pSchedule IN VARCHAR2
1897 ,pRequestType IN VARCHAR2
1898 ,pReportTitle IN VARCHAR2
1899 ,pMode IN VARCHAR2 default 'UPDATE'
1900 ,pPlugId IN VARCHAR2 default NULL
1901 ,pParmPrint IN VARCHAR2 default NULL
1902 ,pSubscribedSchedule IN VARCHAR2 default NULL
1903 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
1904 ,pGraphType IN VARCHAR2 default NULL
1905 ) IS
1906 BEGIN
1907 if pMode = 'SUBSCRIBE' then
1908 subscribeToReport
1909 (pRegionCode
1910 ,pFunctionName
1911 ,pResponsibilityId
1912 ,pApplicationId
1913 ,pSessionId
1914 ,pUserId
1915 ,pSchedule
1916 ,pRequestType
1917 ,pReportTitle
1918 ,pPlugId
1919 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
1920 ,pGraphType
1921 ) ;
1922
1923 else
1924 if pMode = 'UN_SUBSCRIBE' then
1925 unSubscribeFromReport
1926 (pSubscribedSchedule
1927 ,pPlugId
1928 );
1929 else
1930 -- ksadagop Bug Fix:3182441. Encoded header and reportTitle
1931 -- made into local call for bug 503106
1932 scheduleReports
1933 (pRegionCode=>pRegionCode
1934 ,pFunctionName=>pFunctionName
1935 ,pSessionId=>pSessionId
1936 ,pUserId=>pUserId
1937 ,pResponsibilityId=>pResponsibilityId
1938 ,pReportTitle=>bis_pmv_util.encode(pReportTitle)
1939 ,pApplicationId=>pApplicationId
1940 ,pParmPrint => pParmPrint
1941 ,pRequestType => pRequestType
1942 ,pPlugId => pPlugId
1943 -- mdamle 09/07/01 - Add Graph Number poplist
1944 ,pGraphType=>pGraphType
1945 );
1946
1947 end if;
1948 end if;
1949
1950
1951 END scheduleFunction;
1952
1953 --aleung, 8/21/01 -- autoincrement feature
1954 procedure updateIncrementDate(p_concurrent_Request_id IN NUMBER) is
1955 begin
1956
1957 update fnd_concurrent_requests
1958 set increment_dates = 'Y'
1959 where request_id = p_concurrent_request_id;
1960 commit;
1961
1962 exception
1963 when others then null;
1964 end updateIncrementDate;
1965
1966 -- mdamle 09/04/01 Scheduling Enhancements - Phase II - Purge
1967 procedure delete_schedule(
1968 pScheduleId IN NUMBER) is
1969
1970 vRequestId number;
1971
1972 begin
1973
1974
1975 begin
1976 -- Step 1 - Get the File_Id and Request_Id from schedule record
1977
1978 select concurrent_request_id
1979 into vRequestId
1980 from bis_scheduler
1981 where schedule_id = pScheduleId;
1982
1983 -- Step 2 - Delete from BIS_USER_ATTRIBUTES
1984
1985 delete bis_user_attributes
1986 where schedule_id = pScheduleId;
1987
1988 -- Step 3 - Delete from bis_schedule_preferences
1989
1990 delete_schedule_preferences(pScheduleId);
1991
1992 -- Step 4 - Delete from bis_scheduler
1996
1993
1994 delete bis_scheduler
1995 where schedule_id = pScheduleId;
1997 -- Step 5 - Cancel Request
1998 cancelRequest(vRequestId);
1999
2000
2001 end;
2002
2003 commit;
2004
2005 EXCEPTION
2006 when others then null;
2007 commit;
2008
2009 end delete_schedule;
2010
2011 -- mdamle 09/04/01 Scheduling Enhancements - Phase II - Purge Portlet Data
2012 procedure delete_portlet(
2013 pPlugId IN NUMBER
2014 ,pUserId IN NUMBER
2015 -- jprabhud 09/24/02 - Enh 2470068 Storing of Graphs to FND_LOBS -- added xGraphFileId
2016 ,xGraphFileId OUT NOCOPY VARCHAR2) is
2017
2018
2019 vCount number;
2020 vScheduleId number;
2021 -- jprabhud 09/24/02 - Enh 2470068 Storing of Graphs to FND_LOBS
2022 vFunctionName fnd_form_functions.function_name%TYPE;
2023 vRequestType varchar2(1);
2024
2025 begin
2026
2027 -- Purge Portlet data, whenever portlet is deleted or customized, basically whenever
2028 -- a new schedule is being created.
2029
2030 -- jprabhud 09/24/02 - Enh 2470068 Storing of Graphs to FND_LOBS -added vRequestType
2031
2032 if pPlugId is not null then
2033 select distinct schedule_id, request_type
2034 into vScheduleId, vRequestType
2035 from bis_schedule_preferences
2036 where user_id = pUserId
2037 and plug_id = pPlugId;
2038
2039 --jprabhud 09/24/02 - Enh 2470068 Storing of Graphs to FND_LOBS
2040 if vRequestType = 'G' then
2041 select function_name
2042 into vFunctionName
2043 from bis_scheduler
2044 where schedule_id = vScheduleId;
2045 BIS_PMV_PARAMETERS_PVT.RETRIEVE_GRAPH_FILEID(pUserId,vScheduleId,'GRAPH_FILE_ID',vFunctionName,xGraphFileId);
2046 end if;
2047
2048 delete_schedule_preferences(vScheduleId, pUserId, pPlugId);
2049
2050 select count(*) into vCount
2051 from bis_schedule_preferences
2052 where schedule_id = vScheduleId;
2053 if vCount = 0 then
2054 delete_schedule(vScheduleId);
2055 end if;
2056 end if;
2057
2058 EXCEPTION
2059 when others then null;
2060
2061 end delete_portlet;
2062
2063
2064 -- mdamle 09/04/01 Scheduling Enhancements - Phase II - Purge Portlet Data
2065 -- Deletes old portlet data from old schema
2066 procedure delete_old_portlet(
2067 pPlugId IN NUMBER
2068 ,pUserId IN NUMBER
2069 ,pKeepLatest IN BOOLEAN default false) is
2070
2071
2072 cursor cOldPortletSchedules (cpUserId varchar2, cpPlugId number) is
2073 select schedule_id
2074 from bis_scheduler
2075 where user_id = cpUserId
2076 and plug_id = cpPlugId
2077 order by schedule_id desc;
2078
2079
2080 vFirstDone boolean;
2081
2082 begin
2083
2084 -- Purge Portlet data, whenever portlet is deleted or customized, basically whenever
2085 -- a new schedule is being created.
2086
2087 if pPlugId is not null then
2088
2089 if pKeepLatest then
2090 vFirstDone := false;
2091 else
2092 vFirstDone := true;
2093 end if;
2094
2095 for c1 in cOldPortletSchedules(pUserId, pPlugId) loop
2096 if vFirstDone then
2097 delete_old_schedule(c1.schedule_id);
2098 end if;
2099 vFirstDone := true;
2100 end loop;
2101 end if;
2102
2103 end delete_old_portlet;
2104
2105 -- mdamle 09/04/01 Scheduling Enhancements - Phase II - Purge
2106 procedure delete_old_schedule(
2107 pScheduleId IN NUMBER) is
2108
2109 vRequestId number;
2110 vFileId number;
2111
2112 begin
2113
2114
2115 begin
2116 -- Step 1 - Get the File_Id and Request_Id from schedule record
2117
2118 select concurrent_request_id, file_id
2119 into vRequestId, vFileId
2120 from bis_scheduler
2121 where schedule_id = pScheduleId;
2122
2123 -- Step 2 - Delete all schedule files from FND_LOBS
2124
2125 delete fnd_lobs
2126 where file_id = vFileId;
2127
2128 -- Step 3 - Delete from BIS_USER_ATTRIBUTES
2129
2130 delete bis_user_attributes
2131 where schedule_id = pScheduleId;
2132
2133 -- Step 4 - Delete Role
2134
2135 delete wf_local_roles
2136 where name = gvRoleName||pScheduleId;
2137
2138 -- Step 5 - Cancel Request
2139 cancelRequest(vRequestId);
2140
2141 -- Step 6 - Delete from bis_schedule_preferences
2142
2143 delete_schedule_preferences(pScheduleId);
2144
2145 -- Step 7 - Delete from bis_scheduler
2146
2147 delete bis_scheduler
2148 where schedule_id = pScheduleId;
2149
2150 end;
2151
2152 commit;
2153
2154 EXCEPTION
2155 when others then null;
2156 commit;
2157
2158 end delete_old_schedule;
2159
2160
2161 -- mdamle 09/04/01 Scheduling Enhancements - Phase II - Purge Portlet Data
2162 procedure delete_Notification_Data is
2163
2164 cursor cNotificationSchedules is
2165 select schedule_id
2166 from bis_scheduler s, fnd_concurrent_requests cr
2167 where s.concurrent_request_id = cr.request_id
2168 and 0 = (select count(*)
2169 from fnd_concurrent_requests
2170 where phase_code IN ('P', 'R')
2174 where sp.schedule_id = s.schedule_id
2171 start with request_id = cr.request_id
2172 connect by prior request_id = parent_request_id)
2173 and 0 = (select count(*) from bis_schedule_preferences sp
2175 and plug_id is not null);
2176
2177 vCount number;
2178 begin
2179 for c1 in cNotificationSchedules loop
2180 -- Check if the notification is closed
2181 select count(*)
2182 into vCount
2183 from wf_notifications w, bis_schedule_preferences sp
2184 where sp.schedule_id = c1.schedule_id
2185 and w.recipient_role = gvRoleName||sp.file_id
2186 and w.status = 'CLOSED';
2187
2188 if vCount > 0 then
2189 delete_schedule(c1.schedule_id);
2190 end if;
2191 end loop;
2192
2193 -- When a user unsubscribes from a notification (when email is not sent), without closing the notification,
2194 -- the file_id is retained in FND_LOBS since the user may wish to view the file again. In this case though,
2195 -- there is now no record in bis_schedule_preferences since the user has unsubscribed from the schedule.
2196 -- Check to see if any of notifications are now closed, and delete the file if they are.
2197
2198 delete fnd_lobs
2199 where file_id in(
2200 select substr(recipient_role, Length(gvRoleName)+1)
2201 from wf_notifications w
2202 where w.recipient_role like gvRoleName || '%'
2203 and status = 'CLOSED');
2204
2205
2206 end delete_Notification_Data;
2207
2208 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
2209 PROCEDURE create_schedule_preferences(
2210 p_schedule_id IN NUMBER
2211 ,p_user_id IN VARCHAR2
2212 ,p_plug_id IN NUMBER
2213 ,p_title IN VARCHAR2
2214 ,p_graph_type IN VARCHAR2
2215 ,p_request_type IN VARCHAR2
2216 ,p_file_id IN OUT NOCOPY NUMBER
2217 -- mdamle 12/12/01 - Changes for Live Portlet
2218 ,p_live_portlet IN VARCHAR2 DEFAULT 'N'
2219 -- jprabhud 09/24/02 - Enh. 2470068 DB Graph HTML - Reusing file Ids to store graphs - added function name
2220 ,p_function_name IN VARCHAR2 DEFAULT NULL
2221 --jprabhud - 12/20/02 - NLS Bug 2320171 Graph Fonts and Mutli-Byte characters - passed in p_context_values
2222 ,p_context_values IN VARCHAR2 DEFAULT NULL
2223 ) is
2224
2225 vExternalSourceId number;
2226
2227 --jprabhud 09/24/02 - Enh 2470068 Storing of Graphs to FND_LOBS
2228 vGraphFileId varchar2(20);
2229 vRequestType varchar2(1);
2230
2231 BEGIN
2232 -- mdamle 01/16/2002 - External Source Id changes
2233 -- Before delete existing plug id, get the external source id
2234 -- and then update the new record with this source id
2235
2236
2237 --jprabhud 09/24/02 - Enh 2470068 Storing of Graphs to FND_LOBS -- added vRequestType
2238 begin
2239 select external_source_id, request_type
2240 into vExternalSourceId, vRequestType
2241 from bis_schedule_preferences
2242 where user_id = p_user_id
2243 and plug_id = p_plug_id;
2244 exception
2245 when others then vExternalSourceId := null;
2246 end;
2247
2248
2249 if p_plug_id is not null then
2250 --jprabhud 09/24/02 - Enh 2470068 Storing of Graphs to FND_LOBS -- added vGraphFileId
2251 delete_portlet(p_plug_id, p_user_id, vGraphFileId);
2252
2253 --jprabhud 09/24/02 - Enh 2470068 Storing of Graphs to FND_LOBS
2254 -- save the file id associated with the previous schedule, for the same plug,
2255 -- for the new schedule id as the previous schedule gets deleted.
2256 --jprabhud - 12/20/02 - NLS Bug 2320171
2257 if p_request_type = 'G' and vGraphFileId is not null then
2258 BIS_PMV_PARAMETERS_PVT.SAVE_GRAPH_FILEID(p_user_id,p_schedule_id,'GRAPH_FILE_ID',p_function_name,vGraphFileId);
2259 end if;
2260 end if;
2261
2262
2263 --jprabhud - 12/20/02 - NLS Bug 2320171 Graph Fonts and Mutli-Byte characters
2264 if p_live_portlet = 'N' and p_context_values is not null then
2265 --jprabhud - 12/20/02 - NLS Bug 2320171
2266 if p_request_type ='G' or p_request_type = 'R' then
2267 BIS_PMV_PARAMETERS_PVT.SAVE_CONTEXT_VALUES(p_user_id,p_schedule_id,'RENDERING_CONTEXT_VALUES',p_function_name,p_context_values);
2268 end if;
2269 end if;
2270
2271
2272
2273 -- mdamle 12/12/01 - Changes for Live Portlet
2274 if p_live_portlet = 'Y' then
2275 p_file_id := null;
2276 else
2277 if p_file_id is null or p_file_id = 0 then
2278 p_file_id := get_file_id(p_request_type);
2279 end if;
2280 end if;
2281
2282 insert into bis_schedule_preferences
2283 (schedule_id
2284 ,user_id
2285 ,plug_id
2286 ,request_type
2287 ,title
2288 ,graph_type
2289 ,file_id
2290 ,creation_Date
2291 ,last_update_date
2292 ,created_By
2293 ,last_updated_by
2294 ,last_update_login
2295 ,external_source_id
2296 )
2297 values
2298 (p_schedule_id
2299 ,p_user_id
2300 ,p_plug_id
2301 ,p_request_type
2302 ,p_title
2303 -- mdamle 09/07/01 - Add Graph Number poplist
2304 ,decode(p_request_type, 'G', p_graph_type, null)
2305 ,p_file_id
2306 ,sysdate
2307 ,sysdate
2308 ,0
2309 ,0
2310 ,0
2311 ,vExternalSourceId
2312 );
2313
2317 --serao - 02/25/02- added so that the title is not updated for rl portlet
2314 -- mdamle 10/25/01 - Update Title in ICX_PORTLET_CUSTOMIZATIONS
2315 -- In Web Portlets, the title has to be updated in ICX_PORTLET_CUSTOMIZATIONS
2316 -- since changing the title at runtime is not possible
2318
2319 if (p_plug_id is not null and p_title is not null )then
2320 updateTitleInPortal(p_schedule_id, p_plug_id, p_title);
2321 end if;
2322
2323
2324 --commit;
2325
2326 END create_schedule_preferences;
2327
2328 -- mdamle 09/04/01 - Scheduling Enhancements - Phase II - Multiple Preferences per schedule
2329 PROCEDURE delete_schedule_preferences
2330 ( pScheduleId IN number
2331 ,pUserId IN number default NULL
2332 ,pPlugId IN number default NULL
2333 ) IS
2334
2335 cursor cUserFiles (cpScheduleId number, cpUserId number, cpPlugId number) is
2336 select distinct file_id
2337 from bis_schedule_preferences
2338 where schedule_id = cpScheduleId
2339 and user_id = cpUserId
2340 and nvl(plug_id, 0) = nvl(cpPlugID, 0);
2341
2342 vCount number;
2343 BEGIN
2344
2345 if pUserId is not null then
2346
2347 for c1 in cUserFiles(pScheduleId, pUserId, pPlugId) loop
2348
2349 select count(*)
2350 into vCount
2351 from bis_schedule_preferences sp, wf_notifications w
2352 where file_id = c1.file_id
2353 and w.recipient_role = gvRoleName||sp.file_id
2354 and w.status = 'CLOSED';
2355
2356 -- If notification still open, then leave it alone.
2357 if vCount = 1 then
2358 -- Step 1 - Delete file from FND_LOBS for this preference
2359 delete fnd_lobs
2360 where file_id = c1.file_id;
2361
2362 -- Step 2 - Delete Role
2363 delete wf_local_roles
2364 where name = gvRoleName||c1.file_id;
2365 end if;
2366
2367 -- Step 3 - Delete the user preference
2368 delete bis_schedule_preferences
2369 where schedule_id = pScheduleId
2370 and user_id = pUserId
2371 and nvl(plug_id, 0) = nvl(pPlugID, 0);
2372 end loop;
2373
2374 else
2375 -- Step 1 - Delete all schedule files from FND_LOBS
2376
2377 delete fnd_lobs
2378 where file_id IN (select file_id from bis_schedule_preferences where schedule_id = pScheduleId);
2379
2380 -- Step 2 - Delete Role
2381
2382 delete wf_local_roles
2383 where name IN (select gvRoleName||file_id from bis_schedule_preferences where schedule_id = pScheduleId);
2384
2385 -- Step 3 - Delete all preferences for this schedule
2386 delete bis_schedule_preferences
2387 where schedule_id = pScheduleId;
2388 end if;
2389
2390 commit;
2391
2392 EXCEPTION
2393 when others then null;
2394 commit;
2395
2396 END delete_schedule_preferences;
2397
2398 procedure cancelRequest(pRequestId IN NUMBER) IS
2399
2400 vStatusCode varchar2(1);
2401 vPhaseCode varchar2(1);
2402 vCompletionText varchar2(240);
2403 vPendingRequestId number;
2404
2405 begin
2406 begin
2407 select request_id, status_code, phase_code
2408 into vPendingRequestId, vStatusCode, vPhaseCode
2409 from fnd_concurrent_requests
2410 where phase_code <> 'C'
2411 start with request_id = pRequestId
2412 connect by prior request_id = parent_request_id;
2413 exception
2414 when no_data_found then
2415 vPendingRequestId := null;
2416 end;
2417
2418 if vPendingRequestId is not null then
2419 if vStatusCode = 'R' then
2420 vStatusCode := 'T';
2421 else
2422 if vStatusCode IN ('W','B') then
2423 vStatusCode := 'X';
2424 vPhaseCode := 'C';
2425 else
2426 vStatusCode := 'D';
2427 vPhaseCode := 'C';
2428 end if;
2429 end if;
2430
2431 vCompletionText := fnd_message.get_string('BIS', 'BIS_REQUEST_CANCELED');
2432
2433 update fnd_concurrent_requests
2434 set status_code = vStatusCode,
2435 phase_code = vPhaseCode,
2436 completion_text = vCompletionText
2437 where request_id = vPendingRequestId;
2438 end if;
2439
2440
2441 end cancelRequest;
2442
2443 -- mdamle 09/19/01 - Trap File_ID creation error
2444 procedure fndLobsError( pResponsibilityId IN number
2445 ,pSessionId IN number
2446 ,pReportName IN varchar2) is
2447
2448 vPageURL varchar2(5000);
2449 vPageFunctionId number;
2450 vParams varchar2(2000);
2451 vRespId varchar2(80);
2452 vApplicationId varchar2(80);
2453
2454 CURSOR cFndResp (pRespId in varchar2) is
2455 select application_id
2456 from fnd_responsibility
2457 where responsibility_id=pRespId;
2458
2459 begin
2460
2461 vRespId := nvl(pResponsibilityId, icx_sec.getid(ICX_SEC.PV_RESPONSIBILITY_ID));
2462 if cFNDResp%ISOPEN then
2463 CLOSE cFNDResp;
2464 end if;
2465 OPEN cFNDResp(vRespId);
2466 FETCH cFNDResp INTO vApplicationId;
2467 CLOSE cFNDResp;
2468
2469 begin
2470 select function_id into vPageFunctionId
2471 from fnd_form_functions
2472 where function_name = 'BIS_INFORMATION_PAGE';
2473 exception
2474 when others then vPageFunctionId := null;
2475 end;
2476
2477 if vPageFunctionId is not null then
2478 /*
2479 vPageURL := 'OracleApps.RF?F='||icx_call.encrypt2(''||'*'||pResponsibilityId||'*'||icx_sec.g_security_group_id||'*'||vPageFunctionId||'**]',pSessionId)
2483
2480 ||'&P='||icx_call.encrypt2('header='||bis_pmv_util.encode(pReportName)||
2481 '&mainMessage=BIS_SCHEDULE_ERR'||
2482 '&detailMessage=BIS_SCHEDULE_ERR_DETAIL');
2484
2485
2486 -- Show the Default schedule
2487 owa_util.redirect_url(vPageURL);
2488 */
2489
2490 vParams := 'header='||pReportName||
2491 '&mainMessage=BIS_SCHEDULE_ERR'||
2492 '&detailMessage=BIS_SCHEDULE_ERR_DETAIL';
2493
2494 OracleApps.runFunction(c_function_id => vPageFunctionId
2495 ,n_session_id => pSessionId
2496 ,c_parameters => vParams
2497 ,p_resp_appl_id => vApplicationId
2498 ,p_responsibility_id => vRespId
2499 ,p_Security_group_id => icx_sec.g_security_group_id
2500 );
2501
2502 end if;
2503
2504 end fndLobsError;
2505
2506
2507 procedure updateTitleInPortal( p_schedule_id IN NUMBER,
2508 p_plug_id IN NUMBER,
2509 p_title IN VARCHAR2) is
2510
2511 vSQL varchar2(1000);
2512 vPortletPlugExists number;
2513 vTitle varchar2(2000);
2514 vRegion varchar2(240);
2515 vFunctionName fnd_form_functions.function_name%TYPE;
2516 vType varchar2(30);
2517
2518 begin
2519
2520 vSQL := 'select count(*) from icx_portlet_customizations where plug_id = :1';
2521
2522 begin
2523 execute immediate vSQL into vPortletPlugExists using p_Plug_Id;
2524 Exception
2525 when others then
2526 vPortletPlugExists := 0;
2527 end;
2528
2529
2530 if vPortletPlugExists > 0 then
2531
2532 begin
2533 select s.function_name, type
2534 into vFunctionName, vType
2535 from bis_scheduler s, fnd_form_functions f
2536 where schedule_id = p_schedule_id
2537 and s.function_name = f.function_name;
2538 exception
2539 when others then null;
2540 end;
2541
2542 vRegion := BIS_PMV_UTIL.getReportRegion(vFunctionName);
2543 -- mdamle 01/03/2002 - Added plug_id to TL - multiple plugs on a page - will have their respective title link functions
2544 -- mdamle 09/30/2002 - Use the Fwk implementation for the Title link in Web Portlets.
2545 if vType <> 'WEBPORTLET' then
2546 -- P1 Bug 3902169 : Do not pass javascript in the title
2547 -- vTitle := '<A href="javascript:TL'||p_plug_id||'()"><font class=PortletHeaderText>'||p_title||BIS_PMV_UTIL.getAppendTitle(vRegion);
2548 vTitle := p_title||BIS_PMV_UTIL.getAppendTitle(vRegion);
2549
2550 --Title limit = 100 in icx_portlet_customizations table
2551 vTitle := substr(vTitle, 1, 96);
2552
2553 -- P1 Bug 3879391 : Do not pass javascript in the title
2554 -- vTitle := vTitle || '</a>';
2555
2556 vSQL := 'update icx_portlet_customizations set title = :1 where plug_id = :2';
2557
2558 begin
2559 execute immediate vSql using vTitle, p_Plug_Id;
2560 Exception
2561 when others then null;
2562 end;
2563 end if;
2564 end if;
2565
2566 commit;
2567
2568 end updateTitleInPortal;
2569
2570 procedure savePortletSettings( p_schedule_id in varchar2,
2571 p_request_type in varchar2,
2572 p_graph_type in varchar2,
2573 p_title in varchar2,
2574 p_user_id in varchar2,
2575 p_plug_id in varchar2) is
2576
2577 begin
2578
2579 update bis_schedule_preferences
2580 set request_type = p_request_type,
2581 graph_type = decode(p_request_type, 'G', p_graph_type, null),
2582 title = p_title
2583 where user_id = p_user_id
2584 and plug_id = p_plug_id;
2585
2586 --serao - 02/25/02- added so that the title is not updated for rl portlet
2587 if (p_plug_id is not null and p_title is not null) then
2588 updateTitleInPortal(p_schedule_id, p_plug_id, p_title);
2589 end if;
2590
2591 commit;
2592
2593 end savePortletSettings;
2594
2595 -- mdamle 01/16/2002
2596 procedure updateExternalSource( p_schedule_id in varchar2,
2597 p_file_id in varchar2,
2598 p_external_source_id in varchar2) is
2599 begin
2600
2601 update bis_schedule_preferences
2602 set external_source_id = p_external_source_id
2603 where schedule_id = p_schedule_id
2604 and nvl(p_file_id, 0) = nvl(p_file_id, 0);
2605
2606 commit;
2607
2608 end updateExternalSource;
2609
2610 procedure expireExistingFile(
2611 p_schedule_id in varchar2,
2612 p_file_id in varchar2,
2613 o_external_source_id OUT NOCOPY VARCHAR2
2614 ) IS
2615 BEGIN
2616
2617 SELECT external_source_id INTO o_external_source_id
2618 FROM bis_schedule_preferences
2619 WHERE schedule_id = p_schedule_id
2620 AND nvl(file_id, 0) = nvl(p_file_id, 0);
2621
2622 IF (o_external_source_id IS NOT NULL) THEN
2623 update fnd_lobs
2624 SET expiration_date = SYSDATE
2625 WHERE file_id = o_external_source_id;
2626
2627 COMMIT;
2628 -- fnd_gfm.purge_expired;
2629 END IF;
2630 END expireExistingFile;
2631
2632
2633 --jprabhud 09/24/02 - Enh 2470068 Storing of Graphs to FND_LOBS
2634 procedure deleteReportGraphsLobs(p_user_id in varchar2
2635 ,p_schedule_id in varchar2
2636 ,p_function_name in varchar2)
2637 IS
2638 BEGIN
2639 delete fnd_lobs where file_id in
2640 (select session_value
2641 from bis_user_attributes
2642 where user_id = p_user_id
2643 and schedule_id = p_schedule_id
2644 and function_name = p_function_name
2645 and attribute_name in
2646 ('GRAPH_FILE_ID_1','GRAPH_FILE_ID_2','GRAPH_FILE_ID_3','GRAPH_FILE_ID_4','GRAPH_FILE_ID_5','GRAPH_FILE_ID_6')
2647 );
2648
2649 EXCEPTION
2650 WHEN OTHERS then NULL;
2651
2652 END deleteReportGraphsLobs;
2653
2654
2655 END BIS_RG_SCHEDULES_PVT;