[Home] [Help]
PACKAGE BODY: APPS.IEX_CHECKLIST_UTILITY
Source
1 PACKAGE BODY IEX_CHECKLIST_UTILITY AS
2 /* $Header: iexvchkb.pls 120.11 2008/06/20 15:58:41 kasreeni 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 BEGIN
222 IEX_DEBUG_PUB.logmessage('IN checkUpgradeStrategie ');
223 Open c_CheckStrategyGroups;
224 FETCH C_CheckStrategyGroups INTO l_Upgrades;
225 close c_CheckStrategyGroups;
226 if l_Upgrades > 0 then
227 IEX_DEBUG_PUB.logmessage('Inserting into StrategyGroups');
228 INSERT INTO IEX_STRATEGY_TEMPLATE_GROUPS (
229 OBJECT_VERSION_NUMBER,
230 CREATED_BY,
231 CREATION_DATE,
232 LAST_UPDATE_DATE,
233 LAST_UPDATED_BY,
234 LAST_UPDATE_LOGIN,
235 REQUEST_ID,
236 GROUP_ID,
237 GROUP_NAME,
238 STRATEGY_RANK,
239 ENABLED_FLAG,
240 CATEGORY_TYPE,
241 CHANGE_STRATEGY_YN,
242 CHECK_LIST_YN,
243 CHECK_LIST_TEMP_ID,
244 VALID_FROM_DT,
245 VALID_TO_DT,
246 OBJECT_FILTER_ID,
247 STRATEGY_LEVEL,
248 SCORE_TOLERANCE,
249 STRATEGY_TEMP_ID
250 )
251 SELECT
252 1,
253 ISTL.CREATED_BY,
254 ISTL.CREATION_DATE,
255 ISTL.LAST_UPDATE_DATE,
256 ISTL.LAST_UPDATED_BY,
257 ISTL.LAST_UPDATE_LOGIN,
258 ISTL.REQUEST_ID,
259 IEX_STRATEGY_TEMPLATE_GROUPS_S.NEXTVAL,
260 ISTL.STRATEGY_NAME,
261 ISTL.STRATEGY_RANK,
262 ISTL.ENABLED_FLAG,
263 ISTL.CATEGORY_TYPE,
264 ISTL.CHANGE_STRATEGY_YN,
265 ISTL.CHECK_LIST_YN,
266 ISTL.CHECK_LIST_TEMP_ID ,
267 ISTL.VALID_FROM_DT,
268 ISTL.VALID_TO_DT,
269 ISTL.OBJECT_FILTER_ID,
270 ISTL.STRATEGY_LEVEL,
271 ISTL.SCORE_TOLERANCE,
272 ISTL.STRATEGY_TEMP_ID
273 FROM IEX_STRATEGY_TEMPLATES_VL ISTL
274 WHERE istl.strategy_temp_id >= 10000
275 AND (istl.strategy_temp_group_id is NULL or istl.strategy_temp_group_id = 0)
276 AND NOT EXISTS (SELECT 1 FROM IEX_STRATEGY_TEMPLATE_GROUPS istg
277 WHERE istg.STRATEGY_TEMP_ID = ISTL.STRATEGY_TEMP_ID);
278
279 UPDATE IEX_STRATEGY_TEMPLATES_b istl
280 SET istl.STRATEGY_TEMP_GROUP_ID =
281 (SELECT istg.GROUP_ID FROM IEX_STRATEGY_TEMPLATE_GROUPS istg
282 WHERE istg.strategy_temp_id = istl.strategy_temp_id)
283 WHERE istl.strategy_temp_id >= 10000
284 AND (istl.strategy_temp_group_id is NULL or istl.strategy_temp_group_id = 0);
285
286 commit;
287
288 end if;
289 IEX_DEBUG_PUB.logmessage('End checkUpgradeStrategie ');
290 EXCEPTION
291 WHEN OTHERS THEN
292 x_return_status := 'F';
293 fnd_message.set_name ('IEX', 'IEX_ADMIN_UNKNOWN_ERROR');
294 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
295 fnd_msg_pub.add;
296
297 END checkUpgradeStrategies;
298
299 -- End kasreeni 12-16-2005 4887338
300
301 PROCEDURE UPDATE_CHECKLIST_ITEM(
302 p_checklist_item_id IN NUMBER,
303 x_return_status OUT NOCOPY VARCHAR2) IS
304 CURSOR c_general_info IS
305 SELECT fnd_profile.value_specific('IEX_COLLECTIONS_BUCKET_NAME', -1, -1, -1, -1, -1) COLLECTIONS_BUCKET,
306 fnd_profile.value_specific('IEX_ENABLE_CUST_STATUS_EVENT', -1, -1, -1, -1, -1) CUST_STATUS_EVENT,
307 fnd_profile.value_specific('IEX_CUST_ACCESS', -1, -1, -1, -1, -1) WORK_QUEUE_ACCESS,
308 fnd_profile.value_specific('IEX_ACCESS_LEVEL', -1, -1, -1, -1, -1) ACCESS_LEVEL,
309 fnd_profile.value_specific('IEX_COLLECTIONS_RATE_TYPE', -1, -1, -1, -1, -1) RATE_TYPE
310 -- fnd_profile.value_specific('ACCOUNT_INTERACTION_ACTIVITY', -1, -1, -1, -1, -1) ACCOUNT_ACTIVITY,
311 -- fnd_profile.value_specific('IEX_DELINQUENCY_ACTIVITY', -1, -1, -1, -1, -1) DELINQUENCY_ACTIVITY,
312 -- fnd_profile.value_specific('DISPUTE_INTERACTION_ACTIVITY', -1, -1, -1, -1, -1) DISPUTE_ACTIVITY,
313 -- fnd_profile.value_specific('IEX_ADJUSTMENT_ACTIVITY', -1, -1, -1, -1, -1) ADJUSTMENT_ACTIVITY,
314 -- fnd_profile.value_specific('PAYMENT_INTERACTION_ACTIVITY', -1, -1, -1, -1, -1) PAYMENT_ACTIVITY,
315 -- fnd_profile.value_specific('PROMISE_INTERACTION_ACTIVITY', -1, -1, -1, -1, -1) PROMISE_ACTIVITY,
316 -- fnd_profile.value_specific('IEX_STRATEGY_ACTIVITY', -1, -1, -1, -1, -1) STRATEGY_ACTIVITY,
317 -- fnd_profile.value_specific('IEX_CREDIT_HOLD', -1, -1, -1, -1, -1) credit_hold,
318 -- fnd_profile.value_specific('IEX_SERVICE_HOLD', -1, -1, -1, -1, -1) service_hold_delin
319 FROM dual;
320
321 l_general_info_row c_general_info%ROWTYPE;
322 l_status VARCHAR2(30);
323
324 BEGIN
325 x_return_status := 'S';
326 l_status := 'COMPLETE';
327
328 IEX_DEBUG_PUB.logmessage('Update CheckList ' || p_checklist_item_id);
329
330 -- Begin kasreeni 12-16-2005 4887338
331 if (p_checklist_item_id = 100 ) then
332 IEX_DEBUG_PUB.logmessage('Calling checkUpgradeStrategie ');
333 checkUpgradeStrategies(x_return_status);
334 return ;
335 end if;
336 -- End kasreeni 12-16-2005 4887338
337
338 IF p_checklist_item_id = 3 THEN
339 OPEN c_general_info;
340 FETCH c_general_info INTO l_general_info_row;
341 CLOSE c_general_info;
342
343 IF l_general_info_row.collections_bucket IS NOT NULL AND
344 -- l_general_info_row.cust_status_event IS NOT NULL AND
345 l_general_info_row.work_queue_access IS NOT NULL AND
346 l_general_info_row.access_level IS NOT NULL AND
347 l_general_info_row.rate_type IS NOT NULL
348 -- l_general_info_row.account_activity IS NOT NULL AND
349 -- l_general_info_row.delinquency_activity IS NOT NULL AND
350 -- l_general_info_row.dispute_activity IS NOT NULL AND
351 -- l_general_info_row.adjustment_activity IS NOT NULL AND
352 -- l_general_info_row.payment_activity IS NOT NULL AND
353 -- l_general_info_row.promise_activity IS NOT NULL AND
354 -- l_general_info_row.strategy_activity IS NOT NULL AND
355 -- l_general_info_row.credit_hold IS NOT NULL AND
356 -- l_general_info_row.service_hold_delin IS NOT NULL THEN
357 THEN
358 l_status := 'COMPLETE';
359 ELSE
360 l_status := 'INPROGRESS';
361 END IF;
362
363 iex_debug_pub.logmessage('l_status=' || l_status);
364
365 UPDATE iex_checklist_items_b
366 SET status = l_status, task_last_modified_date = SYSDATE, last_update_date = SYSDATE,
367 last_updated_by = G_USER_ID, last_update_login = G_LOGIN_ID
368 WHERE checklist_item_id = p_checklist_item_id;
369 ELSE
370 UPDATE iex_checklist_items_b
371 SET status = l_status, task_last_modified_date = SYSDATE, last_update_date = SYSDATE,
372 last_updated_by = G_USER_ID, last_update_login = G_LOGIN_ID
373 WHERE checklist_item_id = p_checklist_item_id;
374
375 END IF;
376 EXCEPTION
377 WHEN OTHERS THEN
378 x_return_status := 'F';
379 END UPDATE_CHECKLIST_ITEM;
380
381 PROCEDURE CHANGE_LEASING_SETUP(
382 p_leasing_enabled IN VARCHAR2,
383 x_return_status OUT NOCOPY VARCHAR2) IS
384 CURSOR c_resp IS
385 SELECT resp.application_id, resp.responsibility_id, resp.menu_id
386 FROM fnd_responsibility resp, fnd_menus menu
387 WHERE resp.menu_id = menu.menu_id
388 AND menu.menu_name = 'IEX_COLLECTIONS_AGENT'
389 -- Begin fix bug #4930424-remove TABLE ACCESS FULL
390 AND resp.application_id = 695;
391 -- End fix bug #4930424-remove TABLE ACCESS FULL
392
393 CURSOR c_funct(p_responsibility_id NUMBER, p_function_name VARCHAR2) IS
394 SELECT rf.action_id, ff.function_id
395 FROM fnd_resp_functions rf, fnd_form_functions ff
396 WHERE rf.responsibility_id(+) = p_responsibility_id
397 AND ff.function_name = p_function_name
398 AND rf.action_id(+) = ff.function_id
399 AND rf.rule_type(+) = 'F';
400
401 r_funct c_funct%ROWTYPE;
402 l_function_name VARCHAR2(30);
403 l_rowid VARCHAR(1000);
404
405 CURSOR c_lookup IS
406 SELECT lookup_type, lookup_code, meaning,
407 description, enabled_flag, start_date_active, end_date_active,
408 territory_code, attribute_category, attribute1, attribute2,
409 attribute3, attribute4, attribute5, attribute6,
410 attribute7, attribute8, attribute9, attribute10,
411 attribute11, attribute12, attribute13, attribute14, attribute15,
412 tag, security_group_id, view_application_id
413 FROM fnd_lookup_values_vl
414 WHERE (lookup_type = 'IEX_HISTORY_TYPE'
415 AND lookup_code IN ('PAYMENT_CNSLD', 'PAYMENT_CONTRACT', 'PROMISE_CNSLD', 'PROMISE_CONTRACT'))
416 OR (lookup_type = 'IEX_CNSLD');
417 l_enabled_flag VARCHAR2(1);
418 BEGIN
419 x_return_status := 'S';
420
421 l_enabled_flag := NVL(p_leasing_enabled, 'N');
422
423 IF l_enabled_flag = 'N' THEN
424 FOR r_resp IN c_resp LOOP
425 l_function_name := 'IEX_COLL_CNTR';
426 iex_debug_pub.logmessage('r.resp.responsibility_id=' || r_resp.responsibility_id || ':l_function_name=' || l_function_name);
427 OPEN c_funct(r_resp.responsibility_id, l_function_name);
428 FETCH c_funct INTO r_funct;
429 iex_debug_pub.logmessage('r_funct.action_id=' || r_funct.action_id || ':r_funct.function_id=' || r_funct.function_id);
430 IF r_funct.action_id IS NULL THEN
431 fnd_resp_functions_pkg.insert_row(x_rowid => l_rowid,
432 x_application_id => r_resp.application_id,
433 x_responsibility_id => r_resp.responsibility_id,
434 x_action_id => r_funct.function_id,
435 x_rule_type => 'F',
436 x_creation_date => SYSDATE,
437 x_created_by => 1,
438 x_last_updated_by => 1,
439 x_last_update_date => SYSDATE,
440 x_last_update_login => 1);
441
442 iex_debug_pub.logmessage('x_rowid=' || l_rowid);
443
444 END IF;
445
446 CLOSE c_funct;
447
448 l_function_name := 'IEX_COLL_CASE';
449 r_funct.action_id := NULL;
450 r_funct.function_id := NULL;
451 iex_debug_pub.logmessage('r.resp.responsibility_id=' || r_resp.responsibility_id || ':l_function_name=' || l_function_name);
452 OPEN c_funct(r_resp.responsibility_id, l_function_name);
453 FETCH c_funct INTO r_funct;
454 iex_debug_pub.logmessage('r_funct.action_id=' || r_funct.action_id || ':r_funct.function_id=' || r_funct.function_id);
455 IF r_funct.action_id IS NULL THEN
456 fnd_resp_functions_pkg.insert_row(x_rowid => l_rowid,
457 x_application_id => r_resp.application_id,
458 x_responsibility_id => r_resp.responsibility_id,
459 x_action_id => r_funct.function_id,
460 x_rule_type => 'F',
461 x_creation_date => SYSDATE,
462 x_created_by => 1,
463 x_last_updated_by => 1,
464 x_last_update_date => SYSDATE,
465 x_last_update_login => 1);
466
467 iex_debug_pub.logmessage('x_rowid=' || l_rowid);
468 END IF;
469
470 CLOSE c_funct;
471 END LOOP;
472 ELSE
473 FOR r_resp IN c_resp LOOP
474 l_function_name := 'IEX_COLL_CNTR';
475 iex_debug_pub.logmessage('r.resp.responsibility_id=' || r_resp.responsibility_id || ':l_function_name=' || l_function_name);
476 OPEN c_funct(r_resp.responsibility_id, l_function_name);
477 FETCH c_funct INTO r_funct;
478 iex_debug_pub.logmessage('r_funct.action_id=' || r_funct.action_id || ':r_funct.function_id=' || r_funct.function_id);
479 IF r_funct.action_id IS NOT NULL THEN
480 fnd_resp_functions_pkg.delete_row(x_application_id => r_resp.application_id,
481 x_responsibility_id => r_resp.responsibility_id,
482 x_action_id => r_funct.action_id,
483 x_rule_type => 'F');
484 END IF;
485
486 CLOSE c_funct;
487
488 l_function_name := 'IEX_COLL_CASE';
489 r_funct.action_id := NULL;
490 r_funct.function_id := NULL;
491 iex_debug_pub.logmessage('r.resp.responsibility_id=' || r_resp.responsibility_id || ':l_function_name=' || l_function_name);
492 OPEN c_funct(r_resp.responsibility_id, l_function_name);
493 FETCH c_funct INTO r_funct;
494 iex_debug_pub.logmessage('r_funct.action_id=' || r_funct.action_id || ':r_funct.function_id=' || r_funct.function_id);
495 IF r_funct.action_id IS NOT NULL THEN
496 fnd_resp_functions_pkg.delete_row(x_application_id => r_resp.application_id,
497 x_responsibility_id => r_resp.responsibility_id,
498 x_action_id => r_funct.action_id,
499 x_rule_type => 'F');
500
501 iex_debug_pub.logmessage('x_rowid=' || l_rowid);
502 END IF;
503
504 CLOSE c_funct;
505 END LOOP;
506 END IF;
507
508 FOR r_lookup IN c_lookup LOOP
509 fnd_lookup_values_pkg.update_row(
510 x_lookup_type => r_lookup.lookup_type,
511 x_security_group_id => r_lookup.security_group_id,
512 x_view_application_id => r_lookup.view_application_id,
513 x_lookup_code => r_lookup.lookup_code,
514 x_tag => r_lookup.tag,
515 x_enabled_flag => l_enabled_flag,
516 x_start_date_active => r_lookup.start_date_active,
517 x_end_date_active => r_lookup.end_date_active,
518 x_territory_code => r_lookup.territory_code,
519 x_meaning => r_lookup.meaning,
520 x_description => r_lookup.description,
521 x_last_update_date => sysdate,
522 x_last_updated_by => 1,
523 x_last_update_login => 1,
524 x_attribute_category=>r_lookup.attribute_category,
525 x_attribute1=>r_lookup.attribute1,
526 x_attribute2=>r_lookup.attribute2,
527 x_attribute3=>r_lookup.attribute3,
528 x_attribute4=>r_lookup.attribute4,
529 x_attribute5=>r_lookup.attribute5,
530 x_attribute6=>r_lookup.attribute6,
531 x_attribute7=>r_lookup.attribute7,
532 x_attribute8=>r_lookup.attribute8,
533 x_attribute9=>r_lookup.attribute9,
534 x_attribute10=>r_lookup.attribute10,
535 x_attribute11=>r_lookup.attribute11,
536 x_attribute12=>r_lookup.attribute12,
537 x_attribute13=>r_lookup.attribute13,
538 x_attribute14=>r_lookup.attribute14,
539 x_attribute15=>r_lookup.attribute15
540 );
541 END LOOP;
542 EXCEPTION
543 WHEN OTHERS THEN
544 x_return_status := 'F';
545 fnd_message.set_name ('IEX', 'IEX_ADMIN_UNKNOWN_ERROR');
546 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
547 fnd_msg_pub.add;
548 END CHANGE_LEASING_SETUP;
549
550 PROCEDURE CHANGE_LOAN_SETUP(
551 p_loan_enabled IN VARCHAR2,
552 x_return_status OUT NOCOPY VARCHAR2) IS
553 CURSOR c_resp IS
554 SELECT resp.application_id, resp.responsibility_id, resp.menu_id
555 FROM fnd_responsibility resp, fnd_menus menu
556 WHERE resp.menu_id = menu.menu_id
557 AND menu.menu_name = 'IEX_COLLECTIONS_AGENT'
558 -- Begin fix bug #4930424-remove TABLE ACCESS FULL
559 AND resp.application_id = 695;
560 -- End fix bug #4930424-remove TABLE ACCESS FULL
561
562 CURSOR c_funct(p_responsibility_id NUMBER, p_function_name VARCHAR2) IS
563 SELECT rf.action_id, ff.function_id
564 FROM fnd_resp_functions rf, fnd_form_functions ff
565 WHERE rf.responsibility_id(+) = p_responsibility_id
566 AND ff.function_name = p_function_name
567 AND rf.action_id(+) = ff.function_id
568 AND rf.rule_type(+) = 'F';
569
570 -- Begin by Ehuh to fix a bug 4639561
571 CURSOR get_function_id IS
572 Select function_id from fnd_form_functions
573 where function_name = 'IEX_COLL_LOAN';
574
575 CURSOR get_menu_id IS
576 Select menu_id from fnd_menus
577 where menu_name = 'IEX_COLL';
578
579 CURSOR get_entry_sequence(p_menu_id NUMBER, p_function_id NUMBER) IS
580 Select entry_sequence from fnd_menu_entries
581 where menu_id = p_menu_id
582 and function_id = p_function_id;
583
584 l_entry_sequence number := 0;
585 l_menu_id number := 0;
586 l_func_id number := 0;
587 -- End to fix bug 4639561
588
589 r_funct c_funct%ROWTYPE;
590 l_function_name VARCHAR2(30);
591 l_rowid VARCHAR(1000);
592
593 l_enabled_flag VARCHAR2(1);
594 BEGIN
595 x_return_status := 'S';
596
597 l_enabled_flag := NVL(p_loan_enabled, 'N');
598
599 IF l_enabled_flag = 'N' THEN
600 FOR r_resp IN c_resp LOOP
601 l_function_name := 'IEX_COLL_LOAN';
602 iex_debug_pub.logmessage('r.resp.responsibility_id=' || r_resp.responsibility_id || ':l_function_name=' || l_function_name);
603 OPEN c_funct(r_resp.responsibility_id, l_function_name);
604 FETCH c_funct INTO r_funct;
605 iex_debug_pub.logmessage('r_funct.action_id=' || r_funct.action_id || ':r_funct.function_id=' || r_funct.function_id);
606 IF r_funct.action_id IS NULL THEN
607 fnd_resp_functions_pkg.insert_row(x_rowid => l_rowid,
608 x_application_id => r_resp.application_id,
609 x_responsibility_id => r_resp.responsibility_id,
610 x_action_id => r_funct.function_id,
611 x_rule_type => 'F',
612 x_creation_date => SYSDATE,
613 x_created_by => 1,
614 x_last_updated_by => 1,
615 x_last_update_date => SYSDATE,
616 x_last_update_login => 1);
617
618 iex_debug_pub.logmessage('x_rowid=' || l_rowid);
619
620 END IF;
621
622 CLOSE c_funct;
623 END LOOP;
624 ELSE
625 FOR r_resp IN c_resp LOOP
626 l_function_name := 'IEX_COLL_LOAN';
627 iex_debug_pub.logmessage('r.resp.responsibility_id=' || r_resp.responsibility_id || ':l_function_name=' || l_function_name);
628 OPEN c_funct(r_resp.responsibility_id, l_function_name);
629 FETCH c_funct INTO r_funct;
630 iex_debug_pub.logmessage('r_funct.action_id=' || r_funct.action_id || ':r_funct.function_id=' || r_funct.function_id);
631 IF r_funct.action_id IS NOT NULL THEN
632 fnd_resp_functions_pkg.delete_row(x_application_id => r_resp.application_id,
633 x_responsibility_id => r_resp.responsibility_id,
634 x_action_id => r_funct.action_id,
635 x_rule_type => 'F');
636 END IF;
637
638 CLOSE c_funct;
639 END LOOP;
640 END IF;
641
642 -- Begin by Ehuh to fix a bug 4639561
643 Begin
644 OPEN get_function_id;
645 FETCH get_function_id into l_func_id;
646 iex_debug_pub.logmessage('Function ID = '||l_func_id);
647
648 if get_function_id%NOTFOUND then
649 iex_debug_pub.logmessage('NOt found FUNCTION ID ');
650 null;
651 end if;
652
653 close get_function_id;
654
655 OPEN get_menu_id;
656 FETCH get_menu_id into l_menu_id;
657
658 if get_menu_id%NOTFOUND then
659 iex_debug_pub.logmessage('NOt found MENU ID ');
660 null;
661 end if;
662
663 close get_menu_id;
664
665 if (l_menu_id <> 0) and (l_func_id <> 0) then
666 OPEN get_entry_sequence(l_menu_id , l_func_id );
667 FETCH get_entry_sequence into l_entry_sequence;
668
669 if get_entry_sequence%NOTFOUND then
670 iex_debug_pub.logmessage('NOt found ENTRY SEQ ');
671 null;
672 end if;
673
674 close get_entry_sequence;
675
676 FND_MENU_ENTRIES_PKG.update_row(
677 x_menu_id => l_menu_id,
678 x_entry_sequence => l_entry_sequence,
679 x_sub_menu_id => null,
680 x_function_id => l_func_id,
681 x_grant_flag => l_enabled_flag,
682 x_prompt => null,
683 x_description => null,
684 x_last_update_date => SYSDATE,
685 x_last_updated_by => 1,
686 x_last_update_login => 1 );
687 end if;
688
689 Exception
690 When others then
691 iex_debug_pub.logmessage('Exceptopn ');
692 null;
693 End;
694 -- End to fix bug 4639561
695
696 EXCEPTION
697 WHEN OTHERS THEN
698 x_return_status := 'F';
699 fnd_message.set_name ('IEX', 'IEX_ADMIN_UNKNOWN_ERROR');
700 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
701 fnd_msg_pub.add;
702 END CHANGE_LOAN_SETUP;
703
704 PROCEDURE CHANGE_BUSINESS_LEVEL(p_business_level IN VARCHAR2,
705 p_promise_enabled IN VARCHAR2,
706 p_collections_methods IN VARCHAR2,
707 x_return_status OUT NOCOPY VARCHAR2) IS
708 CURSOR c_resp IS
709 SELECT resp.application_id, resp.responsibility_id, resp.menu_id, resp.responsibility_key
710 FROM fnd_responsibility resp
711 WHERE resp.application_id = 695;
712 l_return BOOLEAN;
713 l_promise_enabled VARCHAR2(1);
714 l_strategy_enabled VARCHAR2(1);
715 l_business_level VARCHAR2(30);
716 BEGIN
717 x_return_status := 'S';
718 l_business_level := p_business_level;
719 l_promise_enabled := NVL(p_promise_enabled, 'Y');
720 IF NVL(p_collections_methods, 'DUNNING') = 'DUNNING' THEN
721 l_strategy_enabled := 'N';
722 ELSE
723 l_strategy_enabled := 'Y';
724 END IF;
725
726 iex_debug_pub.logmessage('Starting ....');
727
728 iex_debug_pub.logmessage('l_promise_enabled=' || l_promise_enabled || ': l_strategy_enabled=' || l_strategy_enabled);
729
730 -- Begin fix bug #5142565-JYPARK-Change site level profile too
731 IF l_business_level = 'CUSTOMER' THEN
732 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_DELINQUENCIES',
733 x_value => 'Y',
734 x_level_name => 'SITE',
735 x_level_value => null,
736 x_level_value_app_id => '',
737 x_level_value2 => null);
738 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_DELINQUENCIES',
739 x_value => 'N',
740 x_level_name => 'SITE',
741 x_level_value => null,
742 x_level_value_app_id => '',
743 x_level_value2 => null);
744 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_DELINQUENCIES',
745 x_value => 'N',
746 x_level_name => 'SITE',
747 x_level_value => null,
748 x_level_value_app_id => '',
749 x_level_value2 => null);
750 l_return := fnd_profile.save(x_name => 'IEX_QEN_DELINQUENCIES',
751 x_value => 'N',
752 x_level_name => 'SITE',
753 x_level_value => null,
754 x_level_value_app_id => '',
755 x_level_value2 => null);
756 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_PROMISES',
757 x_value => l_promise_enabled,
758 x_level_name => 'SITE',
759 x_level_value => null,
760 x_level_value_app_id => '',
761 x_level_value2 => null);
762 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_PROMISES',
763 x_value => 'N',
764 x_level_name => 'SITE',
765 x_level_value => null,
766 x_level_value_app_id => '',
767 x_level_value2 => null);
768 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_PROMISES',
769 x_value => 'N',
770 x_level_name => 'SITE',
771 x_level_value => null,
772 x_level_value_app_id => '',
773 x_level_value2 => null);
774 l_return := fnd_profile.save(x_name => 'IEX_QEN_PROMISES',
775 x_value => 'N',
776 x_level_name => 'SITE',
777 x_level_value => null,
778 x_level_value_app_id => '',
779 x_level_value2 => null);
780 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_STRATEGIES',
781 x_value => l_strategy_enabled,
782 x_level_name => 'SITE',
783 x_level_value => null,
784 x_level_value_app_id => '',
785 x_level_value2 => null);
786 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_STRATEGIES',
787 x_value => 'N',
788 x_level_name => 'SITE',
789 x_level_value => null,
790 x_level_value_app_id => '',
791 x_level_value2 => null);
792 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_STRATEGIES',
793 x_value => 'N',
794 x_level_name => 'SITE',
795 x_level_value => null,
796 x_level_value_app_id => '',
797 x_level_value2 => null);
798 l_return := fnd_profile.save(x_name => 'IEX_QEN_STRATEGY',
799 x_value => 'N',
800 x_level_name => 'SITE',
801 x_level_value => null,
802 x_level_value_app_id => '',
803 x_level_value2 => null);
804 ELSIF l_business_level = 'ACCOUNT' THEN
805 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_DELINQUENCIES',
806 x_value => 'N',
807 x_level_name => 'SITE',
808 x_level_value => null,
809 x_level_value_app_id => '',
810 x_level_value2 => null);
811 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_DELINQUENCIES',
812 x_value => 'Y',
813 x_level_name => 'SITE',
814 x_level_value => null,
815 x_level_value_app_id => '',
816 x_level_value2 => null);
817 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_DELINQUENCIES',
818 x_value => 'N',
819 x_level_name => 'SITE',
820 x_level_value => null,
821 x_level_value_app_id => '',
822 x_level_value2 => null);
823 l_return := fnd_profile.save(x_name => 'IEX_QEN_DELINQUENCIES',
824 x_value => 'N',
825 x_level_name => 'SITE',
826 x_level_value => null,
827 x_level_value_app_id => '',
828 x_level_value2 => null);
829 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_PROMISES',
830 x_value => 'N',
831 x_level_name => 'SITE',
832 x_level_value => null,
833 x_level_value_app_id => '',
834 x_level_value2 => null);
835 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_PROMISES',
836 x_value => l_promise_enabled,
837 x_level_name => 'SITE',
838 x_level_value => null,
839 x_level_value_app_id => '',
840 x_level_value2 => null);
841 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_PROMISES',
842 x_value => 'N',
843 x_level_name => 'SITE',
844 x_level_value => null,
845 x_level_value_app_id => '',
846 x_level_value2 => null);
847 l_return := fnd_profile.save(x_name => 'IEX_QEN_PROMISES',
848 x_value => 'N',
849 x_level_name => 'SITE',
850 x_level_value => null,
851 x_level_value_app_id => '',
852 x_level_value2 => null);
853 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_STRATEGIES',
854 x_value => 'N',
855 x_level_name => 'SITE',
856 x_level_value => null,
857 x_level_value_app_id => '',
858 x_level_value2 => null);
859 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_STRATEGIES',
860 x_value => l_Strategy_enabled,
861 x_level_name => 'SITE',
862 x_level_value => null,
863 x_level_value_app_id => '',
864 x_level_value2 => null);
865 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_STRATEGIES',
866 x_value => 'N',
867 x_level_name => 'SITE',
868 x_level_value => null,
869 x_level_value_app_id => '',
870 x_level_value2 => null);
871 l_return := fnd_profile.save(x_name => 'IEX_QEN_STRATEGY',
872 x_value => 'N',
873 x_level_name => 'SITE',
874 x_level_value => null,
875 x_level_value_app_id => '',
876 x_level_value2 => null);
877 ELSIF l_business_level = 'BILL_TO' THEN
878 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_DELINQUENCIES',
879 x_value => 'N',
880 x_level_name => 'SITE',
881 x_level_value => null,
882 x_level_value_app_id => '',
883 x_level_value2 => null);
884 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_DELINQUENCIES',
885 x_value => 'N',
886 x_level_name => 'SITE',
887 x_level_value => null,
888 x_level_value_app_id => '',
889 x_level_value2 => null);
890 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_DELINQUENCIES',
891 x_value => 'Y',
892 x_level_name => 'SITE',
893 x_level_value => null,
894 x_level_value_app_id => '',
895 x_level_value2 => null);
896 l_return := fnd_profile.save(x_name => 'IEX_QEN_DELINQUENCIES',
897 x_value => 'N',
898 x_level_name => 'SITE',
899 x_level_value => null,
900 x_level_value_app_id => '',
901 x_level_value2 => null);
902 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_PROMISES',
903 x_value => 'N',
904 x_level_name => 'SITE',
905 x_level_value => null,
906 x_level_value_app_id => '',
907 x_level_value2 => null);
908 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_PROMISES',
909 x_value => 'N',
910 x_level_name => 'SITE',
911 x_level_value => null,
912 x_level_value_app_id => '',
913 x_level_value2 => null);
914 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_PROMISES',
915 x_value => l_promise_enabled,
916 x_level_name => 'SITE',
917 x_level_value => null,
918 x_level_value_app_id => '',
919 x_level_value2 => null);
920 l_return := fnd_profile.save(x_name => 'IEX_QEN_PROMISES',
921 x_value => 'N',
922 x_level_name => 'SITE',
923 x_level_value => null,
924 x_level_value_app_id => '',
925 x_level_value2 => null);
926 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_STRATEGIES',
927 x_value => 'N',
928 x_level_name => 'SITE',
929 x_level_value => null,
930 x_level_value_app_id => '',
931 x_level_value2 => null);
932 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_STRATEGIES',
933 x_value => 'N',
934 x_level_name => 'SITE',
935 x_level_value => null,
936 x_level_value_app_id => '',
937 x_level_value2 => null);
938 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_STRATEGIES',
939 x_value => l_Strategy_enabled,
940 x_level_name => 'SITE',
941 x_level_value => null,
942 x_level_value_app_id => '',
943 x_level_value2 => null);
944 l_return := fnd_profile.save(x_name => 'IEX_QEN_STRATEGY',
945 x_value => 'N',
946 x_level_name => 'SITE',
947 x_level_value => null,
948 x_level_value_app_id => '',
949 x_level_value2 => null);
950 ELSIF l_business_level = 'DELINQUENCY' THEN
951 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_DELINQUENCIES',
952 x_value => 'N',
953 x_level_name => 'SITE',
954 x_level_value => null,
955 x_level_value_app_id => '',
956 x_level_value2 => null);
957 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_DELINQUENCIES',
958 x_value => 'N',
959 x_level_name => 'SITE',
960 x_level_value => null,
961 x_level_value_app_id => '',
962 x_level_value2 => null);
963 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_DELINQUENCIES',
964 x_value => 'N',
965 x_level_name => 'SITE',
966 x_level_value => null,
967 x_level_value_app_id => '',
968 x_level_value2 => null);
969 l_return := fnd_profile.save(x_name => 'IEX_QEN_DELINQUENCIES',
970 x_value => 'Y',
971 x_level_name => 'SITE',
972 x_level_value => null,
973 x_level_value_app_id => '',
974 x_level_value2 => null);
975 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_PROMISES',
976 x_value => 'N',
977 x_level_name => 'SITE',
978 x_level_value => null,
979 x_level_value_app_id => '',
980 x_level_value2 => null);
981 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_PROMISES',
982 x_value => 'N',
983 x_level_name => 'SITE',
984 x_level_value => null,
985 x_level_value_app_id => '',
986 x_level_value2 => null);
987 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_PROMISES',
988 x_value => 'N',
989 x_level_name => 'SITE',
990 x_level_value => null,
991 x_level_value_app_id => '',
992 x_level_value2 => null);
993 l_return := fnd_profile.save(x_name => 'IEX_QEN_PROMISES',
994 x_value => l_promise_enabled,
995 x_level_name => 'SITE',
996 x_level_value => null,
997 x_level_value_app_id => '',
998 x_level_value2 => null);
999 l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_STRATEGIES',
1000 x_value => 'N',
1001 x_level_name => 'SITE',
1002 x_level_value => null,
1003 x_level_value_app_id => '',
1004 x_level_value2 => null);
1005 l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_STRATEGIES',
1006 x_value => 'N',
1007 x_level_name => 'SITE',
1008 x_level_value => null,
1009 x_level_value_app_id => '',
1010 x_level_value2 => null);
1011 l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_STRATEGIES',
1012 x_value => 'N',
1013 x_level_name => 'SITE',
1014 x_level_value => null,
1015 x_level_value_app_id => '',
1016 x_level_value2 => null);
1017 l_return := fnd_profile.save(x_name => 'IEX_QEN_STRATEGY',
1018 x_value => l_Strategy_enabled,
1019 x_level_name => 'SITE',
1020 x_level_value => null,
1021 x_level_value_app_id => '',
1022 x_level_value2 => null);
1023 END IF;
1024 -- End fix bug #5142565-JYPARK-Change site level profile too
1025
1026 -- Begin fix bug #5142565-JYPARK-remove change profile at resp level
1027 -- FOR r_resp IN c_resp LOOP
1028 -- iex_debug_pub.logmessage('responsibility_key=' || r_resp.responsibility_key);
1029 -- IF l_business_level = 'CUSTOMER' THEN
1030 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_DELINQUENCIES',
1031 -- x_value => 'Y',
1032 -- x_level_name => 'RESP',
1033 -- x_level_value => r_resp.responsibility_id,
1034 -- x_level_value_app_id => '695',
1035 -- x_level_value2 => null);
1036 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_DELINQUENCIES',
1037 -- x_value => 'N',
1038 -- x_level_name => 'RESP',
1039 -- x_level_value => r_resp.responsibility_id,
1040 -- x_level_value_app_id => '695',
1041 -- x_level_value2 => null);
1042 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_DELINQUENCIES',
1043 -- x_value => 'N',
1044 -- x_level_name => 'RESP',
1045 -- x_level_value => r_resp.responsibility_id,
1046 -- x_level_value_app_id => '695',
1047 -- x_level_value2 => null);
1048 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_DELINQUENCIES',
1049 -- x_value => 'N',
1050 -- x_level_name => 'RESP',
1051 -- x_level_value => r_resp.responsibility_id,
1052 -- x_level_value_app_id => '695',
1053 -- x_level_value2 => null);
1054 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_PROMISES',
1055 -- x_value => l_promise_enabled,
1056 -- x_level_name => 'RESP',
1057 -- x_level_value => r_resp.responsibility_id,
1058 -- x_level_value_app_id => '695',
1059 -- x_level_value2 => null);
1060 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_PROMISES',
1061 -- x_value => 'N',
1062 -- x_level_name => 'RESP',
1063 -- x_level_value => r_resp.responsibility_id,
1064 -- x_level_value_app_id => '695',
1065 -- x_level_value2 => null);
1066 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_PROMISES',
1067 -- x_value => 'N',
1068 -- x_level_name => 'RESP',
1069 -- x_level_value => r_resp.responsibility_id,
1070 -- x_level_value_app_id => '695',
1071 -- x_level_value2 => null);
1072 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_PROMISES',
1073 -- x_value => 'N',
1074 -- x_level_name => 'RESP',
1075 -- x_level_value => r_resp.responsibility_id,
1076 -- x_level_value_app_id => '695',
1077 -- x_level_value2 => null);
1078 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_STRATEGIES',
1079 -- x_value => l_strategy_enabled,
1080 -- x_level_name => 'RESP',
1081 -- x_level_value => r_resp.responsibility_id,
1082 -- x_level_value_app_id => '695',
1083 -- x_level_value2 => null);
1084 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_STRATEGIES',
1085 -- x_value => 'N',
1086 -- x_level_name => 'RESP',
1087 -- x_level_value => r_resp.responsibility_id,
1088 -- x_level_value_app_id => '695',
1089 -- x_level_value2 => null);
1090 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_STRATEGIES',
1091 -- x_value => 'N',
1092 -- x_level_name => 'RESP',
1093 -- x_level_value => r_resp.responsibility_id,
1094 -- x_level_value_app_id => '695',
1095 -- x_level_value2 => null);
1096 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_STRATEGY',
1097 -- x_value => 'N',
1098 -- x_level_name => 'RESP',
1099 -- x_level_value => r_resp.responsibility_id,
1100 -- x_level_value_app_id => '695',
1101 -- x_level_value2 => null);
1102 -- ELSIF l_business_level = 'ACCOUNT' THEN
1103 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_DELINQUENCIES',
1104 -- x_value => 'N',
1105 -- x_level_name => 'RESP',
1106 -- x_level_value => r_resp.responsibility_id,
1107 -- x_level_value_app_id => '695',
1108 -- x_level_value2 => null);
1109 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_DELINQUENCIES',
1110 -- x_value => 'Y',
1111 -- x_level_name => 'RESP',
1112 -- x_level_value => r_resp.responsibility_id,
1113 -- x_level_value_app_id => '695',
1114 -- x_level_value2 => null);
1115 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_DELINQUENCIES',
1116 -- x_value => 'N',
1117 -- x_level_name => 'RESP',
1118 -- x_level_value => r_resp.responsibility_id,
1119 -- x_level_value_app_id => '695',
1120 -- x_level_value2 => null);
1121 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_DELINQUENCIES',
1122 -- x_value => 'N',
1123 -- x_level_name => 'RESP',
1124 -- x_level_value => r_resp.responsibility_id,
1125 -- x_level_value_app_id => '695',
1126 -- x_level_value2 => null);
1127 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_PROMISES',
1128 -- x_value => 'N',
1129 -- x_level_name => 'RESP',
1130 -- x_level_value => r_resp.responsibility_id,
1131 -- x_level_value_app_id => '695',
1132 -- x_level_value2 => null);
1133 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_PROMISES',
1134 -- x_value => l_promise_enabled,
1135 -- x_level_name => 'RESP',
1136 -- x_level_value => r_resp.responsibility_id,
1137 -- x_level_value_app_id => '695',
1138 -- x_level_value2 => null);
1139 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_PROMISES',
1140 -- x_value => 'N',
1141 -- x_level_name => 'RESP',
1142 -- x_level_value => r_resp.responsibility_id,
1143 -- x_level_value_app_id => '695',
1144 -- x_level_value2 => null);
1145 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_PROMISES',
1146 -- x_value => 'N',
1147 -- x_level_name => 'RESP',
1148 -- x_level_value => r_resp.responsibility_id,
1149 -- x_level_value_app_id => '695',
1150 -- x_level_value2 => null);
1151 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_STRATEGIES',
1152 -- x_value => 'N',
1153 -- x_level_name => 'RESP',
1154 -- x_level_value => r_resp.responsibility_id,
1155 -- x_level_value_app_id => '695',
1156 -- x_level_value2 => null);
1157 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_STRATEGIES',
1158 -- x_value => l_Strategy_enabled,
1159 -- x_level_name => 'RESP',
1160 -- x_level_value => r_resp.responsibility_id,
1161 -- x_level_value_app_id => '695',
1162 -- x_level_value2 => null);
1163 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_STRATEGIES',
1164 -- x_value => 'N',
1165 -- x_level_name => 'RESP',
1166 -- x_level_value => r_resp.responsibility_id,
1167 -- x_level_value_app_id => '695',
1168 -- x_level_value2 => null);
1169 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_STRATEGY',
1170 -- x_value => 'N',
1171 -- x_level_name => 'RESP',
1172 -- x_level_value => r_resp.responsibility_id,
1173 -- x_level_value_app_id => '695',
1174 -- x_level_value2 => null);
1175 -- ELSIF l_business_level = 'BILL_TO' THEN
1176 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_DELINQUENCIES',
1177 -- x_value => 'N',
1178 -- x_level_name => 'RESP',
1179 -- x_level_value => r_resp.responsibility_id,
1180 -- x_level_value_app_id => '695',
1181 -- x_level_value2 => null);
1182 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_DELINQUENCIES',
1183 -- x_value => 'N',
1184 -- x_level_name => 'RESP',
1185 -- x_level_value => r_resp.responsibility_id,
1186 -- x_level_value_app_id => '695',
1187 -- x_level_value2 => null);
1188 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_DELINQUENCIES',
1189 -- x_value => 'Y',
1190 -- x_level_name => 'RESP',
1191 -- x_level_value => r_resp.responsibility_id,
1192 -- x_level_value_app_id => '695',
1193 -- x_level_value2 => null);
1194 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_DELINQUENCIES',
1195 -- x_value => 'N',
1196 -- x_level_name => 'RESP',
1197 -- x_level_value => r_resp.responsibility_id,
1198 -- x_level_value_app_id => '695',
1199 -- x_level_value2 => null);
1200 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_PROMISES',
1201 -- x_value => 'N',
1202 -- x_level_name => 'RESP',
1203 -- x_level_value => r_resp.responsibility_id,
1204 -- x_level_value_app_id => '695',
1205 -- x_level_value2 => null);
1206 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_PROMISES',
1207 -- x_value => 'N',
1208 -- x_level_name => 'RESP',
1209 -- x_level_value => r_resp.responsibility_id,
1210 -- x_level_value_app_id => '695',
1211 -- x_level_value2 => null);
1212 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_PROMISES',
1213 -- x_value => l_promise_enabled,
1214 -- x_level_name => 'RESP',
1215 -- x_level_value => r_resp.responsibility_id,
1216 -- x_level_value_app_id => '695',
1217 -- x_level_value2 => null);
1218 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_PROMISES',
1219 -- x_value => 'N',
1220 -- x_level_name => 'RESP',
1221 -- x_level_value => r_resp.responsibility_id,
1222 -- x_level_value_app_id => '695',
1223 -- x_level_value2 => null);
1224 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_STRATEGIES',
1225 -- x_value => 'N',
1226 -- x_level_name => 'RESP',
1227 -- x_level_value => r_resp.responsibility_id,
1228 -- x_level_value_app_id => '695',
1229 -- x_level_value2 => null);
1230 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_STRATEGIES',
1231 -- x_value => 'N',
1232 -- x_level_name => 'RESP',
1233 -- x_level_value => r_resp.responsibility_id,
1234 -- x_level_value_app_id => '695',
1235 -- x_level_value2 => null);
1236 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_STRATEGIES',
1237 -- x_value => l_Strategy_enabled,
1238 -- x_level_name => 'RESP',
1239 -- x_level_value => r_resp.responsibility_id,
1240 -- x_level_value_app_id => '695',
1241 -- x_level_value2 => null);
1242 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_STRATEGY',
1243 -- x_value => 'N',
1244 -- x_level_name => 'RESP',
1245 -- x_level_value => r_resp.responsibility_id,
1246 -- x_level_value_app_id => '695',
1247 -- x_level_value2 => null);
1248 -- ELSIF l_business_level = 'DELINQUENCY' THEN
1249 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_DELINQUENCIES',
1250 -- x_value => 'N',
1251 -- x_level_name => 'RESP',
1252 -- x_level_value => r_resp.responsibility_id,
1253 -- x_level_value_app_id => '695',
1254 -- x_level_value2 => null);
1255 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_DELINQUENCIES',
1256 -- x_value => 'N',
1257 -- x_level_name => 'RESP',
1258 -- x_level_value => r_resp.responsibility_id,
1259 -- x_level_value_app_id => '695',
1260 -- x_level_value2 => null);
1261 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_DELINQUENCIES',
1262 -- x_value => 'N',
1263 -- x_level_name => 'RESP',
1264 -- x_level_value => r_resp.responsibility_id,
1265 -- x_level_value_app_id => '695',
1266 -- x_level_value2 => null);
1267 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_DELINQUENCIES',
1268 -- x_value => 'Y',
1269 -- x_level_name => 'RESP',
1270 -- x_level_value => r_resp.responsibility_id,
1271 -- x_level_value_app_id => '695',
1272 -- x_level_value2 => null);
1273 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_PROMISES',
1274 -- x_value => 'N',
1275 -- x_level_name => 'RESP',
1276 -- x_level_value => r_resp.responsibility_id,
1277 -- x_level_value_app_id => '695',
1278 -- x_level_value2 => null);
1279 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_PROMISES',
1280 -- x_value => 'N',
1281 -- x_level_name => 'RESP',
1282 -- x_level_value => r_resp.responsibility_id,
1283 -- x_level_value_app_id => '695',
1284 -- x_level_value2 => null);
1285 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_PROMISES',
1286 -- x_value => 'N',
1287 -- x_level_name => 'RESP',
1288 -- x_level_value => r_resp.responsibility_id,
1289 -- x_level_value_app_id => '695',
1290 -- x_level_value2 => null);
1291 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_PROMISES',
1292 -- x_value => l_promise_enabled,
1293 -- x_level_name => 'RESP',
1294 -- x_level_value => r_resp.responsibility_id,
1295 -- x_level_value_app_id => '695',
1296 -- x_level_value2 => null);
1297 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_CUST_STRATEGIES',
1298 -- x_value => 'N',
1299 -- x_level_name => 'RESP',
1300 -- x_level_value => r_resp.responsibility_id,
1301 -- x_level_value_app_id => '695',
1302 -- x_level_value2 => null);
1303 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_ACC_STRATEGIES',
1304 -- x_value => 'N',
1305 -- x_level_name => 'RESP',
1306 -- x_level_value => r_resp.responsibility_id,
1307 -- x_level_value_app_id => '695',
1308 -- x_level_value2 => null);
1309 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_BILLTO_STRATEGIES',
1310 -- x_value => 'N',
1311 -- x_level_name => 'RESP',
1312 -- x_level_value => r_resp.responsibility_id,
1313 -- x_level_value_app_id => '695',
1314 -- x_level_value2 => null);
1315 -- l_return := fnd_profile.save(x_name => 'IEX_QEN_STRATEGY',
1316 -- x_value => l_Strategy_enabled,
1317 -- x_level_name => 'RESP',
1318 -- x_level_value => r_resp.responsibility_id,
1319 -- x_level_value_app_id => '695',
1320 -- x_level_value2 => null);
1321 -- END IF;
1322 -- END LOOP;
1323 -- End fix bug #5142565-JYPARK-remove change profile at resp level
1324 EXCEPTION
1325 WHEN OTHERS THEN
1326 x_return_status := 'F';
1327 fnd_message.set_name ('IEX', 'IEX_ADMIN_UNKNOWN_ERROR');
1328 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
1329 fnd_msg_pub.add;
1330 END CHANGE_BUSINESS_LEVEL;
1331
1332
1333 PROCEDURE UPDATE_CHECKLIST_ITEM_BY_NAME(
1334 p_checklist_item_name IN VARCHAR2,
1335 x_return_status OUT NOCOPY VARCHAR2) IS
1336 CURSOR c_checklist IS
1337 SELECT checklist_item_id
1338 FROM iex_checklist_items_b
1339 WHERE checklist_item_name = p_checklist_item_name;
1340
1341 l_checklist_item_id NUMBER;
1342 BEGIN
1343 OPEN c_checklist;
1344 FETCH c_checklist INTO l_checklist_item_id;
1345 CLOSE c_checklist;
1346
1347
1348 update_checklist_item(l_checklist_item_id, x_return_status);
1349 EXCEPTION
1350 WHEN OTHERS THEN
1351 x_return_status := 'F';
1352 fnd_message.set_name ('IEX', 'IEX_ADMIN_UNKNOWN_ERROR');
1353
1354 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
1355
1356 fnd_msg_pub.add;
1357 END UPDATE_CHECKLIST_ITEM_BY_NAME;
1358 BEGIN
1359 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
1360 G_APPL_ID := FND_GLOBAL.Prog_Appl_Id;
1361 G_LOGIN_ID := FND_GLOBAL.Conc_Login_Id;
1362 G_PROGRAM_ID := FND_GLOBAL.Conc_Program_Id;
1363 G_USER_ID := FND_GLOBAL.User_Id;
1364 G_REQUEST_ID := FND_GLOBAL.Conc_Request_Id;
1365 END IEX_CHECKLIST_UTILITY;