[Home] [Help]
PACKAGE BODY: APPS.PER_ABB_BUS
Source
1 Package Body per_abb_bus as
2 /* $Header: peabbrhi.pkb 120.3 2006/03/03 06:26 bshukla noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_abb_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_absence_attendance_type_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |---------------------< return_legislation_code >-------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Function return_legislation_code
21 (p_absence_attendance_type_id in number
22 )
23 Return Varchar2 Is
24 --
25 -- Declare cursor
26 --
27 cursor csr_leg_code is
28 select pbg.legislation_code,abb.business_group_id
29 from per_business_groups_perf pbg
30 , per_absence_attendance_types abb
31 where abb.absence_attendance_type_id = p_absence_attendance_type_id
32 and pbg.business_group_id(+) = abb.business_group_id;
33 --
34 -- Declare local variables
35 --
36 l_legislation_code varchar2(150);
37 l_business_group_id per_business_groups.business_group_id%type;
38 l_proc varchar2(72) := g_package||'return_legislation_code';
39 --
40 Begin
41 --
42 hr_utility.set_location('Entering:'|| l_proc, 10);
43 --
44 -- Ensure that all the mandatory parameter are not null
45 --
46 hr_api.mandatory_arg_error
47 (p_api_name => l_proc
48 ,p_argument => 'absence_attendance_type_id'
49 ,p_argument_value => p_absence_attendance_type_id
50 );
51 --
52 if ( nvl(per_abb_bus.g_absence_attendance_type_id, hr_api.g_number)
53 = p_absence_attendance_type_id) then
54 --
55 -- The legislation code has already been found with a previous
56 -- call to this function. Just return the value in the global
57 -- variable.
58 --
59 l_legislation_code := per_abb_bus.g_legislation_code;
60 hr_utility.set_location(l_proc, 20);
61 else
62 --
63 -- The ID is different to the last call to this function
64 -- or this is the first call to this function.
65 --
66 open csr_leg_code;
67 fetch csr_leg_code into l_legislation_code, l_business_group_id;
68 --
69 if csr_leg_code%notfound then
70 --
71 -- The primary key is invalid therefore we must error
72 --
73 close csr_leg_code;
74 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
75 fnd_message.raise_error;
76 end if;
77 hr_utility.set_location(l_proc,30);
78 --
79 -- Set the global variables so the values are
80 -- available for the next call to this function.
81 --
82 close csr_leg_code;
83 if l_business_group_id is not null then
84 per_abb_bus.g_absence_attendance_type_id := p_absence_attendance_type_id;
85 per_abb_bus.g_legislation_code := l_legislation_code;
86 end if;
87 --
88 end if;
89 hr_utility.set_location(' Leaving:'|| l_proc, 40);
90 return l_legislation_code;
91 end return_legislation_code;
92 --
93 -- ----------------------------------------------------------------------------
94 -- |-----------------------------< chk_absence_type_dates >-------------------|
95 -- ----------------------------------------------------------------------------
96 --
97 -- Description:
98 -- Validates that date_effective is supplied.
99 -- Must be earlier than date_end
100 -- If updating, cannot be later than date_start of child row in PER_ABSENCE_ATTENDANCES
101 --
102 -- Prerequisites:
103 --
104 -- In Arguments:
105 -- p_absence_attendance_type_id
106 -- p_object_version_number
107 -- p_date_effective
108 -- p_date_end
109 --
110 -- Post Success:
111 -- Processing continues
112 --
113 -- Post Failure:
114 -- An application error is raised and processing is terminated.
115 --
116 --
117 --
118 -- Access Status:
119 -- Internal Row Handler Use Only.
120 --
121 -- ----------------------------------------------------------------------------
122 procedure chk_absence_type_dates
123 (p_absence_attendance_type_id in per_absence_attendance_types.absence_attendance_type_id%type
124 ,p_object_version_number in per_absence_attendance_types.object_version_number%type
125 ,p_date_effective in per_absence_attendance_types.date_effective%type
126 ,p_date_end in per_absence_attendance_types.date_end%type
127 ) is
128 --
129 l_proc varchar2(72) := g_package || 'chk_absence_type_dates';
130 l_api_updating boolean;
131 --
132 cursor csr_chk_attendance_date is
133 select 1
134 from per_absence_attendances
135 where absence_attendance_type_id = p_absence_attendance_type_id
136 and date_start < p_date_effective;
137 --
138 l_dummy number;
139 --
140 begin
141 hr_utility.set_location('Entering:'||l_proc,10);
142 --
143 -- Check mandatory parameters have been set
144 --
145 hr_api.mandatory_arg_error
146 (p_api_name => l_proc
147 ,p_argument => 'date_effective'
148 ,p_argument_value => p_date_effective
149 );
150 --
151 l_api_updating := per_abb_shd.api_updating
152 (p_absence_attendance_type_id => p_absence_attendance_type_id
153 ,p_object_version_number => p_object_version_number);
154 --
155 -- Only proceed with validation if:
156 -- a) rec is being inserted or
157 -- b) rec is updating and the g_old_rec is not current value
158 --
159 if ( (l_api_updating and (p_date_effective <> per_abb_shd.g_old_rec.date_effective
160 or p_date_end <> per_abb_shd.g_old_rec.date_end))
161 or not l_api_updating) then
162 --
163 if p_date_effective > nvl (p_date_end,hr_api.g_eot) then
164 fnd_message.set_name('PER','PER_7003_ALL_DATE_FROM_TO');
165 fnd_message.raise_error;
166 end if;
167 --
168 if l_api_updating then
169 open csr_chk_attendance_date;
170 fetch csr_chk_attendance_date into l_dummy;
171 if csr_chk_attendance_date%found then
172 close csr_chk_attendance_date;
173 fnd_message.set_name('PER','HR_6790_ABS_NO_CHANGE_2');
174 fnd_message.raise_error;
175 else
176 close csr_chk_attendance_date;
177 end if;
178 end if;
179 --
180 end if;
181 --
182 hr_utility.set_location('Leaving:'||l_proc,10);
183 --
184 exception
185 when app_exception.application_exception then
186 if hr_multi_message.exception_add
187 (p_associated_column1 => 'PER_ABSENCE_ATTENDANCE_TYPES.DATE_EFFECTIVE'
188 ,p_associated_column2 => 'PER_ABSENCE_ATTENDANCE_TYPES.DATE_END'
189 ) then
190 hr_utility.set_location(' Leaving:'||l_proc, 50);
191 raise;
192 end if;
193 hr_utility.set_location(' Leaving:'||l_proc,60);
194 end chk_absence_type_dates;
195 --
196 -- ----------------------------------------------------------------------------
197 -- |-----------------------------< chk_input_value_id >-----------------------|
198 -- ----------------------------------------------------------------------------
199 --
200 -- Description:
201 -- Validates input_value_id
202 -- Must be null if BG is null
203 -- Once it is not null, it cannot be changed.
204 -- Must correspond to a row on pay_input_values_f on both date_effective and date_end
205 -- Must correspond to an element type of processing type N,
206 -- or processing type R with not null proration group
207 --
208 -- Prerequisites:
209 -- Valid business_group_id
210 --
211 -- In Arguments:
212 -- p_absence_attendance_type_id
213 -- p_object_version_number
214 -- p_business_group_id
215 -- p_input_value_id
216 -- p_date_effective
217 -- p_date_end
218 --
219 -- Post Success:
220 -- Processing continues
221 --
222 -- Post Failure:
223 -- An application error is raised and processing terminates.
224 --
225 -- Access Status:
226 -- Internal Row Handler Use Only.
227 --
228 -- ----------------------------------------------------------------------------
229 procedure chk_input_value_id
230 (p_absence_attendance_type_id in per_absence_attendance_types.absence_attendance_type_id%type
231 ,p_object_version_number in per_absence_attendance_types.object_version_number%type
232 ,p_date_effective in per_absence_attendance_types.date_effective%type
233 ,p_date_end in out nocopy per_absence_attendance_types.date_end%type
234 ,p_input_value_id in per_absence_attendance_types.input_value_id%type
235 ,p_business_group_id in per_absence_attendance_types.business_group_id%type
236 ) is
237 --
238 l_proc varchar2(72) := g_package || 'chk_input_value_id';
239 l_api_updating boolean;
240 l_dummy number;
241 l_date_end date := p_date_end;
242 l_input_end date;
243 --
244 cursor csr_valid_input_value_bg is
245 select 1
246 from pay_input_values_f piv1, per_business_groups_perf pbg
247 where piv1.input_value_id = p_input_value_id
248 and pbg.business_group_id = p_business_group_id
249 and nvl(piv1.business_group_id,p_business_group_id) = p_business_group_id
250 and nvl(piv1.legislation_code,pbg.legislation_code) = pbg.legislation_code;
251 --
252 cursor csr_input_end is
253 select max(effective_end_date)
254 from pay_input_values_f
255 where input_value_id = p_input_value_id;
256 --
257 cursor csr_valid_input_value_dates is
258 select 1
259 from pay_input_values_f piv1, per_business_groups_perf pbg
260 where piv1.input_value_id = p_input_value_id
261 and p_date_effective between piv1.effective_start_date and piv1.effective_end_date
262 and exists (select 1
263 from pay_input_values_f piv2
264 where piv1.input_value_id = piv2.input_value_id
265 and nvl(l_date_end,hr_api.g_eot) between
266 piv2.effective_start_date and piv2.effective_end_date);
267 --
268 cursor csr_valid_input_value_uom is
269 select 1
270 from pay_input_values_f piv1, per_business_groups_perf pbg
271 where piv1.input_value_id = p_input_value_id
272 and piv1.uom in ('ND','H_HH','H_DECIMAL1','H_DECIMAL2','H_DECIMAL3')
273 and p_date_effective between piv1.effective_start_date and piv1.effective_end_date;
274 --
275 cursor csr_valid_element_type is
276 select 1
277 from pay_element_types_f pet, pay_input_values_f piv
278 where p_input_value_id = piv.input_value_id
279 and p_date_effective between piv.effective_start_date and piv.effective_end_date
280 and piv.element_type_id = pet.element_type_id
281 and p_date_effective between pet.effective_start_date and pet.effective_end_date
282 and ( pet.processing_type = 'N'
283 or (pet.processing_type= 'R' and pet.proration_group_id is not null));
284 --
285 begin
286 --
287 hr_utility.set_location('Entering:'||l_proc,10);
288 --
289 if hr_multi_message.no_all_inclusive_error
290 (p_check_column1 => 'PER_ABSENCE_ATTENDANCE_TYPES.DATE_EFFECTIVE'
291 ,p_check_column2 => 'PER_ABSENCE_ATTENDANCE_TYPES.DATE_END'
292 ) then
293 --
294 l_api_updating := per_abb_shd.api_updating
295 (p_absence_attendance_type_id => p_absence_attendance_type_id
296 ,p_object_version_number => p_object_version_number);
297 --
298 --
299 -- Only proceed with validation if:
300 -- a) rec is being inserted or
301 -- b) rec is updating and the g_old_rec is not current value
302 --
303 if ( (l_api_updating and
304 ( nvl(per_abb_shd.g_old_rec.input_value_id,-1) <> nvl(p_input_value_id,-1)
305 or per_abb_shd.g_old_rec.date_effective <> p_date_effective
306 or nvl(per_abb_shd.g_old_rec.date_end,hr_api.g_eot) <>
307 nvl(p_date_end,hr_api.g_eot)))
308 or not l_api_updating)
309 then
310 --
311 hr_utility.set_location(l_proc,20);
312 --
313 -- input value cannot be specified unless BG is specified
314 --
315 if p_business_group_id is null
316 and p_input_value_id is not null then
317 fnd_message.set_name('PER','PER_449173_ABB_NO_BG_NO_INPUT');
318 fnd_message.raise_error;
319 end if;
320 --
321 hr_utility.set_location(l_proc,30);
322 --
323 -- input value cannot change once it is not null
324 --
325 if (p_input_value_id is not null
326 and p_input_value_id <> nvl(per_abb_shd.g_old_rec.input_value_id,p_input_value_id))
327 or (p_input_value_id is null and per_abb_shd.g_old_rec.input_value_id is not null)
328 then
329 fnd_message.set_name('PER','PER_449174_ABB_NO_CHANGE_INPUT');
330 fnd_message.raise_error;
331 end if;
332 --
333 if p_input_value_id is not null then
334 --
335 open csr_valid_input_value_bg;
336 fetch csr_valid_input_value_bg into l_dummy;
337 if csr_valid_input_value_bg%notfound then
338 close csr_valid_input_value_bg;
339 fnd_message.set_name('PER','PER_449182_ABB_INPUT_WRONG_BG');
340 fnd_message.raise_error;
341 else
342 close csr_valid_input_value_bg;
343 end if;
344 --
345 hr_utility.set_location(l_proc,40);
346 --
347 open csr_input_end;
348 fetch csr_input_end into l_input_end;
349 close csr_input_end;
350 --
351 if p_date_end is null
352 and l_input_end < hr_api.g_eot then
353 l_date_end := l_input_end; --auto-populate date_end if input has end date
354 elsif p_date_end is not null
355 and p_date_end > l_input_end then
356 fnd_message.set_name('PER','PER_7800_DEF_ABS_ELEMENT_ENDS');
357 fnd_message.raise_error;
358 end if;
359 --
360 open csr_valid_input_value_dates;
361 fetch csr_valid_input_value_dates into l_dummy;
362 if csr_valid_input_value_dates%notfound then
363 close csr_valid_input_value_dates;
364 fnd_message.set_name('PER','PER_449176_ABB_INPUT_NOT_EXIST');
365 fnd_message.raise_error;
366 else
367 close csr_valid_input_value_dates;
368 end if;
369 --
370 hr_utility.set_location(l_proc,50);
371 --
372 open csr_valid_input_value_uom;
373 fetch csr_valid_input_value_uom into l_dummy;
374 if csr_valid_input_value_uom%notfound then
375 close csr_valid_input_value_uom;
376 fnd_message.set_name('PER','PER_449175_ABB_INPUT_WRONG_UOM');
377 fnd_message.raise_error;
378 else
379 close csr_valid_input_value_uom;
380 end if;
381 --
382 hr_utility.set_location(l_proc,60);
383 --
384 open csr_valid_element_type;
385 fetch csr_valid_element_type into l_dummy;
386 if csr_valid_element_type%notfound then
387 close csr_valid_element_type;
388 fnd_message.set_name('PER','PER_449177_ABB_INPUT_ELE_TYPE');
389 fnd_message.raise_error;
390 else
391 close csr_valid_element_type;
392 end if;
393 --
394 hr_utility.set_location(l_proc,70);
395 --
396 end if; --input_value_id is not null
397 end if; --api_updating check
398 end if; --no_all_inclusive_error check
399 --
400 hr_utility.set_location('Leaving:'||l_proc,80);
401 --
402 --set the OUT arguments
403 --
404 p_date_end := l_date_end;
405 --
406 exception
407 when app_exception.application_exception then
408 if hr_multi_message.exception_add
409 (p_associated_column1 => 'PER_ABSENCE_ATTENDANCE_TYPES.DATE_EFFECTIVE'
410 ,p_associated_column2 => 'PER_ABSENCE_ATTENDANCE_TYPES.DATE_END'
411 ,p_associated_column3 => 'PER_ABSENCE_ATTENDANCE_TYPES.INPUT_VALUE_ID'
412 ) then
413 hr_utility.set_location(' Leaving:'||l_proc, 90);
414 raise;
415 end if;
416 hr_utility.set_location(' Leaving:'||l_proc,100);
417 end chk_input_value_id;
418 --
419 --
420 -- ----------------------------------------------------------------------------
421 -- |-------------------------< chk_abs_overlap_flag >------------------------|
422 -- ----------------------------------------------------------------------------
423 -- {Start Of Comments}
424 --
425 -- Description:
426 -- This procedure checks the value of Absence Overlap Flag.
427 --
428 -- Pre Conditions:
429 -- g_old_rec has been populated with details of the values currently in
430 -- the database.
431 --
432 -- In Arguments:
433 -- p_absence_overlap_flag
434 --
435 -- Post Success:
436 -- None.
437 --
438 -- Post Failure:
439 -- An application error is raised
440 -- if updating and old_rec.business_group_id is not null, or
441 -- business_group_id is not valid.
442 --
443 -- {End Of Comments}
444 -- ----------------------------------------------------------------------------
445 procedure chk_abs_overlap_flag
446 (
447 p_absence_overlap_flag in per_absence_attendance_types.absence_overlap_flag %TYPE
448 )
449 is
450 --
451
452 --
453 l_proc varchar2(72) := g_package||'chk_abs_overlap_flag';
454 l_api_updating boolean;
455 l_absence_overlap_flag varchar2(10);
456
457 cursor csr_abs_overlap_flag(p_absence_overlap_flag varchar2)
458 is
459 select 'X'
460 from hr_lookups
461 where lookup_type = 'YES_NO'
462 and lookup_code = p_absence_overlap_flag;
463
464
465 --
466 begin
467 hr_utility.set_location('Entering:'||l_proc, 5);
468
469 open csr_abs_overlap_flag(p_absence_overlap_flag);
470 fetch csr_abs_overlap_flag into l_absence_overlap_flag;
471 if csr_abs_overlap_flag%notfound then
472 close csr_abs_overlap_flag;
473
474 hr_utility.set_message(800, 'HR_449758_INVL_ABS_OVERLAP_FLG');
475 hr_utility.set_message_token('OBJECT', 'ABSENCE_OVERLAP_FLAG');
476 hr_utility.set_message_token('TABLE', 'HR_LOOKUPS');
477 hr_utility.set_message_token('CONDITION', 'lookup type "YES_NO"');
478
479 hr_utility.set_location(l_proc, 10);
480 --
481 else
482 close csr_abs_overlap_flag;
483 end if;
484 hr_utility.set_location(l_proc, 20);
485
486 exception
487 when app_exception.application_exception then
488 if hr_multi_message.exception_add
489 (p_same_associated_columns => 'Y'
490 ) then
491 hr_utility.set_location('Leaving:'||l_proc, 50);
492 raise;
493 end if;
494 hr_utility.set_location('Leaving:'||l_proc,60);
495 end chk_abs_overlap_flag;
496 --
497 -- ----------------------------------------------------------------------------
498 -- |-----------------------------< chk_inc_or_dec_flag >----------------------|
499 -- ----------------------------------------------------------------------------
500 --
501 -- Description:
502 -- Validates increasing_or_decreasing_flag
503 -- Must be null is input_value_id is null
504 -- Must have value 'I' or 'D' if not null
505 -- Once it is not null it cannot be changed
506 --
507 -- Prerequisites:
508 -- input_value_id is valid
509 --
510 -- In Arguments:
511 -- p_absence_attendance_type_id
512 -- p_object_version_number
513 -- p_input_value_id
514 -- p_inc_or_dec_flag
515 --
516 -- Post Success:
517 -- Processing Continues
518 --
519 -- Post Failure:
520 -- An application error is raised and processing terminates.
521 --
522 -- Access Status:
523 -- Internal Row Handler Use Only.
524 --
525 -- ----------------------------------------------------------------------------
526 procedure chk_inc_or_dec_flag
527 (p_absence_attendance_type_id in per_absence_attendance_types.absence_attendance_type_id%type
528 ,p_object_version_number in per_absence_attendance_types.object_version_number%type
529 ,p_input_value_id in per_absence_attendance_types.input_value_id%type
530 ,p_inc_or_dec_flag in per_absence_attendance_types.increasing_or_decreasing_flag%type
531 ) is
532 --
533 l_proc varchar2(72) := g_package || 'chk_inc_or_dec_flag';
534 l_api_updating boolean;
535 --
536 begin
537 --
538 hr_utility.set_location('Entering:'||l_proc,10);
539 --
540 l_api_updating := per_abb_shd.api_updating
541 (p_absence_attendance_type_id => p_absence_attendance_type_id
542 ,p_object_version_number => p_object_version_number);
543 --
544 --
545 -- Only proceed with validation if:
546 -- a) rec is being inserted or
547 -- b) rec is updating and the g_old_rec is not current value
548 --
549 if (l_api_updating and
550 ( nvl(per_abb_shd.g_old_rec.increasing_or_decreasing_flag,hr_api.g_varchar2) <>
551 nvl(p_inc_or_dec_flag,hr_api.g_varchar2)
552 or nvl(per_abb_shd.g_old_rec.input_value_id,-1) <> nvl(p_input_value_id,-1))
553 or not l_api_updating)
554 then
555 --
556 if p_input_value_id is null then
557 if p_inc_or_dec_flag is not null then
558 fnd_message.set_name('PER','PER_449183_ABB_NO_INPUT_NO_INC');
559 fnd_message.raise_error;
560 end if;
561 --
562 hr_utility.set_location(l_proc,20);
563 --
564 else
565 -- input_value_id is not null so inc_or_dec_flag must be specified
566 --
567 if nvl(p_inc_or_dec_flag,hr_api.g_varchar2) not in ('I','D') then
568 fnd_message.set_name('PER','HR_7583_ALL_MAN_INC_FIELD');
569 fnd_message.raise_error;
570 end if;
571 --
572 hr_utility.set_location(l_proc,30);
573 --
574 if l_api_updating
575 and per_abb_shd.g_old_rec.increasing_or_decreasing_flag is not null
576 and nvl(p_inc_or_dec_flag,hr_api.g_varchar2) <>
577 per_abb_shd.g_old_rec.increasing_or_decreasing_flag then
578 --
579 --flag must not be changed once it is not null
580 --
581 fnd_message.set_name('PER','PER_449178_ABB_NO_CHANGE_INC');
582 fnd_message.raise_error;
583 end if;
584 end if;
585 end if;
586 hr_utility.set_location('Leaving:'||l_proc,70);
587 --
588 exception
589 when app_exception.application_exception then
590 if hr_multi_message.exception_add
591 (p_associated_column1 => 'PER_ABSENCE_ATTENDANCE_TYPES.INCREASING_OR_DECREASING_FLAG'
592 ) then
593 hr_utility.set_location(' Leaving:'||l_proc, 80);
594 raise;
595 end if;
596 hr_utility.set_location(' Leaving:'||l_proc,90);
597 end chk_inc_or_dec_flag;
598 --
599 -- ----------------------------------------------------------------------------
600 -- |-----------------------------< chk_hours_or_days >------------------------|
601 -- ----------------------------------------------------------------------------
602 --
603 -- Description:
604 -- Validates hours_or_days
605 -- Must be null if input_Value_id is null
606 -- Once it is not null, it cannot be changed
607 -- Value must be one of 'D','H'
608 --
609 -- Prerequisites:
610 -- Valid input_value_id
611 --
612 -- In Arguments:
613 -- p_absence_attendance_type_id
614 -- p_object_version_number
615 -- p_input_value_id
616 -- p_hours_or_days
617 --
618 -- Post Success:
619 -- Processing continues
620 --
621 -- Post Failure:
622 -- An application error is raised and processing is terminated
623 --
624 -- Access Status:
625 -- Internal Row Handler Use Only.
626 --
627 -- ----------------------------------------------------------------------------
628 procedure chk_hours_or_days
629 (p_absence_attendance_type_id in per_absence_attendance_types.absence_attendance_type_id%type
630 ,p_object_version_number in per_absence_attendance_types.object_version_number%type
631 ,p_input_value_id in per_absence_attendance_types.input_value_id%type
632 ,p_hours_or_days in per_absence_attendance_types.hours_or_days%type
633 ,p_date_effective in per_absence_attendance_types.date_effective%type
634 ) is
635 --
636 l_proc varchar2(72) := g_package || 'chk_hours_or_days';
637 l_api_updating boolean;
638 l_input_uom pay_input_values_f.uom%type;
639 --
640 begin
641 --
642 hr_utility.set_location('Entering:'||l_proc,10);
643 --
644 if hr_multi_message.no_all_inclusive_error
645 (p_check_column1 => 'PER_ABSENCE_ATTENDANCE_TYPES.INPUT_VALUE_ID'
646 ) then
647 --
648 --
649 l_api_updating := per_abb_shd.api_updating
650 (p_absence_attendance_type_id => p_absence_attendance_type_id
651 ,p_object_version_number => p_object_version_number);
652 --
653 --
654 -- Only proceed with validation if:
655 -- a) rec is being inserted or
656 -- b) rec is updating and the g_old_rec is not current value
657 --
658 if (l_api_updating and
659 ( nvl(per_abb_shd.g_old_rec.hours_or_days,hr_api.g_varchar2) <>
660 nvl(p_hours_or_days,hr_api.g_varchar2)
661 or nvl(per_abb_shd.g_old_rec.input_value_id,-1) <> nvl(p_input_value_id,-1))
662 or not l_api_updating)
663 then
664 --
665 if p_input_value_id is null then
666 if p_hours_or_days is not null then
667 fnd_message.set_name('PER','PER_449184_ABB_NO_INPUT_NO_HOU');
668 fnd_message.raise_error;
669 end if;
670 --
671 else -- input_value_id is not null
672 hr_utility.set_location(l_proc,20);
673 --
674 if l_api_updating
675 and per_abb_shd.g_old_rec.hours_or_days is not null
676 and nvl(p_hours_or_days,hr_api.g_varchar2) <>
677 per_abb_shd.g_old_rec.hours_or_days then
678 --
679 --flag must not be changed once it is not null
680 --
681 fnd_message.set_name('PER','PER_449179_ABB_NO_CHANGE_HOURS');
682 fnd_message.raise_error;
683 end if;
684 --
685 hr_utility.set_location(l_proc,30);
686 --
687 -- flag must have the correct value (uom of input value is already validated)
688 --
689 if p_hours_or_days is null
690 or p_hours_or_days not in ('D','H') then
691 fnd_message.set_name('PER','PER_449180_ABB_HOURS_WRONG_UOM');
692 fnd_message.raise_error;
693 end if;
694 end if; --input_value_id is null check
695 --
696 end if; --api updating check
697 end if; --no_all_inclusive_error
698 hr_utility.set_location('Leaving:'||l_proc,70);
699 --
700 exception
701 when app_exception.application_exception then
702 if hr_multi_message.exception_add
703 (p_associated_column1 => 'PER_ABSENCE_ATTENDANCE_TYPES.HOURS_OR_DAYS'
704 ) then
705 hr_utility.set_location(' Leaving:'||l_proc, 80);
706 raise;
707 end if;
708 hr_utility.set_location(' Leaving:'||l_proc,90);
709 --
710 end chk_hours_or_days;
711 --
712 -- ----------------------------------------------------------------------------
713 -- |-----------------------------< chk_absence_category >---------------------|
714 -- ----------------------------------------------------------------------------
715 --
716 -- Description:
717 -- Validates absence_category
718 -- Must exist as enabled lookup_code for lookup_type 'ABSENCE_CATEGORY'
719 -- Value cannot be changed once it is not null if there is a chile record in
720 -- per_absence_attendances
721 --
722 -- Prerequisites:
723 --
724 --
725 -- In Arguments:
726 -- p_absence_attendance_type_id
727 -- p_object_version_number
728 -- p_date_effective
729 -- p_absence_category
730 --
731 -- Post Success:
732 -- Processing continues
733 --
734 -- Post Failure:
735 -- An application error is raised and processing is terminated
736 --
737 -- Access Status:
738 -- Internal Row Handler Use Only.
739 --
740 -- ----------------------------------------------------------------------------
741 procedure chk_absence_category
742 (p_absence_attendance_type_id in per_absence_attendance_types.absence_attendance_type_id%type
743 ,p_object_version_number in per_absence_attendance_types.object_version_number%type
744 ,p_date_effective in per_absence_attendance_types.date_effective%type
745 ,p_absence_category in per_absence_attendance_types.absence_category%type
746 ) is
747 --
748 l_proc varchar2(72) := g_package || 'chk_absence_category';
749 l_api_updating boolean;
750 --
751 cursor csr_absence_attendance is
752 select 1
753 from per_absence_attendances
754 where absence_attendance_type_id = p_absence_attendance_type_id;
755 --
756 l_dummy number;
757 --
758 begin
759 --
760 hr_utility.set_location('Entering:'||l_proc,10);
761 --
762 l_api_updating := per_abb_shd.api_updating
763 (p_absence_attendance_type_id => p_absence_attendance_type_id
764 ,p_object_version_number => p_object_version_number);
765 --
766 --
767 -- Only proceed with validation if:
768 -- a) rec is being inserted or
769 -- b) rec is updating and the g_old_rec is not current value
770 --
771 if (l_api_updating and
772 (nvl(per_abb_shd.g_old_rec.absence_category,hr_api.g_varchar2) <> nvl(p_absence_category,hr_api.g_varchar2))
773 or not l_api_updating)
774 then
775 --
776 hr_utility.set_location(l_proc,20);
777 --
778 if p_absence_category is not null
779 and hr_api.not_exists_in_leg_lookups
780 (p_effective_date => p_date_effective
781 ,p_lookup_type => 'ABSENCE_CATEGORY'
782 ,p_lookup_code => p_absence_category
783 )
784 then
785 --
786 fnd_message.set_name('PER','PER_449185_ABB_CAT_NOT_EXIST');
787 fnd_message.raise_error;
788 end if;
789 --
790 hr_utility.set_location(l_proc,30);
791 --
792 if l_api_updating then
793 open csr_absence_attendance;
794 fetch csr_absence_attendance into l_dummy;
795 --
796 if csr_absence_attendance%found
797 and per_abb_shd.g_old_rec.absence_category is not null
798 and per_abb_shd.g_old_rec.absence_category <>
799 nvl(p_absence_category,hr_api.g_varchar2)
800 then
801 close csr_absence_attendance;
802 fnd_message.set_name('PER','HR_6383_ABS_DET_NO_CHANGE');
803 fnd_message.raise_error;
804 else
805 hr_utility.set_location(l_proc,40);
806 --
807 close csr_absence_attendance;
808 end if;
809 --
810 end if;
811 end if;
812 --
813 hr_utility.set_location('Leaving:'||l_proc,70);
814 --
815 exception
816 when app_exception.application_exception then
817 if hr_multi_message.exception_add
818 (p_associated_column1 => 'PER_ABSENCE_ATTENDANCE_TYPES.ABSENCE_CATEGORY'
819 ) then
820 hr_utility.set_location(' Leaving:'||l_proc, 80);
821 raise;
822 end if;
823 hr_utility.set_location(' Leaving:'||l_proc,90);
824 --
825 end chk_absence_category;
826 --
827 -- ----------------------------------------------------------------------------
828 -- |-----------------------------< chk_information_category >------------------|
829 -- ----------------------------------------------------------------------------
830 --
831 -- Description:
832 -- Validates information_category
833 -- Must be null if business_group_id is null
834 -- If not null, must correspond to the legislation of the BG, and an enabled
835 -- context of DDF 'Absence Type Developer DF'
836 --
837 -- Prerequisites:
838 --
839 -- In Arguments:
840 -- p_absence_attendance_type_id
841 -- p_object_version_number
842 -- p_business_group_id
843 --
844 -- Post Success:
845 -- If p_information_category is not null and it matches the legislation
846 -- corresponding to the business group then the process succeeds.
847 -- If p_information_category is null and a valid DDF context exists for
848 -- the legislation of the business group, then the corresponding legislation
849 -- is set for p_per_information_category
850 --
851 -- Post Failure:
852 -- An application error is raised and processing is terminated.
853 --
854 -- Access Status:
855 -- Internal Row Handler Use Only.
856 --
857 -- ----------------------------------------------------------------------------
858 procedure chk_information_category
859 (p_absence_attendance_type_id in per_absence_attendance_types.absence_attendance_type_id%type
860 ,p_object_version_number in per_absence_attendance_types.object_version_number%type
861 ,p_business_group_id in per_absence_attendance_types.business_group_id%type
862 ,p_information_category in out nocopy per_absence_attendance_types.information_category%type
863 ) is
864 --
865 l_proc varchar2(72) := g_package || 'chk_information_category';
866 l_api_updating boolean;
867 l_dummy number;
868 l_leg per_business_groups.legislation_code%TYPE;
869 --
870 cursor csr_bg_legislation is
871 select legislation_code
872 from per_business_groups pbg
873 where business_group_id = p_business_group_id;
874 --
875 cursor csr_ddf_exist(p_legislation_code varchar2) is
876 select 1
877 from fnd_descr_flex_contexts fdfc
878 where fdfc.application_id = 800
879 and fdfc.descriptive_flexfield_name = 'Absence Type Developer DF'
880 and fdfc.enabled_flag = 'Y'
881 and fdfc.descriptive_flex_context_code = p_legislation_code;
882 --
883 begin
884 --
885 hr_utility.set_location('Entering:'||l_proc,10);
886 --
887 l_api_updating := per_abb_shd.api_updating
888 (p_absence_attendance_type_id => p_absence_attendance_type_id
889 ,p_object_version_number => p_object_version_number);
890 --
891 --
892 -- Only proceed with validation if:
893 -- a) rec is being inserted or
894 -- b) rec is updating and the g_old_rec is not current value
895 --
896 if (l_api_updating and
897 (nvl(per_abb_shd.g_old_rec.information_category,hr_api.g_varchar2) <> nvl(p_information_category,hr_api.g_varchar2))
898 or not l_api_updating)
899 then
900 --
901 hr_utility.set_location(l_proc,10);
902 --
903 if p_information_category is not null then
904 if p_business_group_id is null then
905 fnd_message.set_name('PER','PER_449181_ABB_INFO_CAT_BG');
906 fnd_message.raise_error;
907 else
908 --
909 hr_utility.set_location(l_proc,20);
910 --
911 open csr_bg_legislation;
912 fetch csr_bg_legislation into l_leg;
913 close csr_bg_legislation;
914 --
915 if p_information_category <> l_leg then
916 fnd_message.set_name('PER','PER_449186_ABB_INF_CAT_LEG');
917 fnd_message.raise_error;
918 end if;
919 --
920 end if;
921 else
922 if p_business_group_id is not null then
923 --
924 hr_utility.set_location(l_proc,30);
925 --
926 open csr_bg_legislation;
927 fetch csr_bg_legislation into l_leg;
928 close csr_bg_legislation;
929 --
930 open csr_ddf_exist(l_leg);
931 fetch csr_ddf_exist into l_dummy;
932 if csr_ddf_exist%found then
933 close csr_ddf_exist;
934 --
935 hr_utility.set_location(l_proc,40);
936 --
937 p_information_category := l_leg;
938 else
939 close csr_ddf_exist;
940 --
941 hr_utility.set_location(l_proc,50);
942 --
943 p_information_category := null;
944 end if;
945 else
946 --
947 hr_utility.set_location(l_proc,60);
948 --
949 p_information_category := null;
950 end if;
951 --
952 end if; -- p_information_category is not null
953 --
954 end if; --api updating
955 --
956 hr_utility.set_location('Leaving:'||l_proc,70);
957 --
958 end chk_information_category;
959 --
960 -- ----------------------------------------------------------------------------
961 -- |-----------------------------< chk_absence_type_delete >------------------|
962 -- ----------------------------------------------------------------------------
963 --
964 -- Description:
965 -- Checks if deletion of the absence type will be allowed.
966 -- Must be no records referencing absence type on the following tables.
967 -- per_abs_attendance_reasons, per_absence_attendances
968 --
969 -- Prerequisites:
970 --
971 -- In Arguments:
972 -- p_absence_attendance_type_id
973 --
974 -- Post Success:
975 -- Processing continues
976 --
977 -- Post Failure:
978 -- An application error is raised and processing is terminated.
979 --
980 -- Access Status:
981 -- Internal Row Handler Use Only.
982 --
983 -- ----------------------------------------------------------------------------
984 procedure chk_absence_type_delete
985 (p_absence_attendance_type_id in per_absence_attendance_types.absence_attendance_type_id%type
986 ) is
987 --
988 l_proc varchar2(72) := g_package || 'chk_absence_type_delete';
989 l_dummy number;
990 --
991 cursor csr_attendance is
992 select 1
993 from per_absence_attendances
994 where absence_attendance_type_id = p_absence_attendance_type_id;
995 --
996 cursor csr_reason is
997 select 1
998 from per_abs_attendance_reasons
999 where absence_attendance_type_id = p_absence_attendance_type_id;
1000 --
1001 begin
1002 --
1003 hr_utility.set_location('Entering:'||l_proc,10);
1004 --
1005 open csr_attendance;
1006 fetch csr_attendance into l_dummy;
1007 if csr_attendance%found then
1008 close csr_attendance;
1009 fnd_message.set_name('PER','PER_7059_EMP_ABS_DEL_TYPE');
1010 fnd_message.raise_error;
1011 else
1012 close csr_attendance;
1013 end if;
1014 --
1015 hr_utility.set_location(l_proc,20);
1016 --
1017 open csr_reason;
1018 fetch csr_reason into l_dummy;
1019 if csr_reason%found then
1020 close csr_reason;
1021 fnd_message.set_name('PER','PER_7805_DEF_ABS_DEL_REASON');
1022 fnd_message.raise_error;
1023 else
1024 close csr_reason;
1025 end if;
1026 --
1027 hr_utility.set_location('Leaving:'||l_proc,30);
1028 --
1029 end chk_absence_type_delete;
1030 --
1031 -- ----------------------------------------------------------------------------
1032 -- |-----------------------------< chk_ddf >----------------------------------|
1033 -- ----------------------------------------------------------------------------
1034 --
1035 -- Description:
1036 -- Validates all the Developer Descriptive Flexfield values.
1037 --
1038 -- Prerequisites:
1039 -- All other columns have been validated. Must be called as the
1040 -- last step from insert_validate and update_validate.
1041 --
1042 -- In Arguments:
1043 -- p_rec
1044 --
1045 -- Post Success:
1046 -- If the Developer Descriptive Flexfield structure column and data values
1047 -- are all valid this procedure will end normally and processing will
1048 -- continue.
1049 --
1050 -- Post Failure:
1051 -- If the Developer Descriptive Flexfield structure column value or any of
1052 -- the data values are invalid then an application error is raised as
1053 -- a PL/SQL exception.
1054 --
1055 -- Access Status:
1056 -- Internal Row Handler Use Only.
1057 --
1058 -- ----------------------------------------------------------------------------
1059 procedure chk_ddf
1060 (p_rec in per_abb_shd.g_rec_type
1061 ) is
1062 --
1063 l_proc varchar2(72) := g_package || 'chk_ddf';
1064 --
1065 begin
1066 hr_utility.set_location('Entering:'||l_proc,10);
1067 --
1068 if ((p_rec.absence_attendance_type_id is not null) and (
1069 nvl(per_abb_shd.g_old_rec.information_category, hr_api.g_varchar2) <>
1070 nvl(p_rec.information_category, hr_api.g_varchar2) or
1071 nvl(per_abb_shd.g_old_rec.information1, hr_api.g_varchar2) <>
1072 nvl(p_rec.information1, hr_api.g_varchar2) or
1073 nvl(per_abb_shd.g_old_rec.information2, hr_api.g_varchar2) <>
1074 nvl(p_rec.information2, hr_api.g_varchar2) or
1075 nvl(per_abb_shd.g_old_rec.information3, hr_api.g_varchar2) <>
1076 nvl(p_rec.information3, hr_api.g_varchar2) or
1077 nvl(per_abb_shd.g_old_rec.information4, hr_api.g_varchar2) <>
1078 nvl(p_rec.information4, hr_api.g_varchar2) or
1079 nvl(per_abb_shd.g_old_rec.information5, hr_api.g_varchar2) <>
1080 nvl(p_rec.information5, hr_api.g_varchar2) or
1081 nvl(per_abb_shd.g_old_rec.information6, hr_api.g_varchar2) <>
1082 nvl(p_rec.information6, hr_api.g_varchar2) or
1083 nvl(per_abb_shd.g_old_rec.information7, hr_api.g_varchar2) <>
1084 nvl(p_rec.information7, hr_api.g_varchar2) or
1085 nvl(per_abb_shd.g_old_rec.information8, hr_api.g_varchar2) <>
1086 nvl(p_rec.information8, hr_api.g_varchar2) or
1087 nvl(per_abb_shd.g_old_rec.information9, hr_api.g_varchar2) <>
1088 nvl(p_rec.information9, hr_api.g_varchar2) or
1089 nvl(per_abb_shd.g_old_rec.information10, hr_api.g_varchar2) <>
1090 nvl(p_rec.information10, hr_api.g_varchar2) or
1091 nvl(per_abb_shd.g_old_rec.information11, hr_api.g_varchar2) <>
1092 nvl(p_rec.information11, hr_api.g_varchar2) or
1093 nvl(per_abb_shd.g_old_rec.information12, hr_api.g_varchar2) <>
1094 nvl(p_rec.information12, hr_api.g_varchar2) or
1095 nvl(per_abb_shd.g_old_rec.information13, hr_api.g_varchar2) <>
1096 nvl(p_rec.information13, hr_api.g_varchar2) or
1097 nvl(per_abb_shd.g_old_rec.information14, hr_api.g_varchar2) <>
1098 nvl(p_rec.information14, hr_api.g_varchar2) or
1099 nvl(per_abb_shd.g_old_rec.information15, hr_api.g_varchar2) <>
1100 nvl(p_rec.information15, hr_api.g_varchar2) or
1101 nvl(per_abb_shd.g_old_rec.information16, hr_api.g_varchar2) <>
1102 nvl(p_rec.information16, hr_api.g_varchar2) or
1103 nvl(per_abb_shd.g_old_rec.information17, hr_api.g_varchar2) <>
1104 nvl(p_rec.information17, hr_api.g_varchar2) or
1105 nvl(per_abb_shd.g_old_rec.information18, hr_api.g_varchar2) <>
1106 nvl(p_rec.information18, hr_api.g_varchar2) or
1107 nvl(per_abb_shd.g_old_rec.information19, hr_api.g_varchar2) <>
1108 nvl(p_rec.information19, hr_api.g_varchar2) or
1109 nvl(per_abb_shd.g_old_rec.information20, hr_api.g_varchar2) <>
1110 nvl(p_rec.information20, hr_api.g_varchar2) ))
1111 or (p_rec.absence_attendance_type_id is null) then
1112 --
1113 -- Only execute the validation if absolutely necessary:
1114 -- a) During update, the structure column value or any
1115 -- of the attribute values have actually changed.
1116 -- b) During insert.
1117 --
1118 hr_dflex_utility.ins_or_upd_descflex_attribs
1119 (p_appl_short_name => 'PER'
1120 ,p_descflex_name => 'Absence Type Developer DF'
1121 ,p_attribute_category => p_rec.information_category
1122 ,p_attribute1_name => 'INFORMATION1'
1123 ,p_attribute1_value => p_rec.information1
1124 ,p_attribute2_name => 'INFORMATION2'
1125 ,p_attribute2_value => p_rec.information2
1126 ,p_attribute3_name => 'INFORMATION3'
1127 ,p_attribute3_value => p_rec.information3
1128 ,p_attribute4_name => 'INFORMATION4'
1129 ,p_attribute4_value => p_rec.information4
1130 ,p_attribute5_name => 'INFORMATION5'
1131 ,p_attribute5_value => p_rec.information5
1132 ,p_attribute6_name => 'INFORMATION6'
1133 ,p_attribute6_value => p_rec.information6
1134 ,p_attribute7_name => 'INFORMATION7'
1135 ,p_attribute7_value => p_rec.information7
1136 ,p_attribute8_name => 'INFORMATION8'
1137 ,p_attribute8_value => p_rec.information8
1138 ,p_attribute9_name => 'INFORMATION9'
1139 ,p_attribute9_value => p_rec.information9
1140 ,p_attribute10_name => 'INFORMATION10'
1141 ,p_attribute10_value => p_rec.information10
1142 ,p_attribute11_name => 'INFORMATION11'
1143 ,p_attribute11_value => p_rec.information11
1144 ,p_attribute12_name => 'INFORMATION12'
1145 ,p_attribute12_value => p_rec.information12
1146 ,p_attribute13_name => 'INFORMATION13'
1147 ,p_attribute13_value => p_rec.information13
1148 ,p_attribute14_name => 'INFORMATION14'
1149 ,p_attribute14_value => p_rec.information14
1150 ,p_attribute15_name => 'INFORMATION15'
1151 ,p_attribute15_value => p_rec.information15
1152 ,p_attribute16_name => 'INFORMATION16'
1153 ,p_attribute16_value => p_rec.information16
1154 ,p_attribute17_name => 'INFORMATION17'
1155 ,p_attribute17_value => p_rec.information17
1156 ,p_attribute18_name => 'INFORMATION18'
1157 ,p_attribute18_value => p_rec.information18
1158 ,p_attribute19_name => 'INFORMATION19'
1159 ,p_attribute19_value => p_rec.information19
1160 ,p_attribute20_name => 'INFORMATION20'
1161 ,p_attribute20_value => p_rec.information20
1162 );
1163 end if;
1164 --
1165 hr_utility.set_location(' Leaving:'||l_proc,20);
1166 end chk_ddf;
1167 --
1168 -- ----------------------------------------------------------------------------
1169 -- |------------------------------< chk_df >----------------------------------|
1170 -- ----------------------------------------------------------------------------
1171 --
1172 -- Description:
1173 -- Validates all the Descriptive Flexfield values.
1174 --
1175 -- Prerequisites:
1176 -- All other columns have been validated. Must be called as the
1177 -- last step from insert_validate and update_validate.
1178 --
1179 -- In Arguments:
1180 -- p_rec
1181 --
1182 -- Post Success:
1183 -- If the Descriptive Flexfield structure column and data values are
1184 -- all valid this procedure will end normally and processing will
1185 -- continue.
1186 --
1187 -- Post Failure:
1188 -- If the Descriptive Flexfield structure column value or any of
1189 -- the data values are invalid then an application error is raised as
1190 -- a PL/SQL exception.
1191 --
1192 -- Access Status:
1193 -- Internal Row Handler Use Only.
1194 --
1195 -- ----------------------------------------------------------------------------
1196 procedure chk_df
1197 (p_rec in per_abb_shd.g_rec_type
1198 ) is
1199 --
1200 l_proc varchar2(72) := g_package || 'chk_df';
1201 --
1202 begin
1203 hr_utility.set_location('Entering:'||l_proc,10);
1204 --
1205 if ((p_rec.absence_attendance_type_id is not null) and (
1206 nvl(per_abb_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
1207 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
1208 nvl(per_abb_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
1209 nvl(p_rec.attribute1, hr_api.g_varchar2) or
1210 nvl(per_abb_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
1211 nvl(p_rec.attribute2, hr_api.g_varchar2) or
1212 nvl(per_abb_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
1213 nvl(p_rec.attribute3, hr_api.g_varchar2) or
1214 nvl(per_abb_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
1215 nvl(p_rec.attribute4, hr_api.g_varchar2) or
1216 nvl(per_abb_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
1217 nvl(p_rec.attribute5, hr_api.g_varchar2) or
1218 nvl(per_abb_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
1219 nvl(p_rec.attribute6, hr_api.g_varchar2) or
1220 nvl(per_abb_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
1221 nvl(p_rec.attribute7, hr_api.g_varchar2) or
1222 nvl(per_abb_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
1223 nvl(p_rec.attribute8, hr_api.g_varchar2) or
1224 nvl(per_abb_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
1225 nvl(p_rec.attribute9, hr_api.g_varchar2) or
1226 nvl(per_abb_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
1227 nvl(p_rec.attribute10, hr_api.g_varchar2) or
1228 nvl(per_abb_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
1229 nvl(p_rec.attribute11, hr_api.g_varchar2) or
1230 nvl(per_abb_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
1231 nvl(p_rec.attribute12, hr_api.g_varchar2) or
1232 nvl(per_abb_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
1233 nvl(p_rec.attribute13, hr_api.g_varchar2) or
1234 nvl(per_abb_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
1235 nvl(p_rec.attribute14, hr_api.g_varchar2) or
1236 nvl(per_abb_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
1237 nvl(p_rec.attribute15, hr_api.g_varchar2) or
1238 nvl(per_abb_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
1239 nvl(p_rec.attribute16, hr_api.g_varchar2) or
1240 nvl(per_abb_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
1241 nvl(p_rec.attribute17, hr_api.g_varchar2) or
1242 nvl(per_abb_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
1243 nvl(p_rec.attribute18, hr_api.g_varchar2) or
1244 nvl(per_abb_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
1245 nvl(p_rec.attribute19, hr_api.g_varchar2) or
1246 nvl(per_abb_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
1247 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
1248 or (p_rec.absence_attendance_type_id is null) then
1249 --
1250 -- Only execute the validation if absolutely necessary:
1251 -- a) During update, the structure column value or any
1252 -- of the attribute values have actually changed.
1253 -- b) During insert.
1254 --
1255 hr_dflex_utility.ins_or_upd_descflex_attribs
1256 (p_appl_short_name => 'PER'
1257 ,p_descflex_name => 'PER_ABSENCE_ATTENDANCE_TYPES'
1258 ,p_attribute_category => p_rec.attribute_category
1259 ,p_attribute1_name => 'ATTRIBUTE1'
1260 ,p_attribute1_value => p_rec.attribute1
1261 ,p_attribute2_name => 'ATTRIBUTE2'
1262 ,p_attribute2_value => p_rec.attribute2
1263 ,p_attribute3_name => 'ATTRIBUTE3'
1264 ,p_attribute3_value => p_rec.attribute3
1265 ,p_attribute4_name => 'ATTRIBUTE4'
1266 ,p_attribute4_value => p_rec.attribute4
1267 ,p_attribute5_name => 'ATTRIBUTE5'
1268 ,p_attribute5_value => p_rec.attribute5
1269 ,p_attribute6_name => 'ATTRIBUTE6'
1270 ,p_attribute6_value => p_rec.attribute6
1271 ,p_attribute7_name => 'ATTRIBUTE7'
1272 ,p_attribute7_value => p_rec.attribute7
1273 ,p_attribute8_name => 'ATTRIBUTE8'
1274 ,p_attribute8_value => p_rec.attribute8
1275 ,p_attribute9_name => 'ATTRIBUTE9'
1276 ,p_attribute9_value => p_rec.attribute9
1277 ,p_attribute10_name => 'ATTRIBUTE10'
1278 ,p_attribute10_value => p_rec.attribute10
1279 ,p_attribute11_name => 'ATTRIBUTE11'
1280 ,p_attribute11_value => p_rec.attribute11
1281 ,p_attribute12_name => 'ATTRIBUTE12'
1282 ,p_attribute12_value => p_rec.attribute12
1283 ,p_attribute13_name => 'ATTRIBUTE13'
1284 ,p_attribute13_value => p_rec.attribute13
1285 ,p_attribute14_name => 'ATTRIBUTE14'
1286 ,p_attribute14_value => p_rec.attribute14
1287 ,p_attribute15_name => 'ATTRIBUTE15'
1288 ,p_attribute15_value => p_rec.attribute15
1289 ,p_attribute16_name => 'ATTRIBUTE16'
1290 ,p_attribute16_value => p_rec.attribute16
1291 ,p_attribute17_name => 'ATTRIBUTE17'
1292 ,p_attribute17_value => p_rec.attribute17
1293 ,p_attribute18_name => 'ATTRIBUTE18'
1294 ,p_attribute18_value => p_rec.attribute18
1295 ,p_attribute19_name => 'ATTRIBUTE19'
1296 ,p_attribute19_value => p_rec.attribute19
1297 ,p_attribute20_name => 'ATTRIBUTE20'
1298 ,p_attribute20_value => p_rec.attribute20
1299 );
1300 end if;
1301 --
1302 hr_utility.set_location(' Leaving:'||l_proc,20);
1303 end chk_df;
1304 --
1305 -- ----------------------------------------------------------------------------
1306 -- |-----------------------< chk_non_updateable_args >------------------------|
1307 -- ----------------------------------------------------------------------------
1308 -- {Start Of Comments}
1309 --
1310 -- Description:
1311 -- This procedure is used to ensure that non updateable attributes have
1312 -- not been updated. If an attribute has been updated an error is generated.
1313 --
1314 -- Pre Conditions:
1315 -- g_old_rec has been populated with details of the values currently in
1316 -- the database.
1317 --
1318 -- In Arguments:
1319 -- p_rec has been populated with the updated values the user would like the
1320 -- record set to.
1321 --
1322 -- Post Success:
1323 -- Processing continues if all the non updateable attributes have not
1324 -- changed.
1325 --
1326 -- Post Failure:
1327 -- An application error is raised if any of the non updatable attributes
1328 -- have been altered.
1329 --
1330 -- {End Of Comments}
1331 -- ----------------------------------------------------------------------------
1332 Procedure chk_non_updateable_args
1333 (p_rec in per_abb_shd.g_rec_type
1334 ) IS
1335 --
1336 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
1337 l_error exception;
1338 l_argument varchar2(30);
1339 --
1340 Begin
1341 hr_utility.set_location('Entering:'||l_proc, 5);
1342 --
1343 -- Only proceed with the validation if a row exists for the current
1344 -- record in the HR Schema.
1345 --
1346 IF NOT per_abb_shd.api_updating
1347 (p_absence_attendance_type_id => p_rec.absence_attendance_type_id
1348 ,p_object_version_number => p_rec.object_version_number
1349 ) THEN
1350 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
1351 fnd_message.set_token('PROCEDURE ', l_proc);
1352 fnd_message.set_token('STEP ', '5');
1353 fnd_message.raise_error;
1354 END IF;
1355 --
1356 hr_utility.set_location(l_proc, 10);
1357 --
1358 if nvl(per_abb_shd.g_old_rec.business_group_id,-1) <>
1359 nvl(p_rec.business_group_id,-1) then
1360 l_argument := 'business_group_id';
1361 raise l_error;
1362 end if;
1363 --
1364 hr_utility.set_location(l_proc, 20);
1365 --
1366 if nvl(per_abb_shd.g_old_rec.information_category,hr_api.g_varchar2) <>
1367 nvl(p_rec.information_category,hr_api.g_varchar2) then
1368 l_argument := 'information_category';
1369 raise l_error;
1370 end if;
1371 --
1372 --
1373 hr_utility.set_location(l_proc, 30);
1374 --
1375 if per_abb_shd.g_old_rec.absence_attendance_type_id <>
1376 p_rec.absence_attendance_type_id then
1377 l_argument := 'absence_attendance_type_id';
1378 raise l_error;
1379 end if;
1380 --
1381 hr_utility.set_location(' Leaving:'||l_proc, 40);
1382 --
1383 exception
1384 when l_error then
1385 hr_api.argument_changed_error
1386 (p_api_name => l_proc
1387 ,p_argument => l_argument);
1388 when others then
1389 raise;
1390 hr_utility.set_location(' Leaving:'||l_proc, 90);
1391 End chk_non_updateable_args;
1392 --
1393 -- ----------------------------------------------------------------------------
1394 -- |---------------------------< insert_validate >----------------------------|
1395 -- ----------------------------------------------------------------------------
1396 Procedure insert_validate
1397 (p_rec in out nocopy per_abb_shd.g_rec_type
1398 ) is
1399 --
1400 l_proc varchar2(72) := g_package||'insert_validate';
1401 --
1402 Begin
1403 hr_utility.set_location('Entering:'||l_proc, 5);
1404 --
1405 -- Call all supporting business operations
1406 --
1407 if p_rec.business_group_id is not null then
1408 hr_api.validate_bus_grp_id
1409 (p_business_group_id => p_rec.business_group_id
1410 );
1411 end if;
1412 --
1413 -- After validating the set of important attributes,
1414 -- if Multiple Message detection is enabled and at least
1415 -- one error has been found then abort further validation.
1416 --
1417 hr_multi_message.end_validation_set;
1418 --
1419 -- Validate Dependent Attributes
1420 --
1421 per_abb_bus.chk_absence_type_dates
1422 (p_absence_attendance_type_id => p_rec.absence_attendance_type_id
1423 ,p_object_version_number => p_rec.object_version_number
1424 ,p_date_effective => p_rec.date_effective
1425 ,p_date_end => p_rec.date_end
1426 );
1427 --
1428 per_abb_bus.chk_input_value_id
1429 (p_absence_attendance_type_id => p_rec.absence_attendance_type_id
1430 ,p_object_version_number => p_rec.object_version_number
1431 ,p_date_effective => p_rec.date_effective
1432 ,p_date_end => p_rec.date_end
1433 ,p_input_value_id => p_rec.input_value_id
1434 ,p_business_group_id => p_rec.business_group_id
1435 );
1436 --
1437 per_abb_bus.chk_inc_or_dec_flag
1438 (p_absence_attendance_type_id => p_rec.absence_attendance_type_id
1439 ,p_object_version_number => p_rec.object_version_number
1440 ,p_input_value_id => p_rec.input_value_id
1441 ,p_inc_or_dec_flag => p_rec.increasing_or_decreasing_flag
1442 );
1443 --
1444 per_abb_bus.chk_hours_or_days
1445 (p_absence_attendance_type_id => p_rec.absence_attendance_type_id
1446 ,p_object_version_number => p_rec.object_version_number
1447 ,p_input_value_id => p_rec.input_value_id
1448 ,p_hours_or_days => p_rec.hours_or_days
1449 ,p_date_effective => p_rec.date_effective
1450 );
1451 --
1452 per_abb_bus.chk_absence_category
1453 (p_absence_attendance_type_id => p_rec.absence_attendance_type_id
1454 ,p_object_version_number => p_rec.object_version_number
1455 ,p_date_effective => p_rec.date_effective
1456 ,p_absence_category => p_rec.absence_category
1457 );
1458 --
1459 per_abb_bus.chk_information_category
1460 (p_absence_attendance_type_id => p_rec.absence_attendance_type_id
1461 ,p_object_version_number => p_rec.object_version_number
1462 ,p_business_group_id => p_rec.business_group_id
1463 ,p_information_category => p_rec.information_category
1464 );
1465 --
1466 per_abb_bus.chk_abs_overlap_flag
1467 (p_absence_overlap_flag => p_rec.absence_overlap_flag
1468 );
1469 --
1470 per_abb_bus.chk_ddf(p_rec);
1471 --
1472 per_abb_bus.chk_df(p_rec);
1473 --
1474 hr_utility.set_location(' Leaving:'||l_proc, 10);
1475 End insert_validate;
1476 --
1477 -- ----------------------------------------------------------------------------
1478 -- |---------------------------< update_validate >----------------------------|
1479 -- ----------------------------------------------------------------------------
1480 Procedure update_validate
1481 (p_rec in out nocopy per_abb_shd.g_rec_type
1482 ) is
1483 --
1484 l_proc varchar2(72) := g_package||'update_validate';
1485 --
1486 Begin
1487 hr_utility.set_location('Entering:'||l_proc, 5);
1488 --
1489 -- Call all supporting business operations
1490 --
1491 if p_rec.business_group_id is not null then
1492 hr_api.validate_bus_grp_id
1493 (p_business_group_id => p_rec.business_group_id
1494 );
1495 end if;
1496 --
1497 -- After validating the set of important attributes,
1498 -- if Multiple Message detection is enabled and at least
1499 -- one error has been found then abort further validation.
1500 --
1501 hr_multi_message.end_validation_set;
1502 --
1503 -- Validate Dependent Attributes
1504 --
1505 chk_non_updateable_args
1506 (p_rec => p_rec
1507 );
1508 --
1509 per_abb_bus.chk_absence_type_dates
1510 (p_absence_attendance_type_id => p_rec.absence_attendance_type_id
1511 ,p_object_version_number => p_rec.object_version_number
1512 ,p_date_effective => p_rec.date_effective
1513 ,p_date_end => p_rec.date_end
1514 );
1515 --
1516 per_abb_bus.chk_input_value_id
1517 (p_absence_attendance_type_id => p_rec.absence_attendance_type_id
1518 ,p_object_version_number => p_rec.object_version_number
1519 ,p_date_effective => p_rec.date_effective
1520 ,p_date_end => p_rec.date_end
1521 ,p_input_value_id => p_rec.input_value_id
1522 ,p_business_group_id => p_rec.business_group_id
1523 );
1524 --
1525 per_abb_bus.chk_inc_or_dec_flag
1526 (p_absence_attendance_type_id => p_rec.absence_attendance_type_id
1527 ,p_object_version_number => p_rec.object_version_number
1528 ,p_input_value_id => p_rec.input_value_id
1529 ,p_inc_or_dec_flag => p_rec.increasing_or_decreasing_flag
1530 );
1531 --
1532 per_abb_bus.chk_hours_or_days
1533 (p_absence_attendance_type_id => p_rec.absence_attendance_type_id
1534 ,p_object_version_number => p_rec.object_version_number
1535 ,p_input_value_id => p_rec.input_value_id
1536 ,p_hours_or_days => p_rec.hours_or_days
1537 ,p_date_effective => p_rec.date_effective
1538 );
1539 --
1540 per_abb_bus.chk_absence_category
1541 (p_absence_attendance_type_id => p_rec.absence_attendance_type_id
1542 ,p_object_version_number => p_rec.object_version_number
1543 ,p_date_effective => p_rec.date_effective
1544 ,p_absence_category => p_rec.absence_category
1545 );
1546 --
1547 per_abb_bus.chk_information_category
1548 (p_absence_attendance_type_id => p_rec.absence_attendance_type_id
1549 ,p_object_version_number => p_rec.object_version_number
1550 ,p_business_group_id => p_rec.business_group_id
1551 ,p_information_category => p_rec.information_category
1552 );
1553 --
1554 per_abb_bus.chk_ddf(p_rec);
1555 --
1556 per_abb_bus.chk_df(p_rec);
1557 --
1558 hr_utility.set_location(' Leaving:'||l_proc, 10);
1559 End update_validate;
1560 --
1561 -- ----------------------------------------------------------------------------
1562 -- |---------------------------< delete_validate >----------------------------|
1563 -- ----------------------------------------------------------------------------
1564 Procedure delete_validate
1565 (p_rec in per_abb_shd.g_rec_type
1566 ) is
1567 --
1568 l_proc varchar2(72) := g_package||'delete_validate';
1569 --
1570 Begin
1571 hr_utility.set_location('Entering:'||l_proc, 5);
1572 --
1573 -- Call all supporting business operations
1574 --
1575 per_abb_bus.chk_absence_type_delete
1576 (p_absence_attendance_type_id => p_rec.absence_attendance_type_id
1577 );
1578 --
1579 hr_utility.set_location(' Leaving:'||l_proc, 10);
1580 End delete_validate;
1581 --
1582 end per_abb_bus;