[Home] [Help]
PACKAGE BODY: APPS.CS_CF_UPG_PKG
Source
1 PACKAGE BODY CS_CF_UPG_PKG as
2 /* $Header: cscfupgb.pls 120.1 2005/06/14 10:00:54 appldev $ */
3
4 CURSOR does_region_already_exists (p_region_code VARCHAR2, p_region_application_id NUMBER)
5 IS
6 SELECT count(*) from ak_regions
7 where region_application_id = p_region_application_id
8 and region_code = p_region_code;
9
10 PROCEDURE Upgrade_Main IS
11
12 l_region_upgrade_required boolean := FALSE;
13 l_flow_upgrade_required boolean := FALSE;
14 l_respTable CS_CF_UPG_UTL_PKG.RespTable;
15 l_applTable CS_CF_UPG_UTL_PKG.ApplTable;
16 l_siteProfilesTable CS_CF_UPG_UTL_PKG.ProfileTable;
17 l_return_value boolean := FALSE;
18
19 l_flowRespTable CS_CF_UPG_UTL_PKG.RespTable;
20 l_flowApplTable CS_CF_UPG_UTL_PKG.ApplTable;
21 l_flowProfilesTable CS_CF_UPG_UTL_PKG.ProfileTable;
22
23 l_logfilename VARCHAR2(2000) := '';
24 l_region_fresh_install BOOLEAN := TRUE;
25
26
27 BEGIN
28
29 -- generate a suffix to the filename
30 select to_char(sysdate, 'mm-dd-yy') || to_char(sysdate, 'hh24:mi:ss')
31 into l_logfilename
32 from dual;
33
34 CS_CF_UPG_UTL_PKG.setup_log('IBUCFUPG-' || l_logfilename);
35
36
37 l_region_upgrade_required := Is_Region_Upgrade_Required(l_respTable,l_applTable, l_siteProfilesTable);
38
39 -- if l_region_upgrade_required , do region upgrade
40
41 IF (l_region_upgrade_required) THEN
42 Do_Region_Upgrade(l_respTable, l_applTable, l_siteProfilesTable);
43 END IF;
44
45 l_flow_upgrade_required := Is_Flow_Upgrade_Required(l_flowRespTable, l_flowApplTable, l_flowProfilesTable);
46
47 IF (l_flow_upgrade_required) THEN
48 Do_Flow_Upgrade(l_respTable, l_applTable, l_flowProfilesTable);
49 END IF;
50
51 -- if l_upgrade_required, do flow upgrade
52
53 IF (l_region_upgrade_required OR l_flow_upgrade_required) THEN
54 -- Check if the config profile has been customized
55 IF (CS_CF_UPG_UTL_PKG.configProfileCustomized) THEN
56 -- The profile has been customized. Don't overwrite
57 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Upgrade_Main', 'Configuration profile has been customized. Will not overwrite.');
58 ELSE
59 -- Update the profile to set to CUSTOM
60 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Upgrade_Main', 'Saving profile to CUSTOM');
61
62 l_return_value := FND_PROFILE.SAVE('IBU_REGION_FIELD_CONFIG_OPTION', 'CUSTOM', 'SITE');
63 IF NOT(l_return_value) THEN
64 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Upgrade_Main', 'Saving profile to CUSTOM was unsuccessful');
65
66 RAISE PROGRAM_ERROR;
67 END IF ;
68 commit;
69 END IF;
70 END IF;
71
72 CS_CF_UPG_UTL_PKG.wrapup('SUCCESS');
73
74 EXCEPTION
75 WHEN OTHERS THEN
76 CS_CF_UPG_UTL_PKG.wrapup('ERROR');
77 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_PKG.Upgrade_Main','Exception raised in Upgrade_Main');
78 RAISE;
79 End Upgrade_Main;
80
81 /*
82 * Returns the list of unique responsibilities
83 * and applications for which customization of the
84 * configuration profiles affecting region
85 * configurations have been made.
86 * If there are any responsibilities or applications
87 * for which a non-default value has been set,
88 * this function will return true, otherwise false.
89 */
90 FUNCTION Is_Region_Upgrade_Required(p_respTable OUT NOCOPY CS_CF_UPG_UTL_PKG.RespTable,
91 p_applTable OUT NOCOPY CS_CF_UPG_UTL_PKG.ApplTable,
92 p_siteProfilesTable OUT NOCOPY CS_CF_UPG_UTL_PKG.ProfileTable)
93 RETURN BOOLEAN
94 IS
95 l_resp_index NUMBER := 0;
96 l_appl_index NUMBER := 0;
97 l_site_index NUMBER := 0;
98
99 l_upgrade_required BOOLEAN := FALSE;
100 l_upg_return_value BOOLEAN := FALSE;
101
102 BEGIN
103
104 -- Call the following utility apis to retrieve the list of unique
105 -- responsibilities, and applications for which configuration profiles have
106 -- been customized.
107 -- The apis will add the responsibility and application to the table(s)
108 -- and maintain the indexes on them.
109
110 l_upg_return_value := CS_CF_UPG_UTL_PKG.Eval_SR_Account_Option(l_resp_index,
111 p_respTable,
112 l_appl_index,
113 p_applTable,
114 l_site_index,
115 p_siteProfilesTable);
116 IF (l_upg_return_value) THEN
117 l_upgrade_required := TRUE;
118 END IF;
119
120 l_upg_return_value := CS_CF_UPG_UTL_PKG.Eval_SR_Problem_Code_Option(l_resp_index,
121 p_respTable,
122 l_appl_index,
123 p_applTable,
124 l_site_index,
125 p_siteProfilesTable);
126 IF (l_upg_return_value) THEN
127 l_upgrade_required := TRUE;
128 END IF;
129
130
131 l_upg_return_value := CS_CF_UPG_UTL_PKG.Eval_SR_Creation_Prod_Option(l_appl_index,
132 p_applTable,
133 l_resp_index,
134 p_respTable,
135 l_site_index,
136 p_siteProfilesTable);
137 IF (l_upg_return_value) THEN
138 l_upgrade_required := TRUE;
139 END IF;
140
141 l_upg_return_value := CS_CF_UPG_UTL_PKG.Eval_SR_Addr_Display(l_appl_index,
142 p_applTable,
143 l_resp_index,
144 p_respTable,
145 l_site_index,
146 p_siteProfilesTable);
147 IF (l_upg_return_value) THEN
148 l_upgrade_required := TRUE;
149 END IF;
150
151 l_upg_return_value := CS_CF_UPG_UTL_PKG.Eval_SR_Addr_Mandatory(l_appl_index,
152 p_applTable,
153 l_resp_index,
154 p_respTable,
155 l_site_index,
156 p_siteProfilesTable);
157 IF (l_upg_return_value) THEN
158 l_upgrade_required := TRUE;
159 END IF;
160
161 l_upg_return_value := CS_CF_UPG_UTL_PKG.Eval_SR_BillTo_Address_Option(l_resp_index,
162 p_respTable,
163 l_appl_index,
164 p_applTable,
165 l_site_index,
166 p_siteProfilesTable);
167 IF (l_upg_return_value) THEN
168 l_upgrade_required := TRUE;
169 END IF;
170
171 l_upg_return_value := CS_CF_UPG_UTL_PKG.Eval_SR_BillTo_Contact_Option(l_resp_index,
172 p_respTable,
173 l_appl_index,
174 p_applTable,
175 l_site_index,
176 p_siteProfilesTable);
177 IF (l_upg_return_value) THEN
178 l_upgrade_required := TRUE;
179 END IF;
180
181 l_upg_return_value := CS_CF_UPG_UTL_PKG.Eval_SR_ShipTo_Address_Option(l_resp_index,
182 p_respTable,
183 l_appl_index,
184 p_applTable,
185 l_site_index,
186 p_siteProfilesTable);
187 IF (l_upg_return_value) THEN
188 l_upgrade_required := TRUE;
189 END IF;
190
191 l_upg_return_value := CS_CF_UPG_UTL_PKG.Eval_SR_ShipTo_Contact_Option(l_resp_index,
192 p_respTable,
193 l_appl_index,
194 p_applTable,
195 l_site_index,
196 p_siteProfilesTable);
197 IF (l_upg_return_value) THEN
198 l_upgrade_required := TRUE;
199 END IF;
200
201
202 l_upg_return_value := CS_CF_UPG_UTL_PKG.Eval_SR_InstalledAt_Address(l_resp_index,
203 p_respTable,
204 l_appl_index,
205 p_applTable,
206 l_site_index,
207 p_siteProfilesTable);
208 IF (l_upg_return_value) THEN
209 l_upgrade_required := TRUE;
210 END IF;
211
212 l_upg_return_value := CS_CF_UPG_UTL_PKG.Eval_SR_Attachment_Option(l_resp_index,
213 p_respTable,
214 l_appl_index,
215 p_applTable,
216 l_site_index,
217 p_siteProfilesTable);
218 IF (l_upg_return_value) THEN
219 l_upgrade_required := TRUE;
220 END IF;
221
222 l_upg_return_value := CS_CF_UPG_UTL_PKG.Eval_SR_Task_Display(l_appl_index,
223 p_applTable,
224 l_resp_index,
225 p_respTable,
226 l_site_index,
227 p_siteProfilesTable);
228 IF (l_upg_return_value) THEN
229 l_upgrade_required := TRUE;
230 END IF;
231
232 l_upg_return_value := CS_CF_UPG_UTL_PKG.Eval_SR_Enable_Interact_Log(l_resp_index,
233 p_respTable,
234 l_appl_index,
235 p_applTable,
236 l_site_index,
237 p_siteProfilesTable);
238 IF (l_upg_return_value) THEN
239 l_upgrade_required := TRUE;
240 END IF;
241
242 l_upg_return_value := CS_CF_UPG_UTL_PKG.Eval_SR_Enable_Template(l_appl_index,
243 p_applTable,
244 l_resp_index,
245 p_respTable,
246 l_site_index,
247 p_siteProfilesTable);
248 IF (l_upg_return_value) THEN
249 l_upgrade_required := TRUE;
250 END IF;
251
252 l_upg_return_value := CS_CF_UPG_UTL_PKG.Eval_SR_Product_Selection(l_appl_index,
253 p_applTable,
254 l_resp_index,
255 p_respTable,
256 l_site_index,
257 p_siteProfilesTable);
258 IF (l_upg_return_value) THEN
259 l_upgrade_required := TRUE;
260 END IF;
261
262 IF (l_upgrade_required) THEN
263 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Is_Region_Upgrade_Required','Is_Region_Upgrade_Required returns true');
264 ELSE
265 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Is_Region_Upgrade_Required','Is_Region_Upgrade_Required returns false');
266 END IF;
267 return l_upgrade_required;
268
269 EXCEPTION
270 WHEN OTHERS THEN
271 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_PKG.Is_Region_Upgrade_Required','Exception raised in Is_Region_Upgrade_Required');
272 RAISE;
273
274 END Is_Region_Upgrade_Required;
275
276 /*
277 * Returns the list of unique responsibilities
278 * and applications for which customization of the
279 * configuration profiles affecting page flow
280 * configurations have been made.
281 * If there are any responsibilities or applications
282 * for which a non-default value has been set,
283 * this function will return true, otherwise false.
284 */
285
286 FUNCTION Is_Flow_Upgrade_Required(p_respTable OUT NOCOPY CS_CF_UPG_UTL_PKG.RespTable,
287 p_applTable OUT NOCOPY CS_CF_UPG_UTL_PKG.ApplTable,
288 p_siteProfilesTable OUT NOCOPY CS_CF_UPG_UTL_PKG.ProfileTable)
289 RETURN BOOLEAN
290 IS
291 l_resp_index NUMBER := 0;
292 l_appl_index NUMBER := 0;
293 l_site_index NUMBER := 0;
294
295 l_upgrade_required BOOLEAN := FALSE;
296 l_upg_return_value BOOLEAN := FALSE;
297
298
299 BEGIN
300
301 l_upg_return_value := CS_CF_UPG_UTL_PKG.Eval_SR_KB_Option(l_appl_index,
302 p_applTable,
303 l_resp_index,
304 p_respTable,
305 l_site_index,
306 p_siteProfilesTable);
307
308 IF (l_upg_return_value) THEN
309 l_upgrade_required := TRUE;
310 END IF;
311
312 IF (l_upgrade_required) THEN
313 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Is_Flow_Upgrade_Required','Is_Flow_Upgrade_Required returns true');
314 ELSE
315 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Is_Flow_Upgrade_Required','Is_Flow_Upgrade_Required returns false');
316 END IF;
317 return l_upgrade_required;
318 END Is_Flow_Upgrade_Required;
319
320 /*
321 * Top level procedure for performing region
322 * upgrades; Internally this will call
323 * region upgrades for each profile level, ie resp, application, etc
324 */
325 PROCEDURE Do_Region_Upgrade(p_respTable IN CS_CF_UPG_UTL_PKG.RespTable, p_applTable IN CS_CF_UPG_UTL_PKG.ApplTable, p_siteProfilesTable IN CS_CF_UPG_UTL_PKG.ProfileTable)
326 IS
327
328 BEGIN
329
330 Do_Region_Upgrades_For_Resp(p_respTable);
331 Do_Region_Upgrades_For_Appl(p_applTable);
332 Do_Region_Upgrades_For_Global(p_siteProfilesTable);
333
334 END Do_Region_Upgrade;
335
336 /*
337 * Procedure to performing upgrades for responsibility level
338 * For each resp
339 */
340 PROCEDURE Do_Region_Upgrades_For_Resp(p_respTable IN CS_CF_UPG_UTL_PKG.RespTable)
341 IS
342
343 -- this picks up all the configuration profiles
344 -- that affects region configuration for
345 -- a particular responsibility and it's corresponding
346 -- higher application level and site level
347
348 CURSOR get_profiles_for_resp (respId NUMBER, respApplId NUMBER)
349 IS
350 SELECT a.profile_option_name,
351 b.level_value,
352 b.level_value_application_id,
353 b.profile_option_value,
354 1 "PRIORITY"
355 FROM fnd_profile_option_values b, fnd_profile_options a
356 WHERE b.level_id = 10003
357 AND b.level_value = respId
358 AND b.level_value_application_id = respApplId
359 AND a.profile_option_id = b.profile_option_id
360 AND b.application_id = 672
364 'IBU_SR_ADDR_DISPLAY',
361 AND a.profile_option_name in ('IBU_A_SR_ACCOUNT_OPTION',
362 'IBU_A_SR_PROB_CODE_MANDATORY',
363 'IBU_SR_CREATION_PRODUCT_OPTION',
365 'IBU_SR_ADDR_MANDATORY',
366 'IBU_A_SR_BILLTO_ADDRESS_OPTION',
367 'IBU_A_SR_BILLTO_CONTACT_OPTION',
368 'IBU_A_SR_SHIPTO_ADDRESS_OPTION',
369 'IBU_A_SR_SHIPTO_CONTACT_OPTION',
370 'IBU_A_SR_INSTALLEDAT_ADDRESS_OPTION',
371 'IBU_A_SR_ATTACHMENT_OPTION',
372 'IBU_SR_TASK_DISPLAY',
373 'IBU_A_SR_ENABLE_INTERACTION_LOGGING',
374 'IBU_SR_ENABLE_TEMPLATE',
375 'IBU_A_SR_PRODUCT_SELECTION_OPTION')
376 UNION
377 SELECT a.profile_option_name,
378 b.level_value,
379 b.level_value_application_id,
380 b.profile_option_value,
381 2 "PRIORITY"
382 FROM fnd_profile_option_values b, fnd_profile_options a
383 WHERE b.level_id = 10002
384 AND b.level_value = respApplId
385 AND a.profile_option_id = b.profile_option_id
386 AND b.application_id = 672
387 AND a.profile_option_name in ('IBU_A_SR_ACCOUNT_OPTION',
388 'IBU_A_SR_PROB_CODE_MANDATORY',
389 'IBU_SR_CREATION_PRODUCT_OPTION',
390 'IBU_SR_ADDR_DISPLAY',
391 'IBU_SR_ADDR_MANDATORY',
392 'IBU_A_SR_BILLTO_ADDRESS_OPTION',
393 'IBU_A_SR_BILLTO_CONTACT_OPTION',
394 'IBU_A_SR_SHIPTO_ADDRESS_OPTION',
395 'IBU_A_SR_SHIPTO_CONTACT_OPTION',
396 'IBU_A_SR_INSTALLEDAT_ADDRESS_OPTION',
397 'IBU_A_SR_ATTACHMENT_OPTION',
398 'IBU_SR_TASK_DISPLAY',
399 'IBU_A_SR_ENABLE_INTERACTION_LOGGING',
400 'IBU_SR_ENABLE_TEMPLATE',
401 'IBU_A_SR_PRODUCT_SELECTION_OPTION')
402 UNION
403 SELECT a.profile_option_name,
404 b.level_value,
405 b.level_value_application_id,
406 b.profile_option_value,
407 3 "PRIORITY"
408 FROM fnd_profile_option_values b, fnd_profile_options a
409 WHERE b.level_id = 10001
410 AND a.profile_option_id = b.profile_option_id
411 AND b.application_id = 672
412 AND a.profile_option_name in ('IBU_A_SR_ACCOUNT_OPTION',
413 'IBU_A_SR_PROB_CODE_MANDATORY',
414 'IBU_SR_CREATION_PRODUCT_OPTION',
415 'IBU_SR_ADDR_DISPLAY',
416 'IBU_SR_ADDR_MANDATORY',
417 'IBU_A_SR_BILLTO_ADDRESS_OPTION',
418 'IBU_A_SR_BILLTO_CONTACT_OPTION',
419 'IBU_A_SR_SHIPTO_ADDRESS_OPTION',
420 'IBU_A_SR_SHIPTO_CONTACT_OPTION',
421 'IBU_A_SR_INSTALLEDAT_ADDRESS_OPTION',
422 'IBU_A_SR_ATTACHMENT_OPTION',
423 'IBU_SR_TASK_DISPLAY',
424 'IBU_A_SR_ENABLE_INTERACTION_LOGGING',
425 'IBU_SR_ENABLE_TEMPLATE',
426 'IBU_A_SR_PRODUCT_SELECTION_OPTION')
427 ORDER BY PROFILE_OPTION_NAME, PRIORITY;
428
429
430 l_count NUMBER := p_respTable.COUNT;
431 l_index NUMBER := 0;
432 l_index2 NUMBER := 0;
433 l_profile_option_name FND_PROFILE_OPTIONS.profile_option_name%TYPE;
434 l_level_value FND_PROFILE_OPTION_VALUES.level_value%TYPE;
435 l_level_value_application_id FND_PROFILE_OPTION_VALUES.level_value_application_id%TYPE;
436 l_profile_option_value FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE;
437 l_priority NUMBER := 0;
438
439 l_ProfileTable CS_CF_UPG_UTL_PKG.ProfileTable;
440
441 BEGIN
442
443 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG: Do_Region_Upgrades_For_Resp', 'Processing Do_Region_Upgrades_For_Resp');
444
445
446 WHILE (l_index < l_count) LOOP
447 -- Get the list of profile names and their values
448 -- and store it in a table
449 -- This table may contain duplicate entries for
450 -- each profile because we pick up the values
451 -- for the application and site level also
452 OPEN get_profiles_for_resp(p_respTable(l_index).respId, p_respTable(l_index).respApplId);
453 -- Retrieve information into the local variables
454 FETCH get_profiles_for_resp INTO l_profile_option_name,
455 l_level_value,
456 l_level_value_application_id,
457 l_profile_option_value,
458 l_priority;
459 WHILE get_profiles_for_resp%FOUND LOOP
460
461 l_ProfileTable(l_index2).profileOptionName := l_profile_option_name;
462 l_ProfileTable(l_index2).profileOptionValue := l_profile_option_value;
463
464 l_index2 := l_index2 + 1;
465
466 FETCH get_profiles_for_resp INTO l_profile_option_name,
467 l_level_value,
468 l_level_value_application_id,
469 l_profile_option_value,
470 l_priority;
471
472 END LOOP;
473 CLOSE get_profiles_for_resp;
474
475 -- Now we have a table of profiles
479 Clone_Regions_For_Resp(l_ProfileTable, p_respTable(l_index).respId,
476 -- Determine which regions need to be cloned for this resp
477
478 IF (CS_CF_UPG_UTL_PKG.Regions_Not_Already_Cloned('R' || p_respTable(l_index).respId)) THEN
480 p_respTable(l_index).respApplId);
481 commit;
482 END IF;
483
484 -- now clean up the table so it can be reused
485 l_ProfileTable.DELETE;
486 l_index2 := 0;
487 l_index := l_index + 1;
488
489 END LOOP; -- ends while loop
490
491 EXCEPTION
492 WHEN OTHERS THEN
493 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_PKG: Do_Region_Upgrades_For_Resp', 'Exception in Do_Region_Upgrades_For_Resp');
494 IF (get_profiles_for_resp%ISOPEN) THEN
495 CLOSE get_profiles_for_resp;
496 END IF;
497 RAISE;
498
499 END Do_Region_Upgrades_For_Resp;
500
501
502 /*
503 * Perform the actually cloning
504 * of ak regions, based on the list of
505 * profiles that are customized at the resp level
506 */
507 PROCEDURE Clone_Regions_For_Resp(p_ProfileTable IN CS_CF_UPG_UTL_PKG.ProfileTable,
508 p_respId IN FND_PROFILE_OPTION_VALUES.level_value%TYPE,
509 p_respApplId IN FND_PROFILE_OPTION_VALUES.level_value_application_id%TYPE)
510 IS
511 l_count NUMBER := p_ProfileTable.COUNT;
512 l_index NUMBER := 0;
513 l_profileOptionName FND_PROFILE_OPTIONS.profile_option_name%TYPE;
514 l_profileOptionValue FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE;
515 l_newPrimaryContactRegionCode VARCHAR2(30) := 'IBU_CF_SR_10_G';
516 l_newAddressRegionCode VARCHAR2(30) := 'IBU_CF_SR_20_G';
517 l_newUpdateAddressRegionCode VARCHAR2(30) := 'IBU_CF_SR_25_G';
518 l_newIdentifyProductRegionCode VARCHAR2(30) := 'IBU_CF_SR_30_G';
519 l_newTemplateProductRegionCode VARCHAR2(30) := 'IBU_CF_SR_35_G';
520 l_newIdentifyProblemRegionCode VARCHAR2(30) := 'IBU_CF_SR_40_G';
521 l_newReviewRegionCode VARCHAR2(30) := 'IBU_CF_SR_50_G';
522 l_newProblemDetailsRegionCode VARCHAR2(30) := 'IBU_CF_SR_60_G';
523 l_newContactInfoRegionCode VARCHAR2(30) := 'IBU_CF_SR_70_G';
524 l_newDtlOverviewRegionCode VARCHAR2(30) := 'IBU_CF_SR_130_G';
525 l_newDtlContactAddrRegionCode VARCHAR2(30) := 'IBU_CF_SR_210_G';
526 l_newDtlTabsRegionCode VARCHAR2(30) := 'IBU_CF_SR_160_G';
527 l_newDtlDetailsRegionCode VARCHAR2(30) := 'IBU_CF_SR_190_G';
528 l_newDtlProgOptionsRegionCode VARCHAR2(30) := 'IBU_CF_SR_110_G';
529 l_newDtlProgressRegionCode VARCHAR2(30) := 'IBU_CF_SR_120_G';
530 l_newDtlResolnRegionCode VARCHAR2(30) := 'IBU_CF_SR_150_G';
531 l_newCreateTemplateRegionCode VARCHAR2(30) := 'IBU_CF_SR_310_G';
532 l_newUpdateTemplateRegionCode VARCHAR2(30) := 'IBU_CF_SR_320_G';
533 l_newCreateViewRegionCode VARCHAR2(30) := 'IBU_CF_SR_430_G';
534 l_newUpdateViewRegionCode VARCHAR2(30) := 'IBU_CF_SR_440_G';
535 l_newSearchViewRegionCode VARCHAR2(30) := 'IBU_CF_SR_450_G';
536 l_newProductFilterRegionCode VARCHAR2(30) := 'IBU_CF_SR_420_G';
537 l_newRegProductRegionCode VARCHAR2(30) := 'IBU_CF_SR_80_G';
538 l_newAllProductRegionCode VARCHAR2(30) := 'IBU_CF_SR_90_G';
539 l_newFilterRegionCode VARCHAR2(30) := 'IBU_CF_SR_410_G';
540
541
542 l_displayBillToAddress FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := 'N';
543 l_displayBillToContact FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := 'N';
544 l_displayShipToAddress FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := 'N';
545 l_displayShipToContact FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := 'N';
546 l_displayInstalledAtAddr FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := 'N';
547 l_displayIncidentAddr FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := 'N';
548 l_mandatoryIncidentAddr FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := 'N';
549 l_displayAttachment FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
550 l_displayRegProducts FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
551 l_displayAllProducts FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
552 l_enableTemplate FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
553 l_displayTasks FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
554 l_mandatoryProblemCode FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
555
556
557
558 l_region_count NUMBER := 0;
559
560 l_respId VARCHAR2(10) := to_char(p_respId);
561 l_respApplId VARCHAR2(10) := to_char(p_respApplId);
562
563 l_newIdentifyProblemExists BOOLEAN := FALSE;
564 l_newPrimaryContactExists BOOLEAN := FALSE;
565 l_newProblemDetailsExists BOOLEAN := FALSE;
566 l_newAddressExists BOOLEAN := FALSE;
567 l_newUpdateAddressExists BOOLEAN := FALSE;
568 l_newContactInfoExists BOOLEAN := FALSE;
569 l_newReviewExists BOOLEAN := FALSE;
570 l_newDtlOverviewExists BOOLEAN := FALSE;
571 l_newDtlContactAddrExists BOOLEAN := FALSE;
572 l_newDtlTabsExists BOOLEAN := FALSE;
573 l_newDtlProgressOptionsExists BOOLEAN := FALSE;
574 l_newDtlResolnExists BOOLEAN := FALSE;
575 l_newDtlDetailsExists BOOLEAN := FALSE;
576 l_newDtlProgressExists BOOLEAN := FALSE;
577 l_newIdentifyProductExists BOOLEAN := FALSE;
578 l_newTemplateProductExists BOOLEAN := FALSE;
579 l_newCreateTemplateExists BOOLEAN := FALSE;
580 l_newUpdateTemplateExists BOOLEAN := FALSE;
581 l_newProductFilterExists BOOLEAN := FALSE;
582 l_newCreateViewExists BOOLEAN := FALSE;
583 l_newUpdateViewExists BOOLEAN := FALSE;
584 l_newSearchViewExists BOOLEAN := FALSE;
585 l_newRegProductExists BOOLEAN := FALSE;
586 l_newAllProductExists BOOLEAN := FALSE;
587 l_newFilterExists BOOLEAN := FALSE;
588
589 -- the following set of variables are used to
590 -- determine whether we've already examined
591 -- the profile option. If we have, we don't need to look at it again
592 l_examineSrAccountOption BOOLEAN := TRUE;
596 l_examineTemplateOption BOOLEAN := TRUE;
593 l_examineAddressOption BOOLEAN := TRUE;
594 l_examineCreateProdOption BOOLEAN := TRUE;
595 l_examineAttachmentOption BOOLEAN := TRUE;
597 l_examineLoggingOption BOOLEAN := TRUE;
598 l_examineTaskOption BOOLEAN := TRUE;
599 l_examineProdSelectOption BOOLEAN := TRUE;
600 l_examineProbCodeOption BOOLEAN := TRUE;
601
602 BEGIN
603
604 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Resp', 'Cloning regions for respId: ' || l_respId || ' respApplId: ' || l_respApplId);
605 WHILE (l_index < l_count) LOOP
606 l_profileOptionName := p_ProfileTable(l_index).profileOptionName;
607 l_profileOptionValue := p_ProfileTable(l_index).profileOptionValue;
608
609 IF (l_profileOptionName = 'IBU_A_SR_ACCOUNT_OPTION' AND l_examineSrAccountOption) THEN
610 -- clone the region
611 l_newPrimaryContactRegionCode := 'IBU_CF_SR_10_R' || p_respId;
612
613 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_10_G',
614 672,
615 l_newPrimaryContactRegionCode,
616 672, FALSE);
617
618 IF (l_profileOptionValue = 'OPTIONAL') THEN
619 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newPrimaryContactRegionCode,
620 'IBU_CF_SR_ACCOUNT_NUMBER',
621 'Y', 'N', null);
622 END IF;
623 l_examineSrAccountOption := FALSE;
624 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Resp', 'Done with SR Account Option');
625 ELSIF (l_profileOptionName = 'IBU_A_SR_PRODUCT_SELECTION_OPTION' AND l_examineProdSelectOption) THEN
626 -- clone the regions
627 l_newRegProductRegionCode := 'IBU_CF_SR_80_R' || p_respId;
628
629 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_80_G',
630 672,
631 l_newRegProductRegionCode,
632 672, FALSE);
633
634 IF (l_profileOptionValue = 'N') THEN
635 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newRegProductRegionCode,
636 'IBU_CF_SR_R_PROD_NAME_LOV',
637 'Y', 'N', null);
638
639 END IF;
640 l_newAllProductRegionCode := 'IBU_CF_SR_90_R' || p_respId;
641
642 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_90_G',
643 672,
644 l_newAllProductRegionCode,
645 672, FALSE);
646
647 IF (l_profileOptionValue = 'N') THEN
648 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAllProductRegionCode,
649 'IBU_CF_SR_PROD_BY_NAME_LOV',
650 'Y', 'N', null);
651
652 END IF;
653 l_examineProdSelectOption := FALSE;
654 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Resp', 'Done with SR Product Selection Option');
655 ELSIF ((l_profileOptionName = 'IBU_A_SR_BILLTO_ADDRESS_OPTION' OR
656 l_profileOptionName = 'IBU_A_SR_BILLTO_CONTACT_OPTION' OR
657 l_profileOptionName = 'IBU_A_SR_SHIPTO_ADDRESS_OPTION' OR
658 l_profileOptionName = 'IBU_A_SR_SHIPTO_CONTACT_OPTION' OR
659 l_profileOptionName = 'IBU_A_SR_INSTALLEDAT_ADDRESS_OPTION' OR
660 l_profileOptionName = 'IBU_SR_ADDR_DISPLAY' OR
661 l_profileOptionName = 'IBU_SR_ADDR_MANDATORY') AND
662 l_examineAddressOption) THEN
663
664 -- we only want to clone the address region once if any of the
665 -- address profile options are customized, but we need to clone
666 -- two of them, one for create, and one for update
667
668 l_newAddressRegionCode := 'IBU_CF_SR_20_R' || p_respId;
669 l_newUpdateAddressRegionCode := 'IBU_CF_SR_25_R' || p_respId;
670
671 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_20_G',
672 672,
673 l_newAddressRegionCode,
674 672, FALSE);
675
676 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_25_G',
677 672,
678 l_newUpdateAddressRegionCode,
679 672, FALSE);
680
681
682 -- get all the address-specific profile option values
683 -- for this resp
684
685 CS_CF_UPG_UTL_PKG.getAddressProfileValues(p_ProfileTable,
686 l_displayBillToAddress,
687 l_displayBillToContact,
688 l_displayShipToAddress,
689 l_displayShipToContact,
690 l_displayInstalledAtAddr,
691 l_displayIncidentAddr,
692 l_mandatoryIncidentAddr);
693
694 IF (l_displayBillToAddress = 'Y' OR l_displayBillToContact = 'Y') THEN
695 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
696 'IBU_CF_SR_BILL_TO_HDR',
697 'Y', 'N', null);
698 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
699 'IBU_CF_SR_BILL_TO_HDR',
700 'Y', 'N', null);
701
702 END IF;
703
704 IF (l_displayBillToAddress = 'Y') THEN
705 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
706 'IBU_CF_SR_BILL_TO_ADDRESS',
707 l_displayBillToAddress, 'N', null);
708
709 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
710 'IBU_CF_SR_BILL_TO_ADDRESS',
714
711 l_displayBillToAddress, 'N', null);
712
713 END IF;
715 IF (l_displayBillToContact = 'Y') THEN
716 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
717 'IBU_CF_SR_BILL_TO_CONTACT',
718 l_displayBillToContact, 'N', null);
719
720 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
721 'IBU_CF_SR_BILL_TO_CONTACT',
722 l_displayBillToContact, 'N', null);
723
724 END IF;
725
726 IF (l_displayShipToAddress = 'Y' OR l_displayShipToContact = 'Y') THEN
727 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
728 'IBU_CF_SR_SHIP_TO_HDR',
729 'Y', 'N', null);
730
731 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
732 'IBU_CF_SR_SHIP_TO_HDR',
733 'Y', 'N', null);
734
735 END IF;
736
737 IF (l_displayShipToAddress = 'Y') THEN
738 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
739 'IBU_CF_SR_SHIP_TO_ADDRESS',
740 l_displayShipToAddress, 'N', null);
741
742 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
743 'IBU_CF_SR_SHIP_TO_ADDRESS',
744 l_displayShipToAddress, 'N', null);
745
746 END IF;
747
748 IF (l_displayShipToContact = 'Y') THEN
749 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
750 'IBU_CF_SR_SHIP_TO_CONTACT',
751 l_displayShipToContact, 'N', null);
752
753 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
754 'IBU_CF_SR_SHIP_TO_CONTACT',
755 l_displayShipToContact, 'N', null);
756
757 END IF;
758
759 IF (l_displayInstalledAtAddr = 'Y') THEN
760 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
761 'IBU_CF_SR_INSTALLED_AT_HDR',
762 'Y', 'N', null);
763
764 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
765 'IBU_CF_SR_INSTALLED_AT_HDR',
766 'Y', 'N', null);
767
768
769 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
770 'IBU_CF_SR_INSTALL_AT_ADDR',
771 l_displayInstalledAtAddr, 'N', null);
772
773 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
774 'IBU_CF_SR_INSTALL_AT_ADDR',
775 l_displayInstalledAtAddr, 'N', null);
776
777
778 END IF;
779
780 -- Now we take care of the case for the incident address
781 -- Note that we do not take care of the case where
782 -- display addr = N but mandatory addr = Y, because it
783 -- doesn't make sense
784 IF (l_displayIncidentAddr = 'Y' AND l_mandatoryIncidentAddr = 'Y') THEN
785 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
786 'IBU_CF_SR_INCIDENT_ADDRESS_HDR',
787 'Y', 'Y', null);
788 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
789 'IBU_CF_SR_ADDRESS',
790 'Y', 'Y', null);
791 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
792 'IBU_CF_SR_CITY',
793 'Y', 'Y', null);
794 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
795 'IBU_CF_SR_STATE',
796 'Y', 'Y', null);
797 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
798 'IBU_CF_SR_PROVINCE',
799 'Y', 'N', null);
800 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
801 'IBU_CF_SR_POSTAL_CODE',
802 'Y', 'N', null);
803 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
804 'IBU_CF_SR_COUNTRY',
805 'Y', 'Y', null);
806 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
807 'IBU_CF_SR_COUNTY',
808 'Y', 'N', null);
809
810 -- For the address region for update, the mandatory flag
811 -- can be ignored.
812 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
813 'IBU_CF_SR_INCIDENT_ADDRESS_HDR',
814 'Y', 'N', null);
815 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
816 'IBU_CF_SR_ADDRESS',
817 'Y', 'N', null);
818 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
819 'IBU_CF_SR_CITY',
820 'Y', 'N', null);
821 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
822 'IBU_CF_SR_STATE',
823 'Y', 'N', null);
824 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
825 'IBU_CF_SR_PROVINCE',
826 'Y', 'N', null);
827 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
828 'IBU_CF_SR_POSTAL_CODE',
829 'Y', 'N', null);
830 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
831 'IBU_CF_SR_COUNTRY',
832 'Y', 'N', null);
833 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
834 'IBU_CF_SR_COUNTY',
838 ELSIF (l_displayIncidentAddr = 'Y' AND l_mandatoryIncidentAddr = 'N') THEN
835 'Y', 'N', null);
836
837
839 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
840 'IBU_CF_SR_INCIDENT_ADDRESS_HDR',
841 'Y', 'N', null);
842 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
843 'IBU_CF_SR_ADDRESS',
844 'Y', 'N', null);
845 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
846 'IBU_CF_SR_CITY',
847 'Y', 'N', null);
848 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
849 'IBU_CF_SR_STATE',
850 'Y', 'N', null);
851 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
852 'IBU_CF_SR_PROVINCE',
853 'Y', 'N', null);
854 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
855 'IBU_CF_SR_POSTAL_CODE',
856 'Y', 'N', null);
857 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
858 'IBU_CF_SR_COUNTRY',
859 'Y', 'N', null);
860 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
861 'IBU_CF_SR_COUNTY',
862 'Y', 'N', null);
863
864 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
865 'IBU_CF_SR_INCIDENT_ADDRESS_HDR',
866 'Y', 'N', null);
867 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
868 'IBU_CF_SR_ADDRESS',
869 'Y', 'N', null);
870 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
871 'IBU_CF_SR_CITY',
872 'Y', 'N', null);
873 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
874 'IBU_CF_SR_STATE',
875 'Y', 'N', null);
876 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
877 'IBU_CF_SR_PROVINCE',
878 'Y', 'N', null);
879 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
880 'IBU_CF_SR_POSTAL_CODE',
881 'Y', 'N', null);
882 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
883 'IBU_CF_SR_COUNTRY',
884 'Y', 'N', null);
885 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
886 'IBU_CF_SR_COUNTY',
887 'Y', 'N', null);
888
889
890 ELSIF (l_displayIncidentAddr = 'N' AND l_mandatoryIncidentAddr = 'Y') THEN
891 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
892 'IBU_CF_SR_INCIDENT_ADDRESS_HDR',
893 'N', 'Y', null);
894 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
895 'IBU_CF_SR_ADDRESS',
896 'N', 'Y', null);
897 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
898 'IBU_CF_SR_CITY',
899 'N', 'Y', null);
900 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
901 'IBU_CF_SR_STATE',
902 'N', 'Y', null);
903 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
904 'IBU_CF_SR_PROVINCE',
905 'N', 'N', null);
906 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
907 'IBU_CF_SR_POSTAL_CODE',
908 'N', 'N', null);
909 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
910 'IBU_CF_SR_COUNTRY',
911 'N', 'Y', null);
912 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
913 'IBU_CF_SR_COUNTY',
914 'N', 'N', null);
915 -- We don't need to worry about this case for Update SR address regions, so
916 -- no need to do anything.
917 END IF;
918 l_examineAddressOption := FALSE;
919 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Resp', 'Done with Address Options');
920
921 ELSIF (l_profileOptionName = 'IBU_SR_CREATION_PRODUCT_OPTION' AND l_examineCreateProdOption) THEN
922 -- mkcyee 12/14/2004 - This profile also impacts the product region for
923 -- Search and Templates, so we need to clone that region as well.
924
925 l_newIdentifyProductRegionCode := 'IBU_CF_SR_30_R' || p_respId;
926 l_newTemplateProductRegionCode := 'IBU_CF_SR_35_R' || p_respId;
927
928 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_30_G',
929 672,
930 l_newIdentifyProductRegionCode,
931 672, FALSE);
932
933 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_35_G',
934 672,
935 l_newTemplateProductRegionCode,
936 672, FALSE);
937
938 IF (l_profileOptionValue = 'USE_ONLY_INSTALLBASE_PRODUCT') THEN
939 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProductRegionCode,
940 'IBU_CF_SR_ALL_PRODUCT_RG',
941 'N', 'N', null);
942
943 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newTemplateProductRegionCode,
944 'IBU_CF_SR_ALL_PRODUCT_RG',
945 'N', 'N', null);
946
947
948 ELSIF (l_profileOptionValue = 'USE_ONLY_INVENTORY_PRODUCT') THEN
949 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProductRegionCode,
950 'IBU_CF_SR_REG_PRODUCT_RG',
951 'N', 'N', null);
952
953 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newTemplateProductRegionCode,
954 'IBU_CF_SR_REG_PRODUCT_RG',
955 'N', 'N', null);
956
957 END IF;
958 l_newProductFilterRegionCode := 'IBU_CF_SR_420_R' || p_respId;
959
960 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_420_G',
961 672,
962 l_newProductFilterRegionCode,
963 672, FALSE);
967 'N', 'N', null);
964 IF (l_profileOptionValue = 'USE_ONLY_INSTALLBASE_PRODUCT') THEN
965 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newProductFilterRegionCode,
966 'IBU_CF_SR_ALL_PRODUCT_RG',
968
969 ELSIF (l_profileOptionValue = 'USE_ONLY_INVENTORY_PRODUCT') THEN
970 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newProductFilterRegionCode,
971 'IBU_CF_SR_REG_PRODUCT_RG',
972 'N', 'N', null);
973 END IF;
974 l_examineCreateProdOption := FALSE;
975 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Resp', 'Done with Creation Product Option');
976 ELSIF ((l_profileOptionName = 'IBU_A_SR_ATTACHMENT_OPTION' AND l_examineAttachmentOption) OR (l_profileOptionName = 'IBU_A_SR_PROB_CODE_MANDATORY' and l_examineProbCodeOption)) THEN
977 l_newIdentifyProblemRegionCode := 'IBU_CF_SR_40_R' || p_respId;
978
979 CS_CF_UPG_UTL_PKG.getAttachmentProbCodeValues(p_ProfileTable,
980 l_displayAttachment,
981 l_mandatoryProblemCode);
982
983 IF (l_displayAttachment = 'DONOTSHOW' OR l_displayAttachment = 'SHOWDURINGUPDATE' OR l_mandatoryProblemCode = 'Y') THEN
984 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_40_G',
985 672,
986 l_newIdentifyProblemRegionCode,
987 672, FALSE);
988 IF (l_displayAttachment = 'DONOTSHOW') THEN
989 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProblemRegionCode,
990 'IBU_CF_SR_ATTACHMENTS_RG',
991 'N', 'N', null);
992 ELSIF (l_displayAttachment = 'SHOWDURINGUPDATE') THEN
993 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProblemRegionCode,
994 'IBU_CF_SR_ATTACHMENTS_RG',
995 'N', 'N', null);
996 ELSIF(l_mandatoryProblemCode = 'Y') THEN
997 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProblemRegionCode,
998 'IBU_CF_SR_PROB_TYPE_CODE',
999 'Y', 'Y', null);
1000
1001 END IF;
1002 END IF;
1003 l_newDtlOverviewRegionCode := 'IBU_CF_SR_130_R' || p_respId;
1004 IF (l_displayAttachment = 'DONOTSHOW' OR l_displayAttachment = 'SHOWDURINGCREATE') THEN
1005 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_130_G',
1006 672,
1007 l_newDtlOverviewRegionCode,
1008 672, FALSE);
1009
1010 IF (l_displayAttachment = 'DONOTSHOW') THEN
1011 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlOverviewRegionCode,
1012 'IBU_CF_SR_ATTACHMENTS_RG',
1013 'N', 'N', null);
1014 ELSIF (l_displayAttachment = 'SHOWDURINGCREATE') THEN
1015 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlOverviewRegionCode,
1016 'IBU_CF_SR_ATTACHMENTS_RG',
1017 'N', 'N', null);
1018
1019 END IF;
1020 END IF; -- end if l_region_count for IBU_CF_SR_130_G
1021 l_examineAttachmentOption := FALSE;
1022 l_examineProbCodeOption := FALSE;
1023 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Resp', 'Done with Attachment Option and Mandatory Problem Code');
1024 ELSIF (l_profileOptionName = 'IBU_SR_ENABLE_TEMPLATE' AND l_examineTemplateOption) THEN
1025 l_newProblemDetailsRegionCode := 'IBU_CF_SR_60_R' || p_respId;
1026
1027 IF (l_profileOptionName = 'N') THEN
1028 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_60_G',
1029 672,
1030 l_newProblemDetailsRegionCode,
1031 672, FALSE);
1032
1033 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newProblemDetailsRegionCode,
1034 'IBU_CF_SR_PROB_DETAILS',
1035 'N', 'N', null);
1036 END IF;
1037 l_examineTemplateOption := FALSE;
1038 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Resp', 'Done with Template Option');
1039
1040 ELSIF (l_profileOptionName = 'IBU_A_SR_ENABLE_INTERACTION_LOGGING' AND l_examineLoggingOption) THEN
1041 l_newDtlProgOptionsRegionCode := 'IBU_CF_SR_110_R' || p_respId;
1042
1043 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_110_G',
1044 672,
1045 l_newDtlProgOptionsRegionCode,
1046 672, FALSE);
1047 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlProgOptionsRegionCode,
1048 'IBU_CF_SR_DTL_PROGRESS_INTRCT',
1049 'Y', 'N', null);
1050 l_examineLoggingOption := FALSE;
1051 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Resp', 'Done with Interaction Logging Option');
1052 ELSIF (l_profileOptionName = 'IBU_SR_TASK_DISPLAY' AND l_examineTaskOption) THEN
1053 l_newDtlResolnRegionCode := 'IBU_CF_SR_150_R' || p_respId;
1054 IF (l_profileOptionValue = 'Y') THEN
1055 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_150_G',
1056 672,
1057 l_newDtlResolnRegionCode,
1058 672, FALSE);
1059 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlResolnRegionCode,
1060 'IBU_CF_SR_DTL_ACTS_RG',
1061 'Y', 'N', null);
1062 END IF;
1063 l_examineTaskOption := FALSE;
1064 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Resp', 'Done with Task Display Option');
1065 END IF; -- end profile checks
1066 l_index := l_index + 1;
1067 END LOOP; -- end while loop
1068
1072 OPEN does_region_already_exists(l_newPrimaryContactRegionCode, 672);
1069 -- Check whether a region has already been cloned for
1070 -- Primary Contact sub region
1071 l_newPrimaryContactRegionCode := 'IBU_CF_SR_10_R' || p_respId;
1073 FETCH does_region_already_exists INTO l_region_count;
1074 CLOSE does_region_already_exists;
1075
1076 IF (l_region_count > 0) THEN
1077 l_newPrimaryContactExists := TRUE;
1078 END IF;
1079
1080 -- Check whether a region has already been cloned for
1081 -- Reg Product sub region
1082 l_newRegProductRegionCode := 'IBU_CF_SR_80_R' || p_respId;
1083 OPEN does_region_already_exists(l_newRegProductRegionCode, 672);
1084 FETCH does_region_already_exists INTO l_region_count;
1085 CLOSE does_region_already_exists;
1086
1087 IF (l_region_count > 0) THEN
1088 l_newRegProductExists := TRUE;
1089 END IF;
1090
1091 -- Check whether a region has already been cloned for
1092 -- All Product sub region
1093 l_newAllProductRegionCode := 'IBU_CF_SR_90_R' || p_respId;
1094 OPEN does_region_already_exists(l_newAllProductRegionCode, 672);
1095 FETCH does_region_already_exists INTO l_region_count;
1096 CLOSE does_region_already_exists;
1097
1098 IF (l_region_count > 0) THEN
1099 l_newAllProductExists := TRUE;
1100 END IF;
1101
1102 -- Check whether a region has already been cloned for
1103 -- Identify Product sub region
1104 l_newIdentifyProductRegionCode := 'IBU_CF_SR_30_R' || p_respId;
1105 OPEN does_region_already_exists(l_newIdentifyProductRegionCode, 672);
1106 FETCH does_region_already_exists INTO l_region_count;
1107 CLOSE does_region_already_exists;
1108
1109 IF (l_region_count > 0) THEN
1110 l_newIdentifyProductExists := TRUE;
1111 select node_display_flag INTO
1112 l_displayRegProducts
1113 from ak_region_items
1114 where region_code = l_newIdentifyProductRegionCode
1115 and attribute_code = 'IBU_CF_SR_REG_PRODUCT_RG'
1116 and region_application_id = 672
1117 and attribute_application_id = 672;
1118
1119 select node_display_flag INTO
1120 l_displayAllProducts
1121 from ak_region_items
1122 where region_code = l_newIdentifyProductRegionCode
1123 and attribute_code = 'IBU_CF_SR_ALL_PRODUCT_RG'
1124 and region_application_id = 672
1125 and attribute_application_id = 672;
1126 END IF;
1127
1128 IF (l_newIdentifyProductExists) THEN
1129 l_newTemplateProductExists := TRUE;
1130 END IF;
1131
1132 -- Check whether a region has been cloned for IBU_CF_SR_VW_PRODUCT_FILTER
1133 l_newProductFilterRegionCode := 'IBU_CF_SR_420_R' || p_respId;
1134 OPEN does_region_already_exists(l_newProductFilterRegionCode, 672);
1135 FETCH does_region_already_exists INTO l_region_count;
1136 CLOSE does_region_already_exists;
1137
1138 IF (l_region_count > 0) THEN
1139 l_newProductFilterExists := TRUE;
1140 END IF;
1141
1142 -- Check whether a region has been cloned for addresses
1143 l_newAddressRegionCode := 'IBU_CF_SR_20_R' || p_respId;
1144 OPEN does_region_already_exists(l_newAddressRegionCode, 672);
1145 FETCH does_region_already_exists INTO l_region_count;
1146 CLOSE does_region_already_exists;
1147
1148 IF (l_region_count > 0) THEN
1149 l_newAddressExists := TRUE;
1150 END IF;
1151
1152 l_newUpdateAddressRegionCode := 'IBU_CF_SR_25_R' || p_respId;
1153 OPEN does_region_already_exists(l_newUpdateAddressRegionCode, 672);
1154 FETCH does_region_already_exists INTO l_region_count;
1155 CLOSE does_region_already_exists;
1156
1157 IF (l_region_count > 0) THEN
1158 l_newUpdateAddressExists := TRUE;
1159 END IF;
1160
1161
1162
1163
1164
1165 -- Check cloned subregions for Create Service Request flows
1166
1167 -- Check whether a region has already been
1168 -- cloned for Identify Problem
1169 l_newIdentifyProblemRegionCode := 'IBU_CF_SR_40_R' || p_respId;
1170 OPEN does_region_already_exists(l_newIdentifyProblemRegionCode, 672);
1171 FETCH does_region_already_exists INTO l_region_count;
1172 CLOSE does_region_already_exists;
1173
1174 IF (l_region_count > 0) THEN
1175 l_newIdentifyProblemExists := TRUE;
1176
1177 select node_display_flag INTO
1178 l_displayAttachment
1179 from ak_region_items
1180 where region_code = l_newIdentifyProblemRegionCode
1181 and attribute_code = 'IBU_CF_SR_ATTACHMENTS_RG'
1182 and region_application_id = 672
1183 and attribute_application_id = 672;
1184
1185 ELSIF (l_newPrimaryContactExists OR l_newIdentifyProductExists OR l_newAddressExists) THEN
1186 -- mkcyee 12/07/04 - new Address region has been added to Identify
1187 -- Problem page, so we need to clone this region is Address region
1188 -- has been cloned
1189
1190 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_40_G',
1191 672,
1192 l_newIdentifyProblemRegionCode,
1193 672, FALSE);
1194 l_newIdentifyProblemExists := TRUE;
1195 END IF;
1196
1197 -- Check whether a region has already been cloned for
1198 -- Problem Details
1199 l_newProblemDetailsRegionCode := 'IBU_CF_SR_60_R' || p_respId;
1200 OPEN does_region_already_exists(l_newProblemDetailsRegionCode, 672);
1201 FETCH does_region_already_exists INTO l_region_count;
1202 CLOSE does_region_already_exists;
1203
1204 IF (l_region_count > 0) THEN
1205 l_newProblemDetailsExists := TRUE;
1206
1207 select node_display_flag INTO
1208 l_enableTemplate
1209 from ak_region_items
1210 where region_code = l_newProblemDetailsRegionCode
1211 and attribute_code = 'IBU_CF_SR_PROB_DETAILS'
1212 and region_application_id = 672
1213 and attribute_application_id = 672;
1217 -- has been cloned
1214 ELSIF (l_newPrimaryContactExists OR l_newAddressExists) THEN
1215 -- mkcyee 12/07/04 - new Address region has been added to Problem
1216 -- details page, so we need to clone this region is Address region
1218 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_60_G',
1219 672,
1220 l_newProblemDetailsRegionCode,
1221 672, FALSE);
1222 l_newProblemDetailsExists := TRUE;
1223 END IF;
1224
1225
1226 -- Check whether a region has already been cloned for
1227 -- Update Overview sub region
1228 l_newDtlOverviewRegionCode := 'IBU_CF_SR_130_R' || p_respId;
1229 OPEN does_region_already_exists(l_newDtlOverviewRegionCode, 672);
1230 FETCH does_region_already_exists INTO l_region_count;
1231 CLOSE does_region_already_exists;
1232
1233 IF (l_region_count > 0) THEN
1234 l_newDtlOverviewExists := TRUE;
1235 END IF;
1236
1237
1238 -- Check whether a region has been cloned for Contact Info
1239 l_newContactInfoRegionCode := 'IBU_CF_SR_70_R' || p_respId;
1240 IF (l_newPrimaryContactExists OR l_newAddressExists) THEN
1241 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_70_G',
1242 672,
1243 l_newContactInfoRegionCode,
1244 672, FALSE);
1245 l_newContactInfoExists := TRUE;
1246 END IF;
1247
1248 l_newReviewRegionCode := 'IBU_CF_SR_50_R' || p_respId;
1249
1250 IF (l_newAddressExists OR l_enableTemplate = 'N' OR
1251 l_displayAttachment='N') THEN
1252 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_50_G',
1253 672,
1254 l_newReviewRegionCode,
1255 672, FALSE);
1256
1257 l_newReviewExists := TRUE;
1258 END IF;
1259
1260 -- Check whether a region has been cloned for progress options
1261 l_newDtlProgOptionsRegionCode := 'IBU_CF_SR_110_R' || p_respId;
1262 OPEN does_region_already_exists(l_newDtlProgOptionsRegionCode, 672);
1263 FETCH does_region_already_exists INTO l_region_count;
1264 CLOSE does_region_already_exists;
1265
1266 IF (l_region_count > 0) THEN
1267 l_newDtlProgressOptionsExists := TRUE;
1268 END IF;
1269
1270 -- Check whether a region has been cloned for IBU_CF_SR_DTL_RESOLN
1271 l_newDtlResolnRegionCode := 'IBU_CF_SR_150_R' || p_respId;
1272 OPEN does_region_already_exists(l_newDtlResolnRegionCode, 672);
1273 FETCH does_region_already_exists INTO l_region_count;
1274 CLOSE does_region_already_exists;
1275
1276 IF (l_region_count > 0) THEN
1277 l_newDtlResolnExists := TRUE;
1278 select node_display_flag INTO
1279 l_displayTasks
1280 from ak_region_items
1281 where region_code = l_newDtlResolnRegionCode
1282 and attribute_code = 'IBU_CF_SR_DTL_ACTS_RG'
1283 and region_application_id = 672
1284 and attribute_application_id = 672;
1285
1286 END IF;
1287
1288 -- If regions that were created impact other regions,
1289 -- make sure to set them to the proper region code.
1290 -- For some cases, we may have to clone new regions
1291
1292 IF (l_newAllProductExists AND l_newRegProductExists) THEN
1293 IF NOT(l_newIdentifyProductExists) THEN
1294 -- need to clone the IBU_CF_SR_IDENTIFY_PRODUCT region
1295 l_newIdentifyProductRegionCode := 'IBU_CF_SR_30_R' || p_respId;
1296 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_30_G',
1297 672,
1298 l_newIdentifyProductRegionCode,
1299 672, FALSE);
1300 l_newIdentifyProductExists := TRUE;
1301 IF (l_displayRegProducts = '') THEN
1302 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProductRegionCode,
1303 'IBU_CF_SR_REG_PRODUCT_RG',
1304 'Y', 'N', l_newRegProductRegionCode);
1305 ELSE
1306 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProductRegionCode,
1307 'IBU_CF_SR_REG_PRODUCT_RG',
1308 l_displayRegProducts, 'N', l_newRegProductRegionCode);
1309 END IF;
1310 IF (l_displayAllProducts = '') THEN
1311 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProductRegionCode,
1312 'IBU_CF_SR_ALL_PRODUCT_RG',
1313 'Y', 'N', l_newAllProductRegionCode);
1314 ELSE
1315 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProductRegionCode,
1316 'IBU_CF_SR_ALL_PRODUCT_RG',
1317 l_displayAllProducts, 'N', l_newAllProductRegionCode);
1318 END IF;
1319 END IF;
1320 END IF;
1321
1322 IF (l_displayRegProducts = 'N' OR l_displayAllProducts = 'N') THEN
1323 IF NOT(l_newTemplateProductExists) THEN
1324 -- need to clone the IBU_CF_SR_IDENTIFY_PRODUCT region for Templates
1325 l_newTemplateProductRegionCode := 'IBU_CF_SR_35_R' || p_respId;
1326 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_35_G',
1327 672,
1328 l_newTemplateProductRegionCode,
1329 672, FALSE);
1330 l_newTemplateProductExists := TRUE;
1331 IF (l_displayRegProducts = 'N') THEN
1332 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newTemplateProductRegionCode,
1333 'IBU_CF_SR_REG_PRODUCT_RG',
1334 'N', 'N', 'IBU_CF_SR_REG_PRODUCT');
1335 END IF;
1336 IF (l_displayAllProducts = 'N') THEN
1337 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newTemplateProductRegionCode,
1338 'IBU_CF_SR_ALL_PRODUCT_RG',
1339 'N', 'N', 'IBU_CF_SR_ALL_PRODUCT');
1340 END IF;
1341 END IF;
1342 IF NOT(l_newProductFilterExists) THEN
1343 -- need to clone the IBU_CF_SR_VW_PRODUCT_FILTER region
1347 l_newProductFilterRegionCode,
1344 l_newProductFilterRegionCode := 'IBU_CF_SR_420_R' || p_respId;
1345 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_420_G',
1346 672,
1348 672, FALSE);
1349 l_newProductFilterExists := TRUE;
1350 IF (l_displayRegProducts = 'N') THEN
1351 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newProductFilterRegionCode,
1352 'IBU_CF_SR_REG_PRODUCT_RG',
1353 'N', 'N', 'IBU_CF_SR_REG_PRODUCT');
1354 END IF;
1355 IF (l_displayAllProducts = 'N') THEN
1356 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newProductFilterRegionCode,
1357 'IBU_CF_SR_ALL_PRODUCT_RG',
1358 'N', 'N', 'IBU_CF_SR_ALL_PRODUCT');
1359 END IF;
1360 END IF;
1361 END IF;
1362
1363 IF (l_newIdentifyProblemExists) THEN
1364 IF (l_newPrimaryContactExists) THEN
1365 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProblemRegionCode,
1366 'IBU_CF_SR_PRIMARY_CONTACT_RG',
1367 'Y', 'N', l_newPrimaryContactRegionCode);
1368 END IF;
1369 IF (l_newIdentifyProductExists) THEN
1370 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProblemRegionCode,
1371 'IBU_CF_SR_IDENTIFY_PRODUCT_RG',
1372 'Y', 'N', l_newIdentifyProductRegionCode);
1373
1374 END IF;
1375 IF (l_newAddressExists) THEN
1376 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProblemRegionCode,
1377 'IBU_CF_SR_ADDRESS_RG',
1378 'N', 'N', l_newAddressRegionCode);
1379 END IF;
1380 END IF;
1381
1382 IF (l_newProblemDetailsExists) THEN
1383 IF (l_newPrimaryContactExists) THEN
1384 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newProblemDetailsRegionCode,
1385 'IBU_CF_SR_PRIMARY_CONTACT_RG',
1386 'Y', 'N', l_newPrimaryContactRegionCode);
1387 END IF;
1388 IF (l_displayAttachment = 'N') THEN
1389 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProblemRegionCode,
1390 'IBU_CF_SR_ATTACHMENTS_RG',
1391 'N', 'N', null);
1392 END IF;
1393
1394 -- mkcyee 12/07/2004 - now the address regions have been added to
1395 -- Identify Problem and Problem Details pages
1396 IF (l_newAddressExists) THEN
1397 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newProblemDetailsRegionCode,
1398 'IBU_CF_SR_ADDRESS_RG',
1399 'N', 'N', l_newAddressRegionCode);
1400 END IF;
1401 END IF;
1402
1403
1404 IF (l_newReviewExists) THEN
1405 IF (l_displayAttachment = 'N') THEN
1406 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newReviewRegionCode,
1407 'IBU_CF_SR_ATTACHMENTS_RG',
1408 'N', 'N', null);
1409 END IF;
1410 IF (l_newAddressExists) THEN
1411 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newReviewRegionCode,
1412 'IBU_CF_SR_ADDRESS_RG',
1413 'Y', 'N', l_newAddressRegionCode);
1414 END IF;
1415 IF (l_enableTemplate = 'N') THEN
1416 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newReviewRegionCode,
1417 'IBU_CF_SR_PROB_DETAILS',
1418 'N', 'N', null);
1419 END IF;
1420 END IF;
1421
1422 IF (l_newContactInfoExists) THEN
1423 IF (l_newAddressExists) THEN
1424 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newContactInfoRegionCode,
1425 'IBU_CF_SR_ADDRESS_RG',
1426 'Y', 'N', l_newAddressRegionCode);
1427 END IF;
1428 IF (l_newPrimaryContactExists) THEN
1429 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newContactInfoRegionCode,
1430 'IBU_CF_SR_PRIMARY_CONTACT_RG',
1431 'Y', 'N', l_newPrimaryContactRegionCode);
1432 END IF;
1433 END IF;
1434 --For update service request flows, clone required regions
1435
1436 IF (l_newUpdateAddressExists) THEN
1437 -- need to clone the IBU_CF_SR_DTL_CONTACT region
1438 l_newDtlContactAddrRegionCode := 'IBU_CF_SR_210_R' || p_respId;
1439 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_210_G',
1440 672,
1441 l_newDtlContactAddrRegionCode,
1442 672, FALSE);
1443 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlContactAddrRegionCode,
1444 'IBU_CF_SR_ADDRESS_RG',
1445 'Y', 'N', l_newUpdateAddressRegionCode);
1446 l_newDtlContactAddrExists := TRUE;
1447 END IF;
1448
1449 IF (l_newDtlProgressOptionsExists) THEN
1450 -- We must clone IBU_CF_SR_DTL_PROGRESS
1451 l_newDtlProgressRegionCode := 'IBU_CF_SR_120_R' || p_respId;
1452 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_120_G',
1453 672,
1454 l_newDtlProgressRegionCode,
1455 672, FALSE);
1456 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlProgressRegionCode,
1457 'IBU_CF_SR_DTL_PROGRESS_OPT_RG',
1458 'Y', 'N', l_newDtlProgOptionsRegionCode);
1459 l_newDtlProgressExists := TRUE;
1460 END IF;
1461
1462 IF (l_newDtlProgressExists OR l_newDtlResolnExists) THEN
1463 -- if any of these regions are cloned, then we
1464 -- must clone IBU_CF_SR_DTL_OVERVIEW
1465 l_newDtlOverviewRegionCode := 'IBU_CF_SR_130_R' || p_respId;
1466
1467 IF NOT(l_newDtlOverviewExists) THEN
1468 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_130_G',
1469 672,
1470 l_newDtlOverviewRegionCode,
1471 672, FALSE);
1472 END IF;
1473 IF (l_newUpdateAddressExists) THEN
1474 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlOverviewRegionCode,
1475 'IBU_CF_SR_ADDRESS_RG',
1476 'N', 'N', l_newUpdateAddressRegionCode);
1477 END IF;
1481 'Y', 'N', l_newDtlProgressRegionCode);
1478 IF (l_newDtlProgressExists) THEN
1479 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlOverviewRegionCode,
1480 'IBU_CF_SR_DTL_PROGRESS_RG',
1482 END IF;
1483 IF (l_newDtlResolnExists AND l_displayTasks = 'Y') THEN
1484 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlOverviewRegionCode,
1485 'IBU_CF_SR_DTL_ACTS_RG',
1486 'Y', 'N', null);
1487 END IF;
1488 l_newDtlOverviewExists := TRUE;
1489 END IF;
1490
1491
1492 IF (l_newDtlOverviewExists OR l_newDtlResolnExists) THEN
1493 -- then we must clone IBU_CF_SR_DTL_TABS
1494 l_newDtlTabsRegionCode := 'IBU_CF_SR_160_R' || p_respId;
1495 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_160_G',
1496 672,
1497 l_newDtlTabsRegionCode,
1498 672, FALSE);
1499 IF (l_newDtlOverviewExists) THEN
1500 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlTabsRegionCode,
1501 'IBU_CF_SR_DTL_OVERVIEW_TAB_RG',
1502 'Y', 'N', l_newDtlOverviewRegionCode);
1503 END IF;
1504 IF (l_newDtlResolnExists) THEN
1505 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlTabsRegionCode,
1506 'IBU_CF_SR_DTL_RESOLN_TAB_RG',
1507 'N', 'N', l_newDtlResolnRegionCode);
1508 END IF;
1509 l_newDtlTabsExists := TRUE;
1510 END IF;
1511
1512 IF (l_newDtlTabsExists) THEN
1513 -- then we must clone IBU_CF_SR_DETAILS
1514 l_newDtlDetailsRegionCode := 'IBU_CF_SR_190_R' || p_respId;
1515 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_190_G',
1516 672,
1517 l_newDtlDetailsRegionCode,
1518 672, FALSE);
1519 IF (l_newDtlDetailsExists) THEN
1520 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlDetailsRegionCode,
1521 'IBU_CF_SR_DTL_TABS_RG',
1522 'Y', 'N', l_newDtlTabsRegionCode);
1523 END IF;
1524 l_newDtlDetailsExists := TRUE;
1525 END IF;
1526
1527 -- Now check for the Templates
1528
1529 -- mkcyee 12/14/2004 - use l_newTemplateProductExists
1530 -- because Templates and Search now have a separate product section
1531
1532 IF (l_newTemplateProductExists) THEN
1533 l_newCreateTemplateRegionCode := 'IBU_CF_SR_310_R' || p_respId;
1534 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_310_G',
1535 672,
1536 l_newCreateTemplateRegionCode,
1537 672, FALSE);
1538 -- mkcyee 12/16/2004 - The template region now points to its own copy of
1539 -- primary contact region, so no need to clone create/update template region
1540 -- because of this.
1541 --IF (l_newPrimaryContactExists) THEN
1542 -- CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newCreateTemplateRegionCode,
1543 -- 'IBU_CF_SR_PRIMARY_CONTACT_RG',
1544 -- 'Y', 'N', l_newPrimaryContactRegionCode);
1545 --END IF;
1546 --IF (l_newTemplateProductExists) THEN
1547 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newCreateTemplateRegionCode,
1548 'IBU_CF_SR_IDENTIFY_PRODUCT_RG',
1549 'Y', 'N', l_newTemplateProductRegionCode);
1550 --END IF;
1551 l_newCreateTemplateExists := TRUE;
1552 l_newUpdateTemplateRegionCode := 'IBU_CF_SR_320_R' || p_respId;
1553
1554 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_320_G',
1555 672,
1556 l_newUpdateTemplateRegionCode,
1557 672, FALSE);
1558 --IF (l_newPrimaryContactExists) THEN
1559 -- CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateTemplateRegionCode,
1560 -- 'IBU_CF_SR_PRIMARY_CONTACT_RG',
1561 -- 'Y', 'N', l_newPrimaryContactRegionCode);
1562 --END IF;
1563 --IF (l_newTemplateProductExists) THEN
1564 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateTemplateRegionCode,
1565 'IBU_CF_SR_IDENTIFY_PRODUCT_RG',
1566 'Y', 'N', l_newTemplateProductRegionCode);
1567 --END IF;
1568 l_newUpdateTemplateExists := TRUE;
1569 END IF;
1570
1571 IF (l_newProductFilterExists) THEN
1572 -- need to clone IBU_CF_SR_VW_FILTER
1573 l_newFilterRegionCode := 'IBU_CF_SR_410_R' || p_respId;
1574
1575 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_410_G',
1576 672,
1577 l_newFilterRegionCode,
1578 672, FALSE);
1579 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newFilterRegionCode,
1580 'IBU_CF_SR_VW_PRODUCT_FILTER_RG',
1581 'Y', 'N', l_newProductFilterRegionCode);
1582
1583 l_newFilterExists := TRUE;
1584 END IF;
1585
1586 IF (l_newFilterExists) THEN
1587 -- need to clone IBU_CF_SR_VW_SEARCH, IBU_CF_SR_VW_CREATE, IBU_CF_SR_VW_UPDATE
1588 l_newSearchViewRegionCode := 'IBU_CF_SR_450_R' || p_respId;
1589
1590 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_450_G',
1591 672,
1592 l_newSearchViewRegionCode,
1593 672, FALSE);
1594 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newSearchViewRegionCode,
1595 'IBU_CF_SR_VW_FILTER_RG',
1596 'Y', 'N', l_newFilterRegionCode);
1597
1598 l_newSearchViewExists := TRUE;
1599
1600 l_newCreateViewRegionCode := 'IBU_CF_SR_430_R' || p_respId;
1601
1602 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_430_G',
1603 672,
1604 l_newCreateViewRegionCode,
1605 672, FALSE);
1609
1606 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newCreateViewRegionCode,
1607 'IBU_CF_SR_VW_FILTER_RG',
1608 'Y', 'N', l_newFilterRegionCode);
1610 l_newCreateViewExists := TRUE;
1611
1612 l_newUpdateViewRegionCode := 'IBU_CF_SR_440_R' || p_respId;
1613
1614 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_440_G',
1615 672,
1616 l_newUpdateViewRegionCode,
1617 672, FALSE);
1618 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateViewRegionCode,
1619 'IBU_CF_SR_VW_FILTER_RG',
1620 'Y', 'N', l_newFilterRegionCode);
1621
1622 l_newUpdateViewExists := TRUE;
1623 END IF;
1624
1625 -- Now enter the rows in the cs_cf_source_cxt_targets table
1626 -- for this responsibility
1627
1628 IF (l_newIdentifyProblemExists ) THEN
1629 CS_CF_UPG_UTL_PKG.Insert_New_Target(
1630 'IBU_SR_CR_IDENTIFY_PROBLEM',
1631 'RESP',
1632 l_respId,
1633 l_respApplId,
1634 NULL,
1635 NULL,
1636 l_newIdentifyProblemRegionCode,
1637 '672');
1638 END IF;
1639
1640 IF (l_newReviewExists) THEN
1641 CS_CF_UPG_UTL_PKG.Insert_New_Target(
1642 'IBU_SR_CR_REVIEW',
1643 'RESP',
1644 l_respId,
1645 l_respApplId,
1646 NULL,
1647 NULL,
1648 l_newReviewRegionCode,
1649 '672');
1650 END IF;
1651
1652 IF (l_newProblemDetailsExists) THEN
1653 CS_CF_UPG_UTL_PKG.Insert_New_Target(
1654 'IBU_SR_CR_PROBLEM_DETAILS',
1655 'RESP',
1656 l_respId,
1657 l_respApplId,
1658 NULL,
1659 NULL,
1660 l_newProblemDetailsRegionCode,
1661 '672');
1662 END IF;
1663
1664
1665 IF (l_newContactInfoExists) THEN
1666 CS_CF_UPG_UTL_PKG.Insert_New_Target(
1667 'IBU_SR_CR_CONTACT_INFORMATION',
1668 'RESP',
1669 l_respId,
1670 l_respApplId,
1671 NULL,
1672 NULL,
1673 l_newContactInfoRegionCode,
1674 '672');
1675 END IF;
1676
1677 IF (l_newDtlDetailsExists) THEN
1678 CS_CF_UPG_UTL_PKG.Insert_New_Target(
1679 'IBU_SR_DETAILS',
1680 'RESP',
1681 l_respId,
1682 l_respApplId,
1683 NULL,
1684 NULL,
1685 l_newDtlDetailsRegionCode,
1686 '672');
1687 END IF;
1688
1689 IF (l_newCreateTemplateExists) THEN
1690 CS_CF_UPG_UTL_PKG.Insert_New_Target(
1691 'IBU_SR_TEMP_CREATE',
1692 'RESP',
1693 l_respId,
1694 l_respApplId,
1695 NULL,
1696 NULL,
1697 l_newCreateTemplateRegionCode,
1698 '672');
1699 END IF;
1700
1701 IF (l_newUpdateTemplateExists) THEN
1702 CS_CF_UPG_UTL_PKG.Insert_New_Target(
1703 'IBU_SR_TEMP_UPDATE',
1704 'RESP',
1705 l_respId,
1706 l_respApplId,
1707 NULL,
1708 NULL,
1709 l_newUpdateTemplateRegionCode,
1710 '672');
1711 END IF;
1712
1713 IF (l_newSearchViewExists) THEN
1714 CS_CF_UPG_UTL_PKG.Insert_New_Target(
1715 'IBU_SR_VIEW_SUMMARY',
1716 'RESP',
1717 l_respId,
1718 l_respApplId,
1719 NULL,
1720 NULL,
1721 l_newSearchViewRegionCode,
1722 '672');
1723 END IF;
1724
1725 IF (l_newCreateViewExists) THEN
1726 CS_CF_UPG_UTL_PKG.Insert_New_Target(
1727 'IBU_SR_VIEW_CREATE',
1728 'RESP',
1729 l_respId,
1730 l_respApplId,
1731 NULL,
1732 NULL,
1733 l_newCreateViewRegionCode,
1734 '672');
1735 END IF;
1736
1737 IF (l_newUpdateViewExists) THEN
1738 CS_CF_UPG_UTL_PKG.Insert_New_Target(
1739 'IBU_SR_VIEW_UPDATE',
1740 'RESP',
1741 l_respId,
1742 l_respApplId,
1743 NULL,
1744 NULL,
1745 l_newUpdateViewRegionCode,
1746 '672');
1747 END IF;
1748
1749 END Clone_Regions_For_Resp;
1750
1751 /*
1752 * Procedure to performing upgrades for application level
1753 * For each application
1754 */
1755 PROCEDURE Do_Region_Upgrades_For_Appl(p_applTable IN CS_CF_UPG_UTL_PKG.ApplTable)
1756 IS
1757
1758 -- this picks up all the configuration profiles
1759 -- that affects region configuration for
1760 -- the application and the higher site level
1761 CURSOR get_profiles_for_appl (applId NUMBER)
1762 IS
1763 SELECT a.profile_option_name,
1764 b.level_value,
1765 b.level_value_application_id,
1766 b.profile_option_value,
1767 1 "PRIORITY"
1768 FROM fnd_profile_option_values b, fnd_profile_options a
1769 WHERE b.level_id = 10002
1770 AND b.level_value = applId
1771 AND a.profile_option_id = b.profile_option_id
1772 AND a.application_id = 672
1773 AND a.profile_option_name in ('IBU_A_SR_ACCOUNT_OPTION',
1774 'IBU_A_SR_PROB_CODE_MANDATORY',
1775 'IBU_SR_CREATION_PRODUCT_OPTION',
1776 'IBU_SR_ADDR_DISPLAY',
1777 'IBU_SR_ADDR_MANDATORY',
1778 'IBU_A_SR_BILLTO_ADDRESS_OPTION',
1779 'IBU_A_SR_BILLTO_CONTACT_OPTION',
1780 'IBU_A_SR_SHIPTO_ADDRESS_OPTION',
1781 'IBU_A_SR_SHIPTO_CONTACT_OPTION',
1782 'IBU_A_SR_INSTALLEDAT_ADDRESS_OPTION',
1783 'IBU_A_SR_ATTACHMENT_OPTION',
1787 'IBU_A_SR_PRODUCT_SELECTION_OPTION')
1784 'IBU_SR_TASK_DISPLAY',
1785 'IBU_A_SR_ENABLE_INTERACTION_LOGGING',
1786 'IBU_SR_ENABLE_TEMPLATE',
1788 UNION
1789 SELECT a.profile_option_name,
1790 b.level_value,
1791 b.level_value_application_id,
1792 b.profile_option_value,
1793 2 "PRIORITY"
1794 FROM fnd_profile_option_values b, fnd_profile_options a
1795 WHERE b.level_id = 10001
1796 AND a.profile_option_id = b.profile_option_id
1797 AND a.application_id = 672
1798 AND a.profile_option_name in ('IBU_A_SR_ACCOUNT_OPTION',
1799 'IBU_A_SR_PROB_CODE_MANDATORY',
1800 'IBU_SR_CREATION_PRODUCT_OPTION',
1801 'IBU_SR_ADDR_DISPLAY',
1802 'IBU_SR_ADDR_MANDATORY',
1803 'IBU_A_SR_BILLTO_ADDRESS_OPTION',
1804 'IBU_A_SR_BILLTO_CONTACT_OPTION',
1805 'IBU_A_SR_SHIPTO_ADDRESS_OPTION',
1806 'IBU_A_SR_SHIPTO_CONTACT_OPTION',
1807 'IBU_A_SR_INSTALLEDAT_ADDRESS_OPTION',
1808 'IBU_A_SR_ATTACHMENT_OPTION',
1809 'IBU_SR_TASK_DISPLAY',
1810 'IBU_A_SR_ENABLE_INTERACTION_LOGGING',
1811 'IBU_SR_ENABLE_TEMPLATE',
1812 'IBU_A_SR_PRODUCT_SELECTION_OPTION')
1813 ORDER BY PROFILE_OPTION_NAME, PRIORITY;
1814
1815 l_count NUMBER := p_applTable.COUNT;
1816 l_index NUMBER := 0;
1817 l_index2 NUMBER := 0;
1818 l_profile_option_name FND_PROFILE_OPTIONS.profile_option_name%TYPE;
1819 l_level_value FND_PROFILE_OPTION_VALUES.level_value%TYPE;
1820 l_level_value_application_id FND_PROFILE_OPTION_VALUES.level_value_application_id%TYPE;
1821 l_profile_option_value FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE;
1822 l_priority NUMBER := 0;
1823
1824 l_ProfileTable CS_CF_UPG_UTL_PKG.ProfileTable;
1825
1826 BEGIN
1827
1828
1829 WHILE (l_index < l_count) LOOP
1830 -- Get the list of profile names and their values
1831 -- and store it in a table
1832 -- This table may contain duplicate entries for
1833 -- each profile because we pick up the values
1834 -- for the application and site level also
1835 OPEN get_profiles_for_appl(p_applTable(l_index));
1836 LOOP
1837 -- Retrieve information into the local variables
1838 FETCH get_profiles_for_appl INTO l_profile_option_name,
1839 l_level_value,
1840 l_level_value_application_id,
1841 l_profile_option_value,
1842 l_priority;
1843
1844 l_ProfileTable(l_index2).profileOptionName := l_profile_option_name;
1845 l_ProfileTable(l_index2).profileOptionValue := l_profile_option_value;
1846
1847 l_index2 := l_index2 + 1;
1848
1849 EXIT WHEN get_profiles_for_appl%NOTFOUND;
1850
1851 END LOOP;
1852 CLOSE get_profiles_for_appl;
1853
1854 -- Now we have a table of profiles
1855 -- Determine which regions need to be cloned for this resp
1856
1857 IF (CS_CF_UPG_UTL_PKG.Regions_Not_Already_Cloned('A' || p_ApplTable(l_index))) THEN
1858 Clone_Regions_For_Appl(l_ProfileTable, p_ApplTable(l_index));
1859 commit;
1860 END IF;
1861
1862 -- now clean up the table so it can be reused
1863 l_ProfileTable.DELETE;
1864 l_index2 := 0;
1865
1866 l_index := l_index + 1;
1867 END LOOP; -- ends while loop
1868
1869 END Do_Region_Upgrades_For_Appl;
1870
1871 /*
1872 * Procedure to performing upgrades for site level
1873 */
1874 PROCEDURE Do_Region_Upgrades_For_Global(p_siteProfilesTable IN CS_CF_UPG_UTL_PKG.ProfileTable)
1875 IS
1876
1877 BEGIN
1878
1879 -- mkcyee 12/13/2004 - Global regions are a special case. Because ldt file
1880 -- may overwrite the cust target values, we must run the procedure to
1881 -- make sure we place back the cust target values even if the regions are already cloned.
1882
1883 -- IF (CS_CF_UPG_UTL_PKG.Regions_Not_Already_cloned('GC')) THEN
1884 Clone_Regions_For_Global(p_siteProfilesTable);
1885 commit;
1886 -- END IF;
1887
1888 END Do_Region_Upgrades_For_Global;
1889
1890 /*
1891 * Procedure for cloning regions at the global level
1892 */
1893
1894 PROCEDURE Clone_Regions_For_Global(p_ProfileTable IN CS_CF_UPG_UTL_PKG.ProfileTAble)
1895
1896 IS
1897
1898 l_count NUMBER := p_ProfileTable.COUNT;
1899 l_index NUMBER := 0;
1900 l_profileOptionName FND_PROFILE_OPTIONS.profile_option_name%TYPE;
1901 l_profileOptionValue FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE;
1902 l_newPrimaryContactRegionCode VARCHAR2(30) := 'IBU_CF_SR_10_G';
1903 l_newAddressRegionCode VARCHAR2(30) := 'IBU_CF_SR_20_G';
1904 l_newUpdateAddressRegionCode VARCHAR2(30) := 'IBU_CF_SR_25_G';
1905 l_newIdentifyProductRegionCode VARCHAR2(30) := 'IBU_CF_SR_30_G';
1906 l_newTemplateProductRegionCode VARCHAR2(30) := 'IBU_CF_SR_35_G';
1907 l_newIdentifyProblemRegionCode VARCHAR2(30) := 'IBU_CF_SR_40_G';
1908 l_newReviewRegionCode VARCHAR2(30) := 'IBU_CF_SR_50_G';
1909 l_newProblemDetailsRegionCode VARCHAR2(30) := 'IBU_CF_SR_60_G';
1910 l_newContactInfoRegionCode VARCHAR2(30) := 'IBU_CF_SR_70_G';
1911 l_newDtlOverviewRegionCode VARCHAR2(30) := 'IBU_CF_SR_130_G';
1912 l_newDtlContactAddrRegionCode VARCHAR2(30) := 'IBU_CF_SR_210_G';
1913 l_newDtlTabsRegionCode VARCHAR2(30) := 'IBU_CF_SR_160_G';
1914 l_newDtlDetailsRegionCode VARCHAR2(30) := 'IBU_CF_SR_190_G';
1915 l_newDtlProgOptionsRegionCode VARCHAR2(30) := 'IBU_CF_SR_110_G';
1916 l_newDtlProgressRegionCode VARCHAR2(30) := 'IBU_CF_SR_120_G';
1920 l_newCreateViewRegionCode VARCHAR2(30) := 'IBU_CF_SR_430_G';
1917 l_newDtlResolnRegionCode VARCHAR2(30) := 'IBU_CF_SR_150_G';
1918 l_newCreateTemplateRegionCode VARCHAR2(30) := 'IBU_CF_SR_310_G';
1919 l_newUpdateTemplateRegionCode VARCHAR2(30) := 'IBU_CF_SR_320_G';
1921 l_newUpdateViewRegionCode VARCHAR2(30) := 'IBU_CF_SR_440_G';
1922 l_newSearchViewRegionCode VARCHAR2(30) := 'IBU_CF_SR_450_G';
1923 l_newProductFilterRegionCode VARCHAR2(30) := 'IBU_CF_SR_420_G';
1924 l_newRegProductRegionCode VARCHAR2(30) := 'IBU_CF_SR_80_G';
1925 l_newAllProductRegionCode VARCHAR2(30) := 'IBU_CF_SR_90_G';
1926 l_newFilterRegionCode VARCHAR2(30) := 'IBU_CF_SR_410_G';
1927
1928 l_displayBillToAddress FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := 'N';
1929 l_displayBillToContact FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := 'N';
1930 l_displayShipToAddress FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := 'N';
1931 l_displayShipToContact FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := 'N';
1932 l_displayInstalledAtAddr FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := 'N';
1933 l_displayIncidentAddr FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := 'N';
1934 l_mandatoryIncidentAddr FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := 'N';
1935 l_displayAttachment FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
1936 l_displayRegProducts FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
1937 l_displayAllProducts FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
1938 l_enableTemplate FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
1939 l_displayTasks FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
1940 l_mandatoryProblemCode FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
1941
1942
1943 l_region_count NUMBER := 0;
1944
1945 l_newIdentifyProblemExists BOOLEAN := FALSE;
1946 l_newPrimaryContactExists BOOLEAN := FALSE;
1947 l_newProblemDetailsExists BOOLEAN := FALSE;
1948 l_newAddressExists BOOLEAN := FALSE;
1949 l_newUpdateAddressExists BOOLEAN := FALSE;
1950 l_newContactInfoExists BOOLEAN := FALSE;
1951 l_newReviewExists BOOLEAN := FALSE;
1952 l_newDtlOverviewExists BOOLEAN := FALSE;
1953 l_newDtlContactAddrExists BOOLEAN := FALSE;
1954 l_newDtlTabsExists BOOLEAN := FALSE;
1955 l_newDtlProgressOptionsExists BOOLEAN := FALSE;
1956 l_newDtlResolnExists BOOLEAN := FALSE;
1957 l_newDtlDetailsExists BOOLEAN := FALSE;
1958 l_newDtlProgressExists BOOLEAN := FALSE;
1959 l_newIdentifyProductExists BOOLEAN := FALSE;
1960 l_newTemplateProductExists BOOLEAN := FALSE;
1961 l_newCreateTemplateExists BOOLEAN := FALSE;
1962 l_newUpdateTemplateExists BOOLEAN := FALSE;
1963 l_newProductFilterExists BOOLEAN := FALSE;
1964 l_newCreateViewExists BOOLEAN := FALSE;
1965 l_newUpdateViewExists BOOLEAN := FALSE;
1966 l_newSearchViewExists BOOLEAN := FALSE;
1967 l_newRegProductExists BOOLEAN := FALSE;
1968 l_newAllProductExists BOOLEAN := FALSE;
1969 l_newFilterExists BOOLEAN := FALSE;
1970
1971 -- the following set of variables are used to
1972 -- determine whether we've already examined
1973 -- the profile option. If we have, we don't need to look at it again
1974 l_examineSrAccountOption BOOLEAN := TRUE;
1975 l_examineAddressOption BOOLEAN := TRUE;
1976 l_examineCreateProdOption BOOLEAN := TRUE;
1977 l_examineAttachmentOption BOOLEAN := TRUE;
1978 l_examineTemplateOption BOOLEAN := TRUE;
1979 l_examineLoggingOption BOOLEAN := TRUE;
1980 l_examineTaskOption BOOLEAN := TRUE;
1981 l_examineProdSelectOption BOOLEAN := TRUE;
1982 l_examineProbCodeOption BOOLEAN := TRUE;
1983
1984 BEGIN
1985
1986 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Global', '');
1987 WHILE (l_index < l_count) LOOP
1988 l_profileOptionName := p_ProfileTable(l_index).profileOptionName;
1989 l_profileOptionValue := p_ProfileTable(l_index).profileOptionValue;
1990
1991 IF (l_profileOptionName = 'IBU_A_SR_ACCOUNT_OPTION' AND l_examineSrAccountOption) THEN
1992 -- clone the region
1993 l_newPrimaryContactRegionCode := 'IBU_CF_SR_10_GC';
1994
1995 -- the region doesn't already exist, so go ahead and create it
1996 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_10_G',
1997 672,
1998 l_newPrimaryContactRegionCode,
1999 672, TRUE);
2000
2001 IF (l_profileOptionValue = 'OPTIONAL') THEN
2002 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newPrimaryContactRegionCode,
2003 'IBU_CF_SR_ACCOUNT_NUMBER',
2004 'Y', 'N', null);
2005
2006 END IF;
2007 l_examineSrAccountOption := FALSE;
2008 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Global', 'Done with SR Account Option');
2009 ELSIF (l_profileOptionName = 'IBU_A_SR_PRODUCT_SELECTION_OPTION' AND l_examineProdSelectOption) THEN
2010 -- clone the regions
2011 l_newRegProductRegionCode := 'IBU_CF_SR_80_GC';
2012
2013 -- the region doesn't already exist, so go ahead and create it
2014 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_80_G',
2015 672,
2016 l_newRegProductRegionCode,
2017 672, TRUE);
2018
2019 IF (l_profileOptionValue = 'N') THEN
2020 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newRegProductRegionCode,
2021 'IBU_CF_SR_R_PROD_NAME_LOV',
2022 'Y', 'N', null);
2023
2024 END IF;
2025 l_newAllProductRegionCode := 'IBU_CF_SR_90_GC';
2026 -- the region doesn't already exist, so go ahead and create it
2027 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_90_G',
2028 672,
2032 IF (l_profileOptionValue = 'N') THEN
2029 l_newAllProductRegionCode,
2030 672, TRUE);
2031
2033 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAllProductRegionCode,
2034 'IBU_CF_SR_PROD_BY_NAME_LOV',
2035 'Y', 'N', null);
2036
2037 END IF;
2038 l_examineProdSelectOption := FALSE;
2039 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Global', 'Done with SR Product Selection Option');
2040 ELSIF ((l_profileOptionName = 'IBU_A_SR_BILLTO_ADDRESS_OPTION' OR
2041 l_profileOptionName = 'IBU_A_SR_BILLTO_CONTACT_OPTION' OR
2042 l_profileOptionName = 'IBU_A_SR_SHIPTO_ADDRESS_OPTION' OR
2043 l_profileOptionName = 'IBU_A_SR_SHIPTO_CONTACT_OPTION' OR
2044 l_profileOptionName = 'IBU_A_SR_INSTALLEDAT_ADDRESS_OPTION' OR
2045 l_profileOptionName = 'IBU_SR_ADDR_DISPLAY' OR
2046 l_profileOptionName = 'IBU_SR_ADDR_MANDATORY') AND
2047 l_examineAddressOption) THEN
2048
2049 -- we only want to clone the address region once if any of the
2050 -- address profile options are customized.
2051
2052 l_newAddressRegionCode := 'IBU_CF_SR_20_GC';
2053
2054 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_20_G',
2055 672,
2056 l_newAddressRegionCode,
2057 672, TRUE);
2058 l_newUpdateAddressRegionCode := 'IBU_CF_SR_25_GC';
2059
2060 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_25_G',
2061 672,
2062 l_newUpdateAddressRegionCode,
2063 672, TRUE);
2064
2065
2066 -- get all the address-specific profile option values
2067 -- for this resp
2068
2069 CS_CF_UPG_UTL_PKG.getAddressProfileValues(p_ProfileTable,
2070 l_displayBillToAddress,
2071 l_displayBillToContact,
2072 l_displayShipToAddress,
2073 l_displayShipToContact,
2074 l_displayInstalledAtAddr,
2075 l_displayIncidentAddr,
2076 l_mandatoryIncidentAddr);
2077
2078 IF (l_displayBillToAddress = 'Y' OR l_displayBillToContact = 'Y') THEN
2079 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2080 'IBU_CF_SR_BILL_TO_HDR',
2081 'Y', 'N', null);
2082 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2083 'IBU_CF_SR_BILL_TO_HDR',
2084 'Y', 'N', null);
2085
2086 END IF;
2087
2088 IF (l_displayBillToAddress = 'Y') THEN
2089 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2090 'IBU_CF_SR_BILL_TO_ADDRESS',
2091 l_displayBillToAddress, 'N', null);
2092 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2093 'IBU_CF_SR_BILL_TO_ADDRESS',
2094 l_displayBillToAddress, 'N', null);
2095
2096 END IF;
2097
2098 IF (l_displayBillToContact = 'Y') THEN
2099 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2100 'IBU_CF_SR_BILL_TO_CONTACT',
2101 l_displayBillToContact, 'N', null);
2102
2103 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2104 'IBU_CF_SR_BILL_TO_CONTACT',
2105 l_displayBillToContact, 'N', null);
2106
2107 END IF;
2108
2109 IF (l_displayShipToAddress = 'Y' OR l_displayShipToContact = 'Y') THEN
2110 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2111 'IBU_CF_SR_SHIP_TO_HDR',
2112 'Y', 'N', null);
2113 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2114 'IBU_CF_SR_SHIP_TO_HDR',
2115 'Y', 'N', null);
2116
2117 END IF;
2118
2119 IF (l_displayShipToAddress = 'Y') THEN
2120 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2121 'IBU_CF_SR_SHIP_TO_ADDRESS',
2122 l_displayShipToAddress, 'N', null);
2123 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2124 'IBU_CF_SR_SHIP_TO_ADDRESS',
2125 l_displayShipToAddress, 'N', null);
2126
2127
2128 END IF;
2129
2130 IF (l_displayShipToContact = 'Y') THEN
2131 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2132 'IBU_CF_SR_SHIP_TO_CONTACT',
2133 l_displayShipToContact, 'N', null);
2134 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2135 'IBU_CF_SR_SHIP_TO_CONTACT',
2136 l_displayShipToContact, 'N', null);
2137
2138 END IF;
2139
2140 IF (l_displayInstalledAtAddr = 'Y') THEN
2141 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2142 'IBU_CF_SR_INSTALLED_AT_HDR',
2143 'Y', 'N', null);
2144 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2145 'IBU_CF_SR_INSTALLED_AT_HDR',
2146 'Y', 'N', null);
2147
2148 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2149 'IBU_CF_SR_INSTALL_AT_ADDR',
2150 l_displayInstalledAtAddr, 'N', null);
2151
2152 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2153 'IBU_CF_SR_INSTALL_AT_ADDR',
2154 l_displayInstalledAtAddr, 'N', null);
2155
2156 END IF;
2157
2158 -- Now we take care of the case for the incident address
2159 -- Note that we do not take care of the case where
2160 -- display addr = N but mandatory addr = Y, because it
2161 -- doesn't make sense
2162 IF (l_displayIncidentAddr = 'Y' AND l_mandatoryIncidentAddr = 'Y') THEN
2163 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2164 'IBU_CF_SR_INCIDENT_ADDRESS_HDR',
2165 'Y', 'Y', null);
2166 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2170 'IBU_CF_SR_CITY',
2167 'IBU_CF_SR_ADDRESS',
2168 'Y', 'Y', null);
2169 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2171 'Y', 'Y', null);
2172 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2173 'IBU_CF_SR_STATE',
2174 'Y', 'Y', null);
2175 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2176 'IBU_CF_SR_PROVINCE',
2177 'Y', 'N', null);
2178 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2179 'IBU_CF_SR_POSTAL_CODE',
2180 'Y', 'N', null);
2181 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2182 'IBU_CF_SR_COUNTRY',
2183 'Y', 'Y', null);
2184 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2185 'IBU_CF_SR_COUNTY',
2186 'Y', 'N', null);
2187 -- we update this for the address region for update SR, but the
2188 -- mandatory flag is ignored.
2189 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2190 'IBU_CF_SR_INCIDENT_ADDRESS_HDR',
2191 'Y', 'N', null);
2192 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2193 'IBU_CF_SR_ADDRESS',
2194 'Y', 'N', null);
2195 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2196 'IBU_CF_SR_CITY',
2197 'Y', 'N', null);
2198 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2199 'IBU_CF_SR_STATE',
2200 'Y', 'N', null);
2201 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2202 'IBU_CF_SR_PROVINCE',
2203 'Y', 'N', null);
2204 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2205 'IBU_CF_SR_POSTAL_CODE',
2206 'Y', 'N', null);
2207 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2208 'IBU_CF_SR_COUNTRY',
2209 'Y', 'N', null);
2210 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2211 'IBU_CF_SR_COUNTY',
2212 'Y', 'N', null);
2213
2214 ELSIF (l_displayIncidentAddr = 'Y' AND l_mandatoryIncidentAddr = 'N') THEN
2215 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2216 'IBU_CF_SR_INCIDENT_ADDRESS_HDR',
2217 'Y', 'N', null);
2218 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2219 'IBU_CF_SR_ADDRESS',
2220 'Y', 'N', null);
2221 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2222 'IBU_CF_SR_CITY',
2223 'Y', 'N', null);
2224 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2225 'IBU_CF_SR_STATE',
2226 'Y', 'N', null);
2227 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2228 'IBU_CF_SR_PROVINCE',
2229 'Y', 'N', null);
2230 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2231 'IBU_CF_SR_POSTAL_CODE',
2232 'Y', 'N', null);
2233 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2234 'IBU_CF_SR_COUNTRY',
2235 'Y', 'N', null);
2236 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2237 'IBU_CF_SR_COUNTY',
2238 'Y', 'N', null);
2239
2240 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2241 'IBU_CF_SR_INCIDENT_ADDRESS_HDR',
2242 'Y', 'N', null);
2243 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2244 'IBU_CF_SR_ADDRESS',
2245 'Y', 'N', null);
2246 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2247 'IBU_CF_SR_CITY',
2248 'Y', 'N', null);
2249 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2250 'IBU_CF_SR_STATE',
2251 'Y', 'N', null);
2252 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2253 'IBU_CF_SR_PROVINCE',
2254 'Y', 'N', null);
2255 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2256 'IBU_CF_SR_POSTAL_CODE',
2257 'Y', 'N', null);
2258 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2259 'IBU_CF_SR_COUNTRY',
2260 'Y', 'N', null);
2261 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
2262 'IBU_CF_SR_COUNTY',
2263 'Y', 'N', null);
2264
2265 ELSIF (l_displayIncidentAddr = 'N' AND l_mandatoryIncidentAddr = 'Y') THEN
2266 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2267 'IBU_CF_SR_INCIDENT_ADDRESS_HDR',
2268 'N', 'Y', null);
2269 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2270 'IBU_CF_SR_ADDRESS',
2271 'N', 'Y', null);
2272 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2273 'IBU_CF_SR_CITY',
2274 'N', 'Y', null);
2275 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2276 'IBU_CF_SR_STATE',
2277 'N', 'Y', null);
2278 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2279 'IBU_CF_SR_PROVINCE',
2280 'N', 'N', null);
2281 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2282 'IBU_CF_SR_POSTAL_CODE',
2283 'N', 'N', null);
2284 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2285 'IBU_CF_SR_COUNTRY',
2286 'N', 'Y', null);
2287 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
2288 'IBU_CF_SR_COUNTY',
2289 'N', 'N', null);
2290
2291 -- we can ignore this case for the address region in update SR
2292 END IF;
2293 l_examineAddressOption := FALSE;
2294 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Global', 'Done with Address Options');
2295
2299 -- Templates, so we need to clone that region as well.
2296 ELSIF (l_profileOptionName = 'IBU_SR_CREATION_PRODUCT_OPTION' AND l_examineCreateProdOption) THEN
2297
2298 -- mkcyee 12/14/2004 - This profile also impacts the product region for
2300
2301 l_newIdentifyProductRegionCode := 'IBU_CF_SR_30_GC';
2302 l_newTemplateProductRegioncode := 'IBU_CF_SR_35_GC';
2303
2304 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_30_G',
2305 672,
2306 l_newIdentifyProductRegionCode,
2307 672, TRUE);
2308
2309 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_35_G',
2310 672,
2311 l_newTemplateProductRegionCode,
2312 672, TRUE);
2313
2314 IF (l_profileOptionValue = 'USE_ONLY_INSTALLBASE_PRODUCT') THEN
2315 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProductRegionCode,
2316 'IBU_CF_SR_ALL_PRODUCT_RG',
2317 'N', 'N', null);
2318
2319 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newTemplateProductRegionCode,
2320 'IBU_CF_SR_ALL_PRODUCT_RG',
2321 'N', 'N', null);
2322
2323 ELSIF (l_profileOptionValue = 'USE_ONLY_INVENTORY_PRODUCT') THEN
2324 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProductRegionCode,
2325 'IBU_CF_SR_REG_PRODUCT_RG',
2326 'N', 'N', null);
2327
2328 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newTemplateProductRegionCode,
2329 'IBU_CF_SR_REG_PRODUCT_RG',
2330 'N', 'N', null);
2331
2332 END IF;
2333 l_newProductFilterRegionCode := 'IBU_CF_SR_420_GC';
2334
2335 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_420_G',
2336 672,
2337 l_newProductFilterRegionCode,
2338 672, TRUE);
2339 IF (l_profileOptionValue = 'USE_ONLY_INSTALLBASE_PRODUCT') THEN
2340 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newProductFilterRegionCode,
2341 'IBU_CF_SR_ALL_PRODUCT_RG',
2342 'N', 'N', null);
2343
2344 ELSIF (l_profileOptionValue = 'USE_ONLY_INVENTORY_PRODUCT') THEN
2345 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newProductFilterRegionCode,
2346 'IBU_CF_SR_REG_PRODUCT_RG',
2347 'N', 'N', null);
2348 END IF;
2349 l_examineCreateProdOption := FALSE;
2350 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Global', 'Done with Creation Product Option');
2351 ELSIF ((l_profileOptionName = 'IBU_A_SR_ATTACHMENT_OPTION' AND l_examineAttachmentOption) OR (l_profileOptionName = 'IBU_A_SR_PROB_CODE_MANDATORY' and l_examineProbCodeOption)) THEN
2352 l_newIdentifyProblemRegionCode := 'IBU_CF_SR_40_GC';
2353
2354 CS_CF_UPG_UTL_PKG.getAttachmentProbCodeValues(p_ProfileTable,
2355 l_displayAttachment,
2356 l_mandatoryProblemCode);
2357
2358 IF (l_displayAttachment = 'DONOTSHOW' OR l_displayAttachment = 'SHOWDURINGUPDATE' OR l_mandatoryProblemCode = 'Y') THEN
2359 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_40_G',
2360 672,
2361 l_newIdentifyProblemRegionCode,
2362 672, TRUE);
2363 IF (l_displayAttachment = 'DONOTSHOW') THEN
2364 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProblemRegionCode,
2365 'IBU_CF_SR_ATTACHMENTS_RG',
2366 'N', 'N', null);
2367 ELSIF (l_displayAttachment = 'SHOWDURINGUPDATE') THEN
2368 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProblemRegionCode,
2369 'IBU_CF_SR_ATTACHMENTS_RG',
2370 'N', 'N', null);
2371 ELSIF (l_mandatoryProblemCode = 'Y') THEN
2372 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProblemRegionCode,
2373 'IBU_CF_SR_PROB_TYPE_CODE',
2374 'Y', 'Y', null);
2375
2376 END IF;
2377 END IF;
2378 l_newDtlOverviewRegionCode := 'IBU_CF_SR_130_GC';
2379 IF (l_displayAttachment = 'DONOTSHOW' OR l_displayAttachment = 'SHOWDURINGCREATE') THEN
2380 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_130_G',
2381 672,
2382 l_newDtlOverviewRegionCode,
2386 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlOverviewRegionCode,
2383 672, TRUE);
2384
2385 IF (l_displayAttachment = 'DONOTSHOW') THEN
2387 'IBU_CF_SR_ATTACHMENTS_RG',
2388 'N', 'N', null);
2389 ELSIF (l_displayAttachment = 'SHOWDURINGCREATE') THEN
2393 END IF;
2390 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlOverviewRegionCode,
2391 'IBU_CF_SR_ATTACHMENTS_RG',
2392 'N', 'N', null);
2394 END IF;
2395 l_examineAttachmentOption := FALSE;
2396 l_examineProbCodeOption := FALSE;
2397 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Global', 'Done with Attachment Option and Mandatory Problem Code' );
2398 ELSIF (l_profileOptionName = 'IBU_SR_ENABLE_TEMPLATE' AND l_examineTemplateOption) THEN
2399 l_newProblemDetailsRegionCode := 'IBU_CF_SR_60_GC';
2400 IF (l_profileOptionName = 'N') THEN
2401 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_60_G',
2402 672,
2403 l_newProblemDetailsRegionCode,
2404 672, TRUE);
2405
2406 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newProblemDetailsRegionCode,
2407 'IBU_CF_SR_PROB_DETAILS',
2408 'N', 'N', null);
2409 END IF;
2410 l_examineTemplateOption := FALSE;
2411 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Global', 'Done with Template Option');
2412
2413 ELSIF (l_profileOptionName = 'IBU_A_SR_ENABLE_INTERACTION_LOGGING' AND l_examineLoggingOption) THEN
2414 l_newDtlProgOptionsRegionCode := 'IBU_CF_SR_110_GC';
2415 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_110_G',
2416 672,
2417 l_newDtlProgOptionsRegionCode,
2418 672, TRUE);
2419 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlProgOptionsRegionCode,
2420 'IBU_CF_SR_DTL_PROGRESS_INTRCT',
2421 'Y', 'N', null);
2422 l_examineLoggingOption := FALSE;
2423 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Global', 'Done with Interaction Logging Option');
2424 ELSIF (l_profileOptionName = 'IBU_SR_TASK_DISPLAY' AND l_examineTaskOption) THEN
2425 l_newDtlResolnRegionCode := 'IBU_CF_SR_150_GC';
2426 IF (l_profileOptionValue = 'Y') THEN
2427 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_150_G',
2428 672,
2429 l_newDtlResolnRegionCode,
2430 672, TRUE);
2431 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlResolnRegionCode,
2432 'IBU_CF_SR_DTL_ACTS_RG',
2433 'Y', 'N', null);
2434 END IF;
2435 l_examineTaskOption := FALSE;
2436 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Global', 'Done with Task Display Option');
2437 END IF; -- end profile checks
2438 l_index := l_index + 1;
2439 END LOOP; -- end while loop
2440
2441 -- Check whether a region has already been cloned for
2442 -- Primary Contact sub region
2443 l_newPrimaryContactRegionCode := 'IBU_CF_SR_10_GC';
2444 OPEN does_region_already_exists(l_newPrimaryContactRegionCode, 672);
2445 FETCH does_region_already_exists INTO l_region_count;
2446 CLOSE does_region_already_exists;
2447
2448 IF (l_region_count > 0) THEN
2449 l_newPrimaryContactExists := TRUE;
2450 END IF;
2451
2452 -- Check whether a region has already been cloned for
2453 -- Reg Product sub region
2454 l_newRegProductRegionCode := 'IBU_CF_SR_80_GC';
2455 OPEN does_region_already_exists(l_newRegProductRegionCode, 672);
2456 FETCH does_region_already_exists INTO l_region_count;
2457 CLOSE does_region_already_exists;
2458
2459 IF (l_region_count > 0) THEN
2460 l_newRegProductExists := TRUE;
2461 END IF;
2462
2463 -- Check whether a region has already been cloned for
2464 -- All Product sub region
2465 l_newAllProductRegionCode := 'IBU_CF_SR_90_GC';
2466 OPEN does_region_already_exists(l_newAllProductRegionCode, 672);
2467 FETCH does_region_already_exists INTO l_region_count;
2468 CLOSE does_region_already_exists;
2469
2470 IF (l_region_count > 0) THEN
2471 l_newAllProductExists := TRUE;
2472 END IF;
2473
2474 -- Check whether a region has already been cloned for
2475 -- Identify Product sub region
2476 l_newIdentifyProductRegionCode := 'IBU_CF_SR_30_GC';
2477 OPEN does_region_already_exists(l_newIdentifyProductRegionCode, 672);
2478 FETCH does_region_already_exists INTO l_region_count;
2479 CLOSE does_region_already_exists;
2480
2481 IF (l_region_count > 0) THEN
2482 l_newIdentifyProductExists := TRUE;
2483 select node_display_flag INTO
2484 l_displayRegProducts
2485 from ak_region_items
2486 where region_code = l_newIdentifyProductRegionCode
2487 and attribute_code = 'IBU_CF_SR_REG_PRODUCT_RG'
2488 and region_application_id = 672
2489 and attribute_application_id = 672;
2490
2491 select node_display_flag INTO
2492 l_displayAllProducts
2493 from ak_region_items
2494 where region_code = l_newIdentifyProductRegionCode
2495 and attribute_code = 'IBU_CF_SR_ALL_PRODUCT_RG'
2496 and region_application_id = 672
2497 and attribute_application_id = 672;
2498 END IF;
2499
2500 IF (l_newIdentifyProductExists) THEN
2501 l_newTemplateProductExists := TRUE;
2502 END IF;
2503
2504 -- Check whether a region has been cloned for Product Filter
2505 l_newProductFilterRegionCode := 'IBU_CF_SR_420_GC';
2506 OPEN does_region_already_exists(l_newProductFilterRegionCode, 672);
2507 FETCH does_region_already_exists INTO l_region_count;
2508 CLOSE does_region_already_exists;
2509
2510 IF (l_region_count > 0) THEN
2511 l_newProductFilterExists := TRUE;
2512 END IF;
2513
2514
2515 -- Check whether a region has been cloned for addresses
2516 l_newAddressRegionCode := 'IBU_CF_SR_20_GC';
2517 OPEN does_region_already_exists(l_newAddressRegionCode, 672);
2518 FETCH does_region_already_exists INTO l_region_count;
2519 CLOSE does_region_already_exists;
2520
2524
2521 IF (l_region_count > 0) THEN
2522 l_newAddressExists := TRUE;
2523 END IF;
2525 l_newUpdateAddressRegionCode := 'IBU_CF_SR_25_GC';
2526 OPEN does_region_already_exists(l_newUpdateAddressRegionCode, 672);
2527 FETCH does_region_already_exists INTO l_region_count;
2528 CLOSE does_region_already_exists;
2529
2530 IF (l_region_count > 0) THEN
2531 l_newUpdateAddressExists := TRUE;
2532 END IF;
2533
2534
2535
2536
2537 -- Check cloned subregions for Create Service Request flows
2538
2539 -- Check whether a region has already been
2540 -- cloned for Identify Problem
2541 l_newIdentifyProblemRegionCode := 'IBU_CF_SR_40_GC';
2542 OPEN does_region_already_exists(l_newIdentifyProblemRegionCode, 672);
2543 FETCH does_region_already_exists INTO l_region_count;
2544 CLOSE does_region_already_exists;
2545
2546 IF (l_region_count > 0) THEN
2547 l_newIdentifyProblemExists := TRUE;
2548
2549 select node_display_flag INTO
2550 l_displayAttachment
2551 from ak_region_items
2552 where region_code = l_newIdentifyProblemRegionCode
2553 and attribute_code = 'IBU_CF_SR_ATTACHMENTS_RG'
2554 and region_application_id = 672
2555 and attribute_application_id = 672;
2556
2557 ELSIF (l_newPrimaryContactExists OR l_newIdentifyProductExists OR l_newAddressExists) THEN
2558 -- mkcyee 12/07/04 - new Address region has been added to Identify
2559 -- Problem page, so we need to clone this region is Address region
2560 -- has been cloned
2561
2562 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_40_G',
2563 672,
2564 l_newIdentifyProblemRegionCode,
2565 672, TRUE);
2566 l_newIdentifyProblemExists := TRUE;
2567 END IF;
2568
2569 -- Check whether a region has already been cloned for
2570 -- Problem Details
2571 l_newProblemDetailsRegionCode := 'IBU_CF_SR_60_GC';
2572 OPEN does_region_already_exists(l_newProblemDetailsRegionCode, 672);
2573 FETCH does_region_already_exists INTO l_region_count;
2574 CLOSE does_region_already_exists;
2575
2576 IF (l_region_count > 0) THEN
2577 l_newProblemDetailsExists := TRUE;
2578
2579 select node_display_flag INTO
2580 l_enableTemplate
2581 from ak_region_items
2582 where region_code = l_newProblemDetailsRegionCode
2583 and attribute_code = 'IBU_CF_SR_PROB_DETAILS'
2584 and region_application_id = 672
2585 and attribute_application_id = 672;
2586 ELSIF (l_newPrimaryContactExists OR l_newAddressExists) THEN
2587 -- mkcyee 12/07/04 - new Address region has been added to Problem
2588 -- details page, so we need to clone this region is Address region
2589 -- has been cloned
2590 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_60_G',
2591 672,
2592 l_newProblemDetailsRegionCode,
2593 672, TRUE);
2594 l_newProblemDetailsExists := TRUE;
2595 END IF;
2596
2597
2598 -- Check whether a region has already been cloned for
2599 -- Update Overview sub region
2600 l_newDtlOverviewRegionCode := 'IBU_CF_SR_130_GC';
2601 OPEN does_region_already_exists(l_newDtlOverviewRegionCode, 672);
2602 FETCH does_region_already_exists INTO l_region_count;
2603 CLOSE does_region_already_exists;
2604
2605 IF (l_region_count > 0) THEN
2606 l_newDtlOverviewExists := TRUE;
2607 END IF;
2608
2609
2610 -- Check whether a region has been cloned for Contact Info
2611 l_newContactInfoRegionCode := 'IBU_CF_SR_70_GC' ;
2612 IF (l_newPrimaryContactExists OR l_newAddressExists) THEN
2613 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_70_G',
2614 672,
2615 l_newContactInfoRegionCode,
2616 672, TRUE);
2617 l_newContactInfoExists := TRUE;
2618 END IF;
2619
2620 l_newReviewRegionCode := 'IBU_CF_SR_50_GC' ;
2621 IF (l_newAddressExists OR l_enableTemplate = 'N' OR
2622 l_displayAttachment='N') THEN
2623 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_50_G',
2624 672,
2625 l_newReviewRegionCode,
2626 672, TRUE);
2627
2628 l_newReviewExists := TRUE;
2629 END IF;
2630
2631 -- Check whether a region has been cloned for progress options
2632 l_newDtlProgOptionsRegionCode := 'IBU_CF_SR_110_GC' ;
2633 OPEN does_region_already_exists(l_newDtlProgOptionsRegionCode, 672);
2634 FETCH does_region_already_exists INTO l_region_count;
2635 CLOSE does_region_already_exists;
2636
2637 IF (l_region_count > 0) THEN
2638 l_newDtlProgressOptionsExists := TRUE;
2639 END IF;
2640
2641 -- Check whether a region has been cloned for IBU_CF_SR_DTL_RESOLN
2642 l_newDtlResolnRegionCode := 'IBU_CF_SR_150_GC' ;
2643 OPEN does_region_already_exists(l_newDtlResolnRegionCode, 672);
2644 FETCH does_region_already_exists INTO l_region_count;
2645 CLOSE does_region_already_exists;
2646
2647 IF (l_region_count > 0) THEN
2648 l_newDtlResolnExists := TRUE;
2649 select node_display_flag INTO
2650 l_displayTasks
2651 from ak_region_items
2652 where region_code = l_newDtlResolnRegionCode
2653 and attribute_code = 'IBU_CF_SR_DTL_ACTS_RG'
2654 and region_application_id = 672
2655 and attribute_application_id = 672;
2656 END IF;
2657
2658 -- If regions that were created impact other regions,
2659 -- make sure to set them to the proper region code.
2660 -- For some cases, we may have to clone new regions
2661
2662
2663 IF (l_newAllProductExists AND l_newRegProductExists) THEN
2664 IF NOT(l_newIdentifyProductExists) THEN
2665 -- need to clone the IBU_CF_SR_IDENTIFY_PRODUCT region
2666 l_newIdentifyProductRegionCode := 'IBU_CF_SR_30_GC';
2667 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_30_G',
2671 l_newIdentifyProductExists := TRUE;
2668 672,
2669 l_newIdentifyProductRegionCode,
2670 672, TRUE);
2672 IF (l_displayRegProducts = '') THEN
2673 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProductRegionCode,
2674 'IBU_CF_SR_REG_PRODUCT_RG',
2675 'Y', 'N', l_newRegProductRegionCode);
2676 ELSE
2677 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProductRegionCode,
2678 'IBU_CF_SR_REG_PRODUCT_RG',
2679 l_displayRegProducts, 'N', l_newRegProductRegionCode);
2680 END IF;
2681 IF (l_displayAllProducts = '') THEN
2682 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProductRegionCode,
2683 'IBU_CF_SR_ALL_PRODUCT_RG',
2684 'Y', 'N', l_newAllProductRegionCode);
2685 ELSE
2686 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProductRegionCode,
2687 'IBU_CF_SR_ALL_PRODUCT_RG',
2688 l_displayAllProducts, 'N', l_newAllProductRegionCode);
2689 END IF;
2690 END IF;
2691 END IF;
2692
2693 IF (l_displayRegProducts = 'N' OR l_displayAllProducts = 'N') THEN
2694 IF NOT(l_newTemplateProductExists) THEN
2695 -- need to clone the IBU_CF_SR_IDENTIFY_PRODUCT region for Templates
2696 l_newTemplateProductRegionCode := 'IBU_CF_SR_35_GC';
2700 672, TRUE);
2697 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_35_G',
2698 672,
2699 l_newTemplateProductRegionCode,
2701 l_newTemplateProductExists := TRUE;
2702 IF (l_displayRegProducts = 'N') THEN
2703 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newTemplateProductRegionCode,
2704 'IBU_CF_SR_REG_PRODUCT_RG',
2705 'N', 'N', 'IBU_CF_SR_REG_PRODUCT');
2706 END IF;
2707 IF (l_displayAllProducts = 'N') THEN
2708 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newTemplateProductRegionCode,
2709 'IBU_CF_SR_ALL_PRODUCT_RG',
2710 'N', 'N', 'IBU_CF_SR_ALL_PRODUCT');
2711 END IF;
2712 END IF;
2713 IF NOT(l_newProductFilterExists) THEN
2714 -- need to clone the IBU_CF_SR_VW_PRODUCT_FILTER region
2715 l_newProductFilterRegionCode := 'IBU_CF_SR_420_GC';
2716 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_420_G',
2717 672,
2718 l_newProductFilterRegionCode,
2719 672, TRUE);
2720 l_newProductFilterExists := TRUE;
2721 IF (l_displayRegProducts = 'N') THEN
2722 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newProductFilterRegionCode,
2723 'IBU_CF_SR_REG_PRODUCT_RG',
2724 'N', 'N', 'IBU_CF_SR_REG_PRODUCT');
2725 END IF;
2726 IF (l_displayAllProducts = 'N') THEN
2727 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newProductFilterRegionCode,
2728 'IBU_CF_SR_ALL_PRODUCT_RG',
2729 'N', 'N', 'IBU_CF_SR_ALL_PRODUCT');
2730 END IF;
2731 END IF;
2732 END IF;
2733
2734 IF (l_newIdentifyProblemExists) THEN
2735 IF (l_newPrimaryContactExists) THEN
2736 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProblemRegionCode,
2737 'IBU_CF_SR_PRIMARY_CONTACT_RG',
2738 'Y', 'N', l_newPrimaryContactRegionCode);
2739 END IF;
2740 IF (l_newIdentifyProductExists) THEN
2741 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProblemRegionCode,
2742 'IBU_CF_SR_IDENTIFY_PRODUCT_RG',
2743 'Y', 'N', l_newIdentifyProductRegionCode);
2744
2745 END IF;
2746 IF (l_newAddressExists) THEN
2747 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProblemRegionCode,
2748 'IBU_CF_SR_ADDRESS_RG',
2749 'N', 'N', l_newAddressRegionCode);
2750 END IF;
2751 END IF;
2752
2753 IF (l_newProblemDetailsExists) THEN
2754 IF (l_newPrimaryContactExists) THEN
2755 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newProblemDetailsRegionCode,
2756 'IBU_CF_SR_PRIMARY_CONTACT_RG',
2757 'Y', 'N', l_newPrimaryContactRegionCode);
2758 END IF;
2759 IF (l_displayAttachment = 'N') THEN
2760 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProblemRegionCode,
2761 'IBU_CF_SR_ATTACHMENTS_RG',
2762 'N', 'N', null);
2763 END IF;
2764
2765 -- mkcyee 12/07/2004 - the address region has been added to Identify
2769 'IBU_CF_SR_ADDRESS_RG',
2766 -- Problem and Problem Details
2767 IF (l_newAddressExists) THEN
2768 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newProblemDetailsRegionCode,
2770 'N', 'N', l_newAddressRegionCode);
2771 END IF;
2772 END IF;
2773
2774
2775 IF (l_newReviewExists) THEN
2776 IF (l_displayAttachment = 'N') THEN
2777 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newReviewRegionCode,
2778 'IBU_CF_SR_ATTACHMENTS_RG',
2779 'N', 'N', null);
2780 END IF;
2781 IF (l_newAddressExists) THEN
2782 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newReviewRegionCode,
2783 'IBU_CF_SR_ADDRESS_RG',
2784 'Y', 'N', l_newAddressRegionCode);
2785 END IF;
2786 IF (l_enableTemplate = 'N') THEN
2787 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newReviewRegionCode,
2788 'IBU_CF_SR_PROB_DETAILS',
2789 'N', 'N', null);
2790 END IF;
2791 END IF;
2792
2793 IF (l_newContactInfoExists) THEN
2794 IF (l_newAddressExists) THEN
2795 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newContactInfoRegionCode,
2796 'IBU_CF_SR_ADDRESS_RG',
2797 'Y', 'N', l_newAddressRegionCode);
2798 END IF;
2799 IF (l_newPrimaryContactExists) THEN
2800 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newContactInfoRegionCode,
2801 'IBU_CF_SR_PRIMARY_CONTACT_RG',
2802 'Y', 'N', l_newPrimaryContactRegionCode);
2803 END IF;
2804 END IF;
2805 --For update service request flows, clone required regions
2806 IF (l_newUpdateAddressExists) THEN
2807
2808 -- need to clone the IBU_CF_SR_DTL_CONTACT region
2809 l_newDtlContactAddrRegionCode := 'IBU_CF_SR_210_GC' ;
2810 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_210_G',
2811 672,
2812 l_newDtlContactAddrRegionCode,
2813 672, TRUE);
2814 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlContactAddrRegionCode,
2815 'IBU_CF_SR_ADDRESS_RG',
2816 'Y', 'N', l_newUpdateAddressRegionCode);
2817
2818 l_newDtlContactAddrExists := TRUE;
2819 END IF;
2820
2821 IF (l_newDtlProgressOptionsExists) THEN
2822 -- We must clone IBU_CF_SR_DTL_PROGRESS
2823 l_newDtlProgressRegionCode := 'IBU_CF_SR_120_GC' ;
2824 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_120_G',
2825 672,
2826 l_newDtlProgressRegionCode,
2827 672, TRUE);
2828 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlProgressRegionCode,
2829 'IBU_CF_SR_DTL_PROGRESS_OPT_RG',
2830 'Y', 'N', l_newDtlProgOptionsRegionCode);
2831 l_newDtlProgressExists := TRUE;
2832 END IF;
2833
2834 IF (l_newDtlProgressExists OR l_newDtlResolnExists) THEN
2835 -- if any of these regions are cloned, then we
2836 -- must clone IBU_CF_SR_DTL_OVERVIEW
2837 l_newDtlOverviewRegionCode := 'IBU_CF_SR_130_GC';
2838
2839 IF NOT (l_newDtlOverviewExists) THEN
2840 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_130_G',
2841 672,
2842 l_newDtlOverviewRegionCode,
2843 672, TRUE);
2844 END IF;
2845 IF (l_newUpdateAddressExists) THEN
2846 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlOverviewRegionCode,
2847 'IBU_CF_SR_ADDRESS_RG',
2848 'N', 'N', l_newUpdateAddressRegionCode);
2849 END IF;
2850 IF (l_newDtlProgressExists) THEN
2851 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlOverviewRegionCode,
2852 'IBU_CF_SR_DTL_PROGRESS_RG',
2853 'Y', 'N', l_newDtlProgressRegionCode);
2854 END IF;
2855 IF (l_newDtlResolnExists AND l_displayTasks = 'Y') THEN
2856 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlOverviewRegionCode,
2857 'IBU_CF_SR_DTL_ACTS_RG',
2858 'Y', 'N', null);
2859 END IF;
2860 l_newDtlOverviewExists := TRUE;
2861 END IF;
2862
2863
2864 IF (l_newDtlOverviewExists OR l_newDtlResolnExists) THEN
2865 -- then we must clone IBU_CF_SR_DTL_TABS
2866 l_newDtlTabsRegionCode := 'IBU_CF_SR_160_GC' ;
2867 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_160_G',
2868 672,
2869 l_newDtlTabsRegionCode,
2870 672, TRUE);
2871 IF (l_newDtlOverviewExists) THEN
2872 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlTabsRegionCode,
2873 'IBU_CF_SR_DTL_OVERVIEW_TAB_RG',
2874 'Y', 'N', l_newDtlOverviewRegionCode);
2875 END IF;
2876 IF (l_newDtlResolnExists) THEN
2877 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlTabsRegionCode,
2878 'IBU_CF_SR_DTL_RESOLN_TAB_RG',
2879 'N', 'N', l_newDtlResolnRegionCode);
2880 END IF;
2881 l_newDtlTabsExists := TRUE;
2882 END IF;
2883
2884 IF (l_newDtlTabsExists) THEN
2885 -- then we must clone IBU_CF_SR_DETAILS
2886 l_newDtlDetailsRegionCode := 'IBU_CF_SR_190_GC' ;
2887 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_190_G',
2888 672,
2889 l_newDtlDetailsRegionCode,
2890 672, TRUE);
2891 IF (l_newDtlDetailsExists) THEN
2892 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlDetailsRegionCode,
2893 'IBU_CF_SR_DTL_TABS_RG',
2894 'Y', 'N', l_newDtlTabsRegionCode);
2895 END IF;
2896 l_newDtlDetailsExists := TRUE;
2897 END IF;
2898
2899 -- Now check for the Templates
2900
2901 IF (l_newTemplateProductExists) THEN
2902 l_newCreateTemplateRegionCode := 'IBU_CF_SR_310_GC' ;
2903 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_310_G',
2904 672,
2905 l_newCreateTemplateRegionCode,
2906 672, TRUE);
2907 --IF (l_newPrimaryContactExists) THEN
2911 --END IF;
2908 -- CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newCreateTemplateRegionCode,
2909 -- 'IBU_CF_SR_PRIMARY_CONTACT_RG',
2910 -- 'Y', 'N', l_newPrimaryContactRegionCode);
2912 --IF (l_newTemplateProductExists) THEN
2913 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newCreateTemplateRegionCode,
2914 'IBU_CF_SR_IDENTIFY_PRODUCT_RG',
2915 'Y', 'N', l_newTemplateProductRegionCode);
2916 --END IF;
2917 l_newCreateTemplateExists := TRUE;
2918 l_newUpdateTemplateRegionCode := 'IBU_CF_SR_320_GC' ;
2919 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_320_G',
2920 672,
2921 l_newUpdateTemplateRegionCode,
2922 672, TRUE);
2923 --IF (l_newPrimaryContactExists) THEN
2924 -- CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateTemplateRegionCode,
2925 -- 'IBU_CF_SR_PRIMARY_CONTACT_RG',
2926 -- 'Y', 'N', l_newPrimaryContactRegionCode);
2927 --END IF;
2928 --IF (l_newTemplateProductExists) THEN
2929 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateTemplateRegionCode,
2930 'IBU_CF_SR_IDENTIFY_PRODUCT_RG',
2931 'Y', 'N', l_newTemplateProductRegionCode);
2932 --END IF;
2933 l_newUpdateTemplateExists := TRUE;
2934 END IF;
2935
2936
2937 IF (l_newProductFilterExists) THEN
2938 -- need to clone IBU_CF_SR_VW_FILTER
2939 l_newFilterRegionCode := 'IBU_CF_SR_410_GC' ;
2940 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_410_G',
2941 672,
2942 l_newFilterRegionCode,
2943 672, TRUE);
2944 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newFilterRegionCode,
2945 'IBU_CF_SR_VW_PRODUCT_FILTER_RG',
2946 'Y', 'N', l_newProductFilterRegionCode);
2947
2948 l_newFilterExists := TRUE;
2949 END IF;
2950
2951 IF (l_newFilterExists) THEN
2952 -- need to clone IBU_CF_SR_VW_SEARCH, IBU_CF_SR_VW_CREATE, IBU_CF_SR_VW_UPDATE
2953 l_newSearchViewRegionCode := 'IBU_CF_SR_450_GC' ;
2954 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_450_G',
2955 672,
2956 l_newSearchViewRegionCode,
2957 672, TRUE);
2958 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newSearchViewRegionCode,
2959 'IBU_CF_SR_VW_FILTER_RG',
2960 'Y', 'N', l_newFilterRegionCode);
2961
2962 l_newSearchViewExists := TRUE;
2963
2964 l_newCreateViewRegionCode := 'IBU_CF_SR_430_GC' ;
2965 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_430_G',
2966 672,
2967 l_newCreateViewRegionCode,
2968 672, TRUE);
2969 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newCreateViewRegionCode,
2970 'IBU_CF_SR_VW_FILTER_RG',
2971 'Y', 'N', l_newFilterRegionCode);
2972
2973 l_newCreateViewExists := TRUE;
2974
2975 l_newUpdateViewRegionCode := 'IBU_CF_SR_440_GC' ;
2976 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_440_G',
2977 672,
2978 l_newUpdateViewRegionCode,
2979 672, TRUE);
2980 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateViewRegionCode,
2981 'IBU_CF_SR_VW_FILTER_RG',
2982 'Y', 'N', l_newFilterRegionCode);
2983
2984 l_newUpdateViewExists := TRUE;
2985 END IF;
2986
2987 -- Now enter the rows in the cs_cf_source_cxt_targets table
2988 -- for this responsibility
2989
2990 IF (l_newIdentifyProblemExists ) THEN
2991 CS_CF_UPG_UTL_PKG.Insert_New_Target(
2992 'IBU_SR_CR_IDENTIFY_PROBLEM',
2993 'GLOBAL',
2994 NULL,
2995 NULL,
2996 NULL,
2997 NULL,
2998 l_newIdentifyProblemRegionCode,
2999 '672');
3000 END IF;
3001
3002 IF (l_newReviewExists) THEN
3003 CS_CF_UPG_UTL_PKG.Insert_New_Target(
3004 'IBU_SR_CR_REVIEW',
3005 'GLOBAL',
3006 NULL,
3007 NULL,
3008 NULL,
3009 NULL,
3010 l_newReviewRegionCode,
3011 '672');
3012 END IF;
3013
3014 IF (l_newProblemDetailsExists) THEN
3015 CS_CF_UPG_UTL_PKG.Insert_New_Target(
3016 'IBU_SR_CR_PROBLEM_DETAILS',
3017 'GLOBAL',
3018 NULL,
3019 NULL,
3020 NULL,
3021 NULL,
3022 l_newProblemDetailsRegionCode,
3023 '672');
3024 END IF;
3025
3026
3027 IF (l_newContactInfoExists) THEN
3028 CS_CF_UPG_UTL_PKG.Insert_New_Target(
3029 'IBU_SR_CR_CONTACT_INFORMATION',
3030 'GLOBAL',
3031 NULL,
3032 NULL,
3033 NULL,
3034 NULL,
3038
3035 l_newContactInfoRegionCode,
3036 '672');
3037 END IF;
3039 IF (l_newDtlDetailsExists) THEN
3040 CS_CF_UPG_UTL_PKG.Insert_New_Target(
3041 'IBU_SR_DETAILS',
3042 'GLOBAL',
3043 NULL,
3044 NULL,
3045 NULL,
3046 NULL,
3050
3047 l_newDtlDetailsRegionCode,
3048 '672');
3049 END IF;
3051 IF (l_newCreateTemplateExists) THEN
3052 CS_CF_UPG_UTL_PKG.Insert_New_Target(
3053 'IBU_SR_TEMP_CREATE',
3054 'GLOBAL',
3055 NULL,
3056 NULL,
3057 NULL,
3058 NULL,
3059 l_newCreateTemplateRegionCode,
3060 '672');
3061 END IF;
3062
3063 IF (l_newUpdateTemplateExists) THEN
3064 CS_CF_UPG_UTL_PKG.Insert_New_Target(
3065 'IBU_SR_TEMP_UPDATE',
3066 'GLOBAL',
3067 NULL,
3068 NULL,
3069 NULL,
3070 NULL,
3071 l_newUpdateTemplateRegionCode,
3072 '672');
3073 END IF;
3074
3075 IF (l_newSearchViewExists) THEN
3076 CS_CF_UPG_UTL_PKG.Insert_New_Target(
3077 'IBU_SR_VIEW_SUMMARY',
3078 'GLOBAL',
3079 NULL,
3080 NULL,
3081 NULL,
3082 NULL,
3083 l_newSearchViewRegionCode,
3084 '672');
3085 END IF;
3086
3087 IF (l_newCreateViewExists) THEN
3088 CS_CF_UPG_UTL_PKG.Insert_New_Target(
3089 'IBU_SR_VIEW_CREATE',
3090 'GLOBAL',
3091 NULL,
3092 NULL,
3093 NULL,
3094 NULL,
3095 l_newCreateViewRegionCode,
3096 '672');
3097 END IF;
3098
3099 IF (l_newUpdateViewExists) THEN
3100 CS_CF_UPG_UTL_PKG.Insert_New_Target(
3101 'IBU_SR_VIEW_UPDATE',
3102 'GLOBAL',
3103 NULL,
3104 NULL,
3105 NULL,
3106 NULL,
3107 l_newUpdateViewRegionCode,
3108 '672');
3109 END IF;
3110
3111 END Clone_Regions_For_Global;
3112
3113
3114
3115 /*
3116 * Perform the actually cloning
3117 * of ak regions, based on the list of
3118 * profiles that are customized at the appl level
3119 */
3120 PROCEDURE Clone_Regions_For_Appl(p_ProfileTable IN CS_CF_UPG_UTL_PKG.ProfileTable,
3121 p_ApplId IN NUMBER)
3122 IS
3123 l_count NUMBER := p_ProfileTable.COUNT;
3124 l_index NUMBER := 0;
3125 l_profileOptionName FND_PROFILE_OPTIONS.profile_option_name%TYPE;
3126 l_profileOptionValue FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE;
3127 l_newPrimaryContactRegionCode VARCHAR2(30) := 'IBU_CF_SR_10_G';
3128 l_newAddressRegionCode VARCHAR2(30) := 'IBU_CF_SR_20_G';
3129 l_newUpdateAddressRegionCode VARCHAR2(30) := 'IBU_CF_SR_25_G';
3130 l_newIdentifyProductRegionCode VARCHAR2(30) := 'IBU_CF_SR_30_G';
3131 l_newTemplateProductRegionCode VARCHAR2(30) := 'IBU_CF_SR_35_G';
3132 l_newIdentifyProblemRegionCode VARCHAR2(30) := 'IBU_CF_SR_40_G';
3133 l_newReviewRegionCode VARCHAR2(30) := 'IBU_CF_SR_50_G';
3134 l_newProblemDetailsRegionCode VARCHAR2(30) := 'IBU_CF_SR_60_G';
3135 l_newContactInfoRegionCode VARCHAR2(30) := 'IBU_CF_SR_70_G';
3136 l_newDtlOverviewRegionCode VARCHAR2(30) := 'IBU_CF_SR_130_G';
3137 l_newDtlContactAddrRegionCode VARCHAR2(30) := 'IBU_CF_SR_210_G';
3138 l_newDtlTabsRegionCode VARCHAR2(30) := 'IBU_CF_SR_160_G';
3139 l_newDtlDetailsRegionCode VARCHAR2(30) := 'IBU_CF_SR_190_G';
3140 l_newDtlProgOptionsRegionCode VARCHAR2(30) := 'IBU_CF_SR_110_G';
3141 l_newDtlProgressRegionCode VARCHAR2(30) := 'IBU_CF_SR_120_G';
3142 l_newDtlResolnRegionCode VARCHAR2(30) := 'IBU_CF_SR_150_G';
3143 l_newCreateTemplateRegionCode VARCHAR2(30) := 'IBU_CF_SR_310_G';
3144 l_newUpdateTemplateRegionCode VARCHAR2(30) := 'IBU_CF_SR_320_G';
3145 l_newCreateViewRegionCode VARCHAR2(30) := 'IBU_CF_SR_430_G';
3146 l_newUpdateViewRegionCode VARCHAR2(30) := 'IBU_CF_SR_440_G';
3147 l_newSearchViewRegionCode VARCHAR2(30) := 'IBU_CF_SR_450_G';
3148 l_newProductFilterRegionCode VARCHAR2(30) := 'IBU_CF_SR_420_G';
3149 l_newRegProductRegionCode VARCHAR2(30) := 'IBU_CF_SR_80_G';
3150 l_newAllProductRegionCode VARCHAR2(30) := 'IBU_CF_SR_90_G';
3151 l_newFilterRegionCode VARCHAR2(30) := 'IBU_CF_SR_410_G';
3152
3153 l_displayBillToAddress FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := 'N';
3154 l_displayBillToContact FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := 'N';
3155 l_displayShipToAddress FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := 'N';
3156 l_displayShipToContact FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := 'N';
3157 l_displayInstalledAtAddr FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := 'N';
3158 l_displayIncidentAddr FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := 'N';
3159 l_mandatoryIncidentAddr FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := 'N';
3160 l_displayAttachment FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
3161 l_displayRegProducts FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
3162 l_displayAllProducts FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
3163 l_enableTemplate FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
3164 l_displayTasks FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
3165 l_mandatoryProblemCode FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE := '';
3166
3167
3168 l_region_count NUMBER := 0;
3169
3170 l_ApplId VARCHAR2(10) := to_char(p_ApplId);
3171
3172 l_newIdentifyProblemExists BOOLEAN := FALSE;
3173 l_newPrimaryContactExists BOOLEAN := FALSE;
3174 l_newProblemDetailsExists BOOLEAN := FALSE;
3175 l_newAddressExists BOOLEAN := FALSE;
3176 l_newUpdateAddressExists BOOLEAN := FALSE;
3177 l_newContactInfoExists BOOLEAN := FALSE;
3178 l_newReviewExists BOOLEAN := FALSE;
3179 l_newDtlOverviewExists BOOLEAN := FALSE;
3180 l_newDtlContactAddrExists BOOLEAN := FALSE;
3181 l_newDtlTabsExists BOOLEAN := FALSE;
3182 l_newDtlProgressOptionsExists BOOLEAN := FALSE;
3183 l_newDtlResolnExists BOOLEAN := FALSE;
3184 l_newDtlDetailsExists BOOLEAN := FALSE;
3185 l_newDtlProgressExists BOOLEAN := FALSE;
3186 l_newIdentifyProductExists BOOLEAN := FALSE;
3187 l_newTemplateProductExists BOOLEAN := FALSE;
3191 l_newCreateViewExists BOOLEAN := FALSE;
3188 l_newCreateTemplateExists BOOLEAN := FALSE;
3189 l_newUpdateTemplateExists BOOLEAN := FALSE;
3190 l_newProductFilterExists BOOLEAN := FALSE;
3192 l_newUpdateViewExists BOOLEAN := FALSE;
3193 l_newSearchViewExists BOOLEAN := FALSE;
3194 l_newRegProductExists BOOLEAN := FALSE;
3195 l_newAllProductExists BOOLEAN := FALSE;
3196 l_newFilterExists BOOLEAN := FALSE;
3197
3198 -- the following set of variables are used to
3199 -- determine whether we've already examined
3200 -- the profile option. If we have, we don't need to look at it again
3201 l_examineSrAccountOption BOOLEAN := TRUE;
3202 l_examineAddressOption BOOLEAN := TRUE;
3206 l_examineLoggingOption BOOLEAN := TRUE;
3203 l_examineCreateProdOption BOOLEAN := TRUE;
3204 l_examineAttachmentOption BOOLEAN := TRUE;
3205 l_examineTemplateOption BOOLEAN := TRUE;
3207 l_examineTaskOption BOOLEAN := TRUE;
3208 l_examineProdSelectOption BOOLEAN := TRUE;
3209 l_examineProbCodeOption BOOLEAN := TRUE;
3210
3211 BEGIN
3212
3213 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Appl', 'Cloning regions for ApplId: ' || l_ApplId);
3214 WHILE (l_index < l_count) LOOP
3215 l_profileOptionName := p_ProfileTable(l_index).profileOptionName;
3216 l_profileOptionValue := p_ProfileTable(l_index).profileOptionValue;
3217
3218 IF (l_profileOptionName = 'IBU_A_SR_ACCOUNT_OPTION' AND l_examineSrAccountOption) THEN
3219 -- clone the region
3220 l_newPrimaryContactRegionCode := 'IBU_CF_SR_10_A' || p_ApplId;
3221
3222 -- the region doesn't already exist, so go ahead and create it
3223 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_10_G',
3224 672,
3225 l_newPrimaryContactRegionCode,
3226 672, FALSE);
3227
3228 IF (l_profileOptionValue = 'OPTIONAL') THEN
3229 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newPrimaryContactRegionCode,
3230 'IBU_CF_SR_ACCOUNT_NUMBER',
3231 'Y', 'N', null);
3232
3233 END IF;
3234 l_examineSrAccountOption := FALSE;
3235 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Appl', 'Done with SR Account Option');
3236 ELSIF (l_profileOptionName = 'IBU_A_SR_PRODUCT_SELECTION_OPTION' AND l_examineProdSelectOption) THEN
3237 -- clone the regions
3238 l_newRegProductRegionCode := 'IBU_CF_SR_80_A' || p_ApplId;
3239
3240 -- the region doesn't already exist, so go ahead and create it
3241 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_80_G',
3242 672,
3243 l_newRegProductRegionCode,
3244 672, FALSE);
3245
3246 IF (l_profileOptionValue = 'N') THEN
3247 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newRegProductRegionCode,
3248 'IBU_CF_SR_R_PROD_NAME_LOV',
3249 'Y', 'N', null);
3250
3251 END IF;
3252 l_newAllProductRegionCode := 'IBU_CF_SR_90_A' || p_ApplId;
3253 -- the region doesn't already exist, so go ahead and create it
3254 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_90_G',
3255 672,
3256 l_newAllProductRegionCode,
3257 672, FALSE);
3258
3259 IF (l_profileOptionValue = 'N') THEN
3260 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAllProductRegionCode,
3261 'IBU_CF_SR_PROD_BY_NAME_LOV',
3262 'Y', 'N', null);
3263
3264 END IF;
3265 l_examineProdSelectOption := FALSE;
3266 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Appl', 'Done with SR Product Selection Option');
3267 ELSIF ((l_profileOptionName = 'IBU_A_SR_BILLTO_ADDRESS_OPTION' OR
3268 l_profileOptionName = 'IBU_A_SR_BILLTO_CONTACT_OPTION' OR
3269 l_profileOptionName = 'IBU_A_SR_SHIPTO_ADDRESS_OPTION' OR
3270 l_profileOptionName = 'IBU_A_SR_SHIPTO_CONTACT_OPTION' OR
3271 l_profileOptionName = 'IBU_A_SR_INSTALLEDAT_ADDRESS_OPTION' OR
3272 l_profileOptionName = 'IBU_SR_ADDR_DISPLAY' OR
3273 l_profileOptionName = 'IBU_SR_ADDR_MANDATORY') AND
3274 l_examineAddressOption) THEN
3275
3276 -- we only want to clone the address region once if any of the
3277 -- address profile options are customized.
3278
3279 l_newAddressRegionCode := 'IBU_CF_SR_20_A' || p_ApplId;
3280
3281 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_20_G',
3282 672,
3283 l_newAddressRegionCode,
3284 672,FALSE);
3285
3286 l_newUpdateAddressRegionCode := 'IBU_CF_SR_25_A' || p_ApplId;
3287
3288 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_25_G',
3289 672,
3290 l_newUpdateAddressRegionCode,
3291 672,FALSE);
3295
3292
3293 -- get all the address-specific profile option values
3294 -- for this resp
3296 CS_CF_UPG_UTL_PKG.getAddressProfileValues(p_ProfileTable,
3297 l_displayBillToAddress,
3298 l_displayBillToContact,
3299 l_displayShipToAddress,
3300 l_displayShipToContact,
3301 l_displayInstalledAtAddr,
3302 l_displayIncidentAddr,
3303 l_mandatoryIncidentAddr);
3304
3305 IF (l_displayBillToAddress = 'Y' OR l_displayBillToContact = 'Y') THEN
3306 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3307 'IBU_CF_SR_BILL_TO_HDR',
3308 'Y', 'N', null);
3309
3310 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3311 'IBU_CF_SR_BILL_TO_HDR',
3312 'Y', 'N', null);
3313
3314 END IF;
3315
3316 IF (l_displayBillToAddress = 'Y') THEN
3317 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3318 'IBU_CF_SR_BILL_TO_ADDRESS',
3319 l_displayBillToAddress, 'N', null);
3320
3321 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3322 'IBU_CF_SR_BILL_TO_ADDRESS',
3323 l_displayBillToAddress, 'N', null);
3324
3325 END IF;
3326
3327 IF (l_displayBillToContact = 'Y') THEN
3328 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3329 'IBU_CF_SR_BILL_TO_CONTACT',
3330 l_displayBillToContact, 'N', null);
3331
3332 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3333 'IBU_CF_SR_BILL_TO_CONTACT',
3334 l_displayBillToContact, 'N', null);
3335
3336
3337 END IF;
3338
3339 IF (l_displayShipToAddress = 'Y' OR l_displayShipToContact = 'Y') THEN
3340 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3341 'IBU_CF_SR_SHIP_TO_HDR',
3342 'Y', 'N', null);
3343
3344 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3345 'IBU_CF_SR_SHIP_TO_HDR',
3346 'Y', 'N', null);
3347
3348 END IF;
3349
3350 IF (l_displayShipToAddress = 'Y') THEN
3351 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3352 'IBU_CF_SR_SHIP_TO_ADDRESS',
3353 l_displayShipToAddress, 'N', null);
3354
3358
3355 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3356 'IBU_CF_SR_SHIP_TO_ADDRESS',
3357 l_displayShipToAddress, 'N', null);
3359 END IF;
3360
3361 IF (l_displayShipToContact = 'Y') THEN
3362 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3363 'IBU_CF_SR_SHIP_TO_CONTACT',
3364 l_displayShipToContact, 'N', null);
3365
3366 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3367 'IBU_CF_SR_SHIP_TO_CONTACT',
3368 l_displayShipToContact, 'N', null);
3369
3370 END IF;
3371
3372 IF (l_displayInstalledAtAddr = 'Y') THEN
3373 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3374 'IBU_CF_SR_INSTALLED_AT_HDR',
3375 'Y', 'N', null);
3376
3377 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3378 'IBU_CF_SR_INSTALLED_AT_HDR',
3379 'Y', 'N', null);
3380
3381
3382 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3383 'IBU_CF_SR_INSTALL_AT_ADDR',
3384 l_displayInstalledAtAddr, 'N', null);
3385
3386 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3387 'IBU_CF_SR_INSTALL_AT_ADDR',
3388 l_displayInstalledAtAddr, 'N', null);
3389
3390
3391 END IF;
3392
3393 -- Now we take care of the case for the incident address
3394 -- Note that we do not take care of the case where
3395 -- display addr = N but mandatory addr = Y, because it
3396 -- doesn't make sense
3397 IF (l_displayIncidentAddr = 'Y' AND l_mandatoryIncidentAddr = 'Y') THEN
3398 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3399 'IBU_CF_SR_INCIDENT_ADDRESS_HDR',
3400 'Y', 'Y', null);
3401 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3402 'IBU_CF_SR_ADDRESS',
3403 'Y', 'Y', null);
3404 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3405 'IBU_CF_SR_CITY',
3406 'Y', 'Y', null);
3407 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3408 'IBU_CF_SR_STATE',
3409 'Y', 'Y', null);
3410 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3411 'IBU_CF_SR_PROVINCE',
3412 'Y', 'N', null);
3413 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3414 'IBU_CF_SR_POSTAL_CODE',
3418 'Y', 'Y', null);
3415 'Y', 'N', null);
3416 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3417 'IBU_CF_SR_COUNTRY',
3419 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3420 'IBU_CF_SR_COUNTY',
3421 'Y', 'N', null);
3422 -- we update the update address region, but can ignore the mandatory flag
3423 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3424 'IBU_CF_SR_INCIDENT_ADDRESS_HDR',
3425 'Y', 'N', null);
3426 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3427 'IBU_CF_SR_ADDRESS',
3428 'Y', 'N', null);
3429 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3430 'IBU_CF_SR_CITY',
3431 'Y', 'N', null);
3432 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3433 'IBU_CF_SR_STATE',
3434 'Y', 'N', null);
3435 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3436 'IBU_CF_SR_PROVINCE',
3437 'Y', 'N', null);
3438 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3439 'IBU_CF_SR_POSTAL_CODE',
3440 'Y', 'N', null);
3441 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3442 'IBU_CF_SR_COUNTRY',
3443 'Y', 'N', null);
3444 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3445 'IBU_CF_SR_COUNTY',
3446 'Y', 'N', null);
3447
3448 ELSIF (l_displayIncidentAddr = 'Y' AND l_mandatoryIncidentAddr = 'N') THEN
3449 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3450 'IBU_CF_SR_INCIDENT_ADDRESS_HDR',
3451 'Y', 'N', null);
3452 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3453 'IBU_CF_SR_ADDRESS',
3454 'Y', 'N', null);
3455 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3456 'IBU_CF_SR_CITY',
3457 'Y', 'N', null);
3458 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3459 'IBU_CF_SR_STATE',
3460 'Y', 'N', null);
3461 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3462 'IBU_CF_SR_PROVINCE',
3463 'Y', 'N', null);
3464 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3465 'IBU_CF_SR_POSTAL_CODE',
3466 'Y', 'N', null);
3467 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3468 'IBU_CF_SR_COUNTRY',
3469 'Y', 'N', null);
3470 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3471 'IBU_CF_SR_COUNTY',
3472 'Y', 'N', null);
3473
3474 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3475 'IBU_CF_SR_INCIDENT_ADDRESS_HDR',
3476 'Y', 'N', null);
3477 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3478 'IBU_CF_SR_ADDRESS',
3479 'Y', 'N', null);
3480 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3481 'IBU_CF_SR_CITY',
3482 'Y', 'N', null);
3483 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3484 'IBU_CF_SR_STATE',
3485 'Y', 'N', null);
3486 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3487 'IBU_CF_SR_PROVINCE',
3488 'Y', 'N', null);
3489 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3490 'IBU_CF_SR_POSTAL_CODE',
3491 'Y', 'N', null);
3492 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3493 'IBU_CF_SR_COUNTRY',
3494 'Y', 'N', null);
3495 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateAddressRegionCode,
3496 'IBU_CF_SR_COUNTY',
3497 'Y', 'N', null);
3498
3499 ELSIF (l_displayIncidentAddr = 'N' AND l_mandatoryIncidentAddr = 'Y') THEN
3500 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3501 'IBU_CF_SR_INCIDENT_ADDRESS_HDR',
3502 'N', 'Y', null);
3503 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3504 'IBU_CF_SR_ADDRESS',
3505 'N', 'Y', null);
3506 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3507 'IBU_CF_SR_CITY',
3508 'N', 'Y', null);
3509 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3510 'IBU_CF_SR_STATE',
3511 'N', 'Y', null);
3512 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3513 'IBU_CF_SR_PROVINCE',
3514 'N', 'N', null);
3515 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3516 'IBU_CF_SR_POSTAL_CODE',
3517 'N', 'N', null);
3518 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3519 'IBU_CF_SR_COUNTRY',
3520 'N', 'Y', null);
3521 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newAddressRegionCode,
3522 'IBU_CF_SR_COUNTY',
3523 'N', 'N', null);
3524
3525 END IF;
3526 l_examineAddressOption := FALSE;
3527 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Appl', 'Done with Address Options');
3528
3529 ELSIF (l_profileOptionName = 'IBU_SR_CREATION_PRODUCT_OPTION' AND l_examineCreateProdOption) THEN
3530 -- mkcyee 12/14/2004 - This profile also impacts the product region for
3531 -- Templates, so we need to clone that region as well.
3532
3533 l_newIdentifyProductRegionCode := 'IBU_CF_SR_30_A' || p_ApplId;
3534 l_newTemplateProductRegionCode := 'IBU_CF_SR_35_A' || p_ApplId;
3535
3536 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_30_G',
3537 672,
3538 l_newIdentifyProductRegionCode,
3539 672,FALSE);
3540
3541 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_35_G',
3542 672,
3546 IF (l_profileOptionValue = 'USE_ONLY_INSTALLBASE_PRODUCT') THEN
3543 l_newTemplateProductRegionCode,
3544 672, FALSE);
3545
3547 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProductRegionCode,
3548 'IBU_CF_SR_ALL_PRODUCT_RG',
3549 'N', 'N', null);
3550
3551 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newTemplateProductRegionCode,
3552 'IBU_CF_SR_ALL_PRODUCT_RG',
3553 'N', 'N', null);
3554
3555
3556 ELSIF (l_profileOptionValue = 'USE_ONLY_INVENTORY_PRODUCT') THEN
3557 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProductRegionCode,
3558 'IBU_CF_SR_REG_PRODUCT_RG',
3559 'N', 'N', null);
3560
3561 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newTemplateProductRegionCode,
3562 'IBU_CF_SR_REG_PRODUCT_RG',
3563 'N', 'N', null);
3564 END IF;
3565 l_newProductFilterRegionCode := 'IBU_CF_SR_420_A' || p_ApplId;
3566
3567 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_420_G',
3568 672,
3569 l_newProductFilterRegionCode,
3570 672, FALSE);
3571 IF (l_profileOptionValue = 'USE_ONLY_INSTALLBASE_PRODUCT') THEN
3572 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newProductFilterRegionCode,
3573 'IBU_CF_SR_ALL_PRODUCT_RG',
3574 'N', 'N', null);
3575
3576 ELSIF (l_profileOptionValue = 'USE_ONLY_INVENTORY_PRODUCT') THEN
3577 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newProductFilterRegionCode,
3578 'IBU_CF_SR_REG_PRODUCT_RG',
3579 'N', 'N', null);
3580 END IF;
3581 l_examineCreateProdOption := FALSE;
3582 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Appl', 'Done with Creation Product Option');
3583 ELSIF ((l_profileOptionName = 'IBU_A_SR_ATTACHMENT_OPTION' AND l_examineAttachmentOption) OR (l_profileOptionName = 'IBU_A_SR_PROB_CODE_MANDATORY' and l_examineProbCodeOption)) THEN
3584 l_newIdentifyProblemRegionCode := 'IBU_CF_SR_40_A' || p_ApplId;
3585
3586 CS_CF_UPG_UTL_PKG.getAttachmentProbCodeValues(p_ProfileTable,
3587 l_displayAttachment,
3588 l_mandatoryProblemCode);
3589
3590 IF (l_displayAttachment = 'DONOTSHOW' OR l_displayAttachment = 'SHOWDURINGUPDATE' OR l_mandatoryProblemCode = 'Y') THEN
3591 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_40_G',
3592 672,
3593 l_newIdentifyProblemRegionCode,
3594 672, FALSE);
3595 IF (l_displayAttachment = 'DONOTSHOW') THEN
3596 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProblemRegionCode,
3597 'IBU_CF_SR_ATTACHMENTS_RG',
3598 'N', 'N', null);
3599 ELSIF (l_displayAttachment = 'SHOWDURINGUPDATE') THEN
3600 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProblemRegionCode,
3601 'IBU_CF_SR_ATTACHMENTS_RG',
3602 'N', 'N', null);
3603 ELSIF (l_mandatoryProblemCode = 'Y') THEN
3604 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProblemRegionCode,
3605 'IBU_CF_SR_PROB_TYPE_CODE',
3606 'Y', 'Y', null);
3607
3608 END IF;
3609 END IF;
3610 l_newDtlOverviewRegionCode := 'IBU_CF_SR_130_A' || p_ApplId;
3611 IF (l_displayAttachment = 'DONOTSHOW' OR l_displayAttachment = 'SHOWDURINGCREATE') THEN
3612 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_130_G',
3613 672,
3614 l_newDtlOverviewRegionCode,
3615 672, FALSE);
3616
3617 IF (l_displayAttachment = 'DONOTSHOW') THEN
3618 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlOverviewRegionCode,
3619 'IBU_CF_SR_ATTACHMENTS_RG',
3620 'N', 'N', null);
3621 ELSIF (l_displayAttachment = 'SHOWDURINGCREATE') THEN
3622 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlOverviewRegionCode,
3623 'IBU_CF_SR_ATTACHMENTS_RG',
3624 'N', 'N', null);
3625 END IF;
3626 END IF;
3627 l_examineAttachmentOption := FALSE;
3628 l_examineProbCodeOption := FALSE;
3629 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Appl', 'Done with Attachment Option and Mandatory Problem Code' );
3630 ELSIF (l_profileOptionName = 'IBU_SR_ENABLE_TEMPLATE' AND l_examineTemplateOption) THEN
3631 l_newProblemDetailsRegionCode := 'IBU_CF_SR_60_A' || p_ApplId;
3632 IF (l_profileOptionName = 'N') THEN
3633 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_60_G',
3634 672,
3635 l_newProblemDetailsRegionCode,
3636 672, FALSE);
3637
3638 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newProblemDetailsRegionCode,
3639 'IBU_CF_SR_PROB_DETAILS',
3640 'N', 'N', null);
3641 END IF;
3642 l_examineTemplateOption := FALSE;
3643 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Appl', 'Done with Template Option');
3647 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_110_G',
3644
3645 ELSIF (l_profileOptionName = 'IBU_A_SR_ENABLE_INTERACTION_LOGGING' AND l_examineLoggingOption) THEN
3646 l_newDtlProgOptionsRegionCode := 'IBU_CF_SR_110_A' || p_ApplId;
3648 672,
3649 l_newDtlProgOptionsRegionCode,
3650 672, FALSE);
3651 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlProgOptionsRegionCode,
3652 'IBU_CF_SR_DTL_PROGRESS_INTRCT',
3653 'Y', 'N', null);
3654 l_examineLoggingOption := FALSE;
3655 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Appl', 'Done with Interaction Logging Option');
3656 ELSIF (l_profileOptionName = 'IBU_SR_TASK_DISPLAY' AND l_examineTaskOption) THEN
3657 l_newDtlResolnRegionCode := 'IBU_CF_SR_150_A' || p_ApplId;
3658 IF (l_profileOptionValue = 'Y') THEN
3659 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_150_G',
3660 672,
3661 l_newDtlResolnRegionCode,
3662 672, FALSE);
3663 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlResolnRegionCode,
3664 'IBU_CF_SR_DTL_ACTS_RG',
3665 'Y', 'N', null);
3666 END IF;
3667 l_examineTaskOption := FALSE;
3668 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Regions_For_Appl', 'Done with Task Display Option');
3669 END IF; -- end profile checks
3670 l_index := l_index + 1;
3671 END LOOP; -- end while loop
3672
3673 -- Check whether a region has already been cloned for
3674 -- Primary Contact sub region
3675 l_newPrimaryContactRegionCode := 'IBU_CF_SR_10_A' || p_ApplId;
3676 OPEN does_region_already_exists(l_newPrimaryContactRegionCode, 672);
3677 FETCH does_region_already_exists INTO l_region_count;
3678 CLOSE does_region_already_exists;
3679
3680 IF (l_region_count > 0) THEN
3681 l_newPrimaryContactExists := TRUE;
3682 END IF;
3683
3684 -- Check whether a region has already been cloned for
3685 -- Reg Product sub region
3686 l_newRegProductRegionCode := 'IBU_CF_SR_80_A' || p_ApplId;
3687 OPEN does_region_already_exists(l_newRegProductRegionCode, 672);
3688 FETCH does_region_already_exists INTO l_region_count;
3689 CLOSE does_region_already_exists;
3690
3691 IF (l_region_count > 0) THEN
3692 l_newRegProductExists := TRUE;
3693 END IF;
3694
3695 -- Check whether a region has already been cloned for
3696 -- All Product sub region
3697 l_newAllProductRegionCode := 'IBU_CF_SR_90_A' || p_ApplId;
3701
3698 OPEN does_region_already_exists(l_newAllProductRegionCode, 672);
3699 FETCH does_region_already_exists INTO l_region_count;
3700 CLOSE does_region_already_exists;
3702 IF (l_region_count > 0) THEN
3703 l_newAllProductExists := TRUE;
3704 END IF;
3705
3706 -- Check whether a region has already been cloned for
3707 -- Identify Product sub region
3708 l_newIdentifyProductRegionCode := 'IBU_CF_SR_30_A' || p_ApplId;
3709 OPEN does_region_already_exists(l_newIdentifyProductRegionCode, 672);
3710 FETCH does_region_already_exists INTO l_region_count;
3711 CLOSE does_region_already_exists;
3712
3713 IF (l_region_count > 0) THEN
3714 l_newIdentifyProductExists := TRUE;
3715 select node_display_flag INTO
3716 l_displayRegProducts
3717 from ak_region_items
3718 where region_code = l_newIdentifyProductRegionCode
3719 and attribute_code = 'IBU_CF_SR_REG_PRODUCT_RG'
3720 and region_application_id = 672
3721 and attribute_application_id = 672;
3722
3723 select node_display_flag INTO
3724 l_displayAllProducts
3725 from ak_region_items
3726 where region_code = l_newIdentifyProductRegionCode
3727 and attribute_code = 'IBU_CF_SR_ALL_PRODUCT_RG'
3728 and region_application_id = 672
3729 and attribute_application_id = 672;
3730 END IF;
3731
3732 IF (l_newIdentifyProductExists) THEN
3733 l_newTemplateProductExists := TRUE;
3734 END IF;
3735
3736 -- Check whether a region has been cloned for Product Filter
3737 l_newProductFilterRegionCode := 'IBU_CF_SR_420_A' || p_ApplId;
3738 OPEN does_region_already_exists(l_newProductFilterRegionCode, 672);
3739 FETCH does_region_already_exists INTO l_region_count;
3740 CLOSE does_region_already_exists;
3741
3742 IF (l_region_count > 0) THEN
3743 l_newProductFilterExists := TRUE;
3744 END IF;
3745
3746
3747 -- Check whether a region has been cloned for addresses
3748 l_newAddressRegionCode := 'IBU_CF_SR_20_A' || p_ApplId;
3749 OPEN does_region_already_exists(l_newAddressRegionCode, 672);
3750 FETCH does_region_already_exists INTO l_region_count;
3751 CLOSE does_region_already_exists;
3752
3753 IF (l_region_count > 0) THEN
3754 l_newAddressExists := TRUE;
3755 END IF;
3756
3757 l_newUpdateAddressRegionCode := 'IBU_CF_SR_25_A' || p_ApplId;
3758 OPEN does_region_already_exists(l_newUpdateAddressRegionCode, 672);
3759 FETCH does_region_already_exists INTO l_region_count;
3760 CLOSE does_region_already_exists;
3761
3762 IF (l_region_count > 0) THEN
3763 l_newUpdateAddressExists := TRUE;
3764 END IF;
3765
3766 -- Check cloned subregions for Create Service Request flows
3767
3768 -- Check whether a region has already been
3769 -- cloned for Identify Problem
3770 l_newIdentifyProblemRegionCode := 'IBU_CF_SR_40_A' || p_ApplId;
3771 OPEN does_region_already_exists(l_newIdentifyProblemRegionCode, 672);
3772 FETCH does_region_already_exists INTO l_region_count;
3773 CLOSE does_region_already_exists;
3774
3775 IF (l_region_count > 0) THEN
3776 l_newIdentifyProblemExists := TRUE;
3777
3778 select node_display_flag INTO
3779 l_displayAttachment
3780 from ak_region_items
3781 where region_code = l_newIdentifyProblemRegionCode
3782 and attribute_code = 'IBU_CF_SR_ATTACHMENTS_RG'
3783 and region_application_id = 672
3784 and attribute_application_id = 672;
3785
3786 ELSIF (l_newPrimaryContactExists OR l_newIdentifyProductExists OR l_newAddressExists) THEN
3787 -- mkcyee 12/07/04 - new Address region has been added to Identify
3788 -- Problem page, so we need to clone this region is Address region
3789 -- has been cloned
3790
3791 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_40_G',
3792 672,
3793 l_newIdentifyProblemRegionCode,
3794 672, FALSE);
3795 l_newIdentifyProblemExists := TRUE;
3796 END IF;
3797
3798 -- Check whether a region has already been cloned for
3799 -- Problem Details
3800 l_newProblemDetailsRegionCode := 'IBU_CF_SR_60_A' || p_ApplId;
3801 OPEN does_region_already_exists(l_newProblemDetailsRegionCode, 672);
3802 FETCH does_region_already_exists INTO l_region_count;
3803 CLOSE does_region_already_exists;
3804
3805 IF (l_region_count > 0) THEN
3806 l_newProblemDetailsExists := TRUE;
3807
3808 select node_display_flag INTO
3809 l_enableTemplate
3810 from ak_region_items
3811 where region_code = l_newProblemDetailsRegionCode
3812 and attribute_code = 'IBU_CF_SR_PROB_DETAILS'
3813 and region_application_id = 672
3814 and attribute_application_id = 672;
3815 ELSIF (l_newPrimaryContactExists OR l_newAddressExists) THEN
3816 -- mkcyee 12/07/04 - new Address region has been added to Problem
3817 -- details page, so we need to clone this region is Address region
3818 -- has been cloned
3819 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_60_G',
3820 672,
3821 l_newProblemDetailsRegionCode,
3822 672, FALSE);
3823 l_newProblemDetailsExists := TRUE;
3824 END IF;
3825
3826
3827 -- Check whether a region has already been cloned for
3828 -- Update Overview sub region
3829 l_newDtlOverviewRegionCode := 'IBU_CF_SR_130_A' || p_ApplId;
3830 OPEN does_region_already_exists(l_newDtlOverviewRegionCode, 672);
3831 FETCH does_region_already_exists INTO l_region_count;
3832 CLOSE does_region_already_exists;
3833
3834 IF (l_region_count > 0) THEN
3835 l_newDtlOverviewExists := TRUE;
3836 END IF;
3837
3838
3839 -- Check whether a region has been cloned for Contact Info
3840 l_newContactInfoRegionCode := 'IBU_CF_SR_70_A' || p_ApplId;
3841 IF (l_newPrimaryContactExists OR l_newAddressExists) THEN
3842 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_70_G',
3846 l_newContactInfoExists := TRUE;
3843 672,
3844 l_newContactInfoRegionCode,
3845 672, FALSE);
3847 END IF;
3848
3849 l_newReviewRegionCode := 'IBU_CF_SR_50_A' || p_ApplId;
3850 IF (l_newAddressExists OR l_enableTemplate = 'N' OR
3851 l_displayAttachment='N') THEN
3852 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_50_G',
3853 672,
3854 l_newReviewRegionCode,
3855 672, FALSE);
3856
3857 l_newReviewExists := TRUE;
3858 END IF;
3859
3860 -- Check whether a region has been cloned for progress options
3861 l_newDtlProgOptionsRegionCode := 'IBU_CF_SR_110_A' || p_ApplId;
3862 OPEN does_region_already_exists(l_newDtlProgOptionsRegionCode, 672);
3863 FETCH does_region_already_exists INTO l_region_count;
3864 CLOSE does_region_already_exists;
3865
3866 IF (l_region_count > 0) THEN
3867 l_newDtlProgressOptionsExists := TRUE;
3868 END IF;
3869
3870 -- Check whether a region has been cloned for IBU_CF_SR_DTL_RESOLN
3871 l_newDtlResolnRegionCode := 'IBU_CF_SR_150_A' || p_ApplId;
3872 OPEN does_region_already_exists(l_newDtlResolnRegionCode, 672);
3873 FETCH does_region_already_exists INTO l_region_count;
3874 CLOSE does_region_already_exists;
3875
3876 IF (l_region_count > 0) THEN
3877 l_newDtlResolnExists := TRUE;
3878 select node_display_flag INTO
3879 l_displayTasks
3880 from ak_region_items
3881 where region_code = l_newDtlResolnRegionCode
3882 and attribute_code = 'IBU_CF_SR_DTL_ACTS_RG'
3883 and region_application_id = 672
3884 and attribute_application_id = 672;
3885 END IF;
3886
3887 -- If regions that were created impact other regions,
3888 -- make sure to set them to the proper region code.
3889 -- For some cases, we may have to clone new regions
3890
3891
3892 IF (l_newAllProductExists AND l_newRegProductExists) THEN
3893 IF NOT(l_newIdentifyProductExists) THEN
3894 -- need to clone the IBU_CF_SR_IDENTIFY_PRODUCT region
3895 l_newIdentifyProductRegionCode := 'IBU_CF_SR_30_A' || p_ApplId;
3896 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_30_G',
3897 672,
3898 l_newIdentifyProductRegionCode,
3899 672, FALSE);
3900 l_newIdentifyProductExists := TRUE;
3901 IF (l_displayRegProducts = '') THEN
3902 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProductRegionCode,
3903 'IBU_CF_SR_REG_PRODUCT_RG',
3904 'Y', 'N', l_newRegProductRegionCode);
3905 ELSE
3906 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProductRegionCode,
3907 'IBU_CF_SR_REG_PRODUCT_RG',
3908 l_displayRegProducts, 'N', l_newRegProductRegionCode);
3909 END IF;
3910 IF (l_displayAllProducts = '') THEN
3911 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProductRegionCode,
3912 'IBU_CF_SR_ALL_PRODUCT_RG',
3913 'Y', 'N', l_newAllProductRegionCode);
3914 ELSE
3915 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProductRegionCode,
3916 'IBU_CF_SR_ALL_PRODUCT_RG',
3917 l_displayAllProducts, 'N', l_newAllProductRegionCode);
3918 END IF;
3919 END IF;
3920 END IF;
3921 IF (l_displayRegProducts = 'N' OR l_displayAllProducts = 'N') THEN
3922 IF NOT(l_newTemplateProductExists) THEN
3923 -- need to clone the IBU_CF_SR_IDENTIFY_PRODUCT region for Templates
3924 l_newTemplateProductRegionCode := 'IBU_CF_SR_35_A' || p_ApplId;
3925 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_35_G',
3926 672,
3927 l_newTemplateProductRegionCode,
3928 672, FALSE);
3929 l_newTemplateProductExists := TRUE;
3930 IF (l_displayRegProducts = 'N') THEN
3931 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newTemplateProductRegionCode,
3932 'IBU_CF_SR_REG_PRODUCT_RG',
3933 'N', 'N', 'IBU_CF_SR_REG_PRODUCT');
3934 END IF;
3935 IF (l_displayAllProducts = 'N') THEN
3936 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newTemplateProductRegionCode,
3937 'IBU_CF_SR_ALL_PRODUCT_RG',
3938 'N', 'N', 'IBU_CF_SR_ALL_PRODUCT');
3939 END IF;
3940 END IF;
3941 IF NOT(l_newProductFilterExists) THEN
3942 -- need to clone the IBU_CF_SR_VW_PRODUCT_FILTER region
3943 l_newProductFilterRegionCode := 'IBU_CF_SR_420_A' || p_ApplId;
3944 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_420_G',
3945 672,
3946 l_newProductFilterRegionCode,
3947 672, FALSE);
3948 l_newProductFilterExists := TRUE;
3949 IF (l_displayRegProducts = 'N') THEN
3950 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newProductFilterRegionCode,
3951 'IBU_CF_SR_REG_PRODUCT_RG',
3952 'N', 'N', 'IBU_CF_SR_REG_PRODUCT');
3953 END IF;
3954 IF (l_displayAllProducts = 'N') THEN
3955 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newProductFilterRegionCode,
3956 'IBU_CF_SR_ALL_PRODUCT_RG',
3957 'N', 'N', 'IBU_CF_SR_ALL_PRODUCT');
3958 END IF;
3959 END IF;
3960 END IF;
3961
3962 IF (l_newIdentifyProblemExists) THEN
3963 IF (l_newPrimaryContactExists) THEN
3964 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProblemRegionCode,
3968 IF (l_newIdentifyProductExists) THEN
3965 'IBU_CF_SR_PRIMARY_CONTACT_RG',
3966 'Y', 'N', l_newPrimaryContactRegionCode);
3967 END IF;
3969 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProblemRegionCode,
3970 'IBU_CF_SR_IDENTIFY_PRODUCT_RG',
3971 'Y', 'N', l_newIdentifyProductRegionCode);
3972
3973 END IF;
3974 IF (l_newAddressExists) THEN
3975 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProblemRegionCode,
3976 'IBU_CF_SR_ADDRESS_RG',
3977 'N', 'N', l_newAddressRegionCode);
3978 END IF;
3979 END IF;
3980
3981 IF (l_newProblemDetailsExists) THEN
3982 IF (l_newPrimaryContactExists) THEN
3983 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newProblemDetailsRegionCode,
3984 'IBU_CF_SR_PRIMARY_CONTACT_RG',
3985 'Y', 'N', l_newPrimaryContactRegionCode);
3986 END IF;
3987 IF (l_displayAttachment = 'N') THEN
3988 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newIdentifyProblemRegionCode,
3989 'IBU_CF_SR_ATTACHMENTS_RG',
3990 'N', 'N', null);
3991 END IF;
3992
3993 -- mkcyee 12/07/2004 - the address region have been added in
3994 -- problem details and identify problem pages
3995 IF (l_newAddressExists) THEN
3996 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newProblemDetailsRegionCode,
3997 'IBU_CF_SR_ADDRESS_RG',
3998 'N', 'N', l_newAddressRegionCode);
3999 END IF;
4000 END IF;
4001
4002
4003 IF (l_newReviewExists) THEN
4004 IF (l_displayAttachment = 'N') THEN
4005 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newReviewRegionCode,
4006 'IBU_CF_SR_ATTACHMENTS_RG',
4007 'N', 'N', null);
4008 END IF;
4009 IF (l_newAddressExists) THEN
4010 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newReviewRegionCode,
4011 'IBU_CF_SR_ADDRESS_RG',
4012 'Y', 'N', l_newAddressRegionCode);
4013 END IF;
4014 IF (l_enableTemplate = 'N') THEN
4015 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newReviewRegionCode,
4016 'IBU_CF_SR_PROB_DETAILS',
4017 'N', 'N', null);
4018 END IF;
4019 END IF;
4020
4021 IF (l_newContactInfoExists) THEN
4022 IF (l_newAddressExists) THEN
4023 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newContactInfoRegionCode,
4024 'IBU_CF_SR_ADDRESS_RG',
4025 'Y', 'N', l_newAddressRegionCode);
4026 END IF;
4027 IF (l_newPrimaryContactExists) THEN
4028 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newContactInfoRegionCode,
4029 'IBU_CF_SR_PRIMARY_CONTACT_RG',
4030 'Y', 'N', l_newPrimaryContactRegionCode);
4031 END IF;
4032 END IF;
4033 --For update service request flows, clone required regions
4034 IF (l_newUpdateAddressExists) THEN
4035
4036 -- need to clone the IBU_CF_SR_DTL_CONTACT region
4037 l_newDtlContactAddrRegionCode := 'IBU_CF_SR_210_A' || p_ApplId;
4038 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_210_G',
4039 672,
4040 l_newDtlContactAddrRegionCode,
4041 672, FALSE);
4042 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlContactAddrRegionCode,
4043 'IBU_CF_SR_ADDRESS_RG',
4044 'Y', 'N', l_newUpdateAddressRegionCode);
4045
4046 l_newDtlContactAddrExists := TRUE;
4047 END IF;
4048
4049 IF (l_newDtlProgressOptionsExists) THEN
4050 -- We must clone IBU_CF_SR_DTL_PROGRESS
4051 l_newDtlProgressRegionCode := 'IBU_CF_SR_120_A' || p_ApplId;
4052 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_120_G',
4053 672,
4054 l_newDtlProgressRegionCode,
4055 672, FALSE);
4056 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlProgressRegionCode,
4057 'IBU_CF_SR_DTL_PROGRESS_OPT_RG',
4058 'Y', 'N', l_newDtlProgOptionsRegionCode);
4059 l_newDtlProgressExists := TRUE;
4060 END IF;
4061
4062 IF (l_newDtlProgressExists OR l_newDtlResolnExists) THEN
4063 -- if any of these regions are cloned, then we
4064 -- must clone IBU_CF_SR_DTL_OVERVIEW
4065 l_newDtlOverviewRegionCode := 'IBU_CF_SR_130_A' || p_ApplId;
4066
4067 IF NOT (l_newDtlOverviewExists) THEN
4068 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_130_G',
4069 672,
4070 l_newDtlOverviewRegionCode,
4071 672, FALSE);
4072 END IF;
4073 IF (l_newUpdateAddressExists) THEN
4074 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlOverviewRegionCode,
4075 'IBU_CF_SR_ADDRESS_RG',
4076 'N', 'N', l_newUpdateAddressRegionCode);
4077 END IF;
4078 IF (l_newDtlProgressExists) THEN
4079 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlOverviewRegionCode,
4080 'IBU_CF_SR_DTL_PROGRESS_RG',
4081 'Y', 'N', l_newDtlProgressRegionCode);
4082 END IF;
4083 IF (l_newDtlResolnExists AND l_displayTasks = 'Y') THEN
4084 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlOverviewRegionCode,
4085 'IBU_CF_SR_DTL_ACTS_RG',
4086 'Y', 'N', null);
4087 END IF;
4088 l_newDtlOverviewExists := TRUE;
4089 END IF;
4090
4091
4092 IF (l_newDtlOverviewExists OR l_newDtlResolnExists) THEN
4093 -- then we must clone IBU_CF_SR_DTL_TABS
4094 l_newDtlTabsRegionCode := 'IBU_CF_SR_160_A' || p_ApplId;
4095 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_160_G',
4096 672,
4097 l_newDtlTabsRegionCode,
4098 672, FALSE);
4099 IF (l_newDtlOverviewExists) THEN
4100 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlTabsRegionCode,
4101 'IBU_CF_SR_DTL_OVERVIEW_TAB_RG',
4102 'Y', 'N', l_newDtlOverviewRegionCode);
4103 END IF;
4104 IF (l_newDtlResolnExists) THEN
4108 END IF;
4105 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlTabsRegionCode,
4106 'IBU_CF_SR_DTL_RESOLN_TAB_RG',
4107 'N', 'N', l_newDtlResolnRegionCode);
4109 l_newDtlTabsExists := TRUE;
4110 END IF;
4111
4112 IF (l_newDtlTabsExists) THEN
4113 -- then we must clone IBU_CF_SR_DETAILS
4114 l_newDtlDetailsRegionCode := 'IBU_CF_SR_190_A' || p_ApplId;
4115 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_190_G',
4116 672,
4117 l_newDtlDetailsRegionCode,
4118 672, FALSE);
4119 IF (l_newDtlDetailsExists) THEN
4120 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newDtlDetailsRegionCode,
4121 'IBU_CF_SR_DTL_TABS_RG',
4122 'Y', 'N', l_newDtlTabsRegionCode);
4123 END IF;
4124 l_newDtlDetailsExists := TRUE;
4125 END IF;
4126
4127 -- Now check for the Templates
4128
4129 -- mkcyee 12/14/2004 - use l_newTemplateProductExists
4130 -- because Templates and Search now have a separate product section
4131
4132 IF (l_newTemplateProductExists) THEN
4133 l_newCreateTemplateRegionCode := 'IBU_CF_SR_310_A' || p_ApplId;
4134 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_310_G',
4135 672,
4136 l_newCreateTemplateRegionCode,
4137 672, FALSE);
4138 --IF (l_newPrimaryContactExists) THEN
4139 -- CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newCreateTemplateRegionCode,
4140 -- 'IBU_CF_SR_PRIMARY_CONTACT_RG',
4141 -- 'Y', 'N', l_newPrimaryContactRegionCode);
4142 --END IF;
4143 --IF (l_newTemplateProductExists) THEN
4144 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newCreateTemplateRegionCode,
4145 'IBU_CF_SR_IDENTIFY_PRODUCT_RG',
4146 'Y', 'N', l_newTemplateProductRegionCode);
4147 --END IF;
4148 l_newCreateTemplateExists := TRUE;
4149 l_newUpdateTemplateRegionCode := 'IBU_CF_SR_320_A' || p_ApplId;
4150 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_320_G',
4151 672,
4152 l_newUpdateTemplateRegionCode,
4153 672, FALSE);
4154 --IF (l_newPrimaryContactExists) THEN
4155 -- CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateTemplateRegionCode,
4156 -- 'IBU_CF_SR_PRIMARY_CONTACT_RG',
4157 -- 'Y', 'N', l_newPrimaryContactRegionCode);
4158 --END IF;
4159 --IF (l_newTemplateProductExists) THEN
4160 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateTemplateRegionCode,
4161 'IBU_CF_SR_IDENTIFY_PRODUCT_RG',
4162 'Y', 'N', l_newTemplateProductRegionCode);
4163 --END IF;
4164 l_newUpdateTemplateExists := TRUE;
4165 END IF;
4166
4167
4168 IF (l_newProductFilterExists) THEN
4169 -- need to clone IBU_CF_SR_VW_FILTER
4170 l_newFilterRegionCode := 'IBU_CF_SR_410_A' || p_ApplId;
4171 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_410_G',
4172 672,
4173 l_newFilterRegionCode,
4174 672, FALSE);
4175 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newFilterRegionCode,
4176 'IBU_CF_SR_VW_PRODUCT_FILTER_RG',
4177 'Y', 'N', l_newProductFilterRegionCode);
4178
4179 l_newFilterExists := TRUE;
4180 END IF;
4181
4182 IF (l_newFilterExists) THEN
4183 -- need to clone IBU_CF_SR_VW_SEARCH, IBU_CF_SR_VW_CREATE, IBU_CF_SR_VW_UPDATE
4184 l_newSearchViewRegionCode := 'IBU_CF_SR_450_A' || p_ApplId;
4185 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_450_G',
4186 672,
4187 l_newSearchViewRegionCode,
4188 672, FALSE);
4189 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newSearchViewRegionCode,
4190 'IBU_CF_SR_VW_FILTER_RG',
4191 'Y', 'N', l_newFilterRegionCode);
4192
4193 l_newSearchViewExists := TRUE;
4194
4195 l_newCreateViewRegionCode := 'IBU_CF_SR_430_A' || p_ApplId;
4196 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_430_G',
4197 672,
4198 l_newCreateViewRegionCode,
4199 672, FALSE);
4200 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newCreateViewRegionCode,
4201 'IBU_CF_SR_VW_FILTER_RG',
4202 'Y', 'N', l_newFilterRegionCode);
4203
4204 l_newCreateViewExists := TRUE;
4205
4206 l_newUpdateViewRegionCode := 'IBU_CF_SR_440_A' || p_ApplId;
4207 CS_CF_UPG_UTL_PKG.Clone_Region('IBU_CF_SR_440_G',
4208 672,
4209 l_newUpdateViewRegionCode,
4210 672, FALSE);
4211 CS_CF_UPG_UTL_PKG.UpdateRegionItems(l_newUpdateViewRegionCode,
4212 'IBU_CF_SR_VW_FILTER_RG',
4213 'Y', 'N', l_newFilterRegionCode);
4214
4215 l_newUpdateViewExists := TRUE;
4216 END IF;
4217
4218 -- Now enter the rows in the cs_cf_source_cxt_targets table
4219 -- for this responsibility
4220
4221 IF (l_newIdentifyProblemExists ) THEN
4222 CS_CF_UPG_UTL_PKG.Insert_New_Target(
4223 'IBU_SR_CR_IDENTIFY_PROBLEM',
4224 'APPLICATION',
4225 l_ApplId,
4226 NULL,
4227 NULL,
4228 NULL,
4229 l_newIdentifyProblemRegionCode,
4230 '672');
4231 END IF;
4232
4233 IF (l_newReviewExists) THEN
4234 CS_CF_UPG_UTL_PKG.Insert_New_Target(
4235 'IBU_SR_CR_REVIEW',
4236 'APPLICATION',
4237 l_ApplId,
4238 NULL,
4239 NULL,
4240 NULL,
4241 l_newReviewRegionCode,
4242 '672');
4243 END IF;
4244
4245 IF (l_newProblemDetailsExists) THEN
4246 CS_CF_UPG_UTL_PKG.Insert_New_Target(
4247 'IBU_SR_CR_PROBLEM_DETAILS',
4248 'APPLICATION',
4249 l_ApplId,
4250 NULL,
4251 NULL,
4252 NULL,
4253 l_newProblemDetailsRegionCode,
4254 '672');
4255 END IF;
4256
4257
4258 IF (l_newContactInfoExists) THEN
4259 CS_CF_UPG_UTL_PKG.Insert_New_Target(
4263 NULL,
4260 'IBU_SR_CR_CONTACT_INFORMATION',
4261 'APPLICATION',
4262 l_ApplId,
4264 NULL,
4265 NULL,
4266 l_newContactInfoRegionCode,
4267 '672');
4268 END IF;
4269
4270 IF (l_newDtlDetailsExists) THEN
4271 CS_CF_UPG_UTL_PKG.Insert_New_Target(
4272 'IBU_SR_DETAILS',
4273 'APPLICATION',
4274 l_ApplId,
4275 NULL,
4276 NULL,
4277 NULL,
4278 l_newDtlDetailsRegionCode,
4279 '672');
4280 END IF;
4281
4282 IF (l_newCreateTemplateExists) THEN
4283 CS_CF_UPG_UTL_PKG.Insert_New_Target(
4284 'IBU_SR_TEMP_CREATE',
4285 'APPLICATION',
4286 l_ApplId,
4287 NULL,
4288 NULL,
4289 NULL,
4290 l_newCreateTemplateRegionCode,
4291 '672');
4292 END IF;
4293
4294 IF (l_newUpdateTemplateExists) THEN
4295 CS_CF_UPG_UTL_PKG.Insert_New_Target(
4296 'IBU_SR_TEMP_UPDATE',
4297 'APPLICATION',
4298 l_ApplId,
4299 NULL,
4300 NULL,
4301 NULL,
4302 l_newUpdateTemplateRegionCode,
4303 '672');
4304 END IF;
4305
4306 IF (l_newSearchViewExists) THEN
4307 CS_CF_UPG_UTL_PKG.Insert_New_Target(
4308 'IBU_SR_VIEW_SUMMARY',
4309 'APPLICATION',
4310 l_ApplId,
4311 NULL,
4312 NULL,
4313 NULL,
4314 l_newSearchViewRegionCode,
4315 '672');
4316 END IF;
4317
4318 IF (l_newCreateViewExists) THEN
4319 CS_CF_UPG_UTL_PKG.Insert_New_Target(
4320 'IBU_SR_VIEW_CREATE',
4321 'APPLICATION',
4322 l_ApplId,
4323 NULL,
4324 NULL,
4325 NULL,
4326 l_newCreateViewRegionCode,
4327 '672');
4328 END IF;
4329
4330 IF (l_newUpdateViewExists) THEN
4331 CS_CF_UPG_UTL_PKG.Insert_New_Target(
4332 'IBU_SR_VIEW_UPDATE',
4333 'APPLICATION',
4334 l_ApplId,
4335 NULL,
4336 NULL,
4337 NULL,
4338 l_newUpdateViewRegionCode,
4339 '672');
4340 END IF;
4341
4342
4343 END Clone_Regions_For_Appl;
4344
4345 /*
4346 * Top level procedure for performing flow
4347 * upgrades; Internally this will call
4348 * flow upgrades for each profile level, ie resp, application, etc
4349 */
4350 PROCEDURE Do_Flow_Upgrade(p_respTable IN CS_CF_UPG_UTL_PKG.RespTable,
4351 p_applTable IN CS_CF_UPG_UTL_PKG.ApplTable,
4352 p_siteProfilesTable IN CS_CF_UPG_UTL_PKG.ProfileTable)
4353 IS
4354
4355 BEGIN
4356
4357 Do_Flow_Upgrades_For_Resp(p_respTable);
4358 Do_Flow_Upgrades_For_Appl(p_applTable);
4359 Do_Flow_Upgrades_For_Global(p_siteProfilesTable);
4360
4361 END Do_Flow_Upgrade;
4362
4363 /*
4364 * Procedure to performing flow upgrades for responsibility level
4365 * For each resp
4366 */
4367 PROCEDURE Do_Flow_Upgrades_For_Resp(p_respTable IN CS_CF_UPG_UTL_PKG.RespTable)
4368 IS
4369
4370 -- this picks up all the configuration profiles
4371 -- that affects region configuration for
4372 -- a particular responsibility and it's corresponding
4373 -- higher application level and site level
4374
4375 CURSOR get_profiles_for_resp (respId NUMBER, respApplId NUMBER)
4376 IS
4377 SELECT a.profile_option_name,
4378 b.level_value,
4379 b.level_value_application_id,
4380 b.profile_option_value,
4381 1 "PRIORITY"
4382 FROM fnd_profile_option_values b, fnd_profile_options a
4383 WHERE b.level_id = 10003
4384 AND b.level_value = respId
4385 AND b.level_value_application_id = respApplId
4386 AND a.profile_option_id = b.profile_option_id
4387 AND b.application_id = 672
4388 AND a.profile_option_name in ('IBU_A_SR_KB_OPTION')
4389 UNION
4390 SELECT a.profile_option_name,
4391 b.level_value,
4392 b.level_value_application_id,
4393 b.profile_option_value,
4394 2 "PRIORITY"
4395 FROM fnd_profile_option_values b, fnd_profile_options a
4396 WHERE b.level_id = 10002
4397 AND b.level_value = respApplId
4398 AND a.profile_option_id = b.profile_option_id
4399 AND b.application_id = 672
4400 AND a.profile_option_name in ('IBU_A_SR_KB_OPTION')
4401 UNION
4402 SELECT a.profile_option_name,
4403 b.level_value,
4404 b.level_value_application_id,
4405 b.profile_option_value,
4406 3 "PRIORITY"
4407 FROM fnd_profile_option_values b, fnd_profile_options a
4408 WHERE b.level_id = 10001
4409 AND a.profile_option_id = b.profile_option_id
4410 AND b.application_id = 672
4411 AND a.profile_option_name in ('IBU_A_SR_KB_OPTION')
4412 ORDER BY PROFILE_OPTION_NAME, PRIORITY;
4413
4414 l_count NUMBER := p_respTable.COUNT;
4415 l_index NUMBER := 0;
4416 l_index2 NUMBER := 0;
4417 l_profile_option_name FND_PROFILE_OPTIONS.profile_option_name%TYPE;
4418 l_level_value FND_PROFILE_OPTION_VALUES.level_value%TYPE;
4419 l_level_value_application_id FND_PROFILE_OPTION_VALUES.level_value_application_id%TYPE;
4420 l_profile_option_value FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE;
4421 l_priority NUMBER := 0;
4422
4423 l_ProfileTable CS_CF_UPG_UTL_PKG.ProfileTable;
4424
4425 BEGIN
4426
4427 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG: Do_Flow_Upgrades_For_Resp', 'Processing Do_Flow_Upgrades_For_Resp');
4428
4429
4430 WHILE (l_index < l_count) LOOP
4431 -- Get the list of profile names and their values
4432 -- and store it in a table
4433 -- This table may contain duplicate entries for
4434 -- each profile because we pick up the values
4435 -- for the application and site level also
4439 l_level_value,
4436 OPEN get_profiles_for_resp(p_respTable(l_index).respId, p_respTable(l_index).respApplId);
4437 -- Retrieve information into the local variables
4438 FETCH get_profiles_for_resp INTO l_profile_option_name,
4440 l_level_value_application_id,
4441 l_profile_option_value,
4442 l_priority;
4443 WHILE get_profiles_for_resp%FOUND LOOP
4444
4445 l_ProfileTable(l_index2).profileOptionName := l_profile_option_name;
4446 l_ProfileTable(l_index2).profileOptionValue := l_profile_option_value;
4447
4448 l_index2 := l_index2 + 1;
4449
4450 FETCH get_profiles_for_resp INTO l_profile_option_name,
4451 l_level_value,
4452 l_level_value_application_id,
4453 l_profile_option_value,
4454 l_priority;
4455
4456 END LOOP;
4457 CLOSE get_profiles_for_resp;
4458
4459 -- Now we have a table of profiles
4460 -- Determine which flows need to be cloned for this resp
4461
4462 IF (CS_CF_UPG_UTL_PKG.Flows_Not_Already_Cloned(to_number(p_respTable(l_index).respId || p_respTable(l_index).respApplId))) THEN
4463
4464 Clone_Flows_For_Resp(l_ProfileTable, p_respTable(l_index).respId,
4465 p_respTable(l_index).respApplId);
4466 END IF;
4467
4468 commit;
4469 -- now clean up the table so it can be reused
4470 l_ProfileTable.DELETE;
4471 l_index2 := 0;
4472 l_index := l_index + 1;
4473
4474 END LOOP; -- ends while loop
4475
4476 EXCEPTION
4477 WHEN OTHERS THEN
4478 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_PKG: Do_Flow_Upgrades_For_Resp', 'Exception in Do_Flow_Upgrades_For_Resp');
4479 IF (get_profiles_for_resp%ISOPEN) THEN
4480 CLOSE get_profiles_for_resp;
4481 END IF;
4482 RAISE;
4483
4484 END Do_Flow_Upgrades_For_Resp;
4485
4486 /*
4487 * Perform the actually cloning
4488 * of flows, based on the list of
4489 * profiles that are customized at the resp level
4490 */
4491 PROCEDURE Clone_Flows_For_Resp(p_ProfileTable IN CS_CF_UPG_UTL_PKG.ProfileTable,
4492 p_respId IN FND_PROFILE_OPTION_VALUES.level_value%TYPE,
4493 p_respApplId IN FND_PROFILE_OPTION_VALUES.level_value_application_id%TYPE)
4494 IS
4495 l_count NUMBER := p_ProfileTable.COUNT;
4496 l_index NUMBER := 0;
4497 l_profileOptionName FND_PROFILE_OPTIONS.profile_option_name%TYPE;
4498 l_profileOptionValue FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE;
4499
4500 l_respId VARCHAR2(10) := to_char(p_respId);
4501 l_respApplId VARCHAR2(10) := to_char(p_respApplId);
4502
4503
4504 -- the following set of variables are used to
4505 -- determine whether we've already examined
4506 -- the profile option. If we have, we don't need to look at it again
4507 l_examineKBOption BOOLEAN := TRUE;
4508
4509 BEGIN
4510
4511 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Flows_For_Resp', 'Setting flow for respId: ' || l_respId || ' respApplId: ' || l_respApplId);
4512 WHILE (l_index < l_count) LOOP
4513 l_profileOptionName := p_ProfileTable(l_index).profileOptionName;
4514 l_profileOptionValue := p_ProfileTable(l_index).profileOptionValue;
4515
4516 IF (l_profileOptionName = 'IBU_A_SR_KB_OPTION' AND l_examineKBOption) THEN
4517 IF (l_profileOptionValue = 'SEARCH') THEN
4518 -- this resp will use the default flow seeded for 11510
4519 CS_CF_UPG_UTL_PKG.Clone_Flow(to_number(l_respId || l_respApplId), 10);
4520 ELSE
4521 -- this resp will use default flow seeded for 1159
4522 CS_CF_UPG_UTL_PKG.Clone_Flow(to_number(l_respId || l_respApplId), 20);
4523
4524 END IF;
4525
4526 CS_CF_UPG_UTL_PKG.Insert_New_Target('IBU_SR_CRE',
4527 'RESP',
4528 l_respId,
4529 l_respApplId,
4530 NULL,
4531 NULL,
4532 to_char((0-to_number(l_respId || l_respApplId))),
4533 'NULL');
4534
4535 l_examineKBOption := FALSE;
4536 END IF;
4537
4538 l_index := l_index + 1;
4539 END LOOP;
4540 End Clone_Flows_For_Resp;
4541
4542 /*
4543 * Procedure to performing flow upgrades for application level
4544 */
4545 PROCEDURE Do_Flow_Upgrades_For_Appl(p_applTable IN CS_CF_UPG_UTL_PKG.ApplTable)
4546 IS
4547
4548 -- this picks up all the configuration profiles
4549 -- that affects region configuration for
4550 -- a particular application and site level
4551
4552 CURSOR get_profiles_for_appl (applId NUMBER)
4553 IS
4554 SELECT a.profile_option_name,
4555 b.level_value,
4556 b.level_value_application_id,
4557 b.profile_option_value,
4558 1 "PRIORITY"
4559 FROM fnd_profile_option_values b, fnd_profile_options a
4560 WHERE b.level_id = 10002
4561 AND b.level_value = applId
4562 AND a.profile_option_id = b.profile_option_id
4563 AND b.application_id = 672
4564 AND a.profile_option_name in ('IBU_A_SR_KB_OPTION')
4565 UNION
4566 SELECT a.profile_option_name,
4567 b.level_value,
4568 b.level_value_application_id,
4569 b.profile_option_value,
4570 2 "PRIORITY"
4571 FROM fnd_profile_option_values b, fnd_profile_options a
4572 WHERE b.level_id = 10001
4573 AND a.profile_option_id = b.profile_option_id
4574 AND b.application_id = 672
4575 AND a.profile_option_name in ('IBU_A_SR_KB_OPTION')
4576 ORDER BY PROFILE_OPTION_NAME, PRIORITY;
4577
4578 l_count NUMBER := p_applTable.COUNT;
4579 l_index NUMBER := 0;
4580 l_index2 NUMBER := 0;
4584 l_profile_option_value FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE;
4581 l_profile_option_name FND_PROFILE_OPTIONS.profile_option_name%TYPE;
4582 l_level_value FND_PROFILE_OPTION_VALUES.level_value%TYPE;
4583 l_level_value_application_id FND_PROFILE_OPTION_VALUES.level_value_application_id%TYPE;
4585 l_priority NUMBER := 0;
4586
4587 l_ProfileTable CS_CF_UPG_UTL_PKG.ProfileTable;
4588
4589 BEGIN
4590
4591 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG: Do_Flow_Upgrades_For_Appl', 'Processing Do_Flow_Upgrades_For_Appl');
4592
4593
4594 WHILE (l_index < l_count) LOOP
4595 -- Get the list of profile names and their values
4596 -- and store it in a table
4597 -- This table may contain duplicate entries for
4598 -- each profile because we pick up the values
4599 -- for the application and site level also
4600 OPEN get_profiles_for_appl(p_applTable(l_index));
4601 -- Retrieve information into the local variables
4602 FETCH get_profiles_for_appl INTO l_profile_option_name,
4603 l_level_value,
4604 l_level_value_application_id,
4605 l_profile_option_value,
4606 l_priority;
4607 WHILE get_profiles_for_appl%FOUND LOOP
4608
4609 l_ProfileTable(l_index2).profileOptionName := l_profile_option_name;
4610 l_ProfileTable(l_index2).profileOptionValue := l_profile_option_value;
4611
4612 l_index2 := l_index2 + 1;
4613
4614 FETCH get_profiles_for_appl INTO l_profile_option_name,
4615 l_level_value,
4616 l_level_value_application_id,
4617 l_profile_option_value,
4618 l_priority;
4619
4620 END LOOP;
4621 CLOSE get_profiles_for_appl;
4622
4623 -- Now we have a table of profiles
4624 -- Determine which flows need to be set for this appl
4625
4626 IF (CS_CF_UPG_UTL_PKG.Flows_Not_Already_Cloned(p_applTable(l_index))) THEN
4627 Clone_Flows_For_Appl(l_ProfileTable, p_applTable(l_index));
4628 END IF;
4629 commit;
4630
4631 -- now clean up the table so it can be reused
4632 l_ProfileTable.DELETE;
4633 l_index2 := 0;
4634 l_index := l_index + 1;
4635
4636 END LOOP; -- ends while loop
4637
4638 EXCEPTION
4639 WHEN OTHERS THEN
4640 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_PKG: Do_Flow_Upgrades_For_Appl', 'Exception in Do_Flow_Upgrades_For_Appl');
4641 IF (get_profiles_for_appl%ISOPEN) THEN
4642 CLOSE get_profiles_for_appl;
4643 END IF;
4644 RAISE;
4645
4646 END Do_Flow_Upgrades_For_Appl;
4647
4648 /*
4649 * Perform the actually cloning
4650 * of flows, based on the list of
4651 * profiles that are customized at the application level
4652 */
4653 PROCEDURE Clone_Flows_For_Appl(p_ProfileTable IN CS_CF_UPG_UTL_PKG.ProfileTable,
4654 p_applId IN NUMBER)
4655 IS
4656 l_count NUMBER := p_ProfileTable.COUNT;
4657 l_index NUMBER := 0;
4658 l_profileOptionName FND_PROFILE_OPTIONS.profile_option_name%TYPE;
4659 l_profileOptionValue FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE;
4660
4661 l_applId VARCHAR2(10) := to_char(p_applId);
4662
4663
4664 -- the following set of variables are used to
4665 -- determine whether we've already examined
4666 -- the profile option. If we have, we don't need to look at it again
4667 l_examineKBOption BOOLEAN := TRUE;
4668
4669 BEGIN
4670
4671 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Clone_Flows_For_Appl', 'Setting flow for applId: ' || l_applId );
4672 WHILE (l_index < l_count) LOOP
4673 l_profileOptionName := p_ProfileTable(l_index).profileOptionName;
4674 l_profileOptionValue := p_ProfileTable(l_index).profileOptionValue;
4675
4676 IF (l_profileOptionName = 'IBU_A_SR_KB_OPTION' AND l_examineKBOption) THEN
4677 IF (l_profileOptionValue = 'SEARCH') THEN
4678 -- this resp will use the default flow seeded for 11510
4679 CS_CF_UPG_UTL_PKG.Clone_Flow(p_applId, 10);
4680 ELSE
4681 -- this resp will use default flow seeded for 1159
4682 CS_CF_UPG_UTL_PKG.Clone_Flow(p_applId, 20);
4683 END IF;
4684
4685 CS_CF_UPG_UTL_PKG.Insert_New_Target('IBU_SR_CRE',
4686 'APPLICATION',
4687 l_applId,
4688 NULL,
4689 NULL,
4690 NULL,
4691 to_char(0-l_applId),
4692 NULL);
4693 l_examineKBOption := FALSE;
4694 END IF;
4695
4696 l_index := l_index + 1;
4697 END LOOP;
4698 End Clone_Flows_For_Appl;
4699
4700 /*
4701 * Procedure to performing flow upgrades for site level
4702 */
4703 PROCEDURE Do_Flow_Upgrades_For_Global(p_siteProfilesTable IN CS_CF_UPG_UTL_PKG.ProfileTable)
4704 IS
4705
4706 l_count NUMBER := p_siteProfilesTable.COUNT;
4707 l_index NUMBER := 0;
4708 l_profileOptionName FND_PROFILE_OPTIONS.profile_option_name%TYPE;
4709 l_profileOptionValue FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE;
4710
4711 l_examineKBOption BOOLEAN := TRUE;
4712 l_source_context_type_id NUMBER := 0;
4713 l_last_updated_by NUMBER;
4714 l_count2 NUMBER := 0;
4715
4716 CURSOR l_cur(p_source_context_type_id IN VARCHAR2) IS
4717 SELECT last_updated_by
4718 FROM cs_cf_source_cxt_targets
4719 WHERE source_context_type_id = p_source_context_type_id;
4720
4721 CURSOR l_cur2(p_source_context_type_id IN VARCHAR2) IS
4722 SELECT count(*)
4723 FROM cs_cf_source_cxt_targets
4724 WHERE source_context_type_id = p_source_context_type_id;
4725
4726 BEGIN
4727
4731 l_profileOptionValue := p_siteProfilesTable(l_index).profileOptionValue;
4728 WHILE (l_index < l_count) LOOP
4729
4730 l_profileOptionName := p_siteProfilesTable(l_index).profileOptionName;
4732
4733 IF (l_profileOptionName = 'IBU_A_SR_KB_OPTION' AND l_examineKBOption) THEN
4734 IF (l_profileOptionValue = 'SEARCH') THEN
4735 SELECT SOURCE_CONTEXT_TYPE_ID
4736 INTO l_source_context_type_id
4737 FROM cs_cf_source_cxt_types
4738 WHERE SOURCE_CODE = 'IBU_SR_CRE'
4739 AND CONTEXT_TYPE = 'GLOBAL';
4740
4741 OPEN l_cur(l_source_context_type_id);
4742 FETCH l_cur INTO l_last_updated_by;
4743 CLOSE l_cur;
4744
4745 OPEN l_cur2(l_source_context_type_id);
4746 FETCH l_cur2 INTO l_count2;
4747 CLOSE l_cur2;
4748
4749 -- mkcyee 02/26/2004 - only update the entry is it has not
4750 -- been customized
4751 IF (l_last_updated_by in (-1,1,2) AND l_count2 > 0) THEN
4752
4753 UPDATE cs_cf_source_cxt_targets
4754 SET cust_target_value1 = '10'
4755 WHERE source_context_type_id = l_source_context_type_id;
4756
4757 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_STATEMENT, 'CS_CF_UPG_PKG.Do_Flow_Upgrades_For_Global', 'Updating global entry in cs_cf_source_cxt_targets to use 11510 default flow. l_source_context_type_id:' || l_source_context_type_id);
4758 END IF;
4759
4760 END IF;
4761 l_examineKBOption := FALSE;
4762 END IF;
4763 commit;
4764 l_index := l_index + 1;
4765 END LOOP;
4766
4767 EXCEPTION
4768 WHEN OTHERS THEN
4769 CS_CF_UPG_UTL_PKG.log_mesg(FND_LOG.LEVEL_UNEXPECTED, 'CS_CF_UPG_PKG.Do_Flow_Upgrades_For_Global', 'Unexpected error in Do_Flow_Upgrades_For_Global');
4770
4771 IF (l_cur%ISOPEN) THEN
4772 CLOSE l_cur;
4773 END IF;
4774 IF (l_cur2%ISOPEN) THEN
4775 CLOSE l_cur2;
4776 END IF;
4777
4778 RAISE;
4779
4780 END Do_Flow_Upgrades_For_Global;
4781
4782
4783 END CS_CF_UPG_PKG;