[Home] [Help]
PACKAGE BODY: APPS.BEN_ERP_BUS
Source
1 Package Body ben_erp_bus as
2 /* $Header: beerprhi.pkb 115.7 2002/12/16 13:25:29 vsethi ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_erp_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_enrt_perd_for_pl_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 -- enrt_perd_for_pl_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_enrt_perd_for_pl_id(p_enrt_perd_for_pl_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_enrt_perd_for_pl_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_erp_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_enrt_perd_for_pl_id => p_enrt_perd_for_pl_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_enrt_perd_for_pl_id,hr_api.g_number)
55 <> ben_erp_shd.g_old_rec.enrt_perd_for_pl_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_erp_shd.constraint_error('BEN_ENRT_PERD_FOR_PL_F_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_enrt_perd_for_pl_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_erp_shd.constraint_error('BEN_ENRT_PERD_FOR_PL_F_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_enrt_perd_for_pl_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |------< chk_rt_end_dt_rl >------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure is used to check that the Formula Rule is valid.
85 --
86 -- Pre Conditions
87 -- None.
88 --
89 -- In Parameters
90 -- enrt_perd_for_pl_id PK of record being inserted or updated.
91 -- rt_end_dt_rl Value of formula rule id.
92 -- effective_date effective date
93 -- object_version_number Object version number of record being
94 -- inserted or updated.
95 --
96 -- Post Success
97 -- Processing continues
98 --
99 -- Post Failure
100 -- Error handled by procedure
101 --
102 -- Access Status
103 -- Internal table handler use only.
104 --
105 Procedure chk_rt_end_dt_rl(p_enrt_perd_for_pl_id in number,
106 p_business_group_id in number,
107 p_rt_end_dt_rl in number,
111 l_proc varchar2(72) := g_package||'chk_rt_end_dt_rl';
108 p_effective_date in date,
109 p_object_version_number in number) is
110 --
112 l_api_updating boolean;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc, 5);
117 --
118 l_api_updating := ben_erp_shd.api_updating
119 (p_enrt_perd_for_pl_id => p_enrt_perd_for_pl_id,
120 p_effective_date => p_effective_date,
121 p_object_version_number => p_object_version_number);
122 --
123 if (l_api_updating
124 and nvl(p_rt_end_dt_rl,hr_api.g_number)
125 <> ben_erp_shd.g_old_rec.rt_end_dt_rl
126 or not l_api_updating)
127 and p_rt_end_dt_rl is not null then
128 --
129 -- check if value of formula rule is valid.
130 --
131 if not benutils.formula_exists
132 (p_formula_id => p_rt_end_dt_rl,
133 p_formula_type_id => -67,
134 p_business_group_id => p_business_group_id,
135 p_effective_date => p_effective_date) then
136 --
137 -- raise error
138 --
139 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
140 fnd_message.set_token('ID',p_rt_end_dt_rl);
141 fnd_message.set_token('TYPE_ID',-67);
142 fnd_message.raise_error;
143 --
144 end if;
145 --
146 end if;
147 --
148 hr_utility.set_location('Leaving:'||l_proc,10);
149 --
150 end chk_rt_end_dt_rl;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |------< chk_rt_end_dt_cd >------|
154 -- ----------------------------------------------------------------------------
155 --
156 -- Description
157 -- This procedure is used to check that the lookup value is valid.
158 --
159 -- Pre Conditions
160 -- None.
161 --
162 -- In Parameters
163 -- enrt_perd_for_pl_id PK of record being inserted or updated.
164 -- rt_end_dt_cd Value of lookup code.
165 -- effective_date effective date
166 -- object_version_number Object version number of record being
167 -- inserted or updated.
168 --
169 -- Post Success
170 -- Processing continues
171 --
172 -- Post Failure
173 -- Error handled by procedure
174 --
175 -- Access Status
176 -- Internal table handler use only.
177 --
178 Procedure chk_rt_end_dt_cd(p_enrt_perd_for_pl_id in number,
179 p_rt_end_dt_cd in varchar2,
180 p_effective_date in date,
181 p_object_version_number in number) is
182 --
186 Begin
183 l_proc varchar2(72) := g_package||'chk_rt_end_dt_cd';
184 l_api_updating boolean;
185 --
187 --
188 hr_utility.set_location('Entering:'||l_proc, 5);
189 --
190 l_api_updating := ben_erp_shd.api_updating
191 (p_enrt_perd_for_pl_id => p_enrt_perd_for_pl_id,
192 p_effective_date => p_effective_date,
193 p_object_version_number => p_object_version_number);
194 --
195 if (l_api_updating
196 and p_rt_end_dt_cd
197 <> nvl(ben_erp_shd.g_old_rec.rt_end_dt_cd,hr_api.g_varchar2)
198 or not l_api_updating)
199 and p_rt_end_dt_cd is not null then
200 --
201 -- check if value of lookup falls within lookup type.
205 p_lookup_code => p_rt_end_dt_cd,
202 --
203 if hr_api.not_exists_in_hr_lookups
204 (p_lookup_type => 'BEN_RT_END',
206 p_effective_date => p_effective_date) then
207 --
208 -- raise error as does not exist as lookup
209 --
210 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
211 fnd_message.set_token('FIELD','p_rt_end_dt_cd');
212 fnd_message.set_token('TYPE','BEN_RT_END');
213 fnd_message.raise_error;
214 --
215 end if;
216 --
217 end if;
218 --
219 hr_utility.set_location('Leaving:'||l_proc,10);
220 --
221 end chk_rt_end_dt_cd;
222 --
223 -- ----------------------------------------------------------------------------
224 -- |------< chk_rt_strt_dt_rl >------|
225 -- ----------------------------------------------------------------------------
226 --
227 -- Description
228 -- This procedure is used to check that the Formula Rule is valid.
229 --
230 -- Pre Conditions
231 -- None.
232 --
233 -- In Parameters
234 -- enrt_perd_for_pl_id PK of record being inserted or updated.
235 -- rt_strt_dt_rl Value of formula rule id.
236 -- effective_date effective date
237 -- object_version_number Object version number of record being
238 -- inserted or updated.
239 --
240 -- Post Success
241 -- Processing continues
242 --
243 -- Post Failure
244 -- Error handled by procedure
245 --
246 -- Access Status
247 -- Internal table handler use only.
248 --
249 Procedure chk_rt_strt_dt_rl(p_enrt_perd_for_pl_id in number,
250 p_business_group_id in number,
251 p_rt_strt_dt_rl in number,
252 p_effective_date in date,
253 p_object_version_number in number) is
254 --
255 l_proc varchar2(72) := g_package||'chk_rt_strt_dt_rl';
256 l_api_updating boolean;
257 --
258 Begin
259 --
260 hr_utility.set_location('Entering:'||l_proc, 5);
261 --
262 l_api_updating := ben_erp_shd.api_updating
263 (p_enrt_perd_for_pl_id => p_enrt_perd_for_pl_id,
264 p_effective_date => p_effective_date,
265 p_object_version_number => p_object_version_number);
266 --
267 if (l_api_updating
268 and nvl(p_rt_strt_dt_rl,hr_api.g_number)
269 <> ben_erp_shd.g_old_rec.rt_strt_dt_rl
270 or not l_api_updating)
271 and p_rt_strt_dt_rl is not null then
272 --
273 -- check if value of formula rule is valid.
274 --
275 if not benutils.formula_exists
276 (p_formula_id => p_rt_strt_dt_rl,
277 p_formula_type_id => -66,
278 p_business_group_id => p_business_group_id,
279 p_effective_date => p_effective_date) then
280 --
281 -- raise error
282 --
283 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
284 fnd_message.set_token('ID',p_rt_strt_dt_rl);
285 fnd_message.set_token('TYPE_ID',-66);
286 fnd_message.raise_error;
287 --
288 end if;
289 --
290 end if;
291 --
292 hr_utility.set_location('Leaving:'||l_proc,10);
293 --
294 end chk_rt_strt_dt_rl;
295 --
296 -- ----------------------------------------------------------------------------
297 -- |------< chk_rt_strt_dt_cd >------|
298 -- ----------------------------------------------------------------------------
299 --
300 -- Description
301 -- This procedure is used to check that the lookup value is valid.
302 --
303 -- Pre Conditions
304 -- None.
305 --
306 -- In Parameters
307 -- enrt_perd_for_pl_id PK of record being inserted or updated.
308 -- rt_strt_dt_cd Value of lookup code.
309 -- effective_date effective date
310 -- object_version_number Object version number of record being
311 -- inserted or updated.
312 --
313 -- Post Success
314 -- Processing continues
315 --
316 -- Post Failure
317 -- Error handled by procedure
318 --
319 -- Access Status
320 -- Internal table handler use only.
321 --
322 Procedure chk_rt_strt_dt_cd(p_enrt_perd_for_pl_id in number,
323 p_rt_strt_dt_cd in varchar2,
324 p_effective_date in date,
325 p_object_version_number in number) is
326 --
327 l_proc varchar2(72) := g_package||'chk_rt_strt_dt_cd';
328 l_api_updating boolean;
329 --
330 Begin
331 --
332 hr_utility.set_location('Entering:'||l_proc, 5);
333 --
334 l_api_updating := ben_erp_shd.api_updating
335 (p_enrt_perd_for_pl_id => p_enrt_perd_for_pl_id,
336 p_effective_date => p_effective_date,
337 p_object_version_number => p_object_version_number);
338 --
339 if (l_api_updating
340 and p_rt_strt_dt_cd
341 <> nvl(ben_erp_shd.g_old_rec.rt_strt_dt_cd,hr_api.g_varchar2)
342 or not l_api_updating)
343 and p_rt_strt_dt_cd is not null then
344 --
345 -- check if value of lookup falls within lookup type.
346 --
347 if hr_api.not_exists_in_hr_lookups
348 (p_lookup_type => 'BEN_RT_STRT',
349 p_lookup_code => p_rt_strt_dt_cd,
350 p_effective_date => p_effective_date) then
351 --
352 -- raise error as does not exist as lookup
353 --
357 fnd_message.raise_error;
354 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
355 fnd_message.set_token('FIELD','p_rt_strt_dt_cd');
356 fnd_message.set_token('TYPE','BEN_RT_STRT');
358 --
359 end if;
360 --
361 end if;
362 --
363 hr_utility.set_location('Leaving:'||l_proc,10);
364 --
368 -- |------< chk_enrt_cvg_end_dt_rl >------|
365 end chk_rt_strt_dt_cd;
366 --
367 -- ----------------------------------------------------------------------------
369 -- ----------------------------------------------------------------------------
370 --
371 -- Description
372 -- This procedure is used to check that the Formula Rule is valid.
373 --
374 -- Pre Conditions
375 -- None.
376 --
377 -- In Parameters
378 -- enrt_perd_for_pl_id PK of record being inserted or updated.
379 -- enrt_cvg_end_dt_rl Value of formula rule id.
380 -- effective_date effective date
381 -- object_version_number Object version number of record being
382 -- inserted or updated.
383 --
384 -- Post Success
385 -- Processing continues
386 --
387 -- Post Failure
388 -- Error handled by procedure
389 --
390 -- Access Status
391 -- Internal table handler use only.
392 --
393 Procedure chk_enrt_cvg_end_dt_rl(p_enrt_perd_for_pl_id in number,
394 p_business_group_id in number,
395 p_enrt_cvg_end_dt_rl in number,
396 p_effective_date in date,
397 p_object_version_number in number) is
398 --
399 l_proc varchar2(72) := g_package||'chk_enrt_cvg_end_dt_rl';
400 l_api_updating boolean;
401 --
402 Begin
403 --
404 hr_utility.set_location('Entering:'||l_proc, 5);
405 --
406 l_api_updating := ben_erp_shd.api_updating
407 (p_enrt_perd_for_pl_id => p_enrt_perd_for_pl_id,
408 p_effective_date => p_effective_date,
409 p_object_version_number => p_object_version_number);
410 --
411 if (l_api_updating
412 and nvl(p_enrt_cvg_end_dt_rl,hr_api.g_number)
413 <> ben_erp_shd.g_old_rec.enrt_cvg_end_dt_rl
414 or not l_api_updating)
415 and p_enrt_cvg_end_dt_rl is not null then
416 --
417 -- check if value of formula rule is valid.
418 --
419 if not benutils.formula_exists
420 (p_formula_id => p_enrt_cvg_end_dt_rl,
421 p_formula_type_id => -30,
422 p_business_group_id => p_business_group_id,
423 p_effective_date => p_effective_date) then
424 --
425 -- raise error
426 --
427 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
428 fnd_message.set_token('ID',p_enrt_cvg_end_dt_rl);
429 fnd_message.set_token('TYPE_ID',-30);
430 fnd_message.raise_error;
431 --
432 end if;
433 --
434 end if;
435 --
436 hr_utility.set_location('Leaving:'||l_proc,10);
437 --
438 end chk_enrt_cvg_end_dt_rl;
439 --
440 -- ----------------------------------------------------------------------------
441 -- |------< chk_enrt_cvg_end_dt_cd >------|
442 -- ----------------------------------------------------------------------------
443 --
444 -- Description
445 -- This procedure is used to check that the lookup value is valid.
446 --
447 -- Pre Conditions
448 -- None.
449 --
450 -- In Parameters
451 -- enrt_perd_for_pl_id PK of record being inserted or updated.
452 -- enrt_cvg_end_dt_cd Value of lookup code.
453 -- effective_date effective date
454 -- object_version_number Object version number of record being
455 -- inserted or updated.
456 --
457 -- Post Success
458 -- Processing continues
459 --
460 -- Post Failure
461 -- Error handled by procedure
462 --
463 -- Access Status
464 -- Internal table handler use only.
465 --
466 Procedure chk_enrt_cvg_end_dt_cd(p_enrt_perd_for_pl_id in number,
467 p_enrt_cvg_end_dt_cd in varchar2,
468 p_effective_date in date,
469 p_object_version_number in number) is
470 --
471 l_proc varchar2(72) := g_package||'chk_enrt_cvg_end_dt_cd';
472 l_api_updating boolean;
473 --
474 Begin
475 --
476 hr_utility.set_location('Entering:'||l_proc, 5);
477 --
478 l_api_updating := ben_erp_shd.api_updating
479 (p_enrt_perd_for_pl_id => p_enrt_perd_for_pl_id,
480 p_effective_date => p_effective_date,
481 p_object_version_number => p_object_version_number);
482 --
486 or not l_api_updating)
483 if (l_api_updating
484 and p_enrt_cvg_end_dt_cd
485 <> nvl(ben_erp_shd.g_old_rec.enrt_cvg_end_dt_cd,hr_api.g_varchar2)
487 and p_enrt_cvg_end_dt_cd is not null then
488 --
489 -- check if value of lookup falls within lookup type.
490 --
491 if hr_api.not_exists_in_hr_lookups
492 (p_lookup_type => 'BEN_ENRT_CVG_END',
493 p_lookup_code => p_enrt_cvg_end_dt_cd,
494 p_effective_date => p_effective_date) then
495 --
496 -- raise error as does not exist as lookup
497 --
498 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
499 fnd_message.set_token('FIELD','p_enrt_cvg_end_dt_cd');
500 fnd_message.set_token('TYPE','BEN_ENRT_CVG_END');
501 fnd_message.raise_error;
502 --
503 end if;
504 --
505 end if;
506 --
507 hr_utility.set_location('Leaving:'||l_proc,10);
508 --
509 end chk_enrt_cvg_end_dt_cd;
510 --
511 -- ----------------------------------------------------------------------------
512 -- |------< chk_enrt_cvg_strt_dt_rl >------|
513 -- ----------------------------------------------------------------------------
514 --
515 -- Description
516 -- This procedure is used to check that the Formula Rule is valid.
517 --
518 -- Pre Conditions
519 -- None.
520 --
521 -- In Parameters
522 -- enrt_perd_for_pl_id PK of record being inserted or updated.
523 -- enrt_cvg_strt_dt_rl Value of formula rule id.
524 -- effective_date effective date
525 -- object_version_number Object version number of record being
526 -- inserted or updated.
527 --
528 -- Post Success
529 -- Processing continues
530 --
531 -- Post Failure
532 -- Error handled by procedure
533 --
534 -- Access Status
535 -- Internal table handler use only.
536 --
537 Procedure chk_enrt_cvg_strt_dt_rl(p_enrt_perd_for_pl_id in number,
538 p_business_group_id in number,
539 p_enrt_cvg_strt_dt_rl in number,
540 p_effective_date in date,
541 p_object_version_number in number) is
545 --
542 --
543 l_proc varchar2(72) := g_package||'chk_enrt_cvg_strt_dt_rl';
544 l_api_updating boolean;
546 Begin
547 --
548 hr_utility.set_location('Entering:'||l_proc, 5);
549 --
550 l_api_updating := ben_erp_shd.api_updating
551 (p_enrt_perd_for_pl_id => p_enrt_perd_for_pl_id,
552 p_effective_date => p_effective_date,
553 p_object_version_number => p_object_version_number);
554 --
555 if (l_api_updating
556 and nvl(p_enrt_cvg_strt_dt_rl,hr_api.g_number)
557 <> ben_erp_shd.g_old_rec.enrt_cvg_strt_dt_rl
558 or not l_api_updating)
559 and p_enrt_cvg_strt_dt_rl is not null then
560 --
561 -- check if value of formula rule is valid.
562 --
563 if not benutils.formula_exists
564 (p_formula_id => p_enrt_cvg_strt_dt_rl,
565 p_formula_type_id => -29,
566 p_business_group_id => p_business_group_id,
567 p_effective_date => p_effective_date) then
568 --
569 -- raise error
570 --
571 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
572 fnd_message.set_token('ID',p_enrt_cvg_strt_dt_rl);
573 fnd_message.set_token('TYPE_ID',-29);
574 fnd_message.raise_error;
575 --
576 end if;
577 --
578 end if;
579 --
580 hr_utility.set_location('Leaving:'||l_proc,10);
581 --
582 end chk_enrt_cvg_strt_dt_rl;
583 --
584 -- ----------------------------------------------------------------------------
585 -- |------< chk_enrt_cvg_strt_dt_cd >------|
586 -- ----------------------------------------------------------------------------
587 --
588 -- Description
589 -- This procedure is used to check that the lookup value is valid.
590 --
591 -- Pre Conditions
592 -- None.
593 --
594 -- In Parameters
595 -- enrt_perd_for_pl_id PK of record being inserted or updated.
596 -- enrt_cvg_strt_dt_cd Value of lookup code.
597 -- effective_date effective date
598 -- object_version_number Object version number of record being
599 -- inserted or updated.
600 --
601 -- Post Success
602 -- Processing continues
603 --
604 -- Post Failure
605 -- Error handled by procedure
606 --
607 -- Access Status
611 p_enrt_cvg_strt_dt_cd in varchar2,
608 -- Internal table handler use only.
609 --
610 Procedure chk_enrt_cvg_strt_dt_cd(p_enrt_perd_for_pl_id in number,
612 p_effective_date in date,
613 p_object_version_number in number) is
614 --
615 l_proc varchar2(72) := g_package||'chk_enrt_cvg_strt_dt_cd';
616 l_api_updating boolean;
617 --
618 Begin
619 --
620 hr_utility.set_location('Entering:'||l_proc, 5);
621 --
622 l_api_updating := ben_erp_shd.api_updating
623 (p_enrt_perd_for_pl_id => p_enrt_perd_for_pl_id,
624 p_effective_date => p_effective_date,
625 p_object_version_number => p_object_version_number);
626 --
627 if (l_api_updating
628 and p_enrt_cvg_strt_dt_cd
629 <> nvl(ben_erp_shd.g_old_rec.enrt_cvg_strt_dt_cd,hr_api.g_varchar2)
630 or not l_api_updating)
631 and p_enrt_cvg_strt_dt_cd is not null then
632 --
633 -- check if value of lookup falls within lookup type.
634 --
635 if hr_api.not_exists_in_hr_lookups
636 (p_lookup_type => 'BEN_ENRT_CVG_STRT',
637 p_lookup_code => p_enrt_cvg_strt_dt_cd,
638 p_effective_date => p_effective_date) then
639 --
640 -- raise error as does not exist as lookup
641 --
642 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
643 fnd_message.set_token('FIELD','p_enrt_cvg_strt_dt_cd');
644 fnd_message.set_token('TYPE','BEN_ENRT_CVG_STRT');
645 fnd_message.raise_error;
646 --
647 end if;
648 --
649 end if;
650 --
651 hr_utility.set_location('Leaving:'||l_proc,10);
652 --
653 end chk_enrt_cvg_strt_dt_cd;
654 --
655 -- ----------------------------------------------------------------------------
656 -- |--------------------------< dt_update_validate >--------------------------|
657 -- ----------------------------------------------------------------------------
658 -- {Start Of Comments}
659 --
660 -- Description:
661 -- This procedure is used for referential integrity of datetracked
662 -- parent entities when a datetrack update operation is taking place
663 -- and where there is no cascading of update defined for this entity.
664 --
665 -- Prerequisites:
666 -- This procedure is called from the update_validate.
667 --
668 -- In Parameters:
669 --
670 -- Post Success:
671 -- Processing continues.
672 --
673 -- Post Failure:
674 --
675 -- Developer Implementation Notes:
676 -- This procedure should not need maintenance unless the HR Schema model
677 -- changes.
678 --
679 -- Access Status:
680 -- Internal Row Handler Use Only.
681 --
682 -- {End Of Comments}
683 -- ----------------------------------------------------------------------------
684 Procedure dt_update_validate
685 (p_lee_rsn_id in number default hr_api.g_number,
686 p_pl_id in number default hr_api.g_number,
687 p_datetrack_mode in varchar2,
688 p_validation_start_date in date,
689 p_validation_end_date in date) Is
690 --
691 l_proc varchar2(72) := g_package||'dt_update_validate';
692 l_integrity_error Exception;
693 l_table_name all_tables.table_name%TYPE;
694 --
695 Begin
696 hr_utility.set_location('Entering:'||l_proc, 5);
697 --
698 -- Ensure that the p_datetrack_mode argument is not null
699 --
700 hr_api.mandatory_arg_error
701 (p_api_name => l_proc,
702 p_argument => 'datetrack_mode',
703 p_argument_value => p_datetrack_mode);
704 --
705 -- Only perform the validation if the datetrack update mode is valid
706 --
707 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
708 --
709 --
710 -- Ensure the arguments are not null
711 --
712 hr_api.mandatory_arg_error
713 (p_api_name => l_proc,
714 p_argument => 'validation_start_date',
715 p_argument_value => p_validation_start_date);
716 --
717 hr_api.mandatory_arg_error
718 (p_api_name => l_proc,
719 p_argument => 'validation_end_date',
723 NOT (dt_api.check_min_max_dates
720 p_argument_value => p_validation_end_date);
721 --
722 If ((nvl(p_lee_rsn_id, hr_api.g_number) <> hr_api.g_number) and
724 (p_base_table_name => 'ben_lee_rsn_f',
725 p_base_key_column => 'lee_rsn_id',
726 p_base_key_value => p_lee_rsn_id,
727 p_from_date => p_validation_start_date,
728 p_to_date => p_validation_end_date))) Then
729 l_table_name := 'ben_lee_rsn_f';
730 Raise l_integrity_error;
731 End If;
732 If ((nvl(p_pl_id, hr_api.g_number) <> hr_api.g_number) and
733 NOT (dt_api.check_min_max_dates
734 (p_base_table_name => 'ben_pl_f',
735 p_base_key_column => 'pl_id',
736 p_base_key_value => p_pl_id,
737 p_from_date => p_validation_start_date,
738 p_to_date => p_validation_end_date))) Then
739 l_table_name := 'ben_pl_f';
740 Raise l_integrity_error;
741 End If;
742 --
743 End If;
744 --
745 hr_utility.set_location(' Leaving:'||l_proc, 10);
746 Exception
747 When l_integrity_error Then
748 --
749 -- A referential integrity check was violated therefore
750 -- we must error
751 --
752 hr_utility.set_message(801, 'HR_7216_DT_UPD_INTEGRITY_ERR');
753 hr_utility.set_message_token('TABLE_NAME', l_table_name);
754 hr_utility.raise_error;
755 When Others Then
756 --
757 -- An unhandled or unexpected error has occurred which
758 -- we must report
759 --
760 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
761 hr_utility.set_message_token('PROCEDURE', l_proc);
762 hr_utility.set_message_token('STEP','15');
763 hr_utility.raise_error;
764 End dt_update_validate;
765 --
766 -- ----------------------------------------------------------------------------
767 -- |--------------------------< dt_delete_validate >--------------------------|
768 -- ----------------------------------------------------------------------------
769 -- {Start Of Comments}
770 --
771 -- Description:
772 -- This procedure is used for referential integrity of datetracked
773 -- child entities when either a datetrack DELETE or ZAP is in operation
774 -- and where there is no cascading of delete defined for this entity.
775 -- For the datetrack mode of DELETE or ZAP we must ensure that no
776 -- datetracked child rows exist between the validation start and end
777 -- dates.
778 --
779 -- Prerequisites:
780 -- This procedure is called from the delete_validate.
781 --
782 -- In Parameters:
783 --
784 -- Post Success:
785 -- Processing continues.
786 --
787 -- Post Failure:
788 -- If a row exists by determining the returning Boolean value from the
789 -- generic dt_api.rows_exist function then we must supply an error via
790 -- the use of the local exception handler l_rows_exist.
791 --
792 -- Developer Implementation Notes:
793 -- This procedure should not need maintenance unless the HR Schema model
794 -- changes.
795 --
796 -- Access Status:
797 -- Internal Row Handler Use Only.
798 --
799 -- {End Of Comments}
803 p_datetrack_mode in varchar2,
800 -- ----------------------------------------------------------------------------
801 Procedure dt_delete_validate
802 (p_enrt_perd_for_pl_id in number,
804 p_validation_start_date in date,
805 p_validation_end_date in date) Is
806 --
807 l_proc varchar2(72) := g_package||'dt_delete_validate';
808 l_rows_exist Exception;
809 l_table_name all_tables.table_name%TYPE;
810 --
811 Begin
812 hr_utility.set_location('Entering:'||l_proc, 5);
813 --
814 -- Ensure that the p_datetrack_mode argument is not null
815 --
816 hr_api.mandatory_arg_error
817 (p_api_name => l_proc,
818 p_argument => 'datetrack_mode',
819 p_argument_value => p_datetrack_mode);
820 --
821 -- Only perform the validation if the datetrack mode is either
822 -- DELETE or ZAP
823 --
824 If (p_datetrack_mode = 'DELETE' or
825 p_datetrack_mode = 'ZAP') then
826 --
827 --
828 -- Ensure the arguments are not null
829 --
830 hr_api.mandatory_arg_error
831 (p_api_name => l_proc,
832 p_argument => 'validation_start_date',
833 p_argument_value => p_validation_start_date);
834 --
835 hr_api.mandatory_arg_error
836 (p_api_name => l_proc,
837 p_argument => 'validation_end_date',
838 p_argument_value => p_validation_end_date);
839 --
840 hr_api.mandatory_arg_error
841 (p_api_name => l_proc,
842 p_argument => 'enrt_perd_for_pl_id',
843 p_argument_value => p_enrt_perd_for_pl_id);
844 --
845 --
846 --
847 End If;
848 --
849 hr_utility.set_location(' Leaving:'||l_proc, 10);
850 Exception
851 When l_rows_exist Then
852 --
853 -- A referential integrity check was violated therefore
854 -- we must error
855 --
856 hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
857 hr_utility.set_message_token('TABLE_NAME', l_table_name);
858 hr_utility.raise_error;
859 When Others Then
860 --
861 -- An unhandled or unexpected error has occurred which
862 -- we must report
863 --
864 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
865 hr_utility.set_message_token('PROCEDURE', l_proc);
866 hr_utility.set_message_token('STEP','15');
867 hr_utility.raise_error;
868 End dt_delete_validate;
869 --
870 -- ----------------------------------------------------------------------------
871 -- |---------------------------< insert_validate >----------------------------|
872 -- ----------------------------------------------------------------------------
873 Procedure insert_validate
874 (p_rec in ben_erp_shd.g_rec_type,
875 p_effective_date in date,
876 p_datetrack_mode in varchar2,
877 p_validation_start_date in date,
878 p_validation_end_date in date) is
879 --
880 l_proc varchar2(72) := g_package||'insert_validate';
881 --
882 Begin
883 hr_utility.set_location('Entering:'||l_proc, 5);
884 --
885 -- Call all supporting business operations
886 --
887 --
888 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
889 --
890 chk_enrt_perd_for_pl_id
891 (p_enrt_perd_for_pl_id => p_rec.enrt_perd_for_pl_id,
892 p_effective_date => p_effective_date,
893 p_object_version_number => p_rec.object_version_number);
894 --
895 chk_rt_end_dt_rl
896 (p_enrt_perd_for_pl_id => p_rec.enrt_perd_for_pl_id,
897 p_business_group_id => p_rec.business_group_id,
898 p_rt_end_dt_rl => p_rec.rt_end_dt_rl,
899 p_effective_date => p_effective_date,
900 p_object_version_number => p_rec.object_version_number);
901 --
902 chk_rt_end_dt_cd
903 (p_enrt_perd_for_pl_id => p_rec.enrt_perd_for_pl_id,
904 p_rt_end_dt_cd => p_rec.rt_end_dt_cd,
905 p_effective_date => p_effective_date,
906 p_object_version_number => p_rec.object_version_number);
907 --
908 chk_rt_strt_dt_rl
909 (p_enrt_perd_for_pl_id => p_rec.enrt_perd_for_pl_id,
910 p_business_group_id => p_rec.business_group_id,
911 p_rt_strt_dt_rl => p_rec.rt_strt_dt_rl,
912 p_effective_date => p_effective_date,
913 p_object_version_number => p_rec.object_version_number);
914 --
915 chk_rt_strt_dt_cd
916 (p_enrt_perd_for_pl_id => p_rec.enrt_perd_for_pl_id,
917 p_rt_strt_dt_cd => p_rec.rt_strt_dt_cd,
918 p_effective_date => p_effective_date,
919 p_object_version_number => p_rec.object_version_number);
920 --
921 chk_enrt_cvg_end_dt_rl
922 (p_enrt_perd_for_pl_id => p_rec.enrt_perd_for_pl_id,
923 p_business_group_id => p_rec.business_group_id,
924 p_enrt_cvg_end_dt_rl => p_rec.enrt_cvg_end_dt_rl,
925 p_effective_date => p_effective_date,
926 p_object_version_number => p_rec.object_version_number);
927 --
928 chk_enrt_cvg_end_dt_cd
929 (p_enrt_perd_for_pl_id => p_rec.enrt_perd_for_pl_id,
930 p_enrt_cvg_end_dt_cd => p_rec.enrt_cvg_end_dt_cd,
931 p_effective_date => p_effective_date,
932 p_object_version_number => p_rec.object_version_number);
933 --
934 chk_enrt_cvg_strt_dt_rl
935 (p_enrt_perd_for_pl_id => p_rec.enrt_perd_for_pl_id,
936 p_business_group_id => p_rec.business_group_id,
937 p_enrt_cvg_strt_dt_rl => p_rec.enrt_cvg_strt_dt_rl,
938 p_effective_date => p_effective_date,
939 p_object_version_number => p_rec.object_version_number);
940 --
941 chk_enrt_cvg_strt_dt_cd
945 p_object_version_number => p_rec.object_version_number);
942 (p_enrt_perd_for_pl_id => p_rec.enrt_perd_for_pl_id,
943 p_enrt_cvg_strt_dt_cd => p_rec.enrt_cvg_strt_dt_cd,
944 p_effective_date => p_effective_date,
946 --
947 hr_utility.set_location(' Leaving:'||l_proc, 10);
948 End insert_validate;
949 --
950 -- ----------------------------------------------------------------------------
951 -- |---------------------------< update_validate >----------------------------|
952 -- ----------------------------------------------------------------------------
953 Procedure update_validate
954 (p_rec in ben_erp_shd.g_rec_type,
955 p_effective_date in date,
956 p_datetrack_mode in varchar2,
957 p_validation_start_date in date,
958 p_validation_end_date in date) is
959 --
960 l_proc varchar2(72) := g_package||'update_validate';
961 --
962 Begin
963 hr_utility.set_location('Entering:'||l_proc, 5);
964 --
965 -- Call all supporting business operations
966 --
967 --
968 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
969 --
970 chk_enrt_perd_for_pl_id
971 (p_enrt_perd_for_pl_id => p_rec.enrt_perd_for_pl_id,
972 p_effective_date => p_effective_date,
973 p_object_version_number => p_rec.object_version_number);
974 --
975 chk_rt_end_dt_rl
976 (p_enrt_perd_for_pl_id => p_rec.enrt_perd_for_pl_id,
977 p_business_group_id => p_rec.business_group_id,
978 p_rt_end_dt_rl => p_rec.rt_end_dt_rl,
979 p_effective_date => p_effective_date,
980 p_object_version_number => p_rec.object_version_number);
981 --
982 chk_rt_end_dt_cd
983 (p_enrt_perd_for_pl_id => p_rec.enrt_perd_for_pl_id,
984 p_rt_end_dt_cd => p_rec.rt_end_dt_cd,
985 p_effective_date => p_effective_date,
986 p_object_version_number => p_rec.object_version_number);
987 --
988 chk_rt_strt_dt_rl
989 (p_enrt_perd_for_pl_id => p_rec.enrt_perd_for_pl_id,
990 p_business_group_id => p_rec.business_group_id,
991 p_rt_strt_dt_rl => p_rec.rt_strt_dt_rl,
992 p_effective_date => p_effective_date,
993 p_object_version_number => p_rec.object_version_number);
994 --
995 chk_rt_strt_dt_cd
996 (p_enrt_perd_for_pl_id => p_rec.enrt_perd_for_pl_id,
997 p_rt_strt_dt_cd => p_rec.rt_strt_dt_cd,
998 p_effective_date => p_effective_date,
999 p_object_version_number => p_rec.object_version_number);
1000 --
1001 chk_enrt_cvg_end_dt_rl
1002 (p_enrt_perd_for_pl_id => p_rec.enrt_perd_for_pl_id,
1003 p_business_group_id => p_rec.business_group_id,
1004 p_enrt_cvg_end_dt_rl => p_rec.enrt_cvg_end_dt_rl,
1005 p_effective_date => p_effective_date,
1006 p_object_version_number => p_rec.object_version_number);
1007 --
1008 chk_enrt_cvg_end_dt_cd
1009 (p_enrt_perd_for_pl_id => p_rec.enrt_perd_for_pl_id,
1010 p_enrt_cvg_end_dt_cd => p_rec.enrt_cvg_end_dt_cd,
1011 p_effective_date => p_effective_date,
1012 p_object_version_number => p_rec.object_version_number);
1013 --
1014 chk_enrt_cvg_strt_dt_rl
1015 (p_enrt_perd_for_pl_id => p_rec.enrt_perd_for_pl_id,
1016 p_business_group_id => p_rec.business_group_id,
1017 p_enrt_cvg_strt_dt_rl => p_rec.enrt_cvg_strt_dt_rl,
1018 p_effective_date => p_effective_date,
1019 p_object_version_number => p_rec.object_version_number);
1020 --
1021 chk_enrt_cvg_strt_dt_cd
1022 (p_enrt_perd_for_pl_id => p_rec.enrt_perd_for_pl_id,
1023 p_enrt_cvg_strt_dt_cd => p_rec.enrt_cvg_strt_dt_cd,
1024 p_effective_date => p_effective_date,
1025 p_object_version_number => p_rec.object_version_number);
1026 --
1027 -- Call the datetrack update integrity operation
1028 --
1029 dt_update_validate
1030 (p_lee_rsn_id => p_rec.lee_rsn_id,
1031 p_pl_id => p_rec.pl_id,
1032 p_datetrack_mode => p_datetrack_mode,
1033 p_validation_start_date => p_validation_start_date,
1034 p_validation_end_date => p_validation_end_date);
1035 --
1036 hr_utility.set_location(' Leaving:'||l_proc, 10);
1037 End update_validate;
1038 --
1039 -- ----------------------------------------------------------------------------
1040 -- |---------------------------< delete_validate >----------------------------|
1041 -- ----------------------------------------------------------------------------
1042 Procedure delete_validate
1043 (p_rec in ben_erp_shd.g_rec_type,
1044 p_effective_date in date,
1045 p_datetrack_mode in varchar2,
1046 p_validation_start_date in date,
1047 p_validation_end_date in date) is
1048 --
1049 l_proc varchar2(72) := g_package||'delete_validate';
1050 --
1051 Begin
1052 hr_utility.set_location('Entering:'||l_proc, 5);
1053 --
1054 -- Call all supporting business operations
1055 --
1056 dt_delete_validate
1057 (p_datetrack_mode => p_datetrack_mode,
1058 p_validation_start_date => p_validation_start_date,
1059 p_validation_end_date => p_validation_end_date,
1060 p_enrt_perd_for_pl_id => p_rec.enrt_perd_for_pl_id);
1061 --
1062 hr_utility.set_location(' Leaving:'||l_proc, 10);
1063 End delete_validate;
1064 --
1065 --
1066 -- ---------------------------------------------------------------------------
1067 -- |---------------------< return_legislation_code >-------------------------|
1068 -- ---------------------------------------------------------------------------
1069 --
1070 function return_legislation_code
1071 (p_enrt_perd_for_pl_id in number) return varchar2 is
1072 --
1073 -- Declare cursor
1074 --
1078 ben_enrt_perd_for_pl_f b
1075 cursor csr_leg_code is
1076 select a.legislation_code
1077 from per_business_groups a,
1079 where b.enrt_perd_for_pl_id = p_enrt_perd_for_pl_id
1080 and a.business_group_id = b.business_group_id;
1081 --
1082 -- Declare local variables
1083 --
1084 l_legislation_code varchar2(150);
1085 l_proc varchar2(72) := g_package||'return_legislation_code';
1086 --
1087 begin
1088 --
1089 hr_utility.set_location('Entering:'|| l_proc, 10);
1090 --
1091 -- Ensure that all the mandatory parameter are not null
1092 --
1093 hr_api.mandatory_arg_error(p_api_name => l_proc,
1094 p_argument => 'enrt_perd_for_pl_id',
1095 p_argument_value => p_enrt_perd_for_pl_id);
1096 --
1097 open csr_leg_code;
1098 --
1099 fetch csr_leg_code into l_legislation_code;
1100 --
1101 if csr_leg_code%notfound then
1102 --
1103 close csr_leg_code;
1104 --
1105 -- The primary key is invalid therefore we must error
1106 --
1107 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
1108 hr_utility.raise_error;
1109 --
1110 end if;
1111 --
1112 close csr_leg_code;
1113 --
1114 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1115 --
1116 return l_legislation_code;
1117 --
1118 end return_legislation_code;
1119 --
1120 end ben_erp_bus;