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