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