[Home] [Help]
PACKAGE BODY: APPS.BEN_CLA_BUS
Source
1 Package Body ben_cla_bus as
2 /* $Header: beclarhi.pkb 120.0 2005/05/28 01:03:20 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_cla_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_cmbn_age_los_fctr_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 -- cmbn_age_los_fctr_id PK of record being inserted or updated.
24 -- object_version_number Object version number of record being
25 -- inserted or updated.
26 --
27 -- Post Success
28 -- Processing continues
29 --
30 -- Post Failure
31 -- Errors handled by the procedure
32 --
33 -- Access Status
34 -- Internal table handler use only.
35 --
36 Procedure chk_cmbn_age_los_fctr_id(p_cmbn_age_los_fctr_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_cmbn_age_los_fctr_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := ben_cla_shd.api_updating
47 (p_cmbn_age_los_fctr_id => p_cmbn_age_los_fctr_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_cmbn_age_los_fctr_id,hr_api.g_number)
52 <> ben_cla_shd.g_old_rec.cmbn_age_los_fctr_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_cla_shd.constraint_error('BEN_CMB_AGE_LGTH_OF_SVC_FCT_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_cmbn_age_los_fctr_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_cla_shd.constraint_error('BEN_CMB_AGE_LGTH_OF_SVC_FCT_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_cmbn_age_los_fctr_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_age_fctr_id >------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- Description
81 -- This procedure checks that a referenced foreign key actually exists
82 -- in the referenced table.
83 --
84 -- Pre-Conditions
85 -- None.
86 --
87 -- In Parameters
88 -- p_cmbn_age_los_fctr_id PK
89 -- p_age_fctr_id ID of FK column
90 -- p_object_version_number object version number
91 --
92 -- Post Success
93 -- Processing continues
94 --
95 -- Post Failure
96 -- Error raised.
97 --
98 -- Access Status
99 -- Internal table handler use only.
100 --
101 Procedure chk_age_fctr_id (p_cmbn_age_los_fctr_id in number,
102 p_age_fctr_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_age_fctr_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from ben_age_fctr a
112 where a.age_fctr_id = p_age_fctr_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := ben_cla_shd.api_updating
119 (p_cmbn_age_los_fctr_id => p_cmbn_age_los_fctr_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_age_fctr_id,hr_api.g_number)
124 <> nvl(ben_cla_shd.g_old_rec.age_fctr_id,hr_api.g_number)
125 or not l_api_updating) then
126 --
127 -- check if age_fctr_id value exists in ben_age_fctr table
128 --
129 open c1;
130 --
131 fetch c1 into l_dummy;
132 if c1%notfound then
133 --
134 close c1;
135 --
136 -- raise error as FK does not relate to PK in ben_age_fctr
137 -- table.
138 --
139 ben_cla_shd.constraint_error('BEN_CMB_AGE_LGH_OF_SVC_FCT_FK1');
140 --
141 end if;
142 --
143 close c1;
144 --
145 end if;
146 --
147 hr_utility.set_location('Leaving:'||l_proc,10);
148 --
149 End chk_age_fctr_id;
150 --
151 -- ----------------------------------------------------------------------------
152 -- |------< chk_los_fctr_id >------|
153 -- ----------------------------------------------------------------------------
154 --
155 -- Description
156 -- This procedure checks that a referenced foreign key actually exists
157 -- in the referenced table.
158 --
159 -- Pre-Conditions
160 -- None.
161 --
162 -- In Parameters
163 -- p_cmbn_age_los_fctr_id PK
164 -- p_los_fctr_id ID of FK column
165 -- p_object_version_number object version number
166 --
167 -- Post Success
168 -- Processing continues
169 --
170 -- Post Failure
171 -- Error raised.
172 --
173 -- Access Status
174 -- Internal table handler use only.
175 --
176 Procedure chk_los_fctr_id (p_cmbn_age_los_fctr_id in number,
177 p_los_fctr_id in number,
178 p_object_version_number in number) is
179 --
180 l_proc varchar2(72) := g_package||'chk_los_fctr_id';
181 l_api_updating boolean;
182 l_dummy varchar2(1);
183 --
184 cursor c1 is
185 select null
186 from ben_los_fctr a
187 where a.los_fctr_id = p_los_fctr_id;
188 --
189 Begin
190 --
191 hr_utility.set_location('Entering:'||l_proc,5);
192 --
193 l_api_updating := ben_cla_shd.api_updating
194 (p_cmbn_age_los_fctr_id => p_cmbn_age_los_fctr_id,
195 p_object_version_number => p_object_version_number);
196 --
197 if (l_api_updating
198 and nvl(p_los_fctr_id,hr_api.g_number)
199 <> nvl(ben_cla_shd.g_old_rec.los_fctr_id,hr_api.g_number)
200 or not l_api_updating) then
201 --
202 -- check if los_fctr_id value exists in ben_los_fctr table
203 --
204 open c1;
205 --
206 fetch c1 into l_dummy;
207 if c1%notfound then
208 --
209 close c1;
210 --
211 -- raise error as FK does not relate to PK in ben_los_fctr
212 -- table.
213 --
214 ben_cla_shd.constraint_error('BEN_CMB_AGE_LGH_OF_SVC_FCT_FK2');
215 --
216 end if;
217 --
218 close c1;
219 --
220 end if;
221 --
222 hr_utility.set_location('Leaving:'||l_proc,10);
223 --
224 End chk_los_fctr_id;
225
226 ------------------------------------------------------------------------
227 ----
228 -- |------< chk_cmbn_mn_mx_val >------|
229 --
230 ------------------------------------------------------------------------
231 ----
232 --
233 -- Description
234 -- This procedure is used to check that minimum combined value is always
235 -- less than
236 -- max age number.
237 --
238 -- Pre Conditions
239 -- None.
240 --
241 -- In Parameters
242 -- p_cmbn_age_los_fctr_id PK of record being inserted or updated.
243 -- cmbnd_min_val Value of combined Minimum.
244 -- cmbnd_max_val Value of combined Maximum.
245 -- effective_date effective date
246 -- object_version_number Object version number of record being
247 -- inserted or updated.
248 --
249 -- Post Success
250 -- Processing continues
251 --
252 -- Post Failure
253 -- Error handled by procedure
254 --
255 -- Access Status
256 -- Internal table handler use only.
257 --
258 Procedure chk_cmbn_mn_mx_val( p_cmbn_age_los_fctr_id in number,
259 p_cmbnd_min_val in number,
260 p_cmbnd_max_val in number,
261 p_object_version_number in number) is
262 --
263 l_proc varchar2(72) := g_package || 'chk_cmbn_mn_mx_val';
264 l_api_updating boolean;
265 l_dummy varchar2(1);
266 --
267 Begin
268 --
269 hr_utility.set_location('Entering:'||l_proc, 5);
270 --
271 -- cmbnd_min_val must be < cmbnd_max_val,
272 -- if both are used.
273 --
274 if p_cmbnd_min_val is not null and p_cmbnd_max_val is not null then
275 --
276 -- raise error if max value not greater than min value
277 --
278 -- Bug fix 1873685
279 if (p_cmbnd_max_val < p_cmbnd_min_val) then
280 -- if (p_cmbnd_max_val <= p_cmbnd_min_val) then
281 -- End fix 1873685
282 fnd_message.set_name('BEN','BEN_91069_INVALID_MIN_MAX');
283 fnd_message.raise_error;
284 end if;
285 --
286 --
287 end if;
288 --
289 hr_utility.set_location('Leaving:'||l_proc,10);
290 --
291 end chk_cmbn_mn_mx_val;
292 --
293 ------------------------------------------------------------------------
294 ----
295 -- |------< chk_name >------|
296 --
297 ------------------------------------------------------------------------
298 ----
299 --
300 -- Description
301 -- This procedure is used to check that the Name is unique in a business group.
302 --
303 -- Pre Conditions
304 -- None.
305 --
306 -- In Parameters
307 -- cmbn_age_los_fctr_id PK of record being inserted or updated.
308 -- name Value of Name.
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_name(p_cmbn_age_los_fctr_id in number,
323 p_business_group_id in number,
324 p_name in varchar2,
325 -- p_effective_date in date,
326 p_object_version_number in number) is
327 --
328 l_proc varchar2(72):= g_package||'chk_name';
329 l_api_updating boolean;
330 l_dummy varchar2(1);
331 --
332 cursor c1 is
333 select null
334 from ben_cmbn_age_los_fctr cla
335 where cla.business_group_id = p_business_group_id and
336 cla.name = p_name;
337 Begin
338 --
339 hr_utility.set_location('Entering:'||l_proc, 5);
340 --
341 l_api_updating := ben_cla_shd.api_updating
342 (p_cmbn_age_los_fctr_id => p_cmbn_age_los_fctr_id,
343 -- p_effective_date => p_effective_date,
344 p_object_version_number => p_object_version_number);
345 --
346 if (l_api_updating
347 and nvl(p_name,hr_api.g_varchar2)
348 <> ben_cla_shd.g_old_rec.name
349 or not l_api_updating)
350 and p_name is not null then
351 --
352 -- check if name already used.
353 --
354 open c1;
355 --
356 -- fetch value from cursor if it returns a record then the
357
358 -- name is invalid otherwise its valid
359 --
360 fetch c1 into l_dummy;
361 if c1%found then
362 --
363 close c1;
364 --
365 -- raise error
366 --
367 fnd_message.set_name('BEN','BEN_91009_NAME_NOT_UNIQUE');
368 fnd_message.raise_error;
369 --
370 end if;
371 --
372 close c1;
373 end if;
374 --
375 hr_utility.set_location('Leaving:'||l_proc,10);
376 --
377 end chk_name;
378 --
379
380 --Bug 2978945 begin
381
382 -- ----------------------------------------------------------------------- --
383 -- -----------------------< chk_child_records >-----------------------------|
384 -- -------------------------------------------------------------------------
385 --
386 -- Description
387 -- This procedure is used to check that Combined age and LOS child records do not
388 -- exist when the user deletes the record in the
389 -- BEN_CMBN_AGE_LOS_FCTR table.
390 --
391 -- Pre Conditions
392 -- None.
393 --
394 -- In Parameters
395 -- cmbn_age_los_fctr_id PK of record being inserted or updated.
396 --
397 -- Post Success
398 -- Processing continues
399 --
400 -- Post Failure
401 -- Error handled by procedure
402 --
403 -- Access Status
404 -- Internal table handler use only.
405 --
406 procedure chk_child_records(p_cmbn_age_los_fctr_id in number) is
407 --
408 l_proc varchar2(72):= g_package||'chk_child_records';
409
410
411 begin
412 --
413 hr_utility.set_location('Entering:'||l_proc, 5);
414
415 --Used in variable rate profiles
416 If (ben_batch_utils.rows_exist
417 (p_base_table_name => 'BEN_CMBN_AGE_LOS_RT_F',
418 p_base_key_column => 'cmbn_age_los_fctr_id',
419 p_base_key_value => p_cmbn_age_los_fctr_id
420 )) Then
421 ben_utility.child_exists_error('BEN_CMBN_AGE_LOS_RT_F');
422 End If;
423
424 --Used in eligibility profiles
425 If (ben_batch_utils.rows_exist
426 (p_base_table_name => 'BEN_ELIG_CMBN_AGE_LOS_PRTE_F',
427 p_base_key_column => 'cmbn_age_los_fctr_id',
428 p_base_key_value => p_cmbn_age_los_fctr_id
429 )) Then
430 ben_utility.child_exists_error('BEN_ELIG_CMBN_AGE_LOS_PRTE_F');
431 End If;
432
433 hr_utility.set_location('Leaving:'||l_proc,10);
434 --
435
436 end chk_child_records;
437
438 --Bug 2978945
439
440 -- ----------------------------------------------------------------------------
441 -- |---------------------------< insert_validate >----------------------------|
442 -- ----------------------------------------------------------------------------
443 Procedure insert_validate(p_rec in ben_cla_shd.g_rec_type) is
444 --
445 l_proc varchar2(72) := g_package||'insert_validate';
446 --
447 Begin
448 hr_utility.set_location('Entering:'||l_proc, 5);
449 --
450 -- Call all supporting business operations
451 --
452 --
453 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
454 --
455 chk_cmbn_age_los_fctr_id
456 (p_cmbn_age_los_fctr_id => p_rec.cmbn_age_los_fctr_id,
457 p_object_version_number => p_rec.object_version_number);
458 --
459 chk_age_fctr_id
460 (p_cmbn_age_los_fctr_id => p_rec.cmbn_age_los_fctr_id,
461 p_age_fctr_id => p_rec.age_fctr_id,
462 p_object_version_number => p_rec.object_version_number);
463 --
464 chk_los_fctr_id
465 (p_cmbn_age_los_fctr_id => p_rec.cmbn_age_los_fctr_id,
466 p_los_fctr_id => p_rec.los_fctr_id,
467 p_object_version_number => p_rec.object_version_number);
468 --
469 chk_cmbn_mn_mx_val
470 ( p_cmbn_age_los_fctr_id => p_rec.cmbn_age_los_fctr_id,
471 p_cmbnd_min_val =>p_rec.cmbnd_min_val,
472 p_cmbnd_max_val =>p_rec.cmbnd_max_val,
473 p_object_version_number => p_rec.object_version_number);
474 --
475 chk_name
476 (p_cmbn_age_los_fctr_id => p_rec.cmbn_age_los_fctr_id,
477 p_business_group_id => p_rec.business_group_id,
478 p_name => p_rec.name,
479 -- p_effective_date => p_effective_date,
480 p_object_version_number => p_rec.object_version_number);
481 --
482 hr_utility.set_location(' Leaving:'||l_proc, 10);
483 End insert_validate;
484 --
485 -- ----------------------------------------------------------------------------
486 -- |---------------------------< update_validate >----------------------------|
487 -- ----------------------------------------------------------------------------
488 Procedure update_validate(p_rec in ben_cla_shd.g_rec_type) is
489 --
490 l_proc varchar2(72) := g_package||'update_validate';
491 --
492 Begin
493 hr_utility.set_location('Entering:'||l_proc, 5);
494 --
495 -- Call all supporting business operations
496 --
497 --
498 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
499 --
500 chk_cmbn_age_los_fctr_id
501 (p_cmbn_age_los_fctr_id => p_rec.cmbn_age_los_fctr_id,
502 p_object_version_number => p_rec.object_version_number);
503 --
504 chk_age_fctr_id
505 (p_cmbn_age_los_fctr_id => p_rec.cmbn_age_los_fctr_id,
506 p_age_fctr_id => p_rec.age_fctr_id,
507 p_object_version_number => p_rec.object_version_number);
508 --
509 chk_los_fctr_id
510 (p_cmbn_age_los_fctr_id => p_rec.cmbn_age_los_fctr_id,
511 p_los_fctr_id => p_rec.los_fctr_id,
512 p_object_version_number => p_rec.object_version_number);
513 --
514 --
515 chk_cmbn_mn_mx_val
516 ( p_cmbn_age_los_fctr_id => p_rec.cmbn_age_los_fctr_id,
517 p_cmbnd_min_val =>p_rec.cmbnd_min_val,
518 p_cmbnd_max_val =>p_rec.cmbnd_max_val,
519 p_object_version_number => p_rec.object_version_number);
520 --
521 --
522 chk_name
523 (p_cmbn_age_los_fctr_id => p_rec.cmbn_age_los_fctr_id,
524 p_business_group_id => p_rec.business_group_id,
525 p_name => p_rec.name,
526 -- p_effective_date => p_effective_date,
527 p_object_version_number => p_rec.object_version_number);
528 --
529 hr_utility.set_location(' Leaving:'||l_proc, 10);
530 End update_validate;
531 --
532 -- ----------------------------------------------------------------------------
533 -- |---------------------------< delete_validate >----------------------------|
534 -- ----------------------------------------------------------------------------
535 Procedure delete_validate(p_rec in ben_cla_shd.g_rec_type) is
536 --
537 l_proc varchar2(72) := g_package||'delete_validate';
538 --
539 Begin
540 hr_utility.set_location('Entering:'||l_proc, 5);
541 --
542 -- Call all supporting business operations
543 --
544 chk_child_records(p_cmbn_age_los_fctr_id => p_rec.cmbn_age_los_fctr_id); --Bug 2978945
545 hr_utility.set_location(' Leaving:'||l_proc, 10);
546 End delete_validate;
547 --
548 --
549 -- ---------------------------------------------------------------------------
550 -- |---------------------< return_legislation_code >-------------------------|
551 -- ---------------------------------------------------------------------------
552 --
553 function return_legislation_code
554 (p_cmbn_age_los_fctr_id in number) return varchar2 is
555 --
556 -- Declare cursor
557 --
558 cursor csr_leg_code is
559 select a.legislation_code
560 from per_business_groups a,
561 ben_cmbn_age_los_fctr b
562 where b.cmbn_age_los_fctr_id = p_cmbn_age_los_fctr_id
563 and a.business_group_id = b.business_group_id;
564 --
565 -- Declare local variables
566 --
567 l_legislation_code varchar2(150);
568 l_proc varchar2(72) := g_package||'return_legislation_code';
569 --
570 begin
571 --
572 hr_utility.set_location('Entering:'|| l_proc, 10);
573 --
574 -- Ensure that all the mandatory parameter are not null
575 --
576 hr_api.mandatory_arg_error(p_api_name => l_proc,
577 p_argument => 'cmbn_age_los_fctr_id',
578 p_argument_value => p_cmbn_age_los_fctr_id);
579 --
580 open csr_leg_code;
581 --
582 fetch csr_leg_code into l_legislation_code;
583 --
584 if csr_leg_code%notfound then
585 --
586 close csr_leg_code;
587 --
588 -- The primary key is invalid therefore we must error
589 --
590 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
591 fnd_message.raise_error;
592 --
593 end if;
594 --
595 close csr_leg_code;
596 --
597 hr_utility.set_location(' Leaving:'|| l_proc, 20);
598 --
599 return l_legislation_code;
600 --
601 end return_legislation_code;
602 --
603 end ben_cla_bus;