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