1 Package Body per_bbt_bus as
2 /* $Header: pebbtrhi.pkb 115.7 2002/12/02 13:20:16 apholt noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_bbt_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_balance_type_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_balance_type_id in number
22 ) is
23 --
24 -- Declare cursor
25 --
26 cursor csr_sec_grp is
27 select inf.org_information14
28 from hr_organization_information inf
29 , per_bf_balance_types bbt
30 where bbt.balance_type_id = p_balance_type_id
31 and inf.organization_id = bbt.business_group_id
32 and inf.org_information_context || '' = 'Business Group Information';
33 --
34 -- Declare local variables
35 --
36 l_security_group_id number;
37 l_proc varchar2(72) := g_package||'set_security_group_id';
38 --
39 begin
40 --
41 hr_utility.set_location('Entering:'|| l_proc, 10);
42 --
43 -- Ensure that all the mandatory parameter are not null
44 --
45 hr_api.mandatory_arg_error(p_api_name => l_proc
46 ,p_argument => 'BALANCE_TYPE_ID'
47 ,p_argument_value => p_balance_type_id);
48 --
49 open csr_sec_grp;
50 fetch csr_sec_grp into l_security_group_id;
51 --
52 if csr_sec_grp%notfound then
53 --
54 close csr_sec_grp;
55 --
56 -- The primary key is invalid therefore we must error
57 --
58 fnd_message.set_name('PER','HR_7220_INVALID_PRIMARY_KEY');
59 fnd_message.raise_error;
60 --
61 end if;
62 close csr_sec_grp;
63 --
64 -- Set the security_group_id in CLIENT_INFO
65 --
66 hr_api.set_security_group_id
67 (p_security_group_id => l_security_group_id
68 );
69 --
70 hr_utility.set_location(' Leaving:'|| l_proc, 20);
71 --
72 end set_security_group_id;
73 --
74 -- ---------------------------------------------------------------------------
75 -- |---------------------< return_legislation_code >-------------------------|
76 -- ---------------------------------------------------------------------------
77 --
78 Function return_legislation_code
79 (p_balance_type_id in number
80 )
81 Return Varchar2 Is
82 --
83 -- Declare cursor
84 --
85 cursor csr_leg_code is
86 select pbg.legislation_code
87 from per_business_groups pbg
88 , per_bf_balance_types bbt
89 where bbt.balance_type_id = p_balance_type_id
90 and pbg.business_group_id = bbt.business_group_id;
91 --
92 -- Declare local variables
93 --
94 l_legislation_code varchar2(150);
95 l_proc varchar2(72) := g_package||'return_legislation_code';
96 --
97 Begin
98 --
99 hr_utility.set_location('Entering:'|| l_proc, 10);
100 --
101 -- Ensure that all the mandatory parameter are not null
102 --
103 hr_api.mandatory_arg_error(p_api_name => l_proc
104 ,p_argument => 'BALANCE_TYPE_ID'
105 ,p_argument_value => p_balance_type_id);
106 --
107 if ( nvl(g_balance_type_id, hr_api.g_number)
108 = p_balance_type_id) then
109 --
110 -- The legislation code has already been found with a previous
111 -- call to this function. Just return the value in the global
112 -- variable.
113 --
114 l_legislation_code := g_legislation_code;
115 hr_utility.set_location(l_proc, 20);
116 else
117 --
118 -- The ID is different to the last call to this function
119 -- or this is the first call to this function.
120 --
121 open csr_leg_code;
122 fetch csr_leg_code into l_legislation_code;
123 --
124 if csr_leg_code%notfound then
125 --
126 -- The primary key is invalid therefore we must error
127 --
128 close csr_leg_code;
129 fnd_message.set_name('PER','HR_7220_INVALID_PRIMARY_KEY');
130 fnd_message.raise_error;
131 end if;
132 hr_utility.set_location(l_proc,30);
133 --
134 -- Set the global variables so the values are
135 -- available for the next call to this function.
136 --
137 close csr_leg_code;
138 g_balance_type_id := p_balance_type_id;
139 g_legislation_code := l_legislation_code;
140 end if;
141 hr_utility.set_location(' Leaving:'|| l_proc, 40);
142 return l_legislation_code;
143 end return_legislation_code;
144 --
145 -- ----------------------------------------------------------------------------
146 -- |----------------------< check_non_updateable_args >-----------------------|
147 -- ----------------------------------------------------------------------------
148 -- {Start Of Comments}
149 --
150 -- Description:
151 -- This procedure is used to ensure that non updateable attributes have
152 -- not been updated. If an attribute has been updated an error is generated.
153 --
154 -- Pre Conditions:
155 -- g_old_rec has been populated with details of the values currently in
156 -- the database.
157 --
158 -- In Arguments:
159 -- p_rec has been populated with the updated values the user would like the
160 -- record set to.
161 --
162 -- Post Success:
163 -- Processing continues if all the non updateable attributes have not
164 -- changed.
165 --
166 -- Post Failure:
167 -- An application error is raised if any of the non updatable attributes
168 -- have been altered.
169 --
170 -- {End Of Comments}
171 -- ----------------------------------------------------------------------------
172 Procedure chk_non_updateable_args
173 (p_rec in per_bbt_shd.g_rec_type
174 ) IS
175 --
176 l_proc varchar2(72) := g_package || 'check_non_updateable_args';
177 l_error EXCEPTION;
178 l_argument varchar2(30);
179 --
180 Begin
181 hr_utility.set_location('Entering:'||l_proc,5);
182 --
183 -- Only proceed with the validation if a row exists for the current
184 -- record in the HR Schema.
185 --
186 IF NOT per_bbt_shd.api_updating
187 (p_balance_type_id => p_rec.balance_type_id
188 ,p_object_version_number => p_rec.object_version_number
189 ) THEN
190 hr_utility.set_message(800, 'HR_6153_ALL_PROCEDURE_FAIL');
191 hr_utility.set_message_token('PROCEDURE ', l_proc);
192 hr_utility.set_message_token('STEP ', '5');
193 END IF;
194 --
195 hr_utility.set_location(l_proc,10);
196 --
197 --
198 EXCEPTION
199 WHEN l_error THEN
200 hr_api.argument_changed_error
201 (p_api_name => l_proc
202 ,p_argument => l_argument);
203 WHEN OTHERS THEN
204 RAISE;
205 hr_utility.set_location(' Leaving:'||l_proc,20);
206 End chk_non_updateable_args;
207 --
208 -- ----------------------------------------------------------------------------
209 -- |------------------------< chk_input_value_details >-----------------------|
210 -- ----------------------------------------------------------------------------
211 -- {Start Of Comments}
212 --
213 -- Description:
214 -- This procedure checks to ensure :
215 -- a) If the column INPUT_VALUE_ID is not null, then a check will be made to
216 -- ensure that the value exists in PAY_INPUT_VALUES_F for a input value
217 -- of the same business group.
218 -- b) If the INPUT_VALUE_ID is not null, and the UOM column is not null, then
219 -- a check will be made to ensure that the UOM is the same as the UOM on
220 -- PAY_INPUT_VALUES_F.
221 -- c) If INPUT_VALUE_ID is not null, and CURRENCY is not null, a check will
222 -- be made to ensure that the output_currency_code on the Element Type
223 -- for the input value is the same.
224 --
225 -- Pre Conditions:
226 --
227 -- In Arguments:
228 -- p_input_value_id
229 -- p_business_group_id
230 -- p_currency_code
231 -- p_uom
232 -- p_effective_date
233 -- Post Success:
234 -- Processing continues if all the non updateable attributes have not
235 -- changed.
236 --
237 -- Post Failure:
238 -- An application error is raised if any of the non updatable attributes
239 -- have been altered.
240 --
241 -- {End Of Comments}
242 -- ----------------------------------------------------------------------------
243 PROCEDURE CHK_INPUT_VALUE_DETAILS
244 (p_input_value_id IN NUMBER
245 ,p_business_group_id IN NUMBER
246 ,p_currency_code IN VARCHAR2
247 ,p_uom IN VARCHAR2
248 ,p_effective_date IN DATE
249 ,p_balance_type_id IN NUMBER
250 ,p_object_version_number IN NUMBER
251 )
252 IS
253 --
254 CURSOR csr_get_input_details IS
255 SELECT iv.effective_start_date
256 ,iv.effective_end_date
257 ,et.output_currency_code
258 ,iv.uom
259 FROM pay_element_types_f et
260 , pay_input_values_f iv
261 WHERE iv.input_value_id = p_input_value_id
262 AND iv.element_type_id= et.element_type_id
263 AND iv.business_group_id = p_business_group_id;
264 --
265 l_output_currency_code VARCHAR2(30);
266 l_uom VARCHAR2(30);
267 l_effective_start_date DATE;
268 l_effective_end_date DATE;
269 l_api_updating BOOLEAN;
270 --
271 BEGIN
272 --
273 l_api_updating := per_bbt_shd.api_updating
274 (p_balance_type_id => p_balance_type_id
275 ,p_object_version_number => p_object_version_number);
276 --
277 -- Run the test when input value is not null
278 -- and input_value is not equal to the default
279 --
280 IF ( p_input_value_id IS NOT NULL
281 AND p_input_value_id <> hr_api.g_number ) THEN
282 --
283 OPEN csr_get_input_details;
284 FETCH csr_get_input_details INTO
285 l_effective_start_date
286 ,l_effective_end_date
287 ,l_output_currency_code
288 , l_uom;
289 --
290 IF csr_get_input_details%NOTFOUND THEN
291 --
292 CLOSE csr_get_input_details;
293 --
294 -- The input_value_id that is being entered isn't in the table
295 -- PAY_INPUT_VALUES_F in the same bg so error.
296 --
297 hr_utility.set_message (800,'HR_52939_INVALID_INPUT_ID');
298 hr_utility.raise_error;
299 --
300 END IF;
301 --
302 IF (p_effective_date < l_effective_start_date
303 OR p_effective_date > l_effective_end_date ) THEN
304 --
305 CLOSE csr_get_input_details;
306 --
307 -- The input value does exist, but isn't valid for the
308 -- effective date that is being inserted.
309 --
310 hr_utility.set_message(800,'HR_52940_IV_OUTSIDE_DATES');
311 hr_utility.raise_error;
312 --
313 END IF;
314 --
315 IF (p_uom IS NOT NULL AND p_uom <> l_uom) THEN
316 --
317 CLOSE csr_get_input_details;
318 --
319 -- The UOM exists and is different than the UOM on the input value.
320 --
321 hr_utility.set_message (800,'HR_52941_UOM_DIFFERENT');
322 hr_utility.raise_error;
323 --
324 END IF;
325 --
326 IF (p_currency_code IS NOT NULL
327 AND p_currency_code <> l_output_currency_code) THEN
328 --
329 CLOSE csr_get_input_details;
330 --
331 -- The currency exists but is different than the currency on the type.
332 --
333 hr_utility.set_message(800,'HR_52942_CURR_DIFFERENT');
334 hr_utility.raise_error;
335 --
336 END IF;
337 --
338 CLOSE csr_get_input_details;
339 --
340 END IF;
341 END CHK_INPUT_VALUE_DETAILS;
342 -- ----------------------------------------------------------------------------
343 -- |------------------------< chk_display_name_uniq >-------------------------|
344 -- ----------------------------------------------------------------------------
345 -- {Start Of Comments}
346 --
347 -- Description:
348 -- This procedure checks to ensure :
349 -- The display_name is unique within a BG.
350 -- Pre Conditions:
351 --
352 -- In Arguments:
353 -- p_balance_type_id
354 -- p_display_name
355 -- p_business_group_id
356 -- Post Success:
357 -- Processing continues if all the non updateable attributes have not
358 -- changed.
359 --
360 -- Post Failure:
361 -- An application error is raised if any of the non updatable attributes
362 -- have been altered.
363 --
364 -- {End Of Comments}
365 -- ----------------------------------------------------------------------------
366 PROCEDURE CHK_DISPLAY_NAME_UNIQ
367 ( p_balance_type_id IN NUMBER
368 , p_object_version_number IN NUMBER
369 , p_displayed_name IN VARCHAR2
370 , p_business_group_id IN NUMBER)
371 IS
372 --
373 l_proc varchar2(72) := g_package || 'chk_display_name_uniq';
374 --
375 CURSOR c_check_display_name_uniq IS
376 SELECT 1
377 FROM per_bf_balance_types
378 WHERE( (p_balance_type_id IS NULL)
379 or (balance_type_id <> p_balance_type_id))
380 AND displayed_name = p_displayed_name
381 AND business_group_id = p_business_group_id ;
382 --
383 l_temp VARCHAR2(1);
384 --
385 l_api_updating BOOLEAN;
386 --
387 BEGIN
388 hr_utility.set_location ('Entering:'|| l_proc, 1);
389 --
390 l_api_updating := per_bbt_shd.api_updating
391 (p_balance_type_id => p_balance_type_id
392 ,p_object_version_number => p_object_version_number);
393 --
394 -- Only perform the tests if inserting or updating when the value has changed
395 --
396 IF ((NOT l_api_updating)
397 OR (l_api_updating
398 AND p_displayed_name <> hr_api.g_varchar2
399 and p_displayed_name IS NOT NULL)) THEN
400 --
401 --
402 -- Check that the business_group_id is not null.
403 --
404 hr_api.mandatory_arg_error
405 (p_api_name => l_proc
406 ,p_argument => 'business_group_id'
407 ,p_argument_value => p_business_group_id
408 );
409 --
410 OPEN c_check_display_name_uniq ;
411 FETCH c_check_display_name_uniq INTO l_temp;
412 --
413 IF c_check_display_name_uniq%FOUND THEN
414 --
415 -- Another row exists with the same displayed name in the same context
416 -- so error.
417 --
418 close c_check_display_name_uniq ;
419 --
420 per_bbt_shd.constraint_error('HR_52611_DISPLAYED_NOT_UNQ');
421 --
422 END IF;
423 --
424 close c_check_display_name_uniq ;
425 END IF;
426 END CHK_DISPLAY_NAME_UNIQ;
427 -- ----------------------------------------------------------------------------
428 -- |-----------------------< chk_internal_name_uniq >-------------------------|
429 -- ----------------------------------------------------------------------------
430 -- {Start Of Comments}
431 --
432 -- Description:
433 -- This procedure checks to ensure :
434 -- The internal_name is unique within a BG.
435 -- Pre Conditions:
436 --
437 -- In Arguments:
438 -- p_balance_type_id
439 -- p_internal_name
440 -- p_business_group_id
441 -- Post Success:
442 -- Processing continues if all the non updateable attributes have not
443 -- changed.
444 --
445 -- Post Failure:
446 -- An application error is raised if any of the non updatable attributes
447 -- have been altered.
448 --
449 -- {End Of Comments}
450 -- ----------------------------------------------------------------------------
451 PROCEDURE CHK_INTERNAL_NAME_UNIQ
452 ( p_balance_type_id IN NUMBER
453 , p_object_version_number IN NUMBER
454 , p_internal_name IN VARCHAR2
455 , p_business_group_id IN NUMBER)
456 IS
457 --
458 l_proc varchar2(72) := g_package || 'chk_internal_name_uniq';
459 --
460 CURSOR c_check_internal_name_uniq IS
461 SELECT 1
462 FROM per_bf_balance_types
463 WHERE( (p_balance_type_id IS NULL)
464 or (balance_type_id <> p_balance_type_id))
465 AND internal_name = p_internal_name
466 AND business_group_id = p_business_group_id ;
467 --
468 l_temp VARCHAR2(1);
469 --
470 l_api_updating BOOLEAN;
471 --
472 BEGIN
473 hr_utility.set_location ('Entering:'|| l_proc, 1);
474 --
475 l_api_updating := per_bbt_shd.api_updating
476 (p_balance_type_id => p_balance_type_id
477 ,p_object_version_number => p_object_version_number);
478 --
479 -- Only perform the tests if inserting or updating when the value has changed
480 --
481 IF ((NOT l_api_updating)
482 OR (l_api_updating
483 AND p_internal_name <> hr_api.g_varchar2
484 and p_internal_name IS NOT NULL)) THEN
485 --
486 --
487 -- Check that the business_group_id is not null.
488 --
489 hr_api.mandatory_arg_error
490 (p_api_name => l_proc
491 ,p_argument => 'business_group_id'
492 ,p_argument_value => p_business_group_id
493 );
494 --
495 OPEN c_check_internal_name_uniq ;
496 FETCH c_check_internal_name_uniq INTO l_temp;
497 --
498 IF c_check_internal_name_uniq%FOUND THEN
499 --
500 -- Another row exists with the same internal name in the same context
501 -- so error.
502 --
503 close c_check_internal_name_uniq ;
504 --
505 per_bbt_shd.constraint_error('HR_52613_INTERNAL_NOT_UNQ') ;
506 --
507 END IF;
508 --
509 close c_check_internal_name_uniq ;
510 END IF;
511 END CHK_INTERNAL_NAME_UNIQ;
512 -- ----------------------------------------------------------------------------
513 -- |------------------------------------< chk_uom >---------------------------|
514 -- ----------------------------------------------------------------------------
515 -- {Start Of Comments}
516 --
517 -- Description:
518 -- If the UOM is not null, check that it is contained in HR_LOOKUPS
519 -- Pre Conditions:
520 --
521 -- In Arguments:
522 -- p_uom
523 -- p_balance_type_id
524 -- p_effective_date
525 -- Post Success:
526 -- Processing continues.
527 --
528 -- Post Failure:
529 -- An application error is raised.
530 --
531 -- {End Of Comments}
532 -- ----------------------------------------------------------------------------
533 PROCEDURE CHK_UOM
534 (p_uom IN VARCHAR2
535 ,p_balance_type_id IN NUMBER
536 ,p_object_version_number IN NUMBER
537 ,p_effective_date IN DATE)
538 IS
539 --
540 CURSOR csr_chk_uom_exists IS
541 SELECT start_date_active, end_date_active, enabled_flag
542 FROM hr_lookups
543 WHERE lookup_type = 'UNITS'
544 and lookup_code = p_uom;
545 --
546 l_start_date DATE;
547 l_end_date DATE;
548 l_enabled_flag VARCHAR2(1);
549 l_api_updating BOOLEAN;
550 --
551 BEGIN
552 --
553 l_api_updating := per_bbt_shd.api_updating
554 (p_balance_type_id => p_balance_type_id
555 ,p_object_version_number => p_object_version_number);
556 --
557 -- Only perform the tests if a uom exists
558 --
559 IF (p_uom IS NOT NULL
560 OR (l_api_updating
561 AND p_uom <> hr_api.g_varchar2
562 and p_uom IS NOT NULL)) THEN
563 --
564 -- Cursor selects the start and end active dates rather than including them
565 -- as part of the where clause in order to give a more meaningful message.
566 --
567 OPEN csr_chk_uom_exists;
568 FETCH csr_chk_uom_exists INTO l_start_date, l_end_date, l_enabled_flag;
569 --
570 IF csr_chk_uom_exists%NOTFOUND THEN
571 --
572 -- The lookup code entered doesn't exist in hr_lookups
573 -- so error.
574 --
575 hr_utility.set_message(800, 'HR_52943_UOM_NOT_EXIST');
576 hr_utility.raise_error;
577 --
578 END IF;
579 --
580 IF l_enabled_flag = 'N' THEN
581 --
582 -- The lookup exists, but isn't enabled
583 --
584 hr_utility.set_message(800,'HR_52609_UOM_LOOKUP_OFF');
585 hr_utility.raise_error;
586 --
587 END IF;
588 --
589 IF p_effective_date not between NVL (l_start_date, hr_api.g_sot)
590 and NVL (l_end_date, hr_api.g_eot) THEN
591 --
592 -- The lookup exists, but it isn't valid for the effective date
593 -- so error.
594 --
595 hr_utility.set_message(800,'HR_52944_UOM_NOT_VALID');
596 hr_utility.raise_error;
597 --
598 END IF;
599 --
600 END IF;
601 END chk_uom;
602 -- ----------------------------------------------------------------------------
603 -- |------------------------------< chk_uom_input >---------------------------|
604 -- ----------------------------------------------------------------------------
605 -- {Start Of Comments}
606 --
607 -- Description:
608 -- If the input_value_id is null, check that the UOM is not null.
609 -- If the UOM is 'M' ensure a currency exists
610 -- Pre Conditions:
611 --
612 -- In Arguments:
613 -- p_uom
614 -- p_input_value_id
615 -- Post Success:
616 -- Processing continues.
617 --
618 -- Post Failure:
619 -- An application error is raised.
620 --
621 -- {End Of Comments}
622 -- ----------------------------------------------------------------------------
623 PROCEDURE CHK_UOM_INPUT
624 (p_uom IN VARCHAR2
625 ,p_currency_code IN VARCHAR2
626 ,p_input_value_id IN NUMBER
627 ,p_balance_type_id IN NUMBER
628 ,p_object_version_number IN NUMBER)
629 IS
630 l_uom VARCHAR2(30) DEFAULT p_uom;
631 l_input_value_id NUMBER DEFAULT p_input_value_id;
632 l_currency_code VARCHAR2(30) DEFAULT p_currency_code;
633 --
634 l_api_updating BOOLEAN;
635 --
636 BEGIN
637 --
638 l_api_updating := per_bbt_shd.api_updating
639 (p_balance_type_id => p_balance_type_id
640 ,p_object_version_number => p_object_version_number);
641 --
642 -- If updating, and the value isn't being altered then use the old value
643 --
644 IF (l_api_updating
645 AND l_input_value_id = hr_api.g_number) THEN
646 --
647 l_input_value_id := per_bbt_shd.g_old_rec.input_value_id;
648 --
649 END IF;
650 --
651 IF (l_api_updating
652 AND l_currency_code = hr_api.g_varchar2) THEN
653 --
654 l_currency_code := per_bbt_shd.g_old_rec.currency;
655 --
656 END IF;
657 --
658 IF (l_api_updating
659 AND l_uom = hr_api.g_varchar2) THEN
660 --
661 l_uom := per_bbt_shd.g_old_rec.uom;
662 --
663 END IF;
664 --
665 IF l_uom IS NULL and l_input_value_id IS NULL THEN
666 --
667 -- If there is not input value, there should be a UOM.
668 --
669 hr_utility.set_message(800, 'HR_52612_UOM_INPUT_BAD');
670 hr_utility.raise_error;
671 END IF;
672 --
673 --
674 IF (l_currency_code IS NULL and l_uom = 'M') THEN
675 -- This handles the case for both inserts and updates
676 hr_utility.set_message(800,'HR_52746_NEED_CURR_UOM');
677 hr_utility.raise_error;
678 END IF;
679 END CHK_UOM_INPUT;
680 -- ----------------------------------------------------------------------------
681 -- |-------------------------------< chk_category >---------------------------|
682 -- ----------------------------------------------------------------------------
683 -- {Start Of Comments}
684 --
685 -- Description:
686 -- If the CATEGORY is not null, check that it is contained in HR_LOOKUPS
687 -- and is valid
688 -- Pre Conditions:
689 --
690 -- In Arguments:
691 -- p_category
692 -- p_effective_date
693 -- Post Success:
694 -- Processing continues.
695 --
696 -- Post Failure:
697 -- An application error is raised.
698 --
699 -- {End Of Comments}
700 -- ----------------------------------------------------------------------------
701 PROCEDURE chk_category
702 (p_category IN VARCHAR2
703 ,p_balance_type_id IN NUMBER
704 ,p_object_version_number IN NUMBER
705 ,p_effective_date IN DATE)
706 IS
707 --
708 CURSOR csr_check_category IS
709 SELECT start_date_active, end_date_active , enabled_flag
710 FROM hr_lookups
711 WHERE lookup_type = 'BACKFEED_BT_CATEGORY'
712 AND lookup_code = p_category;
713 --
714 l_start_date DATE;
715 l_end_date DATE;
716 l_enabled_flag VARCHAR2(1);
717 --
718 l_api_updating BOOLEAN;
719 --
720 BEGIN
721 --
722 -- Only carry out the test if inserting and category isn't null
723 -- or updating and category isn't null and has changed
724 --
725 l_api_updating := per_bbt_shd.api_updating
726 (p_balance_type_id => p_balance_type_id
727 ,p_object_version_number => p_object_version_number);
728 --
729 IF (p_category IS NOT NULL AND NOT l_api_updating
730 OR (l_api_updating
731 AND p_category <> hr_api.g_varchar2
732 and p_category IS NOT NULL))
733 THEN
734 --
735 -- Cursor selects the start and end active dates rather than including them
736 -- as part of the where clause in order to give a more meaningful message.
737 --
738 OPEN csr_check_category;
739 FETCH csr_check_category INTO l_start_date, l_end_date, l_enabled_flag;
740 --
741 IF csr_check_category%NOTFOUND THEN
742 --
743 -- The lookup code entered doesn't exist in hr_lookups
744 -- so error.
745 --
746 hr_utility.set_message(800, 'HR_52945_CAT_NOT_EXIST');
747 hr_utility.raise_error;
748 --
749 END IF;
750 --
751 IF l_enabled_flag = 'N' THEN
752 --
753 -- The lookup exists, but isn't enabled
754 --
755 hr_utility.set_message(800,'HR_52610_CAT_LOOKUP_OFF');
756 hr_utility.raise_error;
757 --
758 END IF;
759 IF p_effective_date not between NVL (l_start_date, hr_api.g_sot)
760 and NVL (l_end_date, hr_api.g_eot) THEN
761 --
762 -- The lookup exists, but it isn't valid for the effective date
763 -- so error.
764 --
765 hr_utility.set_message(800,'HR_52946_CAT_NOT_VALID');
766 hr_utility.raise_error;
767 --
768 END IF;
769 END IF;
770 --
771 END chk_category;
772 -- ----------------------------------------------------------------------------
773 -- |---------------------------< insert_validate >----------------------------|
774 -- ----------------------------------------------------------------------------
775 Procedure insert_validate
776 (p_effective_date in date
777 ,p_rec in per_bbt_shd.g_rec_type
778 ) is
779 --
780 l_proc varchar2(72) := g_package||'insert_validate';
781 --
782 Begin
783 hr_utility.set_location('Entering:'||l_proc, 5);
784 --
785 -- Call all supporting business operations
786 --
787 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
788 --
789 CHK_INPUT_VALUE_DETAILS
790 (p_input_value_id => p_rec.input_value_id
791 ,p_business_group_id => p_rec.business_group_id
792 ,p_currency_code => p_rec.currency
793 ,p_uom => p_rec.uom
794 ,p_effective_date => p_effective_date
795 ,p_balance_type_id => p_rec.balance_type_id
796 ,p_object_version_number => p_rec.object_version_number
797 );
798 --
799 CHK_DISPLAY_NAME_UNIQ
800 (p_balance_type_id => p_rec.balance_type_id
801 ,p_object_version_number => p_rec.object_version_number
802 ,p_displayed_name => p_rec.displayed_name
803 , p_business_group_id => p_rec.business_group_id);
804 --
805 CHK_INTERNAL_NAME_UNIQ
806 (p_balance_type_id => p_rec.balance_type_id
807 ,p_object_version_number => p_rec.object_version_number
808 ,p_internal_name => p_rec.internal_name
809 ,p_business_group_id => p_rec.business_group_id);
810 --
811 chk_category
812 ( p_category => p_rec.category
813 ,p_balance_type_id => p_rec.balance_type_id
814 ,p_object_version_number => p_rec.object_version_number
815 ,p_effective_date => p_effective_date);
816 --
817 chk_uom_input
818 (p_uom => p_rec.uom
819 ,p_currency_code => p_rec.currency
820 ,p_input_value_id => p_rec.input_value_id
821 ,p_balance_type_id => p_rec.balance_type_id
822 ,p_object_version_number => p_rec.object_version_number);
823 --
824 CHK_UOM
825 (p_uom => p_rec.uom
826 ,p_balance_type_id => p_rec.balance_type_id
827 ,p_object_version_number => p_rec.object_version_number
828 ,p_effective_date => p_effective_date
829 );
830 --
831 hr_utility.set_location(' Leaving:'||l_proc, 10);
832 End insert_validate;
833 --
834 -- ----------------------------------------------------------------------------
835 -- |---------------------------< update_validate >----------------------------|
836 -- ----------------------------------------------------------------------------
837 Procedure update_validate
838 (p_effective_date in date
839 ,p_rec in per_bbt_shd.g_rec_type
840 ) is
841 --
842 l_proc varchar2(72) := g_package||'update_validate';
843 --
844 Begin
845 hr_utility.set_location('Entering:'||l_proc, 5);
846 --
847 -- Call all supporting business operations
848 --
849 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
850 --
851 CHK_INPUT_VALUE_DETAILS
852 (p_input_value_id => p_rec.input_value_id
853 ,p_business_group_id => p_rec.business_group_id
854 ,p_currency_code => p_rec.currency
855 ,p_uom => p_rec.uom
856 ,p_effective_date => p_effective_date
857 ,p_balance_type_id => p_rec.balance_type_id
858 ,p_object_version_number => p_rec.object_version_number
859 );
860 --
861 CHK_DISPLAY_NAME_UNIQ
862 (p_balance_type_id => p_rec.balance_type_id
863 ,p_object_version_number => p_rec.object_version_number
864 ,p_displayed_name => p_rec.displayed_name
865 , p_business_group_id => p_rec.business_group_id);
866 --
867 CHK_INTERNAL_NAME_UNIQ
868 (p_balance_type_id => p_rec.balance_type_id
869 ,p_object_version_number => p_rec.object_version_number
870 ,p_internal_name => p_rec.internal_name
871 ,p_business_group_id => p_rec.business_group_id);
872 --
873 chk_category
874 ( p_category => p_rec.category
875 ,p_balance_type_id => p_rec.balance_type_id
876 ,p_object_version_number => p_rec.object_version_number
877 ,p_effective_date => p_effective_date);
878 --
879 chk_uom_input
880 (p_uom => p_rec.uom
881 ,p_currency_code => p_rec.currency
882 ,p_input_value_id => p_rec.input_value_id
883 ,p_balance_type_id => p_rec.balance_type_id
884 ,p_object_version_number => p_rec.object_version_number);
885 --
886 CHK_UOM
887 (p_uom => p_rec.uom
888 ,p_balance_type_id => p_rec.balance_type_id
889 ,p_object_version_number => p_rec.object_version_number
890 ,p_effective_date => p_effective_date
891 );
892 --
893 hr_utility.set_location(' Leaving:'||l_proc, 10);
894 End update_validate;
895 --
896 -- ----------------------------------------------------------------------------
897 -- |---------------------------< delete_validate >----------------------------|
898 -- ----------------------------------------------------------------------------
899 Procedure delete_validate
900 (p_rec in per_bbt_shd.g_rec_type
901 ) is
902 --
903 l_proc varchar2(72) := g_package||'delete_validate';
904 --
905 Begin
906 hr_utility.set_location('Entering:'||l_proc, 5);
907 --
908 -- Call all supporting business operations
909 --
910 hr_utility.set_location(' Leaving:'||l_proc, 10);
911 End delete_validate;
912 --
913 end per_bbt_bus;
[Home] [Help]