1 Package Body hxc_hrp_bus as
2 /* $Header: hxchrprhi.pkb 120.2 2005/09/23 10:43:21 sechandr noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hxc_hrp_bus.'; -- Global package name
9 g_debug boolean :=hr_utility.debug_enabled;
10 --
11 -- The following two global variables are only to be
12 -- used by the return_legislation_code function.
13 --
14 g_legislation_code varchar2(150) default null;
15 g_recurring_period_id number default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure set_security_group_id
22 (p_recurring_period_id in number
23 ) is
24 --
25 -- Declare cursor
26 --
27 -- EDIT_HERE In the following cursor statement add join(s) between
28 -- hxc_recurring_periods and PER_BUSINESS_GROUPS
29 -- so that the security_group_id for
30 -- the current business group context can be derived.
31 -- Remove this comment when the edit has been completed.
32 cursor csr_sec_grp is
33 select pbg.security_group_id
34 from per_business_groups pbg
35 , hxc_recurring_periods hrp
36 -- , EDIT_HERE table_name(s) 333
37 where hrp.recurring_period_id = p_recurring_period_id;
38 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
39 --
40 -- Declare local variables
41 --
42 l_security_group_id number;
43 l_proc varchar2(72);
44 --
45 begin
46 --
47 g_debug:=hr_utility.debug_enabled;
48 if g_debug then
49 l_proc := g_package||'set_security_group_id';
50 hr_utility.set_location('Entering:'|| l_proc, 10);
51 end if;
52 --
53 -- Ensure that all the mandatory parameter are not null
54 --
55 hr_api.mandatory_arg_error
56 (p_api_name => l_proc
57 ,p_argument => 'recurring_period_id'
58 ,p_argument_value => p_recurring_period_id
59 );
60 --
61 open csr_sec_grp;
62 fetch csr_sec_grp into l_security_group_id;
63 --
64 if csr_sec_grp%notfound then
65 --
66 close csr_sec_grp;
67 --
68 -- The primary key is invalid therefore we must error
69 --
70 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
71 fnd_message.raise_error;
72 --
73 end if;
74 close csr_sec_grp;
75 --
76 -- Set the security_group_id in CLIENT_INFO
77 --
78 hr_api.set_security_group_id
79 (p_security_group_id => l_security_group_id
80 );
81 --
82 if g_debug then
83 hr_utility.set_location(' Leaving:'|| l_proc, 20);
84 end if ;
85 --
86 end set_security_group_id;
87 --
88 -- ---------------------------------------------------------------------------
89 -- |---------------------< return_legislation_code >-------------------------|
90 -- ---------------------------------------------------------------------------
91 --
92 Function return_legislation_code
93 (p_recurring_period_id in number
94 )
98 --
95 Return Varchar2 Is
96 --
97 -- Declare cursor
99 -- EDIT_HERE In the following cursor statement add join(s) between
100 -- hxc_recurring_periods and PER_BUSINESS_GROUPS
101 -- so that the legislation_code for
102 -- the current business group context can be derived.
103 -- Remove this comment when the edit has been completed.
104 cursor csr_leg_code is
105 select pbg.legislation_code
106 from per_business_groups pbg
107 , hxc_recurring_periods hrp
108 -- , EDIT_HERE table_name(s) 333
109 where hrp.recurring_period_id = p_recurring_period_id;
110 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
111 --
112 -- Declare local variables
113 --
114 l_legislation_code varchar2(150);
115 l_proc varchar2(72);
116 --
117 Begin
118 --
119 g_debug:=hr_utility.debug_enabled;
120 if g_debug then
121 l_proc := g_package||'return_legislation_code';
122 hr_utility.set_location('Entering:'|| l_proc, 10);
123 end if;
124 --
125 -- Ensure that all the mandatory parameter are not null
126 --
127 hr_api.mandatory_arg_error
128 (p_api_name => l_proc
129 ,p_argument => 'recurring_period_id'
130 ,p_argument_value => p_recurring_period_id
131 );
132 --
133 if ( nvl(hxc_hrp_bus.g_recurring_period_id, hr_api.g_number)
134 = p_recurring_period_id) then
135 --
136 -- The legislation code has already been found with a previous
137 -- call to this function. Just return the value in the global
138 -- variable.
139 --
140 l_legislation_code := hxc_hrp_bus.g_legislation_code;
141 if g_debug then
142 hr_utility.set_location(l_proc, 20);
143 end if;
144 else
145 --
146 -- The ID is different to the last call to this function
147 -- or this is the first call to this function.
148 --
149 open csr_leg_code;
150 fetch csr_leg_code into l_legislation_code;
151 --
152 if csr_leg_code%notfound then
153 --
154 -- The primary key is invalid therefore we must error
155 --
156 close csr_leg_code;
157 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
158 fnd_message.raise_error;
159 end if;
160 if g_debug then
161 hr_utility.set_location(l_proc,30);
162 end if;
163 --
164 -- Set the global variables so the values are
165 -- available for the next call to this function.
166 --
167 close csr_leg_code;
168 hxc_hrp_bus.g_recurring_period_id:= p_recurring_period_id;
169 hxc_hrp_bus.g_legislation_code := l_legislation_code;
170 end if;
171 if g_debug then
172 hr_utility.set_location(' Leaving:'|| l_proc, 40);
173 end if;
174 return l_legislation_code;
175 end return_legislation_code;
176 --
177 -- ----------------------------------------------------------------------------
178 -- |-----------------------< chk_non_updateable_args >------------------------|
179 -- ----------------------------------------------------------------------------
180 -- {Start Of Comments}
181 --
182 -- Description:
183 -- This procedure is used to ensure that non updateable attributes have
184 -- not been updated. If an attribute has been updated an error is generated.
185 --
186 -- Pre Conditions:
187 -- g_old_rec has been populated with details of the values currently in
188 -- the database.
189 --
190 -- In Arguments:
191 -- p_rec has been populated with the updated values the user would like the
192 -- record set to.
193 --
194 -- Post Success:
195 -- Processing continues if all the non updateable attributes have not
196 -- changed.
197 --
198 -- Post Failure:
199 -- An application error is raised if any of the non updatable attributes
200 -- have been altered.
201 --
202 -- {End Of Comments}
203 -- ----------------------------------------------------------------------------
204 Procedure chk_non_updateable_args
205 (p_effective_date in date
206 ,p_rec in hxc_hrp_shd.g_rec_type
207 ) IS
208 --
209 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
210 l_error EXCEPTION;
211 l_argument varchar2(30);
212 --
213 Begin
214 --
215 -- Only proceed with the validation if a row exists for the current
216 -- record in the HR Schema.
217 --
218 IF NOT hxc_hrp_shd.api_updating
219 (p_recurring_period_id => p_rec.recurring_period_id
220 ,p_object_version_number => p_rec.object_version_number
221 ) THEN
222 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
223 fnd_message.set_token('PROCEDURE ', l_proc);
224 fnd_message.set_token('STEP ', '5');
225 fnd_message.raise_error;
226 END IF;
227 --
228 -- EDIT_HERE: Add checks to ensure non-updateable args have
229 -- not been updated.
230 --
231 EXCEPTION
232 WHEN l_error THEN
233 hr_api.argument_changed_error
234 (p_api_name => l_proc
235 ,p_argument => l_argument);
236 WHEN OTHERS THEN
237 RAISE;
238 End chk_non_updateable_args;
239 --
240 -- ----------------------------------------------------------------------------
241 -- |-----------------------------< chk_name >---------------------------------|
242 -- ----------------------------------------------------------------------------
243 -- {Start Of Comments}
244 --
245 -- Description:
249 -- Pre Conditions:
246 -- This procedure ensures that a valid and a unique Recurring period name
247 -- has been entered
248 --
250 -- None
251 --
252 -- In Arguments:
253 -- name
254 -- object_version_number
255 --
256 -- Post Success:
257 -- Processing continues if a valid and a unique name has been entered
258 --
259 -- Post Failure:
260 -- An application error is raised if the name is not valid
261 --
262 -- {End Of Comments}
263 -- ----------------------------------------------------------------------------
264 Procedure chk_name
265 (
266 p_name in hxc_recurring_periods.name%TYPE,
267 p_object_version_number in hxc_recurring_periods.object_version_number%TYPE
268 ) IS
269 --
270 l_proc varchar2(72);
271 --
272 -- cursor to check that a duplicate period name is not entered
273 --
274 CURSOR csr_chk_name IS
275 SELECT 'error'
276 FROM sys.dual
277 WHERE EXISTS (
278 SELECT 'x'
279 FROM hxc_recurring_periods hrp
280 WHERE hrp.name = p_name
281 AND hrp.object_version_number <> NVL(p_object_version_number, -1) );
282 --
283 l_error varchar2(5) := NULL;
284 --
285 BEGIN
286 g_debug:=hr_utility.debug_enabled;
287 if g_debug then
288 l_proc := g_package||'chk_name';
289 hr_utility.set_location('Entering:'||l_proc, 5);
290 end if;
291 --
292 -- Raise error if name is NULL as it is a mandatory field.
293 --
294 IF p_name IS NULL
295 THEN
296 --
297 hr_utility.set_message(809, 'HXC_0079_HRP_PERIOD_NAME_MAND');
298 hr_utility.raise_error;
299 --
300 END IF;
301 if g_debug then
302 hr_utility.set_location('Processing:'||l_proc, 10);
303 end if;
304 --
305 -- Raise an error if the period name is not unique
306 --
307 OPEN csr_chk_name;
308 FETCH csr_chk_name INTO l_error;
309 CLOSE csr_chk_name;
310 --
311 IF l_error IS NOT NULL
312 THEN
313 --
314 hr_utility.set_message(809, 'HXC_0080_HRP_DUP_PERIOD_NAME');
315 hr_utility.raise_error;
316 --
317 END IF;
318 --
319 if g_debug then
320 hr_utility.set_location('Leaving:'||l_proc, 20);
321 end if;
322 --
323 END chk_name;
324 --
325 -- ----------------------------------------------------------------------------
326 -- |-----------------------------< chk_period_type >------------------------|
327 -- ----------------------------------------------------------------------------
328 -- {Start Of Comments}
329 --
330 -- Description:
331 -- This chk procedure validates that the entered period type, if one exists,
332 -- is valid within the per_time_period_types table, which is part of shared HR.
333 -- This is used as a foreign key by the timecard screen and other OTC back end
334 -- processes to work out the period dates for timecards etc.
335 --
336 -- Pre Conditions:
337 -- None
338 --
339 -- In Arguments:
340 -- period_type
341 --
342 -- Post Success:
343 -- Processing continues if a valid period type exists within per_time_period
344 -- types.
345 --
346 -- Post Failure:
347 -- An application error is raised if the period type is not present.
348 --
349 -- {End Of Comments}
350 -- ----------------------------------------------------------------------------
351 Procedure chk_period_type
352 (
353 p_period_type in hxc_recurring_periods.period_type%TYPE
354 ) IS
355 --
356 -- Validation cursor, Bi Months are not currently supported
357 -- by the self service OTC code. Remove the extra check when
358 -- they are.
359 --
360 cursor c_period_type(
361 p_period_type in HXC_RECURRING_PERIODS.PERIOD_TYPE%TYPE
362 ) is
363 select 'Y'
364 from PER_TIME_PERIOD_TYPES ptpt
365 where ptpt.period_type = p_period_type
366 and ptpt.period_type <> 'Bi-Month';
367
368 l_proc varchar2(72) ;
369 l_error varchar2(5) := NULL;
370 --
371 BEGIN
372 g_debug:=hr_utility.debug_enabled;
373 if g_debug then
374 l_proc:= g_package||'chk_period_type';
375 hr_utility.set_location('Entering:'||l_proc, 5);
376 end if;
377 --
378 -- Check to see if this is a valid per time period type
379 --
380
381 if p_period_type is not null then
382
383 open c_period_type(p_period_type);
384 fetch c_period_type into l_error;
385 if g_debug then
386 hr_utility.set_location(l_proc, 10);
387 end if;
388
389 if c_period_type%NOTFOUND then
390 --
391 -- This isn't a valid period type, raise an error.
392 --
393 if g_debug then
394 hr_utility.set_location(l_proc, 15);
395 end if;
396 close c_period_type;
397 FND_MESSAGE.SET_NAME('HXC','HXC_0152_INVALID_PERIOD_TYPE');
398 FND_MESSAGE.RAISE_ERROR;
399 else
400 if g_debug then
401 hr_utility.set_location(l_proc, 20);
402 end if;
403 close c_period_type;
404 end if;
405
406 end if;
407
408 if g_debug then
409 hr_utility.set_location('Leaving:'||l_proc, 30);
410 end if;
411 --
412 END chk_period_type;
413 --
414 -- ----------------------------------------------------------------------------
415 -- |-----------------------------< chk_type_duration >------------------------|
416 -- ----------------------------------------------------------------------------
417 -- {Start Of Comments}
418 --
422 --
419 -- Description:
420 -- This procedure ensures that either PERIOD TYPE or DURATION IN DAYS
421 -- has been entered
423 -- Pre Conditions:
424 -- None
425 --
426 -- In Arguments:
427 -- period_type
428 -- duration_in_days
429 --
430 -- Post Success:
431 -- Processing continues if either type or duration for the period has
432 -- been entered
433 --
434 -- Post Failure:
435 -- An application error is raised if both type and duration are NULL
436 --
437 -- {End Of Comments}
438 -- ----------------------------------------------------------------------------
439 Procedure chk_type_duration
440 (
441 p_period_type in hxc_recurring_periods.period_type%TYPE,
442 p_duration_in_days in hxc_recurring_periods.duration_in_days%TYPE
443 ) IS
444 --
445 l_proc varchar2(72);
446 l_error varchar2(5) := NULL;
447 --
448 BEGIN
449 g_debug:=hr_utility.debug_enabled;
450 if g_debug then
451 l_proc := g_package||'chk_type_duration';
452 hr_utility.set_location('Entering:'||l_proc, 5);
453 end if;
454 --
455 -- Raise error if both type and duration are null for a period.
456 --
457 IF p_period_type IS NULL
458 AND p_duration_in_days IS NULL
459 THEN
460 --
461 hr_utility.set_message(809, 'HXC_0081_HRP_TYPE_OR_DAYS_MAND');
462 hr_utility.raise_error;
463 --
464 END IF;
465 if g_debug then
466 hr_utility.set_location('Leaving:'||l_proc, 10);
467 end if;
468 --
469 END chk_type_duration;
470 --
471 -- ----------------------------------------------------------------------------
472 -- |-----------------------< chk_delete >-------------------------------------|
473 -- ----------------------------------------------------------------------------
474 -- {Start Of Comments}
475 --
476 -- Description:
477 -- This procedure carries out delete time refential integrity checks
478 --
479 -- Pre Conditions:
480 -- None
481 --
482 -- In Arguments:
483 -- recurring_period_id
484 --
485 -- Post Success:
486 -- Processing continues if the recurring period name is not being referenced
487 --
488 -- Post Failure:
489 -- An application error is raised if the recurring period is being used.
490 --
491 -- {End Of Comments}
492 -- ----------------------------------------------------------------------------
493 Procedure chk_delete
494 (
495 p_recurring_period_id in hxc_recurring_periods.recurring_period_id%TYPE
496 ) IS
497 --
498 l_proc varchar2(72);
499 --
500 /*
501 CURSOR csr_chk_apc IS
502 SELECT 'exists'
503 FROM hxc_approval_period_comps
504 WHERE recurring_period_id = p_recurring_period_id;
505 */
506 --
507 l_exists VARCHAR2(6) := NULL;
508 --
509 BEGIN
510 g_debug:=hr_utility.debug_enabled;
511 if g_debug then
512 l_proc := g_package||'chk_delete';
513 hr_utility.set_location('Entering:'||l_proc, 5);
514 end if;
515 --
516 -- check that recurring period is not being used
517 --
518 /* OPEN csr_chk_apc;
519 FETCH csr_chk_apc INTO l_exists;
520 CLOSE csr_chk_apc;*/
521 if g_debug then
522 hr_utility.set_location('Calling num_hierarchy_occurances: '||l_proc, 10);
523 end if;
524 l_exists := HXC_PREFERENCE_EVALUATION.num_hierarchy_occurances
525 ('TC_W_TCRD_PERIOD'
526 ,1
527 ,TO_CHAR(p_recurring_period_id));
528 if g_debug then
529 hr_utility.set_location('After calling num_hierarchy_occurances:'||l_proc,20);
530 end if;
531 --
532 if g_debug then
533 hr_utility.set_location('Processing: '||l_proc, 10);
534 end if;
535 --
536 IF l_exists <> 0 THEN
537 --
538 hr_utility.set_message(809, 'HXC_0082_HRP_PERIOD_IN_APRSET');
539 hr_utility.raise_error;
540 --
541 END IF;
542 --
543 if g_debug then
544 hr_utility.set_location('Entering:'||l_proc, 5);
545 end if;
546 --
547 END chk_delete;
548
549 --
550 -- ----------------------------------------------------------------------------
551 -- |---------------------------< insert_validate >----------------------------|
552 -- ----------------------------------------------------------------------------
553 Procedure insert_validate
554 (p_effective_date in date
555 ,p_rec in hxc_hrp_shd.g_rec_type
556 ) is
557 --
558 l_proc varchar2(72);
559 --
560 Begin
561 g_debug:=hr_utility.debug_enabled;
562 if g_debug then
563 l_proc := g_package||'insert_validate';
564 hr_utility.set_location('Entering:'||l_proc, 5);
565 end if;
566 --
567 -- No business group context. HR_STANDARD_LOOKUPS used for validation.
568 -- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS.
569 --
570 -- Call all supporting business operations
571 --
572 --
573 if g_debug then
574 hr_utility.set_location('Processing:'||l_proc, 10);
575 end if;
576 --
577 chk_name ( p_name => p_rec.name,
578 p_object_version_number => p_rec.object_version_number );
579 --
580 if g_debug then
581 hr_utility.set_location(' Leaving:'||l_proc, 10);
582 end if;
583 --
584 if g_debug then
585 hr_utility.set_location(' Processing:'||l_proc, 15);
586 end if;
587 --
588 chk_period_type
589 (p_period_type => p_rec.period_type);
590 --
591 if g_debug then
592 hr_utility.set_location(' Processing:'||l_proc, 20);
593 end if;
594 --
595 if g_debug then
596 hr_utility.set_location('Processing:'||l_proc, 25);
597 end if;
598 --
599
600 chk_type_duration ( p_period_type => p_rec.period_type,
601 p_duration_in_days => p_rec.duration_in_days );
602 --
603 if g_debug then
604 hr_utility.set_location(' Leaving:'||l_proc, 30);
605 end if;
606 --
607
608 End insert_validate;
609 --
610 -- ----------------------------------------------------------------------------
611 -- |---------------------------< update_validate >----------------------------|
612 -- ----------------------------------------------------------------------------
613 Procedure update_validate
614 (p_effective_date in date
615 ,p_rec in hxc_hrp_shd.g_rec_type
616 ) is
617 --
618 l_proc varchar2(72);
619 --
620 Begin
621 g_debug:=hr_utility.debug_enabled;
622 if g_debug then
623 l_proc := g_package||'update_validate';
624 hr_utility.set_location('Entering:'||l_proc, 5);
625 end if;
626 --
627 -- Call all supporting business operations
628 --
629 if g_debug then
630 hr_utility.set_location('Processing:'||l_proc, 10);
631 end if;
632 --
633 chk_name ( p_name => p_rec.name,
634 p_object_version_number => p_rec.object_version_number );
635 --
636 if g_debug then
637 hr_utility.set_location(' Leaving:'||l_proc, 15);
638 --
639 hr_utility.set_location(' Processing:'||l_proc, 20);
640 end if;
641 --
642 chk_period_type
643 (p_period_type => p_rec.period_type);
644 --
645 if g_debug then
646 hr_utility.set_location(' Processing:'||l_proc, 25);
647 --
648 hr_utility.set_location('Processing:'||l_proc, 30);
649 end if;
650 --
651
652 chk_type_duration ( p_period_type => p_rec.period_type,
653 p_duration_in_days => p_rec.duration_in_days );
654 --
655 if g_debug then
656 hr_utility.set_location(' Leaving:'||l_proc, 35);
657 end if;
658 --
659 -- " No business group context. HR_STANDARD_LOOKUPS used for validation."
660 -- " CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
661 --
662 chk_non_updateable_args
663 (p_effective_date => p_effective_date
664 ,p_rec => p_rec
665 );
666 --
667 --
668 if g_debug then
669 hr_utility.set_location(' Leaving:'||l_proc, 40);
670 end if;
671 End update_validate;
672 --
673 -- ----------------------------------------------------------------------------
674 -- |---------------------------< delete_validate >----------------------------|
675 -- ----------------------------------------------------------------------------
676 Procedure delete_validate
677 (p_rec in hxc_hrp_shd.g_rec_type
678 ) is
679 --
680 l_proc varchar2(72);
681 --
682 Begin
683 g_debug:=hr_utility.debug_enabled;
684 if g_debug then
685 l_proc := g_package||'delete_validate';
686 hr_utility.set_location('Entering:'||l_proc, 5);
687 end if;
688 --
689 -- Call all supporting business operations
690 --
691 chk_delete
692 (
693 p_recurring_period_id => p_rec.recurring_period_id
694 );
695 --
696 if g_debug then
697 hr_utility.set_location(' Leaving:'||l_proc, 10);
698 end if;
699 End delete_validate;
700 --
701 end hxc_hrp_bus;
[Home] [Help]