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