[Home] [Help]
PACKAGE BODY: APPS.AD_PATCH_HIST_MIGR_PKG
Source
1 package body ad_patch_hist_migr_pkg as
2 /* $Header: adphmigb.pls 120.0 2005/05/25 11:58:23 appldev noship $ */
3
4 --
5 -- Private package globals
6 --
7 G_WHO_USER_ID constant number := 3; -- 3 == Feeder system
8 G_CURRENT_RELEASE varchar2(30);
9 G_CURRENT_RELEASE_ID number;
10 G_REC_ALL_PTCHS_ON_RLSE_ID number;
11 G_IMPORT_SESSION_ID number;
12 G_APPL_TOP_ID number;
13 G_PATCH_RUN_ID number;
14 G_SNAPSHOT_MAINT_ALLOWED boolean;
15
16 --
17 -- Private program units
18 --
19 procedure init_rlse_info is
20 begin
21 G_CURRENT_RELEASE := null;
22 G_CURRENT_RELEASE_ID := null;
23 G_REC_ALL_PTCHS_ON_RLSE_ID := null;
24 end init_rlse_info;
25
26 procedure initialize is
27 begin
28 init_rlse_info;
29
30 G_APPL_TOP_ID := null;
31 G_PATCH_RUN_ID := null;
32 G_SNAPSHOT_MAINT_ALLOWED := AD_FILE_SYS_SNAPSHOTS_PKG.Snapshot_Maint_Allowed;
33
34 select fnd_concurrent_requests_s.currval
35 into G_IMPORT_SESSION_ID
36 from dual;
37 end initialize;
38
39 procedure bootstrap_release is
40 l_rlse_id number;
41 l1 number;
42 l2 number;
43 l3 number;
44 l4 number;
45 begin
46 -- Attempt to identify release only once
47
48 if G_CURRENT_RELEASE is not null then
49 return;
50 end if;
51
52 select release_name
53 into G_CURRENT_RELEASE
54 from fnd_product_groups;
55
56 l1 := instr(G_CURRENT_RELEASE, '.');
57 l2 := instr(G_CURRENT_RELEASE, '.', l1+1);
58 l3 := instr(G_CURRENT_RELEASE, '.', l2+1);
59 l4 := instr(G_CURRENT_RELEASE, '.', l3+1);
60
61 -- Check that there are necessarily (and at most) 2 dots, and that too at
62 -- valid places.
63 if (l1 > 1 and l2>l1+1 and l3=0) OR
64 (l1 > 1 and l2>l1+1 and l3>l2+1 and l4=0) then
65 -- this is a good format. Re-compute l3 as length(p_release_name)
66 l3 := length(G_CURRENT_RELEASE);
67 else
68 -- this is an invalid format. Error out
69 raise_application_error(-20000, 'Invalid release - '||
70 G_CURRENT_RELEASE);
71 end if;
72
73 AD_RELEASES_PVT.CreateRelease
74 (
75 p_major_version => to_number(substr(G_CURRENT_RELEASE, 1, l1-1)),
76 p_minor_version => to_number(substr(G_CURRENT_RELEASE, l1+1, l2-l1-1)),
77 p_tape_version => to_number(substr(G_CURRENT_RELEASE, l2+1, l3-l2)),
78 p_row_src_comments => 'Created while migrating patch-history info',
79 p_release_id => G_CURRENT_RELEASE_ID,
80 p_base_rel_flag => 'N',
81 p_start_dt => sysdate,
82 p_created_by_user_id => -1
83 );
84
85 end bootstrap_release;
86
87 function identify_patch_driver_only
88 (
89 p_src_cd varchar2,
90 p_chksum number,
91 p_fil_size number,
92 p_drv_fil_nm varchar2,
93 p_src_ptch_drvr_id number,
94 p_exported_from_db varchar2
95 ) return number is
96 l_patch_driver_id number;
97 begin
98 if p_src_cd = 'DRV' then
99
100 -- An actual driver (as opposed to a dummy one uploaded from applptch.txt.
101 -- In this case, we check on checksum, etc
102
103 select patch_driver_id
104 into l_patch_driver_id
105 from ad_patch_drivers
106 where file_contents_checksum = p_chksum
107 and file_size = p_fil_size
108 and driver_file_name = p_drv_fil_nm;
109
110 return l_patch_driver_id;
111
112 else
113
114 -- uploaded info (from applptch.txt)
115 -- In this case, we check if its ever been export-imported before
116
117 select patch_driver_id
118 into l_patch_driver_id
119 from ad_patch_drivers
120 where imported_from_db = p_exported_from_db
121 and imported_id = p_src_ptch_drvr_id;
122
123 end if; -- end if src-cd = DRV/TXT
124
125 return l_patch_driver_id;
126
127 exception when no_data_found then
128 return null;
129 end identify_patch_driver_only;
130
131
132 --
133 -- Public program units
134 --
135 procedure load_patch_driver
136 (
137 p_src_ptch_drvr_id number,
138 p_chksum number,
139 p_fil_size number,
140 p_drv_fil_nm varchar2,
141 p_src_cd varchar2,
142 p_drv_typ_cflag varchar2,
143 p_drv_typ_dflag varchar2,
144 p_drv_typ_gflag varchar2,
145 p_plat varchar2,
146 p_platver varchar2,
147 p_orig_ptch_nm varchar2,
148 p_merged_driver_flag varchar2,
149 p_merge_date date,
150 p_src_ap_app_ptch_id number,
151 p_ap_ptch_nm varchar2,
152 p_ap_ptch_typ varchar2,
153 p_ap_mtpk_lvl varchar2,
154 p_ap_src_cd varchar2,
155 p_exported_from_db varchar2,
156 p_ap_rapid_installed_flag varchar2
157 ) is
158 l_applied_patch_id number;
159 l_patch_driver_id number;
160 begin
161 l_patch_driver_id := identify_patch_driver_only(p_src_cd,
162 p_chksum, p_fil_size, p_drv_fil_nm,
163 p_src_ptch_drvr_id, p_exported_from_db);
164
165 if l_patch_driver_id is not null then
166 -- Bingo! Found a patch-driver. Simply return.
167
168 return;
169 end if;
170
171
172 -- Got here => absolutely no record of the patch-driver
173
174 begin
175 select applied_patch_id
176 into l_applied_patch_id
177 from ad_applied_patches
178 where imported_from_db = p_exported_from_db
179 and imported_id = p_src_ap_app_ptch_id;
180
181 -- Found an APP-PTCH => only need to create a PTCH-DRVR for that APP-PTCH
182
183 insert into ad_patch_drivers
184 (
185 patch_driver_id,
186 applied_patch_id, driver_file_name,
187 driver_type_c_flag, driver_type_d_flag, driver_type_g_flag,
188 platform, platform_version,
189 file_size, file_contents_checksum,
190 source_code, orig_patch_name,
191 merged_driver_flag, merge_date,
192 creation_date, last_update_date, last_updated_by, created_by,
193 imported_flag, imported_from_db, imported_id
194 ) values
195 (
196 ad_patch_drivers_s.nextval,
197 l_applied_patch_id, p_drv_fil_nm,
198 p_drv_typ_cflag, p_drv_typ_dflag, p_drv_typ_gflag,
199 p_plat, p_platver,
200 p_fil_size, p_chksum,
201 p_src_cd, p_orig_ptch_nm,
202 p_merged_driver_flag, p_merge_date,
203 sysdate, sysdate, G_WHO_USER_ID, G_WHO_USER_ID,
204 'Y', p_exported_from_db, p_src_ptch_drvr_id
205 );
206
207 return;
208
209 exception when no_data_found then
210
211 -- Need to create an APP-PTCH as well as a PTCH-DRVR row
212
213 insert into ad_applied_patches
214 (
215 applied_patch_id,
216 rapid_installed_flag, patch_name,
217 patch_type, maint_pack_level, source_code,
218 creation_date, last_update_date, last_updated_by, created_by,
219 imported_flag, imported_from_db,
220 imported_id
221 ) values
222 (
223 ad_applied_patches_s.nextval,
224 p_ap_rapid_installed_flag, p_ap_ptch_nm,
225 p_ap_ptch_typ, p_ap_mtpk_lvl, p_ap_src_cd,
226 sysdate, sysdate, G_WHO_USER_ID, G_WHO_USER_ID,
227 'Y', p_exported_from_db, p_src_ap_app_ptch_id
228 ) returning applied_patch_id into l_applied_patch_id;
229
230 insert into ad_patch_drivers
231 (
232 patch_driver_id,
233 applied_patch_id, driver_file_name,
234 driver_type_c_flag, driver_type_d_flag, driver_type_g_flag,
235 platform, platform_version,
236 file_size, file_contents_checksum,
237 source_code, orig_patch_name,
238 merged_driver_flag, merge_date,
239 creation_date, last_update_date, last_updated_by, created_by,
240 imported_flag, imported_from_db, imported_id
241 ) values
242 (
243 ad_patch_drivers_s.nextval,
244 l_applied_patch_id, p_drv_fil_nm,
245 p_drv_typ_cflag, p_drv_typ_dflag, p_drv_typ_gflag,
246 p_plat, p_platver,
247 p_fil_size, p_chksum,
248 p_src_cd, p_orig_ptch_nm,
249 p_merged_driver_flag, p_merge_date,
250 sysdate, sysdate, G_WHO_USER_ID, G_WHO_USER_ID,
251 'Y', p_exported_from_db, p_src_ptch_drvr_id
252 );
253
254 end;
255 end load_patch_driver;
256
257 procedure load_patch_driver_minipk
258 (
259 p_src_cd varchar2,
260 p_chksum number,
261 p_fil_size number,
262 p_drv_fil_nm varchar2,
263 p_src_ptch_drvr_id number,
264 p_exported_from_db varchar2,
265 p_app_short_name varchar2,
266 p_patch_level varchar2
267 ) is
268 l_patch_driver_id number;
269 l_dummy varchar2(1);
270 l_true_app_short_name varchar2(50);
271 begin
272 -- identify the PTCH-DRVR row
273
274 l_patch_driver_id := identify_patch_driver_only(p_src_cd,
275 p_chksum, p_fil_size, p_drv_fil_nm,
276 p_src_ptch_drvr_id, p_exported_from_db);
277
278 if l_patch_driver_id is null then
279 raise_application_error(-20000, 'PTCH-DRVR not yet identified!');
280 end if;
281
282 -- Then, insert a MINIPK row if it doesnt already exist
283
284 begin
285 -- make it tolerant to prod-abbrevs too
286 if p_app_short_name = 'gl' then
287 l_true_app_short_name := 'SQLGL';
288 elsif p_app_short_name = 'ap' then
289 l_true_app_short_name := 'SQLAP';
290 elsif p_app_short_name = 'fa' then
291 l_true_app_short_name := 'OFA';
292 elsif p_app_short_name = 'so' then
293 l_true_app_short_name := 'SQLSO';
294 else
295 l_true_app_short_name := upper(p_app_short_name);
296 end if;
297
298 select 'x'
299 into l_dummy
300 from ad_patch_driver_minipks
301 where patch_driver_id = l_patch_driver_id
302 and app_short_name = l_true_app_short_name;
303
304 return;
305
306 exception when no_data_found then
307
308 insert into ad_patch_driver_minipks
309 (
310 minipk_id, patch_driver_id,
311 app_short_name, patch_level,
312 creation_date, last_update_date, last_updated_by, created_by
313 ) values
314 (
315 ad_patch_driver_minipks_s.nextval, l_patch_driver_id,
316 l_true_app_short_name, p_patch_level,
317 sysdate, sysdate, G_WHO_USER_ID, G_WHO_USER_ID
318 );
319
320 end;
321
322 end load_patch_driver_minipk;
323
324 procedure load_patch_driver_lang
325 (
326 p_src_cd varchar2,
327 p_chksum number,
328 p_fil_size number,
329 p_drv_fil_nm varchar2,
330 p_src_ptch_drvr_id number,
331 p_exported_from_db varchar2,
332 p_language varchar2
333 ) is
334 l_patch_driver_id number;
335 l_dummy varchar2(1);
336 begin
337 -- identify the PTCH-DRVR row
338
339 l_patch_driver_id := identify_patch_driver_only(p_src_cd,
340 p_chksum, p_fil_size, p_drv_fil_nm,
341 p_src_ptch_drvr_id, p_exported_from_db);
342
343 if l_patch_driver_id is null then
344 raise_application_error(-20000, 'PTCH-DRVR not yet identified!');
345 end if;
346
347 -- Then, insert a LANG row if it doesnt already exist
348
349 begin
350 select 'x'
351 into l_dummy
352 from ad_patch_driver_langs
353 where patch_driver_id = l_patch_driver_id
354 and language = p_language;
355
356 return;
357
358 exception when no_data_found then
359
360 insert into ad_patch_driver_langs
361 (
362 lang_id, patch_driver_id, language,
363 creation_date, last_update_date, last_updated_by, created_by
364 ) values
365 (
366 ad_patch_driver_langs_s.nextval, l_patch_driver_id, p_language,
367 sysdate, sysdate, G_WHO_USER_ID, G_WHO_USER_ID
368 );
369
370 end;
371
372 end load_patch_driver_lang;
373
374 procedure load_comprising_patch
375 (
376 p_src_cd varchar2,
377 p_chksum number,
378 p_fil_size number,
379 p_drv_fil_nm varchar2,
380 p_src_ptch_drvr_id number,
381 p_exported_from_db varchar2,
382 p_bug_number varchar2,
383 p_aru_release_name varchar2
384 ) is
385 l_patch_driver_id number;
386 l_bug_id number;
387 l_dummy varchar2(1);
388 begin
389 if p_bug_number is null then
390 return;
391 end if;
392
393 -- identify the PTCH-DRVR row
394
395 l_patch_driver_id := identify_patch_driver_only(p_src_cd,
396 p_chksum, p_fil_size, p_drv_fil_nm,
397 p_src_ptch_drvr_id, p_exported_from_db);
398
399 if l_patch_driver_id is null then
400 raise_application_error(-20000, 'PTCH-DRVR not yet identified!');
401 end if;
402
403 select bug_id
404 into l_bug_id
405 from ad_bugs
406 where bug_number = p_bug_number
407 and aru_release_name = p_aru_release_name;
408
409 -- Then, insert a COMPRSNG-PTCH row if it doesnt already exist
410
411 begin
412 select 'x'
413 into l_dummy
414 from ad_comprising_patches
415 where patch_driver_id = l_patch_driver_id
416 and bug_id = l_bug_id;
417
418 return;
419
420 exception when no_data_found then
421
422 insert into ad_comprising_patches
423 (
424 comprising_patch_id, patch_driver_id, bug_id,
425 creation_date, last_update_date, last_updated_by, created_by
426 ) values
427 (
428 ad_comprising_patches_s.nextval, l_patch_driver_id, l_bug_id,
429 sysdate, sysdate, G_WHO_USER_ID, G_WHO_USER_ID
430 );
431
432 end;
433
434 end load_comprising_patch;
435
436
437 procedure load_patch_run
438 (
439 p_start_date date,
440 p_at_nm varchar,
441 p_apps_sys_nm varchar,
442 p_cache_appl_top_id boolean,
443 p_chksum number,
444 p_filsiz number,
445 p_filnm varchar,
446 p_pd_src_cd varchar,
447 p_pr_patch_driver_id number,
448 p_exported_from_db varchar,
449 p_maj_v number,
450 p_min_v number,
451 p_tap_v number,
452 p_rapid_install_flag varchar,
453 p_upd_to_maj_v number,
454 p_upd_to_min_v number,
455 p_upd_to_tap_v number,
456 p_patch_top varchar,
457 p_end_date date,
458 p_src_patch_run_id number,
459 p_patch_action_options varchar,
460 p_server_type_admin_flag varchar,
461 p_server_type_forms_flag varchar,
462 p_server_type_node_flag varchar,
463 p_server_type_web_flag varchar,
464 p_source_code varchar,
465 p_success_flag varchar,
466 p_failure_comments varchar,
467 p_record_against_rlse varchar
468 ) is
469 l_at_id number;
470 l_upd_to_rlse_id number;
471 l_patch_run_id number;
472 l_patch_driver_id number;
473 l_rec_this_ptch_on_rlse_id number;
474 begin
475
476 -- code that should run upon the upload of the 1st PATCH_RUN *only*
477
478 if G_CURRENT_RELEASE_ID is null then
479 begin
480 bootstrap_release;
481
482 if p_record_against_rlse is not null then
483 begin
484 select release_id
485 into G_REC_ALL_PTCHS_ON_RLSE_ID
486 from ad_releases
487 where major_version = p_maj_v
488 and minor_version = p_min_v
489 and to_char(major_version)||'.'||to_char(minor_version)||'.'||
490 to_char(tape_version) = p_record_against_rlse;
491 exception when no_data_found then
492 raise_application_error(-20000,
493 'Invalid release. Illegal to request loader to record patches against '''||
494 p_record_against_rlse||'''');
495 end;
496 end if;
497
498 exception when others then
499 -- Reset the pkg globals, else if some are set but others aren't, then
500 -- uploads of subsequent entities is unreliable. Resetting to null
501 -- ensures that this piece of code is re-attempted for the next
502 -- entity, and if it fails there, then it may well keep failing for
503 -- all entities, but thats still better than silently proceeding and
504 -- creating bad data.
505
506 init_rlse_info;
507
508 raise;
509 end;
510 end if;
511
512 if p_cache_appl_top_id and G_APPL_TOP_ID is not null then
513 l_at_id := G_APPL_TOP_ID;
514 else
515 begin
516 select appl_top_id
517 into l_at_id
518 from ad_appl_tops
519 where name = p_at_nm
520 and appl_top_type = 'R' -- only REAL appl-top's
521 and applications_system_name = p_apps_sys_nm;
522
523 if p_cache_appl_top_id then
524 G_APPL_TOP_ID := l_at_id;
525 end if;
526
527 exception when no_data_found then
528 raise_application_error(-20000, 'APPL-TOP not yet identified!');
529 end;
530 end if;
531
532 -- identify the PTCH-DRVR row
533
534 l_patch_driver_id := identify_patch_driver_only(p_pd_src_cd,
535 p_chksum, p_filsiz, p_filnm,
536 p_pr_patch_driver_id, p_exported_from_db);
537
538 if l_patch_driver_id is null then
539 raise_application_error(-20000, 'PTCH-DRVR not yet identified!');
540 end if;
541
542 begin
543 select patch_run_id
544 into l_patch_run_id
545 from ad_patch_runs
546 where start_date = p_start_date
547 and appl_top_id = l_at_id
548 and patch_driver_id = l_patch_driver_id;
549
550 G_PATCH_RUN_ID := l_patch_run_id;
551
552 return;
553
554 exception when no_data_found then
555
556 -- Need to insert a patch-run
557
558 -- Determine the release against which we will record this patch
559 if p_record_against_rlse is null then
560 begin
561 select release_id
562 into l_rec_this_ptch_on_rlse_id
563 from ad_releases
564 where major_version = p_maj_v
565 and minor_version = p_min_v
566 and tape_version = p_tap_v;
567 exception when no_data_found then
568 l_rec_this_ptch_on_rlse_id := G_CURRENT_RELEASE_ID;
569 end;
570 else
571 l_rec_this_ptch_on_rlse_id := G_REC_ALL_PTCHS_ON_RLSE_ID;
572 end if;
573
574 if p_upd_to_maj_v is null then
575 l_upd_to_rlse_id := null;
576 else
577 begin
578 select release_id
579 into l_upd_to_rlse_id
580 from ad_releases
581 where major_version = p_upd_to_maj_v
582 and minor_version = p_upd_to_min_v
583 and tape_version = p_upd_to_tap_v;
584
585 -- Make sure that no other patch already exists that has this value
586 -- in UPDTD-TO-RLSE-ID (there is a unique index). If one exists, then
587 -- that probably is the right one, in which case we wipe out
588 -- l_upd_to_rlse_id. If it doesn't exist, then its safe to continue
589 -- using l_upd_to_rlse_id to populate UPDTD-TO-RLSE-ID for this patch.
590 begin
591 select null
592 into l_upd_to_rlse_id
593 from ad_patch_runs
594 where updated_to_release_id = l_upd_to_rlse_id;
595 exception when no_data_found then
596 null;
597 end;
598 exception when no_data_found then
599 -- It doesn't exist. Wipe it out. (Premise 1: we aren't in the business
600 -- of creating rlse recs here (other than base-rlse and cur-rlse))
601 l_upd_to_rlse_id := null;
602 end;
603 end if;
604
605 insert into ad_patch_runs
606 (
607 patch_run_id,
608 release_id,
609 session_id,
610 rapid_install_flag,
611 updated_to_release_id,
612 patch_top,
613 start_date, end_date,
614 patch_driver_id,
615 patch_action_options,
616 appl_top_id,
617 server_type_admin_flag, server_type_forms_flag,
618 server_type_node_flag, server_type_web_flag,
619 source_code,
620 success_flag, failure_comments,
621 imported_flag, imported_from_db, imported_id, import_session_id,
622 creation_date, last_update_date, last_updated_by, created_by
623 )
624 values
625 (
626 ad_patch_runs_s.nextval,
627 l_rec_this_ptch_on_rlse_id,
628 ad_sessions_s.nextval,
629 p_rapid_install_flag,
630 l_upd_to_rlse_id,
631 p_patch_top,
632 p_start_date, p_end_date,
633 l_patch_driver_id,
634 p_patch_action_options,
635 l_at_id,
636 p_server_type_admin_flag, p_server_type_forms_flag,
637 p_server_type_node_flag, p_server_type_web_flag,
638 p_source_code,
639 p_success_flag, p_failure_comments,
640 'Y', p_exported_from_db, p_src_patch_run_id, G_IMPORT_SESSION_ID,
641 sysdate, sysdate, G_WHO_USER_ID, G_WHO_USER_ID
642 ) returning patch_run_id into G_PATCH_RUN_ID;
643
644 end;
645
646 end load_patch_run;
647
648
649 procedure update_current_view_snapshot
650 (
651 p_use_cache boolean,
652 p_start_date date,
653 p_at_nm varchar,
654 p_apps_sys_nm varchar,
655 p_chksum number,
656 p_filsiz number,
657 p_filnm varchar,
658 p_pd_src_cd varchar,
659 p_pr_patch_driver_id number,
660 p_exported_from_db varchar
661 ) is
662 l_at_id number;
663 l_pr_id number;
664 l_pd_id number;
665 begin
666
667 if not G_SNAPSHOT_MAINT_ALLOWED then
668 return;
669 end if;
670
671 if p_use_cache and G_APPL_TOP_ID is not null and
672 G_PATCH_RUN_ID is not null then
673 l_at_id := G_APPL_TOP_ID;
674 l_pr_id := G_PATCH_RUN_ID;
675 else
676
677 select appl_top_id
678 into l_at_id
679 from ad_appl_tops
680 where name = p_at_nm
681 and appl_top_type = 'R' -- only REAL appl-top's
682 and applications_system_name = p_apps_sys_nm;
683
684 -- identify the PTCH-DRVR row
685
686 l_pd_id := identify_patch_driver_only(p_pd_src_cd,
687 p_chksum, p_filsiz, p_filnm,
688 p_pr_patch_driver_id, p_exported_from_db);
689
690 if l_pd_id is null then
691 raise_application_error(-20000, 'PTCH-DRVR not yet identified!');
692 end if;
693
694 select patch_run_id
695 into l_pr_id
696 from ad_patch_runs
697 where start_date = p_start_date
698 and appl_top_id = l_at_id
699 and patch_driver_id = l_pd_id;
700
701 end if;
702
703 -- call the updt-curr-vw-snapshot API
704
705 delete from ad_ptch_hst_exe_cop_tmp; -- to clear prev ptch-run's info
706
707 AD_FILE_SYS_SNAPSHOTS_PKG.Update_Current_View(l_pr_id, l_at_id);
708
709 end update_current_view_snapshot;
710
711
712 -- package initializer
713 begin
714 initialize;
715 end ad_patch_hist_migr_pkg;