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