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