1 package body FND_CONC_PRIVATE_UTILS as
2 /* $Header: AFCPSCRB.pls 120.11 2011/10/25 20:16:09 jtoruno ship $ */
3
4
5 -- FUNCTION get_fs_svc_name:
6 -- Gets the service name based on given node and the value of FS_SVC_PREVIX
7 -- profile option.
8 --
9 -- node is the node on which FNDFS is running
10 --
11 FUNCTION get_fs_svc_name(node IN VARCHAR2) RETURN VARCHAR2
12 IS
13 svc varchar2(254);
14
15 BEGIN
16 if (fnd_profile.defined('FS_SVC_PREFIX')) then
17 fnd_profile.get('FS_SVC_PREFIX', svc);
18 if (svc is not null) then
19 svc := substr(svc || node, 1, 254);
20 else
21 svc := 'FNDFS_' || node;
22 end if;
23 else
24 svc := 'FNDFS_' || node;
25 end if;
26
27 RETURN svc;
28
29 END get_fs_svc_name;
30
31
32 -- FUNCTION generate_req_out_URL:
33 -- Creates a URL for a report file, given a request ID.
34 --
35 -- req_id is the request id
36 -- So we know what file we're securing
37 --
38 -- lifetime is the number of minutes for which the URL is valid
39 -- After this time, VERIFY_ID will no longer consider the token
40 -- generated by the call to CREATE_ID to be valid.
41 -- Here we default to 1440 minutes = one day, since we're using this
42 -- function to secure a file which multiple users may want to see.
43 --
44 FUNCTION generate_req_out_URL(req_id IN NUMBER,
45 lifetime IN NUMBER DEFAULT 1440) RETURN VARCHAR2
46 IS
47 base VARCHAR2(257);
48 url VARCHAR2(512);
49 fname VARCHAR2(255);
50 node VARCHAR2(50);
51 id VARCHAR2(32);
52 mtype VARCHAR2(80);
53 fs_enabled VARCHAR2(2);
54 pos number;
55 svc varchar2(254);
56 BEGIN
57
58 fnd_profile.get('APPS_WEB_AGENT', base);
59
60 IF(base IS NULL) THEN
61 RETURN NULL;
62 END IF;
63
64 SELECT fcr.outfile_name, fcr.outfile_node_name, fmt.mime_type
65 INTO fname, node, mtype
66 FROM fnd_concurrent_requests fcr, fnd_mime_types_vl fmt
67 WHERE fcr.request_id = req_id
68 AND fcr.output_file_type = fmt.file_format_code
69 AND rownum = 1;
70
71 /* Bug 3322395: No longer limit FNDFS service prefix to 9
72 characters. Logic to get the service name for FNDFS is now
73 split into new function. Avoids duplication of code. */
74
75 svc := get_fs_svc_name(node);
76
77 id := fnd_webfile.create_id(fname,
78 svc,
79 lifetime,
80 mtype,
81 req_id);
82
83 base := Ltrim(Rtrim(base));
84
85 -- Strip any file path from the base URL by truncating at the
86 -- third '/'.
87 -- This leaves us with something like 'http://ap363sun:8000'.
88
89 pos := instr(base, '/', 1, 3);
90 if (pos > 0) then
91 base := substr(base, 1, pos - 1);
92 end if;
93
94 -- 2638328 - security violation - removing login information from URL
95 url := base || '/OA_CGI/FNDWRR.exe?' || 'temp_id=' || id ;
96
97 RETURN url;
98
99 END generate_req_out_URL;
100
101
102 /*returns either error or (possibly null) resubmission time */
103 function get_resub_time(req_id in number) Return varchar2 is
104
105 my_resub varchar2(100);
106 dummy number;
107 unit_code varchar2(100);
108 type_code varchar2(100);
109 resub_time varchar2(8);
110 resub_end date;
111 req_sdate date;
112 interval number;
113 sysd date;
114 class_id number;
115 class_app_id number;
116 class_type varchar2(1);
117 class_info varchar2(64);
118 found number;
119 offset number;
120 temp_date date;
121 dow number;
122 dom number;
123 nextdom number;
124 tz_adjusted boolean;
125 requested_by number;
129 begin
126 client_timezone varchar2(50);
127 server_timezone varchar2(50);
128
130
131 /* are we wasting our time? */
132 select count(*) into dummy
133 from fnd_concurrent_requests
134 where request_id = req_id;
135
136 if dummy = 0 then
137 return 'CONC-Request missing';
138 end if;
139
140 /* get info */
141 select Resubmit_Time, Resubmit_Interval_Type_Code, Requested_Start_Date,
142 Resubmit_Interval_Unit_Code, Resubmit_Interval, sysdate,
143 RELEASE_CLASS_ID, RELEASE_CLASS_APP_ID, Resubmit_End_Date, requested_by
144 into resub_time, type_code, req_sdate,
145 unit_code, interval, sysd,
146 class_id, class_app_id,resub_end, requested_by
147 from fnd_concurrent_requests
148 where request_id = req_id;
149
150
151 /* get class info */
152 class_type := NULL;
153 class_info := NULL;
154
155 if class_id is not null then
156 select C.class_type, C.class_info
157 into class_type, class_info
158 from fnd_conc_release_classes C
159 where RELEASE_CLASS_ID = class_id
160 and APPLICATION_ID = class_app_id;
161 end if;
162
163 /* hack to take care of 'SMART' resubmission with complex schedules
164 basiclly we return the earliest period start (of the periods involved
165 in the schedule) that is greater thensysdate. If none then return sysdate
166 +1 ... Some nice to have improvements would be to use the stop times
167 for negated periods (instead of start times). Also updating the start/stop
168 times so that at least the stop time occurs in the future.
169 */
170
171 if (unit_code= 'SMART') then
172 select to_char(nvl(min(p.PERIOD_START_TIME),sysdate+1),
173 'DD-MON-YYYY HH24:MI:SS')
174 into my_resub
175 from fnd_conc_rel_conj_members cm,
176 fnd_conc_rel_disj_members dm,
177 fnd_conc_release_periods p
178 where cm.DISJUNCTION_ID = dm.DISJUNCTION_ID
179 and cm.DISJUNCTION_APPLICATION_ID = dm.DISJUNCTION_APPLICATION_ID
180 and cm.release_class_id=class_id
181 and cm.class_APPLICATION_ID=class_app_id
182 and dm.PERIOD_OR_STATE_FLAG = 'P'
183 and dm.PERIOD_APPLICATION_ID = p.APPLICATION_ID
184 and dm.PERIOD_ID = p.concurrent_PERIOD_ID
185 and P.PERIOD_START_TIME>sysdate;
186
187 return my_resub;
188 end if;
189
190
191
192 if class_type = 'S' then
193
194 temp_date := req_sdate;
195 tz_adjusted := false;
196
197 if fnd_timezones.timezones_enabled() = 'Y' then
198 client_timezone := fnd_profile.value_specific('CLIENT_TIMEZONE_ID',
199 requested_by, -1, -1, -1, -1);
200 if client_timezone is not null then
201 client_timezone := fnd_timezones.get_code(client_timezone);
202 server_timezone := fnd_timezones.get_server_timezone_code();
203 if server_timezone is not null and client_timezone <> server_timezone
204 then
205 temp_date := fnd_timezone_pub.adjust_datetime(temp_date,
206 server_timezone, client_timezone);
207 tz_adjusted := true;
208 end if;
209 end if;
210 end if;
211
212 temp_date := fnd_conc_release_class_utils.calc_specific_startdate(temp_date,
213 class_info);
214 if temp_date is null then
215 return null;
216 end if;
217
218 if tz_adjusted = true then
219 temp_date := fnd_timezone_pub.adjust_datetime(temp_date, client_timezone,
220 server_timezone);
221 end if;
222
223 my_resub := to_char(temp_date, 'DD-MON-YYYY HH24:MI:SS');
224
225
226 else
227
228 /* this will be resub time for all but type 'S' class types */
229
230 select TO_CHAR(DECODE(resub_time,
231 NULL, DECODE (type_code, 'START', req_sdate, sysd)
232 + DECODE(unit_code,
233 /* 'MINUTES', GREATEST(interval, 1)/1440, */
234 'MINUTES', interval/1440,
235 'HOURS', interval/24,
236 'DAYS', interval,
237 'MONTHS', ADD_MONTHS(
238 DECODE(type_code, 'START', req_sdate, sysd),
239 interval) -
240 DECODE(type_code, 'START', req_sdate, sysd)),
241 /*TIME*/ TO_DATE (TO_CHAR (sysd, 'DD-MON-YYYY')
242 || ' ' || resub_time, 'DD-MON-YYYY HH24:MI:SS')
243 + Decode(
244 Sign(To_Date(To_Char(sysd, 'HH24:MI:SS'), 'HH24:MI:SS')
245 - To_Date(resub_time, 'HH24:MI:SS')
246 ), 1, 1, 0)), 'DD-MON-YYYY HH24:MI:SS')
247 into my_resub
248 from dual;
249
250 end if;
251
252 /* some fairly obvious reasons to quit */
253 if (interval is null and resub_time is null and (class_type <> 'S')) or
254 (NVL(resub_end,sysd) < sysd) or
255 (NVL(resub_end,TO_DATE(my_resub,
256 'DD-MON-YYYY HH24:MI:SS')) <
257 TO_DATE(my_resub,
258 'DD-MON-YYYY HH24:MI:SS')) then
259 return null;
260 end if;
261
262 /***------------------------------------------------------------------
263 ** Make sure the requested_start_date for the next request is greater
264 ** than sysdate (e.g. if the system went down for a week, then cycle
265 ** through resub dates until we get one after sysdate. */
266
267 /* this will only be necessary for type_code = 'START' */
268
269 while (sysd > to_date(my_resub,
270 'DD-MON-YYYY HH24:MI:SS')) loop
271
272 select TO_CHAR(TO_DATE(my_resub,
273 'DD-MON-YYYY HH24:MI:SS') +
274 /* DECODE(unit_code, 'MINUTES', GREATEST(interval, 1)/1440, */
278 'MONTHS', ADD_MONTHS(
275 DECODE(unit_code, 'MINUTES', interval/1440,
276 'HOURS', interval/24,
277 'DAYS', interval,
279 To_Date(my_resub,
280 'DD-MON-YYYY HH24:MI:SS'),
281 interval)
282 - To_Date(my_resub,
283 'DD-MON-YYYY HH24:MI:SS'))
284 ,'DD-MON-YYYY HH24:MI:SS')
285 into my_resub
286 from dual;
287 end loop;
288
289 if (NVL(resub_end,TO_DATE(my_resub,
290 'DD-MON-YYYY HH24:MI:SS')) <
291 TO_DATE(my_resub,
292 'DD-MON-YYYY HH24:MI:SS')) then
293 return null;
294 else return my_resub;
295 end if;
296
297 end get_resub_time;
298
299 -- Procedure added as bug fix for bug#4172657
300 Procedure set_notification_attr (nid in number ,
301 requrl in varchar2,
302 req_id in number,
303 stat in varchar2,
304 completion in date) is
305
306 pname varchar2(512);
307 user varchar2(255);
308 pdesc varchar2(240);
309 e_addr varchar2(240);
310 wf_error exception;
311 begin
312 select u.user_name, r.description,
313 p.user_concurrent_program_name, u.email_address
314 into user, pdesc, pname, e_addr
315 from fnd_concurrent_requests r, fnd_concurrent_programs_vl p,
316 fnd_user u
317 where r.program_application_id = p.application_id
318 and r.concurrent_program_id = p.concurrent_program_id
319 and r.request_id = req_id
320 and u.user_id = r.requested_by;
321
322 if (pdesc is not null) then
323 pname := pdesc || '(' || pname || ')';
324 end if;
325
326
327 begin
328
329 if ( requrl is not NULL ) then
330 wf_notification.setattrtext(nid, 'REQ_URL', requrl);
331 end if;
332
333 wf_notification.setattrnumber(nid, 'REQID', req_id);
334 wf_notification.setattrtext(nid, 'STAT', RTRIM(stat));
335 wf_notification.setattrtext(nid, 'PROGNAME', pname);
336 wf_notification.setattrdate(nid, 'TIME', completion);
337 wf_notification.setattrdate(nid, 'DATE', completion);
338 wf_notification.setattrtext(nid, 'SUBMITTER', user);
339 wf_notification.setattrtext(nid, '#FROM_ROLE', user);
340 wf_notification.setattrtext(nid, '#WFM_REPLYTO', e_addr);
341 wf_notification.denormalize_notification(nid);
342 exception
343 when others then
344 raise wf_error;
345 end;
346
347
348 end set_notification_attr;
349
350 function send_simple_done_msg(req_id in number,
351 stat in varchar2,
352 recip in varchar2,
353 completion in date,
354 etext out NOCOPY varchar2,
355 estack out NOCOPY varchar2
356 ) return number is
357
358 nid number; /*notification id*/
359 pname varchar2(512); /*progname*/
360 user varchar2(255); /*submitter*/
361 pdesc varchar2(240); /* prog description */
362 profbuf varchar2(255); /* buffer for profile value */
363 requrl varchar2(255) := NULL; /* URL for output */
364 url_ttl number; /* Time to live for url - in minutes */
365 ename varchar2(30); /* Error name returned by wf_core.get_error */
366 e_addr varchar2(240); /* email address of user */
367 wf_error exception;
368 msgname varchar2(255);
369 msgtype varchar2(255) := 'FNDCMMSG';
370 send_url boolean;
371 -- for bug 4535714
372 ofilesize number;
373 saveoutputflag varchar2(1);
374 -- for bug 4172657
375 notify_id number;
376
377 cursor nids_cursor is
378 select notification_id
379 from wf_notifications
380 where group_id = nid;
381 -- code for bug 4172657 ends
382
383 begin
384 estack := '';
385 etext := '';
386
387 -- bug4535714
388 -- get the output file size and save output flag here
389 -- output file may exist at this point but deleted later depending on flag
390 -- handling null as zero
391
392 select decode(ofile_size,NULL,0,ofile_size), save_output_flag
393 into ofilesize, saveoutputflag
394 from fnd_concurrent_requests
395 where request_id = req_id;
396
397 -- bug453714 ends
398
399 fnd_profile.get ('CONC_ATTACH_URL', profbuf);
400
401 if profbuf = 'Y' then
402
403 fnd_profile.get ('CONC_URL_LIFETIME', profbuf);
404 url_ttl := to_number(profbuf);
405
406 /* Note, we must set a default value for url_ttl. *
407 * Stored procedure defaults only work for missing args, *
408 * not for null args. */
409 if (url_ttl is null) then
410 url_ttl := 1440;
411 end if;
412
413 -- bug4535714
414 -- if the filesize is zero or saveoutputflag is not set, don't send URL
415 -- as it will point to a zero size file and error
416
417 if ofilesize > 0 and saveoutputflag='Y' then
418 msgname := 'REQ_COMPLETION_W_URL';
419 send_url := true;
420 else
421 msgname := 'SIMPLE_REQ_COMPLETION';
422 send_url := false;
423 end if;
424 -- bug4535714 end
425
426 else
427 msgname := 'SIMPLE_REQ_COMPLETION';
431
428 send_url := false;
429
430 end if;
432 -- If this profile option is set, use the Workflow 'sendgroup' API
433 -- to send a notification to all members of the role.
434 fnd_profile.get('CONC_NOTIFY_GROUP', profbuf);
435
436 begin
437 if profbuf = 'Y' then
438 nid := wf_notification.sendgroup(recip, msgtype, msgname);
439 open nids_cursor;
440 LOOP
441 fetch nids_cursor into notify_id;
442 -- Get a unique file name each time
443 if (send_url) then
444 requrl := fnd_webfile.get_url(
445 fnd_webfile.request_out,
446 req_id, null, null, url_ttl);
447 end if;
448 set_notification_attr (notify_id ,requrl,
449 req_id,stat,completion);
450 exit when nids_cursor%NOTFOUND;
451 end LOOP;
452
453 close nids_cursor;
454 else
455 nid := wf_notification.send(recip, msgtype, msgname);
456
457 -- Get a unique file name each time
458 if (send_url) then
459 requrl := fnd_webfile.get_url( fnd_webfile.request_out,
460 req_id, null, null, url_ttl);
461 end if;
462 set_notification_attr (nid ,requrl,req_id,stat,completion);
463
464 end if;
465
466 exception
467 when others then
468 raise wf_error;
469 end;
470
471
472 return(nid);
473
474
475 exception
476 when wf_error then
477 wf_core.get_error(ename, etext, estack);
478 return(-1);
479 when others then
480 return(-2);
481
482 end send_simple_done_msg;
483
484 /* Gets the machine name in node format for current session instance */
485
486 function get_session_node return varchar is
487
488 mynode varchar2(255) := null;
489
490 begin
491
492 select LOGFILE_NODE_NAME
493 into mynode
494 from fnd_concurrent_requests
495 where request_id = fnd_global.conc_request_id;
496
497 return mynode;
498
499 exception
500 when others then
501 begin
502 select upper(substr (machine, 1, instr(machine, '.')-1 ))
503 into mynode
504 from v$session
505 where audsid=USERENV('SESSIONID');
506 exception
507 when others then null;
508 end;
509
510 return mynode;
511
512 end get_session_node;
513
514
515 procedure record_temp_file_use_pvt (filename in varchar2, type in varchar2,
516 req_id in number, sess_id in number, node in varchar2) is
517 PRAGMA AUTONOMOUS_TRANSACTION;
518
519 begin
520
521 update fnd_temp_files
522 set REQUEST_ID = decode (req_id, 0, null, req_id),
523 SESSION_ID = sess_id
524 where filename = record_temp_file_use_pvt.filename
525 and node = record_temp_file_use_pvt.node
526 and type = record_temp_file_use_pvt.type;
527
528 if not SQL%FOUND then
529 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
530 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'fnd.plsql.fnd_conc_private_utils.record_temp_file_use_pvt',
531 'insert into fnd_temp_files');
532 end if;
533 insert into fnd_temp_files
534 (FILENAME, TYPE, REQUEST_ID, SESSION_ID, NODE)
535 values (record_temp_file_use_pvt.filename, record_temp_file_use_pvt.type, req_id, sess_id, record_temp_file_use_pvt.node);
536 else
537 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
538 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'fnd.plsql.fnd_conc_private_utils.record_temp_file_use_pvt',
539 'fnd_temp_files updated');
540 end if;
541 end if;
542
543 commit;
544
545 exception
546 when others then
547 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
548 FND_MESSAGE.SET_NAME('FND', 'SQL-GENERIC ERROR');
549 FND_MESSAGE.SET_TOKEN('ERRNO', SQLCODE, FALSE);
550 FND_MESSAGE.SET_TOKEN('REASON', SQLERRM, FALSE);
551 FND_MESSAGE.SET_TOKEN('ROUTINE', 'RECORD_TEMP_FILE_USE_PVT');
552 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION, 'fnd.plsql.fnd_conc_private_utils.record_temp_file_use_pvt');
553 end if;
554
555 end record_temp_file_use_pvt;
556
557 /* Overloaded: records use of a temp file in fnd_temp_files */
558
559 procedure record_temp_file_use(filename in varchar, node in varchar) is
560
561 req_id number := null;
562 type_code varchar2(1);
563 begin
564 req_id := fnd_global.conc_request_id;
565 if req_id > 0 then
566 type_code := 'R';
567 else
568 type_code := 'O';
569 end if;
570 record_temp_file_use(filename, node, type_code, req_id);
571
572 end record_temp_file_use;
573
574 /* Overloaded: records use of a temp file in fnd_temp_files
575 * type_codes: F=Fnd_file context; R=Request context; O=Other context
576 */
577 procedure record_temp_file_use( filename in varchar, node in varchar,
578 type_code in varchar, req_id in number) is
579
580 ses_id number := null;
581 mynode varchar2(255);
582
583 begin
584
585 mynode := node;
586 if (mynode is null) then
587 mynode := get_session_node;
588 end if;
589
590 select userenv('SESSIONID') into ses_id from dual;
591
592 record_temp_file_use_pvt (filename, type_code, req_id, ses_id, mynode);
593
594 exception
598
595 when others then null;
596
597 end record_temp_file_use;
599 procedure erase_temp_file_use_pvt (filename in varchar2, node in varchar2, type in varchar) is
600 PRAGMA AUTONOMOUS_TRANSACTION;
601
602 begin
603
604 delete from fnd_temp_files
605 where filename = erase_temp_file_use_pvt.filename
606 and upper(node) = upper(erase_temp_file_use_pvt.node)
607 and upper(type) = upper(erase_temp_file_use_pvt.type);
608
609 if not SQL%FOUND then
610 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
611 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'fnd.plsql.fnd_conc_private_utils.erase_temp_file_use_pvt',
612 'filename: '|| filename ||' on node: '|| node ||
613 ' with type: '||type||' not found');
614 end if;
615 else
616 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
617 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'fnd.plsql.fnd_conc_private_utils.erase_temp_file_use_pvt',
618 'filename: '|| filename ||' on node: '|| node ||
619 ' with type: '||type||' erased');
620 end if;
621 end if;
622
623 commit;
624
625 exception
626 when others then
627 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
628 FND_MESSAGE.SET_NAME('FND', 'SQL-GENERIC ERROR');
629 FND_MESSAGE.SET_TOKEN('ERRNO', SQLCODE, FALSE);
630 FND_MESSAGE.SET_TOKEN('REASON', SQLERRM, FALSE);
631 FND_MESSAGE.SET_TOKEN('ROUTINE', 'ERASE_TEMP_FILE_USAGE_PVT');
632 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION, 'fnd.plsql.fnd_conc_private_utils.erase_temp_file_usage_pvt');
633 end if;
634
635 end erase_temp_file_use_pvt;
636
637 /* deletes record of a temp file from fnd_temp_files */
638
639 procedure erase_temp_file_use(filename in varchar, node in varchar, type in varchar) is
640
641 mynode varchar2(255);
642
643 begin
644
645 mynode := node;
646
647 if (mynode is null) then
648 mynode := get_session_node;
649 end if;
650
651 erase_temp_file_use_pvt (filename, mynode, type);
652
653 exception
654 when others then null;
655
656 end erase_temp_file_use;
657
658 /* returns 1 if temp file has been recorded, else returns 0 */
659
660 function check_temp_file_use (filename in varchar, node in varchar, type in varchar) return number is
661 mynode varchar(255) := null;
662 file_recorded number := 0;
663 begin
664 mynode := node;
665 if mynode is null then
666 mynode := get_session_node;
667 end if;
668
669 begin
670 select 1
671 into file_recorded
672 from fnd_temp_files
673 where filename = check_temp_file_use.filename
674 and (node is null or upper(node) = upper(mynode))
675 and upper(type) = upper(check_temp_file_use.type);
676 exception
677 when no_data_found then
678 null;
679 when others then
680 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
681 FND_MESSAGE.SET_NAME('FND', 'SQL-GENERIC ERROR');
682 FND_MESSAGE.SET_TOKEN('ERRNO', SQLCODE, FALSE);
683 FND_MESSAGE.SET_TOKEN('REASON', SQLERRM, FALSE);
684 FND_MESSAGE.SET_TOKEN('ROUTINE', 'ERASE_TEMP_FILE_USAGE_PVT');
685 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION, 'fnd.plsql.fnd_conc_private_utils.check_temp_file_use');
686 end if;
687 end;
688
689 return file_recorded;
690
691 end check_temp_file_use;
692
693 Procedure call_pp_plsql(user_id in number,
694 resp_id in number,
695 resp_appl_id in number,
696 security_group_id in number,
697 site_id in number,
698 login_id in number,
699 conc_login_id in number,
700 prog_appl_id in number,
701 conc_program_id in number,
702 conc_request_id in number,
703 conc_priority_request in number,
704 program in varchar2,
705 step in number,
706 errbuf out NOCOPY varchar2,
707 retcode out NOCOPY number) is
708 PRAGMA AUTONOMOUS_TRANSACTION;
709 session_id number;
710 sql_string varchar2(255);
711 tmp_buff varchar2(2000);
712 begin
713 fnd_global.bless_next_init('FND_PERMIT_0003');
714 fnd_global.initialize (session_id, user_id, resp_id,
715 resp_appl_id, security_group_id, site_id, login_id,
716 conc_login_id, prog_appl_id, conc_program_id,
717 conc_request_id, conc_priority_request);
718
719 sql_string := 'BEGIN ' || program ||
720 '(:errbuf, :retcode, :step); END;';
721
722
723 execute immediate sql_string using out errbuf,out retcode, in step;
724
725 /* If retcode is not set, then we'll assume success, since
726 * no exception was raised. */
727
728 if retcode is null then
729 retcode := 0;
730 end if;
731
732 return;
733
734 /* The calling routing will handle exceptions. */
735 end call_pp_plsql;
736
737
738
739 /*
740 * Switch a manager's resource consumer group to the group it is assigned to
741 * in FND_CONCURRENT_QUEUES, or the default group if one is not assigned.
742 */
743 procedure set_mgr_rcg(qaid in number, qid in number) is
744
745 que_rcg varchar2(32);
746 old_rcg varchar2(32);
747 resource_mgr_off exception;
748
752 begin
749 pragma exception_init(resource_mgr_off, -29373);
750 pragma autonomous_transaction;
751
753
754 begin
755 select nvl(RESOURCE_CONSUMER_GROUP, 'DEFAULT_CONSUMER_GROUP')
756 into que_rcg
757 from fnd_concurrent_queues
758 where CONCURRENT_QUEUE_ID = qid
759 and APPLICATION_ID = qaid;
760 exception
761 when others then
762 que_rcg := 'DEFAULT_CONSUMER_GROUP';
763 end;
764 dbms_session.switch_current_consumer_group(que_rcg, old_rcg, FALSE);
765
766 commit;
767
768 exception
769 -- Ignore this error, this means the user has not chosen to use the resource manager
770 when resource_mgr_off then
771 commit;
772
773 when others then
774 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
775 FND_MESSAGE.SET_NAME('FND', 'CONC-CANT SWITCH RCG');
776 FND_MESSAGE.SET_TOKEN('ROUTINE', 'SET_MGR_RCG');
777 FND_MESSAGE.SET_TOKEN('QID', qid);
778 FND_MESSAGE.SET_TOKEN('RCG', que_rcg);
779 FND_MESSAGE.SET_TOKEN('REASON', SQLERRM);
780 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION, 'fnd.plsql.FND_CONC_PRIVATE_UTILS.SET_MGR_RCG');
781 end if;
782 commit;
783
784 end set_mgr_rcg;
785
786
787 /*
788 * Switch multiorg context
789 * Not for use in 11i
790 */
791 procedure set_multiorg_context(org_type in varchar2, org_id in number) is
792
793 begin
794
795 execute immediate('begin mo_global.init(''M''); end;');
796 if ( org_type = 'S' ) then
797 mo_global.set_policy_context('S', org_id);
798 elsif ( org_type = 'M' ) then
799 mo_global.set_policy_context('M', null);
800 end if;
801
802
803 end set_multiorg_context;
804
805
806
807 end FND_CONC_PRIVATE_UTILS;
[Home] [Help]