[Home] [Help]
PACKAGE BODY: APPS.PAY_FR_MAP_CALC
Source
1 PACKAGE BODY PAY_FR_MAP_CALC AS
2 /* $Header: pyfrmapp.pkb 120.1 2005/06/28 05:30:22 sbairagi noship $ */
3
4 -----------------------------------------------------------------------
5 -- Date Author Comments
6 -- 19-12-02 asnell Initial version of package with reqd program
7 -- for Absence report and IJSS calc
8 -- 06-01-03 vsjain Additions for Maternity Architecture
9 -- 16-01-03 vsjain Changes in procedures after Design Review
10 -- 20-01-03 vsjain Changes for map deduction procedure
11 -- 22-01-03 asnell added details of calc_map/calc_map_ijss
12 -- 06-02-03 vsjain Check valid parent absence
13 -- 28-06-05 sbairagi GSCC Error ...to_date is removed line 348 . version 115.23
14 -----------------------------------------------------------------------
15 -- PACKAGE GLOBALS
16 --
17 cs_MARGIN CONSTANT NUMBER := 1;
18 blank_map_arch t_map_arch;
19 blank_map_calc t_map_calc;
20 g_ctl t_ctl;
21 blank_ctl t_ctl;
22 g_ijss_net_rate number;
23
24 g_package varchar2(33) := ' PAY_FR_MAP_CALC.';
25 --
26
27 --
28 -- PUBLIC FUNCTIONS
29 ---------------------------------------------------------------------------------
30 PROCEDURE Calculate_Maternity_Deduction is
31
32 l_inputs ff_exec.inputs_t;
33 l_outputs ff_exec.outputs_t;
34
35 begin
36
37 hr_utility.set_location('Deduction',10);
38
39 IF g_map_arch.deduct_formula is null then
40
41 hr_utility.set_message(801, 'PY_75027_SICK_DEDUCT_FF_NULL');
42 hr_utility.raise_error;
43
44 END IF;
45
46 /* set context value before calling fast formula */
47
48 pay_balance_pkg.set_context('ASSIGNMENT_ID'
49 , g_map_arch.assignment_id);
50 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID'
51 , g_map_arch.assignment_action_id);
52 pay_balance_pkg.set_context('DATE_EARNED'
53 , fnd_date.date_to_canonical(g_map_arch.date_earned));
54 pay_balance_pkg.set_context('BUSINESS_GROUP_ID'
55 , g_map_arch.business_group_id);
56 pay_balance_pkg.set_context('PAYROLL_ID'
57 , g_map_arch.payroll_id);
58 pay_balance_pkg.set_context('ELEMENT_ENTRY_ID'
59 , g_map_arch.element_entry_id);
60
61 /* Get input paramaters for fast formula */
62
63 ff_exec.init_formula(g_map_arch.deduct_formula
64 , g_map_arch.date_earned
65 , l_inputs
66 , l_outputs);
67
68 hr_utility.set_location('Deduct',30);
69
70 For i in 1..l_inputs.count Loop
71 IF l_inputs(i).name = 'DEDUCTION_START_DATE' THEN
72 l_inputs(i).value := fnd_date.date_to_canonical(g_map_calc.start_date);
73 ELSIF l_inputs(i).name = 'DEDUCTION_END_DATE' THEN
74 l_inputs(i).value:= fnd_date.date_to_canonical(g_map_calc.end_date);
75 ELSIF l_inputs(i).name = 'ASG_ACTION_START_DATE' THEN
76 l_inputs(i).value:= fnd_date.date_to_canonical(g_map_arch.action_start_date);
77 ELSIF l_inputs(i).name = 'ASG_ACTION_END_DATE' THEN
78 l_inputs(i).value:= fnd_date.date_to_canonical(g_map_arch.action_end_date);
79 ELSIF l_inputs(i).name = 'REFERENCE_SALARY' THEN
80 l_inputs(i).value:= g_map_arch.ded_ref_salary;
81 ELSIF l_inputs(i).name = 'ASSIGNMENT_ID' THEN
82 l_inputs(i).value:= g_map_arch.assignment_id;
83 ELSIF l_inputs(i).name = 'DATE_EARNED' THEN
84 l_inputs(i).value:= fnd_date.date_to_canonical(g_map_arch.date_earned);
85 ELSIF l_inputs(i).name = 'ASSIGNMENT_ACTION_ID' THEN
86 l_inputs(i).value:= g_map_arch.assignment_action_id;
87 ELSIF l_inputs(i).name = 'BUSINESS_GROUP_ID' THEN
88 l_inputs(i).value:= g_map_arch.business_group_id;
89 ELSIF l_inputs(i).name = 'PAYROLL_ID' THEN
90 l_inputs(i).value:= g_map_arch.payroll_id;
91 ELSIF l_inputs(i).name = 'ELEMENT_ENTRY_ID' THEN
92 l_inputs(i).value:= g_map_arch.element_entry_id;
93 END IF;
94 END Loop;
95
96 hr_utility.set_location('Deduction',50);
97
98 /* define output values for fast formula */
99
100 l_outputs(1).name := 'L_SICKNESS_DEDUCTION';
101 l_outputs(2).name := 'L_RATE_NUMBER_OF_DAYS'; --NUMBER_OF_DAYS';
102 l_outputs(3).name := 'L_DAILY_RATE_D'; --'DAILY_RATE';
103
104 /* run formula and get outputs */
105
106 per_formula_functions.run_formula
107 (p_formula_id => g_map_arch.deduct_formula
108 ,p_calculation_date => g_map_arch.date_earned
109 ,p_inputs => l_inputs
110 ,p_outputs => l_outputs);
111
112 g_map_calc.deduction := l_outputs(1).value;
113 g_map_calc.deduction_rate := l_outputs(2).value;
114 g_map_calc.deduction_base := l_outputs(3).value;
115
116 IF g_map_calc.deduction_base = -1 then
117 hr_utility.set_message (801, 'PY_75065_DEDUCT_WORK_PAT_NULL');
118 hr_utility.raise_error;
119 END IF;
120
121 hr_utility.set_location('Deduction',100);
122
123 end Calculate_Maternity_Deduction;
124
125 -----------------------------------------------------------------------
126 -- calculate the number of dependent children the emplouee has
127 -- based on FR Public Sector code in HREMEA.pld which uses
128 -- shared types to classify lookups of type contact as
129 -- dependent children
130 FUNCTION COUNT_CHILDREN(
131 P_person_id IN Number,
132 P_effective_date IN Date) return NUMBER IS
133
134 l_no_dep_child number;
135
136 CURSOR c_no_dep_Child
137 IS
138 SELECT count(*)
139 FROM per_contact_relationships_v2 pcr
140 WHERE pcr.person_id = p_person_id
141 AND dependent_flag = 'Y'
142 AND p_effective_date
143 BETWEEN
144 NVL(pcr.date_start, p_effective_date)
145 AND NVL(pcr.date_end,p_effective_date)
146 AND EXISTS
147 ( SELECT pst.INFORMATION3 from per_shared_types pst
148 WHERE pcr.contact_type = pst.system_type_cd
149 AND pst.lookup_type = 'CONTACT'
150 AND pst.INFORMATION3 = 'Y'
151 AND ( pst.business_group_id = pcr.business_group_id
152 OR pst.business_group_id IS NULL)
153 );
154 BEGIN
155
156 OPEN c_no_dep_Child;
157 fetch c_no_dep_Child into l_no_dep_Child;
158 if c_no_dep_Child%NOTFOUND then
159 l_no_dep_child := 0;
160 end if;
161 close c_no_dep_Child;
162
163 RETURN l_no_dep_child;
164
165 END COUNT_CHILDREN;
166
167 ---------------------------------------------------------------------------------
168
169 -- calculates maternity IJSS
170 PROCEDURE CALC_MAP_IJSS(
171 p_assignment_id IN Number,
172 p_start_date IN Date,
173 p_end_date IN Date
174 ) is
175
176 Cursor c_prev_postnatal_abs(p_end_date date,
177 p_confinement_date date,
178 p_parent_absence_id number,
179 p_person_id number) is
180 select sum(date_end - date_start +1 ) duration
181 from per_absence_attendances
182 where person_id = p_person_id
183 and abs_information_category = 'FR_M'
184 and date_end < p_end_date
185 and date_end > p_confinement_date
186 and abs_information4 is null
187 and abs_information1 = fnd_number.number_to_canonical(p_parent_absence_id);
188
189 -- 1) for anti natal maternity no check is done on duration as the eligibility for
190 -- IJSS lasts until the birth.
191 -- 2) for post natal leave the duration is fetched from user_table FR_MAP_DURATION
192 -- determine the criteria key from the type of absence, the number of births and
193 -- the number of dependent children.
194 -- 3) calculate the duration of the absence from the absence confinement data up to
195 -- the p_end_date. If band is exausted set the p_ijjs_end_date to the date is
196 -- exausts on. Else set p_ijjs_end_date to p_end_date
197 -- 4) set p_absence_duration to be the days between p_ijjs_start and end.
198 -- 5) fetch reference salary over 3 month prior to maternity
199 -- 6) derive the daily reference salary from 5 above and cap on the net SS_CEILING
200 -- 7) multiple the rate by number of days
201 --
202 l_count_dependent_children number;
203 l_start_ijss date;
204 l_end_ijss date;
205 l_ijss_rate number;
206 l_ss_ceiling_rate number;
207 l_criteria varchar2(40);
208 l_ref_date date;
209 l_ref_start_date date;
210 l_ref_end_date date;
211 l_ref_sal number;
212 l_prev_postnatal_duration number := 0;
213 l_postnatal_duration number := 0;
214 l_postnatal_duration_max number := 0;
215
216 begin
217
218 hr_utility.set_location('IJSS',10);
219
220 l_count_dependent_children := count_children(
221 G_MAP_CALC.person_id,
222 G_MAP_CALC.start_date);
223
224 -- first set the IJSS start and end dates to the dates of the absence
225 -- being processed. Thse dates will subsequently be amended if max
226 -- duration for IJSS exceeded
227 g_map_calc.IJSS_GROSS_START_DATE := p_start_date;
228 g_map_calc.IJSS_GROSS_END_DATE := p_end_date;
229
230 -- read the maximum duration from the user table dependent on no of births
231 -- and no of dependent children
232
233 If G_MAP_CALC.absence_category = 'FR_M' and G_MAP_CALC.births > 2 then
234 l_criteria := 'Maternity more than 2 births';
235 elsif G_MAP_CALC.absence_category = 'FR_M' and G_MAP_CALC.births = 2 then
236 l_criteria := 'Maternity Twins';
237 elsif G_MAP_CALC.absence_category = 'FR_M' and l_count_dependent_children > 1 then
238 l_criteria := 'Maternity at least 2 dependent children';
239 elsif G_MAP_CALC.absence_category = 'FR_M' then
240 l_criteria := 'Maternity less than 2 dependent children';
241 elsif G_MAP_CALC.absence_category = 'FR_FR_ADOPTION' and G_MAP_CALC.births > 1 then
242 l_criteria := 'Adoption multiple births';
243 elsif G_MAP_CALC.absence_category = 'FR_FR_ADOPTION' and l_count_dependent_children > 1 then
244 l_criteria := 'Adoption at least 2 dependent children';
245 elsif G_MAP_CALC.absence_category = 'FR_FR_ADOPTION' then
246 l_criteria := 'Adoption less than 2 dependent children';
247 elsif G_MAP_CALC.absence_category = 'FR_FR_PATERNITY' and G_MAP_CALC.births > 1 then
248 l_criteria := 'Paternity multiple births';
249 else l_criteria := 'Paternity';
250 end if;
251
252 -- if the absence is post natal check against max IJSS duration
253
254 hr_utility.set_location('IJSS'|| l_criteria,30);
255
256 BEGIN
257
258 l_postnatal_duration_max := hruserdt.get_table_value(
259 G_MAP_ARCH.business_group_id,
260 'FR_MAP_DURATION',
261 'Post-Natal',
262 l_criteria,
263 p_end_date);
264 EXCEPTION
265 WHEN OTHERS THEN
266 l_postnatal_duration_max := 0;
267
268 END;
269
270 hr_utility.set_location('IJSS'|| l_postnatal_duration_max,31);
271
272 IF G_MAP_CALC.absence_category = 'FR_M' then
273
274 if p_end_date >= G_MAP_CALC.birth_date then -- { post natal
275
276 -- Main Maternity
277
278 l_postnatal_duration := nvl((least(g_map_arch.action_end_date,g_map_calc.parent_absence_end_date) - g_map_calc.birth_date+ 1),0);
279
280 if g_map_calc.parent_absence_id <> g_map_calc.absence_id then
281
282 l_postnatal_duration := nvl(l_postnatal_duration,0) + nvl(g_map_calc.end_date - g_map_calc.absence_start_date + 1,0);
283
284 -- Post Natal Maternity
285 open c_prev_postnatal_abs(g_map_calc.start_date,
286 g_map_calc.birth_date,
287 g_map_calc.parent_absence_id,
288 g_map_calc.person_id);
289 fetch c_prev_postnatal_abs into l_prev_postnatal_duration;
290 close c_prev_postnatal_abs;
291
292 l_postnatal_duration := l_postnatal_duration + nvl(l_prev_postnatal_duration,0);
293
294 end if;
295
296 end if;
297
298 ELSE
299
300 l_prev_postnatal_duration := nvl(g_map_calc.spouses_leave,0);
301
302 l_postnatal_duration := nvl((g_map_calc.end_date - g_map_calc.parent_absence_start_date + 1),0);
303
304 l_postnatal_duration := l_postnatal_duration + l_prev_postnatal_duration;
305
306 END IF;
307
308 -- if postnatal duration is exceeded then set the end of the IJSS Payment to that maximum
309 hr_utility.set_location('IJSS'|| l_postnatal_duration,32);
310
311 if l_postnatal_duration > l_postnatal_duration_max then -- { over max
312 g_map_calc.IJSS_GROSS_END_DATE := p_end_date - ( l_postnatal_duration - l_postnatal_duration_max);
313
314 -- if the post natal duration is exceeded before this absence then no IJSS is due
315 if g_map_calc.IJSS_GROSS_END_DATE < p_start_date then
316 g_map_calc.IJSS_GROSS_END_DATE := null;
317 g_map_calc.IJSS_GROSS_START_DATE := null;
318 end if;
319 end if; -- } over max
320
321 if g_map_calc.IJSS_GROSS_END_DATE is not null and g_map_calc.IJSS_GROSS_START_DATE is not null then -- { ijss calc needed
322 g_map_calc.IJSS_GROSS_DAYS := (g_map_calc.IJSS_GROSS_END_DATE - g_map_calc.IJSS_GROSS_START_DATE) +1;
323
324 hr_utility.set_location('IJSS'|| g_map_calc.ijss_gross_days,32);
325 hr_utility.set_location('IJSS'|| fnd_date.date_to_canonical(g_map_calc.ijss_gross_start_date),32);
326 hr_utility.set_location('IJSS'|| fnd_date.date_to_canonical(g_map_calc.ijss_gross_end_date),32);
327
328 -- fetch reference salary
329 -- reference period is the 3 calendar months that preceed the start of the maternity
330 l_ref_date := least(g_map_calc.parent_absence_start_date, g_map_calc.birth_date);
331 l_ref_start_date := (add_months(trunc(l_ref_date,'MONTH'),-3));
332 l_ref_end_date := last_day(add_months(l_ref_date,-1)) ;
333
334 l_ref_sal := PAY_FR_SICKNESS_CALC.fr_rolling_balance(p_assignment_id,
335 'FR_MAP_IJSS_REFERENCE_SALARY',
336 l_ref_start_date,
337 l_ref_end_date);
338 hr_utility.set_location('IJSS',50);
339
340 l_ref_sal := l_ref_sal / 90; -- convert to daily rate
341
342 -- cap daily rate at social security ceiling limit
343 g_map_calc.IJSS_GROSS_RATE := least( l_ref_sal,g_map_arch.NOTIONAL_SS_RATE);
344
345 -- calculate the gross and Net IJSS
346 g_map_calc.IJSS_GROSS := g_map_calc.IJSS_GROSS_RATE * g_map_calc.IJSS_GROSS_DAYS;
347
348 g_map_calc.IJSS_NET_PAYMENT := g_map_calc.IJSS_GROSS * (100 - g_ijss_net_rate)/100;
349
350 hr_utility.set_location('IJSS'|| g_map_calc.ijss_gross_rate,32);
351 hr_utility.set_location('IJSS'|| g_map_calc.ijss_net_payment,32);
352
353 end if; -- } ijss calc needed
354 --
355 hr_utility.set_location('IJSS',100);
356
357 end CALC_MAP_IJSS;
358
359 PROCEDURE calc_map is
360 l_ref_bal_date_from Date;
361 l_smic_multiplier Number;
362 l_smic_hourly_rate Number;
363 l_smid_rate Number;
364 l_global_smic_ded Number;
365 l_return number;
366 l_ante_duration number := 0;
367 l_ante_duration_max number := 0;
368
369 -- Cursor for fetching global value
370 Cursor csr_global_value(c_global_name VARCHAR2,c_date_earned DATE) IS
371 SELECT global_value
372 FROM ff_globals_f
373 WHERE global_name = c_global_name
374 AND legislation_code = 'FR'
375 AND c_date_earned BETWEEN effective_start_date AND effective_end_date;
379 select sum(date_end - date_start +1 ) duration
376
377 Cursor c_prev_ante_natal_abs(p_start_date date
378 ,p_person_id number) is
380 from per_absence_attendances
381 where person_id = p_person_id
382 and abs_information_category = 'FR_M'
383 and abs_information4 is null
384 and date_start >= add_months(p_start_date,-9)
385 and date_start < p_start_date
386 and abs_information1 is null;
387
388 begin
389
390 hr_utility.set_location('CALC_MAP',10);
391
392 if g_map_calc.absence_category = 'FR_M' and nvl(g_map_calc.birth_date, hr_general.end_of_time) = hr_general.end_of_time then
393
394 OPEN csr_global_value('FR_MATERNITY_MAX_EXTENSION',g_map_arch.date_earned);
395 FETCH csr_global_value INTO l_ante_duration_max;
396 CLOSE csr_global_value;
397
398 hr_utility.set_location('CALC_MAP'|| l_ante_duration_max,12);
399
400 open c_prev_ante_natal_abs(g_map_calc.start_date,g_map_calc.person_id);
401 fetch c_prev_ante_natal_abs into l_ante_duration;
402 close c_prev_ante_natal_abs;
403
404 hr_utility.set_location('CALC_MAP'|| l_ante_duration,14);
405
406 l_ante_duration := (g_map_calc.end_date - g_map_calc.start_date + 1) + nvl(l_ante_duration,0);
407 hr_utility.set_location('CALC_MAP'|| l_ante_duration,16);
408 if (nvl(l_ante_duration,0) > nvl(l_ante_duration_max,0) ) then
409
410 hr_utility.set_message (801, 'PAY_75052_MAT_EXT_EXCEEDED');
411 hr_utility.raise_error;
412
413 end if;
414
415 end if;
416
417 -- fetch the IJSS_NET_RATE based on date_earned ( constant for whole run )
418 if g_ijss_net_rate is null then
419 OPEN csr_global_value('FR_IJSS_NET_RATE',g_map_arch.date_earned);
420 FETCH csr_global_value INTO g_ijss_net_rate;
421 CLOSE csr_global_value;
422 end if;
423
424 -- the start and end dates relate to the absence being processed - reset those
425 -- to be within then run being processed
426
427 if g_map_calc.start_date < g_map_arch.action_start_date then
428 g_map_calc.start_date := g_map_arch.action_start_date;
429 end if;
430
431 hr_utility.set_location('CALC_MAP'|| g_ijss_net_rate,30);
432
433 if g_map_calc.end_date > g_map_arch.action_end_date then
434 g_map_calc.end_date := g_map_arch.action_end_date;
435 end if;
436
437 g_map_calc.deduction_start_date := g_map_calc.start_date;
438 g_map_calc.deduction_end_date := g_map_calc.end_date;
439
440 Calculate_Maternity_Deduction;
441
442 -- check CALC structure for IJSS eligibility, set ELIG_IJSS flag
443 if g_map_calc.ELIG_IJSS_HOURS = 'Y' THEN -- { ELIG_HOURS
444 g_map_calc.ELIG_IJSS := 'Y';
445 end if; -- } ELIG_HOURS
446
447 IF g_map_calc.ELIG_IJSS_HOURS <> 'Y' THEN -- { not eligible hours
448 -- Perform SMID contributions check
449 -- Fetch reference contributions from global
450 -- as of 1st day of reference period
451 l_ref_bal_date_from := TRUNC(ADD_MONTHS(g_map_calc.PARENT_ABSENCE_START_DATE,-3), 'MONTH');
452 --
453 OPEN csr_global_value('FR_IJSS_SMIC_MULTIPLIER',l_ref_bal_date_from);
454 FETCH csr_global_value INTO l_smic_multiplier;
455 CLOSE csr_global_value;
456 --
457 OPEN csr_global_value('FR_HOURLY_SMIC_RATE',l_ref_bal_date_from);
458 FETCH csr_global_value INTO l_smic_hourly_rate;
459 CLOSE csr_global_value;
460
461 hr_utility.set_location('CALC_MAP',50);
462 --
463 l_smid_rate := hruserdt.get_table_value(g_map_arch.business_group_id,'FR_CONTRIBUTION_RATES','Value (EUR)','EE_SMID',l_ref_bal_date_from);
464 --
465 l_global_smic_ded := l_smic_multiplier * l_smic_hourly_rate * l_smid_rate / 100;
466
467 hr_utility.set_location('CALC_MAP'|| l_global_smic_ded,50);
468 hr_utility.set_location('CALC_MAP'|| g_map_calc.ELIG_IJSS_CONTRIB,50);
469
470 IF g_map_calc.ELIG_IJSS_CONTRIB > l_global_smic_ded THEN -- { ELIG_CONTRIB
471 g_map_calc.ELIG_IJSS := 'Y';
472 END IF; -- } ELIG_CONTRIB
473
474 IF g_map_calc.ELIG_IJSS_CONTRIB <= l_global_smic_ded THEN -- { not ELIG_CONTRIB
475 g_map_calc.ELIG_IJSS := 'N';
476 END IF; -- } not ELIG_CONTRIB
477
478 END IF; -- } not eligible hours
479
480 hr_utility.set_location('CALC_MAP',70);
481
482 if g_map_calc.ELIG_IJSS = 'Y'and g_map_calc.estimated_IJSS = 'Y' then -- { ELIG_IJSS
483 calc_map_ijss(g_map_arch.assignment_id,
484 g_map_calc.start_date,
485 g_map_calc.end_date);
486
487 IF g_map_calc.ijss_gross > 0 and g_map_calc.ijss_net_payment > 0 then
488
489 g_map_calc.IJSS_ADJUSTMENT := 'Y';
490
491 END IF;
492
493 end if; -- } ELIG_IJSS
494
495 hr_utility.set_location('CALC_MAP',90);
496
497 IF g_map_calc.gi_eligible = 'ALL' then
498 g_map_calc.GI_ELIGIBLE := 'Y';
499 g_map_calc.GI_PAYMENT := g_map_calc.DEDUCTION;
500 ELSIF g_map_calc.gi_eligible = 'IJSS' and g_map_calc.ELIG_IJSS = 'Y' then
501 g_map_calc.GI_ELIGIBLE := 'Y';
502 g_map_calc.GI_PAYMENT := g_map_calc.DEDUCTION;
503 ELSE
504 g_map_calc.GI_ELIGIBLE := 'N';
505 END IF;
506
507 hr_utility.set_location('CALC_MAP',100);
508
509 end calc_map;
513 FUNCTION init_map_absence(
510
511 --
512
514 P_Assignment_id IN Number,
515 P_element_entry_id IN Number,
516 P_date_earned IN Date,
517 p_business_group_id IN Number,
518 p_payroll_id IN Number,
519 p_assignment_action_id IN Number,
520 p_element_type_id IN Number,
521 p_deduction_formula IN Number,
522 p_deduction_ref_salary IN Number,
523 P_action_start_date IN Date,
524 P_action_end_date IN Date,
525 p_notional_ss_rate IN Number)
526 RETURN Varchar2 is
527
528 cursor c_get_entry_dates is
529 select min(effective_start_date) effective_start_date
530 , max(effective_end_date) effective_end_date
531 , min(creator_id) absence_id
532 from pay_element_entries_f
533 where element_entry_id = p_element_entry_id;
534
535 cursor c_get_absence(p_absence_attendance_id number) is
536 select
537 paa.date_start
538 , paa.date_end
539 , paa.date_end - paa.date_start + 1 duration
540 , paa.person_id
541 , paa.abs_information_category
542 , paa.abs_information1
543 , paa.abs_information2
544 , paa.abs_information3
545 , paa.abs_information4
546 , paa.abs_information5
547 , paa.abs_information6
548 , paa.abs_information7
549 , paa.abs_information8
550 , paa.abs_information9
551 , paa.abs_information10
552 from per_absence_attendances paa
553 where paa.absence_attendance_id = p_absence_attendance_id;
554
555 cursor c_get_parent_maternity(p_parent_absence_id number) is
556 SELECT pabs.abs_information2 elig_gi,
557 nvl(fnd_date.canonical_to_date (pabs.abs_information4),
558 hr_general.end_of_time) birth_date,
559 fnd_number.canonical_to_number(pabs.abs_information6) births,
560 pabs.abs_information7 estmtd_ijss,
561 pabs.abs_information8 elig_ijss_hours,
562 fnd_number.canonical_to_number (pabs.abs_information9)
563 elig_ijss_contrib,
564 pabs.date_start abs_start,
565 pabs.date_end abs_end
566 FROM per_absence_attendances pabs
567 WHERE pabs.absence_attendance_id = p_parent_absence_id;
568
569 cursor c_get_child_absence(p_person_id number
570 ,p_parent_absence_id number
571 ,p_max_end_date date) is
572 select absence_attendance_id
573 , date_start
574 , date_end
575 , date_end - date_start + 1 duration
576 from per_absence_attendances
577 where person_id = p_person_id
578 and date_end <= p_max_end_date
579 and abs_information1 = fnd_number.number_to_canonical(p_parent_absence_id)
580 order by date_start;
581
582 TYPE t_absence is RECORD
583 (absence_id number
584 ,date_start date
585 ,date_end date
586 ,duration number
587 ,effective_start_date date
588 ,effective_end_date date
589 ,person_id number
590 ,abs_information_category varchar2(30)
591 ,abs_information1 varchar2(80)
592 ,abs_information2 varchar2(80)
593 ,abs_information3 varchar2(80)
594 ,abs_information4 varchar2(80)
595 ,abs_information5 varchar2(80)
596 ,abs_information6 varchar2(80)
597 ,abs_information7 varchar2(80)
598 ,abs_information8 varchar2(80)
599 ,abs_information9 varchar2(80)
600 ,abs_information10 varchar2(80)
601 );
602 --
603 abs_rec t_absence;
604 parent_abs_rec t_absence;
605 --
606 l_duration number := 0;
607 l_absence_start_date date;
608 l_absence_end_date date;
609
610 l_proc varchar2(72) := g_package||'init_absence';
611
612 begin
613
614 hr_utility.set_location('INIT_MAP',10);
615
616 -- fetch the dates and the creator_id (which records the absence_attendance_id of
617 -- the absence being processed ) for the element being processed
618 open c_get_entry_dates;
619 fetch c_get_entry_dates into abs_rec.effective_start_date,
620 abs_rec.effective_end_date,
621 abs_rec.absence_id;
622 close c_get_entry_dates;
623
624 -- fetch the details of the absence - not don't know whether its maternity, adoption,
625 -- paternity or whether its a parent maternity yet so pospone the interpretetion
626 -- until the absence has been fetched
627 open c_get_absence(abs_rec.absence_id);
628 fetch c_get_absence into abs_rec.date_start,
629 abs_rec.date_end,
630 abs_rec.duration,
631 abs_rec.person_id,
632 abs_rec.abs_information_category,
633 abs_rec.abs_information1,
634 abs_rec.abs_information2,
635 abs_rec.abs_information3,
636 abs_rec.abs_information4,
637 abs_rec.abs_information5,
638 abs_rec.abs_information6,
639 abs_rec.abs_information7,
640 abs_rec.abs_information8,
641 abs_rec.abs_information9,
642 abs_rec.abs_information10;
643
644 close c_get_absence;
645
646 hr_utility.set_location('INIT_MAP',30);
647
651 g_map_calc.initiator := 'ABSENCE';
648 -- set the columns that are relevent to the child absence/same value for child
649 -- and parent
650 g_map_calc.absence_id := abs_rec.absence_id;
652 g_map_calc.person_id := abs_rec.person_id;
653 g_map_calc.absence_category := abs_rec.abs_information_category;
654 g_map_calc.start_date := abs_rec.date_start;
655 g_map_calc.end_date := abs_rec.date_end;
656 g_map_calc.absence_start_date := abs_rec.date_start;
657 g_map_arch.deduct_formula := p_deduction_formula;
658 g_map_arch.DED_REF_SALARY := p_deduction_ref_salary;
659 g_map_arch.action_start_date := p_action_start_date;
660 g_map_arch.action_end_date := p_action_end_date;
661 g_map_arch.element_type_id := p_element_type_id;
662 g_map_arch.payroll_id := p_payroll_id;
663 g_map_arch.assignment_action_id := p_assignment_action_id;
664 g_map_arch.business_group_id := p_business_group_id;
665 g_map_arch.notional_ss_rate := p_notional_ss_rate;
666
667 if abs_rec.abs_information_category = 'FR_M' then -- { Maternity
668 -- if its a parent then transfer all columns into calc structure
669 if abs_rec.abs_information1 is null then -- { Parent Maternity
670
671 g_map_calc.parent_absence_id := abs_rec.absence_id;
672 g_map_calc.person_id := abs_rec.person_id;
673 g_map_calc.absence_category := abs_rec.abs_information_category;
674 g_map_calc.parent_absence_start_date := abs_rec.date_start;
675 g_map_calc.parent_absence_end_date := abs_rec.date_end;
676 g_map_calc.ESTIMATED_IJSS := abs_rec.abs_information7;
677 g_map_calc.GI_ELIGIBLE := abs_rec.abs_information3;
678 g_map_calc.births := nvl(fnd_number.canonical_to_number
679 (abs_rec.abs_information6),1);
680 g_map_calc.birth_date := nvl(fnd_date.canonical_to_date
681 (abs_rec.abs_information4), hr_general.end_of_time) ;
682 g_map_calc.ELIG_IJSS_HOURS := abs_rec.abs_information8;
683 g_map_calc.ELIG_IJSS_CONTRIB := fnd_number.canonical_to_number
684 (abs_rec.abs_information9) ;
685 hr_utility.set_location('INIT_MAP',50);
686
687 else -- }{ processed absence is child fetch parent
688 g_map_calc.parent_absence_id := to_number(abs_rec.abs_information1);
689
690 hr_utility.set_location('INIT_MAP'|| g_map_calc.parent_absence_id,50);
691
692 open c_get_absence(g_map_calc.parent_absence_id);
693 fetch c_get_absence into abs_rec.date_start,
694 abs_rec.date_end,
695 abs_rec.duration,
696 abs_rec.person_id,
697 abs_rec.abs_information_category,
698 abs_rec.abs_information1,
699 abs_rec.abs_information2,
700 abs_rec.abs_information3,
701 abs_rec.abs_information4,
702 abs_rec.abs_information5,
703 abs_rec.abs_information6,
704 abs_rec.abs_information7,
705 abs_rec.abs_information8,
706 abs_rec.abs_information9,
707 abs_rec.abs_information10;
708
709 if c_get_absence%NOTFOUND then
710 close c_get_absence;
711 hr_utility.set_message (801, 'PAY_75031_INVALID_LINK_ABS');
712 hr_utility.raise_error;
713 end if;
714
715 close c_get_absence;
716
717 g_map_calc.parent_absence_start_date := abs_rec.date_start;
718 g_map_calc.parent_absence_end_date := abs_rec.date_end;
719 g_map_calc.ESTIMATED_IJSS := abs_rec.abs_information7;
720 g_map_calc.GI_ELIGIBLE := abs_rec.abs_information3;
721 g_map_calc.births := nvl(fnd_number.canonical_to_number
722 (abs_rec.abs_information6),1);
723 g_map_calc.birth_date := nvl(fnd_date.canonical_to_date
724 (abs_rec.abs_information4),hr_general.end_of_time);
725 g_map_calc.ELIG_IJSS_HOURS := abs_rec.abs_information8;
726 g_map_calc.ELIG_IJSS_CONTRIB := fnd_number.canonical_to_number
727 (abs_rec.abs_information9) ;
728 hr_utility.set_location('INIT_MAP',70);
729
730 end if ; -- } fetch parent
731 end if; -- } maternity
732
733 if abs_rec.abs_information_category = 'FR_FR_ADOPTION'
734 -- { Adoption assign values into calc structure
735 then
736 g_map_calc.parent_absence_id := abs_rec.absence_id;
737 g_map_calc.parent_absence_start_date := abs_rec.date_start;
738 g_map_calc.parent_absence_end_date := abs_rec.date_end;
739 g_map_calc.ESTIMATED_IJSS := abs_rec.abs_information4;
740 g_map_calc.GI_ELIGIBLE := abs_rec.abs_information2;
741 g_map_calc.births := nvl(fnd_number.canonical_to_number
742 (abs_rec.abs_information3),1);
743 g_map_calc.birth_date := nvl(fnd_date.canonical_to_date
744 (abs_rec.abs_information1), hr_general.end_of_time) ;
745 g_map_calc.ELIG_IJSS_HOURS := abs_rec.abs_information5;
746 g_map_calc.ELIG_IJSS_CONTRIB := fnd_number.canonical_to_number
747 (abs_rec.abs_information6) ;
748 g_map_calc.SPOUSES_LEAVE := fnd_number.canonical_to_number
749 (abs_rec.abs_information7) ;
753
750 hr_utility.set_location('INIT_MAP',80);
751
752 end if; -- } adoption
754 if abs_rec.abs_information_category = 'FR_FR_PATERNITY'
755 -- { Paternity assign values into calc structure
756 then
757 g_map_calc.parent_absence_id := abs_rec.absence_id;
758 g_map_calc.parent_absence_start_date := abs_rec.date_start;
759 g_map_calc.parent_absence_end_date := abs_rec.date_end;
760 g_map_calc.ESTIMATED_IJSS := abs_rec.abs_information4;
761 g_map_calc.GI_ELIGIBLE := abs_rec.abs_information2;
762 g_map_calc.births := nvl(fnd_number.canonical_to_number
763 (abs_rec.abs_information3),1);
764 g_map_calc.birth_date := nvl(fnd_date.canonical_to_date
765 (abs_rec.abs_information1), hr_general.end_of_time) ;
766 g_map_calc.ELIG_IJSS_HOURS := abs_rec.abs_information5;
767 g_map_calc.ELIG_IJSS_CONTRIB := fnd_number.canonical_to_number
768 (abs_rec.abs_information6) ;
769 hr_utility.set_location('INIT_MAP',90);
770
771 end if; -- } end Paternity
772
773 hr_utility.set_location('INIT_MAP',100);
774
775 return 'Y';
776
777 end init_map_absence;
778
779 --
780 FUNCTION init_cpam_absence(
781 P_Assignment_id IN Number,
782 P_element_entry_id IN Number,
783 P_date_earned IN Date,
784 p_business_group_id IN Number,
785 p_payroll_id IN Number,
786 p_assignment_action_id IN Number,
787 p_element_type_id IN Number,
788 p_payment_from_date IN Date,
789 p_payment_to_date IN Date,
790 p_days IN Number,
791 p_gross_amount IN Number,
792 p_net_amount IN Number,
793 p_gross_daily_rate IN Number)
794
795 RETURN Varchar2 is
796
797 cursor c_get_absence(p_assignment_id number,
798 p_date_earned date,
799 p_payment_from_date date) is
800 select paa.absence_attendance_id
801 , paa.date_start
802 , paa.date_end
803 , decode(paa.abs_information_category,'FR_M',to_number(paa.abs_information1)) parent_absence_id
804 , decode(paa.abs_information_category,'FR_M',NVL(paa.abs_information7,'N'),NVL(paa.abs_information4,'N')) estimated_ijss
805 from per_absence_attendances paa
806 ,per_assignments_f paf
807 where paf.assignment_id = p_assignment_id
808 and paf.person_id = paa.person_id
809 and p_date_earned between
810 paf.effective_start_date and paf.effective_end_date
811 and p_payment_from_date between paa.date_start and paa.date_end
812 and paa.abs_information_category IN ('FR_M','FR_FR_PATERNITY','FR_FR_ADOPTION');
813
814 cursor c_get_parent_absence(p_absence_attendance_id number) is
815 select paa.abs_information7 estimated_ijss
816 from per_absence_attendances paa
817 where paa.absence_attendance_id = p_absence_attendance_id;
818
819 TYPE t_absence is RECORD
820 (absence_attendance_id number
821 ,parent_absence_id number
822 ,date_start date
823 ,date_end date
824 ,estimated_ijss varchar2(30)
825 );
826 --
827 abs_rec t_absence;
828 parent_abs_rec t_absence;
829 --
830 l_proc varchar2(72) := g_package||'init_absence';
831
832 begin
833
834 hr_utility.set_location('INIT_CPAM',10);
835
836 -- fetch the details of the absence looking for a maternity absence that is current
837 -- on the payment_start_date. If no maternity is found raise error. If maternity
838 -- exists but has been estimated then skip. If the absence fetched is a child absence
839 -- fetch the parent and read the estimted_ijss flag from that
840 begin
841 open c_get_absence(p_assignment_id, p_date_earned, p_payment_from_date);
842 fetch c_get_absence into abs_rec.absence_attendance_id,
843 abs_rec.date_start,
844 abs_rec.date_end,
845 abs_rec.parent_absence_id,
846 abs_rec.estimated_ijss;
847 if c_get_absence%notfound THEN
848 hr_utility.set_message (801, 'PAY_75049_IJSS_NO_ABSENCE');
849 hr_utility.raise_error;
850 end if;
851 close c_get_absence;
852
853 g_map_arch.element_type_id := p_element_type_id;
854 g_map_arch.payroll_id := p_payroll_id;
855 g_map_arch.assignment_action_id := p_assignment_action_id;
856 g_map_arch.business_group_id := p_business_group_id;
857 g_map_calc.start_date := p_payment_from_date;
858 g_map_calc.end_date := p_payment_to_date;
859 g_map_calc.ijss_gross_start_date := p_payment_from_date;
860 g_map_calc.ijss_gross_end_date := p_payment_to_date;
861 g_map_calc.ijss_gross_rate := p_gross_daily_rate;
862 g_map_calc.ijss_gross_days := p_days;
863 g_map_calc.ijss_gross := p_gross_amount;
864 g_map_calc.ijss_net_payment := p_net_amount;
865 g_map_calc.initiator := 'CPAM';
866
867 hr_utility.set_location('INIT_CPAM',30);
868
869 end;
870
871 -- set the columns that are relevent to the child absence/same value for child
872 -- and parent
873 g_map_calc.absence_id := abs_rec.absence_attendance_id;
874 g_map_calc.initiator := 'CPAM';
878 close c_get_parent_absence;
875 if abs_rec.parent_absence_id is not null then
876 open c_get_parent_absence (abs_rec.parent_absence_id);
877 fetch c_get_parent_absence into abs_rec.estimated_ijss;
879 end if;
880
881 hr_utility.set_location('INIT_CPAM',50);
882
883 g_map_calc.estimated_ijss := abs_rec.estimated_ijss;
884
885 -- if the absence is already estimated then don't iterate and skip
886 -- if the absence is not estimated then iteration is required
887 if (g_map_calc.ESTIMATED_IJSS = 'N'and g_map_calc.ijss_gross > 0 and g_map_calc.ijss_net_payment > 0) then
888 g_map_calc.IJSS_ADJUSTMENT := 'Y';
889 end if;
890
891 hr_utility.set_location('INIT_CPAM',100);
892
893 return g_map_calc.estimated_ijss;
894
895 end init_cpam_absence;
896
897 FUNCTION get_map_skip
898 RETURN Varchar2 is
899
900 begin
901
902 hr_utility.set_location('SKIP',10);
903
904 return g_map_calc.estimated_ijss;
905
906 end get_map_skip;
907
908 --
909
910 FUNCTION iterate(
911 P_Assignment_id IN Number,
912 P_element_entry_id IN Number,
913 P_date_earned IN Date,
914 p_net_pay IN Number,
915 p_stop_processing OUT NOCOPY Varchar2)
916
917 RETURN Number is
918
919 BEGIN
920
921 --
922 -- Checking for change in assignment, absence and initialising Variables
923 --
924 hr_utility.set_location('Iterate',10);
925
926 IF (NVL(g_map_arch.assignment_id, -1) <> p_assignment_id) OR
927 (g_map_arch.element_entry_id IS NULL) OR
928 (g_map_arch.date_earned <> p_date_earned) THEN
929
930 g_map_arch.assignment_id := p_assignment_id;
931 g_map_arch.date_earned := p_date_earned;
932 g_map_arch.element_entry_id := p_element_entry_id;
933 g_map_arch.net := p_net_pay;
934 hr_utility.set_location('actual net'|| p_net_pay,20);
935 hr_utility.set_location('Iterate',20);
936
937 IF g_map_calc.initiator <> 'CPAM' THEN
938 calc_map();
939 END IF;
940
941 ELSE
942 increment_iteration;
943
944 hr_utility.set_location('arch net'|| g_map_arch.net,30);
945 hr_utility.set_location('actual net'|| p_net_pay,30);
946
947 IF nvl(g_map_calc.ijss_adjustment,'N') <> 'Y' OR
948 (g_map_arch.net + cs_MARGIN >= p_net_pay AND
949 g_map_arch.net - cs_MARGIN <= p_net_pay)THEN
950
951 hr_utility.set_location('Iterate',30);
952
953 reset_data_structures;
954 p_stop_processing := 'Y';
955 ELSE
956 set_adjustment(p_net_pay);
957 END IF;
958
959 END IF;
960
961 hr_utility.set_location('Iterate',100);
962
963 RETURN 0;
964 EXCEPTION
965 WHEN OTHERS THEN
966 hr_utility.set_location('iterate ',-10);
967 hr_utility.trace(SQLCODE);
968 hr_utility.trace(SQLERRM);
969 hr_utility.trace_off;
970 RAISE;
971
972 end iterate;
973
974 --
975 -- Create Indirect Elements for Process
976 --
977
978 FUNCTION indirects
979 ( p_absence_id out nocopy number,
980 p_ijss_gross out nocopy number,
981 p_ijss_gross_rate out nocopy number,
982 p_ijss_gross_base out nocopy number,
983 p_ijss_gross_start_date out nocopy date,
984 p_ijss_gross_end_date out nocopy date,
985 p_ijss_estmtd out nocopy varchar2,
986 p_ijss_net_payment out nocopy number,
987 p_map_deduction out nocopy number,
988 p_map_deduction_rate out nocopy number,
989 p_map_deduction_base out nocopy number,
990 p_map_deduct_start_date out nocopy date,
991 p_map_deduct_end_date out nocopy date,
992 p_map_gi_payment out nocopy number,
993 p_map_ijss_adjustment out nocopy number)
994
995 RETURN Number is
996
997 begin
998
999 hr_utility.set_location('Indirect',10);
1000
1001 p_absence_id := g_map_calc.absence_id;
1002 p_ijss_gross := g_map_calc.ijss_gross;
1003 p_ijss_gross_rate := g_map_calc.ijss_gross_days;
1004 p_ijss_gross_base := g_map_calc.ijss_gross_rate;
1005 p_ijss_gross_start_date := g_map_calc.ijss_gross_start_date;
1006 p_ijss_gross_end_date := g_map_calc.ijss_gross_end_date;
1007 p_ijss_estmtd := g_map_calc.estimated_ijss;
1008 p_ijss_net_payment := g_map_calc.ijss_net_payment;
1009 p_map_deduction := g_map_calc.deduction;
1010 p_map_deduction_rate := g_map_calc.deduction_rate;
1011 p_map_deduction_base := g_map_calc.deduction_base;
1012 p_map_deduct_start_date := g_map_calc.deduction_start_date;
1013 p_map_deduct_end_date := g_map_calc.deduction_end_date;
1014 p_map_gi_payment := g_map_calc.gi_payment;
1015 p_map_ijss_adjustment := g_map_calc.gi_ijss_adj;
1016
1017 hr_utility.set_location('Iterate',100);
1018
1019 return 0;
1020
1021 end indirects;
1022
1023 --
1024 -- Maternity Element has been processed to completion so clear down the data
1025 -- structures in preparation for a new Insurance Element NB.
1026 --
1027
1028 PROCEDURE reset_data_structures IS
1029 BEGIN
1030
1031 hr_utility.set_location('Reset Data Str',10);
1032
1033 g_map_calc := blank_map_calc;
1034 g_map_arch := blank_map_arch;
1035 g_ctl := blank_ctl;
1036
1037 hr_utility.set_location('Reset Data Str',100);
1038 END reset_data_structures;
1039
1040 --
1041 -- Increments the iteration.
1042 --
1043 --
1044
1045 PROCEDURE increment_iteration IS
1046 BEGIN
1047 hr_utility.set_location('Increment Iteration',10);
1048 g_ctl.iter := g_ctl.iter + 1;
1049 hr_utility.set_location('Increment Iteration',100);
1050 END increment_iteration;
1051
1052 --
1053 --
1054 -- Sets an adjustment as required for the processing of the current guarantee.
1055 --
1056 --
1057
1058 PROCEDURE set_adjustment
1059 (p_net_pay NUMBER) IS
1060 --
1061 --
1062 -- Local variables
1063 --
1064 l_dummy NUMBER;
1065 l_target_net NUMBER;
1066 l_diff NUMBER;
1067 l_map_adj NUMBER;
1068 l_init NUMBER := 0;
1069 BEGIN
1070 hr_utility.set_location('Set Adjustment',10);
1071 --
1072 --
1073 -- Get the target net for the current guarantee.
1074 --
1075 l_target_net := nvl(g_map_arch.net,0);
1076
1077 select decode(l_target_net,0,1,l_target_net) into l_init from dual;
1078
1079 hr_utility.set_location('Set Adjustment target net: ' || l_target_net,15);
1080 hr_utility.set_location('Set Adjustment init: ' || l_init,17);
1081
1082 --
1083 -- There has not been an adjustment so set an initial value.
1084 --
1085 IF g_map_calc.gi_ijss_adj IS NULL THEN
1086
1087 hr_utility.set_location('IJSS Adjustment: ' ||g_map_calc.gi_ijss_adj,19);
1088
1089 l_dummy := pay_iterate.initialise(g_map_arch.element_entry_id, l_init, - 1 * l_init, l_init);
1090
1091 l_map_adj := pay_iterate.get_interpolation_guess(g_map_arch.element_entry_id, 0) ;
1092 hr_utility.set_location('Set Adjustment',20);
1093 --
1094 --
1095 -- Refine the adjustment.
1096 --
1097 ELSE
1098 hr_utility.set_location('IJSS Adjustment: ' ||g_map_calc.gi_ijss_adj,29);
1099 l_diff := l_target_net - p_net_pay;
1100 l_map_adj := pay_iterate.get_interpolation_guess(g_map_arch.element_entry_id, l_diff);
1101
1102 END IF;
1103 --
1104 --
1105 -- Set the maternity adjustment.
1106 --
1107 g_map_calc.gi_ijss_adj := l_map_adj;
1108 hr_utility.set_location('Set Adjustment',100);
1109
1110 END set_adjustment;
1111
1112 --
1113 END PAY_FR_MAP_CALC;