1 PACKAGE BODY pay_nl_si_pkg AS
2 /* $Header: pynlsoci.pkb 120.27.12020000.3 2013/03/25 13:35:03 sgmaram ship $ */
3 g_package varchar2(33) := ' pay_nl_si_pkg.';
4 g_legislation_code varchar2(2) :='NL';
5 g_udt_name VARCHAR2(50) := 'PQP_COMPANY_WORK_PATTERNS';
6 g_ptp_formula_exists BOOLEAN := TRUE;
7 g_ptp_formula_cached BOOLEAN := FALSE;
8 g_ptp_formula_id ff_formulas_f.formula_id%TYPE;
9 g_ptp_formula_name ff_formulas_f.formula_name%TYPE;
10 /* Global Variables for Standard SI Part Time Percentage */
11 g_std_ptp_formula_exists BOOLEAN := TRUE;
12 g_std_ptp_formula_cached BOOLEAN := FALSE;
13 g_std_ptp_formula_id ff_formulas_f.formula_id%TYPE;
14 g_std_ptp_formula_name ff_formulas_f.formula_name%TYPE;
15 /* Global Variables for Pseudo SI Part Time Percentage */
16 g_pse_ptp_formula_exists BOOLEAN := TRUE;
17 g_pse_ptp_formula_cached BOOLEAN := FALSE;
18 g_pse_ptp_formula_id ff_formulas_f.formula_id%TYPE;
19 g_pse_ptp_formula_name ff_formulas_f.formula_name%TYPE;
20 /* Global Variables for Standard SI Reporting Part Time Percentage */
21 g_std_rep_ptp_formula_exists BOOLEAN := TRUE;
22 g_std_rep_ptp_formula_cached BOOLEAN := FALSE;
23 g_std_rep_ptp_formula_id ff_formulas_f.formula_id%TYPE;
24 g_std_rep_ptp_formula_name ff_formulas_f.formula_name%TYPE;
25 /* Global variables for Override Real SI Days */
26 g_real_si_days_formula_exists BOOLEAN := TRUE;
27 g_real_si_days_formula_cached BOOLEAN := FALSE;
28 g_real_si_days_formula_id ff_formulas_f.formula_id%TYPE;
29 g_real_si_days_formula_name ff_formulas_f.formula_name%TYPE;
30 /* Global Variables for Pseudo SI Reporting Part Time Percentage */
31 g_pse_rep_ptp_formula_exists BOOLEAN := TRUE;
32 g_pse_rep_ptp_formula_cached BOOLEAN := FALSE;
33 g_pse_rep_ptp_formula_id ff_formulas_f.formula_id%TYPE;
34 g_pse_rep_ptp_formula_name ff_formulas_f.formula_name%TYPE;
35 /* Global Variables for Override SI Days */
36 g_si_days_formula_exists BOOLEAN := TRUE;
37 g_si_days_formula_cached BOOLEAN := FALSE;
38 g_si_days_formula_id ff_formulas_f.formula_id%TYPE;
39 g_si_days_formula_name ff_formulas_f.formula_name%TYPE;
40 g_si_days NUMBER;
41 /* Global Variables for Override SI Days ZVW */
42 g_si_days_zvw NUMBER;
43 /* Global Variables for Tax Proration Days */
44 g_assignment_id per_all_assignments_f.assignment_id%TYPE;
45 g_tax_proration_days NUMBER;
46 g_tax_proration_flag VARCHAR2(1);
47
48 cursor c_wp_dets(p_assignment_id NUMBER ) is
49 select *
50 from pqp_assignment_attributes_f paa,
51 fnd_sessions ses
52 where assignment_id = p_assignment_id
53 and ses.session_id = userenv('sessionid')
54 and ses.effective_date between paa.effective_start_date and paa.effective_end_date;
55 --
56 -- Returns the SI Status
57 -- are entered for a employee
58 Function get_si_status
59 ( p_assignment_id in number,
60 p_date_earned in date,
61 p_si_class in varchar2
62 ) return number IS
63 CURSOR Cur_SI_Status IS
64 SELECT paei.AEI_INFORMATION4 SI_Status,
65 paei.AEI_INFORMATION3 SI_Class,
66 DECODE(paei.AEI_INFORMATION3,'AMI',0,1) si_class_order
67 FROM
68 per_assignment_extra_info paei
69 WHERE
70 assignment_id = p_assignment_id
71 and paei.aei_information_category='NL_SII'
72 and (paei.AEI_INFORMATION3 = p_si_class or
73 paei.AEI_INFORMATION3 = DECODE(p_si_class,'ZFW','AMI','ZW','AMI','WW','AMI','WAO','AMI',
74 'ZVW','AMI','ZVWL','AMI','ZVWS','AMI','WGA','AMI','IVA','AMI','UFO','AMI',p_si_class))
75 and p_date_earned between
76 FND_DATE.CANONICAL_TO_DATE(paei.AEI_INFORMATION1)
77 and nvl(FND_DATE.CANONICAL_TO_DATE(paei.AEI_INFORMATION2),hr_general.END_OF_TIME)
78 order by si_class_order desc;
79
80 CURSOR Cur_ZVW_Excluded(p_dt_earned NUMBER)
81 IS
82 SELECT nvl(paei.AEI_INFORMATION2,'N') ZVW_Excluded
83 FROM
84 per_assignment_extra_info paei
85 WHERE assignment_id = p_assignment_id
86 and paei.aei_information_category='NL_EXCL_ZVW'
87 and (to_char(fnd_date.canonical_to_date(paei.AEI_INFORMATION1),'RRRR') <= p_dt_earned
88 AND nvl(to_char(fnd_date.canonical_to_date(paei.AEI_INFORMATION3),'RRRR'),'4712') >= p_dt_earned);
89
90
91 vCur_SI_Status Cur_SI_Status%ROWTYPE;
92 vCur_ZVW_Excluded Cur_ZVW_Excluded%ROWTYPE;
93 l_zvw_status NUMBER;
94 l_zvwl_status NUMBER;
95 l_zvws_status NUMBER;
96 l_zvw_excluded VARCHAR2(1);
97 /*l_exclusion_date VARCHAR2(4);*/
98 l_date_earned VARCHAR2(8);
99 l_proc varchar2(100) := g_package||'get_si_status';
100 BEGIN
101 hr_utility.set_location('Entering ' || l_proc, 100);
102 hr_utility.set_location('p_assignment_id ' || p_assignment_id, 110);
103 hr_utility.set_location('p_si_class ' || p_si_class, 110);
104 hr_utility.set_location('p_date_earned ' || p_date_earned, 110);
105 l_zvw_status := 1;
106 l_zvwl_status := 1;
107 l_zvws_status := 1;
108 OPEN Cur_SI_Status;
109 FETCH Cur_SI_Status INTO vCur_SI_Status;
110 IF Cur_SI_Status%NOTFOUND THEN
111 l_zvw_status := 0;
112 l_zvwl_status := 0;
113 l_zvws_status := 0;
114 END IF;
115 CLOSE Cur_SI_Status;
116 -- If the
117
118 IF p_si_class = 'ZVW' THEN
119 l_date_earned := TO_CHAR(p_date_earned,'RRRR');
120 OPEN Cur_ZVW_Excluded(l_date_earned);
121 FETCH Cur_ZVW_Excluded INTO vCur_ZVW_Excluded;
122 IF(Cur_ZVW_Excluded%NOTFOUND) THEN
123 l_zvw_excluded := 'N';
124 /* l_exclusion_date := l_date_earned;*/
125 ELSE
126 l_zvw_excluded := vCur_ZVW_Excluded.ZVW_Excluded;
127 /* l_exclusion_date := vCur_ZVW_Excluded.Excluded_Year;*/
128 END IF;
129 CLOSE Cur_ZVW_Excluded;
130
131 IF l_zvw_excluded = 'N' THEN
132 IF l_zvw_status = 0 THEN
133 RETURN -3;
134 END IF;
135 ELSE
136 RETURN -5;
137 END IF;
138 END IF;
139
140 IF p_si_class = 'ZVWL' AND p_date_earned > to_date('31/12/2012', 'dd/mm/yyyy') AND l_zvwl_status = 0 THEN
141 vCur_SI_Status.SI_Status := -6;
142 END IF;
143
144 IF p_si_class = 'ZVWS' AND p_date_earned > to_date('31/12/2012', 'dd/mm/yyyy') AND l_zvws_status = 0 THEN
145 vCur_SI_Status.SI_Status := -6;
146 END IF;
147
148 IF (vCur_SI_Status.SI_Class='AMI' and p_si_class <>'ZFW') and
149 vCur_SI_Status.SI_Status='4' THEN
150 vCur_SI_Status.SI_Status:=1;
151 END IF;
152 IF vCur_SI_Status.SI_Status='4' and p_date_earned > to_date('31/12/2005', 'dd/mm/yyyy') THEN
153 vCur_SI_Status.SI_Status:=-4;
154 END IF;
155 IF vCur_SI_Status.SI_Status>0 and p_date_earned > to_date('31/12/2005', 'dd/mm/yyyy') and p_si_class ='ZFW' THEN
156 IF vCur_SI_Status.SI_Class='AMI' THEN
157 vCur_SI_Status.SI_Status:=0;
158 ELSE
159 vCur_SI_Status.SI_Status:=-1;
160 END IF;
161 END IF;
162 IF nvl(vCur_SI_Status.SI_Status, 0)<=0 and p_date_earned > to_date('31/12/2005', 'dd/mm/yyyy') and p_si_class ='ZVW' THEN
163 vCur_SI_Status.SI_Status:=-2;
164 END IF;
165
166 /* IF vCur_SI_Status.SI_Class = 'AMI' and vCur_SI_Status.SI_Status > 0 THEN
167 IF hr_nl_org_info.Get_SI_Provider_Excl_Info(p_assignment_id,p_si_class,p_date_earned) = 0 THEN
168 vCur_SI_Status.SI_Status := 0;
169 END IF;
170 END IF;*/
171
172 hr_utility.set_location('Leaving' || l_proc, 500);
173 RETURN vCur_SI_Status.SI_Status;
174 EXCEPTION
175 WHEN OTHERS THEN
176 hr_utility.set_location(' Others :' || SQLERRM(SQLCODE),900);
177 END get_si_status;
178 --
179 -- Function to check if All Mandatory Insurances is chosen at the assignment level
180 --
181 Function is_ami
182 ( p_assignment_id in number,
183 p_date_earned in date
184 ) return number IS
185 CURSOR Cur_SI_AMI IS
186 SELECT
187 DECODE(paei.AEI_INFORMATION3,'AMI',1,0) si_class_order
188 FROM
189 per_assignment_extra_info paei
190 WHERE
191 assignment_id = p_assignment_id
192 and paei.aei_information_category='NL_SII'
193 and p_date_earned between
194 FND_DATE.CANONICAL_TO_DATE(paei.AEI_INFORMATION1)
195 and nvl(FND_DATE.CANONICAL_TO_DATE(paei.AEI_INFORMATION2),hr_general.END_OF_TIME)
196 order by si_class_order desc;
197 vCur_SI_AMI Cur_SI_AMI%ROWTYPE;
198 l_proc varchar2(100) := g_package||'get_si_status';
199 BEGIN
200 hr_utility.set_location('Entering ' || l_proc, 100);
201 hr_utility.set_location('p_assignment_id ' || p_assignment_id, 110);
202 hr_utility.set_location('p_date_earned ' || p_date_earned, 110);
203 OPEN Cur_SI_AMI;
204 FETCH Cur_SI_AMI INTO vCur_SI_AMI;
205 CLOSE Cur_SI_AMI;
206 RETURN NVL(vCur_SI_AMI.si_class_order,0);
207 EXCEPTION
208 WHEN OTHERS THEN
209 hr_utility.set_location(' Others :' || SQLERRM(SQLCODE),900);
210 RETURN 0;
211 END is_ami;
212 --
213 -- Function to get payroll type
214 --
215 Function get_payroll_type
216 (p_payroll_id in number
217 ) return varchar2 is
218 --
219 cursor cur_get_payroll is
220 select period_type
221 from pay_all_payrolls_f pap
222 where payroll_id = p_payroll_id;
223 v_period_type varchar2(30);
224 --
225 begin
226 open cur_get_payroll;
227 fetch cur_get_payroll into v_period_type;
228 close cur_get_payroll;
229 return (v_period_type);
230 end get_payroll_type;
231 --
232 -- Determines the Number if Week Days(Monday to Friday )
233 -- between two dates
234 FUNCTION Get_Week_Days(P_Start_Date Date,
235 P_End_Date Date) return NUMBER IS
236 v_st_date date :=P_Start_Date;
237 v_en_date date :=P_End_Date;
238 v_beg_of_week date;
239 v_end_of_week date;
240 v_days number := 0;
241 begin
242 if P_Start_Date > P_end_Date then
243 return v_days;
244 end if;
245 --Determine the Beginning of Week Date for Start Date
246 --and End of Week Date for End Date
247 v_beg_of_week := v_st_date - (get_day_of_week(v_st_date)-1);
248 v_end_of_week := v_en_date;
249 if get_day_of_week(v_en_date) NOT IN('1') then
250 v_end_of_week := v_en_date + (7- get_day_of_week(v_en_date)+1);
251 end if;
252 --Calculate the Total Week Days @ of 5 per week
253 v_days := ((v_end_of_week-v_beg_of_week)/7)*5;
254 --Adjust the Total Week Days by subtracting
255 --No of Days before the Start Date
256 if (v_st_date > (v_beg_of_week+1)) then
257 v_days := v_days - (v_st_date - (v_beg_of_week+1)) ;
258 end if;
259 if v_end_of_week <> v_en_date then
260 v_end_of_week := v_end_of_week -2;
261 else
262 if v_st_date = v_en_date then
263 v_days := 0;
264 end if;
265 end if;
266 --Adjust the Total Week Days by subtracting
267 --No of Days After the End Date
268 if (v_end_of_week - v_en_date) >= 0 then
269 v_days := v_days - (v_end_of_week - v_en_date) ;
270 end if;
271 return (v_days);
272 end Get_Week_Days;
273 --
274 -- Determines the Maximum SI Days between two dates
275 -- based on the method of 5 days per week
276 FUNCTION Get_Max_SI_Days(P_Assignment_Id Number,
277 P_Start_Date Date,
278 P_End_Date Date) return NUMBER IS
279 l_assgn_attr c_wp_dets%rowtype;
280 l_curr_date DATE;
281 l_temp1 DATE;
282 l_temp2 DATE;
283 l_max_si_days NUMBER:=0;
284 l_error_code NUMBER;
285 l_counter NUMBER:=0;
286 l_error_msg fnd_new_messages.message_text%TYPE;
287 l_is_wrking_day VARCHAR2(1);
288 BEGIN
289 OPEN c_wp_dets(P_Assignment_Id);
290 FETCH c_wp_dets INTO l_assgn_attr;
291 CLOSE c_wp_dets;
292 l_temp1 :=p_start_date + (7 - get_day_of_week(p_start_date))+1;
293 l_temp2 :=p_end_date - to_number(get_day_of_week(p_end_date))+1;
294 IF p_end_date < l_temp1 THEN
295 l_temp1 := p_end_date;
296 end if;
297 l_curr_date:=p_start_date;
298 l_counter:=0;
299 while l_curr_date <= l_temp1
300 loop
301 l_is_wrking_day:=PQP_SCHEDULE_CALCULATION_PKG.is_working_day
302 (p_assignment_id => l_assgn_attr.assignment_id
303 ,p_business_group_id => l_assgn_attr. business_group_id
304 ,p_date => l_curr_date
305 ,p_error_code => l_error_code
306 ,p_error_message => l_error_msg
307 ,p_default_wp => null
308 );
309 if (l_is_wrking_day ='Y' and l_counter < 5) then
310 l_counter:=l_counter+1;
311 end if;
312 l_curr_date:=l_curr_date+1;
313 end loop;
314 --3082046
315 l_max_si_days:=l_max_si_days + l_counter;
316 l_counter := 0;
317 IF p_end_date - l_temp1 + 1 >= 7 THEN
318 l_curr_date := l_temp1 +1;
319 while l_curr_date <= l_temp2
320 loop
321 l_is_wrking_day:= PQP_SCHEDULE_CALCULATION_PKG.is_working_day
322 (p_assignment_id => l_assgn_attr.assignment_id
323 ,p_business_group_id => l_assgn_attr. business_group_id
324 ,p_date => l_curr_date
325 ,p_error_code => l_error_code
326 ,p_error_message => l_error_msg
327 ,p_default_wp => null
328 );
329 if l_is_wrking_day ='Y' then
330 l_max_si_days := l_max_si_days +5;
331 if get_day_of_week(l_curr_date) <> '1' then
332 l_curr_date:=l_curr_date + (7 - get_day_of_week(l_curr_date) +2);
333 else
334 l_curr_date:=l_curr_date+1;
335 end if;
336 else
337 l_curr_date := l_curr_date+1;
338 end if;
339 end loop;
340 END IF;
341 l_curr_date:=l_temp2 + 1;
342 while l_curr_date <= P_End_Date
343 loop
344 l_is_wrking_day:= PQP_SCHEDULE_CALCULATION_PKG.is_working_day
345 (p_assignment_id => l_assgn_attr.assignment_id
346 ,p_business_group_id => l_assgn_attr. business_group_id
347 ,p_date => l_curr_date
348 ,p_error_code => l_error_code
349 ,p_error_message => l_error_msg
350 ,p_default_wp => null
351 );
352 if (l_is_wrking_day ='Y' and l_counter < 5) then
353 l_counter:=l_counter+1;
354 end if;
355 l_curr_date:=l_curr_date+1;
356 end loop;
357 l_max_si_days:=l_max_si_days + l_counter;
358 return l_max_si_days;
359 END Get_Max_SI_Days;
360 --
361 -- Determines the Number of Unpaid absence Days that reduce
362 -- SI Days indicated by the segment on the Absence
363 FUNCTION Get_Non_SI_Days(P_Assignment_Id Number,
364 P_Start_Date Date,
365 P_End_Date Date) return NUMBER IS
366 CURSOR Get_Non_SI_Absence(p_person_id number) is
367 select date_start,date_end,time_start,time_end
368 from per_absence_attendances_v
369 where person_id=p_person_id
370 and ((ABS_INFORMATION_CATEGORY='NL' and ABS_INFORMATION1='Y')
371 OR (ABS_INFORMATION_CATEGORY='NL_S' and ABS_INFORMATION2='Y'))
372 and (((p_start_date between date_start and date_end)
373 or (p_end_date between date_start and date_end))
374 or ((date_start between p_start_date and p_end_date)
375 or (date_end between p_start_date and p_end_date)));
376 l_hrs_wrked NUMBER;
377 l_assgn_attr c_wp_dets%rowtype;
378 l_error_code NUMBER;
379 l_non_si_days NUMBER:=0;
380 l_error_msg fnd_new_messages.message_text%TYPE;
381 l_person_id number;
382 l_start_date per_absence_attendances_v.date_start%type;
383 l_end_date per_absence_attendances_v.date_end%type;
384 l_time_start per_absence_attendances_v.time_start%type;
385 l_time_end per_absence_attendances_v.time_end%type;
386 l_temp1 date;
387 l_temp2 date;
388 l_curr_date date;
389 l_absence_hours per_absence_attendances_v.absence_hours%type;
390 l_wrking_day varchar2(1):='N';
391 BEGIN
392 OPEN c_wp_dets(P_Assignment_Id);
393 FETCH c_wp_dets INTO l_assgn_attr;
394 CLOSE c_wp_dets;
395 select person_id into l_person_id
396 from per_all_assignments_f paa,
397 fnd_sessions ses
398 where paa.assignment_id =p_assignment_id
399 and ses.session_id = userenv('sessionid')
400 and ses.effective_date between paa.effective_start_date and paa.effective_end_date;
401 for l_absence_record in Get_Non_SI_Absence(l_person_id)
402 loop
403 --hr_utility.set_location(' date_start'||l_absence_record.date_start,100);
404 --hr_utility.set_location(' date_end'||l_absence_record.date_end,100);
405 /*Check whether the start date and end date of the abscence period are equal*/
406 if l_absence_record.date_start = l_absence_record.date_end then
407 l_hrs_wrked:=pqp_schedule_calculation_pkg.get_hours_worked
408 (p_assignment_id => l_assgn_attr.assignment_id
409 ,p_business_group_id => l_assgn_attr.business_group_id
410 ,p_date_start => l_absence_record.date_start
411 ,p_date_end => l_absence_record.date_end
412 ,p_error_code => l_error_code
413 ,p_error_message => l_error_msg
414 ,p_default_wp => NULL
415 );
416 --hr_utility.set_location('l_absence_record.time_end'||l_absence_record.time_end,110);
417 --hr_utility.set_location('l_absence_record.time_start'||l_absence_record.time_start,110);
418 --Bug 3085937
419 --shveerab
420 /*Added code to check time_end and time_start is null for the abscence defined*/
421 if (l_absence_record.time_end is null and
422 l_absence_record.time_start is null ) then
423 /*If the time_end and time_start are null then l_absene_hours should be assigned
424 to the actual working hours of l_absence_record.date_start*/
425 l_absence_hours := l_hrs_wrked;
426 else
427 /*Else the actual time difference between time_end and time_stard of the absence
428 should be assigned*/
429 l_absence_hours := (to_date('0001/01/01 '||l_absence_record.time_end,'yyyy/mm/dd hh24:mi') - to_date('0001/01/01 '||l_absence_record.time_start,'yyyy/mm/dd hh24:mi'))*24;
430 end if;
431 --hr_utility.set_location('l_absence_hours'||l_absence_hours,120);
432 l_wrking_day:= pqp_schedule_calculation_pkg.is_working_day
433 (p_assignment_id => l_assgn_attr.assignment_id
434 ,p_business_group_id => l_assgn_attr.business_group_id
435 ,p_date => l_absence_record.date_start
436 ,p_error_code => l_error_code
437 ,p_error_message => l_error_msg
438 ,p_default_wp => NULL
439 ,p_override_wp => NULL
440 );
441 /*If the absence hours is greater than the actual working hours and its a
442 working day then non si absence days should be incremented by one*/
443 if l_absence_hours >= l_hrs_wrked and l_wrking_day='Y' then
444 l_non_si_days := l_non_si_days + 1;
445 end if;
446 --hr_utility.set_location('l_non_si_days'||l_non_si_days,150);
447 else
448 if l_absence_record.date_start > p_start_date then
449 l_temp1:= l_absence_record.date_start;
450 else
451 l_temp1:=p_start_date;
452 end if;
453 if l_absence_record.date_end > p_end_date then
454 l_temp2:=p_end_date;
455 else
456 l_temp2:=l_absence_record.date_end;
457 end if;
458 l_curr_date:=l_temp1;
459 --Bug 3085937
460 --shveerab
461 /* The Absence should be considered including the end date of the absence period*/
462 while l_curr_date <= l_temp2
463 loop
464 l_wrking_day:= pqp_schedule_calculation_pkg.is_working_day
465 (p_assignment_id => l_assgn_attr.assignment_id
466 ,p_business_group_id => l_assgn_attr.business_group_id
467 ,p_date => l_curr_date
468 ,p_error_code => l_error_code
469 ,p_error_message => l_error_msg
470 ,p_default_wp => NULL
471 ,p_override_wp => NULL
472 );
473 if l_wrking_day='Y' then
474 l_non_si_days:=l_non_si_days + 1;
475 end if;
476 l_curr_date:=l_curr_date+1;
477 end loop;
478 end if;
479 end loop; /*End of for l_absence_record Loop*/
480 return l_non_si_days;
481 END Get_Non_SI_Days;
482 --
483 -- Determines the Total Number of days a Work pattern has been
484 -- setup for regardless of the work pattern start date on
485 -- employee assignment or dates of payroll period.
486 FUNCTION Get_Total_Work_Pattern_days(P_Assignment_Id Number) return NUMBER IS
487 cursor c_get_days(p_wrk_pattern pqp_assignment_attributes_f.work_pattern%type,
488 p_business_group_id pqp_assignment_attributes_f.business_group_id%type ) is
489 select count(uci.value)
490 from pay_user_tables put,
491 pay_user_columns puc,
492 pay_user_column_instances_f uci,
493 fnd_sessions ses
494 where put.user_table_id = puc.user_table_id
495 and puc.business_group_id = p_business_group_id -- Fix for bug 3977437
496 and uci.user_column_id = puc.user_column_id
497 and put.user_table_name = g_udt_name
498 and puc.user_column_name = p_wrk_pattern
499 and ses.session_id = userenv('sessionid')
500 and ses.effective_date between uci.effective_start_date and uci.effective_end_date;
501 l_assgn_attr c_wp_dets%rowtype;
502 l_wrk_pattern_days NUMBER;
503 BEGIN
504 OPEN c_wp_dets(P_Assignment_Id);
505 FETCH c_wp_dets INTO l_assgn_attr;
506 CLOSE c_wp_dets;
507 OPEN c_get_days(l_assgn_attr.work_pattern , l_assgn_attr.business_group_id);
508 FETCH c_get_days INTO l_wrk_pattern_days;
509 CLOSE c_get_days;
510 RETURN l_wrk_pattern_days;
511 END Get_Total_Work_Pattern_days;
512 --
513 -- Determines the Total Number of days marked as Working Days in a
514 -- work pattern regardless of the work pattern start date on
515 -- employee assignment or dates of payroll period.
516 FUNCTION Get_Working_Work_Pattern_days(P_Assignment_Id Number) return NUMBER IS
517 cursor c_get_wrk_days (p_wrk_pattern pqp_assignment_attributes_f.work_pattern%type ,
518 p_business_group_id pqp_assignment_attributes_f.business_group_id%type) is
519 select count(uci.value)
520 from pay_user_tables put,
521 pay_user_columns puc,
522 pay_user_column_instances_f uci,
523 fnd_sessions ses
524 where put.user_table_id = puc.user_table_id
525 and puc.business_group_id = p_business_group_id -- Fix for bug 3977437
526 and uci.user_column_id = puc.user_column_id
527 and put.user_table_name = g_udt_name
528 and puc.user_column_name = p_wrk_pattern
529 and uci.value <> '0'
530 and ses.session_id = userenv('sessionid')
531 and ses.effective_date between uci.effective_start_date and uci.effective_end_date;
532 l_assgn_attr c_wp_dets%rowtype;
533 l_working_days number;
534 BEGIN
535 OPEN c_wp_dets(P_Assignment_Id);
536 FETCH c_wp_dets INTO l_assgn_attr;
537 CLOSE c_wp_dets;
538 OPEN c_get_wrk_days(l_assgn_attr.work_pattern , l_assgn_attr.business_group_id);
539 FETCH c_get_wrk_days INTO l_working_days;
540 CLOSE c_get_wrk_days;
541 RETURN l_working_days;
542 END Get_Working_Work_Pattern_days;
543 FUNCTION get_part_time_perc (p_assignment_id IN NUMBER
544 ,p_date_earned IN DATE
545 ,p_business_group_id IN NUMBER
546 ,p_assignment_action_id IN NUMBER) RETURN number IS
547 --
548 CURSOR csr_get_pay_action_id(c_assignment_action_id NUMBER) IS
549 SELECT payroll_action_id
550 FROM pay_assignment_actions
551 WHERE assignment_action_id = c_assignment_action_id;
552 --
553 l_payroll_action_id number;
554 l_part_time_perc varchar2(35);
555 l_inputs ff_exec.inputs_t;
556 l_outputs ff_exec.outputs_t;
557 l_formula_exists BOOLEAN := TRUE;
558 l_formula_cached BOOLEAN := FALSE;
559 l_formula_id ff_formulas_f.formula_id%TYPE;
560 BEGIN
561 g_ptp_formula_name := 'NL_PART_TIME_PERCENTAGE';
562 --
563 OPEN csr_get_pay_action_id(p_assignment_action_id);
564 FETCH csr_get_pay_action_id INTO l_payroll_action_id;
565 CLOSE csr_get_pay_action_id;
566 --
567 IF g_ptp_formula_exists = TRUE THEN
568 IF g_ptp_formula_cached = FALSE THEN
569 pay_nl_general.cache_formula('NL_PART_TIME_PERCENTAGE',p_business_group_id,p_date_earned,l_formula_id,l_formula_exists,l_formula_cached);
570 g_ptp_formula_exists:=l_formula_exists;
571 g_ptp_formula_cached:=l_formula_cached;
572 g_ptp_formula_id:=l_formula_id;
573 END IF;
574 --
575 IF g_ptp_formula_exists = TRUE THEN
576 -- hr_utility.trace('FORMULA EXISTS');
577 --
578 l_inputs(1).name := 'ASSIGNMENT_ID';
579 l_inputs(1).value := p_assignment_id;
580 l_inputs(2).name := 'DATE_EARNED';
581 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
582 l_inputs(3).name := 'BUSINESS_GROUP_ID';
583 l_inputs(3).value := p_business_group_id;
584 l_inputs(4).name := 'ASSIGNMENT_ACTION_ID';
585 l_inputs(4).value := p_assignment_action_id;
586 l_inputs(5).name := 'PAYROLL_ACTION_ID';
587 l_inputs(5).value := l_payroll_action_id;
588 l_inputs(6).name := 'BALANCE_DATE';
589 l_inputs(6).value := fnd_date.date_to_canonical(p_date_earned);
590 --
591 l_outputs(1).name := 'PART_TIME_PERCENTAGE';
592 --
593 pay_nl_general.run_formula(p_formula_id => g_ptp_formula_id,
594 p_effective_date => p_date_earned,
595 p_formula_name => g_ptp_formula_name,
596 p_inputs => l_inputs,
597 p_outputs => l_outputs);
598 --
599 l_part_time_perc := l_outputs(1).value;
600 -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
601 ELSE
602 -- hr_utility.trace('FORMULA DOESNT EXISTS');
603 l_part_time_perc := NULL;
604 -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
605 END IF;
606 ELSIF g_ptp_formula_exists = FALSE THEN
607 l_part_time_perc := NULL;
608 END IF;
609 RETURN fnd_number.canonical_to_number(l_part_time_perc);
610 END get_part_time_perc;
611 -------------------------------------------------------------------------------
612 -- Function : get_standard_si_part_time_perc
613 -- To get the Standard SI Part time Percentage using
614 -- Assignment Id,Date Earned, Business Group Id and Assignment action Id.
615 ---------------------------------------------------------------------------------
616 FUNCTION get_standard_si_part_time_perc (p_assignment_id IN NUMBER
617 ,p_date_earned IN DATE
618 ,p_business_group_id IN NUMBER
619 ,p_assignment_action_id IN NUMBER) RETURN number IS
620 --
621 CURSOR csr_get_pay_action_id(c_assignment_action_id NUMBER) IS
622 SELECT payroll_action_id
623 FROM pay_assignment_actions
624 WHERE assignment_action_id = c_assignment_action_id;
625 --
626 l_payroll_action_id number;
627 l_part_time_perc varchar2(35);
628 l_inputs ff_exec.inputs_t;
629 l_outputs ff_exec.outputs_t;
630 l_formula_exists BOOLEAN := TRUE;
631 l_formula_cached BOOLEAN := FALSE;
632 l_formula_id ff_formulas_f.formula_id%TYPE;
633 BEGIN
634 g_std_ptp_formula_name := 'NL_STANDARD_SI_PART_TIME_PERCENTAGE';
635 --
636 OPEN csr_get_pay_action_id(p_assignment_action_id);
637 FETCH csr_get_pay_action_id INTO l_payroll_action_id;
638 CLOSE csr_get_pay_action_id;
639 --
640
641 IF g_std_ptp_formula_exists = TRUE THEN
642 IF g_std_ptp_formula_cached = FALSE THEN
643 pay_nl_general.cache_formula('NL_STANDARD_SI_PART_TIME_PERCENTAGE',p_business_group_id,p_date_earned,l_formula_id,l_formula_exists,l_formula_cached);
644 g_std_ptp_formula_exists:=l_formula_exists;
645 g_std_ptp_formula_cached:=l_formula_cached;
646 g_std_ptp_formula_id:=l_formula_id;
647 END IF;
648 --
649 IF g_std_ptp_formula_exists = TRUE THEN
650 -- hr_utility.trace('FORMULA EXISTS');
651 --
652 l_inputs(1).name := 'ASSIGNMENT_ID';
653 l_inputs(1).value := p_assignment_id;
654 l_inputs(2).name := 'DATE_EARNED';
655 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
656 l_inputs(3).name := 'BUSINESS_GROUP_ID';
657 l_inputs(3).value := p_business_group_id;
658 l_inputs(4).name := 'ASSIGNMENT_ACTION_ID';
659 l_inputs(4).value := p_assignment_action_id;
660 l_inputs(5).name := 'PAYROLL_ACTION_ID';
661 l_inputs(5).value := l_payroll_action_id;
662 l_inputs(6).name := 'BALANCE_DATE';
663 l_inputs(6).value := fnd_date.date_to_canonical(p_date_earned);
664 --
665 l_outputs(1).name := 'STANDARD_SI_PART_TIME_PERCENTAGE';
666 --
667 pay_nl_general.run_formula(p_formula_id => g_std_ptp_formula_id,
668 p_effective_date => p_date_earned,
669 p_formula_name => g_std_ptp_formula_name,
670 p_inputs => l_inputs,
671 p_outputs => l_outputs);
672 --
673 l_part_time_perc := l_outputs(1).value;
674 -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
675 ELSE
676 -- hr_utility.trace('FORMULA DOESNT EXISTS');
677 l_part_time_perc := NULL;
678 -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
679 END IF;
680 ELSIF g_std_ptp_formula_exists = FALSE THEN
681 l_part_time_perc := NULL;
682 END IF;
683 RETURN fnd_number.canonical_to_number(l_part_time_perc);
684 END get_standard_si_part_time_perc;
685 -------------------------------------------------------------------------------
686 -- Function : get_pseudo_si_part_time_perc
687 -- To get the Pseudo SI Part time Percentage using
688 -- Assignment Id,Date Earned, Business Group Id and Assignment action Id.
689 ---------------------------------------------------------------------------------
690 FUNCTION get_pseudo_si_part_time_perc (p_assignment_id IN NUMBER
691 ,p_date_earned IN DATE
692 ,p_business_group_id IN NUMBER
693 ,p_assignment_action_id IN NUMBER) RETURN number IS
694 --
695 CURSOR csr_get_pay_action_id(c_assignment_action_id NUMBER) IS
696 SELECT payroll_action_id
697 FROM pay_assignment_actions
698 WHERE assignment_action_id = c_assignment_action_id;
699 --
700 l_payroll_action_id number;
701 l_part_time_perc varchar2(35);
702 l_inputs ff_exec.inputs_t;
703 l_outputs ff_exec.outputs_t;
704 l_formula_exists BOOLEAN := TRUE;
705 l_formula_cached BOOLEAN := FALSE;
706 l_formula_id ff_formulas_f.formula_id%TYPE;
707 BEGIN
708 g_pse_ptp_formula_name := 'NL_PSEUDO_SI_PART_TIME_PERCENTAGE';
709 --
710 OPEN csr_get_pay_action_id(p_assignment_action_id);
711 FETCH csr_get_pay_action_id INTO l_payroll_action_id;
712 CLOSE csr_get_pay_action_id;
713 --
714
715 IF g_pse_ptp_formula_exists = TRUE THEN
716 IF g_pse_ptp_formula_cached = FALSE THEN
717 pay_nl_general.cache_formula('NL_PSEUDO_SI_PART_TIME_PERCENTAGE',p_business_group_id,p_date_earned,l_formula_id,l_formula_exists,l_formula_cached);
718 g_pse_ptp_formula_exists:=l_formula_exists;
719 g_pse_ptp_formula_cached:=l_formula_cached;
720 g_pse_ptp_formula_id:=l_formula_id;
721 END IF;
722 --
723 IF g_pse_ptp_formula_exists = TRUE THEN
724 -- hr_utility.trace('FORMULA EXISTS');
725 --
726 l_inputs(1).name := 'ASSIGNMENT_ID';
727 l_inputs(1).value := p_assignment_id;
728 l_inputs(2).name := 'DATE_EARNED';
729 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
730 l_inputs(3).name := 'BUSINESS_GROUP_ID';
731 l_inputs(3).value := p_business_group_id;
732 l_inputs(4).name := 'ASSIGNMENT_ACTION_ID';
733 l_inputs(4).value := p_assignment_action_id;
734 l_inputs(5).name := 'PAYROLL_ACTION_ID';
735 l_inputs(5).value := l_payroll_action_id;
736 l_inputs(6).name := 'BALANCE_DATE';
737 l_inputs(6).value := fnd_date.date_to_canonical(p_date_earned);
738 --
739 l_outputs(1).name := 'PSEUDO_SI_PART_TIME_PERCENTAGE';
740 --
741 pay_nl_general.run_formula(p_formula_id => g_pse_ptp_formula_id,
742 p_effective_date => p_date_earned,
743 p_formula_name => g_pse_ptp_formula_name,
744 p_inputs => l_inputs,
745 p_outputs => l_outputs);
746 --
747 l_part_time_perc := l_outputs(1).value;
748 -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
749 ELSE
750 -- hr_utility.trace('FORMULA DOESNT EXISTS');
751 l_part_time_perc := NULL;
752 -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
753 END IF;
754 ELSIF g_pse_ptp_formula_exists = FALSE THEN
755 l_part_time_perc := NULL;
756 END IF;
757 RETURN fnd_number.canonical_to_number(l_part_time_perc);
758 END get_pseudo_si_part_time_perc;
759 -------------------------------------------------------------------------------
760 -- Function : get_std_si_rep_part_time_perc
761 -- To get the Standard SI Part time Percentage for Reporting using
762 -- Assignment Id,Date Earned, Business Group Id and Assignment action Id.
763 ---------------------------------------------------------------------------------
764 FUNCTION get_std_si_rep_part_time_perc (p_assignment_id IN NUMBER
765 ,p_date_earned IN DATE
766 ,p_business_group_id IN NUMBER
767 ,p_assignment_action_id IN NUMBER) RETURN number IS
768 --
769 CURSOR csr_get_pay_action_id(c_assignment_action_id NUMBER) IS
770 SELECT payroll_action_id
771 FROM pay_assignment_actions
772 WHERE assignment_action_id = c_assignment_action_id;
773 --
774 l_payroll_action_id number;
775 l_part_time_perc varchar2(35);
776 l_inputs ff_exec.inputs_t;
777 l_outputs ff_exec.outputs_t;
778 l_formula_exists BOOLEAN := TRUE;
779 l_formula_cached BOOLEAN := FALSE;
780 l_formula_id ff_formulas_f.formula_id%TYPE;
781 BEGIN
782 g_std_rep_ptp_formula_name := 'NL_STANDARD_SI_REPORTING_PART_TIME_PERCENTAGE';
783 --
784 OPEN csr_get_pay_action_id(p_assignment_action_id);
785 FETCH csr_get_pay_action_id INTO l_payroll_action_id;
786 CLOSE csr_get_pay_action_id;
787 --
788 IF g_std_rep_ptp_formula_exists = TRUE THEN
789 IF g_std_rep_ptp_formula_cached = FALSE THEN
790 pay_nl_general.cache_formula('NL_STANDARD_SI_REPORTING_PART_TIME_PERCENTAGE',p_business_group_id,p_date_earned,l_formula_id,l_formula_exists,l_formula_cached);
791 g_std_rep_ptp_formula_exists:=l_formula_exists;
792 g_std_rep_ptp_formula_cached:=l_formula_cached;
793 g_std_rep_ptp_formula_id:=l_formula_id;
794 END IF;
795 --
796 IF g_std_rep_ptp_formula_exists = TRUE THEN
797 -- hr_utility.trace('FORMULA EXISTS');
798 --
799 l_inputs(1).name := 'ASSIGNMENT_ID';
800 l_inputs(1).value := p_assignment_id;
801 l_inputs(2).name := 'DATE_EARNED';
802 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
803 l_inputs(3).name := 'BUSINESS_GROUP_ID';
804 l_inputs(3).value := p_business_group_id;
805 l_inputs(4).name := 'ASSIGNMENT_ACTION_ID';
806 l_inputs(4).value := p_assignment_action_id;
807 l_inputs(5).name := 'PAYROLL_ACTION_ID';
808 l_inputs(5).value := l_payroll_action_id;
809 l_inputs(6).name := 'BALANCE_DATE';
810 l_inputs(6).value := fnd_date.date_to_canonical(p_date_earned);
811 --
812 l_outputs(1).name := 'STANDARD_SI_REPORTING_PART_TIME_PERCENTAGE';
813 --
814 pay_nl_general.run_formula(p_formula_id => g_std_rep_ptp_formula_id,
815 p_effective_date => p_date_earned,
816 p_formula_name => g_std_rep_ptp_formula_name,
817 p_inputs => l_inputs,
818 p_outputs => l_outputs);
819 --
820 l_part_time_perc := l_outputs(1).value;
821 -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
822 ELSE
823 -- hr_utility.trace('FORMULA DOESNT EXISTS');
824 l_part_time_perc := NULL;
825 -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
826 END IF;
827 ELSIF g_std_rep_ptp_formula_exists = FALSE THEN
828 l_part_time_perc := NULL;
829 END IF;
830 RETURN fnd_number.canonical_to_number(l_part_time_perc);
831 END get_std_si_rep_part_time_perc;
832 -------------------------------------------------------------------------------
833 -- Function : get_pse_si_rep_part_time_perc
834 -- To get the Pseudo SI Part time Percentage for reporting using
835 -- Assignment Id,Date Earned, Business Group Id and Assignment action Id.
836 ---------------------------------------------------------------------------------
837 FUNCTION get_pse_si_rep_part_time_perc (p_assignment_id IN NUMBER
838 ,p_date_earned IN DATE
839 ,p_business_group_id IN NUMBER
840 ,p_assignment_action_id IN NUMBER) RETURN number IS
841 --
842 CURSOR csr_get_pay_action_id(c_assignment_action_id NUMBER) IS
843 SELECT payroll_action_id
844 FROM pay_assignment_actions
845 WHERE assignment_action_id = c_assignment_action_id;
846 --
847 l_payroll_action_id number;
848 l_part_time_perc varchar2(35);
849 l_inputs ff_exec.inputs_t;
850 l_outputs ff_exec.outputs_t;
851 l_formula_exists BOOLEAN := TRUE;
852 l_formula_cached BOOLEAN := FALSE;
853 l_formula_id ff_formulas_f.formula_id%TYPE;
854 BEGIN
855 g_pse_rep_ptp_formula_name := 'NL_PSEUDO_SI_REPORTING_PART_TIME_PERCENTAGE';
856 --
857 OPEN csr_get_pay_action_id(p_assignment_action_id);
858 FETCH csr_get_pay_action_id INTO l_payroll_action_id;
859 CLOSE csr_get_pay_action_id;
860 --
861 IF g_pse_rep_ptp_formula_exists = TRUE THEN
862 IF g_pse_rep_ptp_formula_cached = FALSE THEN
863 pay_nl_general.cache_formula('NL_PSEUDO_SI_REPORTING_PART_TIME_PERCENTAGE',p_business_group_id,p_date_earned,l_formula_id,l_formula_exists,l_formula_cached);
864 g_pse_rep_ptp_formula_exists:=l_formula_exists;
865 g_pse_rep_ptp_formula_cached:=l_formula_cached;
866 g_pse_rep_ptp_formula_id:=l_formula_id;
867 END IF;
868 --
869 IF g_pse_rep_ptp_formula_exists = TRUE THEN
870 -- hr_utility.trace('FORMULA EXISTS');
871 --
872 l_inputs(1).name := 'ASSIGNMENT_ID';
873 l_inputs(1).value := p_assignment_id;
874 l_inputs(2).name := 'DATE_EARNED';
875 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
876 l_inputs(3).name := 'BUSINESS_GROUP_ID';
877 l_inputs(3).value := p_business_group_id;
878 l_inputs(4).name := 'ASSIGNMENT_ACTION_ID';
879 l_inputs(4).value := p_assignment_action_id;
880 l_inputs(5).name := 'PAYROLL_ACTION_ID';
881 l_inputs(5).value := l_payroll_action_id;
882 l_inputs(6).name := 'BALANCE_DATE';
883 l_inputs(6).value := fnd_date.date_to_canonical(p_date_earned);
884 --
885 l_outputs(1).name := 'PSEUDO_SI_REPORTING_PART_TIME_PERCENTAGE';
886 --
887 pay_nl_general.run_formula(p_formula_id => g_pse_rep_ptp_formula_id,
888 p_effective_date => p_date_earned,
889 p_formula_name => g_pse_rep_ptp_formula_name,
890 p_inputs => l_inputs,
891 p_outputs => l_outputs);
892 --
893 l_part_time_perc := l_outputs(1).value;
894 -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
895 ELSE
896 -- hr_utility.trace('FORMULA DOESNT EXISTS');
897 l_part_time_perc := NULL;
898 -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
899 END IF;
900 ELSIF g_pse_rep_ptp_formula_exists = FALSE THEN
901 l_part_time_perc := NULL;
902 END IF;
903 RETURN fnd_number.canonical_to_number(l_part_time_perc);
904 END get_pse_si_rep_part_time_perc;
905 /* This Function returns the day of the week.
906 Sunday is considered to be the first day of the week*/
907 FUNCTION get_day_of_week(p_date date)
908 return number is
909 l_reference_date date:=to_date('01/01/1984','DD/MM/YYYY');
910 v_index number;
911 begin
912 v_index:=abs(p_date - l_reference_date);
913 v_index:=mod(v_index,7);
914 return v_index+1;
915 end get_day_of_week;
916
917 -----------------------------------------------------------------------------------------------
918 -- Function : get_avg_part_time_percentage
919 -- To get the average Pseudo SI Part time Percentage
920 -- Assignment Id,Period Start Date,Period End Date
921 -----------------------------------------------------------------------------------------------
922 FUNCTION get_avg_part_time_percentage
923 ( p_assignment_id IN per_all_assignments_f.assignment_id%type ,
924 p_period_start_date IN DATE ,
925 p_period_end_date IN DATE
926 )
927 RETURN NUMBER IS
928 -- Cursor to retrieve part time percentages and effective start and end dates for an assignment.
929 CURSOR
930 csr_get_part_time_per( p_assignment_id per_all_assignments_f.assignment_id%type,
931 p_period_start_date DATE ,
932 p_period_end_date DATE
933 ) IS
934 SELECT FND_NUMBER.CANONICAL_TO_NUMBER(hs.segment29) segment29 ,
935 pas.effective_start_date effective_start_date,
936 pas.effective_end_date effective_end_date
937 FROM
938 hr_soft_coding_keyflex hs ,
939 per_all_assignments_f pas
940 WHERE
941 pas.assignment_id = p_assignment_id AND
942 hs.soft_coding_keyflex_id = pas.soft_coding_keyflex_id AND
943 pas.effective_start_date <= p_period_end_date AND
944 pas.effective_end_date >= p_period_start_date ;
945 l_ef_start_date per_all_assignments_f.effective_start_date%type;
946 l_ef_end_date per_all_assignments_f.effective_end_date%type ;
947 l_period_start_date DATE;
948 l_period_end_date DATE;
949 l_asg_start_date DATE;
950 l_asg_end_date DATE;
951 l_days NUMBER := 0;
952 l_part_time_perc NUMBER := 0;
953 l_part_time_days NUMBER := 0;
954 l_part_time_days_add NUMBER := 0;
955 l_period_days NUMBER := 0;
956 l_part_time_percentage NUMBER := 0;
957 a NUMBER;
958 BEGIN
959 a := PAY_NL_GENERAL.get_period_asg_dates
960 ( p_assignment_id => p_assignment_id
961 , p_period_start_date => p_period_start_date
962 , p_period_end_date => p_period_end_date
963 , p_asg_start_date => l_asg_start_date
964 , p_asg_end_date => l_asg_end_date
965 );
966 l_period_start_date := GREATEST( p_period_start_date , l_asg_start_date);
967 l_period_end_date := LEAST( p_period_end_date , l_asg_end_date );
968
969 FOR l_part_time_parameters IN
970 csr_get_part_time_per(p_assignment_id ,l_period_start_date,l_period_end_date)
971 LOOP
972 l_ef_start_date := l_part_time_parameters.effective_start_date;
973 l_ef_end_date := l_part_time_parameters.effective_end_date;
974 l_ef_end_date := LEAST(l_period_end_date, l_ef_end_date ) ;
975 l_ef_start_date := GREATEST(l_period_start_date, l_ef_start_date);
976 l_days := (l_ef_end_date -l_ef_start_date ) + 1;
977 l_part_time_perc := nvl(l_part_time_parameters.segment29 ,100);
978 l_part_time_days := l_part_time_perc*l_days;
979 l_part_time_days_add := l_part_time_days_add + l_part_time_days;
980 END LOOP;
981 l_period_days := ( l_period_end_date - l_period_start_date )+ 1;
982 l_part_time_percentage := l_part_time_days_add/l_period_days;
983 RETURN ROUND(l_part_time_percentage,4);
984 END get_avg_part_time_percentage;
985
986 -----------------------------------------------------------------------------------------------
987 -- Function : get_real_si_days
988 -- To get the override for Real SI Days
989 -- Assignment Id,Date Earned, Business Group Id and Assignment action Id.
990 -----------------------------------------------------------------------------------------------
991 FUNCTION get_real_si_days
992 ( p_assignment_id IN NUMBER
993 ,p_date_earned IN DATE
994 ,p_business_group_id IN NUMBER
995 ,p_assignment_action_id IN NUMBER
996 ,p_payroll_action_id IN NUMBER
997 )
998 RETURN NUMBER IS
999 l_real_si_days varchar2(35);
1000 l_inputs ff_exec.inputs_t;
1001 l_outputs ff_exec.outputs_t;
1002 l_formula_exists BOOLEAN := TRUE;
1003 l_formula_cached BOOLEAN := FALSE;
1004 l_formula_id ff_formulas_f.formula_id%TYPE;
1005 BEGIN
1006 g_real_si_days_formula_name := 'NL_REAL_SOCIAL_INSURANCE_DAYS';
1007 IF g_real_si_days_formula_exists = TRUE THEN
1008 IF g_real_si_days_formula_cached = FALSE THEN
1009 pay_nl_general.cache_formula('NL_REAL_SOCIAL_INSURANCE_DAYS',p_business_group_id,p_date_earned,l_formula_id,l_formula_exists,l_formula_cached);
1010 g_real_si_days_formula_exists:=l_formula_exists;
1011 g_real_si_days_formula_cached:=l_formula_cached;
1012 g_real_si_days_formula_id:=l_formula_id;
1013 END IF;
1014 --
1015 IF g_real_si_days_formula_exists = TRUE THEN
1016 --
1017 l_inputs(1).name := 'ASSIGNMENT_ID';
1018 l_inputs(1).value := p_assignment_id;
1019 l_inputs(2).name := 'DATE_EARNED';
1020 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
1021 l_inputs(3).name := 'BUSINESS_GROUP_ID';
1022 l_inputs(3).value := p_business_group_id;
1023 l_inputs(4).name := 'ASSIGNMENT_ACTION_ID';
1024 l_inputs(4).value := p_assignment_action_id;
1025 l_inputs(5).name := 'PAYROLL_ACTION_ID';
1026 l_inputs(5).value := p_payroll_action_id;
1027 l_inputs(6).name := 'BALANCE_DATE';
1028 l_inputs(6).value := fnd_date.date_to_canonical(p_date_earned);
1029 --
1030 l_outputs(1).name := 'REAL_SOCIAL_INSURANCE_DAYS';
1031 --
1032 pay_nl_general.run_formula(p_formula_id => g_real_si_days_formula_id,
1033 p_effective_date => p_date_earned,
1034 p_formula_name => g_real_si_days_formula_name,
1035 p_inputs => l_inputs,
1036 p_outputs => l_outputs);
1037 --
1038 l_real_si_days := l_outputs(1).value;
1039 ELSE
1040 l_real_si_days := NULL;
1041 END IF;
1042 ELSIF g_real_si_days_formula_exists = FALSE THEN
1043 l_real_si_days := NULL;
1044 END IF;
1045 RETURN fnd_number.canonical_to_number(l_real_si_days);
1046 END get_real_si_days;
1047
1048
1049 Function IsLeapYear(p_year NUMBER) RETURN NUMBER IS
1050 l_year NUMBER;
1051 BEGIN
1052 If (p_year Mod 4 = 0) And
1053 ((p_year Mod 100 <> 0) Or (p_year Mod 400 = 0)) Then
1054 l_year :=1;
1055 Else
1056 l_year :=0;
1057 End If;
1058 return l_year;
1059 End IsLeapYear ;
1060
1061
1062 FUNCTION get_asg_ind_work_hours
1063 ( p_assignment_id IN per_all_assignments_f.assignment_id%type ,
1064 p_period_start_date IN DATE ,
1065 p_period_end_date IN DATE
1066 )
1067 RETURN NUMBER IS
1068 -- Cursor to retrieve part time percentages and effective start and end dates for an assignment.
1069 CURSOR
1070 csr_get_ind_work_hrs( p_assignment_id per_all_assignments_f.assignment_id%type,
1071 p_period_start_date DATE ,
1072 p_period_end_date DATE
1073 ) IS
1074 SELECT FND_NUMBER.CANONICAL_TO_NUMBER(scl.segment28) segment28 ,
1075 paa.frequency ,
1076 paa.effective_start_date effective_start_date,
1077 paa.effective_end_date effective_end_date
1078 FROM
1079 hr_soft_coding_keyflex scl ,
1080 per_all_assignments_f paa
1081 WHERE
1082 paa.assignment_id = p_assignment_id AND
1083 scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id AND
1084 paa.effective_start_date <= p_period_end_date AND
1085 paa.effective_end_date >= p_period_start_date ;
1086
1087 TYPE days_month IS TABLE OF NUMBER;
1088 days_in days_month;
1089
1090 l_ef_start_date per_all_assignments_f.effective_start_date%type;
1091 l_ef_end_date per_all_assignments_f.effective_end_date%type ;
1092 l_period_start_date DATE;
1093 l_period_end_date DATE;
1094 l_asg_start_date DATE;
1095 l_asg_end_date DATE;
1096 l_days NUMBER := 0;
1097 l_ind_wrk_hrs NUMBER := 0;
1098 l_cum_ind_wrk_hrs NUMBER := 0;
1099 l_part_time_days_add NUMBER := 0;
1100 l_temp NUMBER;
1101 l_mon_days NUMBER := 0;
1102 l_freq VARCHAR2(10);
1103
1104
1105
1106 BEGIN
1107
1108 l_mon_days := ( p_period_end_date - p_period_start_date )+ 1;
1109
1110 l_temp := PAY_NL_GENERAL.get_period_asg_dates
1111 ( p_assignment_id => p_assignment_id
1112 , p_period_start_date => p_period_start_date
1113 , p_period_end_date => p_period_end_date
1114 , p_asg_start_date => l_asg_start_date
1115 , p_asg_end_date => l_asg_end_date
1116 );
1117 l_period_start_date := GREATEST( p_period_start_date , l_asg_start_date);
1118 l_period_end_date := LEAST( p_period_end_date , l_asg_end_date );
1119
1120 FOR l_ind_wrk_hrs_csr IN
1121 csr_get_ind_work_hrs (p_assignment_id ,l_period_start_date,l_period_end_date)
1122 LOOP
1123 l_ef_start_date := l_ind_wrk_hrs_csr.effective_start_date;
1124 l_ef_end_date := l_ind_wrk_hrs_csr.effective_end_date;
1125 l_ef_end_date := LEAST(l_period_end_date, l_ef_end_date ) ;
1126 l_ef_start_date := GREATEST(l_period_start_date, l_ef_start_date);
1127 l_days := (l_ef_end_date -l_ef_start_date ) + 1;
1128 l_ind_wrk_hrs := nvl(l_ind_wrk_hrs_csr.segment28 ,0);
1129 l_freq := l_ind_wrk_hrs_csr.frequency;
1130
1131 if l_freq = 'D' then
1132 l_cum_ind_wrk_hrs := l_cum_ind_wrk_hrs + (((l_ind_wrk_hrs*260)/12/l_mon_days)*l_days);
1133 end if;
1134
1135 if l_freq = 'W' then
1136 l_cum_ind_wrk_hrs := l_cum_ind_wrk_hrs + (((l_ind_wrk_hrs*52)/12/l_mon_days)*l_days);
1137 end if;
1138
1139 if l_freq = 'M' then
1140 l_cum_ind_wrk_hrs := l_cum_ind_wrk_hrs + (((l_ind_wrk_hrs)/l_mon_days)*l_days);
1141 end if;
1142
1143 if l_freq = 'Y' then
1144 l_cum_ind_wrk_hrs := l_cum_ind_wrk_hrs + (((l_ind_wrk_hrs)/12/l_mon_days)*l_days);
1145 end if;
1146
1147 END LOOP;
1148
1149 RETURN l_cum_ind_wrk_hrs;
1150 END get_asg_ind_work_hours;
1151
1152 FUNCTION get_period_si_days( p_assignment_id NUMBER
1153 ,p_payroll_id NUMBER
1154 ,p_effective_date DATE
1155 ,p_source_text VARCHAR2
1156 ,p_override_day_method VARCHAR2
1157 ,p_override_day_value VARCHAR2
1158 ,p_avg_ws_si_days NUMBER
1159 ,p_override_si_days NUMBER
1160 ,p_real_si_days NUMBER
1161 ,p_si_day_method VARCHAR2
1162 ,p_max_si_method VARCHAR2
1163 ,p_multi_asg_si_days NUMBER
1164 ,p_year_calc VARCHAR2
1165 ,p_override_real_si_days NUMBER
1166 ,p_override OUT NOCOPY VARCHAR2
1167 ,p_period_si_days_year_calc NUMBER
1168 )
1169 RETURN NUMBER
1170 IS
1171 l_number NUMBER;
1172 l_period_start_date DATE;
1173 l_period_end_date DATE;
1174 period_start_date DATE;
1175 period_end_date DATE;
1176 l_asg_start_date DATE;
1177 l_asg_end_date DATE;
1178 l_period_type VARCHAR2(50);
1179 Average_SI_days NUMBER;
1180 Total_Days NUMBER;
1181 Work_Days NUMBER;
1182 Override VARCHAR2(10);
1183 Period_SI_Days NUMBER := 0;
1184 Max_SI_Days NUMBER;
1185 Available_SI_Days NUMBER;
1186 Multi_Assign_Max_Days NUMBER;
1187 l_si_day_method VARCHAR2(10) := '0';
1188 CURSOR get_payroll_period( p_payroll_id NUMBER
1189 ,p_effective_date DATE ) IS
1190 select START_DATE
1191 ,END_DATE
1192 from
1193 per_time_periods
1194 where payroll_id=p_payroll_id
1195 and p_effective_date between START_DATE and END_DATE;
1196 BEGIN
1197
1198 OPEN get_payroll_period(p_payroll_id,p_effective_date);
1199 FETCH get_payroll_period INTO l_period_start_date, l_period_end_date;
1200 CLOSE get_payroll_period;
1201 l_number := pay_nl_general.get_period_asg_dates (p_assignment_id ,l_period_start_date ,l_period_end_date
1202 ,l_asg_start_date , l_asg_end_date );
1203
1204
1205 l_period_type := pay_nl_si_pkg.get_payroll_type(p_payroll_id);
1206 IF l_period_type = 'Calendar Month' THEN
1207 Average_SI_days:= fnd_number.canonical_to_number(pay_nl_general.get_global_value(p_effective_date,'NL_SI_AVERAGE_DAYS_MONTHLY'));
1208 END IF;
1209 IF l_period_type = 'Week' THEN
1210 Average_SI_days:= fnd_number.canonical_to_number(pay_nl_general.get_global_value(p_effective_date,'NL_SI_AVERAGE_DAYS_WEEKLY'));
1211 END IF;
1212 IF l_period_type = 'Quarter' THEN
1213 Average_SI_days:= fnd_number.canonical_to_number(pay_nl_general.get_global_value(p_effective_date,'NL_SI_AVERAGE_DAYS_QUARTERLY'));
1214 END IF;
1215 IF l_period_type = 'Lunar Month' THEN
1216 Average_SI_days:= fnd_number.canonical_to_number(pay_nl_general.get_global_value(p_effective_date,'NL_SI_AVERAGE_DAYS_4WEEKLY'));
1217 END IF;
1218 IF l_asg_start_date > l_period_start_date THEN
1219 Total_Days := fffunc.days_between(l_period_end_date, l_period_start_date) + 1;
1220 IF l_Asg_End_Date < l_period_end_date THEN
1221 Work_Days := fffunc.days_between(l_asg_end_date, l_asg_start_date) + 1;
1222 ELSE
1223 Work_Days := fffunc.days_between(l_period_end_date, l_asg_start_date) + 1;
1224 END If;
1225 Average_SI_Days := round((Average_SI_Days / Total_Days * Work_Days),2);
1226
1227 ELSE
1228 IF l_asg_end_date < l_period_end_date THEN
1229 Total_Days := fffunc.days_between(l_period_end_date, l_period_start_date) + 1;
1230 Work_Days := fffunc.days_between(l_asg_end_date, l_period_start_date) + 1;
1231 Average_SI_Days := round((Average_SI_Days / Total_Days * Work_Days),2);
1232 END IF;
1233 END IF;
1234
1235 Period_Start_Date := GREATEST( l_period_start_date , l_asg_start_date);
1236 Period_End_Date := LEAST( l_period_end_date , l_asg_end_date );
1237 IF p_max_si_method = '1' THEN
1238 /* If the selected method is the number of week days in a payroll period/year. This is
1239 forced if there are multi assignments. Then call relevant formula function.*/
1240 Max_SI_Days := GET_WEEK_DAYS (Period_Start_Date, Period_End_Date);
1241 ELSE
1242 /* If the selected method is 5 days per week for the weeks worked. Then call relevant formula
1243 function */
1244 Max_SI_Days := GET_MAX_SI_DAYS (p_assignment_id,Period_Start_Date, Period_End_Date);
1245 END IF;
1246
1247 Override := 'N';
1248
1249 IF p_override_si_days <> -1 THEN
1250 Period_SI_Days := p_override_si_days;
1251 Override := 'Y';
1252 ELSE
1253 IF p_override_day_method = '0' THEN
1254 Period_SI_Days := to_number(p_override_day_value);
1255 Override := 'Y';
1256 ELSE
1257 IF p_override_day_method = '1' THEN
1258 Period_SI_Days := (Average_SI_Days / 100) * to_number(p_override_day_value);
1259 Period_SI_Days := Round(Period_SI_Days,2);
1260 Override := 'Y';
1261 ELSE
1262 IF p_override_day_method = '2' THEN
1263 Period_SI_Days := (Max_SI_Days / 100) * to_number(p_override_day_value);
1264 Period_SI_Days := Round(Period_SI_Days, 2);
1265 Override := 'Y';
1266 END IF;
1267 END IF;
1268 END IF;
1269 END IF;
1270
1271 Multi_Assign_Max_Days := Max_SI_Days;
1272
1273 IF p_si_day_method = '0' AND p_year_calc= 'N' AND Override = 'N' THEN
1274 /* Average Days */
1275 Period_SI_Days := Average_SI_Days;
1276 Period_SI_Days := Period_SI_Days - p_multi_asg_si_days ;
1277 IF (Period_SI_Days <0) THEN
1278 Period_SI_Days := 0 ;
1279 END IF;
1280 ELSE
1281 IF p_si_day_method = '2' AND p_year_calc= 'N' AND Override = 'N' THEN
1282 /* Average Days with Work Schedules*/
1283 Period_SI_Days := p_avg_ws_si_days;
1284 Period_SI_Days := Period_SI_Days - p_multi_asg_si_days ;
1285 IF (Period_SI_Days <0) THEN
1286 Period_SI_Days :=0;
1287 END IF;
1288 ELSE
1289 IF (p_si_day_method = '1' AND p_year_calc = 'N') AND Override = 'N' THEN
1290 /* Real SI Days */
1291 Period_SI_Days := p_real_si_days;
1292 /* Check if override of real SI days was used. If so then re-set override indicator */
1293 IF p_override_real_si_days <> -1 THEN
1294 Override := 'Y';
1295 END IF;
1296 ELSE
1297 IF p_year_calc = 'Y' AND Override = 'N' AND p_si_day_method <> '3' THEN
1298 /* Adjusted year value */
1299 Period_SI_Days := p_period_si_days_year_calc;
1300 l_si_day_method := '1';
1301 /* Adjust Max SI Days for multi assignments*/
1302 Multi_Assign_Max_Days := Period_SI_Days;
1303 END IF;
1304 END IF;
1305 END IF;
1306 END IF;
1307 Available_SI_Days:= 0 ;
1308 /*Max Si Days Rule is applicable only for Real SI Days*/
1309 IF (p_si_day_method = '1' or l_si_day_method = '1') AND Override = 'N' THEN
1310 IF Multi_Assign_Max_Days > p_multi_asg_si_days THEN
1311 Available_SI_Days := Multi_Assign_Max_Days - p_multi_asg_si_days;
1312 ELSE
1313 Available_SI_Days := 0;
1314 END IF;
1315 IF Period_SI_Days > Available_SI_Days THEN
1316 Period_SI_Days := Available_SI_Days;
1317 END IF;
1318 END IF;
1319
1320 p_override := Override;
1321
1322
1323 RETURN Period_SI_Days;
1324 END get_period_si_days;
1325
1326 FUNCTION get_ret_real_si_days ( p_assignment_id NUMBER
1327 ,p_payroll_id NUMBER
1328 ,p_effective_date DATE
1329 ,p_source_text VARCHAR2
1330 ,p_source_text2 VARCHAR2
1331 ,p_real_si_days NUMBER
1332 ,p_override_real_si_days NUMBER
1333 ,p_max_si_method VARCHAR2
1334 ,p_real_si_sit_ytd NUMBER
1335 ,p_real_si_sit_ptd NUMBER
1336 ,p_ret_real_si_sit_ytd NUMBER
1337 ,p_real_si_per_pay_sitp_ptd NUMBER
1338 )
1339 RETURN NUMBER IS
1340 l_period_start_date DATE;
1341 l_period_end_date DATE;
1342 l_asg_start_date DATE;
1343 l_asg_end_date DATE;
1344 Period_Start_Date DATE;
1345 Period_End_Date DATE;
1346 p_ret_real_si_days NUMBER := 0;
1347 l_number NUMBER;
1348 l_avail_real_si_days NUMBER;
1349 l_cum_real_si_days NUMBER;
1350 Max_SI_Days NUMBER;
1351 l_multi_asg_real_si NUMBER;
1352 l_si_avg_days_yr NUMBER;
1353 CURSOR get_payroll_period( p_payroll_id NUMBER
1354 ,p_effective_date DATE ) IS
1355 select START_DATE
1356 ,END_DATE
1357 from
1358 per_time_periods
1359 where payroll_id=p_payroll_id
1360 and p_effective_date between START_DATE and END_DATE;
1361 BEGIN
1362 OPEN get_payroll_period(p_payroll_id,p_effective_date);
1363 FETCH get_payroll_period INTO l_period_start_date, l_period_end_date;
1364 CLOSE get_payroll_period;
1365 l_number := pay_nl_general.get_period_asg_dates (p_assignment_id ,l_period_start_date ,l_period_end_date
1366 ,l_asg_start_date , l_asg_end_date );
1367
1368
1369 Period_Start_Date := GREATEST( l_period_start_date , l_asg_start_date);
1370 Period_End_Date := LEAST( l_period_end_date , l_asg_end_date );
1371
1372 IF p_max_si_method = '1' THEN
1373 /* If the selected method is the number of week days in a payroll period/year. This is
1374 forced if there are multi assignments. Then call relevant formula function.*/
1375 Max_SI_Days := GET_WEEK_DAYS (Period_Start_Date, Period_End_Date);
1376 ELSE
1377 /* If the selected method is 5 days per week for the weeks worked. Then call relevant formula
1378 function */
1379 Max_SI_Days := GET_MAX_SI_DAYS (p_assignment_id,Period_Start_Date, Period_End_Date);
1380 END IF;
1381 /* Set return value for real SI days */
1382 p_ret_real_si_days := p_real_si_days;
1383 l_multi_asg_real_si := p_real_si_per_pay_sitp_ptd - p_real_si_sit_ptd;
1384 IF p_override_real_si_days = -1 THEN
1385 IF Max_SI_Days >l_multi_asg_real_si THEN
1386 l_avail_real_si_days := Max_SI_Days - l_multi_asg_real_si ;
1387 ELSE
1388 l_avail_real_si_days := 0;
1389 END IF;
1390 IF p_real_si_days > l_avail_real_si_days THEN
1391 p_ret_real_si_days := l_avail_real_si_days;
1392 END IF;
1393 END IF;
1394 l_si_avg_days_yr := fnd_number.canonical_to_number(pay_nl_general.get_global_value(p_effective_date,'NL_SI_AVERAGE_DAYS_YEARLY'));
1395 IF p_override_real_si_days = -1 THEN
1396 l_cum_real_si_days := (p_real_si_sit_ytd - p_real_si_sit_ptd) + p_ret_real_si_days + p_ret_real_si_sit_ytd;
1397 IF l_cum_real_si_days > l_si_avg_days_yr THEN
1398 p_ret_real_si_days := p_ret_real_si_days - (l_cum_real_si_days - l_si_avg_days_yr);
1399 END IF;
1400 END IF;
1401 p_ret_real_si_days := p_ret_real_si_days - p_real_si_sit_ptd;
1402 RETURN p_ret_real_si_days;
1403 END get_ret_real_si_days;
1404
1405 FUNCTION get_thres_or_max_si ( p_assignment_id NUMBER
1406 ,p_payroll_id NUMBER
1407 ,p_effective_date DATE
1408 ,p_calc_code NUMBER
1409 ,p_part_time_perc NUMBER
1410 ,p_si_days NUMBER
1411 ,p_thre_max_si NUMBER
1412 )
1413 RETURN NUMBER IS
1414
1415 l_period_start_date DATE;
1416 l_period_end_date DATE;
1417 l_asg_start_date DATE;
1418 l_asg_end_date DATE;
1419 Period_Start_Date DATE;
1420 Period_End_Date DATE;
1421 l_number NUMBER;
1422 Non_SI_Days NUMBER;
1423 Total_Days NUMBER;
1424 Work_Days NUMBER;
1425 Absence_Factor NUMBER;
1426 Threshold_or_Max_SI NUMBER;
1427 l_avg_si_days_monthly NUMBER;
1428
1429 CURSOR get_payroll_period( p_payroll_id NUMBER
1430 ,p_effective_date DATE ) IS
1431 select START_DATE
1432 ,END_DATE
1433 from
1434 per_time_periods
1435 where payroll_id=p_payroll_id
1436 and p_effective_date between START_DATE and END_DATE;
1437
1438 BEGIN
1439
1440 OPEN get_payroll_period(p_payroll_id,p_effective_date);
1441 FETCH get_payroll_period INTO l_period_start_date, l_period_end_date;
1442 CLOSE get_payroll_period;
1443 l_number := pay_nl_general.get_period_asg_dates (p_assignment_id ,l_period_start_date ,l_period_end_date
1444 ,l_asg_start_date , l_asg_end_date );
1445
1446 Period_Start_Date := GREATEST( l_period_start_date , l_asg_start_date);
1447 Period_End_Date := LEAST( l_period_end_date , l_asg_end_date );
1448
1449 l_avg_si_days_monthly := fnd_number.canonical_to_number(pay_nl_general.get_global_value(p_effective_date,'NL_SI_AVERAGE_DAYS_MONTHLY'));
1450
1451 IF (p_calc_code = 0 OR p_calc_code =1 OR p_calc_code = 3 ) then
1452
1453 Threshold_or_Max_SI := p_si_days * to_number(p_thre_max_si);
1454
1455 ELSE
1456
1457 Threshold_or_Max_SI := to_number(p_thre_max_si) * (p_part_time_perc / 100);
1458
1459 /* Added Proration code for Bug 3412227
1460 Pro-rate SI base and threshold if employee started or left during the payroll period */
1461 IF l_Asg_Start_Date > l_period_start_date THEN
1462
1463 Total_Days := fffunc.days_between(l_period_end_date, l_period_start_date) + 1;
1464 IF l_Asg_End_Date < l_period_end_date THEN
1465 Work_Days := fffunc.days_between(l_Asg_End_Date, l_Asg_Start_Date) + 1;
1466 ELSE
1467 Work_Days := fffunc.days_between(l_period_end_date, l_Asg_Start_Date) + 1;
1468 END IF;
1469 Threshold_or_Max_SI := round((Threshold_or_Max_SI / Total_Days * Work_Days),2);
1470
1471 ELSE
1472
1473 IF l_Asg_End_Date < l_period_end_date THEN
1474
1475 Total_Days := fffunc.days_between(l_period_end_date, l_period_start_date) + 1;
1476 Work_Days := fffunc.days_between(l_Asg_End_Date, l_period_start_date) + 1;
1477 Threshold_or_Max_SI := round((Threshold_or_Max_SI / Total_Days * Work_Days),2) ;
1478
1479 END IF;
1480 END IF;
1481
1482
1483 Non_SI_Days := GET_NON_SI_DAYS(p_assignment_id,Period_Start_Date, Period_End_Date);
1484
1485
1486 IF Non_SI_Days > 0 THEN
1487
1488 /* Determine factor to reduce Maximum SI Salary and Thresholds by any non SI absences*/
1489 IF l_avg_si_days_monthly > Non_SI_Days then
1490 Absence_Factor := (l_avg_si_days_monthly - Non_SI_Days) / l_avg_si_days_monthly ;
1491 ELSE
1492 Absence_Factor := 0;
1493 END IF;
1494
1495 Threshold_or_Max_SI := Threshold_or_Max_SI * Absence_Factor;
1496
1497 END IF;
1498
1499 END IF;
1500
1501 RETURN Threshold_or_Max_SI;
1502
1503 END get_thres_or_max_si;
1504
1505 FUNCTION get_si_proration_days (p_assignment_id NUMBER
1506 ,p_period_start_date DATE
1507 ,p_period_end_date DATE
1508 ,p_proration_start_date DATE
1509 ,p_proration_end_date DATE
1510 ,p_period_si_days NUMBER
1511 )
1512 RETURN NUMBER IS
1513 CURSOR csr_asg_dates (p_other_assignment_id NUMBER) IS
1514 SELECT MIN(asg.effective_start_date) asg_start_date
1515 ,MAX(asg.effective_end_date) asg_end_date
1516 FROM PER_ASSIGNMENTS_F asg
1517 ,PER_ASSIGNMENT_STATUS_TYPES past
1518 WHERE asg.assignment_id = p_other_assignment_id
1519 AND past.per_system_status = 'ACTIVE_ASSIGN'
1520 AND asg.assignment_status_type_id = past.assignment_status_type_id
1521 AND asg.effective_start_date <= p_period_end_date
1522 AND NVL(asg.effective_end_date,p_period_end_date) >= p_period_start_date;
1523
1524 CURSOR csr_any_other_asg IS
1525 SELECT DISTINCT asg1.assignment_id asgid
1526 FROM per_assignments_f asg1
1527 ,per_assignments_f asg2
1528 ,pay_object_groups pog1
1529 ,pay_object_groups pog2
1530 WHERE asg1.person_id = asg2.person_id
1531 AND asg2.assignment_id = p_assignment_id
1532 AND asg1.assignment_id <> p_assignment_id
1533 AND pog1.source_id = asg1.assignment_id
1534 AND pog1.source_type = 'PAF'
1535 AND pog2.source_id = asg2.assignment_id
1536 AND pog2.source_type = 'PAF'
1537 AND pog1.parent_object_group_id = pog2.parent_object_group_id;
1538
1539 CURSOR csr_person_dates IS
1540 SELECT MIN(asg.effective_start_date) per_start_date
1541 ,MAX(asg.effective_end_date) per_end_date
1542 FROM PER_ASSIGNMENTS_F asg,
1543 PER_ASSIGNMENT_STATUS_TYPES past,
1544 PAY_OBJECT_GROUPS pog
1545 WHERE asg.person_id in
1546 (select asg2.person_id from PER_ASSIGNMENTS_F asg2, PAY_OBJECT_GROUPS pog2
1547 where asg2.assignment_id = p_assignment_id
1548 and pog2.source_id = asg2.assignment_id
1549 and pog2.source_type = 'PAF'
1550 and pog.parent_object_group_id = pog2.parent_object_group_id)
1551 AND past.per_system_status = 'ACTIVE_ASSIGN'
1552 AND asg.assignment_status_type_id = past.assignment_status_type_id
1553 AND asg.effective_start_date <= p_period_end_date
1554 AND NVL(asg.effective_end_date,p_period_end_date) >= p_period_start_date
1555 AND pog.source_id = asg.assignment_id
1556 AND pog.source_type = 'PAF';
1557
1558
1559 l_proration_start_date DATE;
1560 l_proration_end_date DATE;
1561 other_asg_start_date DATE;
1562 other_asg_end_date DATE;
1563 l_other_asg_start_date DATE;
1564 l_other_asg_end_date DATE;
1565 proration_days NUMBER := 0;
1566 overlap_with_main VARCHAR2(10);
1567 other_asg_no NUMBER := 0;
1568 l_other_asg1_start_date DATE;
1569 l_other_asg1_end_date DATE;
1570 l_other_asg2_start_date DATE;
1571 l_other_asg2_end_date DATE;
1572
1573 l_per_start_date DATE;
1574 l_per_end_date DATE;
1575
1576 --6887820
1577 l_element_type_id pay_element_types_f.element_type_id%TYPE;
1578 l_input_value_id pay_input_values_f.input_value_id%TYPE;
1579 l_prorate_flag pay_element_entry_values_f.screen_entry_value%TYPE;
1580
1581 --6887820
1582 CURSOR ele_typ_id
1583 IS
1584 select element_type_id
1585 from pay_element_types_f
1586 where
1587 element_name = 'NL Tax and SI Proration Indicator'
1588 and p_proration_start_date between effective_start_date and effective_end_date
1589 and legislation_code = 'NL';
1590
1591 CURSOR inp_val_id(p_element_type_id NUMBER)
1592 IS
1593 select input_value_id from pay_input_values_f
1594 where element_type_id = p_element_type_id
1595 and p_proration_start_date between effective_start_date and effective_end_date
1596 and legislation_code = 'NL';
1597
1598 /* Cursor to get the override value of proration flag from
1599 seeded element "NL Tax and SI Proration Indicator" */
1600
1601 CURSOR part_time_prorate_flag
1602 IS
1603 SELECT EEV.screen_entry_value
1604 FROM
1605 pay_element_entry_values_f EEV,
1606 pay_element_entries_f EE,
1607 pay_link_input_values_f LIV,
1608 pay_input_values_f INPUTV
1609 WHERE INPUTV.input_value_id = l_input_value_id
1610 AND p_proration_start_date BETWEEN INPUTV.effective_start_date AND INPUTV.effective_end_date
1611 AND INPUTV.element_type_id = l_element_type_id
1612 AND LIV.input_value_id = INPUTV.input_value_id
1613 AND p_proration_start_date BETWEEN LIV.effective_start_date AND LIV.effective_end_date
1614 AND EEV.input_value_id = INPUTV.input_value_id
1615 AND EEV.element_entry_id = EE.element_entry_id
1616 AND EEV.effective_start_date = EE.effective_start_date
1617 AND EEV.effective_end_date = EE.effective_end_date
1618 AND EE.element_link_id = LIV.element_link_id
1619 AND EE.assignment_id = p_assignment_id
1620 AND p_proration_start_date BETWEEN EE.effective_start_date AND EE.effective_end_date
1621 AND nvl(EE.ENTRY_TYPE, 'E') = 'E';
1622 --6887820
1623
1624
1625 BEGIN
1626 --6887820
1627 OPEN ele_typ_id;
1628 FETCH ele_typ_id into l_element_type_id;
1629 CLOSE ele_typ_id;
1630
1631 OPEN inp_val_id(l_element_type_id);
1632 FETCH inp_val_id INTO l_input_value_id;
1633 CLOSE inp_val_id;
1634
1635 OPEN part_time_prorate_flag;
1636 FETCH part_time_prorate_flag INTO l_prorate_flag;
1637 CLOSE part_time_prorate_flag;
1638
1639 --6887820 % if seeded element exists with assignment
1640 IF l_prorate_flag = 'N' THEN
1641 l_proration_start_date := p_period_start_date;
1642 l_proration_end_date := p_period_end_date;
1643 --6887820
1644 ELSE
1645 l_proration_start_date := p_proration_start_date;
1646 l_proration_end_date := p_proration_end_date;
1647 END IF;
1648
1649 /* loop for other assignments for the employee */
1650 FOR c1rec IN csr_any_other_asg LOOP
1651 OPEN csr_asg_dates(c1rec.asgid);
1652 FETCH csr_asg_dates INTO l_other_asg_start_date, l_other_asg_end_date;
1653 CLOSE csr_asg_dates;
1654
1655 /* If other assignment of the employee is active in the payroll period calculate working days using daily table */
1656 IF l_other_asg_start_date IS NOT NULL AND l_other_asg_end_date IS NOT NULL THEN
1657 other_asg_no := other_asg_no + 1;
1658 other_asg_start_date := GREATEST(p_period_start_date, l_other_asg_start_date);
1659 other_asg_end_date := LEAST(p_period_end_date, l_other_asg_end_date);
1660 IF other_asg_no = 1 THEN
1661 l_other_asg1_start_date := other_asg_start_date;
1662 l_other_asg1_end_date := other_asg_end_date;
1663 END IF;
1664 IF other_asg_no = 2 THEN
1665 l_other_asg2_start_date := other_asg_start_date;
1666 l_other_asg2_end_date := other_asg_end_date;
1667 END IF;
1668 IF other_asg_start_date < l_proration_start_date THEN
1669 IF other_asg_end_date < l_proration_start_date - 1 THEN
1670 IF other_asg_no = 1 THEN
1671 overlap_with_main := 'N';
1672 END IF;
1673 proration_days := proration_days + Get_Week_Days (other_asg_start_date, other_asg_end_date);
1674 ELSE
1675 l_proration_start_date := other_asg_start_date;
1676 END IF;
1677 END IF;
1678
1679 IF other_asg_end_date > l_proration_end_date THEN
1680 IF other_asg_start_date > l_proration_end_date + 1 THEN
1681 IF other_asg_no = 1 THEN
1682 overlap_with_main := 'N';
1683 END IF;
1684 proration_days := proration_days + Get_Week_Days (other_asg_start_date, other_asg_end_date);
1685 ELSE
1686 l_proration_end_date := other_asg_end_date;
1687 END IF;
1688 END IF;
1689 END IF;
1690 END LOOP;
1691
1692 /* If there are one or more days in the payroll period having no active assignments, then use daily table only*/
1693 IF l_proration_start_date = p_period_start_date and l_proration_end_date = p_period_end_date and p_period_si_days > 0 THEN
1694 proration_days := p_period_si_days;
1695 RETURN proration_days;
1696 END IF;
1697 proration_days := proration_days + Get_Week_Days (l_proration_start_date, l_proration_end_date);
1698
1699 /* Remove overlaps between the first other assignment and second other assignment, if any */
1700 IF other_asg_no = 2 AND overlap_with_main = 'N' THEN
1701 IF l_other_asg2_start_date BETWEEN l_other_asg1_start_date AND l_other_asg1_end_date THEN
1702 proration_days := proration_days - Get_Week_Days (l_other_asg2_start_date, LEAST(l_other_asg2_end_date, l_other_asg1_end_date));
1703 ELSIF l_other_asg2_end_date BETWEEN l_other_asg1_start_date and l_other_asg1_end_date THEN
1704 proration_days := proration_days - Get_Week_Days (l_other_asg1_start_date, l_other_asg2_end_date);
1705 ELSIF l_other_asg1_start_date BETWEEN l_other_asg2_start_date AND l_other_asg2_end_date THEN
1706 proration_days := proration_days - Get_Week_Days (l_other_asg1_start_date, l_other_asg1_end_date);
1707 END IF;
1708 END IF;
1709
1710 /*For more than 3 active assignments, proration days are returned with assumption that person
1711 is active for every day in the payroll period. Otherwise user feeds the days manually */
1712 IF other_asg_no > 2 THEN
1713 OPEN csr_person_dates;
1714 FETCH csr_person_dates INTO l_per_start_date, l_per_end_date;
1715 CLOSE csr_person_dates;
1716 l_per_start_date := GREATEST(p_period_start_date, l_per_start_date);
1717 l_per_end_date := LEAST(p_period_end_date, l_per_end_date);
1718 IF l_per_start_date = p_period_start_date and l_per_end_date = p_period_end_date and p_period_si_days > 0 THEN
1719 proration_days := p_period_si_days;
1720 ELSE
1721 proration_days := Get_Week_Days (l_per_start_date, l_per_end_date);
1722 END IF;
1723 END IF;
1724 RETURN proration_days;
1725 END get_si_proration_days;
1726
1727 -----------------------------------------------------------------------------------------------
1728 -- Function : get_override_si_days
1729 -- To get the override for SI Days
1730 -- Assignment Id,Date Earned, Business Group Id and Assignment action Id.
1731 -----------------------------------------------------------------------------------------------
1732 FUNCTION get_override_si_days
1733 ( p_assignment_id IN NUMBER
1734 ,p_date_earned IN DATE
1735 ,p_business_group_id IN NUMBER
1736 ,p_assignment_action_id IN NUMBER
1737 ,p_payroll_action_id IN NUMBER
1738 )
1739 RETURN NUMBER IS
1740 l_override_si_days varchar2(35);
1741 l_inputs ff_exec.inputs_t;
1742 l_outputs ff_exec.outputs_t;
1743 l_formula_exists BOOLEAN := TRUE;
1744 l_formula_cached BOOLEAN := FALSE;
1745 l_formula_id ff_formulas_f.formula_id%TYPE;
1746 BEGIN
1747 g_si_days_formula_name := 'NL_OVERRIDE_SOCIAL_INSURANCE_DAYS';
1748 IF g_si_days_formula_exists = TRUE THEN
1749 IF g_si_days_formula_cached = FALSE THEN
1750 pay_nl_general.cache_formula('NL_OVERRIDE_SOCIAL_INSURANCE_DAYS',p_business_group_id,p_date_earned,l_formula_id,l_formula_exists,l_formula_cached);
1751 g_si_days_formula_exists:=l_formula_exists;
1752 g_si_days_formula_cached:=l_formula_cached;
1753 g_si_days_formula_id:=l_formula_id;
1754 END IF;
1755 --
1756 IF g_si_days_formula_exists = TRUE THEN
1757 --
1758 l_inputs(1).name := 'ASSIGNMENT_ID';
1759 l_inputs(1).value := p_assignment_id;
1760 l_inputs(2).name := 'DATE_EARNED';
1761 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
1762 l_inputs(3).name := 'BUSINESS_GROUP_ID';
1763 l_inputs(3).value := p_business_group_id;
1764 l_inputs(4).name := 'ASSIGNMENT_ACTION_ID';
1765 l_inputs(4).value := p_assignment_action_id;
1766 l_inputs(5).name := 'PAYROLL_ACTION_ID';
1767 l_inputs(5).value := p_payroll_action_id;
1768 l_inputs(6).name := 'BALANCE_DATE';
1769 l_inputs(6).value := fnd_date.date_to_canonical(p_date_earned);
1770 --
1771 l_outputs(1).name := 'OVERRIDE_SOCIAL_INSURANCE_DAYS';
1772 l_outputs(2).name := 'PRORATION_FLAG';
1773 l_outputs(3).name := 'OVERRIDE_SOCIAL_INSURANCE_DAYS_ZVW';
1774 --
1775 pay_nl_general.run_formula(p_formula_id => g_si_days_formula_id,
1776 p_effective_date => p_date_earned,
1777 p_formula_name => g_si_days_formula_name,
1778 p_inputs => l_inputs,
1779 p_outputs => l_outputs);
1780 --
1781 g_si_days := fnd_number.canonical_to_number(l_outputs(1).value);
1782 g_tax_proration_days := fnd_number.canonical_to_number(l_outputs(1).value);
1783 g_tax_proration_flag := l_outputs(2).value;
1784 g_si_days_zvw := fnd_number.canonical_to_number(l_outputs(3).value);
1785 ELSE
1786 g_si_days := '-1';
1787 g_tax_proration_days := '-1';
1788 g_tax_proration_flag := 'X';
1789 g_si_days_zvw := '-1';
1790 END IF;
1791 ELSIF g_si_days_formula_exists = FALSE THEN
1792 g_si_days := '-1';
1793 g_tax_proration_days := '-1';
1794 g_tax_proration_flag := 'X';
1795 g_si_days_zvw := '-1';
1796 END IF;
1797 RETURN fnd_number.canonical_to_number(g_si_days);
1798
1799 END get_override_si_days;
1800
1801
1802 -----------------------------------------------------------------------------------------------
1803 -- Function : get_override_si_days_zvw
1804 -- To get the override for SI Days ZVW
1805 -- Assignment Id,Date Earned, Business Group Id and Assignment action Id.
1806 -----------------------------------------------------------------------------------------------
1807 FUNCTION get_override_si_days_zvw
1808 ( p_assignment_id IN NUMBER
1809 ,p_date_earned IN DATE
1810 ,p_business_group_id IN NUMBER
1811 ,p_assignment_action_id IN NUMBER
1812 ,p_payroll_action_id IN NUMBER
1813 )
1814 RETURN NUMBER IS
1815 l_override_si_days varchar2(35);
1816 BEGIN
1817 IF g_assignment_id = p_assignment_id THEN
1818 RETURN fnd_number.canonical_to_number(g_si_days_zvw);
1819 ELSE
1820 l_override_si_days := get_override_si_days
1821 ( p_assignment_id
1822 ,p_date_earned
1823 ,p_business_group_id
1824 ,p_assignment_action_id
1825 ,p_payroll_action_id
1826 );
1827 g_assignment_id := p_assignment_id;
1828 RETURN fnd_number.canonical_to_number(g_si_days_zvw);
1829 END IF;
1830 END get_override_si_days_zvw;
1831
1832
1833 -----------------------------------------------------------------------------------------------
1834 -- Function : get_tax_proration_days
1835 -- To get the number of days for tax proration by executing user formula
1836 -- Assignment Id,Date Earned, Business Group Id and Assignment action Id.
1837 -----------------------------------------------------------------------------------------------
1838 Function get_tax_proration_days
1839 ( p_assignment_id IN NUMBER
1840 ,p_date_earned IN DATE
1841 ,p_assignment_action_id IN NUMBER
1842 ,p_payroll_action_id IN NUMBER
1843 ,p_business_group_id IN NUMBER
1844 )
1845 RETURN number IS
1846 l_override_si_days varchar2(35);
1847 BEGIN
1848 IF g_assignment_id = p_assignment_id THEN
1849 RETURN fnd_number.canonical_to_number(g_tax_proration_days);
1850 ELSE
1851 l_override_si_days := get_override_si_days
1852 ( p_assignment_id
1853 ,p_date_earned
1854 ,p_business_group_id
1855 ,p_assignment_action_id
1856 ,p_payroll_action_id
1857 );
1858 g_assignment_id := p_assignment_id;
1859 RETURN fnd_number.canonical_to_number(g_tax_proration_days);
1860 END IF;
1861 END get_tax_proration_days;
1862
1863 -----------------------------------------------------------------------------------------------
1864 -- Function : get_tax_proration_flag
1865 -- To return the flag to determine whether proration is required or not by executing user formula
1866 -- Assignment Id,Date Earned, Business Group Id and Assignment action Id.
1867 -----------------------------------------------------------------------------------------------
1868 Function get_tax_proration_flag
1869 ( p_assignment_id IN NUMBER
1870 ,p_date_earned IN DATE
1871 ,p_assignment_action_id IN NUMBER
1872 ,p_payroll_action_id IN NUMBER
1873 ,p_business_group_id IN NUMBER
1874 )
1875 RETURN varchar2 IS
1876 l_override_si_days varchar2(35);
1877
1878 l_element_type_id pay_element_types_f.element_type_id%TYPE;
1879 l_input_value_id pay_input_values_f.input_value_id%TYPE;
1880 l_prorate_flag pay_element_entry_values_f.screen_entry_value%TYPE;
1881
1882 --6887820
1883 CURSOR ele_typ_id
1884 IS
1885 select element_type_id
1886 from pay_element_types_f
1887 where
1888 element_name = 'NL Tax and SI Proration Indicator'
1889 and p_date_earned between effective_start_date and effective_end_date
1890 and legislation_code = 'NL';
1891
1892 CURSOR inp_val_id(p_element_type_id NUMBER)
1893 IS
1894 select input_value_id from pay_input_values_f
1895 where element_type_id = p_element_type_id
1896 and p_date_earned between effective_start_date and effective_end_date
1897 and legislation_code = 'NL';
1898
1899 /* Cursor to get the override value of proration flag from
1900 seeded element "NL Tax and SI Proration Indicator" */
1901
1902 CURSOR part_time_prorate_flag
1903 IS
1904 SELECT EEV.screen_entry_value
1905 FROM
1906 pay_element_entry_values_f EEV,
1907 pay_element_entries_f EE,
1908 pay_link_input_values_f LIV,
1909 pay_input_values_f INPUTV
1910 WHERE INPUTV.input_value_id = l_input_value_id
1911 AND p_date_earned BETWEEN INPUTV.effective_start_date AND INPUTV.effective_end_date
1912 AND INPUTV.element_type_id = l_element_type_id
1913 AND LIV.input_value_id = INPUTV.input_value_id
1914 AND p_date_earned BETWEEN LIV.effective_start_date AND LIV.effective_end_date
1915 AND EEV.input_value_id = INPUTV.input_value_id
1916 AND EEV.element_entry_id = EE.element_entry_id
1917 AND EEV.effective_start_date = EE.effective_start_date
1918 AND EEV.effective_end_date = EE.effective_end_date
1919 AND EE.element_link_id = LIV.element_link_id
1920 AND EE.assignment_id = p_assignment_id
1921 AND p_date_earned BETWEEN EE.effective_start_date AND EE.effective_end_date
1922 AND nvl(EE.ENTRY_TYPE, 'E') = 'E';
1923 --6887820
1924 BEGIN
1925 --6887820
1926 OPEN ele_typ_id;
1927 FETCH ele_typ_id into l_element_type_id;
1928 CLOSE ele_typ_id;
1929
1930 OPEN inp_val_id(l_element_type_id);
1931 FETCH inp_val_id INTO l_input_value_id;
1932 CLOSE inp_val_id;
1933
1934 OPEN part_time_prorate_flag;
1935 FETCH part_time_prorate_flag INTO l_prorate_flag;
1936 CLOSE part_time_prorate_flag;
1937 --6887820
1938
1939 IF g_assignment_id = p_assignment_id THEN
1940 --6887820
1941 IF g_tax_proration_flag = 'X' THEN
1942 g_tax_proration_flag := NVL(l_prorate_flag,g_tax_proration_flag);
1943 END IF;
1944 --6887820
1945 RETURN g_tax_proration_flag;
1946 ELSE
1947 l_override_si_days := get_override_si_days
1948 ( p_assignment_id
1949 ,p_date_earned
1950 ,p_business_group_id
1951 ,p_assignment_action_id
1952 ,p_payroll_action_id
1953 );
1954 g_assignment_id := p_assignment_id;
1955 --6887820
1956 IF g_tax_proration_flag = 'X' THEN
1957 g_tax_proration_flag := NVL(l_prorate_flag,g_tax_proration_flag);
1958 END IF;
1959 --6887820
1960 RETURN g_tax_proration_flag;
1961 END IF;
1962 END get_tax_proration_flag;
1963
1964 ---------------------------------------------------------------------------------------
1965 -- Function : get_tax_proration_cal_days
1966 -- To return number of Tax Days for proration based on calender days
1967 ---------------------------------------------------------------------------------------
1968
1969 FUNCTION get_tax_proration_cal_days
1970 ( p_assignment_id IN NUMBER
1971 ,p_period_start_date IN DATE
1972 ,p_period_end_date IN DATE
1973 ,p_proration_start_date IN DATE
1974 ,p_proration_end_date IN DATE
1975 )
1976 RETURN NUMBER IS
1977 CURSOR csr_asg_dates (p_other_assignment_id NUMBER) IS
1978 SELECT MIN(asg.effective_start_date) asg_start_date
1979 ,MAX(asg.effective_end_date) asg_end_date
1980 FROM PER_ASSIGNMENTS_F asg
1981 ,PER_ASSIGNMENT_STATUS_TYPES past
1982 WHERE asg.assignment_id = p_other_assignment_id
1983 AND past.per_system_status = 'ACTIVE_ASSIGN'
1984 AND asg.assignment_status_type_id = past.assignment_status_type_id
1985 AND asg.effective_start_date <= p_period_end_date
1986 AND NVL(asg.effective_end_date,p_period_end_date) >= p_period_start_date;
1987
1988 CURSOR csr_any_other_asg IS
1989 SELECT DISTINCT asg1.assignment_id asgid
1990 FROM per_assignments_f asg1
1991 ,per_assignments_f asg2
1992 ,pay_object_groups pog1
1993 ,pay_object_groups pog2
1994 WHERE asg1.person_id = asg2.person_id
1995 AND asg2.assignment_id = p_assignment_id
1996 AND asg1.assignment_id <> p_assignment_id
1997 AND pog1.source_id = asg1.assignment_id
1998 AND pog1.source_type = 'PAF'
1999 AND pog2.source_id = asg2.assignment_id
2000 AND pog2.source_type = 'PAF'
2001 AND pog1.parent_object_group_id = pog2.parent_object_group_id;
2002
2003
2004 CURSOR csr_person_dates IS
2005 SELECT MIN(asg.effective_start_date) per_start_date
2006 ,MAX(asg.effective_end_date) per_end_date
2007 FROM PER_ASSIGNMENTS_F asg,
2008 PER_ASSIGNMENT_STATUS_TYPES past,
2009 PAY_OBJECT_GROUPS pog
2010 WHERE asg.person_id in
2011 (select asg2.person_id from PER_ASSIGNMENTS_F asg2, PAY_OBJECT_GROUPS pog2
2012 where asg2.assignment_id = p_assignment_id
2013 and pog2.source_id = asg2.assignment_id
2014 and pog2.source_type = 'PAF'
2015 and pog.parent_object_group_id = pog2.parent_object_group_id)
2016 AND past.per_system_status = 'ACTIVE_ASSIGN'
2017 AND asg.assignment_status_type_id = past.assignment_status_type_id
2018 AND asg.effective_start_date <= p_period_end_date
2019 AND NVL(asg.effective_end_date,p_period_end_date) >= p_period_start_date
2020 AND pog.source_id = asg.assignment_id
2021 AND pog.source_type = 'PAF';
2022
2023
2024 l_proration_start_date DATE;
2025 l_proration_end_date DATE;
2026 other_asg_start_date DATE;
2027 other_asg_end_date DATE;
2028 l_other_asg_start_date DATE;
2029 l_other_asg_end_date DATE;
2030 proration_days NUMBER := 0;
2031 overlap_with_main VARCHAR2(10);
2032 other_asg_no NUMBER := 0;
2033 l_other_asg1_start_date DATE;
2034 l_other_asg1_end_date DATE;
2035 l_other_asg2_start_date DATE;
2036 l_other_asg2_end_date DATE;
2037
2038 l_per_start_date DATE;
2039 l_per_end_date DATE;
2040
2041
2042 BEGIN
2043
2044 l_proration_start_date := p_proration_start_date;
2045 l_proration_end_date := p_proration_end_date;
2046
2047 /* loop for other assignments for the employee */
2048 FOR c1rec IN csr_any_other_asg LOOP
2049 OPEN csr_asg_dates(c1rec.asgid);
2050 FETCH csr_asg_dates INTO l_other_asg_start_date, l_other_asg_end_date;
2051 CLOSE csr_asg_dates;
2052
2053 /* If other assignment of the employee is active in the payroll period calculate working days using daily table */
2054 IF l_other_asg_start_date IS NOT NULL AND l_other_asg_end_date IS NOT NULL THEN
2055 other_asg_no := other_asg_no + 1;
2056 other_asg_start_date := GREATEST(p_period_start_date, l_other_asg_start_date);
2057 other_asg_end_date := LEAST(p_period_end_date, l_other_asg_end_date);
2058 IF other_asg_no = 1 THEN
2059 l_other_asg1_start_date := other_asg_start_date;
2060 l_other_asg1_end_date := other_asg_end_date;
2061 END IF;
2062 IF other_asg_no = 2 THEN
2063 l_other_asg2_start_date := other_asg_start_date;
2064 l_other_asg2_end_date := other_asg_end_date;
2065 END IF;
2066 IF other_asg_start_date < l_proration_start_date THEN
2067 IF other_asg_end_date < l_proration_start_date - 1 THEN
2068 IF other_asg_no = 1 THEN
2069 overlap_with_main := 'N';
2070 END IF;
2071 proration_days := proration_days + (other_asg_end_date - other_asg_start_date) + 1;
2072 ELSE
2073 l_proration_start_date := other_asg_start_date;
2074 END IF;
2075 END IF;
2076
2077 IF other_asg_end_date > l_proration_end_date THEN
2078 IF other_asg_start_date > l_proration_end_date + 1 THEN
2079 IF other_asg_no = 1 THEN
2080 overlap_with_main := 'N';
2081 END IF;
2082 proration_days := proration_days + (other_asg_end_date - other_asg_start_date) + 1;
2083 ELSE
2084 l_proration_end_date := other_asg_end_date;
2085 END IF;
2086 END IF;
2087 END IF;
2088 END LOOP;
2089
2090 /* If there are one or more days in the payroll period having no active assignments, then use daily table only*/
2091 IF l_proration_start_date = p_period_start_date and l_proration_end_date = p_period_end_date THEN
2092 proration_days := (p_period_end_date - p_period_start_date) + 1;
2093 RETURN proration_days;
2094 END IF;
2095 proration_days := proration_days + (l_proration_end_date - l_proration_start_date) + 1;
2096
2097 /* Remove overlaps between the first other assignment and second other assignment, if any */
2098 IF other_asg_no = 2 AND overlap_with_main = 'N' THEN
2099 IF l_other_asg2_start_date BETWEEN l_other_asg1_start_date AND l_other_asg1_end_date THEN
2100 proration_days := proration_days - (LEAST(l_other_asg2_end_date, l_other_asg1_end_date) - l_other_asg2_start_date);
2101 ELSIF l_other_asg2_end_date BETWEEN l_other_asg1_start_date and l_other_asg1_end_date THEN
2102 proration_days := proration_days - (l_other_asg2_end_date - l_other_asg1_start_date);
2103 ELSIF l_other_asg1_start_date BETWEEN l_other_asg2_start_date AND l_other_asg2_end_date THEN
2104 proration_days := proration_days - (l_other_asg1_end_date - l_other_asg1_start_date);
2105 END IF;
2106 END IF;
2107
2108 /*For more than 3 active assignments, proration days are returned with assumption that person
2109 is active for every day in the payroll period. Otherwise user feeds the days manually */
2110 IF other_asg_no > 2 THEN
2111 OPEN csr_person_dates;
2112 FETCH csr_person_dates INTO l_per_start_date, l_per_end_date;
2113 CLOSE csr_person_dates;
2114 l_per_start_date := GREATEST(p_period_start_date, l_per_start_date);
2115 l_per_end_date := LEAST(p_period_end_date, l_per_end_date);
2116 proration_days := (l_per_end_date - l_per_start_date) + 1;
2117 END IF;
2118 RETURN proration_days;
2119 END get_tax_proration_cal_days;
2120
2121 END pay_nl_si_pkg;
[Home] [Help]