[Home] [Help]
PACKAGE BODY: APPS.FND_CP_FNDSM
Source
1 package body fnd_cp_fndsm as
2 /* $Header: AFCPFSMB.pls 120.9.12020000.5 2012/10/11 22:31:09 ckclark ship $ */
3
4 procedure mark_shutdown_fndsm( node IN varchar2)
5 is
6 PRAGMA AUTONOMOUS_TRANSACTION;
7 begin
8 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
9 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
10 'fnd.plsql.FND_CP_FNDSM.MARK_SHUTDOWN_FNDSM',
11 'mark_shutdown_fndsm called for node'||node);
12 end if;
13
14 update FND_CONCURRENT_PROCESSES
15 set PROCESS_STATUS_CODE = 'S',
16 LAST_UPDATE_DATE = sysdate
17 where CONCURRENT_QUEUE_ID =
18 ( select CONCURRENT_QUEUE_ID
19 from FND_CONCURRENT_QUEUES
20 where MANAGER_TYPE = '6'
21 and NODE_NAME = node)
22 and PROCESS_STATUS_CODE not in ('S', 'K');
23 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
24 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
25 'fnd.plsql.FND_CP_FNDSM.MARK_SHUTDOWN_FNDSM',
26 to_char(SQL%ROWCOUNT) ||' fnd_concurrent_processes rows updated');
27 end if;
28
29 update FND_CONCURRENT_QUEUES
30 set running_processes = 0,
31 max_processes = 0,
32 control_code = null
33 where MANAGER_TYPE = '6'
34 and NODE_NAME = node;
35 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
36 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
37 'fnd.plsql.FND_CP_FNDSM.MARK_SHUTDOWN_FNDSM',
38 to_char(SQL%ROWCOUNT) ||' fnd_concurrent_queues rows updated');
39 end if;
40
41 commit;
42
43 exception
44 when others then
45 rollback;
46 raise;
47 end mark_shutdown_fndsm;
48
49 procedure mark_killed_fndsm( node IN varchar2)
50 is
51 PRAGMA AUTONOMOUS_TRANSACTION;
52 begin
53
54 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
55 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
56 'fnd.plsql.FND_CP_FNDSM.MARK_KILLED_FNDSM',
57 'mark_shutdown_fndsm called for node'||node);
58 end if;
59 update FND_CONCURRENT_PROCESSES
60 set PROCESS_STATUS_CODE = 'K',
61 LAST_UPDATE_DATE = sysdate
62 where CONCURRENT_QUEUE_ID =
63 ( select CONCURRENT_QUEUE_ID
64 from FND_CONCURRENT_QUEUES
65 where MANAGER_TYPE = '6'
66 and NODE_NAME = node)
67 and PROCESS_STATUS_CODE not in ('S', 'K');
68 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
69 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
70 'fnd.plsql.FND_CP_FNDSM.MARK_SHUTDOWN_FNDSM',
71 to_char(SQL%ROWCOUNT) ||' fnd_concurrent_processes rows updated');
72 end if;
73
74 update FND_CONCURRENT_QUEUES
75 set running_processes = 0,
76 max_processes = 0,
77 control_code = null
78 where MANAGER_TYPE = 6
79 and NODE_NAME = node;
80 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
81 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
82 'fnd.plsql.FND_CP_FNDSM.MARK_SHUTDOWN_FNDSM',
83 to_char(SQL%ROWCOUNT) ||' fnd_concurrent_queues rows updated');
84 end if;
85
86 commit;
87
88 exception
89 when others then
90 rollback;
91 raise;
92
93 end mark_killed_fndsm;
94
95 procedure shutdown_all_fndsm
96 is
97 begin
98 update FND_CONCURRENT_PROCESSES
99 set PROCESS_STATUS_CODE = 'S',
100 LAST_UPDATE_DATE = sysdate
101 where MANAGER_TYPE = 6
102 and PROCESS_STATUS_CODE not in ('S', 'K');
103
104 update FND_CONCURRENT_QUEUES
105 set running_processes = 0,
106 max_processes = 0,
107 control_code = null
108 where MANAGER_TYPE = '6';
109 end shutdown_all_fndsm;
110
111 /* 2849672- Add IN parameter twotask, so that the process row for each
112 FNDSM will have db_instance populated. */
113 /* 5867853- register_fndsm_fcp is used by ICM to insert FNDSM row, then FNDSM
114 is spawned and uses register_fndsm_db to update the row. This procedure
115 should be an autonomous transaction with a commit so that it is instantly
116 available to FNDSM for update. */
117 procedure register_fndsm_fcp( cpid IN number,
118 node IN varchar2,
119 ospid IN number,
120 logfile IN varchar2,
121 mgrusrid IN number,
122 twotask IN varchar2)
123 is
124 PRAGMA AUTONOMOUS_TRANSACTION;
125 dummy number;
126 begin
127
128 -- Bugfix for 8724518
129 -- 14364164- In case of ICM migration, old ICM may have a lock on this
130 -- FND_CONCURRENT_QUEUES row from mark_shutdown_fndsm or mark_killed_fndsm.
131 -- Wait 5 seconds for old ICM to commmit when selecting fnd_concurrent_queue
132 -- row for update.
133 select 1 into dummy from fnd_concurrent_queues
134 where manager_type = 6 and node_name = node
135 for update of running_processes ,max_processes , control_code
136 wait 5;
137
138 update FND_CONCURRENT_QUEUES
139 set running_processes = 1,
140 max_processes = 1,
141 control_code = null
142 where MANAGER_TYPE = 6 and NODE_NAME = node;
143
144 INSERT INTO FND_CONCURRENT_PROCESSES
145 (CONCURRENT_PROCESS_ID, ORACLE_PROCESS_ID,
146 QUEUE_APPLICATION_ID, CONCURRENT_QUEUE_ID,
147 SESSION_ID,
148 Creation_Date, Created_By,
149 LAST_UPDATE_DATE, LAST_UPDATED_BY,
150 PROCESS_START_DATE, PROCESS_STATUS_CODE,
151 MANAGER_TYPE, OS_PROCESS_ID,
152 LOGFILE_NAME,
153 NODE_NAME, SQLNET_STRING)
154
155 (select
156 cpid, '999999',
157
158 Q.APPLICATION_ID, Q.CONCURRENT_QUEUE_ID,
159
160 '999999',
161 sysdate, mgrusrid,
162 SYSDATE, mgrusrid,
163 SYSDATE, 'A',
164 6, ospid,
165 logfile,
166 node, twotask
167
168 from FND_CONCURRENT_QUEUES Q
169 where Q.MANAGER_TYPE = 6
170 and Q.NODE_NAME = node
171 );
172 commit;
173
174 exception
175 when others then
176 rollback;
177 raise;
178
179 end register_fndsm_fcp;
180
181 PROCEDURE register_fndsm_fcq(node varchar2)
182 is
183 mgr_name varchar2(36);
184 sm_name varchar2(241);
185 qcount number;
186 ncount number;
187 dummy number;
188 begin
189 mgr_name := 'FNDSM_' || node;
190 IF lengthb(mgr_name) > 30
191 THEN
192 mgr_name := substrb(mgr_name,1,30);
193 END IF;
194
195 select count(*)
196 into qcount
197 from fnd_concurrent_queues
198 where node_name = node
199 and manager_type = '6';
200
201
202
203 if (qcount = 0) then
204 select count(*)
205 into ncount
206 from fnd_concurrent_queues
207 where upper(CONCURRENT_QUEUE_NAME) = upper(mgr_name);
208
209 if (ncount <> 0) then
210 select fnd_concurrent_queues_s.nextval
211 into dummy
212 from dual;
213
214 mgr_name := substrb('FNDSM_'||dummy||'_'||node,
215 1, 30);
216 end if;
217
218 sm_name := fnd_message.get_string('FND', 'CONC-FNDSM NAME');
219 if(sm_name = 'CONC-FNDSM NAME') then
220 sm_name := 'Service Manager';
221 end if;
222
223 delete from fnd_concurrent_queues_tl
224 where upper(CONCURRENT_QUEUE_NAME) = upper(mgr_name)
225 AND application_id = (SELECT application_id FROM fnd_application WHERE application_short_name = 'FND');
226
227 begin
228 fnd_manager.register(sm_name || ': ' || node, 'FND',
229 mgr_name, sm_name, 'Service Manager',
230 null, null, node, null, null, null,
231 'FNDSM', 'FND', null, 'US' );
232 end;
233 end if;
234
235 end register_fndsm_fcq;
236
237 PROCEDURE register_fndim_fcq(node varchar2)
238 is
239 mgr_name varchar2(36);
240 im_name varchar2(241);
241 ncount number;
242 qcount number;
243 dummy number;
244 begin
245 mgr_name := 'FNDIM_' || node;
246 IF lengthb(mgr_name) > 30
247 THEN
248 mgr_name := substrb(mgr_name,1,30);
249 END IF;
250
251 select count(*)
252 into qcount
253 from fnd_concurrent_queues
254 where manager_type = '2'
255 and node_name = node;
256
257 if (qcount = 0) then
258 select count(*)
259 into ncount
260 from fnd_concurrent_queues
261 where upper(CONCURRENT_QUEUE_NAME) = upper(mgr_name);
262
263 if (ncount <> 0) then
264 select fnd_concurrent_queues_s.nextval
265 into dummy
266 from dual;
267
268 mgr_name := substrb('FNDIM_'||dummy||'_'||node,
269 1, 30);
270 end if;
271
272 im_name := fnd_message.get_string('FND', 'CONC-FNDIM NAME');
273 if(im_name = 'CONC-FNDIM NAME') then
274 im_name := 'Internal Monitor';
275 end if;
276
277 delete from fnd_concurrent_queues_tl
278 where upper(CONCURRENT_QUEUE_NAME) = upper(mgr_name)
279 AND application_id = (SELECT application_id FROM fnd_application WHERE application_short_name = 'FND');
280
281 begin
282 fnd_manager.register(im_name || ': ' || node, 'FND',
283 mgr_name, im_name, 'Internal Monitor',
284 null, null, node, null, null, null,
285 'FNDIMON', 'FND', null, 'US' );
286 end;
287 end if;
288
289 end register_fndim_fcq;
290
291 PROCEDURE register_oamgcs_fcq(node IN varchar2,Oracle_home IN varchar2
292 DEFAULT null, interval IN number DEFAULT 300000)
293 is
294 mgr_name varchar2(36);
295 name varchar2(241);
296 svcparams varchar2(256);
297 qcount number;
298 ncount number;
299 dummy number;
300 begin
301 mgr_name := 'OAMGCS_' || node;
302 svcparams := 'NODE=' || node || ';ORACLE_HOME='|| Oracle_home ||';LOADINTERVAL=' ||TO_CHAR(interval) ||
303 ';RTI_KEEP_DAYS=1;FRD_KEEP_DAYS=7';
304 IF lengthb(mgr_name) > 30
305 THEN
306 mgr_name := substrb(mgr_name,1,30);
307 END IF;
308
309 select count(*)
310 into qcount
311 from fnd_concurrent_queues
312 where node_name = node
313 and TO_NUMBER(manager_type) = (select service_id
314 from fnd_cp_services where service_handle='OAMGCS');
315
316 if (qcount = 0) then
317 select count(*)
318 into ncount
319 from fnd_concurrent_queues
320 where upper(CONCURRENT_QUEUE_NAME) = upper(mgr_name);
321
322 if (ncount <> 0) then
323 select fnd_concurrent_queues_s.nextval
324 into dummy
325 from dual;
326
327 mgr_name := substrb('OAMGCS_'||dummy||'_'||node,
328 1, 30);
329 end if;
330
331 name := fnd_message.get_string('FND', 'CONC-OAMGCS NAME');
332
333 if(name = 'CONC-OAMGCS NAME') then
334 name := 'OAM Generic Collection Service';
335 end if;
336
337 delete from fnd_concurrent_queues_tl
338 where upper(CONCURRENT_QUEUE_NAME) = upper(mgr_name)
339 AND application_id = (SELECT application_id FROM fnd_application WHERE application_short_name = 'FND');
340
341 begin
342 if not fnd_manager.Manager_exists(name || ':' ||node,'FND') then
343 fnd_manager.register_si(manager=>name || ': ' || node,
344 application=>'FND',
345 short_name=>mgr_name,
346 service_handle=>'OAMGCS',
347 PRIMARY_NODE=>node);
348 end if;
349 /* Bug 2557014: use work_shift_id parameter insted of
350 workshift_name parameter to ensure Standard workshift
351 is found in NLS instances */
352 if not fnd_manager.manager_work_shift_exists(name || ':' ||node,'FND','Standard') then
353 fnd_manager.assign_work_shift(manager_short_name=>mgr_name,
354 manager_application=>'FND',
355 work_shift_id => 0,
356 processes=>1,
357 sleep_seconds=>30,
358 svc_params=>svcparams);
359 end if;
360 end;
361 end if;
362
363 end register_oamgcs_fcq;
364
365
366 procedure register_fndsm_db( ospid IN number,
367 cpid IN number,
368 instance IN varchar2)
369 is
370 PRAGMA AUTONOMOUS_TRANSACTION;
371 nodename varchar2(30);
372 opid number;
373 audsid number;
374 dbname varchar2(8);
375 dbdomain varchar2(120);
376 dbinstname varchar2(16);
377 insnum number;
378 begin
379 select instance_number
380 into insnum
381 from v$instance;
382
383 select instance_name
384 into dbinstname
385 from v$instance;
386
387 select value
388 into dbname
389 from v$parameter
390 where name = 'db_name';
391
392 select value
393 into dbdomain
394 from v$parameter
395 where name = 'db_domain';
396
397 select userenv('SESSIONID')
398 into audsid
399 from dual;
400
401 select p.pid
402 into opid
403 from v$process p, v$session s
404 where s.audsid = userenv('SESSIONID')
405 and p.addr = s.paddr;
406
407 select node_name
408 into nodename
409 from fnd_concurrent_processes
410 where concurrent_process_id = cpid;
411
412 update fnd_concurrent_processes
413 set oracle_process_id = opid,
414 session_id = audsid,
415 db_name = dbname,
416 db_domain = dbdomain,
417 db_instance = dbinstname,
418 sqlnet_string = instance,
419 instance_number = insnum
420 where os_process_id = ospid
421 and process_status_code = 'A'
422 and node_name = nodename;
423
424 commit;
425
426 exception
427 when others then
428 rollback;
429
430 end register_fndsm_db;
431
432 procedure insert_service_fcp( cmpid IN number,
433 qapid IN number,
434 qid IN number,
435 mgrusrid IN number,
436 mgrtype IN varchar2,
437 node IN varchar2)
438 is
439 PRAGMA AUTONOMOUS_TRANSACTION;
440 begin
441 INSERT INTO FND_CONCURRENT_PROCESSES
442 (CONCURRENT_PROCESS_ID, ORACLE_PROCESS_ID,
443 QUEUE_APPLICATION_ID, CONCURRENT_QUEUE_ID,
444 OS_PROCESS_ID, SESSION_ID,
445 Creation_Date, Created_By,
446 LAST_UPDATE_DATE, LAST_UPDATED_BY,
447 PROCESS_START_DATE, PROCESS_STATUS_CODE,
448 MANAGER_TYPE, NODE_NAME,
449 Lk_Handle)
450 VALUES
451 (cmpid, '999999',
452 qapid, qid,
453 '999999', '999999',
454 Sysdate, mgrusrid,
455 SYSDATE, mgrusrid,
456 SYSDATE, 'Z',
457 mgrtype, upper(node),
458 'SERVICE');
459
460 commit;
461
462 exception
463 when others then
464 rollback;
465 raise;
466
467 end insert_service_fcp;
468
469 end fnd_cp_fndsm;