[Home] [Help]
PACKAGE BODY: APPS.AD_FILE_UTIL
Source
1 package body ad_file_util as
2 /* $Header: adfilutb.pls 120.17.12020000.6 2013/06/10 07:37:57 mkumandu ship $ */
3
4 procedure lock_infrastructure is
5 l_lockhandle varchar2(128);
6 l_status number := 100;
7 l_exit_loop boolean := FALSE;
8 begin
9 ad_file_util.error_buf := 'lock_infrastructure()';
10
11 dbms_lock.allocate_unique('ORA_APPS_AD_CHKFILTMP', l_lockhandle);
12
13 l_exit_loop := FALSE;
14
15 loop
16 exit when l_exit_loop;
17
18 l_status := dbms_lock.request(l_lockhandle);
19
20 if l_status in (0, 4) then
21 -- 0 => success
22 -- 4 => already held, deem as success.
23
24 l_exit_loop := TRUE;
25
26 elsif l_status <> 1 then
27 -- 1 => Timeout, in which case we want to keep trying (ie. stay in the
28 -- loop). Any value other than 1 is a fatal error.
29
30 raise_application_error(-20000,
31 'Fatal error in lock_infrastructure() - '||
32 to_char(l_status));
33 end if;
34
35 end loop;
36
37 end lock_infrastructure;
38
39 procedure unlock_infrastructure is
40 l_lockhandle varchar2(128);
41 l_status number := 100;
42 begin
43 ad_file_util.error_buf := 'unlock_infrastructure()';
44
45 dbms_lock.allocate_unique('ORA_APPS_AD_CHKFILTMP', l_lockhandle);
46
47 l_status := dbms_lock.release(l_lockhandle);
48
49 if l_status not in (0, 4) then
50 -- 0 => success. 4 => never held, so deem as success. Any other value is
51 -- an error.
52
53 raise_application_error(-20000,
54 'Fatal error in unlock_infrastructure() - '||
55 to_char(l_status));
56 end if;
57
58 end unlock_infrastructure;
59
60 --
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 --
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
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
84 -- d) Call the relevant packaged-procedure that reads the temp table and
85 -- loads whatever is necessary.
86 -- e) Commit.
87 --
88 -- Then repeat steps (a) thru (e) for other rows. When all batches have
89 -- finished processing, then unlock_infrastructure() should be called to
90 -- release the User Lock at the very end.
91 --
92 -- Arguments
93 -- none
94 --
95 procedure lock_and_empty_temp_table
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
105 ad_file_util.error_buf := 'lock_and_empty_temp_table('||
106 ad_file_util.error_buf||
107 ')';
108
109 raise;
110 end lock_and_empty_temp_table;
111
112 --
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'.
122 --
123 -- To handle batch sizes:
124 --
125 -- 1) - fill up whole table with null active_flag
126 -- - In a loop:
127 -- - update a batch to have active_flag='Y'
128 -- - process the batch
129 -- - delete the batch
130 -- - using 'where rownum < batch+1' is handy here
131 --
132 -- 2) perform (truncate, load, process) cycles in an outer loop where
133 -- only <batch size> rows are loaded and processed at a time.
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
140 --
141 procedure load_file_info
142 is
143 begin
144 --
145 -- process ad_files
146 --
147
148 --
149 -- get file_id from ad_files
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
168 and f.subdir = t.subdir
169 and f.filename = t.filename),
170 t.junk = null
171 where nvl(t.active_flag,'N') = 'Y';
172
173 exception
174 when others then
175 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
176 raise;
177 end;
178 --
179 -- add new entries in ad_files
180 --
181 ad_file_util.error_buf := 'load_file_info('||
182 'insert into ad_files '||
183 '(file_id, app_short_name, subdir, filename, '||
184 'creation_date, created_by, last_update_date, '||
185 'last_updated_by) select ad_files_s.nextval, '||
186 'temp.asn, temp.dir, temp.fname, temp.edate, 5, '||
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
196 (file_id, app_short_name, subdir, filename,
197 creation_date, created_by, last_update_date, last_updated_by)
198 select ad_files_s.nextval,
199 temp.asn, temp.dir, temp.fname,
200 temp.edate, 5, temp.edate, 5
201 from
202 (select distinct
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
212 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
213 raise;
214 end;
215
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
234 and f.subdir = t.subdir
235 and f.filename = t.filename)
236 where t.file_id is null
237 and nvl(t.active_flag,'N') = 'Y';
238 exception
239 when others then
240 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
241 raise;
242 end;
243
244 --
245 -- rkagrawa: Fixed bug3354978
246 -- Process the dest_file_id entries in a separate chunk, on lines similar
247 -- to file_id entries (i.e., first update, then insert and finally update)
248 --
249
250 --
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 '||
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
268 and f.subdir = t.dest_subdir
269 and f.filename = t.dest_filename)
270 where nvl(t.active_flag,'N') = 'Y';
271 exception
272 when others then
273 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
274 raise;
275 end;
276
277 --
278 -- add new entries in ad_files
279 --
280
281 ad_file_util.error_buf := 'load_file_info('||
282 'insert into ad_files '||
283 '(file_id, app_short_name, subdir, filename, '||
284 'creation_date, created_by, last_update_date, '||
285 'last_updated_by) select ad_files_s.nextval, '||
286 'temp.asn, temp.dir, temp.fname, temp.edate, 5, '||
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
296 insert into ad_files
297 (file_id, app_short_name, subdir, filename,
298 creation_date, created_by, last_update_date, last_updated_by)
299 select ad_files_s.nextval,
300 temp.asn, temp.dir, temp.fname,
301 temp.edate, 5, temp.edate, 5
302 from
303 (select distinct
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;
313 exception
314 when others then
315 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
316 raise;
317 end;
318
319 --
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) '||
329 'where t.dest_file_id is null '||
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
339 and f.subdir = t.dest_subdir
340 and f.filename = t.dest_filename)
341 where t.dest_file_id is null
342 and t.dest_filename is not null
343 and t.dest_filename <> 'none'
344 and nvl(t.active_flag,'N') = 'Y';
345 exception
346 when others then
347 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
348 raise;
349 end;
350
351 --
352 -- done processing ad_files
353 --
354
355 end load_file_info;
356
357 --
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 --
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
372 -- - In a loop:
373 -- - update a batch to have active_flag='Y'
374 -- - process the batch
375 -- - delete the batch
376 -- - using 'where rownum < batch+1' is handy here
377 --
378 -- 2) perform (truncate, load, process) cycles in an outer loop where
379 -- only <batch size> rows are loaded and processed at a time.
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
388 -- none
389 --
390 procedure load_file_version_info
391 is
392 begin
393 --
394 -- process ad_files
395 --
396 begin
397 ad_file_util.load_file_info;
398 exception
399 when others then
400 ad_file_util.error_buf := 'load_file_version_info('||
401 ad_file_util.error_buf||
402 ')';
403 raise;
404 end;
405 --
406 -- process ad_file_versons
407 --
408
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 '||
421 'where nvl(t.active_flag,''N'') = ''Y'' '||
418 'and fv.version = t.manifest_vers '||
419 'and fv.translation_level = '||
420 't.translation_level) '||
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
429 and fv.version = t.manifest_vers
430 and fv.translation_level = t.translation_level)
431 where nvl(t.active_flag,'N') = 'Y'
432 and lower(t.manifest_vers)<>'none';
433 exception
434 when others then
435 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
436 raise;
437 end;
438
439 --
440 -- add new entries to ad_file_versions
441 --
442 ad_file_util.error_buf := 'load_file_version_info('||
443 'insert into ad_file_versions '||
444 '(file_version_id, file_id, version, '||
445 'translation_level, '||
446 'version_segment1, version_segment2, '||
447 'version_segment3, version_segment4, '||
448 'version_segment5, version_segment6, '||
449 'version_segment7, version_segment8, '||
450 'version_segment9, version_segment10, '||
451 'creation_date, created_by, last_update_date, '||
452 'last_updated_by) select '||
453 'ad_file_versions_s.nextval, '||
454 'temp.f_id, temp.vers, temp.trans_level, '||
455 'temp.vs1, temp.vs2, temp.vs3, temp.vs4, '||
456 'temp.vs5, temp.vs6, temp.vs7, temp.vs8, '||
457 'temp.vs9, temp.vs10, temp.edate, 5, '||
458 'temp.edate, 5 from (select distinct '||
459 't.file_id f_id, t.manifest_vers vers, '||
460 't.translation_level trans_level,....);):(';
461
462 begin
463 insert into ad_file_versions
464 (file_version_id, file_id, version, translation_level,
465 version_segment1, version_segment2, version_segment3,
466 version_segment4, version_segment5, version_segment6,
467 version_segment7, version_segment8, version_segment9,
468 version_segment10,
469 creation_date, created_by, last_update_date, last_updated_by)
470 select ad_file_versions_s.nextval,
471 temp.f_id, temp.vers, temp.trans_level,
472 temp.vs1, temp.vs2, temp.vs3, temp.vs4, temp.vs5,
473 temp.vs6, temp.vs7, temp.vs8, temp.vs9, temp.vs10,
474 temp.edate, 5, temp.edate, 5
475 from
476 (select distinct
477 t.file_id f_id,
478 t.manifest_vers vers,
479 t.translation_level trans_level,
480 decode( instr(t.manifest_vers||'.','.',1,1), 0, 0,
481 to_number(substr(t.manifest_vers||'.',
482 1,
483 ( instr(t.manifest_vers||'.','.',1,1)-1)))) vs1,
484 decode( instr(t.manifest_vers||'.','.',1,2), 0, 0,
485 to_number(substr(t.manifest_vers||'.',
486 (instr(t.manifest_vers||'.','.',1,1)+1),
487 ( (instr(t.manifest_vers||'.','.',1,2))
488 - (instr(t.manifest_vers||'.','.',1,1)+1)) ))) vs2,
489 decode( instr(t.manifest_vers||'.','.',1,3), 0, 0,
490 to_number(substr(t.manifest_vers||'.',
491 (instr(t.manifest_vers||'.','.',1,2)+1),
492 ( (instr(t.manifest_vers||'.','.',1,3))
493 - (instr(t.manifest_vers||'.','.',1,2)+1)) ))) vs3,
494 decode( instr(t.manifest_vers||'.','.',1,4), 0, 0,
495 to_number(substr(t.manifest_vers||'.',
496 (instr(t.manifest_vers||'.','.',1,3)+1),
497 ( (instr(t.manifest_vers||'.','.',1,4))
498 - (instr(t.manifest_vers||'.','.',1,3)+1)) ))) vs4,
499 decode( instr(t.manifest_vers||'.','.',1,5), 0, 0,
500 to_number(substr(t.manifest_vers||'.',
501 (instr(t.manifest_vers||'.','.',1,4)+1),
502 ( (instr(t.manifest_vers||'.','.',1,5))
503 - (instr(t.manifest_vers||'.','.',1,4)+1)) ))) vs5,
504 decode( instr(t.manifest_vers||'.','.',1,6), 0, 0,
505 to_number(substr(t.manifest_vers||'.',
506 (instr(t.manifest_vers||'.','.',1,5)+1),
507 ( (instr(t.manifest_vers||'.','.',1,6))
508 - (instr(t.manifest_vers||'.','.',1,5)+1)) ))) vs6,
509 decode( instr(t.manifest_vers||'.','.',1,7), 0, 0,
510 to_number(substr(t.manifest_vers||'.',
511 (instr(t.manifest_vers||'.','.',1,6)+1),
512 ( (instr(t.manifest_vers||'.','.',1,7))
513 - (instr(t.manifest_vers||'.','.',1,6)+1)) ))) vs7,
514 decode( instr(t.manifest_vers||'.','.',1,8), 0, 0,
515 to_number(substr(t.manifest_vers||'.',
516 (instr(t.manifest_vers||'.','.',1,7)+1),
517 ( (instr(t.manifest_vers||'.','.',1,8))
518 - (instr(t.manifest_vers||'.','.',1,7)+1)) ))) vs8,
519 decode( instr(t.manifest_vers||'.','.',1,9), 0, 0,
520 to_number(substr(t.manifest_vers||'.',
521 (instr(t.manifest_vers||'.','.',1,8)+1),
522 ( (instr(t.manifest_vers||'.','.',1,9))
523 - (instr(t.manifest_vers||'.','.',1,8)+1)) ))) vs9,
524 decode( instr(t.manifest_vers||'.','.',1,10), 0, 0,
525 to_number(substr(t.manifest_vers||'.',
526 (instr(t.manifest_vers||'.','.',1,9)+1),
527 ( (instr(t.manifest_vers||'.','.',1,10))
531 where t.file_version_id is null
528 - (instr(t.manifest_vers||'.','.',1,9)+1)) ))) vs10,
529 t.effective_date edate
530 from ad_check_file_temp t
532 and lower(t.manifest_vers) <> 'none'
533 and nvl(t.active_flag,'N') = 'Y'
534 ) temp;
535 exception
536 when others then
537 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
538 raise;
539 end;
540
541
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 '||
551 'and fv.version = t.manifest_vers '||
552 'and fv.translation_level = t.translation_level)'||
553 'where t.file_version_id is null '||
554 'and nvl(t.active_flag,''N'') = ''Y'' '||
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
564 and fv.version = t.manifest_vers
565 and fv.translation_level = t.translation_level)
566 where t.file_version_id is null
567 and nvl(t.active_flag,'N') = 'Y'
568 and lower(t.manifest_vers)<>'none';
569 exception
570 when others then
571 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
572 raise;
573 end;
574
575 --
576 -- done processing ad_file_versons
577 --
578
579 end load_file_version_info;
580
581 --
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
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
599 -- - In a loop:
600 -- - update a batch to have active_flag='Y'
601 -- - process the batch
602 -- - delete the batch
603 -- - using 'where rownum < batch+1' is handy here
604 --
605 -- 2) perform (truncate, load, process) cycles in an outer loop where
606 -- only <batch size> rows are loaded and processed at a time.
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
615 -- (assuming they started out as null)
616 --
617 -- Arguments
618 -- none
619 --
620 procedure load_checkfile_info(p_ebr_flow boolean default false)
621 is
622 begin
623 --
624 -- process ad_files and ad_file_versions
625 --
626 ad_file_util.error_buf := 'load_checkfile_info(';
627 begin
628 ad_file_util.load_file_version_info;
629 exception
630 when others then
631 ad_file_util.error_buf := 'load_checkfile_info('||
632 ad_file_util.error_buf||sqlerrm||')';
633 raise;
634 end;
635 --
636 -- process ad_check_files
637 --
638
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 '||
648 'and nvl(cf.distinguisher,''*null*'') ='||
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
658 and nvl(cf.distinguisher,'*null*') = nvl(t.distinguisher,'*null*')),
659 t.manifest_vers_higher =
660 (select /*+ ORDERED INDEX(FV1 AD_FILE_VERSIONS_U1)
661 INDEX(CF AD_CHECK_FILES_U2) INDEX(FV2 AD_FILE_VERSIONS_U1)
662 USE_NL(FV1 CF FV2) */
663 decode(
664 sign(nvl(fv1.version_segment1,0) - nvl(fv2.version_segment1,0)),
665 -1, null, 1, 'Y', decode(
666 sign(nvl(fv1.version_segment2,0) - nvl(fv2.version_segment2,0)),
667 -1, null, 1, 'Y', decode(
668 sign(nvl(fv1.version_segment3,0) - nvl(fv2.version_segment3,0)),
669 -1, null, 1, 'Y', decode(
670 sign(nvl(fv1.version_segment4,0) - nvl(fv2.version_segment4,0)),
674 sign(nvl(fv1.version_segment6,0) - nvl(fv2.version_segment6,0)),
671 -1, null, 1, 'Y', decode(
672 sign(nvl(fv1.version_segment5,0) - nvl(fv2.version_segment5,0)),
673 -1, null, 1, 'Y', decode(
675 -1, null, 1, 'Y', decode(
676 sign(nvl(fv1.version_segment7,0) - nvl(fv2.version_segment7,0)),
677 -1, null, 1, 'Y', decode(
678 sign(nvl(fv1.version_segment8,0) - nvl(fv2.version_segment8,0)),
679 -1, null, 1, 'Y', decode(
680 sign(nvl(fv1.version_segment9,0) - nvl(fv2.version_segment9,0)),
681 -1, null, 1, 'Y', decode(
682 sign(nvl(fv1.version_segment10,0) - nvl(fv2.version_segment10,0)),
683 -1, null, 1, 'Y', decode(
684 sign(fv1.translation_level - fv2.translation_level),
685 -1, null, 1, 'Y', null)))))))))))
686 from ad_file_versions fv1, ad_check_files cf, ad_file_versions fv2
687 where t.file_version_id = fv1.file_version_id
688 and t.file_id = cf.file_id
689 and nvl(t.distinguisher,'*null*') = nvl(cf.distinguisher,'*null*')
690 and cf.file_version_id = fv2.file_version_id)
691 where nvl(t.active_flag,'N') = 'Y';
692 exception
693 when others then
694 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
695 raise;
696 end;
697 --
698 -- add new entries into ad_check_files
699 --
700 ad_file_util.error_buf := 'load_checkfile_info('||
701 'insert into ad_check_files '||
702 '(check_file_id, file_id, distinguisher, '||
703 'file_version_id, creation_date) '||
704 'select ad_check_files_s.nextval, '||
705 'temp.f_id, temp.dist, temp.fv_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
715 insert into ad_check_files
716 (check_file_id, file_id, distinguisher,
717 file_version_id, creation_date)
718 select ad_check_files_s.nextval,
719 temp.f_id, temp.dist, temp.fv_id, temp.edate
720 from
721 (select distinct
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
731 when others then
732 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
733 raise;
734 end;
735
736 if (p_ebr_flow = true)
737 then
738
739 -- Insert new records. Insert all the records with patch_run_id -1
740 -- Later while updating patch history update patch_run_id
741
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''))';
751
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');
761
762 exception
763 when others then
764 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
765 raise;
766 end;
767 end if;
768 --
769 --
770 -- delete from ad_check_files where versions lower than manifest
771 --
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
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
789 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
790 raise;
791 end;
792 --
793 -- insert into ad_check_files where versions lower than manifest
794 --
795 ad_file_util.error_buf := 'load_checkfile_info('||
796 ' insert into ad_check_files '||
797 '(check_file_id, file_id, distinguisher, '||
798 'file_version_id, creation_date) '||
799 'select temp.cf_id, '||
803 't.file_id f_id, '||
800 'temp.f_id, temp.dist, temp.fv_id, temp.edate '||
801 'from (select distinct '||
802 't.check_file_id cf_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
812 insert into ad_check_files
813 (check_file_id, file_id, distinguisher,
814 file_version_id, creation_date)
815 select temp.cf_id,
816 temp.f_id, temp.dist, temp.fv_id, temp.edate
817 from
818 (select distinct
819 t.check_file_id cf_id,
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
829 when others then
830 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
831 raise;
832 end;
833
834 if (p_ebr_flow = true)
835 then
836 -- Insert new records. Insert all the records with patch_run_id -1
837 -- Later while updating patch history update patch_run_id
838
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)';
848
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');
858
859 exception
860 when others then
861 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
862 raise;
863 end;
864 end if;
865 --
866 -- done processing ad_check_files
867 --
868
869 end load_checkfile_info;
870
871 --
872 -- Procedure
873 -- update_timestamp
874 --
875 -- Purpose
876 -- Inserts/updates a row in AD_TIMESTAMPS corresponding to the
877 -- specified row type and attribute.
878 --
879 -- Arguments
880 -- in_type The row type
881 -- in_attribute The row attribute
882 -- in_timestamp A timestamp. Defaults to sysdate.
883 --
884 -- Notes
885 -- This is essentially the same as ad_invoker.update_timestamp
886 -- Added it here to make it easier to call from APPS.
887 --
888 procedure update_timestamp
889 (in_type in varchar2,
890 in_attribute in varchar2,
891 in_timestamp in date)
892 --
893 -- Updates/Inserts the row in AD_TIMESTAMPS for the specified
894 -- type and attribute
895 --
896 is
897 begin
898 --
899 -- First try to update
900 --
901 ad_file_util.error_buf := 'update_timestamp(update ad_timestamps '||
902 'set timestamp = '||in_timestamp||
903 'where type = '||in_type||
904 'and attribute = '||in_attribute||'):(';
905 begin
906 update ad_timestamps
907 set timestamp = in_timestamp
908 where type = in_type
909 and attribute = in_attribute;
910 exception
911 when others then
912 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
913 raise;
914 end;
915
916 if SQL%ROWCOUNT = 1 then
917 return;
918 end if;
919 --
920 -- Insert if no rows updated
921 --
922 ad_file_util.error_buf := 'update_timestamp('||
923 'insert into ad_timestamps'||
924 '(type, attribute, timestamp)'||
925 'values ('||in_type||', '||in_attribute||
926 ', '||in_timestamp||'):(';
927
928 begin
929 insert into ad_timestamps
930 (type, attribute, timestamp)
931 values (in_type, in_attribute, in_timestamp);
932 exception
933 when others then
934 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
935 raise;
936 end;
937
938 end update_timestamp;
939 --
940 --
941 procedure update_timestamp
942 (in_type in varchar2,
943 in_attribute in varchar2)
944 is
945 begin
946 update_timestamp
947 (in_type => in_type,
948 in_attribute => in_attribute,
949 in_timestamp => sysdate);
950 end;
951
952 --
953 --
954 --
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.
966 -- To handle batch sizes:
963 --
964 -- Processes all rows in ad_check_file_temp with active_flag='Y'.
965 --
967 --
968 -- 1) - fill up whole table with null active_flag
969 -- - In a loop:
970 -- - update a batch to have active_flag='Y'
971 -- - process the batch
972 -- - delete the batch
973 -- - using 'where rownum < batch+1' is handy here
974 --
975 -- 2) perform (truncate, load, process) cycles in an outer loop where
976 -- only <batch size> rows are loaded and processed at a time.
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
986 -- "no version for this file", so no corresponding record should be
987 -- created in ad_file_versions.
988 --
989 -- Arguments
990 -- none
991 --
992 procedure load_patch_onsite_vers_info
993 is
994 begin
995 --
996 -- process ad_files
997 --
998
999 ad_file_util.load_file_info;
1000
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 '||
1010 'and fv.version = t.manifest_vers '||
1011 'and fv.translation_level = t.translation_level) '||
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
1021 and fv.version = t.manifest_vers
1022 and fv.translation_level = t.translation_level)
1023 where nvl(t.active_flag,'N') = 'Y'
1024 and nvl(t.manifest_vers,'NA')<>'NA';
1025 exception
1026 when others then
1027 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
1028 raise;
1029 end;
1030 --
1031 -- add new entries to ad_file_versions
1032 --
1033 ad_file_util.error_buf := 'load_patch_onsite_vers_info('||
1034 'insert into ad_file_versions '||
1035 '(file_version_id, file_id, version, '||
1036 'translation_level, version_segment1,'||
1037 'version_segment2, version_segment3, '||
1038 'version_segment4, version_segment5, '||
1039 'version_segment6, version_segment7, '||
1040 'version_segment8, version_segment9, '||
1041 'version_segment10, creation_date, created_by, '||
1042 'last_update_date, last_updated_by) '||
1043 '.....):(';
1044 begin
1045 insert into ad_file_versions
1046 (file_version_id, file_id, version, translation_level,
1047 version_segment1, version_segment2, version_segment3,
1048 version_segment4, version_segment5, version_segment6,
1049 version_segment7, version_segment8, version_segment9,
1050 version_segment10,
1051 creation_date, created_by, last_update_date, last_updated_by)
1052 select ad_file_versions_s.nextval,
1053 temp.f_id, temp.vers, temp.trans_level,
1054 temp.vs1, temp.vs2, temp.vs3, temp.vs4, temp.vs5,
1055 temp.vs6, temp.vs7, temp.vs8, temp.vs9, temp.vs10,
1056 temp.edate, 5, temp.edate, 5
1057 from
1058 (select distinct
1059 t.file_id f_id,
1060 t.manifest_vers vers,
1061 t.translation_level trans_level,
1062 decode( instr(t.manifest_vers||'.','.',1,1), 0, null,
1063 to_number(substr(t.manifest_vers||'.',
1064 1,
1065 ( instr(t.manifest_vers||'.','.',1,1)-1)))) vs1,
1066 decode( instr(t.manifest_vers||'.','.',1,2), 0, 0,
1067 to_number(substr(t.manifest_vers||'.',
1068 (instr(t.manifest_vers||'.','.',1,1)+1),
1069 ( (instr(t.manifest_vers||'.','.',1,2))
1070 - (instr(t.manifest_vers||'.','.',1,1)+1)) ))) vs2,
1071 decode( instr(t.manifest_vers||'.','.',1,3), 0, 0,
1072 to_number(substr(t.manifest_vers||'.',
1073 (instr(t.manifest_vers||'.','.',1,2)+1),
1074 ( (instr(t.manifest_vers||'.','.',1,3))
1075 - (instr(t.manifest_vers||'.','.',1,2)+1)) ))) vs3,
1076 decode( instr(t.manifest_vers||'.','.',1,4), 0, 0,
1077 to_number(substr(t.manifest_vers||'.',
1078 (instr(t.manifest_vers||'.','.',1,3)+1),
1079 ( (instr(t.manifest_vers||'.','.',1,4))
1080 - (instr(t.manifest_vers||'.','.',1,3)+1)) ))) vs4,
1081 decode( instr(t.manifest_vers||'.','.',1,5), 0, 0,
1082 to_number(substr(t.manifest_vers||'.',
1083 (instr(t.manifest_vers||'.','.',1,4)+1),
1084 ( (instr(t.manifest_vers||'.','.',1,5))
1085 - (instr(t.manifest_vers||'.','.',1,4)+1)) ))) vs5,
1086 decode( instr(t.manifest_vers||'.','.',1,6), 0, 0,
1087 to_number(substr(t.manifest_vers||'.',
1088 (instr(t.manifest_vers||'.','.',1,5)+1),
1089 ( (instr(t.manifest_vers||'.','.',1,6))
1090 - (instr(t.manifest_vers||'.','.',1,5)+1)) ))) vs6,
1094 ( (instr(t.manifest_vers||'.','.',1,7))
1091 decode( instr(t.manifest_vers||'.','.',1,7), 0, 0,
1092 to_number(substr(t.manifest_vers||'.',
1093 (instr(t.manifest_vers||'.','.',1,6)+1),
1095 - (instr(t.manifest_vers||'.','.',1,6)+1)) ))) vs7,
1096 decode( instr(t.manifest_vers||'.','.',1,8), 0, 0,
1097 to_number(substr(t.manifest_vers||'.',
1098 (instr(t.manifest_vers||'.','.',1,7)+1),
1099 ( (instr(t.manifest_vers||'.','.',1,8))
1100 - (instr(t.manifest_vers||'.','.',1,7)+1)) ))) vs8,
1101 decode( instr(t.manifest_vers||'.','.',1,9), 0, 0,
1102 to_number(substr(t.manifest_vers||'.',
1103 (instr(t.manifest_vers||'.','.',1,8)+1),
1104 ( (instr(t.manifest_vers||'.','.',1,9))
1105 - (instr(t.manifest_vers||'.','.',1,8)+1)) ))) vs9,
1106 decode( instr(t.manifest_vers||'.','.',1,10), 0, 0,
1107 to_number(substr(t.manifest_vers||'.',
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
1117 when others then
1118 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
1119 raise;
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 '||
1129 'and fv.version = t.manifest_vers '||
1130 'and fv.translation_level = t.translation_level) '||
1131 'where nvl(t.active_flag,''N'') = ''Y'' '||
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
1141 and fv.version = t.manifest_vers
1142 and fv.translation_level = t.translation_level)
1143 where nvl(t.active_flag,'N') = 'Y'
1144 and nvl(t.manifest_vers,'NA')<>'NA';
1145 exception
1146 when others then
1147 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
1148 raise;
1149 end;
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 '||
1159 'and fv.version = t.manifest_vers_2 '||
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
1169 and fv.version = t.manifest_vers_2
1170 and fv.translation_level = t.translation_level)
1171 where nvl(t.active_flag,'N') = 'Y' AND
1172 nvl(t.manifest_vers_2,'NA')<>'NA';
1173 exception
1174 when others then
1175 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
1176 raise;
1177 end;
1178
1179 --
1180 -- add new entries to ad_file_versions
1181 --
1182 ad_file_util.error_buf := 'load_patch_onsite_vers_info('||
1183 'insert into ad_file_versions '||
1184 '(file_version_id, file_id, version, '||
1185 'translation_level, version_segment1,'||
1186 'version_segment2, version_segment3, '||
1187 'version_segment4, version_segment5, '||
1188 'version_segment6, version_segment7, '||
1189 'version_segment8, version_segment9, '||
1190 'version_segment10, creation_date, created_by, '||
1191 'last_update_date, last_updated_by) '||
1192 '.....):(';
1193 --
1194 begin
1195 insert into ad_file_versions
1196 (file_version_id, file_id, version, translation_level,
1197 version_segment1, version_segment2, version_segment3,
1198 version_segment4, version_segment5, version_segment6,
1199 version_segment7, version_segment8, version_segment9,
1200 version_segment10,
1201 creation_date, created_by, last_update_date, last_updated_by)
1202 select ad_file_versions_s.nextval,
1203 temp.f_id, temp.vers, temp.trans_level,
1204 temp.vs1, temp.vs2, temp.vs3, temp.vs4, temp.vs5,
1205 temp.vs6, temp.vs7, temp.vs8, temp.vs9, temp.vs10,
1206 temp.edate, 5, temp.edate, 5
1207 from
1208 (select distinct
1209 t.file_id f_id,
1210 t.manifest_vers_2 vers,
1211 t.translation_level trans_level,
1212 decode( instr(t.manifest_vers_2||'.','.',1,1), 0, null,
1213 to_number(substr(t.manifest_vers_2||'.',
1214 1,
1215 ( instr(t.manifest_vers_2||'.','.',1,1)-1)))) vs1,
1219 ( (instr(t.manifest_vers_2||'.','.',1,2))
1216 decode( instr(t.manifest_vers_2||'.','.',1,2), 0, 0,
1217 to_number(substr(t.manifest_vers_2||'.',
1218 (instr(t.manifest_vers_2||'.','.',1,1)+1),
1220 - (instr(t.manifest_vers_2||'.','.',1,1)+1)) ))) vs2,
1221 decode( instr(t.manifest_vers_2||'.','.',1,3), 0, 0,
1222 to_number(substr(t.manifest_vers_2||'.',
1223 (instr(t.manifest_vers_2||'.','.',1,2)+1),
1224 ( (instr(t.manifest_vers_2||'.','.',1,3))
1225 - (instr(t.manifest_vers_2||'.','.',1,2)+1)) ))) vs3,
1226 decode( instr(t.manifest_vers_2||'.','.',1,4), 0, 0,
1227 to_number(substr(t.manifest_vers_2||'.',
1228 (instr(t.manifest_vers_2||'.','.',1,3)+1),
1229 ( (instr(t.manifest_vers_2||'.','.',1,4))
1230 - (instr(t.manifest_vers_2||'.','.',1,3)+1)) ))) vs4,
1231 decode( instr(t.manifest_vers_2||'.','.',1,5), 0, 0,
1232 to_number(substr(t.manifest_vers_2||'.',
1233 (instr(t.manifest_vers_2||'.','.',1,4)+1),
1234 ( (instr(t.manifest_vers_2||'.','.',1,5))
1235 - (instr(t.manifest_vers_2||'.','.',1,4)+1)) ))) vs5,
1236 decode( instr(t.manifest_vers_2||'.','.',1,6), 0, 0,
1237 to_number(substr(t.manifest_vers_2||'.',
1238 (instr(t.manifest_vers_2||'.','.',1,5)+1),
1239 ( (instr(t.manifest_vers_2||'.','.',1,6))
1240 - (instr(t.manifest_vers_2||'.','.',1,5)+1)) ))) vs6,
1241 decode( instr(t.manifest_vers_2||'.','.',1,7), 0, 0,
1242 to_number(substr(t.manifest_vers_2||'.',
1243 (instr(t.manifest_vers_2||'.','.',1,6)+1),
1244 ( (instr(t.manifest_vers_2||'.','.',1,7))
1245 - (instr(t.manifest_vers_2||'.','.',1,6)+1)) ))) vs7,
1246 decode( instr(t.manifest_vers_2||'.','.',1,8), 0, 0,
1247 to_number(substr(t.manifest_vers_2||'.',
1248 (instr(t.manifest_vers_2||'.','.',1,7)+1),
1249 ( (instr(t.manifest_vers_2||'.','.',1,8))
1250 - (instr(t.manifest_vers_2||'.','.',1,7)+1)) ))) vs8,
1251 decode( instr(t.manifest_vers_2||'.','.',1,9), 0, 0,
1252 to_number(substr(t.manifest_vers_2||'.',
1253 (instr(t.manifest_vers_2||'.','.',1,8)+1),
1254 ( (instr(t.manifest_vers_2||'.','.',1,9))
1255 - (instr(t.manifest_vers_2||'.','.',1,8)+1)) ))) vs9,
1256 decode( instr(t.manifest_vers_2||'.','.',1,10), 0, 0,
1257 to_number(substr(t.manifest_vers_2||'.',
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
1267 when others then
1268 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
1269 raise;
1270 end;
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 '||
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
1288 and fv.version = t.manifest_vers_2
1289 and fv.translation_level = t.translation_level)
1290 where nvl(t.active_flag,'N') = 'Y' AND
1291 nvl(t.manifest_vers_2,'NA')<>'NA';
1292 exception
1293 when others then
1294 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
1295 raise;
1296 end;
1297 --
1298 -- done processing ad_file_versions
1299 --
1300 end load_patch_onsite_vers_info;
1301
1302 --
1303 --
1304 --
1305 -- Procedure
1306 -- load_snapshot_file_info
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.
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
1323 --
1324 --
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;
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;
1339 --
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
1340 vers_id_list t_version_id;
1341 chk_sum_list t_check_sum;
1342 fl_size_list t_file_size;
1343 fl_id_list t_file_id;
1344 con_file_id_list t_containing_file_id;
1345 dest_file_id_list t_dest_file_id;
1346 file_type_flag_list t_file_type_flag;
1347 irep_gathered_flag_list t_irep_gathered_flag;
1348 effective_date_list t_effective_date;
1349
1350 --
1351 --
1352 --
1353 cursor c1 is
1354 select
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 --
1364 cur_rec c1%ROWTYPE;
1365 rows NATURAL := 2000;
1366 --
1367 --
1368 --
1369 begin
1370 --
1371 -- process ad_files and ad_file_versions
1372 --
1373 begin
1374 ad_file_util.load_file_version_info;
1375 exception
1376 when others then
1377 ad_file_util.error_buf := 'load_snapshot_file_info('||snp_id||'):('||
1378 ad_file_util.error_buf||sqlerrm||')';
1379 raise;
1380 end;
1381 --
1382 -- get contain_file_id from ad_files
1383 --
1384 -- The containing files are already inserted into ad_files
1385 -- by the procedure load_file_version_info so we are
1386 -- guaranteed to get the file_id from ad_files.
1387 --
1388 -- done processing ad_files
1389 --
1390 --
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
1400 when others then
1401 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
1402 raise;
1403 end;
1404 --
1405 --
1406 LOOP
1407 --
1408 --
1409 --
1410 FETCH c1 BULK COLLECT INTO
1411 vers_id_list ,chk_sum_list ,fl_size_list ,
1412 fl_id_list ,con_file_id_list,
1413 dest_file_id_list, file_type_flag_list,
1414 irep_gathered_flag_list,
1415 effective_date_list
1416 LIMIT rows;
1417 --
1418 --
1419 if fl_id_list.COUNT > 0 then
1420 --
1421 --
1422 --
1423 FORALL j IN fl_id_list.FIRST.. fl_id_list.LAST
1424 update /*+ INDEX(SNAP AD_SNAPSHOT_FILES_U2) */
1425 ad_snapshot_files snap
1426 set
1427 snap.file_version_id = vers_id_list(j),
1428 snap.file_size = fl_size_list(j),
1429 snap.checksum = chk_sum_list(j),
1430 snap.dest_file_id = dest_file_id_list(j),
1431 snap.file_type_flag = file_type_flag_list(j),
1432 snap.update_source_id = snp_id,
1433 -- Intentionally storing 'U' so that these rows will be marked
1434 -- so that we can know which rows were updated
1435 snap.update_type ='U',
1436 snap.last_update_date = sysdate,
1437 snap.last_patched_date = decode(preserve_irep_flag,1,
1438 snap.last_patched_date,
1439 decode ((effective_date_list(j) - snap.last_patched_date) -
1440 abs(effective_date_list(j) - snap.last_patched_date),
1441 0, effective_date_list(j), snap.last_patched_date)),
1442 snap.irep_gathered_flag = decode(preserve_irep_flag,1,
1443 snap.irep_gathered_flag,
1444 irep_gathered_flag_list(j))
1445 where
1446 snap.snapshot_id=snp_id and
1447 snap.file_id =fl_id_list(j) and
1448 nvl(snap.containing_file_id,-1)=nvl(con_file_id_list(j),-1);
1449 --
1450 --
1451 end if;
1452 --
1453 --
1454
1455 EXIT WHEN c1%NOTFOUND;
1456 --
1457 --
1458 --
1459 END LOOP;
1460 --
1461 --
1462 --
1463 begin
1464 close c1;
1465 exception
1466 when others then
1467 ad_file_util.error_buf := 'load_snapshot_file_info(Close cursor):('||
1468 sqlerrm||')';
1469 end;
1470 --
1471 --
1472 --
1473 ad_file_util.error_buf := 'load_snapshot_file_info('||
1474 'INSERT INTO ad_snapshot_files '||
1475 '(snapshot_file_id,snapshot_id,file_id, '||
1476 'containing_file_id,file_size,checksum,'||
1477 'file_version_id, update_source_id, '||
1478 'update_type,creation_date,last_update_date,' ||
1479 'last_updated_by,created_by,' ||
1480 'appl_top_id, inconsistent_flag, '||
1481 'dest_file_id, file_type_flag) '||
1482 'select ad_snapshot_files_s.nextval,'||
1483 'snp_id,t.file_id, t.check_file_id,'||
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 '||
1494 'nvl(t.check_file_id,-1)):(';
1491 'where sf2.snapshot_id = snp_id '||
1492 'and sf2.file_id = t.file_id '||
1493 'and nvl(sf2.containing_file_id,-1) = '||
1495
1496 begin
1497
1498 INSERT INTO ad_snapshot_files
1499 (snapshot_file_id,snapshot_id,file_id,
1500 containing_file_id,file_size,checksum,file_version_id,
1501 update_source_id, update_type,creation_date,last_update_date,
1502 last_updated_by,created_by, appl_top_id, inconsistent_flag,
1503 dest_file_id, file_type_flag, irep_gathered_flag,last_patched_date)
1504 select
1505 ad_snapshot_files_s.nextval,snp_id,t.file_id,
1506 t.check_file_id,t.file_size,t.check_sum,
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
1516 and sf2.file_id = t.file_id
1517 and nvl(sf2.containing_file_id,-1) = nvl(t.check_file_id,-1)
1518 );
1519 exception
1520 when others then
1521 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
1522 raise;
1523 end;
1524
1525 update ad_snapshots set last_update_date = sysdate
1526 where snapshot_id = snp_id;
1527
1528 --
1529 --
1530 --
1531 --
1532 end load_snapshot_file_info;
1533 --
1534 --
1535 --
1536 -- Procedure
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 --
1546 -- Inserts those BUG_IDs into AD_SNAPSHOT_BUGFIXES
1547 --
1548 --
1549 -- Arguments
1550 -- None
1551 procedure load_preseeded_bugfixes
1552 is
1553 begin
1554 --
1555 -- Get the bug_id from ad_bugs
1556 --
1557 -- Bug 5758908 - stangutu - 14 June, 2007
1558 ad_file_util.error_buf := 'load_preseeded_bugfixes('||
1559 'SET t.file_id = (SELECT b.bug_id '||
1560 'FROM ad_bugs b WHERE b.bug_number = t.filename '||
1561 'AND b.aru_release_name = t.subdir '||
1562 'AND b.trackable_entity_abbr=t.app_short_name '||
1563 'AND b.language = t.language '||
1564 'AND b.baseline_name = t.manifest_vers_2), '||
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
1574 -- bug 6317065 diverma Thu Aug 2 04:10:21 PDT 2007
1575 AND b.trackable_entity_abbr = t.app_short_name
1576 -- bug 5615204 diverma Tuesday, August 07, 2007
1577 AND b.language = t.language
1578 -- Bug 5596989 - stangutu - 17 Oct, 2006
1579 -- Bug 5758908 - stangutu - 14 June, 2007
1580 AND b.baseline_name = t.manifest_vers_2),
1581 -- If the above condition does not work, we need to include below line.
1582 -- AND b.generic_patch = t.manifest_vers_higher),
1583 t.junk = NULL
1584 WHERE NVL(t.active_flag,'N') = 'Y';
1585 exception
1586 when others then
1587 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
1588 raise;
1589 end;
1590 --
1591 -- add new entries in ad_bugs
1592 --
1593 -- Bug 5758908 - stangutu - 14 Jun, 2007 */
1594 ad_file_util.error_buf := 'load_preseeded_bugfixes('||
1595 'INSERT INTO ad_bugs '||
1596 '(bug_id, bug_number,aru_release_name, '||
1597 'creation_date, created_by, last_update_date, '||
1598 'last_updated_by, baseline_name, generic_patch, '||
1599 ' trackable_entity_abbr ) SELECT '||
1600 'ad_bugs_s.nextval, temp.bugfix, temp.rel, '||
1601 'temp.edate, 5, temp.edate, 5, '||
1602 'temp.baseline_name, temp.generic_patch, '||
1603 'temp.trackable_entity_abbr, language)' ||
1604 'FROM (SELECT DISTINCT t.filename bugfix, '||
1605 't.subdir rel, t.effective_date edate '||
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
1615 (bug_id, bug_number,aru_release_name,
1616 creation_date, created_by, last_update_date, last_updated_by,
1617 -- Bug 5758908 - stangutu - 14 June, 2007
1618 baseline_name, generic_patch, trackable_entity_abbr,
1619 -- bug 5615204 diverma Tuesday, August 07, 2007
1620 language)
1621 SELECT
1625 temp.baseline_name, temp.generic_patch, temp.trackable_entity_abbr,
1622 ad_bugs_s.nextval, temp.bugfix, temp.rel,
1623 temp.edate, 5, temp.edate, 5,
1624 -- Bug 5758908 - stangutu - 14 June, 2007
1626 -- bug 5615204 diverma Tuesday, August 07, 2007
1627 temp.language
1628 FROM
1629 (SELECT DISTINCT
1630 t.filename bugfix,
1631 t.subdir rel ,
1632 t.effective_date edate,
1633 -- Bug 5758908 - stangutu - 14 June, 2007
1634 t.manifest_vers_2 baseline_name,
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
1644 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
1645 raise;
1646 end;
1647 --
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' ||
1657 'AND b.language = t.language '||
1658 'AND b.baseline_name = t.manifest_vers_2), '||
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
1668 -- bug 6317065 diverma Thu Aug 2 04:10:21 PDT 2007
1669 AND b.trackable_entity_abbr = t.app_short_name
1670 -- bug 5615204 diverma Tuesday, August 07, 2007
1671 AND b.language = t.language
1672 -- Bug 5596989 - stangutu -17Oct, 2006
1673 -- Bug 5758908 - stangutu - 14 June, 2007
1674 AND b.baseline_name = t.manifest_vers_2),
1675 -- If the above condition does not work, we need to include below line.
1676 -- AND b.generic_patch = t.manifest_vers_higher),
1677 t.junk = NULL
1678 WHERE NVL(t.active_flag,'N') = 'Y';
1679 exception
1680 when others then
1681 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
1682 raise;
1683 end;
1684 --
1685 -- store the pre-seed the list of bug fixes included
1686 -- in that Maintenance Pack.
1687 --
1688 ad_file_util.error_buf := 'load_preseeded_bugfixes('||
1689 'INSERT into ad_snapshot_bugfixes( '||
1690 'snapshot_bug_id,snapshot_id, '||
1691 'bugfix_id,bug_status,success_flag, '||
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):(';
1701
1702
1703 begin
1704 INSERT into ad_snapshot_bugfixes(
1705 snapshot_bug_id,snapshot_id,
1706 bugfix_id,bug_status,success_flag,creation_date,
1707 last_update_date,last_updated_by,created_by)
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
1717 b.SNAPSHOT_ID=t.file_version_id_2);
1718 exception
1719 when others then
1720 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
1721 raise;
1722 end;
1723 end load_preseeded_bugfixes;
1724 --
1725 --
1726 --
1727 --
1728 procedure load_patch_hist_action
1729 (bugs_processed out NOCOPY number,
1730 actions_processed out NOCOPY number)
1731 is
1732 l_bugs_processed number := 0;
1733 l_actions_processed number := 0;
1734 --
1735 -- for deleting duplicate actions
1736 --
1737 cursor del_cursor is
1738 select patch_run_bug_id, common_action_id, file_id, rowid row_id
1739 from ad_patch_hist_temp
1740 where (patch_run_bug_id, common_action_id, file_id) in
1741 (select patch_run_bug_id, common_action_id, file_id
1742 from AD_PATCH_HIST_TEMP
1743 group by patch_run_bug_id, common_action_id, file_id
1744 having count(*) > 1)
1745 order by 1, 2, 3;
1746 prb_id number;
1747 ca_id number;
1748 f_id number;
1749 statement varchar2(200);
1750 --
1751 -- end for deleting duplicate actions
1752 --
1753 begin
1754
1755 -- bug 6343734 diverma 16 August 2007
1756 --
1757 -- update AD_PATCH_HIST_TEMP.TRACKABLE_ENTITY_ABBR with
1761 update AD_PATCH_HIST_TEMP
1758 -- AD_PATCH_HIST_TEMP.BUG_APP_SHORT_NAME if it is null.
1759 --
1760
1762 set TRACKABLE_ENTITY_NAME = BUG_APP_SHORT_NAME
1763 where TRACKABLE_ENTITY_NAME is null;
1764
1765 update AD_PATCH_HIST_TEMP
1766 set LANGUAGE = 'US'
1767 where LANGUAGE is null;
1768
1769 --
1770 -- Add new entries in AD_BUGS
1771 --
1772 insert into ad_bugs
1773 (
1774 BUG_ID, BUG_NUMBER, ARU_RELEASE_NAME, CREATION_DATE,
1775 CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
1776 -- bug 5615204 diverma Tuesday, August 07, 2007
1777 TRACKABLE_ENTITY_ABBR, BASELINE_NAME, GENERIC_PATCH, LANGUAGE
1778 )
1779 -- bug 6332450 diverma Thu Aug 9 06:25:06 PDT 2007
1780 select
1781 ad_bugs_s.nextval, BUG_NUMBER, ARU_RELEASE_NAME, sysdate,
1782 5, sysdate, 5, TRACKABLE_ENTITY_NAME , BASELINE_NAME,
1783 GENERIC_PATCH, LANGUAGE
1784 from
1785 (
1786 select
1787 distinct BUG_NUMBER, ARU_RELEASE_NAME,
1788 -- bug 6332450 diverma Thu Aug 9 06:25:06 PDT 2007
1789 -- bug 5615204 diverma Tuesday, August 07, 2007
1790 TRACKABLE_ENTITY_NAME, BASELINE_NAME, GENERIC_PATCH, LANGUAGE
1791 from
1792 AD_PATCH_HIST_TEMP where BUG_NUMBER is not null) tmp
1793 where
1794 not exists (
1795 select
1796 'x'
1797 from
1798 ad_bugs b
1799 where
1800 b.bug_number = tmp.BUG_NUMBER
1801 -- bug 6332450 diverma Thu Aug 9 06:25:06 PDT 2007
1802 and b.trackable_entity_abbr = tmp.TRACKABLE_ENTITY_NAME
1803 and b.baseline_name = tmp.baseline_name
1804 and b.aru_release_name = tmp.aru_release_name
1805 -- bug 5615204 diverma Tuesday, August 07, 2007
1806 and b.language = tmp.LANGUAGE
1807 );
1808 -- schinni bug 5612532 25th Oct 2006
1809 -- ----------------------------------------------------------
1810 -- Changed the condition in the subquery .
1811 -- Earlier condition " b.generic_patch=y " was returning multiple
1812 -- rows for a single row return subquery.
1813 -- Using the generic_patch column present in the ad_patch_hist_temp
1814 -- for refining the search condition in subquery
1815 -- and to return a single row
1816 -- -----------------------------------------------------------
1817 --
1818 -- Get the Bug_id into the Staging Table
1819 --
1820 update AD_PATCH_HIST_TEMP t
1821 set t.bug_id = (
1822 select
1823 b.bug_id from ad_bugs b
1824 where
1825 b.bug_number = t.BUG_NUMBER
1826 -- bug 6332450 diverma Thu Aug 9 06:25:06 PDT 2007
1827 and b.trackable_entity_abbr = t.TRACKABLE_ENTITY_NAME
1828 and nvl(b.baseline_name,'NULL') = nvl(t.baseline_name,'NULL')
1829 and b.aru_release_name = t.aru_release_name
1830 -- bug 5615204 diverma Tuesday, August 07, 2007
1831 and b.language = t.LANGUAGE
1832 );
1833 --
1834 --
1835 commit;
1836 --
1837 -- Add new entries in the AD_PATCH_RUN_BUGS
1838 --
1839 insert into ad_patch_run_bugs
1840 (
1841 PATCH_RUN_BUG_ID,
1842 PATCH_RUN_ID, BUG_ID, ORIG_BUG_NUMBER, APPLICATION_SHORT_NAME,
1843 SUCCESS_FLAG, APPLIED_FLAG, REASON_NOT_APPLIED,
1844 CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY
1845 )
1846 select
1847 ad_patch_run_bugs_s.nextval,
1848 patch_run_id, bug_id, orig_bug_number,bug_app_short_name,
1849 success_flag, applied_flag, reason_not_applied,
1850 sysdate, sysdate, 5, 5
1851 from (
1852 select
1853 distinct patch_run_id,bug_id,
1854 orig_bug_number, bug_app_short_name,
1855 success_flag, applied_flag, reason_not_applied
1856 from
1857 AD_PATCH_HIST_TEMP ) t
1858 where
1859 not exists (
1860 select
1861 'x'
1862 from ad_patch_run_bugs b
1863 where
1864 b.PATCH_RUN_ID = t.patch_run_id
1865 and b.BUG_ID = t.bug_id
1866 and b.ORIG_BUG_NUMBER = t.orig_bug_number
1867 and b.APPLICATION_SHORT_NAME = t.bug_app_short_name);
1868
1869 l_bugs_processed := sql%rowcount;
1870 bugs_processed := l_bugs_processed;
1871 --
1872 -- Get the patch_run_bug_id into staging table
1873 --
1874 update AD_PATCH_HIST_TEMP t
1875 set PATCH_RUN_BUG_ID
1876 =(select
1877 b.PATCH_RUN_BUG_ID
1878 from
1879 ad_patch_run_bugs b
1880 where
1881 b.PATCH_RUN_ID = t.patch_run_id
1882 and b.BUG_ID = t.bug_id
1883 and b.ORIG_BUG_NUMBER = t.orig_bug_number
1884 and b.APPLICATION_SHORT_NAME = t.bug_app_short_name);
1885 --
1886 --
1887 commit;
1888 --
1889 -- Add new entries in ad_files
1890 --
1891 insert into ad_files
1892 (file_id,
1893 app_short_name,
1894 subdir, filename,
1895 creation_date, created_by, last_update_date, last_updated_by)
1896 select ad_files_s.nextval,
1897 temp.FILE_APPS_SHORT_NAME asn,
1898 temp.file_subdir dir, temp.filename fname,
1899 sysdate,5,sysdate,5
1900 from
1901 (select distinct
1902 t.file_apps_short_name ,
1903 t.file_subdir ,
1904 t.filename
1905 from
1906 AD_PATCH_HIST_TEMP t
1907 ) temp
1908 where not exists (
1909 select
1910 'x' from ad_files fl
1911 where
1912 fl.filename = temp.filename
1913 and fl.subdir = temp.file_subdir
1914 and fl.app_short_name = temp.file_apps_short_name
1915 )
1916 and temp.filename is not null;
1917 --
1918 -- Get the file_id into the staging table
1919 --
1920 update AD_PATCH_HIST_TEMP t
1924 where
1921 set t.file_id =
1922 (select f.file_id
1923 from ad_files f
1925 f.filename = t.filename
1926 and f.subdir = t.file_subdir
1927 and f.app_short_name = t.file_apps_short_name);
1928 --
1929 --
1930 commit;
1931 --
1932 -- Add new entries in ad_files for Loader files
1933 --
1934 insert into ad_files
1935 (file_id, app_short_name, subdir, filename,
1936 creation_date, created_by, last_update_date, last_updated_by)
1937 select ad_files_s.nextval,
1938 temp.ldr_app_short_name asn,
1939 temp.ldr_subdir dir, temp.ldr_filename fname,
1940 sysdate, 5, sysdate, 5
1941 from
1942 (select distinct
1943 t.ldr_app_short_name ,
1944 t.ldr_subdir ,
1945 t.ldr_filename
1946 from
1947 AD_PATCH_HIST_TEMP t
1948 ) temp
1949 where not exists (
1950 select
1951 'x' from ad_files fl
1952 where
1953 fl.filename = temp.ldr_filename
1954 and fl.subdir = temp.ldr_subdir
1955 and fl.app_short_name = temp.ldr_app_short_name
1956 )
1957 and temp.ldr_filename is not null;
1958 --
1959 -- Get the Loader file_id into the staging table
1960 --
1961 update AD_PATCH_HIST_TEMP t
1962 set t.loader_data_file_id =
1963 (select f.file_id
1964 from ad_files f
1965 where
1966 f.filename = t.ldr_filename
1967 and f.subdir = t.ldr_subdir
1968 and f.app_short_name = t.ldr_app_short_name)
1969 where t.ldr_filename is not null;
1970 --
1971 --
1972 commit;
1973
1974 --
1975 -- Add new entries in ad_files for the destination files
1976 --
1977 insert into ad_files
1978 (
1979 file_id,
1980 app_short_name,
1981 subdir,
1982 filename,
1983 creation_date, last_update_date, last_updated_by, created_by
1984 )
1985 select
1986 ad_files_s.nextval,
1987 temp.dest_apps_short_name,
1988 temp.dest_subdir,
1989 temp.dest_filename,
1990 sysdate, sysdate, 5, 5
1991 from
1992 (select distinct
1993 t.dest_apps_short_name ,
1994 t.dest_subdir ,
1995 t.dest_filename
1996 from
1997 AD_PATCH_HIST_TEMP t
1998 where t.dest_apps_short_name is not null
1999 and t.dest_subdir is not null
2000 and t.dest_filename is not null
2001 ) temp
2002 where not exists (
2003 select
2004 'dest file already exists' from ad_files f
2005 where
2006 f.filename = temp.dest_filename
2007 and f.subdir = temp.dest_subdir
2008 and f.app_short_name = temp.dest_apps_short_name);
2009
2010 --
2011 -- Get the Destination file_id into the staging table
2012 --
2013 update AD_PATCH_HIST_TEMP t
2014 set t.dest_file_id =
2015 (select /*+ INDEX(F AD_FILES_U2) */ f.file_id
2016 from ad_files f
2017 where f.app_short_name = t.dest_apps_short_name
2018 and f.subdir = t.dest_subdir
2019 and f.filename = t.dest_filename);
2020 --
2021 --
2022 commit;
2023
2024 --
2025 -- Add new entries in the ad_file_versions
2026 --
2027 INSERT into ad_file_versions
2028 (file_version_id, file_id, version, translation_level,
2029 version_segment1, version_segment2, version_segment3,
2030 version_segment4, version_segment5, version_segment6,
2031 version_segment7, version_segment8, version_segment9,
2032 version_segment10,
2033 creation_date, created_by, last_update_date, last_updated_by)
2034 select
2035 ad_file_versions_s.nextval,
2036 temp.f_id, temp.vers, temp.trans_level,
2037 temp.vs1, temp.vs2, temp.vs3, temp.vs4, temp.vs5,
2038 temp.vs6, temp.vs7, temp.vs8, temp.vs9, temp.vs10,
2039 sysdate, 5, sysdate, 5
2040 from
2041 (
2042 select
2043 distinct
2044 t.file_id f_id,
2045 t.PATCH_FILE_VERS vers,
2046 t.PATCH_TRANS_LEVEL trans_level,
2047 t.PATCH_VERSION_SEGMENT1 vs1,
2048 t.PATCH_VERSION_SEGMENT2 vs2,
2049 t.PATCH_VERSION_SEGMENT3 vs3,
2050 t.PATCH_VERSION_SEGMENT4 vs4,
2051 t.PATCH_VERSION_SEGMENT5 vs5,
2052 t.PATCH_VERSION_SEGMENT6 vs6,
2053 t.PATCH_VERSION_SEGMENT7 vs7,
2054 t.PATCH_VERSION_SEGMENT8 vs8,
2055 t.PATCH_VERSION_SEGMENT9 vs9,
2056 t.PATCH_VERSION_SEGMENT10 vs10
2057 from
2058 AD_PATCH_HIST_TEMP t
2059 where
2060 t.PATCH_FILE_VERS is not null
2061 ) temp
2062 where not exists (
2063 select
2064 'x'
2065 from
2066 ad_file_versions vers
2067 where
2068 vers.file_id = temp.f_id
2069 and vers.version = temp.vers
2070 and vers.translation_level = temp.trans_level);
2071 --
2072 -- Add new entries in the ad_file_versions
2073 --
2074 INSERT into ad_file_versions
2075 (file_version_id, file_id, version, translation_level,
2076 version_segment1, version_segment2, version_segment3,
2077 version_segment4, version_segment5, version_segment6,
2078 version_segment7, version_segment8, version_segment9,
2079 version_segment10,
2080 creation_date, created_by, last_update_date, last_updated_by)
2081 select
2082 ad_file_versions_s.nextval,
2083 temp.f_id, temp.vers, temp.trans_level,
2084 temp.vs1, temp.vs2, temp.vs3, temp.vs4, temp.vs5,
2085 temp.vs6, temp.vs7, temp.vs8, temp.vs9, temp.vs10,
2086 sysdate, 5, sysdate, 5
2087 from
2088 (
2089 select
2090 distinct t.file_id f_id,
2091 t.ONSITE_FILE_VERS vers,
2095 t.ONSITE_VERSION_SEGMENT3 vs3,
2092 t.ONSITE_TRANS_LEVEL trans_level,
2093 t.ONSITE_VERSION_SEGMENT1 vs1,
2094 t.ONSITE_VERSION_SEGMENT2 vs2,
2096 t.ONSITE_VERSION_SEGMENT4 vs4,
2097 t.ONSITE_VERSION_SEGMENT5 vs5,
2098 t.ONSITE_VERSION_SEGMENT6 vs6,
2099 t.ONSITE_VERSION_SEGMENT7 vs7,
2100 t.ONSITE_VERSION_SEGMENT8 vs8,
2101 t.ONSITE_VERSION_SEGMENT9 vs9,
2102 t.ONSITE_VERSION_SEGMENT10 vs10
2103 from
2104 AD_PATCH_HIST_TEMP t
2105 where
2106 t.ONSITE_FILE_VERS is not NULL
2107 ) temp
2108 where not exists (
2109 select
2110 'x'
2111 from
2112 ad_file_versions vers
2113 where
2114 vers.file_id = temp.f_id
2115 and vers.version = temp.vers
2116 and vers.translation_level = temp.trans_level);
2117 --
2118 -- Add new entries in the ad_file_versions
2119 --
2120 INSERT into ad_file_versions
2121 (file_version_id, file_id, version, translation_level,
2122 version_segment1, version_segment2, version_segment3,
2123 version_segment4, version_segment5, version_segment6,
2124 version_segment7, version_segment8, version_segment9,
2125 version_segment10,
2126 creation_date, created_by, last_update_date, last_updated_by)
2127 select
2128 ad_file_versions_s.nextval,
2129 tmp.f_id,tmp.vers, tmp.trans_level,
2130 tmp.vs1, tmp.vs2, tmp.vs3, tmp.vs4,
2131 tmp.vs5, tmp.vs6, tmp.vs7, tmp.vs8,
2132 tmp.vs9, tmp.vs10,sysdate, 5, sysdate, 5
2133 from
2134 (
2135 select
2136 distinct
2137 t.file_id f_id,
2138 t.DB_FILE_VERS vers,
2139 t.DB_TRANS_LEVEL trans_level,
2140 t.DB_VERSION_SEGMENT1 vs1 ,
2141 t.DB_VERSION_SEGMENT2 vs2 ,
2142 t.DB_VERSION_SEGMENT3 vs3 ,
2143 t.DB_VERSION_SEGMENT4 vs4 ,
2144 t.DB_VERSION_SEGMENT5 vs5 ,
2145 t.DB_VERSION_SEGMENT6 vs6 ,
2146 t.DB_VERSION_SEGMENT7 vs7 ,
2147 t.DB_VERSION_SEGMENT8 vs8 ,
2148 t.DB_VERSION_SEGMENT9 vs9 ,
2149 t.DB_VERSION_SEGMENT10 vs10
2150 from
2151 AD_PATCH_HIST_TEMP t
2152 where
2153 t.DB_FILE_VERS is not null
2154 ) tmp
2155 where not exists (
2156 select
2157 'x'
2158 from
2159 ad_file_versions vers
2160 where
2161 vers.file_id = tmp.f_id
2162 and vers.version = tmp.vers
2163 and vers.translation_level = tmp.trans_level);
2164 --
2165 --
2166 commit;
2167 --
2168 --
2169 -- Process the PatchFile Versions
2170 --
2171 -- Get the file_version_id into the staging table
2172 --
2173 update AD_PATCH_HIST_TEMP t
2174 set t.PATCH_FILE_VERS_ID =
2175 (select
2176 fv.file_version_id
2177 from
2178 ad_file_versions fv
2179 where
2180 fv.file_id = t.file_id
2181 and fv.version = t.PATCH_FILE_VERS
2182 and fv.translation_level = t.PATCH_TRANS_LEVEL)
2183 where
2184 t.PATCH_FILE_VERS is not NULL;
2185 --
2186 --
2187 -- Process the OnSiteFile Versions
2188 --
2189 --
2190 -- Get the file_version_id into the staging table
2191 --
2192 --
2193 update AD_PATCH_HIST_TEMP t
2194 set t.ONSITE_FILE_VERS_ID =
2195 (select
2196 fv.file_version_id
2197 from
2198 ad_file_versions fv
2199 where
2200 fv.file_id = t.file_id
2201 and fv.version = t.ONSITE_FILE_VERS
2202 and fv.translation_level = t.ONSITE_TRANS_LEVEL
2203 )
2204 where
2205 t.ONSITE_FILE_VERS is not NULL;
2206 --
2207 --
2208 -- Process the Db FileVersions
2209 --
2210 -- Get the file_version_id into the staging table
2211 --
2212 update AD_PATCH_HIST_TEMP t
2213 set t.DB_FILE_VERS_ID =
2214 (select
2215 fv.file_version_id
2216 from
2217 ad_file_versions fv
2218 where
2219 fv.file_id = t.file_id
2220 and fv.version = t.DB_FILE_VERS
2221 and fv.translation_level = t.DB_TRANS_LEVEL)
2222 where
2223 t.DB_FILE_VERS is not NULL;
2224 --
2225 --
2226 commit;
2227 --
2228 -- Add new entries in the ad_patch_common_actions
2229 --
2230 INSERT INTO AD_PATCH_COMMON_ACTIONS
2231 (
2232 COMMON_ACTION_ID, ACTION_CODE, ACTION_PHASE, NUMERIC_PHASE,
2233 NUMERIC_SUB_PHASE, ACTION_ARGUMENTS, CHECKFILE_ARGS,
2234 ACTION_CHECK_OBJ, ACTION_CHECK_OBJ_USERNAME, ACTION_CHECK_OBJ_PASSWD,
2235 ACTION_WHAT_SQL_EXEC, ACTION_TIERLIST_IN_DRIVER, ACTION_LANG_CODE,
2236 CONCAT_ATTRIBS, LOADER_DATA_FILE_ID, CREATION_DATE,
2237 LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY
2238 )
2239 select AD_PATCH_COMMON_ACTIONS_S.NEXTVAL,
2240 t.action_code, t.action_phase, t.major_phase, t.minor_phase,
2241 t.action_arguments, t.checkfile_args, t.checkobj , t.checkobj_un ,
2242 t.checkobj_pw, t.action_modifier , t.action_tierlist ,
2243 t.action_lang_code , t.concat_attribs, t.loader_data_file_id,
2244 sysdate, sysdate, 5, 5
2245 from
2246 (
2247 select distinct
2248 action_code, action_phase, major_phase, minor_phase,
2249 action_arguments, checkfile_args, checkobj , checkobj_un ,
2250 checkobj_pw, action_modifier, action_tierlist ,
2251 action_lang_code, concat_attribs, loader_data_file_id
2252 from
2253 AD_PATCH_HIST_TEMP )t
2254 where not exists (
2255 select
2256 'x'
2257 FROM
2258 AD_PATCH_COMMON_ACTIONS PCA
2259 WHERE
2260 PCA.CONCAT_ATTRIBS = t.CONCAT_ATTRIBS)
2261 and t.concat_attribs is not null;
2262 --
2266 set t.COMMON_ACTION_ID =
2263 -- Get the COMMON_ACTION_ID into the staging table
2264 --
2265 update AD_PATCH_HIST_TEMP t
2267 (select
2268 PCA.COMMON_ACTION_ID
2269 from
2270 AD_PATCH_COMMON_ACTIONS PCA
2271 WHERE
2272 PCA.CONCAT_ATTRIBS = t.concat_attribs )
2273 where t.concat_attribs is not null;
2274 --
2275 --
2276 commit;
2277 --
2278 -- Fix bug 2757813:
2279 -- remove any duplicate actions in same bug fix from temp table
2280 -- These will cause logic below to fail
2281 --
2282 -- Later we should set allow_duplicate_actions= FALSE; in adpdrv.lc
2283 -- so that we don't get any duplicate actions in the action list
2284 -- and also stop calling adptod().
2285 --
2286 begin
2287
2288 prb_id := -1;
2289 ca_id := -1;
2290 f_id := -1;
2291
2292 for c1 in del_cursor loop
2293
2294 -- dbms_output.put_line(c1.patch_run_bug_id||','||c1.common_action_id||
2295 -- ','||c1.file_id||','||c1.row_id);
2296 -- dbms_output.put_line(prb_id||','||ca_id||','||f_id);
2297
2298 if c1.patch_run_bug_id <> prb_id
2299 or c1.common_action_id <> ca_id
2300 or c1.file_id <> f_id then
2301
2302 prb_id := c1.patch_run_bug_id;
2303 ca_id := c1.common_action_id;
2304 f_id := c1.file_id;
2305
2306 statement := 'delete from ad_patch_hist_temp'||
2307 ' where patch_run_bug_id = '||c1.patch_run_bug_id||
2308 ' and common_action_id = '||c1.common_action_id||
2309 ' and file_id = '||c1.file_id||
2310 ' and rowid <> '''||c1.row_id||'''';
2311
2312 -- dbms_output.put_line(statement);
2313
2314 execute immediate statement;
2315 end if;
2316
2317 end loop;
2318 end;
2319 --
2320 -- Add new entries in the ad_patch_run_bug_actions
2321 --
2322 insert into AD_PATCH_RUN_BUG_ACTIONS
2323 (
2324 ACTION_ID,
2325 PATCH_RUN_BUG_ID,
2326 COMMON_ACTION_ID,
2327 FILE_ID,
2328 PATCH_FILE_VERSION_ID,
2329 ONSITE_FILE_VERSION_ID,
2330 ONSITE_PKG_VERSION_IN_DB_ID,
2331 EXECUTED_FLAG,
2332 DEST_FILE_ID, FILE_TYPE_FLAG,
2333 CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY
2334 )
2335 select
2336 AD_PATCH_RUN_BUG_ACTIONS_S.NEXTVAL,
2337 t.patch_run_bug_id,
2338 t.common_action_id,
2339 t.file_id,
2340 t.patch_file_vers_id,
2341 t.onsite_file_vers_id,
2342 t.db_file_vers_id,
2343 t.action_executed_flag,
2344 t.dest_file_id, t.file_type_flag,
2345 SYSDATE, SYSDATE, 5, 5 from AD_PATCH_HIST_TEMP t
2346 where not exists
2347 (select
2348 'x'
2349 from
2350 AD_PATCH_RUN_BUG_ACTIONS aprba
2351 where
2352 aprba.PATCH_RUN_BUG_ID = t.patch_run_bug_id
2353 and aprba.FILE_ID = t.file_id
2354 and aprba.COMMON_ACTION_ID = t.common_action_id)
2355 and t.common_action_id is not null and t.ldr_filename is null;
2356 --
2357 --
2358 l_actions_processed := sql%rowcount;
2359 actions_processed := l_actions_processed;
2360 --
2361 --
2362 commit;
2363 --
2364 -- Add new entries in the ad_patch_run_bug_actions with loader files.
2365 -- bug 3486202, cbhati
2366 --
2367 insert into AD_PATCH_RUN_BUG_ACTIONS
2368 (
2369 ACTION_ID,
2370 PATCH_RUN_BUG_ID,
2371 COMMON_ACTION_ID,
2372 FILE_ID,
2373 PATCH_FILE_VERSION_ID,
2374 ONSITE_FILE_VERSION_ID,
2375 ONSITE_PKG_VERSION_IN_DB_ID,
2376 EXECUTED_FLAG,
2377 DEST_FILE_ID, FILE_TYPE_FLAG,
2378 CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY
2379 )
2380 select
2381 AD_PATCH_RUN_BUG_ACTIONS_S.NEXTVAL,
2382 t.patch_run_bug_id,
2383 t.common_action_id,
2384 t.loader_data_file_id,
2385 t.patch_file_vers_id,
2386 t.onsite_file_vers_id,
2387 t.db_file_vers_id,
2388 t.action_executed_flag,
2389 t.dest_file_id, t.file_type_flag,
2390 SYSDATE, SYSDATE, 5, 5 from AD_PATCH_HIST_TEMP t
2391 where not exists
2392 (select
2393 'x'
2394 from
2395 AD_PATCH_RUN_BUG_ACTIONS aprba
2396 where
2397 aprba.PATCH_RUN_BUG_ID = t.patch_run_bug_id
2398 and aprba.FILE_ID = t.loader_data_file_id
2399 and aprba.COMMON_ACTION_ID = t.common_action_id)
2400 and t.common_action_id is not null and t.loader_data_file_id is not null;
2401 --
2402 --
2403 l_actions_processed := sql%rowcount;
2404 actions_processed := l_actions_processed;
2405 --
2406 --
2407 commit;
2408
2409 --
2410 --
2411 end load_patch_hist_action;
2412
2413
2414 -- Procedure
2415 -- create_global_view
2416 -- Arguments
2417 -- p_apps_system_name - Applications system name
2418 -- Purpose
2419 -- Procedure to create Global View snapshot using exisiting
2420 -- current view snapshots for an applications system.
2421 -- Notes
2422 -- Pre-requiste: ad_snapshot_files_temp sholud have been populated
2423 -- before calling this API.
2424
2425 procedure create_global_view(p_apps_system_name varchar2,
2426 p_is_run_flow boolean default true)
2427 is
2428 l_release_id number;
2429 l_snapshot_count number;
2430 l_global_snapshot_id number;
2431 l_appl_top_id number;
2432 l_appl_top_count number;
2433 l_global_snapshot_type varchar2(1);
2434 l_current_snapshot_type varchar2(1);
2435 begin
2436
2437 if (p_is_run_flow = TRUE)
2438 then
2439 l_global_snapshot_type := 'G';
2440 l_current_snapshot_type := 'C';
2441 else
2445
2442 l_global_snapshot_type := 'Q';
2443 l_current_snapshot_type := 'P';
2444 end if;
2446 /* Compute total number of current view snapshots available */
2447 select count(*) into l_snapshot_count
2448 from ad_snapshots s, ad_appl_tops t
2449 where s.snapshot_type = l_current_snapshot_type and
2450 s.snapshot_name = 'CURRENT_VIEW' and
2451 s.appl_top_id = t.appl_top_id and
2452 t.applications_system_name = p_apps_system_name;
2453
2454 /* Get the release id */
2455 select release_id into l_release_id from ad_releases
2456 where to_char(major_version) || '.' ||
2457 to_char(minor_version) || '.' ||
2458 to_char(tape_version) = (select release_name
2459 from fnd_product_groups
2460 where applications_system_name =
2461 p_apps_system_name);
2462
2463 /* Create a dummy Appl_top called 'GLOBAL' */
2464 insert into ad_appl_tops
2465 (
2466 appl_top_id, name, applications_system_name, appl_top_type,
2467 description,
2468 server_type_admin_flag,
2469 server_type_forms_flag,
2470 server_type_node_flag,
2471 server_type_web_flag,
2472 creation_date,
2473 created_by,
2474 last_update_date,
2475 last_updated_by,
2476 active_flag
2477 )
2478 select
2479 ad_appl_tops_s.nextval,
2480 'GLOBAL', /* APPL_TOP type is 'G' */
2481 p_apps_system_name,
2482 'G',
2483 'Created for Global View Snapshot',
2484 null,
2485 null,
2486 null,
2487 null,
2488 sysdate,
2489 5,
2490 sysdate,
2491 5,
2492 'N' /* ACTIVE_FLAG is set to 'N'. (Refer CONCURRENT_SESSIONS) */
2493 from dual where not exists(select 'Already exists'
2494 from ad_appl_tops t
2495 where t.name = 'GLOBAL' and
2496 t.appl_top_type = 'G' and
2497 t.applications_system_name = p_apps_system_name);
2498
2499 /* Get 'GLOBAL' APPL_TOP_ID */
2500 select appl_top_id into l_appl_top_id
2501 from ad_appl_tops
2502 where appl_top_type = 'G' and
2503 name = 'GLOBAL' and
2504 applications_system_name = p_apps_system_name;
2505
2506 insert into ad_snapshots
2507 (
2508 snapshot_id, release_id, appl_top_id, snapshot_name,
2509 snapshot_creation_date,
2510 snapshot_update_date,
2511 snapshot_type,
2512 comments,
2513 ran_snapshot_flag,
2514 creation_date,
2515 last_updated_by,
2516 created_by,
2517 last_update_date
2518 )
2519 select ad_snapshots_s.nextval, l_release_id,
2520 l_appl_top_id,
2521 'GLOBAL_VIEW',
2522 sysdate,
2523 sysdate,
2524 l_global_snapshot_type, /* snapshot type is 'G' */
2525 'Created from Current View Snapshots',
2526 'Y', /* Setting RAN_SNAPSHOT_FLAG to 'Y'. Because, it doesn't */
2527 sysdate, /* have any significance for GLOBAL_VIEW */
2528 5,
2529 5,
2530 sysdate
2531 from dual where not exists(select 'Already exists'
2532 from ad_snapshots s
2533 where s.appl_top_id = l_appl_top_id
2534 and s.snapshot_type = l_global_snapshot_type
2535 and s.snapshot_name = 'GLOBAL_VIEW');
2536
2537 /* Get Global snapshot ID for this Applications Sytem Name */
2538 select s.snapshot_id into l_global_snapshot_id
2539 from ad_snapshots s
2540 where s.snapshot_type = l_global_snapshot_type and
2541 s.snapshot_name = 'GLOBAL_VIEW' and
2542 s.appl_top_id = l_appl_top_id;
2543
2544 commit;
2545 exception
2546 when others then
2547 rollback;
2548 raise;
2549 end create_global_view;
2550 -- Procedure
2551 -- populate_snapshot_files_temp
2552 -- Arguments
2553 -- p_apps_system_name - Applications System Name
2554 --
2555 -- p_min_file_id - lower file_id in the range of file_ids
2556 --
2557 -- p_max_file_id - upper file_id in the range of file_ids
2558 --
2559 -- p_global_snapshot_id - Global snapshot_id
2560 --
2561 -- p_un_fnd - applsys username
2562 --
2563 -- p_iteration - which iteration (1,2,etc)
2564 -- Purpose
2565 -- This procedure populates temp table with a range of file_ids
2566 -- processes the data and updates the ad_snapshot_files with negative
2567 -- global snapshot_id
2568 -- Notes
2569
2570 procedure populate_snapshot_files_temp(p_applications_sys_name varchar2,p_min_file_id number,
2571 p_max_file_id number,p_global_snapshot_id number,
2572 p_un_fnd varchar2,p_iteration number,
2573 p_is_run_flow boolean default true)
2574 is
2575 v_global_snapshot_count number;
2576 v_global_snapshot_id number;
2577 v_global_appl_top_id number;
2578 v_current_snapshot_type varchar2(1);
2579 begin
2580 --
2581 --
2582 execute immediate 'truncate table '|| p_un_fnd ||'.ad_snapshot_files_temp';
2583 execute immediate 'truncate table '|| p_un_fnd ||'.ad_patch_hist_temp';
2584 --
2585 --
2586 if (p_is_run_flow = TRUE)
2587 then
2588 v_current_snapshot_type := 'C';
2589 else
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 --
2600 EFFECTIVE_DATE)
2597 insert into ad_check_file_temp
2598 (TRANSLATION_LEVEL,APP_SHORT_NAME,
2599 SUBDIR,FILENAME, MANIFEST_VERS,
2601 select
2602 snapshot_id, ' ',
2603 ' ',' ',' ',
2604 sysdate
2605 from
2606 ad_snapshots snap,
2607 ad_appl_tops atp,
2608 ad_releases ar,
2609 fnd_product_groups fpg
2610 where
2611 atp.appl_top_id=snap.appl_top_id and
2612 atp.appl_top_type='R' and
2613 atp.applications_system_name=p_applications_sys_name and
2614 nvl(atp.active_flag,'Y') = 'Y' and
2615 snap.snapshot_type = v_current_snapshot_type and
2616 snap.snapshot_name = 'CURRENT_VIEW' and
2617 snap.release_id = ar.release_id and
2618 fpg.release_name = ar.major_version||'.'||ar.minor_version||'.'||ar.tape_version and
2619 fpg.applications_system_name=atp.applications_system_name and
2620 fpg.product_group_id=1;
2621 --
2622 --
2623 end if;
2624 --
2625 --
2626 commit;
2627 --
2628 --
2629 v_global_snapshot_id:=(-1*p_global_snapshot_id);
2630 --
2631 --
2632 insert into ad_snapshot_files
2633 (
2634 snapshot_file_id,snapshot_id,
2635 file_id, file_version_id, containing_file_id,
2636 file_size, checksum, update_source_id, update_type,
2637 appl_top_id, inconsistent_flag, dest_file_id,file_type_flag,
2638 creation_date,last_update_date,last_updated_by,created_by
2639 )
2640 select
2641 ad_snapshot_files_s.nextval,v_global_snapshot_id,
2642 file_id,file_version_id,containing_file_id,
2643 file_size,checksum,update_source_id,'S',
2644 appl_top_id, 'N', dest_file_id,file_type_flag,
2645 sysdate,sysdate,5,5
2646 from
2647 (
2648 select
2649 file_id,
2650 max(file_version_id) file_version_id,
2651 max(containing_file_id) containing_file_id,
2652 max(file_size) file_size,
2653 max(checksum) checksum,
2654 max(snapshot_id) snapshot_id,
2655 max(dest_file_id) dest_file_id,
2656 max(appl_top_id) appl_top_id,
2657 decode(max(decode(update_type, 'P', 2, 1)), 2, 'P', 'S') update_type,
2658 decode(max(decode(file_type_flag, 'M', 2, 1)), 2, 'M', 'N') file_type_flag,
2659 replace(max(decode(update_type, 'P', 'a', null)||
2660 to_char(update_source_id)), 'a', null) update_source_id
2661 from
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
2671 );
2672 --
2673 --
2674 commit;
2675 --
2676 --
2677 insert into ad_patch_hist_temp
2678 (
2679 file_id, patch_file_vers_id , onsite_file_vers_id,
2680 bug_id, patch_run_bug_id ,
2681 db_file_vers_id, applied_flag,common_action_id,
2682 success_flag, major_phase,action_executed_flag,
2683 concat_attribs
2684 )
2685 select /*+ opt_param('_gby_hash_aggregation_enabled','false') */
2686 file_id,
2687 nvl(file_version_id,0),
2688 containing_file_id,
2689 file_size,
2690 checksum,
2691 update_source_id,
2692 update_type,
2693 appl_top_id ,
2694 'Y',
2695 dest_file_id,
2696 file_type_flag,
2697 '1234567890123456789012345678901234567890123456789012345678901234567890'
2698 from
2699 ad_snapshot_files
2700 where
2701 file_id in
2702 ( select
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
2712 ) and
2713 snapshot_id in (select TRANSLATION_LEVEL from ad_check_file_temp);
2714 --
2715 --
2716 commit;
2717 --
2718 --
2719 update ad_patch_hist_temp tmp set
2720 (tmp.PATCH_VERSION_SEGMENT1, tmp.PATCH_VERSION_SEGMENT2,
2721 tmp.PATCH_VERSION_SEGMENT3, tmp.PATCH_VERSION_SEGMENT4,
2722 tmp.PATCH_VERSION_SEGMENT5, tmp.PATCH_VERSION_SEGMENT6,
2723 tmp.PATCH_VERSION_SEGMENT7, tmp.PATCH_VERSION_SEGMENT8,
2724 tmp.PATCH_VERSION_SEGMENT9, tmp.PATCH_VERSION_SEGMENT10,
2725 tmp.PATCH_FILE_VERS, tmp.PATCH_TRANS_LEVEL) =
2726 (select
2727 v.VERSION_SEGMENT1, v.VERSION_SEGMENT2,
2728 v.VERSION_SEGMENT3, v.VERSION_SEGMENT4,
2729 v.VERSION_SEGMENT5, v.VERSION_SEGMENT6,
2730 v.VERSION_SEGMENT7, v.VERSION_SEGMENT8,
2731 v.VERSION_SEGMENT9, v.VERSION_SEGMENT10,
2732 v.VERSION, v.TRANSLATION_LEVEL
2733 from
2734 ad_file_versions v
2735 where
2736 v.file_version_id = tmp.PATCH_FILE_VERS_ID),
2737 tmp.concat_attribs=null;
2738 --
2739 --
2740 update ad_patch_hist_temp tmp set
2741 tmp.PATCH_VERSION_SEGMENT1=0, tmp.PATCH_VERSION_SEGMENT2=0,
2742 tmp.PATCH_VERSION_SEGMENT3=0, tmp.PATCH_VERSION_SEGMENT4=0,
2743 tmp.PATCH_VERSION_SEGMENT5=0, tmp.PATCH_VERSION_SEGMENT6=0,
2744 tmp.PATCH_VERSION_SEGMENT7=0, tmp.PATCH_VERSION_SEGMENT8=0,
2745 tmp.PATCH_VERSION_SEGMENT9=0, tmp.PATCH_VERSION_SEGMENT10=0,
2746 tmp.PATCH_FILE_VERS=null, tmp.PATCH_TRANS_LEVEL=null
2747 where tmp.PATCH_FILE_VERS_ID=0;
2748 --
2752 file_id, file_version_id, containing_file_id,
2749 execute immediate 'insert into ad_snapshot_files
2750 (
2751 snapshot_file_id,snapshot_id,
2753 file_size, checksum, update_source_id, update_type,
2754 appl_top_id, inconsistent_flag, dest_file_id,file_type_flag,
2755 creation_date,last_update_date,last_updated_by,
2756 created_by
2757 )
2758 select
2759 ad_snapshot_files_s.nextval,:v_global_snapshot_id,
2760 file_id, patch_file_vers_id , onsite_file_vers_id,
2761 bug_id, patch_run_bug_id,db_file_vers_id,applied_flag,
2762 common_action_id, ''Y'', major_phase,action_executed_flag,
2763 sysdate,sysdate,5,5
2764 from
2765 (
2766 select
2767 file_id, patch_file_vers_id , onsite_file_vers_id,
2768 bug_id, patch_run_bug_id ,
2769 db_file_vers_id, applied_flag,common_action_id,
2770 success_flag, major_phase ,action_executed_flag,row_number() over
2771 (
2772 PARTITION BY file_id order by
2773 PATCH_VERSION_SEGMENT1 desc, PATCH_VERSION_SEGMENT2 desc,
2774 PATCH_VERSION_SEGMENT3 desc, PATCH_VERSION_SEGMENT4 desc,
2775 PATCH_VERSION_SEGMENT5 desc, PATCH_VERSION_SEGMENT6 desc,
2776 PATCH_VERSION_SEGMENT7 desc, PATCH_VERSION_SEGMENT8 desc,
2777 PATCH_VERSION_SEGMENT9 desc, PATCH_VERSION_SEGMENT10 desc,
2778 PATCH_TRANS_LEVEL desc NULLS LAST
2779 ) rnk
2780 from
2781 ad_patch_hist_temp) where rnk=1' using v_global_snapshot_id;
2782 --
2783 --
2784 commit;
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 --
2794 end populate_snapshot_files_temp;
2795 --
2796 --
2797 -- Procedure
2798 -- populate_snapshot_bugs_temp
2799 -- Arguments
2800 -- p_apps_system_name - Applications System Name
2801 --
2802 -- p_min_bug_id - lower bugfix_id in the range of bugfix_id
2803 --
2804 -- p_max_bug_id - upper bugfix_id in the range of bugfix_id
2805 --
2806 -- p_global_snapshot_id - Global snapshot_id
2807 --
2808 -- p_un_fnd - applsys username
2809 --
2810 -- p_iteration - which iteration (1,2,etc)
2811 -- Purpose
2812 -- This procedure populates temp table with a range of bugfix_id
2813 -- processes the data and updates the ad_snapshot_bugfixes with negative
2814 -- global snapshot_id
2815 -- Notes
2816 --
2817 procedure populate_snapshot_bugs_temp(p_applications_sys_name varchar2,p_min_bug_id number,
2818 p_max_bug_id number,p_global_snapshot_id number,
2819 p_un_fnd varchar2,p_iteration number,
2820 p_is_run_flow boolean default true)
2821 is
2822 v_global_snapshot_id number;
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
2832 v_current_snapshot_type := 'C';
2833 else
2834 v_current_snapshot_type := 'P';
2835 end if;
2836
2837 if (p_iteration = 1) then
2838 --
2839 --
2840 execute immediate 'truncate table '||p_un_fnd||'.ad_patch_hist_temp';
2841 --
2842 --
2843 insert into ad_patch_hist_temp
2844 (patch_run_id)
2845 select
2846 snapshot_id
2847 from
2848 ad_snapshots snap,
2849 ad_appl_tops atp,
2850 ad_releases ar,
2851 fnd_product_groups fpg
2852 where
2853 atp.appl_top_id=snap.appl_top_id and
2854 atp.appl_top_type='R' and
2855 atp.applications_system_name=p_applications_sys_name and
2856 nvl(atp.active_flag,'Y') = 'Y' and
2857 snap.snapshot_type = v_current_snapshot_type and
2858 snap.snapshot_name = 'CURRENT_VIEW' and
2859 snap.release_id = ar.release_id and
2860 fpg.release_name = ar.major_version||'.'||ar.minor_version||'.'||ar.tape_version and
2861 fpg.applications_system_name=atp.applications_system_name and
2862 fpg.product_group_id=1;
2863 --
2864 --
2865 end if;
2866 v_global_snapshot_id:=(-1 *p_global_snapshot_id);
2867 --
2868 --
2869 insert into ad_snapshot_bugfixes
2870 (
2871 SNAPSHOT_BUG_ID,
2872 snapshot_id, bugfix_id,
2873 inconsistent_flag,
2874 bug_status, success_flag,
2875 creation_date,last_update_date,last_updated_by,
2876 created_by
2877 )
2878 select
2879 ad_snapshot_bugfixes_s.nextval,v_global_snapshot_id,
2880 bugfix_id,
2881 'N',
2882 bug_status,success_flag,
2883 sysdate,sysdate,5,5
2884 from
2885 (
2886 select
2887 bugfix_id,
2888 decode(max(decode(success_flag, 'Y', 2, 1)),
2889 2, 'Y', 'N') success_flag,
2890 decode(max(decode(bug_status, 'EXPLICIT', 2, 1)),
2891 2, 'EXPLICIT', 'IMPLICIT') bug_status
2892 from
2893 ad_snapshot_bugfixes
2894 where
2895 bugfix_id >= p_min_bug_id and
2896 bugfix_id < p_max_bug_id and
2897 snapshot_id in (select patch_run_id from ad_patch_hist_temp)
2898 group by
2899 bugfix_id
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,
2910 bugfix_id,
2907 check_file_id,subdir,filename,
2908 manifest_vers,translation_level,effective_date)
2909 select
2911 bug_status, success_flag,
2912 (decode(success_flag,'Y',1,2) * 3 +
2913 decode(bug_status,'EXPLICIT',1,'IMPLICIT',2,3)) bug_rank ,
2914 'NA','NA','NA',0,sysdate
2915 from
2916 (
2917 select
2918 bugfix_id,
2919 decode(max(decode(success_flag, 'Y', 2, 1)),
2920 2, 'Y', 'N') success_flag,
2921 decode(max(decode(bug_status, 'EXPLICIT', 2, 1)),
2922 2, 'EXPLICIT', 'IMPLICIT') bug_status
2923 from
2924 ad_snapshot_bugfixes
2925 where
2926 bugfix_id >= p_min_bug_id and
2927 bugfix_id < p_max_bug_id and
2928 snapshot_id in (select patch_run_id from ad_patch_hist_temp)
2929 group by bugfix_id
2930 having count(distinct decode(success_flag, 'Y', 2, 1)) >1);
2931 --
2932 --
2933 execute immediate 'insert into ad_snapshot_bugfixes
2934 (
2935 SNAPSHOT_BUG_ID,
2936 snapshot_id, bugfix_id,
2937 inconsistent_flag,
2938 bug_status, success_flag,
2939 creation_date,last_update_date,last_updated_by,
2940 created_by
2941 )
2942 select
2943 ad_snapshot_bugfixes_s.nextval,:snp_id,
2944 file_version_id,
2945 ''Y'',
2946 app_short_name , active_flag,
2947 sysdate,sysdate,5,5
2948 from
2949 (
2950 select
2951 file_version_id,
2952 app_short_name , active_flag,rnk
2953 from
2954 (
2955 select
2956 file_version_id,
2957 app_short_name , active_flag,
2958 ROW_NUMBER() over
2959 (
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 --
2969 commit;
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 --
2979 end populate_snapshot_bugs_temp;
2980 --
2981 --
2982 --
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
2992 --
2993 procedure load_prepmode_checkfile_info is
2994 begin
2995
2996 --
2997 -- first update versions for existing rows
2998 -- (assume that the versions in temporary table are higher)
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)
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
3015 --
3016 insert into ad_prepmode_check_files cf
3017 (
3018 app_short_name, subdir, filename, distinguisher,
3019 version
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
3029 where cf2.app_short_name = t.app_short_name
3030 and cf2.subdir = t.subdir
3031 and cf2.filename = t.filename
3032 and cf2.distinguisher = nvl(t.distinguisher, '~'));
3033
3034
3035 end load_prepmode_checkfile_info;
3036
3037 --
3038 -- Procedure
3039 -- cln_prepmode_checkfile_info
3040 --
3041 -- Purpose
3042 -- deletes rows from ad_premode_check_files (called after the merge)
3043 --
3044 -- Arguments
3045 -- none
3046 --
3047 procedure cln_prepmode_checkfile_info
3048 is
3049 begin
3050 delete from ad_prepmode_check_files;
3051 end cln_prepmode_checkfile_info;
3052
3053 --
3054 -- Bug 4488796 - rahkumar
3055 -- Procedure
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 --
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;
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;
3081 --
3078 forms_server_flag_list t_forms_server_flag;
3079 node_server_flag_list t_node_server_flag;
3080 web_server_flag_list t_web_server_flag;
3082 --
3083 --
3084 CURSOR c1 IS
3085 SELECT
3086 file_id,check_file_id,
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 --
3096 cur_rec c1%ROWTYPE;
3097 rows NATURAL := 2000;
3098 --
3099 --
3100 begin
3101
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
3111 when others then
3112 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
3113 raise;
3114 end;
3115 --
3116 --
3117 LOOP
3118 --
3119 --
3120 --
3121 FETCH c1 BULK COLLECT INTO
3122 fl_id_list ,con_file_id_list,
3123 admin_server_flag_list, forms_server_flag_list,
3124 node_server_flag_list, web_server_flag_list
3125 LIMIT rows;
3126 --
3127 --
3128 if fl_id_list.COUNT > 0 then
3129 --
3130 --
3131 --
3132 FORALL j IN fl_id_list.FIRST.. fl_id_list.LAST
3133 update
3134 ad_snapshot_files snap
3135 set
3136 snap.server_type_admin_flag = admin_server_flag_list(j),
3137 snap.server_type_forms_flag = forms_server_flag_list(j),
3138 snap.server_type_node_flag = node_server_flag_list(j),
3139 snap.server_type_web_flag = web_server_flag_list(j)
3140 where
3141 snap.snapshot_id=snp_id and
3142 snap.file_id =fl_id_list(j) and
3143 nvl(snap.containing_file_id,-1)=nvl(con_file_id_list(j),-1);
3144 --
3145 --
3146 end if;
3147 --
3148 --
3149
3150 EXIT WHEN c1%NOTFOUND;
3151 --
3152 --
3153 --
3154 END LOOP;
3155 --
3156 --
3157 --
3158 begin
3159 close c1;
3160 exception
3161 when others then
3162 ad_file_util.error_buf := 'load_snpst_file_server_info(Close cursor):('||
3163 sqlerrm||')';
3164 end;
3165
3166 --
3167 --
3168 --
3169 end load_snpst_file_server_info;
3170 --
3171 --
3172
3173 end ad_file_util;