[Home] [Help]
PACKAGE BODY: APPS.PQH_FR_SPEED
Source
1 PACKAGE BODY PQH_FR_SPEED AS
2 /* $Header: pqchgspd.pkb 120.2 2005/06/09 16:58:22 deenath noship $ */
3 --
4 --Package Variables
5 g_package VARCHAR2(33) := 'PQH_FR_SPEED.';
6 --
7 ------------------------------------------------------------------------------
8 ----------------------------< get_increased_index >---------------------------
9 ------------------------------------------------------------------------------
10 FUNCTION get_increased_index(p_comments IN VARCHAR2
11 ,p_gross_index IN NUMBER
12 ,p_effective_date IN DATE) RETURN NUMBER IS
13 --
14 --Cursor to fetch Increased Index
15 CURSOR csr_increased_index IS
16 SELECT increased_index
17 FROM pqh_fr_global_indices_f
18 WHERE gross_index = p_gross_index
19 AND type_of_record = 'IND' -- for indices
20 AND p_effective_date BETWEEN effective_start_date and effective_end_date;
21 --
22 --Variable Declarations.
23 l_increased_index PQH_FR_GLOBAL_INDICES_F.increased_index%TYPE;
24 l_proc VARCHAR2(72) := g_package||'get_increased_index';
25 --
26 BEGIN
27 --
28 HR_UTILITY.set_location('Entering:'||l_proc,10);
29 --
30 OPEN csr_increased_index;
31 FETCH csr_increased_index INTO l_increased_index;
32 CLOSE csr_increased_index;
33 --
34 HR_UTILITY.set_location('Leaving:'||l_proc,20);
35 --
36 RETURN l_increased_index;
37 --
38 END get_increased_index;
39 --
40 ------------------------------------------------------------------------------
41 --------------------------------< chk_notify >--------------------------------
42 ------------------------------------------------------------------------------
43 FUNCTION chk_notify(p_ben_pgm_id NUMBER
44 ,p_mgr_id NUMBER
45 ,p_review_date DATE
46 ,p_effective_date DATE) RETURN VARCHAR2 IS
47 --
48 --Cursor to fetch Review Length for the Corps from Extra Info
49 CURSOR csr_review_length IS
50 SELECT TO_NUMBER(pgi_information3)
51 FROM ben_pgm_extra_info
52 WHERE information_type = 'PQH_FR_CORP_INFO'
53 AND pgm_id = p_ben_pgm_id;
54 --
55 --Variable Declarations.
56 l_review_length NUMBER;
57 l_notify VARCHAR2(01);
58 l_proc VARCHAR2(72) := g_package||'chk_notify';
59 --
60 BEGIN
61 --
62 HR_UTILITY.set_location('Entering:'||l_proc,10);
63 --
64 l_notify := NULL;
65 --
66 IF p_mgr_id IS NULL THEN
67 l_notify := 'N';
68 ELSIF p_review_date IS NULL THEN
69 l_notify := 'Y';
70 ELSE
71 --
72 l_review_length := NULL;
73 OPEN csr_review_length;
74 FETCH csr_review_length INTO l_review_length;
75 IF csr_review_length%NOTFOUND THEN
76 l_review_length := NULL;
77 END IF;
78 IF csr_review_length%ISOPEN THEN
79 CLOSE csr_review_length;
80 END IF;
81 --
82 IF l_review_length IS NULL THEN
83 l_notify := 'Y';
84 ELSE
85 IF p_review_date < ADD_MONTHS(p_effective_date,-l_review_length) THEN
86 l_notify := 'Y';
87 ELSE
88 l_notify := 'N';
89 END IF;
90 END IF;
91 --
92 END IF;
93 --
94 HR_UTILITY.set_location('Leaving:'||l_proc,20);
95 --
96 RETURN l_notify;
97 --
98 END chk_notify;
99 --
100 ------------------------------------------------------------------------------
101 ------------------------------< get_appraisal >------------------------------
102 ------------------------------------------------------------------------------
103 FUNCTION get_appraisal(p_ben_pgm_id NUMBER
104 ,p_person_id NUMBER
105 ,p_assignment_id NUMBER
106 ,p_appraisal_status VARCHAR2
107 ,p_appraisal_start_date DATE
108 ,p_appraisal_end_date DATE
109 ,p_effective_date DATE) RETURN NUMBER IS
110 --
111 --Cursor to fetch Appraisal Type for the Corps
112 CURSOR csr_appraisal_type IS
113 SELECT pgi_information2
114 FROM ben_pgm_extra_info
115 WHERE information_type = 'PQH_FR_CORP_INFO'
116 AND pgm_id = p_ben_pgm_id;
117 --
118 --Cursor to fetch Appraisal Date
119 CURSOR csr_appraisal(p_appraisal_type VARCHAR2) IS
120 SELECT appraisal_id
121 FROM per_appraisals
122 WHERE appraisee_person_id = p_person_id
123 AND type = p_appraisal_type
124 AND appraisal_system_status = p_appraisal_status
125 AND appraisal_date >= NVL(p_appraisal_start_date,HR_GENERAL.start_of_time)
126 AND appraisal_date <= NVL(p_appraisal_end_date,HR_GENERAL.end_of_time)
127 AND appraisal_date <= p_effective_date
128 ORDER BY appraisal_date DESC;
129 --
130 --Variable Declarations.
131 l_appraisal_type VARCHAR2(30);
132 l_appraisal_id NUMBER;
133 l_proc VARCHAR2(72) := g_package||'get_appraisal';
134 --
135 BEGIN
136 --
137 HR_UTILITY.set_location('Entering:'||l_proc,10);
138 --
139 l_appraisal_type := NULL;
140 OPEN csr_appraisal_type;
141 FETCH csr_appraisal_type INTO l_appraisal_type;
142 IF csr_appraisal_type%NOTFOUND THEN
143 l_appraisal_type := NULL;
144 END IF;
145 IF csr_appraisal_type%ISOPEN THEN
146 CLOSE csr_appraisal_type;
147 END IF;
148 --
149 l_appraisal_id := NULL;
150 --
151 IF l_appraisal_type IS NOT NULL THEN
152 OPEN csr_appraisal(l_appraisal_type);
153 FETCH csr_appraisal INTO l_appraisal_id;
154 IF csr_appraisal%NOTFOUND THEN
155 l_appraisal_id := NULL;
156 END IF;
157 IF csr_appraisal%ISOPEN THEN
158 CLOSE csr_appraisal;
159 END IF;
160 END IF;
161 --
162 HR_UTILITY.set_location('Leaving:'||l_proc,20);
163 --
164 RETURN l_appraisal_id;
165 --
166 END get_appraisal;
167 --
168 ------------------------------------------------------------------------------
169 --------------------------------< get_marks >---------------------------------
170 ------------------------------------------------------------------------------
171 FUNCTION get_marks(p_appraisal_id NUMBER) RETURN NUMBER IS
172 --
173 --Cursor to fetch Appraisal Date
174 CURSOR csr_assessment IS
175 SELECT assessment_id
176 FROM per_assessments
177 WHERE appraisal_id = p_appraisal_id;
178 --
179 --Variable Declarations.
180 l_assessment_id NUMBER;
181 l_marks NUMBER;
182 l_proc VARCHAR2(72) := g_package||'get_marks';
183 --
184 BEGIN
185 --
186 HR_UTILITY.set_location('Entering:'||l_proc,10);
187 --
188 l_marks := 0;
189 OPEN csr_assessment;
190 FETCH csr_assessment INTO l_assessment_id;
191 IF csr_assessment%NOTFOUND THEN
192 l_marks := 0;
193 ELSE
194 l_marks := HR_APPRAISALS_UTIL_SS.get_assessment_score(l_assessment_id);
195 END IF;
196 IF csr_assessment%ISOPEN THEN
197 CLOSE csr_assessment;
198 END IF;
199 --
200 HR_UTILITY.set_location('Leaving:'||l_proc,20);
201 --
202 l_marks := ROUND(l_marks,2); --Rounding to 2 decimal places.
203 --
204 RETURN l_marks;
205 --
206 END get_marks;
207 --
208 ------------------------------------------------------------------------------
209 -----------------------------< chk_speed_quota >------------------------------
210 ------------------------------------------------------------------------------
211 PROCEDURE chk_speed_quota(p_ben_pgm_id IN NUMBER
212 ,p_grade_id IN NUMBER
213 ,p_speed IN VARCHAR2
214 ,p_effective_date IN DATE
215 ,p_num_allowed OUT NOCOPY NUMBER
216 ,p_speed_meaning OUT NOCOPY VARCHAR2
217 ,p_return_status OUT NOCOPY VARCHAR2) IS
218 --
219 --Cursor to fetch total people (fonctionaires only) in specified Corps and Grade
220 CURSOR csr_tot_ppl_in_grd IS
221 SELECT COUNT(asg.person_id)
222 FROM per_all_assignments_f asg
223 ,per_all_people_f ppl
224 WHERE asg.grade_ladder_pgm_id = p_ben_pgm_id
225 AND asg.grade_id = p_grade_id
226 AND asg.primary_flag = 'Y'
227 AND ppl.person_id = asg.person_id
228 AND ppl.per_information15 = '01' --Fonctionaires only
229 AND asg.assignment_status_type_id IN (SELECT assignment_status_type_id
230 FROM per_assignment_status_types
231 WHERE per_system_status IN ('ACTIVE_ASSIGN','SUSP_ASSIGN'))
232 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
233 AND p_effective_date BETWEEN ppl.effective_start_date AND ppl.effective_end_date;
234 --
235 --Cursor to fetch total people (fonctionaires only) in specified Speed within the Corps and Grade
236 CURSOR csr_tot_ppl_in_speed IS
237 SELECT COUNT(placement_id)
238 FROM per_spinal_point_placements_f
239 WHERE assignment_id IN (SELECT asg.assignment_id
240 FROM per_all_assignments_f asg
241 ,per_all_people_f ppl
242 WHERE asg.grade_ladder_pgm_id = p_ben_pgm_id
243 AND asg.grade_id = p_grade_id
244 AND asg.primary_flag = 'Y'
245 AND ppl.person_id = asg.person_id
246 AND ppl.per_information15 = '01' --Fonctionaires only
247 AND asg.assignment_status_type_id IN (SELECT assignment_status_type_id
248 FROM per_assignment_status_types
249 WHERE per_system_status IN ('ACTIVE_ASSIGN','SUSP_ASSIGN'))
250 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
251 AND p_effective_date BETWEEN ppl.effective_start_date AND ppl.effective_end_date)
252 AND information3 = p_speed
253 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
254 --
255 --Cursor to fetch Speed Quota for the specified Corps and Grade
256 CURSOR csr_speed_quota IS
257 SELECT NVL(cei.information6,-1) --MAX Speed Quota
258 ,NVL(cei.information7,-1) --AVG Speed Quota
259 FROM pqh_corps_definitions corps
260 ,pqh_corps_extra_info cei
261 WHERE corps.ben_pgm_id = p_ben_pgm_id
262 AND cei.corps_definition_id = corps.corps_definition_id
263 AND cei.information3 = TO_CHAR(p_grade_id)
264 AND cei.information_type = 'GRADE';
265 --
266 --Variable Declarations.
267 l_tot_ppl_in_grd NUMBER;
268 l_tot_ppl_in_speed NUMBER;
269 l_max_speed_quota NUMBER;
270 l_avg_speed_quota NUMBER;
271 l_speed_quota NUMBER;
272 l_speed_quota_chk VARCHAR2(1);
273 l_proc VARCHAR2(72) := g_package||'chk_speed_quota';
274 --
275 BEGIN
276 --
277 HR_UTILITY.set_location('Entering:'||l_proc,10);
278 --
279 p_num_allowed := 0;
280 p_return_status := 'S';
281 IF p_speed <> 'MIN' THEN
282 --
283 l_tot_ppl_in_grd := 0;
284 OPEN csr_tot_ppl_in_grd;
285 FETCH csr_tot_ppl_in_grd INTO l_tot_ppl_in_grd;
286 IF csr_tot_ppl_in_grd%NOTFOUND THEN
287 l_tot_ppl_in_grd := 0;
288 END IF;
289 IF csr_tot_ppl_in_grd%ISOPEN THEN
290 CLOSE csr_tot_ppl_in_grd;
291 END IF;
292 --
293 l_tot_ppl_in_speed := 0;
294 OPEN csr_tot_ppl_in_speed;
295 FETCH csr_tot_ppl_in_speed INTO l_tot_ppl_in_speed;
296 IF csr_tot_ppl_in_speed%NOTFOUND THEN
297 l_tot_ppl_in_speed := 0;
298 END IF;
299 IF csr_tot_ppl_in_speed%ISOPEN THEN
300 CLOSE csr_tot_ppl_in_speed;
301 END IF;
302 --
303 l_max_speed_quota := -1;
304 l_avg_speed_quota := -1;
305 OPEN csr_speed_quota;
306 FETCH csr_speed_quota INTO l_max_speed_quota,l_avg_speed_quota;
307 IF csr_speed_quota%NOTFOUND THEN
308 l_max_speed_quota := -1;
309 l_avg_speed_quota := -1;
310 END IF;
311 IF csr_speed_quota%ISOPEN THEN
312 CLOSE csr_speed_quota;
313 END IF;
314 --
315 l_speed_quota := -1;
316 IF p_speed = 'MAX' AND l_max_speed_quota <> -1 THEN
317 l_speed_quota := ROUND((l_max_speed_quota * l_tot_ppl_in_grd)/100);
318 ELSIF p_speed = 'AVG' AND l_avg_speed_quota <> -1 THEN
319 l_speed_quota := ROUND((l_avg_speed_quota * l_tot_ppl_in_grd)/100);
320 END IF;
321 --
322 IF l_speed_quota <> -1 THEN
323 IF (l_tot_ppl_in_speed + 1) > l_speed_quota THEN
324 l_speed_quota_chk := 'F'; --Quota check failure
325 ROLLBACK; --Rollback all preceeding transactions.
326 ELSE
327 l_speed_quota_chk := 'S'; --Quota check success
328 END IF;
329 ELSE
330 l_speed_quota_chk := 'S'; --Quota check success because Speed Quota not defined
331 END IF;
332 --
333 p_speed_meaning := HR_GENERAL.decode_lookup('FR_PQH_PROGRESSION_SPEED',p_speed);
334 p_num_allowed := l_speed_quota;
335 p_return_status := l_speed_quota_chk;
336 --
337 END IF;
338 --
339 HR_UTILITY.set_location('Leaving:'||l_proc,20);
340 --
341 END chk_speed_quota;
342 --
343 ------------------------------------------------------------------------------
344 -------------------------------< update_speed >-------------------------------
345 ------------------------------------------------------------------------------
346 PROCEDURE update_speed(p_place_id IN NUMBER
347 ,p_speed IN VARCHAR2
348 ,p_eff_dt IN DATE
349 ,p_ovn IN OUT NOCOPY NUMBER
350 ,p_eff_st_dt OUT NOCOPY DATE
351 ,p_eff_end_dt OUT NOCOPY DATE) IS
352 --
353 --Variable Declarations.
354 l_ovn NUMBER;
355 l_datetrack_mode VARCHAR2(100);
356 l_proc VARCHAR2(72) := g_package||'update_speed';
357 --
358 BEGIN
359 --
360 HR_UTILITY.set_location('Entering:'||l_proc,10);
361 --
362 l_ovn := p_ovn;
363 --
364 HR_UTILITY.set_location('OVN before update: '||l_ovn,20);
365 --
366 l_datetrack_mode := PQH_FR_UTILITY.get_datetrack_mode(p_eff_dt
367 ,'PER_SPINAL_POINT_PLACEMENTS_F'
368 ,'PLACEMENT_ID'
369 ,p_place_id);
370 --
371 HR_UTILITY.set_location('l_datetrack_mode: '||l_datetrack_mode,30);
372 --
373 HR_SP_PLACEMENT_API.update_spp(p_effective_date => p_eff_dt
374 ,p_datetrack_mode => l_datetrack_mode
375 ,p_placement_id => p_place_id
376 ,p_information3 => p_speed
377 ,p_object_version_number => l_ovn
378 ,p_effective_start_date => p_eff_st_dt
379 ,p_effective_end_date => p_eff_end_dt);
380 --
381 HR_UTILITY.set_location('OVN after update: '||l_ovn,40);
382 --
383 HR_UTILITY.set_location('Leaving:'||l_proc,50);
384 --
385 END update_speed;
386 --
387 ------------------------------------------------------------------------------
388 ------------------------------< notify_manager >------------------------------
389 ------------------------------------------------------------------------------
390 PROCEDURE notify_manager(p_ItemType IN VARCHAR2
391 ,p_ProcessName IN VARCHAR2
392 ,p_EmpNumber IN VARCHAR2
393 ,p_EmpName IN VARCHAR2
394 ,p_UserName IN VARCHAR2
395 ,p_MgrUserName IN VARCHAR2
396 ,p_Corps IN VARCHAR2
397 ,p_Grade IN VARCHAR2
398 ,p_Step IN VARCHAR2
399 ,p_Speed IN VARCHAR2
400 ,p_LastApprDt IN DATE
401 ,p_EffDt IN DATE
402 ,p_Duration IN NUMBER) IS
403 --
404 --Variable Declarations.
405 l_itemKey VARCHAR2(240);
406 l_proc VARCHAR2(72) := g_package||'notify_manager';
407 --
408 BEGIN
409 --
410 HR_UTILITY.set_location('Entering:'||l_proc,10);
411 --
412 SELECT PQH_WORKFLOW_ITEM_KEY_S.nextval INTO l_itemKey FROM dual;
413 l_itemKey := 'FRPS'||l_itemKey;
414 --
415 --Kick off the workflow process.
416 WF_ENGINE.CreateProcess(p_itemtype,l_itemkey,p_processName);
417 --
418 IF p_userName is not null then
419 WF_ENGINE.SetItemOwner(p_itemtype,l_itemkey,p_userName);
420 END IF;
421 --
422 --Set the route by user (appears in from on worklist)
423 WF_ENGINE.SetItemAttrText(itemtype => p_ItemType
424 ,itemkey => l_ItemKey
425 ,aname => 'ROUTED_BY_USER'
426 ,avalue => p_UserName);
427 --
428 --Set the manager user to be notified
429 WF_ENGINE.SetItemAttrText(itemtype => p_ItemType
430 ,itemkey => l_ItemKey
431 ,aname => 'FYI_USER'
432 ,avalue => p_MgrUserName);
433 --
434 --Set the Effective Date
435 WF_ENGINE.SetItemAttrDate(itemtype => p_ItemType
436 ,itemkey => l_ItemKey
437 ,aname => 'EFFECTIVE_DATE'
438 ,avalue => p_EffDt);
439 --
440 --Set the person name
441 WF_ENGINE.SetItemAttrText(itemtype => p_ItemType
442 ,itemkey => l_ItemKey
443 ,aname => 'PSV_PERSON_NAME'
444 ,avalue => p_EmpName);
445 --
446 WF_ENGINE.SetItemAttrText(itemtype => p_ItemType
447 ,itemkey => l_ItemKey
448 ,aname => 'PARAMETER1_VALUE'
449 ,avalue => p_EmpNumber);
450 --
451 WF_ENGINE.SetItemAttrText(itemtype => p_ItemType
452 ,itemkey => l_ItemKey
453 ,aname => 'PARAMETER2_VALUE'
454 ,avalue => p_Corps);
455 --
456 WF_ENGINE.SetItemAttrText(itemtype => p_ItemType
457 ,itemkey => l_ItemKey
458 ,aname => 'PARAMETER3_VALUE'
459 ,avalue => p_Grade);
460 --
461 WF_ENGINE.SetItemAttrText(itemtype => p_ItemType
462 ,itemkey => l_ItemKey
463 ,aname => 'PARAMETER4_VALUE'
464 ,avalue => p_Step);
465 --
466 WF_ENGINE.SetItemAttrText(itemtype => p_ItemType
467 ,itemkey => l_ItemKey
468 ,aname => 'PARAMETER5_VALUE'
469 ,avalue => p_Speed);
470 --
471 WF_ENGINE.SetItemAttrText(itemtype => p_ItemType
472 ,itemkey => l_ItemKey
473 ,aname => 'PARAMETER6_VALUE'
474 ,avalue => TO_CHAR(p_Duration));
475 --
476 WF_ENGINE.SetItemAttrText(itemtype => p_ItemType
477 ,itemkey => l_ItemKey
478 ,aname => 'PARAMETER7_VALUE'
479 ,avalue => p_LastApprDt);
480 --
481 WF_ENGINE.StartProcess(p_itemtype,l_itemkey);
482 --
483 COMMIT;
484 --
485 HR_UTILITY.set_location('Leaving:'||l_proc,20);
486 --
487 END notify_manager;
488 --
489 ------------------------------------------------------------------------------
490 -------------------------------< get_speed >----------------------------------
491 ------------------------------------------------------------------------------
492 FUNCTION chk_speed_length(p_assignment_id IN NUMBER
493 ,p_effective_date IN DATE) RETURN VARCHAR2 IS
494 --
495 --Cursor to fetch current assginment, step and speed details
496 CURSOR csr_step_dtls IS
497 SELECT asg.grade_id
498 ,spp.step_id
499 ,spp.information3
500 ,MONTHS_BETWEEN(p_effective_date,spp.effective_start_date)
501 ,p_effective_date-spp.effective_start_date
502 ,spp.effective_start_date
503 FROM per_all_assignments_f asg
504 ,per_spinal_point_placements_f spp
505 WHERE asg.assignment_id = p_assignment_id
506 AND spp.assignment_id(+) = asg.assignment_id
507 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
508 AND p_effective_date BETWEEN spp.effective_start_date(+) AND spp.effective_end_date(+);
509 --
510 --Cursor to fetch speed length for the Step
511 CURSOR csr_speed_length(p_grade_id NUMBER
512 ,p_step_id NUMBER
513 ,p_speed_cd VARCHAR) IS
514 SELECT DECODE(p_speed_cd
515 ,'MIN',psp.information4
516 ,'AVG',psp.information5
517 ,'MAX',psp.information3
518 ,-1) speed_len
519 ,NVL(pps.information2,'CM') speed_unit
520 FROM per_grade_spines_f gsp
521 ,per_spinal_point_steps_f sps
522 ,per_spinal_points psp
523 ,per_parent_spines pps
524 WHERE gsp.grade_id = p_grade_id
525 AND sps.step_id = p_step_id
526 AND sps.grade_spine_id = gsp.grade_spine_id
527 AND sps.business_group_id = gsp.business_group_id
528 AND psp.parent_spine_id = gsp.parent_spine_id
529 AND psp.spinal_point_id = sps.spinal_point_id
530 AND psp.business_group_id = gsp.business_group_id
531 AND psp.information_category = 'FR_PQH'
532 AND pps.information_category = psp.information_category
533 AND pps.business_group_id = psp.business_group_id
534 AND pps.parent_spine_id = psp.parent_spine_id
535 AND p_effective_date BETWEEN gsp.effective_start_date AND gsp.effective_end_date
536 AND p_effective_date BETWEEN sps.effective_start_date AND sps.effective_end_date;
537 --
538 --Variable Declarations.
539 l_grade_id NUMBER;
540 l_step_id NUMBER;
541 l_speed_cd VARCHAR2(240);
542 l_step_los_mon NUMBER;
543 l_step_los_day NUMBER;
544 l_speed_start_dt DATE;
545 l_date DATE;
546 l_speed_len NUMBER;
547 l_speed_unit VARCHAR2(10);
548 l_return_cd VARCHAR2(1);
549 l_proc VARCHAR2(72) := g_package||'chk_speed_length';
550 --
551 BEGIN
552 --
553 HR_UTILITY.set_location('Entering:'||l_proc,10);
554 --
555 OPEN csr_step_dtls;
556 FETCH csr_step_dtls INTO l_grade_id,l_step_id,l_speed_cd,l_step_los_mon,l_step_los_day,l_speed_start_dt;
557 IF csr_step_dtls%NOTFOUND THEN
558 CLOSE csr_step_dtls;
559 l_return_cd := 'N'; --Assignment not found. Return Failure.
560 --
561 HR_UTILITY.set_location('Leaving:'||l_proc,20);
562 --
563 RETURN l_return_cd;
564 END IF;
565 IF csr_step_dtls%ISOPEN THEN
566 CLOSE csr_step_dtls;
567 END IF;
568 --
569 IF l_step_id IS NULL OR l_speed_cd IS NULL THEN
570 l_return_cd := 'N'; --Person not on Step or Speed not set for Person. Return Failure.
571 --
572 HR_UTILITY.set_location('Leaving:'||l_proc,20);
573 --
574 RETURN l_return_cd;
575 ELSE
576 --
577 OPEN csr_speed_length(l_grade_id,l_step_id,l_speed_cd);
578 FETCH csr_speed_length INTO l_speed_len,l_speed_unit;
579 IF csr_speed_length%NOTFOUND THEN
580 CLOSE csr_speed_length;
581 l_return_cd := 'Y'; --Speed Lengths not defined in Corps Setup. Return Success.
582 --
583 HR_UTILITY.set_location('Leaving:'||l_proc,20);
584 --
585 RETURN l_return_cd;
586 END IF;
587 IF csr_speed_length%ISOPEN THEN
588 CLOSE csr_speed_length;
589 END IF;
590 --
591 IF l_speed_len IS NULL THEN
592 l_return_cd := 'Y'; --Speed Lengths defined as NULL in Corps Setup. Return Success.
593 ELSIF l_speed_len = -1 THEN
594 l_return_cd := 'N'; --Invalid Speed Code for emp in SPP. Return Failure.
595 ELSE
596 --
597 IF l_speed_unit = 'Y' THEN --Year
598 l_speed_len := l_speed_len*12;
599 ELSIF l_speed_unit = 'SY' THEN --Semi Year
600 l_speed_len := l_speed_len*6;
601 ELSIF l_speed_unit = 'Q' THEN --Quarter
602 l_speed_len := l_speed_len*3;
603 ELSIF l_speed_unit = 'BM' THEN --Bi Month
604 l_speed_len := l_speed_len*2;
605 ELSIF l_speed_unit = 'CM' THEN --Calendar Month
606 l_speed_len := l_speed_len;
607 ELSIF l_speed_unit = 'LM' THEN --Lunar Month
608 l_speed_len := l_speed_len*28;
609 ELSIF l_speed_unit = 'SM' THEN --Semi Month
610 l_date := l_speed_start_dt;
611 FOR i IN 1..l_speed_len
612 LOOP
613 l_date := TRUNC(l_date+TRUNC(((ADD_MONTHS(l_date,1)-l_date)/2)));
614 END LOOP;
615 l_speed_len := l_date-l_speed_start_dt;
616 ELSIF l_speed_unit = 'F' THEN --Fortnight/BiWeek
617 l_speed_len := l_speed_len*14;
618 ELSIF l_speed_unit = 'W' THEN --Week
619 l_speed_len := l_speed_len*7;
620 END IF;
621 --
622 IF l_speed_unit IN ('Y','SY','Q','BM','CM') THEN --Month Comparision
623 --
624 IF l_step_los_mon >= l_speed_len THEN
625 l_return_cd := 'Y'; --Person has satisfied speed length. Return Success.
626 ELSE
627 l_return_cd := 'N'; --Pesron has not satisfied speed length. Return Failure.
628 END IF;
629 --
630 ELSE --Days Comparision
631 --
632 IF l_step_los_day >= l_speed_len THEN
633 l_return_cd := 'Y'; --Person has satisfied speed length. Return Success.
634 ELSE
635 l_return_cd := 'N'; --Pesron has not satisfied speed length. Return Failure.
636 END IF;
637 --
638 END IF;
639 --
640 END IF;
641 --
642 END IF;
643 --
644 HR_UTILITY.set_location('Leaving:'||l_proc,20);
645 --
646 RETURN l_return_cd;
647 --
648 END chk_speed_length;
649 --
650 ------------------------------------------------------------------------------
651 -------------------------------< get_mgr_user >-------------------------------
652 ------------------------------------------------------------------------------
653 PROCEDURE get_mgr_user(p_effective_date IN DATE
654 ,p_mgr_id IN NUMBER
655 ,p_mgr_username OUT NOCOPY VARCHAR2) IS
656 --
657 --Cursor to fetch FNDUSER for Manager
658 CURSOR csr_fnduser IS
659 SELECT user_name
660 FROM fnd_user
661 WHERE employee_id = p_mgr_id
662 AND p_effective_date BETWEEN NVL(start_date,HR_GENERAL.start_of_time) AND NVL(end_date,HR_GENERAL.end_of_time);
663 --
664 --Variable Declarations.
665 l_proc VARCHAR2(72) := g_package||'get_mgr_user';
666 BEGIN
667 --
668 HR_UTILITY.set_location('Entering:'||l_proc,10);
669 --
670 OPEN csr_fnduser;
671 FETCH csr_fnduser INTO p_mgr_username;
672 IF csr_fnduser%NOTFOUND THEN
673 p_mgr_username := 'N';
674 END IF;
675 CLOSE csr_fnduser;
676 --
677 HR_UTILITY.set_location('Leaving:'||l_proc,20);
678 --
679 END get_mgr_user;
680 --
681 --
682 END pqh_fr_speed;