[Home] [Help]
PACKAGE BODY: APPS.PER_ABSENCE_ATTENDANCES_PKG
Source
1 package body PER_ABSENCE_ATTENDANCES_PKG as
2 /* $Header: peaba01t.pkb 120.0 2005/05/31 04:39:27 appldev noship $ */
3
4 /* Date Name Release Description
5 ---- ---- ------- -----------
6 25-Jul-95 AMILLS 70.9 Replaced tokenised error messages with hard
7 coded messages, including
8 'PER_7073_EMP_ABS_DURATION' with
9 PER_7887_EMP_ABS_DURATION ,
10 PER_7886_EMP_ABS_DURATION.
11 06-SEP-95 SSETHI Added SSP/SMP Procedures/Functions and
12 modified Insert_row, Update_Row and
13 Lock_Row to include new columns.
14 15-SEP-95 SSETHI Removed calls to
15 app_exception.raise_exception
16 3 Oct 95 N Simpson Added code to Insert/Update/Delete Row
17 procedures to validate SSP Periods of Incapacity
18 for Work.
19 6-OCT-95 S Sethi Added constraint exception handling to
20 insert and update statements.
21 12-OCT-95 N Simpson Commented out references to objects which will
22 not be delivered until SSP delivery. These
23 commented out bits should be uncommented at that
24 time.
25 13-OCT-95 J Thuringer Replaced 'PACKAGE_NAME' variable by
26 hardcoded 'PER_ABSENCE_ATTENDANCES_PKG';
27 removed error checking code from end of script
28 17-OCT-95 F Assadi Added a new function get_anuual_bal to work
29 out the absence taken from the year.
30 is_emp_entitled function was added. This
31 function checks whether an employee is
32 entilted for the requested absence.
33 03-NOV-95 N Simpson Amended constraint names in procedure
34 constraint_error.
35 13-Nov-95 N Simpson Renamed column reason_for_notification_delay
36 because it was incorrectly named on the table
37 and it is easier to modify all references to it
38 than to rename the column itself.
39 16 Nov 95 N Simpson Corrected message names in constraint_error
40 and added comments.
41 05 Dec 95 A Mills Corrected date checking method for ease of
42 translation.
43 20 Dec 95 N Simpson Added check on MPP start date so that maternity
44 absence may not start if there is no MPP start
45 date on the maternity record.
46 20 Dec 95 N Simpson Changed check_related_maternity to use a
47 different error message.
48 04 Sep 96 K Habibulla Removed rtrim on columns sickness_start_date
49 and sickness_end_date as this was causing a
50 locking problem on the record.
51 * Date columns should not be rtrim'med *
52 04 Mar 97 J Alloun Changed all occurances of system.dual
53 to sys.dual for next release requirements.
54 ------------------------------------------------------------------------------------
55 Change List
56 ===========
57
58 Version Date BugNo Author Comment
59 -------+------------+---------+----------+--------------------------------------
60 70.36 29-NOV-1996 418051 Khabibul Applied fix to bug 418051. Suppressed the
61 index in procedure geT-annual_balance on
62 pay_element_entry_values_f.input_value_id
63 Also included new Change List Header.
64
65 70.39 07-AUG-1997 Mbocutt Change hardcoded date usage to use
66 110.1 hr_general.end_of_time
67 70.40 23-OCT-1997 565478 ASAHAY modified cursor in b_check_auth_de
68 110.1 and b_check_rep_de to check for
69 correct start date and end date
70 70.42 05-MAY-1998 563202 AParkes Performance fix for SSP; Altered
71 110.3 cursor c19 in get_annual_balance
72 110.4 26-MAY-1998 673835 CTredwin Amended cursor csr_get_accrual_plan
73 since it could not previously
74 get any accrual plans. Cursor is
75 in fuction is_emp_entitled. Also
76 amended function to close cursor if
77 no plans are found.
78 110.5 07-SEP-1998 JARTHURT Added parameters to insert, update
79 and lock routine to deal with the new
80 DDF fields
81 110.6 22-DEC-1998 725730 A.Myers Added facility to update MPP date if
82 it is not null. Added procedures
83 get_mat_details, Delete_Row overload
84 and get_earliest_abs_start_date.
85 22-DEC-1998 673835 A.Myers C.Tredwin fix forward ported.
86 Altered is_emp_entitled. Changed
87 cursor to pick up element entries for
88 the correct element type.
89 110.7 19-Jan-1999 n/a C.Tredwin Replaced get_net_accrual call in
90 is_emp_entitled with new pto accrual
91 procedure per_accrual_calc_functions.
92 get_net_accrual.
93 110.8 19-Jan-1999 n/a C.Tredwin Correction to 110.7
94 110.9 19-Jan-1999 n/a C.Tredwin Correction to 110.8
95 110.10 20-Jan-1999 n/a C.Tredwin Corrected call to get_net_accrual,
96 the function having changed as a
97 result of the pto accruals upgrade.
98 115.9 11-Mar-1999 835944 S.Billing Altered cursor c15,
99 added employment category to link
100 selection criteria
101 115.10 12-Oct-1999 SCNair Date track position related changes
102 115.16 22-Oct-1999 Ctredwin Added Batch_Id to insert and
103 update procedures.
104 115.16 07-Feb-2000 1184545 CTredwin Amended get_defaults to calculate
105 days in hours value correctly, ie
106 (end date - start date + 1) * 24
107 115.17 24-Jul-2000 1337672 CTredwin Amended get_defaults again to
108 correct default hours calculation
109 115.19 19-Oct-2000 1408379 GPerry Fixed WWBUG 1408379.
110 Provided hooks to OAB.
111 115.20 24-Jan-2002 2189311 Kparekh Took out the default null values from
112 the 'insert_row' procedure to match
113 the parameter values in the header.
114 115.21 09-JUL-2002 mmudigon Absences related changes
115 Added new proc populate_ben_absence_rec.
116 Also added a call to ler_chk in
117 delete_row
118 115.22 11-JUL-2002 mmudigon Added dbdrv commands
119 115.24 23-JUL-2003 2829746 ablinko amendments for SAP/SPP
120 115.25 25-Aug-2003 3111653 skota added to_number() for explicit
121 conversion. Seems implicit conversion
122 is not taking place in 10G
123 115.26 06-NOV-2003 3156665 kjagadee Modified per_absence_attendances_pkg.get_defaults
124 procedure to calculate the drfault hours in a day
125 as 24 hours.
126 115.27 29-JAN-2004 3387265 smparame Modified inti_form procedure.
127 upper(formula_type_name) is changed to formula
128 name in the cursor c21 to avoid FTS.
129 115.28 27-APR-2004 3580603 kthampan Change the condition in the if statement
130 in procedure default_MPP_start_date.
131 115.29 29-JUN-2004 3648445 kjagadee Performance repository fixes.
132 115.30 14-JUL-2004 3694165 ablinko Now calls earliest_ppp_start_date with
133 actual_birth_date instead of due_date
134 -------+------------+---------+----------+--------------------------------------
135 */
136 g_package varchar2(33) := ' per_absence_attendances_pkg.';
137 -- Global package name
138
139 --
140 ------------------------------------------------------------------------------
141 -- |--------------------------< constraint_error >---------------------------|
142 ------------------------------------------------------------------------------
143 Procedure constraint_error
144 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
145 --
146 l_proc varchar2(72) := g_package||'constraint_error';
147 --
148 Begin
149 --
150 hr_utility.set_location('Entering:'||l_proc, 5);
151 --
152 If (p_constraint_name = 'PER_ABSENCE_ATTENDANCES_FK4') Then
153 --
154 -- The linked_absence_id does not refer to a valid absence_attendance_id.
155 --
156 fnd_message.set_name('SSP', 'SSP_35060_SSP_ABS_FK2');
157 --
158 ElsIf (p_constraint_name = 'PER_ABSENCE_ATTENDANCES_FK5') Then
159 --
160 -- The maternity_id is not valid.
161 --
162 fnd_message.set_name('SSP', 'SSP_35061_SSP_ABS_FK1');
163 --
164 ElsIf (p_constraint_name = 'SSP_ABA_SICKNESS_END_DATE') Then
165 --
166 -- date_end must be specified if sickness_end_date is specified, and
167 -- date_end cannot be after sickness_end_date
168 --
169 fnd_message.set_name('SSP', 'SSP_35062_ABS_SICK_END_DATE');
170 --
171 ElsIf (p_constraint_name = 'SSP_ABA_MATERNITY_NOT_SICKNESS') Then
172 --
173 -- Cannot mix maternity and sickness attributes for an absence.
174 --
175 fnd_message.set_name('SSP', 'SSP_35063_MAT_NOT_SICKNESS');
176 --
177 ElsIf (p_constraint_name = 'SSP_ABA_START_AND_END_DATES') Then
178 --
179 -- Date_start must be before date_end
180 --
181 fnd_message.set_name('SSP', 'SSP_35064_ABS_START_AND_END');
182 --
183 ElsIf (p_constraint_name = 'SSP_ABA_SICKNESS_START_DATE') Then
184 --
185 -- The sickness start date must not be after the absence date start
186 --
187 fnd_message.set_name('SSP', 'SSP_36066_ABS_SICK_START_DATE');
188 --
189 ElsIf (p_constraint_name = 'SSP_ABA_SICK_NOTIFICATION_DATE') Then
190 --
191 -- The notification date must be on or after the sickness start date.
192 --
193 fnd_message.set_name('SSP', 'SSP_35067_ABS_SICK_NOTIF_DATE');
194 --
195 ElsIf (p_constraint_name = 'SSP_ABA_TIME_START_FORMAT') Then
196 --
197 -- The time format is incorrect.
198 --
199 fnd_message.set_name('SSP', 'SSP_36069_TIME_FORMAT');
200 --
201 ElsIf (p_constraint_name = 'SSP_ABA_TIME_END_FORMAT') Then
202 --
203 -- The time format is incorrect.
204 --
205 fnd_message.set_name('SSP', 'SSP_36069_TIME_FORMAT');
206 --
207 ElsIf (p_constraint_name = 'SSP_ABA_SICKNESS_ATTRIBUTES') Then
208 --
209 -- An invalid combination of attributes have been entered.
210 --
211 fnd_message.set_name('SSP', 'SSP_36072_ABS_SICK_ATTRIBUTES');
212 --
213 ElsIf (p_constraint_name = 'SSP_35037_PIW_BROKEN') Then
214 --
215 -- The linked series of sickness absences has been disrupted.
216 --
217 fnd_message.set_name('SSP', 'SSP_35037_PIW_BROKEN');
218 --
219 Else
220 --
221 -- A constraint has been violated which is not explicitly handled by this
222 -- code.
223 --
224 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
225 fnd_message.set_token('PROCEDURE',l_proc);
226 fnd_message.set_token('CONSTRAINT_NAME',p_constraint_name);
227 --
228 End If;
229 --
230 fnd_message.raise_error;
231 --
232 hr_utility.set_location(' Leaving:'||l_proc, 10);
233 --
234 End constraint_error;
235 --
236 -- ---------------------------------------------------------------------------
237 -- ---------------------< get_earliest_abs_start_date >-----------------------
238 -- ---------------------------------------------------------------------------
239 -- PRIVATE:
240 -- Description: returns start date of earliest absence for this maternity and
241 -- the number of absences for the maternity.
242 --
243 procedure get_earliest_abs_start_date (
244 p_maternity_id in number,
245 p_earliest_abs_start_date in out nocopy date,
246 p_earliest_abs_rowid in out nocopy rowid,
247 p_nos_absences in out nocopy number) is
248 --
249 cursor c2 is
250 select date_start, rowid
251 from per_absence_attendances
252 where maternity_id = p_maternity_id;
253 c2_rec c2%ROWTYPE;
254 --
255 begin
256 p_nos_absences := 0;
257 p_earliest_abs_start_date := hr_general.end_of_time;
258 --
259 for c2_rec in c2 LOOP
260 p_nos_absences := p_nos_absences + 1;
261 --
262 if c2_rec.date_start < p_earliest_abs_start_date
263 then
264 p_earliest_abs_start_date := c2_rec.date_start;
265 p_earliest_abs_rowid := c2_rec.rowid;
266 end if;
267 end LOOP;
268 --
269 end get_earliest_abs_start_date;
270 --
271 -- ---------------------------------------------------------------------------
272 -- ---------------------------< MPP_update_mode >-----------------------------
273 -- ---------------------------------------------------------------------------
274 procedure MPP_update_mode (p_update_mode in number) is
275 --
276 l_proc varchar2 (72) := g_package||'MPP_update_mode';
277 --
278 begin
279 --
280 hr_utility.set_location ('Entering:'||l_proc,1);
281 --
282 if p_update_mode = 1
283 then
284 g_mpp_update_mode := TRUE;
285 else
286 g_mpp_update_mode := FALSE;
287 end if;
288 --
289 hr_utility.set_location ('Leaving :'||l_proc,100);
290 --
291 end MPP_update_mode;
292 --
293 ------------------------------------------------------------------------------
294 procedure reset_MPP_start_date_on_delete (p_maternity_id in number) is
295 --
296 cursor csr_MPP is
297 select MPP_start_date,
298 leave_type,
299 object_version_number,
300 decode(leave_type,
301 'AD',ssp_sap_pkg.earliest_APP_start_date(due_date),
302 'PA',ssp_pad_pkg.earliest_PPP_start_date(due_date),
303 'PB',ssp_pab_pkg.earliest_PPP_start_date(actual_birth_date),
304 ssp_smp_pkg.earliest_MPP_start_date (due_date))
305 earliest_MPP_start_date
306 from ssp_maternities
307 where maternity_id = p_maternity_id;
308 --
309 l_maternity csr_MPP%rowtype;
310 --
311 l_proc varchar2 (72) := g_package||'reset_MPP_start_date_on_delete';
312 --
313 l_default_MPP_start_date date := null;
314 l_earliest_abs_start_date date;
315 l_earliest_abs_rowid rowid;
316 l_nos_absences number;
317 --
318 begin
319 --
320 hr_utility.set_location ('Entering:'||l_proc,1);
321 --
322 if g_mpp_update_mode = TRUE
323 then
324 --
325 -- If we are deleting an absence, then reset the MPP start date IF there are
326 -- still absences left to provide data to do this.
327 --
328 open csr_MPP;
329 fetch csr_MPP into l_maternity;
330 close csr_MPP;
331 --
332 get_earliest_abs_start_date(p_maternity_id,
333 l_earliest_abs_start_date,
334 l_earliest_abs_rowid,
335 l_nos_absences);
336 --
337 if l_nos_absences = 0 and l_maternity.MPP_start_date is not null
338 then
339 ssp_mat_upd.upd
340 (p_maternity_id => p_maternity_id,
341 p_object_version_number => l_maternity.object_version_number,
342 p_leave_type => l_maternity.leave_type,
343 p_mpp_start_date => null);
344 else
345 l_default_MPP_start_date :=
346 greatest (l_earliest_abs_start_date, l_maternity.earliest_MPP_start_date
347 );
348 ssp_mat_upd.upd
349 (p_maternity_id => p_maternity_id,
350 p_object_version_number => l_maternity.object_version_number,
351 p_leave_type => l_maternity.leave_type,
352 p_mpp_start_date => l_default_MPP_start_date);
353 end if;
354 --
355 g_mpp_updated_date := l_default_MPP_start_date;
356 end if;
357 --
358 hr_utility.set_location ('Leaving :'||l_proc,100);
359 --
360 end reset_MPP_start_date_on_delete;
361 --
362 --------------------------------------------------------------------------------
363 procedure default_MPP_start_date (
364 --
365 p_maternity_id in number,
366 p_start_date in date,
367 p_end_date in date) is
368 --
369 l_proc varchar2 (72) := g_package||'default_MPP_start_date';
370 --
371 cursor csr_MPP is
372 --
373 -- Find out if the MPP has started
374 --
375 select MPP_start_date,
376 object_version_number,
377 decode(leave_type,
378 'AD',ssp_sap_pkg.earliest_APP_start_date(due_date),
379 'PA',ssp_pad_pkg.earliest_PPP_start_date(due_date),
380 'PB',ssp_pab_pkg.earliest_PPP_start_date(actual_birth_date),
381 ssp_smp_pkg.earliest_MPP_start_date (due_date))
382 earliest_MPP_start_date,
383 ssp_smp_pkg.qualifying_week (due_date) QW,
384 nvl(mpp_start_date,hr_general.end_of_time) MPP,
385 person_id,
386 leave_type,
387 placement_date,
388 due_date,
389 actual_birth_date
390 from ssp_maternities
391 where maternity_id = p_maternity_id;
392 --
393 l_maternity csr_MPP%rowtype;
394 l_default_MPP_start_date date := null;
395 l_end_date date := nvl (p_end_date, hr_general.end_of_time);
396 l_current_mpp_start_date date := hr_general.end_of_time;
397 l_earliest_abs_start_date date;
398 l_ewc date;
399 l_earliest_mpp_start date;
400 l_qw date;
401 l_cont_emp_start date;
402 l_earliest_abs_rowid rowid;
403 l_nos_absences number;
404 l_mpp_updated_date date := g_mpp_updated_date;
405 --
406 begin
407 --
408 hr_utility.set_location ('Entering:'||l_proc,1);
409 --
410 if p_maternity_id is not null
411 then
412 --
413 -- this is a maternity absence so look up the maternity record
414 --
415 open csr_MPP;
416 fetch csr_MPP into l_maternity;
417 close csr_MPP;
418 --
419 if (
420 -- The MPP has not started
421 l_maternity.MPP_start_date is null
422 or
423 -- or user wants the MPP date to be udpated
424 g_mpp_update_mode = TRUE
425 )
426 -- and the absence does not end before the earliest MPP start date
427 and l_end_date >= l_maternity.earliest_MPP_start_date
428 then
429 get_earliest_abs_start_date(p_maternity_id,
430 l_earliest_abs_start_date,
431 l_earliest_abs_rowid,
432 l_nos_absences);
433 --
434 -- The default MPP start date is the later of the absence start and
435 -- the earliest allowed MPP start date.
436 --
437 l_default_MPP_start_date :=
438 greatest (least(p_start_date, l_earliest_abs_start_date),
439 l_maternity.earliest_MPP_start_date);
440 --
441 if l_maternity.MPP_start_date is not null
442 then
443 l_current_mpp_start_date := l_maternity.MPP_start_date;
444 end if;
445 --
446 -- Update the MPP start date to the default MPP start date
447 --
448
449 -- Bug 3580603 - change the condition of the If statement
450 if (l_current_mpp_start_date <> l_default_MPP_start_date)
451 then
452
453 if l_maternity.leave_type = 'AD'
454 then
455 ssp_mat_bus.check_app_start_date(
456 p_mpp_start_date => l_default_MPP_start_date,
457 p_placement_date => l_maternity.placement_date,
458 p_due_date => l_maternity.due_date);
459 elsif l_maternity.leave_type = 'PB'
460 then
461 sspwsmat_pkg.calculate_pab_form_fields (
462 p_due_date => l_maternity.due_date,
463 p_ewc => l_ewc,
464 p_qw => l_qw,
465 p_cont_emp_start_date => l_cont_emp_start);
466
467 ssp_mat_bus.check_ppp_start_date (
468 p_ppp_start_date => l_default_MPP_start_date,
469 p_birth_date => l_maternity.actual_birth_date,
470 p_ewc => l_ewc,
471 p_due_date => l_maternity.due_date);
472 elsif l_maternity.leave_type = 'PA'
473 then
474 ssp_mat_bus.check_pppa_start_date (
475 p_ppp_start_date => l_default_MPP_start_date,
476 p_placement_date => l_maternity.placement_date,
477 p_due_date => l_maternity.due_date);
478 else
479 sspwsmat_pkg.calculate_smp_form_fields (
480 p_due_date => l_maternity.due_date,
481 p_ewc => l_ewc,
482 p_earliest_mpp_start => l_earliest_mpp_start,
483 p_qw => l_qw,
484 p_cont_emp_start_date => l_cont_emp_start);
485
486 ssp_mat_bus.check_MPP_start_date_2 (
487 p_mpp_start_date => l_default_MPP_start_date,
488 p_person_id => l_maternity.person_id,
489 p_ewc => l_ewc,
490 p_earliest_mpp_start_date => l_earliest_mpp_start,
491 p_due_date => l_maternity.due_date,
492 p_prev_mpp_start_date => l_maternity.mpp,
493 p_actual_birth_date => l_maternity.actual_birth_date);
494
495 end if;
496
497 ssp_mat_upd.upd
498 (p_maternity_id => p_maternity_id,
499 p_object_version_number => l_maternity.object_version_number,
500 p_leave_type => l_maternity.leave_type,
501 p_mpp_start_date => l_default_MPP_start_date);
502 --
503 g_mpp_updated_date := l_default_MPP_start_date;
504 end if;
505 end if;
506 end if;
507 --
508 hr_utility.set_location ('Leaving :'||l_proc,100);
509 --
510 end default_MPP_start_date;
511 --
512 procedure populate_ben_absence_rec
513 (p_absence_attendance_id in number,
514 p_rec_type in varchar2,
515 p_old_rec in per_absence_attendances%rowtype,
516 p_ben_rec out nocopy ben_abs_ler.g_abs_ler_rec) is
517
518 cursor c_current_absence is
519 select *
520 from per_absence_attendances
521 where absence_attendance_id = p_absence_attendance_id;
522 --
523 l_absence_rec per_absence_attendances%rowtype;
524 l_proc varchar2(72) := g_package||'populate_ben_absence_rec';
525
526 begin
527
528 hr_utility.set_location('Entering: '|| l_proc, 10);
529
530 if (p_rec_type = 'O') then
531 l_absence_rec := p_old_rec;
532 else
533 open c_current_absence;
534 fetch c_current_absence into l_absence_rec;
535 close c_current_absence;
536 end if;
537
538 p_ben_rec.person_id := l_absence_rec.person_id;
539 p_ben_rec.business_group_id := l_absence_rec.business_group_id;
540 p_ben_rec.date_start := l_absence_rec.date_start;
541 p_ben_rec.date_end := l_absence_rec.date_end;
542 p_ben_rec.absence_attendance_type_id := l_absence_rec.absence_attendance_type_id;
543 p_ben_rec.abs_attendance_reason_id := l_absence_rec.abs_attendance_reason_id;
544 p_ben_rec.absence_attendance_id := l_absence_rec.absence_attendance_id;
545 p_ben_rec.authorising_person_id := l_absence_rec.authorising_person_id;
546 p_ben_rec.replacement_person_id := l_absence_rec.replacement_person_id;
547 p_ben_rec.period_of_incapacity_id := l_absence_rec.period_of_incapacity_id;
548 p_ben_rec.absence_days := l_absence_rec.absence_days;
549 p_ben_rec.absence_hours := l_absence_rec.absence_hours;
550 p_ben_rec.date_notification := l_absence_rec.date_notification;
551 p_ben_rec.date_projected_end := l_absence_rec.date_projected_end;
552 p_ben_rec.date_projected_start := l_absence_rec.date_projected_start;
553 p_ben_rec.occurrence := l_absence_rec.occurrence;
554 p_ben_rec.ssp1_issued := l_absence_rec.ssp1_issued;
555 p_ben_rec.time_end := l_absence_rec.time_end;
556 p_ben_rec.time_projected_end := l_absence_rec.time_projected_end;
557 p_ben_rec.time_projected_start := l_absence_rec.time_projected_start;
558 p_ben_rec.time_start := l_absence_rec.time_start;
559 p_ben_rec.attribute_category := l_absence_rec.attribute_category;
560 p_ben_rec.attribute1 := l_absence_rec.attribute1;
561 p_ben_rec.attribute2 := l_absence_rec.attribute2;
562 p_ben_rec.attribute3 := l_absence_rec.attribute3;
563 p_ben_rec.attribute4 := l_absence_rec.attribute4;
564 p_ben_rec.attribute5 := l_absence_rec.attribute5;
565 p_ben_rec.attribute6 := l_absence_rec.attribute6;
566 p_ben_rec.attribute7 := l_absence_rec.attribute7;
567 p_ben_rec.attribute8 := l_absence_rec.attribute8;
568 p_ben_rec.attribute9 := l_absence_rec.attribute9;
569 p_ben_rec.attribute10 := l_absence_rec.attribute10;
570 p_ben_rec.attribute11 := l_absence_rec.attribute11;
571 p_ben_rec.attribute12 := l_absence_rec.attribute12;
572 p_ben_rec.attribute13 := l_absence_rec.attribute13;
573 p_ben_rec.attribute14 := l_absence_rec.attribute14;
574 p_ben_rec.attribute15 := l_absence_rec.attribute15;
575 p_ben_rec.attribute16 := l_absence_rec.attribute16;
576 p_ben_rec.attribute17 := l_absence_rec.attribute17;
577 p_ben_rec.attribute18 := l_absence_rec.attribute18;
578 p_ben_rec.attribute19 := l_absence_rec.attribute19;
579 p_ben_rec.attribute20 := l_absence_rec.attribute20;
580 p_ben_rec.maternity_id := l_absence_rec.maternity_id;
581 p_ben_rec.sickness_start_date := l_absence_rec.sickness_start_date;
582 p_ben_rec.sickness_end_date := l_absence_rec.sickness_end_date;
583 p_ben_rec.pregnancy_related_illness := l_absence_rec.pregnancy_related_illness;
584 p_ben_rec.reason_for_notification_delay := l_absence_rec.reason_for_notification_delay;
585 p_ben_rec.accept_late_notification_flag := l_absence_rec.accept_late_notification_flag;
586 p_ben_rec.linked_absence_id := l_absence_rec.linked_absence_id;
587 p_ben_rec.batch_id := l_absence_rec.batch_id;
588 p_ben_rec.abs_information_category := l_absence_rec.abs_information_category;
589 p_ben_rec.abs_information1 := l_absence_rec.abs_information1;
590 p_ben_rec.abs_information2 := l_absence_rec.abs_information2;
591 p_ben_rec.abs_information3 := l_absence_rec.abs_information3;
592 p_ben_rec.abs_information4 := l_absence_rec.abs_information4;
593 p_ben_rec.abs_information5 := l_absence_rec.abs_information5;
594 p_ben_rec.abs_information6 := l_absence_rec.abs_information6;
595 p_ben_rec.abs_information7 := l_absence_rec.abs_information7;
596 p_ben_rec.abs_information8 := l_absence_rec.abs_information8;
597 p_ben_rec.abs_information9 := l_absence_rec.abs_information9;
598 p_ben_rec.abs_information10 := l_absence_rec.abs_information10;
599 p_ben_rec.abs_information11 := l_absence_rec.abs_information11;
600 p_ben_rec.abs_information12 := l_absence_rec.abs_information12;
601 p_ben_rec.abs_information13 := l_absence_rec.abs_information13;
602 p_ben_rec.abs_information14 := l_absence_rec.abs_information14;
603 p_ben_rec.abs_information15 := l_absence_rec.abs_information15;
604 p_ben_rec.abs_information16 := l_absence_rec.abs_information16;
605 p_ben_rec.abs_information17 := l_absence_rec.abs_information17;
606 p_ben_rec.abs_information18 := l_absence_rec.abs_information18;
607 p_ben_rec.abs_information19 := l_absence_rec.abs_information19;
608 p_ben_rec.abs_information20 := l_absence_rec.abs_information20;
609 p_ben_rec.abs_information21 := l_absence_rec.abs_information21;
610 p_ben_rec.abs_information22 := l_absence_rec.abs_information22;
611 p_ben_rec.abs_information23 := l_absence_rec.abs_information23;
612 p_ben_rec.abs_information24 := l_absence_rec.abs_information24;
613 p_ben_rec.abs_information25 := l_absence_rec.abs_information25;
614 p_ben_rec.abs_information26 := l_absence_rec.abs_information26;
615 p_ben_rec.abs_information27 := l_absence_rec.abs_information27;
616 p_ben_rec.abs_information28 := l_absence_rec.abs_information28;
617 p_ben_rec.abs_information29 := l_absence_rec.abs_information29;
618 p_ben_rec.abs_information30 := l_absence_rec.abs_information30;
619
620 hr_utility.set_location('Leaving: '|| l_proc, 15);
621 end;
622 --------------------------------------------------------------------------------
623 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
624 X_Absence_Attendance_Id IN OUT NOCOPY NUMBER,
625 X_Business_Group_Id NUMBER,
626 X_Absence_Attendance_Type_Id NUMBER,
627 X_Abs_Attendance_Reason_Id NUMBER,
628 X_Person_Id NUMBER,
629 X_Authorising_Person_Id NUMBER,
630 X_Replacement_Person_Id NUMBER,
631 X_Period_Of_Incapacity_Id NUMBER,
632 X_Absence_Days NUMBER,
633 X_Absence_Hours NUMBER,
634 X_Comments VARCHAR2,
635 X_Date_End DATE,
636 X_Date_Notification DATE,
637 X_Date_Projected_End DATE,
638 X_Date_Projected_Start DATE,
639 X_Date_Start DATE,
640 X_Occurrence NUMBER,
641 X_Ssp1_Issued VARCHAR2,
642 X_Time_End VARCHAR2,
643 X_Time_Projected_End VARCHAR2,
644 X_Time_Projected_Start VARCHAR2,
645 X_Time_Start VARCHAR2,
646 X_Attribute_Category VARCHAR2,
647 X_Attribute1 VARCHAR2,
648 X_Attribute2 VARCHAR2,
649 X_Attribute3 VARCHAR2,
650 X_Attribute4 VARCHAR2,
651 X_Attribute5 VARCHAR2,
652 X_Attribute6 VARCHAR2,
653 X_Attribute7 VARCHAR2,
654 X_Attribute8 VARCHAR2,
655 X_Attribute9 VARCHAR2,
656 X_Attribute10 VARCHAR2,
657 X_Attribute11 VARCHAR2,
658 X_Attribute12 VARCHAR2,
659 X_Attribute13 VARCHAR2,
660 X_Attribute14 VARCHAR2,
661 X_Attribute15 VARCHAR2,
662 X_Attribute16 VARCHAR2,
663 X_Attribute17 VARCHAR2,
664 X_Attribute18 VARCHAR2,
665 X_Attribute19 VARCHAR2,
666 X_Attribute20 VARCHAR2 ,
667 X_Linked_Absence_id NUMBER,
668 X_Sickness_Start_Date DATE,
669 X_Sickness_End_Date DATE,
670 X_Accept_Late_Notif_Flag VARCHAR2,
671 x_reason_for_late_notification VARCHAR2,
672 X_Pregnancy_Related_Illness VARCHAR2,
673 X_Maternity_Id NUMBER,
674 X_Batch_Id NUMBER default null,
675 X_Abs_Information_Category VARCHAR2,
676 X_Abs_Information1 VARCHAR2,
677 X_Abs_Information2 VARCHAR2,
678 X_Abs_Information3 VARCHAR2,
679 X_Abs_Information4 VARCHAR2,
680 X_Abs_Information5 VARCHAR2,
681 X_Abs_Information6 VARCHAR2,
682 X_Abs_Information7 VARCHAR2,
683 X_Abs_Information8 VARCHAR2,
684 X_Abs_Information9 VARCHAR2,
685 X_Abs_Information10 VARCHAR2,
686 X_Abs_Information11 VARCHAR2,
687 X_Abs_Information12 VARCHAR2,
688 X_Abs_Information13 VARCHAR2,
689 X_Abs_Information14 VARCHAR2,
690 X_Abs_Information15 VARCHAR2,
691 X_Abs_Information16 VARCHAR2,
692 X_Abs_Information17 VARCHAR2,
693 X_Abs_Information18 VARCHAR2,
694 X_Abs_Information19 VARCHAR2,
695 X_Abs_Information20 VARCHAR2,
696 X_Abs_Information21 VARCHAR2,
697 X_Abs_Information22 VARCHAR2,
698 X_Abs_Information23 VARCHAR2,
699 X_Abs_Information24 VARCHAR2,
700 X_Abs_Information25 VARCHAR2,
701 X_Abs_Information26 VARCHAR2,
702 X_Abs_Information27 VARCHAR2,
703 X_Abs_Information28 VARCHAR2,
704 X_Abs_Information29 VARCHAR2,
705 X_Abs_Information30 VARCHAR2
706 ) IS
707 CURSOR C IS SELECT rowid FROM per_absence_attendances
708 WHERE absence_attendance_id = X_absence_attendance_id;
709
710 CURSOR C2 IS SELECT per_absence_attendances_s.nextval FROM sys.dual;
711
712 cursor csr_later_sickness is
713 select 1
714 from per_absence_attendances
715 where person_id = X_person_id
716 and sickness_start_date is not null
717 and sickness_start_date > X_sickness_start_date;
718 --
719 l_dummy number;
720 --
721 -- Start of BUGFIX for WWBUG 1408379
722 --
723 l_old ben_abs_ler.g_abs_ler_rec;
724 l_new ben_abs_ler.g_abs_ler_rec;
725 --
726 -- End of BUGFIX for WWBUG 1408379
727 --
728 BEGIN
729
730 --
731 -- Check that maternity absence does not start before the QW
732 check_val_abs_start (p_maternity_id => x_maternity_id,
733 p_date_start => x_date_start);
734 --
735 -- Prevent insertion of sickness absence prior to an existing sickness
736 -- for the person
737 --
738 if X_sickness_start_date is not null then
739 --
740 -- We must be dealing with a sickness absence
741 --
742 open csr_later_sickness;
743 fetch csr_later_sickness into l_dummy;
744 if csr_later_sickness%found then
745 --
746 close csr_later_sickness;
747 constraint_error (p_constraint_name => 'SSP_35037_PIW_BROKEN');
748 --
749 else
750 --
751 close csr_later_sickness;
752 --
753 end if;
754 --
755 end if;
756 --
757
758 if (X_absence_attendance_id is NULL) then
759 OPEN C2;
760 FETCH C2 INTO X_absence_attendance_id;
761 CLOSE C2;
762 end if;
763 INSERT INTO per_absence_attendances(
764 absence_attendance_id,
765 business_group_id,
766 absence_attendance_type_id,
767 abs_attendance_reason_id,
768 person_id,
769 authorising_person_id,
770 replacement_person_id,
771 period_of_incapacity_id,
772 absence_days,
773 absence_hours,
774 comments,
775 date_end,
776 date_notification,
777 date_projected_end,
778 date_projected_start,
779 date_start,
780 occurrence,
781 ssp1_issued,
782 time_end,
783 time_projected_end,
784 time_projected_start,
785 time_start,
786 attribute_category,
787 attribute1,
788 attribute2,
789 attribute3,
790 attribute4,
791 attribute5,
792 attribute6,
793 attribute7,
794 attribute8,
795 attribute9,
796 attribute10,
797 attribute11,
798 attribute12,
799 attribute13,
800 attribute14,
801 attribute15,
802 attribute16,
803 attribute17,
804 attribute18,
805 attribute19,
806 attribute20,
807 Linked_Absence_id,
808 Sickness_Start_Date,
809 Sickness_End_Date,
810 Accept_Late_Notification_Flag,
811 reason_for_notification_delay,
812 Pregnancy_Related_Illness,
813 Maternity_Id,
814 Batch_Id,
815 abs_information_category,
816 abs_information1,
817 abs_information2,
818 abs_information3,
819 abs_information4,
820 abs_information5,
821 abs_information6,
822 abs_information7,
823 abs_information8,
824 abs_information9,
825 abs_information10,
826 abs_information11,
827 abs_information12,
828 abs_information13,
829 abs_information14,
830 abs_information15,
831 abs_information16,
832 abs_information17,
833 abs_information18,
834 abs_information19,
835 abs_information20,
836 abs_information21,
837 abs_information22,
838 abs_information23,
839 abs_information24,
840 abs_information25,
841 abs_information26,
842 abs_information27,
843 abs_information28,
844 abs_information29,
845 abs_information30
846 ) VALUES (
847 X_Absence_Attendance_Id,
848 X_Business_Group_Id,
849 X_Absence_Attendance_Type_Id,
850 X_Abs_Attendance_Reason_Id,
851 X_Person_Id,
852 X_Authorising_Person_Id,
853 X_Replacement_Person_Id,
854 X_Period_Of_Incapacity_Id,
855 X_Absence_Days,
856 X_Absence_Hours,
857 X_Comments,
858 X_Date_End,
859 X_Date_Notification,
860 X_Date_Projected_End,
861 X_Date_Projected_Start,
862 X_Date_Start,
863 X_Occurrence,
864 X_Ssp1_Issued,
865 X_Time_End,
866 X_Time_Projected_End,
867 X_Time_Projected_Start,
868 X_Time_Start,
869 X_Attribute_Category,
870 X_Attribute1,
871 X_Attribute2,
872 X_Attribute3,
873 X_Attribute4,
874 X_Attribute5,
875 X_Attribute6,
876 X_Attribute7,
877 X_Attribute8,
878 X_Attribute9,
879 X_Attribute10,
880 X_Attribute11,
881 X_Attribute12,
882 X_Attribute13,
883 X_Attribute14,
884 X_Attribute15,
885 X_Attribute16,
886 X_Attribute17,
887 X_Attribute18,
888 X_Attribute19,
889 X_Attribute20,
890 X_Linked_Absence_id,
891 X_Sickness_Start_Date,
892 X_Sickness_End_Date,
893 X_Accept_Late_Notif_Flag,
894 x_reason_for_late_notification,
895 X_Pregnancy_Related_Illness,
896 X_Maternity_Id,
897 X_Batch_Id,
898 X_Abs_Information_Category,
899 X_Abs_Information1,
900 X_Abs_Information2,
901 X_Abs_Information3,
902 X_Abs_Information4,
903 X_Abs_Information5,
904 X_Abs_Information6,
905 X_Abs_Information7,
906 X_Abs_Information8,
907 X_Abs_Information9,
908 X_Abs_Information10,
909 X_Abs_Information11,
910 X_Abs_Information12,
911 X_Abs_Information13,
912 X_Abs_Information14,
913 X_Abs_Information15,
914 X_Abs_Information16,
915 X_Abs_Information17,
916 X_Abs_Information18,
917 X_Abs_Information19,
918 X_Abs_Information20,
919 X_Abs_Information21,
920 X_Abs_Information22,
921 X_Abs_Information23,
922 X_Abs_Information24,
923 X_Abs_Information25,
924 X_Abs_Information26,
925 X_Abs_Information27,
926 X_Abs_Information28,
927 X_Abs_Information29,
928 X_Abs_Information30
929 );
930 --
931 -- Start of BUGFIX for WWBUG 1408379
932 --
933 populate_ben_absence_rec
934 (p_absence_attendance_id => x_absence_attendance_id,
935 p_rec_type => 'N',
936 p_old_rec => null,
937 p_ben_rec => l_new);
938
939 ben_abs_ler.ler_chk(p_old => l_old,
940 p_new => l_new,
941 p_effective_date => trunc(nvl(l_new.date_start,sysdate)));
942 --
943 -- End of BUGFIX for WWBUG 1408379
944 --
945 OPEN C;
946 FETCH C INTO X_Rowid;
947 if (C%NOTFOUND) then
948 CLOSE C;
949 RAISE NO_DATA_FOUND;
950 end if;
951 CLOSE C;
952 --
953 -- SSP users only:
954 -- If the MPP has not started and the absence is maternity leave, update
955 -- the MPP start date
956 --
957 default_MPP_start_date (p_maternity_id => x_maternity_id,
958 p_start_date => x_date_start,
959 p_end_date => x_date_end);
960 --
961 Exception
962 When hr_api.check_integrity_violated Then
963 -- A check constraint has been violated
964 PER_ABSENCE_ATTENDANCES_PKG.constraint_error
965 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
966 When hr_api.parent_integrity_violated Then
967 -- Parent integrity has been violated
968 PER_ABSENCE_ATTENDANCES_PKG.constraint_error
969 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
970 When hr_api.unique_integrity_violated Then
971 -- Unique integrity has been violated
972 PER_ABSENCE_ATTENDANCES_PKG.constraint_error
973 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
974 When Others Then
975 Raise;
976 END Insert_Row;
977
978
979
980 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
981 X_Absence_Attendance_Id NUMBER,
982 X_Business_Group_Id NUMBER,
983 X_Absence_Attendance_Type_Id NUMBER,
984 X_Abs_Attendance_Reason_Id NUMBER,
985 X_Person_Id NUMBER,
986 X_Authorising_Person_Id NUMBER,
987 X_Replacement_Person_Id NUMBER,
988 X_Period_Of_Incapacity_Id NUMBER,
989 X_Absence_Days NUMBER,
990 X_Absence_Hours NUMBER,
991 X_Comments VARCHAR2,
992 X_Date_End DATE,
993 X_Date_Notification DATE,
994 X_Date_Projected_End DATE,
995 X_Date_Projected_Start DATE,
996 X_Date_Start DATE,
997 X_Occurrence NUMBER,
998 X_Ssp1_Issued VARCHAR2,
999 X_Time_End VARCHAR2,
1000 X_Time_Projected_End VARCHAR2,
1001 X_Time_Projected_Start VARCHAR2,
1002 X_Time_Start VARCHAR2,
1003 X_Attribute_Category VARCHAR2,
1004 X_Attribute1 VARCHAR2,
1005 X_Attribute2 VARCHAR2,
1006 X_Attribute3 VARCHAR2,
1007 X_Attribute4 VARCHAR2,
1008 X_Attribute5 VARCHAR2,
1009 X_Attribute6 VARCHAR2,
1010 X_Attribute7 VARCHAR2,
1011 X_Attribute8 VARCHAR2,
1012 X_Attribute9 VARCHAR2,
1013 X_Attribute10 VARCHAR2,
1014 X_Attribute11 VARCHAR2,
1015 X_Attribute12 VARCHAR2,
1016 X_Attribute13 VARCHAR2,
1017 X_Attribute14 VARCHAR2,
1018 X_Attribute15 VARCHAR2,
1019 X_Attribute16 VARCHAR2,
1020 X_Attribute17 VARCHAR2,
1021 X_Attribute18 VARCHAR2,
1022 X_Attribute19 VARCHAR2,
1023 X_Attribute20 VARCHAR2 ,
1024 X_Linked_Absence_id NUMBER,
1025 X_Sickness_Start_Date DATE,
1026 X_Sickness_End_Date DATE,
1027 X_Accept_Late_Notif_Flag VARCHAR2,
1028 x_reason_for_late_notification VARCHAR2,
1029 X_Pregnancy_Related_Illness VARCHAR2,
1030 X_Maternity_Id NUMBER,
1031 X_Abs_Information_Category VARCHAR2,
1032 X_Abs_Information1 VARCHAR2,
1033 X_Abs_Information2 VARCHAR2,
1034 X_Abs_Information3 VARCHAR2,
1035 X_Abs_Information4 VARCHAR2,
1036 X_Abs_Information5 VARCHAR2,
1037 X_Abs_Information6 VARCHAR2,
1038 X_Abs_Information7 VARCHAR2,
1039 X_Abs_Information8 VARCHAR2,
1040 X_Abs_Information9 VARCHAR2,
1041 X_Abs_Information10 VARCHAR2,
1042 X_Abs_Information11 VARCHAR2,
1043 X_Abs_Information12 VARCHAR2,
1044 X_Abs_Information13 VARCHAR2,
1045 X_Abs_Information14 VARCHAR2,
1046 X_Abs_Information15 VARCHAR2,
1047 X_Abs_Information16 VARCHAR2,
1048 X_Abs_Information17 VARCHAR2,
1049 X_Abs_Information18 VARCHAR2,
1050 X_Abs_Information19 VARCHAR2,
1051 X_Abs_Information20 VARCHAR2,
1052 X_Abs_Information21 VARCHAR2,
1053 X_Abs_Information22 VARCHAR2,
1054 X_Abs_Information23 VARCHAR2,
1055 X_Abs_Information24 VARCHAR2,
1056 X_Abs_Information25 VARCHAR2,
1057 X_Abs_Information26 VARCHAR2,
1058 X_Abs_Information27 VARCHAR2,
1059 X_Abs_Information28 VARCHAR2,
1060 X_Abs_Information29 VARCHAR2,
1061 X_Abs_Information30 VARCHAR2
1062 ) IS
1063 CURSOR C IS
1064 SELECT *
1065 FROM per_absence_attendances
1066 WHERE rowid = chartorowid(X_Rowid)
1067 FOR UPDATE of absence_attendance_id NOWAIT;
1068
1069 Recinfo C%ROWTYPE;
1070 BEGIN
1071 OPEN C;
1072 FETCH C INTO Recinfo;
1073 if (C%NOTFOUND) then
1074 CLOSE C;
1075 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1076 hr_utility.set_message_token('PROCEDURE', 'lock_row');
1077 hr_utility.set_message_token('STEP', '1');
1078 hr_utility.raise_error;
1079 end if;
1080 CLOSE C;
1081
1082 recinfo.comments := rtrim(recinfo.comments);
1083 recinfo.ssp1_issued := rtrim(recinfo.ssp1_issued);
1084 recinfo.time_end := rtrim(recinfo.time_end);
1085 recinfo.time_projected_end := rtrim(recinfo.time_projected_end);
1086 recinfo.time_projected_start := rtrim(recinfo.time_projected_start);
1087 recinfo.time_start := rtrim(recinfo.time_start);
1088 recinfo.attribute_category := rtrim(recinfo.attribute_category);
1089 recinfo.attribute1 := rtrim(recinfo.attribute1);
1090 recinfo.attribute2 := rtrim(recinfo.attribute2);
1091 recinfo.attribute3 := rtrim(recinfo.attribute3);
1092 recinfo.attribute4 := rtrim(recinfo.attribute4);
1093 recinfo.attribute5 := rtrim(recinfo.attribute5);
1094 recinfo.attribute6 := rtrim(recinfo.attribute6);
1095 recinfo.attribute7 := rtrim(recinfo.attribute7);
1096 recinfo.attribute8 := rtrim(recinfo.attribute8);
1097 recinfo.attribute9 := rtrim(recinfo.attribute9);
1098 recinfo.attribute10 := rtrim(recinfo.attribute10);
1099 recinfo.attribute11 := rtrim(recinfo.attribute11);
1100 recinfo.attribute12 := rtrim(recinfo.attribute12);
1101 recinfo.attribute13 := rtrim(recinfo.attribute13);
1102 recinfo.attribute14 := rtrim(recinfo.attribute14);
1103 recinfo.attribute15 := rtrim(recinfo.attribute15);
1104 recinfo.attribute16 := rtrim(recinfo.attribute16);
1105 recinfo.attribute17 := rtrim(recinfo.attribute17);
1106 recinfo.attribute18 := rtrim(recinfo.attribute18);
1107 recinfo.attribute19 := rtrim(recinfo.attribute19);
1108 recinfo.attribute20 := rtrim(recinfo.attribute20);
1109 recinfo.abs_information_category := rtrim(recinfo.abs_information_category);
1110 recinfo.abs_information1 := rtrim(recinfo.abs_information1);
1111 recinfo.abs_information2 := rtrim(recinfo.abs_information2);
1112 recinfo.abs_information3 := rtrim(recinfo.abs_information3);
1113 recinfo.abs_information4 := rtrim(recinfo.abs_information4);
1114 recinfo.abs_information5 := rtrim(recinfo.abs_information5);
1115 recinfo.abs_information6 := rtrim(recinfo.abs_information6);
1116 recinfo.abs_information7 := rtrim(recinfo.abs_information7);
1117 recinfo.abs_information8 := rtrim(recinfo.abs_information8);
1118 recinfo.abs_information9 := rtrim(recinfo.abs_information9);
1119 recinfo.abs_information10 := rtrim(recinfo.abs_information10);
1120 recinfo.abs_information11 := rtrim(recinfo.abs_information11);
1121 recinfo.abs_information12 := rtrim(recinfo.abs_information12);
1122 recinfo.abs_information13 := rtrim(recinfo.abs_information13);
1123 recinfo.abs_information14 := rtrim(recinfo.abs_information14);
1124 recinfo.abs_information15 := rtrim(recinfo.abs_information15);
1125 recinfo.abs_information16 := rtrim(recinfo.abs_information16);
1126 recinfo.abs_information17 := rtrim(recinfo.abs_information17);
1127 recinfo.abs_information18 := rtrim(recinfo.abs_information18);
1128 recinfo.abs_information19 := rtrim(recinfo.abs_information19);
1129 recinfo.abs_information20 := rtrim(recinfo.abs_information20);
1130 recinfo.abs_information21 := rtrim(recinfo.abs_information21);
1131 recinfo.abs_information22 := rtrim(recinfo.abs_information22);
1132 recinfo.abs_information23 := rtrim(recinfo.abs_information23);
1133 recinfo.abs_information24 := rtrim(recinfo.abs_information24);
1134 recinfo.abs_information25 := rtrim(recinfo.abs_information25);
1135 recinfo.abs_information26 := rtrim(recinfo.abs_information26);
1136 recinfo.abs_information27 := rtrim(recinfo.abs_information27);
1137 recinfo.abs_information28 := rtrim(recinfo.abs_information28);
1138 recinfo.abs_information29 := rtrim(recinfo.abs_information29);
1139 recinfo.abs_information30 := rtrim(recinfo.abs_information30);
1140
1141 --
1142 -- SSP additions
1143 --
1144 recinfo.accept_late_notification_flag
1145 := rtrim(recinfo.accept_late_notification_flag);
1146 recinfo.reason_for_notification_delay
1147 := rtrim(recinfo.reason_for_notification_delay);
1148 recinfo.pregnancy_related_illness := rtrim(recinfo.pregnancy_related_illness);
1149
1150 --
1151 --
1152
1153 if (
1154 ( (Recinfo.absence_attendance_id = X_Absence_Attendance_Id)
1155 OR ( (Recinfo.absence_attendance_id IS NULL)
1156 AND (X_Absence_Attendance_Id IS NULL)))
1157 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
1158 OR ( (Recinfo.business_group_id IS NULL)
1159 AND (X_Business_Group_Id IS NULL)))
1160 AND ( (Recinfo.absence_attendance_type_id = X_Absence_Attendance_Type_Id)
1161 OR ( (Recinfo.absence_attendance_type_id IS NULL)
1162 AND (X_Absence_Attendance_Type_Id IS NULL)))
1163 AND ( (Recinfo.abs_attendance_reason_id = X_Abs_Attendance_Reason_Id)
1164 OR ( (Recinfo.abs_attendance_reason_id IS NULL)
1165 AND (X_Abs_Attendance_Reason_Id IS NULL)))
1166 AND ( (Recinfo.person_id = X_Person_Id)
1167 OR ( (Recinfo.person_id IS NULL)
1168 AND (X_Person_Id IS NULL)))
1169 AND ( (Recinfo.authorising_person_id = X_Authorising_Person_Id)
1170 OR ( (Recinfo.authorising_person_id IS NULL)
1171 AND (X_Authorising_Person_Id IS NULL)))
1172 AND ( (Recinfo.replacement_person_id = X_Replacement_Person_Id)
1173 OR ( (Recinfo.replacement_person_id IS NULL)
1174 AND (X_Replacement_Person_Id IS NULL)))
1175 AND ( (Recinfo.period_of_incapacity_id = X_Period_Of_Incapacity_Id)
1176 OR ( (Recinfo.period_of_incapacity_id IS NULL)
1177 AND (X_Period_Of_Incapacity_Id IS NULL)))
1178 AND ( (Recinfo.absence_days = X_Absence_Days)
1179 OR ( (Recinfo.absence_days IS NULL)
1180 AND (X_Absence_Days IS NULL)))
1181 AND ( (Recinfo.absence_hours = X_Absence_Hours)
1182 OR ( (Recinfo.absence_hours IS NULL)
1183 AND (X_Absence_Hours IS NULL)))
1184 AND ( (Recinfo.comments = X_Comments)
1185 OR ( (Recinfo.comments IS NULL)
1186 AND (X_Comments IS NULL)))
1187 AND ( (Recinfo.date_end = X_Date_End)
1188 OR ( (Recinfo.date_end IS NULL)
1189 AND (X_Date_End IS NULL)))
1190 AND ( (Recinfo.date_notification = X_Date_Notification)
1191 OR ( (Recinfo.date_notification IS NULL)
1192 AND (X_Date_Notification IS NULL)))
1193 AND ( (Recinfo.date_projected_end = X_Date_Projected_End)
1194 OR ( (Recinfo.date_projected_end IS NULL)
1195 AND (X_Date_Projected_End IS NULL)))
1196 AND ( (Recinfo.date_projected_start = X_Date_Projected_Start)
1197 OR ( (Recinfo.date_projected_start IS NULL)
1198 AND (X_Date_Projected_Start IS NULL)))
1199 AND ( (Recinfo.date_start = X_Date_Start)
1200 OR ( (Recinfo.date_start IS NULL)
1201 AND (X_Date_Start IS NULL)))
1202 AND ( (Recinfo.occurrence = X_Occurrence)
1203 OR ( (Recinfo.occurrence IS NULL)
1204 AND (X_Occurrence IS NULL)))
1205 AND ( (Recinfo.ssp1_issued = X_Ssp1_Issued)
1206 OR ( (Recinfo.ssp1_issued IS NULL)
1207 AND (X_Ssp1_Issued IS NULL)))
1208 AND ( (Recinfo.time_end = X_Time_End)
1209 OR ( (Recinfo.time_end IS NULL)
1210 AND (X_Time_End IS NULL)))
1211 AND ( (Recinfo.time_projected_end = X_Time_Projected_End)
1212 OR ( (Recinfo.time_projected_end IS NULL)
1213 AND (X_Time_Projected_End IS NULL)))
1214 AND ( (Recinfo.time_projected_start = X_Time_Projected_Start)
1215 OR ( (Recinfo.time_projected_start IS NULL)
1216 AND (X_Time_Projected_Start IS NULL)))
1217 AND ( (Recinfo.time_start = X_Time_Start)
1218 OR ( (Recinfo.time_start IS NULL)
1219 AND (X_Time_Start IS NULL)))
1220 AND ( (Recinfo.attribute_category = X_Attribute_Category)
1221 OR ( (Recinfo.attribute_category IS NULL)
1222 AND (X_Attribute_Category IS NULL)))
1223 AND ( (Recinfo.attribute1 = X_Attribute1)
1224 OR ( (Recinfo.attribute1 IS NULL)
1225 AND (X_Attribute1 IS NULL)))
1226 AND ( (Recinfo.attribute2 = X_Attribute2)
1227 OR ( (Recinfo.attribute2 IS NULL)
1228 AND (X_Attribute2 IS NULL)))
1229 AND ( (Recinfo.attribute3 = X_Attribute3)
1230 OR ( (Recinfo.attribute3 IS NULL)
1231 AND (X_Attribute3 IS NULL)))
1232 AND ( (Recinfo.attribute4 = X_Attribute4)
1233 OR ( (Recinfo.attribute4 IS NULL)
1234 AND (X_Attribute4 IS NULL)))
1235 AND ( (Recinfo.attribute5 = X_Attribute5)
1236 OR ( (Recinfo.attribute5 IS NULL)
1237 AND (X_Attribute5 IS NULL)))
1238 AND ( (Recinfo.attribute6 = X_Attribute6)
1239 OR ( (Recinfo.attribute6 IS NULL)
1240 AND (X_Attribute6 IS NULL)))
1241 AND ( (Recinfo.attribute7 = X_Attribute7)
1242 OR ( (Recinfo.attribute7 IS NULL)
1243 AND (X_Attribute7 IS NULL)))
1244 AND ( (Recinfo.attribute8 = X_Attribute8)
1245 OR ( (Recinfo.attribute8 IS NULL)
1246 AND (X_Attribute8 IS NULL)))
1247 AND ( (Recinfo.attribute9 = X_Attribute9)
1248 OR ( (Recinfo.attribute9 IS NULL)
1249 AND (X_Attribute9 IS NULL)))
1250 AND ( (Recinfo.attribute10 = X_Attribute10)
1251 OR ( (Recinfo.attribute10 IS NULL)
1252 AND (X_Attribute10 IS NULL)))
1253 AND ( (Recinfo.attribute11 = X_Attribute11)
1254 OR ( (Recinfo.attribute11 IS NULL)
1255 AND (X_Attribute11 IS NULL)))
1256 AND ( (Recinfo.attribute12 = X_Attribute12)
1257 OR ( (Recinfo.attribute12 IS NULL)
1258 AND (X_Attribute12 IS NULL)))
1259 AND ( (Recinfo.attribute13 = X_Attribute13)
1260 OR ( (Recinfo.attribute13 IS NULL)
1261 AND (X_Attribute13 IS NULL)))
1262 AND ( (Recinfo.attribute14 = X_Attribute14)
1263 OR ( (Recinfo.attribute14 IS NULL)
1264 AND (X_Attribute14 IS NULL)))
1265 AND ( (Recinfo.attribute15 = X_Attribute15)
1266 OR ( (Recinfo.attribute15 IS NULL)
1267 AND (X_Attribute15 IS NULL)))
1268 AND ( (Recinfo.attribute16 = X_Attribute16)
1269 OR ( (Recinfo.attribute16 IS NULL)
1270 AND (X_Attribute16 IS NULL)))
1271 AND ( (Recinfo.attribute17 = X_Attribute17)
1272 OR ( (Recinfo.attribute17 IS NULL)
1273 AND (X_Attribute17 IS NULL)))
1274 AND ( (Recinfo.attribute18 = X_Attribute18)
1275 OR ( (Recinfo.attribute18 IS NULL)
1276 AND (X_Attribute18 IS NULL)))
1277 AND ( (Recinfo.attribute19 = X_Attribute19)
1278 OR ( (Recinfo.attribute19 IS NULL)
1279 AND (X_Attribute19 IS NULL)))
1280 AND ( (Recinfo.attribute20 = X_Attribute20)
1281 OR ( (Recinfo.attribute20 IS NULL)
1282 AND (X_Attribute20 IS NULL)))
1283 --
1284 -- SSP Columns added SS 2/10/1995
1285 --
1286
1287 AND ( (Recinfo.linked_absence_id = X_linked_absence_id)
1288 OR ( (Recinfo.linked_absence_id IS NULL)
1289 AND (X_linked_absence_id IS NULL)))
1290 AND ( (Recinfo.sickness_start_date = X_sickness_start_date)
1291 OR ( (Recinfo.sickness_start_date IS NULL)
1292 AND (X_sickness_start_date IS NULL)))
1293 AND ( (Recinfo.sickness_end_date = X_sickness_end_date)
1294 OR ( (Recinfo.sickness_end_date IS NULL)
1295 AND (X_sickness_end_date IS NULL)))
1296 AND ( (Recinfo.accept_late_notification_flag = X_accept_late_notif_flag)
1297 OR ( (Recinfo.accept_late_notification_flag IS NULL)
1298 AND (X_accept_late_notif_flag IS NULL)))
1299 AND ((Recinfo.reason_for_notification_delay=x_reason_for_late_notification)
1300 OR ( (Recinfo.reason_for_notification_delay IS NULL)
1301 AND (x_reason_for_late_notification IS NULL)))
1302 AND ( (Recinfo.pregnancy_related_illness = X_pregnancy_related_illness)
1303 OR ( (Recinfo.pregnancy_related_illness IS NULL)
1304 AND (X_pregnancy_related_illness IS NULL)))
1305 AND ( (Recinfo.maternity_id = X_maternity_id)
1306 OR ( (Recinfo.maternity_id IS NULL)
1307 AND (X_maternity_id IS NULL)))
1308 AND ( (Recinfo.abs_information_category = X_Abs_Information_Category)
1309 OR ( (Recinfo.abs_information_category IS NULL)
1310 AND (X_Abs_Information_Category IS NULL)))
1311 AND ( (Recinfo.abs_information1 = X_Abs_Information1)
1312 OR ( (Recinfo.abs_information1 IS NULL)
1313 AND (X_Abs_Information1 IS NULL)))
1314 AND ( (Recinfo.abs_information2 = X_Abs_Information2)
1315 OR ( (Recinfo.abs_information2 IS NULL)
1316 AND (X_Abs_Information2 IS NULL)))
1317 AND ( (Recinfo.abs_information3 = X_Abs_Information3)
1318 OR ( (Recinfo.abs_information3 IS NULL)
1319 AND (X_Abs_Information3 IS NULL)))
1320 AND ( (Recinfo.abs_information4 = X_Abs_Information4)
1321 OR ( (Recinfo.abs_information4 IS NULL)
1322 AND (X_Abs_Information4 IS NULL)))
1323 AND ( (Recinfo.abs_information5 = X_Abs_Information5)
1324 OR ( (Recinfo.abs_information5 IS NULL)
1325 AND (X_Abs_Information5 IS NULL)))
1326 AND ( (Recinfo.abs_information6 = X_Abs_Information6)
1327 OR ( (Recinfo.abs_information6 IS NULL)
1328 AND (X_Abs_Information6 IS NULL)))
1329 AND ( (Recinfo.abs_information7 = X_Abs_Information7)
1330 OR ( (Recinfo.abs_information7 IS NULL)
1331 AND (X_Abs_Information7 IS NULL)))
1332 AND ( (Recinfo.abs_information8 = X_Abs_Information8)
1333 OR ( (Recinfo.abs_information8 IS NULL)
1334 AND (X_Abs_Information8 IS NULL)))
1335 AND ( (Recinfo.abs_information9 = X_Abs_Information9)
1336 OR ( (Recinfo.abs_information9 IS NULL)
1337 AND (X_Abs_Information9 IS NULL)))
1338 AND ( (Recinfo.abs_information10 = X_Abs_Information10)
1339 OR ( (Recinfo.abs_information10 IS NULL)
1340 AND (X_Abs_Information10 IS NULL)))
1341 AND ( (Recinfo.abs_information11 = X_Abs_Information11)
1342 OR ( (Recinfo.abs_information11 IS NULL)
1343 AND (X_Abs_Information11 IS NULL)))
1344 AND ( (Recinfo.abs_information12 = X_Abs_Information12)
1345 OR ( (Recinfo.abs_information12 IS NULL)
1346 AND (X_Abs_Information12 IS NULL)))
1347 AND ( (Recinfo.abs_information13 = X_Abs_Information13)
1348 OR ( (Recinfo.abs_information13 IS NULL)
1349 AND (X_Abs_Information13 IS NULL)))
1350 AND ( (Recinfo.abs_information14 = X_Abs_Information14)
1351 OR ( (Recinfo.abs_information14 IS NULL)
1352 AND (X_Abs_Information14 IS NULL)))
1353 AND ( (Recinfo.abs_information15 = X_Abs_Information15)
1354 OR ( (Recinfo.abs_information15 IS NULL)
1355 AND (X_Abs_Information15 IS NULL)))
1356 AND ( (Recinfo.abs_information16 = X_Abs_Information16)
1357 OR ( (Recinfo.abs_information16 IS NULL)
1358 AND (X_Abs_Information16 IS NULL)))
1359 AND ( (Recinfo.abs_information17 = X_Abs_Information17)
1360 OR ( (Recinfo.abs_information17 IS NULL)
1361 AND (X_Abs_Information17 IS NULL)))
1362 AND ( (Recinfo.abs_information18 = X_Abs_Information18)
1363 OR ( (Recinfo.abs_information18 IS NULL)
1364 AND (X_Abs_Information18 IS NULL)))
1365 AND ( (Recinfo.abs_information19 = X_Abs_Information19)
1366 OR ( (Recinfo.abs_information19 IS NULL)
1367 AND (X_Abs_Information19 IS NULL)))
1368 AND ( (Recinfo.abs_information20 = X_Abs_Information20)
1369 OR ( (Recinfo.abs_information20 IS NULL)
1370 AND (X_Abs_Information20 IS NULL)))
1371 AND ( (Recinfo.abs_information21 = X_Abs_Information21)
1372 OR ( (Recinfo.abs_information21 IS NULL)
1373 AND (X_Abs_Information21 IS NULL)))
1374 AND ( (Recinfo.abs_information22 = X_Abs_Information22)
1375 OR ( (Recinfo.abs_information22 IS NULL)
1376 AND (X_Abs_Information22 IS NULL)))
1377 AND ( (Recinfo.abs_information23 = X_Abs_Information23)
1378 OR ( (Recinfo.abs_information23 IS NULL)
1379 AND (X_Abs_Information23 IS NULL)))
1380 AND ( (Recinfo.abs_information24 = X_Abs_Information24)
1381 OR ( (Recinfo.abs_information24 IS NULL)
1382 AND (X_Abs_Information24 IS NULL)))
1383 AND ( (Recinfo.abs_information25 = X_Abs_Information25)
1384 OR ( (Recinfo.abs_information25 IS NULL)
1385 AND (X_Abs_Information25 IS NULL)))
1386 AND ( (Recinfo.abs_information26 = X_Abs_Information26)
1387 OR ( (Recinfo.abs_information26 IS NULL)
1388 AND (X_Abs_Information26 IS NULL)))
1389 AND ( (Recinfo.abs_information27 = X_Abs_Information27)
1390 OR ( (Recinfo.abs_information27 IS NULL)
1391 AND (X_Abs_Information27 IS NULL)))
1392 AND ( (Recinfo.abs_information28 = X_Abs_Information28)
1393 OR ( (Recinfo.abs_information28 IS NULL)
1394 AND (X_Abs_Information28 IS NULL)))
1395 AND ( (Recinfo.abs_information29 = X_Abs_Information29)
1396 OR ( (Recinfo.abs_information29 IS NULL)
1397 AND (X_Abs_Information29 IS NULL)))
1398 AND ( (Recinfo.abs_information30 = X_Abs_Information30)
1399 OR ( (Recinfo.abs_information30 IS NULL)
1400 AND (X_Abs_Information30 IS NULL)))
1401 )
1402 then
1403 return;
1404 else
1405 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1406 fnd_message.raise_error;
1407 end if;
1408 END Lock_Row;
1409
1410
1411
1412 PROCEDURE Update_Row(X_Rowid VARCHAR2,
1413 X_Absence_Attendance_Id NUMBER,
1414 X_Business_Group_Id NUMBER,
1415 X_Absence_Attendance_Type_Id NUMBER,
1416 X_Abs_Attendance_Reason_Id NUMBER,
1417 X_Person_Id NUMBER,
1418 X_Authorising_Person_Id NUMBER,
1419 X_Replacement_Person_Id NUMBER,
1420 X_Period_Of_Incapacity_Id NUMBER,
1421 X_Absence_Days NUMBER,
1422 X_Absence_Hours NUMBER,
1423 X_Comments VARCHAR2,
1424 X_Date_End DATE,
1425 X_Date_Notification DATE,
1426 X_Date_Projected_End DATE,
1427 X_Date_Projected_Start DATE,
1428 X_Date_Start DATE,
1429 X_Occurrence NUMBER,
1430 X_Ssp1_Issued VARCHAR2,
1431 X_Time_End VARCHAR2,
1432 X_Time_Projected_End VARCHAR2,
1433 X_Time_Projected_Start VARCHAR2,
1434 X_Time_Start VARCHAR2,
1435 X_Attribute_Category VARCHAR2,
1436 X_Attribute1 VARCHAR2,
1437 X_Attribute2 VARCHAR2,
1438 X_Attribute3 VARCHAR2,
1439 X_Attribute4 VARCHAR2,
1440 X_Attribute5 VARCHAR2,
1441 X_Attribute6 VARCHAR2,
1442 X_Attribute7 VARCHAR2,
1443 X_Attribute8 VARCHAR2,
1444 X_Attribute9 VARCHAR2,
1445 X_Attribute10 VARCHAR2,
1446 X_Attribute11 VARCHAR2,
1447 X_Attribute12 VARCHAR2,
1448 X_Attribute13 VARCHAR2,
1449 X_Attribute14 VARCHAR2,
1450 X_Attribute15 VARCHAR2,
1451 X_Attribute16 VARCHAR2,
1452 X_Attribute17 VARCHAR2,
1453 X_Attribute18 VARCHAR2,
1454 X_Attribute19 VARCHAR2,
1455 X_Attribute20 VARCHAR2 ,
1456 X_Linked_Absence_id NUMBER ,
1457 X_Sickness_Start_Date DATE,
1458 X_Sickness_End_Date DATE,
1459 X_Accept_Late_Notif_Flag VARCHAR2,
1460 x_reason_for_late_notification VARCHAR2,
1461 X_Pregnancy_Related_Illness VARCHAR2,
1462 X_Maternity_Id NUMBER,
1463 X_Abs_Information_Category VARCHAR2,
1464 X_Abs_Information1 VARCHAR2,
1465 X_Abs_Information2 VARCHAR2,
1466 X_Abs_Information3 VARCHAR2,
1467 X_Abs_Information4 VARCHAR2,
1468 X_Abs_Information5 VARCHAR2,
1469 X_Abs_Information6 VARCHAR2,
1470 X_Abs_Information7 VARCHAR2,
1471 X_Abs_Information8 VARCHAR2,
1472 X_Abs_Information9 VARCHAR2,
1473 X_Abs_Information10 VARCHAR2,
1474 X_Abs_Information11 VARCHAR2,
1475 X_Abs_Information12 VARCHAR2,
1476 X_Abs_Information13 VARCHAR2,
1477 X_Abs_Information14 VARCHAR2,
1478 X_Abs_Information15 VARCHAR2,
1479 X_Abs_Information16 VARCHAR2,
1480 X_Abs_Information17 VARCHAR2,
1481 X_Abs_Information18 VARCHAR2,
1482 X_Abs_Information19 VARCHAR2,
1483 X_Abs_Information20 VARCHAR2,
1484 X_Abs_Information21 VARCHAR2,
1485 X_Abs_Information22 VARCHAR2,
1486 X_Abs_Information23 VARCHAR2,
1487 X_Abs_Information24 VARCHAR2,
1488 X_Abs_Information25 VARCHAR2,
1489 X_Abs_Information26 VARCHAR2,
1490 X_Abs_Information27 VARCHAR2,
1491 X_Abs_Information28 VARCHAR2,
1492 X_Abs_Information29 VARCHAR2,
1493 X_Abs_Information30 VARCHAR2
1494 ) IS
1495
1496 cursor csr_absence is
1497 select *
1498 from per_absence_attendances
1499 where absence_attendance_id = X_absence_attendance_id;
1500 --
1501 l_absence csr_absence%rowtype;
1502 --
1503 --
1504 -- Start of BUGFIX for WWBUG 1408379
1505 --
1506 cursor c1 is
1507 select *
1508 from per_absence_attendances
1509 where rowid = chartorowid(X_rowid);
1510 --
1511 l_c1 c1%rowtype;
1512 l_rows_found boolean := false;
1513 --
1514 l_old ben_abs_ler.g_abs_ler_rec;
1515 l_new ben_abs_ler.g_abs_ler_rec;
1516 --
1517 -- End of BUGFIX for WWBUG 1408379
1518 --
1519 BEGIN
1520
1521 -- Check that maternity absence does not start before the QW
1522 check_val_abs_start (p_maternity_id => x_maternity_id,
1523 p_date_start => x_date_start);
1524 --
1525 --
1526 if X_sickness_start_date is not null then
1527 --
1528 -- We must be dealing with a sickness absence. Find the old values for the
1529 -- absence being updated (for use later).
1530 --
1531 open csr_absence;
1532 fetch csr_absence into l_absence;
1533 close csr_absence;
1534 --
1535 end if;
1536 --
1537 -- Start of BUGFIX for WWBUG 1408379
1538 --
1539 open c1;
1540 --
1541 fetch c1 into l_c1;
1542 if c1%found then
1543 --
1544 l_rows_found := true;
1545 --
1546 end if;
1547 --
1548 close c1;
1549 --
1550 -- End of BUGFIX for WWBUG 1408379
1551 --
1552 UPDATE per_absence_attendances
1553 SET
1554 absence_attendance_id = X_Absence_Attendance_Id,
1555 business_group_id = X_Business_Group_Id,
1556 absence_attendance_type_id = X_Absence_Attendance_Type_Id,
1557 abs_attendance_reason_id = X_Abs_Attendance_Reason_Id,
1558 person_id = X_Person_Id,
1559 authorising_person_id = X_Authorising_Person_Id,
1560 replacement_person_id = X_Replacement_Person_Id,
1561 period_of_incapacity_id = X_Period_Of_Incapacity_Id,
1562 absence_days = X_Absence_Days,
1563 absence_hours = X_Absence_Hours,
1564 comments = X_Comments,
1565 date_end = X_Date_End,
1566 date_notification = X_Date_Notification,
1567 date_projected_end = X_Date_Projected_End,
1568 date_projected_start = X_Date_Projected_Start,
1569 date_start = X_Date_Start,
1570 occurrence = X_Occurrence,
1571 ssp1_issued = X_Ssp1_Issued,
1572 time_end = X_Time_End,
1573 time_projected_end = X_Time_Projected_End,
1574 time_projected_start = X_Time_Projected_Start,
1575 time_start = X_Time_Start,
1576 attribute_category = X_Attribute_Category,
1577 attribute1 = X_Attribute1,
1578 attribute2 = X_Attribute2,
1579 attribute3 = X_Attribute3,
1580 attribute4 = X_Attribute4,
1581 attribute5 = X_Attribute5,
1582 attribute6 = X_Attribute6,
1583 attribute7 = X_Attribute7,
1584 attribute8 = X_Attribute8,
1585 attribute9 = X_Attribute9,
1586 attribute10 = X_Attribute10,
1587 attribute11 = X_Attribute11,
1588 attribute12 = X_Attribute12,
1589 attribute13 = X_Attribute13,
1590 attribute14 = X_Attribute14,
1591 attribute15 = X_Attribute15,
1592 attribute16 = X_Attribute16,
1593 attribute17 = X_Attribute17,
1594 attribute18 = X_Attribute18,
1595 attribute19 = X_Attribute19,
1596 attribute20 = X_Attribute20 ,
1597 Linked_Absence_id = X_Linked_Absence_Id,
1598 Sickness_Start_Date = X_Sickness_Start_Date,
1599 Sickness_End_Date = X_Sickness_End_Date,
1600 Accept_Late_Notification_Flag = X_Accept_Late_Notif_Flag,
1601 reason_for_notification_delay = x_reason_for_late_notification,
1602 Pregnancy_Related_Illness = X_Pregnancy_Related_Illness,
1603 Maternity_Id = X_Maternity_Id,
1604 Batch_Id = NULL,
1605 abs_information_category = X_Abs_Information_Category,
1606 abs_information1 = X_Abs_Information1,
1607 abs_information2 = X_Abs_Information2,
1608 abs_information3 = X_Abs_Information3,
1609 abs_information4 = X_Abs_Information4,
1610 abs_information5 = X_Abs_Information5,
1611 abs_information6 = X_Abs_Information6,
1612 abs_information7 = X_Abs_Information7,
1613 abs_information8 = X_Abs_Information8,
1614 abs_information9 = X_Abs_Information9,
1615 abs_information10 = X_Abs_Information10,
1616 abs_information11 = X_Abs_Information11,
1617 abs_information12 = X_Abs_Information12,
1618 abs_information13 = X_Abs_Information13,
1619 abs_information14 = X_Abs_Information14,
1620 abs_information15 = X_Abs_Information15,
1621 abs_information16 = X_Abs_Information16,
1622 abs_information17 = X_Abs_Information17,
1623 abs_information18 = X_Abs_Information18,
1624 abs_information19 = X_Abs_Information19,
1625 abs_information20 = X_Abs_Information20,
1626 abs_information21 = X_Abs_Information21,
1627 abs_information22 = X_Abs_Information22,
1628 abs_information23 = X_Abs_Information23,
1629 abs_information24 = X_Abs_Information24,
1630 abs_information25 = X_Abs_Information25,
1631 abs_information26 = X_Abs_Information26,
1632 abs_information27 = X_Abs_Information27,
1633 abs_information28 = X_Abs_Information28,
1634 abs_information29 = X_Abs_Information29,
1635 abs_information30 = X_Abs_Information30
1636 WHERE rowid = chartorowid(X_rowid);
1637 --
1638 -- Start of BUGFIX for WWBUG 1408379
1639 --
1640 if l_rows_found then
1641 --
1642 populate_ben_absence_rec
1643 (p_absence_attendance_id => x_absence_attendance_id,
1644 p_rec_type => 'O',
1645 p_old_rec => l_c1,
1646 p_ben_rec => l_old);
1647
1648 populate_ben_absence_rec
1649 (p_absence_attendance_id => x_absence_attendance_id,
1650 p_rec_type => 'N',
1651 p_old_rec => null,
1652 p_ben_rec => l_new);
1653
1654 ben_abs_ler.ler_chk(p_old => l_old,
1655 p_new => l_new,
1656 p_effective_date => trunc(nvl(l_new.date_start,sysdate)));
1657
1658 --
1659 end if;
1660 --
1661 -- End of BUGFIX for WWBUG 1408379
1662 --
1663 --
1664 -- SSP specific code to check the effect of the update of sickness dates.
1665 --
1666 if X_sickness_start_date is not null
1667 and (l_absence.sickness_start_date <> X_sickness_start_date
1668 or nvl (l_absence.sickness_end_date,
1669 hr_general.end_of_time) <> nvl (X_sickness_end_date,
1670 hr_general.end_of_time) )
1671 then
1672 --
1673 -- The sickness dates are being updated. Make sure that the PIW series is
1674 -- not affected as a result of the update.
1675 --
1676 ssp_ssp_pkg.check_sickness_date_change (
1677 --
1678 p_person_id => l_absence.person_id,
1679 p_linked_absence_id => l_absence.linked_absence_id,
1680 p_absence_attendance_id => l_absence.absence_attendance_id,
1681 p_old_sickness_end_date => l_absence.sickness_end_date,
1682 p_new_sickness_start_date => X_sickness_start_date,
1683 p_new_sickness_end_date => X_sickness_end_date);
1684 --
1685 end if;
1686 --
1687 -- SSP users only:
1688 -- If the MPP has not started and the absence is maternity leave, update
1689 -- the MPP start date
1690 --
1691 default_MPP_start_date (p_maternity_id => x_maternity_id,
1692 p_start_date => x_date_start,
1693 p_end_date => x_date_end);
1694 --
1695 Exception
1696 When hr_api.check_integrity_violated Then
1697 -- A check constraint has been violated
1698 PER_ABSENCE_ATTENDANCES_PKG.constraint_error
1699 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
1700 When hr_api.parent_integrity_violated Then
1701 -- Parent integrity has been violated
1702 PER_ABSENCE_ATTENDANCES_PKG.constraint_error
1703 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
1704 When hr_api.unique_integrity_violated Then
1705 -- Unique integrity has been violated
1706 PER_ABSENCE_ATTENDANCES_PKG.constraint_error
1707 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
1708 When Others Then
1709 Raise;
1710 END Update_Row;
1711
1712
1713
1714 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
1715 --
1716 cursor csr_current_absence is
1717 select *
1718 from per_absence_attendances
1719 where rowid = X_ROWID;
1720 --
1721 l_absence csr_current_absence%rowtype;
1722 l_old ben_abs_ler.g_abs_ler_rec;
1723 l_new ben_abs_ler.g_abs_ler_rec;
1724 --
1725 BEGIN
1726 --
1727 open csr_current_absence;
1728 fetch csr_current_absence into l_absence;
1729 close csr_current_absence;
1730 --
1731 if l_absence.sickness_start_date is not null then
1732 --
1733 -- This is a sickness absence so check that deletion will not interrupt a
1734 -- PIW series.
1735 --
1736 ssp_ssp_pkg.check_for_break_in_linked_PIW (
1737 --
1738 p_sickness_start_date => l_absence.sickness_start_date,
1739 p_sickness_end_date => l_absence.sickness_end_date,
1740 p_linked_absence_id => l_absence.linked_absence_id,
1741 p_absence_attendance_id => l_absence.absence_attendance_id);
1742 --
1743 end if;
1744 --
1745 DELETE FROM per_absence_attendances
1746 WHERE rowid = chartorowid(X_Rowid);
1747
1748 if (SQL%NOTFOUND) then
1749 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1750 hr_utility.set_message_token('PROCEDURE', 'delete_row');
1751 hr_utility.set_message_token('STEP', '1');
1752 hr_utility.raise_error;
1753 end if;
1754
1755 populate_ben_absence_rec
1756 (p_absence_attendance_id => l_absence.absence_attendance_id,
1757 p_rec_type => 'O',
1758 p_old_rec => l_absence,
1759 p_ben_rec => l_old);
1760
1761 ben_abs_ler.ler_chk(p_old => l_old,
1762 p_new => l_new,
1763 p_effective_date => trunc(sysdate));
1764 END Delete_Row;
1765
1766 PROCEDURE Delete_Row(X_Rowid VARCHAR2, p_maternity_id in number) IS
1767 --
1768 begin
1769 --
1770 Delete_Row(X_Rowid);
1771 reset_MPP_start_date_on_delete (p_maternity_id);
1772 --
1773 END Delete_Row;
1774
1775
1776 procedure b_check_auth_de(p_date_start IN DATE,
1777 p_proj_start IN DATE,
1778 p_date_end IN DATE,
1779 p_proj_end IN DATE,
1780 p_sess In DATE,
1781 p_auth_id IN NUMBER) is
1782 l_exists VARCHAR2(1);
1783
1784 cursor c1 is
1785 select 'x'
1786 from per_all_people_f pp
1787 where nvl(p_date_start,nvl(p_proj_start,p_sess)) >=
1788 (select min(ppf.effective_start_date)
1789 from per_all_people_f ppf
1790 where pp.person_id = ppf.person_id
1791 and pp.current_employee_flag = ppf.current_employee_flag)
1792 and nvl(p_date_end,nvl(p_proj_end,p_sess)) <=
1793 (select max(ppf.effective_end_date)
1794 from per_all_people_f ppf
1795 where pp.person_id = ppf.person_id
1796 and pp.current_employee_flag = ppf.current_employee_flag)
1797 -- where nvl(p_date_start,nvl(p_proj_start,p_sess)) between
1798 -- pp.effective_start_date and pp.effective_end_date
1799 -- and nvl(p_date_end,nvl(p_proj_end,p_sess)) between
1800 -- pp.effective_start_date and pp.effective_end_date
1801 and pp.person_id = p_auth_id
1802 and pp.current_employee_flag = 'Y';
1803 --
1804 begin
1805 --
1806 hr_utility.set_location('per_absence_attendances_pkg.b_check_auth_de',1);
1807 --
1808 open c1;
1809 --
1810 fetch c1 into l_exists;
1811 IF c1%notfound THEN
1812 hr_utility.set_message(801, 'PER_7886_EMP_ABS_DURATION');
1813 close c1;
1814 hr_utility.raise_error;
1815 END IF;
1816 --
1817 close c1;
1818 --
1819 end b_check_auth_de;
1820
1821
1822 procedure b_check_rep_de(p_date_start In DATE,
1823 p_date_proj_start IN DATE,
1824 p_date_end IN DATE,
1825 p_date_proj_end IN DATE,
1826 p_sess IN DATE,
1827 p_rep_id IN NUMBER) is
1828 l_exists VARCHAR2(1);
1829
1830 cursor c3 is
1831 select 'x'
1832 from per_all_people_f pp
1833 where nvl(p_date_start,nvl(p_date_proj_start,p_sess)) >=
1834 (select min(ppf.effective_start_date)
1835 from per_all_people_f ppf
1836 where pp.person_id = ppf.person_id
1837 and pp.current_employee_flag = ppf.current_employee_flag)
1838 and nvl(p_date_end,nvl(p_date_proj_end,p_sess)) <=
1839 (select max(ppf.effective_end_date)
1840 from per_all_people_f ppf
1841 where pp.person_id = ppf.person_id
1842 and pp.current_employee_flag = ppf.current_employee_flag)
1843 -- where nvl(p_date_start,nvl(p_date_proj_start,p_sess)) between
1844 -- pp.effective_start_date and pp.effective_end_date
1845 -- and nvl(p_date_end,nvl(p_date_proj_end,p_sess)) between
1846 -- pp.effective_start_date and pp.effective_end_date
1847 and pp.person_id = p_rep_id
1848 and pp.current_employee_flag = 'Y';
1849 --
1850 begin
1851 --
1852 hr_utility.set_location('per_absence_attendances_pkg.b_check_rep_de',1);
1853 --
1854 open c3;
1855 --
1856 fetch c3 into l_exists;
1857 IF c3%notfound THEN
1858 hr_utility.set_message(801, 'PER_7887_EMP_ABS_DURATION');
1859 close c3;
1860 hr_utility.raise_error;
1861 END IF;
1862 --
1863 close c3;
1864 --
1865 end b_check_rep_de;
1866
1867
1868 function check_for_term(p_date IN DATE,
1869 p_rep_per IN NUMBER)
1870 RETURN BOOLEAN IS
1871 l_ret_val BOOLEAN;
1872
1873 cursor c2 is
1874 select 'x'
1875 from per_periods_of_service p
1876 where ((trunc(p.actual_termination_date) < p_date
1877 and p.actual_termination_date is not null)
1878 or (trunc(p.projected_termination_date) < p_date
1879 and p.projected_termination_date is not null))
1880 and p.person_id = p_rep_per;
1881 --
1882 begin
1883 --
1884 hr_utility.set_location('per_absence_attendances_pkg.b_check_for_termination',1);
1885 --
1886 open c2;
1887 --
1888 l_ret_val := c2%found;
1889 --
1890 close c2;
1891 --
1892 RETURN l_ret_val;
1893 --
1894 end check_for_term;
1895
1896
1897
1898 function chk_rep_req(p_psn_id IN NUMBER,
1899 p_dstart IN DATE,
1900 p_proj_start IN DATE,
1901 p_sess IN DATE)
1902 RETURN BOOLEAN is
1903 l_exists number;
1904 l_ret_val BOOLEAN;
1905 --
1906 -- Changed 12-Oct-99 SCNair (per_positions to hr_positions) Date tracked position req.
1907 --
1908 cursor c4 is
1909 select 1
1910 from hr_positions_f pp,
1911 per_assignments_f asg
1912 where asg.person_id = p_psn_id
1913 and nvl(p_dstart,nvl(p_proj_start,p_sess)) between
1914 asg.effective_start_date and asg.effective_end_date
1915 and pp.position_id = asg.position_id
1916 and nvl(p_dstart,nvl(p_proj_start,p_sess)) between
1917 pp.effective_start_date and pp.effective_end_date
1918 and pp.replacement_required_flag = 'Y';
1919 --
1920 begin
1921 --
1922 hr_utility.set_location('per_absence_attendances_pkg.chk_rep_req',1);
1923 --
1924 open c4;
1925 fetch c4 into l_exists;
1926 --
1927 IF c4%found THEN
1928 l_ret_val := true;
1929 ELSE l_ret_val := false;
1930 END IF;
1931 --
1932 close c4;
1933 --
1934 RETURN (l_ret_val);
1935 --
1936 end chk_rep_req;
1937
1938
1939 procedure chk_type(p_abat_type In NUMBER,
1940 p_dstart IN DATE,
1941 p_eot IN DATE,
1942 p_dend IN DATE,
1943 p_abs_from IN DATE,
1944 p_abs_to IN DATE) is
1945 l_exists VARCHAR2(1);
1946
1947 cursor c5 is
1948 select 'x'
1949 from per_absence_attendance_types
1950 where absence_attendance_type_id = p_abat_type
1951 and date_effective <= p_dstart
1952 and (nvl(date_end,p_eot) >= p_dend
1953 or p_dend is null);
1954 --
1955 begin
1956 --
1957 hr_utility.set_location('per_absence_attendances_pkg.chk_type',1);
1958 --
1959 open c5;
1960 --
1961 fetch c5 into l_exists;
1962 IF c5%notfound THEN
1963 hr_utility.set_message(801, 'HR_6847_ABS_DET_RANGE_CHECK');
1964 hr_utility.set_message_token('ABS_DATE_FROM', to_char(p_abs_from,'DD-MON-YYYY'));
1965 hr_utility.set_message_token('ABS_DATE_TO', to_char(p_abs_to,'DD-MON-YYYY'));
1966 close c5;
1967 hr_utility.raise_error;
1968 END IF;
1969 --
1970 close c5;
1971 --
1972 end chk_type;
1973
1974
1975 procedure chk_proj(p_abat_type IN NUMBER,
1976 p_proj_start IN DATE,
1977 p_eot IN DATE,
1978 p_proj_end IN DATE) is
1979 l_exists VARCHAR2(1);
1980
1981 cursor c6 is
1982 select 'x'
1983 from per_absence_attendance_types
1984 where absence_attendance_type_id = p_abat_type
1985 and date_effective <= p_proj_start
1986 and (nvl(date_end,p_eot) >= p_proj_end
1987 or p_proj_end is null);
1988 --
1989 begin
1990 --
1991 hr_utility.set_location('per_absence_attendances_pkg.chk_proj',1);
1992 --
1993 open c6;
1994 --
1995 fetch c6 into l_exists;
1996 IF c6%notfound THEN
1997 hr_utility.set_message(801, 'HR_6457_ABS_DET_DATES');
1998 close c6;
1999 hr_utility.raise_error;
2000 END IF;
2001 --
2002 close c6;
2003 --
2004 end chk_proj;
2005
2006
2007
2008 function chkab1(p_abat_id IN NUMBER,
2009 p_per_id IN NUMBER,
2010 p_abat_type In NUMBER,
2011 p_dstart IN DATE)
2012 RETURN BOOLEAN is
2013 l_exists number;
2014 l_ret_val BOOLEAN;
2015
2016 cursor c7 is
2017 select 1
2018 from per_absence_attendances t,
2019 per_absence_attendance_types a,
2020 per_absence_attendance_types b
2021 where (p_abat_id is null
2022 or (p_abat_id is not null and p_abat_id <> t.absence_attendance_id))
2023 and t.person_id = p_per_id
2024 and p_abat_type = a.absence_attendance_type_id
2025 and t.absence_attendance_type_id = b.absence_attendance_type_id
2026 and a.absence_category = 'S'
2027 and b.absence_category = 'S'
2028 and t.date_end = p_dstart -1;
2029 --
2030 begin
2031 --
2032 hr_utility.set_location('per_absence_attendances_pkg.chkab1',1);
2033 --
2034 open c7;
2035 fetch c7 into l_exists;
2036 --
2037 IF c7%found THEN
2038 l_ret_val := true;
2039 ELSE l_ret_val := false;
2040 END IF;
2041 --
2042 close c7;
2043 --
2044 RETURN (l_ret_val);
2045 --
2046 end chkab1;
2047
2048
2049
2050 procedure chkab2(p_abat_id IN NUMBER,
2051 p_per_id IN NUMBER,
2052 p_abat_type IN NUMBER,
2053 p_dstart IN DATE,
2054 p_dend IN DATE,
2055 p_eot IN DATE) is
2056 l_exists VARCHAR2(1);
2057 l_exists2 VARCHAR2(1);
2058
2059 cursor c8 is
2060 select 'x'
2061 from per_absence_attendances t,
2062 per_absence_attendance_types a,
2063 per_absence_attendance_types b
2064 where (p_abat_id is null
2065 or (p_abat_id is not null and p_abat_id <> t.absence_attendance_id))
2066 and t.person_id = p_per_id
2067 and p_abat_type = a.absence_attendance_type_id
2068 and t.absence_attendance_type_id = b.absence_attendance_type_id
2069 and a.absence_category = 'S'
2070 and b.absence_category = 'S'
2071 and p_dend is null
2072 and t.date_end is null;
2073 --
2074 cursor c9 is
2075 select 'x'
2076 from per_absence_attendances t,
2077 per_absence_attendance_types a,
2078 per_absence_attendance_types b
2079 where (p_abat_id is null
2080 or (p_abat_id is not null and p_abat_id <> t.absence_attendance_id))
2081 and t.person_id = p_per_id
2082 and t.absence_attendance_type_id = a.absence_attendance_type_id
2083 and a.absence_category = 'S'
2084 and p_abat_type = b.absence_attendance_type_id
2085 and b.absence_category = 'S'
2086 and ((p_dstart between t.date_start and nvl(t.date_end,p_eot))
2087 or (t.date_start between p_dstart and nvl(p_dend,p_eot)));
2088 --
2089 begin
2090 --
2091 hr_utility.set_location('per_absence_attendances_pkg.chkab2',1);
2092 --
2093 open c8;
2094 --
2095 fetch c8 into l_exists;
2096 IF c8%found THEN
2097 hr_utility.set_message(801, 'SSP_35217_DEF_ONLY_ONE_ABS');
2098 close c8;
2099 hr_utility.raise_error;
2100 END IF;
2101 --
2102 close c8;
2103 --
2104 hr_utility.set_location('per_absence_attendances_pkg.chkab2',2);
2105 --
2106 open c9;
2107 --
2108 fetch c9 into l_exists2;
2109 IF c9%found THEN
2110 hr_utility.set_message(801, 'SSP_35216_DEF_OVERLAP_ABS');
2111 close c9;
2112 hr_utility.raise_error;
2113 END IF;
2114 --
2115 close c9;
2116 --
2117 end chkab2;
2118
2119
2120
2121 function chkab3(p_abat_id IN NUMBER,
2122 p_per_id IN NUMBER,
2123 p_abat_type IN NUMBER,
2124 p_dstart IN DATE,
2125 p_dend In DATE,
2126 p_eot IN DATE)
2127 RETURN BOOLEAN is
2128 l_exists number;
2129 l_ret_val BOOLEAN;
2130
2131 cursor c10 is
2132 select 1
2133 from per_absence_attendances t,
2134 per_absence_attendance_types a,
2135 per_absence_attendance_types b
2136 where (p_abat_id is null
2137 or (p_abat_id is not null and p_abat_id <> t.absence_attendance_id))
2138 and t.person_id = p_per_id
2139 and t.absence_attendance_type_id = a.absence_attendance_type_id
2140 and p_abat_type = b.absence_attendance_type_id
2141 and ((p_dstart between t.date_start and nvl(t.date_end,p_eot))
2142 or (t.date_start between p_dstart and nvl(p_dend,p_eot)));
2143 --
2144 begin
2145 --
2146 hr_utility.set_location('per_absence_attendances_pkg.chkab3',1);
2147 --
2148 open c10;
2149 fetch c10 into l_exists;
2150 --
2151 IF c10%found THEN
2152 l_ret_val := true;
2153 ELSE l_ret_val := false;
2154 END IF;
2155 --
2156 close c10;
2157 --
2158 RETURN (l_ret_val);
2159 --
2160 end chkab3;
2161
2162
2163
2164 procedure b_elmnt_entry_dets(p_per_id IN NUMBER,
2165 p_sdstart IN DATE,
2166 p_abat_id IN NUMBER,
2167 p_e_entry_id IN OUT NOCOPY NUMBER,
2168 p_e_link_id IN OUT NOCOPY NUMBER,
2169 p_cpay_id IN OUT NOCOPY NUMBER,
2170 p_period_sdate IN OUT NOCOPY DATE,
2171 p_period_edate IN OUT NOCOPY DATE) is
2172
2173 cursor c11 is
2174 select e.element_entry_id,
2175 e.element_link_id,
2176 a.payroll_id,
2177 t.start_date,
2178 t.end_date
2179 from per_time_periods t,
2180 pay_element_entries_f e,
2181 per_assignments_f a
2182 where a.person_id = p_per_id
2183 and a.primary_flag = 'Y'
2184 and p_sdstart between a.effective_start_date and a.effective_end_date
2185 and e.assignment_id = a.assignment_id
2186 and e.creator_type = 'A'
2187 and e.creator_id = p_abat_id
2188 and p_sdstart between e.effective_start_date and e.effective_end_date
2189 and t.payroll_id = a.payroll_id
2190 and p_sdstart between t.start_date and t.end_date;
2191 --
2192 begin
2193 --
2194 hr_utility.set_location('per_absence_attendances_pkg.b_elmnt_entry_dets',1);
2195 --
2196 open c11;
2197 --
2198 fetch c11 into p_e_entry_id,
2199 p_e_link_id,
2200 p_cpay_id,
2201 p_period_sdate,
2202 p_period_edate;
2203 --
2204 close c11;
2205 --
2206 end b_elmnt_entry_dets;
2207
2208
2209 procedure b_get_category(p_mean IN OUT NOCOPY VARCHAR2,
2210 p_abcat IN VARCHAR2) is
2211
2212 cursor c12 is
2213 select meaning
2214 from hr_lookups
2215 where lookup_type = 'ABSENCE_CATEGORY'
2216 and lookup_code = p_abcat;
2217 --
2218 begin
2219 --
2220 hr_utility.set_location('per_absence_attendances_pkg.b_get_category',1);
2221 --
2222 open c12;
2223 --
2224 fetch c12 into p_mean;
2225 --
2226 close c12;
2227 --
2228 end b_get_category;
2229
2230
2231 procedure get_defaults(p_tend IN VARCHAR2,
2232 p_tstart IN VARCHAR2,
2233 p_dend IN DATE,
2234 p_dstart IN DATE,
2235 p_hrs_def IN OUT NOCOPY NUMBER,
2236 p_dys_hrs IN OUT NOCOPY NUMBER,
2237 p_dys_def IN OUT NOCOPY NUMBER) is
2238
2239 l_hrs_def number;
2240 l_dys_def number;
2241 l_dys_hrs number;
2242
2243 cursor c14 is
2244 select (((substr(p_tend,1,2) * 60) + substr(p_tend,4,2)) -
2245 ((substr(p_tstart,1,2) * 60) + substr(p_tstart,4,2))) / 60,
2246 (p_dend - p_dstart + 1) * 24,
2247 (p_dend - p_dstart) + 1
2248 from sys.dual;
2249 --
2250 begin
2251 --
2252 hr_utility.set_location('per_absence_attendances_pkg.get_defaults',1);
2253 --
2254 open c14;
2255 --
2256 fetch c14 into l_hrs_def,
2257 l_dys_hrs,
2258 l_dys_def;
2259 --
2260 close c14;
2261
2262 p_hrs_def := l_hrs_def * l_dys_def;
2263 -- Start of fix 3156665
2264 /* If the normal working hours is not defined at Assignment or
2265 Organization level, then system will take the default start time as
2266 00:00 and end time as 23:59. If we calculate the total hours with
2267 this duration then we will be getting as 23.98333'. For a day the
2268 it must be 24 hours as default. We cannot change the default
2269 end time as 24:00, because this will not work with the standard
2270 hours format like HH24:MI. Hence overriding the calculated hours
2271 with 24 hours. */
2272 --
2273 if p_tstart = '00:00' and p_tend = '23:59' then
2274 p_hrs_def := l_dys_hrs;
2275 end if;
2276 -- End of 3156665
2277 p_dys_hrs := l_dys_hrs;
2278 p_dys_def := l_dys_def;
2279
2280 --
2281 end get_defaults;
2282
2283
2284 procedure get_ele_det1(p_bgroup_id IN NUMBER,
2285 p_eltype IN NUMBER,
2286 p_per_id IN NUMBER,
2287 p_dstart IN DATE,
2288 p_sess IN DATE,
2289 p_ass_id IN OUT NOCOPY NUMBER,
2290 p_ele_link IN OUT NOCOPY NUMBER,
2291 p_pay_id IN OUT NOCOPY NUMBER,
2292 p_test IN OUT NOCOPY VARCHAR2) is
2293
2294 cursor c15 is
2295 select asg.assignment_id,
2296 l.element_link_id,
2297 asg.payroll_id,
2298 'X'
2299 from per_assignments_f asg,
2300 pay_element_types_f t,
2301 pay_element_links_f l
2302 where l.business_group_id + 0 = p_bgroup_id
2303 and t.element_type_id = p_eltype
2304 and asg.person_id = p_per_id
2305 and asg.primary_flag = 'Y'
2306 and p_dstart between asg.effective_start_date and asg.effective_end_date
2307 and t.element_type_id = l.element_type_id
2308 and ((l.payroll_id is not null
2309 and l.payroll_id = asg.payroll_id)
2310 or (l.link_to_all_payrolls_flag = 'Y'
2311 and asg.payroll_id is not null)
2312 or (l.payroll_id is null
2313 and l.link_to_all_payrolls_flag = 'N'))
2314 and (l.organization_id = asg.organization_id
2315 or l.organization_id is null)
2316 and (l.position_id = asg.position_id
2317 or l.position_id is null)
2318 and (l.grade_id = asg.grade_id
2319 or l.grade_id is null)
2320 and (l.location_id = asg.location_id
2321 or l.location_id is null)
2322 and (l.job_id = asg.job_id
2323 or l.job_id is null)
2324 and (l.employment_category = asg.employment_category
2325 or l.employment_category is null)
2326 and p_dstart between l.effective_start_date and l.effective_end_date
2327 and p_dstart between t.effective_start_date and t.effective_end_date
2328 and (l.people_group_id is null
2329 or exists
2330 (select 1
2331 from pay_assignment_link_usages_f lu1
2332 where lu1.assignment_id = asg.assignment_id
2333 and lu1.element_link_id = l.element_link_id
2334 and p_sess between lu1.effective_start_date and lu1.effective_end_date));
2335 --
2336 begin
2337 --
2338 hr_utility.set_location('per_absence_attendances_pkg.get_ele_det1',1);
2339 --
2340 open c15;
2341 --
2342 fetch c15 into p_ass_id,
2343 p_ele_link,
2344 p_pay_id,
2345 p_test;
2346 --
2347 close c15;
2348 --
2349 end get_ele_det1;
2350
2351
2352 procedure get_ele_det2(p_eletype IN NUMBER,
2353 p_abat_type IN NUMBER,
2354 p_dstart IN DATE,
2355 p_dele_name IN OUT NOCOPY VARCHAR2) is
2356
2357 cursor c16 is
2358 select pettl.element_name
2359 from per_absence_attendance_types paa,
2360 pay_element_types_f pet,
2361 pay_element_types_f_tl pettl
2362 where pet.element_type_id = p_eletype
2363 and paa.absence_attendance_type_id = p_abat_type
2364 and pet.element_type_id = pettl.element_type_id
2365 and nvl(p_dstart,paa.date_effective)
2366 between pet.effective_start_date and pet.effective_end_date
2367 and pettl.language = userenv('LANG');
2368 --
2369 begin
2370 --
2371 hr_utility.set_location('per_absence_attendances_pkg.get_ele_det2',1);
2372 --
2373 open c16;
2374 --
2375 fetch c16 into p_dele_name;
2376 --
2377 close c16;
2378 --
2379 end get_ele_det2;
2380
2381
2382
2383 procedure get_period_dates(p_cpay_id IN NUMBER,
2384 p_dstart In DATE,
2385 p_prd_start IN OUT NOCOPY DATE,
2386 p_prd_end IN OUT NOCOPY DATE,
2387 p_test IN OUT NOCOPY VARCHAR2) is
2388
2389 cursor c18 is
2390 select start_date,
2391 end_date,
2392 'X'
2393 from per_time_periods
2394 where payroll_id = p_cpay_id
2395 and p_dstart between start_date and end_date;
2396 --
2397 begin
2398 --
2399 hr_utility.set_location('per_absence_attendances_pkg.get_period_dates',1);
2400 --
2401 open c18;
2402 --
2403 fetch c18 into p_prd_start,
2404 p_prd_end,
2405 p_test;
2406 --
2407 close c18;
2408 --
2409 end get_period_dates;
2410
2411
2412 procedure get_run_tot(p_abat_type IN NUMBER,
2413 p_per_id IN NUMBER,
2414 p_db_itm IN OUT NOCOPY VARCHAR2,
2415 p_ass_id IN OUT NOCOPY NUMBER) is
2416
2417 cursor c19 is
2418 select di.user_name
2419 from ff_database_items di,
2420 ff_user_entities ue
2421 where di.user_entity_id = ue.user_entity_id
2422 and ue.creator_type = 'A'
2423 and ue.creator_id = p_abat_type;
2424 --
2425 cursor c20 is
2426 select assignment_id
2427 from per_assignments
2428 where person_id = p_per_id
2429 and primary_flag = 'Y';
2430 --
2431 begin
2432 --
2433 hr_utility.set_location('per_absence_attendances_pkg.get_run_tot',1);
2434 --
2435 open c19;
2436 --
2437 fetch c19 into p_db_itm;
2438 --
2439 close c19;
2440 --
2441 hr_utility.set_location('per_absence_attendances_pkg.get_run_tot',2);
2442 --
2443 open c20;
2444 --
2445 fetch c20 into p_ass_id;
2446 --
2447 close c20;
2448 --
2449 end get_run_tot;
2450 --
2451 -- this function returns the total number of absence taken
2452 -- by the employee from the start of the year.
2453 --
2454 --
2455 function get_annual_balance(p_session_date IN DATE,
2456 p_abs_type_id IN NUMBER,
2457 p_ass_id IN NUMBER)return NUMBER is
2458
2459 cursor c19 is
2460 -- Bug 563202 - drive from abs and go via link input value
2461 select nvl(sum(fnd_number.canonical_to_number(
2462 nvl(pev.screen_entry_value,'0.0'))),0)
2463 from pay_element_entry_values_f pev,
2464 pay_element_entries_f pee,
2465 pay_link_input_values_f lnk,
2466 per_absence_attendance_types abs
2467 where abs.absence_attendance_type_id = p_abs_type_id
2468 and lnk.input_value_id = abs.input_value_id
2469 and pev.input_value_id + 0 = abs.input_value_id
2470 and pee.element_link_id = lnk.element_link_id
2471 and pev.element_entry_id = pee.element_entry_id
2472 and pee.assignment_id = p_ass_id
2473 and to_char(pev.effective_start_date, 'YYYY') =
2474 to_char(p_session_date, 'YYYY')
2475 and to_char(pev.effective_end_date,'YYYY') =
2476 to_char(p_session_date, 'YYYY')
2477 and pev.effective_start_date = pee.effective_start_date
2478 and pev.effective_end_date = pee.effective_end_date
2479 and pee.effective_start_date between
2480 lnk.effective_start_date and lnk.effective_end_date;
2481 --
2482 l_balance NUMBER := 0;
2483 --
2484 begin
2485 --
2486 hr_utility.set_location('per_absence_attendances_pkg.get_annual_bal',1);
2487 --
2488 open c19;
2489 --
2490 fetch c19 into l_balance;
2491 --
2492 close c19;
2493 --
2494 return l_balance;
2495 --
2496 end get_annual_balance;
2497 --
2498 -- The following function checks first of all that the absence is of type
2499 -- accrual. If the absence is Accural then the net entitlement is checked
2500 -- to see whether the absence duration would exceeds the entitlement.
2501 --
2502 function is_emp_entitled (p_abs_att_type_id NUMBER,
2503 p_ass_id NUMBER,
2504 p_calculation_date DATE,
2505 p_days_requested NUMBER,
2506 p_hours_requested NUMBER)
2507 return boolean is
2508
2509 -- Bug 673835. Editted cursor so that it can pick up an accrual plan,
2510 -- if there is one for the given assignment_id. Also added check so that
2511 -- plan is only selected if the employee is enrolled in it as at the
2512 -- calculation date.
2513
2514 CURSOR csr_get_accrual_plan is
2515 select pap.accrual_plan_id,
2516 pap.accrual_category,
2517 pap.accrual_units_of_measure,
2518 asg.payroll_id,
2519 asg.business_group_id
2520 from pay_element_entry_values_f pev,
2521 pay_element_entries_f pee,
2522 pay_input_values_f piv,
2523 pay_accrual_plans pap,
2524 per_absence_attendance_types abst,
2525 per_all_assignments_f asg
2526 where pee.assignment_id = p_ass_id
2527 and abst.absence_attendance_type_id = p_abs_att_type_id
2528 and pap.pto_input_value_id = abst.input_value_id
2529 and pee.element_entry_id = pev.element_entry_id
2530 and piv.input_value_id = pev.input_value_id
2531 and pap.accrual_plan_element_type_id = piv.element_type_id
2532 and piv.element_type_id = pee.element_type_id
2533 and asg.assignment_id = pee.assignment_id
2534 and p_calculation_date between pee.effective_start_date
2535 and pee.effective_end_date;
2536
2537 l_plan_id pay_accrual_plans.accrual_plan_id%TYPE;
2538 l_plan_category pay_accrual_plans.accrual_category%TYPE;
2539 l_units_of_measure pay_accrual_plans.accrual_units_of_measure%TYPE;
2540 l_payroll_id number;
2541 l_business_group_id number;
2542 l_entitlement NUMBER;
2543 l_start_date date;
2544 l_end_date date;
2545 l_accrual_end_date date;
2546 l_accrual number;
2547
2548 --
2549 begin
2550 --
2551 hr_utility.set_location('per_absence_attendances_pkg.check_entitlement',1);
2552 --
2553 if p_days_requested is null and p_days_requested is null then
2554 --
2555 return true;
2556 --
2557 end if;
2558
2559 open csr_get_accrual_plan;
2560 fetch csr_get_accrual_plan into l_plan_id,
2561 l_plan_category,
2562 l_units_of_measure,
2563 l_payroll_id,
2564 l_business_group_id;
2565
2566 if csr_get_accrual_plan%FOUND and l_payroll_id is not null then
2567 --
2568 close csr_get_accrual_plan;
2569 per_accrual_calc_functions.get_net_accrual(
2570 P_Assignment_ID => p_ass_id
2571 ,P_Plan_ID => l_plan_id
2572 ,P_Payroll_ID => l_payroll_id
2573 ,P_Business_Group_ID => l_business_group_id
2574 ,P_Calculation_Date => p_calculation_date
2575 ,P_Start_Date => l_start_date
2576 ,P_End_Date => l_end_date
2577 ,P_Accrual_End_Date => l_accrual_end_date
2578 ,P_Accrual => l_accrual
2579 ,P_Net_Entitlement => l_entitlement
2580 );
2581 hr_utility.trace('ent= '||to_char(l_entitlement));
2582
2583 if (l_units_of_measure = 'D' and l_entitlement < p_days_requested) THEN
2584 return FALSE;
2585 elsif (l_units_of_measure = 'H' and l_entitlement < p_hours_requested) THEN
2586 return FALSE;
2587 else
2588 return true;
2589 end if;
2590 else
2591 close csr_get_accrual_plan;
2592 return TRUE;
2593 end if;
2594 --
2595 end is_emp_entitled;
2596
2597
2598 procedure init_form(p_form_type IN OUT NOCOPY NUMBER,
2599 p_per_id IN NUMBER,
2600 p_sess IN DATE,
2601 p_dstart IN OUT NOCOPY DATE,
2602 p_dend IN OUT NOCOPY DATE) is
2603
2604 -- Bug fix 3387265.
2605 -- cursor c21 modified to improve performance.
2606
2607 cursor c21 is
2608 select formula_type_id
2609 from ff_formula_types
2610 where formula_type_name = 'QuickPaint';
2611 --
2612 cursor c21b is
2613 select greatest(date_start),
2614 actual_termination_date
2615 from per_periods_of_service
2616 where person_id = p_per_id
2617 and date_start <= p_sess;
2618 --
2619 begin
2620 --
2621 hr_utility.set_location('per_absence_attendances_pkg.init_form',1);
2622 --
2623 open c21;
2624 --
2625 fetch c21 into p_form_type;
2626 --
2627 close c21;
2628 --
2629 hr_utility.set_location('per_absence_attendances_pkg.init_form',2);
2630 --
2631 open c21b;
2632 --
2633 fetch c21b into p_dstart,
2634 p_dend;
2635 --
2636 close c21b;
2637 --
2638 end init_form;
2639
2640
2641 procedure ins_ok(p_per_id IN NUMBER,
2642 p_test IN OUT NOCOPY VARCHAR2) is
2643
2644 cursor c22 is
2645 select 'Y'
2646 from per_assignments
2647 where primary_flag = 'Y'
2648 and person_id = p_per_id
2649 and payroll_id is not null;
2650 --
2651 begin
2652 --
2653 hr_utility.set_location('per_absence_attendances_pkg.ins_ok',1);
2654 --
2655 open c22;
2656 --
2657 fetch c22 into p_test;
2658 --
2659 close c22;
2660 --
2661 end ins_ok;
2662
2663
2664 procedure get_occur(p_bgroup_id IN NUMBER,
2665 p_abat_type IN NUMBER,
2666 p_per_id IN NUMBER,
2667 p_occur IN OUT NOCOPY NUMBER) is
2668
2669 cursor c23 is
2670 select nvl(max(occurrence),0) + 1
2671 from per_absence_attendances
2672 where business_group_id + 0 = p_bgroup_id
2673 and absence_attendance_type_id = p_abat_type
2674 and person_id = p_per_id;
2675 --
2676 begin
2677 --
2678 hr_utility.set_location('per_absence_attendances_pkg.get_occur',1);
2679 --
2680 open c23;
2681 --
2682 fetch c23 into p_occur;
2683 --
2684 close c23;
2685 --
2686 end get_occur;
2687
2688
2689 function chk_serv_period(p_per_id in number,
2690 p_curr_date_end in date,
2691 p_proj_start in date)
2692 RETURN BOOLEAN IS
2693 l_ret_val BOOLEAN;
2694 --
2695 cursor chk_periods is
2696 select 1
2697 from per_periods_of_service pos
2698 where pos.person_id = p_per_id
2699 and pos.date_start > p_curr_date_end
2700 and p_proj_start between
2701 pos.date_start AND nvl(pos.actual_termination_date,hr_general.end_of_time);
2702 --
2703 begin
2704 --
2705 hr_utility.set_location('per_absence_attendances_pkg.chk_serv_period',1);
2706 --
2707 open chk_periods;
2708 --
2709 l_ret_val := chk_periods%found;
2710 --
2711 close chk_periods;
2712 --
2713 RETURN l_ret_val;
2714 --
2715 end chk_serv_period;
2716 --
2717 -- ---------------------------------------------------------------------------
2718 -- ---------------------------< get_mat_details >-----------------------------
2719 -- ---------------------------------------------------------------------------
2720 --
2721 -- PUBLIC
2722 -- Description:
2723 -- Procedure that is used by SSP/SMP users of Absence form.
2724 -- Gets the due and MPP dates. Called from Form PERWSEAD (absence).
2725 --
2726 -- NOTE: rowid parameter defined as varchar2 parameter because PL/SQL 1.1
2727 -- used in forms and rowid isn't a datatype.
2728 --
2729 procedure get_mat_details
2730 (
2731 p_maternity_id in number,
2732 p_due_date in out nocopy date,
2733 p_mpp_start_date in out nocopy date,
2734 p_earliest_abs_start_date in out nocopy date,
2735 p_earliest_abs_rowid in out nocopy varchar2,
2736 p_nos_absences in out nocopy number
2737 ) is
2738 --
2739 cursor c1 is
2740 select h.due_date, h.mpp_start_date
2741 from ssp_maternities h
2742 where h.maternity_id = p_maternity_id;
2743 --
2744 BEGIN
2745 open c1;
2746 fetch c1 into p_due_date, p_mpp_start_date;
2747 close c1;
2748 --
2749 get_earliest_abs_start_date(p_maternity_id,
2750 p_earliest_abs_start_date,
2751 p_earliest_abs_rowid,
2752 p_nos_absences);
2753 END get_mat_details;
2754 --
2755 -- ---------------------------------------------------------------------------
2756 -- ---------------------------< get_due_date >--------------------------------
2757 -- ---------------------------------------------------------------------------
2758 --
2759 -- PUBLIC
2760 -- Description:
2761 -- Procedure that is used by SSP/SMP users of Absence form
2762 -- Get the due date. Used from Form PERWSEAD (absence).
2763 --
2764 Function get_due_date (p_maternity_id in number) return date is
2765 cursor c1 is
2766 select h.due_date
2767 from ssp_maternities h
2768 where h.maternity_id = p_maternity_id;
2769 c1_rec c1%ROWTYPE;
2770 BEGIN
2771 open c1;
2772 hr_utility.set_location('per_absence_attendances_pkg.get_due_date',1);
2773 fetch c1 into c1_rec;
2774 close c1;
2775 return(c1_rec.due_date);
2776 END get_due_date;
2777 --
2778 -- ---------------------------------------------------------------------------
2779 -- ---------------------------< get_due_date_2 >------------------------------
2780 -- ---------------------------------------------------------------------------
2781 --
2782 -- PUBLIC
2783 -- Description:
2784 -- Procedure that is used by SSP/SMP users of Absence form
2785 -- Get the due_date if there is ONLY one due date and also check that
2786 -- the person specified has indeed a related maternity record.
2787 --
2788 Function get_due_date_2 (p_person_id in number,
2789 p_leave_type in varchar2,
2790 p_smp_due_date in out nocopy date)
2791 return BOOLEAN is
2792
2793 counter number;
2794
2795 cursor c1 is
2796 select h.due_date
2797 from ssp_maternities h
2798 where h.person_id = p_person_id
2799 and nvl(h.leave_type,'MA') = p_leave_type;
2800
2801
2802 BEGIN
2803 hr_utility.set_location('per_absence_attendances_pkg.get_due_date_2',1);
2804 counter:= 0;
2805 for c1_rec in c1 LOOP
2806 p_smp_due_date := c1_rec.due_date;
2807 counter := counter + 1;
2808 end LOOP;
2809
2810 if counter = 0 then
2811 -- there was no maternity record
2812 fnd_message.set_name('SSP','SSP_35032_NO_RELATED_MATERNITY');
2813 fnd_message.raise_error;
2814 return(FALSE);
2815 elsif counter > 1 then
2816 -- There are multiple maternity records
2817 p_smp_due_date := null;
2818 end if;
2819
2820 -- So left with only one row returned and smp_due_date was set
2821 return(TRUE);
2822 END get_due_date_2;
2823 --
2824 -- ---------------------------------------------------------------------------
2825 -- -----------------------< check_val_abs_start >-----------------------------
2826 -- ---------------------------------------------------------------------------
2827 --
2828 -- PUBLIC
2829 -- Description:
2830 -- Check that the maternity absence start is not before the Qualifying Week
2831 --
2832 Procedure check_val_abs_start (p_date_start in date,
2833 p_maternity_id in number) is
2834 Cursor csr_maternity is
2835 select ssp_smp_pkg.qualifying_week (mat.due_date) QW,
2836 nvl(mat.mpp_start_date,hr_general.end_of_time) MPP,
2837 nvl(mat.leave_type,'MA') leave_type
2838 from ssp_maternities MAT
2839 where mat.maternity_id = p_maternity_id;
2840 --
2841 maternity csr_maternity%ROWTYPE;
2842 --
2843 BEGIN
2844 hr_utility.set_location('per_absence_attendances_pkg.check_val_abs_start',1);
2845 --
2846 -- This functionality only applies to Oracle SSP users
2847 if not ssp_ssp_pkg.ssp_is_installed then
2848 return;
2849 end if;
2850 --
2851 if p_maternity_id is not null then
2852 --
2853 open csr_maternity;
2854 fetch csr_maternity into maternity;
2855 close csr_maternity;
2856 --
2857 if p_date_start < least(maternity.QW, maternity.MPP)
2858 and maternity.leave_type = 'MA'
2859 then
2860 fnd_message.set_name('SSP', 'SSP_35031_MAT_ABS_DATE');
2861 fnd_message.raise_error;
2862 end if;
2863
2864 end if;
2865 --
2866 END check_val_abs_start;
2867
2868 --
2869 -- ---------------------------------------------------------------------------
2870 -- -----------------------< check_related_maternity >-------------------------
2871 -- ---------------------------------------------------------------------------
2872 --
2873 -- PUBLIC
2874 -- Description:
2875 -- Procedure that is used by SSP/SMP users of Absence form
2876 -- Ensure that there is a related Maternity record for this Person
2877 --
2878 Procedure check_related_maternity (p_person_id in number) is
2879 cursor c1 is
2880 select h.rowid
2881 from ssp_maternities h
2882 where h.person_id = p_person_id;
2883 c1_rec c1%ROWTYPE;
2884 BEGIN
2885 hr_utility.set_location('per_absence_attendances_pkg.chk_related_maernity',1);
2886 open c1;
2887 fetch c1 into c1_rec;
2888 if c1%NOTFOUND then
2889 fnd_message.set_name('SSP','SSP_36073_PREG_RELATED_ILLNESS');
2890 fnd_message.raise_error;
2891 end if;
2892 close c1;
2893 END check_related_maternity;
2894
2895
2896 --
2897 -- ---------------------------------------------------------------------------
2898 -- -----------------------< check_evd_before_del >----------------------------
2899 -- ---------------------------------------------------------------------------
2900 --
2901 -- PUBLIC
2902 -- Description:
2903 -- Procedure that is used by SSP/SMP users of Absence form
2904 -- Ensure that there is no attached sick note for this Sickness absence
2905 -- before deletion. Valid for SSP/SMP users only
2906 --
2907 Procedure check_evd_before_del(p_absence_attendance_id in number,
2908 p_medical_type in varchar2) is
2909 cursor c1 is
2910 select h.rowid
2911 from ssp_medicals h
2912 where h.absence_attendance_id = p_absence_attendance_id
2913 and h.medical_type = p_medical_type;
2914 c1_rec c1%ROWTYPE;
2915 BEGIN
2916 hr_utility.set_location('per_absence_attendances_pkg.chk_evd_before_del',1);
2917 open c1;
2918 fetch c1 into c1_rec;
2919 if c1%FOUND then
2920 fnd_message.set_name('SSP','SSP_35033_ATTACHED_SICK_NOTE');
2921 fnd_message.raise_error;
2922 end if;
2923 close c1;
2924 END check_evd_before_del;
2925
2926 -- ---------------------------------------------------------------------------
2927 -- -----------------------< late_abs_notification>----------------------------
2928 -- ---------------------------------------------------------------------------
2929 --
2930 -- PUBLIC
2931 -- Description:
2932 -- Procedure that is used by SSP/SMP users of Absence form
2933 -- Validation used for Absences of type SSP to see if notification
2934 -- was late. If it is, then reason_for_notification_delay becomes
2935 -- mandatory (accept_late_notification will be also).
2936 --
2937 Function late_abs_notification (p_date_notification in date,
2938 p_date_start in date,
2939 p_effective_date in date,
2940 p_element_name in varchar2) return boolean is
2941
2942 l_element_details ssp_SMP_pkg.csr_SMP_element_details%rowtype;
2943
2944 BEGIN
2945 hr_utility.set_location
2946 ('per_absence_attendances_pkg.late_abs_notification',1);
2947 open ssp_SMP_pkg.csr_SMP_element_details(p_effective_date,
2948 p_element_name);
2949 fetch ssp_SMP_pkg.csr_SMP_element_details into l_element_details;
2950 close ssp_SMP_pkg.csr_SMP_element_details;
2951 if p_date_notification >
2952 (p_date_start -
2953 to_number(l_element_details.mpp_notice_requirement)) then
2954 -- Added explicit number conversion above. Bug 3111653
2955
2956 return(TRUE);
2957 else
2958 return(FALSE);
2959 end if;
2960 END late_abs_notification;
2961
2962 end PER_ABSENCE_ATTENDANCES_PKG;