[Home] [Help]
PACKAGE BODY: APPS.BEN_PGR_BUS
Source
1 Package Body ben_pgr_bus as
2 /* $Header: bepgrrhi.pkb 120.0.12010000.2 2008/08/05 15:12:41 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_pgr_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_ppl_grp_rt_id >------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- ppl_grp_rt_id PK of record being inserted or updated.
24 -- effective_date Effective Date of session
25 -- object_version_number Object version number of record being
26 -- inserted or updated.
27 --
28 -- Post Success
29 -- Processing continues
30 --
31 -- Post Failure
32 -- Errors handled by the procedure
33 --
34 -- Access Status
35 -- Internal table handler use only.
36 --
37 Procedure chk_ppl_grp_rt_id(p_ppl_grp_rt_id in number,
38 p_effective_date in date,
39 p_object_version_number in number) is
40 --
41 l_proc varchar2(72) := g_package||'chk_ppl_grp_rt_id';
42 l_api_updating boolean;
43 --
44 Begin
45 --
46 hr_utility.set_location('Entering:'||l_proc, 5);
47 --
48 l_api_updating := ben_pgr_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_ppl_grp_rt_id => p_ppl_grp_rt_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_ppl_grp_rt_id,hr_api.g_number)
55 <> ben_pgr_shd.g_old_rec.ppl_grp_rt_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_pgr_shd.constraint_error('BEN_PPL_GRP_RT_F_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_ppl_grp_rt_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_pgr_shd.constraint_error('BEN_PPL_GRP_RT_F_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_ppl_grp_rt_id;
78 -- ----------------------------------------------------------------------------
79 -- |------< chk_dup_record >------|
80 -- ----------------------------------------------------------------------------
81 --
82 -- Description
83 -- This procedure is used to check that there is no duplicate record
84 --
85 -- Pre Conditions
86 -- None.
87 --
88 -- In Parameters
89 -- p_ppl_grp_rt_id PK of record being inserted or updated.
90 -- p_people_group_id Value of FK
91 -- p_vrbl_rt_prfl_id FK of the record
92 -- p_effective_date effective date
93 -- p_object_version_number Object version number of record being
94 -- inserted or updated.
95 -- p_business_group_id business_group_id of the record
96 -- p_validation_start_date validation_start_date of record
97 -- p_validation_end_date validation_end_date of record
98 --
99 -- Post Success
100 -- Processing continues
101 --
102 -- Post Failure
103 -- Error handled by procedure
104 --
105 -- Access Status
106 -- Internal table handler use only.
107 --
108 Procedure chk_dup_record
109 (p_ppl_grp_rt_id in number,
110 p_people_group_id in number,
111 p_vrbl_rt_prfl_id in number,
112 p_effective_date in date,
113 p_object_version_number in number,
114 p_business_group_id in number,
115 p_validation_start_date in date,
116 p_validation_end_date in date )
117 is
118 --
119 l_proc varchar2(72) := g_package||'chk_dup_record';
120 l_api_updating boolean;
121 l_exists varchar2(1);
122 --
123 cursor c_dup is
124 select null
125 from ben_ppl_grp_rt_f
126 where people_group_id = p_people_group_id
127 and vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
128 and ppl_grp_rt_id <> nvl(p_ppl_grp_rt_id,hr_api.g_number)
129 and business_group_id + 0 = p_business_group_id
130 and p_validation_start_date <= effective_end_date
131 and p_validation_end_date >= effective_start_date;
132 --
133 BEGIN
134 --
135 hr_utility.set_location('Entering:'||l_proc, 5);
136 --
137 l_api_updating := ben_pgr_shd.api_updating
138 (p_ppl_grp_rt_id => p_ppl_grp_rt_id,
139 p_effective_date => p_effective_date,
140 p_object_version_number => p_object_version_number);
141 --
142 if (l_api_updating
143 and p_people_group_id <> nvl(ben_pgr_shd.g_old_rec.people_group_id,hr_api.g_number)
144 or not l_api_updating) then
145
146 open c_dup;
147 fetch c_dup into l_exists;
148 if c_dup%found then
149 close c_dup;
150 --
151 -- raise error as this People Group criteria already exists for this profile
152 --
153 fnd_message.set_name('BEN', 'BEN_92992_DUPS_ROW');
154 fnd_message.set_token('VAR1','People Group criteria',TRUE);
155 fnd_message.set_token('VAR2','Variable Rate Profile',TRUE);
156 fnd_message.raise_error;
157 --
158 end if;
159 close c_dup;
160 --
161 end if;
162 hr_utility.set_location('Leaving:'||l_proc,10);
163 --
164 END chk_dup_record;
165 --
166 -- |--------------------< chk_duplicate_ordr_num >----------------------------|
167 -- ----------------------------------------------------------------------------
168 --
169 -- Description
170 --
171 -- Pre Conditions
172 -- None.
173 --
174 -- In Parameters
175 -- p_vrbl_rt_prfl_id
176 -- p_ordr_num
177 -- p_effective_date
178 -- p_business_group_id
179 --
180 -- Post Success
181 -- Processing continues
182 --
183 -- Post Failure
184 -- Errors handled by the procedure
185 --
186 -- Access Status
187 -- Internal table handler use only.
188 --
189 -- ----------------------------------------------------------------------------
190 procedure chk_duplicate_ordr_num
191 (p_vrbl_rt_prfl_id in number
192 ,p_ordr_num in number
193 ,p_effective_date in date
194 ,p_business_group_id in number
195 ,p_ppl_grp_rt_id in number)
196 is
197 l_proc varchar2(72) := g_package||' chk_duplicate_ordr_num ';
198 l_dummy char(1);
199 cursor c1 is select null
200 from ben_ppl_grp_rt_f
201 where vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
202 and ppl_grp_rt_id <> nvl(p_ppl_grp_rt_id,-1)
203 and p_effective_date between effective_start_date
204 and effective_end_date
205 and business_group_id + 0 = p_business_group_id
206 and ordr_num = p_ordr_num;
207 --
208 Begin
209 hr_utility.set_location('Entering:'||l_proc, 5);
210
211 --
212 open c1;
213 fetch c1 into l_dummy;
214 --
215 if c1%found then
216 fnd_message.set_name('BEN','BEN_91001_SEQ_NOT_UNIQUE');
217 fnd_message.raise_error;
218 end if;
219 close c1;
220 --
221 hr_utility.set_location('Leaving:'||l_proc, 15);
222 End chk_duplicate_ordr_num;
223
224 --
225 -- ----------------------------------------------------------------------------
226 -- |------< chk_excld_flag >------|
227 -- ----------------------------------------------------------------------------
228 --
229 -- Description
230 -- This procedure is used to check that the lookup value is valid.
231 --
232 -- Pre Conditions
233 -- None.
234 --
235 -- In Parameters
236 -- ppl_grp_rt_id PK of record being inserted or updated.
237 -- excld_flag Value of lookup code.
238 -- effective_date effective date
239 -- object_version_number Object version number of record being
240 -- inserted or updated.
241 --
242 -- Post Success
243 -- Processing continues
244 --
245 -- Post Failure
246 -- Error handled by procedure
247 --
248 -- Access Status
249 -- Internal table handler use only.
250 --
251 Procedure chk_excld_flag(p_ppl_grp_rt_id in number,
252 p_excld_flag in varchar2,
253 p_effective_date in date,
254 p_object_version_number in number) is
255 --
256 l_proc varchar2(72) := g_package||'chk_excld_flag';
257 l_api_updating boolean;
258 --
259 Begin
260 --
261 hr_utility.set_location('Entering:'||l_proc, 5);
262 --
263 l_api_updating := ben_pgr_shd.api_updating
264 (p_ppl_grp_rt_id => p_ppl_grp_rt_id,
265 p_effective_date => p_effective_date,
266 p_object_version_number => p_object_version_number);
267 --
268 if (l_api_updating
269 and p_excld_flag
270 <> nvl(ben_pgr_shd.g_old_rec.excld_flag,hr_api.g_varchar2)
271 or not l_api_updating) then
272 --
273 -- check if value of lookup falls within lookup type.
274 --
275 --
276 if hr_api.not_exists_in_hr_lookups
277 (p_lookup_type => 'YES_NO',
278 p_lookup_code => p_excld_flag,
279 p_effective_date => p_effective_date) then
280 --
281 -- raise error as does not exist as lookup
282 --
283 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
284 hr_utility.raise_error;
285 --
286 end if;
287 --
288 end if;
289 --
290 hr_utility.set_location('Leaving:'||l_proc,10);
291 --
292 end chk_excld_flag;
293 --
294 -- ----------------------------------------------------------------------------
295 -- |--------------------------< dt_update_validate >--------------------------|
296 -- ----------------------------------------------------------------------------
297 -- {Start Of Comments}
298 --
299 -- Description:
300 -- This procedure is used for referential integrity of datetracked
301 -- parent entities when a datetrack update operation is taking place
302 -- and where there is no cascading of update defined for this entity.
303 --
304 -- Prerequisites:
305 -- This procedure is called from the update_validate.
306 --
307 -- In Parameters:
308 --
309 -- Post Success:
310 -- Processing continues.
311 --
312 -- Post Failure:
313 --
314 -- Developer Implementation Notes:
315 -- This procedure should not need maintenance unless the HR Schema model
316 -- changes.
317 --
318 -- Access Status:
319 -- Internal Row Handler Use Only.
320 --
321 -- {End Of Comments}
322 -- ----------------------------------------------------------------------------
323 Procedure dt_update_validate
324 (p_vrbl_rt_prfl_id in number default hr_api.g_number,
325 p_datetrack_mode in varchar2,
326 p_validation_start_date in date,
327 p_validation_end_date in date) Is
328 --
329 l_proc varchar2(72) := g_package||'dt_update_validate';
330 l_integrity_error Exception;
331 l_table_name all_tables.table_name%TYPE;
332 --
333 Begin
334 hr_utility.set_location('Entering:'||l_proc, 5);
335 --
336 -- Ensure that the p_datetrack_mode argument is not null
337 --
338 hr_api.mandatory_arg_error
339 (p_api_name => l_proc,
340 p_argument => 'datetrack_mode',
341 p_argument_value => p_datetrack_mode);
342 --
343 -- Only perform the validation if the datetrack update mode is valid
344 --
345 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
346 --
347 --
348 -- Ensure the arguments are not null
349 --
350 hr_api.mandatory_arg_error
351 (p_api_name => l_proc,
352 p_argument => 'validation_start_date',
353 p_argument_value => p_validation_start_date);
354 --
355 hr_api.mandatory_arg_error
356 (p_api_name => l_proc,
357 p_argument => 'validation_end_date',
358 p_argument_value => p_validation_end_date);
359 --
360 If ((nvl(p_vrbl_rt_prfl_id, hr_api.g_number) <> hr_api.g_number) and
361 NOT (dt_api.check_min_max_dates
362 (p_base_table_name => 'ben_vrbl_rt_prfl_f',
363 p_base_key_column => 'vrbl_rt_prfl_id',
364 p_base_key_value => p_vrbl_rt_prfl_id,
365 p_from_date => p_validation_start_date,
366 p_to_date => p_validation_end_date))) Then
367 l_table_name := 'ben_vrbl_rt_prfl_f';
368 Raise l_integrity_error;
369 End If;
370 --
371 End If;
372 --
373 hr_utility.set_location(' Leaving:'||l_proc, 10);
374 Exception
375 When l_integrity_error Then
376 --
377 -- A referential integrity check was violated therefore
378 -- we must error
379 --
380 hr_utility.set_message(801, 'HR_7216_DT_UPD_INTEGRITY_ERR');
381 hr_utility.set_message_token('TABLE_NAME', l_table_name);
382 hr_utility.raise_error;
383 When Others Then
384 --
385 -- An unhandled or unexpected error has occurred which
386 -- we must report
387 --
388 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
389 hr_utility.set_message_token('PROCEDURE', l_proc);
390 hr_utility.set_message_token('STEP','15');
391 hr_utility.raise_error;
392 End dt_update_validate;
393 --
394 -- ----------------------------------------------------------------------------
395 -- |--------------------------< dt_delete_validate >--------------------------|
396 -- ----------------------------------------------------------------------------
397 -- {Start Of Comments}
398 --
399 -- Description:
400 -- This procedure is used for referential integrity of datetracked
401 -- child entities when either a datetrack DELETE or ZAP is in operation
402 -- and where there is no cascading of delete defined for this entity.
403 -- For the datetrack mode of DELETE or ZAP we must ensure that no
404 -- datetracked child rows exist between the validation start and end
405 -- dates.
406 --
407 -- Prerequisites:
408 -- This procedure is called from the delete_validate.
409 --
410 -- In Parameters:
411 --
412 -- Post Success:
413 -- Processing continues.
414 --
415 -- Post Failure:
416 -- If a row exists by determining the returning Boolean value from the
417 -- generic dt_api.rows_exist function then we must supply an error via
418 -- the use of the local exception handler l_rows_exist.
419 --
420 -- Developer Implementation Notes:
421 -- This procedure should not need maintenance unless the HR Schema model
422 -- changes.
423 --
424 -- Access Status:
425 -- Internal Row Handler Use Only.
426 --
427 -- {End Of Comments}
428 -- ----------------------------------------------------------------------------
432 p_validation_start_date in date,
429 Procedure dt_delete_validate
430 (p_ppl_grp_rt_id in number,
431 p_datetrack_mode in varchar2,
433 p_validation_end_date in date) Is
434 --
435 l_proc varchar2(72) := g_package||'dt_delete_validate';
436 l_rows_exist Exception;
437 l_table_name all_tables.table_name%TYPE;
438 --
439 Begin
440 hr_utility.set_location('Entering:'||l_proc, 5);
441 --
442 -- Ensure that the p_datetrack_mode argument is not null
443 --
444 hr_api.mandatory_arg_error
445 (p_api_name => l_proc,
446 p_argument => 'datetrack_mode',
447 p_argument_value => p_datetrack_mode);
448 --
449 -- Only perform the validation if the datetrack mode is either
450 -- DELETE or ZAP
451 --
452 If (p_datetrack_mode = 'DELETE' or
453 p_datetrack_mode = 'ZAP') then
454 --
455 --
456 -- Ensure the arguments are not null
457 --
458 hr_api.mandatory_arg_error
459 (p_api_name => l_proc,
460 p_argument => 'validation_start_date',
461 p_argument_value => p_validation_start_date);
462 --
463 hr_api.mandatory_arg_error
464 (p_api_name => l_proc,
465 p_argument => 'validation_end_date',
466 p_argument_value => p_validation_end_date);
467 --
468 hr_api.mandatory_arg_error
469 (p_api_name => l_proc,
470 p_argument => 'ppl_grp_rt_id',
471 p_argument_value => p_ppl_grp_rt_id);
472 --
473 --
474 --
475 End If;
476 --
477 hr_utility.set_location(' Leaving:'||l_proc, 10);
478 Exception
479 When l_rows_exist Then
480 --
481 -- A referential integrity check was violated therefore
482 -- we must error
483 --
484 hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
485 hr_utility.set_message_token('TABLE_NAME', l_table_name);
486 hr_utility.raise_error;
487 When Others Then
488 --
489 -- An unhandled or unexpected error has occurred which
490 -- we must report
491 --
492 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
493 hr_utility.set_message_token('PROCEDURE', l_proc);
494 hr_utility.set_message_token('STEP','15');
495 hr_utility.raise_error;
496 End dt_delete_validate;
497 --
498 -- ----------------------------------------------------------------------------
499 -- |---------------------------< insert_validate >----------------------------|
500 -- ----------------------------------------------------------------------------
501 Procedure insert_validate
502 (p_rec in ben_pgr_shd.g_rec_type,
503 p_effective_date in date,
504 p_datetrack_mode in varchar2,
505 p_validation_start_date in date,
506 p_validation_end_date in date) is
507 --
508 l_proc varchar2(72) := g_package||'insert_validate';
509 --
510 Begin
511 hr_utility.set_location('Entering:'||l_proc, 5);
512 --
513 -- Call all supporting business operations
514 --
515 --
516 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
517 --
518 chk_ppl_grp_rt_id
519 (p_ppl_grp_rt_id => p_rec.ppl_grp_rt_id,
520 p_effective_date => p_effective_date,
521 p_object_version_number => p_rec.object_version_number);
522 --
523 chk_excld_flag
524 (p_ppl_grp_rt_id => p_rec.ppl_grp_rt_id,
525 p_excld_flag => p_rec.excld_flag,
526 p_effective_date => p_effective_date,
527 p_object_version_number => p_rec.object_version_number);
528 --
529 chk_dup_record
530 (p_ppl_grp_rt_id => p_rec.ppl_grp_rt_id,
531 p_people_group_id => p_rec.people_group_id,
532 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
533 p_effective_date => p_effective_date,
534 p_object_version_number => p_rec.object_version_number,
535 p_business_group_id => p_rec.business_group_id,
536 p_validation_start_date => p_validation_start_date,
537 p_validation_end_date => p_validation_end_date);
538 --
539 chk_duplicate_ordr_num
540 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id
541 ,p_ordr_num => p_rec.ordr_num
542 ,p_effective_date => p_effective_date
543 ,p_business_group_id => p_rec.business_group_id
544 ,p_ppl_grp_rt_id => p_rec.ppl_grp_rt_id);
545 --
546 hr_utility.set_location(' Leaving:'||l_proc, 10);
547 End insert_validate;
548 --
549 -- ----------------------------------------------------------------------------
550 -- |---------------------------< update_validate >----------------------------|
551 -- ----------------------------------------------------------------------------
552 Procedure update_validate
553 (p_rec in ben_pgr_shd.g_rec_type,
554 p_effective_date in date,
555 p_datetrack_mode in varchar2,
556 p_validation_start_date in date,
557 p_validation_end_date in date) is
558 --
559 l_proc varchar2(72) := g_package||'update_validate';
560 --
561 Begin
562 hr_utility.set_location('Entering:'||l_proc, 5);
563 --
564 -- Call all supporting business operations
565 --
566 --
567 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
568 --
569 chk_ppl_grp_rt_id
570 (p_ppl_grp_rt_id => p_rec.ppl_grp_rt_id,
571 p_effective_date => p_effective_date,
572 p_object_version_number => p_rec.object_version_number);
573 --
574 chk_excld_flag
575 (p_ppl_grp_rt_id => p_rec.ppl_grp_rt_id,
576 p_excld_flag => p_rec.excld_flag,
577 p_effective_date => p_effective_date,
578 p_object_version_number => p_rec.object_version_number);
579 --
580 chk_dup_record
581 (p_ppl_grp_rt_id => p_rec.ppl_grp_rt_id,
582 p_people_group_id => p_rec.people_group_id,
583 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
584 p_effective_date => p_effective_date,
585 p_object_version_number => p_rec.object_version_number,
586 p_business_group_id => p_rec.business_group_id,
587 p_validation_start_date => p_validation_start_date,
588 p_validation_end_date => p_validation_end_date);
589 --
590 -- Call the datetrack update integrity operation
591 --
592 dt_update_validate
593 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
594 p_datetrack_mode => p_datetrack_mode,
595 p_validation_start_date => p_validation_start_date,
596 p_validation_end_date => p_validation_end_date);
597 --
598 chk_duplicate_ordr_num
599 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id
600 ,p_ordr_num => p_rec.ordr_num
601 ,p_effective_date => p_effective_date
602 ,p_business_group_id => p_rec.business_group_id
603 ,p_ppl_grp_rt_id => p_rec.ppl_grp_rt_id);
604 --
605 hr_utility.set_location(' Leaving:'||l_proc, 10);
606 End update_validate;
607 --
608 -- ----------------------------------------------------------------------------
609 -- |---------------------------< delete_validate >----------------------------|
610 -- ----------------------------------------------------------------------------
611 Procedure delete_validate
612 (p_rec in ben_pgr_shd.g_rec_type,
613 p_effective_date in date,
614 p_datetrack_mode in varchar2,
615 p_validation_start_date in date,
616 p_validation_end_date in date) is
617 --
618 l_proc varchar2(72) := g_package||'delete_validate';
619 --
620 Begin
621 hr_utility.set_location('Entering:'||l_proc, 5);
622 --
623 -- Call all supporting business operations
624 --
625 dt_delete_validate
626 (p_datetrack_mode => p_datetrack_mode,
627 p_validation_start_date => p_validation_start_date,
628 p_validation_end_date => p_validation_end_date,
629 p_ppl_grp_rt_id => p_rec.ppl_grp_rt_id);
630 --
631 hr_utility.set_location(' Leaving:'||l_proc, 10);
632 End delete_validate;
633 --
634 --
635 -- ---------------------------------------------------------------------------
636 -- |---------------------< return_legislation_code >-------------------------|
637 -- ---------------------------------------------------------------------------
638 --
639 function return_legislation_code
640 (p_ppl_grp_rt_id in number) return varchar2 is
641 --
642 -- Declare cursor
643 --
644 cursor csr_leg_code is
645 select a.legislation_code
646 from per_business_groups a,
647 ben_ppl_grp_rt_f b
648 where b.ppl_grp_rt_id = p_ppl_grp_rt_id
649 and a.business_group_id = b.business_group_id;
650 --
651 -- Declare local variables
652 --
653 l_legislation_code varchar2(150);
654 l_proc varchar2(72) := g_package||'return_legislation_code';
655 --
656 begin
657 --
658 hr_utility.set_location('Entering:'|| l_proc, 10);
659 --
660 -- Ensure that all the mandatory parameter are not null
661 --
662 hr_api.mandatory_arg_error(p_api_name => l_proc,
663 p_argument => 'ppl_grp_rt_id',
664 p_argument_value => p_ppl_grp_rt_id);
665 --
666 open csr_leg_code;
667 --
668 fetch csr_leg_code into l_legislation_code;
669 --
670 if csr_leg_code%notfound then
671 --
672 close csr_leg_code;
673 --
674 -- The primary key is invalid therefore we must error
675 --
676 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
677 hr_utility.raise_error;
678 --
679 end if;
680 --
681 close csr_leg_code;
682 --
683 hr_utility.set_location(' Leaving:'|| l_proc, 20);
684 --
685 return l_legislation_code;
686 --
687 end return_legislation_code;
688 --
689 end ben_pgr_bus;