1 PACKAGE BODY per_gb_absence_rules AS
2 /* $Header: pegbabsr.pkb 120.8.12010000.5 2008/09/07 10:28:23 npershad ship $ */
3
4
5 -------------------------------------------------------------------------------
6 -- CHECK_ABS_OVERLAP
7 ---------------------------------------------------------------------------------
8 -- Bug 6708992
9 -- Procedure to raise error if overlapping absences of same type are present
10 PROCEDURE check_abs_overlap( p_person_id IN NUMBER
11 ,p_date_start IN DATE
12 ,p_date_end IN DATE
13 ,p_time_start IN VARCHAR2
14 ,p_time_end IN VARCHAR2
15 ,p_absence_attendance_id IN NUMBER
16 --,p_absence_attendance_type_id IN NUMBER) IS --Absence category instead of Absence type 6888892
17 ,p_absence_category IN VARCHAR2) IS
18
19 -- 6888892 Changed this cursor, so that the check is based on Absence Categories
20 cursor c_abs_overlap_another is
21 select 1
22 from per_absence_attendances abs, per_absence_attendance_types paat
23 where paat.absence_category = p_absence_category
24 and paat.absence_attendance_type_id = abs.absence_attendance_type_id
25 and abs.person_id = p_person_id
26 and (p_absence_attendance_id is null or
27 p_absence_attendance_id <> abs.absence_attendance_id)
28 and abs.date_start is not null
29 and p_date_start is not null
30 and ((
31 to_date(to_char(abs.date_start,'YYYY-MM-DD')|| ' ' ||
32 nvl(abs.time_start,'00:00'),'YYYY-MM-DD HH24:MI:SS')
33 between
34 to_date(to_char(p_date_start,'YYYY-MM-DD')|| ' ' ||
35 nvl(p_time_start,'00:00'),'YYYY-MM-DD HH24:MI:SS')
36 AND
37 to_date(to_char(nvl(p_date_end,hr_api.g_eot),'YYYY-MM-DD')|| ' ' ||
38 nvl(p_time_end,'23:59'),'YYYY-MM-DD HH24:MI:SS'))
39 OR
40 (
41 to_date(to_char(p_date_start,'YYYY-MM-DD')|| ' ' ||
42 nvl(p_time_start,'00:00'),'YYYY-MM-DD HH24:MI:SS')
43 between
44 to_date(to_char(abs.date_start,'YYYY-MM-DD')|| ' ' ||
45 nvl(abs.time_start,'00:00'),'YYYY-MM-DD HH24:MI:SS')
46 AND
47 to_date(to_char(nvl(abs.date_end,hr_api.g_eot),'YYYY-MM-DD')|| ' ' ||
48 nvl(abs.time_end,'23:59'),'YYYY-MM-DD HH24:MI:SS')
49 )
50 );
51
52 l_exists NUMBER ;
53 BEGIN
54 open c_abs_overlap_another;
55 fetch c_abs_overlap_another INTO l_exists;
56 if c_abs_overlap_another%found then
57 close c_abs_overlap_another;
58 hr_utility.set_message(804,'SSP_35084_SIMILAR_ABS_OVERLAP');
59 hr_utility.raise_error;
60 else
61 close c_abs_overlap_another;
62 end if ;
63 END check_abs_overlap;
64
65 --
66 --
67
68 PROCEDURE sickness_date_update
69 (p_absence_attendance_id IN NUMBER
70 ) IS
71 --
72 l_proc VARCHAR2(30) ;
73 --
74 CURSOR get_abs_category IS
75 SELECT paat.absence_category
76 FROM per_absence_attendance_types paat,
77 per_absence_attendances paa
78 WHERE paa.absence_attendance_id = p_absence_attendance_id
79 AND paa.absence_attendance_type_id = paat.absence_attendance_type_id;
80 --
81 l_abs_category per_absence_attendance_types.absence_category%TYPE;
82 --
83 BEGIN
84 --
85 -- Added for GSI Bug 5472781
86 --
87 IF hr_utility.chk_product_install('Oracle Human Resources', 'GB') THEN
88 --
89 l_proc := 'PER_GB_ABSENCE_RULES';
90
91 hr_utility.set_location('Entering:'|| l_proc, 10);
92 hr_utility.trace(l_proc||': Opening get_abs_category'||
93 ', p_absence_attendance_id='||p_absence_attendance_id);
94 --
95 -- Get absence_category
96 --
97 OPEN get_abs_category;
98 FETCH get_abs_category INTO l_abs_category;
99 CLOSE get_abs_category;
100 --
101 hr_utility.trace(l_proc||': Closed get_abs_category'||
102 ', l_abs_category='||l_abs_category);
103 --
104 --7157943 when this procedure is called from after delete hook l_abs_category will be NULL
105 --IF l_abs_category = 'S' THEN
106 IF nvl(l_abs_category,'S') = 'S' THEN
107 -- call recalculate_SSP_and_SMP to create/update stoppages and/or element entries.
108
109 ssp_smp_support_pkg.recalculate_SSP_and_SMP(p_deleting=>FALSE);
110
111 END IF;
112 --
113 END IF;
114 --
115 hr_utility.set_location('Leaving:'|| l_proc, 200);
116 --
117 END sickness_date_update;
118 --
119
120 -------------------------------------------------------------------------------
121 -- VALIDATE_ABS_CREATE
122 -------------------------------------------------------------------------------
123 PROCEDURE validate_abs_create(p_business_group_id IN NUMBER
124 ,p_person_id IN NUMBER
125 ,p_date_start IN DATE
126 ,p_date_end IN DATE -- Bug 6708992
127 ,p_time_start IN VARCHAR2 -- Bug 6708992
128 ,p_time_end IN VARCHAR2 -- Bug 6708992
129 ,p_absence_attendance_type_id IN NUMBER
130 ) IS
131
132 CURSOR get_abs_category IS
133 SELECT paat.absence_category
134 FROM per_absence_attendance_types paat
135 WHERE paat.absence_attendance_type_id = p_absence_attendance_type_id
136 AND paat.business_group_id = p_business_group_id;
137
138 CURSOR csr_absences
139 IS
140 SELECT 1
141 FROM per_absence_attendances PAA
142 WHERE PAA.person_id = p_person_id
143 and PAA.business_group_id = p_business_group_id
144 AND PAA.sickness_start_date is not null
145 AND p_date_start < (select max(ABS.sickness_start_date)
146 from per_absence_attendances ABS
147 where ABS.business_group_id = p_business_group_id
148 and ABS.person_id = p_person_id);
149
150 l_absence NUMBER;
151 l_abs_category per_absence_attendance_types.absence_category%TYPE;
152
153 BEGIN
154 --
155 -- Added for GSI Bug 5472781
156 --
157 IF hr_utility.chk_product_install('Oracle Human Resources', 'GB') THEN
158 --
159 -- Get absence_category
160 --
161 OPEN get_abs_category;
162 FETCH get_abs_category INTO l_abs_category;
163 CLOSE get_abs_category;
164 --
165 --
166 IF l_abs_category = 'S' THEN
167
168 OPEN csr_absences;
169 FETCH csr_absences INTO l_absence;
170 if csr_absences%found then
171 close csr_absences;
172 hr_utility.set_message(804,'SSP_35037_PIW_BROKEN');
173 hr_utility.raise_error;
174 else
175 close csr_absences;
176 end if;
177 END IF;
178
179 -- Bug 6708992
180 -- Raise error if overlapping absences of same type are present
181 -- Bug 6888892 begin
182 -- To check only in case below mentioned absence categories also
183 -- changed the check to be based on Absence Category
184 if l_abs_category in ('S','M','GB_ADO','GB_PAT_BIRTH','GB_PAT_ADO') then
185 check_abs_overlap( p_person_id, p_date_start, p_date_end
186 ,p_time_start, p_time_end, null
187 --,p_absence_attendance_type_id) ;
188 ,l_abs_category);
189 end if;
190 -- Bug 6888892 begin
191
192 END IF;
193
194 END validate_abs_create;
195
196 -------------------------------------------------------------------------------
197 -- VALIDATE_ABS_UPDATE
198 -------------------------------------------------------------------------------
199 PROCEDURE validate_abs_update(p_date_start IN DATE,
200 p_date_end IN DATE,
201 p_time_start IN VARCHAR2, -- Bug 6708992
202 p_time_end IN VARCHAR2, -- Bug 6708992
203 p_absence_attendance_id IN NUMBER) IS
204
205 cursor csr_abs_details is
206 select absence_attendance_type_id,
207 business_group_id,
208 person_id,
209 sickness_start_date,
210 sickness_end_date
211 --7287548 begin
212 ,date_start,time_start,date_end,time_end
213 --7287548 End
214 from per_absence_attendances
215 where absence_attendance_id = p_absence_attendance_id;
216
217 CURSOR get_abs_category(p_abs_type_id number,
218 p_bus_group number) IS
219 SELECT paat.absence_category
220 FROM per_absence_attendance_types paat
221 WHERE paat.absence_attendance_type_id = p_abs_type_id
222 AND paat.business_group_id = p_bus_group;
223
224 CURSOR csr_absences(p_person_id number,
225 p_business_group_id number,
226 p_start date)IS
227 SELECT 1
228 FROM per_absence_attendances PAA
229 WHERE PAA.person_id = p_person_id
230 and PAA.business_group_id = p_business_group_id
231 AND PAA.sickness_start_date is not null
232 AND PAA.sickness_start_date > p_start
233 AND PAA.absence_attendance_id <> p_absence_attendance_id;
234
235 l_absence number;
236 l_business_group_id number;
237 l_person_id number;
238 l_abs_type_id number;
239 l_param_start date;
240 l_current_start date;
241 l_current_end date;
242 l_abs_category per_absence_attendance_types.absence_category%TYPE;
243 --7287548 begin
244 l_date_start date;
245 l_time_start varchar2(5);
246 l_date_end date;
247 l_time_end varchar2(5);
248 v_date_start date;
249 v_time_start varchar2(5);
250 v_date_end date;
251 v_time_end varchar2(5);
252 --7287548 End
253
254 BEGIN
255 --
256 -- Added for GSI Bug 5472781
257 --
258 IF hr_utility.chk_product_install('Oracle Human Resources', 'GB') THEN
259 -- Get absence details
260 open csr_abs_details;
261 fetch csr_abs_details into l_abs_type_id,
262 l_business_group_id,
263 l_person_id,
264 l_current_start,
265 l_current_end
266 --7287548 begin
267 ,l_date_start
268 ,l_time_start
269 ,l_date_end
270 ,l_time_end;
271 --7287548 End
272 close csr_abs_details;
273 --
274 -- Check if default date is passed in for start/end date
275 -- logic
276 -- if not updating start or end date then skip
277 -- if not updating start or end date, but date is pass in then skip
278 -- if updating start/end and exists a future absence then error
279 if trunc(p_date_start) = trunc(hr_api.g_date) and
280 trunc(p_date_end) = trunc(hr_api.g_date) then
281 -- not updating sickness start/end date so skip
282 null;
283 else
284 if trunc(l_current_start) = trunc(p_date_start) and
285 trunc(l_current_end) = trunc(p_date_end) then
286 -- not updating sickness start/end date so skip
287 null;
288 else
289 -- going to update start or end date, check for future absence
290 -- Get absence_category
291 --
292 OPEN get_abs_category(l_abs_type_id, l_business_group_id);
293 FETCH get_abs_category INTO l_abs_category;
294 CLOSE get_abs_category;
295 --
296 --
297 IF l_abs_category = 'S' THEN
298 if trunc(p_date_start) = trunc(hr_api.g_date) then
299 l_param_start := trunc(l_current_start);
300 else
301 l_param_start := trunc(p_date_start);
302 end if;
303 OPEN csr_absences(l_person_id, l_business_group_id,l_param_start);
304 FETCH csr_absences INTO l_absence;
305 if csr_absences%found then
306 close csr_absences;
307 hr_utility.set_message(804,'SSP_35037_PIW_BROKEN');
308 hr_utility.raise_error;
309 else
310 close csr_absences;
311 end if;
312 END IF;
313 end if;
314 end if;
315
316 --7287548 begin
317 If (p_date_start = hr_api.g_date) then
318 hr_utility.trace(' p_date_start value is defaulted '||p_date_start||' Repl by'||l_date_start);
319 v_date_start := l_date_start;
320 else
321 v_date_start := p_date_start;
322 End If;
323
324 If (p_date_end = hr_api.g_date) then
325 hr_utility.trace(' p_date_end value is defaulted '||p_date_end||' Repl by'||l_date_end);
326 v_date_end := l_date_end;
327 else
328 v_date_end := p_date_end;
329 End If;
330
331 If (p_time_start = hr_api.g_varchar2) then
332 hr_utility.trace(' p_time_start value is defaulted '||p_time_start||' Repl by'||l_time_start);
333 v_time_start := l_time_start;
334 else
335 v_time_start := p_time_start;
336 End If;
337
338 If (p_time_end = hr_api.g_varchar2) then
339 hr_utility.trace(' p_time_end value is defaulted '||p_time_end||' Repl by'||l_time_end);
340 v_time_end := l_time_end;
341 else
342 v_time_end := p_time_end;
343 End If;
344
345 --7287548 end
346
347
348 -- Bug 6708992
349 -- Raise error if overlapping absences of same type are present
350 -- Bug 6888892 begin
351 -- To check only in case below mentioned absence categories also
352 -- changed the check to be based on Absence Category
353 if l_abs_category in ('S','M','GB_ADO','GB_PAT_BIRTH','GB_PAT_ADO') then
354 check_abs_overlap( l_person_id, v_date_start, v_date_end
355 ,v_time_start, v_time_end, p_absence_attendance_id
356 -- ,l_abs_type_id) ;
357 ,l_abs_category);
358 end if;
359 -- Bug 6888892 begin
360
361 END IF;
362 END validate_abs_update;
363
364 -------------------------------------------------------------------------------
365 -- VALIDATE_ABS_DELETE
366 -------------------------------------------------------------------------------
367 PROCEDURE validate_abs_delete(p_absence_attendance_id IN NUMBER
368 ) IS
369
370
371
372 CURSOR get_abs_category IS
373 SELECT paat.absence_category
374 FROM per_absence_attendance_types paat,
375 per_absence_attendances paa
376 WHERE paa.absence_attendance_id = p_absence_attendance_id
377 AND paa.absence_attendance_type_id = paat.absence_attendance_type_id;
378
379
380 CURSOR csr_absences IS
381 SELECT 1
382 FROM per_absence_attendances PAA
383 WHERE PAA.absence_attendance_id = p_absence_attendance_id
384 AND PAA.sickness_start_date is not null
385 AND PAA.sickness_start_date < (select max(ABS.sickness_start_date)
386 from per_absence_attendances ABS
387 where ABS.person_id =PAA.person_id);
388
389 l_absence NUMBER;
390 l_abs_category per_absence_attendance_types.absence_category%TYPE;
391
392 --
393 BEGIN
394 --
395 -- Added for GSI Bug 5472781
396 --
397 IF hr_utility.chk_product_install('Oracle Human Resources', 'GB') THEN
398 --
399 -- Get absence_category
400 --
401 OPEN get_abs_category;
402 FETCH get_abs_category INTO l_abs_category;
403 CLOSE get_abs_category;
404
405
406 IF l_abs_category = 'S' THEN
407
408 OPEN csr_absences;
409 FETCH csr_absences INTO l_absence;
410 if csr_absences%found then
411 close csr_absences;
412 hr_utility.set_message(804,'SSP_35037_PIW_BROKEN');
413 hr_utility.raise_error;
414 else
415 close csr_absences;
416 end if;
417 END IF;
418 END IF;
419 END validate_abs_delete;
420 --
421 END per_gb_absence_rules;
[Home] [Help]