[Home] [Help]
PACKAGE BODY: APPS.IEX_CHECKLIST_UTILITY
Source
1 PACKAGE BODY IEX_CHECKLIST_UTILITY AS
2 /* $Header: iexvchkb.pls 120.20.12020000.2 2012/07/24 19:13:26 schekuri ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IEX_CHECKLIST_UTILITY';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexvchkb.pls';
6 G_APPL_ID NUMBER;
7 G_LOGIN_ID NUMBER;
8 G_PROGRAM_ID NUMBER;
9 G_USER_ID NUMBER;
10 G_REQUEST_ID NUMBER;
11
12 PG_DEBUG NUMBER(2);
13
14 --------------------------------------------------------------------
15 -- This function returns to image name for the checklist items which are
16 -- FUNCTIONAL_AREA
17 --------------------------------------------------------------------
18
19 FUNCTION GET_GO_TO_TASK_IMAGE_NAME(p_checklist_item_name IN VARCHAR2,
20 p_checklist_item_type IN VARCHAR2,
21 p_checklist_item_status IN VARCHAR2)
22 RETURN VARCHAR2
23 IS
24 BEGIN
25 IF p_checklist_item_type IN ('FUNCTIONAL_AREA', 'FUNCTIONAL_INIT') THEN
26 IF p_checklist_item_status = 'DISABLED' THEN
27 RETURN '/OA_MEDIA/takeaction_disabled.gif';
28 ELSE
29 RETURN '/OA_MEDIA/takeaction_enabled.gif';
30 END IF;
31 ELSE
32 RETURN NULL;
33 END IF;
34 EXCEPTION
35 WHEN OTHERS THEN
36 RETURN NULL;
37 END;
38
39 --------------------------------------------------------------------
40 -- This function returns to image name of status of the checklist items
41 -- based upon the status of the tasks of the checklist items.
42 --
43 -- If any task is 'In Progress' then the main status will also be
44 -- In Progress'.
45 -- If any task is 'Complete' and no task is 'In Progress' then the
46 -- main status will be 'Complete'.
47 -- If all tasks are 'Not Applicable' then the main status will also
48 -- be 'Not Applicable'.
49 -- If all tasks are 'Not Started' then the main status will also
50 -- be 'Not Started'
51 --
52 --------------------------------------------------------------------
53 FUNCTION GET_STATUS_IMAGE_NAME(p_checklist_item_name IN VARCHAR2,
54 p_checklist_item_type IN VARCHAR2,
55 p_checklist_item_status IN VARCHAR2)
56 RETURN VARCHAR2
57 IS
58 BEGIN
59 IF p_checklist_item_type IN ('FUNCTIONAL_AREA', 'FUNCTIONAL_INIT') THEN
60 IF p_checklist_item_status = 'DISABLED' THEN
61 RETURN '/OA_MEDIA/notstartedind_status.gif';
62 ELSIF p_checklist_item_status = 'NOTSTARTED' THEN
63 RETURN '/OA_MEDIA/notstartedind_status.gif';
64 ELSIF p_checklist_item_status = 'COMPLETE' THEN
65 RETURN '/OA_MEDIA/completeind_status.gif';
66 ELSIF p_checklist_item_status = 'INPROGRESS' THEN
67 RETURN '/OA_MEDIA/inprogressind_status.gif';
68 ELSIF p_checklist_item_status = 'NOTAPPLICABLE' THEN
69 RETURN '/OA_MEDIA/notapplicableind_status.gif';
70 ELSE
71 RETURN NULL;
72 END IF;
73 ELSE
74 RETURN NULL;
75 END IF;
76 EXCEPTION
77 WHEN OTHERS THEN
78 RETURN NULL;
79 END;
80
81 FUNCTION GET_RANGE_FROM_VALUE(
82 p_score_comp_type_id IN NUMBER,
83 p_lookup_code IN VARCHAR2)
84 RETURN NUMBER
85 IS
86 CURSOR c_low_from IS
87 SELECT low_from
88 FROM iex_metric_ratings
89 WHERE score_comp_type_id = p_score_comp_type_id;
90
91 CURSOR c_medium_from IS
92 SELECT medium_from
93 FROM iex_metric_ratings
94 WHERE score_comp_type_id = p_score_comp_type_id;
95
96 CURSOR c_high_from IS
97 SELECT high_from
98 FROM iex_metric_ratings
99 WHERE score_comp_type_id = p_score_comp_type_id;
100
101 l_value NUMBER;
102 BEGIN
103 IF p_lookup_code = 'LOW' THEN
104 OPEN c_low_from;
105 FETCH c_low_from INTO l_value;
106 CLOSE c_low_from;
107 ELSIF p_lookup_code = 'MEDIUM' THEN
108 OPEN c_medium_from;
109 FETCH c_medium_from INTO l_value;
110 CLOSE c_medium_from;
111 ELSIF p_lookup_code = 'HIGH' THEN
112 OPEN c_high_from;
113 FETCH c_high_from INTO l_value;
114 CLOSE c_high_from;
115 END IF;
116
117 return l_value;
118
119 EXCEPTION
120 WHEN OTHERS THEN
121 RETURN NULL;
122 END;
123
124 FUNCTION GET_RANGE_TO_VALUE(
125 p_score_comp_type_id IN NUMBER,
126 p_lookup_code IN VARCHAR2) RETURN NUMBER
127 IS
128 CURSOR c_low_to IS
129 SELECT low_to
130 FROM iex_metric_ratings
131 WHERE score_comp_type_id = p_score_comp_type_id;
132
133 CURSOR c_medium_to IS
134 SELECT medium_to
135 FROM iex_metric_ratings
136 WHERE score_comp_type_id = p_score_comp_type_id;
137
138 CURSOR c_high_to IS
139 SELECT high_to
140 FROM iex_metric_ratings
141 WHERE score_comp_type_id = p_score_comp_type_id;
142
143 l_value NUMBER;
144 BEGIN
145 IF p_lookup_code = 'LOW' THEN
146 OPEN c_low_to;
147 FETCH c_low_to INTO l_value;
148 CLOSE c_low_to;
149 ELSIF p_lookup_code = 'MEDIUM' THEN
150 OPEN c_medium_to;
151 FETCH c_medium_to INTO l_value;
152 CLOSE c_medium_to;
153 ELSIF p_lookup_code = 'HIGH' THEN
154 OPEN c_high_to;
155 FETCH c_high_to INTO l_value;
156 CLOSE c_high_to;
157 END IF;
158
159 return l_value;
160
161 EXCEPTION
162 WHEN OTHERS THEN
163 RETURN NULL;
164 END;
165
166 PROCEDURE UPDATE_METRIC_RATING(
167 p_score_comp_type_id IN NUMBER,
168 p_low_from IN NUMBER,
169 p_low_to IN NUMBER,
170 p_medium_from IN NUMBER,
171 p_medium_to IN NUMBER,
172 p_high_from IN NUMBER,
173 p_high_to IN NUMBER)
174 IS
175 CURSOR c_rating IS
176 SELECT '1'
177 FROM iex_metric_ratings
178 WHERE score_comp_type_id = p_score_comp_type_id;
179 l_metric_rating_id NUMBER;
180 l_dummy VARCHAR2(1);
181 BEGIN
182 OPEN c_rating;
183 FETCH c_rating INTO l_dummy;
184 IF c_rating%FOUND THEN
185 UPDATE iex_metric_ratings
186 SET low_from = p_low_from, low_to = p_low_to,
187 medium_from = p_medium_from, medium_to = p_medium_to,
188 high_from = p_high_from, high_to = p_high_to,
189 last_update_date = SYSDATE, last_updated_by = fnd_global.user_id, last_update_login = fnd_global.login_id
190 WHERE score_comp_type_id = p_score_comp_type_id;
191 ELSE
192 SELECT iex_metric_ratings_s.nextval
193 INTO l_metric_rating_id
194 FROM dual;
195
196 INSERT INTO iex_metric_ratings(metric_rating_id, score_comp_type_id, low_from, low_to,
197 medium_from, medium_to, high_from, high_to,
198 creation_date, created_by, last_update_date, last_updated_by, last_update_login)
199 VALUES (l_metric_rating_id, p_score_comp_type_id, p_low_from, p_low_to,
200 p_medium_from, p_medium_to, p_high_from, p_high_to,
201 SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.user_id, fnd_global.user_id);
202 END IF;
203 CLOSE c_rating;
204 --EXCEPTION
205 --WHEN OTHERS THEN
206 --fnd_message.set_name ('IEX', 'IEX_ADMIN_UNKNOWN_ERROR');
207 --fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
208 --fnd_msg_pub.add;
209 END;
210
211 -- Begin kasreeni 12-16-2005 4887338
212 PROCEDURE checkUpgradeStrategies( x_return_status OUT NOCOPY VARCHAR2) IS
213
214 CURSOR c_CheckStrategyGroups IS
215 SELECT COUNT(1) FROM IEX_STRATEGY_TEMPLATES_B istl
216 -- WHERE istl.strategy_temp_id > 10000 and NOT EXISTS (SELECT 1 FROM IEX_STRATEGY_TEMPLATE_GROUPS istg -- bug 6067428
217 WHERE istl.strategy_temp_id >= 10000 and NOT EXISTS (SELECT 1 FROM IEX_STRATEGY_TEMPLATE_GROUPS istg
218 WHERE istg.STRATEGY_TEMP_ID = ISTL.STRATEGY_TEMP_ID);
219
220 l_Upgrades NUMBER := 0;
221 l_max_group NUMBER := 0;
222 l_next_seq NUMBER := 0;
223 BEGIN
224 IEX_DEBUG_PUB.logmessage('IN checkUpgradeStrategie ');
225 Open c_CheckStrategyGroups;
226 FETCH C_CheckStrategyGroups INTO l_Upgrades;
227 close c_CheckStrategyGroups;
228 if l_Upgrades > 0 then
229 IEX_DEBUG_PUB.logmessage('Inserting into StrategyGroups');
230 INSERT INTO IEX_STRATEGY_TEMPLATE_GROUPS (
231 OBJECT_VERSION_NUMBER,
232 CREATED_BY,
233 CREATION_DATE,
234 LAST_UPDATE_DATE,
235 LAST_UPDATED_BY,
236 LAST_UPDATE_LOGIN,
237 REQUEST_ID,
238 GROUP_ID,
239 GROUP_NAME,
240 STRATEGY_RANK,
241 ENABLED_FLAG,
242 CATEGORY_TYPE,
243 CHANGE_STRATEGY_YN,
244 CHECK_LIST_YN,
245 CHECK_LIST_TEMP_ID,
246 VALID_FROM_DT,
247 VALID_TO_DT,
248 OBJECT_FILTER_ID,
249 STRATEGY_LEVEL,
250 SCORE_TOLERANCE,
251 STRATEGY_TEMP_ID
252 )
253 SELECT
254 1,
255 ISTL.CREATED_BY,
256 ISTL.CREATION_DATE,
257 ISTL.LAST_UPDATE_DATE,
258 ISTL.LAST_UPDATED_BY,
259 ISTL.LAST_UPDATE_LOGIN,
260 ISTL.REQUEST_ID,
261 ISTL.STRATEGY_TEMP_ID, --IEX_STRATEGY_TEMPLATE_GROUPS_S.NEXTVAL, -- bug 9256394
262 ISTL.STRATEGY_NAME,
263 ISTL.STRATEGY_RANK,
264 ISTL.ENABLED_FLAG,
265 ISTL.CATEGORY_TYPE,
266 ISTL.CHANGE_STRATEGY_YN,
267 ISTL.CHECK_LIST_YN,
268 ISTL.CHECK_LIST_TEMP_ID ,
269 ISTL.VALID_FROM_DT,
270 ISTL.VALID_TO_DT,
271 (select max(object_filter_id) from iex_object_filters where object_filter_type = 'IEXSTRAT'
272 and object_id = istl.strategy_temp_id), --ISTL.OBJECT_FILTER_ID,
273 ISTL.STRATEGY_LEVEL,
274 ISTL.SCORE_TOLERANCE,
275 ISTL.STRATEGY_TEMP_ID
276 FROM IEX_STRATEGY_TEMPLATES_VL ISTL
277 WHERE istl.strategy_temp_id >= 10000
278 AND (istl.strategy_temp_group_id is NULL or istl.strategy_temp_group_id = 0)
279 AND NOT EXISTS (SELECT 1 FROM IEX_STRATEGY_TEMPLATE_GROUPS istg
280 WHERE istg.STRATEGY_TEMP_ID = ISTL.STRATEGY_TEMP_ID);
281
282 UPDATE IEX_STRATEGY_TEMPLATES_b istl
283 SET istl.STRATEGY_TEMP_GROUP_ID =
284 (SELECT istg.GROUP_ID FROM IEX_STRATEGY_TEMPLATE_GROUPS istg
285 WHERE istg.strategy_temp_id = istl.strategy_temp_id)
286 WHERE istl.strategy_temp_id >= 10000
287 AND (istl.strategy_temp_group_id is NULL or istl.strategy_temp_group_id = 0);
288 commit;
289
290 begin
291 select max(strategy_temp_id) into l_max_group from iex_strategy_templates_b;
292
293 select IEX_STRATEGY_TEMPLATE_GROUPS_S.nextval into l_next_seq from dual;
294
295 while l_next_seq <= l_max_group loop
296 select IEX_STRATEGY_TEMPLATE_GROUPS_S.nextval INTO l_next_seq from dual;
297 end loop;
298
299 exception
300 when others then
301 IEX_DEBUG_PUB.logmessage('Exception in sequence '|| sqlerrm);
302 end;
303 end if;
304 commit;
305 IEX_DEBUG_PUB.logmessage('End checkUpgradeStrategie ');
306 EXCEPTION
307 WHEN OTHERS THEN
308 x_return_status := 'F';
309 fnd_message.set_name ('IEX', 'IEX_ADMIN_UNKNOWN_ERROR');
310 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
311 fnd_msg_pub.add;
312
313 END checkUpgradeStrategies;
314
315 -- End kasreeni 12-16-2005 4887338
316
317 PROCEDURE UPDATE_CHECKLIST_ITEM(
318 p_checklist_item_id IN NUMBER,
319 x_return_status OUT NOCOPY VARCHAR2) IS
320 CURSOR c_general_info IS
321 SELECT fnd_profile.value_specific('IEX_COLLECTIONS_BUCKET_NAME', -1, -1, -1, -1, -1) COLLECTIONS_BUCKET,
322 fnd_profile.value_specific('IEX_ENABLE_CUST_STATUS_EVENT', -1, -1, -1, -1, -1) CUST_STATUS_EVENT,
323 fnd_profile.value_specific('IEX_CUST_ACCESS', -1, -1, -1, -1, -1) WORK_QUEUE_ACCESS,
324 fnd_profile.value_specific('IEX_ACCESS_LEVEL', -1, -1, -1, -1, -1) ACCESS_LEVEL,
325 fnd_profile.value_specific('IEX_COLLECTIONS_RATE_TYPE', -1, -1, -1, -1, -1) RATE_TYPE
326 -- fnd_profile.value_specific('ACCOUNT_INTERACTION_ACTIVITY', -1, -1, -1, -1, -1) ACCOUNT_ACTIVITY,
327 -- fnd_profile.value_specific('IEX_DELINQUENCY_ACTIVITY', -1, -1, -1, -1, -1) DELINQUENCY_ACTIVITY,
328 -- fnd_profile.value_specific('DISPUTE_INTERACTION_ACTIVITY', -1, -1, -1, -1, -1) DISPUTE_ACTIVITY,
329 -- fnd_profile.value_specific('IEX_ADJUSTMENT_ACTIVITY', -1, -1, -1, -1, -1) ADJUSTMENT_ACTIVITY,
330 -- fnd_profile.value_specific('PAYMENT_INTERACTION_ACTIVITY', -1, -1, -1, -1, -1) PAYMENT_ACTIVITY,
331 -- fnd_profile.value_specific('PROMISE_INTERACTION_ACTIVITY', -1, -1, -1, -1, -1) PROMISE_ACTIVITY,
332 -- fnd_profile.value_specific('IEX_STRATEGY_ACTIVITY', -1, -1, -1, -1, -1) STRATEGY_ACTIVITY,
333 -- fnd_profile.value_specific('IEX_CREDIT_HOLD', -1, -1, -1, -1, -1) credit_hold,
334 -- fnd_profile.value_specific('IEX_SERVICE_HOLD', -1, -1, -1, -1, -1) service_hold_delin
335 FROM dual;
336
337 l_general_info_row c_general_info%ROWTYPE;
338 l_status VARCHAR2(30);
339
340 -- Bug 8479638 by ehuh 5/6/2009
341 Cursor c_object_filter Is
342 select obj.object_id,obj.object_filter_id,obj.last_updated_by,obj.last_update_login
343 from iex_object_filters obj, iex_strategy_template_groups stg
344 where stg.group_id = obj.object_id
345 and obj.object_filter_type = 'IEXSTRAT'
346 and obj.object_id > 10000
347 and (stg.object_filter_id is null or stg.object_filter_id = 0);
348
349 BEGIN
350 x_return_status := 'S';
351 l_status := 'COMPLETE';
352
353 IEX_DEBUG_PUB.logmessage('Update CheckList ' || p_checklist_item_id);
354
355 -- Bug 8479638 by ehuh 5/6/2009
356 For rec_object_filter IN c_object_filter LOOP
357 update iex_strategy_template_groups
358 set object_filter_id=rec_object_filter.object_filter_id,last_update_date=sysdate,last_updated_by=rec_object_filter.last_updated_by,last_update_login=rec_object_filter.last_update_login
359 where group_id = rec_object_filter.object_id;
360 End LOOP;
361
362 -- Begin kasreeni 12-16-2005 4887338
363 if (p_checklist_item_id = 100 ) then
364 IEX_DEBUG_PUB.logmessage('Calling checkUpgradeStrategie ');
365 checkUpgradeStrategies(x_return_status);
366 return ;
367 end if;
368 -- End kasreeni 12-16-2005 4887338
369
370 IF p_checklist_item_id = 3 THEN
371 OPEN c_general_info;
372 FETCH c_general_info INTO l_general_info_row;
373 CLOSE c_general_info;
374
375 IF l_general_info_row.collections_bucket IS NOT NULL AND
376 -- l_general_info_row.cust_status_event IS NOT NULL AND
377 l_general_info_row.work_queue_access IS NOT NULL AND
378 l_general_info_row.access_level IS NOT NULL AND
379 l_general_info_row.rate_type IS NOT NULL
380 -- l_general_info_row.account_activity IS NOT NULL AND
381 -- l_general_info_row.delinquency_activity IS NOT NULL AND
382 -- l_general_info_row.dispute_activity IS NOT NULL AND
383 -- l_general_info_row.adjustment_activity IS NOT NULL AND
384 -- l_general_info_row.payment_activity IS NOT NULL AND
385 -- l_general_info_row.promise_activity IS NOT NULL AND
386 -- l_general_info_row.strategy_activity IS NOT NULL AND
387 -- l_general_info_row.credit_hold IS NOT NULL AND
388 -- l_general_info_row.service_hold_delin IS NOT NULL THEN
389 THEN
390 l_status := 'COMPLETE';
391 ELSE
392 l_status := 'INPROGRESS';
393 END IF;
394
395 iex_debug_pub.logmessage('l_status=' || l_status);
396
397 UPDATE iex_checklist_items_b
398 SET status = l_status, task_last_modified_date = SYSDATE, last_update_date = SYSDATE,
399 last_updated_by = G_USER_ID, last_update_login = G_LOGIN_ID
400 WHERE checklist_item_id = p_checklist_item_id;
401 ELSE
402 UPDATE iex_checklist_items_b
403 SET status = l_status, task_last_modified_date = SYSDATE, last_update_date = SYSDATE,
404 last_updated_by = G_USER_ID, last_update_login = G_LOGIN_ID
405 WHERE checklist_item_id = p_checklist_item_id;
406
407 END IF;
408 EXCEPTION
409 WHEN OTHERS THEN
410 x_return_status := 'F';
411 END UPDATE_CHECKLIST_ITEM;
412
413 PROCEDURE CHANGE_LEASING_SETUP(
414 p_leasing_enabled IN VARCHAR2,
415 x_return_status OUT NOCOPY VARCHAR2) IS
416 CURSOR c_resp IS
417 SELECT resp.application_id, resp.responsibility_id, resp.menu_id
418 FROM fnd_responsibility resp, fnd_menus menu
419 WHERE resp.menu_id = menu.menu_id
420 AND menu.menu_name = 'IEX_COLLECTIONS_AGENT'
421 -- Begin fix bug #4930424-remove TABLE ACCESS FULL
422 AND resp.application_id = 695;
423 -- End fix bug #4930424-remove TABLE ACCESS FULL
424
425 CURSOR c_funct(p_responsibility_id NUMBER, p_function_name VARCHAR2) IS
426 SELECT rf.action_id, ff.function_id
427 FROM fnd_resp_functions rf, fnd_form_functions ff
428 WHERE rf.responsibility_id(+) = p_responsibility_id
429 AND ff.function_name = p_function_name
430 AND rf.action_id(+) = ff.function_id
431 AND rf.rule_type(+) = 'F';
432
433 r_funct c_funct%ROWTYPE;
434 l_function_name VARCHAR2(30);
435 l_rowid VARCHAR(1000);
436
437 CURSOR c_lookup IS
438 SELECT lookup_type, lookup_code, meaning,
439 description, enabled_flag, start_date_active, end_date_active,
440 territory_code, attribute_category, attribute1, attribute2,
441 attribute3, attribute4, attribute5, attribute6,
442 attribute7, attribute8, attribute9, attribute10,
443 attribute11, attribute12, attribute13, attribute14, attribute15,
444 tag, security_group_id, view_application_id
445 FROM fnd_lookup_values_vl
446 WHERE (lookup_type = 'IEX_HISTORY_TYPE'
447 AND lookup_code IN ('PAYMENT_CNSLD', 'PAYMENT_CONTRACT', 'PROMISE_CNSLD', 'PROMISE_CONTRACT'))
448 OR (lookup_type = 'IEX_CNSLD');
449 l_enabled_flag VARCHAR2(1);
450 BEGIN
451 x_return_status := 'S';
452
453 l_enabled_flag := NVL(p_leasing_enabled, 'N');
454
455 IF l_enabled_flag = 'N' THEN
456 FOR r_resp IN c_resp LOOP
457 l_function_name := 'IEX_COLL_CNTR';
458 iex_debug_pub.logmessage('r.resp.responsibility_id=' || r_resp.responsibility_id || ':l_function_name=' || l_function_name);
459 OPEN c_funct(r_resp.responsibility_id, l_function_name);
460 FETCH c_funct INTO r_funct;
461 iex_debug_pub.logmessage('r_funct.action_id=' || r_funct.action_id || ':r_funct.function_id=' || r_funct.function_id);
462 IF r_funct.action_id IS NULL THEN
463 fnd_resp_functions_pkg.insert_row(x_rowid => l_rowid,
464 x_application_id => r_resp.application_id,
465 x_responsibility_id => r_resp.responsibility_id,
466 x_action_id => r_funct.function_id,
467 x_rule_type => 'F',
468 x_creation_date => SYSDATE,
469 x_created_by => 1,
470 x_last_updated_by => 1,
471 x_last_update_date => SYSDATE,
472 x_last_update_login => 1);
473
474 iex_debug_pub.logmessage('x_rowid=' || l_rowid);
475
476 END IF;
477
478 CLOSE c_funct;
479
480 l_function_name := 'IEX_COLL_CASE';
481 r_funct.action_id := NULL;
482 r_funct.function_id := NULL;
483 iex_debug_pub.logmessage('r.resp.responsibility_id=' || r_resp.responsibility_id || ':l_function_name=' || l_function_name);
484 OPEN c_funct(r_resp.responsibility_id, l_function_name);
485 FETCH c_funct INTO r_funct;
486 iex_debug_pub.logmessage('r_funct.action_id=' || r_funct.action_id || ':r_funct.function_id=' || r_funct.function_id);
487 IF r_funct.action_id IS NULL THEN
488 fnd_resp_functions_pkg.insert_row(x_rowid => l_rowid,
489 x_application_id => r_resp.application_id,
490 x_responsibility_id => r_resp.responsibility_id,
491 x_action_id => r_funct.function_id,
492 x_rule_type => 'F',
493 x_creation_date => SYSDATE,
494 x_created_by => 1,
495 x_last_updated_by => 1,
496 x_last_update_date => SYSDATE,
497 x_last_update_login => 1);
498
499 iex_debug_pub.logmessage('x_rowid=' || l_rowid);
500 END IF;
501
502 CLOSE c_funct;
503 END LOOP;
504 ELSE
505 FOR r_resp IN c_resp LOOP
506 l_function_name := 'IEX_COLL_CNTR';
507 iex_debug_pub.logmessage('r.resp.responsibility_id=' || r_resp.responsibility_id || ':l_function_name=' || l_function_name);
508 OPEN c_funct(r_resp.responsibility_id, l_function_name);
509 FETCH c_funct INTO r_funct;
510 iex_debug_pub.logmessage('r_funct.action_id=' || r_funct.action_id || ':r_funct.function_id=' || r_funct.function_id);
511 IF r_funct.action_id IS NOT NULL THEN
512 fnd_resp_functions_pkg.delete_row(x_application_id => r_resp.application_id,
513 x_responsibility_id => r_resp.responsibility_id,
514 x_action_id => r_funct.action_id,
515 x_rule_type => 'F');
516 END IF;
517
518 CLOSE c_funct;
519
520 l_function_name := 'IEX_COLL_CASE';
521 r_funct.action_id := NULL;
522 r_funct.function_id := NULL;
523 iex_debug_pub.logmessage('r.resp.responsibility_id=' || r_resp.responsibility_id || ':l_function_name=' || l_function_name);
524 OPEN c_funct(r_resp.responsibility_id, l_function_name);
525 FETCH c_funct INTO r_funct;
526 iex_debug_pub.logmessage('r_funct.action_id=' || r_funct.action_id || ':r_funct.function_id=' || r_funct.function_id);
527 IF r_funct.action_id IS NOT NULL THEN
528 fnd_resp_functions_pkg.delete_row(x_application_id => r_resp.application_id,
529 x_responsibility_id => r_resp.responsibility_id,
530 x_action_id => r_funct.action_id,
531 x_rule_type => 'F');
532
533 iex_debug_pub.logmessage('x_rowid=' || l_rowid);
534 END IF;
535
536 CLOSE c_funct;
537 END LOOP;
538 END IF;
539
540 FOR r_lookup IN c_lookup LOOP
541 fnd_lookup_values_pkg.update_row(
542 x_lookup_type => r_lookup.lookup_type,
543 x_security_group_id => r_lookup.security_group_id,
544 x_view_application_id => r_lookup.view_application_id,
545 x_lookup_code => r_lookup.lookup_code,
546 x_tag => r_lookup.tag,
547 x_enabled_flag => l_enabled_flag,
548 x_start_date_active => r_lookup.start_date_active,
549 x_end_date_active => r_lookup.end_date_active,
550 x_territory_code => r_lookup.territory_code,
551 x_meaning => r_lookup.meaning,
552 x_description => r_lookup.description,
553 x_last_update_date => sysdate,
554 x_last_updated_by => 1,
555 x_last_update_login => 1,
556 x_attribute_category=>r_lookup.attribute_category,
557 x_attribute1=>r_lookup.attribute1,
558 x_attribute2=>r_lookup.attribute2,
559 x_attribute3=>r_lookup.attribute3,
560 x_attribute4=>r_lookup.attribute4,
561 x_attribute5=>r_lookup.attribute5,
562 x_attribute6=>r_lookup.attribute6,
563 x_attribute7=>r_lookup.attribute7,
564 x_attribute8=>r_lookup.attribute8,
565 x_attribute9=>r_lookup.attribute9,
566 x_attribute10=>r_lookup.attribute10,
567 x_attribute11=>r_lookup.attribute11,
568 x_attribute12=>r_lookup.attribute12,
569 x_attribute13=>r_lookup.attribute13,
570 x_attribute14=>r_lookup.attribute14,
571 x_attribute15=>r_lookup.attribute15
572 );
573 END LOOP;
574 EXCEPTION
575 WHEN OTHERS THEN
576 x_return_status := 'F';
577 fnd_message.set_name ('IEX', 'IEX_ADMIN_UNKNOWN_ERROR');
578 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
579 fnd_msg_pub.add;
580 END CHANGE_LEASING_SETUP;
581
582 PROCEDURE CHANGE_LOAN_SETUP(
583 p_loan_enabled IN VARCHAR2,
584 x_return_status OUT NOCOPY VARCHAR2) IS
585 CURSOR c_resp IS
586 SELECT resp.application_id, resp.responsibility_id, resp.menu_id
587 FROM fnd_responsibility resp, fnd_menus menu
588 WHERE resp.menu_id = menu.menu_id
589 AND menu.menu_name = 'IEX_COLLECTIONS_AGENT'
590 -- Begin fix bug #4930424-remove TABLE ACCESS FULL
591 AND resp.application_id = 695;
592 -- End fix bug #4930424-remove TABLE ACCESS FULL
593
594 CURSOR c_funct(p_responsibility_id NUMBER, p_function_name VARCHAR2) IS
595 SELECT rf.action_id, ff.function_id
596 FROM fnd_resp_functions rf, fnd_form_functions ff
597 WHERE rf.responsibility_id(+) = p_responsibility_id
598 AND ff.function_name = p_function_name
599 AND rf.action_id(+) = ff.function_id
600 AND rf.rule_type(+) = 'F';
601
602 -- Begin by Ehuh to fix a bug 4639561
603 CURSOR get_function_id IS
604 Select function_id from fnd_form_functions
605 where function_name = 'IEX_COLL_LOAN';
606
607 CURSOR get_menu_id IS
608 Select menu_id from fnd_menus
609 where menu_name = 'IEX_COLL';
610
611 CURSOR get_entry_sequence(p_menu_id NUMBER, p_function_id NUMBER) IS
612 Select entry_sequence from fnd_menu_entries
613 where menu_id = p_menu_id
614 and function_id = p_function_id;
615
616 l_entry_sequence number := 0;
617 l_menu_id number := 0;
618 l_func_id number := 0;
619 -- End to fix bug 4639561
620
621 r_funct c_funct%ROWTYPE;
622 l_function_name VARCHAR2(30);
623 l_rowid VARCHAR(1000);
624
625 l_enabled_flag VARCHAR2(1);
626 BEGIN
627 x_return_status := 'S';
628
629 l_enabled_flag := NVL(p_loan_enabled, 'N');
630
631 IF l_enabled_flag = 'N' THEN
632 FOR r_resp IN c_resp LOOP
633 l_function_name := 'IEX_COLL_LOAN';
634 iex_debug_pub.logmessage('r.resp.responsibility_id=' || r_resp.responsibility_id || ':l_function_name=' || l_function_name);
635 OPEN c_funct(r_resp.responsibility_id, l_function_name);
636 FETCH c_funct INTO r_funct;
637 iex_debug_pub.logmessage('r_funct.action_id=' || r_funct.action_id || ':r_funct.function_id=' || r_funct.function_id);
638 IF r_funct.action_id IS NULL THEN
639 fnd_resp_functions_pkg.insert_row(x_rowid => l_rowid,
640 x_application_id => r_resp.application_id,
641 x_responsibility_id => r_resp.responsibility_id,
642 x_action_id => r_funct.function_id,
643 x_rule_type => 'F',
644 x_creation_date => SYSDATE,
645 x_created_by => 1,
646 x_last_updated_by => 1,
647 x_last_update_date => SYSDATE,
648 x_last_update_login => 1);
649
650 iex_debug_pub.logmessage('x_rowid=' || l_rowid);
651
652 END IF;
653
654 CLOSE c_funct;
655 END LOOP;
656 ELSE
657 FOR r_resp IN c_resp LOOP
658 l_function_name := 'IEX_COLL_LOAN';
659 iex_debug_pub.logmessage('r.resp.responsibility_id=' || r_resp.responsibility_id || ':l_function_name=' || l_function_name);
660 OPEN c_funct(r_resp.responsibility_id, l_function_name);
661 FETCH c_funct INTO r_funct;
662 iex_debug_pub.logmessage('r_funct.action_id=' || r_funct.action_id || ':r_funct.function_id=' || r_funct.function_id);
663 IF r_funct.action_id IS NOT NULL THEN
664 fnd_resp_functions_pkg.delete_row(x_application_id => r_resp.application_id,
665 x_responsibility_id => r_resp.responsibility_id,
666 x_action_id => r_funct.action_id,
667 x_rule_type => 'F');
668 END IF;
669
670 CLOSE c_funct;
671 END LOOP;
672 END IF;
673
674 -- Begin by Ehuh to fix a bug 4639561
675 Begin
676 OPEN get_function_id;
677 FETCH get_function_id into l_func_id;
678 iex_debug_pub.logmessage('Function ID = '||l_func_id);
679
680 if get_function_id%NOTFOUND then
681 iex_debug_pub.logmessage('NOt found FUNCTION ID ');
682 null;
683 end if;
684
685 close get_function_id;
686
687 OPEN get_menu_id;
688 FETCH get_menu_id into l_menu_id;
689
690 if get_menu_id%NOTFOUND then
691 iex_debug_pub.logmessage('NOt found MENU ID ');
692 null;
693 end if;
694
695 close get_menu_id;
696
697 if (l_menu_id <> 0) and (l_func_id <> 0) then
698 OPEN get_entry_sequence(l_menu_id , l_func_id );
699 FETCH get_entry_sequence into l_entry_sequence;
700
701 if get_entry_sequence%NOTFOUND then
702 iex_debug_pub.logmessage('NOt found ENTRY SEQ ');
703 null;
704 end if;
705
706 close get_entry_sequence;
707
708 FND_MENU_ENTRIES_PKG.update_row(
709 x_menu_id => l_menu_id,
710 x_entry_sequence => l_entry_sequence,
711 x_sub_menu_id => null,
712 x_function_id => l_func_id,
713 x_grant_flag => l_enabled_flag,
714 x_prompt => null,
715 x_description => null,
716 x_last_update_date => SYSDATE,
717 x_last_updated_by => 1,
718 x_last_update_login => 1 );
719 end if;
720
721 Exception
722 When others then
723 iex_debug_pub.logmessage('Exceptopn ');
724 null;
725 End;
726 -- End to fix bug 4639561
727
728 EXCEPTION
729 WHEN OTHERS THEN
730 x_return_status := 'F';
731 fnd_message.set_name ('IEX', 'IEX_ADMIN_UNKNOWN_ERROR');
732 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
733 fnd_msg_pub.add;
734 END CHANGE_LOAN_SETUP;
735
736 PROCEDURE CHANGE_BUSINESS_LEVEL(p_business_level IN VARCHAR2,
737 p_promise_enabled IN VARCHAR2,
738 p_collections_methods IN VARCHAR2,
739 x_return_status OUT NOCOPY VARCHAR2) IS
740 CURSOR c_resp IS
741 SELECT resp.application_id, resp.responsibility_id, resp.menu_id, resp.responsibility_key
742 FROM fnd_responsibility resp
743 WHERE resp.application_id = 695;
744 l_return BOOLEAN;
745 l_promise_enabled VARCHAR2(1);
746 l_strategy_enabled VARCHAR2(1);
747 l_business_level VARCHAR2(30);
748 l_str_levels number; -- Changed for bug 8708271 pnaveenk multi level strategy
749 BEGIN
750 x_return_status := 'S';
751 l_business_level := p_business_level;
752 l_promise_enabled := NVL(p_promise_enabled, 'Y');
753 IF NVL(p_collections_methods, 'DUNNING') = 'DUNNING' THEN
754 l_strategy_enabled := 'N';
755 ELSE
756 l_strategy_enabled := 'Y';
757 END IF;
758
759 iex_debug_pub.logmessage('Starting ....');
760
761 iex_debug_pub.logmessage('l_promise_enabled=' || l_promise_enabled || ': l_strategy_enabled=' || l_strategy_enabled);
762
763 -- Start bug 7454867
764 begin
765 iex_debug_pub.logmessage('Starting Update menu for Stategy Tab based on Questionnaire ....');
766 iex_debug_pub.logmessage('p_collections_methods...... '||p_collections_methods);
767 -- if p_collections_methods = 'STRATEGIES' then
768 -- commented above if and added below Added by snuthala for bug 10221334 21/10/2010
769
770 if p_collections_methods <> 'DUNNING' then
771 update fnd_menu_entries set grant_flag = 'Y' where menu_id = 1006151 and function_id = 1011354;
772 else
773 update fnd_menu_entries set grant_flag = 'N' where menu_id = 1006151 and function_id = 1011354;
774 end if;
775 exception
776 when others then
777 null;
778 iex_debug_pub.logmessage('Exception ....Starting Update menu for Stategy Tab based on Questionnaire');
779 end;
780 -- End bug 7454867
781
782 -- Start for bug 8708271 pnaveenk multi level strategy
783 Begin
784
785 SELECT count(*)
786 INTO l_str_levels
787 FROM IEX_LOOKUPS_V
788 WHERE LOOKUP_TYPE='IEX_RUNNING_LEVEL'
789 AND iex_utilities.validate_running_level(LOOKUP_CODE)='Y';
790 Exception
791 when others then
792 iex_debug_pub.logmessage( 'Exception in finding number of strategy levels being used');
793 End;
794
795 IF l_str_levels > 1 then
796 iex_debug_pub.logmessage(' Multi level strategy is being used. No need to setup profiles here.');
797 return;
798 End if;
799
800 -- End for bug 8708271 pnaveenk
801 -- Begin fix bug #5142565-JYPARK-Change site level profile too
802 IF l_business_level = 'CUSTOMER' THEN
803 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_DELINQUENCIES',
804 x_value => 'Y',
805 x_level_name => 'SITE',
806 x_level_value => null,
807 x_level_value_app_id => '',
808 x_level_value2 => null);
809 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_DELINQUENCIES',
810 x_value => 'N',
811 x_level_name => 'SITE',
812 x_level_value => null,
813 x_level_value_app_id => '',
814 x_level_value2 => null);
815 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_DELINQUENCIES',
816 x_value => 'N',
817 x_level_name => 'SITE',
818 x_level_value => null,
819 x_level_value_app_id => '',
820 x_level_value2 => null);
821 l_return := fnd_profile.save(x_name => 'IEX_QEN_DELINQUENCIES',
822 x_value => 'N',
823 x_level_name => 'SITE',
824 x_level_value => null,
825 x_level_value_app_id => '',
826 x_level_value2 => null);
827 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_PROMISES',
828 x_value => l_promise_enabled,
829 x_level_name => 'SITE',
830 x_level_value => null,
831 x_level_value_app_id => '',
832 x_level_value2 => null);
833 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_PROMISES',
834 x_value => 'N',
835 x_level_name => 'SITE',
836 x_level_value => null,
837 x_level_value_app_id => '',
838 x_level_value2 => null);
839 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_PROMISES',
840 x_value => 'N',
841 x_level_name => 'SITE',
842 x_level_value => null,
843 x_level_value_app_id => '',
844 x_level_value2 => null);
845 l_return := fnd_profile.save(x_name => 'IEX_QEN_PROMISES',
846 x_value => 'N',
847 x_level_name => 'SITE',
848 x_level_value => null,
849 x_level_value_app_id => '',
850 x_level_value2 => null);
851 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_STRATEGIES',
852 x_value => l_strategy_enabled,
853 x_level_name => 'SITE',
854 x_level_value => null,
855 x_level_value_app_id => '',
856 x_level_value2 => null);
857 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_STRATEGIES',
858 x_value => 'N',
859 x_level_name => 'SITE',
860 x_level_value => null,
861 x_level_value_app_id => '',
862 x_level_value2 => null);
863 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_STRATEGIES',
864 x_value => 'N',
865 x_level_name => 'SITE',
866 x_level_value => null,
867 x_level_value_app_id => '',
868 x_level_value2 => null);
869 l_return := fnd_profile.save(x_name => 'IEX_QEN_STRATEGY',
870 x_value => 'N',
871 x_level_name => 'SITE',
872 x_level_value => null,
873 x_level_value_app_id => '',
874 x_level_value2 => null);
875 ELSIF l_business_level = 'ACCOUNT' THEN
876 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_DELINQUENCIES',
877 x_value => 'N',
878 x_level_name => 'SITE',
879 x_level_value => null,
880 x_level_value_app_id => '',
881 x_level_value2 => null);
882 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_DELINQUENCIES',
883 x_value => 'Y',
884 x_level_name => 'SITE',
885 x_level_value => null,
886 x_level_value_app_id => '',
887 x_level_value2 => null);
888 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_DELINQUENCIES',
889 x_value => 'N',
890 x_level_name => 'SITE',
891 x_level_value => null,
892 x_level_value_app_id => '',
893 x_level_value2 => null);
894 l_return := fnd_profile.save(x_name => 'IEX_QEN_DELINQUENCIES',
895 x_value => 'N',
896 x_level_name => 'SITE',
897 x_level_value => null,
898 x_level_value_app_id => '',
899 x_level_value2 => null);
900 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_PROMISES',
901 x_value => 'N',
902 x_level_name => 'SITE',
903 x_level_value => null,
904 x_level_value_app_id => '',
905 x_level_value2 => null);
906 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_PROMISES',
907 x_value => l_promise_enabled,
908 x_level_name => 'SITE',
909 x_level_value => null,
910 x_level_value_app_id => '',
911 x_level_value2 => null);
912 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_PROMISES',
913 x_value => 'N',
914 x_level_name => 'SITE',
915 x_level_value => null,
916 x_level_value_app_id => '',
917 x_level_value2 => null);
918 l_return := fnd_profile.save(x_name => 'IEX_QEN_PROMISES',
919 x_value => 'N',
920 x_level_name => 'SITE',
921 x_level_value => null,
922 x_level_value_app_id => '',
923 x_level_value2 => null);
924 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_STRATEGIES',
925 x_value => 'N',
926 x_level_name => 'SITE',
927 x_level_value => null,
928 x_level_value_app_id => '',
929 x_level_value2 => null);
930 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_STRATEGIES',
931 x_value => l_Strategy_enabled,
932 x_level_name => 'SITE',
933 x_level_value => null,
934 x_level_value_app_id => '',
935 x_level_value2 => null);
936 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_STRATEGIES',
937 x_value => 'N',
938 x_level_name => 'SITE',
939 x_level_value => null,
940 x_level_value_app_id => '',
941 x_level_value2 => null);
942 l_return := fnd_profile.save(x_name => 'IEX_QEN_STRATEGY',
943 x_value => 'N',
944 x_level_name => 'SITE',
945 x_level_value => null,
946 x_level_value_app_id => '',
947 x_level_value2 => null);
948 ELSIF l_business_level = 'BILL_TO' THEN
949 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_DELINQUENCIES',
950 x_value => 'N',
951 x_level_name => 'SITE',
952 x_level_value => null,
953 x_level_value_app_id => '',
954 x_level_value2 => null);
955 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_DELINQUENCIES',
956 x_value => 'N',
957 x_level_name => 'SITE',
958 x_level_value => null,
959 x_level_value_app_id => '',
960 x_level_value2 => null);
961 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_DELINQUENCIES',
962 x_value => 'Y',
963 x_level_name => 'SITE',
964 x_level_value => null,
965 x_level_value_app_id => '',
966 x_level_value2 => null);
967 l_return := fnd_profile.save(x_name => 'IEX_QEN_DELINQUENCIES',
968 x_value => 'N',
969 x_level_name => 'SITE',
970 x_level_value => null,
971 x_level_value_app_id => '',
972 x_level_value2 => null);
973 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_PROMISES',
974 x_value => 'N',
975 x_level_name => 'SITE',
976 x_level_value => null,
977 x_level_value_app_id => '',
978 x_level_value2 => null);
979 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_PROMISES',
980 x_value => 'N',
981 x_level_name => 'SITE',
982 x_level_value => null,
983 x_level_value_app_id => '',
984 x_level_value2 => null);
985 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_PROMISES',
986 x_value => l_promise_enabled,
987 x_level_name => 'SITE',
988 x_level_value => null,
989 x_level_value_app_id => '',
990 x_level_value2 => null);
991 l_return := fnd_profile.save(x_name => 'IEX_QEN_PROMISES',
992 x_value => 'N',
993 x_level_name => 'SITE',
994 x_level_value => null,
995 x_level_value_app_id => '',
996 x_level_value2 => null);
997 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_STRATEGIES',
998 x_value => 'N',
999 x_level_name => 'SITE',
1000 x_level_value => null,
1001 x_level_value_app_id => '',
1002 x_level_value2 => null);
1003 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_STRATEGIES',
1004 x_value => 'N',
1005 x_level_name => 'SITE',
1006 x_level_value => null,
1007 x_level_value_app_id => '',
1008 x_level_value2 => null);
1009 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_STRATEGIES',
1010 x_value => l_Strategy_enabled,
1011 x_level_name => 'SITE',
1012 x_level_value => null,
1013 x_level_value_app_id => '',
1014 x_level_value2 => null);
1015 l_return := fnd_profile.save(x_name => 'IEX_QEN_STRATEGY',
1016 x_value => 'N',
1017 x_level_name => 'SITE',
1018 x_level_value => null,
1019 x_level_value_app_id => '',
1020 x_level_value2 => null);
1021 ELSIF l_business_level = 'DELINQUENCY' THEN
1022 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_DELINQUENCIES',
1023 x_value => 'N',
1024 x_level_name => 'SITE',
1025 x_level_value => null,
1026 x_level_value_app_id => '',
1027 x_level_value2 => null);
1028 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_DELINQUENCIES',
1029 x_value => 'N',
1030 x_level_name => 'SITE',
1031 x_level_value => null,
1032 x_level_value_app_id => '',
1033 x_level_value2 => null);
1034 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_DELINQUENCIES',
1035 x_value => 'N',
1036 x_level_name => 'SITE',
1037 x_level_value => null,
1038 x_level_value_app_id => '',
1039 x_level_value2 => null);
1040 l_return := fnd_profile.save(x_name => 'IEX_QEN_DELINQUENCIES',
1041 x_value => 'Y',
1042 x_level_name => 'SITE',
1043 x_level_value => null,
1044 x_level_value_app_id => '',
1045 x_level_value2 => null);
1046 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_PROMISES',
1047 x_value => 'N',
1048 x_level_name => 'SITE',
1049 x_level_value => null,
1050 x_level_value_app_id => '',
1051 x_level_value2 => null);
1052 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_PROMISES',
1053 x_value => 'N',
1054 x_level_name => 'SITE',
1055 x_level_value => null,
1056 x_level_value_app_id => '',
1057 x_level_value2 => null);
1058 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_PROMISES',
1059 x_value => 'N',
1060 x_level_name => 'SITE',
1061 x_level_value => null,
1062 x_level_value_app_id => '',
1063 x_level_value2 => null);
1064 l_return := fnd_profile.save(x_name => 'IEX_QEN_PROMISES',
1065 x_value => l_promise_enabled,
1066 x_level_name => 'SITE',
1067 x_level_value => null,
1068 x_level_value_app_id => '',
1069 x_level_value2 => null);
1070 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_STRATEGIES',
1071 x_value => 'N',
1072 x_level_name => 'SITE',
1073 x_level_value => null,
1074 x_level_value_app_id => '',
1075 x_level_value2 => null);
1076 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_STRATEGIES',
1077 x_value => 'N',
1078 x_level_name => 'SITE',
1079 x_level_value => null,
1080 x_level_value_app_id => '',
1081 x_level_value2 => null);
1082 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_STRATEGIES',
1083 x_value => 'N',
1084 x_level_name => 'SITE',
1085 x_level_value => null,
1086 x_level_value_app_id => '',
1087 x_level_value2 => null);
1088 l_return := fnd_profile.save(x_name => 'IEX_QEN_STRATEGY',
1089 x_value => l_Strategy_enabled,
1090 x_level_name => 'SITE',
1091 x_level_value => null,
1092 x_level_value_app_id => '',
1093 x_level_value2 => null);
1094 END IF;
1095 -- End fix bug #5142565-JYPARK-Change site level profile too
1096
1097 -- Begin fix bug #5142565-JYPARK-remove change profile at resp level
1098 -- FOR r_resp IN c_resp LOOP
1099 -- iex_debug_pub.logmessage('responsibility_key=' || r_resp.responsibility_key);
1100 -- IF l_business_level = 'CUSTOMER' THEN
1101 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_DELINQUENCIES',
1102 -- x_value => 'Y',
1103 -- x_level_name => 'RESP',
1104 -- x_level_value => r_resp.responsibility_id,
1105 -- x_level_value_app_id => '695',
1106 -- x_level_value2 => null);
1107 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_DELINQUENCIES',
1108 -- x_value => 'N',
1109 -- x_level_name => 'RESP',
1110 -- x_level_value => r_resp.responsibility_id,
1111 -- x_level_value_app_id => '695',
1112 -- x_level_value2 => null);
1113 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_DELINQUENCIES',
1114 -- x_value => 'N',
1115 -- x_level_name => 'RESP',
1116 -- x_level_value => r_resp.responsibility_id,
1117 -- x_level_value_app_id => '695',
1118 -- x_level_value2 => null);
1119 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_DELINQUENCIES',
1120 -- x_value => 'N',
1121 -- x_level_name => 'RESP',
1122 -- x_level_value => r_resp.responsibility_id,
1123 -- x_level_value_app_id => '695',
1124 -- x_level_value2 => null);
1125 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_PROMISES',
1126 -- x_value => l_promise_enabled,
1127 -- x_level_name => 'RESP',
1128 -- x_level_value => r_resp.responsibility_id,
1129 -- x_level_value_app_id => '695',
1130 -- x_level_value2 => null);
1131 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_PROMISES',
1132 -- x_value => 'N',
1133 -- x_level_name => 'RESP',
1134 -- x_level_value => r_resp.responsibility_id,
1135 -- x_level_value_app_id => '695',
1136 -- x_level_value2 => null);
1137 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_PROMISES',
1138 -- x_value => 'N',
1139 -- x_level_name => 'RESP',
1140 -- x_level_value => r_resp.responsibility_id,
1141 -- x_level_value_app_id => '695',
1142 -- x_level_value2 => null);
1143 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_PROMISES',
1144 -- x_value => 'N',
1145 -- x_level_name => 'RESP',
1146 -- x_level_value => r_resp.responsibility_id,
1147 -- x_level_value_app_id => '695',
1148 -- x_level_value2 => null);
1149 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_STRATEGIES',
1150 -- x_value => l_strategy_enabled,
1151 -- x_level_name => 'RESP',
1152 -- x_level_value => r_resp.responsibility_id,
1153 -- x_level_value_app_id => '695',
1154 -- x_level_value2 => null);
1155 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_STRATEGIES',
1156 -- x_value => 'N',
1157 -- x_level_name => 'RESP',
1158 -- x_level_value => r_resp.responsibility_id,
1159 -- x_level_value_app_id => '695',
1160 -- x_level_value2 => null);
1161 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_STRATEGIES',
1162 -- x_value => 'N',
1163 -- x_level_name => 'RESP',
1164 -- x_level_value => r_resp.responsibility_id,
1165 -- x_level_value_app_id => '695',
1166 -- x_level_value2 => null);
1167 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_STRATEGY',
1168 -- x_value => 'N',
1169 -- x_level_name => 'RESP',
1170 -- x_level_value => r_resp.responsibility_id,
1171 -- x_level_value_app_id => '695',
1172 -- x_level_value2 => null);
1173 -- ELSIF l_business_level = 'ACCOUNT' THEN
1174 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_DELINQUENCIES',
1175 -- x_value => 'N',
1176 -- x_level_name => 'RESP',
1177 -- x_level_value => r_resp.responsibility_id,
1178 -- x_level_value_app_id => '695',
1179 -- x_level_value2 => null);
1180 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_DELINQUENCIES',
1181 -- x_value => 'Y',
1182 -- x_level_name => 'RESP',
1183 -- x_level_value => r_resp.responsibility_id,
1184 -- x_level_value_app_id => '695',
1185 -- x_level_value2 => null);
1186 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_DELINQUENCIES',
1187 -- x_value => 'N',
1188 -- x_level_name => 'RESP',
1189 -- x_level_value => r_resp.responsibility_id,
1190 -- x_level_value_app_id => '695',
1191 -- x_level_value2 => null);
1192 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_DELINQUENCIES',
1193 -- x_value => 'N',
1194 -- x_level_name => 'RESP',
1195 -- x_level_value => r_resp.responsibility_id,
1196 -- x_level_value_app_id => '695',
1197 -- x_level_value2 => null);
1198 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_PROMISES',
1199 -- x_value => 'N',
1200 -- x_level_name => 'RESP',
1201 -- x_level_value => r_resp.responsibility_id,
1202 -- x_level_value_app_id => '695',
1203 -- x_level_value2 => null);
1204 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_PROMISES',
1205 -- x_value => l_promise_enabled,
1206 -- x_level_name => 'RESP',
1207 -- x_level_value => r_resp.responsibility_id,
1208 -- x_level_value_app_id => '695',
1209 -- x_level_value2 => null);
1210 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_PROMISES',
1211 -- x_value => 'N',
1212 -- x_level_name => 'RESP',
1213 -- x_level_value => r_resp.responsibility_id,
1214 -- x_level_value_app_id => '695',
1215 -- x_level_value2 => null);
1216 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_PROMISES',
1217 -- x_value => 'N',
1218 -- x_level_name => 'RESP',
1219 -- x_level_value => r_resp.responsibility_id,
1220 -- x_level_value_app_id => '695',
1221 -- x_level_value2 => null);
1222 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_STRATEGIES',
1223 -- x_value => 'N',
1224 -- x_level_name => 'RESP',
1225 -- x_level_value => r_resp.responsibility_id,
1226 -- x_level_value_app_id => '695',
1227 -- x_level_value2 => null);
1228 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_STRATEGIES',
1229 -- x_value => l_Strategy_enabled,
1230 -- x_level_name => 'RESP',
1231 -- x_level_value => r_resp.responsibility_id,
1232 -- x_level_value_app_id => '695',
1233 -- x_level_value2 => null);
1234 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_STRATEGIES',
1235 -- x_value => 'N',
1236 -- x_level_name => 'RESP',
1237 -- x_level_value => r_resp.responsibility_id,
1238 -- x_level_value_app_id => '695',
1239 -- x_level_value2 => null);
1240 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_STRATEGY',
1241 -- x_value => 'N',
1242 -- x_level_name => 'RESP',
1243 -- x_level_value => r_resp.responsibility_id,
1244 -- x_level_value_app_id => '695',
1245 -- x_level_value2 => null);
1246 -- ELSIF l_business_level = 'BILL_TO' THEN
1247 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_DELINQUENCIES',
1248 -- x_value => 'N',
1249 -- x_level_name => 'RESP',
1250 -- x_level_value => r_resp.responsibility_id,
1251 -- x_level_value_app_id => '695',
1252 -- x_level_value2 => null);
1253 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_DELINQUENCIES',
1254 -- x_value => 'N',
1255 -- x_level_name => 'RESP',
1256 -- x_level_value => r_resp.responsibility_id,
1257 -- x_level_value_app_id => '695',
1258 -- x_level_value2 => null);
1259 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_DELINQUENCIES',
1260 -- x_value => 'Y',
1261 -- x_level_name => 'RESP',
1262 -- x_level_value => r_resp.responsibility_id,
1263 -- x_level_value_app_id => '695',
1264 -- x_level_value2 => null);
1265 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_DELINQUENCIES',
1266 -- x_value => 'N',
1267 -- x_level_name => 'RESP',
1268 -- x_level_value => r_resp.responsibility_id,
1269 -- x_level_value_app_id => '695',
1270 -- x_level_value2 => null);
1271 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_PROMISES',
1272 -- x_value => 'N',
1273 -- x_level_name => 'RESP',
1274 -- x_level_value => r_resp.responsibility_id,
1275 -- x_level_value_app_id => '695',
1276 -- x_level_value2 => null);
1277 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_PROMISES',
1278 -- x_value => 'N',
1279 -- x_level_name => 'RESP',
1280 -- x_level_value => r_resp.responsibility_id,
1281 -- x_level_value_app_id => '695',
1282 -- x_level_value2 => null);
1283 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_PROMISES',
1284 -- x_value => l_promise_enabled,
1285 -- x_level_name => 'RESP',
1286 -- x_level_value => r_resp.responsibility_id,
1287 -- x_level_value_app_id => '695',
1288 -- x_level_value2 => null);
1289 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_PROMISES',
1290 -- x_value => 'N',
1291 -- x_level_name => 'RESP',
1292 -- x_level_value => r_resp.responsibility_id,
1293 -- x_level_value_app_id => '695',
1294 -- x_level_value2 => null);
1295 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_STRATEGIES',
1296 -- x_value => 'N',
1297 -- x_level_name => 'RESP',
1298 -- x_level_value => r_resp.responsibility_id,
1299 -- x_level_value_app_id => '695',
1300 -- x_level_value2 => null);
1301 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_STRATEGIES',
1302 -- x_value => 'N',
1303 -- x_level_name => 'RESP',
1304 -- x_level_value => r_resp.responsibility_id,
1305 -- x_level_value_app_id => '695',
1306 -- x_level_value2 => null);
1307 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_STRATEGIES',
1308 -- x_value => l_Strategy_enabled,
1309 -- x_level_name => 'RESP',
1310 -- x_level_value => r_resp.responsibility_id,
1311 -- x_level_value_app_id => '695',
1312 -- x_level_value2 => null);
1313 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_STRATEGY',
1314 -- x_value => 'N',
1315 -- x_level_name => 'RESP',
1316 -- x_level_value => r_resp.responsibility_id,
1317 -- x_level_value_app_id => '695',
1318 -- x_level_value2 => null);
1319 -- ELSIF l_business_level = 'DELINQUENCY' THEN
1320 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_DELINQUENCIES',
1321 -- x_value => 'N',
1322 -- x_level_name => 'RESP',
1323 -- x_level_value => r_resp.responsibility_id,
1324 -- x_level_value_app_id => '695',
1325 -- x_level_value2 => null);
1326 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_DELINQUENCIES',
1327 -- x_value => 'N',
1328 -- x_level_name => 'RESP',
1329 -- x_level_value => r_resp.responsibility_id,
1330 -- x_level_value_app_id => '695',
1331 -- x_level_value2 => null);
1332 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_DELINQUENCIES',
1333 -- x_value => 'N',
1334 -- x_level_name => 'RESP',
1335 -- x_level_value => r_resp.responsibility_id,
1336 -- x_level_value_app_id => '695',
1337 -- x_level_value2 => null);
1338 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_DELINQUENCIES',
1339 -- x_value => 'Y',
1340 -- x_level_name => 'RESP',
1341 -- x_level_value => r_resp.responsibility_id,
1342 -- x_level_value_app_id => '695',
1343 -- x_level_value2 => null);
1344 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_PROMISES',
1345 -- x_value => 'N',
1346 -- x_level_name => 'RESP',
1347 -- x_level_value => r_resp.responsibility_id,
1348 -- x_level_value_app_id => '695',
1349 -- x_level_value2 => null);
1350 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_PROMISES',
1351 -- x_value => 'N',
1352 -- x_level_name => 'RESP',
1353 -- x_level_value => r_resp.responsibility_id,
1354 -- x_level_value_app_id => '695',
1355 -- x_level_value2 => null);
1356 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_PROMISES',
1357 -- x_value => 'N',
1358 -- x_level_name => 'RESP',
1359 -- x_level_value => r_resp.responsibility_id,
1360 -- x_level_value_app_id => '695',
1361 -- x_level_value2 => null);
1362 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_PROMISES',
1363 -- x_value => l_promise_enabled,
1364 -- x_level_name => 'RESP',
1365 -- x_level_value => r_resp.responsibility_id,
1366 -- x_level_value_app_id => '695',
1367 -- x_level_value2 => null);
1368 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_STRATEGIES',
1369 -- x_value => 'N',
1370 -- x_level_name => 'RESP',
1371 -- x_level_value => r_resp.responsibility_id,
1372 -- x_level_value_app_id => '695',
1373 -- x_level_value2 => null);
1374 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_STRATEGIES',
1375 -- x_value => 'N',
1376 -- x_level_name => 'RESP',
1377 -- x_level_value => r_resp.responsibility_id,
1378 -- x_level_value_app_id => '695',
1379 -- x_level_value2 => null);
1380 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_STRATEGIES',
1381 -- x_value => 'N',
1382 -- x_level_name => 'RESP',
1383 -- x_level_value => r_resp.responsibility_id,
1384 -- x_level_value_app_id => '695',
1385 -- x_level_value2 => null);
1386 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_STRATEGY',
1387 -- x_value => l_Strategy_enabled,
1388 -- x_level_name => 'RESP',
1389 -- x_level_value => r_resp.responsibility_id,
1390 -- x_level_value_app_id => '695',
1391 -- x_level_value2 => null);
1392 -- END IF;
1393 -- END LOOP;
1394 -- End fix bug #5142565-JYPARK-remove change profile at resp level
1395 EXCEPTION
1396 WHEN OTHERS THEN
1397 x_return_status := 'F';
1398 fnd_message.set_name ('IEX', 'IEX_ADMIN_UNKNOWN_ERROR');
1399 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
1400 fnd_msg_pub.add;
1401 END CHANGE_BUSINESS_LEVEL;
1402
1403
1404 PROCEDURE UPDATE_CHECKLIST_ITEM_BY_NAME(
1405 p_checklist_item_name IN VARCHAR2,
1406 x_return_status OUT NOCOPY VARCHAR2) IS
1407 CURSOR c_checklist IS
1408 SELECT checklist_item_id
1409 FROM iex_checklist_items_b
1410 WHERE checklist_item_name = p_checklist_item_name;
1411
1412 l_checklist_item_id NUMBER;
1413 BEGIN
1414 OPEN c_checklist;
1415 FETCH c_checklist INTO l_checklist_item_id;
1416 CLOSE c_checklist;
1417
1418
1419 update_checklist_item(l_checklist_item_id, x_return_status);
1420 EXCEPTION
1421 WHEN OTHERS THEN
1422 x_return_status := 'F';
1423 fnd_message.set_name ('IEX', 'IEX_ADMIN_UNKNOWN_ERROR');
1424
1425 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
1426
1427 fnd_msg_pub.add;
1428 END UPDATE_CHECKLIST_ITEM_BY_NAME;
1429
1430 -- Start for bug 8708271 pnaveenk multi level strategy
1431 PROCEDURE CHANGE_MULTIPLE_LEVEL(
1432 p_account_level IN VARCHAR2,
1433 p_billto_level IN VARCHAR2,
1434 p_customer_level IN VARCHAR2,
1435 p_delinquency_level IN VARCHAR2,
1436 p_override_party_level IN VARCHAR2,
1437 p_ou_running_level IN VARCHAR2,
1438 x_return_status OUT NOCOPY VARCHAR2) IS
1439
1440 l_return BOOLEAN;
1441 l_promise_enabled varchar2(1);
1442 l_strategy_enabled varchar2(1);
1443 p_promise_enabled varchar2(1);
1444 p_collections_methods varchar2(20);
1445
1446 cursor c_promise_str is
1447 select promise_enabled, collections_methods
1448 from iex_questionnaire_items;
1449
1450 BEGIN
1451
1452 open c_promise_str;
1453 fetch c_promise_str into p_promise_enabled,p_collections_methods;
1454 close c_promise_str;
1455
1456 l_promise_enabled := NVL(p_promise_enabled, 'Y');
1457 IF NVL(p_collections_methods, 'DUNNING') = 'DUNNING' THEN
1458 l_strategy_enabled := 'N';
1459 ELSE
1460 l_strategy_enabled := 'Y';
1461 END IF;
1462
1463 iex_debug_pub.logmessage(' Start IEX_CHECKLIST_UTILITY.CHANGE_MULTIPLE_LEVEL procedure. Setting profiles');
1464 iex_debug_pub.logmessage (' Values of levels set -- Customer Level' || p_customer_level || 'Account Level' || p_account_level || 'BillTo Level' || p_billto_level);
1465 iex_debug_pub.logmessage ( ' Delinquency Level ' || p_delinquency_level || 'Party Override' || p_override_party_level || 'Operating Unit Override' || p_ou_running_level);
1466 iex_debug_pub.logmessage ( ' Promise enabled ' || l_promise_enabled || 'Strategy enabled ' || l_strategy_enabled);
1467
1468 IF p_customer_level = 'Y' then
1469 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_DELINQUENCIES',
1470 x_value => 'Y',
1471 x_level_name => 'SITE',
1472 x_level_value => null,
1473 x_level_value_app_id => '',
1474 x_level_value2 => null);
1475 IF l_return = FALSE then
1476 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Customer View Delinquencies.');
1477 END IF;
1478 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_PROMISES',
1479 x_value => l_promise_enabled,
1480 x_level_name => 'SITE',
1481 x_level_value => null,
1482 x_level_value_app_id => '',
1483 x_level_value2 => null);
1484 IF l_return = FALSE then
1485 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Customer View Promises.');
1486 END IF;
1487 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_STRATEGIES',
1488 x_value => l_strategy_enabled,
1489 x_level_name => 'SITE',
1490 x_level_value => null,
1491 x_level_value_app_id => '',
1492 x_level_value2 => null);
1493 IF l_return = FALSE then
1494 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Customer View Strategies.');
1495 END IF;
1496 ELSE
1497 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_DELINQUENCIES',
1498 x_value => 'N',
1499 x_level_name => 'SITE',
1500 x_level_value => null,
1501 x_level_value_app_id => '',
1502 x_level_value2 => null);
1503 IF l_return = FALSE then
1504 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Customer View Delinquencies.');
1505 END IF;
1506 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_PROMISES',
1507 x_value => 'N',
1508 x_level_name => 'SITE',
1509 x_level_value => null,
1510 x_level_value_app_id => '',
1511 x_level_value2 => null);
1512 IF l_return = FALSE then
1513 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Customer View Promises.');
1514 END IF;
1515 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_STRATEGIES',
1516 x_value => 'N',
1517 x_level_name => 'SITE',
1518 x_level_value => null,
1519 x_level_value_app_id => '',
1520 x_level_value2 => null);
1521 IF l_return = FALSE then
1522 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Customer View Strategies.');
1523 END IF;
1524
1525 END IF;
1526
1527 IF p_account_level = 'Y' then
1528 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_DELINQUENCIES',
1529 x_value => 'Y',
1530 x_level_name => 'SITE',
1531 x_level_value => null,
1532 x_level_value_app_id => '',
1533 x_level_value2 => null);
1534 IF l_return = FALSE then
1535 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Account View Delinquencies.');
1536 END IF;
1537 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_PROMISES',
1538 x_value => l_promise_enabled,
1539 x_level_name => 'SITE',
1540 x_level_value => null,
1541 x_level_value_app_id => '',
1542 x_level_value2 => null);
1543 IF l_return = FALSE then
1544 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Account View Promises.');
1545 END IF;
1546 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_STRATEGIES',
1547 x_value => l_Strategy_enabled,
1548 x_level_name => 'SITE',
1549 x_level_value => null,
1550 x_level_value_app_id => '',
1551 x_level_value2 => null);
1552 IF l_return = FALSE then
1553 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Account View Strategies.');
1554 END IF;
1555
1556 ELSE
1557 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_DELINQUENCIES',
1558 x_value => 'N',
1559 x_level_name => 'SITE',
1560 x_level_value => null,
1561 x_level_value_app_id => '',
1562 x_level_value2 => null);
1563 IF l_return = FALSE then
1564 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Account View Delinquencies.');
1565 END IF;
1566 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_PROMISES',
1567 x_value => 'N',
1568 x_level_name => 'SITE',
1569 x_level_value => null,
1570 x_level_value_app_id => '',
1571 x_level_value2 => null);
1572 IF l_return = FALSE then
1573 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Account View Promises.');
1574 END IF;
1575 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_STRATEGIES',
1576 x_value => 'N',
1577 x_level_name => 'SITE',
1578 x_level_value => null,
1579 x_level_value_app_id => '',
1580 x_level_value2 => null);
1581 IF l_return = FALSE then
1582 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Account View Strategies.');
1583 END IF;
1584 END IF;
1585
1586 IF p_billto_level = 'Y' then
1587 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_DELINQUENCIES',
1588 x_value => 'Y',
1589 x_level_name => 'SITE',
1590 x_level_value => null,
1591 x_level_value_app_id => '',
1592 x_level_value2 => null);
1593 IF l_return = FALSE then
1594 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Bill-to View Delinquencies.');
1595 END IF;
1596 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_PROMISES',
1597 x_value => l_promise_enabled,
1598 x_level_name => 'SITE',
1599 x_level_value => null,
1600 x_level_value_app_id => '',
1601 x_level_value2 => null);
1602 IF l_return = FALSE then
1603 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Bill-to View Promises.');
1604 END IF;
1605 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_STRATEGIES',
1606 x_value => l_Strategy_enabled,
1607 x_level_name => 'SITE',
1608 x_level_value => null,
1609 x_level_value_app_id => '',
1610 x_level_value2 => null);
1611 IF l_return = FALSE then
1612 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Bill-to View Strategies.');
1613 END IF;
1614
1615 ELSE
1616
1617 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_DELINQUENCIES',
1618 x_value => 'N',
1619 x_level_name => 'SITE',
1620 x_level_value => null,
1621 x_level_value_app_id => '',
1622 x_level_value2 => null);
1623 IF l_return = FALSE then
1624 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Bill-to View Delinquencies.');
1625 END IF;
1626 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_PROMISES',
1627 x_value => 'N',
1628 x_level_name => 'SITE',
1629 x_level_value => null,
1630 x_level_value_app_id => '',
1631 x_level_value2 => null);
1632 IF l_return = FALSE then
1633 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Bill-to View Promises.');
1634 END IF;
1635 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_STRATEGIES',
1636 x_value => 'N',
1637 x_level_name => 'SITE',
1638 x_level_value => null,
1639 x_level_value_app_id => '',
1640 x_level_value2 => null);
1641 IF l_return = FALSE then
1642 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Bill-to View Strategies.');
1643 END IF;
1644
1645 END IF;
1646
1647 IF p_delinquency_level = 'Y' then
1648 l_return := fnd_profile.save(x_name => 'IEX_QEN_DELINQUENCIES',
1649 x_value => 'Y',
1650 x_level_name => 'SITE',
1651 x_level_value => null,
1652 x_level_value_app_id => '',
1653 x_level_value2 => null);
1654 IF l_return = FALSE then
1655 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Delinquency View Delinquencies.');
1656 END IF;
1657 l_return := fnd_profile.save(x_name => 'IEX_QEN_PROMISES',
1658 x_value => l_promise_enabled,
1659 x_level_name => 'SITE',
1660 x_level_value => null,
1661 x_level_value_app_id => '',
1662 x_level_value2 => null);
1663 IF l_return = FALSE then
1664 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Delinquency View Promises.');
1665 END IF;
1666 l_return := fnd_profile.save(x_name => 'IEX_QEN_STRATEGY',
1667 x_value => l_Strategy_enabled,
1668 x_level_name => 'SITE',
1669 x_level_value => null,
1670 x_level_value_app_id => '',
1671 x_level_value2 => null);
1672 IF l_return = FALSE then
1673 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Delinquency View Strategies.');
1674 END IF;
1675
1676 ELSE
1677
1678 l_return := fnd_profile.save(x_name => 'IEX_QEN_DELINQUENCIES',
1679 x_value => 'N',
1680 x_level_name => 'SITE',
1681 x_level_value => null,
1682 x_level_value_app_id => '',
1683 x_level_value2 => null);
1684 IF l_return = FALSE then
1685 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Delinquency View Delinquencies.');
1686 END IF;
1687 l_return := fnd_profile.save(x_name => 'IEX_QEN_PROMISES',
1688 x_value => 'N',
1689 x_level_name => 'SITE',
1690 x_level_value => null,
1691 x_level_value_app_id => '',
1692 x_level_value2 => null);
1693 IF l_return = FALSE then
1694 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Delinquency View Promises.');
1695 END IF;
1696 l_return := fnd_profile.save(x_name => 'IEX_QEN_STRATEGY',
1697 x_value => 'N',
1698 x_level_name => 'SITE',
1699 x_level_value => null,
1700 x_level_value_app_id => '',
1701 x_level_value2 => null);
1702 IF l_return = FALSE then
1703 iex_debug_pub.logmessage('Failed to set the profile IEU: Queue: Delinquency View Strategies.');
1704 END IF;
1705
1706 END IF;
1707
1708 IF p_ou_running_level = 'Y' then
1709 l_return := fnd_profile.save(x_name => 'IEX_PROC_STR_ORG',
1710 x_value => 'Y',
1711 x_level_name => 'SITE',
1712 x_level_value => null,
1713 x_level_value_app_id => '',
1714 x_level_value2 => null);
1715 IF l_return = FALSE then
1716 iex_debug_pub.logmessage('Failed to set the profile IEX: Process Strategies by Operating Unit.');
1717 END IF;
1718 -- bug 9818211 begin
1719 elsif p_ou_running_level = 'N' then
1720 l_return := fnd_profile.save(x_name => 'IEX_PROC_STR_ORG',
1721 x_value => 'N',
1722 x_level_name => 'SITE',
1723 x_level_value => null,
1724 x_level_value_app_id => '',
1725 x_level_value2 => null);
1726 IF l_return = FALSE then
1727 iex_debug_pub.logmessage('Failed to set the profile IEX: Process Strategies by Operating Unit.');
1728 END IF;
1729 -- end bug 9818211
1730 END IF;
1731 x_return_status := 'S';
1732 iex_debug_pub.logmessage(' End procedure IEX_CHECKLIST_UTILITY.CHANGE_MULTIPLE_LEVEL . Successfully set the profile values');
1733
1734 EXCEPTION
1735 WHEN OTHERS THEN
1736 x_return_status := 'F';
1737 fnd_message.set_name ('IEX', 'IEX_ADMIN_UNKNOWN_ERROR');
1738 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
1739 fnd_msg_pub.add;
1740
1741
1742 END CHANGE_MULTIPLE_LEVEL;
1743
1744 PROCEDURE UPDATE_MLSETUP IS
1745
1746 cursor c_questionnaire_details is
1747 select business_level, using_customer_level, using_account_level, using_billto_level,
1748 using_delinquency_level, define_ou_running_level , define_party_running_level
1749 from iex_questionnaire_items;
1750
1751 l_business_level varchar2(20);
1752 l_using_customer_level varchar2(1);
1753 l_using_account_level varchar2(1);
1754 l_using_billto_level varchar2(1);
1755 l_using_delinquency_level varchar2(1);
1756 l_define_ou_running_level varchar2(1);
1757 l_define_party_running_level varchar2(1);
1758 c_str_upd varchar2(2000);
1759 l_count number;
1760
1761 l_last_updated_by number := FND_GLOBAL.USER_ID;
1762 l_last_update_login number := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),0);
1763
1764
1765 Begin
1766
1767 open c_questionnaire_details;
1768 fetch c_questionnaire_details into l_business_level,l_using_customer_level,l_using_account_level,l_using_billto_level,
1769 l_using_delinquency_level,l_define_ou_running_level,l_define_party_running_level;
1770 close c_questionnaire_details;
1771 iex_debug_pub.logmessage(' In procedure IEX_CHECKLIST_UTILITY.UPDATE_MLSETUP Begin updating multi level strategy set up in questionnaire table');
1772
1773 c_str_upd := 'update iex_questionnaire_items set ';
1774 l_count := 0;
1775
1776 IF l_using_customer_level is null then
1777
1778 if l_business_level = 'CUSTOMER' then
1779 c_str_upd := c_str_upd || ' using_customer_level = ''Y'' ';
1780 else
1781 c_str_upd := c_str_upd || ' using_customer_level = ''N'' ';
1782 end if;
1783 l_count := l_count + 1;
1784
1785 END IF;
1786
1787 IF l_using_account_level is null then
1788 if l_count > 0 then
1789 c_str_upd := c_str_upd || ' , ';
1790 end if;
1791 if l_business_level = 'ACCOUNT' then
1792 c_str_upd := c_str_upd || ' using_account_level = ''Y'' ';
1793 else
1794 c_str_upd := c_str_upd || ' using_account_level = ''N'' ';
1795 end if;
1796 l_count := l_count + 1;
1797
1798 END IF;
1799
1800 IF l_using_billto_level is null then
1801 if l_count > 0 then
1802 c_str_upd := c_str_upd || ' , ';
1803 end if;
1804 if l_business_level = 'BILL_TO' then
1805 c_str_upd := c_str_upd || ' using_billto_level = ''Y'' ';
1806 else
1807 c_str_upd := c_str_upd || ' using_billto_level = ''N'' ';
1808 end if;
1809 l_count := l_count + 1;
1810
1811 END IF;
1812
1813 IF l_using_delinquency_level is null then
1814 if l_count > 0 then
1815 c_str_upd := c_str_upd || ' , ';
1816 end if;
1817 if l_business_level = 'DELINQUENCY' then
1818 c_str_upd := c_str_upd || ' using_delinquency_level = ''Y'' ';
1819 else
1820 c_str_upd := c_str_upd || ' using_delinquency_level = ''N'' ';
1821 end if;
1822 l_count := l_count + 1;
1823
1824 END IF;
1825
1826 IF l_define_ou_running_level is null then
1827 if l_count > 0 then
1828 c_str_upd := c_str_upd || ' , ';
1829 end if;
1830 if l_business_level = 'DELINQUENCY' then
1831 c_str_upd := c_str_upd || ' define_ou_running_level = ''Y'' ';
1832 else
1833 c_str_upd := c_str_upd || ' define_ou_running_level = ''N'' ';
1834 end if;
1835 l_count := l_count + 1;
1836
1837 END IF;
1838
1839 IF l_define_party_running_level is null then
1840 if l_count > 0 then
1841 c_str_upd := c_str_upd || ' , ';
1842 end if;
1843 if l_business_level = 'DELINQUENCY' then
1844 c_str_upd := c_str_upd || ' define_party_running_level = ''Y'' ';
1845 else
1846 c_str_upd := c_str_upd || ' define_party_running_level = ''N'' ';
1847 end if;
1848 l_count := l_count + 1;
1849
1850 END IF;
1851 Begin
1852 IF l_count > 0 then
1853
1854 c_str_upd := c_str_upd || ' , last_update_date = sysdate , last_updated_by = ' || l_last_updated_by || ' , last_update_login = ' || l_last_update_login;
1855 iex_debug_pub.logmessage( ' Update Statement constructed before execution ' || c_str_upd);
1856 execute immediate c_str_upd;
1857 commit;
1858 END IF;
1859
1860 Exception
1861 when others then
1862 iex_debug_pub.logmessage( ' Exception in executing SQL statement ' );
1863 End;
1864 iex_debug_pub.logmessage('End Procedure IEX_CHECKLIST_UTILITY.UPDATE_MLSETUP updating multi level set up in questionnaire table');
1865
1866 Exception
1867 When Others then
1868 iex_debug_pub.logmessage( ' Exception in updating ml set up' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
1869
1870 End UPDATE_MLSETUP;
1871
1872
1873 -- End for bug 8708271 multi level strategy
1874
1875 BEGIN
1876 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
1877 G_APPL_ID := FND_GLOBAL.Prog_Appl_Id;
1878 G_LOGIN_ID := FND_GLOBAL.Conc_Login_Id;
1879 G_PROGRAM_ID := FND_GLOBAL.Conc_Program_Id;
1880 G_USER_ID := FND_GLOBAL.User_Id;
1881 G_REQUEST_ID := FND_GLOBAL.Conc_Request_Id;
1882 END IEX_CHECKLIST_UTILITY;