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