1 Package Body pay_gbe_bus as
2 /* $Header: pygberhi.pkb 120.1 2005/06/30 06:59:09 tukumar noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_gbe_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_grossup_balances_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 --Procedure set_security_group_id
21 -- (p_grossup_balances_id in number
22 -- ) is
23 --
24 -- Declare cursor
25 --
26 -- EDIT_HERE In the following cursor statement add join(s) between
27 -- pay_grossup_bal_exclusions and PER_BUSINESS_GROUPS
28 -- so that the security_group_id for
29 -- the current business group context can be derived.
30 -- Remove this comment when the edit has been completed.
31 -- cursor csr_sec_grp is
32 -- select pbg.security_group_id
33 -- from per_business_groups pbg
34 -- , pay_grossup_bal_exclusions gbe
35 -- , EDIT_HERE table_name(s) 333
36 -- where gbe.grossup_balances_id = p_grossup_balances_id;
37 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
38 --
39 -- Declare local variables
40 --
41 -- l_security_group_id number;
42 -- l_proc varchar2(72) := g_package||'set_security_group_id';
43 --
44 --begin
45 --
46 -- hr_utility.set_location('Entering:'|| l_proc, 10);
47 --
48 -- Ensure that all the mandatory parameter are not null
49 --
50 -- hr_api.mandatory_arg_error
51 -- (p_api_name => l_proc
52 -- ,p_argument => 'grossup_balances_id'
53 -- ,p_argument_value => p_grossup_balances_id
54 -- );
55 --
56 -- open csr_sec_grp;
57 -- fetch csr_sec_grp into l_security_group_id;
58 --
59 -- if csr_sec_grp%notfound then
60 --
61 -- close csr_sec_grp;
62 --
63 -- The primary key is invalid therefore we must error
64 --
65 -- fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
66 -- fnd_message.raise_error;
67 --
68 -- end if;
69 -- close csr_sec_grp;
70 --
71 -- Set the security_group_id in CLIENT_INFO
72 --
73 -- hr_api.set_security_group_id
74 -- (p_security_group_id => l_security_group_id
75 -- );
76 --
77 -- hr_utility.set_location(' Leaving:'|| l_proc, 20);
78 --
79 --end set_security_group_id;
80 --
81 -- ---------------------------------------------------------------------------
82 -- |---------------------< return_legislation_code >-------------------------|
83 -- ---------------------------------------------------------------------------
84 --
85 --Function return_legislation_code
86 -- (p_grossup_balances_id in number
87 -- )
88 -- Return Varchar2 Is
89 --
90 -- Declare cursor
91 --
92 -- EDIT_HERE In the following cursor statement add join(s) between
93 -- pay_grossup_bal_exclusions and PER_BUSINESS_GROUPS
94 -- so that the legislation_code for
95 -- the current business group context can be derived.
96 -- Remove this comment when the edit has been completed.
97 -- cursor csr_leg_code is
98 -- select pbg.legislation_code
99 -- from per_business_groups pbg
100 -- , pay_grossup_bal_exclusions gbe
101 -- , EDIT_HERE table_name(s) 333
102 -- where gbe.grossup_balances_id = p_grossup_balances_id;
103 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
104 --
105 -- Declare local variables
106 --
107 -- l_legislation_code varchar2(150);
108 -- l_proc varchar2(72) := g_package||'return_legislation_code';
109 --
110 --Begin
111 --
112 -- hr_utility.set_location('Entering:'|| l_proc, 10);
113 --
114 -- Ensure that all the mandatory parameter are not null
115 --
116 -- hr_api.mandatory_arg_error
117 -- (p_api_name => l_proc
118 -- ,p_argument => 'grossup_balances_id'
119 -- ,p_argument_value => p_grossup_balances_id
120 -- );
121 --
122 -- if ( nvl(pay_gbe_bus.g_grossup_balances_id, hr_api.g_number)
123 -- = p_grossup_balances_id) then
124 --
125 -- The legislation code has already been found with a previous
126 -- call to this function. Just return the value in the global
127 -- variable.
128 --
129 -- l_legislation_code := pay_gbe_bus.g_legislation_code;
130 -- hr_utility.set_location(l_proc, 20);
131 -- else
132 --
133 -- The ID is different to the last call to this function
134 -- or this is the first call to this function.
135 --
136 -- open csr_leg_code;
137 -- fetch csr_leg_code into l_legislation_code;
138 --
139 -- if csr_leg_code%notfound then
140 --
141 -- The primary key is invalid therefore we must error
142 --
143 -- close csr_leg_code;
144 -- fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
145 -- fnd_message.raise_error;
146 -- end if;
147 -- hr_utility.set_location(l_proc,30);
148 --
149 -- Set the global variables so the values are
150 -- available for the next call to this function.
151 --
152 -- close csr_leg_code;
153 -- pay_gbe_bus.g_grossup_balances_i:= p_grossup_balances_id;
154 -- pay_gbe_bus.g_legislation_code := l_legislation_code;
155 -- end if;
156 -- hr_utility.set_location(' Leaving:'|| l_proc, 40);
157 -- return l_legislation_code;
158 --end return_legislation_code;
159 --
160 -- ----------------------------------------------------------------------------
161 -- |-----------------------< chk_non_updateable_args >------------------------|
162 -- ----------------------------------------------------------------------------
163 -- {Start Of Comments}
164 --
165 -- Description:
166 -- This procedure is used to ensure that non updateable attributes have
167 -- not been updated. If an attribute has been updated an error is generated.
168 --
169 -- Pre Conditions:
170 -- g_old_rec has been populated with details of the values currently in
171 -- the database.
172 --
173 -- In Arguments:
174 -- p_rec has been populated with the updated values the user would like the
175 -- record set to.
176 --
177 -- Post Success:
178 -- Processing continues if all the non updateable attributes have not
179 -- changed.
180 --
181 -- Post Failure:
182 -- An application error is raised if any of the non updatable attributes
183 -- have been altered.
184 --
185 -- {End Of Comments}
186 -- ----------------------------------------------------------------------------
187 Procedure chk_non_updateable_args
188 (p_rec in pay_gbe_shd.g_rec_type
189 ) IS
190 --
191 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
192 l_error EXCEPTION;
193 l_argument varchar2(30);
194 --
195 Begin
196 --
197 -- Only proceed with the validation if a row exists for the current
198 -- record in the HR Schema.
199 --
200 IF NOT pay_gbe_shd.api_updating
201 (p_grossup_balances_id => p_rec.grossup_balances_id
202 ,p_object_version_number => p_rec.object_version_number
203 ) THEN
204 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
205 fnd_message.set_token('PROCEDURE ', l_proc);
206 fnd_message.set_token('STEP ', '5');
207 fnd_message.raise_error;
208 END IF;
209 --
210 -- EDIT_HERE: Add checks to ensure non-updateable args have
211 -- not been updated.
212 --
213 if nvl(p_rec.grossup_balances_id, hr_api.g_number) <>
214 nvl(pay_gbe_shd.g_old_rec.grossup_balances_id, hr_api.g_number) then
215 l_argument := 'grossup_balances_id';
216 raise l_error;
217 end if;
218 hr_utility.set_location(l_proc, 7);
219 --
220 EXCEPTION
221 WHEN l_error THEN
222 hr_api.argument_changed_error
223 (p_api_name => l_proc
224 ,p_argument => l_argument);
225 WHEN OTHERS THEN
226 RAISE;
227 End chk_non_updateable_args;
228 --
229 -- ----------------------------------------------------------------------------
230 -- |---------------------------< chk_upd_dates >-------------------------------|
231 -- ----------------------------------------------------------------------------
232 --
233 -- Description
234 -- This procedure is used to check that for the row being updated, the start
235 -- date is not later than the end date. Also checks that the dates do not overlap
236 -- with some existing one with the same source_type, source_id and balance_type_id.
237 --
238 -- Pre Conditions
239 -- None.
240 --
241 -- In Parameters
242 -- p_grossup_balances_id
243 -- p_start_date
244 -- p_end_date
245 -- p_source_type
246 -- p_source_id
247 -- p_balance_type_id
248 -- p_object_version_number
249 --
250 -- Post Success
251 -- Processing continues
252 --
253 -- Post Failure
254 -- Errors handled by the procedure
255 --
256 -- Access Status
257 -- Internal table handler use only.
258 --
259 procedure chk_upd_dates
260 (p_grossup_balances_id in number
261 ,p_start_date in date
262 ,p_end_date in date
263 ,p_source_type in varchar2
264 ,p_source_id in number
265 ,p_balance_type_id in number
266 ,p_object_version_number in number )
267 is
268 --
269 l_exists varchar2(1);
270 l_proc varchar2(72) := g_package||'chk_upd_dates';
271 l_api_updating boolean;
272 --
273 Cursor C1_upd is
274 Select 'Y'
275 From pay_grossup_bal_exclusions
276 Where not
277 ((start_date < p_start_date
278 and nvl(end_date, hr_general.END_OF_TIME) < p_start_date)
279 OR
280 (start_date > p_end_date
281 AND NVL(end_date, hr_general.END_OF_TIME) > NVL(p_end_date, hr_general.END_OF_TIME))
282 )
283 And not (grossup_balances_id = p_grossup_balances_id)
284 And source_type = p_source_type
285 And source_id = p_source_id
286 And balance_type_id = p_balance_type_id ;
287 --
288 begin
289 hr_utility.set_location('Entering:'|| l_proc, 1);
290 --
291 -- check that the start date is not on or more than the end date
292 --
293 IF nvl(p_start_date, hr_general.START_OF_TIME)
294 > NVL(p_end_date, hr_general.END_OF_TIME) THEN
295 --
296 hr_utility.set_message
297 (800
298 ,'PAY_52900_GBE_DATE_ERROR'
299 );
300 hr_utility.raise_error;
301 --
302 END IF;
303 --
304 l_api_updating := pay_gbe_shd.api_updating (
305 p_grossup_balances_id => p_grossup_balances_id
306 ,p_object_version_number => p_object_version_number );
307 --
308 if (l_api_updating
309 and ( nvl(p_start_date,hr_general.start_of_time)
310 <> nvl(per_asp_shd.g_old_rec.start_date, hr_general.start_of_time)
311 or nvl(p_end_date,hr_general.end_of_time)
312 <> nvl(per_asp_shd.g_old_rec.end_date, hr_general.end_of_time))) then
313 hr_utility.set_location(l_proc, 4);
314 open c1_upd;
315 fetch c1_upd into l_exists;
316 If c1_upd%found then
317 hr_utility.set_location(l_proc, 5);
318 Close c1_upd;
319 hr_utility.set_message
320 (800
321 ,'PAY_52901_GBE_OVERLAP_ERROR'
322 );
323 hr_utility.raise_error;
324 else Close c1_upd;
325 End if;
326 end if;
327 --
328 END chk_upd_dates;
329 --
330 -- ----------------------------------------------------------------------------
331 -- |---------------------------< chk_ins_dates >-------------------------------|
332 -- ----------------------------------------------------------------------------
333 --
334 -- Description
335 -- This procedure is used to check that for the row being inserted, the start
336 -- date is not later than the end date. Also checks that the dates do not overlap
337 -- with some existing one with the same source_type, source_id and balance_type_id.
338 --
339 -- Pre Conditions
340 -- None.
341 --
342 -- In Parameters
343 -- p_start_date
344 -- p_end_date
345 -- p_source_type
346 -- p_source_id
347 -- p_balance_type_id
348 -- p_object_version_number
349 --
350 -- Post Success
351 -- Processing continues
352 --
353 -- Post Failure
354 -- Errors handled by the procedure
355 --
356 -- Access Status
357 -- Internal table handler use only.
358 --
359 --
360 procedure chk_ins_dates
361 (p_start_date in date
362 ,p_end_date in date
363 ,p_source_type in varchar2
364 ,p_source_id in number
365 ,p_balance_type_id in number
366 ,p_object_version_number in number )
367 is
368 --
369 l_exists varchar2(1);
370 l_proc varchar2(72) := g_package||'chk_ins_dates';
371 --
372 Cursor C1_ins is
373 Select 'Y'
374 From pay_grossup_bal_exclusions
375 Where not
376 ((start_date < p_start_date
377 and nvl(end_date, hr_general.END_OF_TIME) < p_start_date)
378 OR
379 (start_date > p_end_date
380 AND NVL(end_date, hr_general.END_OF_TIME) > NVL(p_end_date, hr_general.END_OF_TIME))
381 )
382 And source_type = p_source_type
383 And source_id = p_source_id
384 And balance_type_id = p_balance_type_id ;
385 --
386 begin
387 hr_utility.set_location('Entering:'|| l_proc, 1);
388 --
389 -- check that the start date is not on or more than the end date
390 --
391 IF nvl(p_start_date, hr_general.START_OF_TIME)
392 > NVL(p_end_date, hr_general.END_OF_TIME) THEN
393 --
394 hr_utility.set_message
395 (800
396 ,'PAY_52900_GBE_DATE_ERROR'
397 );
398 hr_utility.raise_error;
399 --
400 END IF;
401 --
402 open C1_ins;
403 fetch C1_ins into l_exists;
404 If C1_ins%found then
405 hr_utility.set_location(l_proc, 5);
406 Close C1_ins;
407 hr_utility.set_message
408 (800
409 ,'PAY_52901_GBE_OVERLAP_ERROR'
410 );
411 hr_utility.raise_error;
412 else Close C1_ins;
413 End if;
414 --
415 END chk_ins_dates;
416 --
417 -- ----------------------------------------------------------------------------
418 -- |---------------------------< chk_source >---------------------------------|
419 -- ----------------------------------------------------------------------------
420 --
421 -- Description
422 -- This procedure is used to check that for the row being updated or inserted,
423 -- the source type is either 'EE' or 'ET', and source_id is not null.
424 -- Also, if source_type is 'EE', source_id will have to be an element_entry_id
425 -- which exists on pay_element_entries_f. If source_type is 'ET', source_id
426 -- will have to be an element_type id which exists on pay_element_types_f.
427 --
428 -- Pre Conditions
429 -- None.
430 --
431 -- In Parameters
432 -- p_source_type
433 -- p_source_id
434 --
435 -- Post Success
436 -- Processing continues
437 --
438 -- Post Failure
439 -- Errors handled by the procedure
440 --
441 -- Access Status
442 -- Internal table handler use only.
443 --
444 --
445 procedure chk_source
446 ( p_source_type in varchar2
447 ,p_source_id in number)
448 is
449 --
450 l_exists number;
451 l_proc varchar2(72) := g_package||'chk_source ';
452 --
453 cursor C_ee is
454 Select distinct ee.element_entry_id
455 from pay_element_entries_f ee
456 where ee.element_entry_id = p_source_id ;
457 --
458 cursor C_et is
459 Select distinct et.element_type_id
460 from pay_element_types_f et
461 where et.element_type_id = p_source_id ;
462 --
463 begin
464 --
465 hr_utility.set_location('Entering:'|| p_source_type, 1);
466 hr_api.mandatory_arg_error
467 (p_api_name => l_proc
468 ,p_argument => 'source_type'
469 ,p_argument_value => p_source_type
470 );
471 hr_api.mandatory_arg_error
472 (p_api_name => l_proc
473 ,p_argument => 'source_id'
474 ,p_argument_value => p_source_id
475 );
476 --
477 if (p_source_type not in ('EE', 'ET') ) then
478 pay_gbe_shd.constraint_error(p_constraint_name => 'PAY_GBE_SOURCE_TYPE_CHK');
479 end if;
480 --
481 if (p_source_type = 'EE') then
482 open C_ee;
483 fetch C_ee into l_exists;
487 hr_utility.set_message(801, 'PAY_52902_GBE_SOURCE_ERROR');
484 if C_ee%notfound then
485 hr_utility.set_location(l_proc, 3);
486 close C_ee;
488 hr_utility.raise_error;
489 end if;
490 close C_ee;
491 elsif (p_source_type = 'ET') then
492 open C_et;
493 fetch C_et into l_exists;
494 if C_et%notfound then
495 hr_utility.set_location(l_proc, 3);
496 close C_et;
497 hr_utility.set_message(801, 'PAY_52902_GBE_SOURCE_ERROR');
498 hr_utility.raise_error;
499 end if;
500 close C_et;
501 end if;
502 --
503 hr_utility.set_location(l_proc, 2);
504 --
505 end chk_source;
506 --
507 -- ----------------------------------------------------------------------------
508 -- |---------------------------< chk_bal_type_id >----------------------------|
509 -- ----------------------------------------------------------------------------
510 --
511 -- Description
512 -- This procedure is used to check that for the row being updated or inserted,
513 -- the balance type id exists in pay_balance_types.
514 --
515 -- Pre Conditions
516 -- None.
517 --
518 -- In Parameters
519 -- p_balance_type_id
520 --
521 -- Post Success
522 -- Processing continues
523 --
524 -- Post Failure
525 -- Errors handled by the procedure
526 --
527 -- Access Status
528 -- Internal table handler use only.
529 --
530 Procedure chk_bal_type_id
531 ( p_balance_type_id in number )
532 is
533 --
534 l_exists number;
535 l_proc varchar2(72) := g_package||'chk_bal_type_id ';
536 --
537 -- Cursor to check a valid BALANCE_TYPE_ID being inserted.
538 -- It should exist on pay_balance_types table.
539 --
540 cursor C1 is
541 select balance_type_id
542 from pay_balance_types
543 where balance_type_id = p_balance_type_id ;
544 --
545 begin
546 hr_api.mandatory_arg_error
547 (p_api_name => l_proc
548 ,p_argument => 'balance_type_id'
549 ,p_argument_value => p_balance_type_id
550 );
551 --
552 open C1;
553 fetch C1 into l_exists;
554 if C1%notfound then
555 hr_utility.set_location(l_proc, 3);
556 close C1;
557 -- raise error as FK does not relate to PK in pay_balance_types
558 pay_gbe_shd.constraint_error('PAY_GROSSUP_BAL_EXCLUSIONS_FK1');
559 end if;
560 close C1;
561 hr_utility.set_location('Leaving:'|| l_proc, 10);
562 end chk_bal_type_id;
563 --
564 -- ----------------------------------------------------------------------------
565 -- |---------------------------< chk_unique_key >-----------------------------|
566 -- ----------------------------------------------------------------------------
567 --
568 -- Description
569 -- This procedure is used to check that the row being inserted or updated does
570 -- not already exists on the database, i.e, has the same balance_type_id,
571 -- start_date, end_date, source_type and source_id combination.
572 --
573 -- Pre Conditions
574 -- None.
575 --
576 -- In Parameters
577 -- p_balance_type_id
578 -- p_start_date
579 -- p_end_date
580 -- p_source_type
581 -- p_source_id
582 --
583 -- Post Success
584 -- Processing continues
585 --
586 -- Post Failure
587 -- Errors handled by the procedure
588 --
589 -- Access Status
590 -- Internal table handler use only.
591 --
592 Procedure chk_unique_key
593 ( p_balance_type_id in number
594 ,p_start_date in date
595 ,p_end_date in date
596 ,p_source_type in varchar2
597 ,p_source_id in number) is
598 --
599 l_exists varchar2(1);
600 l_proc varchar2(72) := g_package||'chk_unique_key';
601 --
602 cursor C1 is
603 select 'Y'
604 from pay_grossup_bal_exclusions gbe
605 where gbe.balance_type_id = p_balance_type_id
606 and gbe.start_date = p_start_date
607 and gbe.end_date = p_end_date
608 and gbe.source_type = p_source_type
609 and gbe.source_id = p_source_id ;
610 --
611 begin
612 --
613 hr_utility.set_location('Entering:'|| l_proc, 1);
614 --
615 open C1;
616 fetch C1 into l_exists;
617 if C1%found then
618 hr_utility.set_location(l_proc, 3);
619 -- row is not unique
620 close C1;
621 pay_gbe_shd.constraint_error('PAY_GROSSUP_BAL_EXCLUSIONS_UK1');
622 end if;
623 close C1;
624 --
625 hr_utility.set_location('Leaving:'|| l_proc, 10);
626 --
627 end chk_unique_key;
628 --
629 -- ----------------------------------------------------------------------------
630 -- |---------------------------< insert_validate >----------------------------|
631 -- ----------------------------------------------------------------------------
632 Procedure insert_validate
633 (p_rec in pay_gbe_shd.g_rec_type
634 ) is
635 --
636 l_proc varchar2(72) := g_package||'insert_validate';
637 --
638 Begin
639 hr_utility.set_location('Entering:'||l_proc, 5);
640 --
641 -- Call all supporting business operations
642 --
643 chk_ins_dates (
644 p_start_date => p_rec.start_date
645 ,p_end_date => p_rec.end_date
646 ,p_source_type => p_rec.source_type
647 ,p_source_id => p_rec.source_id
648 ,p_balance_type_id => p_rec.balance_type_id
649 ,p_object_version_number => p_rec.object_version_number );
650 --
651 chk_source ( p_source_type => p_rec.source_type
652 ,p_source_id => p_rec.source_id );
653 --
654 chk_bal_type_id ( p_balance_type_id => p_rec.balance_type_id );
655 --
656 chk_unique_key ( p_balance_type_id => p_rec.balance_type_id
657 ,p_start_date => p_rec.start_date
658 ,p_end_date => p_rec.end_date
659 ,p_source_type => p_rec.source_type
660 ,p_source_id => p_rec.source_id );
661 --
662 hr_utility.set_location(' Leaving:'||l_proc, 10);
663 --
664 End insert_validate;
665 --
666 -- ----------------------------------------------------------------------------
667 -- |---------------------------< update_validate >----------------------------|
668 -- ----------------------------------------------------------------------------
669 Procedure update_validate
670 (p_rec in pay_gbe_shd.g_rec_type
671 ) is
672 --
673 l_proc varchar2(72) := g_package||'update_validate';
674 --
675 Begin
676 hr_utility.set_location('Entering:'||l_proc, 5);
677 --
678 -- Call all supporting business operations
679 --
680 chk_non_updateable_args
681 (p_rec => p_rec
682 );
683 --
684 chk_upd_dates ( p_grossup_balances_id => p_rec.grossup_balances_id
685 ,p_start_date => p_rec.start_date
686 ,p_end_date => p_rec.end_date
687 ,p_source_type => p_rec.source_type
688 ,p_source_id => p_rec.source_id
689 ,p_balance_type_id => p_rec.balance_type_id
690 ,p_object_version_number => p_rec.object_version_number);
691 --
692 chk_source ( p_source_type => p_rec.source_type
693 ,p_source_id => p_rec.source_id );
694 --
695 chk_bal_type_id ( p_balance_type_id => p_rec.balance_type_id );
696 --
697 chk_unique_key ( p_balance_type_id => p_rec.balance_type_id
698 ,p_start_date => p_rec.start_date
699 ,p_end_date => p_rec.end_date
700 ,p_source_type => p_rec.source_type
701 ,p_source_id => p_rec.source_id );
702 --
703 hr_utility.set_location(' Leaving:'||l_proc, 10);
704 --
705 End update_validate;
706 --
707 -- ----------------------------------------------------------------------------
708 -- |---------------------------< delete_validate >----------------------------|
709 -- ----------------------------------------------------------------------------
710 Procedure delete_validate
711 (p_rec in pay_gbe_shd.g_rec_type
712 ) is
713 --
714 l_proc varchar2(72) := g_package||'delete_validate';
715 --
716 Begin
717 hr_utility.set_location('Entering:'||l_proc, 5);
718 --
719 -- Call all supporting business operations
720 --
721 hr_utility.set_location(' Leaving:'||l_proc, 10);
722 End delete_validate;
723 --
724 end pay_gbe_bus;
[Home] [Help]