1 Package Body hr_user_acct_api as
2 /* $Header: hrusrapi.pkb 120.3.12000000.2 2007/02/14 08:14:27 amunsi ship $ */
3 --
4 -- Private Global Variables
5 --
6 --
7 g_package varchar2(33) := 'hr_user_acct_api.';
8 g_api_vers constant number := 1.0;
9 g_empty_fnd_user_rec hr_user_acct_utility.fnd_user_rec;
10 g_emtpy_fnd_resp_tbl hr_user_acct_utility.fnd_responsibility_tbl;
11 g_emtpy_fnd_prof_opt_val_tbl hr_user_acct_utility.fnd_profile_opt_val_tbl;
12 g_empty_fnd_resp_func_tbl hr_user_acct_utility.fnd_resp_functions_tbl;
13 g_empty_func_sec_excl_tbl hr_user_acct_utility.func_sec_excl_tbl;
14 --
15 -- ----------------------------------------------------------------------------
16 -- |-----------------------< create_user_acct >------------------------|
17 -- ----------------------------------------------------------------------------
18 --
19 PROCEDURE create_user_acct
20 (p_validate in boolean default false
21 ,p_person_id in number
22 ,p_per_effective_start_date in date default null
23 ,p_per_effective_end_date in date default null
24 ,p_assignment_id in number default null
25 ,p_asg_effective_start_date in date default null
26 ,p_asg_effective_end_date in date default null
27 ,p_business_group_id in number
28 ,p_date_from in date default null
29 ,p_date_to in date default null
30 ,p_hire_date in date default null
31 ,p_org_structure_id in number default null
32 ,p_org_structure_vers_id in number default null
33 ,p_parent_org_id in number default null
34 ,p_single_org_id in number default null
35 ,p_run_type in varchar2 default null
36 ,p_user_id out nocopy number
37 )
38 is
39 --
40 -- Declare cursors and local variables
41 -- The following two variables must be declared ahead of the cursors because
42 -- they are referenced inside the cursors.
43 l_exist_resp_id fnd_responsibility.responsibility_id%type;
44 l_exist_resp_app_id fnd_responsibility.application_id%type;
45 --
46 --
47 CURSOR lc_get_existing_fnd_resp
48 IS
49 SELECT fr.responsibility_id
50 ,frtl.responsibility_name
51 ,fr.responsibility_key
52 ,fr.application_id resp_app_id
53 ,frtl.description
54 ,fr.start_date
55 ,fr.end_date
56 ,fdg.data_group_name
57 ,fr.data_group_application_id data_group_app_id
58 ,fm.menu_name
59 ,frg.request_group_name
60 ,fr.request_group_id
61 ,fr.group_application_id req_group_app_id
62 ,fr.version
63 ,fr.web_host_name
64 ,fr.web_agent_name
65 FROM fnd_responsibility fr
66 ,fnd_responsibility_tl frtl
67 ,fnd_menus fm
68 ,fnd_data_groups fdg
69 ,fnd_request_groups frg
70 WHERE fr.responsibility_id = l_exist_resp_id
71 AND fr.application_id = l_exist_resp_app_id
72 AND fr.responsibility_id = frtl.responsibility_id
73 AND fr.data_group_id = fdg.data_group_id
74 AND fr.menu_id = fm.menu_id
75 AND fr.request_group_id = frg.request_group_id(+)
76 --BUG 3648732
77 AND fr.application_id = frtl.application_id
78 AND fr.application_id = frg.application_id(+);
79 --
80 --
81 CURSOR lc_fnd_resp_exists (p_resp_key in varchar2)
82 IS
83 SELECT responsibility_id, application_id
84 FROM fnd_responsibility
85 WHERE responsibility_key = p_resp_key;
86 --
87 CURSOR lc_get_sec_group_id
88 IS
89 SELECT security_group_id
90 FROM fnd_security_groups
91 WHERE security_group_key = to_char(p_business_group_id);
92 --
93 l_proc varchar2(72) := g_package||'create_user_acct';
94 l_hire_date date default null;
95 l_date_from date default null;
96 l_date_to date default null;
97 --
98 l_user_name fnd_user.user_name%type default null;
99 l_fnd_user_start_date fnd_user.start_date%type default null;
100 l_fnd_user_end_date fnd_user.end_date%type default null;
101 l_email_address fnd_user.email_address%type default null;
102 l_fax fnd_user.email_address%type default null;
103 l_customer_id fnd_user.customer_id%type default null;
104 l_description fnd_user.description%type default null;
105 l_language fnd_profile_option_values.profile_option_value%type
106 default null;
107 l_user_id number default null;
108 --
109 l_fnd_resp_rec hr_user_acct_utility.fnd_responsibility_rec;
110 l_responsibility_id fnd_responsibility.responsibility_id%type := null;
111 l_responsibility_key fnd_responsibility.responsibility_key%type := null;
112 l_user_resp_start_date fnd_user_resp_groups.start_date%type;
113 l_user_resp_end_date fnd_user_resp_groups.end_date%type;
114 l_user_level_only boolean default false;
115 l_count number default 0;
116 l_prof_opt_val_count number default 0;
117 l_resp_count number default 0;
118 l_resp_func_count number default 0;
119 l_out_profile_opt_val_tbl hr_user_acct_utility.fnd_profile_opt_val_tbl;
120 l_out_profile_opt_val_count number default 0;
121 l_profile_value_saved boolean default null;
122 l_msg_text varchar2(2000) default null;
123 l_new_resp_id fnd_responsibility.responsibility_id%type := null;
124 l_temp varchar2(2000) default null;
125 l_out_func_sec_excl_tbl hr_user_acct_utility.func_sec_excl_tbl;
126 l_old_resp_rec lc_get_existing_fnd_resp%rowtype;
127 l_user_resp_app_id fnd_responsibility.application_id%type := null;
128 l_temp_id number(15) default null;
129 l_status varchar2(2000) default null;
130 l_enable_sec_groups varchar2(2000) default null;
131 l_sec_profile_asg_count number(15) default null;
132 l_sec_group_id fnd_security_groups.security_group_id%type := null;
133
134 --
135 BEGIN
136 hr_utility.set_location('Entering:'|| l_proc, 10);
137 --
138 -- Issue a savepoint.
139 --
140 savepoint create_user_acct;
141 l_hire_date := trunc(p_hire_date);
142 l_date_from := trunc(p_date_from);
143 l_date_to := trunc(p_date_to);
144 --
145 begin
146 -- Clear the global record table variables first. Otherwise,these variables
147 -- will retain values from the previous employee.
148 --
149 hr_user_acct_utility.g_fnd_user_rec := g_empty_fnd_user_rec;
150 hr_user_acct_utility.g_fnd_resp_tbl := g_emtpy_fnd_resp_tbl;
151 hr_user_acct_utility.g_fnd_profile_opt_val_tbl :=
152 g_emtpy_fnd_prof_opt_val_tbl;
153 hr_user_acct_utility.g_fnd_resp_functions_tbl :=
154 g_empty_fnd_resp_func_tbl;
155 --
156 -- Start of API User Hook for the before hook of create_user_acct
157 -- The Person ID, per_all_people_f.effective_start_date and
158 -- per_all_people_f.effective_end_date along with the concurrent
159 -- program's input parameters are passed to the user hooks so
160 -- that the user can retrieve the proper person record or
161 -- assignment record to determine what user name, password and
162 -- the start date for the fnd_users account. Normally, the start
163 -- date for the fnd_users account is the hire date. But the user
164 -- can change it to a later date but not earlier in their user hooks code.
165 --
166 -- Users should use the create_user_acct_b user hook to
167 -- return username, password, responsibilities and profile option values.
168 --
169 hr_utility.set_location('Calling hr_user_acct_bk1.create_user_acct_b', 12);
170 --
171 hr_user_acct_bk1.create_user_acct_b
172 (p_person_id => p_person_id
173 ,p_per_effective_start_date => p_per_effective_start_date
174 ,p_per_effective_end_date => p_per_effective_end_date
175 ,p_assignment_id => p_assignment_id
176 ,p_asg_effective_start_date => p_asg_effective_start_date
177 ,p_asg_effective_end_date => p_asg_effective_end_date
178 ,p_business_group_id => p_business_group_id
179 ,p_date_from => l_date_from
180 ,p_date_to => l_date_to
181 ,p_org_structure_id => p_org_structure_id
182 ,p_org_structure_vers_id => p_org_structure_vers_id
183 ,p_parent_org_id => p_parent_org_id
184 ,p_single_org_id => p_single_org_id
185 ,p_run_type => p_run_type
186 ,p_hire_date => l_hire_date
187 );
188
189 hr_utility.set_location('After calling hr_user_acct_bk1.create_user_acct_b'
190 ,14);
191
192 EXCEPTION
193 when hr_api.cannot_find_prog_unit then
194 hr_api.cannot_find_prog_unit_error
195 (p_module_name => 'CREATE_USER_ACCOUNT'
196 ,p_hook_type => 'BP'
197 );
198 --
199 -- End of API User Hook for the before hook of create_fnd_user_resp
200 --
201 end;
202 --
203 --
204 hr_utility.set_location(l_proc, 20);
205 --
206 --
207 -----------------------------------------------------------------------------
208 -- NOTE: User hooks can pass information back to us, such as user name,
209 -- password, responsibilities and profile info. via global variables
210 -- in the dummy hr_user_acct_utility package header. We directly
211 -- move information from there to the parameters in
212 -- create_fnd_user. Validations will be done in individual business
213 -- process, such as create_fnd_user, not at the wrapper level.
214 --
215 -----------------------------------------------------------------------------
216 --
217 -- In R11.5, the java program no longer needs the host port information since
218 -- the java program is executed within the same session and context. However,
219 -- the fup api still has this parameter as mandatory. We just pass
220 -- null value to the parameter.
221 --
222
223 -- Fix 2288014.
224 -- Passing password_date to the modified hr_user_acct_internal.create_fnd_user
225 -- enabling users to have control over password change after first login.
226 --
227 hr_user_acct_internal.create_fnd_user
228 (p_hire_date => l_hire_date
229 ,p_user_name =>
230 hr_user_acct_utility.g_fnd_user_rec.user_name
231 ,p_password =>
232 hr_user_acct_utility.g_fnd_user_rec.password
233 ,p_user_start_date =>
234 hr_user_acct_utility.g_fnd_user_rec.start_date
235 ,p_user_end_date =>
236 hr_user_acct_utility.g_fnd_user_rec.end_date
237 ,p_email_address =>
238 hr_user_acct_utility.g_fnd_user_rec.email_address
239 ,p_fax =>
240 hr_user_acct_utility.g_fnd_user_rec.fax
241 ,p_description =>
242 hr_user_acct_utility.g_fnd_user_rec.description
243 ,p_password_date =>
244 hr_user_acct_utility.g_fnd_user_rec.password_date -- Fix 2288014
245 ,p_language =>
246 hr_user_acct_utility.g_fnd_user_rec.language
247 ,p_host_port => null
248 ,p_employee_id =>
249 hr_user_acct_utility.g_fnd_user_rec.employee_id
250 ,p_customer_id =>
251 hr_user_acct_utility.g_fnd_user_rec.customer_id
252 ,p_supplier_id =>
253 hr_user_acct_utility.g_fnd_user_rec.supplier_id
254 ,p_user_id => l_user_id
255 );
256 --
257 hr_utility.set_location(l_proc, 30);
258 --
259 -- Create the fnd_responsibility record
260 --
261 l_resp_count := hr_user_acct_utility.g_fnd_resp_tbl.count;
262 --
263 IF l_resp_count < 1
264 -- No fnd_user_resp_groups, per_sec_profile_assignments,
265 -- function exclusions or profile option values to insert
266 THEN
267 goto after_process_hook;
268 END IF;
269 --
270 FOR i in 1..l_resp_count
271 LOOP
272 --
273 -- Clear the l_responsibility_id first
274 l_responsibility_id := null;
275 l_user_resp_app_id := null;
276
277 l_responsibility_key :=
278 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_key;
279 --
280 IF hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_name
281 IS NOT NULL
282 AND
283 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_key IS NOT NULL
284 THEN
285 -- Create a new responsibility,check if using a template responsibility
286 IF hr_user_acct_utility.g_fnd_resp_tbl(i).existing_resp_id
287 IS NOT NULL
288 THEN
289 -- That means we want to create a new responsiblity based on an
290 -- existing responsibility as template. We need to read the
291 -- template responsibility record first.
292 --
293 l_exist_resp_id :=
294 hr_user_acct_utility.g_fnd_resp_tbl(i).existing_resp_id;
295 l_exist_resp_app_id :=
296 hr_user_acct_utility.g_fnd_resp_tbl(i).existing_resp_app_id;
297 --
298 -- read the existing responsibility rec
299 --
300 OPEN lc_get_existing_fnd_resp;
301 FETCH lc_get_existing_fnd_resp into l_old_resp_rec;
302 IF lc_get_existing_fnd_resp%NOTFOUND
303 THEN
304 CLOSE lc_get_existing_fnd_resp;
305 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
306 fnd_message.set_token('TABLE', 'FND_RESPONSIBILITY');
307 fnd_message.set_token('COLUMN', 'RESPONSIBILITY_ID');
308 fnd_message.set_token('VALUE', to_char(l_exist_resp_id));
309 hr_utility.raise_error;
310 ELSE
311 CLOSE lc_get_existing_fnd_resp;
312 END IF;
313
314 l_fnd_resp_rec.new_resp_app_id := l_old_resp_rec.resp_app_id ;
315 l_fnd_resp_rec.new_resp_description := null;
316 l_fnd_resp_rec.new_resp_start_date :=l_old_resp_rec.start_date;
317 l_fnd_resp_rec.new_resp_end_date := null;
318 l_fnd_resp_rec.new_resp_data_group_name :=
319 l_old_resp_rec.data_group_name;
320 l_fnd_resp_rec.new_resp_data_grp_app_id :=
321 l_old_resp_rec.data_group_app_id;
322 l_fnd_resp_rec.new_resp_menu_name := l_old_resp_rec.menu_name;
323 l_fnd_resp_rec.new_resp_request_group_name :=
324 l_old_resp_rec.request_group_name;
325 l_fnd_resp_rec.new_resp_req_grp_app_id :=
326 l_old_resp_rec.req_group_app_id;
327 l_fnd_resp_rec.new_resp_version := l_old_resp_rec.version;
328 l_fnd_resp_rec.new_resp_web_host_name :=
329 l_old_resp_rec.web_host_name;
330 l_fnd_resp_rec.new_resp_web_agent_name :=
331 l_old_resp_rec.web_agent_name;
332 --
333 --
334 END IF; -- existing_resp_id is not null
335 --
336
337 -- Even the responsibility is new, check if the new responsibility
338 -- is already created in an eariler processing of new users who come
339 -- first in the batch. When the user hook passes in the new resp
340 -- info, the responsibility id is not known yet.
341 --
342 For check_resp_rec in lc_fnd_resp_exists
343 (p_resp_key =>
344 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_key)
345 LOOP
346 l_responsibility_id := check_resp_rec.responsibility_id;
347 l_user_resp_app_id := check_resp_rec.application_id;
348 END LOOP;
349 --
350 IF l_responsibility_id IS NOT NULL
351 THEN
352 -- No need to create the responsibility again
353 null;
354 ELSE
355 -- Now, move the new responsibility information in the global
356 -- rec to l_fnd_resp_rec work area.
357 --
358 l_fnd_resp_rec.new_resp_name :=
359 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_name;
360 l_responsibility_key :=
361 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_key;
362 l_fnd_resp_rec.new_resp_key :=
363 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_key;
364 l_fnd_resp_rec.new_resp_description :=
365 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_description;
366 --
367 IF hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_app_id <>
368 hr_api.g_number
369 THEN
370 l_fnd_resp_rec.new_resp_app_id :=
371 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_app_id;
372 -- Set the application_id for fnd_user_resp_groups to
373 -- the new resp app id
374 l_user_resp_app_id :=
375 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_app_id;
376 ELSE
377 -- Set the application_id for fnd_user_resp_groups to
378 -- the template responsibility's app id
379 l_user_resp_app_id := l_fnd_resp_rec.new_resp_app_id;
380 END IF;
381 --
382 IF hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_start_date <>
383 hr_api.g_date
384 THEN
385 l_fnd_resp_rec.new_resp_start_date :=
386 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_start_date;
387 END IF;
388 --
389 IF hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_end_date <>
390 hr_api.g_date
391 THEN
392 l_fnd_resp_rec.new_resp_end_date :=
393 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_end_date;
394 END IF;
395 --
396 IF
397 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_data_group_name
398 <> hr_api.g_varchar2
399 THEN
400 l_fnd_resp_rec.new_resp_data_group_name :=
401 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_data_group_name;
402 END IF;
403 --
404 IF
405 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_data_grp_app_id
406 <> hr_api.g_number
407 THEN
408 l_fnd_resp_rec.new_resp_data_grp_app_id :=
409 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_data_grp_app_id;
410 --
411 END IF;
412 --
413 IF
414 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_menu_name
415 <> hr_api.g_varchar2
416 THEN
417 l_fnd_resp_rec.new_resp_menu_name :=
418 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_menu_name;
419 END IF;
420 --
421 IF
422 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_request_group_name
423 <> hr_api.g_varchar2
424 THEN
425 l_fnd_resp_rec.new_resp_request_group_name :=
426 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_request_group_name;
427 END IF;
428 --
429 IF
430 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_req_grp_app_id
431 <> hr_api.g_number
432 THEN
433 l_fnd_resp_rec.new_resp_req_grp_app_id :=
434 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_req_grp_app_id;
435 END IF;
436 --
437 IF hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_version
438 <> hr_api.g_varchar2
439 THEN
440 l_fnd_resp_rec.new_resp_version :=
441 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_version;
442 END IF;
443 --
444 IF hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_web_host_name
445 <> hr_api.g_varchar2
446 THEN
447 l_fnd_resp_rec.new_resp_web_host_name :=
448 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_web_host_name;
449 END IF;
450 --
451 IF hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_web_agent_name
452 <> hr_api.g_varchar2
453 THEN
454 l_fnd_resp_rec.new_resp_web_agent_name :=
455 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_web_agent_name;
456 END IF;
457 --
458 hr_utility.set_location(l_proc, 40);
459 -- **********************************************
460 -- Create the new responsibility
461 -- **********************************************
462 hr_user_acct_internal.create_fnd_responsibility
463 (p_resp_key => l_fnd_resp_rec.new_resp_key
464 ,p_resp_name => l_fnd_resp_rec.new_resp_name
465 ,p_resp_app_id => l_fnd_resp_rec.new_resp_app_id
466 ,p_resp_description => l_fnd_resp_rec.new_resp_description
467 ,p_start_date => l_fnd_resp_rec.new_resp_start_date
468 ,p_end_date => l_fnd_resp_rec.new_resp_end_date
469 ,p_data_group_name =>
470 l_fnd_resp_rec.new_resp_data_group_name
471 ,p_data_group_app_id => l_fnd_resp_rec.new_resp_data_grp_app_id
472 ,p_menu_name => l_fnd_resp_rec.new_resp_menu_name
473 ,p_request_group_name =>
474 l_fnd_resp_rec.new_resp_request_group_name
475 ,p_request_group_app_id =>
476 l_fnd_resp_rec.new_resp_req_grp_app_id
477 ,p_version => l_fnd_resp_rec.new_resp_version
478 ,p_web_host_name => l_fnd_resp_rec.new_resp_web_host_name
479 ,p_web_agent_name => l_fnd_resp_rec.new_resp_web_agent_name
480 ,p_responsibility_id => l_responsibility_id
481 );
482 --
483 -- Save the new responsibility id for later use when creating profile
484 -- option values.
485 --
486 END IF; -- l_responsibility_id is null on checking the new resp key
487 --
488 ELSE
489 -- new resp name is null, no need to create a new responsibility.
490 -- Attach an existing responsibility to the new user.
491 l_responsibility_id :=
492 hr_user_acct_utility.g_fnd_resp_tbl(i).existing_resp_id;
493 l_responsibility_key :=
494 hr_user_acct_utility.g_fnd_resp_tbl(i).existing_resp_key;
495 l_user_resp_app_id :=
496 hr_user_acct_utility.g_fnd_resp_tbl(i).existing_resp_app_id;
497 END IF;
498 --
499 l_fnd_resp_rec.user_resp_start_date :=
500 hr_user_acct_utility.g_fnd_resp_tbl(i).user_resp_start_date;
501
502 l_fnd_resp_rec.user_resp_end_date :=
503 hr_user_acct_utility.g_fnd_resp_tbl(i).user_resp_end_date;
504
505 l_fnd_resp_rec.user_resp_description :=
506 hr_user_acct_utility.g_fnd_resp_tbl(i).user_resp_description;
507
508 l_fnd_resp_rec.sec_group_id :=
509 hr_user_acct_utility.g_fnd_resp_tbl(i).sec_group_id;
510
511 l_fnd_resp_rec.sec_profile_id :=
512 hr_user_acct_utility.g_fnd_resp_tbl(i).sec_profile_id;
513 --
514 -- Get the profile option value for 'ENABLE_SECURITY_GROUPS'
515 -- Use value_specific because you want the value of the resp being
516 -- assigned, not the resp you used to login.
517 -- The following code is copied from FNDSCAUS.fmb
518 -- FND_RESTRICT_SECURITY_GROUP program unit.
519 --
520 l_enable_sec_groups := nvl(fnd_profile_server.value_specific(
521 'ENABLE_SECURITY_GROUPS'
522 ,l_user_id
523 ,l_responsibility_id
524 ,l_user_resp_app_id)
525 ,'N');
526
527 IF l_enable_sec_groups = 'N'
528 THEN
529 hr_utility.set_location (l_proc ||
530 ' before create_fnd_user_resp_groups', 50);
531 --
532 -- **********************************************
533 -- Create the new fnd_user_resp_groups record
534 -- **********************************************
535 -- NOTE: Only insert a row into fnd_user_resp_groups
536 -- when the profile option 'ENABLE_SECURITY_GROUPS'
537 -- is 'N'.
538 --
539 hr_user_acct_internal.create_fnd_user_resp_groups
540 (p_user_id => l_user_id
541 ,p_responsibility_id => l_responsibility_id
542 ,p_application_id => l_user_resp_app_id
543 ,p_sec_group_id => 0
544 ,p_start_date => l_fnd_resp_rec.user_resp_start_date
545 ,p_end_date => l_fnd_resp_rec.user_resp_end_date
546 ,p_description => l_fnd_resp_rec.user_resp_description
547 );
548
549 hr_utility.set_location (l_proc ||
550 ' after create_fnd_user_resp_groups', 51);
551 ELSE
552 -- 'ENABLE_SECURITY_GROUPS' = 'Y'; customers have the option to insert
553 -- into fnd_user_resp_groups only if view-all security profile of the
554 -- employee's business group is to be used. This will be applicable
555 -- to Employee Self Service responsibility. If a restricted security
556 -- profile is to be used, then we must call hrasprhi.pkb which will
557 -- insert one row into per_sec_profile_assignments as well as to
558 -- fnd_user_resp_groups.
559
560 IF l_fnd_resp_rec.sec_group_id IS NULL OR
561 l_fnd_resp_rec.sec_profile_id IS NULL
562 THEN
563 -- Insert into fnd_user_resp_groups only
564 -- get the security_group_id for the employee's business_group_id
565
566 OPEN lc_get_sec_group_id;
567 FETCH lc_get_sec_group_id into l_sec_group_id;
568 IF lc_get_sec_group_id%NOTFOUND
569 THEN
570 CLOSE lc_get_sec_group_id;
571 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
572 fnd_message.set_token('TABLE', 'FND_SECURITY_GROUPS');
573 fnd_message.set_token('COLUMN', 'SECURITY_GROUP_KEY');
574 fnd_message.set_token('VALUE', to_char(p_business_group_id));
575 hr_utility.raise_error;
576 ELSE
577 CLOSE lc_get_sec_group_id;
578 END IF;
579
580 hr_utility.set_location (l_proc ||
581 ' before create_fnd_user_resp_groups', 53);
582 --
583 hr_user_acct_internal.create_fnd_user_resp_groups
584 (p_user_id => l_user_id
585 ,p_responsibility_id => l_responsibility_id
586 ,p_application_id => l_user_resp_app_id
587 ,p_sec_group_id => l_sec_group_id
588 ,p_start_date => l_fnd_resp_rec.user_resp_start_date
589 ,p_end_date => l_fnd_resp_rec.user_resp_end_date
590 ,p_description => l_fnd_resp_rec.user_resp_description
591 );
592
593 hr_utility.set_location (l_proc ||
594 ' after create_fnd_user_resp_groups', 54);
595 --
596 ELSE
597 -- sec_group_id and sec_profile_id are filled in
598 -- call peasprhi.pkb to insert into per_sec_profile_assignments
599 -- as well as fnd_user_resp_groups.
600
601 hr_utility.set_location (l_proc ||
602 ' before create_sec_profile_asg', 56);
603
604 -- Insert this row into per_sec_profile_assignments
605 hr_user_acct_internal.create_sec_profile_asg
606 (p_user_id => l_user_id
607 ,p_sec_group_id => l_fnd_resp_rec.sec_group_id
608 ,p_sec_profile_id => l_fnd_resp_rec.sec_profile_id
609 ,p_resp_key => l_responsibility_key
610 ,p_resp_app_id => l_user_resp_app_id
611 ,p_start_date => l_fnd_resp_rec.user_resp_start_date
612 ,p_end_date => l_fnd_resp_rec.user_resp_end_date
613 );
614
615 hr_utility.set_location (l_proc ||
616 ' after create_sec_profile_asg', 57);
617 END IF;
618 END IF; -- End l_enable_sec_groups check
619 --
620 END LOOP; -- end of loop of g_fnd_resp_tbl
621 --
622 --
623 --
624 <<add_func_security_exclusion>>
625 -- ************************************************
626 -- Now create the fnd_resp_functions
627 -- ************************************************
628
629 hr_utility.set_location(l_proc, 60);
630 --
631 l_resp_func_count :=
632 hr_user_acct_utility.g_fnd_resp_functions_tbl.count;
633 --
634 IF l_resp_func_count < 1
635 THEN
636 goto add_profile_opt_values;
637 END IF;
638 --
639 -- Initialize l_out_func_sec_excl_tbl
640 l_out_func_sec_excl_tbl := g_empty_func_sec_excl_tbl;
641 --
642 -- Build the function security exclusion rules table by
643 -- combining the template responsibility's rules with
644 -- any new rules.
645 --
646 hr_user_acct_internal.build_func_sec_exclusion_rules
647 (p_func_sec_excl_tbl => hr_user_acct_utility.g_fnd_resp_functions_tbl
648 ,p_out_func_sec_excl_tbl => l_out_func_sec_excl_tbl);
649 --
650 l_resp_func_count := l_out_func_sec_excl_tbl.count;
651
652 IF l_resp_func_count < 1
653 THEN
654 goto add_profile_opt_values;
655 END IF;
656 --
657 FOR i in 1..l_resp_func_count
658 LOOP
659 hr_user_acct_internal.create_fnd_resp_functions
660 (p_resp_key => l_out_func_sec_excl_tbl(i).resp_key
661 ,p_rule_type => l_out_func_sec_excl_tbl(i).rule_type
662 ,p_rule_name => l_out_func_sec_excl_tbl(i).rule_name
663 ,p_delete_flag => 'N');
664 END LOOP;
665 --
666 --
667 <<add_profile_opt_values>>
668 -- ************************************************
669 -- Now create the fnd_profile_option_values
670 -- ************************************************
671
672 hr_utility.set_location(l_proc, 70);
673 --
674 l_prof_opt_val_count :=
675 hr_user_acct_utility.g_fnd_profile_opt_val_tbl.count;
676 --
677 IF l_prof_opt_val_count < 1
678 THEN
679 goto after_process_hook;
680 END IF;
681 --
682 -- NOTE: For Profile Option Level, 10003 = 'RESP', 10004 = 'USER'.
683 IF l_resp_count < 1
684 THEN
685 -- That means there is no responsibility attached to the new user id.
686 -- We only need to add profile option values at the user level. We
687 -- will do that at the end of this IF statement.
688 goto add_user_lvl_profile_val;
689 END IF;
690 --
691 -- The user id is attached to some responsibilities
692 -- Add profile option values at either the responsibility or user level.
693 -- Check if a new responsibility is created and a template
694 -- responsibility_id is used for creating the new responsibility.
695 -- Loop through the hr_user_acct_utility.g_fnd_resp_tbl table. If
696 -- a new responsibility is created via a template responsibility, we'll
697 -- create profile option values for the new responsibility based on the
698 -- template responsibility.
699 FOR i in 1..l_resp_count
700 LOOP
701 --
702 IF hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_key IS NOT NULL
703 AND
704 hr_user_acct_utility.g_fnd_resp_tbl(i).existing_resp_id
705 IS NOT NULL
706 THEN
707 -- New responsibility using an existing resp as a template, we'll
708 -- make a copy of the profile option values from the template resp
709 -- for the new responsibility.
710 hr_user_acct_internal.build_resp_profile_val
711 (p_template_resp_id =>
712 hr_user_acct_utility.g_fnd_resp_tbl(i).existing_resp_id
713 ,p_template_resp_app_id =>
714 hr_user_acct_utility.g_fnd_resp_tbl(i).existing_resp_app_id
715 ,p_new_resp_key =>
716 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_key
717 ,p_new_resp_app_id =>
718 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_app_id
719 ,p_fnd_profile_opt_val_tbl =>
720 hr_user_acct_utility.g_fnd_profile_opt_val_tbl
721 ,p_out_profile_opt_val_tbl => l_out_profile_opt_val_tbl
722 );
723 --
724 l_out_profile_opt_val_count := l_out_profile_opt_val_tbl.count;
725 --
726 OPEN lc_fnd_resp_exists (p_resp_key =>
727 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_key);
728 FETCH lc_fnd_resp_exists into l_new_resp_id, l_temp_id;
729 IF lc_fnd_resp_exists%NOTFOUND
730 THEN
731 CLOSE lc_fnd_resp_exists;
732 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
733 fnd_message.set_token('TABLE', 'FND_RESPONSIBILITY');
734 fnd_message.set_token('COLUMN', 'RESPONSIBILITY_KEY');
735 fnd_message.set_token('VALUE',
736 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_key);
737 hr_utility.raise_error;
738 ELSE
739 CLOSE lc_fnd_resp_exists;
740 END IF;
741 --
742 IF l_out_profile_opt_val_count > 0
743 THEN
744 --
745 FOR j in 1..l_out_profile_opt_val_count
746 LOOP
747 -- Reset the variable before each loop
748 l_profile_value_saved := null;
749 hr_user_acct_internal.create_fnd_profile_values
750 (p_profile_opt_name =>
751 l_out_profile_opt_val_tbl(j).profile_option_name
752 ,p_profile_opt_value =>
753 l_out_profile_opt_val_tbl(j).profile_option_value
754 ,p_profile_level_name => 'RESP'
755 ,p_profile_level_value => l_new_resp_id
756 ,p_profile_lvl_val_app_id =>
757 l_out_profile_opt_val_tbl(j).profile_level_value_app_id
758 ,p_profile_value_saved => l_profile_value_saved
759 );
760 --
761 IF l_profile_value_saved
762 THEN
763 null;
764 ELSE
765 -- Write the error to the log file and continue to
766 -- process.
767 l_msg_text := null;
768 fnd_message.set_name('PER', 'HR_PROFILE_VAL_NOT_ADDED');
769 fnd_message.set_token('RESP_KEY',
770 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_key);
771 fnd_message.set_token('PROFIE_OPTION_NAME',
772 l_out_profile_opt_val_tbl(j).profile_option_name);
773 fnd_message.set_token('PROFILE_OPTION_VALUE',
774 l_out_profile_opt_val_tbl(j).profile_option_value);
775 hr_utility.raise_error;
776 END IF;
777 END LOOP; -- end loop for inserting each profile opt value rec
778 -- at the responsibility level
779
780 END IF; -- end l_out_profile_opt_val_count > 0
781 ELSIF
782 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_key IS NOT NULL
783 THEN
784 -- that means existing resp id is null, we'll just attach profile
785 -- option values at the new resp level.
786 hr_user_acct_internal.build_resp_profile_val
787 (p_template_resp_id => null
788 ,p_template_resp_app_id => null
789 ,p_new_resp_key =>
790 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_key
791 ,p_new_resp_app_id =>
792 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_app_id
793 ,p_fnd_profile_opt_val_tbl =>
794 hr_user_acct_utility.g_fnd_profile_opt_val_tbl
795 ,p_out_profile_opt_val_tbl => l_out_profile_opt_val_tbl
796 );
797 --
798 l_out_profile_opt_val_count := l_out_profile_opt_val_tbl.count;
799 --
800 OPEN lc_fnd_resp_exists (p_resp_key =>
801 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_key);
802 FETCH lc_fnd_resp_exists into l_new_resp_id, l_temp_id;
803 IF lc_fnd_resp_exists%NOTFOUND
804 THEN
805 CLOSE lc_fnd_resp_exists;
806 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
807 fnd_message.set_token('TABLE', 'FND_RESPONSIBILITY');
808 fnd_message.set_token('COLUMN', 'RESPONSIBILITY_KEY');
809 fnd_message.set_token('VALUE',
810 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_key);
811 hr_utility.raise_error;
812 ELSE
813 CLOSE lc_fnd_resp_exists;
814 END IF;
815 --
816 IF l_out_profile_opt_val_count > 0
817 THEN
818 --
819 FOR j in 1..l_out_profile_opt_val_count
820 LOOP
821 l_profile_value_saved := null;
822 hr_user_acct_internal.create_fnd_profile_values
823 (p_profile_opt_name =>
824 l_out_profile_opt_val_tbl(j).profile_option_name
825 ,p_profile_opt_value =>
826 l_out_profile_opt_val_tbl(j).profile_option_value
827 ,p_profile_level_name => 'RESP'
828 ,p_profile_level_value => l_new_resp_id
829 ,p_profile_lvl_val_app_id =>
830 l_out_profile_opt_val_tbl(j).profile_level_value_app_id
831 ,p_profile_value_saved => l_profile_value_saved
832 );
833 IF l_profile_value_saved
834 THEN
835 null;
836 ELSE
837 -- Write the error to the log file and continue to
838 -- process.
839 l_msg_text := null;
840 fnd_message.set_name('PER', 'HR_PROFILE_VAL_NOT_ADDED');
841 fnd_message.set_token('RESP_KEY',
842 hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_key);
843 l_temp :=l_out_profile_opt_val_tbl(j).profile_option_name;
844 fnd_message.set_token('PROFIE_OPTION_NAME', l_temp);
845 --
846 l_temp:=l_out_profile_opt_val_tbl(j).profile_option_value;
847 fnd_message.set_token('PROFILE_OPTION_VALUE', l_temp);
848 hr_utility.raise_error;
849 END IF;
850 END LOOP; -- end loop for inserting each profile opt value rec
851 -- at the responsibility level
852
853 END IF; -- end l_out_proifile_opt_val_count > 0
854 END IF; -- end new_resp_key is not null
855 --
856 END LOOP; -- end loop of hr_user_acct_utility.g_fnd_resp_tbl
857 --
858 --
859 <<add_user_lvl_profile_val>>
860 --
861 -- Now insert user level profile opt values
862 FOR i in 1..l_prof_opt_val_count
863 LOOP
864 l_profile_value_saved := null;
865 IF hr_user_acct_utility.g_fnd_profile_opt_val_tbl(i).profile_level_name
866 = 'USER'
867 THEN
868 hr_user_acct_internal.create_fnd_profile_values
869 (p_profile_opt_name =>
870 hr_user_acct_utility.g_fnd_profile_opt_val_tbl(i).profile_option_name
871 ,p_profile_opt_value =>
872 hr_user_acct_utility.g_fnd_profile_opt_val_tbl(i).profile_option_value
873 ,p_profile_level_name => 'USER'
874 ,p_profile_level_value =>l_user_id -- Fix 2825757
875 ,p_profile_lvl_val_app_id =>
876 hr_user_acct_utility.g_fnd_profile_opt_val_tbl(i).profile_level_value_app_id
877 ,p_profile_value_saved => l_profile_value_saved
878 );
879 --
880 IF l_profile_value_saved
881 THEN
882 null;
883 ELSE
884 -- Write the error to the log file and continue to process.
885 l_msg_text := null;
886 fnd_message.set_name('PER', 'HR_PROFILE_USER_VAL_NOT_ADDED');
887 l_temp := l_out_profile_opt_val_tbl(i).profile_option_name;
888 fnd_message.set_token('PROFIE_OPTION_NAME', l_temp);
889 --
890 l_temp := l_out_profile_opt_val_tbl(i).profile_option_value;
891 fnd_message.set_token('PROFILE_OPTION_VALUE', l_temp);
892 hr_utility.raise_error;
893 END IF;
894 END IF;
895 END LOOP;
896 --
897
898 <<after_process_hook>>
899
900 BEGIN
901 --
902 -- Start of API User Hook for the after hook of create_user_acct
903 --
904 hr_user_acct_bk1.create_user_acct_a
905 (p_person_id => p_person_id
906 ,p_per_effective_start_date => p_per_effective_start_date
907 ,p_per_effective_end_date => p_per_effective_end_date
908 ,p_assignment_id => p_assignment_id
909 ,p_asg_effective_start_date => p_asg_effective_start_date
910 ,p_asg_effective_end_date => p_asg_effective_end_date
911 ,p_business_group_id => p_business_group_id
912 ,p_date_from => l_date_from
913 ,p_date_to => l_date_to
914 ,p_org_structure_id => p_org_structure_id
915 ,p_org_structure_vers_id => p_org_structure_vers_id
916 ,p_parent_org_id => p_parent_org_id
917 ,p_single_org_id => p_single_org_id
918 ,p_run_type => p_run_type
919 ,p_hire_date => l_hire_date
920 );
921
922 EXCEPTION
923 WHEN hr_api.cannot_find_prog_unit THEN
924 hr_api.cannot_find_prog_unit_error
925 (p_module_name => 'CREATE_USER_ACCOUNT'
926 ,p_hook_type => 'AP'
927 );
928 --
929 -- End of API User Hook for the after hook of create_user_acct
930 --
931 end;
932 --
933 -- Set all output arguments
934 --
935 p_user_id := l_user_id;
936 --
937 -- When in validation only mode raise the Validate_Enabled exception
938 --
939 IF p_validate
940 THEN
941 raise hr_api.validate_enabled;
942 END IF;
943 --
944 hr_utility.set_location(' Leaving:'||l_proc, 80);
945
946 EXCEPTION
947 WHEN hr_api.validate_enabled THEN
948 --
949 -- As the Validate_Enabled exception has been raised
950 -- we must rollback to the savepoint
951 --
952 ROLLBACK TO create_user_acct;
953 --
954 ---------------------------------------------------------------------------
955 -- NOTE:
956 -- In R11.5, the java program WebSessionManager.class is now a stored
957 -- procedure on the database. It is being executed as part of the
958 -- transaction. When we rollback, the initial fnd_user record created by
959 -- the java program will also be rolled back. We no longer need to
960 -- remove the dangling fnd_user manually.
961 ---------------------------------------------------------------------------
962 --
963 -- Only set output warning arguments
964 -- (Any key or derived arguments must be set to null
965 -- when validation only mode is being used.)
966 --
967 p_user_id := null;
968 --
969 --
970 WHEN others THEN
971 --
972 -- A validation or unexpected error has occurred
973 --
974 --
975 ROLLBACK TO create_user_acct;
976 --
977 ---------------------------------------------------------------------------
978 -- NOTE:
979 -- In R11.5, the java program WebSessionManager.class is now a stored
980 -- procedure on the database. It is being executed as part of the
981 -- transaction. When we rollback, the initial fnd_user record created by
982 -- the java program will also be rolled back. We no longer need to
983 -- remove the dangling fnd_user manually.
984 ---------------------------------------------------------------------------
985 --
986 p_user_id := null;
987 raise;
988 --
989 END create_user_acct;
990 --
991 --
992 -- ----------------------------------------------------------------------------
993 -- |-------------------------- < update_user_acct > --------------------------|
994 -- | |
995 -- | USAGE: |
996 -- | ----- |
997 -- | This wrapper module is used to update fnd_user and |
998 -- | fnd_user_resp_groups, or per_sec_profile_assignments records specifically|
999 -- | for expiring a user account. |
1000 -- | User accounts for terminated employees will not be deleted because |
1001 -- | some HR history forms have sql statements join to the fnd_user table |
1002 -- | derive the who columns. |
1003 -- ----------------------------------------------------------------------------
1004 --
1005 PROCEDURE update_user_acct
1006 (p_validate in boolean default false
1007 ,p_person_id in number
1008 ,p_per_effective_start_date in date default null
1009 ,p_per_effective_end_date in date default null
1010 ,p_assignment_id in number default null
1011 ,p_asg_effective_start_date in date default null
1012 ,p_asg_effective_end_date in date default null
1013 ,p_business_group_id in number
1014 ,p_date_from in date default null
1015 ,p_date_to in date default null
1016 ,p_org_structure_id in number default null
1017 ,p_org_structure_vers_id in number default null
1018 ,p_parent_org_id in number default null
1019 ,p_single_org_id in number default null
1020 ,p_run_type in varchar2 default null
1021 ,p_inactivate_date in date
1022 )
1023 is
1024 --
1025 l_date date default null;
1026 --
1027 --
1028 CURSOR lc_get_user_id
1029 IS
1030 SELECT user_id
1031 FROM fnd_user
1032 WHERE employee_id = p_person_id
1033 AND nvl(end_date, hr_api.g_eot) > l_date;
1034 --
1035 -- Cursor to select all records which belongs to the terminated employee's
1036 -- user id and the end date is null or the end date is greater than the
1037 -- termination date.
1038 --
1039 CURSOR lc_get_sec_profile_asg (c_user_id in number)
1040 IS
1041 SELECT sec_profile_assignment_id
1042 ,security_group_id
1043 ,security_profile_id
1044 ,responsibility_id
1045 ,responsibility_application_id
1046 ,object_version_number
1047 ,start_date
1048 FROM per_sec_profile_assignments
1049 WHERE user_id = c_user_id
1050 AND nvl(end_date, l_date + 1) > l_date;
1051 --
1052 -- Cursor to select all records which belongs to the terminated employee's
1053 -- user id and the end date is null or the end date is greater than the
1054 -- termination date.
1055 --
1056 -- Fix for bug 4147802 starts here. used fnd_user_resp_groups_direct view
1057 -- in place of fnd_user_resp_groups. Also the column description is removed.
1058 --
1059 CURSOR lc_get_user_resp (c_user_id in number)
1060 IS
1061 /*
1062 SELECT responsibility_application_id
1063 ,responsibility_id
1064 ,security_group_id
1065 ,start_date
1066 ,end_date
1067 -- ,description
1068 FROM fnd_user_resp_groups_direct
1069 WHERE user_id = c_user_id
1070 AND nvl(end_date, l_date + 1) > l_date
1071 AND trunc(sysdate) between start_date and nvl(end_date,sysdate); --5090502
1072 */
1073 SELECT furgd.responsibility_application_id
1074 ,furgd.responsibility_id
1075 ,furgd.security_group_id
1076 ,furgd.start_date
1077 ,furgd.end_date
1078 -- ,description
1079 FROM fnd_user_resp_groups_direct furgd, FND_RESPONSIBILITY fr
1080 WHERE furgd.user_id = c_user_id
1081 AND fr.responsibility_id = furgd.responsibility_id
1082 AND trunc(sysdate) between fr.start_date and nvl(fr.end_date,sysdate)
1083 AND nvl(furgd.end_date, l_date + 1) > l_date
1084 AND trunc(sysdate) between furgd.start_date and nvl(furgd.end_date,sysdate);
1085 --
1086 --
1087
1088 l_proc varchar2(72) := g_package||'update_user_acct';
1089 --
1090 --
1091 BEGIN
1092 hr_utility.set_location('Entering:'|| l_proc, 10);
1093 --
1094 -- Issue a savepoint.
1095 --
1096 savepoint update_user_acct;
1097 l_date := trunc(p_inactivate_date) + 1; -- Bug 4960718
1098 --
1099 begin
1100 --
1101 hr_user_acct_bk2.update_user_acct_b
1102 (p_person_id => p_person_id
1103 ,p_per_effective_start_date => p_per_effective_start_date
1104 ,p_per_effective_end_date => p_per_effective_end_date
1105 ,p_assignment_id => p_assignment_id
1106 ,p_asg_effective_start_date => p_asg_effective_start_date
1107 ,p_asg_effective_end_date => p_asg_effective_end_date
1108 ,p_business_group_id => p_business_group_id
1109 ,p_date_from => p_date_from
1110 ,p_date_to => p_date_to
1111 ,p_org_structure_id => p_org_structure_id
1112 ,p_org_structure_vers_id => p_org_structure_vers_id
1113 ,p_parent_org_id => p_parent_org_id
1114 ,p_single_org_id => p_single_org_id
1115 ,p_run_type => p_run_type
1116 ,p_inactivate_date => l_date
1117 );
1118
1119
1120 EXCEPTION
1121 when hr_api.cannot_find_prog_unit then
1122 hr_api.cannot_find_prog_unit_error
1123 (p_module_name => 'UPDATE_USER_ACCOUNT'
1124 ,p_hook_type => 'BP'
1125 );
1126 --
1127 end;
1128 --
1129 --
1130 hr_utility.set_location(l_proc, 20);
1131 --
1132 --
1133 -- Update fnd_user.end_date with the p_inactivate_date passed in
1134 -- A person may have more than 1 user accounts opened. Need to
1135 -- get all the user ids associated to the person.
1136 --
1137 FOR get_user_ids in lc_get_user_id
1138 LOOP
1139 --
1140 ----------------------------------------------------------------------------
1141 -- NOTE:
1142 -- As of the time writing this code, the allowable update function is to
1143 -- inactivate an fnd_user.date_to when an employee is terminated.
1144 -- No code is provided to massively update other attributes of the fnd_user
1145 -- rec, such as the password. For bulk changes to password, we need to know
1146 -- the old password (decrypted) and as of now, there is no way to decrypt
1147 -- a password without using the java code. Hence, the only functionality
1148 -- allowed for updating an fnd_user rec is to end date the record.
1149 ----------------------------------------------------------------------------
1150 hr_user_acct_internal.update_fnd_user
1151 (p_user_id => get_user_ids.user_id
1152 ,p_end_date => l_date
1153 );
1154 --
1155 -- Need to end date all rows in per_sec_profile_assignments associated
1156 -- the user id.
1157 -- The per_asp_upd(peasprhi.pkb) api will transparently end date the
1158 -- fnd_user_resp_groups records as well.
1159
1160
1161 FOR get_sec_prf_asg in lc_get_sec_profile_asg
1162 (c_user_id => get_user_ids.user_id)
1163 LOOP
1164 hr_utility.trace('Calling update_sec_profile_asg with ' || get_sec_prf_asg.sec_profile_assignment_id);
1165 hr_user_acct_internal.update_sec_profile_asg
1166 (p_sec_profile_asg_id => get_sec_prf_asg.sec_profile_assignment_id
1167
1168 ,p_object_version_number => get_sec_prf_asg.object_version_number
1169 ,p_start_date => get_sec_prf_asg.start_date
1170 ,p_end_date => l_date -- Fix 2978610
1171 );
1172 END LOOP;
1173
1174
1175 -- Need to end date the fnd_user_resp_groups record for
1176 -- each user id.
1177 -- The cursor needs to return the application_id, start_date
1178 -- end_date, description, responsibility_id in addition to
1179 -- user_id because these attributes are the required parameters
1180 -- in fnd_user_resp_groups update_row.
1181 --
1182 -- If 'ENABLE_SECURITY_GROUPS' = 'Y' and the data are setup
1183 -- correctly, there should not be any rows returned from the
1184 -- lc_get_user_resp cursor because the peasprhi.pkb api would
1185 -- have already end dated the fnd_user_resp_groups record. If
1186 -- we have rows returned from the lc_get_user_resp, that means
1187 -- 'ENABLE_SECURITY_GROUPS' = 'N'. Hence, we need to end date
1188 -- the fnd_user_resp_groups rows.
1189 FOR get_user_resp in lc_get_user_resp
1190 (c_user_id => get_user_ids.user_id)
1191 LOOP
1192 hr_utility.trace('Calling update_fnd_user_resp_groups with ');
1193 hr_utility.trace('user_id' || get_user_ids.user_id);
1194 hr_utility.trace('security_group_id' || get_user_resp.security_group_id);
1195 hr_utility.trace('p_end_date' || l_date);
1196
1197 hr_user_acct_internal.update_fnd_user_resp_groups
1198 (p_user_id => get_user_ids.user_id
1199 ,p_responsibility_id => get_user_resp.responsibility_id
1200 ,p_resp_application_id
1201 => get_user_resp.responsibility_application_id
1202 ,p_security_group_id => get_user_resp.security_group_id
1203 ,p_start_date => get_user_resp.start_date
1204 ,p_end_date => l_date
1205 --,p_description => get_user_resp.description -- Bug 4147802
1206 );
1207 END LOOP;
1208 END LOOP;
1209 --
1210 hr_utility.set_location(l_proc, 30);
1211 --
1212 --
1213 BEGIN
1214 --
1215 -- Start of API User Hook for the after hook of create_user_acct
1216 --
1217 hr_user_acct_bk2.update_user_acct_a
1218 (p_person_id => p_person_id
1219 ,p_per_effective_start_date => p_per_effective_start_date
1220 ,p_per_effective_end_date => p_per_effective_end_date
1221 ,p_assignment_id => p_assignment_id
1222 ,p_asg_effective_start_date => p_asg_effective_start_date
1223 ,p_asg_effective_end_date => p_asg_effective_end_date
1224 ,p_business_group_id => p_business_group_id
1225 ,p_date_from => p_date_from
1226 ,p_date_to => p_date_to
1227 ,p_org_structure_id => p_org_structure_id
1228 ,p_org_structure_vers_id => p_org_structure_vers_id
1229 ,p_parent_org_id => p_parent_org_id
1230 ,p_single_org_id => p_single_org_id
1231 ,p_run_type => p_run_type
1232 ,p_inactivate_date => l_date
1233 );
1234
1235 EXCEPTION
1236 WHEN hr_api.cannot_find_prog_unit THEN
1237 hr_api.cannot_find_prog_unit_error
1238 (p_module_name => 'UPDATE_USER_ACCOUNT'
1239 ,p_hook_type => 'AP'
1240 );
1241 --
1242 -- End of API User Hook for the after hook of create_user_acct
1243 --
1244 end;
1245 --
1246 -- Set all output arguments, if any.
1247 --
1248 -- When in validation only mode raise the Validate_Enabled exception
1249 --
1250 IF p_validate
1251 THEN
1252 raise hr_api.validate_enabled;
1253 END IF;
1254 --
1255 hr_utility.set_location(' Leaving:'||l_proc, 70);
1256
1257 EXCEPTION
1258 WHEN hr_api.validate_enabled THEN
1259 --
1260 -- As the Validate_Enabled exception has been raised
1261 -- we must rollback to the savepoint
1262 --
1263 ROLLBACK TO update_user_acct;
1264 --
1265 -- Only set output warning arguments
1266 -- (Any key or derived arguments must be set to null
1267 -- when validation only mode is being used.)
1268 --
1269 --
1270 WHEN others THEN
1271 --
1272 -- A validation or unexpected error has occurred
1273 --
1274 --
1275 ROLLBACK TO update_user_acct;
1276 raise;
1277 --
1278 END update_user_acct;
1279 --
1280 --
1281 END hr_user_acct_api;
[Home] [Help]