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