1 Package Body hxc_hrr_bus as
2 /* $Header: hxchrrrhi.pkb 120.3 2005/09/23 10:43:47 sechandr noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hxc_hrr_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_resource_rule_id number default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure set_security_group_id
22 (p_resource_rule_id in number
23 ) is
24 --
25 -- Declare cursor
26 --
27 -- EDIT_HERE In the following cursor statement add join(s) between
28 -- hxc_resource_rules 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_resource_rules hrr
36 -- , EDIT_HERE table_name(s) 333
37 where hrr.resource_rule_id = p_resource_rule_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 => 'resource_rule_id'
58 ,p_argument_value => p_resource_rule_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_resource_rule_id in number
94 )
95 Return Varchar2 Is
96 --
97 -- Declare cursor
98 --
99 -- EDIT_HERE In the following cursor statement add join(s) between
100 -- hxc_resource_rules 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_resource_rules hrr
108 -- , EDIT_HERE table_name(s) 333
109 where hrr.resource_rule_id = p_resource_rule_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 => 'resource_rule_id'
130 ,p_argument_value => p_resource_rule_id
131 );
132 --
133 if ( nvl(hxc_hrr_bus.g_resource_rule_id, hr_api.g_number)
134 = p_resource_rule_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_hrr_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_hrr_bus.g_resource_rule_id := p_resource_rule_id;
169 hxc_hrr_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_hrr_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_hrr_shd.api_updating
219 (p_resource_rule_id => p_rec.resource_rule_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:
246 -- This procedure ensures that a valid and a unique Resource rule name
247 -- has been entered
248 --
249 -- Pre Conditions:
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_resource_rules.name%TYPE,
267 p_business_group_id in hxc_resource_rules.business_group_id%TYPE,
268 p_object_version_number in hxc_resource_rules.object_version_number%TYPE
269 ) IS
270 --
271 l_proc varchar2(72);
272 --
273 -- cursor to check that a duplicate resource rule name is not entered
274 --
275 CURSOR csr_chk_name IS
276 SELECT 'error'
277 FROM sys.dual
278 WHERE EXISTS (
279 SELECT 'x'
280 FROM hxc_resource_rules hrr
281 WHERE hrr.name = p_name
282 AND (hrr.business_group_id = p_business_group_id or hrr.business_group_id is null)
283 AND hrr.object_version_number <> NVL(p_object_version_number, -1) );
284 --
285 l_error varchar2(5) := NULL;
286 --
287 BEGIN
288 g_debug:=hr_utility.debug_enabled;
289 if g_debug then
290 l_proc := g_package||'chk_name';
291 hr_utility.set_location('Entering:'||l_proc, 5);
292 end if;
293 --
294 -- Raise error if name is NULL as it is a mandatory field.
295 --
296 IF p_name IS NULL
297 THEN
298 --
299 hr_utility.set_message(809, 'HXC_0083_HRR_RULE_NAME_MAND');
300 hr_utility.raise_error;
301 --
302 END IF;
303 if g_debug then
304 hr_utility.set_location('Processing:'||l_proc, 10);
305 end if;
306 --
307 -- Raise an error if the resource rule name is not unique
308 --
309 OPEN csr_chk_name;
310 FETCH csr_chk_name INTO l_error;
311 CLOSE csr_chk_name;
312 --
313 IF l_error IS NOT NULL
314 THEN
315 --
316 hr_utility.set_message(809, 'HXC_0084_HRR_DUP_RESC_RL_NAME');
317 hr_utility.raise_error;
318 --
319 END IF;
320 --
321 if g_debug then
322 hr_utility.set_location('Leaving:'||l_proc, 20);
323 end if;
324 --
325 END chk_name;
326 --
327 -- ----------------------------------------------------------------------------
328 -- |-< chk_dates >------------------------------------------------------------|
329 -- ----------------------------------------------------------------------------
330 --
331 -- {Start Of Comments}
332 --
333 -- Description:
334 -- This procedure performs basic checks on the assignment dates to ensure
335 -- that they conform with the business rules.
336 -- At the moment the only business rule enforced in this procedure is that
337 -- the end date must be >= the start date
338 --
339 -- Prerequisites:
340 -- None.
341 --
342 -- In Parameters:
343 -- p_resource_rule_id
344 -- p_start_date
345 -- p_end_date
346 --
347 -- Post Success:
348 -- Processing continues.
349 --
350 -- Post Failure:
351 -- An exception is raised.
352 --
353 -- Developer Implementation Notes:
354 -- None.
355 --
356 -- Access Status:
357 -- Internal Row Handler Use Only.
358 --
359 -- {End Of Comments}
360 -- ----------------------------------------------------------------------------
361 --
362 PROCEDURE chk_dates
363 (p_resource_rule_id
364 IN hxc_resource_rules.resource_rule_id%TYPE
365 ,p_start_date
366 IN hxc_resource_rules.start_date%TYPE
367 ,p_end_date
368 IN hxc_resource_rules.end_date%TYPE
369 )
370 IS
371 --
372 CURSOR c_get_dates
373 IS
374 SELECT start_date
375 ,end_date
376 FROM hxc_resource_rules
377 WHERE resource_rule_id = p_resource_rule_id;
378 --
379 BEGIN
380 --
381 -- check that the start date is not greater than the end date
382 --
383 IF p_start_date > NVL(p_end_date, hr_general.END_OF_TIME) THEN
384 --
385 hr_utility.set_message
386 (809
387 ,'HXC_0085_HRR_START_DT_ERR'
388 );
389 hr_utility.raise_error;
390 --
391 END IF;
392 --
393 END chk_dates;
394 --
395 -- ----------------------------------------------------------------------------
396 -- |-----------------------< chk_rule_evaluation_order >----------------------|
397 -- ----------------------------------------------------------------------------
398 -- {Start Of Comments}
399 --
400 -- Description:
401 -- This procedure ensures that a valid and a unique Rule Evaluation Order
402 -- has been entered for each resource rule
403 --
404 -- Pre Conditions:
405 -- None
406 --
407 -- In Arguments:
408 -- rule_evaluation_order
409 -- object_version_number
410 --
411 -- Post Success:
412 -- Processing continues if a valid and a unique rule evaluation order
413 -- has been entered
414 --
415 -- Post Failure:
416 -- An application error is raised if the rule evaluation order is not
417 -- unique
418 --
419 -- {End Of Comments}
420 -- ----------------------------------------------------------------------------
421 Procedure chk_rule_evaluation_order
422 (
423 p_rule_evaluation_order in hxc_resource_rules.rule_evaluation_order%TYPE,
424 p_business_group_id in hxc_resource_rules.business_group_id%TYPE,
425 p_object_version_number in hxc_resource_rules.object_version_number%TYPE
426 ) IS
427 --
428 l_proc varchar2(72) := g_package||'chk_rule_evaluation_order';
429 --
430 -- cursor to check that a duplicate rule evaluation order is not entered
431 --
432 CURSOR csr_chk_rule_evaluation_order IS
433 SELECT 'error'
434 FROM sys.dual
435 WHERE EXISTS (
436 SELECT 'x'
437 FROM hxc_resource_rules hrr
438 WHERE hrr.rule_evaluation_order = p_rule_evaluation_order
439 AND (hrr.business_group_id = p_business_group_id or hrr.business_group_id is null)
440 AND hrr.object_version_number <> NVL(p_object_version_number, -1) );
441 --
442 l_error varchar2(5) := NULL;
443 --
444 BEGIN
445 g_debug:=hr_utility.debug_enabled;
446 if g_debug then
447 hr_utility.set_location('Entering:'||l_proc, 5);
448 end if;
449 --
450 -- Raise an error if the rule evaluation order is not unique
451 --
452 IF (p_rule_evaluation_order <> 0) THEN
453 OPEN csr_chk_rule_evaluation_order;
454 FETCH csr_chk_rule_evaluation_order INTO l_error;
455 CLOSE csr_chk_rule_evaluation_order;
456 END IF;
457 --
458 IF l_error IS NOT NULL
459 THEN
460 --
461 hr_utility.set_message(809, 'HXC_0129_HRR_DUP_RL_EVL_ORD');
462 hr_utility.raise_error;
463 --
464 END IF;
465 --
466 if g_debug then
467 hr_utility.set_location('Leaving:'||l_proc, 10);
468 end if;
469 --
470 END chk_rule_evaluation_order;
471 --
472 --
473 -- -----------------------------------------------------------------
474 -- |-----------------------< chk_legislation_code >----------------|
475 -- -----------------------------------------------------------------
476 --
477 -- Description:
478 -- Validate the legislation_code against the FND_TERRITORIES table.
479 --
480 -- Pre-Requisites:
481 -- None
482 --
483 -- In Parameters:
484 -- p_legislation_code
485 --
486 -- Post Success:
487 -- Processing continues if the legislation_code is valid.
488 --
489 -- Post Failure:
490 -- An application error is raised and processing is terminated if
491 -- the legislation_code is invalid.
492 --
493 -- Access Status:
494 -- Internal Row Handler Use Only.
495 --
496 Procedure chk_legislation_code
497 (p_business_group_id in number,
498 p_legislation_code in varchar2
499 ) is
500 --
501 -- Local declarations
502 l_proc varchar2(72);
503 l_territory_code fnd_territories.territory_code%TYPE;
504 l_lc per_business_groups.legislation_code%TYPE;
505 --
506 -- Setup cursor for valid legislation code check
507 cursor csr_valid_legislation_code is
508 select territory_code
509 from fnd_territories ft
510 where ft.territory_code = p_legislation_code;
511
512 -- Setup cursor for valid legislation code for a particular business_group
513 cursor csr_valid_bg_lc is
514 select legislation_code
515 from per_business_groups pbg
516 where pbg.business_group_id = p_business_group_id
517 and pbg.legislation_code = p_legislation_code;
518
519 --
520 --
521 begin
522 g_debug:=hr_utility.debug_enabled;
523 if g_debug then
524 l_proc := g_package||'chk_legislation_code';
525 hr_utility.set_location('Entering: '||l_proc,5);
526 end if;
527 --------------------------------
528 -- Check legislation code is valid --
529 --------------------------------
530 if p_legislation_code is not null then
531
532 open csr_valid_legislation_code;
533 fetch csr_valid_legislation_code into l_territory_code;
534 if csr_valid_legislation_code%notfound then
535 close csr_valid_legislation_code;
536 hr_utility.set_message(800,'PER_52123_AMD_LEG_CODE_INV');
537 hr_utility.raise_error;
538 end if; -- End cursor if
539 close csr_valid_legislation_code;
540
541 if p_business_group_id is not null then
542 open csr_valid_bg_lc;
543 fetch csr_valid_bg_lc into l_lc;
544 if csr_valid_bg_lc%notfound then
545 close csr_valid_bg_lc;
546 hr_utility.set_message(800,'PER_52123_AMD_LEG_CODE_INV');
547 hr_utility.raise_error;
548 end if; -- End cursor if
549 close csr_valid_bg_lc;
550 end if;
551
552 end if; -- end check
553
554 if g_debug then
555 hr_utility.set_location('Leaving: '||l_proc,10);
556 end if;
557 end chk_legislation_code;
558 --
559 -- ----------------------------------------------------------------------------
560 -- |---------------------------< insert_validate >----------------------------|
561 -- ----------------------------------------------------------------------------
562 Procedure insert_validate
563 (p_effective_date in date
564 ,p_rec in hxc_hrr_shd.g_rec_type
565 ) is
566 --
567 l_proc varchar2(72);
568 --
569 Begin
570 g_debug:=hr_utility.debug_enabled;
571 if g_debug then
572 l_proc := g_package||'insert_validate';
573 hr_utility.set_location('Entering:'||l_proc, 5);
574 end if;
575 --
576 -- Call all supporting business operations
577 --
578 if g_debug then
579 hr_utility.set_location('Processing:'||l_proc, 10);
580 end if;
581 --
582 -- Do checks for the unique resource rule name
583 --
584 chk_name ( p_name => p_rec.name,
585 p_business_group_id => p_rec.business_group_id,
586 p_object_version_number => p_rec.object_version_number );
587 --
588 if g_debug then
589 hr_utility.set_location(' Leaving:'||l_proc, 10);
590 end if;
591 --
592 --
593 -- Do checks on the dates of the resource rule
594 --
595 chk_dates
596 (p_resource_rule_id => p_rec.resource_rule_id
597 ,p_start_date => p_rec.start_date
598 ,p_end_date => p_rec.end_date
599 );
600 --
601 -- Do checks for the unique rule_evaluation_order
602 --
603 chk_rule_evaluation_order(p_rule_evaluation_order =>p_rec.rule_evaluation_order,
604 p_business_group_id => p_rec.business_group_id,
605 p_object_version_number => p_rec.object_version_number );
606 --
607 -- EDIT_HERE: As this table does not have a mandatory business_group_id
608 -- column, ensure client_info is populated by calling a suitable
609 -- ???_???_bus.set_security_group_id procedure, or add one of the following
610 -- comments:
611 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
612 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
613 --
614 --
615 if p_rec.business_group_id is not null then
616 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
617 end if;
618 -- Validate the legislation_code
619 chk_legislation_code
620 (p_business_group_id => p_rec.business_group_id,
621 p_legislation_code => p_rec.legislation_code);
622 --
623 --
624 if g_debug then
625 hr_utility.set_location(' Leaving:'||l_proc, 10);
626 end if;
627 End insert_validate;
628 --
629 -- ----------------------------------------------------------------------------
630 -- |---------------------------< update_validate >----------------------------|
631 -- ----------------------------------------------------------------------------
632 Procedure update_validate
633 (p_effective_date in date
634 ,p_rec in hxc_hrr_shd.g_rec_type
635 ) is
636 --
637 l_proc varchar2(72);
638 --
639 Begin
640 g_debug:=hr_utility.debug_enabled;
641 if g_debug then
642 l_proc := g_package||'update_validate';
643 hr_utility.set_location('Entering:'||l_proc, 5);
644 end if;
645 --
646 -- Call all supporting business operations
647 --
648 --
649 -- EDIT_HERE: As this table does not have a mandatory business_group_id
650 -- column, ensure client_info is populated by calling a suitable
651 -- ???_???_bus.set_security_group_id procedure, or add one of the following
652 -- comments:
653 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
654 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
655 --
656 chk_non_updateable_args
657 (p_effective_date => p_effective_date
658 ,p_rec => p_rec
659 );
660 --
661 -- Do check for duplicate resource rule name
662 --
663 if g_debug then
664 hr_utility.set_location('Processing:'||l_proc, 10);
665 end if;
666 --
667 chk_name ( p_name => p_rec.name,
668 p_business_group_id => p_rec.business_group_id,
669 p_object_version_number => p_rec.object_version_number );
670 --
671 if g_debug then
672 hr_utility.set_location(' Leaving:'||l_proc, 10);
673 end if;
674 --
675 -- Do checks on the dates of the resource rule
676 --
677 chk_dates
678 (p_resource_rule_id => p_rec.resource_rule_id
679 ,p_start_date => p_rec.start_date
680 ,p_end_date => p_rec.end_date
681 );
682 --
683 -- Do checks for the unique rule evaluation order
684 --
685 chk_rule_evaluation_order(p_rule_evaluation_order =>p_rec.rule_evaluation_order,
686 p_business_group_id => p_rec.business_group_id,
687 p_object_version_number => p_rec.object_version_number );
688 --
689 --
690 if p_rec.business_group_id is not null then
691 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
692 end if;
693 -- Validate the legislation_code
694 chk_legislation_code
695 (p_business_group_id => p_rec.business_group_id,
696 p_legislation_code => p_rec.legislation_code);
697 --
698 if g_debug then
699 hr_utility.set_location(' Leaving:'||l_proc, 10);
700 end if;
701 End update_validate;
702 --
703 -- ----------------------------------------------------------------------------
704 -- |---------------------------< delete_validate >----------------------------|
705 -- ----------------------------------------------------------------------------
706 Procedure delete_validate
707 (p_rec in hxc_hrr_shd.g_rec_type
708 ) is
709 --
710 l_proc varchar2(72);
711 --
712 Begin
713 g_debug:=hr_utility.debug_enabled;
714 if g_debug then
715 l_proc := g_package||'delete_validate';
716 hr_utility.set_location('Entering:'||l_proc, 5);
717 end if;
718 --
719 -- Call all supporting business operations
720 --
721 if g_debug then
722 hr_utility.set_location(' Leaving:'||l_proc, 10);
723 end if;
724 End delete_validate;
725 --
726 end hxc_hrr_bus;
[Home] [Help]