1 PACKAGE BODY AD_ZD_ADOP AS
2 /* $Header: ADZDADOPB.pls 120.25.12020000.39 2013/06/05 11:09:34 mkumandu ship $ */
3
4 /*API to get the status of applied patch*/
5 PROCEDURE ADOP_PATCH_APPLY_STATUS(adop_sessionID NUMBER, ADOP_tab out
6 adrecordtable, stat varchar2 default 'ALL',node varchar2 default 'ALL')
7 AS
8 TYPE cur_typ IS REF CURSOR;
9 TYPE map_varchar_char IS TABLE OF CHAR INDEX BY VARCHAR2(30);
10 TYPE map_char_varchar IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(1);
11 TYPE rec is RECORD
12 (
13 bugnumber VARCHAR2(30),
14 appliedfsb VARCHAR2(512),
15 patchfsb VARCHAR2(512),
16 st VARCHAR2(30),
17 noden VARCHAR2(30)
18 );
19 srec rec;
20 c cur_typ;
21 status_map map_varchar_char;
22 reverse_map map_char_varchar;
23 nodeName varchar2(30);
24 fs varchar2(30);
25 v_counter number :=0;
26 v_str varchar2(400);
27 v_str_common varchar2(400);
28 BEGIN
29
30 status_map('NOTAPPLIED') := 'N';
31 status_map('RUNNING') := 'R';
32 status_map('SKIP_FAILURE') := 'F';
33 status_map('SKIPPED') := 'S';
34 status_map('SUCCESS') := 'Y';
35 status_map('HARD_FAILURE') := 'H';
36 status_map('ALL') := 'A';
37 status_map('NULL') := 'A';
38
39 reverse_map('N') := 'NOTAPPLIED';
40 reverse_map('R') := 'RUNNING';
41 reverse_map('F') := 'SKIP_FAILURE';
42 reverse_map('S') := 'SKIPPED';
43 reverse_map('Y') := 'SUCCESS';
44 reverse_map('H') := 'HARD_FAILURE';
45 IF node = 'ALL' OR node IS NULL THEN
46 nodeName := 'A';
47 ELSE
48 nodeName := node;
49 END IF;
50 v_str_common := 'Select BUG_NUMBER, APPLIED_FILE_SYSTEM_BASE,'||
51 'PATCH_FILE_SYSTEM_BASE,STATUS, NODE_NAME FROM AD_ADOP_SESSION_PATCHES WHERE '||
52 'BUG_NUMBER <> '||''''||'CLONE'||''''||' AND BUG_NUMBER <>' ||
53 ''''||'CONFIG_CLONE'||''''||' AND ADOP_SESSION_ID ='||adop_sessionID;
54 IF (stat = 'ALL' OR stat IS NULL) AND nodeName = 'A' THEN
55 v_str := v_str_common;
56
57 ELSIF (stat = 'ALL' OR stat IS NULL) AND nodeName <> 'A' THEN
58 v_str := v_str_common||
59 ' AND NODE_NAME = '||''''||node||'''';
60
61 ELSIF stat <> 'ALL' AND nodeName = 'A' THEN
62 v_str := v_str_common||
63 ' AND STATUS = '||''''||status_map(stat)||'''';
64
65 ELSIF stat <> 'ALL' AND nodeName <> 'A' THEN
66 v_str := v_str_common||
67 ' AND STATUS = '||''''||status_map(stat)||''''||' AND NODE_NAME ='||
68 ''''|| node ||'''';
69 END IF;
70
71
72 open c for v_str;
73 LOOP
74 fetch c into srec;
75 exit when c%notfound;
76 ADOP_tab(v_counter).BugNumber:=srec.bugnumber;
77 ADOP_tab(v_counter).File_edition:=
78 srec.appliedfsb;
79 ADOP_tab(v_counter).Status:=reverse_map(srec.st);
80 ADOP_tab(v_counter).node:=srec.noden;
81 v_counter:=v_counter+1;
82 END LOOP;
83 CLOSE c;
84
85 end ADOP_PATCH_APPLY_STATUS;
86
87 /*-----------------------------------------------------------------+
88 | |
89 | LOG |
90 | Logging api. This API calls AD_ZD_LOG.Message api. |
91 | |
92 | |
93 +-----------------------------------------------------------------*/
94
95 procedure LOG(p_module varchar2, p_log_type varchar2, p_message varchar2)
96 is
97 begin
98
99 AD_ZD_LOG.Message(x_module => pkg_name||'.'||p_module,
100 x_log_type => p_log_type,
101 x_message => p_message);
102 end;
103
104 /*-----------------------------------------------------------------+
105 | |
106 | getPatchNumbers |
107 | This api gets the delta patches between Run and Patch FSs. |
108 | |
109 +-----------------------------------------------------------------*/
110
111 function getPatchNumbers return varchar2
112 is
113 l_bug_numbers_list varchar2(32767);
114 l_first boolean :=true;
115 begin
116 for rec in
117 (select distinct bug_number || ',' as bug_number
118 from ad_adop_session_patches
119 where PATCH_FILE_SYSTEM_BASE is NULL
120 and status = 'Y'
121 order by bug_number )
122 loop
123 l_bug_numbers_list := l_bug_numbers_list || rec.bug_number;
124 end loop;
125 return l_bug_numbers_list;
126 end getPatchNumbers;
127
128 /*-----------------------------------------------------------------+
129 | |
130 | GET_ACTIVE_CP_REQUEST_ID |
131 | This api returns active ADZDPATCH CP request id, if any |
132 | |
133 | Returns -1, if no such CP request found |
134 | |
135 +-----------------------------------------------------------------*/
136
137 FUNCTION GET_ACTIVE_CP_REQUEST_ID RETURN NUMBER
138 IS
139 c_ret boolean;
140 c_reqid number;
141 l_phase varchar2(30);
142 l_dev_phase varchar2(30);
143 l_dev_status varchar2(30);
144 l_status varchar2(30);
145 l_message varchar2(2000);
146 l_mod_name varchar2(30) := 'GET_ACTIVE_CP_REQUEST_ID';
147 BEGIN
148
149 -- This api would be called from adop which is outside of forms administration
150 -- Hence needs to call Apps_Initialize
151 FND_GLOBAL.Apps_Initialize(0, 20420, 1);
152
153 /*
154 * Check is there any CP request already submitted
155 */
156
157 c_ret := FND_CONCURRENT.get_request_status(request_id => c_reqid,
158 appl_shortname => 'AD',
159 program => 'ADZDPATCH',
160 phase => l_phase,
161 status => l_status,
162 dev_phase => l_dev_phase,
163 dev_status => l_dev_status,
164 message => l_message);
165 if (c_ret = TRUE)
166 then
167 if ((l_dev_phase = 'PENDING') OR (l_dev_phase = 'RUNNING'))
168 then
169 log(l_mod_name,'STATEMENT', 'An ADZDPATCH CP request ' || c_reqid ||
170 ' is in progress');
171 return c_reqid;
172 end if;
173 end if;
174 return 0;
175 END GET_ACTIVE_CP_REQUEST_ID;
176
177 /*-----------------------------------------------------------------+
178 | |
179 | WAIT_FOR_CP_TO_RUN |
180 | This api waits for the CP to run. |
181 | i.e. Waits until the CP request moves to RUNNING status |
182 | |
183 | Returns 0, if CP manager is down |
184 | Returns 1, if the request started successfully. |
185 | |
186 +-----------------------------------------------------------------*/
187
188 FUNCTION WAIT_FOR_CP_TO_RUN (REQID IN NUMBER) RETURN NUMBER
189 IS
190 c_ret boolean;
191 c_reqid number;
192 l_phase varchar2(30);
193 l_dev_phase varchar2(30);
194 l_dev_status varchar2(30);
195 l_status varchar2(30);
196 l_message varchar2(2000);
197 l_defined boolean;
198 l_active boolean;
199 l_workshift boolean;
200 l_mgr_running boolean;
201 l_run_alone boolean;
202 l_mod_name varchar2(25) := 'WAIT_FOR_CP_TO_RUN';
203 BEGIN
204
205 -- This api is called only when ICM is running
206
207 -- This api would be called from adop which is outside of forms administration
208 -- Hence needs to call Apps_Initialize
209 FND_GLOBAL.Apps_Initialize(0, 20420, 1);
210
211 log(l_mod_name, 'STATEMENT', 'Waiting for the ' ||
212 'ADZDPATCH CP to start');
213
214 c_reqid:=REQID;
215 l_dev_phase := 'PENDING';
216
217 while l_dev_phase = 'PENDING' loop
218
219 /*
220 * The conc managers are up if we get here.
221 * Now check to make sure there is a manager defined
222 * that can run the ADZDPATCH conc pgm.
223 * If manager is not running then this is a failure situation
224 * since we know other managers are currently running (see above).
225 */
226 l_mgr_running := TRUE;
227
228 FND_CONC.manager_check(req_id => c_reqid,
229 cd_id => null,
230 mgr_defined => l_defined,
231 mgr_active => l_active,
232 mgr_workshift => l_workshift,
233 mgr_running => l_mgr_running,
234 run_alone => l_run_alone);
235
236 if( l_mgr_running = FALSE)
237 then
238 log(l_mod_name, 'ERROR', 'No Concurrent Manager is defined that can run ' ||
239 'concurrent program ADZDPATCH');
240 raise_application_error (-20008, 'No Concurrent Manager is defined that' ||
241 ' can run concurrent program ADZDPATCH');
242 end if;
243
244 /*
245 * If we get here, we know conc mgrs are up.
246 * and we know there is one defined that can run ADZDPATCH conc pgm.
247 * So now let's check the status of ADZDPATCH conc pgm.
248 */
249 l_dev_phase := null;
250
251 c_ret := FND_CONCURRENT.get_request_status(request_id => c_reqid,
252 appl_shortname => 'AD',
253 program => 'ADZDPATCH',
254 phase => l_phase,
255 status => l_status,
256 dev_phase => l_dev_phase,
257 dev_status => l_dev_status,
258 message => l_message);
259
260 if (c_ret = FALSE)
261 then
262 log(l_mod_name, 'ERROR', 'Failed to check the status of ' ||
263 'concurrent program ADZDPATCH');
264 raise_application_error (-20008, 'Failed to check the status of concurrent ' ||
265 'program ADZDPATCH');
266 end if;
267
268 if (l_dev_phase = 'PENDING')
269 then
270 dbms_lock.sleep(30);
271 end if;
272 end loop;
273
274 /*
275 * If we come here we have exit the PENDING loop.
276 * Now we must check that it went from PENDING to RUNNING.
277 * Anything else is a failure situation.
278 */
279
280 if (l_dev_phase = 'RUNNING')
281 then
282
283 log (l_mod_name, 'STATEMENT', 'ADZDPATCH concurrent program ' ||
284 'started successfully');
285 else
286 log(l_mod_name, 'ERROR', 'ADZDPATCH status is INVALID. ' ||
287 ' Failed to submit ADZDPATCH');
288 raise_application_error (-20008, 'Failed to submit concurrent ' ||
289 'program ADZDPATCH');
290 end if;
291
292 return 1;
293
294 END WAIT_FOR_CP_TO_RUN;
295
296 /*-----------------------------------------------------------------+
297 | |
298 | SUBMIT_REQUEST |
299 | This api submits the CP request and returns the request id. |
300 | |
301 | If any active CP request found, no new request will be |
302 | submitted. The current active CP request id gets returned. |
303 | |
307 IS
304 +-----------------------------------------------------------------*/
305
306 FUNCTION SUBMIT_REQUEST RETURN NUMBER
308 c_error varchar2(2000);
309 c_reqid number;
310 l_mod_name varchar2(25) := 'SUBMIT_REQUEST';
311 BEGIN
312
313 -- This api would be called from adop which is outside of forms administration
314 -- Hence needs to call Apps_Initialize
315 FND_GLOBAL.Apps_Initialize(0, 20420, 1);
316
317 /*
318 * Check is there is any CP request already submitted
319 */
320 c_reqid := get_active_cp_request_id();
321 if (c_reqid > 0)
322 then
323 /*
324 * Since there is an active CP request error out
325 */
326 log(l_mod_name,'ERROR', 'Can not submit CP request, ' ||
327 ' as there is an active ADZDPATCH concurrent program.');
328 return -1;
329 end if;
330
331 /*
332 * Submit a new CP request only in case of no ADZDPATCH CP requests
333 * with PENDING or RUNNING status.
334 */
335 c_reqid := FND_REQUEST.SUBMIT_REQUEST(application=>'AD',
336 program=>'ADZDPATCH');
337
338 /*
339 * the below commit is needed to commit the submitted request above
340 */
341 commit;
342
343 if (c_reqid = 0)
344 then
345 c_error := fnd_message.GET_TOKEN('REASON');
346 log(l_mod_name,'ERROR', 'Error occured whiile submitting ' ||
347 'ADZDPATCH concurrent program. Error= '||c_error);
348 raise_application_error(-20008, 'Error in submitting ADZDPATCH ' ||
349 'concurrent request');
350 else
351 log(l_mod_name,'STATEMENT', 'Submitted concurrent program ' ||
352 'ADZDPATCH successfully');
353 end if;
354
355 return c_reqid;
356
357 END SUBMIT_REQUEST;
358
359 PROCEDURE INSERT_INTO_PATCHES_TABLE(
360 p_session_id IN NUMBER,
361 p_bug_number IN VARCHAR2,
362 p_patch_run_id IN NUMBER,
363 p_appltop_base IN VARCHAR2,
364 p_adpatch_options IN VARCHAR2,
365 p_autoconfig_status IN VARCHAR2,
366 p_start_date IN DATE,
367 p_end_date IN DATE,
368 p_patch_top IN VARCHAR2,
369 p_driverfile_name IN VARCHAR2,
370 p_status IN VARCHAR2
371 )
372 IS
373 l_mod_name varchar2(25) := 'INSERT_INTO_PATCHES_TABLE';
374 l_exists number;
375 l_status varchar2(1) := 'N';
376 l_ac_status varchar2(1);
377 l_invoking_node varchar2(30);
378 l_current_status varchar2(1);
379 TYPE status_order_t IS TABLE OF NUMBER INDEX BY VARCHAR2(1);
380 status_order status_order_t;
381
382 cursor nodes is
383
384 SELECT
385 fn.host node_name ,
386 aat.appl_top_id appl_top_id ,
387 EXTRACTVALUE(XMLType(TEXT),'//shared_file_system') is_shared
388 FROM
389 fnd_nodes fn,
390 FND_OAM_CONTEXT_FILES focf,
391 fnd_product_groups fpg,
392 ad_appl_tops aat,
393 ad_releases ar
394 WHERE focf.NAME not in ('TEMPLATE','METADATA','config.txt') and focf.CTX_TYPE='A' and
395 (focf.status is null or upper(focf.status) in ('S','F')) and
396 EXTRACTVALUE(XMLType(focf.TEXT),'//file_edition_type') = 'run' and
397 focf.node_name=fn.host and
398 (fn.support_cp='Y' or fn.support_forms='Y' or
399 fn.support_web='Y' or fn.support_admin='Y') and
400 aat.appl_top_type='R' and aat.applications_system_name=fpg.applications_system_name and
401 aat.active_flag='Y' and
402 fpg.release_name=ar.major_version||'.'||ar.minor_version||'.'||ar.tape_version and
403 fpg.aru_release_name=ar.aru_release_name and
404 aat.name=EXTRACTVALUE(XMLType(focf.TEXT),'//APPL_TOP_NAME');
405
406 BEGIN
407 log(l_mod_name,'STATEMENT','START -->');
408
409 select substr(machine, 1,decode(instr(machine, '.', 1,1),0,length(machine),instr(machine, '.', 1, 1)-1)) into l_invoking_node
410 from v$session where audsid=USERENV('SESSIONID');
411
412 l_invoking_node := substr(l_invoking_node, instr(l_invoking_node,'\')+1);
413
414 status_order('N'):=10;
415 status_order('R'):=20;
416 status_order('H'):=30;
417 status_order('F'):=40;
418 status_order('S'):=50;
419 status_order('Y'):=60;
420
421 for rec in nodes loop
422
423 if(upper(rec.node_name) = upper(l_invoking_node) OR rec.is_shared='true') then
424 l_status:=p_status;
425 else
426 l_status:='N';
427 end if;
428
429 if(upper(rec.node_name) = upper(l_invoking_node)) then
430 l_ac_status:=p_autoconfig_status;
431 else
432 l_ac_status:='N';
433 end if;
434
435 select count(1) into l_exists from ad_adop_session_patches
436 where ADOP_SESSION_ID=p_session_id and BUG_NUMBER=p_bug_number and
437 APPLIED_FILE_SYSTEM_BASE=p_appltop_base and APPLTOP_ID=rec.appl_top_id and
438 NODE_NAME=rec.node_name and DRIVER_FILE_NAME=p_driverfile_name;
439
440 if (l_exists > 0) then
441 select STATUS into l_current_status from ad_adop_session_patches
445
442 where ADOP_SESSION_ID=p_session_id and BUG_NUMBER=p_bug_number and
443 APPLIED_FILE_SYSTEM_BASE=p_appltop_base and APPLTOP_ID=rec.appl_top_id and
444 NODE_NAME=rec.node_name and DRIVER_FILE_NAME=p_driverfile_name;
446 if (status_order(l_current_status) < status_order(l_status)) then
447 log(l_mod_name,'STATEMENTC','Updating existing AD_ADOP_SESSION_PATCHES table entry for patch #' || p_bug_number);
448 log(l_mod_name,'STATEMENT','- STATUS: ' || l_current_status || ' -> ' || l_status);
449
450 update ad_adop_session_patches
451 set PATCHRUN_ID=p_patch_run_id, STATUS=l_status, AUTOCONFIG_STATUS=l_ac_status,
452 START_DATE=p_start_date, END_DATE=p_end_date
453 where ADOP_SESSION_ID=p_session_id and BUG_NUMBER=p_bug_number and APPLTOP_ID=rec.appl_top_id and
454 NODE_NAME=rec.node_name and DRIVER_FILE_NAME=p_driverfile_name;
455 end if;
456 else
457 log(l_mod_name,'STATEMENT','Inserting new AD_ADOP_SESSION_PATCHES table entry for patch #' || p_bug_number);
458
459 INSERT INTO ad_adop_session_patches
460 (ADOP_SESSION_ID, BUG_NUMBER, PATCHRUN_ID, STATUS, APPLIED_FILE_SYSTEM_BASE, PATCH_FILE_SYSTEM_BASE,
461 ADPATCH_OPTIONS, APPLTOP_ID, NODE_NAME, AUTOCONFIG_STATUS, START_DATE, END_DATE, PATCH_TOP, DRIVER_FILE_NAME)
462 VALUES
463 (p_session_id, p_bug_number, p_patch_run_id, l_status, p_appltop_base, NULL, p_adpatch_options,
464 rec.appl_top_id, rec.node_name, l_ac_status, p_start_date, p_end_date, p_patch_top, p_driverfile_name);
465 end if;
466
467 end loop;
468 commit;
469 log(l_mod_name,'STATEMENT','<-- END');
470 EXCEPTION
471 when others then
472 log(l_mod_name, 'ERROR', 'ERROR: '||SQLERRM);
473 RAISE_APPLICATION_ERROR(-20010,SQLERRM);
474 END INSERT_INTO_PATCHES_TABLE;
475
476 FUNCTION GET_CUTOVER_STATUS(p_appltop_id in number,p_node_name in varchar2,p_session_id in number) return varchar2 is
477 l_cutover_status varchar2(1);
478 l_session_id number;
479 begin
480 if ( p_session_id is null) then
481 select max(adop_session_id) into l_session_id from ad_adop_sessions
482 where appltop_id=p_appltop_id and node_name=p_node_name;
483 else
484 l_session_id:=p_session_id;
485 end if;
486 select cutover_status into l_cutover_status
487 from ad_adop_sessions
488 where appltop_id=p_appltop_id
489 and node_name=p_node_name
490 and adop_session_id= l_session_id;
491 return l_cutover_status;
492 end GET_CUTOVER_STATUS;
493
494 PROCEDURE LOCK_SESSIONS_TABLE(p_node_name in varchar2,p_wait_interval in number,p_num_tries in number) is
495 l_mod_name varchar2(25) := 'LOCK_SESSIONS_TABLE';
496 resource_busy exception;
497 pragma exception_init(resource_busy,-54);
498 l_is_locked number:=0;
499 begin
500
501 for i in 1..p_num_tries loop
502 begin
503 lock table ad_adop_sessions in exclusive mode nowait;
504 select count(*) into l_is_locked from ad_adop_sessions where adop_session_id=0 and EDITION_NAME='LOCK';
505 if (l_is_locked = 0) then
506 insert into ad_adop_sessions
507
508
509 (ADOP_SESSION_ID,PREPARE_STATUS,APPLY_STATUS,FINALIZE_STATUS,CUTOVER_STATUS,CLEANUP_STATUS,ABORT_STATUS,STATUS,EDITION_NAME,NODE_NAME)
510 values (0,'X','X','X','X','X','X','Y','LOCK',p_node_name);
511 commit;
512 return;
513 else
514 commit;
515 dbms_lock.sleep(p_wait_interval);
516 end if;
517 exception
518 when resource_busy then
519 dbms_lock.sleep(p_wait_interval);
520 end;
521 end loop;
522 log(l_mod_name, 'ERROR', 'ERROR: Unable to acquire lock on ad_adop_sessions table.');
523 RAISE_APPLICATION_ERROR(-20010,'ERROR: Unable to acquire lock on ad_adop_sessions table.');
524
525 end LOCK_SESSIONS_TABLE;
526
527 PROCEDURE UNLOCK_SESSIONS_TABLE(p_node_name in varchar2,p_wait_interval in number,p_num_tries in number) is
528 l_mod_name varchar2(25) := 'UNLOCK_SESSIONS_TABLE';
529 resource_busy exception;
530 pragma exception_init(resource_busy,-54);
531 l_is_locked number:=0;
532 begin
533
534 for i in 1..p_num_tries loop
535 begin
536 lock table ad_adop_sessions in exclusive mode nowait;
537 select count(*) into l_is_locked from ad_adop_sessions where adop_session_id=0 and EDITION_NAME='LOCK' and node_name=p_node_name;
538 if (l_is_locked <> 0) then
539 delete from ad_adop_sessions where adop_session_id=0 and EDITION_NAME='LOCK' and node_name=p_node_name;
540 commit;
541 return;
542 else
543 log(l_mod_name, 'WARN', 'WARN: Trying to unlock ad_adop_sessions table when no lock in place.');
544 commit;
545 return;
546 end if;
547 exception
548 when resource_busy then
549 dbms_lock.sleep(p_wait_interval);
550 end;
551 end loop;
552 log(l_mod_name, 'ERROR', 'ERROR: Unable to acquire lock on ad_adop_sessions table.');
553 RAISE_APPLICATION_ERROR(-20010,'ERROR: Unable to acquire lock on ad_adop_sessions table.');
554
555 end UNLOCK_SESSIONS_TABLE;
556
557 /*----------------------------------------------------------------+
558 | CUTOVER_DONE |
559 | This api updates the cutover phase for a patching cycle |
560 | of a multi node environment. |
561 +-----------------------------------------------------------------*/
562 procedure SET_CUTOVER_STATUS(p_appltop_id in number,p_node_name in varchar2,p_status in varchar2) is
563 l_session_id number;
564 begin
568 update ad_adop_sessions set cutover_status=p_status
565 select max(adop_session_id) into l_session_id from ad_adop_sessions
566 where appltop_id=p_appltop_id and node_name=p_node_name
567 and prepare_status='Y' and apply_status='Y';
569 where adop_session_id= l_session_id
570 and appltop_id=p_appltop_id
571 and node_name=p_node_name;
572 commit;
573 end SET_CUTOVER_STATUS;
574
575 PROCEDURE KILL_OLD_SESSIONS(p_session_id number)
576 IS
577 l_message varchar2(250);
578 l_statement varchar2(200);
579 l_info varchar2(200);
580 invalid_session EXCEPTION;
581 PRAGMA EXCEPTION_INIT(invalid_session,-30);
582 CURSOR c_sessions_to_kill is
583 select
584 '/* '||e.object_name||' '||s.osuser||' '||s.username||' */' info,
585 'ALTER SYSTEM KILL SESSION '||''''||s.sid||','||s.serial#||'''' kill
586 from
587 v$session s, v$process p, database_properties run -- /* run edition name */
588 , dba_objects_ae e
589 where s.type <> 'BACKGROUND' and p.addr = s.paddr
590 and run.property_name = 'DEFAULT_EDITION'
591 and e.object_id = s.session_edition_id
592 and e.object_name < run.property_value
593 and s.username in
594 ( select oracle_username from fnd_oracle_userid
595 where read_only_flag in ('A', 'B', 'C', 'E', 'U') );
596 begin
597 OPEN c_sessions_to_kill;
598 FETCH c_sessions_to_kill into l_info,l_statement;
599 while (c_sessions_to_kill%found)
600 loop
601 begin
602 AD_ZD.EXEC(l_statement,'STATEMENT');
603 exception
604 when invalid_session then
605 l_message := 'Invalid user session <'||l_statement||'>';
606 ad_zd_log.message('ADOP','WARNING',l_message);
607 when others then
608 l_message := 'Error: while killing session using <'||l_statement||'> '||sqlerrm;
609 ad_zd_log.message('ADOP','ERROR',l_message);
610 raise_application_error(-20001, l_message);
611 end;
612 FETCH c_sessions_to_kill into l_info,l_statement;
613 end loop;
614 CLOSE c_sessions_to_kill;
615 exception
616 when others then
617 if c_sessions_to_kill%isopen then
618 -- cursor was not already closed
619 CLOSE c_sessions_to_kill;
620 end if;
621 update ad_adop_sessions set status='F'
622 where adop_session_id=p_session_id;
623 commit;
624 raise_application_error(-20001,'Error: while killing old sessions.' || sqlerrm);
625 end KILL_OLD_SESSIONS;
626
627 FUNCTION GET_ADZDPATCH_STATUS(REQID IN NUMBER) RETURN VARCHAR2
628 IS
629 c_error varchar2(2000);
630 c_reqid number;
631 l_mod_name varchar2(25) := 'GET_ADZDPATCH_STATUS';
632 l_phase varchar2(80);
633 l_status varchar2(80);
634 l_diag varchar2(2000);
635
636 BEGIN
637
638 -- This api would be called from adop which is outside of forms administration
639 -- Hence needs to call Apps_Initialize
640 FND_GLOBAL.Apps_Initialize(0, 20420, 1);
641
642 c_reqid := REQID;
643 FND_CONC.DIAGNOSE(c_reqid, l_phase, l_status, l_diag);
644
645 return l_diag;
646
647 END GET_ADZDPATCH_STATUS;
648
649 PROCEDURE LOCK_PATCHES_TABLE(p_node_name in varchar2,p_lock_name in varchar2) is
650 l_mod_name varchar2(25) := 'LOCK_PATCHES_TABLE';
651 resource_busy exception;
652 pragma exception_init(resource_busy,-54);
653 l_is_locked number:=0;
654 begin
655 begin
656 select count(*) into l_is_locked from ad_adop_session_patches where adop_session_id=0 and BUG_NUMBER=p_lock_name;
657 if (l_is_locked = 0) then
658 lock table ad_adop_session_patches in exclusive mode nowait;
659 insert into ad_adop_session_patches
660 (ADOP_SESSION_ID,BUG_NUMBER,STATUS,NODE_NAME)
661 values (0,p_lock_name,'N',p_node_name);
662 commit;
663 log(l_mod_name, 'STATEMENT', 'Able to acquire lock on ad_adop_session_patches table.');
664 return;
665 else
666 log(l_mod_name, 'WARNING', 'Unable to acquire lock on ad_adop_session_patches table.');
667 commit;
668 end if;
669 exception
670 when resource_busy then
671 log(l_mod_name, 'WARNING', 'Unable to acquire lock on ad_adop_session_patches table.');
672 end;
673 end LOCK_PATCHES_TABLE;
674
675 PROCEDURE UNLOCK_PATCHES_TABLE(p_node_name in varchar2,p_lock_name in varchar2) is
676 l_mod_name varchar2(25) := 'UNLOCK_PATCHES_TABLE';
677 resource_busy exception;
678 pragma exception_init(resource_busy,-54);
679 l_is_locked number:=0;
680 begin
681 begin
682 select count(1) into l_is_locked from ad_adop_session_patches
683 where adop_session_id=0 and BUG_NUMBER=p_lock_name
684 and node_name=p_node_name;
685 if (l_is_locked <> 0) then
686 lock table ad_adop_session_patches in exclusive mode nowait;
687 delete from ad_adop_session_patches
688 where adop_session_id=0 and BUG_NUMBER=p_lock_name
689 and node_name=p_node_name;
690 commit;
691 log(l_mod_name, 'STATEMENT', 'Able to acquire lock on ad_adop_session_patches table.');
692 return;
693 else
694 log(l_mod_name, 'WARNING', 'Unable to acquire lock on ad_adop_session_patches table.');
695 commit;
696 return;
697 end if;
698 exception
699 when resource_busy then
700 log(l_mod_name, 'WARNING', 'Unable to acquire lock on ad_adop_session_patches table.');
701 end;
702 end UNLOCK_PATCHES_TABLE;
703
707 -- Cleaning ad_snapshot_files
704 PROCEDURE SYNC_SNAPSHOTS(p_appl_top_id in number default null)
705 is
706 begin
708 -- Deleting new records inserted by patch
709 delete AD_SNAPSHOT_FILES
710 where (snapshot_id, file_id, nvl(containing_file_id, -2)) in
711 (select ou.snapshot_id, ou.file_id, nvl(ou.containing_file_id, -2)
712 from AD_SNAPSHOT_FILES OU, AD_SNAPSHOTS OUASN,
713 AD_APPL_TOPS ouaat, FND_PRODUCT_GROUPS oufpg,
714 AD_RELEASES rel
715 where OU.SNAPSHOT_ID=OUASN.SNAPSHOT_ID
716 and OUASN.SNAPSHOT_TYPE='P'
717 and ouasn.snapshot_name='CURRENT_VIEW'
718 and ouasn.appl_top_id=ouaat.appl_top_id
719 and ouaat.appl_top_id=nvl(p_appl_top_id, ouaat.appl_top_id)
720 and ouaat.appl_top_type='R'
721 and ouaat.applications_system_name=oufpg.applications_system_name
722 and oufpg.product_group_id=1
723 and rel.aru_release_name=oufpg.aru_release_name
724 and ouasn.release_id=rel.release_id
725 and ouaat.active_flag='Y'
726 and oufpg.release_name=rel.major_version||'.'||
727 rel.minor_version||'.'||
728 rel.tape_version
729 and not exists (
730 select 'x'
731 from AD_SNAPSHOT_FILES ASF, AD_SNAPSHOTS ASN
732 where ASF.SNAPSHOT_ID=ASN.SNAPSHOT_ID
733 and ASN.SNAPSHOT_TYPE='C'
734 and ASN.SNAPSHOT_NAME=OUASN.SNAPSHOT_NAME
735 and ASN.APPL_TOP_ID=OUASN.APPL_TOP_ID
736 and OU.FILE_ID=ASF.FILE_ID
737 and NVL(OU.CONTAINING_FILE_ID, -2)=NVL(ASF.CONTAINING_FILE_ID, -2)
738 and OUASN.APPL_TOP_ID=ASN.APPL_TOP_ID)
739 );
740
741 delete AD_SNAPSHOT_FILES
742 where (snapshot_id, file_id, nvl(containing_file_id, -2)) in
743 (select ou.snapshot_id, ou.file_id, nvl(ou.containing_file_id, -2)
744 from AD_SNAPSHOT_FILES OU, AD_SNAPSHOTS OUASN,
745 AD_APPL_TOPS ouaat, FND_PRODUCT_GROUPS oufpg,
746 AD_RELEASES rel
747 where OU.SNAPSHOT_ID=OUASN.SNAPSHOT_ID
748 and OUASN.SNAPSHOT_TYPE='Q'
749 and ouasn.snapshot_name='GLOBAL_VIEW'
750 and ouasn.appl_top_id=ouaat.appl_top_id
751 and ouaat.appl_top_type='G'
752 and ouaat.applications_system_name=oufpg.applications_system_name
753 and oufpg.product_group_id=1
754 and rel.aru_release_name=oufpg.aru_release_name
755 and ouasn.release_id=rel.release_id
756 and ouaat.active_flag='Y'
757 and oufpg.release_name=rel.major_version||'.'||
758 rel.minor_version||'.'||
759 rel.tape_version
760 and not exists (
761 select 'x'
762 from AD_SNAPSHOT_FILES ASF, AD_SNAPSHOTS ASN
763 where ASF.SNAPSHOT_ID=ASN.SNAPSHOT_ID
764 and ASN.SNAPSHOT_TYPE='G'
765 and ASN.SNAPSHOT_NAME=OUASN.SNAPSHOT_NAME
766 and ASN.APPL_TOP_ID=OUASN.APPL_TOP_ID
767 and OU.FILE_ID=ASF.FILE_ID
768 and NVL(OU.CONTAINING_FILE_ID, -2)=NVL(ASF.CONTAINING_FILE_ID, -2)
769 and OUASN.APPL_TOP_ID=ASN.APPL_TOP_ID)
770 );
771
772 -- Cleaning ad_snapshot_bugfixes
773 -- Deleting new records inserted by patch
774 delete AD_SNAPSHOT_BUGFIXES
775 where (SNAPSHOT_ID, BUGFIX_ID) in
776 (select OU.SNAPSHOT_ID, OU.BUGFIX_ID
777 from AD_SNAPSHOT_BUGFIXES OU, AD_SNAPSHOTS OUASN,
778 AD_APPL_TOPS ouaat, FND_PRODUCT_GROUPS oufpg,
779 AD_RELEASES rel
780 where OU.SNAPSHOT_ID=OUASN.SNAPSHOT_ID
781 and OUASN.SNAPSHOT_TYPE='P'
782 and ouasn.snapshot_name='CURRENT_VIEW'
783 and ouasn.appl_top_id=ouaat.appl_top_id
784 and ouaat.appl_top_id=nvl(p_appl_top_id, ouaat.appl_top_id)
785 and ouaat.appl_top_type='R'
786 and ouaat.applications_system_name=oufpg.applications_system_name
787 and oufpg.product_group_id=1
788 and rel.aru_release_name=oufpg.aru_release_name
789 and ouasn.release_id=rel.release_id
790 and ouaat.active_flag='Y'
791 and oufpg.release_name=rel.major_version||'.'||
792 rel.minor_version||'.'||
793 rel.tape_version
794 and not exists (
795 select 'x'
796 from AD_SNAPSHOT_BUGFIXES ASF, AD_SNAPSHOTS ASN
797 where ASF.SNAPSHOT_ID=ASN.SNAPSHOT_ID
798 and ASN.SNAPSHOT_TYPE='C'
799 and ASN.SNAPSHOT_NAME=OUASN.SNAPSHOT_NAME
800 and ASN.APPL_TOP_ID=OUASN.APPL_TOP_ID
801 and OU.BUGFIX_ID=ASF.BUGFIX_ID
802 and OUASN.APPL_TOP_ID=ASN.APPL_TOP_ID)
803 );
804
805 delete AD_SNAPSHOT_BUGFIXES
806 where (SNAPSHOT_ID, BUGFIX_ID) in
807 (select OU.SNAPSHOT_ID, OU.BUGFIX_ID
808 from AD_SNAPSHOT_BUGFIXES OU, AD_SNAPSHOTS OUASN,
809 AD_APPL_TOPS ouaat, FND_PRODUCT_GROUPS oufpg,
810 AD_RELEASES rel
811 where OU.SNAPSHOT_ID=OUASN.SNAPSHOT_ID
812 and OUASN.SNAPSHOT_TYPE='Q'
813 and ouasn.snapshot_name='GLOBAL_VIEW'
814 and ouasn.appl_top_id=ouaat.appl_top_id
815 and ouaat.appl_top_type='G'
816 and ouaat.applications_system_name=oufpg.applications_system_name
817 and rel.aru_release_name=oufpg.aru_release_name
818 and ouasn.release_id=rel.release_id
819 and ouaat.active_flag='Y'
820 and oufpg.release_name=rel.major_version||'.'||
821 rel.minor_version||'.'||
825 from AD_SNAPSHOT_BUGFIXES ASF, AD_SNAPSHOTS ASN
822 rel.tape_version
823 and not exists (
824 select 'x'
826 where ASF.SNAPSHOT_ID=ASN.SNAPSHOT_ID
827 and ASN.SNAPSHOT_TYPE='G'
828 and ASN.SNAPSHOT_NAME=OUASN.SNAPSHOT_NAME
829 and ASN.APPL_TOP_ID=OUASN.APPL_TOP_ID
830 and OU.BUGFIX_ID=ASF.BUGFIX_ID)
831 );
832
833 -- Update existing records
834 declare
835 cursor crs is
836 select ASF1.SNAPSHOT_ID,
837 ASF2.FILE_ID,
838 ASF2.CONTAINING_FILE_ID,
839 ASF2.FILE_VERSION_ID
840 from AD_SNAPSHOTS ASN1, AD_SNAPSHOT_FILES ASF1,
841 AD_SNAPSHOTS ASN2, AD_SNAPSHOT_FILES ASF2,
842 AD_APPL_TOPS AAT, FND_PRODUCT_GROUPS FPG,
843 AD_RELEASES rel
844 where ASN1.SNAPSHOT_ID=ASF1.SNAPSHOT_ID
845 and ASN2.SNAPSHOT_ID=ASF2.SNAPSHOT_ID
846 and ASN1.SNAPSHOT_TYPE='P'
847 and ASN2.SNAPSHOT_TYPE='C'
848 and ASN1.SNAPSHOT_NAME='CURRENT_VIEW'
849 and ASN2.SNAPSHOT_NAME=ASN1.SNAPSHOT_NAME
850 and ASN1.APPL_TOP_ID=ASN2.APPL_TOP_ID
851 and ASF1.FILE_ID=ASF2.FILE_ID
852 and NVL(ASF1.CONTAINING_FILE_ID, -2)=NVL(ASF2.CONTAINING_FILE_ID, -2)
853 and NVL(ASF1.FILE_VERSION_ID, -2)<>NVL(ASF2.FILE_VERSION_ID, -2)
854 and ASN1.APPL_TOP_ID=AAT.APPL_TOP_ID
855 and AAT.APPL_TOP_ID=nvl(p_appl_top_id, AAT.APPL_TOP_ID)
856 and AAT.APPLICATIONS_SYSTEM_NAME=FPG.APPLICATIONS_SYSTEM_NAME
857 and fpg.PRODUCT_GROUP_ID=1
858 and rel.aru_release_name=fpg.aru_release_name
859 and asn1.release_id=rel.release_id
860 and asn2.release_id=rel.release_id
861 and aat.active_flag='Y'
862 and fpg.release_name=rel.major_version||'.'||
863 rel.minor_version||'.'||
864 rel.tape_version;
865
866 cursor grs is
867 select ASF1.SNAPSHOT_ID,
868 ASF2.FILE_ID,
869 ASF2.CONTAINING_FILE_ID,
870 ASF2.FILE_VERSION_ID
871 from AD_SNAPSHOTS ASN1, AD_SNAPSHOT_FILES ASF1,
872 AD_SNAPSHOTS ASN2, AD_SNAPSHOT_FILES ASF2,
873 AD_APPL_TOPS AAT, FND_PRODUCT_GROUPS FPG,
874 AD_RELEASES rel
875 where ASN1.SNAPSHOT_ID=ASF1.SNAPSHOT_ID
876 and ASN2.SNAPSHOT_ID=ASF2.SNAPSHOT_ID
877 and ASN1.SNAPSHOT_TYPE='Q'
878 and ASN2.SNAPSHOT_TYPE='G'
879 and ASN1.SNAPSHOT_NAME='GLOBAL_VIEW'
880 and ASN2.SNAPSHOT_NAME=ASN1.SNAPSHOT_NAME
881 and ASN1.APPL_TOP_ID=ASN2.APPL_TOP_ID
882 and ASF1.FILE_ID=ASF2.FILE_ID
883 and NVL(ASF1.CONTAINING_FILE_ID, -2)=NVL(ASF2.CONTAINING_FILE_ID, -2)
884 and NVL(ASF1.FILE_VERSION_ID, -2)<>NVL(ASF2.FILE_VERSION_ID, -2)
885 and ASN1.APPL_TOP_ID=AAT.APPL_TOP_ID
886 and AAT.APPLICATIONS_SYSTEM_NAME=FPG.APPLICATIONS_SYSTEM_NAME
887 and fpg.PRODUCT_GROUP_ID=1
888 and rel.aru_release_name=fpg.aru_release_name
889 and asn1.release_id=rel.release_id
890 and asn2.release_id=rel.release_id
891 and aat.active_flag='Y'
892 and fpg.release_name=rel.major_version||'.'||
893 rel.minor_version||'.'||
894 rel.tape_version;
895
896 begin
897 for cr in crs
898 loop
899 update AD_SNAPSHOT_FILES
900 set FILE_VERSION_ID=cr.FILE_VERSION_ID
901 where SNAPSHOT_ID=cr.SNAPSHOT_ID
902 and file_id=cr.file_id
903 and nvl(containing_file_id, -2)=nvl(cr.containing_file_id, -2);
904 end loop;
905
906 for globalr in grs
907 loop
908 update AD_SNAPSHOT_FILES
909 set FILE_VERSION_ID=globalr.FILE_VERSION_ID
910 where SNAPSHOT_ID=globalr.SNAPSHOT_ID
911 and file_id=globalr.file_id
912 and nvl(containing_file_id, -2)=nvl(globalr.containing_file_id, -2);
913 end loop;
914 end;
915
916 -- Insert the missing records;
917 insert into ad_snapshot_files(SNAPSHOT_FILE_ID, SNAPSHOT_ID, FILE_ID,
918 CONTAINING_FILE_ID, FILE_SIZE, CHECKSUM,
919 FILE_VERSION_ID, UPDATE_SOURCE_ID, UPDATE_TYPE,
920 CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
921 CREATED_BY, APPL_TOP_ID, INCONSISTENT_FLAG,
922 SERVER_TYPE_ADMIN_FLAG, SERVER_TYPE_FORMS_FLAG, SERVER_TYPE_NODE_FLAG,
923 SERVER_TYPE_WEB_FLAG, DEST_FILE_ID, FILE_TYPE_FLAG,
924 IREP_GATHERED_FLAG, LAST_PATCHED_DATE)
925 select AD_SNAPSHOT_FILES_S.NEXTVAL, ouasnpat.SNAPSHOT_ID, ouasf.FILE_ID,
926 ouasf.CONTAINING_FILE_ID, ouasf.FILE_SIZE, ouasf.CHECKSUM,
927 ouasf.FILE_VERSION_ID, ouasf.UPDATE_SOURCE_ID, ouasf.UPDATE_TYPE,
928 ouasf.CREATION_DATE, ouasf.LAST_UPDATE_DATE, ouasf.LAST_UPDATED_BY,
929 ouasf.CREATED_BY, ouasf.APPL_TOP_ID, ouasf.INCONSISTENT_FLAG,
930 ouasf.SERVER_TYPE_ADMIN_FLAG, ouasf.SERVER_TYPE_FORMS_FLAG, ouasf.SERVER_TYPE_NODE_FLAG,
931 ouasf.SERVER_TYPE_WEB_FLAG, ouasf.DEST_FILE_ID, ouasf.FILE_TYPE_FLAG,
932 ouasf.IREP_GATHERED_FLAG, ouasf.LAST_PATCHED_DATE
933 from ad_snapshot_files ouasf, ad_snapshots ouasn, AD_APPL_TOPS ouaat, FND_PRODUCT_GROUPS oufpg, AD_RELEASES rel, ad_snapshots ouasnpat
934 where ouasf.SNAPSHOT_ID=OUASN.SNAPSHOT_ID
935 and ouasn.appl_top_id=ouasnpat.appl_top_id
936 and ouasn.release_id=ouasnpat.release_id
937 and ouasnpat.snapshot_type='P'
941 and OUASN.APPL_TOP_ID=OUAAT.APPL_TOP_ID
938 and ouasn.snapshot_name=ouasnpat.snapshot_name
939 and OUASN.SNAPSHOT_TYPE='C'
940 and ouasn.snapshot_name='CURRENT_VIEW'
942 and ouaat.appl_top_id=nvl(p_appl_top_id, ouaat.appl_top_id)
943 and ouaat.appl_top_type='R'
944 and ouaat.applications_system_name=oufpg.applications_system_name
945 and oufpg.product_group_id=1
946 and rel.aru_release_name=oufpg.aru_release_name
947 and ouasn.release_id=rel.release_id
948 and ouaat.active_flag='Y'
949 and oufpg.release_name=rel.major_version||'.'||
950 rel.minor_version||'.'||
951 rel.tape_version
952 and not exists (
953 select 'x'
954 from AD_SNAPSHOT_FILES ASF, AD_SNAPSHOTS ASN
955 where ASF.SNAPSHOT_ID=ASN.SNAPSHOT_ID
956 and ASN.SNAPSHOT_TYPE='P'
957 and ASN.SNAPSHOT_NAME=OUASN.SNAPSHOT_NAME
958 and ASN.APPL_TOP_ID=OUASN.APPL_TOP_ID
959 and OUASF.FILE_ID=ASF.FILE_ID
960 and NVL(OUASF.CONTAINING_FILE_ID, -2)=NVL(ASF.CONTAINING_FILE_ID, -2));
961
962 insert into ad_snapshot_files(SNAPSHOT_FILE_ID, SNAPSHOT_ID, FILE_ID,
963 CONTAINING_FILE_ID, FILE_SIZE, CHECKSUM,
964 FILE_VERSION_ID, UPDATE_SOURCE_ID, UPDATE_TYPE,
965 CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
966 CREATED_BY, APPL_TOP_ID, INCONSISTENT_FLAG,
967 SERVER_TYPE_ADMIN_FLAG, SERVER_TYPE_FORMS_FLAG, SERVER_TYPE_NODE_FLAG,
968 SERVER_TYPE_WEB_FLAG, DEST_FILE_ID, FILE_TYPE_FLAG,
969 IREP_GATHERED_FLAG, LAST_PATCHED_DATE)
970 select AD_SNAPSHOT_FILES_S.NEXTVAL, ouasnpat.SNAPSHOT_ID, ouasf.FILE_ID,
971 ouasf.CONTAINING_FILE_ID, ouasf.FILE_SIZE, ouasf.CHECKSUM,
972 ouasf.FILE_VERSION_ID, ouasf.UPDATE_SOURCE_ID, ouasf.UPDATE_TYPE,
973 ouasf.CREATION_DATE, ouasf.LAST_UPDATE_DATE, ouasf.LAST_UPDATED_BY,
974 ouasf.CREATED_BY, ouasf.APPL_TOP_ID, ouasf.INCONSISTENT_FLAG,
975 ouasf.SERVER_TYPE_ADMIN_FLAG, ouasf.SERVER_TYPE_FORMS_FLAG, ouasf.SERVER_TYPE_NODE_FLAG,
976 ouasf.SERVER_TYPE_WEB_FLAG, ouasf.DEST_FILE_ID, ouasf.FILE_TYPE_FLAG,
977 ouasf.IREP_GATHERED_FLAG, ouasf.LAST_PATCHED_DATE
978 from ad_snapshot_files ouasf, ad_snapshots ouasn, AD_APPL_TOPS ouaat, FND_PRODUCT_GROUPS oufpg, AD_RELEASES rel, ad_snapshots ouasnpat
979 where ouasf.SNAPSHOT_ID=OUASN.SNAPSHOT_ID
980 and ouasn.appl_top_id=ouasnpat.appl_top_id
981 and ouasn.release_id=ouasnpat.release_id
982 and ouasnpat.snapshot_type='Q'
983 and ouasn.snapshot_name=ouasnpat.snapshot_name
984 and OUASN.SNAPSHOT_TYPE='G'
985 and ouasn.snapshot_name='GLOBAL_VIEW'
986 and OUASN.APPL_TOP_ID=OUAAT.APPL_TOP_ID
987 and ouaat.appl_top_type='G'
988 and ouaat.applications_system_name=oufpg.applications_system_name
989 and oufpg.product_group_id=1
990 and rel.aru_release_name=oufpg.aru_release_name
991 and ouasn.release_id=rel.release_id
992 and ouaat.active_flag='Y'
993 and oufpg.release_name=rel.major_version||'.'||
994 rel.minor_version||'.'||
995 rel.tape_version
996 and not exists (
997 select 'x'
998 from AD_SNAPSHOT_FILES ASF, AD_SNAPSHOTS ASN
999 where ASF.SNAPSHOT_ID=ASN.SNAPSHOT_ID
1000 and ASN.SNAPSHOT_TYPE='Q'
1001 and ASN.SNAPSHOT_NAME=OUASN.SNAPSHOT_NAME
1002 and ASN.APPL_TOP_ID=OUASN.APPL_TOP_ID
1003 and OUASF.FILE_ID=ASF.FILE_ID
1004 and NVL(OUASF.CONTAINING_FILE_ID, -2)=NVL(ASF.CONTAINING_FILE_ID, -2));
1005 end SYNC_SNAPSHOTS;
1006
1007 procedure abort_patch_info(x_patch_run_id in number)
1008 is
1009 v_blvl varchar2(150);
1010 v_clvl varchar2(150);
1011 v_query varchar2(4000);
1012 t_or_v_not_exist exception;
1013 PRAGMA EXCEPTION_INIT(t_or_v_not_exist, -942);
1014 v_process_cfhistory boolean := true;
1015
1016 cursor bugs is
1017 select PATCH_RUN_BUG_ID
1018 from ad_patch_run_bugs
1019 where PATCH_RUN_ID=x_patch_run_id;
1020
1021 cursor tes is
1022 select distinct abbreviation
1023 from ad_te_level_history
1024 where patch_run_id=x_patch_run_id;
1025
1026 begin
1027
1028 -- Cleaning ad_patch_run_bug_actions
1029 for run_bug_id in bugs
1030 loop
1031 delete ad_patch_run_bug_actions
1032 where PATCH_RUN_BUG_ID=run_bug_id.patch_run_bug_id;
1033 end loop;
1034
1035 -- Cleaning ad_patch_run_bug
1036 delete ad_patch_run_bugs where patch_run_id=x_patch_run_id;
1037
1038 -- Cleaning Codelevels
1039 for te in tes
1040 loop
1041 delete AD_TE_LEVEL_HISTORY
1042 where patch_run_id=x_patch_run_id and abbreviation=te.abbreviation;
1043
1044 begin
1045 select max(baseline), max(codelevel)
1046 into v_blvl, v_clvl
1047 from AD_TE_LEVEL_HISTORY
1048 where abbreviation=te.abbreviation;
1049
1050 update ad_trackable_entities
1051 set baseline=v_blvl, codelevel=v_clvl
1052 where abbreviation=te.abbreviation;
1053 exception
1054 when no_data_found
1055 then
1056 delete ad_trackable_entities
1060 end loop;
1057 where abbreviation=te.abbreviation;
1058 when others then null;
1059 end;
1061
1062 -- Cleaning ad_patch_runs
1063 delete ad_patch_runs where patch_run_id=x_patch_run_id;
1064
1065 -- Cleaning Checkfile Repository
1066 -- Delete new records created by patch
1067 v_query :=
1068 'delete ad_check_files acf ' ||
1069 'where not exists ' ||
1070 ' (select ''x'' ' ||
1071 ' from ad_check_file_history acfh ' ||
1072 ' where acfh.check_file_id=acf.check_file_id ' ||
1073 ' and acfh.patch_run_id not in (:1, :2)) ';
1074
1075 begin
1076 execute immediate v_query using x_patch_run_id, -1;
1077 v_process_cfhistory := true;
1078 exception when t_or_v_not_exist then
1079 v_process_cfhistory := false;
1080 end;
1081
1082 if (v_process_cfhistory = true)
1083 then
1084 declare
1085 TYPE cur_typ IS REF CURSOR;
1086 cs cur_typ;
1087 v_prid number;
1088 v_cfid number;
1089 v_qry varchar2(2000);
1090 -- Update existing records with older file_version_id
1091 begin
1092
1093 v_query := 'select max(ACFH2.PATCH_RUN_ID) PRID, ' ||
1094 ' ACFH2.CHECK_FILE_ID CFID ' ||
1095 'from AD_CHECK_FILE_HISTORY ACFH1, ' ||
1096 ' AD_CHECK_FILE_HISTORY ACFH2 ' ||
1097 'where ACFH1.CHECK_FILE_ID=ACFH2.CHECK_FILE_ID ' ||
1098 'and ACFH1.PATCH_RUN_ID in (:1, :2) ' ||
1099 'and ACFH2.PATCH_RUN_ID not in (:3, :4) ' ||
1100 'group by ACFH2.CHECK_FILE_ID';
1101
1102 open cs for v_query using x_patch_run_id, -1, x_patch_run_id, -1;
1103 loop
1104 fetch cs into v_prid, v_cfid;
1105 exit when cs%NOTFOUND;
1106
1107 v_qry := 'update ad_check_files acf ' ||
1108 'set acf.file_version_id=( ' ||
1109 ' select acfh.file_Version_id ' ||
1110 ' from ad_check_file_history acfh ' ||
1111 ' where acfh.patch_run_id=:1' ||
1112 ' and acfh.check_file_id=:2) ' ||
1113 'where acf.check_file_id=:3';
1114 execute immediate v_qry using v_prid, v_cfid, v_cfid;
1115 end loop;
1116
1117 v_qry := 'delete ad_check_file_history acfh ' ||
1118 'where acfh.patch_run_id in (:1, :2)';
1119
1120 execute immediate v_qry using x_patch_run_id, -1;
1121 end;
1122 end if;
1123 SYNC_SNAPSHOTS;
1124 end abort_patch_info;
1125
1126 procedure ABORT(X_MODE in varchar2 default null, X_SESSION_ID in number) is
1127 cursor prids is
1128 SELECT AASP.PATCHRUN_ID
1129 FROM AD_ADOP_SESSION_PATCHES AASP
1130 WHERE AASP.ADOP_SESSION_ID=x_session_id
1131 AND AASP.PATCHRUN_ID IS NOT NULL
1132 UNION
1133 SELECT ACFH.PATCH_RUN_ID
1134 FROM AD_CHECK_FILE_HISTORY ACFH,
1135 AD_ADOP_SESSION_PATCHES aasp
1136 WHERE ACFH.PATCH_RUN_ID>AASP.PATCHRUN_ID
1137 AND AASP.ADOP_SESSION_ID=x_session_id
1138 AND AASP.PATCHRUN_ID<>-1
1139 AND AASP.PATCHRUN_ID IS NOT NULL;
1140
1141 begin
1142
1143 ad_zd.abort(x_mode=>X_MODE);
1144 for prid in prids
1145 loop
1146 abort_patch_info(prid.patchrun_id);
1147 end loop;
1148 end ABORT;
1149
1150 procedure CUTOVER is
1151 begin
1152 ad_zd.cutover('QUICK');
1153 end CUTOVER;
1154
1155 procedure FLIP_SNAPSHOTS is
1156 cursor snps is
1157 select AAT.APPL_TOP_ID,
1158 ASN.SNAPSHOT_ID,
1159 ASN.SNAPSHOT_NAME,
1160 ASN.SNAPSHOT_TYPE,
1161 AR.RELEASE_ID
1162 FROM AD_APPL_TOPS AAT,
1163 AD_SNAPSHOTS ASN,
1164 AD_RELEASES AR,
1165 FND_PRODUCT_GROUPS FPG,
1166 FND_NODES FN
1167 WHERE AAT.APPL_TOP_TYPE='R'
1168 and aat.active_flag='Y'
1169 AND AAT.APPL_TOP_ID=ASN.APPL_TOP_ID
1170 and ASN.SNAPSHOT_TYPE in ('C', 'P')
1171 and ASN.SNAPSHOT_NAME='CURRENT_VIEW'
1172 and AR.RELEASE_ID=ASN.RELEASE_ID
1173 and AR.MAJOR_VERSION||'.'||AR.MINOR_VERSION||'.'||AR.TAPE_VERSION=FPG.RELEASE_NAME
1174 and FPG.APPLICATIONS_SYSTEM_NAME = AAT.APPLICATIONS_SYSTEM_NAME
1175 and UPPER(FN.NODE_NAME)=UPPER(AAT.name)
1176 and ( FN.SUPPORT_CP='Y' or FN.SUPPORT_FORMS='Y' or FN.SUPPORT_WEB='Y' or FN.SUPPORT_ADMIN='Y')
1177 and fn.node_name is not null
1178 union
1179 select AAT.APPL_TOP_ID,
1180 ASN.SNAPSHOT_ID,
1181 ASN.SNAPSHOT_NAME,
1182 ASN.SNAPSHOT_TYPE,
1183 AR.RELEASE_ID
1184 FROM AD_APPL_TOPS AAT,
1185 AD_SNAPSHOTS ASN,
1186 AD_RELEASES AR,
1187 FND_PRODUCT_GROUPS FPG
1188 WHERE AAT.APPL_TOP_TYPE='G'
1189 and aat.active_flag='Y'
1190 and aat.name='GLOBAL'
1191 AND AAT.APPL_TOP_ID=ASN.APPL_TOP_ID
1192 and ASN.SNAPSHOT_TYPE in ('G', 'Q')
1193 and ASN.SNAPSHOT_NAME='GLOBAL_VIEW'
1194 and AR.RELEASE_ID=ASN.RELEASE_ID
1195 and AR.MAJOR_VERSION||'.'||AR.MINOR_VERSION||'.'||AR.TAPE_VERSION=FPG.RELEASE_NAME
1196 and FPG.APPLICATIONS_SYSTEM_NAME = AAT.APPLICATIONS_SYSTEM_NAME;
1197
1198 begin
1199 -- Update release_id if necessary
1200 for snp in snps
1201 loop
1202 update ad_snapshots
1203 set release_id=snp.release_id
1204 where appl_top_id=snp.appl_top_id
1205 and snapshot_type=decode(snp.snapshot_type, 'P', 'C', 'Q', 'G')
1206 and snapshot_name=snp.snapshot_name
1207 and release_id<>snp.release_id;
1211 update ad_snapshots
1208 end loop;
1209
1210 -- Flip snapshots
1212 set snapshot_type=DECODE(snapshot_type, 'C', 'P', 'P', 'C',
1213 'G', 'Q', 'Q', 'G')
1214 where snapshot_id in (
1215 select ASN.SNAPSHOT_ID
1216 FROM AD_APPL_TOPS AAT,
1217 AD_SNAPSHOTS ASN,
1218 AD_RELEASES AR,
1219 FND_PRODUCT_GROUPS FPG,
1220 FND_NODES FN
1221 WHERE AAT.APPL_TOP_TYPE='R'
1222 and aat.active_flag='Y'
1223 AND AAT.APPL_TOP_ID=ASN.APPL_TOP_ID
1224 and ASN.SNAPSHOT_TYPE in ('C', 'P')
1225 and ASN.SNAPSHOT_NAME='CURRENT_VIEW'
1226 and AR.RELEASE_ID=ASN.RELEASE_ID
1227 and AR.MAJOR_VERSION||'.'||AR.MINOR_VERSION||'.'||AR.TAPE_VERSION=FPG.RELEASE_NAME
1228 and FPG.APPLICATIONS_SYSTEM_NAME = AAT.APPLICATIONS_SYSTEM_NAME
1229 and UPPER(FN.NODE_NAME)=UPPER(AAT.name)
1230 and ( FN.SUPPORT_CP='Y' or FN.SUPPORT_FORMS='Y' or FN.SUPPORT_WEB='Y' or FN.SUPPORT_ADMIN='Y' )
1231 and fn.node_name is not null
1232 union
1233 select ASN.SNAPSHOT_ID
1234 FROM AD_APPL_TOPS AAT,
1235 AD_SNAPSHOTS ASN,
1236 AD_RELEASES AR,
1237 FND_PRODUCT_GROUPS FPG
1238 WHERE AAT.APPL_TOP_TYPE='G'
1239 and aat.active_flag='Y'
1240 AND AAT.APPL_TOP_ID=ASN.APPL_TOP_ID
1241 and ASN.SNAPSHOT_TYPE in ('G', 'Q')
1242 and ASN.SNAPSHOT_NAME='GLOBAL_VIEW'
1243 and AR.RELEASE_ID=ASN.RELEASE_ID
1244 and AR.MAJOR_VERSION||'.'||AR.MINOR_VERSION||'.'||AR.TAPE_VERSION=FPG.RELEASE_NAME
1245 and FPG.APPLICATIONS_SYSTEM_NAME = AAT.APPLICATIONS_SYSTEM_NAME);
1246
1247 end FLIP_SNAPSHOTS;
1248
1249 PROCEDURE WAIT_FOR_DB_CUTOVER(p_session_id in number) is
1250 l_mod_name varchar2(25) := 'WAIT_FOR_DB_CUTOVER';
1251 l_cutover_status varchar2(1);
1252 l_node_name varchar2(31);
1253 l_appltop_id number;
1254 l_status varchar2(1);
1255 type array_t is varray(8) of varchar2(30);
1256 l_break boolean := false;
1257 -- The sequence of the below array should match with the perl/ADOP/CutoverPhase.pm
1258 cutover_statuses array_t := array_t('N',
1259 '0', --force_shutdown_begin
1260 '1', --force_shutdown
1261 '3', --db_cutover
1262 'D', --flip_snapshots
1263 '4', --fs_cutover
1264 '6', --force_startup
1265 'Y');
1266 begin
1267 select node_name,appltop_id into l_node_name,l_appltop_id
1268 from ad_adop_sessions
1269 where node_type='master' and adop_session_id=p_session_id;
1270 loop
1271 l_cutover_status :=
1272 get_cutover_status(l_appltop_id,l_node_name,p_session_id);
1273 for i in 1..cutover_statuses.count
1274 loop
1275 if (cutover_statuses(i) = '3')
1276 then
1277 return;
1278 end if;
1279 if (l_cutover_status = cutover_statuses(i))
1280 then
1281 exit;
1282 end if;
1283 end loop;
1284 select status into l_status from ad_adop_sessions
1285 where node_type='master' and adop_session_id=p_session_id;
1286 if(l_status = 'F') then
1287 RAISE_APPLICATION_ERROR(-20010,'ERROR: Master in failure status');
1288 else
1289 dbms_lock.sleep(60);
1290 end if;
1291 end loop;
1292 end WAIT_FOR_DB_CUTOVER;
1293
1294 PROCEDURE INSERT_CLONE_TRACKING_ROW(p_run_base in varchar2,
1295 p_patch_base in varchar2)
1296 is
1297 l_mod_name varchar2(30) := 'INSERT_CLONE_TRACKING_ROW';
1298 l_adop_session_id number:= 0;
1299 l_clone_exists number;
1300 l_appl_top_id number;
1301 cursor nodes is
1302 select distinct fn.host host
1303 from fnd_product_groups fpg, ad_appl_tops aat,
1304 ad_releases ar, fnd_nodes fn
1305 where aat.appl_top_type='R'
1306 and aat.applications_system_name=fpg.applications_system_name
1307 and aat.active_flag='Y'
1308 and fpg.release_name=ar.major_version||'.'||ar.minor_version||'.'||ar.tape_version
1309 and fpg.aru_release_name=ar.aru_release_name
1310 and fn.host is not null
1311 and ( fn.support_cp='Y' or fn.support_forms='Y' or FN.SUPPORT_WEB='Y' or FN.SUPPORT_ADMIN='Y');
1312 begin
1313 SELECT NVL(MAX(ADOP_SESSION_ID),0)
1314 INTO l_adop_session_id
1315 FROM AD_ADOP_SESSIONS
1316 WHERE APPLY_STATUS IN ('P','N')
1317 AND PREPARE_STATUS IN ('Y','X')
1318 AND ABORT_STATUS<> 'Y'
1319 AND CLEANUP_STATUS <> 'Y';
1320
1321 if(l_adop_session_id = 0)
1322 then
1323 l_adop_session_id := ad_adop_session_id_seq.nextval;
1324 end if;
1325
1326 for node in nodes
1327 loop
1328 SELECT aat.appl_top_id
1329 into l_appl_top_id
1330 FROM FND_OAM_CONTEXT_FILES focf,
1331 fnd_product_groups fpg,
1332 ad_appl_tops aat,
1333 ad_releases ar
1334 WHERE focf.NAME not in ('TEMPLATE','METADATA','config.txt')
1335 and focf.CTX_TYPE='A'
1336 and (focf.status is null or upper(focf.status) in ('S','F'))
1337 and EXTRACTVALUE(XMLType(focf.TEXT),'//file_edition_type') = 'run'
1338 and focf.node_name=node.host
1339 and aat.appl_top_type='R'
1340 and aat.applications_system_name=fpg.applications_system_name
1341 and aat.active_flag='Y'
1342 and fpg.release_name=ar.major_version||'.'||
1343 ar.minor_version||'.'||
1347
1344 ar.tape_version
1345 and fpg.aru_release_name=ar.aru_release_name
1346 and aat.name=EXTRACTVALUE(XMLType(focf.TEXT),'//APPL_TOP_NAME');
1348 select count(1) into l_clone_exists
1349 from ad_adop_session_patches
1350 where APPLTOP_ID=l_appl_top_id and node_name=node.host
1351 and bug_number='CLONE' and status='N'
1352 and clone_status='NOT-STARTED';
1353
1354 if (l_clone_exists = 0)
1355 then
1356 insert into ad_adop_session_patches(ADOP_SESSION_ID, BUG_NUMBER, STATUS,
1357 APPLIED_FILE_SYSTEM_BASE, PATCH_FILE_SYSTEM_BASE, APPLTOP_ID,
1358 NODE_NAME, AUTOCONFIG_STATUS, START_DATE, CLONE_STATUS)
1359 values (l_adop_session_id, 'CLONE', 'N', p_run_base, p_patch_base,
1360 l_appl_top_id, node.host, 'N', sysdate, 'NOT-STARTED');
1361 end if;
1362 end loop;
1363 end INSERT_CLONE_TRACKING_ROW;
1364
1365 FUNCTION IS_ICM_ALIVE RETURN NUMBER
1366 IS
1367 BEGIN
1368 if(fnd_conc.icm_alive(false)) then
1369 return 1;
1370 else
1371 return 0;
1372 end if;
1373 END IS_ICM_ALIVE;
1374
1375 FUNCTION EVAL_SRV_STATUS(avail_node_list in VARCHAR2) RETURN BOOLEAN IS
1376
1377 l_mod_name varchar2(25) := 'EVAL_SRV_STATUS';
1378 TYPE service_map_type IS TABLE OF NUMBER INDEX BY VARCHAR2(30);
1379 TYPE service_varray IS VARRAY(20) OF VARCHAR2(40);
1380
1381 service_map service_map_type;
1382 group_service_map service_map_type;
1383
1384 avail_service_map service_map_type;
1385 avail_group_service_map service_map_type;
1386
1387 services service_varray := service_varray('s_nodemanagerstatus','s_adminserverstatus',
1388 's_opmnstatus','s_apcstatus','s_oacorestatus',
1389 's_formsstatus','s_oafmstatus','s_forms-c4wsstatus',
1390 's_tnsstatus','s_concstatus','s_icsmstatus',
1391 's_jtffsstatus','s_formsserver_status',
1392 's_metcstatus','s_metsstatus','s_mwastatus');
1393
1394 group_services service_varray := service_varray('s_root_status','s_web_admin_status',
1395 's_web_entry_status','s_web_applications_status',
1396 's_batch_status','s_other_service_group_status');
1397
1398 cursor service_status is
1399 select
1400 (XMLQuery(
1401 ' for $i in /oa_context/oa_services/oa_service_list/oa_service/oa_service_status
1402 where $i/text() eq "enabled"
1403 return fn:concat($i/@oa_var,",") '
1404 PASSING XMLType(TEXT) RETURNING CONTENT
1405 )).getStringVal() services, node_name node
1406 from FND_OAM_CONTEXT_FILES
1407 where NAME not in ('TEMPLATE','METADATA','config.txt') and
1408 CTX_TYPE='A' and (status is null or upper(status) in ('S','F'))
1409 and EXTRACTVALUE(XMLType(TEXT),'//file_edition_type') = 'patch';
1410
1411
1412 cursor group_service_status is
1413 select
1414 (XMLQuery(
1415 ' for $i in /oa_context/oa_services/oa_service_group_list/oa_service_group/oa_service_group_status
1416 where $i/text() eq "enabled"
1417 return fn:concat($i/@oa_var,",") '
1418 PASSING XMLType(TEXT) RETURNING CONTENT
1419 )).getStringVal() group_services, node_name node
1420 from FND_OAM_CONTEXT_FILES
1421 where NAME not in ('TEMPLATE','METADATA','config.txt') and
1422 CTX_TYPE='A' and (status is null or upper(status) in ('S','F'))
1423 and EXTRACTVALUE(XMLType(TEXT),'//file_edition_type')= 'patch';
1424
1425
1426 BEGIN
1427
1428 log(l_mod_name,'STATEMENT', 'Node List:'||avail_node_list);
1429
1430 if(avail_node_list is NULL) then
1431 return FALSE;
1432 end if;
1433
1434 for i IN services.FIRST..services.LAST loop
1435 service_map(services(i)) := 0;
1436 avail_service_map(services(i)) := 0;
1437 end loop;
1438
1439 for i IN group_services.FIRST..group_services.LAST loop
1440 group_service_map(group_services(i)) := 0;
1441 avail_group_service_map(group_services(i)) := 0;
1442 end loop;
1443
1444 for rec in service_status loop
1445 for i IN services.FIRST..services.LAST loop
1446 if (INSTR( rec.services, services(i) ) > 0) then
1447 service_map(services(i)) := service_map(services(i)) + 1;
1448 if (INSTR(avail_node_list, rec.node ) > 0) then
1449 avail_service_map(services(i)) := avail_service_map(services(i)) + 1;
1450 end if;
1451 end if;
1452
1453 end loop;
1454 end loop;
1455
1456 for rec in group_service_status loop
1457 for i IN group_services.FIRST..group_services.LAST loop
1458 if (INSTR( rec.group_services, group_services(i) ) > 0) then
1459 group_service_map(group_services(i)) := group_service_map(group_services(i)) + 1;
1460
1461 if (INSTR(avail_node_list, rec.node ) > 0) then
1462 avail_group_service_map(group_services(i)) := avail_group_service_map(group_services(i)) + 1;
1463 end if;
1464
1465 end if;
1466 end loop;
1467 end loop;
1468
1469 for i IN services.FIRST..services.LAST loop
1470 if ( (service_map(services(i)) > 0) and (avail_service_map(services(i)) = 0) ) then
1471 return FALSE;
1472 end if;
1473 end loop;
1474
1475 for i IN group_services.FIRST..group_services.LAST loop
1476 if ( (group_service_map(group_services(i)) = 1) and (avail_group_service_map(group_services(i)) = 0) ) then
1477 return FALSE;
1478 end if;
1479 end loop;
1480
1481 return TRUE;
1482 END EVAL_SRV_STATUS;
1483
1487
1484 FUNCTION IS_ABORTABLE RETURN BOOLEAN IS
1485 abort_status VARCHAR2(1);
1486 BEGIN
1488 begin
1489 select abort_status into abort_status from ad_adop_sessions
1490 where prepare_status <> 'X' and node_type='master' and
1491 adop_session_id = (select max(adop_session_id) from ad_adop_sessions where
1492 prepare_status <> 'X' and node_type='master');
1493 exception
1494 when no_data_found then
1495 return FALSE;
1496 end;
1497
1498 if(abort_status <> 'X' and abort_status <> 'Y') then
1499 return TRUE;
1500 end if;
1501 return FALSE;
1502 END IS_ABORTABLE;
1503
1504 FUNCTION ADOP_HEALTH_CHECK(phase in VARCHAR2,node in VARCHAR2) RETURN NUMBER IS
1505
1506 session_id NUMBER;
1507 cnt NUMBER := 0;
1508 cnt1 NUMBER := 0;
1509 TYPE node_map_type IS TABLE OF NUMBER INDEX BY VARCHAR2(30);
1510 node_map node_map_type;
1511 node_list VARCHAR2(32767) := NULL;
1512 succ_flag BOOLEAN := TRUE;
1513
1514 BEGIN
1515 --get the latest normal patching cycle session id
1516 begin
1517 select max(adop_session_id) into session_id from ad_adop_sessions
1518 where prepare_status <> 'X' and node_type='master' and cleanup_status <> 'Y' ;
1519 exception
1520 when no_data_found then
1521 return 0;
1522 end;
1523
1524 for rec in (select node_name from ad_adop_sessions where adop_session_id = session_id) loop
1525 node_map(rec.node_name) := 1;
1526 end loop;
1527
1528 if (phase = 'prepare') then
1529 select count(1) into cnt from ad_adop_sessions where adop_session_id = session_id
1530 and prepare_status <> 'Y';
1531
1532 if ( cnt > 0 ) then
1533 for rec in (select node_name from ad_adop_sessions where adop_session_id
1534 = session_id and prepare_status <> 'Y') loop
1535 node_map(rec.node_name) := 0;
1536 end loop;
1537 end if;
1538
1539 end if;
1540
1541 if (phase = 'apply') then
1542 if node is not NULL then
1543 select count(1) into cnt from ad_adop_sessions where adop_session_id =
1544 session_id and node_name = node and ((apply_status = 'N') or
1545 (apply_status='P' and status in ('F','R')));
1546
1547 if (cnt > 0 ) then
1548 node_map(node) := 0;
1549 end if;
1550 else
1551 select count(1) into cnt from ad_adop_sessions where adop_session_id = session_id
1552 and ((apply_status = 'N' and status in ('C','N')) or (apply_status ='P' and
1553 status = 'F'));
1554
1555 select count(1) into cnt1 from ad_adop_sessions where adop_session_id =
1556 session_id and apply_status in ('P','Y') and status='C';
1557
1558 if(cnt <> 0 and cnt1 > 0) then
1559 for rec in (select node_name from ad_adop_sessions where adop_session_id
1560 = session_id and ((apply_status = 'N' and status in ('C','N')) or
1561 (apply_status ='P' and status = 'F'))) loop
1562 node_map(rec.node_name) := 0;
1563 end loop;
1564 end if;
1565 end if;
1566 end if;
1567
1568 if (phase = 'cutover') then
1569 select count(1) into cnt from ad_adop_sessions where adop_session_id =
1570 session_id and cutover_status not in ('X','Y');
1571
1572 if ( cnt > 0 ) then
1573 for rec in (select node_name from ad_adop_sessions where adop_session_id
1574 = session_id and cutover_status not in ('X','Y')) loop
1575 node_map(rec.node_name) := 0;
1576 end loop;
1577 end if;
1578 end if;
1579
1580 if (phase = 'abort') then
1581 select count(1) into cnt from ad_adop_sessions where adop_session_id =
1582 session_id and abort_status not in ('X','Y');
1583
1584 if ( cnt > 0 ) then
1585 for rec in (select node_name from ad_adop_sessions where adop_session_id
1586 = session_id and abort_status not in ('X','Y')) loop
1587 node_map(rec.node_name) := 0;
1588 end loop;
1589 end if;
1590 end if;
1591
1592 if(node is not NULL) then
1593 if(node_map(node) = 0) then
1594 return 1;
1595 else
1596 return 2;
1597 end if;
1598 end if;
1599
1600 for rec in (select node_name from ad_adop_sessions where adop_session_id = session_id) loop
1601 if ((node_map(rec.node_name)) = 1) then
1602 node_list := node_list || ',' || rec.node_name;
1603 else
1604 succ_flag := FALSE;
1605 end if;
1606 end loop;
1607
1608 if(succ_flag) then
1609 return 3;
1610 end if;
1611
1612 if(EVAL_SRV_STATUS(node_list)) then
1613 return 2;
1614 else
1615 return 1;
1616 end if;
1617
1618 END ADOP_HEALTH_CHECK;
1619
1620
1621 FUNCTION IS_ABANDONED(node in varchar2) RETURN NUMBER IS
1622 l_session_id number;
1623 l_abandon_flag number;
1624 l_cnt number;
1625
1626 BEGIN
1627
1628 select max(adop_session_id) into l_session_id
1629 from ad_adop_sessions
1630 where node_type='master' and prepare_status <> 'X';
1631
1632 select abandon_flag into l_abandon_flag
1633 from ad_adop_sessions
1634 where adop_session_id = l_session_id and node_name = node;
1635
1636 if (l_abandon_flag <> l_session_id) then
1637 return 2;
1638 else
1639 select count(1) into l_cnt from ad_adop_sessions slave, ad_adop_sessions master
1640 where
1641 (slave.prepare_status <> master.prepare_status or
1642 slave.apply_status <> master.apply_status or
1643 slave.cutover_status <> master.cutover_status or
1644 slave.abort_status <> master.abort_status) and
1645 master.adop_session_id = l_session_id and
1649
1646 slave.adop_session_id = l_session_id and
1647 master.node_type = 'master' and
1648 slave.node_name = node;
1650 if l_cnt > 0 then
1651 return 1;
1652 end if;
1653 end if;
1654 return 0;
1655 END IS_ABANDONED;
1656
1657 FUNCTION GET_ABANDONED_NODES(p_mode in varchar2) RETURN VARCHAR2 IS
1658 l_session_id number;
1659 l_node_list VARCHAR2(32767) := NULL;
1660
1661 BEGIN
1662
1663 select max(adop_session_id) into l_session_id
1664 from ad_adop_sessions
1665 where node_type='master' and prepare_status <> 'X';
1666
1667 if (p_mode = 'ABANDONED') then
1668 for rec in (select node_name from ad_adop_sessions where
1669 adop_session_id=l_session_id and
1670 abandon_flag <> l_session_id)
1671 loop
1672 if l_node_list is NULL then
1673 l_node_list := rec.node_name;
1674 else
1675 l_node_list := l_node_list || ',' || rec.node_name;
1676 end if;
1677 end loop;
1678 else
1679 for rec in (
1680 select slave.node_name node_name from ad_adop_sessions slave,ad_adop_sessions master
1681 where
1682 (slave.prepare_status <> master.prepare_status or
1683 slave.apply_status <> master.apply_status or
1684 slave.cutover_status <> master.cutover_status or
1685 slave.abort_status <> master.abort_status) and
1686 master.adop_session_id = l_session_id and
1687 slave.adop_session_id = l_session_id and
1688 slave.abandon_flag = l_session_id and
1689 master.node_type = 'master' and
1690 slave.node_type = 'slave')
1691 loop
1692 if l_node_list is NULL then
1693 l_node_list := rec.node_name;
1694 else
1695 l_node_list := l_node_list || ',' || rec.node_name;
1696 end if;
1697 end loop;
1698 end if;
1699 return l_node_list;
1700 END GET_ABANDONED_NODES;
1701
1702 PROCEDURE CLEAR_ABANDON_FLAG(dest_node in varchar2) IS
1703 BEGIN
1704
1705 update ad_adop_sessions set abandon_flag = NULL
1706 where node_name=dest_node and abandon_flag is not null and
1707 adop_session_id = (select max(adop_session_id) from ad_adop_sessions where
1708 ((prepare_status='Y' and apply_status='Y' and cutover_status='Y') or
1709 (abort_status='Y')) and node_type='master');
1710 commit;
1711
1712 END CLEAR_ABANDON_FLAG;
1713
1714 --returns active patching Cycle ADOP Session ID
1715 --we also can extend this api in future
1716 PROCEDURE GET_ACTIV_PATCHING_SES_DETAILS(id out NOCOPY NUMBER) IS
1717 begin
1718 select max(adop_session_id)
1719 into id
1720 from ad_adop_sessions
1721 where prepare_status <> 'X'
1722 and node_type='master'
1723 and cutover_status = 'N'
1724 and abort_status='N';
1725 end GET_ACTIV_PATCHING_SES_DETAILS;
1726
1727 END AD_ZD_ADOP;
[Home] [Help]