1 package body SSP_SMP_SUPPORT_PKG as
2 /* $Header: spsspbsi.pkb 120.19 2011/11/22 13:59:26 achandwa noship $
3 +==============================================================================+
4 | Copyright (c) 1994 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +==============================================================================+
8 --
9 Name
10 SSP/SMP shared code package
11 Purpose
12 To hold code common to both SSP and SMP business processes.
13 History
14 31 Aug 95 N Simpson Created
15 15 Sep 95 N Simpson Added function stoppage_overridden
16 20 sep 95 N Simpson Added procedure recalculate_SSP_and_SMP
17 27 Oct 95 N Simpson Replaced hr_ prefix with ssp_
18 3 Nov 95 N Simpson csr_next_available_period%notfound
19 replaced with l_effective_start_date is
20 not null in get_entry_details procedure;
21 cursor will always return a row because
22 of the MIN function.
23 27 Nov 95 N Simpson Removed temporary code from last update
24 and changed criteria for selecting
25 payroll periods in get_entry_details
26 30 Nov 95 N Simpson Reversed comparison between
27 p_last_process_date and p_date_earned
28 in get_entry_details cursor
29 csr_next_available_period. It was,
30 incorrectly, ignoring date earned
31 because date earned was always less than
32 the end of time.
33 5 dec 95 N Simpson Added function average_earnings_error.
34 6 Dec 95 N Simpson Added reference to ssp_smp_support_pkg.
35 reason_for_no_earnings
36 19 Jan 96 N Simpson Added functions start_of_week and
37 end_of_week.
38 08-Jan-98 RThirlby 608724 30.24 Altered non-translateable MON format
39 for dates to MM.
40 Parameter p_deleting added to procedure
41 recalculate_ssp_and_smp. If p_deleting
42 logic to get rid of orphaned stoppages.
43 Part of SMP entries problem.
44 24-Mar-98 RThirlby 563202 30.25 Performance fix to csr_stoppage.
45 19-AUG-98 A.Myers 701750 30.26 Amended cursors for affected rows, as
46 the "where not exists" did not work.
47 This existence checking is now done in
48 SSP_SSP_PKG/SSP_SMP_PKG on the actual
49 row insert. Added p_deleting to SMP call
50 to SMP_control.
51 06-JAN-2000 ILeath 30.27/ Add call to stop_if_director and
52 110.7 check_payroll_installed within
53 recalculate_ssp_and_smp. To
54 ensure that message within
55 average_earnings_error is always
56 set. Also default if null
57 message to 'Cannot derive new
58 emps pay'.
59 12-APR-2000 A.Mills 30.28 Changed NI_Lower_Earnings_Limit
60 =110.8 function to retrieve the Weekly
61 LEL figure from the Global
62 'NI_WEEKLY_LEL' rather than the
63 User Table, which becomes obsolete
64 as at 6-APR-2000. Bug 871095.
65 05-DEC-2001 GButler 115.7 Added new procedure update_ssp_smp_entries
66 to allow automatic recalculation of SSP/
67 SMP entries over tax year end following
68 legislative updates to the corresponding
69 SSP/SMP rates. Procedure is called from
70 perleggb.sql script after seed data
71 install completed
72 31-DEC-2001 ABHADURI 115.8 Added a condition to inform user that
73 employee has been re-hired within 8 weeks.
74 15-JAN-2002 GBUTLER 115.9 Updated update_ssp_smp_entries procedure
75 to exclude all terminated employees whose
76 final process date has already passed
77 02-FEB-2002 GBUTLER 115.10 Bug 2189501. Updated SSP and SMP queries
78 to better handle employee terminations and
79 exclude deceased employees for SMP. Also
80 added exceptions to handle cases where no
81 element entries can be found in new tax year
82 05-FEB-2002 GBUTLER 115.12 Updated queries to retrieve SSP/SMP entries in
83 new tax year so that entries retrieved relate to
84 people who would be retrieved by the main SSP/SMP
85 queries
86 14-FEB-2002 GBUTLER 115.13 Added close statements to cursors
87 26-FEB-2002 GBUTLER 115.14 Altered update_ssp_smp_entries by adding sub-blocks
88 into loops to detect errors as they occur but not to
89 halt update process because of them. Added p_update_
90 error boolean flag to alert user to absences that
91 could not be updated
92 02-DEC-2002 ABLINKO 115.16 Bug 2690305. New SAP/SPP functionality
93 10-DEC-2002 GBUTLER 115.17 Bug 2702282. Commented out section for SMP rate updates
94 for TYE 2002/3
95 17-DEC-2002 ABLINKO 115.18 gscc fix
96 09-jan-2003 vmkhande 115.19 bug 2706844
97 Effective start date now retuned from
98 get_entry_details will be the max of
99 assignment start date and payroll
100 period start date.
101 24-JAN-2003 GButler 115.20 nocopy fixes
102 06-MAR-2003 GButler 115.21 Bug 1681054. Change to csr_assignment cursor in
103 get_entry_details to exclude benefits assignments
104 24-OCT-2003 ABlinko 115.22 Replaced hardcoded SATURDAY and SUNDAY references
105 08-DEC-2003 RMakhija 115.24 Uncommented SMP element entry update when rate
106 changes in next tax year. Also added similar
107 Functionality for SAP, SPP Birth and SPP Adoption.
108 17-DEC-2003 RMakhija 115.25 Added detection of SMP/SAP/SPP standard rate
109 changes and SMP Higher Rate changes to auto
110 update element entries in next tax year.
111 12-FEB-2004 RMakhija 115.26 Bug 3437026. Updated csr_affected_leave
112 cursor in update_ssp_smp_entries procedure.
113 02-MAR-2004 ABlinko 115.27 Bug 3456918 - Added rtrim when deriving
114 l_saturday_txt and l_sunday_txt
115 21-MAR-2006 Kthampan 115.28 Bug 5105039 - Passing the correct date when
116 fetching element link.
117 31-JUL-2006 Kthampan 115.29 Bug 5346648 - Update procedure get_entry_details
118 to re-fetch the effective_start/end date again
119 if the assignment start date is > the
120 period start_date.
121 23-AUG-06 KThampan 115.30 Bug 5482199 - Statutory changes for 2007
122 115.31 Change cursor csr_payroll_period to check
123 for period.cut_off_date when payment is not
124 in lump sum.
125 19-SEP-06 KThampan 115.33 Bug 5547703 - Amend recalculate_SSP_and_SMP
126 only to delete stoppage when absence
127 record = 0
128 20-OCT-06 KThampan 115.34 Amend cursor csr_payroll_period to use
129 p_date_earned <= period.end_date instead of
130 p_date_earned <= nvl(cut_off_date,end_date)
131 09-DEC-06 KThampan 115.35 Amend procedure recalculate_SSP_and_SMP to only
132 process rows within the same session id
133 12-MAR-07 KThampan 115.36 Added distinct when select input id for SSP
134 cursor csr_first_new_ssp_entry
135 21-MAR-07 KThampan 115.37 Amended cursor csr_affected_absences and
136 csr_affected_leave to check for period of
137 service id
138 20-FEB-07 pbalu 115.38 Added Multi threaded update_ssp_smp_entries
139 as part of 6800788.
140 27-FEB-07 pbalu 115.39 Error flag is not set in the multithreaded
141 update_ssp_smp_entries
142 25-AUG-08 pbalu 115.40 Changed the cursor csr_payroll_period to
143 take care of positive cutoff period for bug 6959669
144 18-DEC-08 npannamp 115.42 Modified multithreaded update_ssp_smp_entries
145 to resubmit failed jobs once more as part of
146 bug 6870415
147 17-Nov-10 npannamp 115.43 Additional Statutory Paternity Pay logic introduced.
148 bug 9897338
149 22-Nov-11 achandwa 115.44 Additional Statutory Paternity Pay Rate Changes
150 Bug 13400812
151 22-Nov-11 achandwa 115.45 Bug 13400812: Included the two count variables
152 */
153 --------------------------------------------------------------------------------
154 g_package constant varchar2 (31) := 'ssp_smp_support_pkg.';
155
156
157 --
158 cursor csr_entry_value (
159 --
160 p_element_entry_id in number,
161 p_input_value_name in varchar2) is
162 --
163 -- Selects an entry value for a given
164 -- element entry and named input value
165 --
166 select entry.screen_entry_value,
167 inp.uom,
168 ele.input_currency_code,
169 inp.input_value_id
170 from pay_element_entry_values_f entry,
171 pay_input_values_f inp,
172 pay_element_types_f ele
173 where entry.element_entry_id = p_element_entry_id
174 and inp.name = p_input_value_name
175 and entry.input_value_id = inp.input_value_id
176 and ele.element_type_id = inp.element_type_id
177 and ele.effective_start_date <= inp.effective_end_date
178 and ele.effective_end_date >= inp.effective_start_date
179 and inp.effective_start_date <= entry.effective_end_date
180 and inp.effective_end_date >= entry.effective_start_date;
181 --
182 --------------------------------------------------------------------------------
183 function start_of_week (p_date date) return date is
184 --
185 -- Returns the date of the last Sunday before the p_date, or the p_date if
186 -- that is actually a Sunday anyway.
187 --
188 l_Sunday date := p_date;
189 l_proc varchar2 (72) := g_package||'start_of_week';
190 l_sunday_txt varchar2(100) := rtrim(to_char(to_date('07/01/2001','DD/MM/YYYY'),'DAY'));
191 --
192 begin
193 --
194 hr_utility.set_location('Entering:'||l_proc,1);
195 --
196 hr_utility.trace(' p_date: '||to_char (p_date));
197 --
198 if p_date is not null and p_date <> hr_general.end_of_time
199 then
200 if rtrim (to_char (p_date, 'DAY')) <> l_sunday_txt then
201 l_Sunday := next_day (p_date, l_sunday_txt) -7;
202 end if;
203 end if;
204 --
205 hr_utility.set_location('Leaving :'||l_proc,100);
206 --
207 return l_Sunday;
208 --
209 end start_of_week;
210 --------------------------------------------------------------------------------
211 function end_of_week (p_date date) return date is
212 --
213 -- Returns the date of the Saturday following the p_date, or the p_date if
214 -- that is actually a Saturday anyway.
215 --
216 l_Saturday date := p_date;
217 l_proc varchar2 (72) := g_package||'end_of_week';
218 l_saturday_txt varchar2(100) := rtrim(to_char(to_date('06/01/2001','DD/MM/YYYY'),'DAY'));
219 --
220 begin
221 --
222 hr_utility.set_location('Entering:'||l_proc,1);
223 --
224 hr_utility.trace(' p_date: '||to_char (p_date));
225 --
226 if p_date is not null and p_date <> hr_general.end_of_time
227 then
228 if rtrim (to_char (p_date, 'DAY')) <> l_saturday_txt then
229 l_Saturday := next_day (p_date, l_saturday_txt);
230 end if;
231 end if;
232 --
233 hr_utility.set_location('Leaving :'||l_proc,100);
234 --
235 return l_Saturday;
236 --
237 end end_of_week;
238 --------------------------------------------------------------------------------
239 function end_of_rolling_week (p_start_date date, p_date date) return date is
240 --
241 -- p_start_date : Start date of the rolling week
242 -- p_date : Date for which end of rolling week to be identified
243 -- Returns the next rolling week end date following the p_date, or the p_date if
244 -- that is actually rolling week end day.
245 --
246 l_date date := p_date;
247 l_proc varchar2 (72) := g_package||'end_of_rolling_week';
248 l_day_txt varchar2(100);
249 --
250 begin
251 --
252 hr_utility.set_location('Entering:'||l_proc,1);
253 hr_utility.trace(' p_start_date: '||to_char (p_date));
254 hr_utility.trace(' p_date: '||to_char (p_date));
255 --
256 if p_start_date is not null then
257 l_day_txt := rtrim(to_char(p_start_date -1,'DAY'));
258 if p_date is not null and p_date <> hr_general.end_of_time
259 then
260 if rtrim (to_char (p_date, 'DAY')) <> l_day_txt then
261 l_date := next_day (p_date, l_day_txt);
262 end if;
263 end if;
264 end if;
265 --
266 hr_utility.set_location('Leaving :'||l_proc,100);
267 --
268 return l_date;
269 --
270 end end_of_rolling_week;
271 --------------------------------------------------------------------------------
272 function NI_Lower_Earnings_Limit (p_effective_date in date) return number is
273 --
274 cursor csr_LEL is
275 --
276 -- Get the LEL as at the effective date
277 --
278 select to_number(ni.global_value) LEL
279 from ff_globals_f ni
280 where ni.global_name = 'NI_WEEKLY_LEL'
281 and ni.business_group_id is null
282 and ni.legislation_code = 'GB'
283 and p_effective_date between ni.effective_start_date
284 and ni.effective_end_date;
285 --
286 l_proc varchar2 (72) := g_package||'NI_Lower_Earnings_Limit';
287 l_LEL number;
288 --
289 procedure check_parameters is
290 --
291 all_parameters_valid constant boolean := (p_effective_date is not null
292 and p_effective_date = trunc (p_effective_date));
293 --
294 begin
295 --
296 hr_utility.trace (l_proc||' p_effective_date = '
297 ||to_char (p_effective_date));
298 --
299 hr_general.assert_condition (all_parameters_valid);
300 --
301 end check_parameters;
302 --
303 begin
304 --
305 hr_utility.set_location('Entering:'||l_proc,1);
306 --
307 check_parameters;
308 --
309 open csr_LEL;
310 fetch csr_LEL into l_lel;
311 close csr_LEL;
312 --
313 if l_LEL is null then
314 --
315 -- The LEL is seeded on to user tables, but we do not seed data for the
316 -- tax years before 95/96 so if the customer has need for the LEL value
317 -- before this date (eg for historic absences) then we must set the historic
318 -- value before returning.
319 --
320 hr_utility.trace (' No LEL defined on user tables; hard-coding value');
321 --
322 if p_effective_date between to_date ('06-04-1991', 'DD-MM-YYYY')
323 and to_date ('05-04-1992', 'DD-MM-YYYY')
324 then
325 l_LEL := 52.00; -- Tax Year 91/92
326 elsif
327 p_effective_date between to_date ('06-04-1992', 'DD-MM-YYYY')
328 and to_date ('05-04-1993', 'DD-MM-YYYY')
329 then
330 l_LEL := 54.00; -- Tax Year 92/93
331 elsif
332 p_effective_date between to_date ('06-04-1993', 'DD-MM-YYYY')
333 and to_date ('05-04-1994', 'DD-MM-YYYY')
334 then
335 l_LEL := 56.00; -- Tax Year 93/94
336 elsif
337 p_effective_date between to_date ('06-04-1994', 'DD-MM-YYYY')
338 and to_date ('05-04-1995', 'DD-MM-YYYY')
339 then
340 l_LEL := 57.00; -- Tax Year 94/95
341 end if;
342 end if;
343 --
344 hr_utility.set_location('Leaving :'||l_proc, 100);
345 --
346 return l_LEL;
347 --
348 end NI_Lower_Earnings_Limit;
349 --------------------------------------------------------------------------------
350 function entry_already_processed (
351 --
352 -- Returns TRUE if the entry passed in has already been processed in a
353 -- payroll run
354 --
355 p_element_entry_id in number) return boolean is
356 --
357 cursor csr_processed is
358 select 1
359 from pay_run_results
360 where source_id = p_element_entry_id
361 and status <> 'U';
362 --
363 l_processed boolean := FALSE;
364 l_dummy integer (1);
365 --
366 begin
367 --
368 open csr_processed;
369 fetch csr_processed into l_dummy;
370 --
371 if csr_processed%found then
372 l_processed := TRUE;
373 end if;
374 --
375 close csr_processed;
376 --
377 return l_processed;
378 --
379 end entry_already_processed;
380 --------------------------------------------------------------------------------
381 function value (
382 --
383 -- Returns an entry value for a named input value
384 -- and element entry.
385 --
386 p_element_entry_id number,
387 p_input_value_name varchar2)
388 --
389 return varchar2 is
390 --
391 l_entry_value csr_entry_value%rowtype;
392 l_value varchar2 (80);
393 --
394 begin
395 --
396 open csr_entry_value (p_element_entry_id,p_input_value_name);
397 fetch csr_entry_value into l_entry_value;
398 close csr_entry_value;
399 --
400 l_value := hr_chkfmt.changeformat ( l_entry_value.screen_entry_value,
401 l_entry_value.uom,
402 l_entry_value.input_currency_code);
403 return l_value;
404 --
405 end value;
406 --------------------------------------------------------------------------------
407 function element_input_value_id (
408 --
409 -- Returns the input value ID for a named
410 -- input value
411 --
412 p_element_type_id number,
413 p_input_value_name varchar2)
414 --
415 return number is
416 --
417 cursor csr_input_value is
418 select input_value_id
419 from pay_input_values_f
420 where name = p_input_value_name
421 and element_type_id = p_element_type_id;
422 --
423 l_input_value csr_input_value%rowtype;
424 --
425 begin
426 --
427 open csr_input_value;
428 fetch csr_input_value into l_input_value;
429 close csr_input_value;
430 --
431 return l_input_value.input_value_id;
432 --
433 end element_input_value_id;
434 --------------------------------------------------------------------------------
435 function withholding_reason_id (
436 --
437 -- Returns the ID of a withholding reason, given a reason and element type id
438 --
439 p_element_type_id in number,
440 p_reason in varchar2
441 --
442 ) return number is
443 --
444 cursor csr_reason_id is
445 --
446 -- Get the reason id.
447 --
448 select reason_id
449 from ssp_withholding_reasons
450 where upper (reason) = upper (p_reason)
451 and element_type_id = p_element_type_id;
452 --
453 l_reason_id number;
454 --
455 begin
456 --
457 open csr_reason_id;
458 fetch csr_reason_id into l_reason_id;
459 --
460 if csr_reason_id%notfound then
461 --
462 fnd_message.set_name ('SSP','SSP_35046_NO_SUCH_REASON');
463 fnd_message.set_token ('REASON',p_reason);
464 fnd_message.raise_error;
465 --
466 end if;
467 --
468 close csr_reason_id;
469 --
470 return l_reason_id;
471 --
472 end withholding_reason_id;
473 --------------------------------------------------------------------------------
474 function stoppage_overridden (
475 --
476 -- Returns TRUE if there is a stoppage for the specified reason which has been
477 -- overridden by the user.
478 --
479 p_reason_id in number,
480 p_absence_attendance_id in number default null,
481 p_maternity_id in number default null
482 ) return boolean is
483 --
484 cursor csr_stoppage is
485 --
486 -- Return a row if there is a stoppage of the specified type for the
487 -- absence.
488 --
489 -- 563202 - by concatenating null to reason_id forces a more efficient index
490 -- to be used.
491 select 1
492 from ssp_stoppages
493 where ((p_absence_attendance_id is not null
494 and p_absence_attendance_id = absence_attendance_id)
495 or (p_maternity_id is not null
496 and p_maternity_id = maternity_id))
497 and reason_id||null = p_reason_id
498 and override_stoppage = 'Y';
499 --
500 l_dummy integer (1);
501 l_stoppage_overridden boolean := FALSE;
502 --
503 begin
504 --
505 open csr_stoppage;
506 fetch csr_stoppage into l_dummy;
507 l_stoppage_overridden := csr_stoppage%found;
508 close csr_stoppage;
509 --
510 return l_stoppage_overridden;
511 --
512 end stoppage_overridden;
513 --------------------------------------------------------------------------------
514 procedure recalculate_SSP_and_SMP (p_deleting in boolean default FALSE) is
515 --
516 -- Recalculate SSP and SMP for any rows affected by DML.
517 --
518 cursor csr_affected_maternities is
519 --
520 -- Find all maternity ids which have been inserted by row triggers
521 -- because a change to one of their SMP parameters occurred. Exclude
522 -- rows which are already being processed (because the process may
523 -- cause the row triggers to fire and call this procedure recursively),
524 -- and rows which are duplicated.
525 --
526 select tar1.maternity_id, nvl(tar1.p_deleting, 'N') l_deleting
527 from ssp_temp_affected_rows TAR1
528 where tar1.maternity_id is not null
529 --and nvl (tar1.locked, 'NULL') <> 'Y'
530 and tar1.locked = to_char(userenv('sessionid'))
531 for update;
532 --
533 cursor csr_leave_type (p_maternity_id number) is
534 select leave_type
535 from ssp_maternities
536 where maternity_id = p_maternity_id;
537
538 cursor csr_affected_PIWs is
539 --
540 -- Find all PIW IDs which have been inserted by row triggers
541 -- because a change to one of their SSP parameters occurred.
542 -- Exclusion of duplicates now done in ssp_ssp_pkg.SSP-Control as
543 -- the previous "not exists" did not work... the data is already
544 -- selected before they can be updated.
545 --
546 select tar1.PIW_id, nvl(tar1.locked,'NULL') locked
547 from ssp_temp_affected_rows TAR1
548 where tar1.PIW_id is not null
549 -- and nvl (tar1.locked,'NULL') <> 'Y'
550 and tar1.locked = to_char(userenv('sessionid'))
551 for update;
552 --
553 cursor csr_count_absences(p_maternity_id number) is
554 select count(*)
555 from ssp_maternities mat,
556 per_absence_attendances ab
557 where mat.maternity_id = p_maternity_id
558 and ab.person_id = mat.person_id
559 and ab.maternity_id = mat.maternity_id;
560 --
561 l_proc varchar2 (72) := g_package||'recalculate_SSP_and_SMa';
562 l_deleting boolean;
563 row_deleted exception;
564 l_count number;
565 l_leave_type varchar2 (2);
566 pragma exception_init (row_deleted, -8006);
567 --
568 mutating_table exception;
569 pragma exception_init (mutating_table, -4091);
570 --
571 -- Oracle error -4091 occurs if a trigger attempts to read or modify a table
572 -- which is being modified by the code which caused the trigger to fire.
573 --
574 BEGIN
575 --
576 hr_utility.set_location('Entering:'||l_proc,1);
577 --
578 if ssp_ssp_pkg.ssp_is_installed
579 then
580 if csr_affected_PIWs%IsOpen then
581 close csr_affected_PIWs;
582 end if;
583 --
584 if csr_affected_maternities%IsOpen then
585 close csr_affected_maternities;
586 end if;
587 --
588 -- Recalculate SSP for all PIWs affected by row inserts/updates/deletes
589 --
590 -- Make sure all the linked PIWs are correctly defined.
591 ssp_ssp_pkg.update_linked_absence_IDs;
592 --
593 for each_PIW in csr_affected_PIWs LOOP
594 hr_utility.trace (' Recalculate SSP for PIW #'||
595 to_char(each_PIW.PIW_ID));
596 --
597 if each_PIW.locked <> 'Y'
598 then
599 update ssp_temp_affected_rows
600 set locked = 'Y'
601 where current of csr_affected_PIWs;
602 --
603 ssp_ssp_pkg.ssp_control(each_PIW.piw_id);
604 end if;
605 end loop;
606 --
607 -- Recalculate SMP for all maternities affected by inserts/updates/deletes
608 --
609 for each_maternity in csr_affected_maternities LOOP
610 hr_utility.trace (' Recalculate SMP for maternity_id # '
611 ||to_char (each_maternity.maternity_id));
612 --
613 update ssp_temp_affected_rows
614 set locked = 'Y'
615 where current of csr_affected_maternities;
616 --
617 if each_maternity.l_deleting = 'Y'
618 then
619 l_deleting := TRUE;
620 else
621 l_deleting := FALSE;
622 end if;
623 open csr_leave_type(each_maternity.maternity_id) ;
624 fetch csr_leave_type into l_leave_type;
625 close csr_leave_type;
626
627 if l_leave_type is null or l_leave_type = 'MA' then
628 ssp_SMP_pkg.SMP_control (each_maternity.maternity_id, l_deleting);
629 elsif l_leave_type ='AD' then
630 ssp_sap_pkg.sap_control (each_maternity.maternity_id, l_deleting);
631 elsif l_leave_type ='PA' then
632 ssp_pad_pkg.pad_control (each_maternity.maternity_id, l_deleting);
633 elsif l_leave_type ='PB' then
634 ssp_pab_pkg.pab_control (each_maternity.maternity_id, l_deleting);
635 elsif l_leave_type ='AB' then -- Additional Paterntiy Pay Birth
636 ssp_apab_pkg.apab_control (each_maternity.maternity_id, l_deleting);
637 elsif l_leave_type ='AA' then -- Additional Paterntiy Pay Adoption
638 ssp_apad_pkg.apad_control (each_maternity.maternity_id, l_deleting);
639 end if;
640 --
641 -- RT entries prob
642 --
643 open csr_count_absences(each_maternity.maternity_id);
644 fetch csr_count_absences into l_count;
645 close csr_count_absences;
646
647 if l_deleting and l_count < 1
648 then
649 hr_utility.set_location('ssp_del_orphaned_rows:'||l_proc,50);
650 --
651 delete ssp_stoppages
652 where maternity_id = each_maternity.maternity_id;
653 end if;
654 end loop;
655 --
656 delete ssp_temp_affected_rows
657 where locked = to_char(userenv('sessionid'))
658 or locked is null
659 or locked not in (select to_char(AUDSID) from v$session);
660 end if;
661 --
662 hr_utility.set_location('Leaving :'||l_proc,100);
663 --
664 exception
665 --
666 when mutating_table or row_deleted then
667 --
668 -- If we get a mutating table restriction then we must be firing this code
669 -- recursively (eg the user deleted an absence which cascaded to delete the
670 -- stoppages for it; both the absence deletion and the stoppage deletion
671 -- causing this code to fire). If this occurs then we do not want the
672 -- second and subsequent calls to do anything so just exit silently.
673 --
674 null;
675 --
676 hr_utility.set_location (l_proc,999);
677 --
678 end recalculate_SSP_and_SMP;
679 ------------------------------------------------------------------------------
680 procedure get_entry_details(p_date_earned in date,
681 p_last_process_date in date,
682 p_person_id in number,
683 p_element_type_id in number,
684 p_element_link_id in out nocopy number,
685 p_assignment_id in out nocopy number,
686 p_effective_start_date out nocopy date,
687 p_effective_end_date out nocopy date,
688 p_pay_as_lump_sum in varchar2 default 'N') is
689 --
690 cannot_derive_payroll_period exception;
691 no_payroll exception;
692 --
693 l_found boolean := false;
694 --
695 l_temp_date date;
696 l_assignment_start date;
697 l_assignment_end date;
698 l_closed_period date;
699 l_effective_date date := null;
700 l_effective_start_date date := null;
701 l_effective_end_date date := null;
702 l_last_process_date date := nvl (p_last_process_date,hr_general.end_of_time);
703 --
704 l_payroll_id number := null;
705 --
706 --
707 l_proc varchar2(72) := g_package||'get_entry_details';
708 l_pay_as_lump_sum varchar2(1) := nvl (p_pay_as_lump_sum, 'N');
709 --
710 -- Get the user's effective date
711 cursor csr_effective_date is
712 select effective_date
713 from fnd_sessions
714 where session_id = userenv ('sessionid');
715 --
716 -- Get the details of the primary assignment as at the date
717 -- earned.
718 cursor csr_assignment(p_date date,
719 p_lsp date) is
720 select assignment_id,
721 payroll_id
722 from per_all_assignments_f
723 where person_id = p_person_id
724 and primary_flag = 'Y'
725 and assignment_type = 'E'
726 and least(p_date,p_lsp) between effective_start_date and effective_end_date;
727 --
728 -- Get Min(start), Max(end) of assignment on payroll x
729 cursor csr_assignment_duration(p_date date,
730 p_asg_id number,
731 p_pay_id number) is
732 select min(effective_start_date),
733 max(nvl(effective_end_date,hr_general.end_of_time))
734 from per_all_assignments_f
735 where assignment_id = p_asg_id
736 and primary_flag = 'Y'
737 and assignment_type = 'E'
738 and payroll_id = p_pay_id;
739 --
740 -- Get minimum closed period
741 cursor csr_minimum_closed(p_payroll_id number) is
742 select nvl(max(period.start_date),to_date('01/01/0001','DD/MM/YYYY'))
743 from per_time_periods period
744 where period.payroll_id = p_payroll_id
745 and period.prd_information_category = 'GB'
746 and period.prd_information1 = 'Closed';
747 --
748 -- Get period details
749 cursor csr_payroll_period is
750 select min(period.start_date),
751 min(nvl(period.end_date,hr_general.end_of_time))
752 from per_time_periods period
753 where period.payroll_id = l_payroll_id
754 and period.start_date > l_closed_period
755 --6959669 begin
756 -- and nvl(period.cut_off_date,period.end_date) <= l_last_process_date
757 and least(nvl(period.cut_off_date,period.end_date),period.end_date) <= l_last_process_date
758 --6959669 end
759 and (nvl(period.cut_off_date,period.end_date) >= l_effective_date
760 or ( l_effective_date > l_last_process_date
761 and l_last_process_date between period.start_date and period.end_date))
762 and ( l_pay_as_lump_sum = 'Y'
763 or( l_pay_as_lump_sum = 'N'
764 and ( p_date_earned <= period.end_date
765 or l_last_process_date < p_date_earned)));
766
767 --
768 procedure check_parameters is
769 all_parameters_valid constant boolean :=
770 (p_pay_as_lump_sum in ('Y','N') and
771 p_person_id is not null and
772 p_element_type_id is not null and
773 p_date_earned is not null and
774 p_date_earned = trunc (p_date_earned) and
775 p_last_process_date = trunc (p_last_process_date));
776 begin
777 hr_utility.trace (' p_date_earned = '||to_char (p_date_earned, 'DD-MON-YYYY'));
778 hr_utility.trace (' p_last_process_date = '||to_char (p_last_process_date));
779 hr_utility.trace (' p_person_id = ' ||to_char (p_person_id));
780 hr_utility.trace (' p_element_type_id = '||to_char (p_element_type_id));
781 hr_utility.trace (' p_element_link_id = '||to_char (p_element_link_id));
782 hr_utility.trace (' p_assignment_id = '||to_char (p_assignment_id));
783 hr_utility.trace (' p_pay_as_lump_sum = '||p_pay_as_lump_sum);
784 --
785 hr_general.assert_condition (all_parameters_valid);
786 --
787 end check_parameters;
788 --
789 begin
790 --
791 hr_utility.set_location ('Entering:'||l_proc,1);
792 --
793 check_parameters;
794 --
795 -- Get the effective date
796 open csr_effective_date;
797 fetch csr_effective_date into l_effective_date;
798 close csr_effective_date;
799 --
800 if l_effective_date is null then
801 l_effective_date := trunc (sysdate);
802 end if;
803 --
804 hr_utility.trace(l_proc||' effective date = '||to_char(l_effective_date, 'DD-MON-YYYY'));
805 --
806 -- 1. Find out which date to use when searching for payroll period
807 if l_pay_as_lump_sum = 'Y' then
808 l_temp_date := l_effective_date;
809 else
810 l_temp_date := greatest(p_date_earned,l_effective_date);
811 end if;
812
813 while not l_found loop
814 -- 2. Get assignemnt info as of l_temp_date
815 open csr_assignment(l_temp_date,l_last_process_date);
816 fetch csr_assignment into p_assignment_id, l_payroll_id;
817 close csr_assignment;
818
819 open csr_assignment_duration(l_temp_date,p_assignment_id,l_payroll_id);
820 fetch csr_assignment_duration into l_assignment_start,l_assignment_end;
821 close csr_assignment_duration;
822
823 open csr_minimum_closed(l_payroll_id);
824 fetch csr_minimum_closed into l_closed_period;
825 close csr_minimum_closed;
826
827 -- 3. Check payroll
828 if l_payroll_id is null then
829 raise no_payroll;
830 end if;
831
832 -- 4. Get period details
833 open csr_payroll_period;
834 fetch csr_payroll_period into l_effective_start_date,
835 l_effective_end_date;
836 close csr_payroll_period;
837
838 -- 5. Check that the period is valid for assignment
839 if l_assignment_start > l_effective_start_date then
840 l_effective_start_date := l_assignment_start;
841 -- bug fix 5346648 - If asg start date is more than the period end date
842 -- then we have to fetch the next period
843 if l_assignment_start >= l_effective_end_date then
844 l_effective_date := l_assignment_start;
845 open csr_payroll_period;
846 fetch csr_payroll_period into l_effective_start_date,
847 l_effective_end_date;
848 close csr_payroll_period;
849 end if;
850 end if;
851
852 -- 6. If cannot find any period then, error out
853 if (l_effective_start_date is null or l_effective_end_date is null) then
854 raise cannot_derive_payroll_period;
855 else
856 -- period is found but is it > assignment end date
857 if l_assignment_end < l_effective_start_date then
858 l_temp_date := l_effective_start_date;
859 else
860 l_found := true;
861 p_effective_start_date := l_effective_start_date;
862 p_effective_end_date := l_effective_end_date;
863 hr_utility.trace(l_proc||' l_effective_start_date = ' ||to_char(l_effective_start_date, 'DD-MON-YYYY'));
864 hr_utility.trace(l_proc||' l_effective_end_date = ' ||to_char(l_effective_end_date, 'DD-MON-YYYY'));
865 end if;
866 end if;
867 end loop;
868 --
869 -- 7. Fetching the link based on the effective start date of the entry.
870 p_element_link_id := hr_entry_api.get_link (p_assignment_id => p_assignment_id,
871 p_element_type_id => p_element_type_id,
872 p_session_date => p_effective_start_date);
873 --
874 hr_utility.set_location ('Leaving :'||l_proc,1);
875 --
876 exception
877 when cannot_derive_payroll_period then
878 fnd_message.set_name ('SSP', 'SSP_35029_NO_PAYROLL_PERIOD');
879 fnd_message.raise_error;
880 when no_payroll then
881 fnd_message.set_name ('SSP','SSP_35080_NO_PAYROLL');
882 fnd_message.set_token ('DATE_EARNED',p_date_earned);
883 fnd_message.raise_error;
884 end get_entry_details;
885 --------------------------------------------------------------------------------
886 function average_earnings_error return varchar2 is
887 --
888 -- Returns the withholding reason corresponding to the error which was raised
889 -- by the procedure to calculate average earnings in the event that the figure
890 -- could not be derived. This withholding reason may be used to create a
891 -- stoppage of SSP/SMP thus indicating why the entries were not created.
892 --
893 -- The message is defaulted only if ssp_smp_support_pkg returns a
894 -- null value. This ocurs when a commit is fired within the Absence
895 -- form, as the recalculate_ssp_and_smp is fired and does not create a
896 -- 'reason for no earnings', this is only created when
897 -- calculate_average_earnings is used.
898 --
899
900 l_message_name_on_stack varchar2 (80) := ssp_smp_support_pkg.reason_for_no_earnings;
901 l_withholding_reason varchar2 (80) := null;
902 --
903 begin
904 --
905 if l_message_name_on_stack = 'SSP_35024_NEED_PAYROLL_FOR_ERN' then
906 --
907 -- Message is:
908 -- "Average Earnings cannot be calculated automatically unless
909 -- you have installed Oracle Payroll. You must enter the figure
910 -- yourself."
911 --
912 l_withholding_reason := 'Payroll not installed';
913 --
914 elsif l_message_name_on_stack = 'SSP_35026_NO_NEW_EMP_EARNINGS' then
915 --
916 -- Message is:
917 -- "Oracle Payroll cannot derive average earnings for employees who
918 -- have not yet received any pay on which to base a calculation.
919 -- Please enter the average earnings figure yourself, based upon the
920 -- employee's contracted weekly earnings."
921 --
922 l_withholding_reason := 'Cannot derive new emps pay';
923 --
924 -- (abhaduri) added to inform about employees re-hired
925 -- within 8 weeks of their termination
926 elsif l_message_name_on_stack = 'SSP_36076_EMP_REHIRED' then
927 --
928 -- Message is:
929 -- "Average earnings cannot be calculated as the employee is new.
930 -- Please take into account the previous period payment while
931 -- calculating average earnings manually."
932 --
933 l_withholding_reason :='Re-hired employee,please check';
934 --
935 elsif l_message_name_on_stack = 'SSP_35025_NO_DIRECTOR_EARNINGS' then
936 --
937 -- Message is:
938 -- "Oracle Payroll is unable to calculate the earnings of directors
939 -- because it has no way to distinguish between voted fees and fees
940 -- drawn in anticipation of voting. Please enter average earnings
941 -- for directors yourself."
942 --
943 l_withholding_reason := 'Cannot derive directors pay';
944 --
945 --
946 elsif l_message_name_on_stack IS NULL then
947 --
948 l_withholding_reason := 'Cannot derive new emps pay';
949 --
950 end if;
951 --
952 -- Reset global variable to avoid later confusion
953 ssp_smp_support_pkg.reason_for_no_earnings := null;
954 --
955 return l_withholding_reason;
956 --
957 end average_earnings_error;
958
959 --------------------------------------------------------------------------------
960 --
961 procedure update_ssp_smp_entries (P_UPDATE_ERROR OUT NOCOPY boolean) is
962
963
964 cursor csr_affected_absences is
965 select nvl(paa.linked_absence_id,paa.absence_attendance_id) as absence_id,
966 paa.person_id,
967 nvl(paa.date_start,paa.sickness_start_date) as absence_start_date,
968 nvl(paa.date_end,paa.sickness_end_date) as absence_end_date
969 from per_absence_attendances paa,
970 per_absence_attendance_types paat
971 where paa.absence_attendance_type_id = paat.absence_attendance_type_id
972 and paat.absence_category = 'S'
973 and
974 /* SSP absences which span tax years or start in old tax year and are open-ended */
975 (
976 ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
977 and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
978 )
979 or
980 ((paa.sickness_start_date between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
981 and (paa.sickness_end_date > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.sickness_end_date is null)
982 )
983 or
984 /* SSP absences which start in the new tax year */
985 (
986 paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
987 or paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
988 )
989 )
990 /* Do not retrieve terminated employees whose actual termination dates have passed or are null */
991 and not exists
992 ( select 1
993 from per_all_people_f ppf,
994 per_person_types ppt,
995 per_periods_of_service pps
996 where ppf.person_id = pps.person_id
997 and ppt.person_type_id = ppf.person_type_id
998 and ppt.system_person_type = 'EX_EMP'
999 and nvl(pps.actual_termination_date,to_date('01/01/0001','DD/MM/YYYY')) <= sysdate
1000 and ppf.person_id = paa.person_id
1001 and pps.date_start = (select max(date_start)
1002 from per_periods_of_service pos
1003 where pos.person_id = pps.person_id)
1004 and ppf.effective_start_date >= pps.date_start
1005 and pps.date_start <= paa.sickness_start_date)
1006 order by nvl(paa.linked_absence_id,paa.absence_attendance_id);
1007
1008
1009 cursor csr_affected_leave(p_leave_type IN VARCHAR2) is
1010 -- p_leave_type = 'MA' - Maternity, 'AD' - Adoption, 'PA' - Paternity Adoption, 'PB' - Paternity Birth, 'AB'- Additional Paternity Birth, 'AA'- Additional Paternity Adoption
1011 select paa.maternity_id,
1012 paa.person_id,
1013 paa.date_start,
1014 paa.date_end,
1015 paa.date_projected_start,
1016 paa.date_projected_end
1017 from per_absence_attendances paa,
1018 ssp_maternities mat
1019 where paa.maternity_id = mat.maternity_id
1020 and nvl(mat.leave_type, 'MA') = p_leave_type
1021 and
1022 /* SMP absences which span tax years or start in old tax year and are open-ended */
1023 (
1024 ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
1025 and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
1026 )
1027 or
1028 ((paa.date_projected_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
1029 and (paa.date_projected_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_projected_end is null)
1030 and paa.date_start IS NULL -- use projected dates only when actual dates not available
1031 )
1032 or
1033 /* SMP absences which start in the new tax year */
1034 (
1035 paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
1036 or (paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
1037 and paa.date_start IS NULL) -- use projected dates only when actual dates not available
1038 )
1039 )
1040 /* Do not retrieve employees whose final process dates have passed */
1041 and not exists
1042 ( select 1
1043 from per_all_people_f ppf,
1044 per_person_types ppt,
1045 per_periods_of_service pps
1046 where ppf.person_id = pps.person_id
1047 and ppt.person_type_id = ppf.person_type_id
1048 and ppt.system_person_type = 'EX_EMP'
1049 and pps.final_process_date <= sysdate
1050 and ppf.person_id = paa.person_id
1051 and pps.date_start = (select max(date_start)
1052 from per_periods_of_service pos
1053 where pos.person_id = pps.person_id)
1054 and ppf.effective_start_date >= pps.date_start
1055 and pps.date_start <= paa.date_start)
1056 /* Do not retrieve employees who are deceased */
1057 and not exists
1058 ( select 1
1059 from per_all_people_f ppf,
1060 per_periods_of_service pps
1061 where ppf.person_id = pps.person_id
1062 and pps.leaving_reason = 'D'
1063 and ppf.person_id = paa.person_id)
1064 order by paa.maternity_id;
1065
1066
1067 l_count NUMBER := 0;
1068 l_mat_count NUMBER := 0;
1069 l_adop_count NUMBER := 0;
1070 l_pat_adop_count NUMBER := 0;
1071 l_pat_birth_count NUMBER := 0;
1072
1073 /**/
1074 l_apad_count NUMBER :=0;
1075 l_apab_count NUMBER :=0;
1076
1077 e_ssp_rate_not_set exception;
1078 e_smp_rate_not_set exception;
1079 e_sap_rate_not_set exception;
1080 e_sppa_rate_not_set exception;
1081 e_sppb_rate_not_set exception;
1082 e_asppa_rate_not_set exception;
1083 e_asppb_rate_not_set exception;
1084
1085 e_no_new_ssp_entry exception;
1086 e_no_new_smp_entry exception;
1087 e_no_new_sap_entry exception;
1088 e_no_new_sppa_entry exception;
1089 e_no_new_sppb_entry exception;
1090 e_no_new_asppa_entry exception;
1091 e_no_new_asppb_entry exception;
1092
1093 l_update_error boolean := FALSE;
1094
1095 /* Function to check if SSP entries in new tax year have already been updated */
1096 /* with new SSP rates and recalculated - returns TRUE if so, FALSE if not */
1097 function ssp_entries_already_updated return boolean is
1098
1099 cursor csr_new_ssp_rate is
1100 select piv.default_value
1101 from pay_input_values_f piv,
1102 pay_element_types_f petf
1103 where petf.element_type_id = piv.element_type_id
1104 and piv.name = 'Rate'
1105 and petf.element_name = 'Statutory Sick Pay'
1106 and piv.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate);
1107
1108
1109 cursor csr_first_new_ssp_entry is
1110 select peev1.screen_entry_value
1111 from pay_element_entry_values_f peev1,
1112 pay_element_entry_values_f peev2,
1113 pay_input_values_f piv,
1114 pay_element_entries_f peef,
1115 pay_element_links_f pelf,
1116 pay_element_types_f petf
1117 where piv.input_value_id = peev1.input_value_id
1118 and peev1.element_entry_id = peev2.element_entry_id
1119 and peev1.element_entry_id = peef.element_entry_id
1120 and peef.element_link_id = pelf.element_link_id
1121 and pelf.element_type_id = petf.element_type_id
1122 and piv.name = 'Rate'
1123 and petf.element_name = 'Statutory Sick Pay'
1124 and piv.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate)
1125 and peev2.element_entry_value_id =
1126 (select peev3.element_entry_value_id
1127 from pay_element_entry_values_f peev3
1128 where input_value_id =
1129 (select distinct input_value_id
1130 from pay_input_values_f piv,
1131 pay_element_types_f petf
1132 where petf.element_type_id = piv.element_type_id
1133 and petf.element_name = 'Statutory Sick Pay'
1134 and piv.name = 'From')
1135 and peev3.screen_entry_value >
1136 fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate))
1137 /* Retrieve only those entries that will be retrieved by main SSP query */
1138 and peev3.element_entry_id in
1139 ( select peef1.element_entry_id
1140 from pay_element_entries_f peef1,
1141 per_all_assignments_f paf,
1142 per_all_people_f ppf,
1143 per_person_types ppt,
1144 per_periods_of_service pps
1145 where peef1.assignment_id = paf.assignment_id
1146 and paf.person_id = ppf.person_id
1147 and ppf.person_id = pps.person_id
1148 and ppt.person_type_id = ppf.person_type_id
1149 and peev3.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1150 and fnd_date.date_to_canonical(ppf.effective_end_date)
1151 and peev3.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1152 and fnd_date.date_to_canonical(paf.effective_end_date)
1153 and ppt.system_person_type = 'EMP'
1154 and nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1155 )
1156 and rownum = 1);
1157
1158
1159 l_new_SSP_rate number;
1160 l_first_SSP_entry_rate number;
1161
1162
1163
1164 begin
1165
1166 hr_utility.trace('Entering ssp_entries_already_updated function');
1167
1168 /* Find SSP rate for new tax year */
1169 open csr_new_ssp_rate;
1170 fetch csr_new_ssp_rate into l_new_SSP_rate;
1171
1172 /* If unable to find new SSP rate, then rate probably not set yet */
1173 /* Make note of this and exit quietly */
1174 if csr_new_ssp_rate%notfound
1175 then
1176 close csr_new_ssp_rate;
1177 raise e_ssp_rate_not_set;
1178 end if;
1179
1180 close csr_new_ssp_rate;
1181
1182 hr_utility.trace('New SSP rate: '||l_new_SSP_rate);
1183
1184 /* Find first element entry value holding SSP rate for new tax year */
1185 open csr_first_new_ssp_entry;
1186 fetch csr_first_new_ssp_entry into l_first_SSP_entry_rate;
1187
1188 /* If unable to find SSP entry in new tax year, then warn user */
1189 /* Possible causes are employee terminations or stoppages */
1190 if csr_first_new_ssp_entry%notfound
1191 then
1192 close csr_first_new_ssp_entry;
1193 raise e_no_new_ssp_entry;
1194 end if;
1195
1196 close csr_first_new_ssp_entry;
1197
1198 hr_utility.trace('First SSP entry rate: '||l_first_SSP_entry_rate);
1199
1200 if l_new_SSP_rate = l_first_SSP_entry_rate
1201 then
1202
1203 return true;
1204
1205 else
1206
1207 return false;
1208
1209 end if;
1210
1211 exception
1212
1213 when others
1214 then raise;
1215
1216
1217 end ssp_entries_already_updated;
1218
1219 /* Function to check if SMP entries in new tax year have already updated */
1220 /* with new SMP rates and recalculated - returns TRUE if so, FALSE if not */
1221 function smp_rate_changed return boolean is
1222
1223 /* Check for SMP rates beginning on or after April 1st */
1224 cursor csr_new_smp_rate is
1225 select petf.element_information10, petf.element_information9, petf.element_information16
1226 from pay_element_types_f petf
1227 where petf.element_name = 'Statutory Maternity Pay'
1228 and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1229 order by effective_start_date;
1230
1231
1232 /* 09/12/2003 rmakhija - commented out following cursor because element entry value
1233 for 'Amount' is a calculated value and it is not same
1234 as smp rate therefore it can not be compared with new smp
1235 rate to detect the change in smp rate. A new cursor
1236 csr_old_smp_rate has been added to find old smp rate
1237 and to compare it with new smp rate to detect the change.
1238
1239 cursor csr_first_new_smp_entry is
1240 select peev1.screen_entry_value
1241 from pay_element_entry_values_f peev1,
1242 pay_element_entry_values_f peev2,
1243 pay_element_entry_values_f peev3,
1244 pay_input_values_f piv,
1245 pay_element_entries_f peef,
1246 pay_element_links_f pelf,
1247 pay_element_types_f petf
1248 where piv.input_value_id = peev1.input_value_id
1249 and peev1.element_entry_id = peev2.element_entry_id
1250 and peev1.element_entry_id = peev3.element_entry_id
1251 and peev1.element_entry_id = peef.element_entry_id
1252 and peef.element_link_id = pelf.element_link_id
1253 and pelf.element_type_id = petf.element_type_id
1254 and piv.name = 'Amount'
1255 and petf.element_name = 'Statutory Maternity Pay'
1256 and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
1257 -- Time restriction - only rows after tax year end
1258 and peev2.input_value_id =
1259 (select input_value_id
1260 from pay_input_values_f piv
1261 where petf.element_type_id = piv.element_type_id
1262 and upper(piv.name) = upper('Week commencing')
1263 )
1264 and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
1265 -- Retrieve only those entries that main SMP query will retrieve
1266 and exists
1267 ( select 1
1268 from per_all_assignments_f paf,
1269 per_all_people_f ppf,
1270 per_person_types ppt,
1271 per_periods_of_service pps
1272 where peef.assignment_id = paf.assignment_id
1273 and paf.person_id = ppf.person_id
1274 and ppf.person_id = pps.person_id
1275 and ppt.person_type_id = ppf.person_type_id
1276 and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1277 and fnd_date.date_to_canonical(ppf.effective_end_date)
1278 and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1279 and fnd_date.date_to_canonical(paf.effective_end_date)
1280 and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1281 )
1282 and peev3.input_value_id =
1283 (select input_value_id
1284 from pay_input_values_f piv
1285 where petf.element_type_id = piv.element_type_id
1286 and upper(piv.name) = upper('Rate'))
1287 -- Rate restriction - only retrieve entries on LOW rate for SMP
1288 and upper(peev3.screen_entry_value) = upper('Low')
1289 -- Get first row that matches all of the above criteria
1290 and rownum = 1;
1291 */
1292
1293 cursor csr_old_smp_rate is
1294 select petf.element_information10, petf.element_information9, petf.element_information16
1295 from pay_element_types_f petf
1296 where petf.element_name = 'Statutory Maternity Pay'
1297 and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1298 order by effective_start_date desc;
1299
1300 l_new_SMP_rate number;
1301 l_old_SMP_rate number;
1302 l_new_high_smp_rate number;
1303 l_old_high_smp_rate number;
1304 l_new_std_smp_rate number;
1305 l_old_std_smp_rate number;
1306
1307 begin
1308
1309 hr_utility.trace('Entering smp_rate_changed function');
1310 --
1311 /* Find SMP rate for new tax year */
1312 open csr_new_smp_rate;
1313 fetch csr_new_smp_rate into l_new_SMP_rate, l_new_high_smp_rate, l_new_std_smp_rate;
1314 --
1315 /* If unable to find new SMP rate, then rate probably not set yet */
1316 /* Make note of this and exit quietly */
1317 if csr_new_SMP_rate%notfound
1318 then
1319 close csr_new_SMP_rate;
1320 raise e_SMP_rate_not_set;
1321 end if;
1322 --
1323 close csr_new_smp_rate;
1324 --
1325 hr_utility.trace('New Lower SMP rate: '||l_new_SMP_rate);
1326 hr_utility.trace('New Higher SMP rate: '||l_new_high_SMP_rate);
1327 hr_utility.trace('New Standard SMP rate: '||l_new_std_SMP_rate);
1328 --
1329 /* Find SMP rate for current tax year */
1330 open csr_old_smp_rate;
1331 fetch csr_old_smp_rate into l_old_SMP_rate, l_old_high_smp_rate, l_old_std_smp_rate;
1332 close csr_old_smp_rate;
1333 --
1334 hr_utility.trace('Old SMP rate: '||l_old_SMP_rate);
1335 hr_utility.trace('Old Higher SMP rate: '||l_old_high_SMP_rate);
1336 hr_utility.trace('Old Standard SMP rate: '||l_old_std_SMP_rate);
1337 --
1338 if (l_new_SMP_rate = l_old_smp_rate) and (l_new_high_smp_rate = l_old_high_smp_rate) and (l_new_std_smp_rate = l_old_std_smp_rate)
1339 then
1340 return false;
1341 else
1342 return true;
1343 end if;
1344 --
1345 exception
1346 when others then
1347 raise;
1348 end smp_rate_changed;
1349
1350
1351 /* Function to check if SAP entries in new tax year have already updated */
1352 /* with new SAP rates and recalculated - returns TRUE if so, FALSE if not */
1353 function sap_rate_changed return boolean is
1354
1355 /* Check for SAP rates beginning on or after April 1st */
1356 cursor csr_new_sap_rate is
1357 select petf.element_information5, petf.element_information7
1358 from pay_element_types_f petf
1359 where petf.element_name = 'Statutory Adoption Pay'
1360 and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1361 order by effective_start_date;
1362 --
1363 /* 09/12/2003 rmakhija - commented out following cursor because element entry value
1364 for 'Amount' is a calculated value and it is not same
1365 as sap rate therefore it can not be compared with new sap
1366 rate to detect the change in sap rate. A new cursor
1367 csr_old_sap_rate has been added to find old sap rate
1368 and to compare it with new sap rate to detect the change.
1369
1370 cursor csr_first_new_sap_entry is
1371 select peev1.screen_entry_value
1372 from pay_element_entry_values_f peev1,
1373 pay_element_entry_values_f peev2,
1374 pay_input_values_f piv,
1375 pay_element_entries_f peef,
1376 pay_element_links_f pelf,
1377 pay_element_types_f petf
1378 where piv.input_value_id = peev1.input_value_id
1379 and peev1.element_entry_id = peev2.element_entry_id
1380 and peev1.element_entry_id = peef.element_entry_id
1381 and peef.element_link_id = pelf.element_link_id
1382 and pelf.element_type_id = petf.element_type_id
1383 and piv.name = 'Amount'
1384 and petf.element_name = 'Statutory Adoption Pay'
1385 and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
1386 -- Time restriction - only rows after tax year end
1387 and peev2.input_value_id =
1388 (select input_value_id
1389 from pay_input_values_f piv
1390 where petf.element_type_id = piv.element_type_id
1391 and upper(piv.name) = upper('Week commencing')
1392 )
1393 and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
1394 -- Retrieve only those entries that main sap query will retrieve
1395 and exists
1396 ( select 1
1397 from per_all_assignments_f paf,
1398 per_all_people_f ppf,
1399 per_person_types ppt,
1400 per_periods_of_service pps
1401 where peef.assignment_id = paf.assignment_id
1402 and paf.person_id = ppf.person_id
1403 and ppf.person_id = pps.person_id
1404 and ppt.person_type_id = ppf.person_type_id
1405 and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1406 and fnd_date.date_to_canonical(ppf.effective_end_date)
1407 and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1408 and fnd_date.date_to_canonical(paf.effective_end_date)
1409 and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1410 )
1411 -- Get first row that matches all of the above criteria
1412 and rownum = 1;
1413 */
1414 --
1415 cursor csr_old_sap_rate is
1416 select petf.element_information5, petf.element_information7
1417 from pay_element_types_f petf
1418 where petf.element_name = 'Statutory Adoption Pay'
1419 and petf.effective_start_date < hr_gbnicar.uk_tax_yr_end(sysdate) - 4
1420 order by effective_start_date desc;
1421 --
1422 --
1423 l_new_sap_rate number;
1424 l_old_sap_rate number;
1425 --
1426 l_new_std_sap_rate number;
1427 l_old_std_sap_rate number;
1428 --
1429 begin
1430 --
1431 hr_utility.trace('Entering sap_rate_changed function');
1432 --
1433 /* Find sap rate for new tax year */
1434 open csr_new_sap_rate;
1435 fetch csr_new_sap_rate into l_new_sap_rate, l_new_std_sap_rate;
1436 --
1437 /* If unable to find new sap rate, then rate probably not set yet */
1438 /* Make note of this and exit quietly */
1439 if csr_new_sap_rate%notfound then
1440 close csr_new_sap_rate;
1441 raise e_sap_rate_not_set;
1442 end if;
1443 --
1444 close csr_new_sap_rate;
1445 --
1446 hr_utility.trace('New sap rate: '||l_new_sap_rate);
1447 hr_utility.trace('New std sap rate: '||l_new_std_sap_rate);
1448 --
1449 /* Find sap rate for current tax year */
1450 open csr_old_sap_rate;
1451 fetch csr_old_sap_rate into l_old_sap_rate, l_old_std_sap_rate;
1452 close csr_old_sap_rate;
1453 --
1454 hr_utility.trace('Old sap rate: '||l_old_sap_rate);
1455 hr_utility.trace('Old std sap rate: '||l_old_std_sap_rate);
1456 --
1457 if (l_new_sap_rate = l_old_sap_rate) and (l_new_std_sap_rate = l_old_std_sap_rate) then
1458 return false;
1459 else
1460 return true;
1461 end if;
1462 --
1463 exception
1464 when others then raise;
1465 end sap_rate_changed;
1466
1467 /* Function to check if SPPA entries in new tax year have already updated */
1468 /* with new SPPA rates and recalculated - returns TRUE if so, FALSE if not */
1469 function sppa_rate_changed return boolean is
1470
1471 /* Check for SPPA rates beginning on or after April 1st */
1472 cursor csr_new_sppa_rate is
1473 select petf.element_information6, petf.element_information8
1474 from pay_element_types_f petf
1475 where petf.element_name = 'Statutory Paternity Pay Adoption'
1476 and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1477 order by effective_start_date;
1478 --
1479 /* 09/12/2003 rmakhija - commented out following cursor because element entry value
1480 for 'Amount' is a calculated value and it is not same
1481 as SPP Adoption rate therefore it can not be compared with new
1482 SPP Adoption rate to detect the change. A new cursor
1483 csr_old_sppa_rate has been added to find old SPP Adoption rate
1484 and to compare it with new SPP Adoption rate to detect the change.
1485
1486 cursor csr_first_new_sppa_entry is
1487 select peev1.screen_entry_value
1488 from pay_element_entry_values_f peev1,
1489 pay_element_entry_values_f peev2,
1490 pay_input_values_f piv,
1491 pay_element_entries_f peef,
1492 pay_element_links_f pelf,
1493 pay_element_types_f petf
1494 where piv.input_value_id = peev1.input_value_id
1495 and peev1.element_entry_id = peev2.element_entry_id
1496 and peev1.element_entry_id = peef.element_entry_id
1497 and peef.element_link_id = pelf.element_link_id
1498 and pelf.element_type_id = petf.element_type_id
1499 and piv.name = 'Amount'
1500 and petf.element_name = 'Statutory Paternity Pay Adoption'
1501 and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
1502 -- Time restriction - only rows after tax year end
1503 and peev2.input_value_id =
1504 (select input_value_id
1505 from pay_input_values_f piv
1506 where petf.element_type_id = piv.element_type_id
1507 and upper(piv.name) = upper('Week commencing')
1508 )
1509 and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
1510 -- Retrieve only those entries that main sppa query will retrieve
1511 and exists
1512 ( select 1
1513 from per_all_assignments_f paf,
1514 per_all_people_f ppf,
1515 per_person_types ppt,
1516 per_periods_of_service pps
1517 where peef.assignment_id = paf.assignment_id
1518 and paf.person_id = ppf.person_id
1519 and ppf.person_id = pps.person_id
1520 and ppt.person_type_id = ppf.person_type_id
1521 and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1522 and fnd_date.date_to_canonical(ppf.effective_end_date)
1523 and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1524 and fnd_date.date_to_canonical(paf.effective_end_date)
1525 and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1526 )
1527 -- Get first row that matches all of the above criteria
1528 and rownum = 1;
1529 */
1530 --
1531 cursor csr_old_sppa_rate is
1532 select petf.element_information6, petf.element_information8
1533 from pay_element_types_f petf
1534 where petf.element_name = 'Statutory Paternity Pay Adoption'
1535 and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1536 order by effective_start_date desc;
1537
1538 --
1539 l_new_sppa_rate number;
1540 l_old_sppa_rate number;
1541 --
1542 l_new_std_sppa_rate number;
1543 l_old_std_sppa_rate number;
1544 --
1545 begin
1546 --
1547 hr_utility.trace('Entering sppa_rate_changed function');
1548 --
1549 /* Find sppa rate for new tax year */
1550 open csr_new_sppa_rate;
1551 fetch csr_new_sppa_rate into l_new_sppa_rate, l_new_std_sppa_rate;
1552 --
1553 /* If unable to find new sppa rate, then rate probably not set yet */
1554 /* Make note of this and exit quietly */
1555 if csr_new_sppa_rate%notfound then
1556 close csr_new_sppa_rate;
1557 raise e_sppa_rate_not_set;
1558 end if;
1559 --
1560 close csr_new_sppa_rate;
1561 --
1562 hr_utility.trace('New sppa rate: '||l_new_sppa_rate);
1563 hr_utility.trace('New std sppa rate: '||l_new_std_sppa_rate);
1564 --
1565 open csr_old_sppa_rate;
1566 fetch csr_old_sppa_rate into l_old_sppa_rate, l_old_std_sppa_rate;
1567 close csr_old_sppa_rate;
1568 --
1569 hr_utility.trace('Old SPPA rate: '||l_old_sppa_rate);
1570 hr_utility.trace('Old Std SPPA rate: '||l_old_std_sppa_rate);
1571 --
1572 if (l_new_sppa_rate = l_old_sppa_rate) and (l_new_std_sppa_rate = l_old_std_sppa_rate) then
1573 return false;
1574 else
1575 return true;
1576 end if;
1577 --
1578 exception
1579 when others then raise;
1580 end sppa_rate_changed;
1581
1582 /* Function to check if SPPB entries in new tax year have already updated */
1583 /* with new SPPB rates and recalculated - returns TRUE if so, FALSE if not */
1584 function sppb_rate_changed return boolean is
1585
1586 /* Check for SPPB rates beginning on or after April 1st */
1587 cursor csr_new_sppb_rate is
1588 select petf.element_information6, petf.element_information9
1589 from pay_element_types_f petf
1590 where petf.element_name = 'Statutory Paternity Pay Birth'
1591 and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1592 order by effective_start_date;
1593 --
1594 /* 09/12/2003 rmakhija - commented out following cursor because element entry value
1595 for 'Amount' is a calculated value and it is not same
1596 as SPP Birth rate therefore it can not be compared with new
1597 SPP Birth rate to detect the change. A new cursor
1598 csr_old_sppb_rate has been added to find old SPP Birth rate
1599 and to compare it with new SPP Birth rate to detect the change.
1600
1601 cursor csr_first_new_sppb_entry is
1602 select peev1.screen_entry_value
1603 from pay_element_entry_values_f peev1,
1604 pay_element_entry_values_f peev2,
1605 pay_input_values_f piv,
1606 pay_element_entries_f peef,
1607 pay_element_links_f pelf,
1608 pay_element_types_f petf
1609 where piv.input_value_id = peev1.input_value_id
1610 and peev1.element_entry_id = peev2.element_entry_id
1611 and peev1.element_entry_id = peef.element_entry_id
1612 and peef.element_link_id = pelf.element_link_id
1613 and pelf.element_type_id = petf.element_type_id
1614 and piv.name = 'Amount'
1615 and petf.element_name = 'Statutory Paternity Pay Birth'
1616 and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
1617 -- Time restriction - only rows after tax year end
1618 and peev2.input_value_id =
1619 (select input_value_id
1620 from pay_input_values_f piv
1621 where petf.element_type_id = piv.element_type_id
1622 and upper(piv.name) = upper('Week commencing')
1623 )
1624 and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
1625 -- Retrieve only those entries that main SPPB query will retrieve
1626 and exists
1627 ( select 1
1628 from per_all_assignments_f paf,
1629 per_all_people_f ppf,
1630 per_person_types ppt,
1631 per_periods_of_service pps
1632 where peef.assignment_id = paf.assignment_id
1633 and paf.person_id = ppf.person_id
1634 and ppf.person_id = pps.person_id
1635 and ppt.person_type_id = ppf.person_type_id
1636 and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1637 and fnd_date.date_to_canonical(ppf.effective_end_date)
1638 and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1639 and fnd_date.date_to_canonical(paf.effective_end_date)
1640 and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1641 )
1642 -- Get first row that matches all of the above criteria
1643 and rownum = 1;
1644 */
1645 --
1646 cursor csr_old_sppb_rate is
1647 select petf.element_information6, petf.element_information9
1648 from pay_element_types_f petf
1649 where petf.element_name = 'Statutory Paternity Pay Birth'
1650 and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1651 order by effective_start_date desc;
1652 --
1653 l_new_sppb_rate number;
1654 l_old_sppb_rate number;
1655 --
1656 l_new_std_sppb_rate number;
1657 l_old_std_sppb_rate number;
1658 --
1659 begin
1660 --
1661 hr_utility.trace('Entering sppb_rate_changed function');
1662 --
1663 /* Find SPPB rate for new tax year */
1664 open csr_new_sppb_rate;
1665 fetch csr_new_sppb_rate into l_new_sppb_rate, l_new_std_sppb_rate;
1666 --
1667 /* If unable to find new SPPB rate, then rate probably not set yet */
1668 /* Make note of this and exit quietly */
1669 if csr_new_sppb_rate%notfound then
1670 close csr_new_sppb_rate;
1671 raise e_sppb_rate_not_set;
1672 end if;
1673 --
1674 close csr_new_sppb_rate;
1675 --
1676 hr_utility.trace('New SPPB rate: '||l_new_sppb_rate);
1677 hr_utility.trace('New Std SPPB rate: '||l_new_Std_sppb_rate);
1678 --
1679 open csr_old_sppb_rate;
1680 fetch csr_old_sppb_rate into l_old_sppb_rate, l_old_std_sppb_rate;
1681 close csr_old_sppb_rate;
1682 --
1683 hr_utility.trace('old SPPB rate: '||l_old_sppb_rate);
1684 hr_utility.trace('old Std SPPB rate: '||l_old_Std_sppb_rate);
1685 --
1686 if (l_new_sppb_rate = l_old_sppb_rate) and (l_new_std_sppb_rate = l_old_std_sppb_rate) then
1687 return false;
1688 else
1689 return true;
1690 end if;
1691 --
1692 exception
1693 when others then raise;
1694 end sppb_rate_changed;
1695
1696
1697 /*Start of Bug 13400812*/
1698 function asppb_rate_changed return boolean is
1699
1700 /* Check for ASPPB rates beginning on or after April 1st */
1701 cursor csr_new_asppb_rate is
1702 select petf.element_information6, petf.element_information9
1703 from pay_element_types_f petf
1704 where petf.element_name = 'Additional Statutory Paternity Pay Birth'
1705 and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1706 order by effective_start_date;
1707
1708
1709 cursor csr_old_asppb_rate is
1710 select petf.element_information6, petf.element_information9
1711 from pay_element_types_f petf
1712 where petf.element_name = 'Additional Statutory Paternity Pay Birth'
1713 and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1714 order by effective_start_date desc;
1715 --
1716 l_new_asppb_rate number;
1717 l_old_asppb_rate number;
1718 --
1719 l_new_std_asppb_rate number;
1720 l_old_std_asppb_rate number;
1721 --
1722 begin
1723 --
1724 hr_utility.trace('Entering asppb_rate_changed function');
1725 --
1726 /* Find SPPB rate for new tax year */
1727 open csr_new_asppb_rate;
1728 fetch csr_new_asppb_rate into l_old_asppb_rate, l_new_std_asppb_rate;
1729 --
1730 /* If unable to find new ASPPB rate, then rate probably not set yet */
1731 /* Make note of this and exit quietly */
1732 if csr_new_asppb_rate%notfound then
1733 close csr_new_asppb_rate;
1734 raise e_asppb_rate_not_set;
1735 end if;
1736 --
1737 close csr_new_asppb_rate;
1738 --
1739 hr_utility.trace('New ASPPB rate: '||l_new_asppb_rate);
1740 hr_utility.trace('New Std ASPPB rate: '||l_new_std_asppb_rate);
1741 --
1742 open csr_old_asppb_rate;
1743 fetch csr_old_asppb_rate into l_old_asppb_rate, l_old_std_asppb_rate;
1744 close csr_old_asppb_rate;
1745 --
1746 hr_utility.trace('old ASPPB rate: '||l_old_asppb_rate);
1747 hr_utility.trace('old Std ASPPB rate: '||l_old_std_asppb_rate);
1748 --
1749 if (l_new_asppb_rate= l_old_asppb_rate) and (l_new_std_asppb_rate = l_old_std_asppb_rate) then
1750 return false;
1751 else
1752 return true;
1753 end if;
1754 --
1755 exception
1756 when others then raise;
1757 end asppb_rate_changed;
1758
1759 function asppa_rate_changed return boolean is
1760
1761 /* Check for ASPPB rates beginning on or after April 1st */
1762 cursor csr_new_asppa_rate is
1763 select petf.element_information6, petf.element_information8
1764 from pay_element_types_f petf
1765 where petf.element_name = 'Additional Statutory Paternity Pay Adoption'
1766 and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1767 order by effective_start_date;
1768
1769
1770 cursor csr_old_asppa_rate is
1771 select petf.element_information6, petf.element_information8
1772 from pay_element_types_f petf
1773 where petf.element_name = 'Additional Statutory Paternity Pay Adoption'
1774 and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1775 order by effective_start_date desc;
1776
1777 l_new_asppa_rate number;
1778 l_old_asppa_rate number;
1779
1780 l_new_std_asppa_rate number;
1781 l_old_std_asppa_rate number;
1782 --
1783 begin
1784 --
1785 hr_utility.trace('Entering asppa_rate_changed function');
1786 --
1787 /* Find SPPB rate for new tax year */
1788 open csr_new_asppa_rate;
1789 fetch csr_new_asppa_rate into l_old_asppa_rate, l_new_std_asppa_rate;
1790 --
1791 /* If unable to find new ASPPB rate, then rate probably not set yet */
1792 /* Make note of this and exit quietly */
1793 if csr_new_asppa_rate%notfound then
1794 close csr_new_asppa_rate;
1795 raise e_asppa_rate_not_set;
1796 end if;
1797 --
1798 close csr_new_asppa_rate;
1799 --
1800 hr_utility.trace('New ASPPA rate: '||l_new_asppa_rate);
1801 hr_utility.trace('New Std ASPPA rate: '||l_new_std_asppa_rate);
1802 --
1803 open csr_old_asppa_rate;
1804 fetch csr_old_asppa_rate into l_old_asppa_rate, l_old_std_asppa_rate;
1805 close csr_old_asppa_rate;
1806 --
1807 hr_utility.trace('old ASPPA rate: '||l_old_asppa_rate);
1808 hr_utility.trace('old Std ASPPA rate: '||l_old_std_asppa_rate);
1809 --
1810 if (l_new_asppa_rate= l_old_asppa_rate) and (l_new_std_asppa_rate = l_old_std_asppa_rate) then
1811 return false;
1812 else
1813 return true;
1814 end if;
1815 --
1816 exception
1817 when others then raise;
1818 end asppa_rate_changed;
1819
1820 /*End of Bug 13400812*/
1821
1822
1823
1824
1825 /* Main program body */
1826 begin
1827
1828 hr_utility.trace('Entering: '||g_package||'.update_ssp_smp_entries');
1829
1830 savepoint pre_update_status;
1831 /* Check first whether SSP element entries already updated for new tax year */
1832 /* SSP update block */
1833 begin
1834
1835 savepoint pre_ssp_update_status;
1836
1837 if not ssp_entries_already_updated
1838 then
1839
1840 hr_utility.trace('SSP element entries not updated, updating ....');
1841
1842 for r_affected_absences in csr_affected_absences loop
1843
1844 /* SSP control call block */
1845 begin
1846
1847 hr_utility.trace('Processing SSP absence: '||r_affected_absences.absence_id);
1848
1849 ssp_ssp_pkg.ssp_control(r_affected_absences.absence_id);
1850
1851 l_count := l_count + 1;
1852
1853 exception
1854
1855 when others then
1856 hr_utility.trace('Error occurred while processing SSP absence: '||r_affected_absences.absence_id);
1857 hr_utility.trace('SQL error code: '||SQLCODE);
1858 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1859 hr_utility.trace('Person id: '||r_affected_absences.person_id);
1860 hr_utility.trace('Absence start date: '||r_affected_absences.absence_start_date);
1861 hr_utility.trace('Absence end date: '||r_affected_absences.absence_end_date);
1862 l_update_error := true;
1863
1864 end;
1865
1866
1867
1868 end loop;
1869
1870 hr_utility.trace('Updated entries for '||l_count||' absences');
1871
1872 else
1873
1874 hr_utility.trace('SSP element entries already updated for new tax year');
1875
1876 end if;
1877
1878 commit;
1879
1880 exception
1881
1882 when e_ssp_rate_not_set
1883 then
1884 hr_utility.trace('Warning: SSP rate for new tax year not set');
1885 hr_utility.trace('Unable to proceed with updating SSP entries');
1886
1887
1888 when e_no_new_ssp_entry
1889 then
1890 hr_utility.trace('Warning: Unable to locate SSP entry in new tax year');
1891 hr_utility.trace('Entries for SSP absences in new tax year may be non-existent');
1892 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end dates of absences');
1893
1894 when others
1895 then
1896 hr_utility.trace('Unexpected error occurred inside SSP update block');
1897 hr_utility.trace('SQL error number: '||SQLCODE);
1898 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1899 rollback to pre_ssp_update_status;
1900 l_update_error := true;
1901
1902 end; /* SSP update block */
1903
1904 /* Now check SMP entries to see if they have been updated */
1905 /* SMP update block */
1906 -- 05/12/2003 rmakhija: Uncommented following section for TYE 2003/4
1907 begin
1908 --
1909 savepoint pre_smp_update_status;
1910 --
1911 if smp_rate_changed then
1912 --
1913 hr_utility.trace('SMP element entries updating ....');
1914 for r_affected_maternities in csr_affected_leave('MA') loop
1915 /* SMP Control call block */
1916 begin
1917 hr_utility.trace('Processing SMP absence: '||r_affected_maternities.maternity_id);
1918 ssp_smp_pkg.smp_control(p_maternity_id => r_affected_maternities.maternity_id,
1919 p_deleting => FALSE);
1920 l_mat_count := l_mat_count + 1;
1921 exception
1922 when others then
1923 hr_utility.trace('Error occurred while processing SMP absence: '||r_affected_maternities.maternity_id);
1924 hr_utility.trace('SQL error code: '||SQLCODE);
1925 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1926 hr_utility.trace('Person id: '||r_affected_maternities.person_id);
1927 hr_utility.trace('Maternity start date: '||r_affected_maternities.date_start);
1928 hr_utility.trace('Maternity end date: '||r_affected_maternities.date_end);
1929 hr_utility.trace('Maternity projected start date: '||r_affected_maternities.date_projected_start);
1930 hr_utility.trace('Maternity projected end date: '||r_affected_maternities.date_projected_end);
1931 l_update_error := true;
1932 end;
1933 end loop;
1934 hr_utility.trace('Updated entries for '||l_mat_count||' maternities');
1935 else
1936 hr_utility.trace('SMP element entries already updated for new tax year');
1937 end if;
1938 --
1939 commit;
1940 --
1941 exception
1942 when e_smp_rate_not_set then
1943 hr_utility.trace('Warning: SMP rate for new tax year not set');
1944 hr_utility.trace('Unable to proceed with updating SMP entries');
1945 when e_no_new_smp_entry then
1946 hr_utility.trace('Warning: Unable to locate SMP entry in new tax year');
1947 hr_utility.trace('Entries for SMP absences in new tax year may be non-existent');
1948 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1949 when others then
1950 hr_utility.trace('Unexpected error occurred inside SMP update block');
1951 hr_utility.trace('SQL error number: '||SQLCODE);
1952 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1953 rollback to pre_smp_update_status;
1954 l_update_error := true;
1955 end; /* SMP update block */
1956 -- 05/12/2003 rmakhija: Uncommented section ends here
1957 -- Following code has been added to auto update SAP/SPP Adoption/SPP Birth element entries
1958 -- Begin SAP Update Block
1959 BEGIN
1960 --
1961 savepoint pre_sap_update_status;
1962 --
1963 if sap_rate_changed then
1964 --
1965 hr_utility.trace('SAP element entries updating ....');
1966 for r_affected_adoption in csr_affected_leave('AD') loop
1967 /* SAP Control call block */
1968 begin
1969 hr_utility.trace('Processing SAP absence: '||r_affected_adoption.maternity_id);
1970 ssp_sap_pkg.sap_control(p_maternity_id => r_affected_adoption.maternity_id,
1971 p_deleting => FALSE);
1972 l_adop_count := l_adop_count + 1;
1973 exception
1974 when others then
1975 hr_utility.trace('Error occurred while processing SAP absence: '||r_affected_adoption.maternity_id);
1976 hr_utility.trace('SQL error code: '||SQLCODE);
1977 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1978 hr_utility.trace('Person id: '||r_affected_adoption.person_id);
1979 hr_utility.trace('Adoption start date: '||r_affected_adoption.date_start);
1980 hr_utility.trace('Adoption end date: '||r_affected_adoption.date_end);
1981 hr_utility.trace('Adoption projected start date: '||r_affected_adoption.date_projected_start);
1982 hr_utility.trace('Adoption projected end date: '||r_affected_adoption.date_projected_end);
1983 l_update_error := true;
1984 end;
1985 end loop;
1986 hr_utility.trace('Updated entries for '||l_adop_count||' adoptions');
1987 else
1988 hr_utility.trace('SAP element entries already updated for new tax year');
1989 end if;
1990 --
1991 commit;
1992 --
1993 exception
1994 when e_sap_rate_not_set then
1995 hr_utility.trace('Warning: SAP rate for new tax year not set');
1996 hr_utility.trace('Unable to proceed with updating SAP entries');
1997 when e_no_new_sap_entry then
1998 hr_utility.trace('Warning: Unable to locate SAP entry in new tax year');
1999 hr_utility.trace('Entries for SAP absences in new tax year may be non-existent');
2000 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2001 when others then
2002 hr_utility.trace('Unexpected error occurred inside SAP update block');
2003 hr_utility.trace('SQL error number: '||SQLCODE);
2004 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2005 rollback to pre_sap_update_status;
2006 l_update_error := true;
2007 end; /* SAP update block */
2008 -- Begin SPP Adoption Update Block
2009 BEGIN
2010 --
2011 savepoint pre_sppa_update_status;
2012 --
2013 if sppa_rate_changed then
2014 --
2015 hr_utility.trace('SPP Adoption element entries updating ....');
2016 for r_affected_pat_adop in csr_affected_leave('PA') loop
2017 /* SPP Adoption Control call block */
2018 begin
2019 hr_utility.trace('Processing SPP Adoption absence: '||r_affected_pat_adop.maternity_id);
2020 ssp_pad_pkg.pad_control(p_maternity_id => r_affected_pat_adop.maternity_id,
2021 p_deleting => FALSE);
2022 l_pat_adop_count := l_pat_adop_count + 1;
2023 exception
2024 when others then
2025 hr_utility.trace('Error occurred while processing SPP Adoption absence: '||r_affected_pat_adop.maternity_id);
2026 hr_utility.trace('SQL error code: '||SQLCODE);
2027 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2028 hr_utility.trace('Person id: '||r_affected_pat_adop.person_id);
2029 hr_utility.trace('Paternity Adoption start date: '||r_affected_pat_adop.date_start);
2030 hr_utility.trace('Paternity Adoption end date: '||r_affected_pat_adop.date_end);
2031 hr_utility.trace('Paternity Adoption projected start date: '||r_affected_pat_adop.date_projected_start);
2032 hr_utility.trace('Paternity Adoption projected end date: '||r_affected_pat_adop.date_projected_end);
2033 l_update_error := true;
2034 end;
2035 end loop;
2036 hr_utility.trace('Updated entries for '||l_pat_adop_count||' paternity adoptions');
2037 else
2038 hr_utility.trace('SPP Adoption element entries already updated for new tax year');
2039 end if;
2040 --
2041 commit;
2042 --
2043 exception
2044 when e_sppa_rate_not_set then
2045 hr_utility.trace('Warning: SPP ADoption rate for new tax year not set');
2046 hr_utility.trace('Unable to proceed with updating SPP ADoption entries');
2047 when e_no_new_sppa_entry then
2048 hr_utility.trace('Warning: Unable to locate SPP ADoption entry in new tax year');
2049 hr_utility.trace('Entries for SPP ADoption absences in new tax year may be non-existent');
2050 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2051 when others then
2052 hr_utility.trace('Unexpected error occurred inside SPP ADoption update block');
2053 hr_utility.trace('SQL error number: '||SQLCODE);
2054 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2055 rollback to pre_sppa_update_status;
2056 l_update_error := true;
2057 end; /* SPP Adoption update block */
2058 -- Begin SPP Birth Update Block
2059 BEGIN
2060 --
2061 savepoint pre_sppb_update_status;
2062 --
2063 if sppb_rate_changed then
2064 --
2065 hr_utility.trace('SPP Birth element entries updating ....');
2066 for r_affected_pat_Birth in csr_affected_leave('PB') loop
2067 /* SPP Birth Control call block */
2068 begin
2069 hr_utility.trace('Processing SPP Birth absence: '||r_affected_pat_Birth.maternity_id);
2070 ssp_pab_pkg.pab_control(p_maternity_id => r_affected_pat_Birth.maternity_id,
2071 p_deleting => FALSE);
2072 l_pat_Birth_count := l_pat_birth_count + 1;
2073 exception
2074 when others then
2075 hr_utility.trace('Error occurred while processing SPP Birth absence: '||r_affected_pat_Birth.maternity_id);
2076 hr_utility.trace('SQL error code: '||SQLCODE);
2077 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2078 hr_utility.trace('Person id: '||r_affected_pat_Birth.person_id);
2079 hr_utility.trace('Paternity Birth start date: '||r_affected_pat_Birth.date_start);
2080 hr_utility.trace('Paternity Birth end date: '||r_affected_pat_Birth.date_end);
2081 hr_utility.trace('Paternity Birth projected start date: '||r_affected_pat_Birth.date_projected_start);
2082 hr_utility.trace('Paternity Birth projected end date: '||r_affected_pat_Birth.date_projected_end);
2083 l_update_error := true;
2084 end;
2085 end loop;
2086 hr_utility.trace('Updated entries for '||l_pat_birth_count||' paternity births');
2087 else
2088 hr_utility.trace('SPP Birth element entries already updated for new tax year');
2089 end if;
2090 --
2091 commit;
2092 --
2093 exception
2094 when e_sppb_rate_not_set then
2095 hr_utility.trace('Warning: SPP Birth rate for new tax year not set');
2096 hr_utility.trace('Unable to proceed with updating SPP Birth entries');
2097 when e_no_new_sppb_entry then
2098 hr_utility.trace('Warning: Unable to locate SPP Birth entry in new tax year');
2099 hr_utility.trace('Entries for SPP Birth absences in new tax year may be non-existent');
2100 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2101 when others then
2102 hr_utility.trace('Unexpected error occurred inside SPP Birth update block');
2103 hr_utility.trace('SQL error number: '||SQLCODE);
2104 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2105 rollback to pre_sppb_update_status;
2106 l_update_error := true;
2107 end; /* SPP Birth update block */
2108
2109
2110 /*Start of Bug 13400812*/
2111 /*ASPP Birth and adoption Calls*/
2112
2113 begin
2114 --
2115 savepoint pre_asppa_update_status;
2116 --
2117 if asppa_rate_changed then
2118 --
2119 hr_utility.trace('ASPP Adoption element entries updating ....');
2120 for r_affected_apat_adop in csr_affected_leave('AA') loop
2121 /* ASPP Adoption Control call block */
2122 begin
2123 hr_utility.trace('Processing ASPP Adoption absence: '||r_affected_apat_adop.maternity_id);
2124
2125 ssp_apad_pkg.apad_control(p_maternity_id => r_affected_apat_adop.maternity_id,
2126
2127
2128 p_deleting => FALSE);
2129 l_apad_count := l_apad_count + 1;
2130
2131 exception
2132 when others then
2133 hr_utility.trace('Error occurred while processing ASPP Adoption absence: '||r_affected_apat_adop.maternity_id);
2134 hr_utility.trace('SQL error code: '||SQLCODE);
2135 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2136 hr_utility.trace('Person id: '||r_affected_apat_adop.person_id);
2137 hr_utility.trace('Additional Paternity Adoption start date: '||r_affected_apat_adop.date_start);
2138 hr_utility.trace('Additional Paternity Adoption end date: '||r_affected_apat_adop.date_end);
2139 hr_utility.trace('Additional Paternity Adoption projected start date: '||r_affected_apat_adop.date_projected_start);
2140 hr_utility.trace('Additional Paternity Adoption projected end date: '||r_affected_apat_adop.date_projected_end);
2141 l_update_error := true;
2142 end;
2143 end loop;
2144 hr_utility.trace('Updated entries for '||l_apad_count||' additional paternity adoptions');
2145
2146 else
2147 hr_utility.trace('ASPP Adoption element entries already updated for new tax year');
2148 end if;
2149 --
2150 commit;
2151 --
2152 exception
2153 when e_asppa_rate_not_set then
2154 hr_utility.trace('Warning: ASPP ADoption rate for new tax year not set');
2155 hr_utility.trace('Unable to proceed with updating ASPP ADoption entries');
2156 when e_no_new_asppa_entry then
2157 hr_utility.trace('Warning: Unable to locate ASPP ADoption entry in new tax year');
2158 hr_utility.trace('Entries for ASPP ADoption absences in new tax year may be non-existent');
2159 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2160 when others then
2161 hr_utility.trace('Unexpected error occurred inside ASPP ADoption update block');
2162 hr_utility.trace('SQL error number: '||SQLCODE);
2163 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2164 rollback to pre_asppa_update_status;
2165 l_update_error := true;
2166
2167 end;
2168
2169 begin
2170 --
2171 savepoint pre_asppb_update_status;
2172 --
2173 if asppb_rate_changed then
2174 --
2175 hr_utility.trace('ASPP Birth element entries updating ....');
2176 for r_affected_apat_birth in csr_affected_leave('AB') loop
2177 /* ASPP Adoption Control call block */
2178 begin
2179 hr_utility.trace('Processing ASPP Birth absence: '||r_affected_apat_birth.maternity_id);
2180
2181 ssp_apab_pkg.apab_control(p_maternity_id => r_affected_apat_birth.maternity_id,
2182 p_deleting => FALSE);
2183 l_apab_count := l_apab_count + 1;
2184
2185 exception
2186 when others then
2187 hr_utility.trace('Error occurred while processing ASPP Birth absence: '||r_affected_apat_birth.maternity_id);
2188 hr_utility.trace('SQL error code: '||SQLCODE);
2189 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2190 hr_utility.trace('Person id: '||r_affected_apat_birth.person_id);
2191 hr_utility.trace('Additional Paternity Birth start date: '||r_affected_apat_birth.date_start);
2192 hr_utility.trace('Additional Paternity Birth end date: '||r_affected_apat_birth.date_end);
2193 hr_utility.trace('Additional Paternity Birth projected start date: '||r_affected_apat_birth.date_projected_start);
2194 hr_utility.trace('Additional Paternity Birth projected end date: '||r_affected_apat_birth.date_projected_end);
2195 l_update_error := true;
2196 end;
2197 end loop;
2198 hr_utility.trace('Updated entries for '||l_apab_count||' additional paternity Birth');
2199
2200 else
2201 hr_utility.trace('ASPP Birth element entries already updated for new tax year');
2202 end if;
2203 --
2204 commit;
2205 --
2206 exception
2207 when e_asppb_rate_not_set then
2208 hr_utility.trace('Warning: ASPP Birth rate for new tax year not set');
2209 hr_utility.trace('Unable to proceed with updating ASPP Birth entries');
2210 when e_no_new_asppb_entry then
2211 hr_utility.trace('Warning: Unable to locate ASPP Birth entry in new tax year');
2212 hr_utility.trace('Entries for ASPP Birth absences in new tax year may be non-existent');
2213 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2214 when others then
2215 hr_utility.trace('Unexpected error occurred inside ASPP Birth update block');
2216 hr_utility.trace('SQL error number: '||SQLCODE);
2217 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2218 rollback to pre_asppb_update_status;
2219 l_update_error := true;
2220 end;
2221
2222
2223 /*End of Bug 13400812*/
2224 hr_utility.trace('Update of SSP, SMP, SAP, SPP Adoption,SPP Birth, ASPP Birth and ASPP Adoption entries complete');
2225
2226 p_update_error := l_update_error;
2227
2228 hr_utility.trace('Leaving: '||g_package||'.update_ssp_smp_entries');
2229
2230
2231 exception
2232
2233
2234 when others
2235 then
2236 hr_utility.trace('Unexpected error occurred inside SSP/SMP element entries update procedure');
2237 hr_utility.trace('SQL error number: '||SQLCODE);
2238 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2239 rollback to pre_update_status;
2240 p_update_error := true;
2241
2242
2243
2244
2245 end update_ssp_smp_entries;
2246
2247 --------------------------------------------------------------------------------
2248 --
2249 procedure update_ssp_smp_entries (P_UPDATE_ERROR OUT NOCOPY boolean, p_job_err OUT NOCOPY l_job_err_typ) is
2250
2251
2252 cursor csr_affected_absences is
2253 select nvl(paa.linked_absence_id,paa.absence_attendance_id) as absence_id,
2254 paa.person_id,
2255 nvl(paa.date_start,paa.sickness_start_date) as absence_start_date,
2256 nvl(paa.date_end,paa.sickness_end_date) as absence_end_date
2257 from per_absence_attendances paa,
2258 per_absence_attendance_types paat
2259 where paa.absence_attendance_type_id = paat.absence_attendance_type_id
2260 and paat.absence_category = 'S'
2261 and
2262 /* SSP absences which span tax years or start in old tax year and are open-ended */
2263 (
2264 ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
2265 and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
2266 )
2267 or
2268 ((paa.sickness_start_date between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
2269 and (paa.sickness_end_date > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.sickness_end_date is null)
2270 )
2271 or
2272 /* SSP absences which start in the new tax year */
2273 (
2274 paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
2275 or paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
2276 )
2277 )
2278 /* Do not retrieve terminated employees whose actual termination dates have passed or are null */
2279 and not exists
2280 ( select 1
2281 from per_all_people_f ppf,
2282 per_person_types ppt,
2283 per_periods_of_service pps
2284 where ppf.person_id = pps.person_id
2285 and ppt.person_type_id = ppf.person_type_id
2286 and ppt.system_person_type = 'EX_EMP'
2287 and nvl(pps.actual_termination_date,to_date('01/01/0001','DD/MM/YYYY')) <= sysdate
2288 and ppf.person_id = paa.person_id
2289 and pps.date_start = (select max(date_start)
2290 from per_periods_of_service pos
2291 where pos.person_id = pps.person_id)
2292 and ppf.effective_start_date >= pps.date_start
2293 and pps.date_start <= paa.sickness_start_date)
2294 order by nvl(paa.linked_absence_id,paa.absence_attendance_id);
2295
2296
2297 cursor csr_affected_leave(p_leave_type IN VARCHAR2) is
2298 -- p_leave_type = 'MA' - Maternity, 'AD' - Adoption, 'PA' - Paternity Adoption, 'PB' - Paternity Birth, 'AB'- Additional Paternity Birth, 'AA'- Additional Paternity Adoption
2299 select paa.maternity_id,
2300 paa.person_id,
2301 paa.date_start,
2302 paa.date_end,
2303 paa.date_projected_start,
2304 paa.date_projected_end
2305 from per_absence_attendances paa,
2306 ssp_maternities mat
2307 where paa.maternity_id = mat.maternity_id
2308 and nvl(mat.leave_type, 'MA') = p_leave_type
2309 and
2310 /* SMP absences which span tax years or start in old tax year and are open-ended */
2311 (
2312 ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
2313 and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
2314 )
2315 or
2316 ((paa.date_projected_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
2317 and (paa.date_projected_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_projected_end is null)
2318 and paa.date_start IS NULL -- use projected dates only when actual dates not available
2319 )
2320 or
2321 /* SMP absences which start in the new tax year */
2322 (
2323 paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
2324 or (paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
2325 and paa.date_start IS NULL) -- use projected dates only when actual dates not available
2326 )
2327 )
2328 /* Do not retrieve employees whose final process dates have passed */
2329 and not exists
2330 ( select 1
2331 from per_all_people_f ppf,
2332 per_person_types ppt,
2333 per_periods_of_service pps
2334 where ppf.person_id = pps.person_id
2335 and ppt.person_type_id = ppf.person_type_id
2336 and ppt.system_person_type = 'EX_EMP'
2337 and pps.final_process_date <= sysdate
2338 and ppf.person_id = paa.person_id
2339 and pps.date_start = (select max(date_start)
2340 from per_periods_of_service pos
2341 where pos.person_id = pps.person_id)
2342 and ppf.effective_start_date >= pps.date_start
2343 and pps.date_start <= paa.date_start)
2344 /* Do not retrieve employees who are deceased */
2345 and not exists
2346 ( select 1
2347 from per_all_people_f ppf,
2348 per_periods_of_service pps
2349 where ppf.person_id = pps.person_id
2350 and pps.leaving_reason = 'D'
2351 and ppf.person_id = paa.person_id)
2352 order by paa.maternity_id;
2353
2354
2355 l_count NUMBER := 0;
2356 l_mat_count NUMBER := 0;
2357 l_adop_count NUMBER := 0;
2358 l_pat_adop_count NUMBER := 0;
2359 l_pat_birth_count NUMBER := 0;
2360 l_apat_adop_count NUMBER := 0;
2361 l_apat_birth_count NUMBER := 0;
2362
2363 e_ssp_rate_not_set exception;
2364 e_smp_rate_not_set exception;
2365 e_sap_rate_not_set exception;
2366 e_sppa_rate_not_set exception;
2367 e_sppb_rate_not_set exception;
2368 e_asppb_rate_not_set exception;
2369 e_asppa_rate_not_set exception;
2370
2371
2372 e_no_new_ssp_entry exception;
2373 e_no_new_smp_entry exception;
2374 e_no_new_sap_entry exception;
2375 e_no_new_sppa_entry exception;
2376 e_no_new_sppb_entry exception;
2377 e_no_new_asppa_entry exception;
2378 e_no_new_asppb_entry exception;
2379
2380 l_update_error boolean := FALSE;
2381
2382 --6800788 begin
2383 l_fail number;
2384 l_fail_count number :=0;
2385 l_job_no binary_integer;
2386 Type l_job_type is record
2387 (
2388 Job_no number,
2389 Person_id number,
2390 Absence_id number
2391 );
2392 Type l_tbl_job_typ is table of l_job_type index by binary_integer;
2393 l_tbl_job l_tbl_job_typ;
2394 --6800788 end
2395
2396 /* Function to check if SSP entries in new tax year have already been updated */
2397 /* with new SSP rates and recalculated - returns TRUE if so, FALSE if not */
2398 function ssp_entries_already_updated return boolean is
2399
2400 cursor csr_new_ssp_rate is
2401 select piv.default_value
2402 from pay_input_values_f piv,
2403 pay_element_types_f petf
2404 where petf.element_type_id = piv.element_type_id
2405 and piv.name = 'Rate'
2406 and petf.element_name = 'Statutory Sick Pay'
2407 and piv.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate);
2408
2409
2410 cursor csr_first_new_ssp_entry is
2411 select peev1.screen_entry_value
2412 from pay_element_entry_values_f peev1,
2413 pay_element_entry_values_f peev2,
2414 pay_input_values_f piv,
2415 pay_element_entries_f peef,
2416 pay_element_links_f pelf,
2417 pay_element_types_f petf
2418 where piv.input_value_id = peev1.input_value_id
2419 and peev1.element_entry_id = peev2.element_entry_id
2420 and peev1.element_entry_id = peef.element_entry_id
2421 and peef.element_link_id = pelf.element_link_id
2422 and pelf.element_type_id = petf.element_type_id
2423 and piv.name = 'Rate'
2424 and petf.element_name = 'Statutory Sick Pay'
2425 and piv.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate)
2426 and peev2.element_entry_value_id =
2427 (select peev3.element_entry_value_id
2428 from pay_element_entry_values_f peev3
2429 where input_value_id =
2430 (select distinct input_value_id
2431 from pay_input_values_f piv,
2432 pay_element_types_f petf
2433 where petf.element_type_id = piv.element_type_id
2434 and petf.element_name = 'Statutory Sick Pay'
2435 and piv.name = 'From')
2436 and peev3.screen_entry_value >
2437 fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate))
2438 /* Retrieve only those entries that will be retrieved by main SSP query */
2439 and peev3.element_entry_id in
2440 ( select peef1.element_entry_id
2441 from pay_element_entries_f peef1,
2442 per_all_assignments_f paf,
2443 per_all_people_f ppf,
2444 per_person_types ppt,
2445 per_periods_of_service pps
2446 where peef1.assignment_id = paf.assignment_id
2447 and paf.person_id = ppf.person_id
2448 and ppf.person_id = pps.person_id
2449 and ppt.person_type_id = ppf.person_type_id
2450 and peev3.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2451 and fnd_date.date_to_canonical(ppf.effective_end_date)
2452 and peev3.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2453 and fnd_date.date_to_canonical(paf.effective_end_date)
2454 and ppt.system_person_type = 'EMP'
2455 and nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2456 )
2457 and rownum = 1);
2458
2459
2460 l_new_SSP_rate number;
2461 l_first_SSP_entry_rate number;
2462
2463
2464
2465 begin
2466
2467 hr_utility.trace('Entering ssp_entries_already_updated function');
2468
2469 /* Find SSP rate for new tax year */
2470 open csr_new_ssp_rate;
2471 fetch csr_new_ssp_rate into l_new_SSP_rate;
2472
2473 /* If unable to find new SSP rate, then rate probably not set yet */
2474 /* Make note of this and exit quietly */
2475 if csr_new_ssp_rate%notfound
2476 then
2477 close csr_new_ssp_rate;
2478 raise e_ssp_rate_not_set;
2479 end if;
2480
2481 close csr_new_ssp_rate;
2482
2483 hr_utility.trace('New SSP rate: '||l_new_SSP_rate);
2484
2485 /* Find first element entry value holding SSP rate for new tax year */
2486 open csr_first_new_ssp_entry;
2487 fetch csr_first_new_ssp_entry into l_first_SSP_entry_rate;
2488
2489 /* If unable to find SSP entry in new tax year, then warn user */
2490 /* Possible causes are employee terminations or stoppages */
2491 if csr_first_new_ssp_entry%notfound
2492 then
2493 close csr_first_new_ssp_entry;
2494 raise e_no_new_ssp_entry;
2495 end if;
2496
2497 close csr_first_new_ssp_entry;
2498
2499 hr_utility.trace('First SSP entry rate: '||l_first_SSP_entry_rate);
2500
2501 if l_new_SSP_rate = l_first_SSP_entry_rate
2502 then
2503
2504 return true;
2505
2506 else
2507
2508 return false;
2509
2510 end if;
2511
2512 exception
2513
2514 when others
2515 then raise;
2516
2517
2518 end ssp_entries_already_updated;
2519
2520 /* Function to check if SMP entries in new tax year have already updated */
2521 /* with new SMP rates and recalculated - returns TRUE if so, FALSE if not */
2522 function smp_rate_changed return boolean is
2523
2524 /* Check for SMP rates beginning on or after April 1st */
2525 cursor csr_new_smp_rate is
2526 select petf.element_information10, petf.element_information9, petf.element_information16
2527 from pay_element_types_f petf
2528 where petf.element_name = 'Statutory Maternity Pay'
2529 and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
2530 order by effective_start_date;
2531
2532
2533 /* 09/12/2003 rmakhija - commented out following cursor because element entry value
2534 for 'Amount' is a calculated value and it is not same
2535 as smp rate therefore it can not be compared with new smp
2536 rate to detect the change in smp rate. A new cursor
2537 csr_old_smp_rate has been added to find old smp rate
2538 and to compare it with new smp rate to detect the change.
2539
2540 cursor csr_first_new_smp_entry is
2541 select peev1.screen_entry_value
2542 from pay_element_entry_values_f peev1,
2543 pay_element_entry_values_f peev2,
2544 pay_element_entry_values_f peev3,
2545 pay_input_values_f piv,
2546 pay_element_entries_f peef,
2547 pay_element_links_f pelf,
2548 pay_element_types_f petf
2549 where piv.input_value_id = peev1.input_value_id
2550 and peev1.element_entry_id = peev2.element_entry_id
2551 and peev1.element_entry_id = peev3.element_entry_id
2552 and peev1.element_entry_id = peef.element_entry_id
2553 and peef.element_link_id = pelf.element_link_id
2554 and pelf.element_type_id = petf.element_type_id
2555 and piv.name = 'Amount'
2556 and petf.element_name = 'Statutory Maternity Pay'
2557 and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
2558 -- Time restriction - only rows after tax year end
2559 and peev2.input_value_id =
2560 (select input_value_id
2561 from pay_input_values_f piv
2562 where petf.element_type_id = piv.element_type_id
2563 and upper(piv.name) = upper('Week commencing')
2564 )
2565 and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
2566 -- Retrieve only those entries that main SMP query will retrieve
2567 and exists
2568 ( select 1
2569 from per_all_assignments_f paf,
2570 per_all_people_f ppf,
2571 per_person_types ppt,
2572 per_periods_of_service pps
2573 where peef.assignment_id = paf.assignment_id
2574 and paf.person_id = ppf.person_id
2575 and ppf.person_id = pps.person_id
2576 and ppt.person_type_id = ppf.person_type_id
2577 and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2578 and fnd_date.date_to_canonical(ppf.effective_end_date)
2579 and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2580 and fnd_date.date_to_canonical(paf.effective_end_date)
2581 and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2582 )
2583 and peev3.input_value_id =
2584 (select input_value_id
2585 from pay_input_values_f piv
2586 where petf.element_type_id = piv.element_type_id
2587 and upper(piv.name) = upper('Rate'))
2588 -- Rate restriction - only retrieve entries on LOW rate for SMP
2589 and upper(peev3.screen_entry_value) = upper('Low')
2590 -- Get first row that matches all of the above criteria
2591 and rownum = 1;
2592 */
2593
2594 cursor csr_old_smp_rate is
2595 select petf.element_information10, petf.element_information9, petf.element_information16
2596 from pay_element_types_f petf
2597 where petf.element_name = 'Statutory Maternity Pay'
2598 and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
2599 order by effective_start_date desc;
2600
2601 l_new_SMP_rate number;
2602 l_old_SMP_rate number;
2603 l_new_high_smp_rate number;
2604 l_old_high_smp_rate number;
2605 l_new_std_smp_rate number;
2606 l_old_std_smp_rate number;
2607
2608 begin
2609
2610 hr_utility.trace('Entering smp_rate_changed function');
2611 --
2612 /* Find SMP rate for new tax year */
2613 open csr_new_smp_rate;
2614 fetch csr_new_smp_rate into l_new_SMP_rate, l_new_high_smp_rate, l_new_std_smp_rate;
2615 --
2616 /* If unable to find new SMP rate, then rate probably not set yet */
2617 /* Make note of this and exit quietly */
2618 if csr_new_SMP_rate%notfound
2619 then
2620 close csr_new_SMP_rate;
2621 raise e_SMP_rate_not_set;
2622 end if;
2623 --
2624 close csr_new_smp_rate;
2625 --
2626 hr_utility.trace('New Lower SMP rate: '||l_new_SMP_rate);
2627 hr_utility.trace('New Higher SMP rate: '||l_new_high_SMP_rate);
2628 hr_utility.trace('New Standard SMP rate: '||l_new_std_SMP_rate);
2629 --
2630 /* Find SMP rate for current tax year */
2631 open csr_old_smp_rate;
2632 fetch csr_old_smp_rate into l_old_SMP_rate, l_old_high_smp_rate, l_old_std_smp_rate;
2633 close csr_old_smp_rate;
2634 --
2635 hr_utility.trace('Old SMP rate: '||l_old_SMP_rate);
2636 hr_utility.trace('Old Higher SMP rate: '||l_old_high_SMP_rate);
2637 hr_utility.trace('Old Standard SMP rate: '||l_old_std_SMP_rate);
2638 --
2639 if (l_new_SMP_rate = l_old_smp_rate) and (l_new_high_smp_rate = l_old_high_smp_rate) and (l_new_std_smp_rate = l_old_std_smp_rate)
2640 then
2641 return false;
2642 else
2643 return true;
2644 end if;
2645 --
2646 exception
2647 when others then
2648 raise;
2649 end smp_rate_changed;
2650
2651
2652 /* Function to check if SAP entries in new tax year have already updated */
2653 /* with new SAP rates and recalculated - returns TRUE if so, FALSE if not */
2654 function sap_rate_changed return boolean is
2655
2656 /* Check for SAP rates beginning on or after April 1st */
2657 cursor csr_new_sap_rate is
2658 select petf.element_information5, petf.element_information7
2659 from pay_element_types_f petf
2660 where petf.element_name = 'Statutory Adoption Pay'
2661 and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
2662 order by effective_start_date;
2663 --
2664 /* 09/12/2003 rmakhija - commented out following cursor because element entry value
2665 for 'Amount' is a calculated value and it is not same
2666 as sap rate therefore it can not be compared with new sap
2667 rate to detect the change in sap rate. A new cursor
2668 csr_old_sap_rate has been added to find old sap rate
2669 and to compare it with new sap rate to detect the change.
2670
2671 cursor csr_first_new_sap_entry is
2672 select peev1.screen_entry_value
2673 from pay_element_entry_values_f peev1,
2674 pay_element_entry_values_f peev2,
2675 pay_input_values_f piv,
2676 pay_element_entries_f peef,
2677 pay_element_links_f pelf,
2678 pay_element_types_f petf
2679 where piv.input_value_id = peev1.input_value_id
2680 and peev1.element_entry_id = peev2.element_entry_id
2681 and peev1.element_entry_id = peef.element_entry_id
2682 and peef.element_link_id = pelf.element_link_id
2683 and pelf.element_type_id = petf.element_type_id
2684 and piv.name = 'Amount'
2685 and petf.element_name = 'Statutory Adoption Pay'
2686 and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
2687 -- Time restriction - only rows after tax year end
2688 and peev2.input_value_id =
2689 (select input_value_id
2690 from pay_input_values_f piv
2691 where petf.element_type_id = piv.element_type_id
2692 and upper(piv.name) = upper('Week commencing')
2693 )
2694 and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
2695 -- Retrieve only those entries that main sap query will retrieve
2696 and exists
2697 ( select 1
2698 from per_all_assignments_f paf,
2699 per_all_people_f ppf,
2700 per_person_types ppt,
2701 per_periods_of_service pps
2702 where peef.assignment_id = paf.assignment_id
2703 and paf.person_id = ppf.person_id
2704 and ppf.person_id = pps.person_id
2705 and ppt.person_type_id = ppf.person_type_id
2706 and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2707 and fnd_date.date_to_canonical(ppf.effective_end_date)
2708 and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2709 and fnd_date.date_to_canonical(paf.effective_end_date)
2710 and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2711 )
2712 -- Get first row that matches all of the above criteria
2713 and rownum = 1;
2714 */
2715 --
2716 cursor csr_old_sap_rate is
2717 select petf.element_information5, petf.element_information7
2718 from pay_element_types_f petf
2719 where petf.element_name = 'Statutory Adoption Pay'
2720 and petf.effective_start_date < hr_gbnicar.uk_tax_yr_end(sysdate) - 4
2721 order by effective_start_date desc;
2722 --
2723 --
2724 l_new_sap_rate number;
2725 l_old_sap_rate number;
2726 --
2727 l_new_std_sap_rate number;
2728 l_old_std_sap_rate number;
2729 --
2730 begin
2731 --
2732 hr_utility.trace('Entering sap_rate_changed function');
2733 --
2734 /* Find sap rate for new tax year */
2735 open csr_new_sap_rate;
2736 fetch csr_new_sap_rate into l_new_sap_rate, l_new_std_sap_rate;
2737 --
2738 /* If unable to find new sap rate, then rate probably not set yet */
2739 /* Make note of this and exit quietly */
2740 if csr_new_sap_rate%notfound then
2741 close csr_new_sap_rate;
2742 raise e_sap_rate_not_set;
2743 end if;
2744 --
2745 close csr_new_sap_rate;
2746 --
2747 hr_utility.trace('New sap rate: '||l_new_sap_rate);
2748 hr_utility.trace('New std sap rate: '||l_new_std_sap_rate);
2749 --
2750 /* Find sap rate for current tax year */
2751 open csr_old_sap_rate;
2752 fetch csr_old_sap_rate into l_old_sap_rate, l_old_std_sap_rate;
2753 close csr_old_sap_rate;
2754 --
2755 hr_utility.trace('Old sap rate: '||l_old_sap_rate);
2756 hr_utility.trace('Old std sap rate: '||l_old_std_sap_rate);
2757 --
2758 if (l_new_sap_rate = l_old_sap_rate) and (l_new_std_sap_rate = l_old_std_sap_rate) then
2759 return false;
2760 else
2761 return true;
2762 end if;
2763 --
2764 exception
2765 when others then raise;
2766 end sap_rate_changed;
2767
2768 /* Function to check if SPPA entries in new tax year have already updated */
2769 /* with new SPPA rates and recalculated - returns TRUE if so, FALSE if not */
2770 function sppa_rate_changed return boolean is
2771
2772 /* Check for SPPA rates beginning on or after April 1st */
2773 cursor csr_new_sppa_rate is
2774 select petf.element_information6, petf.element_information8
2775 from pay_element_types_f petf
2776 where petf.element_name = 'Statutory Paternity Pay Adoption'
2777 and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
2778 order by effective_start_date;
2779 --
2780 /* 09/12/2003 rmakhija - commented out following cursor because element entry value
2781 for 'Amount' is a calculated value and it is not same
2782 as SPP Adoption rate therefore it can not be compared with new
2783 SPP Adoption rate to detect the change. A new cursor
2784 csr_old_sppa_rate has been added to find old SPP Adoption rate
2785 and to compare it with new SPP Adoption rate to detect the change.
2786
2787 cursor csr_first_new_sppa_entry is
2788 select peev1.screen_entry_value
2789 from pay_element_entry_values_f peev1,
2790 pay_element_entry_values_f peev2,
2791 pay_input_values_f piv,
2792 pay_element_entries_f peef,
2793 pay_element_links_f pelf,
2794 pay_element_types_f petf
2795 where piv.input_value_id = peev1.input_value_id
2796 and peev1.element_entry_id = peev2.element_entry_id
2797 and peev1.element_entry_id = peef.element_entry_id
2798 and peef.element_link_id = pelf.element_link_id
2799 and pelf.element_type_id = petf.element_type_id
2800 and piv.name = 'Amount'
2801 and petf.element_name = 'Statutory Paternity Pay Adoption'
2802 and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
2803 -- Time restriction - only rows after tax year end
2804 and peev2.input_value_id =
2805 (select input_value_id
2806 from pay_input_values_f piv
2807 where petf.element_type_id = piv.element_type_id
2808 and upper(piv.name) = upper('Week commencing')
2809 )
2810 and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
2811 -- Retrieve only those entries that main sppa query will retrieve
2812 and exists
2813 ( select 1
2814 from per_all_assignments_f paf,
2815 per_all_people_f ppf,
2816 per_person_types ppt,
2817 per_periods_of_service pps
2818 where peef.assignment_id = paf.assignment_id
2819 and paf.person_id = ppf.person_id
2820 and ppf.person_id = pps.person_id
2821 and ppt.person_type_id = ppf.person_type_id
2822 and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2823 and fnd_date.date_to_canonical(ppf.effective_end_date)
2824 and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2825 and fnd_date.date_to_canonical(paf.effective_end_date)
2826 and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2827 )
2828 -- Get first row that matches all of the above criteria
2829 and rownum = 1;
2830 */
2831 --
2832 cursor csr_old_sppa_rate is
2833 select petf.element_information6, petf.element_information8
2834 from pay_element_types_f petf
2835 where petf.element_name = 'Statutory Paternity Pay Adoption'
2836 and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
2837 order by effective_start_date desc;
2838
2839 --
2840 l_new_sppa_rate number;
2841 l_old_sppa_rate number;
2842 --
2843 l_new_std_sppa_rate number;
2844 l_old_std_sppa_rate number;
2845 --
2846 begin
2847 --
2848 hr_utility.trace('Entering sppa_rate_changed function');
2849 --
2850 /* Find sppa rate for new tax year */
2851 open csr_new_sppa_rate;
2852 fetch csr_new_sppa_rate into l_new_sppa_rate, l_new_std_sppa_rate;
2853 --
2854 /* If unable to find new sppa rate, then rate probably not set yet */
2855 /* Make note of this and exit quietly */
2856 if csr_new_sppa_rate%notfound then
2857 close csr_new_sppa_rate;
2858 raise e_sppa_rate_not_set;
2859 end if;
2860 --
2861 close csr_new_sppa_rate;
2862 --
2863 hr_utility.trace('New sppa rate: '||l_new_sppa_rate);
2864 hr_utility.trace('New std sppa rate: '||l_new_std_sppa_rate);
2865 --
2866 open csr_old_sppa_rate;
2867 fetch csr_old_sppa_rate into l_old_sppa_rate, l_old_std_sppa_rate;
2868 close csr_old_sppa_rate;
2869 --
2870 hr_utility.trace('Old SPPA rate: '||l_old_sppa_rate);
2871 hr_utility.trace('Old Std SPPA rate: '||l_old_std_sppa_rate);
2872 --
2873 if (l_new_sppa_rate = l_old_sppa_rate) and (l_new_std_sppa_rate = l_old_std_sppa_rate) then
2874 return false;
2875 else
2876 return true;
2877 end if;
2878 --
2879 exception
2880 when others then raise;
2881 end sppa_rate_changed;
2882
2883 /* Function to check if SPPB entries in new tax year have already updated */
2884 /* with new SPPB rates and recalculated - returns TRUE if so, FALSE if not */
2885 function sppb_rate_changed return boolean is
2886
2887 /* Check for SPPB rates beginning on or after April 1st */
2888 cursor csr_new_sppb_rate is
2889 select petf.element_information6, petf.element_information9
2890 from pay_element_types_f petf
2891 where petf.element_name = 'Statutory Paternity Pay Birth'
2892 and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
2893 order by effective_start_date;
2894 --
2895 /* 09/12/2003 rmakhija - commented out following cursor because element entry value
2896 for 'Amount' is a calculated value and it is not same
2897 as SPP Birth rate therefore it can not be compared with new
2898 SPP Birth rate to detect the change. A new cursor
2899 csr_old_sppb_rate has been added to find old SPP Birth rate
2900 and to compare it with new SPP Birth rate to detect the change.
2901
2902 cursor csr_first_new_sppb_entry is
2903 select peev1.screen_entry_value
2904 from pay_element_entry_values_f peev1,
2905 pay_element_entry_values_f peev2,
2906 pay_input_values_f piv,
2907 pay_element_entries_f peef,
2908 pay_element_links_f pelf,
2909 pay_element_types_f petf
2910 where piv.input_value_id = peev1.input_value_id
2911 and peev1.element_entry_id = peev2.element_entry_id
2912 and peev1.element_entry_id = peef.element_entry_id
2913 and peef.element_link_id = pelf.element_link_id
2914 and pelf.element_type_id = petf.element_type_id
2915 and piv.name = 'Amount'
2916 and petf.element_name = 'Statutory Paternity Pay Birth'
2917 and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
2918 -- Time restriction - only rows after tax year end
2919 and peev2.input_value_id =
2920 (select input_value_id
2921 from pay_input_values_f piv
2922 where petf.element_type_id = piv.element_type_id
2923 and upper(piv.name) = upper('Week commencing')
2924 )
2925 and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
2926 -- Retrieve only those entries that main SPPB query will retrieve
2927 and exists
2928 ( select 1
2929 from per_all_assignments_f paf,
2930 per_all_people_f ppf,
2931 per_person_types ppt,
2932 per_periods_of_service pps
2933 where peef.assignment_id = paf.assignment_id
2934 and paf.person_id = ppf.person_id
2935 and ppf.person_id = pps.person_id
2936 and ppt.person_type_id = ppf.person_type_id
2937 and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2938 and fnd_date.date_to_canonical(ppf.effective_end_date)
2939 and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2940 and fnd_date.date_to_canonical(paf.effective_end_date)
2941 and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2942 )
2943 -- Get first row that matches all of the above criteria
2944 and rownum = 1;
2945 */
2946 --
2947 cursor csr_old_sppb_rate is
2948 select petf.element_information6, petf.element_information9
2949 from pay_element_types_f petf
2950 where petf.element_name = 'Statutory Paternity Pay Birth'
2951 and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
2952 order by effective_start_date desc;
2953 --
2954 l_new_sppb_rate number;
2955 l_old_sppb_rate number;
2956 --
2957 l_new_std_sppb_rate number;
2958 l_old_std_sppb_rate number;
2959 --
2960 begin
2961 --
2962 hr_utility.trace('Entering sppb_rate_changed function');
2963 --
2964 /* Find SPPB rate for new tax year */
2965 open csr_new_sppb_rate;
2966 fetch csr_new_sppb_rate into l_new_sppb_rate, l_new_std_sppb_rate;
2967 --
2968 /* If unable to find new SPPB rate, then rate probably not set yet */
2969 /* Make note of this and exit quietly */
2970 if csr_new_sppb_rate%notfound then
2971 close csr_new_sppb_rate;
2972 raise e_sppb_rate_not_set;
2973 end if;
2974 --
2975 close csr_new_sppb_rate;
2976 --
2977 hr_utility.trace('New SPPB rate: '||l_new_sppb_rate);
2978 hr_utility.trace('New Std SPPB rate: '||l_new_Std_sppb_rate);
2979 --
2980 open csr_old_sppb_rate;
2981 fetch csr_old_sppb_rate into l_old_sppb_rate, l_old_std_sppb_rate;
2982 close csr_old_sppb_rate;
2983 --
2984 hr_utility.trace('old SPPB rate: '||l_old_sppb_rate);
2985 hr_utility.trace('old Std SPPB rate: '||l_old_Std_sppb_rate);
2986 --
2987 if (l_new_sppb_rate = l_old_sppb_rate) and (l_new_std_sppb_rate = l_old_std_sppb_rate) then
2988 return false;
2989 else
2990 return true;
2991 end if;
2992 --
2993 exception
2994 when others then raise;
2995 end sppb_rate_changed;
2996
2997
2998
2999 /*Start of Bug 13400812*/
3000 function asppb_rate_changed return boolean is
3001
3002 /* Check for ASPPB rates beginning on or after April 1st */
3003 cursor csr_new_asppb_rate is
3004 select petf.element_information6, petf.element_information9
3005 from pay_element_types_f petf
3006 where petf.element_name = 'Additional Statutory Paternity Pay Birth'
3007 and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
3008 order by effective_start_date;
3009
3010
3011 cursor csr_old_asppb_rate is
3012 select petf.element_information6, petf.element_information9
3013 from pay_element_types_f petf
3014 where petf.element_name = 'Additional Statutory Paternity Pay Birth'
3015 and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
3016 order by effective_start_date desc;
3017 --
3018 l_new_asppb_rate number;
3019 l_old_asppb_rate number;
3020 --
3021 l_new_std_asppb_rate number;
3022 l_old_std_asppb_rate number;
3023 --
3024 begin
3025 --
3026 hr_utility.trace('Entering asppb_rate_changed function');
3027 --
3028 /* Find ASPPB rate for new tax year */
3029 open csr_new_asppb_rate;
3030 fetch csr_new_asppb_rate into l_old_asppb_rate, l_new_std_asppb_rate;
3031 --
3032 /* If unable to find new ASPPB rate, then rate probably not set yet */
3033 /* Make note of this and exit quietly */
3034 if csr_new_asppb_rate%notfound then
3035 close csr_new_asppb_rate;
3036 raise e_asppb_rate_not_set;
3037 end if;
3038 --
3039 close csr_new_asppb_rate;
3040 --
3041 hr_utility.trace('New ASPPB rate: '||l_new_asppb_rate);
3042 hr_utility.trace('New Std ASPPB rate: '||l_new_std_asppb_rate);
3043 --
3044 open csr_old_asppb_rate;
3045 fetch csr_old_asppb_rate into l_old_asppb_rate, l_old_std_asppb_rate;
3046 close csr_old_asppb_rate;
3047 --
3048 hr_utility.trace('old ASPPB rate: '||l_old_asppb_rate);
3049 hr_utility.trace('old Std ASPPB rate: '||l_old_std_asppb_rate);
3050 --
3051 if (l_new_asppb_rate= l_old_asppb_rate) and (l_new_std_asppb_rate = l_old_std_asppb_rate) then
3052 return false;
3053 else
3054 return true;
3055 end if;
3056 --
3057 exception
3058 when others then raise;
3059 end asppb_rate_changed;
3060
3061 function asppa_rate_changed return boolean is
3062
3063 /* Check for ASPPB rates beginning on or after April 1st */
3064 cursor csr_new_asppa_rate is
3065 select petf.element_information6, petf.element_information8
3066 from pay_element_types_f petf
3067 where petf.element_name = 'Additional Statutory Paternity Pay Adoption'
3068 and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
3069 order by effective_start_date;
3070
3071
3072 cursor csr_old_asppa_rate is
3073 select petf.element_information6, petf.element_information8
3074 from pay_element_types_f petf
3075 where petf.element_name = 'Additional Statutory Paternity Pay Adoption'
3076 and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
3077 order by effective_start_date desc;
3078 --
3079 l_new_asppa_rate number;
3080 l_old_asppa_rate number;
3081 --
3082 l_new_std_asppa_rate number;
3083 l_old_std_asppa_rate number;
3084 --
3085 begin
3086 --
3087 hr_utility.trace('Entering asppb_rate_changed function');
3088 --
3089 /* Find SPPB rate for new tax year */
3090 open csr_new_asppa_rate;
3091 fetch csr_new_asppa_rate into l_old_asppa_rate, l_new_std_asppa_rate;
3092 --
3093 /* If unable to find new ASPPA rate, then rate probably not set yet */
3094 /* Make note of this and exit quietly */
3095 if csr_new_asppa_rate%notfound then
3096 close csr_new_asppa_rate;
3097 raise e_asppa_rate_not_set;
3098 end if;
3099 --
3100 close csr_new_asppa_rate;
3101 --
3102 hr_utility.trace('New ASPPA rate: '||l_new_asppa_rate);
3103 hr_utility.trace('New Std ASPPA rate: '||l_new_std_asppa_rate);
3104 --
3105 open csr_old_asppa_rate;
3106 fetch csr_old_asppa_rate into l_old_asppa_rate, l_old_std_asppa_rate;
3107 close csr_old_asppa_rate;
3108 --
3109 hr_utility.trace('old ASPPA rate: '||l_old_asppa_rate);
3110 hr_utility.trace('old Std ASPPA rate: '||l_old_std_asppa_rate);
3111 --
3112 if (l_new_asppa_rate= l_old_asppa_rate) and (l_new_std_asppa_rate = l_old_std_asppa_rate) then
3113 return false;
3114 else
3115 return true;
3116 end if;
3117 --
3118 exception
3119 when others then raise;
3120 end asppa_rate_changed;
3121
3122 /*End of Bug 13400812*/
3123
3124
3125 /* Main program body */
3126 begin
3127
3128 hr_utility.trace('Entering: '||g_package||'.update_ssp_smp_entries');
3129
3130 savepoint pre_update_status;
3131 /* Check first whether SSP element entries already updated for new tax year */
3132 /* SSP update block */
3133 begin
3134
3135 savepoint pre_ssp_update_status;
3136
3137 if not ssp_entries_already_updated
3138 then
3139
3140 hr_utility.trace('SSP element entries not updated, updating ....');
3141
3142 for r_affected_absences in csr_affected_absences loop
3143
3144 /* SSP control call block */
3145 begin
3146
3147 hr_utility.trace('Processing SSP absence: '||r_affected_absences.absence_id);
3148 --6800788 begin
3149 --ssp_ssp_pkg.ssp_control(r_affected_absences.absence_id);
3150 DBMS_JOB.SUBMIT(l_job_no,'ssp_ssp_pkg.ssp_control('||r_affected_absences.absence_id||');');
3151 l_count := l_count + 1;
3152 l_tbl_job(l_count).job_no := l_job_no;
3153 l_tbl_job(l_count).person_id := r_affected_absences.person_id;
3154 l_tbl_job(l_count).absence_id := r_affected_absences.absence_id;
3155 if mod(l_count,500) = 0 then
3156 commit;
3157 end if;
3158 --6800788 end
3159 exception
3160
3161 when others then
3162 hr_utility.trace('Error occurred while processing SSP absence: '||r_affected_absences.absence_id);
3163 hr_utility.trace('SQL error code: '||SQLCODE);
3164 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3165 hr_utility.trace('Person id: '||r_affected_absences.person_id);
3166 hr_utility.trace('Absence start date: '||r_affected_absences.absence_start_date);
3167 hr_utility.trace('Absence end date: '||r_affected_absences.absence_end_date);
3168 l_update_error := true;
3169
3170 end;
3171
3172
3173
3174 end loop;
3175
3176 hr_utility.trace('Updated entries for '||l_count||' absences');
3177 -- dbms_output.put_line(' Total SSP absences ='|| l_count);
3178 else
3179
3180 hr_utility.trace('SSP element entries already updated for new tax year');
3181
3182 end if;
3183
3184 commit;
3185
3186 exception
3187
3188 when e_ssp_rate_not_set
3189 then
3190 hr_utility.trace('Warning: SSP rate for new tax year not set');
3191 hr_utility.trace('Unable to proceed with updating SSP entries');
3192
3193
3194 when e_no_new_ssp_entry
3195 then
3196 hr_utility.trace('Warning: Unable to locate SSP entry in new tax year');
3197 hr_utility.trace('Entries for SSP absences in new tax year may be non-existent');
3198 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end dates of absences');
3199
3200 when others
3201 then
3202 hr_utility.trace('Unexpected error occurred inside SSP update block');
3203 hr_utility.trace('SQL error number: '||SQLCODE);
3204 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3205 rollback to pre_ssp_update_status;
3206 l_update_error := true;
3207
3208 end; /* SSP update block */
3209
3210 /* Now check SMP entries to see if they have been updated */
3211 /* SMP update block */
3212 -- 05/12/2003 rmakhija: Uncommented following section for TYE 2003/4
3213 begin
3214 --
3215 savepoint pre_smp_update_status;
3216 --
3217 if smp_rate_changed then
3218 --
3219 hr_utility.trace('SMP element entries updating ....');
3220 for r_affected_maternities in csr_affected_leave('MA') loop
3221 /* SMP Control call block */
3222 begin
3223 hr_utility.trace('Processing SMP absence: '||r_affected_maternities.maternity_id);
3224 --6800788 begin
3225 -- ssp_smp_pkg.smp_control(p_maternity_id => r_affected_maternities.maternity_id,
3226 -- p_deleting => FALSE);
3227
3228 DBMS_JOB.SUBMIT(l_job_no,'ssp_smp_pkg.smp_control('||r_affected_maternities.maternity_id||',FALSE);');
3229 l_mat_count := l_mat_count + 1;
3230 l_count := l_count + 1;
3231 l_tbl_job(l_count).job_no := l_job_no;
3232 l_tbl_job(l_count).person_id := r_affected_maternities.person_id;
3233 l_tbl_job(l_count).absence_id := r_affected_maternities.maternity_id;
3234 if mod(l_count,500) = 0 then
3235 commit;
3236 end if;
3237 --6800788 end
3238
3239 exception
3240 when others then
3241 hr_utility.trace('Error occurred while processing SMP absence: '||r_affected_maternities.maternity_id);
3242 hr_utility.trace('SQL error code: '||SQLCODE);
3243 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3244 hr_utility.trace('Person id: '||r_affected_maternities.person_id);
3245 hr_utility.trace('Maternity start date: '||r_affected_maternities.date_start);
3246 hr_utility.trace('Maternity end date: '||r_affected_maternities.date_end);
3247 hr_utility.trace('Maternity projected start date: '||r_affected_maternities.date_projected_start);
3248 hr_utility.trace('Maternity projected end date: '||r_affected_maternities.date_projected_end);
3249 l_update_error := true;
3250 end;
3251 end loop;
3252 hr_utility.trace('Updated entries for '||l_mat_count||' maternities');
3253 -- dbms_output.put_line(' Total SMP absences ='|| l_mat_count);
3254 else
3255 hr_utility.trace('SMP element entries already updated for new tax year');
3256 end if;
3257 --
3258 commit;
3259 --
3260 exception
3261 when e_smp_rate_not_set then
3262 hr_utility.trace('Warning: SMP rate for new tax year not set');
3263 hr_utility.trace('Unable to proceed with updating SMP entries');
3264 when e_no_new_smp_entry then
3265 hr_utility.trace('Warning: Unable to locate SMP entry in new tax year');
3266 hr_utility.trace('Entries for SMP absences in new tax year may be non-existent');
3267 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
3268 when others then
3269 hr_utility.trace('Unexpected error occurred inside SMP update block');
3270 hr_utility.trace('SQL error number: '||SQLCODE);
3271 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3272 rollback to pre_smp_update_status;
3273 l_update_error := true;
3274 end; /* SMP update block */
3275 -- 05/12/2003 rmakhija: Uncommented section ends here
3276 -- Following code has been added to auto update SAP/SPP Adoption/SPP Birth element entries
3277 -- Begin SAP Update Block
3278 BEGIN
3279 --
3280 savepoint pre_sap_update_status;
3281 --
3282 if sap_rate_changed then
3283 --
3284 hr_utility.trace('SAP element entries updating ....');
3285 for r_affected_adoption in csr_affected_leave('AD') loop
3286 /* SAP Control call block */
3287 begin
3288 hr_utility.trace('Processing SAP absence: '||r_affected_adoption.maternity_id);
3289 --6800788 begin
3290 -- ssp_sap_pkg.sap_control(p_maternity_id => r_affected_adoption.maternity_id,
3291 -- p_deleting => FALSE);
3292
3293 DBMS_JOB.SUBMIT(l_job_no,'ssp_sap_pkg.sap_control('||r_affected_adoption.maternity_id||',FALSE);');
3294 l_adop_count := l_adop_count + 1;
3295 l_count := l_count + 1;
3296 l_tbl_job(l_count).job_no := l_job_no;
3297 l_tbl_job(l_count).person_id := r_affected_adoption.person_id;
3298 l_tbl_job(l_count).absence_id := r_affected_adoption.maternity_id;
3299 if mod(l_count,500) = 0 then
3300 commit;
3301 end if;
3302 --6800788 end
3303
3304 exception
3305 when others then
3306 hr_utility.trace('Error occurred while processing SAP absence: '||r_affected_adoption.maternity_id);
3307 hr_utility.trace('SQL error code: '||SQLCODE);
3308 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3309 hr_utility.trace('Person id: '||r_affected_adoption.person_id);
3310 hr_utility.trace('Adoption start date: '||r_affected_adoption.date_start);
3311 hr_utility.trace('Adoption end date: '||r_affected_adoption.date_end);
3312 hr_utility.trace('Adoption projected start date: '||r_affected_adoption.date_projected_start);
3313 hr_utility.trace('Adoption projected end date: '||r_affected_adoption.date_projected_end);
3314 l_update_error := true;
3315 end;
3316 end loop;
3317 hr_utility.trace('Updated entries for '||l_adop_count||' adoptions');
3318 -- dbms_output.put_line(' Total SAP absences ='|| l_adop_count);
3319 else
3320 hr_utility.trace('SAP element entries already updated for new tax year');
3321 end if;
3322 --
3323 commit;
3324 --
3325 exception
3326 when e_sap_rate_not_set then
3327 hr_utility.trace('Warning: SAP rate for new tax year not set');
3328 hr_utility.trace('Unable to proceed with updating SAP entries');
3329 when e_no_new_sap_entry then
3330 hr_utility.trace('Warning: Unable to locate SAP entry in new tax year');
3331 hr_utility.trace('Entries for SAP absences in new tax year may be non-existent');
3332 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
3333 when others then
3334 hr_utility.trace('Unexpected error occurred inside SAP update block');
3335 hr_utility.trace('SQL error number: '||SQLCODE);
3336 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3337 rollback to pre_sap_update_status;
3338 l_update_error := true;
3339 end; /* SAP update block */
3340 -- Begin SPP Adoption Update Block
3341 BEGIN
3342 --
3343 savepoint pre_sppa_update_status;
3344 --
3345 if sppa_rate_changed then
3346 --
3347 hr_utility.trace('SPP Adoption element entries updating ....');
3348 for r_affected_pat_adop in csr_affected_leave('PA') loop
3349 /* SPP Adoption Control call block */
3350 begin
3351 hr_utility.trace('Processing SPP Adoption absence: '||r_affected_pat_adop.maternity_id);
3352 --6800788 begin
3353 -- ssp_pad_pkg.pad_control(p_maternity_id => r_affected_pat_adop.maternity_id,
3354 -- p_deleting => FALSE);
3355
3356 DBMS_JOB.SUBMIT(l_job_no,'ssp_pad_pkg.pad_control('||r_affected_pat_adop.maternity_id||',FALSE);');
3357 l_pat_adop_count := l_pat_adop_count + 1;
3358 l_count := l_count + 1;
3359 l_tbl_job(l_count).job_no := l_job_no;
3360 l_tbl_job(l_count).person_id := r_affected_pat_adop.person_id;
3361 l_tbl_job(l_count).absence_id := r_affected_pat_adop.maternity_id;
3362 if mod(l_count,500) = 0 then
3363 commit;
3364 end if;
3365 --6800788 end
3366
3367 exception
3368 when others then
3369 hr_utility.trace('Error occurred while processing SPP Adoption absence: '||r_affected_pat_adop.maternity_id);
3370 hr_utility.trace('SQL error code: '||SQLCODE);
3371 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3372 hr_utility.trace('Person id: '||r_affected_pat_adop.person_id);
3373 hr_utility.trace('Paternity Adoption start date: '||r_affected_pat_adop.date_start);
3374 hr_utility.trace('Paternity Adoption end date: '||r_affected_pat_adop.date_end);
3375 hr_utility.trace('Paternity Adoption projected start date: '||r_affected_pat_adop.date_projected_start);
3376 hr_utility.trace('Paternity Adoption projected end date: '||r_affected_pat_adop.date_projected_end);
3377 l_update_error := true;
3378 end;
3379 end loop;
3380 hr_utility.trace('Updated entries for '||l_pat_adop_count||' paternity adoptions');
3381 -- dbms_output.put_line(' Total SPPA absences ='|| l_pat_adop_count);
3382 else
3383 hr_utility.trace('SPP Adoption element entries already updated for new tax year');
3384 end if;
3385 --
3386 commit;
3387 --
3388 exception
3389 when e_sppa_rate_not_set then
3390 hr_utility.trace('Warning: SPP ADoption rate for new tax year not set');
3391 hr_utility.trace('Unable to proceed with updating SPP ADoption entries');
3392 when e_no_new_sppa_entry then
3393 hr_utility.trace('Warning: Unable to locate SPP ADoption entry in new tax year');
3394 hr_utility.trace('Entries for SPP ADoption absences in new tax year may be non-existent');
3395 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
3396 when others then
3397 hr_utility.trace('Unexpected error occurred inside SPP ADoption update block');
3398 hr_utility.trace('SQL error number: '||SQLCODE);
3399 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3400 rollback to pre_sppa_update_status;
3401 l_update_error := true;
3402 end; /* SPP Adoption update block */
3403 -- Begin SPP Birth Update Block
3404 BEGIN
3405 --
3406 savepoint pre_sppb_update_status;
3407 --
3408 if sppb_rate_changed then
3409 --
3410 hr_utility.trace('SPP Birth element entries updating ....');
3411 --6800788 begin
3412 -- for r_affected_pat_Birth in csr_affected_leave('PA') loop
3413 for r_affected_pat_Birth in csr_affected_leave('PB') loop
3414 --6800788 end
3415 /* SPP Birth Control call block */
3416 begin
3417 hr_utility.trace('Processing SPP Birth absence: '||r_affected_pat_Birth.maternity_id);
3418 --6800788 begin
3419 -- ssp_pab_pkg.pab_control(p_maternity_id => r_affected_pat_Birth.maternity_id,
3420 -- p_deleting => FALSE);
3421
3422 DBMS_JOB.SUBMIT(l_job_no,'ssp_pab_pkg.pab_control('||r_affected_pat_Birth.maternity_id||',FALSE);');
3423 l_pat_Birth_count := l_pat_birth_count + 1;
3424 l_count := l_count + 1;
3425 l_tbl_job(l_count).job_no := l_job_no;
3426 l_tbl_job(l_count).person_id := r_affected_pat_Birth.person_id;
3427 l_tbl_job(l_count).absence_id := r_affected_pat_Birth.maternity_id;
3428 if mod(l_count,500) = 0 then
3429 commit;
3430 end if;
3431 --6800788 end
3432
3433
3434 exception
3435 when others then
3436 hr_utility.trace('Error occurred while processing SPP Birth absence: '||r_affected_pat_Birth.maternity_id);
3437 hr_utility.trace('SQL error code: '||SQLCODE);
3438 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3439 hr_utility.trace('Person id: '||r_affected_pat_Birth.person_id);
3440 hr_utility.trace('Paternity Birth start date: '||r_affected_pat_Birth.date_start);
3441 hr_utility.trace('Paternity Birth end date: '||r_affected_pat_Birth.date_end);
3442 hr_utility.trace('Paternity Birth projected start date: '||r_affected_pat_Birth.date_projected_start);
3443 hr_utility.trace('Paternity Birth projected end date: '||r_affected_pat_Birth.date_projected_end);
3444 l_update_error := true;
3445 end;
3446 end loop;
3447 hr_utility.trace('Updated entries for '||l_pat_birth_count||' paternity births');
3448 -- dbms_output.put_line(' Total SPPB absences ='|| l_pat_birth_count);
3449 else
3450 hr_utility.trace('SPP Birth element entries already updated for new tax year');
3451 end if;
3452 --
3453 commit;
3454 --
3455 exception
3456 when e_sppb_rate_not_set then
3457 hr_utility.trace('Warning: SPP Birth rate for new tax year not set');
3458 hr_utility.trace('Unable to proceed with updating SPP Birth entries');
3459 when e_no_new_sppb_entry then
3460 hr_utility.trace('Warning: Unable to locate SPP Birth entry in new tax year');
3461 hr_utility.trace('Entries for SPP Birth absences in new tax year may be non-existent');
3462 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
3463 when others then
3464 hr_utility.trace('Unexpected error occurred inside SPP Birth update block');
3465 hr_utility.trace('SQL error number: '||SQLCODE);
3466 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3467 rollback to pre_sppb_update_status;
3468 l_update_error := true;
3469 end; /* SPP Birth update block */
3470
3471
3472 /*Start of Bug 13400812
3473 Calls to ASPP Entries*/
3474 begin
3475 --
3476 savepoint pre_asppa_update_status;
3477 --
3478 if asppa_rate_changed then
3479 --
3480 hr_utility.trace('ASPP Adoption element entries updating ....');
3481 for r_affected_apat_adop in csr_affected_leave('AA') loop
3482 /* ASPP Adoption Control call block */
3483 begin
3484 hr_utility.trace('Processing ASPP Adoption absence: '||r_affected_apat_adop.maternity_id);
3485
3486
3487 DBMS_JOB.SUBMIT(l_job_no,'ssp_apad_pkg.apad_control('||r_affected_apat_adop.maternity_id||',FALSE);');
3488 l_apat_adop_count := l_apat_adop_count + 1;
3489 l_count := l_count + 1;
3490 l_tbl_job(l_count).job_no := l_job_no;
3491 l_tbl_job(l_count).person_id := r_affected_apat_adop.person_id;
3492 l_tbl_job(l_count).absence_id := r_affected_apat_adop.maternity_id;
3493 if mod(l_count,500) = 0 then
3494 commit;
3495 end if;
3496
3497 exception
3498 when others then
3499 hr_utility.trace('Error occurred while processing SPP Adoption absence: '||r_affected_apat_adop.maternity_id);
3500 hr_utility.trace('SQL error code: '||SQLCODE);
3501 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3502 hr_utility.trace('Person id: '||r_affected_apat_adop.person_id);
3503 hr_utility.trace('Additional Paternity Adoption start date: '||r_affected_apat_adop.date_start);
3504 hr_utility.trace('Additional Paternity Adoption end date: '||r_affected_apat_adop.date_end);
3505 hr_utility.trace('Additional Paternity Adoption projected start date: '||r_affected_apat_adop.date_projected_start);
3506 hr_utility.trace('Additional Paternity Adoption projected end date: '||r_affected_apat_adop.date_projected_end);
3507 l_update_error := true;
3508 end;
3509 end loop;
3510 hr_utility.trace('Updated entries for '||l_apat_adop_count||' paternity adoptions');
3511
3512 else
3513 hr_utility.trace('ASPP Adoption element entries already updated for new tax year');
3514 end if;
3515 --
3516 commit;
3517 --
3518 exception
3519 when e_asppa_rate_not_set then
3520 hr_utility.trace('Warning: ASPP ADoption rate for new tax year not set');
3521 hr_utility.trace('Unable to proceed with updating ASPP ADoption entries');
3522 when e_no_new_asppa_entry then
3523 hr_utility.trace('Warning: Unable to locate ASPP ADoption entry in new tax year');
3524 hr_utility.trace('Entries for ASPP ADoption absences in new tax year may be non-existent');
3525 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
3526 when others then
3527 hr_utility.trace('Unexpected error occurred inside ASPP ADoption update block');
3528 hr_utility.trace('SQL error number: '||SQLCODE);
3529 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3530 rollback to pre_asppa_update_status;
3531 l_update_error := true;
3532
3533 end;
3534
3535 --
3536 begin
3537 savepoint pre_asppb_update_status;
3538 --
3539 if asppb_rate_changed then
3540 --
3541 hr_utility.trace('ASPP Birth element entries updating ....');
3542 for r_affected_apat_birth in csr_affected_leave('AB') loop
3543 /* ASPP Adoption Control call block */
3544 begin
3545 hr_utility.trace('Processing ASPP Birth absence: '||r_affected_apat_birth.maternity_id);
3546
3547 DBMS_JOB.SUBMIT(l_job_no,'ssp_apab_pkg.apab_control('||r_affected_apat_birth.maternity_id||',FALSE);');
3548 l_apat_birth_count := l_apat_birth_count + 1;
3549 l_count := l_count + 1;
3550 l_tbl_job(l_count).job_no := l_job_no;
3551 l_tbl_job(l_count).person_id := r_affected_apat_birth.person_id;
3552 l_tbl_job(l_count).absence_id := r_affected_apat_birth.maternity_id;
3553 if mod(l_count,500) = 0 then
3554 commit;
3555 end if;
3556
3557 exception
3558 when others then
3559 hr_utility.trace('Error occurred while processing SPP Birth absence: '||r_affected_apat_birth.maternity_id);
3560 hr_utility.trace('SQL error code: '||SQLCODE);
3561 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3562 hr_utility.trace('Person id: '||r_affected_apat_birth.person_id);
3563 hr_utility.trace('Additional Paternity Birth start date: '||r_affected_apat_birth.date_start);
3564 hr_utility.trace('Additional Paternity Birth end date: '||r_affected_apat_birth.date_end);
3565 hr_utility.trace('Additional Paternity Birth projected start date: '||r_affected_apat_birth.date_projected_start);
3566 hr_utility.trace('Additional Paternity Birth projected end date: '||r_affected_apat_birth.date_projected_end);
3567 l_update_error := true;
3568 end;
3569 end loop;
3570 hr_utility.trace('Updated entries for '||l_apat_birth_count||' paternity adoptions');
3571
3572 else
3573 hr_utility.trace('ASPP Birth element entries already updated for new tax year');
3574 end if;
3575 --
3576 commit;
3577 --
3578 exception
3579 when e_asppb_rate_not_set then
3580 hr_utility.trace('Warning: ASPP Birth rate for new tax year not set');
3581 hr_utility.trace('Unable to proceed with updating ASPP Birth entries');
3582 when e_no_new_asppb_entry then
3583 hr_utility.trace('Warning: Unable to locate ASPP Birth entry in new tax year');
3584 hr_utility.trace('Entries for ASPP Birth absences in new tax year may be non-existent');
3585 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
3586 when others then
3587 hr_utility.trace('Unexpected error occurred inside ASPP Birth update block');
3588 hr_utility.trace('SQL error number: '||SQLCODE);
3589 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3590 rollback to pre_asppb_update_status;
3591 l_update_error := true;
3592 end;
3593
3594 /*End of Bug 13400812*/
3595
3596
3597 p_update_error := l_update_error;
3598
3599 --6800788 begin
3600 hr_utility.trace(' Total Absences to be processed '|| l_count);
3601 --DBMS_OUTPUT.PUT_LINE(' Total Absences to be processed '|| l_count);
3602 for I in 1..l_count
3603 loop
3604 loop
3605 begin
3606
3607 select nvl(FAILURES,0) into l_fail from dba_jobs
3608 where job = l_tbl_job(I).job_no and rownum = 1;
3609
3610 --Bug Fix 6870415 Begin
3611 -- IF l_fail > 0
3612 IF l_fail > 1
3613 --Bug Fix 6870415 End
3614 THEN
3615 l_fail_count := l_fail_count + 1;
3616 DBMS_JOB.REMOVE(l_tbl_job(I).job_no);
3617 commit;
3618 p_job_err(l_fail_count) := ' Process Failed for person id '||l_tbl_job(I).person_id||' with absence id '||l_tbl_job(I).absence_id;
3619 hr_utility.trace(' Job '||l_tbl_job(I).job_no||' Failed for person id '||l_tbl_job(I).person_id||' with absence id '||l_tbl_job(I).absence_id);
3620 -- DBMS_OUTPUT.PUT_LINE(' Job '||l_tbl_job(I).job_no||' Failed for person id '||l_tbl_job(I).person_id||' with absence id '||l_tbl_job(I).absence_id);
3621 p_update_error := true;
3622 exit;
3623 ELSE
3624 DBMS_LOCK.SLEEP(20);
3625 END IF;
3626 Exception
3627 when no_data_found then
3628 exit;
3629 end;
3630 end loop;
3631 end loop;
3632 hr_utility.trace('Total Absences failed '||l_fail_count);
3633 --DBMS_OUTPUT.PUT_LINE('Total Absences failed '||l_fail_count);
3634 --6800788 end
3635 hr_utility.trace('Update of SSP, SMP, SAP, SPP Adoption and SPP Birth entries complete');
3636 hr_utility.trace('Leaving: '||g_package||'.update_ssp_smp_entries');
3637
3638
3639 exception
3640
3641
3642 when others
3643 then
3644 hr_utility.trace('Unexpected error occurred inside SSP/SMP element entries update procedure');
3645 hr_utility.trace('SQL error number: '||SQLCODE);
3646 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3647 rollback to pre_update_status;
3648 p_update_error := true;
3649
3650
3651
3652
3653 end update_ssp_smp_entries;
3654
3655 end ssp_smp_support_pkg;
[Home] [Help]