[Home] [Help]
PACKAGE BODY: APPS.AD_FILE_SYS_SNAPSHOTS_PKG
Source
1 package body ad_file_sys_snapshots_pkg as
2 /* $Header: adfssnpb.pls 120.3.12020000.3 2012/07/10 03:16:02 mkumandu ship $ */
3
4
5 --
6 -- Private types
7 --
8 type NUM_TAB is table of number;
9 type VC1_TAB is table of varchar2(1);
10
11 --
12 -- Private program units
13 --
14
15 -- APPLSYS schema name
16
17 G_UN_FND varchar2(30) := null;
18
19 -- ACTION code for internal use only
20
21 G_PR_ID_ACT_CD2 constant number := 200;
22
23 g_current_snapshot_type varchar2(1);
24 g_global_snapshot_type varchar2(1);
25
26 --
27 --
28 -- Debug utils START
29 --
30 G_DEBUG constant boolean := FALSE; --%%set to FALSE in production code
31
32 procedure put_line
33 (msg varchar2, len number default 80)
34 is
35 n number := 1;
36 nmax number;
37 begin
38 nmax := nvl(length(msg), 0);
39 if not G_DEBUG then
40 return;
41 end if;
42
43 loop
44 -- dbms_output.put_line(substr(msg, n, len)); --%%comment out in prodn code
45 n := n + len;
46 exit when n > nmax;
47 end loop;
48 end put_line;
49 --
50 -- Debug utils END
51 --
52 --
53 function get_snapshot_type (p_snapshot_name in varchar2)
54 return varchar2
55 is
56 flag boolean;
57 begin
58 if (ad_zd.is_editions_enabled = 'N' or
59 sys_context('userenv', 'current_edition_name') = ad_zd.get_edition('RUN'))
60 then
61 g_current_snapshot_type := 'C';
62 g_global_snapshot_type := 'G';
63 else
64 g_current_snapshot_type := 'P';
65 g_global_snapshot_type := 'Q';
66 end if;
67
68 if (p_snapshot_name = 'CURRENT_VIEW')
69 then
70 return g_current_snapshot_type;
71 elsif (p_snapshot_name = 'GLOBAL_VIEW')
72 then
73 return g_global_snapshot_type;
74 else
75 raise_application_error(-20001, 'Invalid Snapshot Name <'
76 ||p_snapshot_name||'>');
77 end if;
78 end get_snapshot_type;
79
80 procedure handle_bugs
81 (p_action_code varchar2,
82 p_update_global_view boolean,
83 p_snapshot_id number,
84 p_global_snapshot_id number,
85 p_delete_junk_created boolean)
86 is
87 L_BUGSTAT_EXPL_ACT_CD constant number := 15; -- bug fix row: EXPLICIT status
88 L_ADPBNAAS constant varchar2(20) := 'No active actions';
89
90 l_snapshot_bugs_inserted number := 0;
91 l_gathered_stats_flag boolean := FALSE;
92 l_snapshot_count number;
93 l_global_snapshot_count number;
94 l_global_snapshot_id number;
95 begin
96 -- Gather stats on the temp table at the start (it may have stats from its
97 -- prior use. For that, first commit.
98
99 commit;
100
101 fnd_stats.gather_table_stats(G_UN_FND, 'ad_patch_hist_snaps_temp');
102
103 if G_DEBUG then
104 put_line('Inserting candidate bugs into temp table');
105 end if;
106
107 --bug3537094. The following insert should be used instead
108 --Also removed 2 delete stmt which deletes unknown and duplicate
109 --rows from ad_patch_hist_snaps_temp
110
111 insert into ad_patch_hist_snaps_temp (action_code, bug_id)
112 select distinct L_BUGSTAT_EXPL_ACT_CD, prb.bug_id
113 from ad_patch_run_bugs prb, ad_patch_hist_snaps_temp t
114 where prb.patch_run_id = t.patch_run_id
115 and t.action_code = p_action_code
116 and (prb.applied_flag = 'Y' or prb.reason_not_applied = L_ADPBNAAS);
117
118 if G_DEBUG then
119 put_line('Inserted '||to_char(sql%rowcount)||
120 ' candidate bugs into temp table');
121 end if;
122
123 commit;
124
125 -- Now gather stats.
126
127 if G_DEBUG then
128 put_line('Gathering stats');
129 end if;
130
131 FND_STATS.Gather_Table_Stats(G_UN_FND, 'ad_patch_hist_snaps_temp');
132
133 if G_DEBUG then
134 put_line('Inserting new bugfixes in the curr-vw snapshot');
135 end if;
136
137 -- Insert new ones
138 insert into ad_snapshot_bugfixes
139 (
140 snapshot_bug_id,
141 snapshot_id,
142 bugfix_id,
143 bug_status,
144 success_flag,
145 creation_date, last_update_date, last_updated_by, created_by
146 )
147 select
148 ad_snapshot_bugfixes_s.nextval,
149 p_snapshot_id,
150 t.bug_id,
151 'EXPLICIT',
152 'Y',
153 sysdate, sysdate, 5, 5
154 from ad_patch_hist_snaps_temp t
155 where t.action_code = L_BUGSTAT_EXPL_ACT_CD
156 and not exists (select 'Bug not yet recorded in the curr-vw'
157 from ad_snapshot_bugfixes sb2
158 where sb2.snapshot_id = p_snapshot_id
159 and sb2.bugfix_id = t.bug_id);
160
161 l_snapshot_bugs_inserted := sql%rowcount;
162
163 if G_DEBUG then
164 put_line('Inserted '||to_char(l_snapshot_bugs_inserted)||
165 ' new bugfixes in the CV');
166 end if;
167
168 commit;
169
170 if G_DEBUG then
171 put_line('Updating existing bugfix statuses in the curr-vw snapshot');
172 end if;
173
174 -- update the status of existing ones, if they dont already say EXPLICIT, Y
175
176 update ad_snapshot_bugfixes sb
177 set sb.bug_status = 'EXPLICIT',
178 sb.success_flag = 'Y',
179 sb.last_update_date = sysdate,
180 sb.last_updated_by = 5
181 where sb.snapshot_id = p_snapshot_id
182 and sb.bugfix_id in (select t.bug_id
183 from ad_patch_hist_snaps_temp t
184 where t.action_code = L_BUGSTAT_EXPL_ACT_CD)
185 and (sb.bug_status <> 'EXPLICIT' or
186 sb.success_flag <> 'Y');
187
188 if G_DEBUG then
189 put_line('Updated '||to_char(sql%rowcount)|| ' bugfix rows in the CV');
190
191 end if;
192
193 commit;
194
195 -- GLOBAL_VIEW snapshot processing
196
197 if p_update_global_view then
198
199 if G_DEBUG then
200 put_line('Inserting new bugfixes in the GV');
201 end if;
202
203 -- Insert new ones
204 insert into ad_snapshot_bugfixes
205 (
206 snapshot_bug_id,
207 snapshot_id, bugfix_id,
208 bug_status, success_flag,
209 creation_date, last_update_date, last_updated_by, created_by
210 )
211 select
212 ad_snapshot_bugfixes_s.nextval,
213 p_global_snapshot_id, t.bug_id,
214 'EXPLICIT', 'Y',
215 sysdate, sysdate, 5, 5
216 from ad_patch_hist_snaps_temp t
217 where t.action_code = L_BUGSTAT_EXPL_ACT_CD
218 and not exists (select 'Bug not yet recorded in the curr-vw'
219 from ad_snapshot_bugfixes sb2
220 where sb2.snapshot_id = p_global_snapshot_id
221 and sb2.bugfix_id = t.bug_id);
222
223 l_snapshot_bugs_inserted := l_snapshot_bugs_inserted + sql%rowcount;
224
225 if G_DEBUG then
226 put_line('Inserted '||to_char(sql%rowcount)|| ' new bugfixes in the GV');
227 end if;
228
229 commit;
230
231 if G_DEBUG then
232 put_line('Updating existing bugfix statuses in the GV');
233 end if;
234
235 -- update the status of existing ones, if they dont already say EXPLICIT, Y
236
237 update ad_snapshot_bugfixes sb
238 set sb.bug_status = 'EXPLICIT',
239 sb.success_flag = 'Y',
240 sb.last_update_date = sysdate,
241 sb.last_updated_by = 5,
242 sb.inconsistent_flag = null
243 where sb.snapshot_id = p_global_snapshot_id
244 and sb.bugfix_id in (select t.bug_id
245 from ad_patch_hist_snaps_temp t
246 where t.action_code = L_BUGSTAT_EXPL_ACT_CD)
247 and (sb.bug_status <> 'EXPLICIT' or
248 sb.success_flag <> 'Y');
249
250 if G_DEBUG then
251 put_line('Updated '||to_char(sql%rowcount)|| ' bugfix rows in the GV');
252 end if;
253
254 commit;
255
256 end if; -- End If p_update_global_view
257
258
259 -- gather stats if necessary
260
261 ad_stats_util_pkg.gather_stats_if_necessary('SNAPSHOT',
262 l_snapshot_bugs_inserted,
263 TRUE,
264 TRUE,
265 l_gathered_stats_flag);
266
267
268 if p_delete_junk_created then
269
270 delete from ad_patch_hist_snaps_temp
271 where action_code = L_BUGSTAT_EXPL_ACT_CD;
272
273 end if;
274
275 --Bug 7255366 nissubra---
276 update ad_snapshots set snapshot_update_date = sysdate
277 where snapshot_id in (p_snapshot_id, p_global_snapshot_id);
278
279 commit;
280
281 end handle_bugs;
282
283
284 procedure backfill_bugs_from_patch_hist
285 (p_snapshot_id number)
286 is
287 l_appl_top_id number;
288 l_apps_sys_nm varchar2(30);
289 l_at_name varchar2(50);
290 l_dummy varchar2(1);
291 begin
292 if G_DEBUG then
293 put_line('just entered backfill_bugs_from_patch_hist');
294 end if;
295
296 select s.appl_top_id, aat.applications_system_name, aat.name
297 into l_appl_top_id, l_apps_sys_nm, l_at_name
298 from ad_snapshots s, ad_appl_tops aat
299 where s.appl_top_id = aat.appl_top_id
300 and s.snapshot_id = p_snapshot_id;
301
302 begin
303 select 'x'
304 into l_dummy
305 from ad_timestamps
306 where type = 'BACKFILLED_BUGS_IN_CURRENT_VIEW_SNAPSHOT'
307 and attribute = l_apps_sys_nm||'*'||l_at_name;
308
309 return;
310
311 exception when no_data_found then
312
313 -- insert ALL patch-runs in this appltop into temp table that dont
314 -- already doesnt exist. While inserting, insert with special action
315 -- code G_PR_ID_ACT_CD2 (so that we can delete them later on)
316
317 if G_DEBUG then
318 put_line('About to insert PR-IDs into temp table - 2');
319 end if;
320
321 insert into ad_patch_hist_snaps_temp
322 (
323 patch_run_id, action_code
324 )
325 select
326 patch_run_id, G_PR_ID_ACT_CD2
327 from ad_patch_runs
328 where appl_top_id = l_appl_top_id
329 and patch_run_id not in (select patch_run_id
330 from ad_patch_hist_snaps_temp
331 where action_code in (G_PR_ID_ACT_CD,
332 G_PR_ID_ACT_CD2));
333
334 if G_DEBUG then
335 put_line('Inserted '||to_char(sql%rowcount)||' PR-ID rows - 2');
336 end if;
337
338
339 -- Backfill bugs ONLY if above INSERT inserted anything
340
341 if sql%rowcount > 0 then
342
343 if G_DEBUG then
344 put_line('calling handle_bugs - 2');
345 end if;
346
347 -- Backfill the bugs. Do it only if processing an actual patch-run,
348 -- not when backfilling from prior patch-hist. Hence we pass in
349 -- p_update_global_view as FALSE. Reason: Backfilling bugs into GV
350 -- has already been taken care of when instantiating the GV (albeit
351 -- indirectly, viz. by creating it from constituent CV's which must
352 -- have already been backfilled).
353
354 handle_bugs(p_snapshot_id => p_snapshot_id,
355 p_action_code => G_PR_ID_ACT_CD2,
356 p_update_global_view => FALSE,
357 p_global_snapshot_id => -1,
358 p_delete_junk_created => TRUE);
359
360 -- Now delete all G_PR_ID_ACT_CD2 rows from temp table
361
362 delete from ad_patch_hist_snaps_temp
363 where action_code = G_PR_ID_ACT_CD2;
364
365 end if;
366
367 -- mark as having done this backfill
368
369 ad_file_util.update_timestamp('BACKFILLED_BUGS_IN_CURRENT_VIEW_SNAPSHOT',
370 l_apps_sys_nm||'*'||l_at_name);
371
372 commit;
373 end;
374
375 end backfill_bugs_from_patch_hist;
376
377
378 -- Creates just the top level info (ad_appl_tops and ad_snapshots)
379 procedure get_create_global_view_header
380 (
381 p_apps_system_name varchar2,
382 p_is_run_flow boolean,
383 p_global_appl_top_id out nocopy number,
384 p_global_snapshot_id out nocopy number,
385 p_count_appltops out nocopy number
386 ) is
387 l_release_id number;
388 l_snapshot_type varchar2(1);
389 begin
390
391 if (p_is_run_flow = TRUE)
392 then
393 l_snapshot_type := 'G';
394 else
395 l_snapshot_type := 'Q';
396 end if;
397
398 select nvl(count(*), 0)
399 into p_count_appltops
400 from ad_appl_tops
401 where applications_system_name = p_apps_system_name
402 and active_flag = 'Y';
403
404 /* Create a dummy appl_top called 'GLOBAL' (inactive) */
405
406 insert into ad_appl_tops
407 (
408 appl_top_id, name, applications_system_name, appl_top_type,
409 description,
410 server_type_admin_flag, server_type_forms_flag,
411 server_type_node_flag, server_type_web_flag,
412 creation_date, created_by, last_update_date, last_updated_by,
413 active_flag
414 )
415 select
416 ad_appl_tops_s.nextval, 'GLOBAL', p_apps_system_name, 'G',
417 'Created for Global View Snapshot',
418 null, null,
419 null, null,
420 sysdate, 5, sysdate, 5,
421 'N'
422 from dual
423 where not exists (select 'Already exists'
427 and t.applications_system_name = p_apps_system_name);
424 from ad_appl_tops t
425 where t.name = 'GLOBAL'
426 and t.appl_top_type ='G'
428
429 /* Get ID of above GLOBAL appl_top */
430
431 select appl_top_id
432 into p_global_appl_top_id
433 from ad_appl_tops
434 where appl_top_type ='G'
435 and name = 'GLOBAL'
436 and applications_system_name = p_apps_system_name;
437
438 /* Get the release id */
439 select release_id into l_release_id from ad_releases
440 where to_char(major_version) || '.' ||
441 to_char(minor_version) || '.' ||
442 to_char(tape_version) = (select release_name
443 from fnd_product_groups
444 where applications_system_name =
445 p_apps_system_name);
446 insert into ad_snapshots
447 (
448 snapshot_id, release_id,
449 appl_top_id, snapshot_name,
450 snapshot_creation_date, snapshot_update_date,
451 snapshot_type, comments,
452 ran_snapshot_flag,
453 creation_date, last_updated_by, created_by, last_update_date
454 )
455 select
456 ad_snapshots_s.nextval, l_release_id,
457 p_global_appl_top_id, 'GLOBAL_VIEW',
458 sysdate, sysdate,
459 l_snapshot_type, 'Created from Current View Snapshots',
460 'N',
461 sysdate, 5, 5, sysdate
462 from dual
463 where not exists (select 'Already exists'
464 from ad_snapshots
465 where appl_top_id = p_global_appl_top_id
466 and snapshot_type = l_snapshot_type
467 and snapshot_name = 'GLOBAL_VIEW');
468
469 /* Get above created Global snapshot ID */
470
471 select snapshot_id
472 into p_global_snapshot_id
473 from ad_snapshots
474 where snapshot_type = l_snapshot_type
475 and snapshot_name = 'GLOBAL_VIEW'
476 and appl_top_id = p_global_appl_top_id;
477
478 end get_create_global_view_header;
479
480
481 -- procedure get_max_fil_vers_over_appltops : Identify highest file versions
482 -- across APPL_TOPs, and inserts them into a temp table.
483 procedure get_max_fil_vers_over_appltops
484 (
485 p_apps_system_name varchar2,
486 p_limit_to_candidate_files boolean,
487 p_wipe_out_temp_table_at_start boolean,
488 p_commit boolean,
489 p_gather_stats boolean,
490 p_is_run_flow boolean
491 )
492 is
493 l_cv_ids NUM_TAB; -- curr-vw snapshot-id's
494 l_cv_ids_str varchar2(4000); -- comma-separated CV-ID's
495
496 l_str1 varchar2(1024) := null;
497 l_str2 varchar2(256) := null;
498 l_str3 varchar2(512) := null;
499
500 L_ARCH_NONE_ACT_CD constant number := 10;
501 L_ARCH_CLIB_ACT_CD constant number := 11;
502 L_ARCH_AZIP_ACT_CD constant number := 12;
503 i number;
504 l_snapshot_type varchar2(1);
505
506 begin
507
508 if p_wipe_out_temp_table_at_start then
509 execute immediate
510 'truncate table ' || G_UN_FND || '.ad_snapshot_files_temp';
511 end if;
512
513 if (p_is_run_flow = TRUE)
514 then
515 l_snapshot_type := 'C';
516 else
517 l_snapshot_type := 'P';
518 end if;
519
520 select snapshot_id
521 bulk collect into l_cv_ids
522 from ad_snapshots s, ad_appl_tops at1
523 where s.appl_top_id = at1.appl_top_id
524 and at1.applications_system_name = p_apps_system_name
525 and at1.appl_top_type = 'R'
526 and at1.active_flag = 'Y'
527 and s.snapshot_type = l_snapshot_type
528 and s.snapshot_name = 'CURRENT_VIEW';
529
530 l_cv_ids_str := null;
531
532 -- Bug 4207329 : The condition must be if count >=1 and not
533 -- if count > 1. Otherwise, if there is only one appltop and
534 -- only one current_view for that appl_top, the global_view
535 -- for that appl_top will never be updated through adpatch!.
536 --
537 -- sgadag - 02-MAR-2005
538
539
540 if l_cv_ids.count >= 1 then
541
542 -- Build a string of CV-IDs
543
544 for i in l_cv_ids.first .. l_cv_ids.last loop
545 if l_cv_ids_str is null then
546 l_cv_ids_str := to_char(l_cv_ids(i)); -- Dont prepend "," on 1st iter.
547 else
548 -- @@TODO: Add safeguard to ensure that l_cv_ids_str length is not
549 -- exceeded (very unlikely, but need a check anyway). Err msg should
550 -- say something like "Developer error: ..."
551
552 l_cv_ids_str := l_cv_ids_str || ', ' || to_char(l_cv_ids(i));
553 end if;
554 end loop;
555
556 end if; -- End If l_cv_ids.count >= 1
557
558 if l_cv_ids_str is null then
559
560 return; -- No curr-vws. Just return
561
562 end if;
563
564
565 l_str1 :=
566 'insert into ad_snapshot_files_temp '||
567 '( '||
568 'snapshot_file_id, file_id, update_source_id, update_type, '||
569 'inconsistent_flag, containing_file_id, file_version_id, '||
570 'dest_file_id, file_type_flag '||
571 ') '||
572 'select '||
573 'snapshot_file_id, file_id, -1, ''P'', ''Y'', containing_file_id, '||
574 'file_version_id, '||
575 'dest_file_id, file_type_flag '||
576 'from (select '||
577 'sf.snapshot_file_id snapshot_file_id, sf.file_id file_id, '||
578 'sf.containing_file_id containing_file_id, '||
579 'sf.file_version_id file_version_id, '||
580 'sf.dest_file_id dest_file_id, sf.file_type_flag file_type_flag, '||
581 'row_number() over '||
585 'afv.version_segment5 desc, afv.version_segment6 desc, '||
582 '(partition by afv.file_id, sf.containing_file_id '||
583 'order by afv.version_segment1 desc, afv.version_segment2 desc, '||
584 'afv.version_segment3 desc, afv.version_segment4 desc, '||
586 'afv.version_segment7 desc, afv.version_segment8 desc, '||
587 'afv.version_segment9 desc, afv.version_segment10 desc, '||
588 'afv.translation_level desc nulls last) as r '||
589 'from ad_snapshot_files sf, ad_file_versions afv ';
590
591 if p_limit_to_candidate_files then
592 l_str2 :=
593 ', ad_patch_hist_snaps_temp t ' ||
594 'where t.file_id = sf.file_id '||
595 'and t.action_code in (:1, :2, :3) and ';
596 else
597 l_str2 := ' where ';
598 end if;
599
600 l_str3 :=
601 'sf.snapshot_id in (' || l_cv_ids_str || ')' ||
602 'and sf.file_version_id = afv.file_version_id (+) ' ||
603 ') r_sf ' ||
604 'where r_sf.r = 1';
605
606 if G_DEBUG then
607
608 put_line('@@INDIV VC2 VARS');
609
610 put_line('l_str1 (len='||to_char(length(l_str1))||'): ');
611 put_line(l_str1);
612
613 put_line('l_str2 (len='||to_char(length(l_str2))||'): ');
614 put_line(l_str2);
615
616 put_line('l_str3 (len='||to_char(length(l_str3))||'): ');
617 put_line(l_str3);
618
619 end if;
620
621 if p_limit_to_candidate_files then
622
623 execute immediate l_str1 || l_str2 || l_str3 using
624 L_ARCH_NONE_ACT_CD, L_ARCH_CLIB_ACT_CD, L_ARCH_AZIP_ACT_CD;
625
626 else
627
628 execute immediate l_str1 || l_str2 || l_str3;
629
630 end if;
631
632 if p_commit then
633 commit;
634 end if;
635
636 if p_gather_stats then
637 fnd_stats.gather_table_stats(G_UN_FND, 'ad_snapshot_files_temp');
638 end if;
639
640 end get_max_fil_vers_over_appltops;
641
642
643
644 --
645 -- procedure instantiate_global_view
646 --
647 procedure instantiate_global_view
648 (
649 p_apps_system_name varchar2,
650 p_instantiate_from_preseed boolean,
651 p_cur_appl_top_id varchar2,
652 p_release_name varchar2,
653 p_is_run_flow boolean
654 ) is
655 l_dummy varchar2(1);
656 l_preseeded_snapshot_id number;
657 l_global_appl_top_id number;
658 l_global_snapshot_id number;
659 l_count_appltops number;
660 l_is_curr_rel_gv boolean;
661 l_release_id number;
662 l_global_snapshot_type varchar2(1);
663 l_current_snapshot_type varchar2(1);
664
665 begin
666
667 select release_id into l_release_id
668 from ad_releases
669 where major_version||'.'||minor_version||'.'||tape_version = p_release_name;
670
671
672 if (p_is_run_flow = TRUE)
673 then
674 l_global_snapshot_type := 'G';
675 l_current_snapshot_type := 'C';
676 else
677 l_global_snapshot_type := 'Q';
678 l_current_snapshot_type := 'P';
679 end if;
680
681 begin
682 select snapshot_id into l_global_snapshot_id
683 from ad_snapshots
684 where snapshot_name ='GLOBAL_VIEW'
685 and snapshot_type= l_global_snapshot_type
686 and appl_top_id = (
687 select appl_top_id
688 from ad_appl_tops
689 where appl_top_type = 'G'
690 and name = 'GLOBAL'
691 and applications_system_name = p_apps_system_name
692 );
693
694 exception when no_data_found then
695 l_global_snapshot_id := -1;
696 end;
697
698 if p_instantiate_from_preseed then
699 if l_global_snapshot_id <> -1 then
700 begin
701 select 'x' into l_dummy
702 from ad_snapshots ads
703 where ads.release_id = l_release_id
704 and ads.snapshot_id = l_global_snapshot_id;
705 exception when no_data_found then
706 l_dummy := null;
707 end;
708
709 if l_dummy is null then
710 update ad_snapshots
711 set snapshot_type = 'O',
712 snapshot_name = snapshot_name||'-'||snapshot_id,
713 last_update_date = sysdate
714 where snapshot_id = l_global_snapshot_id;
715
716 l_global_snapshot_id := -1;
717
718 end if;
719 end if;
720
721 if l_global_snapshot_id = -1 then
722
723 -- Caller ensures that preseeded info is present
724
725 -- Bug 4143940: Earlier this query used to use the
726 -- release_name from FND_PRODUCT_GROUPS. This caused
727 -- a few problems because instantiate_current_view()
728 -- ended up using a different release_name (l_rlse_nm)
729 -- and this function was using another one (like
730 -- 11.5.10 in instantiate_current_view() and 11.5.10.1
731 -- in this.
732 --
733 -- To solve this, instantiate_current_view now passes
734 -- l_rlse_nm to this function as a parameter.
735 --
736
737 select ss.snapshot_id
738 into l_preseeded_snapshot_id
739 from ad_snapshots ss, -- seeded
740 ad_appl_tops ats -- seeded
741 where ss.appl_top_id = ats.appl_top_id
742 and ss.snapshot_type = 'B'
743 and ss.snapshot_name like '*PRESEEDED*'||p_release_name||'%'
744 and ats.name = '*PRESEEDED*'
745 and ats.applications_system_name = '*PRESEEDED*'
746 and ats.appl_top_type = 'S';
747
751
748 get_create_global_view_header(p_apps_system_name, p_is_run_flow,
749 l_global_appl_top_id,
750 l_global_snapshot_id, l_count_appltops);
752
753 /* Instantiate preseeded bugfixes information to GLOBAL_VIEW */
754
755 insert into ad_snapshot_bugfixes
756 (
757 snapshot_bug_id, snapshot_id, bugfix_id, bug_status, success_flag,
758 creation_date, last_update_date, last_updated_by, created_by,
759 inconsistent_flag
760 )
761 select
762 ad_snapshot_bugfixes_s.nextval,
763 l_global_snapshot_id,
764 sbs.bugfix_id,
765 sbs.bug_status,
766 sbs.success_flag,
767 sysdate, sysdate, 5, 5,
768 'N' /* inconsistent_flag: set to 'N' */
769 from ad_snapshot_bugfixes sbs -- seeded
770 where sbs.snapshot_id = l_preseeded_snapshot_id
771 and not exists (select /*+ INDEX(SB2 AD_SNAPSHOT_BUGFIXES_U2) */
772 'Already exists'
773 from ad_snapshot_bugfixes sb2
774 where sb2.snapshot_id = l_global_snapshot_id
775 and sb2.bugfix_id = sbs.bugfix_id);
776
777
778 /* Instantiate preseeded files information to GLOBAL_VIEW */
779
780 insert into ad_snapshot_files
781 (
782 snapshot_file_id,
783 snapshot_id, file_id, containing_file_id,
784 file_version_id,
785 update_source_id, update_type,
786 creation_date, last_update_date, last_updated_by, created_by,
787 appl_top_id, inconsistent_flag,
788 server_type_admin_flag, server_type_forms_flag,
789 server_type_node_flag, server_type_web_flag,
790 dest_file_id, file_type_flag
791 )
792 select
793 ad_snapshot_files_s.nextval,
794 l_global_snapshot_id, file_id, containing_file_id,
795 file_version_id,
796 update_source_id, update_type,
797 sysdate, sysdate, 5, 5,
798 p_cur_appl_top_id, 'N',
799 server_type_admin_flag, server_type_forms_flag,
800 server_type_node_flag, server_type_web_flag,
801 dest_file_id, file_type_flag
802 from ad_snapshot_files sf
803 where snapshot_id = l_preseeded_snapshot_id
804 and not exists (select /*+ INDEX(SF2 AD_SNAPSHOT_FILES_U2) */
805 'Already exists'
806 from ad_snapshot_files sf2
807 where sf2.snapshot_id = l_global_snapshot_id
808 and sf2.file_id = sf.file_id
809 and nvl(sf2.containing_file_id, -1) =
810 nvl(sf.containing_file_id, -1));
811
812
813 end if;
814
815 else -- p_instantiate_from_preseed is false
816
817 if l_global_snapshot_id <> -1 then
818
819 begin
820 select 'x' into l_dummy
821 from ad_snapshots ads, ad_releases adr
822 where ads.release_id = adr.release_id
823 and ads.snapshot_id = l_global_snapshot_id
824 and adr.major_version = ( select distinct major_version from ad_releases
825 where release_id = l_release_id );
826
827 exception when no_data_found then
828 l_dummy := null;
829 end;
830
831 if l_dummy is null then
832 update ad_snapshots
833 set snapshot_type = 'O',
834 snapshot_name = snapshot_name||'-'||snapshot_id,
835 last_update_date = sysdate
836 where snapshot_id = l_global_snapshot_id;
837 l_global_snapshot_id := -1;
838
839 else
840 update ad_snapshots
841 set release_id = l_release_id,
842 last_update_date = sysdate
843 where snapshot_id = l_global_snapshot_id
844 and release_id <> l_release_id;
845 end if;
846
847 end if;
848
849 if l_global_snapshot_id = -1 then
850
851 -- In this case, instantiate from existing curr-vws.
852
853 -- Wipe out temp table
854
855 execute immediate
856 'truncate table ' || G_UN_FND || '.ad_snapshot_bugfixes_temp';
857
858 --
859 -- Insert candidate bugs into temp table.
860 -- Rules:
861 -- If explicit in any appltop, insert it as explicit
862 -- If success-flag=N in any appltop, insert it as N
863 --
864
865 insert into ad_snapshot_bugfixes_temp
866 (
867 bugfix_id,
868 bug_status,
869 success_flag
870 )
871 select
872 bugfix_id,
873 decode(min(decode(bug_status, 'EXPLICIT', 1, 2)), 1,
874 'EXPLICIT', 'IMPLICIT'),
875 decode(min(decode(success_flag, 'N', 1, 2)), 1, 'N', 'Y')
876 from ad_snapshot_bugfixes
877 where snapshot_id in (select snapshot_id
878 from ad_snapshots s, ad_appl_tops at1
879 where s.appl_top_id = at1.appl_top_id
880 and at1.applications_system_name = p_apps_system_name
881 and at1.appl_top_type = 'R'
882 and s.snapshot_type = l_current_snapshot_type
883 and s.snapshot_name = 'CURRENT_VIEW')
884 group by bugfix_id;
885
886 --
887 -- Now insert files into temp table, picking highest version from across
888 -- APPL_TOPs
889 --
890
891 get_max_fil_vers_over_appltops(p_apps_system_name => p_apps_system_name,
892 p_limit_to_candidate_files => FALSE,
896 p_is_run_flow => p_is_run_flow
893 p_wipe_out_temp_table_at_start => TRUE,
894 p_commit => TRUE,
895 p_gather_stats => TRUE,
897 );
898
899
900 get_create_global_view_header(p_apps_system_name, p_is_run_flow,
901 l_global_appl_top_id,
902 l_global_snapshot_id, l_count_appltops);
903
904
905 -- Now insert bugs into actual table (mark them all as inconsistent)
906
907 insert into ad_snapshot_bugfixes
908 (
909 snapshot_bug_id, snapshot_id,
910 bugfix_id, bug_status, success_flag,
911 inconsistent_flag,
912 creation_date, last_update_date, last_updated_by, created_by
913 )
914 select
915 ad_snapshot_bugfixes_s.nextval, l_global_snapshot_id,
916 t.bugfix_id, t.bug_status, t.success_flag,
917 decode(l_count_appltops, 1, 'N', null),
918 sysdate, sysdate, 5, 5
919 from ad_snapshot_bugfixes_temp t
920 where not exists (select 'Already exists'
921 from ad_snapshot_bugfixes sb2
922 where sb2.snapshot_id = l_global_snapshot_id
923 and sb2.bugfix_id = t.bugfix_id);
924
925 -- Now insert files into actual table
926 -- Bug 3863707, changed the query not to select duplicate rows. 09/01/2004, cbhati.
927
928 insert into ad_snapshot_files
929 (
930 snapshot_file_id, snapshot_id,
931 file_id, containing_file_id, file_version_id,
932 dest_file_id, file_type_flag,
933 appl_top_id,
934 inconsistent_flag,
935 update_source_id, update_type,
936 creation_date, last_update_date, created_by, last_updated_by
937 )
938 select
939 ad_snapshot_files_s.nextval, l_global_snapshot_id,
940 t.file_id, t.containing_file_id, t.file_version_id,
941 t.dest_file_id, t.file_type_flag,
942 decode(l_count_appltops, 1, p_cur_appl_top_id, null),
943 decode(l_count_appltops, 1, 'N', null),
944 -1, 'P',
945 sysdate, sysdate, 5, 5
946 from
947 (
948 select
949 file_id,
950 max(containing_file_id) containing_file_id,
951 max(file_version_id) file_version_id,
952 max(dest_file_id) dest_file_id,
953 decode(max(decode(file_type_flag,'M',1,'N',0,2)),
954 1,'M',0,'N',null) file_type_flag
955 from
956 ad_snapshot_files_temp
957 group by file_id) t
958 where not exists (select 'Already exists'
959 from ad_snapshot_files sf2
960 where sf2.snapshot_id = l_global_snapshot_id
961 and sf2.file_id = t.file_id
962 and nvl(sf2.containing_file_id, -1) =
963 nvl(t.containing_file_id, -1)
964 );
965 end if;
966 end if;
967
968 commit;
969
970 end instantiate_global_view;
971
972
973
974 --
975 -- Public program units
976 --
977 -- Returns TRUE if we are allowed to maintain snapshots (using a temporary
978 -- strategy of a "wierd" row in AD_TIMESTAMPS)
979 --
980 -- CORRECTION: Starting 4/25/02 bug# 2345215, will always return TRUE.
981
982 function snapshot_maint_allowed return boolean is
983 begin
984 return TRUE;
985 end;
986
987 procedure update_current_view
988 (p_patch_run_id number,
989 p_appl_top_id number)
990 is
991 l_at_id number;
992 begin
993 -- obtain a lock, to ensure serialized access to temp table infrastructure
994 ad_file_util.lock_infrastructure;
995
996 if not snapshot_maint_allowed then
997 goto return_success;
998 end if;
999
1000 if p_appl_top_id is null then
1001 select pr.appl_top_id
1002 into l_at_id
1003 from ad_patch_runs pr
1004 where pr.patch_run_id = p_patch_run_id;
1005 else
1006 -- make sure the 2 are consistent
1007 select pr.appl_top_id
1008 into l_at_id
1009 from ad_patch_runs pr
1010 where pr.patch_run_id = p_patch_run_id
1011 and pr.appl_top_id = p_appl_top_id;
1012 end if;
1013
1014 delete from ad_patch_hist_snaps_temp;
1015
1016 if G_DEBUG then
1017 put_line('About to insert PR-ID into temp table');
1018 end if;
1019
1020 insert into ad_patch_hist_snaps_temp
1021 (
1022 action_code, patch_run_id
1023 )
1024 select G_PR_ID_ACT_CD, pr.patch_run_id
1025 from ad_patch_runs pr
1026 where pr.patch_run_id = p_patch_run_id;
1027
1028 if G_DEBUG then
1029 put_line('Inserted '||to_char(sql%rowcount)||' PR-ID rows');
1030 put_line('About to call update_current_view() (the one '||
1031 'that works on many)');
1032 end if;
1033
1034 update_current_view('IN_TEMP_TAB', l_at_id, TRUE);
1035
1036
1037 -- release the lock upon successful completion
1038
1039 <<return_success>>
1040
1041 ad_file_util.unlock_infrastructure;
1042
1043 exception when others then
1044 -- release the lock upon errors
1045 ad_file_util.unlock_infrastructure;
1046
1047 -- and allow the exception (that made us land here) to propogate
1048 raise;
1049 end update_current_view;
1050
1051 procedure update_current_view
1052 (p_patch_runs_spec varchar2,
1056 l_snapshot_id number;
1053 p_appl_top_id number,
1054 p_caller_is_managing_locks boolean)
1055 is
1057 l_curr_rlse_nm varchar2(50);
1058 l_curr_rlse_id number;
1059 l_apps_zip_f_id number; -- file-id of apps.zip (using the AU one)
1060
1061 L_ARCH_NONE_ACT_CD constant number := 10; -- file is never archived (eg.fmbs)
1062 L_ARCH_CLIB_ACT_CD constant number := 11; -- file is archived in a C archive
1063 -- library (eg .o's)
1064 L_ARCH_AZIP_ACT_CD constant number := 12; -- file is archived in apps.zip
1065 -- (eg .class files)
1066
1067 l_return_code varchar2(3);
1068
1069 l_ins_stmt1 varchar2(400);
1070 l_ins_stmt1_contd varchar2(400);
1071 l_hint varchar2(10);
1072 l_sel_list varchar2(1000);
1073 l_from_where varchar2(700);
1074 l_trailer varchar2(100);
1075
1076 l_count number := 0;
1077 l_src_dest_info_exists boolean := FALSE;
1078 l_copy_actions_exist boolean;
1079 l_only_one_driver_row boolean;
1080
1081 l_snapshot_files_inserted number := 0;
1082 l_gathered_stats_flag boolean := FALSE;
1083
1084 l_inconsistent_flag varchar2(1);
1085 l_global_snapshot_id number;
1086 l_snapshot_count number;
1087 l_gsnapshot_count number;
1088 l_apps_system_name varchar2(30);
1089
1090 l_is_run_flow boolean;
1091 l_current_snapshot_type varchar2(1);
1092 l_global_snapshot_type varchar2(1);
1093
1094 l_deleted_ru_file_ids NUM_TAB;
1095
1096 i number;
1097 l_dummy varchar2(1);
1098 begin
1099 l_inconsistent_flag := 'Y';
1100
1101 if (ad_zd.is_editions_enabled = 'N' or
1102 sys_context('userenv', 'current_edition_name') = ad_zd.get_edition('RUN'))
1103 then
1104 l_is_run_flow := TRUE;
1105 l_current_snapshot_type := 'C';
1106 l_global_snapshot_type := 'G';
1107 else
1108 l_is_run_flow := FALSE;
1109 l_current_snapshot_type := 'P';
1110 l_global_snapshot_type := 'Q';
1111 end if;
1112
1113 if not p_caller_is_managing_locks then
1114 -- obtain a lock, to ensure serialized access to temp table infrastructure
1115 ad_file_util.lock_infrastructure;
1116 end if;
1117
1118 if G_DEBUG then
1119 put_line('In update_current_view(). (the one that '||
1120 'works on many ptch-runs)');
1121 end if;
1122
1123 if p_patch_runs_spec not in ('IN_TEMP_TAB', 'ALL') then
1124 raise_application_error(-20000,
1125 'Invalid parameters: p_patch_runs_spec MUST be IN_TEMP_TAB or ALL.');
1126 end if;
1127
1128 if not snapshot_maint_allowed then
1129 goto return_success;
1130 end if;
1131
1132 if p_patch_runs_spec = 'ALL' then
1133 -- insert all patch-runs for this appl-top into temp table
1134
1135 if G_DEBUG then
1136 put_line('About to insert PR-IDs into temp table');
1137 end if;
1138
1139 insert into ad_patch_hist_snaps_temp
1140 (
1141 patch_run_id, action_code
1142 )
1143 select
1144 patch_run_id, G_PR_ID_ACT_CD
1145 from ad_patch_runs
1146 where appl_top_id = p_appl_top_id
1147 and patch_run_id not in (select patch_run_id
1148 from ad_patch_hist_snaps_temp
1149 where action_code = G_PR_ID_ACT_CD);
1150
1151 if G_DEBUG then
1152 put_line('Inserted '||to_char(sql%rowcount)||' PR-ID rows');
1153 end if;
1154 end if;
1155
1156
1157 -- Gather stats on the temp table at the start (it may have stats from its
1158 -- prior use. For that, first commit.
1159
1160 commit;
1161
1162 fnd_stats.gather_table_stats(G_UN_FND, 'ad_patch_hist_snaps_temp');
1163
1164 -- set some flags, that help us fine-tune SQL's down the line
1165
1166 l_only_one_driver_row := FALSE;
1167 l_copy_actions_exist := TRUE;
1168
1169 select nvl(count(*), 0)
1170 into l_count
1171 from ad_patch_hist_snaps_temp;
1172
1173 if l_count = 0 then
1174 goto return_success;
1175 elsif l_count = 1 then
1176 l_only_one_driver_row := TRUE;
1177 else
1178 l_only_one_driver_row := FALSE;
1179 end if;
1180
1181 l_count := 0;
1182
1183 begin
1184 select 1
1185 into l_count
1186 from ad_patch_hist_snaps_temp t,
1187 ad_patch_runs pr,
1188 ad_patch_drivers pd
1189 where t.patch_run_id = pr.patch_run_id
1190 and pr.patch_driver_id = pd.patch_driver_id
1191 and pd.driver_type_c_flag = 'Y'
1192 and rownum < 2;
1193
1194 l_copy_actions_exist := TRUE;
1195
1196 exception when no_data_found then
1197 l_copy_actions_exist := FALSE;
1198 end;
1199
1200
1201 -- Instantiate current-view snapshot for the current on-site rlse. For
1202 -- that, first get the onsite rlse.
1203
1204 select release_name
1205 into l_curr_rlse_nm
1206 from fnd_product_groups;
1207
1208 -- Then get its rlse-id
1209
1210 declare
1211 l1 number;
1212 l2 number;
1213 l3 number;
1214 l4 number;
1215 begin
1216 l1 := instr(l_curr_rlse_nm, '.');
1217 l2 := instr(l_curr_rlse_nm, '.', l1+1);
1218 l3 := instr(l_curr_rlse_nm, '.', l2+1);
1219 l4 := instr(l_curr_rlse_nm, '.', l3+1);
1220
1221 -- Check that there are necessarily (and at most) 2 dots, and that too at
1222 -- valid places.
1223 if (l1 > 1 and l2>l1+1 and l3=0 ) OR
1224 (l1 > 1 and l2>l1+1 and l3>l2+1 and l4=0) then
1225 -- this is a good format. Re-compute l3 as length(p_release_name)
1229 raise_application_error(-20000, 'Invalid release - '||
1226 l3 := length(l_curr_rlse_nm);
1227 else
1228 -- this is an invalid format. Error out
1230 l_curr_rlse_nm);
1231 end if;
1232
1233 AD_RELEASES_PVT.CreateRelease
1234 (
1235 p_major_version => to_number(substr(l_curr_rlse_nm, 1, l1-1)),
1236 p_minor_version => to_number(substr(l_curr_rlse_nm, l1+1, l2-l1-1)),
1237 p_tape_version => to_number(substr(l_curr_rlse_nm, l2+1, l3-l2)),
1238 p_row_src_comments => 'Created while updating current-view snapshot '||
1239 'using patch-history info',
1240 p_release_id => l_curr_rlse_id,
1241 p_base_rel_flag => 'N',
1242 p_start_dt => sysdate,
1243 p_created_by_user_id => -1
1244 );
1245 end;
1246
1247 -- Finally, instantiate the current-view snapshot
1248
1249 instantiate_current_view
1250 (
1251 l_curr_rlse_id, p_appl_top_id,
1252 FALSE, p_caller_is_managing_locks,
1253 l_return_code
1254 );
1255
1256 select snapshot_id
1257 into l_snapshot_id
1258 from ad_snapshots
1259 where appl_top_id = p_appl_top_id
1260 and snapshot_name = 'CURRENT_VIEW'
1261 and snapshot_type = l_current_snapshot_type;
1262
1263 if G_DEBUG then
1264 put_line('Curr-vw snapshotid is '||to_char(l_snapshot_id));
1265 end if;
1266
1267 select applications_system_name into l_apps_system_name
1268 from ad_appl_tops where appl_top_id = p_appl_top_id;
1269
1270
1271 /* Get Global snapshot ID for this Applications System */
1272
1273 select snapshot_id into l_global_snapshot_id
1274 from ad_snapshots s, ad_appl_tops t
1275 where s.snapshot_type = l_global_snapshot_type and
1276 s.snapshot_name = 'GLOBAL_VIEW' and
1277 s.appl_top_id = t.appl_top_id and
1278 t.applications_system_name = l_apps_system_name;
1279
1280 if G_DEBUG then
1281 put_line('Global-vw snapshot id is '||to_char(l_global_snapshot_id));
1282 end if;
1283
1284 -- identify the apps.zip:
1285 -- (assumed to be in lower case, even on NT. It is *suspected* that our
1286 -- OSD's for ALL platforms (when the Snapshot Utility C code is run) will
1287 -- return apps.zip filename in lower case, so we can rest assured that
1288 -- we won't end up with a slew of records (atleast so long as our
1289 -- *suspicion* is true. %% Need NT testing to really verify)
1290 begin
1291 select f.file_id
1292 into l_apps_zip_f_id
1293 from ad_files f
1294 where f.filename = 'apps.zip'
1295 and f.app_short_name = 'AU'
1296 and f.subdir = 'java';
1297 exception when no_data_found then
1298 insert into ad_files
1299 (
1300 file_id,
1301 app_short_name, subdir, filename,
1302 creation_date, last_update_date, last_updated_by, created_by
1303 )
1304 values
1305 (
1306 ad_files_s.nextval,
1307 'AU', 'java', 'apps.zip',
1308 sysdate, sysdate, 5, 5
1309 ) returning file_id into l_apps_zip_f_id;
1310 end;
1311
1312 if G_DEBUG then
1313 put_line('apps.zip file-id is '||to_char(l_apps_zip_f_id));
1314 put_line('Ensuring that lib<prod>.a files exist in AD_FILES...');
1315 end if;
1316
1317 -- Note: assumes that app-short-names are indeed APP-SHORT-NAMES, and not
1318 -- prod-abbrs, as they used to be some time back.
1319
1320 -- for all the various Unix's
1321 insert into ad_files
1322 (
1323 file_id,
1324 app_short_name,
1325 subdir,
1326 filename,
1327 creation_date, last_update_date, last_updated_by, created_by
1328 )
1329 select -- for all the various Unix's
1330 ad_files_s.nextval,
1331 a.application_short_name,
1332 'lib',
1333 'lib'||decode(a.application_short_name, 'SQLGL', 'gl',
1334 'SQLAP', 'ap',
1335 'OFA', 'fa',
1336 'SQLSO', 'so',
1337 lower(a.application_short_name))||
1338 '.a',
1339 sysdate, sysdate, 5, 5
1340 from fnd_application a
1341 where not exists (select
1342 'lib<prod>.a already exists'
1343 from ad_files f2
1344 where f2.filename = 'lib'||
1345 decode(a.application_short_name,
1346 'SQLGL', 'gl',
1347 'SQLAP', 'ap',
1348 'OFA', 'fa',
1349 'SQLSO', 'so',
1350 lower(a.application_short_name))||
1351 '.a'
1352 and f2.subdir = 'lib'
1353 and f2.app_short_name = a.application_short_name);
1354
1355 if G_DEBUG then
1356 put_line('Done ensuring that lib<prod>.a files exist in AD_FILES...');
1357 put_line('>>(had to insert '||to_char(sql%rowcount)||' rows)');
1358 put_line('Ensuring that <prod>st.lib files exist in AD_FILES...');
1359 end if;
1360
1361 -- for NT
1362 insert into ad_files
1363 (
1364 file_id,
1365 app_short_name,
1366 subdir,
1367 filename,
1368 creation_date, last_update_date, last_updated_by, created_by
1369 )
1370 select
1371 ad_files_s.nextval,
1372 a.application_short_name,
1373 'lib',
1374 decode(a.application_short_name, 'SQLGL', 'gl',
1375 'SQLAP', 'ap',
1379 'st.lib',
1376 'OFA', 'fa',
1377 'SQLSO', 'so',
1378 lower(a.application_short_name))||
1380 sysdate, sysdate, 5, 5
1381 from fnd_application a
1382 where not exists (select
1383 '<prod>st.lib already exists'
1384 from ad_files f2
1385 where f2.filename = decode(a.application_short_name,
1386 'SQLGL', 'gl',
1387 'SQLAP', 'ap',
1388 'OFA', 'fa',
1389 'SQLSO', 'so',
1390 lower(a.application_short_name))||
1391 'st.lib'
1392 and f2.subdir = 'lib'
1393 and f2.app_short_name = a.application_short_name);
1394
1395 if G_DEBUG then
1396 put_line('Done ensuring that <prod>st.lib files exist in AD_FILES...');
1397 put_line('>>(had to insert '||to_char(sql%rowcount)||' rows)');
1398 end if;
1399
1400
1401 -- Process snapshot_files *only* if there were copy-actions in the patch
1402
1403 if l_copy_actions_exist then
1404
1405 if G_DEBUG then
1406 put_line('About to insert candidate FILES info into temp table');
1407 end if;
1408
1409 /*
1410 Using the set of starter patch-run rows in the temp table, we first
1411 build the list of candidate files in the temp table.
1412
1413 To help populate the CONTAINING_FILE_ID correctly for C code, we need to
1414 be able to distinguish bween C code that have a main() and those that dont.
1415 The reason is that the former do NOT get into the archive
1416 library (lib<prod>.a on Unix), while the latter do. Hence, the containing
1417 file should be left NULL for C code that have a main(). To achieve this, we
1418 need to set ACTION_CODE (in temp tab) for "main()" C code to
1419 L_ARCH_NONE_ACT_CD.
1420
1421 This is done in a 2-step process, viz. an INSERT SQL followed by an UPDATE
1422 SQL. The first SQL inserts candidate rows into the temp table, and the
1423 second one flags (updates) C object files that are not archived (main()'s),
1424 that got mistaken as non-main() files in the first SQL.
1425
1426 Moreover, when there is only 1 row in the temp table, then the analytic
1427 function can be done away with and replaced with a grp by instead (since
1428 each FILE_ID partition will have only 1 row, or worst case, multiple
1429 rows with same version). Note that in the vast majority of the cases,
1430 we will have only 1 starter row in the temp table (multiple rows
1431 occur only in the applptch.txt upload case)
1432
1433 The 2nd SQL (the UPDATE) is however the same regardless of whether its
1434 a big/small patch, or whether there are 1/many rows in the temp tables
1435 to start with.
1436
1437 The first SQL (INSERT stmt) is explained below:
1438
1439 Case 1: 1 patch-run
1440 ------
1441 The SQL to use is:
1442
1443 insert into ad_patch_hist_snaps_temp
1444 (patch_run_id, action_code, file_id, file_version_id,
1445 app_short_name, filename,
1446 version_segment1, version_segment2,
1447 version_segment3, version_segment4,
1448 version_segment5, version_segment6,
1449 version_segment7, version_segment8,
1450 version_segment9, version_segment10,
1451 translation_level, dest_file_id, file_type_flag)
1452 select
1453 max(pr.patch_run_id),
1454 decode(max(pca.action_code),
1455 'copy',
1456 decode(max(f.subdir),
1457 'lib',
1458 case when (max(f.filename) like '%.o' or
1459 max(f.filename) like '%.obj') then
1460 :clib_cd else
1461 :none_cd end,
1462 :none_cd),
1463 'RU_Insert', :azip_cd,
1464 'RU_Update', :azip_cd,
1465 'RU_Delete', :azip_cd,
1466 :none_cd) act_cd,
1467 f.file_id, max(afv.file_version_id),
1468 max(f.app_short_name), max(f.filename),
1469 max(afv.version_segment1), max(afv.version_segment2),
1470 max(afv.version_segment3), max(afv.version_segment4),
1471 max(afv.version_segment5), max(afv.version_segment6),
1472 max(afv.version_segment7), max(afv.version_segment8),
1473 max(afv.version_segment9), max(afv.version_segment10),
1474 max(afv.translation_level), max(prba.dest_file_id),
1475 max(prba.file_type_flag)
1476 from ad_patch_hist_snaps_temp t, ad_patch_runs pr,
1477 ad_patch_run_bugs prb, ad_patch_run_bug_actions prba,
1478 ad_patch_common_actions pca, ad_file_versions afv, ad_files f
1479 where pr.patch_run_id = t.patch_run_id
1480 and pr.appl_top_id = :at_id
1481 and t.action_code = :pr_id_act_cd
1482 and pr.patch_run_id = prb.patch_run_id
1483 and prb.patch_run_bug_id = prba.patch_run_bug_id
1484 and prba.common_action_id = pca.common_action_id
1485 and prba.file_id = f.file_id
1486 and prba.patch_file_version_id = afv.file_version_id (+)
1487 and prba.executed_flag = 'Y'
1488 and pca.action_code in ('copy', 'forcecopy', 'RU_Update',
1489 'RU_Insert', 'RU_Delete')
1490 group by f.file_id;
1491
1492 Case 2: Many patch-runs
1493 ------
1494 Here, we may have different versions of the same file copied in
1495 different patches (eg. adpvov v115.10 in patch 1, v115.11 in patch 2,
1496 etc). And what we'd like is to form groups, each group containing
1497 the various copied versions of 1 file, and then pick the highest
1501 The SQL to use is:
1498 version for a file in that file's group). To achieve this, we use the
1499 analytic function ROW_NUMBER.
1500
1502
1503 insert into ad_patch_hist_snaps_temp
1504 (patch_run_id, action_code, file_id, file_version_id,
1505 app_short_name, filename,
1506 version_segment1, version_segment2,
1507 version_segment3, version_segment4,
1508 version_segment5, version_segment6,
1509 version_segment7, version_segment8,
1510 version_segment9, version_segment10,
1511 translation_level, dest_file_id, file_type_flag)
1512 select
1513 m.patch_run_id, m.act_cd, m.file_id, m.file_version_id,
1514 m.app_short_name, m.filename,
1515 m.version_segment1, m.version_segment2,
1516 m.version_segment3, m.version_segment4,
1517 m.version_segment5, m.version_segment6,
1518 m.version_segment7, m.version_segment8,
1519 m.version_segment9, m.version_segment10,
1520 m.translation_level, prba.dest_file_id, prba.file_type_flag
1521 from
1522 (
1523 select
1524 pr.patch_run_id, afv.file_version_id,
1525 f.file_id, f.app_short_name, f.filename,
1526 afv.version_segment1, afv.version_segment2,
1527 afv.version_segment3, afv.version_segment4,
1528 afv.version_segment5, afv.version_segment6,
1529 afv.version_segment7, afv.version_segment8,
1530 afv.version_segment9, afv.version_segment10,
1531 afv.translation_level,
1532 decode(pca.action_code, 'copy',
1533 decode(f.subdir, 'lib',
1534 case when (f.filename like '%.o' or f.filename like '%.obj')
1535 then :clib_cd
1536 else :none_cd
1537 end,
1538 :none_cd),
1539 'RU_Insert', :azip_cd,
1540 'RU_Update', :azip_cd,
1541 'RU_Delete', :azip_cd,
1542 :none_cd) act_cd,
1543 row_number() over (partition by f.file_id
1544 order by afv.version_segment1 desc, afv.version_segment2 desc,
1545 afv.version_segment3 desc, afv.version_segment4 desc,
1546 afv.version_segment5 desc, afv.version_segment6 desc,
1547 afv.version_segment7 desc, afv.version_segment8 desc,
1548 afv.version_segment9 desc, afv.version_segment10 desc,
1549 afv.translation_level desc nulls last) as r1
1550 from ad_patch_hist_snaps_temp t, ad_patch_runs pr,
1551 ad_patch_run_bugs prb, ad_patch_run_bug_actions prba,
1552 ad_patch_common_actions pca, ad_file_versions afv, ad_files f
1553 where pr.patch_run_id = t.patch_run_id
1554 and pr.appl_top_id = :at_id
1555 and t.action_code = :pr_id_act_cd
1556 and pr.patch_run_id = prb.patch_run_id
1557 and prb.patch_run_bug_id = prba.patch_run_bug_id
1558 and prba.common_action_id = pca.common_action_id
1559 and prba.file_id = f.file_id
1560 and prba.patch_file_version_id = afv.file_version_id (+)
1561 and prba.executed_flag = 'Y'
1562 and pca.action_code in ('copy', 'forcecopy', 'RU_Update',
1563 'RU_Insert', 'RU_Delete')
1564 ) m
1565 where m.r1 = 1
1566
1567
1568 Next, the second SQL (UPDATE stmt) is explained below:
1569
1570 update
1571 ad_patch_hist_snaps_temp t
1572 set t.action_code = :none_cd
1573 where t.action_code = :clib_cd
1574 and not exists (select
1575 'libin action exists for this .o (ie. its archived)'
1576 from ad_patch_run_bug_actions prba,
1577 ad_patch_run_bugs prb,
1578 ad_patch_common_actions pca
1579 where prba.file_id = t.file_id
1580 and prba.patch_run_bug_id = prb.patch_run_bug_id
1581 and prb.patch_run_id = t.patch_run_id
1582 and pca.common_action_id = prba.common_action_id
1583 and pca.action_code = 'libin')
1584
1585 */
1586
1587 l_ins_stmt1 :=
1588 'insert into ad_patch_hist_snaps_temp '||
1589 '(patch_run_id, action_code, file_id, file_version_id, '||
1590 'app_short_name, filename, '||
1591 'version_segment1, version_segment2, '||
1592 'version_segment3, version_segment4, '||
1593 'version_segment5, version_segment6, '||
1594 'version_segment7, version_segment8, '||
1595 'version_segment9, version_segment10, '||
1596 'translation_level, dest_file_id, file_type_flag) '||
1597 'select ';
1598
1599 l_hint := ' ';
1600
1601 l_from_where :=
1602 'from ad_patch_hist_snaps_temp t, ad_patch_runs pr, '||
1603 'ad_patch_run_bugs prb, ad_patch_run_bug_actions prba, '||
1604 'ad_patch_common_actions pca, ad_file_versions afv, ad_files f '||
1605 'where pr.patch_run_id = t.patch_run_id '||
1606 'and pr.appl_top_id = :at_id '||
1607 'and t.action_code = :pr_id_act_cd '||
1608 'and pr.patch_run_id = prb.patch_run_id '||
1609 'and prb.patch_run_bug_id = prba.patch_run_bug_id '||
1610 'and prba.common_action_id = pca.common_action_id '||
1611 'and prba.file_id = f.file_id '||
1612 'and prba.patch_file_version_id = afv.file_version_id (+) '||
1613 'and prba.executed_flag = ''Y'' '||
1614 'and pca.action_code in (''copy'', ''forcecopy'', ''RU_Update'', '||
1615 '''RU_Insert'', ''RU_Delete'') ';
1616
1617 if l_only_one_driver_row then
1618
1619 l_ins_stmt1_contd := null;
1620
1621 l_sel_list :=
1622 'max(pr.patch_run_id), '||
1623 'decode(max(pca.action_code), '||
1627 'case when (max(f.filename) like ''%.o'' or '||
1624 '''copy'', '||
1625 'decode(max(f.subdir), '||
1626 '''lib'', '||
1628 'max(f.filename) like ''%.obj'') then '||
1629 ':clib_cd else '||
1630 ':none_cd end, '||
1631 ':none_cd), '||
1632 '''RU_Insert'', '||
1633 ':azip_cd, '||
1634 '''RU_Update'', '||
1635 ':azip_cd, '||
1636 '''RU_Delete'', '||
1637 ':azip_cd, '||
1638 ':none_cd) act_cd, '||
1639 'f.file_id, max(afv.file_version_id), '||
1640 'max(f.app_short_name), max(f.filename), '||
1641 'max(afv.version_segment1), max(afv.version_segment2), '||
1642 'max(afv.version_segment3), max(afv.version_segment4), '||
1643 'max(afv.version_segment5), max(afv.version_segment6), '||
1644 'max(afv.version_segment7), max(afv.version_segment8), '||
1645 'max(afv.version_segment9), max(afv.version_segment10), '||
1646 'max(afv.translation_level), max(prba.dest_file_id), '||
1647 'max(prba.file_type_flag) ';
1648
1649 l_trailer := 'group by f.file_id ';
1650
1651 else
1652 -- Multiple starter patch-runs in temp table
1653
1654 l_ins_stmt1_contd :=
1655 'm.patch_run_id, m.act_cd, m.file_id, m.file_version_id, '||
1656 'm.app_short_name, m.filename, '||
1657 'm.version_segment1, m.version_segment2, '||
1658 'm.version_segment3, m.version_segment4, '||
1659 'm.version_segment5, m.version_segment6, '||
1660 'm.version_segment7, m.version_segment8, '||
1661 'm.version_segment9, m.version_segment10, '||
1662 'm.translation_level, m.dest_file_id, m.file_type_flag '||
1663 'from '||
1664 '( '||
1665 'select ';
1666
1667 l_sel_list :=
1668 'pr.patch_run_id, afv.file_version_id, '||
1669 'f.file_id, f.app_short_name, f.filename, '||
1670 'afv.version_segment1, afv.version_segment2, '||
1671 'afv.version_segment3, afv.version_segment4, '||
1672 'afv.version_segment5, afv.version_segment6, '||
1673 'afv.version_segment7, afv.version_segment8, '||
1674 'afv.version_segment9, afv.version_segment10, '||
1675 'afv.translation_level, '||
1676 'decode(pca.action_code, ''copy'', '||
1677 'decode(f.subdir, ''lib'', '||
1678 'case when (f.filename like ''%.o'' or '||
1679 'f.filename like ''%.obj'') '||
1680 'then :clib_cd '||
1681 'else :none_cd '||
1682 'end, '||
1683 ':none_cd), '||
1684 '''RU_Insert'', :azip_cd, '||
1685 '''RU_Update'', :azip_cd, '||
1686 '''RU_Delete'', :azip_cd, '||
1687 ':none_cd) act_cd, '||
1688 'row_number() over (partition by f.file_id '||
1689 'order by afv.version_segment1 desc, afv.version_segment2 desc, '||
1690 'afv.version_segment3 desc, afv.version_segment4 desc, '||
1691 'afv.version_segment5 desc, afv.version_segment6 desc, '||
1692 'afv.version_segment7 desc, afv.version_segment8 desc, '||
1693 'afv.version_segment9 desc, afv.version_segment10 desc, '||
1694 'afv.translation_level desc nulls last) as r1, '||
1695 'prba.dest_file_id, prba.file_type_flag ';
1696 l_trailer := ') m where m.r1 = 1';
1697
1698 end if; -- End If Multiple starter patch-runs in temp table
1699
1700 /* Debugging code. Helps in validating the built SQL, as well as in
1701 sizing the vars appropriately.
1702
1703 if G_DEBUG then
1704 put_line('@@INDIVIDUAL VC2 VARS:');
1705 put_line('l_ins_stmt1 (len='||to_char(length(l_ins_stmt1))||'): ');
1706 put_line(l_ins_stmt1);
1707 put_line('l_ins_stmt1_contd (len='||
1708 to_char(length(l_ins_stmt1_contd))||'): ');
1709 put_line(l_ins_stmt1_contd);
1710 put_line('l_hint (len='||to_char(length(l_hint))||'): ');
1711 put_line(l_hint);
1712 put_line('l_sel_list (len='||to_char(length(l_sel_list))||'): ');
1713 put_line(l_sel_list);
1714 put_line('l_from_where (len='||to_char(length(l_from_where))||'): ');
1715 put_line(l_from_where);
1716 put_line('l_trailer (len='||to_char(length(l_trailer))||'): ');
1717 put_line(l_trailer);
1718
1719 put_line('@@FULL INSERT STMT:@@');
1720 put_line(l_ins_stmt1||l_ins_stmt1_contd||l_hint||
1721 l_sel_list||l_from_where||l_trailer);
1722 end if;
1723 */
1724
1725 execute immediate l_ins_stmt1 || l_ins_stmt1_contd || l_hint ||
1726 l_sel_list || l_from_where || l_trailer
1727 using L_ARCH_CLIB_ACT_CD, L_ARCH_NONE_ACT_CD, L_ARCH_NONE_ACT_CD,
1728 L_ARCH_AZIP_ACT_CD, L_ARCH_AZIP_ACT_CD,
1729 L_ARCH_AZIP_ACT_CD, L_ARCH_NONE_ACT_CD,
1730 p_appl_top_id, G_PR_ID_ACT_CD;
1731
1732 if G_DEBUG then
1733 put_line('Inserted '||to_char(sql%rowcount)||
1734 ' candidate FILES rows into temp table');
1735 end if;
1736
1737
1738 -- Gather stats regardless of # of rows inserted. For that first commit.
1739
1740 commit;
1741
1742 FND_STATS.Gather_Table_Stats(G_UN_FND, 'ad_patch_hist_snaps_temp');
1743
1744 -- Now issue the 2nd SQL (the UPDATE stmt, to flag the unarchived C object
1745 -- libraries as such)
1746
1747 update
1748 ad_patch_hist_snaps_temp t
1749 set t.action_code = L_ARCH_NONE_ACT_CD
1750 where t.action_code = L_ARCH_CLIB_ACT_CD
1754 ad_patch_run_bugs prb,
1751 and not exists (select
1752 'libin action exists for this .o (ie. its archived)'
1753 from ad_patch_run_bug_actions prba,
1755 ad_patch_common_actions pca
1756 where prba.file_id = t.file_id
1757 and prba.patch_run_bug_id = prb.patch_run_bug_id
1758 and prb.patch_run_id = t.patch_run_id
1759 and pca.common_action_id = prba.common_action_id
1760 and pca.action_code = 'libin');
1761
1762
1763 -- Next, update the CLIB_ARCH_FILE_ID column in the temp table.
1764
1765 update
1766 ad_patch_hist_snaps_temp t
1767 set t.clib_arch_file_id =
1768 (
1769 select f.file_id
1770 from ad_files f
1771 where f.app_short_name = translate(t.app_short_name, 'A#', 'A')
1772 and f.subdir = 'lib'
1773 and f.filename = decode(
1774 lower(substr(t.filename, instr(t.filename,'.',-1),
1775 length(t.filename) -
1776 instr(t.filename,'.',-1) + 1)),
1777 '.o', 'lib', null) ||
1778 decode(translate(t.app_short_name, 'A#', 'A'),
1779 'SQLGL', 'gl',
1780 'SQLAP', 'ap',
1781 'OFA', 'fa',
1782 'SQLSO', 'so',
1783 lower(translate(t.app_short_name, 'A#', 'A'))) ||
1784 decode(
1785 lower(substr(t.filename, instr(t.filename,'.',-1),
1786 length(t.filename) -
1787 instr(t.filename,'.',-1) + 1)),
1788 '.o', '.a', 'st.lib')
1789 )
1790 where t.action_code = L_ARCH_CLIB_ACT_CD;
1791
1792 -- Update information in ad_patch_hist_snaps_temp about the
1793 -- irep_gathered_flag. Since ad_patch_hist_snaps_temp table
1794 -- has information about files which were patched (or newly
1795 -- introduced), set/reset the irep_gathered_flag to 'N' and
1796 -- the last_patched_date to sysdate. The irep_gathered_flag
1797 -- signifies that these files (with the flag set to 'N') have
1798 -- not been processed yet. Once they are processed, this flag
1799 -- will be set to 'Y'. Then again when they get patched, we set
1800 -- the flag to 'N' making them ready for processing .... and the
1801 -- cycle continues...
1802 --
1803 -- Bug 3807737 - sgadag.
1804
1805 if G_DEBUG then
1806 put_line('Updating irep_gathered_flag data in ad_patch_hist_snaps_temp');
1807 end if;
1808
1809
1810 update ad_patch_hist_snaps_temp t
1811 set t.irep_gathered_flag = 'N';
1812
1813
1814 -- Rupsingh Bug 3675019. 06/07/2004
1815
1816
1817 -- Now, incrementally INSERT new files and UPDATE existing files
1818 -- in the snapshot.
1819
1820 if G_DEBUG then
1821 put_line('Inserting new files in the curr-vw snapshot');
1822 end if;
1823
1824 -- insert new files
1825 insert into ad_snapshot_files
1826 (
1827 snapshot_file_id,
1828 snapshot_id,
1829 file_id,
1830 containing_file_id,
1831 file_version_id,
1832 dest_file_id,
1833 file_type_flag,
1834 update_source_id,
1835 update_type,
1836 creation_date, last_update_date,
1837 last_updated_by, created_by,
1838 appl_top_id,
1839 irep_gathered_flag,
1840 last_patched_date
1841 )
1842 select
1843 ad_snapshot_files_s.nextval,
1844 l_snapshot_id,
1845 t.file_id,
1846 decode(t.action_code, L_ARCH_CLIB_ACT_CD, t.clib_arch_file_id,
1847 L_ARCH_AZIP_ACT_CD, l_apps_zip_f_id,
1848 null),
1849 t.file_version_id,
1850 t.dest_file_id,
1851 t.file_type_flag,
1852 t.patch_run_id,
1853 'P',
1854 sysdate, sysdate,
1855 5, 5, p_appl_top_id,
1856 t.irep_gathered_flag,
1857 sysdate
1858 from ad_patch_hist_snaps_temp t
1859 where t.action_code in (L_ARCH_NONE_ACT_CD, L_ARCH_CLIB_ACT_CD,
1860 L_ARCH_AZIP_ACT_CD)
1861 and not exists (select 'Already exists'
1862 from ad_snapshot_files sf
1863 where sf.snapshot_id = l_snapshot_id
1864 and sf.file_id = t.file_id
1865 and ((sf.containing_file_id is null and
1866 t.action_code = L_ARCH_NONE_ACT_CD)
1867 or
1868 (sf.containing_file_id = decode(t.action_code,
1869 L_ARCH_CLIB_ACT_CD, t.clib_arch_file_id,
1870 L_ARCH_AZIP_ACT_CD, l_apps_zip_f_id))
1871 )
1872 );
1873
1874 l_snapshot_files_inserted := sql%rowcount;
1875
1876 if G_DEBUG then
1877 put_line('Inserted '||to_char(l_snapshot_files_inserted)||
1878 ' new files in the curr-vw snapshot');
1879 end if;
1880
1881 select count(*) into l_snapshot_count
1882 from ad_snapshots s, ad_appl_tops t
1883 where s.snapshot_type = l_current_snapshot_type and
1884 s.appl_top_id = t.appl_top_id and
1885 t.applications_system_name = l_apps_system_name;
1886
1887 /* At least one current view snapshot should be there at this point */
1888
1889 if l_snapshot_count = 1 then
1890 l_inconsistent_flag := 'N';
1891 elsif l_snapshot_count = 0 then
1895
1892 raise_application_error(-20000, 'Error: update_current_view: ' ||
1893 'Currrent view snapshot doesn''t exist.');
1894 end if;
1896 /* insert new files into Global current view snapshot */
1897
1898 insert into ad_snapshot_files
1899 (
1900 snapshot_file_id, snapshot_id, file_id, containing_file_id,
1901 file_version_id, dest_file_id, file_type_flag,
1902 update_source_id, update_type,
1903 creation_date, last_update_date, last_updated_by,
1904 created_by, appl_top_id, inconsistent_flag
1905 )
1906 select
1907 ad_snapshot_files_s.nextval,
1908 l_global_snapshot_id, -- Global Snapshot ID here
1909 t.file_id,
1910 decode(t.action_code, L_ARCH_CLIB_ACT_CD, t.clib_arch_file_id,
1911 L_ARCH_AZIP_ACT_CD, l_apps_zip_f_id,
1912 null),
1913 t.file_version_id,
1914 t.dest_file_id,
1915 t.file_type_flag,
1916 t.patch_run_id,
1917 'P',
1918 sysdate, sysdate,
1919 5, 5, p_appl_top_id,
1920 l_inconsistent_flag
1921 from ad_patch_hist_snaps_temp t
1922 where
1923 t.action_code in (L_ARCH_NONE_ACT_CD, L_ARCH_CLIB_ACT_CD,
1924 L_ARCH_AZIP_ACT_CD)
1925 and not exists (select 'Already exists'
1926 from ad_snapshot_files sf
1927 where sf.snapshot_id = l_global_snapshot_id
1928 and sf.file_id = t.file_id
1929 and ((sf.containing_file_id is null and
1930 t.action_code = L_ARCH_NONE_ACT_CD)
1931 or
1932 (sf.containing_file_id = decode(t.action_code,
1933 L_ARCH_CLIB_ACT_CD, t.clib_arch_file_id,
1934 L_ARCH_AZIP_ACT_CD, l_apps_zip_f_id))
1935 )
1936 );
1937
1938 l_snapshot_files_inserted := l_snapshot_files_inserted + sql%rowcount;
1939
1940 if G_DEBUG then
1941 put_line('Inserted '||to_char(sql%rowcount)||
1942 ' new files in the Global View snapshot');
1943 end if;
1944
1945 commit;
1946
1947 -- gather stats if necessary
1948
1949 ad_stats_util_pkg.gather_stats_if_necessary('SNAPSHOT',
1950 l_snapshot_files_inserted,
1951 TRUE,
1952 TRUE,
1953 l_gathered_stats_flag);
1954
1955 if G_DEBUG then
1956 put_line('Updating existing files in the curr-vw snapshot');
1957 end if;
1958
1959 -- update existing files if higher version, or if dest_file_id
1960 -- or file_type_flag is different (Current View)
1961
1962 update ad_snapshot_files sf
1963 set
1964 (sf.file_version_id, sf.update_source_id,
1965 sf.dest_file_id, sf.file_type_flag, sf.irep_gathered_flag) =
1966 (select t.file_version_id, t.patch_run_id,
1967 t.dest_file_id, t.file_type_flag, t.irep_gathered_flag
1968 from ad_patch_hist_snaps_temp t
1969 where t.file_id = sf.file_id),
1970 sf.update_type = 'P',
1971 sf.last_update_date = sysdate,
1972 sf.last_updated_by = 5,
1973 sf.last_patched_date = sysdate
1974 where sf.snapshot_id = l_snapshot_id
1975 and sf.file_id in (select t2.file_id
1976 from ad_patch_hist_snaps_temp t2
1977 where t2.action_code in (L_ARCH_NONE_ACT_CD,
1978 L_ARCH_CLIB_ACT_CD, L_ARCH_AZIP_ACT_CD))
1979 and exists
1980 (
1981 select 'File exists in curr-vw with lower version'
1982 from ad_patch_hist_snaps_temp t, ad_file_versions fv_old
1983 where sf.file_id = t.file_id
1984 and t.file_version_id = fv_old.file_version_id (+)
1985 and sf.file_version_id = fv_old.file_version_id (+)
1986 -- Update only if patch version is higher (code copied from adfilutb.pls)
1987 and (((fv_old.file_version_id is null)
1988 or
1989 ('Y' = decode(
1990 sign(nvl(t.version_segment1,0) - nvl(fv_old.version_segment1,0)),
1991 -1, null, 1, 'Y', decode(
1992 sign(nvl(t.version_segment2,0) - nvl(fv_old.version_segment2,0)),
1993 -1, null, 1, 'Y', decode(
1994 sign(nvl(t.version_segment3,0) - nvl(fv_old.version_segment3,0)),
1995 -1, null, 1, 'Y', decode(
1996 sign(nvl(t.version_segment4,0) - nvl(fv_old.version_segment4,0)),
1997 -1, null, 1, 'Y', decode(
1998 sign(nvl(t.version_segment5,0) - nvl(fv_old.version_segment5,0)),
1999 -1, null, 1, 'Y', decode(
2000 sign(nvl(t.version_segment6,0) - nvl(fv_old.version_segment6,0)),
2001 -1, null, 1, 'Y', decode(
2002 sign(nvl(t.version_segment7,0) - nvl(fv_old.version_segment7,0)),
2003 -1, null, 1, 'Y', decode(
2004 sign(nvl(t.version_segment8,0) - nvl(fv_old.version_segment8,0)),
2005 -1, null, 1, 'Y', decode(
2006 sign(nvl(t.version_segment9,0) - nvl(fv_old.version_segment9,0)),
2007 -1, null, 1, 'Y', decode(
2008 sign(nvl(t.version_segment10,0) - nvl(fv_old.version_segment10,0)),
2009 -1, null, 1, 'Y', decode(
2010 sign(t.translation_level - fv_old.translation_level),
2011 -1, null, 1, 'Y', null)))))))))))
2012 )
2013 )
2014 or (nvl(sf.dest_file_id, -1) <> nvl(t.dest_file_id, -1))
2015 or (nvl(sf.file_type_flag, 'X') <> nvl(t.file_type_flag, 'X'))
2016 )
2020 (sf.containing_file_id = decode(t.action_code,
2017 and ((sf.containing_file_id is null and
2018 t.action_code = L_ARCH_NONE_ACT_CD)
2019 or
2021 L_ARCH_CLIB_ACT_CD, t.clib_arch_file_id,
2022 L_ARCH_AZIP_ACT_CD, l_apps_zip_f_id))
2023 )
2024 );
2025
2026 if G_DEBUG then
2027 put_line('Updated '||to_char(sql%rowcount)||
2028 ' files in the curr-vw snapshot');
2029 end if;
2030
2031 commit;
2032
2033
2034 /* Now Deleting the class files that are not part of apps.zip
2035 ie. All the entries in ad_patch_run_bug_actions for which
2036 action_code is RU_Delete */
2037
2038 /* Bug#3483080/3419891:
2039 We KNOW that RU_Delete actions will be very, very few.
2040 Therefore our approach is:
2041 - Hold these temporarily in a collection type
2042 - While fetching the data, give the hints exactly as given
2043 below (no more, no less) worked great in volume testing
2044 db (gsiappkm).
2045 - Issue a bulk-bind DELETE using the collection type
2046 */
2047
2048 select /*+ ordered use_nl(prba, prb, pr, t)
2049 index(pca ad_patch_common_actions_n1) */
2050 distinct prba.file_id
2051 bulk collect into l_deleted_ru_file_ids
2052 from ad_patch_common_actions pca,
2053 ad_patch_run_bug_actions prba,
2054 ad_patch_run_bugs prb,
2055 ad_patch_runs pr,
2056 ad_patch_hist_snaps_temp t
2057 where pr.patch_run_id = t.patch_run_id and
2058 pr.appl_top_id = p_appl_top_id and
2059 t.action_code = G_PR_ID_ACT_CD and
2060 pr.patch_run_id = prb.patch_run_id and
2061 prba.patch_run_bug_id = prb.patch_run_bug_id and
2062 prba.common_action_id = pca.common_action_id and
2063 pca.action_code = 'RU_Delete';
2064
2065 if nvl(l_deleted_ru_file_ids.last, 0) > 0 then
2066
2067 forall i in l_deleted_ru_file_ids.first..l_deleted_ru_file_ids.last
2068 delete from ad_snapshot_files
2069 where snapshot_id = l_snapshot_id and
2070 file_id = l_deleted_ru_file_ids(i);
2071
2072 if G_DEBUG then
2073 put_line('Deleted '||to_char(sql%rowcount)||
2074 ' class files which are removed from apps.zip');
2075 end if;
2076
2077 end if;
2078
2079 commit;
2080
2081
2082 -- Now update the files into the global view (GV). For this, first insert
2083 -- relevant rows into the temp table, picking highest version from across
2084 -- APPL_TOPs, considering just the files delivered by this patch (ie. just
2085 -- the candidate files)
2086 --
2087
2088 get_max_fil_vers_over_appltops(p_apps_system_name => l_apps_system_name,
2089 p_limit_to_candidate_files => TRUE,
2090 p_wipe_out_temp_table_at_start => TRUE,
2091 p_commit => TRUE,
2092 p_gather_stats => TRUE,
2093 p_is_run_flow => l_is_run_flow
2094 );
2095
2096 declare
2097 file_id_list NUM_TAB;
2098 dest_file_id_list NUM_TAB;
2099 file_type_flag_list VC1_TAB;
2100 file_version_id_list NUM_TAB;
2101 containing_file_id_list NUM_TAB;
2102 inconsistent_flag_list VC1_TAB;
2103
2104 rows NATURAL := 1000;
2105
2106 cursor crec is
2107 select file_id, dest_file_id, file_type_flag,
2108 file_version_id, containing_file_id,
2109 inconsistent_flag
2110 from ad_snapshot_files_temp;
2111 begin
2112
2113 open crec;
2114
2115 loop
2116 fetch crec bulk collect into
2117 file_id_list,
2118 dest_file_id_list,
2119 file_type_flag_list,
2120 file_version_id_list,
2121 containing_file_id_list,
2122 inconsistent_flag_list
2123 limit rows;
2124
2125 if file_id_list.count > 0 then
2126 forall j in file_id_list.first.. file_id_list.last
2127 update /*+ INDEX(SF AD_SNAPSHOT_FILES_U2) */
2128 ad_snapshot_files sf
2129 set sf.last_update_date = sysdate,
2130 sf.last_updated_by = 5,
2131 sf.file_version_id = file_version_id_list(j),
2132 sf.update_source_id = -1,
2133 sf.update_type = 'P',
2134 sf.appl_top_id = null,
2135 sf.inconsistent_flag = null,
2136 sf.dest_file_id = dest_file_id_list(j),
2137 sf.file_type_flag = file_type_flag_list(j)
2138 where
2139 sf.snapshot_id = l_global_snapshot_id and
2140 sf.file_id = file_id_list(j) and
2141 nvl(sf.containing_file_id, -1) =
2142 nvl(containing_file_id_list(j), -1) and
2143 -- rkagrawa: deliberately putting -2 here since for versionless
2144 -- files, we want the update_type, update_source_id, etc to be
2145 -- updated each time the file is patched
2146 (nvl(sf.file_version_id, -1) <> nvl(file_version_id_list(j), -2) or
2147 sf.inconsistent_flag <> inconsistent_flag_list(j) or
2148 nvl(sf.dest_file_id, -1) <> nvl(dest_file_id_list(j), -1) or
2149 nvl(sf.file_type_flag, -1) <> nvl(file_type_flag_list(j), -1))
2150 ;
2151 end if;
2152
2153 commit;
2154 exit when crec%NOTFOUND;
2158
2155 end loop;
2156
2157 close crec;
2159 if G_DEBUG then
2160 put_line('Updated ' || to_char(sql%rowcount) ||
2161 ' in Global view snapshot');
2162 end if;
2163
2164 end;
2165
2166
2167 /* Delete files from Global view snapshot */
2168
2169 if nvl(l_deleted_ru_file_ids.last, 0) > 0 then
2170
2171 forall i in l_deleted_ru_file_ids.first..l_deleted_ru_file_ids.last
2172 delete from ad_snapshot_files sf
2173 where sf.snapshot_id = l_global_snapshot_id and
2174 sf.file_id = l_deleted_ru_file_ids(i)
2175 and not exists
2176 (
2177 select 'Exists in the curr vw of some appltop'
2178 from ad_snapshot_files sf1
2179 where sf1.file_id = l_deleted_ru_file_ids(i)
2180 and nvl(sf1.containing_file_id, -1) = nvl(sf.containing_file_id, -1)
2181 and sf1.snapshot_id in (
2182 select s.snapshot_id
2183 from ad_snapshots s, ad_appl_tops a
2184 where s.snapshot_type = l_current_snapshot_type
2185 and s.appl_top_id = a.appl_top_id
2186 and a.applications_system_name = l_apps_system_name
2187 and nvl(a.active_flag,'Y') = 'Y'
2188 )
2189 );
2190
2191 if G_DEBUG then
2192 put_line('Deleted '||to_char(sql%rowcount)||
2193 ' files from Global View snapshot');
2194 end if;
2195
2196 end if;
2197
2198 --Bug 7255366 nissubra---
2199 update ad_snapshots set last_update_date = sysdate,
2200 snapshot_update_date = sysdate
2201 where snapshot_id in (l_snapshot_id, l_global_snapshot_id);
2202
2203 commit;
2204
2205 end if; -- End If l_copy_actions_exist
2206
2207
2208
2209 --
2210 -- Maintain bugfixes
2211 --
2212
2213 -- First, delete unwanted rows from the temp table.
2214
2215 delete from ad_patch_hist_snaps_temp
2216 where action_code <> G_PR_ID_ACT_CD;
2217
2218 commit;
2219
2220 handle_bugs(p_action_code => G_PR_ID_ACT_CD,
2221 p_snapshot_id => l_snapshot_id,
2222 p_update_global_view => TRUE,
2223 p_global_snapshot_id => l_global_snapshot_id,
2224 p_delete_junk_created => FALSE); -- let it be, can help debug.
2225
2226 commit;
2227
2228
2229 -- release the lock upon successful completion
2230
2231 <<return_success>>
2232
2233 if not p_caller_is_managing_locks then
2234 ad_file_util.unlock_infrastructure;
2235 end if;
2236
2237 exception when others then
2238 if not p_caller_is_managing_locks then
2239 -- release the lock upon errors
2240 ad_file_util.unlock_infrastructure;
2241 end if;
2242
2243 -- and allow the exception (that made us land here) to propogate
2244 raise;
2245 end update_current_view;
2246
2247 -- instantiate_current_view:
2248 -- instantiates snapshot and snapshot-bugfixes (not snapshot-files)
2249 procedure instantiate_current_view
2250 (p_release_id number,
2251 p_appl_top_id number,
2252 p_fail_if_no_preseeded_rows boolean,
2253 p_caller_is_managing_locks boolean,
2254 p_return_code out nocopy varchar2)
2255 is
2256 l_dummy varchar2(1);
2257 l_apps_sys_nm varchar2(30);
2258 l_prim_apps_sys_nm varchar2(30);
2259 l_at_name varchar2(50);
2260 l_preseeded_rlse_id number;
2261 l_rlse_nm varchar2(50);
2262 l_preseeded_snapshot_id number := -1;
2263 l_curr_vw_snapshot_id number := -1;
2264 l_snapshot_bugs_inserted number := 0;
2265 l_snapshot_files_inserted number := 0;
2266 l_gathered_stats_flag boolean := FALSE;
2267 l_inst_gv_snap_from_pseed boolean := FALSE;
2268 l_global_snapshot_id number;
2269 l_global_appl_top_id number;
2270 -- l_server_type_admin_flag varchar2(1);
2271 -- l_server_type_forms_flag varchar2(1);
2272 -- l_server_type_node_flag varchar2(1);
2273 -- l_server_type_web_flag varchar2(1);
2274 l_snapshot_type varchar2(1);
2275 l_is_run_flow boolean;
2276 begin
2277
2278 l_preseeded_snapshot_id := -1;
2279
2280 if not p_caller_is_managing_locks then
2281 -- obtain a lock, to ensure serialized access to temp table infrastructure
2282 ad_file_util.lock_infrastructure;
2283 end if;
2284
2285 if not snapshot_maint_allowed then
2286 p_return_code := G_SNAPSHOT_MAINT_DISALLOWED; -- snapshot maint not allowed
2287 goto return_success;
2288 else
2289 p_return_code := null;
2290 end if;
2291
2292 -- First, abort if PRIMARY apps-sys-nm has not been set by bootstrap code
2293
2294 select nvl(applications_system_name, '1 UNKNOWN 1')
2295 into l_prim_apps_sys_nm
2296 from fnd_product_groups;
2297
2298 if l_prim_apps_sys_nm = '1 UNKNOWN 1' then
2299 raise_application_error(-20000,
2300 'Primary Applications System has not been initialized yet.');
2301 end if;
2302
2303 select to_char(major_version)||'.'||
2304 to_char(minor_version)||'.'||
2305 to_char(tape_version)
2306 into l_rlse_nm
2307 from ad_releases
2308 where release_id = p_release_id;
2309
2310 -- Fail if appl-top in question is invalid
2311
2312 begin
2313 select nvl(applications_system_name, '1 UNKNOWN 1'), name
2314 into l_apps_sys_nm, l_at_name
2315 from ad_appl_tops
2316 where appl_top_id = p_appl_top_id
2320 'APPL-TOP ID "'||to_char(p_appl_top_id)||'" is not a valid APPL-TOP.');
2317 and appl_top_type = 'R';
2318 exception when no_data_found then
2319 raise_application_error(-20000,
2321 end;
2322
2323 -- Fail if p_appl_top_id belongs to some other apps-system (refer talk with
2324 -- Rick on 1/11/02)
2325
2326 if l_apps_sys_nm <> l_prim_apps_sys_nm then
2327 raise_application_error(-20000,
2328 'Applications System name for APPL-TOP ID "'||to_char(p_appl_top_id)||
2329 '" is not a primary one. Instantiation not allowed.');
2330 end if;
2331
2332 -- Fail if that apps-sys-nm has not been initialized.
2333
2334 if nvl(l_apps_sys_nm, '1 UNKNOWN 1') = '1 UNKNOWN 1' then
2335 raise_application_error(-20000,
2336 'Applications System name for APPL-TOP ID "'||to_char(p_appl_top_id)||
2337 '" has not been initialized yet.');
2338 end if;
2339
2340 if (ad_zd.is_editions_enabled = 'N' or
2341 sys_context('userenv', 'current_edition_name') = ad_zd.get_edition('RUN'))
2342 then
2343 l_is_run_flow := TRUE;
2344 l_snapshot_type := 'C';
2345 else
2346 l_is_run_flow := FALSE;
2347 l_snapshot_type := 'P';
2348 end if;
2349
2350 begin
2351 select snapshot_id
2352 into l_curr_vw_snapshot_id
2353 from ad_snapshots
2354 where appl_top_id = p_appl_top_id
2355 and snapshot_name = 'CURRENT_VIEW'
2356 and snapshot_type = l_snapshot_type;
2357 exception when no_data_found then
2358 null;
2359 end;
2360
2361
2362 begin
2363 select ss.snapshot_id, ss.release_id
2364 into l_preseeded_snapshot_id, l_preseeded_rlse_id
2365 from ad_snapshots ss, -- seeded
2366 ad_appl_tops ats -- seeded
2367 where ss.appl_top_id = ats.appl_top_id
2368 and ss.snapshot_type = 'B'
2369 and ss.snapshot_name like '*PRESEEDED*'||l_rlse_nm||'%'
2370 and ats.name = '*PRESEEDED*'
2371 and ats.applications_system_name = '*PRESEEDED*'
2372 and ats.appl_top_type = 'S'
2373 and ss.release_id = p_release_id;
2374
2375 exception when no_data_found then
2376 null;
2377 end;
2378
2379 if l_preseeded_snapshot_id <> -1 then
2380 if l_curr_vw_snapshot_id <> -1 then
2381 begin
2382 select 'x' into l_dummy
2383 from ad_snapshots ads
2384 where nvl(ran_snapshot_flag, 'N') = 'Y'
2385 and ads.release_id = p_release_id
2386 and ads.snapshot_id = l_curr_vw_snapshot_id;
2387 exception when no_data_found then
2388 l_dummy := null;
2389 end;
2390
2391 if l_dummy is null then
2392 update ad_snapshots
2393 set snapshot_type = 'O',
2394 snapshot_name = snapshot_name||'-'||snapshot_id,
2395 last_update_date = sysdate
2396 where snapshot_id = l_curr_vw_snapshot_id;
2397
2398 l_curr_vw_snapshot_id := -1;
2399
2400 end if;
2401 end if;
2402
2403 if l_curr_vw_snapshot_id = -1 then
2404
2405 -- create new CV through pressed.
2406
2407 select ad_snapshots_s.nextval into l_curr_vw_snapshot_id from dual;
2408
2409 insert into ad_snapshots
2410 (
2411 snapshot_id,
2412 release_id, appl_top_id,
2413 snapshot_name, comments,
2414 snapshot_creation_date, snapshot_update_date,
2415 snapshot_type, ran_snapshot_flag,
2416 creation_date, last_update_date, last_updated_by, created_by
2417 )
2418 select
2419 l_curr_vw_snapshot_id,
2420 p_release_id, p_appl_top_id,
2421 'CURRENT_VIEW', 'Current View',
2422 sysdate, sysdate,
2423 l_snapshot_type, 'N',
2424 sysdate, sysdate, 5, 5
2425 from dual;
2426
2427
2428 -- select
2429 -- nvl(at1.server_type_admin_flag, 'N'), nvl(at1.server_type_forms_flag, 'N'),
2430 -- nvl(at1.server_type_node_flag, 'N'), nvl(at1.server_type_web_flag, 'N')
2431 -- into
2432 -- l_server_type_admin_flag, l_server_type_forms_flag,
2433 -- l_server_type_node_flag, l_server_type_web_flag
2434 -- from ad_appl_tops at1
2435 -- where at1.appl_top_id = p_appl_top_id;
2436
2437 -- Bugs
2438
2439 insert into ad_snapshot_bugfixes
2440 (
2441 snapshot_bug_id,
2442 snapshot_id, bugfix_id,
2443 bug_status, success_flag,
2444 creation_date, last_update_date, last_updated_by, created_by
2445 )
2446 select
2447 ad_snapshot_bugfixes_s.nextval,
2448 l_curr_vw_snapshot_id, sbs.bugfix_id,
2449 sbs.bug_status, sbs.success_flag,
2450 sysdate, sysdate, 5, 5
2451 from ad_snapshot_bugfixes sbs -- seeded
2452 where sbs.snapshot_id = l_preseeded_snapshot_id
2453 and not exists (select /*+ INDEX(SB2 AD_SNAPSHOT_BUGFIXES_U2) */
2454 'Already exists'
2455 from ad_snapshot_bugfixes sb2
2456 where sb2.snapshot_id = l_curr_vw_snapshot_id
2457 and sb2.bugfix_id = sbs.bugfix_id);
2458
2459 l_snapshot_bugs_inserted := sql%rowcount;
2460
2461 insert into ad_snapshot_files
2462 (
2463 snapshot_file_id,
2464 snapshot_id, file_id, containing_file_id,
2465 file_version_id,
2466 update_source_id, update_type,
2467 creation_date, last_update_date, last_updated_by, created_by,
2468 dest_file_id, file_type_flag
2469 )
2470 select
2471 ad_snapshot_files_s.nextval,
2472 l_curr_vw_snapshot_id, file_id, containing_file_id,
2473 file_version_id,
2474 update_source_id, update_type,
2478 where sf.snapshot_id = l_preseeded_snapshot_id
2475 sysdate, sysdate, 5, 5,
2476 dest_file_id, file_type_flag
2477 from ad_snapshot_files sf
2479
2480 -- Added for bug 3947949
2481 -- and ((sf.server_type_admin_flag = l_server_type_admin_flag
2482 -- and l_server_type_admin_flag = 'Y') or
2483 -- (sf.server_type_forms_flag = l_server_type_forms_flag
2484 -- and l_server_type_forms_flag = 'Y') or
2485 -- (sf.server_type_node_flag = l_server_type_node_flag
2486 -- and l_server_type_node_flag = 'Y') or
2487 -- (sf.server_type_web_flag = l_server_type_web_flag
2488 -- and l_server_type_web_flag = 'Y')
2489 -- )
2490
2491 and not exists (select /*+ INDEX(SF2 AD_SNAPSHOT_FILES_U2) */
2492 'Already exists' from ad_snapshot_files sf2
2493 where sf2.snapshot_id = l_curr_vw_snapshot_id
2494 and sf2.file_id = sf.file_id
2495 and nvl(sf2.containing_file_id, -1) =
2496 nvl(sf.containing_file_id, -1));
2497 l_snapshot_files_inserted := sql%rowcount;
2498
2499 if l_snapshot_files_inserted > 0 then
2500
2501 /* set ran_snapshot_flag only incase the *PRESEEDED* snapshot
2502 * has files information.
2503 */
2504
2505 update ad_snapshots
2506 set ran_snapshot_flag = 'Y',
2507 last_update_date = sysdate
2508 where snapshot_id = l_curr_vw_snapshot_id;
2509
2510 l_inst_gv_snap_from_pseed := TRUE;
2511
2512 end if;
2513
2514 p_return_code := G_INSTANTIATED_SNAPSHOT_BUGS; -- Instantiated snpsht-bugs
2515
2516 else
2517 p_return_code := G_ALREADY_INSTANTIATED; -- Already Instantiated
2518 end if;
2519
2520 else -- l_preseeded_snapshot_id is null
2521
2522 if l_curr_vw_snapshot_id <> -1 then
2523
2524 begin
2525 select 'x' into l_dummy
2526 from ad_snapshots ads, ad_releases adr
2527 where ads.release_id = adr.release_id
2528 and ads.snapshot_id = l_curr_vw_snapshot_id
2529 and adr.major_version = ( select distinct major_version from ad_releases
2530 where release_id = p_release_id );
2531
2532 exception when no_data_found then
2533 l_dummy := null;
2534 end;
2535
2536 if l_dummy is null then
2537
2538 update ad_snapshots
2539 set snapshot_type = 'O',
2540 last_update_date = sysdate,
2541 snapshot_name = snapshot_name||'-'||snapshot_id
2542 where snapshot_id = l_curr_vw_snapshot_id;
2543
2544 l_curr_vw_snapshot_id := -1;
2545
2546 end if;
2547 end if;
2548
2549 if l_curr_vw_snapshot_id = -1 then
2550
2551 if p_fail_if_no_preseeded_rows then
2552 raise_application_error(-20000,
2553 'No preseeded snapshots to instantiate from.');
2554 else
2555 p_return_code := G_NO_PRESEEDED_BASELINE; -- No Preseeded Baseline rows
2556 l_preseeded_snapshot_id := -1;
2557 end if;
2558
2559 p_return_code := G_INSTANTIATED_SNAPSHOT; -- Instantiated Snapshot
2560
2561 select ad_snapshots_s.nextval into l_curr_vw_snapshot_id from dual;
2562
2563 insert into ad_snapshots
2564 (
2565 snapshot_id,
2566 release_id, appl_top_id,
2567 snapshot_name, comments,
2568 snapshot_creation_date, snapshot_update_date,
2569 snapshot_type, ran_snapshot_flag,
2570 creation_date, last_update_date, last_updated_by, created_by
2571 )
2572 select
2573 l_curr_vw_snapshot_id,
2574 p_release_id, p_appl_top_id,
2575 'CURRENT_VIEW', 'Current View',
2576 sysdate, sysdate,
2577 l_snapshot_type, 'N',
2578 sysdate, sysdate, 5, 5
2579 from dual;
2580
2581 else
2582
2583 update ad_snapshots
2584 set release_id = p_release_id,
2585 last_update_date = sysdate
2586 where snapshot_id = l_curr_vw_snapshot_id
2587 and release_id <> p_release_id;
2588
2589 p_return_code := G_ALREADY_INSTANTIATED; -- Already Instantiated
2590
2591 end if;
2592
2593 end if;
2594
2595 commit;
2596
2597 -- gather stats if necessary
2598
2599 ad_stats_util_pkg.gather_stats_if_necessary
2600 (
2601 'SNAPSHOT',
2602 l_snapshot_bugs_inserted + l_snapshot_files_inserted,
2603 TRUE,
2604 TRUE,
2605 l_gathered_stats_flag
2606 );
2607
2608 backfill_bugs_from_patch_hist(l_curr_vw_snapshot_id);
2609
2610 commit;
2611
2612
2613 -- The cust likely already has curr-vw snapshots of all APPLTOPs. Just
2614 -- instantiate global view using those CV's (as opposed to considering
2615 -- creating from preseeded info)
2616
2617 instantiate_global_view(p_apps_system_name => l_apps_sys_nm,
2618 p_instantiate_from_preseed => l_inst_gv_snap_from_pseed,
2619 p_cur_appl_top_id => p_appl_top_id,
2620 p_release_name => l_rlse_nm,
2621 p_is_run_flow => l_is_run_flow
2622 );
2623 <<return_success>>
2624
2625 if not p_caller_is_managing_locks then
2626 ad_file_util.unlock_infrastructure;
2627 end if;
2628
2629 exception when others then
2630 if not p_caller_is_managing_locks then
2631 -- release the lock upon errors
2632 ad_file_util.unlock_infrastructure;
2636 raise;
2633 end if;
2634
2635 -- and allow the exception (that made us land here) to propogate
2637 end instantiate_current_view;
2638
2639
2640 procedure update_rel_name(rel_name varchar2) is
2641
2642 G_CURRENT_RELEASE varchar2(50);
2643 ret_status boolean;
2644 begin
2645 select release_name
2646 into G_CURRENT_RELEASE
2647 from fnd_product_groups;
2648
2649 ret_status:=system.ad_apps_private.compare_releases(G_CURRENT_RELEASE, rel_name);
2650 --Compare and update ONLY if the rel_name is greater than the value in db.
2651 if ret_status = TRUE then
2652 update fnd_product_groups
2653 set release_name = rel_name,
2654 last_update_date = sysdate,
2655 last_updated_by = 1
2656 where product_group_id = 1;
2657 --dbms_output.put_line('major versions are different');
2658 else
2659 null; -- release_name is > the extension mimipack info for MP driver
2660 --dbms_output.put_line('no update needed.');
2661 end if;
2662 end update_rel_name;
2663
2664 begin
2665 -- initialization code
2666
2667 declare
2668 l_stat varchar2(1);
2669 l_ind varchar2(1);
2670 begin
2671 if not FND_INSTALLATION.Get_App_Info('FND', l_stat, l_ind, G_UN_FND) then
2672 raise_application_error(-20000, 'Error calling Get_App_Info().');
2673 end if;
2674 end;
2675
2676 end ad_file_sys_snapshots_pkg;