[Home] [Help]
PACKAGE BODY: APPS.CS_CF_UPG_UTL_PKG
Source
1 PACKAGE BODY CS_CF_UPG_UTL_PKG as
2 /* $Header: cscfutlb.pls 120.1 2006/01/03 16:53:41 mkcyee noship $ */
3
4 CURSOR get_profile_option_values (p_profile_option_name VARCHAR2)
5 IS SELECT
6 b.level_id,
7 b.level_value,
8 b.level_value_application_id,
9 b.profile_option_value
10 FROM fnd_profile_options a, fnd_profile_option_values b
11 where a.profile_option_name = p_profile_option_name
12 and a.profile_option_id = b.profile_option_id
13 and a.application_id = b.application_id
14 order by b.level_id;
15
16
17 CURSOR get_profile_option_count (p_profile_option_name VARCHAR2)
18 IS SELECT count(*)
19 FROM fnd_profile_options a
20 WHERE a.profile_option_name in (p_profile_option_name);
21
22
23
24 /*
25 * Compare values for the IBU_SR_ACCOUNT_OPTION profile
26 * Oracle iSupport: Service Request Account Option
27 */
28
29 FUNCTION Eval_SR_Account_Option (p_resp_index IN OUT NOCOPY NUMBER,
30 p_respTable IN OUT NOCOPY RespTable,
31 p_appl_index IN OUT NOCOPY NUMBER,
32 p_applTable IN OUT NOCOPY ApplTable,
33 p_site_index IN OUT NOCOPY NUMBER,
34 p_siteProfilesTable IN OUT NOCOPY ProfileTable)
35 RETURN BOOLEAN
36 IS
37
38 l_profile_option_id FND_PROFILE_OPTION_VALUES.profile_option_id%TYPE := 0;
39 l_level_id FND_PROFILE_OPTION_VALUES.level_id%TYPE := 0;
40 l_level_value FND_PROFILE_OPTION_VALUES.level_value%TYPE := 0;
41 l_level_value_appl_id FND_PROFILE_OPTION_VALUES.level_value_application_id%TYPE := 0;
42 l_profile_option_value FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
43
44 l_upgrade_required BOOLEAN := FALSE;
45
46 BEGIN
47
48 OPEN get_profile_option_values('IBU_A_SR_ACCOUNT_OPTION');
49 FETCH get_profile_option_values INTO
50 l_level_id,
51 l_level_value,
52 l_level_value_appl_id,
53 l_profile_option_value;
54 WHILE get_profile_option_values%FOUND LOOP
55 IF (l_level_id = 10001) THEN
56 -- check if the value set at site is equal to
57 -- value seeded out-of-the-box
58 IF (l_profile_option_value <> 'MWOSO') THEN
59 l_upgrade_required := TRUE;
60 p_siteProfilesTable(p_site_index).profileOptionName := 'IBU_A_SR_ACCOUNT_OPTION';
61 p_siteProfilesTable(p_site_index).profileOptionValue := l_profile_option_value;
62 p_site_index := p_site_index + 1;
63 log_mesg(FND_LOG.LEVEL_STATEMENT,'CS_CF_UPG_UTL_PKG.Eval_SR_Account_Option', 'site level value customized. Profile option value: ' || l_profile_option_value);
64 END IF;
65 ELSIF (l_level_id = 10002) THEN
66 -- there should not have been a value set at the appl level
67 -- If there is a vvalue, then it is customized
68 l_upgrade_required := TRUE;
69 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Account_Option', 'appl level value customized. Profile option value: ' || l_profile_option_value);
70 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Account_Option', 'Application Id: ' || to_char(l_level_value));
71 IF NOT(Appl_Already_Exists(p_applTable, l_level_value)) THEN
72 p_applTable(p_appl_index) := l_level_value;
73 p_appl_index := p_appl_index + 1;
74 END IF;
75 ELSIF (l_level_id = 10003) THEN
76 -- there should not have been a value set at the resp level
77 -- If there is a value, then it is customized
78 l_upgrade_required := TRUE;
79 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Account_Option', 'resp level value customized. Profile option value: ' || l_profile_option_value);
80
81 log_mesg(FND_LOG.LEVEL_STATEMENT,'CS_CF_UPG_UTL_PKG.Eval_SR_Account_Option', 'Resp id: ' || to_char(l_level_value) || ' Resp Appl Id: ' || to_char(l_level_value_appl_id));
82
83
84 IF NOT (Resp_Already_Exists(p_respTable, l_level_value, l_level_value_appl_id)) THEN
85 p_respTable(p_resp_index).respId := l_level_value;
86 p_respTable(p_resp_index).respApplId := l_level_value_appl_id;
87 p_resp_index := p_resp_index + 1;
88 END IF;
89 END IF;
90 FETCH get_profile_option_values INTO
91 l_level_id,
92 l_level_value,
93 l_level_value_appl_id,
94 l_profile_option_value;
95 END LOOP;
96 CLOSE get_profile_option_values;
97
98 return l_upgrade_required;
99
100 EXCEPTION
101 WHEN OTHERS THEN
102 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG:Eval_SR_Account_Option', 'Exception in Eval_SR_Account_Option');
103 IF (get_profile_option_values%ISOPEN) THEN
104 CLOSE get_profile_option_values;
105 END IF;
106 RAISE;
107 END Eval_SR_Account_Option;
108
109 /*
110 * Compare values for the IBU_A_SR_PROB_CODE_MANDATORY profile
111 * Oracle iSupport: Problem Code Mandatory During Service Request Creation
112 */
113
114 FUNCTION Eval_SR_Problem_Code_Option (p_resp_index IN OUT NOCOPY NUMBER,
115 p_respTable IN OUT NOCOPY RespTable,
116 p_appl_index IN OUT NOCOPY NUMBER,
117 p_applTable IN OUT NOCOPY ApplTable,
118 p_site_index IN OUT NOCOPY NUMBER,
119 p_siteProfilesTable IN OUT NOCOPY ProfileTable)
120 RETURN BOOLEAN
121 IS
122
123 l_profile_option_id FND_PROFILE_OPTION_VALUES.profile_option_id%TYPE := 0;
124 l_level_id FND_PROFILE_OPTION_VALUES.level_id%TYPE := 0;
125 l_level_value FND_PROFILE_OPTION_VALUES.level_value%TYPE := 0;
126 l_level_value_appl_id FND_PROFILE_OPTION_VALUES.level_value_application_id%TYPE := 0;
127 l_profile_option_value FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
128
129 l_upgrade_required BOOLEAN := FALSE;
130 l_count NUMBER := 0;
131
132 BEGIN
133
134 OPEN get_profile_option_count('IBU_A_SR_PROB_CODE_MANDATORY');
135 FETCH get_profile_option_count INTO l_count;
136 CLOSE get_profile_option_count;
137
138 -- mkcyee 12/14/2004 - This profile was added in the branch of 1159 and was not
139 -- forward ported to seedr10, so it is possible that this profile does not get
140 -- exist if this is a pre-1159+ or 11510 and later installation.
141
142 IF (l_count = 0) THEN
143 return l_upgrade_required;
144 END IF;
145
146 OPEN get_profile_option_values('IBU_A_SR_PROB_CODE_MANDATORY');
147 FETCH get_profile_option_values INTO
148 l_level_id,
149 l_level_value,
150 l_level_value_appl_id,
151 l_profile_option_value;
152 WHILE get_profile_option_values%FOUND LOOP
153 IF (l_level_id = 10001) THEN
154 -- check if the value set at site is equal to
155 -- value seeded out-of-the-box
156 IF (l_profile_option_value <> 'N') THEN
157 l_upgrade_required := TRUE;
158 p_siteProfilesTable(p_site_index).profileOptionName := 'IBU_A_SR_PROB_CODE_MANDATORY';
159 p_siteProfilesTable(p_site_index).profileOptionValue := l_profile_option_value;
160 p_site_index := p_site_index + 1;
161 log_mesg(FND_LOG.LEVEL_STATEMENT,'CS_CF_UPG_UTL_PKG.Eval_SR_Problem_Code_Option', 'site level value customized. Profile option value: ' || l_profile_option_value);
162 END IF;
163 ELSIF (l_level_id = 10002) THEN
164 -- there should not have been a value set at the appl level
165 -- If there is a vvalue, then it is customized
166 l_upgrade_required := TRUE;
167 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Problem_Code_Option', 'appl level value customized. Profile option value: ' || l_profile_option_value);
168 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Problem_Option', 'Application Id: ' || to_char(l_level_value));
169 IF NOT(Appl_Already_Exists(p_applTable, l_level_value)) THEN
170 p_applTable(p_appl_index) := l_level_value;
171 p_appl_index := p_appl_index + 1;
172 END IF;
173 ELSIF (l_level_id = 10003) THEN
174 -- there should not be any values, so if there is,
175 -- it must be a customization
176 l_upgrade_required := TRUE;
177 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Problem_Code_Option', 'resp level value customized. Profile option value: ' || l_profile_option_value);
178
179 log_mesg(FND_LOG.LEVEL_STATEMENT,'CS_CF_UPG_UTL_PKG.Eval_SR_Problem_Code_Option', 'Resp id: ' || to_char(l_level_value) || ' Resp Appl Id: ' || to_char(l_level_value_appl_id));
180
181
182 IF NOT (Resp_Already_Exists(p_respTable, l_level_value, l_level_value_appl_id)) THEN
183 p_respTable(p_resp_index).respId := l_level_value;
184 p_respTable(p_resp_index).respApplId := l_level_value_appl_id;
185 p_resp_index := p_resp_index + 1;
186 END IF;
187 END IF;
188 FETCH get_profile_option_values INTO
189 l_level_id,
190 l_level_value,
191 l_level_value_appl_id,
192 l_profile_option_value;
193 END LOOP;
194 CLOSE get_profile_option_values;
195
196 return l_upgrade_required;
197
198 EXCEPTION
199 WHEN OTHERS THEN
200 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG:Eval_SR_Problem_Code_Option', 'Exception in Eval_SR_Problem_Code_Option');
201 IF (get_profile_option_values%ISOPEN) THEN
202 CLOSE get_profile_option_values;
203 END IF;
204 IF (get_profile_option_count%ISOPEN) THEN
205 CLOSE get_profile_option_count;
206 END IF;
207
208 RAISE;
209 END Eval_SR_Problem_Code_Option;
210
211 /*
212 * Compare values for the IBU_SR_CREATION_PRODUCT_OPTION profile
213 * Oracle iSupport: Create Service Request Product Option
214 */
215
216 FUNCTION Eval_SR_Creation_Prod_Option (p_appl_index IN OUT NOCOPY NUMBER,
217 p_applTable IN OUT NOCOPY ApplTable,
218 p_resp_index IN OUT NOCOPY NUMBER,
219 p_respTable IN OUT NOCOPY RespTable,
220 p_site_index IN OUT NOCOPY NUMBER,
221 p_siteProfilesTable IN OUT NOCOPY ProfileTable)
222 RETURN BOOLEAN
223 IS
224
225 l_profile_option_id FND_PROFILE_OPTION_VALUES.profile_option_id%TYPE := 0;
226 l_level_id FND_PROFILE_OPTION_VALUES.level_id%TYPE := 0;
227 l_level_value FND_PROFILE_OPTION_VALUES.level_value%TYPE := 0;
228 l_level_value_appl_id FND_PROFILE_OPTION_VALUES.level_value_application_id%TYPE := 0;
229 l_profile_option_value FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
230
231 l_upgrade_required BOOLEAN := FALSE;
232
233 BEGIN
234 OPEN get_profile_option_values('IBU_SR_CREATION_PRODUCT_OPTION');
235 FETCH get_profile_option_values INTO
236 l_level_id,
237 l_level_value,
238 l_level_value_appl_id,
239 l_profile_option_value;
240 WHILE get_profile_option_values%FOUND LOOP
241
242 IF (l_level_id = 10001) THEN
243 IF (l_profile_option_value <> 'USE_BOTH_INVENTORY_AND_INSTALL') THEN
244 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Prod_Creation_Option', 'site level value customized. Profile option value: ' || l_profile_option_value);
245 p_siteProfilesTable(p_site_index).profileOptionName := 'IBU_SR_CREATION_PRODUCT_OPTION';
246 p_siteProfilesTAble(p_site_index).profileOptionValue := l_profile_option_value;
247 p_site_index := p_site_index + 1;
248 l_upgrade_required := TRUE;
249 END IF ;
250 ELSIF (l_level_id = 10002) THEN
251 -- There wasn't any seeded value for the app level. Therefore,
252 -- this must be a customization
253 l_upgrade_required := TRUE;
254 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Prod_Creation_Option', 'appl level value customized. Profile option value: ' || l_profile_option_value);
255 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Prod_Creation_Option', 'Application Id: ' || to_char(l_level_value));
256
257 IF NOT(Appl_Already_Exists(p_ApplTable, l_level_value)) THEN
258 p_ApplTable(p_appl_index) := l_level_value;
259 p_appl_index := p_appl_index + 1;
260 END IF;
261 ELSIF (l_level_id = 10003) THEN
262 -- There wasn't any seeded value for the resp level. Therefore,
263 -- this must be a customization
264 l_upgrade_required := TRUE;
265 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Prod_Creation_Option', 'resp level value customized. Profile option value: ' || l_profile_option_value);
266 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Prod_Creation_Option', 'Resp id: ' || to_char(l_level_value) || ' Resp Appl Id: ' || to_char(l_level_value_appl_id));
267 IF NOT (Resp_Already_Exists(p_RespTable, l_level_value, l_level_value_appl_id)) THEN
268 p_RespTable(p_resp_index).respId := l_level_value;
269 p_RespTable(p_resp_index).respApplId := l_level_value_appl_id;
270 p_resp_index := p_resp_index + 1;
271 END IF;
272 END IF;
273 FETCH get_profile_option_values INTO
274 l_level_id,
275 l_level_value,
276 l_level_value_appl_id,
277 l_profile_option_value;
278 END LOOP;
279 CLOSE get_profile_option_values;
280
281 return l_upgrade_required;
282
283 EXCEPTION
284 WHEN OTHERS THEN
285 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG.Eval_SR_Prod_Creation_Option', 'Exception in Eval_SR_Prod_Creation_Option');
286 IF (get_profile_option_values%ISOPEN) THEN
287 CLOSE get_profile_option_values;
288 END IF;
289 RAISE;
290 END Eval_SR_Creation_Prod_Option;
291
292 /*
293 * Compare values for the IBU_SR_ADDR_DISPLAY profile
294 * Oracle iSupport: Show Address Section in Service Request
295 */
296
297 FUNCTION Eval_SR_Addr_Display (p_appl_index IN OUT NOCOPY NUMBER,
298 p_applTable IN OUT NOCOPY ApplTable,
299 p_resp_index IN OUT NOCOPY NUMBER,
300 p_respTable IN OUT NOCOPY RespTable,
301 p_site_index IN OUT NOCOPY NUMBER,
302 p_siteProfilesTable IN OUT NOCOPY ProfileTable)
303 RETURN BOOLEAN
304 IS
305
306 l_profile_option_id FND_PROFILE_OPTION_VALUES.profile_option_id%TYPE := 0;
307 l_level_id FND_PROFILE_OPTION_VALUES.level_id%TYPE := 0;
308 l_level_value FND_PROFILE_OPTION_VALUES.level_value%TYPE := 0;
309 l_level_value_appl_id FND_PROFILE_OPTION_VALUES.level_value_application_id%TYPE := 0;
310 l_profile_option_value FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
311
312 l_upgrade_required BOOLEAN := FALSE;
313
314 BEGIN
315 OPEN get_profile_option_values('IBU_SR_ADDR_DISPLAY');
316 FETCH get_profile_option_values INTO
317 l_level_id,
318 l_level_value,
319 l_level_value_appl_id,
320 l_profile_option_value;
321 WHILE get_profile_option_values%FOUND LOOP
322 IF (l_level_id = 10001) THEN
323 -- There wasn't any seeded value for the site level. Therefore, this
324 -- must be a customization
325 l_upgrade_required := TRUE;
326 log_mesg(FND_LOG.LEVEL_STATEMENT,'CS_CF_UPG_UTL_PKG.Eval_SR_Addr_Display', 'site level value customized. Profile option value: ' || l_profile_option_value);
327 p_siteProfilesTable(p_site_index).profileOptionName := 'IBU_SR_ADDR_DISPLAY';
328 p_siteProfilesTable(p_site_index).profileOptionValue := l_profile_option_value;
329 p_site_index := p_site_index + 1;
330
331 ELSIF (l_level_id = 10002) THEN
332 -- There wasn't any seeded value for the app level. Therefore,
333 -- this must be a customization
334 l_upgrade_required := TRUE;
335 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Addr_Display', 'appl level value customized. Profile option value: ' || l_profile_option_value);
336 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Addr_Display', 'Application Id: ' || to_char(l_level_value));
337 IF NOT (Appl_Already_Exists(p_applTable, l_level_value)) THEN
338 p_ApplTable(p_appl_index) := l_level_value;
339 p_appl_index := p_appl_index + 1;
340 END IF;
341 ELSIF (l_level_id = 10003) THEN
342 -- There wasn't any seeded value for the resp level. Therefore,
343 -- this must be a customization
344 l_upgrade_required := TRUE;
345 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Addr_Display', 'resp level value customized. Profile option value: ' || l_profile_option_value);
346 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Addr_Display', 'Resp id: ' || to_char(l_level_value) || ' Resp Appl Id: ' || to_char(l_level_value_appl_id));
347 IF NOT (Resp_Already_Exists(p_RespTable, l_level_value, l_level_value_appl_id)) THEN
348 p_RespTable(p_resp_index).respId := l_level_value;
349 p_RespTable(p_resp_index).respApplId := l_level_value_appl_id;
350 p_resp_index := p_resp_index + 1;
351 END IF;
352 END IF;
353 FETCH get_profile_option_values INTO
354 l_level_id,
355 l_level_value,
356 l_level_value_appl_id,
357 l_profile_option_value;
358 END LOOP;
359 CLOSE get_profile_option_values;
360
361 return l_upgrade_required;
362
363 EXCEPTION
364 WHEN OTHERS THEN
365 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG.Eval_SR_Addr_Display', 'Exception in Eval_SR_Addr_Display');
366 IF (get_profile_option_values%ISOPEN) THEN
367 CLOSE get_profile_option_values;
368 END IF;
369 RAISE;
370 END Eval_SR_Addr_Display;
371
372
373 /*
374 * Compare values for the IBU_SR_ADDR_MANDATORY profile
375 * Oracle iSupport: Address Field Mandatory in Service Request Creation
376 */
377
378 FUNCTION Eval_SR_Addr_Mandatory (p_appl_index IN OUT NOCOPY NUMBER,
379 p_ApplTable IN OUT NOCOPY ApplTable,
380 p_resp_index IN OUT NOCOPY NUMBER,
381 p_RespTable IN OUT NOCOPY RespTable,
382 p_site_index IN OUT NOCOPY NUMBER,
383 p_siteProfilesTable IN OUT NOCOPY ProfileTable)
384 RETURN BOOLEAN
385 IS
386
387 l_profile_option_id FND_PROFILE_OPTION_VALUES.profile_option_id%TYPE := 0;
388 l_level_id FND_PROFILE_OPTION_VALUES.level_id%TYPE := 0;
389 l_level_value FND_PROFILE_OPTION_VALUES.level_value%TYPE := 0;
390 l_level_value_appl_id FND_PROFILE_OPTION_VALUES.level_value_application_id%TYPE := 0;
391 l_profile_option_value FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
392
393 l_upgrade_required BOOLEAN := FALSE;
394
395 BEGIN
396 OPEN get_profile_option_values('IBU_SR_ADDR_MANDATORY');
397 FETCH get_profile_option_values INTO
398 l_level_id,
399 l_level_value,
400 l_level_value_appl_id,
401 l_profile_option_value;
402 WHILE get_profile_option_values%FOUND LOOP
403
404 IF (l_level_id = 10001) THEN
405 -- There wasn't any seeded value for the site level. Therefore, this
406 -- must be a customization
407 l_upgrade_required := TRUE;
408 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Addr_Mandatory', 'site level value customized. Profile option value: ' || l_profile_option_value);
409 p_siteProfilesTable(p_site_index).profileOptionName := 'IBU_SR_ADDR_MANDATORY';
410 p_siteProfilesTAble(p_site_index).profileOptionValue := l_profile_option_value;
411 p_site_index := p_site_index + 1;
412 ELSIF (l_level_id = 10002) THEN
413 -- There wasn't any seeded value for the app level. Therefore,
414 -- this must be a customization
415 l_upgrade_required := TRUE;
416 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Addr_Mandatory', 'appl level value customized. Profile option value: ' || l_profile_option_value);
417 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Addr_Mandatory','Application Id: ' || to_char(l_level_value));
418 IF NOT (Appl_Already_Exists(p_ApplTable, l_level_value)) THEN
419 p_ApplTable(p_appl_index) := l_level_value;
420 p_appl_index := p_appl_index + 1;
421 END IF;
422 ELSIF (l_level_id = 10003) THEN
423 -- There wasn't any seeded value for the resp level. Therefore,
424 -- this must be a customization
425 l_upgrade_required := TRUE;
426 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Addr_Mandatory', 'resp level value customized. Profile option value: ' || l_profile_option_value);
427 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Addr_Mandatory','Resp id: ' || to_char(l_level_value) || ' Resp Appl Id: ' || to_char(l_level_value_appl_id));
428 IF NOT (Resp_Already_Exists(p_RespTable, l_level_value, l_level_value_appl_id)) THEN
429 p_RespTable(p_resp_index).respId := l_level_value;
430 p_RespTable(p_resp_index).respApplId := l_level_value_appl_id;
431 p_resp_index := p_resp_index + 1;
432 END IF;
433 END IF;
434 FETCH get_profile_option_values INTO
435 l_level_id,
436 l_level_value,
437 l_level_value_appl_id,
438 l_profile_option_value;
439 END LOOP;
440 CLOSE get_profile_option_values;
441
442 return l_upgrade_required;
443
444 EXCEPTION
445 WHEN OTHERS THEN
446 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG.Eval_SR_Addr_Mandatory', 'Exception in Eval_SR_Addr_Mandatory');
447 IF (get_profile_option_values%ISOPEN) THEN
448 CLOSE get_profile_option_values;
449 END IF;
450 RAISE;
451 END Eval_SR_Addr_Mandatory;
452
453
454 /*
455 * Compare values for the IBU_A_SR_BILLTO_ADDRESS_OPTION profile
456 * Oracle iSupport: Service Request Bill To Address Option
457 */
458
459 FUNCTION Eval_SR_BillTo_Address_Option (p_resp_index IN OUT NOCOPY NUMBER,
460 p_respTable IN OUT NOCOPY RespTable,
461 p_appl_index IN OUT NOCOPY NUMBER,
462 p_applTable IN OUT NOCOPY ApplTable,
463 p_site_index IN OUT NOCOPY NUMBER,
464 p_siteProfilesTable IN OUT NOCOPY ProfileTable)
465 RETURN BOOLEAN
466 IS
467
468 l_profile_option_id FND_PROFILE_OPTION_VALUES.profile_option_id%TYPE := 0;
469 l_level_id FND_PROFILE_OPTION_VALUES.level_id%TYPE := 0;
470 l_level_value FND_PROFILE_OPTION_VALUES.level_value%TYPE := 0;
471 l_level_value_appl_id FND_PROFILE_OPTION_VALUES.level_value_application_id%TYPE := 0;
472 l_profile_option_value FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
473
474 l_upgrade_required BOOLEAN := FALSE;
475
476 BEGIN
477
478 OPEN get_profile_option_values('IBU_A_SR_BILLTO_ADDRESS_OPTION');
479 FETCH get_profile_option_values INTO
480 l_level_id,
481 l_level_value,
482 l_level_value_appl_id,
483 l_profile_option_value;
484 WHILE get_profile_option_values%FOUND LOOP
485 IF (l_level_id = 10001) THEN
486 -- check if the value set at site is equal to
487 -- value seeded out-of-the-box
488 IF (l_profile_option_value <> 'NOTDISPLAYED') THEN
489 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_BillTo_Address', 'site level value customized. Profile option value: ' || l_profile_option_value);
490 p_siteProfilesTable(p_site_index).profileOptionName := 'IBU_SR_BILLTO_ADDRESS_OPTION';
491 p_siteProfilesTAble(p_site_index).profileOptionValue := l_profile_option_value;
492 p_site_index := p_site_index + 1;
493 l_upgrade_required := TRUE;
494 END IF;
495 ELSIF (l_level_id = 10002) THEN
496 -- There wasn't any seeded value for the app level. Therefore,
497 -- this must be a customization
498 l_upgrade_required := TRUE;
499 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_BillTo_Address', 'appl level value customized. Profile option value: ' || l_profile_option_value);
500 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_BillTo_Address','Application Id: ' || to_char(l_level_value));
501 IF NOT (Appl_Already_Exists(p_ApplTable, l_level_value)) THEN
502 p_ApplTable(p_appl_index) := l_level_value;
503 p_appl_index := p_appl_index + 1;
504 END IF;
505 ELSIF (l_level_id = 10003) THEN
506 -- there should not have been a value set at the resp level
507 -- If there is a value, then it is customized
508 l_upgrade_required := TRUE;
509 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_BillTo_Address', 'resp level value customized. Profile option value: ' || l_profile_option_value);
510 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_BillTo_Address', 'Resp id: ' || to_char(l_level_value) || ' Resp Appl Id: ' || to_char(l_level_value_appl_id));
511 IF NOT (Resp_Already_Exists(p_respTable, l_level_value, l_level_value_appl_id)) THEN
512 p_respTable(p_resp_index).respId := l_level_value;
513 p_respTable(p_resp_index).respApplId := l_level_value_appl_id;
514 p_resp_index := p_resp_index + 1;
515 END IF;
516 END IF;
517 FETCH get_profile_option_values INTO
518 l_level_id,
519 l_level_value,
520 l_level_value_appl_id,
521 l_profile_option_value;
522 END LOOP;
523 CLOSE get_profile_option_values;
524
525 return l_upgrade_required;
526
527 EXCEPTION
528 WHEN OTHERS THEN
529 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG.Eval_SR_BillTo_Address', 'Exception in Eval_SR_BillTo_Address_Option');
530 IF (get_profile_option_values%ISOPEN) THEN
531 CLOSE get_profile_option_values;
532 END IF;
533 RAISE;
534 END Eval_SR_BillTo_Address_Option;
535
536 /*
537 * Compare values for the IBU_A_SR_BILLTO_CONTACT_OPTION profile
538 * Oracle iSupport: Service Request Bill To Contact Option
539 */
540
541 FUNCTION Eval_SR_BillTo_Contact_Option (p_resp_index IN OUT NOCOPY NUMBER,
542 p_respTable IN OUT NOCOPY RespTable,
543 p_appl_index IN OUT NOCOPY NUMBER,
544 p_applTable IN OUT NOCOPY ApplTable,
545 p_site_index IN OUT NOCOPY NUMBER,
546 p_siteProfilesTable IN OUT NOCOPY ProfileTable)
547 RETURN BOOLEAN
548 IS
549
550 l_profile_option_id FND_PROFILE_OPTION_VALUES.profile_option_id%TYPE := 0;
551 l_level_id FND_PROFILE_OPTION_VALUES.level_id%TYPE := 0;
552 l_level_value FND_PROFILE_OPTION_VALUES.level_value%TYPE := 0;
553 l_level_value_appl_id FND_PROFILE_OPTION_VALUES.level_value_application_id%TYPE := 0;
554 l_profile_option_value FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
555
556 l_upgrade_required BOOLEAN := FALSE;
557
558 BEGIN
559
560 OPEN get_profile_option_values('IBU_A_SR_BILLTO_CONTACT_OPTION');
561 FETCH get_profile_option_values INTO
562 l_level_id,
563 l_level_value,
564 l_level_value_appl_id,
565 l_profile_option_value;
566 WHILE get_profile_option_values%FOUND LOOP
567 IF (l_level_id = 10001) THEN
568 -- check if the value set at site is equal to
569 -- value seeded out-of-the-box
570 IF (l_profile_option_value <> 'NOTDISPLAYED') THEN
571 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_BillTo_Contact', 'site level value customized. Profile option value: ' || l_profile_option_value);
572 p_siteProfilesTable(p_site_index).profileOptionName := 'IBU_SR_BILLTO_CONTACT_OPTION';
573 p_siteProfilesTAble(p_site_index).profileOptionValue := l_profile_option_value;
574 p_site_index := p_site_index + 1;
575 l_upgrade_required := TRUE;
576 END IF;
577 ELSIF (l_level_id = 10002) THEN
578 -- There wasn't any seeded value for the app level. Therefore,
579 -- this must be a customization
580 l_upgrade_required := TRUE;
581 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_BillTo_Contact', 'appl level value customized. Profile option value: ' || l_profile_option_value);
582 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_BillTo_Contact','Application Id: ' || to_char(l_level_value));
583 IF NOT (Appl_Already_Exists(p_ApplTable, l_level_value)) THEN
584 p_ApplTable(p_appl_index) := l_level_value;
585 p_appl_index := p_appl_index + 1;
586 END IF;
587 ELSIF (l_level_id = 10003) THEN
588 -- there should not have been a value set at the resp level
589 -- If there is a value, then it is customized
590 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_BillTo_Contact', 'resp level value customized. Profile option value: ' || l_profile_option_value);
591 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_BillTo_Contact', 'Resp id: ' || to_char(l_level_value) || ' Resp Appl Id: ' || to_char(l_level_value_appl_id));
592 l_upgrade_required := TRUE;
593 IF NOT (Resp_Already_Exists(p_respTable, l_level_value, l_level_value_appl_id)) THEN
594 p_respTable(p_resp_index).respId := l_level_value;
595 p_respTable(p_resp_index).respApplId := l_level_value_appl_id;
596 p_resp_index := p_resp_index + 1;
597 END IF;
598 END IF;
599 FETCH get_profile_option_values INTO
600 l_level_id,
601 l_level_value,
602 l_level_value_appl_id,
603 l_profile_option_value;
604 END LOOP;
605 CLOSE get_profile_option_values;
606
607 return l_upgrade_required;
608
609 EXCEPTION
610 WHEN OTHERS THEN
611 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG.Eval_SR_BillTo_Contact_Option', 'Exception in Eval_SR_BillTo_Contact_Option');
612 IF (get_profile_option_values%ISOPEN) THEN
613 CLOSE get_profile_option_values;
614 END IF;
615 RAISE;
616 END Eval_SR_BillTo_Contact_Option;
617
618 /*
619 * Compare values for the IBU_A_SR_SHIPTO_ADDRESS_OPTION profile
620 * Oracle iSupport: Service Request Ship To Address Option
621 */
622
623 FUNCTION Eval_SR_ShipTo_Address_Option (p_resp_index IN OUT NOCOPY NUMBER,
624 p_respTable IN OUT NOCOPY RespTable,
625 p_appl_index IN OUT NOCOPY NUMBER,
626 p_applTable IN OUT NOCOPY ApplTable,
627 p_site_index IN OUT NOCOPY NUMBER,
628 p_siteProfilesTable IN OUT NOCOPY ProfileTable)
629 RETURN BOOLEAN
630 IS
631
632 l_profile_option_id FND_PROFILE_OPTION_VALUES.profile_option_id%TYPE := 0;
633 l_level_id FND_PROFILE_OPTION_VALUES.level_id%TYPE := 0;
634 l_level_value FND_PROFILE_OPTION_VALUES.level_value%TYPE := 0;
635 l_level_value_appl_id FND_PROFILE_OPTION_VALUES.level_value_application_id%TYPE := 0;
636 l_profile_option_value FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
637
638 l_upgrade_required BOOLEAN := FALSE;
639
640 BEGIN
641
642 OPEN get_profile_option_values('IBU_A_SR_SHIPTO_ADDRESS_OPTION');
643 FETCH get_profile_option_values INTO
644 l_level_id,
645 l_level_value,
646 l_level_value_appl_id,
647 l_profile_option_value;
648 WHILE get_profile_option_values%FOUND LOOP
649 IF (l_level_id = 10001) THEN
650 -- check if the value set at site is equal to
651 -- value seeded out-of-the-box
652 IF (l_profile_option_value <> 'NOTDISPLAYED') THEN
653 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_ShipTo_Address', 'site level value customized. Profile option value: ' || l_profile_option_value);
654 p_siteProfilesTable(p_site_index).profileOptionName := 'IBU_A_SR_SHIPTO_ADDRESS_OPTION';
655 p_siteProfilesTable(p_site_index).profileOptionValue := l_profile_option_value;
656 p_site_index := p_site_index + 1;
657 l_upgrade_required := TRUE;
658 END IF;
659 ELSIF (l_level_id = 10002) THEN
660 -- there should not have been a value set at the appl level
661 -- if there is a value, then it is customized
662 l_upgrade_required := TRUE;
663 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_ShipTo_Address_Option', 'appl level value customized. Profile option value: ' || l_profile_option_value);
664 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_ShipTo_Address_Option', 'Application Id: ' || to_char(l_level_value));
665 IF NOT(Appl_Already_Exists(p_applTable, l_level_value)) THEN
666 p_applTable(p_appl_index) := l_level_value;
667 p_appl_index := p_appl_index + 1;
668 END IF;
669
670 ELSIF (l_level_id = 10003) THEN
671 -- there should not have been a value set at the resp level
672 -- If there is a value, then it is customized
673 l_upgrade_required := TRUE;
674 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_ShipTo_Address_Option', 'resp level value customized. Profile option value: ' || l_profile_option_value);
675 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_ShipTo_Address_Option', 'Resp id: ' || to_char(l_level_value) || ' Resp Appl Id: ' || to_char(l_level_value_appl_id));
676 l_upgrade_required := TRUE;
677 IF NOT (Resp_Already_Exists(p_respTable, l_level_value, l_level_value_appl_id)) THEN
678 p_respTable(p_resp_index).respId := l_level_value;
679 p_respTable(p_resp_index).respApplId := l_level_value_appl_id;
680 p_resp_index := p_resp_index + 1;
681 END IF;
682 END IF;
683 FETCH get_profile_option_values INTO
684 l_level_id,
685 l_level_value,
686 l_level_value_appl_id,
687 l_profile_option_value;
688 END LOOP;
689 CLOSE get_profile_option_values;
690
691 return l_upgrade_required;
692
693 EXCEPTION
694 WHEN OTHERS THEN
695 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG.Eval_SR_ShipTo_Address_Option', 'Exception in Eval_SR_ShipTo_Address_Option');
696 IF (get_profile_option_values%ISOPEN) THEN
697 CLOSE get_profile_option_values;
698 END IF;
699 RAISE;
700 END Eval_SR_ShipTo_Address_Option;
701
702 /*
703 * Compare values for the IBU_A_SR_SHIPTO_CONTACT_OPTION profile
704 * Oracle iSupport: Service Request Ship To Contact Option
705 */
706
707 FUNCTION Eval_SR_ShipTo_Contact_Option (p_resp_index IN OUT NOCOPY NUMBER,
708 p_respTable IN OUT NOCOPY RespTable,
709 p_appl_index IN OUT NOCOPY NUMBER,
710 p_applTable IN OUT NOCOPY ApplTable,
711 p_site_index IN OUT NOCOPY NUMBER,
712 p_siteProfilesTable IN OUT NOCOPY ProfileTable)
713 RETURN BOOLEAN
714 IS
715
716 l_profile_option_id FND_PROFILE_OPTION_VALUES.profile_option_id%TYPE := 0;
717 l_level_id FND_PROFILE_OPTION_VALUES.level_id%TYPE := 0;
718 l_level_value FND_PROFILE_OPTION_VALUES.level_value%TYPE := 0;
719 l_level_value_appl_id FND_PROFILE_OPTION_VALUES.level_value_application_id%TYPE := 0;
720 l_profile_option_value FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
721
722 l_upgrade_required BOOLEAN := FALSE;
723
724 BEGIN
725
726 OPEN get_profile_option_values('IBU_A_SR_SHIPTO_CONTACT_OPTION');
727 FETCH get_profile_option_values INTO
728 l_level_id,
729 l_level_value,
730 l_level_value_appl_id,
731 l_profile_option_value;
732 WHILE get_profile_option_values%FOUND LOOP
733 IF (l_level_id = 10001) THEN
734 -- check if the value set at site is equal to
735 -- value seeded out-of-the-box
736 IF (l_profile_option_value <> 'NOTDISPLAYED') THEN
737 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_ShipTo_Contact', 'site level value customized. Profile option value: ' || l_profile_option_value);
738 p_siteProfilesTable(p_site_index).profileOptionName := 'IBU_A_SR_SHIPTO_CONTACT_OPTION';
739 p_siteProfilesTable(p_site_index).profileOptionValue := l_profile_option_value;
740 p_site_index := p_site_index + 1;
741 l_upgrade_required := TRUE;
742 END IF;
743 ELSIF (l_level_id = 10002) THEN
744 -- there should not have been a value set at the appl level
745 -- if there is a value, then it is customized
746 l_upgrade_required := TRUE;
747 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_ShipTo_Contact_Option', 'appl level value customized. Profile option value: ' || l_profile_option_value);
748 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_ShipTo_Contact_Option', 'Application Id: ' || to_char(l_level_value));
749 IF NOT(Appl_Already_Exists(p_applTable, l_level_value)) THEN
750 p_applTable(p_appl_index) := l_level_value;
751 p_appl_index := p_appl_index + 1;
752 END IF;
753 ELSIF (l_level_id = 10003) THEN
754 -- there should not have been a value set at the resp level
755 -- If there is a value, then it is customized
756 l_upgrade_required := TRUE;
757 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_ShipTo_Contact_Option', 'resp level value customized. Profile option value: ' || l_profile_option_value);
758 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_ShipTo_Contact_Option','Resp id: ' || to_char(l_level_value) || ' Resp Appl Id: ' || to_char(l_level_value_appl_id));
759 l_upgrade_required := TRUE;
760 IF NOT (Resp_Already_Exists(p_respTable, l_level_value, l_level_value_appl_id)) THEN
761 p_respTable(p_resp_index).respId := l_level_value;
762 p_respTable(p_resp_index).respApplId := l_level_value_appl_id;
763 p_resp_index := p_resp_index + 1;
764 END IF;
765 END IF;
766 FETCH get_profile_option_values INTO
767 l_level_id,
768 l_level_value,
769 l_level_value_appl_id,
770 l_profile_option_value;
771 END LOOP;
772 CLOSE get_profile_option_values;
773
774 return l_upgrade_required;
775
776 EXCEPTION
777 WHEN OTHERS THEN
778 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG.Eval_SR_ShipTo_Contact_Option', 'Exception in Eval_SR_ShipTo_Contact_Option');
779 IF (get_profile_option_values%ISOPEN) THEN
780 CLOSE get_profile_option_values;
781 END IF;
782 RAISE;
783
784 END Eval_SR_ShipTo_Contact_Option;
785
786 /*
787 * Compare values for the IBU_A_SR_INSTALLEDAT_ADDRESS_OPTION profile
788 * Oracle iSupport: Service Request Installed At Address Option
789 */
790
791 FUNCTION Eval_SR_InstalledAt_Address (p_resp_index IN OUT NOCOPY NUMBER,
792 p_respTable IN OUT NOCOPY RespTable,
793 p_appl_index IN OUT NOCOPY NUMBER,
794 p_applTable IN OUT NOCOPY ApplTable,
795 p_site_index IN OUT NOCOPY NUMBER,
796 p_siteProfilesTable IN OUT NOCOPY ProfileTable)
797 RETURN BOOLEAN
798 IS
799
800 l_profile_option_id FND_PROFILE_OPTION_VALUES.profile_option_id%TYPE := 0;
801 l_level_id FND_PROFILE_OPTION_VALUES.level_id%TYPE := 0;
802 l_level_value FND_PROFILE_OPTION_VALUES.level_value%TYPE := 0;
803 l_level_value_appl_id FND_PROFILE_OPTION_VALUES.level_value_application_id%TYPE := 0;
804 l_profile_option_value FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
805
806 l_upgrade_required BOOLEAN := FALSE;
807
808 BEGIN
809
810 OPEN get_profile_option_values('IBU_A_SR_INSTALLEDAT_ADDRESS_OPTION');
811 FETCH get_profile_option_values INTO
812 l_level_id,
813 l_level_value,
814 l_level_value_appl_id,
815 l_profile_option_value;
816 WHILE get_profile_option_values%FOUND LOOP
817 IF (l_level_id = 10001) THEN
818 -- check if the value set at site is equal to
819 -- value seeded out-of-the-box
820 IF (l_profile_option_value <> 'NOTDISPLAYED') THEN
821 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_InstalledAt_Addr', 'site level value customized. Profile option value: ' || l_profile_option_value);
822 p_siteProfilesTable(p_site_index).profileOptionName := 'IBU_A_SR_INSTALLEDAT_ADDRESS_OPTION';
823 p_siteProfilesTable(p_site_index).profileOptionValue := l_profile_option_value;
824 p_site_index := p_site_index + 1;
825 l_upgrade_required := TRUE;
826 END IF;
827 ELSIF (l_level_id = 10002) THEN
828 -- there should not have been a value set at the appl level
829 -- if there is a value, then it is customized
830 l_upgrade_required := TRUE;
831 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_InstalledAt_Addr', 'appl level value customized. Profile option value: ' || l_profile_option_value);
832 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_InstalledAt_Addr', 'Application Id: ' || to_char(l_level_value));
833 IF NOT(Appl_Already_Exists(p_applTable, l_level_value)) THEN
834 p_applTable(p_appl_index) := l_level_value;
835 p_appl_index := p_appl_index + 1;
836 END IF;
837 ELSIF (l_level_id = 10003) THEN
838 -- there should not have been a value set at the resp level
839 -- If there is a value, then it is customized
840 l_upgrade_required := TRUE;
841 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_InstalledAt_Addr', 'resp level value customized. Profile option value: ' || l_profile_option_value);
842 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_InstalledAt_Addr', 'Resp id: ' || to_char(l_level_value) || ' Resp Appl Id: ' || to_char(l_level_value_appl_id));
843 l_upgrade_required := TRUE;
844 IF NOT (Resp_Already_Exists(p_respTable, l_level_value, l_level_value_appl_id)) THEN
845 p_respTable(p_resp_index).respId := l_level_value;
846 p_respTable(p_resp_index).respApplId := l_level_value_appl_id;
847 p_resp_index := p_resp_index + 1;
848 END IF;
849 END IF;
850 FETCH get_profile_option_values INTO
851 l_level_id,
852 l_level_value,
853 l_level_value_appl_id,
854 l_profile_option_value;
855 END LOOP;
856 CLOSE get_profile_option_values;
857
858 return l_upgrade_required;
859
860 EXCEPTION
861 WHEN OTHERS THEN
862 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG.Eval_SR_InstalledAt_Addr', 'Exception in Eval_SR_InstalledAt_Address');
863 IF (get_profile_option_values%ISOPEN) THEN
864 CLOSE get_profile_option_values;
865 END IF;
866 RAISE;
867
868 END Eval_SR_InstalledAt_Address;
869
870
871 /*
872 * Compare values for the IBU_A_SR_ATTACHMENT_OPTION profile
873 * Oracle iSupport: Service Request Attachment Option
874 */
875
876 FUNCTION Eval_SR_Attachment_Option (p_resp_index IN OUT NOCOPY NUMBER,
877 p_respTable IN OUT NOCOPY RespTable,
878 p_appl_index IN OUT NOCOPY NUMBER,
879 p_applTable IN OUT NOCOPY ApplTable,
880 p_site_index IN OUT NOCOPY NUMBER,
881 p_siteProfilesTable IN OUT NOCOPY ProfileTable)
882 RETURN BOOLEAN
883 IS
884
885 l_profile_option_id FND_PROFILE_OPTION_VALUES.profile_option_id%TYPE := 0;
886 l_level_id FND_PROFILE_OPTION_VALUES.level_id%TYPE := 0;
887 l_level_value FND_PROFILE_OPTION_VALUES.level_value%TYPE := 0;
888 l_level_value_appl_id FND_PROFILE_OPTION_VALUES.level_value_application_id%TYPE := 0;
889 l_profile_option_value FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
890
891 l_upgrade_required BOOLEAN := FALSE;
892
893 BEGIN
894
895 OPEN get_profile_option_values('IBU_A_SR_ATTACHMENT_OPTION');
896 FETCH get_profile_option_values INTO
897 l_level_id,
898 l_level_value,
899 l_level_value_appl_id,
900 l_profile_option_value;
901 WHILE get_profile_option_values%FOUND LOOP
902 IF (l_level_id = 10001) THEN
903 -- check if the value set at site is equal to
904 -- value seeded out-of-the-box
905 IF (l_profile_option_value <> 'SHOWDURINGBOTH') THEN
906 log_mesg(FND_LOG.LEVEL_STATEMENT,'CS_CF_UPG_UTL_PKG.Eval_SR_Attachment_Option', 'site level value customized. Profile option value: ' || l_profile_option_value);
907 p_siteProfilesTable(p_site_index).profileOptionName := 'IBU_A_SR_ATTACHMENT_OPTION';
908 p_siteProfilesTable(p_site_index).profileOptionValue := l_profile_option_value;
909 p_site_index := p_site_index + 1;
910 l_upgrade_required := TRUE;
911 END IF;
912 ELSIF (l_level_id = 10002) THEN
913 -- there should not have been a value set at the appl level
914 -- if there is a value, then it is customized
915 l_upgrade_required := TRUE;
916 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Attachment_Option', 'appl level value customized. Profile option value: ' || l_profile_option_value);
917 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Attachment_Option', 'Application Id: ' || to_char(l_level_value));
918 IF NOT(Appl_Already_Exists(p_applTable, l_level_value)) THEN
919 p_applTable(p_appl_index) := l_level_value;
920 p_appl_index := p_appl_index + 1;
921 END IF;
922 ELSIF (l_level_id = 10003) THEN
923 -- there should not have been a value set at the resp level
924 -- If there is a value, then it is customized
925 l_upgrade_required := TRUE;
926 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Attachment_Option', 'resp level value customized. Profile option value: ' || l_profile_option_value);
927 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Attachment_Option','Resp id: ' || to_char(l_level_value) || ' Resp Appl Id: ' || to_char(l_level_value_appl_id));
928 l_upgrade_required := TRUE;
929 IF NOT (Resp_Already_Exists(p_respTable, l_level_value, l_level_value_appl_id)) THEN
930 p_respTable(p_resp_index).respId := l_level_value;
931 p_respTable(p_resp_index).respApplId := l_level_value_appl_id;
932 p_resp_index := p_resp_index + 1;
933 END IF;
934 END IF;
935 FETCH get_profile_option_values INTO
936 l_level_id,
937 l_level_value,
938 l_level_value_appl_id,
939 l_profile_option_value;
940 END LOOP;
941 CLOSE get_profile_option_values;
942
943 return l_upgrade_required;
944
945 EXCEPTION
946 WHEN OTHERS THEN
947 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG.Eval_SR_Attachment_Option', 'Exception in Eval_SR_ShipTo_Address_Option');
948 IF (get_profile_option_values%ISOPEN) THEN
949 CLOSE get_profile_option_values;
950 END IF;
951 RAISE;
952
953 END Eval_SR_Attachment_Option;
954
955 /*
956 * Compare values for the IBU_SR_TASK_DISPLAY profile
957 * Oracle iSupport: Show Task in Service Request Module
958 */
959
960 FUNCTION Eval_SR_Task_Display (p_appl_index IN OUT NOCOPY NUMBER,
961 p_ApplTable IN OUT NOCOPY ApplTable,
962 p_resp_index IN OUT NOCOPY NUMBER,
963 p_RespTable IN OUT NOCOPY RespTable,
964 p_site_index IN OUT NOCOPY NUMBER,
965 p_siteProfilesTable IN OUT NOCOPY ProfileTable)
966 RETURN BOOLEAN
967 IS
968
969 l_profile_option_id FND_PROFILE_OPTION_VALUES.profile_option_id%TYPE := 0;
970 l_level_id FND_PROFILE_OPTION_VALUES.level_id%TYPE := 0;
971 l_level_value FND_PROFILE_OPTION_VALUES.level_value%TYPE := 0;
972 l_level_value_appl_id FND_PROFILE_OPTION_VALUES.level_value_application_id%TYPE := 0;
973 l_profile_option_value FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
974
975 l_upgrade_required BOOLEAN := FALSE;
976
977 BEGIN
978 OPEN get_profile_option_values('IBU_SR_TASK_DISPLAY');
979 FETCH get_profile_option_values INTO
980 l_level_id,
981 l_level_value,
982 l_level_value_appl_id,
983 l_profile_option_value;
984 WHILE get_profile_option_values%FOUND LOOP
985 IF (l_level_id = 10001) THEN
986 IF (l_profile_option_value <> 'N') THEN
987 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Task_Display', 'site level value customized. Profile option value: ' || l_profile_option_value);
988 p_siteProfilesTable(p_site_index).profileOptionName := 'IBU_SR_TASK_DISPLAY';
989 p_siteProfilesTable(p_site_index).profileOptionValue := l_profile_option_value;
990 p_site_index := p_site_index + 1;
991 l_upgrade_required := TRUE;
992 END IF;
993 ELSIF (l_level_id = 10002) THEN
994 -- There wasn't any seeded value for the app level. Therefore,
995 -- this must be a customization
996 l_upgrade_required := TRUE;
997 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Task_Display', 'appl level value customized. Profile option value: ' || l_profile_option_value);
998 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Task_Display', 'Application Id: ' || to_char(l_level_value));
999 IF NOT (Appl_Already_Exists(p_ApplTable, l_level_value)) THEN
1000 p_ApplTable(p_appl_index) := l_level_value;
1001 p_appl_index := p_appl_index + 1;
1002 END IF;
1003 ELSIF (l_level_id = 10003) THEN
1004 -- There wasn't any seeded value for the resp level. Therefore,
1005 -- this must be a customization
1006 l_upgrade_required := TRUE;
1007 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Task_Display', 'resp level value customized. Profile option value: ' || l_profile_option_value);
1008 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Task_Display', 'Resp id: ' || to_char(l_level_value) || ' Resp Appl Id: ' || to_char(l_level_value_appl_id));
1009 IF NOT(Resp_Already_Exists(p_RespTable, l_level_value, l_level_value_appl_id)) THEN
1010 p_RespTable(p_resp_index).respId := l_level_value;
1011 p_RespTable(p_resp_index).respApplId := l_level_value_appl_id;
1012 p_resp_index := p_resp_index + 1;
1013 END IF;
1014 END IF;
1015 FETCH get_profile_option_values INTO
1016 l_level_id,
1017 l_level_value,
1018 l_level_value_appl_id,
1019 l_profile_option_value;
1020 END LOOP;
1021 CLOSE get_profile_option_values;
1022
1023 return l_upgrade_required;
1024
1025 EXCEPTION
1026 WHEN OTHERS THEN
1027 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG.Eval_SR_Task_Display','Exception in Eval_SR_ShipTo_Address_Option');
1028 IF (get_profile_option_values%ISOPEN) THEN
1029 CLOSE get_profile_option_values;
1030 END IF;
1031 RAISE;
1032
1033 END Eval_SR_Task_Display;
1034
1035 /*
1036 * Compare values for the IBU_A_SR_ENABLE_INTERACTION_LOGGING profile
1037 * Oracle iSupport: Enable Interaction Logging
1038 */
1039
1040 FUNCTION Eval_SR_Enable_Interact_Log (p_resp_index IN OUT NOCOPY NUMBER,
1041 p_respTable IN OUT NOCOPY RespTable,
1042 p_appl_index IN OUT NOCOPY NUMBER,
1043 p_applTable IN OUT NOCOPY ApplTable,
1044 p_site_index IN OUT NOCOPY NUMBER,
1045 p_siteProfilesTable IN OUT NOCOPY ProfileTable)
1046 RETURN BOOLEAN
1047 IS
1048
1049 l_profile_option_id FND_PROFILE_OPTION_VALUES.profile_option_id%TYPE := 0;
1050 l_level_id FND_PROFILE_OPTION_VALUES.level_id%TYPE := 0;
1051 l_level_value FND_PROFILE_OPTION_VALUES.level_value%TYPE := 0;
1052 l_level_value_appl_id FND_PROFILE_OPTION_VALUES.level_value_application_id%TYPE := 0;
1053 l_profile_option_value FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
1054
1055 l_upgrade_required BOOLEAN := FALSE;
1056
1057 BEGIN
1058
1059 OPEN get_profile_option_values('IBU_A_SR_ENABLE_INTERACTION_LOGGING');
1060 FETCH get_profile_option_values INTO
1061 l_level_id,
1062 l_level_value,
1063 l_level_value_appl_id,
1064 l_profile_option_value;
1065 WHILE get_profile_option_values%FOUND LOOP
1066 IF (l_level_id = 10001) THEN
1067 -- check if the value set at site is equal to
1068 -- value seeded out-of-the-box
1069 IF (l_profile_option_value <> 'N') THEN
1070 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Enable_Interaction_Logging', 'site level value customized. Profile option value: ' || l_profile_option_value);
1071 p_siteProfilesTable(p_site_index).profileOptionName := 'IBU_A_SR_ENABLE_INTERACTION_LOGGING';
1072 p_siteProfilesTable(p_site_index).profileOptionValue := l_profile_option_value;
1073 p_site_index := p_site_index + 1;
1074 l_upgrade_required := TRUE;
1075 END IF;
1076 ELSIF (l_level_id = 10002) THEN
1077 -- There wasn't any seeded value for the app level. Therefore,
1078 -- this must be a customization
1079 l_upgrade_required := TRUE;
1080 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Enable_Interaction_Logging', 'appl level value customized. Profile option value: ' || l_profile_option_value);
1081 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Enable_Interaction_Logging', 'Application Id: ' || to_char(l_level_value));
1082 IF NOT (Appl_Already_Exists(p_ApplTable, l_level_value)) THEN
1083 p_ApplTable(p_appl_index) := l_level_value;
1084 p_appl_index := p_appl_index + 1;
1085 END IF;
1086 ELSIF (l_level_id = 10003) THEN
1087 -- there should not have been a value set at the resp level
1088 -- If there is a value, then it is customized
1089 l_upgrade_required := TRUE;
1090 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Enable_Interaction_Logging', 'resp level value customized. Profile option value: ' || l_profile_option_value);
1091 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Enable_Interaction_Logging', 'Resp id: ' || to_char(l_level_value) || ' Resp Appl Id: ' || to_char(l_level_value_appl_id));
1092 IF NOT (Resp_Already_Exists(p_respTable, l_level_value, l_level_value_appl_id)) THEN
1093 p_respTable(p_resp_index).respId := l_level_value;
1094 p_respTable(p_resp_index).respApplId := l_level_value_appl_id;
1095 p_resp_index := p_resp_index + 1;
1096 END IF;
1097 END IF;
1098 FETCH get_profile_option_values INTO
1099 l_level_id,
1100 l_level_value,
1101 l_level_value_appl_id,
1102 l_profile_option_value;
1103 END LOOP;
1104 CLOSE get_profile_option_values;
1105
1106 return l_upgrade_required;
1107
1108 EXCEPTION
1109 WHEN OTHERS THEN
1110 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG.Eval_SR_Enable_Interact_Log', 'Exception in Eval_SR_Enable_Interact_Log');
1111 IF (get_profile_option_values%ISOPEN) THEN
1112 CLOSE get_profile_option_values;
1113 END IF;
1114 RAISE;
1115
1116 END Eval_SR_Enable_Interact_Log;
1117
1118 /*
1119 * Compare values for the IBU_A_SR_KB_OPTION profile
1120 * Oracle iSupport: Search Knowledge Base Option
1121 */
1122
1123 FUNCTION Eval_SR_KB_Option (p_appl_index IN OUT NOCOPY NUMBER,
1124 p_applTable IN OUT NOCOPY ApplTable,
1125 p_resp_index IN OUT NOCOPY NUMBER,
1126 p_respTable IN OUT NOCOPY RespTable,
1127 p_site_index IN OUT NOCOPY NUMBER,
1128 p_siteProfilesTable IN OUT NOCOPY ProfileTable)
1129 RETURN BOOLEAN
1130 IS
1131
1132 l_profile_option_id FND_PROFILE_OPTION_VALUES.profile_option_id%TYPE := 0;
1133 l_level_id FND_PROFILE_OPTION_VALUES.level_id%TYPE := 0;
1134 l_level_value FND_PROFILE_OPTION_VALUES.level_value%TYPE := 0;
1135 l_level_value_appl_id FND_PROFILE_OPTION_VALUES.level_value_application_id%TYPE := 0;
1136 l_profile_option_value FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
1137
1138 l_upgrade_required BOOLEAN := FALSE;
1139
1140 BEGIN
1141 OPEN get_profile_option_values('IBU_A_SR_KB_OPTION');
1142 FETCH get_profile_option_values INTO
1143 l_level_id,
1144 l_level_value,
1145 l_level_value_appl_id,
1146 l_profile_option_value;
1147 WHILE get_profile_option_values%FOUND LOOP
1148 IF (l_level_id = 10001) THEN
1149 IF (l_profile_option_value <> 'PROMPT') THEN
1150 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_KB_Option','site level value customized. Profile option value: ' || l_profile_option_value);
1151 p_siteProfilesTable(p_site_index).profileOptionName := 'IBU_A_SR_KB_OPTION';
1152 p_siteProfilesTable(p_site_index).profileOptionValue := l_profile_option_value;
1153 p_site_index := p_site_index + 1;
1154
1155 l_upgrade_required := TRUE;
1156 END IF;
1157 ELSIF (l_level_id = 10002) THEN
1158 -- There wasn't any seeded value for the app level. Therefore,
1159 -- this must be a customization
1160 l_upgrade_required := TRUE;
1161 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_KB_Option', 'appl level value customized. Profile option value: ' || l_profile_option_value);
1162 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_KB_Option', 'Application Id: ' || to_char(l_level_value));
1163 IF NOT (Appl_Already_Exists(p_ApplTable, l_level_value)) THEN
1164 p_ApplTable(p_appl_index) := l_level_value;
1165 p_appl_index := p_appl_index + 1;
1166 END IF;
1167 ELSIF (l_level_id = 10003) THEN
1168 -- There wasn't any seeded value for the resp level. Therefore,
1169 -- this must be a customization
1170 l_upgrade_required := TRUE;
1171 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_KB_Option', 'resp level value customized. Profile option value: ' || l_profile_option_value);
1172 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_KB_Option', 'Resp id: ' || to_char(l_level_value) || ' Resp Appl Id: ' || to_char(l_level_value_appl_id));
1173 IF NOT (Resp_Already_Exists(p_RespTable, l_level_value, l_level_value_appl_id)) THEN
1174 p_RespTable(p_resp_index).respId := l_level_value;
1175 p_RespTable(p_resp_index).respApplId := l_level_value_appl_id;
1176 p_resp_index := p_resp_index + 1;
1177 END IF;
1178 END IF;
1179 FETCH get_profile_option_values INTO
1180 l_level_id,
1181 l_level_value,
1182 l_level_value_appl_id,
1183 l_profile_option_value;
1184 END LOOP;
1185 CLOSE get_profile_option_values;
1186
1187 return l_upgrade_required;
1188
1189 EXCEPTION
1190 WHEN OTHERS THEN
1191 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG:Eval_SR_ShipTo_Address_Option', 'Exception in Eval_SR_ShipTo_Address_Option');
1192 IF (get_profile_option_values%ISOPEN) THEN
1193 CLOSE get_profile_option_values;
1194 END IF;
1195 RAISE;
1196
1197 END Eval_SR_KB_OPTION;
1198
1199 /*
1200 * Compare values for the IBU_SR_ENABLE_TEMPLATE profile
1201 * Oracle iSupport: Enable Service Request Template
1202 */
1203
1204 FUNCTION Eval_SR_Enable_Template (p_appl_index IN OUT NOCOPY NUMBER,
1205 p_applTable IN OUT NOCOPY ApplTable,
1206 p_resp_index IN OUT NOCOPY NUMBER,
1207 p_respTable IN OUT NOCOPY RespTable,
1208 p_site_index IN OUT NOCOPY NUMBER,
1209 p_siteProfilesTable IN OUT NOCOPY ProfileTable)
1210 RETURN BOOLEAN
1211 IS
1212
1213 l_profile_option_id FND_PROFILE_OPTION_VALUES.profile_option_id%TYPE := 0;
1214 l_level_id FND_PROFILE_OPTION_VALUES.level_id%TYPE := 0;
1215 l_level_value FND_PROFILE_OPTION_VALUES.level_value%TYPE := 0;
1216 l_level_value_appl_id FND_PROFILE_OPTION_VALUES.level_value_application_id%TYPE := 0;
1217 l_profile_option_value FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
1218
1219 l_upgrade_required BOOLEAN := FALSE;
1220
1221 BEGIN
1222 OPEN get_profile_option_values('IBU_SR_ENABLE_TEMPLATE');
1223 FETCH get_profile_option_values INTO
1224 l_level_id,
1225 l_level_value,
1226 l_level_value_appl_id,
1227 l_profile_option_value;
1228 WHILE get_profile_option_values%FOUND LOOP
1229 IF (l_level_id = 10001) THEN
1230 IF (l_profile_option_value <> 'Y') THEN
1231 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Enable_Template', 'site level value customized. Profile option value: ' || l_profile_option_value);
1232 p_siteProfilesTable(p_site_index).profileOptionName := 'IBU_SR_ENABLE_TEMPLATE';
1233 p_siteProfilesTable(p_site_index).profileOptionValue := l_profile_option_value;
1234 p_site_index := p_site_index + 1;
1235 l_upgrade_required := TRUE;
1236 END IF;
1237 ELSIF (l_level_id = 10002) THEN
1238 IF(l_profile_option_value <> 'Y') THEN
1239 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Enable_Template', 'appl level value customized. Profile option value: ' || l_profile_option_value);
1240 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Enable_Template', 'Application Id: ' || to_char(l_level_value));
1241 IF NOT (Appl_Already_Exists(p_ApplTable, l_level_value)) THEN
1242 p_ApplTable(p_appl_index) := l_level_value;
1243 p_appl_index := p_appl_index + 1;
1244 END IF;
1245 l_upgrade_required := TRUE;
1246 END IF;
1247 ELSIF (l_level_id = 10003) THEN
1248 -- There wasn't any seeded value for the resp level. Therefore,
1249 -- this must be a customization
1250 l_upgrade_required := TRUE;
1251 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Enable_Template', 'resp level value customized. Profile option value: ' || l_profile_option_value);
1252 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Enable_Template','Resp id: ' || to_char(l_level_value) || ' Resp Appl Id: ' || to_char(l_level_value_appl_id));
1253 IF NOT (Resp_Already_Exists(p_RespTable, l_level_value, l_level_value_appl_id)) THEN
1254 p_RespTable(p_resp_index).respId := l_level_value;
1255 p_RespTable(p_resp_index).respApplId := l_level_value_appl_id;
1256 p_resp_index := p_resp_index + 1;
1257 END IF;
1258 END IF;
1259 FETCH get_profile_option_values INTO
1260 l_level_id,
1261 l_level_value,
1262 l_level_value_appl_id,
1263 l_profile_option_value;
1264 END LOOP;
1265 CLOSE get_profile_option_values;
1266
1267 return l_upgrade_required;
1268
1269 EXCEPTION
1270 WHEN OTHERS THEN
1271 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG.Eval_SR_Enable_Template','Exception in Eval_SR_Enable_Template');
1272 IF (get_profile_option_values%ISOPEN) THEN
1273 CLOSE get_profile_option_values;
1274 END IF;
1275 RAISE;
1276
1277 END Eval_SR_Enable_Template;
1278
1279 /*
1280 * Compare values for the IBU_A_SR_PRODUCT_SELECTION_OPTION profile
1281 * Oracle iSupport: Enforce Product Selection Option
1282 */
1283
1284 FUNCTION Eval_SR_Product_Selection (p_appl_index IN OUT NOCOPY NUMBER,
1285 p_applTable IN OUT NOCOPY ApplTable,
1286 p_resp_index IN OUT NOCOPY NUMBER,
1287 p_respTable IN OUT NOCOPY RespTable,
1288 p_site_index IN OUT NOCOPY NUMBER,
1289 p_siteProfilesTable IN OUT NOCOPY ProfileTable)
1290 RETURN BOOLEAN
1291 IS
1292
1293 l_profile_option_id FND_PROFILE_OPTION_VALUES.profile_option_id%TYPE := 0;
1294 l_level_id FND_PROFILE_OPTION_VALUES.level_id%TYPE := 0;
1295 l_level_value FND_PROFILE_OPTION_VALUES.level_value%TYPE := 0;
1296 l_level_value_appl_id FND_PROFILE_OPTION_VALUES.level_value_application_id%TYPE := 0;
1297 l_profile_option_value FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
1298
1299 l_upgrade_required BOOLEAN := FALSE;
1300
1301 BEGIN
1302 OPEN get_profile_option_values('IBU_A_SR_PRODUCT_SELECTION_OPTION');
1303 FETCH get_profile_option_values INTO
1304 l_level_id,
1305 l_level_value,
1306 l_level_value_appl_id,
1307 l_profile_option_value;
1308 WHILE get_profile_option_values%FOUND LOOP
1309 IF (l_level_id = 10001) THEN
1310 IF (l_profile_option_value <> 'Y') THEN
1311 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Enable_Template', 'site level value customized. Profile option value: ' || l_profile_option_value);
1312 p_siteProfilesTable(p_site_index).profileOptionName := 'IBU_SR_ENABLE_TEMPLATE';
1313 p_siteProfilesTable(p_site_index).profileOptionValue := l_profile_option_value;
1314 p_site_index := p_site_index + 1;
1315 l_upgrade_required := TRUE;
1316 END IF;
1317 ELSIF (l_level_id = 10002) THEN
1318 -- there wasn't any seeded value for the appl level. Therefore,
1319 -- this must be a customization
1320 l_upgrade_required := TRUE;
1321 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Product_Selection', 'appl level value customized. Profile option value: ' || l_profile_option_value);
1322 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Product_Selection', 'Application Id: ' || to_char(l_level_value));
1323 IF NOT (Appl_Already_Exists(p_ApplTable, l_level_value)) THEN
1324 p_ApplTable(p_appl_index) := l_level_value;
1325 p_appl_index := p_appl_index + 1;
1326 END IF;
1327 ELSIF (l_level_id = 10003) THEN
1328 -- There wasn't any seeded value for the resp level. Therefore,
1329 -- this must be a customization
1330 l_upgrade_required := TRUE;
1331 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Product_Selection', 'resp level value customized. Profile option value: ' || l_profile_option_value);
1332 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Eval_SR_Product_Selection','Resp id: ' || to_char(l_level_value) || ' Resp Appl Id: ' || to_char(l_level_value_appl_id));
1333 IF NOT (Resp_Already_Exists(p_RespTable, l_level_value, l_level_value_appl_id)) THEN
1334 p_RespTable(p_resp_index).respId := l_level_value;
1335 p_RespTable(p_resp_index).respApplId := l_level_value_appl_id;
1336 p_resp_index := p_resp_index + 1;
1337 END IF;
1338 END IF;
1339 FETCH get_profile_option_values INTO
1340 l_level_id,
1341 l_level_value,
1342 l_level_value_appl_id,
1343 l_profile_option_value;
1344 END LOOP;
1345 CLOSE get_profile_option_values;
1346
1347 return l_upgrade_required;
1348
1349 EXCEPTION
1350 WHEN OTHERS THEN
1351 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG.Eval_SR_Product_Selection','Exception in Eval_SR_Product_Selection');
1352 IF (get_profile_option_values%ISOPEN) THEN
1353 CLOSE get_profile_option_values;
1354 END IF;
1355 RAISE;
1356
1357 END Eval_SR_Product_Selection;
1358
1359
1360 /*
1361 * Checks whether a specific responsibility
1362 * has already been added to the table
1363 */
1364 FUNCTION Resp_Already_Exists(p_RespTable IN RespTable,
1365 p_level_value IN NUMBER,
1366 p_level_value_application_id IN NUMBER)
1367 RETURN BOOLEAN
1368 IS
1369
1370 l_found BOOLEAN := FALSE;
1371 l_count NUMBER := p_RespTable.COUNT;
1372 l_index NUMBER := 0;
1373
1374 BEGIN
1375 WHILE ((l_index < l_count) AND l_found = FALSE) LOOP
1376 IF (p_RespTable(l_index).respId = p_level_value
1377 AND p_RespTable(l_index).respApplId = p_level_value_application_id) THEN
1378 l_found := TRUE;
1379 END IF;
1380 l_index := l_index + 1;
1381 END LOOP;
1382
1383 RETURN l_found;
1384
1385 END Resp_Already_Exists;
1386
1387 /*
1388 * Checks whether a specific application has
1389 * already been added to the table
1390 */
1391 FUNCTION Appl_Already_Exists(p_ApplTable IN ApplTable,
1392 p_level_value IN NUMBER)
1393 RETURN BOOLEAN
1394 IS
1395
1396 l_found BOOLEAN := FALSE;
1397 l_count NUMBER := p_ApplTable.COUNT;
1398 l_index NUMBER := 0;
1399
1400 BEGIN
1401 WHILE ((l_index < l_count) AND l_found=FALSE) LOOP
1402 IF (p_ApplTable(l_index) = p_level_value) THEN
1403 l_found := TRUE;
1404 END IF;
1405 l_index := l_index + 1;
1406 END LOOP;
1407
1408 RETURN l_found;
1409
1410 END Appl_Already_Exists;
1411
1412 /*
1413 * This procedure inserts a new row into CS_CF_SOURCE_CONTEXT_TARGETS
1414 * table for the newly cloned regions
1415 */
1416
1417 PROCEDURE Insert_New_Target(p_sourceCode IN VARCHAR2,
1418 p_contextType IN VARCHAR2,
1419 p_contextValue1 IN VARCHAR2,
1420 p_contextValue2 IN VARCHAR2,
1421 p_seedTargetValue1 IN VARCHAR2,
1422 p_seedTargetValue2 IN VARCHAR2,
1423 p_custTargetValue1 IN VARCHAR2,
1424 p_custTargetValue2 IN VARCHAR2)
1425 IS
1426
1427 no_row_found EXCEPTION;
1428
1429 l_source_context_type_id NUMBER := 0;
1430 l_source_context_target_id NUMBER := 0;
1431 l_count NUMBER := 0;
1432 l_rowid VARCHAR2(50) := '';
1433 l_created_by NUMBER := FND_LOAD_UTIL.OWNER_ID('ORACLE');
1434
1435 u_source_context_target_id NUMBER := 0;
1436 u_context_value1 VARCHAR2(10) := 0;
1437 u_context_value2 VARCHAR2(10) := 0;
1438 u_context_value3 VARCHAR2(10) := 0;
1439 u_context_value4 VARCHAR2(10) := 0;
1440 u_context_value5 VARCHAR2(10) := 0;
1441 u_object_version_number NUMBER := 0;
1442 u_seed_target_value1 VARCHAR2(30) := '';
1443 u_seed_target_value2 VARCHAR2(30) := '';
1444 u_cust_target_value1 VARCHAR2(30) := '';
1445 u_cust_target_value2 VARCHAR2(30) := '';
1446 u_created_by NUMBER := 2;
1447 u_creation_date DATE;
1448 u_last_updated_by NUMBER := 0;
1449 u_last_update_date DATE;
1450 u_last_update_login NUMBER := 0;
1451 u_attribute_category VARCHAR2(30) := '';
1452 u_attribute1 VARCHAR2(150) := '';
1453 u_attribute2 VARCHAR2(150) := '';
1454 u_attribute3 VARCHAR2(150) := '';
1455 u_attribute4 VARCHAR2(150) := '';
1456 u_attribute5 VARCHAR2(150) := '';
1457 u_attribute6 VARCHAR2(150) := '';
1458 u_attribute7 VARCHAR2(150) := '';
1459 u_attribute8 VARCHAR2(150) := '';
1460 u_attribute9 VARCHAR2(150) := '';
1461 u_attribute10 VARCHAR2(150) := '';
1462 u_attribute11 VARCHAR2(150) := '';
1463 u_attribute12 VARCHAR2(150) := '';
1464 u_attribute13 VARCHAR2(150) := '';
1465 u_attribute14 VARCHAR2(150) := '';
1466 u_attribute15 VARCHAR2(150) := '';
1467 u_additional_info1 VARCHAR2(150) := '';
1468 u_additional_info2 VARCHAR2(150) := '';
1469 u_additional_info3 VARCHAR2(150) := '';
1470 u_additional_info4 VARCHAR2(150) := '';
1471 u_additional_info5 VARCHAR2(150) := '';
1472 u_additional_info6 VARCHAR2(150) := '';
1473 u_additional_info7 VARCHAR2(150) := '';
1474 u_additional_info8 VARCHAR2(150) := '';
1475 u_additional_info9 VARCHAR2(150) := '';
1476 u_additional_info10 VARCHAR2(150) := '';
1477 u_additional_info11 VARCHAR2(150) := '';
1478 u_additional_info12 VARCHAR2(150) := '';
1479 u_additional_info13 VARCHAR2(150) := '';
1480 u_additional_info14 VARCHAR2(150) := '';
1481 u_additional_info15 VARCHAR2(150) := '';
1482
1483
1484
1485
1486
1487 CURSOR source_context_type_id (p_sourceCode VARCHAR2, p_contextType VARCHAR2)
1488 IS
1489 SELECT source_context_type_id
1490 FROM CS_CF_SOURCE_CXT_TYPES
1491 WHERE SOURCE_CODE = p_sourceCode
1492 AND CONTEXT_TYPE = p_contextType;
1493
1494 CURSOR target_count_resp (p_contextTypeId NUMBER, p_contextValue1 VARCHAR2, p_contextValue2 VARCHAR2)
1495 IS
1496 SELECT count(*)
1497 FROM CS_CF_SOURCE_CXT_TARGETS
1498 WHERE source_context_type_id = p_contextTypeId
1499 AND context_value1 = p_contextValue1
1500 AND context_value2 = p_contextValue2;
1501
1502 CURSOR target_count_appl (p_contextTypeId NUMBER, p_contextValue1 VARCHAR2)
1503 IS
1504 SELECT count(*)
1505 FROM CS_CF_SOURCE_CXT_TARGETS
1506 WHERE source_context_type_id = p_contextTypeId
1507 AND context_value1 = p_contextValue1;
1508
1509 CURSOR target_count_global (p_contextTypeId NUMBER)
1510 IS
1511 SELECT count(*)
1512 FROM CS_CF_SOURCE_CXT_TARGETS
1513 WHERE source_context_type_id = p_contextTypeId;
1514
1515 CURSOR target_values_global (p_contextTypeId NUMBER)
1516 IS
1517 SELECT source_context_target_id,
1518 context_value1,
1519 context_value2,
1520 context_value3,
1521 context_value4,
1522 context_value5,
1523 object_version_number,
1524 seed_target_value1,
1525 seed_target_value2,
1526 cust_target_value1,
1527 cust_target_value2,
1528 created_by,
1529 creation_date,
1530 last_updated_by,
1531 last_update_date,
1532 last_update_login,
1533 attribute_category,
1534 attribute1,
1535 attribute2,
1536 attribute3,
1537 attribute4,
1538 attribute5,
1539 attribute6,
1540 attribute7,
1541 attribute8,
1542 attribute9,
1543 attribute10,
1544 attribute11,
1545 attribute12,
1546 attribute13,
1547 attribute14,
1548 attribute15,
1549 additional_info1,
1550 additional_info2,
1551 additional_info3,
1552 additional_info4,
1553 additional_info5,
1554 additional_info6,
1555 additional_info7,
1556 additional_info8,
1557 additional_info9,
1558 additional_info10,
1559 additional_info11,
1560 additional_info12,
1561 additional_info13,
1562 additional_info14,
1563 additional_info15
1564 FROM CS_CF_SOURCE_CXT_TARGETS
1565 WHERE source_context_type_id = p_contextTypeId;
1566
1567 BEGIN
1568 log_mesg(FND_LOG.LEVEL_STATEMENT,'CS_CF_UPG_UTL_PKG.Insert_New_Target', 'Called Inserting new target row for sourceCode: ' || p_sourceCode || ' contextType: ' || p_contextType);
1569
1570 OPEN source_context_type_id(p_sourceCode, p_contextType);
1571 FETCH source_context_type_id INTO l_source_context_type_id;
1572 CLOSE source_context_type_id;
1573
1574 IF (p_contextType = 'RESP') THEN
1575 OPEN target_count_resp (l_source_context_type_id, p_contextValue1, p_contextValue2);
1576 FETCH target_count_resp INTO l_count;
1577 CLOSE target_count_resp;
1578
1579 ELSIF (p_contextType = 'APPLICATION') THEN
1580 OPEN target_count_appl (l_source_context_type_id, p_contextValue1);
1581 FETCH target_count_appl INTO l_count;
1582 CLOSE target_count_appl;
1583
1584 ELSIF (p_contextType = 'GLOBAL') THEN
1585 OPEN target_count_global(l_source_context_type_id);
1586 FETCH target_count_global INTO l_count;
1587 CLOSE target_count_global;
1588
1589 IF (l_count > 0) THEN
1590 OPEN target_values_global(l_source_context_type_id);
1591 FETCH target_values_global
1592 INTO u_source_context_target_id,
1593 u_context_value1,
1594 u_context_value2,
1595 u_context_value3,
1596 u_context_value4,
1597 u_context_value5,
1598 u_object_version_number,
1599 u_seed_target_value1,
1600 u_seed_target_value2,
1601 u_cust_target_value1,
1602 u_cust_target_value2,
1603 u_created_by,
1604 u_creation_date,
1605 u_last_updated_by,
1606 u_last_update_date,
1607 u_last_update_login,
1608 u_attribute_category,
1609 u_attribute1,
1610 u_attribute2,
1611 u_attribute3,
1612 u_attribute4,
1613 u_attribute5,
1614 u_attribute6,
1615 u_attribute7,
1616 u_attribute8,
1617 u_attribute9,
1618 u_attribute10,
1619 u_attribute11,
1620 u_attribute12,
1621 u_attribute13,
1622 u_attribute14,
1623 u_attribute15,
1624 u_additional_info1,
1625 u_additional_info2,
1626 u_additional_info3,
1627 u_additional_info4,
1628 u_additional_info5,
1629 u_additional_info6,
1630 u_additional_info7,
1631 u_additional_info8,
1632 u_additional_info9,
1633 u_additional_info10,
1634 u_additional_info11,
1635 u_additional_info12,
1636 u_additional_info13,
1637 u_additional_info14,
1638 u_additional_info15;
1639
1640 CLOSE target_values_global;
1641 log_mesg(FND_LOG.LEVEL_STATEMENT,'CS_CF_UPG_UTL_PKG.Insert_New_Target', 'Global context: last_updated_by:' || u_last_updated_by || 'created_by:' || u_created_by);
1642 END IF;
1643 END IF;
1644
1645 IF (p_contextType <> 'GLOBAL' AND (l_count = 0)) THEN
1646 SELECT cs_cf_source_cxt_targets_s.nextval
1647 INTO l_source_context_target_id
1648 FROM dual;
1649
1650 log_mesg(FND_LOG.LEVEL_STATEMENT,'CS_CF_UPG_UTL_PKG.Insert_New_Target', 'Inserting new target row for sourceCode: ' || p_sourceCode || ' contextTargetId: ' || l_source_context_target_id);
1651 log_mesg(FND_LOG.LEVEL_STATEMENT,'CS_CF_UPG_UTL_PKG.Insert_New_Target', ' contextTypeId: ' || l_source_context_type_id || ' p_contextValue1: ' || p_contextValue1 || ' p_contextValue2: ' || p_contextValue2 );
1652
1653 CS_CF_SOURCE_CXT_TARGETS_PKG.Insert_Row(
1654 X_ROWID => l_rowid,
1655 X_SOURCE_CONTEXT_TARGET_ID => l_source_context_target_id,
1656 X_SOURCE_CONTEXT_TYPE_ID => l_source_context_type_id,
1657 X_CONTEXT_VALUE1 => p_contextValue1,
1658 X_CONTEXT_VALUE2 => p_contextValue2,
1659 X_CONTEXT_VALUE3 => NULL,
1660 X_CONTEXT_VALUE4 => NULL,
1661 X_CONTEXT_VALUE5 => NULL,
1662 X_SEED_TARGET_VALUE1 => p_seedTargetValue1,
1663 X_SEED_TARGET_VALUE2 => p_seedTargetValue2,
1664 X_CUST_TARGET_VALUE1 => p_custTargetValue1,
1665 X_CUST_TARGET_VALUE2 => p_custTargetValue2,
1666 X_OBJECT_VERSION_NUMBER => 1,
1667 X_ATTRIBUTE_CATEGORY => NULL,
1668 X_ATTRIBUTE1 => NULL,
1669 X_ATTRIBUTE2 => NULL,
1670 X_ATTRIBUTE3 => NULL,
1671 X_ATTRIBUTE4 => NULL,
1672 X_ATTRIBUTE5 => NULL,
1673 X_ATTRIBUTE6 => NULL,
1674 X_ATTRIBUTE7 => NULL,
1675 X_ATTRIBUTE8 => NULL,
1676 X_ATTRIBUTE9 => NULL,
1677 X_ATTRIBUTE10 => NULL,
1678 X_ATTRIBUTE11 => NULL,
1679 X_ATTRIBUTE12 => NULL,
1680 X_ATTRIBUTE13 => NULL,
1681 X_ATTRIBUTE14 => NULL,
1682 X_ATTRIBUTE15 => NULL,
1683 X_ADDITIONAL_INFO1 => NULL,
1684 X_ADDITIONAL_INFO2 => NULL,
1685 X_ADDITIONAL_INFO3 => NULL,
1686 X_ADDITIONAL_INFO4 => NULL,
1687 X_ADDITIONAL_INFO5 => NULL,
1688 X_ADDITIONAL_INFO6 => NULL,
1689 X_ADDITIONAL_INFO7 => NULL,
1690 X_ADDITIONAL_INFO8 => NULL,
1691 X_ADDITIONAL_INFO9 => NULL,
1692 X_ADDITIONAL_INFO10 => NULL,
1693 x_ADDITIONAL_INFO11 => NULL,
1694 X_ADDITIONAL_INFO12 => NULL,
1695 X_ADDITIONAL_INFO13 => NULL,
1696 X_ADDITIONAL_INFO14 => NULL,
1697 X_ADDITIONAL_INFO15 => NULL,
1698 X_CREATION_DATE => sysdate,
1699 X_CREATED_BY => l_created_by,
1700 X_LAST_UPDATE_DATE => sysdate,
1701 X_LAST_UPDATED_BY => l_created_by,
1702 X_LAST_UPDATE_LOGIN => 0);
1703
1704 ELSIF(p_contextType = 'GLOBAL' and l_count = 0) THEN
1705 RAISE no_row_found;
1706
1707 ELSIF (p_contextType = 'GLOBAL' AND (l_count > 0 AND ((u_last_updated_by = u_created_by) OR (u_last_updated_by = -1)))) THEN
1708
1709 -- mkcyee 12/09/2004
1710 -- Update the existing row in place. This is a special
1711 -- case for global regions because there will already be an existing
1712 -- row present after installation.
1713 -- We also keep the last_update_date to be the same because
1714 -- we want future ldt changes to overwrite this row.
1715
1716 log_mesg(FND_LOG.LEVEL_STATEMENT,'CS_CF_UPG_UTL_PKG.Insert_New_Target', 'Updating target row for sourceCode: ' || p_sourceCode || ' contextTargetId: ' || l_source_context_target_id);
1717 log_mesg(FND_LOG.LEVEL_STATEMENT,'CS_CF_UPG_UTL_PKG.Insert_New_Target', ' contextTypeId: ' || l_source_context_type_id || ' p_contextValue1: ' || p_contextValue1 || ' p_contextValue2: ' || p_contextValue2 );
1718
1719 CS_CF_SOURCE_CXT_TARGETS_PKG.UPDATE_ROW(
1720 X_SOURCE_CONTEXT_TARGET_ID => u_source_context_target_id,
1721 X_SOURCE_CONTEXT_TYPE_ID => l_source_context_type_id,
1722 X_CONTEXT_VALUE1 => u_context_value1,
1723 X_CONTEXT_VALUE2 => u_context_value2,
1724 X_CONTEXT_VALUE3 => u_context_value3,
1725 X_CONTEXT_VALUE4 => u_context_value4,
1726 X_CONTEXT_VALUE5 => u_context_value5,
1727 X_SEED_TARGET_VALUE1 => u_seed_target_value1,
1728 X_SEED_TARGET_VALUE2 => u_seed_target_value2,
1729 X_CUST_TARGET_VALUE1 => p_custTargetValue1,
1730 X_CUST_TARGET_VALUE2 => p_custTargetValue2,
1731 X_OBJECT_VERSION_NUMBER => u_object_version_number+1,
1732 X_LAST_UPDATE_DATE => u_last_update_date,
1733 X_LAST_UPDATED_BY => u_last_updated_by,
1734 X_LAST_UPDATE_LOGIN => u_last_update_login,
1735 X_ATTRIBUTE_CATEGORY => u_attribute_category,
1736 X_ATTRIBUTE1 => u_attribute1,
1737 X_ATTRIBUTE2 => u_attribute2,
1738 X_ATTRIBUTE3 => u_attribute3,
1739 X_ATTRIBUTE4 => u_attribute4,
1740 X_ATTRIBUTE5 => u_attribute5,
1741 X_ATTRIBUTE6 => u_attribute6,
1742 X_ATTRIBUTE7 => u_attribute7,
1743 X_ATTRIBUTE8 => u_attribute8,
1744 X_ATTRIBUTE9 => u_attribute9,
1745 X_ATTRIBUTE10 => u_attribute10,
1746 X_ATTRIBUTE11 => u_attribute11,
1747 X_ATTRIBUTE12 => u_attribute12,
1748 X_ATTRIBUTE13 => u_attribute13,
1749 X_ATTRIBUTE14 => u_attribute14,
1750 X_ATTRIBUTE15 => u_attribute15,
1751 X_ADDITIONAL_INFO1 => u_additional_info1,
1752 X_ADDITIONAL_INFO2 => u_additional_info2,
1753 X_ADDITIONAL_INFO3 => u_additional_info3,
1754 X_ADDITIONAL_INFO4 => u_additional_info4,
1755 X_ADDITIONAL_INFO5 => u_additional_info5,
1756 X_ADDITIONAL_INFO6 => u_additional_info6,
1757 X_ADDITIONAL_INFO7 => u_additional_info7,
1758 X_ADDITIONAL_INFO8 => u_additional_info8,
1759 X_ADDITIONAL_INFO9 => u_additional_info9,
1760 X_ADDITIONAL_INFO10 => u_additional_info10,
1761 X_ADDITIONAL_INFO11 => u_additional_info11,
1762 X_ADDITIONAL_INFO12 => u_additional_info12,
1763 X_ADDITIONAL_INFO13 => u_additional_info13,
1764 X_ADDITIONAL_INFO14 => u_additional_info14,
1765 X_ADDITIONAL_INFO15 => u_additional_info15);
1766
1767 END IF;
1768
1769 EXCEPTION
1770 WHEN no_row_found THEN
1771 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG.Insert_New_Target', 'Unexpected error: There should be at least one entry at the global level for source code ' || p_sourceCode);
1772 RAISE;
1773 WHEN OTHERS THEN
1774 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG:Insert_New_Target', 'Exception for sourceCode: ' || p_sourceCode || 'contextType: ' || p_contextType);
1775 IF (source_context_type_id%ISOPEN) THEN
1776 CLOSE source_context_type_id;
1777 END IF;
1778 IF (target_count_resp%ISOPEN) THEN
1779 CLOSE target_count_resp;
1780 END IF;
1781
1782 IF (target_count_appl%ISOPEN) THEN
1783 CLOSE target_count_appl;
1784 END IF;
1785 IF (target_count_global%ISOPEN) THEN
1786 CLOSE target_count_global;
1787 END IF;
1788 IF (target_values_global%ISOPEN) THEN
1789 CLOSE target_values_global;
1790 END IF;
1791
1792 RAISE;
1793
1794 END Insert_New_Target;
1795
1796 /*
1797 * Wrapper function to call AK's api to clone regions
1798 */
1799 PROCEDURE Clone_Region(p_regionCode IN VARCHAR2,
1800 p_regionApplId IN NUMBER,
1801 p_newRegionCode IN VARCHAR2,
1802 p_newRegionApplId IN NUMBER,
1803 p_checkRegion IN BOOLEAN)
1804 IS
1805
1806 r_rec ak_regions%rowtype;
1807
1808 CURSOR r_csr (p_csr_code IN VARCHAR2, p_csr_id IN NUMBER) is
1809 SELECT *
1810 FROM ak_regions
1811 WHERE region_code = p_csr_code
1812 AND region_application_id = p_csr_id;
1813
1814 CURSOR region_exists (p_region_code IN VARCHAR2, p_region_application_id IN NUMBER) IS
1815 SELECT count(*)
1816 FROM ak_regions
1817 where region_code = p_region_code
1818 AND region_application_id = p_region_application_id;
1819
1820
1821
1822 b_success BOOLEAN;
1823 l_count NUMBER := 0;
1824
1825 BEGIN
1826
1827 -- mkcyee 12/14/2004 p_checkRgion is TRUE only in the case of the Global
1828 -- level.
1829
1830 IF (p_checkRegion) THEN
1831 OPEN region_exists(p_newRegionCode, p_newRegionApplId);
1832 FETCH region_exists INTO l_count;
1833 CLOSE region_exists;
1834 END IF;
1835
1836 -- mkcyee 12/14/2004 - For global regions, we will make sure
1837 -- not to clone a region if it already exists. For resp and appl,
1838 -- we will never get here because if any regions are cloned, we will
1839 -- exit out of their upgrade procedure.
1840 -- This is so that the cust target values column in the
1841 -- cs_cf_source_cxt_targets table will get populated again
1842 -- after the ldt is uploaded.
1843
1844 IF ((p_checkRegion AND l_count = 0) OR NOT p_checkRegion) THEN
1845
1846 AK_REGIONS2_PKG.copy_records(p_regionCode,
1847 p_regionApplId,
1848 p_newRegionCode,
1849 p_newRegionApplId);
1850
1851 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG:Clone_Region', 'Cloning Region for p_regionCode: ' || p_regionCode || ' p_regionApplId: ' || to_char(p_regionApplId));
1852 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG:Clone_Region', ' p_newRegionCode: ' || p_newRegionCode || ' p_newRegionApplId: ' || to_char(p_newRegionApplId));
1853
1854 OPEN r_csr(p_newRegionCode, p_newRegionApplId);
1855 FETCH r_csr INTO r_rec;
1856
1857 b_success := r_csr%FOUND;
1858 IF (not b_success) THEN
1859 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG:Clone_Region', ' Could not find cloned region');
1860 close r_csr;
1861 RAISE PROGRAM_ERROR;
1862 ELSE
1863 close r_csr;
1864
1865 -- mkcyee 02/24/2004 After we clone the region, we need to update
1866 -- the region name; Otherwise, it uses the same name as the region we
1867 -- are trying to clone
1868 AK_REGIONS_PKG.Update_Row(X_REGION_APPLICATION_ID => r_rec.region_application_id,
1869 X_REGION_CODE => r_rec.region_code,
1870 X_DATABASE_OBJECT_NAME => r_rec.database_object_name,
1871 X_REGION_STYLE => r_rec.region_style,
1872 X_NUM_COLUMNS => r_rec.num_columns,
1873 X_ICX_CUSTOM_CALL => r_rec.icx_custom_call,
1874 X_NAME => p_newRegionCode,
1875 X_DESCRIPTION => '',
1876 X_REGION_DEFAULTING_API_PKG => r_rec.region_defaulting_api_pkg,
1877 X_REGION_DEFAULTING_API_PROC => r_rec.region_defaulting_api_proc,
1878 X_REGION_VALIDATION_API_PKG => r_rec.region_validation_api_pkg,
1879 X_REGION_VALIDATION_API_PROC => r_rec.region_validation_api_proc,
1880 X_APPL_MODULE_OBJECT_TYPE => r_rec.applicationmodule_object_type,
1881 X_NUM_ROWS_DISPLAY => r_rec.num_rows_display,
1882 X_REGION_OBJECT_TYPE => r_rec.region_object_type,
1883 X_IMAGE_FILE_NAME => r_rec.image_file_name,
1884 X_ISFORM_FLAG => r_rec.isform_flag,
1885 X_HELP_TARGET => r_rec.help_target,
1886 X_STYLE_SHEET_FILENAME => r_rec.style_sheet_filename,
1887 X_VERSION => r_rec.version,
1888 X_APPLICATIONMODULE_USAGE_NAME => r_rec.applicationmodule_usage_name,
1889 X_ADD_INDEXED_CHILDREN => r_rec.add_indexed_children,
1890 X_STATEFUL_FLAG => r_rec.stateful_flag,
1891 X_FUNCTION_NAME => r_rec.function_name,
1892 X_CHILDREN_VIEW_USAGE_NAME => r_rec.children_view_usage_name,
1893 X_SEARCH_PANEL => r_rec.search_panel,
1894 X_ADVANCED_SEARCH_PANEL => r_rec.advanced_search_panel,
1895 X_CUSTOMIZE_PANEL => r_rec.customize_panel,
1896 X_DEFAULT_SEARCH_PANEL => r_rec.default_search_panel,
1897 X_RESULTS_BASED_SEARCH => r_rec.results_based_search,
1898 X_DISPLAY_GRAPH_TABLE => r_rec.display_graph_table,
1899 X_DISABLE_HEADER => r_rec.disable_header,
1900 X_STANDALONE => r_rec.standalone,
1901 X_AUTO_CUSTOMIZATION_CRITERIA => r_rec.auto_customization_criteria,
1902 X_LAST_UPDATE_DATE => sysdate,
1903 X_LAST_UPDATED_BY => r_rec.last_updated_by,
1904 X_LAST_UPDATE_LOGIN => r_rec.last_update_login,
1905 X_ATTRIBUTE_CATEGORY => r_rec.attribute_category,
1906 X_ATTRIBUTE1 => r_rec.attribute1,
1907 X_ATTRIBUTE2 => r_rec.attribute2,
1908 X_ATTRIBUTE3 => r_rec.attribute3,
1909 X_ATTRIBUTE4 => r_rec.attribute4,
1910 X_ATTRIBUTE5 => r_rec.attribute5,
1911 X_ATTRIBUTE6 => r_rec.attribute6,
1912 X_ATTRIBUTE7 => r_rec.attribute7,
1913 X_ATTRIBUTE8 => r_rec.attribute8,
1914 X_ATTRIBUTE9 => r_rec.attribute9,
1915 X_ATTRIBUTE10 => r_rec.attribute10,
1916 X_ATTRIBUTE11 => r_rec.attribute11,
1917 X_ATTRIBUTE12 => r_rec.attribute12,
1918 X_ATTRIBUTE13 => r_rec.attribute13,
1919 X_ATTRIBUTE14 => r_rec.attribute14,
1920 X_ATTRIBUTE15 => r_rec.attribute15);
1921
1922 END IF;
1923 END IF;
1924
1925 EXCEPTION
1926 WHEN OTHERS THEN
1927 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG:Clone_Region', 'Unexpected exception - p_regionCode: ' || p_regionCode || ' p_regionApplId: ' || to_char(p_regionApplId));
1928 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG:Clone_Region', ' p_newRegionCode: ' || p_newRegionCode || ' p_newRegionApplId: ' || to_char(p_newRegionApplId));
1929
1930 IF (r_csr%ISOPEN) THEN
1931 CLOSE r_csr;
1932 END IF;
1933
1934 IF (region_exists%ISOPEN) THEN
1935 CLOSE region_exists;
1936 END IF;
1937
1938 RAISE;
1939 END;
1940
1941 /*
1942 * Updates the actual region items in the region
1943 * Assumes all regions and attributes are defined
1944 * under 672.
1945 */
1946 PROCEDURE UpdateRegionItems(p_regionCode IN VARCHAR2,
1947 p_attributeCode IN VARCHAR2,
1948 p_displayFlag IN VARCHAR2,
1949 p_mandatoryFlag IN VARCHAR2,
1950 p_subRegionCode IN VARCHAR2)
1951 IS
1952
1953 l_displaySequence NUMBER;
1954 l_nodeQueryFlag VARCHAR2(1);
1955 l_attributeLabelLength NUMBER;
1956 l_bold VARCHAR2(1);
1957 l_italic VARCHAR2(1);
1958 l_verticalAlignment VARCHAR2(30);
1959 l_horizontalAlignment VARCHAR2(30);
1960 l_itemStyle VARCHAR2(30);
1961 l_objectAttributeFlag VARCHAR2(1);
1962 l_attributeLabelLong VARCHAR2(80);
1963 l_description VARCHAR2(2000);
1964 l_securityCode VARCHAR2(30);
1965 l_updateFlag VARCHAR2(1);
1966 l_requiredFlag VARCHAR2(1);
1967 l_displayValueLength NUMBER;
1968 l_lovRegionApplicationId NUMBER;
1969 l_lovRegionCode VARCHAR2(30);
1970 l_lovForeignKeyName VARCHAR2(30);
1971 l_lovAttributeApplicationId NUMBER;
1972 l_lovAttributeCode VARCHAR2(30);
1973 l_lovDefaultFlag VARCHAR2(1);
1974 l_regionDefaultingApiPkg VARCHAR2(30);
1975 l_regionDefaultingApiProc VARCHAR2(30);
1976 l_regionValidationApiPkg VARCHAR2(30);
1977 l_regionValidationApiProc VARCHAR2(30);
1978 l_orderSequence NUMBER;
1979 l_orderDirection VARCHAR2(30);
1980 l_defaultValueVarchar2 VARCHAR2(240);
1981 l_defaultValueNumber NUMBER;
1982 l_defaultValueDate DATE;
1983 l_itemName VARCHAR2(30);
1984 l_displayHeight NUMBER;
1985 l_submit VARCHAR2(1);
1986 l_encrypt VARCHAR2(1);
1987 l_viewUsageName VARCHAR2(80);
1988 l_viewAttributeName VARCHAR2(80);
1989 l_cssClassName VARCHAR2(80);
1990 l_cssLabelClassName VARCHAR2(80);
1991 l_url VARCHAR2(2000);
1992 l_poplistViewObject VARCHAR2(240);
1993 l_poplistDisplayAttribute VARCHAR2(80);
1994 l_poplistValueAttribute VARCHAR2(80);
1995 l_imageFileName VARCHAR2(80);
1996 l_nestedRegionCode VARCHAR2(30);
1997 l_nestedRegionApplId NUMBER;
1998 l_menuName VARCHAR2(30);
1999 l_flexfieldName VARCHAR2(40);
2000 l_flexfieldApplicationId NUMBER;
2001 l_tabularFunctionCode VARCHAR2(30);
2002 l_tipType VARCHAR2(30);
2003 l_tipTypeMessageName VARCHAR2(30);
2004 l_tipMessageApplicationId NUMBER;
2005 l_flexSegmentList VARCHAR2(4000);
2006 l_entityId VARCHAR2(30);
2007 l_anchor VARCHAR2(1);
2008 l_poplistViewUsageName VARCHAR2(80);
2009 l_attributeCategory VARCHAR2(30);
2010 l_attribute1 VARCHAR2(150);
2011 l_attribute2 VARCHAR2(150);
2012 l_attribute3 VARCHAR2(150);
2013 l_attribute4 VARCHAR2(150);
2014 l_attribute5 VARCHAR2(150);
2015 l_attribute6 VARCHAR2(150);
2016 l_attribute7 VARCHAR2(150);
2017 l_attribute8 VARCHAR2(150);
2018 l_attribute9 VARCHAR2(150);
2019 l_attribute10 VARCHAR2(150);
2020 l_attribute11 VARCHAR2(150);
2021 l_attribute12 VARCHAR2(150);
2022 l_attribute13 VARCHAR2(150);
2023 l_attribute14 VARCHAR2(150);
2024 l_attribute15 VARCHAR2(150);
2025
2026 l_subRegionCode VARCHAR2(30) := p_subRegionCode;
2027
2028 BEGIN
2029
2030 IF (l_subRegionCode is NULL) THEN
2031 l_subRegionCode := '';
2032 END IF;
2033
2034 SELECT a.display_sequence,
2035 a.node_query_flag,
2036 a.attribute_label_length,
2037 a.bold,
2038 a.italic,
2039 a.vertical_alignment,
2040 a.horizontal_alignment,
2041 a.item_style,
2042 a.object_attribute_flag,
2043 b.attribute_label_long,
2044 b.description,
2045 a.security_code,
2046 a.update_flag,
2047 a.required_flag,
2048 a.display_value_length,
2049 a.lov_region_application_id,
2050 a.lov_region_code,
2051 a.lov_foreign_key_name,
2052 a.lov_attribute_application_id,
2053 a.lov_attribute_code,
2054 a.lov_default_flag,
2055 a.region_defaulting_api_pkg,
2056 a.region_defaulting_api_proc,
2057 a.region_validation_api_pkg,
2058 a.region_validation_api_proc,
2059 a.order_sequence,
2060 a.order_direction,
2061 a.default_value_varchar2,
2062 a.default_value_number,
2063 a.default_value_date,
2064 a.item_name,
2065 a.display_height,
2066 a.submit,
2067 a.encrypt,
2068 a.view_usage_name,
2069 a.view_attribute_name,
2070 a.css_class_name,
2071 a.css_label_class_name,
2072 a.url,
2073 a.poplist_viewobject,
2074 a.poplist_display_attribute,
2075 a.poplist_value_attribute,
2076 a.image_file_name,
2077 a.nested_region_application_id,
2078 a.nested_region_code,
2079 a.menu_name,
2080 a.flexfield_name,
2081 a.flexfield_application_id,
2082 a.tabular_function_code,
2083 a.tip_type,
2084 a.tip_message_name,
2085 a.tip_message_application_id,
2086 a.flex_segment_list,
2087 a.entity_id,
2088 a.anchor,
2089 a.poplist_view_usage_name,
2090 a.attribute_category,
2091 a.attribute1,
2092 a.attribute2,
2093 a.attribute3,
2094 a.attribute4,
2095 a.attribute5,
2096 a.attribute6,
2097 a.attribute7,
2098 a.attribute8,
2099 a.attribute9,
2100 a.attribute10,
2101 a.attribute11,
2102 a.attribute12,
2103 a.attribute13,
2104 a.attribute14,
2105 a.attribute15
2106 INTO
2107 l_displaySequence,
2108 l_nodeQueryFlag,
2109 l_attributeLabelLength,
2110 l_bold,
2111 l_italic,
2112 l_verticalAlignment,
2113 l_horizontalAlignment,
2114 l_itemStyle,
2115 l_objectAttributeFlag,
2116 l_attributeLabelLong,
2117 l_description,
2118 l_securityCode,
2119 l_updateFlag,
2120 l_requiredFlag,
2121 l_displayValueLength,
2122 l_lovRegionApplicationId,
2123 l_lovRegionCode,
2124 l_lovForeignKeyName,
2125 l_lovAttributeApplicationId,
2126 l_lovAttributeCode,
2127 l_lovDefaultFlag,
2128 l_regionDefaultingApiPkg,
2129 l_regionDefaultingApiProc,
2130 l_regionValidationApiPkg,
2131 l_regionValidationApiProc,
2132 l_orderSequence,
2133 l_orderDirection,
2134 l_defaultValueVarchar2,
2135 l_defaultValueNumber,
2136 l_defaultValueDate,
2137 l_itemName,
2138 l_displayHeight,
2139 l_submit,
2140 l_encrypt,
2141 l_viewUsageName,
2142 l_viewAttributeName,
2143 l_cssClassName,
2144 l_cssLabelClassName,
2145 l_url,
2146 l_poplistViewObject,
2147 l_poplistDisplayAttribute,
2148 l_poplistValueAttribute,
2149 l_imageFileName,
2150 l_nestedRegionApplId,
2151 l_nestedRegionCode,
2152 l_menuName,
2153 l_flexfieldName,
2154 l_flexfieldApplicationId,
2155 l_tabularFunctionCode,
2156 l_tipType,
2157 l_tipTypeMessageName,
2158 l_tipMessageApplicationId,
2159 l_flexSegmentList,
2160 l_entityId,
2161 l_anchor,
2162 l_poplistViewUsageName,
2163 l_attributeCategory,
2164 l_attribute1,
2165 l_attribute2,
2166 l_attribute3,
2167 l_attribute4,
2168 l_attribute5,
2169 l_attribute6,
2170 l_attribute7,
2171 l_attribute8,
2172 l_attribute9,
2173 l_attribute10,
2174 l_attribute11,
2175 l_attribute12,
2176 l_attribute13,
2177 l_attribute14,
2178 l_attribute15
2179 FROM ak_region_items a,
2180 ak_region_items_tl b
2181 WHERE a.region_code = b.region_code
2182 AND a.region_application_id = b.region_application_id
2183 AND a.attribute_code = b.attribute_code
2184 AND a.attribute_application_id = b.attribute_application_id
2185 AND b.language = USERENV('LANG')
2186 AND a.region_code = p_regionCode
2187 AND a.region_application_id = 672
2188 AND a.attribute_code = p_attributeCode
2189 and a.attribute_application_id = 672
2190 FOR UPDATE NOWAIT;
2191
2192
2193 IF (p_subRegionCode is null) THEN
2194
2195 AK_REGION_ITEMS_PKG.UPDATE_ROW(
2196 X_REGION_APPLICATION_ID => 672,
2197 X_REGION_CODE => p_regionCode,
2198 X_ATTRIBUTE_APPLICATION_ID => 672,
2199 X_ATTRIBUTE_CODE => p_attributeCode,
2200 X_DISPLAY_SEQUENCE => l_displaySequence,
2201 X_NODE_DISPLAY_FLAG => p_displayFlag,
2202 X_NODE_QUERY_FLAG => l_nodeQueryFlag,
2203 X_ATTRIBUTE_LABEL_LENGTH => l_attributeLabelLength,
2204 X_BOLD => l_bold,
2205 X_ITALIC => l_italic,
2206 X_VERTICAL_ALIGNMENT => l_verticalAlignment,
2207 X_HORIZONTAL_ALIGNMENT => l_horizontalAlignment,
2208 X_ITEM_STYLE => l_itemStyle,
2209 X_OBJECT_ATTRIBUTE_FLAG => l_objectAttributeFlag,
2210 X_ATTRIBUTE_LABEL_LONG => l_attributeLabelLong,
2211 X_DESCRIPTION => l_description,
2212 X_SECURITY_CODE => l_securityCode,
2213 X_UPDATE_FLAG => l_updateFlag,
2214 X_REQUIRED_FLAG => p_mandatoryFlag,
2215 X_DISPLAY_VALUE_LENGTH => l_displayValueLength,
2216 X_LOV_REGION_APPLICATION_ID => l_lovRegionApplicationId,
2217 X_LOV_REGION_CODE => l_lovRegionCode,
2218 X_LOV_FOREIGN_KEY_NAME => l_lovForeignKeyName,
2219 X_LOV_ATTRIBUTE_APPLICATION_ID => l_lovAttributeApplicationId,
2220 X_LOV_ATTRIBUTE_CODE => l_lovAttributeCode,
2221 X_LOV_DEFAULT_FLAG => l_lovDefaultFlag,
2222 X_REGION_DEFAULTING_API_PKG => l_regionDefaultingApiPkg,
2223 X_REGION_DEFAULTING_API_PROC => l_regionDefaultingApiProc,
2224 X_REGION_VALIDATION_API_PKG => l_regionValidationApiPkg,
2225 X_REGION_VALIDATION_API_PROC => l_regionValidationApiProc,
2226 X_ORDER_SEQUENCE => l_orderSequence,
2227 X_ORDER_DIRECTION => l_orderDirection,
2228 X_DEFAULT_VALUE_VARCHAR2 => l_defaultValueVarchar2,
2229 X_DEFAULT_VALUE_NUMBER => l_defaultValueNumber,
2230 X_DEFAULT_VALUE_DATE => l_defaultValueDate,
2231 X_ITEM_NAME => l_itemName,
2232 X_DISPLAY_HEIGHT => l_displayHeight,
2233 X_SUBMIT => l_submit,
2234 X_ENCRYPT => l_encrypt,
2235 X_VIEW_USAGE_NAME => l_viewUsageName,
2236 X_VIEW_ATTRIBUTE_NAME => l_viewAttributeName,
2237 X_CSS_CLASS_NAME => l_cssClassName,
2238 X_CSS_LABEL_CLASS_NAME => l_cssLabelClassName,
2239 X_URL => l_url,
2240 X_POPLIST_VIEWOBJECT => l_poplistViewObject,
2241 X_POPLIST_DISPLAY_ATTRIBUTE => l_poplistDisplayAttribute,
2242 X_POPLIST_VALUE_ATTRIBUTE => l_poplistValueAttribute,
2243 X_IMAGE_FILE_NAME => l_imageFileName,
2244 X_NESTED_REGION_CODE => l_nestedRegionCode,
2245 X_NESTED_REGION_APPL_ID => l_nestedRegionApplId,
2246 X_MENU_NAME => l_menuName,
2247 X_FLEXFIELD_NAME => l_flexfieldName,
2248 X_FLEXFIELD_APPLICATION_ID => l_flexfieldApplicationId,
2249 X_TABULAR_FUNCTION_CODE => l_tabularFunctionCode,
2250 X_TIP_TYPE => l_tipType,
2251 X_TIP_MESSAGE_NAME => l_tipTypeMessageName,
2252 X_TIP_MESSAGE_APPLICATION_ID => l_tipMessageApplicationId,
2253 X_FLEX_SEGMENT_LIST => l_flexSegmentList,
2254 X_ENTITY_ID => l_entityId,
2255 X_ANCHOR => l_anchor,
2256 X_POPLIST_VIEW_USAGE_NAME => l_poplistViewUsageName,
2257 X_LAST_UPDATE_DATE => sysdate,
2258 X_LAST_UPDATED_BY => fnd_load_util.owner_id('ORACLE'),
2259 X_LAST_UPDATE_LOGIN => 0,
2260 X_ATTRIBUTE_CATEGORY => l_attributeCategory,
2261 X_ATTRIBUTE1 => l_attribute1,
2262 X_ATTRIBUTE2 => l_attribute2,
2263 X_ATTRIBUTE3 => l_attribute3,
2264 X_ATTRIBUTE4 => l_attribute4,
2265 X_ATTRIBUTE5 => l_attribute5,
2266 X_ATTRIBUTE6 => l_attribute6,
2267 X_ATTRIBUTE7 => l_attribute7,
2268 X_ATTRIBUTE8 => l_attribute8,
2269 X_ATTRIBUTE9 => l_attribute9,
2270 X_ATTRIBUTE10 => l_attribute10,
2271 X_ATTRIBUTE11 => l_attribute11,
2272 X_ATTRIBUTE12 => l_attribute12,
2273 X_ATTRIBUTE13 => l_attribute13,
2274 X_ATTRIBUTE14 => l_attribute14,
2275 X_ATTRIBUTE15 => l_attribute15);
2276 ELSE
2277 AK_REGION_ITEMS_PKG.UPDATE_ROW(
2278 X_REGION_APPLICATION_ID => 672,
2279 X_REGION_CODE => p_regionCode,
2280 X_ATTRIBUTE_APPLICATION_ID => 672,
2281 X_ATTRIBUTE_CODE => p_attributeCode,
2282 X_DISPLAY_SEQUENCE => l_displaySequence,
2283 X_NODE_DISPLAY_FLAG => p_displayFlag,
2284 X_NODE_QUERY_FLAG => l_nodeQueryFlag,
2285 X_ATTRIBUTE_LABEL_LENGTH => l_attributeLabelLength,
2286 X_BOLD => l_bold,
2287 X_ITALIC => l_italic,
2288 X_VERTICAL_ALIGNMENT => l_verticalAlignment,
2289 X_HORIZONTAL_ALIGNMENT => l_horizontalAlignment,
2290 X_ITEM_STYLE => l_itemStyle,
2291 X_OBJECT_ATTRIBUTE_FLAG => l_objectAttributeFlag,
2292 X_ATTRIBUTE_LABEL_LONG => l_attributeLabelLong,
2293 X_DESCRIPTION => l_description,
2294 X_SECURITY_CODE => l_securityCode,
2295 X_UPDATE_FLAG => l_updateFlag,
2296 X_REQUIRED_FLAG => p_mandatoryFlag,
2297 X_DISPLAY_VALUE_LENGTH => l_displayValueLength,
2298 X_LOV_REGION_APPLICATION_ID => l_lovRegionApplicationId,
2299 X_LOV_REGION_CODE => l_lovRegionCode,
2300 X_LOV_FOREIGN_KEY_NAME => l_lovForeignKeyName,
2301 X_LOV_ATTRIBUTE_APPLICATION_ID => l_lovAttributeApplicationId,
2302 X_LOV_ATTRIBUTE_CODE => l_lovAttributeCode,
2303 X_LOV_DEFAULT_FLAG => l_lovDefaultFlag,
2304 X_REGION_DEFAULTING_API_PKG => l_regionDefaultingApiPkg,
2305 X_REGION_DEFAULTING_API_PROC => l_regionDefaultingApiProc,
2306 X_REGION_VALIDATION_API_PKG => l_regionValidationApiPkg,
2307 X_REGION_VALIDATION_API_PROC => l_regionValidationApiProc,
2308 X_ORDER_SEQUENCE => l_orderSequence,
2309 X_ORDER_DIRECTION => l_orderDirection,
2310 X_DEFAULT_VALUE_VARCHAR2 => l_defaultValueVarchar2,
2311 X_DEFAULT_VALUE_NUMBER => l_defaultValueNumber,
2312 X_DEFAULT_VALUE_DATE => l_defaultValueDate,
2313 X_ITEM_NAME => l_itemName,
2314 X_DISPLAY_HEIGHT => l_displayHeight,
2315 X_SUBMIT => l_submit,
2316 X_ENCRYPT => l_encrypt,
2317 X_VIEW_USAGE_NAME => l_viewUsageName,
2318 X_VIEW_ATTRIBUTE_NAME => l_viewAttributeName,
2319 X_CSS_CLASS_NAME => l_cssClassName,
2320 X_CSS_LABEL_CLASS_NAME => l_cssLabelClassName,
2321 X_URL => l_url,
2322 X_POPLIST_VIEWOBJECT => l_poplistViewObject,
2323 X_POPLIST_DISPLAY_ATTRIBUTE => l_poplistDisplayAttribute,
2324 X_POPLIST_VALUE_ATTRIBUTE => l_poplistValueAttribute,
2325 X_IMAGE_FILE_NAME => l_imageFileName,
2326 X_NESTED_REGION_CODE => p_subRegionCode,
2327 X_NESTED_REGION_APPL_ID => 672,
2328 X_MENU_NAME => l_menuName,
2329 X_FLEXFIELD_NAME => l_flexfieldName,
2330 X_FLEXFIELD_APPLICATION_ID => l_flexfieldApplicationId,
2331 X_TABULAR_FUNCTION_CODE => l_tabularFunctionCode,
2332 X_TIP_TYPE => l_tipType,
2333 X_TIP_MESSAGE_NAME => l_tipTypeMessageName,
2334 X_TIP_MESSAGE_APPLICATION_ID => l_tipMessageApplicationId,
2335 X_FLEX_SEGMENT_LIST => l_flexSegmentList,
2336 X_ENTITY_ID => l_entityId,
2337 X_ANCHOR => l_anchor,
2338 X_POPLIST_VIEW_USAGE_NAME => l_poplistViewUsageName,
2339 X_LAST_UPDATE_DATE => sysdate,
2340 X_LAST_UPDATED_BY => fnd_load_util.owner_id('ORACLE'),
2341 X_LAST_UPDATE_LOGIN => 0,
2342 X_ATTRIBUTE_CATEGORY => l_attributeCategory,
2343 X_ATTRIBUTE1 => l_attribute1,
2344 X_ATTRIBUTE2 => l_attribute2,
2345 X_ATTRIBUTE3 => l_attribute3,
2346 X_ATTRIBUTE4 => l_attribute4,
2347 X_ATTRIBUTE5 => l_attribute5,
2348 X_ATTRIBUTE6 => l_attribute6,
2349 X_ATTRIBUTE7 => l_attribute7,
2350 X_ATTRIBUTE8 => l_attribute8,
2351 X_ATTRIBUTE9 => l_attribute9,
2352 X_ATTRIBUTE10 => l_attribute10,
2353 X_ATTRIBUTE11 => l_attribute11,
2354 X_ATTRIBUTE12 => l_attribute12,
2355 X_ATTRIBUTE13 => l_attribute13,
2356 X_ATTRIBUTE14 => l_attribute14,
2357 X_ATTRIBUTE15 => l_attribute15);
2358
2359 END IF;
2360
2361 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UTL_UPG_PKG.UpdateRegionItems', 'Updating region items for p_regionCode: ' || p_regionCode || ' p_attributeCode: ' || p_attributeCode );
2362
2363 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UTL_UPG_PKG.UpdateRegionItems', ' p_displayFlag: ' || p_displayFlag || ' p_mandatoryFlag: ' || p_mandatoryFlag || ' p_subRegionCode: ' || l_subRegionCode);
2364
2365 EXCEPTION
2366 WHEN OTHERS THEN
2367 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UTL_UPG_PKG.UpdateRegionItems', 'Exception for p_regionCode: ' || p_regionCode || ' p_attributeCode: ' || p_attributeCode );
2368 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UTL_UPG_PKG.UpdateRegionItems', ' p_displayFlag: ' || p_displayFlag || ' p_mandatoryFlag: ' || p_mandatoryFlag || ' p_subRegionCode: ' || l_subRegionCode);
2369 RAISE;
2370 END UpdateRegionItems;
2371
2372 PROCEDURE getAddressProfileValues(p_ProfileTable IN CS_CF_UPG_UTL_PKG.ProfileTable,
2373 p_displayBillToAddress IN OUT NOCOPY VARCHAR2,
2374 p_displayBillToContact IN OUT NOCOPY VARCHAR2,
2375 p_displayShipToAddress IN OUT NOCOPY VARCHAR2,
2376 p_displayShipToContact IN OUT NOCOPY VARCHAR2,
2377 p_displayInstalledAtAddr IN OUT NOCOPY VARCHAR2,
2378 p_displayIncidentAddr IN OUT NOCOPY VARCHAR2,
2379 p_mandatoryIncidentAddr IN OUT NOCOPY VARCHAR2)
2380 IS
2381 l_count NUMBER := p_ProfileTable.COUNT;
2382 l_index NUMBER := 0;
2383 l_profileOptionName FND_PROFILE_OPTIONS.profile_option_name%TYPE;
2384
2385 l_examineBillToAddress BOOLEAN := TRUE;
2386 l_examineBillToContact BOOLEAN := TRUE;
2387 l_examineShipToAddress BOOLEAN := TRUE;
2388 l_examineShipToContact BOOLEAN := TRUE;
2389 l_examineInstalledAtAddress BOOLEAN := TRUE;
2390 l_examineAddrDisplay BOOLEAN := TRUE;
2391 l_examineAddrMandatory BOOLEAN := TRUE;
2392
2393 BEGIN
2394
2395 WHILE (l_index < l_count) LOOP
2396 l_profileOptionName := p_ProfileTable(l_index).profileOptionName;
2397 IF (l_profileOptionName = 'IBU_A_SR_BILLTO_ADDRESS_OPTION' AND
2398 l_examineBillToAddress) THEN
2399 p_displayBillToAddress := p_ProfileTable(l_index).profileOptionValue;
2400 l_examineBillToAddress := FALSE;
2401 ELSIF (l_profileOptionName = 'IBU_A_SR_BILLTO_CONTACT_OPTION' AND
2402 l_examineBillToContact) THEN
2403 p_displayBillToContact := p_ProfileTable(l_index).profileOptionValue;
2404 l_examineBillToContact := FALSE;
2405 ELSIF (l_profileOptionName = 'IBU_A_SR_SHIPTO_ADDRESS_OPTION' AND
2406 l_examineShipToAddress) THEN
2407 p_displayShipToAddress := p_ProfileTable(l_index).profileOptionValue;
2408 l_examineShipToAddress := FALSE;
2409 ELSIF (l_profileOptionName = 'IBU_A_SR_SHIPTO_CONTACT_OPTION' AND
2410 l_examineShipToContact) THEN
2411 p_displayShipToContact := p_ProfileTable(l_index).profileOptionValue;
2412 l_examineShipToContact := FALSE;
2413 ELSIF (l_profileOptionName = 'IBU_A_SR_INSTALLEDAT_ADDRESS_OPTION' AND
2414 l_examineInstalledAtAddress) THEN
2415 p_displayInstalledAtAddr := p_ProfileTable(l_index).profileOptionValue;
2416 l_examineInstalledAtAddress := FALSE;
2417 ELSIF (l_profileOptionName = 'IBU_SR_ADDR_DISPLAY' AND
2418 l_examineAddrDisplay) THEN
2419 p_displayIncidentAddr := p_ProfileTable(l_index).profileOptionValue;
2420 l_examineAddrDisplay := FALSE;
2421 ELSIF (l_profileOptionName = 'IBU_SR_ADDR_MANDATORY' AND
2422 l_examineAddrMandatory) THEN
2423 p_mandatoryIncidentAddr := p_ProfileTable(l_index).profileOptionValue;
2424 l_examineAddrMandatory := FALSE;
2425 END IF;
2426 l_index := l_index + 1;
2427 END LOOP;
2428
2429 EXCEPTION
2430 WHEN OTHERS THEN
2431 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG:getAddressProfileOptions', 'Unexpected exception in getAddressProfileOptions');
2432 RAISE;
2433
2434 END getAddressProfileValues;
2435
2436 PROCEDURE getAttachmentProbCodeValues(p_ProfileTable IN CS_CF_UPG_UTL_PKG.ProfileTable,
2437 p_displayAttachment IN OUT NOCOPY VARCHAR2,
2438 p_mandatoryProblemCode IN OUT NOCOPY VARCHAR2)
2439 IS
2440 l_count NUMBER := p_ProfileTable.COUNT;
2441 l_index NUMBER := 0;
2442 l_profileOptionName FND_PROFILE_OPTIONS.profile_option_name%TYPE;
2443
2444 l_examineAttachment BOOLEAN := TRUE;
2445 l_examineProbCode BOOLEAN := TRUE;
2446
2447 BEGIN
2448
2449 WHILE (l_index < l_count) LOOP
2450 l_profileOptionName := p_ProfileTable(l_index).profileOptionName;
2451 IF (l_profileOptionName = 'IBU_A_SR_PROB_CODE_MANDATORY' AND
2452 l_examineProbCode) THEN
2453 p_mandatoryProblemCode := p_ProfileTable(l_index).profileOptionValue;
2454 l_examineProbCode := FALSE;
2455 ELSIF (l_profileOptionName = 'IBU_A_SR_ATTACHMENT_OPTION' AND
2456 l_examineAttachment) THEN
2457 p_displayAttachment := p_ProfileTable(l_index).profileOptionValue;
2458 l_examineAttachment:= FALSE;
2459 END IF;
2460 l_index := l_index + 1;
2461 END LOOP;
2462
2463 EXCEPTION
2464 WHEN OTHERS THEN
2465 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG:getAttachmentProbCodeValues', 'Unexpected exception in getAttachmentProbCodeValues');
2466 RAISE;
2467
2468 END getAttachmentProbCodeValues;
2469
2470 PROCEDURE setup_log(p_filename IN VARCHAR2)
2471 IS
2472 BEGIN
2473 FND_FILE.PUT_NAMES(p_filename || '.log', p_filename || '.out',
2474 get_log_directory);
2475
2476
2477 FND_FILE.PUT_LINE(FND_FILE.LOG, '*** THIS FILE IS GENERATED BY ORACLE ISUPPORT UPGRADE PROCEDURE. IT IS INTENDED FOR DEVELOPMENT USE. *** ');
2478 FND_FILE.PUT_LINE(FND_FILE.LOG, '*** PLEASE DO NOT DISCARD. THIS FILE MAY BE REFERENCED IN THE FUTURE TO DIAGNOSE ISSUES EXPERIENCED BY THE CUSTOMER. *** ');
2479
2480 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
2481
2482 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG:setup_log', 'System time at the start of the upgrade process is: ' || fnd_date.date_to_charDT(sysdate));
2483
2484 END setup_log;
2485
2486
2487 PROCEDURE log_mesg(p_level IN NUMBER,
2488 p_module IN VARCHAR2,
2489 p_text IN VARCHAR2)
2490 IS
2491 BEGIN
2492 FND_FILE.PUT_LINE(FND_FILE.LOG, p_module || '- ' || p_text);
2493 --DBMS_OUTPUT.PUT_LINE(p_module || '-' || p_text);
2494
2495 /*
2496 IF (p_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2497 FND_LOG.STRING(p_level, p_module, p_text);
2498 END IF;
2499 */
2500 END log_mesg;
2501
2502 PROCEDURE wrapup(p_status IN VARCHAR2)
2503 IS
2504 BEGIN
2505 IF (p_status = 'SUCCESS') THEN
2506 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.wrapup', 'Upgrade process completed at: ' || fnd_date.date_to_charDT(sysdate));
2507 ELSIF (p_status = 'ERROR') THEN
2508 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.wrapup', 'Upgrade process terminated at: ' || fnd_date.date_to_charDT(sysdate));
2509 END IF;
2510
2511 End wrapup;
2512
2513 FUNCTION get_log_directory return VARCHAR2
2514 IS
2515
2516 utl_file_dir varchar2(2000);
2517 l_start number := 1;
2518 l_end number := 1;
2519 l_val varchar2(512);
2520 BEGIN
2521 -- We use v$parameter to determine the utl_file_dir
2522 SELECT value || ',' into utl_file_dir
2523 FROM v$parameter
2524 WHERE name = 'utl_file_dir';
2525
2526 -- We just pick the first one from the list
2527 l_end := instr(utl_file_dir, ',', l_start) - 1;
2528 l_val := substr(utl_file_dir, l_start, l_end - l_start + 1);
2529
2530 return l_val;
2531 END get_log_directory;
2532
2533
2534 FUNCTION Regions_Not_Already_Cloned(p_suffix IN VARCHAR2) RETURN BOOLEAN
2535 IS
2536
2537 l_count number := 0;
2538 BEGIN
2539 SELECT count(*) INTO l_count
2540 FROM ak_regions
2541 WHERE region_code in ('IBU_CF_SR_10_'|| p_suffix,
2542 'IBU_CF_SR_20_' || p_suffix,
2543 'IBU_CF_SR_30_' || p_suffix,
2544 'IBU_CF_SR_40_' || p_suffix,
2545 'IBU_CF_SR_50_' || p_suffix,
2546 'IBU_CF_SR_60_' || p_suffix,
2547 'IBU_CF_SR_70_' || p_suffix,
2548 'IBU_CF_SR_80_' || p_suffix,
2549 'IBU_CF_SR_90_' || p_suffix,
2550 'IBU_CF_SR_110_' || p_suffix,
2551 'IBU_CF_SR_120_' || p_suffix,
2552 'IBU_CF_SR_130_' || p_suffix,
2553 'IBU_CF_SR_140_' || p_suffix,
2554 'IBU_CF_SR_150_' || p_suffix,
2555 'IBU_CF_SR_160_' || p_suffix,
2556 'IBU_CF_SR_170_' || p_suffix,
2557 'IBU_CF_SR_180_' || p_suffix,
2558 'IBU_CF_SR_190_' || p_suffix,
2559 'IBU_CF_SR_200_' || p_suffix,
2560 'IBU_CF_SR_210_' || p_suffix,
2561 'IBU_CF_SR_220_' || p_suffix,
2562 'IBU_CF_SR_230_' || p_suffix,
2563 'IBU_CF_SR_240_' || p_suffix,
2564 'IBU_CF_SR_310_' || p_suffix,
2565 'IBU_CF_SR_320_' || p_suffix,
2566 'IBU_CF_SR_410_' || p_suffix,
2567 'IBU_CF_SR_420_' || p_suffix,
2568 'IBU_CF_SR_430_' || p_suffix,
2569 'IBU_CF_SR_440_' || p_suffix,
2570 'IBU_CF_SR_450_' || p_suffix);
2571
2572 IF (l_count > 0) THEN
2573 return FALSE;
2574 ELSE
2575 return TRUE;
2576 END IF;
2577 END Regions_Not_Already_Cloned ;
2578
2579 -- mkcyee 02/25/2004 - added to check if flow has already been cloned
2580 FUNCTION Flows_Not_Already_Cloned(p_flowId IN NUMBER) RETURN BOOLEAN
2581 IS
2582
2583 l_count number := 0;
2584 l_flowId NUMBER := 0-p_flowId;
2585 b_success BOOLEAN;
2586
2587 CURSOR l_cur (flowId IN NUMBER) IS
2588 SELECT count(*)
2589 FROM cs_cf_flows_b
2590 WHERE flow_id = flowId;
2591
2592 BEGIN
2593
2594 OPEN l_cur(l_flowId);
2595 FETCH l_cur INTO l_count;
2596 CLOSE l_cur;
2597
2598 IF (l_count > 0) THEN
2599 return FALSE;
2600 ELSE
2601 return TRUE;
2602 END IF;
2603
2604 EXCEPTION
2605 WHEN OTHERS THEN
2606 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG: Flows_Not_Already_Cloned', ' Unexpected exception in Flows_Not_Already_Cloned');
2607 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG: Flows_Not_Already_Cloned', ' p_flowId = ' + to_char(p_flowId));
2608
2609 IF (l_cur%ISOPEN) THEN
2610 CLOSE l_cur;
2611 END IF;
2612 RAISE;
2613
2614 END Flows_Not_Already_Cloned ;
2615
2616
2617 -- mkcyee 02/24/04 - added to check if config profile option has been customized
2618 -- mkcyee 01/03/06 - fix bug 4887917
2619 FUNCTION configProfileCustomized RETURN BOOLEAN IS
2620
2621 CURSOR l_cur IS
2622 SELECT b.last_updated_by
2623 FROM fnd_profile_options a, fnd_profile_option_values b
2624 WHERE a.profile_option_id = b.profile_option_id
2625 AND a.profile_option_name = 'IBU_REGION_FIELD_CONFIG_OPTION'
2626 AND B.LEVEL_ID = 10001
2627 AND A.APPLICATION_ID = 672
2628 AND A.APPLICATION_ID = B.APPLICATION_ID;
2629
2630 l_lub NUMBER;
2631 b_success BOOLEAN;
2632
2633 BEGIN
2634
2635 OPEN l_cur;
2636 FETCH l_cur INTO l_lub;
2637
2638 b_success := l_cur%FOUND;
2639 IF (NOT b_success) THEN
2640 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG:configProfileCustomized', ' Could not find configuration profile');
2641 CLOSE l_cur;
2642 RAISE PROGRAM_ERROR;
2643 ELSE
2644 CLOSE l_cur;
2645 IF (l_lub in (-1,1,2)) THEN
2646 RETURN false;
2647 ELSE
2648 RETURN true;
2649 END IF;
2650 END IF;
2651
2652 EXCEPTION
2653 WHEN OTHERS THEN
2654 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG: configProfileCustomized', ' Unexpected exception in configProfileCustomized');
2655 IF (l_cur%ISOPEN) THEN
2656 CLOSE l_cur;
2657 END IF;
2658 RAISE;
2659
2660
2661 END configProfileCustomized;
2662
2663
2664 /*
2665 * mkcyee 02/25/2004 - Create new function to clone a flow
2666 * p_newFlowId is the flow id of the newly cloned flow
2667 * p_flowId is the flow that we want to clone
2668 */
2669
2670 PROCEDURE Clone_Flow(p_newFlowId in NUMBER, p_flowId in NUMBER) IS
2671
2672 l_flow_rec cs_cf_flows_vl%rowtype;
2673 l_page_rec cs_cf_flow_pages_vl%rowtype;
2674
2675 l_newFlowId NUMBER := 0-p_newFlowId;
2676 l_rowid VARCHAR2(50);
2677
2678 CURSOR l_flow_cur(l_flowId IN NUMBER) IS
2679 SELECT *
2680 FROM cs_cf_flows_vl
2681 WHERE flow_id = l_flowId;
2682
2683 CURSOR l_page_cur(l_flowId IN NUMBER) IS
2684 SELECT *
2685 FROM cs_cf_flow_pages_vl
2686 WHERE flow_id = l_flowId;
2687
2688 b_success BOOLEAN;
2689 l_created_by NUMBER := fnd_load_util.owner_id('ORACLE');
2690
2691 BEGIN
2692
2693 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Clone_Flow', ' Cloning Flow for p_newFlowId = ' || to_char(p_newFlowId));
2694 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Clone_Flow', ' Cloning Flow for p_flowId = ' || to_char(p_flowId));
2695
2696 OPEN l_flow_cur(p_flowId);
2697 FETCH l_flow_cur INTO l_flow_rec;
2698
2699 b_success := l_flow_cur%FOUND;
2700 IF (NOT b_success) THEN
2701 log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_UTL_PKG.Clone_Flow', ' Could not successfully execute cursor! ');
2702 close l_flow_cur;
2703 RAISE PROGRAM_ERROR;
2704 ELSE
2705 close l_flow_cur;
2706
2707 -- mkcyee 02/26/2004 - we need to supply a unique flow display name for the
2708 -- cloned flow because we have a unique index defined on flow_display_name
2709 -- and language on the table CS_CF_FLOWS_TL
2710 CS_CF_FLOWS_PKG.Insert_Row(X_ROWID => l_rowid,
2711 X_FLOW_ID => l_newFlowId,
2712 X_FLOW_TYPE_CODE => l_flow_rec.flow_type_code,
2713 X_FLOW_DISPLAY_NAME => 'Cloned Flow for Flow Id: ' || to_char(p_newFlowId),
2714 X_OBJECT_VERSION_NUMBER => 1,
2715 X_ATTRIBUTE_CATEGORY => l_flow_rec.attribute_category,
2716 X_ATTRIBUTE1 => l_flow_rec.attribute1,
2717 X_ATTRIBUTE2 => l_flow_rec.attribute2,
2718 X_ATTRIBUTE3 => l_flow_rec.attribute3,
2719 X_ATTRIBUTE4 => l_flow_rec.attribute4,
2720 X_ATTRIBUTE5 => l_flow_rec.attribute5,
2721 X_ATTRIBUTE6 => l_flow_rec.attribute6,
2722 X_ATTRIBUTE7 => l_flow_rec.attribute7,
2723 X_ATTRIBUTE8 => l_flow_rec.attribute8,
2724 X_ATTRIBUTE9 => l_flow_rec.attribute9,
2725 X_ATTRIBUTE10 => l_flow_rec.attribute10,
2726 X_ATTRIBUTE11 => l_flow_rec.attribute11,
2727 X_ATTRIBUTE12 => l_flow_rec.attribute12,
2728 X_ATTRIBUTE13 => l_flow_rec.attribute13,
2729 X_ATTRIBUTE14 => l_flow_rec.attribute14,
2730 X_ATTRIBUTE15 => l_flow_rec.attribute15,
2731 X_SEEDED_FLAG => 'N',
2732 X_CREATION_DATE => sysdate,
2733 X_CREATED_BY => l_created_by,
2734 X_LAST_UPDATE_DATE => sysdate,
2735 X_LAST_UPDATED_BY => l_created_by,
2736 X_LAST_UPDATE_LOGIN => 0);
2737
2738
2739 OPEN l_page_cur(p_flowId);
2740 FETCH l_page_cur INTO l_page_rec;
2741
2742 WHILE l_page_cur%FOUND LOOP
2743 CS_CF_FLOW_PAGES_PKG.INSERT_ROW(X_ROWID => l_rowid,
2744 X_FLOW_ID => l_newFlowId,
2745 X_FLOW_TYPE_PAGE_ID => l_page_rec.flow_type_page_id,
2746 X_ENABLED_FLAG => l_page_rec.enabled_flag,
2747 X_OBJECT_VERSION_NUMBER => 1,
2748 X_ATTRIBUTE_CATEGORY => l_page_rec.attribute_category,
2749 X_ATTRIBUTE1 => l_page_rec.attribute1,
2750 X_ATTRIBUTE2 => l_page_rec.attribute2,
2751 X_ATTRIBUTE3 => l_page_rec.attribute3,
2752 X_ATTRIBUTE4 => l_page_rec.attribute4,
2753 X_ATTRIBUTE5 => l_page_rec.attribute5,
2754 X_ATTRIBUTE6 => l_page_rec.attribute6,
2755 X_ATTRIBUTE7 => l_page_rec.attribute7,
2756 X_ATTRIBUTE8 => l_page_rec.attribute8,
2757 X_ATTRIBUTE9 => l_page_rec.attribute9,
2758 X_ATTRIBUTE10 => l_page_rec.attribute10,
2759 X_ATTRIBUTE11 => l_page_rec.attribute11,
2760 X_ATTRIBUTE12 => l_page_rec.attribute12,
2761 X_ATTRIBUTE13 => l_page_rec.attribute13,
2762 X_ATTRIBUTE14 => l_page_rec.attribute14,
2763 X_ATTRIBUTE15 => l_page_rec.attribute15,
2764 X_PAGE_DISPLAY_NAME => l_page_rec.page_display_name,
2765 X_CREATION_DATE => sysdate,
2766 X_CREATED_BY => l_created_by,
2767 X_LAST_UPDATE_DATE => sysdate,
2768 X_LAST_UPDATED_BY => l_created_by,
2769 X_LAST_UPDATE_LOGIN => 0);
2770
2771 FETCH l_page_cur INTO l_page_rec;
2772 END LOOP;
2773 CLOSE l_page_cur;
2774
2775 END IF;
2776
2777 EXCEPTION
2778 WHEN OTHERS THEN
2779 log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_UTL_PKG:Clone_Flow',
2780 'Unexpected exception - p_newFlowId: ' || to_char(p_newFlowId) || ' p_flowId: ' || to_char(p_flowId));
2781
2782 IF (l_page_cur%ISOPEN) THEN
2783 CLOSE l_page_cur;
2784 END IF;
2785
2786 IF (l_flow_cur%ISOPEN) THEN
2787 CLOSE l_flow_cur;
2788 END IF;
2789
2790 RAISE;
2791
2792 END Clone_Flow;
2793
2794 END CS_CF_UPG_UTL_PKG;