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