1 PACKAGE BODY PAY_FR_SICKNESS_CALC AS
2 /* $Header: pyfrsick.pkb 120.0 2005/05/29 05:10:20 appldev noship $ */
3
4 -----------------------------------------------------------------------
5 -- Date Author Comments
6 -- 17-9-02 Aditya T. Initial version of package with programs reqd by Absence report and IJSS calc
7 -- 24-9-02 ASNELL Added stubs for function calls
8 -- 30-9-02 Satyajit Added procedure Calculate_Sickness_Deduction
9 -- and function Get_Open_Days
10 -- 30-9-02 ASNELL modified stub calls
11 -- 07-10-02 ABhaduri Modified functions for IJSS and
12 -- g_overlap declaration
13 -- 08-10-02 ABhaduri Added procedure Get_abs_print_flg
14 -- For checking validity and eligibility
15 -- absences from the report.
16 -- 08-10-02 ASNELL Added function get sickness_skip
17 -- 09-10-02 jrhodes Completed calc_sickness
18 -- get_sickness_skip
19 -- compare_guarantee
20 -- Get_backdated_payments
21 -- get_Reference_salary
22 -- 09-10-02 115.11 jrhodes Completed calc_ijss_gross
23 -- Included temproray FR_ROLLING_BALANCE
24 -- 09-10-02 115.12 jrhodes Completed FR_ROLLING_BALANCE
25 -- 09-10-02 115.13 jrhodes Changed message name
26 -- 10-10-02 asnell added get_gi_payments_audit
27 -- 11-10-02 115.18 autiwari Complete version of CALC_LEGAL_GI
28 -- 11-10-02 115.18 jheer Initial version of Get_GI_BANDS_audit
29 -- (uses Band_overlaps)
30 -- Compliant with header ver 115.13
31 -- 14-10-02 115.19 jheer Added procedure "Enter" and "Leave" trace statements.
32 -- 14-10-02 115.20 autiwari Added private function Get_GI_ref_salary_divisor
33 -- Modified Sickness_Calc for No_G(corrected index and removed call to IJSS_Gross)
34 -- 14-10-02 115.22 vsjain Changes in calc_legal after testing
35 -- 14-10-02 115.23 asnell added get_ben cusor to calc_cagr_gi
36 -- 15-10-02 115.24 autiwari Completed procedure Calc_CAGR_GI
37 -- Added private function Use_GI_bands
38 -- Modifications to Calc_LEGAL_GI
39 -- (population of cagr_id and type in g_coverages
40 -- and band usage uses Use_GI_Bands and l_bands table)
41 -- Asnell: Added function Get_CAGR_reference_salary
42 -- 16-10-02 115.25 autiwari Minor fixes to Use_GI_Bands and Calc_IJSS_Gross
43 -- 16-10-02 115.26 asnell wrong input name in get_gi_payments_audit
44 -- 16-10-02 115.28 autiwari Redundant declarations removed
45 -- Calc_CAGR_GI--changed date_to passed to Bands_audit
46 -- 17-10-02 115.29 autiwari Calc_CAGR_GI--Asnell:Changed cursor 'get_ben'
47 -- and related conditional logic to get_benefits
48 -- Calc_IJSS--Commented out Null Ref Salary error mssgs
49 -- 18-10-02 115.30 autiwari Calc_IJSS--Debugging changes
50 -- G_overlap population only when absence is eligible
51 -- Logic to modify End_date(p_end_date)
52 -- Balance get_value calls changed to date mode
53 -- 22-10-02 115.33 autiwari Calc_CAGR_GI - Modified to correct NET for g_coverages
54 -- (to include pymnt for non-absence days)
55 -- 24-10-02 115.36 abhaduri Modified get abs_print_flg to
56 -- return the last absence date.
57 -- 25-10-02 115.38 autiwari Modified Calc_Cagr_Gi after debugging
58 -- 30-10-02 115.41 autiwari Modified Calc_IJSS: Refined Eligibility checking to daily
59 -- 05-11-02 115.46 autiwari Revoked 115.45 changes and corrected Get_sickness_skip to
60 -- return correct previous duration (spclly long absences)
61 -- 06-11-02 115.51 autiwari Modified Calc_IJSS for retrieving rates throughout the absence duration
62 -- when duration crosses the boundary days for bug #2651295
63 -- Replaced local UDT function with hruserdt
64 -- 07-11-02 115.52 autiwari Bugfix 2659924:Commented out IJSS Ineligibility Mssg
65 -- PAY_75021_ABS_INELIG_FOR_IJSS
66 -- 07-11-02 115.57 autiwari Bugfix 2651568:Zero divisor for ppl hired on 1st day of mth
67 -- 08-11-02 115.60 autiwari Bugfix 2661851:2nd absence in period has incorrect days processed in guarantee
68 -- 11-11-02 115.61 jheer Bugfix 2662162: corrected input value names for bands in band_overlaps
69 -- altered rolling year calculation so that it does not add 1
70 -- 12-11-02 115.63 jheer Bugfix 2662195 The dates being passed to the get_gi_bands routine were incorrect.
71
72 -- 29-11-02 115.70 kavenkat Bug:2683421:Created a private procedure absence_not_processed to raise error
73 -- Bug:2683421:if unrocessed sickness absence exists.
74 -- 02-12-02 115.71 kavenkat Closed the cursor.
75 -- 04-12-02 115.72 kavenkat Bug:2683421:Removed the payroll id check in the cursor csr_absence_not_processsed.
76 -- 11-12-02 115.73 kavenkat Bug:2706983:Introduced by 2683421.Errors for multiple absences in a pay period.
77 -- 24-12-02 115.76 abhaduri Modifications for CPAM processing
78 -- Added 3 new functions/procedures.
79 -- Modified existing func/procs.
80 -- 21-01-03 115.79 abhaduri Modified get_sickness_cpam_ijss
81 -- for bug 2751760.
82 -- 12-02-03 115.79 asnell added FR_MAP_IJSS_REFERENCE_SALARY to get_reference_salary
83 -- added maternity extensions processing
84 -- fixes for bug 2763291 max ijss
85 -- 26-02-03 115.81 abhaduri Modified absence_not_processed
86 -- for bug 2791833 to check for
87 -- payments for absences with IJSS
88 -- estimate as 'N'.
89 -- 26-03-03 115.82 abhaduri Incorporated review comments.
90 -- Removed joins with pay_element_links
91 -- and element entries.
92 -- 04-08-03 115.83 asnell chenges for retro sickness
93 -- 05-08-03 115.84 asnell added concatenated_input / result
94 -- functions to help report retro
95 -- results
96 -- 04-09-03 115.98 asnell changed csr_get_entry_processed to not
97 -- check action status as the action may
98 -- still be processing
99 -- 03-11-03 115.100 asnell 3227237 when fetching results ensure status is
100 -- checked as retro no longer deletes
101 -- rolled back results.
102 -- 09-12-03 115.101 asnell 3274341 retro for audit on a seperate element
103 -- so fetch extended to look for results of
104 -- FR_SICKNESS_GI_INFO_RETRO
105 -- 17-12-03 115.101 asnell 3221356 performance bug for 11.5.10 remove redundent
106 -- join to element_links in csr_get_CPAM_results
107 -- change to get_sickness_cpam_skip to use
108 -- person_id index
109 -- 23-12-03 115.102 asnell 3331014 removed rule hint from get_gi_bands_audit
110 -- 31-03-04 115.105 autiwari 3545189 Assigning context PAYROLL_ACTION_ID and ELEMENT_TYPE_ID
111 -- to deduction_formula in Get_sickness_deduction
112 -- 06-05-04 115.107 aparkes 3594040 Removed MJC from cursor in
113 -- get_gi_bands_audit()
114 -- More changes for bug 3274341 (further
115 -- to those in 115.104)
116 -- 07-05-04 115.108 aparkes workaround inconsistency in pl/sql vs.
117 -- sql in 8.1.7.4
118 -- 21-06-04 115.109 abhaduri Modifeid calc_sickness procedure to
119 -- pass values to partial and unpaid days
120 -- balances.
121 -- 09-08-04 115.110 abhaduri Modified 'IJSS_Eligibility_Working_hours
122 -- function, substituted the value of new
123 -- balance FR_ACTUAL_HRS_WORKED_IJSS
124 -- for working time changes.
125 -----------------------------------------------------------------------
126 -- PACKAGE GLOBALS
127 g_ijss_refsal_defbal_id number;
128 g_map_refsal_defbal_id number;
129
130 g_gi_info_element_type_id pay_element_types_f.element_type_id%type;
131 g_gi_info_absence_id_iv_id pay_input_values_f.input_value_id%type;
132 g_gi_info_guarantee_type_iv_id pay_input_values_f.input_value_id%type;
133 g_gi_info_guarantee_id_iv_id pay_input_values_f.input_value_id%type;
134 g_gi_info_gi_payment_iv_id pay_input_values_f.input_value_id%type;
135 g_gi_info_net_iv_id pay_input_values_f.input_value_id%type;
136 g_gi_info_ijss_gross_iv_id pay_input_values_f.input_value_id%type;
137 g_gi_info_adjustment_iv_id pay_input_values_f.input_value_id%type;
138 g_gi_info_best_method_iv_id pay_input_values_f.input_value_id%type;
139 g_gi_info_start_date_iv_id pay_input_values_f.input_value_id%type;
140 g_gi_info_end_date_iv_id pay_input_values_f.input_value_id%type;
141 g_gi_info_band1_iv_id pay_input_values_f.input_value_id%type;
142 g_gi_info_band2_iv_id pay_input_values_f.input_value_id%type;
143 g_gi_info_band3_iv_id pay_input_values_f.input_value_id%type;
144 g_gi_info_band4_iv_id pay_input_values_f.input_value_id%type;
145
146 g_gi_i_r_element_type_id pay_element_types_f.element_type_id%type;
147 g_gi_i_r_absence_id_iv_id pay_input_values_f.input_value_id%type;
148 g_gi_i_r_guarantee_type_iv_id pay_input_values_f.input_value_id%type;
149 g_gi_i_r_guarantee_id_iv_id pay_input_values_f.input_value_id%type;
150 g_gi_i_r_gi_payment_iv_id pay_input_values_f.input_value_id%type;
151 g_gi_i_r_net_iv_id pay_input_values_f.input_value_id%type;
152 g_gi_i_r_ijss_gross_iv_id pay_input_values_f.input_value_id%type;
153 g_gi_i_r_adjustment_iv_id pay_input_values_f.input_value_id%type;
154 g_gi_i_r_best_method_iv_id pay_input_values_f.input_value_id%type;
155 g_gi_i_r_start_date_iv_id pay_input_values_f.input_value_id%type;
156 g_gi_i_r_end_date_iv_id pay_input_values_f.input_value_id%type;
157 g_gi_i_r_band1_iv_id pay_input_values_f.input_value_id%type;
158 g_gi_i_r_band2_iv_id pay_input_values_f.input_value_id%type;
159 g_gi_i_r_band3_iv_id pay_input_values_f.input_value_id%type;
160 g_gi_i_r_band4_iv_id pay_input_values_f.input_value_id%type;
161
162 g_ben_element_type_id pay_element_types_f.element_type_id%type;
163 g_ben_absence_id_iv_id pay_input_values_f.input_value_id%type;
164 g_ben_guarantee_id_iv_id pay_input_values_f.input_value_id%type;
165 g_ben_guarantee_type_iv_id pay_input_values_f.input_value_id%type;
166 g_ben_waiting_days_iv_id pay_input_values_f.input_value_id%type;
167 g_ben_duration_iv_id pay_input_values_f.input_value_id%type;
168 g_ben_band1_iv_id pay_input_values_f.input_value_id%type;
169 g_ben_band1_rate_iv_id pay_input_values_f.input_value_id%type;
170 g_ben_band2_iv_id pay_input_values_f.input_value_id%type;
171 g_ben_band2_rate_iv_id pay_input_values_f.input_value_id%type;
172 g_ben_band3_iv_id pay_input_values_f.input_value_id%type;
173 g_ben_band3_rate_iv_id pay_input_values_f.input_value_id%type;
174 g_ben_band4_iv_id pay_input_values_f.input_value_id%type;
175 g_ben_band4_rate_iv_id pay_input_values_f.input_value_id%type;
176 g_ben_balance_iv_id pay_input_values_f.input_value_id%type;
177
178 -- Used for LEGI and CAGR
179 TYPE bands_rec IS RECORD
180 (band_payment NUMBER,
181 band_rate NUMBER,
182 band_days NUMBER,
183 band_from_dt DATE,
184 band_to_dt DATE);
185
186 TYPE t_bands IS TABLE OF bands_rec INDEX BY BINARY_INTEGER;
187 l_bands t_bands;
188
189
190 --
191 g_package varchar2(33) := ' PAY_FR_SICKNESS_CALC.';
192 --
193
194 --
195 -- PUBLIC FUNCTIONS
196 --
197 -- Parameter p_long_absence denotes if the absence has a duration more than 180 calendar days
198 -- For Absence report, this parameter will always be FALSE as only normal case is considered
199 --
200 FUNCTION IJSS_Eligibility_Working_hours(
201 P_Legislation_code IN Varchar2 := 'FR',
202 P_Business_group_id IN Number,
203 P_Assignment_id IN Number,
204 P_Absence_start_date IN Date,
205 P_long_absence IN Boolean)
206 RETURN Varchar2 IS
207 --
208 l_bal_date_to Date;
209 l_bal_date_from Date;
210 l_ref_from_date Date;
211 l_rolling_hrs_bal Number;
212 l_ref_hrs Number;
213 l_hire_date Date;
214
215 l_proc varchar2(72) := g_package||'IJSS_Eligibility_Working_hours';
216
217 -- Cursor to retrieve person's hiredate
218 Cursor csr_hiredate IS
219 SELECT papf.original_date_of_hire
220 FROM per_all_people_f papf,
221 per_all_assignments_f pasg
222 WHERE pasg.assignment_id = p_assignment_id
223 AND pasg.Business_group_id= p_business_group_id
224 AND p_absence_start_date BETWEEN pasg.effective_start_date and pasg.effective_end_date
225 AND papf.person_id = pasg.person_id
226 AND papf.Business_group_id=p_Business_group_id
227 AND p_absence_start_date BETWEEN papf.effective_start_date and papf.effective_end_date;
228
229 -- Cursor for fetching global value
230 Cursor csr_global_value(c_global_name VARCHAR2,c_date_earned DATE) IS
231 SELECT global_value
232 FROM ff_globals_f
233 WHERE global_name = c_global_name
234 AND legislation_code = 'FR'
235 AND c_date_earned BETWEEN effective_start_date AND effective_end_date;
236 --
237 BEGIN
238
239 hr_utility.set_location('Entering:'|| l_proc, 10);
240
241 -- Setting up reference dates required while calculating eligibility
242 -- Get last day of previous calendar month
243 l_bal_date_to := LAST_DAY(ADD_MONTHS(p_absence_start_date,-1)) ;
244 IF NOT(p_long_absence) THEN --Absence shorter than 180 days
245 -- function calculates Working hours contributions over last 3 months
246 -- 1st day of 3 calendar months prior to above date
247 l_bal_date_from := TRUNC(ADD_MONTHS(p_absence_start_date, -3), 'MONTH');
248 l_ref_from_date := l_bal_date_from;
249 -- Get rolling balances
250 l_rolling_hrs_bal := FR_ROLLING_BALANCE
251 (p_assignment_id,
252 'FR_ACTUAL_HRS_WORKED_IJSS',-- name modified for time analysis changes
253 l_bal_date_from,
254 l_bal_date_to);
255 -- IF hire_date of person lies between the l_bal_date_to and l_bal_date_from dates
256 -- factor the above balance up for 90 days, using the 30 day calendar period.
257 OPEN csr_hiredate;
258 FETCH csr_hiredate INTO l_hire_date;
259 CLOSE csr_hiredate;
260 IF l_hire_date > l_bal_date_from AND (l_bal_date_to - l_hire_date +1) <> 0 THEN --Bug #2651568
261 l_rolling_hrs_bal := (l_rolling_hrs_bal/(l_bal_date_to - l_hire_date +1)) * 90 ;
262 END IF;
263 --
264 -- Fetch reference contributions from global
265 OPEN csr_global_value ('FR_WORKING_HOURS_MINIMUM_FOR_IJSS',l_ref_from_date);
266 FETCH csr_global_value INTO l_ref_hrs;
267 CLOSE csr_global_value;
268 --
269 IF l_rolling_hrs_bal < l_ref_hrs THEN
270 RETURN 'N';
271 ELSE
272 RETURN 'Y';
273 END IF;
274 ELSE
275 -- For absences longer than 180 days,
276 -- function calculates working hours balance over last 12 calendar mths
277 -- 1st day of 12 calendar months prior to above date
278 l_bal_date_from := TRUNC(ADD_MONTHS(p_absence_start_date,-12), 'MONTH');
279 l_ref_from_date := TRUNC(l_bal_date_from,'YEAR');
280 -- Get rolling balances
281 l_rolling_hrs_bal := FR_ROLLING_BALANCE
282 (p_assignment_id,
283 'FR_ACTUAL_HRS_WORKED_IJSS',-- name modified for time analysis changes
284 l_bal_date_from,
285 l_bal_date_to);
286 -- IF hire_date of person lies between the l_bal_date_to and l_bal_date_from dates
287 -- factor the above balance up for 360 days, using the 30 day calendar period.
288 OPEN csr_hiredate;
289 FETCH csr_hiredate INTO l_hire_date;
290 CLOSE csr_hiredate;
291 IF l_hire_date > l_bal_date_from AND (l_bal_date_to - l_hire_date +1) <> 0 THEN --Bug #2651568
292 l_rolling_hrs_bal := (l_rolling_hrs_bal/ (l_bal_date_to - l_hire_date +1)) * 360;
293 END IF;
294 --
295 -- Fetch reference contributions from global
296 OPEN csr_global_value ('FR_WORKING_HOURS_MINIMUM_FOR_IJSS',l_ref_from_date);
297 FETCH csr_global_value INTO l_ref_hrs;
298 CLOSE csr_global_value;
299 l_ref_hrs := 4 * l_ref_hrs;
300 --
301 IF l_rolling_hrs_bal < l_ref_hrs THEN -- Also, working hours check for 12 mths
302 RETURN 'N';
303 ELSE
304 RETURN 'Y';
305 END IF;
306 END IF;
307
308 hr_utility.set_location(' Leaving:'||l_proc, 70);
309
310 END IJSS_Eligibility_Working_hours;
311 --
312
313 FUNCTION IJSS_Eligibility_SMID(
314 P_Legislation_code IN Varchar2 := 'FR',
315 P_Business_group_id IN Number,
316 P_Assignment_id IN Number,
317 P_Absence_start_date IN Date,
318 P_long_absence IN Boolean)
319 RETURN Number IS
320 --
321 l_bal_date_to Date;
322 l_bal_date_from Date;
323 l_rolling_SMID_balance Number;
324 l_hire_date Date;
325 o_hire_date Date;
326
327 l_proc varchar2(72) := g_package||'IJSS_Eligibility_SMID';
328
329 -- Cursor to retrive person's hiredate
330 Cursor csr_hiredate IS
331 SELECT papf.original_date_of_hire
332 FROM per_all_people_f papf,
333 per_all_assignments_f pasg
334 WHERE pasg.assignment_id = p_assignment_id
335 AND pasg.Business_group_id= p_Business_group_id
336 AND p_absence_start_date BETWEEN pasg.effective_start_date and pasg.effective_end_date
337 AND papf.person_id = pasg.person_id
338 AND papf.Business_group_id=p_Business_group_id
339 AND p_absence_start_date BETWEEN papf.effective_start_date and papf.effective_end_date;
340 --
341 BEGIN
342
343 --hr_utility.trace_on(NULL,'REQID');
344 hr_utility.set_location(' Entering'||l_proc, 10);
345
346 -- Setting up reference dates required while calculating eligibility
347 -- Get last day of previous calendar month
348 l_bal_date_to := LAST_DAY(ADD_MONTHS(p_Absence_start_date,-1)) ;
349 IF NOT(p_long_absence) THEN --Absence shorter than 180 days
350 -- function calculates SMID contributions over last 6 months
351 -- 1st day of 6 calendar months prior to above date
352 l_bal_date_from := TRUNC(ADD_MONTHS(p_Absence_start_date,-6), 'MONTH');
353 -- Get rolling balances
354 l_rolling_SMID_balance := FR_ROLLING_BALANCE
355 (p_assignment_id,
356 'FR_EE_SMID',
357 l_bal_date_from,
358 l_bal_date_to);
359
360 -- IF hire_date of person lies between the l_bal_date_to and l_bal_date_from dates
361 -- factor the above balance up for 180 days, using the 30 day calendar period.
362 OPEN csr_hiredate;
363 FETCH csr_hiredate INTO l_hire_date;
364 CLOSE csr_hiredate;
365
366 IF (l_hire_date > l_bal_date_from) AND (l_bal_date_to - l_hire_date +1) <> 0 THEN --Bug #2651568
367
368 l_rolling_SMID_balance := round((l_rolling_SMID_balance/(l_bal_date_to - l_hire_date +1)) * 180, 2);
369 END IF;
370 --
371 hr_utility.set_location(' Leaving:'||l_proc, 20);
372 RETURN l_rolling_SMID_balance;
373 ELSE
374 -- For absences longer than 180 days,
375 -- function calculates SMID contributions over last 12 calendar mths
376 -- 1st day of 12 calendar months prior to above date
377 l_bal_date_from := TRUNC(ADD_MONTHS(P_Absence_start_date,-12), 'MONTH');
378 -- Fetch SMID contributions over the reference period
379 l_rolling_SMID_balance := FR_ROLLING_BALANCE
380 (p_assignment_id,
381 'FR_EE_SMID',
382 l_bal_date_from,
383 l_bal_date_to);
384
385 -- IF hire_date of person lies between the l_bal_date_to and l_bal_date_from dates
386 -- factor the above balance up for 360 days, using the 30 day calendar period.
387 OPEN csr_hiredate;
388 FETCH csr_hiredate INTO l_hire_date;
389 CLOSE csr_hiredate;
390 IF l_hire_date > l_bal_date_from AND (l_bal_date_to - l_hire_date +1) <> 0 THEN --Bug #2651568
391 l_rolling_SMID_balance := round((l_rolling_SMID_balance/(l_bal_date_to - l_hire_date +1)) * 360, 2);
392 END IF;
393 --
394 hr_utility.set_location(' Leaving:'||l_proc, 30);
395 RETURN l_rolling_SMID_balance;
396 END IF;
397 --hr_utility.trace_off;
398 END IJSS_Eligibility_SMID;
399 --
400
401 -- Returns Amount of salary for a period
402 -- Sickness needs (Gross Salary - Professional Reductions)
403 -- Maternity needs (Gross Salary - New balance [Statutory deductions
404 -- +Conventional deductions+CSG-Non mandatory])
405 FUNCTION Get_Reference_salary(
406 P_Business_group_id IN Number,
407 P_Assignment_id IN Number,
408 P_Period_end_date IN Date,
409 P_Absence_category IN Varchar2)
410 RETURN Number IS
411 --
412 cursor c_get_defined_balance(p_balance_name varchar2) is
413 select db.defined_balance_id
414 from pay_defined_balances db
415 , pay_balance_dimensions bd
416 , pay_balance_types bt
417 where db.balance_type_id = bt.balance_type_id
418 and db.balance_dimension_id = bd.balance_dimension_id
419 and bt.balance_name = p_balance_name
420 and bt.legislation_code = 'FR'
421 and bd.database_item_suffix = '_ASG_PTD'
422 and bd.legislation_code = 'FR';
423 --
424 l_balance_value number;
425 l_defined_balance_id number;
426 l_target_net number;
427
428 l_proc varchar2(72) := g_package||'Get_Reference_salary';
429
430 --
431 begin
432
433 begin
434
435 hr_utility.set_location('Entering:'|| l_proc,10);
436 if p_absence_category = 'S' then
437
438 -- Check if g_ijss_refsal_defined_balance_id has been set,
439 -- if not fetch defined_balance_id for
440 -- FR_SICKNESS_IJSS_REFERENCE_SALARY_ASG_PTD and set
441 -- g_ijss_refsal_defbal_id
442 --
443 if g_ijss_refsal_defbal_id is not null then
444 l_defined_balance_id := g_ijss_refsal_defbal_id;
445 else
446 open c_get_defined_balance('FR_SICKNESS_IJSS_REFERENCE_SALARY');
447 fetch c_get_defined_balance into l_defined_balance_id;
448 close c_get_defined_balance;
449 --
450 g_ijss_refsal_defbal_id := l_defined_balance_id;
451 end if;
452 --
453 -- Fetch the value using core balance user exit in date mode:
454 BEGIN
455 l_balance_value :=
456 pay_balance_pkg.get_value(l_defined_balance_id
457 ,p_assignment_id
458 ,p_period_end_date);
459
460 hr_utility.set_location(' Leaving:'||l_proc, 70);
461 EXCEPTION
462 WHEN NO_DATA_FOUND THEN --Bug #2659884
463 l_balance_value := 0;
464 END;
465 end if;
466 if p_absence_category in ( 'M', 'FR_ADOPTION','FR_PATERNITY') then
467
468 -- Check if g_map_refsal_defined_balance_id has been set,
469 -- if not fetch defined_balance_id for
470 -- FR_MAP_IJSS_REFERENCE_SALARY
471 --
472 if g_map_refsal_defbal_id is null then
473 open c_get_defined_balance('FR_MAP_IJSS_REFERENCE_SALARY');
474 fetch c_get_defined_balance into g_map_refsal_defbal_id;
475 close c_get_defined_balance;
476 --
477 end if;
478 --
479 -- Fetch the value using core balance user exit in date mode:
480 BEGIN
481 l_balance_value :=
482 pay_balance_pkg.get_value(g_map_refsal_defbal_id
483 ,p_assignment_id
484 ,p_period_end_date);
485
486 hr_utility.set_location(' Leaving:'||l_proc, 72);
487 EXCEPTION
488 WHEN NO_DATA_FOUND THEN
489 l_balance_value := 0;
490 END;
491 end if;
492 return l_balance_value;
493
494 end get_Reference_salary;
495
496
497 return l_balance_value;
498
499 end get_Reference_salary;
500 --
501 --
502 -- Returns Amount received as backdated retro payments for the previous calendar year
503 -- Or for the calendar year before previous in a period,
504 -- Depending on the parameter 'calendar_year_before' either '1' or '2'
505 --
506 PROCEDURE Get_backdated_payments(
507 P_Business_group_id IN Number,
508 P_Assignment_id IN Number,
509 P_Period_end_date IN Date,
510 P_Absence_category IN Varchar2 default 'S',
511 p_payment_backyr_1 OUT NOCOPY Number,
512 p_payment_backyr_2 OUT NOCOPY Number) IS
513 --
514 l_start_of_year_1 date;
515 l_start_of_year_2 date;
516 l_end_of_years date;
517 l_balance_name varchar2(50);
518 l_pymt_yr1 number;
519 l_pymt_yr2 number;
520 --
521 cursor c_get_payment(c_balance_name varchar2,
522 c_start_yr1 date,
523 c_start_yr2 date,
524 c_end_yrs date) is
525 select sum(decode(trunc(spact.effective_date,'YYYY'),c_start_yr1,fnd_number.canonical_to_number(TARGET.result_value))) pymt_yr1,
526 sum(decode(trunc(spact.effective_date,'YYYY'),c_start_yr2,fnd_number.canonical_to_number(TARGET.result_value))) pymt_yr2
527 from pay_run_result_values TARGET
528 , pay_balance_feeds_f FEED
529 , pay_run_results RR
530 , pay_assignment_actions ASSACT
531 , pay_payroll_actions PACT
532 , pay_balance_types bal
533 --
534 , pay_assignment_actions sasact
535 , pay_payroll_actions spact
536 , pay_entry_process_details proc
537 where ASSACT.assignment_id = P_Assignment_id
538 and BAL.balance_name = c_balance_name
539 and BAL.balance_type_id = FEED.balance_type_id
540 and FEED.balance_type_id +0
541 = bal.balance_type_id + DECODE(TARGET.input_value_id,null,0,0)
542 and FEED.input_value_id = TARGET.input_value_id
543 and nvl(TARGET.result_value,'0') <> '0'
544 and TARGET.run_result_id = RR.run_result_id
545 and RR.assignment_action_id = ASSACT.assignment_action_id
546 and ASSACT.payroll_action_id = PACT.payroll_action_id
547 and PACT.effective_date
548 between FEED.effective_start_date and FEED.effective_end_date
549 and RR.status in ('P','PA')
550 and PACT.action_type <> 'V'
551 and NOT EXISTS
552 (SELECT NULL
553 FROM pay_payroll_actions RPACT
554 , pay_assignment_actions RASSACT
555 , pay_action_interlocks RINTLK
556 where ASSACT.assignment_action_id = RINTLK.locked_action_id
557 and RINTLK.locking_action_id = RASSACT.assignment_action_id
558 and RPACT.payroll_action_id = RASSACT.payroll_action_id
559 and RPACT.action_type = 'V' )
560 and PACT.effective_date
561 between trunc(p_period_end_date,'MM')
562 and last_day(p_period_end_date)
563 --
564 and sasact.payroll_action_id = spact.payroll_action_id
565 and spact.effective_date between c_start_yr2 and c_end_yrs
566 and proc.source_asg_action_id = sasact.assignment_action_id
567 and rr.element_entry_id = proc.element_entry_id
568 and proc.retro_component_id is not null
569 group by trunc(spact.effective_date,'YYYY');
570 --
571 --
572 l_proc varchar2(72) := g_package||'Get_backdated_payments';
573
574 begin
575 --
576 hr_utility.set_location('Entering:'|| l_proc,10);
577 -- Select the apt balance name (bug#3779780)
578 IF P_Absence_category = 'S' THEN
579 --
580 l_balance_name := 'FR_SICKNESS_IJSS_REFERENCE_SALARY';
581 --
582 ELSE
583 --
584 l_balance_name := 'FR_MAP_IJSS_REFERENCE_SALARY';
585 --
586 END IF;
587 --
588 l_start_of_year_1 := add_months(trunc(p_period_end_date,'YYYY'), -12);
589 l_start_of_year_2 := add_months(l_start_of_year_1, -12);
590 l_end_of_years := add_months(l_start_of_year_1,12) -1;
591 --
592 Open c_get_payment(l_balance_name,
593 l_start_of_year_1,
594 l_start_of_year_2,
595 l_end_of_years);
596 Fetch c_get_payment into l_pymt_yr1 ,l_pymt_yr2;
597 Close c_get_payment;
598 --
599 p_payment_backyr_1 := nvl(l_pymt_yr1,0);
600 p_payment_backyr_2 := nvl(l_pymt_yr2,0);
601 --
602 hr_utility.set_location(' Leaving:'||l_proc, 70);
603 end get_backdated_payments;
604 --
605 -- GET_OPEN_DAYS
606 -- Returns open days between two dates
607
608 FUNCTION Get_Open_Days
609 (p_start_date IN Date,
610 p_end_date IN Date ) RETURN Number is
611
612
613 l_proc varchar2(72) := g_package||'Get_Open_Days';
614
615 l_calendar_days Number;
616 l_open_days Number;
617
618 BEGIN
619 l_proc := g_package||'Get_open_days';
620 hr_utility.set_location('Entering:'|| l_proc,10);
621
622 l_calendar_days := p_end_date - p_start_date + 1;
623
624 l_open_days := l_calendar_days - CEIL ((l_calendar_days - mod(to_number(to_char(p_end_date,'J'))+1,7))/ 7);
625
626 hr_utility.set_location(' Leaving:'||l_proc, 70);
627
628 RETURN l_open_days;
629
630 END Get_Open_days;
631
632
633 -- CALC_SICKNESS_DEDUCTION
634 -- fires legislative or user formula as indicated on the establishment
635 -- to calculate the deduction for sickness absence
636
637 PROCEDURE Calculate_Sickness_Deduction
638 (p_absence_start_date IN date,
639 p_absence_end_date IN date,
640 p_asg IN pay_fr_sick_pay_processing.t_asg,
641 p_absence_arch IN OUT NOCOPY pay_fr_sick_pay_processing.t_absence_arch) IS
642
643 /* declare local variables */
644
645 l_inputs ff_exec.inputs_t;
646 l_outputs ff_exec.outputs_t;
647
648 l_proc varchar2(72) := g_package||'calculate_sickness_deduction';
649
650 BEGIN
651
652 l_proc := g_package||'Calculate_Sickness_Deduction';
653 hr_utility.set_location('Entering:'|| l_proc,10);
654
655 --hr_utility.trace_on(null, 'SRJ01');
656 --hr_utility.set_location('Starting', 100);
657
658 /* Raise error if deduction formula is not set */
659
660 IF p_asg.deduct_formula is null then
661
662 --hr_utility.set_location('In l_deduction_formula is null', 100);
663
664 fnd_message.set_name ('PAY', 'PY_75027_SICK_DEDUCT_FF_NULL');
665 fnd_message.raise_error;
666
667 END IF;
668
669 /* set context value before calling fast formula */
670
671 --hr_utility.set_location('set context value before calling fast formula', 100);
672
673 pay_balance_pkg.set_context('ASSIGNMENT_ID'
674 , p_asg.assignment_id);
675 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID'
676 , p_asg.assignment_action_id);
677 pay_balance_pkg.set_context('DATE_EARNED'
678 , fnd_date.date_to_canonical(p_absence_arch.date_earned));
679 pay_balance_pkg.set_context('BUSINESS_GROUP_ID'
680 , p_asg.business_group_id);
681 pay_balance_pkg.set_context('PAYROLL_ID'
682 , p_asg.payroll_id);
683 pay_balance_pkg.set_context('ELEMENT_ENTRY_ID'
684 , p_absence_arch.element_entry_id);
685
686 /* Get input paramaters for fast formula */
687
688 ff_exec.init_formula(p_asg.deduct_formula
689 , p_absence_arch.date_earned
690 , l_inputs
691 , l_outputs);
692
693 For i in 1..l_inputs.count Loop
694 IF l_inputs(i).name = 'DEDUCTION_START_DATE' THEN
695 l_inputs(i).value := fnd_date.date_to_canonical(p_absence_start_date);
696 ELSIF l_inputs(i).name = 'DEDUCTION_END_DATE' THEN
697 l_inputs(i).value:= fnd_date.date_to_canonical(p_absence_end_date);
698 ELSIF l_inputs(i).name = 'ASG_ACTION_START_DATE' THEN
699 -- condition added for CPAM payment process
700 IF g_absence_calc.initiator = 'CPAM' THEN
701 l_inputs(i).value:= fnd_date.date_to_canonical(g_absence_calc.abs_ptd_start_date);
702 ELSE
703 l_inputs(i).value:= fnd_date.date_to_canonical(p_asg.action_start_date);
704 END IF;
705 --
706 ELSIF l_inputs(i).name = 'ASG_ACTION_END_DATE' THEN
707 -- condition added for CPAM payment process
708 IF g_absence_calc.initiator = 'CPAM' THEN
709 l_inputs(i).value:= fnd_date.date_to_canonical(g_absence_calc.abs_ptd_end_date);
710 ELSE
711 l_inputs(i).value:= fnd_date.date_to_canonical(p_asg.action_end_date);
712 END IF;
713 --
714 ELSIF l_inputs(i).name = 'REFERENCE_SALARY' THEN
715 l_inputs(i).value:= p_asg.ded_ref_salary;
716 ELSIF l_inputs(i).name = 'ASSIGNMENT_ID' THEN
717 l_inputs(i).value:= p_asg.assignment_id;
718 ELSIF l_inputs(i).name = 'DATE_EARNED' THEN
719 l_inputs(i).value:= fnd_date.date_to_canonical(p_absence_arch.date_earned);
720 ELSIF l_inputs(i).name = 'ASSIGNMENT_ACTION_ID' THEN
721 l_inputs(i).value:= p_asg.assignment_action_id;
722 ELSIF l_inputs(i).name = 'BUSINESS_GROUP_ID' THEN
723 l_inputs(i).value:= p_asg.business_group_id;
724 ELSIF l_inputs(i).name = 'PAYROLL_ID' THEN
725 l_inputs(i).value:= p_asg.payroll_id;
726 ELSIF l_inputs(i).name = 'PAYROLL_ACTION_ID' THEN
727 l_inputs(i).value:= p_asg.payroll_action_id;
728 ELSIF l_inputs(i).name = 'ELEMENT_TYPE_ID' THEN
729 l_inputs(i).value:= p_asg.element_type_id;
730 ELSIF l_inputs(i).name = 'ELEMENT_ENTRY_ID' THEN
731 l_inputs(i).value:= p_absence_arch.element_entry_id;
732 END IF;
733 END Loop;
734
735
736 /* define output values for fast formula */
737
738 l_outputs(1).name := 'L_SICKNESS_DEDUCTION';
739 l_outputs(2).name := 'L_RATE_NUMBER_OF_DAYS'; --NUMBER_OF_DAYS';
740 l_outputs(3).name := 'L_DAILY_RATE_D'; --'DAILY_RATE';
741
742
743
744
745 /* run formula and get outputs */
746
747 per_formula_functions.run_formula
748 (p_formula_id => p_asg.deduct_formula
749 ,p_calculation_date => p_absence_arch.date_earned
750 ,p_inputs => l_inputs
751 ,p_outputs => l_outputs);
752
753
754 p_absence_arch.sick_deduction := l_outputs(1).value;
755 p_absence_arch.sick_deduction_rate := l_outputs(2).value;
756 p_absence_arch.sick_deduction_base := l_outputs(3).value;
757
758 hr_utility.set_location(' Leaving:'||l_proc, 70);
759
760 --hr_utility.trace_off;
761
762
763 END Calculate_Sickness_Deduction;
764
765 -- CALC_IJSS
766 -- calculates IJSS gross and net and populates on the g_overlap table
767 PROCEDURE Calc_IJSS(
768 p_business_group_id IN Number,
769 p_assignment_id IN Number,
770 p_absence_id IN Number,
771 p_start_date IN Date,
772 p_end_date IN Date,
773 p_absence_duration IN Number,
774 p_work_inc_class IN Varchar2) is
775
776 l_delay_days number :=0;
777 l_total_abs_duration number;
778 l_hol_exist number;
779 l_ijss_calculate varchar2(3);
780 l_inelig_date date;
781 l_end_date date;
782 l_parent_abs_id number;
783 l_abs_detail_id number;
784 l_person_id number;
785 l_orig_hiredate date;
786 l_this_abs_start_date date;
787 l_abs_start_date date;
788 l_count_abs_days number;
789 l_total_overlap_rows Number;
790 l_short_term_elig varchar2(3);
791 l_long_term_elig varchar2(3);
792 l_message varchar2(50);
793 l_ref_start_dt date;
794 l_ref_end_dt date;
795 l_current_ref_start_dt date;
796 l_date_earned date;
797 l_period_ref_sal number :=0;
798 l_total_ref_sal_bal number :=0;
799 l_ref_sal_bal_id number;
800 l_assignment_action_id number;
801 l_count_ref_periods number :=1;
802 l_monthly_ss_ceiling number;
803 l_ann_ss_ceiling number;
804 l_ijss_rate number;
805 l_ijss_daily_ref_sal number;
806 l_max_rate number;
807 l_max_daily_sal number;
808 l_min_rate number;
809 l_min_daily_sal number;
810 l_ann_min_pension number;
811 l_ijss_daily_gross number;
812 l_ijss_daily_net number;
813 l_ijss_net_rate number;
814 l_count_ijss_loop number :=1;
815 l_dependent_count number;
816 l_incr_period number;
817 l_elig_for_IJSS varchar2(1) := 'N';
818 l_range_loop_count number :=1;
819 l_maternity_related varchar2(1) := 'N';
820
821 TYPE l_boundary_rec IS RECORD(l_boundary_value number);
822 TYPE l_boundary_table IS TABLE OF l_boundary_rec INDEX BY BINARY_INTEGER;
823 l_lower_range_value l_boundary_table;
824
825
826 l_proc varchar2(72) := g_package||'calc_ijss';
827
828 --Cursor for determining linked or standalone absences
829 Cursor csr_abs_ids IS
830 SELECT pabs.person_id person,
831 pabs.abs_information1 parent_abs,
832 papf.original_date_of_hire hiredate,
833 pabs.date_start abs_start
834 FROM per_absence_attendances pabs,
835 per_all_people_f papf
836 WHERE pabs.absence_attendance_id = p_absence_id
837 AND pabs.business_group_id = p_business_group_id
838 AND pabs.abs_information_category ='FR_S'
839 AND papf.person_id = pabs.person_id
840 AND papf.business_group_id = p_business_group_id
841 AND p_start_date BETWEEN papf.effective_start_date AND papf.effective_end_date;
842
843 -- cursor for finding absence details
844 Cursor csr_abs_detail(c_absence_id NUMBER) IS
845 SELECT pabs.date_start abs_start_date,
846 pabs.abs_information8 ijss_cal,
847 nvl(fnd_date.canonical_to_date(pabs.abs_information7),hr_general.end_of_time) elig_dt
848 FROM per_absence_attendances pabs
849 WHERE pabs.absence_attendance_id = c_absence_id
850 AND pabs.business_group_id = p_business_group_id
851 AND pabs.abs_information_category ='FR_S';
852
853 -- Cursor for finding holidays within the date range
854 Cursor csr_holidays(c_hol_date varchar2, c_person_id Number) IS
855 SELECT count(*)
856 FROM per_absence_attendances pabs_hol,
857 per_absence_attendance_types pabt
858 WHERE pabs_hol.person_id = c_person_id
859 AND pabs_hol.business_group_id = p_business_group_id
860 AND c_hol_date BETWEEN pabs_hol.date_start AND pabs_hol.date_end
861 AND pabt.absence_attendance_type_id = pabs_hol.absence_attendance_type_id
862 AND pabt.absence_category in ('FR_MAIN_HOLIDAY','FR_ADDITIONAL_HOLIDAY','FR_RTT_HOLIDAY') ;
863
864 -- check if a maternity absence exists within 48 hours of this sickness
865 Cursor csr_maternity(c_sick_start_date date, c_person_id Number) IS
866 SELECT nvl(min('Y'),'N') maternity_related
867 FROM per_absence_attendances pabs,
868 per_absence_attendance_types pabt
869 WHERE pabs.person_id = c_person_id
870 AND (c_sick_start_date - 3 ) BETWEEN pabs.date_start AND pabs.date_end
871 AND pabt.absence_attendance_type_id = pabs.absence_attendance_type_id
872 AND pabt.absence_category = 'M';
873
874 -- Cursor to find the defined balance id
875 -- of reference salary
876 Cursor csr_ref_bal_id IS
877 SELECT pdb.defined_balance_id
878 FROM pay_balance_types pbt,
879 pay_balance_dimensions pbd,
880 pay_defined_balances pdb
881 WHERE pdb.balance_type_id = pbt.balance_type_id
882 AND pdb.balance_dimension_id = pbd.balance_dimension_id
883 AND pbt.balance_name = 'FR_SICKNESS_IJSS_REFERENCE_SALARY'
884 AND pbd.database_item_suffix = '_ASG_PTD'
885 AND pdb.legislation_code = 'FR';
886
887 -- Cursor for fetching global value
888 Cursor csr_global_value(c_global_name VARCHAR2,c_date_earned DATE) IS
889 SELECT global_value
890 FROM ff_globals_f
891 WHERE global_name = c_global_name
892 AND legislation_code = 'FR'
893 AND c_date_earned BETWEEN effective_start_date AND effective_end_date;
894
895 -- Cursor for selecting no. of depedents on a person
896 -- as on the parent absence date
897 Cursor csr_dependent(c_person_id NUMBER,c_abs_start_date DATE) IS
898 SELECT count(*)
899 FROM per_contact_relationships
900 WHERE person_id = c_person_id
901 AND business_group_id = p_business_group_id
902 AND c_abs_start_date BETWEEN nvl(date_start, hr_general.start_of_time) and nvl(date_end, hr_general.end_of_time)
903 AND dependent_flag ='Y';
904 -- Cursor defined as part of bug #2651295
905 -- for fetching boundary values of rows of UDT
906 Cursor csr_row_value(c_table_name VARCHAR2, c_effective_date DATE) IS
907 SELECT row_low_range_or_name
908 FROM pay_user_tables put, pay_user_rows_f purf
909 WHERE put.user_table_name = c_table_name
910 AND put.user_table_id = purf.user_table_id
911 AND c_effective_date between effective_start_date and effective_end_date;
912 --
913 --
914 BEGIN
915 l_proc := g_package||'calc_ijss';
916 -- hr_utility.trace_off;
917 hr_utility.set_location('Entering:'|| l_proc,10);
918 --
919 OPEN csr_abs_ids;
920 FETCH csr_abs_ids INTO l_person_id,l_parent_abs_id,l_orig_hiredate,l_this_abs_start_date;
921 CLOSE csr_abs_ids;
922
923 -- Assign values for linked and standalone absences
924 IF l_parent_abs_id IS NOT NULL THEN
925 l_abs_detail_id := l_parent_abs_id;
926 ELSE
927 l_abs_detail_id := p_absence_id;
928 END IF;
929
930 OPEN csr_abs_detail(l_abs_detail_id);
931 FETCH csr_abs_detail INTO l_abs_start_date,l_ijss_calculate,l_inelig_date;
932 CLOSE csr_abs_detail;
933
934 l_end_date := p_end_date;
935
936 -- Fix for "Bug #2661851 and 2665751: 2ND Absence in period has incorrect days processed in guarantee"
937 hr_utility.set_location(' Current count '||g_overlap.COUNT,20);
938 IF (g_overlap.COUNT <> 0) THEN
939 g_overlap.DELETE;
940 hr_utility.set_location(' Modified count '||g_overlap.COUNT,20);
941 END IF;
942
943 -- Determining if IJSS for the absence is to be estimated
944 IF l_ijss_calculate = 'Y' THEN
945 -- IJSS is to be estimated
946 -- (a) check for ineligibility date
947 IF (l_inelig_date <= p_start_date) THEN
948 hr_utility.set_location('PAY_75022_ABS_MARKED_INELIG',20);
949 -- Fix for "Bug #2659924: SICKNESS DEDUCTION NOT PROCESSED WHEN IJSS ELIGIBILITY NOT MET "
950 --fnd_message.set_name ('PAY', 'PAY_75022_ABS_MARKED_INELIG');
951 --fnd_message.raise_error;
952 ELSE
953 -- If Ineligible for IJSS Date(on parent absence) is before the absence end date,
954 -- IJSS to be estimated only for the restricted days
955 IF (l_inelig_date <= p_end_date) THEN
956 l_end_date := l_inelig_date - 1;
957 END IF;
958
959 -- (b)Check for working hours
960 -- and SMID contributions eligibility
961 -- Pass suitable(parent/self) absence id and date
962 IJSS_Eligibility_Check(P_legislation_code => 'FR',
963 P_business_group_id => p_business_group_id,
964 P_assignment_id => p_assignment_id,
965 P_absence_id => l_abs_detail_id,
966 P_abs_start_date => l_abs_start_date,
967 P_short_term_eligibility => l_short_term_elig,
968 P_long_term_eligibility => l_long_term_elig,
969 P_Message => l_message);
970
971 hr_utility.set_location(' Short term IJSS Eligibility?'||l_short_term_elig,20);
972 hr_utility.set_location(' Long term IJSS Eligibility? '||l_long_term_elig,20);
973 -- finding the number of rows to be populated
974 -- in g_overlap table for this absence period
975 l_total_overlap_rows := trunc(l_end_date - p_start_date) +1;
976
977 l_total_abs_duration := l_total_overlap_rows + p_absence_duration;
978 hr_utility.set_location(' Prevs absence durn '||p_absence_duration,30);
979 hr_utility.set_location(' Total absence durn '||l_total_abs_duration,30);
980 -- Eligibility needs to be checked as soon as the absence crosses the 180 day mark
981 -- In case eligibility is lost when the absence becomes longer than 180(ie. else if 2)
982 -- i) if in this processing period, only the eligible part of the absence should be processed
983 -- ii) if not, error should be returned
984 --
985 IF (l_total_abs_duration <= 180 AND l_short_term_elig = 'Y') THEN
986 l_elig_for_IJSS := 'Y';
987 ELSIF (l_total_abs_duration > 180 AND l_long_term_elig = 'Y') THEN
988 l_elig_for_IJSS := 'Y';
989 ELSIF (l_total_abs_duration > 180 AND l_long_term_elig = 'N') THEN
990 IF (p_absence_duration <= 180) THEN
991 -- absence has become longer than 180 days in this processing period
992 -- and not eligible for IJSS anymore
993 -- i) so check short term eligibility
994 IF (l_short_term_elig = 'Y') THEN
995 l_end_date := p_start_date + (180 - p_absence_duration);
996 -- Repeat check for "Ineligible for IJSS Date"
997 IF (l_inelig_date <= l_end_date) THEN
998 l_end_date := l_inelig_date - 1;
999 END IF;
1000 l_total_overlap_rows := trunc(l_end_date - p_start_date) +1;
1001 l_elig_for_IJSS := 'Y';
1002 hr_utility.set_location(' Modified end date to '||l_end_date||' as absence does not have long eligibility',20);
1003 ELSE -- ii)
1004 hr_utility.set_location('PAY_75021_ABS_INELIG_FOR_IJSS',40);
1005 -- Fix for "Bug #2659924: SICKNESS DEDUCTION NOT PROCESSED WHEN IJSS ELIGIBILITY NOT MET "
1006
1007 --fnd_message.set_name ('PAY', 'PAY_75021_ABS_INELIG_FOR_IJSS');
1008 --fnd_message.raise_error;
1009 END IF;
1010 END IF;
1011 ELSE
1012 hr_utility.set_location('PAY_75021_ABS_INELIG_FOR_IJSS',50);
1013 -- Fix for "Bug #2659924: Sickness deduction not processed when ijss eligibility not met "
1014
1015 --fnd_message.set_name ('PAY', 'PAY_75021_ABS_INELIG_FOR_IJSS');
1016 --fnd_message.raise_error;
1017 END IF;
1018
1019
1020 IF l_elig_for_IJSS = 'Y' THEN
1021
1022 -- Only when the absence is to be estimated and eligible,
1023 -- populate the g_overlap with the dates (possibly restricted)
1024
1025 hr_utility.set_location('Calculation '||l_elig_for_IJSS,20);
1026 hr_utility.set_location(' Total rows to be in overlap table:'||to_char(l_total_overlap_rows),20);
1027
1028 -- Populate g_overlap table with absence dates
1029 -- index being the actual day of the absence
1030
1031 FOR l_count_abs_days in (p_absence_duration + 1)..(p_absence_duration + l_total_overlap_rows)
1032 LOOP
1033 IF l_count_abs_days = p_absence_duration + 1 THEN
1034 g_overlap(l_count_abs_days).Absence_day := p_start_date;
1035 ELSE
1036 g_overlap(l_count_abs_days).Absence_day := g_overlap(l_count_abs_days-1).Absence_day +1;
1037 END IF;
1038
1039 -- (2) check for holidays
1040 --
1041 OPEN csr_holidays(g_overlap(l_count_abs_days).Absence_day,l_person_id);
1042 FETCH csr_holidays INTO l_hol_exist;
1043 CLOSE csr_holidays;
1044 IF l_hol_exist >0 THEN
1045 g_overlap(l_count_abs_days).Holiday :='H';
1046 END IF;
1047 --
1048 hr_utility.set_location(to_char(l_count_abs_days)||'th row and Date:'
1049 ||to_char(g_overlap(l_count_abs_days).Absence_day)||
1050 ' Hols? '||g_overlap(l_count_abs_days).Holiday,20);
1051 --
1052 END LOOP;
1053
1054 -- continue with IJSS estimation
1055 -- Get the defined balance id of 'FR_SICKNESS_IJSS_REFERENCE_SALARY'
1056 -- of '_ASG_PTD' dimension
1057 OPEN csr_ref_bal_id;
1058 FETCH csr_ref_bal_id INTO l_ref_sal_bal_id;
1059 CLOSE csr_ref_bal_id;
1060 --
1061 IF p_work_inc_class ='N' OR p_work_inc_class IS NULL THEN
1062 -- for non-occupational sickness
1063 l_ref_start_dt := add_months(trunc(l_abs_start_date,'MONTH'),-3);
1064 l_ref_end_dt := last_day(add_months(l_abs_start_date, -1));
1065 FOR l_count_ref_periods IN 1..3 LOOP
1066 l_incr_period := l_count_ref_periods -1;
1067 l_date_earned := add_months(last_day(l_ref_start_dt), l_incr_period);
1068 l_period_ref_sal := Get_reference_salary(P_Business_group_id => p_business_group_id,
1069 P_Assignment_id => p_assignment_id,
1070 P_Period_end_date => l_date_earned,
1071 P_Absence_category => 'S');
1072 /*
1073 -- find the assignment_action_id for each period
1074 OPEN csr_assignment_actions(l_date_earned);
1075 FETCH csr_assignment_actions INTO l_assignment_action_id;
1076 CLOSE csr_assignment_actions;
1077 -- find the balance value for each period
1078 hr_utility.set_location(' REF SAL BAL id :'||l_ref_sal_bal_id,40);
1079 hr_utility.set_location(' REF SAL ASAC id :'||l_assignment_action_id,40);
1080 l_period_ref_sal :=pay_balance_pkg.get_value(l_ref_sal_bal_id,
1081 l_assignment_action_id);
1082 */
1083 -- limit the total balance value to SS ceiling
1084 OPEN csr_global_value('FR_MONTHLY_SS_CEILING',l_date_earned);
1085 FETCH csr_global_value INTO l_monthly_ss_ceiling;
1086 CLOSE csr_global_value;
1087
1088 IF l_monthly_ss_ceiling < l_period_ref_sal THEN
1089 l_period_ref_sal := l_monthly_ss_ceiling;
1090 END IF;
1091 --
1092 -- Check for new hires
1093 l_current_ref_start_dt := trunc(l_date_earned, 'MONTH');
1094 IF l_orig_hiredate > l_current_ref_start_dt THEN
1095 IF l_orig_hiredate > l_date_earned THEN
1096 -- reference salary for this period is nil
1097 l_period_ref_sal := 0 ;
1098 ELSE
1099 -- prorate reference salary
1100 l_period_ref_sal := (l_period_ref_sal/(l_date_earned - l_orig_hiredate +1)) * 30;
1101 END IF;
1102 END IF;
1103 hr_utility.set_location('reference period :'||to_char(l_current_ref_start_dt),40);
1104 l_total_ref_sal_bal := l_total_ref_sal_bal+ l_period_ref_sal;
1105 hr_utility.set_location('total ref salary'||to_char(l_total_ref_sal_bal),40);
1106 END LOOP;
1107 -- Check for null reference salary
1108 -- Error commented out, do need to reconsider it later
1109 /*
1110 IF l_total_ref_sal_bal = 0 THEN
1111 fnd_message.set_name ('PAY', 'PAY_75020_IJSS_NULL_REF_SAL');
1112 fnd_message.raise_error;
1113 END IF;
1114 */
1115
1116 -- get the daily reference salary
1117 l_ijss_daily_ref_sal := l_total_ref_sal_bal/90;
1118 --
1119 -- get the delay days and
1120
1121 -- if sickness is within 48 hrs of a maternity absence then no delay is required
1122 -- look for maternity absence
1123 OPEN csr_maternity(l_abs_start_date,l_person_id);
1124 FETCH csr_maternity INTO l_maternity_related;
1125 CLOSE csr_maternity;
1126 hr_utility.trace(' l_maternity_related:'||(l_maternity_related));
1127 -- subtract the ones already covered in the previous period or
1128 -- previous linked/parent absence
1129 if l_maternity_related = 'N' then
1130 l_delay_days := hruserdt.get_table_value(p_business_group_id,
1131 'FR_IJSS_NON_OCCUP_RATES_MAX','Delay',
1132 p_absence_duration+1,l_ref_start_dt);
1133 else l_delay_days := 0;
1134 end if;
1135 hr_utility.set_location(' Delay Days :'||to_char(l_delay_days),22);
1136 -- Get the no. of dependents on parent absence start date
1137 OPEN csr_dependent(l_person_id,l_abs_start_date);
1138 FETCH csr_dependent INTO l_dependent_count;
1139 CLOSE csr_dependent;
1140 -- get the annual minimal invalidity pension
1141 OPEN csr_global_value('FR_MINIMAL_INVALIDITY_PENSION',p_start_date);
1142 FETCH csr_global_value INTO l_ann_min_pension;
1143 CLOSE csr_global_value;
1144 -- get the maximum daily salary
1145 l_ann_ss_ceiling := 12 * l_monthly_ss_ceiling;
1146 -- get the value of ijss net rate
1147 OPEN csr_global_value('FR_IJSS_NET_RATE',p_start_date);
1148 FETCH csr_global_value INTO l_ijss_net_rate;
1149 CLOSE csr_global_value;
1150 -- Calculate IJSS and
1151 -- Populate the g_overlap table with ijss values
1152 -- Find the boundary values from the rows of UDT
1153 OPEN csr_row_value ('FR_IJSS_NON_OCCUP_RATES_MAX',l_abs_start_date);
1154 LOOP
1155 FETCH csr_row_value INTO l_lower_range_value(l_range_loop_count);
1156 l_range_loop_count := l_range_loop_count +1;
1157 IF csr_row_value%NOTFOUND THEN
1158 EXIT;
1159 END IF;
1160 END LOOP;
1161 CLOSE csr_row_value;
1162
1163 FOR l_count_ijss_loop IN (p_absence_duration+1)..(p_absence_duration+l_total_overlap_rows) LOOP
1164 -- code added for bug#2651295
1165 -- Use the boundary values for retrieving rates
1166 -- (i) check for duration every time
1167 --l_incr_abs_duration:= g_overlap(l_count_ijss_loop).Absence_Day - l_this_abs_start_date +1;
1168 IF l_count_ijss_loop = p_absence_duration+1 OR -- first time
1169 l_count_ijss_loop = l_lower_range_value(2).l_boundary_value OR
1170 l_count_ijss_loop = l_lower_range_value(3).l_boundary_value THEN
1171 -- (ii) retrieve rates if required
1172 -- according to the number of dependents
1173 hr_utility.set_location('Absence duration boundary for non-occup :'||to_char(l_count_ijss_loop),22);
1174 IF l_dependent_count<3 THEN
1175 l_ijss_rate := hruserdt.get_table_value(p_business_group_id,'FR_IJSS_NON_OCCUP_RATES_MAX','Rate for others (%)',l_count_ijss_loop,l_abs_start_date);
1176 l_max_rate := hruserdt.get_table_value(p_business_group_id,'FR_IJSS_NON_OCCUP_RATES_MAX','Max for others - Related to annual SS ceiling',l_count_ijss_loop,l_abs_start_date);
1177 l_min_rate := hruserdt.get_table_value(p_business_group_id,'FR_IJSS_NON_OCCUP_RATES_MAX','Min for others:Factor of global minimal invalidity pension',l_count_ijss_loop,l_abs_start_date);
1178 ELSE
1179 l_ijss_rate := hruserdt.get_table_value(p_business_group_id,'FR_IJSS_NON_OCCUP_RATES_MAX','Rate for 3 dependents or more (%)',l_count_ijss_loop,l_abs_start_date);
1180 l_max_rate := hruserdt.get_table_value(p_business_group_id,'FR_IJSS_NON_OCCUP_RATES_MAX','Max for 3 dependents or more - Related to annual SS ceiling',l_count_ijss_loop,l_abs_start_date);
1181 l_min_rate := hruserdt.get_table_value(p_business_group_id,'FR_IJSS_NON_OCCUP_RATES_MAX','Min for 3 dependents or more:Factor of global minimal invalidity pension',l_count_ijss_loop,l_abs_start_date);
1182 END IF;
1183 END IF;
1184 hr_utility.set_location('ijss rate:'||to_char(l_ijss_rate)||' max rate:'||to_char(l_max_rate)||' min rate:'||to_char(l_min_rate),22);
1185 -- (iii) calculate IJSS
1186 -- get minimum daily salary
1187 l_min_daily_sal := l_ann_min_pension * l_min_rate;
1188 l_max_daily_sal := l_ann_ss_ceiling * l_max_rate;
1189 -- limit the daily ref salary by the minimum and maximum value
1190 l_ijss_daily_gross := l_ijss_daily_ref_sal * l_ijss_rate/100;
1191 -- Cap IJSS to maximum
1192 IF l_ijss_daily_gross > l_max_daily_sal THEN
1193 l_ijss_daily_gross := l_max_daily_sal;
1194 ELSE
1195 l_ijss_daily_gross := l_ijss_daily_gross;
1196 END IF;
1197 -- Minimum IJSS for absences with duration more than 6 months
1198 IF l_count_ijss_loop > 180 THEN
1199 IF l_ijss_daily_gross < l_min_daily_sal THEN
1200 l_ijss_daily_gross := l_min_daily_sal;
1201 ELSE
1202 l_ijss_daily_gross := l_ijss_daily_gross;
1203 END IF;
1204 END IF;
1205
1206 l_ijss_daily_net := l_ijss_daily_gross * (100 - l_ijss_net_rate)/100;
1207
1208 --
1209 IF l_count_ijss_loop > l_delay_days AND
1210 g_overlap(l_count_ijss_loop).Absence_Day < l_inelig_date
1211 THEN
1212 g_overlap(l_count_ijss_loop).IJSS_Gross:= l_ijss_daily_gross;
1213 g_overlap(l_count_ijss_loop).IJSS_Net:= l_ijss_daily_net;
1214 ELSE
1215 -- populate IJSS payment columns as '0'
1216 -- for days either less than delay days or
1217 -- after the ineligibility date
1218 g_overlap(l_count_ijss_loop).IJSS_Gross:= 0;
1219 g_overlap(l_count_ijss_loop).IJSS_Net := 0;
1220 END IF;
1221 --
1222 hr_utility.set_location('Cnt of pymnts:'||to_char(l_count_ijss_loop)||
1223 ' Gross Pymnt:'||substr(to_char(g_overlap(l_count_ijss_loop).IJSS_Gross),1,10)||
1224 ' Net Pymnt:'||substr(to_char(g_overlap(l_count_ijss_loop).IJSS_Net),1,10),30);
1225 --
1226 END LOOP;
1227 ELSE
1228 -- for occupational sickness
1229 l_ref_start_dt := add_months(trunc(l_abs_start_date,'MONTH'),-1);
1230 l_ref_end_dt := last_day(add_months(l_abs_start_date, -1));
1231
1232 l_date_earned := last_day(l_ref_start_dt);
1233 l_total_ref_sal_bal := Get_reference_salary(
1234 P_Business_group_id => p_business_group_id,
1235 P_Assignment_id => p_assignment_id,
1236 P_Period_end_date => l_date_earned,
1237 P_Absence_category => 'S');
1238 /*
1239 -- find the assignment_action_id for this period
1240 OPEN csr_assignment_actions(l_date_earned);
1241 FETCH csr_assignment_actions INTO l_assignment_action_id;
1242 CLOSE csr_assignment_actions;
1243 -- find the balance value for this period
1244 hr_utility.set_location(' OCCUP ',40);
1245 hr_utility.set_location(' REF SAL BAL id :'||l_ref_sal_bal_id,40);
1246 hr_utility.set_location(' REF SAL ASAC id :'||l_assignment_action_id,40);
1247 l_total_ref_sal_bal := pay_balance_pkg.get_value(l_ref_sal_bal_id,
1248 l_assignment_action_id);
1249 */
1250 -- Check for new hires
1251 IF l_orig_hiredate > l_ref_start_dt THEN
1252 IF l_orig_hiredate > l_date_earned THEN
1253 -- reference salary for this period is nil
1254 l_period_ref_sal := 0 ;
1255 ELSE
1256 -- prorate reference salary
1257 l_period_ref_sal := (l_period_ref_sal/(l_date_earned - l_orig_hiredate +1)) * 30;
1258 END IF;
1259 END IF;
1260 -- Check for null reference salary
1261 -- Error commented out, do need to reconsider it later
1262 /*
1263 IF l_total_ref_sal_bal = 0 THEN
1264 fnd_message.set_name ('PAY', 'PAY_75020_IJSS_NULL_REF_SAL');
1265 fnd_message.raise_error;
1266 END IF;
1267 */
1268 -- get the daily reference salary
1269 l_ijss_daily_ref_sal := l_total_ref_sal_bal/30;
1270 -- Getting the annual SS ceiling
1271 OPEN csr_global_value('FR_MONTHLY_SS_CEILING',p_start_date);
1272 FETCH csr_global_value INTO l_monthly_ss_ceiling;
1273 CLOSE csr_global_value;
1274 l_ann_ss_ceiling := 12 * l_monthly_ss_ceiling;
1275 --
1276 -- get the value of ijss net rate
1277 OPEN csr_global_value('FR_IJSS_NET_RATE',p_start_date);
1278 FETCH csr_global_value INTO l_ijss_net_rate;
1279 CLOSE csr_global_value;
1280 -- Calculate IJSS and populate g_overlap table with ijss values:
1281 -- Find the boundary values from the rows of UDT
1282 OPEN csr_row_value ('FR_IJSS_OCCUP_RATES_MAX',l_abs_start_date);
1283 LOOP
1284 FETCH csr_row_value INTO l_lower_range_value(l_range_loop_count);
1285 l_range_loop_count := l_range_loop_count +1;
1286 IF csr_row_value%NOTFOUND THEN
1287 EXIT;
1288 END IF;
1289 END LOOP;
1290 CLOSE csr_row_value;
1291
1292 l_delay_days := hruserdt.get_table_value(p_business_group_id,'FR_IJSS_OCCUP_RATES_MAX','Delay',p_absence_duration+1,l_ref_start_dt);
1293
1294
1295 FOR l_count_ijss_loop IN (p_absence_duration+1)..(p_absence_duration+l_total_overlap_rows) LOOP
1296 --
1297 -- code added for bug#2651295
1298 -- Use the boundary values for retrieving rates
1299 -- (i) check for duration every time
1300 --l_incr_abs_duration:= g_overlap(l_count_ijss_loop).Absence_Day - l_this_abs_start_date +1;
1301 IF l_count_ijss_loop =p_absence_duration+1 OR -- first time
1302 l_count_ijss_loop = l_lower_range_value(2).l_boundary_value THEN
1303 -- (ii) retrieve rates if required
1304 hr_utility.set_location('Absence duration boundary for occup :'||to_char(l_count_ijss_loop),22);
1305 l_ijss_rate := hruserdt.get_table_value(p_business_group_id,'FR_IJSS_OCCUP_RATES_MAX','Rate (%)',l_count_ijss_loop,l_abs_start_date);
1306 l_max_rate := hruserdt.get_table_value(p_business_group_id,'FR_IJSS_OCCUP_RATES_MAX','Max - Related to annual SS ceiling',l_count_ijss_loop,l_abs_start_date);
1307 END IF;
1308 hr_utility.set_location('ijss rate:'||to_char(l_ijss_rate)||' max rate:'||to_char(l_max_rate),22);
1309 -- (iii) calculate IJSS
1310 -- the maximum daily salary
1311 l_ijss_daily_gross := l_ijss_daily_ref_sal * l_ijss_rate/100;
1312 -- Cap IJSS to maximum
1313 IF l_ijss_daily_gross > l_max_daily_sal THEN
1314 l_ijss_daily_gross := l_max_daily_sal;
1315 ELSE
1316 l_ijss_daily_gross := l_ijss_daily_gross;
1317 END IF;
1318 l_ijss_daily_net := l_ijss_daily_gross * (100 - l_ijss_net_rate)/100;
1319
1320 --
1321 IF l_count_ijss_loop > l_delay_days AND
1322 g_overlap(l_count_ijss_loop).Absence_Day < l_inelig_date
1323 THEN
1324 g_overlap(l_count_ijss_loop).IJSS_Gross:= l_ijss_daily_gross;
1325 g_overlap(l_count_ijss_loop).IJSS_Net:= l_ijss_daily_net;
1326 ELSE
1327 -- populate IJSS payment columns as '0'
1328 -- for days either less than delay days or
1329 -- after the ineligibility date
1330 g_overlap(l_count_ijss_loop).IJSS_Gross:= 0;
1331 g_overlap(l_count_ijss_loop).IJSS_Net := 0;
1332 END IF;
1333 --
1334 hr_utility.set_location('Cnt of pymnts:'||to_char(l_count_ijss_loop)||
1335 ' Grss Pymnt:'||substr(to_char(g_overlap(l_count_ijss_loop).IJSS_Gross),1,10)||
1336 ' Net Pymnt:'||substr(to_char(g_overlap(l_count_ijss_loop).IJSS_Net),1,10),30);
1337 --
1338 END LOOP;
1339 END IF; -- Work Incident check
1340 --
1341 ELSE
1342 hr_utility.set_location('PAY_75021_ABS_INELIG_FOR_IJSS',60);
1343 -- Fix for "Bug #2659924:
1344 --fnd_message.set_name ('PAY', 'PAY_75021_ABS_INELIG_FOR_IJSS');
1345 --fnd_message.raise_error;
1346 END IF; -- Absence Eligibility check
1347 END IF; -- 'Ineligible IJSS date' on parent absence check
1348
1349 ELSE
1350 null; -- to be coded in the next phase of FR payroll
1351 -- IJSS is not to be estimated, find Actual IJSS
1352
1353 END IF; -- IJSS Estimation flag check
1354
1355 hr_utility.set_location(' Leaving:'||l_proc, 70);
1356
1357
1358 END Calc_IJSS;
1359 --
1360 -- Checks for IJSS eligibility
1361 PROCEDURE IJSS_Eligibility_Check(
1362 P_legislation_code IN Varchar2,
1363 P_business_group_id IN Number,
1364 P_assignment_id IN Number,
1365 P_absence_id IN Number,
1366 P_abs_start_date IN Date,
1367 P_short_term_eligibility OUT NOCOPY Varchar2,
1368 P_long_term_eligibility OUT NOCOPY Varchar2,
1369 P_Message OUT NOCOPY Varchar2)
1370 IS
1371 --
1372 l_smid_6_mths Number;
1373 l_smid_12_mths Number;
1374 l_plus_200_hrs Varchar2(3);
1375 l_plus_800_hrs Varchar2(3);
1376 l_short_term_elig_flg Varchar2(3);
1377 l_long_term_elig_flg Varchar2(3);
1378 l_ref_bal_date_from Date;
1379 l_smic_multiplier Number;
1380 l_smic_hourly_rate Number;
1381 l_smid_rate Number;
1382 l_global_smic_ded Number;
1383
1384 -- Cursor to retrieve eligibility segment details
1385 Cursor csr_elig_details IS
1386 SELECT abs_information9 SMID_6,
1387 abs_information10 SMID_12,
1388 abs_information11 hrs_200,
1389 abs_information12 hrs_800
1390 FROM per_absence_Attendances
1391 WHERE absence_Attendance_id = p_absence_id
1392 AND business_group_id = p_business_group_id
1393 AND abs_information_category ='FR_S';
1394
1395 -- Cursor for fetching global value
1396 Cursor csr_global_value(c_global_name VARCHAR2,c_date_earned DATE) IS
1397 SELECT global_value
1398 FROM ff_globals_f
1399 WHERE global_name = c_global_name
1400 AND legislation_code = 'FR'
1401 AND c_date_earned BETWEEN effective_start_date AND effective_end_date;
1402 --
1403 l_proc varchar2(72) := g_package||' IJSS_Eligibility_Check';
1404 --
1405 BEGIN
1406
1407 l_proc := g_package||'IJSS_Eligibility_Check';
1408
1409 hr_utility.set_location('Entering:'|| l_proc,10);
1410
1411 -- Check for stored eligibility on DDF segment of absence
1412 OPEN csr_elig_details;
1413 FETCH csr_elig_details INTO l_smid_6_mths,l_smid_12_mths,l_plus_200_hrs,l_plus_800_hrs;
1414 CLOSE csr_elig_details;
1415
1416 -- Derive values of the variables
1417 IF l_plus_200_hrs IS NULL THEN
1418 l_plus_200_hrs := IJSS_Eligibility_Working_hours
1419 (P_legislation_code => 'FR',
1420 P_business_group_id => p_business_group_id,
1421 P_assignment_id => p_assignment_id,
1422 P_absence_start_date => p_abs_start_date,
1423 P_long_absence => FALSE );
1424 END IF;
1425 IF l_plus_800_hrs IS NULL THEN
1426 l_plus_800_hrs := IJSS_Eligibility_Working_hours
1427 (P_legislation_code => 'FR',
1428 P_business_group_id => p_business_group_id,
1429 P_assignment_id => p_assignment_id,
1430 P_absence_start_date => p_abs_start_date,
1431 P_long_absence => TRUE );
1432 END IF;
1433
1434 -- Determine short term eligibility
1435 IF l_plus_200_hrs = 'Y' THEN
1436 l_short_term_elig_flg := 'Y';
1437 ELSE
1438 -- Derive SMID values from function if not entered
1439 IF l_smid_6_mths IS NULL THEN
1440 l_smid_6_mths := IJSS_Eligibility_SMID
1441 (P_legislation_code => 'FR',
1442 P_business_group_id => p_business_group_id,
1443 P_assignment_id => p_assignment_id,
1444 P_absence_start_date => p_abs_start_date,
1445 P_long_absence => FALSE );
1446 END IF;
1447 -- Perform SMID contributions check
1448 -- Fetch reference contributions from global
1449 -- as of 1st day of reference period
1450 l_ref_bal_date_from:= TRUNC(ADD_MONTHS(p_abs_start_date,-6), 'MONTH');
1451 --
1452 OPEN csr_global_value('FR_IJSS_SMIC_MULTIPLIER',l_ref_bal_date_from);
1453 FETCH csr_global_value INTO l_smic_multiplier;
1454 CLOSE csr_global_value;
1455 --
1456 OPEN csr_global_value('FR_HOURLY_SMIC_RATE',l_ref_bal_date_from);
1457 FETCH csr_global_value INTO l_smic_hourly_rate;
1458 CLOSE csr_global_value;
1459 --
1460 l_smid_rate := hruserdt.get_table_value(p_business_group_id,'FR_CONTRIBUTION_RATES','Value (EUR)','EE_SMID',l_ref_bal_date_from);
1461 --
1462 l_global_smic_ded := l_smic_multiplier * l_smic_hourly_rate * l_smid_rate / 100;
1463 IF l_smid_6_mths > l_global_smic_ded THEN
1464 l_short_term_elig_flg := 'Y';
1465 ELSE
1466 l_short_term_elig_flg:= 'N';
1467 END IF;
1468 END IF;
1469
1470 -- Determine long term eligibility only when the absence is eligible short term
1471 IF l_short_term_elig_flg ='Y' THEN
1472 --
1473 IF l_plus_800_hrs = 'Y' THEN
1474 l_long_term_elig_flg := 'Y';
1475 ELSE
1476 -- Derive SMID values from function if not entered
1477 IF l_smid_12_mths IS NULL THEN
1478 l_smid_12_mths := IJSS_Eligibility_SMID
1479 (P_legislation_code => 'FR',
1480 P_business_group_id => p_business_group_id,
1481 P_assignment_id => p_assignment_id,
1482 P_absence_start_date => p_abs_start_date,
1483 P_long_absence => TRUE );
1484 END IF;
1485 -- Perform SMID contributions check
1486 -- Fetch reference contributions from global as of
1487 -- 1st day of the year preceding the reference period
1488 l_ref_bal_date_from:= TRUNC(ADD_MONTHS(P_abs_start_date,-12), 'YEAR');
1489 --
1490 OPEN csr_global_value('FR_IJSS_SMIC_MULTIPLIER',l_ref_bal_date_from);
1491 FETCH csr_global_value INTO l_smic_multiplier;
1492 CLOSE csr_global_value;
1493 --
1494 OPEN csr_global_value('FR_HOURLY_SMIC_RATE',l_ref_bal_date_from);
1495 FETCH csr_global_value INTO l_smic_hourly_rate;
1496 CLOSE csr_global_value;
1497 --
1498 l_smid_rate := hruserdt.get_table_value(p_business_group_id,'FR_CONTRIBUTION_RATES','Value (EUR)','EE_SMID',l_ref_bal_date_from);
1499 --
1500 l_global_smic_ded := 2 * (l_smic_multiplier * l_smic_hourly_rate * l_smid_rate / 100);
1501 IF l_smid_12_mths > l_global_smic_ded THEN
1502 l_long_term_elig_flg := 'Y';
1503 ELSE
1504 l_long_term_elig_flg := 'N';
1505 END IF;
1506 --
1507 END IF;
1508 END IF;
1509 P_short_term_eligibility := l_short_term_elig_flg;
1510 P_long_term_eligibility := l_long_term_elig_flg;
1511
1512 hr_utility.set_location(' Leaving:'||l_proc, 70);
1513
1514 END IJSS_Eligibility_Check;
1515 --
1516
1517 PROCEDURE Get_abs_print_flg(p_business_group_id IN Number,
1518 p_parent_abs_id IN Number,
1519 p_period_end_date IN Date, -- for subrogation date
1520 p_person_id IN number,
1521 p_abs_duration OUT NOCOPY Number,-- for eligibility
1522 p_invalid_start_date OUT NOCOPY Date, -- for comparison
1523 p_subr_start_date OUT NOCOPY Date,
1524 p_subr_end_date OUT NOCOPY Date,
1525 p_last_absence_date OUT NOCOPY Date, -- for printing subrogation
1526 p_maternity_related OUT NOCOPY Varchar2)
1527 IS
1528 --
1529
1530 l_proc varchar2(72) := g_package||'Get_abs_print_flg';
1531
1532 l_prev_end_dt Date;
1533 l_invalid_start_dt Date := NULL;
1534 l_abs_duration Number:=0;
1535 l_subr_start_date Date;
1536 l_subr_end_date Date;
1537 l_absence_start_date Date;
1538
1539 -- Cursor fetching absence details
1540 Cursor csr_abs_details IS
1541 SELECT date_start, date_end,absence_attendance_id,abs_information2
1542 FROM per_absence_attendances
1543 WHERE person_id = p_person_id
1544 AND ( absence_attendance_id = p_parent_abs_id
1545 OR abs_information1 = to_char(p_parent_abs_id))
1546 AND business_group_id = p_business_group_id
1547 AND abs_information_category = 'FR_S'
1548 ORDER BY date_start ;
1549 --
1550 -- Cursor for subrogation details
1551 Cursor csr_subr_details IS
1552 SELECT date_start, date_end,abs_information2
1553 FROM per_absence_attendances
1554 WHERE person_id = p_person_id
1555 AND (absence_attendance_id = p_parent_abs_id
1556 OR abs_information1 = to_char(p_parent_abs_id))
1557 AND business_group_id = p_business_group_id
1558 AND abs_information_category = 'FR_S'
1559 AND date_start <= p_period_end_date
1560 ORDER BY date_start desc;
1561
1562 -- check if a maternity absence exists within 48 hours of this sickness
1563 Cursor csr_maternity(c_sick_start_date date, c_person_id Number) IS
1564 SELECT nvl(min('Y'),'N') maternity_related
1565 FROM per_absence_attendances pabs,
1566 per_absence_attendance_types pabt
1567 WHERE pabs.person_id = c_person_id
1568 AND (c_sick_start_date - 3 ) BETWEEN pabs.date_start AND pabs.date_end
1569 AND pabt.absence_attendance_type_id = pabs.absence_attendance_type_id
1570 AND pabt.absence_category = 'M';
1571
1572 --
1573 BEGIN
1574 --
1575 -- fetch all absences linked to this absence
1576 hr_utility.set_location('Entering:'|| l_proc,10);
1577
1578 l_absence_start_date := null; -- initialize start of parent absence
1579
1580 FOR abs_details IN csr_abs_details LOOP
1581 if l_absence_start_date is null then
1582 l_absence_start_date := abs_details.date_start;
1583 end if;
1584 -- If this is not the parent absence
1585 IF abs_details.absence_attendance_id <> p_parent_abs_id THEN
1586 -- (a)check for absence validity
1587 IF abs_details.date_start - l_prev_end_dt > 2 THEN
1588 -- populate the invalid start date variable
1589 l_invalid_start_dt := abs_details.date_start;
1590 EXIT;
1591 ELSE
1592 l_invalid_start_dt := hr_general.end_of_time;
1593 END IF;
1594 END IF;
1595 l_prev_end_dt := abs_details.date_end;
1596 -- (b) add up the duration
1597 l_abs_duration := l_abs_duration + round(abs_details.date_end - abs_details.date_start+1);
1598 -- (c) set the last valid absence date for printing absence
1599 p_last_absence_date := abs_details.date_end;
1600 END LOOP;
1601 -- populate OUT parameters
1602 p_abs_duration := l_abs_duration;
1603 p_invalid_start_date := l_invalid_start_dt;
1604 -- Get subrogation dates
1605 FOR subr_details in csr_subr_details LOOP
1606 -- (c) populate the subrogation dates if flag is 'Y'
1607 IF subr_details.abs_information2 = 'Y' THEN
1608 l_subr_start_date := subr_details.date_start;
1609 IF l_subr_end_date IS NULL THEN
1610 l_subr_end_date := subr_details.date_end;
1611 END IF;
1612 ELSE
1613 IF l_subr_end_date IS NOT NULL THEN
1614 EXIT;
1615 END IF;
1616 END IF;
1617 END LOOP;
1618 -- populate subrogation OUT parameters
1619 p_subr_start_date := l_subr_start_date;
1620 p_subr_end_date:= l_subr_end_date;
1621
1622 -- is the absence maternity related?
1623 OPEN csr_maternity(l_absence_start_date,p_person_id);
1624 FETCH csr_maternity INTO p_maternity_related;
1625 CLOSE csr_maternity;
1626
1627
1628 hr_utility.set_location(' Leaving:'||l_proc, 70);
1629 END Get_abs_print_flg;
1630 --
1631 -- get_sickness_skip
1632 FUNCTION Get_Sickness_skip(
1633 P_Assignment_id IN Number,
1634 P_element_entry_id IN Number,
1635 P_date_earned IN Date,
1636 P_action_start_date IN Date,
1637 P_action_end_date IN Date)
1638 RETURN Varchar2 IS
1639 --
1640 -- Populate the g_absence_calc structure for the element being processed
1641 --
1642 -- fetch the estimate flag from the parent absence
1643 -- if its Y then return N for nor no skip else Y
1644 --
1645 cursor c_get_absence is
1646 select paa.absence_attendance_id
1647 , to_number(paa.abs_information1) parent_absence_id
1648 , paa.date_start
1649 , paa.date_end
1650 , paa.date_end - paa.date_start + 1 duration
1651 , null effective_start_date
1652 , null effective_end_date
1653 , paa.abs_information2 subrogated
1654 , paa.abs_information8 estimated
1655 , nvl(inc.inc_information1,'N') work_incident
1656 , paa.person_id
1657 , paa.business_group_id
1658 from per_absence_attendances paa
1659 , pay_element_entries_f pee
1660 , per_work_incidents inc
1661 where pee.element_entry_id = p_element_entry_id
1662 and paa.absence_attendance_id = pee.creator_id
1663 and pee.creator_type = 'A'
1664 and decode(paa.abs_information_category,'FR_S',to_number(paa.abs_information6),null) = inc.incident_id(+);
1665 --
1666 cursor c_get_entry_dates is
1667 select min(effective_start_date)
1668 , max(effective_end_date)
1669 from pay_element_entries_f
1670 where element_entry_id = p_element_entry_id;
1671 --
1672 cursor c_get_parent_absence(p_absence_attendance_id number) is
1673 select paa.absence_attendance_id
1674 , 0 parent_absence_id
1675 , paa.date_start
1676 , paa.date_end
1677 , paa.date_end - paa.date_start + 1 duration
1678 , null effective_start_date
1679 , null effective_end_date
1680 , paa.abs_information2 subrogated
1681 , paa.abs_information8 estimated
1682 , nvl(inc.inc_information1,'N') work_incident
1683 , paa.person_id
1684 , paa.business_group_id
1685 from per_absence_attendances paa
1686 , per_work_incidents inc
1687 where paa.absence_attendance_id = p_absence_attendance_id
1688 and paa.abs_information6 = to_char(inc.incident_id(+));
1689 --
1690 cursor c_get_child_absence(p_parent_absence_id number
1691 ,p_max_end_date date
1692 ,p_person_id number
1693 ,p_business_group_id number) is
1694 select absence_attendance_id
1695 , to_number(abs_information1) parent_absence_id
1696 , date_start
1697 , date_end
1698 , date_end - date_start + 1 duration
1699 , null effective_start_date
1700 , null effective_end_date
1701 , null subrogated
1702 , null estimated
1703 , null work_incident
1704 , person_id
1705 , business_group_id
1706 from per_absence_attendances
1707 where abs_information1 = to_char(p_parent_absence_id)
1708 and date_end <= p_max_end_date
1709 and person_id = p_person_id
1710 and business_group_id = p_business_group_id
1711 order by date_start;
1712 --
1713 TYPE t_absence is RECORD
1714 (absence_attendance_id number
1715 ,parent_absence_id number
1716 ,date_start date
1717 ,date_end date
1718 ,duration number
1719 ,effective_start_date date
1720 ,effective_end_date date
1721 ,subrogated varchar2(30)
1722 ,estimated varchar2(30)
1723 ,work_incident varchar2(30)
1724 ,person_id number
1725 ,business_group_id number
1726 );
1727 --
1728 abs_rec t_absence;
1729 parent_abs_rec t_absence;
1730 --
1731 l_duration number := 0;
1732 l_absence_start_date date;
1733 l_absence_end_date date;
1734
1735 l_proc varchar2(72) := g_package||'Get_Sickness_skip';
1736
1737 --
1738 procedure check_gap(p_absence_end_date date
1739 ,p_absence_start_date date) is
1740
1741 l_proc varchar2(72) := g_package||'check_gap';
1742
1743 begin
1744 hr_utility.set_location('Entering:'|| l_proc,10);
1745 if p_absence_start_date - p_absence_end_date - 1 > 2 then
1746 fnd_message.set_name ('PAY', 'PAY_75031_INVALID_LINK_ABS');
1747 fnd_message.raise_error;
1748 end if;
1749 hr_utility.set_location(' Leaving:'||l_proc, 70);
1750 end check_gap;
1751 --
1752 begin
1753 --
1754 -- Fetch absence attendance id corresponding to the element entry
1755 --
1756 l_proc := g_package||'get_sickness_skip';
1757 hr_utility.set_location('Entering:'|| l_proc,10);
1758
1759 open c_get_absence;
1760 fetch c_get_absence into abs_rec;
1761 close c_get_absence;
1762 --
1763 open c_get_entry_dates;
1764 fetch c_get_entry_dates into abs_rec.effective_start_date,
1765 abs_rec.effective_end_date;
1766 close c_get_entry_dates;
1767 --
1768 -- Was there any part of the current absence
1769 -- that was processed in some other (prorated) period??
1770 -- If yes, that contributes to the duration
1771 IF (abs_rec.date_start >= p_action_start_date) THEN
1772 l_duration := 0;
1773 ELSE
1774 l_duration := p_action_start_date - abs_rec.date_start ;
1775 END IF;
1776
1777 if abs_rec.parent_absence_id is not null then
1778 --
1779 -- get the parent absence details
1780 --
1781 open c_get_parent_absence(abs_rec.parent_absence_id);
1782 fetch c_get_parent_absence into parent_abs_rec;
1783 close c_get_parent_absence;
1784 --
1785 l_duration := parent_abs_rec.duration;
1786 --
1787 l_absence_end_date := parent_abs_rec.date_end;
1788 --
1789 -- get the child absences up to but not including the
1790 -- orginiating absence
1791 --
1792 for a in c_get_child_absence(parent_abs_rec.absence_attendance_id
1793 ,abs_rec.date_start - 1, parent_abs_rec.person_id,parent_abs_rec.business_group_id) loop
1794 --
1795 l_absence_start_date := a.date_start;
1796 --
1797 -- check length of time between linked absences
1798 --
1799 check_gap(l_absence_end_date,l_absence_start_date);
1800 --
1801 l_absence_end_date := a.date_end;
1802 l_duration := l_duration + a.duration;
1803
1804 hr_utility.set_location(' In Sickness Skip and duration ='||l_duration,60);
1805 end loop;
1806
1807
1808 --
1809 -- check length of time between initiating absence and last absence
1810 -- processed
1811 --
1812 check_gap(l_absence_end_date,abs_rec.date_start);
1813 else
1814 parent_abs_rec := abs_rec;
1815 end if;
1816 --
1817 g_absence_calc.element_entry_id := p_element_entry_id;
1818 g_absence_calc.date_earned := p_date_earned;
1819 g_absence_calc.id := abs_rec.absence_attendance_id;
1820 g_absence_calc.effective_start_date := abs_rec.effective_start_date;
1821 g_absence_calc.effective_end_date := abs_rec.effective_end_date;
1822 g_absence_calc.ijss_subrogated := abs_rec.subrogated;
1823 g_absence_calc.ijss_estimated := parent_abs_rec.estimated;
1824 g_absence_calc.parent_absence_id := parent_abs_rec.absence_attendance_id;
1825 g_absence_calc.parent_absence_start_date
1826 := parent_abs_rec.date_start;
1827 g_absence_calc.work_incident := parent_abs_rec.work_incident;
1828 g_absence_calc.prior_linked_absence_duration := l_duration;
1829 hr_utility.set_location(' In Sickness Skip and final duration ='||g_absence_calc.prior_linked_absence_duration,65);
1830 --
1831 if g_absence_calc.ijss_estimated = 'Y' then
1832 hr_utility.set_location(' Leaving:'||l_proc, 70);
1833 return 'N';
1834 else
1835 hr_utility.set_location(' Leaving:'||l_proc, 70);
1836 return 'Y';
1837 end if;
1838 END get_sickness_skip;
1839
1840
1841
1842 FUNCTION Get_Sickness_skip_result
1843 RETURN Varchar2 IS
1844 l_proc VARCHAR2(72) := g_package||'Get_Sickness_Skip_result';
1845 BEGIN
1846
1847 if g_absence_calc.ijss_estimated = 'Y' then
1848 hr_utility.set_location(' Leaving:'||l_proc, 70);
1849 return 'N';
1850 else
1851 hr_utility.set_location(' Leaving:'||l_proc, 70);
1852 return 'Y';
1853 end if;
1854
1855 END;
1856 --
1857 -----------------------------------------------------------------
1858 -- Function to compare the best net guarantee and to return the
1859 -- adjustment to previous paymnts if the best method changes
1860 -----------------------------------------------------------------
1861 PROCEDURE Compare_Guarantee
1862 (p_absence_arch IN OUT NOCOPY pay_fr_sick_pay_processing.t_absence_arch
1863 ,p_coverages IN OUT NOCOPY pay_fr_sick_pay_processing.t_coverages) IS
1864 --
1865 total_net number;
1866 best_net number;
1867 best_net_index number;
1868 prev_best_net_index number;
1869 prev_payment number := 0;
1870 prev_ijss number := 0;
1871 best_payment number := 0;
1872 best_ijss number := 0;
1873 gi_adjustment number := 0;
1874 gross_ijss_adjustment number := 0;
1875 --
1876 l_proc varchar2(72) := g_package||'Compare_Guarantee';
1877 begin
1878 --
1879 hr_utility.set_location('Entering:'|| l_proc,10);
1880
1881 for i in p_coverages.first..p_coverages.last loop
1882 -- identify the total guarantee
1883 total_net := nvl(p_coverages(i).net,0) + nvl(p_coverages(i).previous_net,0);
1884 hr_utility.trace('in p_coverages i:'||to_char(i)|| ' total_net:'||to_char(total_net));
1885 --
1886 -- if the total guarantee is better than the currently identified best
1887 -- then reset the current best
1888 --
1889 -- if it is the same as the current best and was the previous best method
1890 -- then use the previous best method
1891 --
1892 if total_net > best_net or best_net is null then
1893 best_net := total_net;
1894 best_net_index := i;
1895 elsif total_net = best_net then
1896 if p_coverages(i).best_method = 'P' then
1897 best_net_index := i;
1898 end if;
1899 end if;
1900 --
1901 if p_coverages(i).best_method = 'P' then
1902 prev_best_net_index := i;
1903 end if;
1904 --
1905 end loop;
1906 --
1907 -- Calculate the previous best GI payment and IJSS payment
1908 --
1909 if prev_best_net_index is not null then
1910 prev_payment := p_coverages(prev_best_net_index).previous_gi_payment +
1911 p_coverages(prev_best_net_index).previous_sick_adj;
1912 prev_ijss := p_coverages(prev_best_net_index).previous_ijss_gross;
1913 --
1914 p_coverages(prev_best_net_index).best_method := '';
1915 end if;
1916 --
1917 -- Calculate the current best GI payment and IJSS payment
1918 --
1919 best_payment := p_coverages(best_net_index).previous_gi_payment +
1920 p_coverages(best_net_index).previous_sick_adj;
1921 best_ijss := p_coverages(best_net_index).previous_ijss_gross;
1922 --
1923 --
1924 -- Store the adjustments required for HI payment and IJSS payment
1925 --
1926 p_absence_arch.gi_adjustment := best_payment - prev_payment;
1927 p_absence_arch.gross_ijss_adjustment := best_ijss - prev_ijss;
1928 --
1929 -- Set the best method flag
1930 --
1931 p_coverages(best_net_index).best_method := 'Y';
1932 hr_utility.set_location(' Leaving:'||l_proc, 70);
1933 --
1934 end compare_guarantee;
1935 --
1936 PROCEDURE Calc_IJSS_Gross
1937 (p_IJSS_Gross_Start_Date IN Date
1938 ,p_IJSS_Gross_End_Date IN Date
1939 ,p_coverage_idx IN Number
1940 ,p_coverages IN OUT NOCOPY pay_fr_sick_pay_processing.t_coverages
1941 ,p_absence_arch IN OUT NOCOPY pay_fr_sick_pay_processing.t_absence_arch) IS
1942 --
1943 TYPE t_ijss_gross_rec IS RECORD
1944 (Gross number
1945 ,Rate number
1946 ,Start_Date date
1947 ,End_Date date
1948 ,Number_of_days number);
1949 --
1950 TYPE t_ijss_gross IS TABLE OF t_ijss_gross_rec INDEX BY BINARY_INTEGER;
1951
1952 Cursor csr_global_value(c_global_name VARCHAR2,c_date_earned DATE) IS
1953 SELECT global_value
1954 FROM ff_globals_f
1955 WHERE global_name = c_global_name
1956 AND legislation_code = 'FR'
1957 AND c_date_earned BETWEEN effective_start_date AND effective_end_date;
1958
1959 --
1960 l_ijss_gross t_ijss_gross;
1961 l_current_rate number := -1;
1962 j number := 0;
1963 l_ijss_net_rate number := 0;
1964 l_ijss_net_for_guarantee1 number := 0;
1965 l_ijss_net_for_guarantee2 number := 0;
1966 --
1967 l_proc varchar2(72) := g_package||'Calc_IJSS_Gross';
1968 begin
1969 hr_utility.set_location('Entering:'|| l_proc,10);
1970 l_ijss_gross(1).Gross := 0;
1971 l_ijss_gross(2).Gross := 0;
1972 l_ijss_gross(1).Number_of_days := 0;
1973 l_ijss_gross(2).Number_of_days := 0;
1974 --
1975 for i in g_overlap.first..g_overlap.last loop
1976 if g_overlap(i).absence_day >= p_IJSS_Gross_Start_Date and
1977 g_overlap(i).absence_day <= p_IJSS_Gross_End_Date and
1978 g_overlap(i).IJSS_Gross > 0 then
1979 --
1980 if g_overlap(i).IJSS_Gross <> l_current_rate then
1981 l_current_rate := g_overlap(i).IJSS_Gross;
1982 j := j+1;
1983 end if;
1984 --
1985 if l_ijss_gross(j).Rate is null then
1986 l_ijss_gross(j).Rate := g_overlap(i).IJSS_Gross;
1987 l_ijss_gross(j).Start_Date := g_overlap(i).Absence_day;
1988 end if;
1989 --
1990 l_ijss_gross(j).Gross :=
1991 l_ijss_gross(j).Gross + g_overlap(i).IJSS_Gross;
1992 l_ijss_gross(j).End_Date := g_overlap(i).Absence_day;
1993 l_ijss_gross(j).Number_of_days := l_ijss_gross(j).Number_of_days + 1;
1994 --
1995 end if;
1996 end loop;
1997
1998 OPEN csr_global_value('FR_IJSS_NET_RATE',p_IJSS_Gross_Start_Date);
1999 FETCH csr_global_value INTO l_ijss_net_rate;
2000 CLOSE csr_global_value;
2001
2002 if l_ijss_gross(1).Rate is not null then
2003 p_coverages(p_coverage_idx).IJSS_Gross1 :=
2004 l_ijss_gross(1).Gross;
2005 p_coverages(p_coverage_idx).IJSS_Gross_rate1 :=
2006 l_ijss_gross(1).Rate;
2007 p_coverages(p_coverage_idx).IJSS_from_date1 :=
2008 l_ijss_gross(1).Start_Date;
2009 p_coverages(p_coverage_idx).IJSS_to_date1 :=
2010 l_ijss_gross(1).End_Date;
2011 p_coverages(p_coverage_idx).IJSS_Gross_days1 :=
2012 l_ijss_gross(1).Number_of_days;
2013
2014 IF g_absence_calc.initiator <> 'CPAM' THEN
2015
2016 l_ijss_net_for_guarantee1 := nvl(p_coverages(p_coverage_idx).IJSS_Gross1,0) * ((100 - l_ijss_net_rate)/100);
2017 p_coverages(p_coverage_idx).ijss_net_adjustment := (nvl(p_absence_arch.ijss_net,0) + nvl(p_absence_arch.ijss_payment,0) - l_ijss_net_for_guarantee1) ;
2018
2019 END IF;
2020
2021 end if;
2022 if l_ijss_gross(2).Rate is not null then
2023 p_coverages(p_coverage_idx).IJSS_Gross2 :=
2024 l_ijss_gross(2).Gross;
2025 p_coverages(p_coverage_idx).IJSS_Gross_rate2 :=
2026 l_ijss_gross(2).Rate;
2027 p_coverages(p_coverage_idx).IJSS_from_date2 :=
2028 l_ijss_gross(2).Start_Date;
2029 p_coverages(p_coverage_idx).IJSS_to_date2 :=
2030 l_ijss_gross(2).End_Date;
2031 p_coverages(p_coverage_idx).IJSS_Gross_days2 :=
2032 l_ijss_gross(2).Number_of_days;
2033
2034 IF g_absence_calc.initiator <> 'CPAM' THEN
2035
2036 l_ijss_net_for_guarantee2 := nvl(p_coverages(p_coverage_idx).IJSS_Gross2,0) *((100 - l_ijss_net_rate)/100);
2037 p_coverages(p_coverage_idx).ijss_net_adjustment := nvl(p_coverages(p_coverage_idx).ijss_net_adjustment,0) - l_ijss_net_for_guarantee2;
2038
2039 END IF;
2040
2041 end if;
2042 --
2043 hr_utility.set_location(' Leaving:'||l_proc, 70);
2044 end;
2045 --
2046
2047 -- +********************************************************************+
2048 -- | PRIVATE PROCEDURE |
2049 -- +********************************************************************+
2050 ------------------------------------------------------------------------
2051 --Bug:2683421
2052 --Procedure absence_not_processed
2053 --This Procedure will raise error if any of the sickness absences
2054 --was not processed prior to this absence.
2055 --
2056 --This will be called from calc_sicknes procedure
2057 ------------------------------------------------------------------------
2058
2059 Procedure absence_not_processed
2060 ( p_asg In pay_fr_sick_pay_processing.t_asg
2061 ,p_sickness_start_date In Date
2062 ) As
2063
2064 -- Added another parameters to fetch IJSS estimation flag
2065 -- and absence start date
2066 -- Cursor for getting sickness element entries
2067 Cursor csr_get_sickness_entries Is
2068 Select peef.element_entry_id,
2069 peef.effective_start_date,
2070 peef.effective_end_date,
2071 peef.assignment_id,
2072 ceil(months_between(peef.effective_end_date,peef.effective_start_date)) Period,
2073 --
2074 paa.date_start Abs_start_date,
2075 paa.date_end Abs_end_date,
2076 nvl(paa.abs_information8,'N') IJSS_estimate
2077 from
2078 pay_element_entries_f peef
2079 ,pay_element_links_f pelf
2080 ,pay_element_types_f petf
2081 ,per_absence_attendances paa
2082 where peef.assignment_id = p_asg.assignment_id
2083 and peef.element_link_id = pelf.element_link_id
2084 and pelf.element_type_id = petf.element_type_id
2085 and p_asg.action_start_date between pelf.effective_start_date and pelf.effective_end_date
2086 and petf.element_name = 'FR_SICKNESS_INFORMATION'
2087 and p_asg.action_start_date between petf.effective_start_date and petf.effective_end_date
2088 and peef.effective_start_date < trunc(p_sickness_start_date,'MONTH')
2089 and paa.absence_attendance_id = peef.creator_id
2090 -- added clause for selecting within a year
2091 and paa.date_start >= add_months(p_asg.action_start_date, -12)
2092 and peef.creator_type = 'A' ;
2093 --
2094
2095 -- Cursor for getting processed sickness elements' run results
2096 Cursor csr_get_entry_processed(c_element_entry_id Number,
2097 c_assignment_id Number,
2098 c_period Date) Is
2099 -- AS 5/9/3 commented out some date and status criteria here as error was being raised incorrectly
2100 Select 'Y'
2101 from pay_payroll_actions ppa
2102 ,pay_assignment_actions paa
2103 ,pay_run_results prr
2104 where ppa.payroll_id + 0 = p_asg.payroll_id
2105 -- and ppa.date_earned < trunc(p_sickness_start_date,'MONTH')
2106 -- and to_char(ppa.date_earned,'MON-YYYY') = to_char(c_period,'MON-YYYY')
2107 and paa.assignment_id + 0 = c_assignment_id
2108 and ppa.payroll_action_id = paa.payroll_action_id
2109 and ppa.action_type in ('R','Q')
2110 and prr.status in ('P','PA')
2111 -- and paa.action_status = 'C'
2112 and paa.assignment_action_id = prr.assignment_action_id
2113 and prr.source_id = c_element_entry_id;
2114
2115 -- Cursor for getting CPAM element run results
2116 Cursor csr_get_CPAM_results(c_abs_start_date DATE,
2117 c_abs_end_date DATE) is
2118 Select min(fnd_date.canonical_to_date(prrv_pst.result_value)) Pmt_start_dt,
2119 max(fnd_date.canonical_to_date(prrv_pet.result_value)) Pmt_end_dt,
2120 piv_pst.input_value_id pmt_start_input_id,
2121 piv_pet.input_value_id pmt_end_input_id
2122 --
2123 From pay_run_result_values prrv_pst,
2124 pay_run_result_values prrv_pet,
2125 pay_input_values_f piv_pst,
2126 pay_input_values_f piv_pet,
2127 pay_run_results prr_pst,
2128 pay_run_results prr_pet,
2129 pay_element_types_f peltf,
2130 pay_assignment_actions pact,
2131 pay_payroll_actions ppac
2132 --
2133 Where prrv_pst.result_value between fnd_date.date_to_canonical(c_abs_start_date) and fnd_date.date_to_canonical(c_abs_end_date)
2134 and prrv_pet.result_value between fnd_date.date_to_canonical(c_abs_start_date) and fnd_date.date_to_canonical(c_abs_end_date)
2135 --
2136 and prrv_pst.input_value_id = piv_pst.input_value_id
2137 and prrv_pst.run_result_id = prr_pst.run_result_id
2138 --
2139 and peltf.element_name = 'FR_SICKNESS_CPAM_PROCESS'
2140 and c_abs_start_date between peltf.effective_start_date and peltf.effective_end_date
2141 and piv_pst.element_type_id = peltf.element_type_id
2142 and piv_pst.name = 'Payment From Date'
2143 and c_abs_start_date between piv_pst.effective_start_date and piv_pst.effective_end_date
2144 ----
2145 and prr_pst.element_type_id = peltf.element_type_id
2146 and prr_pst.assignment_action_id = pact.assignment_action_id
2147 ----
2148 and pact.assignment_id = p_asg.assignment_id
2149 and pact.action_status ='C'
2150 and prr_pst.status in ('P','PA')
2151 and prr_pet.status in ('P','PA')
2152 and pact.payroll_action_id = ppac.payroll_action_id
2153 and ppac.action_type in ('R','Q')
2154 and ppac.payroll_id = p_asg.payroll_id
2155 and ppac.date_earned between c_abs_start_date and p_asg.action_end_date
2156 --
2157 and prrv_pet.input_value_id = piv_pet.input_value_id
2158 and prrv_pet.run_result_id = prr_pet.run_result_id
2159 --
2160 and piv_pet.element_type_id = peltf.element_type_id
2161 and piv_pet.name = 'Payment To Date'
2162 and c_abs_start_date between piv_pet.effective_start_date and piv_pet.effective_end_date
2163 --
2164 and prr_pet.element_type_id = peltf.element_type_id
2165 and prr_pet.assignment_action_id = pact.assignment_action_id
2166 group by piv_pst.input_value_id,piv_pet.input_value_id;
2167 --
2168 -- Cursor for getting CPAM element entries
2169 Cursor csr_get_cpam_entries(c_pst_input_value_id NUMBER,
2170 c_pet_input_value_id NUMBER,
2171 c_prev_pmt_end_dt DATE,
2172 c_abs_end_date DATE)IS
2173 --
2174 Select peval_pst.screen_entry_value pmt_start_date,
2175 peval_pet.screen_entry_value pmt_end_date
2176 --
2177 from pay_element_entry_values_f peval_pst,
2178 pay_element_entry_values_f peval_pet,
2179 pay_element_entries_f pentf,
2180 pay_element_links_f plink,
2181 pay_element_types_f peltf
2182 --
2183 where pentf.assignment_id = p_asg.assignment_id
2184 and pentf.element_link_id = plink.element_link_id
2185 and pentf.effective_start_date between p_asg.action_start_date and p_asg.action_end_date
2186 and plink.element_type_id = peltf.element_type_id
2187 and peltf.element_name = 'FR_SICKNESS_CPAM_PROCESS'
2188 and p_asg.action_start_date between peltf.effective_start_date and peltf.effective_end_date
2189 and p_asg.action_start_date between plink.effective_start_date and plink.effective_end_date
2190 and peval_pst.element_entry_id = pentf.element_entry_id
2191 and peval_pst.input_value_id = c_pst_input_value_id
2192 and peval_pet.element_entry_id = pentf.element_entry_id
2193 and peval_pet.input_value_id = c_pet_input_value_id
2194 and peval_pst.screen_entry_value > fnd_date.date_to_canonical(c_prev_pmt_end_dt)
2195 and peval_pet.screen_entry_value <= fnd_date.date_to_canonical(c_abs_end_date)
2196 and p_asg.action_end_date between peval_pst.effective_start_date and peval_pst.effective_end_date
2197 and p_asg.action_end_date between peval_pet.effective_start_date and peval_pet.effective_end_date;
2198 --
2199 l_entry_rec csr_get_sickness_entries%RowType;
2200 l_temp Varchar2(2);
2201 l_payment_Start_date Date;
2202 l_payment_end_date Date;
2203 l_pmt_start_id Number;
2204 l_pmt_end_id Number;
2205 l_prev_pmt_end_dt Date;
2206
2207 l_date Date;
2208
2209 Begin
2210
2211
2212 FOR l_entry_rec In csr_get_sickness_entries LOOP
2213 -- added extra condition for checkin IJSS estimate flag
2214 IF l_entry_rec.IJSS_estimate = 'Y' THEN
2215 -- check if absence has been processed
2216 --
2217 FOR i in 1..l_entry_rec.period LOOP
2218
2219 l_date := add_months(l_entry_rec.effective_start_date,i-1);
2220
2221 If l_date < p_sickness_start_date Then
2222
2223 hr_utility.trace('pre csr_get_entry_processed. l_entry_rec.element_entry_id:'||to_char(l_entry_rec.element_entry_id)|| ' l_entry_rec.assignment_id:'||to_char(l_entry_rec.assignment_id)|| ' l_date:'||to_char(l_date));
2224 Open csr_get_entry_processed(l_entry_rec.element_entry_id,l_entry_rec.assignment_id,l_date);
2225 Fetch csr_get_entry_processed Into l_temp;
2226
2227 If csr_get_entry_processed%NotFound Then
2228 Close csr_get_entry_processed;
2229 fnd_message.set_name('PAY', 'PAY_75043_SICK_NOT_PROCESSED');
2230 fnd_message.set_token('START_DATE',to_char(l_entry_rec.Abs_start_date,'DD/MM/YYYY'));
2231 fnd_message.set_token('END_DATE',to_char(l_entry_rec.Abs_end_date,'DD/MM/YYYY'));
2232 fnd_message.raise_error;
2233 End If;
2234 Close csr_get_entry_processed;
2235 END IF;
2236 END LOOP;
2237 ELSE
2238 -- Check if absence has been paid off
2239 -- Find CPAM element run results corresponding to this absence
2240 OPEN csr_get_CPAM_results(l_entry_rec.Abs_start_date, l_entry_rec.Abs_end_date);
2241 FETCH csr_get_CPAM_results INTO l_payment_Start_date,
2242 l_payment_end_date,
2243 l_pmt_start_id ,
2244 l_pmt_end_id;
2245 CLOSE csr_get_CPAM_results;
2246 --
2247 IF l_payment_Start_date IS NULL -- no payment
2248 OR l_payment_end_date < l_entry_rec.Abs_end_date -- incomplete payment
2249 THEN
2250 l_prev_pmt_end_dt := nvl(l_payment_end_date,l_entry_rec.Abs_start_date-1);
2251 -- Check for element entries of this period
2252 FOR cpam_entry_rec IN csr_get_cpam_entries(l_pmt_start_id,
2253 l_pmt_end_id,
2254 l_prev_pmt_end_dt,
2255 l_entry_rec.Abs_end_date) LOOP
2256 --
2257 IF fnd_date.canonical_to_date(cpam_entry_rec.pmt_start_date) = l_prev_pmt_end_dt +1
2258 OR fnd_date.canonical_to_date(cpam_entry_rec.pmt_start_date) = l_entry_rec.Abs_start_date THEN
2259 --
2260 IF fnd_date.canonical_to_date(cpam_entry_rec.pmt_end_date) = l_entry_rec.Abs_end_date THEN
2261 EXIT;
2262 END IF;
2263 l_prev_pmt_end_dt := fnd_date.canonical_to_date(cpam_entry_rec.pmt_end_date);
2264 ELSE
2265 fnd_message.set_name('PAY', 'PAY_75043_SICK_NOT_PROCESSED');
2266 fnd_message.set_token('START_DATE',to_char(l_entry_rec.Abs_start_date,'DD/MM/YYYY'));
2267 fnd_message.set_token('END_DATE',to_char(l_entry_rec.Abs_end_date,'DD/MM/YYYY'));
2268 fnd_message.raise_error;
2269 END IF;
2270 END LOOP;
2271 END IF;
2272 END IF;
2273
2274 END LOOP;
2275
2276 If csr_get_entry_processed%IsOpen Then
2277 Close csr_get_entry_processed;
2278 End If;
2279
2280 End absence_not_processed;
2281 --
2282 PROCEDURE Calc_Sickness(
2283 P_mode IN OUT NOCOPY VARCHAR2 ,
2284 p_asg IN OUT NOCOPY pay_fr_sick_pay_processing.t_asg,
2285 p_absence_arch IN OUT NOCOPY pay_fr_sick_pay_processing.t_absence_arch,
2286 p_coverages IN OUT NOCOPY pay_fr_sick_pay_processing.t_coverages) IS
2287 --
2288 -- cursor added for CPAM payment
2289 -- to get period start and end dates for absence being processed
2290 cursor csr_abs_period_dates (c_business_group_id number
2291 ,c_payroll_id number
2292 ,c_payroll_action_id number
2293 ,c_payment_start_date date
2294 ,c_payment_end_date date) is
2295 select PTP.start_date,
2296 PTP.end_date
2297 from pay_payroll_actions PPA,
2298 per_time_periods PTP
2299 where ppa.payroll_action_id = c_payroll_action_id
2300 and ppa.business_group_id = c_business_group_id
2301 and ppa.payroll_id = c_payroll_id
2302 and ppa.payroll_id = ptp.payroll_id
2303 and c_payment_start_date between ptp.start_date and ptp.end_date
2304 and c_payment_end_date between ptp.start_date and ptp.end_date ;
2305 --
2306 l_work_inc_class varchar2(30);
2307 l_first_day date;
2308 l_net number := 0;
2309 l_index number;
2310 -- declared for calculating paid days
2311 l_total_days number;
2312 l_idx_coverages number;
2313 l_idx_bm number;
2314 --
2315 l_proc varchar2(72) := g_package||'Calc_Sickness';
2316 begin
2317 hr_utility.set_location('Entering:'|| l_proc,10);
2318 if p_mode = 'I' then
2319 -- Code added for CPAM payment processing
2320 IF g_absence_calc.initiator = 'CPAM' THEN
2321 -- get the absence period dates
2322 OPEN csr_abs_period_dates (p_asg.business_group_id
2323 ,p_asg.payroll_id
2324 ,p_asg.payroll_action_id
2325 ,g_absence_calc.effective_start_date
2326 ,g_absence_calc.effective_end_date);
2327 FETCH csr_abs_period_dates into g_absence_calc.abs_ptd_start_date
2328 ,g_absence_calc.abs_ptd_end_date;
2329 CLOSE csr_abs_period_dates;
2330 --
2331 p_absence_arch.sick_deduct_start_date := greatest(g_absence_calc.effective_start_date
2332 ,g_absence_calc.abs_ptd_start_date);
2333 p_absence_arch.sick_deduct_end_date := least(g_absence_calc.effective_end_date
2334 ,g_absence_calc.abs_ptd_end_date);
2335 Get_CPAM_ref_salary( p_asg.business_group_id,
2336 p_asg.assignment_id,
2337 p_asg);
2338 --Check for unprocessed sickness absences.
2339 absence_not_processed(p_asg,p_absence_arch.sick_deduct_start_date);
2340
2341 IF g_absence_calc.work_incident = 'N' THEN
2342 l_work_inc_class := 'N';
2343 ELSE
2344 l_work_inc_class := 'O';
2345 END IF;
2346 --
2347 -- Calling procedure to populate g_overlap table
2348 Get_sickness_CPAM_IJSS(
2349 p_business_group_id => p_asg.business_group_id,
2350 p_assignment_id => p_asg.assignment_id,
2351 p_absence_id => g_absence_calc.ID,
2352 p_start_date => p_absence_arch.sick_deduct_start_date,
2353 p_end_date => p_absence_arch.sick_deduct_end_date ,
2354 p_work_inc_class => l_work_inc_class);
2355 --
2356 ELSE
2357 p_absence_arch.sick_deduct_start_date := greatest(g_absence_calc.effective_start_date
2358 ,p_asg.action_start_date);
2359 --
2360 p_absence_arch.sick_deduct_end_date := least(g_absence_calc.effective_end_date
2361 ,p_asg.action_end_date
2362 ,p_absence_arch.date_earned);
2363
2364 --Check For unprossed sickness absences.
2365 absence_not_processed(p_asg,p_absence_arch.sick_deduct_start_date);
2366 --
2367 -- Call calculate_IJSS to populate g_overlap table
2368 --
2369 if g_absence_calc.work_incident = 'N' then
2370 l_work_inc_class := 'N';
2371 else
2372 l_work_inc_class := 'O';
2373 end if;
2374 --
2375 Calc_IJSS
2376 (p_business_group_id => p_asg.business_group_id
2377 ,p_assignment_id => p_asg.assignment_id
2378 ,p_absence_id => g_absence_calc.ID
2379 ,p_start_date => p_absence_arch.sick_deduct_start_date
2380 ,p_end_date => p_absence_arch.sick_deduct_end_date
2381 ,p_absence_duration => g_absence_calc.prior_linked_absence_duration
2382 ,p_work_inc_class => l_work_inc_class);
2383 --
2384 END IF;
2385 --
2386 Calculate_Sickness_Deduction
2387 (p_absence_start_date => p_absence_arch.sick_deduct_start_date
2388 ,p_absence_end_date => p_absence_arch.sick_deduct_end_date
2389 ,p_asg => p_asg
2390 ,p_absence_arch => p_absence_arch);
2391
2392 p_absence_arch.ijss_estimated := g_absence_calc.IJSS_estimated;
2393 p_absence_arch.parent_absence_id := g_absence_calc.parent_absence_id;
2394
2395 --
2396 --
2397 -- Calculate the sum of the IJSS_Net values for the days to be paid
2398 --
2399 hr_utility.set_location(' 1st row in g_overlap='||g_overlap.first||' and last row='||g_overlap.last,20);
2400 if g_overlap.count > 0 THEN
2401
2402 for i in g_overlap.first..g_overlap.last loop
2403 l_net := l_net + g_overlap(i).IJSS_Net;
2404 --
2405 hr_utility.set_location(' Date of row(idx='||g_overlap.first||')in g_overlap='||g_overlap(i).absence_day,40);
2406 if g_overlap(i).Holiday is null then
2407 if l_first_day is null then
2408 l_first_day := g_overlap(i).absence_day;
2409 end if;
2410 end if;
2411 end loop;
2412 --
2413
2414 if l_first_day is not null then
2415 hr_utility.set_location(' For GIs,start date='||l_first_day||' and end date='||g_overlap(g_overlap.last).Absence_day,50);
2416 g_absence_calc.IJSS_payment_start_date := l_first_day;
2417 g_absence_calc.IJSS_payment_end_date
2418 := g_overlap(g_overlap.last).Absence_day;
2419
2420 --
2421 if g_absence_calc.ijss_subrogated = 'Y' then
2422 p_absence_arch.ijss_payment := l_net;
2423 else
2424 p_absence_arch.ijss_net := l_net;
2425 end if;
2426 --
2427 Calc_LEGAL_GI
2428 (p_asg => p_asg
2429 ,p_coverages => p_coverages
2430 ,p_absence_arch => p_absence_arch);
2431 --
2432 Calc_CAGR_GI
2433 (p_asg => p_asg
2434 ,p_coverages => p_coverages
2435 ,p_absence_arch => p_absence_arch);
2436 --
2437 end if;
2438 end if;
2439 --
2440 -- Populate a row for No Guarantee always
2441 --
2442 l_index := nvl(p_coverages.LAST, 0) + 1;
2443 p_coverages(l_index).g_type
2444 := pay_fr_sick_pay_processing.get_guarantee_id('NO_G');
2445
2446 --
2447 else -- p_mode = 'C'
2448 Compare_Guarantee
2449 (p_absence_arch => p_absence_arch
2450 ,p_coverages => p_coverages);
2451 end if;
2452 -- Added for calculating paid days
2453 -- Find total days
2454 l_total_days := p_absence_arch.sick_deduct_end_date - p_absence_arch.sick_deduct_start_date +1;
2455 hr_utility.set_location('Total Days: '||l_total_days, 22);
2456
2457 -- Find the best method row in p_coverages
2458 FOR l_idx_coverages IN p_coverages.FIRST..p_coverages.LAST LOOP
2459 hr_utility.set_location('Index for p_coverages: '||l_idx_coverages, 22);
2460 hr_utility.set_location('Best Method Flag for Index Number '||l_idx_coverages||' is: '||p_coverages(l_idx_coverages).BEST_METHOD, 22);
2461 IF p_coverages(l_idx_coverages).BEST_METHOD ='Y' THEN
2462 l_idx_bm := l_idx_coverages;
2463 EXIT;
2464 ELSE
2465 l_idx_bm := -1;
2466 END IF;
2467 END LOOP;
2468 hr_utility.set_location('Idx for best method: '||l_idx_bm, 22);
2469
2470 -- Check g_type, overlapping it with the g_guarantee_type_lookups
2471 IF l_idx_bm <> -1 THEN
2472 IF p_coverages(l_idx_bm).g_type <> -1 AND
2473 p_coverages(l_idx_bm).g_type = pay_fr_sick_pay_processing.get_guarantee_id('GN') THEN
2474 hr_utility.set_location('Calculations when guarantee is GN', 22);
2475 p_absence_arch.partial_paid_days := 0;
2476 p_absence_arch.unpaid_days := l_total_days - (p_coverages(l_idx_bm).gi_days1 + p_coverages(l_idx_bm).gi_days2);
2477 ELSE
2478 hr_utility.set_location('Calculations when guarantee is NOT GN', 22);
2479 p_absence_arch.partial_paid_days := p_coverages(l_idx_bm).gi_days1 + p_coverages(l_idx_bm).gi_days2;
2480 p_absence_arch.unpaid_days := l_total_days - p_absence_arch.partial_paid_days;
2481 END IF;
2482 END IF;
2483 --
2484 hr_utility.set_location(' Leaving:'||l_proc, 70);
2485 end calc_sickness;
2486 --
2487 -----------------------------------------------------------------------
2488 -- Function FR_ROLLING_BALANCE
2489 -- function to return rolling balance values
2490 ----------------------------------------------------------------------
2491 Function fr_rolling_balance (p_assignment_id in number,
2492 p_balance_name in varchar2,
2493 p_balance_start_date in date,
2494 p_balance_end_date in date) return number
2495 IS
2496 Cursor csr_def_bal_id IS
2497 SELECT pdb.defined_balance_id
2498 FROM pay_balance_types pbt,
2499 pay_balance_dimensions pbd,
2500 pay_defined_balances pdb
2501 WHERE pdb.balance_type_id = pbt.balance_type_id
2502 AND pdb.balance_dimension_id = pbd.balance_dimension_id
2503 AND pbt.balance_name = p_balance_name
2504 AND pbd.database_item_suffix = '_ASG_PTD'
2505 AND pdb.legislation_code = 'FR';
2506 --
2507 l_defined_balance_id number;
2508 l_start number := to_char(p_balance_start_date,'J');
2509 l_end number := to_char(p_balance_end_date,'J');
2510 i number := 0;
2511 l_value number := 0;
2512 l_proc varchar2(72) := g_package||'fr_rolling_balance';
2513 BEGIN
2514 hr_utility.set_location('Entering:'|| l_proc,10);
2515 open csr_def_bal_id;
2516 fetch csr_def_bal_id into l_defined_balance_id;
2517 close csr_def_bal_id;
2518 --
2519 while add_months(p_balance_start_date,i) <= p_balance_end_date loop
2520 BEGIN
2521 l_value := l_value +
2522 pay_balance_pkg.get_value
2523 (l_defined_balance_id
2524 ,p_assignment_id
2525 ,add_months(p_balance_start_date,i+1)-1);
2526
2527 EXCEPTION
2528 WHEN NO_DATA_FOUND THEN --Bug #2651568
2529 l_value := 0;
2530 END;
2531 i := i + 1;
2532 hr_utility.set_location(' BAL VAL='||l_value, 60);
2533 end loop;
2534 hr_utility.set_location(' FINAL BAL VAL='||l_value, 60);
2535 hr_utility.set_location(' Leaving:'||l_proc, 70);
2536 return l_value;
2537 END;
2538 -----------------------------------------------------------------------------
2539 -- SET_GLOBAL_IDS
2540 -- cache the id's for sudit elements and inputs
2541 -----------------------------------------------------------------------------
2542 PROCEDURE SET_GLOBAL_IDS(p_effective_date in DATE) is
2543 l_proc varchar2(72) := g_package||'SET_GLOBAL_IDS';
2544 begin
2545 hr_utility.set_location('Entering:'|| l_proc,10);
2546 select max(e.element_type_id)
2547 ,max(decode(i.name,'Parent Absence ID',i.input_value_id,null))
2548 ,max(decode(i.name,'Guarantee Type',i.input_value_id,null))
2549 ,max(decode(i.name,'Guarantee ID',i.input_value_id,null))
2550 ,max(decode(i.name,'GI Payment',i.input_value_id,null))
2551 ,max(decode(i.name,'Net',i.input_value_id,null))
2552 ,max(decode(i.name,'Adjustment',i.input_value_id,null))
2553 ,max(decode(i.name,'IJSS Gross',i.input_value_id,null))
2554 ,max(decode(i.name,'Best Method',i.input_value_id,null))
2555 ,max(decode(i.name,'Band1',i.input_value_id,null))
2556 ,max(decode(i.name,'Band2',i.input_value_id,null))
2557 ,max(decode(i.name,'Band3',i.input_value_id,null))
2558 ,max(decode(i.name,'Band4',i.input_value_id,null))
2559 ,max(decode(i.name,'Payment Start Date',i.input_value_id,null))
2560 ,max(decode(i.name,'Payment End Date',i.input_value_id,null))
2561 into g_gi_info_element_type_id
2562 ,g_gi_info_absence_id_iv_id
2563 ,g_gi_info_guarantee_type_iv_id
2564 ,g_gi_info_guarantee_id_iv_id
2565 ,g_gi_info_gi_payment_iv_id
2566 ,g_gi_info_net_iv_id
2567 ,g_gi_info_adjustment_iv_id
2568 ,g_gi_info_ijss_gross_iv_id
2569 ,g_gi_info_best_method_iv_id
2570 ,g_gi_info_band1_iv_id
2571 ,g_gi_info_band2_iv_id
2572 ,g_gi_info_band3_iv_id
2573 ,g_gi_info_band4_iv_id
2574 ,g_gi_info_start_date_iv_id
2575 ,g_gi_info_end_date_iv_id
2576 from pay_element_types_f e,
2577 pay_input_values_f i
2578 where e.element_name = 'FR_SICKNESS_GI_INFO'
2579 and e.legislation_code = 'FR'
2580 and e.element_type_id = i.element_type_id
2581 and p_effective_date between e.effective_start_date and e.effective_end_date
2582 and p_effective_date between i.effective_start_date and i.effective_end_date;
2583
2584 select max(e.element_type_id)
2585 ,max(decode(i.name,'Parent Absence ID',i.input_value_id,null))
2586 ,max(decode(i.name,'Guarantee Type',i.input_value_id,null))
2587 ,max(decode(i.name,'Guarantee ID',i.input_value_id,null))
2588 ,max(decode(i.name,'GI Payment',i.input_value_id,null))
2589 ,max(decode(i.name,'Net',i.input_value_id,null))
2590 ,max(decode(i.name,'Adjustment',i.input_value_id,null))
2591 ,max(decode(i.name,'IJSS Gross',i.input_value_id,null))
2592 ,max(decode(i.name,'Best Method',i.input_value_id,null))
2593 ,max(decode(i.name,'Band1',i.input_value_id,null))
2594 ,max(decode(i.name,'Band2',i.input_value_id,null))
2595 ,max(decode(i.name,'Band3',i.input_value_id,null))
2596 ,max(decode(i.name,'Band4',i.input_value_id,null))
2597 ,max(decode(i.name,'Payment Start Date',i.input_value_id,null))
2598 ,max(decode(i.name,'Payment End Date',i.input_value_id,null))
2599 into g_gi_i_r_element_type_id
2600 ,g_gi_i_r_absence_id_iv_id
2601 ,g_gi_i_r_guarantee_type_iv_id
2602 ,g_gi_i_r_guarantee_id_iv_id
2603 ,g_gi_i_r_gi_payment_iv_id
2604 ,g_gi_i_r_net_iv_id
2605 ,g_gi_i_r_adjustment_iv_id
2606 ,g_gi_i_r_ijss_gross_iv_id
2607 ,g_gi_i_r_best_method_iv_id
2608 ,g_gi_i_r_band1_iv_id
2609 ,g_gi_i_r_band2_iv_id
2610 ,g_gi_i_r_band3_iv_id
2611 ,g_gi_i_r_band4_iv_id
2612 ,g_gi_i_r_start_date_iv_id
2613 ,g_gi_i_r_end_date_iv_id
2614 from pay_element_types_f e,
2615 pay_input_values_f i
2616 where e.element_name = 'FR_SICKNESS_GI_INFO_RETRO'
2617 and e.legislation_code = 'FR'
2618 and e.element_type_id = i.element_type_id
2619 and p_effective_date between e.effective_start_date and e.effective_end_date
2620 and p_effective_date between i.effective_start_date and i.effective_end_date;
2621
2622 hr_utility.set_location('Leaving:'|| l_proc,90);
2623
2624 end SET_GLOBAL_IDS;
2625 --
2626 -----------------------------------------------------------------------
2627 -- GET_GI_PAYMENTS_AUDIT
2628 -- fetch results from element FR_SICKNESS_GI_INFO for a particular
2629 -- absence and guarantee
2630 -----------------------------------------------------------------------
2631 --
2632 PROCEDURE Get_GI_Payments_Audit
2633 (p_GI_id IN Number,
2634 p_asg IN pay_fr_sick_pay_processing.t_asg,
2635 p_parent_absence_id IN Number,
2636 p_current_date IN Date,
2637 p_GI_Previous_Net OUT NOCOPY Number,
2638 p_GI_Previous_Payment OUT NOCOPY Number,
2639 p_GI_Previous_Adjustment OUT NOCOPY Number,
2640 p_GI_Previous_IJSS_Gross OUT NOCOPY Number,
2641 p_GI_Best_Method OUT NOCOPY Varchar2) IS
2642
2643 cursor get_audit( p_parent_absence_id varchar2,
2644 p_guarantee_id varchar2,
2645 p_assignment_id number,
2646 p_parent_absence_start_date date) is
2647 -- fetch audit results for this absence
2648 -- Net Payment Adjustment IJSS Gross Best Method for each Guarantee ID
2649 select /*+ORDERED */
2650 assact.action_sequence action_sequence,
2651 nvl(sum(decode(target.input_value_id,
2652 g_gi_info_net_iv_id,fnd_number.canonical_to_number(target.result_value),
2653 g_gi_i_r_net_iv_id, fnd_number.canonical_to_number(target.result_value),
2654 0)),0) previous_net,
2655 nvl(sum(decode(target.input_value_id,
2656 g_gi_info_gi_payment_iv_id,
2657 fnd_number.canonical_to_number(target.result_value),
2658 g_gi_i_r_gi_payment_iv_id,
2659 fnd_number.canonical_to_number(target.result_value),
2660 0)),0) previous_payment,
2661 nvl(sum(decode(target.input_value_id,
2662 g_gi_info_adjustment_iv_id,
2663 fnd_number.canonical_to_number(target.result_value),
2664 g_gi_i_r_adjustment_iv_id,
2665 fnd_number.canonical_to_number(target.result_value),
2666 0)),0) previous_adjustment,
2667 nvl(sum(decode(target.input_value_id,
2668 g_gi_info_ijss_gross_iv_id,
2669 fnd_number.canonical_to_number(target.result_value),
2670 g_gi_i_r_ijss_gross_iv_id,
2671 fnd_number.canonical_to_number(target.result_value),
2672 0)),0) previous_IJSS_gross,
2673 max(decode(target.input_value_id,
2674 g_gi_info_end_date_iv_id,
2675 fnd_date.canonical_to_date(target.result_value),
2676 g_gi_i_r_end_date_iv_id,
2677 fnd_date.canonical_to_date(target.result_value),
2678 null)) payment_end_date,
2679 max(decode(target.input_value_id,
2680 g_gi_info_best_method_iv_id,target.result_value,
2681 g_gi_i_r_best_method_iv_id, target.result_value,null)) best_method,
2682 nvl(ee.creator_id,0) retro_asg_action,
2683 epd.adjustment_type retro_adj_type
2684 from pay_assignment_actions assact
2685 ,pay_payroll_actions pact
2686 ,pay_run_results rr
2687 ,pay_run_result_values guarantee_id
2688 ,pay_run_result_values parent_absence_id
2689 ,pay_run_result_values target
2690 ,pay_entry_process_details epd
2691 ,pay_element_entries_f ee
2692 where assact.assignment_id = p_assignment_id
2693 and assact.payroll_action_id = pact.payroll_action_id
2694 and pact.action_type in ('R', 'Q', 'B')
2695 and pact.date_earned >= p_parent_absence_start_date
2696 and assact.assignment_action_id = rr.assignment_action_id
2697 and rr.element_type_id in (g_gi_info_element_type_id,
2698 g_gi_i_r_element_type_id)
2699 and rr.status in ('P','PA')
2700 and epd.element_entry_id(+) = rr.element_entry_id
2701 and epd.retro_component_id(+) is not null
2702 and ee.element_entry_id(+) = epd.element_entry_id
2703 and target.run_result_id = rr.run_result_id
2704 and target.input_value_id in (g_gi_info_net_iv_id,
2705 g_gi_i_r_net_iv_id,
2706 g_gi_info_gi_payment_iv_id,
2707 g_gi_i_r_gi_payment_iv_id,
2708 g_gi_info_adjustment_iv_id,
2709 g_gi_i_r_adjustment_iv_id,
2710 g_gi_info_ijss_gross_iv_id,
2711 g_gi_i_r_ijss_gross_iv_id,
2712 g_gi_info_end_date_iv_id,
2713 g_gi_i_r_end_date_iv_id,
2714 g_gi_info_best_method_iv_id,
2715 g_gi_i_r_best_method_iv_id)
2716 and target.result_value is not null
2717 and parent_absence_id.run_result_id = rr.run_result_id
2718 and parent_absence_id.input_value_id in (g_gi_info_absence_id_iv_id,
2719 g_gi_i_r_absence_id_iv_id)
2720 and parent_absence_id.result_value = p_parent_absence_id
2721 and guarantee_id.run_result_id = rr.run_result_id
2722 and guarantee_id.input_value_id in (g_gi_info_guarantee_id_iv_id,
2723 g_gi_i_r_guarantee_id_iv_id)
2724 and guarantee_id.result_value = p_guarantee_id
2725 and NOT EXISTS
2726 (SELECT 1
2727 FROM pay_payroll_actions RPACT
2728 , pay_assignment_actions RASSACT
2729 , pay_action_interlocks RINTLK
2730 where ASSACT.assignment_action_id = RINTLK.locked_action_id
2731 and RINTLK.locking_action_id = RASSACT.assignment_action_id
2732 and RPACT.payroll_action_id = RASSACT.payroll_action_id
2733 and RPACT.action_type = 'V' )
2734 group by assact.action_sequence, rr.run_result_id,
2735 ee.creator_id,epd.adjustment_type
2736 order by 1,6,8,9 desc;
2737
2738 -- previous_net, previous_payment, previous_adjustment, previous_IJSS_gross, best_method
2739 l_previous_net number;
2740 l_previous_payment number;
2741 l_previous_adjustment number;
2742 l_previous_IJSS_gross number;
2743 l_best_method varchar2(1);
2744
2745 l_proc varchar2(72) := g_package||'Get_GI_Payments_Audit';
2746
2747 begin
2748 hr_utility.set_location('Entering:'|| l_proc,10);
2749 begin
2750 -- fetch ids of element_type and its inputs
2751 if g_gi_info_element_type_id is null then
2752 set_global_ids(p_effective_date => p_current_date);
2753 END IF;
2754
2755 hr_utility.set_location('Entering:'||l_proc||' g_gi_info_element_type_id:'||
2756 to_char(g_gi_info_element_type_id),10);
2757
2758 end;
2759 --
2760 begin
2761 -- fetch the audit payments for this absence and guarantee
2762 -- previous_net, previous_payment, previous_adjustment, previous_IJSS_gross, best_method
2763
2764 for a in get_audit(fnd_number.number_to_canonical( p_parent_absence_id),
2765 fnd_number.number_to_canonical(p_gi_id),
2766 p_asg.assignment_id,
2767 nvl(g_absence_calc.parent_absence_start_date,
2768 to_date('01/01/0001','dd/mm/yyyy'))) loop
2769
2770 p_gi_previous_net := a.previous_net + p_gi_previous_net;
2771 p_gi_previous_payment := a.previous_payment + p_gi_previous_payment;
2772 p_gi_previous_adjustment := a.previous_adjustment + p_gi_previous_adjustment;
2773 p_gi_previous_ijss_gross := a.previous_ijss_gross + p_gi_previous_ijss_gross;
2774 -- the cursor is ordered so that the last row retrieved is the one that records the last
2775 -- best_method - in normal runs thats the last action but in the case of retros also take
2776 -- payment date and whether its a contra result into account
2777 if a.best_method = 'Y' then p_gi_best_method := 'P';
2778 else p_gi_best_method := '';
2779 end if;
2780 end loop;
2781
2782 hr_utility.set_location('Leaving :'|| l_proc, 90);
2783 end;
2784 --
2785 hr_utility.set_location(' Leaving:'||l_proc, 70);
2786 end get_gi_payments_audit;
2787
2788
2789 PROCEDURE Band_Overlaps
2790 (p_ov_band1_days IN OUT NOCOPY NUMBER,
2791 p_ov_band2_days IN OUT NOCOPY NUMBER,
2792 p_ov_band3_days IN OUT NOCOPY NUMBER,
2793 p_ov_band4_days IN OUT NOCOPY NUMBER,
2794 p_ov_gi_id IN NUMBER,
2795 p_date_from IN DATE,
2796 p_date_to IN DATE,
2797 p_asg IN pay_fr_sick_pay_processing.t_asg)
2798 IS
2799
2800 l_proc varchar2(72) := g_package||'Band_Overlaps';
2801 l_ov_gi_id_chr varchar2(100) := to_char(p_ov_gi_id);
2802 l_date_from_chr varchar2(20) := fnd_date.date_to_canonical(p_date_from);
2803
2804 l_overlap_band1_days number := 0;
2805 l_overlap_band2_days number := 0;
2806 l_overlap_band3_days number := 0;
2807 l_overlap_band4_days number := 0;
2808
2809 l_payment_end_date date;
2810
2811 l_number_of_days_to_add_back number :=0;
2812
2813 -- We now have the band usage from the start of the period upto the parent_absence_start_date
2814 -- From this we need to find if there were any overlapping absences during the start of the period -- ( l_date_from ).
2815 --
2816 -- E.g. if the parent_absence_start_date is 15-FEB-2002 and we are using a rolling year, we go back to
2817 -- the 15-FEB-2001. At this point we have all the band usage between the 15-FEB-2001 and 15-FEB-2002 but
2818 -- but this also includes any band usage from the 10-FEB-2001 to the 15-FEB-2001 which we need to remove -- from the band usage totals from th previous statement.
2819
2820
2821 -- 15-FEB-2001 15-FEB-2002
2822 -- |________________|_______________________________________|
2823 -- |--------------------|
2824 -- 10-FEB 20-FEB
2825
2826 --
2827 -- The following statement will find the result id for the overlap if one exists.
2828 --
2829 BEGIN
2830 hr_utility.set_location('Entering:'|| l_proc,10);
2831 select /*+ORDERED*/
2832 fnd_date.canonical_to_date(rrv_end.result_value) payment_end_date
2833 ,nvl(sum(decode(target.input_value_id,
2834 g_gi_info_band1_iv_id,target.result_value,
2835 g_gi_i_r_band1_iv_id, target.result_value,0)),0)
2836 ,nvl(sum(decode(target.input_value_id,
2837 g_gi_info_band2_iv_id,target.result_value,
2838 g_gi_i_r_band2_iv_id, target.result_value,0)),0)
2839 ,nvl(sum(decode(target.input_value_id,
2840 g_gi_info_band3_iv_id,target.result_value,
2841 g_gi_i_r_band3_iv_id, target.result_value,0)),0)
2842 ,nvl(sum(decode(target.input_value_id,
2843 g_gi_info_band4_iv_id,target.result_value,
2844 g_gi_i_r_band4_iv_id, target.result_value,0)),0)
2845 INTO l_payment_end_date
2846 ,l_overlap_band1_days
2847 ,l_overlap_band2_days
2848 ,l_overlap_band3_days
2849 ,l_overlap_band4_days
2850 from pay_assignment_actions assact,
2851 pay_payroll_actions pact,
2852 pay_run_results rr,
2853 pay_run_result_values rrv_end,
2854 pay_run_result_values rrv_start,
2855 pay_run_result_values rrv_guarantee,
2856 pay_run_result_values target
2857 where assact.assignment_id = P_asg.assignment_id
2858 and assact.action_status = 'C'
2859 and assact.payroll_action_id = pact.payroll_action_id
2860 and pact.action_type in ('R','Q','B','V')
2861 and assact.assignment_action_id = rr.assignment_action_id
2862 and rr.element_type_id in (g_gi_info_element_type_id,
2863 g_gi_i_r_element_type_id)
2864 and rr.status in ('P','PA')
2865 and rr.run_result_id = rrv_start.run_result_id
2866 and rrv_start.input_value_id in (g_gi_info_start_date_iv_id,
2867 g_gi_i_r_start_date_iv_id )
2868 and rrv_start.result_value < l_date_from_chr
2869 and rr.run_result_id = rrv_end.run_result_id
2870 and rrv_end.input_value_id in (g_gi_info_end_date_iv_id,
2871 g_gi_i_r_end_date_iv_id)
2872 and rrv_end.result_value >= l_date_from_chr
2873 and rr.run_result_id = rrv_guarantee.run_result_id
2874 and rrv_guarantee.input_value_id in (g_gi_info_guarantee_id_iv_id,
2875 g_gi_i_r_guarantee_id_iv_id )
2876 and rrv_guarantee.result_value = l_ov_gi_id_chr
2877 and target.run_result_id = rr.run_result_id
2878 and target.input_value_id in (g_gi_info_band1_iv_id,
2879 g_gi_info_band2_iv_id,
2880 g_gi_info_band3_iv_id,
2881 g_gi_info_band4_iv_id,
2882 g_gi_i_r_band1_iv_id,
2883 g_gi_i_r_band2_iv_id,
2884 g_gi_i_r_band3_iv_id,
2885 g_gi_i_r_band4_iv_id)
2886 and target.result_value is not null
2887 group by fnd_date.canonical_to_date(rrv_end.result_value);
2888
2889 p_ov_band1_days := p_ov_band1_days - l_overlap_band1_days;
2890 p_ov_band2_days := p_ov_band2_days - l_overlap_band2_days;
2891 p_ov_band3_days := p_ov_band3_days - l_overlap_band3_days;
2892 p_ov_band4_days := p_ov_band4_days - l_overlap_band4_days;
2893
2894 --
2895 -- Now add the relevant part back in
2896 --
2897 l_number_of_days_to_add_back := (l_payment_end_date - p_date_from) + 1;
2898 hr_utility.trace('pymt end date = ' || l_payment_end_date || ' p_date_from = ' || p_date_from);
2899 hr_utility.trace('Band_Overlaps - number Of days to add back = ' || l_number_of_days_to_add_back);
2900 --
2901 -- For the overlap find which bands were used and how many days were used, starting with
2902 -- the highest band.
2903 --
2904
2905 IF l_number_of_days_to_add_back <= l_overlap_band4_days THEN
2906 p_ov_band4_days := p_ov_band4_days + l_number_of_days_to_add_back;
2907 l_number_of_days_to_add_back := l_number_of_days_to_add_back - l_overlap_band4_days;
2908 ELSE
2909 p_ov_band4_days := p_ov_band4_days - l_overlap_band4_days;
2910 l_number_of_days_to_add_back := l_number_of_days_to_add_back - l_overlap_band4_days;
2911 END IF;
2912
2913 IF l_number_of_days_to_add_back > 0 THEN
2914 IF l_number_of_days_to_add_back <= l_overlap_band3_days THEN
2915 p_ov_band3_days := p_ov_band3_days + l_number_of_days_to_add_back;
2916 l_number_of_days_to_add_back := l_number_of_days_to_add_back - l_overlap_band3_days;
2917 ELSE
2918 p_ov_band3_days := p_ov_band3_days - l_overlap_band3_days;
2919 l_number_of_days_to_add_back := l_number_of_days_to_add_back - l_overlap_band3_days;
2920 END IF;
2921 END IF;
2922
2923 IF l_number_of_days_to_add_back > 0 THEN
2924 IF l_number_of_days_to_add_back <= l_overlap_band2_days THEN
2925 p_ov_band2_days := p_ov_band2_days + l_number_of_days_to_add_back;
2926 l_number_of_days_to_add_back := l_number_of_days_to_add_back - l_overlap_band2_days;
2927 ELSE
2928 p_ov_band2_days := p_ov_band2_days - l_overlap_band2_days;
2929 l_number_of_days_to_add_back := l_number_of_days_to_add_back - l_overlap_band2_days;
2930 END IF;
2931 END IF;
2932
2933 IF l_number_of_days_to_add_back > 0 THEN
2934 IF l_number_of_days_to_add_back <= l_overlap_band1_days THEN
2935 p_ov_band1_days := p_ov_band1_days + l_number_of_days_to_add_back;
2936 l_number_of_days_to_add_back := l_number_of_days_to_add_back - l_overlap_band1_days;
2937 ELSE
2938 p_ov_band1_days := p_ov_band1_days - l_overlap_band1_days;
2939 l_number_of_days_to_add_back := l_number_of_days_to_add_back - l_overlap_band1_days;
2940 END IF;
2941 END IF;
2942
2943
2944 hr_utility.set_location(' Leaving:'||l_proc, 70);
2945
2946 Exception
2947 When no_data_found THEN
2948 hr_utility.trace (' band_overlaps - no data found');
2949
2950 when others then
2951 hr_utility.trace(SQLCODE);
2952 hr_utility.trace(SQLERRM);
2953 Raise;
2954
2955
2956 END Band_Overlaps;
2957
2958 -----------------------------------------------------------------------
2959 -- GET_GI_BANDS_AUDIT
2960 -- fetch results(bands) from element FR_SICKNESS_GI_INFO for a particular
2961 -- guarantee over the Rolling/Calendar Year
2962 -- Uses Band_overlaps from above
2963 -----------------------------------------------------------------------
2964 PROCEDURE Get_GI_Bands_Audit
2965 (p_GI_id IN Number,
2966 p_asg IN pay_fr_sick_pay_processing.t_asg,
2967 p_date_to IN Date,
2968 p_band_expiry_duration IN Varchar2,
2969 p_band1_days OUT NOCOPY Number,
2970 p_band2_days OUT NOCOPY Number,
2971 p_band3_days OUT NOCOPY Number,
2972 p_band4_days OUT NOCOPY Number) IS
2973
2974 cursor csr_get_band_usage(p_gi_id_chr varchar2,
2975 p_date_from_chr varchar2,
2976 p_date_to_chr varchar2) is
2977 -- No need to explicitly convert these fetched result values to number as
2978 -- they are integer values
2979 select /*+ ORDERED */
2980 nvl(sum(decode(target.input_value_id,
2981 g_gi_info_band1_iv_id,target.result_value,
2982 g_gi_i_r_band1_iv_id, target.result_value,0)),0)
2983 ,nvl(sum(decode(target.input_value_id,
2984 g_gi_info_band2_iv_id,target.result_value,
2985 g_gi_i_r_band2_iv_id, target.result_value,0)),0)
2986 ,nvl(sum(decode(target.input_value_id,
2987 g_gi_info_band3_iv_id,target.result_value,
2988 g_gi_i_r_band3_iv_id, target.result_value,0)),0)
2989 ,nvl(sum(decode(target.input_value_id,
2990 g_gi_info_band4_iv_id,target.result_value,
2991 g_gi_i_r_band4_iv_id, target.result_value,0)),0)
2992 from pay_assignment_actions assact
2993 ,pay_payroll_actions pact
2994 ,pay_run_results rr
2995 ,pay_run_result_values guarantee_id
2996 ,pay_run_result_values payment_date
2997 ,pay_run_result_values target
2998 where assact.assignment_id = p_asg.assignment_id
2999 and assact.payroll_action_id = pact.payroll_action_id
3000 and pact.action_type in ('R', 'Q', 'V', 'B')
3001 and assact.assignment_action_id = rr.assignment_action_id
3002 and rr.element_type_id in (g_gi_info_element_type_id,
3003 g_gi_i_r_element_type_id)
3004 and rr.run_result_id = target.run_result_id
3005 and rr.status in ('P','PA')
3006 and target.result_value is not null
3007 and target.input_value_id in (g_gi_info_band1_iv_id,
3008 g_gi_info_band2_iv_id,
3009 g_gi_info_band3_iv_id,
3010 g_gi_info_band4_iv_id,
3011 g_gi_i_r_band1_iv_id,
3012 g_gi_i_r_band2_iv_id,
3013 g_gi_i_r_band3_iv_id,
3014 g_gi_i_r_band4_iv_id)
3015 and rr.run_result_id = guarantee_id.run_result_id
3016 and guarantee_id.input_value_id in (g_gi_info_guarantee_id_iv_id,
3017 g_gi_i_r_guarantee_id_iv_id)
3018 and guarantee_id.result_value = p_gi_id_chr
3019 and rr.run_result_id = payment_date.run_result_id
3020 and payment_date.input_value_id in (g_gi_info_end_date_iv_id,
3021 g_gi_i_r_end_date_iv_id)
3022 and payment_date.result_value between p_date_from_chr and p_date_to_chr;
3023 --
3024 l_date_from date;
3025 l_date_to date;
3026 l_total_band1_days number := 0;
3027 l_total_band2_days number := 0;
3028 l_total_band3_days number := 0;
3029 l_total_band4_days number := 0;
3030 l_id number;
3031 l_from_dt_id number;
3032
3033 l_proc varchar2(72) := g_package||'Get_GI_Bands_Audit';
3034
3035 --
3036
3037 BEGIN
3038
3039 hr_utility.set_location('Entering:'|| l_proc,10);
3040
3041 -- If p_band_expiry_duration = 'Y' (rolling year) then go back to the
3042 -- equivalent day last year + 1 day, the date to becomes the parent absence
3043 -- start date - 1 day
3044 -- Else
3045 -- set the l_date_from = the first day of the current year
3046 -- go back to the start of the current year. The date to is the same.
3047
3048 IF p_band_expiry_duration = 'RY' THEN
3049 l_date_from := add_months(g_absence_calc.parent_absence_start_date,-12); -- add_months(p_date_to,-12);
3050 l_date_to := p_date_to - 1;
3051 ELSE
3052 l_date_from := trunc(p_date_to,'YYYY');
3053 l_date_to := p_date_to - 1;
3054 END IF;
3055
3056
3057 if g_gi_info_element_type_id is null then
3058 set_global_ids(p_effective_date => l_date_to);
3059 end if;
3060 -- Now get the Band usage over the period
3061 open csr_get_band_usage(fnd_number.number_to_canonical(p_gi_id),
3062 fnd_date.date_to_canonical(l_date_from),
3063 fnd_date.date_to_canonical(l_date_to));
3064 fetch csr_get_band_usage into
3065 l_total_band1_days
3066 ,l_total_band2_days
3067 ,l_total_band3_days
3068 ,l_total_band4_days;
3069 close csr_get_band_usage;
3070
3071 -- Check for any overlap days if there has been any band usage over the year
3072 IF l_total_band1_days <> 0
3073 OR l_total_band2_days <> 0
3074 OR l_total_band3_days <> 0
3075 OR l_total_band4_days <> 0 THEN
3076 Band_Overlaps( p_ov_band1_days => l_total_band1_days,
3077 p_ov_band2_days => l_total_band2_days,
3078 p_ov_band3_days => l_total_band3_days,
3079 p_ov_band4_days => l_total_band4_days,
3080 p_ov_gi_id => p_gi_id,
3081 p_date_from => l_date_from,
3082 p_date_to => l_date_to,
3083 p_asg => p_asg );
3084 ELSE
3085 p_band1_days := 0;
3086 p_band2_days := 0;
3087 p_band3_days := 0;
3088 p_band4_days := 0;
3089 END IF;
3090
3091 -- assign out variables
3092 p_band1_days := l_total_band1_days;
3093 p_band2_days := l_total_band2_days;
3094 p_band3_days := l_total_band3_days;
3095 p_band4_days := l_total_band4_days;
3096
3097 hr_utility.set_location(' Leaving:'||l_proc, 70);
3098
3099 exception when no_data_found THEN -- return zeros as no band usage
3100 hr_utility.trace ('get_gi_bands_audit - no band usage');
3101 p_band1_days := 0;
3102 p_band2_days := 0;
3103 p_band3_days := 0;
3104 p_band4_days := 0;
3105
3106 when others then
3107 hr_utility.set_location('get_gi_bands audit',20);
3108 hr_utility.trace(SQLCODE);
3109 hr_utility.trace(SQLERRM);
3110 Raise;
3111
3112 END Get_GI_Bands_Audit;
3113 --
3114
3115 -- ----------------------------------------------------------------------
3116 -- Common internal function to use up bands and calculate payments for GI
3117 -- Output is broken down by band and written to l_bands pl/sql table
3118 --
3119 PROCEDURE Use_GI_Bands(p_band_name IN NUMBER,
3120 p_band_avail_days IN NUMBER,
3121 p_band_prcnt IN NUMBER,
3122 p_GI_ref_sal IN NUMBER,
3123 p_band_start_date IN OUT NOCOPY DATE,
3124 p_for_days IN OUT NOCOPY NUMBER) IS
3125 --
3126 l_proc varchar2(72) := g_package||'Use_GI_bands';
3127
3128 l_band_curr_days PLS_INTEGER;
3129 l_band_idx PLS_INTEGER := p_band_name;
3130 l_band_rate NUMBER;
3131 --
3132 BEGIN
3133 hr_utility.set_location(' Entering:'||l_proc, 10);
3134
3135 IF (p_for_days >= p_band_avail_days) THEN
3136 l_band_curr_days := p_band_avail_days ;
3137 p_for_days := p_for_days - l_band_curr_days;
3138 ELSE
3139 l_band_curr_days := p_for_days;
3140 p_for_days := p_for_days - l_band_curr_days; -- 0
3141 END IF;
3142
3143 l_band_rate := p_GI_ref_sal * (p_band_prcnt/100);
3144
3145 IF (nvl(l_band_rate,0) > 0) THEN
3146 -- Write to pl-sql table l_bands
3147 l_bands(l_band_idx).band_rate := l_band_rate;
3148 l_bands(l_band_idx).band_payment := l_band_rate * l_band_curr_days;
3149 l_bands(l_band_idx).band_from_dt := p_band_start_date;
3150 l_bands(l_band_idx).band_to_dt := p_band_start_date + (l_band_curr_days - 1);
3151 l_bands(l_band_idx).band_days := l_band_curr_days;
3152
3153 hr_utility.set_location(' Populating l_Bands for current GI for band:'||l_band_idx, 11) ;
3154 hr_utility.set_location(' l_Bands rate :'||l_bands(l_band_idx).band_rate, 11) ;
3155 hr_utility.set_location(' l_Bands days :'||l_bands(l_band_idx).band_days, 11) ;
3156 hr_utility.set_location(' l_Bands payment:'||l_bands(l_band_idx).band_payment, 11) ;
3157 hr_utility.set_location(' l_Bands from :'||l_bands(l_band_idx).band_from_dt, 11) ;
3158 hr_utility.set_location(' l_Bands to :'||l_bands(l_band_idx).band_to_dt, 11) ;
3159
3160
3161
3162 END IF;
3163 -- Absence date moved on for lower Band utilisations
3164 p_band_start_date := p_band_start_date + l_band_curr_days;
3165
3166 hr_utility.set_location(' Leaving:'||l_proc, 70);
3167 END Use_GI_bands;
3168 -- ----------------------------------------------------------------------
3169
3170 -- ----------------------------------------------------------------------
3171 -- Common internal function to get daily rate divisor for GI balances
3172 -- Use this function to incorporate 'Unpaid days' logic at a later time
3173 --
3174 FUNCTION Get_GI_ref_salary_divisor(
3175 p_start_date IN DATE,
3176 p_end_date IN DATE)
3177 RETURN NUMBER IS
3178
3179 l_proc varchar2(72) := g_package||'Get_GI_ref_sal_divisor';
3180 l_days NUMBER;
3181
3182 BEGIN
3183 hr_utility.set_location(' Entering:'||l_proc, 10);
3184 --
3185 IF p_start_date IS NOT NULL
3186 AND p_end_date IS NOT NULL
3187 AND (p_end_date >= p_start_date) THEN
3188 l_days := (p_end_date - p_start_date) + 1;
3189 ELSE
3190 l_days := 0;
3191 END IF;
3192 --
3193 hr_utility.set_location(' Leaving:'||l_proc, 70);
3194 RETURN l_days;
3195 END;
3196
3197 --------------********************--------------
3198 -- CALC_LEGAL_GI
3199 -- Calculate Legal Guaranteed Income
3200 PROCEDURE Calc_LEGAL_GI(
3201 p_asg IN pay_fr_sick_pay_processing.t_asg,
3202 p_coverages IN OUT NOCOPY pay_fr_sick_pay_processing.t_coverages,
3203 p_absence_arch IN OUT NOCOPY pay_fr_sick_pay_processing.t_absence_arch ) IS
3204
3205 --
3206 l_assgt_id NUMBER := p_asg.assignment_id;
3207 l_business_group_id NUMBER := p_asg.business_group_id;
3208 l_gi_start_date DATE := g_absence_calc.IJSS_payment_start_date;
3209 l_gi_end_date DATE := g_absence_calc.IJSS_payment_end_date;
3210 l_parent_absence_id NUMBER := g_absence_calc.parent_absence_id;
3211 l_work_incident_type VARCHAR2(30) := g_absence_calc.work_incident;
3212 l_abs_duration NUMBER := g_absence_calc.prior_linked_absence_duration;
3213 l_parent_absence_start_date DATE := g_absence_calc.parent_absence_start_date;
3214 l_svc_in_years NUMBER(9,4);
3215
3216
3217 l_legi_id Number :=-9999;
3218 l_legi_code PLS_Integer;
3219
3220 l_waiting_days PLS_Integer :=0;
3221 l_curr_waiting_days PLS_Integer :=0;
3222
3223 l_B1_days PLS_Integer :=0;
3224 l_B2_days PLS_Integer :=0;
3225 l_B1_rate NUMBER :=0;
3226 l_B2_rate NUMBER :=0;
3227 l_B1_prcnt NUMBER :=0;
3228 l_B2_prcnt NUMBER :=0;
3229
3230
3231 l_B1_used_days PLS_Integer :=0;
3232 l_B2_used_days PLS_Integer :=0;
3233 l_B1_avail_days PLS_Integer :=0;
3234 l_B2_avail_days PLS_Integer :=0;
3235 l_B3_used_days PLS_Integer :=0;
3236 l_B4_used_days PLS_Integer :=0;
3237
3238 l_for_days PLS_Integer :=0;
3239
3240 l_idx PLS_Integer;
3241
3242 l_GI_previous_net NUMBER;
3243 l_GI_previous_payment NUMBER;
3244 l_GI_previous_adjustment NUMBER;
3245 l_GI_previous_IJSS_gross NUMBER;
3246 l_GI_best_method VARCHAR2(1);
3247
3248 l_ref_sal_divisor NUMBER;
3249 l_LEGI_ref_sal NUMBER;
3250 l_LEGI_daily_ref_sal NUMBER;
3251
3252 l_IJSS_gross_from_date DATE;
3253 l_IJSS_gross_to_date DATE;
3254
3255
3256 l_proc varchar2(72) := g_package||'Calc_LEGAL_GI';
3257
3258 BEGIN
3259
3260 hr_utility.set_location('Entering:'|| l_proc,10);
3261 hr_utility.set_location('Entering:'|| l_proc || l_gi_start_date,10);
3262 hr_utility.set_location('Entering:'|| l_proc|| l_gi_end_date,10);
3263
3264 -- Get Service period(in years) of the person
3265 BEGIN
3266 SELECT TRUNC((MONTHS_BETWEEN(paa.date_start,
3267 decode(ps.adjusted_svc_date,NULL, ps.date_start, ps.adjusted_svc_date)
3268 )/12), 4)
3269 --paa.date_start
3270 INTO l_svc_in_years
3271 --l_parent_absence_start_date
3272 FROM per_absence_attendances paa,
3273 per_periods_of_service ps,
3274 per_all_assignments_f pas
3275 WHERE ps.person_id = pas.person_id
3276 AND pas.assignment_id = l_assgt_id
3277 AND paa.absence_attendance_id = l_parent_absence_id
3278 AND paa.person_id = pas.person_id
3279 AND l_parent_absence_start_date between pas.effective_start_date and pas.effective_end_date ;
3280
3281 EXCEPTION
3282 WHEN NO_DATA_FOUND THEN
3283 l_svc_in_years := 0;
3284 END;
3285
3286 -- Waiting days check
3287 l_waiting_days := hruserdt.get_table_value(l_business_group_id,
3288 'FR_LEGI_WAITING_DAYS',
3289 'DAYS_DELAY',
3290 l_work_incident_type,
3291 l_parent_absence_start_date);
3292
3293 hr_utility.set_location(' IN '|| l_proc ||' and previous duration '||l_abs_duration,20);
3294 hr_utility.set_location(' and waiting days='||l_waiting_days,20);
3295 -- Any waiting days still to be used
3296 l_curr_waiting_days := l_waiting_days - l_abs_duration;
3297 IF l_curr_waiting_days <= 0 THEN -- Absence duration has exhausted waiting days
3298 l_curr_waiting_days:= 0;
3299 ELSE
3300 -- Modify start_date for calculating LEGI
3301 l_gi_start_date := l_gi_start_date + l_curr_waiting_days;
3302 END IF;
3303
3304 -- Get Bands information from Range UDT
3305 l_B1_days := hruserdt.get_table_value(l_business_group_id,
3306 'FR_LEGI_RATE_BANDS',
3307 'DAYS_AT_HIGH_RATE',
3308 l_svc_in_years,
3309 l_parent_absence_start_date);
3310
3311 l_B2_days := hruserdt.get_table_value(l_business_group_id,
3312 'FR_LEGI_RATE_BANDS',
3313 'DAYS_AT_LOW_RATE',
3314 l_svc_in_years,
3315 l_parent_absence_start_date);
3316
3317 l_B1_prcnt := hruserdt.get_table_value(l_business_group_id,
3318 'FR_LEGI_RATE_BANDS',
3319 'HIGH_RATE(%)',
3320 l_svc_in_years,
3321 l_parent_absence_start_date);
3322
3323 l_B2_prcnt := hruserdt.get_table_value(l_business_group_id,
3324 'FR_LEGI_RATE_BANDS',
3325 'LOW_RATE(%)',
3326 l_svc_in_years,
3327 l_parent_absence_start_date);
3328
3329 --
3330 -- Get Bands used over the Rolling year
3331 --
3332 Get_GI_Bands_Audit
3333 (p_GI_id => l_legi_id,
3334 p_asg => p_asg,
3335 p_date_to => l_gi_start_date,
3336 p_band_expiry_duration => 'RY',
3337 p_band1_days => l_B1_used_days,
3338 p_band2_days => l_B2_used_days,
3339 p_band3_days => l_B3_used_days,
3340 p_band4_days => l_B4_used_days);
3341
3342 --
3343 -- Get Previous payments made for the absence under LEGI
3344 --
3345 Get_GI_Payments_Audit
3346 (p_GI_id => l_legi_id,
3347 p_asg => p_asg,
3348 p_parent_absence_id => l_parent_absence_id,
3349 p_Current_date => l_gi_start_date,
3350 p_GI_Previous_Net => l_GI_previous_net,
3351 p_GI_Previous_Payment => l_GI_previous_payment,
3352 p_GI_Previous_Adjustment => l_GI_previous_adjustment,
3353 p_GI_Previous_IJSS_Gross => l_GI_previous_IJSS_gross,
3354 p_GI_Best_Method => l_GI_best_method);
3355
3356 -- Get index for g_coverages table
3357 l_idx := nvl(p_coverages.LAST, 0) + 1;
3358
3359 -- Get numeric code for the Guarantee type from global table g_guarantee_lookup_types in the PAY_FR_SICK_PAY_PROCESSING package
3360 -- This should use a common function for both LEGI and CAGR
3361 -- For LEGI, the code returned would have a value of 20
3362 l_legi_code := PAY_FR_SICK_PAY_PROCESSING.Get_guarantee_id('LE');
3363
3364 -- Only if values are not null, should a row be populated in g_coverages for the current GI
3365 IF nvl(l_GI_previous_net,0) > 0
3366 OR nvl(l_GI_previous_payment,0) > 0
3367 OR nvl(l_GI_previous_Adjustment,0) > 0
3368 OR nvl(l_GI_previous_IJSS_Gross,0) > 0
3369 OR l_GI_Best_method IS NOT NULL THEN
3370 p_coverages(l_idx).cagr_id := l_legi_id;
3371 p_coverages(l_idx).g_type := l_legi_code;
3372
3373 p_coverages(l_idx).previous_net := l_GI_previous_net;
3374 p_coverages(l_idx).previous_gi_payment := l_GI_previous_payment;
3375 p_coverages(l_idx).previous_sick_adj := l_GI_previous_adjustment;
3376 p_coverages(l_idx).previous_IJSS_Gross := l_GI_previous_IJSS_Gross;
3377 p_coverages(l_idx).best_method := l_GI_best_method;
3378
3379 END IF;
3380
3381 -- Calculate available days
3382 L_B1_avail_days := l_B1_days - l_B1_used_days;
3383 L_B2_avail_days := l_B2_days - l_B2_used_days;
3384
3385 -- Calculate Daily Ref Salary for LEGI
3386 -- A global held in Pay_fr_sick_pay_processing p_asg.lg_ref_salary holds the Balance value
3387 -- 'FR_SICKNESS_LEGAL_GUARANTEE_REFERENCE_SALARY_ASG_PRO_RUN'
3388 -- and proration dates p_asg.action_start_date and p_asg.action_end_date
3389 -- Raising an error if the reference salary is null
3390 l_LEGI_ref_sal := p_asg.lg_ref_salary;
3391
3392 IF l_LEGI_ref_sal IS NULL THEN
3393 fnd_message.set_name ('PAY', 'PAY_75039_LEGI_NULL_REF_SALARY');
3394 fnd_message.raise_error;
3395 END IF;
3396
3397 -- Added condition for CPAM processing
3398 IF g_absence_calc.initiator = 'CPAM' THEN
3399 l_ref_sal_divisor := Get_GI_ref_salary_divisor(
3400 p_start_date => g_absence_calc.abs_ptd_start_date,
3401 p_end_date => g_absence_calc.abs_ptd_end_date);
3402 ELSE
3403 l_ref_sal_divisor := Get_GI_ref_salary_divisor(
3404 p_start_date => p_asg.action_start_date,
3405 p_end_date => p_asg.action_end_date);
3406 END IF;
3407 --
3408
3409 l_LEGI_daily_ref_sal := l_LEGI_ref_sal/l_ref_sal_divisor;
3410
3411
3412 -- Calculate LEGI payments for the current absence days passed
3413 --
3414 l_for_days := l_gi_end_date - l_gi_start_date + 1;
3415
3416 -- LEGI has 2 Bands, use if available and if needed
3417 IF (l_B1_avail_days > 0) AND (l_for_days >0) THEN
3418 Use_GI_Bands(p_band_name => 1,
3419 p_band_avail_days => l_B1_avail_days,
3420 p_band_prcnt => l_B1_prcnt,
3421 p_GI_ref_sal => l_LEGI_daily_ref_sal,
3422 p_band_start_date => l_gi_start_date,
3423 p_for_days => l_for_days);
3424 END IF;
3425 IF (l_B2_avail_days > 0) AND (l_for_days >0) THEN
3426 Use_GI_Bands(p_band_name => 2,
3427 p_band_avail_days => l_B2_avail_days,
3428 p_band_prcnt => l_B2_prcnt,
3429 p_GI_ref_sal => l_LEGI_daily_ref_sal,
3430 p_band_start_date => l_gi_start_date,
3431 p_for_days => l_for_days);
3432 END IF;
3433
3434 -- Populating g_coverages with the band values from l_bands
3435 IF l_bands.COUNT > 0 THEN
3436 p_coverages(l_idx).cagr_id := l_legi_id;
3437 p_coverages(l_idx).g_type := l_legi_code;
3438
3439 FOR i IN l_bands.FIRST..l_bands.LAST -- i contains Band Numbers(index of l_bands)
3440 LOOP
3441 --l_first_band := l_bands.FIRST;
3442 IF l_IJSS_gross_from_date IS NULL THEN -- Only for 1st row in l_bands
3443 l_IJSS_gross_from_date := l_bands(i).band_from_dt;
3444 p_coverages(l_idx).gi_from_date1:= l_bands(i).band_from_dt;
3445 END IF;
3446
3447 l_IJSS_gross_to_date := l_bands(i).band_to_dt;
3448
3449 IF (i = l_bands.FIRST) THEN
3450 p_coverages(l_idx).gi_to_date1 := l_bands(i).band_to_dt;
3451 p_coverages(l_idx).gi_payment1 := l_bands(i).band_payment;
3452 p_coverages(l_idx).gi_rate1 := l_bands(i).band_rate;
3453 p_coverages(l_idx).gi_days1 := l_bands(i).band_days;
3454 ELSE
3455 p_coverages(l_idx).gi_from_date2:= l_bands(i).band_from_dt;
3456 p_coverages(l_idx).gi_to_date2 := l_bands(i).band_to_dt;
3457 p_coverages(l_idx).gi_payment2 := l_bands(i).band_payment;
3458 p_coverages(l_idx).gi_rate2 := l_bands(i).band_rate;
3459 p_coverages(l_idx).gi_days2 := l_bands(i).band_days;
3460 END IF;
3461
3462 IF i = 1 THEN
3463 p_coverages(l_idx).band1 := l_bands(i).band_days;
3464 ELSIF i = 2 THEN
3465 p_coverages(l_idx).band2 := l_bands(i).band_days;
3466 END IF;
3467
3468 END LOOP;
3469 END IF; -- l_bands.COUNT check
3470
3471 l_bands.DELETE; -- Clearing the l_bands table of the current GI rows
3472
3473 -- Populating IJSS_Gross values in G_coverages for LEGI overlap days
3474 IF (l_IJSS_gross_from_date IS NOT NULL)
3475 AND (l_IJSS_gross_to_date IS NOT NULL) THEN
3476 Calc_IJSS_Gross(p_IJSS_Gross_Start_Date => l_IJSS_gross_from_date
3477 ,p_IJSS_Gross_End_Date => l_IJSS_gross_to_date
3478 ,p_coverage_idx => l_idx
3479 ,p_coverages => p_coverages
3480 ,p_absence_arch => p_absence_arch);
3481 END IF;
3482
3483 hr_utility.set_location(' Leaving:'||l_proc, 70);
3484
3485 EXCEPTION
3486 WHEN OTHERS THEN
3487 hr_utility.set_location('calc_legal_GI',100);
3488 hr_utility.trace(SQLCODE);
3489 hr_utility.trace(SQLERRM);
3490 Raise;
3491
3492 END Calc_LEGAL_GI;
3493
3494
3495 -----------------------------------------------------------------------
3496 -- Function Get_CAGR_reference_salary
3497 -- function to return balance value
3498 ----------------------------------------------------------------------
3499 Function Get_CAGR_reference_salary (
3500 p_balance_name in varchar2,
3501 p_gi_type in varchar2,
3502 p_asg in pay_fr_sick_pay_processing.t_asg) return number
3503 IS
3504 Cursor csr_def_bal_id IS
3505 SELECT pdb.defined_balance_id
3506 FROM pay_balance_types_tl pbt,
3507 pay_balance_dimensions pbd,
3508 pay_defined_balances pdb
3509 WHERE pdb.balance_type_id = pbt.balance_type_id
3510 AND pdb.balance_dimension_id = pbd.balance_dimension_id
3511 AND pbt.balance_name = p_balance_name
3512 AND pbd.dimension_name = 'Assignment Proration Run To Date'
3513 AND (( pdb.legislation_code = 'FR') or
3514 (pdb.business_group_id = p_asg.business_group_id));
3515 --
3516 l_defined_balance_id number;
3517 l_value number;
3518 l_proc varchar2(72) := g_package||'get_ref_sal';
3519
3520 BEGIN
3521 -- for Net guarantee ref_sal is FR_SICKNESS_TARGET_NET_ASG_PRO_RUN
3522 -- this is fetched in FR_SICKNESS_CONTROL and passed to p_asg.base_net
3523 -- for Gross guarantee ref_sal is part of the terms of the guarantee
3524 -- recorded on element FR_SICK_BENEFIT.balance_name. The ASG_PRO_RUN
3525 -- dimension of this balance is calculated for the current action.
3526 -- a daily rate is found by deviding by the number of days in the run
3527 -- proration period ( or full period if not prorated ).
3528
3529 hr_utility.set_location('Entering:'|| l_proc,10);
3530
3531 if p_gi_type in ('CA_N','GN') then -- { net guarantee
3532 l_value := p_asg.base_net;
3533
3534 else -- gross guarantee
3535
3536 hr_utility.set_location(' Entering:'|| l_proc,20);
3537 begin
3538 open csr_def_bal_id;
3539 fetch csr_def_bal_id into l_defined_balance_id;
3540 if csr_def_bal_id%notfound THEN
3541 hr_utility.trace('Defined balance not found:' || p_balance_name||'_ASG_PRO_RUN');
3542 hr_utility.set_message (801, 'PAY_75042_DEF_BAL_MISSING');
3543 hr_utility.set_message_token('BALANCE_NAME',p_balance_name);
3544 hr_utility.raise_error;
3545 end if;
3546 close csr_def_bal_id;
3547 end;
3548 --
3549 hr_utility.set_location(' Entering:'|| l_proc,30);
3550 -- Added condition for CPAM processing
3551 IF g_absence_calc.initiator = 'CPAM' THEN
3552 l_value := pay_balance_pkg.get_value
3553 (l_defined_balance_id
3554 ,p_asg.assignment_id
3555 ,g_absence_calc.abs_ptd_end_date);
3556 ELSE
3557 l_value := pay_balance_pkg.get_value
3558 (l_defined_balance_id
3559 ,p_asg.assignment_action_id);
3560 END IF;
3561 --
3562 hr_utility.trace('cagr_balance_value:'||to_char(l_value));
3563
3564 end if; -- }
3565 -- balance is fr prorated period, divide by number of days in prorated period
3566 -- to get daily rate
3567 hr_utility.set_location(' starting:'||l_proc, 40);
3568
3569 -- Added condition for CPAM processing
3570 IF g_absence_calc.initiator = 'CPAM' THEN
3571 l_value := l_value / Get_GI_ref_salary_divisor(
3572 p_start_date =>g_absence_calc.abs_ptd_start_date,
3573 p_end_date =>g_absence_calc.abs_ptd_end_date);
3574 ELSE
3575 l_value := l_value / Get_GI_ref_salary_divisor(
3576 p_start_date => p_asg.action_start_date,
3577 p_end_date => p_asg.action_end_date);
3578 END IF;
3579 --
3580 hr_utility.set_location('Leaving:'||l_proc, 70);
3581
3582 return l_value;
3583
3584 END Get_CAGR_reference_salary;
3585 -----------------------------------------------------------------------
3586
3587 --------------********************--------------
3588 -- CALC_CAGR_GI
3589 -- Calculate Legal Guaranteed Income
3590
3591 PROCEDURE Calc_CAGR_GI
3592 (p_asg IN pay_fr_sick_pay_processing.t_asg
3593 ,p_coverages IN OUT NOCOPY pay_fr_sick_pay_processing.t_coverages
3594 ,p_absence_arch IN OUT NOCOPY pay_fr_sick_pay_processing.t_absence_arch) is
3595
3596 --
3597 l_proc varchar2(72) := g_package||'calc_cagr_gi';
3598
3599 l_assgt_id NUMBER := p_asg.assignment_id;
3600 l_business_group_id NUMBER := p_asg.business_group_id;
3601 l_gi_start_date DATE ; -- Dates set internally to ensure proper input for each GI
3602 l_gi_end_date DATE ;
3603 l_parent_absence_id NUMBER := g_absence_calc.parent_absence_id;
3604 l_abs_duration NUMBER := g_absence_calc.prior_linked_absence_duration;
3605 l_parent_absence_start_date DATE := g_absence_calc.parent_absence_start_date;
3606
3607 l_gi_id NUMBER;
3608 l_gi_code PLS_Integer;
3609
3610 l_curr_waiting_days PLS_Integer :=0;
3611
3612 l_B1_used_days PLS_Integer :=0;
3613 l_B2_used_days PLS_Integer :=0;
3614 l_B3_used_days PLS_Integer :=0;
3615 l_B4_used_days PLS_Integer :=0;
3616
3617 l_B1_avail_days PLS_Integer :=0;
3618 l_B2_avail_days PLS_Integer :=0;
3619 l_B3_avail_days PLS_Integer :=0;
3620 l_B4_avail_days PLS_Integer :=0;
3621
3622 l_for_days PLS_Integer :=0;
3623 l_present_days PLS_Integer :=0;
3624
3625 l_idx PLS_Integer;
3626 l_GI_previous_net NUMBER;
3627 l_GI_previous_payment NUMBER;
3628 l_GI_previous_adjustment NUMBER;
3629 l_GI_previous_IJSS_gross NUMBER;
3630 l_GI_best_method VARCHAR2(1);
3631
3632 l_CAGR_daily_ref_sal NUMBER;
3633
3634 l_IJSS_gross_from_date DATE;
3635 l_IJSS_gross_to_date DATE;
3636
3637 l_net NUMBER :=0;
3638 l_min_net NUMBER :=0;
3639
3640 cursor get_ben (p_assignment_id number,
3641 --p_absence_id number,
3642 --p_parent_absence_id number,
3643 p_effective_date date ) IS
3644 -- fetch FR_SICK_BENEFIT element entries that are relate to this absence as of the end
3645 -- date of the absence
3646 select ENT.element_entry_id
3647 ,fnd_number.canonical_to_number(G.screen_entry_value) guarantee_id
3648 ,max(decode(ENT_PT.input_value_id,g_ben_guarantee_type_iv_id,ENT_PT.screen_entry_value,null)) guarantee_type
3649 ,fnd_number.canonical_to_number(max(decode(ENT_PT.input_value_id,g_ben_waiting_days_iv_id,ENT_PT.screen_entry_value,null))) waiting_days
3650 ,max(decode(ENT_PT.input_value_id,g_ben_duration_iv_id,ENT_PT.screen_entry_value,null)) duration
3651 ,fnd_number.canonical_to_number(max(decode(ENT_PT.input_value_id,g_ben_band1_iv_id,ENT_PT.screen_entry_value,null))) band1
3652 ,fnd_number.canonical_to_number(max(decode(ENT_PT.input_value_id,g_ben_band1_rate_iv_id,ENT_PT.screen_entry_value,null))) b1_rate
3653 ,fnd_number.canonical_to_number(max(decode(ENT_PT.input_value_id,g_ben_band2_iv_id,ENT_PT.screen_entry_value,null))) band2
3654 ,fnd_number.canonical_to_number(max(decode(ENT_PT.input_value_id,g_ben_band2_rate_iv_id,ENT_PT.screen_entry_value,null))) b2_rate
3655 ,fnd_number.canonical_to_number(max(decode(ENT_PT.input_value_id,g_ben_band3_iv_id,ENT_PT.screen_entry_value,null))) band3
3656 ,fnd_number.canonical_to_number(max(decode(ENT_PT.input_value_id,g_ben_band3_rate_iv_id,ENT_PT.screen_entry_value,null))) b3_rate
3657 ,fnd_number.canonical_to_number(max(decode(ENT_PT.input_value_id,g_ben_band4_iv_id,ENT_PT.screen_entry_value,null))) band4
3658 ,fnd_number.canonical_to_number(max(decode(ENT_PT.input_value_id,g_ben_band4_rate_iv_id,ENT_PT.screen_entry_value,null))) b4_rate
3659 ,max(decode(ENT_PT.input_value_id,g_ben_balance_iv_id,ENT_PT.screen_entry_value,null)) balance
3660 from pay_element_entry_values_f ENT_PT
3661 ,pay_element_entry_values_f G
3662 ,pay_element_entries_f ENT
3663 ,pay_element_links_f EL
3664 where ENT_PT.element_entry_id = ENT.element_entry_id
3665 and G.element_entry_id = ENT.element_entry_id
3666 and G.input_value_id = g_ben_guarantee_id_iv_id
3667 and ENT.assignment_id = p_assignment_id
3668 and EL.element_type_id = g_ben_element_type_id
3669 and EL.element_link_id = ENT.element_link_id
3670 and p_effective_date between
3671 EL.effective_start_date and EL.effective_end_date
3672 and p_effective_date between
3673 ENT.effective_start_date and ENT.effective_end_date
3674 and ENT.effective_start_date = ENT_PT.effective_start_date
3675 and ENT.effective_end_date = ENT_PT.effective_end_date
3676 and ENT.effective_start_date = G.effective_start_date
3677 and ENT.effective_end_date = G.effective_end_date
3678 group by fnd_number.canonical_to_number(G.screen_entry_value), ENT.element_entry_id
3679 order by 2,1;
3680
3681 l_guarantee_id number;
3682 l_effective_end_date date;
3683
3684
3685 BEGIN
3686 hr_utility.set_location('Entering:'|| l_proc,10);
3687
3688 BEGIN
3689
3690 -- fetch ids of element_type and its inputs
3691 IF g_ben_element_type_id is null then
3692 hr_utility.set_location(' Entering:'|| l_proc,20);
3693 select max(e.element_type_id)
3694 ,max(decode(i.name,'Absence ID',i.input_value_id,null))
3695 ,max(decode(i.name,'Guarantee ID',i.input_value_id,null))
3696 ,max(decode(i.name,'Guarantee Type',i.input_value_id,null))
3697 ,max(decode(i.name,'Waiting Days',i.input_value_id,null))
3698 ,max(decode(i.name,'Band Expiry Duration',i.input_value_id,null))
3699 ,max(decode(i.name,'Band1',i.input_value_id,null))
3700 ,max(decode(i.name,'Band1 Rate',i.input_value_id,null))
3701 ,max(decode(i.name,'Band2',i.input_value_id,null))
3702 ,max(decode(i.name,'Band2 Rate',i.input_value_id,null))
3703 ,max(decode(i.name,'Band3',i.input_value_id,null))
3704 ,max(decode(i.name,'Band3 Rate',i.input_value_id,null))
3705 ,max(decode(i.name,'Band4',i.input_value_id,null))
3706 ,max(decode(i.name,'Band4 Rate',i.input_value_id,null))
3707 ,max(decode(i.name,'Balance Name',i.input_value_id,null))
3708 into g_ben_element_type_id
3709 ,g_ben_absence_id_iv_id
3710 ,g_ben_guarantee_id_iv_id
3711 ,g_ben_guarantee_type_iv_id
3712 ,g_ben_waiting_days_iv_id
3713 ,g_ben_duration_iv_id
3714 ,g_ben_band1_iv_id
3715 ,g_ben_band1_rate_iv_id
3716 ,g_ben_band2_iv_id
3717 ,g_ben_band2_rate_iv_id
3718 ,g_ben_band3_iv_id
3719 ,g_ben_band3_rate_iv_id
3720 ,g_ben_band4_iv_id
3721 ,g_ben_band4_rate_iv_id
3722 ,g_ben_balance_iv_id
3723 from pay_element_types_f e,
3724 pay_input_values_f i
3725 where e.element_name = 'FR_SICK_BENEFIT'
3726 and e.legislation_code = 'FR'
3727 and e.element_type_id = i.element_type_id
3728 and g_absence_calc.IJSS_payment_start_date between e.effective_start_date and e.effective_end_date
3729 and g_absence_calc.IJSS_payment_start_date between i.effective_start_date and i.effective_end_date;
3730 hr_utility.set_location(' Entering:'|| l_proc ||' g_ben_absence_id_iv_id '||g_ben_absence_id_iv_id,25);
3731 END IF;
3732 EXCEPTION
3733 WHEN OTHERS THEN
3734 hr_utility.set_location('Exception in '||l_proc,10);
3735 hr_utility.trace(SQLCODE);
3736 hr_utility.trace(SQLERRM);
3737 Raise;
3738
3739 END;
3740 -----------------------------------------------------------------------------
3741 -- fetch Sickness Benefit element entries that are for this absence
3742 -- for child absences 2 posibilities exist: all benefits can be recorded
3743 -- against that child absence for the duration of the child absence or
3744 -- all benefits can be recorded against the parent for the duration of
3745 -- all the linked absences.
3746
3747 BEGIN
3748
3749 hr_utility.set_location(' Entering:'|| l_proc,30);
3750 l_guarantee_id := 0;
3751 l_effective_end_date := g_absence_calc.IJSS_payment_end_date; --g_absence_calc.effective_end_date;
3752
3753 hr_utility.trace(' Ensuring parameters populated for getting CAGR terms');
3754 hr_utility.trace(' p_asg.assignment_id:'||to_char(p_asg.assignment_id));
3755 hr_utility.trace(' p_absence_arch.ID:'||to_char(p_absence_arch.ID));
3756 hr_utility.trace(' l_parent_absence_id:'||to_char(l_parent_absence_id));
3757 hr_utility.trace(' l_effective_end_date:'||to_char(l_effective_end_date));
3758
3759 -- Absence ids removed as not used within the cursor, bug #2647436
3760 FOR l_ben in get_ben( p_asg.assignment_id,
3761 l_effective_end_date)
3762 LOOP
3763 hr_utility.set_location(' Entering:'|| l_proc,35);
3764 -- check if this guarantee has already been processed if so do nothing
3765 -- (the element is defined a multiple entries allowed but there shouldn't
3766 -- be more than 1 entry for the same guarantee so ignore the duplicate )
3767
3768 IF l_guarantee_id <> l_ben.guarantee_id THEN -- {process
3769 l_guarantee_id := l_ben.guarantee_id;
3770
3771 hr_utility.set_location(' IN:'|| l_proc,40);
3772
3773
3774 -- Setting input dates for GI to be calculated for each GI
3775 l_gi_start_date := g_absence_calc.IJSS_payment_start_date;
3776 l_gi_end_date := g_absence_calc.IJSS_payment_end_date;
3777
3778 hr_utility.set_location(' Initial GI_START:'|| l_gi_start_date,40);
3779 hr_utility.set_location(' IN '|| l_proc ||' and previous duration='||l_abs_duration,40);
3780 hr_utility.set_location(' and waiting days='||l_ben.waiting_days,40);
3781 -- Check for waiting days
3782 l_curr_waiting_days := nvl(l_ben.waiting_days,0) - l_abs_duration;
3783 IF l_curr_waiting_days <= 0 THEN -- Absence duration has exhausted waiting days
3784 l_curr_waiting_days:= 0;
3785 ELSE
3786 -- Modify start_date for calculating GI
3787 l_gi_start_date := l_gi_start_date + l_curr_waiting_days;
3788 END IF;
3789
3790 hr_utility.set_location(' Modified GI_START:'|| l_gi_start_date,40);
3791 hr_utility.set_location(' GI_END :'|| l_gi_end_date,40);
3792 --
3793 -- Get Bands used over the Rolling year
3794 --
3795 Get_GI_Bands_Audit
3796 (p_GI_id => l_ben.guarantee_id,
3797 p_asg => p_asg,
3798 p_date_to => l_gi_start_date, -- Rolling year starts from actual GI date being processed
3799 p_band_expiry_duration => l_ben.duration,
3800 p_band1_days => l_B1_used_days,
3801 p_band2_days => l_B2_used_days,
3802 p_band3_days => l_B3_used_days,
3803 p_band4_days => l_B4_used_days);
3804
3805 --
3806 -- Get Previous payments made for the absence under LEGI
3807 --
3808 Get_GI_Payments_Audit
3809 (p_GI_id => l_ben.guarantee_id,
3810 p_asg => p_asg,
3811 p_parent_absence_id => l_parent_absence_id,
3812 p_Current_date => l_gi_start_date,
3813 p_GI_Previous_Net => l_GI_previous_net,
3814 p_GI_Previous_Payment => l_GI_previous_payment,
3815 p_GI_Previous_Adjustment => l_GI_previous_adjustment,
3816 p_GI_Previous_IJSS_Gross => l_GI_previous_IJSS_gross,
3817 p_GI_Best_Method => l_GI_best_method);
3818
3819 -- Get index for g_coverages table
3820 l_idx := nvl(p_coverages.LAST, 0) + 1;
3821
3822 hr_utility.set_location(' IN:'|| l_proc,45);
3823 --
3824 -- Get numeric code for the Guarantee type from global table g_guarantee_lookup_types
3825 -- in the PAY_FR_SICK_PAY_PROCESSING package.
3826 -- Using a common function for both LEGI and CAGR
3827
3828 l_gi_code := PAY_FR_SICK_PAY_PROCESSING.Get_guarantee_id(l_ben.guarantee_type);
3829
3830 -- Get Daily Reference Salary for guarantee
3831 l_CAGR_daily_ref_sal := Get_CAGR_reference_salary(
3832 p_balance_name => l_ben.balance,
3833 p_gi_type => l_ben.guarantee_type,
3834 p_asg => p_asg);
3835
3836 hr_utility.set_location(' IN:'|| l_proc,50);
3837
3838 -- Calculate GI payments for the current absence days passed
3839 --
3840 l_for_days := l_gi_end_date - l_gi_start_date + 1;
3841
3842 -- Setting Net for days the person was actually present
3843 IF g_absence_calc.initiator = 'CPAM' THEN
3844
3845 l_present_days := nvl(( (p_absence_arch.sick_deduct_start_date - g_absence_calc.abs_ptd_start_date) + (g_absence_calc.abs_ptd_end_date - p_absence_arch.sick_deduct_end_date )),0) ;
3846
3847 ELSE
3848 l_present_days := nvl(( (p_absence_arch.sick_deduct_start_date - p_asg.action_start_date )+ (p_asg.action_end_date - p_absence_arch.sick_deduct_end_date )),0) ;
3849 END IF;
3850
3851 l_net := (l_CAGR_daily_ref_sal * l_present_days) + nvl(p_asg.sick_net,0) - nvl(p_asg.base_net,0) ;
3852
3853 l_min_net := nvl(p_absence_arch.ijss_net,0)+ nvl(p_absence_arch.ijss_payment,0) + l_net;
3854 -- Only if values are not null, should a row be populated in g_coverages for the current GI
3855 IF nvl(l_GI_previous_net,0) > 0
3856 OR nvl(l_GI_previous_payment,0) > 0
3857 OR nvl(l_GI_previous_Adjustment,0) > 0
3858 OR nvl(l_GI_previous_IJSS_Gross,0) > 0
3859 OR l_GI_Best_method IS NOT NULL THEN
3860 p_coverages(l_idx).cagr_id := l_ben.guarantee_id;
3861 p_coverages(l_idx).g_type := l_gi_code;
3862
3863 p_coverages(l_idx).previous_net := l_GI_previous_net;
3864 p_coverages(l_idx).previous_gi_payment:= l_GI_previous_payment;
3865 p_coverages(l_idx).previous_sick_adj := l_GI_previous_adjustment;
3866 p_coverages(l_idx).previous_IJSS_Gross:= l_GI_previous_IJSS_Gross;
3867 p_coverages(l_idx).best_method := l_GI_best_method;
3868
3869 IF l_ben.guarantee_type IN ('GN','CA_N') THEN
3870 p_coverages(l_idx).net := nvl(l_min_net,0);
3871 END IF;
3872 END IF;
3873
3874
3875 -- Calculate available days
3876 l_B1_avail_days := l_ben.band1 - l_B1_used_days;
3877 l_B2_avail_days := l_ben.band2 - l_B2_used_days;
3878 l_B3_avail_days := l_ben.band3 - l_B3_used_days;
3879 l_B4_avail_days := l_ben.band4 - l_B4_used_days;
3880
3881 -- Get Daily Reference Salary for guarantee
3882 l_CAGR_daily_ref_sal := Get_CAGR_reference_salary(
3883 p_balance_name => l_ben.balance,
3884 p_gi_type => l_ben.guarantee_type,
3885 p_asg => p_asg);
3886
3887
3888 hr_utility.set_location(' IN:'|| l_proc,50);
3889
3890 hr_utility.set_location(' Ensuring parameters set to enter Use_GI_Bands', 55);
3891 hr_utility.set_location(' For_days '|| l_for_days, 55);
3892 hr_utility.set_location(' B1_avail_days '|| l_b1_avail_days, 55);
3893 hr_utility.set_location(' Ben cursor: B1_rate '|| l_ben.b1_rate,55);
3894 hr_utility.set_location(' B2_avail_days '|| l_b2_avail_days,55);
3895 hr_utility.set_location(' Ben cursor: B2_rate '|| l_ben.b2_rate, 55);
3896
3897 -- Use available bands for current absence
3898 -- Outputs are populated in table l_bands
3899 IF (l_B1_avail_days > 0) AND (l_for_days >0) THEN
3900 Use_GI_Bands(p_band_name => 1,
3901 p_band_avail_days => l_B1_avail_days,
3902 p_band_prcnt => l_ben.b1_rate,
3903 p_GI_ref_sal => l_CAGR_daily_ref_sal,
3904 p_band_start_date => l_gi_start_date,
3905 p_for_days => l_for_days);
3906 END IF;
3907
3908 IF (l_B2_avail_days > 0) AND (l_for_days >0) THEN
3909 Use_GI_Bands(p_band_name => 2,
3910 p_band_avail_days => l_B2_avail_days,
3911 p_band_prcnt => l_ben.b2_rate,
3912 p_GI_ref_sal => l_CAGR_daily_ref_sal,
3913 p_band_start_date => l_gi_start_date,
3914 p_for_days => l_for_days);
3915 END IF;
3916 IF (l_B3_avail_days > 0) AND (l_for_days >0) THEN
3917 Use_GI_Bands(p_band_name => 3,
3918 p_band_avail_days => l_B3_avail_days,
3919 p_band_prcnt => l_ben.b3_rate,
3920 p_GI_ref_sal => l_CAGR_daily_ref_sal,
3921 p_band_start_date => l_gi_start_date,
3922 p_for_days => l_for_days);
3923 END IF;
3924 IF (l_B4_avail_days > 0) AND (l_for_days >0) THEN
3925 Use_GI_Bands(p_band_name => 4,
3926 p_band_avail_days => l_B4_avail_days,
3927 p_band_prcnt => l_ben.b4_rate,
3928 p_GI_ref_sal => l_CAGR_daily_ref_sal,
3929 p_band_start_date => l_gi_start_date,
3930 p_for_days => l_for_days);
3931 END IF;
3932
3933
3934 -- Reading l_bands table to populate g_coverages
3935 -- Depending on the type of CAGR,certain columns of g_coverages need to be populated
3936 --
3937 -- Max of 2 rows will be populated in l_bands
3938 -- as there can only be 1 GI band completely used up in a period
3939 -- and hence only 1 crossover of bands
3940 --
3941 hr_utility.set_location(' l_Bands count '||l_bands.COUNT||' 1st: '||l_bands.FIRST||' last: '||l_bands.LAST, 52) ;
3942
3943 IF l_bands.COUNT > 0 THEN
3944 p_coverages(l_idx).cagr_id := l_ben.guarantee_id;
3945 p_coverages(l_idx).g_type := l_gi_code;
3946
3947 FOR i IN l_bands.FIRST..l_bands.LAST -- i contains Band Numbers(index of l_bands)
3948 LOOP
3949 hr_utility.set_location(' In l_Bands for band'||i, 55) ;
3950 hr_utility.set_location(' rate '||l_bands(i).band_rate, 55) ;
3951 hr_utility.set_location(' days '||l_bands(i).band_days, 55) ;
3952 hr_utility.set_location(' paymnt '||l_bands(i).band_payment, 55) ;
3953 hr_utility.set_location(' Existing l_net for GI '||l_net, 55) ;
3954
3955 --l_first_band := l_bands.FIRST;
3956 --IF l_IJSS_gross_from_date IS NULL THEN
3957 IF (i = l_bands.FIRST) THEN -- Only for 1st row in l_bands
3958 l_IJSS_gross_from_date := l_bands(i).band_from_dt;
3959 p_coverages(l_idx).gi_from_date1:= l_bands(i).band_from_dt;
3960 END IF;
3961
3962 hr_utility.set_location(' Gross from :'||l_bands(i).band_from_dt, 11) ;
3963 hr_utility.set_location(' Gross to :'||l_bands(i).band_to_dt, 11) ;
3964
3965 l_IJSS_gross_to_date := l_bands(i).band_to_dt;
3966
3967 IF (i = l_bands.FIRST) THEN
3968 p_coverages(l_idx).gi_to_date1 := l_bands(i).band_to_dt;
3969 ELSE
3970 p_coverages(l_idx).gi_from_date2:= l_bands(i).band_from_dt;
3971 p_coverages(l_idx).gi_to_date2 := l_bands(i).band_to_dt;
3972 END IF;
3973
3974 IF i = 1 THEN
3975 p_coverages(l_idx).band1 := l_bands(i).band_days;
3976 ELSIF i = 2 THEN
3977 p_coverages(l_idx).band2 := l_bands(i).band_days;
3978 ELSIF i = 3 THEN
3979 p_coverages(l_idx).band3 := l_bands(i).band_days;
3980 ELSIF i = 4 THEN
3981 p_coverages(l_idx).band4 := l_bands(i).band_days;
3982 END IF;
3983
3984 hr_utility.set_location(' Populating g_covg from each row in l_bands for CAGR id '||p_coverages(l_idx).cagr_id, 60);
3985 hr_utility.set_location(' GITYPE:'|| p_coverages(l_idx).g_type||' IDX:'|| l_idx ||' NET:'|| p_coverages(l_idx).net,60);
3986
3987 IF l_ben.guarantee_type IN ('GN','CA_N') THEN
3988 -- For net CAGRs, net(includes amt for present days) is summed across bands and populated
3989 p_coverages(l_idx).net := l_net + l_bands(i).band_payment;
3990 l_net := l_net + l_bands(i).band_payment;
3991
3992 ELSE
3993 -- For Gross CAGRs, payments are populated broken across bands
3994 IF (i = l_bands.FIRST) THEN
3995 p_coverages(l_idx).gi_payment1 := l_bands(i).band_payment;
3996 p_coverages(l_idx).gi_rate1 := l_bands(i).band_rate;
3997 p_coverages(l_idx).gi_days1 := l_bands(i).band_days;
3998 ELSE
3999 p_coverages(l_idx).gi_payment2 := l_bands(i).band_payment;
4000 p_coverages(l_idx).gi_rate2 := l_bands(i).band_rate;
4001 p_coverages(l_idx).gi_days2 := l_bands(i).band_days;
4002 END IF;
4003
4004 END IF; -- guarantee_type check
4005
4006 hr_utility.set_location(' GITYPE:'|| p_coverages(l_idx).g_type ||' IDX:'|| l_idx||' NET:'|| p_coverages(l_idx).net,60);
4007
4008 END LOOP;
4009
4010 END IF; -- l_bands.COUNT check
4011
4012 l_bands.DELETE; -- Clearing the l_bands table of the current CAGI
4013
4014 -- Calculate and populate IJSS Gross overlaps in g_coverages table for the current CAGR
4015 IF (l_IJSS_gross_from_date IS NOT NULL)
4016 AND (l_IJSS_gross_to_date IS NOT NULL) THEN
4017 Calc_IJSS_Gross(p_IJSS_Gross_Start_Date => l_IJSS_gross_from_date
4018 ,p_IJSS_Gross_End_Date => l_IJSS_gross_to_date
4019 ,p_coverage_idx => l_idx
4020 ,p_coverages => p_coverages
4021 ,p_absence_arch => p_absence_arch);
4022
4023 IF l_ben.guarantee_type IN ('GN','CA_N') THEN
4024 p_coverages(l_idx).net := nvl(p_coverages(l_idx).net,0) + nvl(p_coverages(l_idx).ijss_net_adjustment,0);
4025 hr_utility.set_location(' GITYPE:'|| p_coverages(l_idx).net,60);
4026 END IF;
4027
4028 END IF;
4029
4030
4031 END IF; -- }new guarantee row
4032
4033 END LOOP;
4034
4035 hr_utility.set_location('Leaving:'||l_proc, 90);
4036
4037 END;
4038
4039 EXCEPTION
4040 WHEN OTHERS THEN
4041 hr_utility.set_location('Exception in '||l_proc,100);
4042 hr_utility.trace(SQLCODE);
4043 hr_utility.trace(SQLERRM);
4044 Raise;
4045
4046 END CALC_CAGR_GI;
4047 --------------********************--------------
4048 -- Addded functions for CPAM processing
4049 --
4050 -- Function for the Skip formula function
4051 FUNCTION get_sickness_cpam_skip(
4052 p_business_group_id IN Number,
4053 P_Assignment_id IN Number,
4054 P_element_entry_id IN Number,
4055 P_date_earned IN Date,
4056 P_payment_start_date IN Date,
4057 P_payment_end_date IN Date,
4058 --P_subrogated IN Varchar2, Bug#2977789
4059 P_net_daily_rate IN Number,
4060 P_gross_daily_rate IN Number) RETURN Varchar2 IS
4061
4062 --
4063 -- Populate the g_absence_calc structure for the element being processed
4064 --
4065 cursor c_get_absence is
4066 select pabs.absence_attendance_id
4067 , to_number(pabs.abs_information1) parent_absence_id
4068 , pabs.date_start
4069 , pabs.date_end
4070 , pabs.date_end - pabs.date_start + 1 duration
4071 , null effective_start_date
4072 , null effective_end_date
4073 , nvl(abs_information2, 'Y') subrogated
4074 , nvl(pabs.abs_information8, 'N') estimated
4075 , nvl(pwi.inc_information1,'N') work_incident
4076 from per_absence_attendances pabs,
4077 per_all_assignments_f pasg,
4078 per_absence_attendance_types pabt,
4079 per_work_incidents pwi
4080 where pabs.business_group_id = p_business_group_id
4081 and pabs.abs_information_category ='FR_S'
4082 and pabs.date_start <= p_payment_start_date
4083 and pabs.date_end >= p_payment_end_date
4084 and decode(pabs.abs_information_category,'FR_S',to_number(pabs.abs_information6)) = pwi.incident_id(+)
4085 and pabs.person_id = pwi.person_id(+)
4086 and pabs.absence_Attendance_type_id = pabt.absence_attendance_type_id
4087 and pabt.absence_category = 'S'
4088 and pabs.date_start between pasg.effective_start_date and pasg.effective_end_date
4089 and pasg.primary_flag = 'Y'
4090 and pasg.person_id = pabs.person_id
4091 and pasg.assignment_id = p_assignment_id;
4092 --
4093 cursor c_get_parent_absence(p_absence_attendance_id number) is
4094 select paa.absence_attendance_id
4095 , 0 parent_absence_id
4096 , paa.date_start
4097 , paa.date_end
4098 , paa.date_end - paa.date_start + 1 duration
4099 , null effective_start_date
4100 , null effective_end_date
4101 , nvl(abs_information2, 'Y') subrogated
4102 , nvl(paa.abs_information8,'N') estimated
4103 , nvl(inc.inc_information1,'N') work_incident
4104 from per_absence_attendances paa
4105 , per_work_incidents inc
4106 where paa.absence_attendance_id = p_absence_attendance_id
4107 and paa.abs_information6 = to_char(inc.incident_id(+));
4108 --
4109 cursor c_get_child_absence(p_parent_absence_id number
4110 ,p_max_end_date date) is
4111 select a.absence_attendance_id
4112 , to_number(a.abs_information1) parent_absence_id
4113 , a.date_start
4114 , a.date_end
4115 , a.date_end - a.date_start + 1 duration
4116 , null effective_start_date
4117 , null effective_end_date
4118 , null estimated
4119 , null work_incident
4120 from per_absence_attendances a,
4121 per_absence_attendances p
4122 where a.abs_information1 = to_char(p_parent_absence_id)
4123 and p.absence_attendance_id = p_parent_absence_id
4124 and a.person_id = p.person_id
4125 and a.date_end <= p_max_end_date
4126 order by a.date_start;
4127 --
4128 TYPE t_absence is RECORD
4129 (absence_attendance_id number
4130 ,parent_absence_id number
4131 ,date_start date
4132 ,date_end date
4133 ,duration number
4134 ,effective_start_date date
4135 ,effective_end_date date
4136 ,subrogated varchar2(30)
4137 ,estimated varchar2(30)
4138 ,work_incident varchar2(30)
4139 );
4140 --
4141 abs_rec t_absence;
4142 parent_abs_rec t_absence;
4143 --
4144 l_duration number := 0;
4145 l_absence_start_date date;
4146 l_absence_end_date date;
4147 l_proc varchar2(72) := g_package||'Get_Sickness_cpam_skip';
4148
4149 --
4150 procedure check_gap(p_absence_end_date date
4151 ,p_absence_start_date date) is
4152 l_proc varchar2(72) := g_package||'check_gap';
4153
4154 begin
4155
4156
4157
4158 if p_absence_start_date - p_absence_end_date - 1 > 2
4159 then
4160 fnd_message.set_name ('PAY', 'PAY_75031_INVALID_LINK_ABS');
4161 fnd_message.raise_error;
4162 end if;
4163 end check_gap;
4164 --
4165 begin
4166 --
4167 -- Fetch absence detail as per the element entry
4168 --
4169 l_proc := g_package||'get_sickness_cpam_skip';
4170 open c_get_absence;
4171 fetch c_get_absence into abs_rec;
4172 close c_get_absence;
4173 -- If there was any part of the current absence
4174 -- that was processed in some other period
4175 -- then that contributes to the duration
4176
4177 IF (abs_rec.date_start >= p_payment_start_date) THEN
4178 l_duration := 0;
4179 ELSE
4180 l_duration := p_payment_start_date - abs_rec.date_start ;
4181 END IF;
4182
4183 if abs_rec.parent_absence_id is not null then
4184 --
4185 -- get the parent absence details
4186 --
4187 open c_get_parent_absence(abs_rec.parent_absence_id);
4188 fetch c_get_parent_absence into parent_abs_rec;
4189 close c_get_parent_absence;
4190 --
4191 l_duration := parent_abs_rec.duration;
4192 l_absence_end_date := parent_abs_rec.date_end;
4193
4194 --
4195 -- get the child absences up to but not including
4196 -- the orginiating absence
4197 --
4198 for a in c_get_child_absence(parent_abs_rec.absence_attendance_id,abs_rec.date_start - 1) loop
4199
4200 l_absence_start_date := a.date_start;
4201 -- check length of time between linked absences
4202 check_gap(l_absence_end_date,l_absence_start_date);
4203 --
4204 l_absence_end_date := a.date_end;
4205 l_duration := l_duration + a.duration;
4206 end loop;
4207 --
4208 -- check length of time between initiating absence
4209 -- and last absence processed
4210 check_gap(l_absence_end_date,abs_rec.date_start);
4211 else
4212 parent_abs_rec := abs_rec;
4213 end if;
4214 --
4215 g_absence_calc.element_entry_id := p_element_entry_id;
4216 g_absence_calc.date_earned := p_date_earned;
4217 g_absence_calc.id := abs_rec.absence_attendance_id;
4218 g_absence_calc.ijss_estimated := parent_abs_rec.estimated;
4219 g_absence_calc.parent_absence_id := parent_abs_rec.absence_attendance_id;
4220 g_absence_calc.parent_absence_start_date := parent_abs_rec.date_start;
4221 g_absence_calc.work_incident := parent_abs_rec.work_incident;
4222 g_absence_calc.prior_linked_absence_duration := l_duration;
4223 g_absence_calc.effective_start_date := p_payment_start_date;
4224 g_absence_calc.effective_end_date := p_payment_end_date;
4225 g_absence_calc.ijss_subrogated := abs_rec.subrogated;
4226 g_absence_calc.ijss_net_daily_rate := P_net_daily_rate;
4227 g_absence_calc.ijss_gross_daily_rate := P_gross_daily_rate;
4228 g_absence_calc.initiator := 'CPAM';
4229 --
4230 if g_absence_calc.ijss_estimated = 'Y' then
4231 hr_utility.set_location(' Leaving:'||l_proc, 70);
4232 return 'Y';
4233 else
4234 hr_utility.set_location(' Leaving:'||l_proc, 70);
4235 return 'N';
4236 end if;
4237 END get_sickness_cpam_skip;
4238 --
4239 -- Procedure for reference salary balances
4240 PROCEDURE Get_CPAM_Ref_salary(
4241 P_Business_group_id IN Number,
4242 P_Assignment_id IN Number,
4243 P_Absence_arch IN OUT NOCOPY pay_fr_sick_pay_processing.t_asg)
4244
4245 IS
4246 --
4247 cursor c_get_defined_balance(p_balance_name VARCHAR2) is
4248 select db.defined_balance_id
4249 from pay_defined_balances db
4250 , pay_balance_dimensions bd
4251 , pay_balance_types bt
4252 where db.balance_type_id = bt.balance_type_id
4253 and db.balance_dimension_id = bd.balance_dimension_id
4254 and bt.balance_name = p_balance_name
4255 and bt.legislation_code = 'FR'
4256 and bd.database_item_suffix = '_ASG_PTD'
4257 and bd.legislation_code = 'FR';
4258 --
4259 l_defined_balance_id number;
4260 l_proc varchar2(72) := g_package||'Get_cpam_Ref_salary';
4261
4262 BEGIN
4263 open c_get_defined_balance('FR_SICKNESS_DEDUCTION_REFERENCE_SALARY');
4264 fetch c_get_defined_balance into l_defined_balance_id;
4265 close c_get_defined_balance;
4266 --
4267 P_Absence_arch.ded_ref_salary := Nvl(pay_balance_pkg.get_value(
4268 P_DEFINED_BALANCE_ID => l_defined_balance_id
4269 ,P_ASSIGNMENT_ID => p_assignment_id
4270 ,P_VIRTUAL_DATE => g_absence_calc.abs_ptd_end_date),0);
4271 open c_get_defined_balance('FR_SICKNESS_LEGAL_GUARANTEE_REFERENCE_SALARY');
4272 fetch c_get_defined_balance into l_defined_balance_id;
4273 close c_get_defined_balance;
4274 --
4275 P_Absence_arch.lg_ref_salary := Nvl(pay_balance_pkg.get_value(
4276 P_DEFINED_BALANCE_ID => l_defined_balance_id
4277 ,P_ASSIGNMENT_ID => p_assignment_id
4278 ,P_VIRTUAL_DATE => g_absence_calc.abs_ptd_end_date),0);
4279 --
4280 EXCEPTION
4281 WHEN OTHERS THEN
4282 P_absence_arch.ded_ref_salary :=0;
4283 P_absence_arch.lg_ref_salary :=0;
4284 --
4285 END Get_CPAM_Ref_salary;
4286 --
4287 -- Procedure for getting IJSS value as entered in cpam
4288 --
4289 PROCEDURE get_sickness_CPAM_IJSS(
4290 p_business_group_id IN Number,
4291 p_assignment_id IN Number,
4292 p_absence_id IN Number,
4293 p_start_date IN Date,
4294 p_end_date IN Date,
4295 p_work_inc_class IN Varchar2) IS
4296 --
4297 l_total_process_days number;
4298 l_total_days number;
4299 --
4300 -- Cursor for finding holidays within the date range
4301 Cursor csr_holidays(c_hol_date varchar2) IS
4302 SELECT 'H'
4303 FROM per_absence_attendances pabs_hol,
4304 per_absence_attendance_types pabt,
4305 per_absence_attendances pabs_sick
4306 WHERE pabs_sick.absence_attendance_id = p_absence_id
4307 AND pabs_sick.business_group_id = p_business_group_id
4308 AND pabs_hol.person_id = pabs_sick.person_id
4309 AND pabs_hol.business_group_id = p_business_group_id
4310 AND c_hol_date BETWEEN pabs_hol.date_start AND pabs_hol.date_end
4311 AND pabt.absence_attendance_type_id = pabs_hol.absence_attendance_type_id
4312 AND pabt.absence_category in ('FR_MAIN_HOLIDAY','FR_ADDITIONAL_HOLIDAY','FR_RTT_HOLIDAY') ;
4313 --
4314 BEGIN
4315
4316 IF (g_overlap.COUNT <> 0) THEN
4317 g_overlap.DELETE;
4318 hr_utility.set_location(' Modified count '||g_overlap.COUNT,20);
4319 END IF;
4320
4321 -- find the number of rows of the global table
4322 l_total_process_days := p_end_date - p_start_date +1;
4323
4324 FOR l_total_days in 1..l_total_process_days LOOP
4325 -- Populate absence days
4326 IF l_total_days = 1 THEN
4327 g_overlap(1).absence_day := g_absence_calc.effective_start_date;
4328 ELSE
4329 g_overlap(l_total_days).absence_day := g_overlap(l_total_days -1).absence_day +1;
4330 END IF;
4331 -- Populate holidays
4332 OPEN csr_holidays(g_overlap(l_total_days).absence_day);
4333 FETCH csr_holidays INTO g_overlap(l_total_days).holiday;
4334 CLOSE csr_holidays;
4335 -- Populate IJSS values:
4336 -- Removed and modified code for bug 2751760
4337 g_overlap(l_total_days).IJSS_Gross := g_absence_calc.ijss_gross_daily_rate;
4338 g_overlap(l_total_days).IJSS_Net := g_absence_calc.ijss_net_daily_rate;
4339 --
4340 END LOOP;
4341 END get_sickness_cpam_ijss;
4342
4343 -----------------------------------------------------------------------------
4344 -- CONCATENATED_INPUTS
4345 -- returns a string that is a concatenation of the entry values for a given
4346 -- element entry. For use in reports when presenting the inputs for an
4347 -- entry on a single line of information.
4348 -----------------------------------------------------------------------------
4349
4350 FUNCTION concatenated_inputs(
4351 p_element_entry_id in number
4352 ,p_effective_date in DATE
4353 ,p_delimiter in varchar2 default '|'
4354 )
4355 RETURN varchar2 is
4356
4357 CURSOR ele_input(p_element_entry_id in NUMBER
4358 ,p_effective_date in DATE) is
4359 select name,
4360 uom,
4361 substr(screen_entry_value,1,10) screen_entry_value
4362 from pay_element_entry_values_f ev,
4363 pay_input_values_f i
4364 where ev.element_entry_id = p_element_entry_id
4365 and ev.input_value_id + 0 = i.input_value_id
4366 and p_effective_date between
4367 ev.effective_start_date and ev.effective_end_date
4368 and p_effective_date between
4369 i.effective_start_date and i.effective_end_date
4370 order by display_sequence;
4371
4372
4373 l_length_value NUMBER;
4374 l_concatenated_input varchar2(240);
4375 l_name pay_input_values_f.name%TYPE;
4376
4377
4378 BEGIN
4379
4380 l_concatenated_input := p_delimiter ;
4381 for l_count in ele_input(p_element_entry_id, p_effective_date) loop
4382
4383 l_concatenated_input := substr(l_concatenated_input ||
4384 ' '||
4385 l_count.screen_entry_value ||
4386 ' '||p_delimiter , 1,240);
4387 end loop;
4388 return l_concatenated_input;
4389 end concatenated_inputs;
4390 -----------------------------------------------------------------------------
4391 FUNCTION concatenated_result_values(
4392 p_run_result_id in number
4393 ,p_delimiter in varchar2 default '|'
4394 )
4395 RETURN varchar2 is
4396
4397 CURSOR ele_input_res(p_run_result_id in NUMBER) is
4398 select name,
4399 uom,
4400 substr(result_value,1,10) result_value
4401 from pay_run_result_values rv,
4402 pay_input_values_f i
4403 where rv.run_result_id = p_run_result_id
4404 and rv.input_value_id + 0 = i.input_value_id
4405 order by display_sequence;
4406
4407
4408 l_length_value NUMBER;
4409 l_concatenated_input varchar2(240);
4410 l_name pay_input_values_f.name%TYPE;
4411
4412
4413 BEGIN
4414
4415 l_concatenated_input := p_delimiter ;
4416 for l_count in ele_input_res(p_run_result_id) loop
4417
4418 l_concatenated_input := substr(l_concatenated_input ||
4419 ' '||
4420 l_count.result_value ||
4421 ' '||p_delimiter , 1,240);
4422 end loop;
4423 return l_concatenated_input;
4424 end concatenated_result_values;
4425
4426 -----------------------------------------------------------------------------
4427 -- CONCATENATED_INPUT_NAMES
4428 -- returns a string that is a concatenation of the input names for a given
4429 -- element type. For use in reports when providing a key to interpret
4430 -- concatenated_inputs string.
4431 -----------------------------------------------------------------------------
4432 FUNCTION concatenated_input_names(
4433 p_element_type_id in number
4434 ,p_effective_date in DATE
4435 ,p_delimiter in varchar2 default '|'
4436 )
4437 RETURN varchar2 is
4438
4439 CURSOR ele_input_name(p_element_type_id in NUMBER
4440 ,p_effective_date in DATE) is
4441 select name,
4442 uom
4443 from
4444 pay_input_values_f i
4445 where i.element_type_id = p_element_type_id
4446 and p_effective_date between
4447 i.effective_start_date and i.effective_end_date
4448 order by display_sequence;
4449
4450
4451 l_length_value NUMBER;
4452 l_concatenated_input_name varchar2(240);
4453 l_name pay_input_values_f.name%TYPE;
4454
4455
4456 BEGIN
4457
4458 l_concatenated_input_name := p_delimiter ;
4459 for l_count in ele_input_name(p_element_type_id, p_effective_date) loop
4460
4461 l_concatenated_input_name := substr(l_concatenated_input_name ||
4462 ' '||
4463 l_count.name ||
4464 ' '||p_delimiter , 1,240);
4465 end loop;
4466 return l_concatenated_input_name;
4467 end concatenated_input_names;
4468 --
4469 END PAY_FR_SICKNESS_CALC;
[Home] [Help]