1 PACKAGE BODY FND_IMP_PKG AS
2 /* $Header: afimpb.pls 120.14.12010000.1 2008/07/25 14:32:06 appldev ship $ */
3
4 FUNCTION lastupdate(snapshot_id__ INTEGER, patch_id__ INTEGER)
5 RETURN DATE IS
6 answ__ DATE;
7 BEGIN
8 select max(z.ts) into answ__
9 from
10 (select max(greatest(adts.snapshot_creation_date, adts.snapshot_update_date, adts.creation_date, adts.last_update_date)) ts
11 from ad_snapshots adts where adts.snapshot_id = snapshot_id__
12 UNION ALL
13 select max(greatest(umsts.last_update_date, umsts.last_definition_date)) ts
14 from fnd_ums_bugfixes umsts
15 where umsts.bug_number = (select bug_number from ad_pm_patches where patch_id = patch_id__) AND --ang
16 umsts.baseline = (select baseline from ad_pm_patches where patch_id = patch_id__) --ang
17 ) z;
18
19 return answ__;
20 END lastupdate;
21
22 PROCEDURE makesingletons(request_id__ INTEGER) IS
23 snapshot_id__ NUMBER;
24 --buglist ad_patch_impact_api.t_rec_patch; --ang
25 patchlist ad_patch_impact_api.t_recomm_patch_tab; --ang
26 bug_no INTEGER;
27 --baseline VARCHAR2(150); --ang
28 patch_id NUMBER; --ang
29 i INTEGER;
30 BEGIN
31 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> reading patch list...');
32 ad_patch_impact_api.get_global_snapshot_id(snapshot_id__);
33 --ad_patch_impact_api.get_recommend_patch_list(buglist);--ang
34 ad_patch_impact_api.get_recommend_patch_list(patchlist);--ang
35
36 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> master patch list = '||patchlist.COUNT||' patches');
37 for i in 1..patchlist.COUNT
38 loop
39 bug_no := patchlist(i).bug_number;
40 --baseline := patchlist(i).baseline;
41 patch_id := patchlist(i).patch_id;
42 -- MONITOR: for every patch in the request, create a local singleton record('S')
43 insert into fnd_imp_monitor(request_id, snapshot_id, set_type, virtual_bug_no,
44 virtual_patch_id, r1_requestor, r1_sync_date, r2_requestor, r2_sync_date, --ang
45 creation_date, last_update_date, last_updated_by, created_by)
46 values(request_id__, snapshot_id__, 'S', bug_no,
47 patch_id, request_id__, null, request_id__, null, sysdate, sysdate, -1, -1); --ang
48 end loop;
49
50 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> reading prereq list...');
51 -- ang entire for loop can be commented out as prereq analysis is, at the moment, not supported by AD
52 --for i in 1..patchlist.COUNT --ang
53 --loop
54 --bug_no := patchlist(i).bug_number; --ang
55 --ang for now AD Folks are not doing prereq analysis
56 --makeprereqs(request_id__, bug_no);
57 --end loop;
58
59 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> continuing...');
60 END makesingletons;
61
62 --ang commenting out the below code as prereq analysis is, at the moment, not supported by AD
63 /*PROCEDURE makeprereqs(request_id__ INTEGER, bug_no__ INTEGER) IS
64 snapshot_id__ NUMBER;
65 buglist ad_patch_impact_api.t_prereq_patch;
66 prereq_bug_no INTEGER;
67 patch_order INTEGER;
68 BEGIN
69 ad_patch_impact_api.get_global_snapshot_id(snapshot_id__);
70 ad_patch_impact_api.get_prereq_list(bug_no__, buglist);
71
72 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> '||bug_no__||' prereq list = '||buglist.COUNT||' patches');
73 for patch_order in 1..buglist.COUNT
74 loop
75 prereq_bug_no := buglist(patch_order);
76 -- BUGSET: for every prereq for patches in the request, create a local prereq record ('m')
77 insert into fnd_imp_bugset(request_id, snapshot_id, set_type, virtual_bug_no, bug_no, patch_order,
78 creation_date, last_update_date, last_updated_by, created_by)
79 values(request_id__, snapshot_id__, 'm', 0-bug_no__, prereq_bug_no,
80 patch_order, sysdate, sysdate, -1, -1);
81 end loop;
82 END makeprereqs;*/
83
84 PROCEDURE refresh1M(request_id__ INTEGER, virtual_patch_id__ INTEGER, snapshot_id__ INTEGER) IS --ang
85 patch_id__ INTEGER; --ang
86 BEGIN
87 patch_id__ := virtual_patch_id__; --ang
88 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> running-refresh1M['||patch_id__||','||snapshot_id__||']...'); --ang
89 delete from fnd_imp_bugset_temp;
90 delete from FND_IMP_PSMaster2 where patch_id = patch_id__ and snapshot_id = snapshot_id__; --ang
91 -- delete from FND_IMP_AffectedFiles where bug_no = bug_no__ and snapshot_id = snapshot_id__;
92 -- delete from FND_IMP_DiagMap where bug_no = bug_no__ and snapshot_id = snapshot_id__;
93 commit;
94 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> after delete');
95
96 insert into fnd_imp_bugset_temp(patch_id)
97 (select q1.patch_id from fnd_imp_bugset q1
98 where q1.request_id = request_id__ and q1.snapshot_id = snapshot_id__
99 and q1.virtual_patch_id = virtual_patch_id__ and q1.set_type IN ('M','m','A'));
100 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> after insert into fnd_imp_bugset_temp');
101 --
102 -- Tuned Performance Fix from Performance Team
103 --
104 INSERT INTO FND_IMP_PSMASTER2(PATCH_ID, SNAPSHOT_ID, APP_SHORT_NAME,
105 DIRECTORY, FILENAME, TYPEID, NEW_VERSION, OLD_VERSION, FILES_AFFECTED, TYPE_AFFECTED, FILE_TYPE, TRANS_NAME, IS_FLAGGED_FILE)
106 SELECT virtual_patch_id__ PATCH_ID, snapshot_id__ SNAPSHOT_ID, APP_SHORT_NAME,
107 DIRECTORY, FILENAME, MAX(TYPEID), MAX(NEW_VERSION), MAX(OLD_VERSION),
108 MAX(FILES_AFFECTED), MAX(TYPE_AFFECTED), MAX(FILE_TYPE), MAX(TRANS_NAME),MAX(IS_FLAGGED_FILE)
109 FROM
110 FND_IMP_PSMASTER2 M
111 WHERE
112 M.SNAPSHOT_ID = snapshot_id__
113 AND M.PATCH_ID IN ( SELECT PATCH_ID FROM FND_IMP_BUGSET_TEMP)
114 AND NOT EXISTS (
115 SELECT /*+ INDEX(X FND_IMP_PSMASTER2_N1) */ 1
116 FROM FND_IMP_PSMASTER2 X
117 WHERE X.SNAPSHOT_ID = snapshot_id__
118 AND X.PATCH_ID IN (SELECT PATCH_ID FROM FND_IMP_BUGSET_TEMP where PATCH_ID <> M.PATCH_ID)
119 AND X.APP_SHORT_NAME = M.APP_SHORT_NAME
120 AND X.DIRECTORY = M.DIRECTORY
121 AND X.FILENAME = M.FILENAME
122 and X.NEW_VERSION IS NOT NULL
123 AND ( ( M.NEW_VERSION IS NULL)
124 or ( M.NEW_VERSION IS NOT NULL AND FND_IMP_CONV_PKG.COMPARE_RCSID(M.NEW_VERSION, X.NEW_VERSION) IN (1, 2)) ) )
125 GROUP BY APP_SHORT_NAME, DIRECTORY,FILENAME;
126
127 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> after insert into fnd_imp_psmaster2');
128 commit;
129 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> completed-refresh1M['||patch_id__||','||snapshot_id__||']...');
130 END refresh1M;
131
132 PROCEDURE logstats(request_id__ INTEGER, stage__ CHAR) IS
133 CURSOR stats1 IS
134 select virtual_patch_id, r1_requestor --ang
135 from fnd_imp_monitor
136 where r1_requestor IS NOT NULL and request_id <> r1_requestor
137 and psmaster2_sz <> 0 and request_id = request_id__;
138 CURSOR stats2 IS
139 select virtual_patch_id, r2_requestor --ang
140 from fnd_imp_monitor
141 where r2_requestor IS NOT NULL and request_id <> r2_requestor
142 and psmaster2_sz <> 0 and request_id = request_id__;
143 tmp1 stats1%ROWTYPE;
144 tmp2 stats2%ROWTYPE;
145 BEGIN
146 if(stage__ = '1') THEN
147 OPEN stats1;
148 LOOP
149 FETCH stats1 into tmp1;
150 EXIT WHEN stats1%NOTFOUND;
151 -- dbms_output.put_line('Note: Skipped stage1 for: '||tmp1.virtual_bug_no||' since it was already processed with request='||tmp1.r1_requestor);
152 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> Skipped stage1 for: '||tmp1.virtual_patch_id||' since it was already processed with request='||tmp1.r1_requestor); --ang
153 commit;
154 END LOOP;
155 CLOSE stats1;
156 else
157 if(stage__ = '2') THEN
158 OPEN stats2;
159 LOOP
160 FETCH stats2 into tmp2;
161 EXIT WHEN stats2%NOTFOUND;
162 -- dbms_output.put_line('Note: Skipped stage2 for: '||tmp2.virtual_bug_no||' since it was already processed with request='||tmp2.r2_requestor);
163 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> Skipped stage2 for: '||tmp2.virtual_patch_id||' since it was already processed with request='||tmp2.r2_requestor); --ang
164 commit;
165 END LOOP;
166 CLOSE stats2;
167 end if;
168 end if;
169 END logstats;
170
171 PROCEDURE rsync1(request_id__ INTEGER) IS
172 CURSOR bugsnaps1 IS
173 --select snapshot_id, virtual_bug_no, set_type
174 select snapshot_id, virtual_patch_id, set_type --ang
175 from fnd_imp_monitor
176 where request_id = request_id__ and
177 ((set_type IN ('M', 'S', 's') and r1_sync_date IS NULL)
178 or psmaster2_sz = 0)
179 order by snapshot_id, set_type desc, virtual_patch_id;
180 virtual_patch_id__ INTEGER;
181 snapshot_id__ INTEGER;
182 set_type__ CHAR;
183 time__ DATE;
184 stage__ CHAR := '1';
185 psmaster2_sz__ NUMBER;
186 BEGIN
187 -- MONITOR/BUGSET: clean local records
188 delete from fnd_imp_monitor where request_id = request_id__;
189 -- todo: would this clear what PW sets!?
190 delete from fnd_imp_bugset where request_id = request_id__ and set_type <> 'A';
191
192 makesingletons(request_id__);
193 -- BUGSET/MONITOR: for every patch with prereq record(s), create a local multiset record ('M')
194 /* ang - not applicable as AD Team, for now, isn't doing prereq analysis
195 insert into fnd_imp_bugset(request_id, snapshot_id, set_type, virtual_bug_no, bug_no,
196 creation_date, last_update_date, last_updated_by, created_by)
197 select request_id__, s.snapshot_id, 'M', s.virtual_bug_no, 0-s.virtual_bug_no, sysdate, sysdate, -1, -1
198 from fnd_imp_bugset s
199 where s.request_id = request_id__
200 group by s.snapshot_id, s.virtual_bug_no;
201 insert into fnd_imp_monitor(request_id, snapshot_id, set_type, virtual_bug_no,
202 r1_requestor, r1_sync_date, r2_requestor, r2_sync_date,
203 creation_date, last_update_date, last_updated_by, created_by)
204 select request_id__, s.snapshot_id, 'M' set_type, s.virtual_bug_no,
205 request_id__, null, request_id__, null, sysdate, sysdate, -1, -1
206 from fnd_imp_bugset s
207 where s.request_id = request_id__ and set_type = 'M';
208 */
209 -- MONITOR: for all prereqs, create a local servant singleton record ('s')
210 /* ang - not applicable as AD Team, for now, isn't doing prereq analysis
211 insert into fnd_imp_monitor(request_id, snapshot_id, set_type, virtual_bug_no,
212 r1_requestor, r1_sync_date, r2_requestor, r2_sync_date,
213 creation_date, last_update_date, last_updated_by, created_by)
214 select request_id__, s.snapshot_id, 's' set_type, s.bug_no virtual_bug_no,
215 request_id__, null, request_id__, null, sysdate, sysdate, -1, -1
216 from fnd_imp_bugset s
217 where s.request_id = request_id__
218 and s.set_type = 'm'
219 and s.bug_no NOT IN(select m.virtual_bug_no from fnd_imp_monitor m where m.request_id = request_id__)
220 group by s.snapshot_id, s.bug_no;
221 */
222 -- MONITOR: remove any local 'M', 'S' or 's' records that already have an up-to-date global record
223 update fnd_imp_monitor x
224 set
225 r1_requestor =
226 (select DECODE(stage__,'1',n.r1_requestor,n.r2_requestor)
227 from fnd_imp_monitor n
228 where n.request_id IS NULL
229 --and n.snapshot_id = x.snapshot_id and n.virtual_bug_no = x.virtual_bug_no), --ang
230 and n.snapshot_id = x.snapshot_id and n.virtual_patch_id = x.virtual_patch_id), --ang
231 r1_sync_date =
232 (select DECODE(stage__,'1',n.r1_sync_date,n.r2_sync_date)
233 from fnd_imp_monitor n
234 where n.request_id IS NULL
235 --and n.snapshot_id = x.snapshot_id and n.virtual_bug_no = x.virtual_bug_no), --ang
236 and n.snapshot_id = x.snapshot_id and n.virtual_patch_id = x.virtual_patch_id), --ang
237 psmaster2_sz =
238 (select n.psmaster2_sz
239 from fnd_imp_monitor n
240 where n.request_id IS NULL
241 --and n.snapshot_id = x.snapshot_id and n.virtual_bug_no = x.virtual_bug_no) --ang
242 and n.snapshot_id = x.snapshot_id and n.virtual_patch_id = x.virtual_patch_id) --ang
243 where x.request_id = request_id__
244 and EXISTS (
245 select 1 from fnd_imp_monitor m
246 where
247 m.request_id IS NULL
248 --and m.snapshot_id = x.snapshot_id and m.virtual_bug_no = x.virtual_bug_no --ang
249 and m.snapshot_id = x.snapshot_id and m.virtual_patch_id = x.virtual_patch_id
250 --and DECODE(stage__,'1',m.r1_sync_date,m.r2_sync_date) >= lastupdate(x.snapshot_id, x.virtual_bug_no) --ang
251 and DECODE(stage__,'1',m.r1_sync_date,m.r2_sync_date) >= lastupdate(x.snapshot_id, x.virtual_patch_id) --ang
252 );
253 commit;
254 logstats(request_id__, '1');
255 commit;
256
257 OPEN bugsnaps1;
258 LOOP
259 FETCH bugsnaps1 into snapshot_id__, virtual_patch_id__, set_type__; --ang
260 EXIT WHEN bugsnaps1%NOTFOUND;
261 select sysdate into time__ from dual;
262 if(set_type__ = 'M') then
263 refresh1M(request_id__, virtual_patch_id__, snapshot_id__); --ang
264 else
265 refresh1(virtual_patch_id__, snapshot_id__); --ang
266 end if;
267
268 select count(*) into psmaster2_sz__
269 from fnd_imp_psmaster2
270 where snapshot_id = snapshot_id__
271 and patch_id = virtual_patch_id__;
272
273 if psmaster2_sz__ = 0 then
274 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> No Patch Metadata found for patch: '||virtual_patch_id__);
275 end if;
276
277 if(SIGN(virtual_patch_id__) = 1) THEN
278 wipedata(0-virtual_patch_id__, snapshot_id__);
279 end if;
280
281 -- delete global record
282 delete from fnd_imp_monitor
283 where request_id IS NULL and snapshot_id = snapshot_id__ and virtual_patch_id = virtual_patch_id__;
284 -- insert global record
285 insert into fnd_imp_monitor(request_id, snapshot_id, set_type, virtual_patch_id,
286 r1_requestor, r1_sync_date, r2_requestor, r2_sync_date, psmaster2_sz,
287 creation_date, last_update_date, last_updated_by, created_by)
288 values(NULL, snapshot_id__, set_type__, virtual_patch_id__,
289 request_id__, time__, request_id__, null, psmaster2_sz__,
290 sysdate, sysdate, -1, -1);
291 if(SIGN(virtual_patch_id__) = -1) then
292 update fnd_imp_monitor set set_type = 'S'
293 where request_id IS NULL and snapshot_id = snapshot_id__
294 and set_type = 's' and virtual_patch_id = 0-virtual_patch_id__;
295 end if;
296 -- update local record
297 update fnd_imp_monitor set r1_sync_date = time__, last_update_date = time__, psmaster2_sz = psmaster2_sz__
298 where request_id = request_id__ and snapshot_id = snapshot_id__
299 and set_type = set_type__ and virtual_patch_id = virtual_patch_id__;
300 commit;
301 --fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> Done with patch: '||virtual_bug_no__);
302 END LOOP;
303 CLOSE bugsnaps1;
304 --fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> Done with rsync1');
305 END rsync1;
306
307 PROCEDURE rsync2(request_id__ INTEGER) IS
308 CURSOR bugsnaps2 IS
309 select snapshot_id, virtual_patch_id, set_type, psmaster2_sz
310 from fnd_imp_monitor
311 where request_id = request_id__ and
312 ((set_type IN ('M', 'S', 's') and r2_sync_date IS NULL)
313 or psmaster2_sz = 0)
314 order by snapshot_id, set_type desc, virtual_patch_id;
315 virtual_patch_id__ INTEGER;
316 snapshot_id__ INTEGER;
317 set_type__ CHAR;
318 time__ DATE;
319 stage__ CHAR := '2';
320 psmaster2_sz__ NUMBER;
321 BEGIN
322 -- MONITOR: remove any local 'M', 'S' or 's' records that already have an up-to-date global record
323 update fnd_imp_monitor x
324 set
325 r2_requestor =
326 (select DECODE(stage__,'1',n.r1_requestor,n.r2_requestor)
327 from fnd_imp_monitor n
328 where n.request_id IS NULL
329 and n.snapshot_id = x.snapshot_id and n.virtual_patch_id = x.virtual_patch_id),
330 r2_sync_date =
331 (select DECODE(stage__,'1',n.r1_sync_date,n.r2_sync_date)
332 from fnd_imp_monitor n
333 where n.request_id IS NULL
334 and n.snapshot_id = x.snapshot_id and n.virtual_patch_id = x.virtual_patch_id)
335 where x.request_id = request_id__
336 and EXISTS (
337 select 1 from fnd_imp_monitor m
338 where
339 m.request_id IS NULL
340 and m.snapshot_id = x.snapshot_id and m.virtual_patch_id = x.virtual_patch_id
341 and DECODE(stage__,'1',m.r1_sync_date,m.r2_sync_date) >= lastupdate(x.snapshot_id, x.virtual_patch_id)
342 );
343 commit;
344 logstats(request_id__, '2');
345 commit;
346
347 OPEN bugsnaps2;
348 LOOP
349 FETCH bugsnaps2 into snapshot_id__, virtual_patch_id__, set_type__, psmaster2_sz__;
350 EXIT WHEN bugsnaps2%NOTFOUND;
351 select sysdate into time__ from dual;
352 refresh2(virtual_patch_id__, snapshot_id__);
353
354 if psmaster2_sz__ = 0 then
355 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> No Patch Metadata found for patch: '||virtual_patch_id__);
356 end if;
357
358 -- update global record
359 update fnd_imp_monitor set r2_requestor = request_id__, r2_sync_date = time__, last_update_date = time__
360 where request_id IS NULL and snapshot_id = snapshot_id__
361 and set_type = set_type__ and virtual_patch_id = virtual_patch_id__;
362 -- update local record
363 update fnd_imp_monitor set r2_sync_date = time__, last_update_date = time__
364 where request_id = request_id__ and snapshot_id = snapshot_id__
365 and set_type = set_type__ and virtual_patch_id = virtual_patch_id__;
366 commit;
367 END LOOP;
368 CLOSE bugsnaps2;
369 end rsync2;
370
371 PROCEDURE refresh(request_id__ INTEGER, stage__ CHAR) IS
372 patchlist__ ad_patch_impact_api.t_recomm_patch_tab; --todo
373 BEGIN
374 -- dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')||'> master running['||request_id__||' stage='||stage__||']...');
375 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> master running['||request_id__||' stage='||stage__||']...');
376
377 if(stage__ = '1') then
378 rsync1(request_id__);
379 end if;
380
381 if(stage__ = '2') then
382 rsync2(request_id__);
383 end if;
384
385 -- Aggregate Patch Impact for request_id -- all patches in request
386 if(stage__ = '3') then
387 ad_patch_impact_api.get_recommend_patch_list(patchlist__);
388 set_aggregate_list(request_id__, patchlist__);
389 aggregate_patches(request_id__);
390 end if;
391
392 -- dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')||'> master completed '||request_id__||' stage='||stage__);
393 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> master completed '||request_id__||' stage='||stage__);
394 END refresh;
395
396 PROCEDURE aggregate_patches(request_id__ INTEGER) IS
397 snapshot_id__ NUMBER;
398 BEGIN
399 --compute_prereqs(request_id__); --ang we donot deal w/ prereqs as AD is not supporting prereq analysis at the moment
400 ad_patch_impact_api.get_global_snapshot_id(snapshot_id__);
401 refresh1M(request_id__, 0-request_id__, snapshot_id__);
402 refresh2(0-request_id__, snapshot_id__);
403 update fnd_imp_bugset
404 set set_type = 'a'
405 where request_id = request_id__
406 and virtual_patch_id = 0-request_id__;
407 commit;
408 END aggregate_patches;
409
410 FUNCTION is_aggregate_running(request_id__ INTEGER)
411 RETURN VARCHAR2 IS
412 is_running__ VARCHAR2(1);
413 BEGIN
414 select 'Y' into is_running__
415 from fnd_imp_bugset
416 where request_id = request_id__
417 and virtual_bug_no = 0-request_id__
418 and set_type = 'A'
419 and rownum = 1;
420 return is_running__;
421 END;
422
423 FUNCTION get_aggregate_list(request_id__ INTEGER)
424 RETURN VARCHAR2 IS
425 CURSOR agg_list IS
426 select patch_id
427 from fnd_imp_bugset
428 where virtual_patch_id = 0-request_id__ and request_id = request_id__
429 and set_type = 'a' and patch_order = 1;
430 tmp1 agg_list%ROWTYPE;
431 --buglist__ VARCHAR2(4000);
432 patchlist__ VARCHAR2(4000);
433 BEGIN
434 OPEN agg_list;
435 LOOP
436 FETCH agg_list into tmp1;
437 EXIT WHEN agg_list%NOTFOUND;
438 patchlist__ := patchlist__ || tmp1.patch_id || ',';
439 END LOOP;
440 CLOSE agg_list;
441 return patchlist__;
442 END get_aggregate_list;
443
444 PROCEDURE set_aggregate_list(request_id__ INTEGER, buglist__ ad_patch_impact_api.t_rec_patch) IS
445 i INTEGER;
446 snapshot_id__ NUMBER;
447 bug_no INTEGER;
448 BEGIN
449 ad_patch_impact_api.get_global_snapshot_id(snapshot_id__);
450 delete from fnd_imp_bugset
451 where request_id = request_id__ and (set_type = 'A' or set_type = 'a') and snapshot_id = snapshot_id__;
452 for i in 1..buglist__.COUNT
453 loop
454 bug_no := buglist__(i);
455 -- for each top-level patch in request, enter record in bugset
456 insert into fnd_imp_bugset(request_id, snapshot_id, set_type, virtual_bug_no, bug_no, patch_order,
457 creation_date, last_update_date, last_updated_by, created_by)
458 values(request_id__, snapshot_id__, 'A', 0-request_id__, bug_no,
459 1, sysdate, sysdate, -1, -1);
460 end loop;
461 commit;
462 END set_aggregate_list;
463
464 PROCEDURE set_aggregate_list(request_id__ INTEGER, patchlist__ ad_patch_impact_api.t_recomm_patch_tab) IS
465 i INTEGER;
466 snapshot_id__ NUMBER;
467 patch_id INTEGER;
468 BEGIN
469 ad_patch_impact_api.get_global_snapshot_id(snapshot_id__);
470 delete from fnd_imp_bugset
471 where request_id = request_id__ and (set_type = 'A' or set_type = 'a') and snapshot_id = snapshot_id__;
472 for i in 1..patchlist__.COUNT
473 loop
474 patch_id := patchlist__(i).patch_id;
475 -- for each top-level patch in request, enter record in bugset
476 insert into fnd_imp_bugset(request_id, snapshot_id, set_type, virtual_patch_id, patch_id, patch_order,
477 creation_date, last_update_date, last_updated_by, created_by)
478 values(request_id__, snapshot_id__, 'A', 0-request_id__, patch_id,
479 1, sysdate, sysdate, -1, -1);
480 end loop;
481 commit;
482 END set_aggregate_list;
483
484 --ang commenting out the below method as prereq analysis is currently not supported by AD team
485 /*PROCEDURE compute_prereqs(request_id__ INTEGER) IS
486 CURSOR top_level_bug IS
487 select bug_no
488 from fnd_imp_bugset
489 where set_type = 'A' and patch_order = 1 and request_id = request_id__;
490 i INTEGER;
491 snapshot_id__ NUMBER;
492 prereq_bug_no INTEGER;
493 prereq_buglist__ ad_patch_impact_api.t_prereq_patch;
494 tmp1 top_level_bug%ROWTYPE;
495 BEGIN
496 ad_patch_impact_api.get_global_snapshot_id(snapshot_id__);
497 OPEN top_level_bug;
498 LOOP
499 FETCH top_level_bug into tmp1;
500 EXIT WHEN top_level_bug%NOTFOUND;
501 ad_patch_impact_api.get_prereq_list(request_id__, tmp1.bug_no, prereq_buglist__);
502 for i in 1..prereq_buglist__.COUNT
503 loop
504 prereq_bug_no := prereq_buglist__(i);
505 -- for each pre-req patch in request, enter record in bugset
506 -- todo: avoid duplicates
507 insert into fnd_imp_bugset(request_id, snapshot_id, set_type, virtual_bug_no, bug_no, patch_order,
508 creation_date, last_update_date, last_updated_by, created_by)
509 values(request_id__, snapshot_id__, 'A', 0-request_id__, prereq_bug_no,
510 2, sysdate, sysdate, -1, -1);
511 end loop;
512 commit;
513 END LOOP;
514 CLOSE top_level_bug;
515 END compute_prereqs;*/
516
517 PROCEDURE wipedata(patch_id__ INTEGER, snapshot_id__ INTEGER) IS
518 cnt NUMBER;
519 BEGIN
520 SELECT COUNT(*) INTO cnt FROM fnd_imp_monitor where request_id IS NULL and snapshot_id = snapshot_id__ and virtual_patch_id = patch_id__;
521 delete from fnd_imp_monitor where request_id IS NULL and snapshot_id = snapshot_id__ and virtual_patch_id = patch_id__; commit;
522 SELECT COUNT(*) INTO cnt from FND_IMP_PFileInfo where patch_id = patch_id__;
523 delete from FND_IMP_PFileInfo where patch_id = patch_id__; commit;
524 SELECT COUNT(*) INTO cnt from FND_IMP_PFileInfo2 where patch_id = patch_id__;
525 delete from FND_IMP_PFileInfo2 where patch_id = patch_id__; commit;
526 SELECT COUNT(*) INTO cnt from FND_IMP_PSCommon where patch_id = patch_id__ and snapshot_id = snapshot_id__;
527 delete from FND_IMP_PSCommon where patch_id = patch_id__ and snapshot_id = snapshot_id__; commit;
528 SELECT COUNT(*) INTO cnt from FND_IMP_PSNew where patch_id = patch_id__ and snapshot_id = snapshot_id__;
529 delete from FND_IMP_PSNew where patch_id = patch_id__ and snapshot_id = snapshot_id__; commit;
530 BEGIN
531 SELECT COUNT(*) INTO cnt from fnd_imp_menu_dep_summary2 where patch_id = patch_id__ and snapshot_id = snapshot_id__;
532 delete from fnd_imp_menu_dep_summary2 where patch_id = patch_id__ and snapshot_id = snapshot_id__;
533 SELECT COUNT(*) INTO cnt from fnd_imp_menu_dep_summary3 where patch_id = patch_id__ and snapshot_id = snapshot_id__;
534 delete from fnd_imp_menu_dep_summary3 where patch_id = patch_id__ and snapshot_id = snapshot_id__;
535 SELECT COUNT(*) INTO cnt from FND_IMP_PISummary where patch_id = patch_id__ and snapshot_id = snapshot_id__;
536 delete from FND_IMP_PISummary where patch_id = patch_id__ and snapshot_id = snapshot_id__;
537 commit;
538 SELECT COUNT(*) INTO cnt from FND_IMP_PSMaster2 where patch_id = patch_id__ and snapshot_id = snapshot_id__;
539 delete from FND_IMP_PSMaster2 where patch_id = patch_id__ and snapshot_id = snapshot_id__;
540 SELECT COUNT(*) INTO cnt from FND_IMP_AffectedFiles where patch_id = patch_id__ and snapshot_id = snapshot_id__;
541 delete from FND_IMP_AffectedFiles where patch_id = patch_id__ and snapshot_id = snapshot_id__;
542 SELECT COUNT(*) INTO cnt from FND_IMP_DiagMap where patch_id = patch_id__ and snapshot_id = snapshot_id__;
543 delete from FND_IMP_DiagMap where patch_id = patch_id__ and snapshot_id = snapshot_id__;
544 commit;
545 end;
546 END wipedata;
547
548 PROCEDURE refresh1(patch_id__ INTEGER, snapshot_id__ INTEGER) IS
549 BEGIN
550 -- dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')||'> running['||bug_no__||','||snapshot_id__||']...');
551 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> running['||patch_id__||','||snapshot_id__||']...');
552 delete from FND_IMP_PFileInfo where patch_id = patch_id__; commit; --ang
553 delete from FND_IMP_PFileInfo2 where patch_id = patch_id__; commit;
554 delete from FND_IMP_PSCommon where patch_id = patch_id__ and snapshot_id = snapshot_id__; commit;
555 delete from FND_IMP_PSNew where patch_id = patch_id__ and snapshot_id = snapshot_id__; commit;
556
557 insert into FND_IMP_PFileInfo (select * from FND_IMP_PFileInfo_VL where patch_id = patch_id__); commit;
558 insert into FND_IMP_PFileInfo2 (select * from FND_IMP_PFileInfo2_VL where patch_id = patch_id__); commit;
559 insert into FND_IMP_PSCommon (select * from FND_IMP_PSCommon_VL where patch_id = patch_id__ and snapshot_id = snapshot_id__); commit;
560 insert into FND_IMP_PSNew (select * from FND_IMP_PSNew_VL where patch_id = patch_id__ and snapshot_id = snapshot_id__); commit;
561
562 begin
563 delete from FND_IMP_PSMaster2 where patch_id = patch_id__ and snapshot_id = snapshot_id__;
564 -- delete from FND_IMP_AffectedFiles where bug_no = bug_no__ and snapshot_id = snapshot_id__;
565 -- delete from FND_IMP_DiagMap where bug_no = bug_no__ and snapshot_id = snapshot_id__;
566 commit;
567 insert into FND_IMP_PSMaster2 (select * from FND_IMP_PSMaster2_VL where patch_id = patch_id__ and snapshot_id = snapshot_id__);
568
569 -- Moving this logic to Analyze Impact 2 (i.e. refresh2)
570 /*
571 insert into FND_IMP_AffectedFiles (select * from FND_IMP_AffectedFiles_VL where bug_no = bug_no__ and snapshot_id = snapshot_id__);
572 insert into FND_IMP_DiagMap (select * from FND_IMP_DiagMap_VL where bug_no = bug_no__ and snapshot_id = snapshot_id__);
573 */
574 commit;
575 end;
576 -- dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')||'> completed');
577 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> completed');
578 END refresh1;
579
580 PROCEDURE refresh2(patch_id__ INTEGER, snapshot_id__ INTEGER) IS
581 BEGIN
582 -- dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')||'> running['||bug_no__||','||snapshot_id__||']...');
583 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> running['||patch_id__||','||snapshot_id__||']...');
584
585 begin
586 -- Adding this from refresh1/refresh1M
587 delete from FND_IMP_AffectedFiles where patch_id = patch_id__ and snapshot_id = snapshot_id__;
588 delete from FND_IMP_DiagMap where patch_id = patch_id__ and snapshot_id = snapshot_id__;
589
590 delete from fnd_imp_menu_dep_summary2 where patch_id = patch_id__ and snapshot_id = snapshot_id__;
591 delete from fnd_imp_menu_dep_summary3 where patch_id = patch_id__ and snapshot_id = snapshot_id__;
592 delete from FND_IMP_PISummary where patch_id = patch_id__ and snapshot_id = snapshot_id__;
593
594 -- Adding this from refresh1/refresh1M - Moved it to this stage from
595 -- first stage
596 insert into FND_IMP_AffectedFiles (select * from FND_IMP_AffectedFiles_VL where patch_id = patch_id__ and snapshot_id = snapshot_id__);
597 insert into FND_IMP_DiagMap (select * from FND_IMP_DiagMap_VL where patch_id = patch_id__ and snapshot_id = snapshot_id__);
598
599 insert into fnd_imp_menu_dep_summary2 (select * from fnd_imp_menu_dep_summary2_vl where patch_id = patch_id__ and snapshot_id = snapshot_id__);
600 insert into fnd_imp_menu_dep_summary3 (select * from fnd_imp_menu_dep_summary3_vl where patch_id = patch_id__ and snapshot_id = snapshot_id__);
601 insert into FND_IMP_PISummary
602 (select * from FND_IMP_PISummary_VL
603 where patch_id = patch_id__ and snapshot_id = snapshot_id__);
604 commit;
605
606 update fnd_imp_psmaster2 p
607 set files_affected =
608 (
609 select count(*)
610 from fnd_imp_affectedfiles f
611 where p.patch_id = f.patch_id
612 and p.snapshot_id = f.snapshot_id
613 and p.app_short_name = f.patched_app_short_name
614 and p.directory = f.patched_directory
615 and p.filename = f.patched_filename
616 )
617 where patch_id=patch_id__
618 and snapshot_id=snapshot_id__
619 and typeid <> 'not applied'
620 and file_type = 'jsp';
621
622 update fnd_imp_psmaster2
623 set type_affected='jsp'
624 where patch_id=patch_id__
625 and snapshot_id=snapshot_id__
626 and typeid <> 'not applied'
627 and files_affected<>0
628 and file_type = 'jsp';
629
630 update fnd_imp_psmaster2 p
631 set files_affected=(select count(*)
632 from fnd_imp_menu_dep_summary2 s
633 where p.patch_id=s.patch_id
634 and p.snapshot_id=s.snapshot_id
635 and p.filename=s.form_name)
636 where patch_id=patch_id__
637 and snapshot_id=snapshot_id__
638 and typeid <> 'not applied'
639 and file_type = 'fmb';
640
641 update fnd_imp_psmaster2 p
642 set type_affected='menu'
643 where patch_id=patch_id__
644 and snapshot_id=snapshot_id__
645 and typeid <> 'not applied'
646 and file_type = 'fmb'
647 and files_affected <> 0;
648
649 update fnd_imp_psmaster2 p
650 set files_affected=(select count(*)
651 from jtf_diagnostic_cmap s
652 where p.trans_name=s.classname)
653 where patch_id=patch_id__
654 and snapshot_id=snapshot_id__
655 and typeid <> 'not applied'
656 and file_type = 'class';
657
658 update fnd_imp_psmaster2 p
659 set type_affected='diag'
660 where patch_id=patch_id__
661 and snapshot_id=snapshot_id__
662 and typeid <> 'not applied'
663 and file_type = 'class'
664 and files_affected <> 0;
665
666 update fnd_imp_affectedfiles p
667 set objects_affected=(select count(*)
668 from fnd_imp_menu_dep_summary2 s
669 where p.patch_id=s.patch_id
670 and p.snapshot_id=s.snapshot_id
671 and p.dep_filename=s.form_name)
672 where patch_id=patch_id__
673 and snapshot_id=snapshot_id__
674 and typeid <> 'not applied';
675
676 update fnd_imp_affectedfiles p
677 set object_type='menu'
678 where patch_id=patch_id__
679 and snapshot_id=snapshot_id__
680 and typeid <> 'not applied'
681 and objects_affected <> 0;
682
683 commit;
684 end;
685 -- dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')||'> completed');
686 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> completed');
687 END refresh2;
688
689 --
690 -- Procedure to compute language impact
691 --
692 PROCEDURE compute_language_impact (
693 request_id__ INTEGER)
694 IS
695 snapshot_id__ NUMBER;
696 cursor c_bugs(v_req_id number, v_snapshot_id number) IS
697 select virtual_bug_no
698 from fnd_imp_monitor
699 where set_type in ('s','S')
700 and request_id = v_req_id
701 and snapshot_id = v_snapshot_id;
702 cursor M_bugs(v_req_id number, v_snapshot_id number) IS
703 select virtual_patch_id
704 from fnd_imp_monitor
705 where set_type = 'M'
706 and request_id = v_req_id
707 and snapshot_id = v_snapshot_id;
708 BEGIN
709 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> computing language impact['||request_id__||']...');
710
711 ad_patch_impact_api.get_global_snapshot_id(snapshot_id__);
712
713 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> snapshot_id['||snapshot_id__||']...');
714
715 -- Cleanup any records associated with this request_id
716 delete from fnd_imp_lang_summary l
717 where l.snapshot_id = snapshot_id__
718 and patch_id in (
719 select m.virtual_patch_id
720 from fnd_imp_monitor m
721 where m.set_type in ('s','S','M')
722 and m.request_id = request_id__
723 and m.snapshot_id = l.snapshot_id
724 union
725 select 0-request_id__ from dual);
726
727 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||' cleaned up fnd_imp_lang_summary');
728
729
730 -- 1) First record for each singletons (top level or pre-reqs)
731 -- whether translation patch is required with count as 1.
732 for v_bug in c_bugs(request_id__,snapshot_id__) loop
733 declare
734 v_req_trans number := 0;
735 begin
736 select decode(count(fcv.file_type),0,0,1) into v_req_trans
737 from fnd_imp_filetypecount_vl fcv
738 where lower(fcv.file_type) in
739 --('fmb','fmx','rdf','ldt','ildt','jtl','msg','msb','res')
740 (select lower(lu.lookup_code) from fnd_lookups lu
741 where lu.lookup_type = 'OAM_PIA_TRANS_FILE_TYPES')
742 and ( fcv.upgrade > 0 or fcv.new > 0 )
743 and fcv.snapshot_id = snapshot_id__
744 and fcv.bug_no = v_bug.virtual_bug_no;
745 insert into fnd_imp_lang_summary (
746 snapshot_id, patch_id, req_trans_cnt) values
747 (snapshot_id__, v_bug.virtual_bug_no, v_req_trans);
748 exception
749 when no_data_found then
750 null;
751 end;
752 end loop;
753
754 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||' processed top levels and prerequisites (S,s)');
755
756 -- 2) Now aggregate for each Multi-set (M) the sum of singletons
757 -- for which translation patch is required.
758 for v_bug in M_bugs(request_id__,snapshot_id__) loop
759 declare
760 v_req_trans number := 0;
761 begin
762 select sum(req_trans_cnt) into v_req_trans from
763 ( -- get all the prequisites
764 select ls.req_trans_cnt req_trans_cnt
765 from fnd_imp_bugset b, fnd_imp_lang_summary ls
766 where ls.patch_id = b.patch_id
767 and ls.snapshot_id = b.snapshot_id
768 and b.virtual_patch_id = v_bug.virtual_patch_id
769 and b.set_type = 'm'
770 and b.snapshot_id = snapshot_id__
771 union all
772 -- combine with the top level
773 select ls.req_trans_cnt req_trans_cnt
774 from fnd_imp_lang_summary ls
775 where ls.patch_id = (0 - v_bug.virtual_patch_id)
776 );
777 insert into fnd_imp_lang_summary (
778 snapshot_id, patch_id, req_trans_cnt) values
779 (snapshot_id__, v_bug.virtual_patch_id, v_req_trans);
780 exception
781 when no_data_found then
782 null;
783 end;
784 end loop;
785
786 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||' processed top level and prerequisite aggregation (M)');
787
788 -- 3) Now aggregate for ad-hoc aggregation (a) to be the sum of
789 -- all top-levels (S and M) - and of course avoiding double
790 -- counting. This step is only applicable if we're in
791 -- aggregate impact mode.
792 declare
793 v_req_trans number := 0;
794 begin
795 select sum(ls.req_trans_cnt) into v_req_trans
796 from fnd_imp_bugset b, fnd_imp_lang_summary ls
797 where ls.patch_id = b.patch_id
798 and ls.snapshot_id = b.snapshot_id
799 and b.virtual_patch_id = (0-request_id__)
800 and b.set_type = 'a'
801 and b.snapshot_id = snapshot_id__
802 group by b.virtual_patch_id;
803 insert into fnd_imp_lang_summary (
804 snapshot_id, patch_id, req_trans_cnt) values
805 (snapshot_id__, (0-request_id__), v_req_trans);
806 exception
807 when no_data_found then
808 null;
809 end;
810 commit;
811
812 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||' processed aggregation (M)');
813
814 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> Done computing language impact['||request_id__||','||snapshot_id__||']...');
815 END compute_language_impact;
816
817 PROCEDURE refreshCP(
818 ERRBUF OUT NOCOPY VARCHAR2,
819 RETCODE OUT NOCOPY NUMBER
820 ) IS
821 request_id INTEGER;
822 no_request_id_found EXCEPTION;
823 BEGIN
824 fnd_file.put_line(fnd_file.log, 'FND_IMP_PKG.refreshCP: running');
825 select fcr.request_id into request_id
826 from fnd_application fa, fnd_concurrent_requests fcr, fnd_concurrent_programs fcp
827 where fcr.priority_request_id = fnd_global.conc_priority_request
828 and fcr.program_application_id = fcp.application_id
829 and fcp.application_id = fa.application_id
830 and fcr.concurrent_program_id = fcp.concurrent_program_id
831 and fa.application_short_name = 'AD'
832 and fcp.concurrent_program_name IN ('PATCHANALYSIS', 'PAANALYSIS', 'PADOWNLOADPATCHES', 'PAANALYZEPATCHES', 'PARECOMMENDPATCHES');
833 fnd_file.put_line(fnd_file.log, 'FND_IMP_PKG.refreshCP: request id = '||request_id);
834 refresh(request_id, '1');
835
836 EXCEPTION
837 WHEN OTHERS
838 THEN
839 RETCODE := 2;
840 ERRBUF := sqlcode||':'||sqlerrm;
841 fnd_file.put_line(fnd_file.log, 'FND_IMP_PKG.refreshCP failed ' || sqlcode||':'||sqlerrm);
842 END refreshCP;
843
844 PROCEDURE refreshCP2(
845 ERRBUF OUT NOCOPY VARCHAR2,
846 RETCODE OUT NOCOPY NUMBER
847 ) IS
848 request_id INTEGER;
849 no_request_id_found EXCEPTION;
850 BEGIN
851 fnd_file.put_line(fnd_file.log, 'FND_IMP_PKG.refreshCP2: running');
852 select fcr.request_id into request_id
853 from fnd_application fa, fnd_concurrent_requests fcr, fnd_concurrent_programs fcp
854 where fcr.priority_request_id = fnd_global.conc_priority_request
855 and fcr.program_application_id = fcp.application_id
856 and fcp.application_id = fa.application_id
857 and fcr.concurrent_program_id = fcp.concurrent_program_id
858 and fa.application_short_name = 'AD'
859 and fcp.concurrent_program_name IN ('PATCHANALYSIS', 'PAANALYSIS', 'PADOWNLOADPATCHES', 'PAANALYZEPATCHES', 'PARECOMMENDPATCHES');
860 fnd_file.put_line(fnd_file.log, 'FND_IMP_PKG.refreshCP2: request id = '||request_id);
861 refresh(request_id, '2');
862 compute_language_impact(request_id);
863 EXCEPTION
864 WHEN OTHERS
865 THEN
866 RETCODE := 2;
867 ERRBUF := sqlcode||':'||sqlerrm;
868 fnd_file.put_line(fnd_file.log, 'FND_IMP_PKG.refreshCP2 failed ' || sqlcode||':'||sqlerrm);
869 END refreshCP2;
870
871 PROCEDURE refreshCPAgg(
872 ERRBUF OUT NOCOPY VARCHAR2,
873 RETCODE OUT NOCOPY NUMBER,
874 request_id__ IN INTEGER
875 ) IS
876 request_id INTEGER;
877 no_request_id_found EXCEPTION;
878 BEGIN
879 fnd_file.put_line(fnd_file.log, 'FND_IMP_PKG.refreshCPAgg: running');
880 if (request_id__ is null) then
881 select fcr.request_id into request_id
882 from fnd_application fa, fnd_concurrent_requests fcr, fnd_concurrent_programs fcp
883 where fcr.priority_request_id = fnd_global.conc_priority_request
884 and fcr.program_application_id = fcp.application_id
885 and fcp.application_id = fa.application_id
886 and fcr.concurrent_program_id = fcp.concurrent_program_id
887 and fa.application_short_name = 'AD'
888 and fcp.concurrent_program_name IN ('PATCHANALYSIS', 'PAANALYSIS', 'PADOWNLOADPATCHES', 'PAANALYZEPATCHES', 'PARECOMMENDPATCHES');
889 fnd_file.put_line(fnd_file.log, 'FND_IMP_PKG.refreshCPAgg: request id = '||request_id);
890 refresh(request_id, '3');
891 compute_language_impact(request_id);
892 else
893 if (request_id__ >= 0) then
894 aggregate_patches(request_id__);
895 compute_language_impact(request_id);
896 end if;
897 end if;
898
899 EXCEPTION
900 WHEN OTHERS
901 THEN
902 RETCODE := 2;
903 ERRBUF := sqlcode||':'||sqlerrm;
904 fnd_file.put_line(fnd_file.log, 'FND_IMP_PKG.refreshCPAgg failed ' || sqlcode||':'||sqlerrm);
905 END refreshCPAgg;
906
907 PROCEDURE sync(table_name VARCHAR2) IS
908 BEGIN
909 execute immediate 'truncate table '||table_name;
910 -- BVS-OK (never using user-given table_name)
911 execute immediate 'insert into '||table_name||' (select * from '||table_name||'_VL)';
912 -- dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')||'> synced '||table_name);
913 END sync;
914
915 PROCEDURE wipe(table_name VARCHAR2) IS
916 BEGIN
917 execute immediate 'truncate table '||table_name;
918 END wipe;
919
920 PROCEDURE refreshAll IS
921 BEGIN
922 -- dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')||'> running...');
923 wipe('FND_IMP_PFileInfo');
924 wipe('FND_IMP_PFileInfo2');
925 wipe('FND_IMP_PSCommon');
926 wipe('FND_IMP_PSNew');
927 wipe('FND_IMP_PSMaster2');
928 wipe('FND_IMP_PISummary');
929 wipe('FND_IMP_AffectedFiles');
930 -- dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')||'> cleaned data');
931
932 sync('FND_IMP_PFileInfo'); commit;
933 sync('FND_IMP_PFileInfo2'); commit;
934 sync('FND_IMP_PSCommon'); commit;
935 sync('FND_IMP_PSNew'); commit;
936 sync('FND_IMP_PSMaster2');
937 sync('FND_IMP_PISummary'); commit;
938 sync('FND_IMP_AffectedFiles'); commit;
939 -- dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')||'> completed');
940 commit;
941
942 END refreshAll;
943
944 --
945 -- API to determine if there is at least one new or modified file of the
946 -- given type in the set of patches being analyzed in this request.
947 --
948 FUNCTION isFileTypeAffected(request_id__ INTEGER, snapshot_id__ INTEGER, filetype__ VARCHAR2)
949 RETURN VARCHAR2 IS
950 v_return varchar2(1) := 'N';
951
952 cursor c_bugs(v_req_id number, v_snapshot_id number) IS
953 select virtual_bug_no
954 from fnd_imp_monitor
955 where set_type in ('s','S')
956 and request_id = v_req_id
957 and snapshot_id = v_snapshot_id;
958 BEGIN
959 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> FND_IMP_PKG.isFileTypeAffected IN');
960
961 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> computing file type affected for file type ' || filetype__ || '['||request_id__||']...');
962
963 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> snapshot_id['||snapshot_id__||']...');
964
965 -- For each top level bug and pre-reqs, determine if the given
966 -- file type is affected.
967 for v_bug in c_bugs(request_id__,snapshot_id__) loop
968
969 begin
970 select 'Y' into v_return
971 from fnd_imp_filetypecount_vl fcv
972 where lower(fcv.file_type) = lower(filetype__)
973 and ( fcv.upgrade > 0 or fcv.new > 0 )
974 and fcv.snapshot_id = snapshot_id__
975 and fcv.bug_no = v_bug.virtual_bug_no;
976 if (v_return = 'Y') then
977 exit; -- we're done as soon as we find the first bug
978 end if;
979 exception
980 when no_data_found then
981 null;
982 end;
983 end loop;
984
985 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> v_return:'||v_return);
986
987 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> FND_IMP_PKG.isFileTypeAffected OUT');
988 return v_return;
989 END isFileTypeAffected;
990
991 END FND_IMP_PKG;
[Home] [Help]