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