[Home] [Help]
PACKAGE BODY: APPS.AD_POST_PATCH
Source
1 package body ad_post_patch as
2 /* $Header: adpostpb.pls 120.2 2005/10/17 05:45:59 rahkumar noship $ */
3
4
5 -- This procedure populates the table ad_processed_files_temp with
6 -- the list of files in the current_view for which the irep_gathered_flag
7 -- is set to 'N' and their extension matches with the list of extensions
8 -- provided to this procedure.
9
10 procedure get_patched_files
11 (p_appltop_id number,
12 p_file_extension_list varchar2 default NULL,
13 p_applsys_user_name varchar2)
14 is
15 v_snapshot_id number;
16 v_sql_stmt varchar2(4000);
17 begin
18
19
20 -- Truncate table applsys.ad_processed_files_temp.
21 v_sql_stmt := 'truncate table ' || p_applsys_user_name || '.ad_processed_files_temp';
22 execute immediate v_sql_stmt;
23
24 -- Get the snapshot_id for use later.
25
26 select snapshot_id
27 into v_snapshot_id
28 from ad_snapshots
29 where appl_top_id = p_appltop_id
30 and snapshot_name = 'CURRENT_VIEW'
31 and snapshot_type = 'C';
32
33 -- dbms_output.put_line('->SNAPSHOT_ID : [' || v_snapshot_id || ']');
34
35 -- Insert into ad_processed_files_temp all the files in ad_snapshot_files
36 -- whose extension matches the given list of extension, whose irep_gathered_flag
37 -- is set to 'N' and thet belong to the current view snapshot for this appltop.
38
39 v_sql_stmt := 'insert into ad_processed_files_temp' ||
40 '(' ||
41 'product_short_name,' ||
42 'subdir,' ||
43 'file_base,' ||
44 'file_extension,' ||
45 'file_id,' ||
46 'file_version_id,' ||
47 'version,' ||
48 'date_applied,' ||
49 'adpatch_flag' ||
50 ')' ||
51 ' select f.app_short_name,' ||
52 ' f.subdir,' ||
53 ' substr(f.filename,0,instr(f.filename,''.'')-1),' ||
54 ' substr(f.filename,instr(f.filename,''.'')+1),' ||
55 ' sf.file_id, ' ||
56 ' sf.file_version_id, ' ||
57 ' adfv.version, ' ||
58 ' sf.last_patched_date, ' ||
59 ' ''Y'' ' ||
60 ' from ad_files f, ' ||
61 ' ad_file_versions adfv, ' ||
62 ' ad_snapshot_files sf ' ||
63 ' where f.file_id = adfv.file_id ' ||
64 ' and sf.file_id = f.file_id ' ||
65 ' and sf.file_version_id = adfv.file_version_id ' ||
66 ' and substr(f.filename,instr(f.filename,''.'')+1) in ' || p_file_extension_list ||
67 ' and sf.snapshot_id = ' || v_snapshot_id ||
68 ' and sf.irep_gathered_flag = ''N''';
69
70 execute immediate v_sql_stmt;
71
72 -- Commit.
73
74 v_sql_stmt := 'commit';
75 execute immediate v_sql_stmt;
76
77 exception
78 when others
79 then
80 raise;
81 end get_patched_files;
82
83
84
85 -- This procedure populates the table ad_processed_files_temp with
86 -- the list of files in the current_view having extension as 'ildt'
87 -- and patched since the given date.
88
89 procedure get_files
90 (p_appltop_id number,
91 p_start_date varchar2)
92 is
93 v_snapshot_id number;
94 v_sql_stmt varchar2(4000);
95 begin
96
97
98 -- Truncate table applsys.ad_processed_files_temp.
99 --v_sql_stmt := 'truncate table ' || p_applsys_user_name || '.ad_processed_files_temp';
100 --execute immediate v_sql_stmt;
101
102 -- Get the snapshot_id for use later.
103
104 select snapshot_id
105 into v_snapshot_id
106 from ad_snapshots
107 where appl_top_id = p_appltop_id
108 and snapshot_name = 'CURRENT_VIEW'
109 and snapshot_type = 'C';
110
111 -- dbms_output.put_line('->SNAPSHOT_ID : [' || v_snapshot_id || ']');
112
113 -- Insert into ad_processed_files_temp all the files in ad_snapshot_files
114 -- whose extension matches the given list of extension, whose irep_gathered_flag
115 -- is set to 'N' and thet belong to the current view snapshot for this appltop.
116
117 v_sql_stmt := 'MERGE INTO ad_processed_files_temp apft' ||
118 ' USING (' ||
119 ' select f.app_short_name,' ||
120 ' f.subdir,' ||
121 ' substr(f.filename,0,instr(f.filename,''.'')-1) file_base,' ||
122 ' substr(f.filename,instr(f.filename,''.'')+1) file_extension,' ||
123 ' sf.file_id, ' ||
124 ' sf.file_version_id, ' ||
125 ' adfv.version, ' ||
126 ' sf.last_patched_date ' ||
127 ' from ad_files f, ' ||
128 ' ad_file_versions adfv, ' ||
129 ' ad_snapshot_files sf ' ||
130 ' where f.file_id = adfv.file_id ' ||
131 ' and sf.file_id = f.file_id ' ||
132 ' and sf.file_version_id = adfv.file_version_id ' ||
133 ' and substr(f.filename,instr(f.filename,''.'')+1) = ''ildt''' ||
134 ' and sf.snapshot_id = ' || v_snapshot_id ||
135 ' and sf.last_patched_date >= ''' || to_date(p_start_date,'DD-MM-YYYY') || '''' ||
136 ' ) S' ||
137 ' ON ( apft.product_short_name=S.app_short_name and' ||
138 ' apft.subdir=S.subdir and' ||
139 ' apft.file_base=S.file_base and' ||
140 ' apft.file_extension=S.file_extension )' ||
141 ' WHEN MATCHED THEN UPDATE SET ' ||
142 ' apft.file_version_id=S.file_version_id,' ||
143 ' apft.version=S.version,' ||
144 ' apft.date_applied=S.last_patched_date,' ||
145 ' apft.adpatch_flag=''Y''' ||
146 ' WHEN NOT MATCHED THEN INSERT ' ||
147 ' (product_short_name,subdir,file_base,' ||
148 ' file_extension,file_id,file_version_id,' ||
149 ' version,date_applied,adpatch_flag)' ||
150 ' VALUES ' ||
151 ' (S.app_short_name ,S.subdir ,S.file_base,' ||
152 ' S.file_extension, S.file_id, S.file_version_id,' ||
153 ' S.version, S.last_patched_date,''Y'')';
154
155 execute immediate v_sql_stmt;
156
157 -- Commit.
158
159 v_sql_stmt := 'commit';
160 execute immediate v_sql_stmt;
161
162 exception
163 when no_data_found then
164 return;
165 when others
166 then
167 raise;
168 end get_files;
169
170
171 -- Populates the table ad_processed_files_temp with the details
172 -- of all ildt files in the current view of the given appltop.
173
174 procedure get_all_files
175 (p_appltop_id number)
176 is
177 v_snapshot_id number;
178 v_sql_stmt varchar2(4000);
179 begin
180
181 -- Truncate table applsys.ad_processed_files_temp.
182 --v_sql_stmt := 'truncate table ' || p_applsys_user_name || '.ad_processed_files_temp';
183 --execute immediate v_sql_stmt;
184
185 select snapshot_id
186 into v_snapshot_id
187 from ad_snapshots
188 where appl_top_id = p_appltop_id
189 and snapshot_name = 'CURRENT_VIEW'
190 and snapshot_type = 'C';
191
192 -- dbms_output.put_line('->SNAPSHOT_ID : [' || v_snapshot_id || ']');
193
194 -- Insert into ad_processed_files_temp all the files in ad_snapshot_files
195 -- whose last_patched_date is between the start_date and end_date passed
196 -- and the extension of the file matches the list of extensions passed.
197 -- Do NOT consider the irep_gathered_flag here.
198
199 v_sql_stmt := 'MERGE INTO ad_processed_files_temp apft' ||
200 ' USING (' ||
201 ' select f.app_short_name,' ||
202 ' f.subdir,' ||
203 ' substr(f.filename,0,instr(f.filename,''.'')-1) file_base,' ||
204 ' substr(f.filename,instr(f.filename,''.'')+1) file_extension,' ||
205 ' sf.file_id, ' ||
206 ' sf.file_version_id, ' ||
207 ' adfv.version, ' ||
208 ' sf.last_patched_date ' ||
209 ' from ad_files f, ' ||
210 ' ad_file_versions adfv, ' ||
211 ' ad_snapshot_files sf ' ||
212 ' where f.file_id = adfv.file_id ' ||
213 ' and sf.file_id = f.file_id ' ||
214 ' and sf.file_version_id = adfv.file_version_id ' ||
215 ' and substr(f.filename,instr(f.filename,''.'')+1) = ''ildt''' ||
216 ' and sf.snapshot_id = ' || v_snapshot_id ||
217 ' ) S' ||
218 ' ON ( apft.product_short_name=S.app_short_name and' ||
219 ' apft.subdir=S.subdir and' ||
220 ' apft.file_base=S.file_base and' ||
221 ' apft.file_extension=S.file_extension )' ||
222 ' WHEN MATCHED THEN UPDATE SET ' ||
223 ' apft.file_version_id=S.file_version_id,' ||
224 ' apft.version=S.version,' ||
225 ' apft.date_applied=S.last_patched_date,' ||
226 ' apft.adpatch_flag=''Y''' ||
227 ' WHEN NOT MATCHED THEN INSERT ' ||
228 ' (product_short_name,subdir,file_base,' ||
229 ' file_extension,file_id,file_version_id,' ||
230 ' version,date_applied,adpatch_flag)' ||
231 ' VALUES ' ||
232 ' (S.app_short_name ,S.subdir ,S.file_base,' ||
233 ' S.file_extension, S.file_id, S.file_version_id,' ||
234 ' S.version, S.last_patched_date,''Y'')';
235
236 execute immediate v_sql_stmt;
237
238 -- Commit.
239
240 v_sql_stmt := 'commit';
241 execute immediate v_sql_stmt;
242
243
244 exception
245 when no_data_found then
246 return;
247 when others
248 then
249 raise;
250 end get_all_files;
251
252
253 -- Gets the list of files in the current view who have been patched between the two
254 -- dates (given in DD-MM-YYYY format) and whose extension matches the list of
255 -- extensions passed.
256
257 procedure get_all_files
258 (p_appltop_id number,
259 p_start_date varchar2,
260 p_end_date varchar2,
261 p_file_extension_list varchar2 default NULL)
262
263 is
264 v_snapshot_id number;
265 v_sql_stmt varchar2(4000);
266 begin
267
268 -- Truncate table applsys.ad_processed_files_temp.
269 -- v_sql_stmt := 'truncate table ' || p_applsys_user_name || '.ad_processed_files_temp';
270 -- execute immediate v_sql_stmt;
271
272 select snapshot_id
273 into v_snapshot_id
274 from ad_snapshots
275 where appl_top_id = p_appltop_id
276 and snapshot_name = 'CURRENT_VIEW'
277 and snapshot_type = 'C';
278
279 -- dbms_output.put_line('->SNAPSHOT_ID : [' || v_snapshot_id || ']');
280
281 -- Insert into ad_processed_files_temp all the files in ad_snapshot_files
282 -- whose last_patched_date is between the start_date and end_date passed
283 -- and the extension of the file matches the list of extensions passed.
284 -- Do NOT consider the irep_gathered_flag here.
285
286 v_sql_stmt := 'MERGE INTO ad_processed_files_temp apft' ||
287 ' USING (' ||
288 ' select f.app_short_name,' ||
289 ' f.subdir,' ||
290 ' substr(f.filename,0,instr(f.filename,''.'')-1) file_base,' ||
291 ' substr(f.filename,instr(f.filename,''.'')+1) file_extension,' ||
292 ' sf.file_id, ' ||
293 ' sf.file_version_id, ' ||
294 ' adfv.version, ' ||
295 ' sf.last_patched_date ' ||
296 ' from ad_files f, ' ||
297 ' ad_file_versions adfv, ' ||
298 ' ad_snapshot_files sf ' ||
299 ' where f.file_id = adfv.file_id ' ||
300 ' and sf.file_id = f.file_id ' ||
301 ' and sf.file_version_id = adfv.file_version_id ' ||
302 ' and substr(f.filename,instr(f.filename,''.'')+1) in '||
303 p_file_extension_list||
304 ' and sf.snapshot_id = ' || v_snapshot_id ||
305 ' and sf.last_patched_date >= ''' || to_date(p_start_date,'DD-MM-YYYY') ||'''' ||
306 ' ) S' ||
310 ' apft.file_extension=S.file_extension )' ||
307 ' ON ( apft.product_short_name=S.app_short_name and' ||
308 ' apft.subdir=S.subdir and' ||
309 ' apft.file_base=S.file_base and' ||
311 ' WHEN MATCHED THEN UPDATE SET ' ||
312 ' apft.file_version_id=S.file_version_id,' ||
313 ' apft.version=S.version,' ||
314 ' apft.date_applied=S.last_patched_date,' ||
315 ' apft.adpatch_flag=''Y''' ||
316 ' WHEN NOT MATCHED THEN INSERT ' ||
317 ' (product_short_name,subdir,file_base,' ||
318 ' file_extension,file_id,file_version_id,' ||
319 ' version,date_applied,adpatch_flag)' ||
320 ' VALUES ' ||
321 ' (S.app_short_name ,S.subdir ,S.file_base,' ||
322 ' S.file_extension, S.file_id, S.file_version_id,' ||
323 ' S.version, S.last_patched_date,''Y'')';
324
325 execute immediate v_sql_stmt;
326
327 -- Commit.
328
329 v_sql_stmt := 'commit';
330 execute immediate v_sql_stmt;
331
332
333 exception
334 when no_data_found then
335 return;
336 when others
337 then
338 raise;
339 end get_all_files;
340
341
342 -- This procedure sets the ad_snapshot_files.irep_gathered_flag to 'Y'
343 -- for all files present in ad_processed_files_temp (its assumed that
344 -- whoever calls this function would have made sure the files have
345 -- really been processed and are ready to be marked as processed).
346 -- The flag will remain at 'Y' until the next time when the file gets
347 -- patched and the flag gets reset to 'N" meaning "I have been patched
348 -- and I need to be processed again".
349
350 procedure set_gathered_flag
351 (p_appltop_id number)
352 is
353 v_snapshot_id number;
354 v_sql_stmt varchar2(4000);
355 begin
356
357 -- Dont truncate the table ad_processed_files_temp!!!
358
359 select snapshot_id
360 into v_snapshot_id
364 and snapshot_type = 'C';
361 from ad_snapshots
362 where appl_top_id = p_appltop_id
363 and snapshot_name = 'CURRENT_VIEW'
365
366 -- dbms_output.put_line('->SNAPSHOT_ID : [' || v_snapshot_id || ']');
367
368 -- Taking the file_id and the file_version_id from ad_processed_files_temp,
369 -- update the row in ad_snapshot_files to make its column irep_gathered_flag
370 -- set to 'Y'. This marks the file saying "We have processed this version of
371 -- this file.". The next time this file is patched, this flag will get reset
372 -- to 'N' and will be again ready for processing and the cycle continues...
373
374
375 v_sql_stmt := 'update ad_snapshot_files sf' ||
376 ' set sf.irep_gathered_flag=''Y''' ||
377 ' where sf.snapshot_id = ' || v_snapshot_id ||
378 ' and sf.irep_gathered_flag = ''N''' ||
379 ' and sf.file_id in ' ||
380 ' (select t.file_id from ad_processed_files_temp t ' ||
381 ' where sf.file_version_id = t.file_version_id ' ||
382 ' and sf.file_id = t.file_id ' ||
383 ' and sf.last_patched_date = t.date_applied ' ||
384 ' and t.file_extension = ''ildt''' || ') ';
385
386 --DBMS_OUTPUT.PUT_LINE('->[' || v_sql_stmt || ']');
387
388 execute immediate v_sql_stmt;
389
390 -- Commit.
391
392 v_sql_stmt := 'commit';
393 execute immediate v_sql_stmt;
394
395
396
397 exception
398 when others
399 then
400 raise;
401 end set_gathered_flag;
402
403
404
405
406 --
407 --
408 --
409 end ad_post_patch;