[Home] [Help]
PACKAGE BODY: APPS.AD_FILE_UTIL
Source
1 package body ad_file_util as
2 /* $Header: adfilutb.pls 120.15 2007/12/14 13:07:57 diverma 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
318
315 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
316 raise;
317 end;
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 '||
418 'and fv.version = t.manifest_vers '||
419 'and fv.translation_level = '||
420 't.translation_level) '||
421 'where nvl(t.active_flag,''N'') = ''Y'' '||
422 'and lower(t.manifest_vers)<>''none'';):(';
423 begin
424 update ad_check_file_temp t
425 set t.file_version_id =
426 (select /*+ INDEX(FV AD_FILE_VERSIONS_U2) */ fv.file_version_id
427 from ad_file_versions fv
428 where fv.file_id = t.file_id
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 '||
456 'temp.vs5, temp.vs6, temp.vs7, temp.vs8, '||
453 'ad_file_versions_s.nextval, '||
454 'temp.f_id, temp.vers, temp.trans_level, '||
455 'temp.vs1, temp.vs2, temp.vs3, temp.vs4, '||
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))
528 - (instr(t.manifest_vers||'.','.',1,9)+1)) ))) vs10,
529 t.effective_date edate
530 from ad_check_file_temp t
531 where t.file_version_id is null
532 and lower(t.manifest_vers) <> 'none'
533 and nvl(t.active_flag,'N') = 'Y'
534 ) temp;
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'';):(';
559 update ad_check_file_temp t
556
557
558 begin
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
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)),
671 -1, null, 1, 'Y', decode(
672 sign(nvl(fv1.version_segment5,0) - nvl(fv2.version_segment5,0)),
673 -1, null, 1, 'Y', decode(
674 sign(nvl(fv1.version_segment6,0) - nvl(fv2.version_segment6,0)),
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(
687 where t.file_version_id = fv1.file_version_id
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
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 exception
730 when others then
731 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
732 raise;
733 end;
734 --
735 --
736 -- delete from ad_check_files where versions lower than manifest
737 --
738 ad_file_util.error_buf := 'load_checkfile_info('||
739 'delete from ad_check_files kf '||
740 'where cf.check_file_id in '||
741 '(select t.check_file_id '||
742 'from ad_check_file_temp t '||
743 'where t.manifest_vers_higher = ''Y'' '||
744 'and nvl(t.active_flag,''N'') = ''Y'');):(';
745
746 begin
747 delete /*+ INDEX(CF AD_CHECK_FILES_U1) */ from ad_check_files cf
748 where cf.check_file_id in
749 (select t.check_file_id
750 from ad_check_file_temp t
751 where t.manifest_vers_higher = 'Y'
752 and nvl(t.active_flag,'N') = 'Y');
753 exception
754 when others then
755 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
756 raise;
757 end;
758 --
759 -- insert into ad_check_files where versions lower than manifest
760 --
761 ad_file_util.error_buf := 'load_checkfile_info('||
762 ' insert into ad_check_files '||
763 '(check_file_id, file_id, distinguisher, '||
764 'file_version_id, creation_date) '||
765 'select temp.cf_id, '||
766 'temp.f_id, temp.dist, temp.fv_id, temp.edate '||
767 'from (select distinct '||
768 't.check_file_id cf_id, '||
769 't.file_id f_id, '||
770 't.distinguisher dist, '||
771 't.file_version_id fv_id, '||
772 't.effective_date edate '||
773 'from ad_check_file_temp t '||
774 'where t.manifest_vers_higher = ''Y'' '||
775 'and nvl(t.active_flag,''N'') = ''Y'') temp;):(';
776
777 begin
778 insert into ad_check_files
779 (check_file_id, file_id, distinguisher,
780 file_version_id, creation_date)
781 select temp.cf_id,
782 temp.f_id, temp.dist, temp.fv_id, temp.edate
783 from
784 (select distinct
785 t.check_file_id cf_id,
786 t.file_id f_id,
787 t.distinguisher dist,
788 t.file_version_id fv_id,
789 t.effective_date edate
790 from ad_check_file_temp t
791 where t.manifest_vers_higher = 'Y'
792 and nvl(t.active_flag,'N') = 'Y') temp;
793 exception
794 when others then
795 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
796 raise;
797 end;
798
799 --
800 -- done processing ad_check_files
801 --
802
803 end load_checkfile_info;
804
805 --
806 -- Procedure
807 -- update_timestamp
808 --
809 -- Purpose
813 -- Arguments
810 -- Inserts/updates a row in AD_TIMESTAMPS corresponding to the
811 -- specified row type and attribute.
812 --
814 -- in_type The row type
815 -- in_attribute The row attribute
816 -- in_timestamp A timestamp. Defaults to sysdate.
817 --
818 -- Notes
819 -- This is essentially the same as ad_invoker.update_timestamp
820 -- Added it here to make it easier to call from APPS.
821 --
822 procedure update_timestamp
823 (in_type in varchar2,
824 in_attribute in varchar2,
825 in_timestamp in date)
826 --
827 -- Updates/Inserts the row in AD_TIMESTAMPS for the specified
828 -- type and attribute
829 --
830 is
831 begin
832 --
833 -- First try to update
834 --
835 ad_file_util.error_buf := 'update_timestamp(update ad_timestamps '||
836 'set timestamp = '||in_timestamp||
837 'where type = '||in_type||
838 'and attribute = '||in_attribute||'):(';
839 begin
840 update ad_timestamps
841 set timestamp = in_timestamp
842 where type = in_type
843 and attribute = in_attribute;
844 exception
845 when others then
846 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
847 raise;
848 end;
849
850 if SQL%ROWCOUNT = 1 then
851 return;
852 end if;
853 --
854 -- Insert if no rows updated
855 --
856 ad_file_util.error_buf := 'update_timestamp('||
857 'insert into ad_timestamps'||
858 '(type, attribute, timestamp)'||
859 'values ('||in_type||', '||in_attribute||
860 ', '||in_timestamp||'):(';
861
862 begin
863 insert into ad_timestamps
864 (type, attribute, timestamp)
865 values (in_type, in_attribute, in_timestamp);
866 exception
867 when others then
868 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
869 raise;
870 end;
871
872 end update_timestamp;
873 --
874 --
875 procedure update_timestamp
876 (in_type in varchar2,
877 in_attribute in varchar2)
878 is
879 begin
880 update_timestamp
881 (in_type => in_type,
882 in_attribute => in_attribute,
883 in_timestamp => sysdate);
884 end;
885
886 --
887 --
888 --
889 -- Procedure
890 -- load_patch_onsite_vers_info
891 --
892 -- Purpose
893 -- Imports file information from ad_check_file_temp to ad_files and
894 -- ad_file_versions.
895 --
896 -- Only creates rows that don't already exist.
897 --
898 -- Processes all rows in ad_check_file_temp with active_flag='Y'.
899 --
900 -- To handle batch sizes:
901 --
902 -- 1) - fill up whole table with null active_flag
903 -- - In a loop:
904 -- - update a batch to have active_flag='Y'
905 -- - process the batch
906 -- - delete the batch
907 -- - using 'where rownum < batch+1' is handy here
908 --
909 -- 2) perform (truncate, load, process) cycles in an outer loop where
910 -- only <batch size> rows are loaded and processed at a time.
911 --
912 -- Calls load_file_info
913 --
914 -- Updates the file_version_id and file_version_id_2 columns of
915 -- ad_check_file_temp so that all rows point to the file_version_id
916 -- of the file versions referenced in the row.
917 --
918 -- Doesn't try to update ad_file_versions for rows in ad_check_file_temp
919 -- with manifest_vers='NA' or manifest_vers_2='NA'. These values mean
920 -- "no version for this file", so no corresponding record should be
921 -- created in ad_file_versions.
922 --
923 -- Arguments
924 -- none
925 --
926 procedure load_patch_onsite_vers_info
927 is
928 begin
929 --
930 -- process ad_files
931 --
932
933 ad_file_util.load_file_info;
934
935 --
936 -- process ad_file_versons
937 --
938 ad_file_util.error_buf := 'load_patch_onsite_vers_info('||
939 'update ad_check_file_temp t '||
940 'set t.file_version_id = '||
941 '(select fv.file_version_id '||
942 'from ad_file_versions fv '||
943 'where fv.file_id = t.file_id '||
944 'and fv.version = t.manifest_vers '||
945 'and fv.translation_level = t.translation_level) '||
946 'where nvl(t.active_flag,''N'') = ''Y'' '||
947 'and nvl(t.manifest_vers,''NA'')<>''NA''):(';
948
949 begin
950 update ad_check_file_temp t
951 set t.file_version_id =
952 (select /*+ INDEX(FV AD_FILE_VERSIONS_U2) */ fv.file_version_id
953 from ad_file_versions fv
954 where fv.file_id = t.file_id
955 and fv.version = t.manifest_vers
956 and fv.translation_level = t.translation_level)
957 where nvl(t.active_flag,'N') = 'Y'
958 and nvl(t.manifest_vers,'NA')<>'NA';
962 raise;
959 exception
960 when others then
961 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
963 end;
964 --
965 -- add new entries to ad_file_versions
966 --
967 ad_file_util.error_buf := 'load_patch_onsite_vers_info('||
968 'insert into ad_file_versions '||
969 '(file_version_id, file_id, version, '||
970 'translation_level, version_segment1,'||
971 'version_segment2, version_segment3, '||
972 'version_segment4, version_segment5, '||
973 'version_segment6, version_segment7, '||
974 'version_segment8, version_segment9, '||
975 'version_segment10, creation_date, created_by, '||
976 'last_update_date, last_updated_by) '||
977 '.....):(';
978 begin
979 insert into ad_file_versions
980 (file_version_id, file_id, version, translation_level,
981 version_segment1, version_segment2, version_segment3,
982 version_segment4, version_segment5, version_segment6,
983 version_segment7, version_segment8, version_segment9,
984 version_segment10,
985 creation_date, created_by, last_update_date, last_updated_by)
986 select ad_file_versions_s.nextval,
987 temp.f_id, temp.vers, temp.trans_level,
988 temp.vs1, temp.vs2, temp.vs3, temp.vs4, temp.vs5,
989 temp.vs6, temp.vs7, temp.vs8, temp.vs9, temp.vs10,
990 temp.edate, 5, temp.edate, 5
991 from
992 (select distinct
993 t.file_id f_id,
994 t.manifest_vers vers,
995 t.translation_level trans_level,
996 decode( instr(t.manifest_vers||'.','.',1,1), 0, null,
997 to_number(substr(t.manifest_vers||'.',
998 1,
999 ( instr(t.manifest_vers||'.','.',1,1)-1)))) vs1,
1000 decode( instr(t.manifest_vers||'.','.',1,2), 0, 0,
1001 to_number(substr(t.manifest_vers||'.',
1002 (instr(t.manifest_vers||'.','.',1,1)+1),
1003 ( (instr(t.manifest_vers||'.','.',1,2))
1004 - (instr(t.manifest_vers||'.','.',1,1)+1)) ))) vs2,
1005 decode( instr(t.manifest_vers||'.','.',1,3), 0, 0,
1006 to_number(substr(t.manifest_vers||'.',
1007 (instr(t.manifest_vers||'.','.',1,2)+1),
1008 ( (instr(t.manifest_vers||'.','.',1,3))
1009 - (instr(t.manifest_vers||'.','.',1,2)+1)) ))) vs3,
1010 decode( instr(t.manifest_vers||'.','.',1,4), 0, 0,
1011 to_number(substr(t.manifest_vers||'.',
1012 (instr(t.manifest_vers||'.','.',1,3)+1),
1013 ( (instr(t.manifest_vers||'.','.',1,4))
1014 - (instr(t.manifest_vers||'.','.',1,3)+1)) ))) vs4,
1015 decode( instr(t.manifest_vers||'.','.',1,5), 0, 0,
1016 to_number(substr(t.manifest_vers||'.',
1017 (instr(t.manifest_vers||'.','.',1,4)+1),
1018 ( (instr(t.manifest_vers||'.','.',1,5))
1019 - (instr(t.manifest_vers||'.','.',1,4)+1)) ))) vs5,
1020 decode( instr(t.manifest_vers||'.','.',1,6), 0, 0,
1021 to_number(substr(t.manifest_vers||'.',
1022 (instr(t.manifest_vers||'.','.',1,5)+1),
1023 ( (instr(t.manifest_vers||'.','.',1,6))
1024 - (instr(t.manifest_vers||'.','.',1,5)+1)) ))) vs6,
1025 decode( instr(t.manifest_vers||'.','.',1,7), 0, 0,
1026 to_number(substr(t.manifest_vers||'.',
1027 (instr(t.manifest_vers||'.','.',1,6)+1),
1028 ( (instr(t.manifest_vers||'.','.',1,7))
1029 - (instr(t.manifest_vers||'.','.',1,6)+1)) ))) vs7,
1030 decode( instr(t.manifest_vers||'.','.',1,8), 0, 0,
1031 to_number(substr(t.manifest_vers||'.',
1032 (instr(t.manifest_vers||'.','.',1,7)+1),
1033 ( (instr(t.manifest_vers||'.','.',1,8))
1034 - (instr(t.manifest_vers||'.','.',1,7)+1)) ))) vs8,
1035 decode( instr(t.manifest_vers||'.','.',1,9), 0, 0,
1036 to_number(substr(t.manifest_vers||'.',
1037 (instr(t.manifest_vers||'.','.',1,8)+1),
1038 ( (instr(t.manifest_vers||'.','.',1,9))
1039 - (instr(t.manifest_vers||'.','.',1,8)+1)) ))) vs9,
1040 decode( instr(t.manifest_vers||'.','.',1,10), 0, 0,
1041 to_number(substr(t.manifest_vers||'.',
1042 (instr(t.manifest_vers||'.','.',1,9)+1),
1043 ( (instr(t.manifest_vers||'.','.',1,10))
1044 - (instr(t.manifest_vers||'.','.',1,9)+1)) ))) vs10,
1045 t.effective_date edate
1046 from ad_check_file_temp t
1047 where t.file_version_id is null
1048 and nvl(t.active_flag,'N') = 'Y'
1049 and nvl(t.manifest_vers,'NA')<>'NA') temp;
1050 exception
1051 when others then
1052 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
1053 raise;
1054 end;
1055 --
1056 --
1057 ad_file_util.error_buf := 'load_patch_onsite_vers_info('||
1058 'update ad_check_file_temp t '||
1059 'set t.file_version_id = '||
1060 '(select fv.file_version_id '||
1061 'from ad_file_versions fv '||
1062 'where fv.file_id = t.file_id '||
1066 'and nvl(t.manifest_vers,''NA'')<>''NA''):(';
1063 'and fv.version = t.manifest_vers '||
1064 'and fv.translation_level = t.translation_level) '||
1065 'where nvl(t.active_flag,''N'') = ''Y'' '||
1067 --
1068 --
1069 begin
1070 update ad_check_file_temp t
1071 set t.file_version_id =
1072 (select /*+ INDEX(FV AD_FILE_VERSIONS_U2) */ fv.file_version_id
1073 from ad_file_versions fv
1074 where fv.file_id = t.file_id
1075 and fv.version = t.manifest_vers
1076 and fv.translation_level = t.translation_level)
1077 where nvl(t.active_flag,'N') = 'Y'
1078 and nvl(t.manifest_vers,'NA')<>'NA';
1079 exception
1080 when others then
1081 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
1082 raise;
1083 end;
1084 --
1085 -- get file_version_id_2 from ad_file_versions
1086 --
1087 ad_file_util.error_buf := 'load_patch_onsite_vers_info('||
1088 'update ad_check_file_temp t '||
1089 'set t.file_version_id_2 = '||
1090 '(select fv.file_version_id '||
1091 'from ad_file_versions fv '||
1092 'where fv.file_id = t.file_id '||
1093 'and fv.version = t.manifest_vers_2 '||
1094 'nvl(t.manifest_vers_2,''NA'')<>''NA''):(';
1095 --
1096 --
1097 begin
1098 update ad_check_file_temp t
1099 set t.file_version_id_2 =
1100 (select /*+ INDEX(FV AD_FILE_VERSIONS_U2) */ fv.file_version_id
1101 from ad_file_versions fv
1102 where fv.file_id = t.file_id
1103 and fv.version = t.manifest_vers_2
1104 and fv.translation_level = t.translation_level)
1105 where nvl(t.active_flag,'N') = 'Y' AND
1106 nvl(t.manifest_vers_2,'NA')<>'NA';
1107 exception
1108 when others then
1109 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
1110 raise;
1111 end;
1112
1113 --
1114 -- add new entries to ad_file_versions
1115 --
1116 ad_file_util.error_buf := 'load_patch_onsite_vers_info('||
1117 'insert into ad_file_versions '||
1118 '(file_version_id, file_id, version, '||
1119 'translation_level, version_segment1,'||
1120 'version_segment2, version_segment3, '||
1121 'version_segment4, version_segment5, '||
1122 'version_segment6, version_segment7, '||
1123 'version_segment8, version_segment9, '||
1124 'version_segment10, creation_date, created_by, '||
1125 'last_update_date, last_updated_by) '||
1126 '.....):(';
1127 --
1128 begin
1129 insert into ad_file_versions
1130 (file_version_id, file_id, version, translation_level,
1131 version_segment1, version_segment2, version_segment3,
1132 version_segment4, version_segment5, version_segment6,
1133 version_segment7, version_segment8, version_segment9,
1134 version_segment10,
1135 creation_date, created_by, last_update_date, last_updated_by)
1136 select ad_file_versions_s.nextval,
1137 temp.f_id, temp.vers, temp.trans_level,
1138 temp.vs1, temp.vs2, temp.vs3, temp.vs4, temp.vs5,
1139 temp.vs6, temp.vs7, temp.vs8, temp.vs9, temp.vs10,
1140 temp.edate, 5, temp.edate, 5
1141 from
1142 (select distinct
1143 t.file_id f_id,
1144 t.manifest_vers_2 vers,
1145 t.translation_level trans_level,
1146 decode( instr(t.manifest_vers_2||'.','.',1,1), 0, null,
1147 to_number(substr(t.manifest_vers_2||'.',
1148 1,
1149 ( instr(t.manifest_vers_2||'.','.',1,1)-1)))) vs1,
1150 decode( instr(t.manifest_vers_2||'.','.',1,2), 0, 0,
1151 to_number(substr(t.manifest_vers_2||'.',
1152 (instr(t.manifest_vers_2||'.','.',1,1)+1),
1153 ( (instr(t.manifest_vers_2||'.','.',1,2))
1154 - (instr(t.manifest_vers_2||'.','.',1,1)+1)) ))) vs2,
1155 decode( instr(t.manifest_vers_2||'.','.',1,3), 0, 0,
1156 to_number(substr(t.manifest_vers_2||'.',
1157 (instr(t.manifest_vers_2||'.','.',1,2)+1),
1158 ( (instr(t.manifest_vers_2||'.','.',1,3))
1159 - (instr(t.manifest_vers_2||'.','.',1,2)+1)) ))) vs3,
1160 decode( instr(t.manifest_vers_2||'.','.',1,4), 0, 0,
1161 to_number(substr(t.manifest_vers_2||'.',
1162 (instr(t.manifest_vers_2||'.','.',1,3)+1),
1163 ( (instr(t.manifest_vers_2||'.','.',1,4))
1164 - (instr(t.manifest_vers_2||'.','.',1,3)+1)) ))) vs4,
1165 decode( instr(t.manifest_vers_2||'.','.',1,5), 0, 0,
1166 to_number(substr(t.manifest_vers_2||'.',
1167 (instr(t.manifest_vers_2||'.','.',1,4)+1),
1168 ( (instr(t.manifest_vers_2||'.','.',1,5))
1169 - (instr(t.manifest_vers_2||'.','.',1,4)+1)) ))) vs5,
1170 decode( instr(t.manifest_vers_2||'.','.',1,6), 0, 0,
1171 to_number(substr(t.manifest_vers_2||'.',
1172 (instr(t.manifest_vers_2||'.','.',1,5)+1),
1173 ( (instr(t.manifest_vers_2||'.','.',1,6))
1177 (instr(t.manifest_vers_2||'.','.',1,6)+1),
1174 - (instr(t.manifest_vers_2||'.','.',1,5)+1)) ))) vs6,
1175 decode( instr(t.manifest_vers_2||'.','.',1,7), 0, 0,
1176 to_number(substr(t.manifest_vers_2||'.',
1178 ( (instr(t.manifest_vers_2||'.','.',1,7))
1179 - (instr(t.manifest_vers_2||'.','.',1,6)+1)) ))) vs7,
1180 decode( instr(t.manifest_vers_2||'.','.',1,8), 0, 0,
1181 to_number(substr(t.manifest_vers_2||'.',
1182 (instr(t.manifest_vers_2||'.','.',1,7)+1),
1183 ( (instr(t.manifest_vers_2||'.','.',1,8))
1184 - (instr(t.manifest_vers_2||'.','.',1,7)+1)) ))) vs8,
1185 decode( instr(t.manifest_vers_2||'.','.',1,9), 0, 0,
1186 to_number(substr(t.manifest_vers_2||'.',
1187 (instr(t.manifest_vers_2||'.','.',1,8)+1),
1188 ( (instr(t.manifest_vers_2||'.','.',1,9))
1189 - (instr(t.manifest_vers_2||'.','.',1,8)+1)) ))) vs9,
1190 decode( instr(t.manifest_vers_2||'.','.',1,10), 0, 0,
1191 to_number(substr(t.manifest_vers_2||'.',
1192 (instr(t.manifest_vers_2||'.','.',1,9)+1),
1193 ( (instr(t.manifest_vers_2||'.','.',1,10))
1194 - (instr(t.manifest_vers_2||'.','.',1,9)+1)) ))) vs10,
1195 t.effective_date edate
1196 from ad_check_file_temp t
1197 where t.file_version_id_2 is null
1198 and nvl(t.active_flag,'N') = 'Y'
1199 and nvl(t.manifest_vers_2,'NA')<>'NA') temp;
1200 exception
1201 when others then
1202 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
1203 raise;
1204 end;
1205 --
1206 -- get file_version_id_2 from ad_file_versions
1207 --
1208 ad_file_util.error_buf := 'load_patch_onsite_vers_info('||
1209 'update ad_check_file_temp t '||
1210 'set t.file_version_id_2 = '||
1211 '(select fv.file_version_id '||
1212 'from ad_file_versions fv '||
1213 'where fv.file_id = t.file_id '||
1214 'and fv.version = t.manifest_vers_2 '||
1215 'nvl(t.manifest_vers_2,''NA'')<>''NA''):(';
1216 begin
1217 update ad_check_file_temp t
1218 set t.file_version_id_2 =
1219 (select /*+ INDEX(FV AD_FILE_VERSIONS_U2) */ fv.file_version_id
1220 from ad_file_versions fv
1221 where fv.file_id = t.file_id
1222 and fv.version = t.manifest_vers_2
1223 and fv.translation_level = t.translation_level)
1224 where nvl(t.active_flag,'N') = 'Y' AND
1225 nvl(t.manifest_vers_2,'NA')<>'NA';
1226 exception
1227 when others then
1228 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
1229 raise;
1230 end;
1231 --
1232 -- done processing ad_file_versions
1233 --
1234 end load_patch_onsite_vers_info;
1235
1236 --
1237 --
1238 --
1239 -- Procedure
1240 -- load_snapshot_file_info
1241 --
1242 -- Purpose
1243 -- Create Snapshot data by
1244 -- 1.Calls ad_file_versions and loads the file versions
1245 -- into the ad_check_file_temp table .
1246 -- 2.Updates rows in AD_SNAPSHOT_FILES from ad_check_file_temp
1247 -- which have the same file_id, snapshot_id and containing_file_id
1248 -- 3.Inserts those rows from ad_check_file_temp into AD_SNAPSHOT_FILES
1249 -- which exists in ad_check_file_temp but are not in AD_SNAPSHOT_FILES.
1250 -- for the given snapshot_id
1251 -- 4.Delete those rows from AD_SNAPSHOT_FILES which exists
1252 -- in AD_SNAPSHOT_FILES but do not exist in ad_check_file_temp
1253 -- for the given snapshot_id
1254 --
1255 -- Arguments
1256 -- is_upload pass TRUE if it is an upload otherwise FALSE
1257 --
1258 --
1259 procedure load_snapshot_file_info
1260 (snp_id number,
1261 preserve_irep_flag number)
1262 is
1263 TYPE t_version_id IS TABLE OF ad_check_file_temp.file_version_id%TYPE;
1264 TYPE t_check_sum IS TABLE OF ad_check_file_temp.check_sum%TYPE;
1265 TYPE t_file_size IS TABLE OF ad_check_file_temp.file_size%TYPE;
1266 TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
1267 TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
1268 TYPE t_dest_file_id IS TABLE OF ad_check_file_temp.dest_file_id%TYPE;
1269 TYPE t_file_type_flag IS TABLE OF ad_check_file_temp.file_type_flag%TYPE;
1270 TYPE t_irep_gathered_flag IS TABLE OF ad_check_file_temp.manifest_vers_higher%TYPE;
1271 TYPE t_effective_date IS TABLE OF ad_check_file_temp.effective_date%TYPE;
1272
1273 --
1274 vers_id_list t_version_id;
1275 chk_sum_list t_check_sum;
1276 fl_size_list t_file_size;
1277 fl_id_list t_file_id;
1278 con_file_id_list t_containing_file_id;
1279 dest_file_id_list t_dest_file_id;
1280 file_type_flag_list t_file_type_flag;
1281 irep_gathered_flag_list t_irep_gathered_flag;
1282 effective_date_list t_effective_date;
1283
1284 --
1285 --
1286 --
1287 cursor c1 is
1288 select
1289 file_version_id,check_sum,file_size,
1290 file_id,check_file_id,dest_file_id,
1291 file_type_flag, manifest_vers_higher,
1292 effective_date
1293 from ad_check_file_temp;
1294 --
1295 --
1296 --
1297 --
1298 cur_rec c1%ROWTYPE;
1299 rows NATURAL := 2000;
1300 --
1301 --
1302 --
1303 begin
1304 --
1305 -- process ad_files and ad_file_versions
1306 --
1307 begin
1311 ad_file_util.error_buf := 'load_snapshot_file_info('||snp_id||'):('||
1308 ad_file_util.load_file_version_info;
1309 exception
1310 when others then
1312 ad_file_util.error_buf||sqlerrm||')';
1313 raise;
1314 end;
1315 --
1316 -- get contain_file_id from ad_files
1317 --
1318 -- The containing files are already inserted into ad_files
1319 -- by the procedure load_file_version_info so we are
1320 -- guaranteed to get the file_id from ad_files.
1321 --
1322 -- done processing ad_files
1323 --
1324 --
1325 ad_file_util.error_buf := 'load_snapshot_file_info(cursor: '||
1326 'select file_version_id,check_sum,file_size, '||
1327 'file_id,check_file_id,dest_file_id, '||
1328 'file_type_flag from '||
1329 'ad_check_file_temp):(';
1330
1331 begin
1332 OPEN c1;
1333 exception
1334 when others then
1335 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
1336 raise;
1337 end;
1338 --
1339 --
1340 LOOP
1341 --
1342 --
1343 --
1344 FETCH c1 BULK COLLECT INTO
1345 vers_id_list ,chk_sum_list ,fl_size_list ,
1346 fl_id_list ,con_file_id_list,
1347 dest_file_id_list, file_type_flag_list,
1348 irep_gathered_flag_list,
1349 effective_date_list
1350 LIMIT rows;
1351 --
1352 --
1353 if fl_id_list.COUNT > 0 then
1354 --
1355 --
1356 --
1357 FORALL j IN fl_id_list.FIRST.. fl_id_list.LAST
1358 update /*+ INDEX(SNAP AD_SNAPSHOT_FILES_U2) */
1359 ad_snapshot_files snap
1360 set
1361 snap.file_version_id = vers_id_list(j),
1362 snap.file_size = fl_size_list(j),
1363 snap.checksum = chk_sum_list(j),
1364 snap.dest_file_id = dest_file_id_list(j),
1365 snap.file_type_flag = file_type_flag_list(j),
1366 snap.update_source_id = snp_id,
1367 -- Intentionally storing 'U' so that these rows will be marked
1368 -- so that we can know which rows were updated
1369 snap.update_type ='U',
1370 snap.last_update_date = sysdate,
1371 snap.last_patched_date = decode(preserve_irep_flag,1,
1372 snap.last_patched_date,
1373 decode ((effective_date_list(j) - snap.last_patched_date) -
1374 abs(effective_date_list(j) - snap.last_patched_date),
1375 0, effective_date_list(j), snap.last_patched_date)),
1376 snap.irep_gathered_flag = decode(preserve_irep_flag,1,
1377 snap.irep_gathered_flag,
1378 irep_gathered_flag_list(j))
1379 where
1380 snap.snapshot_id=snp_id and
1381 snap.file_id =fl_id_list(j) and
1382 nvl(snap.containing_file_id,-1)=nvl(con_file_id_list(j),-1);
1383 --
1384 --
1385 end if;
1386 --
1387 --
1388
1389 EXIT WHEN c1%NOTFOUND;
1390 --
1391 --
1392 --
1393 END LOOP;
1394 --
1395 --
1396 --
1397 begin
1398 close c1;
1399 exception
1400 when others then
1401 ad_file_util.error_buf := 'load_snapshot_file_info(Close cursor):('||
1402 sqlerrm||')';
1403 end;
1404 --
1405 --
1406 --
1407 ad_file_util.error_buf := 'load_snapshot_file_info('||
1408 'INSERT INTO ad_snapshot_files '||
1409 '(snapshot_file_id,snapshot_id,file_id, '||
1410 'containing_file_id,file_size,checksum,'||
1411 'file_version_id, update_source_id, '||
1412 'update_type,creation_date,last_update_date,' ||
1413 'last_updated_by,created_by,' ||
1414 'appl_top_id, inconsistent_flag, '||
1415 'dest_file_id, file_type_flag) '||
1416 'select ad_snapshot_files_s.nextval,'||
1417 'snp_id,t.file_id, t.check_file_id,'||
1418 't.file_size,t.check_sum, t.file_version_id,'||
1419 'snp_id,''U'',sysdate,sysdate, 5,5,' ||
1420 't.appl_top_id, t.inconsistent_flag, '||
1421 't.dest_file_id, t.file_type_flag '||
1422 'from ad_check_file_temp t where not exists '||
1423 '(select ''already present'' '||
1424 'from ad_snapshot_files sf2 '||
1425 'where sf2.snapshot_id = snp_id '||
1426 'and sf2.file_id = t.file_id '||
1427 'and nvl(sf2.containing_file_id,-1) = '||
1428 'nvl(t.check_file_id,-1)):(';
1429
1430 begin
1431
1432 INSERT INTO ad_snapshot_files
1433 (snapshot_file_id,snapshot_id,file_id,
1434 containing_file_id,file_size,checksum,file_version_id,
1435 update_source_id, update_type,creation_date,last_update_date,
1436 last_updated_by,created_by, appl_top_id, inconsistent_flag,
1437 dest_file_id, file_type_flag, irep_gathered_flag,last_patched_date)
1438 select
1442 5,5, t.appl_top_id, t.inconsistent_flag,
1439 ad_snapshot_files_s.nextval,snp_id,t.file_id,
1440 t.check_file_id,t.file_size,t.check_sum,
1441 t.file_version_id,snp_id,'U',sysdate,sysdate,
1443 t.dest_file_id, t.file_type_flag,
1444 t.manifest_vers_higher, t.effective_date
1445 from ad_check_file_temp t
1446 where not exists
1447 (select /*+ INDEX(SF2 AD_SNAPSHOT_FILES_U2) */ 'already present'
1448 from ad_snapshot_files sf2
1449 where sf2.snapshot_id = snp_id
1450 and sf2.file_id = t.file_id
1451 and nvl(sf2.containing_file_id,-1) = nvl(t.check_file_id,-1)
1452 );
1453 exception
1454 when others then
1455 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
1456 raise;
1457 end;
1458
1459 update ad_snapshots set last_update_date = sysdate
1460 where snapshot_id = snp_id;
1461
1462 --
1463 --
1464 --
1465 --
1466 end load_snapshot_file_info;
1467 --
1468 --
1469 --
1470 -- Procedure
1471 -- load_preseeded_bugfixes
1472 --
1473 -- Purpose
1474 -- Gets the bug_id from AD_BUGS for the bugnumbers in
1475 -- in ad_check_file_temp table .
1476 -- Creates new rows in the AD_BUGS for the new bugnumbers
1477 -- and gets the bug_id for those bugnumbers and stores them
1478 -- ad_check_file_temp table .
1479 --
1480 -- Inserts those BUG_IDs into AD_SNAPSHOT_BUGFIXES
1481 --
1482 --
1483 -- Arguments
1484 -- None
1485 procedure load_preseeded_bugfixes
1486 is
1487 begin
1488 --
1489 -- Get the bug_id from ad_bugs
1490 --
1491 -- Bug 5758908 - stangutu - 14 June, 2007
1492 ad_file_util.error_buf := 'load_preseeded_bugfixes('||
1493 'SET t.file_id = (SELECT b.bug_id '||
1494 'FROM ad_bugs b WHERE b.bug_number = t.filename '||
1495 'AND b.aru_release_name = t.subdir '||
1496 'AND b.trackable_entity_abbr=t.app_short_name '||
1497 'AND b.language = t.language '||
1498 'AND b.baseline_name = t.manifest_vers_2), '||
1499 't.junk = NULL '||
1500 'WHERE NVL(t.active_flag,''N'') = ''Y''):(';
1501 begin
1502 -- Bug 5579901- stangutu - 9 Oct, 2006
1503 UPDATE ad_check_file_temp t
1504 SET t.file_id = (SELECT /*+ INDEX(B AD_BUGS_U2) */ b.bug_id
1505 FROM ad_bugs b
1506 WHERE b.bug_number = t.filename
1507 AND b.aru_release_name = t.subdir
1508 -- bug 6317065 diverma Thu Aug 2 04:10:21 PDT 2007
1509 AND b.trackable_entity_abbr = t.app_short_name
1510 -- bug 5615204 diverma Tuesday, August 07, 2007
1511 AND b.language = t.language
1512 -- Bug 5596989 - stangutu - 17 Oct, 2006
1513 -- Bug 5758908 - stangutu - 14 June, 2007
1514 AND b.baseline_name = t.manifest_vers_2),
1515 -- If the above condition does not work, we need to include below line.
1516 -- AND b.generic_patch = t.manifest_vers_higher),
1517 t.junk = NULL
1518 WHERE NVL(t.active_flag,'N') = 'Y';
1519 exception
1520 when others then
1521 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
1522 raise;
1523 end;
1524 --
1525 -- add new entries in ad_bugs
1526 --
1527 -- Bug 5758908 - stangutu - 14 Jun, 2007 */
1528 ad_file_util.error_buf := 'load_preseeded_bugfixes('||
1529 'INSERT INTO ad_bugs '||
1530 '(bug_id, bug_number,aru_release_name, '||
1531 'creation_date, created_by, last_update_date, '||
1532 'last_updated_by, baseline_name, generic_patch, '||
1533 ' trackable_entity_abbr ) SELECT '||
1534 'ad_bugs_s.nextval, temp.bugfix, temp.rel, '||
1535 'temp.edate, 5, temp.edate, 5, '||
1536 'temp.baseline_name, temp.generic_patch, '||
1537 'temp.trackable_entity_abbr, language)' ||
1538 'FROM (SELECT DISTINCT t.filename bugfix, '||
1539 't.subdir rel, t.effective_date edate '||
1540 't.manifest_vers_2 baseline_name, '||
1541 't.manifest_vers_higher, generic_patch, '||
1542 't.app_short_name trackable_entity_abbr, ' ||
1543 't.language language '||
1544 'FROM ad_check_file_temp t '||
1545 'WHERE t.file_id is null '||
1546 'AND NVL(t.active_flag,''N'') = ''Y'') temp):(';
1547 begin
1548 INSERT INTO ad_bugs
1549 (bug_id, bug_number,aru_release_name,
1550 creation_date, created_by, last_update_date, last_updated_by,
1551 -- Bug 5758908 - stangutu - 14 June, 2007
1552 baseline_name, generic_patch, trackable_entity_abbr,
1553 -- bug 5615204 diverma Tuesday, August 07, 2007
1554 language)
1555 SELECT
1556 ad_bugs_s.nextval, temp.bugfix, temp.rel,
1557 temp.edate, 5, temp.edate, 5,
1558 -- Bug 5758908 - stangutu - 14 June, 2007
1562 FROM
1559 temp.baseline_name, temp.generic_patch, temp.trackable_entity_abbr,
1560 -- bug 5615204 diverma Tuesday, August 07, 2007
1561 temp.language
1563 (SELECT DISTINCT
1564 t.filename bugfix,
1565 t.subdir rel ,
1566 t.effective_date edate,
1567 -- Bug 5758908 - stangutu - 14 June, 2007
1568 t.manifest_vers_2 baseline_name,
1569 t.manifest_vers_higher generic_patch,
1570 t.app_short_name trackable_entity_abbr,
1571 -- bug 5615204 diverma Tuesday, August 07, 2007
1572 t.language language
1573 FROM ad_check_file_temp t
1574 WHERE t.file_id is null
1575 AND NVL(t.active_flag,'N') = 'Y') temp;
1576 exception
1577 when others then
1578 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
1579 raise;
1580 end;
1581 --
1582 -- add bug_id for new entries
1583 --
1584 -- Bug 5758908 - stangutu - 14 June, 2007
1585 ad_file_util.error_buf := 'load_preseeded_bugfixes('||
1586 'UPDATE ad_check_file_temp t '||
1587 'SET t.file_id = (SELECT b.bug_id '||
1588 'FROM ad_bugs b WHERE b.bug_number = t.filename '||
1589 'AND b.aru_release_name = t.subdir, '||
1590 'AND b.trackable_entity_abbr = t.app_short_name' ||
1591 'AND b.language = t.language '||
1592 'AND b.baseline_name = t.manifest_vers_2), '||
1593 't.junk = NULL '||
1594 'WHERE NVL(t.active_flag,''N'') = ''Y''):(';
1595 begin
1596 -- Bug 5579901- stangutu - 9 Oct, 2006
1597 UPDATE ad_check_file_temp t
1598 SET t.file_id = (SELECT /*+ INDEX(B AD_BUGS_U2) */ b.bug_id
1599 FROM ad_bugs b
1600 WHERE b.bug_number = t.filename
1601 AND b.aru_release_name = t.subdir
1602 -- bug 6317065 diverma Thu Aug 2 04:10:21 PDT 2007
1603 AND b.trackable_entity_abbr = t.app_short_name
1604 -- bug 5615204 diverma Tuesday, August 07, 2007
1605 AND b.language = t.language
1606 -- Bug 5596989 - stangutu -17Oct, 2006
1607 -- Bug 5758908 - stangutu - 14 June, 2007
1608 AND b.baseline_name = t.manifest_vers_2),
1609 -- If the above condition does not work, we need to include below line.
1610 -- AND b.generic_patch = t.manifest_vers_higher),
1611 t.junk = NULL
1612 WHERE NVL(t.active_flag,'N') = 'Y';
1613 exception
1614 when others then
1615 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
1616 raise;
1617 end;
1618 --
1619 -- store the pre-seed the list of bug fixes included
1620 -- in that Maintenance Pack.
1621 --
1622 ad_file_util.error_buf := 'load_preseeded_bugfixes('||
1623 'INSERT into ad_snapshot_bugfixes( '||
1624 'snapshot_bug_id,snapshot_id, '||
1625 'bugfix_id,bug_status,success_flag, '||
1626 'creation_date,last_update_date, '||
1627 'last_updated_by,created_by) '||
1628 'SELECT ad_snapshot_bugfixes_s.nextval,'||
1629 'file_version_id_2, file_id,''EXPLICIT'',''Y'','||
1630 'sysdate, sysdate,5,5 FROM ad_check_file_temp t '||
1631 'where not exists (select ''already present'' '||
1632 'from ad_snapshot_bugfixes b '||
1633 'where b.BUGFIX_ID=t.file_id and '||
1634 'b.SNAPSHOT_ID=t.file_version_id_2):(';
1635
1636
1637 begin
1638 INSERT into ad_snapshot_bugfixes(
1639 snapshot_bug_id,snapshot_id,
1640 bugfix_id,bug_status,success_flag,creation_date,
1641 last_update_date,last_updated_by,created_by)
1642 SELECT ad_snapshot_bugfixes_s.nextval,file_version_id_2,
1643 file_id,'EXPLICIT','Y',sysdate,
1644 sysdate,5,5
1645 FROM
1646 ad_check_file_temp t
1647 where not exists
1648 (select /*+ INDEX(B AD_SNAPSHOT_BUGFIXES_U2) */ 'already present'
1649 from ad_snapshot_bugfixes b
1650 where b.BUGFIX_ID=t.file_id and
1651 b.SNAPSHOT_ID=t.file_version_id_2);
1652 exception
1653 when others then
1654 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
1655 raise;
1656 end;
1657 end load_preseeded_bugfixes;
1658 --
1659 --
1660 --
1661 --
1662 procedure load_patch_hist_action
1663 (bugs_processed out NOCOPY number,
1664 actions_processed out NOCOPY number)
1665 is
1666 l_bugs_processed number := 0;
1667 l_actions_processed number := 0;
1668 --
1669 -- for deleting duplicate actions
1670 --
1671 cursor del_cursor is
1672 select patch_run_bug_id, common_action_id, file_id, rowid row_id
1673 from ad_patch_hist_temp
1674 where (patch_run_bug_id, common_action_id, file_id) in
1675 (select patch_run_bug_id, common_action_id, file_id
1676 from AD_PATCH_HIST_TEMP
1677 group by patch_run_bug_id, common_action_id, file_id
1678 having count(*) > 1)
1679 order by 1, 2, 3;
1680 prb_id number;
1681 ca_id number;
1682 f_id number;
1683 statement varchar2(200);
1684 --
1685 -- end for deleting duplicate actions
1689 -- bug 6343734 diverma 16 August 2007
1686 --
1687 begin
1688
1690 --
1691 -- update AD_PATCH_HIST_TEMP.TRACKABLE_ENTITY_ABBR with
1692 -- AD_PATCH_HIST_TEMP.BUG_APP_SHORT_NAME if it is null.
1693 --
1694
1695 update AD_PATCH_HIST_TEMP
1696 set TRACKABLE_ENTITY_NAME = BUG_APP_SHORT_NAME
1697 where TRACKABLE_ENTITY_NAME is null;
1698
1699 update AD_PATCH_HIST_TEMP
1700 set LANGUAGE = 'US'
1701 where LANGUAGE is null;
1702
1703 --
1704 -- Add new entries in AD_BUGS
1705 --
1706 insert into ad_bugs
1707 (
1708 BUG_ID, BUG_NUMBER, ARU_RELEASE_NAME, CREATION_DATE,
1709 CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
1710 -- bug 5615204 diverma Tuesday, August 07, 2007
1711 TRACKABLE_ENTITY_ABBR, BASELINE_NAME, GENERIC_PATCH, LANGUAGE
1712 )
1713 -- bug 6332450 diverma Thu Aug 9 06:25:06 PDT 2007
1714 select
1715 ad_bugs_s.nextval, BUG_NUMBER, ARU_RELEASE_NAME, sysdate,
1716 5, sysdate, 5, TRACKABLE_ENTITY_NAME , BASELINE_NAME,
1717 GENERIC_PATCH, LANGUAGE
1718 from
1719 (
1720 select
1721 distinct BUG_NUMBER, ARU_RELEASE_NAME,
1722 -- bug 6332450 diverma Thu Aug 9 06:25:06 PDT 2007
1723 -- bug 5615204 diverma Tuesday, August 07, 2007
1724 TRACKABLE_ENTITY_NAME, BASELINE_NAME, GENERIC_PATCH, LANGUAGE
1725 from
1726 AD_PATCH_HIST_TEMP where BUG_NUMBER is not null) tmp
1727 where
1728 not exists (
1729 select
1730 'x'
1731 from
1732 ad_bugs b
1733 where
1734 b.bug_number = tmp.BUG_NUMBER
1735 -- bug 6332450 diverma Thu Aug 9 06:25:06 PDT 2007
1736 and b.trackable_entity_abbr = tmp.TRACKABLE_ENTITY_NAME
1737 and b.baseline_name = tmp.baseline_name
1738 -- bug 5615204 diverma Tuesday, August 07, 2007
1739 and b.language = tmp.LANGUAGE
1740 );
1741 -- schinni bug 5612532 25th Oct 2006
1742 -- ----------------------------------------------------------
1743 -- Changed the condition in the subquery .
1744 -- Earlier condition " b.generic_patch=y " was returning multiple
1745 -- rows for a single row return subquery.
1746 -- Using the generic_patch column present in the ad_patch_hist_temp
1747 -- for refining the search condition in subquery
1748 -- and to return a single row
1749 -- -----------------------------------------------------------
1750 --
1751 -- Get the Bug_id into the Staging Table
1752 --
1753 update AD_PATCH_HIST_TEMP t
1754 set t.bug_id = (
1755 select
1756 b.bug_id from ad_bugs b
1757 where
1758 b.bug_number = t.BUG_NUMBER
1759 -- bug 6332450 diverma Thu Aug 9 06:25:06 PDT 2007
1760 and b.trackable_entity_abbr = t.TRACKABLE_ENTITY_NAME
1761 and nvl(b.baseline_name,'NULL') = nvl(t.baseline_name,'NULL')
1762 -- bug 5615204 diverma Tuesday, August 07, 2007
1763 and b.language = t.LANGUAGE
1764 );
1765 --
1766 --
1767 commit;
1768 --
1769 -- Add new entries in the AD_PATCH_RUN_BUGS
1770 --
1771 insert into ad_patch_run_bugs
1772 (
1773 PATCH_RUN_BUG_ID,
1774 PATCH_RUN_ID, BUG_ID, ORIG_BUG_NUMBER, APPLICATION_SHORT_NAME,
1775 SUCCESS_FLAG, APPLIED_FLAG, REASON_NOT_APPLIED,
1776 CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY
1777 )
1778 select
1779 ad_patch_run_bugs_s.nextval,
1780 patch_run_id, bug_id, orig_bug_number,bug_app_short_name,
1781 success_flag, applied_flag, reason_not_applied,
1782 sysdate, sysdate, 5, 5
1783 from (
1784 select
1785 distinct patch_run_id,bug_id,
1786 orig_bug_number, bug_app_short_name,
1787 success_flag, applied_flag, reason_not_applied
1788 from
1789 AD_PATCH_HIST_TEMP ) t
1790 where
1791 not exists (
1792 select
1793 'x'
1794 from ad_patch_run_bugs b
1795 where
1796 b.PATCH_RUN_ID = t.patch_run_id
1797 and b.BUG_ID = t.bug_id
1798 and b.ORIG_BUG_NUMBER = t.orig_bug_number
1799 and b.APPLICATION_SHORT_NAME = t.bug_app_short_name);
1800
1801 l_bugs_processed := sql%rowcount;
1802 bugs_processed := l_bugs_processed;
1803 --
1804 -- Get the patch_run_bug_id into staging table
1805 --
1806 update AD_PATCH_HIST_TEMP t
1807 set PATCH_RUN_BUG_ID
1808 =(select
1809 b.PATCH_RUN_BUG_ID
1810 from
1811 ad_patch_run_bugs b
1812 where
1813 b.PATCH_RUN_ID = t.patch_run_id
1814 and b.BUG_ID = t.bug_id
1815 and b.ORIG_BUG_NUMBER = t.orig_bug_number
1816 and b.APPLICATION_SHORT_NAME = t.bug_app_short_name);
1817 --
1818 --
1819 commit;
1820 --
1821 -- Add new entries in ad_files
1822 --
1823 insert into ad_files
1824 (file_id,
1825 app_short_name,
1826 subdir, filename,
1827 creation_date, created_by, last_update_date, last_updated_by)
1828 select ad_files_s.nextval,
1829 temp.FILE_APPS_SHORT_NAME asn,
1830 temp.file_subdir dir, temp.filename fname,
1831 sysdate,5,sysdate,5
1832 from
1833 (select distinct
1834 t.file_apps_short_name ,
1835 t.file_subdir ,
1836 t.filename
1837 from
1838 AD_PATCH_HIST_TEMP t
1839 ) temp
1840 where not exists (
1844 fl.filename = temp.filename
1841 select
1842 'x' from ad_files fl
1843 where
1845 and fl.subdir = temp.file_subdir
1846 and fl.app_short_name = temp.file_apps_short_name
1847 )
1848 and temp.filename is not null;
1849 --
1850 -- Get the file_id into the staging table
1851 --
1852 update AD_PATCH_HIST_TEMP t
1853 set t.file_id =
1854 (select f.file_id
1855 from ad_files f
1856 where
1857 f.filename = t.filename
1858 and f.subdir = t.file_subdir
1859 and f.app_short_name = t.file_apps_short_name);
1860 --
1861 --
1862 commit;
1863 --
1864 -- Add new entries in ad_files for Loader files
1865 --
1866 insert into ad_files
1867 (file_id, app_short_name, subdir, filename,
1868 creation_date, created_by, last_update_date, last_updated_by)
1869 select ad_files_s.nextval,
1870 temp.ldr_app_short_name asn,
1871 temp.ldr_subdir dir, temp.ldr_filename fname,
1872 sysdate, 5, sysdate, 5
1873 from
1874 (select distinct
1875 t.ldr_app_short_name ,
1876 t.ldr_subdir ,
1877 t.ldr_filename
1878 from
1879 AD_PATCH_HIST_TEMP t
1880 ) temp
1881 where not exists (
1882 select
1883 'x' from ad_files fl
1884 where
1885 fl.filename = temp.ldr_filename
1886 and fl.subdir = temp.ldr_subdir
1887 and fl.app_short_name = temp.ldr_app_short_name
1888 )
1889 and temp.ldr_filename is not null;
1890 --
1891 -- Get the Loader file_id into the staging table
1892 --
1893 update AD_PATCH_HIST_TEMP t
1894 set t.loader_data_file_id =
1895 (select f.file_id
1896 from ad_files f
1897 where
1898 f.filename = t.ldr_filename
1899 and f.subdir = t.ldr_subdir
1900 and f.app_short_name = t.ldr_app_short_name)
1901 where t.ldr_filename is not null;
1902 --
1903 --
1904 commit;
1905
1906 --
1907 -- Add new entries in ad_files for the destination files
1908 --
1909 insert into ad_files
1910 (
1911 file_id,
1912 app_short_name,
1913 subdir,
1914 filename,
1915 creation_date, last_update_date, last_updated_by, created_by
1916 )
1917 select
1918 ad_files_s.nextval,
1919 temp.dest_apps_short_name,
1920 temp.dest_subdir,
1921 temp.dest_filename,
1922 sysdate, sysdate, 5, 5
1923 from
1924 (select distinct
1925 t.dest_apps_short_name ,
1926 t.dest_subdir ,
1927 t.dest_filename
1928 from
1929 AD_PATCH_HIST_TEMP t
1930 where t.dest_apps_short_name is not null
1931 and t.dest_subdir is not null
1932 and t.dest_filename is not null
1933 ) temp
1934 where not exists (
1935 select
1936 'dest file already exists' from ad_files f
1937 where
1938 f.filename = temp.dest_filename
1939 and f.subdir = temp.dest_subdir
1940 and f.app_short_name = temp.dest_apps_short_name);
1941
1942 --
1943 -- Get the Destination file_id into the staging table
1944 --
1945 update AD_PATCH_HIST_TEMP t
1946 set t.dest_file_id =
1947 (select /*+ INDEX(F AD_FILES_U2) */ f.file_id
1948 from ad_files f
1949 where f.app_short_name = t.dest_apps_short_name
1950 and f.subdir = t.dest_subdir
1951 and f.filename = t.dest_filename);
1952 --
1953 --
1954 commit;
1955
1956 --
1957 -- Add new entries in the ad_file_versions
1958 --
1959 INSERT into ad_file_versions
1960 (file_version_id, file_id, version, translation_level,
1961 version_segment1, version_segment2, version_segment3,
1962 version_segment4, version_segment5, version_segment6,
1963 version_segment7, version_segment8, version_segment9,
1964 version_segment10,
1965 creation_date, created_by, last_update_date, last_updated_by)
1966 select
1967 ad_file_versions_s.nextval,
1968 temp.f_id, temp.vers, temp.trans_level,
1969 temp.vs1, temp.vs2, temp.vs3, temp.vs4, temp.vs5,
1970 temp.vs6, temp.vs7, temp.vs8, temp.vs9, temp.vs10,
1971 sysdate, 5, sysdate, 5
1972 from
1973 (
1974 select
1975 distinct
1976 t.file_id f_id,
1977 t.PATCH_FILE_VERS vers,
1978 t.PATCH_TRANS_LEVEL trans_level,
1979 t.PATCH_VERSION_SEGMENT1 vs1,
1980 t.PATCH_VERSION_SEGMENT2 vs2,
1981 t.PATCH_VERSION_SEGMENT3 vs3,
1982 t.PATCH_VERSION_SEGMENT4 vs4,
1983 t.PATCH_VERSION_SEGMENT5 vs5,
1984 t.PATCH_VERSION_SEGMENT6 vs6,
1985 t.PATCH_VERSION_SEGMENT7 vs7,
1986 t.PATCH_VERSION_SEGMENT8 vs8,
1987 t.PATCH_VERSION_SEGMENT9 vs9,
1988 t.PATCH_VERSION_SEGMENT10 vs10
1989 from
1990 AD_PATCH_HIST_TEMP t
1991 where
1992 t.PATCH_FILE_VERS is not null
1993 ) temp
1994 where not exists (
1995 select
1996 'x'
1997 from
1998 ad_file_versions vers
1999 where
2000 vers.file_id = temp.f_id
2001 and vers.version = temp.vers
2002 and vers.translation_level = temp.trans_level);
2003 --
2007 (file_version_id, file_id, version, translation_level,
2004 -- Add new entries in the ad_file_versions
2005 --
2006 INSERT into ad_file_versions
2008 version_segment1, version_segment2, version_segment3,
2009 version_segment4, version_segment5, version_segment6,
2010 version_segment7, version_segment8, version_segment9,
2011 version_segment10,
2012 creation_date, created_by, last_update_date, last_updated_by)
2013 select
2014 ad_file_versions_s.nextval,
2015 temp.f_id, temp.vers, temp.trans_level,
2016 temp.vs1, temp.vs2, temp.vs3, temp.vs4, temp.vs5,
2017 temp.vs6, temp.vs7, temp.vs8, temp.vs9, temp.vs10,
2018 sysdate, 5, sysdate, 5
2019 from
2020 (
2021 select
2022 distinct t.file_id f_id,
2023 t.ONSITE_FILE_VERS vers,
2024 t.ONSITE_TRANS_LEVEL trans_level,
2025 t.ONSITE_VERSION_SEGMENT1 vs1,
2026 t.ONSITE_VERSION_SEGMENT2 vs2,
2027 t.ONSITE_VERSION_SEGMENT3 vs3,
2028 t.ONSITE_VERSION_SEGMENT4 vs4,
2029 t.ONSITE_VERSION_SEGMENT5 vs5,
2030 t.ONSITE_VERSION_SEGMENT6 vs6,
2031 t.ONSITE_VERSION_SEGMENT7 vs7,
2032 t.ONSITE_VERSION_SEGMENT8 vs8,
2033 t.ONSITE_VERSION_SEGMENT9 vs9,
2034 t.ONSITE_VERSION_SEGMENT10 vs10
2035 from
2036 AD_PATCH_HIST_TEMP t
2037 where
2038 t.ONSITE_FILE_VERS is not NULL
2039 ) temp
2040 where not exists (
2041 select
2042 'x'
2043 from
2044 ad_file_versions vers
2045 where
2046 vers.file_id = temp.f_id
2047 and vers.version = temp.vers
2048 and vers.translation_level = temp.trans_level);
2049 --
2050 -- Add new entries in the ad_file_versions
2051 --
2052 INSERT into ad_file_versions
2053 (file_version_id, file_id, version, translation_level,
2054 version_segment1, version_segment2, version_segment3,
2055 version_segment4, version_segment5, version_segment6,
2056 version_segment7, version_segment8, version_segment9,
2057 version_segment10,
2058 creation_date, created_by, last_update_date, last_updated_by)
2059 select
2060 ad_file_versions_s.nextval,
2061 tmp.f_id,tmp.vers, tmp.trans_level,
2062 tmp.vs1, tmp.vs2, tmp.vs3, tmp.vs4,
2063 tmp.vs5, tmp.vs6, tmp.vs7, tmp.vs8,
2064 tmp.vs9, tmp.vs10,sysdate, 5, sysdate, 5
2065 from
2066 (
2067 select
2068 distinct
2069 t.file_id f_id,
2070 t.DB_FILE_VERS vers,
2071 t.DB_TRANS_LEVEL trans_level,
2072 t.DB_VERSION_SEGMENT1 vs1 ,
2073 t.DB_VERSION_SEGMENT2 vs2 ,
2074 t.DB_VERSION_SEGMENT3 vs3 ,
2075 t.DB_VERSION_SEGMENT4 vs4 ,
2076 t.DB_VERSION_SEGMENT5 vs5 ,
2077 t.DB_VERSION_SEGMENT6 vs6 ,
2078 t.DB_VERSION_SEGMENT7 vs7 ,
2079 t.DB_VERSION_SEGMENT8 vs8 ,
2080 t.DB_VERSION_SEGMENT9 vs9 ,
2081 t.DB_VERSION_SEGMENT10 vs10
2082 from
2083 AD_PATCH_HIST_TEMP t
2084 where
2085 t.DB_FILE_VERS is not null
2086 ) tmp
2087 where not exists (
2088 select
2089 'x'
2090 from
2091 ad_file_versions vers
2092 where
2093 vers.file_id = tmp.f_id
2094 and vers.version = tmp.vers
2095 and vers.translation_level = tmp.trans_level);
2096 --
2097 --
2098 commit;
2099 --
2100 --
2101 -- Process the PatchFile Versions
2102 --
2103 -- Get the file_version_id into the staging table
2104 --
2105 update AD_PATCH_HIST_TEMP t
2106 set t.PATCH_FILE_VERS_ID =
2107 (select
2108 fv.file_version_id
2109 from
2110 ad_file_versions fv
2111 where
2112 fv.file_id = t.file_id
2113 and fv.version = t.PATCH_FILE_VERS
2114 and fv.translation_level = t.PATCH_TRANS_LEVEL)
2115 where
2116 t.PATCH_FILE_VERS is not NULL;
2117 --
2118 --
2119 -- Process the OnSiteFile Versions
2120 --
2121 --
2122 -- Get the file_version_id into the staging table
2123 --
2124 --
2125 update AD_PATCH_HIST_TEMP t
2126 set t.ONSITE_FILE_VERS_ID =
2127 (select
2128 fv.file_version_id
2129 from
2130 ad_file_versions fv
2131 where
2132 fv.file_id = t.file_id
2133 and fv.version = t.ONSITE_FILE_VERS
2134 and fv.translation_level = t.ONSITE_TRANS_LEVEL
2135 )
2136 where
2137 t.ONSITE_FILE_VERS is not NULL;
2138 --
2139 --
2140 -- Process the Db FileVersions
2141 --
2142 -- Get the file_version_id into the staging table
2143 --
2144 update AD_PATCH_HIST_TEMP t
2145 set t.DB_FILE_VERS_ID =
2146 (select
2147 fv.file_version_id
2148 from
2149 ad_file_versions fv
2150 where
2151 fv.file_id = t.file_id
2152 and fv.version = t.DB_FILE_VERS
2153 and fv.translation_level = t.DB_TRANS_LEVEL)
2154 where
2155 t.DB_FILE_VERS is not NULL;
2156 --
2157 --
2158 commit;
2159 --
2160 -- Add new entries in the ad_patch_common_actions
2161 --
2162 INSERT INTO AD_PATCH_COMMON_ACTIONS
2163 (
2164 COMMON_ACTION_ID, ACTION_CODE, ACTION_PHASE, NUMERIC_PHASE,
2168 CONCAT_ATTRIBS, LOADER_DATA_FILE_ID, CREATION_DATE,
2165 NUMERIC_SUB_PHASE, ACTION_ARGUMENTS, CHECKFILE_ARGS,
2166 ACTION_CHECK_OBJ, ACTION_CHECK_OBJ_USERNAME, ACTION_CHECK_OBJ_PASSWD,
2167 ACTION_WHAT_SQL_EXEC, ACTION_TIERLIST_IN_DRIVER, ACTION_LANG_CODE,
2169 LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY
2170 )
2171 select AD_PATCH_COMMON_ACTIONS_S.NEXTVAL,
2172 t.action_code, t.action_phase, t.major_phase, t.minor_phase,
2173 t.action_arguments, t.checkfile_args, t.checkobj , t.checkobj_un ,
2174 t.checkobj_pw, t.action_modifier , t.action_tierlist ,
2175 t.action_lang_code , t.concat_attribs, t.loader_data_file_id,
2176 sysdate, sysdate, 5, 5
2177 from
2178 (
2179 select distinct
2180 action_code, action_phase, major_phase, minor_phase,
2181 action_arguments, checkfile_args, checkobj , checkobj_un ,
2182 checkobj_pw, action_modifier, action_tierlist ,
2183 action_lang_code, concat_attribs, loader_data_file_id
2184 from
2185 AD_PATCH_HIST_TEMP )t
2186 where not exists (
2187 select
2188 'x'
2189 FROM
2190 AD_PATCH_COMMON_ACTIONS PCA
2191 WHERE
2192 PCA.CONCAT_ATTRIBS = t.CONCAT_ATTRIBS)
2193 and t.concat_attribs is not null;
2194 --
2195 -- Get the COMMON_ACTION_ID into the staging table
2196 --
2197 update AD_PATCH_HIST_TEMP t
2198 set t.COMMON_ACTION_ID =
2199 (select
2200 PCA.COMMON_ACTION_ID
2201 from
2202 AD_PATCH_COMMON_ACTIONS PCA
2203 WHERE
2204 PCA.CONCAT_ATTRIBS = t.concat_attribs )
2205 where t.concat_attribs is not null;
2206 --
2207 --
2208 commit;
2209 --
2210 -- Fix bug 2757813:
2211 -- remove any duplicate actions in same bug fix from temp table
2212 -- These will cause logic below to fail
2213 --
2214 -- Later we should set allow_duplicate_actions= FALSE; in adpdrv.lc
2215 -- so that we don't get any duplicate actions in the action list
2216 -- and also stop calling adptod().
2217 --
2218 begin
2219
2220 prb_id := -1;
2221 ca_id := -1;
2222 f_id := -1;
2223
2224 for c1 in del_cursor loop
2225
2226 -- dbms_output.put_line(c1.patch_run_bug_id||','||c1.common_action_id||
2227 -- ','||c1.file_id||','||c1.row_id);
2228 -- dbms_output.put_line(prb_id||','||ca_id||','||f_id);
2229
2230 if c1.patch_run_bug_id <> prb_id
2231 or c1.common_action_id <> ca_id
2232 or c1.file_id <> f_id then
2233
2234 prb_id := c1.patch_run_bug_id;
2235 ca_id := c1.common_action_id;
2236 f_id := c1.file_id;
2237
2238 statement := 'delete from ad_patch_hist_temp'||
2239 ' where patch_run_bug_id = '||c1.patch_run_bug_id||
2240 ' and common_action_id = '||c1.common_action_id||
2241 ' and file_id = '||c1.file_id||
2242 ' and rowid <> '''||c1.row_id||'''';
2243
2244 -- dbms_output.put_line(statement);
2245
2246 execute immediate statement;
2247 end if;
2248
2249 end loop;
2250 end;
2251 --
2252 -- Add new entries in the ad_patch_run_bug_actions
2253 --
2254 insert into AD_PATCH_RUN_BUG_ACTIONS
2255 (
2256 ACTION_ID,
2257 PATCH_RUN_BUG_ID,
2258 COMMON_ACTION_ID,
2259 FILE_ID,
2260 PATCH_FILE_VERSION_ID,
2261 ONSITE_FILE_VERSION_ID,
2262 ONSITE_PKG_VERSION_IN_DB_ID,
2263 EXECUTED_FLAG,
2264 DEST_FILE_ID, FILE_TYPE_FLAG,
2265 CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY
2266 )
2267 select
2268 AD_PATCH_RUN_BUG_ACTIONS_S.NEXTVAL,
2269 t.patch_run_bug_id,
2270 t.common_action_id,
2271 t.file_id,
2272 t.patch_file_vers_id,
2273 t.onsite_file_vers_id,
2274 t.db_file_vers_id,
2275 t.action_executed_flag,
2276 t.dest_file_id, t.file_type_flag,
2277 SYSDATE, SYSDATE, 5, 5 from AD_PATCH_HIST_TEMP t
2278 where not exists
2279 (select
2280 'x'
2281 from
2282 AD_PATCH_RUN_BUG_ACTIONS aprba
2283 where
2284 aprba.PATCH_RUN_BUG_ID = t.patch_run_bug_id
2285 and aprba.FILE_ID = t.file_id
2286 and aprba.COMMON_ACTION_ID = t.common_action_id)
2287 and t.common_action_id is not null and t.ldr_filename is null;
2288 --
2289 --
2290 l_actions_processed := sql%rowcount;
2291 actions_processed := l_actions_processed;
2292 --
2293 --
2294 commit;
2295 --
2296 -- Add new entries in the ad_patch_run_bug_actions with loader files.
2297 -- bug 3486202, cbhati
2298 --
2299 insert into AD_PATCH_RUN_BUG_ACTIONS
2300 (
2301 ACTION_ID,
2302 PATCH_RUN_BUG_ID,
2303 COMMON_ACTION_ID,
2304 FILE_ID,
2305 PATCH_FILE_VERSION_ID,
2306 ONSITE_FILE_VERSION_ID,
2307 ONSITE_PKG_VERSION_IN_DB_ID,
2308 EXECUTED_FLAG,
2309 DEST_FILE_ID, FILE_TYPE_FLAG,
2310 CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY
2311 )
2312 select
2313 AD_PATCH_RUN_BUG_ACTIONS_S.NEXTVAL,
2314 t.patch_run_bug_id,
2315 t.common_action_id,
2316 t.loader_data_file_id,
2317 t.patch_file_vers_id,
2318 t.onsite_file_vers_id,
2319 t.db_file_vers_id,
2320 t.action_executed_flag,
2321 t.dest_file_id, t.file_type_flag,
2322 SYSDATE, SYSDATE, 5, 5 from AD_PATCH_HIST_TEMP t
2326 from
2323 where not exists
2324 (select
2325 'x'
2327 AD_PATCH_RUN_BUG_ACTIONS aprba
2328 where
2329 aprba.PATCH_RUN_BUG_ID = t.patch_run_bug_id
2330 and aprba.FILE_ID = t.loader_data_file_id
2331 and aprba.COMMON_ACTION_ID = t.common_action_id)
2332 and t.common_action_id is not null and t.loader_data_file_id is not null;
2333 --
2334 --
2335 l_actions_processed := sql%rowcount;
2336 actions_processed := l_actions_processed;
2337 --
2338 --
2339 commit;
2340
2341 --
2342 --
2343 end load_patch_hist_action;
2344
2345
2346 -- Procedure
2347 -- create_global_view
2348 -- Arguments
2349 -- p_apps_system_name - Applications system name
2350 -- Purpose
2351 -- Procedure to create Global View snapshot using exisiting
2352 -- current view snapshots for an applications system.
2353 -- Notes
2354 -- Pre-requiste: ad_snapshot_files_temp sholud have been populated
2355 -- before calling this API.
2356
2357 procedure create_global_view(p_apps_system_name varchar2)
2358 is
2359 l_release_id number;
2360 l_snapshot_count number;
2361 l_global_snapshot_id number;
2362 l_appl_top_id number;
2363 l_appl_top_count number;
2364 begin
2365
2366 /* Compute total number of current view snapshots available */
2367 select count(*) into l_snapshot_count
2368 from ad_snapshots s, ad_appl_tops t
2369 where s.snapshot_type = 'C' and
2370 s.snapshot_name = 'CURRENT_VIEW' and
2371 s.appl_top_id = t.appl_top_id and
2372 t.applications_system_name = p_apps_system_name;
2373
2374 /* Get the release id */
2375 select release_id into l_release_id from ad_releases
2376 where to_char(major_version) || '.' ||
2377 to_char(minor_version) || '.' ||
2378 to_char(tape_version) = (select release_name
2379 from fnd_product_groups
2380 where applications_system_name =
2381 p_apps_system_name);
2382
2383 /* Create a dummy Appl_top called 'GLOBAL' */
2384 insert into ad_appl_tops
2385 (
2386 appl_top_id, name, applications_system_name, appl_top_type,
2387 description,
2388 server_type_admin_flag,
2389 server_type_forms_flag,
2390 server_type_node_flag,
2391 server_type_web_flag,
2392 creation_date,
2393 created_by,
2394 last_update_date,
2395 last_updated_by,
2396 active_flag
2397 )
2398 select
2399 ad_appl_tops_s.nextval,
2400 'GLOBAL', /* APPL_TOP type is 'G' */
2401 p_apps_system_name,
2402 'G',
2403 'Created for Global View Snapshot',
2404 null,
2405 null,
2406 null,
2407 null,
2408 sysdate,
2409 5,
2410 sysdate,
2411 5,
2412 'N' /* ACTIVE_FLAG is set to 'N'. (Refer CONCURRENT_SESSIONS) */
2413 from dual where not exists(select 'Already exists'
2414 from ad_appl_tops t
2415 where t.name = 'GLOBAL' and
2416 t.appl_top_type = 'G' and
2417 t.applications_system_name = p_apps_system_name);
2418
2419 /* Get 'GLOBAL' APPL_TOP_ID */
2420 select appl_top_id into l_appl_top_id
2421 from ad_appl_tops
2422 where appl_top_type = 'G' and
2423 name = 'GLOBAL' and
2424 applications_system_name = p_apps_system_name;
2425
2426 insert into ad_snapshots
2427 (
2428 snapshot_id, release_id, appl_top_id, snapshot_name,
2429 snapshot_creation_date,
2430 snapshot_update_date,
2431 snapshot_type,
2432 comments,
2433 ran_snapshot_flag,
2434 creation_date,
2435 last_updated_by,
2436 created_by,
2437 last_update_date
2438 )
2439 select ad_snapshots_s.nextval, l_release_id,
2440 l_appl_top_id,
2441 'GLOBAL_VIEW',
2442 sysdate,
2443 sysdate,
2444 'G', /* snapshot type is 'G' */
2445 'Created from Current View Snapshots',
2446 'Y', /* Setting RAN_SNAPSHOT_FLAG to 'Y'. Because, it doesn't */
2447 sysdate, /* have any significance for GLOBAL_VIEW */
2448 5,
2449 5,
2450 sysdate
2451 from dual where not exists(select 'Already exists'
2452 from ad_snapshots s
2453 where s.appl_top_id = l_appl_top_id
2454 and s.snapshot_type = 'G'
2455 and s.snapshot_name = 'GLOBAL_VIEW');
2456
2457 /* Get Global snapshot ID for this Applications Sytem Name */
2458 select s.snapshot_id into l_global_snapshot_id
2459 from ad_snapshots s
2460 where s.snapshot_type = 'G' and
2461 s.snapshot_name = 'GLOBAL_VIEW' and
2462 s.appl_top_id = l_appl_top_id;
2463
2464 commit;
2465 exception
2466 when others then
2467 rollback;
2468 raise;
2469 end create_global_view;
2470 -- Procedure
2471 -- populate_snapshot_files_temp
2472 -- Arguments
2473 -- p_apps_system_name - Applications System Name
2474 --
2475 -- p_min_file_id - lower file_id in the range of file_ids
2479 -- p_global_snapshot_id - Global snapshot_id
2476 --
2477 -- p_max_file_id - upper file_id in the range of file_ids
2478 --
2480 --
2481 -- p_un_fnd - applsys username
2482 --
2483 -- p_iteration - which iteration (1,2,etc)
2484 -- Purpose
2485 -- This procedure populates temp table with a range of file_ids
2486 -- processes the data and updates the ad_snapshot_files with negative
2487 -- global snapshot_id
2488 -- Notes
2489
2490 procedure populate_snapshot_files_temp(p_applications_sys_name varchar2,p_min_file_id number,
2491 p_max_file_id number,p_global_snapshot_id number,
2492 p_un_fnd varchar2,p_iteration number)
2493 is
2494 v_global_snapshot_count number;
2495 v_global_snapshot_id number;
2496 v_global_appl_top_id number;
2497 begin
2498 --
2499 --
2500 execute immediate 'truncate table '|| p_un_fnd ||'.ad_snapshot_files_temp';
2501 execute immediate 'truncate table '|| p_un_fnd ||'.ad_patch_hist_temp';
2502 --
2503 --
2504 if (p_iteration = 1) then
2505 execute immediate 'truncate table '|| p_un_fnd ||'.ad_check_file_temp';
2506 --
2507 --
2508 insert into ad_check_file_temp
2509 (TRANSLATION_LEVEL,APP_SHORT_NAME,
2510 SUBDIR,FILENAME, MANIFEST_VERS,
2511 EFFECTIVE_DATE)
2512 select
2513 snapshot_id, ' ',
2514 ' ',' ',' ',
2515 sysdate
2516 from
2517 ad_snapshots snap,
2518 ad_appl_tops atp
2519 where
2520 atp.appl_top_id=snap.appl_top_id and
2521 atp.applications_system_name=p_applications_sys_name and
2522 nvl(atp.active_flag,'Y') = 'Y' and
2523 snap.snapshot_type = 'C';
2524 --
2525 --
2526 end if;
2527 --
2528 --
2529 commit;
2530 --
2531 --
2532 v_global_snapshot_id:=(-1*p_global_snapshot_id);
2533 --
2534 --
2535 insert into ad_snapshot_files
2536 (
2537 snapshot_file_id,snapshot_id,
2538 file_id, file_version_id, containing_file_id,
2539 file_size, checksum, update_source_id, update_type,
2540 appl_top_id, inconsistent_flag, dest_file_id,file_type_flag,
2541 creation_date,last_update_date,last_updated_by,created_by
2542 )
2543 select
2544 ad_snapshot_files_s.nextval,v_global_snapshot_id,
2545 file_id,file_version_id,containing_file_id,
2546 file_size,checksum,update_source_id,'S',
2547 appl_top_id, 'N', dest_file_id,file_type_flag,
2548 sysdate,sysdate,5,5
2549 from
2550 (
2551 select
2552 file_id,
2553 max(file_version_id) file_version_id,
2554 max(containing_file_id) containing_file_id,
2555 max(file_size) file_size,
2556 max(checksum) checksum,
2557 max(snapshot_id) snapshot_id,
2558 max(dest_file_id) dest_file_id,
2559 max(appl_top_id) appl_top_id,
2560 decode(max(decode(update_type, 'P', 2, 1)), 2, 'P', 'S') update_type,
2561 decode(max(decode(file_type_flag, 'M', 2, 1)), 2, 'M', 'N') file_type_flag,
2562 replace(max(decode(update_type, 'P', 'a', null)||
2563 to_char(update_source_id)), 'a', null) update_source_id
2564 from
2565 ad_snapshot_files
2566 where
2567 file_id >= p_min_file_id and
2568 file_id < p_max_file_id and
2569 snapshot_id in (select TRANSLATION_LEVEL from ad_check_file_temp)
2570 group by
2571 file_id
2572 having
2573 count(distinct nvl(file_version_id,0))=1
2574 );
2575 --
2576 --
2577 commit;
2578 --
2579 --
2580 insert into ad_patch_hist_temp
2581 (
2582 file_id, patch_file_vers_id , onsite_file_vers_id,
2583 bug_id, patch_run_bug_id ,
2584 db_file_vers_id, applied_flag,common_action_id,
2585 success_flag, major_phase,action_executed_flag,
2586 concat_attribs
2587 )
2588 select
2589 file_id,
2590 nvl(file_version_id,0),
2591 containing_file_id,
2592 file_size,
2593 checksum,
2594 update_source_id,
2595 update_type,
2596 appl_top_id ,
2597 'Y',
2598 dest_file_id,
2599 file_type_flag,
2600 '1234567890123456789012345678901234567890123456789012345678901234567890'
2601 from
2602 ad_snapshot_files
2603 where
2604 file_id in
2605 ( select
2606 file_id from ad_snapshot_files
2607 where
2608 file_id >= p_min_file_id and
2609 file_id < p_max_file_id and
2610 snapshot_id in (select TRANSLATION_LEVEL from ad_check_file_temp)
2611 group by
2612 file_id
2613 having
2614 count(distinct nvl(file_version_id,0)) >1
2615 ) and
2616 snapshot_id in (select TRANSLATION_LEVEL from ad_check_file_temp);
2617 --
2618 --
2619 commit;
2620 --
2621 --
2622 update ad_patch_hist_temp tmp set
2623 (tmp.PATCH_VERSION_SEGMENT1, tmp.PATCH_VERSION_SEGMENT2,
2624 tmp.PATCH_VERSION_SEGMENT3, tmp.PATCH_VERSION_SEGMENT4,
2625 tmp.PATCH_VERSION_SEGMENT5, tmp.PATCH_VERSION_SEGMENT6,
2629 (select
2626 tmp.PATCH_VERSION_SEGMENT7, tmp.PATCH_VERSION_SEGMENT8,
2627 tmp.PATCH_VERSION_SEGMENT9, tmp.PATCH_VERSION_SEGMENT10,
2628 tmp.PATCH_FILE_VERS, tmp.PATCH_TRANS_LEVEL) =
2630 v.VERSION_SEGMENT1, v.VERSION_SEGMENT2,
2631 v.VERSION_SEGMENT3, v.VERSION_SEGMENT4,
2632 v.VERSION_SEGMENT5, v.VERSION_SEGMENT6,
2633 v.VERSION_SEGMENT7, v.VERSION_SEGMENT8,
2634 v.VERSION_SEGMENT9, v.VERSION_SEGMENT10,
2635 v.VERSION, v.TRANSLATION_LEVEL
2636 from
2637 ad_file_versions v
2638 where
2639 v.file_version_id = tmp.PATCH_FILE_VERS_ID),
2640 tmp.concat_attribs=null;
2641 --
2642 --
2643 update ad_patch_hist_temp tmp set
2644 tmp.PATCH_VERSION_SEGMENT1=0, tmp.PATCH_VERSION_SEGMENT2=0,
2645 tmp.PATCH_VERSION_SEGMENT3=0, tmp.PATCH_VERSION_SEGMENT4=0,
2646 tmp.PATCH_VERSION_SEGMENT5=0, tmp.PATCH_VERSION_SEGMENT6=0,
2647 tmp.PATCH_VERSION_SEGMENT7=0, tmp.PATCH_VERSION_SEGMENT8=0,
2648 tmp.PATCH_VERSION_SEGMENT9=0, tmp.PATCH_VERSION_SEGMENT10=0,
2649 tmp.PATCH_FILE_VERS=null, tmp.PATCH_TRANS_LEVEL=null
2650 where tmp.PATCH_FILE_VERS_ID=0;
2651 --
2652 execute immediate 'insert into ad_snapshot_files
2653 (
2654 snapshot_file_id,snapshot_id,
2655 file_id, file_version_id, containing_file_id,
2656 file_size, checksum, update_source_id, update_type,
2657 appl_top_id, inconsistent_flag, dest_file_id,file_type_flag,
2658 creation_date,last_update_date,last_updated_by,
2659 created_by
2660 )
2661 select
2662 ad_snapshot_files_s.nextval,:v_global_snapshot_id,
2663 file_id, patch_file_vers_id , onsite_file_vers_id,
2664 bug_id, patch_run_bug_id,db_file_vers_id,applied_flag,
2665 common_action_id, ''Y'', major_phase,action_executed_flag,
2666 sysdate,sysdate,5,5
2667 from
2668 (
2669 select
2670 file_id, patch_file_vers_id , onsite_file_vers_id,
2671 bug_id, patch_run_bug_id ,
2672 db_file_vers_id, applied_flag,common_action_id,
2673 success_flag, major_phase ,action_executed_flag,row_number() over
2674 (
2675 PARTITION BY file_id order by
2676 PATCH_VERSION_SEGMENT1 desc, PATCH_VERSION_SEGMENT2 desc,
2677 PATCH_VERSION_SEGMENT3 desc, PATCH_VERSION_SEGMENT4 desc,
2678 PATCH_VERSION_SEGMENT5 desc, PATCH_VERSION_SEGMENT6 desc,
2679 PATCH_VERSION_SEGMENT7 desc, PATCH_VERSION_SEGMENT8 desc,
2680 PATCH_VERSION_SEGMENT9 desc, PATCH_VERSION_SEGMENT10 desc,
2681 PATCH_TRANS_LEVEL desc NULLS LAST
2682 ) rnk
2683 from
2684 ad_patch_hist_temp) where rnk=1' using v_global_snapshot_id;
2685 --
2686 --
2687 commit;
2688 --
2689 --
2690 if (p_iteration = 1) then
2691 FND_STATS.Gather_Table_Stats(p_un_fnd, 'AD_SNAPSHOT_FILES_TEMP');
2692 FND_STATS.Gather_Table_Stats(p_un_fnd, 'AD_CHECK_FILE_TEMP');
2693 FND_STATS.Gather_Table_Stats(p_un_fnd, 'AD_PATCH_HIST_TEMP');
2694 end if;
2695 --
2696 --
2697 end populate_snapshot_files_temp;
2698 --
2699 --
2700 -- Procedure
2701 -- populate_snapshot_bugs_temp
2702 -- Arguments
2703 -- p_apps_system_name - Applications System Name
2704 --
2705 -- p_min_bug_id - lower bugfix_id in the range of bugfix_id
2706 --
2707 -- p_max_bug_id - upper bugfix_id in the range of bugfix_id
2708 --
2709 -- p_global_snapshot_id - Global snapshot_id
2710 --
2711 -- p_un_fnd - applsys username
2712 --
2713 -- p_iteration - which iteration (1,2,etc)
2714 -- Purpose
2715 -- This procedure populates temp table with a range of bugfix_id
2716 -- processes the data and updates the ad_snapshot_bugfixes with negative
2717 -- global snapshot_id
2718 -- Notes
2719 --
2720 procedure populate_snapshot_bugs_temp(p_applications_sys_name varchar2,p_min_bug_id number,
2721 p_max_bug_id number,p_global_snapshot_id number,
2722 p_un_fnd varchar2,p_iteration number)
2723 is
2724 v_global_snapshot_id number;
2725 begin
2726 --
2727 --
2728 execute immediate 'truncate table '||p_un_fnd||'.ad_check_file_temp';
2729 --
2730 --
2731 if (p_iteration = 1) then
2732 --
2733 --
2737 insert into ad_patch_hist_temp
2734 execute immediate 'truncate table '||p_un_fnd||'.ad_patch_hist_temp';
2735 --
2736 --
2738 (patch_run_id)
2739 select
2740 snapshot_id
2741 from
2742 ad_snapshots snap,
2743 ad_appl_tops atp
2744 where
2745 atp.appl_top_id=snap.appl_top_id and
2746 atp.applications_system_name=p_applications_sys_name and
2747 nvl(atp.active_flag,'Y') = 'Y' and
2748 snap.snapshot_type = 'C';
2749 --
2750 --
2751 end if;
2752 v_global_snapshot_id:=(-1 *p_global_snapshot_id);
2753 --
2754 --
2755 insert into ad_snapshot_bugfixes
2756 (
2757 SNAPSHOT_BUG_ID,
2758 snapshot_id, bugfix_id,
2759 inconsistent_flag,
2760 bug_status, success_flag,
2761 creation_date,last_update_date,last_updated_by,
2762 created_by
2763 )
2764 select
2765 ad_snapshot_bugfixes_s.nextval,v_global_snapshot_id,
2766 bugfix_id,
2767 'N',
2768 bug_status,success_flag,
2769 sysdate,sysdate,5,5
2770 from
2771 (
2772 select
2773 bugfix_id,
2774 decode(max(decode(success_flag, 'Y', 2, 1)),
2775 2, 'Y', 'N') success_flag,
2776 decode(max(decode(bug_status, 'EXPLICIT', 2, 1)),
2777 2, 'EXPLICIT', 'IMPLICIT') bug_status
2778 from
2779 ad_snapshot_bugfixes
2780 where
2781 bugfix_id >= p_min_bug_id and
2782 bugfix_id < p_max_bug_id and
2783 snapshot_id in (select patch_run_id from ad_patch_hist_temp)
2784 group by
2785 bugfix_id
2786 having
2787 count(distinct decode(success_flag, 'Y', 2, 1)) = 1);
2788 --
2789 --
2790 insert into ad_check_file_temp (
2791 file_version_id,
2792 app_short_name , active_flag,
2793 check_file_id,subdir,filename,
2794 manifest_vers,translation_level,effective_date)
2795 select
2796 bugfix_id,
2797 bug_status, success_flag,
2798 (decode(success_flag,'Y',1,2) * 3 +
2799 decode(bug_status,'EXPLICIT',1,'IMPLICIT',2,3)) bug_rank ,
2800 'NA','NA','NA',0,sysdate
2801 from
2802 (
2803 select
2804 bugfix_id,
2805 decode(max(decode(success_flag, 'Y', 2, 1)),
2806 2, 'Y', 'N') success_flag,
2807 decode(max(decode(bug_status, 'EXPLICIT', 2, 1)),
2808 2, 'EXPLICIT', 'IMPLICIT') bug_status
2809 from
2810 ad_snapshot_bugfixes
2811 where
2812 bugfix_id >= p_min_bug_id and
2813 bugfix_id < p_max_bug_id and
2814 snapshot_id in (select patch_run_id from ad_patch_hist_temp)
2815 group by bugfix_id
2816 having count(distinct decode(success_flag, 'Y', 2, 1)) >1);
2817 --
2818 --
2819 execute immediate 'insert into ad_snapshot_bugfixes
2820 (
2821 SNAPSHOT_BUG_ID,
2822 snapshot_id, bugfix_id,
2823 inconsistent_flag,
2824 bug_status, success_flag,
2825 creation_date,last_update_date,last_updated_by,
2826 created_by
2827 )
2828 select
2829 ad_snapshot_bugfixes_s.nextval,:snp_id,
2830 file_version_id,
2831 ''Y'',
2832 app_short_name , active_flag,
2833 sysdate,sysdate,5,5
2834 from
2835 (
2836 select
2837 file_version_id,
2838 app_short_name , active_flag,rnk
2839 from
2840 (
2841 select
2842 file_version_id,
2843 app_short_name , active_flag,
2844 ROW_NUMBER() over
2845 (
2846 PARTITION BY file_version_id order by
2847 check_file_id
2848 ) rnk
2849 from
2850 ad_check_file_temp
2851 )
2852 ) where rnk=1 ' using v_global_snapshot_id;
2853 --
2854 --
2855 commit;
2856 --
2857 --
2858 if (p_iteration = 1) then
2859 FND_STATS.Gather_Table_Stats(p_un_fnd, 'AD_SNAPSHOT_FILES_TEMP');
2860 FND_STATS.Gather_Table_Stats(p_un_fnd, 'AD_CHECK_FILE_TEMP');
2861 FND_STATS.Gather_Table_Stats(p_un_fnd, 'AD_PATCH_HIST_TEMP');
2862 end if;
2863 --
2864 --
2865 end populate_snapshot_bugs_temp;
2866 --
2867 --
2868 --
2869 -- Procedure
2870 -- load_prepmode_checkfile_info
2871 --
2872 -- Purpose
2873 -- Imports file information from ad_check_file_temp to
2874 -- ad_prepmode_check_files, when applying a patch is "prepare" mode.
2875 --
2876 -- Arguments
2877 -- none
2878 --
2879 procedure load_prepmode_checkfile_info is
2880 begin
2881
2882 --
2883 -- first update versions for existing rows
2884 -- (assume that the versions in temporary table are higher)
2885 --
2886
2887 update ad_prepmode_check_files cf
2888 set version = (select t.manifest_vers
2889 from ad_check_file_temp t
2890 where t.app_short_name = cf.app_short_name
2891 and t.subdir = cf.subdir
2892 and t.filename = cf.filename
2893 and nvl(t.distinguisher, '~') = cf.distinguisher)
2894 where (app_short_name, subdir, filename, distinguisher) in
2895 (select app_short_name, subdir, filename, nvl(distinguisher, '~')
2896 from ad_check_file_temp
2900 -- insert rows for new files
2897 where manifest_vers is not null);
2898
2899 --
2901 --
2902 insert into ad_prepmode_check_files cf
2903 (
2904 app_short_name, subdir, filename, distinguisher,
2905 version
2906 )
2907 select distinct app_short_name, subdir, filename, nvl(distinguisher, '~'),
2908
2909 manifest_vers
2910 from ad_check_file_temp t
2911 where t.manifest_vers is not null
2912 and not exists (
2913 select null
2914 from ad_prepmode_check_files cf2
2915 where cf2.app_short_name = t.app_short_name
2916 and cf2.subdir = t.subdir
2917 and cf2.filename = t.filename
2918 and cf2.distinguisher = nvl(t.distinguisher, '~'));
2919
2920
2921 end load_prepmode_checkfile_info;
2922
2923 --
2924 -- Procedure
2925 -- cln_prepmode_checkfile_info
2926 --
2927 -- Purpose
2928 -- deletes rows from ad_premode_check_files (called after the merge)
2929 --
2930 -- Arguments
2931 -- none
2932 --
2933 procedure cln_prepmode_checkfile_info
2934 is
2935 begin
2936 delete from ad_prepmode_check_files;
2937 end cln_prepmode_checkfile_info;
2938
2939 --
2940 -- Bug 4488796 - rahkumar
2941 -- Procedure
2942 -- load_snpst_file_server_info
2943 --
2944 -- Purpose
2945 -- updates the values of the server flags of the table ad_snapshot_files
2946 -- as obtained from the temporary table ad_check_file_temp
2947 --
2948 -- Arguments
2949 -- snp_id - snapshot_id for which the rows are to be updated
2950 --
2951 procedure load_snpst_file_server_info
2952 (snp_id number)
2953 is
2954 TYPE t_file_id IS TABLE OF ad_check_file_temp.file_id%TYPE;
2955 TYPE t_containing_file_id IS TABLE OF ad_check_file_temp.check_file_id%TYPE;
2956 TYPE t_admin_server_flag IS TABLE OF ad_check_file_temp.server_type_admin_flag%TYPE;
2957 TYPE t_forms_server_flag IS TABLE OF ad_check_file_temp.server_type_forms_flag%TYPE;
2958 TYPE t_node_server_flag IS TABLE OF ad_check_file_temp.server_type_node_flag%TYPE;
2959 TYPE t_web_server_flag IS TABLE OF ad_check_file_temp.server_type_web_flag%TYPE;
2960 --
2961 fl_id_list t_file_id;
2962 con_file_id_list t_containing_file_id;
2963 admin_server_flag_list t_admin_server_flag;
2964 forms_server_flag_list t_forms_server_flag;
2965 node_server_flag_list t_node_server_flag;
2966 web_server_flag_list t_web_server_flag;
2967 --
2968 --
2969 --
2970 CURSOR c1 IS
2971 SELECT
2972 file_id,check_file_id,
2973 server_type_admin_flag,
2974 server_type_forms_flag,
2975 server_type_node_flag,
2976 server_type_web_flag
2977 FROM ad_check_file_temp;
2978 --
2979 --
2980 --
2981 --
2982 cur_rec c1%ROWTYPE;
2983 rows NATURAL := 2000;
2984 --
2985 --
2986 begin
2987
2988 ad_file_util.error_buf := 'load_snpst_file_server_info(cursor: '||
2989 'select file_id,check_file_id,server_type_admin_flag, '||
2990 'server_type_forms_flag, server_type_node_flag, '||
2991 'server_type_web_flag from '||
2992 'ad_check_file_temp):(';
2993 --
2994 begin
2995 OPEN c1;
2996 exception
2997 when others then
2998 ad_file_util.error_buf := ad_file_util.error_buf||sqlerrm||')';
2999 raise;
3000 end;
3001 --
3002 --
3003 LOOP
3004 --
3005 --
3006 --
3007 FETCH c1 BULK COLLECT INTO
3008 fl_id_list ,con_file_id_list,
3009 admin_server_flag_list, forms_server_flag_list,
3010 node_server_flag_list, web_server_flag_list
3011 LIMIT rows;
3012 --
3013 --
3014 if fl_id_list.COUNT > 0 then
3015 --
3016 --
3017 --
3018 FORALL j IN fl_id_list.FIRST.. fl_id_list.LAST
3019 update
3020 ad_snapshot_files snap
3021 set
3022 snap.server_type_admin_flag = admin_server_flag_list(j),
3023 snap.server_type_forms_flag = forms_server_flag_list(j),
3024 snap.server_type_node_flag = node_server_flag_list(j),
3025 snap.server_type_web_flag = web_server_flag_list(j)
3026 where
3030 --
3027 snap.snapshot_id=snp_id and
3028 snap.file_id =fl_id_list(j) and
3029 nvl(snap.containing_file_id,-1)=nvl(con_file_id_list(j),-1);
3031 --
3032 end if;
3033 --
3034 --
3035
3036 EXIT WHEN c1%NOTFOUND;
3037 --
3038 --
3039 --
3040 END LOOP;
3041 --
3042 --
3043 --
3044 begin
3045 close c1;
3046 exception
3047 when others then
3048 ad_file_util.error_buf := 'load_snpst_file_server_info(Close cursor):('||
3049 sqlerrm||')';
3050 end;
3051
3052 --
3053 --
3054 --
3055 end load_snpst_file_server_info;
3056 --
3057 --
3058
3059 end ad_file_util;