[Home] [Help]
PACKAGE BODY: APPS.FND_MANAGER
Source
1 PACKAGE BODY fnd_manager AS
2 /* $Header: AFCPMGRB.pls 120.2.12000000.2 2007/07/13 10:02:52 ggupta ship $ */
3
4
5 /* Exceptions */
6 bad_parameter EXCEPTION;
7 PRAGMA EXCEPTION_INIT(bad_parameter, -06501); -- program error
8
9 /* Flag for datamerge or customer data */
10 who_mode VARCHAR2(20) := 'customer_data'; /* customer_data or seed_data */
11
12 /* Message buffer */
13 internal_messages VARCHAR2(10000);
14
15 --
16 -- Set the contents of the message buffer */
17 --
18 PROCEDURE message(msg VARCHAR2) IS
19 BEGIN
20 internal_messages := internal_messages || msg || fnd_global.newline;
21 END;
22
23 --
24 -- Clear the message buffer.
25 --
26 PROCEDURE message_init IS
27 BEGIN
28 internal_messages := '';
29 END;
30
31
32 /* ------------------------------------------------------------ */
33 /* who information functions */
34 /* ------------------------------------------------------------ */
35
36 FUNCTION customer_mode RETURN BOOLEAN IS
37 BEGIN
38 IF(who_mode = 'customer_data') THEN
39 RETURN TRUE;
40 ELSIF(who_mode = 'seed_data') THEN
41 RETURN FALSE;
42 ELSE
43 message('bad session mode:' || who_mode);
44 message('use set_session_mode to specify');
45 RAISE bad_parameter;
46 END IF;
47 END;
48
49
50 FUNCTION last_updated_by_f(l_updated_by IN NUMBER DEFAULT NULL)
51 RETURN NUMBER IS
52 BEGIN
53 IF(l_updated_by IS NOT NULL) THEN
54 RETURN l_updated_by;
55 ELSIF(customer_mode) THEN
56 RETURN -1;
57 ELSE
58 RETURN 1;
59 END IF;
60 END;
61
62 FUNCTION last_update_date_f(l_update_date IN DATE DEFAULT NULL)
63 RETURN DATE IS
64 BEGIN
65 IF(l_update_date IS NOT NULL) THEN
66 RETURN l_update_date;
67 ELSE
68 RETURN Sysdate;
69 END IF;
70 END;
71
72 FUNCTION created_by_f(c_updated_by IN NUMBER DEFAULT NULL)
73 RETURN NUMBER IS
74 BEGIN
75 RETURN last_updated_by_f(c_updated_by);
76 END;
77
78 FUNCTION creation_date_f(c_update_date IN DATE DEFAULT NULL)
79 RETURN DATE IS
80 BEGIN
81 IF(c_update_date IS NOT NULL) THEN
82 RETURN c_update_date;
83 ELSIF(customer_mode) THEN
84 RETURN Sysdate;
85 ELSE
86 RETURN To_date('01011980', 'MMDDYYYY');
87 END IF;
88 END;
89
90 FUNCTION last_update_login_f RETURN NUMBER IS
91 BEGIN
92 return 0;
93 END;
94
95
96 /* ------------------------------------------------------------ */
97 PROCEDURE check_notnull(val IN VARCHAR2) IS
98 BEGIN
99 IF(val IS NULL) THEN
100 message('A NULL value was specified for a NOT NULL parameter.');
101 RAISE bad_parameter;
102 END IF;
103 END;
104
105 PROCEDURE check_notnull(val IN NUMBER) IS
106 BEGIN
107 IF(val IS NULL) THEN
108 message('A NULL value was specified for a NOT NULL parameter.');
109 RAISE bad_parameter;
110 END IF;
111 END;
112
113 PROCEDURE check_notnull(val IN DATE) IS
114 BEGIN
115 IF(val IS NULL) THEN
116 message('A NULL value was specified for a NOT NULL parameter.');
117 RAISE bad_parameter;
118 END IF;
119 END;
120 /* ------------------------------------------------------------ */
121
122
123 --
124 -- get the application id, given the name (or short name)
125 --
126 FUNCTION application_id_f(application_name_in IN VARCHAR2)
127 RETURN fnd_application.application_id%TYPE
128 IS
129 application_id_ret fnd_application.application_id%TYPE;
130 BEGIN
131 IF(application_name_in IS NULL) THEN
132 message('A null application_name was specified.');
133 RAISE bad_parameter;
134 END IF;
135 /* Check the short name first. */
136 SELECT application_id
137 INTO application_id_ret
138 FROM fnd_application
139 WHERE upper(application_short_name) = upper(application_name_in);
140 RETURN application_id_ret;
141 EXCEPTION
142 WHEN no_data_found THEN /* Try the translated name. */
143 BEGIN
144 SELECT application_id
145 INTO application_id_ret
146 FROM fnd_application_tl
147 WHERE upper(application_name) = upper(application_name_in)
148 AND rownum = 1;
149 RETURN application_id_ret;
150 EXCEPTION
151 when NO_DATA_FOUND then
152 message('Invalid application name: ' || application_name_in);
153 RAISE bad_parameter;
154 END;
155 END;
156
157
158 --
159 -- Get a concurrent program ID given appl ID and short name
160 --
161 FUNCTION concurrent_program_id_f(program_application_id number,
162 program_short_name varchar2) return number is
163 program_id fnd_concurrent_programs.concurrent_program_id%TYPE;
164 BEGIN
165 SELECT concurrent_program_id
166 INTO program_id
167 FROM fnd_concurrent_programs
168 WHERE application_id = program_application_id
169 AND upper(concurrent_program_name) = upper(program_short_name);
170
171 RETURN program_id;
172
173 EXCEPTION
174 WHEN no_data_found THEN
175 message('Could not find program: '||program_short_name);
176 RAISE bad_parameter;
177 END;
178
179 --
180 -- Get a concurrent library ID given appl ID and name
181 --
182 FUNCTION library_id_f(lib_appl_id number,
183 lib_name varchar2) return number is
184 lib_id fnd_concurrent_processors.concurrent_processor_id%type;
185 begin
186 select concurrent_processor_id
187 into lib_id
188 from fnd_concurrent_processors
189 where application_id = lib_appl_id
190 and lib_name = concurrent_processor_name;
191
192 return lib_id;
193
194 exception
195 when no_data_found then
196 message('Could not find library: '|| lib_name);
197 raise bad_parameter;
198 end;
199
200 --
201 -- Get a manager ID given appl ID and name
202 --
203 FUNCTION manager_id_f(mgr_appl_id number,
204 mgr_name varchar2) return number is
205 mgr_id fnd_concurrent_queues.concurrent_queue_id%type;
206 begin
207 select concurrent_queue_id
208 into mgr_id
209 from fnd_concurrent_queues
210 where application_id = mgr_appl_id
211 and upper(mgr_name) = upper(concurrent_queue_name);
212
213 return mgr_id;
214
215 exception
216 when no_data_found then
217 message('Could not find manager: '|| mgr_name);
218 raise bad_parameter;
219 end;
220
221 --
222 -- Get a Service ID given sevice handle
223 --
224 FUNCTION service_id_f(svc_handle varchar2) return number is
225 svc_id number;
226 begin
227 select SERVICE_ID
228 into svc_id
229 from FND_CP_SERVICES
230 where upper(svc_handle) = upper(SERVICE_HANDLE);
231
232 return svc_id;
233
234 exception
235 when no_data_found then
236 message('Could not find service handle: '|| svc_handle);
237 raise bad_parameter;
238 end;
239
240 --
241 -- Get a Node given node name
242 --
243 FUNCTION node_name_f(nodename varchar2) return varchar2 is
244 nname varchar2(31);
245 begin
246 select node_name
247 into nname
248 from FND_NODES
249 where upper(nodename) = upper(NODE_NAME);
250
251 return nname;
252
253 exception
254 when no_data_found then
255 message('Could not find node: '|| nodename);
256 raise bad_parameter;
257 end;
258
259 /* ------------------------------------------------------------ */
260
261 --
262 -- Procedure
263 -- SET_SESSION_MODE
264 --
265 -- Purpose
266 -- Sets the package mode for the current session.
267 --customer_data
268 -- Arguments:
269 -- session_mode - 'seed_data' if new data is for Datamerge.
270 -- 'customer_data' is the default.
271 --
272 PROCEDURE set_session_mode(session_mode IN VARCHAR2) IS
273 BEGIN
274 IF(lower(session_mode) NOT IN ('customer_data', 'seed_data')) THEN
275 message('bad mode:'|| session_mode);
276 message('valid values are: customer_data, seed_data');
277 RAISE bad_parameter;
278 END IF;
279 who_mode := lower(session_mode);
280 END;
281
282
283 -- Function
284 -- MESSAGE
285 --
286 -- Purpose
287 -- Return an error message. Messages are set when
288 -- validation (program) errors occur.
289 --
290 FUNCTION message RETURN VARCHAR2 IS
291 BEGIN
292 RETURN internal_messages;
293 END;
294
295
296 -- Function
297 -- GET_SPECIALIZATION_TYPE_ID
298 --
299 -- Purpose
300 -- Get a Type (Object) ID from FND_CONCURRENT_QUEUE_CONTENT
301 -- (fcqc.TYPE_ID) given the Object's Name, Application ID, and
302 -- Lookup Code
303 --
304 -- Arguments:
305 -- obj_name - The name of the specialization object
306 -- (complex rule name/oracle username/program name/
307 -- request class name/apps username)
308 -- obj_appl_id - The application id (fcqc.TYPE_APPLICATION_ID) of the
309 -- specialization object
310 -- obj_code - The lookup code (fcqc.TYPE_CODE) of the specialization
311 -- object which corresponds to CP_SPECIAL_RULES lookup type
312 -- (C/O/P/R/U)
313
314 FUNCTION get_specialization_type_id(obj_name varchar2,
315 obj_appl_id number,
316 obj_code varchar2) return number is
317
318 obj_id number;
319
320 begin
321 if (obj_code = 'C') then
322 begin
323 select complex_rule_id
324 into obj_id
325 from fnd_concurrent_complex_rules
326 where application_id = obj_appl_id
327 and complex_rule_name = obj_name;
328 exception
329 when no_data_found then
330 message('Could not find rule: '||obj_name);
331 raise bad_parameter;
332 end;
333 elsif (obj_code = 'O') then
334 begin
335 select oracle_id
336 into obj_id
337 from fnd_oracle_userid
338 where oracle_username = obj_name;
339 exception
340 when no_data_found then
341 message('Could not find Oracle ID: '|| obj_name);
342 raise bad_parameter;
343 end;
344 elsif (obj_code = 'P') then
345 if (obj_name is not null) then
346 obj_id := concurrent_program_id_f(obj_appl_id, obj_name);
347 end if;
348 elsif (obj_code = 'R') then
349 begin
350 select request_class_id
351 into obj_id
352 from fnd_concurrent_request_class
353 where application_id = obj_appl_id
354 and request_class_name = obj_name;
355 exception
356 when no_data_found then
357 message('Could not find request type: '||obj_name);
358 raise bad_parameter;
359 end;
360 else
361 begin
362 select user_id
363 into obj_id
364 from fnd_user
365 where user_name = obj_name;
366 exception
367 when no_data_found then
368 message('Could not find user: '||obj_name);
369 raise bad_parameter;
370 end;
371 end if;
372 return obj_id;
373 end;
374
375 /* ------------------------------------------------------------ */
376
377 -- Procedure
378 -- REGISTER
379 --
380 -- Purpose
381 -- Register a concurrent manager.
382 --
383 -- Arguments
384 -- Manager - Concurrent manager name.
385 -- Application - Manager application short name.
386 -- Short_Name - Manager short (non-translated) name.
387 -- Description - Manager description (Optional).
388 -- Type - 'Concurrent Manager', 'Internal Monitor', or
389 -- 'Transaction Manager'.
390 -- Cache_Size - Request cache size (Optional; Concurrent Managers only).
391 -- Data_Group - Data group name (Transaction Managers only).
392 -- Primary_Node - Primary node (optional).
393 -- Primary_Queue - Primary OS queue (Optional).
394 -- Secondary_Node - Secondary node (optional).
395 -- Secondary_Queue - Secondary OS queue (Optional).
396 -- Library - Concurrent processing library (e.g. FNDLIBR).
397 -- Library_Application - Library application short name.
398 -- Data_Group_id - Optional. Overrides 'data_group' parameter.
399 -- Language_code - Language code for translated values.
400 -- Last_Update_Date- Who information for FNDLOAD standards
401 -- Last_Updated_By - Who information for FNDLOAD standards
402 --
403 PROCEDURE register(manager IN VARCHAR2,
404 application IN VARCHAR2,
405 short_name IN VARCHAR2,
406 description IN VARCHAR2 DEFAULT NULL,
407 type IN VARCHAR2,
408 cache_size IN NUMBER DEFAULT NULL,
409 data_group IN VARCHAR2 DEFAULT NULL,
410 primary_node IN VARCHAR2 DEFAULT NULL,
411 primary_queue IN VARCHAR2 DEFAULT NULL,
412 secondary_node IN VARCHAR2 DEFAULT NULL,
413 secondary_queue IN VARCHAR2 DEFAULT NULL,
414 library IN VARCHAR2,
415 library_application IN VARCHAR2,
416 data_group_id IN NUMBER DEFAULT NULL,
417 language_code IN VARCHAR2 DEFAULT 'US',
418 last_update_date IN DATE DEFAULT NULL,
419 last_updated_by IN NUMBER DEFAULT NULL
420 ) is
421
422 mgr_appl_id fnd_application.application_id%TYPE;
423 mgr_id fnd_concurrent_queues.concurrent_queue_id%TYPE;
424 type_code fnd_concurrent_queues.manager_type%TYPE;
425 dg_id fnd_data_groups.data_group_id%TYPE;
426 lib_appl_id fnd_application.application_id%TYPE;
427 lib_id fnd_concurrent_processors.concurrent_processor_id%TYPE;
428
429 last_update_login fnd_concurrent_queues.last_update_login%TYPE
430 := last_update_login_f;
431 l_update_date fnd_concurrent_queues.last_update_date%TYPE
432 := last_update_date_f(last_update_date);
433 l_updated_by fnd_concurrent_queues.last_updated_by%TYPE
434 := last_updated_by_f(last_updated_by);
435 creation_date fnd_concurrent_queues.creation_date%TYPE
436 := creation_date_f(last_update_date);
437 created_by fnd_concurrent_queues.created_by%TYPE
438 := created_by_f(last_updated_by);
439
440 begin
441 message_init;
442
443 check_notnull(manager);
444 check_notnull(short_name);
445 check_notnull(application);
446 check_notnull(type);
447 check_notnull(library);
448 check_notnull(library_application);
449
450 /* Get new ID */
451 select fnd_concurrent_queues_s.nextval
452 into mgr_id
453 from sys.dual;
454
455 mgr_appl_id := application_id_f(application);
456 lib_appl_id := application_id_f(library_application);
457
458 /* Get type code */
459 begin
460 select lookup_code
461 into type_code
462 from fnd_lookup_values
463 where lookup_type = 'CP_MANAGER_TYPE'
464 and upper(meaning) = upper(type)
465 and rownum = 1;
466 exception
467 when no_data_found then
468 message('Invalid manager type: ' || type);
472 /* Check shortname for invalid spaces */
469 raise bad_parameter;
470 end;
471
473 if ( instr(short_name, ' ') > 0 ) then
474 message('Short Name must not contain spaces ');
475 raise bad_parameter;
476 end if;
477
478 if (type_code = 0) then
479 message('Cannot create an Internal Manager');
480 raise bad_parameter;
481 end if;
482
483 /* Check cache size */
484 if (type_code <> 1 and cache_size is not null) then
485 message('cache_size cannot be specified for a '||type);
486 raise bad_parameter;
487 end if;
488
489 if (cache_size is not null and cache_size < 0) then
490 message('Invalid cache_size: '||to_char(cache_size));
491 raise bad_parameter;
492 end if;
493
494 /* Check data group */
495 if (type_code <> 3 and
496 (data_group is not null or data_group_id is not null)) then
497 message('Specify a data group only for Transaction Managers');
498 raise bad_parameter;
499 elsif (type_code = 3 and data_group is null
500 and data_group_id is null) then
501 message('Data group must be specified for Transaction Managers');
502 raise bad_parameter;
503 end if;
504
505 if (data_group_id is not null) then
506 begin
507 select fdg.data_group_id
508 into dg_id
509 from fnd_data_groups fdg
510 where fdg.data_group_id = register.data_group_id;
511 exception
512 when no_data_found then
513 message('Cannot find data group with id: '||data_group_id);
514 raise bad_parameter;
515 end;
516 elsif (data_group is not null) then
517 begin
518 select fdg.data_group_id
519 into dg_id
520 from fnd_data_groups fdg
521 where data_group = data_group_name;
522 exception
523 when no_data_found then
524 message('Cannot find data group: '||data_group);
525 raise bad_parameter;
526 end;
527 end if;
528
529 /* Get Library */
530 lib_id := library_id_f(lib_appl_id, library);
531
532 /* Make sure no bad data in tl table */
533 delete from fnd_concurrent_queues_tl
534 where application_id = mgr_appl_id
535 and language in (select l.language_code
536 from fnd_languages l
537 where l.installed_flag in ('I', 'B'))
538 and (concurrent_queue_name = short_name
539 or user_concurrent_queue_name = manager);
540
541 /* Do the insert */
542 insert into fnd_concurrent_queues
543 (application_id, concurrent_queue_id, concurrent_queue_name,
544 processor_application_id, concurrent_processor_id,
545 running_processes, max_processes, creation_date, created_by,
546 last_update_date, last_updated_by, last_update_login,
547 cache_size, control_code, manager_type,
548 node_name, node_name2, os_queue, os_queue2, data_group_id,
549 enabled_flag)
550 values
551 (mgr_appl_id, mgr_id, short_name,
552 lib_appl_id, lib_id,
553 0, 0, creation_date, created_by,
554 l_update_date, l_updated_by, last_update_login,
555 cache_size, 'E', type_code,
556 primary_node, secondary_node, primary_queue, secondary_queue,
557 dg_id, 'Y');
558
559 insert into FND_CONCURRENT_QUEUES_TL (
560 user_concurrent_queue_name, application_id, concurrent_queue_id,
561 concurrent_queue_name, creation_date, created_by,
562 last_update_date, last_updated_by, last_update_login,
563 description, language, source_lang
564 ) select
565 manager, mgr_appl_id, mgr_id, short_name,
566 register.creation_date, register.created_by, l_update_date,
567 l_updated_by, register.last_update_login, register.description,
568 l.language_code, register.language_code
569 from FND_LANGUAGES L
570 where L.INSTALLED_FLAG in ('I', 'B')
571 and not exists
572 (select NULL
573 from FND_CONCURRENT_QUEUES_TL T
574 where T.APPLICATION_ID = mgr_appl_id
575 and T.CONCURRENT_QUEUE_ID = mgr_id
576 and T.LANGUAGE = L.LANGUAGE_CODE);
577
578 end register;
579
580 -- Procedure
581 -- REGISTER_SVC
582 --
583 -- Purpose
584 -- Register a Service .
585 --
586 -- Arguments
587 -- Service_name - Service name.
588 -- Service_Handle - Service Handle
589 -- DESCRIPTION
590 -- CARTRIDGE_HANDLE
591 -- ALLOW_MULTIPLE_PROC_INSTANCE -Y/N
592 -- ALLOW_MULTIPLE_PROC_NODE -Y/N
593 -- MIGRATE_ON_FAILURE -Y/N
594 -- ALLOW_SUSPEND -Y/N
595 -- ALLOW_VERIFY -Y/N
596 -- ALLOW_PARAMETER -Y/N
597 -- ALLOW_START -Y/N
598 -- ALLOW_RESTART -Y/N
599 -- ALLOW_RCG -Y/N
600 -- ALLOW_CREATE - Y/N
601 -- ALLOW_EDIT - Y/N
602 -- PARAMETER_CHANGE_ACTION V=Verify, R=Restart
603 -- DEVELOPER_PARAMETERS
604 -- SERVER_TYPE
605 -- ENV_FILE_NAME -might not be used
606 -- SERVICE_CLASS
607 -- SERVICE_INSTANCE_CLASS
608 -- OAM_DISPLAY_ORDER -if null default to max(OAM_DISPLAY_ORDER) +10
609 -- language_code IN VARCHAR2 DEFAULT 'US'
610 -- DEBUG_CHANGE_ACTION N=No followup action V=Verify X=Dynamic change off
611 -- ENABLED -Y/N
615 -- ALLOW_MULTIPLE_PROC_SI -Y/N
612 -- CARTRIDGE_APPLICATION - Defaults to 'FND'
613 -- DEBUG_TYPE
614 -- SERVICE_PLURAL_NAME
616 -- DEFAULT_DEBUG_LEVEL
617
618 PROCEDURE register_svc (
619 SERVICE_NAME IN VARCHAR2,
620 SERVICE_HANDLE IN VARCHAR2,
621 DESCRIPTION IN VARCHAR2 DEFAULT NULL,
622 CARTRIDGE_HANDLE IN VARCHAR2,
623 ALLOW_MULTIPLE_PROC_INSTANCE IN VARCHAR2 DEFAULT 'Y',
624 ALLOW_MULTIPLE_PROC_NODE IN VARCHAR2 DEFAULT 'Y',
625 MIGRATE_ON_FAILURE IN VARCHAR2 DEFAULT 'Y',
626 ALLOW_SUSPEND IN VARCHAR2 DEFAULT 'Y',
627 ALLOW_VERIFY IN VARCHAR2 DEFAULT 'Y',
628 ALLOW_PARAMETER IN VARCHAR2 DEFAULT 'Y',
629 ALLOW_START IN VARCHAR2 DEFAULT 'Y',
630 ALLOW_RESTART IN VARCHAR2 DEFAULT 'Y',
631 ALLOW_RCG IN VARCHAR2 DEFAULT 'Y',
632 ALLOW_CREATE IN VARCHAR2 DEFAULT 'Y',
633 ALLOW_EDIT IN VARCHAR2 DEFAULT 'Y',
634 PARAMETER_CHANGE_ACTION IN VARCHAR2 DEFAULT 'V',
635 DEVELOPER_PARAMETERS IN VARCHAR2 DEFAULT NULL,
636 SERVER_TYPE IN VARCHAR2 DEFAULT 'C',
637 language_code IN VARCHAR2 DEFAULT 'US',
638 ENV_FILE_NAME IN VARCHAR2 DEFAULT NULL,
639 SERVICE_CLASS IN VARCHAR2 DEFAULT NULL,
640 SERVICE_INSTANCE_CLASS IN VARCHAR2 DEFAULT NULL,
641 OAM_DISPLAY_ORDER IN NUMBER DEFAULT NULL,
642 DEBUG_CHANGE_ACTION IN VARCHAR2 DEFAULT 'N',
643 ENABLED IN VARCHAR2 DEFAULT 'Y',
644 CARTRIDGE_APPLICATION IN VARCHAR2 DEFAULT NULL,
645 DEBUG_TYPE IN VARCHAR2 DEFAULT NULL,
646 SERVICE_PLURAL_NAME IN VARCHAR2 DEFAULT NULL,
647 ALLOW_MULTIPLE_PROC_SI IN VARCHAR2 DEFAULT 'Y',
648 DEFAULT_DEBUG_LEVEL IN VARCHAR2 DEFAULT NULL,
649 last_updated_by IN NUMBER DEFAULT NULL) is
650
651
652 svc_id number;
653 oam_d_o number;
654 cart_appl_id number := 0;
655 LAST_UPDATE_DATE_v date;
656 LAST_UPDATED_BY_v number;
657 LAST_UPDATE_LOGIN_v number;
658 CREATION_DATE_v date;
659 CREATED_BY_v number;
660
661 begin
662
663 check_notnull(SERVICE_NAME);
664 check_notnull(SERVICE_HANDLE);
665 check_notnull(CARTRIDGE_HANDLE);
666
667 LAST_UPDATE_DATE_v := LAST_UPDATE_DATE_f;
668 LAST_UPDATED_BY_v := last_updated_by_f(last_updated_by);
669 LAST_UPDATE_LOGIN_v := LAST_UPDATE_LOGIN_f;
670 CREATION_DATE_v := CREATION_DATE_f;
671 CREATED_BY_v := CREATED_BY_f;
672
673 /* Get new ID */
674 if ( service_handle = 'FNDICM' ) then
675 svc_id := 0;
676 elsif ( service_handle = 'FNDRPM' ) then
677 svc_id := 1;
678 elsif (service_handle = 'FNDIM') then
679 svc_id := 2;
680 elsif ( service_handle = 'FNDTM' ) then
681 svc_id := 3;
682 elsif ( service_handle = 'FNDCRM' ) then
683 svc_id := 4;
684 elsif ( service_handle = 'FNDSCH' ) then
685 svc_id := 5;
686 elsif ( service_handle = 'FNDSM' ) then
687 svc_id := 6;
688 else
689 select fnd_cp_services_s.nextval
690 into svc_id
691 from sys.dual;
692 end if;
693
694 /* Default OAM_DISPLAY_ORDER */
695
696 if ( OAM_DISPLAY_ORDER is NULL) then
697 select (NVL(max(OAM_DISPLAY_ORDER),0) + 10)
698 into oam_d_o
699 from FND_CP_SERVICES;
700 else
701 oam_d_o := OAM_DISPLAY_ORDER;
702 end if;
703
704 /* Get Cartridge Application Id */
705
706 if (CARTRIDGE_APPLICATION is not NULL) then
707 begin
708 select APPLICATION_ID
709 into cart_appl_id
710 from FND_APPLICATION
711 where APPLICATION_SHORT_NAME = CARTRIDGE_APPLICATION;
712 exception
713 when others then
714 cart_appl_id := 0;
715 end;
716 end if;
717
718 /* Do the insert */
719 insert into fnd_cp_services
720 (SERVICE_ID, SERVICE_HANDLE, ALLOW_MULTIPLE_PROC_INSTANCE,
721 ALLOW_MULTIPLE_PROC_NODE, MIGRATE_ON_FAILURE, SERVER_TYPE,
722 ALLOW_SUSPEND, ALLOW_VERIFY, ALLOW_PARAMETER, ALLOW_START,
723 ALLOW_RESTART, PARAMETER_CHANGE_ACTION, LAST_UPDATE_DATE,
724 LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY,
725 ENV_FILE_NAME, CARTRIDGE_HANDLE, DEVELOPER_PARAMETERS,
726 ALLOW_RCG, ALLOW_CREATE, ALLOW_EDIT, SERVICE_CLASS,
727 SERVICE_INSTANCE_CLASS, OAM_DISPLAY_ORDER, DEBUG_CHANGE_ACTION,
728 ENABLED, CARTRIDGE_APPLICATION_ID,DEBUG_TYPE,
729 ALLOW_MULTIPLE_PROC_SI, DEFAULT_DEBUG_LEVEL)
730 Select svc_id, SERVICE_HANDLE, ALLOW_MULTIPLE_PROC_INSTANCE,
731 ALLOW_MULTIPLE_PROC_NODE, MIGRATE_ON_FAILURE, SERVER_TYPE,
732 ALLOW_SUSPEND, ALLOW_VERIFY, ALLOW_PARAMETER, ALLOW_START,
733 ALLOW_RESTART, PARAMETER_CHANGE_ACTION, LAST_UPDATE_DATE_v,
734 LAST_UPDATED_BY_v, LAST_UPDATE_LOGIN_v, CREATION_DATE_v,
735 CREATED_BY_v,
736 ENV_FILE_NAME, CARTRIDGE_HANDLE, DEVELOPER_PARAMETERS,
737 ALLOW_RCG, ALLOW_CREATE, ALLOW_EDIT, SERVICE_CLASS,
738 SERVICE_INSTANCE_CLASS, oam_d_o, DEBUG_CHANGE_ACTION,
739 ENABLED, cart_appl_id, DEBUG_TYPE, ALLOW_MULTIPLE_PROC_SI,
740 DEFAULT_DEBUG_LEVEL
741 from sys.dual;
742
746 SERVICE_PLURAL_NAME)
743 insert into fnd_cp_services_tl (
744 SERVICE_ID, LANGUAGE, SOURCE_LANG, SERVICE_NAME, LAST_UPDATE_DATE,
745 LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY, DESCRIPTION,
747 select
748 svc_id, l.language_code, register_svc.language_code, SERVICE_NAME,
749 LAST_UPDATE_DATE_v, LAST_UPDATED_BY_v,LAST_UPDATE_LOGIN_v,
750 CREATION_DATE_v, CREATED_BY_v, DESCRIPTION,SERVICE_PLURAL_NAME
751 from FND_LANGUAGES L
752 where L.INSTALLED_FLAG in ('I', 'B')
753 and not exists
754 (select NULL
755 from fnd_cp_services_tl T
756 where T.SERVICE_ID = svc_id
757 and T.LANGUAGE = L.LANGUAGE_CODE);
758
759 end register_svc;
760
761 -- Procedure
762 -- REGISTER_SI
763 --
764 -- Purpose
765 -- Register a Service Instance.
766 --
767 -- Arguments
768 -- Manager - Service Instance name.
769 -- Application - Manager application short name.
770 -- Short_Name - Short (non-translated) name
771 -- Description - Manager description (Optional).
772 -- Service_Type -
773 -- Primary_Node - Primary node (optional).
774 -- Primary_Queue - Primary OS queue (Optional).
775 -- Secondary_Node - Secondary node (optional).
776 -- Secondary_Queue - Secondary OS queue (Optional).
777 -- Language_code - Langauge code for translated values.
778 -- Last_Update_Date- Who information for FNDLOAD standards
779 -- Last_Updated_By - Who information for FNDLOAD standards
780 --
781 PROCEDURE register_si (manager IN VARCHAR2,
782 application IN VARCHAR2,
783 short_name IN VARCHAR2,
784 description IN VARCHAR2 DEFAULT NULL,
785 service_handle IN VARCHAR2,
786 primary_node IN VARCHAR2 DEFAULT NULL,
787 primary_queue IN VARCHAR2 DEFAULT NULL,
788 secondary_node IN VARCHAR2 DEFAULT NULL,
789 secondary_queue IN VARCHAR2 DEFAULT NULL,
790 language_code IN VARCHAR2 DEFAULT 'US',
791 last_update_date IN DATE DEFAULT NULL,
792 last_updated_by IN VARCHAR2 DEFAULT NULL) is
793
794 svc_id number;
795 si_id number;
796 mgr_appl_id number;
797
798
799 begin
800 /* have register do most of the work- pretend it is a copy of FNDLIBR */
801 register(manager, application, short_name, description,
802 'Concurrent Manager', NULL, NULL, primary_node, primary_queue,
803 secondary_node, secondary_queue, 'FNDSVC', 'FND', NULL, language_code,
804 last_update_date, last_updated_by);
805
806 mgr_appl_id := application_id_f(application);
807 si_id := manager_id_f(mgr_appl_id, short_name);
808 svc_id := service_id_f(service_handle);
809
810 update fnd_concurrent_queues
811 set manager_type = svc_id
812 where APPLICATION_ID = mgr_appl_id
813 and CONCURRENT_QUEUE_ID = si_id;
814
815 end register_si;
816
817 -- Procedure
818 -- ASSIGN_WORK_SHIFT
819 --
820 -- Purpose
821 -- Assign a work shift to a manager.
822 --
823 -- Arguments
824 -- Manager_Short_Name - Concurrent manager short name.
825 -- Manager_Application - Manager application short name.
826 -- Work_Shift - Work shift name.
827 -- Processes - Number of concurrent processes.
828 -- Sleep_Seconds - Length of sleep interval.
829 -- Work_Shift_ID - ID of Work Shift (Optional, overrides parameter
830 -- 'work_shift')
831 -- Last_Update_Date- Who information for FNDLOAD standards
832 -- Last_Updated_By - Who information for FNDLOAD standards
833 --
834 PROCEDURE assign_work_shift(manager_short_name IN VARCHAR2,
835 manager_application IN VARCHAR2,
836 work_shift IN VARCHAR2 DEFAULT NULL,
837 processes IN NUMBER,
838 sleep_seconds IN NUMBER,
839 work_shift_id IN NUMBER DEFAULT NULL,
840 svc_params in VARCHAR2 DEFAULT NULL,
841 last_update_date IN DATE DEFAULT NULL,
842 last_updated_by IN VARCHAR2 DEFAULT NULL) is
843
844 mgr_appl_id fnd_application.application_id%TYPE;
845 mgr_id fnd_concurrent_queues.concurrent_queue_id%TYPE;
846 ws_id fnd_concurrent_time_periods.concurrent_time_period_id%TYPE;
847 dg_id fnd_data_groups.data_group_id%TYPE;
848 lib_appl_id fnd_application.application_id%TYPE;
849 lib_id fnd_concurrent_processors.concurrent_processor_id%TYPE;
850 mgr_type fnd_concurrent_queues.manager_type%TYPE;
851
852 last_update_login fnd_concurrent_queue_size.last_update_login%TYPE
853 := last_update_login_f;
854 l_update_date fnd_concurrent_queue_size.last_update_date%TYPE
855 := last_update_date_f(last_update_date);
856 l_updated_by fnd_concurrent_queue_size.last_updated_by%TYPE
857 := last_updated_by_f(last_updated_by);
858 creation_date fnd_concurrent_queue_size.creation_date%TYPE
859 := creation_date_f(last_update_date);
860 created_by fnd_concurrent_queue_size.created_by%TYPE
861 := created_by_f(last_updated_by);
862 begin
866 check_notnull(manager_application);
863 message_init;
864
865 check_notnull(manager_short_name);
867 check_notnull(processes);
868 check_notnull(sleep_seconds);
869
870 if (last_update_date IS NOT NULL) then
871 l_update_date := last_update_date;
872 creation_date := last_update_date;
873 end if;
874
875 if (last_updated_by IS NOT NULL) then
876 l_updated_by := last_updated_by;
877 created_by := last_updated_by;
878 end if;
879
880 /* Get manager ID */
881 mgr_appl_id := application_id_f(manager_application);
882 mgr_id := manager_id_f(mgr_appl_id, manager_short_name);
883
884 /* Check type */
885 select manager_type
886 into mgr_type
887 from fnd_concurrent_queues
888 where application_id = mgr_appl_id
889 and concurrent_queue_id = mgr_id;
890
891 if (mgr_type = 0) then
892 message('Work Shifts cannot be assigned to the ICM.');
893 raise bad_parameter;
894 end if;
895
896 /* Get work shift ID */
897 begin
898 if (work_shift_id is not null) then
899 select concurrent_time_period_id
900 into ws_id
901 from fnd_concurrent_time_periods
902 where concurrent_time_period_id = work_shift_id
903 and application_id = 0;
904 else
905 select concurrent_time_period_id
906 into ws_id
907 from fnd_concurrent_time_periods
908 where concurrent_time_period_name = work_shift
909 and application_id = 0;
910 end if;
911 exception
912 when no_data_found then
913 if (work_shift_id is not null) then
914 message('Could not find work shift with ID: '||work_shift_id);
915 else
916 message('Could not find work shift: '||work_shift);
917 end if;
918 raise bad_parameter;
919 end;
920
921 /* Check processes */
922 if (processes < 0) then
923 message('Invalid number of processes: '||to_char(processes));
924 raise bad_parameter;
925 end if;
926
927 /* Check sleep seconds */
928 if (sleep_seconds < 0) then
929 message('Invalid sleep seconds: '||to_char(sleep_seconds));
930 raise bad_parameter;
931 end if;
932
933 /* do the insert */
934 insert into fnd_concurrent_queue_size
935 (queue_application_id, concurrent_queue_id,
936 period_application_id, concurrent_time_period_id,
937 last_update_date, last_updated_by, creation_date,
938 last_update_login, created_by, min_processes, sleep_seconds,
939 SERVICE_PARAMETERS)
940 values
941 (mgr_appl_id, mgr_id, 0, ws_id,
942 l_update_date, l_updated_by, creation_date,
943 last_update_login, created_by, processes, sleep_seconds,
944 svc_params);
945
946 end assign_work_shift;
947
948
949 -- Procedure
950 -- CREATE_LIBRARY
951 --
952 -- Purpose
953 -- Create a concurrent program library.
954 --
955 -- Arguments
956 -- Library - Library name.
957 -- Application - Library application short name.
958 -- Description - Library description.
959 -- Type - 'Concurrent Programs Library' or
960 -- 'Transaction Programs Library '
961 --
962 PROCEDURE create_library(library IN VARCHAR2,
963 application IN VARCHAR2,
964 description IN VARCHAR2 DEFAULT NULL,
965 type IN VARCHAR2) is
966
967 lib_appl_id fnd_application.application_id%TYPE;
968 lib_id fnd_concurrent_processors.concurrent_processor_id%TYPE;
969 type_code fnd_concurrent_processors.library_type%type;
970
971 last_update_login fnd_concurrent_queues.last_update_login%TYPE
972 := last_update_login_f;
973 last_update_date fnd_concurrent_queues.last_update_date%TYPE
974 := last_update_date_f;
975 last_updated_by fnd_concurrent_queues.last_updated_by%TYPE
976 := last_updated_by_f;
977 creation_date fnd_concurrent_queues.creation_date%TYPE
978 := creation_date_f;
979 created_by fnd_concurrent_queues.created_by%TYPE
980 := created_by_f;
981 begin
982 message_init;
983
984 check_notnull(library);
985 check_notnull(application);
986 check_notnull(type);
987
988 lib_appl_id := application_id_f(application);
989
990 /* Get type */
991 begin
992 select lookup_code
993 into type_code
994 from fnd_lookup_values
995 where lookup_type = 'CP_PROGRAM_LIBRARY_TYPE'
996 and upper(meaning) = upper(type)
997 and rownum = 1;
998 exception
999 when no_data_found then
1000 message('Invalid library type: ' || type);
1001 raise bad_parameter;
1002 end;
1003
1004 /* Get new library ID */
1005 select fnd_concurrent_processors_s.nextval
1006 into lib_id
1007 from sys.dual;
1008
1009 insert into fnd_concurrent_processors
1010 (application_id, concurrent_processor_id,
1011 concurrent_processor_name, last_update_date,
1012 last_updated_by, creation_date, created_by,
1013 last_update_login, description, library_type)
1014 values
1015 (lib_appl_id, lib_id, library, last_update_date,
1016 last_updated_by, creation_date, created_by,
1020
1017 last_update_login, description, type_code);
1018
1019 end create_library;
1021
1022 -- Procedure
1023 -- ADD_PROGRAM_TO_LIBRARY
1024 --
1025 -- Purpose
1026 -- Add a concurrent program to a concurrent library.
1027 --
1028 -- Arguments
1029 -- Library - Library name.
1030 -- Library_Application - Library application short name.
1031 -- Program - Program short name.
1032 -- Program_Application - Program application short name.
1033 --
1034 PROCEDURE add_program_to_library(library IN VARCHAR2,
1035 library_application IN VARCHAR2,
1036 program IN VARCHAR2,
1037 program_application IN VARCHAR2)
1038 is
1039 lib_appl_id fnd_application.application_id%TYPE;
1040 lib_id fnd_concurrent_processors.concurrent_processor_id%TYPE;
1041 prg_appl_id fnd_application.application_id%TYPE;
1042 prg_id fnd_concurrent_programs.concurrent_program_id%TYPE;
1043
1044 last_update_login fnd_concurrent_queues.last_update_login%TYPE
1045 := last_update_login_f;
1046 last_update_date fnd_concurrent_queues.last_update_date%TYPE
1047 := last_update_date_f;
1048 last_updated_by fnd_concurrent_queues.last_updated_by%TYPE
1049 := last_updated_by_f;
1050 creation_date fnd_concurrent_queues.creation_date%TYPE
1051 := creation_date_f;
1052 created_by fnd_concurrent_queues.created_by%TYPE
1053 := created_by_f;
1054 begin
1055 message_init;
1056
1057 check_notnull(library);
1058 check_notnull(library_application);
1059 check_notnull(program);
1060 check_notnull(program_application);
1061
1062 lib_appl_id := application_id_f(library_application);
1063 lib_id := library_id_f(lib_appl_id, library);
1064 prg_appl_id := application_id_f(program_application);
1065 prg_id := concurrent_program_id_f(prg_appl_id, program);
1066
1067 insert into fnd_conc_processor_programs
1068 (processor_application_id, concurrent_processor_id,
1069 program_application_id, concurrent_program_id,
1070 last_update_date, last_updated_by, creation_date,
1071 created_by, last_update_login)
1072 values
1073 (lib_appl_id, lib_id, prg_appl_id, prg_id,
1074 last_update_date, last_updated_by, creation_date,
1075 created_by, last_update_login);
1076 end add_program_to_library;
1077
1078
1079 -- Procedure
1080 -- SPECIALIZE
1081 --
1082 -- Purpose
1083 -- Register a specialization rule for a manager.
1084 --
1085 -- Arguments
1086 -- manager_short_name - Concurrent manager name.
1087 -- manager_application - Manager application short name.
1088 -- action - 'Include' or 'Exclude'.
1089 -- object_type - 'Combined Rule', 'Oracle ID', 'Program',
1090 -- 'Request Type', 'User'.
1091 -- object_name - Name of the object being included or excluded.
1092 -- (Short name for Programs.)
1093 -- object_application - Application short name of the object being
1094 -- included or excluded. (Not used for Oracle IDs
1095 -- or Users.)
1096 -- Last_Update_Date- Who information for FNDLOAD standards
1097 -- Last_Updated_By - Who information for FNDLOAD standards
1098 --
1099 PROCEDURE specialize(manager_short_name in VARCHAR2,
1100 manager_application in VARCHAR2,
1101 action in VARCHAR2,
1102 object_type in VARCHAR2,
1103 object_name in VARCHAR2 DEFAULT NULL,
1104 object_application in VARCHAR2 DEFAULT NULL,
1105 last_update_date in DATE DEFAULT NULL,
1106 last_updated_by in NUMBER DEFAULT NULL) is
1107 mgr_appl_id fnd_application.application_id%TYPE;
1108 mgr_id fnd_concurrent_queues.concurrent_queue_id%type;
1109 action_code fnd_concurrent_queue_content.include_flag%type;
1110 type_code fnd_concurrent_queue_content.type_code%type;
1111 type_id fnd_concurrent_queue_content.type_id%type;
1112 type_appl_id fnd_application.application_id%TYPE;
1113 mgr_type fnd_concurrent_queues.manager_type%TYPE;
1114
1115 last_update_login fnd_concurrent_queue_content.last_update_login%TYPE
1116 := last_update_login_f;
1117 l_update_date fnd_concurrent_queue_content.last_update_date%TYPE
1118 := last_update_date_f(last_update_date);
1119 l_updated_by fnd_concurrent_queue_content.last_updated_by%TYPE
1120 := last_updated_by_f(last_updated_by);
1121 creation_date fnd_concurrent_queue_content.creation_date%TYPE
1122 := creation_date_f(last_update_date);
1123 created_by fnd_concurrent_queue_content.created_by%TYPE
1124 := created_by_f(last_updated_by);
1125
1126 begin
1127 message_init;
1128
1129 check_notnull(manager_application);
1130 check_notnull(manager_short_name);
1131 check_notnull(action);
1132 check_notnull(object_type);
1133
1134 mgr_appl_id := application_id_f(manager_application);
1135 mgr_id := manager_id_f(mgr_appl_id, manager_short_name);
1136
1137 if (last_update_date IS NOT NULL) then
1138 l_update_date := last_update_date;
1139 creation_date := last_update_date;
1140 end if;
1141
1142 if (last_updated_by IS NOT NULL) then
1143 l_updated_by := last_updated_by;
1144 created_by := last_updated_by;
1145 end if;
1146
1147
1148 /* Check manager type */
1152 where application_id = mgr_appl_id
1149 select manager_type
1150 into mgr_type
1151 from fnd_concurrent_queues
1153 and concurrent_queue_id = mgr_id;
1154
1155 if (mgr_type <> 1) then
1156 message('This manager is of a type which cannot be specialized.');
1157 raise bad_parameter;
1158 end if;
1159
1160 /* Get action code */
1161 begin
1162 select lookup_code
1163 into action_code
1164 from fnd_lookup_values
1165 where lookup_type = 'INCLUDE_EXCLUDE'
1166 and upper(meaning) = upper(action)
1167 and rownum = 1;
1168 exception
1169 when no_data_found then
1170 message('Invalid action: '||action);
1171 raise bad_parameter;
1172 end;
1173
1174 /* Get object type */
1175 begin
1176 select lookup_code
1177 into type_code
1178 from fnd_lookup_values
1179 where lookup_type = 'CP_SPECIAL_RULES'
1180 and upper(meaning) = upper(object_type)
1181 and lookup_code in ('C','O','P','R','U')
1182 and rownum = 1;
1183 exception
1184 when no_data_found then
1185 message('Invalid object type: '||object_type);
1186 raise bad_parameter;
1187 end;
1188
1189 /* get object id */
1190 if (object_application is null) then
1191 if (type_code in ('C','P','R')) then
1192 message('Application required for object of type '||object_type);
1193 raise bad_parameter;
1194 end if;
1195 elsif (type_code in ('O', 'U')) then
1196 message('Application cannot be specified for an object of type: '
1197 || object_type);
1198 raise bad_parameter;
1199 else
1200 type_appl_id := application_id_f(object_application);
1201 end if;
1202
1203 type_id := get_specialization_type_id(object_name,
1204 type_appl_id,
1205 type_code);
1206
1207 insert into fnd_concurrent_queue_content
1208 (queue_application_id, concurrent_queue_id, type_code,
1209 type_application_id, type_id, last_update_date,
1210 last_updated_by, creation_date, created_by,
1211 last_update_login, include_flag)
1212 values (mgr_appl_id, mgr_id, type_code,
1213 type_appl_id, type_id, l_update_date,
1214 l_updated_by, creation_date, created_by,
1215 last_update_login, action_code);
1216 end specialize;
1217
1218
1219 -- Function
1220 -- MANAGER_EXISTS
1221 --
1222 -- Purpose
1223 -- Return TRUE if a manager exists.
1224 --
1225 -- Arguments
1226 -- Manager_Short_Name - Manager short name.
1227 -- Application - Manager application short name.
1228 --
1229 FUNCTION manager_exists(manager_short_name IN VARCHAR2,
1230 application IN VARCHAR2)
1231 RETURN BOOLEAN is
1232 mgr_appl_id fnd_application.application_id%TYPE;
1233 dummy varchar2(4);
1234 begin
1235 message_init;
1236
1237 mgr_appl_id := application_id_f(application);
1238
1239 select 'Y'
1240 into dummy
1241 from fnd_concurrent_queues
1242 where application_id = mgr_appl_id
1243 and concurrent_queue_name = manager_short_name;
1244
1245 return TRUE;
1246 exception
1247 when others then
1248 return FALSE;
1249 end manager_exists;
1250
1251 -- Function
1252 -- SERVICE_EXISTS
1253 --
1254 -- Purpose
1255 -- Return TRUE if a service exists.
1256 --
1257 -- Arguments
1258 -- svc_handle - service_handle.
1259 --
1260 FUNCTION Service_exists(svc_handle IN VARCHAR2) RETURN BOOLEAN is
1261 dummy varchar2(4);
1262 begin
1263 message_init;
1264
1265 select 'Y'
1266 into dummy
1267 from fnd_cp_services
1268 where svc_handle = SERVICE_HANDLE;
1269
1270 return TRUE;
1271 exception
1272 when others then
1273 return FALSE;
1274 end Service_exists;
1275
1276 -- Function
1277 -- MANAGER_WORK_SHIFT_EXISTS
1278 --
1279 -- Purpose
1280 -- Return TRUE if a manager has an assignment for a work shift.
1281 --
1282 -- Arguments
1283 -- Manager_Short_Name - Manager short name.
1284 -- Manager_Application - Manager application short name.
1285 -- Work_Shift - Work shift name.
1286 -- Work_Shift_ID - ID of Work Shift (Optional, overrides parameter
1287 -- 'work_shift')
1288 --
1289 FUNCTION manager_work_shift_exists(
1290 manager_short_name IN VARCHAR2,
1291 manager_application IN VARCHAR2,
1292 work_shift IN VARCHAR2 DEFAULT NULL,
1293 work_shift_id IN NUMBER DEFAULT NULL)
1294 RETURN BOOLEAN is
1295 mgr_appl_id fnd_application.application_id%TYPE;
1296 mgr_id fnd_concurrent_queues.concurrent_queue_id%TYPE;
1297 dummy varchar2(4);
1298 begin
1299 message_init;
1300
1301 mgr_appl_id := application_id_f(manager_application);
1302 mgr_id := manager_id_f(mgr_appl_id, manager_short_name);
1303
1304 if (work_shift_id is null) then
1305 select 'Y'
1306 into dummy
1307 from fnd_concurrent_queue_size qs,
1308 fnd_concurrent_time_periods tp
1309 where tp.concurrent_time_period_name = work_shift
1313 and qs.queue_application_id = mgr_appl_id
1310 and tp.application_id = 0
1311 and tp.concurrent_time_period_id = qs.concurrent_time_period_id
1312 and qs.concurrent_queue_id = mgr_id
1314 and rownum = 1;
1315 else
1316 select 'Y'
1317 into dummy
1318 from fnd_concurrent_queue_size
1319 where work_shift_id = concurrent_time_period_id
1320 and concurrent_queue_id = mgr_id
1321 and queue_application_id = mgr_appl_id
1322 and rownum = 1;
1323 end if;
1324
1325 return TRUE;
1326
1327 exception
1328 when others then
1329 return FALSE;
1330 end manager_work_shift_exists;
1331
1332
1333 -- Function
1334 -- LIBRARY_EXISTS
1335 --
1336 -- Purpose
1337 -- Return TRUE if a library exists.
1338 --
1339 -- Arguments
1340 -- Library - Library name.
1341 -- Application - Library application short name.
1342 --
1343 FUNCTION library_exists(library IN VARCHAR2,
1344 application IN VARCHAR2)
1345 RETURN BOOLEAN is
1346 lib_appl_id fnd_application.application_id%TYPE;
1347 dummy varchar2(4);
1348 begin
1349 message_init;
1350
1351 lib_appl_id := application_id_f(application);
1352
1353 select 'Y'
1354 into dummy
1355 from fnd_concurrent_processors
1356 where application_id = lib_appl_id
1357 and concurrent_processor_name = library;
1358
1359 return TRUE;
1360
1361 exception
1362 when others then
1363 return FALSE;
1364 end library_exists;
1365
1366
1367 -- Function
1368 -- PROGRAM_IN_LIBRARY
1369 --
1370 -- Purpose
1371 -- Return TRUE if a library exists.
1372 --
1373 -- Arguments
1374 -- Library - Library name.
1375 -- Library_Application - Library application short name.
1376 -- Program - Program short name.
1377 -- Program_Application - Program application short name.
1378 --
1379 FUNCTION program_in_library(library IN VARCHAR2,
1380 library_application IN VARCHAR2,
1381 program IN VARCHAR2,
1382 program_application IN VARCHAR2)
1383 RETURN BOOLEAN is
1384 lib_appl_id fnd_application.application_id%TYPE;
1385 prg_appl_id fnd_application.application_id%TYPE;
1386 lib_id fnd_concurrent_processors.concurrent_processor_id%TYPE;
1387 dummy varchar2(4);
1388 begin
1389 message_init;
1390
1391 lib_appl_id := application_id_f(library_application);
1392 prg_appl_id := application_id_f(program_application);
1393 lib_id := library_id_f(lib_appl_id, library);
1394
1395 select 'Y'
1396 into dummy
1397 from fnd_conc_processor_programs pp,
1398 fnd_concurrent_programs cp
1399 where pp.processor_application_id = lib_appl_id
1400 and pp.concurrent_processor_id = lib_id
1401 and pp.concurrent_program_id = cp.concurrent_program_id
1402 and cp.application_id = pp.program_application_id
1403 and cp.application_id = prg_appl_id
1404 and cp.concurrent_program_name = program;
1405
1406 return TRUE;
1407
1408 exception
1409 when others then
1410 return FALSE;
1411 end program_in_library;
1412
1413
1414 -- FUNCTION
1415 -- SPECIALIZATION_EXISTS
1416 --
1417 -- Purpose
1418 -- Check if a manager has been specialized for an object.
1419 --
1420 -- Arguments
1421 -- manager_short_name - Concurrent manager short name.
1422 -- manager_application - Manager application short name.
1423 -- object_type - 'Combined Rule', 'ORACLE ID', 'Program',
1424 -- 'Request Type', 'User'.
1425 -- object_name - Name of the object being included or excluded.
1426 -- (Short name for Programs.)
1427 -- object_application - Application short name of the object being
1428 -- included or excluded. (Not used for Oracle IDs
1429 -- or Users.)
1430 --
1431 FUNCTION specialization_exists(
1432 manager_short_name in VARCHAR2,
1433 manager_application in VARCHAR2,
1434 object_type in VARCHAR2,
1435 object_name in VARCHAR2 DEFAULT NULL,
1436 object_application in VARCHAR2 DEFAULT NULL)
1437 return boolean is
1438
1439 mgr_appl_id fnd_application.application_id%TYPE;
1440 mgr_id fnd_concurrent_queues.concurrent_queue_id%type;
1441 action_code fnd_concurrent_queue_content.include_flag%type;
1442 obj_code fnd_concurrent_queue_content.type_code%type;
1443 obj_id fnd_concurrent_queue_content.type_id%type;
1444 obj_appl_id fnd_application.application_id%TYPE;
1445 mgr_type fnd_concurrent_queues.manager_type%TYPE;
1446 dummy varchar2(4);
1447 begin
1448 message_init;
1449
1450 mgr_appl_id := application_id_f(manager_application);
1451 mgr_id := manager_id_f(mgr_appl_id, manager_short_name);
1452
1453 /* Get object type */
1454 select lookup_code
1455 into obj_code
1456 from fnd_lookup_values
1457 where lookup_type = 'CP_SPECIAL_RULES'
1458 and upper(meaning) = upper(object_type)
1459 and lookup_code in ('C','O','P','R','U')
1460 and rownum = 1;
1461
1462 /* get object id */
1466 raise bad_parameter;
1463 if (object_application is null) then
1464 if (obj_code in ('C','P','R')) then
1465 message('Application required for object of type '||object_type);
1467 end if;
1468 else
1469 obj_appl_id := application_id_f(object_application);
1470 end if;
1471
1472 if (obj_code = 'C') then
1473 select complex_rule_id
1474 into obj_id
1475 from fnd_concurrent_complex_rules
1476 where application_id = obj_appl_id
1477 and complex_rule_name = object_name;
1478 elsif (obj_code = 'O') then
1479 select oracle_id
1480 into obj_id
1481 from fnd_oracle_userid
1482 where oracle_username = object_name;
1483 elsif (obj_code = 'P') then
1484 if (object_name is not null) then
1485 obj_id := concurrent_program_id_f(obj_appl_id, object_name);
1486 end if;
1487 elsif (obj_code = 'R') then
1488 select request_class_id
1489 into obj_id
1490 from fnd_concurrent_request_class
1491 where application_id = obj_appl_id
1492 and request_class_name = object_name;
1493 else
1494 select user_id
1495 into obj_id
1496 from fnd_user
1497 where user_name = object_name;
1498 end if;
1499
1500 obj_id := get_specialization_type_id(object_name,
1501 obj_appl_id,
1502 obj_code);
1503
1504 select 'Y'
1505 into dummy
1506 from fnd_concurrent_queue_content
1507 where concurrent_queue_id = mgr_id
1508 and queue_application_id = mgr_appl_id
1509 and type_code = obj_code
1510 and ((obj_id is null and type_id is null)
1511 or type_id = obj_id)
1512 and ((obj_appl_id is null and type_application_id is null)
1513 or obj_appl_id = type_application_id);
1514
1515 return TRUE;
1516
1517 exception
1518 when others then
1519 return FALSE;
1520 end specialization_exists;
1521
1522
1523 -- Procedure
1524 -- DELETE_MANAGER
1525 --
1526 -- Purpose
1527 -- Delete a concurrent manager and all its dependent data.
1528 --
1529 -- Arguments
1530 -- Manager_short_name - Concurrent manager short name.
1531 -- Application - Manager application short name.
1532 --
1533 -- Warning:
1534 -- This will delete request and process data belonging to the
1535 -- manager.
1536 --
1537 PROCEDURE delete_manager (manager_short_name IN VARCHAR2,
1538 application IN VARCHAR2) is
1539
1540 mgr_appl_id fnd_application.application_id%TYPE;
1541 mgr_id fnd_concurrent_queues.concurrent_queue_id%type;
1542
1543 targetp number := 0;
1544 activep number := 0;
1545 pmon varchar2(10);
1546 callstat number;
1547 begin
1548 message_init;
1549
1550 mgr_appl_id := application_id_f(application);
1551
1552 begin
1553 mgr_id := manager_id_f(mgr_appl_id, manager_short_name);
1554 exception
1555 when others then -- No manager to delete.
1556 message_init;
1557 return;
1558 end;
1559
1560 if (mgr_appl_id = 0 and mgr_id in (1,0,4)) then
1561 message('Cannot delete the Internal or Standard managers.');
1562 raise bad_parameter;
1563 end if;
1564
1565 /* ------------------------------------------------------------ */
1566 /* Bug 2048187: Removed deletes for request and process history */
1567 /* from tables fnd_run_requests, fnd_concurrent_requests, */
1568 /* and fnd_concurrent_processes. */
1569 /* ------------------------------------------------------------ */
1570
1571 /* Specialization Rules */
1572 delete from fnd_concurrent_queue_content
1573 where concurrent_queue_id = mgr_id
1574 and queue_application_id = mgr_appl_id;
1575
1576 /* Work Shifts */
1577 delete from fnd_concurrent_queue_size
1578 where queue_application_id = mgr_appl_id
1579 and concurrent_queue_id = mgr_id;
1580
1581 /* TL tables */
1582 delete from fnd_concurrent_queues_tl
1583 where concurrent_queue_id = mgr_id
1584 and application_id = mgr_appl_id;
1585
1586 /* Manager */
1587 delete from fnd_concurrent_queues
1588 where concurrent_queue_id = mgr_id
1589 and application_id = mgr_appl_id;
1590
1591 end delete_manager;
1592
1593
1594 -- Procedure
1595 -- DELETE_LIBRARY
1596 --
1597 -- Purpose
1598 -- Delete a concurrent program library.
1599 --
1600 -- Arguments
1601 -- Library - Library name.
1602 -- Application - Library application short name.
1603 --
1604 PROCEDURE delete_library(library IN VARCHAR2,
1605 application IN VARCHAR2) is
1606
1607 lib_appl_id fnd_application.application_id%TYPE;
1608 lib_id fnd_concurrent_processors.concurrent_processor_id%TYPE;
1609
1610 i number;
1611 begin
1612 message_init;
1613
1614 lib_appl_id := application_id_f(application);
1615
1616 begin
1617 lib_id := library_id_f(lib_appl_id, library);
1618 exception
1622 end;
1619 when others then -- No library to delete
1620 message_init;
1621 return;
1623
1624 /* Check if in use */
1625 select count(*)
1626 into i
1627 from fnd_concurrent_queues
1628 where concurrent_processor_id = lib_id
1629 and processor_application_id = lib_appl_id;
1630
1631 if ( i > 0 ) then
1632 message('Library ' || library || ' is in use.');
1633 raise bad_parameter;
1634 end if;
1635
1636 /* Delete programs */
1637 delete from fnd_conc_processor_programs
1638 where concurrent_processor_id = lib_id
1639 and processor_application_id = lib_appl_id;
1640
1641 /* Delete library */
1642 delete from fnd_concurrent_processors
1643 where concurrent_processor_id = lib_id
1644 and application_id = lib_appl_id;
1645 end delete_library;
1646
1647
1648 -- Procedure
1649 -- STANDARDIZE
1650 --
1651 -- Purpose
1652 -- Changes the value of a concurrent_queue_id and all references
1653 -- to that ID.
1654 --
1655 -- Arguments
1656 -- Manager_short_name - Concurrent manager short name.
1657 -- Application - Manager application short name.
1658 -- Queue_ID - New ID
1659 --
1660 --
1661 PROCEDURE standardize (manager_short_name IN VARCHAR2,
1662 application IN VARCHAR2,
1663 manager_id IN number) is
1664
1665 mgr_appl_id fnd_application.application_id%TYPE;
1666 mgr_id fnd_concurrent_queues.concurrent_queue_id%type;
1667
1668 dummy number;
1669
1670 BEGIN
1671
1672 message_init;
1673
1674 check_notnull(application);
1675 check_notnull(manager_id);
1676
1677 mgr_appl_id := application_id_f(application);
1678 mgr_id := manager_id_f(mgr_appl_id, manager_short_name);
1679
1680 if (mgr_id = manager_id) then /* Do nothing */
1681 return;
1682 end if;
1683
1684 if (mgr_appl_id = 0 and mgr_id in (1,0,4)) then
1685 message('Cannot change the Internal or Standard managers.');
1686 raise bad_parameter;
1687 end if;
1688
1689 /* Ensure uniqueness */
1690 begin
1691 select 1 into dummy
1692 from sys.dual
1693 where not exists
1694 (select 1
1695 from fnd_concurrent_queues
1696 where concurrent_queue_id = manager_id);
1697 exception
1698 when no_data_found then
1699 message('ID '|| to_char(manager_id) || ' is already in use.');
1700 raise bad_parameter;
1701 end;
1702
1703 /* Process data */
1704 update fnd_concurrent_processes
1705 set concurrent_queue_id = manager_id
1706 where queue_application_id = mgr_appl_id
1707 and concurrent_queue_id = mgr_id;
1708
1709 /* Work Shifts */
1710 update fnd_concurrent_queue_size
1711 set concurrent_queue_id = manager_id
1712 where queue_application_id = mgr_appl_id
1713 and concurrent_queue_id = mgr_id;
1714
1715 /* Process data */
1716 update fnd_concurrent_queue_content
1717 set concurrent_queue_id = manager_id
1718 where queue_application_id = mgr_appl_id
1719 and concurrent_queue_id = mgr_id;
1720
1721 /* TL tables */
1722 update fnd_concurrent_queues_tl
1723 set concurrent_queue_id = manager_id
1724 where application_id = mgr_appl_id
1725 and concurrent_queue_id = mgr_id;
1726
1727 /* manager */
1728 update fnd_concurrent_queues
1729 set concurrent_queue_id = manager_id
1730 where application_id = mgr_appl_id
1731 and concurrent_queue_id = mgr_id;
1732
1733 END standardize;
1734
1735 -- Procedure
1736 -- UPDATE_NODE
1737 --
1738 -- Purpose
1739 -- Set Primary and/or Secondary Node assignment for a Service Instance
1740 --
1741 -- Arguments
1742 --
1743 -- Short_Name - Concurrent Queue Name
1744 -- Application - Application short name
1745 -- Primary_Node - Primary node
1746 -- Secondary_Node - Secondary node
1747 --
1748 -- Node names may be set to null.
1749
1750 PROCEDURE update_node(short_name IN VARCHAR2,
1751 application IN VARCHAR2,
1752 primary_node IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1753 secondary_node IN VARCHAR2 DEFAULT fnd_api.g_miss_char) IS
1754
1755 mgr_appl_id fnd_application.application_id%TYPE;
1756 mgr_id fnd_concurrent_queues.concurrent_queue_id%type;
1757 pnode varchar2(31);
1758 snode varchar2(31);
1759
1760 BEGIN
1761
1762 message_init;
1763
1764 check_notnull(application);
1765 check_notnull(short_name);
1766
1767 if (primary_node = fnd_api.g_miss_char and
1768 secondary_node = fnd_api.g_miss_char) then
1769 return; /* Do nothing. */
1770 end if;
1771
1772 /* Get concurrent queue id */
1773
1774 mgr_appl_id := application_id_f(application);
1775 mgr_id := manager_id_f(mgr_appl_id, short_name);
1776
1777 /* Verify node(s) */
1778
1779 if (primary_node <> fnd_api.g_miss_char and
1780 primary_node is not null) then
1781 pnode := node_name_f(primary_node);
1782 else
1783 pnode := primary_node;
1784 end if;
1785
1786 if (secondary_node <> fnd_api.g_miss_char and
1787 secondary_node is not null) then
1788 snode := node_name_f(secondary_node);
1789 else
1790 snode := secondary_node;
1791 end if;
1792
1793 /* Update */
1794 if (snode = fnd_api.g_miss_char) then /* No secondary */
1795 update fnd_concurrent_queues
1796 set node_name = pnode
1797 where concurrent_queue_id = mgr_id
1798 and application_id = mgr_appl_id;
1799
1800 elsif (pnode = fnd_api.g_miss_char) then /* No primary */
1801 update fnd_concurrent_queues
1802 set node_name2 = snode
1803 where concurrent_queue_id = mgr_id
1804 and application_id = mgr_appl_id;
1805
1806 else /* primary and secondary */
1807 update fnd_concurrent_queues
1808 set node_name = pnode,
1809 node_name2 = snode
1810 where concurrent_queue_id = mgr_id
1811 and application_id = mgr_appl_id;
1812
1813 end if;
1814
1815 END update_node;
1816
1817 end fnd_manager;