1 package FND_CONCURRENT AUTHID CURRENT_USER as
2 /* $Header: AFCPUTLS.pls 120.4.12000000.1 2007/01/18 13:17:23 appldev ship $ */
3 /*#
4 * Utility APIs for concurrent processing.
5 * @rep:scope public
6 * @rep:product FND
7 * @rep:displayname Request Set
8 * @rep:lifecycle active
9 * @rep:compatibility S
10 */
11 --
12 -- Package
13 -- FND_CONCURRENT
14 -- Purpose
15 -- Concurrent processing related utilities
16 -- History
17 -- XX/XX/93 Ram Bhoopalam Created
18 --
19 --
20 -- PUBLIC VARIABLES
21 --
22
23 TYPE Print_Options_Rec_Typ IS RECORD
24 ( number_of_copies number := null
25 , print_style varchar2(30) := null
26 , printer varchar2(30) := null
27 , save_output_flag varchar2(1) := null
28 );
29
30 TYPE Print_Options_Tbl_Typ IS TABLE OF Print_Options_Rec_Typ
31 INDEX BY BINARY_INTEGER;
32
33 -- Exceptions
34
35 -- Exception Pragmas
36
37 --
38 -- PUBLIC FUNCTIONS
39 --
40
41 --
42 -- Name
43 -- GET_REQUEST_STATUS
44 -- Purpose
45 -- returns the status of concurrent request and completion message
46 -- if the request has completed. Returns both user ( translatable )
47 -- and developer ( could you be used to compare/check and base their
48 -- program logic ) version for phase and status values.
49 -- Arguments ( input )
50 -- request_id - Request id for which status has to be checked
51 -- - If Application and prorgram information is passed,
52 -- - most recent request id for this program is returned
53 -- - along with the status and phase.
54 -- appl_shortname - Application to which the program belongs
55 -- program - Program name ( appl and program information used
56 -- - only if request id is not provided )
57 -- Arguments ( output )
58 -- phase - Request phase ( from meaning in fnd_lookups )
59 -- status - Request status( for display purposes )
60 -- dev_phase - Request phase as a constant string so that it
61 -- - can be used for comparisons )
62 -- dev_status - Request status as a constatnt string
63 -- message - Completion message if request has completed
64 --
65 /*#
66 * Returns the Status of a concurrent request. Also returns the completion text if the request is already completed.
67 * @param request_id Request ID of the program to be checked
68 * @param appl_shortname Short name of the application associated with the program
69 * @param program Short name of the concurrent program
70 * @param phase Request phase
71 * @param status Request status
72 * @param dev_phase Request phase as a string constant
73 * @param dev_status Request status as a string constant
74 * @param message Request completion message
75 * @return Returns TRUE on succesful retrieval of the information, FALSE otherwise
76 * @rep:displayname Get Request Status
77 * @rep:category BUSINESS_ENTITY FND_CP_REQUEST
78 * @rep:scope public
79 * @rep:lifecycle active
80 * @rep:compatibility S
81 */
82 function get_request_status(request_id IN OUT NOCOPY number,
83 appl_shortname IN varchar2 default NULL,
84 program IN varchar2 default NULL,
85 phase OUT NOCOPY varchar2,
86 status OUT NOCOPY varchar2,
87 dev_phase OUT NOCOPY varchar2,
88 dev_status OUT NOCOPY varchar2,
89 message OUT NOCOPY varchar2) return boolean;
90 pragma restrict_references (get_request_status, WNDS);
91
92 --
93 -- Name
94 -- WAIT_FOR_REQUEST
95 -- Purpose
96 -- Waits for the request completion, returns phase/status and
97 -- completion text to the caller. Calls sleep between db checks.
98 -- Arguments (input)
99 -- request_id - Request ID to wait on
100 -- interval - time b/w checks. Number of seconds to sleep
101 -- - (default 60 seconds)
102 -- max_wait - Max amount of time to wait (in seconds)
103 -- - for request's completion
104 -- Arguments (output)
105 -- User version of phase and status
106 -- Developer version of phase and status
107 -- Completion text if any
108 -- phase - Request phase ( from meaning in fnd_lookups )
109 -- status - Request status( for display purposes )
110 -- dev_phase - Request phase as a constant string so that it
111 -- - can be used for comparisons )
112 -- dev_status - Request status as a constatnt string
113 -- message - Completion message if request has completed
114 --
115 --
116 /*#
117 * Waits for the request completion, then returns the request phase/status and
118 * completion message to the caller. Goes to sleep between checks for the
119 * request completion.
120 * @param request_id Request ID of the request to wait on
121 * @param interval Number of seconds to wait between checks
122 * @param max_wait Maximum number of seconds to wait for the request completion
123 * @param phase User-friendly Request phase
124 * @param status User-friendly Request status
125 * @param dev_phase Request phase as a constant string
126 * @param dev_status Request status as a constant string
127 * @param message Request completion message
128 * @return Returns TRUE on succesful retrieval of the information, FALSE otherwise
129 * @rep:displayname Wait for Request
130 * @rep:category BUSINESS_ENTITY FND_CP_REQUEST
131 * @rep:scope public
132 * @rep:lifecycle active
133 * @rep:compatibility S
134 */
135 function wait_for_request(request_id IN number default NULL,
136 interval IN number default 60,
137 max_wait IN number default 0,
138 phase OUT NOCOPY varchar2,
139 status OUT NOCOPY varchar2,
140 dev_phase OUT NOCOPY varchar2,
141 dev_status OUT NOCOPY varchar2,
142 message OUT NOCOPY varchar2) return boolean;
143
144 --
145 -- Name
146 -- FND_CONCURRENT.GET_MANAGER_STATUS
147 -- Purpose
148 -- Returns the target ( number that should be active at this instant )
149 -- and active number of processes for a given manager.
150 -- along with the current PMON method currently in use
151 -- Arguments (input)
152 -- applid - Application ID of application under which the
153 -- - manager is registered
154 -- managerid - Concurrent manager ID ( queue id )
155 -- (output)
156 -- target - Number of manager processes that should be active
157 -- - for the current workshift in effect
158 -- active - actual number of processes that are active
159 -- pmon_method - RDBMS/OS
160 -- message - message if any
161 --
162
163 procedure get_manager_status (applid IN number default 0,
164 managerid IN number default 1,
165 targetp OUT NOCOPY number,
166 activep OUT NOCOPY number,
167 pmon_method OUT NOCOPY varchar2,
168 callstat OUT NOCOPY number);
169
170 --
171 -- Name
172 -- FND_CONCURRENT.SET_COMPLETION_STATUS
173 -- Purpose
174 -- Called from a concurrent request to set its completion
175 -- status and message.
176 --
177 -- Arguments (input)
178 -- status - 'NORMAL', 'WARNING', or 'ERROR'
179 -- message - Optional message
180 --
181 -- Returns:
182 -- TRUE on success. FALSE on error.
183 --
184
185 function set_completion_status (status IN varchar2,
186 message IN varchar2) return boolean;
187
188 --
189 -- Name
190 -- Get_Request_Print_Options
191 -- Purpose
192 -- Returns the print options for a concurrent request.
193 --
194 function GET_REQUEST_PRINT_OPTIONS
195 (request_id IN number,
196 number_of_copies OUT NOCOPY number,
197 print_style OUT NOCOPY varchar2,
198 printer OUT NOCOPY varchar2,
199 save_output_flag OUT NOCOPY varchar2) return boolean;
200
201 pragma restrict_references (get_request_print_options, WNDS);
202
203 --
204 -- Name
205 -- Get_Request_Print_Options
206 -- Purpose
207 -- Returns the print options for a concurrent request.
208 -- Parameters
209 -- request_id: The request_id for the concurrent request
210 -- print_options: pl/sql table of print_options_tbl_typ
211 -- (see spec for details)
212 -- Returns
213 -- The total number post-processing actions for printing
214 --
215 function GET_REQUEST_PRINT_OPTIONS
216 (request_id IN number,
217 print_options OUT NOCOPY print_options_tbl_typ) return number;
218
219 --
220 -- Name
221 -- Check_Lock_Contention
222 -- Purpose
223 -- Identifies the process that is holding the lock(s) on resources
224 -- that are needed by the process identified by the Queue Name or
225 -- Session ID parameter.
226 -- Arguments (input)
227 -- Queue_Name - Concurrent Manager name (optional)
228 -- Session ID - Session ID of manager or other process (optional)
229 --
230 -- Only one of the above arguments needs to be passed
231 -- Arguments (output)
232 -- Returns information about the holding process ...
233 --
234 -- Process ID - Oracle Process ID of the process holding the lock
235 -- Terminal - Terminal (if any) associated with the holding process
236 -- Node - Node on which the holding process resides
237 -- User - Owner of the holding process
238 -- Program - Name of the program that is holding the lock/resource
239 --
240 --
241 function Check_Lock_Contention
242 (Queue_Name IN varchar2 default NULL,
243 Session_ID IN number default NULL,
244 UProcess_ID OUT NOCOPY number,
245 UTerminal OUT NOCOPY varchar2,
246 UNode OUT NOCOPY varchar2,
247 UName OUT NOCOPY varchar2,
248 UProgram OUT NOCOPY varchar2) return boolean;
249 -- pragma restrict_references (Check_Lock_Contention, WNDS);
250
251 function get_program_attributes
252 (appl_shortname IN varchar2 default NULL,
253 program IN varchar2 default NULL,
254 printer OUT NOCOPY varchar2,
255 style OUT NOCOPY varchar2,
256 save_output OUT NOCOPY varchar2) return boolean;
257 pragma restrict_references (get_program_attributes, WNDS);
258
259 --
260 -- Name
261 -- FND_CONCURRENT.INIT_REQUEST
262 -- Purpose
263 -- Called for all concurrent requests to update session id and process
264 -- id information
265 --
266 -- Arguments (input)
267 --
268 --
269 -- Returns:
270 --
271 --
272
273 procedure init_request;
274
275 PROCEDURE SET_PREFERRED_RBS;
276
277 function Reset_Context(Request_ID IN Number default NULL) return boolean;
278
279 -- Name
280 -- FND_CONCURRENT.AF_COMMIT
281 -- Purpose
282 -- It does the commit and set the preferred rollback segment for the
283 -- program. Call this routine only in the concurrent program context.
284 --
285 -- Arguments (input)
286 --
287 -- Returns:
288 Procedure AF_COMMIT;
289
290 -- Name
291 -- FND_CONCURRENT.AF_ROLLBACK
292 -- Purpose
293 -- It does the rollback and set the preferred rollback segment for the
294 -- program. Call this routine only in the concurrent program context.
295 --
296 -- Arguments (input)
297 --
298 -- Returns:
299 Procedure AF_ROLLBACK;
300
301 -- Name
302 -- FND_CONCURRENT.SHUT_DOWN_PROCS
303 -- Purpose
304 -- Runs the pl/sql shutdown procedures stored in FND_EXECUTABLES
305 -- with EXECUTION_METHOD_CODE = 'Z'.
306 --
307
308 procedure shut_down_procs;
309
310 -- Name
311 -- FND_CONCURRENT.SET_INTERIM_STATUS
312 -- Purpose
313 -- sets the requests phase_code, interim_status_code and completion_text
314 -- this is used in Java Concurrent Programs.
315 --
316 -- Arguments (input)
317 -- status - 'NORMAL', 'WARNING', or 'ERROR'
318 -- message - Optional message
319 --
320 -- Returns:
321 -- TRUE on success. FALSE on error.
322 --
323
324 function set_interim_status( status IN VARCHAR2,
325 message IN VARCHAR2) return boolean;
326
327 TYPE requests_rec IS RECORD ( request_id number,
328 phase varchar2(80),
329 status varchar2(80),
330 dev_phase varchar2(30),
331 dev_status varchar2(30),
332 message varchar2(240));
333
334 TYPE requests_tab_type IS TABLE of requests_rec
335 index by binary_integer;
336
337 -- Name
338 -- FND_CONCURRENT.DEBUG
339 -- Purpose
340 -- Used to put some debug statements
341 --
342 -- procedure debug(message in varchar2);
343
344 -- Name
345 -- FND_CONCURRENT.GET_SUB_REQUESTS
346 -- Purpose
347 -- gets all sub-requests for a given request id. For each sub-request it
348 -- provides request_id, phase,status, developer phase , developer status
349 -- completion text.
350 --
351 -- Arguments (input)
352 -- request_id - Request Id for which sub-requests are required.
353 --
354 -- Returns:
355 -- Table FND_CONCURRENT.REQUESTS_TAB_TYPE.If the table size is 0 then
356 -- there are no sub requests for the given request.
357 --
358
359 function get_sub_requests(p_request_id IN number) return requests_tab_type;
360
361
362 -- Name
363 -- FND_CONCURRENT.CHILDREN_DONE
364 -- Purpose
365 -- Examines all descendant requests of a given request id. Returns TRUE if
366 -- all have completed.
367 --
368 -- Arguments (input)
369 -- Parent_Request_ID - Request Id for which sub-requests are required. Null
370 -- will be interpreted as current req_id.
371 -- Recursive_Flag - Should the function check/wait for all descendants (grandchildren)as well
372 --
373 -- Interval - If Timeout>0, then we sleep this many seconds between queries(default 60)
374 -- Max_Wait - If > 0 and children not done, we will wait up to Timeout seconds before
375 -- responding FALSE
376 -- Function returns TRUE if all descendant requests have "Completed".
377 -- Outcome (Success/Error/Warning) of the completed requests does not influence the outcome of
378 -- "Children_Done" function. Function returns FALSE if it's unable to determine the
379 -- status of all the descendant requests OR if it timesout.
380
381
382 function CHILDREN_DONE(Parent_Request_ID IN NUMBER default NULL,
383 Recursive_Flag in varchar2 default 'N',
384 Interval IN number default 60,
385 Max_Wait IN number default 0) return boolean;
386
387 procedure register_node( name varchar2, /* Max 30 bytes */
388 platform_id number, /* Platform ID from BugDB */
389 forms_tier varchar2, /* 'Y'/'N' */
390 cp_tier varchar2, /* 'Y'/'N' */
391 web_tier varchar2, /* 'Y'/'N' */
392 admin_tier varchar2, /* 'Y'/'N' */
393 p_server_id varchar2 default NULL, /* ID of server */
394 p_address varchar2 default NULL, /* IP address of server */
395 p_description varchar2 default NULL,
396 p_host_name varchar2 default NULL,
397 p_domain varchar2 default NULL, /* description of server*/
398 db_tier varchar2 default NULL, /* 'Y'/'N' */
399 p_virtual_ip varchar2 default NULL); /* Virtual IP */
400
401 TYPE Service_Instance_Rec IS RECORD ( Service_Handle varchar2(8),
402 Application varchar2(50),
403 Instance_Name varchar2(30),
404 State varchar2(30));
405
406 TYPE Service_Instance_Tab_Type IS TABLE of Service_Instance_Rec
407 index by binary_integer;
408
409 -- Name
410 -- Fnd_Concurrent.Get_Service_Instances
411 -- Purpose
412 -- Fetch all service instances defined for a Service type
413 -- Returns the service instance identity along with it's current
414 -- state (Active/Disabled/Inactive/Suspended/Transit )
415 --
416 -- Arguments (input)
417 -- svc_handle - Developer name for the Service type
418 --
419 -- Returns:
423 --
420 -- Table Fnd_Concurrent.Service_Instance_Tab_Type. A table size of 0
421 -- indicates absence of any service instances for the specified service
422 -- type
424
425 function Get_Service_Instances(svc_handle IN VARCHAR2)
426 return Service_Instance_Tab_Type;
427
428 TYPE Service_Process_Rec IS RECORD ( CPID number,
429 State varchar2(80),
430 Node varchar2(30),
431 Parameters varchar2(2000));
432
433 TYPE Service_Process_Tab_Type IS TABLE of Service_Process_Rec
434 index by binary_integer;
435
436 -- Name
437 -- Fnd_Concurrent.Get_Service_Processes
438 -- Purpose
439 -- Fetch all service instance processes for a service instance
440 --
441 -- Arguments (input)
442 -- appl_short_name - Application Short Name under which the service
443 -- - instance is registered
444 -- svc_instance_name - Developer name for the service instance
445 -- proc_state - Service process state
446 --
447 -- Application and Service Instance Name together can be used to locate
448 -- all processes
449 --
450 -- Returns (Fnd_Concurrent.Service_Process_Tab_Type)
451 -- Fnd_Concurrent.Service_Process_Tab_Type.
452 -- CPID (Concurrent_Process_ID) - Can be used to address/act on the
453 -- process
454 -- Service_Parameters - To be used to target particular
455 -- - service instances
456 -- A table size of 0 indicates absence of any service processes
457 -- for the specified service instance and state
458 --
459
460 function Get_Service_Processes(appl_short_name IN varchar2,
461 svc_instance_name IN varchar2,
462 proc_state IN varchar2)
463 return Service_Process_Tab_Type;
464
465
466 /* internal use only . . .need to put in spec for pragma */
467 function MSC_MATCH(requestid number,
468 app_id number, que_id number, mtype number) return number;
469
470 pragma restrict_references(MSC_MATCH,WNDS);
471
472
473
474 -- Name
475 -- FND_CONCURRENT.find_pending_svc_ctrl_reqs
476 -- Purpose
477 -- gets all pending service control requests for a given service or service
478 -- instance. Returns number of requests found and has an out parameter
479 -- containing a comma delimited list of matching requests.
480 --
481 -- Arguments (input)
482 -- service_id - Service ID of service in which we are interested.
483 -- (Set to null if this doesn't matter)
484 -- service_inst_id - Service instance ID of svc in which we are interested.
485 -- (Set to null if this doesn't matter)
486 -- request_list - Comma delimited list of matching request ids.
487 --
488 -- Returns:
489 -- Number of mathcing requests.
490 --
491
492
493 function find_pending_svc_ctrl_reqs(service_id in number,
494 service_inst_id in number,
495 req_list out NOCOPY varchar2) return number;
496
497
498 -- Name
499 -- FND_CONCURRENT.Function Wait_for_SCTL_Done
500 -- Purpose
501 -- Waits for Svc Ctrl request to finish, or another conflicting request,
502 -- or timeout.
503 --
504 -- Arguments (input)
505 -- reqid - request id we are interested in.
506 --
507 -- timeout - timeout in seconds;
508 --
509 -- Returns:
510 -- Number -
511 -- - 1: request not found.
512 -- - 2: request is not a supported type.
513 -- - 3: request has not run before timeout.
514 -- - 4: later request conflicts with this request.
515 -- - 5: request has run, but not complete before timeout.
516 -- - 6: requested actions have completed.
517 --
518 -- Supporting routines:
519 -- For readability the following functions are available to compare to
520 -- result:
521
522 Function SCTL_REQ_NOT_FOUND return number; -- 1
523 Function SCTL_REQ_NOT_SUPPD return number; -- 2
524 Function SCTL_TIMEOUT_NOT_R return number; -- 3
525 Function SCTL_REQ_CONFLICTS return number; -- 4
526 Function SCTL_TIMEOUT_NOT_C return number; -- 5
527 Function SCTL_REQ_COMPLETED return number; -- 6
528
529 Function Wait_for_SCTL_Done(reqid in number, timeout in number) return number;
530 -- Name
531 -- FND_CONCURRENT.Find_SC_Conflict
532 -- Purpose
533 -- Finds later conflicting service control request (if any) for another
534 -- service control request.
535 --
536 -- Arguments (input)
537 -- reqid - request id we are interested in.
538 --
539 -- Returns:
540 -- Request ID of a conflicting request, or -1 if none exist.
541
542 Function Find_SC_Conflict(reqid in number) return number;
543
544
545 -- Name
546 -- FND_CONCURRENT.Wait_For_All_Down
547 -- Purpose
548 -- Waits for all services, managers, and icm to go down, or timesout.
549 --
550 -- Arguments (input)
551 -- Timeout - in seconds.
552 --
553 -- Returns:
554 -- True if all shut down, false for timeout.
555
556 Function Wait_For_All_Down(Timeout in number) return boolean;
557
558 -- Name
559 -- FND_CONCURRENT.Build_Svc_Ctrl_Desc.
560 -- Purpose
561 -- Provides description text for svc ctrl request based on args.
562 --
563 -- Arguments (input)
564 -- Arg1, Arg2, Arg3 - request arguments for svc ctrl request.
565 --
569 Function Build_Svc_Ctrl_Desc(Arg1 in number,
566 -- Returns:
567 -- Description of Request
568
570 Arg2 in number,
571 Arg3 in number,
572 Prog in varchar2
573 ) return varchar2;
574
575 -- Name
576 -- FND_CONCURRENT.Cancel_Request.
577 -- Purpose
578 -- It Cancels given Concurrent Request.
579 --
580 -- Arguments (input)
581 -- request_id - request id of the request you want to cancel.
582 --
583 -- (out args)
584 -- message - API will fill the message with any errors while canceling
585 -- request.
586 --
587 -- Returns:
588 -- Returns TRUE if success or FALSE on failure.
589
590 function cancel_request( request_id in number,
591 message out NOCOPY varchar2) return boolean;
592 --
593 -- Name
594 -- FND_CONCURRENT.get_resource_lock
595 -- Purpose
596 -- It gets an exclusive lock for a given resource or task name.
597 --
598 -- Arguments (input)
599 -- Resource_name - Name of the resource that uniquely identifies
600 -- in the system.
601 -- timeout - Number of seconds to continue trying to grant the lock
602 -- default is 2 seconds.
603 --
604 -- Returns:
605 -- 0 - Success
606 -- 1 - Timeout ( Resource is locked by other session)
607 -- 2 - Deadlock
608 -- 3 - Parameter error
609 -- 4 - Already own lock specified by lockhandle
610 -- 5 - Illegal lock handle
611 -- -1 - Other exceptions, get the message from message stack for reason.
612
613 function get_resource_lock ( resource_name in varchar2,
614 timeout in number default 2 ) return number;
615
616 --
617 -- Name
618 -- FND_CONCURRENT.release_resource_lock
619 -- Purpose
620 -- It releases an exclusive lock for a given resource or task name.
621 --
622 -- Arguments (input)
623 -- Resource_name - Name of the resource that uniquely identifies
624 -- in the system.
625 --
626 -- Returns:
627 -- 0 - Success
628 -- 3 - Parameter Error
629 -- 4 - Do not own lock
630 -- 5 - Illegal lock handle
631 -- -1 - Other exceptions, get the message from message stack for reason.
632
633 function release_resource_lock ( resource_name in varchar2 ) return number;
634
635 --
636 -- Name
637 -- FND_CONCURRENT.INIT_SQL_REQUEST
638 -- Purpose
639 -- Called for all SQL*PLUS concurrent requests to perform request initialization.
640 --
641
642 procedure init_sql_request;
643
644
645 function check_user_privileges( p_user_name IN varchar2,
646 p_test_code IN varchar2 DEFAULT NULL) RETURN number;
647
648 function check_program_privileges( p_user_name IN varchar2,
649 p_resp_id IN number DEFAULT NULL,
650 p_resp_appl_id IN number DEFAULT NULL,
651 p_program_name IN varchar2,
652 p_application_short_name IN varchar2,
653 p_sec_group_id IN number ) RETURN number;
654
655 end FND_CONCURRENT;