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