[Home] [Help]
61: -- Procedure
62: -- lock_and_empty_temp_table
63: --
64: -- Purpose
65: -- Serializes access to the AD_CHECK_FILE_TEMP table using a User Lock
66: -- (created using DBMS_LOCK mgmt services), and also empties the table.
67: -- This lock would be a session-level lock, and is intended to be released
68: -- when the calling script is totally done with its use of the temp table.
69: --
67: -- This lock would be a session-level lock, and is intended to be released
68: -- when the calling script is totally done with its use of the temp table.
69: --
70: -- This is especially necessary when we have multiple scripts that use
71: -- the infrastructure built around AD_CHECK_FILE_TEMP, that perhaps could
72: -- be running in parallel. As of 2/25/02, we already a case for
73: -- this, viz. the snapshot preseeding scripts and the checkfile preseeding
74: -- scripts use the same temp table. In the absence of such a serializing
75: -- facility, they could end up stamping on each others feet (eg. creating
75: -- facility, they could end up stamping on each others feet (eg. creating
76: -- bugs as files and files as bugs!!)
77: --
78: -- Usage
79: -- Any script that uses the AD_CHECK_FILE_TEMP infrastructure must do the
80: -- following:
81: -- a) Call lock_and_empty_temp_table
82: -- b) Insert rows into AD_CHECK_FILE_TEMP
83: -- c) Gather statistics on AD_CHECK_FILE_TEMP
78: -- Usage
79: -- Any script that uses the AD_CHECK_FILE_TEMP infrastructure must do the
80: -- following:
81: -- a) Call lock_and_empty_temp_table
82: -- b) Insert rows into AD_CHECK_FILE_TEMP
83: -- c) Gather statistics on AD_CHECK_FILE_TEMP
84: -- d) Call the relevant packaged-procedure that reads the temp table and
85: -- loads whatever is necessary.
86: -- e) Commit.
79: -- Any script that uses the AD_CHECK_FILE_TEMP infrastructure must do the
80: -- following:
81: -- a) Call lock_and_empty_temp_table
82: -- b) Insert rows into AD_CHECK_FILE_TEMP
83: -- c) Gather statistics on AD_CHECK_FILE_TEMP
84: -- d) Call the relevant packaged-procedure that reads the temp table and
85: -- loads whatever is necessary.
86: -- e) Commit.
87: --
96: (p_un_fnd varchar2) is
97: begin
98: lock_infrastructure;
99:
100: ad_file_util.error_buf := 'truncate ad_check_file_temp';
101:
102: execute immediate 'truncate table '||p_un_fnd||'.ad_check_file_temp';
103:
104: exception when others then
98: lock_infrastructure;
99:
100: ad_file_util.error_buf := 'truncate ad_check_file_temp';
101:
102: execute immediate 'truncate table '||p_un_fnd||'.ad_check_file_temp';
103:
104: exception when others then
105: ad_file_util.error_buf := 'lock_and_empty_temp_table('||
106: ad_file_util.error_buf||
113: -- Procedure
114: -- load_file_info
115: --
116: -- Purpose
117: -- Imports file information from ad_check_file_temp to ad_files
118: --
119: -- Only creates rows that don't already exist.
120: --
121: -- Processes all rows in ad_check_file_temp with active_flag='Y'.
117: -- Imports file information from ad_check_file_temp to ad_files
118: --
119: -- Only creates rows that don't already exist.
120: --
121: -- Processes all rows in ad_check_file_temp with active_flag='Y'.
122: --
123: -- To handle batch sizes:
124: --
125: -- 1) - fill up whole table with null active_flag
131: --
132: -- 2) perform (truncate, load, process) cycles in an outer loop where
133: -- only
134: --
135: -- Updates the file_id column of ad_check_file_temp so that all
136: -- rows point to the file_id of the file referenced in the row.
137: --
138: -- Arguments
139: -- none
150: --
151: -- set junk to null to free up space in row and avoid row chaining
152: --
153: ad_file_util.error_buf := 'load_file_info('||
154: ' update ad_check_file_temp t '||
155: 'set t.file_id = (select f.file_id '||
156: 'from ad_files f '||
157: 'where f.app_short_name = t.app_short_name '||
158: 'and f.subdir = t.subdir '||
159: 'and f.filename = t.filename), '||
160: 't.junk = null '||
161: 'where nvl(t.active_flag,''N'') = ''Y'';):(';
162: begin
163: update ad_check_file_temp t
164: set t.file_id =
165: (select /*+ INDEX(F AD_FILES_U2) */ f.file_id
166: from ad_files f
167: where f.app_short_name = t.app_short_name
187: 'temp.edate, 5 '||
188: 'from (select distinct t.app_short_name asn, '||
189: 't.subdir dir, t.filename fname, '||
190: 't.effective_date edate from '||
191: 'ad_check_file_temp t where t.file_id is null '||
192: ' and nvl(t.active_flag,''N'') = ''Y'') temp;):(';
193:
194: begin
195: insert into ad_files
203: t.app_short_name asn,
204: t.subdir dir,
205: t.filename fname,
206: t.effective_date edate
207: from ad_check_file_temp t
208: where t.file_id is null
209: and nvl(t.active_flag,'N') = 'Y') temp;
210: exception
211: when others then
216: --
217: -- add file_id for new entries
218: --
219: ad_file_util.error_buf := 'load_file_info('||
220: 'update ad_check_file_temp t set t.file_id = '||
221: '(select f.file_id from ad_files f '||
222: 'where f.app_short_name = t.app_short_name '||
223: 'and f.subdir = t.subdir '||
224: 'and f.filename = t.filename) '||
225: 'where t.file_id is null '||
226: 'and nvl(t.active_flag,''N'') = ''Y'';):(';
227:
228: begin
229: update ad_check_file_temp t
230: set t.file_id =
231: (select /*+ INDEX(F AD_FILES_U2) */ f.file_id
232: from ad_files f
233: where f.app_short_name = t.app_short_name
251: -- get dest_file_id from ad_files
252: --
253:
254: ad_file_util.error_buf := 'load_file_info('||
255: ' update ad_check_file_temp t '||
256: 'set t.dest_file_id = (select f.file_id '||
257: 'from ad_files f '||
258: 'where f.app_short_name = t.dest_apps_short_name '||
259: 'and f.subdir = t.dest_subdir '||
259: 'and f.subdir = t.dest_subdir '||
260: 'and f.filename = t.dest_filename) '||
261: 'where nvl(t.active_flag,''N'') = ''Y'';):(';
262: begin
263: update ad_check_file_temp t
264: set t.dest_file_id =
265: (select /*+ INDEX(F AD_FILES_U2) */ f.file_id
266: from ad_files f
267: where f.app_short_name = t.dest_apps_short_name
287: 'temp.edate, 5 '||
288: 'from (select distinct t.dest_apps_short_name asn, '||
289: 't.dest_subdir dir, t.dest_filename fname, '||
290: 't.effective_date edate from '||
291: 'ad_check_file_temp t where t.dest_file_id is null '||
292: ' and t.dest_filename is not null '||
293: ' and nvl(t.active_flag,''N'') = ''Y'') temp;):(';
294:
295: begin
304: t.dest_apps_short_name asn,
305: t.dest_subdir dir,
306: t.dest_filename fname,
307: t.effective_date edate
308: from ad_check_file_temp t
309: where t.dest_file_id is null
310: and t.dest_filename is not null
311: and t.dest_filename <> 'none'
312: and nvl(t.active_flag,'N') = 'Y') temp;
320: -- add dest_file_id for new entries
321: --
322:
323: ad_file_util.error_buf := 'load_file_info('||
324: 'update ad_check_file_temp t set t.dest_file_id ='||
325: '(select f.file_id from ad_files f '||
326: 'where f.app_short_name = t.dest_apps_short_name'||
327: 'and f.subdir = t.dest_subdir '||
328: 'and f.filename = t.dest_filename) '||
330: 'and t.dest_filename is not null '||
331: 'and nvl(t.active_flag,''N'') = ''Y'';):(';
332:
333: begin
334: update ad_check_file_temp t
335: set t.dest_file_id =
336: (select /*+ INDEX(F AD_FILES_U2) */ f.file_id
337: from ad_files f
338: where f.app_short_name = t.dest_apps_short_name
358: -- Procedure
359: -- load_file_version_info
360: --
361: -- Purpose
362: -- Imports file information from ad_check_file_temp to ad_files and
363: -- ad_file_versions.
364: --
365: -- Only creates rows that don't already exist.
366: --
363: -- ad_file_versions.
364: --
365: -- Only creates rows that don't already exist.
366: --
367: -- Processes all rows in ad_check_file_temp with active_flag='Y'.
368: --
369: -- To handle batch sizes:
370: --
371: -- 1) - fill up whole table with null active_flag
379: -- only
380: --
381: -- Calls load_file_info
382: --
383: -- Updates the file_version_id column of ad_check_file_temp so that all
384: -- rows point to the file_version_id of the file version referenced
385: -- in the row.
386: --
387: -- Arguments
409: --
410: -- get file_version_id from ad_file_versions
411: --
412: ad_file_util.error_buf := 'load_file_version_info('||
413: 'update ad_check_file_temp t '||
414: 'set t.file_version_id = '||
415: '(select fv.file_version_id '||
416: 'from ad_file_versions fv '||
417: 'where fv.file_id = t.file_id '||
420: 't.translation_level) '||
421: 'where nvl(t.active_flag,''N'') = ''Y'' '||
422: 'and lower(t.manifest_vers)<>''none'';):(';
423: begin
424: update ad_check_file_temp t
425: set t.file_version_id =
426: (select /*+ INDEX(FV AD_FILE_VERSIONS_U2) */ fv.file_version_id
427: from ad_file_versions fv
428: where fv.file_id = t.file_id
526: (instr(t.manifest_vers||'.','.',1,9)+1),
527: ( (instr(t.manifest_vers||'.','.',1,10))
528: - (instr(t.manifest_vers||'.','.',1,9)+1)) ))) vs10,
529: t.effective_date edate
530: from ad_check_file_temp t
531: where t.file_version_id is null
532: and lower(t.manifest_vers) <> 'none'
533: and nvl(t.active_flag,'N') = 'Y'
534: ) temp;
542: --
543: -- get file_version_id for new entries
544: --
545: ad_file_util.error_buf :='load_file_version_info('||
546: 'update ad_check_file_temp t '||
547: 'set t.file_version_id = '||
548: '(select fv.file_version_id '||
549: 'from ad_file_versions fv '||
550: 'where fv.file_id = t.file_id '||
555: 'and lower(t.manifest_vers)<>''none'';):(';
556:
557:
558: begin
559: update ad_check_file_temp t
560: set t.file_version_id =
561: (select /*+ INDEX(FV AD_FILE_VERSIONS_U2) */ fv.file_version_id
562: from ad_file_versions fv
563: where fv.file_id = t.file_id
582: -- Procedure
583: -- load_checkfile_info
584: --
585: -- Purpose
586: -- Imports file information from ad_check_file_temp to ad_files,
587: -- ad_file_versions, and ad_check_files.
588: --
589: -- Only creates rows in ad_files and ad_file_versions that don't
590: -- already exist. In ad_check_files, it creates rows that don't already
590: -- already exist. In ad_check_files, it creates rows that don't already
591: -- exist and also updates existing rows if the version to load is higher
592: -- than the current version in ad_check_files.
593: --
594: -- Processes all rows in ad_check_file_temp with active_flag='Y'.
595: --
596: -- To handle batch sizes:
597: --
598: -- 1) - fill up whole table with null active_flag
606: -- only
607: --
608: -- Calls load_file_version_info
609: --
610: -- Updates the check_file_id column of ad_check_file_temp so that any
611: -- rows that were already in ad_check_files point to the check_file_id
612: -- of the (file, distinguisher) referenced in the row. Rows in
613: -- ad_check_file_temp that did not already have corresponding rows in
614: -- ad_check_files still have null values for check_file_id
609: --
610: -- Updates the check_file_id column of ad_check_file_temp so that any
611: -- rows that were already in ad_check_files point to the check_file_id
612: -- of the (file, distinguisher) referenced in the row. Rows in
613: -- ad_check_file_temp that did not already have corresponding rows in
614: -- ad_check_files still have null values for check_file_id
615: -- (assuming they started out as null)
616: --
617: -- Arguments
639: --
640: -- get check_file_id and manifest_vers_higher
641: --
642: ad_file_util.error_buf := 'load_checkfile_info('||
643: 'update ad_check_file_temp t '||
644: 'set t.check_file_id = '||
645: '(select cf.check_file_id '||
646: 'from ad_check_files cf '||
647: 'where cf.file_id = t.file_id '||
649: ' nvl(t.distinguisher,''*null*'')), '||
650: 't.manifest_vers_higher =.....);):(';
651:
652: begin
653: update ad_check_file_temp t
654: set t.check_file_id =
655: (select /*+ INDEX(CF AD_CHECK_FILES_U2) */ cf.check_file_id
656: from ad_check_files cf
657: where cf.file_id = t.file_id
706: 'temp.edate from (select distinct '||
707: 't.file_id f_id, t.distinguisher dist, '||
708: 't.file_version_id fv_id, '||
709: 't.effective_date edate from '||
710: 'ad_check_file_temp t where t.check_file_id '||
711: 'is null and nvl(t.active_flag,''N'') = '||
712: '''Y'') temp;):(';
713:
714: begin
722: t.file_id f_id,
723: t.distinguisher dist,
724: t.file_version_id fv_id,
725: t.effective_date edate
726: from ad_check_file_temp t
727: where t.check_file_id is null
728: and nvl(t.active_flag,'N') = 'Y') temp;
729:
730: exception
742: ad_file_util.error_buf := 'load_checkfile_info('||
743: 'insert into ad_check_file_history ' ||
744: '(CHECK_FILE_ID, FILE_VERSION_ID, PATCH_RUN_ID, CREATION_DATE) ' ||
745: 'select distinct acf.check_file_id, acf.file_version_id, -1, sysdate ' ||
746: 'from ad_check_files acf, ad_check_file_temp acft ' ||
747: ' where acft.check_file_id is null ' ||
748: ' and nvl(acft.active_flag,''N'') = ''Y'' ' ||
749: ' and acf.file_id=acft.file_id ' ||
750: ' and nvl(acf.distinguisher, ''x'')=nvl(acft.distinguisher, ''x''))';
752: begin
753: insert into ad_check_file_history
754: (CHECK_FILE_ID, FILE_VERSION_ID, PATCH_RUN_ID, CREATION_DATE)
755: select distinct acf.check_file_id, acf.file_version_id, -1, sysdate
756: from ad_check_files acf, ad_check_file_temp acft
757: where acft.check_file_id is null
758: and nvl(acft.active_flag,'N') = 'Y'
759: and acf.file_id=acft.file_id
760: and nvl(acf.distinguisher, 'x')=nvl(acft.distinguisher, 'x');
772: ad_file_util.error_buf := 'load_checkfile_info('||
773: 'delete from ad_check_files kf '||
774: 'where cf.check_file_id in '||
775: '(select t.check_file_id '||
776: 'from ad_check_file_temp t '||
777: 'where t.manifest_vers_higher = ''Y'' '||
778: 'and nvl(t.active_flag,''N'') = ''Y'');):(';
779:
780: begin
780: begin
781: delete /*+ INDEX(CF AD_CHECK_FILES_U1) */ from ad_check_files cf
782: where cf.check_file_id in
783: (select t.check_file_id
784: from ad_check_file_temp t
785: where t.manifest_vers_higher = 'Y'
786: and nvl(t.active_flag,'N') = 'Y');
787: exception
788: when others then
803: 't.file_id f_id, '||
804: 't.distinguisher dist, '||
805: 't.file_version_id fv_id, '||
806: 't.effective_date edate '||
807: 'from ad_check_file_temp t '||
808: 'where t.manifest_vers_higher = ''Y'' '||
809: 'and nvl(t.active_flag,''N'') = ''Y'') temp;):(';
810:
811: begin
820: t.file_id f_id,
821: t.distinguisher dist,
822: t.file_version_id fv_id,
823: t.effective_date edate
824: from ad_check_file_temp t
825: where t.manifest_vers_higher = 'Y'
826: and nvl(t.active_flag,'N') = 'Y') temp;
827:
828: exception
839: ad_file_util.error_buf := 'load_checkfile_info('||
840: 'insert into ad_check_file_history ' ||
841: ' (CHECK_FILE_ID, FILE_VERSION_ID, PATCH_RUN_ID, CREATION_DATE) ' ||
842: 'select distinct acf.check_file_id, acf.file_version_id, -1, sysdate ' ||
843: ' from ad_check_files acf, ad_check_file_temp acft ' ||
844: ' where acft.manifest_vers_higher = ''Y'' ' ||
845: ' and nvl(acft.active_flag,''N'') = ''Y'' ' ||
846: ' and acf.file_id=acft.file_id ' ||
847: ' and acf.distinguisher=acft.distinguisher)';
849: begin
850: insert into ad_check_file_history
851: (CHECK_FILE_ID, FILE_VERSION_ID, PATCH_RUN_ID, CREATION_DATE)
852: select distinct acf.check_file_id, acf.file_version_id, -1, sysdate
853: from ad_check_files acf, ad_check_file_temp acft
854: where acft.manifest_vers_higher = 'Y'
855: and nvl(acft.active_flag,'N') = 'Y'
856: and acf.file_id=acft.file_id
857: and nvl(acf.distinguisher, 'x')=nvl(acft.distinguisher, 'x');
955: -- Procedure
956: -- load_patch_onsite_vers_info
957: --
958: -- Purpose
959: -- Imports file information from ad_check_file_temp to ad_files and
960: -- ad_file_versions.
961: --
962: -- Only creates rows that don't already exist.
963: --
960: -- ad_file_versions.
961: --
962: -- Only creates rows that don't already exist.
963: --
964: -- Processes all rows in ad_check_file_temp with active_flag='Y'.
965: --
966: -- To handle batch sizes:
967: --
968: -- 1) - fill up whole table with null active_flag
977: --
978: -- Calls load_file_info
979: --
980: -- Updates the file_version_id and file_version_id_2 columns of
981: -- ad_check_file_temp so that all rows point to the file_version_id
982: -- of the file versions referenced in the row.
983: --
984: -- Doesn't try to update ad_file_versions for rows in ad_check_file_temp
985: -- with manifest_vers='NA' or manifest_vers_2='NA'. These values mean
980: -- Updates the file_version_id and file_version_id_2 columns of
981: -- ad_check_file_temp so that all rows point to the file_version_id
982: -- of the file versions referenced in the row.
983: --
984: -- Doesn't try to update ad_file_versions for rows in ad_check_file_temp
985: -- with manifest_vers='NA' or manifest_vers_2='NA'. These values mean
986: -- "no version for this file", so no corresponding record should be
987: -- created in ad_file_versions.
988: --
1001: --
1002: -- process ad_file_versons
1003: --
1004: ad_file_util.error_buf := 'load_patch_onsite_vers_info('||
1005: 'update ad_check_file_temp t '||
1006: 'set t.file_version_id = '||
1007: '(select fv.file_version_id '||
1008: 'from ad_file_versions fv '||
1009: 'where fv.file_id = t.file_id '||
1012: 'where nvl(t.active_flag,''N'') = ''Y'' '||
1013: 'and nvl(t.manifest_vers,''NA'')<>''NA''):(';
1014:
1015: begin
1016: update ad_check_file_temp t
1017: set t.file_version_id =
1018: (select /*+ INDEX(FV AD_FILE_VERSIONS_U2) */ fv.file_version_id
1019: from ad_file_versions fv
1020: where fv.file_id = t.file_id
1108: (instr(t.manifest_vers||'.','.',1,9)+1),
1109: ( (instr(t.manifest_vers||'.','.',1,10))
1110: - (instr(t.manifest_vers||'.','.',1,9)+1)) ))) vs10,
1111: t.effective_date edate
1112: from ad_check_file_temp t
1113: where t.file_version_id is null
1114: and nvl(t.active_flag,'N') = 'Y'
1115: and nvl(t.manifest_vers,'NA')<>'NA') temp;
1116: exception
1120: end;
1121: --
1122: --
1123: ad_file_util.error_buf := 'load_patch_onsite_vers_info('||
1124: 'update ad_check_file_temp t '||
1125: 'set t.file_version_id = '||
1126: '(select fv.file_version_id '||
1127: 'from ad_file_versions fv '||
1128: 'where fv.file_id = t.file_id '||
1132: 'and nvl(t.manifest_vers,''NA'')<>''NA''):(';
1133: --
1134: --
1135: begin
1136: update ad_check_file_temp t
1137: set t.file_version_id =
1138: (select /*+ INDEX(FV AD_FILE_VERSIONS_U2) */ fv.file_version_id
1139: from ad_file_versions fv
1140: where fv.file_id = t.file_id
1150: --
1151: -- get file_version_id_2 from ad_file_versions
1152: --
1153: ad_file_util.error_buf := 'load_patch_onsite_vers_info('||
1154: 'update ad_check_file_temp t '||
1155: 'set t.file_version_id_2 = '||
1156: '(select fv.file_version_id '||
1157: 'from ad_file_versions fv '||
1158: 'where fv.file_id = t.file_id '||
1160: 'nvl(t.manifest_vers_2,''NA'')<>''NA''):(';
1161: --
1162: --
1163: begin
1164: update ad_check_file_temp t
1165: set t.file_version_id_2 =
1166: (select /*+ INDEX(FV AD_FILE_VERSIONS_U2) */ fv.file_version_id
1167: from ad_file_versions fv
1168: where fv.file_id = t.file_id
1258: (instr(t.manifest_vers_2||'.','.',1,9)+1),
1259: ( (instr(t.manifest_vers_2||'.','.',1,10))
1260: - (instr(t.manifest_vers_2||'.','.',1,9)+1)) ))) vs10,
1261: t.effective_date edate
1262: from ad_check_file_temp t
1263: where t.file_version_id_2 is null
1264: and nvl(t.active_flag,'N') = 'Y'
1265: and nvl(t.manifest_vers_2,'NA')<>'NA') temp;
1266: exception
1271: --
1272: -- get file_version_id_2 from ad_file_versions
1273: --
1274: ad_file_util.error_buf := 'load_patch_onsite_vers_info('||
1275: 'update ad_check_file_temp t '||
1276: 'set t.file_version_id_2 = '||
1277: '(select fv.file_version_id '||
1278: 'from ad_file_versions fv '||
1279: 'where fv.file_id = t.file_id '||
1279: 'where fv.file_id = t.file_id '||
1280: 'and fv.version = t.manifest_vers_2 '||
1281: 'nvl(t.manifest_vers_2,''NA'')<>''NA''):(';
1282: begin
1283: update ad_check_file_temp t
1284: set t.file_version_id_2 =
1285: (select /*+ INDEX(FV AD_FILE_VERSIONS_U2) */ fv.file_version_id
1286: from ad_file_versions fv
1287: where fv.file_id = t.file_id
1307: --
1308: -- Purpose
1309: -- Create Snapshot data by
1310: -- 1.Calls ad_file_versions and loads the file versions
1311: -- into the ad_check_file_temp table .
1312: -- 2.Updates rows in AD_SNAPSHOT_FILES from ad_check_file_temp
1313: -- which have the same file_id, snapshot_id and containing_file_id
1314: -- 3.Inserts those rows from ad_check_file_temp into AD_SNAPSHOT_FILES
1315: -- which exists in ad_check_file_temp but are not in AD_SNAPSHOT_FILES.
1308: -- Purpose
1309: -- Create Snapshot data by
1310: -- 1.Calls ad_file_versions and loads the file versions
1311: -- into the ad_check_file_temp table .
1312: -- 2.Updates rows in AD_SNAPSHOT_FILES from ad_check_file_temp
1313: -- which have the same file_id, snapshot_id and containing_file_id
1314: -- 3.Inserts those rows from ad_check_file_temp into AD_SNAPSHOT_FILES
1315: -- which exists in ad_check_file_temp but are not in AD_SNAPSHOT_FILES.
1316: -- for the given snapshot_id
1310: -- 1.Calls ad_file_versions and loads the file versions
1311: -- into the ad_check_file_temp table .
1312: -- 2.Updates rows in AD_SNAPSHOT_FILES from ad_check_file_temp
1313: -- which have the same file_id, snapshot_id and containing_file_id
1314: -- 3.Inserts those rows from ad_check_file_temp into AD_SNAPSHOT_FILES
1315: -- which exists in ad_check_file_temp but are not in AD_SNAPSHOT_FILES.
1316: -- for the given snapshot_id
1317: -- 4.Delete those rows from AD_SNAPSHOT_FILES which exists
1318: -- in AD_SNAPSHOT_FILES but do not exist in ad_check_file_temp
1311: -- into the ad_check_file_temp table .
1312: -- 2.Updates rows in AD_SNAPSHOT_FILES from ad_check_file_temp
1313: -- which have the same file_id, snapshot_id and containing_file_id
1314: -- 3.Inserts those rows from ad_check_file_temp into AD_SNAPSHOT_FILES
1315: -- which exists in ad_check_file_temp but are not in AD_SNAPSHOT_FILES.
1316: -- for the given snapshot_id
1317: -- 4.Delete those rows from AD_SNAPSHOT_FILES which exists
1318: -- in AD_SNAPSHOT_FILES but do not exist in ad_check_file_temp
1319: -- for the given snapshot_id
1314: -- 3.Inserts those rows from ad_check_file_temp into AD_SNAPSHOT_FILES
1315: -- which exists in ad_check_file_temp but are not in AD_SNAPSHOT_FILES.
1316: -- for the given snapshot_id
1317: -- 4.Delete those rows from AD_SNAPSHOT_FILES which exists
1318: -- in AD_SNAPSHOT_FILES but do not exist in ad_check_file_temp
1319: -- for the given snapshot_id
1320: --
1321: -- Arguments
1322: -- is_upload pass TRUE if it is an upload otherwise FALSE
1325: procedure load_snapshot_file_info
1326: (snp_id number,
1327: preserve_irep_flag number)
1328: is
1329: TYPE t_version_id IS TABLE OF ad_check_file_temp.file_version_id%TYPE;
1330: TYPE t_check_sum IS TABLE OF ad_check_file_temp.check_sum%TYPE;
1331: TYPE t_file_size IS TABLE OF ad_check_file_temp.file_size%TYPE;
1332: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
1333: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
1326: (snp_id number,
1327: preserve_irep_flag number)
1328: is
1329: TYPE t_version_id IS TABLE OF ad_check_file_temp.file_version_id%TYPE;
1330: TYPE t_check_sum IS TABLE OF ad_check_file_temp.check_sum%TYPE;
1331: TYPE t_file_size IS TABLE OF ad_check_file_temp.file_size%TYPE;
1332: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
1333: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
1334: TYPE t_dest_file_id IS TABLE OF ad_check_file_temp.dest_file_id%TYPE;
1327: preserve_irep_flag number)
1328: is
1329: TYPE t_version_id IS TABLE OF ad_check_file_temp.file_version_id%TYPE;
1330: TYPE t_check_sum IS TABLE OF ad_check_file_temp.check_sum%TYPE;
1331: TYPE t_file_size IS TABLE OF ad_check_file_temp.file_size%TYPE;
1332: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
1333: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
1334: TYPE t_dest_file_id IS TABLE OF ad_check_file_temp.dest_file_id%TYPE;
1335: TYPE t_file_type_flag IS TABLE OF ad_check_file_temp.file_type_flag%TYPE;
1328: is
1329: TYPE t_version_id IS TABLE OF ad_check_file_temp.file_version_id%TYPE;
1330: TYPE t_check_sum IS TABLE OF ad_check_file_temp.check_sum%TYPE;
1331: TYPE t_file_size IS TABLE OF ad_check_file_temp.file_size%TYPE;
1332: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
1333: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
1334: TYPE t_dest_file_id IS TABLE OF ad_check_file_temp.dest_file_id%TYPE;
1335: TYPE t_file_type_flag IS TABLE OF ad_check_file_temp.file_type_flag%TYPE;
1336: TYPE t_irep_gathered_flag IS TABLE OF ad_check_file_temp.manifest_vers_higher%TYPE;
1329: TYPE t_version_id IS TABLE OF ad_check_file_temp.file_version_id%TYPE;
1330: TYPE t_check_sum IS TABLE OF ad_check_file_temp.check_sum%TYPE;
1331: TYPE t_file_size IS TABLE OF ad_check_file_temp.file_size%TYPE;
1332: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
1333: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
1334: TYPE t_dest_file_id IS TABLE OF ad_check_file_temp.dest_file_id%TYPE;
1335: TYPE t_file_type_flag IS TABLE OF ad_check_file_temp.file_type_flag%TYPE;
1336: TYPE t_irep_gathered_flag IS TABLE OF ad_check_file_temp.manifest_vers_higher%TYPE;
1337: TYPE t_effective_date IS TABLE OF ad_check_file_temp.effective_date%TYPE;
1330: TYPE t_check_sum IS TABLE OF ad_check_file_temp.check_sum%TYPE;
1331: TYPE t_file_size IS TABLE OF ad_check_file_temp.file_size%TYPE;
1332: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
1333: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
1334: TYPE t_dest_file_id IS TABLE OF ad_check_file_temp.dest_file_id%TYPE;
1335: TYPE t_file_type_flag IS TABLE OF ad_check_file_temp.file_type_flag%TYPE;
1336: TYPE t_irep_gathered_flag IS TABLE OF ad_check_file_temp.manifest_vers_higher%TYPE;
1337: TYPE t_effective_date IS TABLE OF ad_check_file_temp.effective_date%TYPE;
1338:
1331: TYPE t_file_size IS TABLE OF ad_check_file_temp.file_size%TYPE;
1332: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
1333: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
1334: TYPE t_dest_file_id IS TABLE OF ad_check_file_temp.dest_file_id%TYPE;
1335: TYPE t_file_type_flag IS TABLE OF ad_check_file_temp.file_type_flag%TYPE;
1336: TYPE t_irep_gathered_flag IS TABLE OF ad_check_file_temp.manifest_vers_higher%TYPE;
1337: TYPE t_effective_date IS TABLE OF ad_check_file_temp.effective_date%TYPE;
1338:
1339: --
1332: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
1333: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
1334: TYPE t_dest_file_id IS TABLE OF ad_check_file_temp.dest_file_id%TYPE;
1335: TYPE t_file_type_flag IS TABLE OF ad_check_file_temp.file_type_flag%TYPE;
1336: TYPE t_irep_gathered_flag IS TABLE OF ad_check_file_temp.manifest_vers_higher%TYPE;
1337: TYPE t_effective_date IS TABLE OF ad_check_file_temp.effective_date%TYPE;
1338:
1339: --
1340: vers_id_list t_version_id;
1333: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
1334: TYPE t_dest_file_id IS TABLE OF ad_check_file_temp.dest_file_id%TYPE;
1335: TYPE t_file_type_flag IS TABLE OF ad_check_file_temp.file_type_flag%TYPE;
1336: TYPE t_irep_gathered_flag IS TABLE OF ad_check_file_temp.manifest_vers_higher%TYPE;
1337: TYPE t_effective_date IS TABLE OF ad_check_file_temp.effective_date%TYPE;
1338:
1339: --
1340: vers_id_list t_version_id;
1341: chk_sum_list t_check_sum;
1355: file_version_id,check_sum,file_size,
1356: file_id,check_file_id,dest_file_id,
1357: file_type_flag, manifest_vers_higher,
1358: effective_date
1359: from ad_check_file_temp;
1360: --
1361: --
1362: --
1363: --
1391: ad_file_util.error_buf := 'load_snapshot_file_info(cursor: '||
1392: 'select file_version_id,check_sum,file_size, '||
1393: 'file_id,check_file_id,dest_file_id, '||
1394: 'file_type_flag from '||
1395: 'ad_check_file_temp):(';
1396:
1397: begin
1398: OPEN c1;
1399: exception
1484: 't.file_size,t.check_sum, t.file_version_id,'||
1485: 'snp_id,''U'',sysdate,sysdate, 5,5,' ||
1486: 't.appl_top_id, t.inconsistent_flag, '||
1487: 't.dest_file_id, t.file_type_flag '||
1488: 'from ad_check_file_temp t where not exists '||
1489: '(select ''already present'' '||
1490: 'from ad_snapshot_files sf2 '||
1491: 'where sf2.snapshot_id = snp_id '||
1492: 'and sf2.file_id = t.file_id '||
1507: t.file_version_id,snp_id,'U',sysdate,sysdate,
1508: 5,5, t.appl_top_id, t.inconsistent_flag,
1509: t.dest_file_id, t.file_type_flag,
1510: t.manifest_vers_higher, t.effective_date
1511: from ad_check_file_temp t
1512: where not exists
1513: (select /*+ INDEX(SF2 AD_SNAPSHOT_FILES_U2) */ 'already present'
1514: from ad_snapshot_files sf2
1515: where sf2.snapshot_id = snp_id
1537: -- load_preseeded_bugfixes
1538: --
1539: -- Purpose
1540: -- Gets the bug_id from AD_BUGS for the bugnumbers in
1541: -- in ad_check_file_temp table .
1542: -- Creates new rows in the AD_BUGS for the new bugnumbers
1543: -- and gets the bug_id for those bugnumbers and stores them
1544: -- ad_check_file_temp table .
1545: --
1540: -- Gets the bug_id from AD_BUGS for the bugnumbers in
1541: -- in ad_check_file_temp table .
1542: -- Creates new rows in the AD_BUGS for the new bugnumbers
1543: -- and gets the bug_id for those bugnumbers and stores them
1544: -- ad_check_file_temp table .
1545: --
1546: -- Inserts those BUG_IDs into AD_SNAPSHOT_BUGFIXES
1547: --
1548: --
1565: 't.junk = NULL '||
1566: 'WHERE NVL(t.active_flag,''N'') = ''Y''):(';
1567: begin
1568: -- Bug 5579901- stangutu - 9 Oct, 2006
1569: UPDATE ad_check_file_temp t
1570: SET t.file_id = (SELECT /*+ INDEX(B AD_BUGS_U2) */ b.bug_id
1571: FROM ad_bugs b
1572: WHERE b.bug_number = t.filename
1573: AND b.aru_release_name = t.subdir
1606: 't.manifest_vers_2 baseline_name, '||
1607: 't.manifest_vers_higher, generic_patch, '||
1608: 't.app_short_name trackable_entity_abbr, ' ||
1609: 't.language language '||
1610: 'FROM ad_check_file_temp t '||
1611: 'WHERE t.file_id is null '||
1612: 'AND NVL(t.active_flag,''N'') = ''Y'') temp):(';
1613: begin
1614: INSERT INTO ad_bugs
1635: t.manifest_vers_higher generic_patch,
1636: t.app_short_name trackable_entity_abbr,
1637: -- bug 5615204 diverma Tuesday, August 07, 2007
1638: t.language language
1639: FROM ad_check_file_temp t
1640: WHERE t.file_id is null
1641: AND NVL(t.active_flag,'N') = 'Y') temp;
1642: exception
1643: when others then
1648: -- add bug_id for new entries
1649: --
1650: -- Bug 5758908 - stangutu - 14 June, 2007
1651: ad_file_util.error_buf := 'load_preseeded_bugfixes('||
1652: 'UPDATE ad_check_file_temp t '||
1653: 'SET t.file_id = (SELECT b.bug_id '||
1654: 'FROM ad_bugs b WHERE b.bug_number = t.filename '||
1655: 'AND b.aru_release_name = t.subdir, '||
1656: 'AND b.trackable_entity_abbr = t.app_short_name' ||
1659: 't.junk = NULL '||
1660: 'WHERE NVL(t.active_flag,''N'') = ''Y''):(';
1661: begin
1662: -- Bug 5579901- stangutu - 9 Oct, 2006
1663: UPDATE ad_check_file_temp t
1664: SET t.file_id = (SELECT /*+ INDEX(B AD_BUGS_U2) */ b.bug_id
1665: FROM ad_bugs b
1666: WHERE b.bug_number = t.filename
1667: AND b.aru_release_name = t.subdir
1692: 'creation_date,last_update_date, '||
1693: 'last_updated_by,created_by) '||
1694: 'SELECT ad_snapshot_bugfixes_s.nextval,'||
1695: 'file_version_id_2, file_id,''EXPLICIT'',''Y'','||
1696: 'sysdate, sysdate,5,5 FROM ad_check_file_temp t '||
1697: 'where not exists (select ''already present'' '||
1698: 'from ad_snapshot_bugfixes b '||
1699: 'where b.BUGFIX_ID=t.file_id and '||
1700: 'b.SNAPSHOT_ID=t.file_version_id_2):(';
1708: SELECT ad_snapshot_bugfixes_s.nextval,file_version_id_2,
1709: file_id,'EXPLICIT','Y',sysdate,
1710: sysdate,5,5
1711: FROM
1712: ad_check_file_temp t
1713: where not exists
1714: (select /*+ INDEX(B AD_SNAPSHOT_BUGFIXES_U2) */ 'already present'
1715: from ad_snapshot_bugfixes b
1716: where b.BUGFIX_ID=t.file_id and
2590: v_current_snapshot_type := 'P';
2591: end if;
2592:
2593: if (p_iteration = 1) then
2594: execute immediate 'truncate table '|| p_un_fnd ||'.ad_check_file_temp';
2595: --
2596: --
2597: insert into ad_check_file_temp
2598: (TRANSLATION_LEVEL,APP_SHORT_NAME,
2593: if (p_iteration = 1) then
2594: execute immediate 'truncate table '|| p_un_fnd ||'.ad_check_file_temp';
2595: --
2596: --
2597: insert into ad_check_file_temp
2598: (TRANSLATION_LEVEL,APP_SHORT_NAME,
2599: SUBDIR,FILENAME, MANIFEST_VERS,
2600: EFFECTIVE_DATE)
2601: select
2662: ad_snapshot_files
2663: where
2664: file_id >= p_min_file_id and
2665: file_id < p_max_file_id and
2666: snapshot_id in (select TRANSLATION_LEVEL from ad_check_file_temp)
2667: group by
2668: file_id
2669: having
2670: count(distinct nvl(file_version_id,0))=1
2703: file_id from ad_snapshot_files
2704: where
2705: file_id >= p_min_file_id and
2706: file_id < p_max_file_id and
2707: snapshot_id in (select TRANSLATION_LEVEL from ad_check_file_temp)
2708: group by
2709: file_id
2710: having
2711: count(distinct nvl(file_version_id,0)) >1
2709: file_id
2710: having
2711: count(distinct nvl(file_version_id,0)) >1
2712: ) and
2713: snapshot_id in (select TRANSLATION_LEVEL from ad_check_file_temp);
2714: --
2715: --
2716: commit;
2717: --
2785: --
2786: --
2787: if (p_iteration = 1) then
2788: FND_STATS.Gather_Table_Stats(p_un_fnd, 'AD_SNAPSHOT_FILES_TEMP');
2789: FND_STATS.Gather_Table_Stats(p_un_fnd, 'AD_CHECK_FILE_TEMP');
2790: FND_STATS.Gather_Table_Stats(p_un_fnd, 'AD_PATCH_HIST_TEMP');
2791: end if;
2792: --
2793: --
2823: v_current_snapshot_type varchar2(1);
2824: begin
2825: --
2826: --
2827: execute immediate 'truncate table '||p_un_fnd||'.ad_check_file_temp';
2828: --
2829: --
2830: if (p_is_run_flow = TRUE)
2831: then
2900: having
2901: count(distinct decode(success_flag, 'Y', 2, 1)) = 1);
2902: --
2903: --
2904: insert into ad_check_file_temp (
2905: file_version_id,
2906: app_short_name , active_flag,
2907: check_file_id,subdir,filename,
2908: manifest_vers,translation_level,effective_date)
2960: PARTITION BY file_version_id order by
2961: check_file_id
2962: ) rnk
2963: from
2964: ad_check_file_temp
2965: )
2966: ) where rnk=1 ' using v_global_snapshot_id;
2967: --
2968: --
2970: --
2971: --
2972: if (p_iteration = 1) then
2973: FND_STATS.Gather_Table_Stats(p_un_fnd, 'AD_SNAPSHOT_FILES_TEMP');
2974: FND_STATS.Gather_Table_Stats(p_un_fnd, 'AD_CHECK_FILE_TEMP');
2975: FND_STATS.Gather_Table_Stats(p_un_fnd, 'AD_PATCH_HIST_TEMP');
2976: end if;
2977: --
2978: --
2983: -- Procedure
2984: -- load_prepmode_checkfile_info
2985: --
2986: -- Purpose
2987: -- Imports file information from ad_check_file_temp to
2988: -- ad_prepmode_check_files, when applying a patch is "prepare" mode.
2989: --
2990: -- Arguments
2991: -- none
2999: --
3000:
3001: update ad_prepmode_check_files cf
3002: set version = (select t.manifest_vers
3003: from ad_check_file_temp t
3004: where t.app_short_name = cf.app_short_name
3005: and t.subdir = cf.subdir
3006: and t.filename = cf.filename
3007: and nvl(t.distinguisher, '~') = cf.distinguisher)
3006: and t.filename = cf.filename
3007: and nvl(t.distinguisher, '~') = cf.distinguisher)
3008: where (app_short_name, subdir, filename, distinguisher) in
3009: (select app_short_name, subdir, filename, nvl(distinguisher, '~')
3010: from ad_check_file_temp
3011: where manifest_vers is not null);
3012:
3013: --
3014: -- insert rows for new files
3020: )
3021: select distinct app_short_name, subdir, filename, nvl(distinguisher, '~'),
3022:
3023: manifest_vers
3024: from ad_check_file_temp t
3025: where t.manifest_vers is not null
3026: and not exists (
3027: select null
3028: from ad_prepmode_check_files cf2
3056: -- load_snpst_file_server_info
3057: --
3058: -- Purpose
3059: -- updates the values of the server flags of the table ad_snapshot_files
3060: -- as obtained from the temporary table ad_check_file_temp
3061: --
3062: -- Arguments
3063: -- snp_id - snapshot_id for which the rows are to be updated
3064: --
3064: --
3065: procedure load_snpst_file_server_info
3066: (snp_id number)
3067: is
3068: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
3069: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
3070: TYPE t_admin_server_flag IS TABLE OF ad_check_file_temp.server_type_admin_flag%TYPE;
3071: TYPE t_forms_server_flag IS TABLE OF ad_check_file_temp.server_type_forms_flag%TYPE;
3072: TYPE t_node_server_flag IS TABLE OF ad_check_file_temp.server_type_node_flag%TYPE;
3065: procedure load_snpst_file_server_info
3066: (snp_id number)
3067: is
3068: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
3069: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
3070: TYPE t_admin_server_flag IS TABLE OF ad_check_file_temp.server_type_admin_flag%TYPE;
3071: TYPE t_forms_server_flag IS TABLE OF ad_check_file_temp.server_type_forms_flag%TYPE;
3072: TYPE t_node_server_flag IS TABLE OF ad_check_file_temp.server_type_node_flag%TYPE;
3073: TYPE t_web_server_flag IS TABLE OF ad_check_file_temp.server_type_web_flag%TYPE;
3066: (snp_id number)
3067: is
3068: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
3069: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
3070: TYPE t_admin_server_flag IS TABLE OF ad_check_file_temp.server_type_admin_flag%TYPE;
3071: TYPE t_forms_server_flag IS TABLE OF ad_check_file_temp.server_type_forms_flag%TYPE;
3072: TYPE t_node_server_flag IS TABLE OF ad_check_file_temp.server_type_node_flag%TYPE;
3073: TYPE t_web_server_flag IS TABLE OF ad_check_file_temp.server_type_web_flag%TYPE;
3074: --
3067: is
3068: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
3069: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
3070: TYPE t_admin_server_flag IS TABLE OF ad_check_file_temp.server_type_admin_flag%TYPE;
3071: TYPE t_forms_server_flag IS TABLE OF ad_check_file_temp.server_type_forms_flag%TYPE;
3072: TYPE t_node_server_flag IS TABLE OF ad_check_file_temp.server_type_node_flag%TYPE;
3073: TYPE t_web_server_flag IS TABLE OF ad_check_file_temp.server_type_web_flag%TYPE;
3074: --
3075: fl_id_list t_file_id;
3068: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
3069: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
3070: TYPE t_admin_server_flag IS TABLE OF ad_check_file_temp.server_type_admin_flag%TYPE;
3071: TYPE t_forms_server_flag IS TABLE OF ad_check_file_temp.server_type_forms_flag%TYPE;
3072: TYPE t_node_server_flag IS TABLE OF ad_check_file_temp.server_type_node_flag%TYPE;
3073: TYPE t_web_server_flag IS TABLE OF ad_check_file_temp.server_type_web_flag%TYPE;
3074: --
3075: fl_id_list t_file_id;
3076: con_file_id_list t_containing_file_id;
3069: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
3070: TYPE t_admin_server_flag IS TABLE OF ad_check_file_temp.server_type_admin_flag%TYPE;
3071: TYPE t_forms_server_flag IS TABLE OF ad_check_file_temp.server_type_forms_flag%TYPE;
3072: TYPE t_node_server_flag IS TABLE OF ad_check_file_temp.server_type_node_flag%TYPE;
3073: TYPE t_web_server_flag IS TABLE OF ad_check_file_temp.server_type_web_flag%TYPE;
3074: --
3075: fl_id_list t_file_id;
3076: con_file_id_list t_containing_file_id;
3077: admin_server_flag_list t_admin_server_flag;
3087: server_type_admin_flag,
3088: server_type_forms_flag,
3089: server_type_node_flag,
3090: server_type_web_flag
3091: FROM ad_check_file_temp;
3092: --
3093: --
3094: --
3095: --
3102: ad_file_util.error_buf := 'load_snpst_file_server_info(cursor: '||
3103: 'select file_id,check_file_id,server_type_admin_flag, '||
3104: 'server_type_forms_flag, server_type_node_flag, '||
3105: 'server_type_web_flag from '||
3106: 'ad_check_file_temp):(';
3107: --
3108: begin
3109: OPEN c1;
3110: exception