1 package body FND_CONCURRENT as
2 /* $Header: AFCPUTLB.pls 120.12 2006/12/12 22:19:49 ckclark ship $ */
3 --
4 -- Package
5 -- FND_CONCURRENT
6 -- Purpose
7 -- Concurrent processing related utilities
8 -- History
9 -- XX/XX/93 Ram Bhoopalam Created
10 --
11 --
12 -- PRIVATE VARIABLES
13 --
14 oraerrmesg varchar2(240) := NULL;
15 P_ICMCID number := 0;
16 P_CRM boolean := FALSE;
17
18 PHASE_LOOKUP_TYPE constant varchar2(16) := 'CP_PHASE_CODE';
19 STATUS_LOOKUP_TYPE constant varchar2(16) := 'CP_STATUS_CODE';
20
21 TYpe ConcProcessLocks Is Table of
22 Fnd_Concurrent_Processes.Lk_Handle%TYPE
23 Index By Binary_Integer;
24
25 CmLkHandles ConcProcessLocks;
26
27
28 -- Exception info.
29
30 --
31 -- PRIVATE FUNCTIONS
32 -- --
33
34 function get_handle(cpid IN number default 0,
35 apid IN number default 0,
36 cqid IN number default 0,
37 lkhn IN OUT NOCOPY varchar2) return boolean is
38 result number;
39 icm_cid varchar2(20);
40 begin
41
42 if ( apid = 0 AND cqid = 1 ) then
43 Select Max(Concurrent_Process_ID)
44 Into icm_cid
45 From Fnd_Concurrent_Processes
46 Where Process_Status_Code = 'A'
47 And (Queue_Application_ID = 0 And
48 Concurrent_Queue_ID = 1);
49
50 if (Sql%NotFound) then
51 P_ICMCID := 0;
52 raise no_data_found;
53 end if;
54
55 if ( icm_cid <> P_ICMCID ) then
56 P_ICMCID := icm_cid;
57 raise no_data_found;
58 end if;
59 end if;
60
61 lkhn := CmLkHandles(cpid);
62 return TRUE;
63
64 exception
65 when no_data_found then
66 FND_DCP.get_lk_handle(apid, cqid, cpid, lkhn, result);
67 if ( result = 1 ) then
68 CmLKHandles(cpid) := lkhn;
69 return TRUE;
70 else
71 return FALSE;
72 end if;
73
74 when others then
75 oraerrmesg := substr(SQLERRM, 1, 80);
76 Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
77 Fnd_Message.Set_Token('ERROR', oraerrmesg, FALSE);
78 Fnd_Message.Set_Token('ROUTINE', 'FND_CONCURRENT.Get_Handle', FALSE);
79 return FALSE;
80 end get_handle;
81
82 -- procedure is internal to this package.
83 -- returns developer phase and status values for a given phase and status
84 -- codes.
85
86 procedure get_dev_phase_status(phase_code IN varchar2,
87 status_code IN varchar2,
88 dev_phase OUT NOCOPY varchar2,
89 dev_status OUT NOCOPY varchar2) is
90 begin
91 IF (phase_code = 'R') THEN
92 Dev_Phase := 'RUNNING';
93 ELSIF (phase_code = 'P') THEN
94 Dev_Phase := 'PENDING';
95 ELSIF (phase_code = 'C') THEN
96 Dev_Phase := 'COMPLETE';
97 ELSIF (phase_code = 'I') THEN
98 Dev_Phase := 'INACTIVE';
99 END IF;
100
101 IF (status_code = 'R') THEN
102 Dev_Status := 'NORMAL';
103 ELSIF (status_code = 'T') THEN
104 Dev_Status := 'TERMINATING';
105 ELSIF (status_code = 'A') THEN
106 Dev_Status := 'WAITING';
107 ELSIF (status_code = 'B') THEN
108 Dev_Status := 'RESUMING';
109
110 ELSIF (status_code = 'I') THEN
111 Dev_Status := 'NORMAL'; -- Pending normal
112 ELSIF (status_code = 'Q') THEN
113 Dev_Status := 'STANDBY'; -- Pending, due to incompatabilities
114 ELSIF (status_code = 'F' or status_code = 'P') THEN
115 Dev_Status := 'SCHEDULED'; --
116 ELSIF (status_code = 'W') THEN
117 Dev_Status := 'PAUSED'; --
118
119 ELSIF (status_code = 'H') THEN
120 Dev_Status := 'ON_HOLD'; -- Request Pending and on hold
121 ELSIF (status_code = 'S') THEN
122 Dev_Status := 'SUSPENDED'; --
123 ELSIF (status_code = 'U') THEN
124 Dev_Status := 'DISABLED'; -- Program has been disabled
125 ELSIF (status_code = 'M') THEN
126 Dev_Status := 'NO_MANAGER'; -- No defined manager can run it
127
128 ELSIF (status_code = 'C') THEN
129 Dev_Status := 'NORMAL'; -- Completed normally
130 ELSIF (status_code = 'G') THEN
131 Dev_Status := 'WARNING'; -- Completed with warning
132 ELSIF (status_code = 'E') THEN
133 Dev_Status := 'ERROR'; -- Completed with error
134 ELSIF (status_code = 'X') THEN
135 Dev_Status := 'TERMINATED'; -- Was terminated by user
136 ELSIF (status_code = 'D') THEN
137 Dev_Status := 'DELETED'; -- Was deleted when pending
138 END IF;
139 end;
140
141 function get_svc_state(p_enabled IN varchar2,
142 p_ctrl_code IN varchar2,
143 p_run_procs IN varchar2,
144 p_max_procs IN varchar2) return varchar2 is
145
146 begin
147 if (p_enabled = 'N') then
148 return 'DISABLED';
149 elsif (p_ctrl_code IS NULL and p_run_procs > 0) then
150 return 'ACTIVE';
151 elsif ((p_ctrl_code IN ('E', 'X', 'N') or
152 p_ctrl_code IS NULL) and
153 p_run_procs = 0 and p_max_procs = 0) then
154 return 'INACTIVE';
155 elsif (p_ctrl_code = 'P') then
156 return 'SUSPENDED';
157 else
158 return 'TRANSIT';
159 end if;
160 end;
161
162
163 --
164 -- PUBLIC FUNCTIONS
165 --
166 --
167 -- Name
168 -- GET_REQUEST_STATUS
169 -- Purpose
170 -- returns the status of concurrent request and completion message
171 -- if the request has completed. Returns both user ( translatable )
172 -- and developer ( could you be used to compare/check and base their
173 -- program logic ) version for phase and status values.
174 -- Arguments ( input )
175 -- request_id - Request id for which status has to be checked
176 -- - If Application and prorgram information is passed,
177 -- - most recent request id for this program is returned
178 -- - along with the status and phase.
179 -- appl_shortname - Application to which the program belongs
180 -- program - Program name ( appl and program information used
181 -- - only if request id is not provided )
182 -- Arguments ( output )
183 -- phase - Request phase ( from meaning in fnd_lookups )
184 -- status - Request status( for display purposes )
185 -- dev_phase - Request phase as a constant string so that it
186 -- - can be used for comparisons )
187 -- dev_status - Request status as a constatnt string
188 -- message - Completion message if request has completed
189 --
190 function get_request_status(request_id IN OUT NOCOPY number,
191 appl_shortname IN varchar2 default NULL,
192 program IN varchar2 default NULL,
193 phase OUT NOCOPY varchar2,
194 status OUT NOCOPY varchar2,
195 dev_phase OUT NOCOPY varchar2,
196 dev_status OUT NOCOPY varchar2,
197 message OUT NOCOPY varchar2) return boolean is
198
199 Prog_Appl_ID number;
200 Program_ID number;
201 phase_code char;
202 status_code char;
203 req_phase char;
204 req_status char;
205 phasem varchar2(80);
206 statusm varchar2(80);
207 comptext varchar2(255);
208 Reqid_for_message varchar2(15);
209
210 Req_ID number := Request_ID;
211 program_validate_error exception;
212 fcr_access_error exception;
213 status_fetch_error exception;
214
215 begin
216 --
217 -- Check if request id is provided. If request id is not provided
218 -- then get the most recent request id for the program name given
219 --
220 dev_status := NULL;
221 dev_phase := NULL;
222 status := NULL;
223 phase := NULL;
224
225
226 if (Request_ID is null) then
227 if (Program is null or Appl_ShortName is null) then
228 Fnd_Message.Set_Name('FND', 'CONC-Req information required');
229 return FALSE;
230 end if;
231 begin
232 Select Concurrent_Program_ID, P.Application_ID
233 Into Program_ID, Prog_Appl_ID
234 From Fnd_Concurrent_Programs P,
235 Fnd_Application A
236 Where Concurrent_Program_Name = Program
237 And P.Application_ID = A.Application_ID
238 And A.Application_Short_Name = Appl_ShortName;
239 --
240 -- If no rows returned, return message "CONC-Invalid Appl/Prog combo"
241 --
242 exception
243 when no_data_found THEN
244 Fnd_Message.Set_Name('FND', 'CONC-Invalid Appl/Prog combo');
245 return FALSE;
246 when others then
247 raise; -- program_validate_error
248 end;
249 --
250 -- Check for the most recently submitted request for this program
251 --
252 begin
253 Select Max(Request_ID)
254 Into Req_ID
255 From Fnd_Concurrent_Requests
256 Where Program_Application_ID = Prog_Appl_ID
257 And Concurrent_Program_ID = Program_ID;
258 --
259 -- If No rows returned, then return message saying there are no
260 -- requests for this program "CONC-No req for appl/prog"
261 --
262 exception
263 when no_data_found then
264 Fnd_Message.Set_Name('FND', 'CONC-No req for appl/prog');
265 Fnd_Message.Set_Token('APPL', Appl_ShortName, FALSE);
266 Fnd_Message.Set_Token('PROGRAM', Program, FALSE);
267 return FALSE;
268 when others then
269 raise; -- fcr_access_error
270 end;
271
272 Request_ID := Req_ID;
273
274 end if;
275
276 --
277 -- Get Request Phase, Status and Completion Text
278 --
279 begin
280
281
282
283 Select Phase_Code, Status_Code, Completion_Text,
284 Phase.Lookup_Code, Status.Lookup_Code,
285 Phase.Meaning, Status.Meaning
286 Into req_phase, req_status, comptext,
287 phase_code, status_code,
288 phasem, statusm
289 From Fnd_Concurrent_Requests R,
290 Fnd_Concurrent_programs P,
291 Fnd_Lookups Phase,
292 Fnd_Lookups Status
293 Where
294 Phase.Lookup_Type = PHASE_LOOKUP_TYPE
295 AND Phase.Lookup_Code = Decode(Status.Lookup_Code,
296 'H', 'I',
297 'S', 'I',
298 'U', 'I',
299 'M', 'I',
300 R.Phase_Code) AND
301 Status.Lookup_Type = STATUS_LOOKUP_TYPE AND
302 Status.Lookup_Code =
303 Decode(R.Phase_Code,
304 'P', Decode(R.Hold_Flag, 'Y', 'H',
305 Decode(P.Enabled_Flag, 'N', 'U',
306 Decode(Sign(R.Requested_Start_Date - SYSDATE),1,'P',
307 R.Status_Code))),
308 'R', Decode(R.Hold_Flag, 'Y', 'S',
309 Decode(R.Status_Code, 'Q', 'B',
310 'I', 'B',
311 R.Status_Code)),
312 R.Status_Code)
313 And (R.Concurrent_Program_Id = P.Concurrent_program_ID AND
314 R.Program_Application_ID= P.Application_ID )
315 And Request_Id = Req_ID;
316 --
317 -- If no rows, return message...
318 --
319 exception
320 when no_data_found then
321 Reqid_for_message := Req_ID;
322 Fnd_Message.Set_Name('FND', 'CONC-Request missing');
323 Fnd_Message.Set_Token('ROUTINE',
324 'FND_CONCURRENT.GET_REQUEST_STATUS', FALSE);
325 Fnd_Message.Set_Token('REQUEST', Reqid_for_message, FALSE);
326 return FALSE;
327 when others then
328 raise; -- status_fetch_error
329 end;
330 --
331 -- Copy phase, status and completion text to out varaibles and
332 -- fill in developer names for phase and status
333 --
334 Phase := phasem;
335 Status := statusm;
336 message := comptext;
337
338 get_dev_phase_status(phase_code, status_code, dev_phase, dev_status);
339
340 return TRUE;
341 exception
342 -- when program_validate_error then
343 -- null;
344 -- when fcr_access_error then
345 -- null;
346 -- when status_fetch_error then
347 -- null;
348 when others then
349 oraerrmesg := substr(SQLERRM, 1, 80);
350 Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
351 Fnd_Message.Set_Token('ERROR', oraerrmesg, FALSE);
352 Fnd_Message.Set_Token('ROUTINE',
353 'FND_CONCURRENT.GET_REQUEST_STATUS', FALSE);
354 return FALSE;
355 end get_request_status;
356
357
358 --
359 -- Name
360 -- WAIT_FOR_REQUEST
361 -- Purpose
362 -- Waits for the request completion, returns phase/status and
363 -- completion text to the caller. Calls sleep between db checks.
364 -- Arguments (input)
365 -- request_id - Request ID to wait on
366 -- interval - time b/w checks. Number of seconds to sleep
367 -- - (default 60 seconds)
368 -- max_wait - Max amount of time to wait (in seconds)
369 -- - for request's completion
370 -- Arguments (output)
371 -- User version of phase and status
372 -- Developer version of phase and status
373 -- Completion text if any
374 -- phase - Request phase ( from meaning in fnd_lookups )
375 -- status - Request status( for display purposes )
376 -- dev_phase - Request phase as a constant string so that it
380 --
377 -- - can be used for comparisons )
378 -- dev_status - Request status as a constatnt string
379 -- message - Completion message if request has completed
381 function wait_for_request(request_id IN number default NULL,
382 interval IN number default 60,
383 max_wait IN number default 0,
384 phase OUT NOCOPY varchar2,
385 status OUT NOCOPY varchar2,
386 dev_phase OUT NOCOPY varchar2,
387 dev_status OUT NOCOPY varchar2,
388 message OUT NOCOPY varchar2) return boolean is
389 Call_Status boolean;
390 Time_Out boolean := FALSE;
391 pipename varchar2(60);
392 req_phase varchar2(15);
393 STime number(30);
394 ETime number(30);
395 Rid number := request_id;
396 i number;
397 begin
398 if (Rid is null) then
399 Fnd_Message.Set_Name('FND', 'CONC-Req information required');
400 return FALSE;
401 end if;
402
403 if ( max_wait > 0 ) then
404 Time_Out := TRUE;
405 Select To_Number(((To_Char(Sysdate, 'J') - 1 ) * 86400) +
406 To_Char(Sysdate, 'SSSSS'))
407 Into STime From Sys.Dual;
408 end if;
409
410 LOOP
411 call_status := FND_CONCURRENT.get_request_status(Rid, '', '',
412 phase, status, req_phase, dev_status, message);
413 if ( call_status = FALSE OR req_phase = 'COMPLETE' ) then
414 dev_phase := req_phase;
415 return (call_status);
416 end if;
417
418 if ( Time_Out ) then
419 Select To_Number(((To_Char(Sysdate, 'J') - 1 ) * 86400) +
420 To_Char(Sysdate, 'SSSSS'))
421 Into ETime From Sys.Dual;
422
423 if ( (ETime - STime) >= max_wait ) then
424 dev_phase := req_phase;
425 return (call_status);
426 end if;
427 end if;
428 dbms_lock.sleep(interval);
429 END LOOP;
430
431 exception
432 when others then
433 oraerrmesg := substr(SQLERRM, 1, 80);
434 Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
435 Fnd_Message.Set_Token('ERROR', oraerrmesg, FALSE);
436 Fnd_Message.Set_Token('ROUTINE',
437 'FND_CONCURRENT.WAIT_FOR_REQUEST', FALSE);
438 return FALSE;
439 end wait_for_request;
440
441 --
442 -- Name
443 -- FND_CONCURRENT_GET_MANAGER_STATUS
444 -- Purpose
445 -- Returns the target ( number that should be active at this instant )
446 -- and active number of processes for a given manager.
447 -- along with the current PMON method currently in use
448 -- Arguments (input)
449 -- applid - Application ID of application under which the
450 -- - manager is registered
451 -- managerid - Concurrent manager ID ( queue id )
452 -- (output)
453 -- target - Number of manager processes that should be active
454 -- - for the current workshift in effect
455 -- active - actual number of processes that are active
456 -- pmon_method - RDBMS/OS
457 -- message - message if any
458 --
459
460 procedure get_manager_status(applid IN number default 0,
461 managerid IN number default 1,
462 targetp OUT NOCOPY number,
463 activep OUT NOCOPY number,
464 pmon_method OUT NOCOPY varchar2,
465 callstat OUT NOCOPY number) is
466 lkh FND_CONCURRENT_PROCESSES.Lk_Handle%TYPE;
467 result number; -- result code from DBMS_LOCK.Request_Lock
468 alive number; -- is process alive? 1=TRUE 0=FALSE
469 i number := 0;
470 errflag boolean := FALSE;
471 gothandle boolean;
472 mtype number;
473 CartType varchar2(10);
474 cur_session_id number;
475
476 Cursor C1 IS
477 Select Concurrent_Process_Id, Session_Id
478 From Fnd_Concurrent_Processes
479 Where Process_Status_Code in ( 'A', 'C', 'T' )
480 And (Queue_Application_ID = applid and
481 Concurrent_Queue_ID = managerid );
482 begin
483 callstat := 0;
484 pmon_method := 'LOCK';
485
486
487 /* If a service and uses AQCART, use RDBMS PMON method */
488 /* If a service and does not use AQCART, return values */
489 /* from FCQ */
490 select manager_type,
491 Running_processes, MAX_PROCESSES, Cartridge_Handle
492 into mtype, ActiveP, TargetP, CartType
493 from Fnd_Concurrent_Queues Q, Fnd_Cp_Services S
494 Where S.Service_ID = Q.Manager_Type
495 And (Q.Application_ID = applid
496 And Q.Concurrent_Queue_ID = managerid);
497
498 if (mtype>999) then
499 if (CartType = 'AQCART') then
500 select count(*)
501 into ActiveP
502 from gv$session GV, fnd_concurrent_processes P
503 where
504 GV.Inst_id = P.Instance_number
505 And GV.audsid = p.session_id
506 And (Process_Status_Code not in ('S','K','U'))
507 And ( Queue_Application_ID = applid AND
508 Concurrent_Queue_ID = managerid );
509
510 pmon_method := 'RDBMS';
511 end if;
512
513 return;
514 end if;
515
516 --
520 TargetP := 0;
517 -- Lock PMON method
518 --
519 ActiveP := 0;
521
522 /* By convention we want FNDSM's to show exactly the same data as
523 the ICM so we have added the mtype = 6 condition to trigger the
524 icm status detection */
525
526 if (( applid = 0 AND managerid = 1 ) OR (mtype = 6)) then
527 TargetP := 1;
528 gothandle := get_handle(0, 0, 1, lkh);
529 if ( gothandle ) then
530 /*---------------------------------------------------------+
531 | Bug 2093806: Use FND_DCP.Check_Process_Status_By_Handle |
532 +---------------------------------------------------------*/
533 FND_DCP.check_process_status_by_handle(lkh, result, alive);
534 if (alive = 1) then
535 ActiveP := 1;
536 elsif ((alive = 0) and ( result <> 0 )) then
537 /*-------------------------------------------------------+
538 | Message set by FND_DCP.Check_Process_Status_By_Handle |
539 | available for retreival. |
540 +-------------------------------------------------------*/
541 callstat := result;
542 end if;
543 return;
544 else
545 callstat := 1;
546 return;
547 end if;
548 end if;
549
550 Select Max_Processes Into TargetP From Fnd_Concurrent_Queues
551 Where Concurrent_Queue_ID = ManagerID
552 And Application_ID = ApplID;
553
554
555 /* Bug 3443136: Since this API may be called from within a concurrent
556 program running in this session, select the current session id and
557 compare it with the session id of each manager process.
558 If they are they same, do not call check_process_status_by_handle,
559 as this will cause this session to lose its lock.
560 */
561 SELECT userenv('SESSIONID') INTO cur_session_id FROM dual;
562
563
564 For C1REC in C1 Loop
565
566 if C1REC.SESSION_ID = cur_session_id then
567 /* This session is obviously alive ... */
568 i := i + 1;
569 else
570 gothandle := get_handle(C1REC.CONCURRENT_PROCESS_ID,
571 applid, managerid, lkh);
572 if ( gothandle ) then
573 /*---------------------------------------------------------+
574 | Bug 2093806: Use FND_DCP.Check_Process_Status_By_Handle |
575 +---------------------------------------------------------*/
576 FND_DCP.check_process_status_by_handle(lkh, result, alive);
577 if (alive = 1) then
578 i := i + 1;
579 elsif ((alive = 0) and ( result <> 0 )) then
580 /*-------------------------------------------------------+
581 | Message set by FND_DCP.Check_Process_Status_By_Handle |
582 | available for retrieval. |
583 +-------------------------------------------------------*/
584 callstat := result;
585 end if;
586 else
587 callstat := 1;
588 return;
589 end if;
590 end if;
591 End Loop;
592
593 ActiveP := i;
594 return;
595
596 exception
597 when no_data_found then
598 ActiveP := i;
599 return;
600
601 when others then
602 TargetP := 0;
603 ActiveP := 0;
604 callstat := 1;
605 oraerrmesg := substr(SQLERRM, 1, 80);
606 Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
607 Fnd_Message.Set_Token('ERROR', oraerrmesg, FALSE);
608 Fnd_Message.Set_Token('ROUTINE',
609 'FND_CONCURRENT.GET_MANAGER_STATUS', FALSE);
610 return;
611 end get_manager_status;
612
613 -- Name
614 -- FND_CONCURRENT.SET_STATUS_AUTONUMOUS
615 -- Purpose
616 -- Updates given request status and completion text in an autonomous
617 -- transaction. This is function is called in set_completion_status
618 -- function. (Internal use only).
619 --
620 -- Arguments (input)
621 -- Request_ID- Request Id for which it needs update status.
622 -- Stutus - 'NORMAL','WARNING', or 'ERROR'
623 -- message - Optional message
624 --
625 -- Returns:
626 -- If there is any sql error then returns sql error other-wise null string
627
628 function SET_STATUS_AUTONOMOUS(request_id IN number,
629 status IN varchar2,
630 message IN varchar2,
631 interim IN boolean default FALSE)
632 return varchar2 is
633 ret_str varchar2(80) := null;
634 PRAGMA AUTONOMOUS_TRANSACTION;
635 begin
636 if ( interim ) then
637 if(upper(status) = 'W') then
638 update fnd_concurrent_requests
639 set interim_status_code = 'W',
640 req_information = substrb(message,1,240)
641 where request_id = set_status_autonomous.request_id;
642 --debug('updated req_information for request_id '|| set_status_autonomous.request_id);
643 else
644 update fnd_concurrent_requests
645 set interim_status_code = set_status_autonomous.status,
649 end if;
646 completion_text = substrb(message, 1, 240)
647 where request_id = set_status_autonomous.request_id;
648 --debug('updated completion_text for request_id '|| set_status_autonomous.request_id);
650
651 else
652 update fnd_concurrent_requests
653 set phase_code = 'C',
654 status_code = set_status_autonomous.status,
655 completion_text = substrb(message, 1, 240)
656 where request_id = set_status_autonomous.request_id;
657
658 end if;
659
660 commit;
661
662 return ret_str;
663
664 exception
665 when others then
666 rollback;
667 ret_str := substrb(SQLERRM, 1, 80);
668 return ret_str;
669 end;
670
671 --
672 -- Name
673 -- FND_CONCURRENT.SET_COMPLETION_STATUS
674 -- Purpose
675 -- Called from a concurrent request to set its completion
676 -- status and message.
677 --
678 -- Arguments (input)
679 -- status - 'NORMAL', 'WARNING', or 'ERROR'
680 -- message - Optional message
681 --
682 -- Returns:
683 -- TRUE on success. FALSE on error.
684 --
685
686 function set_completion_status (status IN varchar2,
687 message IN varchar2) return boolean is
688 scode varchar2(1);
689 ret_str varchar2(80) := null;
690 req_id number;
691 begin
692 if (upper(status) = 'NORMAL') then
693 scode := 'C';
694 elsif (upper(status) = 'WARNING') then
695 scode := 'G';
696 elsif (upper(status) = 'ERROR') then
697 scode := 'E';
698
699 else
700 fnd_message.set_name('FND', 'CONC-SCS BAD STATUS');
701 fnd_message.set_token('STATUS', status);
702 return FALSE;
703 end if;
704
705 if ( lengthb(message) > 240 ) then
706 fnd_file.put_line( fnd_file.log, message);
707 end if;
708
709 req_id := fnd_global.conc_request_id;
710
711 ret_str := set_status_autonomous(req_id, scode, message);
712
713 -- if ret_str has some string that means some error otherwise return TRUE
714 if ( nvl(lengthb(ret_str), 0 ) > 0 ) then
715 Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
716 Fnd_Message.Set_Token('ERROR', ret_str, FALSE);
717 Fnd_Message.Set_Token('ROUTINE',
718 'FND_CONCURRENT.SET_COMPLETION_STATUS', FALSE);
719 return FALSE;
720 else
721 return TRUE;
722 end if;
723
724 end set_completion_status;
725
726 --
727 -- Name
728 -- Get_Request_Print_Options
729 -- Purpose
730 -- Returns the print options for a concurrent request in the
731 -- form of a PLSQL table type Print_Options_Tbl_Typ.
732 -- This function is required when multiple printers have been
733 -- specified for the request.
734 --
735 function GET_REQUEST_PRINT_OPTIONS
736 (request_id IN number,
737 number_of_copies OUT NOCOPY number,
738 print_style OUT NOCOPY varchar2,
739 printer OUT NOCOPY varchar2,
740 save_output_flag OUT NOCOPY varchar2) return boolean is
741 begin
742 select number_of_copies, print_style, printer, save_output_flag
743 into number_of_copies, print_style, printer, save_output_flag
744 from fnd_concurrent_requests r
745 where r.request_id = get_request_print_options.request_id;
746
747 return TRUE;
748
749 exception
750 when others then
751 oraerrmesg := substr(SQLERRM, 1, 80);
752 Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
753 Fnd_Message.Set_Token('ERROR', oraerrmesg, FALSE);
754 Fnd_Message.Set_Token('ROUTINE',
755 'FND_CONCURRENT.GET_REQUEST_PRINT_OPTIONS', FALSE);
756 return FALSE;
757 end get_request_print_options;
758
759 --
760 -- Name
761 -- Get_Request_Print_Options
762 -- Purpose
763 -- Returns the print options for a concurrent request.
764 -- Parameters
765 -- request_id: The request_id for the concurrent request
766 -- print_options: pl/sql table of print_options_tbl_typ
767 -- (see spec for type details)
768 -- Returns
769 -- The total number post-processing actions for printing
770 --
771 function GET_REQUEST_PRINT_OPTIONS
772 (request_id IN number,
773 print_options OUT NOCOPY print_options_tbl_typ)
774 return number is
775 counter number := 0;
776 cursor c1 is
777 select p.number_of_copies, r.print_style,
778 p.arguments, r.save_output_flag
779 from fnd_concurrent_requests r,
780 fnd_conc_pp_actions p
781 where r.request_id = p.concurrent_request_id
782 and p.action_type = 1
783 and p.concurrent_request_id = get_request_print_options.request_id
784 order by sequence;
785 begin
786 for c1_data in c1 loop
787 counter := counter + 1;
788 print_options(counter).number_of_copies := c1_data.number_of_copies;
789 print_options(counter).print_style := c1_data.print_style;
793 return counter;
790 print_options(counter).printer := c1_data.arguments;
791 print_options(counter).save_output_flag := c1_data.save_output_flag;
792 end loop;
794
795 exception
796 when others then
797 oraerrmesg := substr(SQLERRM, 1, 80);
798 Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
799 Fnd_Message.Set_Token('ERROR', oraerrmesg, FALSE);
800 Fnd_Message.Set_Token('ROUTINE',
801 'FND_CONCURRENT.GET_REQUEST_PRINT_OPTIONS', FALSE);
802 return counter;
803 end get_request_print_options;
804
805 --
806 -- Name
807 -- Check_Lock_Contention
808 -- Purpose
809 -- Identifies the process that is holding the lock(s) on resources
810 -- that are needed by the process identified by the Queue Name or
811 -- Session ID parameter.
812 --
813 function Check_Lock_Contention
814 (Queue_Name IN varchar2 default NULL,
815 Session_ID IN number default NULL,
816 UProcess_ID OUT NOCOPY number,
817 UTerminal OUT NOCOPY varchar2,
818 UNode OUT NOCOPY varchar2,
819 UName OUT NOCOPY varchar2,
820 UProgram OUT NOCOPY varchar2) return boolean is
821 C_SessionID number := Session_ID;
822 P_ProcessID number;
823 begin
824
825 UProcess_ID := NULL;
826 UTerminal := NULL;
827 UNode := NULL;
828 UName := NULL;
829 UProgram := NULL;
830
831 -- If session id is not null skip this step else
832 -- Get session id based on queue name.
833 --
834 if (C_SessionID is NULL ) then
835 Select Session_ID
836 into C_SessionID
837 from fnd_concurrent_processes cp,
838 fnd_concurrent_queues cq
839 where process_status_code = 'A'
840 and cp.Queue_Application_ID = cq.application_ID
841 and cp.concurrent_queue_id = cq.concurrent_queue_id
842 and cq.concurrent_queue_name = Queue_Name;
843
844 if (Sql%NotFound) then
845 Fnd_Message.Set_Name('FND', 'CP-Need valid manager name');
846 return FALSE;
847 end if;
848 end if;
849
850 -- Check for resource/locks that process with session id C_SessionID
851 -- is waiting for
852 --
853
854 select SH.OSUSER,
855 SH.PROCESS,
856 SH.MACHINE,
857 SH.TERMINAL,
858 SH.PROGRAM
859 into UName, UProcess_ID, UNode, UTerminal, UProgram
860 from V$SESSION SH,
861 V$LOCK LW,
862 V$LOCK LH,
863 V$SESSION SW
864 where LH.SID = SH.SID
865 and LH.SID <> SW.SID
866 and LH.ID1 = LW.ID1
867 and LH.ID2 = LW.ID2
868 and LW.KADDR = SW.LOCKWAIT
869 and SW.LOCKWAIT is not null
870 and SW.AUDSID = C_SessionID;
871
872 return TRUE;
873
874 exception
875 when no_data_found then
876 UProcess_ID := NULL;
877 UTerminal := NULL;
878 UNode := NULL;
879 UName := NULL;
880 UProgram := NULL;
881 return TRUE;
882
883 when others then
884 oraerrmesg := substr(SQLERRM, 1, 80);
885 Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
886 Fnd_Message.Set_Token('ERROR', oraerrmesg, FALSE);
887 Fnd_Message.Set_Token('ROUTINE',
888 'FND_CONCURRENT.Check_Lock_Contention', FALSE);
889 return FALSE;
890
891 end Check_Lock_Contention;
892
893 --
894 -- Name
895 -- get_program_attributes
896 -- Purpose
897 -- Returns the print options for a concurrent program.
898 --
899 -- Short term usage - only known users are from AP
900 function GET_PROGRAM_ATTRIBUTES
901 (appl_shortname IN varchar2 default NULL,
902 program IN varchar2 default NULL,
903 printer OUT NOCOPY varchar2,
904 style OUT NOCOPY varchar2,
905 save_output OUT NOCOPY varchar2) return boolean IS
906
907 ltype varchar2(8) := 'YES_NO';
908 begin
909 select PRINTER_NAME, user_printer_style_name, l.meaning
910 into printer, style, save_output
911 from fnd_concurrent_programs p, fnd_printer_styles_VL ps,
912 fnd_lookups L, fnd_application_vl A
913 where
914 l.lookup_code = p.SAVE_OUTPUT_FLAG
915 and l.lookup_type = ltype
916 and ps.printer_style_name = p.OUTPUT_PRINT_STYLE
917 and p.application_id = a.application_id
918 and p.concurrent_program_name = program
919 and a.application_short_name = appl_shortname;
920
921 if (Sql%NotFound) then
922 Fnd_Message.Set_Name('FND', 'CONC-Invalid Appl/Prog combo');
923 return FALSE;
924 end if;
925
926 return TRUE;
927
928 exception
929 when others then
930 oraerrmesg := substr(SQLERRM, 1, 80);
931 Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
932 Fnd_Message.Set_Token('ERROR', oraerrmesg, FALSE);
933 Fnd_Message.Set_Token('ROUTINE',
934 'FND_CONCURRENT.GET_PROGRAM_ATTRIBUTES', FALSE);
935 return FALSE;
939 -- Name
936 end get_program_attributes;
937
938 --
940 -- FND_CONCURRENT.SET_COMPLETION_STATUS
941 -- Purpose
942 -- Called from a concurrent request to set its completion
943 -- status and message.
944 --
945 -- Arguments (input)
946 -- status - 'NORMAL', 'WARNING', or 'ERROR'
947 -- message - Optional message
948 --
949 -- Returns:
950 -- TRUE on success. FALSE on error.
951 --
952
953 PROCEDURE init_request is
954 csid number; -- Auditing session ID
955 cpid number; -- Oracle process identifier
956 cspid varchar2(30); -- client process os pid
957 csspid varchar2(30); -- Shadow process os pid
958 codeset varchar2(30); -- NLS_CodeSet
959 optmode varchar2(30); -- Optimizer mode
960 que_rcg varchar2(32);
961 prg_rcg varchar2(32);
962 new_rcg varchar2(32);
963 old_rcg varchar2(32);
964 program_name varchar2(30);
965 plog varchar2(56);
966 pout varchar2(56);
967 pdir varchar2(255);
968 etstat varchar2(4) := Null;
969 etrace varchar2(4) := Null;
970 rtrace char;
971 ptrace char;
972 emethod varchar2(1);
973 ret_val boolean;
974 dbg_comp varchar2(30);
975 morg_cat varchar2(1) := null;
976 orgid number;
977 begin
978
979 if (fnd_global.conc_request_id > 0) then
980
981 Select P.PID, P.SPID, AUDSID, PROCESS,
982 substr(userenv('LANGUAGE'),
983 instr( userenv('LANGUAGE'), '.') + 1)
984 Into cpid, csspid, csid, cspid, codeset
985 From V$Session S, V$Process P
986 Where P.Addr = S.Paddr
987 and S.AUDSID = userenv('SESSIONID');
988
989 update fnd_concurrent_requests
990 set ORACLE_SESSION_ID = csid,
991 ORACLE_PROCESS_ID = csspid,
992 OS_PROCESS_ID = cspid,
993 NLS_CodeSet = codeset
994 where request_id = fnd_global.conc_request_id;
995
996 -- DOING COMMIT HERE SO THAT GUI TOOLS GET THE SESSION INFO
997 -- DURING PROGRAM EXECUTION.
998
999 commit;
1000
1001 begin
1002 select P.Optimizer_Mode, P.CONCURRENT_PROGRAM_NAME,
1003 upper(P.enable_Trace), upper(R.enable_trace),
1004 Decode(upper(P.ENABLE_TIME_STATISTICS),'Y','TRUE',NULL),
1005 execution_method_code,
1006 multi_org_category, org_id
1007 into optmode, program_name, ptrace, rtrace, etstat, emethod,
1008 morg_cat, orgid
1009 from FND_CONCURRENT_PROGRAMS P,
1010 FND_CONCURRENT_REQUESTS R
1011 WHERE P.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_ID
1012 And P.APPLICATION_ID = R.Program_APPLICATION_ID
1013 And R.request_id = fnd_global.conc_request_id;
1014 exception
1015 when others then
1016 optmode := null;
1017 program_name := null;
1018 end;
1019
1020 mo_global.init('M');
1021 -- initialize Multi-Org Context
1022 if ( morg_cat = 'S' ) then
1023 mo_global.set_policy_context(morg_cat, orgid);
1024 elsif ( morg_cat = 'M' ) then
1025 mo_global.set_policy_context(morg_cat, null);
1026 end if;
1027
1028 Select plsql_log, plsql_out, plsql_dir
1029 Into plog, pout, pdir
1030 From Fnd_Concurrent_Processes P, Fnd_Concurrent_Requests R
1031 Where P.Concurrent_Process_ID = R.Controlling_Manager
1032 And R.Request_ID = fnd_global.conc_request_id;
1033
1034 fnd_file.put_names(plog, pout, pdir);
1035 fnd_file_private.put_names(plog, pout, pdir);
1036
1037 if ( ptrace = 'Y' OR rtrace = 'Y') then
1038 etrace := 'TRUE';
1039 end if;
1040
1041 /* bug 3657332 */
1042 if ( optmode <> 'FIRST_ROWS' ) then
1043 optmode := NULL;
1044 end if;
1045
1046 FND_CTL.FND_SESS_CTL(Null, optmode, etrace, etstat, Null, Null);
1047
1048 dbms_application_info.set_module(program_name,
1049 'Concurrent Request');
1050 begin
1051 select Q.RESOURCE_CONSUMER_GROUP
1052 into que_rcg
1053 from fnd_concurrent_requests r,
1054 fnd_concurrent_processes p,
1055 fnd_concurrent_queues q
1056 where R.request_id = fnd_global.conc_request_id
1057 and R.controlling_manager = P.concurrent_process_id
1058 and Q.CONCURRENT_QUEUE_ID= P.CONCURRENT_QUEUE_ID
1059 and Q.APPLICATION_ID = P.QUEUE_APPLICATION_ID;
1060 exception
1061 when others then
1062 que_rcg := null;
1063 end;
1064
1065 begin
1066 select p.RESOURCE_CONSUMER_GROUP
1067 into prg_rcg
1068 from fnd_concurrent_programs P,
1069 fnd_concurrent_requests R
1070 where R.request_id = fnd_global.conc_request_id
1071 and r.PROGRAM_APPLICATION_ID = P.APPLICATION_ID
1072 and R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID;
1076 end;
1073 exception
1074 when others then
1075 prg_rcg := null;
1077
1078 if prg_rcg is not null then
1079 new_rcg := prg_rcg;
1080 elsif que_rcg is not null then
1081 new_rcg := que_rcg;
1082 else
1083 new_rcg := 'DEFAULT_CONSUMER_GROUP';
1084 end if;
1085
1086 begin
1087 dbms_session.switch_current_consumer_group(new_rcg,
1088 old_rcg,false);
1089 exception when others then null;
1090 end;
1091
1092 begin
1093 dbg_comp := fnd_debug_rep_util.get_program_comp(emethod);
1094
1095 -- ignore return value from fnd_debug.
1096 ret_val := fnd_debug.enable_db_rules(comp_type => dbg_comp,
1097 comp_name => program_name,
1098 comp_appl_id => null,
1099 comp_id => null,
1100 req_id => fnd_global.conc_request_id);
1101 end;
1102 end if;
1103
1104 return;
1105
1106 exception
1107 when others then
1108 oraerrmesg := substr(SQLERRM, 1, 80);
1109 Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
1110 Fnd_Message.Set_Token('ERROR', oraerrmesg, FALSE);
1111 Fnd_Message.Set_Token('ROUTINE',
1112 'FND_CONCURRENT.INIT_REQUEST', FALSE);
1113 return;
1114
1115 end init_request;
1116
1117
1118 --
1119 -- Name
1120 -- FND_CONCURRENT.SET_PREFERRED_RBS
1121 -- Purpose
1122 -- Called from afpirq, etc to set the Rollback Segment associated with req.
1123 --
1124 -- Arguments (input)
1125 --
1126 -- Returns:
1127
1128 Procedure SET_PREFERRED_RBS is
1129
1130 RBS varchar2(101); -- Rollback segment.
1131 sql_stmt varchar2(101);
1132
1133 begin
1134 select P.Rollback_Segment
1135 into RBS
1136 from FND_CONCURRENT_PROGRAMS P,
1137 FND_CONCURRENT_REQUESTS R
1138 WHERE R.request_id = fnd_global.conc_request_id
1139 AND R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID
1140 And R.PROGRAM_APPLICATION_ID = P.APPLICATION_ID;
1141
1142 if RBS is not Null then
1143 sql_stmt := 'Set Transaction Use Rollback Segment '|| RBS;
1144 commit;
1145 execute immediate sql_stmt;
1146 end if;
1147
1148 return;
1149
1150 exception
1151 when others then
1152 return;
1153
1154 end SET_PREFERRED_RBS;
1155
1156 --
1157 -- Name
1158 -- Fnd_Concurrent.Reset_Context
1159 -- Purpose
1160 -- To reset/re-establish context that may have been lost due to commits
1161 --
1162 -- Arguments (input)
1163 --
1164 -- Returns:
1165
1166 function Reset_Context(Request_ID IN number default NULL) return boolean is
1167
1168 g_request_id number := NULL;
1169
1170 begin
1171 --
1172 -- Use request id from global context if one exists, else
1173 -- use the one provided by the caller (security hole?)
1174 --
1175 g_request_id := fnd_global.conc_request_id;
1176
1177 if (g_request_id is null) then
1178 g_request_id := Request_Id;
1179 end if;
1180
1181 Fnd_Concurrent.Set_Preferred_RBS;
1182
1183 return TRUE;
1184
1185 exception
1186 when others then
1187 return TRUE;
1188
1189 end Reset_Context;
1190
1191 -- Name
1192 -- FND_CONCURRENT.AF_COMMIT
1193 -- Purpose
1194 -- It does the commit and set the preferred rollback segment for the
1195 -- program. Call this routine only in the concurrent program context.
1196 --
1197 -- Arguments (input)
1198 --
1199 -- Returns:
1200
1201 Procedure AF_COMMIT is
1202 Begin
1203 -- do the commit first
1204 commit;
1205
1206 -- if the context is concurrent program then set the rollback segment to
1207 -- program preferred
1208 if (fnd_global.conc_request_id > 0) then
1209 fnd_concurrent.set_preferred_rbs;
1210 end if;
1211
1212 End AF_COMMIT;
1213
1214 -- Name
1215 -- FND_CONCURRENT.AF_ROLLBACK
1216 -- Purpose
1217 -- It does the rollback and set the preferred rollback segment for the
1218 -- program. Call this routine only in the concurrent program context.
1219 --
1220 -- Arguments (input)
1221 --
1222 -- Returns:
1223
1224 Procedure AF_ROLLBACK is
1225 Begin
1226 -- do the rollback first
1227 rollback;
1228
1229 -- if the context is concurrent program then set the rollback segment to
1230 -- program preferred
1231 if (fnd_global.conc_request_id > 0) then
1232 fnd_concurrent.set_preferred_rbs;
1233 end if;
1234
1235 End AF_ROLLBACK;
1236
1237 -- Name
1238 -- FND_CONCURRENT.SHUT_DOWN_PROCS
1239 -- Purpose
1240 -- Runs the pl/sql shutdown procedures stored in FND_EXECUTABLES
1241 -- with EXECUTION_METHOD_CODE = 'Z'.
1242 --
1243 -- Errors encountered during execution of stored procedures are
1244 -- logged to FND_EVENTS
1245
1246 procedure shut_down_procs is
1247 errbuf VARCHAR2(256);
1248
1252 begin
1249 CURSOR c IS
1250 SELECT execution_file_name from fnd_executables
1251 where execution_method_code = 'Z';
1253 FOR c_rec IN c LOOP
1254 begin
1255 savepoint startpoint;
1256 EXECUTE IMMEDIATE 'begin ' || c_rec.execution_file_name || '; end;';
1257 EXCEPTION
1258 -- if error arises we want to continue executing the rest of the
1259 -- shutdowns
1260 WHEN OTHERS THEN
1261 rollback to startpoint;
1262
1263 errbuf := SQLERRM;
1264 if ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1265 fnd_message.set_name('FND', 'CONC-SHUTDOWN_ACTION_FAILURE');
1266 fnd_message.set_token('PROCEDURE', c_rec.execution_file_name);
1267 fnd_message.set_token('REASON', errbuf);
1268 fnd_log.message(FND_LOG.LEVEL_ERROR, 'fnd.plsql.fnd_concurrent.shut_down_procs', FALSE);
1269 end if;
1270 end;
1271 END LOOP;
1272
1273 end shut_down_procs;
1274
1275 -- Name
1276 -- FND_CONCURRENT.SET_INTERIM_STATUS
1277 -- Purpose
1278 -- sets the requests phase_code, interim_status_code and completion_text
1279 -- this is used in Java Concurrent Programs.
1280 --
1281 -- Arguments (input)
1282 -- status - 'NORMAL', 'WARNING', or 'ERROR'
1283 -- message - Optional message
1284 --
1285 -- Returns:
1286 -- TRUE on success. FALSE on error.
1287 --
1288
1289 function set_interim_status (status IN varchar2,
1290 message IN varchar2) return boolean is
1291 scode varchar2(1);
1292 lmessage varchar2(2000);
1293 req_id number;
1294 ret_str varchar2(80) := null;
1295 begin
1296
1297 -- these status codes from the afcp.h.
1298
1299
1300 if (upper(status) = 'NORMAL') then
1301 scode := 'N';
1302 elsif (upper(status) = 'WARNING') then
1303 scode := 'G';
1304 elsif (upper(status) = 'ERROR') then
1305 scode := 'E';
1306 elsif (upper(status) = 'PAUSED') then
1307 scode := 'W';
1308 else
1309 fnd_message.set_name('FND', 'CONC-SCS BAD STATUS');
1310 fnd_message.set_token('STATUS', status);
1311 return FALSE;
1312 end if;
1313
1314
1315 if ( (lengthb(message) = 0) and scode in ('G','E')) then
1316 fnd_message.set_name('FND', 'CONC-REQ RETURNED NO MESG');
1317 lmessage := fnd_message.get;
1318 elsif( (lengthb(message) = 0) and scode in ('W')) then
1319 fnd_message.set_name('FND', 'CONC_REQ REQUEST INFO');
1320 lmessage := message;
1321 else
1322 lmessage := message;
1323 end if;
1324
1325 if ( lengthb(lmessage) > 240 ) then
1326 fnd_file.put_line( fnd_file.log, lmessage);
1327 end if;
1328
1329 req_id := fnd_global.conc_request_id;
1330
1331 ret_str := set_status_autonomous(req_id, scode, lmessage, TRUE);
1332
1333 -- if ret_str has some string that means some error otherwise return TRUE
1334 if ( nvl(lengthb(ret_str), 0 ) > 0 ) then
1335 Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
1336 Fnd_Message.Set_Token('ERROR', ret_str, FALSE);
1337 Fnd_Message.Set_Token('ROUTINE',
1338 'FND_CONCURRENT.SET_INTERIM_STATUS', FALSE);
1339 return FALSE;
1340 else
1341 return TRUE;
1342 end if;
1343
1344 end set_interim_status;
1345
1346 -- Name
1347 -- FND_CONCURRENT.GET_SUB_REQUESTS
1348 -- Purpose
1349 -- gets all sub-requests for a given request id. For each sub-request it
1350 -- provides request_id, phase,status, developer phase , developer status
1351 -- completion text.
1352 --
1353 -- Arguments (input)
1354 -- request_id - Request Id for which sub-requests are required.
1355 --
1356 -- Returns:
1357 -- Table FND_CONCURRENT.REQUESTS_TAB_TYPE.
1358 --
1359
1360 function get_sub_requests( p_request_id IN number)
1361 return requests_tab_type is
1362 CURSOR C1 is
1363 Select Request_Id, Completion_Text,
1364 Phase.Lookup_Code p_lookup_code,
1365 Status.Lookup_Code s_lookup_code,
1366 Phase.Meaning p_meaning, Status.Meaning s_meaning
1367 From Fnd_Concurrent_Requests R,
1368 Fnd_Concurrent_programs P,
1369 Fnd_Lookups Phase,
1370 Fnd_Lookups Status
1371 Where
1372 Phase.Lookup_Type = PHASE_LOOKUP_TYPE
1373 AND Phase.Lookup_Code = Decode(Status.Lookup_Code,
1374 'H', 'I',
1375 'S', 'I',
1376 'U', 'I',
1377 'M', 'I',
1378 R.Phase_Code) AND
1379 Status.Lookup_Type = STATUS_LOOKUP_TYPE AND
1380 Status.Lookup_Code =
1381 Decode(R.Phase_Code,
1382 'P', Decode(R.Hold_Flag, 'Y', 'H',
1383 Decode(P.Enabled_Flag, 'N', 'U',
1384 Decode(Sign(R.Requested_Start_Date - SYSDATE),1,'P',
1385 R.Status_Code))),
1386 'R', Decode(R.Hold_Flag, 'Y', 'S',
1390 R.Status_Code)
1387 Decode(R.Status_Code, 'Q', 'B',
1388 'I', 'B',
1389 R.Status_Code)),
1391 And (R.Concurrent_Program_Id = P.Concurrent_program_ID AND
1392 R.Program_Application_ID= P.Application_ID )
1393 And Parent_Request_Id = p_request_id;
1394 sub_reqs requests_tab_type;
1395 i number := 0;
1396 phase_code varchar2(30);
1397 status_code varchar2(30);
1398 Dev_Phase varchar2(30);
1399 Dev_Status varchar2(30);
1400 begin
1401 FOR c1_rec in C1 LOOP
1402 i := i + 1;
1403 sub_reqs(i).request_id := c1_rec.request_id;
1404 sub_reqs(i).phase := c1_rec.p_meaning;
1405 sub_reqs(i).status := c1_rec.s_meaning;
1406 sub_reqs(i).message := c1_rec.completion_text;
1407 phase_code := c1_rec.p_lookup_code;
1408 status_code := c1_rec.s_lookup_code;
1409
1410 get_dev_phase_status(phase_code, status_code, dev_phase, dev_status);
1411
1412 sub_reqs(i).dev_phase := Dev_Phase;
1413 sub_reqs(i).dev_status := Dev_Status;
1414 END LOOP;
1415
1416 return sub_reqs;
1417 end get_sub_requests;
1418
1419 -- Name
1420 -- FND_CONCURRENT.CHILDREN_DONE
1421 -- Purpose
1422 -- Examines all child requests of a given request id. Returns TRUE if
1423 -- all have completed. Does not consider grandchildren or parent
1424 --
1425 -- Arguments (input)
1426 -- Parent_Request_ID- Request Id for which sub-requests are required. Null
1427 -- will be interpreted as current req_id.
1428 -- Recursive_Flag - Shall we look for grandchildren, etc.?
1429 --
1430 -- Interval - If Timeout>0, then we sleep this many seconds
1431 -- between queries (default 60)
1432 -- Max_Wait - if > 0 and children not done, we will wait up to Max_Wait
1433 -- seconds before responding FALSE
1434
1435 function CHILDREN_DONE(Parent_Request_ID IN NUMBER default NULL,
1436 Recursive_Flag in varchar2 default 'N',
1437 Interval IN number default 60,
1438 Max_Wait IN number default 0) return boolean is
1439
1440 kount number;
1441 end_of_time date;
1442 time_left number;
1443 parent_req_id number;
1444 cursor kidslist(parent_id number) is
1445 Select request_id
1446 from fnd_concurrent_requests
1447 where parent_request_id = parent_id;
1448
1449 begin
1450 Select sysdate + (greatest(Max_Wait, 0)/86400)
1451 into end_of_time
1452 from dual;
1453
1454 Select NVL(Parent_Request_ID,FND_GLOBAL.CONC_REQUEST_ID)
1455 into parent_req_id
1456 from dual;
1457
1458 if (parent_req_id = -1) then return TRUE; end if;
1459
1460 LOOP
1461 Select count(*) into kount
1462 from fnd_concurrent_requests
1463 where parent_request_id = parent_req_id
1464 and phase_code <> 'C';
1465
1466 if (kount = 0) AND (Recursive_Flag = 'Y') then -- check for kids
1467 for kidreq in kidslist(parent_req_id) loop
1468 if (NOT CHILDREN_DONE(kidreq.request_id, 'Y', 0, 0) ) then
1469 kount := 1;
1470 exit;
1471 end if;
1472 end loop;
1473 end if;
1474
1475 /* if we haven't found one then exit */
1476 if (kount = 0) then return TRUE; end if;
1477
1478 /* otherwise return false if we have run out of time */
1479 select (end_of_time - sysdate) * 86400
1480 into time_left
1481 from dual;
1482
1483 if (time_left <= Interval) then return FALSE; end if;
1484
1485 /* If we still have time, take a nap before trying again */
1486 dbms_lock.sleep(Interval);
1487 end LOOP;
1488
1489 end CHILDREN_DONE;
1490
1491 procedure register_node( name varchar2, /* Max 30 bytes */
1492 platform_id number, /* Platform ID from BugDB */
1493 forms_tier varchar2, /* 'Y'/'N' */
1494 cp_tier varchar2, /* 'Y'/'N' */
1495 web_tier varchar2, /* 'Y'/'N' */
1496 admin_tier varchar2, /* 'Y'/'N' */
1497 p_server_id varchar2, /* ID of server */
1498 p_address varchar2, /* IP address of server */
1499 p_description varchar2,
1500 p_host_name varchar2 default null,
1501 p_domain varchar2 default null, /* description of server*/
1502 db_tier varchar2 default null, /* 'Y'/'N' */
1503 p_virtual_ip varchar2 default null /* Virtual IP */ )
1504
1505 is
1506
1507 kount number;
1508
1509 begin
1510 /*
1511 * Create node. If it already exists,then we'll Update instead
1512 */
1513 select count(*)
1514 into kount
1515 from fnd_nodes
1516 where upper(node_name) = upper(name);
1517
1518 if (kount = 0) then
1519 insert into fnd_nodes
1520 (node_id, node_name,
1521 support_forms, support_cp, support_web, support_admin,
1522 platform_code, created_by, creation_date,
1526 select
1523 last_updated_by, last_update_date, last_update_login,
1524 node_mode, server_id, server_address, description,
1525 host, domain,support_db, virtual_ip)
1527 fnd_nodes_s.nextval, name,
1528 forms_tier, cp_tier, web_tier, admin_tier,
1529 platform_id, 1, SYSDATE,
1530 1, SYSDATE, 0,
1531 'O', p_server_id, p_address, p_description,
1532 p_host_name, p_domain,db_tier, p_virtual_ip
1533 from dual;
1534 else
1535 /*
1536 * Node exists already.
1537 * Allow for the case where multiple APPL_TOPs
1538 * are installed on the same node for the same
1539 * Apps system, but for different tiers. Essentially,
1540 * we're performing an 'OR' of the flags in the table
1541 * and those passed into the procedure.
1542 */
1543 update fnd_nodes set
1544 description = p_description,
1545 support_forms = decode(forms_tier, 'Y', 'Y', support_forms),
1546 support_cp = decode(cp_tier, 'Y', 'Y', support_cp),
1547 support_web = decode(web_tier, 'Y', 'Y', support_web),
1548 support_admin = decode(admin_tier, 'Y', 'Y', support_admin),
1549 platform_code = platform_id,
1550 last_update_date = SYSDATE, last_updated_by = 1,
1551 host = p_host_name,
1552 domain = p_domain,
1553 support_db = decode(db_tier,'Y','Y',support_db)
1554 where upper(node_name) = upper(name);
1555
1556 -- If server_id is not null, update fnd_nodes.server_id.
1557 -- fnd_nodes.server_id can only be null if the application server node has been
1558 -- removed.
1559 if (p_server_id is not null) then
1560 update fnd_nodes
1561 set server_id = p_server_id
1562 where upper(node_name) = upper(name);
1563 end if;
1564
1565 -- If server_address is not null, update fnd_nodes.server_address.
1566 if (p_address is not null) then
1567 update fnd_nodes
1568 set server_address = p_address
1569 where upper(node_name) = upper(name);
1570 end if;
1571
1572 -- if p_virtual_ip is not null update the fnd_node.virtual_ip
1573 if (p_virtual_ip is not null) then
1574 update fnd_nodes
1575 set virtual_ip = p_virtual_ip
1576 where upper(node_name) = upper(name);
1577 end if;
1578 end if;
1579 end register_node;
1580
1581 -- Name
1582 -- Fnd_Concurrent.Get_Service_Instances
1583 -- Purpose
1584 -- Fetch all service instances defined for a Service type
1585 -- Returns the service instance identity along with it's current
1586 -- state (Active/Disabled/Inactive/Suspended/Transit )
1587 --
1588 -- Arguments (input)
1589 -- svc_handle - Developer name for the Service type
1590 --
1591 -- Returns:
1592 -- Table Fnd_Concurrent.Service_Instance_Tab_Type. A table size of 0
1593 -- indicates absence of any service instances for the specified service
1594 -- type
1595 --
1596
1597 function Get_Service_Instances(svc_handle IN VARCHAR2)
1598 return Service_Instance_Tab_Type is
1599
1600 svc_id number;
1601 CURSOR C1 is
1602 select APPLICATION_SHORT_NAME c_appl_short_name,
1603 CONCURRENT_QUEUE_NAME c_svc_name,
1604 RUNNING_PROCESSES c_run_procs,
1605 MAX_PROCESSES c_max_procs,
1606 CONTROL_CODE c_ctrl_code,
1607 ENABLED_FLAG c_enabled
1608 from FND_CONCURRENT_QUEUES fcq,
1609 FND_CP_SERVICES fcs,
1610 FND_APPLICATION fa
1611 where
1612 fcq.MANAGER_TYPE = to_char(fcs.SERVICE_id)
1613 and fcq.application_id = fa.application_id
1614 and fcs.SERVICE_ID = svc_id;
1615 svc_inst_inf Service_Instance_Tab_Type;
1616 i number := 0;
1617 user_error varchar2(255); -- to store translated file_error
1618
1619 begin
1620
1621 -- Validate service type
1622
1623 begin
1624 select SERVICE_ID
1625 into svc_id
1626 from FND_CP_SERVICES
1627 where SERVICE_HANDLE = upper(svc_handle);
1628 exception
1629 when no_data_found then
1630 fnd_message.set_name('FND', 'CONC-SM INVALID SVC HANDLE');
1631 fnd_message.set_token('HANDLE', svc_handle);
1632 user_error := substrb(fnd_message.get, 1, 255);
1633 raise_application_error(-20100, user_error);
1634 end;
1635
1636
1637 FOR c1_rec in C1 LOOP
1638 i := i + 1;
1639 svc_inst_inf(i).Service_Handle := upper(svc_handle);
1640 svc_inst_inf(i).Application := c1_rec.c_appl_short_name;
1641 svc_inst_inf(i).Instance_Name := c1_rec.c_svc_name;
1642
1643 svc_inst_inf(i).State := get_svc_state(c1_rec.c_enabled,
1644 c1_rec.c_ctrl_code,
1645 c1_rec.c_run_procs,
1646 c1_rec.c_max_procs);
1647 END LOOP;
1648
1649 return svc_inst_inf;
1650 end Get_Service_Instances;
1651
1652 -- Name
1653 -- Fnd_Concurrent.Get_Service_Processes
1654 -- Purpose
1658 -- appl_short_name - Application Short Name under which the service
1655 -- Fetch all service instance processes for a service instance
1656 --
1657 -- Arguments (input)
1659 -- - instance is registered
1660 -- svc_instance_name - Developer name for the service instance
1661 -- proc_state - Service process state
1662 --
1663 -- Application and Service Instance Name together can be used to locate
1664 -- all processes
1665 --
1666 -- Returns (Fnd_Concurrent.Service_Process_Tab_Type)
1667 -- Fnd_Concurrent.Service_Process_Tab_Type.
1668 -- CPID (Concurrent_Process_ID) - Can be used to address/act on the
1669 -- process
1670 -- Service_Parameters - To be used to target particular
1671 -- - service instances
1672 -- A table size of 0 indicates absence of any service processes
1673 -- for the specified service instance and state
1674 --
1675
1676 function Get_Service_Processes(appl_short_name IN varchar2,
1677 svc_instance_name IN varchar2,
1678 proc_state IN varchar2)
1679 return Service_Process_Tab_Type is
1680
1681 ltype varchar2(32) := 'CP_PROCESS_STATUS_CODE';
1682
1683 CURSOR C1 is
1684 select CONCURRENT_PROCESS_ID c_cpid,
1685 MEANING c_state,
1686 fcp.NODE_NAME c_node,
1687 fcp.SERVICE_PARAMETERS c_parameters
1688 from FND_CONCURRENT_QUEUES fcq,
1689 FND_CONCURRENT_PROCESSES fcp,
1690 FND_APPLICATION fa,
1691 FND_LOOKUP_VALUES_VL flv
1692 where
1693 fcp.QUEUE_APPLICATION_ID = fcq.APPLICATION_ID
1694 and fcp.CONCURRENT_QUEUE_ID = fcq.CONCURRENT_QUEUE_ID
1695 and fcq.APPLICATION_ID = fa.APPLICATION_ID
1696 and flv.LOOKUP_TYPE = ltype
1697 and flv.LOOKUP_CODE = fcp.PROCESS_STATUS_CODE
1698 and fa.APPLICATION_SHORT_NAME = upper(appl_short_name)
1699 and fcq.CONCURRENT_QUEUE_NAME = upper(svc_instance_name)
1700 and ((proc_state is not null)
1701 or (fcp.PROCESS_STATUS_CODE not in ('S', 'K', 'U')))
1702 and exists
1703 (select 1
1704 from fnd_lookup_values flv2
1705 where flv2.LOOKUP_TYPE = ltype
1706 and flv2.LOOKUP_CODE = fcp.PROCESS_STATUS_CODE
1707 and upper(flv2.meaning) =
1708 upper(nvl(proc_state,flv2.meaning)));
1709
1710 svc_proc_inf Service_Process_Tab_Type;
1711 i number := 0;
1712 user_error varchar2(255); -- to store translated file_error
1713
1714 begin
1715
1716 -- Validate service application and name
1717 begin
1718 select 0
1719 into i
1720 from FND_CONCURRENT_QUEUES fcq,
1721 FND_APPLICATION fa
1722 where fcq.APPLICATION_ID = fa.APPLICATION_ID
1723 and APPLICATION_SHORT_NAME = upper(appl_short_name)
1724 and upper(CONCURRENT_QUEUE_NAME) = upper(svc_instance_name);
1725 exception
1726 when no_data_found then
1727 fnd_message.set_name('FND', 'CONC-SM INVALID SVC INSTANCE');
1728 fnd_message.set_token('APPLICATION', appl_short_name);
1729 fnd_message.set_token('INSTANCE', svc_instance_name);
1730 user_error := substrb(fnd_message.get, 1, 255);
1731 raise_application_error(-20100, user_error);
1732 end;
1733
1734 FOR c1_rec in C1 LOOP
1735
1736 i := i + 1;
1737 svc_proc_inf(i).CPID := c1_rec.c_cpid;
1738 svc_proc_inf(i).State := c1_rec.c_state;
1739 svc_proc_inf(i).Node := c1_rec.c_node;
1740 svc_proc_inf(i).Parameters := c1_rec.c_parameters;
1741
1742 END LOOP;
1743
1744 return svc_proc_inf;
1745 end Get_Service_Processes;
1746
1747 -- Name
1748 -- FND_CONCURRENT.MSC_MATCH_BY_SERVICE_TYPE
1749 -- Purpose
1750 -- internal function used to find matches of svc cntl requests and
1751 -- Service types. returns 1 for a match, 0 for no.
1752
1753 function MSC_MATCH_BY_SERVICE_TYPE(requestid number, mtype number)
1754 return number is
1755
1756 rarg1 number;
1757 rarg2 number;
1758 rarg3 number;
1759 kount number;
1760
1761 begin
1762 /* get request arguments. Last sane thing we do before the arbitrary
1763 ugly piece of coding where we decipher if we have a hit */
1764
1765 select argument1, argument2, argument3
1766 into rarg1, rarg2, rarg3
1767 from fnd_concurrent_requests R
1768 where requestid = R.request_id;
1769
1770 /* CASE positive : old style requests */
1771 if (rarg1 >= 0) then
1772
1773 select count(concurrent_queue_id)
1774 into kount
1775 from fnd_concurrent_queues
1776 where concurrent_queue_id = rarg1
1777 and application_id = rarg2
1778 and manager_type = mtype;
1779
1783
1780 if ((kount > 0) or (rarg1 = 1)) then return 1;
1781 else return 0;
1782 end if;
1784
1785 /* CASE -1 : By app id */
1786 elsif ((rarg1 = -1) and (
1787 /* either CM or TM and request is for mgrs (or both) */
1788 (((mtype = 1) or (mtype = 3)) and ((rarg3 = 0) or (rarg3 = 2)))
1789 or /* or service and request is for services (or both) */
1790 ((mtype > 999) and ((rarg3 = 1) or (rarg3 = 2)))
1791 )) then
1792
1793
1794 select count(concurrent_queue_id)
1795 into kount
1796 from fnd_concurrent_queues
1797 where application_id = rarg2
1798 and manager_type = mtype;
1799
1800 if (kount > 0) then return 1;
1801 else return 0;
1802 end if;
1803
1804
1805
1806 elsif (rarg1 = -1) then
1807 return 0;
1808
1809 /* CASE -2 : cp fun pak */
1810 elsif ((rarg1 = -2) and (mtype > 0) and (mtype < 6) and (mtype <> 2)) then
1811 return 1;
1812 elsif (rarg1 = -2) then
1813 return 0;
1814
1815 /* CASE -3 : By service type */
1816 elsif ((rarg1 = -3) and ((mtype = rarg2) or (rarg2 = 0))) then
1817 return 1;
1818 elsif (rarg1 = -3) then
1819 return 0;
1820
1821 /* CASE -4 : cp all */
1822 elsif ((rarg1 = -4) and (mtype < 1000)) then
1823 return 1;
1824 elsif (rarg1 = -4) then
1825 return 0;
1826
1827 else -- for expansion...we'll assume no match.
1828 return 0;
1829 end if;
1830
1831 end MSC_MATCH_BY_SERVICE_TYPE;
1832
1833 -- Name
1834 -- FND_CONCURRENT.MSC_MATCH
1835 -- Purpose
1836 -- internal function used to find matches of svc cntl requests and services
1837 -- or managers. returns 1 for a match, 0 for no.
1838
1839 function MSC_MATCH(requestid number,
1840 app_id number, que_id number, mtype number) return number is
1841
1842 rarg1 number;
1843 rarg2 number;
1844 rarg3 number;
1845
1846 begin
1847 if (que_id is null) then
1848 /* Deal with special case required for OAM */
1849 return MSC_MATCH_BY_SERVICE_TYPE(requestid, mtype);
1850 end if;
1851
1852 /* get request arguments. Last sane thing we do before the arbitrary
1853 ugly piece of coding where we decipher if we have a hit */
1854
1855 select argument1, argument2, argument3
1856 into rarg1, rarg2, rarg3
1857 from fnd_concurrent_requests R
1858 where requestid = R.request_id;
1859
1860 /* CASE positive : old style requests */
1861 if ((rarg1 >= 0) and (rarg1 = que_id) and (rarg2 = app_id)) then
1862 return 1;
1863 elsif ((rarg1 >= 0) and (rarg1 = 1) and (rarg2 = 0)) then
1864 return 1;
1865 elsif (rarg1 >= 0) then
1866 return 0;
1867
1868 /* CASE -1 : By app id */
1869 elsif ((rarg1 = -1) and (rarg2 = app_id) and (
1870 /* either CM or TM and request is for mgrs (or both) */
1871 (((mtype = 1) or (mtype = 3)) and ((rarg3 = 0) or (rarg3 = 2)))
1872 or /* or service and request is for services (or both) */
1873 ((mtype > 999) and ((rarg3 = 1) or (rarg3 = 2)))
1874 )) then return 1;
1875 elsif (rarg1 = -1) then
1876 return 0;
1877
1878 /* CASE -2 : cp fun pak */
1879 elsif ((rarg1 = -2) and (mtype > 0) and (mtype < 6) and (mtype <> 2)) then
1880 return 1;
1881 elsif (rarg1 = -2) then
1882 return 0;
1883
1884 /* CASE -3 : By service type */
1885 elsif ((rarg1 = -3) and (mtype = rarg2)) then
1886 return 1;
1887 elsif (rarg1 = -3) then
1888 return 0;
1889
1890 /* CASE -4 : cp all */
1891 elsif ((rarg1 = -4) and (mtype < 1000) ) then
1892 return 1;
1893 elsif (rarg1 = -4) then
1894 return 0;
1895
1896 else -- for expansion...we'll assume no match.
1897 return 0;
1898 end if;
1899
1900 end MSC_MATCH;
1901 -- Name
1902 -- FND_CONCURRENT.find_pending_svc_ctrl_reqs
1903 -- Purpose
1904 -- gets all pending service control requests for a given service or service
1905 -- instance. Returns number of requests found and has an out parameter
1906 -- containing a comma delimited list of matching requests.
1907 --
1908 -- Arguments (input)
1909 -- service_id - Service ID of service in which we are interested.
1910 -- (Set to null if this doesn't matter)
1911 -- service_inst_id - Service instance ID of svc in which we are interested.
1912 -- (Set to null if this doesn't matter)
1913 -- request_list - Comma delimited list of matching request ids.
1914 --
1915 -- Returns:
1916 -- Number of matching requests.
1917 --
1918
1919
1920 function find_pending_svc_ctrl_reqs(service_id in number,
1921 service_inst_id in number,
1922 req_list out NOCOPY varchar2) return number is
1923
1924 app_id number := null;
1925 kount number := 0;
1926 my_service_id number := service_id;
1927
1928 Cursor C1 IS
1929 Select request_id
1930 from fnd_concurrent_requests R, fnd_concurrent_programs P
1931 where r.phase_code = 'P'
1935 and msc_match(request_id, app_id, service_inst_id, my_service_id) = 1
1932 and p.application_id = r.PROGRAM_APPLICATION_ID
1933 and p.concurrent_program_id = r.concurrent_program_id
1934 and p.queue_control_flag = 'Y'
1936 order by request_id;
1937
1938 begin
1939 req_list := '';
1940
1941 If ((service_id is null) and (service_inst_id is null)) then
1942 /* all null case */
1943 return 0;
1944 elsif (service_id is null) then
1945 /* populate app and service id */
1946 select manager_type, application_id
1947 into my_service_id, app_id
1948 from fnd_concurrent_queues
1949 where concurrent_queue_id = service_inst_id;
1950 end if;
1951
1952 /* find results */
1953 For C1REC in C1 Loop
1954 kount := kount + 1;
1955
1956 if (kount = 0) then
1957 req_list := to_char(C1REC.request_id);
1958 else
1959 req_list := req_list || ',' || to_char(C1REC.request_id);
1960 end if;
1961 End Loop;
1962
1963 return kount;
1964 end find_pending_svc_ctrl_reqs;
1965
1966
1967 -- Name
1968 -- FND_CONCURRENT.Find_SC_Conflict
1969 -- Purpose
1970 -- Finds later conflicting service control request (if any) for another
1971 -- service control request.
1972 --
1973 -- Arguments (input)
1974 -- reqid - request id we are interested in.
1975 --
1976 -- Returns:
1977 -- Request ID of a conflicting request, or -1 if none exist.
1978
1979 Function Find_SC_Conflict(reqid in number) return number is
1980 app_id number := null;
1981 kount number := 0;
1982
1983 Cursor C1 IS
1984 Select R2.request_id
1985 from fnd_concurrent_requests R1,
1986 fnd_concurrent_requests R2,
1987 fnd_concurrent_programs P1,
1988 fnd_concurrent_programs P2,
1989 fnd_concurrent_queues Q,
1990 fnd_application A
1991 where r1.request_id = reqid
1992 and P1.APPLICATION_ID = R1.PROGRAM_APPLICATION_ID
1993 and P1.concurrent_program_id = R1.concurrent_program_id
1994 and p1.queue_control_flag = 'Y'
1995 and r2.request_id > r1.request_id
1996 and P2.APPLICATION_ID = R2.PROGRAM_APPLICATION_ID
1997 and P2.concurrent_program_id = R2.concurrent_program_id
1998 and P2.concurrent_program_id <> 2
1999 and P2.concurrent_program_id <> 6
2000 and p2.queue_control_flag = 'Y'
2001 AND a.application_id = p2.application_id
2002 AND a.application_short_name = 'FND'
2003 and msc_match(reqid,
2004 Q.application_id, Q.concurrent_queue_id, Q.manager_type) +
2005 msc_match(R2.request_id,
2006 Q.application_id, Q.concurrent_queue_id, Q.manager_type) = 2
2007 order by R2.request_id;
2008
2009 begin
2010 For C1REC in C1 Loop
2011 return C1REC.request_id;
2012 End Loop;
2013
2014 return -1;
2015 end Find_SC_Conflict;
2016
2017 -- Private Function
2018 Function private_check_for_sctl_done(goal_state varchar2,
2019 rarg1 number, rarg2 number, rarg3 number) return number is
2020
2021 kount number;
2022
2023 begin
2024 /* CASE positive : old style requests */
2025 if (rarg1 >= 0) then
2026 select count(concurrent_queue_id)
2027 into kount
2028 from fnd_concurrent_queues
2029 where concurrent_queue_id = rarg1
2030 and application_id = rarg2
2031 and ((Running_processes <> MAX_PROCESSES)
2032 or ((goal_state is not null) and
2033 ((CONTROL_CODE <> goal_state) or (CONTROL_CODE is null)))
2034 or ((goal_state is null) and (CONTROL_CODE is null)));
2035
2036
2037 /* CASE -1 : By app id */
2038 elsif (rarg1 = -1) then
2039 select count(concurrent_queue_id)
2040 into kount
2041 from fnd_concurrent_queues
2042 where application_id = rarg2
2043 and (
2044 /* either CM or TM and request is for mgrs (or both) */
2045 (((manager_type = 1) or (manager_type = 3))
2046 and ((rarg3 = 0) or (rarg3 = 2)))
2047 or /* or service and request is for services (or both) */
2048 ((manager_type > 999) and ((rarg3 = 1) or (rarg3 = 2))))
2049 and ((Running_processes <> MAX_PROCESSES)
2050 or ((goal_state is not null) and
2051 ((CONTROL_CODE <> goal_state) or (CONTROL_CODE is null)))
2052 or ((goal_state is null) and (CONTROL_CODE is null)));
2053
2054 /* CASE -2 : cp fun pak */
2055 elsif (rarg1 = -2) then
2056 select count(concurrent_queue_id)
2057 into kount
2058 from fnd_concurrent_queues
2059 where manager_type IN ('1', '3', '4', '5')
2060 and ((Running_processes <> MAX_PROCESSES)
2061 or ((goal_state is not null) and
2062 ((CONTROL_CODE <> goal_state) or (CONTROL_CODE is null)))
2063 or ((goal_state is null) and (CONTROL_CODE is null)));
2064
2065
2066 /* CASE -3 : By service type */
2067 elsif (rarg1 = -3) then
2068 select count(concurrent_queue_id)
2069 into kount
2070 from fnd_concurrent_queues
2071 where manager_type = to_char(rarg2)
2072 and ((Running_processes <> MAX_PROCESSES)
2076
2073 or ((goal_state is not null) and
2074 ((CONTROL_CODE <> goal_state) or (CONTROL_CODE is null)))
2075 or ((goal_state is null) and (CONTROL_CODE is null)));
2077 /* CASE -4 : cp all */
2078 elsif (rarg1 = -4) then
2079 select count(concurrent_queue_id)
2080 into kount
2081 from fnd_concurrent_queues
2082 where manager_type < 1000
2083 and ((Running_processes <> MAX_PROCESSES)
2084 or ((goal_state is not null) and
2085 ((CONTROL_CODE <> goal_state) or (CONTROL_CODE is null)))
2086 or ((goal_state is null) and (CONTROL_CODE is null)));
2087
2088 else -- for expansion...we'll assume not complete.
2089 kount := 1;
2090 end if;
2091
2092 if (kount > 0) then return 0;
2093 else return 1; end if;
2094
2095 end private_check_for_sctl_done;
2096
2097
2098
2099 -- Name
2100 -- FND_CONCURRENT.Function Wait_for_SCTL_Done
2101 -- Purpose
2102 -- Waits for Svc Ctrl request to finish, or another conflicting request,
2103 -- or timeout.
2104 --
2105 -- Arguments (input)
2106 -- reqid - request id we are interested in.
2107 --
2108 -- timeout - timeout in seconds;
2109 --
2110 -- Returns:
2111 -- Number -
2112 -- - 1: request not found.
2113 -- - 2: request is not a supported type.
2114 -- - 3: request has not run before timeout.
2115 -- - 4: later request conflicts with this request.
2116 -- - 5: request has run, but not complete before timeout.
2117 -- - 6: requested actions have completed.
2118 --
2119 -- Supporting routines:
2120 -- For readability the following functions are available to compare to
2121 -- result:
2122
2123 Function Wait_for_SCTL_Done(reqid in number,timeout in number)return number is
2124 mystart date;
2125 done number := -1; -- -1 = unrun, 0 = run, 2 = finished
2126 timesup number;
2127 r_app_id number;
2128 prog_id number;
2129 r_phase varchar2(1);
2130 rarg1 number;
2131 rarg2 number;
2132 rarg3 number;
2133 goal_state varchar2(1);
2134
2135 begin
2136 /* start time */
2137 select sysdate
2138 into mystart
2139 from dual;
2140
2141 /* get request info */
2142 begin
2143 select concurrent_program_id, program_application_id, phase_code,
2144 argument1, argument2, argument3, Decode(concurrent_program_id,
2145 0,null, 1,'E', 3, null, 4, 'X', 5, 'E', 7, 'P', 8, null, null)
2146 into prog_id, r_app_id, r_phase, rarg1, rarg2, rarg3, goal_state
2147 from fnd_concurrent_requests
2148 where request_id = reqid;
2149 exception
2150 when others then
2151 return SCTL_REQ_NOT_FOUND;
2152 end;
2153
2154 /* take out the trash */
2155 if ((r_app_id <> 0) OR (prog_id = 2) OR (prog_id = 6) OR (prog_id < 0) OR
2156 (prog_id > 8)) then
2157 return SCTL_REQ_NOT_SUPPD;
2158 end if;
2159
2160 LOOP
2161 /* see if we finished */
2162 if (Done = 0) then -- request ran...let's see if everything done
2163 Done := private_check_for_sctl_done(goal_state, rarg1, rarg2, rarg3);
2164 end if;
2165
2166 /* are we done? */
2167 if (Done = 1) then
2168 return SCTL_REQ_COMPLETED;
2169 end if;
2170
2171 /* Let's see if there is a reason that we are not done */
2172 if (Done = 0) then
2173 if (Find_SC_Conflict(reqid) > -1) then
2174 return SCTL_REQ_CONFLICTS;
2175 end if;
2176 end if;
2177
2178 /* time out? */
2179 select ((sysdate - mystart) * 86400) - Timeout
2180 into timesup
2181 from dual;
2182
2183 /* have we run yet? */
2184 select decode(phase_code, 'C', 0, -1)
2185 into Done
2186 from fnd_concurrent_requests
2187 where request_id = reqid;
2188
2189 if ((timesup > 0) and (Done = 0)) then
2190 return SCTL_TIMEOUT_NOT_C;
2191 elsif (timesup > 0) then
2192 return SCTL_TIMEOUT_NOT_R;
2193 end if;
2194
2195 dbms_lock.sleep(5);
2196 END LOOP;
2197
2198 end Wait_for_SCTL_Done;
2199
2200 Function SCTL_REQ_NOT_FOUND return number is
2201 begin
2202 return 1;
2203 end SCTL_REQ_NOT_FOUND;
2204
2205 Function SCTL_REQ_NOT_SUPPD return number is
2206 begin
2207 return 2;
2208 end SCTL_REQ_NOT_SUPPD;
2209
2210 Function SCTL_TIMEOUT_NOT_R return number is
2211 begin
2212 return 3;
2213 end SCTL_TIMEOUT_NOT_R;
2214
2215 Function SCTL_REQ_CONFLICTS return number is
2216 begin
2217 return 4;
2218 end SCTL_REQ_CONFLICTS;
2219
2220 Function SCTL_TIMEOUT_NOT_C return number is
2221 begin
2222 return 5;
2223 end SCTL_TIMEOUT_NOT_C;
2224
2225 Function SCTL_REQ_COMPLETED return number is
2226 begin
2227 return 6;
2228 end SCTL_REQ_COMPLETED;
2229
2230
2231 -- Name
2232 -- FND_CONCURRENT.Wait_For_All_Down
2233 -- Purpose
2237 -- Timeout - in seconds.
2234 -- Waits for all services, managers, and icm to go down, or timesout.
2235 --
2236 -- Arguments (input)
2238 --
2239 -- Returns:
2240 -- True if all shut down, false for timeout.
2241
2242 Function Wait_For_All_Down(Timeout in number) return boolean is
2243
2244 mystart date;
2245 done number := -1;
2246 icm_tgt number;
2247 icm_act number;
2248 icm_pmon varchar2(255);
2249 icm_callstat number;
2250
2251 begin
2252
2253 select sysdate
2254 into mystart
2255 from dual;
2256
2257
2258 LOOP
2259 /* check to see if the icm is down */
2260 get_manager_status(0,1,icm_tgt,icm_act,icm_pmon,icm_callstat);
2261
2262 if ((icm_tgt = 0) and (icm_act = 0) and (icm_callstat = 0)) then
2263 return TRUE;
2264 end if;
2265
2266 /* time out? */
2267 select ((sysdate - mystart) * 86400) - Timeout
2268 into done
2269 from dual;
2270
2271 if (done > 0) then return FALSE;
2272 end if;
2273
2274 dbms_lock.sleep(5);
2275 END LOOP;
2276
2277 exception
2278 when others then
2279 oraerrmesg := substr(SQLERRM, 1, 80);
2280 Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
2281 Fnd_Message.Set_Token('ERROR', oraerrmesg, FALSE);
2282 Fnd_Message.Set_Token('ROUTINE',
2283 'FND_CONCURRENT.WAIT_FOR_ALL_DOWN', FALSE);
2284 return FALSE;
2285 end Wait_For_All_Down;
2286
2287
2288 -- Name
2289 -- FND_CONCURRENT.Build_Svc_Ctrl_Desc.
2290 -- Purpose
2291 -- Provides description text for svc ctrl request based on args.
2292 --
2293 -- Arguments (input)
2294 -- Arg1, Arg2, Arg3 - request arguments for svc ctrl request.
2295 --
2296 -- Returns:
2297 -- Description of Request
2298
2299 Function Build_Svc_Ctrl_Desc(Arg1 in number,
2300 Arg2 in number,
2301 Arg3 in number,
2302 Prog in varchar2
2303 ) return varchar2 is
2304
2305
2306 action varchar2(255) := Prog;
2307 descr varchar2(255) := null;
2308 detail varchar2(255) := null;
2309
2310 begin
2311 begin
2312 select cp.USER_CONCURRENT_PROGRAM_NAME
2313 into action
2314 from fnd_concurrent_programs_vl cp, fnd_application a
2315 where cp.concurrent_program_name = prog
2316 AND cp.application_id = a.application_id
2317 AND a.application_short_name = 'FND';
2318 exception when others then
2319 null;
2320 end;
2321
2322 if (Arg1 >=0) then
2323
2324 begin
2325 select USER_CONCURRENT_QUEUE_NAME
2326 into Detail
2327 from fnd_concurrent_queues_vl
2328 where APPLICATION_ID = Arg2
2329 and concurrent_queue_id = Arg1;
2330 exception
2331 when others then
2332 Detail := 'QID = ' || to_char(Arg1);
2333 end;
2334
2335 Fnd_Message.Set_Name('FND', 'CP-MultiQC OldStyle');
2336 Fnd_Message.Set_Token('ACTION', action, FALSE);
2337 Fnd_Message.Set_Token('QUEUE', detail, FALSE);
2338 Descr := Fnd_Message.get;
2339
2340 return Descr;
2341
2342 elsif (Arg1 = -1) then
2343 begin
2344 select APPLICATION_NAME
2345 into Detail
2346 from fnd_application_vl
2347 where APPLICATION_ID = Arg2;
2348 exception
2349 when others then
2350 Detail := 'APPID = ' || to_char(Arg2);
2351 end;
2352
2353 Fnd_Message.Set_Name('FND', 'CP-MultiQC -1 ' || to_char(Arg3));
2354 Fnd_Message.Set_Token('ACTION', action, FALSE);
2355 Fnd_Message.Set_Token('APP', detail, FALSE);
2356 Descr := Fnd_Message.get;
2357
2358 return Descr;
2359
2360 elsif (Arg1 = -2) then
2361
2362 Fnd_Message.Set_Name('FND', 'CP-MultiQC -2');
2363 Fnd_Message.Set_Token('ACTION', action, FALSE);
2364 Descr := Fnd_Message.get;
2365
2366 return Descr;
2367
2368 elsif (Arg1 = -3) then
2369 begin
2370 select SERVICE_NAME
2371 into Detail
2372 from fnd_cp_services_vl
2373 where SERVICE_ID = Arg2;
2374 exception
2375 when others then
2376 Detail := 'SVCID = ' || to_char(Arg2);
2377 end;
2378
2379 Fnd_Message.Set_Name('FND', 'CP-MultiQC -3');
2380 Fnd_Message.Set_Token('ACTION', action, FALSE);
2381 Fnd_Message.Set_Token('SVC', detail, FALSE);
2382 Descr := Fnd_Message.get;
2383
2384 return Descr;
2385
2386 elsif (Arg1 = -4) then
2387
2388 Fnd_Message.Set_Name('FND', 'CP-MultiQC -4');
2389 Fnd_Message.Set_Token('ACTION', action, FALSE);
2390 Descr := Fnd_Message.get;
2391
2392 return Descr;
2393
2394
2395 else
2396 return Null;
2397 end if;
2398
2402 -- FND_CONCURRENT.Cancel_Request.
2399 end Build_Svc_Ctrl_Desc;
2400
2401 -- Name
2403 -- Purpose
2404 -- It Cancels given Concurrent Request.
2405 --
2406 -- Arguments (input)
2407 -- request_id - request id of the request you want to cancel.
2408 --
2409 -- (out args)
2410 -- message - API will fill the message with any errors while canceling
2411 -- request.
2412 --
2413 -- Returns:
2414 -- Returns TRUE if success or FALSE on failure.
2415 Function Cancel_Request( Request_Id in NUMBER,
2416 Message out NOCOPY VARCHAR2) return boolean is
2417 ret_val number;
2418 submitter number;
2419 request_missing exception;
2420 no_privilege exception;
2421
2422 begin
2423 -- check this user got privilege to cancel request
2424 begin
2425 Select Requested_By
2426 into submitter
2427 from fnd_concurrent_requests
2428 where request_id = Cancel_Request.request_id;
2429 exception
2430 when no_data_found then
2431 raise request_missing;
2432 end;
2433
2434 if ( submitter <> fnd_global.user_id ) then
2435 raise no_privilege;
2436 end if;
2437
2438 ret_val := FND_AMP_PRIVATE.cancel_request(request_id, message);
2439
2440 if ( ret_val = 0 ) then
2441 return FALSE;
2442 elsif ( ret_val = 1 ) then
2443 fnd_message.set_name('FND', 'CONC-Could not lock Request');
2444 message := fnd_message.get;
2445 return FALSE;
2446 elsif ( ret_val = 2 ) then
2447 fnd_message.set_name('FND', 'CONC-request completed');
2448 message := fnd_message.get;
2449 return FALSE;
2450 elsif ( ret_val = 3 ) then
2451 fnd_message.set_name('FND', 'CONC-cannot cancel mgr dead');
2452 message := fnd_message.get;
2453 return FALSE;
2454 elsif ( ret_val in (4,5)) then
2455 return TRUE;
2456 end if;
2457
2458 exception
2459 when request_missing then
2460 fnd_message.set_name('FND', 'CONC-MISSING REQUEST');
2461 fnd_message.set_token('ROUTINE', 'FND_CONCURRENT.CANCEL_REQUEST');
2462 fnd_message.set_token('REQUEST', to_char(request_id));
2463 message := fnd_message.get;
2464 return FALSE;
2465 when no_privilege then
2466 fnd_message.set_name('FND', 'CONC-NOT OWNER OF REQUEST');
2467 fnd_message.set_token('REQUEST', to_char(request_id));
2468 message := fnd_message.get;
2469 return FALSE;
2470 when others then
2471 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
2472 fnd_message.set_token('ROUTINE', 'FND_CONCURRENT.CANCEL_REQUEST');
2473 fnd_message.set_token('ERRNO', SQLCODE);
2474 fnd_message.set_token('REASON', SQLERRM);
2475 message := fnd_message.get;
2476 return FALSE;
2477
2478 end Cancel_Request;
2479
2480 --
2481 -- Name
2482 -- FND_CONCURRENT.get_resource_lock
2483 -- Purpose
2484 -- It gets an exclusive lock for a given resource or task name.
2485 --
2486 -- Arguments (input)
2487 -- Resource_name - Name of the resource that uniquely identifies
2488 -- in the system.
2489 -- timeout - Number of seconds to continue trying to grant the lock
2490 -- default is 2 seconds.
2491 --
2492 -- Returns:
2493 -- 0 - Success
2494 -- 1 - Timeout
2495 -- 2 - Deadlock
2496 -- 3 - Parameter error
2497 -- 4 - Already own lock specified by lockhandle
2498 -- 5 - Illegal lock handle
2499 -- -1 - Other exceptions, get the message from message stack for reason.
2500
2501 function get_resource_lock ( resource_name in varchar2,
2502 timeout in number default 2 )
2503 return number is
2504 hndl varchar2(128);
2505 lk varchar2(128);
2506 result number := -1;
2507 begin
2508
2509 lk := 'FND$_' || resource_name;
2510
2511 dbms_lock.allocate_unique( lockname => lk,
2512 lockhandle => hndl );
2513
2514 result := dbms_lock.request( lockhandle => hndl,
2515 lockmode => 6,
2516 timeout => timeout );
2517
2518 return result;
2519
2520 exception
2521 when others then
2522 fnd_message.set_name ('FND', 'CP-Generic oracle error');
2523 fnd_message.set_token ('ERROR', substr (sqlerrm, 1, 30), FALSE);
2524 fnd_message.set_token ('ROUTINE', 'fnd_concurrent.get_resource_lock',
2525 FALSE);
2526 return result;
2527
2528 end;
2529
2530 --
2531 -- Name
2532 -- FND_CONCURRENT.release_resource_lock
2533 -- Purpose
2534 -- It releases an exclusive lock for a given resource or task name.
2535 --
2536 -- Arguments (input)
2537 -- Resource_name - Name of the resource that uniquely identifies
2538 -- in the system.
2539 --
2540 -- Returns:
2541 -- 0 - Success
2542 -- 3 - Parameter Error
2543 -- 4 - Do not own lock
2544 -- 5 - Illegal lock handle
2545 -- -1 - Other exceptions, get the message from message stack for reason.
2549 lk varchar2(128);
2546
2547 function release_resource_lock ( resource_name in varchar2 ) return number is
2548 hndl varchar2(128);
2550 result number := -1;
2551 begin
2552
2553 lk := 'FND$_' || resource_name;
2554
2555 dbms_lock.allocate_unique( lockname => lk,
2556 lockhandle => hndl );
2557
2558 result := dbms_lock.release( lockhandle => hndl );
2559
2560 return result;
2561
2562 exception
2563 when others then
2564 fnd_message.set_name ('FND', 'CP-Generic oracle error');
2565 fnd_message.set_token ('ERROR', substr (sqlerrm, 1, 30), FALSE);
2566 fnd_message.set_token ('ROUTINE', 'fnd_concurrent.release_resource_lock',
2567 FALSE);
2568 return result;
2569
2570 end;
2571
2572
2573
2574
2575
2576
2577
2578 --
2579 -- Name
2580 -- FND_CONCURRENT.INIT_SQL_REQUEST
2581 -- Purpose
2582 -- Called for all SQL*PLUS concurrent requests to perform request initialization.
2583 --
2584
2585 procedure init_sql_request is
2586
2587 creqid number;
2588 session_id number;
2589 userid number;
2590 respid number;
2591 respappid number;
2592 secgrpid number;
2593 siteid number;
2594 loginid number;
2595 cloginid number;
2596 progappid number;
2597 cprogid number;
2598 cprireqid number;
2599 counter number := 0;
2600
2601 lpid v$session.process%type;
2602 lmachine v$session.machine%type;
2603 position number;
2604 begin
2605
2606 -- Select all the information needed for this request from fnd_concurrent_requests,
2607 -- using the fnd_cp_sql_requests table.
2608 -- A row should have been inserted earlier in usdspid, containing the current request id,
2609 -- machine name, and process id.
2610 -- By joining these tables with v$session, we can pull out all the information we need,
2611 -- using only our own session id.
2612 begin
2613
2614
2615
2616 select process, machine
2617 into lpid, lmachine
2618 from v$session
2619 where audsid = userenv('sessionid');
2620
2621 position := instr(lpid,':');
2622 if ( position>0 ) then
2623 lpid := substr(lpid,1,position-1);
2624 end if;
2625
2626 while counter <= 4 loop
2627 counter := counter + 1;
2628 begin
2629 select 0, fcr.requested_by,
2630 fcr.responsibility_id, fcr.responsibility_application_id,
2631 fcr.security_group_id, 0,
2632 fcr.requested_by, fcr.conc_login_id,
2633 fcr.program_application_id, fcr.concurrent_program_id,
2634 fcr.request_id, fcr.priority_request_id
2635 into session_id, userid, respid, respappid,
2636 secgrpid, siteid, loginid, cloginid,
2637 progappid, cprogid, creqid, cprireqid
2638 from fnd_concurrent_requests fcr,
2639 fnd_cp_sql_requests sr
2640 where fcr.phase_code = 'R'
2641 and fcr.status_code = 'R'
2642 and fcr.request_id = sr.request_id
2643 and sr.machine = lmachine
2644 and sr.client_process_id = lpid;
2645
2646 fnd_global.bless_next_init('FND_PERMIT_0002');
2647 FND_GLOBAL.INITIALIZE(session_id, userid, respid, respappid, secgrpid, siteid, loginid,
2648 cloginid, progappid, cprogid, creqid, cprireqid);
2649
2650 -- now delete the row, to avoid having to purge the table.
2651 DELETE from fnd_cp_sql_requests where request_id = creqid;
2652
2653 exit;
2654 exception
2655 when no_data_found then
2656 if (counter = 1) then
2657 DBMS_LOCK.sleep(5);
2658 elsif (counter > 1 and counter <= 4) then
2659 DBMS_LOCK.sleep(10);
2660 else
2661 -- no row found, most likely ran from the command line.
2662 -- Initialize a default context:
2663 -- USER = ANONYMOUS (-1)
2664 -- RESP = System Administrator (20420)
2665 -- RESP APPL = System Administration (1)
2666 FND_GLOBAL.APPS_INITIALIZE(-1, 20420, 1);
2667 end if;
2668
2669 end;
2670 end loop;
2671
2672 -- The rest of the initialization used to be hardcoded into the SQL script by fdpsql().
2673 INIT_REQUEST;
2674
2675 FND_PROFILE.PUT('SECURITY_GROUP_ID', FND_GLOBAL.SECURITY_GROUP_ID);
2676
2677 SET_PREFERRED_RBS;
2678
2679 exception
2680 when others then
2681
2682 if ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2683 fnd_message.set_name ('FND', 'SQL-Generic error');
2684 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
2685 fnd_message.set_token ('REASON', sqlerrm, FALSE);
2686 fnd_message.set_token ('ROUTINE', 'FND_CONCURRENT.INIT_SQL_REQUEST', FALSE);
2687 fnd_log.message(FND_LOG.LEVEL_EXCEPTION,
2688 'fnd.plsql.FND_CONCURRENT.INIT_SQL_REQUEST.others', FALSE);
2689 end if;
2690 end;
2691
2692 end init_sql_request;
2693
2694 --procedure debug(message in varchar2) is
2698 -- begin
2695 -- pragma autonomous_transaction;
2696 -- msg varchar2(2000);
2697 -- l_count number;
2699
2700 -- insert into FND_CONCURRENT_DEBUG_INFO(TIME, ACTION, message, TIME_IN_NUMBER)
2701 -- VALUES(sysdate,'FND_CONCURRENT.get_m_s',message,0);
2702 --commit;
2703 --end debug;
2704
2705
2706 function check_user_privileges( p_user_name IN varchar2,
2707 p_test_code IN varchar2 DEFAULT NULL) RETURN number IS
2708 l_user_id number := -1;
2709 l_resp_id number := -1;
2710 l_ret_value number := 0;
2711 CURSOR l_user_csr(p_user_name varchar2) IS
2712 SELECT user_id
2713 FROM fnd_user
2714 WHERE user_name = upper(p_user_name);
2715 CURSOR l_resp_csr IS
2716 SELECT responsibility_id
2717 FROM fnd_responsibility
2718 WHERE responsibility_key = 'SYSTEM_ADMINISTRATOR';
2719 CURSOR l_user_resp_csr( p_user_id number, p_responsibility_id number)IS
2720 SELECT count(responsibility_id)
2721 FROM fnd_user_resp_groups
2722 WHERE user_id = p_user_id
2723 AND responsibility_id = p_responsibility_id;
2724 BEGIN
2725 IF (p_test_code <> 'STARTMGR' and p_test_code <> 'RUNCONCPROG') THEN
2726 l_ret_value := -1;
2727 RETURN l_ret_value;
2728 END IF;
2729
2730 OPEN l_user_csr(check_user_privileges.p_user_name);
2731 FETCH l_user_csr INTO l_user_id;
2732 CLOSE l_user_csr;
2733
2734 OPEN l_resp_csr;
2735 FETCH l_resp_csr INTO l_resp_id;
2736 CLOSE l_resp_csr;
2737
2738 IF (l_user_id <> -1 and l_resp_id <> -1) THEN
2739 OPEN l_user_resp_csr(l_user_id, l_resp_id);
2740 FETCH l_user_resp_csr INTO l_ret_value;
2741 CLOSE l_user_resp_csr;
2742 END IF;
2743
2744 RETURN l_ret_value;
2745 END check_user_privileges;
2746
2747 function check_program_privileges( p_user_name IN varchar2,
2748 p_resp_id IN number DEFAULT NULL,
2749 p_resp_appl_id IN number DEFAULT NULL,
2750 p_program_name IN varchar2,
2751 p_application_short_name IN varchar2,
2752 p_sec_group_id IN number ) RETURN number IS
2753 l_ret_value number := 0;
2754 l_application_id number;
2755 l_conc_program_id number;
2756 l_user_id number := -1;
2757 l_srs_flag varchar2(1);
2758 l_predicate varchar2(9500);
2759 l_sql_stmt varchar2(10000);
2760 l_return_status varchar2(1);
2761
2762 CURSOR l_user_resp_csr(p_user_id number,
2763 p_resp_id number,
2764 p_resp_appl_id number,
2765 p_sec_group_id number)IS
2766 SELECT count(responsibility_id)
2767 FROM fnd_user_resp_groups
2768 WHERE user_id = p_user_id
2769 AND responsibility_id = p_resp_id
2770 AND responsibility_application_id = p_resp_appl_id
2771 AND security_group_id = p_sec_group_id;
2772
2773 CURSOR l_user_csr(p_user_name varchar2) IS
2774 SELECT user_id
2775 FROM fnd_user
2776 WHERE user_name = p_user_name;
2777
2778 CURSOR l_appl_id_csr(p_application_short_name varchar2) IS
2779 SELECT application_id
2780 FROM fnd_application
2781 WHERE application_short_name = p_application_short_name;
2782
2783 CURSOR l_prog_id_csr(p_program_name varchar2,
2784 p_application_id number) IS
2785 SELECT concurrent_program_id, srs_flag
2786 FROM fnd_concurrent_programs
2787 WHERE concurrent_program_name = p_program_name
2788 AND application_id = p_application_id;
2789
2790 BEGIN
2791 OPEN l_user_csr(check_program_privileges.p_user_name);
2792 FETCH l_user_csr INTO l_user_id;
2793 CLOSE l_user_csr;
2794
2795 OPEN l_appl_id_csr(p_application_short_name);
2796 FETCH l_appl_id_csr INTO l_application_id;
2797 CLOSE l_appl_id_csr;
2798
2799 OPEN l_prog_id_csr(p_program_name, l_application_id);
2800 FETCH l_prog_id_csr INTO l_conc_program_id, l_srs_flag;
2801 CLOSE l_prog_id_csr;
2802
2803 IF (l_application_id is not null and l_conc_program_id is not NULL
2804 AND l_user_id IS NOT null) THEN
2805
2806 OPEN l_user_resp_csr(l_user_id, check_program_privileges.p_resp_id,
2807 check_program_privileges.p_resp_appl_id,
2808 check_program_privileges.p_sec_group_id);
2809 FETCH l_user_resp_csr INTO l_ret_value;
2810 CLOSE l_user_resp_csr;
2811 IF (l_ret_value = 0) THEN
2812 l_ret_value := -1;
2813 RETURN l_ret_value;
2814 END IF;
2815 l_ret_value := 0;
2816
2817 --check IF this program is not SRS-enabled and IF true let it pass through
2818 IF (check_user_privileges(p_user_name, 'RUNCONCPROG') > 0 AND (l_srs_flag = 'N' OR l_srs_flag = 'Q')) THEN
2819 l_ret_value := 1;
2820 ELSIF (l_srs_flag = 'Y') THEN
2821 fnd_global.apps_initialize
2822 (user_id => l_user_id,
2823 resp_id => p_resp_id,
2824 resp_appl_id => p_resp_appl_id,
2825 security_group_id => p_sec_group_id);
2826
2827 fnd_data_security.get_security_predicate
2831 x_predicate => l_predicate,
2828 (p_api_version => 1.0,
2829 p_function => 'FND_CP_REQ_SUBMIT',
2830 p_object_name => 'FND_CONCURRENT_PROGRAMS',
2832 x_return_status => l_return_status,
2833 p_table_alias => 'p');
2834
2835 l_sql_stmt := 'select count(p.concurrent_program_id) from fnd_concurrent_programs p where p.concurrent_program_id = :1 and p.application_id = :2 and ' || l_predicate || '';
2836
2837 execute immediate l_sql_stmt INTO l_ret_value using l_conc_program_id, l_application_id;
2838
2839 IF (l_ret_value > 0) THEN
2840 l_ret_value := 1;
2841 ELSE
2842 l_ret_value := 0;
2843 END IF;
2844
2845 END IF; --IF (l_srs...
2846 ELSE
2847 l_ret_value := -1;
2848 END IF; --IF l_application_id...
2849
2850 RETURN l_ret_value;
2851 END check_program_privileges;
2852
2853
2854 end FND_CONCURRENT;
[Home] [Help]