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