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