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