1 package body SSP_SMP_SUPPORT_PKG as
2 /* $Header: spsspbsi.pkb 120.10.12010000.5 2008/08/25 08:55:48 pbalu ship $
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 */
145 --------------------------------------------------------------------------------
146 g_package constant varchar2 (31) := 'ssp_smp_support_pkg.';
147
148
149 --
150 cursor csr_entry_value (
151 --
152 p_element_entry_id in number,
153 p_input_value_name in varchar2) is
154 --
155 -- Selects an entry value for a given
156 -- element entry and named input value
157 --
158 select entry.screen_entry_value,
159 inp.uom,
160 ele.input_currency_code,
161 inp.input_value_id
162 from pay_element_entry_values_f entry,
163 pay_input_values_f inp,
164 pay_element_types_f ele
165 where entry.element_entry_id = p_element_entry_id
166 and inp.name = p_input_value_name
167 and entry.input_value_id = inp.input_value_id
168 and ele.element_type_id = inp.element_type_id
169 and ele.effective_start_date <= inp.effective_end_date
170 and ele.effective_end_date >= inp.effective_start_date
171 and inp.effective_start_date <= entry.effective_end_date
172 and inp.effective_end_date >= entry.effective_start_date;
173 --
174 --------------------------------------------------------------------------------
175 function start_of_week (p_date date) return date is
176 --
177 -- Returns the date of the last Sunday before the p_date, or the p_date if
178 -- that is actually a Sunday anyway.
179 --
180 l_Sunday date := p_date;
181 l_proc varchar2 (72) := g_package||'start_of_week';
182 l_sunday_txt varchar2(100) := rtrim(to_char(to_date('07/01/2001','DD/MM/YYYY'),'DAY'));
183 --
184 begin
185 --
186 hr_utility.set_location('Entering:'||l_proc,1);
187 --
188 hr_utility.trace(' p_date: '||to_char (p_date));
189 --
190 if p_date is not null and p_date <> hr_general.end_of_time
191 then
192 if rtrim (to_char (p_date, 'DAY')) <> l_sunday_txt then
193 l_Sunday := next_day (p_date, l_sunday_txt) -7;
194 end if;
195 end if;
196 --
197 hr_utility.set_location('Leaving :'||l_proc,100);
198 --
199 return l_Sunday;
200 --
201 end start_of_week;
202 --------------------------------------------------------------------------------
203 function end_of_week (p_date date) return date is
204 --
205 -- Returns the date of the Saturday following the p_date, or the p_date if
206 -- that is actually a Saturday anyway.
207 --
208 l_Saturday date := p_date;
209 l_proc varchar2 (72) := g_package||'end_of_week';
210 l_saturday_txt varchar2(100) := rtrim(to_char(to_date('06/01/2001','DD/MM/YYYY'),'DAY'));
211 --
212 begin
213 --
214 hr_utility.set_location('Entering:'||l_proc,1);
215 --
216 hr_utility.trace(' p_date: '||to_char (p_date));
217 --
218 if p_date is not null and p_date <> hr_general.end_of_time
219 then
220 if rtrim (to_char (p_date, 'DAY')) <> l_saturday_txt then
221 l_Saturday := next_day (p_date, l_saturday_txt);
222 end if;
223 end if;
224 --
225 hr_utility.set_location('Leaving :'||l_proc,100);
226 --
227 return l_Saturday;
228 --
229 end end_of_week;
230 --------------------------------------------------------------------------------
231 function NI_Lower_Earnings_Limit (p_effective_date in date) return number is
232 --
233 cursor csr_LEL is
234 --
235 -- Get the LEL as at the effective date
236 --
237 select to_number(ni.global_value) LEL
238 from ff_globals_f ni
239 where ni.global_name = 'NI_WEEKLY_LEL'
240 and ni.business_group_id is null
241 and ni.legislation_code = 'GB'
242 and p_effective_date between ni.effective_start_date
243 and ni.effective_end_date;
244 --
245 l_proc varchar2 (72) := g_package||'NI_Lower_Earnings_Limit';
246 l_LEL number;
247 --
248 procedure check_parameters is
249 --
250 all_parameters_valid constant boolean := (p_effective_date is not null
251 and p_effective_date = trunc (p_effective_date));
252 --
253 begin
254 --
255 hr_utility.trace (l_proc||' p_effective_date = '
256 ||to_char (p_effective_date));
257 --
258 hr_general.assert_condition (all_parameters_valid);
259 --
260 end check_parameters;
261 --
262 begin
263 --
264 hr_utility.set_location('Entering:'||l_proc,1);
265 --
266 check_parameters;
267 --
268 open csr_LEL;
269 fetch csr_LEL into l_lel;
270 close csr_LEL;
271 --
272 if l_LEL is null then
273 --
274 -- The LEL is seeded on to user tables, but we do not seed data for the
275 -- tax years before 95/96 so if the customer has need for the LEL value
276 -- before this date (eg for historic absences) then we must set the historic
277 -- value before returning.
278 --
279 hr_utility.trace (' No LEL defined on user tables; hard-coding value');
280 --
281 if p_effective_date between to_date ('06-04-1991', 'DD-MM-YYYY')
282 and to_date ('05-04-1992', 'DD-MM-YYYY')
283 then
284 l_LEL := 52.00; -- Tax Year 91/92
285 elsif
286 p_effective_date between to_date ('06-04-1992', 'DD-MM-YYYY')
287 and to_date ('05-04-1993', 'DD-MM-YYYY')
288 then
289 l_LEL := 54.00; -- Tax Year 92/93
290 elsif
291 p_effective_date between to_date ('06-04-1993', 'DD-MM-YYYY')
292 and to_date ('05-04-1994', 'DD-MM-YYYY')
293 then
294 l_LEL := 56.00; -- Tax Year 93/94
295 elsif
296 p_effective_date between to_date ('06-04-1994', 'DD-MM-YYYY')
297 and to_date ('05-04-1995', 'DD-MM-YYYY')
298 then
299 l_LEL := 57.00; -- Tax Year 94/95
300 end if;
301 end if;
302 --
303 hr_utility.set_location('Leaving :'||l_proc, 100);
304 --
305 return l_LEL;
306 --
307 end NI_Lower_Earnings_Limit;
308 --------------------------------------------------------------------------------
309 function entry_already_processed (
310 --
311 -- Returns TRUE if the entry passed in has already been processed in a
312 -- payroll run
313 --
314 p_element_entry_id in number) return boolean is
315 --
316 cursor csr_processed is
317 select 1
318 from pay_run_results
319 where source_id = p_element_entry_id
320 and status <> 'U';
321 --
322 l_processed boolean := FALSE;
323 l_dummy integer (1);
324 --
325 begin
326 --
327 open csr_processed;
328 fetch csr_processed into l_dummy;
329 --
330 if csr_processed%found then
331 l_processed := TRUE;
332 end if;
333 --
334 close csr_processed;
335 --
336 return l_processed;
337 --
338 end entry_already_processed;
339 --------------------------------------------------------------------------------
340 function value (
341 --
342 -- Returns an entry value for a named input value
343 -- and element entry.
344 --
345 p_element_entry_id number,
346 p_input_value_name varchar2)
347 --
348 return varchar2 is
349 --
350 l_entry_value csr_entry_value%rowtype;
351 l_value varchar2 (80);
352 --
353 begin
354 --
355 open csr_entry_value (p_element_entry_id,p_input_value_name);
356 fetch csr_entry_value into l_entry_value;
357 close csr_entry_value;
358 --
359 l_value := hr_chkfmt.changeformat ( l_entry_value.screen_entry_value,
360 l_entry_value.uom,
361 l_entry_value.input_currency_code);
362 return l_value;
363 --
364 end value;
365 --------------------------------------------------------------------------------
366 function element_input_value_id (
367 --
368 -- Returns the input value ID for a named
369 -- input value
370 --
371 p_element_type_id number,
372 p_input_value_name varchar2)
373 --
374 return number is
375 --
376 cursor csr_input_value is
377 select input_value_id
378 from pay_input_values_f
379 where name = p_input_value_name
380 and element_type_id = p_element_type_id;
381 --
382 l_input_value csr_input_value%rowtype;
383 --
384 begin
385 --
386 open csr_input_value;
387 fetch csr_input_value into l_input_value;
388 close csr_input_value;
389 --
390 return l_input_value.input_value_id;
391 --
392 end element_input_value_id;
393 --------------------------------------------------------------------------------
394 function withholding_reason_id (
395 --
396 -- Returns the ID of a withholding reason, given a reason and element type id
397 --
398 p_element_type_id in number,
399 p_reason in varchar2
400 --
401 ) return number is
402 --
403 cursor csr_reason_id is
404 --
405 -- Get the reason id.
406 --
407 select reason_id
408 from ssp_withholding_reasons
409 where upper (reason) = upper (p_reason)
410 and element_type_id = p_element_type_id;
411 --
412 l_reason_id number;
413 --
414 begin
415 --
416 open csr_reason_id;
417 fetch csr_reason_id into l_reason_id;
418 --
419 if csr_reason_id%notfound then
420 --
421 fnd_message.set_name ('SSP','SSP_35046_NO_SUCH_REASON');
422 fnd_message.set_token ('REASON',p_reason);
423 fnd_message.raise_error;
424 --
425 end if;
426 --
427 close csr_reason_id;
428 --
429 return l_reason_id;
430 --
431 end withholding_reason_id;
432 --------------------------------------------------------------------------------
433 function stoppage_overridden (
434 --
435 -- Returns TRUE if there is a stoppage for the specified reason which has been
436 -- overridden by the user.
437 --
438 p_reason_id in number,
439 p_absence_attendance_id in number default null,
440 p_maternity_id in number default null
441 ) return boolean is
442 --
443 cursor csr_stoppage is
444 --
445 -- Return a row if there is a stoppage of the specified type for the
446 -- absence.
447 --
448 -- 563202 - by concatenating null to reason_id forces a more efficient index
449 -- to be used.
450 select 1
451 from ssp_stoppages
452 where ((p_absence_attendance_id is not null
453 and p_absence_attendance_id = absence_attendance_id)
454 or (p_maternity_id is not null
455 and p_maternity_id = maternity_id))
456 and reason_id||null = p_reason_id
457 and override_stoppage = 'Y';
458 --
459 l_dummy integer (1);
460 l_stoppage_overridden boolean := FALSE;
461 --
462 begin
463 --
464 open csr_stoppage;
465 fetch csr_stoppage into l_dummy;
466 l_stoppage_overridden := csr_stoppage%found;
467 close csr_stoppage;
468 --
469 return l_stoppage_overridden;
470 --
471 end stoppage_overridden;
472 --------------------------------------------------------------------------------
473 procedure recalculate_SSP_and_SMP (p_deleting in boolean default FALSE) is
474 --
475 -- Recalculate SSP and SMP for any rows affected by DML.
476 --
477 cursor csr_affected_maternities is
478 --
479 -- Find all maternity ids which have been inserted by row triggers
480 -- because a change to one of their SMP parameters occurred. Exclude
481 -- rows which are already being processed (because the process may
482 -- cause the row triggers to fire and call this procedure recursively),
483 -- and rows which are duplicated.
484 --
485 select tar1.maternity_id, nvl(tar1.p_deleting, 'N') l_deleting
486 from ssp_temp_affected_rows TAR1
487 where tar1.maternity_id is not null
488 --and nvl (tar1.locked, 'NULL') <> 'Y'
489 and tar1.locked = to_char(userenv('sessionid'))
490 for update;
491 --
492 cursor csr_leave_type (p_maternity_id number) is
493 select leave_type
494 from ssp_maternities
495 where maternity_id = p_maternity_id;
496
497 cursor csr_affected_PIWs is
498 --
499 -- Find all PIW IDs which have been inserted by row triggers
500 -- because a change to one of their SSP parameters occurred.
501 -- Exclusion of duplicates now done in ssp_ssp_pkg.SSP-Control as
502 -- the previous "not exists" did not work... the data is already
503 -- selected before they can be updated.
504 --
505 select tar1.PIW_id, nvl(tar1.locked,'NULL') locked
506 from ssp_temp_affected_rows TAR1
507 where tar1.PIW_id is not null
508 -- and nvl (tar1.locked,'NULL') <> 'Y'
509 and tar1.locked = to_char(userenv('sessionid'))
510 for update;
511 --
512 cursor csr_count_absences(p_maternity_id number) is
513 select count(*)
514 from ssp_maternities mat,
515 per_absence_attendances ab
516 where mat.maternity_id = p_maternity_id
517 and ab.person_id = mat.person_id
518 and ab.maternity_id = mat.maternity_id;
519 --
520 l_proc varchar2 (72) := g_package||'recalculate_SSP_and_SMa';
521 l_deleting boolean;
522 row_deleted exception;
523 l_count number;
524 l_leave_type varchar2 (2);
525 pragma exception_init (row_deleted, -8006);
526 --
527 mutating_table exception;
528 pragma exception_init (mutating_table, -4091);
529 --
530 -- Oracle error -4091 occurs if a trigger attempts to read or modify a table
531 -- which is being modified by the code which caused the trigger to fire.
532 --
533 BEGIN
534 --
535 hr_utility.set_location('Entering:'||l_proc,1);
536 --
537 if ssp_ssp_pkg.ssp_is_installed
538 then
539 if csr_affected_PIWs%IsOpen then
540 close csr_affected_PIWs;
541 end if;
542 --
543 if csr_affected_maternities%IsOpen then
544 close csr_affected_maternities;
545 end if;
546 --
547 -- Recalculate SSP for all PIWs affected by row inserts/updates/deletes
548 --
549 -- Make sure all the linked PIWs are correctly defined.
550 ssp_ssp_pkg.update_linked_absence_IDs;
551 --
552 for each_PIW in csr_affected_PIWs LOOP
553 hr_utility.trace (' Recalculate SSP for PIW #'||
554 to_char(each_PIW.PIW_ID));
555 --
556 if each_PIW.locked <> 'Y'
557 then
558 update ssp_temp_affected_rows
559 set locked = 'Y'
560 where current of csr_affected_PIWs;
561 --
562 ssp_ssp_pkg.ssp_control(each_PIW.piw_id);
563 end if;
564 end loop;
565 --
566 -- Recalculate SMP for all maternities affected by inserts/updates/deletes
567 --
568 for each_maternity in csr_affected_maternities LOOP
569 hr_utility.trace (' Recalculate SMP for maternity_id # '
570 ||to_char (each_maternity.maternity_id));
571 --
572 update ssp_temp_affected_rows
573 set locked = 'Y'
574 where current of csr_affected_maternities;
575 --
576 if each_maternity.l_deleting = 'Y'
577 then
578 l_deleting := TRUE;
579 else
580 l_deleting := FALSE;
581 end if;
582 open csr_leave_type(each_maternity.maternity_id) ;
583 fetch csr_leave_type into l_leave_type;
584 close csr_leave_type;
585
586 if l_leave_type is null or l_leave_type = 'MA' then
587 ssp_SMP_pkg.SMP_control (each_maternity.maternity_id, l_deleting);
588 elsif l_leave_type ='AD' then
589 ssp_sap_pkg.sap_control (each_maternity.maternity_id, l_deleting);
590 elsif l_leave_type ='PA' then
591 ssp_pad_pkg.pad_control (each_maternity.maternity_id, l_deleting);
592 elsif l_leave_type ='PB' then
593 ssp_pab_pkg.pab_control (each_maternity.maternity_id, l_deleting);
594 end if;
595 --
596 -- RT entries prob
597 --
598 open csr_count_absences(each_maternity.maternity_id);
599 fetch csr_count_absences into l_count;
600 close csr_count_absences;
601
602 if l_deleting and l_count < 1
603 then
604 hr_utility.set_location('ssp_del_orphaned_rows:'||l_proc,50);
605 --
606 delete ssp_stoppages
607 where maternity_id = each_maternity.maternity_id;
608 end if;
609 end loop;
610 --
611 delete ssp_temp_affected_rows
612 where locked = to_char(userenv('sessionid'))
613 or locked is null
614 or locked not in (select to_char(AUDSID) from v$session);
615 end if;
616 --
617 hr_utility.set_location('Leaving :'||l_proc,100);
618 --
619 exception
620 --
621 when mutating_table or row_deleted then
622 --
623 -- If we get a mutating table restriction then we must be firing this code
624 -- recursively (eg the user deleted an absence which cascaded to delete the
625 -- stoppages for it; both the absence deletion and the stoppage deletion
626 -- causing this code to fire). If this occurs then we do not want the
627 -- second and subsequent calls to do anything so just exit silently.
628 --
629 null;
630 --
631 hr_utility.set_location (l_proc,999);
632 --
633 end recalculate_SSP_and_SMP;
634 ------------------------------------------------------------------------------
635 procedure get_entry_details(p_date_earned in date,
636 p_last_process_date in date,
637 p_person_id in number,
638 p_element_type_id in number,
639 p_element_link_id in out nocopy number,
640 p_assignment_id in out nocopy number,
641 p_effective_start_date out nocopy date,
642 p_effective_end_date out nocopy date,
643 p_pay_as_lump_sum in varchar2 default 'N') is
644 --
645 cannot_derive_payroll_period exception;
646 no_payroll exception;
647 --
648 l_found boolean := false;
649 --
650 l_temp_date date;
651 l_assignment_start date;
652 l_assignment_end date;
653 l_closed_period date;
654 l_effective_date date := null;
655 l_effective_start_date date := null;
656 l_effective_end_date date := null;
657 l_last_process_date date := nvl (p_last_process_date,hr_general.end_of_time);
658 --
659 l_payroll_id number := null;
660 --
661 --
662 l_proc varchar2(72) := g_package||'get_entry_details';
663 l_pay_as_lump_sum varchar2(1) := nvl (p_pay_as_lump_sum, 'N');
664 --
665 -- Get the user's effective date
666 cursor csr_effective_date is
667 select effective_date
668 from fnd_sessions
669 where session_id = userenv ('sessionid');
670 --
671 -- Get the details of the primary assignment as at the date
672 -- earned.
673 cursor csr_assignment(p_date date,
674 p_lsp date) is
675 select assignment_id,
676 payroll_id
677 from per_all_assignments_f
678 where person_id = p_person_id
679 and primary_flag = 'Y'
680 and assignment_type = 'E'
681 and least(p_date,p_lsp) between effective_start_date and effective_end_date;
682 --
683 -- Get Min(start), Max(end) of assignment on payroll x
684 cursor csr_assignment_duration(p_date date,
685 p_asg_id number,
686 p_pay_id number) is
687 select min(effective_start_date),
688 max(nvl(effective_end_date,hr_general.end_of_time))
689 from per_all_assignments_f
690 where assignment_id = p_asg_id
691 and primary_flag = 'Y'
692 and assignment_type = 'E'
693 and payroll_id = p_pay_id;
694 --
695 -- Get minimum closed period
696 cursor csr_minimum_closed(p_payroll_id number) is
697 select nvl(max(period.start_date),to_date('01/01/0001','DD/MM/YYYY'))
698 from per_time_periods period
699 where period.payroll_id = p_payroll_id
700 and period.prd_information_category = 'GB'
701 and period.prd_information1 = 'Closed';
702 --
703 -- Get period details
704 cursor csr_payroll_period is
705 select min(period.start_date),
706 min(nvl(period.end_date,hr_general.end_of_time))
707 from per_time_periods period
708 where period.payroll_id = l_payroll_id
709 and period.start_date > l_closed_period
710 --6959669 begin
711 -- and nvl(period.cut_off_date,period.end_date) <= l_last_process_date
712 and least(nvl(period.cut_off_date,period.end_date),period.end_date) <= l_last_process_date
713 --6959669 end
714 and (nvl(period.cut_off_date,period.end_date) >= l_effective_date
715 or ( l_effective_date > l_last_process_date
716 and l_last_process_date between period.start_date and period.end_date))
717 and ( l_pay_as_lump_sum = 'Y'
718 or( l_pay_as_lump_sum = 'N'
719 and ( p_date_earned <= period.end_date
720 or l_last_process_date < p_date_earned)));
721
722 --
723 procedure check_parameters is
724 all_parameters_valid constant boolean :=
725 (p_pay_as_lump_sum in ('Y','N') and
726 p_person_id is not null and
727 p_element_type_id is not null and
728 p_date_earned is not null and
729 p_date_earned = trunc (p_date_earned) and
730 p_last_process_date = trunc (p_last_process_date));
731 begin
732 hr_utility.trace (' p_date_earned = '||to_char (p_date_earned, 'DD-MON-YYYY'));
733 hr_utility.trace (' p_last_process_date = '||to_char (p_last_process_date));
734 hr_utility.trace (' p_person_id = ' ||to_char (p_person_id));
735 hr_utility.trace (' p_element_type_id = '||to_char (p_element_type_id));
736 hr_utility.trace (' p_element_link_id = '||to_char (p_element_link_id));
737 hr_utility.trace (' p_assignment_id = '||to_char (p_assignment_id));
738 hr_utility.trace (' p_pay_as_lump_sum = '||p_pay_as_lump_sum);
739 --
740 hr_general.assert_condition (all_parameters_valid);
741 --
742 end check_parameters;
743 --
744 begin
745 --
746 hr_utility.set_location ('Entering:'||l_proc,1);
747 --
748 check_parameters;
749 --
750 -- Get the effective date
751 open csr_effective_date;
752 fetch csr_effective_date into l_effective_date;
753 close csr_effective_date;
754 --
755 if l_effective_date is null then
756 l_effective_date := trunc (sysdate);
757 end if;
758 --
759 hr_utility.trace(l_proc||' effective date = '||to_char(l_effective_date, 'DD-MON-YYYY'));
760 --
761 -- 1. Find out which date to use when searching for payroll period
762 if l_pay_as_lump_sum = 'Y' then
763 l_temp_date := l_effective_date;
764 else
765 l_temp_date := greatest(p_date_earned,l_effective_date);
766 end if;
767
768 while not l_found loop
769 -- 2. Get assignemnt info as of l_temp_date
770 open csr_assignment(l_temp_date,l_last_process_date);
771 fetch csr_assignment into p_assignment_id, l_payroll_id;
772 close csr_assignment;
773
774 open csr_assignment_duration(l_temp_date,p_assignment_id,l_payroll_id);
775 fetch csr_assignment_duration into l_assignment_start,l_assignment_end;
776 close csr_assignment_duration;
777
778 open csr_minimum_closed(l_payroll_id);
779 fetch csr_minimum_closed into l_closed_period;
780 close csr_minimum_closed;
781
782 -- 3. Check payroll
783 if l_payroll_id is null then
784 raise no_payroll;
785 end if;
786
787 -- 4. Get period details
788 open csr_payroll_period;
789 fetch csr_payroll_period into l_effective_start_date,
790 l_effective_end_date;
791 close csr_payroll_period;
792
793 -- 5. Check that the period is valid for assignment
794 if l_assignment_start > l_effective_start_date then
795 l_effective_start_date := l_assignment_start;
796 -- bug fix 5346648 - If asg start date is more than the period end date
797 -- then we have to fetch the next period
798 if l_assignment_start >= l_effective_end_date then
799 l_effective_date := l_assignment_start;
800 open csr_payroll_period;
801 fetch csr_payroll_period into l_effective_start_date,
802 l_effective_end_date;
803 close csr_payroll_period;
804 end if;
805 end if;
806
807 -- 6. If cannot find any period then, error out
808 if (l_effective_start_date is null or l_effective_end_date is null) then
809 raise cannot_derive_payroll_period;
810 else
811 -- period is found but is it > assignment end date
812 if l_assignment_end < l_effective_start_date then
813 l_temp_date := l_effective_start_date;
814 else
815 l_found := true;
816 p_effective_start_date := l_effective_start_date;
817 p_effective_end_date := l_effective_end_date;
818 hr_utility.trace(l_proc||' l_effective_start_date = ' ||to_char(l_effective_start_date, 'DD-MON-YYYY'));
819 hr_utility.trace(l_proc||' l_effective_end_date = ' ||to_char(l_effective_end_date, 'DD-MON-YYYY'));
820 end if;
821 end if;
822 end loop;
823 --
824 -- 7. Fetching the link based on the effective start date of the entry.
825 p_element_link_id := hr_entry_api.get_link (p_assignment_id => p_assignment_id,
826 p_element_type_id => p_element_type_id,
827 p_session_date => p_effective_start_date);
828 --
829 hr_utility.set_location ('Leaving :'||l_proc,1);
830 --
831 exception
832 when cannot_derive_payroll_period then
833 fnd_message.set_name ('SSP', 'SSP_35029_NO_PAYROLL_PERIOD');
834 fnd_message.raise_error;
835 when no_payroll then
836 fnd_message.set_name ('SSP','SSP_35080_NO_PAYROLL');
837 fnd_message.set_token ('DATE_EARNED',p_date_earned);
838 fnd_message.raise_error;
839 end get_entry_details;
840 --------------------------------------------------------------------------------
841 function average_earnings_error return varchar2 is
842 --
843 -- Returns the withholding reason corresponding to the error which was raised
844 -- by the procedure to calculate average earnings in the event that the figure
845 -- could not be derived. This withholding reason may be used to create a
846 -- stoppage of SSP/SMP thus indicating why the entries were not created.
847 --
848 -- The message is defaulted only if ssp_smp_support_pkg returns a
849 -- null value. This ocurs when a commit is fired within the Absence
850 -- form, as the recalculate_ssp_and_smp is fired and does not create a
851 -- 'reason for no earnings', this is only created when
852 -- calculate_average_earnings is used.
853 --
854
855 l_message_name_on_stack varchar2 (80) := ssp_smp_support_pkg.reason_for_no_earnings;
856 l_withholding_reason varchar2 (80) := null;
857 --
858 begin
859 --
860 if l_message_name_on_stack = 'SSP_35024_NEED_PAYROLL_FOR_ERN' then
861 --
862 -- Message is:
863 -- "Average Earnings cannot be calculated automatically unless
864 -- you have installed Oracle Payroll. You must enter the figure
865 -- yourself."
866 --
867 l_withholding_reason := 'Payroll not installed';
868 --
869 elsif l_message_name_on_stack = 'SSP_35026_NO_NEW_EMP_EARNINGS' then
870 --
871 -- Message is:
872 -- "Oracle Payroll cannot derive average earnings for employees who
873 -- have not yet received any pay on which to base a calculation.
874 -- Please enter the average earnings figure yourself, based upon the
875 -- employee's contracted weekly earnings."
876 --
877 l_withholding_reason := 'Cannot derive new emps pay';
878 --
879 -- (abhaduri) added to inform about employees re-hired
880 -- within 8 weeks of their termination
881 elsif l_message_name_on_stack = 'SSP_36076_EMP_REHIRED' then
882 --
883 -- Message is:
884 -- "Average earnings cannot be calculated as the employee is new.
885 -- Please take into account the previous period payment while
886 -- calculating average earnings manually."
887 --
888 l_withholding_reason :='Re-hired employee,please check';
889 --
890 elsif l_message_name_on_stack = 'SSP_35025_NO_DIRECTOR_EARNINGS' then
891 --
892 -- Message is:
893 -- "Oracle Payroll is unable to calculate the earnings of directors
894 -- because it has no way to distinguish between voted fees and fees
895 -- drawn in anticipation of voting. Please enter average earnings
896 -- for directors yourself."
897 --
898 l_withholding_reason := 'Cannot derive directors pay';
899 --
900 --
901 elsif l_message_name_on_stack IS NULL then
902 --
903 l_withholding_reason := 'Cannot derive new emps pay';
904 --
905 end if;
906 --
907 -- Reset global variable to avoid later confusion
908 ssp_smp_support_pkg.reason_for_no_earnings := null;
909 --
910 return l_withholding_reason;
911 --
912 end average_earnings_error;
913
914 --------------------------------------------------------------------------------
915 --
916 procedure update_ssp_smp_entries (P_UPDATE_ERROR OUT NOCOPY boolean) is
917
918
919 cursor csr_affected_absences is
920 select nvl(paa.linked_absence_id,paa.absence_attendance_id) as absence_id,
921 paa.person_id,
922 nvl(paa.date_start,paa.sickness_start_date) as absence_start_date,
923 nvl(paa.date_end,paa.sickness_end_date) as absence_end_date
924 from per_absence_attendances paa,
925 per_absence_attendance_types paat
926 where paa.absence_attendance_type_id = paat.absence_attendance_type_id
927 and paat.absence_category = 'S'
928 and
929 /* SSP absences which span tax years or start in old tax year and are open-ended */
930 (
931 ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
932 and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
933 )
934 or
935 ((paa.sickness_start_date between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
936 and (paa.sickness_end_date > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.sickness_end_date is null)
937 )
938 or
939 /* SSP absences which start in the new tax year */
940 (
941 paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
942 or paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
943 )
944 )
945 /* Do not retrieve terminated employees whose actual termination dates have passed or are null */
946 and not exists
947 ( select 1
948 from per_all_people_f ppf,
949 per_person_types ppt,
950 per_periods_of_service pps
951 where ppf.person_id = pps.person_id
952 and ppt.person_type_id = ppf.person_type_id
953 and ppt.system_person_type = 'EX_EMP'
954 and nvl(pps.actual_termination_date,to_date('01/01/0001','DD/MM/YYYY')) <= sysdate
955 and ppf.person_id = paa.person_id
956 and pps.date_start = (select max(date_start)
957 from per_periods_of_service pos
958 where pos.person_id = pps.person_id)
959 and ppf.effective_start_date >= pps.date_start
960 and pps.date_start <= paa.sickness_start_date)
961 order by nvl(paa.linked_absence_id,paa.absence_attendance_id);
962
963
964 cursor csr_affected_leave(p_leave_type IN VARCHAR2) is
965 -- p_leave_type = 'MA' - Maternity, 'AD' - Adoption, 'PA' - Paternity Adoption, 'PB' - Paternity Birth
966 select paa.maternity_id,
967 paa.person_id,
968 paa.date_start,
969 paa.date_end,
970 paa.date_projected_start,
971 paa.date_projected_end
972 from per_absence_attendances paa,
973 ssp_maternities mat
974 where paa.maternity_id = mat.maternity_id
975 and nvl(mat.leave_type, 'MA') = p_leave_type
976 and
977 /* SMP absences which span tax years or start in old tax year and are open-ended */
978 (
979 ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
980 and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
981 )
982 or
983 ((paa.date_projected_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
984 and (paa.date_projected_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_projected_end is null)
985 and paa.date_start IS NULL -- use projected dates only when actual dates not available
986 )
987 or
988 /* SMP absences which start in the new tax year */
989 (
990 paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
991 or (paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
992 and paa.date_start IS NULL) -- use projected dates only when actual dates not available
993 )
994 )
995 /* Do not retrieve employees whose final process dates have passed */
996 and not exists
997 ( select 1
998 from per_all_people_f ppf,
999 per_person_types ppt,
1000 per_periods_of_service pps
1001 where ppf.person_id = pps.person_id
1002 and ppt.person_type_id = ppf.person_type_id
1003 and ppt.system_person_type = 'EX_EMP'
1004 and pps.final_process_date <= sysdate
1005 and ppf.person_id = paa.person_id
1006 and pps.date_start = (select max(date_start)
1007 from per_periods_of_service pos
1008 where pos.person_id = pps.person_id)
1009 and ppf.effective_start_date >= pps.date_start
1010 and pps.date_start <= paa.date_start)
1011 /* Do not retrieve employees who are deceased */
1012 and not exists
1013 ( select 1
1014 from per_all_people_f ppf,
1015 per_periods_of_service pps
1016 where ppf.person_id = pps.person_id
1017 and pps.leaving_reason = 'D'
1018 and ppf.person_id = paa.person_id)
1019 order by paa.maternity_id;
1020
1021
1022 l_count NUMBER := 0;
1023 l_mat_count NUMBER := 0;
1024 l_adop_count NUMBER := 0;
1025 l_pat_adop_count NUMBER := 0;
1026 l_pat_birth_count NUMBER := 0;
1027
1028 e_ssp_rate_not_set exception;
1029 e_smp_rate_not_set exception;
1030 e_sap_rate_not_set exception;
1031 e_sppa_rate_not_set exception;
1032 e_sppb_rate_not_set exception;
1033
1034 e_no_new_ssp_entry exception;
1035 e_no_new_smp_entry exception;
1036 e_no_new_sap_entry exception;
1037 e_no_new_sppa_entry exception;
1038 e_no_new_sppb_entry exception;
1039
1040 l_update_error boolean := FALSE;
1041
1042 /* Function to check if SSP entries in new tax year have already been updated */
1043 /* with new SSP rates and recalculated - returns TRUE if so, FALSE if not */
1044 function ssp_entries_already_updated return boolean is
1045
1046 cursor csr_new_ssp_rate is
1047 select piv.default_value
1048 from pay_input_values_f piv,
1049 pay_element_types_f petf
1050 where petf.element_type_id = piv.element_type_id
1051 and piv.name = 'Rate'
1052 and petf.element_name = 'Statutory Sick Pay'
1053 and piv.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate);
1054
1055
1056 cursor csr_first_new_ssp_entry is
1057 select peev1.screen_entry_value
1058 from pay_element_entry_values_f peev1,
1059 pay_element_entry_values_f peev2,
1060 pay_input_values_f piv,
1061 pay_element_entries_f peef,
1062 pay_element_links_f pelf,
1063 pay_element_types_f petf
1064 where piv.input_value_id = peev1.input_value_id
1065 and peev1.element_entry_id = peev2.element_entry_id
1066 and peev1.element_entry_id = peef.element_entry_id
1067 and peef.element_link_id = pelf.element_link_id
1068 and pelf.element_type_id = petf.element_type_id
1069 and piv.name = 'Rate'
1070 and petf.element_name = 'Statutory Sick Pay'
1071 and piv.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate)
1072 and peev2.element_entry_value_id =
1073 (select peev3.element_entry_value_id
1074 from pay_element_entry_values_f peev3
1075 where input_value_id =
1076 (select distinct input_value_id
1077 from pay_input_values_f piv,
1078 pay_element_types_f petf
1079 where petf.element_type_id = piv.element_type_id
1080 and petf.element_name = 'Statutory Sick Pay'
1081 and piv.name = 'From')
1082 and peev3.screen_entry_value >
1083 fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate))
1084 /* Retrieve only those entries that will be retrieved by main SSP query */
1085 and peev3.element_entry_id in
1086 ( select peef1.element_entry_id
1087 from pay_element_entries_f peef1,
1088 per_all_assignments_f paf,
1089 per_all_people_f ppf,
1090 per_person_types ppt,
1091 per_periods_of_service pps
1092 where peef1.assignment_id = paf.assignment_id
1093 and paf.person_id = ppf.person_id
1094 and ppf.person_id = pps.person_id
1095 and ppt.person_type_id = ppf.person_type_id
1096 and peev3.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1097 and fnd_date.date_to_canonical(ppf.effective_end_date)
1098 and peev3.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1099 and fnd_date.date_to_canonical(paf.effective_end_date)
1100 and ppt.system_person_type = 'EMP'
1101 and nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1102 )
1103 and rownum = 1);
1104
1105
1106 l_new_SSP_rate number;
1107 l_first_SSP_entry_rate number;
1108
1109
1110
1111 begin
1112
1113 hr_utility.trace('Entering ssp_entries_already_updated function');
1114
1115 /* Find SSP rate for new tax year */
1116 open csr_new_ssp_rate;
1117 fetch csr_new_ssp_rate into l_new_SSP_rate;
1118
1119 /* If unable to find new SSP rate, then rate probably not set yet */
1120 /* Make note of this and exit quietly */
1121 if csr_new_ssp_rate%notfound
1122 then
1123 close csr_new_ssp_rate;
1124 raise e_ssp_rate_not_set;
1125 end if;
1126
1127 close csr_new_ssp_rate;
1128
1129 hr_utility.trace('New SSP rate: '||l_new_SSP_rate);
1130
1131 /* Find first element entry value holding SSP rate for new tax year */
1132 open csr_first_new_ssp_entry;
1133 fetch csr_first_new_ssp_entry into l_first_SSP_entry_rate;
1134
1135 /* If unable to find SSP entry in new tax year, then warn user */
1136 /* Possible causes are employee terminations or stoppages */
1137 if csr_first_new_ssp_entry%notfound
1138 then
1139 close csr_first_new_ssp_entry;
1140 raise e_no_new_ssp_entry;
1141 end if;
1142
1143 close csr_first_new_ssp_entry;
1144
1145 hr_utility.trace('First SSP entry rate: '||l_first_SSP_entry_rate);
1146
1147 if l_new_SSP_rate = l_first_SSP_entry_rate
1148 then
1149
1150 return true;
1151
1152 else
1153
1154 return false;
1155
1156 end if;
1157
1158 exception
1159
1160 when others
1161 then raise;
1162
1163
1164 end ssp_entries_already_updated;
1165
1166 /* Function to check if SMP entries in new tax year have already updated */
1167 /* with new SMP rates and recalculated - returns TRUE if so, FALSE if not */
1168 function smp_rate_changed return boolean is
1169
1170 /* Check for SMP rates beginning on or after April 1st */
1171 cursor csr_new_smp_rate is
1172 select petf.element_information10, petf.element_information9, petf.element_information16
1173 from pay_element_types_f petf
1174 where petf.element_name = 'Statutory Maternity Pay'
1175 and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1176 order by effective_start_date;
1177
1178
1179 /* 09/12/2003 rmakhija - commented out following cursor because element entry value
1180 for 'Amount' is a calculated value and it is not same
1181 as smp rate therefore it can not be compared with new smp
1182 rate to detect the change in smp rate. A new cursor
1183 csr_old_smp_rate has been added to find old smp rate
1184 and to compare it with new smp rate to detect the change.
1185
1186 cursor csr_first_new_smp_entry is
1187 select peev1.screen_entry_value
1188 from pay_element_entry_values_f peev1,
1189 pay_element_entry_values_f peev2,
1190 pay_element_entry_values_f peev3,
1191 pay_input_values_f piv,
1192 pay_element_entries_f peef,
1193 pay_element_links_f pelf,
1194 pay_element_types_f petf
1195 where piv.input_value_id = peev1.input_value_id
1196 and peev1.element_entry_id = peev2.element_entry_id
1197 and peev1.element_entry_id = peev3.element_entry_id
1198 and peev1.element_entry_id = peef.element_entry_id
1199 and peef.element_link_id = pelf.element_link_id
1200 and pelf.element_type_id = petf.element_type_id
1201 and piv.name = 'Amount'
1202 and petf.element_name = 'Statutory Maternity Pay'
1203 and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
1204 -- Time restriction - only rows after tax year end
1205 and peev2.input_value_id =
1206 (select input_value_id
1207 from pay_input_values_f piv
1208 where petf.element_type_id = piv.element_type_id
1209 and upper(piv.name) = upper('Week commencing')
1210 )
1211 and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
1212 -- Retrieve only those entries that main SMP query will retrieve
1213 and exists
1214 ( select 1
1215 from per_all_assignments_f paf,
1216 per_all_people_f ppf,
1217 per_person_types ppt,
1218 per_periods_of_service pps
1219 where peef.assignment_id = paf.assignment_id
1220 and paf.person_id = ppf.person_id
1221 and ppf.person_id = pps.person_id
1222 and ppt.person_type_id = ppf.person_type_id
1223 and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1224 and fnd_date.date_to_canonical(ppf.effective_end_date)
1225 and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1226 and fnd_date.date_to_canonical(paf.effective_end_date)
1227 and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1228 )
1229 and peev3.input_value_id =
1230 (select input_value_id
1231 from pay_input_values_f piv
1232 where petf.element_type_id = piv.element_type_id
1233 and upper(piv.name) = upper('Rate'))
1234 -- Rate restriction - only retrieve entries on LOW rate for SMP
1235 and upper(peev3.screen_entry_value) = upper('Low')
1236 -- Get first row that matches all of the above criteria
1237 and rownum = 1;
1238 */
1239
1240 cursor csr_old_smp_rate is
1241 select petf.element_information10, petf.element_information9, petf.element_information16
1242 from pay_element_types_f petf
1243 where petf.element_name = 'Statutory Maternity Pay'
1244 and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1245 order by effective_start_date desc;
1246
1247 l_new_SMP_rate number;
1248 l_old_SMP_rate number;
1249 l_new_high_smp_rate number;
1250 l_old_high_smp_rate number;
1251 l_new_std_smp_rate number;
1252 l_old_std_smp_rate number;
1253
1254 begin
1255
1256 hr_utility.trace('Entering smp_rate_changed function');
1257 --
1258 /* Find SMP rate for new tax year */
1259 open csr_new_smp_rate;
1260 fetch csr_new_smp_rate into l_new_SMP_rate, l_new_high_smp_rate, l_new_std_smp_rate;
1261 --
1262 /* If unable to find new SMP rate, then rate probably not set yet */
1263 /* Make note of this and exit quietly */
1264 if csr_new_SMP_rate%notfound
1265 then
1266 close csr_new_SMP_rate;
1267 raise e_SMP_rate_not_set;
1268 end if;
1269 --
1270 close csr_new_smp_rate;
1271 --
1272 hr_utility.trace('New Lower SMP rate: '||l_new_SMP_rate);
1273 hr_utility.trace('New Higher SMP rate: '||l_new_high_SMP_rate);
1274 hr_utility.trace('New Standard SMP rate: '||l_new_std_SMP_rate);
1275 --
1276 /* Find SMP rate for current tax year */
1277 open csr_old_smp_rate;
1278 fetch csr_old_smp_rate into l_old_SMP_rate, l_old_high_smp_rate, l_old_std_smp_rate;
1279 close csr_old_smp_rate;
1280 --
1281 hr_utility.trace('Old SMP rate: '||l_old_SMP_rate);
1282 hr_utility.trace('Old Higher SMP rate: '||l_old_high_SMP_rate);
1283 hr_utility.trace('Old Standard SMP rate: '||l_old_std_SMP_rate);
1284 --
1285 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)
1286 then
1287 return false;
1288 else
1289 return true;
1290 end if;
1291 --
1292 exception
1293 when others then
1294 raise;
1295 end smp_rate_changed;
1296
1297
1298 /* Function to check if SAP entries in new tax year have already updated */
1299 /* with new SAP rates and recalculated - returns TRUE if so, FALSE if not */
1300 function sap_rate_changed return boolean is
1301
1302 /* Check for SAP rates beginning on or after April 1st */
1303 cursor csr_new_sap_rate is
1304 select petf.element_information5, petf.element_information7
1305 from pay_element_types_f petf
1306 where petf.element_name = 'Statutory Adoption Pay'
1307 and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1308 order by effective_start_date;
1309 --
1310 /* 09/12/2003 rmakhija - commented out following cursor because element entry value
1311 for 'Amount' is a calculated value and it is not same
1312 as sap rate therefore it can not be compared with new sap
1313 rate to detect the change in sap rate. A new cursor
1314 csr_old_sap_rate has been added to find old sap rate
1315 and to compare it with new sap rate to detect the change.
1316
1317 cursor csr_first_new_sap_entry is
1318 select peev1.screen_entry_value
1319 from pay_element_entry_values_f peev1,
1320 pay_element_entry_values_f peev2,
1321 pay_input_values_f piv,
1322 pay_element_entries_f peef,
1323 pay_element_links_f pelf,
1324 pay_element_types_f petf
1325 where piv.input_value_id = peev1.input_value_id
1326 and peev1.element_entry_id = peev2.element_entry_id
1327 and peev1.element_entry_id = peef.element_entry_id
1328 and peef.element_link_id = pelf.element_link_id
1329 and pelf.element_type_id = petf.element_type_id
1330 and piv.name = 'Amount'
1331 and petf.element_name = 'Statutory Adoption Pay'
1332 and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
1333 -- Time restriction - only rows after tax year end
1334 and peev2.input_value_id =
1335 (select input_value_id
1336 from pay_input_values_f piv
1337 where petf.element_type_id = piv.element_type_id
1338 and upper(piv.name) = upper('Week commencing')
1339 )
1340 and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
1341 -- Retrieve only those entries that main sap query will retrieve
1342 and exists
1343 ( select 1
1344 from per_all_assignments_f paf,
1345 per_all_people_f ppf,
1346 per_person_types ppt,
1347 per_periods_of_service pps
1348 where peef.assignment_id = paf.assignment_id
1349 and paf.person_id = ppf.person_id
1350 and ppf.person_id = pps.person_id
1351 and ppt.person_type_id = ppf.person_type_id
1352 and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1353 and fnd_date.date_to_canonical(ppf.effective_end_date)
1354 and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1355 and fnd_date.date_to_canonical(paf.effective_end_date)
1356 and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1357 )
1358 -- Get first row that matches all of the above criteria
1359 and rownum = 1;
1360 */
1361 --
1362 cursor csr_old_sap_rate is
1363 select petf.element_information5, petf.element_information7
1364 from pay_element_types_f petf
1365 where petf.element_name = 'Statutory Adoption Pay'
1366 and petf.effective_start_date < hr_gbnicar.uk_tax_yr_end(sysdate) - 4
1367 order by effective_start_date desc;
1368 --
1369 --
1370 l_new_sap_rate number;
1371 l_old_sap_rate number;
1372 --
1373 l_new_std_sap_rate number;
1374 l_old_std_sap_rate number;
1375 --
1376 begin
1377 --
1378 hr_utility.trace('Entering sap_rate_changed function');
1379 --
1380 /* Find sap rate for new tax year */
1381 open csr_new_sap_rate;
1382 fetch csr_new_sap_rate into l_new_sap_rate, l_new_std_sap_rate;
1383 --
1384 /* If unable to find new sap rate, then rate probably not set yet */
1385 /* Make note of this and exit quietly */
1386 if csr_new_sap_rate%notfound then
1387 close csr_new_sap_rate;
1388 raise e_sap_rate_not_set;
1389 end if;
1390 --
1391 close csr_new_sap_rate;
1392 --
1393 hr_utility.trace('New sap rate: '||l_new_sap_rate);
1394 hr_utility.trace('New std sap rate: '||l_new_std_sap_rate);
1395 --
1396 /* Find sap rate for current tax year */
1397 open csr_old_sap_rate;
1398 fetch csr_old_sap_rate into l_old_sap_rate, l_old_std_sap_rate;
1399 close csr_old_sap_rate;
1400 --
1401 hr_utility.trace('Old sap rate: '||l_old_sap_rate);
1402 hr_utility.trace('Old std sap rate: '||l_old_std_sap_rate);
1403 --
1404 if (l_new_sap_rate = l_old_sap_rate) and (l_new_std_sap_rate = l_old_std_sap_rate) then
1405 return false;
1406 else
1407 return true;
1408 end if;
1409 --
1410 exception
1411 when others then raise;
1412 end sap_rate_changed;
1413
1414 /* Function to check if SPPA entries in new tax year have already updated */
1415 /* with new SPPA rates and recalculated - returns TRUE if so, FALSE if not */
1416 function sppa_rate_changed return boolean is
1417
1418 /* Check for SPPA rates beginning on or after April 1st */
1419 cursor csr_new_sppa_rate is
1420 select petf.element_information6, petf.element_information8
1421 from pay_element_types_f petf
1422 where petf.element_name = 'Statutory Paternity Pay Adoption'
1423 and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1424 order by effective_start_date;
1425 --
1426 /* 09/12/2003 rmakhija - commented out following cursor because element entry value
1427 for 'Amount' is a calculated value and it is not same
1428 as SPP Adoption rate therefore it can not be compared with new
1429 SPP Adoption rate to detect the change. A new cursor
1430 csr_old_sppa_rate has been added to find old SPP Adoption rate
1431 and to compare it with new SPP Adoption rate to detect the change.
1432
1433 cursor csr_first_new_sppa_entry is
1434 select peev1.screen_entry_value
1435 from pay_element_entry_values_f peev1,
1436 pay_element_entry_values_f peev2,
1437 pay_input_values_f piv,
1438 pay_element_entries_f peef,
1439 pay_element_links_f pelf,
1440 pay_element_types_f petf
1441 where piv.input_value_id = peev1.input_value_id
1442 and peev1.element_entry_id = peev2.element_entry_id
1443 and peev1.element_entry_id = peef.element_entry_id
1444 and peef.element_link_id = pelf.element_link_id
1445 and pelf.element_type_id = petf.element_type_id
1446 and piv.name = 'Amount'
1447 and petf.element_name = 'Statutory Paternity Pay Adoption'
1448 and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
1449 -- Time restriction - only rows after tax year end
1450 and peev2.input_value_id =
1451 (select input_value_id
1452 from pay_input_values_f piv
1453 where petf.element_type_id = piv.element_type_id
1454 and upper(piv.name) = upper('Week commencing')
1455 )
1456 and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
1457 -- Retrieve only those entries that main sppa query will retrieve
1458 and exists
1459 ( select 1
1460 from per_all_assignments_f paf,
1461 per_all_people_f ppf,
1462 per_person_types ppt,
1463 per_periods_of_service pps
1464 where peef.assignment_id = paf.assignment_id
1465 and paf.person_id = ppf.person_id
1466 and ppf.person_id = pps.person_id
1467 and ppt.person_type_id = ppf.person_type_id
1468 and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1469 and fnd_date.date_to_canonical(ppf.effective_end_date)
1470 and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1471 and fnd_date.date_to_canonical(paf.effective_end_date)
1472 and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1473 )
1474 -- Get first row that matches all of the above criteria
1475 and rownum = 1;
1476 */
1477 --
1478 cursor csr_old_sppa_rate is
1479 select petf.element_information6, petf.element_information8
1480 from pay_element_types_f petf
1481 where petf.element_name = 'Statutory Paternity Pay Adoption'
1482 and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1483 order by effective_start_date desc;
1484
1485 --
1486 l_new_sppa_rate number;
1487 l_old_sppa_rate number;
1488 --
1489 l_new_std_sppa_rate number;
1490 l_old_std_sppa_rate number;
1491 --
1492 begin
1493 --
1494 hr_utility.trace('Entering sppa_rate_changed function');
1495 --
1496 /* Find sppa rate for new tax year */
1497 open csr_new_sppa_rate;
1498 fetch csr_new_sppa_rate into l_new_sppa_rate, l_new_std_sppa_rate;
1499 --
1500 /* If unable to find new sppa rate, then rate probably not set yet */
1501 /* Make note of this and exit quietly */
1502 if csr_new_sppa_rate%notfound then
1503 close csr_new_sppa_rate;
1504 raise e_sppa_rate_not_set;
1505 end if;
1506 --
1507 close csr_new_sppa_rate;
1508 --
1509 hr_utility.trace('New sppa rate: '||l_new_sppa_rate);
1510 hr_utility.trace('New std sppa rate: '||l_new_std_sppa_rate);
1511 --
1512 open csr_old_sppa_rate;
1513 fetch csr_old_sppa_rate into l_old_sppa_rate, l_old_std_sppa_rate;
1514 close csr_old_sppa_rate;
1515 --
1516 hr_utility.trace('Old SPPA rate: '||l_old_sppa_rate);
1517 hr_utility.trace('Old Std SPPA rate: '||l_old_std_sppa_rate);
1518 --
1519 if (l_new_sppa_rate = l_old_sppa_rate) and (l_new_std_sppa_rate = l_old_std_sppa_rate) then
1520 return false;
1521 else
1522 return true;
1523 end if;
1524 --
1525 exception
1526 when others then raise;
1527 end sppa_rate_changed;
1528
1529 /* Function to check if SPPB entries in new tax year have already updated */
1530 /* with new SPPB rates and recalculated - returns TRUE if so, FALSE if not */
1531 function sppb_rate_changed return boolean is
1532
1533 /* Check for SPPB rates beginning on or after April 1st */
1534 cursor csr_new_sppb_rate is
1535 select petf.element_information6, petf.element_information9
1536 from pay_element_types_f petf
1537 where petf.element_name = 'Statutory Paternity Pay Birth'
1538 and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1539 order by effective_start_date;
1540 --
1541 /* 09/12/2003 rmakhija - commented out following cursor because element entry value
1542 for 'Amount' is a calculated value and it is not same
1543 as SPP Birth rate therefore it can not be compared with new
1544 SPP Birth rate to detect the change. A new cursor
1545 csr_old_sppb_rate has been added to find old SPP Birth rate
1546 and to compare it with new SPP Birth rate to detect the change.
1547
1548 cursor csr_first_new_sppb_entry is
1549 select peev1.screen_entry_value
1550 from pay_element_entry_values_f peev1,
1551 pay_element_entry_values_f peev2,
1552 pay_input_values_f piv,
1553 pay_element_entries_f peef,
1554 pay_element_links_f pelf,
1555 pay_element_types_f petf
1556 where piv.input_value_id = peev1.input_value_id
1557 and peev1.element_entry_id = peev2.element_entry_id
1558 and peev1.element_entry_id = peef.element_entry_id
1559 and peef.element_link_id = pelf.element_link_id
1560 and pelf.element_type_id = petf.element_type_id
1561 and piv.name = 'Amount'
1562 and petf.element_name = 'Statutory Paternity Pay Birth'
1563 and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
1564 -- Time restriction - only rows after tax year end
1565 and peev2.input_value_id =
1566 (select input_value_id
1567 from pay_input_values_f piv
1568 where petf.element_type_id = piv.element_type_id
1569 and upper(piv.name) = upper('Week commencing')
1570 )
1571 and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
1572 -- Retrieve only those entries that main SPPB query will retrieve
1573 and exists
1574 ( select 1
1575 from per_all_assignments_f paf,
1576 per_all_people_f ppf,
1577 per_person_types ppt,
1578 per_periods_of_service pps
1579 where peef.assignment_id = paf.assignment_id
1580 and paf.person_id = ppf.person_id
1581 and ppf.person_id = pps.person_id
1582 and ppt.person_type_id = ppf.person_type_id
1583 and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1584 and fnd_date.date_to_canonical(ppf.effective_end_date)
1585 and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1586 and fnd_date.date_to_canonical(paf.effective_end_date)
1587 and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1588 )
1589 -- Get first row that matches all of the above criteria
1590 and rownum = 1;
1591 */
1592 --
1593 cursor csr_old_sppb_rate is
1594 select petf.element_information6, petf.element_information9
1595 from pay_element_types_f petf
1596 where petf.element_name = 'Statutory Paternity Pay Birth'
1597 and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1598 order by effective_start_date desc;
1599 --
1600 l_new_sppb_rate number;
1601 l_old_sppb_rate number;
1602 --
1603 l_new_std_sppb_rate number;
1604 l_old_std_sppb_rate number;
1605 --
1606 begin
1607 --
1608 hr_utility.trace('Entering sppb_rate_changed function');
1609 --
1610 /* Find SPPB rate for new tax year */
1611 open csr_new_sppb_rate;
1612 fetch csr_new_sppb_rate into l_new_sppb_rate, l_new_std_sppb_rate;
1613 --
1614 /* If unable to find new SPPB rate, then rate probably not set yet */
1615 /* Make note of this and exit quietly */
1616 if csr_new_sppb_rate%notfound then
1617 close csr_new_sppb_rate;
1618 raise e_sppb_rate_not_set;
1619 end if;
1620 --
1621 close csr_new_sppb_rate;
1622 --
1623 hr_utility.trace('New SPPB rate: '||l_new_sppb_rate);
1624 hr_utility.trace('New Std SPPB rate: '||l_new_Std_sppb_rate);
1625 --
1626 open csr_old_sppb_rate;
1627 fetch csr_old_sppb_rate into l_old_sppb_rate, l_old_std_sppb_rate;
1628 close csr_old_sppb_rate;
1629 --
1630 hr_utility.trace('old SPPB rate: '||l_old_sppb_rate);
1631 hr_utility.trace('old Std SPPB rate: '||l_old_Std_sppb_rate);
1632 --
1633 if (l_new_sppb_rate = l_old_sppb_rate) and (l_new_std_sppb_rate = l_old_std_sppb_rate) then
1634 return false;
1635 else
1636 return true;
1637 end if;
1638 --
1639 exception
1640 when others then raise;
1641 end sppb_rate_changed;
1642
1643 /* Main program body */
1644 begin
1645
1646 hr_utility.trace('Entering: '||g_package||'.update_ssp_smp_entries');
1647
1648 savepoint pre_update_status;
1649 /* Check first whether SSP element entries already updated for new tax year */
1650 /* SSP update block */
1651 begin
1652
1653 savepoint pre_ssp_update_status;
1654
1655 if not ssp_entries_already_updated
1656 then
1657
1658 hr_utility.trace('SSP element entries not updated, updating ....');
1659
1660 for r_affected_absences in csr_affected_absences loop
1661
1662 /* SSP control call block */
1663 begin
1664
1665 hr_utility.trace('Processing SSP absence: '||r_affected_absences.absence_id);
1666
1667 ssp_ssp_pkg.ssp_control(r_affected_absences.absence_id);
1668
1669 l_count := l_count + 1;
1670
1671 exception
1672
1673 when others then
1674 hr_utility.trace('Error occurred while processing SSP absence: '||r_affected_absences.absence_id);
1675 hr_utility.trace('SQL error code: '||SQLCODE);
1676 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1677 hr_utility.trace('Person id: '||r_affected_absences.person_id);
1678 hr_utility.trace('Absence start date: '||r_affected_absences.absence_start_date);
1679 hr_utility.trace('Absence end date: '||r_affected_absences.absence_end_date);
1680 l_update_error := true;
1681
1682 end;
1683
1684
1685
1686 end loop;
1687
1688 hr_utility.trace('Updated entries for '||l_count||' absences');
1689
1690 else
1691
1692 hr_utility.trace('SSP element entries already updated for new tax year');
1693
1694 end if;
1695
1696 commit;
1697
1698 exception
1699
1700 when e_ssp_rate_not_set
1701 then
1702 hr_utility.trace('Warning: SSP rate for new tax year not set');
1703 hr_utility.trace('Unable to proceed with updating SSP entries');
1704
1705
1706 when e_no_new_ssp_entry
1707 then
1708 hr_utility.trace('Warning: Unable to locate SSP entry in new tax year');
1709 hr_utility.trace('Entries for SSP absences in new tax year may be non-existent');
1710 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end dates of absences');
1711
1712 when others
1713 then
1714 hr_utility.trace('Unexpected error occurred inside SSP update block');
1715 hr_utility.trace('SQL error number: '||SQLCODE);
1716 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1717 rollback to pre_ssp_update_status;
1718 l_update_error := true;
1719
1720 end; /* SSP update block */
1721
1722 /* Now check SMP entries to see if they have been updated */
1723 /* SMP update block */
1724 -- 05/12/2003 rmakhija: Uncommented following section for TYE 2003/4
1725 begin
1726 --
1727 savepoint pre_smp_update_status;
1728 --
1729 if smp_rate_changed then
1730 --
1731 hr_utility.trace('SMP element entries updating ....');
1732 for r_affected_maternities in csr_affected_leave('MA') loop
1733 /* SMP Control call block */
1734 begin
1735 hr_utility.trace('Processing SMP absence: '||r_affected_maternities.maternity_id);
1736 ssp_smp_pkg.smp_control(p_maternity_id => r_affected_maternities.maternity_id,
1737 p_deleting => FALSE);
1738 l_mat_count := l_mat_count + 1;
1739 exception
1740 when others then
1741 hr_utility.trace('Error occurred while processing SMP absence: '||r_affected_maternities.maternity_id);
1742 hr_utility.trace('SQL error code: '||SQLCODE);
1743 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1744 hr_utility.trace('Person id: '||r_affected_maternities.person_id);
1745 hr_utility.trace('Maternity start date: '||r_affected_maternities.date_start);
1746 hr_utility.trace('Maternity end date: '||r_affected_maternities.date_end);
1747 hr_utility.trace('Maternity projected start date: '||r_affected_maternities.date_projected_start);
1748 hr_utility.trace('Maternity projected end date: '||r_affected_maternities.date_projected_end);
1749 l_update_error := true;
1750 end;
1751 end loop;
1752 hr_utility.trace('Updated entries for '||l_mat_count||' maternities');
1753 else
1754 hr_utility.trace('SMP element entries already updated for new tax year');
1755 end if;
1756 --
1757 commit;
1758 --
1759 exception
1760 when e_smp_rate_not_set then
1761 hr_utility.trace('Warning: SMP rate for new tax year not set');
1762 hr_utility.trace('Unable to proceed with updating SMP entries');
1763 when e_no_new_smp_entry then
1764 hr_utility.trace('Warning: Unable to locate SMP entry in new tax year');
1765 hr_utility.trace('Entries for SMP absences in new tax year may be non-existent');
1766 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1767 when others then
1768 hr_utility.trace('Unexpected error occurred inside SMP update block');
1769 hr_utility.trace('SQL error number: '||SQLCODE);
1770 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1771 rollback to pre_smp_update_status;
1772 l_update_error := true;
1773 end; /* SMP update block */
1774 -- 05/12/2003 rmakhija: Uncommented section ends here
1775 -- Following code has been added to auto update SAP/SPP Adoption/SPP Birth element entries
1776 -- Begin SAP Update Block
1777 BEGIN
1778 --
1779 savepoint pre_sap_update_status;
1780 --
1781 if sap_rate_changed then
1782 --
1783 hr_utility.trace('SAP element entries updating ....');
1784 for r_affected_adoption in csr_affected_leave('AD') loop
1785 /* SAP Control call block */
1786 begin
1787 hr_utility.trace('Processing SAP absence: '||r_affected_adoption.maternity_id);
1788 ssp_sap_pkg.sap_control(p_maternity_id => r_affected_adoption.maternity_id,
1789 p_deleting => FALSE);
1790 l_adop_count := l_adop_count + 1;
1791 exception
1792 when others then
1793 hr_utility.trace('Error occurred while processing SAP absence: '||r_affected_adoption.maternity_id);
1794 hr_utility.trace('SQL error code: '||SQLCODE);
1795 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1796 hr_utility.trace('Person id: '||r_affected_adoption.person_id);
1797 hr_utility.trace('Adoption start date: '||r_affected_adoption.date_start);
1798 hr_utility.trace('Adoption end date: '||r_affected_adoption.date_end);
1799 hr_utility.trace('Adoption projected start date: '||r_affected_adoption.date_projected_start);
1800 hr_utility.trace('Adoption projected end date: '||r_affected_adoption.date_projected_end);
1801 l_update_error := true;
1802 end;
1803 end loop;
1804 hr_utility.trace('Updated entries for '||l_adop_count||' adoptions');
1805 else
1806 hr_utility.trace('SAP element entries already updated for new tax year');
1807 end if;
1808 --
1809 commit;
1810 --
1811 exception
1812 when e_sap_rate_not_set then
1813 hr_utility.trace('Warning: SAP rate for new tax year not set');
1814 hr_utility.trace('Unable to proceed with updating SAP entries');
1815 when e_no_new_sap_entry then
1816 hr_utility.trace('Warning: Unable to locate SAP entry in new tax year');
1817 hr_utility.trace('Entries for SAP absences in new tax year may be non-existent');
1818 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1819 when others then
1820 hr_utility.trace('Unexpected error occurred inside SAP update block');
1821 hr_utility.trace('SQL error number: '||SQLCODE);
1822 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1823 rollback to pre_sap_update_status;
1824 l_update_error := true;
1825 end; /* SAP update block */
1826 -- Begin SPP Adoption Update Block
1827 BEGIN
1828 --
1829 savepoint pre_sppa_update_status;
1830 --
1831 if sppa_rate_changed then
1832 --
1833 hr_utility.trace('SPP Adoption element entries updating ....');
1834 for r_affected_pat_adop in csr_affected_leave('PA') loop
1835 /* SPP Adoption Control call block */
1836 begin
1837 hr_utility.trace('Processing SPP Adoption absence: '||r_affected_pat_adop.maternity_id);
1838 ssp_pad_pkg.pad_control(p_maternity_id => r_affected_pat_adop.maternity_id,
1839 p_deleting => FALSE);
1840 l_pat_adop_count := l_pat_adop_count + 1;
1841 exception
1842 when others then
1843 hr_utility.trace('Error occurred while processing SPP Adoption absence: '||r_affected_pat_adop.maternity_id);
1844 hr_utility.trace('SQL error code: '||SQLCODE);
1845 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1846 hr_utility.trace('Person id: '||r_affected_pat_adop.person_id);
1847 hr_utility.trace('Paternity Adoption start date: '||r_affected_pat_adop.date_start);
1848 hr_utility.trace('Paternity Adoption end date: '||r_affected_pat_adop.date_end);
1849 hr_utility.trace('Paternity Adoption projected start date: '||r_affected_pat_adop.date_projected_start);
1850 hr_utility.trace('Paternity Adoption projected end date: '||r_affected_pat_adop.date_projected_end);
1851 l_update_error := true;
1852 end;
1853 end loop;
1854 hr_utility.trace('Updated entries for '||l_pat_adop_count||' paternity adoptions');
1855 else
1856 hr_utility.trace('SPP Adoption element entries already updated for new tax year');
1857 end if;
1858 --
1859 commit;
1860 --
1861 exception
1862 when e_sppa_rate_not_set then
1863 hr_utility.trace('Warning: SPP ADoption rate for new tax year not set');
1864 hr_utility.trace('Unable to proceed with updating SPP ADoption entries');
1865 when e_no_new_sppa_entry then
1866 hr_utility.trace('Warning: Unable to locate SPP ADoption entry in new tax year');
1867 hr_utility.trace('Entries for SPP ADoption absences in new tax year may be non-existent');
1868 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1869 when others then
1870 hr_utility.trace('Unexpected error occurred inside SPP ADoption update block');
1871 hr_utility.trace('SQL error number: '||SQLCODE);
1872 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1873 rollback to pre_sppa_update_status;
1874 l_update_error := true;
1875 end; /* SPP Adoption update block */
1876 -- Begin SPP Birth Update Block
1877 BEGIN
1878 --
1879 savepoint pre_sppb_update_status;
1880 --
1881 if sppb_rate_changed then
1882 --
1883 hr_utility.trace('SPP Birth element entries updating ....');
1884 for r_affected_pat_Birth in csr_affected_leave('PB') loop
1885 /* SPP Birth Control call block */
1886 begin
1887 hr_utility.trace('Processing SPP Birth absence: '||r_affected_pat_Birth.maternity_id);
1888 ssp_pab_pkg.pab_control(p_maternity_id => r_affected_pat_Birth.maternity_id,
1889 p_deleting => FALSE);
1890 l_pat_Birth_count := l_pat_birth_count + 1;
1891 exception
1892 when others then
1893 hr_utility.trace('Error occurred while processing SPP Birth absence: '||r_affected_pat_Birth.maternity_id);
1894 hr_utility.trace('SQL error code: '||SQLCODE);
1895 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1896 hr_utility.trace('Person id: '||r_affected_pat_Birth.person_id);
1897 hr_utility.trace('Paternity Birth start date: '||r_affected_pat_Birth.date_start);
1898 hr_utility.trace('Paternity Birth end date: '||r_affected_pat_Birth.date_end);
1899 hr_utility.trace('Paternity Birth projected start date: '||r_affected_pat_Birth.date_projected_start);
1900 hr_utility.trace('Paternity Birth projected end date: '||r_affected_pat_Birth.date_projected_end);
1901 l_update_error := true;
1902 end;
1903 end loop;
1904 hr_utility.trace('Updated entries for '||l_pat_birth_count||' paternity births');
1905 else
1906 hr_utility.trace('SPP Birth element entries already updated for new tax year');
1907 end if;
1908 --
1909 commit;
1910 --
1911 exception
1912 when e_sppb_rate_not_set then
1913 hr_utility.trace('Warning: SPP Birth rate for new tax year not set');
1914 hr_utility.trace('Unable to proceed with updating SPP Birth entries');
1915 when e_no_new_sppb_entry then
1916 hr_utility.trace('Warning: Unable to locate SPP Birth entry in new tax year');
1917 hr_utility.trace('Entries for SPP Birth absences in new tax year may be non-existent');
1918 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
1919 when others then
1920 hr_utility.trace('Unexpected error occurred inside SPP Birth update block');
1921 hr_utility.trace('SQL error number: '||SQLCODE);
1922 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1923 rollback to pre_sppb_update_status;
1924 l_update_error := true;
1925 end; /* SPP Birth update block */
1926
1927 hr_utility.trace('Update of SSP, SMP, SAP, SPP Adoption and SPP Birth entries complete');
1928
1929 p_update_error := l_update_error;
1930
1931 hr_utility.trace('Leaving: '||g_package||'.update_ssp_smp_entries');
1932
1933
1934 exception
1935
1936
1937 when others
1938 then
1939 hr_utility.trace('Unexpected error occurred inside SSP/SMP element entries update procedure');
1940 hr_utility.trace('SQL error number: '||SQLCODE);
1941 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
1942 rollback to pre_update_status;
1943 p_update_error := true;
1944
1945
1946
1947
1948 end update_ssp_smp_entries;
1949
1950 --------------------------------------------------------------------------------
1951 --
1952 procedure update_ssp_smp_entries (P_UPDATE_ERROR OUT NOCOPY boolean, p_job_err OUT NOCOPY l_job_err_typ) is
1953
1954
1955 cursor csr_affected_absences is
1956 select nvl(paa.linked_absence_id,paa.absence_attendance_id) as absence_id,
1957 paa.person_id,
1958 nvl(paa.date_start,paa.sickness_start_date) as absence_start_date,
1959 nvl(paa.date_end,paa.sickness_end_date) as absence_end_date
1960 from per_absence_attendances paa,
1961 per_absence_attendance_types paat
1962 where paa.absence_attendance_type_id = paat.absence_attendance_type_id
1963 and paat.absence_category = 'S'
1964 and
1965 /* SSP absences which span tax years or start in old tax year and are open-ended */
1966 (
1967 ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
1968 and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
1969 )
1970 or
1971 ((paa.sickness_start_date between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
1972 and (paa.sickness_end_date > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.sickness_end_date is null)
1973 )
1974 or
1975 /* SSP absences which start in the new tax year */
1976 (
1977 paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
1978 or paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
1979 )
1980 )
1981 /* Do not retrieve terminated employees whose actual termination dates have passed or are null */
1982 and not exists
1983 ( select 1
1984 from per_all_people_f ppf,
1985 per_person_types ppt,
1986 per_periods_of_service pps
1987 where ppf.person_id = pps.person_id
1988 and ppt.person_type_id = ppf.person_type_id
1989 and ppt.system_person_type = 'EX_EMP'
1990 and nvl(pps.actual_termination_date,to_date('01/01/0001','DD/MM/YYYY')) <= sysdate
1991 and ppf.person_id = paa.person_id
1992 and pps.date_start = (select max(date_start)
1993 from per_periods_of_service pos
1994 where pos.person_id = pps.person_id)
1995 and ppf.effective_start_date >= pps.date_start
1996 and pps.date_start <= paa.sickness_start_date)
1997 order by nvl(paa.linked_absence_id,paa.absence_attendance_id);
1998
1999
2000 cursor csr_affected_leave(p_leave_type IN VARCHAR2) is
2001 -- p_leave_type = 'MA' - Maternity, 'AD' - Adoption, 'PA' - Paternity Adoption, 'PB' - Paternity Birth
2002 select paa.maternity_id,
2003 paa.person_id,
2004 paa.date_start,
2005 paa.date_end,
2006 paa.date_projected_start,
2007 paa.date_projected_end
2008 from per_absence_attendances paa,
2009 ssp_maternities mat
2010 where paa.maternity_id = mat.maternity_id
2011 and nvl(mat.leave_type, 'MA') = p_leave_type
2012 and
2013 /* SMP absences which span tax years or start in old tax year and are open-ended */
2014 (
2015 ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
2016 and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
2017 )
2018 or
2019 ((paa.date_projected_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
2020 and (paa.date_projected_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_projected_end is null)
2021 and paa.date_start IS NULL -- use projected dates only when actual dates not available
2022 )
2023 or
2024 /* SMP absences which start in the new tax year */
2025 (
2026 paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
2027 or (paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
2028 and paa.date_start IS NULL) -- use projected dates only when actual dates not available
2029 )
2030 )
2031 /* Do not retrieve employees whose final process dates have passed */
2032 and not exists
2033 ( select 1
2034 from per_all_people_f ppf,
2035 per_person_types ppt,
2036 per_periods_of_service pps
2037 where ppf.person_id = pps.person_id
2038 and ppt.person_type_id = ppf.person_type_id
2039 and ppt.system_person_type = 'EX_EMP'
2040 and pps.final_process_date <= sysdate
2041 and ppf.person_id = paa.person_id
2042 and pps.date_start = (select max(date_start)
2043 from per_periods_of_service pos
2044 where pos.person_id = pps.person_id)
2045 and ppf.effective_start_date >= pps.date_start
2046 and pps.date_start <= paa.date_start)
2047 /* Do not retrieve employees who are deceased */
2048 and not exists
2049 ( select 1
2050 from per_all_people_f ppf,
2051 per_periods_of_service pps
2052 where ppf.person_id = pps.person_id
2053 and pps.leaving_reason = 'D'
2054 and ppf.person_id = paa.person_id)
2055 order by paa.maternity_id;
2056
2057
2058 l_count NUMBER := 0;
2059 l_mat_count NUMBER := 0;
2060 l_adop_count NUMBER := 0;
2061 l_pat_adop_count NUMBER := 0;
2062 l_pat_birth_count NUMBER := 0;
2063
2064 e_ssp_rate_not_set exception;
2065 e_smp_rate_not_set exception;
2066 e_sap_rate_not_set exception;
2067 e_sppa_rate_not_set exception;
2068 e_sppb_rate_not_set exception;
2069
2070 e_no_new_ssp_entry exception;
2071 e_no_new_smp_entry exception;
2072 e_no_new_sap_entry exception;
2073 e_no_new_sppa_entry exception;
2074 e_no_new_sppb_entry exception;
2075
2076 l_update_error boolean := FALSE;
2077
2078 --6800788 begin
2079 l_fail number;
2080 l_fail_count number :=0;
2081 l_job_no binary_integer;
2082 Type l_job_type is record
2083 (
2084 Job_no number,
2085 Person_id number,
2086 Absence_id number
2087 );
2088 Type l_tbl_job_typ is table of l_job_type index by binary_integer;
2089 l_tbl_job l_tbl_job_typ;
2090 --6800788 end
2091
2092 /* Function to check if SSP entries in new tax year have already been updated */
2093 /* with new SSP rates and recalculated - returns TRUE if so, FALSE if not */
2094 function ssp_entries_already_updated return boolean is
2095
2096 cursor csr_new_ssp_rate is
2097 select piv.default_value
2098 from pay_input_values_f piv,
2099 pay_element_types_f petf
2100 where petf.element_type_id = piv.element_type_id
2101 and piv.name = 'Rate'
2102 and petf.element_name = 'Statutory Sick Pay'
2103 and piv.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate);
2104
2105
2106 cursor csr_first_new_ssp_entry is
2107 select peev1.screen_entry_value
2108 from pay_element_entry_values_f peev1,
2109 pay_element_entry_values_f peev2,
2110 pay_input_values_f piv,
2111 pay_element_entries_f peef,
2112 pay_element_links_f pelf,
2113 pay_element_types_f petf
2114 where piv.input_value_id = peev1.input_value_id
2115 and peev1.element_entry_id = peev2.element_entry_id
2116 and peev1.element_entry_id = peef.element_entry_id
2117 and peef.element_link_id = pelf.element_link_id
2118 and pelf.element_type_id = petf.element_type_id
2119 and piv.name = 'Rate'
2120 and petf.element_name = 'Statutory Sick Pay'
2121 and piv.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate)
2122 and peev2.element_entry_value_id =
2123 (select peev3.element_entry_value_id
2124 from pay_element_entry_values_f peev3
2125 where input_value_id =
2126 (select distinct input_value_id
2127 from pay_input_values_f piv,
2128 pay_element_types_f petf
2129 where petf.element_type_id = piv.element_type_id
2130 and petf.element_name = 'Statutory Sick Pay'
2131 and piv.name = 'From')
2132 and peev3.screen_entry_value >
2133 fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate))
2134 /* Retrieve only those entries that will be retrieved by main SSP query */
2135 and peev3.element_entry_id in
2136 ( select peef1.element_entry_id
2137 from pay_element_entries_f peef1,
2138 per_all_assignments_f paf,
2139 per_all_people_f ppf,
2140 per_person_types ppt,
2141 per_periods_of_service pps
2142 where peef1.assignment_id = paf.assignment_id
2143 and paf.person_id = ppf.person_id
2144 and ppf.person_id = pps.person_id
2145 and ppt.person_type_id = ppf.person_type_id
2146 and peev3.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2147 and fnd_date.date_to_canonical(ppf.effective_end_date)
2148 and peev3.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2149 and fnd_date.date_to_canonical(paf.effective_end_date)
2150 and ppt.system_person_type = 'EMP'
2151 and nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2152 )
2153 and rownum = 1);
2154
2155
2156 l_new_SSP_rate number;
2157 l_first_SSP_entry_rate number;
2158
2159
2160
2161 begin
2162
2163 hr_utility.trace('Entering ssp_entries_already_updated function');
2164
2165 /* Find SSP rate for new tax year */
2166 open csr_new_ssp_rate;
2167 fetch csr_new_ssp_rate into l_new_SSP_rate;
2168
2169 /* If unable to find new SSP rate, then rate probably not set yet */
2170 /* Make note of this and exit quietly */
2171 if csr_new_ssp_rate%notfound
2172 then
2173 close csr_new_ssp_rate;
2174 raise e_ssp_rate_not_set;
2175 end if;
2176
2177 close csr_new_ssp_rate;
2178
2179 hr_utility.trace('New SSP rate: '||l_new_SSP_rate);
2180
2181 /* Find first element entry value holding SSP rate for new tax year */
2182 open csr_first_new_ssp_entry;
2183 fetch csr_first_new_ssp_entry into l_first_SSP_entry_rate;
2184
2185 /* If unable to find SSP entry in new tax year, then warn user */
2186 /* Possible causes are employee terminations or stoppages */
2187 if csr_first_new_ssp_entry%notfound
2188 then
2189 close csr_first_new_ssp_entry;
2190 raise e_no_new_ssp_entry;
2191 end if;
2192
2193 close csr_first_new_ssp_entry;
2194
2195 hr_utility.trace('First SSP entry rate: '||l_first_SSP_entry_rate);
2196
2197 if l_new_SSP_rate = l_first_SSP_entry_rate
2198 then
2199
2200 return true;
2201
2202 else
2203
2204 return false;
2205
2206 end if;
2207
2208 exception
2209
2210 when others
2211 then raise;
2212
2213
2214 end ssp_entries_already_updated;
2215
2216 /* Function to check if SMP entries in new tax year have already updated */
2217 /* with new SMP rates and recalculated - returns TRUE if so, FALSE if not */
2218 function smp_rate_changed return boolean is
2219
2220 /* Check for SMP rates beginning on or after April 1st */
2221 cursor csr_new_smp_rate is
2222 select petf.element_information10, petf.element_information9, petf.element_information16
2223 from pay_element_types_f petf
2224 where petf.element_name = 'Statutory Maternity Pay'
2225 and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
2226 order by effective_start_date;
2227
2228
2229 /* 09/12/2003 rmakhija - commented out following cursor because element entry value
2230 for 'Amount' is a calculated value and it is not same
2231 as smp rate therefore it can not be compared with new smp
2232 rate to detect the change in smp rate. A new cursor
2233 csr_old_smp_rate has been added to find old smp rate
2234 and to compare it with new smp rate to detect the change.
2235
2236 cursor csr_first_new_smp_entry is
2237 select peev1.screen_entry_value
2238 from pay_element_entry_values_f peev1,
2239 pay_element_entry_values_f peev2,
2240 pay_element_entry_values_f peev3,
2241 pay_input_values_f piv,
2242 pay_element_entries_f peef,
2243 pay_element_links_f pelf,
2244 pay_element_types_f petf
2245 where piv.input_value_id = peev1.input_value_id
2246 and peev1.element_entry_id = peev2.element_entry_id
2247 and peev1.element_entry_id = peev3.element_entry_id
2248 and peev1.element_entry_id = peef.element_entry_id
2249 and peef.element_link_id = pelf.element_link_id
2250 and pelf.element_type_id = petf.element_type_id
2251 and piv.name = 'Amount'
2252 and petf.element_name = 'Statutory Maternity Pay'
2253 and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
2254 -- Time restriction - only rows after tax year end
2255 and peev2.input_value_id =
2256 (select input_value_id
2257 from pay_input_values_f piv
2258 where petf.element_type_id = piv.element_type_id
2259 and upper(piv.name) = upper('Week commencing')
2260 )
2261 and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
2262 -- Retrieve only those entries that main SMP query will retrieve
2263 and exists
2264 ( select 1
2265 from per_all_assignments_f paf,
2266 per_all_people_f ppf,
2267 per_person_types ppt,
2268 per_periods_of_service pps
2269 where peef.assignment_id = paf.assignment_id
2270 and paf.person_id = ppf.person_id
2271 and ppf.person_id = pps.person_id
2272 and ppt.person_type_id = ppf.person_type_id
2273 and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2274 and fnd_date.date_to_canonical(ppf.effective_end_date)
2275 and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2276 and fnd_date.date_to_canonical(paf.effective_end_date)
2277 and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2278 )
2279 and peev3.input_value_id =
2280 (select input_value_id
2281 from pay_input_values_f piv
2282 where petf.element_type_id = piv.element_type_id
2283 and upper(piv.name) = upper('Rate'))
2284 -- Rate restriction - only retrieve entries on LOW rate for SMP
2285 and upper(peev3.screen_entry_value) = upper('Low')
2286 -- Get first row that matches all of the above criteria
2287 and rownum = 1;
2288 */
2289
2290 cursor csr_old_smp_rate is
2291 select petf.element_information10, petf.element_information9, petf.element_information16
2292 from pay_element_types_f petf
2293 where petf.element_name = 'Statutory Maternity Pay'
2294 and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
2295 order by effective_start_date desc;
2296
2297 l_new_SMP_rate number;
2298 l_old_SMP_rate number;
2299 l_new_high_smp_rate number;
2300 l_old_high_smp_rate number;
2301 l_new_std_smp_rate number;
2302 l_old_std_smp_rate number;
2303
2304 begin
2305
2306 hr_utility.trace('Entering smp_rate_changed function');
2307 --
2308 /* Find SMP rate for new tax year */
2309 open csr_new_smp_rate;
2310 fetch csr_new_smp_rate into l_new_SMP_rate, l_new_high_smp_rate, l_new_std_smp_rate;
2311 --
2312 /* If unable to find new SMP rate, then rate probably not set yet */
2313 /* Make note of this and exit quietly */
2314 if csr_new_SMP_rate%notfound
2315 then
2316 close csr_new_SMP_rate;
2317 raise e_SMP_rate_not_set;
2318 end if;
2319 --
2320 close csr_new_smp_rate;
2321 --
2322 hr_utility.trace('New Lower SMP rate: '||l_new_SMP_rate);
2323 hr_utility.trace('New Higher SMP rate: '||l_new_high_SMP_rate);
2324 hr_utility.trace('New Standard SMP rate: '||l_new_std_SMP_rate);
2325 --
2326 /* Find SMP rate for current tax year */
2327 open csr_old_smp_rate;
2328 fetch csr_old_smp_rate into l_old_SMP_rate, l_old_high_smp_rate, l_old_std_smp_rate;
2329 close csr_old_smp_rate;
2330 --
2331 hr_utility.trace('Old SMP rate: '||l_old_SMP_rate);
2332 hr_utility.trace('Old Higher SMP rate: '||l_old_high_SMP_rate);
2333 hr_utility.trace('Old Standard SMP rate: '||l_old_std_SMP_rate);
2334 --
2335 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)
2336 then
2337 return false;
2338 else
2339 return true;
2340 end if;
2341 --
2342 exception
2343 when others then
2344 raise;
2345 end smp_rate_changed;
2346
2347
2348 /* Function to check if SAP entries in new tax year have already updated */
2349 /* with new SAP rates and recalculated - returns TRUE if so, FALSE if not */
2350 function sap_rate_changed return boolean is
2351
2352 /* Check for SAP rates beginning on or after April 1st */
2353 cursor csr_new_sap_rate is
2354 select petf.element_information5, petf.element_information7
2355 from pay_element_types_f petf
2356 where petf.element_name = 'Statutory Adoption Pay'
2357 and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
2358 order by effective_start_date;
2359 --
2360 /* 09/12/2003 rmakhija - commented out following cursor because element entry value
2361 for 'Amount' is a calculated value and it is not same
2362 as sap rate therefore it can not be compared with new sap
2363 rate to detect the change in sap rate. A new cursor
2364 csr_old_sap_rate has been added to find old sap rate
2365 and to compare it with new sap rate to detect the change.
2366
2367 cursor csr_first_new_sap_entry is
2368 select peev1.screen_entry_value
2369 from pay_element_entry_values_f peev1,
2370 pay_element_entry_values_f peev2,
2371 pay_input_values_f piv,
2372 pay_element_entries_f peef,
2373 pay_element_links_f pelf,
2374 pay_element_types_f petf
2375 where piv.input_value_id = peev1.input_value_id
2376 and peev1.element_entry_id = peev2.element_entry_id
2377 and peev1.element_entry_id = peef.element_entry_id
2378 and peef.element_link_id = pelf.element_link_id
2379 and pelf.element_type_id = petf.element_type_id
2380 and piv.name = 'Amount'
2381 and petf.element_name = 'Statutory Adoption Pay'
2382 and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
2383 -- Time restriction - only rows after tax year end
2384 and peev2.input_value_id =
2385 (select input_value_id
2386 from pay_input_values_f piv
2387 where petf.element_type_id = piv.element_type_id
2388 and upper(piv.name) = upper('Week commencing')
2389 )
2390 and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
2391 -- Retrieve only those entries that main sap query will retrieve
2392 and exists
2393 ( select 1
2394 from per_all_assignments_f paf,
2395 per_all_people_f ppf,
2396 per_person_types ppt,
2397 per_periods_of_service pps
2398 where peef.assignment_id = paf.assignment_id
2399 and paf.person_id = ppf.person_id
2400 and ppf.person_id = pps.person_id
2401 and ppt.person_type_id = ppf.person_type_id
2402 and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2403 and fnd_date.date_to_canonical(ppf.effective_end_date)
2404 and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2405 and fnd_date.date_to_canonical(paf.effective_end_date)
2406 and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2407 )
2408 -- Get first row that matches all of the above criteria
2409 and rownum = 1;
2410 */
2411 --
2412 cursor csr_old_sap_rate is
2413 select petf.element_information5, petf.element_information7
2414 from pay_element_types_f petf
2415 where petf.element_name = 'Statutory Adoption Pay'
2416 and petf.effective_start_date < hr_gbnicar.uk_tax_yr_end(sysdate) - 4
2417 order by effective_start_date desc;
2418 --
2419 --
2420 l_new_sap_rate number;
2421 l_old_sap_rate number;
2422 --
2423 l_new_std_sap_rate number;
2424 l_old_std_sap_rate number;
2425 --
2426 begin
2427 --
2428 hr_utility.trace('Entering sap_rate_changed function');
2429 --
2430 /* Find sap rate for new tax year */
2431 open csr_new_sap_rate;
2432 fetch csr_new_sap_rate into l_new_sap_rate, l_new_std_sap_rate;
2433 --
2434 /* If unable to find new sap rate, then rate probably not set yet */
2435 /* Make note of this and exit quietly */
2436 if csr_new_sap_rate%notfound then
2437 close csr_new_sap_rate;
2438 raise e_sap_rate_not_set;
2439 end if;
2440 --
2441 close csr_new_sap_rate;
2442 --
2443 hr_utility.trace('New sap rate: '||l_new_sap_rate);
2444 hr_utility.trace('New std sap rate: '||l_new_std_sap_rate);
2445 --
2446 /* Find sap rate for current tax year */
2447 open csr_old_sap_rate;
2448 fetch csr_old_sap_rate into l_old_sap_rate, l_old_std_sap_rate;
2449 close csr_old_sap_rate;
2450 --
2451 hr_utility.trace('Old sap rate: '||l_old_sap_rate);
2452 hr_utility.trace('Old std sap rate: '||l_old_std_sap_rate);
2453 --
2454 if (l_new_sap_rate = l_old_sap_rate) and (l_new_std_sap_rate = l_old_std_sap_rate) then
2455 return false;
2456 else
2457 return true;
2458 end if;
2459 --
2460 exception
2461 when others then raise;
2462 end sap_rate_changed;
2463
2464 /* Function to check if SPPA entries in new tax year have already updated */
2465 /* with new SPPA rates and recalculated - returns TRUE if so, FALSE if not */
2466 function sppa_rate_changed return boolean is
2467
2468 /* Check for SPPA rates beginning on or after April 1st */
2469 cursor csr_new_sppa_rate is
2470 select petf.element_information6, petf.element_information8
2471 from pay_element_types_f petf
2472 where petf.element_name = 'Statutory Paternity Pay Adoption'
2473 and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
2474 order by effective_start_date;
2475 --
2476 /* 09/12/2003 rmakhija - commented out following cursor because element entry value
2477 for 'Amount' is a calculated value and it is not same
2478 as SPP Adoption rate therefore it can not be compared with new
2479 SPP Adoption rate to detect the change. A new cursor
2480 csr_old_sppa_rate has been added to find old SPP Adoption rate
2481 and to compare it with new SPP Adoption rate to detect the change.
2482
2483 cursor csr_first_new_sppa_entry is
2484 select peev1.screen_entry_value
2485 from pay_element_entry_values_f peev1,
2486 pay_element_entry_values_f peev2,
2487 pay_input_values_f piv,
2488 pay_element_entries_f peef,
2489 pay_element_links_f pelf,
2490 pay_element_types_f petf
2491 where piv.input_value_id = peev1.input_value_id
2492 and peev1.element_entry_id = peev2.element_entry_id
2493 and peev1.element_entry_id = peef.element_entry_id
2494 and peef.element_link_id = pelf.element_link_id
2495 and pelf.element_type_id = petf.element_type_id
2496 and piv.name = 'Amount'
2497 and petf.element_name = 'Statutory Paternity Pay Adoption'
2498 and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
2499 -- Time restriction - only rows after tax year end
2500 and peev2.input_value_id =
2501 (select input_value_id
2502 from pay_input_values_f piv
2503 where petf.element_type_id = piv.element_type_id
2504 and upper(piv.name) = upper('Week commencing')
2505 )
2506 and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
2507 -- Retrieve only those entries that main sppa query will retrieve
2508 and exists
2509 ( select 1
2510 from per_all_assignments_f paf,
2511 per_all_people_f ppf,
2512 per_person_types ppt,
2513 per_periods_of_service pps
2514 where peef.assignment_id = paf.assignment_id
2515 and paf.person_id = ppf.person_id
2516 and ppf.person_id = pps.person_id
2517 and ppt.person_type_id = ppf.person_type_id
2518 and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2519 and fnd_date.date_to_canonical(ppf.effective_end_date)
2520 and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2521 and fnd_date.date_to_canonical(paf.effective_end_date)
2522 and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2523 )
2524 -- Get first row that matches all of the above criteria
2525 and rownum = 1;
2526 */
2527 --
2528 cursor csr_old_sppa_rate is
2529 select petf.element_information6, petf.element_information8
2530 from pay_element_types_f petf
2531 where petf.element_name = 'Statutory Paternity Pay Adoption'
2532 and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
2533 order by effective_start_date desc;
2534
2535 --
2536 l_new_sppa_rate number;
2537 l_old_sppa_rate number;
2538 --
2539 l_new_std_sppa_rate number;
2540 l_old_std_sppa_rate number;
2541 --
2542 begin
2543 --
2544 hr_utility.trace('Entering sppa_rate_changed function');
2545 --
2546 /* Find sppa rate for new tax year */
2547 open csr_new_sppa_rate;
2548 fetch csr_new_sppa_rate into l_new_sppa_rate, l_new_std_sppa_rate;
2549 --
2550 /* If unable to find new sppa rate, then rate probably not set yet */
2551 /* Make note of this and exit quietly */
2552 if csr_new_sppa_rate%notfound then
2553 close csr_new_sppa_rate;
2554 raise e_sppa_rate_not_set;
2555 end if;
2556 --
2557 close csr_new_sppa_rate;
2558 --
2559 hr_utility.trace('New sppa rate: '||l_new_sppa_rate);
2560 hr_utility.trace('New std sppa rate: '||l_new_std_sppa_rate);
2561 --
2562 open csr_old_sppa_rate;
2563 fetch csr_old_sppa_rate into l_old_sppa_rate, l_old_std_sppa_rate;
2564 close csr_old_sppa_rate;
2565 --
2566 hr_utility.trace('Old SPPA rate: '||l_old_sppa_rate);
2567 hr_utility.trace('Old Std SPPA rate: '||l_old_std_sppa_rate);
2568 --
2569 if (l_new_sppa_rate = l_old_sppa_rate) and (l_new_std_sppa_rate = l_old_std_sppa_rate) then
2570 return false;
2571 else
2572 return true;
2573 end if;
2574 --
2575 exception
2576 when others then raise;
2577 end sppa_rate_changed;
2578
2579 /* Function to check if SPPB entries in new tax year have already updated */
2580 /* with new SPPB rates and recalculated - returns TRUE if so, FALSE if not */
2581 function sppb_rate_changed return boolean is
2582
2583 /* Check for SPPB rates beginning on or after April 1st */
2584 cursor csr_new_sppb_rate is
2585 select petf.element_information6, petf.element_information9
2586 from pay_element_types_f petf
2587 where petf.element_name = 'Statutory Paternity Pay Birth'
2588 and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
2589 order by effective_start_date;
2590 --
2591 /* 09/12/2003 rmakhija - commented out following cursor because element entry value
2592 for 'Amount' is a calculated value and it is not same
2593 as SPP Birth rate therefore it can not be compared with new
2594 SPP Birth rate to detect the change. A new cursor
2595 csr_old_sppb_rate has been added to find old SPP Birth rate
2596 and to compare it with new SPP Birth rate to detect the change.
2597
2598 cursor csr_first_new_sppb_entry is
2599 select peev1.screen_entry_value
2600 from pay_element_entry_values_f peev1,
2601 pay_element_entry_values_f peev2,
2602 pay_input_values_f piv,
2603 pay_element_entries_f peef,
2604 pay_element_links_f pelf,
2605 pay_element_types_f petf
2606 where piv.input_value_id = peev1.input_value_id
2607 and peev1.element_entry_id = peev2.element_entry_id
2608 and peev1.element_entry_id = peef.element_entry_id
2609 and peef.element_link_id = pelf.element_link_id
2610 and pelf.element_type_id = petf.element_type_id
2611 and piv.name = 'Amount'
2612 and petf.element_name = 'Statutory Paternity Pay Birth'
2613 and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
2614 -- Time restriction - only rows after tax year end
2615 and peev2.input_value_id =
2616 (select input_value_id
2617 from pay_input_values_f piv
2618 where petf.element_type_id = piv.element_type_id
2619 and upper(piv.name) = upper('Week commencing')
2620 )
2621 and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
2622 -- Retrieve only those entries that main SPPB query will retrieve
2623 and exists
2624 ( select 1
2625 from per_all_assignments_f paf,
2626 per_all_people_f ppf,
2627 per_person_types ppt,
2628 per_periods_of_service pps
2629 where peef.assignment_id = paf.assignment_id
2630 and paf.person_id = ppf.person_id
2631 and ppf.person_id = pps.person_id
2632 and ppt.person_type_id = ppf.person_type_id
2633 and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2634 and fnd_date.date_to_canonical(ppf.effective_end_date)
2635 and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2636 and fnd_date.date_to_canonical(paf.effective_end_date)
2637 and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2638 )
2639 -- Get first row that matches all of the above criteria
2640 and rownum = 1;
2641 */
2642 --
2643 cursor csr_old_sppb_rate is
2644 select petf.element_information6, petf.element_information9
2645 from pay_element_types_f petf
2646 where petf.element_name = 'Statutory Paternity Pay Birth'
2647 and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
2648 order by effective_start_date desc;
2649 --
2650 l_new_sppb_rate number;
2651 l_old_sppb_rate number;
2652 --
2653 l_new_std_sppb_rate number;
2654 l_old_std_sppb_rate number;
2655 --
2656 begin
2657 --
2658 hr_utility.trace('Entering sppb_rate_changed function');
2659 --
2660 /* Find SPPB rate for new tax year */
2661 open csr_new_sppb_rate;
2662 fetch csr_new_sppb_rate into l_new_sppb_rate, l_new_std_sppb_rate;
2663 --
2664 /* If unable to find new SPPB rate, then rate probably not set yet */
2665 /* Make note of this and exit quietly */
2666 if csr_new_sppb_rate%notfound then
2667 close csr_new_sppb_rate;
2668 raise e_sppb_rate_not_set;
2669 end if;
2670 --
2671 close csr_new_sppb_rate;
2672 --
2673 hr_utility.trace('New SPPB rate: '||l_new_sppb_rate);
2674 hr_utility.trace('New Std SPPB rate: '||l_new_Std_sppb_rate);
2675 --
2676 open csr_old_sppb_rate;
2677 fetch csr_old_sppb_rate into l_old_sppb_rate, l_old_std_sppb_rate;
2678 close csr_old_sppb_rate;
2679 --
2680 hr_utility.trace('old SPPB rate: '||l_old_sppb_rate);
2681 hr_utility.trace('old Std SPPB rate: '||l_old_Std_sppb_rate);
2682 --
2683 if (l_new_sppb_rate = l_old_sppb_rate) and (l_new_std_sppb_rate = l_old_std_sppb_rate) then
2684 return false;
2685 else
2686 return true;
2687 end if;
2688 --
2689 exception
2690 when others then raise;
2691 end sppb_rate_changed;
2692
2693 /* Main program body */
2694 begin
2695
2696 hr_utility.trace('Entering: '||g_package||'.update_ssp_smp_entries');
2697
2698 savepoint pre_update_status;
2699 /* Check first whether SSP element entries already updated for new tax year */
2700 /* SSP update block */
2701 begin
2702
2703 savepoint pre_ssp_update_status;
2704
2705 if not ssp_entries_already_updated
2706 then
2707
2708 hr_utility.trace('SSP element entries not updated, updating ....');
2709
2710 for r_affected_absences in csr_affected_absences loop
2711
2712 /* SSP control call block */
2713 begin
2714
2715 hr_utility.trace('Processing SSP absence: '||r_affected_absences.absence_id);
2716 --6800788 begin
2717 --ssp_ssp_pkg.ssp_control(r_affected_absences.absence_id);
2718 DBMS_JOB.SUBMIT(l_job_no,'ssp_ssp_pkg.ssp_control('||r_affected_absences.absence_id||');');
2719 l_count := l_count + 1;
2720 l_tbl_job(l_count).job_no := l_job_no;
2721 l_tbl_job(l_count).person_id := r_affected_absences.person_id;
2722 l_tbl_job(l_count).absence_id := r_affected_absences.absence_id;
2723 if mod(l_count,500) = 0 then
2724 commit;
2725 end if;
2726 --6800788 end
2727 exception
2728
2729 when others then
2730 hr_utility.trace('Error occurred while processing SSP absence: '||r_affected_absences.absence_id);
2731 hr_utility.trace('SQL error code: '||SQLCODE);
2732 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2733 hr_utility.trace('Person id: '||r_affected_absences.person_id);
2734 hr_utility.trace('Absence start date: '||r_affected_absences.absence_start_date);
2735 hr_utility.trace('Absence end date: '||r_affected_absences.absence_end_date);
2736 l_update_error := true;
2737
2738 end;
2739
2740
2741
2742 end loop;
2743
2744 hr_utility.trace('Updated entries for '||l_count||' absences');
2745 -- dbms_output.put_line(' Total SSP absences ='|| l_count);
2746 else
2747
2748 hr_utility.trace('SSP element entries already updated for new tax year');
2749
2750 end if;
2751
2752 commit;
2753
2754 exception
2755
2756 when e_ssp_rate_not_set
2757 then
2758 hr_utility.trace('Warning: SSP rate for new tax year not set');
2759 hr_utility.trace('Unable to proceed with updating SSP entries');
2760
2761
2762 when e_no_new_ssp_entry
2763 then
2764 hr_utility.trace('Warning: Unable to locate SSP entry in new tax year');
2765 hr_utility.trace('Entries for SSP absences in new tax year may be non-existent');
2766 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end dates of absences');
2767
2768 when others
2769 then
2770 hr_utility.trace('Unexpected error occurred inside SSP update block');
2771 hr_utility.trace('SQL error number: '||SQLCODE);
2772 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2773 rollback to pre_ssp_update_status;
2774 l_update_error := true;
2775
2776 end; /* SSP update block */
2777
2778 /* Now check SMP entries to see if they have been updated */
2779 /* SMP update block */
2780 -- 05/12/2003 rmakhija: Uncommented following section for TYE 2003/4
2781 begin
2782 --
2783 savepoint pre_smp_update_status;
2784 --
2785 if smp_rate_changed then
2786 --
2787 hr_utility.trace('SMP element entries updating ....');
2788 for r_affected_maternities in csr_affected_leave('MA') loop
2789 /* SMP Control call block */
2790 begin
2791 hr_utility.trace('Processing SMP absence: '||r_affected_maternities.maternity_id);
2792 --6800788 begin
2793 -- ssp_smp_pkg.smp_control(p_maternity_id => r_affected_maternities.maternity_id,
2794 -- p_deleting => FALSE);
2795
2796 DBMS_JOB.SUBMIT(l_job_no,'ssp_smp_pkg.smp_control('||r_affected_maternities.maternity_id||',FALSE);');
2797 l_mat_count := l_mat_count + 1;
2798 l_count := l_count + 1;
2799 l_tbl_job(l_count).job_no := l_job_no;
2800 l_tbl_job(l_count).person_id := r_affected_maternities.person_id;
2801 l_tbl_job(l_count).absence_id := r_affected_maternities.maternity_id;
2802 if mod(l_count,500) = 0 then
2803 commit;
2804 end if;
2805 --6800788 end
2806
2807 exception
2808 when others then
2809 hr_utility.trace('Error occurred while processing SMP absence: '||r_affected_maternities.maternity_id);
2810 hr_utility.trace('SQL error code: '||SQLCODE);
2811 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2812 hr_utility.trace('Person id: '||r_affected_maternities.person_id);
2813 hr_utility.trace('Maternity start date: '||r_affected_maternities.date_start);
2814 hr_utility.trace('Maternity end date: '||r_affected_maternities.date_end);
2815 hr_utility.trace('Maternity projected start date: '||r_affected_maternities.date_projected_start);
2816 hr_utility.trace('Maternity projected end date: '||r_affected_maternities.date_projected_end);
2817 l_update_error := true;
2818 end;
2819 end loop;
2820 hr_utility.trace('Updated entries for '||l_mat_count||' maternities');
2821 -- dbms_output.put_line(' Total SMP absences ='|| l_mat_count);
2822 else
2823 hr_utility.trace('SMP element entries already updated for new tax year');
2824 end if;
2825 --
2826 commit;
2827 --
2828 exception
2829 when e_smp_rate_not_set then
2830 hr_utility.trace('Warning: SMP rate for new tax year not set');
2831 hr_utility.trace('Unable to proceed with updating SMP entries');
2832 when e_no_new_smp_entry then
2833 hr_utility.trace('Warning: Unable to locate SMP entry in new tax year');
2834 hr_utility.trace('Entries for SMP absences in new tax year may be non-existent');
2835 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2836 when others then
2837 hr_utility.trace('Unexpected error occurred inside SMP update block');
2838 hr_utility.trace('SQL error number: '||SQLCODE);
2839 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2840 rollback to pre_smp_update_status;
2841 l_update_error := true;
2842 end; /* SMP update block */
2843 -- 05/12/2003 rmakhija: Uncommented section ends here
2844 -- Following code has been added to auto update SAP/SPP Adoption/SPP Birth element entries
2845 -- Begin SAP Update Block
2846 BEGIN
2847 --
2848 savepoint pre_sap_update_status;
2849 --
2850 if sap_rate_changed then
2851 --
2852 hr_utility.trace('SAP element entries updating ....');
2853 for r_affected_adoption in csr_affected_leave('AD') loop
2854 /* SAP Control call block */
2855 begin
2856 hr_utility.trace('Processing SAP absence: '||r_affected_adoption.maternity_id);
2857 --6800788 begin
2858 -- ssp_sap_pkg.sap_control(p_maternity_id => r_affected_adoption.maternity_id,
2859 -- p_deleting => FALSE);
2860
2861 DBMS_JOB.SUBMIT(l_job_no,'ssp_sap_pkg.sap_control('||r_affected_adoption.maternity_id||',FALSE);');
2862 l_adop_count := l_adop_count + 1;
2863 l_count := l_count + 1;
2864 l_tbl_job(l_count).job_no := l_job_no;
2865 l_tbl_job(l_count).person_id := r_affected_adoption.person_id;
2866 l_tbl_job(l_count).absence_id := r_affected_adoption.maternity_id;
2867 if mod(l_count,500) = 0 then
2868 commit;
2869 end if;
2870 --6800788 end
2871
2872 exception
2873 when others then
2874 hr_utility.trace('Error occurred while processing SAP absence: '||r_affected_adoption.maternity_id);
2875 hr_utility.trace('SQL error code: '||SQLCODE);
2876 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2877 hr_utility.trace('Person id: '||r_affected_adoption.person_id);
2878 hr_utility.trace('Adoption start date: '||r_affected_adoption.date_start);
2879 hr_utility.trace('Adoption end date: '||r_affected_adoption.date_end);
2880 hr_utility.trace('Adoption projected start date: '||r_affected_adoption.date_projected_start);
2881 hr_utility.trace('Adoption projected end date: '||r_affected_adoption.date_projected_end);
2882 l_update_error := true;
2883 end;
2884 end loop;
2885 hr_utility.trace('Updated entries for '||l_adop_count||' adoptions');
2886 -- dbms_output.put_line(' Total SAP absences ='|| l_adop_count);
2887 else
2888 hr_utility.trace('SAP element entries already updated for new tax year');
2889 end if;
2890 --
2891 commit;
2892 --
2893 exception
2894 when e_sap_rate_not_set then
2895 hr_utility.trace('Warning: SAP rate for new tax year not set');
2896 hr_utility.trace('Unable to proceed with updating SAP entries');
2897 when e_no_new_sap_entry then
2898 hr_utility.trace('Warning: Unable to locate SAP entry in new tax year');
2899 hr_utility.trace('Entries for SAP absences in new tax year may be non-existent');
2900 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2901 when others then
2902 hr_utility.trace('Unexpected error occurred inside SAP update block');
2903 hr_utility.trace('SQL error number: '||SQLCODE);
2904 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2905 rollback to pre_sap_update_status;
2906 l_update_error := true;
2907 end; /* SAP update block */
2908 -- Begin SPP Adoption Update Block
2909 BEGIN
2910 --
2911 savepoint pre_sppa_update_status;
2912 --
2913 if sppa_rate_changed then
2914 --
2915 hr_utility.trace('SPP Adoption element entries updating ....');
2916 for r_affected_pat_adop in csr_affected_leave('PA') loop
2917 /* SPP Adoption Control call block */
2918 begin
2919 hr_utility.trace('Processing SPP Adoption absence: '||r_affected_pat_adop.maternity_id);
2920 --6800788 begin
2921 -- ssp_pad_pkg.pad_control(p_maternity_id => r_affected_pat_adop.maternity_id,
2922 -- p_deleting => FALSE);
2923
2924 DBMS_JOB.SUBMIT(l_job_no,'ssp_pad_pkg.pad_control('||r_affected_pat_adop.maternity_id||',FALSE);');
2925 l_pat_adop_count := l_pat_adop_count + 1;
2926 l_count := l_count + 1;
2927 l_tbl_job(l_count).job_no := l_job_no;
2928 l_tbl_job(l_count).person_id := r_affected_pat_adop.person_id;
2929 l_tbl_job(l_count).absence_id := r_affected_pat_adop.maternity_id;
2930 if mod(l_count,500) = 0 then
2931 commit;
2932 end if;
2933 --6800788 end
2934
2935 exception
2936 when others then
2937 hr_utility.trace('Error occurred while processing SPP Adoption absence: '||r_affected_pat_adop.maternity_id);
2938 hr_utility.trace('SQL error code: '||SQLCODE);
2939 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2940 hr_utility.trace('Person id: '||r_affected_pat_adop.person_id);
2941 hr_utility.trace('Paternity Adoption start date: '||r_affected_pat_adop.date_start);
2942 hr_utility.trace('Paternity Adoption end date: '||r_affected_pat_adop.date_end);
2943 hr_utility.trace('Paternity Adoption projected start date: '||r_affected_pat_adop.date_projected_start);
2944 hr_utility.trace('Paternity Adoption projected end date: '||r_affected_pat_adop.date_projected_end);
2945 l_update_error := true;
2946 end;
2947 end loop;
2948 hr_utility.trace('Updated entries for '||l_pat_adop_count||' paternity adoptions');
2949 -- dbms_output.put_line(' Total SPPA absences ='|| l_pat_adop_count);
2950 else
2951 hr_utility.trace('SPP Adoption element entries already updated for new tax year');
2952 end if;
2953 --
2954 commit;
2955 --
2956 exception
2957 when e_sppa_rate_not_set then
2958 hr_utility.trace('Warning: SPP ADoption rate for new tax year not set');
2959 hr_utility.trace('Unable to proceed with updating SPP ADoption entries');
2960 when e_no_new_sppa_entry then
2961 hr_utility.trace('Warning: Unable to locate SPP ADoption entry in new tax year');
2962 hr_utility.trace('Entries for SPP ADoption absences in new tax year may be non-existent');
2963 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
2964 when others then
2965 hr_utility.trace('Unexpected error occurred inside SPP ADoption update block');
2966 hr_utility.trace('SQL error number: '||SQLCODE);
2967 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
2968 rollback to pre_sppa_update_status;
2969 l_update_error := true;
2970 end; /* SPP Adoption update block */
2971 -- Begin SPP Birth Update Block
2972 BEGIN
2973 --
2974 savepoint pre_sppb_update_status;
2975 --
2976 if sppb_rate_changed then
2977 --
2978 hr_utility.trace('SPP Birth element entries updating ....');
2979 --6800788 begin
2980 -- for r_affected_pat_Birth in csr_affected_leave('PA') loop
2981 for r_affected_pat_Birth in csr_affected_leave('PB') loop
2982 --6800788 end
2983 /* SPP Birth Control call block */
2984 begin
2985 hr_utility.trace('Processing SPP Birth absence: '||r_affected_pat_Birth.maternity_id);
2986 --6800788 begin
2987 -- ssp_pab_pkg.pab_control(p_maternity_id => r_affected_pat_Birth.maternity_id,
2988 -- p_deleting => FALSE);
2989
2990 DBMS_JOB.SUBMIT(l_job_no,'ssp_pab_pkg.pab_control('||r_affected_pat_Birth.maternity_id||',FALSE);');
2991 l_pat_Birth_count := l_pat_birth_count + 1;
2992 l_count := l_count + 1;
2993 l_tbl_job(l_count).job_no := l_job_no;
2994 l_tbl_job(l_count).person_id := r_affected_pat_Birth.person_id;
2995 l_tbl_job(l_count).absence_id := r_affected_pat_Birth.maternity_id;
2996 if mod(l_count,500) = 0 then
2997 commit;
2998 end if;
2999 --6800788 end
3000
3001
3002 exception
3003 when others then
3004 hr_utility.trace('Error occurred while processing SPP Birth absence: '||r_affected_pat_Birth.maternity_id);
3005 hr_utility.trace('SQL error code: '||SQLCODE);
3006 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3007 hr_utility.trace('Person id: '||r_affected_pat_Birth.person_id);
3008 hr_utility.trace('Paternity Birth start date: '||r_affected_pat_Birth.date_start);
3009 hr_utility.trace('Paternity Birth end date: '||r_affected_pat_Birth.date_end);
3010 hr_utility.trace('Paternity Birth projected start date: '||r_affected_pat_Birth.date_projected_start);
3011 hr_utility.trace('Paternity Birth projected end date: '||r_affected_pat_Birth.date_projected_end);
3012 l_update_error := true;
3013 end;
3014 end loop;
3015 hr_utility.trace('Updated entries for '||l_pat_birth_count||' paternity births');
3016 -- dbms_output.put_line(' Total SPPB absences ='|| l_pat_birth_count);
3017 else
3018 hr_utility.trace('SPP Birth element entries already updated for new tax year');
3019 end if;
3020 --
3021 commit;
3022 --
3023 exception
3024 when e_sppb_rate_not_set then
3025 hr_utility.trace('Warning: SPP Birth rate for new tax year not set');
3026 hr_utility.trace('Unable to proceed with updating SPP Birth entries');
3027 when e_no_new_sppb_entry then
3028 hr_utility.trace('Warning: Unable to locate SPP Birth entry in new tax year');
3029 hr_utility.trace('Entries for SPP Birth absences in new tax year may be non-existent');
3030 hr_utility.trace('Check for stoppages and employee terminations, as well as the start/end date of absences');
3031 when others then
3032 hr_utility.trace('Unexpected error occurred inside SPP Birth update block');
3033 hr_utility.trace('SQL error number: '||SQLCODE);
3034 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3035 rollback to pre_sppb_update_status;
3036 l_update_error := true;
3037 end; /* SPP Birth update block */
3038
3039
3040
3041 p_update_error := l_update_error;
3042
3043 --6800788 begin
3044 hr_utility.trace(' Total Absences to be processed '|| l_count);
3045 --DBMS_OUTPUT.PUT_LINE(' Total Absences to be processed '|| l_count);
3046 for I in 1..l_count
3047 loop
3048 loop
3049 begin
3050
3051 select nvl(FAILURES,0) into l_fail from dba_jobs
3052 where job = l_tbl_job(I).job_no and rownum = 1;
3053
3054 IF l_fail > 0
3055 THEN
3056 l_fail_count := l_fail_count + 1;
3057 DBMS_JOB.REMOVE(l_tbl_job(I).job_no);
3058 commit;
3059 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;
3060 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);
3061 -- 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);
3062 p_update_error := true;
3063 exit;
3064 ELSE
3065 DBMS_LOCK.SLEEP(20);
3066 END IF;
3067 Exception
3068 when no_data_found then
3069 exit;
3070 end;
3071 end loop;
3072 end loop;
3073 hr_utility.trace('Total Absences failed '||l_fail_count);
3074 --DBMS_OUTPUT.PUT_LINE('Total Absences failed '||l_fail_count);
3075 --6800788 end
3076 hr_utility.trace('Update of SSP, SMP, SAP, SPP Adoption and SPP Birth entries complete');
3077 hr_utility.trace('Leaving: '||g_package||'.update_ssp_smp_entries');
3078
3079
3080 exception
3081
3082
3083 when others
3084 then
3085 hr_utility.trace('Unexpected error occurred inside SSP/SMP element entries update procedure');
3086 hr_utility.trace('SQL error number: '||SQLCODE);
3087 hr_utility.trace('SQL error message: '||substr(SQLERRM,1,235));
3088 rollback to pre_update_status;
3089 p_update_error := true;
3090
3091
3092
3093
3094 end update_ssp_smp_entries;
3095
3096 end ssp_smp_support_pkg;
[Home] [Help]