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