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