[Home] [Help]
PACKAGE BODY: APPS.HR_USER_ACCT_INTERNAL
Source
1 Package Body hr_user_acct_internal as
2 /* $Header: hrusrbsi.pkb 120.4.12010000.3 2009/07/02 06:22:14 pthoonig ship $ */
3 --
4 -- Private Global Variables
5 --
6 g_package varchar2(33) := 'hr_user_acct_internal.';
7 g_max_user_name_length constant number := 100;
8 g_max_email_address_length constant number := 240;
9 g_max_fax_length constant number := 80;
10 g_api_vers constant number := 1.0;
11 g_empty_fnd_user_rec hr_user_acct_utility.fnd_user_rec;
12 g_emtpy_fnd_resp_tbl hr_user_acct_utility.fnd_responsibility_tbl;
13 g_emtpy_fnd_prof_opt_val_tbl hr_user_acct_utility.fnd_profile_opt_val_tbl;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |--------------------------- < generate_string > ---------------------------|
17 -- ----------------------------------------------------------------------------
18 FUNCTION generate_string (p_user_name in varchar2
19 ,p_id in number)
20 RETURN varchar2 IS
21 --
22 --
23 j number default 0;
24 k number default null;
25 l_str varchar2(30) default null;
26 l_result varchar2(30) default null;
27 l_proc varchar2(72) := g_package||'generate_string';
28 --
29 BEGIN
30 --
31 hr_utility.set_location('Entering:' || l_proc, 10);
32
33 IF p_id is null
34 THEN
35 return null;
36 END IF;
37 while true loop
38 l_str := to_char(sysdate, 'SSSSMIHH');
39 j := 0;
40 k := null;
41 l_result := null;
42 --
43 FOR i in 1..least(length(l_str), 8)
44 LOOP
45 j := mod(j + ascii(substr(l_str, i, 1)), 256);
46 k := mod(bitand(j,ascii(substr(l_str, i, 1))), 74)+48;
47 --
48 IF k between 58 and 64
49 THEN
50 k := k + 7;
51 ELSIF k between 91 and 96
52 THEN
53 k := k + 6;
54 END IF;
55 --
56 l_result := l_result || fnd_global.local_chr(k);
57 END LOOP;
58 --
59 if fnd_web_sec.validate_password(username => p_user_name
60 ,password => l_result
61 ) = 'Y' then
62 return l_result;
63 end if;
64 end loop;
65 RETURN l_result;
66 --
67 hr_utility.set_location('Leaving:' || l_proc, 50);
68 --
69 EXCEPTION
70 WHEN others THEN
71 hr_utility.set_message(800, 'HR_GENERATE_PASSWORD_ERR');
72 hr_utility.raise_error;
73 return null;
74
75 END generate_string;
76 --
77 --
78 --
79 -- ----------------------------------------------------------------------------
80 -- |-------------------------- < create_fnd_user > ---------------------------|
81 -- | NOTE: The fnd api fnd_user_pkg.create_user that this api will be calling |
82 -- | does not have code to handle AK securing attributes. Thus, this |
83 -- | api will not do any inserts into ak_web_user_sec_attr_values table.|
84 -- | So, this api does not do everything that the FND Create User form |
85 -- | does. |
86 -- | No savepoint will be issued here because business support internal |
87 -- process is not supposed to issue any savepoint or rollback. |
88 -- ----------------------------------------------------------------------------
89 --
90 -- Fix 2288014. Modified procedure create_fnd_user adding parameter
91 -- p_password_date that could be passed to fnd_user_pkg.CreateUserId
92 PROCEDURE create_fnd_user
93 (p_hire_date in date default null
94 ,p_user_name in varchar2
95 ,p_password in out nocopy varchar2
96 ,p_user_start_date in date default null
97 ,p_user_end_date in date default null
98 ,p_email_address in varchar2 default null
99 ,p_fax in varchar2 default null
100 ,p_description in varchar2 default null
101 ,p_password_date in date default null -- Fix 2288014
102 ,p_language in varchar2 default 'AMERICAN'
103 ,p_host_port in varchar2 default null
104 ,p_employee_id in varchar2 default null
105 ,p_customer_id in varchar2 default null
106 ,p_supplier_id in varchar2 default null
107 ,p_user_id out nocopy number
108 ) IS
109
110 CURSOR lc_get_user_name IS
111 SELECT user_name, user_id
112 FROM fnd_user
113 WHERE user_name = upper(p_user_name);
114
115 l_proc varchar2(72) := g_package||'create_fnd_user';
116 l_fnd_user_start_date date default null;
117 l_fnd_user_end_date date default null;
118 l_host_port_name varchar2(2000) default null;
119 l_pos number default 0;
120 l_count number default 0;
121 l_password varchar2(30) default null;
122 l_return_status varchar2(32000) default null;
123 l_msg_count number default 0;
124 l_msg_data varchar2(32000) default null;
125 l_fnd_user_id number default null;
126 l_last_updated_by number default null;
127 l_last_update_login number default null;
128 l_app_short_name varchar2(200) default null;
129 l_msg_name fnd_new_messages.message_name%type
130 default null;
131 l_host_name varchar2(2000) default null;
132 l_port_name varchar2(2000) default null;
133 l_plsql_agent varchar2(2000) default null;
134 l_user_name fnd_user.user_name%type default null;
135 l_user_id fnd_user.user_id%type default null;
136
137 e_create_fnd_user EXCEPTION; -- Fix 2288014
138 --
139 --
140 begin
141 hr_utility.set_location('Entering:'|| l_proc, 10);
142 --
143 -- Validate input parameters first
144 -- Validate p_user_start_date
145 IF p_user_start_date IS NULL
146 THEN
147 IF p_hire_date IS NULL
148 THEN
149 hr_utility.set_message(800, 'HR_NO_HIRE_DATE');
150 hr_utility.raise_error;
151 ELSE
152 l_fnd_user_start_date := p_hire_date;
153 END IF;
154 ELSE
155 l_fnd_user_start_date := p_user_start_date;
156 END IF;
157
158
159 -- Validate p_user_end_date. If entered, it must be larger than start_date
160 IF p_user_end_date IS NULL
161 THEN
162 l_fnd_user_end_date := null;
163 ELSE
164 IF p_user_end_date >= l_fnd_user_start_date
165 THEN
166 l_fnd_user_end_date := p_user_end_date;
167 ELSE
168 hr_utility.set_message(800, 'HR_51070_CAU_START_END');
169 hr_utility.raise_error;
170 END IF;
171 END IF;
172 --
173 --
174 -- Validate email address length
175 IF length(p_email_address) > g_max_email_address_length
176 THEN
177 hr_utility.set_message(800, 'HR_INVALID_EMAIL_ADDR_LENGTH');
178 hr_utility.raise_error;
179 END IF;
180 --
181 -- Validate fax length
182 IF length(p_fax) > g_max_fax_length
183 THEN
184 hr_utility.set_message(800, 'HR_INVALID_FAX_LENGTH');
185 hr_utility.raise_error;
186 END IF;
187 --
188 -- Validate user name
189 IF p_user_name IS NOT NULL
190 THEN
191 l_pos := length(p_user_name);
192 IF l_pos IS NULL
193 THEN
194 hr_utility.set_message(800, 'HR_USER_NAME_NOT_SUPPLIED');
195 hr_utility.raise_error;
196 ELSIF l_pos > g_max_user_name_length
197 THEN
198 hr_utility.set_message(800, 'HR_USER_NAME_LENGTH_EXCEEDED');
199 hr_utility.raise_error;
200 END IF;
201 ELSE
202 hr_utility.set_message(800, 'HR_USER_NAME_MISSING');
203 hr_utility.raise_error;
204 END IF;
205 --
206 -- Check for uniqueness of the user name
207 OPEN lc_get_user_name;
208 FETCH lc_get_user_name into l_user_name, l_user_id;
209 IF lc_get_user_name%NOTFOUND
210 THEN
211 CLOSE lc_get_user_name;
212 ELSE
213 -- Issue an error if user_name already exists
214 CLOSE lc_get_user_name;
215 fnd_message.set_name('PER', 'HR_USER_NAME_ALREADY_EXISTS');
216 fnd_message.set_token('USER_NAME', p_user_name);
217 hr_utility.raise_error;
218 END IF;
219
220 --
221 -- Check for employee id if it exists
222 IF p_employee_id IS NOT NULL
223 THEN
224 SELECT count(1)
225 INTO l_count
226 FROM per_all_people_f
227 WHERE person_id = p_employee_id;
228
229 IF l_count <= 0
230 THEN
231 hr_utility.set_message(800, 'HR_INVALID_EMP_ID');
232 hr_utility.raise_error;
233 END IF;
234 ELSE
235 NULL;
236 END IF;
237 --
238 -- The following is mimicking FNDSCAUS.fmb program unit fnd_encrypt_pwd.
239 -- Check password length. The minimum password length can be set via a
240 -- profile option. If that profile option is null, the default is 5.
241 --
242 l_count := 0;
243 l_count := to_number(
244 nvl(fnd_profile.value('SIGNON_PASSWORD_LENGTH'), '5')
245 );
246
247 IF l_count > 8 AND p_password IS NULL
248 THEN
249 -- The random password generator can produce 8-byte alphanumeric string.
250 -- So any length more than 8 must be supplied by customers.
251 hr_utility.set_message(800, 'HR_USER_PASSWORD_LENGTH_INV');
252 hr_utility.raise_error;
253 END IF;
254 --
255 -- Customers can supply a password. If no password is supplied, we randomly
256 -- generate an 8-byte alphanumeric characters.
257 IF p_password IS NULL
258 THEN
259 --
260 l_password := generate_string (p_user_name => p_user_name
261 ,p_id => fnd_crypto.smallrandomnumber);
262 --
263 IF l_password IS NULL
264 THEN
265 hr_utility.set_message(800, 'HR_PASSWORD_NULL');
266 hr_utility.raise_error;
267 END IF;
268 --
269 ELSIF (length(p_password) < l_count)
270 THEN
271 fnd_message.set_name('FND', 'PASSWORD-LONGER');
272 fnd_message.set_token('LENGTH', to_char(l_count));
273 hr_utility.raise_error;
274 ELSIF (length(p_password) > 30)
275 THEN
276 hr_utility.set_message(800, 'HR_USER_PASSWORD_TOO_LONG');
277 hr_utility.raise_error;
278 ELSE
279 l_password := p_password;
280 END IF;
281 --
282 -----------------------------------------------------------------------------
283 -- Set fnd last_updated_by and last_update_login columns
284 -----------------------------------------------------------------------------
285 l_last_updated_by := fnd_global.user_id;
286 IF l_last_updated_by IS NULL
287 THEN
288 l_last_updated_by := -1;
289 END IF;
290 --
291 l_last_update_login := fnd_global.login_id;
292 IF l_last_update_login IS NULL
293 THEN
294 l_last_update_login := -1;
295 END IF;
296 --
297 -- Now, we're ready to call fnd api to create a user name.
298 --
299 hr_utility.set_location (l_proc || ' before fnd_user_pkg.CreateUser', 30);
300 --
301 -- Fix 2288014 Start
302
303 BEGIN
304 -- Using fnd_user_pkg.CreateUserId is useful as we do not have to write another select
305 -- query to retrieve user_id based on user_name.
306
307 p_user_id := fnd_user_pkg.CreateUserId (
308 x_user_name => p_user_name,
309 x_owner => '',
310 x_unencrypted_password => l_password,
311 x_start_date => l_fnd_user_start_date,
312 x_end_date => l_fnd_user_end_date,
313 -- x_last_logon_date => sysdate, -- For BUG 7116804
314 x_description => p_description,
315 x_password_date => p_password_date, -- Fix 2288014
316 x_employee_id => p_employee_id,
317 x_email_address => p_email_address,
318 x_fax => p_fax,
319 x_customer_id => p_customer_id,
320 x_supplier_id => p_supplier_id);
321
322 EXCEPTION
323 WHEN OTHERS THEN
324 raise e_create_fnd_user;
325 END;
326
327 -- Set out parameters
328
329 p_password := l_password;
330
331
332 --
333 hr_utility.set_location('Leaving:' || l_proc, 70);
334 --
335
336 EXCEPTION
337 WHEN OTHERS THEN
338
339 hr_utility.raise_error;
340
341 -- Fix 2288014 End
342
343
344 END create_fnd_user;
345 --
346 -- ----------------------------------------------------------------------------
347 -- |---------------------- < create_fnd_responsibility > ----------------------|
348 -- | |
349 -- |NOTE: No savepoint will be issued here because business support internal |
350 -- | process is not supposed to issue any savepoint or rollback. |
351 -- ----------------------------------------------------------------------------
352 --
353 PROCEDURE create_fnd_responsibility
354 (p_resp_key in fnd_responsibility.responsibility_key%type
355 ,p_resp_name in fnd_responsibility_tl.responsibility_name%type
356 ,p_resp_app_id in fnd_responsibility.application_id%type
357 ,p_resp_description in fnd_responsibility_tl.description%type
358 default null
359 ,p_start_date in fnd_responsibility.start_date%type
360 ,p_end_date in fnd_responsibility.end_date%type default null
361 ,p_data_group_name in fnd_data_groups_standard_view.data_group_name%type
362 ,p_data_group_app_id in fnd_responsibility.data_group_application_id%type
363 ,p_menu_name in fnd_menus.menu_name%type
364 ,p_request_group_name in fnd_request_groups.request_group_name%type
365 default null
366 ,p_request_group_app_id in fnd_responsibility.group_application_id%type
367 default null
368 ,p_version in fnd_responsibility.version%type default '4'
369 ,p_web_host_name in fnd_responsibility.web_host_name%type default null
370 ,p_web_agent_name in fnd_responsibility.web_agent_name%type
371 default null
372 ,p_responsibility_id out nocopy number
373 ) IS
374 --
375 CURSOR lc_get_app_short_name (c_app_id in number) IS
376 SELECT application_short_name
377 FROM fnd_application
378 WHERE application_id = c_app_id;
379 --
380 -- The following check unique resp key sql is copied from FNDSCRSP.fmb,
381 -- program unit FND_UNIQUE_RESP_KEY.
382 --
383 CURSOR lc_unique_resp_key IS
384 SELECT 1
385 FROM sys.dual
386 WHERE NOT EXISTS
387 (SELECT 1
388 FROM fnd_responsibility
389 WHERE responsibility_key = p_resp_key
390 AND application_id = p_resp_app_id);
391 --
392 -- The following check unique resp name sql is copied from FNDSCRSP.fmb,
393 -- program unit FND_UNIQUE_RESP_NAME.
394 --
395 CURSOR lc_unique_resp_name IS
396 SELECT 1
397 FROM sys.dual
398 WHERE NOT EXISTS
399 (SELECT 1
400 FROM fnd_responsibility_vl
401 WHERE responsibility_name = p_resp_name
402 AND application_id = p_resp_app_id);
403 --
404 CURSOR lc_get_data_group_id IS
405 SELECT data_group_id
406 FROM fnd_data_groups_standard_view
407 WHERE data_group_name = p_data_group_name;
408 --
409 CURSOR lc_get_menu_id IS
410 SELECT menu_id
411 FROM fnd_menus
412 WHERE menu_name = p_menu_name;
413 --
414 CURSOR lc_get_req_group_id IS
415 SELECT request_group_id
416 FROM fnd_request_groups
417 WHERE request_group_name = p_request_group_name
418 AND application_id = p_request_group_app_id;
419 --
420 CURSOR lc_generate_resp_id IS
421 SELECT fnd_responsibility_s.nextval
422 FROM sys.dual;
423
424
425 l_proc varchar2(72) := g_package||'create_fnd_responsibility';
426 l_resp_app_short_name fnd_application.application_short_name%type := null;
427 l_data_grp_app_short_name fnd_application.application_short_name%type := null;
428 l_req_grp_app_short_name fnd_application.application_short_name%type := null;
429 l_dummy number default null;
430 l_request_group_app_id fnd_responsibility.group_application_id%type
431 default null;
432 l_responsibility_id fnd_responsibility.responsibility_id%type default null;
433 l_data_grp_id fnd_data_groups.data_group_id%type default null;
434 l_req_grp_app_id fnd_request_groups.application_id%type default null;
435 --
436 BEGIN
437 hr_utility.set_location('Entering:' || l_proc, 10);
438 --
439 -- Validate input parameters first
440 -- Validate responsibility application id exists
441 --
442 OPEN lc_get_app_short_name (c_app_id => p_resp_app_id);
443 FETCH lc_get_app_short_name into l_resp_app_short_name;
444 IF lc_get_app_short_name%NOTFOUND
445 THEN
446 CLOSE lc_get_app_short_name;
447 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
448 fnd_message.set_token('TABLE', 'FND_APPLICATION');
449 fnd_message.set_token('COLUMN', 'APPLICATION_ID');
450 fnd_message.set_token('VALUE', to_char(p_resp_app_id));
451 hr_utility.raise_error;
452 ELSE
453 CLOSE lc_get_app_short_name;
454 END IF;
455
456 -- Get the Data Group Application Short Name because fnd api uses the
457 -- short name as input parameter instead of the id.
458 OPEN lc_get_app_short_name (c_app_id => p_data_group_app_id);
459 FETCH lc_get_app_short_name into l_data_grp_app_short_name;
460 IF lc_get_app_short_name%NOTFOUND
461 THEN
462 CLOSE lc_get_app_short_name;
463 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
464 fnd_message.set_token('TABLE', 'FND_APPLICATION');
465 fnd_message.set_token('COLUMN', 'APPLICATION_ID');
466 fnd_message.set_token('VALUE', to_char(p_data_group_app_id));
467 hr_utility.raise_error;
468 ELSE
469 CLOSE lc_get_app_short_name;
470 END IF;
471
472 -- Get the Request Group Application Short Name because fnd api uses the
473 -- short name as input parameter instead of the id.
474 -- Only get the short name when the p_request_group_app_id is not null.
475
476 IF p_request_group_app_id IS NOT NULL
477 THEN
478 OPEN lc_get_app_short_name (c_app_id => p_request_group_app_id);
479 FETCH lc_get_app_short_name into l_req_grp_app_short_name;
480 IF lc_get_app_short_name%NOTFOUND
481 THEN
482 CLOSE lc_get_app_short_name;
483 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
484 fnd_message.set_token('TABLE', 'FND_APPLICATION');
485 fnd_message.set_token('COLUMN', 'APPLICATION_ID');
486 fnd_message.set_token('VALUE', to_char(p_request_group_app_id));
487 hr_utility.raise_error;
488 ELSE
489 CLOSE lc_get_app_short_name;
490 END IF;
491 END IF;
492 --
493 -- Validate Data Group Application ID
494 --
495 OPEN lc_get_data_group_id;
496 FETCH lc_get_data_group_id into l_data_grp_id;
497 IF lc_get_data_group_id%NOTFOUND
498 THEN
499 CLOSE lc_get_data_group_id;
500 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
501 fnd_message.set_token('TABLE', 'FND_DATA_GROUPS_STANDARD_VIEW');
502 fnd_message.set_token('COLUMN', 'DATA_GROUP_NAME');
503 fnd_message.set_token('VALUE', p_data_group_name);
504 hr_utility.raise_error;
505 ELSE
506 CLOSE lc_get_data_group_id;
507 END IF;
508 --
509 --
510 -- Validate unique responsibility key
511 --
512 OPEN lc_unique_resp_key;
513 FETCH lc_unique_resp_key into l_dummy;
514 IF lc_unique_resp_key%NOTFOUND
515 THEN
516 CLOSE lc_unique_resp_key;
517 fnd_message.set_name('FND', 'SECURITY-DUPLICATE RESP NAME');
518 hr_utility.raise_error;
519 ELSE
520 CLOSE lc_unique_resp_key;
521 END IF;
522
523 --
524 -- Validate unique responsibility name
525 --
526 l_dummy := null;
527 OPEN lc_unique_resp_name;
528 FETCH lc_unique_resp_name into l_dummy;
529 IF lc_unique_resp_name%NOTFOUND
530 THEN
531 CLOSE lc_unique_resp_name;
532 fnd_message.set_name('FND', 'SECURITY-DUPLICATE RESP NAME');
533 hr_utility.raise_error;
534 ELSE
535 CLOSE lc_unique_resp_name;
536 END IF;
537
538 --
539 -- Validate Version
540 IF p_version = '4' OR
541 p_version = 'W'
542 THEN
543 null;
544 ELSE
545 hr_utility.set_message(800, 'HR_INVALID_RESP_VERSION');
546 hr_utility.raise_error;
547 END IF;
548 --
549 -- Validate End Date must be >= Start Date
550 IF p_end_date IS NOT NULL
551 THEN
552 IF p_end_date < nvl(p_start_date, p_end_date + 1)
553 THEN
554 hr_utility.set_message(800, 'HR_51070_CAU_START_END');
555 hr_utility.raise_error;
556 END IF;
557 END IF;
558 --
559 -- Validate data_group_name
560 --
561 l_dummy := null;
562 OPEN lc_get_data_group_id;
563 FETCH lc_get_data_group_id into l_dummy;
564 IF lc_get_data_group_id%NOTFOUND
565 THEN
566 CLOSE lc_get_data_group_id;
567 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
568 fnd_message.set_token('TABLE', 'FND_DATA_GROUPS_STANDARD_VIEW');
569 fnd_message.set_token('COLUMN', 'DATA_GROUP_NAME');
570 fnd_message.set_token('VALUE', p_data_group_name);
571 hr_utility.raise_error;
572 ELSE
573 CLOSE lc_get_data_group_id;
574 END IF;
575
576 --
577 -- Validate menu name
578 --
579 l_dummy := null;
580 OPEN lc_get_menu_id;
581 FETCH lc_get_menu_id into l_dummy;
582 IF lc_get_menu_id%NOTFOUND
583 THEN
584 CLOSE lc_get_menu_id;
585 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
586 fnd_message.set_token('TABLE', 'FND_MENUS_VL');
587 fnd_message.set_token('COLUMN', 'MENU_NAME');
588 fnd_message.set_token('VALUE', p_menu_name);
589 hr_utility.raise_error;
590 ELSE
591 CLOSE lc_get_menu_id;
592 END IF;
593
594 --
595 -- Validate request_group_name and request_group_app_id only if both
596 -- parameters are not null.
597 --
598 l_dummy := null;
599
600 IF p_request_group_name IS NOT NULL AND
601 p_request_group_app_id IS NOT NULL
602 THEN
603 OPEN lc_get_req_group_id;
604 FETCH lc_get_req_group_id into l_dummy;
605 IF lc_get_req_group_id%NOTFOUND
606 THEN
607 CLOSE lc_get_req_group_id;
608 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
609 fnd_message.set_token('TABLE', 'FND_REQUEST_GROUPS');
610 fnd_message.set_token('COLUMN',
611 '(REQUEST_GROUP_NAME, REQUEST_GROUP_APP_ID)');
612 fnd_message.set_token('VALUE', p_request_group_name || ', ' ||
613 to_char(p_request_group_app_id));
614 hr_utility.raise_error;
615 ELSE
616 CLOSE lc_get_req_group_id;
617 END IF;
618 END IF;
619 --
620 -- The following is mimicking FNDSCRSP.fmb program unit
621 -- FND_CLEAR_REQUEST_GROUP code.
622 -- Clear request group
623 IF p_request_group_name is null
624 THEN
625 l_request_group_app_id := null;
626 ELSE
627 l_request_group_app_id := p_request_group_app_id;
628 END IF;
629 --
630 -- Generate responsibility id
631 --
632 l_responsibility_id := null;
633 OPEN lc_generate_resp_id;
634 FETCH lc_generate_resp_id into l_responsibility_id;
635 IF lc_generate_resp_id%NOTFOUND
636 THEN
637 CLOSE lc_generate_resp_id;
638 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
639 fnd_message.set_token('SEQUENCE', 'FND_RESPONSIBILITY_S');
640 fnd_message.set_token('COLUMN', 'RESPONSIBILITY_ID');
641 fnd_message.set_token('VALUE', 'NULL');
642 hr_utility.raise_error;
643 ELSE
644 CLOSE lc_generate_resp_id;
645 END IF;
646
647 --
648 -- Now call the fnd create responsibility api which needs the app short name.
649 --
650 hr_utility.set_location(l_proc ||
651 ' before fnd_function_security.responsibility', 30);
652 --
653 fnd_function_security.responsibility
654 (responsibility_id => l_responsibility_id
655 ,responsibility_key => p_resp_key
656 ,responsibility_name => p_resp_name
657 ,application => l_resp_app_short_name
658 ,description => p_resp_description
659 ,start_date => p_start_date
660 ,end_date => p_end_date
661 ,data_group_name => p_data_group_name
662 ,data_group_application => l_data_grp_app_short_name
663 ,menu_name => p_menu_name
664 ,request_group_name => p_request_group_name
665 ,request_group_application => l_req_grp_app_short_name
666 ,version => p_version
667 ,web_host_name => p_web_host_name
668 ,web_agent_name => p_web_agent_name
669 );
670 --
671 p_responsibility_id := l_responsibility_id;
672
673 hr_utility.set_location('Leaving:'||l_proc, 50);
674
675 END create_fnd_responsibility;
676 --
677 -- ----------------------------------------------------------------------------
678 -- |-------------------- < create_fnd_user_resp_groups > ----------------------|
679 -- |NOTE: No savepoint will be issued here because business support internal |
680 -- | process is not supposed to issue any savepoint or rollback. |
681 -- ----------------------------------------------------------------------------
682 --
683 PROCEDURE create_fnd_user_resp_groups
684 (p_user_id in fnd_user.user_id%type
685 ,p_responsibility_id in fnd_responsibility.responsibility_id%type
686 ,p_application_id in
687 fnd_user_resp_groups.responsibility_application_id%type
688 ,p_sec_group_id in fnd_user_resp_groups.security_group_id%type
689 ,p_start_date in fnd_user_resp_groups.start_date%type
690 ,p_end_date in fnd_user_resp_groups.end_date%type
691 default null
692 ,p_description in fnd_user_resp_groups.description%type
693 default null
694 ) IS
695 --
696 CURSOR lc_get_user_id IS
697 SELECT user_id
698 FROM fnd_user
699 WHERE user_id = p_user_id;
700 --
701 CURSOR lc_get_resp_id IS
702 SELECT responsibility_id
703 FROM fnd_responsibility
704 WHERE responsibility_id = p_responsibility_id;
705 --
706 CURSOR lc_get_app_id IS
707 SELECT application_id
708 FROM fnd_application
709 WHERE application_id = p_application_id;
710 --
711 --
712 CURSOR lc_unique_user_resp_groups IS
713 SELECT user_id
714 FROM fnd_user_resp_groups
715 WHERE user_id = p_user_id
716 AND responsibility_application_id = p_application_id
717 AND responsibility_id = p_responsibility_id
718 AND security_group_id = p_sec_group_id;
719
720 l_proc varchar2(72) := g_package || 'create_fnd_user_resp_groups';
721 l_dummy number default null;
722 --
723 BEGIN
724 hr_utility.set_location('Entering:' || l_proc, 10);
725 --
726 -- Validate input parameters first. We need to validate input parameter
727 -- again here because users can just invoke this procedure without calling
728 -- create_fnd_user_api first.
729 --
730 -- Validate user_id
731 --
732 l_dummy := null;
733
734 OPEN lc_get_user_id;
735 FETCH lc_get_user_id into l_dummy;
736 IF lc_get_user_id%NOTFOUND
737 THEN
738 CLOSE lc_get_user_id;
739 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
740 fnd_message.set_token('TABLE', 'FND_USER');
741 fnd_message.set_token('COLUMN', 'USER_ID');
742 fnd_message.set_token('VALUE', to_char(p_user_id));
743 hr_utility.raise_error;
744 ELSE
745 CLOSE lc_get_user_id;
746 END IF;
747 --
748 -- Validate responsibility_id
749 --
750 l_dummy := null;
751
752 OPEN lc_get_resp_id;
753 FETCH lc_get_resp_id into l_dummy;
754 IF lc_get_resp_id%NOTFOUND
755 THEN
756 CLOSE lc_get_resp_id;
757 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
758 fnd_message.set_token('TABLE', 'FND_RESPONSIBILITY');
759 fnd_message.set_token('COLUMN', 'RESPONSIBILITY_ID');
760 fnd_message.set_token('VALUE', to_char(p_responsibility_id));
761 hr_utility.raise_error;
762 ELSE
763 CLOSE lc_get_resp_id;
764 END IF;
765 --
766 -- Validate application_id
767 --
768 l_dummy := null;
769
770 OPEN lc_get_app_id;
771 FETCH lc_get_app_id into l_dummy;
772 IF lc_get_app_id%NOTFOUND
773 THEN
774 CLOSE lc_get_app_id;
775 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
776 fnd_message.set_token('TABLE', 'FND_APPLICATION');
777 fnd_message.set_token('COLUMN', 'APPLICATION_ID');
778 fnd_message.set_token('VALUE', to_char(p_application_id));
779 hr_utility.raise_error;
780 ELSE
781 CLOSE lc_get_app_id;
782 END IF;
783 --
784 -- Validate unique user_resp_groups
785
786 l_dummy := null;
787
788 BEGIN
789 OPEN lc_unique_user_resp_groups;
790 FETCH lc_unique_user_resp_groups into l_dummy;
791 CLOSE lc_unique_user_resp_groups;
792 --
793 IF l_dummy IS NOT NULL
794 THEN
795 fnd_message.set_name('FND', 'SECURITY-DUPLICATE USER RESP');
796 hr_utility.raise_error;
797 END IF;
798
799 EXCEPTION
800 WHEN NO_DATA_FOUND THEN
801 -- It's ok. That means there is no duplicate.
802 CLOSE lc_unique_user_resp_groups;
803 END;
804 --
805 -- Validate Start Date cannot be null
806 IF p_start_date IS NULL
807 THEN
808 hr_utility.set_message(800, 'HR_50374_SSL_MAND_START_DATE');
809 hr_utility.raise_error;
810 END IF;
811
812
813 -- Validate End Date must be >= Start Date
814 IF p_end_date IS NOT NULL
815 THEN
816 IF p_end_date < nvl(p_start_date, p_end_date + 1)
817 THEN
818 hr_utility.set_message(800, 'HR_51070_CAU_START_END');
819 hr_utility.raise_error;
820 END IF;
821 END IF;
822 --
823 -- Now call the fnd_user_resp_groups_api
824 --
825 hr_utility.set_location(l_proc ||
826 ' before fnd_user_resp_groups_api.insert_assignment', 30);
827 --
828 fnd_user_resp_groups_api.insert_assignment
829 (user_id => p_user_id
830 ,responsibility_id => p_responsibility_id
831 ,responsibility_application_id => p_application_id
832 ,security_group_id => p_sec_group_id
833 ,start_date => p_start_date
834 ,end_date => p_end_date
835 ,description => p_description
836 );
837
838 --
839 hr_utility.set_location('Leaving:'||l_proc, 50);
840
841 END create_fnd_user_resp_groups;
842 --
843 -- ----------------------------------------------------------------------------
844 -- |---------------------- < create_sec_profile_asg > ------------------------|
845 -- ----------------------------------------------------------------------------
846 --
847 PROCEDURE create_sec_profile_asg
848 (p_user_id in fnd_user.user_id%type
849 ,p_sec_group_id in fnd_security_groups.security_group_id%type
850 ,p_sec_profile_id in per_security_profiles.security_profile_id%type
851 ,p_resp_key in fnd_responsibility.responsibility_key%type
852 ,p_resp_app_id in
853 per_sec_profile_assignments.responsibility_application_id%type
854 ,p_start_date in per_sec_profile_assignments.start_date%type
855 ,p_end_date in per_sec_profile_assignments.end_date%type
856 default null
857 ,p_business_group_id in per_sec_profile_assignments.business_group_id%type
858 default null
859 ) IS
860 --
861 --
862
863 CURSOR lc_get_user_id IS
864 SELECT user_id
865 FROM fnd_user
866 WHERE user_id = p_user_id;
867 --
868 CURSOR lc_get_sec_group_id IS
869 SELECT security_group_id
870 FROM fnd_security_groups
871 WHERE security_group_id = p_sec_group_id;
872 --
873 CURSOR lc_get_sec_profile IS
874 SELECT security_profile_id
875 ,business_group_id
876 FROM per_security_profiles
877 WHERE security_profile_id = p_sec_profile_id;
878 --
879 CURSOR lc_get_resp_id IS
880 SELECT responsibility_id
881 FROM fnd_responsibility
882 WHERE responsibility_key = p_resp_key
883 AND application_id = p_resp_app_id;
884
885 l_bg_id per_security_profiles.business_group_id%type := null;
886 l_resp_id fnd_responsibility.responsibility_id%type := null;
887 l_dummy number default null;
888 l_sec_prof_asg_id
889 per_sec_profile_assignments.sec_profile_assignment_id%type := null;
890 l_obj_vers_num
891 per_sec_profile_assignments.object_version_number%type := null;
892 l_proc varchar2(72) := g_package|| 'create_sec_profile_asg';
893
894 BEGIN
895 hr_utility.set_location('Entering:' || l_proc, 10);
896
897 -- Validate input parameters first. We need to validate input parameter
898 -- again here because users can just invoke this procedure without calling
899 -- fnd_user_acct_api first.
900 --
901 -- Validate user_id
902 --
903 l_dummy := null;
904
905 OPEN lc_get_user_id;
906 FETCH lc_get_user_id into l_dummy;
907 IF lc_get_user_id%NOTFOUND
908 THEN
909 CLOSE lc_get_user_id;
910 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
911 fnd_message.set_token('TABLE', 'FND_USER');
912 fnd_message.set_token('COLUMN', 'USER_ID');
913 fnd_message.set_token('VALUE', to_char(p_user_id));
914 hr_utility.raise_error;
915 ELSE
916 CLOSE lc_get_user_id;
917 END IF;
918 --
919 -- Validate responsibility_id
920 --
921 OPEN lc_get_resp_id;
922 FETCH lc_get_resp_id into l_resp_id;
923 IF lc_get_resp_id%NOTFOUND
924 THEN
925 CLOSE lc_get_resp_id;
926 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
927 fnd_message.set_token('TABLE', 'FND_RESPONSIBILITY');
928 fnd_message.set_token('COLUMN', 'RESPONSIBILITY_KEY');
929 fnd_message.set_token('VALUE', p_resp_key);
930 hr_utility.raise_error;
931 ELSE
932 CLOSE lc_get_resp_id;
933 END IF;
934 --
935 -- Validate security_group_id
936 --
937 l_dummy := null;
938
939 OPEN lc_get_sec_group_id;
940 FETCH lc_get_sec_group_id into l_dummy;
941 IF lc_get_sec_group_id%NOTFOUND
942 THEN
943 CLOSE lc_get_sec_group_id;
944 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
945 fnd_message.set_token('TABLE', 'FND_SECURITY_GROUPS');
946 fnd_message.set_token('COLUMN', 'SECURITY_GROUP_ID');
947 fnd_message.set_token('VALUE', to_char(p_sec_group_id));
948 hr_utility.raise_error;
949 ELSE
950 CLOSE lc_get_sec_group_id;
951 END IF;
952 --
953 -- Validate security_profile_id
954 l_dummy := null;
955
956 OPEN lc_get_sec_profile;
957 FETCH lc_get_sec_profile into l_dummy, l_bg_id;
958 --
959 IF lc_get_sec_profile%NOTFOUND
960 THEN
961 CLOSE lc_get_sec_profile;
962 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
963 fnd_message.set_token('TABLE', 'PER_SECURITY_PROFILES');
964 fnd_message.set_token('COLUMN', 'SECURITY_PROFILE_ID');
965 fnd_message.set_token('VALUE', to_char(p_sec_profile_id));
966 hr_utility.raise_error;
967 ELSE
968 CLOSE lc_get_sec_profile;
969 END IF;
970 --
971 -- Validate Start Date cannot be null
972 IF p_start_date IS NULL
973 THEN
974 hr_utility.set_message(800, 'HR_50374_SSL_MAND_START_DATE');
975 hr_utility.raise_error;
976 END IF;
977 --
978 -- Validate End Date must be >= Start Date
979 IF p_end_date IS NOT NULL
980 THEN
981 IF p_end_date < nvl(p_start_date, p_end_date + 1)
982 THEN
983 hr_utility.set_message(800, 'HR_51070_CAU_START_END');
984 hr_utility.raise_error;
985 END IF;
986 END IF;
987 --
988 -- Now call the per_asp_ins.ins which will insert a row into
989 -- per_sec_profile_assignments as well as fnd_user_resp_groups.
990 per_asp_ins.ins
991 (p_user_id => p_user_id
992 ,p_security_group_id => p_sec_group_id
993 ,p_business_group_id => l_bg_id
994 ,p_security_profile_id => p_sec_profile_id
995 ,p_responsibility_id => l_resp_id
996 ,p_responsibility_application_i => p_resp_app_id
997 ,p_start_date => p_start_date
998 ,p_end_date => p_end_date
999 ,p_sec_profile_assignment_id => l_sec_prof_asg_id
1000 ,p_object_version_number => l_obj_vers_num
1001 );
1002
1003 --
1004 hr_utility.set_location('Leaving:'||l_proc, 50);
1005
1006
1007 END create_sec_profile_asg;
1008 --
1009 --
1010 -- ----------------------------------------------------------------------------
1011 -- |---------------------- < create_fnd_profile_values > ----------------------|
1012 -- |NOTE: No savepoint will be issued here because business support internal |
1013 -- | process is not supposed to issue any savepoint or rollback. |
1014 -- ----------------------------------------------------------------------------
1015 --
1016 PROCEDURE create_fnd_profile_values
1017 (p_profile_opt_name in fnd_profile_options.profile_option_name%type
1018 ,p_profile_opt_value in fnd_profile_option_values.profile_option_value%type
1019 ,p_profile_level_name in varchar2
1020 ,p_profile_level_value in fnd_profile_option_values.level_value%type
1021 ,p_profile_lvl_val_app_id in
1022 fnd_profile_option_values.level_value_application_id%type default null
1023 ,p_profile_value_saved out nocopy boolean
1024 ) IS
1025 --
1026 --
1027 CURSOR lc_get_update_flag
1028 IS
1029 SELECT resp_update_allowed_flag
1030 ,user_update_allowed_flag
1031 ,sql_validation
1032 FROM fnd_profile_options
1033 WHERE profile_option_name = p_profile_opt_name;
1034 --
1035 l_resp_update_allowed_flag fnd_profile_options.resp_update_allowed_flag%type
1036 default null;
1037 l_user_update_allowed_flag fnd_profile_options.user_update_allowed_flag%type
1038 default null;
1039 l_profile_val_saved boolean default null;
1040 l_sql_validation varchar2(2000) default null;
1041 l_num_data number default null;
1042 l_varchar2_data varchar2(2000) default null;
1043 l_profile_opt_value_valid boolean default null;
1044 l_proc varchar2(72) := g_package||'create_fnd_profile_values';
1045
1046 BEGIN
1047 hr_utility.set_location('Entering:' || l_proc, 10);
1048 --
1049 -- Validate input parameters first. We need to validate input parameter
1050 -- again here because users can just invoke this procedure without calling
1051 -- fnd_user_resp_wrapper first.
1052 --
1053 -- Validate profile options to determine if it is updateable
1054 OPEN lc_get_update_flag;
1055 FETCH lc_get_update_flag INTO l_resp_update_allowed_flag
1056 ,l_user_update_allowed_flag
1057 ,l_sql_validation;
1058 IF lc_get_update_flag%NOTFOUND THEN
1059 CLOSE lc_get_update_flag;
1060 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
1061 fnd_message.set_token('TABLE', 'FND_PROFILE_OPTIONS');
1062 fnd_message.set_token('COLUMN', 'PROFILE_OPTION_NAME');
1063 fnd_message.set_token('VALUE', p_profile_opt_name);
1064 hr_utility.raise_error;
1065 END IF;
1066 --
1067 CLOSE lc_get_update_flag;
1068
1069 -- For responsibility level (profile_level_id = 10003), the
1070 -- resp_update_allowed_flag must be 'Y'. If not, raise an error.
1071
1072 IF upper(p_profile_level_name) = 'RESP' AND
1073 l_resp_update_allowed_flag <> 'Y'
1074 THEN
1075 fnd_message.set_name('FND', 'PROFILES- CANT UPDATE');
1076 hr_utility.raise_error;
1077 ELSIF upper(p_profile_level_name) = 'USER' AND
1078 l_user_update_allowed_flag <> 'Y'
1079 THEN
1080 fnd_message.set_name('FND', 'PROFILES- CANT UPDATE');
1081 hr_utility.raise_error;
1082 END IF;
1083 --
1084 IF l_sql_validation IS NOT NULL
1085 THEN
1086 l_profile_opt_value_valid := null;
1087 l_num_data := null;
1088 l_varchar2_data := null;
1089 hr_user_acct_internal.validate_profile_opt_value
1090 (p_profile_opt_name => p_profile_opt_name
1091 ,p_profile_opt_value => p_profile_opt_value
1092 ,p_profile_level_value => p_profile_level_value
1093 ,p_profile_level_name => p_profile_level_name
1094 ,p_sql_validation => l_sql_validation
1095 ,p_profile_opt_value_valid => l_profile_opt_value_valid
1096 ,p_num_data => l_num_data
1097 ,p_varchar2_data => l_varchar2_data);
1098 END IF;
1099
1100
1101 hr_utility.set_location(l_proc || ' before fnd_profile.save', 30);
1102 --
1103 IF l_profile_opt_value_valid THEN
1104 l_profile_val_saved := fnd_profile.save
1105 (x_name => p_profile_opt_name
1106 ,x_value => p_profile_opt_value
1107 ,x_level_name => p_profile_level_name
1108 ,x_level_value => p_profile_level_value
1109 ,x_level_value_app_id =>
1110 to_char(p_profile_lvl_val_app_id)
1111 );
1112
1113 ELSE
1114 IF upper(p_profile_level_name) = 'RESP'
1115 THEN
1116 fnd_message.set_name('PER', 'HR_PROFILE_VAL_NOT_ADDED');
1117 fnd_message.set_token('RESP_ID', p_profile_level_value);
1118 ELSIF upper(p_profile_level_name) = 'USER'
1119 THEN
1120 fnd_message.set_name('PER', 'HR_PROFILE_USER_VAL_NOT_ADDED');
1121 END IF;
1122
1123 fnd_message.set_token('PROFILE_OPTION_NAME', p_profile_opt_name);
1124 fnd_message.set_token('PROFILE_OPTION_VALUE', p_profile_opt_value);
1125
1126 hr_utility.raise_error;
1127 END IF;
1128
1129 /*
1130 --
1131 -- In R11.5, not sure if the PER_BUSINESS_GROUP_ID is set to be not
1132 -- updateable in both RESP and USER Level. It looks like it is updateable in
1133 -- the seed 11.5 database. The following code to derive the profile option
1134 -- value from PER_SECURITY_PROFILE_ID is commented out.
1135 IF l_profile_val_saved
1136 AND p_profile_opt_name = 'PER_SECURITY_PROFILE_ID'
1137 THEN
1138 -- Use the Business Group Id derived from the security profile to set
1139 -- the PER_BUSINESS_GROUP_ID profile option.
1140 l_profile_val_saved := null;
1141 l_profile_val_saved := fnd_profile.save
1142 (x_name => 'PER_BUSINESS_GROUP_ID'
1143 ,x_value => to_char(l_num_data)
1144 ,x_level_name => p_profile_level_name
1145 ,x_level_value => p_profile_level_value
1146 ,x_level_value_app_id =>
1147 to_char(p_profile_lvl_val_app_id)
1148 );
1149 END IF;
1150
1151 */
1152
1153 p_profile_value_saved := l_profile_val_saved;
1154
1155 hr_utility.set_location('Leaving:'||l_proc, 50);
1156
1157
1158 END create_fnd_profile_values;
1159
1160 -- ----------------------------------------------------------------------------
1161 -- |--------------------- < validate_profile_opt_value > ----------------------|
1162 -- | Validate profile options which use SQL validation. The SQL validation |
1163 -- | will be hard coded here for a given profile option name because there is |
1164 -- | no pl/sql parser to parse the SQL statement. |
1165 -- | |
1166 -- | OUTPUT: |
1167 -- | p_profile_opt_value_valid - boolean, indicating whether the value is |
1168 -- | valid or not after validation. |
1169 -- | p_num_data - number, not always has a value in this output. This is to |
1170 -- | save another database call if certain values can be |
1171 -- | retrieved while validating the profile option value. For |
1172 -- | example, PER_SECURITY_PROFILE_ID, the business group id can|
1173 -- | be derived while running the sql to validate the security |
1174 -- | profile id. |
1175 -- | p_varchar2_data - varchar2, not always has a value in this output. This |
1176 -- | is to save another database call if certain values can be |
1177 -- | retrieved while validating the profile option value. See |
1178 -- | p_num_data above. |
1179 -- ----------------------------------------------------------------------------
1180 -- There are only 21 profile options (application_id between 800 and 899)
1181 -- which use SQL validation at the time of coding (May 2000) in R11.5 and the
1182 -- options are updateable at either Responsibility or User level.
1183 -- Profile Option Name Validation Table
1184 -- --------------------------------- ----------------------------------------
1185 -- DATETRACK:DATE_SECURITY FND_COMMON_LOOKUPS where lookup_type =
1186 -- 'DATETRACK:DATE_SECURITY'
1187 --
1188 -- DATETRACK:SESSION_DATE_WARNING FND_COMMON_LOOKUPS where lookup_type =
1189 -- 'DATETRACK:SESSION_DATE_WARNING'
1190 --
1191 -- HR:EXECUTE_LEG_FORMULA fnd_lookups where lookup_type ='YES_NO'
1192 --
1193 -- HR_DISPLAY_PERSON_SEARCH fnd_lookups where lookup_type ='YES_NO'
1194 --
1195 -- HR_DISPLAY_SKILLS FND_COMMON_LOOKUPS where lookup_type =
1196 -- 'YES_NO'
1197 --
1198 -- HR_ELE_ENTRY_PURGE_CONTROL hr_lookups where lookup_type =
1199 -- 'HR_ELE_ENTRY_PURGE_CONTROL'
1200 --
1201 -- HR_OTF_UPDATE_METHOD hr_lookups where lookup_type =
1202 -- 'PAY_US_OTF_UPDATE_METHODS'
1203 --
1204 -- HR_TIPS_TEST_MODE FND_COMMON_LOOKUPS where lookup_type =
1205 -- 'YES_NO'
1206 --
1207 -- HR_USER_TYPE FND_COMMON_LOOKUPS where lookup_type =
1208 -- 'HR_USER_TYPE'
1209 --
1210 -- OTA_AUTO_WAITLIST_BOOKING_STATUS ota_booking_status and
1211 -- hr_all_organization_units
1212 --
1213 -- OTA_PA_INTEGRATION fnd_lookups where lookup_type ='YES_NO'
1214 --
1215 -- PER_ABSENCE_DURATION_AUTO_OVERWRITE FND_COMMON_LOOKUPS where lookup_type =
1216 -- 'YES_NO'
1217 --
1218 -- PER_ATTACHMENT_USAGE fnd_lookups where lookup_type ='YES_NO'
1219 --
1220 -- PER_BUSINESS_GROUP_ID per_business_groups
1221 --
1222 -- PER_DEFAULT_CORRESPONDENCE_LANGUAGE fnd_languages
1223 --
1224 -- PER_DEFAULT_NATIONALITY FND_COMMON_LOOKUPS where lookup_type =
1225 -- 'NATIONALITY'
1226 --
1227 -- PER_NI_UNIQUE_ERROR_WARNING fnd_common_lookups where lookup_type =
1228 -- NI_UNIQUE_ERROR_WARNING'
1229 --
1230 -- PER_OAB_NEW_BENEFITS_MODEL fnd_lookups where lookup_type ='YES_NO'
1231 --
1232 -- PER_QUERY_ONLY_MODE fnd_lookups where lookup_type ='YES_NO'
1233 --
1234 -- PER_SECURITY_PROFILE_ID PER_SECURITY_PROFILES and
1235 -- HR_ALL_ORGANIZATION_UNITS
1236 --
1237 -- VIEW_UNPUBLISHED_360_SELF_APPR FND_COMMON_LOOKUPS where lookup_type =
1238 -- 'YES_NO'
1239 --
1240 -- *** The following options are not updateable in Responsibility or User
1241 -- level in Seed11.5 db as of May 2000 ***
1242 -- DATETRACK: DELETE_MODE
1243 -- DATETRACK: ENABLED
1244 -- DATETRACK: OVERRIDE_MODE
1245 -- DATETRACK: UPDATE_MODE
1246 -- HR_CROSS_BUSINESS_GROUP
1247 -- HR_DM_BG_LOCKOUT
1248 -- HR_PAYROLL_CONTACT_SOURCE
1249 -- OTA_AUTO_WAITLIST_ACTIVE
1250 -- OTA_WAITLIST_SORT_CRITERIA
1251 -- PAY_USER_FF_PTO
1252 --
1253 -- *** The following options are not found in Seed11.5 db as of May 2000 ***
1254 -- HR_PAYROLL_CURRENCY_RATES pay_payrolls_f and per_business_groups
1255 -- HR:EXECUTE_LEG_FORMULA fnd_lookups where lookup_type ='YES_NO'
1256 -- HR:COST_MAND_SEG_CHECK fnd_lookups where lookup_type ='YES_NO'
1257 -- HR_BG_LOCATIONS FND_COMMON_LOOKUPS where lookup_type =
1258 -- 'YES_NO'
1259 -- HR_BIS_REPORTING_HIERARCHY PER_ORGANIZATION_STRUCTURES_V
1260 --
1261 -- --------------------------------------------------------------------------
1262 -- Update for Fix 2288014 Start.
1263 -- Following are new profile options (application_id between 800 and 899)
1264 -- which use SQL validation at the time of coding (May 2002) in R11.5 and the
1265 -- options are updateable at either Responsibility or User level.
1266 -- This list is obtained from SEED115.
1267 -- Non-HR profile options are not used for validation.
1268 --
1269 -- Profile Option Name Validation Table
1270 -- --------------------------------- -----------------------------
1271 -- OM_DEFAULT_ENROLLMENT_CANCELLED_STATUS OTA_BOOKING_STATUS_TYPES
1272 -- PER_NATIONAL_IDENTIFIER_VALIDATION HR_LOOKUPS
1273 -- OM_DEFAULT_EVENT_CENTER HR_LOOKUPS
1274 -- OM_DEFAULT_EVENT_SYSTEM_STATUS HR_LOOKUPS
1275 -- OM_DEFAULT_EVENT_USER_STATUS HR_LOOKUPS
1276 -- HR_TAX_LOCATION_CHANGE FND_LOOKUPS
1277 -- OTA_SSHR_AUTO_GL_TRANSFER FND_LOOKUPS
1278 -- BEN_NEW_USER_RESP_PROFILE FND_RESPONSIBILITY_TL,
1279 -- FND_RESPONSIBILITY
1280 -- BEN_USER_TO_ORG_LINK HR_ALL_ORGANIZATION_UNITS,
1281 -- HR_ALL_ORGANIZATION_UNITS_TL
1282 -- OTA_HR_GLOBAL_BUSINESS_GROUP_ID PER_BUSINESS_GROUPS
1283 -- HR_DISPLAY_ALL_OFFERS FND_LOOKUPS
1284 -- HR_MONITOR_BALANCE_RETRIEVAL FND_LOOKUPS
1285 -- HR_CANCEL_APPLICATION FND_LOOKUPS
1286 -- HR_BLANK_EFFECTIVE_DATE FND_LOOKUPS
1287 -- HR_SSHR_LOCALIZATION FND_LOOKUPS
1288 -- HR_USE_GRADE_DEFAULTS FND_LOOKUPS
1289 -- HR_OVERRIDE_GRADE_DEFAULTS FND_LOOKUPS
1290 -- NL_DISPLAY_MESSAGE FND_LOOKUPS
1291 -- PAY_PPM_MULTI_ASSIGNMENT_ENABLE FND_LOOKUPS
1292 -- HR_ACTIONS_VALIDATION FND_COMMON_LOOKUPS
1293 -- OTA_DEFAULT_EVENT_OWNER PER_ALL_PEOPLE_F,
1294 -- HR_ALL_ORGANIZATION_UNITS,
1295 -- HR_ALL_ORGANIZATION_UNITS,
1296 -- PER_ALL_ASSIGNMENTS_F
1297 -- HR_USE_HIRE_MGR_APPR_CHAIN FND_LOOKUPS
1298 -- OTA_DEFAULT_EVENT_CENTER HR_ALL_ORGANIZATION_UNITS,
1299 -- HR_ORGANIZATION_INFORMATION,
1300 -- HR_ALL_ORGANIZATION_UNITS
1301 -- HR_NL_JOB_LEVEL_PROFILE FND_LOOKUP_VALUES
1302 -- BEN_USER_TO_PAYROLL_LINK PAY_ALL_PAYROLLS_F
1303 -- HR_BIS_REPORTING_HIERARCHY PER_ORGANIZATION_STRUCTURES_V,
1304 -- PER_BUSINESS_GROUPS
1305 -- HR_PERINFO_CHECK_PENDING FND_LOOKUPS
1306 -- HR_SELF_SERV_SAVEFORLATER FND_COMMON_LOOKUPS
1307 -- PAY_FR_CHECK_MANDATORY_ASG_ATTRIBUTES FND_LOOKUPS
1308 -- HR:COST_MAND_SEG_CHECK FND_LOOKUPS
1309 -- HR_MANAGER_ACTIONS_MENU FND_MENUS_VL
1310 -- HR_PERSONAL_ACTIONS_MENU FND_MENUS_VL
1311 -- PER_AUTO_EVAL_ENTITLEMENTS FND_LOOKUPS
1312 -- PER_CHECK_ENTITLEMENT_CACHE FND_LOOKUPS
1313 -- PER_AUTO_APPLY_ENTITLEMENTS FND_LOOKUPS
1314 -- PER_CAGR_LOG_DETAIL FND_COMMON_LOOKUPS
1315 -- HR_NL_ETHNICITY_PROFILE FND_LOOKUP_VALUES
1316 -- BEN_CWB_PREFERRED_CURRENCY FND_CURRENCIES_VL
1317 -- OTA_ILEARNING_DEFAULT_ACTIVITY OTA_ACTIVITY_DEFINITIONS,
1318 -- HR_ALL_ORGANIZATION_UNITS
1319 -- BEN_DSG_NO_CHG FND_LOOKUPS
1320 -- HR_RESTRICT_X_BUSINESS_TRAN FND_LOOKUPS
1321 -- HR_ALLOW_MULTIPLE_ASSIGNMENTS FND_LOOKUPS
1322 -- HR_NL_FULL_NAME_PROFILE FND_LOOKUP_VALUES
1323 -- OTA_ILEARNING_DEFAULT_ATTENDED OTA_BOOKING_STATUS_TYPES,
1324 -- HR_ALL_ORGANIZATION_UNITS
1325 -- HR_APPRAISAL_TEMPLATE_LOV FND_LOOKUPS
1326 -- OTA_OM_RESTRICT_ENR_BY_COUNTRY FND_LOOKUPS
1327 -- HR_AUTHORIA_ENABLED FND_LOOKUPS
1328 -- HXC_TIMEKEEPER_OVERRIDE HR_LOOKUPS
1329 -- IMC_VIS_SOL_TYPE FND_LOOKUP_VALUES_VL
1330 -- IRC_DEFAULT_COUNTRY FND_TERRITORIES_VL
1331 -- Update for Fix 2288014 End.
1332 -- Bug 2825757 : Added validation for ICX_LANGUAGE
1333 -- ----------------------------------------------------------------------------
1334 --
1335 PROCEDURE validate_profile_opt_value
1336 (p_profile_opt_name in fnd_profile_options.profile_option_name%type
1337 ,p_profile_opt_value in
1338 fnd_profile_option_values.profile_option_value%type
1339 ,p_profile_level_name in varchar2
1340 ,p_profile_level_value in fnd_profile_option_values.level_value%type
1341 ,p_sql_validation in fnd_profile_options.sql_validation%type
1342 ,p_profile_opt_value_valid out nocopy boolean
1343 ,p_num_data out nocopy number
1344 ,p_varchar2_data out nocopy varchar2
1345 ) IS
1346 --
1347 --
1348 -- FND_COMMON_LOOKUPS
1349 -- NOTE: Since the lookup_type is a unique key and is mandatory, we don't
1350 -- need to compare the application id. This will make the cursor more
1351 -- generic.
1352 --
1353 CURSOR lc_get_fnd_cmn_lkups (p_lookup_type in varchar2) IS
1354 SELECT lookup_code
1355 ,meaning
1356 FROM fnd_common_lookups
1357 WHERE lookup_type = p_lookup_type;
1358
1359 -- FND_LOOKUPS
1360 -- NOTE: Since the lookup_type is a unique key and is mandatory, we don't
1361 -- need to compare the application id. This will make the cursor more
1362 -- generic.
1363 --
1364 CURSOR lc_get_fnd_lkups (p_lookup_type in varchar2) IS
1365 SELECT lookup_code
1366 ,meaning
1367 FROM fnd_lookups
1368 WHERE lookup_type = p_lookup_type;
1369
1370 -- HR_LOOKUPS
1371 -- NOTE: Since the lookup_type is a unique key and is mandatory, we don't
1372 -- need to compare the application id. This will make the cursor more
1373 -- generic.
1374 --
1375 CURSOR lc_get_hr_lkups (p_lookup_type in varchar2) IS
1376 SELECT lookup_code
1377 ,meaning
1378 FROM hr_lookups
1379 WHERE lookup_type = p_lookup_type;
1380
1381 -- FND_LOOKUP_VALUES
1382 -- NOTE: Since the lookup_type is a unique key and is mandatory, we don't
1383 -- need to compare the application id. This will make the cursor more
1384 -- generic.
1385 --
1386 CURSOR lc_get_fnd_lkup_val (p_lookup_type in varchar2) IS
1387 SELECT lookup_code
1388 ,meaning
1389 FROM fnd_lookup_values
1390 WHERE lookup_type = p_lookup_type;
1391
1392 -- OTA_AUTO_WAITLIST_BOOKING_STATUS
1393 CURSOR lc_get_bkg_status IS
1394 SELECT bst.name booking_status
1395 ,bst.booking_status_type_id
1396 ,org.name org_name
1397 FROM ota_booking_status_types bst
1398 ,hr_organization_units org
1399 WHERE bst.business_group_id = org.organization_id
1400 AND bst.type in ('A', 'P')
1401 ORDER BY org.name;
1402
1403 -- PER_BUSINESS_GROUP_ID
1404 CURSOR lc_get_bg_id IS
1405 SELECT name
1406 ,business_group_id
1407 FROM per_business_groups;
1408
1409 -- PER_DEFAULT_CORRESPONDENCE_LANGUAGE
1410 CURSOR lc_get_def_lang IS
1411 SELECT initcap(NLS_LANGUAGE)
1412 ,language_code
1413 FROM fnd_languages
1414 ORDER BY 1;
1415
1416
1417 -- Since customers must need to supply the internal id instead of the name
1418 -- for profile option value, the ORDER BY clause will be ordered by id instead
1419 -- of name.
1420 -- PER_SECURITY_PROFILE_ID
1421 CURSOR lc_get_security_profile IS
1422 SELECT s.security_profile_name
1423 ,s.security_profile_id
1424 ,s.business_group_id
1425 ,o.name
1426 FROM per_security_profiles s
1427 ,hr_all_organization_units o
1428 WHERE o.business_group_id = s.business_group_id
1429 AND o.organization_id = o.business_group_id
1430 ORDER BY s.security_profile_id;
1431
1432 -- HR_PAYROLL_CURRENCY_RATES
1433 CURSOR lc_get_pay_curr_rates IS
1434 SELECT pay.payroll_name
1435 ,pay.payroll_id
1436 ,per.name
1437 FROM pay_payrolls_f pay
1438 ,per_business_groups per
1439 WHERE pay.business_group_id = per.business_group_id
1440 AND sysdate between effective_start_date and effective_end_date
1441 ORDER BY pay.payroll_id;
1442
1443 -- Fix 2288014 start
1444 -- Generic cursors are already defined for validations using
1445 -- fnd_lookups , hr_lookups, fnd_lookup_values and fnd_common_lookups.
1446 -- Define cursors for profile option validations
1447
1448 -- OM_DEFAULT_ENROLLMENT_CANCELLED_STATUS
1449
1450 CURSOR lc_get_enroll_cancel_sts IS
1451 SELECT BST.NAME visible_option_value , BST.BOOKING_STATUS_TYPE_ID profile_option_value
1452 from ota_booking_status_types bst,
1453 hr_all_organization_units org
1454 where bst.business_group_id = org.organization_id
1455 and bst.type = 'C' order by org.name, bst.name;
1456
1457
1458 -- BEN_NEW_USER_RESP_PROFILE
1459 CURSOR lc_get_new_usr_resp_profl IS
1460 SELECT L.RESPONSIBILITY_NAME visible_option_value ,TO_CHAR(L.RESPONSIBILITY_ID)|| TO_CHAR(L.APPLICATION_ID) profile_option_value
1461 FROM FND_RESPONSIBILITY_TL L,
1462 FND_RESPONSIBILITY R
1463 WHERE R.RESPONSIBILITY_ID = L.RESPONSIBILITY_ID
1464 AND R.APPLICATION_ID = L.APPLICATION_ID
1465 AND L.LANGUAGE = USERENV('LANG')
1466 AND R.APPLICATION_ID = 805;
1467
1468
1469 -- BEN_USER_TO_ORG_LINK
1470 CURSOR lc_get_usr_org_lnk IS
1471 SELECT HAO.NAME visible_option_value,HAO.ORGANIZATION_ID profile_option_value
1472 FROM HR_ALL_ORGANIZATION_UNITS HAO,
1473 HR_ALL_ORGANIZATION_UNITS_TL HAOTL
1474 WHERE HAO.ORGANIZATION_ID = HAOTL.ORGANIZATION_ID
1475 AND HAOTL.LANGUAGE = USERENV('LANG')
1476 AND SYSDATE BETWEEN HAO.DATE_FROM
1477 AND NVL(HAO.DATE_TO,SYSDATE)
1478 ORDER BY HAO.NAME;
1479
1480
1481 --OTA_HR_GLOBAL_BUSINESS_GROUP_ID
1482 CURSOR lc_get_glbl_bd_id IS
1483 SELECT NAME visible_option_value, BUSINESS_GROUP_ID profile_option_value
1484 FROM PER_BUSINESS_GROUPS;
1485
1486
1487 -- OTA_DEFAULT_EVENT_OWNER
1488 CURSOR lc_get_def_envt_ownr IS
1489 SELECT P.FULL_NAME visible_option_value,P.PERSON_ID profile_option_value
1490 FROM PER_ALL_PEOPLE_F P,HR_ALL_ORGANIZATION_UNITS O,HR_ALL_ORGANIZATION_UNITS BG,
1491 PER_ALL_ASSIGNMENTS_F A
1492 WHERE P.PERSON_ID = A.PERSON_ID AND
1493 O.ORGANIZATION_ID = A.ORGANIZATION_ID AND
1494 O.BUSINESS_GROUP_ID = BG.ORGANIZATION_ID AND
1495 A.PRIMARY_FLAG = 'Y' AND
1496 (TRUNC(SYSDATE) BETWEEN
1497 P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE) AND
1498 (TRUNC(SYSDATE) BETWEEN
1499 A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE);
1500
1501
1502
1503 -- OTA_DEFAULT_EVENT_CENTER
1504 CURSOR lc_get_def_evnt_cntr IS
1505 SELECT ORG.NAME visible_option_value, ORG.ORGANIZATION_ID profile_option_value
1506 FROM HR_ALL_ORGANIZATION_UNITS ORG,HR_ORGANIZATION_INFORMATION ORI,HR_ALL_ORGANIZATION_UNITS BG
1507 WHERE ORG.ORGANIZATION_ID = ORI.ORGANIZATION_ID
1508 AND ORG.BUSINESS_GROUP_ID = BG.ORGANIZATION_ID
1509 AND ORI.ORG_INFORMATION_CONTEXT = 'CLASS'
1510 AND ORI.ORG_INFORMATION1 ='OTA_TC'
1511 AND ORI.ORG_INFORMATION2= 'Y';
1512
1513
1514 -- BEN_USER_TO_PAYROLL_LINK
1515 CURSOR lc_get_usr_payroll_lnk IS
1516 SELECT PPF.PAYROLL_NAME visible_option_value ,PPF.PAYROLL_ID profile_option_value
1517 FROM PAY_PAYROLLS_F PPF
1518 WHERE SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE
1519 AND PPF.EFFECTIVE_END_DATE
1520 AND NVL( PPF.PAYROLL_TYPE, 'PAYROLL' ) <> 'BENEFIT'
1521 ORDER BY PPF.PAYROLL_NAME;
1522
1523
1524 --HR_BIS_REPORTING_HIERARCHY
1525 CURSOR lc_get_bis_rep_hierarchy IS
1526 SELECT OST.NAME visible_option_value,
1527 OST.ORGANIZATION_STRUCTURE_ID profile_option_value
1528 FROM PER_ORGANIZATION_STRUCTURES_V OST,
1529 PER_BUSINESS_GROUPS ORG
1530 WHERE OST.BUSINESS_GROUP_ID = ORG.BUSINESS_GROUP_ID
1531 ORDER BY OST.NAME;
1532
1533
1534
1535 -- HR_MANAGER_ACTIONS_MENU
1536 CURSOR lc_get_mgr_actns_menu IS
1537 SELECT user_menu_name visible_option_value , menu_name profile_option_value
1538 FROM fnd_menus_vl
1539 ORDER BY user_menu_name;
1540
1541
1542 -- HR_PERSONAL_ACTIONS_MENU
1543 CURSOR lc_get_pers_actns_menu IS
1544 SELECT user_menu_name visible_option_value , menu_name profile_option_value
1545 FROM fnd_menus_vl
1546 ORDER BY user_menu_name;
1547
1548
1549 -- BEN_CWB_PREFERRED_CURRENCY
1550 CURSOR lc_get_pref_cur IS
1551 Select name , currency_code profile_option_value
1552 FROM fnd_currencies_vl WHERE enabled_flag='Y';
1553
1554 -- OTA_ILEARNING_DEFAULT_ACTIVITY
1555 CURSOR lc_get_ilearn_def_act IS
1556 SELECT
1557 /*+ INDEX(tad OTA_ACTIVITY_DEFINITIONS_FK1) */
1558 TAD.NAME visible_option_value ,TAD.ACTIVITY_ID profile_option_value
1559 from ota_activity_definitions tad,
1560 hr_all_organization_units org
1561 where tad.business_group_id = org.organization_id
1562 order by org.name,tad.name;
1563
1564
1565 -- OTA_ILEARNING_DEFAULT_ATTENDED
1566 CURSOR lc_get_ilearn_def_atnd IS
1567 SELECT BST.NAME visible_option_value ,BST.BOOKING_STATUS_TYPE_ID profile_option_value
1568 from ota_booking_status_types bst,
1569 hr_all_organization_units org
1570 where bst.business_group_id = org.organization_id
1571 and bst.type in ('A') order by org.name,bst.name;
1572
1573
1574 -- IRC_DEFAULT_COUNTRY
1575 CURSOR lc_get_irc_def_cntry IS
1576 SELECT TERRITORY_SHORT_NAME visible_option_value, TERRITORY_CODE profile_option_value
1577 FROM FND_TERRITORIES_VL
1578 ORDER BY TERRITORY_SHORT_NAME;
1579
1580 -- Bug 2825757 : Added validation for ICX_LANGUAGE
1581
1582 CURSOR lc_get_icx_lang IS
1583 SELECT DESCRIPTION visible_option_value, NLS_LANGUAGE profile_option_value
1584 FROM FND_LANGUAGES_VL WHERE INSTALLED_FLAG IN ('B','I')
1585 ORDER BY DESCRIPTION;
1586
1587 -- Fix 2288014 End
1588
1589 --
1590
1591 l_sql_validation fnd_profile_options.sql_validation%type default null;
1592 l_num_data number default null;
1593 l_varchar2_data varchar2(2000) default null;
1594 l_lookup_type varchar2(2000) default null;
1595 l_opt_value_valid boolean default null;
1596 l_lookup_type_start number default null;
1597 l_lookup_type_end number default null;
1598 l_proc varchar2(30) default 'validate_profile_opt_value';
1599
1600 BEGIN
1601 hr_utility.set_location('Entering:' || l_proc, 10);
1602
1603 -- For those sql validations against FND_COMMON_LOOKUPS, or FND_LOOKUPS
1604 -- the validation is always against the lookup_code. So, we don't need to
1605 -- hard code the validation. But we need to first find out if the validation
1606 -- uses lookup table or not.
1607 --
1608 l_opt_value_valid := null;
1609 l_sql_validation := upper(p_sql_validation);
1610 l_num_data := null;
1611 l_varchar2_data := null;
1612 --
1613 IF instr(l_sql_validation, 'FND_COMMON_LOOKUPS') > 0
1614 OR instr(l_sql_validation, 'FND_LOOKUPS') > 0
1615 OR instr(l_sql_validation, 'HR_LOOKUPS') > 0
1616 OR instr(l_sql_validation, 'FND_LOOKUP_VALUES') > 0
1617 THEN
1618 -- Extract the string starting from the where clause to the end of the
1619 -- statement
1620 -- E.g. "where l.lookup_type = 'HR_USER_TYPE' and ...."
1621 l_lookup_type := substr(l_sql_validation
1622 ,instr(l_sql_validation,'WHERE'));
1623
1624 -- Now extract the string starting from the word lookup_type.
1625 -- E.g. "lookup_type = 'HR_USER_TYPE' and ...."
1626 l_lookup_type := substr(l_lookup_type
1627 ,instr(l_lookup_type, 'LOOKUP_TYPE'));
1628
1629 -- Now extract the lookup type.
1630 l_lookup_type_start := instr(l_lookup_type, '''', 1) + 1;
1631 l_lookup_type_end := instr(l_lookup_type, '''', 1, 2);
1632
1633 l_lookup_type := substr(l_lookup_type
1634 ,l_lookup_type_start
1635 ,l_lookup_type_end - l_lookup_type_start);
1636
1637
1638
1639 -- Now we can call the cursor
1640 IF instr(l_sql_validation, 'FND_COMMON_LOOKUPS') > 0
1641 THEN
1642 FOR get_lkup_code IN lc_get_fnd_cmn_lkups(p_lookup_type =>l_lookup_type)
1643 LOOP
1644 IF get_lkup_code.lookup_code = p_profile_opt_value
1645 THEN
1646 l_opt_value_valid := true;
1647 END IF;
1648 END LOOP;
1649 --
1650 ELSIF instr(l_sql_validation, 'FND_LOOKUPS') > 0
1651 THEN
1652 FOR get_lkup_code IN lc_get_fnd_lkups(p_lookup_type =>l_lookup_type)
1653 LOOP
1654 IF get_lkup_code.lookup_code = p_profile_opt_value
1655 THEN
1656 l_opt_value_valid := true;
1657 END IF;
1658 END LOOP;
1659 --
1660 ELSIF instr(l_sql_validation, 'FND_LOOKUP_VALUES') > 0
1661 THEN
1662 FOR get_lkup_code IN lc_get_fnd_lkup_val (p_lookup_type =>l_lookup_type)
1663 LOOP
1664 IF get_lkup_code.lookup_code = p_profile_opt_value
1665 THEN
1666 l_opt_value_valid := true;
1667 END IF;
1668 END LOOP;
1669 --
1670 ELSIF instr(l_sql_validation, 'HR_LOOKUPS') > 0
1671 THEN
1672 FOR get_lkup_code IN lc_get_hr_lkups(p_lookup_type =>l_lookup_type)
1673 LOOP
1674 IF get_lkup_code.lookup_code = p_profile_opt_value
1675 THEN
1676 l_opt_value_valid := true;
1677 END IF;
1678 END LOOP;
1679 END IF;
1680 --
1681 ELSE
1682 null;
1683 END IF; -- end if statment for checking lookups
1684
1685 --
1686 --
1687 IF upper(p_profile_opt_name) = 'PER_SECURITY_PROFILE_ID'
1688 THEN
1689 FOR get_sec_profile in lc_get_security_profile
1690 LOOP
1691 IF get_sec_profile.security_profile_id = to_number(p_profile_opt_value)
1692 THEN
1693 l_opt_value_valid := true;
1694 l_num_data := get_sec_profile.business_group_id;
1695
1696
1697 END IF;
1698 END LOOP;
1699 ELSIF upper(p_profile_opt_name) = 'HR_PAYROLL_CURRENCY_RATES'
1700 THEN
1701 FOR get_curr_rates in lc_get_pay_curr_rates
1702 LOOP
1703 IF get_curr_rates.payroll_id = to_number(p_profile_opt_value)
1704 THEN
1705 l_opt_value_valid := true;
1706 END IF;
1707 END LOOP;
1708 ELSIF upper(p_profile_opt_name) = 'OTA_AUTO_WAITLIST_BOOKING_STATUS'
1709 THEN
1710 FOR get_booking_status in lc_get_bkg_status
1711 LOOP
1712 IF get_booking_status.booking_status_type_id =
1713 to_number(p_profile_opt_value)
1714 THEN
1715 l_opt_value_valid := true;
1716 END IF;
1717 END LOOP;
1718 ELSIF upper(p_profile_opt_name) = 'PER_BUSINESS_GROUP_ID'
1719 THEN
1720 For get_bg_id in lc_get_bg_id
1721 LOOP
1722 IF get_bg_id.business_group_id = to_number(p_profile_opt_value)
1723 THEN
1724 l_opt_value_valid := true;
1725 END IF;
1726 END LOOP;
1727 ELSIF upper(p_profile_opt_name) = 'PER_DEFAULT_CORRESPONDENCE_LANGUAGE'
1728 THEN
1729 For get_language_code in lc_get_def_lang
1730 LOOP
1731 IF get_language_code.language_code = p_profile_opt_value
1732 THEN
1733 l_opt_value_valid := true;
1734 END IF;
1735 END LOOP;
1736 -- 2288014 start.
1737 ELSIF upper(p_profile_opt_name) = 'OM_DEFAULT_ENROLLMENT_CANCELLED_STATUS' THEN
1738 FOR get_enroll_cancel_sts in lc_get_enroll_cancel_sts
1739 LOOP
1740 IF get_enroll_cancel_sts.profile_option_value = p_profile_opt_value
1741 THEN
1742 l_opt_value_valid := true;
1743 END IF;
1744 END LOOP;
1745 ELSIF upper(p_profile_opt_name) = 'BEN_NEW_USER_RESP_PROFILE' THEN
1746 FOR get_new_usr_resp_profl IN lc_get_new_usr_resp_profl
1747 LOOP
1748 IF get_new_usr_resp_profl.profile_option_value = p_profile_opt_value
1749 THEN
1750 l_opt_value_valid := true;
1751 END IF;
1752 END LOOP;
1753 ELSIF upper(p_profile_opt_name) = 'BEN_USER_TO_ORG_LINK' THEN
1754 FOR get_usr_org_lnk IN lc_get_usr_org_lnk
1755 LOOP
1756 IF get_usr_org_lnk.profile_option_value = p_profile_opt_value
1757 THEN
1758 l_opt_value_valid := true;
1759 END IF;
1760 END LOOP;
1761
1762
1763 ELSIF upper(p_profile_opt_name) = 'OTA_HR_GLOBAL_BUSINESS_GROUP_ID' THEN
1764 FOR get_glbl_bd_id IN lc_get_glbl_bd_id
1765 LOOP
1766 IF get_glbl_bd_id.profile_option_value = p_profile_opt_value
1767 THEN
1768 l_opt_value_valid := true;
1769 END IF;
1770 END LOOP;
1771 ELSIF upper(p_profile_opt_name) = 'OTA_DEFAULT_EVENT_OWNER' THEN
1772 FOR get_def_envt_ownr IN lc_get_def_envt_ownr
1773 LOOP
1774 IF get_def_envt_ownr.profile_option_value = p_profile_opt_value
1775 THEN
1776 l_opt_value_valid := true;
1777 END IF;
1778 END LOOP;
1779 ELSIF upper(p_profile_opt_name) = 'OTA_DEFAULT_EVENT_CENTER' THEN
1780 FOR get_def_evnt_cntr IN lc_get_def_evnt_cntr
1781 LOOP
1782 IF get_def_evnt_cntr.profile_option_value = p_profile_opt_value
1783 THEN
1784 l_opt_value_valid := true;
1785 END IF;
1786 END LOOP;
1787 ELSIF upper(p_profile_opt_name) = 'BEN_USER_TO_PAYROLL_LINK' THEN
1788 FOR get_usr_payroll_lnk IN lc_get_usr_payroll_lnk
1789 LOOP
1790 IF get_usr_payroll_lnk.profile_option_value = p_profile_opt_value
1791 THEN
1792 l_opt_value_valid := true;
1793 END IF;
1794 END LOOP;
1795
1796 ELSIF upper(p_profile_opt_name) = 'HR_BIS_REPORTING_HIERARCHY' THEN
1797 FOR get_bis_rep_hierarchy IN lc_get_bis_rep_hierarchy
1798 LOOP
1799 IF get_bis_rep_hierarchy.profile_option_value = p_profile_opt_value
1800 THEN
1801 l_opt_value_valid := true;
1802 END IF;
1803 END LOOP;
1804 ELSIF upper(p_profile_opt_name) = 'HR_MANAGER_ACTIONS_MENU' THEN
1805 FOR get_mgr_actns_menu IN lc_get_mgr_actns_menu
1806 LOOP
1807 IF get_mgr_actns_menu.profile_option_value = p_profile_opt_value
1808 THEN
1809 l_opt_value_valid := true;
1810 END IF;
1811 END LOOP;
1812 ELSIF upper(p_profile_opt_name) = 'HR_PERSONAL_ACTIONS_MENU' THEN
1813 FOR get_pers_actns_menu IN lc_get_pers_actns_menu
1814 LOOP
1815 IF get_pers_actns_menu.profile_option_value = p_profile_opt_value
1816 THEN
1817 l_opt_value_valid := true;
1818 END IF;
1819 END LOOP;
1820 ELSIF upper(p_profile_opt_name) = 'BEN_CWB_PREFERRED_CURRENCY' THEN
1821 FOR get_pref_cur IN lc_get_pref_cur
1822 LOOP
1823 IF get_pref_cur.profile_option_value = p_profile_opt_value
1824 THEN
1825 l_opt_value_valid := true;
1826 END IF;
1827 END LOOP;
1828 ELSIF upper(p_profile_opt_name) = 'OTA_ILEARNING_DEFAULT_ACTIVITY' THEN
1829 FOR get_ilearn_def_act IN lc_get_ilearn_def_act
1830 LOOP
1831 IF get_ilearn_def_act.profile_option_value = p_profile_opt_value
1832 THEN
1833 l_opt_value_valid := true;
1834 END IF;
1835 END LOOP;
1836 ELSIF upper(p_profile_opt_name) = 'OTA_ILEARNING_DEFAULT_ATTENDED' THEN
1837 FOR get_ilearn_def_atnd IN lc_get_ilearn_def_atnd
1838 LOOP
1839 IF get_ilearn_def_atnd.profile_option_value = p_profile_opt_value
1840 THEN
1841 l_opt_value_valid := true;
1842 END IF;
1843 END LOOP;
1844 ELSIF upper(p_profile_opt_name) = 'IRC_DEFAULT_COUNTRY' THEN
1845 FOR get_irc_def_cntry IN lc_get_irc_def_cntry
1846 LOOP
1847 IF get_irc_def_cntry.profile_option_value = p_profile_opt_value
1848 THEN
1849 l_opt_value_valid := true;
1850 END IF;
1851
1852 END LOOP;
1853 -- Fix 2288014 End.
1854 ELSIF upper(p_profile_opt_name) = 'ICX_LANGUAGE' THEN
1855 FOR get_icx_lang IN lc_get_icx_lang
1856 LOOP
1857 IF get_icx_lang.profile_option_value = p_profile_opt_value
1858 THEN
1859 l_opt_value_valid := true;
1860 END IF;
1861 END LOOP; -- Bug 2825757 : Added validation for ICX_LANGUAGE
1862 --
1863 ELSE
1864 null;
1865 END IF;
1866
1867 --
1868
1869 IF l_opt_value_valid
1870 THEN
1871 p_profile_opt_value_valid := l_opt_value_valid;
1872 hr_utility.set_location('profile opt value is valid', 19);
1873 ELSE
1874 p_profile_opt_value_valid := false;
1875 hr_utility.set_location('profile opt value is invalid', 19);
1876 END IF;
1877
1878 p_num_data := l_num_data;
1879 p_varchar2_data := l_varchar2_data;
1880
1881 hr_utility.set_location('Leaving:' || l_proc, 50);
1882
1883 EXCEPTION
1884 WHEN OTHERS THEN
1885 IF upper(p_profile_level_name) = 'RESP'
1886 THEN
1887 fnd_message.set_name('PER', 'HR_PROFILE_VAL_NOT_ADDED');
1888 fnd_message.set_token('RESP_ID', p_profile_level_value);
1889 ELSIF upper(p_profile_level_name) = 'USER'
1890 THEN
1891 fnd_message.set_name('PER', 'HR_PROFILE_USER_VAL_NOT_ADDED');
1892 END IF;
1893
1894 fnd_message.set_token('PROFILE_OPTION_NAME', p_profile_opt_name);
1895 fnd_message.set_token('PROFILE_OPTION_VALUE', p_profile_opt_value);
1896 hr_utility.raise_error;
1897
1898
1899 END validate_profile_opt_value;
1900 --
1901 -- ----------------------------------------------------------------------------
1902 -- |----------------------- < build_resp_profile_val > -----------------------|
1903 -- ----------------------------------------------------------------------------
1904 PROCEDURE build_resp_profile_val
1905 (p_template_resp_id in fnd_responsibility.responsibility_id%type
1906 default null
1907 ,p_template_resp_app_id in fnd_responsibility.application_id%type
1908 default null
1909 ,p_new_resp_key in fnd_responsibility.responsibility_key%type
1910 ,p_new_resp_app_id in fnd_responsibility.application_id%type
1911 ,p_fnd_profile_opt_val_tbl in
1912 hr_user_acct_utility.fnd_profile_opt_val_tbl
1913 ,p_out_profile_opt_val_tbl out
1914 hr_user_acct_utility.fnd_profile_opt_val_tbl
1915 ) IS
1916 --
1917 --
1918 -- Derive profile option values from the template responsibility id
1919 CURSOR lc_get_resp_lvl_profile_val IS
1920 SELECT fpv.profile_option_id
1921 ,fpv.profile_option_value
1922 ,fp.profile_option_name
1923 FROM fnd_profile_options fp
1924 ,fnd_profile_option_values fpv
1925 WHERE fpv.profile_option_id = fp.profile_option_id
1926 AND fpv.level_id = 10003
1927 AND fpv.level_value = p_template_resp_id
1928 AND fpv.level_value_application_id = p_template_resp_app_id;
1929
1930
1931 l_prof_opt_val_tbl hr_user_acct_utility.fnd_profile_opt_val_tbl;
1932 l_found_sw boolean default false;
1933 l_index binary_integer := 0;
1934 l_count number default 0;
1935 l_proc varchar2(72) := g_package||'build_resp_profile_val';
1936 --
1937 BEGIN
1938 --
1939 hr_utility.set_location('Entering:' || l_proc, 10);
1940 --
1941 -- Now copy the p_fnd_profile_opt_val_tbl to the local table for output
1942 -- Only copy the profile option values for the passed in responsibility.
1943 FOR i in 1..p_fnd_profile_opt_val_tbl.count
1944 LOOP
1945 IF p_fnd_profile_opt_val_tbl(i).profile_level_name = 'RESP'
1946 AND
1947 p_fnd_profile_opt_val_tbl(i).profile_level_value = p_new_resp_key
1948 AND
1949 p_fnd_profile_opt_val_tbl(i).profile_level_value_app_id =
1950 p_new_resp_app_id
1951 THEN
1952 l_index := l_index + 1;
1953 l_prof_opt_val_tbl(l_index).profile_option_name :=
1954 p_fnd_profile_opt_val_tbl(i).profile_option_name;
1955 l_prof_opt_val_tbl(l_index).profile_option_value :=
1956 p_fnd_profile_opt_val_tbl(i).profile_option_value;
1957 l_prof_opt_val_tbl(l_index).profile_level_name :=
1958 p_fnd_profile_opt_val_tbl(i).profile_level_name;
1959 l_prof_opt_val_tbl(l_index).profile_level_value :=
1960 p_fnd_profile_opt_val_tbl(i).profile_level_value;
1961 l_prof_opt_val_tbl(l_index).profile_level_value_app_id :=
1962 p_fnd_profile_opt_val_tbl(i).profile_level_value_app_id;
1963 END IF;
1964 END LOOP;
1965
1966
1967 IF p_template_resp_id IS NOT NULL AND p_template_resp_app_id IS NOT NULL
1968 THEN
1969 -- get the profile opt values for the passed in template resp id
1970 -- and only copy into the l_prof_opt_val_tbl if it does not exist already
1971 FOR get_prof_values in lc_get_resp_lvl_profile_val
1972 LOOP
1973 l_found_sw := false;
1974 l_count := l_prof_opt_val_tbl.count;
1975 FOR i in 1..l_count
1976 LOOP
1977 IF l_prof_opt_val_tbl(i).profile_option_name =
1978 get_prof_values.profile_option_name
1979 AND
1980 l_prof_opt_val_tbl(i).profile_level_name = 'RESP'
1981 THEN
1982 l_found_sw := true;
1983 END IF;
1984 END LOOP;
1985 --
1986 IF l_found_sw
1987 THEN
1988 -- there is already an overwrite, don't move to the new table
1989 null;
1990 ELSE
1991 -- Add this profile opt value to the resp level
1992 l_prof_opt_val_tbl(l_count + 1).profile_option_name :=
1993 get_prof_values.profile_option_name ;
1994 l_prof_opt_val_tbl(l_count + 1).profile_option_value :=
1995 get_prof_values.profile_option_value;
1996 l_prof_opt_val_tbl(l_count + 1).profile_level_name := 'RESP';
1997 l_prof_opt_val_tbl(l_count + 1).profile_level_value :=
1998 p_new_resp_key;
1999 l_prof_opt_val_tbl(l_count + 1).profile_level_value_app_id :=
2000 p_new_resp_app_id;
2001 END IF;
2002 END LOOP;
2003 --
2004 END IF;
2005
2006 p_out_profile_opt_val_tbl := l_prof_opt_val_tbl;
2007
2008 hr_utility.set_location('Leaving:' || l_proc, 50);
2009 --
2010 EXCEPTION
2011 WHEN others THEN
2012 hr_utility.set_message(800, 'HR_BUILD_PROFILE_VAL_ERR');
2013 hr_utility.raise_error;
2014
2015 END build_resp_profile_val;
2016 --
2017 -- ----------------------------------------------------------------------------
2018 -- |-------------------- < build_func_sec_exclusion_rules > -------------------|
2019 -- ----------------------------------------------------------------------------
2020 PROCEDURE build_func_sec_exclusion_rules
2021 (p_func_sec_excl_tbl in hr_user_acct_utility.fnd_resp_functions_tbl
2022 ,p_out_func_sec_excl_tbl out nocopy hr_user_acct_utility.func_sec_excl_tbl)
2023 IS
2024 --
2025 CURSOR lc_get_resp_id (p_resp_key in
2026 fnd_responsibility.responsibility_key%TYPE)
2027 IS
2028 SELECT application_id, responsibility_id
2029 FROM fnd_responsibility
2030 WHERE responsibility_key = p_resp_key;
2031 --
2032 CURSOR lc_get_resp_func (p_resp_id in
2033 fnd_responsibility.responsibility_id%TYPE
2034 ,p_app_id in
2035 fnd_responsibility.application_id%TYPE)
2036 IS
2037 SELECT action_id, rule_type
2038 FROM fnd_resp_functions
2039 WHERE application_id = p_app_id
2040 AND responsibility_id = p_resp_id;
2041 --
2042
2043 CURSOR lc_get_function_name (p_func_id in fnd_form_functions.function_id%TYPE)
2044 IS
2045 SELECT function_name
2046 FROM fnd_form_functions
2047 WHERE function_id = p_func_id;
2048 --
2049 CURSOR lc_get_menu_name (p_menu_id in fnd_menus.menu_id%TYPE)
2050 IS
2051 SELECT menu_name
2052 FROM fnd_menus
2053 WHERE menu_id = p_menu_id;
2054 --
2055 --
2056 l_proc varchar2(72) := g_package ||
2057 'build_func_sec_exclusion_rules';
2058 l_resp_rec lc_get_resp_id%rowtype;
2059 l_resp_func_rec lc_get_resp_func%rowtype;
2060 l_index binary_integer default 0;
2061 l_func_sec_excl_tbl_count integer default 0;
2062 l_out_func_sec_excl_tbl hr_user_acct_utility.func_sec_excl_tbl;
2063 l_rule_name fnd_menus.menu_name%type;
2064 l_func_sec_excl_err exception;
2065 --
2066 BEGIN
2067 hr_utility.set_location('Entering:'|| l_proc, 11);
2068
2069 l_func_sec_excl_tbl_count := p_func_sec_excl_tbl.count;
2070 --
2071 FOR i in 1..l_func_sec_excl_tbl_count
2072 LOOP
2073 IF p_func_sec_excl_tbl(i).existing_resp_key IS NOT NULL AND
2074 p_func_sec_excl_tbl(i).new_resp_key IS NOT NULL
2075 THEN
2076 -- read the existing responsibility
2077 -- First check that the application_id and responsibility_id have
2078 -- already been read in the previous entry of the table.
2079 IF i > 1 AND
2080 (p_func_sec_excl_tbl(i).existing_resp_key =
2081 p_func_sec_excl_tbl(i - 1).existing_resp_key AND
2082 p_func_sec_excl_tbl(i).new_resp_key =
2083 p_func_sec_excl_tbl(i - 1).new_resp_key)
2084 THEN
2085 -- no need to read the responsibility record because it's already
2086 -- been read in the previous entry.
2087 null;
2088 ELSE
2089 FOR get_resp_rec in lc_get_resp_id
2090 (p_resp_key => p_func_sec_excl_tbl(i).existing_resp_key)
2091 LOOP
2092 l_resp_rec.application_id := get_resp_rec.application_id;
2093 l_resp_rec.responsibility_id := get_resp_rec.responsibility_id;
2094 END LOOP;
2095 END IF;
2096 --
2097 -- load the output table with the template responsibility's function
2098 -- security exclusion rules.
2099 FOR get_resp_func_rec in lc_get_resp_func
2100 (p_resp_id => l_resp_rec.responsibility_id
2101 ,p_app_id => l_resp_rec.application_id)
2102 LOOP
2103 l_resp_func_rec.action_id := get_resp_func_rec.action_id;
2104 l_resp_func_rec.rule_type := get_resp_func_rec.rule_type;
2105 --
2106 IF l_resp_func_rec.rule_type = 'F'
2107 THEN
2108 -- derive the function_name from fnd_form_functions
2109 OPEN lc_get_function_name
2110 (p_func_id => l_resp_func_rec.action_id);
2111 FETCH lc_get_function_name into l_rule_name;
2112 IF lc_get_function_name%NOTFOUND
2113 THEN
2114 -- raise an error
2115 CLOSE lc_get_function_name;
2116 raise l_func_sec_excl_err;
2117 ELSE
2118 CLOSE lc_get_function_name;
2119 END IF;
2120 ELSE
2121 -- derive the menu_name from fnd_menus
2122 OPEN lc_get_menu_name
2123 (p_menu_id => l_resp_func_rec.action_id);
2124 FETCH lc_get_menu_name into l_rule_name;
2125 IF lc_get_menu_name%NOTFOUND
2126 THEN
2127 -- raise an error
2128 CLOSE lc_get_menu_name;
2129 raise l_func_sec_excl_err;
2130 ELSE
2131 CLOSE lc_get_menu_name;
2132 END IF;
2133 END IF;
2134 --
2135 l_index := l_index + 1;
2136 l_out_func_sec_excl_tbl(l_index).resp_key :=
2137 p_func_sec_excl_tbl(i).new_resp_key;
2138 l_out_func_sec_excl_tbl(l_index).rule_type :=
2139 l_resp_func_rec.rule_type;
2140 l_out_func_sec_excl_tbl(l_index).rule_name := l_rule_name;
2141 l_out_func_sec_excl_tbl(l_index).delete_flag := 'N';
2142 END LOOP; -- end loop of lc_get_resp_func
2143 --
2144 ELSIF p_func_sec_excl_tbl(i).new_resp_key IS NOT NULL
2145 THEN
2146 -- it's a new func security exclusion rule, use as is
2147 l_index := l_index + 1;
2148 l_out_func_sec_excl_tbl(l_index).resp_key :=
2149 p_func_sec_excl_tbl(i).new_resp_key;
2150 l_out_func_sec_excl_tbl(l_index).rule_type :=
2151 p_func_sec_excl_tbl(i).rule_type;
2152 l_out_func_sec_excl_tbl(l_index).rule_name :=
2153 p_func_sec_excl_tbl(i).rule_name;
2154 l_out_func_sec_excl_tbl(l_index).delete_flag := 'N';
2155 ELSE
2156 null;
2157 END IF;
2158 --
2159 END LOOP;
2160 --
2161 p_out_func_sec_excl_tbl := l_out_func_sec_excl_tbl;
2162 --
2163 hr_utility.set_location('Leaving:'|| l_proc, 15);
2164 --
2165 --
2166 EXCEPTION
2167 WHEN l_func_sec_excl_err THEN
2168 hr_utility.set_message(800, 'HR_BUILD_FUNC_EXCL_RULE_ERR');
2169 hr_utility.raise_error;
2170 --
2171 --
2172 WHEN others THEN
2173 hr_utility.set_message(800, 'HR_BUILD_FUNC_EXCL_RULE_ERR');
2174 hr_utility.raise_error;
2175 --
2176 END build_func_sec_exclusion_rules;
2177 --
2178 --
2179 -- ----------------------------------------------------------------------------
2180 -- |--------------------- < create_fnd_resp_functions > -----------------------|
2181 -- |NOTE: No savepoint will be issued here because business support internal |
2182 -- | process is not supposed to issue any savepoint or rollback. |
2183 -- ----------------------------------------------------------------------------
2184 --
2185 PROCEDURE create_fnd_resp_functions
2186 (p_resp_key in fnd_responsibility.responsibility_key%type
2187 ,p_rule_type in fnd_resp_functions.rule_type%type
2188 ,p_rule_name in varchar2
2189 ,p_delete_flag in varchar2 default 'N')
2190 IS
2191 --
2192 CURSOR lc_get_resp_id
2193 IS
2194 SELECT responsibility_id
2195 FROM fnd_responsibility
2196 WHERE responsibility_key = p_resp_key;
2197 --
2198 CURSOR lc_get_function_id
2199 IS
2200 SELECT function_id
2201 FROM fnd_form_functions
2202 WHERE function_name = p_rule_name;
2203 --
2204 CURSOR lc_get_menu_id
2205 IS
2206 SELECT menu_id
2207 FROM fnd_menus
2208 WHERE menu_name = p_rule_name;
2209 --
2210 l_proc varchar2(72) := g_package ||
2211 'create_fnd_resp_functions';
2212
2213 l_temp_id number default null;
2214 --
2215 --
2216 BEGIN
2217 hr_utility.set_location('Entering:'|| l_proc, 10);
2218 --
2219 -- FND API does not validate or throw an exception. So, do validations here
2220 -- instead of build_func_sec_exclusion_rules because users might call this
2221 -- procedure directly and thus bypass the build_func_sec_exclusion_rules
2222 -- procedure.
2223 --
2224 -- Validate that p_resp_key exists
2225 OPEN lc_get_resp_id;
2226 FETCH lc_get_resp_id into l_temp_id;
2227 IF lc_get_resp_id%NOTFOUND
2228 THEN
2229 CLOSE lc_get_resp_id;
2230 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
2231 fnd_message.set_token('TABLE', 'FND_RESPONSIBILITY');
2232 fnd_message.set_token('COLUMN', 'RESPONSIBILITY_KEY');
2233 fnd_message.set_token('VALUE', p_resp_key);
2234
2235 hr_utility.set_message(800, 'HR_INVALID_RESP_KEY');
2236 hr_utility.raise_error;
2237 ELSE
2238 CLOSE lc_get_resp_id;
2239 END IF;
2240 --
2241 -- Validate that rule_type can have the values of 'F' or 'M' only.
2242 IF p_rule_type = 'F' OR p_rule_type = 'M'
2243 THEN
2244 NULL;
2245 ELSE
2246 hr_utility.set_message(800, 'HR_INVALID_SEC_EXCL_RULE_TYPE');
2247 hr_utility.raise_error;
2248 END IF;
2249 --
2250 -- Validate rule_name
2251 IF p_rule_type = 'F'
2252 THEN
2253 OPEN lc_get_function_id;
2254 FETCH lc_get_function_id into l_temp_id;
2255 IF lc_get_function_id%NOTFOUND
2256 THEN
2257 CLOSE lc_get_function_id;
2258 hr_utility.set_message(800, 'HR_INVALID_SEC_EXCL_FUNC_NAME');
2259 hr_utility.raise_error;
2260 ELSE
2261 CLOSE lc_get_function_id;
2262 END IF;
2263 ELSIF p_rule_type = 'M'
2264 THEN
2265 OPEN lc_get_menu_id;
2266 FETCH lc_get_menu_id into l_temp_id;
2267 IF lc_get_menu_id%NOTFOUND
2268 THEN
2269 CLOSE lc_get_menu_id;
2270 hr_utility.set_message(800, 'HR_INVALID_SEC_EXCL_MENU_NAME');
2271 hr_utility.raise_error;
2272 ELSE
2273 CLOSE lc_get_menu_id;
2274 END IF;
2275 ELSE
2276 hr_utility.set_message(800, 'HR_INVALID_SEC_EXCL_RULE_NAME');
2277 hr_utility.raise_error;
2278 END IF;
2279 --
2280 fnd_function_security.security_rule
2281 (responsibility_key => p_resp_key
2282 ,rule_type => p_rule_type
2283 ,rule_name => p_rule_name
2284 ,delete_flag => p_delete_flag);
2285 --
2286 --
2287 hr_utility.set_location('Leaving:'|| l_proc, 50);
2288 --
2289 --
2290 END create_fnd_resp_functions;
2291 --
2292 -- ----------------------------------------------------------------------------
2293 -- |-------------------------- < update_fnd_user > ----------------------------|
2294 -- |NOTE: No savepoint will be issued here because business support internal |
2295 -- | process is not supposed to issue any savepoint or rollback. |
2296 -- ----------------------------------------------------------------------------
2297 --
2298 PROCEDURE update_fnd_user
2299 (p_user_id in number
2300 ,p_old_password in varchar2 default hr_api.g_varchar2
2301 ,p_new_password in varchar2 default hr_api.g_varchar2
2302 ,p_end_date in date default hr_api.g_date
2303 ,p_email_address in varchar2 default hr_api.g_varchar2
2304 ,p_fax in varchar2 default hr_api.g_varchar2
2305 ,p_known_as in varchar2 default hr_api.g_varchar2
2306 ,p_language in varchar2 default hr_api.g_varchar2
2307 ,p_host_port in varchar2 default hr_api.g_varchar2
2308 ,p_employee_id in number default hr_api.g_number
2309 ,p_customer_id in number default hr_api.g_number
2310 ,p_supplier_id in number default hr_api.g_number
2311 ) IS
2312
2313 --
2314 CURSOR lc_get_user_data
2315 IS
2316 SELECT *
2317 FROM fnd_user
2318 WHERE user_id = p_user_Id;
2319 --
2320 l_proc varchar2(72) := g_package||'update_fnd_user';
2321 l_user_data fnd_user%rowtype;
2322 l_old_password fnd_user.encrypted_user_password%type
2323 default null;
2324 -- The new un-encrypted password cannot exceed a length of 30, which
2325 -- is what is allowed in forms.
2326 l_new_password varchar2(30) default null;
2327 l_end_date date default null;
2328 l_host_port varchar2(32000) default null;
2329 l_pos number default 0;
2330 l_count number default 0;
2331 l_email_address fnd_user.email_address%type default null;
2332 l_fax fnd_user.fax%type default null;
2333 l_description fnd_user.description%type default null;
2334 l_language varchar2(32000) default null;
2335 l_employee_id number default null ; -- Fix 2951145
2336 l_customer_id number default null ; -- Fix 2951145
2337 l_supplier_id number default null ; -- Fix 2951145
2338 l_return_status varchar2(32000) default null;
2339 l_msg_count number default 0;
2340 l_msg_data varchar2(32000) default null;
2341 l_last_updated_by number default null;
2342 l_last_update_login number default null;
2343 l_app_short_name varchar2(200) default null;
2344 l_msg_name fnd_new_messages.message_name%type default null;
2345 --
2346 l_user_name VARCHAR2(80); -- Fix 2288014
2347 --
2348
2349 BEGIN
2350 hr_utility.set_location('Entering:'|| l_proc, 10);
2351 --
2352 -- Validate input parameters first, need to convert hr_api default
2353 -- value to fnd's api default value.
2354 IF p_old_password = hr_api.g_varchar2
2355 THEN
2356 l_old_password := null ; -- Fix 2951145
2357 ELSE
2358 l_old_password := p_old_password;
2359 END IF;
2360 --
2361 OPEN lc_get_user_data;
2362 FETCH lc_get_user_data INTO l_user_data;
2363 IF lc_get_user_data%NOTFOUND
2364 THEN
2365
2366 CLOSE lc_get_user_data;
2367 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
2368 fnd_message.set_token('TABLE', 'FND_USER');
2369 fnd_message.set_token('COLUMN', 'USER_ID');
2370 fnd_message.set_token('VALUE', p_user_id);
2371 hr_utility.raise_error;
2372 ELSE
2373 CLOSE lc_get_user_data;
2374 END IF;
2375 --
2376 -- Validate p_user_end_date. If entered, it must be larger than start_date
2377 IF p_end_date = hr_api.g_date
2378 THEN
2379 l_end_date := null ; -- Fix 2951145
2380 ELSE
2381 IF nvl(p_end_date, hr_api.g_eot) >= l_user_data.start_date
2382 THEN
2383 l_end_date := p_end_date;
2384 ELSE
2385 hr_utility.set_message(800, 'HR_51070_CAU_START_END');
2386 hr_utility.raise_error;
2387 END IF;
2388 END IF;
2389 --
2390 -- Validate host_port
2391 IF p_host_port = hr_api.g_varchar2
2392 THEN
2393 l_host_port := null ; -- Fix 2951145
2394 ELSE
2395 l_host_port := p_host_port;
2396 END IF;
2397 --
2398 -- Validate email address length
2399 IF p_email_address = hr_api.g_varchar2
2400 THEN
2401 l_email_address := null ; -- Fix 2951145
2402 ELSE
2403 IF length(p_email_address) > g_max_email_address_length
2404 THEN
2405 hr_utility.set_message(800, 'HR_INVALID_EMAIL_ADDR_LENGTH');
2406 hr_utility.raise_error;
2407 ELSE
2408 l_email_address := p_email_address;
2409 END IF;
2410 END IF;
2411 --
2412 -- Validate fax length
2413 IF p_fax = hr_api.g_varchar2
2414 THEN
2415 l_fax := null ; -- Fix 2951145
2416 ELSE
2417 IF length(p_fax) > g_max_fax_length
2418 THEN
2419 hr_utility.set_message(800, 'HR_INVALID_FAX_LENGTH');
2420 hr_utility.raise_error;
2421 ELSE
2422 l_fax := p_fax;
2423 END IF;
2424 END IF;
2425 --
2426 IF p_language = hr_api.g_varchar2
2427 THEN
2428 l_language := null ; -- Fix 2951145
2429 ELSE
2430 l_language := p_language;
2431 END IF;
2432 --
2433 IF p_employee_id = hr_api.g_number
2434 THEN
2435 l_employee_id := null ; -- Fix 2951145
2436 ELSE
2437 -- Check for employee id if it exists
2438 SELECT count(1)
2439 INTO l_count
2440 FROM per_all_people_f
2441 WHERE person_id = p_employee_id;
2442
2443 IF l_count <= 0
2444 THEN
2445 hr_utility.set_message(800, 'HR_INVALLID_EMP_ID');
2446 hr_utility.raise_error;
2447 ELSE
2448 l_employee_id := p_employee_id;
2449 END IF;
2450 END IF;
2451 --
2452 -- We won't do validation of Customer Id and Supplier Id because we don't
2453 -- know what we are supposed to validate.
2454
2455 -- The following is mimicking FNDSCAUS.fmb program unit fnd_encrypt_pwd.
2456 -- Check password length. The minimum password length can be set via a
2457 -- profile option. If that profile option is null, the default is 5.
2458 --
2459 l_count := 0;
2460 l_count := to_number(
2461 nvl(fnd_profile.value('SIGNON_PASSWORD_LENGTH'), '5')
2462 );
2463
2464 IF p_new_password = hr_api.g_varchar2
2465 THEN
2466 l_new_password := null ; -- Fix 2951145
2467 ELSIF (length(p_new_password) < l_count)
2468 THEN
2469 fnd_message.set_name('FND', 'PASSWORD-LONGER');
2470 fnd_message.set_token('LENGTH', to_char(l_count));
2471 hr_utility.raise_error;
2472 ELSIF (length(p_new_password) > 30)
2473 THEN
2474 hr_utility.set_message(800, 'HR_USER_PASSWORD_TOO_LONG');
2475 hr_utility.raise_error;
2476 ELSE
2477 l_new_password := p_new_password;
2478 END IF;
2479 --
2480 --
2481 -----------------------------------------------------------------------------
2482 -- Set fnd last_updated_by and last_update_login columns
2483 -----------------------------------------------------------------------------
2484 l_last_updated_by := fnd_global.user_id;
2485 IF l_last_updated_by IS NULL
2486 THEN
2487 l_last_updated_by := -1;
2488 END IF;
2489 --
2490 l_last_update_login := fnd_global.login_id;
2491 IF l_last_update_login IS NULL
2492 THEN
2493 l_last_update_login := -1;
2494 END IF;
2495 --
2496 -- Now, we're ready to call fnd api to update a user account.
2497 --
2498 hr_utility.set_location (l_proc || ' before fnd_user_pkg.UpdateUser', 30);
2499 --
2500 -- Fix 2288014 Start
2501
2502 select user_name into l_user_name from fnd_user
2503 where user_id = p_user_id;
2504
2505
2506 fnd_user_pkg.UpdateUser (
2507 x_user_name => l_user_name,
2508 x_owner => '',
2509 x_unencrypted_password =>l_new_password,
2510 x_description => l_description,
2511 -- x_last_logon_date => sysdate, -- for BUG 7116804
2512 x_end_date => l_end_date,
2513 x_employee_id => l_employee_id,
2514 x_email_address => l_email_address,
2515 x_fax => l_fax,
2516 x_customer_id => l_customer_id,
2517 x_supplier_id => l_supplier_id
2518 );
2519
2520 --
2521 hr_utility.set_location('Leaving:' || l_proc, 70);
2522 --
2523
2524 EXCEPTION
2525 WHEN OTHERS THEN
2526 hr_utility.raise_error;
2527
2528 -- Fix 2288014 End
2529
2530 END update_fnd_user;
2531 --
2532 -- ----------------------------------------------------------------------------
2533 -- |-------------------- < update_fnd_user_resp_groups > ----------------------|
2534 -- |NOTE: No savepoint will be issued here because business support internal |
2535 -- | process is not supposed to issue any savepoint or rollback. |
2536 -- ----------------------------------------------------------------------------
2537 --
2538 PROCEDURE update_fnd_user_resp_groups
2539 (p_user_id in number
2540 ,p_responsibility_id in number
2541 ,p_resp_application_id in number
2542 ,p_security_group_id in fnd_user_resp_groups.security_group_id%type
2543 ,p_start_date in date default hr_api.g_date
2544 ,p_end_date in date default hr_api.g_date
2545 ,p_description in varchar2 default hr_api.g_varchar2
2546 ) IS
2547 --
2548 CURSOR lc_get_user_id IS
2549 SELECT user_id
2550 FROM fnd_user
2551 WHERE user_id = p_user_id;
2552 --
2553 CURSOR lc_get_resp_id_n_key IS
2554 SELECT responsibility_id
2555 ,responsibility_key
2556 FROM fnd_responsibility
2557 WHERE responsibility_id = p_responsibility_id;
2558 --
2559 CURSOR lc_get_app_id IS
2560 SELECT application_id
2561 FROM fnd_application
2562 WHERE application_id = p_resp_application_id;
2563 --
2564 -- When ENABLED_SECURITY_GROUPS profile option = 'N', then the
2565 -- fnd_user_resp_groups should function like R11 fnd_user_responsibility, the
2566 -- security_group_id is 0 and there should not be more than 1 row for the
2567 -- combination of user_id, responsibility_id, application_id and
2568 -- security_group_id.
2569
2570 CURSOR lc_unique_user_resp IS
2571 SELECT count(*)
2572 FROM fnd_user_resp_groups
2573 WHERE user_id = p_user_id
2574 AND responsibility_id = p_responsibility_id
2575 AND responsibility_application_id = p_resp_application_id
2576 AND security_group_id = p_security_group_id;
2577
2578 CURSOR lc_user_resp_row IS
2579 SELECT responsibility_application_id
2580 ,responsibility_id
2581 ,start_date
2582 ,end_date
2583 ,description
2584 FROM fnd_user_resp_groups
2585 WHERE user_id = p_user_id
2586 AND responsibility_id = p_responsibility_id
2587 AND responsibility_application_id = p_resp_application_id
2588 AND security_group_id = p_security_group_id;
2589
2590 CURSOR lc_user_resp_direct_row IS
2591 SELECT responsibility_application_id
2592 ,responsibility_id
2593 ,start_date
2594 ,end_date
2595 ,description
2596 FROM fnd_user_resp_groups_direct
2597 WHERE user_id = p_user_id
2598 AND responsibility_id = p_responsibility_id
2599 AND responsibility_application_id = p_resp_application_id
2600 AND security_group_id = p_security_group_id;
2601
2602 l_proc varchar2(72) := g_package||'update_fnd_user_resp_groups';
2603 l_count number default null;
2604 l_dummy number default null;
2605 l_start_date date default null;
2606 l_end_date date default null;
2607 l_description fnd_user_resp_groups.description%type default null;
2608 l_resp_key fnd_responsibility.responsibility_key%type default null;
2609 l_fnd_user_resp_data lc_user_resp_row%rowtype;
2610 --
2611 BEGIN
2612 hr_utility.set_location('Entering:' || l_proc, 10);
2613 --
2614 -- Validate input parameters first. We need to validate input parameter
2615 -- again here because users can just invoke this procedure without calling
2616 -- fnd_user_acct_api first.
2617 --
2618 -- Validate user_id
2619 --
2620 l_dummy := null;
2621
2622 OPEN lc_get_user_id;
2623 FETCH lc_get_user_id into l_dummy;
2624 IF lc_get_user_id%NOTFOUND
2625 THEN
2626 CLOSE lc_get_user_id;
2627 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
2628 fnd_message.set_token('TABLE', 'FND_USER');
2629 fnd_message.set_token('COLUMN', 'USER_ID');
2630 fnd_message.set_token('VALUE', to_char(p_user_id));
2631 hr_utility.raise_error;
2632 ELSE
2633 CLOSE lc_get_user_id;
2634 END IF;
2635
2636 --
2637 -- Validate responsibility_id
2638 --
2639 l_dummy := null;
2640
2641 OPEN lc_get_resp_id_n_key;
2642 FETCH lc_get_resp_id_n_key into l_dummy, l_resp_key;
2643 IF lc_get_resp_id_n_key%NOTFOUND
2644 THEN
2645 CLOSE lc_get_resp_id_n_key;
2646 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
2647 fnd_message.set_token('TABLE', 'FND_RESPONSIBILITY');
2648 fnd_message.set_token('COLUMN', 'RESPONSIBILITY_ID');
2649 fnd_message.set_token('VALUE', to_char(p_responsibility_id));
2650 hr_utility.raise_error;
2651 ELSE
2652 CLOSE lc_get_resp_id_n_key;
2653 END IF;
2654
2655 --
2656 -- Validate application_id
2657 --
2658 l_dummy := null;
2659
2660 OPEN lc_get_app_id;
2661 FETCH lc_get_app_id into l_dummy;
2662 IF lc_get_app_id%NOTFOUND
2663 THEN
2664 CLOSE lc_get_app_id;
2665 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
2666 fnd_message.set_token('TABLE', 'FND_APPLICATION');
2667 fnd_message.set_token('COLUMN', 'APPLICATION_ID');
2668 fnd_message.set_token('VALUE', to_char(p_resp_application_id));
2669 hr_utility.raise_error;
2670 ELSE
2671 CLOSE lc_get_app_id;
2672 END IF;
2673 --
2674 -- Validate unique user_resp_groups
2675 -- The following code is copied from FNDSCAUS.fmb program unit
2676 -- FND_UNIQUE_USER_RESP.
2677 --
2678 l_dummy := null;
2679
2680 BEGIN
2681 OPEN lc_unique_user_resp;
2682 FETCH lc_unique_user_resp into l_dummy;
2683 CLOSE lc_unique_user_resp;
2684 --
2685 IF l_dummy > 1
2686 THEN
2687 fnd_message.set_name('FND', 'SECURITY-DUPLICATE USER RESP');
2688 hr_utility.raise_error;
2689 ELSIF l_dummy = 0 OR l_dummy IS NULL
2690 THEN
2691 /*
2692 Bug fix 8582264
2693 When 2 responsibilities have same responsibility_id , but different
2694 application_ids and one of those responsibility is end dated, l_dummy
2695 will be 0
2696 */
2697 NULL;
2698 /*
2699 CLOSE lc_unique_user_resp;
2700 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
2701 fnd_message.set_token('TABLE', 'FND_USER_RESP_GROUPS');
2702 fnd_message.set_token('COLUMN', 'USER_ID');
2703 fnd_message.set_token('VALUE', to_char(p_user_id));
2704 hr_utility.raise_error;
2705 */
2706 END IF;
2707
2708 EXCEPTION
2709 WHEN NO_DATA_FOUND THEN
2710 -- It's an error, the user responsibility record must exist before
2711 -- this program is invoked.
2712 -- CLOSE lc_unique_user_resp;
2713 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
2714 fnd_message.set_token('TABLE', 'FND_USER_RESP_GROUPS');
2715 fnd_message.set_token('COLUMN', 'USER_ID');
2716 fnd_message.set_token('VALUE', to_char(p_user_id));
2717 hr_utility.raise_error;
2718 END;
2719 --
2720 -- Bug #1341128 Fix
2721 -- Get existing fnd_user_resp_groups data
2722 /*
2723 Bug fix 8582264
2724 When 2 responsibilities have same responsibility_id , but different
2725 application_ids and one of those responsibility is end dated, l_dummy
2726 will be 0. In this case, we will fetch the data from
2727 FND_USER_RESP_GROUPS_DIRECT instead of FND_USER_RESP_GROUPS
2728 */
2729
2730 IF l_dummy = 0 OR l_dummy IS NULL THEN
2731 OPEN lc_user_resp_direct_row;
2732 FETCH lc_user_resp_direct_row into l_fnd_user_resp_data;
2733 CLOSE lc_user_resp_direct_row;
2734 ELSE
2735 OPEN lc_user_resp_row;
2736 FETCH lc_user_resp_row into l_fnd_user_resp_data;
2737 CLOSE lc_user_resp_row;
2738 END IF;
2739
2740 -- Convert hr_api default values to null values
2741 IF p_start_date = hr_api.g_date
2742 THEN
2743 l_start_date := l_fnd_user_resp_data.start_date;
2744 ELSE
2745 l_start_date := p_start_date;
2746 END IF;
2747
2748 -- Validate End Date must be >= Start Date
2749 IF p_end_date = hr_api.g_date
2750 THEN
2751 l_end_date := l_fnd_user_resp_data.end_date;
2752 ELSIF p_end_date IS NULL
2753 THEN
2754 l_end_date := p_end_date;
2755 ELSE
2756 -- End Date is not null
2757 l_end_date := p_end_date;
2758 --
2759 -- IF Start Date is null, then it's an error
2760 IF l_end_date < nvl(l_start_date, l_end_date + 1)
2761 THEN
2762 fnd_message.set_name('PER', 'HR_RESP_START_END_DATE');
2763 fnd_message.set_token('RESP_ID', to_char(p_responsibility_id));
2764 fnd_message.set_token('USER_ID', to_char(p_user_id));
2765 hr_utility.raise_error;
2766 END IF;
2767 END IF;
2768 --
2769 IF p_description = hr_api.g_varchar2
2770 THEN
2771 l_description := l_fnd_user_resp_data.description;
2772 ELSE
2773 l_description := p_description;
2774 END IF;
2775 --
2776 --
2777 -- Now call the fnd_user_resp_groups_api
2778 --
2779 hr_utility.set_location(l_proc ||
2780 ' before fnd_user_resp_groups_api.update_assignment', 30);
2781 hr_utility.set_location(l_proc || ' Passing p_user_id=' || p_user_id , 31);
2782 hr_utility.set_location(l_proc || ' Security_group_id=' || p_security_group_id ,32);
2783 --
2784 fnd_user_resp_groups_api.update_assignment
2785 (user_id => p_user_id
2786 ,responsibility_id => p_responsibility_id
2787 ,responsibility_application_id => p_resp_application_id
2788 ,security_group_id =>p_security_group_id -- Fix 2978610
2789 ,start_date => l_start_date
2790 ,end_date => l_end_date
2791 ,description => l_description
2792 );
2793 --
2794 --
2795 hr_utility.set_location('Leaving:'||l_proc, 50);
2796
2797 END update_fnd_user_resp_groups;
2798 --
2799 -- ----------------------------------------------------------------------------
2800 -- |---------------------- < update_sec_profile_asg > ------------------------|
2801 -- ----------------------------------------------------------------------------
2802 --
2803 PROCEDURE update_sec_profile_asg
2804 (p_sec_profile_asg_id in
2805 per_sec_profile_assignments.sec_profile_assignment_id%type default null
2806 ,p_user_id in fnd_user.user_id%type default null
2807 ,p_responsibility_id in per_sec_profile_assignments.responsibility_id%type
2808 default null
2809 ,p_resp_app_id in
2810 per_sec_profile_assignments.responsibility_application_id%type default null
2811 ,p_security_group_id in fnd_user_resp_groups.security_group_id%type
2812 default null
2813 ,p_start_date in per_sec_profile_assignments.start_date%type
2814 default null
2815 ,p_end_date in per_sec_profile_assignments.end_date%type
2816 default null
2817 ,p_object_version_number in
2818 per_sec_profile_assignments.object_version_number%type default null
2819 ) IS
2820 --
2821 --
2822
2823 CURSOR lc_get_user_id IS
2824 SELECT user_id
2825 FROM fnd_user
2826 WHERE user_id = p_user_id;
2827 --
2828 --
2829 CURSOR lc_get_sec_profile_asg_id IS
2830 SELECT sec_profile_assignment_id
2831 ,security_group_id
2832 ,business_group_id
2833 FROM per_sec_profile_assignments
2834 WHERE user_id = p_user_id
2835 AND responsibility_id = p_responsibility_id
2836 AND responsibility_application_id = p_resp_app_id
2837 AND security_group_id = p_security_group_id;
2838 --
2839 CURSOR lc_get_resp_id IS
2840 SELECT responsibility_key
2841 FROM fnd_responsibility
2842 WHERE responsibility_id = p_responsibility_id
2843 AND application_id = p_resp_app_id;
2844
2845 l_dummy number default null;
2846 l_resp_key fnd_responsibility.responsibility_key%type default null;
2847 l_sec_prof_asg_id
2848 per_sec_profile_assignments.sec_profile_assignment_id%type := null;
2849 l_security_group_id per_sec_profile_assignments.security_group_id%type
2850 default null;
2851 l_bg_id per_sec_profile_assignments.business_group_id%type
2852 default null;
2853 l_obj_vers_num
2854 per_sec_profile_assignments.object_version_number%type := null;
2855 l_proc varchar2(72) := g_package|| 'update_sec_profile_asg';
2856
2857 BEGIN
2858 hr_utility.set_location('Entering:' || l_proc, 10);
2859 --
2860 IF p_sec_profile_asg_id IS NOT NULL
2861 THEN
2862 l_sec_prof_asg_id := p_sec_profile_asg_id;
2863 l_obj_vers_num := p_object_version_number;
2864 END IF;
2865
2866 -- Validate input parameters first. We need to validate input parameter
2867 -- again here because users can just invoke this procedure without calling
2868 -- fnd_user_acct_api first.
2869 --
2870 -- Validate user_id
2871 --
2872 l_dummy := null;
2873
2874 IF p_user_id IS NOT NULL
2875 THEN
2876 OPEN lc_get_user_id;
2877 FETCH lc_get_user_id into l_dummy;
2878 IF lc_get_user_id%NOTFOUND
2879 THEN
2880 CLOSE lc_get_user_id;
2881 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
2882 fnd_message.set_token('TABLE', 'FND_USER');
2883 fnd_message.set_token('COLUMN', 'USER_ID');
2884 fnd_message.set_token('VALUE', to_char(p_user_id));
2885 hr_utility.raise_error;
2886 ELSE
2887 CLOSE lc_get_user_id;
2888 END IF;
2889 END IF;
2890 --
2891 -- Validate responsibility_id
2892 --
2893 IF p_responsibility_id IS NOT NULL
2894 THEN
2895 OPEN lc_get_resp_id;
2896 FETCH lc_get_resp_id into l_resp_key;
2897 IF lc_get_resp_id%NOTFOUND
2898 THEN
2899 CLOSE lc_get_resp_id;
2900 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
2901 fnd_message.set_token('TABLE', 'FND_RESPONSIBILITY');
2902 fnd_message.set_token('COLUMN', 'RESPONSIBILITY_ID');
2903 fnd_message.set_token('VALUE', p_responsibility_id);
2904 hr_utility.raise_error;
2905 ELSE
2906 CLOSE lc_get_resp_id;
2907 END IF;
2908 END IF;
2909 --
2910 -- Get sec_profile_assignment_id for a given user and responsibility
2911 --
2912
2913 IF p_user_id IS NOT NULL AND
2914 p_responsibility_id IS NOT NULL
2915 THEN
2916 OPEN lc_get_sec_profile_asg_id;
2917 FETCH lc_get_sec_profile_asg_id into l_sec_prof_asg_id
2918 ,l_security_group_id
2919 ,l_bg_id;
2920 IF lc_get_sec_profile_asg_id%NOTFOUND
2921 THEN
2922 CLOSE lc_get_sec_profile_asg_id;
2923 hr_utility.set_message(800, 'PER_52524_ASP_ASN_NOT_EXISTS');
2924 hr_utility.raise_error;
2925 ELSE
2926 CLOSE lc_get_sec_profile_asg_id;
2927 END IF;
2928 END IF;
2929 --
2930 -- Validate Start Date cannot be null
2931 IF p_start_date IS NULL AND p_sec_profile_asg_id IS NULL
2932 -- IF p_sec_profile_asg_id is not supplied, then caller must supply
2933 -- all the parameters, including p_start_date.
2934 THEN
2935 hr_utility.set_message(800, 'HR_50374_SSL_MAND_START_DATE');
2936 hr_utility.raise_error;
2937 END IF;
2938 --
2939 -- Validate End Date must be >= Start Date
2940 IF p_end_date IS NOT NULL
2941 THEN
2942 IF p_end_date < nvl(p_start_date, p_end_date + 1)
2943 THEN
2944 fnd_message.set_name('PER', 'HR_RESP_START_END_DATE');
2945 fnd_message.set_token('RESP_ID', to_char(p_responsibility_id));
2946 fnd_message.set_token('USER_ID', to_char(p_user_id));
2947 hr_utility.raise_error;
2948 END IF;
2949 END IF;
2950 --
2951 --
2952 -- Now call the per_asp_upd.upd which will update a row in
2953 -- per_sec_profile_assignments as well as fnd_user_resp_groups.
2954 --
2955 per_asp_upd.upd
2956 (p_sec_profile_assignment_id => l_sec_prof_asg_id
2957 ,p_object_version_number => l_obj_vers_num
2958 ,p_start_date => p_start_date
2959 ,p_end_date => p_end_date -- Fix 2978610. Passing start date and end date.
2960
2961 );
2962
2963 --
2964 hr_utility.set_location('Leaving:'||l_proc, 50);
2965
2966
2967 END update_sec_profile_asg;
2968 --
2969
2970 END hr_user_acct_internal;