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