1 PACKAGE BODY per_es_absence AS
2 /* $Header: peesabsp.pkb 120.1.12000000.3 2007/06/21 15:23:26 rbaker ship $ */
3 --
4 -------------------------------------------------------------------------------
5 -- PERSON_ENTRY_CREATE
6 -------------------------------------------------------------------------------
7 PROCEDURE person_entry_create(p_business_group_id IN NUMBER
8 ,p_absence_attendance_id IN NUMBER
9 ,p_date_start IN DATE
10 ,p_date_end IN DATE
11 ,p_abs_information_category IN VARCHAR2
12 ,p_abs_information1 IN VARCHAR2
13 ,p_abs_information2 IN VARCHAR2
14 ,p_abs_information3 IN VARCHAR2
15 ,p_abs_information4 IN VARCHAR2
16 ,p_abs_information5 IN VARCHAR2
17 ,p_abs_information6 IN VARCHAR2
18 ,p_abs_information7 IN VARCHAR2
19 ,p_abs_information8 IN VARCHAR2
20 ,p_abs_information9 IN VARCHAR2
21 ,p_abs_information10 IN VARCHAR2) IS
22 --
23 CURSOR csr_get_input_value_info(p_element_entry_id NUMBER
24 ,p_input_value_name VARCHAR2
25 ,p_date DATE) IS
26 SELECT pivf.input_value_id iv_start_date_id
27 FROM pay_input_values_f pivf
28 ,pay_element_entries_f peef
29 ,pay_element_types_f petf
30 WHERE peef.element_entry_id = p_element_entry_id
31 AND peef.element_type_id = petf.element_type_id
32 AND pivf.element_type_id = petf.element_type_id
33 AND pivf.name = p_input_value_name
34 AND p_date BETWEEN peef.effective_start_date
35 AND peef.effective_end_date
36 AND p_date BETWEEN petf.effective_start_date
37 AND petf.effective_end_date
38 AND p_date BETWEEN pivf.effective_start_date
39 AND pivf.effective_end_date;
40 --
41 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
42 l_start_date_iv_id pay_input_values_f.input_value_id%TYPE;
43 l_end_date_iv_id pay_input_values_f.input_value_id%TYPE;
44 l_absence_id pay_input_values_f.input_value_id%TYPE;
45 l_ptm_percentage_iv_id pay_input_values_f.input_value_id%TYPE;
46 l_effective_start_date pay_element_entries_f.effective_start_date%TYPE;
47 l_effective_end_date pay_element_entries_f.effective_end_date%TYPE;
48 l_ovn pay_element_entries_f.object_version_number%TYPE;
49 l_o_start_dt pay_element_entries_f.effective_start_date%TYPE;
50 l_o_end_dt pay_element_entries_f.effective_end_date%TYPE;
51 l_o_warning BOOLEAN;
52 --
53
54 BEGIN
55 --
56 -- Added for GSI Bug 5472781
57 --
58 IF hr_utility.chk_product_install('Oracle Human Resources', 'ES') THEN
59 --
60 --
61 hr_utility.set_location('Entering hook Person_entry_Create',10);
62 --
63 IF p_date_start IS NOT NULL THEN
64 --
65 IF p_abs_information_category IN ('ES_TD','ES_M','ES_IE_AL','ES_PAR','ES_PTM') THEN
66 --
67
68 hr_utility.set_location(' IN Create User hook ',20);
69 --
70 hr_person_absence_api.get_absence_element
71 (p_absence_attendance_id => p_absence_attendance_id
72 ,p_element_entry_id => l_element_entry_id
73 ,p_effective_start_date => l_effective_start_date
74 ,p_effective_end_date => l_effective_end_date);
75 --
76 IF l_element_entry_id IS NOT NULL THEN
77 --
78 hr_utility.set_location(' Updating dates for entry_id='||l_element_entry_id,30);
79 hr_utility.set_location('-- Date='|| to_char(l_effective_end_date,'dd-mm-yyyy'),35);
80 IF p_date_end = to_date('31-12-4712','DD-MM-YYYY') OR
81 p_date_end IS NULL THEN
82 l_effective_end_date := NULL;
83 END IF;
84 --
85 hr_utility.set_location('-- Date='|| to_char(l_effective_end_date,'dd-mm-yyyy'),35);
86
87 OPEN csr_get_input_value_info(l_element_entry_id
88 ,'Start Date'
89 ,l_effective_start_date);
90 FETCH csr_get_input_value_info INTO l_start_date_iv_id;
91 CLOSE csr_get_input_value_info;
92 --
93 OPEN csr_get_input_value_info(l_element_entry_id
94 ,'End Date'
95 ,l_effective_start_date);
96 FETCH csr_get_input_value_info INTO l_end_date_iv_id;
97 CLOSE csr_get_input_value_info;
98 --
99 OPEN csr_get_input_value_info(l_element_entry_id
100 ,'Absence ID'
101 ,l_effective_start_date);
102 FETCH csr_get_input_value_info INTO l_absence_id;
103 CLOSE csr_get_input_value_info;
104 --
105 IF p_abs_information_category = 'ES_PTM' THEN
106 OPEN csr_get_input_value_info(l_element_entry_id
107 ,'Part Time Percentage'
108 ,l_effective_start_date);
109 FETCH csr_get_input_value_info INTO l_ptm_percentage_iv_id;
110 CLOSE csr_get_input_value_info;
111 END IF;
112 --
113 SELECT max(object_version_number) INTO l_ovn
114 FROM pay_element_entries_f
115 WHERE element_entry_id = l_element_entry_id;
116 --
117 hr_utility.set_location('~~ Before updating ',30);
118 hr_utility.set_location('~~ Absence Att ID ' || to_char(p_absence_attendance_id),10);
119 IF p_abs_information_category = 'ES_PTM' THEN
120 --
121 pay_element_entry_api.update_element_entry
122 (p_validate => FALSE
123 ,p_datetrack_update_mode => 'CORRECTION'
124 ,p_effective_date => l_effective_start_date
125 ,p_business_group_id => p_business_group_id
126 ,p_element_entry_id => l_element_entry_id
127 ,p_object_version_number => l_ovn
128 ,p_input_value_id1 => l_start_date_iv_id
129 ,p_entry_value1 => fnd_date.date_to_displaydate(l_effective_start_date) --l_effective_start_date
130 ,p_input_value_id2 => l_end_date_iv_id
131 ,p_entry_value2 => fnd_date.date_to_displaydate(l_effective_end_date) --l_effective_end_date
132 ,p_input_value_id3 => l_absence_id
133 ,p_entry_value3 => p_absence_attendance_id
134 ,p_input_value_id4 => l_ptm_percentage_iv_id
135 ,p_entry_value4 => p_abs_information3
136 ,p_effective_start_date => l_o_start_dt
137 ,p_effective_end_date => l_o_end_dt
138 ,p_update_warning => l_o_warning);
139
140 ELSE
141 pay_element_entry_api.update_element_entry
142 (p_validate => FALSE
143 ,p_datetrack_update_mode => 'CORRECTION'
144 ,p_effective_date => l_effective_start_date
145 ,p_business_group_id => p_business_group_id
146 ,p_element_entry_id => l_element_entry_id
147 ,p_object_version_number => l_ovn
148 ,p_input_value_id1 => l_start_date_iv_id
149 ,p_entry_value1 => fnd_date.date_to_displaydate(l_effective_start_date) --l_effective_start_date
150 ,p_input_value_id2 => l_end_date_iv_id
151 ,p_entry_value2 => fnd_date.date_to_displaydate(l_effective_end_date) --l_effective_end_date
152 ,p_input_value_id3 => l_absence_id
153 ,p_entry_value3 => p_absence_attendance_id
154 ,p_effective_start_date => l_o_start_dt
155 ,p_effective_end_date => l_o_end_dt
156 ,p_update_warning => l_o_warning);
157 END IF;
158 hr_utility.set_location('~~ After updating ',30);
159 END IF;
160 END IF;
161 END IF;
162 --
163 END IF;
164 --
165 hr_utility.set_location('Leaving hook Person_entry_Create',90);
166 --
167 END person_entry_create;
168 --
169 -------------------------------------------------------------------------------
170 -- PERSON_ENTRY_UPDATE
171 -------------------------------------------------------------------------------
172 PROCEDURE person_entry_update(p_absence_attendance_id IN NUMBER
173 ,p_date_start IN DATE
174 ,p_date_end IN DATE
175 ,p_abs_information_category IN VARCHAR2
176 ,p_abs_information1 IN VARCHAR2
177 ,p_abs_information2 IN VARCHAR2
178 ,p_abs_information3 IN VARCHAR2
179 ,p_abs_information4 IN VARCHAR2
180 ,p_abs_information5 IN VARCHAR2
181 ,p_abs_information6 IN VARCHAR2
182 ,p_abs_information7 IN VARCHAR2
183 ,p_abs_information8 IN VARCHAR2
184 ,p_abs_information9 IN VARCHAR2
185 ,p_abs_information10 IN VARCHAR2) IS
186
187
188 --
189 CURSOR csr_get_input_value_info(p_element_entry_id NUMBER
190 ,p_input_value_name VARCHAR2
191 ,p_date DATE) IS
192 SELECT pivf.input_value_id iv_start_date_id
193 FROM pay_input_values_f pivf
194 ,pay_element_entries_f peef
195 ,pay_element_types_f petf
196 WHERE peef.element_entry_id = p_element_entry_id
197 AND peef.element_type_id = petf.element_type_id
198 AND pivf.element_type_id = petf.element_type_id
199 AND pivf.name = p_input_value_name
200 AND p_date BETWEEN peef.effective_start_date
201 AND peef.effective_end_date
202 AND p_date BETWEEN petf.effective_start_date
203 AND petf.effective_end_date
204 AND p_date BETWEEN pivf.effective_start_date
205 AND pivf.effective_end_date;
206 --
207 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
208 l_start_date_iv_id pay_input_values_f.input_value_id%TYPE;
209 l_end_date_iv_id pay_input_values_f.input_value_id%TYPE;
210 l_absence_type_iv_id pay_input_values_f.input_value_id%TYPE;
211 l_ptm_percentage_iv_id pay_input_values_f.input_value_id%TYPE;
212 l_effective_start_date pay_element_entries_f.effective_start_date%TYPE;
213 l_effective_end_date pay_element_entries_f.effective_end_date%TYPE;
214 l_ovn pay_element_entries_f.object_version_number%TYPE;
215 l_o_start_dt pay_element_entries_f.effective_start_date%TYPE;
216 l_o_end_dt pay_element_entries_f.effective_end_date%TYPE;
217 l_o_warning BOOLEAN;
218 l_bus_grp_id pay_input_values_f.business_group_id%TYPE;
219
220 --
221 BEGIN
222 --
223 -- Added for GSI Bug 5472781
224 --
225 IF hr_utility.chk_product_install('Oracle Human Resources', 'ES') THEN
226 --
227 --
228 hr_utility.set_location('Entering hook Person_entry_Create',10);
229 --
230 IF p_date_start IS NOT NULL THEN
231 --
232 IF p_abs_information_category IN ('ES_TD','ES_M','ES_IE_AL','ES_PAR','ES_PTM') THEN
233 --
234 hr_utility.set_location(' IN Update User hook ',20);
235 --
236 hr_person_absence_api.get_absence_element
237 (p_absence_attendance_id => p_absence_attendance_id
238 ,p_element_entry_id => l_element_entry_id
239 ,p_effective_start_date => l_effective_start_date
240 ,p_effective_end_date => l_effective_end_date);
241 --
242 IF l_element_entry_id IS NOT NULL THEN
243
244 hr_utility.set_location(' Updating dates for entry_id='||l_element_entry_id,30);
245 --
246 IF p_date_end = to_date('31-12-4712','DD-MM-YYYY') OR
247 p_date_end IS NULL THEN
248 l_effective_end_date := NULL;
249 END IF;
250 --
251 OPEN csr_get_input_value_info(l_element_entry_id
252 ,'Start Date'
253 ,l_effective_start_date);
254 FETCH csr_get_input_value_info INTO l_start_date_iv_id;
255 CLOSE csr_get_input_value_info;
256 --
257 OPEN csr_get_input_value_info(l_element_entry_id
258 ,'End Date'
259 ,l_effective_start_date);
260 FETCH csr_get_input_value_info INTO l_end_date_iv_id;
261 CLOSE csr_get_input_value_info;
262 --
263 OPEN csr_get_input_value_info(l_element_entry_id
264 ,'Absence ID'
265 ,l_effective_start_date);
266 FETCH csr_get_input_value_info INTO l_absence_type_iv_id;
267 CLOSE csr_get_input_value_info;
268 --
269 IF p_abs_information_category = 'ES_PTM' THEN
270 OPEN csr_get_input_value_info(l_element_entry_id
271 ,'Part Time Percentage'
272 ,l_effective_start_date);
273 FETCH csr_get_input_value_info INTO l_ptm_percentage_iv_id;
274 CLOSE csr_get_input_value_info;
275 END IF;
276 --
277 SELECT max(object_version_number) INTO l_ovn
278 FROM pay_element_entries_f
279 WHERE element_entry_id = l_element_entry_id;
280 --
281 SELECT business_group_id INTO l_bus_grp_id
282 FROM per_absence_attendances
283 WHERE absence_attendance_id = p_absence_attendance_id;
284 --
285 hr_utility.set_location('~~ Before updating ',30);
286 hr_utility.set_location('~~ Absence Att ID ' || to_char(p_absence_attendance_id),10);
287 --
288 IF p_abs_information_category = 'ES_PTM' THEN
289 --
290 pay_element_entry_api.update_element_entry
291 (p_validate => FALSE
292 ,p_datetrack_update_mode => 'CORRECTION'
293 ,p_effective_date => l_effective_start_date
294 ,p_business_group_id => l_bus_grp_id
295 ,p_element_entry_id => l_element_entry_id
296 ,p_object_version_number => l_ovn
297 ,p_input_value_id1 => l_start_date_iv_id
298 ,p_entry_value1 => fnd_date.date_to_displaydate(l_effective_start_date) --l_effective_start_date
299 ,p_input_value_id2 => l_end_date_iv_id
300 ,p_entry_value2 => fnd_date.date_to_displaydate(l_effective_end_date) --l_effective_end_date
301 ,p_input_value_id3 => l_absence_type_iv_id
302 ,p_entry_value3 => p_absence_attendance_id
303 ,p_input_value_id4 => l_ptm_percentage_iv_id
304 ,p_entry_value4 => p_abs_information3
305 ,p_effective_start_date => l_o_start_dt
306 ,p_effective_end_date => l_o_end_dt
307 ,p_update_warning => l_o_warning);
308 ELSE
309 pay_element_entry_api.update_element_entry
310 (p_validate => FALSE
311 ,p_datetrack_update_mode => 'CORRECTION'
312 ,p_effective_date => l_effective_start_date
313 ,p_business_group_id => l_bus_grp_id
314 ,p_element_entry_id => l_element_entry_id
315 ,p_object_version_number => l_ovn
316 ,p_input_value_id1 => l_start_date_iv_id
317 ,p_entry_value1 => fnd_date.date_to_displaydate(l_effective_start_date) --l_effective_start_date
318 ,p_input_value_id2 => l_end_date_iv_id
319 ,p_entry_value2 => fnd_date.date_to_displaydate(l_effective_end_date) --l_effective_end_date
320 ,p_input_value_id3 => l_absence_type_iv_id
321 ,p_entry_value3 => p_absence_attendance_id
322 ,p_effective_start_date => l_o_start_dt
323 ,p_effective_end_date => l_o_end_dt
324 ,p_update_warning => l_o_warning);
325 END IF;
326 --
327 hr_utility.set_location('~~ After updating ',30);
328 END IF;
329 END IF;
330 END IF;
331 --
332 END IF;
333 --
334 hr_utility.set_location('Leaving hook Person_entry_Update',90);
335 --
336 END person_entry_update;
337 -------------------------------------------------------------------------------
338 -- VALIDATE_ABS_CREATE
339 -------------------------------------------------------------------------------
340 PROCEDURE validate_abs_create(p_business_group_id IN NUMBER
341 ,p_person_id IN NUMBER
342 ,p_absence_attendance_type_id IN NUMBER
343 ,p_date_start IN DATE
344 ,p_time_start IN VARCHAR2
345 ,p_date_end IN DATE
346 ,p_time_end IN VARCHAR2
347 ,p_abs_information_category IN VARCHAR2
348 ,p_abs_information1 IN VARCHAR2
349 ,p_abs_information2 IN VARCHAR2
350 ,p_abs_information3 IN VARCHAR2
351 ,p_abs_information4 IN VARCHAR2
352 ,p_abs_information5 IN VARCHAR2
353 ,p_abs_information6 IN VARCHAR2
354 ,p_abs_information7 IN VARCHAR2
355 ,p_abs_information8 IN VARCHAR2
356 ,p_abs_information9 IN VARCHAR2
357 ,p_abs_information10 IN VARCHAR2) IS
358 --
359 CURSOR csr_get_absence_category(p_absence_attendance_type_id NUMBER) IS
360 SELECT absence_category
361 FROM per_absence_attendance_types
362 WHERE absence_attendance_type_id = p_absence_attendance_type_id;
363 --
364 CURSOR csr_get_other_absences(p_person_id NUMBER
365 ,p_date_start DATE
366 ,p_date_end DATE) IS
367 SELECT PAAT.absence_category
368 FROM per_absence_attendances PAA
369 ,per_absence_attendance_types PAAT
370 WHERE PAA.person_id = p_person_id
371 AND PAAT.absence_attendance_type_id = PAA.absence_attendance_type_id
372 AND (( p_date_start BETWEEN PAA.date_start
373 AND NVL(PAA.date_end,to_date('31-12-4712','DD-MM-YYYY')))
374 OR (PAA.date_start BETWEEN p_date_start
375 AND NVL(p_date_end,to_date('31-12-4712','DD-MM-YYYY'))));
376 --
377 CURSOR csr_validate_ptm(p_person_id NUMBER
378 ,p_date_start DATE) IS
379 SELECT nvl(to_number(max(PAA.date_end) - max(PAA.date_start)+1),0)
380 FROM per_absence_attendances PAA
381 ,per_absence_attendance_types PAAT
382 WHERE PAA.person_id = p_person_id
383 AND PAAT.absence_attendance_type_id = PAA.absence_attendance_type_id
384 AND PAAT.absence_category = 'M'
385 AND p_date_start = PAA.date_end + 1 ;
386 -- AND p_date_start > PAA.date_end;
387 --
388
389 CURSOR csr_validate_sex_par(p_person_id NUMBER) IS
390 SELECT ppf.sex
391 FROM per_people_f ppf
392 WHERE ppf.person_id = p_person_id ;
393
394 l_sex per_people_f.sex%TYPE;
395
396 l_maternity_benefit_days NUMBER;
397 l_absence_category per_absence_attendance_types.absence_category%TYPE;
398 --
399 BEGIN
400 --
401 -- Added for GSI Bug 5472781
402 --
403 IF hr_utility.chk_product_install('Oracle Human Resources', 'ES') THEN
404 --
405 --
406 -- get the values of the person_id profile
407 fnd_profile.put('HR_FR_PERSON_ID',p_person_id);
408 -- get the value of the absence_start_date
409 fnd_profile.put('HR_FR_ABSENCE_START_DATE',fnd_date.date_to_canonical(p_date_start));
410 --
411 --IF p_date_start IS NOT NULL THEN
412 --
413 OPEN csr_get_absence_category(p_absence_attendance_type_id);
414 FETCH csr_get_absence_category INTO l_absence_category ;
415 CLOSE csr_get_absence_category;
416 --
417 IF l_absence_category IN ('TD') THEN
418 FOR i IN csr_get_other_absences(p_person_id
419 ,p_date_start
420 ,p_date_end) LOOP
421 IF i.absence_category IN ('TD') THEN
422 hr_utility.set_message(800,'HR_ES_TD_OVERLAP');
423 hr_utility.raise_error;
424 END IF;
425 IF i.absence_category IN ('V') THEN
426 hr_utility.set_message(800,'HR_ES_V_OVERLAP');
427 hr_utility.raise_error;
428 END IF;
429 IF i.absence_category IN ('ZZB') THEN
430 hr_utility.set_message(800,'HR_ES_STRIKE_OVERLAP');
431 hr_utility.raise_error;
432 END IF;
433 END LOOP;
434 -- BU Gross Pay Daily Rate Formula validation
435 IF (p_abs_information3 = 'GROSS_PAY' ) AND (p_abs_information4 IS NULL) THEN
436 hr_utility.set_message(800, 'HR_ES_BU_RATE_FORMULA_MISSING');
437 hr_utility.raise_error;
438 END IF;
439 --
440 END IF;
441 --
442 IF l_absence_category IN ('V') THEN
443 FOR i IN csr_get_other_absences(p_person_id
444 ,p_date_start
445 ,p_date_end) LOOP
446 IF i.absence_category IN ('TD') THEN
447 hr_utility.set_message(800,'HR_ES_TD_OVERLAP');
448 hr_utility.raise_error;
449 END IF;
450 IF i.absence_category IN ('V') THEN
451 hr_utility.set_message(800,'HR_ES_V_OVERLAP');
452 hr_utility.raise_error;
453 END IF;
454 END LOOP;
455 END IF;
456 --
457 IF l_absence_category IN ('ZZB') THEN
458 FOR i IN csr_get_other_absences(p_person_id
459 ,p_date_start
460 ,p_date_end) LOOP
461 IF i.absence_category IN ('TD') THEN
462 hr_utility.set_message(800,'HR_ES_TD_OVERLAP');
463 hr_utility.raise_error;
464 END IF;
465 IF i.absence_category IN ('ZZB') THEN
466 hr_utility.set_message(800,'HR_ES_STRIKE_OVERLAP');
467 hr_utility.raise_error;
468 END IF;
469 END LOOP;
470 END IF;
471 --
472 IF l_absence_category IN ('M') THEN
473 FOR i IN csr_get_other_absences(p_person_id
474 ,p_date_start
475 ,p_date_end) LOOP
476 IF i.absence_category IN ('M') THEN
477 hr_utility.set_message(800,'HR_ES_M_OVERLAP');
478 hr_utility.raise_error;
479 END IF;
480 IF i.absence_category IN ('PAR') THEN
481 hr_utility.set_message(800,'HR_ES_PAR_OVERLAP');
482 hr_utility.raise_error;
483 END IF;
484 END LOOP;
485 -- BU Gross Pay Daily Rate Formula validation
486 IF (p_abs_information5 = 'GROSS_PAY' ) AND (p_abs_information6 IS NULL) THEN
487 hr_utility.set_message(800, 'HR_ES_BU_RATE_FORMULA_MISSING');
488 hr_utility.raise_error;
489 END IF;
490 --
491 END IF;
492 --
493 IF l_absence_category IN ('PAR') THEN
494
495 OPEN csr_validate_sex_par (p_person_id);
496 FETCH csr_validate_sex_par INTO l_sex ;
497 CLOSE csr_validate_sex_par;
498 IF l_sex = 'M' THEN
499 hr_utility.set_message(800,'HR_ES_PAR_CHK_SEX');
500 hr_utility.raise_error;
501 END IF;
502
503 FOR i IN csr_get_other_absences(p_person_id
504 ,p_date_start
505 ,p_date_end) LOOP
506 IF i.absence_category IN ('PAR') THEN
507 hr_utility.set_message(800,'HR_ES_PAR_OVERLAP');
508 hr_utility.raise_error;
509 END IF;
510 END LOOP;
511 -- BU Gross Pay Daily Rate Formula validation
512 IF (p_abs_information2= 'GROSS_PAY' ) AND (p_abs_information3 IS NULL) THEN
513 hr_utility.set_message(800, 'HR_ES_BU_RATE_FORMULA_MISSING');
514 hr_utility.raise_error;
515 END IF;
516 --
517 END IF;
518 --
519 IF l_absence_category IN ('IE_AL') THEN
520 FOR i IN csr_get_other_absences(p_person_id
521 ,p_date_start
522 ,p_date_end) LOOP
523 IF i.absence_category IN ('IE_AL') THEN
524 hr_utility.set_message(800,'HR_ES_ADOPTION_OVERLAP');
525 hr_utility.raise_error;
526 END IF;
527 END LOOP;
528 -- BU Gross Pay Daily Rate Formula validation
529 IF (p_abs_information2 = 'GROSS_PAY' ) AND (p_abs_information3 IS NULL) THEN
530 hr_utility.set_message(800, 'HR_ES_BU_RATE_FORMULA_MISSING');
531 hr_utility.raise_error;
532 END IF;
533 --
534 END IF;
535 --
536 IF l_absence_category IN ('PTM') THEN
537 FOR i IN csr_get_other_absences(p_person_id
538 ,p_date_start
539 ,p_date_end) LOOP
540 IF i.absence_category IN ('M') THEN
541 hr_utility.set_message(800,'HR_ES_M_OVERLAP');
542 hr_utility.raise_error;
543 END IF;
544 IF i.absence_category IN ('PTM') THEN
545 hr_utility.set_message(800,'HR_ES_PTM_OVERLAP');
546 hr_utility.raise_error;
547 END IF;
548 END LOOP;
549 --
550 OPEN csr_validate_ptm (p_person_id
551 ,p_date_start);
552 FETCH csr_validate_ptm INTO l_maternity_benefit_days ;
553 CLOSE csr_validate_ptm;
554 --
555 IF l_maternity_benefit_days = 0 THEN
556 hr_utility.set_message(800,'HR_ES_M_NOT_FOUND');
557 hr_utility.raise_error;
558 END IF;
559 --
560 IF l_maternity_benefit_days < 42 THEN
561 hr_utility.set_message(800,'HR_ES_PTM_CANT_COMMENCE');
562 hr_utility.raise_error;
563 END IF;
564 --
565 -- BU Gross Pay Daily Rate Formula validation
566 IF (p_abs_information4 = 'GROSS_PAY' ) AND (p_abs_information5 IS NULL) THEN
567 hr_utility.set_message(800, 'HR_ES_BU_RATE_FORMULA_MISSING');
568 hr_utility.raise_error;
569 END IF;
570 --
571 END IF;
572 --
573 END IF;
574 --
575 END validate_abs_create;
576 --
577 -------------------------------------------------------------------------------
578 -- VALIDATE_ABS_UPDATE
579 -------------------------------------------------------------------------------
580 PROCEDURE validate_abs_update(p_absence_attendance_id IN NUMBER
581 ,p_date_start IN DATE
582 ,p_time_start IN VARCHAR2
583 ,p_date_end IN DATE
584 ,p_time_end IN VARCHAR2
585 ,p_abs_information_category IN VARCHAR2
586 ,p_abs_information1 IN VARCHAR2
587 ,p_abs_information2 IN VARCHAR2
588 ,p_abs_information3 IN VARCHAR2
589 ,p_abs_information4 IN VARCHAR2
590 ,p_abs_information5 IN VARCHAR2
591 ,p_abs_information6 IN VARCHAR2
592 ,p_abs_information7 IN VARCHAR2
593 ,p_abs_information8 IN VARCHAR2
594 ,p_abs_information9 IN VARCHAR2
595 ,p_abs_information10 IN VARCHAR2) IS
596 --
597 CURSOR csr_get_absence_category(p_absence_attendance_id NUMBER) IS
598 SELECT paat.absence_category
599 FROM per_absence_attendance_types paat
600 ,per_absence_attendances paa
601 WHERE paa.absence_attendance_id = p_absence_attendance_id
602 AND paat.absence_attendance_type_id = paa.absence_attendance_type_id;
603 --
604 CURSOR csr_get_other_absences(p_absence_attendance_id NUMBER
605 ,p_date_start DATE
606 ,p_date_end DATE) IS
607 SELECT PAAT.absence_category
608 FROM per_absence_attendances PAA1
609 ,per_absence_attendances PAA2
610 ,per_absence_attendance_types PAAT
611 WHERE PAA1.absence_attendance_id = p_absence_attendance_id
612 AND PAA2.person_id = PAA1.person_id
613 AND PAAT.absence_attendance_type_id = PAA2.absence_attendance_type_id
614 AND PAA1.absence_attendance_id <> PAA2.absence_attendance_id
615 AND (( p_date_start BETWEEN PAA2.date_start
616 AND NVL(PAA2.date_end,to_date('31-12-4712','DD-MM-YYYY')))
617 OR (PAA2.date_start BETWEEN p_date_start
618 AND NVL(p_date_end,to_date('31-12-4712','DD-MM-YYYY'))));
619 --
620 CURSOR csr_validate_ptm(p_absence_attendance_id NUMBER
621 ,p_date_start DATE) IS
622 SELECT nvl(to_number(max(PAA2.date_end) - max(PAA2.date_start)+1),0)
623 FROM per_absence_attendances PAA1
624 ,per_absence_attendances PAA2
625 ,per_absence_attendance_types PAAT
626 WHERE PAA1.absence_attendance_id = p_absence_attendance_id
627 AND PAA2.person_id = PAA1.person_id
628 AND PAAT.absence_attendance_type_id = PAA2.absence_attendance_type_id
629 AND PAA1.absence_attendance_id <> PAA2.absence_attendance_id
630 AND PAAT.absence_category = 'M'
631 AND p_date_start = PAA2.date_end + 1;
632 -- AND p_date_start > PAA2.date_end;
633 --
634 CURSOR get_person_id(p_absence_attendance_id in number) is
635 SELECT person_id
636 FROM per_absence_attendances
637 WHERE absence_attendance_id =p_absence_attendance_id;
638 --
639
640 CURSOR csr_validate_sex_par(p_person_id NUMBER) IS
641 SELECT ppf.sex
642 FROM per_people_f ppf
643 WHERE ppf.person_id = p_person_id ;
644
645 l_sex per_people_f.sex%TYPE;
646
647 l_maternity_benefit_days NUMBER;
648 l_absence_category per_absence_attendance_types.absence_category%TYPE;
649 l_person_id per_absence_attendances.person_id%TYPE;
650 --
651 BEGIN
652 --
653 -- Added for GSI Bug 5472781
654 --
655 IF hr_utility.chk_product_install('Oracle Human Resources', 'ES') THEN
656 --
657 --
658 OPEN get_person_id(p_absence_attendance_id);
659 FETCH get_person_id into l_person_id;
660 CLOSE get_person_id;
661
662 -- get the values of the person_id profile
663 fnd_profile.put('HR_FR_PERSON_ID',l_person_id);
664 -- get the value of the absence_start_date
665 fnd_profile.put('HR_FR_ABSENCE_START_DATE',fnd_date.date_to_canonical(p_date_start));
666 --
667 --IF p_date_start IS NOT NULL THEN
668 --
669 OPEN csr_get_absence_category(p_absence_attendance_id);
670 FETCH csr_get_absence_category INTO l_absence_category;
671 CLOSE csr_get_absence_category;
672 --
673 IF l_absence_category IN ('TD') THEN
674 FOR i IN csr_get_other_absences(p_absence_attendance_id
675 ,p_date_start
676 ,p_date_end) LOOP
677 IF i.absence_category IN ('TD') THEN
678 hr_utility.set_message(800,'HR_ES_TD_OVERLAP');
679 hr_utility.raise_error;
680 END IF;
681 IF i.absence_category IN ('V') THEN
682 hr_utility.set_message(800,'HR_ES_V_OVERLAP');
683 hr_utility.raise_error;
684 END IF;
685 IF i.absence_category IN ('ZZB') THEN
686 hr_utility.set_message(800,'HR_ES_STRIKE_OVERLAP');
687 hr_utility.raise_error;
688 END IF;
689 END LOOP;
690 -- BU Gross Pay Daily Rate Formula validation
691 IF (p_abs_information3 = 'GROSS_PAY' ) AND (p_abs_information4 IS NULL) THEN
692 hr_utility.set_message(800, 'HR_ES_BU_RATE_FORMULA_MISSING');
693 hr_utility.raise_error;
694 END IF;
695 --
696 END IF;
697 --
698 IF l_absence_category IN ('V') THEN
699 FOR i IN csr_get_other_absences(p_absence_attendance_id
700 ,p_date_start
701 ,p_date_end) LOOP
702 IF i.absence_category IN ('V') THEN
703 hr_utility.set_message(800,'HR_ES_V_OVERLAP');
704 hr_utility.raise_error;
705 END IF;
706 IF i.absence_category IN ('TD') THEN
707 hr_utility.set_message(800,'HR_ES_TD_OVERLAP');
708 hr_utility.raise_error;
709 END IF;
710 END LOOP;
711 END IF;
712 --
713 IF l_absence_category IN ('ZZB') THEN
714 FOR i IN csr_get_other_absences(p_absence_attendance_id
715 ,p_date_start
716 ,p_date_end) LOOP
717 IF i.absence_category IN ('TD') THEN
718 hr_utility.set_message(800,'HR_ES_TD_OVERLAP');
719 hr_utility.raise_error;
720 END IF;
721 IF i.absence_category IN ('ZZB') THEN
722 hr_utility.set_message(800,'HR_ES_STRIKE_OVERLAP');
723 hr_utility.raise_error;
724 END IF;
725 END LOOP;
726 END IF;
727 --
728 IF l_absence_category IN ('M') THEN
729 FOR i IN csr_get_other_absences(p_absence_attendance_id
730 ,p_date_start
731 ,p_date_end) LOOP
732 IF i.absence_category IN ('M') THEN
733 hr_utility.set_message(800,'HR_ES_M_OVERLAP');
734 hr_utility.raise_error;
735 END IF;
736 IF i.absence_category IN ('PAR') THEN
737 hr_utility.set_message(800,'HR_ES_PAR_OVERLAP');
738 hr_utility.raise_error;
739 END IF;
740 END LOOP;
741 -- BU Gross Pay Daily Rate Formula validation
742 IF (p_abs_information5 = 'GROSS_PAY' ) AND (p_abs_information6 IS NULL) THEN
743 hr_utility.set_message(800, 'HR_ES_BU_RATE_FORMULA_MISSING');
744 hr_utility.raise_error;
745 END IF;
746 --
747 END IF;
748 --
749 IF l_absence_category IN ('PAR') THEN
750
751 OPEN csr_validate_sex_par (l_person_id);
752 FETCH csr_validate_sex_par INTO l_sex ;
753 CLOSE csr_validate_sex_par;
754 IF l_sex = 'M' THEN
755 hr_utility.set_message(800,'HR_ES_PAR_CHK_SEX');
756 hr_utility.raise_error;
757 END IF;
758
759
760 FOR i IN csr_get_other_absences(p_absence_attendance_id
761 ,p_date_start
762 ,p_date_end) LOOP
763 IF i.absence_category IN ('PAR') THEN
764 hr_utility.set_message(800,'HR_ES_PAR_OVERLAP');
765 hr_utility.raise_error;
766 END IF;
767 END LOOP;
768 -- BU Gross Pay Daily Rate Formula validation
769 IF (p_abs_information2= 'GROSS_PAY' ) AND (p_abs_information3 IS NULL) THEN
770 hr_utility.set_message(800, 'HR_ES_BU_RATE_FORMULA_MISSING');
771 hr_utility.raise_error;
772 END IF;
773 --
774 END IF;
775 --
776 IF l_absence_category IN ('IE_AL') THEN
777 FOR i IN csr_get_other_absences(p_absence_attendance_id
778 ,p_date_start
779 ,p_date_end) LOOP
780 IF i.absence_category IN ('IE_AL') THEN
781 hr_utility.set_message(800,'HR_ES_ADOPTION_OVERLAP');
782 hr_utility.raise_error;
783 END IF;
784 END LOOP;
785 -- BU Gross Pay Daily Rate Formula validation
786 IF (p_abs_information2 = 'GROSS_PAY' ) AND (p_abs_information3 IS NULL) THEN
787 hr_utility.set_message(800, 'HR_ES_BU_RATE_FORMULA_MISSING');
788 hr_utility.raise_error;
789 END IF;
790 --
791 END IF;
792 --
793 IF l_absence_category IN ('PTM') THEN
794 FOR i IN csr_get_other_absences(p_absence_attendance_id
795 ,p_date_start
796 ,p_date_end) LOOP
797 IF i.absence_category IN ('PTM') THEN
798 hr_utility.set_message(800,'HR_ES_PTM_OVERLAP');
799 hr_utility.raise_error;
800 END IF;
801 IF i.absence_category IN ('M') THEN
802 hr_utility.set_message(800,'HR_ES_M_OVERLAP');
803 hr_utility.raise_error;
804 END IF;
805 END LOOP;
806 --
807 OPEN csr_validate_ptm (p_absence_attendance_id
808 ,p_date_start);
809 FETCH csr_validate_ptm INTO l_maternity_benefit_days ;
810 CLOSE csr_validate_ptm;
811 --
812 IF l_maternity_benefit_days = 0 THEN
813 hr_utility.set_message(800,'HR_ES_M_NOT_FOUND');
814 hr_utility.raise_error;
815 END IF;
816 --
817 IF l_maternity_benefit_days < 42 THEN
818 hr_utility.set_message(800,'HR_ES_PTM_CANT_COMMENCE');
819 hr_utility.raise_error;
820 END IF;
821 -- BU Gross Pay Daily Rate Formula validation
822 IF (p_abs_information4 = 'GROSS_PAY' ) AND (p_abs_information5 IS NULL) THEN
823 hr_utility.set_message(800, 'HR_ES_BU_RATE_FORMULA_MISSING');
824 hr_utility.raise_error;
825 END IF;
826 --
827 END IF;
828 --
829 END IF;
830 --
831 END validate_abs_update;
832 --
833 END per_es_absence;
[Home] [Help]