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