[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_update_global_view boolean,
p_snapshot_id number,
p_global_snapshot_id number,
p_delete_junk_created boolean)
is
L_BUGSTAT_EXPL_ACT_CD constant number := 15; -- bug fix row: EXPLICIT status
l_snapshot_bugs_inserted number := 0;
put_line('Inserting candidate bugs into temp table');
insert into ad_patch_hist_snaps_temp (action_code, bug_id)
select distinct L_BUGSTAT_EXPL_ACT_CD, prb.bug_id
from ad_patch_run_bugs prb, ad_patch_hist_snaps_temp t
where prb.patch_run_id = t.patch_run_id
and t.action_code = p_action_code
and (prb.applied_flag = 'Y' or prb.reason_not_applied = L_ADPBNAAS);
put_line('Inserted '||to_char(sql%rowcount)||
' candidate bugs into temp table');
put_line('Inserting new bugfixes in the curr-vw snapshot');
insert into ad_snapshot_bugfixes
(
snapshot_bug_id,
snapshot_id,
bugfix_id,
bug_status,
success_flag,
creation_date, last_update_date, last_updated_by, created_by
)
select
ad_snapshot_bugfixes_s.nextval,
p_snapshot_id,
t.bug_id,
'EXPLICIT',
'Y',
sysdate, sysdate, 5, 5
from ad_patch_hist_snaps_temp t
where t.action_code = L_BUGSTAT_EXPL_ACT_CD
and not exists (select 'Bug not yet recorded in the curr-vw'
from ad_snapshot_bugfixes sb2
where sb2.snapshot_id = p_snapshot_id
and sb2.bugfix_id = t.bug_id);
l_snapshot_bugs_inserted := sql%rowcount;
put_line('Inserted '||to_char(l_snapshot_bugs_inserted)||
' new bugfixes in the CV');
update ad_snapshot_bugfixes sb
set sb.bug_status = 'EXPLICIT',
sb.success_flag = 'Y',
sb.last_update_date = sysdate,
sb.last_updated_by = 5
where sb.snapshot_id = p_snapshot_id
and sb.bugfix_id in (select t.bug_id
from ad_patch_hist_snaps_temp t
where t.action_code = L_BUGSTAT_EXPL_ACT_CD)
and (sb.bug_status <> 'EXPLICIT' or
sb.success_flag <> 'Y');
put_line('Updated '||to_char(sql%rowcount)|| ' bugfix rows in the CV');
if p_update_global_view then
if G_DEBUG then
put_line('Inserting new bugfixes in the GV');
insert into ad_snapshot_bugfixes
(
snapshot_bug_id,
snapshot_id, bugfix_id,
bug_status, success_flag,
creation_date, last_update_date, last_updated_by, created_by
)
select
ad_snapshot_bugfixes_s.nextval,
p_global_snapshot_id, t.bug_id,
'EXPLICIT', 'Y',
sysdate, sysdate, 5, 5
from ad_patch_hist_snaps_temp t
where t.action_code = L_BUGSTAT_EXPL_ACT_CD
and not exists (select 'Bug not yet recorded in the curr-vw'
from ad_snapshot_bugfixes sb2
where sb2.snapshot_id = p_global_snapshot_id
and sb2.bugfix_id = t.bug_id);
l_snapshot_bugs_inserted := l_snapshot_bugs_inserted + sql%rowcount;
put_line('Inserted '||to_char(sql%rowcount)|| ' new bugfixes in the GV');
update ad_snapshot_bugfixes sb
set sb.bug_status = 'EXPLICIT',
sb.success_flag = 'Y',
sb.last_update_date = sysdate,
sb.last_updated_by = 5,
sb.inconsistent_flag = null
where sb.snapshot_id = p_global_snapshot_id
and sb.bugfix_id in (select t.bug_id
from ad_patch_hist_snaps_temp t
where t.action_code = L_BUGSTAT_EXPL_ACT_CD)
and (sb.bug_status <> 'EXPLICIT' or
sb.success_flag <> 'Y');
put_line('Updated '||to_char(sql%rowcount)|| ' bugfix rows in the GV');
end if; -- End If p_update_global_view
l_snapshot_bugs_inserted,
TRUE,
TRUE,
l_gathered_stats_flag);
if p_delete_junk_created then
delete from ad_patch_hist_snaps_temp
where action_code = L_BUGSTAT_EXPL_ACT_CD;
update ad_snapshots set snapshot_update_date = sysdate
where snapshot_id in (p_snapshot_id, p_global_snapshot_id);
select s.appl_top_id, aat.applications_system_name, aat.name
into l_appl_top_id, l_apps_sys_nm, l_at_name
from ad_snapshots s, ad_appl_tops aat
where s.appl_top_id = aat.appl_top_id
and s.snapshot_id = p_snapshot_id;
select 'x'
into l_dummy
from ad_timestamps
where type = 'BACKFILLED_BUGS_IN_CURRENT_VIEW_SNAPSHOT'
and attribute = l_apps_sys_nm||'*'||l_at_name;
put_line('About to insert PR-IDs into temp table - 2');
insert into ad_patch_hist_snaps_temp
(
patch_run_id, action_code
)
select
patch_run_id, G_PR_ID_ACT_CD2
from ad_patch_runs
where appl_top_id = l_appl_top_id
and patch_run_id not in (select patch_run_id
from ad_patch_hist_snaps_temp
where action_code in (G_PR_ID_ACT_CD,
G_PR_ID_ACT_CD2));
put_line('Inserted '||to_char(sql%rowcount)||' PR-ID rows - 2');
p_update_global_view => FALSE,
p_global_snapshot_id => -1,
p_delete_junk_created => TRUE);
delete from ad_patch_hist_snaps_temp
where action_code = G_PR_ID_ACT_CD2;
ad_file_util.update_timestamp('BACKFILLED_BUGS_IN_CURRENT_VIEW_SNAPSHOT',
l_apps_sys_nm||'*'||l_at_name);
select nvl(count(*), 0)
into p_count_appltops
from ad_appl_tops
where applications_system_name = p_apps_system_name
and active_flag = 'Y';
insert into ad_appl_tops
(
appl_top_id, name, applications_system_name, appl_top_type,
description,
server_type_admin_flag, server_type_forms_flag,
server_type_node_flag, server_type_web_flag,
creation_date, created_by, last_update_date, last_updated_by,
active_flag
)
select
ad_appl_tops_s.nextval, 'GLOBAL', p_apps_system_name, 'G',
'Created for Global View Snapshot',
null, null,
null, null,
sysdate, 5, sysdate, 5,
'N'
from dual
where not exists (select 'Already exists'
from ad_appl_tops t
where t.name = 'GLOBAL'
and t.appl_top_type = 'G'
and t.applications_system_name = p_apps_system_name);
select appl_top_id
into p_global_appl_top_id
from ad_appl_tops
where appl_top_type = 'G'
and name = 'GLOBAL'
and applications_system_name = p_apps_system_name;
select release_id into l_release_id from ad_releases
where to_char(major_version) || '.' ||
to_char(minor_version) || '.' ||
to_char(tape_version) = (select release_name
from fnd_product_groups
where applications_system_name =
p_apps_system_name);
insert into ad_snapshots
(
snapshot_id, release_id,
appl_top_id, snapshot_name,
snapshot_creation_date, snapshot_update_date,
snapshot_type, comments,
ran_snapshot_flag,
creation_date, last_updated_by, created_by, last_update_date
)
select
ad_snapshots_s.nextval, l_release_id,
p_global_appl_top_id, 'GLOBAL_VIEW',
sysdate, sysdate,
'G', 'Created from Current View Snapshots',
'N',
sysdate, 5, 5, sysdate
from dual
where not exists (select 'Already exists'
from ad_snapshots
where appl_top_id = p_global_appl_top_id
and snapshot_type = 'G'
and snapshot_name = 'GLOBAL_VIEW');
select snapshot_id
into p_global_snapshot_id
from ad_snapshots
where snapshot_type = 'G'
and snapshot_name = 'GLOBAL_VIEW'
and appl_top_id = p_global_appl_top_id;
select snapshot_id
bulk collect into l_cv_ids
from ad_snapshots s, ad_appl_tops at1
where s.appl_top_id = at1.appl_top_id
and at1.applications_system_name = p_apps_system_name
and at1.appl_top_type = 'R'
and at1.active_flag = 'Y'
and s.snapshot_type = 'C'
and s.snapshot_name = 'CURRENT_VIEW';
'insert into ad_snapshot_files_temp '||
'( '||
'snapshot_file_id, file_id, update_source_id, update_type, '||
'inconsistent_flag, containing_file_id, file_version_id, '||
'dest_file_id, file_type_flag '||
') '||
'select '||
'snapshot_file_id, file_id, -1, ''P'', ''Y'', containing_file_id, '||
'file_version_id, '||
'dest_file_id, file_type_flag '||
'from (select '||
'sf.snapshot_file_id snapshot_file_id, sf.file_id file_id, '||
'sf.containing_file_id containing_file_id, '||
'sf.file_version_id file_version_id, '||
'sf.dest_file_id dest_file_id, sf.file_type_flag file_type_flag, '||
'row_number() over '||
'(partition by afv.file_id, sf.containing_file_id '||
'order by afv.version_segment1 desc, afv.version_segment2 desc, '||
'afv.version_segment3 desc, afv.version_segment4 desc, '||
'afv.version_segment5 desc, afv.version_segment6 desc, '||
'afv.version_segment7 desc, afv.version_segment8 desc, '||
'afv.version_segment9 desc, afv.version_segment10 desc, '||
'afv.translation_level desc nulls last) as r '||
'from ad_snapshot_files sf, ad_file_versions afv ';
select release_id into l_release_id
from ad_releases
where major_version||'.'||minor_version||'.'||tape_version = p_release_name;
select snapshot_id into l_global_snapshot_id
from ad_snapshots
where snapshot_name ='GLOBAL_VIEW'
and snapshot_type= 'G'
and appl_top_id = (
select appl_top_id
from ad_appl_tops
where appl_top_type = 'G'
and name = 'GLOBAL'
and applications_system_name = p_apps_system_name
);
select 'x' into l_dummy
from ad_snapshots ads
where ads.release_id = l_release_id
and ads.snapshot_id = l_global_snapshot_id;
update ad_snapshots
set snapshot_type = 'O',
snapshot_name = snapshot_name||'-'||snapshot_id,
last_update_date = sysdate
where snapshot_id = l_global_snapshot_id;
select ss.snapshot_id
into l_preseeded_snapshot_id
from ad_snapshots ss, -- seeded
ad_appl_tops ats -- seeded
where ss.appl_top_id = ats.appl_top_id
and ss.snapshot_type = 'B'
and ss.snapshot_name like '*PRESEEDED*'||p_release_name||'%'
and ats.name = '*PRESEEDED*'
and ats.applications_system_name = '*PRESEEDED*'
and ats.appl_top_type = 'S';
insert into ad_snapshot_bugfixes
(
snapshot_bug_id, snapshot_id, bugfix_id, bug_status, success_flag,
creation_date, last_update_date, last_updated_by, created_by,
inconsistent_flag
)
select
ad_snapshot_bugfixes_s.nextval,
l_global_snapshot_id,
sbs.bugfix_id,
sbs.bug_status,
sbs.success_flag,
sysdate, sysdate, 5, 5,
'N' /* inconsistent_flag: set to 'N' */
from ad_snapshot_bugfixes sbs -- seeded
where sbs.snapshot_id = l_preseeded_snapshot_id
and not exists (select /*+ INDEX(SB2 AD_SNAPSHOT_BUGFIXES_U2) */
'Already exists'
from ad_snapshot_bugfixes sb2
where sb2.snapshot_id = l_global_snapshot_id
and sb2.bugfix_id = sbs.bugfix_id);
insert into ad_snapshot_files
(
snapshot_file_id,
snapshot_id, file_id, containing_file_id,
file_version_id,
update_source_id, update_type,
creation_date, last_update_date, last_updated_by, created_by,
appl_top_id, inconsistent_flag,
server_type_admin_flag, server_type_forms_flag,
server_type_node_flag, server_type_web_flag,
dest_file_id, file_type_flag
)
select
ad_snapshot_files_s.nextval,
l_global_snapshot_id, file_id, containing_file_id,
file_version_id,
update_source_id, update_type,
sysdate, sysdate, 5, 5,
p_cur_appl_top_id, 'N',
server_type_admin_flag, server_type_forms_flag,
server_type_node_flag, server_type_web_flag,
dest_file_id, file_type_flag
from ad_snapshot_files sf
where snapshot_id = l_preseeded_snapshot_id
and not exists (select /*+ INDEX(SF2 AD_SNAPSHOT_FILES_U2) */
'Already exists'
from ad_snapshot_files sf2
where sf2.snapshot_id = l_global_snapshot_id
and sf2.file_id = sf.file_id
and nvl(sf2.containing_file_id, -1) =
nvl(sf.containing_file_id, -1));
select 'x' into l_dummy
from ad_snapshots ads, ad_releases adr
where ads.release_id = adr.release_id
and ads.snapshot_id = l_global_snapshot_id
and adr.major_version = ( select distinct major_version from ad_releases
where release_id = l_release_id );
update ad_snapshots
set snapshot_type = 'O',
snapshot_name = snapshot_name||'-'||snapshot_id,
last_update_date = sysdate
where snapshot_id = l_global_snapshot_id;
update ad_snapshots
set release_id = l_release_id,
last_update_date = sysdate
where snapshot_id = l_global_snapshot_id
and release_id <> l_release_id;
insert into ad_snapshot_bugfixes_temp
(
bugfix_id,
bug_status,
success_flag
)
select
bugfix_id,
decode(min(decode(bug_status, 'EXPLICIT', 1, 2)), 1,
'EXPLICIT', 'IMPLICIT'),
decode(min(decode(success_flag, 'N', 1, 2)), 1, 'N', 'Y')
from ad_snapshot_bugfixes
where snapshot_id in (select snapshot_id
from ad_snapshots s, ad_appl_tops at1
where s.appl_top_id = at1.appl_top_id
and at1.applications_system_name = p_apps_system_name
and at1.appl_top_type = 'R'
and s.snapshot_type = 'C'
and s.snapshot_name = 'CURRENT_VIEW')
group by bugfix_id;
insert into ad_snapshot_bugfixes
(
snapshot_bug_id, snapshot_id,
bugfix_id, bug_status, success_flag,
inconsistent_flag,
creation_date, last_update_date, last_updated_by, created_by
)
select
ad_snapshot_bugfixes_s.nextval, l_global_snapshot_id,
t.bugfix_id, t.bug_status, t.success_flag,
decode(l_count_appltops, 1, 'N', null),
sysdate, sysdate, 5, 5
from ad_snapshot_bugfixes_temp t
where not exists (select 'Already exists'
from ad_snapshot_bugfixes sb2
where sb2.snapshot_id = l_global_snapshot_id
and sb2.bugfix_id = t.bugfix_id);
insert into ad_snapshot_files
(
snapshot_file_id, snapshot_id,
file_id, containing_file_id, file_version_id,
dest_file_id, file_type_flag,
appl_top_id,
inconsistent_flag,
update_source_id, update_type,
creation_date, last_update_date, created_by, last_updated_by
)
select
ad_snapshot_files_s.nextval, l_global_snapshot_id,
t.file_id, t.containing_file_id, t.file_version_id,
t.dest_file_id, t.file_type_flag,
decode(l_count_appltops, 1, p_cur_appl_top_id, null),
decode(l_count_appltops, 1, 'N', null),
-1, 'P',
sysdate, sysdate, 5, 5
from
(
select
file_id,
max(containing_file_id) containing_file_id,
max(file_version_id) file_version_id,
max(dest_file_id) dest_file_id,
decode(max(decode(file_type_flag,'M',1,'N',0,2)),
1,'M',0,'N',null) file_type_flag
from
ad_snapshot_files_temp
group by file_id) t
where not exists (select 'Already exists'
from ad_snapshot_files sf2
where sf2.snapshot_id = l_global_snapshot_id
and sf2.file_id = t.file_id
and nvl(sf2.containing_file_id, -1) =
nvl(t.containing_file_id, -1)
);
procedure update_current_view
(p_patch_run_id number,
p_appl_top_id number)
is
l_at_id number;
select pr.appl_top_id
into l_at_id
from ad_patch_runs pr
where pr.patch_run_id = p_patch_run_id;
select pr.appl_top_id
into l_at_id
from ad_patch_runs pr
where pr.patch_run_id = p_patch_run_id
and pr.appl_top_id = p_appl_top_id;
delete from ad_patch_hist_snaps_temp;
put_line('About to insert PR-ID into temp table');
insert into ad_patch_hist_snaps_temp
(
action_code, patch_run_id
)
select G_PR_ID_ACT_CD, pr.patch_run_id
from ad_patch_runs pr
where pr.patch_run_id = p_patch_run_id;
put_line('Inserted '||to_char(sql%rowcount)||' PR-ID rows');
put_line('About to call update_current_view() (the one '||
'that works on many)');
update_current_view('IN_TEMP_TAB', l_at_id, TRUE);
end update_current_view;
procedure update_current_view
(p_patch_runs_spec varchar2,
p_appl_top_id number,
p_caller_is_managing_locks boolean)
is
l_snapshot_id number;
l_snapshot_files_inserted number := 0;
l_deleted_ru_file_ids NUM_TAB;
put_line('In update_current_view(). (the one that '||
'works on many ptch-runs)');
put_line('About to insert PR-IDs into temp table');
insert into ad_patch_hist_snaps_temp
(
patch_run_id, action_code
)
select
patch_run_id, G_PR_ID_ACT_CD
from ad_patch_runs
where appl_top_id = p_appl_top_id
and patch_run_id not in (select patch_run_id
from ad_patch_hist_snaps_temp
where action_code = G_PR_ID_ACT_CD);
put_line('Inserted '||to_char(sql%rowcount)||' PR-ID rows');
select nvl(count(*), 0)
into l_count
from ad_patch_hist_snaps_temp;
select 1
into l_count
from ad_patch_hist_snaps_temp t,
ad_patch_runs pr,
ad_patch_drivers pd
where t.patch_run_id = pr.patch_run_id
and pr.patch_driver_id = pd.patch_driver_id
and pd.driver_type_c_flag = 'Y'
and rownum < 2;
select release_name
into l_curr_rlse_nm
from fnd_product_groups;
select snapshot_id
into l_snapshot_id
from ad_snapshots
where appl_top_id = p_appl_top_id
and snapshot_name = 'CURRENT_VIEW'
and snapshot_type = 'C';
select applications_system_name into l_apps_system_name
from ad_appl_tops where appl_top_id = p_appl_top_id;
select snapshot_id into l_global_snapshot_id
from ad_snapshots s, ad_appl_tops t
where s.snapshot_type = 'G' and
s.snapshot_name = 'GLOBAL_VIEW' and
s.appl_top_id = t.appl_top_id and
t.applications_system_name = l_apps_system_name;
select f.file_id
into l_apps_zip_f_id
from ad_files f
where f.filename = 'apps.zip'
and f.app_short_name = 'AU'
and f.subdir = 'java';
insert into ad_files
(
file_id,
app_short_name, subdir, filename,
creation_date, last_update_date, last_updated_by, created_by
)
values
(
ad_files_s.nextval,
'AU', 'java', 'apps.zip',
sysdate, sysdate, 5, 5
) returning file_id into l_apps_zip_f_id;
insert into ad_files
(
file_id,
app_short_name,
subdir,
filename,
creation_date, last_update_date, last_updated_by, created_by
)
select -- for all the various Unix's
ad_files_s.nextval,
a.application_short_name,
'lib',
'lib'||decode(a.application_short_name, 'SQLGL', 'gl',
'SQLAP', 'ap',
'OFA', 'fa',
'SQLSO', 'so',
lower(a.application_short_name))||
'.a',
sysdate, sysdate, 5, 5
from fnd_application a
where not exists (select
'lib.a already exists'
from ad_files f2
where f2.filename = 'lib'||
decode(a.application_short_name,
'SQLGL', 'gl',
'SQLAP', 'ap',
'OFA', 'fa',
'SQLSO', 'so',
lower(a.application_short_name))||
'.a'
and f2.subdir = 'lib'
and f2.app_short_name = a.application_short_name);
put_line('>>(had to insert '||to_char(sql%rowcount)||' rows)');
insert into ad_files
(
file_id,
app_short_name,
subdir,
filename,
creation_date, last_update_date, last_updated_by, created_by
)
select
ad_files_s.nextval,
a.application_short_name,
'lib',
decode(a.application_short_name, 'SQLGL', 'gl',
'SQLAP', 'ap',
'OFA', 'fa',
'SQLSO', 'so',
lower(a.application_short_name))||
'st.lib',
sysdate, sysdate, 5, 5
from fnd_application a
where not exists (select
'st.lib already exists'
from ad_files f2
where f2.filename = decode(a.application_short_name,
'SQLGL', 'gl',
'SQLAP', 'ap',
'OFA', 'fa',
'SQLSO', 'so',
lower(a.application_short_name))||
'st.lib'
and f2.subdir = 'lib'
and f2.app_short_name = a.application_short_name);
put_line('>>(had to insert '||to_char(sql%rowcount)||' rows)');
put_line('About to insert candidate FILES info into temp table');
This is done in a 2-step process, viz. an INSERT SQL followed by an UPDATE
SQL. The first SQL inserts candidate rows into the temp table, and the
second one flags (updates) C object files that are not archived (main()'s),
that got mistaken as non-main() files in the first SQL.
Moreover, when there is only 1 row in the temp table, then the analytic
function can be done away with and replaced with a grp by instead (since
each FILE_ID partition will have only 1 row, or worst case, multiple
rows with same version). Note that in the vast majority of the cases,
we will have only 1 starter row in the temp table (multiple rows
occur only in the applptch.txt upload case)
The 2nd SQL (the UPDATE) is however the same regardless of whether its
a big/small patch, or whether there are 1/many rows in the temp tables
to start with.
The first SQL (INSERT stmt) is explained below:
Case 1: 1 patch-run
------
The SQL to use is:
insert into ad_patch_hist_snaps_temp
(patch_run_id, action_code, file_id, file_version_id,
app_short_name, filename,
version_segment1, version_segment2,
version_segment3, version_segment4,
version_segment5, version_segment6,
version_segment7, version_segment8,
version_segment9, version_segment10,
translation_level, dest_file_id, file_type_flag)
select
max(pr.patch_run_id),
decode(max(pca.action_code),
'copy',
decode(max(f.subdir),
'lib',
case when (max(f.filename) like '%.o' or
max(f.filename) like '%.obj') then
:clib_cd else
:none_cd end,
:none_cd),
'RU_Insert', :azip_cd,
'RU_Update', :azip_cd,
'RU_Delete', :azip_cd,
:none_cd) act_cd,
f.file_id, max(afv.file_version_id),
max(f.app_short_name), max(f.filename),
max(afv.version_segment1), max(afv.version_segment2),
max(afv.version_segment3), max(afv.version_segment4),
max(afv.version_segment5), max(afv.version_segment6),
max(afv.version_segment7), max(afv.version_segment8),
max(afv.version_segment9), max(afv.version_segment10),
max(afv.translation_level), max(prba.dest_file_id),
max(prba.file_type_flag)
from ad_patch_hist_snaps_temp t, ad_patch_runs pr,
ad_patch_run_bugs prb, ad_patch_run_bug_actions prba,
ad_patch_common_actions pca, ad_file_versions afv, ad_files f
where pr.patch_run_id = t.patch_run_id
and pr.appl_top_id = :at_id
and t.action_code = :pr_id_act_cd
and pr.patch_run_id = prb.patch_run_id
and prb.patch_run_bug_id = prba.patch_run_bug_id
and prba.common_action_id = pca.common_action_id
and prba.file_id = f.file_id
and prba.patch_file_version_id = afv.file_version_id (+)
and prba.executed_flag = 'Y'
and pca.action_code in ('copy', 'forcecopy', 'RU_Update',
'RU_Insert', 'RU_Delete')
group by f.file_id;
insert into ad_patch_hist_snaps_temp
(patch_run_id, action_code, file_id, file_version_id,
app_short_name, filename,
version_segment1, version_segment2,
version_segment3, version_segment4,
version_segment5, version_segment6,
version_segment7, version_segment8,
version_segment9, version_segment10,
translation_level, dest_file_id, file_type_flag)
select
m.patch_run_id, m.act_cd, m.file_id, m.file_version_id,
m.app_short_name, m.filename,
m.version_segment1, m.version_segment2,
m.version_segment3, m.version_segment4,
m.version_segment5, m.version_segment6,
m.version_segment7, m.version_segment8,
m.version_segment9, m.version_segment10,
m.translation_level, prba.dest_file_id, prba.file_type_flag
from
(
select
pr.patch_run_id, afv.file_version_id,
f.file_id, f.app_short_name, f.filename,
afv.version_segment1, afv.version_segment2,
afv.version_segment3, afv.version_segment4,
afv.version_segment5, afv.version_segment6,
afv.version_segment7, afv.version_segment8,
afv.version_segment9, afv.version_segment10,
afv.translation_level,
decode(pca.action_code, 'copy',
decode(f.subdir, 'lib',
case when (f.filename like '%.o' or f.filename like '%.obj')
then :clib_cd
else :none_cd
end,
:none_cd),
'RU_Insert', :azip_cd,
'RU_Update', :azip_cd,
'RU_Delete', :azip_cd,
:none_cd) act_cd,
row_number() over (partition by f.file_id
order by afv.version_segment1 desc, afv.version_segment2 desc,
afv.version_segment3 desc, afv.version_segment4 desc,
afv.version_segment5 desc, afv.version_segment6 desc,
afv.version_segment7 desc, afv.version_segment8 desc,
afv.version_segment9 desc, afv.version_segment10 desc,
afv.translation_level desc nulls last) as r1
from ad_patch_hist_snaps_temp t, ad_patch_runs pr,
ad_patch_run_bugs prb, ad_patch_run_bug_actions prba,
ad_patch_common_actions pca, ad_file_versions afv, ad_files f
where pr.patch_run_id = t.patch_run_id
and pr.appl_top_id = :at_id
and t.action_code = :pr_id_act_cd
and pr.patch_run_id = prb.patch_run_id
and prb.patch_run_bug_id = prba.patch_run_bug_id
and prba.common_action_id = pca.common_action_id
and prba.file_id = f.file_id
and prba.patch_file_version_id = afv.file_version_id (+)
and prba.executed_flag = 'Y'
and pca.action_code in ('copy', 'forcecopy', 'RU_Update',
'RU_Insert', 'RU_Delete')
) m
where m.r1 = 1
Next, the second SQL (UPDATE stmt) is explained below:
update
ad_patch_hist_snaps_temp t
set t.action_code = :none_cd
where t.action_code = :clib_cd
and not exists (select
'libin action exists for this .o (ie. its archived)'
from ad_patch_run_bug_actions prba,
ad_patch_run_bugs prb,
ad_patch_common_actions pca
where prba.file_id = t.file_id
and prba.patch_run_bug_id = prb.patch_run_bug_id
and prb.patch_run_id = t.patch_run_id
and pca.common_action_id = prba.common_action_id
and pca.action_code = 'libin')
*/
l_ins_stmt1 :=
'insert into ad_patch_hist_snaps_temp '||
'(patch_run_id, action_code, file_id, file_version_id, '||
'app_short_name, filename, '||
'version_segment1, version_segment2, '||
'version_segment3, version_segment4, '||
'version_segment5, version_segment6, '||
'version_segment7, version_segment8, '||
'version_segment9, version_segment10, '||
'translation_level, dest_file_id, file_type_flag) '||
'select ';
'and pca.action_code in (''copy'', ''forcecopy'', ''RU_Update'', '||
'''RU_Insert'', ''RU_Delete'') ';
'''RU_Insert'', '||
':azip_cd, '||
'''RU_Update'', '||
':azip_cd, '||
'''RU_Delete'', '||
':azip_cd, '||
':none_cd) act_cd, '||
'f.file_id, max(afv.file_version_id), '||
'max(f.app_short_name), max(f.filename), '||
'max(afv.version_segment1), max(afv.version_segment2), '||
'max(afv.version_segment3), max(afv.version_segment4), '||
'max(afv.version_segment5), max(afv.version_segment6), '||
'max(afv.version_segment7), max(afv.version_segment8), '||
'max(afv.version_segment9), max(afv.version_segment10), '||
'max(afv.translation_level), max(prba.dest_file_id), '||
'max(prba.file_type_flag) ';
'select ';
'''RU_Insert'', :azip_cd, '||
'''RU_Update'', :azip_cd, '||
'''RU_Delete'', :azip_cd, '||
':none_cd) act_cd, '||
'row_number() over (partition by f.file_id '||
'order by afv.version_segment1 desc, afv.version_segment2 desc, '||
'afv.version_segment3 desc, afv.version_segment4 desc, '||
'afv.version_segment5 desc, afv.version_segment6 desc, '||
'afv.version_segment7 desc, afv.version_segment8 desc, '||
'afv.version_segment9 desc, afv.version_segment10 desc, '||
'afv.translation_level desc nulls last) as r1, '||
'prba.dest_file_id, prba.file_type_flag ';
put_line('@@FULL INSERT STMT:@@');
put_line('Inserted '||to_char(sql%rowcount)||
' candidate FILES rows into temp table');
update
ad_patch_hist_snaps_temp t
set t.action_code = L_ARCH_NONE_ACT_CD
where t.action_code = L_ARCH_CLIB_ACT_CD
and not exists (select
'libin action exists for this .o (ie. its archived)'
from ad_patch_run_bug_actions prba,
ad_patch_run_bugs prb,
ad_patch_common_actions pca
where prba.file_id = t.file_id
and prba.patch_run_bug_id = prb.patch_run_bug_id
and prb.patch_run_id = t.patch_run_id
and pca.common_action_id = prba.common_action_id
and pca.action_code = 'libin');
update
ad_patch_hist_snaps_temp t
set t.clib_arch_file_id =
(
select f.file_id
from ad_files f
where f.app_short_name = translate(t.app_short_name, 'A#', 'A')
and f.subdir = 'lib'
and f.filename = decode(
lower(substr(t.filename, instr(t.filename,'.',-1),
length(t.filename) -
instr(t.filename,'.',-1) + 1)),
'.o', 'lib', null) ||
decode(translate(t.app_short_name, 'A#', 'A'),
'SQLGL', 'gl',
'SQLAP', 'ap',
'OFA', 'fa',
'SQLSO', 'so',
lower(translate(t.app_short_name, 'A#', 'A'))) ||
decode(
lower(substr(t.filename, instr(t.filename,'.',-1),
length(t.filename) -
instr(t.filename,'.',-1) + 1)),
'.o', '.a', 'st.lib')
)
where t.action_code = L_ARCH_CLIB_ACT_CD;
update ad_patch_hist_snaps_temp t
set t.irep_gathered_flag = 'N';
put_line('Inserting new files in the curr-vw snapshot');
insert into ad_snapshot_files
(
snapshot_file_id,
snapshot_id,
file_id,
containing_file_id,
file_version_id,
dest_file_id,
file_type_flag,
update_source_id,
update_type,
creation_date, last_update_date,
last_updated_by, created_by,
appl_top_id,
irep_gathered_flag,
last_patched_date
)
select
ad_snapshot_files_s.nextval,
l_snapshot_id,
t.file_id,
decode(t.action_code, L_ARCH_CLIB_ACT_CD, t.clib_arch_file_id,
L_ARCH_AZIP_ACT_CD, l_apps_zip_f_id,
null),
t.file_version_id,
t.dest_file_id,
t.file_type_flag,
t.patch_run_id,
'P',
sysdate, sysdate,
5, 5, p_appl_top_id,
t.irep_gathered_flag,
sysdate
from ad_patch_hist_snaps_temp t
where t.action_code in (L_ARCH_NONE_ACT_CD, L_ARCH_CLIB_ACT_CD,
L_ARCH_AZIP_ACT_CD)
and not exists (select 'Already exists'
from ad_snapshot_files sf
where sf.snapshot_id = l_snapshot_id
and sf.file_id = t.file_id
and ((sf.containing_file_id is null and
t.action_code = L_ARCH_NONE_ACT_CD)
or
(sf.containing_file_id = decode(t.action_code,
L_ARCH_CLIB_ACT_CD, t.clib_arch_file_id,
L_ARCH_AZIP_ACT_CD, l_apps_zip_f_id))
)
);
l_snapshot_files_inserted := sql%rowcount;
put_line('Inserted '||to_char(l_snapshot_files_inserted)||
' new files in the curr-vw snapshot');
select count(*) into l_snapshot_count
from ad_snapshots s, ad_appl_tops t
where s.snapshot_type = 'C' and
s.appl_top_id = t.appl_top_id and
t.applications_system_name = l_apps_system_name;
raise_application_error(-20000, 'Error: update_current_view: ' ||
'Currrent view snapshot doesn''t exist.');
/* insert new files into Global current view snapshot */
insert into ad_snapshot_files
(
snapshot_file_id, snapshot_id, file_id, containing_file_id,
file_version_id, dest_file_id, file_type_flag,
update_source_id, update_type,
creation_date, last_update_date, last_updated_by,
created_by, appl_top_id, inconsistent_flag
)
select
ad_snapshot_files_s.nextval,
l_global_snapshot_id, -- Global Snapshot ID here
t.file_id,
decode(t.action_code, L_ARCH_CLIB_ACT_CD, t.clib_arch_file_id,
L_ARCH_AZIP_ACT_CD, l_apps_zip_f_id,
null),
t.file_version_id,
t.dest_file_id,
t.file_type_flag,
t.patch_run_id,
'P',
sysdate, sysdate,
5, 5, p_appl_top_id,
l_inconsistent_flag
from ad_patch_hist_snaps_temp t
where
t.action_code in (L_ARCH_NONE_ACT_CD, L_ARCH_CLIB_ACT_CD,
L_ARCH_AZIP_ACT_CD)
and not exists (select 'Already exists'
from ad_snapshot_files sf
where sf.snapshot_id = l_global_snapshot_id
and sf.file_id = t.file_id
and ((sf.containing_file_id is null and
t.action_code = L_ARCH_NONE_ACT_CD)
or
(sf.containing_file_id = decode(t.action_code,
L_ARCH_CLIB_ACT_CD, t.clib_arch_file_id,
L_ARCH_AZIP_ACT_CD, l_apps_zip_f_id))
)
);
l_snapshot_files_inserted := l_snapshot_files_inserted + sql%rowcount;
put_line('Inserted '||to_char(sql%rowcount)||
' new files in the Global View snapshot');
l_snapshot_files_inserted,
TRUE,
TRUE,
l_gathered_stats_flag);
update ad_snapshot_files sf
set
(sf.file_version_id, sf.update_source_id,
sf.dest_file_id, sf.file_type_flag, sf.irep_gathered_flag) =
(select t.file_version_id, t.patch_run_id,
t.dest_file_id, t.file_type_flag, t.irep_gathered_flag
from ad_patch_hist_snaps_temp t
where t.file_id = sf.file_id),
sf.update_type = 'P',
sf.last_update_date = sysdate,
sf.last_updated_by = 5,
sf.last_patched_date = sysdate
where sf.snapshot_id = l_snapshot_id
and sf.file_id in (select t2.file_id
from ad_patch_hist_snaps_temp t2
where t2.action_code in (L_ARCH_NONE_ACT_CD,
L_ARCH_CLIB_ACT_CD, L_ARCH_AZIP_ACT_CD))
and exists
(
select 'File exists in curr-vw with lower version'
from ad_patch_hist_snaps_temp t, ad_file_versions fv_old
where sf.file_id = t.file_id
and t.file_version_id = fv_old.file_version_id (+)
and sf.file_version_id = fv_old.file_version_id (+)
-- Update only if patch version is higher (code copied from adfilutb.pls)
and (((fv_old.file_version_id is null)
or
('Y' = decode(
sign(nvl(t.version_segment1,0) - nvl(fv_old.version_segment1,0)),
-1, null, 1, 'Y', decode(
sign(nvl(t.version_segment2,0) - nvl(fv_old.version_segment2,0)),
-1, null, 1, 'Y', decode(
sign(nvl(t.version_segment3,0) - nvl(fv_old.version_segment3,0)),
-1, null, 1, 'Y', decode(
sign(nvl(t.version_segment4,0) - nvl(fv_old.version_segment4,0)),
-1, null, 1, 'Y', decode(
sign(nvl(t.version_segment5,0) - nvl(fv_old.version_segment5,0)),
-1, null, 1, 'Y', decode(
sign(nvl(t.version_segment6,0) - nvl(fv_old.version_segment6,0)),
-1, null, 1, 'Y', decode(
sign(nvl(t.version_segment7,0) - nvl(fv_old.version_segment7,0)),
-1, null, 1, 'Y', decode(
sign(nvl(t.version_segment8,0) - nvl(fv_old.version_segment8,0)),
-1, null, 1, 'Y', decode(
sign(nvl(t.version_segment9,0) - nvl(fv_old.version_segment9,0)),
-1, null, 1, 'Y', decode(
sign(nvl(t.version_segment10,0) - nvl(fv_old.version_segment10,0)),
-1, null, 1, 'Y', decode(
sign(t.translation_level - fv_old.translation_level),
-1, null, 1, 'Y', null)))))))))))
)
)
or (nvl(sf.dest_file_id, -1) <> nvl(t.dest_file_id, -1))
or (nvl(sf.file_type_flag, 'X') <> nvl(t.file_type_flag, 'X'))
)
and ((sf.containing_file_id is null and
t.action_code = L_ARCH_NONE_ACT_CD)
or
(sf.containing_file_id = decode(t.action_code,
L_ARCH_CLIB_ACT_CD, t.clib_arch_file_id,
L_ARCH_AZIP_ACT_CD, l_apps_zip_f_id))
)
);
put_line('Updated '||to_char(sql%rowcount)||
' files in the curr-vw snapshot');
action_code is RU_Delete */
/* Bug#3483080/3419891:
We KNOW that RU_Delete actions will be very, very few.
Therefore our approach is:
- Hold these temporarily in a collection type
- While fetching the data, give the hints exactly as given
below (no more, no less) worked great in volume testing
db (gsiappkm).
- Issue a bulk-bind DELETE using the collection type
*/
select /*+ ordered use_nl(prba, prb, pr, t)
index(pca ad_patch_common_actions_n1) */
distinct prba.file_id
bulk collect into l_deleted_ru_file_ids
from ad_patch_common_actions pca,
ad_patch_run_bug_actions prba,
ad_patch_run_bugs prb,
ad_patch_runs pr,
ad_patch_hist_snaps_temp t
where pr.patch_run_id = t.patch_run_id and
pr.appl_top_id = p_appl_top_id and
t.action_code = G_PR_ID_ACT_CD and
pr.patch_run_id = prb.patch_run_id and
prba.patch_run_bug_id = prb.patch_run_bug_id and
prba.common_action_id = pca.common_action_id and
pca.action_code = 'RU_Delete';
if nvl(l_deleted_ru_file_ids.last, 0) > 0 then
forall i in l_deleted_ru_file_ids.first..l_deleted_ru_file_ids.last
delete from ad_snapshot_files
where snapshot_id = l_snapshot_id and
file_id = l_deleted_ru_file_ids(i);
put_line('Deleted '||to_char(sql%rowcount)||
' class files which are removed from apps.zip');
select file_id, dest_file_id, file_type_flag,
file_version_id, containing_file_id,
inconsistent_flag
from ad_snapshot_files_temp;
update /*+ INDEX(SF AD_SNAPSHOT_FILES_U2) */
ad_snapshot_files sf
set sf.last_update_date = sysdate,
sf.last_updated_by = 5,
sf.file_version_id = file_version_id_list(j),
sf.update_source_id = -1,
sf.update_type = 'P',
sf.appl_top_id = null,
sf.inconsistent_flag = null,
sf.dest_file_id = dest_file_id_list(j),
sf.file_type_flag = file_type_flag_list(j)
where
sf.snapshot_id = l_global_snapshot_id and
sf.file_id = file_id_list(j) and
nvl(sf.containing_file_id, -1) =
nvl(containing_file_id_list(j), -1) and
-- rkagrawa: deliberately putting -2 here since for versionless
-- files, we want the update_type, update_source_id, etc to be
-- updated each time the file is patched
(nvl(sf.file_version_id, -1) <> nvl(file_version_id_list(j), -2) or
sf.inconsistent_flag <> inconsistent_flag_list(j) or
nvl(sf.dest_file_id, -1) <> nvl(dest_file_id_list(j), -1) or
nvl(sf.file_type_flag, -1) <> nvl(file_type_flag_list(j), -1))
;
put_line('Updated ' || to_char(sql%rowcount) ||
' in Global view snapshot');
/* Delete files from Global view snapshot */
if nvl(l_deleted_ru_file_ids.last, 0) > 0 then
forall i in l_deleted_ru_file_ids.first..l_deleted_ru_file_ids.last
delete from ad_snapshot_files sf
where sf.snapshot_id = l_global_snapshot_id and
sf.file_id = l_deleted_ru_file_ids(i)
and not exists
(
select 'Exists in the curr vw of some appltop'
from ad_snapshot_files sf1
where sf1.file_id = l_deleted_ru_file_ids(i)
and nvl(sf1.containing_file_id, -1) = nvl(sf.containing_file_id, -1)
and sf1.snapshot_id in (
select s.snapshot_id
from ad_snapshots s, ad_appl_tops a
where s.snapshot_type = 'C'
and s.appl_top_id = a.appl_top_id
and a.applications_system_name = l_apps_system_name
and nvl(a.active_flag,'Y') = 'Y'
)
);
put_line('Deleted '||to_char(sql%rowcount)||
' files from Global View snapshot');
update ad_snapshots set last_update_date = sysdate,
snapshot_update_date = sysdate
where snapshot_id in (l_snapshot_id, l_global_snapshot_id);
delete from ad_patch_hist_snaps_temp
where action_code <> G_PR_ID_ACT_CD;
p_update_global_view => TRUE,
p_global_snapshot_id => l_global_snapshot_id,
p_delete_junk_created => FALSE); -- let it be, can help debug.
end update_current_view;
l_snapshot_bugs_inserted number := 0;
l_snapshot_files_inserted number := 0;
select nvl(applications_system_name, '1 UNKNOWN 1')
into l_prim_apps_sys_nm
from fnd_product_groups;
select to_char(major_version)||'.'||
to_char(minor_version)||'.'||
to_char(tape_version)
into l_rlse_nm
from ad_releases
where release_id = p_release_id;
select nvl(applications_system_name, '1 UNKNOWN 1'), name
into l_apps_sys_nm, l_at_name
from ad_appl_tops
where appl_top_id = p_appl_top_id
and appl_top_type = 'R';
select snapshot_id
into l_curr_vw_snapshot_id
from ad_snapshots
where appl_top_id = p_appl_top_id
and snapshot_name = 'CURRENT_VIEW'
and snapshot_type = 'C';
select ss.snapshot_id, ss.release_id
into l_preseeded_snapshot_id, l_preseeded_rlse_id
from ad_snapshots ss, -- seeded
ad_appl_tops ats -- seeded
where ss.appl_top_id = ats.appl_top_id
and ss.snapshot_type = 'B'
and ss.snapshot_name like '*PRESEEDED*'||l_rlse_nm||'%'
and ats.name = '*PRESEEDED*'
and ats.applications_system_name = '*PRESEEDED*'
and ats.appl_top_type = 'S'
and ss.release_id = p_release_id;
select 'x' into l_dummy
from ad_snapshots ads
where nvl(ran_snapshot_flag, 'N') = 'Y'
and ads.release_id = p_release_id
and ads.snapshot_id = l_curr_vw_snapshot_id;
update ad_snapshots
set snapshot_type = 'O',
snapshot_name = snapshot_name||'-'||snapshot_id,
last_update_date = sysdate
where snapshot_id = l_curr_vw_snapshot_id;
select ad_snapshots_s.nextval into l_curr_vw_snapshot_id from dual;
insert into ad_snapshots
(
snapshot_id,
release_id, appl_top_id,
snapshot_name, comments,
snapshot_creation_date, snapshot_update_date,
snapshot_type, ran_snapshot_flag,
creation_date, last_update_date, last_updated_by, created_by
)
select
l_curr_vw_snapshot_id,
p_release_id, p_appl_top_id,
'CURRENT_VIEW', 'Current View',
sysdate, sysdate,
'C', 'N',
sysdate, sysdate, 5, 5
from dual;
insert into ad_snapshot_bugfixes
(
snapshot_bug_id,
snapshot_id, bugfix_id,
bug_status, success_flag,
creation_date, last_update_date, last_updated_by, created_by
)
select
ad_snapshot_bugfixes_s.nextval,
l_curr_vw_snapshot_id, sbs.bugfix_id,
sbs.bug_status, sbs.success_flag,
sysdate, sysdate, 5, 5
from ad_snapshot_bugfixes sbs -- seeded
where sbs.snapshot_id = l_preseeded_snapshot_id
and not exists (select /*+ INDEX(SB2 AD_SNAPSHOT_BUGFIXES_U2) */
'Already exists'
from ad_snapshot_bugfixes sb2
where sb2.snapshot_id = l_curr_vw_snapshot_id
and sb2.bugfix_id = sbs.bugfix_id);
l_snapshot_bugs_inserted := sql%rowcount;
insert into ad_snapshot_files
(
snapshot_file_id,
snapshot_id, file_id, containing_file_id,
file_version_id,
update_source_id, update_type,
creation_date, last_update_date, last_updated_by, created_by,
dest_file_id, file_type_flag
)
select
ad_snapshot_files_s.nextval,
l_curr_vw_snapshot_id, file_id, containing_file_id,
file_version_id,
update_source_id, update_type,
sysdate, sysdate, 5, 5,
dest_file_id, file_type_flag
from ad_snapshot_files sf
where sf.snapshot_id = l_preseeded_snapshot_id
-- Added for bug 3947949
-- and ((sf.server_type_admin_flag = l_server_type_admin_flag
-- and l_server_type_admin_flag = 'Y') or
-- (sf.server_type_forms_flag = l_server_type_forms_flag
-- and l_server_type_forms_flag = 'Y') or
-- (sf.server_type_node_flag = l_server_type_node_flag
-- and l_server_type_node_flag = 'Y') or
-- (sf.server_type_web_flag = l_server_type_web_flag
-- and l_server_type_web_flag = 'Y')
-- )
and not exists (select /*+ INDEX(SF2 AD_SNAPSHOT_FILES_U2) */
'Already exists' from ad_snapshot_files sf2
where sf2.snapshot_id = l_curr_vw_snapshot_id
and sf2.file_id = sf.file_id
and nvl(sf2.containing_file_id, -1) =
nvl(sf.containing_file_id, -1));
l_snapshot_files_inserted := sql%rowcount;
if l_snapshot_files_inserted > 0 then
/* set ran_snapshot_flag only incase the *PRESEEDED* snapshot
* has files information.
*/
update ad_snapshots
set ran_snapshot_flag = 'Y',
last_update_date = sysdate
where snapshot_id = l_curr_vw_snapshot_id;
select 'x' into l_dummy
from ad_snapshots ads, ad_releases adr
where ads.release_id = adr.release_id
and ads.snapshot_id = l_curr_vw_snapshot_id
and adr.major_version = ( select distinct major_version from ad_releases
where release_id = p_release_id );
update ad_snapshots
set snapshot_type = 'O',
last_update_date = sysdate,
snapshot_name = snapshot_name||'-'||snapshot_id
where snapshot_id = l_curr_vw_snapshot_id;
select ad_snapshots_s.nextval into l_curr_vw_snapshot_id from dual;
insert into ad_snapshots
(
snapshot_id,
release_id, appl_top_id,
snapshot_name, comments,
snapshot_creation_date, snapshot_update_date,
snapshot_type, ran_snapshot_flag,
creation_date, last_update_date, last_updated_by, created_by
)
select
l_curr_vw_snapshot_id,
p_release_id, p_appl_top_id,
'CURRENT_VIEW', 'Current View',
sysdate, sysdate,
'C', 'N',
sysdate, sysdate, 5, 5
from dual;
update ad_snapshots
set release_id = p_release_id,
last_update_date = sysdate
where snapshot_id = l_curr_vw_snapshot_id
and release_id <> p_release_id;
l_snapshot_bugs_inserted + l_snapshot_files_inserted,
TRUE,
TRUE,
l_gathered_stats_flag
);
procedure update_rel_name(rel_name varchar2) is
G_CURRENT_RELEASE varchar2(50);
select release_name
into G_CURRENT_RELEASE
from fnd_product_groups;
update fnd_product_groups
set release_name = rel_name,
last_update_date = sysdate,
last_updated_by = 1
where product_group_id = 1;
end update_rel_name;