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