1 PACKAGE BODY PAY_FR_SICKNESS_ABSENCE AS
2 /* $Header: perfrabs.pkb 120.1.12000000.2 2007/02/28 11:04:00 spendhar ship $ */
3 --
4 g_package varchar2(50) := 'pay_fr_sickness_absence.';
5 l_adoption_spouse_min_leave Number;
6
7 PROCEDURE PERSON_ABSENCE_CREATE(
8 p_business_group_id IN Number
9 ,p_abs_information_category IN varchar2
10 ,p_person_id IN Number
11 ,p_date_start IN Date
12 ,p_date_end IN Date
13 ,p_abs_information1 IN Varchar2
14 ,p_abs_information4 IN Varchar2
15 ,p_abs_information5 IN Varchar2
16 ,p_abs_information6 IN Varchar2
17 ,p_abs_information7 IN Varchar2
18 ,p_abs_information8 IN Varchar2
19 ,p_abs_information9 IN Varchar2
20 ,p_abs_information10 IN Varchar2
21 ,p_abs_information11 IN Varchar2
22 ,p_abs_information12 IN Varchar2
23 ) IS
24
25 l_proc varchar2(200) := g_package ||'person_absence_create';
26 --
27
28 begin
29
30 --
31 /* Added for GSI Bug 5472781 */
32 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'FR') THEN
33 hr_utility.set_location('Leaving : '||l_proc , 10);
34 return;
35 END IF;
36 --
37 -- get the values of the person_id profile
38 fnd_profile.put('HR_FR_PERSON_ID',p_person_id);
39 -- get the value of the absence_start_date
40 fnd_profile.put('HR_FR_ABSENCE_START_DATE',to_char(p_date_start,'DD-MON-YYYY'));
41
42
43
44 If p_abs_information_category = 'FR_S' then
45
46
47
48 --
49 IF p_abs_information1 IS NOT NULL THEN
50 -- Current absence is Child absence
51 IF (p_abs_information9 IS NOT NULL
52 OR p_abs_information10 IS NOT NULL
53 OR p_abs_information11 IS NOT NULL
54 OR p_abs_information12 IS NOT NULL) THEN
55 hr_utility.set_message(801,'PAY_75030_INV_ELIGIBILITY_SEG');
56 hr_utility.raise_error;
57 END IF;
58 -- #3040003
59 IF (p_abs_information7 IS NOT NULL) THEN --IJSS Inelig dt
60 hr_utility.set_message(801,'PAY_75079_INV_IJSS_INELIG_DT');
61 hr_utility.raise_error;
62 END IF;
63
64 --
65 END IF;
66 --
67
68 ElsIf p_abs_information_category = 'FR_M' Then
69 --
70 If p_abs_information1 Is Not Null --Child Absence
71 And
72 (p_abs_information4 Is Not Null
73 Or p_abs_information5 Is Not Null
74 Or p_abs_information6 Is Not Null
75 Or p_abs_information7 Is Not Null
76 Or p_abs_information8 Is Not Null
77 Or p_abs_information9 Is Not Null) Then
78
79 hr_utility.set_message(801,'PAY_75044_INV_MATERNITY_SEG');
80 hr_utility.raise_error;
81 End If;
82 --
83 If (fnd_date.canonical_to_date(p_abs_information4) < p_date_start or
84 fnd_date.canonical_to_date(p_abs_information4) > p_date_end ) Then
85 hr_utility.set_message(801,'PAY_75045_INV_MAT_CONFINE_DATE');
86 hr_utility.raise_error;
87 End If;
88 --
89
90 ElsIf p_abs_information_category = 'FR_FR_ADOPTION' Then
91 --
92 select global_value
93 Into l_adoption_spouse_min_leave
94 from ff_globals_f
95 where global_name ='FR_ADOPTION_SPOUSE_MIN_LEAVE'
96 and legislation_code = 'FR'
97 and business_group_id is null;
98 --
99 If p_abs_information7 < l_adoption_spouse_min_leave And p_abs_information7 <> 0 Then
100 hr_utility.set_message(801,'PAY_75046_ADPT_MIN_SPOUSE_LVE');
101 hr_utility.set_message_token(801,'GLOBAL_VAL',l_adoption_spouse_min_leave);
102 hr_utility.raise_error;
103 End If;
104 --
105
106 End If;
107
108
109 --
110 end PERSON_ABSENCE_CREATE;
111 --
112 --
113 procedure PERSON_ABSENCE_UPDATE(
114 p_absence_attendance_id IN Number
115 ,p_abs_information_category IN varchar2
116 ,p_date_start IN Date
117 ,p_date_end IN Date
118 ,p_abs_information1 IN Varchar2
119 ,p_abs_information4 IN Varchar2
120 ,p_abs_information5 IN Varchar2
121 ,p_abs_information6 IN Varchar2
122 ,p_abs_information7 IN Varchar2
123 ,p_abs_information8 IN Varchar2
124 ,p_abs_information9 IN Varchar2
125 ,p_abs_information10 IN Varchar2
126 ,p_abs_information11 IN Varchar2
127 ,p_abs_information12 IN Varchar2
128 ) IS
129
130 l_person_id number;
131 l_proc varchar2(200) := g_package||'person_absence_update';
132 --
133 cursor get_person_id(p_absence_attendance_id in number) is
134 select person_id
135 from per_absence_attendances
136 where absence_attendance_id =p_absence_attendance_id;
137 --
138 begin
139 --
140 /* Added for GSI Bug 5472781 */
141 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'FR') THEN
142 hr_utility.set_location('Leaving : '||l_proc , 10);
143 return;
144 END IF;
145 --
146 open get_person_id(p_absence_attendance_id);
147 fetch get_person_id into l_person_id;
148 -- get the values of the person_id profile
149 fnd_profile.put('HR_FR_PERSON_ID',l_person_id);
150 close get_person_id;
151 -- get the value of the absence_start_date
152 fnd_profile.put('HR_FR_ABSENCE_START_DATE',to_char(p_date_start,'DD-MON-YYYY'));
153
154
155 If p_abs_information_category = 'FR_S' then
156
157
158 --
159 IF p_abs_information1 IS NOT NULL THEN
160 -- Current absence is Child absence
161 IF (p_abs_information9 IS NOT NULL
162 OR p_abs_information10 IS NOT NULL
163 OR p_abs_information11 IS NOT NULL
164 OR p_abs_information12 IS NOT NULL) THEN
165 hr_utility.set_message(801,'PAY_75030_INV_ELIGIBILITY_SEG');
166 hr_utility.raise_error;
167 END IF;
168 -- #3040003
169 IF (p_abs_information7 IS NOT NULL) THEN --IJSS Inelig dt
170 hr_utility.set_message(801,'PAY_75079_INV_IJSS_INELIG_DT');
171 hr_utility.raise_error;
172 END IF;
173
174 --
175 END IF;
176 --
177
178 ElsIf p_abs_information_category = 'FR_M' Then
179
180 If p_abs_information1 Is Not Null
181 And --Child Absence
182 (p_abs_information4 Is Not Null
183 Or p_abs_information5 Is Not Null
184 Or p_abs_information6 Is Not Null
185 Or p_abs_information7 Is Not Null
186 Or p_abs_information8 Is Not Null
187 Or p_abs_information9 Is Not Null) Then
188
189 hr_utility.set_message(801,'PAY_75044_INV_MATERNITY_SEG');
190 hr_utility.raise_error;
191
192 End If;
193
194 --
195 If (fnd_date.canonical_to_date(p_abs_information4) < p_date_start or
196 fnd_date.canonical_to_date(p_abs_information4) > p_date_end ) Then
197
198 hr_utility.set_message(801,'PAY_75045_INV_MAT_CONFINE_DATE');
199 hr_utility.raise_error;
200
201 End If;
202 --
203
204 ElsIf p_abs_information_category = 'FR_FR_ADOPTION' Then
205 --
206 select global_value
207 Into l_adoption_spouse_min_leave
208 from ff_globals_f
209 where global_name ='FR_ADOPTION_SPOUSE_MIN_LEAVE'
210 and legislation_code = 'FR'
211 and business_group_id is null;
212 --
213 If p_abs_information7 < l_adoption_spouse_min_leave And p_abs_information7 <> 0 Then
214 hr_utility.set_message(801,'PAY_75046_ADPT_MIN_SPOUSE_LVE');
215 hr_utility.set_message_token(801,'GLOBAL_VAL',l_adoption_spouse_min_leave);
216 hr_utility.raise_error;
217 End If;
218 --
219
220 End If;
221
222 --
223 end PERSON_ABSENCE_UPDATE;
224 --
225 --
226 procedure PERSON_ENTRY_CREATE(
227 p_business_group_id IN Number
228 ,p_absence_attendance_id IN Number
229 ,p_abs_information_category IN Varchar2
230 ,p_date_start IN Date
231 ) IS
232
233 l_proc VARCHAR2(200) := g_package ||'person_entry_create';
234 l_element_entry_id NUMBER;
235 l_effective_start_date DATE;
236 l_effective_end_date DATE;
237 l_ovn NUMBER;
238 l_sub NUMBER;
239 l_o_start_dt DATE;
240 l_o_end_dt DATE;
241 l_o_warning BOOLEAN;
242
243 BEGIN
244 --
245 /* Added for GSI Bug 5472781 */
246 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'FR') THEN
247 hr_utility.set_location('Leaving : '||l_proc , 10);
248 return;
249 END IF;
250 --
251 hr_utility.set_location('Entering hook Person_entry_Create',10);
252 If p_abs_information_category = 'FR_S' then
253 hr_utility.set_location(' IN Create User hook ',20);
254 HR_PERSON_ABSENCE_API.get_absence_element
255 (p_absence_attendance_id => p_absence_attendance_id
256 ,p_element_entry_id => l_element_entry_id
257 ,p_effective_start_date => l_effective_start_date
258 ,p_effective_end_date => l_effective_end_date);
259 hr_utility.set_location(' IN Create, setting subpriority for entry_id='||l_element_entry_id,30);
260
261 SELECT max(object_version_number) INTO l_ovn
262 FROM pay_element_entries_f
263 WHERE element_entry_id = l_element_entry_id;
264
265 SELECT to_number(substr(to_char(p_date_start,'J'),4,4)) INTO l_sub
266 FROM dual;
267
268 hr_utility.set_location(' IN Create, sub='||l_sub||' ovn='||l_ovn,30);
269
270 PAY_ELEMENT_ENTRY_API.update_element_entry
271 (p_validate => FALSE
272 ,p_datetrack_update_mode => 'CORRECTION'
273 ,p_effective_date => l_effective_start_date
274 ,p_business_group_id => p_business_group_id
275 ,p_element_entry_id => l_element_entry_id
276 ,p_object_version_number => l_ovn
277 ,p_subpriority => l_sub
278 ,p_effective_start_date => l_o_start_dt
279 ,p_effective_end_date => l_o_end_dt
280 ,p_update_warning => l_o_warning);
281
282
283 END IF;
284 hr_utility.set_location('Leaving hook Person_entry_Create',90);
285 END PERSON_ENTRY_CREATE;
286 --
287 procedure PERSON_ENTRY_UPDATE(
288 p_absence_attendance_id IN Number
289 ,p_abs_information_category IN Varchar2
290 ,p_date_start IN Date
291 ) IS
292 l_proc VARCHAR2(200) := g_package ||'person_entry_update';
293 l_element_entry_id NUMBER;
294 l_effective_start_date DATE;
295 l_effective_end_date DATE;
296 l_ovn NUMBER;
297 l_sub NUMBER;
298 l_o_start_dt DATE;
299 l_o_end_dt DATE;
300 l_o_warning BOOLEAN;
301 l_bus_grp_id NUMBER;
302
303 BEGIN
304 --
305 /* Added for GSI Bug 5472781 */
306 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'FR') THEN
307 hr_utility.set_location('Leaving : '||l_proc , 10);
308 return;
309 END IF;
310 --
311 hr_utility.set_location('Entering hook Person_entry_Update',10);
312 If p_abs_information_category = 'FR_S' then
313 hr_utility.set_location(' IN Update User hook ',20);
314 HR_PERSON_ABSENCE_API.get_absence_element
315 (p_absence_attendance_id => p_absence_attendance_id
316 ,p_element_entry_id => l_element_entry_id
317 ,p_effective_start_date => l_effective_start_date
318 ,p_effective_end_date => l_effective_end_date);
319 hr_utility.set_location(' IN Update, setting subpriority for entry_id='||l_element_entry_id,30);
320
321 SELECT max(object_version_number) INTO l_ovn
322 FROM pay_element_entries_f
323 WHERE element_entry_id = l_element_entry_id;
324
325 select business_group_id INTO l_bus_grp_id
326 from per_absence_attendances
327 where absence_attendance_id = p_absence_attendance_id;
328
329 SELECT to_number(substr(to_char(p_date_start,'J'),4,4)) INTO l_sub
330 FROM dual;
331
332 hr_utility.set_location(' IN Update, sub='||l_sub||' ovn='||l_ovn,30);
333
334 PAY_ELEMENT_ENTRY_API.update_element_entry
335 (p_validate => FALSE
336 ,p_datetrack_update_mode => 'CORRECTION'
337 ,p_effective_date => l_effective_start_date
338 ,p_business_group_id => l_bus_grp_id
339 ,p_element_entry_id => l_element_entry_id
340 ,p_object_version_number => l_ovn
341 ,p_subpriority => l_sub
342 ,p_effective_start_date => l_o_start_dt
343 ,p_effective_end_date => l_o_end_dt
344 ,p_update_warning => l_o_warning);
345
346
347
348 END IF;
349 hr_utility.set_location('Leaving hook Person_entry_Update',90);
350 END PERSON_ENTRY_UPDATE;
351 --
352 -- procedure added for checking additional holiday entitlements
353 PROCEDURE CHECK_ADD_ABS_ENT_CREATE(p_absence_days in number,
354 p_absence_attendance_type_id in number,
355 p_date_start in date,
356 p_person_id in number)
357 IS
358 -- Cursor to find the accrual plan id
359 -- and absence type
360 Cursor csr_get_accrual_plan is
361 Select pap.accrual_plan_id, pabt.absence_category
362 from pay_accrual_plans pap,
363 per_absence_attendance_types pabt
364 where pabt.absence_attendance_type_id = p_absence_attendance_type_id
365 and pap.pto_input_value_id= pabt.input_value_id;
366 --
367 -- Defining cursor to get existing entitlements
368 -- Modified cursor to check for element entry dates
369 -- instead of value of 'Accrual Date'
370 cursor csr_get_ent(
371 c_type_m_iv_id number
372 ,c_start_date date
373 ,c_end_date date) is
374 select nvl(sum(pevm.screen_entry_value),0)
375 from pay_element_entry_values_f pevm
376 ,pay_element_entries_f pee
377 ,per_all_assignments_f pasg
378 where pevm.input_value_id = c_type_m_iv_id
379 and pee.element_entry_id = pevm.element_entry_id
380 and pevm.effective_start_date between c_start_date and c_end_date
381 and pee.effective_start_date between c_start_date and c_end_date
382 and pee.assignment_id = pasg.assignment_id
383 and pasg.person_id = p_person_id;
384 --
385 -- Defining cursor selecting hiredate
386 Cursor csr_assg_hiredate(c_person_id number) is
387 Select max(ppos.date_start)
388 From per_periods_of_service ppos
389 Where ppos.person_id = c_person_id
390 And ppos.date_start <= p_date_start;
391 --
392 -- Cursor to sum up existing absences against entitled holidays
393 Cursor csr_exist_abs(p_from_date date) is
394 Select sum(date_end-date_start+1)
395 from per_absence_attendances
396 where person_id = p_person_id
397 and date_end < p_date_start
398 and date_start >= p_from_date
399 and absence_attendance_type_id = p_absence_attendance_type_id;
400 --
401 l_fr_plan_info pay_fr_pto_pkg.g_fr_plan_info;
402 l_accrual_plan_id number;
403 l_absence_type varchar2(30);
404 l_ent_m number :=0;
405 l_hiredate date;
406 l_exist_absence number;
407 l_net_entitlement number;
411 begin
408 l_proc varchar2(200) := g_package ||'check_add_abs_ent_create';
409 --
410 --
412 --
413 /* Added for GSI Bug 5472781 */
414 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'FR') THEN
415 hr_utility.set_location('Leaving : '|| l_proc , 22);
416 return;
417 END IF;
418 --
419
420 hr_utility.set_location('Entering entilement check user hook', 22);
421 -- Get accrual plan details
422 open csr_get_accrual_plan;
423 fetch csr_get_accrual_plan into l_accrual_plan_id,l_absence_type;
424 close csr_get_accrual_plan;
425 hr_utility.set_location('Absence type is : '||l_absence_type,22);
426 --
427 IF l_absence_type = 'FR_ADDITIONAL_HOLIDAY' THEN
428 l_fr_plan_info := pay_fr_pto_pkg.get_fr_accrual_plan_info(
429 p_accrual_plan_id => l_accrual_plan_id
430 ,p_accrual_date => p_date_start );
431 -- Get the hire date
432 -- to get the date range for checking absence
433 open csr_assg_hiredate(p_person_id);
434 fetch csr_assg_hiredate into l_hiredate;
435 close csr_assg_hiredate;
436 -- Calculate the sum of total entitlements created for this accrual plan
437 -- within the hire date and absence start date
438 -- Modified cursor call for parameters passed
439 open csr_get_ent(l_fr_plan_info.ent_m_iv_id,
440 l_hiredate,
441 p_date_start);
442 fetch csr_get_ent into l_ent_m;
443 close csr_get_ent;
444 hr_utility.set_location('total entitlements are :'||l_ent_m, 22);
445 -- Check for sum of existing absences against additional entitlements
446 open csr_exist_abs(l_hiredate);
447 fetch csr_exist_abs into l_exist_absence;
448 close csr_exist_abs;
449 IF l_exist_absence IS NULL THEN
450 l_exist_absence := 0;
451 END IF;
452 hr_utility.set_location('Total absences are : '||l_exist_absence, 22);
453 --
454 l_net_entitlement := l_ent_m - l_exist_absence - p_absence_days;
455 hr_utility.set_location('Net entitlements are : '||l_net_entitlement, 22);
456 IF l_net_entitlement <0 THEN
457 -- fnd_message.set_name('PER','HR_EMP_NOT_ENTITLED');
458 -- replaced by a different function and message
459 hr_utility.set_message(801,'PAY_75197_ADD_ENT_EXCEED');
460 hr_utility.raise_error;
461 --
462 END IF;
463 --
464 END IF;-- check for absence type
465 hr_utility.set_location('Leaving entilement check user hook', 22);
466 END CHECK_ADD_ABS_ENT_CREATE;
467 --
468 PROCEDURE CHECK_ADD_ABS_ENT_UPDATE(p_absence_days in number,
469 p_absence_attendance_id in number,
470 p_date_start in date)
471 IS
472 --
473 -- Cursor to find the accrual plan id
474 -- and absence type
475 Cursor csr_get_accrual_plan is
476 Select pap.accrual_plan_id,
477 pabt.absence_category,
478 pabs.person_id,
479 pabt.absence_attendance_type_id
480 from pay_accrual_plans pap,
481 per_absence_attendance_types pabt,
482 per_absence_attendances pabs
483 where pabs.absence_attendance_id = p_absence_attendance_id
484 and pabt.absence_attendance_type_id = pabs.absence_attendance_type_id
485 and pap.pto_input_value_id= pabt.input_value_id;
486 --
487 -- Defining cursor to get existing entitlements
488 -- Modfied cursor to check for element entry date
489 -- instead of value of 'Accrual Date'
490 cursor csr_get_ent(
491 c_type_m_iv_id number
492 ,c_start_date date
493 ,c_end_date date
494 ,c_person_id number) is
495 select nvl(sum(pevm.screen_entry_value),0)
496 from pay_element_entry_values_f pevm
497 ,pay_element_entries_f pee
498 ,per_all_assignments_f pasg
499 where pevm.input_value_id = c_type_m_iv_id
500 and pee.element_entry_id = pevm.element_entry_id
501 and pevm.effective_start_date between c_start_date and c_end_date
502 and pee.effective_start_date between c_start_date and c_end_date
503 and pee.assignment_id = pasg.assignment_id
504 and pasg.person_id = c_person_id;
505 --
506 -- Defining cursor selecting hiredate
507 Cursor csr_assg_hiredate(c_person_id number) is
508 Select max(ppos.date_start)
509 From per_periods_of_service ppos
510 Where ppos.person_id = c_person_id
511 And ppos.date_start <= p_date_start;
512 --
513 -- Cursor to sum up existing absences against entitled holidays
514 Cursor csr_exist_abs(p_person_id number,
515 p_absence_attendance_type_id number,
516 p_from_date date) is
517 Select sum(date_end-date_start+1)
518 from per_absence_attendances
519 where person_id = p_person_id
520 and date_end < p_date_start
521 and date_start >= p_from_date
522 and absence_attendance_type_id = p_absence_attendance_type_id;
523 --
524 l_fr_plan_info pay_fr_pto_pkg.g_fr_plan_info;
525 l_accrual_plan_id number;
526 l_absence_type varchar2(30);
527 l_person_id number;
531 l_exist_absence number;
528 l_absence_attendance_type_id number;
529 l_ent_m number :=0;
530 l_hiredate date;
532 l_net_entitlement number;
533 l_proc varchar2(200) := g_package ||'check_add_abs_ent_update';
534 --
535 --
536 begin
537 --
538 /* Added for GSI Bug 5472781 */
539 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'FR') THEN
540 hr_utility.set_location('Leaving : '|| l_proc , 22);
541 return;
542 END IF;
543 --
544 hr_utility.set_location('Entering entilement update user hook', 22);
545 -- Get accrual plan details
546 open csr_get_accrual_plan;
547 fetch csr_get_accrual_plan into l_accrual_plan_id,l_absence_type, l_person_id, l_absence_attendance_type_id;
548 close csr_get_accrual_plan;
549 hr_utility.set_location('Absence type is : '||l_absence_type, 22);
550 --
551 IF l_absence_type = 'FR_ADDITIONAL_HOLIDAY' THEN
552 hr_utility.set_location('Absence is additional type', 22);
553 hr_utility.set_location('Plan id is : '||to_char(l_accrual_plan_id),22);
554 hr_utility.set_location('Accrual Date is : '||to_char(p_date_start),22);
555 l_fr_plan_info := pay_fr_pto_pkg.get_fr_accrual_plan_info(
556 p_accrual_plan_id => l_accrual_plan_id
557 ,p_accrual_date => p_date_start );
558 -- Get the hire date
559 -- to get the date range for checking absence
560 open csr_assg_hiredate(l_person_id);
561 fetch csr_assg_hiredate into l_hiredate;
562 close csr_assg_hiredate;
563 hr_utility.set_location('Hiredate is : '||to_char(l_hiredate),22);
564 -- Calculate the sum of total entitlements created for this accrual plan
565 -- within the hire date and absence start date
566 hr_utility.set_location('Entilements input id is : '||to_char(l_fr_plan_info.ent_m_iv_id),22);
567 hr_utility.set_location('Accrual date input id is : '||to_char(l_fr_plan_info.ent_accrual_date_iv_id), 22);
568 -- modified cursor call
569 open csr_get_ent(l_fr_plan_info.ent_m_iv_id,
570 l_hiredate,
571 p_date_start,
572 l_person_id);
573 fetch csr_get_ent into l_ent_m;
574 close csr_get_ent;
575 hr_utility.set_location('total entitlements are :'||l_ent_m, 22);
576 -- Check for sum of existing absences against additional entitlements
577 open csr_exist_abs(l_person_id,l_absence_attendance_type_id,l_hiredate);
578 fetch csr_exist_abs into l_exist_absence;
579 close csr_exist_abs;
580 IF l_exist_absence IS NULL THEN
581 l_exist_absence := 0;
582 END IF;
583 hr_utility.set_location('Total absences are : '||l_exist_absence, 22);
584 --
585 l_net_entitlement := l_ent_m - l_exist_absence - p_absence_days;
586 hr_utility.set_location('Net entitlements are : '||l_net_entitlement, 22);
587 IF l_net_entitlement <0 THEN
588 -- fnd_message.set_name('PER','HR_EMP_NOT_ENTITLED');
589 -- replaced by a different function and message
590 hr_utility.set_message(801,'PAY_75197_ADD_ENT_EXCEED');
591 hr_utility.raise_error;
592 --
593 END IF;
594 --
595 END IF;-- check for absence type
596 hr_utility.set_location('Leaving entilement update user hook', 22);
597 --
598 END CHECK_ADD_ABS_ENT_UPDATE;
599
600
601 PROCEDURE CHK_TRG_CATG_HRS(
602 p_abs_information_category IN varchar2
603 ,p_abs_information1 IN Varchar2
604 -- added for bug#4104220
605 ,p_abs_information5 IN Varchar2
606 ,p_abs_information6 IN Varchar2
607 ,p_abs_information7 IN Varchar2
608 ,p_abs_information8 IN Varchar2
609 ,p_abs_information9 IN Varchar2
610 ,p_abs_information10 IN Varchar2
611 ,p_abs_information11 IN Varchar2
612 ,p_abs_information12 IN Varchar2
613 ,p_abs_information13 IN Varchar2
614 ,p_abs_information14 IN Varchar2
615 ,p_abs_information15 IN Varchar2
616 ,p_abs_information16 IN Varchar2
617 ,p_abs_information18 IN Varchar2
618 ,p_abs_information19 IN Varchar2
619 --
620 ,p_abs_information20 IN Varchar2
621 -- Added for bug 5218081
622 ,p_abs_information21 IN Varchar2
623 ,p_abs_information22 IN Varchar2
624 -- added for validating leave category
625 ,p_date_start IN Date)
626 IS
627 --
628 l_value_end_date date;
629 l_proc varchar2(200) := g_package ||'chk_trg_catg_hrs';
630 --
631 Cursor csr_lookup_end is
632 Select end_date_active
633 from hr_lookups
634 where lookup_type = 'FR_TRAINING_LEAVE_CATEGORY'
635 and lookup_code = 'TRAINING_CREDIT';
636 --
637 BEGIN
638 --
639 /* Added for GSI Bug 5472781 */
640 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'FR') THEN
641 hr_utility.set_location('Leaving : '|| l_proc , 10);
642 return;
643 END IF;
644 --
645 IF p_abs_information_category = 'FR_TRAINING_ABSENCE' THEN
646 IF p_abs_information1 NOT IN ('OTHER', 'PP')
650 hr_utility.raise_error;
647 AND p_abs_information20 IS NOT NULL THEN
648 -- raise error
649 hr_utility.set_message(800,'PER_75093_TRG_CATG_OUT_HRS');
651 END IF;
652 -- added for bug#4104220
653 IF p_abs_information18 = 'Y' THEN
654 IF p_abs_information19 IS NOT NULL
655 OR (p_abs_information20 IS NOT NULL
656 AND fnd_number.canonical_to_number(p_abs_information20)<>0)
657 OR p_abs_information5 IS NOT NULL
658 OR p_abs_information6 IS NOT NULL
659 OR p_abs_information7 IS NOT NULL
660 OR p_abs_information8 IS NOT NULL
661 OR (p_abs_information9 IS NOT NULL
662 AND fnd_number.canonical_to_number(p_abs_information9)<>0)
663 OR (p_abs_information10 IS NOT NULL
664 AND fnd_number.canonical_to_number(p_abs_information10)<>0)
665 OR (p_abs_information11 IS NOT NULL
666 AND fnd_number.canonical_to_number(p_abs_information11)<>0)
667 OR (p_abs_information12 IS NOT NULL
668 AND fnd_number.canonical_to_number(p_abs_information12)<>0)
669 OR (p_abs_information13 IS NOT NULL
670 AND fnd_number.canonical_to_number(p_abs_information13)<>0)
671 OR (p_abs_information14 IS NOT NULL
672 AND fnd_number.canonical_to_number(p_abs_information14)<>0)
673 OR (p_abs_information15 IS NOT NULL
674 AND fnd_number.canonical_to_number(p_abs_information15)<>0)
675 OR (p_abs_information16 IS NOT NULL
676 AND fnd_number.canonical_to_number(p_abs_information16)<>0)
677 OR (p_abs_information21 IS NOT NULL
678 AND fnd_number.canonical_to_number(p_abs_information21)<>0)
679 OR (p_abs_information22 IS NOT NULL
680 AND fnd_number.canonical_to_number(p_abs_information22)<>0)
681 THEN
682 -- raise an error
683 hr_utility.set_message(800,'PER_75097_DIF_TRG_PLAN');
684 hr_utility.raise_error;
685 END IF;
686 END IF;
687 -- added for validating leave category
688 IF p_abs_information1 = 'TRAINING_CREDIT' THEN
689 OPEN csr_lookup_end;
690 FETCH csr_lookup_end INTO l_value_end_date;
691 CLOSE csr_lookup_end;
692 --
693 IF p_date_start is not null
694 AND p_date_start > l_value_end_date THEN
695 -- raise error
696 hr_utility.set_message(800,'PER_75098_TRG_CATG_END_DATE');
697 hr_utility.raise_error;
698 --
699 END IF;
700 END IF;
701 END IF;
702 end;
703
704 --
705 end PAY_FR_SICKNESS_ABSENCE;
[Home] [Help]