[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
' update ad_check_file_temp t '||
'set t.file_id = (select f.file_id '||
'from ad_files f '||
'where f.app_short_name = t.app_short_name '||
'and f.subdir = t.subdir '||
'and f.filename = t.filename), '||
't.junk = null '||
'where nvl(t.active_flag,''N'') = ''Y'';):(';
update ad_check_file_temp t
set t.file_id =
(select /*+ INDEX(F AD_FILES_U2) */ f.file_id
from ad_files f
where f.app_short_name = t.app_short_name
and f.subdir = t.subdir
and f.filename = t.filename),
t.junk = null
where nvl(t.active_flag,'N') = 'Y';
'insert into ad_files '||
'(file_id, app_short_name, subdir, filename, '||
'creation_date, created_by, last_update_date, '||
'last_updated_by) select ad_files_s.nextval, '||
'temp.asn, temp.dir, temp.fname, temp.edate, 5, '||
'temp.edate, 5 '||
'from (select distinct t.app_short_name asn, '||
't.subdir dir, t.filename fname, '||
't.effective_date edate from '||
'ad_check_file_temp t where t.file_id is null '||
' and nvl(t.active_flag,''N'') = ''Y'') temp;):(';
insert into ad_files
(file_id, app_short_name, subdir, filename,
creation_date, created_by, last_update_date, last_updated_by)
select ad_files_s.nextval,
temp.asn, temp.dir, temp.fname,
temp.edate, 5, temp.edate, 5
from
(select distinct
t.app_short_name asn,
t.subdir dir,
t.filename fname,
t.effective_date edate
from ad_check_file_temp t
where t.file_id is null
and nvl(t.active_flag,'N') = 'Y') temp;
'update ad_check_file_temp t set t.file_id = '||
'(select f.file_id from ad_files f '||
'where f.app_short_name = t.app_short_name '||
'and f.subdir = t.subdir '||
'and f.filename = t.filename) '||
'where t.file_id is null '||
'and nvl(t.active_flag,''N'') = ''Y'';):(';
update ad_check_file_temp t
set t.file_id =
(select /*+ INDEX(F AD_FILES_U2) */ f.file_id
from ad_files f
where f.app_short_name = t.app_short_name
and f.subdir = t.subdir
and f.filename = t.filename)
where t.file_id is null
and nvl(t.active_flag,'N') = 'Y';
' update ad_check_file_temp t '||
'set t.dest_file_id = (select f.file_id '||
'from ad_files f '||
'where f.app_short_name = t.dest_apps_short_name '||
'and f.subdir = t.dest_subdir '||
'and f.filename = t.dest_filename) '||
'where nvl(t.active_flag,''N'') = ''Y'';):(';
update ad_check_file_temp t
set t.dest_file_id =
(select /*+ INDEX(F AD_FILES_U2) */ f.file_id
from ad_files f
where f.app_short_name = t.dest_apps_short_name
and f.subdir = t.dest_subdir
and f.filename = t.dest_filename)
where nvl(t.active_flag,'N') = 'Y';
'insert into ad_files '||
'(file_id, app_short_name, subdir, filename, '||
'creation_date, created_by, last_update_date, '||
'last_updated_by) select ad_files_s.nextval, '||
'temp.asn, temp.dir, temp.fname, temp.edate, 5, '||
'temp.edate, 5 '||
'from (select distinct t.dest_apps_short_name asn, '||
't.dest_subdir dir, t.dest_filename fname, '||
't.effective_date edate from '||
'ad_check_file_temp t where t.dest_file_id is null '||
' and t.dest_filename is not null '||
' and nvl(t.active_flag,''N'') = ''Y'') temp;):(';
insert into ad_files
(file_id, app_short_name, subdir, filename,
creation_date, created_by, last_update_date, last_updated_by)
select ad_files_s.nextval,
temp.asn, temp.dir, temp.fname,
temp.edate, 5, temp.edate, 5
from
(select distinct
t.dest_apps_short_name asn,
t.dest_subdir dir,
t.dest_filename fname,
t.effective_date edate
from ad_check_file_temp t
where t.dest_file_id is null
and t.dest_filename is not null
and t.dest_filename <> 'none'
and nvl(t.active_flag,'N') = 'Y') temp;
'update ad_check_file_temp t set t.dest_file_id ='||
'(select f.file_id from ad_files f '||
'where f.app_short_name = t.dest_apps_short_name'||
'and f.subdir = t.dest_subdir '||
'and f.filename = t.dest_filename) '||
'where t.dest_file_id is null '||
'and t.dest_filename is not null '||
'and nvl(t.active_flag,''N'') = ''Y'';):(';
update ad_check_file_temp t
set t.dest_file_id =
(select /*+ INDEX(F AD_FILES_U2) */ f.file_id
from ad_files f
where f.app_short_name = t.dest_apps_short_name
and f.subdir = t.dest_subdir
and f.filename = t.dest_filename)
where t.dest_file_id is null
and t.dest_filename is not null
and t.dest_filename <> 'none'
and nvl(t.active_flag,'N') = 'Y';
'update ad_check_file_temp t '||
'set t.file_version_id = '||
'(select fv.file_version_id '||
'from ad_file_versions fv '||
'where fv.file_id = t.file_id '||
'and fv.version = t.manifest_vers '||
'and fv.translation_level = '||
't.translation_level) '||
'where nvl(t.active_flag,''N'') = ''Y'' '||
'and lower(t.manifest_vers)<>''none'';):(';
update ad_check_file_temp t
set t.file_version_id =
(select /*+ INDEX(FV AD_FILE_VERSIONS_U2) */ fv.file_version_id
from ad_file_versions fv
where fv.file_id = t.file_id
and fv.version = t.manifest_vers
and fv.translation_level = t.translation_level)
where nvl(t.active_flag,'N') = 'Y'
and lower(t.manifest_vers)<>'none';
'insert into ad_file_versions '||
'(file_version_id, file_id, version, '||
'translation_level, '||
'version_segment1, version_segment2, '||
'version_segment3, version_segment4, '||
'version_segment5, version_segment6, '||
'version_segment7, version_segment8, '||
'version_segment9, version_segment10, '||
'creation_date, created_by, last_update_date, '||
'last_updated_by) select '||
'ad_file_versions_s.nextval, '||
'temp.f_id, temp.vers, temp.trans_level, '||
'temp.vs1, temp.vs2, temp.vs3, temp.vs4, '||
'temp.vs5, temp.vs6, temp.vs7, temp.vs8, '||
'temp.vs9, temp.vs10, temp.edate, 5, '||
'temp.edate, 5 from (select distinct '||
't.file_id f_id, t.manifest_vers vers, '||
't.translation_level trans_level,....);):(';
insert into ad_file_versions
(file_version_id, file_id, version, translation_level,
version_segment1, version_segment2, version_segment3,
version_segment4, version_segment5, version_segment6,
version_segment7, version_segment8, version_segment9,
version_segment10,
creation_date, created_by, last_update_date, last_updated_by)
select ad_file_versions_s.nextval,
temp.f_id, temp.vers, temp.trans_level,
temp.vs1, temp.vs2, temp.vs3, temp.vs4, temp.vs5,
temp.vs6, temp.vs7, temp.vs8, temp.vs9, temp.vs10,
temp.edate, 5, temp.edate, 5
from
(select distinct
t.file_id f_id,
t.manifest_vers vers,
t.translation_level trans_level,
decode( instr(t.manifest_vers||'.','.',1,1), 0, 0,
to_number(substr(t.manifest_vers||'.',
1,
( instr(t.manifest_vers||'.','.',1,1)-1)))) vs1,
decode( instr(t.manifest_vers||'.','.',1,2), 0, 0,
to_number(substr(t.manifest_vers||'.',
(instr(t.manifest_vers||'.','.',1,1)+1),
( (instr(t.manifest_vers||'.','.',1,2))
- (instr(t.manifest_vers||'.','.',1,1)+1)) ))) vs2,
decode( instr(t.manifest_vers||'.','.',1,3), 0, 0,
to_number(substr(t.manifest_vers||'.',
(instr(t.manifest_vers||'.','.',1,2)+1),
( (instr(t.manifest_vers||'.','.',1,3))
- (instr(t.manifest_vers||'.','.',1,2)+1)) ))) vs3,
decode( instr(t.manifest_vers||'.','.',1,4), 0, 0,
to_number(substr(t.manifest_vers||'.',
(instr(t.manifest_vers||'.','.',1,3)+1),
( (instr(t.manifest_vers||'.','.',1,4))
- (instr(t.manifest_vers||'.','.',1,3)+1)) ))) vs4,
decode( instr(t.manifest_vers||'.','.',1,5), 0, 0,
to_number(substr(t.manifest_vers||'.',
(instr(t.manifest_vers||'.','.',1,4)+1),
( (instr(t.manifest_vers||'.','.',1,5))
- (instr(t.manifest_vers||'.','.',1,4)+1)) ))) vs5,
decode( instr(t.manifest_vers||'.','.',1,6), 0, 0,
to_number(substr(t.manifest_vers||'.',
(instr(t.manifest_vers||'.','.',1,5)+1),
( (instr(t.manifest_vers||'.','.',1,6))
- (instr(t.manifest_vers||'.','.',1,5)+1)) ))) vs6,
decode( instr(t.manifest_vers||'.','.',1,7), 0, 0,
to_number(substr(t.manifest_vers||'.',
(instr(t.manifest_vers||'.','.',1,6)+1),
( (instr(t.manifest_vers||'.','.',1,7))
- (instr(t.manifest_vers||'.','.',1,6)+1)) ))) vs7,
decode( instr(t.manifest_vers||'.','.',1,8), 0, 0,
to_number(substr(t.manifest_vers||'.',
(instr(t.manifest_vers||'.','.',1,7)+1),
( (instr(t.manifest_vers||'.','.',1,8))
- (instr(t.manifest_vers||'.','.',1,7)+1)) ))) vs8,
decode( instr(t.manifest_vers||'.','.',1,9), 0, 0,
to_number(substr(t.manifest_vers||'.',
(instr(t.manifest_vers||'.','.',1,8)+1),
( (instr(t.manifest_vers||'.','.',1,9))
- (instr(t.manifest_vers||'.','.',1,8)+1)) ))) vs9,
decode( instr(t.manifest_vers||'.','.',1,10), 0, 0,
to_number(substr(t.manifest_vers||'.',
(instr(t.manifest_vers||'.','.',1,9)+1),
( (instr(t.manifest_vers||'.','.',1,10))
- (instr(t.manifest_vers||'.','.',1,9)+1)) ))) vs10,
t.effective_date edate
from ad_check_file_temp t
where t.file_version_id is null
and lower(t.manifest_vers) <> 'none'
and nvl(t.active_flag,'N') = 'Y'
) temp;
'update ad_check_file_temp t '||
'set t.file_version_id = '||
'(select fv.file_version_id '||
'from ad_file_versions fv '||
'where fv.file_id = t.file_id '||
'and fv.version = t.manifest_vers '||
'and fv.translation_level = t.translation_level)'||
'where t.file_version_id is null '||
'and nvl(t.active_flag,''N'') = ''Y'' '||
'and lower(t.manifest_vers)<>''none'';):(';
update ad_check_file_temp t
set t.file_version_id =
(select /*+ INDEX(FV AD_FILE_VERSIONS_U2) */ fv.file_version_id
from ad_file_versions fv
where fv.file_id = t.file_id
and fv.version = t.manifest_vers
and fv.translation_level = t.translation_level)
where t.file_version_id is null
and nvl(t.active_flag,'N') = 'Y'
and lower(t.manifest_vers)<>'none';
'update ad_check_file_temp t '||
'set t.check_file_id = '||
'(select cf.check_file_id '||
'from ad_check_files cf '||
'where cf.file_id = t.file_id '||
'and nvl(cf.distinguisher,''*null*'') ='||
' nvl(t.distinguisher,''*null*'')), '||
't.manifest_vers_higher =.....);):(';
update ad_check_file_temp t
set t.check_file_id =
(select /*+ INDEX(CF AD_CHECK_FILES_U2) */ cf.check_file_id
from ad_check_files cf
where cf.file_id = t.file_id
and nvl(cf.distinguisher,'*null*') = nvl(t.distinguisher,'*null*')),
t.manifest_vers_higher =
(select /*+ ORDERED INDEX(FV1 AD_FILE_VERSIONS_U1)
INDEX(CF AD_CHECK_FILES_U2) INDEX(FV2 AD_FILE_VERSIONS_U1)
USE_NL(FV1 CF FV2) */
decode(
sign(nvl(fv1.version_segment1,0) - nvl(fv2.version_segment1,0)),
-1, null, 1, 'Y', decode(
sign(nvl(fv1.version_segment2,0) - nvl(fv2.version_segment2,0)),
-1, null, 1, 'Y', decode(
sign(nvl(fv1.version_segment3,0) - nvl(fv2.version_segment3,0)),
-1, null, 1, 'Y', decode(
sign(nvl(fv1.version_segment4,0) - nvl(fv2.version_segment4,0)),
-1, null, 1, 'Y', decode(
sign(nvl(fv1.version_segment5,0) - nvl(fv2.version_segment5,0)),
-1, null, 1, 'Y', decode(
sign(nvl(fv1.version_segment6,0) - nvl(fv2.version_segment6,0)),
-1, null, 1, 'Y', decode(
sign(nvl(fv1.version_segment7,0) - nvl(fv2.version_segment7,0)),
-1, null, 1, 'Y', decode(
sign(nvl(fv1.version_segment8,0) - nvl(fv2.version_segment8,0)),
-1, null, 1, 'Y', decode(
sign(nvl(fv1.version_segment9,0) - nvl(fv2.version_segment9,0)),
-1, null, 1, 'Y', decode(
sign(nvl(fv1.version_segment10,0) - nvl(fv2.version_segment10,0)),
-1, null, 1, 'Y', decode(
sign(fv1.translation_level - fv2.translation_level),
-1, null, 1, 'Y', null)))))))))))
from ad_file_versions fv1, ad_check_files cf, ad_file_versions fv2
where t.file_version_id = fv1.file_version_id
and t.file_id = cf.file_id
and nvl(t.distinguisher,'*null*') = nvl(cf.distinguisher,'*null*')
and cf.file_version_id = fv2.file_version_id)
where nvl(t.active_flag,'N') = 'Y';
'insert into ad_check_files '||
'(check_file_id, file_id, distinguisher, '||
'file_version_id, creation_date) '||
'select ad_check_files_s.nextval, '||
'temp.f_id, temp.dist, temp.fv_id, '||
'temp.edate from (select distinct '||
't.file_id f_id, t.distinguisher dist, '||
't.file_version_id fv_id, '||
't.effective_date edate from '||
'ad_check_file_temp t where t.check_file_id '||
'is null and nvl(t.active_flag,''N'') = '||
'''Y'') temp;):(';
insert into ad_check_files
(check_file_id, file_id, distinguisher,
file_version_id, creation_date)
select ad_check_files_s.nextval,
temp.f_id, temp.dist, temp.fv_id, temp.edate
from
(select distinct
t.file_id f_id,
t.distinguisher dist,
t.file_version_id fv_id,
t.effective_date edate
from ad_check_file_temp t
where t.check_file_id is null
and nvl(t.active_flag,'N') = 'Y') temp;
'insert into ad_check_file_history ' ||
'(CHECK_FILE_ID, FILE_VERSION_ID, PATCH_RUN_ID, CREATION_DATE) ' ||
'select distinct acf.check_file_id, acf.file_version_id, -1, sysdate ' ||
'from ad_check_files acf, ad_check_file_temp acft ' ||
' where acft.check_file_id is null ' ||
' and nvl(acft.active_flag,''N'') = ''Y'' ' ||
' and acf.file_id=acft.file_id ' ||
' and nvl(acf.distinguisher, ''x'')=nvl(acft.distinguisher, ''x''))';
insert into ad_check_file_history
(CHECK_FILE_ID, FILE_VERSION_ID, PATCH_RUN_ID, CREATION_DATE)
select distinct acf.check_file_id, acf.file_version_id, -1, sysdate
from ad_check_files acf, ad_check_file_temp acft
where acft.check_file_id is null
and nvl(acft.active_flag,'N') = 'Y'
and acf.file_id=acft.file_id
and nvl(acf.distinguisher, 'x')=nvl(acft.distinguisher, 'x');
'delete from ad_check_files kf '||
'where cf.check_file_id in '||
'(select t.check_file_id '||
'from ad_check_file_temp t '||
'where t.manifest_vers_higher = ''Y'' '||
'and nvl(t.active_flag,''N'') = ''Y'');):(';
delete /*+ INDEX(CF AD_CHECK_FILES_U1) */ from ad_check_files cf
where cf.check_file_id in
(select t.check_file_id
from ad_check_file_temp t
where t.manifest_vers_higher = 'Y'
and nvl(t.active_flag,'N') = 'Y');
' insert into ad_check_files '||
'(check_file_id, file_id, distinguisher, '||
'file_version_id, creation_date) '||
'select temp.cf_id, '||
'temp.f_id, temp.dist, temp.fv_id, temp.edate '||
'from (select distinct '||
't.check_file_id cf_id, '||
't.file_id f_id, '||
't.distinguisher dist, '||
't.file_version_id fv_id, '||
't.effective_date edate '||
'from ad_check_file_temp t '||
'where t.manifest_vers_higher = ''Y'' '||
'and nvl(t.active_flag,''N'') = ''Y'') temp;):(';
insert into ad_check_files
(check_file_id, file_id, distinguisher,
file_version_id, creation_date)
select temp.cf_id,
temp.f_id, temp.dist, temp.fv_id, temp.edate
from
(select distinct
t.check_file_id cf_id,
t.file_id f_id,
t.distinguisher dist,
t.file_version_id fv_id,
t.effective_date edate
from ad_check_file_temp t
where t.manifest_vers_higher = 'Y'
and nvl(t.active_flag,'N') = 'Y') temp;
'insert into ad_check_file_history ' ||
' (CHECK_FILE_ID, FILE_VERSION_ID, PATCH_RUN_ID, CREATION_DATE) ' ||
'select distinct acf.check_file_id, acf.file_version_id, -1, sysdate ' ||
' from ad_check_files acf, ad_check_file_temp acft ' ||
' where acft.manifest_vers_higher = ''Y'' ' ||
' and nvl(acft.active_flag,''N'') = ''Y'' ' ||
' and acf.file_id=acft.file_id ' ||
' and acf.distinguisher=acft.distinguisher)';
insert into ad_check_file_history
(CHECK_FILE_ID, FILE_VERSION_ID, PATCH_RUN_ID, CREATION_DATE)
select distinct acf.check_file_id, acf.file_version_id, -1, sysdate
from ad_check_files acf, ad_check_file_temp acft
where acft.manifest_vers_higher = 'Y'
and nvl(acft.active_flag,'N') = 'Y'
and acf.file_id=acft.file_id
and nvl(acf.distinguisher, 'x')=nvl(acft.distinguisher, 'x');
procedure update_timestamp
(in_type in varchar2,
in_attribute in varchar2,
in_timestamp in date)
--
-- Updates/Inserts the row in AD_TIMESTAMPS for the specified
-- type and attribute
--
is
begin
--
-- First try to update
--
ad_file_util.error_buf := 'update_timestamp(update ad_timestamps '||
'set timestamp = '||in_timestamp||
'where type = '||in_type||
'and attribute = '||in_attribute||'):(';
update ad_timestamps
set timestamp = in_timestamp
where type = in_type
and attribute = in_attribute;
ad_file_util.error_buf := 'update_timestamp('||
'insert into ad_timestamps'||
'(type, attribute, timestamp)'||
'values ('||in_type||', '||in_attribute||
', '||in_timestamp||'):(';
insert into ad_timestamps
(type, attribute, timestamp)
values (in_type, in_attribute, in_timestamp);
end update_timestamp;
procedure update_timestamp
(in_type in varchar2,
in_attribute in varchar2)
is
begin
update_timestamp
(in_type => in_type,
in_attribute => in_attribute,
in_timestamp => sysdate);
'update ad_check_file_temp t '||
'set t.file_version_id = '||
'(select fv.file_version_id '||
'from ad_file_versions fv '||
'where fv.file_id = t.file_id '||
'and fv.version = t.manifest_vers '||
'and fv.translation_level = t.translation_level) '||
'where nvl(t.active_flag,''N'') = ''Y'' '||
'and nvl(t.manifest_vers,''NA'')<>''NA''):(';
update ad_check_file_temp t
set t.file_version_id =
(select /*+ INDEX(FV AD_FILE_VERSIONS_U2) */ fv.file_version_id
from ad_file_versions fv
where fv.file_id = t.file_id
and fv.version = t.manifest_vers
and fv.translation_level = t.translation_level)
where nvl(t.active_flag,'N') = 'Y'
and nvl(t.manifest_vers,'NA')<>'NA';
'insert into ad_file_versions '||
'(file_version_id, file_id, version, '||
'translation_level, version_segment1,'||
'version_segment2, version_segment3, '||
'version_segment4, version_segment5, '||
'version_segment6, version_segment7, '||
'version_segment8, version_segment9, '||
'version_segment10, creation_date, created_by, '||
'last_update_date, last_updated_by) '||
'.....):(';
insert into ad_file_versions
(file_version_id, file_id, version, translation_level,
version_segment1, version_segment2, version_segment3,
version_segment4, version_segment5, version_segment6,
version_segment7, version_segment8, version_segment9,
version_segment10,
creation_date, created_by, last_update_date, last_updated_by)
select ad_file_versions_s.nextval,
temp.f_id, temp.vers, temp.trans_level,
temp.vs1, temp.vs2, temp.vs3, temp.vs4, temp.vs5,
temp.vs6, temp.vs7, temp.vs8, temp.vs9, temp.vs10,
temp.edate, 5, temp.edate, 5
from
(select distinct
t.file_id f_id,
t.manifest_vers vers,
t.translation_level trans_level,
decode( instr(t.manifest_vers||'.','.',1,1), 0, null,
to_number(substr(t.manifest_vers||'.',
1,
( instr(t.manifest_vers||'.','.',1,1)-1)))) vs1,
decode( instr(t.manifest_vers||'.','.',1,2), 0, 0,
to_number(substr(t.manifest_vers||'.',
(instr(t.manifest_vers||'.','.',1,1)+1),
( (instr(t.manifest_vers||'.','.',1,2))
- (instr(t.manifest_vers||'.','.',1,1)+1)) ))) vs2,
decode( instr(t.manifest_vers||'.','.',1,3), 0, 0,
to_number(substr(t.manifest_vers||'.',
(instr(t.manifest_vers||'.','.',1,2)+1),
( (instr(t.manifest_vers||'.','.',1,3))
- (instr(t.manifest_vers||'.','.',1,2)+1)) ))) vs3,
decode( instr(t.manifest_vers||'.','.',1,4), 0, 0,
to_number(substr(t.manifest_vers||'.',
(instr(t.manifest_vers||'.','.',1,3)+1),
( (instr(t.manifest_vers||'.','.',1,4))
- (instr(t.manifest_vers||'.','.',1,3)+1)) ))) vs4,
decode( instr(t.manifest_vers||'.','.',1,5), 0, 0,
to_number(substr(t.manifest_vers||'.',
(instr(t.manifest_vers||'.','.',1,4)+1),
( (instr(t.manifest_vers||'.','.',1,5))
- (instr(t.manifest_vers||'.','.',1,4)+1)) ))) vs5,
decode( instr(t.manifest_vers||'.','.',1,6), 0, 0,
to_number(substr(t.manifest_vers||'.',
(instr(t.manifest_vers||'.','.',1,5)+1),
( (instr(t.manifest_vers||'.','.',1,6))
- (instr(t.manifest_vers||'.','.',1,5)+1)) ))) vs6,
decode( instr(t.manifest_vers||'.','.',1,7), 0, 0,
to_number(substr(t.manifest_vers||'.',
(instr(t.manifest_vers||'.','.',1,6)+1),
( (instr(t.manifest_vers||'.','.',1,7))
- (instr(t.manifest_vers||'.','.',1,6)+1)) ))) vs7,
decode( instr(t.manifest_vers||'.','.',1,8), 0, 0,
to_number(substr(t.manifest_vers||'.',
(instr(t.manifest_vers||'.','.',1,7)+1),
( (instr(t.manifest_vers||'.','.',1,8))
- (instr(t.manifest_vers||'.','.',1,7)+1)) ))) vs8,
decode( instr(t.manifest_vers||'.','.',1,9), 0, 0,
to_number(substr(t.manifest_vers||'.',
(instr(t.manifest_vers||'.','.',1,8)+1),
( (instr(t.manifest_vers||'.','.',1,9))
- (instr(t.manifest_vers||'.','.',1,8)+1)) ))) vs9,
decode( instr(t.manifest_vers||'.','.',1,10), 0, 0,
to_number(substr(t.manifest_vers||'.',
(instr(t.manifest_vers||'.','.',1,9)+1),
( (instr(t.manifest_vers||'.','.',1,10))
- (instr(t.manifest_vers||'.','.',1,9)+1)) ))) vs10,
t.effective_date edate
from ad_check_file_temp t
where t.file_version_id is null
and nvl(t.active_flag,'N') = 'Y'
and nvl(t.manifest_vers,'NA')<>'NA') temp;
'update ad_check_file_temp t '||
'set t.file_version_id = '||
'(select fv.file_version_id '||
'from ad_file_versions fv '||
'where fv.file_id = t.file_id '||
'and fv.version = t.manifest_vers '||
'and fv.translation_level = t.translation_level) '||
'where nvl(t.active_flag,''N'') = ''Y'' '||
'and nvl(t.manifest_vers,''NA'')<>''NA''):(';
update ad_check_file_temp t
set t.file_version_id =
(select /*+ INDEX(FV AD_FILE_VERSIONS_U2) */ fv.file_version_id
from ad_file_versions fv
where fv.file_id = t.file_id
and fv.version = t.manifest_vers
and fv.translation_level = t.translation_level)
where nvl(t.active_flag,'N') = 'Y'
and nvl(t.manifest_vers,'NA')<>'NA';
'update ad_check_file_temp t '||
'set t.file_version_id_2 = '||
'(select fv.file_version_id '||
'from ad_file_versions fv '||
'where fv.file_id = t.file_id '||
'and fv.version = t.manifest_vers_2 '||
'nvl(t.manifest_vers_2,''NA'')<>''NA''):(';
update ad_check_file_temp t
set t.file_version_id_2 =
(select /*+ INDEX(FV AD_FILE_VERSIONS_U2) */ fv.file_version_id
from ad_file_versions fv
where fv.file_id = t.file_id
and fv.version = t.manifest_vers_2
and fv.translation_level = t.translation_level)
where nvl(t.active_flag,'N') = 'Y' AND
nvl(t.manifest_vers_2,'NA')<>'NA';
'insert into ad_file_versions '||
'(file_version_id, file_id, version, '||
'translation_level, version_segment1,'||
'version_segment2, version_segment3, '||
'version_segment4, version_segment5, '||
'version_segment6, version_segment7, '||
'version_segment8, version_segment9, '||
'version_segment10, creation_date, created_by, '||
'last_update_date, last_updated_by) '||
'.....):(';
insert into ad_file_versions
(file_version_id, file_id, version, translation_level,
version_segment1, version_segment2, version_segment3,
version_segment4, version_segment5, version_segment6,
version_segment7, version_segment8, version_segment9,
version_segment10,
creation_date, created_by, last_update_date, last_updated_by)
select ad_file_versions_s.nextval,
temp.f_id, temp.vers, temp.trans_level,
temp.vs1, temp.vs2, temp.vs3, temp.vs4, temp.vs5,
temp.vs6, temp.vs7, temp.vs8, temp.vs9, temp.vs10,
temp.edate, 5, temp.edate, 5
from
(select distinct
t.file_id f_id,
t.manifest_vers_2 vers,
t.translation_level trans_level,
decode( instr(t.manifest_vers_2||'.','.',1,1), 0, null,
to_number(substr(t.manifest_vers_2||'.',
1,
( instr(t.manifest_vers_2||'.','.',1,1)-1)))) vs1,
decode( instr(t.manifest_vers_2||'.','.',1,2), 0, 0,
to_number(substr(t.manifest_vers_2||'.',
(instr(t.manifest_vers_2||'.','.',1,1)+1),
( (instr(t.manifest_vers_2||'.','.',1,2))
- (instr(t.manifest_vers_2||'.','.',1,1)+1)) ))) vs2,
decode( instr(t.manifest_vers_2||'.','.',1,3), 0, 0,
to_number(substr(t.manifest_vers_2||'.',
(instr(t.manifest_vers_2||'.','.',1,2)+1),
( (instr(t.manifest_vers_2||'.','.',1,3))
- (instr(t.manifest_vers_2||'.','.',1,2)+1)) ))) vs3,
decode( instr(t.manifest_vers_2||'.','.',1,4), 0, 0,
to_number(substr(t.manifest_vers_2||'.',
(instr(t.manifest_vers_2||'.','.',1,3)+1),
( (instr(t.manifest_vers_2||'.','.',1,4))
- (instr(t.manifest_vers_2||'.','.',1,3)+1)) ))) vs4,
decode( instr(t.manifest_vers_2||'.','.',1,5), 0, 0,
to_number(substr(t.manifest_vers_2||'.',
(instr(t.manifest_vers_2||'.','.',1,4)+1),
( (instr(t.manifest_vers_2||'.','.',1,5))
- (instr(t.manifest_vers_2||'.','.',1,4)+1)) ))) vs5,
decode( instr(t.manifest_vers_2||'.','.',1,6), 0, 0,
to_number(substr(t.manifest_vers_2||'.',
(instr(t.manifest_vers_2||'.','.',1,5)+1),
( (instr(t.manifest_vers_2||'.','.',1,6))
- (instr(t.manifest_vers_2||'.','.',1,5)+1)) ))) vs6,
decode( instr(t.manifest_vers_2||'.','.',1,7), 0, 0,
to_number(substr(t.manifest_vers_2||'.',
(instr(t.manifest_vers_2||'.','.',1,6)+1),
( (instr(t.manifest_vers_2||'.','.',1,7))
- (instr(t.manifest_vers_2||'.','.',1,6)+1)) ))) vs7,
decode( instr(t.manifest_vers_2||'.','.',1,8), 0, 0,
to_number(substr(t.manifest_vers_2||'.',
(instr(t.manifest_vers_2||'.','.',1,7)+1),
( (instr(t.manifest_vers_2||'.','.',1,8))
- (instr(t.manifest_vers_2||'.','.',1,7)+1)) ))) vs8,
decode( instr(t.manifest_vers_2||'.','.',1,9), 0, 0,
to_number(substr(t.manifest_vers_2||'.',
(instr(t.manifest_vers_2||'.','.',1,8)+1),
( (instr(t.manifest_vers_2||'.','.',1,9))
- (instr(t.manifest_vers_2||'.','.',1,8)+1)) ))) vs9,
decode( instr(t.manifest_vers_2||'.','.',1,10), 0, 0,
to_number(substr(t.manifest_vers_2||'.',
(instr(t.manifest_vers_2||'.','.',1,9)+1),
( (instr(t.manifest_vers_2||'.','.',1,10))
- (instr(t.manifest_vers_2||'.','.',1,9)+1)) ))) vs10,
t.effective_date edate
from ad_check_file_temp t
where t.file_version_id_2 is null
and nvl(t.active_flag,'N') = 'Y'
and nvl(t.manifest_vers_2,'NA')<>'NA') temp;
'update ad_check_file_temp t '||
'set t.file_version_id_2 = '||
'(select fv.file_version_id '||
'from ad_file_versions fv '||
'where fv.file_id = t.file_id '||
'and fv.version = t.manifest_vers_2 '||
'nvl(t.manifest_vers_2,''NA'')<>''NA''):(';
update ad_check_file_temp t
set t.file_version_id_2 =
(select /*+ INDEX(FV AD_FILE_VERSIONS_U2) */ fv.file_version_id
from ad_file_versions fv
where fv.file_id = t.file_id
and fv.version = t.manifest_vers_2
and fv.translation_level = t.translation_level)
where nvl(t.active_flag,'N') = 'Y' AND
nvl(t.manifest_vers_2,'NA')<>'NA';
select
file_version_id,check_sum,file_size,
file_id,check_file_id,dest_file_id,
file_type_flag, manifest_vers_higher,
effective_date
from ad_check_file_temp;
'select file_version_id,check_sum,file_size, '||
'file_id,check_file_id,dest_file_id, '||
'file_type_flag from '||
'ad_check_file_temp):(';
update /*+ INDEX(SNAP AD_SNAPSHOT_FILES_U2) */
ad_snapshot_files snap
set
snap.file_version_id = vers_id_list(j),
snap.file_size = fl_size_list(j),
snap.checksum = chk_sum_list(j),
snap.dest_file_id = dest_file_id_list(j),
snap.file_type_flag = file_type_flag_list(j),
snap.update_source_id = snp_id,
-- Intentionally storing 'U' so that these rows will be marked
-- so that we can know which rows were updated
snap.update_type ='U',
snap.last_update_date = sysdate,
snap.last_patched_date = decode(preserve_irep_flag,1,
snap.last_patched_date,
decode ((effective_date_list(j) - snap.last_patched_date) -
abs(effective_date_list(j) - snap.last_patched_date),
0, effective_date_list(j), snap.last_patched_date)),
snap.irep_gathered_flag = decode(preserve_irep_flag,1,
snap.irep_gathered_flag,
irep_gathered_flag_list(j))
where
snap.snapshot_id=snp_id and
snap.file_id =fl_id_list(j) and
nvl(snap.containing_file_id,-1)=nvl(con_file_id_list(j),-1);
'INSERT INTO ad_snapshot_files '||
'(snapshot_file_id,snapshot_id,file_id, '||
'containing_file_id,file_size,checksum,'||
'file_version_id, update_source_id, '||
'update_type,creation_date,last_update_date,' ||
'last_updated_by,created_by,' ||
'appl_top_id, inconsistent_flag, '||
'dest_file_id, file_type_flag) '||
'select ad_snapshot_files_s.nextval,'||
'snp_id,t.file_id, t.check_file_id,'||
't.file_size,t.check_sum, t.file_version_id,'||
'snp_id,''U'',sysdate,sysdate, 5,5,' ||
't.appl_top_id, t.inconsistent_flag, '||
't.dest_file_id, t.file_type_flag '||
'from ad_check_file_temp t where not exists '||
'(select ''already present'' '||
'from ad_snapshot_files sf2 '||
'where sf2.snapshot_id = snp_id '||
'and sf2.file_id = t.file_id '||
'and nvl(sf2.containing_file_id,-1) = '||
'nvl(t.check_file_id,-1)):(';
INSERT INTO ad_snapshot_files
(snapshot_file_id,snapshot_id,file_id,
containing_file_id,file_size,checksum,file_version_id,
update_source_id, update_type,creation_date,last_update_date,
last_updated_by,created_by, appl_top_id, inconsistent_flag,
dest_file_id, file_type_flag, irep_gathered_flag,last_patched_date)
select
ad_snapshot_files_s.nextval,snp_id,t.file_id,
t.check_file_id,t.file_size,t.check_sum,
t.file_version_id,snp_id,'U',sysdate,sysdate,
5,5, t.appl_top_id, t.inconsistent_flag,
t.dest_file_id, t.file_type_flag,
t.manifest_vers_higher, t.effective_date
from ad_check_file_temp t
where not exists
(select /*+ INDEX(SF2 AD_SNAPSHOT_FILES_U2) */ 'already present'
from ad_snapshot_files sf2
where sf2.snapshot_id = snp_id
and sf2.file_id = t.file_id
and nvl(sf2.containing_file_id,-1) = nvl(t.check_file_id,-1)
);
update ad_snapshots set last_update_date = sysdate
where snapshot_id = snp_id;
'SET t.file_id = (SELECT b.bug_id '||
'FROM ad_bugs b WHERE b.bug_number = t.filename '||
'AND b.aru_release_name = t.subdir '||
'AND b.trackable_entity_abbr=t.app_short_name '||
'AND b.language = t.language '||
'AND b.baseline_name = t.manifest_vers_2), '||
't.junk = NULL '||
'WHERE NVL(t.active_flag,''N'') = ''Y''):(';
UPDATE ad_check_file_temp t
SET t.file_id = (SELECT /*+ INDEX(B AD_BUGS_U2) */ b.bug_id
FROM ad_bugs b
WHERE b.bug_number = t.filename
AND b.aru_release_name = t.subdir
-- bug 6317065 diverma Thu Aug 2 04:10:21 PDT 2007
AND b.trackable_entity_abbr = t.app_short_name
-- bug 5615204 diverma Tuesday, August 07, 2007
AND b.language = t.language
-- Bug 5596989 - stangutu - 17 Oct, 2006
-- Bug 5758908 - stangutu - 14 June, 2007
AND b.baseline_name = t.manifest_vers_2),
-- If the above condition does not work, we need to include below line.
-- AND b.generic_patch = t.manifest_vers_higher),
t.junk = NULL
WHERE NVL(t.active_flag,'N') = 'Y';
'INSERT INTO ad_bugs '||
'(bug_id, bug_number,aru_release_name, '||
'creation_date, created_by, last_update_date, '||
'last_updated_by, baseline_name, generic_patch, '||
' trackable_entity_abbr ) SELECT '||
'ad_bugs_s.nextval, temp.bugfix, temp.rel, '||
'temp.edate, 5, temp.edate, 5, '||
'temp.baseline_name, temp.generic_patch, '||
'temp.trackable_entity_abbr, language)' ||
'FROM (SELECT DISTINCT t.filename bugfix, '||
't.subdir rel, t.effective_date edate '||
't.manifest_vers_2 baseline_name, '||
't.manifest_vers_higher, generic_patch, '||
't.app_short_name trackable_entity_abbr, ' ||
't.language language '||
'FROM ad_check_file_temp t '||
'WHERE t.file_id is null '||
'AND NVL(t.active_flag,''N'') = ''Y'') temp):(';
INSERT INTO ad_bugs
(bug_id, bug_number,aru_release_name,
creation_date, created_by, last_update_date, last_updated_by,
-- Bug 5758908 - stangutu - 14 June, 2007
baseline_name, generic_patch, trackable_entity_abbr,
-- bug 5615204 diverma Tuesday, August 07, 2007
language)
SELECT
ad_bugs_s.nextval, temp.bugfix, temp.rel,
temp.edate, 5, temp.edate, 5,
-- Bug 5758908 - stangutu - 14 June, 2007
temp.baseline_name, temp.generic_patch, temp.trackable_entity_abbr,
-- bug 5615204 diverma Tuesday, August 07, 2007
temp.language
FROM
(SELECT DISTINCT
t.filename bugfix,
t.subdir rel ,
t.effective_date edate,
-- Bug 5758908 - stangutu - 14 June, 2007
t.manifest_vers_2 baseline_name,
t.manifest_vers_higher generic_patch,
t.app_short_name trackable_entity_abbr,
-- bug 5615204 diverma Tuesday, August 07, 2007
t.language language
FROM ad_check_file_temp t
WHERE t.file_id is null
AND NVL(t.active_flag,'N') = 'Y') temp;
'UPDATE ad_check_file_temp t '||
'SET t.file_id = (SELECT b.bug_id '||
'FROM ad_bugs b WHERE b.bug_number = t.filename '||
'AND b.aru_release_name = t.subdir, '||
'AND b.trackable_entity_abbr = t.app_short_name' ||
'AND b.language = t.language '||
'AND b.baseline_name = t.manifest_vers_2), '||
't.junk = NULL '||
'WHERE NVL(t.active_flag,''N'') = ''Y''):(';
UPDATE ad_check_file_temp t
SET t.file_id = (SELECT /*+ INDEX(B AD_BUGS_U2) */ b.bug_id
FROM ad_bugs b
WHERE b.bug_number = t.filename
AND b.aru_release_name = t.subdir
-- bug 6317065 diverma Thu Aug 2 04:10:21 PDT 2007
AND b.trackable_entity_abbr = t.app_short_name
-- bug 5615204 diverma Tuesday, August 07, 2007
AND b.language = t.language
-- Bug 5596989 - stangutu -17Oct, 2006
-- Bug 5758908 - stangutu - 14 June, 2007
AND b.baseline_name = t.manifest_vers_2),
-- If the above condition does not work, we need to include below line.
-- AND b.generic_patch = t.manifest_vers_higher),
t.junk = NULL
WHERE NVL(t.active_flag,'N') = 'Y';
'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,'||
'file_version_id_2, file_id,''EXPLICIT'',''Y'','||
'sysdate, sysdate,5,5 FROM ad_check_file_temp t '||
'where not exists (select ''already present'' '||
'from ad_snapshot_bugfixes b '||
'where b.BUGFIX_ID=t.file_id and '||
'b.SNAPSHOT_ID=t.file_version_id_2):(';
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,file_version_id_2,
file_id,'EXPLICIT','Y',sysdate,
sysdate,5,5
FROM
ad_check_file_temp t
where not exists
(select /*+ INDEX(B AD_SNAPSHOT_BUGFIXES_U2) */ 'already present'
from ad_snapshot_bugfixes b
where b.BUGFIX_ID=t.file_id and
b.SNAPSHOT_ID=t.file_version_id_2);
select patch_run_bug_id, common_action_id, file_id, rowid row_id
from ad_patch_hist_temp
where (patch_run_bug_id, common_action_id, file_id) in
(select patch_run_bug_id, common_action_id, file_id
from AD_PATCH_HIST_TEMP
group by patch_run_bug_id, common_action_id, file_id
having count(*) > 1)
order by 1, 2, 3;
update AD_PATCH_HIST_TEMP
set TRACKABLE_ENTITY_NAME = BUG_APP_SHORT_NAME
where TRACKABLE_ENTITY_NAME is null;
update AD_PATCH_HIST_TEMP
set LANGUAGE = 'US'
where LANGUAGE is null;
insert into ad_bugs
(
BUG_ID, BUG_NUMBER, ARU_RELEASE_NAME, CREATION_DATE,
CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
-- bug 5615204 diverma Tuesday, August 07, 2007
TRACKABLE_ENTITY_ABBR, BASELINE_NAME, GENERIC_PATCH, LANGUAGE
)
-- bug 6332450 diverma Thu Aug 9 06:25:06 PDT 2007
select
ad_bugs_s.nextval, BUG_NUMBER, ARU_RELEASE_NAME, sysdate,
5, sysdate, 5, TRACKABLE_ENTITY_NAME , BASELINE_NAME,
GENERIC_PATCH, LANGUAGE
from
(
select
distinct BUG_NUMBER, ARU_RELEASE_NAME,
-- bug 6332450 diverma Thu Aug 9 06:25:06 PDT 2007
-- bug 5615204 diverma Tuesday, August 07, 2007
TRACKABLE_ENTITY_NAME, BASELINE_NAME, GENERIC_PATCH, LANGUAGE
from
AD_PATCH_HIST_TEMP where BUG_NUMBER is not null) tmp
where
not exists (
select
'x'
from
ad_bugs b
where
b.bug_number = tmp.BUG_NUMBER
-- bug 6332450 diverma Thu Aug 9 06:25:06 PDT 2007
and b.trackable_entity_abbr = tmp.TRACKABLE_ENTITY_NAME
and b.baseline_name = tmp.baseline_name
and b.aru_release_name = tmp.aru_release_name
-- bug 5615204 diverma Tuesday, August 07, 2007
and b.language = tmp.LANGUAGE
);
update AD_PATCH_HIST_TEMP t
set t.bug_id = (
select
b.bug_id from ad_bugs b
where
b.bug_number = t.BUG_NUMBER
-- bug 6332450 diverma Thu Aug 9 06:25:06 PDT 2007
and b.trackable_entity_abbr = t.TRACKABLE_ENTITY_NAME
and nvl(b.baseline_name,'NULL') = nvl(t.baseline_name,'NULL')
and b.aru_release_name = t.aru_release_name
-- bug 5615204 diverma Tuesday, August 07, 2007
and b.language = t.LANGUAGE
);
insert into ad_patch_run_bugs
(
PATCH_RUN_BUG_ID,
PATCH_RUN_ID, BUG_ID, ORIG_BUG_NUMBER, APPLICATION_SHORT_NAME,
SUCCESS_FLAG, APPLIED_FLAG, REASON_NOT_APPLIED,
CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY
)
select
ad_patch_run_bugs_s.nextval,
patch_run_id, bug_id, orig_bug_number,bug_app_short_name,
success_flag, applied_flag, reason_not_applied,
sysdate, sysdate, 5, 5
from (
select
distinct patch_run_id,bug_id,
orig_bug_number, bug_app_short_name,
success_flag, applied_flag, reason_not_applied
from
AD_PATCH_HIST_TEMP ) t
where
not exists (
select
'x'
from ad_patch_run_bugs b
where
b.PATCH_RUN_ID = t.patch_run_id
and b.BUG_ID = t.bug_id
and b.ORIG_BUG_NUMBER = t.orig_bug_number
and b.APPLICATION_SHORT_NAME = t.bug_app_short_name);
update AD_PATCH_HIST_TEMP t
set PATCH_RUN_BUG_ID
=(select
b.PATCH_RUN_BUG_ID
from
ad_patch_run_bugs b
where
b.PATCH_RUN_ID = t.patch_run_id
and b.BUG_ID = t.bug_id
and b.ORIG_BUG_NUMBER = t.orig_bug_number
and b.APPLICATION_SHORT_NAME = t.bug_app_short_name);
insert into ad_files
(file_id,
app_short_name,
subdir, filename,
creation_date, created_by, last_update_date, last_updated_by)
select ad_files_s.nextval,
temp.FILE_APPS_SHORT_NAME asn,
temp.file_subdir dir, temp.filename fname,
sysdate,5,sysdate,5
from
(select distinct
t.file_apps_short_name ,
t.file_subdir ,
t.filename
from
AD_PATCH_HIST_TEMP t
) temp
where not exists (
select
'x' from ad_files fl
where
fl.filename = temp.filename
and fl.subdir = temp.file_subdir
and fl.app_short_name = temp.file_apps_short_name
)
and temp.filename is not null;
update AD_PATCH_HIST_TEMP t
set t.file_id =
(select f.file_id
from ad_files f
where
f.filename = t.filename
and f.subdir = t.file_subdir
and f.app_short_name = t.file_apps_short_name);
insert into ad_files
(file_id, app_short_name, subdir, filename,
creation_date, created_by, last_update_date, last_updated_by)
select ad_files_s.nextval,
temp.ldr_app_short_name asn,
temp.ldr_subdir dir, temp.ldr_filename fname,
sysdate, 5, sysdate, 5
from
(select distinct
t.ldr_app_short_name ,
t.ldr_subdir ,
t.ldr_filename
from
AD_PATCH_HIST_TEMP t
) temp
where not exists (
select
'x' from ad_files fl
where
fl.filename = temp.ldr_filename
and fl.subdir = temp.ldr_subdir
and fl.app_short_name = temp.ldr_app_short_name
)
and temp.ldr_filename is not null;
update AD_PATCH_HIST_TEMP t
set t.loader_data_file_id =
(select f.file_id
from ad_files f
where
f.filename = t.ldr_filename
and f.subdir = t.ldr_subdir
and f.app_short_name = t.ldr_app_short_name)
where t.ldr_filename is not null;
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,
temp.dest_apps_short_name,
temp.dest_subdir,
temp.dest_filename,
sysdate, sysdate, 5, 5
from
(select distinct
t.dest_apps_short_name ,
t.dest_subdir ,
t.dest_filename
from
AD_PATCH_HIST_TEMP t
where t.dest_apps_short_name is not null
and t.dest_subdir is not null
and t.dest_filename is not null
) temp
where not exists (
select
'dest file already exists' from ad_files f
where
f.filename = temp.dest_filename
and f.subdir = temp.dest_subdir
and f.app_short_name = temp.dest_apps_short_name);
update AD_PATCH_HIST_TEMP t
set t.dest_file_id =
(select /*+ INDEX(F AD_FILES_U2) */ f.file_id
from ad_files f
where f.app_short_name = t.dest_apps_short_name
and f.subdir = t.dest_subdir
and f.filename = t.dest_filename);
INSERT into ad_file_versions
(file_version_id, file_id, version, translation_level,
version_segment1, version_segment2, version_segment3,
version_segment4, version_segment5, version_segment6,
version_segment7, version_segment8, version_segment9,
version_segment10,
creation_date, created_by, last_update_date, last_updated_by)
select
ad_file_versions_s.nextval,
temp.f_id, temp.vers, temp.trans_level,
temp.vs1, temp.vs2, temp.vs3, temp.vs4, temp.vs5,
temp.vs6, temp.vs7, temp.vs8, temp.vs9, temp.vs10,
sysdate, 5, sysdate, 5
from
(
select
distinct
t.file_id f_id,
t.PATCH_FILE_VERS vers,
t.PATCH_TRANS_LEVEL trans_level,
t.PATCH_VERSION_SEGMENT1 vs1,
t.PATCH_VERSION_SEGMENT2 vs2,
t.PATCH_VERSION_SEGMENT3 vs3,
t.PATCH_VERSION_SEGMENT4 vs4,
t.PATCH_VERSION_SEGMENT5 vs5,
t.PATCH_VERSION_SEGMENT6 vs6,
t.PATCH_VERSION_SEGMENT7 vs7,
t.PATCH_VERSION_SEGMENT8 vs8,
t.PATCH_VERSION_SEGMENT9 vs9,
t.PATCH_VERSION_SEGMENT10 vs10
from
AD_PATCH_HIST_TEMP t
where
t.PATCH_FILE_VERS is not null
) temp
where not exists (
select
'x'
from
ad_file_versions vers
where
vers.file_id = temp.f_id
and vers.version = temp.vers
and vers.translation_level = temp.trans_level);
INSERT into ad_file_versions
(file_version_id, file_id, version, translation_level,
version_segment1, version_segment2, version_segment3,
version_segment4, version_segment5, version_segment6,
version_segment7, version_segment8, version_segment9,
version_segment10,
creation_date, created_by, last_update_date, last_updated_by)
select
ad_file_versions_s.nextval,
temp.f_id, temp.vers, temp.trans_level,
temp.vs1, temp.vs2, temp.vs3, temp.vs4, temp.vs5,
temp.vs6, temp.vs7, temp.vs8, temp.vs9, temp.vs10,
sysdate, 5, sysdate, 5
from
(
select
distinct t.file_id f_id,
t.ONSITE_FILE_VERS vers,
t.ONSITE_TRANS_LEVEL trans_level,
t.ONSITE_VERSION_SEGMENT1 vs1,
t.ONSITE_VERSION_SEGMENT2 vs2,
t.ONSITE_VERSION_SEGMENT3 vs3,
t.ONSITE_VERSION_SEGMENT4 vs4,
t.ONSITE_VERSION_SEGMENT5 vs5,
t.ONSITE_VERSION_SEGMENT6 vs6,
t.ONSITE_VERSION_SEGMENT7 vs7,
t.ONSITE_VERSION_SEGMENT8 vs8,
t.ONSITE_VERSION_SEGMENT9 vs9,
t.ONSITE_VERSION_SEGMENT10 vs10
from
AD_PATCH_HIST_TEMP t
where
t.ONSITE_FILE_VERS is not NULL
) temp
where not exists (
select
'x'
from
ad_file_versions vers
where
vers.file_id = temp.f_id
and vers.version = temp.vers
and vers.translation_level = temp.trans_level);
INSERT into ad_file_versions
(file_version_id, file_id, version, translation_level,
version_segment1, version_segment2, version_segment3,
version_segment4, version_segment5, version_segment6,
version_segment7, version_segment8, version_segment9,
version_segment10,
creation_date, created_by, last_update_date, last_updated_by)
select
ad_file_versions_s.nextval,
tmp.f_id,tmp.vers, tmp.trans_level,
tmp.vs1, tmp.vs2, tmp.vs3, tmp.vs4,
tmp.vs5, tmp.vs6, tmp.vs7, tmp.vs8,
tmp.vs9, tmp.vs10,sysdate, 5, sysdate, 5
from
(
select
distinct
t.file_id f_id,
t.DB_FILE_VERS vers,
t.DB_TRANS_LEVEL trans_level,
t.DB_VERSION_SEGMENT1 vs1 ,
t.DB_VERSION_SEGMENT2 vs2 ,
t.DB_VERSION_SEGMENT3 vs3 ,
t.DB_VERSION_SEGMENT4 vs4 ,
t.DB_VERSION_SEGMENT5 vs5 ,
t.DB_VERSION_SEGMENT6 vs6 ,
t.DB_VERSION_SEGMENT7 vs7 ,
t.DB_VERSION_SEGMENT8 vs8 ,
t.DB_VERSION_SEGMENT9 vs9 ,
t.DB_VERSION_SEGMENT10 vs10
from
AD_PATCH_HIST_TEMP t
where
t.DB_FILE_VERS is not null
) tmp
where not exists (
select
'x'
from
ad_file_versions vers
where
vers.file_id = tmp.f_id
and vers.version = tmp.vers
and vers.translation_level = tmp.trans_level);
update AD_PATCH_HIST_TEMP t
set t.PATCH_FILE_VERS_ID =
(select
fv.file_version_id
from
ad_file_versions fv
where
fv.file_id = t.file_id
and fv.version = t.PATCH_FILE_VERS
and fv.translation_level = t.PATCH_TRANS_LEVEL)
where
t.PATCH_FILE_VERS is not NULL;
update AD_PATCH_HIST_TEMP t
set t.ONSITE_FILE_VERS_ID =
(select
fv.file_version_id
from
ad_file_versions fv
where
fv.file_id = t.file_id
and fv.version = t.ONSITE_FILE_VERS
and fv.translation_level = t.ONSITE_TRANS_LEVEL
)
where
t.ONSITE_FILE_VERS is not NULL;
update AD_PATCH_HIST_TEMP t
set t.DB_FILE_VERS_ID =
(select
fv.file_version_id
from
ad_file_versions fv
where
fv.file_id = t.file_id
and fv.version = t.DB_FILE_VERS
and fv.translation_level = t.DB_TRANS_LEVEL)
where
t.DB_FILE_VERS is not NULL;
INSERT INTO AD_PATCH_COMMON_ACTIONS
(
COMMON_ACTION_ID, ACTION_CODE, ACTION_PHASE, NUMERIC_PHASE,
NUMERIC_SUB_PHASE, ACTION_ARGUMENTS, CHECKFILE_ARGS,
ACTION_CHECK_OBJ, ACTION_CHECK_OBJ_USERNAME, ACTION_CHECK_OBJ_PASSWD,
ACTION_WHAT_SQL_EXEC, ACTION_TIERLIST_IN_DRIVER, ACTION_LANG_CODE,
CONCAT_ATTRIBS, LOADER_DATA_FILE_ID, CREATION_DATE,
LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY
)
select AD_PATCH_COMMON_ACTIONS_S.NEXTVAL,
t.action_code, t.action_phase, t.major_phase, t.minor_phase,
t.action_arguments, t.checkfile_args, t.checkobj , t.checkobj_un ,
t.checkobj_pw, t.action_modifier , t.action_tierlist ,
t.action_lang_code , t.concat_attribs, t.loader_data_file_id,
sysdate, sysdate, 5, 5
from
(
select distinct
action_code, action_phase, major_phase, minor_phase,
action_arguments, checkfile_args, checkobj , checkobj_un ,
checkobj_pw, action_modifier, action_tierlist ,
action_lang_code, concat_attribs, loader_data_file_id
from
AD_PATCH_HIST_TEMP )t
where not exists (
select
'x'
FROM
AD_PATCH_COMMON_ACTIONS PCA
WHERE
PCA.CONCAT_ATTRIBS = t.CONCAT_ATTRIBS)
and t.concat_attribs is not null;
update AD_PATCH_HIST_TEMP t
set t.COMMON_ACTION_ID =
(select
PCA.COMMON_ACTION_ID
from
AD_PATCH_COMMON_ACTIONS PCA
WHERE
PCA.CONCAT_ATTRIBS = t.concat_attribs )
where t.concat_attribs is not null;
statement := 'delete from ad_patch_hist_temp'||
' where patch_run_bug_id = '||c1.patch_run_bug_id||
' and common_action_id = '||c1.common_action_id||
' and file_id = '||c1.file_id||
' and rowid <> '''||c1.row_id||'''';
insert into AD_PATCH_RUN_BUG_ACTIONS
(
ACTION_ID,
PATCH_RUN_BUG_ID,
COMMON_ACTION_ID,
FILE_ID,
PATCH_FILE_VERSION_ID,
ONSITE_FILE_VERSION_ID,
ONSITE_PKG_VERSION_IN_DB_ID,
EXECUTED_FLAG,
DEST_FILE_ID, FILE_TYPE_FLAG,
CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY
)
select
AD_PATCH_RUN_BUG_ACTIONS_S.NEXTVAL,
t.patch_run_bug_id,
t.common_action_id,
t.file_id,
t.patch_file_vers_id,
t.onsite_file_vers_id,
t.db_file_vers_id,
t.action_executed_flag,
t.dest_file_id, t.file_type_flag,
SYSDATE, SYSDATE, 5, 5 from AD_PATCH_HIST_TEMP t
where not exists
(select
'x'
from
AD_PATCH_RUN_BUG_ACTIONS aprba
where
aprba.PATCH_RUN_BUG_ID = t.patch_run_bug_id
and aprba.FILE_ID = t.file_id
and aprba.COMMON_ACTION_ID = t.common_action_id)
and t.common_action_id is not null and t.ldr_filename is null;
insert into AD_PATCH_RUN_BUG_ACTIONS
(
ACTION_ID,
PATCH_RUN_BUG_ID,
COMMON_ACTION_ID,
FILE_ID,
PATCH_FILE_VERSION_ID,
ONSITE_FILE_VERSION_ID,
ONSITE_PKG_VERSION_IN_DB_ID,
EXECUTED_FLAG,
DEST_FILE_ID, FILE_TYPE_FLAG,
CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY
)
select
AD_PATCH_RUN_BUG_ACTIONS_S.NEXTVAL,
t.patch_run_bug_id,
t.common_action_id,
t.loader_data_file_id,
t.patch_file_vers_id,
t.onsite_file_vers_id,
t.db_file_vers_id,
t.action_executed_flag,
t.dest_file_id, t.file_type_flag,
SYSDATE, SYSDATE, 5, 5 from AD_PATCH_HIST_TEMP t
where not exists
(select
'x'
from
AD_PATCH_RUN_BUG_ACTIONS aprba
where
aprba.PATCH_RUN_BUG_ID = t.patch_run_bug_id
and aprba.FILE_ID = t.loader_data_file_id
and aprba.COMMON_ACTION_ID = t.common_action_id)
and t.common_action_id is not null and t.loader_data_file_id is not null;
select count(*) into l_snapshot_count
from ad_snapshots s, ad_appl_tops t
where s.snapshot_type = l_current_snapshot_type and
s.snapshot_name = 'CURRENT_VIEW' and
s.appl_top_id = t.appl_top_id and
t.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_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', /* APPL_TOP type is 'G' */
p_apps_system_name,
'G',
'Created for Global View Snapshot',
null,
null,
null,
null,
sysdate,
5,
sysdate,
5,
'N' /* ACTIVE_FLAG is set to 'N'. (Refer CONCURRENT_SESSIONS) */
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 l_appl_top_id
from ad_appl_tops
where appl_top_type = 'G' and
name = 'GLOBAL' and
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,
l_appl_top_id,
'GLOBAL_VIEW',
sysdate,
sysdate,
l_global_snapshot_type, /* snapshot type is 'G' */
'Created from Current View Snapshots',
'Y', /* Setting RAN_SNAPSHOT_FLAG to 'Y'. Because, it doesn't */
sysdate, /* have any significance for GLOBAL_VIEW */
5,
5,
sysdate
from dual where not exists(select 'Already exists'
from ad_snapshots s
where s.appl_top_id = l_appl_top_id
and s.snapshot_type = l_global_snapshot_type
and s.snapshot_name = 'GLOBAL_VIEW');
select s.snapshot_id into l_global_snapshot_id
from ad_snapshots s
where s.snapshot_type = l_global_snapshot_type and
s.snapshot_name = 'GLOBAL_VIEW' and
s.appl_top_id = l_appl_top_id;
insert into ad_check_file_temp
(TRANSLATION_LEVEL,APP_SHORT_NAME,
SUBDIR,FILENAME, MANIFEST_VERS,
EFFECTIVE_DATE)
select
snapshot_id, ' ',
' ',' ',' ',
sysdate
from
ad_snapshots snap,
ad_appl_tops atp,
ad_releases ar,
fnd_product_groups fpg
where
atp.appl_top_id=snap.appl_top_id and
atp.appl_top_type='R' and
atp.applications_system_name=p_applications_sys_name and
nvl(atp.active_flag,'Y') = 'Y' and
snap.snapshot_type = v_current_snapshot_type and
snap.snapshot_name = 'CURRENT_VIEW' and
snap.release_id = ar.release_id and
fpg.release_name = ar.major_version||'.'||ar.minor_version||'.'||ar.tape_version and
fpg.applications_system_name=atp.applications_system_name and
fpg.product_group_id=1;
insert into ad_snapshot_files
(
snapshot_file_id,snapshot_id,
file_id, file_version_id, containing_file_id,
file_size, checksum, update_source_id, update_type,
appl_top_id, inconsistent_flag, dest_file_id,file_type_flag,
creation_date,last_update_date,last_updated_by,created_by
)
select
ad_snapshot_files_s.nextval,v_global_snapshot_id,
file_id,file_version_id,containing_file_id,
file_size,checksum,update_source_id,'S',
appl_top_id, 'N', dest_file_id,file_type_flag,
sysdate,sysdate,5,5
from
(
select
file_id,
max(file_version_id) file_version_id,
max(containing_file_id) containing_file_id,
max(file_size) file_size,
max(checksum) checksum,
max(snapshot_id) snapshot_id,
max(dest_file_id) dest_file_id,
max(appl_top_id) appl_top_id,
decode(max(decode(update_type, 'P', 2, 1)), 2, 'P', 'S') update_type,
decode(max(decode(file_type_flag, 'M', 2, 1)), 2, 'M', 'N') file_type_flag,
replace(max(decode(update_type, 'P', 'a', null)||
to_char(update_source_id)), 'a', null) update_source_id
from
ad_snapshot_files
where
file_id >= p_min_file_id and
file_id < p_max_file_id and
snapshot_id in (select TRANSLATION_LEVEL from ad_check_file_temp)
group by
file_id
having
count(distinct nvl(file_version_id,0))=1
);
insert into ad_patch_hist_temp
(
file_id, patch_file_vers_id , onsite_file_vers_id,
bug_id, patch_run_bug_id ,
db_file_vers_id, applied_flag,common_action_id,
success_flag, major_phase,action_executed_flag,
concat_attribs
)
select /*+ opt_param('_gby_hash_aggregation_enabled','false') */
file_id,
nvl(file_version_id,0),
containing_file_id,
file_size,
checksum,
update_source_id,
update_type,
appl_top_id ,
'Y',
dest_file_id,
file_type_flag,
'1234567890123456789012345678901234567890123456789012345678901234567890'
from
ad_snapshot_files
where
file_id in
( select
file_id from ad_snapshot_files
where
file_id >= p_min_file_id and
file_id < p_max_file_id and
snapshot_id in (select TRANSLATION_LEVEL from ad_check_file_temp)
group by
file_id
having
count(distinct nvl(file_version_id,0)) >1
) and
snapshot_id in (select TRANSLATION_LEVEL from ad_check_file_temp);
update ad_patch_hist_temp tmp set
(tmp.PATCH_VERSION_SEGMENT1, tmp.PATCH_VERSION_SEGMENT2,
tmp.PATCH_VERSION_SEGMENT3, tmp.PATCH_VERSION_SEGMENT4,
tmp.PATCH_VERSION_SEGMENT5, tmp.PATCH_VERSION_SEGMENT6,
tmp.PATCH_VERSION_SEGMENT7, tmp.PATCH_VERSION_SEGMENT8,
tmp.PATCH_VERSION_SEGMENT9, tmp.PATCH_VERSION_SEGMENT10,
tmp.PATCH_FILE_VERS, tmp.PATCH_TRANS_LEVEL) =
(select
v.VERSION_SEGMENT1, v.VERSION_SEGMENT2,
v.VERSION_SEGMENT3, v.VERSION_SEGMENT4,
v.VERSION_SEGMENT5, v.VERSION_SEGMENT6,
v.VERSION_SEGMENT7, v.VERSION_SEGMENT8,
v.VERSION_SEGMENT9, v.VERSION_SEGMENT10,
v.VERSION, v.TRANSLATION_LEVEL
from
ad_file_versions v
where
v.file_version_id = tmp.PATCH_FILE_VERS_ID),
tmp.concat_attribs=null;
update ad_patch_hist_temp tmp set
tmp.PATCH_VERSION_SEGMENT1=0, tmp.PATCH_VERSION_SEGMENT2=0,
tmp.PATCH_VERSION_SEGMENT3=0, tmp.PATCH_VERSION_SEGMENT4=0,
tmp.PATCH_VERSION_SEGMENT5=0, tmp.PATCH_VERSION_SEGMENT6=0,
tmp.PATCH_VERSION_SEGMENT7=0, tmp.PATCH_VERSION_SEGMENT8=0,
tmp.PATCH_VERSION_SEGMENT9=0, tmp.PATCH_VERSION_SEGMENT10=0,
tmp.PATCH_FILE_VERS=null, tmp.PATCH_TRANS_LEVEL=null
where tmp.PATCH_FILE_VERS_ID=0;
execute immediate 'insert into ad_snapshot_files
(
snapshot_file_id,snapshot_id,
file_id, file_version_id, containing_file_id,
file_size, checksum, update_source_id, update_type,
appl_top_id, inconsistent_flag, dest_file_id,file_type_flag,
creation_date,last_update_date,last_updated_by,
created_by
)
select
ad_snapshot_files_s.nextval,:v_global_snapshot_id,
file_id, patch_file_vers_id , onsite_file_vers_id,
bug_id, patch_run_bug_id,db_file_vers_id,applied_flag,
common_action_id, ''Y'', major_phase,action_executed_flag,
sysdate,sysdate,5,5
from
(
select
file_id, patch_file_vers_id , onsite_file_vers_id,
bug_id, patch_run_bug_id ,
db_file_vers_id, applied_flag,common_action_id,
success_flag, major_phase ,action_executed_flag,row_number() over
(
PARTITION BY file_id order by
PATCH_VERSION_SEGMENT1 desc, PATCH_VERSION_SEGMENT2 desc,
PATCH_VERSION_SEGMENT3 desc, PATCH_VERSION_SEGMENT4 desc,
PATCH_VERSION_SEGMENT5 desc, PATCH_VERSION_SEGMENT6 desc,
PATCH_VERSION_SEGMENT7 desc, PATCH_VERSION_SEGMENT8 desc,
PATCH_VERSION_SEGMENT9 desc, PATCH_VERSION_SEGMENT10 desc,
PATCH_TRANS_LEVEL desc NULLS LAST
) rnk
from
ad_patch_hist_temp) where rnk=1' using v_global_snapshot_id;
insert into ad_patch_hist_temp
(patch_run_id)
select
snapshot_id
from
ad_snapshots snap,
ad_appl_tops atp,
ad_releases ar,
fnd_product_groups fpg
where
atp.appl_top_id=snap.appl_top_id and
atp.appl_top_type='R' and
atp.applications_system_name=p_applications_sys_name and
nvl(atp.active_flag,'Y') = 'Y' and
snap.snapshot_type = v_current_snapshot_type and
snap.snapshot_name = 'CURRENT_VIEW' and
snap.release_id = ar.release_id and
fpg.release_name = ar.major_version||'.'||ar.minor_version||'.'||ar.tape_version and
fpg.applications_system_name=atp.applications_system_name and
fpg.product_group_id=1;
insert into ad_snapshot_bugfixes
(
SNAPSHOT_BUG_ID,
snapshot_id, bugfix_id,
inconsistent_flag,
bug_status, success_flag,
creation_date,last_update_date,last_updated_by,
created_by
)
select
ad_snapshot_bugfixes_s.nextval,v_global_snapshot_id,
bugfix_id,
'N',
bug_status,success_flag,
sysdate,sysdate,5,5
from
(
select
bugfix_id,
decode(max(decode(success_flag, 'Y', 2, 1)),
2, 'Y', 'N') success_flag,
decode(max(decode(bug_status, 'EXPLICIT', 2, 1)),
2, 'EXPLICIT', 'IMPLICIT') bug_status
from
ad_snapshot_bugfixes
where
bugfix_id >= p_min_bug_id and
bugfix_id < p_max_bug_id and
snapshot_id in (select patch_run_id from ad_patch_hist_temp)
group by
bugfix_id
having
count(distinct decode(success_flag, 'Y', 2, 1)) = 1);
insert into ad_check_file_temp (
file_version_id,
app_short_name , active_flag,
check_file_id,subdir,filename,
manifest_vers,translation_level,effective_date)
select
bugfix_id,
bug_status, success_flag,
(decode(success_flag,'Y',1,2) * 3 +
decode(bug_status,'EXPLICIT',1,'IMPLICIT',2,3)) bug_rank ,
'NA','NA','NA',0,sysdate
from
(
select
bugfix_id,
decode(max(decode(success_flag, 'Y', 2, 1)),
2, 'Y', 'N') success_flag,
decode(max(decode(bug_status, 'EXPLICIT', 2, 1)),
2, 'EXPLICIT', 'IMPLICIT') bug_status
from
ad_snapshot_bugfixes
where
bugfix_id >= p_min_bug_id and
bugfix_id < p_max_bug_id and
snapshot_id in (select patch_run_id from ad_patch_hist_temp)
group by bugfix_id
having count(distinct decode(success_flag, 'Y', 2, 1)) >1);
execute immediate 'insert into ad_snapshot_bugfixes
(
SNAPSHOT_BUG_ID,
snapshot_id, bugfix_id,
inconsistent_flag,
bug_status, success_flag,
creation_date,last_update_date,last_updated_by,
created_by
)
select
ad_snapshot_bugfixes_s.nextval,:snp_id,
file_version_id,
''Y'',
app_short_name , active_flag,
sysdate,sysdate,5,5
from
(
select
file_version_id,
app_short_name , active_flag,rnk
from
(
select
file_version_id,
app_short_name , active_flag,
ROW_NUMBER() over
(
PARTITION BY file_version_id order by
check_file_id
) rnk
from
ad_check_file_temp
)
) where rnk=1 ' using v_global_snapshot_id;
update ad_prepmode_check_files cf
set version = (select t.manifest_vers
from ad_check_file_temp t
where t.app_short_name = cf.app_short_name
and t.subdir = cf.subdir
and t.filename = cf.filename
and nvl(t.distinguisher, '~') = cf.distinguisher)
where (app_short_name, subdir, filename, distinguisher) in
(select app_short_name, subdir, filename, nvl(distinguisher, '~')
from ad_check_file_temp
where manifest_vers is not null);
insert into ad_prepmode_check_files cf
(
app_short_name, subdir, filename, distinguisher,
version
)
select distinct app_short_name, subdir, filename, nvl(distinguisher, '~'),
manifest_vers
from ad_check_file_temp t
where t.manifest_vers is not null
and not exists (
select null
from ad_prepmode_check_files cf2
where cf2.app_short_name = t.app_short_name
and cf2.subdir = t.subdir
and cf2.filename = t.filename
and cf2.distinguisher = nvl(t.distinguisher, '~'));
delete from ad_prepmode_check_files;
SELECT
file_id,check_file_id,
server_type_admin_flag,
server_type_forms_flag,
server_type_node_flag,
server_type_web_flag
FROM ad_check_file_temp;
'select file_id,check_file_id,server_type_admin_flag, '||
'server_type_forms_flag, server_type_node_flag, '||
'server_type_web_flag from '||
'ad_check_file_temp):(';
update
ad_snapshot_files snap
set
snap.server_type_admin_flag = admin_server_flag_list(j),
snap.server_type_forms_flag = forms_server_flag_list(j),
snap.server_type_node_flag = node_server_flag_list(j),
snap.server_type_web_flag = web_server_flag_list(j)
where
snap.snapshot_id=snp_id and
snap.file_id =fl_id_list(j) and
nvl(snap.containing_file_id,-1)=nvl(con_file_id_list(j),-1);