1 package Body ad_pa_criteria_util as
2 /* $Header: adpasedb.pls 120.2 2007/03/01 04:41:31 vsigamal ship $ */
3 -- This procedure is to seed data for Recommended Patches in ad_pa_criteria and ad_pa_criteria_prod_fams table
4 procedure update_rp
5 is
6 l_advisor_criteria_id ad_pa_criteria.ADVISOR_CRITERIA_ID%TYPE;
7 l_pre_seeded ad_pa_criteria.pre_seeded_flag%TYPE;
8 l_last_update_date date;
9 l_created_by number;
10 begin
11 l_last_update_date := sysdate;
12 -- This package is called by Autopatch program when AD-OAM Module is being installed
13 -- Check if this criteria set (criteria_id and pre_seeded flag) exists
14 -- in ad_pa_advisor_criteria (create if not exists)
15 --
16 -- Then delete rows from ad_pa_criteria_prod_fams.
17 --
18 -- For all products in FND_PRODUCT_INSTALLATIONS with status='I' and 'S'
19 -- create row in ad_pa_criteria_prod_fams for this
20 -- criteria set and product Family with Recommended Patches flag and licensed flag set Y and other flags N
21 --
22 -- For all products in FND_PRODUCT_INSTALLATIONS with status != 'I' and 'S'
23 -- create row in ad_pa_criteria_prod_fams for this
24 -- criteria set and product Family with Recommended Patches flag set Y and other flags N including licensed flag
25 --
26 -- Change last_update_date for this criteria set in ad_pa_advisor_criteria
27 --
28 begin
29 select advisor_criteria_id, pre_seeded_flag, created_by into l_advisor_criteria_id,
30 l_pre_seeded, l_created_by
31 from AD_PA_CRITERIA
32 where advisor_criteria_id ='Recommended Patches'
33 and pre_seeded_flag = 'Y';
34 --dbms_output.put_line('before exception');
35
36 if (l_created_by <> 2) then
37 update AD_PA_CRITERIA
38 set created_by = 2, last_updated_by = 2
39 where advisor_criteria_id ='Recommended Patches'
40 and pre_seeded_flag = 'Y';
41 end if;
42 exception
43 when no_data_found then
44
45 insert into AD_PA_CRITERIA
46 (advisor_criteria_id, advisor_criteria_description,pre_seeded_flag,
47 creation_date,last_update_date,last_updated_by,created_by)
48 values('Recommended Patches','Recommended Patches for Current Codelevel','Y',sysdate,sysdate,2,2);
49
50 end;
51
52
53 begin
54 delete from ad_pa_criteria_prod_fams
55 where advisor_criteria_id = 'Recommended Patches';
56
57 insert into ad_pa_criteria_prod_fams
58 (PRODUCT_FAMILY_ABBREVIATION,ADVISOR_CRITERIA_ID,RECOMMENDED_PATCH_FLAG,NEW_CODE_LEVEL_FLAG,LICENSED_FLAG,
59 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
60 select distinct pfm.product_family_abbreviation,'Recommended Patches','Y','N','Y',sysdate,1,sysdate,1
61 from fnd_product_installations fpi, fnd_application a,
62 ad_pm_product_info pi, ad_pm_prod_family_map pfm
63 where fpi.status in ('S', 'I')
64 and fpi.application_id = a.application_id
65 and a.application_short_name= pi.application_short_name
66 and pi.PSEUDO_PRODUCT_FLAG='N'
67 and pi.product_abbreviation = pfm.product_abbreviation ;
68
69
70 begin
71 insert into ad_pa_criteria_prod_fams
72 (PRODUCT_FAMILY_ABBREVIATION,ADVISOR_CRITERIA_ID,RECOMMENDED_PATCH_FLAG,NEW_CODE_LEVEL_FLAG,LICENSED_FLAG,
73 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
74 select distinct pi.product_abbreviation,'Recommended Patches', 'Y', 'N', 'N',sysdate, 1, sysdate, 1
75 from ad_pm_product_info pi
76 where pi.pseudo_product_flag='Y'
77 and pi.product_family_flag='Y'
78 and not exists
79 (select 'X' from ad_pa_criteria_prod_fams adf
80 where adf.product_family_abbreviation= pi.product_abbreviation
81 and adf.advisor_criteria_id='Recommended Patches');
82 end;
83
84 BEGIN
85 DECLARE
86 cursor prod_family is
87 select pi.PRODUCT_FAMILY_ABBREVIATION adi_prod_abbr, pfm.product_family_abbreviation adp_prod_abbr from ad_pm_product_info pi, ad_pa_criteria_prod_fams pfm
88 where pfm.PRODUCT_FAMILY_ABBREVIATION = pi.PRODUCT_FAMILY_ABBREVIATION
89 and pfm.ADVISOR_CRITERIA_ID ='Recommended Patches' for update of pfm.product_family_abbreviation;
90 prod_abb prod_family%ROWTYPE;
91 begin
92 for prod_abb in prod_family
93 loop
94 update ad_pa_criteria_prod_fams
95 set product_family_abbreviation = prod_abb.adi_prod_abbr
96 where current of prod_family;
97 end loop;
98 end;
99 end;
100
101 exception
102 when dup_val_on_index then
103 raise_application_error(-20001,
104 'Attempting to insert a duplicate record '||
105 'into AD_PA_CRITERIA_PROD_FAMS advisor_criteria_id = '
106 || l_advisor_criteria_id || ' and pre_seeded_flag '||
107 l_pre_seeded);
108 end;
109
110 update ad_pa_criteria
111 set last_update_date = l_last_update_date
112 where advisor_criteria_id ='Recommended Patches';
113 end update_rp;
114
115 -- This procedure is to seed data for New Code Levels in ad_pa_criteria and ad_pa_criteria_prod_fams table
116 procedure update_nc
117 is
118 l_advisor_criteria_id ad_pa_criteria.ADVISOR_CRITERIA_ID%TYPE;
119 l_pre_seeded ad_pa_criteria.pre_seeded_flag%TYPE;
120 l_last_update_date date;
121 l_created_by number;
122 begin
123 l_last_update_date := sysdate;
124 --
125 -- This package is called by Autopatch program when AD-OAM Module is being installed
126 -- Check if this criteria set (criteria_id and pre_seeded flag) exists
127 -- in ad_pa_advisor_criteria (create if not exists)
128 --
129 -- Then delete rows from ad_pa_criteria_prod_fams.
130 --
131 -- For all products in FND_PRODUCT_INSTALLATIONS with status='I' and 'S'
132 -- create row in ad_pa_criteria_prod_fams for this
133 -- criteria set and product Family with New Code Levels flag and licensed flag set Y and other flags N
134 --
135 -- For all products in FND_PRODUCT_INSTALLATIONS with status != 'I' and 'S'
136 -- create row in ad_pa_criteria_prod_fams for this
137 -- criteria set and product Family with New Code Levels flag set Y and other flags N including licensed flag
138 --
139 -- Change last_update_date for this criteria set in ad_pa_advisor_criteria
140 --
141 begin
142 select advisor_criteria_id, pre_seeded_flag, created_by into l_advisor_criteria_id,
143 l_pre_seeded, l_created_by
144 from AD_PA_CRITERIA
145 where advisor_criteria_id ='New Codelevels'
146 and pre_seeded_flag = 'Y';
147
148 if (l_created_by <> 2) then
149 update AD_PA_CRITERIA
150 set created_by = 2, last_updated_by = 2
151 where advisor_criteria_id ='New Codelevels'
152 and pre_seeded_flag = 'Y';
153 end if;
154
155 exception
156 when no_data_found then
157 insert into AD_PA_CRITERIA
158 (advisor_criteria_id, advisor_criteria_description,pre_seeded_flag,
159 creation_date,last_update_date,last_updated_by,created_by)
160 values('New Codelevels','New Codelevels','Y',sysdate,sysdate,2,2);
161 end;
162
163 begin
164 delete from ad_pa_criteria_prod_fams
165 where advisor_criteria_id = 'New Codelevels';
166
167 insert into ad_pa_criteria_prod_fams
168 (PRODUCT_FAMILY_ABBREVIATION,ADVISOR_CRITERIA_ID,RECOMMENDED_PATCH_FLAG,NEW_CODE_LEVEL_FLAG,LICENSED_FLAG,
169 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
170 select distinct pfm.product_family_abbreviation,'New Codelevels','N','Y','Y',sysdate,1,sysdate,1
171 from fnd_product_installations fpi, fnd_application a,
172 ad_pm_product_info pi, ad_pm_prod_family_map pfm
173 where fpi.status in ('S', 'I')
174 and fpi.application_id = a.application_id
175 and a.application_short_name= pi.application_short_name
176 and pi.PSEUDO_PRODUCT_FLAG='N'
177 and pi.product_abbreviation = pfm.product_abbreviation ;
178 begin
179 insert into ad_pa_criteria_prod_fams
180 (PRODUCT_FAMILY_ABBREVIATION,ADVISOR_CRITERIA_ID,RECOMMENDED_PATCH_FLAG,NEW_CODE_LEVEL_FLAG,LICENSED_FLAG,
181 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
182 select distinct pi.product_abbreviation,'New Codelevels', 'N', 'Y', 'N',sysdate, 1, sysdate, 1
183 from ad_pm_product_info pi
184 where pi.pseudo_product_flag='Y'
185 and pi.product_family_flag='Y'
186 and not exists
187 (select 'X' from ad_pa_criteria_prod_fams adf
188 where adf.product_family_abbreviation= pi.product_abbreviation
189 and adf.advisor_criteria_id='New Codelevels');
190 end;
191
192 BEGIN
193 DECLARE
194 cursor prod_family is
195 select adi.PRODUCT_FAMILY_ABBREVIATION adi_prod_abbr, adp.product_family_abbreviation adp_prod_abbr from ad_pm_product_info adi, ad_pa_criteria_prod_fams adp
196 where adp.PRODUCT_FAMILY_ABBREVIATION = adi.PRODUCT_FAMILY_ABBREVIATION
197 and adp.ADVISOR_CRITERIA_ID ='New Codelevels' for update of adp.product_family_abbreviation;
198 prod_abb prod_family%ROWTYPE;
199 begin
200 for prod_abb in prod_family
201 loop
202 update ad_pa_criteria_prod_fams
203 set product_family_abbreviation = prod_abb.adi_prod_abbr
204 where current of prod_family;
205 end loop;
206 end;
207 end;
208
209 exception
210 when dup_val_on_index then
211 raise_application_error(-20001,
212 'Attempting to insert a duplicate record '||
213 'into AD_PA_CRITERIA_PROD_FAMS advisor_criteria_id = '||
214 l_advisor_criteria_id || ' and pre_seeded_flag '||
215 l_pre_seeded);
216 end;
217
218 update ad_pa_criteria
219 set last_update_date = l_last_update_date
220 where advisor_criteria_id ='New Codelevels';
221 end update_nc;
222 -- This procedure is to seed data for Recommended Patches Plus New Code Levels in ad_pa_criteria and ad_pa_criteria_prod_fams table
223 procedure update_rpandnc
224 is
225 l_advisor_criteria_id ad_pa_criteria.ADVISOR_CRITERIA_ID%TYPE;
226 l_pre_seeded ad_pa_criteria.pre_seeded_flag%TYPE;
227 l_last_update_date date;
228 l_created_by number;
229 begin
230 l_last_update_date := sysdate;
231 --
232 -- This package is called by Autopatch program when AD-OAM Module is being installed
233 -- Check if this criteria set (criteria_id and pre_seeded flag) exists
234 -- in ad_pa_advisor_criteria (create if not exists)
235 --
236 -- Then delete rows from ad_pa_criteria_prod_fams.
237 --
238 -- For all products in FND_PRODUCT_INSTALLATIONS with status='I' and 'S'
239 -- create row in ad_pa_criteria_prod_fams for this
240 -- criteria set and product Family with Recommended Patches plus New Code Levels flag and licensed flag set Y and other flags N
241 --
242 -- For all products in FND_PRODUCT_INSTALLATIONS with status != 'I' and 'S'
243 -- create row in ad_pa_criteria_prod_fams for this
244 -- criteria set and product Family with Recommended Patches plus New Code Levels flag set Y and other flags N including licensed flag
245 --
246 -- Change last_update_date for this criteria set in ad_pa_advisor_criteria
247 --
248 begin
249 select advisor_criteria_id, pre_seeded_flag, created_by into l_advisor_criteria_id,
250 l_pre_seeded, l_created_by
251 from AD_PA_CRITERIA
252 where advisor_criteria_id ='Recommended Patches and New Codelevels'
253 and pre_seeded_flag = 'Y';
254
255 if (l_created_by <> 2) then
256 update AD_PA_CRITERIA
257 set created_by = 2, last_updated_by = 2
258 where advisor_criteria_id ='Recommended Patches and New Codelevels'
259 and pre_seeded_flag = 'Y';
260 end if;
261
262 exception
263 when no_data_found then
264 insert into AD_PA_CRITERIA
265 (advisor_criteria_id, advisor_criteria_description,pre_seeded_flag,
266 creation_date,last_update_date,last_updated_by,created_by)
267 values('Recommended Patches and New Codelevels','Current Recommended Patches and New Codelevels','Y',sysdate,sysdate,2,2);
268 end;
269
270 begin
271 delete from ad_pa_criteria_prod_fams
272 where advisor_criteria_id = 'Recommended Patches and New Codelevels';
273
274 insert into ad_pa_criteria_prod_fams
275 (PRODUCT_FAMILY_ABBREVIATION,ADVISOR_CRITERIA_ID,RECOMMENDED_PATCH_FLAG,NEW_CODE_LEVEL_FLAG,LICENSED_FLAG,
276 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
277 select distinct pfm.product_family_abbreviation,'Recommended Patches and New Codelevels','Y','Y','Y',sysdate,1,sysdate,1
278 from fnd_product_installations fpi, fnd_application a,
279 ad_pm_product_info pi, ad_pm_prod_family_map pfm
280 where fpi.status in ('S', 'I')
281 and fpi.application_id = a.application_id
282 and a.application_short_name= pi.application_short_name
283 and pi.PSEUDO_PRODUCT_FLAG='N'
284 and pi.product_abbreviation = pfm.product_abbreviation ;
285 begin
286 insert into ad_pa_criteria_prod_fams
287 (PRODUCT_FAMILY_ABBREVIATION,ADVISOR_CRITERIA_ID,RECOMMENDED_PATCH_FLAG,NEW_CODE_LEVEL_FLAG,LICENSED_FLAG,
288 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
289 select distinct pi.product_abbreviation,'Recommended Patches and New Codelevels', 'Y', 'Y', 'N',sysdate, 1, sysdate, 1
290 from ad_pm_product_info pi
291 where pi.pseudo_product_flag='Y'
292 and pi.product_family_flag='Y'
293 and not exists
294 (select 'X' from ad_pa_criteria_prod_fams adf
295 where adf.product_family_abbreviation= pi.product_abbreviation
296 and adf.advisor_criteria_id='Recommended Patches and New Codelevels');
297 end;
298
299 BEGIN
300 DECLARE
301 cursor prod_family is
302 select adi.PRODUCT_FAMILY_ABBREVIATION adi_prod_abbr, adp.product_family_abbreviation adp_prod_abbr from ad_pm_product_info adi, ad_pa_criteria_prod_fams adp
303 where adp.PRODUCT_FAMILY_ABBREVIATION = adi.PRODUCT_FAMILY_ABBREVIATION
304 and adp.ADVISOR_CRITERIA_ID ='Recommended Patches and New Codelevels' for update of adp.product_family_abbreviation;
305 prod_abb prod_family%ROWTYPE;
306 begin
307 for prod_abb in prod_family
308 loop
309 update ad_pa_criteria_prod_fams
310 set product_family_abbreviation = prod_abb.adi_prod_abbr
311 where current of prod_family;
312 end loop;
313 end;
314 end;
315
316 exception
317 when dup_val_on_index then
318 raise_application_error(-20001,
322 l_pre_seeded);
319 'Attempting to insert a duplicate record '||
320 'into AD_PA_CRITERIA_PROD_FAMS advisor_criteria_id = '||
321 l_advisor_criteria_id || ' and pre_seeded_flag '||
323 end;
324
325 update ad_pa_criteria
326 set last_update_date = l_last_update_date
327 where advisor_criteria_id ='Recommended Patches and New Codelevels';
328 end update_rpandnc;
329
330
331 procedure update_all
332 is
333
334 --
335 -- Create/update all three of the pre-seeded Criteria Sets
336 -- Delete rows from ad_pa_criteria and ad_pa_criteria_products for 11.5.9
337 x_statement varchar2(100);
338 G_UN_FND varchar2(30) := null;
339 l_stat varchar2(1) := null;
340 l_ind varchar2(1) := null;
341 l_ign boolean;
342
343 begin
344
345 x_statement := 'n';
346 begin
347
348 --Bug # 3407988 -This is to delete existing old advisor_criteria_id from the database
349 delete from ad_pa_criteria
350 where advisor_criteria_id = 'RecPatches and New Codelevels';
351 delete from ad_pa_criteria_prod_fams
352 where advisor_criteria_id = 'RecPatches and New Codelevels';
353 -- end of --Bug # 3407988
354
355 --vsigamal 10-Oct-2006 Bug # 5575432 - This is to delete existing old advisor_criteria_id from the database
356 delete from ad_pa_criteria_prod_fams
357 where advisor_criteria_id in ( select advisor_criteria_id
358 from ad_pa_criteria
359 where pre_seeded_flag='Y');
360 delete from ad_pa_criteria
361 where pre_seeded_flag='Y';
362
363 -- Get APPLSYS schema name (Bug 3871565).
364 l_ign := fnd_installation.get_app_info('FND', l_stat,
365 l_ind, G_UN_FND);
366
367 --DBMS_OUTPUT.PUT_LINE('Username -> [' || G_UN_FND || ']');
368
369 select 'y' into x_statement
370 from dba_tables
371 where table_name = 'AD_PA_CRITERIA_PRODUCTS'
372 and owner = G_UN_FND;
373
374 if x_statement = 'y' then
375 begin
376 delete from ad_pa_criteria ac
377 where advisor_criteria_id in ( select advisor_criteria_id
378 from ad_pa_criteria_products acp
379 where acp.advisor_criteria_id = ac.advisor_criteria_id);
380 DELETE from AD_PA_CRITERIA_products;
381 exception
382 when no_data_found then
383 null;
384 end;
385 end if;
386
387 exception
388 when others then
389 null;
390 end;
391
392 update_rp;
393 update_nc;
394 update_rpandnc;
395 end update_all;
396
397 end ad_pa_criteria_util;
[Home] [Help]