[Home] [Help]
PACKAGE BODY: APPS.BEN_RGR_BUS
Source
1 Package Body ben_rgr_bus as
2 /* $Header: bergrrhi.pkb 120.0.12010000.3 2008/08/05 15:26:29 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_rgr_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_popl_rptg_grp_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 -- popl_rptg_grp_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_popl_rptg_grp_id(p_popl_rptg_grp_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_popl_rptg_grp_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_rgr_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_popl_rptg_grp_id => p_popl_rptg_grp_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_popl_rptg_grp_id,hr_api.g_number)
55 <> ben_rgr_shd.g_old_rec.popl_rptg_grp_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_rgr_shd.constraint_error('BEN_POPL_RPTG_GRP_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_popl_rptg_grp_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_rgr_shd.constraint_error('BEN_POPL_RPTG_GRP_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_popl_rptg_grp_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |------------------------------< chk_parent_rec_exists >-------------------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure checks that a parent rec exists in different business group
85 --
86 -- Pre-Conditions
87 -- None.
88 --
89 -- In Parameters
90 -- p_rptg_grp_id ID of FK column
91 -- p_business_group_id
92 -- p_effective_date session date
93 -- p_object_version_number object version number
94 --
95 -- Post Success
96 -- Processing continues
97 --
98 -- Post Failure
99 -- Error raised.
100 --
101 -- Access Status
102 -- Internal table handler use only.
103 --
104 Procedure chk_parent_rec_exists
105 (p_rptg_grp_id in number,
106 p_business_group_id in number,
107 p_effective_date in date,
108 p_object_version_number in number) is
109 --
110 l_proc varchar2(72) := g_package||'chk_parent_rec_exists';
111 l_api_updating boolean;
112 l_dummy varchar2(1);
113 --
114 -- This should return if there is a parent in different business group
115 -- If there is no parent business group id this should not return any rows
116 cursor c1 is
117 select null
118 from ben_rptg_grp bnr
119 where bnr.rptg_grp_id = p_rptg_grp_id
120 and nvl(bnr.business_group_id,p_business_group_id) <> p_business_group_id ;
121 --
122 Begin
123 --
124 hr_utility.set_location('Entering:'||l_proc,5);
125 --
126 -- check if rptg_grp_id value exists in ben_rptg_grp table
127 --
128 open c1;
129 --
130 fetch c1 into l_dummy;
131 if c1%found then
132 --
133 close c1;
134 --
135 -- raise error
136 fnd_message.set_name('BEN','BEN_92776_PARENT_REC_EXISTS');
137 fnd_message.raise_error;
138 --
139 end if;
140 --
141 close c1;
142 --
143 --
144 hr_utility.set_location('Leaving:'||l_proc,10);
145 --
146 End chk_parent_rec_exists;
147 --
148
149 -- ----------------------------------------------------------------------------
150 -- |------< chk_rptg_grp_id >------|
151 -- ----------------------------------------------------------------------------
152 --
153 -- Description
154 -- This procedure checks that a referenced foreign key actually exists
155 -- in the referenced table.
156 --
157 -- Pre-Conditions
158 -- None.
159 --
160 -- In Parameters
161 -- p_popl_rptg_grp_id PK
162 -- p_rptg_grp_id ID of FK column
163 -- p_effective_date session date
164 -- p_object_version_number object version number
165 --
166 -- Post Success
167 -- Processing continues
168 --
169 -- Post Failure
170 -- Error raised.
171 --
172 -- Access Status
173 -- Internal table handler use only.
174 --
175 Procedure chk_rptg_grp_id (p_popl_rptg_grp_id in number,
176 p_rptg_grp_id in number,
177 p_effective_date in date,
178 p_object_version_number in number) is
179 --
180 l_proc varchar2(72) := g_package||'chk_rptg_grp_id';
181 l_api_updating boolean;
182 l_dummy varchar2(1);
183 --
184 cursor c1 is
185 select null
186 from ben_rptg_grp a
187 where a.rptg_grp_id = p_rptg_grp_id;
188 --
189 Begin
190 --
191 hr_utility.set_location('Entering:'||l_proc,5);
192 --
193 l_api_updating := ben_rgr_shd.api_updating
194 (p_popl_rptg_grp_id => p_popl_rptg_grp_id,
195 p_effective_date => p_effective_date,
196 p_object_version_number => p_object_version_number);
197 --
198 if (l_api_updating
199 and nvl(p_rptg_grp_id,hr_api.g_number)
200 <> nvl(ben_rgr_shd.g_old_rec.rptg_grp_id,hr_api.g_number)
201 or not l_api_updating)
202 -- and p_rptg_grp_id is not null /* Moved the check condition below bug 2690186 */
203 then
204 --
205 -- check if rptg_grp_id value exists in ben_rptg_grp table
206 --
207 if p_rptg_grp_id is null then
208
209 fnd_message.set_name('BEN','BEN_93266_RPTG_GRP_NAME_NULL');
210 fnd_message.raise_error;
211
212 else
213
214 open c1;
215 --
216 fetch c1 into l_dummy;
217 if c1%notfound then
218 --
219 close c1;
220 --
221 -- raise error as FK does not relate to PK in ben_rptg_grp
222 -- table.
223 --
224 ben_rgr_shd.constraint_error('BEN_POPL_RPTG_GRP_FK1');
225 --
226 end if;
227 --
228 close c1;
229
230 end if;
231 --
232 end if;
233 --
234 hr_utility.set_location('Leaving:'||l_proc,10);
235 --
236 End chk_rptg_grp_id;
237
238 -- --------------------------------------------------------------------------
239 --
240 -- |------< chk_pl_id >------|
241 -- --------------------------------------------------------------------------
242 --
243 --
244 -- Description
245 -- This procedure checks that a referenced foreign key is already used
246 -- in the popl reporting table for a reporting group.
247 --
248 -- Pre-Conditions
249 -- None.
250 --
251 -- In Parameters
252 -- p_popl_rptg_grp_id ID of FK column
253 -- p_pl_id FK
254 -- p_rptg_grp_id ID of FK column
255 -- p_effective_date session date
256 -- p_object_version_number object version number
257 --
258 -- Post Success
259 -- Processing continues
260 --
261 -- Post Failure
262 -- Error raised.
263 --
264 -- Access Status
265 -- Internal table handler use only.
266 --
267 ----------------------------------------------------------------------------
268 Procedure chk_pl_id(p_popl_rptg_grp_id in number,
269 p_rptg_grp_id in number,
270 p_pl_id in varchar2,
271 p_effective_date in date,
272 p_validate_start_date in date,
273 p_validate_end_date in date,
274 p_business_group_id in number,
275 p_object_version_number in number) is
276 --
277 l_proc varchar2(72) := g_package||'chk_pl_id';
278 l_api_updating boolean;
279 l_exists varchar2(1);
280 l_dummy varchar2(1);
281 l_rptg_prps_cd varchar2(30);
282 --
283 --
284 cursor crs_pl_id is
285 select null
286 from ben_popl_rptg_grp_f
287 where pl_id = p_pl_id
288 and rptg_grp_id = nvl(p_rptg_grp_id, hr_api.g_number)
289 and business_group_id + 0 = p_business_group_id
290 and p_validate_start_date <= effective_end_date
291 and p_validate_end_date >= effective_start_date;
292 --iRec
293 cursor crs_rptg_prps is
294 select rptg_prps_cd
295 from ben_rptg_grp
296 where rptg_grp_id = p_rptg_grp_id;
297 --
298 cursor crs_irec_pln is
299 select null
300 from ben_pl_f pln, ben_pl_typ_f ptp
301 where pln.pl_id = p_pl_id
302 and p_validate_start_date <= pln.effective_end_date
303 and p_validate_end_date >= pln.effective_start_date
304 and pln.pl_typ_id = ptp.pl_typ_id
305 and ptp.opt_typ_cd <> 'COMP'
306 and ptp.business_group_id = p_business_group_id
307 and greatest(p_validate_start_date, pln.effective_start_date) <= ptp.effective_end_date
308 and least(p_validate_end_date, pln.effective_end_date) >= ptp.effective_start_date;
309 --iRec
310 --
311 Begin
312 --
313 hr_utility.set_location('Entering:'||l_proc, 5);
314 --
315 l_api_updating := ben_rgr_shd.api_updating
316 (p_effective_date => p_effective_date,
317 p_popl_rptg_grp_id => p_popl_rptg_grp_id,
318 p_object_version_number => p_object_version_number);
319 --
320 if (l_api_updating
321 and p_pl_id <> ben_rgr_shd.g_old_rec.pl_id) or
322 not l_api_updating then
323 --
324 hr_utility.set_location('Entering:'||l_proc, 10);
325 --
326 -- check if this name already exist
327 --
328 open crs_pl_id;
329 fetch crs_pl_id into l_exists;
330 if crs_pl_id%found then
331 --
332 close crs_pl_id;
333 --
334 -- raise error as UK1 is violated
335 --
336 -- ben_rgr_shd.constraint_error('BEN_REGN_UK1');
337 fnd_message.set_name('BEN','BEN_91313_DUP_RPTG_GRP_PL_ID');
338 fnd_message.raise_error;
339 --
340 end if;
341 --
342 close crs_pl_id;
343 --
344 --iRec
345 --Get the purpose of the reporting group to which the popl_rptg_grp record belongs to
346 --
347 open crs_rptg_prps;
348 fetch crs_rptg_prps into l_rptg_prps_cd;
349 if crs_rptg_prps%found and l_rptg_prps_cd = 'IREC'
350 then
351 --
352 open crs_irec_pln;
353 fetch crs_irec_pln into l_dummy;
354 if crs_irec_pln%found
355 then
356 --
357 close crs_rptg_prps;
358 close crs_irec_pln;
359 --
360 --Raise error : plan being added or modified has Plan Type other than Individual Compensation Distribution
361 --at some point of time later than p_validation_start_date
362 --
363 fnd_message.set_name('BEN','BEN_93920_RPTG_IREC_NONICD_PLN'); --Bug#5204203
364 fnd_message.raise_error;
365 --
366 end if;
367 --
368 close crs_irec_pln;
369 --
370 end if;
371 close crs_rptg_prps;
372 --iRec
373 end if;
374 --
375 hr_utility.set_location('Leaving:'||l_proc, 20);
376 --
377 End chk_pl_id;
378 --
379 -- --------------------------------------------------------------------------
380 --
381 -- |------< chk_pgm_id >------|
382 -- --------------------------------------------------------------------------
383 --
384 --
385 -- Description
386 -- This procedure checks that a referenced foreign key is already used
387 -- in the popl reporting table for a reporting group.
388 --
389 -- Pre-Conditions
390 -- None.
391 --
392 -- In Parameters
393 -- p_popl_rptg_grp_id ID of FK column
394 -- p_pgm_id FK
395 -- p_rptg_grp_id ID of FK column
396 -- p_effective_date session date
397 -- p_object_version_number object version number
398 --
399 -- Post Success
400 -- Processing continues
401 --
402 -- Post Failure
403 -- Error raised.
404 --
405 -- Access Status
406 -- Internal table handler use only.
407 --
408 ----------------------------------------------------------------------------
409 Procedure chk_pgm_id(p_popl_rptg_grp_id in number,
410 p_rptg_grp_id in number,
411 p_pgm_id in varchar2,
412 p_effective_date in date,
413 p_validate_start_date in date,
414 p_validate_end_date in date,
415 p_business_group_id in number,
416 p_object_version_number in number) is
417 --
418 l_proc varchar2(72) := g_package||'chk_pgm_id';
419 l_api_updating boolean;
420 l_exists varchar2(1);
421 --
422 --
423 cursor csr_pgm_id is
424 select null
425 from ben_popl_rptg_grp_f
426 where pgm_id = p_pgm_id
427 and rptg_grp_id = nvl(p_rptg_grp_id, hr_api.g_number)
428 and business_group_id + 0 = p_business_group_id
429 and p_validate_start_date <= effective_end_date
430 and p_validate_end_date >= effective_start_date;
431 --
432 Begin
433 --
434 hr_utility.set_location('Entering:'||l_proc, 5);
435 --
436 l_api_updating := ben_rgr_shd.api_updating
437 (p_effective_date => p_effective_date,
438 p_popl_rptg_grp_id => p_popl_rptg_grp_id,
439 p_object_version_number => p_object_version_number);
440 --
441 if (l_api_updating
442 and p_pgm_id <> ben_rgr_shd.g_old_rec.pgm_id) or
443 not l_api_updating then
444 --
445 hr_utility.set_location('Entering:'||l_proc, 10);
446 --
447 -- check if this name already exist
448 --
449 open csr_pgm_id;
450 fetch csr_pgm_id into l_exists;
451 if csr_pgm_id%found then
452 close csr_pgm_id;
453 --
454 -- raise error as UK1 is violated
455 --
456 -- ben_rgr_shd.constraint_error('BEN_REGN_UK1');
457 fnd_message.set_name('BEN','BEN_91314_DUP_RPTG_GRP_PGM_ID');
458 fnd_message.raise_error;
459 --
460 end if;
461 --
462 close csr_pgm_id;
463 --
464 end if;
465 --
466 hr_utility.set_location('Leaving:'||l_proc, 20);
467 --
468 End chk_pgm_id;
469 --
470 -- ----------------------------------------------------------------------------
471 -- |------< chk_pgm_pl_id >------|
472 -- ----------------------------------------------------------------------------
473 --
474 -- Description
475 -- This procedure checks that only one of the program or plan id is
476 -- referenced in a record.
477 --
478 -- Pre-Conditions
479 -- None.
480 --
481 -- In Parameters
482 -- p_pgm_id ID of FK column
483 -- p_pl_id ID of FK column
484 --
485 -- Post Success
486 -- Processing continues
487 --
488 -- Post Failure
489 -- Error raised.
490 --
491 -- Access Status
492 -- Internal table handler use only.
493 --
494 Procedure chk_pgm_pl_id (p_pgm_id in number,
495 p_pl_id in number ) is
496 --
497 l_proc varchar2(72) := g_package||'chk_pgm_pl_id';
498 l_dummy varchar2(1);
499 --
500 Begin
501 --
502 hr_utility.set_location('Entering:'||l_proc,5);
503 --
504 If p_pgm_id is null and p_pl_id is null then
505 --
506 -- raise error as both pl_id and pgm_id can't be null
507 --
508 fnd_message.set_name('BEN','BEN_91111_WO_PGM_OR_PL_ID');
509 fnd_message.raise_error;
510 --
511 elsif p_pgm_id is not null and p_pl_id is not null then
512 --
513 -- raise error as both pl_id and pgm_id can't be not null
514 --
515 fnd_message.set_name('BEN','BEN_91110_EITHER_PGM_OR_PL_ID');
516 fnd_message.raise_error;
517 --
518 --
519 end if;
520 --
521 hr_utility.set_location('Leaving:'||l_proc,10);
522 --
523 End chk_pgm_pl_id;
524 --
525 -- ----------------------------------------------------------------------------
526 -- |--------------------------< dt_update_validate >--------------------------|
527 -- ----------------------------------------------------------------------------
528 -- {Start Of Comments}
529 --
530 -- Description:
531 -- This procedure is used for referential integrity of datetracked
532 -- parent entities when a datetrack update operation is taking place
533 -- and where there is no cascading of update defined for this entity.
534 --
535 -- Prerequisites:
536 -- This procedure is called from the update_validate.
537 --
538 -- In Parameters:
539 --
540 -- Post Success:
541 -- Processing continues.
542 --
543 -- Post Failure:
544 --
545 -- Developer Implementation Notes:
546 -- This procedure should not need maintenance unless the HR Schema model
547 -- changes.
548 --
549 -- Access Status:
550 -- Internal Row Handler Use Only.
551 --
552 -- {End Of Comments}
553 -- ----------------------------------------------------------------------------
554 Procedure dt_update_validate
555 (p_pgm_id in number default hr_api.g_number,
556 p_pl_id in number default hr_api.g_number,
557 p_datetrack_mode in varchar2,
558 p_validation_start_date in date,
559 p_validation_end_date in date) Is
560 --
561 l_proc varchar2(72) := g_package||'dt_update_validate';
562 l_integrity_error Exception;
563 l_table_name all_tables.table_name%TYPE;
564 --
565 Begin
566 hr_utility.set_location('Entering:'||l_proc, 5);
567 --
568 -- Ensure that the p_datetrack_mode argument is not null
569 --
570 hr_api.mandatory_arg_error
571 (p_api_name => l_proc,
572 p_argument => 'datetrack_mode',
573 p_argument_value => p_datetrack_mode);
574 --
575 -- Only perform the validation if the datetrack update mode is valid
576 --
577 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
578 --
579 --
580 -- Ensure the arguments are not null
581 --
582 hr_api.mandatory_arg_error
583 (p_api_name => l_proc,
584 p_argument => 'validation_start_date',
585 p_argument_value => p_validation_start_date);
586 --
587 hr_api.mandatory_arg_error
588 (p_api_name => l_proc,
589 p_argument => 'validation_end_date',
590 p_argument_value => p_validation_end_date);
591 --
592 If ((nvl(p_pgm_id, hr_api.g_number) <> hr_api.g_number) and
593 NOT (dt_api.check_min_max_dates
594 (p_base_table_name => 'ben_pgm_f',
595 p_base_key_column => 'pgm_id',
596 p_base_key_value => p_pgm_id,
597 p_from_date => p_validation_start_date,
598 p_to_date => p_validation_end_date))) Then
599 l_table_name := 'ben_pgm_f';
600 Raise l_integrity_error;
601 End If;
602 If ((nvl(p_pl_id, hr_api.g_number) <> hr_api.g_number) and
603 NOT (dt_api.check_min_max_dates
604 (p_base_table_name => 'ben_pl_f',
605 p_base_key_column => 'pl_id',
606 p_base_key_value => p_pl_id,
607 p_from_date => p_validation_start_date,
608 p_to_date => p_validation_end_date))) Then
609 l_table_name := 'ben_pl_f';
610 Raise l_integrity_error;
611 End If;
612 --
613 End If;
614 --
615 hr_utility.set_location(' Leaving:'||l_proc, 10);
616 Exception
617 When l_integrity_error Then
618 --
619 -- A referential integrity check was violated therefore
620 -- we must error
621 --
622 -- fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
623 -- fnd_message.set_token('TABLE_NAME', l_table_name);
624 -- fnd_message.raise_error;
625 ben_utility.parent_integrity_error(p_table_name => l_table_name);
626 When Others Then
627 --
628 -- An unhandled or unexpected error has occurred which
629 -- we must report
630 --
631 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
632 fnd_message.set_token('PROCEDURE', l_proc);
633 fnd_message.set_token('STEP','15');
634 fnd_message.raise_error;
635 End dt_update_validate;
636 --
637 -- ----------------------------------------------------------------------------
638 -- |--------------------------< dt_delete_validate >--------------------------|
639 -- ----------------------------------------------------------------------------
640 -- {Start Of Comments}
641 --
642 -- Description:
643 -- This procedure is used for referential integrity of datetracked
644 -- child entities when either a datetrack DELETE or ZAP is in operation
645 -- and where there is no cascading of delete defined for this entity.
646 -- For the datetrack mode of DELETE or ZAP we must ensure that no
647 -- datetracked child rows exist between the validation start and end
648 -- dates.
649 --
650 -- Prerequisites:
651 -- This procedure is called from the delete_validate.
652 --
653 -- In Parameters:
654 --
655 -- Post Success:
656 -- Processing continues.
657 --
658 -- Post Failure:
659 -- If a row exists by determining the returning Boolean value from the
660 -- generic dt_api.rows_exist function then we must supply an error via
661 -- the use of the local exception handler l_rows_exist.
662 --
663 -- Developer Implementation Notes:
664 -- This procedure should not need maintenance unless the HR Schema model
665 -- changes.
666 --
667 -- Access Status:
668 -- Internal Row Handler Use Only.
669 --
670 -- {End Of Comments}
671 -- ----------------------------------------------------------------------------
672 Procedure dt_delete_validate
673 (p_popl_rptg_grp_id in number,
674 p_datetrack_mode in varchar2,
675 p_validation_start_date in date,
676 p_validation_end_date in date) Is
677 --
678 l_proc varchar2(72) := g_package||'dt_delete_validate';
679 l_rows_exist Exception;
680 l_table_name all_tables.table_name%TYPE;
681 --
682 Begin
683 hr_utility.set_location('Entering:'||l_proc, 5);
684 --
685 -- Ensure that the p_datetrack_mode argument is not null
686 --
687 hr_api.mandatory_arg_error
688 (p_api_name => l_proc,
689 p_argument => 'datetrack_mode',
690 p_argument_value => p_datetrack_mode);
691 --
692 -- Only perform the validation if the datetrack mode is either
693 -- DELETE or ZAP
694 --
695 If (p_datetrack_mode = 'DELETE' or
696 p_datetrack_mode = 'ZAP') then
697 --
698 --
699 -- Ensure the arguments are not null
700 --
701 hr_api.mandatory_arg_error
702 (p_api_name => l_proc,
703 p_argument => 'validation_start_date',
704 p_argument_value => p_validation_start_date);
705 --
706 hr_api.mandatory_arg_error
707 (p_api_name => l_proc,
708 p_argument => 'validation_end_date',
709 p_argument_value => p_validation_end_date);
710 --
711 hr_api.mandatory_arg_error
712 (p_api_name => l_proc,
713 p_argument => 'popl_rptg_grp_id',
714 p_argument_value => p_popl_rptg_grp_id);
715 --
716 --
717 --
718 End If;
719 --
720 hr_utility.set_location(' Leaving:'||l_proc, 10);
721 Exception
722 When l_rows_exist Then
723 --
724 -- A referential integrity check was violated therefore
725 -- we must error
726 --
727 -- fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
728 -- fnd_message.set_token('TABLE_NAME', l_table_name);
729 -- fnd_message.raise_error;
730 ben_utility.child_exists_error(p_table_name => l_table_name);
731 When Others Then
732 --
733 -- An unhandled or unexpected error has occurred which
734 -- we must report
735 --
736 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
737 fnd_message.set_token('PROCEDURE', l_proc);
738 fnd_message.set_token('STEP','15');
739 fnd_message.raise_error;
740 End dt_delete_validate;
741 --
742 -- ----------------------------------------------------------------------------
743 -- |---------------------------< insert_validate >----------------------------|
744 -- ----------------------------------------------------------------------------
745 Procedure insert_validate
746 (p_rec in ben_rgr_shd.g_rec_type,
747 p_effective_date in date,
748 p_datetrack_mode in varchar2,
749 p_validation_start_date in date,
750 p_validation_end_date in date) is
751 --
752 l_proc varchar2(72) := g_package||'insert_validate';
753 --
754 Begin
755 hr_utility.set_location('Entering:'||l_proc, 5);
756 --
757 -- Call all supporting business operations
758 --
759 -- CWB Changes
760 if p_rec.business_group_id is not null then
761 --
762 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
763 --
764 end if;
765 --
766 chk_popl_rptg_grp_id
767 (p_popl_rptg_grp_id => p_rec.popl_rptg_grp_id,
768 p_effective_date => p_effective_date,
769 p_object_version_number => p_rec.object_version_number);
770 --
771 chk_rptg_grp_id
772 (p_popl_rptg_grp_id => p_rec.popl_rptg_grp_id,
773 p_rptg_grp_id => p_rec.rptg_grp_id,
774 p_effective_date => p_effective_date,
775 p_object_version_number => p_rec.object_version_number);
776 --
777 chk_pgm_pl_id (p_pgm_id => p_rec.pgm_id,
778 p_pl_id => p_rec.pl_id );
779 --
780 chk_pgm_id(p_popl_rptg_grp_id => p_rec.popl_rptg_grp_id,
781 p_rptg_grp_id => p_rec.rptg_grp_id,
782 p_pgm_id => p_rec.pgm_id,
783 p_effective_date => p_effective_date,
784 p_validate_start_date => p_validation_start_date,
785 p_validate_end_date => p_validation_end_date,
786 p_business_group_id => p_rec.business_group_id,
787 p_object_version_number => p_rec.object_version_number);
788 --
789 --
790 chk_pl_id( p_popl_rptg_grp_id => p_rec.popl_rptg_grp_id,
791 p_rptg_grp_id => p_rec.rptg_grp_id,
792 p_pl_id => p_rec.pl_id,
793 p_effective_date => p_effective_date,
794 p_validate_start_date => p_validation_start_date,
795 p_validate_end_date => p_validation_end_date,
796 p_business_group_id => p_rec.business_group_id,
797 p_object_version_number => p_rec.object_version_number)
798 ;
799 --
800 chk_parent_rec_exists
801 (p_rptg_grp_id => p_rec.rptg_grp_id,
802 p_business_group_id => p_rec.business_group_id,
803 p_effective_date => p_effective_date,
804 p_object_version_number => p_rec.object_version_number);
805 --
806 hr_utility.set_location(' Leaving:'||l_proc, 10);
807 End insert_validate;
808 --
809 -- ----------------------------------------------------------------------------
810 -- |---------------------------< update_validate >----------------------------|
811 -- ----------------------------------------------------------------------------
812 Procedure update_validate
813 (p_rec in ben_rgr_shd.g_rec_type,
814 p_effective_date in date,
815 p_datetrack_mode in varchar2,
816 p_validation_start_date in date,
817 p_validation_end_date in date) is
818 --
819 l_proc varchar2(72) := g_package||'update_validate';
820 --
821 Begin
822 hr_utility.set_location('Entering:'||l_proc, 5);
823 --
824 -- Call all supporting business operations
825 --
826 -- CWB Changes
827 if p_rec.business_group_id is not null then
828 --
829 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
830 --
831 end if;
832 --
833 chk_popl_rptg_grp_id
834 (p_popl_rptg_grp_id => p_rec.popl_rptg_grp_id,
835 p_effective_date => p_effective_date,
836 p_object_version_number => p_rec.object_version_number);
837 --
838 chk_rptg_grp_id
839 (p_popl_rptg_grp_id => p_rec.popl_rptg_grp_id,
840 p_rptg_grp_id => p_rec.rptg_grp_id,
841 p_effective_date => p_effective_date,
842 p_object_version_number => p_rec.object_version_number);
843 --
844 chk_pgm_pl_id (p_pgm_id => p_rec.pgm_id,
845 p_pl_id => p_rec.pl_id );
846 --
847 --
848 chk_pgm_id(p_popl_rptg_grp_id => p_rec.popl_rptg_grp_id,
849 p_rptg_grp_id => p_rec.rptg_grp_id,
850 p_pgm_id => p_rec.pgm_id,
851 p_effective_date => p_effective_date,
852 p_validate_start_date => p_validation_start_date,
853 p_validate_end_date => p_validation_end_date,
854 p_business_group_id => p_rec.business_group_id,
855 p_object_version_number => p_rec.object_version_number)
856 ;
857 --
858 --
859 chk_pl_id(p_popl_rptg_grp_id => p_rec.popl_rptg_grp_id,
860 p_rptg_grp_id => p_rec.rptg_grp_id,
861 p_pl_id => p_rec.pl_id,
862 p_effective_date => p_effective_date,
863 p_validate_start_date => p_validation_start_date,
864 p_validate_end_date => p_validation_end_date,
865 p_business_group_id => p_rec.business_group_id,
866 p_object_version_number => p_rec.object_version_number)
867 ;
868 --
869 -- Call the datetrack update integrity operation
870 --
871 dt_update_validate
872 (p_pgm_id => p_rec.pgm_id,
873 p_pl_id => p_rec.pl_id,
874 p_datetrack_mode => p_datetrack_mode,
875 p_validation_start_date => p_validation_start_date,
876 p_validation_end_date => p_validation_end_date);
877 --
878 chk_parent_rec_exists
879 (p_rptg_grp_id => p_rec.rptg_grp_id,
880 p_business_group_id => p_rec.business_group_id,
881 p_effective_date => p_effective_date,
882 p_object_version_number => p_rec.object_version_number);
883 --
884 hr_utility.set_location(' Leaving:'||l_proc, 10);
885 End update_validate;
886 --
887 -- ----------------------------------------------------------------------------
888 -- |---------------------------< delete_validate >----------------------------|
889 -- ----------------------------------------------------------------------------
890 Procedure delete_validate
891 (p_rec in ben_rgr_shd.g_rec_type,
892 p_effective_date in date,
893 p_datetrack_mode in varchar2,
894 p_validation_start_date in date,
895 p_validation_end_date in date) is
896 --
897 l_proc varchar2(72) := g_package||'delete_validate';
898 --
899 Begin
900 hr_utility.set_location('Entering:'||l_proc, 5);
901 --
902 -- Call all supporting business operations
903 --
904 dt_delete_validate
905 (p_datetrack_mode => p_datetrack_mode,
906 p_validation_start_date => p_validation_start_date,
907 p_validation_end_date => p_validation_end_date,
908 p_popl_rptg_grp_id => p_rec.popl_rptg_grp_id);
909 --
910 hr_utility.set_location(' Leaving:'||l_proc, 10);
911 End delete_validate;
912 --
913 --
914 -- ---------------------------------------------------------------------------
915 -- |---------------------< return_legislation_code >-------------------------|
916 -- ---------------------------------------------------------------------------
917 --
918 function return_legislation_code
919 (p_popl_rptg_grp_id in number) return varchar2 is
920 --
921 -- Declare cursor
922 --
923 cursor csr_leg_code is
924 select a.legislation_code
925 from per_business_groups a,
926 ben_popl_rptg_grp_f b
927 where b.popl_rptg_grp_id = p_popl_rptg_grp_id
928 and a.business_group_id = b.business_group_id;
929 --
930 -- Declare local variables
931 --
932 l_legislation_code varchar2(150);
933 l_proc varchar2(72) := g_package||'return_legislation_code';
934 --
935 begin
936 --
937 hr_utility.set_location('Entering:'|| l_proc, 10);
938 --
939 -- Ensure that all the mandatory parameter are not null
940 --
941 hr_api.mandatory_arg_error(p_api_name => l_proc,
942 p_argument => 'popl_rptg_grp_id',
943 p_argument_value => p_popl_rptg_grp_id);
944 --
945 open csr_leg_code;
946 --
947 fetch csr_leg_code into l_legislation_code;
948 --
949 /** CWB Changes
950 if csr_leg_code%notfound then
951 --
952 close csr_leg_code;
953 --
954 -- The primary key is invalid therefore we must error
955 --
956 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
957 fnd_message.raise_error;
958 --
959 end if;
960 */
961 --
962 close csr_leg_code;
963 --
964 hr_utility.set_location(' Leaving:'|| l_proc, 20);
965 --
966 return l_legislation_code;
967 --
968 end return_legislation_code;
969 --
970 end ben_rgr_bus;