1 PACKAGE BODY PQH_LENGTH_OF_SERVICE_PKG AS
2 /* $Header: pqlosclc.pkb 120.0 2005/05/29 02:11:12 appldev noship $ */
3
4 g_end_of_time DATE := TO_DATE('31/12/4712','DD/MM/RRRR');
5 g_package varchar2(30) := 'PQH_LENGTH_OF_SERVICE_PKG.';
6 g_emp_type varchar2(30);
7 g_determination_date DATE;
8 -- -----------------------------------------------------------------------*
9 -- FUNCTION get_effective_date
10 -- This function returns the session date for the Current Session
11 -- -----------------------------------------------------------------------*
12
13 FUNCTION get_effective_date RETURN DATE IS
14 l_proc varchar2(60) := g_package||'get_effective_date';
15 l_date DATE;
16 BEGIN
17 SELECT effective_date
18 INTO l_date
19 FROM fnd_sessions
20 WHERE session_id = USERENV('sessionid');
21
22 RETURN l_date;
23
24 EXCEPTION
25 When No_Data_Found Then
26 l_date := Sysdate;
27 RETURN l_date;
28 When Others THEN
29 hr_utility.set_location('Erroring out from '||l_proc,5);
30 RAISE_Application_Error(-20001,SQLERRM);
31 END get_effective_date;
32
33
34 -- -----------------------------------------------------------------------*
35 -- PROCEDURE bg_normal_hours
36 -- This procedure gets the Normal Workd Day Hours, Normal Hours and its Frequency defined
37 -- at the Business Group level.
38 -- -----------------------------------------------------------------------*
39
40 PROCEDURE bg_normal_hours (p_bg_id IN per_all_organization_units.organization_id%TYPE,
41 p_bg_normal_day_hours OUT NOCOPY NUMBER,
42 p_bg_normal_hours OUT NOCOPY NUMBER,
43 p_bg_frequency OUT NOCOPY VARCHAR2)
44 IS
45 l_proc varchar2(60) := g_package||'bg_normal_hours';
46 l_bg_start_time VARCHAR2(150);
47 l_bg_end_time VARCHAR2(150);
48
49 CURSOR Csr_bg_norm_hours IS
50 SELECT org_information1, -- normal start time
51 org_information2, -- normal end time
52 NVL(org_information3,0), -- normal hours
53 org_information4 -- frequency
54 FROM hr_organization_information
55 WHERE organization_id = p_bg_id
56 AND org_information_context = 'Work Day Information';
57 BEGIN
58 OPEN Csr_bg_norm_hours;
59 FETCH Csr_bg_norm_hours INTO l_bg_start_time, l_bg_end_time,p_bg_normal_hours, p_bg_frequency;
60 CLOSE Csr_bg_norm_hours;
61 IF p_bg_normal_hours IS NULL THEN -- if normal working hours not defined then default it to 40 hours per week
62 p_bg_normal_hours := 35;
63 p_bg_frequency := 'W';
64 END IF;
65 IF l_bg_start_time IS NOT NULL AND l_bg_end_time IS NOT NULL THEN
66 p_bg_normal_day_hours := TO_DATE(l_bg_end_time,'HH24:MI')-TO_DATE(l_bg_start_time,'HH24:MI');--normal work hours per a day
67 ELSE
68 p_bg_normal_day_hours := 7; -- if not defined, default it to 7 hours per day
69 END IF;
70 EXCEPTION
71 When Others THEN
72 p_bg_normal_day_hours := null;
73 p_bg_normal_hours := null;
74 p_bg_frequency := null;
75 hr_utility.set_location('Erroring out from '||l_proc,5);
76 RAISE_Application_Error(-20001,SQLERRM);
77 END;
78
79
80 FUNCTION get_working_time_ratio( p_bg_normal_day IN NUMBER,
81 p_bg_hours IN NUMBER,
82 p_bg_frequency IN VARCHAR2,
83 p_asg_hours IN NUMBER,
84 p_asg_frequency IN VARCHAR2)
85 RETURN NUMBER IS
86 l_proc varchar2(60) := g_package||'get_working_time_ratio';
87 l_working_time_ratio NUMBER(4,2) := 1;
88 l_asg_hours NUMBER(22,3) := 0;
89 BEGIN
90 hr_utility.set_location('Entering '||l_proc,1);
91 IF p_bg_frequency = 'D' THEN
92 IF p_asg_frequency = 'W' THEN
93 l_asg_hours := p_asg_hours/5; --(one week is taken as 5 days)
94 ELSIF p_asg_frequency = 'M' THEN
95 l_asg_hours := p_asg_hours/20; --(one month is taken as 4 week)
96 ELSIF p_asg_frequency = 'Y' THEN
97 l_asg_hours := p_asg_hours/240; --(one year is taken as 12 months)
98 END IF;
99 ELSIF p_bg_frequency = 'W' THEN
100 IF p_asg_frequency = 'D' THEN
101 l_asg_hours := p_asg_hours * 5;
102 ELSIF p_asg_frequency = 'M' THEN
103 l_asg_hours := p_asg_hours/4;
104 ELSIF p_asg_hours = 'Y' THEN
105 l_asg_hours := p_asg_hours/48;
106 END IF;
107 ELSIF p_bg_frequency = 'M' THEN
108 IF p_asg_frequency = 'D' THEN
109 l_asg_hours := p_asg_hours*20;
110 ELSIF p_asg_frequency = 'W' THEN
111 l_asg_hours := p_asg_hours*4;
112 ELSIF p_asg_frequency = 'Y' THEN
113 l_asg_hours := p_asg_hours/12;
114 END IF;
115 ELSIF p_bg_frequency = 'Y' THEN
116 IF p_asg_frequency = 'D' THEN
117 l_asg_hours := p_asg_hours*24;
118 ELSIF p_asg_frequency = 'W' THEN
119 l_asg_hours := p_asg_hours*48;
120 ELSIF p_asg_frequency = 'M' THEN
121 l_asg_hours := p_asg_hours*12;
122 END IF;
123 END IF;
124 -- calculate the ratio of Assignment hours to the Business group hours (for proportional to Parttime hours)
125 l_working_time_ratio := l_asg_hours/p_bg_hours;
126
127 RETURN l_working_time_ratio;
128 EXCEPTION
129 When Others THEN
130 hr_utility.set_location('Erroring out from '||l_proc,5);
131 RAISE_Application_Error(-20001,SQLERRM);
132
133 END get_working_time_ratio;
134
135 -- -----------------------------------------------------------------------*
136 -- FUNCTION get_employee_type
137 -- This function returns the agent type (as held in PER_INFORMATION15 of PER_ALL_PEOPLE_F)
138 -- for the person
139 -- -----------------------------------------------------------------------*
140
141 FUNCTION get_employee_type (p_person_id IN per_all_people_f.person_id%TYPE,
142 p_determination_date IN DATE)
143 RETURN VARCHAR2 IS
144 l_emp_type per_assignments_f.employee_category%TYPE;
145 l_proc varchar2(60) := g_package||'get_emp_type';
146 l_leg_code varchar2(30) := 'x';
147 CURSOR csr_emp_type IS
148 SELECT per.per_information15
149 FROM per_all_people_f per
150 WHERE per.person_id = p_person_id
151 AND p_determination_date between per.effective_start_date and per.effective_end_date;
152
153 CURSOR csr_leg_code IS
154 SELECT hr_api.return_legislation_code(per.business_group_id)
155 FROM per_all_people_f per
156 WHERE per.person_id = p_person_id
157 AND trunc(sysdate) between per.effective_start_date and per.effective_end_date;
158
159 CURSOR csr_emp_catg IS
160 SELECT employee_category
161 FROM per_all_assignments_f
162 WHERE person_id = p_person_id
163 AND p_determination_date between effective_start_date and effective_end_date;
164
165 BEGIN
166
167 OPEN csr_leg_code;
168 FETCH csr_leg_code INTO l_leg_code;
169 CLOSE csr_leg_code;
170
171 IF l_leg_code = 'DE' THEN
172 OPEN csr_emp_catg;
173 FETCH csr_emp_catg INTO l_emp_type;
174 CLOSE csr_emp_catg;
175 RETURN l_emp_type;
176 END IF;
177
178 OPEN csr_emp_type;
179
180 FETCH csr_emp_type INTO l_emp_type;
181
182 CLOSE csr_emp_type;
183
184 IF l_emp_type IS NULL THEN
185 hr_utility.set_location ('emp_type is NULL',10);
186 RETURN TO_CHAR(NULL);
187 END IF;
188
189 hr_utility.set_location ('emp_type is '||l_emp_type||' '||l_proc,10);
190 RETURN l_emp_type;
191 EXCEPTION
192 When Others THEN
193 hr_utility.set_location('Erroring out from '||l_proc,5);
194 RAISE_Application_Error(-20001,SQLERRM);
195 END get_employee_type;
196
197 -- -----------------------------------------------------------------------*
198 -- FUNCTION get_absent_period
199 -- This function returns the absence duration to be deducted from the
200 -- Length of service calculations after evaluating the relevant entitlements.
201 -- -----------------------------------------------------------------------*
202
203
204 FUNCTION get_absent_period (p_bg_id IN per_all_organization_units.organization_id%TYPE,
205 p_person_id IN per_all_people_f.person_id%TYPE,
206 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
207 p_los_type IN hr_lookups.lookup_code%TYPE,
208 p_start_date IN DATE,
209 p_end_date IN DATE
210 )
211 RETURN NUMBER IS
212
213 l_absence_duration NUMBER(22,3) := 0;
214 l_entitlement NUMBER := 0;
215 l_emp_type VARCHAR2(30);
216 l_abs_catg VARCHAR2(30);
217 l_net_absence NUMBER(22,3) := 0;
218 l_abs_hours_to_days NUMBER(22,3) := 0;
219 l_proc varchar2(60) := g_package||'get_absent_period';
220 -- BG Normaly day info for converting the Absent hours to days
221 l_bg_normal_day_hours NUMBER(22,3);
222 l_bg_normal_hours NUMBER(22,3);
223 l_bg_normal_frequency VARCHAR2(30);
224
225 -- Absence details for the person for the given period
226 CURSOR Csr_absence_details IS
227 SELECT NVL(aat.absence_category,'*') ABSENCE_CATEGORY,
228 NVL(paa.absence_days,0) ABSENCE_DAYS,
229 NVL(paa.absence_hours,0) ABSENCE_HOURS,
230 paa.date_start,
231 paa.date_end
232 FROM per_absence_attendances paa,
233 per_absence_attendance_types aat
234 WHERE paa.business_group_id = p_bg_id
235 AND paa.person_id = p_person_id
236 AND ( paa.date_start BETWEEN p_start_date AND p_end_date
237 OR paa.date_end BETWEEN p_start_date AND p_end_date)
238 AND paa.absence_attendance_type_id = aat.absence_attendance_type_id;
239
240 -- entitlement for the employee category, for the given LOS type, for the give absence type
241 CURSOR Csr_absence_entitlements (p_abs_catg VARCHAR2, p_emp_type VARCHAR2) IS
242 SELECT NVL(entitlement_value,0)
243 FROM pqh_situations
244 WHERE business_group_id = p_bg_id
245 AND situation_type = 'ABSENCE'
246 AND length_of_service = p_los_type
247 AND situation = p_abs_catg
248 AND employee_type = p_emp_type
249 AND g_determination_date BETWEEN effective_start_date AND NVL(effective_end_date,g_end_of_time)
250 AND entitlement_flag = 'Y';
251 BEGIN
252 hr_utility.set_location('Entering '||l_proc,1);
253 -- get the BG normal day hours for converting absence hours to days
254 bg_normal_hours(p_bg_id => p_bg_id,
255 p_bg_normal_day_hours => l_bg_normal_day_hours,
256 p_bg_normal_hours => l_bg_normal_hours,
257 p_bg_frequency => l_bg_normal_frequency);
258 l_emp_type := g_emp_type;
259
260 IF l_emp_type IS NOT NULL THEN
261
262 FOR lr_absence IN Csr_absence_details
263 LOOP
264 l_absence_duration := 0;
265 l_entitlement := 0;
266 l_abs_hours_to_days := lr_absence.absence_hours/l_bg_normal_day_hours;-- take into account the absent hours
267 l_absence_duration := lr_absence.absence_days + l_abs_hours_to_days;
268 l_abs_catg := lr_absence.absence_category;
269 OPEN Csr_absence_entitlements(l_abs_catg,l_emp_type);
270 FETCH Csr_absence_entitlements INTO l_entitlement;
271 CLOSE Csr_absence_entitlements;
272 l_net_absence := l_net_absence + (l_absence_duration * (1-(NVL(l_entitlement,0)/100)) );
273 END LOOP;
274 END IF;
275 hr_utility.set_location(l_proc||' Net Absence Duration '||l_net_absence,2);
276 RETURN l_net_absence;
277 EXCEPTION
278 When Others THEN
279 hr_utility.set_location('Erroring out from '||l_proc,5);
280 RAISE_Application_Error(-20001,SQLERRM);
281 END get_absent_period;
282
283 -- -----------------------------------------------------------------------*
284 -- FUNCTION get_parttime_entitlement
285 -- This function returns the parttime entitlement defined for the
286 -- the assignment.
287 -- -----------------------------------------------------------------------*
288
289 FUNCTION get_parttime_entitlement(p_person_id IN per_all_assignments_f.person_id%TYPE,
290 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
291 p_bg_id IN per_all_organization_units.organization_id%TYPE,
292 p_los_type IN hr_lookups.lookup_code%TYPE,
293 p_start_date IN DATE,
294 p_end_date IN DATE)
295
296 RETURN NUMBER IS
297 l_emp_type per_assignments_f.employee_category%TYPE;
298 l_emp_catg per_assignments_f.employment_category%TYPE;
299 l_entitlement_flag VARCHAR2(30);
300 l_work_proportional VARCHAR2(30) ;
301 l_parttime_entitlement NUMBER(22,3) := 0;
302 l_bg_normal_day NUMBER(22,3) := 0;
303 l_bg_hours NUMBER(22,3) := 0;
304 l_bg_frequency VARCHAR2(30);
305 l_temp_duration NUMBER(22,3) := 0;
306 l_asg_duration NUMBER(22,3) := 0;
307 l_proc varchar2(60) := g_package||'get_parttime_entitlement';
308
309 CURSOR Csr_employment_category IS
310 SELECT NVL(asg.employment_category,'$#') EMPLOYMENT_CATEGORY,
311 asg.effective_start_date,
312 asg.effective_end_date,
313 NVL(asg.normal_hours,0) NORMAL_HOURS,
314 asg.frequency
315 FROM per_all_assignments_f asg
316 WHERE asg.assignment_id = p_assignment_id
317 AND (p_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
318 OR p_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date);
319
320
321 -- entitlement for the employee type, for the given LOS type, for the given Employment Category
322 CURSOR Csr_parttime_entitlements IS
323 SELECT NVL(worktime_proportional,'N'),
324 NVL(entitlement_value,0)
325 FROM pqh_situations
326 WHERE business_group_id = p_bg_id
327 AND situation_type = 'PARTTIME'
328 AND length_of_service = p_los_type
329 -- AND NVL(situation,'PT') = l_emp_catg
330 AND employee_type = l_emp_type
331 AND entitlement_flag = 'Y'
332 AND g_determination_date BETWEEN effective_start_date AND NVL(effective_end_date,g_end_of_time);
333 BEGIN
334 hr_utility.set_location('Entering '||l_proc,1);
335 bg_normal_hours(p_bg_id, l_bg_normal_day, l_bg_hours,l_bg_frequency);
336 hr_utility.set_location(l_proc||' BG Normal Hours '||l_bg_hours||'Determination Date'||to_char(p_end_date,'dd-mm-RRRR')||l_bg_frequency,2);
337 hr_utility.set_location(l_proc||'Determination Date'||to_char(p_end_date,'dd-mm-RRRR')||l_bg_frequency,2);
338 l_emp_type := g_emp_type;
339
340 hr_utility.set_location(l_proc||'Emp Type '||l_emp_type,2);
341 hr_utility.set_location(l_proc||'Assignment ID '||p_assignment_id,2);
342 IF l_emp_type IS NOT NULL THEN
343 FOR lr_employment_catg IN Csr_employment_category
344 LOOP
345 l_emp_catg := lr_employment_catg.employment_category;
346 IF lr_employment_catg.effective_end_date > p_end_date THEN
347 lr_employment_catg.effective_end_date := p_end_date;
348 END IF;
349 IF lr_employment_catg.effective_start_date < p_start_date THEN
350 lr_employment_catg.effective_start_date := p_start_date;
351 END IF;
352
353 hr_utility.set_location(l_proc||' period '||to_char(lr_employment_catg.effective_start_date,'dd-mm-RRRR')||to_char(lr_employment_catg.effective_end_date,'dd-mm-RRRR'),3);
354 l_temp_duration := lr_employment_catg.effective_end_date - lr_employment_catg.effective_start_date+1;
355 hr_utility.set_location(l_proc||' temp duration'||l_temp_duration,3);
356 IF l_emp_catg IN ('PT','PR') THEN
357
358 hr_utility.set_location(l_proc,3);
359 OPEN Csr_parttime_entitlements;
360 FETCH Csr_parttime_entitlements INTO l_work_proportional,
361 l_parttime_entitlement;
362 IF csr_parttime_entitlements%FOUND THEN
363 IF l_work_proportional = 'Y' THEN
364 IF lr_employment_catg.frequency = l_bg_frequency THEN
365 l_parttime_entitlement := lr_employment_catg.normal_hours/l_bg_hours;
366 ELSE
367 l_parttime_entitlement := get_working_time_ratio( p_bg_normal_day => l_bg_normal_day,
368 p_bg_hours => l_bg_hours,
369 p_bg_frequency => l_bg_frequency,
370 p_asg_hours => lr_employment_catg.normal_hours,
371 p_asg_frequency => lr_employment_catg.frequency);
372 END IF;
373 END IF;
374 l_temp_duration := l_temp_duration * NVL(l_parttime_entitlement,0)/100;
375 ELSE
376 l_temp_duration := 0; -- don't count the entire duration if not entitled.
377 END IF;
378
379 CLOSE Csr_parttime_entitlements;
380
381 END IF;
382 hr_utility.set_location(l_proc||' l_asg_duration in loop '||l_asg_duration,2);
383 l_asg_duration := l_asg_duration + l_temp_duration;
384 END LOOP;
385 ELSE
386 l_asg_duration := p_end_date - p_start_date;
387 END IF;
388 hr_utility.set_location(l_proc||' returning duration '||l_asg_duration,2);
389 RETURN l_asg_duration;
390 EXCEPTION
391 When Others THEN
392 hr_utility.set_location('Erroring out from '||l_proc,5);
393 RAISE_Application_Error(-20001,SQLERRM);
394 END get_parttime_entitlement;
395
396 -- -----------------------------------------------------------------------*
397 -- FUNCTION get_previous_employment
398 -- This function returns the previous employment duration to be taken into
399 -- while calculating the LOS.
400 -- -----------------------------------------------------------------------*
401
402 FUNCTION get_previous_employment(p_person_id IN per_all_people_f.person_id%TYPE,
403 p_assignment_id IN per_assignments_f.assignment_id%TYPE,
404 p_start_date IN DATE,
405 p_end_date IN DATE) RETURN NUMBER IS
406 l_prev_emp_period NUMBER(22,3) := 0;
407 l_proc varchar2(60) := g_package||'get_previous_employment';
408
409 BEGIN
410 hr_utility.set_location('Entering '||l_proc,1);
411 RETURN l_prev_emp_period;
412 EXCEPTION
413 When Others THEN
414 hr_utility.set_location('Erroring out from '||l_proc,5);
415 RAISE_Application_Error(-20001,SQLERRM);
416 END;
417
418 -- -----------------------------------------------------------------------*
419 -- FUNCTION get_previous_employment
420 -- This function returns the length of previous employment to be taken into
421 -- while calculating the LOS for French Public Sector.
422 -- -----------------------------------------------------------------------*
423
424 FUNCTION get_length_previous_employment(p_person_id IN per_all_people_f.person_id%TYPE,
425 p_bg_id IN per_all_organization_units.organization_id%TYPE,
426 p_los_type IN hr_lookups.lookup_code%TYPE,
427 p_previous_job_id IN per_previous_jobs.previous_job_id%TYPE
428 ) RETURN NUMBER IS
429 l_prev_emp_period NUMBER(22,3) := 0;
430 l_temp_duration NUMBER(22,3) := 0;
431 l_entitlement NUMBER(22,3) := 0;
432 l_proc varchar2(60) := g_package||'get_length_previous_employment';
433 l_emp_type VARCHAR2(30);
434 l_start_date date;
435 l_end_date date;
436 l_all_assignments varchar2(2);
437 l_person_id number(10);
438 l_corps_id number(10);
439 l_grade_id number(10);
440 l_step_id number(10);
441 l_position_id number(10);
442
443 CURSOR csr_prevemp_entitlements IS
444 SELECT situation, NVL(entitlement_value, 0)entitlement_value
445 FROM pqh_situations
446 WHERE business_group_id = p_bg_id
447 AND situation_type = 'EMPLOYMENT'
448 AND length_of_service = p_los_type
449 AND employee_type = l_emp_type
450 AND entitlement_flag = 'Y'
451 AND trunc(sysdate) between effective_start_date and NVL(effective_end_date,g_end_of_time);
452
453 CURSOR csr_prev_job is
454 select pem.person_id, pem.business_group_id, pem.all_assignments, nvl(pjo.pjo_information2, 'XX') emp_type, pju.pju_information2 corps_definition_id,
455 pju.pju_information3 grade_id, pju.pju_information4 step_id, pju.pju_information5 position_id,
456 nvl(pjo.start_date, trunc(sysdate)) pjo_start_date,
457 nvl(pjo.end_date, trunc(sysdate)) pjo_end_date,
458 nvl(pju.start_date, trunc(sysdate)) pju_start_date,
459 nvl(pju.end_date, trunc(sysdate)) pju_end_date
460 from per_previous_employers pem, per_previous_jobs pjo, per_previous_job_usages pju
461 where
462 pem.previous_employer_id = pjo.previous_employer_id(+)
463 and
464 pjo.previous_job_id = pju.previous_job_id(+)
465 and
466 pem.person_id = p_person_id
467 and
468 pjo.previous_job_id = p_previous_job_id;
469
470 cursor cur_corps is
471 select to_number(hsck.segment7) corps_id
472 from hr_soft_coding_keyflex hsck,
473 fnd_id_flex_structures fifs,
474 per_all_assignments_f paf,
475 per_all_people_f ppf
476 where hsck.id_flex_num = fifs.id_flex_num
477 and fifs.id_flex_structure_code = 'FR_STATUTORY_INFO.'
478 and fifs.application_id = 800
479 and fifs.id_flex_code = 'SCL'
480 and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
481 and paf.person_id = ppf.person_id
482 and paf.primary_flag = 'Y'
483 and sysdate between paf.effective_start_date and paf.effective_end_date
484 and ppf.person_id = l_person_id;
485
486 cursor cur_grade is
487 select grade_id from
488 per_all_assignments_f
489 where person_id = l_person_id
490 and
491 primary_flag = 'Y'
492 and
493 sysdate between effective_start_date and effective_end_date;
494
495 cursor cur_step is
496 select special_ceiling_step_id from
497 per_all_assignments_f
498 where person_id = l_person_id
499 and
500 primary_flag = 'Y'
501 and
502 sysdate between effective_start_date and effective_end_date;
503
504 cursor cur_position is
505 select position_id from
506 per_all_assignments_f
507 where person_id = l_person_id
508 and
509 primary_flag = 'Y'
510 and
511 sysdate between effective_start_date and effective_end_date;
512 BEGIN
513 hr_utility.set_location('Entering '||l_proc,1);
514 for prev_job in csr_prev_job loop
515 l_emp_type := prev_job.emp_type;
516 l_all_assignments := prev_job.all_assignments;
517 l_entitlement := 0;
518 l_temp_duration := 0;
519 for entitlement in csr_prevemp_entitlements loop
520 -- Look for the entitlement for this employee type and los type in pqh_situations
521 l_entitlement := entitlement.entitlement_value;
522 end loop;
523 if (l_entitlement <> 0 and l_emp_type <> '02') then
524 if p_los_type = '10' then -- General Length of Service
525 if l_all_assignments = 'Y' then
526 l_temp_duration := prev_job.pjo_end_date-prev_job.pjo_start_date;
527 else
528 l_temp_duration := prev_job.pju_end_date-prev_job.pju_start_date;
529 end if;
530 elsif p_los_type = '20' then -- Length of Service in Public Services
531 if l_all_assignments = 'Y' then
532 l_temp_duration := prev_job.pjo_end_date-prev_job.pjo_start_date;
533 else
534 l_temp_duration := prev_job.pju_end_date-prev_job.pju_start_date;
535 end if;
536 elsif p_los_type = '30' then -- Length of Service in Corps
537 l_person_id := prev_job.person_id;
538 for c_corps in cur_corps loop
539 l_corps_id := c_corps.corps_id;
540 end loop;
541
542 if l_corps_id = prev_job.corps_definition_id then
543 l_temp_duration := prev_job.pju_end_date-prev_job.pju_start_date;
544 end if;
545 elsif p_los_type = '40' then -- Length of Service in Grade
546 for c_grade in cur_grade loop
547 l_grade_id := c_grade.grade_id;
548 end loop;
549
550 if l_grade_id = prev_job.grade_id then
551 l_temp_duration := prev_job.pju_end_date-prev_job.pju_start_date;
552 end if;
553 elsif p_los_type = '50' then -- Length of Service in Step
554 for c_step in cur_step loop
555 l_step_id := c_step.special_ceiling_step_id;
556 end loop;
557
558 if l_step_id = prev_job.step_id then
559 l_temp_duration := prev_job.pju_end_date-prev_job.pju_start_date;
560 end if;
561 elsif p_los_type = '60' then -- Length of Service in Position
562 for c_position in cur_position loop
563 l_position_id := c_position.position_id;
564 end loop;
565
566 if l_position_id = prev_job.position_id then
567 l_temp_duration := prev_job.pju_end_date-prev_job.pju_start_date;
568 end if;
569 end if;
570
571 l_temp_duration := trunc(l_entitlement*l_temp_duration/100.0);
572 end if;
573 end loop;
574 hr_utility.set_location('Leaving from '||l_proc,5);
575 RETURN l_temp_duration;
576 EXCEPTION
577 When Others THEN
578 hr_utility.set_location('Erroring out from '||l_proc,5);
579 RAISE_Application_Error(-20001,SQLERRM);
580 END;
581
582 -- -----------------------------------------------------------------------*
583 -- FUNCTION get_correction_factor
584 -- This function returns corrected number of days defined for the person
585 -- in Person EIT FR_PQH_ADDL_SENIORITY_INFO
586 -- -----------------------------------------------------------------------*
587
588
589 FUNCTION get_correction_factor ( p_person_id IN per_all_people_f.person_id%TYPE,
590 p_los_type IN hr_lookups.lookup_code%TYPE,
591 p_effective_date IN DATE)
592 RETURN NUMBER IS
593
594 l_correct_days NUMBER(22,3) := 0;
595 l_proc varchar2(60) := g_package||'get_correction_factor';
596
597 CURSOR Csr_correction IS
598 SELECT NVL(fnd_number.canonical_to_number(peit.pei_information4),0)
599 FROM per_people_extra_info peit
600 WHERE peit.person_id = p_person_id
601 AND peit.information_type = 'FR_PQH_ADDL_SENIORITY_INFO'
602 AND peit.pei_information1 = p_los_type
603 AND p_effective_date between fnd_date.canonical_to_date(peit.pei_information2)
604 AND NVL(fnd_date.canonical_to_date(peit.pei_information3),g_end_of_time);
605 BEGIN
606 hr_utility.set_location('Entering '||l_proc,1);
607 OPEN Csr_correction;
608 FETCH Csr_correction INTO l_correct_days;
609 CLOSE Csr_correction;
610 hr_utility.set_location(l_proc||' Correction Factor '||l_correct_days,2);
611 RETURN l_correct_days;
612 EXCEPTION
613 When Others THEN
614 hr_utility.set_location('Erroring out from '||l_proc,5);
615 RAISE_Application_Error(-20001,SQLERRM);
616 END get_correction_factor;
617
618 -- -----------------------------------------------------------------------*
619 -- FUNCTION get_military_service_period
620 -- This function returns corrected number of days in Military Service for the
621 -- person as held in FR_PQH_BONIFICATION_DETAILS
622 -- -----------------------------------------------------------------------*
623 FUNCTION get_military_service_period (p_bg_id IN hr_all_organization_units.organization_id%TYPE,
624 p_person_id IN per_all_people_f.person_id%TYPE,
625 p_assignment_id IN per_assignments_f.assignment_id%TYPE,
626 p_los_type IN hr_lookups.lookup_code%TYPE,
627 p_start_date IN DATE,
628 p_end_date IN DATE)
629 RETURN NUMBER IS
630
631 l_emp_type per_assignments_f.employee_category%TYPE;
632 l_entitlement_value NUMBER(22,3) := 0;
633 l_proc varchar2(60) := g_package||'get_military_service_period';
634 l_military_duration NUMBER(22,3) :=0;
635
636 CURSOR csr_military_entitlement IS
637 SELECT NVL(entitlement_value,0)
638 FROM pqh_situations
639 WHERE business_group_id = p_bg_id
640 AND situation_type = 'MILITARY'
641 AND length_of_service = p_los_type
642 AND employee_type = l_emp_type
643 AND entitlement_flag = 'Y'
644 AND g_determination_date BETWEEN effective_start_date AND NVL(effective_end_date,g_end_of_time);
645
646 CURSOR csr_military_periods IS
647 SELECT NVL(pei_information7,0) LENGTH_OF_SERVICE
648 FROM per_people_extra_info
649 WHERE person_id = p_person_id
650 AND information_type = 'FR_PQH_BONIFICATION_DETAILS'
651 AND (NVL(fnd_date.canonical_to_date(pei_information3),g_end_of_time) BETWEEN p_start_date AND p_end_date
652 OR fnd_date.canonical_to_date(pei_information2) BETWEEN p_start_date AND p_end_date );
653
654 BEGIN
655 hr_utility.set_location('Entering '||l_proc,1);
656 l_emp_type := get_employee_type(p_person_id => p_person_id,
657 p_determination_date => p_end_date);
658 hr_utility.set_location(l_proc||' employee type '||l_emp_type||to_char(p_start_date,'dd-mm-RRRR')||to_char(p_end_date,'dd-mm-RRRR'),1);
659 IF l_emp_type IS NOT NULL THEN
660 OPEN csr_military_entitlement;
661 FETCH csr_military_entitlement INTO l_entitlement_value;
662 CLOSE csr_military_entitlement;
663 hr_utility.set_location(l_proc||' military entitlement '||l_entitlement_value,1);
664 FOR lr_military IN csr_military_periods
665 LOOP
666 l_military_duration := l_military_duration + fnd_number.canonical_to_number(lr_military.length_of_service)*(NVL(l_entitlement_value,0)/100);
667 END LOOP;
668 END IF;
669 hr_utility.set_location(l_proc||' Military Duration '||l_military_duration,1);
670 RETURN l_military_duration;
671 EXCEPTION
672 When Others THEN
673 hr_utility.set_location('Erroring out from '||l_proc,5);
674 RAISE_Application_Error(-20001,SQLERRM);
675 END get_military_service_period;
676
677 -- -----------------------------------------------------------------------*
678 -- FUNCTION get_gen_pub_length_of_service
679 -- This function returns the general / public length of service for the employee
680 -- -----------------------------------------------------------------------*
681
682 FUNCTION get_gen_pub_length_of_service( p_bg_id IN per_all_organization_units.organization_id%TYPE,
683 p_person_id IN per_all_people_f.person_id%TYPE,
684 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
685 p_los_type IN VARCHAR2,
686 p_determination_date IN DATE)
687 RETURN NUMBER IS
688 /*
689 CURSOR csr_asg_period IS
690 SELECT Min(effective_start_date)
691 FROM per_all_assignments_f
692 WHERE person_id = p_person_id
693 AND assignment_id = p_assignment_id;
694
695 */
696 -- rewritten the above cursor to consider the service start date as a basis for the General LOS
697 CURSOR csr_service_start_date IS
698 SELECT date_start
699 FROM per_periods_of_service
700 WHERE person_id = p_person_id
701 AND business_group_id = p_bg_id
702 AND p_determination_date BETWEEN date_start AND NVL(actual_termination_date,g_end_of_time);
703
704 l_start_date DATE;
705 l_asg_duration NUMBER(22,3) := 0;
706 l_absent_duration NUMBER(22,3) := 0;
707 l_prev_employment NUMBER(22,3) := 0;
708 l_correction_factor NUMBER(22,3) := 0;
709 l_general_los NUMBER(22,3) := 0;
710 l_military_duration NUMBER(22,3) := 0;
711 l_parttime_duration NUMBER(22,3) := 0;
712 l_proc varchar2(60) := g_package||'get_gen_pub_LOS';
713
714 BEGIN
715 hr_utility.set_location('Entering '||l_proc,1);
716 OPEN csr_service_start_date;
717 FETCH csr_service_start_date INTO l_start_date;
718 CLOSE csr_service_start_date;
719 hr_utility.set_location(l_proc,2);
720 -- get the actual assignment period
721 hr_utility.set_location(l_proc||'person_id'||p_person_id||'assignment_id'||p_assignment_id||'start_date '||to_char(l_start_date,'dd-mm-RRRR'),3);
722 l_asg_duration := p_determination_date - l_start_date+1;
723 hr_utility.set_location(l_proc||' Assignment duration '||l_asg_duration,2);
724 -- findout any Impact because of Parttime entitlements defined for the LOS type
725 l_parttime_duration := get_parttime_entitlement(p_person_id => p_person_id,
726 p_assignment_id => p_assignment_id,
727 p_bg_id => p_bg_id,
728 p_los_type => p_los_type,
729 p_start_date => l_start_date,
730 p_end_date => p_determination_date);
731 hr_utility.set_location(l_proc||' Post Parttime Entitlement Duration '||l_parttime_duration,4);
732 l_prev_employment := get_previous_employment(p_person_id => p_person_id,
733 p_assignment_id => p_assignment_id,
734 p_start_date => l_start_date,
735 p_end_date => p_determination_date);
736 hr_utility.set_location(l_proc||' Previous Employment Duration '||l_prev_employment,5);
737 -- findout the absence duration for the person during this period
738 l_absent_duration := get_absent_period( p_bg_id => p_bg_id,
739 p_person_id => p_person_id,
740 p_assignment_id =>p_assignment_id,
741 p_los_type => p_los_type,
742 p_start_date => l_start_date,
743 p_end_date => p_determination_date);
744 --Approximately proportionating the Absence duration to consider the Parttime periods
745 -- l_absent_duration := l_absent_duration * l_parttime_duration/l_asg_duration;
746 --
747 l_asg_duration := l_parttime_duration;
748 hr_utility.set_location(l_proc||' Absent Duration '||l_absent_duration,6);
749 -- collect the correction factor defined (if any) for the person
750 l_correction_factor := get_correction_factor(p_person_id => p_person_id,
751 p_los_type => p_los_type,
752 p_effective_date => p_determination_date);
753 hr_utility.set_location(l_proc||' Corrected Days '||l_correction_Factor,7);
754 l_military_duration := get_military_service_period (p_bg_id => p_bg_id,
755 p_person_id => p_person_id,
756 p_assignment_id => p_assignment_id,
757 p_los_type => p_los_type,
758 p_start_date => l_start_date,
759 p_end_date => p_determination_date);
760 hr_utility.set_location(l_proc||' Military service Period '||l_military_duration,8);
761
762 l_general_los := l_asg_duration + l_prev_employment + l_correction_factor + l_military_duration - l_absent_duration;
763
764 hr_utility.set_location(l_proc||p_los_type||' Length of Service '||l_general_los,8);
765
766 RETURN l_general_los;
767
768 EXCEPTION
769 When Others THEN
770 hr_utility.set_location('Erroring out from '||l_proc,5);
771 RAISE_Application_Error(-20001,SQLERRM);
772 END get_gen_pub_length_of_service;
773
774 -- -----------------------------------------------------------------------*
775 -- FUNCTION get_grade_length_of_service
776 -- This function returns the length of service in the current grade for the employee
777 -- -----------------------------------------------------------------------*
778
779 FUNCTION get_grade_length_of_service(p_bg_id IN per_all_organization_units.organization_id%TYPE,
780 p_person_id IN per_all_people_f.person_id%TYPE,
781 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
782 p_determination_date IN DATE)
783 RETURN NUMBER IS
784
785
786 CURSOR csr_asg_grade IS
787 SELECT asg.assignment_id,
788 asg.grade_id
789 FROM per_all_assignments_f asg
790 WHERE asg.person_id = p_person_id
791 AND (p_assignment_id IS NOT NULL OR asg.primary_flag ='Y')
792 AND (p_assignment_id IS NULL OR asg.assignment_id = p_assignment_id)
793 AND p_determination_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
794
795 l_assignment_id per_assignments_f.assignment_id%TYPE;
796 l_grade_id per_assignments_f.grade_id%TYPE;
797
798 l_proc varchar2(60) := g_package||'get_grade_LOS';
799
800 CURSOR csr_grade_period IS
801 SELECT asg.effective_start_date,
802 asg.effective_end_date
803 FROM per_all_assignments_f asg
804 WHERE asg.assignment_id = l_assignment_id
805 AND asg.grade_id = l_grade_id
806 AND asg.effective_start_date <= p_determination_date
807 ORDER BY asg.effective_start_date, asg.effective_end_date;
808
809 l_start_date DATE;
810 l_end_date DATE;
811 l_grade_los NUMBER(22,3) := 0;
812 l_absent_duration NUMBER(22,3) := 0;
813 l_grade_entitlements NUMBER(22,3) := 0;
814 l_correction_factor NUMBER(22,3) := 0;
815 l_parttime_duration NUMBER(22,3) := 0;
816 l_prev_employment NUMBER(22,3) := 0;
817 l_military_duration NUMBER(22,3) := 0;
818 l_net_grade_los NUMBER(22,3) := 0;
819 BEGIN
820 hr_utility.set_location('Entering '||l_proc,1);
821 OPEN csr_asg_grade;
822 FETCH csr_asg_grade INTO l_assignment_id, l_grade_id;
823 CLOSE csr_asg_grade;
824 IF l_grade_id IS NOT NULL THEN
825 FOR l_grade_period IN csr_grade_period
826 LOOP
827 l_start_date := l_grade_period.effective_start_date;
828 l_end_date := l_grade_period.effective_end_date;
829 IF p_determination_date < l_end_date THEN
830 l_end_date := p_determination_date;
831 END IF;
832 l_grade_los := l_end_date - l_start_date+1;
833 hr_utility.set_location(l_proc||' Grade Duration '||l_grade_los,2);
834 -- findout any parttime entitlements defined for the LOS type
835 l_parttime_duration := get_parttime_entitlement( p_person_id => p_person_id,
836 p_assignment_id => p_assignment_id,
837 p_bg_id => p_bg_id,
838 p_los_type => '40',
839 p_start_date => l_start_date,
840 p_end_date => l_end_date);
841 hr_utility.set_location(l_proc||'Parttime Grade Duration '||l_parttime_duration,4);
842 l_prev_employment := get_previous_employment(p_person_id => p_person_id,
843 p_assignment_id => l_assignment_id,
844 p_start_date => l_start_date,
845 p_end_date => l_end_date);
846 hr_utility.set_location(l_proc||' Previous Employment '||l_prev_employment,5);
847 -- findout the absence duration for the person during this period
848 l_absent_duration := get_absent_period( p_bg_id => p_bg_id,
849 p_person_id => p_person_id,
850 p_assignment_id => l_assignment_id,
851 p_los_type => '40',
852 p_start_date => l_start_date,
853 p_end_date => l_end_date);
854
855 --Approximately proportionating the Absence duration to consider the Parttime periods
856 -- l_absent_duration := l_absent_duration * l_parttime_duration/l_grade_los;
857 l_grade_los := l_parttime_duration;
858 hr_utility.set_location(l_proc||' Absent Duration '||l_absent_duration,6);
859 -- collect the correction factor defined (if any) for the person
860 l_correction_factor := get_correction_factor(p_person_id => p_person_id,
861 p_los_type => '40',
862 p_effective_date => p_determination_date);
863 hr_utility.set_location(l_proc||' Corrected Days '||l_correction_factor,7);
864 l_military_duration := get_military_service_period (p_bg_id => p_bg_id,
865 p_person_id => p_person_id,
866 p_assignment_id => p_assignment_id,
867 p_los_type => '40',
868 p_start_date => l_start_date,
869 p_end_date => l_end_date);
870 hr_utility.set_location(l_proc||' Military service Period '||l_military_duration,8);
871 hr_utility.set_location(l_proc||'Calculation net_grade_los l_grade_los is '||l_grade_los,9);
872 hr_utility.set_location(l_proc||'Calculation net_grade_los l_prev_employment is '||l_prev_employment,10);
873 hr_utility.set_location(l_proc||'Calculation net_grade_los l_correction_factor is '||l_correction_factor,11);
874 hr_utility.set_location(l_proc||'Calculation net_grade_los l_military_duration is '||l_military_duration,12);
875 hr_utility.set_location(l_proc||'Calculation net_grade_los l_absent_duration is '||l_absent_duration,13);
876 l_net_grade_los := l_net_grade_los + (l_grade_los + l_prev_employment + l_correction_factor + l_military_duration - l_absent_duration);
877 END LOOP;
878 END IF;
879 hr_utility.set_location(l_proc||' Net Grade LOS '||l_net_grade_los,8);
880 RETURN l_net_grade_los;
881 EXCEPTION
882 When Others THEN
883 hr_utility.set_location('Erroring out from '||l_proc,5);
884 RAISE_Application_Error(-20001,SQLERRM);
885 END get_grade_length_of_service;
886
887 -- -----------------------------------------------------------------------*
888 -- FUNCTION get_position_length_of_service
889 -- This function returns the length of service in the current position for the employee
890 -- -----------------------------------------------------------------------*
891
892 FUNCTION get_position_length_of_service(p_bg_id IN per_all_organization_units.organization_id%TYPE,
893 p_person_id IN per_all_people_f.person_id%TYPE,
894 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
895 p_determination_date IN DATE)
896 RETURN NUMBER IS
897
898
899 l_proc varchar2(60) := g_package||'get_position_LOS';
900
901 CURSOR csr_asg_position IS
902 SELECT asg.assignment_id,
903 asg.position_id
904 FROM per_all_assignments_f asg
905 WHERE asg.person_id = p_person_id
906 AND (p_assignment_id IS NOT NULL OR asg.primary_flag ='Y')
907 AND (p_assignment_id IS NULL OR asg.assignment_id = p_assignment_id)
908 AND p_determination_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
909 l_assignment_id per_assignments_f.assignment_id%TYPE;
910 l_position_id per_assignments_f.position_id%TYPE;
911
912 CURSOR csr_position_period IS
913 SELECT asg.effective_start_date,
914 asg.effective_end_date
915 FROM per_all_assignments_f asg
916 WHERE asg.assignment_id = l_assignment_id
917 AND asg.position_id = l_position_id
918 AND asg.effective_start_date <= p_determination_date
919 ORDER BY asg.effective_start_date, asg.effective_end_date;
920
921 l_start_date DATE;
922 l_end_date DATE;
923 l_position_los NUMBER(22,3) := 0;
924 l_absent_duration NUMBER(22,3) := 0;
925 l_position_entitlements NUMBER(22,3) := 0;
926 l_correction_factor NUMBER(22,3) := 0;
927 l_parttime_duration NUMBER(22,3) := 0;
928 l_prev_employment NUMBER(22,3) := 0;
929 l_military_duration NUMBER(22,3) := 0;
930 l_net_position_los NUMBER(22,3) := 0;
931 BEGIN
932 hr_utility.set_location('Entering '||l_proc,1);
933 OPEN csr_asg_position;
934 FETCH csr_asg_position INTO l_assignment_id, l_position_id;
935 CLOSE csr_asg_position;
936 IF l_position_id IS NOT NULL THEN
937 FOR l_position_period IN csr_position_period
938 LOOP
939 l_start_date := l_position_period.effective_start_date;
940 l_end_date := l_position_period.effective_end_date;
941 IF p_determination_date < l_end_date THEN
942 l_end_date := p_determination_date;
943 END IF;
944 l_position_los := l_end_date - l_start_date+1;
945 hr_utility.set_location(l_proc||' Position Duration '||l_position_los,2);
946 -- findout any parttime entitlements defined for the LOS type
947 l_parttime_duration := get_parttime_entitlement( p_person_id => p_person_id,
948 p_assignment_id => p_assignment_id,
949 p_bg_id => p_bg_id,
950 p_los_type => '60',
951 p_start_date => l_start_date,
952 p_end_date => l_end_date);
953
954 hr_utility.set_location(l_proc||' Post Parttime Duration '||l_position_los,4);
955 l_prev_employment := get_previous_employment(p_person_id => p_person_id,
956 p_assignment_id => l_assignment_id,
957 p_start_date => l_start_date,
958 p_end_date => l_end_date);
959 hr_utility.set_location(l_proc||' Previous Employment Duration '||l_position_los,5);
960 -- findout the absence duration for the person during this period
961 l_absent_duration := get_absent_period( p_bg_id => p_bg_id,
962 p_person_id => p_person_id,
963 p_assignment_id => l_assignment_id,
964 p_los_type => '60',
965 p_start_date => l_start_date,
966 p_end_date => l_end_date);
967 --Approximately proportionating the Absence duration to consider the Parttime periods
968 -- l_absent_duration := l_absent_duration * l_parttime_duration/l_position_los;
969 l_position_los := l_parttime_duration;
970 hr_utility.set_location(l_proc||' Absent Duration '||l_absent_duration,6);
971 -- collect the correction factor defined (if any) for the person
972 l_correction_factor := get_correction_factor(p_person_id => p_person_id,
973 p_los_type => '60',
974 p_effective_date => p_determination_date);
975 hr_utility.set_location(l_proc||' Correct Days '||l_correction_factor,7);
976 -- get the military service duration, if entitled for this LOS calculation
977 l_military_duration := get_military_service_period (p_bg_id => p_bg_id,
978 p_person_id => p_person_id,
979 p_assignment_id => p_assignment_id,
980 p_los_type => '60',
981 p_start_date => l_start_date,
982 p_end_date => l_end_date);
983 hr_utility.set_location(l_proc||' Military service Period '||l_military_duration,8);
984
985 l_net_position_los := l_net_position_los + (l_position_los + l_prev_employment + l_correction_factor + l_military_duration - l_absent_duration);
986 END LOOP;
987 END IF;
988
989 hr_utility.set_location(l_proc||' Net Position LOS '||l_net_position_los,8);
990
991 RETURN l_net_position_los;
992 EXCEPTION
993 When Others THEN
994 hr_utility.set_location('Erroring out from '||l_proc,5);
995 RAISE_Application_Error(-20001,SQLERRM);
996 END get_position_length_of_service;
997
998
999 -- -----------------------------------------------------------------------*
1000 -- FUNCTION get_corps_length_of_service
1001 -- This function returns the length of service in the current position for the employee
1002 -- -----------------------------------------------------------------------*
1003
1004 FUNCTION get_corps_length_of_service(p_bg_id IN per_all_organization_units.organization_id%TYPE,
1005 p_person_id IN per_all_people_f.person_id%TYPE,
1006 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1007 p_determination_date IN DATE)
1008 RETURN NUMBER IS
1009
1010
1011 l_proc varchar2(60) := g_package||'get_corps_LOS';
1012
1013 CURSOR csr_asg_corps
1014 IS
1015 SELECT asg.assignment_id, grade_ladder_pgm_id
1016 FROM per_all_assignments_f asg
1017 WHERE asg.person_id = p_person_id
1018 AND (p_assignment_id IS NOT NULL OR asg.primary_flag = 'Y')
1019 AND (p_assignment_id IS NULL
1020 OR asg.assignment_id = p_assignment_id
1021 )
1022 AND p_determination_date BETWEEN asg.effective_start_date
1023 AND asg.effective_end_date;
1024
1025
1026 l_assignment_id per_assignments_f.assignment_id%TYPE;
1027 l_corps_id hr_soft_coding_keyflex.segment7%TYPE;
1028
1029 CURSOR csr_corps_period
1030 IS
1031 SELECT asg.effective_start_date, asg.effective_end_date
1032 FROM per_all_assignments_f asg
1033 WHERE asg.assignment_id = l_assignment_id
1034 AND asg.effective_start_date <= p_determination_date
1035 AND asg.grade_ladder_pgm_id = l_corps_id;
1036
1037
1038 l_start_date DATE;
1039 l_end_date DATE;
1040 l_corps_los NUMBER(22,3) := 0;
1041 l_absent_duration NUMBER(22,3) := 0;
1042 l_corps_entitlements NUMBER(22,3) := 0;
1043 l_correction_factor NUMBER(22,3) := 0;
1044 l_parttime_duration NUMBER(22,3) := 0;
1045 l_prev_employment NUMBER(22,3) := 0;
1046 l_military_duration NUMBER(22,3) := 0;
1047 l_net_corps_los NUMBER(22,3) := 0;
1048 BEGIN
1049 hr_utility.set_location('Entering '||l_proc,1);
1050 OPEN csr_asg_corps;
1051 FETCH csr_asg_corps INTO l_assignment_id, l_corps_id;
1052 CLOSE csr_asg_corps;
1053 IF l_corps_id IS NOT NULL THEN
1054 FOR l_corps_period IN csr_corps_period
1055 LOOP
1056 l_start_date := l_corps_period.effective_start_date;
1057 l_end_date := l_corps_period.effective_end_date;
1058 IF p_determination_date < l_end_date THEN
1059 l_end_date := p_determination_date;
1060 END IF;
1061 l_corps_los := l_end_date - l_start_date+1;
1062 hr_utility.set_location(l_proc||' Corps Duration '||l_corps_los,2);
1063 -- findout any parttime entitlements defined for the LOS type
1064 l_parttime_duration := get_parttime_entitlement( p_person_id => p_person_id,
1065 p_assignment_id => p_assignment_id,
1066 p_bg_id => p_bg_id,
1067 p_los_type => '30',
1068 p_start_date => l_start_date,
1069 p_end_date => l_end_date);
1070
1071 hr_utility.set_location(l_proc||' Post Parttime Duration '||l_parttime_duration,4);
1072 l_prev_employment := get_previous_employment(p_person_id => p_person_id,
1073 p_assignment_id => l_assignment_id,
1074 p_start_date => l_start_date,
1075 p_end_date => l_end_date);
1076 hr_utility.set_location(l_proc||' Previous Employment Duration '||l_corps_los,5);
1077 -- findout the absence duration for the person during this period
1078 l_absent_duration := get_absent_period( p_bg_id => p_bg_id,
1079 p_person_id => p_person_id,
1080 p_assignment_id => l_assignment_id,
1081 p_los_type => '30',
1082 p_start_date => l_start_date,
1083 p_end_date => l_end_date);
1084 --Approximately proportionating the Absence duration to consider the Parttime periods
1085 -- l_absent_duration := l_absent_duration * l_parttime_duration/l_corps_los;
1086 l_corps_los := l_parttime_duration;
1087 hr_utility.set_location(l_proc||' Absent Duration '||l_absent_duration,6);
1088 -- collect the correction factor defined (if any) for the person
1089 l_correction_factor := get_correction_factor(p_person_id => p_person_id,
1090 p_los_type => '60',
1091 p_effective_date => p_determination_date);
1092 hr_utility.set_location(l_proc||' Correct Days '||l_correction_factor,7);
1093 -- get the military service duration, if entitled for this LOS calculation
1094 l_military_duration := get_military_service_period (p_bg_id => p_bg_id,
1095 p_person_id => p_person_id,
1096 p_assignment_id => p_assignment_id,
1097 p_los_type => '60',
1098 p_start_date => l_start_date,
1099 p_end_date => l_end_date);
1100 hr_utility.set_location(l_proc||' Military service Period '||l_military_duration,8);
1101
1102 l_net_corps_los := l_net_corps_los + (l_corps_los + l_prev_employment + l_correction_factor + l_military_duration - l_absent_duration);
1103 END LOOP;
1104 END IF;
1105
1106 hr_utility.set_location(l_proc||' Net Corps LOS '||l_net_corps_los,8);
1107
1108 RETURN l_net_corps_los;
1109 EXCEPTION
1110 When Others THEN
1111 hr_utility.set_location('Erroring out from '||l_proc,5);
1112 RAISE_Application_Error(-20001,SQLERRM);
1113 END get_corps_length_of_service;
1114
1115 -- -----------------------------------------------------------------------*
1116 -- FUNCTION get_step_length_of_service
1117 -- This function returns the length of service in the current grade step for the employee
1118 -- -----------------------------------------------------------------------*
1119
1120 FUNCTION get_step_length_of_service (p_bg_id IN per_all_organization_units.organization_id%TYPE,
1121 p_person_id IN per_all_people_f.person_id%TYPE,
1122 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1123 p_determination_date IN DATE)
1124 RETURN NUMBER IS
1125 Cursor Csr_asg_step IS
1126 SELECT spp.assignment_id,
1127 spp.step_id,
1128 spp.effective_start_date,
1129 spp.effective_end_date
1130 FROM per_spinal_point_placements_f spp, per_all_assignments_f asg
1131 WHERE asg.person_id = p_person_id
1132 AND (p_assignment_id IS NOT NULL OR asg.primary_flag = 'Y')
1133 AND (p_assignment_id IS NULL OR asg.assignment_id = p_assignment_id)
1134 AND p_determination_date BETWEEN asg.effective_start_date AND asg.effective_end_date
1135 AND spp.assignment_id = asg.assignment_id
1136 AND p_determination_date BETWEEN spp.effective_start_date and spp.effective_end_date;
1137
1138 l_assignment_id per_assignments_f.assignment_id%TYPE;
1139 l_step_id per_spinal_point_placements_f.step_id%TYPE;
1140 l_start_date DATE;
1141 l_end_date DATE;
1142 l_step_los NUMBER(22,3) := 0;
1143 l_absent_duration NUMBER(22,3) := 0;
1144 l_step_entitlements NUMBER(22,3) := 0;
1145 l_correction_factor NUMBER(22,3) := 0;
1146 l_parttime_duration NUMBER(22,3) := 0;
1147 l_prev_employment NUMBER(22,3) := 0;
1148 l_military_duration NUMBER(22,3) := 0;
1149 l_net_step_los NUMBER(22,3) := 0;
1150 l_proc varchar2(60) := g_package||'get_step_LOS';
1151
1152
1153 BEGIN
1154 hr_utility.set_location('Entering '||l_proc,1);
1155 OPEN csr_asg_step;
1156
1157 FETCH csr_asg_step INTO l_assignment_id, l_step_id, l_start_date, l_end_date;
1158
1159 IF csr_asg_step%NOTFOUND THEN
1160 CLOSE csr_asg_step;
1161 RETURN l_net_step_los;
1162 END IF;
1163 CLOSE csr_asg_step;
1164 IF l_end_date > p_determination_date THEN
1165 l_end_date := p_determination_date;
1166 END IF;
1167 l_step_los := l_end_date - l_start_date;
1168
1169 hr_utility.set_location(l_proc||' Step Duration '||l_step_los,2);
1170 -- findout any parttime entitlements defined for the LOS type
1171 l_parttime_duration := get_parttime_entitlement( p_person_id => p_person_id,
1172 p_assignment_id => p_assignment_id,
1173 p_bg_id => p_bg_id,
1174 p_los_type => '50',
1175 p_start_date => l_start_date,
1176 p_end_date => l_end_date);
1177
1178 hr_utility.set_location(l_proc||' Post parttime Step Duration '||l_step_los,4);
1179 l_prev_employment := get_previous_employment(p_person_id => p_person_id,
1180 p_assignment_id => l_assignment_id,
1181 p_start_date => l_start_date,
1182 p_end_date => l_end_date);
1183 hr_utility.set_location(l_proc||' Previous Emp Duration '||l_prev_employment,5);
1184
1185 -- findout the absence duration for the person during this period
1186 l_absent_duration := get_absent_period( p_bg_id => p_bg_id,
1187 p_person_id => p_person_id,
1188 p_assignment_id => l_assignment_id,
1189 p_los_type => '50',
1190 p_start_date => l_start_date,
1191 p_end_date => l_end_date);
1192 --Approximately proportionating the Absence duration to consider the Parttime periods
1193 -- l_absent_duration := l_absent_duration * l_parttime_duration/l_step_los;
1194 l_step_los := l_parttime_duration;
1195 hr_utility.set_location(l_proc||' Absent Duration '||l_absent_duration,6);
1196
1197 -- collect the correction factor defined (if any) for the person
1198 l_correction_factor := get_correction_factor(p_person_id => p_person_id,
1199 p_los_type => '50',
1200 p_effective_date => p_determination_date);
1201 hr_utility.set_location(l_proc||' Correct Days '||l_correction_factor,7);
1202
1203 -- get the military service duration, if entitled for this LOS calculation
1204 l_military_duration := get_military_service_period (p_bg_id => p_bg_id,
1205 p_person_id => p_person_id,
1206 p_assignment_id => p_assignment_id,
1207 p_los_type => '50',
1208 p_start_date => l_start_date,
1209 p_end_date => l_end_date);
1210 hr_utility.set_location(l_proc||' Military service Period '||l_military_duration,8);
1211
1212 l_net_step_los := l_net_step_los + (l_step_los + l_prev_employment + l_correction_factor + l_military_duration - l_absent_duration);
1213 hr_utility.set_location(l_proc||' LOS on Step '||l_net_step_los,8);
1214 RETURN l_net_step_los;
1215 EXCEPTION
1216 When Others THEN
1217 hr_utility.set_location('Erroring out from '||l_proc,5);
1218 RAISE_Application_Error(-20001,SQLERRM);
1219
1220 END get_step_length_of_service;
1221
1222 FUNCTION get_los_for_display ( p_bg_id IN NUMBER,
1223 p_person_id IN NUMBER default NULL,
1224 p_assignment_id IN NUMBER default NULL,
1225 p_los_type IN VARCHAR2,
1226 p_determination_date IN DATE default SYSDATE) RETURN VARCHAR2 IS
1227 l_display_los Varchar2(200);
1228 l_yy number(5);
1229 l_mm number(5);
1230 l_dd number(5);
1231 l_temp_los_mm number(22,3);
1232 l_temp_los_yy number(22,3);
1233 l_los_days NUMBER(22,3);
1234 l_adj_service_date DATE;
1235 l_determination_date DATE := TRUNC(p_determination_date);
1236 l_proc varchar2(60) := g_package||'get_los_for_display';
1237 BEGIN
1238 hr_utility.set_location(l_proc||' Entering',10);
1239 l_los_days := get_length_of_service( p_bg_id => p_bg_id,
1240 p_person_id => p_person_id,
1241 p_assignment_id => p_assignment_id,
1242 p_los_type => p_los_type,
1243 p_return_units =>'D',
1244 p_determination_date => l_determination_date);
1245
1246 l_adj_service_date := l_determination_date - l_los_days;
1247
1248 l_temp_los_mm := months_between(l_determination_date ,l_adj_service_date);
1249 l_yy := NVL((l_temp_los_mm - mod(l_temp_los_mm,12))/12,0);
1250 l_temp_los_mm := l_temp_los_mm - l_yy*12;
1251 l_mm := NVL(TRUNC(l_temp_los_mm,0),0);
1252 l_dd := NVL(l_determination_date - TRUNC( ADD_MONTHS(l_adj_service_date,(l_mm+l_yy*12))),0);
1253
1254 l_display_los := NVL(l_yy,0)||' '||hr_general.decode_lookup('QUALIFYING_UNITS','Y')||' '||NVL(l_mm,0)||' '||hr_general.decode_lookup('QUALIFYING_UNITS','M')||' '||l_dd||' '||hr_general.decode_lookup('QUALIFYING_UNITS','D');
1255
1256 hr_utility.set_location(l_proc||l_display_los,15);
1257
1258 hr_utility.set_location(l_proc||' Leaving',20);
1259
1260 RETURN l_display_los;
1261 END get_los_for_display;
1262 -- -----------------------------------------------------------------------*
1263 -- FUNCTION get_length_of_service
1264 -- This function returns the appropriate length of service for the employee
1265 -- depending on the type of length of service required for
1266 -- -----------------------------------------------------------------------*
1267
1268
1269 FUNCTION get_length_of_service( p_bg_id IN NUMBER,
1270 p_person_id IN NUMBER default NULL,
1271 p_assignment_id IN NUMBER default NULL,
1272 p_los_type IN VARCHAR2,
1273 p_return_units IN VARCHAR2 default 'D',
1274 p_determination_date IN DATE default NULL)
1275 RETURN NUMBER
1276
1277 IS
1278
1279 l_start_date DATE;
1280 l_determination_date DATE := p_determination_date;
1281 l_assignment_id PER_ALL_ASSIGNMENTS_F.assignment_id%TYPE;
1282 l_los_days NUMBER(22,3) := 0;
1283 l_los_return NUMBER(22,3);
1284 l_emp_type PER_ALL_ASSIGNMENTS_F.employee_category%TYPE;
1285 l_person_id PER_ALL_PEOPLE_F.person_id%TYPE;
1286 l_bg_id per_all_organization_units.organization_id%TYPE := p_bg_id;
1287 l_adj_service_date DATE;
1288 l_proc varchar2(60) := g_package||'get_length_of_sevice';
1289 l_exists VARCHAR2(2) := '0';
1290
1291 CURSOR CSR_validate_person IS
1292 SELECT '1'
1293 FROM per_all_people_f
1294 WHERE person_id = p_person_id
1295 AND l_determination_date BETWEEN effective_start_date AND effective_end_date;
1296
1297 CURSOR CSR_validate_assignment IS
1298 SELECT person_id
1299 FROM per_all_assignments_f
1300 WHERE assignment_id = p_assignment_id
1301 AND l_determination_date BETWEEN effective_start_date AND effective_end_date;
1302 CURSOR Csr_get_primary_asg IS
1303 SELECT assignment_id
1304 FROM per_all_assignments_f
1305 WHERE person_id = p_person_id
1306 AND primary_flag = 'Y'
1307 AND l_determination_date BETWEEN effective_start_date AND effective_end_date;
1308
1309
1310 BEGIN
1311
1312 hr_utility.set_location('Entering '||l_proc,1);
1313
1314 -- take session date as the determination date if determination date is not passed
1315 IF l_determination_date IS NULL THEN
1316 l_determination_date := get_effective_date;
1317 END IF;
1318 --
1319 hr_utility.set_location(l_proc||' Effective Date '||to_char(l_determination_date,'dd-mm-RRRR'),2);
1320
1321 IF p_bg_id IS NULL THEN
1322 hr_api.mandatory_arg_error(p_api_name => l_proc,
1323 p_argument => 'p_bg_id',
1324 p_argument_value => p_bg_id);
1325 ELSE
1326 hr_api.validate_bus_grp_id(p_business_group_id=>p_bg_id);
1327 END IF;
1328 hr_utility.set_location(l_proc||' BG ID Validated',2);
1329 IF p_person_id IS NULL AND p_assignment_id IS NULL THEN
1330 hr_api.mandatory_arg_error(p_api_name => l_proc,
1331 p_argument => 'p_person_id',
1332 p_argument_value => p_person_id);
1333 END IF;
1334 hr_utility.set_location(l_proc||' Person ID Validated',2);
1335 IF p_person_id IS NOT NULL THEN
1336 OPEN Csr_validate_person;
1337 FETCH Csr_validate_person INTO l_exists;
1338 CLOSE Csr_validate_person;
1339 IF l_exists = '0' THEN
1340 fnd_message.set_name('PQH','PQH_INVALID_PARAM_VALUE');
1341 fnd_message.set_token('VALUE',to_char(p_person_id));
1342 fnd_message.set_token('PARAMETER','p_person_id');
1343 fnd_message.raise_error;
1344 END IF;
1345 IF p_assignment_id IS NULL THEN
1346 OPEN Csr_Get_Primary_Asg;
1347 Fetch Csr_Get_Primary_Asg INTO l_assignment_id;
1348 CLOSE Csr_Get_Primary_Asg;
1349 END IF;
1350 END IF;
1351
1352 IF p_assignment_id IS NOT NULL THEN
1353 l_assignment_id := p_assignment_id;
1354 OPEN Csr_validate_assignment;
1355 FETCH Csr_validate_assignment INTO l_person_id;
1356 IF Csr_validate_assignment%NOTFOUND THEN
1357 CLOSE Csr_validate_assignment;
1358 fnd_message.set_name('PQH','PQH_INVALID_PARAM_VALUE');
1359 fnd_message.set_token('VALUE',to_char(p_assignment_id));
1360 fnd_message.set_token('PARAMETER','p_assignment_id');
1361 fnd_message.raise_error;
1362 END IF;
1363 CLOSE Csr_validate_assignment;
1364 END IF;
1365 l_person_id := NVL(p_person_id,l_person_id);
1366 hr_utility.set_location(l_proc||' Assignment iD Validated',2);
1367 IF p_los_type IS NULL THEN
1368 hr_api.mandatory_arg_error(p_api_name => l_proc,
1369 p_argument => 'p_los_type',
1370 p_argument_value => p_los_type);
1371 ELSE
1372 IF hr_api.NOT_EXISTS_IN_HR_LOOKUPS(p_effective_date => l_determination_date,
1373 p_lookup_type => 'FR_PQH_LENGTH_OF_SERVICE_TYPE',
1374 p_lookup_code => p_los_type) THEN
1375 fnd_message.set_name('PQH','PQH_INVALID_PARAM_VALUE');
1376 fnd_message.set_token('VALUE',p_los_type);
1377 fnd_message.set_token('PARAMETER','p_los_type');
1378 END IF;
1379 END IF;
1380 hr_utility.set_location(l_proc||' LOS TYPE Validated',2);
1381 IF p_return_units IS NULL THEN
1382 hr_api.mandatory_arg_error(p_api_name => l_proc,
1383 p_argument => 'p_return_units',
1384 p_argument_value => p_return_units);
1385 ELSE
1386 IF hr_api.NOT_EXISTS_IN_HR_LOOKUPS(p_effective_date => l_determination_date,
1387 p_lookup_type => 'QUALIFYING_UNITS',
1388 p_lookup_code => p_return_units) THEN
1389 fnd_message.set_name('PQH','PQH_INVALID_PARAM_VALUE');
1390 fnd_message.set_token('VALUE',p_return_units);
1391 fnd_message.set_token('PARAMETER','p_return_units');
1392
1393 END IF;
1394 END IF;
1395 g_emp_type := get_employee_type(p_person_id => p_person_id,
1396 p_determination_date => l_determination_date);
1397 g_determination_date := l_determination_date;
1398 hr_utility.set_location(l_proc||' Completed Validations ',2);
1399 hr_utility.set_location(l_proc||' Person Id '||to_char(L_person_id),2);
1400 hr_utility.set_location(l_proc||' Assignment Id '||to_char(L_Assignment_Id),2);
1401 IF p_los_type IN ('10','20') THEN -- General Length of service and Length of service in Public Service
1402 hr_utility.set_location(l_proc||' Calling get_gen_pub_length_of_service',3);
1403 l_los_days := get_gen_pub_length_of_service( p_bg_id => l_bg_id,
1404 p_person_id => l_person_id,
1405 p_assignment_id => l_assignment_id,
1406 p_los_type => p_los_type,
1407 p_determination_date => l_determination_date);
1408
1409 ELSIF p_los_type = '30' THEN -- Length of Service in Corps
1410
1411 l_los_days := get_corps_length_of_service(p_bg_id => p_bg_id,
1412 p_person_id => l_person_id,
1413 p_assignment_id => l_assignment_id,
1414 p_determination_date => l_determination_date);
1415
1416 ELSIF p_los_type = '40' THEN -- Length of Service in Grade
1417
1418 l_los_days := get_grade_length_of_service(p_bg_id => p_bg_id,
1419 p_person_id => l_person_id,
1420 p_assignment_id => l_assignment_id,
1421 p_determination_date => l_determination_date);
1422 ELSIF p_los_type = '50' THEN -- Length of Service in Step
1423
1424 l_los_days := get_step_length_of_service(p_bg_id => p_bg_id,
1425 p_person_id => l_person_id,
1426 p_assignment_id => l_assignment_id,
1427 p_determination_date => l_determination_date);
1428 ELSIF p_los_type = '60' THEN -- Length of service in Position
1429
1430 l_los_days := get_position_length_of_service(p_bg_id => p_bg_id,
1431 p_person_id => l_person_id,
1432 p_assignment_id => l_assignment_id,
1433 p_determination_date => l_determination_date);
1434
1435 END IF;
1436
1437 hr_utility.set_location(l_proc,2);
1438
1439 l_adj_service_date := l_determination_date - l_los_days;
1440
1441 hr_utility.set_location(l_proc||' Adjusted Service Date '||To_Char(l_adj_service_date,'dd-Mm-RRRR'),3);
1442
1443 IF p_return_units = 'D' THEN
1444 l_los_return := l_los_days;
1445 ELSIF p_return_units = 'W' THEN
1446 l_los_return := l_los_days/7;
1447 ELSIF p_return_units = 'M' THEN
1448 l_los_return := Months_Between(l_determination_date,l_adj_service_date);
1449 ELSIF p_return_units = 'Y' THEN
1450 l_los_return := Months_Between(l_determination_date,l_adj_service_date)/12;
1451 END IF;
1452
1453 hr_utility.set_location(l_proc||' LOS in '||p_return_units||' '||l_los_return,4);
1454
1455 return l_los_return;
1456 EXCEPTION
1457 When Others THEN
1458 hr_utility.set_location('Erroring out from '||l_proc,5);
1459 RAISE_Application_Error(-20001,SQLERRM);
1460
1461 END get_length_of_service;
1462
1463
1464 --
1465 -- This Function returns the military service duration for an employee
1466 --
1467 FUNCTION get_de_military_service_period(p_bg_id IN hr_all_organization_units.organization_id%TYPE,
1468 p_person_id IN per_all_people_f.person_id%TYPE,
1469 p_los_type IN hr_lookups.lookup_code%TYPE,
1470 p_start_date IN DATE,
1471 p_end_date IN DATE)
1472 RETURN VARCHAR2 IS
1473
1474 l_emp_type per_assignments_f.employee_category%TYPE;
1475 l_military_entitlement VARCHAR2(30);
1476 l_proc VARCHAR2(60) := g_package||'get_military_service_period';
1477 l_military_duration NUMBER(22,3) :=0;
1478 l_los_return VARCHAR2(240);
1479 l_los_years NUMBER := 0;
1480 l_los_months NUMBER := 0;
1481 l_adj_service_date DATE;
1482
1483 CURSOR csr_military_entitlement IS
1484 SELECT '1'
1485 FROM pqh_situations
1486 WHERE business_group_id = p_bg_id
1487 AND situation_type = 'MILITARY'
1488 AND length_of_service = p_los_type
1489 AND employee_type = l_emp_type
1490 AND entitlement_flag = 'Y'
1491 AND p_start_date BETWEEN effective_start_date AND NVL(effective_end_date,g_end_of_time)
1492 AND p_end_date BETWEEN effective_start_date AND NVL(effective_end_date,g_end_of_time);
1493
1494 CURSOR csr_military_periods IS
1495 SELECT nvl((p_end_date - p_start_date),0) los
1496 FROM per_people_extra_info
1497 WHERE person_id = p_person_id
1498 AND information_type = 'DE_MILITARY_SERVICE'
1499 AND p_start_date = fnd_date.canonical_to_date(pei_information1)
1500 AND p_end_date = fnd_date.canonical_to_date(pei_information2);
1501
1502 BEGIN
1503 hr_utility.set_location('Entering '||l_proc,1);
1504 l_emp_type := get_employee_type(p_person_id => p_person_id,
1505 p_determination_date => trunc(sysdate));
1506 hr_utility.set_location(l_proc||' employee type '||l_emp_type||to_char(p_start_date,'dd-mm-RRRR')||to_char(p_end_date,'dd-mm-RRRR'),1);
1507 OPEN csr_military_entitlement;
1508 FETCH csr_military_entitlement INTO l_military_entitlement;
1509 CLOSE csr_military_entitlement;
1510 hr_utility.set_location(l_proc||' military entitlement '||l_military_entitlement,1);
1511 IF l_military_entitlement IS NOT NULL THEN
1512 OPEN csr_military_periods;
1513 FETCH csr_military_periods INTO l_military_duration;
1514 IF csr_military_periods%FOUND THEN
1515 l_military_duration := l_military_duration + 1;
1516 END IF; --both dates inclusive
1517 CLOSE csr_military_periods;
1518 END IF;
1519
1520 l_adj_service_date := p_end_date - l_military_duration;
1521
1522 l_los_years := months_between(p_end_date,l_adj_service_date)/12;
1523
1524 If instr(l_los_years,'.',1) <> 0 Then
1525 l_los_months := substr(l_los_years,instr(l_los_years,'.',1)) * 12;
1526 End If;
1527
1528 IF trunc(l_los_years) = 0 and trunc(l_los_months) = 0 THEN
1529 l_los_return := 0||'/'||0||'/'||l_military_duration;
1530 ELSE
1531 l_military_duration := round(substr(l_los_months,instr(l_los_months,'.',1)) * 31);
1532 l_los_return := trunc(l_los_years)||'/'||trunc(l_los_months)||'/'||l_military_duration;
1533 END IF;
1534
1535 hr_utility.set_location(l_proc||' Military Duration '||l_military_duration,1);
1536 RETURN l_los_return;
1537 EXCEPTION
1538 When Others THEN
1539 hr_utility.set_location('Erroring out from '||l_proc,5);
1540 RAISE_Application_Error(-20001,SQLERRM);
1541 END get_de_military_service_period;
1542
1543
1544 -- -----------------------------------------------------------------------*
1545 -- FUNCTION get_de_pub_length_of_service
1546 -- This function returns the length of service in Public Services for an employee
1547 -- -----------------------------------------------------------------------*
1548
1549 FUNCTION get_de_pub_length_of_service(p_bg_id IN per_all_organization_units.organization_id%TYPE,
1550 p_person_id IN per_all_people_f.person_id%TYPE,
1551 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1552 p_los_type IN VARCHAR2,
1553 p_assg_start_date IN DATE ,
1554 p_assg_end_date IN DATE
1555 )
1556 RETURN NUMBER IS
1557
1558 CURSOR c_person_dob IS
1559 SELECT date_of_birth
1560 FROM per_all_people_f
1561 WHERE person_id = p_person_id
1562 AND effective_start_date <= p_assg_end_date
1563 AND effective_end_date >= p_assg_start_date;
1564
1565 CURSOR c_assg_los_type IS
1566 SELECT '1'
1567 FROM per_assignment_extra_info
1568 WHERE assignment_id = p_assignment_id
1569 AND nvl(aei_information1,'x') = p_los_type
1570 AND information_type = 'DE_PQH_ASSG_LOS_INFO';
1571
1572
1573 l_start_date DATE;
1574 l_asg_duration NUMBER(22,3) := 0;
1575 l_absent_duration NUMBER(22,3) := 0;
1576 l_correction_factor NUMBER(22,3) := 0;
1577 l_general_los NUMBER(22,3) := 0;
1578 l_military_duration NUMBER(22,3) := 0;
1579 l_proc VARCHAR2(60) := g_package||'get_de_pub_LOS';
1580 l_date_of_birth DATE;
1581 l_assg_los_type varchar2(1);
1582
1583
1584 BEGIN
1585 hr_utility.set_location('Entering '||l_proc,1);
1586
1587 OPEN c_assg_los_type;
1588 FETCH c_assg_los_type INTO l_assg_los_type;
1589 CLOSE c_assg_los_type;
1590
1591 --
1592 -- If the length of service type is present in the Extra Info then the assignment
1593 -- is not taken for LOS calculation.
1594 --
1595
1596 IF l_assg_los_type IS NOT NULL THEN
1597 RETURN 0;
1598 END IF;
1599
1600 OPEN c_person_dob;
1601 FETCH c_person_dob INTO l_date_of_birth;
1602 CLOSE c_person_dob;
1603
1604 hr_utility.set_location(' date of birth '||l_date_of_birth,2);
1605 hr_utility.set_location(' assignment start date '||p_assg_start_date,2);
1606
1607 IF l_date_of_birth IS NOT NULL THEN
1608 IF Months_between(p_assg_start_date,l_date_of_birth)/12 < 18 THEN
1609 l_start_date := add_months(l_date_of_birth,18*12);
1610 ELSE
1611 l_start_date := p_assg_start_date;
1612 END IF;
1613 ELSE
1614 l_start_date := p_assg_start_date;
1615 END IF;
1616
1617 IF (p_assg_end_date - l_start_date) < 0 THEN
1618 RETURN 0;
1619 END IF;
1620 l_asg_duration := trunc(p_assg_end_date - l_start_date);
1621
1622 hr_utility.set_location(l_proc||' Assignment duration '||l_asg_duration,2);
1623
1624 -- findout the absence duration for the person during this period
1625 l_absent_duration := get_absent_period( p_bg_id => p_bg_id,
1626 p_person_id => p_person_id,
1627 p_assignment_id => p_assignment_id,
1628 p_los_type => p_los_type,
1629 p_start_date => l_start_date,
1630 p_end_date => p_assg_end_date);
1631
1632 hr_utility.set_location(l_proc||' Absent Duration '||l_absent_duration,6);
1633
1634 -- collect the correction factor defined (if any) for the person
1635 /* l_correction_factor := get_correction_factor(p_person_id => p_person_id,
1636 p_los_type => p_los_type,
1637 p_effective_date => p_assg_end_date);
1638 hr_utility.set_location(l_proc||' Corrected Days '||l_correction_Factor,7);
1639 l_military_duration := get_de_military_service_period (p_bg_id => p_bg_id,
1640 p_person_id => p_person_id,
1641 p_los_type => p_los_type,
1642 p_start_date => l_start_date,
1643 p_end_date => p_assg_end_date);
1644
1645 hr_utility.set_location(l_proc||' Military service Period '||l_military_duration,8); */
1646
1647 l_general_los := (l_asg_duration + 1) - l_absent_duration;
1648 hr_utility.set_location(l_proc||p_los_type||' Length of Service '||l_general_los,8);
1649
1650 RETURN l_general_los;
1651
1652 EXCEPTION
1653 When Others THEN
1654 hr_utility.set_location('Erroring out from '||l_proc,5);
1655 RAISE_Application_Error(-20001,SQLERRM);
1656 END get_de_pub_length_of_service;
1657
1658
1659 -- -----------------------------------------------------------------------*
1660 -- FUNCTION get_jub_mon_length_of_service
1661 -- This function returns the length of service for Jubilee Money for an employee
1662 -- -----------------------------------------------------------------------*
1663
1664 FUNCTION get_jub_mon_length_of_service(p_bg_id IN per_all_organization_units.organization_id%TYPE,
1665 p_person_id IN per_all_people_f.person_id%TYPE,
1666 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1667 p_los_type IN VARCHAR2,
1668 p_assg_start_date IN DATE ,
1669 p_assg_end_date IN DATE
1670 )
1671 RETURN NUMBER IS
1672
1673 l_start_date DATE;
1674 l_asg_duration NUMBER(22,3) := 0;
1675 l_absent_duration NUMBER(22,3) := 0;
1676 l_correction_factor NUMBER(22,3) := 0;
1677 l_general_los NUMBER(22,3) := 0;
1678 l_military_duration NUMBER(22,3) := 0;
1679 l_proc VARCHAR2(60) := g_package||'get_jub_mon_los';
1680 l_date_of_birth DATE;
1681 l_per_los_flag per_all_people_f.per_information10%TYPE;
1682 l_assg_los_type varchar2(1);
1683 l_entitlement pqh_situations.entitlement_value%TYPE :=0;
1684 l_emp_type per_all_assignments_f.employee_category%TYPE;
1685
1686
1687 CURSOR c_person_dob IS
1688 SELECT date_of_birth, nvl(per_information10,'N')
1689 FROM per_all_people_f
1690 WHERE person_id = p_person_id
1691 AND effective_start_date < p_assg_end_date
1692 AND effective_end_date > p_assg_start_date;
1693
1694 CURSOR c_assg_los_type IS
1695 SELECT '1'
1696 FROM per_assignment_extra_info
1697 WHERE assignment_id = p_assignment_id
1698 AND nvl(aei_information1,'x') = p_los_type
1699 AND information_type = 'DE_PQH_ASSG_LOS_INFO';
1700
1701 CURSOR c_18yrs_entitlements IS
1702 SELECT nvl(entitlement_value,0)
1703 FROM pqh_situations
1704 WHERE business_group_id = p_bg_id
1705 AND situation_type = 'PERSON'
1706 AND length_of_service = p_los_type
1707 AND situation = 'BEFORE_18'
1708 AND employee_type = l_emp_type
1709 AND p_assg_start_date BETWEEN effective_start_date AND NVL(effective_end_date,g_end_of_time)
1710 AND p_assg_end_date BETWEEN effective_start_date AND NVL(effective_end_date,g_end_of_time)
1711 AND entitlement_flag = 'Y';
1712
1713
1714 BEGIN
1715 hr_utility.set_location('Entering '||l_proc,1);
1716
1717 OPEN c_assg_los_type;
1718 FETCH c_assg_los_type INTO l_assg_los_type;
1719 CLOSE c_assg_los_type;
1720
1721 --
1722 -- If the length of service type is present in the Extra Info then the assignment
1723 -- is not taken for LOS calculation.
1724 --
1725
1726 IF l_assg_los_type IS NOT NULL THEN
1727 RETURN 0;
1728 END IF;
1729
1730 OPEN c_person_dob;
1731 FETCH c_person_dob INTO l_date_of_birth, l_per_los_flag;
1732 CLOSE c_person_dob;
1733
1734
1735 l_emp_type := get_employee_type(p_person_id => p_person_id,
1736 p_determination_date => p_assg_end_date);
1737
1738 OPEN c_18yrs_entitlements;
1739 FETCH c_18yrs_entitlements INTO l_entitlement;
1740 CLOSE c_18yrs_entitlements;
1741
1742 IF l_date_of_birth IS NOT NULL THEN
1743 IF Months_between(p_assg_start_date,l_date_of_birth)/12 < 18 THEN
1744 IF l_per_los_flag = 'Y' THEN
1745 l_start_date := p_assg_start_date;
1746 ELSE
1747 l_start_date := add_months(l_date_of_birth,18*12);
1748 END IF;
1749 ELSE
1750 l_start_date := p_assg_start_date;
1751 END IF;
1752 ELSE
1753 l_start_date := p_assg_start_date;
1754 END IF;
1755
1756 IF (p_assg_end_date - l_start_date) < 0 THEN
1757 RETURN 0;
1758 END IF;
1759 l_asg_duration := trunc(p_assg_end_date - l_start_date) * l_entitlement/100;
1760
1761 hr_utility.set_location(l_proc||' Assignment duration '||l_asg_duration,2);
1762
1763 -- findout the absence duration for the person during this period
1764 l_absent_duration := get_absent_period( p_bg_id => p_bg_id,
1765 p_person_id => p_person_id,
1766 p_assignment_id => p_assignment_id,
1767 p_los_type => p_los_type,
1768 p_start_date => l_start_date,
1769 p_end_date => p_assg_end_date);
1770
1771 hr_utility.set_location(l_proc||' Absent Duration '||l_absent_duration,6);
1772 -- collect the correction factor defined (if any) for the person
1773 /*l_correction_factor := get_correction_factor(p_person_id => p_person_id,
1774 p_los_type => p_los_type,
1775 p_effective_date => p_assg_end_date);
1776 hr_utility.set_location(l_proc||' Corrected Days '||l_correction_Factor,7);
1777 l_military_duration := get_de_military_service_period (p_bg_id => p_bg_id,
1778 p_person_id => p_person_id,
1779 p_los_type => p_los_type,
1780 p_start_date => l_start_date,
1781 p_end_date => p_assg_end_date);
1782 hr_utility.set_location(l_proc||' Military service Period '||l_military_duration,8); */
1783
1784 l_general_los := (l_asg_duration + 1) - l_absent_duration;
1785 hr_utility.set_location(l_proc||p_los_type||' Length of Service '||l_general_los,8);
1786
1787 RETURN l_general_los;
1788
1789 EXCEPTION
1790 When Others THEN
1791 hr_utility.set_location('Erroring out from '||l_proc,5);
1792 RAISE_Application_Error(-20001,SQLERRM);
1793 END get_jub_mon_length_of_service;
1794
1795 -- -----------------------------------------------------------------------*
1796 -- FUNCTION get_bda_length_of_service
1797 -- This function returns the length of service for BDA calculation of an employee
1798 -- -----------------------------------------------------------------------*
1799
1800 FUNCTION get_bda_length_of_service(p_bg_id IN per_all_organization_units.organization_id%TYPE,
1801 p_person_id IN per_all_people_f.person_id%TYPE,
1802 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1803 p_los_type IN VARCHAR2,
1804 p_assg_start_date IN DATE ,
1805 p_assg_end_date IN DATE
1806 )
1807 RETURN NUMBER IS
1808
1809 CURSOR c_assg_emp_catg IS
1810 SELECT employee_category, position_id
1811 FROM per_all_assignments_f
1812 WHERE person_id = p_person_id
1813 AND assignment_id = p_assignment_id
1814 AND effective_start_date = p_assg_start_date
1815 AND effective_end_date = decode(p_assg_end_date,trunc(sysdate),effective_end_date,p_assg_end_date);
1816
1817 CURSOR c_person_dob IS
1818 SELECT date_of_birth
1819 FROM per_all_people_f
1820 WHERE person_id = p_person_id
1821 AND effective_start_date <= p_assg_end_date
1822 AND effective_end_date >= p_assg_start_date;
1823
1824 CURSOR c_tariff_contract(p_position_id per_all_assignments_f.position_id%type) IS
1825 SELECT wkvr.tariff_contract_code
1826 FROM hr_all_positions_f pos, pqh_de_wrkplc_vldtn_vers wkvr
1827 WHERE pos.position_id = p_position_id
1828 AND pos.information1='WP'
1829 AND to_char(wkvr.wrkplc_vldtn_ver_id) = decode(pos.information6,'A',pos.information5,pos.information9)
1830 AND p_assg_end_date between pos.effective_start_date and pos.effective_end_date
1831 AND pos.business_group_id = wkvr.business_group_id;
1832
1833 CURSOR c_assg_los_type IS
1834 SELECT '1'
1835 FROM per_assignment_extra_info
1836 WHERE assignment_id = p_assignment_id
1837 AND nvl(aei_information1,'x') = p_los_type
1838 AND information_type = 'DE_PQH_ASSG_LOS_INFO';
1839
1840 l_employee_category per_all_assignments_f.employee_category%TYPE;
1841 l_position_id per_all_assignments_f.position_id%TYPE;
1842 l_tariff_contract_code pqh_de_wrkplc_vldtn_vers.tariff_contract_code%TYPE;
1843 l_date_of_birth DATE;
1844 l_dob_21 DATE;
1845 l_dob_31 DATE;
1846 l_dob_35 DATE;
1847 l_proc VARCHAR2(60) := g_package||'get_bda_los';
1848 l_days_betn_assg_35dob NUMBER;
1849 l_days_betn_35dob_31dob NUMBER;
1850 l_days_betn_assg_31dob NUMBER;
1851 l_postpone_bda NUMBER;
1852 l_bda_date DATE;
1853 l_asg_duration NUMBER(22,3) := 0;
1854 l_absent_duration NUMBER(22,3) := 0;
1855 l_bda_los NUMBER(22,3) := 0;
1856 l_assg_los_type VARCHAR2(1);
1857
1858
1859 BEGIN
1860 hr_utility.set_location('Entering '||l_proc,1);
1861
1862 OPEN c_assg_los_type;
1863 FETCH c_assg_los_type INTO l_assg_los_type;
1864 CLOSE c_assg_los_type;
1865
1866 --
1867 -- If the length of service type is present in the Extra Info then the assignment
1868 -- is not taken for LOS calculation.
1869 --
1870
1871 IF l_assg_los_type IS NOT NULL THEN
1872 RETURN 0;
1873 END IF;
1874
1875 OPEN c_assg_emp_catg;
1876 FETCH c_assg_emp_catg INTO l_employee_category, l_position_id;
1877 CLOSE c_assg_emp_catg;
1878
1879 hr_utility.set_location('Position id '|| l_position_id,1);
1880
1881 /* OPEN c_tariff_contract(l_position_id);
1882 FETCH c_tariff_contract INTO l_tariff_contract_code;
1883 CLOSE c_tariff_contract; */
1884
1885 hr_utility.set_location('Employee catg '||l_employee_category,2);
1886
1887 IF nvl(l_employee_category,'x') = 'BE' Then
1888 -- and nvl(l_tariff_contract_code,'x') = 'CS' THEN
1889 OPEN c_person_dob;
1890 FETCH c_person_dob INTO l_date_of_birth;
1891 CLOSE c_person_dob;
1892
1893 IF l_date_of_birth IS NULL THEN
1894 fnd_message.set_name('PER','HR_BE_DNP_INVALID_BIRTHDATE');
1895 fnd_message.raise_error;
1896 else
1897 --
1898 --Truncating the date of birth to the first of the month. Bug Fix 2419524
1899 --
1900 l_date_of_birth := trunc(l_date_of_birth, 'MM');
1901 END IF;
1902 --
1903 -- Get the 1st of the 21st Birthday
1904 --
1905 l_dob_21 := trunc(add_months(l_date_of_birth,(12 * 21)), 'MM');
1906
1907 --
1908 -- Get the 1st of the 31st Birthday
1909 --
1910 l_dob_31 := trunc(add_months(l_date_of_birth,(12 * 31)), 'MM');
1911 --
1912 -- Get the 1st of the 35th Birthday
1913 --
1914 l_dob_35 := trunc(add_months(l_date_of_birth,(12 * 35)), 'MM');
1915
1916
1917 IF (p_assg_start_date - l_date_of_birth) > 21 THEN
1918 IF (p_assg_start_date - l_dob_31) > 0 THEN
1919 IF (p_assg_start_date - l_dob_35) > 0 THEN
1920
1921 -- l_days_betn_assg_35dob := (p_assg_start_date - 1) - l_dob_35;
1922 l_days_betn_assg_35dob := Months_Between((p_assg_start_date - 1), l_dob_35);
1923
1924 -- l_days_betn_35dob_31dob := l_dob_35 - l_dob_31;
1925 l_days_betn_35dob_31dob := Months_Between(l_dob_35, l_dob_31);
1926 --
1927 -- Get the postponing BDA in months
1928 --
1929 -- l_postpone_bda := trunc(((l_days_betn_assg_35dob/2) + (l_days_betn_35dob_31dob/4))/30);
1930 --
1931 -- Add the postponing BDA months to the 1st of the 21st birthday
1932 --
1933 -- l_bda_date := add_months(l_dob_21,l_postpone_bda);
1934 l_bda_date := add_months(l_dob_21,trunc(((l_days_betn_assg_35dob/2) + (l_days_betn_35dob_31dob/4))));
1935
1936 ELSE
1937 -- l_days_betn_assg_31dob := (p_assg_start_date - 1) - l_dob_31;
1938 l_days_betn_assg_31dob := Months_Between((p_assg_start_date - 1), l_dob_31);
1939 --
1940 -- Get the postponing BDA in months
1941 --
1942 -- l_postpone_bda := trunc((l_days_betn_assg_31dob/4)/30);
1943 --
1944 -- Add the postponing BDA months to the 1st of the 21st birthday
1945 --
1946 l_bda_date := add_months(l_dob_21,trunc(l_days_betn_assg_31dob/4));
1947 END IF;
1948
1949 l_asg_duration := trunc(p_assg_end_date - l_bda_date);
1950
1951 hr_utility.set_location(l_proc||' Assignment duration '||l_asg_duration,2);
1952
1953 l_absent_duration := get_absent_period( p_bg_id => p_bg_id,
1954 p_person_id => p_person_id,
1955 p_assignment_id => p_assignment_id,
1956 p_los_type => p_los_type,
1957 p_start_date => l_bda_date,
1958 p_end_date => p_assg_end_date);
1959
1960 l_bda_los := (l_asg_duration + 1) - l_absent_duration;
1961 hr_utility.set_location(l_proc||p_los_type||' Length of Service '||l_bda_los,8);
1962 RETURN l_bda_los;
1963
1964 ELSIF (p_assg_start_date >= l_dob_21) and (p_assg_start_date <= l_dob_31) THEN
1965 RETURN 0;
1966 END IF;
1967 RETURN 0;
1968 END IF;
1969
1970 ELSE
1971 RETURN 0;
1972 END IF;
1973
1974 END get_bda_length_of_service;
1975
1976 --
1977 -- Function to get the current grade for a person
1978 --
1979 FUNCTION get_current_grade(p_person_id IN per_all_people_f.person_id%TYPE)
1980 RETURN NUMBER IS
1981
1982 CURSOR c_curr_grade IS
1983 SELECT grade_id
1984 FROM per_all_assignments_f asg
1985 WHERE asg.person_id = p_person_id
1986 AND trunc(sysdate) between effective_start_date and effective_end_date;
1987
1988 l_curr_grade_id per_assignments_f.grade_id%TYPE;
1989
1990 BEGIN
1991 --
1992 -- Fetch the grade in the current assignment
1993 --
1994 OPEN c_curr_grade;
1995 FETCH c_curr_grade INTO l_curr_grade_id;
1996 CLOSE c_curr_grade;
1997
1998 RETURN l_curr_grade_id;
1999
2000 END;
2001
2002 --
2003 -- This function return the Length of service in a Grade for an Employee - German PS
2004 --
2005
2006 FUNCTION get_de_grade_length_of_service(p_bg_id IN per_all_organization_units.organization_id%TYPE,
2007 p_person_id IN per_all_people_f.person_id%TYPE,
2008 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
2009 p_los_type IN VARCHAR2,
2010 p_assg_start_date IN DATE ,
2011 p_assg_end_date IN DATE
2012 )
2013 RETURN NUMBER IS
2014
2015
2016 CURSOR csr_asg_grade IS
2017 SELECT asg.grade_id
2018 FROM per_all_assignments_f asg
2019 WHERE asg.person_id = p_person_id
2020 AND asg.assignment_id = p_assignment_id
2021 AND asg.effective_start_date = p_assg_start_date
2022 AND asg.effective_end_date = decode(p_assg_end_date,trunc(sysdate),asg.effective_end_date,p_assg_end_date);
2023
2024 CURSOR c_curr_grade IS
2025 SELECT grade_id
2026 FROM per_all_assignments_f asg
2027 WHERE asg.person_id = p_person_id
2028 AND trunc(sysdate) between effective_start_date and effective_end_date;
2029
2030 CURSOR c_assg_los_type IS
2031 SELECT '1'
2032 FROM per_assignment_extra_info
2033 WHERE assignment_id = p_assignment_id
2034 AND nvl(aei_information1,'x') = p_los_type
2035 AND information_type = 'DE_PQH_ASSG_LOS_INFO';
2036
2037 l_grade_id per_assignments_f.grade_id%TYPE;
2038 l_curr_grade_id per_assignments_f.grade_id%TYPE;
2039 l_proc VARCHAR2(60) := g_package||'get_grade_LOS';
2040 l_grade_los NUMBER(22,3) := 0;
2041 l_absent_duration NUMBER(22,3) := 0;
2042 l_grade_entitlements NUMBER(22,3) := 0;
2043 l_correction_factor NUMBER(22,3) := 0;
2044 l_military_duration NUMBER(22,3) := 0;
2045 l_net_grade_los NUMBER(22,3) := 0;
2046 l_assg_los_type VARCHAR2(1);
2047 BEGIN
2048 hr_utility.set_location('Entering '||l_proc,1);
2049 OPEN csr_asg_grade;
2050 FETCH csr_asg_grade INTO l_grade_id;
2051 CLOSE csr_asg_grade;
2052
2053 --
2054 -- If the length of service type is present in the Extra Info then the assignment
2055 -- is not taken for LOS calculation.
2056 --
2057
2058 OPEN c_assg_los_type;
2059 FETCH c_assg_los_type INTO l_assg_los_type;
2060 CLOSE c_assg_los_type;
2061
2062 IF l_assg_los_type IS NOT NULL THEN
2063 RETURN 0;
2064 END IF;
2065
2066 IF l_grade_id IS NOT NULL THEN
2067 l_curr_grade_id := get_current_grade(p_person_id);
2068 --
2069 -- Check if the Grade is the current assignment Grade.
2070 --
2071 IF l_grade_id <> nvl(l_curr_grade_id,-1) THEN
2072 RETURN l_grade_los;
2073 END IF;
2074 l_grade_los := p_assg_end_date - p_assg_start_date;
2075 hr_utility.set_location(l_proc||' Grade Duration '||l_grade_los,2);
2076
2077
2078 -- findout the absence duration for the person during this period
2079 l_absent_duration := get_absent_period( p_bg_id => p_bg_id,
2080 p_person_id => p_person_id,
2081 p_assignment_id => p_assignment_id,
2082 p_los_type => '40',
2083 p_start_date => p_assg_start_date,
2084 p_end_date => p_assg_end_date);
2085
2086 --Approximately proportionating the Absence duration to consider the Parttime periods
2087 hr_utility.set_location(l_proc||' Absent Duration '||l_absent_duration,6);
2088 -- collect the correction factor defined (if any) for the person
2089 /*l_correction_factor := get_correction_factor(p_person_id => p_person_id,
2090 p_los_type => '40',
2091 p_effective_date => p_assg_end_date);
2092 hr_utility.set_location(l_proc||' Corrected Days '||l_correction_factor,7);
2093 l_military_duration := get_de_military_service_period (p_bg_id => p_bg_id,
2094 p_person_id => p_person_id,
2095 p_los_type => '40',
2096 p_start_date => p_assg_start_date,
2097 p_end_date => p_assg_end_date);
2098 hr_utility.set_location(l_proc||' Military service Period '||l_military_duration,8); */
2099
2100 l_net_grade_los := l_net_grade_los + (l_grade_los + 1 - l_absent_duration);
2101
2102 END IF;
2103 hr_utility.set_location(l_proc||' Net Grade LOS '||l_net_grade_los,8);
2104 RETURN l_net_grade_los;
2105 EXCEPTION
2106 When Others THEN
2107 hr_utility.set_location('Erroring out from '||l_proc,5);
2108 RAISE_Application_Error(-20001,SQLERRM);
2109 END get_de_grade_length_of_service;
2110
2111 --
2112 -- This Function returns the step in the current assignment of a person
2113 --
2114 FUNCTION get_current_step(p_person_id IN per_all_people_f.person_id%TYPE)
2115 RETURN NUMBER IS
2116
2117 Cursor c_current_step IS
2118 SELECT spp.step_id
2119 FROM per_spinal_point_placements_f spp, per_all_assignments_f asg
2120 WHERE asg.person_id = p_person_id
2121 AND spp.assignment_id = asg.assignment_id
2122 AND trunc(sysdate) BETWEEN spp.effective_start_date and spp.effective_end_date;
2123
2124 l_curr_step_id per_spinal_point_placements_f.step_id%TYPE;
2125
2126 BEGIN
2127 OPEN c_current_step;
2128 FETCH c_current_step INTO l_curr_step_id;
2129 CLOSE c_current_step;
2130
2131 RETURN l_curr_step_id;
2132 END;
2133
2134 -- -----------------------------------------------------------------------*
2135 -- FUNCTION get_step_length_of_service
2136 -- This function returns the length of service in the current grade step for the employee
2137 -- -----------------------------------------------------------------------*
2138
2139 FUNCTION get_de_step_length_of_service (p_bg_id IN per_all_organization_units.organization_id%TYPE,
2140 p_person_id IN per_all_people_f.person_id%TYPE,
2141 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
2142 p_los_type IN VARCHAR2,
2143 p_assg_start_date IN DATE ,
2144 p_assg_end_date IN DATE
2145 )
2146 RETURN NUMBER IS
2147 Cursor Csr_asg_step IS
2148 SELECT spp.step_id,
2149 spp.effective_start_date,
2150 spp.effective_end_date
2151 FROM per_spinal_point_placements_f spp, per_all_assignments_f asg
2152 WHERE asg.person_id = p_person_id
2153 AND asg.assignment_id = p_assignment_id
2154 AND spp.assignment_id = asg.assignment_id
2155 AND p_assg_end_date BETWEEN spp.effective_start_date and spp.effective_end_date;
2156
2157 CURSOR c_assg_los_type IS
2158 SELECT '1'
2159 FROM per_assignment_extra_info
2160 WHERE assignment_id = p_assignment_id
2161 AND nvl(aei_information1,'x') = p_los_type
2162 AND information_type = 'DE_PQH_ASSG_LOS_INFO';
2163
2164 l_assignment_id per_assignments_f.assignment_id%TYPE;
2165 l_step_id per_spinal_point_placements_f.step_id%TYPE;
2166 l_curr_step_id per_spinal_point_placements_f.step_id%TYPE;
2167 l_start_date DATE;
2168 l_end_date DATE;
2169 l_step_los NUMBER(22,3) := 0;
2170 l_absent_duration NUMBER(22,3) := 0;
2171 l_step_entitlements NUMBER(22,3) := 0;
2172 l_correction_factor NUMBER(22,3) := 0;
2173 l_military_duration NUMBER(22,3) := 0;
2174 l_net_step_los NUMBER(22,3) := 0;
2175 l_assg_los_type VARCHAR2(1);
2176 l_proc varchar2(60) := g_package||'get_step_LOS';
2177
2178
2179 BEGIN
2180 hr_utility.set_location('Entering '||l_proc,1);
2181 OPEN csr_asg_step;
2182 FETCH csr_asg_step INTO l_step_id, l_start_date, l_end_date;
2183 CLOSE csr_asg_step;
2184
2185 OPEN c_assg_los_type;
2186 FETCH c_assg_los_type INTO l_assg_los_type;
2187 CLOSE c_assg_los_type;
2188
2189 --
2190 -- If the length of service type is present in the Extra Info then the assignment
2191 -- is not taken for LOS calculation.
2192 --
2193
2194 IF l_assg_los_type IS NOT NULL THEN
2195 RETURN 0;
2196 END IF;
2197
2198 IF l_step_id IS NOT NULL THEN
2199 l_curr_step_id := get_current_step(p_person_id);
2200
2201 IF l_step_id <> l_curr_step_id THEN
2202 RETURN l_step_los;
2203 END IF;
2204
2205 l_step_los := l_end_date - l_start_date;
2206
2207 hr_utility.set_location(l_proc||' Step Duration '||l_step_los,2);
2208
2209 -- findout the absence duration for the person during this period
2210 l_absent_duration := get_absent_period( p_bg_id => p_bg_id,
2211 p_person_id => p_person_id,
2212 p_assignment_id => l_assignment_id,
2213 p_los_type => '50',
2214 p_start_date => l_start_date,
2215 p_end_date => l_end_date);
2216
2217 hr_utility.set_location(l_proc||' Absent Duration '||l_absent_duration,6);
2218
2219 -- collect the correction factor defined (if any) for the person
2220 /*l_correction_factor := get_correction_factor(p_person_id => p_person_id,
2221 p_los_type => '50',
2222 p_effective_date => l_end_date);
2223 hr_utility.set_location(l_proc||' Correct Days '||l_correction_factor,7); /
2224
2225 -- get the military service duration, if entitled for this LOS calculation
2226 l_military_duration := get_de_military_service_period (p_bg_id => p_bg_id,
2227 p_person_id => p_person_id,
2228 p_los_type => '50',
2229 p_start_date => l_start_date,
2230 p_end_date => l_end_date);
2231 hr_utility.set_location(l_proc||' Military service Period '||l_military_duration,8); */
2232
2233 l_net_step_los := l_net_step_los + (l_step_los + 1 - l_absent_duration);
2234 hr_utility.set_location(l_proc||' LOS on Step '||l_net_step_los,8);
2235 END IF;
2236 RETURN l_net_step_los;
2237 EXCEPTION
2238 When Others THEN
2239 hr_utility.set_location('Erroring out from '||l_proc,5);
2240 RAISE_Application_Error(-20001,SQLERRM);
2241
2242 END get_de_step_length_of_service;
2243
2244 --
2245 -- This function checks whether the Employee's previous job length of service has
2246 -- to be considered or not.
2247 --
2248
2249 FUNCTION check_prev_job_info (p_prev_job_id per_previous_jobs.previous_job_id%TYPE)
2250 RETURN VARCHAR2 IS
2251 l_los_flag varchar2(150) := 'N';
2252 CURSOR c_los_flag IS
2253 SELECT nvl(pjo_information4,'N')
2254 FROM per_previous_jobs
2255 WHERE previous_job_id = p_prev_job_id;
2256 BEGIN
2257 OPEN c_los_flag;
2258 FETCH c_los_flag INTO l_los_flag;
2259 CLOSE c_los_flag;
2260 RETURN l_los_flag;
2261
2262 END;
2263
2264 -- -----------------------------------------------------------------------*
2265 -- FUNCTION get_de_assg_length_of_service
2266 -- This function returns the length of service in the current assignment for the employee
2267 -- -----------------------------------------------------------------------*
2268
2269 FUNCTION get_de_assg_length_of_service( p_bg_id IN per_all_organization_units.organization_id%TYPE,
2270 p_person_id IN per_all_people_f.person_id%TYPE,
2271 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
2272 p_los_type IN VARCHAR2,
2273 p_assg_start_date IN DATE ,
2274 p_assg_end_date IN DATE)
2275 RETURN NUMBER IS
2276
2277
2278 l_proc varchar2(60) := g_package||'get_de_assg_LOS';
2279
2280 CURSOR c_assg_los_type IS
2281 SELECT '1'
2282 FROM per_assignment_extra_info
2283 WHERE assignment_id = p_assignment_id
2284 AND nvl(aei_information1,'x') = p_los_type
2285 AND information_type = 'DE_PQH_ASSG_LOS_INFO';
2286
2287
2288 l_assignment_id per_assignments_f.assignment_id%TYPE;
2289 l_position_id per_assignments_f.position_id%TYPE;
2290 l_position_los NUMBER(22,3) := 0;
2291 l_absent_duration NUMBER(22,3) := 0;
2292 l_prev_employment NUMBER(22,3) := 0;
2293 l_military_duration NUMBER(22,3) := 0;
2294 l_assg_los_type VARCHAR2(1);
2295
2296 BEGIN
2297 hr_utility.set_location('Entering '||l_proc,1);
2298 -- OPEN csr_asg_position;
2299 -- FETCH csr_asg_position INTO l_assignment_id, l_position_id;
2300 -- CLOSE csr_asg_position;
2301
2302 OPEN c_assg_los_type;
2303 FETCH c_assg_los_type INTO l_assg_los_type;
2304 CLOSE c_assg_los_type;
2305
2306 --
2307 -- If the length of service type is present in the Extra Info then the assignment
2308 -- is not taken for LOS calculation.
2309 --
2310
2311 IF l_assg_los_type IS NOT NULL THEN
2312 RETURN 0;
2313 END IF;
2314
2315 l_position_los := trunc(p_assg_end_date - p_assg_start_date);
2316 hr_utility.set_location(l_proc||' Position Duration '||l_position_los,2);
2317
2318 -- findout the absence duration for the person during this period
2319 l_absent_duration := get_absent_period( p_bg_id => p_bg_id,
2320 p_person_id => p_person_id,
2321 p_assignment_id => l_assignment_id,
2322 p_los_type => p_los_type,
2323 p_start_date => p_assg_start_date,
2324 p_end_date => p_assg_start_date);
2325 hr_utility.set_location(l_proc||' Absent Duration '||l_absent_duration,6);
2326
2327 l_position_los := (l_position_los + 1)- l_absent_duration;
2328
2329 hr_utility.set_location(l_proc||' Net Position LOS '||l_position_los,8);
2330
2331 RETURN l_position_los;
2332 EXCEPTION
2333 When Others THEN
2334 hr_utility.set_location('Erroring out from '||l_proc,5);
2335 RAISE_Application_Error(-20001,SQLERRM);
2336 END get_de_assg_length_of_service;
2337 --
2338 -- This function returns the length of service for a person
2339 -- This is called in the Report for Period of Employment - DEPS
2340 --
2341
2342 FUNCTION get_length_of_service( p_bg_id IN per_all_organization_units.organization_id%TYPE,
2343 p_person_id IN per_all_people_f.person_id%TYPE,
2344 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE DEFAULT NULL,
2345 p_prev_job_id IN per_previous_jobs.previous_job_id%TYPE DEFAULT NULL,
2346 p_los_type IN VARCHAR2,
2347 p_assg_start_date IN DATE ,
2348 p_assg_end_date IN DATE
2349 ) RETURN VARCHAR2 is
2350
2351 l_end_date DATE := p_assg_end_date;
2352 l_los_days NUMBER := 0;
2353 l_los_years NUMBER := 0;
2354 l_los_months NUMBER := 0;
2355 l_los_return VARCHAR2(240);
2356 l_adj_service_date DATE;
2357 l_date_of_birth DATE;
2358 l_start_date DATE;
2359 l_exists VARCHAR2(2) := '0';
2360 l_correction_factor NUMBER(22,3) := 0;
2361 l_prev_grade_id per_previous_jobs.pjo_information2%TYPE;
2362 l_prev_step_id per_previous_jobs.pjo_information3%TYPE;
2363 l_curr_grade_id per_assignments_f.grade_id%TYPE;
2364 l_curr_step_id per_spinal_point_placements_f.step_id%TYPE;
2365 l_emp_type per_previous_jobs.employment_category%TYPE := 'x';
2366 l_employer_type per_previous_employers.employer_type%TYPE := 'x';
2367 l_prev_empl_id per_previous_employers.previous_employer_id%TYPE;
2368 l_person_id per_all_people_f.person_id%TYPE;
2369 l_bg_id per_all_organization_units.organization_id%TYPE := p_bg_id;
2370 l_entitlement pqh_situations.entitlement_value%TYPE :=0;
2371 l_assignment_id per_all_assignments_f.assignment_id%TYPE := p_assignment_id;
2372 l_proc VARCHAR2(60) := g_package||'get_length_of_service';
2373 l_primary_flag per_all_assignments_f.primary_flag%TYPE;
2374
2375 CURSOR CSR_validate_person IS
2376 SELECT '1'
2377 FROM per_all_people_f
2378 WHERE person_id = p_person_id
2379 AND (p_assg_start_date BETWEEN effective_start_date AND effective_end_date
2380 or l_end_date BETWEEN effective_start_date AND effective_end_date);
2381
2382 CURSOR CSR_validate_assignment IS
2383 SELECT person_id, primary_flag
2384 FROM per_all_assignments_f
2385 WHERE assignment_id = p_assignment_id
2386 AND (p_assg_start_date BETWEEN effective_start_date AND effective_end_date
2387 or l_end_date BETWEEN effective_start_date AND effective_end_date);
2388
2389 CURSOR c_prev_job_info IS
2390 SELECT (end_date - start_date) los_days, previous_employer_id, pjo_information2, pjo_information3
2391 FROM per_previous_jobs
2392 WHERE previous_job_id = p_prev_job_id
2393 AND start_date = p_assg_start_date
2394 AND end_date = l_end_date;
2395
2396 CURSOR c_prev_employers IS
2397 SELECT nvl(employer_type,'x')
2398 FROM per_previous_employers
2399 WHERE previous_employer_id = l_prev_empl_id;
2400
2401 CURSOR c_person_dob IS
2402 SELECT date_of_birth
2403 FROM per_all_people_f
2404 WHERE person_id = p_person_id
2405 AND trunc(sysdate) BETWEEN effective_start_date AND effective_end_date;
2406
2407 CURSOR c_empl_entitlements IS
2408 SELECT NVL(entitlement_value,0)
2409 FROM pqh_situations
2410 WHERE business_group_id = p_bg_id
2411 AND situation_type = 'EMPLOYMENT'
2412 AND length_of_service = p_los_type
2413 AND situation = decode(l_employer_type,'CM','C',l_employer_type)
2414 AND employee_type = l_emp_type
2415 AND p_assg_start_date BETWEEN effective_start_date AND NVL(effective_end_date,g_end_of_time)
2416 AND l_end_date BETWEEN effective_start_date AND NVL(effective_end_date,g_end_of_time)
2417 AND entitlement_flag = 'Y';
2418
2419 CURSOR c_emp_catg IS
2420 SELECT nvl(employment_category,'x')
2421 FROM per_previous_jobs
2422 WHERE previous_job_id = p_prev_job_id;
2423
2424 BEGIN
2425
2426 hr_utility.set_location('Entering '||l_proc,1);
2427
2428 IF p_assg_end_date = g_end_of_time THEN
2429 l_end_date := trunc(sysdate);
2430 END IF;
2431
2432 IF p_bg_id IS NULL THEN
2433 hr_api.mandatory_arg_error(p_api_name => l_proc,
2434 p_argument => 'Business Group Id',
2435 p_argument_value => p_bg_id);
2436 ELSE
2437 hr_api.validate_bus_grp_id(p_business_group_id => p_bg_id);
2438 END IF;
2439
2440 IF p_person_id IS NULL THEN
2441 hr_api.mandatory_arg_error(p_api_name => l_proc,
2442 p_argument => 'Person Id',
2443 p_argument_value => p_person_id);
2444
2445 ELSIF p_person_id IS NOT NULL AND p_prev_job_id IS NULL THEN
2446
2447 OPEN Csr_validate_person;
2448 FETCH Csr_validate_person INTO l_exists;
2449 CLOSE Csr_validate_person;
2450 IF l_exists = '0' THEN
2451 fnd_message.set_name('PQH','PQH_INVALID_PARAM_VALUE');
2452 fnd_message.set_token('VALUE',to_char(p_person_id));
2453 fnd_message.set_token('PARAMETER','p_person_id');
2454 fnd_message.raise_error;
2455 END IF;
2456 END IF;
2457
2458 IF p_assignment_id IS NOT NULL THEN
2459 OPEN Csr_validate_assignment;
2460 FETCH Csr_validate_assignment INTO l_person_id, l_primary_flag;
2461 IF Csr_validate_assignment%NOTFOUND THEN
2462 CLOSE Csr_validate_assignment;
2463 fnd_message.set_name('PQH','PQH_INVALID_PARAM_VALUE');
2464 fnd_message.set_token('VALUE',to_char(p_assignment_id));
2465 fnd_message.set_token('PARAMETER','p_assignment_id');
2466 fnd_message.raise_error;
2467 END IF;
2468 CLOSE Csr_validate_assignment;
2469 IF l_person_id <> NVL(p_person_id,-999999) THEN
2470 fnd_message.set_name('PQH','PQH_INVALID_PARAM_VALUE');
2471 fnd_message.set_token('VALUE',to_char(p_person_id));
2472 fnd_message.set_token('PARAMETER','p_person_id');
2473 fnd_message.raise_error;
2474 END IF;
2475 l_person_id := NVL(p_person_id,l_person_id);
2476 END IF;
2477
2478 IF p_los_type IS NULL THEN
2479 hr_api.mandatory_arg_error(p_api_name => l_proc,
2480 p_argument => 'Length of Service Type',
2481 p_argument_value => p_los_type);
2482 ELSE
2483 IF hr_api.NOT_EXISTS_IN_HR_LOOKUPS(p_effective_date => l_end_date,
2484 p_lookup_type => 'FR_PQH_LENGTH_OF_SERVICE_TYPE',
2485 p_lookup_code => p_los_type) THEN
2486 fnd_message.set_name('PQH','PQH_INVALID_PARAM_VALUE');
2487 fnd_message.set_token('VALUE',p_los_type);
2488 fnd_message.set_token('PARAMETER','p_los_type');
2489 END IF;
2490 END IF;
2491
2492 IF p_prev_job_id IS NOT NULL THEN
2493 IF p_los_type in ('20','40','50','70','90') THEN
2494
2495 OPEN c_emp_catg;
2496 FETCH c_emp_catg INTO l_emp_type;
2497 CLOSE c_emp_catg;
2498
2499 -- Check if the LOS flag is 'Yes' in Previous Employment
2500 IF check_prev_job_info(p_prev_job_id) = 'Y' THEN
2501 OPEN c_prev_job_info;
2502 FETCH c_prev_job_info into l_los_days, l_prev_empl_id, l_prev_grade_id, l_prev_step_id;
2503 CLOSE c_prev_job_info;
2504
2505 --
2506 -- Check whether the current grade is the same grade in the previous job.
2507 --
2508
2509 IF p_los_type = '40' THEN
2510 l_curr_grade_id := get_current_grade(p_person_id);
2511
2512 IF l_prev_grade_id <> l_curr_grade_id THEN
2513 RETURN l_los_days;
2514 END IF;
2515 END IF;
2516
2517 --
2518 -- Check whether the current step is the same step in the previous job.
2519 --
2520
2521 IF p_los_type = '50' THEN
2522 l_curr_step_id := get_current_step(p_person_id);
2523
2524 IF l_prev_step_id <> l_curr_step_id THEN
2525 RETURN l_los_days;
2526 END IF;
2527 END IF;
2528
2529 OPEN c_prev_employers;
2530 FETCH c_prev_employers INTO l_employer_type;
2531 CLOSE c_prev_employers;
2532
2533 OPEN c_empl_entitlements;
2534 FETCH c_empl_entitlements INTO l_entitlement;
2535 CLOSE c_empl_entitlements;
2536
2537 OPEN c_person_dob;
2538 FETCH c_person_dob INTO l_date_of_birth;
2539 CLOSE c_person_dob;
2540
2541 IF l_date_of_birth IS NOT NULL THEN
2542 IF months_between(p_assg_start_date,l_date_of_birth)/12 < 18 THEN
2543 l_start_date := add_months(l_date_of_birth,18*12);
2544 l_los_days := (p_assg_end_date - l_start_date);
2545 END IF;
2546 END IF;
2547
2548 l_los_days := (l_los_days + 1) * (l_entitlement/100);
2549
2550 END IF;
2551 END IF;
2552
2553 ELSE
2554 IF l_primary_flag = 'Y' THEN
2555 IF p_los_type = '10' THEN -- Length of service with Current Employer
2556
2557 IF l_assignment_id IS NOT NULL THEN
2558 l_los_days := get_de_pub_length_of_service( p_bg_id => l_bg_id,
2559 p_person_id => l_person_id,
2560 p_assignment_id => l_assignment_id,
2561 p_los_type => p_los_type,
2562 p_assg_start_date => p_assg_start_date,
2563 p_assg_end_date => l_end_date);
2564 END IF;
2565
2566 ELSIF p_los_type = '20' THEN -- General Length of service and Length of service in Public Service
2567 l_los_days := get_de_pub_length_of_service( p_bg_id => l_bg_id,
2568 p_person_id => l_person_id,
2569 p_assignment_id => l_assignment_id,
2570 p_los_type => p_los_type,
2571 p_assg_start_date => p_assg_start_date,
2572 p_assg_end_date => l_end_date);
2573
2574
2575 ELSIF p_los_type = '40' THEN -- Length of Service in Current Grade
2576 l_los_days := get_de_grade_length_of_service(p_bg_id => p_bg_id,
2577 p_person_id => l_person_id,
2578 p_assignment_id => l_assignment_id,
2579 p_los_type => p_los_type,
2580 p_assg_start_date => p_assg_start_date,
2581 p_assg_end_date => l_end_date);
2582
2583 ELSIF p_los_type = '50' THEN -- Length of Service in Current Step
2584 l_los_days := get_de_step_length_of_service(p_bg_id => p_bg_id,
2585 p_person_id => l_person_id,
2586 p_assignment_id => l_assignment_id,
2587 p_los_type => p_los_type,
2588 p_assg_start_date => p_assg_start_date,
2589 p_assg_end_date => l_end_date);
2590
2591
2592 ELSIF p_los_type = '70' THEN -- Length of Service for Jubilee Money
2593 l_los_days := get_jub_mon_length_of_service(p_bg_id => l_bg_id,
2594 p_person_id => l_person_id,
2595 p_assignment_id => l_assignment_id,
2596 p_los_type => p_los_type,
2597 p_assg_start_date => p_assg_start_date,
2598 p_assg_end_date => l_end_date);
2599
2600 ELSIF p_los_type = '80' THEN -- Length of service for BDA Calculation
2601 l_los_days := get_bda_length_of_service(p_bg_id => l_bg_id,
2602 p_person_id => l_person_id,
2603 p_assignment_id => l_assignment_id,
2604 p_los_type => p_los_type,
2605 p_assg_start_date => p_assg_start_date,
2606 p_assg_end_date => l_end_date);
2607 ELSIF p_los_type = '90' THEN -- Length of service in Assignment
2608 l_los_days := get_de_assg_length_of_service(p_bg_id => l_bg_id,
2609 p_person_id => l_person_id,
2610 p_assignment_id => l_assignment_id,
2611 p_los_type => p_los_type,
2612 p_assg_start_date => p_assg_start_date,
2613 p_assg_end_date => l_end_date);
2614 END IF;
2615 END IF;
2616 END IF;
2617
2618 l_adj_service_date := l_end_date - l_los_days;
2619
2620 hr_utility.set_location(l_proc||' Adjusted Service Date '||To_Char(l_adj_service_date,'DD-MM-RRRR'),3);
2621
2622 /* IF nvl(l_los_days,0) < 365 THEN
2623 l_los_return := round(to_char(nvl(l_los_days,0)))|| ' Days ';
2624 ELSE
2625 --
2626 -- Return the Length of Service in Years and Days.
2627 --
2628 l_los_return := trunc(nvl(l_los_days,0)/365) || ' Years ' || round(substr((nvl(l_los_days,0)/365),instr((nvl(l_los_days,0)/365),'.',1)) * 365) || ' Days ';
2629 l_los_return := trunc(months_between(l_end_date,(l_end_date-l_los_days))/12)
2630
2631 END IF; */
2632
2633 l_los_years := months_between(l_end_date,l_adj_service_date)/12;
2634
2635 If instr(l_los_years,'.',1) <> 0 Then
2636 l_los_months := substr(l_los_years,instr(l_los_years,'.',1)) * 12;
2637 End If;
2638
2639 IF trunc(l_los_years) = 0 and trunc(l_los_months) = 0 THEN
2640 l_los_return := 0||'/'||0||'/'||l_los_days;
2641 ELSE
2642 l_los_days := round(substr(l_los_months,instr(l_los_months,'.',1)) * 31);
2643 l_los_return := trunc(l_los_years)||'/'||trunc(l_los_months)||'/'||l_los_days;
2644 END IF;
2645
2646 RETURN l_los_return;
2647
2648 END get_length_of_service;
2649
2650 FUNCTION get_de_correction_factor(p_person_id IN per_all_people_f.person_id%TYPE,
2651 p_los_type IN hr_lookups.lookup_code%TYPE,
2652 p_effective_date IN DATE)
2653 RETURN VARCHAR2 IS
2654
2655 CURSOR c_correction_factor IS
2656 SELECT nvl(pei.pei_information4,'0')
2657 FROM per_people_extra_info pei
2658 WHERE pei.person_id = p_person_id
2659 AND pei.information_type ='DE_PQH_POE_INFO'
2660 AND pei.pei_information1 = p_los_type
2661 AND p_effective_date BETWEEN fnd_date.canonical_to_date(pei.pei_information2)
2662 AND NVL(fnd_date.canonical_to_date(pei.pei_information3),g_end_of_time);
2663
2664 l_correction_factor per_people_extra_info.pei_information4%TYPE;
2665
2666 BEGIN
2667
2668 OPEN c_correction_factor;
2669 FETCH c_correction_factor INTO l_correction_factor;
2670 CLOSE c_correction_factor;
2671 RETURN (nvl(l_correction_factor,'0'));
2672
2673 END get_de_correction_factor;
2674
2675 FUNCTION get_corps_name (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
2676 p_bg_id IN per_all_organization_units.organization_id%TYPE)
2677 RETURN VARCHAR2 IS
2678
2679 cursor csr_corps is
2680 select segment7 from hr_soft_coding_keyflex where id_flex_num in
2681 (select id_flex_num from fnd_id_flex_structures fifs
2682 where id_flex_structure_code = 'FR_STATUTORY_INFO.'
2683 and fifs.application_id = 800 and fifs.id_flex_code = 'SCL')
2684 and soft_coding_keyflex_id in
2685 (select soft_coding_keyflex_id from per_all_assignments_f where
2686 assignment_id = p_assignment_id
2687 and business_group_id = p_bg_id);
2688 l_corps_id number(20);
2689 l_corps_name varchar2(100);
2690 BEGIN
2691 for c_corps in csr_corps loop
2692 l_corps_id := to_number(c_corps.segment7);
2693 end loop;
2694
2695 if l_corps_id is not null then
2696 select name into l_corps_name from pqh_corps_definitions
2697 where corps_definition_id = l_corps_id;
2698 end if;
2699
2700 return l_corps_name;
2701 END;
2702
2703 FUNCTION get_corps_name (p_corps_id IN pqh_corps_definitions.corps_definition_id%TYPE)
2704 RETURN VARCHAR2 IS
2705 l_corps_name varchar2(100);
2706 BEGIN
2707 if p_corps_id is not null then
2708 select name into l_corps_name from pqh_corps_definitions
2709 where corps_definition_id = p_corps_id;
2710 end if;
2711 return l_corps_name;
2712 END;
2713
2714 FUNCTION get_grade_name (p_grade_id IN per_grades.grade_id%TYPE)
2715 RETURN VARCHAR2 IS
2716 l_grade_name varchar2(100);
2717 BEGIN
2718 if p_grade_id is not null then
2719 select name into l_grade_name from per_grades_vl where
2720 grade_id = p_grade_id;
2721 end if;
2722 return l_grade_name;
2723 END;
2724 --
2725 FUNCTION get_date_diff_for_display (
2726 p_start_date IN DATE,
2727 p_end_date IN DATE DEFAULT SYSDATE
2728 )
2729 RETURN VARCHAR2
2730 IS
2731 l_display_los VARCHAR2 (200);
2732 l_yy NUMBER (5);
2733 l_mm NUMBER (5);
2734 l_dd NUMBER (5);
2735 l_temp_los_mm NUMBER (22, 3);
2736 l_proc VARCHAR2 (60)
2737 := g_package || 'get_date_diff_for_display';
2738 BEGIN
2739 hr_utility.set_location (l_proc || ' Entering', 10);
2740
2741 IF (p_start_date IS NOT NULL AND p_end_date IS NOT NULL)
2742 THEN
2743 l_temp_los_mm := MONTHS_BETWEEN (p_end_date, p_start_date);
2744 l_yy := NVL ((l_temp_los_mm - MOD (l_temp_los_mm, 12)) / 12, 0);
2745 l_temp_los_mm := l_temp_los_mm - l_yy * 12;
2746 l_mm := NVL (TRUNC (l_temp_los_mm, 0), 0);
2747 l_dd :=
2748 NVL ( p_end_date
2749 - TRUNC (ADD_MONTHS (p_start_date, (l_mm + l_yy * 12))),
2750 0
2751 );
2752 l_display_los :=
2753 NVL (l_yy, 0)
2754 || ' '
2755 || hr_general.decode_lookup ('QUALIFYING_UNITS', 'Y')
2756 || ' '
2757 || NVL (l_mm, 0)
2758 || ' '
2759 || hr_general.decode_lookup ('QUALIFYING_UNITS', 'M')
2760 || ' '
2761 || l_dd
2762 || ' '
2763 || hr_general.decode_lookup ('QUALIFYING_UNITS', 'D');
2764 END IF;
2765
2766 hr_utility.set_location (l_proc || l_display_los, 15);
2767 hr_utility.set_location (l_proc || ' Leaving', 20);
2768 RETURN l_display_los;
2769 END get_date_diff_for_display;
2770
2771 --
2772
2773 END pqh_length_of_service_pkg;
[Home] [Help]