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