1 Package Body irc_iad_bus as
2 /* $Header: iriadrhi.pkb 120.3.12000000.2 2007/03/23 07:14:06 vboggava noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_iad_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_assignment_details_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_assignment_details_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id,
29 pbg.legislation_code
30 from per_business_groups_perf pbg
31 where pbg.business_group_id =
32 (select distinct asg.business_group_id
33 from irc_assignment_details_f iad
34 , per_all_assignments_f asg
35 where iad.assignment_details_id = p_assignment_details_id
36 and iad.assignment_id = asg.assignment_id);
37 --
38 -- Declare local variables
39 --
40 l_security_group_id number;
41 l_proc varchar2(72) := g_package||'set_security_group_id';
42 l_legislation_code varchar2(150);
43 --
44 begin
45 --
46 hr_utility.set_location('Entering:'|| l_proc, 10);
47 --
48 -- Ensure that all the mandatory parameter are not null
49 --
50 hr_api.mandatory_arg_error
51 (p_api_name => l_proc
52 ,p_argument => 'assignment_details_id'
53 ,p_argument_value => p_assignment_details_id
54 );
55 --
56 open csr_sec_grp;
57 fetch csr_sec_grp into l_security_group_id
58 , l_legislation_code;
59 --
60 if csr_sec_grp%notfound then
61 --
62 close csr_sec_grp;
63 --
64 -- The primary key is invalid therefore we must error
65 --
66 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
67 hr_multi_message.add
68 (p_associated_column1
69 => nvl(p_associated_column1,'ASSIGNMENT_DETAILS_ID')
70 );
71 --
72 else
73 close csr_sec_grp;
74 --
75 -- Set the security_group_id in CLIENT_INFO
76 --
77 hr_api.set_security_group_id
78 (p_security_group_id => l_security_group_id
79 );
80 --
81 -- Set the sessions legislation context in HR_SESSION_DATA
82 --
83 hr_api.set_legislation_context(l_legislation_code);
84 end if;
85 --
86 hr_utility.set_location(' Leaving:'|| l_proc, 20);
87 --
88 end set_security_group_id;
89 --
90 -- ---------------------------------------------------------------------------
91 -- |---------------------< return_legislation_code >-------------------------|
92 -- ---------------------------------------------------------------------------
93 --
94 Function return_legislation_code
95 (p_assignment_details_id in number
96 )
97 Return Varchar2 Is
98 --
99 -- Declare cursor
100 --
101 cursor csr_leg_code is
102 select pbg.legislation_code
103 from per_business_groups_perf pbg
104 where pbg.business_group_id =
105 (select distinct asg.business_group_id
106 from irc_assignment_details_f iad
107 , per_all_assignments_f asg
108 where iad.assignment_details_id = p_assignment_details_id
109 and iad.assignment_id = asg.assignment_id);
110 --
111 -- Declare local variables
112 --
113 l_legislation_code varchar2(150);
114 l_proc varchar2(72) := g_package||'return_legislation_code';
115 --
116 Begin
117 --
118 hr_utility.set_location('Entering:'|| l_proc, 10);
119 --
120 -- Ensure that all the mandatory parameter are not null
121 --
122 hr_api.mandatory_arg_error
123 (p_api_name => l_proc
124 ,p_argument => 'assignment_details_id'
125 ,p_argument_value => p_assignment_details_id
126 );
127 --
128 if ( nvl(irc_iad_bus.g_assignment_details_id, hr_api.g_number)
129 = p_assignment_details_id) then
130 --
131 -- The legislation code has already been found with a previous
132 -- call to this function. Just return the value in the global
133 -- variable.
134 --
135 l_legislation_code := irc_iad_bus.g_legislation_code;
136 hr_utility.set_location(l_proc, 20);
137 else
138 --
139 -- The ID is different to the last call to this function
140 -- or this is the first call to this function.
141 --
142 open csr_leg_code;
143 fetch csr_leg_code into l_legislation_code;
144 --
145 if csr_leg_code%notfound then
146 --
147 -- The primary key is invalid therefore we must error
148 --
149 close csr_leg_code;
150 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
151 fnd_message.raise_error;
152 end if;
153 hr_utility.set_location(l_proc,30);
154 --
155 -- Set the global variables so the values are
156 -- available for the next call to this function.
157 --
158 close csr_leg_code;
159 irc_iad_bus.g_assignment_details_id := p_assignment_details_id;
160 irc_iad_bus.g_legislation_code := l_legislation_code;
161 end if;
162 hr_utility.set_location(' Leaving:'|| l_proc, 40);
163 return l_legislation_code;
164 end return_legislation_code;
165 --
166 -- ----------------------------------------------------------------------------
167 -- |-----------------------< chk_non_updateable_args >------------------------|
168 -- ----------------------------------------------------------------------------
169 -- {Start Of Comments}
170 --
171 -- Description:
172 -- This procedure is used to ensure that non updateable attributes have
173 -- not been updated. If an attribute has been updated an error is generated.
174 --
175 -- Pre Conditions:
176 -- g_old_rec has been populated with details of the values currently in
177 -- the database.
178 --
179 -- In Arguments:
180 -- p_rec has been populated with the updated values the user would like the
181 -- record set to.
182 --
183 -- Post Success:
184 -- Processing continues if all the non updateable attributes have not
185 -- changed.
186 --
187 -- Post Failure:
188 -- An application error is raised if any of the non updatable attributes
189 -- have been altered.
190 --
191 -- {End Of Comments}
192 -- ----------------------------------------------------------------------------
193 Procedure chk_non_updateable_args
194 (p_effective_date in date
195 ,p_rec in irc_iad_shd.g_rec_type
196 ) IS
197 --
198 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
199 --
200 Begin
201 --
202 -- Only proceed with the validation if a row exists for the current
203 -- record in the HR Schema.
204 --
205 IF NOT irc_iad_shd.api_updating
206 (p_assignment_details_id => p_rec.assignment_details_id
207 ,p_effective_date => p_effective_date
208 ,p_object_version_number => p_rec.object_version_number
209 ) THEN
210 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
211 fnd_message.set_token('PROCEDURE ', l_proc);
212 fnd_message.set_token('STEP ', '5');
213 fnd_message.raise_error;
214 END IF;
215 --
216 -- Ensure non-updateable args have not been updated.
217 --
218 if p_rec.assignment_id <> irc_iad_shd.g_old_rec.assignment_id
219 then
220 hr_api.argument_changed_error
221 (p_api_name => l_proc
222 ,p_argument => 'assignment_id'
223 ,p_base_table => irc_iad_shd.g_tab_nam
224 );
225 end if;
226 --
227 if p_rec.details_version<> irc_iad_shd.g_old_rec.details_version
228 then
229 hr_api.argument_changed_error
230 (p_api_name => l_proc
231 ,p_argument => 'details_version'
232 ,p_base_table => irc_iad_shd.g_tab_nam
233 );
234 end if;
235 --
236 if p_rec.latest_details <> irc_iad_shd.g_old_rec.latest_details
237 then
238 hr_api.argument_changed_error
239 (p_api_name => l_proc
240 ,p_argument => 'latest_details'
241 ,p_base_table => irc_iad_shd.g_tab_nam
242 );
243 end if;
244 --
245 End chk_non_updateable_args;
246 --
247 -- ----------------------------------------------------------------------------
248 -- |--------------------------< dt_update_validate >--------------------------|
249 -- ----------------------------------------------------------------------------
250 -- {Start Of Comments}
251 --
252 -- Description:
253 -- This procedure is used for referential integrity of datetracked
254 -- parent entities when a datetrack update operation is taking place
255 -- and where there is no cascading of update defined for this entity.
256 --
257 -- Prerequisites:
258 -- This procedure is called from the update_validate.
259 --
260 -- In Parameters:
261 --
262 -- Post Success:
263 -- Processing continues.
264 --
265 -- Post Failure:
266 --
267 -- Developer Implementation Notes:
268 -- This procedure should not need maintenance unless the HR Schema model
269 -- changes.
270 --
271 -- Access Status:
272 -- Internal Row Handler Use Only.
273 --
274 -- {End Of Comments}
275 -- ----------------------------------------------------------------------------
276 Procedure dt_update_validate
277 (p_assignment_id in number default hr_api.g_number
278 ,p_datetrack_mode in varchar2
279 ,p_validation_start_date in date
280 ,p_validation_end_date in date
281 ) Is
282 --
283 l_proc varchar2(72) := g_package||'dt_update_validate';
284 --
285 Begin
286 --
287 -- Ensure that the p_datetrack_mode argument is not null
288 --
289 hr_api.mandatory_arg_error
290 (p_api_name => l_proc
291 ,p_argument => 'datetrack_mode'
292 ,p_argument_value => p_datetrack_mode
293 );
294 --
295 -- Mode will be valid, as this is checked at the start of the upd.
296 --
297 -- Ensure the arguments are not null
298 --
299 hr_api.mandatory_arg_error
300 (p_api_name => l_proc
301 ,p_argument => 'validation_start_date'
302 ,p_argument_value => p_validation_start_date
303 );
304 --
305 hr_api.mandatory_arg_error
306 (p_api_name => l_proc
307 ,p_argument => 'validation_end_date'
308 ,p_argument_value => p_validation_end_date
309 );
310 --
311 If ((nvl(p_assignment_id, hr_api.g_number) <> hr_api.g_number) and
312 NOT (dt_api.check_min_max_dates
313 (p_base_table_name => 'per_all_assignments_f'
314 ,p_base_key_column => 'ASSIGNMENT_ID'
315 ,p_base_key_value => p_assignment_id
316 ,p_from_date => p_validation_start_date
317 ,p_to_date => p_validation_end_date))) Then
318 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
319 fnd_message.set_token('TABLE_NAME','all assignments');
320 hr_multi_message.add
321 (p_associated_column1 => irc_iad_shd.g_tab_nam || '.ASSIGNMENT_ID');
322 End If;
323 --
324 Exception
325 When Others Then
326 --
327 -- An unhandled or unexpected error has occurred which
328 -- we must report
329 --
330 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
331 fnd_message.set_token('PROCEDURE', l_proc);
332 fnd_message.set_token('STEP','15');
333 fnd_message.raise_error;
334 End dt_update_validate;
335 --
336 -- ----------------------------------------------------------------------------
337 -- |--------------------------< dt_delete_validate >--------------------------|
338 -- ----------------------------------------------------------------------------
339 -- {Start Of Comments}
340 --
341 -- Description:
342 -- This procedure is used for referential integrity of datetracked
343 -- child entities when either a datetrack DELETE or ZAP is in operation
344 -- and where there is no cascading of delete defined for this entity.
345 -- For the datetrack mode of DELETE or ZAP we must ensure that no
346 -- datetracked child rows exist between the validation start and end
347 -- dates.
348 --
349 -- Prerequisites:
350 -- This procedure is called from the delete_validate.
351 --
352 -- In Parameters:
353 --
354 -- Post Success:
355 -- Processing continues.
356 --
357 -- Post Failure:
358 -- If a row exists by determining the returning Boolean value from the
359 -- generic dt_api.rows_exist function then we must supply an error via
360 -- the use of the local exception handler l_rows_exist.
361 --
362 -- Developer Implementation Notes:
363 -- This procedure should not need maintenance unless the HR Schema model
364 -- changes.
365 --
366 -- Access Status:
367 -- Internal Row Handler Use Only.
368 --
369 -- {End Of Comments}
370 -- ----------------------------------------------------------------------------
371 Procedure dt_delete_validate
372 (p_assignment_details_id in number
373 ,p_datetrack_mode in varchar2
374 ,p_validation_start_date in date
375 ,p_validation_end_date in date
376 ) Is
377 --
378 l_proc varchar2(72) := g_package||'dt_delete_validate';
379 --
380 Begin
381 --
382 -- Ensure that the p_datetrack_mode argument is not null
383 --
384 hr_api.mandatory_arg_error
385 (p_api_name => l_proc
386 ,p_argument => 'datetrack_mode'
387 ,p_argument_value => p_datetrack_mode
388 );
389 --
390 -- Only perform the validation if the datetrack mode is either
391 -- DELETE or ZAP
392 --
393 If (p_datetrack_mode = hr_api.g_delete or
394 p_datetrack_mode = hr_api.g_zap) then
395 --
396 --
397 -- Ensure the arguments are not null
398 --
399 hr_api.mandatory_arg_error
400 (p_api_name => l_proc
401 ,p_argument => 'validation_start_date'
402 ,p_argument_value => p_validation_start_date
403 );
404 --
405 hr_api.mandatory_arg_error
406 (p_api_name => l_proc
407 ,p_argument => 'validation_end_date'
408 ,p_argument_value => p_validation_end_date
409 );
410 --
411 hr_api.mandatory_arg_error
412 (p_api_name => l_proc
413 ,p_argument => 'assignment_details_id'
414 ,p_argument_value => p_assignment_details_id
415 );
416 --
417 --
418 --
419 End If;
420 --
421 Exception
422 When Others Then
423 --
424 -- An unhandled or unexpected error has occurred which
425 -- we must report
426 --
430 fnd_message.raise_error;
427 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
428 fnd_message.set_token('PROCEDURE', l_proc);
429 fnd_message.set_token('STEP','15');
431 --
432 End dt_delete_validate;
433 --
434 -- ----------------------------------------------------------------------------
435 -- |---------------------------< chk_attempt_id >-----------------------------|
436 -- ----------------------------------------------------------------------------
437 --
438 -- Description:
439 -- Verifies that the attempt id exists in OTA_ATTEMPTS and is being provided
440 -- for applicant assignment type only.
441 --
442 -- Prerequisites:
443 -- Must be called as the first step in insert_validate.
444 --
445 -- In Arguments:
446 -- p_attempt_id
447 --
448 -- Post Success:
449 -- If attempt_id exists in OTA_ATTEMPTS and the assignment is of type
450 -- application assignment, then continue.
451 --
452 -- Post Failure:
453 -- If the attempt_id does not exists in OTA_ATTEMPTS or if the assignment
454 -- type is not application assignment, then throw an error indicating
455 -- the same.
456 --
457 -- Access Status:
458 -- Internal Row Handler Use Only.
459 --
460 -- ----------------------------------------------------------------------------
461 procedure chk_attempt_id
462 (p_attempt_id in number
463 ,p_assignment_details_id in
464 irc_assignment_details_f.assignment_details_id%TYPE
465 ,p_assignment_id in irc_assignment_details_f.assignment_id%TYPE
466 ,p_effective_date in date
467 ,p_object_version_number in
468 irc_assignment_details_f.object_version_number%TYPE
469 )
470 IS
471 --
472 l_proc varchar2(72) := g_package||'chk_attempt_id';
473 l_api_updating boolean;
474 l_dummy varchar2(1);
475 l_assignment_type per_all_assignments_f.assignment_type%TYPE;
476 --
477 cursor csr_assignment_type(p_assignment_id number, p_effective_date date) is
478 select assignment_type
479 from per_all_assignments_f
480 where assignment_id = p_assignment_id and
481 p_effective_date between effective_start_date and effective_end_date;
482 --
483 cursor attempt_exists(p_attempt_id number) is
484 select null
485 from ota_attempts
486 where attempt_id = p_attempt_id;
487 --
488 Begin
489 --
490 hr_utility.set_location('Entering:'|| l_proc, 10);
491 --
492 l_api_updating := irc_iad_shd.api_updating
493 (p_assignment_details_id => p_assignment_details_id
494 ,p_effective_date => p_effective_date
495 ,p_object_version_number => p_object_version_number
496 );
497 --
498 if ((l_api_updating and
499 nvl(irc_iad_shd.g_old_rec.attempt_id, hr_api.g_number) <>
500 nvl(p_attempt_id, hr_api.g_number)) or
501 (NOT l_api_updating)) then
502 hr_utility.set_location(l_proc, 20);
503 --
504 -- Check if attempt_id is not null
505 --
506 if p_attempt_id IS NOT NULL then
507 --
508 -- attempt_id must exist in ota_attempts
509 --
510 open attempt_exists(p_attempt_id);
511 fetch attempt_exists into l_dummy;
512 --
513 if attempt_exists%notfound then
514 close attempt_exists;
515 hr_utility.set_location(l_proc, 30);
516 fnd_message.set_name('PER', 'IRC_412233_INV_OTA_ATTEMPT');
517 fnd_message.raise_error;
518 else
519 close attempt_exists;
520 end if;
521 --
522 -- Check that when inserting, the assignment is an applicant assignment
523 --
524 open csr_assignment_type(p_assignment_id, p_effective_date);
525 fetch csr_assignment_type into l_assignment_type;
526 close csr_assignment_type;
527 --
528 if l_assignment_type in ('E','C','B','O') then
529 hr_utility.set_location(l_proc, 40);
530 --
531 -- Check if the assignment is being updated
532 --
533 if l_api_updating then
534 --
535 -- non applicant, attempt_id can only be updated to null
536 --
537 fnd_message.set_name('PER', 'IRC_412235_OTA_ATTEMPT_INV_UPD');
538 fnd_message.raise_error;
539 else -- inserting a non applicant
540 fnd_message.set_name('PER', 'IRC_412234_OTA_ATTEMPT_ASG');
541 fnd_message.raise_error;
542 end if;
543 end if;
544 end if;
545 end if;
546 hr_utility.set_location('Leaving: '||l_proc, 50);
547 exception
548 when app_exception.application_exception then
549 if hr_multi_message.exception_add
550 (p_associated_column1 => 'IRC_ASSIGNMENT_DETAILS_F.ATTEMPT_ID'
551 ) then
552 raise;
553 end if;
554 end chk_attempt_id;
555 -- ----------------------------------------------------------------------------
556 -- |---------------------------< insert_validate >----------------------------|
557 -- ----------------------------------------------------------------------------
558 Procedure insert_validate
559 (p_rec in irc_iad_shd.g_rec_type
560 ,p_effective_date in date
561 ,p_datetrack_mode in varchar2
562 ,p_validation_start_date in date
563 ,p_validation_end_date in date
564 ) is
565 --
566 l_proc varchar2(72) := g_package||'insert_validate';
567 --
568 Begin
569 hr_utility.set_location('Entering:'||l_proc, 5);
570 --
571 -- Call all supporting business operations
572 --
573 -- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS
574 --
575 -- Validate Dependent Attributes
576 --
577 chk_attempt_id
578 (p_attempt_id => p_rec.attempt_id
579 ,p_assignment_details_id => p_rec.assignment_details_id
580 ,p_assignment_id => p_rec.assignment_id
581 ,p_effective_date => p_effective_date
582 ,p_object_version_number => p_rec.object_version_number
583 );
584 --
585 hr_utility.set_location(' Leaving:'||l_proc, 10);
586 End insert_validate;
587 --
588 -- ----------------------------------------------------------------------------
589 -- |---------------------------< update_validate >----------------------------|
590 -- ----------------------------------------------------------------------------
591 Procedure update_validate
592 (p_rec in irc_iad_shd.g_rec_type
593 ,p_effective_date in date
594 ,p_datetrack_mode in varchar2
595 ,p_validation_start_date in date
596 ,p_validation_end_date in date
597 ) is
598 --
599 l_proc varchar2(72) := g_package||'update_validate';
600 --
601 Begin
602 hr_utility.set_location('Entering:'||l_proc, 5);
603 --
604 -- Call all supporting business operations
605 --
606 -- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS
607 --
608 -- Validate Dependent Attributes
609 --
610 -- Call the datetrack update integrity operation
611 --
612 dt_update_validate
613 (p_assignment_id => p_rec.assignment_id
614 ,p_datetrack_mode => p_datetrack_mode
615 ,p_validation_start_date => p_validation_start_date
616 ,p_validation_end_date => p_validation_end_date
617 );
618 --
619 hr_utility.set_location(' Leaving:'||l_proc, 10);
620 --
621 chk_non_updateable_args
622 (p_effective_date => p_effective_date
623 ,p_rec => p_rec
624 );
625 --
626 hr_utility.set_location(' Leaving:'||l_proc, 20);
627 --
628 chk_attempt_id
629 (p_attempt_id => p_rec.attempt_id
630 ,p_assignment_details_id => p_rec.assignment_details_id
631 ,p_assignment_id => p_rec.assignment_id
632 ,p_effective_date => p_effective_date
633 ,p_object_version_number => p_rec.object_version_number
634 );
635 --
636 hr_utility.set_location(' Leaving:'||l_proc, 30);
637 End update_validate;
638 --
639 -- ----------------------------------------------------------------------------
640 -- |---------------------------< delete_validate >----------------------------|
641 -- ----------------------------------------------------------------------------
642 Procedure delete_validate
643 (p_rec in irc_iad_shd.g_rec_type
644 ,p_effective_date in date
645 ,p_datetrack_mode in varchar2
646 ,p_validation_start_date in date
647 ,p_validation_end_date in date
648 ) is
649 --
650 l_proc varchar2(72) := g_package||'delete_validate';
651 --
652 Begin
653 hr_utility.set_location('Entering:'||l_proc, 5);
654 --
655 -- Call all supporting business operations
656 --
657 dt_delete_validate
658 (p_datetrack_mode => p_datetrack_mode
659 ,p_validation_start_date => p_validation_start_date
660 ,p_validation_end_date => p_validation_end_date
661 ,p_assignment_details_id => p_rec.assignment_details_id
662 );
663 --
664 hr_utility.set_location(' Leaving:'||l_proc, 10);
665 End delete_validate;
666 --
667 end irc_iad_bus;
[Home] [Help]