eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AD_PA_CRITERIA_UTIL dependencies on AD_PA_CRITERIA

Line 1: package Body ad_pa_criteria_util as

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

Line 3: -- This procedure is to seed data for Recommended Patches in ad_pa_criteria and ad_pa_criteria_prod_fams table

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;

Line 6: l_advisor_criteria_id ad_pa_criteria.ADVISOR_CRITERIA_ID%TYPE;

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

Line 7: l_pre_seeded ad_pa_criteria.pre_seeded_flag%TYPE;

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;

Line 16: -- Then delete rows from ad_pa_criteria_prod_fams.

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

Line 19: -- create row in ad_pa_criteria_prod_fams for this

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

Line 23: -- create row in ad_pa_criteria_prod_fams for this

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: --

Line 31: from AD_PA_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:

Line 37: update AD_PA_CRITERIA

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;

Line 45: insert into AD_PA_CRITERIA

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:

Line 54: delete from ad_pa_criteria_prod_fams

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,

Line 57: insert into ad_pa_criteria_prod_fams

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,

Line 71: insert into ad_pa_criteria_prod_fams

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

Line 79: (select 'X' from ad_pa_criteria_prod_fams adf

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:

Line 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

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

Line 94: update ad_pa_criteria_prod_fams

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;

Line 105: 'into AD_PA_CRITERIA_PROD_FAMS advisor_criteria_id = '

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:

Line 110: update ad_pa_criteria

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:

Line 115: -- This procedure is to seed data for New Code Levels in ad_pa_criteria and ad_pa_criteria_prod_fams table

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;

Line 118: l_advisor_criteria_id ad_pa_criteria.ADVISOR_CRITERIA_ID%TYPE;

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

Line 119: l_pre_seeded ad_pa_criteria.pre_seeded_flag%TYPE;

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;

Line 129: -- Then delete rows from ad_pa_criteria_prod_fams.

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

Line 132: -- create row in ad_pa_criteria_prod_fams for this

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

Line 136: -- create row in ad_pa_criteria_prod_fams for this

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: --

Line 144: from AD_PA_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

Line 149: update 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;

Line 157: insert into AD_PA_CRITERIA

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;

Line 164: delete from ad_pa_criteria_prod_fams

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,

Line 167: insert into ad_pa_criteria_prod_fams

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,

Line 179: insert into ad_pa_criteria_prod_fams

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

Line 187: (select 'X' from ad_pa_criteria_prod_fams adf

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:

Line 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

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

Line 202: update ad_pa_criteria_prod_fams

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;

Line 213: 'into AD_PA_CRITERIA_PROD_FAMS advisor_criteria_id = '||

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:

Line 218: update ad_pa_criteria

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

Line 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

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;

Line 225: l_advisor_criteria_id ad_pa_criteria.ADVISOR_CRITERIA_ID%TYPE;

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

Line 226: l_pre_seeded ad_pa_criteria.pre_seeded_flag%TYPE;

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;

Line 236: -- Then delete rows from ad_pa_criteria_prod_fams.

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

Line 239: -- create row in ad_pa_criteria_prod_fams for this

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

Line 243: -- create row in ad_pa_criteria_prod_fams for this

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: --

Line 251: from AD_PA_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

Line 256: update 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;

Line 264: insert into AD_PA_CRITERIA

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;

Line 271: delete from ad_pa_criteria_prod_fams

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,

Line 274: insert into ad_pa_criteria_prod_fams

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,

Line 286: insert into ad_pa_criteria_prod_fams

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

Line 294: (select 'X' from ad_pa_criteria_prod_fams adf

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:

Line 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

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

Line 309: update ad_pa_criteria_prod_fams

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;

Line 320: 'into AD_PA_CRITERIA_PROD_FAMS advisor_criteria_id = '||

316: exception
317: when dup_val_on_index then
318: raise_application_error(-20001,
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 '||
322: l_pre_seeded);
323: end;
324:

Line 325: update ad_pa_criteria

321: l_advisor_criteria_id || ' and pre_seeded_flag '||
322: l_pre_seeded);
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:

Line 336: -- Delete rows from ad_pa_criteria and ad_pa_criteria_products for 11.5.9

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;

Line 349: delete from ad_pa_criteria

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

Line 351: delete from ad_pa_criteria_prod_fams

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

Line 356: 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

Line 358: from ad_pa_criteria

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:

Line 360: delete from ad_pa_criteria

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,

Line 371: where table_name = 'AD_PA_CRITERIA_PRODUCTS'

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

Line 376: delete from ad_pa_criteria ac

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;

Line 378: from ad_pa_criteria_products acp

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

Line 380: DELETE from AD_PA_CRITERIA_products;

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;

Line 397: end ad_pa_criteria_util;

393: update_nc;
394: update_rpandnc;
395: end update_all;
396:
397: end ad_pa_criteria_util;