[Home] [Help]
PACKAGE BODY: APPS.BEN_CMT_BUS
Source
1 Package Body ben_cmt_bus as
2 /* $Header: becmtrhi.pkb 115.14 2002/12/31 23:57:48 mmudigon ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_cmt_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_cm_dlvry_mthd_typ_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 -- cm_dlvry_mthd_typ_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_cm_dlvry_mthd_typ_id
37 (p_cm_dlvry_mthd_typ_id in number,
38 p_object_version_number in number) is
39 --
40 l_proc varchar2(72) := g_package||'chk_cm_dlvry_mthd_typ_id';
41 l_api_updating boolean;
42 --
43 Begin
44 --
45 hr_utility.set_location('Entering:'||l_proc, 5);
46 --
47 l_api_updating := ben_cmt_shd.api_updating
48 (p_cm_dlvry_mthd_typ_id => p_cm_dlvry_mthd_typ_id,
49 p_object_version_number => p_object_version_number);
50 --
51 if (l_api_updating
52 and nvl(p_cm_dlvry_mthd_typ_id,hr_api.g_number)
53 <> ben_cmt_shd.g_old_rec.cm_dlvry_mthd_typ_id) then
54 --
55 -- raise error as PK has changed
56 --
57 ben_cmt_shd.constraint_error('BEN_CM_DLVRY_MTHD_TYP_PK');
58 --
59 elsif not l_api_updating then
60 --
61 -- check if PK is null
62 --
63 if p_cm_dlvry_mthd_typ_id is not null then
64 --
65 -- raise error as PK is not null
66 --
67 ben_cmt_shd.constraint_error('BEN_CM_DLVRY_MTHD_TYP_PK');
68 --
69 end if;
70 --
71 end if;
72 --
73 hr_utility.set_location('Leaving:'||l_proc, 10);
74 --
75 End chk_cm_dlvry_mthd_typ_id;
76 --
77 -- ----------------------------------------------------------------------------
78 -- |------< chk_cm_typ_id >------|
79 -- ----------------------------------------------------------------------------
80 --
81 -- Description
82 -- This procedure checks that a referenced foreign key actually exists
83 -- in the referenced table.
84 --
85 -- Pre-Conditions
86 -- None.
87 --
88 -- In Parameters
89 -- p_cm_dlvry_mthd_typ_id PK
90 -- p_cm_typ_id ID of FK column
91 -- p_object_version_number object version number
92 --
93 -- Post Success
94 -- Processing continues
95 --
96 -- Post Failure
97 -- Error raised.
98 --
99 -- Access Status
100 -- Internal table handler use only.
101 --
102 Procedure chk_cm_typ_id (p_cm_dlvry_mthd_typ_id in number,
103 p_cm_typ_id in number,
104 p_object_version_number in number) is
105 --
106 l_proc varchar2(72) := g_package||'chk_cm_typ_id';
107 l_api_updating boolean;
108 l_dummy varchar2(1);
109 --
110 cursor c1 is
111 select null
112 from ben_cm_typ_f a
113 where a.cm_typ_id = p_cm_typ_id;
114 --
115 Begin
116 --
117 hr_utility.set_location('Entering:'||l_proc,5);
118 --
119 l_api_updating := ben_cmt_shd.api_updating
120 (p_cm_dlvry_mthd_typ_id => p_cm_dlvry_mthd_typ_id,
121 p_object_version_number => p_object_version_number);
122 --
123 if (l_api_updating
124 and nvl(p_cm_typ_id,hr_api.g_number)
125 <> nvl(ben_cmt_shd.g_old_rec.cm_typ_id,hr_api.g_number)
126 or not l_api_updating) and
127 p_cm_typ_id is not null then
128 --
129 -- check if cm_typ_id value exists in ben_cm_typ_f table
130 --
131 open c1;
132 --
133 fetch c1 into l_dummy;
134 if c1%notfound then
135 --
136 close c1;
137 --
138 -- raise error as FK does not relate to PK in ben_cm_typ_f
139 -- table.
140 --
141 ben_cmt_shd.constraint_error('BEN_CM_DLVRY_MTHD_TYP_FK3');
142 --
143 end if;
144 --
145 close c1;
146 --
147 end if;
148 --
149 hr_utility.set_location('Leaving:'||l_proc,10);
150 --
151 End chk_cm_typ_id;
152 --
153 -- ----------------------------------------------------------------------------
154 -- |------< chk_dflt_flag >------|
155 -- ----------------------------------------------------------------------------
156 --
157 -- Description
158 -- This procedure is used to check that the lookup value is valid.
159 --
160 -- Pre Conditions
161 -- None.
162 --
163 -- In Parameters
164 -- cm_dlvry_mthd_typ_id PK of record being inserted or updated.
165 -- dflt_flag Value of lookup code.
166 -- effective_date effective date
167 -- object_version_number Object version number of record being
168 -- inserted or updated.
169 --
170 -- Post Success
171 -- Processing continues
172 --
173 -- Post Failure
174 -- Error handled by procedure
175 --
176 -- Access Status
177 -- Internal table handler use only.
178 --
179 Procedure chk_dflt_flag(p_cm_dlvry_mthd_typ_id in number,
180 p_dflt_flag 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_dflt_flag';
185 l_api_updating boolean;
186 --
187 Begin
188 --
189 hr_utility.set_location('Entering:'||l_proc, 5);
190 --
191 l_api_updating := ben_cmt_shd.api_updating
192 (p_cm_dlvry_mthd_typ_id => p_cm_dlvry_mthd_typ_id,
193 p_object_version_number => p_object_version_number);
194 --
195 if (l_api_updating
196 and p_dflt_flag
197 <> nvl(ben_cmt_shd.g_old_rec.dflt_flag,hr_api.g_varchar2)
198 or not l_api_updating) then
199 --
200 -- check if value of lookup falls within lookup type.
201 --
202 --
203 if hr_api.not_exists_in_hr_lookups
204 (p_lookup_type => 'YES_NO',
205 p_lookup_code => p_dflt_flag,
206 p_effective_date => p_effective_date) then
207 --
208 -- raise error as does not exist as lookup
209 --
210 fnd_message.set_name('BEN','BEN_91210_INVLD_DFLT_FLAG');
211 fnd_message.raise_error;
212 --
213 end if;
214 --
215 end if;
216 --
217 hr_utility.set_location('Leaving:'||l_proc,10);
218 --
219 end chk_dflt_flag;
220 --
221 -- ----------------------------------------------------------------------------
222 -- |------< chk_rqd_flag >------|
223 -- ----------------------------------------------------------------------------
224 --
225 -- Description
226 -- This procedure is used to check that the lookup value is valid.
227 --
228 -- Pre Conditions
229 -- None.
230 --
231 -- In Parameters
232 -- cm_dlvry_mthd_typ_id PK of record being inserted or updated.
233 -- rqd_flag Value of lookup code.
234 -- effective_date effective date
235 -- object_version_number Object version number of record being
236 -- inserted or updated.
237 --
238 -- Post Success
239 -- Processing continues
240 --
241 -- Post Failure
242 -- Error handled by procedure
243 --
244 -- Access Status
245 -- Internal table handler use only.
246 --
247 Procedure chk_rqd_flag(p_cm_dlvry_mthd_typ_id in number,
248 p_rqd_flag in varchar2,
249 p_effective_date in date,
250 p_object_version_number in number) is
251 --
252 l_proc varchar2(72) := g_package||'chk_rqd_flag';
253 l_api_updating boolean;
254 --
255 Begin
256 --
257 hr_utility.set_location('Entering:'||l_proc, 5);
258 --
259 l_api_updating := ben_cmt_shd.api_updating
260 (p_cm_dlvry_mthd_typ_id => p_cm_dlvry_mthd_typ_id,
261 p_object_version_number => p_object_version_number);
262 --
263 if (l_api_updating
264 and p_rqd_flag
265 <> nvl(ben_cmt_shd.g_old_rec.rqd_flag,hr_api.g_varchar2)
266 or not l_api_updating) then
267 --
268 -- check if value of lookup falls within lookup type.
269 --
270 --
271 if hr_api.not_exists_in_hr_lookups
272 (p_lookup_type => 'YES_NO',
273 p_lookup_code => p_rqd_flag,
274 p_effective_date => p_effective_date) then
275 --
276 -- raise error as does not exist as lookup
277 --
278 fnd_message.set_name('BEN','BEN_91211_INVLD_RQD_FLAG');
279 fnd_message.raise_error;
280 --
281 end if;
282 --
283 end if;
284 --
285 hr_utility.set_location('Leaving:'||l_proc,10);
286 --
287 end chk_rqd_flag;
288 --
289 -- ----------------------------------------------------------------------------
290 -- |------< chk_cm_dlvry_mthd_typ_cd >------|
291 -- ----------------------------------------------------------------------------
292 --
293 -- Description
294 -- This procedure is used to check that the lookup value is valid.
295 --
296 -- Pre Conditions
297 -- None.
298 --
299 -- In Parameters
300 -- cm_dlvry_mthd_typ_id PK of record being inserted or updated.
301 -- cm_dlvry_mthd_typ_cd Value of lookup code.
302 -- effective_date effective date
303 -- object_version_number Object version number of record being
304 -- inserted or updated.
305 --
306 -- Post Success
307 -- Processing continues
308 --
309 -- Post Failure
310 -- Error handled by procedure
311 --
312 -- Access Status
313 -- Internal table handler use only.
314 --
315 Procedure chk_cm_dlvry_mthd_typ_cd
316 (p_cm_dlvry_mthd_typ_id in number,
317 p_cm_dlvry_mthd_typ_cd in varchar2,
318 p_effective_date in date,
319 p_object_version_number in number) is
320 --
321 l_proc varchar2(72) := g_package||'chk_cm_dlvry_mthd_typ_cd';
322 l_api_updating boolean;
323 --
324 Begin
325 --
326 hr_utility.set_location('Entering:'||l_proc, 5);
327 --
328 l_api_updating := ben_cmt_shd.api_updating
329 (p_cm_dlvry_mthd_typ_id => p_cm_dlvry_mthd_typ_id,
330 p_object_version_number => p_object_version_number);
331 --
332 if (l_api_updating
333 and p_cm_dlvry_mthd_typ_cd
334 <> nvl(ben_cmt_shd.g_old_rec.cm_dlvry_mthd_typ_cd,hr_api.g_varchar2)
335 or not l_api_updating) then
336 --
337 -- check if value of lookup falls within lookup type.
338 --
339 --
340 if hr_api.not_exists_in_hr_lookups
341 (p_lookup_type => 'BEN_DLVRY_MTHD',
342 p_lookup_code => p_cm_dlvry_mthd_typ_cd,
343 p_effective_date => p_effective_date) then
344 --
345 -- raise error as does not exist as lookup
346 --
347 fnd_message.set_name('BEN','BEN_91208_INVLD_DLVRY_MTHD_CD');
348 fnd_message.raise_error;
349 --
350 end if;
351 --
352 end if;
353 --
354 hr_utility.set_location('Leaving:'||l_proc,10);
355 --
356 end chk_cm_dlvry_mthd_typ_cd;
357 --
358 -- --------------------------------------------------------------------------
359 --
360 -- |------< chk_dup_cm_dlvry_mthd >------|
361 -- --------------------------------------------------------------------------
362 --
363 --
364 -- Description
365 -- This procedure checks the Communication delivery method is Unique
366 -- in the Communication Type, in other words duplicates are not allowed.
367 --
368 -- Pre-Conditions
369 -- None.
370 --
371 -- In Parameters
372 -- p_cm_typ_id ID of FK column
373 -- p_cm_dlvry_mthd_typ_id PK
374 -- p_effective_date session date
375 -- p_object_version_number object version number
376 --
377 -- Post Success
378 -- Processing continues
379 --
380 -- Post Failure
381 -- Error raised.
382 --
383 -- Access Status
384 -- Internal table handler use only.
385 --
386 ----------------------------------------------------------------------------
387 Procedure chk_dupl_cm_dlvry_mthd
388 (p_cm_dlvry_mthd_typ_id in number,
389 p_cm_dlvry_mthd_typ_cd in varchar2,
390 p_cm_typ_id in number,
391 p_effective_date in date,
392 p_business_group_id in number,
393 p_object_version_number in number) is
394 --
395 l_proc varchar2(72) := g_package||'chk_dupl_cm_dlvry_mthd';
396 l_api_updating boolean;
397 l_exists varchar2(1);
398 --
399 --
403 where cm_typ_id = nvl(p_cm_typ_id, hr_api.g_number)
400 cursor crs_cm_dlvry_mthd is
401 select null
402 from ben_cm_dlvry_mthd_typ
407 --
404 /* Bug Fix for Bug 1862 Benefits Bugs */
405 and cm_dlvry_mthd_typ_cd = p_cm_dlvry_mthd_typ_cd
406 and business_group_id + 0 = p_business_group_id ;
408 Begin
409 --
410 hr_utility.set_location('Entering:'||l_proc, 5);
411 --
412 l_api_updating := ben_cmt_shd.api_updating
413 ( p_cm_dlvry_mthd_typ_id => p_cm_dlvry_mthd_typ_id,
414 p_object_version_number => p_object_version_number);
415 --
416 if (l_api_updating
417 and p_cm_dlvry_mthd_typ_cd <> ben_cmt_shd.g_old_rec.cm_dlvry_mthd_typ_cd)
418 or
419 not l_api_updating then
420 --
421 hr_utility.set_location('Entering:'||l_proc, 10);
422 --
423 -- check if this code is already exist
424 --
425 open crs_cm_dlvry_mthd;
426 fetch crs_cm_dlvry_mthd into l_exists;
427 if crs_cm_dlvry_mthd%found then
428 close crs_cm_dlvry_mthd;
429 --
430 -- raise error as UK1 is violated
431 --
432 -- ben_cmt_shd.constraint_error('BEN_REGN_UK1');
433 fnd_message.set_name('BEN','BEN_91407_DUP_CM_MTHD_CD');
434 fnd_message.raise_error;
435 --
436 end if;
437 --
438 close crs_cm_dlvry_mthd;
439 --
440 end if;
441 --
442 hr_utility.set_location('Leaving:'||l_proc, 20);
443 --
444 End chk_dupl_cm_dlvry_mthd;
445
446 -- ----------------------------------------------------------------------------
447 -- |---------------------------< insert_validate >----------------------------|
448 -- ----------------------------------------------------------------------------
449 Procedure insert_validate(p_rec in ben_cmt_shd.g_rec_type
450 ,p_effective_date in date) is
451 --
452 l_proc varchar2(72) := g_package||'insert_validate';
453 --
454 Begin
455 hr_utility.set_location('Entering:'||l_proc, 5);
456 --
457 -- Call all supporting business operations
458 --
459 --
460 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
461 --
462 chk_cm_dlvry_mthd_typ_id
463 (p_cm_dlvry_mthd_typ_id => p_rec.cm_dlvry_mthd_typ_id,
464 p_object_version_number => p_rec.object_version_number);
465 --
466 chk_cm_typ_id
467 (p_cm_dlvry_mthd_typ_id => p_rec.cm_dlvry_mthd_typ_id,
468 p_cm_typ_id => p_rec.cm_typ_id,
469 p_object_version_number => p_rec.object_version_number);
470 --
471 chk_dflt_flag
472 (p_cm_dlvry_mthd_typ_id => p_rec.cm_dlvry_mthd_typ_id,
473 p_dflt_flag => p_rec.dflt_flag,
474 p_effective_date => p_effective_date,
475 p_object_version_number => p_rec.object_version_number);
476 --
477 chk_rqd_flag
478 (p_cm_dlvry_mthd_typ_id => p_rec.cm_dlvry_mthd_typ_id,
479 p_rqd_flag => p_rec.rqd_flag,
480 p_effective_date => p_effective_date,
481 p_object_version_number => p_rec.object_version_number);
482 --
483 chk_cm_dlvry_mthd_typ_cd
484 (p_cm_dlvry_mthd_typ_id => p_rec.cm_dlvry_mthd_typ_id,
485 p_cm_dlvry_mthd_typ_cd => p_rec.cm_dlvry_mthd_typ_cd,
486 p_effective_date => p_effective_date,
487 p_object_version_number => p_rec.object_version_number);
488 --
489 chk_dupl_cm_dlvry_mthd
490 (p_cm_dlvry_mthd_typ_id => p_rec.cm_dlvry_mthd_typ_id,
491 p_cm_dlvry_mthd_typ_cd => p_rec.cm_dlvry_mthd_typ_cd,
492 p_cm_typ_id => p_rec.cm_typ_id ,
493 p_effective_date => p_effective_date,
494 p_business_group_id => p_rec.business_group_id,
495 p_object_version_number => p_rec.object_version_number);
496 --
497 hr_utility.set_location(' Leaving:'||l_proc, 10);
498 End insert_validate;
499 --
500 -- ----------------------------------------------------------------------------
501 -- |---------------------------< update_validate >----------------------------|
502 -- ----------------------------------------------------------------------------
503 Procedure update_validate(p_rec in ben_cmt_shd.g_rec_type
504 ,p_effective_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_cm_dlvry_mthd_typ_id
517 (p_cm_dlvry_mthd_typ_id => p_rec.cm_dlvry_mthd_typ_id,
518 p_object_version_number => p_rec.object_version_number);
519 --
520 chk_cm_typ_id
521 (p_cm_dlvry_mthd_typ_id => p_rec.cm_dlvry_mthd_typ_id,
522 p_cm_typ_id => p_rec.cm_typ_id,
523 p_object_version_number => p_rec.object_version_number);
524 --
525 chk_dflt_flag
526 (p_cm_dlvry_mthd_typ_id => p_rec.cm_dlvry_mthd_typ_id,
527 p_dflt_flag => p_rec.dflt_flag,
528 p_effective_date => p_effective_date,
529 p_object_version_number => p_rec.object_version_number);
530 --
531 chk_rqd_flag
532 (p_cm_dlvry_mthd_typ_id => p_rec.cm_dlvry_mthd_typ_id,
533 p_rqd_flag => p_rec.rqd_flag,
534 p_effective_date => p_effective_date,
535 p_object_version_number => p_rec.object_version_number);
536 --
537 chk_cm_dlvry_mthd_typ_cd
538 (p_cm_dlvry_mthd_typ_id => p_rec.cm_dlvry_mthd_typ_id,
539 p_cm_dlvry_mthd_typ_cd => p_rec.cm_dlvry_mthd_typ_cd,
540 p_effective_date => p_effective_date,
541 p_object_version_number => p_rec.object_version_number);
542 --
543 chk_dupl_cm_dlvry_mthd
544 (p_cm_dlvry_mthd_typ_id => p_rec.cm_dlvry_mthd_typ_id,
548 p_business_group_id => p_rec.business_group_id,
545 p_cm_dlvry_mthd_typ_cd => p_rec.cm_dlvry_mthd_typ_cd,
546 p_cm_typ_id => p_rec.cm_typ_id ,
547 p_effective_date => p_effective_date,
549 p_object_version_number => p_rec.object_version_number);
550 --
551 hr_utility.set_location(' Leaving:'||l_proc, 10);
552 End update_validate;
553 --
554 -- ----------------------------------------------------------------------------
555 -- |---------------------------< delete_validate >----------------------------|
556 -- ----------------------------------------------------------------------------
557 Procedure delete_validate(p_rec in ben_cmt_shd.g_rec_type
558 ,p_effective_date in date) is
559 --
560 l_proc varchar2(72) := g_package||'delete_validate';
561 --
562 Begin
563 hr_utility.set_location('Entering:'||l_proc, 5);
564 --
565 -- Call all supporting business operations
566 --
567 hr_utility.set_location(' Leaving:'||l_proc, 10);
568 End delete_validate;
569 --
570 --
571 -- ---------------------------------------------------------------------------
572 -- |---------------------< return_legislation_code >-------------------------|
573 -- ---------------------------------------------------------------------------
574 --
575 function return_legislation_code
576 (p_cm_dlvry_mthd_typ_id in number) return varchar2 is
577 --
578 -- Declare cursor
579 --
580 cursor csr_leg_code is
581 select a.legislation_code
582 from per_business_groups a,
583 ben_cm_dlvry_mthd_typ b
584 where b.cm_dlvry_mthd_typ_id = p_cm_dlvry_mthd_typ_id
585 and a.business_group_id = b.business_group_id;
586 --
587 -- Declare local variables
588 --
589 l_legislation_code varchar2(150);
590 l_proc varchar2(72) := g_package||'return_legislation_code';
591 --
592 begin
593 --
594 hr_utility.set_location('Entering:'|| l_proc, 10);
595 --
596 -- Ensure that all the mandatory parameter are not null
597 --
598 hr_api.mandatory_arg_error(p_api_name => l_proc,
599 p_argument => 'cm_dlvry_mthd_typ_id',
600 p_argument_value => p_cm_dlvry_mthd_typ_id);
601 --
602 open csr_leg_code;
603 --
604 fetch csr_leg_code into l_legislation_code;
605 --
606 if csr_leg_code%notfound then
607 --
608 close csr_leg_code;
609 --
610 -- The primary key is invalid therefore we must error
611 --
612 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
613 hr_utility.raise_error;
614 --
615 end if;
616 --
617 close csr_leg_code;
618 --
619 hr_utility.set_location(' Leaving:'|| l_proc, 20);
620 --
621 return l_legislation_code;
622 --
623 end return_legislation_code;
624 --
625 end ben_cmt_bus;