[Home] [Help]
PACKAGE BODY: APPS.PER_SEC_PROFILE_ASG_API
Source
1 Package Body per_sec_profile_asg_api as
2 /* $Header: peaspapi.pkb 115.1 2003/09/16 01:13 vkonda noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' per_sec_profile_asg_api.';
7
8 PROCEDURE update_existing_assignment
9 (p_sec_profile_assignment_id IN NUMBER
10 ,p_object_version_number IN NUMBER
11 ,p_start_date IN DATE
12 ,p_end_date IN DATE
13 )
14 IS
15 --
16 l_object_version_number NUMBER;
17 --
18 BEGIN
19 --
20 l_object_version_number := p_object_version_number;
21 --
22 -- Call the api to update the previous assignment
23 --
24 per_asp_upd.upd
25 (p_sec_profile_assignment_id => p_sec_profile_assignment_id
26 ,p_start_date => p_start_date
27 ,p_end_date => p_end_date
28 ,p_object_version_number => l_object_version_number
29 );
30 END;
31
32 PROCEDURE insert_update
33 (p_sec_profile_assignment_id in number,
34 p_user_id in number,
35 p_security_group_id in number,
36 p_business_group_id in number,
37 p_security_profile_id in number,
38 p_responsibility_id in number,
39 p_responsibility_application_i in number,
40 p_start_date in date,
41 p_end_date in date default null,
42 p_object_version_number in number
43 )
44 IS
45 --
46 l_security_group_id NUMBER;
47 l_sec_profile_assignment_id NUMBER;
48 l_object_version_number NUMBER;
49 l_assignment_exists BOOLEAN;
50 --
51 l_clashing_start_date DATE;
52 l_clashing_end_date DATE;
53 l_clashing_id NUMBER;
54 l_clashing_ovn NUMBER;
55 --
56 l_exception EXCEPTION;
57 l_response NUMBER;
58 l_check_complete BOOLEAN := FALSE;
59 l_proc varchar2(72) := g_package|| 'insert_update';
60 --
61 BEGIN
62 --
63 -- Perform some checks on the date to be inserted/updated
64 --
65 --
66 hr_utility.set_location('Entering ' || l_proc ,5);
67 hr_utility.trace('Sec Prf Asg Id ' || p_sec_profile_assignment_id);
68 hr_utility.trace('p_start_date ' || p_start_date);
69 hr_utility.trace('p_end_date' || p_end_date);
70
71 l_security_group_id := p_security_group_id;
72 --
73 -- If we are inserting a record, or updating an entry on the form that
74 -- has been bought back from the view but is not a record in
75 -- per_sec_profile_assignments then make sure that the id and ovn are null.
76 --
77 l_sec_profile_assignment_id := p_sec_profile_assignment_id;
78 l_object_version_number := p_object_version_number;
79
80 --
81 -- Ok - lets check if the assignment exists..
82 --
83 l_assignment_exists :=
84 per_asp_bus.chk_assignment_exists
85 (p_user_id => p_user_id
86 ,p_responsibility_id => p_responsibility_id
87 ,p_application_id => p_responsibility_application_i
88 ,p_security_group_id => p_security_group_id
89 );
90 hr_utility.set_location('Entering ' || l_proc ,10);
91 --
92 -- Always do the duplicate assignment check even if we are updating
93 -- a record since it may overlap the assignment for a different
94 -- security profile!
95 --
96 --
97 -- Check that there are no other records for the same
98 -- U/R/G combination but for a different security
99 -- profile.
100 --
101 per_asp_bus.chk_duplicate_assignments
102 (p_user_id => p_user_id
103 ,p_responsibility_id => p_responsibility_id
104 ,p_application_id => p_responsibility_application_i
105 ,p_security_group_id => p_security_group_id
106 ,p_business_group_id => p_business_group_id
107 ,p_security_profile_id => p_security_profile_id
108 ,p_start_date => p_start_date
109 ,p_end_date => p_end_date
110 );
111 hr_utility.set_location('Entering ' || l_proc ,15);
112 --
113 -- So now lets check the dates for the assignment
114 --
115 per_asp_bus.chk_assignment_dates
116 (p_user_id => p_user_id
117 ,p_responsibility_id => p_responsibility_id
118 ,p_application_id => p_responsibility_application_i
119 ,p_security_group_id => p_security_group_id
120 ,p_start_date => p_start_date
121 ,p_end_date => p_end_date
122 );
123 hr_utility.set_location('Entering ' || l_proc ,20);
124 --
125 per_asp_bus.chk_invalid_dates
126 (p_sec_profile_assignment_id => p_sec_profile_assignment_id
127 ,p_user_id => p_user_id
128 ,p_responsibility_id => p_responsibility_id
129 ,p_application_id => p_responsibility_application_i
130 ,p_security_group_id => p_security_group_id
131 ,p_business_group_id => p_business_group_id
132 ,p_security_profile_id => p_security_profile_id
133 ,p_start_date => p_start_date
134 ,p_end_date => p_end_date
135 );
136 hr_utility.set_location('Entering ' || l_proc ,25);
137 --
138 l_check_complete := FALSE;
139 --
140 l_sec_profile_assignment_id := p_sec_profile_assignment_id;
141 WHILE NOT l_check_complete LOOP
142 --
143 l_clashing_id := NULL;
144 l_clashing_ovn := NULL;
145 l_clashing_start_date := NULL;
146 l_clashing_end_date := NULL;
147 --
148 hr_utility.set_location('Entering ' || l_proc ,30);
149 per_asp_bus.chk_overlapping_dates
150 (p_sec_profile_assignment_id => l_sec_profile_assignment_id
151 ,p_user_id => p_user_id
152 ,p_responsibility_id => p_responsibility_id
153 ,p_application_id => p_responsibility_application_i
154 ,p_security_group_id => l_security_group_id
155 ,p_business_group_id => p_business_group_id
156 ,p_security_profile_id => p_security_profile_id
157 ,p_start_date => p_start_date
158 ,p_end_date => p_end_date
159 ,p_clashing_id => l_clashing_id
160 ,p_clashing_ovn => l_clashing_ovn
161 ,p_clashing_start_date => l_clashing_start_date
162 ,p_clashing_end_date => l_clashing_end_date
163 );
164 hr_utility.set_location('Entering ' || l_proc ,35);
165 IF l_clashing_id IS NOT NULL THEN
166 --
167 IF p_start_date >= l_clashing_start_date
168 AND p_start_date <= NVL(l_clashing_end_date, hr_general.end_of_time)
169 THEN
170 --
171 -- A previous record exists which has either not been end-dated, or has
172 -- an end-date of more than the start date of this record.
173 --
174 -- Check if moving the end date of the earlier
175 -- record would break the start/end date constraint
176 --
177 IF l_clashing_start_date >= p_start_date - 1 THEN
178 hr_utility.set_message
179 (800
180 ,'PER_52549_ASP_START_DATE_ERR'
181 );
182 hr_utility.raise_error;
183 END IF;
184 --
185 -- Prompt the user if they want to change the end date of the earlier
186 -- record.
187 -- If no, then rollback all changes.. if yes then alter record and continue - we
188 -- may want to do the check again
189 --
190 hr_utility.set_location('Entering ' || l_proc ,40);
191 update_existing_assignment
192 (p_sec_profile_assignment_id => l_clashing_id
193 ,p_object_version_number => l_clashing_ovn
194 ,p_start_date => l_clashing_start_date
195 ,p_end_date => p_start_date - 1
196 );
197 hr_utility.set_location('Entering ' || l_proc ,45);
198 ELSIF NVL(p_end_date, hr_general.end_of_time) >= l_clashing_start_date
199 AND NVL(p_end_date, hr_general.end_of_time) <= NVL(l_clashing_end_date, hr_general.end_of_time)
200 THEN
201 --
202 -- A future record exists with a start date of less than the old date
203 -- (The id of the record is in l_id
204 --
205 -- Check if moving the end date of the earlier
206 -- record would break the start/end date constraint
207 --
208 IF p_end_date IS NULL
209 OR p_end_date + 1 >= NVL(l_clashing_end_date, hr_general.end_of_time)
210 THEN
211 hr_utility.set_message
212 (800
213 ,'PER_52550_ASP_END_DATE_ERR'
214 );
215 hr_utility.raise_error;
216 END IF;
217 --
218 -- prompt the user if they want to alter the end date of the future record...
219 -- If no, then rollback all changes.. if yes then alter record and continue - we
220 -- may want to do the check again
221 --
222 -- Update the existing row, and then redo the
223 -- validation check to make sure that the value
224 -- is ok.
225 --
226 hr_utility.set_location('Entering ' || l_proc ,50);
227 update_existing_assignment
228 (p_sec_profile_assignment_id => l_clashing_id
229 ,p_object_version_number => l_clashing_ovn
230 ,p_start_date => p_end_date + 1
231 ,p_end_date => l_clashing_end_date
232 );
233 hr_utility.set_location('Entering ' || l_proc ,55);
234 END IF;
235 ELSE
236 l_check_complete := TRUE;
237 END IF;
238 END LOOP;
239 --
240 hr_utility.set_location('Entering ' || l_proc ,60);
241 END insert_update;
242 --
243 -- ----------------------------------------------------------------------------
244 -- |---------------------< create_security_profile_asg >----------------------|
245 -- ----------------------------------------------------------------------------
246 --
247 procedure create_security_profile_asg
248 (p_validate in boolean default false,
249 p_sec_profile_assignment_id out nocopy number,
250 p_user_id in number,
251 p_security_group_id in number,
252 p_business_group_id in number,
253 p_security_profile_id in number,
254 p_responsibility_id in number,
255 p_responsibility_application_i in number,
256 p_start_date in date,
257 p_end_date in date default null,
258 p_object_version_number out nocopy number
259 ) is
260 --
261 -- Declare cursors and local variables
262 --
263 l_sec_profile_assignment_id number;
264 l_object_version_number number;
265 l_proc varchar2(72) := g_package||'create_security_profile_asg';
266 begin
267 hr_utility.set_location('Entering:'|| l_proc, 10);
268 --
269 -- Issue a savepoint
270 --
271 savepoint create_security_profile_asg;
272 /* --
273 -- Call Before Process User Hook
274 --
275 begin
276 per_sec_profile_asg_BK_1.create_security_profile_asg_b
277 (p_sec_profile_assignment_id => p_sec_profile_assignment_id,
278 p_user_id => p_user_id,
279 p_security_group_id => p_security_group_id,
280 p_business_group_id => p_business_group_id,
281 p_security_profile_id => p_security_profile_id,
282 p_responsibility_id => p_responsibility_id,
283 p_responsibility_application_i => p_responsibility_application_i,
284 p_start_date => p_start_date,
285 p_end_date => p_end_date,
286 p_object_version_number => p_object_version_number
287 );
288 exception
289 when hr_api.cannot_find_prog_unit then
290 hr_api.cannot_find_prog_unit_error
291 (p_module_name => 'create_security_profile_asg_b'
292 ,p_hook_type => 'BP'
293 );
294 end; */
295
296 insert_update
297 (p_sec_profile_assignment_id => p_sec_profile_assignment_id,
298 p_user_id => p_user_id,
299 p_security_group_id => p_security_group_id,
300 p_business_group_id => p_business_group_id,
301 p_security_profile_id => p_security_profile_id,
302 p_responsibility_id => p_responsibility_id,
303 p_responsibility_application_i => p_responsibility_application_i,
304 p_start_date => p_start_date,
305 p_end_date => p_end_date,
306 p_object_version_number => p_object_version_number
307 );
308 --
309 -- Process Logic
310 --
311 per_asp_ins.ins
312 (p_sec_profile_assignment_id => l_sec_profile_assignment_id,
313 p_user_id => p_user_id,
314 p_security_group_id => p_security_group_id,
315 p_business_group_id => p_business_group_id,
316 p_security_profile_id => p_security_profile_id,
317 p_responsibility_id => p_responsibility_id,
318 p_responsibility_application_i => p_responsibility_application_i,
319 p_start_date => p_start_date,
320 p_end_date => p_end_date,
321 p_object_version_number => l_object_version_number
322 );
323
324
325 /* --
326 -- Call After Process User Hook
327 --
328 begin
329 per_sec_profile_asg_BK_1.create_security_profile_asg_a
330 (p_sec_profile_assignment_id => p_sec_profile_assignment_id,
331 p_user_id => p_user_id,
332 p_security_group_id => p_security_group_id,
333 p_business_group_id => p_business_group_id,
334 p_security_profile_id => p_security_profile_id,
335 p_responsibility_id => p_responsibility_id,
336 p_responsibility_application_i => p_responsibility_application_i,
337 p_start_date => p_start_date,
338 p_end_date => p_end_date,
339 p_object_version_number => p_object_version_number
340 );
341 exception
342 when hr_api.cannot_find_prog_unit then
343 hr_api.cannot_find_prog_unit_error
344 (p_module_name => 'create_security_profile_asg_a'
345 ,p_hook_type => 'AP'
346 );
347 end; */
348 --
349 -- When in validation only mode raise the Validate_Enabled exception
350 --
351 if p_validate then
352 raise hr_api.validate_enabled;
353 end if;
354 --
355 -- Set all IN OUT and OUT parameters with out values
356 --
357 p_object_version_number := l_object_version_number;
358 p_sec_profile_assignment_id := l_sec_profile_assignment_id;
359 --
360 hr_utility.set_location(' Leaving:'||l_proc, 70);
361 exception
362 when hr_api.validate_enabled then
363 --
364 -- As the Validate_Enabled exception has been raised
365 -- we must rollback to the savepoint
366 --
367 rollback to create_security_profile_asg;
368 --
369 -- Reset IN OUT parameters and set OUT parameters
370 -- (Any key or derived arguments must be set to null
371 -- when validation only mode is being used.)
372 --
373 p_object_version_number := null;
374 p_sec_profile_assignment_id := null;
375
376 hr_utility.set_location(' Leaving:'||l_proc, 80);
377 when others then
378 --
379 -- A validation or unexpected error has occured
380 --
381 rollback to create_security_profile_asg;
382 --
383 -- Reset IN OUT parameters and set all
384 -- OUT parameters, including warnings, to null
385 --
386 p_object_version_number := null;
387 p_sec_profile_assignment_id := null;
388 hr_utility.set_location(' Leaving:'||l_proc, 90);
389 raise;
390 end create_security_profile_asg;
391
392 --
393 -- ----------------------------------------------------------------------------
394 -- |---------------------< update_security_profile_asg >----------------------|
395 -- ----------------------------------------------------------------------------
396 --
397 procedure update_security_profile_asg
398 (p_validate in boolean default false,
399 p_sec_profile_assignment_id in number,
400 p_user_id in number,
401 p_security_group_id in number,
402 p_business_group_id in number,
403 p_security_profile_id in number,
404 p_responsibility_id in number,
405 p_responsibility_application_i in number,
406 p_start_date in date,
407 p_end_date in date,
408 p_object_version_number in out nocopy number
409 ) is
410 --
411 -- Declare cursors and local variables
412 --
413 l_object_version_number number := p_object_version_number;
414 l_proc varchar2(72) := g_package||'update_security_profile_asg';
415 begin
416 --hr_utility.trace_on(null,'KKK');
417 hr_utility.set_location('Entering:'|| l_proc, 10);
418 --
419 -- Issue a savepoint
420 --
421 savepoint update_security_profile_asg;
422 /* --
423 -- Call Before Process User Hook
424 --
425 begin
426 per_sec_profile_asg_BK_1.update_security_profile_asg_b
427 (p_sec_profile_assignment_id =>p_sec_profile_assignment_id,
428 p_start_date => p_start_date,
429 p_end_date => p_end_date,
430 p_object_version_number => p_object_version_number
431 );
432 exception
433 when hr_api.cannot_find_prog_unit then
434 hr_api.cannot_find_prog_unit_error
435 (p_module_name => 'update_security_profile_asg_b'
436 ,p_hook_type => 'BP'
437 );
438 end;*/
439
440 insert_update
441 (p_sec_profile_assignment_id => p_sec_profile_assignment_id,
442 p_user_id => p_user_id,
443 p_security_group_id => p_security_group_id,
444 p_business_group_id => p_business_group_id,
445 p_security_profile_id => p_security_profile_id,
446 p_responsibility_id => p_responsibility_id,
447 p_responsibility_application_i => p_responsibility_application_i,
448 p_start_date => p_start_date,
449 p_end_date => p_end_date,
450 p_object_version_number => p_object_version_number
451 );
452
453 --
454 -- Process Logic
455 --
456 per_asp_upd.upd
457 (p_sec_profile_assignment_id => p_sec_profile_assignment_id,
458 p_start_date => p_start_date,
459 p_end_date => p_end_date,
460 p_object_version_number => l_object_version_number
461 );
462 /* --
463 -- Call After Process User Hook
464 --
465 begin
466 per_sec_profile_asg_BK_1.update_security_profile_asg_a
467 (p_sec_profile_assignment_id => p_sec_profile_assignment_id,
468 p_start_date => p_start_date,
469 p_end_date => p_end_date,
470 p_object_version_number => p_object_version_number
471 );
472 exception
473 when hr_api.cannot_find_prog_unit then
474 hr_api.cannot_find_prog_unit_error
475 (p_module_name => 'update_security_profile_asg_a'
476 ,p_hook_type => 'AP'
477 );
478 end; */
479 --
480 -- When in validation only mode raise the Validate_Enabled exception
481 --
482 if p_validate then
483 raise hr_api.validate_enabled;
484 end if;
485 --
486 -- Set all IN OUT and OUT parameters with out values
487 --
488 p_object_version_number := l_object_version_number;
489 --
490 hr_utility.set_location(' Leaving:'||l_proc, 70);
491 exception
492 when hr_api.validate_enabled then
493 --
494 -- As the Validate_Enabled exception has been raised
495 -- we must rollback to the savepoint
496 --
497 rollback to update_security_profile_asg;
498 hr_utility.set_location(' Leaving:'||l_proc, 80);
499 when others then
500 --
501 -- A validation or unexpected error has occured
502 --
503 rollback to update_security_profile_asg;
504 hr_utility.set_location(' Leaving:'||l_proc, 90);
505 raise;
506 end update_security_profile_asg;
507 --
508 end per_sec_profile_asg_api;