1 package body SSP_PAB_PKG as
2 /*$Header: sppabapi.pkb 120.5.12010000.2 2009/07/27 13:56:00 npannamp ship $
3 +==============================================================================+
4 | Copyright (c) 1994 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +==============================================================================+
8 --
9 Name
10 Statutory Paternity Pay (Birth) Business Process
11 --
12 Purpose
13 To perform calculation of entitlement and payment for PAB purposes
14 --
15 History
16 17 Oct 02 2690305 A Blinko Created from SSP_SMP_PKG
17 10 Jan 03 A Blinko Amended latest_ppp_start_date
18 27 Jan 03 G Butler nocopy fix - added 2nd dummy variable to
19 average_earnings to act as placeholder
20 for OUT param from ssp_ern_ins
21 01 Feb 03 2774903 A Blinko Fixed check_death procedure
22 11 Feb 03 2772479 A Blinko Late Absence Notification stoppage no
23 no longer created
24 24 Oct 03 3208325 A Blinko Replaced hardcoded SATURDAY and
25 SUNDAY references
26 16 Mar 04 3509432 npershad Modified the call to create_stoppage
27 procedure to rectify misleading stoppage
28 date for 'Some work done' stoppage.
29 29 Mar 04 3516539 skhandwa Added condition in the cursor to fetch
30 termination date for latest assignment
31 07 Jun 04 3598838 A Blinko Now only first absence is recognised
32 12 Jul 04 3682122 A Blinko Changes for recalculating lump sum
33 updates
34 14 Mar 05 4226911 npershad Modified the cursor csr_no_of_absences in
35 procedure generate_payments to get a correct
36 count of absence records.
37 18 Oct 05 4670360 KThampan Amended parameter's order of function
38 LATEST_PPP_START_DATE
39 09 Feb 06 4891953 Kthampan Fixed performance bug.
40 23 Aug 06 5482199 KThampan Change from per_people_f and per_assignments_f
41 to per_all_people_f and per_all_assignments_f
42 19 Spe 06 5547703 KThampan Amend pab_control to call generate_payments
43 with insert-mode if absence is > 0 and
44 also change csr_check_if_existing_entries
45 not to reference from per_absence_attendances
46 table
47 26 Jul 09 8692219 npannamp Changed the To_Date function to
48 fnd_date.chardate_to_date function in
49 the cursor csr_existing_entries.
50 */
51 --------------------------------------------------------------------------------
52 g_package varchar2(33) := ' ssp_pab_pkg.'; -- Global package name
53 --
54 cursor csr_absence_details (p_maternity_id in number) is
55 --
56 -- Get details of the initial paternity leave for a paternity
57 --
58 select absence.absence_attendance_id,
59 absence.date_start,
60 nvl (absence.date_end, hr_general.end_of_time) date_end,
61 absence.date_notification,
62 absence.accept_late_notification_flag
63 from
64 per_absence_attendances ABSENCE
65 where absence.maternity_id = p_maternity_id
66 and absence.absence_attendance_id =
67 (select min(paa_tab.absence_attendance_id)
68 from per_absence_attendances paa_tab
69 where paa_tab.maternity_id = absence.maternity_id);
70 --
71 --------------------------------------------------------------------------------
72 cursor csr_personal_details (p_maternity_id in number) is
73 --
74 -- Get details of the paternal person
75 --
76 select maternity.person_id,
77 maternity.due_date,
78 ssp_pab_pkg.qualifying_week (due_date) QW,
79 ssp_pab_pkg.expected_week_of_confinement (due_date) EWC,
80 maternity.maternity_id,
81 maternity.actual_birth_date,
82 maternity.live_birth_flag,
83 maternity.start_date_with_new_employer,
84 maternity.MPP_start_date APP_start_date,
85 maternity.notification_of_birth_date,
86 maternity.start_date_maternity_allowance,
87 maternity.pay_SMP_as_lump_sum pay_PAB_as_lump_sum,
88 person.date_of_death,
89 service.date_start,
90 nvl (service.final_process_date, hr_general.end_of_time) FINAL_PROCESS_DATE
91 from ssp_maternities MATERNITY,
92 per_all_people_f PERSON,
93 per_periods_of_service SERVICE
94 where person.person_id = maternity.person_id
95 and person.person_id = service.person_id
96 and maternity.maternity_id = p_maternity_id
97 and service.date_start between person.effective_start_date
98 and person.effective_end_date
99 and service.date_start = (select max(serv.date_start)
100 from per_periods_of_service serv
101 where serv.person_id = person.person_id);
102 --------------------------------------------------------------------------------
103 person csr_personal_details%rowtype;
104 g_PAB_element csr_PAB_element_details%rowtype;
105 g_PAB_Correction_element csr_PAB_element_details%rowtype;
106 --------------------------------------------------------------------------------
107 l_sunday varchar2(100) := to_char(to_date('07/01/2001','DD/MM/YYYY'),'DAY');
108
109 function QUALIFYING_WEEK (p_due_date in date) return date is
110 --
111 -- Returns the date on which the QW starts
112 --
113 -- QW is the EWC minus the QW weeks from the PAB element
114 --
115 l_QW date;
116 l_PAB_element csr_PAB_element_details%rowtype;
117 --
118 begin
119 --
120 open csr_PAB_element_details (p_due_date,c_PAB_element_name);
121 fetch csr_PAB_element_details into l_PAB_element;
122 close csr_PAB_element_details;
123 --
124 l_QW := expected_week_of_confinement(p_due_date)
125 - l_PAB_element.qualifying_week;
126 --
127 return l_QW;
128 --
129 end QUALIFYING_WEEK;
130 --------------------------------------------------------------------------------
131 Function CONTINUOUS_EMPLOYMENT_DATE (p_due_date in date)
132 return date is
133 --
134 -- The continuous employment start date is the date on which the person must
135 -- have been employed (and continuously from then to the MW) in order to
136 -- qualify for SAP. It is the MW minus the continuous employment period
137 -- specified on the SAP element.
138 -- A woman must have started work on or before the last day of the
139 -- week which starts 182 days (26 weeks) before the last day of the MW. In
140 -- SAP weeks start on Sunday and end on Saturday.
141 --
142 l_PAB_element csr_PAB_element_details%rowtype;
143 l_Continuously_employed_since date;
144 l_saturday varchar2(100) := to_char(to_date('06/01/2001','DD/MM/YYYY'),'DAY');
145 --
146 begin
147 --
148 open csr_PAB_element_details (p_due_date,c_PAB_element_name);
149 fetch csr_PAB_element_details into l_PAB_element;
150 close csr_PAB_element_details;
151 --
152 l_Continuously_employed_since :=
153 next_day(next_day(
154 QUALIFYING_WEEK(p_due_date) ,l_saturday)
155 - (l_PAB_element.continuous_employment_period),l_saturday);
156 --
157 return l_Continuously_employed_since;
158 --
159 end continuous_employment_date;
160 --------------------------------------------------------------------------------
161 procedure get_PAB_correction_element (p_effective_date in date) is
162 --
163 l_proc varchar2(72) := g_package||'get_PAB_correction_element';
164 --
165 procedure check_parameters is
166 begin
167 --
168 hr_utility.trace (l_proc||' p_effective_date = '
169 ||to_char (p_effective_date));
170 --
171 hr_api.mandatory_arg_error (
172 p_api_name => l_proc,
173 p_argument => 'effective_date',
174 p_argument_value=> p_effective_date);
175 end check_parameters;
176 --
177 begin
178 --
179 hr_utility.set_location (l_proc,1);
180 --
181 check_parameters;
182 --
183 open csr_PAB_element_details (p_effective_date,c_PAB_corr_element_name);
184 fetch csr_PAB_element_details into g_PAB_Correction_element;
185 close csr_PAB_element_details;
186 --
187 hr_utility.set_location (l_proc,100);
188 --
189 end get_PAB_correction_element;
190 --------------------------------------------------------------------------------
191 procedure get_PAB_element (p_effective_date in date) is
192 --
193 l_proc varchar2(72) := g_package||'get_PAB_element';
194 --
195 procedure check_parameters is
196 begin
197 --
198 hr_utility.trace (l_proc||' p_effective_date = '
199 ||to_char (p_effective_date));
200 --
201 hr_api.mandatory_arg_error (
202 p_api_name => l_proc,
203 p_argument => 'effective_date',
204 p_argument_value=> p_effective_date);
205 end check_parameters;
206 --
207 begin
208 --
209 hr_utility.set_location (l_proc,1);
210 --
211 check_parameters;
212 --
213 open csr_PAB_element_details (p_effective_date,c_PAB_element_name);
214 fetch csr_PAB_element_details into g_PAB_element;
215 close csr_PAB_element_details;
216 --
217 hr_utility.set_location (l_proc,100);
218 --
219 end get_PAB_element;
220 --------------------------------------------------------------------------------
221 function EARLIEST_PPP_START_DATE (p_birth_date in date) return date is
222 --
223 -- The earliest PPP start date, under normal circumstances is the
224 -- child expected date minus
225 -- the number of weeks specified on the SAP element for earliest SAP start
226 --
227 l_earliest_PPP_start date;
228 l_PAB_element csr_PAB_element_details%rowtype;
229 --
230 begin
231 --
232 open csr_PAB_element_details (p_birth_date,c_PAB_element_name);
233 fetch csr_PAB_element_details into l_PAB_element;
234 close csr_PAB_element_details;
235 --
236 l_earliest_PPP_start := p_birth_date
237 - (l_PAB_element.earliest_start_of_PPP);
238 --
239 return l_earliest_PPP_start;
240 --
241 end earliest_PPP_start_date;
242 --------------------------------------------------------------------------------
243 function LATEST_PPP_START_DATE (p_birth_date in date,
244 p_ewc in date,
245 p_due_date in date) return date is
246 --
247 l_latest_PPP_start date;
248 l_PAB_element csr_PAB_element_details%rowtype;
249 --
250 begin
251 --
252 open csr_PAB_element_details (p_birth_date,c_PAB_element_name);
253 fetch csr_PAB_element_details into l_PAB_element;
254 close csr_PAB_element_details;
255 --
256 if p_birth_date < p_due_date then
257 l_latest_PPP_start := greatest(p_birth_date,p_ewc)
258 + (l_PAB_element.latest_end_of_PPP - 7);
259 else
260 l_latest_PPP_start := p_birth_date + (l_PAB_element.latest_end_of_PPP - 7);
261 end if;
262 --
263 return l_latest_PPP_start;
264 --
265 end latest_ppp_start_date;
266 --------------------------------------------------------------------------------
267 function expected_week_of_confinement (p_due_date in date) return date is
268 --
269 l_ewc date;
270 --
271 begin
272 --
273 l_ewc := (next_day(p_due_date,l_sunday)-7);
274 --
275 return l_ewc;
276 --
277 end expected_week_of_confinement;
278 --------------------------------------------------------------------------------
279 function MATERNITY_RECORD_EXISTS (p_person_id in number) return boolean is
280 --
281 cursor maternity_record is
282 select 1
283 from ssp_maternities
284 where person_id = p_person_id;
285 --
286 l_dummy number (1);
287 l_maternity_record_exists boolean;
288 --
289 begin
290 --
291 open maternity_record;
292 fetch maternity_record into l_dummy;
293 l_maternity_record_exists := maternity_record%found;
294 close maternity_record;
295 --
296 return l_maternity_record_exists;
297 --
298 end maternity_record_exists;
299 --------------------------------------------------------------------------------
300 function AVERAGE_EARNINGS return number is
301 --
302 l_average_earnings number := null;
303 l_effective_date date := null;
304 l_dummy number;
305 l_dummy2 number; -- nocopy fix, placeholder variable
306 l_user_entered varchar2(30) := 'N'; --DFoster 1304683
307 l_absence_category varchar2(30) := 'GB_PAT_BIRTH'; --DFoster 1304683
308 l_payment_periods number := null; --DFoster 1304683
309 l_proc varchar2(72) := g_package||'average_earnings';
310 --
311 cursor csr_average_earnings is
312 select average_earnings_amount
313 from ssp_earnings_calculations
314 where person_id = person.person_id
315 and effective_date = l_effective_date;
316 --
317 begin
318 --
319 hr_utility.set_location ('Entering '||l_proc,1);
320 --
321 l_effective_date := greatest(person.QW, person.date_start);
322 --
323 open csr_average_earnings;
324 fetch csr_average_earnings into l_average_earnings;
325 --
326 if csr_average_earnings%notfound
327 then
328 ssp_ern_ins.ins (p_earnings_calculations_id => l_dummy,
329 p_object_version_number => l_dummy2,
330 p_person_id => person.person_id,
331 p_effective_date => l_effective_date,
332 p_average_earnings_amount => l_average_earnings,
333 p_user_entered => l_user_entered, --DFoster 1304683
334 p_absence_category => l_absence_category, --DFoster 1304683
335 p_payment_periods => l_payment_periods); --DFoster 1304683
336 end if;
337 --
338 close csr_average_earnings;
339 --
340 hr_utility.set_location ('Leaving '||l_proc,10);
341 --
342 return l_average_earnings;
343 --
344 end average_earnings;
345 --------------------------------------------------------------------------------
346 function entitled_to_PAB (p_maternity_id in number) return boolean is
347 --
348 -- See header for description of this procedure.
349 --
350 no_prima_facia_entitlement exception;
351 invalid_absence_date exception;
352 l_work_start_date date := hr_general.end_of_time;
353 stoppage_end_date date := null;
354 no_of_absence_periods integer := 0;
355 l_proc varchar2(72) := g_package||'entitled_to_PAB';
356 l_keep_stoppages boolean default FALSE;
357 --
358 cursor csr_no_of_absences is
359 --
360 select count (*)
361 from per_absence_attendances
362 where person_id = person.person_id
363 and maternity_id = p_maternity_id
364 and absence_attendance_id =
365 (select min(paa_tab.absence_attendance_id)
366 from per_absence_attendances paa_tab
367 where paa_tab.maternity_id = p_maternity_id);
368 --
369 -- returns entries associated with a maternity_id.
370 cursor csr_check_if_existing_entries is
371 --
372 select /*+ ORDERED use_nl(paa,paaf,etype,entry) */
373 entry.element_entry_id,
374 entry.effective_start_date
375 from per_all_assignments_f PAAF,
376 pay_element_types_f ETYPE,
377 pay_element_entries_f ENTRY
378 where PAAF.person_id = person.person_id
379 and ETYPE.element_name = c_PAB_element_name
380 and ETYPE.legislation_code = 'GB'
381 and ENTRY.element_type_id = ETYPE.element_type_id
382 and ENTRY.creator_type = c_PAB_creator_type
383 and ENTRY.creator_id = p_maternity_id
384 and ENTRY.assignment_id = PAAF.assignment_id
385 and not exists (
386 --
387 -- Do not select entries which have already had reversal action
388 -- taken against them because they are effectively cancelled out.
389 --
390 select 1
391 from pay_element_entries_f ENTRY2
392 where entry.element_entry_id= entry2.target_entry_id
393 and entry.assignment_id = entry2.assignment_id)
394 --
395 and not exists (
396 --
397 -- Do not select reversal entries
398 --
399 select 1
400 from pay_element_links_f LINK,
401 pay_element_types_f TYPE
402 where link.element_link_id = entry.element_link_id
403 and entry.effective_start_date between link.effective_start_date and link.effective_end_date
404 and link.element_type_id = type.element_type_id
405 and link.effective_start_date between type.effective_start_date and type.effective_end_date
406 and type.element_name = c_PAB_corr_element_name);
407 --
408 l_existing_entries csr_check_if_existing_entries%rowtype;
409 --
410 procedure create_stoppage (
411 --
412 -- Create a stoppage of payment for PAB
413 --
414 p_reason in varchar2,
415 p_withhold_from in date,
416 p_withhold_to in date default null) is
417 --
418 l_proc varchar2(72) := g_package||'create_stoppage';
419 l_dummy number;
420 l_reason_id number;
421 --
422 procedure check_parameters is
423 --
424 begin
425 --
426 hr_utility.trace (l_proc||' p_reason = '||p_reason);
427 hr_utility.trace (l_proc||' withhold from '||to_char (p_withhold_from));
428 hr_utility.trace (l_proc||' withhold to '||to_char (p_withhold_to));
429 --
430 hr_api.mandatory_arg_error (
431 p_api_name => l_proc,
432 p_argument => 'reason',
433 p_argument_value=> p_reason);
434 --
435 hr_api.mandatory_arg_error (
436 p_api_name => l_proc,
437 p_argument => 'withhold_from',
438 p_argument_value=> p_withhold_from);
439 --
440 end check_parameters;
441 --
442 begin
443 --
444 hr_utility.set_location (l_proc,1);
445 --
446 check_parameters;
447 --
448 l_reason_id := ssp_smp_support_pkg.withholding_reason_id (
449 g_PAB_element.element_type_id,
450 p_reason);
451 --
452 if not ssp_smp_support_pkg.stoppage_overridden (
453 p_maternity_id => p_maternity_id,
454 p_reason_id => l_reason_id)
455 then
456 --
457 -- Only create the stoppage if there is not already a stoppage marked
458 -- as over--ridden. Thus, overriding a stoppage effectively blocks that
459 -- reason being used to withhold payment for this person.
460 --
461 ssp_stp_ins.ins (p_withhold_from => p_withhold_from,
462 p_withhold_to => p_withhold_to,
463 p_stoppage_id => l_dummy,
464 p_object_version_number => l_dummy,
465 p_maternity_id => p_maternity_id,
466 p_user_entered => 'N',
467 p_reason_id => l_reason_id);
468 else
469 hr_utility.trace (l_proc||' Stoppage is overridden');
470 end if;
471 --
472 hr_utility.set_location (l_proc,100);
473 --
474 end create_stoppage;
475 --
476 procedure remove_stoppages is
477 --
478 -- Remove old system, non-overridden stoppages
479 --
480 cursor csr_stoppages is
481 select stoppage_id
482 from ssp_stoppages
483 where user_entered <>'Y'
484 and override_stoppage <> 'Y'
485 and maternity_id = p_maternity_id;
486 --
487 l_dummy number;
488 l_proc varchar2 (72) := g_package||'remove_stoppages';
489 --
490 begin
491 --
492 hr_utility.set_location (l_proc,1);
493 --
494 for each_stoppage in csr_stoppages LOOP
495 ssp_stp_del.del (p_stoppage_id => each_stoppage.stoppage_id,
496 p_object_version_number => l_dummy);
497 end loop;
498 --
499 hr_utility.set_location (l_proc,100);
500 --
501 end remove_stoppages;
502 --
503 procedure check_employment_end is
504
505 cursor csr_termination_date is
506 select actual_termination_date
507 from per_periods_of_service
508 where person_id = person.person_id
509 -- 3516539 Added condition to obtain termination date for latest assignment
510 and period_of_service_id = (select max(period_of_service_id)
511 from per_periods_of_service ppos
512 where ppos.person_id = person.person_id);
513 --
514 l_termination_date date := null;
515 l_proc varchar2(72) := g_package||'check_employment_end';
516 --
517 begin
518 --
519 hr_utility.set_location (l_proc,1);
520 --
521 open csr_termination_date;
522 fetch csr_termination_date into l_termination_date;
523 close csr_termination_date;
524 --
525 if l_termination_date < person.actual_birth_date then
526 --
527 -- Stop all PAB payment for the Birth because the person left
528 -- before the PPP start date.
529 --
530 create_stoppage (p_withhold_from => person.actual_birth_date,
531 p_reason => 'Not employed on Birth Date');
532 end if;
533 --
534 hr_utility.set_location (l_proc,100);
535 --
536 end check_employment_end;
537 --
538 procedure check_continuity_rule is
539 --
540 -- Check that the person has the right amount of continuous service to
541 -- qualify for SAP
542 --
543 cursor period_of_service is
544 --
545 -- Check the period of service length up to the MW start date
546 --
547 select 1
548 from per_periods_of_service
549 where person_id = person.person_id
550 and date_start <= ssp_pab_pkg.continuous_employment_date(person.due_date)
551 and nvl (actual_termination_date, hr_general.end_of_time) >= person.QW;
552 --
553 l_dummy number (1);
554 l_proc varchar2 (72) := g_package||'check_continuity_rule';
555 --
556 begin
557 --
558 hr_utility.set_location (l_proc,1);
559 --
560 open period_of_service;
561 fetch period_of_service into l_dummy;
562 --
563 if period_of_service%notfound then
564 --
565 -- Stop all SAP payment for the Adoption because the person has not
566 -- been continuously employed for long enough.
567 --
568 create_stoppage (p_withhold_from => person.APP_start_date,
569 p_reason => 'Insufficient employment');
570 end if;
571 --
572 close period_of_service;
573 --
574 hr_utility.set_location (l_proc,100);
575 --
576 end check_continuity_rule;
577 --
578 procedure check_start_date is
579
580 l_proc varchar2 (72) := g_package||'check_start_date';
581 --
582 l_latest_PPP_start date;
583 l_PAB_element csr_PAB_element_details%rowtype;
584 --
585 begin
586 --
587 hr_utility.set_location (l_proc,1);
588 --
589 open csr_PAB_element_details (person.actual_birth_date,c_PAB_element_name);
590 fetch csr_PAB_element_details into l_PAB_element;
591 close csr_PAB_element_details;
592 --
593 if person.app_start_date > greatest(person.ewc,person.actual_birth_date)
594 + (l_PAB_element.latest_end_of_PPP - 14)
595 then
596
597 create_stoppage (p_withhold_from => person.APP_start_date + 7,
598 p_reason => 'Max PPP Period Exceeded');
599
600 end if;
601 --
602 hr_utility.set_location (l_proc,100);
603 --
604 end check_start_date;
605
606 procedure check_stillbirth is
607 --
608 -- Check the pregnancy condition for qualification for PAB
609 --
610 l_proc varchar2 (72) := g_package||'check_stillbirth';
611 --
612 begin
613 --
614 hr_utility.set_location (l_proc,1);
615 --
616 -- Woman must be still pregnant, have had a live birth, or have had a
617 -- stillbirth after the threshhold week to be eligible for PAB
618 --
619 if NOT (person.actual_birth_date is null
620 or person.live_birth_flag = 'Y'
621 or person.actual_birth_date > person.EWC
622 - g_PAB_element.stillbirth_threshold_week)
623 then
624 --
625 -- Stop SMP payment from the start of the week in which the absence
626 -- started.
627 --
628 create_stoppage (p_withhold_from => person.APP_start_date,
629 p_reason => 'Stillbirth');
630 end if;
631 --
632 hr_utility.set_location (l_proc,100);
633 --
634 end check_stillbirth;
635 --
636 procedure check_new_employer is
637 --
638 -- Check the person has not been employed by a new employer after the
639 -- child placement
640 --
641 l_proc varchar2 (72) := g_package||'check_new_employer';
642 --
643 begin
644 --
645 hr_utility.set_location (l_proc,1);
646 --
647 if person.start_date_with_new_employer >= person.actual_birth_date then
648 --
649 -- Stop SAP payment from the start of the week in which the person
650 -- started work for a new employer after the placement of her child.
651 --
652 create_stoppage (p_withhold_from => ssp_smp_support_pkg.start_of_week
653 (person.start_date_with_new_employer),
654 p_reason => 'Worked for another employer');
655 end if;
656 --
657 hr_utility.set_location (l_proc,100);
658 --
659 end check_new_employer;
660 --
661 procedure check_maternity_allowance is
662 --
663 -- SAP ceases when SMA starts.
664 --
665 l_proc varchar2 (72) := g_package||'check_maternity_allowance';
666 --
667 begin
668 --
669 hr_utility.set_location (l_proc,1);
670 --
671 if person.start_date_maternity_allowance is not null then
672 --
673 -- Stop SAP payment from the start of the week in which SMA was first
674 -- paid.
675 --
676 create_stoppage (p_withhold_from => ssp_smp_support_pkg.start_of_week
677 (person.start_date_maternity_allowance),
678 p_reason => 'Employee is receiving SMA');
679 end if;
680 --
681 hr_utility.set_location (l_proc,100);
682 --
683 end check_maternity_allowance;
684 --
685 procedure check_death is
686 --
687 -- PAB ceases after the death of the person.
688 --
689 l_proc varchar2 (72) := g_package||'check_death';
690 --
691 cursor csr_check_death is
692 select ppf.date_of_death
693 from per_all_people_f ppf
694 where ppf.person_id = person.person_id
695 and ppf.date_of_death is not null;
696 --
697 l_death_date date;
698 --
699 begin
700 --
701 hr_utility.set_location (l_proc,1);
702 --
703 -- PAB ceases on the Saturday following death
704 --
705 l_death_date := null;
706 --
707 open csr_check_death;
708 fetch csr_check_death into l_death_date;
709 close csr_check_death;
710 --
711 if l_death_date is not null then
712 create_stoppage (p_withhold_from => next_day (l_death_date,
713 l_sunday),
714 p_reason => 'Employee died');
715 end if;
716 --
717 hr_utility.set_location (l_proc,100);
718 --
719 end check_death;
720 --
721 procedure check_average_earnings is
722 --
723 -- The person must earn enough to qualify for SAP
724 --
725 l_proc varchar2 (72) := g_package||'check_average_earnings';
726 l_average_earnings number := average_earnings;
727 l_reason_for_no_earnings varchar2 (80) := null;
728 earnings_not_derived exception;
729 --
730 begin
731 --
732 hr_utility.set_location (l_proc,1);
733 --
734 if l_average_earnings = 0
735 then
736 --
737 -- If the average earnings figure returned is zero then check that
738 -- no error message was set. Error messages will be set for system-
739 -- generated average earnings when the earnings could not be derived
740 -- for some reason, but to allow this procedure to continue, no error
741 -- will be raised.
742 --
743 l_reason_for_no_earnings:=ssp_smp_support_pkg.average_earnings_error;
744 --
745 if l_reason_for_no_earnings is not null then
746 create_stoppage (p_withhold_from => person.aPP_start_date,
747 p_reason => l_reason_for_no_earnings);
748 --
749 raise earnings_not_derived;
750 end if;
751 end if;
752 --
753 if l_average_earnings
754 < ssp_smp_support_pkg.NI_Lower_Earnings_Limit (person.QW)
755 then
756 --
757 -- Stop SAP payment from the APP start date
758 --
759 create_stoppage (p_withhold_from => person.APP_start_date,
760 p_reason => 'Earnings too low');
761 end if;
762 --
763 hr_utility.set_location (l_proc,100);
764 --
765 exception
766 --
767 when earnings_not_derived then
768 --
769 -- Exit silently from this procedure
770 --
771 hr_utility.trace (l_proc||' Earnings not derived');
772 null;
773 --
774 end check_average_earnings;
775 --
776 procedure check_birth_confirmation is
777 --
778 -- Check that confirmation of birth was received in good time.
779 --
780 l_proc varchar2 (72) := g_package||'check_birth_confirmation';
781 --
782 begin
783 --
784 hr_utility.set_location (l_proc,1);
785 --
786 hr_utility.set_location (l_proc,100);
787 --
788 end check_birth_confirmation;
789 --
790 procedure check_parameters is
791 --
792 begin
793 --
794 hr_utility.trace (l_proc||' p_maternity_id = '
795 ||to_char (p_maternity_id));
796 --
797 hr_api.mandatory_arg_error (
798 p_api_name => l_proc,
799 p_argument => 'maternity_id',
800 p_argument_value=> p_maternity_id);
801 --
802 end check_parameters;
803 --
804 begin
805 --
806 hr_utility.set_location (l_proc,1);
807 --
808 check_parameters;
809 --
810 -- Get the details of the woman and her maternity.
811 --
812 open csr_personal_details (p_maternity_id);
813 fetch csr_personal_details into person;
814 --
815 if csr_personal_details%notfound
816 then
817 --
818 -- If no maternity record exists then there can be no entitlement to SAP
819 --
820 close csr_personal_details;
821 --
822 hr_utility.trace (l_proc||' Person has no maternity record - exiting');
823 --
824 raise no_prima_facia_entitlement;
825 end if;
826 --
827 close csr_personal_details;
828 --
829 if person.APP_start_date is null then
830 --
831 -- If the APP has not started then there is no entitlement to SAP.
832 --
833 hr_utility.trace (l_proc||' Person has no APP start date - exiting');
834 --
835 raise no_prima_facia_entitlement;
836 end if;
837 --
838 -- Count how many absences there are for the maternity.
839 --
840 open csr_no_of_absences;
841 fetch csr_no_of_absences into no_of_absence_periods;
842 close csr_no_of_absences;
843 --
844 if no_of_absence_periods = 0
845 then
846 --
847 -- check if entries exist despite there being no absence
848 --
849 open csr_check_if_existing_entries;
850 fetch csr_check_if_existing_entries into l_existing_entries;
851 --
852 if csr_check_if_existing_entries%NOTFOUND
853 then
854 hr_utility.trace (l_proc||' Person has not stopped work - exiting');
855 raise no_prima_facia_entitlement;
856 end if;
857 --
858 -- if entries are found then the absence has been deleted and entries remain
859 -- that must be dealt with
860 --
861 while csr_check_if_existing_entries%FOUND LOOP
862 fetch csr_check_if_existing_entries into l_existing_entries;
863 end loop;
864 --
865 close csr_check_if_existing_entries;
866 l_keep_stoppages := TRUE;
867 end if;
868 --
869 -- Having established a prima facia entitlement to SAP, perform checks which
870 -- may lead to creation of stoppages for particular periods.
871 --
872 hr_utility.set_location ('ssp_smp_pkg.entitled_to_PAB',2);
873 --
874 -- Get the SAP legislative parameters.
875 --
876 get_PAB_element (person.due_date);
877 --
878 -- Clear stoppages created by previous calculations of SAP but if an absence
879 -- is being deleted, then must keep stoppages so that when later comparison of
880 -- old_entry and hypothetical_entry is done then stoppages are still there.
881 --
882 if not l_keep_stoppages then
883 remove_stoppages;
884 end if;
885 --
886 for absence in csr_absence_details (p_maternity_id) LOOP
887 --
888 -- Check that sufficient notification of absence was given
889 --
890 -- if notification of absence was later than the allowed date
891 -- and there was no acceptable reason for the delay
892 -- or notification of absence was later than the extended allowable date
893 --
894 if (absence.date_notification > absence.date_start
895 - g_PAB_element.MPP_notice_requirement
896 and absence.accept_late_notification_flag = 'N')
897 or (absence.date_notification > person.actual_birth_date
898 + g_PAB_element.MPP_notice_requirement)
899 then
900 --
901 -- Stop SAP payment from the start of the week in which the absence
902 -- starts, to the end of the notice period
903 --
904 stoppage_end_date := g_PAB_element.MPP_notice_requirement
905 + absence.date_start - 1;
906 --
907 /* Bug 2772479 - Stoppage no longer required but may be reintroduced
908
909 create_stoppage (
910 p_withhold_from => ssp_smp_support_pkg.start_of_week
911 (absence.date_start),
912 p_withhold_to => ssp_smp_support_pkg.end_of_week (stoppage_end_date),
913 p_reason => 'Late absence notification');
914 */
915 end if;
916 --
917 hr_utility.set_location ('ssp_sap_pkg.entitled_to_SaP',3);
918 --
919 -- Check for any work done during the APP.
920 --
921 -- Check if this is the first absence period in the APP
922 -- and the absence starts after the APP start date
923 if csr_absence_details%rowcount = 1
924 and absence.date_start > person.APP_start_date
925 then
926 create_stoppage (p_reason => 'Some work was done',
927 p_withhold_from => person.APP_start_date,
928 p_withhold_to => ssp_smp_support_pkg.end_of_week
929 (absence.date_start -1));
930 end if;
931 --
932 -- If this is the last absence period in the MPP
933 -- and the absence period ends before the end of the MPP
934 --
935 if csr_absence_details%rowcount = no_of_absence_periods
936 and absence.date_end < (g_PAB_element.maximum_PPP_weeks * 7)
937 + person.APP_start_date
938 then
939
940
941 --Commented code for Bug Fix 3509432
942 /* create_stoppage (p_reason => 'Some work was done',
943 p_withhold_from => ssp_smp_support_pkg.start_of_week
944 (absence.date_end+1));*/
945
946 --Bug 3509432 start
947 --Only one week is paid, if absence is for > 1 week but < 2 weeks
948 if (absence.date_end - absence.date_start) + 1 > 7 and (absence.date_end - absence.date_start) + 1 < 14 then
949
950 create_stoppage (p_reason => 'Some work was done',
951 p_withhold_from => person.APP_start_date + 7);
952
953 --no weeks are paid,if absence < 1 week
954 elsif (absence.date_end - absence.date_start) + 1 < 7 then
955
956 create_stoppage (p_reason => 'Some work was done',
957 p_withhold_from => person.APP_start_date);
958
959 elsif (absence.date_end - absence.date_start) + 1 = 7 then
960
961 create_stoppage (p_reason => 'Some work was done',
962 p_withhold_from => person.APP_start_date + 7);
963
964 --no stoppage created if absence is equal to 2 weeks
965 elsif absence.date_start is not null and absence.date_end is not null then
966 if (absence.date_end - absence.date_start) + 1 = 14 then
967 null;
968 end if;
969 end if;
970
971 --Bug 3509432 End
972 elsif
973 -- there is a period of work between two absences
974 l_work_start_date < absence.date_start
975 and l_work_start_date < (g_PAB_element.maximum_PPP_weeks * 7)
976 + person.APP_start_date
977 then
978 create_stoppage (p_reason => 'Some work was done',
979 p_withhold_from => ssp_smp_support_pkg.start_of_week
980 (l_work_start_date),
981 p_withhold_to => ssp_smp_support_pkg.end_of_week
982 (absence.date_start -1));
983 --
984 if absence.date_end <> hr_general.end_of_time
985 then
986 l_work_start_date := absence.date_end + 1;
987 else
988 --
989 -- This is not the last absence in the maternity but it has no end date.
990 --
991 hr_utility.trace (l_proc||' ERROR: Invalid null absence end date');
992 --
993 raise invalid_absence_date;
994 end if;
995 end if;
996 end loop;
997 --
998 check_continuity_rule;
999 check_start_date;
1000 check_employment_end;
1001 check_stillbirth;
1002 check_new_employer;
1003 check_maternity_allowance;
1004 check_death;
1005 check_birth_confirmation;
1006 check_average_earnings;
1007 --
1008 -- If we get this far the person is entitled to SAP (though stoppages may apply)
1009 --
1010 return TRUE;
1011 --
1012 exception
1013 --
1014 when invalid_absence_date then
1015 fnd_message.set_name ('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1016 fnd_message.set_token ('PROCEDURE','ssp_smp_pkg.entitled_to_PAB');
1017 fnd_message.set_token ('STEP','3');
1018 --
1019 when no_prima_facia_entitlement then
1020 --
1021 -- Exit silently; this will allow us to call this procedure with impunity
1022 -- from absences which are not maternity absences (e.g. via a row trigger)
1023 --
1024 return FALSE;
1025 --
1026 end entitled_to_PAB;
1027 --------------------------------------------------------------------------------
1028 procedure generate_payments(p_maternity_id in number,
1029 p_deleting in boolean default FALSE) is
1030 --
1031 type date_table is table of date index by binary_integer;
1032 type number_table is table of number index by binary_integer;
1033 type varchar_table is table of varchar2 (80) index by binary_integer;
1034 l_proc varchar2(72) := g_package||'generate_payments';
1035 --
1036 type PAB_entry is record (
1037 --
1038 element_entry_id number_table,
1039 element_link_id number_table,
1040 assignment_id number_table,
1041 effective_start_date date_table,
1042 effective_end_date date_table,
1043 amount number_table,
1044 rate varchar_table,
1045 week_commencing date_table,
1046 recoverable_amount number_table,
1047 stopped varchar_table,
1048 dealt_with varchar_table);
1049 --
1050 --
1051 -- A store for all the SAP entries that potentially may be
1052 -- granted to the person.
1053 --
1054 hypothetical_entry PAB_entry;
1055 --
1056 -- A tally of the number of weeks of the APP which are subject to stoppages.
1057 --
1058 l_stopped_weeks number := 0;
1059 --
1060 l_high_rate varchar2 (80) := null;
1061 l_low_rate varchar2 (80) := null;
1062 -- RT entries prob
1063 l_no_of_absence_periods integer := 0;
1064 --
1065 -- p_deleting passed into save_hypothetical_entries, so that logic can be
1066 -- dealt with for deleted absences.
1067 --
1068 procedure save_hypothetical_entries (p_deleting in boolean default false) is
1069 --
1070 -- Having generated the potential SAP entries, reconcile them with any
1071 -- previously granted entries for the same maternity.
1072 --
1073 cursor csr_existing_entries is
1074 --
1075 -- Get all entries and entry values for the Adoption
1076 --
1077 -- Decode the rate code to give the meaning using local variables
1078 -- populated earlier by Calculate_correct_SAP_rate
1079 -- these can then be passed directly into the hr_entry_api's and
1080 -- simplifies comparison with hypo entries
1081 --
1082 select entry.element_entry_id,
1083 entry.element_link_id,
1084 entry.assignment_id,
1085 entry.effective_start_date,
1086 entry.effective_end_date,
1087 -- if in future we get two different rates then a decode can be added here
1088 l_high_rate RATE,
1089 /* Bug Fix 8692219 begin
1090 to_date (ssp_smp_support_pkg.value
1091 (entry.element_entry_id,
1092 ssp_sap_pkg.c_week_commencing_name),
1093 'DD-MON-YYYY') WEEK_COMMENCING,
1094 */
1095 fnd_date.chardate_to_date(ssp_smp_support_pkg.value
1096 (entry.element_entry_id,
1097 ssp_sap_pkg.c_week_commencing_name)) WEEK_COMMENCING,
1098 -- Bug Fix 8692219 End
1099 to_number(ssp_smp_support_pkg.value (entry.element_entry_id,
1100 ssp_sap_pkg.c_amount_name)) AMOUNT,
1101 to_number(ssp_smp_support_pkg.value (entry.element_entry_id,
1102 ssp_sap_pkg.c_recoverable_amount_name)) RECOVERABLE_AMOUNT
1103 from pay_element_entries_f ENTRY,
1104 per_all_assignments_f asg
1105 where creator_type = c_PAB_creator_type
1106 and creator_id = p_maternity_id
1107 and asg.person_id = person.person_id
1108 and asg.assignment_id = entry.assignment_id
1109 and entry.effective_start_date between asg.effective_start_date
1110 and asg.effective_end_date
1111 and not exists (
1112 --
1113 -- Do not select entries which have already had reversal action taken
1114 -- against them because they are effectively cancelled out.
1115 --
1116 select 1
1117 from pay_element_entries_f ENTRY2
1118 where entry.element_entry_id= entry2.target_entry_id
1119 and entry.assignment_id = entry2.assignment_id)
1120 --
1121 and not exists (
1122 --
1123 -- Do not select reversal entries
1124 --
1125 select 1
1126 from pay_element_links_f LINK,
1127 pay_element_types_f TYPE
1128 where link.element_link_id = entry.element_link_id
1129 and entry.effective_start_date between link.effective_start_date
1130 and link.effective_end_date
1131 and link.element_type_id = type.element_type_id
1132 and link.effective_start_date between type.effective_start_date
1133 and type.effective_end_date
1134 and type.element_name = c_PAB_corr_element_name);
1135 --
1136 cursor csr_no_of_absences is
1137 --
1138 select count (*)
1139 from per_absence_attendances
1140 where person_id = person.person_id
1141 and maternity_id = p_maternity_id
1142 and absence_attendance_id =
1143 (select min(paa_tab.absence_attendance_id)
1144 from per_absence_attendances paa_tab
1145 where paa_tab.maternity_id = p_maternity_id); --Bug fix 4226911
1146
1147
1148 --
1149 l_ins_corr_ele boolean;
1150 l_dummy number;
1151 Entry_number integer;
1152 l_ern_calc_id number;
1153 l_ob_v_no number;
1154 l_new_ob_v_no number;
1155 l_proc varchar2 (72) := g_package||'save_hypothetical_entries';
1156 --
1157
1158
1159 -- This procedure was a private procedure in the function entitled_to_sap. I
1160 -- wanted to call it within this procedure (generate_payments) aswell, so
1161 -- instead of making it a public procedure I have copied the procedure to here.
1162 --
1163 procedure remove_stoppages is
1164 --
1165 -- Remove old system, non-overridden stoppages
1166 --
1167 cursor csr_stoppages is
1168 select stoppage_id
1169 from ssp_stoppages
1170 where user_entered <>'Y'
1171 and override_stoppage <> 'Y'
1172 and maternity_id = p_maternity_id;
1173 --
1174 l_dummy number;
1175 l_proc varchar2 (72) := g_package||'.remove_stoppages';
1176 --
1177 begin
1178 --
1179 hr_utility.set_location (l_proc,1);
1180 --
1181 for each_stoppage in csr_stoppages LOOP
1182 ssp_stp_del.del (p_stoppage_id => each_stoppage.stoppage_id,
1183 p_object_version_number => l_dummy);
1184
1185 end loop;
1186 --
1187 hr_utility.set_location (l_proc,100);
1188 --
1189 end remove_stoppages;
1190 --
1191 begin
1192 --
1193 hr_utility.set_location('Entering: '||l_proc,10);
1194 --
1195 get_PAB_correction_element (person.due_date);
1196 --
1197 -- Check each existing SaP entry in turn against all the potential new ones.
1198 --
1199 <<OLD_ENTRIES>>
1200 for old_entry in csr_existing_entries
1201 LOOP
1202 --First loop through the hypothetical entries to see if there is one
1203 --which covers the same week as the old entry and is not subject to
1204 --a stoppage. If there isn't one, invalidate the old entry.
1205 --Assume we don't need to correct the entry until we discover otherwise:
1206 --
1207 l_ins_corr_ele := FALSE;
1208 begin
1209 entry_number := 0;
1210 if p_deleting then
1211 raise no_data_found; -- enter exception handler
1212 end if;
1213 LOOP
1214 entry_number := entry_number +1;
1215 -- Exit the loop when we find a hypothetical entry covering the
1216 -- same week as the old entry, which is not subject to a stoppage.
1217 -- If no such match is found, then we will reach the end of the
1218 -- pl/sql table and attempt to read beyond the existing rows; this
1219 -- will cause us to enter the exception handler and indicate that
1220 -- no match was found.
1221 exit when ((old_entry.week_commencing
1222 = hypothetical_entry.week_commencing (entry_number)
1223 and not hypothetical_entry.stopped (entry_number) = 'TRUE'
1224 and ssp_smp_pkg.g_smp_update = 'N')
1225 or (old_entry.effective_start_date
1226 = hypothetical_entry.effective_start_date (entry_number)
1227 and old_entry.week_commencing
1228 = hypothetical_entry.week_commencing (entry_number)
1229 and not hypothetical_entry.stopped (entry_number) = 'TRUE'
1230 and ssp_smp_pkg.g_smp_update = 'Y'));
1231 end loop;
1232 hr_utility.trace (l_proc||' Old entry / Hypo entry time Match with values:');
1233 hr_utility.trace (l_proc||' Rate: ' ||old_entry.rate||' / '
1234 ||hypothetical_entry.rate (Entry_number));
1235 hr_utility.trace (l_proc||' Amount: '
1236 ||old_entry.amount||' / '
1237 ||hypothetical_entry.amount (entry_number));
1238 hr_utility.trace (l_proc||' Recoverable: '
1239 ||old_entry.recoverable_amount||' / '
1240 ||hypothetical_entry.recoverable_amount (entry_number));
1241 hr_utility.trace (l_proc||' Week Comm: '
1242 ||hypothetical_entry.week_commencing (entry_number) );
1243 --A hypo entry covers the same week as the old one
1244 if old_entry.rate = hypothetical_entry.rate (entry_number)
1245 and old_entry.amount = hypothetical_entry.amount(entry_number)
1246 and old_entry.recoverable_amount
1247 = hypothetical_entry.recoverable_amount (entry_number)
1248 then
1249 -- the hypo entry has the same values as the old one
1250 -- don't create a correction element.
1251 -- don't create a new entry
1252 hypothetical_entry.dealt_with (entry_number) := 'TRUE';
1253 hr_utility.trace (l_proc||' leave unchanged');
1254 else
1255 if ssp_smp_support_pkg.entry_already_processed
1256 (old_entry.element_entry_id)
1257 then
1258 l_ins_corr_ele := TRUE;
1259 hr_utility.trace (l_proc||' processed - correct it');
1260 else
1261 -- update old entry
1262 hr_utility.trace (l_proc||' unprocessed - update it');
1263 hr_entry_api.update_element_entry (
1264 p_dt_update_mode => 'CORRECTION',
1265 p_session_date => old_entry.effective_start_date,
1266 p_element_entry_id => old_entry.element_entry_id,
1267 p_input_value_id1 => g_PAB_element.rate_id,
1268 p_input_value_id2 => g_PAB_element.amount_id,
1269 p_input_value_id3 => g_PAB_element.recoverable_amount_id,
1270 p_entry_value1=> hypothetical_entry.rate (entry_number),
1271 p_entry_value2=> hypothetical_entry.amount(entry_number),
1272 p_entry_value3=>
1273 hypothetical_entry.recoverable_amount (entry_number));
1274 --
1275 --prevent insertion of new entry
1276 --
1277 hypothetical_entry.dealt_with (entry_number) := 'TRUE';
1278 end if;
1279 end if;
1280 exception
1281 when no_data_found then
1282 -- There was no new entry which exactly matched the old entry.
1283 -- or we are deleting.
1284 entry_number := null;
1285 hr_utility.trace (l_proc||' No Old entry - Hypo entry time Match');
1286 hr_utility.trace (l_proc||' or p_deleting is true');
1287 hr_utility.trace (l_proc||' Old entry values:');
1288 hr_utility.trace (l_proc||' Rate: '||old_entry.rate);
1289 hr_utility.trace (l_proc||' Amount: '||old_entry.amount);
1290 hr_utility.trace (l_proc||' Recoverable: '
1291 ||old_entry.recoverable_amount);
1292 if ssp_smp_support_pkg.entry_already_processed
1293 (old_entry.element_entry_id)
1294 then l_ins_corr_ele := TRUE;
1295 hr_utility.trace (l_proc||' Old entry already processed');
1296 else
1297 hr_utility.trace (l_proc||' Old entry NOT already processed');
1298 --Old entry not already processed so delete it
1299 hr_entry_api.delete_element_entry (
1300 p_dt_delete_mode => 'ZAP',
1301 p_session_date => old_entry.effective_start_date,
1302 p_element_entry_id => old_entry.element_entry_id);
1303 end if;
1304 end;
1305 if l_ins_corr_ele
1306 then
1307 -- Create a correction element to reverse the old entry. Then create a
1308 -- brand new entry with the correct values.
1309 --
1310 hr_utility.trace (l_proc ||
1311 ' Inserting CORRECTION entry for week commencing ' ||
1312 to_char (old_entry.week_commencing));
1313 hr_utility.trace (l_proc||' Old value / New value:');
1314 if entry_number is null then
1315 hr_utility.trace (l_proc||' Rate: '
1316 ||old_entry.rate||' / NA');
1317 hr_utility.trace (l_proc||' Amount: '
1318 ||old_entry.amount||' / NA');
1319 hr_utility.trace (l_proc||' Recoverable: '
1320 ||old_entry.recoverable_amount||' / NA');
1321 else
1322 hr_utility.trace (l_proc||' Rate: '
1323 ||old_entry.rate||' / '
1324 ||hypothetical_entry.rate (Entry_number));
1325 hr_utility.trace (l_proc||' Amount: '
1326 ||old_entry.amount||' / '
1327 ||hypothetical_entry.amount (entry_number));
1328 hr_utility.trace (l_proc||' Recoverable: '
1329 ||old_entry.recoverable_amount||' /'
1330 ||hypothetical_entry.recoverable_amount (entry_number));
1331 end if;
1332 --
1333 -- Determine the next available period in which to place the
1334 -- correction entry
1335 --
1336 ssp_smp_support_pkg.get_entry_details (
1337 p_date_earned => old_entry.week_commencing,
1338 p_last_process_date => person.final_process_date,
1339 p_person_id => person.person_id,
1340 p_element_type_id => g_PAB_Correction_element.element_type_id,
1341 p_element_link_id => old_entry.element_link_id,
1342 p_assignment_id => old_entry.assignment_id,
1343 p_effective_start_date => old_entry.effective_start_date,
1344 p_effective_end_date => old_entry.effective_end_date,
1345 p_pay_as_lump_sum => person.pay_PAB_as_lump_sum);
1346 --
1347 -- hr_entry_api's take the lookup meanings not the lookup codes.
1348 -- converted rate codes to meanings before calling the
1349 -- api. Later fix made old_entry (csr_existing_entries) return
1350 -- the meaning, so rate passed directly.
1351 --
1352 hr_entry_api.insert_element_entry (
1353 p_effective_start_date=> old_entry.effective_start_date,
1354 p_effective_end_date => old_entry.effective_end_date,
1355 p_element_entry_id => l_dummy,
1356 p_target_entry_id => old_entry.element_entry_id,
1357 p_assignment_id => old_entry.assignment_id,
1358 p_element_link_id => old_entry.element_link_id,
1359 p_creator_type => c_PAB_creator_type,
1360 p_creator_id => p_maternity_id,
1361 p_entry_type => c_PAB_entry_type,
1362 p_input_value_id1=> g_PAB_correction_element.rate_id,
1363 p_input_value_id2=> g_PAB_correction_element.week_commencing_id,
1364 p_input_value_id3=> g_PAB_correction_element.amount_id,
1365 p_input_value_id4=> g_PAB_correction_element.recoverable_amount_id,
1366 p_entry_value1=> old_entry.rate,
1367 -- p_entry_value2=> old_entry.week_commencing,
1368 p_entry_value2 => to_char(old_entry.week_commencing,'DD-MON-YYYY'),
1369 p_entry_value3=> old_entry.amount * -1,
1370 p_entry_value4=> old_entry.recoverable_amount * -1);
1371 --
1372 --New entry will be created by brand_new_entries loop if not p_deleting
1373 end if;
1374 --
1375 end loop old_entries;
1376 --
1377 -- Having been through all the existing entries, we now check that we
1378 -- have dealt with all the newly derived entries by inserting any which
1379 -- were not flagged as dealt with during the above actions.
1380 --
1381 hr_utility.set_location (l_proc,20);
1382 --
1383 <<BRAND_NEW_ENTRIES>>
1384 begin
1385 if p_deleting then
1386 hr_utility.trace('Deleting an absence so don''t insert entries');
1387 else
1388 for new_entry in 1..g_PAB_element.maximum_PPP_weeks LOOP
1389
1390 if (not hypothetical_entry.dealt_with (new_entry) = 'TRUE')
1391 and (not hypothetical_entry.stopped (new_entry) = 'TRUE')
1392 then
1393 hr_entry_api.insert_element_entry (
1394 p_effective_start_date =>
1395 hypothetical_entry.effective_start_date (new_entry),
1396 p_effective_end_date =>
1397 hypothetical_entry.effective_end_date (new_entry),
1398 p_element_entry_id => l_dummy,
1399 p_assignment_id => hypothetical_entry.assignment_id (new_entry),
1400 p_element_link_id => hypothetical_entry.element_link_id (new_entry),
1401 p_creator_type => c_PAB_creator_type,
1402 p_creator_id => p_maternity_id,
1403 p_entry_type => c_PAB_entry_type,
1404 p_input_value_id1 => g_PAB_element.rate_id,
1405 p_input_value_id2 => g_PAB_element.week_commencing_id,
1406 p_input_value_id3 => g_PAB_element.amount_id,
1407 p_input_value_id4 => g_PAB_element.recoverable_amount_id,
1408 p_entry_value1 => hypothetical_entry.rate (new_entry),
1409 -- p_entry_value2 => hypothetical_entry.week_commencing (new_entry),
1410 p_entry_value2 => to_char(hypothetical_entry.week_commencing(new_entry),'DD-MON-YYYY'),
1411 p_entry_value3 => hypothetical_entry.amount (new_entry),
1412 p_entry_value4 =>
1413 hypothetical_entry.recoverable_amount (new_entry));
1414 end if;
1415 end loop brand_new_entries;
1416 end if;
1417 exception
1418 when no_data_found then
1419 --
1420 -- We have run out of hypothetical entries to insert
1421 --
1422 null;
1423 --
1424 end;
1425 --
1426 -- Orphaned stoppages, associated with deleted absence can now be deleted
1427 -- This replaces cross product constraints that are no longer allowed.
1428 --
1429 open csr_no_of_absences;
1430 fetch csr_no_of_absences into l_no_of_absence_periods;
1431 close csr_no_of_absences;
1432 --
1433 if l_no_of_absence_periods = 0 then
1434 remove_stoppages;
1435 end if;
1436 --
1437 hr_utility.set_location(' Leaving: '||l_proc,100);
1438 --
1439 end save_hypothetical_entries;
1440 --
1441 procedure derive_PAB_week (p_week_number in integer) is
1442 --
1443 -- Derive the start and end dates of the week covered by the SAP
1444 -- payment. This is done by finding out how many weeks into the APP
1445 -- we are and finding the offset from the start date.
1446 --
1447 begin
1448 --
1449 hr_utility.set_location ('Entering: ssp_sap_pkg.derive_PAB_week',1);
1450 hr_utility.trace ('Entry number = '||to_char (p_week_number));
1451 --
1452 hypothetical_entry.week_commencing (p_week_number)
1453 := (person.APP_start_date + ((p_week_number -1) * 7));
1454 --
1455 hypothetical_entry.dealt_with (p_week_number) := 'FALSE';
1456 hypothetical_entry.stopped (p_week_number) := 'FALSE';
1457 hypothetical_entry.element_link_id (p_week_number) := null;
1458 hypothetical_entry.assignment_id (p_week_number) := null;
1459 --
1460 hr_utility.trace ('week_commencing = '
1461 ||to_char (hypothetical_entry.week_commencing (p_week_number)));
1462 --
1463 hr_utility.set_location ('Leaving : ssp_sap_pkg.derive_PAB_week',100);
1464 --
1465 end derive_PAB_week;
1466 --
1467 procedure Check_PAB_stoppages (p_week_number in integer) is
1468 --
1469 -- Find any SAP stoppage for the Adoption which overlaps a date range
1470 --
1471 employee_died varchar2 (30) := 'Employee died';
1472 --
1473 cursor csr_stoppages (p_start_date in date, p_end_date in date) is
1474 --
1475 -- Find any non-overridden stoppages
1476 --
1477 select 1
1478 from ssp_stoppages STP,
1479 ssp_withholding_reasons WRE
1480 where stp.override_stoppage <> 'Y'
1481 --
1482 -- and the stoppage ovelaps the period or the stoppage is for
1483 -- death and is prior to the period
1484 --
1485 and ((wre.reason <> employee_died
1486 and stp.withhold_from <= p_end_date
1487 and nvl (stp.withhold_to, hr_general.end_of_time)
1488 >= p_start_date)
1489 --
1490 or (wre.reason = employee_died
1491 and stp.withhold_from < p_start_date))
1492 --
1493 and stp.maternity_id = p_maternity_id
1494 and stp.reason_id = wre.reason_id;
1495 --
1496 l_dummy integer (1);
1497 --
1498 begin
1499 --
1500 hr_utility.set_location ('ssp_sap_pkg.Check_PAB_stoppages',1);
1501 --
1502 hypothetical_entry.stopped (p_week_number) := 'FALSE';
1503 --
1504 --
1505 open csr_stoppages (
1506 hypothetical_entry.week_commencing (p_week_number),
1507 ssp_smp_support_pkg.end_of_week
1508 (hypothetical_entry.week_commencing (p_week_number)));
1509
1510 fetch csr_stoppages into l_dummy;
1511 --
1512 if csr_stoppages%found
1513 then
1514 --
1515 -- There is an overlap between the SAP week and a stoppage so no SAP
1516 -- is payable.
1517 --
1518 hypothetical_entry.stopped (p_week_number) := 'TRUE';
1519 --
1520 hr_utility.trace ('Entry is STOPPED');
1521 --
1522 -- Keep a tally of the number of stopped weeks
1523 --
1524 l_stopped_weeks := l_stopped_weeks +1;
1525 end if;
1526 --
1527 close csr_stoppages;
1528 --
1529 hr_utility.set_location ('ssp_sap_pkg.Check_PAB_stoppages',10);
1530 --
1531 end Check_PAB_stoppages;
1532 --
1533 procedure Calculate_correct_PAB_rate (p_week_number in number) is
1534 --
1535 -- The entry API takes the lookup meanings so we must find
1536 -- the meanings rather than the codes for SAP rates.
1537 --
1538 cursor csr_rate_meaning (p_rate_band varchar2) is
1539 --
1540 select meaning
1541 from hr_lookups
1542 where lookup_type = 'SPP_RATES'
1543 and lookup_code = p_rate_band;
1544 --
1545 begin
1546 --
1547 hr_utility.set_location ('ssp_sap_pkg.Calculate_correct_PAB_rate',1);
1548 --
1549 if l_high_rate is null then
1550 --
1551 -- Get the meanings for the rate bands
1552 --
1553 -- Get the higher rate band
1554 --
1555 open csr_rate_meaning ('STD');
1556 fetch csr_rate_meaning into l_high_rate;
1557 close csr_rate_meaning;
1558
1559 end if;
1560 --
1561 /*
1562 if (p_week_number - l_stopped_weeks)
1563 <= g_SMP_element.period_at_higher_rate
1564 then
1565 hr_utility.set_location ('ssp_smp_pkg.Calculate_correct_SMP_rate',1);
1566 --
1567 -- We have not yet given the employee all their higher rate weeks
1568 --
1569 */
1570 hypothetical_entry.rate (p_week_number) := l_high_rate;
1571 /*
1572 else
1573 hypothetical_entry.rate (p_week_number) := l_low_rate;
1574 end if;
1575 */
1576 --
1577 hr_utility.trace ('PAB Rate = '
1578 ||hypothetical_entry.rate (p_week_number));
1579 --
1580 hr_utility.set_location ('ssp_sap_pkg.Calculate_correct_PAB_rate',10);
1581 --
1582 end Calculate_correct_PAB_rate;
1583 --
1584 procedure Calculate_PAB_amounts (p_week_number in integer, p_APP_start_date in date) is
1585 --
1586 begin
1587 --
1588 hr_utility.set_location('Entering: ssp_sAp_pkg.Calculate_SaP_amounts',1);
1589 --
1590 -- Get the SAP element for each week in case the SAP rate has changed
1591 --
1592 get_PAB_element (hypothetical_entry.week_commencing (p_week_number));
1593 --
1594 hypothetical_entry.amount (p_week_number)
1595 := least (round (
1596 (average_earnings * g_PAB_element.SPP_rate)
1597 + 0.0049,2),
1598 g_PAB_element.STANDARD_RATE);
1599 --
1600 hypothetical_entry.recoverable_amount (p_week_number)
1601 := round (hypothetical_entry.amount (p_week_number)
1602 * g_PAB_element.recovery_rate,2);
1603 --
1604 hr_utility.trace ('PAB amount = '
1605 ||to_char (hypothetical_entry.amount (p_week_number)));
1606 hr_utility.trace ('Recoverable amount = '
1607 ||to_char (hypothetical_entry.recoverable_amount (p_week_number)));
1608 --
1609 hr_utility.set_location('Leaving : ssp_sap_pkg.Calculate_PAB_amounts',100);
1610 --
1611 end calculate_PAB_amounts;
1612 --
1613 procedure check_parameters is
1614 begin
1615 hr_api.mandatory_arg_error (
1616 p_api_name => l_proc,
1617 p_argument => 'maternity_id',
1618 p_argument_value=> p_maternity_id);
1619 --
1620 end check_parameters;
1621 --
1622 begin
1623 --
1624 hr_utility.set_location ('ssp_sap_pkg.generate_payments',1);
1625 --
1626 check_parameters;
1627 --
1628 <<PAB_WEEKS>>
1629 --
1630 if person.APP_start_date is not null then
1631 for week_number in 1..g_PAB_element.maximum_PPP_weeks
1632 LOOP
1633 --
1634 -- Derive hypothetical entries ie those entries which would be applied for a
1635 -- completely new maternity. Store them internally because we must check
1636 -- previously created entries before applying the hypothetical entries to the
1637 -- database.
1638 --
1639 Derive_PAB_week (week_number);
1640 Check_PAB_stoppages (week_number);
1641 Calculate_correct_PAB_rate (week_number);
1642 Calculate_PAB_amounts (week_number, person.APP_start_date);
1643 --
1644 if (hypothetical_entry.stopped (week_number) = 'FALSE') then
1645 --
1646 -- Get the entry details unless the entry has been stopped (in which case
1647 -- we do not need the entry details and errors may occur if we call the
1648 -- procedure; eg the woman's assignment ends)
1649 --
1650 ssp_smp_support_pkg.get_entry_details (
1651 p_date_earned => hypothetical_entry.week_commencing
1652 (week_number),
1653 p_pay_as_lump_sum => person.pay_PAB_as_lump_sum,
1654 p_last_process_date => person.final_process_date,
1655 p_person_id => person.person_id,
1656 p_element_type_id => g_PAB_element.element_type_id,
1657 p_element_link_id => hypothetical_entry.element_link_id
1658 (week_number),
1659 p_assignment_id => hypothetical_entry.assignment_id
1660 (week_number),
1661 p_effective_start_date => hypothetical_entry.effective_start_date
1662 (week_number),
1663 p_effective_end_date => hypothetical_entry.effective_end_date
1664 (week_number));
1665 end if;
1666 end loop PAB_weeks;
1667 end if;
1668 --
1669 Save_hypothetical_entries(p_deleting);
1670 --
1671 end generate_payments;
1672 --
1673 --------------------------------------------------------------------------------
1674 procedure PAB_control (p_maternity_id in number,
1675 p_deleting in boolean default FALSE) is
1676 --
1677 -- p_deleting parameter added to deal with absences being deleted, without
1678 -- maternity being deleted.
1679 --
1680 cursor csr_maternity is
1681 --
1682 -- Find out if the maternity exists
1683 --
1684 select 1
1685 from ssp_maternities
1686 where maternity_id = p_maternity_id;
1687 --
1688 cursor csr_entries is
1689 --
1690 -- Get all element entries associated with a maternity
1691 --
1692 select /*+ ORDERED use_nl(paa,paaf,etype,entry) */
1693 entry.element_entry_id,
1694 entry.effective_start_date
1695 from per_absence_attendances PAA,
1696 per_all_assignments_f PAAF,
1697 pay_element_entries_f entry
1698 where PAA.maternity_id = p_maternity_id
1699 and PAAF.person_id = PAA.person_id
1700 and entry.creator_type = 'M'
1701 and entry.creator_id = p_maternity_id
1702 and entry.assignment_id = paaf.assignment_id;
1703 --
1704 cursor csr_count_absences is
1705 select count(*)
1706 from ssp_maternities mat,
1707 per_absence_attendances ab
1708 where mat.maternity_id = p_maternity_id
1709 and ab.person_id = mat.person_id
1710 and ab.maternity_id = mat.maternity_id;
1711 --
1712 l_count number;
1713 l_dummy number;
1714 l_proc varchar2 (72) := g_package||'PAB_control';
1715 --
1716 begin
1717 --
1718 hr_utility.set_location (l_proc,1);
1719 --
1720 open csr_maternity;
1721 fetch csr_maternity into l_dummy;
1722 --
1723 if csr_maternity%found then
1724 --
1725 -- Recalculate SAP
1726 --
1727 if entitled_to_PAB (p_maternity_id) then
1728 open csr_count_absences;
1729 fetch csr_count_absences into l_count;
1730 close csr_count_absences;
1731 if l_count > 0 then
1732 generate_payments (p_maternity_id, false);
1733 else
1734 generate_payments (p_maternity_id, p_deleting);
1735 end if;
1736 elsif p_deleting then
1737 -- not entitled but deleting absence then
1738 generate_payments (p_maternity_id, p_deleting);
1739 end if;
1740 else
1741 --
1742 -- The maternity may have been deleted. Remove any element entries associated
1743 -- with it (the absences, stoppages and medicals are handled by constraints).
1744 --
1745 for obsolete in csr_entries LOOP
1746 hr_utility.trace (l_proc||' Deleting element entry_id '||
1747 to_char (obsolete.element_entry_id));
1748 hr_utility.trace (l_proc||'-------------------------------------------');
1749 --
1750 hr_entry_api.delete_element_entry (
1751 p_dt_delete_mode => 'ZAP',
1752 p_session_date => obsolete.effective_start_date,
1753 p_element_entry_id => obsolete.element_entry_id);
1754 end loop;
1755 end if;
1756 --
1757 hr_utility.set_location (l_proc,100);
1758 --
1759 end PAB_control;
1760 --------------------------------------------------------------------------------
1761 end ssp_pab_pkg;
[Home] [Help]