[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: exception
730: when others then
738: ad_file_util.error_buf := 'load_checkfile_info('||
739: 'delete from ad_check_files kf '||
740: 'where cf.check_file_id in '||
741: '(select t.check_file_id '||
742: 'from ad_check_file_temp t '||
743: 'where t.manifest_vers_higher = ''Y'' '||
744: 'and nvl(t.active_flag,''N'') = ''Y'');):(';
745:
746: begin
746: begin
747: delete /*+ INDEX(CF AD_CHECK_FILES_U1) */ from ad_check_files cf
748: where cf.check_file_id in
749: (select t.check_file_id
750: from ad_check_file_temp t
751: where t.manifest_vers_higher = 'Y'
752: and nvl(t.active_flag,'N') = 'Y');
753: exception
754: when others then
769: 't.file_id f_id, '||
770: 't.distinguisher dist, '||
771: 't.file_version_id fv_id, '||
772: 't.effective_date edate '||
773: 'from ad_check_file_temp t '||
774: 'where t.manifest_vers_higher = ''Y'' '||
775: 'and nvl(t.active_flag,''N'') = ''Y'') temp;):(';
776:
777: begin
786: t.file_id f_id,
787: t.distinguisher dist,
788: t.file_version_id fv_id,
789: t.effective_date edate
790: from ad_check_file_temp t
791: where t.manifest_vers_higher = 'Y'
792: and nvl(t.active_flag,'N') = 'Y') temp;
793: exception
794: when others then
889: -- Procedure
890: -- load_patch_onsite_vers_info
891: --
892: -- Purpose
893: -- Imports file information from ad_check_file_temp to ad_files and
894: -- ad_file_versions.
895: --
896: -- Only creates rows that don't already exist.
897: --
894: -- ad_file_versions.
895: --
896: -- Only creates rows that don't already exist.
897: --
898: -- Processes all rows in ad_check_file_temp with active_flag='Y'.
899: --
900: -- To handle batch sizes:
901: --
902: -- 1) - fill up whole table with null active_flag
911: --
912: -- Calls load_file_info
913: --
914: -- Updates the file_version_id and file_version_id_2 columns of
915: -- ad_check_file_temp so that all rows point to the file_version_id
916: -- of the file versions referenced in the row.
917: --
918: -- Doesn't try to update ad_file_versions for rows in ad_check_file_temp
919: -- with manifest_vers='NA' or manifest_vers_2='NA'. These values mean
914: -- Updates the file_version_id and file_version_id_2 columns of
915: -- ad_check_file_temp so that all rows point to the file_version_id
916: -- of the file versions referenced in the row.
917: --
918: -- Doesn't try to update ad_file_versions for rows in ad_check_file_temp
919: -- with manifest_vers='NA' or manifest_vers_2='NA'. These values mean
920: -- "no version for this file", so no corresponding record should be
921: -- created in ad_file_versions.
922: --
935: --
936: -- process ad_file_versons
937: --
938: ad_file_util.error_buf := 'load_patch_onsite_vers_info('||
939: 'update ad_check_file_temp t '||
940: 'set t.file_version_id = '||
941: '(select fv.file_version_id '||
942: 'from ad_file_versions fv '||
943: 'where fv.file_id = t.file_id '||
946: 'where nvl(t.active_flag,''N'') = ''Y'' '||
947: 'and nvl(t.manifest_vers,''NA'')<>''NA''):(';
948:
949: begin
950: update ad_check_file_temp t
951: set t.file_version_id =
952: (select /*+ INDEX(FV AD_FILE_VERSIONS_U2) */ fv.file_version_id
953: from ad_file_versions fv
954: where fv.file_id = t.file_id
1042: (instr(t.manifest_vers||'.','.',1,9)+1),
1043: ( (instr(t.manifest_vers||'.','.',1,10))
1044: - (instr(t.manifest_vers||'.','.',1,9)+1)) ))) vs10,
1045: t.effective_date edate
1046: from ad_check_file_temp t
1047: where t.file_version_id is null
1048: and nvl(t.active_flag,'N') = 'Y'
1049: and nvl(t.manifest_vers,'NA')<>'NA') temp;
1050: exception
1054: end;
1055: --
1056: --
1057: ad_file_util.error_buf := 'load_patch_onsite_vers_info('||
1058: 'update ad_check_file_temp t '||
1059: 'set t.file_version_id = '||
1060: '(select fv.file_version_id '||
1061: 'from ad_file_versions fv '||
1062: 'where fv.file_id = t.file_id '||
1066: 'and nvl(t.manifest_vers,''NA'')<>''NA''):(';
1067: --
1068: --
1069: begin
1070: update ad_check_file_temp t
1071: set t.file_version_id =
1072: (select /*+ INDEX(FV AD_FILE_VERSIONS_U2) */ fv.file_version_id
1073: from ad_file_versions fv
1074: where fv.file_id = t.file_id
1084: --
1085: -- get file_version_id_2 from ad_file_versions
1086: --
1087: ad_file_util.error_buf := 'load_patch_onsite_vers_info('||
1088: 'update ad_check_file_temp t '||
1089: 'set t.file_version_id_2 = '||
1090: '(select fv.file_version_id '||
1091: 'from ad_file_versions fv '||
1092: 'where fv.file_id = t.file_id '||
1094: 'nvl(t.manifest_vers_2,''NA'')<>''NA''):(';
1095: --
1096: --
1097: begin
1098: update ad_check_file_temp t
1099: set t.file_version_id_2 =
1100: (select /*+ INDEX(FV AD_FILE_VERSIONS_U2) */ fv.file_version_id
1101: from ad_file_versions fv
1102: where fv.file_id = t.file_id
1192: (instr(t.manifest_vers_2||'.','.',1,9)+1),
1193: ( (instr(t.manifest_vers_2||'.','.',1,10))
1194: - (instr(t.manifest_vers_2||'.','.',1,9)+1)) ))) vs10,
1195: t.effective_date edate
1196: from ad_check_file_temp t
1197: where t.file_version_id_2 is null
1198: and nvl(t.active_flag,'N') = 'Y'
1199: and nvl(t.manifest_vers_2,'NA')<>'NA') temp;
1200: exception
1205: --
1206: -- get file_version_id_2 from ad_file_versions
1207: --
1208: ad_file_util.error_buf := 'load_patch_onsite_vers_info('||
1209: 'update ad_check_file_temp t '||
1210: 'set t.file_version_id_2 = '||
1211: '(select fv.file_version_id '||
1212: 'from ad_file_versions fv '||
1213: 'where fv.file_id = t.file_id '||
1213: 'where fv.file_id = t.file_id '||
1214: 'and fv.version = t.manifest_vers_2 '||
1215: 'nvl(t.manifest_vers_2,''NA'')<>''NA''):(';
1216: begin
1217: update ad_check_file_temp t
1218: set t.file_version_id_2 =
1219: (select /*+ INDEX(FV AD_FILE_VERSIONS_U2) */ fv.file_version_id
1220: from ad_file_versions fv
1221: where fv.file_id = t.file_id
1241: --
1242: -- Purpose
1243: -- Create Snapshot data by
1244: -- 1.Calls ad_file_versions and loads the file versions
1245: -- into the ad_check_file_temp table .
1246: -- 2.Updates rows in AD_SNAPSHOT_FILES from ad_check_file_temp
1247: -- which have the same file_id, snapshot_id and containing_file_id
1248: -- 3.Inserts those rows from ad_check_file_temp into AD_SNAPSHOT_FILES
1249: -- which exists in ad_check_file_temp but are not in AD_SNAPSHOT_FILES.
1242: -- Purpose
1243: -- Create Snapshot data by
1244: -- 1.Calls ad_file_versions and loads the file versions
1245: -- into the ad_check_file_temp table .
1246: -- 2.Updates rows in AD_SNAPSHOT_FILES from ad_check_file_temp
1247: -- which have the same file_id, snapshot_id and containing_file_id
1248: -- 3.Inserts those rows from ad_check_file_temp into AD_SNAPSHOT_FILES
1249: -- which exists in ad_check_file_temp but are not in AD_SNAPSHOT_FILES.
1250: -- for the given snapshot_id
1244: -- 1.Calls ad_file_versions and loads the file versions
1245: -- into the ad_check_file_temp table .
1246: -- 2.Updates rows in AD_SNAPSHOT_FILES from ad_check_file_temp
1247: -- which have the same file_id, snapshot_id and containing_file_id
1248: -- 3.Inserts those rows from ad_check_file_temp into AD_SNAPSHOT_FILES
1249: -- which exists in ad_check_file_temp but are not in AD_SNAPSHOT_FILES.
1250: -- for the given snapshot_id
1251: -- 4.Delete those rows from AD_SNAPSHOT_FILES which exists
1252: -- in AD_SNAPSHOT_FILES but do not exist in ad_check_file_temp
1245: -- into the ad_check_file_temp table .
1246: -- 2.Updates rows in AD_SNAPSHOT_FILES from ad_check_file_temp
1247: -- which have the same file_id, snapshot_id and containing_file_id
1248: -- 3.Inserts those rows from ad_check_file_temp into AD_SNAPSHOT_FILES
1249: -- which exists in ad_check_file_temp but are not in AD_SNAPSHOT_FILES.
1250: -- for the given snapshot_id
1251: -- 4.Delete those rows from AD_SNAPSHOT_FILES which exists
1252: -- in AD_SNAPSHOT_FILES but do not exist in ad_check_file_temp
1253: -- for the given snapshot_id
1248: -- 3.Inserts those rows from ad_check_file_temp into AD_SNAPSHOT_FILES
1249: -- which exists in ad_check_file_temp but are not in AD_SNAPSHOT_FILES.
1250: -- for the given snapshot_id
1251: -- 4.Delete those rows from AD_SNAPSHOT_FILES which exists
1252: -- in AD_SNAPSHOT_FILES but do not exist in ad_check_file_temp
1253: -- for the given snapshot_id
1254: --
1255: -- Arguments
1256: -- is_upload pass TRUE if it is an upload otherwise FALSE
1259: procedure load_snapshot_file_info
1260: (snp_id number,
1261: preserve_irep_flag number)
1262: is
1263: TYPE t_version_id IS TABLE OF ad_check_file_temp.file_version_id%TYPE;
1264: TYPE t_check_sum IS TABLE OF ad_check_file_temp.check_sum%TYPE;
1265: TYPE t_file_size IS TABLE OF ad_check_file_temp.file_size%TYPE;
1266: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
1267: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
1260: (snp_id number,
1261: preserve_irep_flag number)
1262: is
1263: TYPE t_version_id IS TABLE OF ad_check_file_temp.file_version_id%TYPE;
1264: TYPE t_check_sum IS TABLE OF ad_check_file_temp.check_sum%TYPE;
1265: TYPE t_file_size IS TABLE OF ad_check_file_temp.file_size%TYPE;
1266: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
1267: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
1268: TYPE t_dest_file_id IS TABLE OF ad_check_file_temp.dest_file_id%TYPE;
1261: preserve_irep_flag number)
1262: is
1263: TYPE t_version_id IS TABLE OF ad_check_file_temp.file_version_id%TYPE;
1264: TYPE t_check_sum IS TABLE OF ad_check_file_temp.check_sum%TYPE;
1265: TYPE t_file_size IS TABLE OF ad_check_file_temp.file_size%TYPE;
1266: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
1267: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
1268: TYPE t_dest_file_id IS TABLE OF ad_check_file_temp.dest_file_id%TYPE;
1269: TYPE t_file_type_flag IS TABLE OF ad_check_file_temp.file_type_flag%TYPE;
1262: is
1263: TYPE t_version_id IS TABLE OF ad_check_file_temp.file_version_id%TYPE;
1264: TYPE t_check_sum IS TABLE OF ad_check_file_temp.check_sum%TYPE;
1265: TYPE t_file_size IS TABLE OF ad_check_file_temp.file_size%TYPE;
1266: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
1267: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
1268: TYPE t_dest_file_id IS TABLE OF ad_check_file_temp.dest_file_id%TYPE;
1269: TYPE t_file_type_flag IS TABLE OF ad_check_file_temp.file_type_flag%TYPE;
1270: TYPE t_irep_gathered_flag IS TABLE OF ad_check_file_temp.manifest_vers_higher%TYPE;
1263: TYPE t_version_id IS TABLE OF ad_check_file_temp.file_version_id%TYPE;
1264: TYPE t_check_sum IS TABLE OF ad_check_file_temp.check_sum%TYPE;
1265: TYPE t_file_size IS TABLE OF ad_check_file_temp.file_size%TYPE;
1266: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
1267: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
1268: TYPE t_dest_file_id IS TABLE OF ad_check_file_temp.dest_file_id%TYPE;
1269: TYPE t_file_type_flag IS TABLE OF ad_check_file_temp.file_type_flag%TYPE;
1270: TYPE t_irep_gathered_flag IS TABLE OF ad_check_file_temp.manifest_vers_higher%TYPE;
1271: TYPE t_effective_date IS TABLE OF ad_check_file_temp.effective_date%TYPE;
1264: TYPE t_check_sum IS TABLE OF ad_check_file_temp.check_sum%TYPE;
1265: TYPE t_file_size IS TABLE OF ad_check_file_temp.file_size%TYPE;
1266: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
1267: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
1268: TYPE t_dest_file_id IS TABLE OF ad_check_file_temp.dest_file_id%TYPE;
1269: TYPE t_file_type_flag IS TABLE OF ad_check_file_temp.file_type_flag%TYPE;
1270: TYPE t_irep_gathered_flag IS TABLE OF ad_check_file_temp.manifest_vers_higher%TYPE;
1271: TYPE t_effective_date IS TABLE OF ad_check_file_temp.effective_date%TYPE;
1272:
1265: TYPE t_file_size IS TABLE OF ad_check_file_temp.file_size%TYPE;
1266: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
1267: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
1268: TYPE t_dest_file_id IS TABLE OF ad_check_file_temp.dest_file_id%TYPE;
1269: TYPE t_file_type_flag IS TABLE OF ad_check_file_temp.file_type_flag%TYPE;
1270: TYPE t_irep_gathered_flag IS TABLE OF ad_check_file_temp.manifest_vers_higher%TYPE;
1271: TYPE t_effective_date IS TABLE OF ad_check_file_temp.effective_date%TYPE;
1272:
1273: --
1266: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
1267: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
1268: TYPE t_dest_file_id IS TABLE OF ad_check_file_temp.dest_file_id%TYPE;
1269: TYPE t_file_type_flag IS TABLE OF ad_check_file_temp.file_type_flag%TYPE;
1270: TYPE t_irep_gathered_flag IS TABLE OF ad_check_file_temp.manifest_vers_higher%TYPE;
1271: TYPE t_effective_date IS TABLE OF ad_check_file_temp.effective_date%TYPE;
1272:
1273: --
1274: vers_id_list t_version_id;
1267: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
1268: TYPE t_dest_file_id IS TABLE OF ad_check_file_temp.dest_file_id%TYPE;
1269: TYPE t_file_type_flag IS TABLE OF ad_check_file_temp.file_type_flag%TYPE;
1270: TYPE t_irep_gathered_flag IS TABLE OF ad_check_file_temp.manifest_vers_higher%TYPE;
1271: TYPE t_effective_date IS TABLE OF ad_check_file_temp.effective_date%TYPE;
1272:
1273: --
1274: vers_id_list t_version_id;
1275: chk_sum_list t_check_sum;
1289: file_version_id,check_sum,file_size,
1290: file_id,check_file_id,dest_file_id,
1291: file_type_flag, manifest_vers_higher,
1292: effective_date
1293: from ad_check_file_temp;
1294: --
1295: --
1296: --
1297: --
1325: ad_file_util.error_buf := 'load_snapshot_file_info(cursor: '||
1326: 'select file_version_id,check_sum,file_size, '||
1327: 'file_id,check_file_id,dest_file_id, '||
1328: 'file_type_flag from '||
1329: 'ad_check_file_temp):(';
1330:
1331: begin
1332: OPEN c1;
1333: exception
1418: 't.file_size,t.check_sum, t.file_version_id,'||
1419: 'snp_id,''U'',sysdate,sysdate, 5,5,' ||
1420: 't.appl_top_id, t.inconsistent_flag, '||
1421: 't.dest_file_id, t.file_type_flag '||
1422: 'from ad_check_file_temp t where not exists '||
1423: '(select ''already present'' '||
1424: 'from ad_snapshot_files sf2 '||
1425: 'where sf2.snapshot_id = snp_id '||
1426: 'and sf2.file_id = t.file_id '||
1441: t.file_version_id,snp_id,'U',sysdate,sysdate,
1442: 5,5, t.appl_top_id, t.inconsistent_flag,
1443: t.dest_file_id, t.file_type_flag,
1444: t.manifest_vers_higher, t.effective_date
1445: from ad_check_file_temp t
1446: where not exists
1447: (select /*+ INDEX(SF2 AD_SNAPSHOT_FILES_U2) */ 'already present'
1448: from ad_snapshot_files sf2
1449: where sf2.snapshot_id = snp_id
1471: -- load_preseeded_bugfixes
1472: --
1473: -- Purpose
1474: -- Gets the bug_id from AD_BUGS for the bugnumbers in
1475: -- in ad_check_file_temp table .
1476: -- Creates new rows in the AD_BUGS for the new bugnumbers
1477: -- and gets the bug_id for those bugnumbers and stores them
1478: -- ad_check_file_temp table .
1479: --
1474: -- Gets the bug_id from AD_BUGS for the bugnumbers in
1475: -- in ad_check_file_temp table .
1476: -- Creates new rows in the AD_BUGS for the new bugnumbers
1477: -- and gets the bug_id for those bugnumbers and stores them
1478: -- ad_check_file_temp table .
1479: --
1480: -- Inserts those BUG_IDs into AD_SNAPSHOT_BUGFIXES
1481: --
1482: --
1499: 't.junk = NULL '||
1500: 'WHERE NVL(t.active_flag,''N'') = ''Y''):(';
1501: begin
1502: -- Bug 5579901- stangutu - 9 Oct, 2006
1503: UPDATE ad_check_file_temp t
1504: SET t.file_id = (SELECT /*+ INDEX(B AD_BUGS_U2) */ b.bug_id
1505: FROM ad_bugs b
1506: WHERE b.bug_number = t.filename
1507: AND b.aru_release_name = t.subdir
1540: 't.manifest_vers_2 baseline_name, '||
1541: 't.manifest_vers_higher, generic_patch, '||
1542: 't.app_short_name trackable_entity_abbr, ' ||
1543: 't.language language '||
1544: 'FROM ad_check_file_temp t '||
1545: 'WHERE t.file_id is null '||
1546: 'AND NVL(t.active_flag,''N'') = ''Y'') temp):(';
1547: begin
1548: INSERT INTO ad_bugs
1569: t.manifest_vers_higher generic_patch,
1570: t.app_short_name trackable_entity_abbr,
1571: -- bug 5615204 diverma Tuesday, August 07, 2007
1572: t.language language
1573: FROM ad_check_file_temp t
1574: WHERE t.file_id is null
1575: AND NVL(t.active_flag,'N') = 'Y') temp;
1576: exception
1577: when others then
1582: -- add bug_id for new entries
1583: --
1584: -- Bug 5758908 - stangutu - 14 June, 2007
1585: ad_file_util.error_buf := 'load_preseeded_bugfixes('||
1586: 'UPDATE ad_check_file_temp t '||
1587: 'SET t.file_id = (SELECT b.bug_id '||
1588: 'FROM ad_bugs b WHERE b.bug_number = t.filename '||
1589: 'AND b.aru_release_name = t.subdir, '||
1590: 'AND b.trackable_entity_abbr = t.app_short_name' ||
1593: 't.junk = NULL '||
1594: 'WHERE NVL(t.active_flag,''N'') = ''Y''):(';
1595: begin
1596: -- Bug 5579901- stangutu - 9 Oct, 2006
1597: UPDATE ad_check_file_temp t
1598: SET t.file_id = (SELECT /*+ INDEX(B AD_BUGS_U2) */ b.bug_id
1599: FROM ad_bugs b
1600: WHERE b.bug_number = t.filename
1601: AND b.aru_release_name = t.subdir
1626: 'creation_date,last_update_date, '||
1627: 'last_updated_by,created_by) '||
1628: 'SELECT ad_snapshot_bugfixes_s.nextval,'||
1629: 'file_version_id_2, file_id,''EXPLICIT'',''Y'','||
1630: 'sysdate, sysdate,5,5 FROM ad_check_file_temp t '||
1631: 'where not exists (select ''already present'' '||
1632: 'from ad_snapshot_bugfixes b '||
1633: 'where b.BUGFIX_ID=t.file_id and '||
1634: 'b.SNAPSHOT_ID=t.file_version_id_2):(';
1642: SELECT ad_snapshot_bugfixes_s.nextval,file_version_id_2,
1643: file_id,'EXPLICIT','Y',sysdate,
1644: sysdate,5,5
1645: FROM
1646: ad_check_file_temp t
1647: where not exists
1648: (select /*+ INDEX(B AD_SNAPSHOT_BUGFIXES_U2) */ 'already present'
1649: from ad_snapshot_bugfixes b
1650: where b.BUGFIX_ID=t.file_id and
2501: execute immediate 'truncate table '|| p_un_fnd ||'.ad_patch_hist_temp';
2502: --
2503: --
2504: if (p_iteration = 1) then
2505: execute immediate 'truncate table '|| p_un_fnd ||'.ad_check_file_temp';
2506: --
2507: --
2508: insert into ad_check_file_temp
2509: (TRANSLATION_LEVEL,APP_SHORT_NAME,
2504: if (p_iteration = 1) then
2505: execute immediate 'truncate table '|| p_un_fnd ||'.ad_check_file_temp';
2506: --
2507: --
2508: insert into ad_check_file_temp
2509: (TRANSLATION_LEVEL,APP_SHORT_NAME,
2510: SUBDIR,FILENAME, MANIFEST_VERS,
2511: EFFECTIVE_DATE)
2512: select
2565: ad_snapshot_files
2566: where
2567: file_id >= p_min_file_id and
2568: file_id < p_max_file_id and
2569: snapshot_id in (select TRANSLATION_LEVEL from ad_check_file_temp)
2570: group by
2571: file_id
2572: having
2573: count(distinct nvl(file_version_id,0))=1
2606: file_id from ad_snapshot_files
2607: where
2608: file_id >= p_min_file_id and
2609: file_id < p_max_file_id and
2610: snapshot_id in (select TRANSLATION_LEVEL from ad_check_file_temp)
2611: group by
2612: file_id
2613: having
2614: count(distinct nvl(file_version_id,0)) >1
2612: file_id
2613: having
2614: count(distinct nvl(file_version_id,0)) >1
2615: ) and
2616: snapshot_id in (select TRANSLATION_LEVEL from ad_check_file_temp);
2617: --
2618: --
2619: commit;
2620: --
2688: --
2689: --
2690: if (p_iteration = 1) then
2691: FND_STATS.Gather_Table_Stats(p_un_fnd, 'AD_SNAPSHOT_FILES_TEMP');
2692: FND_STATS.Gather_Table_Stats(p_un_fnd, 'AD_CHECK_FILE_TEMP');
2693: FND_STATS.Gather_Table_Stats(p_un_fnd, 'AD_PATCH_HIST_TEMP');
2694: end if;
2695: --
2696: --
2724: v_global_snapshot_id number;
2725: begin
2726: --
2727: --
2728: execute immediate 'truncate table '||p_un_fnd||'.ad_check_file_temp';
2729: --
2730: --
2731: if (p_iteration = 1) then
2732: --
2786: having
2787: count(distinct decode(success_flag, 'Y', 2, 1)) = 1);
2788: --
2789: --
2790: insert into ad_check_file_temp (
2791: file_version_id,
2792: app_short_name , active_flag,
2793: check_file_id,subdir,filename,
2794: manifest_vers,translation_level,effective_date)
2846: PARTITION BY file_version_id order by
2847: check_file_id
2848: ) rnk
2849: from
2850: ad_check_file_temp
2851: )
2852: ) where rnk=1 ' using v_global_snapshot_id;
2853: --
2854: --
2856: --
2857: --
2858: if (p_iteration = 1) then
2859: FND_STATS.Gather_Table_Stats(p_un_fnd, 'AD_SNAPSHOT_FILES_TEMP');
2860: FND_STATS.Gather_Table_Stats(p_un_fnd, 'AD_CHECK_FILE_TEMP');
2861: FND_STATS.Gather_Table_Stats(p_un_fnd, 'AD_PATCH_HIST_TEMP');
2862: end if;
2863: --
2864: --
2869: -- Procedure
2870: -- load_prepmode_checkfile_info
2871: --
2872: -- Purpose
2873: -- Imports file information from ad_check_file_temp to
2874: -- ad_prepmode_check_files, when applying a patch is "prepare" mode.
2875: --
2876: -- Arguments
2877: -- none
2885: --
2886:
2887: update ad_prepmode_check_files cf
2888: set version = (select t.manifest_vers
2889: from ad_check_file_temp t
2890: where t.app_short_name = cf.app_short_name
2891: and t.subdir = cf.subdir
2892: and t.filename = cf.filename
2893: and nvl(t.distinguisher, '~') = cf.distinguisher)
2892: and t.filename = cf.filename
2893: and nvl(t.distinguisher, '~') = cf.distinguisher)
2894: where (app_short_name, subdir, filename, distinguisher) in
2895: (select app_short_name, subdir, filename, nvl(distinguisher, '~')
2896: from ad_check_file_temp
2897: where manifest_vers is not null);
2898:
2899: --
2900: -- insert rows for new files
2906: )
2907: select distinct app_short_name, subdir, filename, nvl(distinguisher, '~'),
2908:
2909: manifest_vers
2910: from ad_check_file_temp t
2911: where t.manifest_vers is not null
2912: and not exists (
2913: select null
2914: from ad_prepmode_check_files cf2
2942: -- load_snpst_file_server_info
2943: --
2944: -- Purpose
2945: -- updates the values of the server flags of the table ad_snapshot_files
2946: -- as obtained from the temporary table ad_check_file_temp
2947: --
2948: -- Arguments
2949: -- snp_id - snapshot_id for which the rows are to be updated
2950: --
2950: --
2951: procedure load_snpst_file_server_info
2952: (snp_id number)
2953: is
2954: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
2955: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
2956: TYPE t_admin_server_flag IS TABLE OF ad_check_file_temp.server_type_admin_flag%TYPE;
2957: TYPE t_forms_server_flag IS TABLE OF ad_check_file_temp.server_type_forms_flag%TYPE;
2958: TYPE t_node_server_flag IS TABLE OF ad_check_file_temp.server_type_node_flag%TYPE;
2951: procedure load_snpst_file_server_info
2952: (snp_id number)
2953: is
2954: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
2955: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
2956: TYPE t_admin_server_flag IS TABLE OF ad_check_file_temp.server_type_admin_flag%TYPE;
2957: TYPE t_forms_server_flag IS TABLE OF ad_check_file_temp.server_type_forms_flag%TYPE;
2958: TYPE t_node_server_flag IS TABLE OF ad_check_file_temp.server_type_node_flag%TYPE;
2959: TYPE t_web_server_flag IS TABLE OF ad_check_file_temp.server_type_web_flag%TYPE;
2952: (snp_id number)
2953: is
2954: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
2955: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
2956: TYPE t_admin_server_flag IS TABLE OF ad_check_file_temp.server_type_admin_flag%TYPE;
2957: TYPE t_forms_server_flag IS TABLE OF ad_check_file_temp.server_type_forms_flag%TYPE;
2958: TYPE t_node_server_flag IS TABLE OF ad_check_file_temp.server_type_node_flag%TYPE;
2959: TYPE t_web_server_flag IS TABLE OF ad_check_file_temp.server_type_web_flag%TYPE;
2960: --
2953: is
2954: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
2955: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
2956: TYPE t_admin_server_flag IS TABLE OF ad_check_file_temp.server_type_admin_flag%TYPE;
2957: TYPE t_forms_server_flag IS TABLE OF ad_check_file_temp.server_type_forms_flag%TYPE;
2958: TYPE t_node_server_flag IS TABLE OF ad_check_file_temp.server_type_node_flag%TYPE;
2959: TYPE t_web_server_flag IS TABLE OF ad_check_file_temp.server_type_web_flag%TYPE;
2960: --
2961: fl_id_list t_file_id;
2954: TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
2955: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
2956: TYPE t_admin_server_flag IS TABLE OF ad_check_file_temp.server_type_admin_flag%TYPE;
2957: TYPE t_forms_server_flag IS TABLE OF ad_check_file_temp.server_type_forms_flag%TYPE;
2958: TYPE t_node_server_flag IS TABLE OF ad_check_file_temp.server_type_node_flag%TYPE;
2959: TYPE t_web_server_flag IS TABLE OF ad_check_file_temp.server_type_web_flag%TYPE;
2960: --
2961: fl_id_list t_file_id;
2962: con_file_id_list t_containing_file_id;
2955: TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
2956: TYPE t_admin_server_flag IS TABLE OF ad_check_file_temp.server_type_admin_flag%TYPE;
2957: TYPE t_forms_server_flag IS TABLE OF ad_check_file_temp.server_type_forms_flag%TYPE;
2958: TYPE t_node_server_flag IS TABLE OF ad_check_file_temp.server_type_node_flag%TYPE;
2959: TYPE t_web_server_flag IS TABLE OF ad_check_file_temp.server_type_web_flag%TYPE;
2960: --
2961: fl_id_list t_file_id;
2962: con_file_id_list t_containing_file_id;
2963: admin_server_flag_list t_admin_server_flag;
2973: server_type_admin_flag,
2974: server_type_forms_flag,
2975: server_type_node_flag,
2976: server_type_web_flag
2977: FROM ad_check_file_temp;
2978: --
2979: --
2980: --
2981: --
2988: ad_file_util.error_buf := 'load_snpst_file_server_info(cursor: '||
2989: 'select file_id,check_file_id,server_type_admin_flag, '||
2990: 'server_type_forms_flag, server_type_node_flag, '||
2991: 'server_type_web_flag from '||
2992: 'ad_check_file_temp):(';
2993: --
2994: begin
2995: OPEN c1;
2996: exception