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