1 package body ad_patch as
2 /* $Header: adphpchb.pls 120.12.12020000.3 2013/03/06 13:41:47 rraam ship $ */
3
4 VER_SEPARATOR CONSTANT varchar2(1) := '.';
5 CURRENT_VIEW_SNP_NAME CONSTANT varchar2(12) := 'CURRENT_VIEW';
6 GLOBAL_VIEW_SNP_NAME CONSTANT varchar2(11) := 'GLOBAL_VIEW';
7 g_errm varchar2(132);
8
9 /**************************************************************************
10 Returns whether or not the patch has been applied.
11
12 release_name - the aru release name (e.g., '11i')
13 bug_number - the bug number (e.g., '1234567')
14
15 returns
16 NOT_APPLIED, IMPLICITLY_APPLIED, or EXPLICITLY_APPLIED
17 **************************************************************************/
18
19 /**************************************************************************
20 New version to go with new proposed data model changes (11.5.6 maybe),
21 resulting in simpler joins. This function queries on AD_SNAPSHOT_BUGFIXES
22 bug_status column to report the status of a bug.
23 With the snapshot project, and changes to the patch history data
24 model, the bug_status column is moved to ad_snapshot_bugfixes and hence
25 the query has to join on ad_snapshots,ad_snapshot_bugfixes and ad_bugs
26 tables.
27 2/15/02 : app_short_name is moved from AD_BUGS to AD_PATCH_RUN_BUGS
28 due to AOL's requirement, since AOL doesn't always know
29 app_short_name to pass.
30 **************************************************************************/
31 function is_patch_applied (p_release_name in varchar2,
32 p_appl_top_id in number,
33 p_bug_number in varchar2)
34 return varchar2
35 is
36 begin
37
38 return is_patch_applied(p_release_name, p_appl_top_id,
39 p_bug_number, 'US');
40 end is_patch_applied;
41
42 function is_patch_applied (p_release_name in varchar2,
43 p_appl_top_id in number,
44 p_bug_number in varchar2,
45 p_bug_language in varchar2)
46 return varchar2
47 is
48 cursor FIND_BUG_ID_CURSOR is
49 select b.bug_id
50 from AD_BUGS b, AD_TRACKABLE_ENTITIES adte
51 where b.bug_number = p_bug_number
52 and upper(b.trackable_entity_abbr) =
53 DECODE(UPPER(adte.abbreviation),'SO', 'SQLSO',
54 'FA', 'OFA',
55 'AP', 'SQLAP',
56 'GL', 'SQLGL',
57 UPPER(adte.abbreviation))
58 and b.language = p_bug_language
59 and ( b.baseline_name = adte.baseline
60 or b.generic_patch = 'y'
61 );
62
63 l_bugst ad_snapshot_bugfixes.bug_status%type;
64 l_bugid ad_bugs.bug_id%type;
65 l_snapshot_id ad_snapshots.snapshot_id%type;
66
67 l_row_found boolean := TRUE;
68 begin
69
70 if (p_bug_number in ('13839836', '13839844', '14100919', '16399723'))
71 then
72 return(NOT_APPLIED);
73 end if;
74
75 if (p_appl_top_id = -1)
76 then
77 begin
78 select s.snapshot_id into l_snapshot_id
79 from AD_SNAPSHOTS s, AD_APPL_TOPS a,
80 FND_PRODUCT_GROUPS fpg
81 where s.snapshot_name = GLOBAL_VIEW_SNP_NAME
82 and s.snapshot_type =
83 ad_file_sys_snapshots_pkg.get_snapshot_type(GLOBAL_VIEW_SNP_NAME)
84 and s.appl_top_id = a.appl_top_id
85 and a.name = 'GLOBAL'
86 and a.appl_top_type = 'G'
87 and a.applications_system_name = fpg.applications_system_name
88 and fpg.product_group_id=1;
89 exception
90 when no_data_found then
91 return(NOT_APPLIED);
92 end;
93 else
94 begin
95 select snapshot_id into l_snapshot_id
96 from AD_SNAPSHOTS
97 where appl_top_id = p_appl_top_id
98 and snapshot_name = CURRENT_VIEW_SNP_NAME
99 and snapshot_type =
100 ad_file_sys_snapshots_pkg.get_snapshot_type(CURRENT_VIEW_SNP_NAME);
101 exception
102 when no_data_found then
103 return(NOT_APPLIED);
104 end;
105 end if;
106
107 begin
108 for cur_bugid in FIND_BUG_ID_CURSOR loop
109 l_row_found := TRUE;
110 begin
111 select bug_status into l_bugst
112 from ad_snapshot_bugfixes
113 where bugfix_id = cur_bugid.bug_id
114 and snapshot_id = l_snapshot_id;
115 exception
116 when no_data_found then
117 l_row_found := FALSE;
118 end;
119 if (l_row_found) then
120 if (not l_bugst = NOT_APPLIED) then
121 -- if a row was found and the
122 -- bug_status is other than 'NOT_APPLIED'
123 -- return this status
124 return(l_bugst);
125 end if;
126 end if;
127 end loop;
128
129 -- either no row was found or bug_status = 'NOT_APPLIED'
130 -- for all the rows that were found when we reach this point
131 return (NOT_APPLIED);
132 end;
133 end is_patch_applied;
134 /**************************************************************************
135 Analyuzes whether or not the codeline patch has been applied.
136
137 release_name - the aru release name - 'R12'
138 baseline_name - baseline value - (eg., 'A',B',R12')
139 appl_top_id - id of appl top.( eg., -1, 123 )
140 bug_number - the bug number (e.g., '1234567')
141 langauage - the language of the patch (e.g. 'D', 'US' )
142
143 returns
144 NOT_APPLIED, IMPLICITLY_APPLIED, or EXPLICITLY_APPLIED
145 **************************************************************************/
146
147
148 function is_codeline_patch_applied ( p_release_name in varchar2,
149 p_baseline_name in varchar2,
150 p_appl_top_id in number,
151 p_bug_number in varchar2)
152 return varchar2
153 is
154 begin
155
156 return is_codeline_patch_applied ( p_release_name, p_baseline_name,
157 p_appl_top_id, p_bug_number,
158 'US');
159 end is_codeline_patch_applied;
160
161
162 function is_codeline_patch_applied ( p_release_name in varchar2,
163 p_baseline_name in varchar2,
164 p_appl_top_id in number,
165 p_bug_number in varchar2,
166 p_language in varchar2)
167 return varchar2
168 is
169 l_snapshot_id ad_snapshots.snapshot_id%type;
170 l_bug_id ad_bugs.bug_id%type;
171 l_bugst ad_snapshot_bugfixes.bug_status%type;
172 BEGIN
173
174 if (p_appl_top_id = -1)
175 then
176 begin
177 select s.snapshot_id into l_snapshot_id
178 from AD_SNAPSHOTS s, AD_APPL_TOPS a,
179 FND_PRODUCT_GROUPS fpg
180 where s.snapshot_name = GLOBAL_VIEW_SNP_NAME
181 and s.snapshot_type =
182 ad_file_sys_snapshots_pkg.get_snapshot_type(GLOBAL_VIEW_SNP_NAME)
183 and s.appl_top_id = a.appl_top_id
184 and a.name = 'GLOBAL'
185 and a.appl_top_type = 'G'
186 and a.applications_system_name = fpg.applications_system_name
187 and fpg.product_group_id=1;
188 exception
189 when no_data_found then
190 return(NOT_APPLIED);
191 end;
192 else
193 begin
194 select snapshot_id into l_snapshot_id
195 from AD_SNAPSHOTS
196 where appl_top_id = p_appl_top_id
197 and snapshot_name = CURRENT_VIEW_SNP_NAME
198 and snapshot_type =
199 ad_file_sys_snapshots_pkg.get_snapshot_type(CURRENT_VIEW_SNP_NAME);
200 exception
201 when no_data_found then
202 return(NOT_APPLIED);
203 end;
204 end if;
205
206 begin
207 select b.bug_id into l_bug_id
208 from AD_BUGS b
209 where b.bug_number = p_bug_number
210 and b.baseline_name = p_baseline_name
211 and b.aru_release_name = p_release_name
212 and b.language = p_language;
213 exception
214 when no_data_found then
215 return(NOT_APPLIED);
216 end;
217
218
219 begin
220 select bug_status into l_bugst
221 from ad_snapshot_bugfixes
222 where bugfix_id = l_bug_id
223 and snapshot_id = l_snapshot_id;
224 exception
225 when no_data_found then
226 return (NOT_APPLIED);
227 end;
228
229 return l_bugst;
230
231 END is_codeline_patch_applied ;
232
233
234 /*****************************************************************************
235 Compare passed versions of files to determine which is greater,
236 the one requested by caller or the one in database.
237 *****************************************************************************/
238 function compare_versions(p_version in varchar2,
239 p_version_indb in varchar2)
240 return boolean
241 is
242 db_ver number;
243 passed_ver number;
244 passed_len number;
245 db_ver_len number;
246 l_ver_str varchar2(132) ;
247 db_ver_str varchar2(132) ;
248 ret_status boolean := TRUE;
249 begin
250 l_ver_str := p_version||'.';
251 db_ver_str := p_version_indb||'.';
252
253 -- Version format is a.b.c.e...x
254 -- parse out a version from the passed in version (p_version)
255
256 while l_ver_str is not null or db_ver_str is not null loop
257 if (l_ver_str is null)
258 then
259 passed_ver := 0;
260 else
261 passed_ver := nvl(to_number(substr(l_ver_str,1,instr(l_ver_str,'.')-1)),
262 -1);
263 l_ver_str := substr(l_ver_str,instr(l_ver_str,'.')+1);
264 end if;
265
266 -- Next parse out a version from the db_version (p_version_indb)
267
268 if (db_ver_str is null)
269 then
270 db_ver := 0;
271 else
272 db_ver := nvl(to_number(substr(db_ver_str,1,instr(db_ver_str,'.')-1)),
273 -1);
274 db_ver_str := substr(db_ver_str,instr(db_ver_str,'.')+1);
275 end if;
276
277 -- If passed file ver is greater than version in DB, then ret FALSE
278
279 if (passed_ver > db_ver)
280 then
281 ret_status := FALSE;
282 exit; /* get out of the loop, we're done. */
283
284 -- If passed file ver is less than version in DB, then ret TRUE
285
286 elsif (passed_ver < db_ver)
287 then
288 exit; /* get out of the loop, we're done. */
289 end if;
290
291 -- Continue looping only if sub string versions are equal, i.e
292 -- compraing 115.2.1151.2 - 115.42, second time thru the loop should
293 -- exit with a ret_status, since 2 and 42 are not equal.
294
295 end loop;
296
297 return(ret_status);
298
299 end compare_versions;
300
301 /***********************************************************************
302 Find max version of the passed file that was applied to the
303 system. Not just any max version of the passed file in patch hist tables.
304 For is_file_copied, the relevant question is "does the file with a
305 specific version or higher, exists on the file system" ?
306 Not whether it was explicitly copied or not by a patch.
307 And for that, just a file with a exact or higher version in
308 ad_snapshot_files is sufficient.
309 ***********************************************************************/
310
311 function find_max_applied_version_indb(p_file_id in number,
312 p_snapshot_id in number)
313 return varchar2
314 is
315 cursor MAX_VER_CUR(p_file_id in ad_files.file_id%TYPE) is
316 select v.version
317 from ad_file_versions v, ad_snapshot_files s
318 where s.file_id = v.file_id
319 and s.file_id = p_file_id
320 and s.snapshot_id = p_snapshot_id
321 and v.file_version_id = s.file_version_id;
322
323 max_ver ad_file_versions.version%TYPE ;
324 old_ver ad_file_versions.version%TYPE ;
325 ret boolean := FALSE;
326 begin
327 max_ver := '0';
328 old_ver := '0';
329
330 for max_ver_cur_rec in MAX_VER_CUR(p_file_id) loop
331 ret := compare_versions(max_ver_cur_rec.version,old_ver);
332 if (ret = FALSE) then
333 max_ver := max_ver_cur_rec.version;
334 end if;
335 old_ver := max_ver_cur_rec.version;
336 end loop;
337
338 return(max_ver);
339
340 end find_max_applied_version_indb;
341
342
343 /*************************************************************************
344 is_file_copied()
345 This function returns whether a given file with a subdir and version
346 passed, was already applied to an appl_top. The return value is either
350 p_appl_top_id in number,
347 NOT_APPLIED or EXPLICITLY_APPLIED.
348 *************************************************************************/
349 function is_file_copied (p_application_short_name in varchar2,
351 p_object_location in varchar2,
352 p_object_name in varchar2,
353 p_object_version in varchar2)
354 return varchar2
355 is
356 l_file_id ad_files.file_id%TYPE;
357 l_snapshot_id ad_snapshots.snapshot_id%TYPE;
358 l_ver ad_file_versions.version%TYPE;
359 l_dbver ad_file_versions.version%TYPE;
360 max_ver_indb ad_file_versions.version%TYPE;
361 ret boolean := FALSE;
362
363 cursor FILE_VER_CUR(p_file_id in ad_files.file_id%TYPE) is
364 select executed_flag
365 from ad_patch_run_bug_actions
366 where file_id = p_file_id
367 and patch_file_version_id in (select file_version_id
368 from ad_file_versions
369 where file_id = p_file_id
370 and version = max_ver_indb);
371 begin
372
373 l_ver := rtrim(p_object_version);
374
375 -- First get the snapshot_id, from the given appl_top_id to work on.
376 if (p_appl_top_id = -1)
377 then
378 begin
379 select s.snapshot_id into l_snapshot_id
380 from AD_SNAPSHOTS s, AD_APPL_TOPS a,
381 FND_PRODUCT_GROUPS fpg
382 where s.snapshot_name = GLOBAL_VIEW_SNP_NAME
383 and s.snapshot_type =
384 ad_file_sys_snapshots_pkg.get_snapshot_type(GLOBAL_VIEW_SNP_NAME)
385 and s.appl_top_id = a.appl_top_id
386 and a.name = 'GLOBAL'
387 and a.appl_top_type = 'G'
388 and a.applications_system_name = fpg.applications_system_name
389 and fpg.product_group_id=1;
390 exception
391 /*bug 2770858 Do not rollback and do not raise exception*/
392 when no_data_found then
393 return(NOT_APPLIED);
394 end;
395 else
396 begin
397 select snapshot_id into l_snapshot_id
398 from AD_SNAPSHOTS
399 where appl_top_id = p_appl_top_id
400 and snapshot_name = CURRENT_VIEW_SNP_NAME
401 and snapshot_type =
402 ad_file_sys_snapshots_pkg.get_snapshot_type(CURRENT_VIEW_SNP_NAME);
403 exception
404 /*bug 2770858 Do not rollback and do not raise exception*/
405 when no_data_found then
406 return(NOT_APPLIED);
407 end;
408 end if;
409
410 begin
411 select file_id into l_file_id
412 from ad_files
413 where app_short_name = p_application_short_name
414 and subdir = p_object_location
415 and filename = p_object_name;
416 exception
417 when no_data_found then
418 return(NOT_APPLIED);
419 end;
420
421 -- Find the max applied version of the passed file in db.
422 max_ver_indb := find_max_applied_version_indb(l_file_id,l_snapshot_id);
423
424 -- Compare the version of the passed file and the version of the file
425 -- that exists in the database to return if file is copied explicitly or
426 -- not
427
428 ret := compare_versions(l_ver,max_ver_indb);
429
430 if (ret = TRUE) then
431 return(EXPLICITLY_APPLIED);
432 end if;
433
434 -- If you are here means the file with the specific version was not
435 -- applied, and hence return not applied.
436
437 return(NOT_APPLIED);
438
439 end is_file_copied;
440
441
442
443 /*************************************************************************
444 cascade update AD_SNAPSHOT_BUGFIXES based on the values in AD_PATCH_RUN_BUGS.
445 - Don't update AD_SNAPSHOT_BUGFIXES at all if already set to the value
446 being updated.
447 - When updating to successful, just mark row in AD_SNAPSHOT_BUGFIXES
448 as successful.
449 - When updating to unsuccessful, only mark row in AD_SNAPSHOT_BUGFIXES
450 as not successful if there are no rows in AD_PATCH_RUN_BUGS marked as
451 successful for the bug, product, and aru_release.
452 This routine updates AD_SNAPSHOT_BUGFIXES for given a patch_run_id and
453 a flag.
454 *************************************************************************/
455 procedure mark_patch_bug_in_snpbgfix(p_appl_top_id in number,
456 p_patch_run_id in number,
457 p_flag in varchar2)
458 is
459 l_snapshot_id AD_SNAPSHOTS.snapshot_id%type;
460
461 cursor BUG_ID_CUR is
462 select bug_id
463 from AD_PATCH_RUN_BUGS
464 where patch_run_id = p_patch_run_id;
465
466 cursor FIND_SUCC_CUR(p_bug_id in ad_bugs.bug_id%type) is
467 select success_flag
468 from AD_PATCH_RUN_BUGS
469 where patch_run_id <> p_patch_run_id
470 and bug_id = p_bug_id;
471
472 no_update boolean := FALSE;
473
474 begin
475
476 begin
477 select snapshot_id into l_snapshot_id
478 from AD_SNAPSHOTS
479 where appl_top_id = p_appl_top_id
480 and snapshot_name = CURRENT_VIEW_SNP_NAME
481 and snapshot_type =
482 ad_file_sys_snapshots_pkg.get_snapshot_type(CURRENT_VIEW_SNP_NAME);
483 exception
484 when no_data_found then
485 /* bug 2770858 Do not rollback */
486 return;
487 end;
488
489
490 if (p_flag = 'Y') then
491 begin
492 update AD_SNAPSHOT_BUGFIXES
493 set success_flag = p_flag
494 where snapshot_id = l_snapshot_id
495 and bugfix_id in (select bug_id from AD_PATCH_RUN_BUGS
499 raise_application_error(-20001, g_errm ||
496 where patch_run_id = p_patch_run_id);
497 exception when others then
498 g_errm := sqlerrm;
500 'Error occurred in mark_patch_bug_in_snpbgfix() '||
501 'while trying to update success_flag to Y '||
502 ' in AD_SNAPSHOT_BUGFIXES '||
503 'for patch_run_id = ' || p_patch_run_id);
504 end;
505 elsif (p_flag = 'N') then
506 for mybugid in BUG_ID_CUR loop
507 for succ_rec in FIND_SUCC_CUR(mybugid.bug_id) loop
508 if (succ_rec.success_flag = 'Y') then
509 no_update := TRUE;
510 exit;
511 end if;
512 end loop;
513 if (no_update = FALSE) then
514 begin
515 update AD_SNAPSHOT_BUGFIXES
516 set success_flag = p_flag
517 where bugfix_id = mybugid.bug_id
518 and snapshot_id = l_snapshot_id;
519 exception when others then
520 g_errm := sqlerrm;
521 raise_application_error(-20001, g_errm ||
522 'Error occurred in mark_patch_bug_in_snpbgfix() '||
523 'while trying to update success_flag to N '||
524 ' in AD_SNAPSHOT_BUGFIXES '||
525 'for patch_run_id = ' || p_patch_run_id);
526 end;
527 end if;
528 no_update := FALSE;
529 end loop;
530 else
531 raise_application_error(-20000,'Value for success_flag passed "'||
532 p_flag ||'" is invalid');
533 end if;
534 end mark_patch_bug_in_snpbgfix;
535
536 /*************************************************************************
537 cascade update AD_SNAPSHOT_BUGFIXES based on the values in AD_PATCH_RUN_BUGS.
538 - Don't update AD_SNAPSHOT_BUGFIXES at all if already set to the value
539 being updated.
540 - When updating to successful, just mark row in AD_SNAPSHOT_BUGFIXES
541 as successful.
542 - When updating to unsuccessful, only mark row in AD_SNAPSHOT_BUGFIXES
543 as not successful if there are no rows in AD_PATCH_RUN_BUGS marked as
544 successful for the bug, product, and aru_release.
545 This routine updates AD_SNAPSHOT_BUGFIXES, given a patch_run, bug id.
546 *************************************************************************/
547 procedure mark_bug_in_snpbgfix(p_patch_run_id in number,
548 p_appl_top_id in number,
549 p_bug_id in number,
550 p_flag in varchar2)
551 is
552 l_bug_id ad_bugs.bug_id%TYPE;
553 l_snapshot_id AD_SNAPSHOTS.snapshot_id%type;
554
555 cursor FIND_SUCC_CUR(p_bug_id in ad_bugs.bug_id%type) is
556 select success_flag
557 from AD_PATCH_RUN_BUGS
558 where patch_run_id <> p_patch_run_id
559 and bug_id = p_bug_id;
560
561 no_update boolean := FALSE;
562
563 begin
564
565 begin
566 select snapshot_id into l_snapshot_id
567 from AD_SNAPSHOTS
568 where appl_top_id = p_appl_top_id
569 and snapshot_name = CURRENT_VIEW_SNP_NAME
570 and snapshot_type =
571 ad_file_sys_snapshots_pkg.get_snapshot_type(CURRENT_VIEW_SNP_NAME);
572 exception
573 when no_data_found then
574 /* bug 2770858 Do not rollback */
575 return;
576 end;
577
578 if (p_flag = 'Y') then
579 begin
580 update AD_SNAPSHOT_BUGFIXES
581 set success_flag = p_flag
582 where bugfix_id = p_bug_id
583 and snapshot_id = l_snapshot_id;
584 exception when others then
585 g_errm := sqlerrm;
586 raise_application_error(-20001, g_errm ||
587 'Error occurred in mark_bug_in_snpbgfix() '||
588 'while trying to update success_flag to Y '||
589 ' in AD_SNAPSHOT_BUGFIXES '||
590 'for patch_run_id = ' || p_patch_run_id);
591 end;
592 elsif (p_flag = 'N') then
593 for succ_rec in FIND_SUCC_CUR(p_bug_id) loop
594 if (succ_rec.success_flag = 'Y') then
595 no_update := TRUE;
596 exit;
597 end if;
598 end loop;
599 if (no_update = FALSE) then
600 begin
601 update AD_SNAPSHOT_BUGFIXES
602 set success_flag = p_flag
603 where bugfix_id = p_bug_id
604 and snapshot_id = l_snapshot_id;
605 exception when others then
606 g_errm := sqlerrm;
607 raise_application_error(-20001, g_errm ||
608 'Error occurred in mark_bug_in_snpbgfix() '||
609 'while trying to update success_flag to N '||
610 ' in AD_SNAPSHOT_BUGFIXES '||
611 'for patch_run_id = ' || p_patch_run_id);
612 end;
613 end if;
614 no_update := FALSE;
615 else
616 raise_application_error(-20000,'Value for success_flag passed "'||
617 p_flag ||'" is invalid');
618 end if;
619 end mark_bug_in_snpbgfix;
620
621 /*****************************************************************************
622 Mark patch runs as successful or unsuccessful, in a given appl_top_id,
623 cascading to ad_patch_run_bugs and ad_bugs. This procedure will use
624 patch_run_id, which would be selected via another layer of sql report that
625 the user would run to list all patch_run_ids for a given patch_name,
626 appl_top_id and a date.
627 *****************************************************************************/
628
629 procedure mark_patch_succ(p_patch_run_id in NUMBER ,
630 p_appl_top_id in number,
631 p_release_name in varchar2,
635
632 p_flag in varchar2,
633 p_reason_text in varchar2)
634 is
636 begin
637
638 begin
639 update AD_PATCH_RUNS
640 set success_flag = p_flag, failure_comments = p_reason_text
641 where patch_run_id = p_patch_run_id;
642 exception when others then
643 g_errm := sqlerrm;
644 raise_application_error(-20001, g_errm ||
645 'Error occurred in mark_patch_succ() '||
646 'while trying to update success_flag '||
647 'in AD_PATCH_RUNS '||
648 'for patch_run_id = ' || p_patch_run_id);
649 end;
650
651 begin
652 update AD_PATCH_RUN_BUGS
653 set success_flag = p_flag, failure_comments = p_reason_text
654 where patch_run_id = p_patch_run_id;
655 exception when others then
656 g_errm := sqlerrm;
657 raise_application_error(-20001, g_errm ||
658 'Error occurred in mark_patch_succ() '||
659 'while trying to update success_flag '||
660 'in AD_PATCH_RUN_BUGS '||
661 'for patch_run_id = ' || p_patch_run_id);
662 end;
663
664 mark_patch_bug_in_snpbgfix(p_appl_top_id,p_patch_run_id,p_flag);
665
666 end mark_patch_succ;
667
668
669 /*************************************************************************
670 are_all_bugs_for_patch_succ(p_patch_run_id);
671 Go thru all the bug_id's for the given patch_run_id in ad_patch_run_bugs
672 to get all the bugs for a given patch and check if all the bugs have
673 success_flag set to 'Y' or 'N' and return true or false accordingly.
674 *************************************************************************/
675 function are_all_bugs_for_patch_succ(p_patch_run_id in
676 ad_patch_runs.patch_run_id%TYPE)
677 return boolean
678 is
679 l_flag varchar2(1);
680 begin
681
682 begin
683 select success_flag into l_flag
684 from AD_PATCH_RUN_BUGS
685 where patch_run_id = p_patch_run_id
686 and success_flag = 'N';
687 exception
688 when no_data_found then
689 return(TRUE);
690 end;
691
692 -- If you are here means one or more records had success_flag = 'N' and
693 -- hence need to return a FALSE.
694
695 return(FALSE);
696
697 end are_all_bugs_for_patch_succ;
698
699 /*************************************************************************
700 Given a patch_run_id and a bug number, mark the bug as successful or
701 unsuccessful and cascade to ad_patch_runs and ad_bugs in the following
702 manner:
703 - update AD_PATCH_RUN_BUGS for the specified patch run and bug
704 - Also update AD_PATCH_RUNS in the following cases:
705 - if updating bug to successful and all other bugs for this patch run
706 are successful and patch run marked as unsuccessful, update patch run
707 to successful.
708 - if updating bug to unsuccessful and patch run marked
709 as successful, update patch run to unsuccessful.
710 - cascade update AD_BUGS as described above based on values in
711 AD_PATCH_RUN_BUGS.
712 *************************************************************************/
713
714 procedure mark_bug_succ(p_patch_run_id in NUMBER ,
715 p_appl_top_id in number,
716 p_release_name in varchar2,
717 p_bug_number in varchar2,
718 p_flag in varchar2,
719 p_reason_text in varchar2)
720 is
721 l_bug_id ad_bugs.bug_id%TYPE;
722 l_patch_succ ad_snapshot_bugfixes.success_flag%TYPE;
723
724 begin
725
726 -- get bug id from ad_bugs for the passed bug number and aru_release_name.
727
728 begin
729 select bug_id into l_bug_id
730 from AD_BUGS
731 where bug_number = p_bug_number
732 and aru_release_name = p_release_name;
733 exception
734 when no_data_found then
735 raise_application_error(-20000,'Bug number '||p_bug_number||
736 ' does not exist'||' in patch history tables!!');
737 return;
738 when too_many_rows then
739 raise_application_error(-20001,
740 'Too many rows returned for bug '|| p_bug_number);
741 return;
742 end;
743
744
745 /* Case I:
746 update ad_patch_runs for the specified patch run and bug.
747 */
748
749 begin
750 update AD_PATCH_RUN_BUGS
751 set success_flag = p_flag, failure_comments = p_reason_text
752 where patch_run_id = p_patch_run_id and
753 bug_id = l_bug_id;
754 exception when others then
755 g_errm := sqlerrm;
756 raise_application_error(-20001, g_errm ||
757 'Error occurred in mark_bug_succ() '||
758 'while trying to update case I success_flag '||
759 'in AD_PATCH_RUNS '||
760 'for patch_run_id = ' || p_patch_run_id);
761 end;
762
763 /* Case II:
764 update ad_patch_runs in the following manner.
765 1.if updating bug to successful and all other bugs for this patch run
766 are successful and patch run marked as unsuccessful, update patch run
767 to successful.
768 */
769
770 begin
771 select success_flag into l_patch_succ
772 from AD_PATCH_RUNS
773 where patch_run_id = p_patch_run_id;
774 exception
775 when no_data_found then
776 /* bug 2770858 Do not rollback */
777 return;
778 end;
779
780 if (p_flag = 'Y' and
784 begin
781 are_all_bugs_for_patch_succ(p_patch_run_id) = TRUE and
782 l_patch_succ = 'N') then
783
785 update AD_PATCH_RUNS
786 set success_flag = p_flag, failure_comments = p_reason_text
787 where patch_run_id = p_patch_run_id;
788 exception when others then
789 g_errm := sqlerrm;
790 raise_application_error(-20001, g_errm ||
791 'Error occurred in mark_bug_succ() '||
792 'while trying to update case II success_flag '||
793 'in AD_PATCH_RUNS '||
794 'for patch_run_id = ' || p_patch_run_id);
795 end;
796
797 -- case III. if updating bug to unsuccessful and patch run marked
798 -- as successful, update patch run to unsuccessful.
799
800 elsif (p_flag = 'N' and l_patch_succ = 'Y') then
801
802 begin
803 update AD_PATCH_RUNS
804 set success_flag = p_flag, failure_comments = p_reason_text
805 where patch_run_id = p_patch_run_id;
806 exception when others then
807 g_errm := sqlerrm;
808 raise_application_error(-20001, g_errm ||
809 'Error occurred in mark_bug_succ() '||
810 'while trying to update case III success_flag '||
811 'in AD_PATCH_RUNS '||
812 'for patch_run_id = ' || p_patch_run_id);
813 end;
814
815 end if;
816
817 -- Case IV:
818 -- cascade update AD_SNAPSHOT_BUGFIXES just like in mark_patch_succ
819 -- above based on values in AD_PATCH_RUN_BUGS.
820
821 mark_bug_in_snpbgfix(p_patch_run_id,p_appl_top_id,l_bug_id,p_flag);
822
823 end mark_bug_succ;
824
825
826 /*******************************************************************************
827 - set patch application status.
828 - arguments include:
829 - aru_release_name (e.g., 11i)
830 - bug_number (eg., '1234567')
831 - application_status, the patch application status (e.g.,IMPLICITLY_APPLIED)
832 - appl_top_id, to know which appl_top, user is requesting patch status for.
833
834 2/15/02 : app_short_name is moved from AD_BUGS to AD_PATCH_RUN_BUGS
835 due to AOL's requirement, since AOL doesn't always know
836 app_short_name to pass.
837 *******************************************************************************/
838 procedure set_patch_status(p_release_name in varchar2,
839 p_appl_top_id number,
840 p_bug_number in varchar2,
841 p_bug_status in varchar2)
842 is
843 l_bug_id ad_bugs.bug_id%TYPE;
844 l_snapshot_id ad_snapshots.snapshot_id%TYPE;
845 l_snapshot_bug_id ad_snapshot_bugfixes.snapshot_bug_id%TYPE;
846
847 begin
848
849 -- CASE I: On create, when there are no entries in AD_BUGS for the
850 -- passed bug_number, aru_release_name. create a row in AD_BUGS and
851 -- AD_SNAPSHOT_BUGFIXES.
852
853 -- First get the snapshot_id, from the given appl_top_id to work on.
854
855 if (p_appl_top_id = -1)
856 then
857 begin
858 select s.snapshot_id into l_snapshot_id
859 from AD_SNAPSHOTS s, AD_APPL_TOPS a,
860 FND_PRODUCT_GROUPS fpg
861 where s.snapshot_name = GLOBAL_VIEW_SNP_NAME
862 and s.snapshot_type =
863 ad_file_sys_snapshots_pkg.get_snapshot_type(GLOBAL_VIEW_SNP_NAME)
864 and s.appl_top_id = a.appl_top_id
865 and a.name = 'GLOBAL'
866 and a.appl_top_type = 'G'
867 and a.applications_system_name = fpg.applications_system_name
868 and fpg.product_group_id=1;
869 exception
870 /* bug 2770858 Do not rollback */
871 when no_data_found then
872 return;
873 end;
874 else
875 begin
876 select snapshot_id into l_snapshot_id
877 from AD_SNAPSHOTS
878 where appl_top_id = p_appl_top_id
879 and snapshot_name = CURRENT_VIEW_SNP_NAME
880 and snapshot_type =
881 ad_file_sys_snapshots_pkg.get_snapshot_type(CURRENT_VIEW_SNP_NAME);
882 exception
883 when no_data_found then
884 /* bug 2770858 Do not rollback */
885 return;
886 end;
887 end if;
888
889 begin
890 select bug_id into l_bug_id
891 from AD_BUGS
892 where bug_number = p_bug_number
893 and aru_release_name = p_release_name;
894 exception
895 when no_data_found then
896
897 -- Insert a new record
898
899 select ad_bugs_s.nextval into l_bug_id from dual;
900
901 begin
902 insert into AD_BUGS
903 (bug_id, bug_number,
904 creation_date, aru_release_name,
905 last_update_date, last_updated_by,created_by)
906 values(l_bug_id,
907 p_bug_number,
908 sysdate,
909 p_release_name,
910 sysdate,
911 -1,
912 -1);
913 exception
914 when dup_val_on_index then
915 raise_application_error(-20001,
916 'Attempting to insert a duplicate record '||
917 'into AD_BUGS for bug_number = '||
918 p_bug_number || ' and release '||
919 p_release_name);
920
921 when others then
922 g_errm := sqlerrm;
923 raise_application_error(-20001, g_errm ||
924 'Error occurred in set_patch_status() '||
925 'while trying to insert new record '||
929 end;
926 'into AD_BUGS for bug_number = '||
927 p_bug_number || ' and release '||
928 p_release_name);
930 end;
931
932 begin
933 select snapshot_bug_id into l_snapshot_bug_id
934 from AD_SNAPSHOT_BUGFIXES
935 where bugfix_id = l_bug_id
936 and snapshot_id = l_snapshot_id;
937 exception
938 when no_data_found then
939 -- create an entry in AD_SNAPSHOT_BUGFIXES
940 begin
941 insert into AD_SNAPSHOT_BUGFIXES
942 (snapshot_bug_id,
943 snapshot_id,
944 bugfix_id,
945 bug_status,
946 success_flag,
947 creation_date,
948 last_update_date,
949 last_updated_by,
950 created_by)
951 values (ad_snapshot_bugfixes_s.nextval,
952 l_snapshot_id,
953 l_bug_id,
954 p_bug_status,
955 'Y',
956 sysdate,
957 sysdate,
958 -1,
959 -1);
960 exception
961 when dup_val_on_index then
962 raise_application_error(-20001,
963 'Attempting to insert a duplicate record '||
964 'into AD_SNAPSHOT_BUGFIXES for bug_number = '||
965 p_bug_number || ' and release '||
966 p_release_name);
967
968 when others then
969 g_errm := sqlerrm;
970 raise_application_error(-20001, g_errm ||
971 'Error occurred in set_patch_status() '||
972 'while trying to insert new record '||
973 'into AD_SNAPSHOT_BUGFIXES for bug_number = '||
974 p_bug_number || ' and release '||
975 p_release_name);
976 end;
977
978 when too_many_rows then
979 /* bug 2770858 Do not rollback */
980 raise_application_error(-20001,
981 'Too many rows in AD_SNAPSHOT_BUGFIXES '||
982 'table for '||p_bug_number||
983 ' '||p_release_name);
984 end;
985
986 -- If you are here means there was a record in AD_BUGS and
987 -- AD_SNAPSHOT_BUGFIXES.
988 -- So just update the existing record.
989
990 begin
991 update AD_SNAPSHOT_BUGFIXES
992 set BUG_STATUS = p_bug_status, success_flag = 'Y', last_updated_by = -1,
993 last_update_date = sysdate
994 where bugfix_id = l_bug_id and
995 snapshot_id = l_snapshot_id;
996 exception when others then
997 g_errm := sqlerrm;
998 raise_application_error(-20001, g_errm ||
999 'Error occurred in set_patch_status() '||
1000 'while trying to update bug_status '||
1001 'in AD_SNAPSHOT_BUGFIXES '||
1002 'for bug_id = ' || l_bug_id);
1003 end;
1004
1005 end set_patch_status;
1006
1007 /*******************************************************************************
1008 getAppltopID()
1009
1010 This function returns an appl_top_id to the caller given the appl_top name,
1011 and optionally an Applications System name and/or APPL_TOP type.
1012 1. If APPL_TOP type not specified, it defaults to a normal APPL_TOP
1013 i.e 'R'
1014 2. If Applications Sytem name not specified, it defaults to the value in
1015 FND_PRODUCT_GROUPS.
1016
1017 *******************************************************************************/
1018
1019 function getAppltopID(p_appl_top_name in varchar2,
1020 p_app_sys_name in varchar2,
1021 p_appl_top_type in varchar2)
1022 return number
1023 is
1024 l_app_sys_name ad_appl_tops.applications_system_name%TYPE;
1025 l_appl_top_type ad_appl_tops.appl_top_type%TYPE;
1026 l_appl_top_id ad_appl_tops.appl_top_id%TYPE;
1027 begin
1028
1029 select decode(upper(p_appl_top_type),'','R',
1030 upper(p_appl_top_type))
1031 into l_appl_top_type
1032 from dual;
1033
1034 if ((upper(p_app_sys_name)) is null) then
1035 select applications_system_name into l_app_sys_name
1036 from FND_PRODUCT_GROUPS;
1037 else
1038 l_app_sys_name := p_app_sys_name;
1039 end if;
1040
1041 begin
1042 select appl_top_id into l_appl_top_id
1043 from AD_APPL_TOPS
1044 where name = p_appl_top_name
1045 and appl_top_type = l_appl_top_type
1046 and applications_system_name = l_app_sys_name;
1047 exception
1048 when no_data_found then
1049 raise_application_error(-20001,
1050 'No rows in AD_APPL_TOPS table for appl_top_name '||
1051 '''' || p_appl_top_name || ''''||
1052 'and applications system name '||
1053 '''' || l_app_sys_name || ''''||
1054 ' and appl_top type '||
1055 '''' || l_appl_top_type || '''');
1056 return(0);
1057 when too_many_rows then
1058 raise_application_error(-20001,
1059 'Too many rows in AD_APPL_TOPS table for appl_top_name '||
1060 '''' || p_appl_top_name || ''''||
1061 'and applications system name '||
1062 '''' || l_app_sys_name || '''');
1063 return(0);
1064 end;
1065
1066 return(l_appl_top_id);
1067
1068 end getAppltopID;
1069
1070 end ad_patch;
[Home] [Help]