1 PACKAGE BODY ghr_ss_views_pkg AS
2 /* $Header: ghssview.pkb 120.4.12010000.4 2008/11/03 08:48:51 managarw ship $ */
3 --
4 -- This is global variable to store flex number for performance SIT .
5 -- This variable is used as cache.
6 g_perf_flex_num fnd_id_flex_structures_tl.id_flex_num%type;
7
8 --
9 -- This function returns best fit history id for person Extra Info type record on a given a day.
10
11 function get_people_ei_id_ason_date(
12 p_person_id in number,
13 p_information_type in varchar2,
14 p_effective_date in date
15 ) return number is
16 l_proc varchar2(72) ;
17 l_history_id ghr_pa_history.pa_history_id%type;
18
19
20 -- This cursor gets the latest date on which the given Extra information Type record
21 -- is inserted or updated on or before the given date
22 -- and then gets the the record with highest history id on that date.
23
24 -- In c_history_id join on PER_PEOPLE_EXTRA_INFO is required to skip the history records
25 -- on the extra information records that are deleted.
26 cursor c_history_id is
27 select max(gph1.pa_history_id) pa_history_id
28 from ghr_pa_history gph1,
29 PER_PEOPLE_EXTRA_INFO pei1
30 where gph1.table_name = 'PER_PEOPLE_EXTRA_INFO'
31 and pei1.person_id = p_person_id
32 and pei1.information_type = p_information_type
33 and gph1.information1 = to_char(pei1.person_extra_info_id)
34 and gph1.effective_date = ( select max(gph2.effective_date)maxdate
35 from ghr_pa_history gph2
36 where gph2.table_name = 'PER_PEOPLE_EXTRA_INFO'
37 and gph2.effective_date <= p_effective_date
38 and gph2.information1 = to_char(pei1.person_extra_info_id)) ;
39
40
41
42 begin
43
44 l_proc := g_package||'.get_people_ei_id_ason_date';
45
46 if ( hr_utility.debug_enabled()) then
47 hr_utility.set_location('Entering... ' ||l_proc,1000);
48 hr_utility.set_location('Person_id : ' || p_person_id, 1000);
49 hr_utility.set_location('Information_type : ' || p_information_type, 1000);
50 hr_utility.set_location('Effective_date : ' || p_effective_date, 1000);
51 End if;
52
53 for history_id_rec in c_history_id
54 loop
55 l_history_id := history_id_rec.pa_history_id;
56 exit;
57 end loop;
58
59 hr_utility.set_location('history_id : ' || l_history_id, 1000);
60
61 return (l_history_id);
62
63 end get_people_ei_id_ason_date;
64
65
66 -- This function is a wrapper functions which returns the input value of an element on the given date.
67 -- This function calls ghr_per_sum.get_element_details procedure.
68
69 function get_ele_value_ason_date (p_ele_name in varchar2
70 ,p_input_name in varchar2
71 ,p_asg_id in number
72 ,p_eff_date in date,
73 P_BUSINESS_GROUP_ID in Number
74 ) return varchar2 is
75
76 l_screen_entry_value varchar2 (150);
77 l_effective_start_date date;
78 l_proc varchar2(50);
79 Begin
80 l_proc := g_package||'.get_ele_value_ason_date';
81 hr_utility.set_location('Entering... ' ||l_proc,1000);
82
83 ghr_per_sum.get_element_details(p_ele_name,
84 p_input_name,
85 p_asg_id,
86 p_eff_date,
87 l_screen_entry_value,
88 l_effective_start_date,
89 P_BUSINESS_GROUP_ID
90 );
91
92 hr_utility.set_location('Element input value : ' || l_screen_entry_value, 1000);
93 return (l_screen_entry_value);
94
95 End get_ele_value_ason_date;
96
97 -- This function is a wrapper functions which returns the input value of an element on the given date.
98 -- This function calls ghr_per_sum.get_element_entry_values procedure.
99
100
101 function get_ele_entry_value_ason_date (p_element_entry_id IN NUMBER
102 ,p_input_value_name IN VARCHAR2
103 ,p_effective_date IN DATE
104 ) return varchar2 is
105
106 l_value varchar2 (150);
107 l_effective_start_date date;
108 l_proc varchar2(100);
109 Begin
110
111 l_proc := g_package||'.get_ele_entry_value_ason_date';
112 hr_utility.set_location('Entering... ' ||l_proc,1000);
113 ghr_per_sum.get_element_entry_values (p_element_entry_id
114 ,p_input_value_name
115 ,p_effective_date
116 ,l_value
117 ,l_effective_start_date );
118
119 hr_utility.set_location('Element input value : ' || l_value, 1000);
120 return (l_value);
121
122 End get_ele_entry_value_ason_date;
123
124 -- This function returns the latest pa request id on or before a given date.
125 -- Latest Personnel Action is Latest Effective Date, if multiple on same Effective Date,
126 -- use greatest NPA ID; display the Second NOAC if available, otherwise First NOAC;
127 -- if First NOAC is either 001 or 002, then go with next Personnel Action
128
129 function get_latest_pa_req_id (
130 p_person_id in number,
131 p_effective_date in date
132 ) return number is
133
134
135 cursor c_notification_id is
136 select max(par1.pa_notification_id) notification_id
137 from ghr_pa_requests par1
138 where par1.person_id = p_person_id
139 and par1.pa_notification_id is NOT Null
140 and par1.noa_family_code NOT in ('CORRECT', 'CANCEL')
141 and nvl(par1.first_noa_cancel_or_correct, 'normal') <> 'CANCEL'
142 and par1.effective_date = ( select max(par2.effective_date) maxdate
143 from ghr_pa_requests par2
144 where par2.person_id = p_person_id
145 and par2.pa_notification_id is NOT Null
146 and par2.effective_date <= p_effective_date
147 and par2.noa_family_code NOT in ('CORRECT', 'CANCEL')
148 and nvl(par2.first_noa_cancel_or_correct, 'normal') <> 'CANCEL' ) ;
149
150
151 cursor c_request_id (c_notification_id number) is
152 select pa_request_id
153 from ghr_pa_requests g
154 where g.pa_notification_id = c_notification_id ;
155
156 l_notification_id ghr_pa_requests.pa_notification_id%type;
157 l_pa_request_id ghr_pa_requests.pa_request_id%type;
158 l_proc varchar2(72);
159
160
161 BEGIN
162
163 l_proc := g_package||'.get_latest_pa_req_id';
164
165 if ( hr_utility.debug_enabled()) then
166 hr_utility.set_location('Entering... ' ||l_proc,1000);
167 hr_utility.set_location('Person_id : ' || p_person_id, 1000);
168 hr_utility.set_location('Effective_date : ' || p_effective_date, 1000);
169 end if;
170
171 for notification_id_rec in c_notification_id
172 loop
173 l_notification_id := notification_id_rec.notification_id;
174 exit;
175 end loop;
176
177 hr_utility.set_location('notification_id : ' || l_notification_id, 1000);
178
179 for pa_request_id_rec in c_request_id (l_notification_id)
180 loop
181 l_pa_request_id := pa_request_id_rec.pa_request_id;
182 exit;
183 end loop;
184
185 hr_utility.set_location('pa_request_id : ' || l_pa_request_id, 1000);
186
187 return (l_pa_request_id);
188
189 END get_latest_pa_req_id;
190
191 -- This function returns the latest performance rating for the person on or before a given date.
192
193 Function get_latest_perf_rating(p_person_id in number,
194 p_effective_date in Date) return varchar2 is
195
196 -- cursor get_flex_num gives the flex number id for performance appraisal SIT.
197 -- This cursor will be used only for the first time this function is called in a session.
198 -- From the second time it uses the flex number stored in cache.
199
200 cursor get_flex_num is
201 select flx.id_flex_num id_flex_num
202 from fnd_id_flex_structures_tl flx
203 where flx.id_flex_code = 'PEA' and
204 flx.application_id = 800 and
205 flx.id_flex_structure_name = 'US Fed Perf Appraisal' and
206 flx.language = 'US' ;
207
208 -- cursor get_latest_perf_rating gets the latest performance rating.
209 -- Latest Performance Rating: use Appraisal Start Date and Rating of Record;
210 -- if duplicate Start Dates, go with the greatest ID
211
212 cursor get_latest_perf_rating is
213 select pan.person_analysis_id, pea.segment2 rating_of_record
214 from per_analysis_criteria pea,
215 per_person_analyses pan
216 where pan.person_id = p_person_id and
217 pan.id_flex_num = g_perf_flex_num and
218 pea.id_flex_num = pan.id_flex_num and
219 nvl(pan.date_from,sysdate) between nvl(pea.start_date_active,nvl(pan.date_from,sysdate) )
220 and nvl(pea.end_date_active,nvl(pan.date_from,sysdate) ) and
221 pan.analysis_criteria_id = pea.analysis_criteria_id and
222 trunc(nvl(pan.date_from,sysdate)) = (select max(trunc(nvl(pan.date_from,sysdate))) max_date_from
223 from per_person_analyses pan
224 where pan.person_id = p_person_id and
225 pan.id_flex_num = g_perf_flex_num and
226 trunc(nvl(pan.date_from,sysdate)) <= p_effective_date )
227
228 order by person_analysis_id desc ;
229
230 l_max_date_from date;
231 l_perf_rating varchar2(10);
232 l_proc varchar2(72);
233 Begin
234
235 l_proc := g_package||'.get_latest_perf_rating';
236
237 if ( hr_utility.debug_enabled()) then
238 hr_utility.set_location('Entering... ' ||l_proc,1000);
239 hr_utility.set_location('Person_id : ' || p_person_id, 1000);
240 hr_utility.set_location('Effective_date : ' || p_effective_date, 1000);
241 hr_utility.set_location('perf Flex Num : ' || g_perf_flex_num, 1000);
242 end if;
243
244 If g_perf_flex_num is null THEN
245 for get_flex_num_rec in get_flex_num
246 loop
247 g_perf_flex_num := get_flex_num_rec.id_flex_num;
248 exit;
249 End loop;
250 END IF;
251
252 hr_utility.set_location('perf Flex Num : ' || g_perf_flex_num, 10);
253
254 for get_latest_perf_rating_rec in get_latest_perf_rating
255 loop
256 l_perf_rating := get_latest_perf_rating_rec.rating_of_record;
257 exit;
258 End loop;
259
260 hr_utility.set_location('Performance rating : ' || l_perf_rating, 1000);
261
262 return( l_perf_rating);
263
264 End get_latest_perf_rating;
265 --
266 -- This function returns the currency code of an element on an effective date.
267
268 Function retrieve_element_curr_code (p_element_name in pay_element_types_f.element_name%type,
269 p_assignment_id in pay_element_entries_f.assignment_id%type,
270 p_business_group_id in per_all_assignments_f.business_group_id%type,
271 p_effective_date in date ) return varchar2 is
272
273 --
274 l_proc varchar2(72);
275 l_new_element_name VARCHAR2(80);
276 l_input_curr_code varchar2(10);
277
278 --
279
280 Cursor c_ele_input_curr_code is
281
282 select elt.input_currency_code input_curr_code
283 from pay_element_types_f elt,
284 pay_element_entries_f ele
285 where
286 trunc(p_effective_date) between elt.effective_start_date
287 and elt.effective_end_date
288 and trunc(p_effective_date) between ele.effective_start_date
289 and ele.effective_end_date
290 and ele.assignment_id = p_assignment_id
291 and elt.element_type_id = ele.element_type_id
292 and upper(elt.element_name) = upper(l_new_element_name)
293 and (elt.business_group_id is null or elt.business_group_id = p_business_group_id) ;
294
295
296
297 Begin
298
299 -- Initialization
300 l_proc := g_package||'retrieve_element_curr_code';
301
302 ---- pqp_fedhr_uspay_int_utils.return_new_element_name is called to get the new element name
303
304 ---- For all elements in HR User old function will fetch the same name.
305 ---- because of is_script will be FALSE
306 ----
307 ---- For all elements (except BSR) in Payroll user old function.
308 ---- for BSR a new function which will fetch from assignmnet id.
309 ----
310 l_new_element_name := p_element_name;
311
312 IF (p_element_name = 'Basic Salary Rate'
313 and (fnd_profile.value('HR_USER_TYPE') = 'INT')) THEN
314 hr_utility.set_location('PAYROLL User -- BSR -- from asgid-- '||l_proc, 1);
315 l_new_element_name :=
316 pqp_fedhr_uspay_int_utils.return_new_element_name(
317 p_assignment_id => p_assignment_id,
318 p_business_group_id => p_business_group_id,
319 p_effective_date => p_effective_date);
320
321 ELSE
322 hr_utility.set_location('HR USER or PAYROLL User without BSR element -- from elt name -- '||l_proc, 1);
323 l_new_element_name :=
324 pqp_fedhr_uspay_int_utils.return_new_element_name(
325 p_fedhr_element_name => p_element_name,
326 p_business_group_id => p_business_group_id,
327 p_effective_date => p_effective_date,
328 p_pay_basis => NULL);
329
330 END IF;
331
332 if ( hr_utility.debug_enabled()) then
333 hr_utility.set_location('Element Name ' ||p_element_name,1000);
334 hr_utility.set_location('BG ID '|| p_business_group_id,2000);
335 hr_utility.set_location('Eff date'|| p_effective_date ,3000);
336 hr_utility.set_location('New element Name ' ||l_new_element_name,100000);
337 end if;
338
339 for c_ele_input_curr_code_rec in c_ele_input_curr_code
340 Loop
341 l_input_curr_code := c_ele_input_curr_code_rec.input_curr_code;
342 exit;
343 End Loop;
344
345 hr_utility.set_location('Input currency code ' ||l_input_curr_code,100000);
346
347 return (l_input_curr_code);
348
349 End retrieve_element_curr_code;
350
351 -- This is a wrapper function to get the locality pay area percentage.
352 -- This function calls ghr_per_sum.get_duty_station_details procedure.
353
354 function get_loc_pay_area_percentage (p_location_id in number,
355 p_effective_date in date ) return varchar2 is
356 --
357
358 l_locality_pay_area_percentage number;
359 l_locality_pay_area varchar2(80);
360 l_duty_station_desc varchar2(80);
361 l_duty_sation_code varchar2(80);
362 l_proc varchar2(72);
363
364 Begin
365
366 l_proc := g_package||'.get_loc_pay_area_percentage';
367
368 if ( hr_utility.debug_enabled()) then
369 hr_utility.set_location('Entering... ' ||l_proc,1000);
370 hr_utility.set_location('Location id : ' || p_location_id, 1000);
371 hr_utility.set_location('Effective_date : ' || p_effective_date, 1000);
372 end if;
373
374 ghr_per_sum.get_duty_station_details (p_location_id
375 ,p_effective_date
376 ,l_duty_sation_code
377 ,l_duty_station_desc
378 ,l_locality_pay_area
379 ,l_locality_pay_area_percentage ) ;
380
381 hr_utility.set_location('locality pay area percentage : ' || l_locality_pay_area_percentage, 1000);
382 return (l_locality_pay_area_percentage);
383
384 End get_loc_pay_area_percentage;
385
386
387 --This function checks out whether a person has got an Award
388 --for a given assignment
389
390 function check_if_awards_exists ( p_assignment_id in number,
391 p_effective_date in date ) return varchar2 is
392 --
393
394 l_award_bonus VARCHAR2(5);
395 l_proc VARCHAR2(100);
396
397
398 CURSOR award_check (l_assignment_id NUMBER,
399 l_effective_date DATE ) IS
400 SELECT eef.element_entry_id
401 FROM pay_element_entries_f eef,
402 pay_element_types_f elt
403 WHERE eef.assignment_id = l_assignment_id
404 AND eef.effective_start_date <= l_effective_date
405 AND elt.element_type_id = eef.element_type_id
406 AND eef.effective_start_date BETWEEN elt.effective_start_date
407 AND elt.effective_end_date
408 AND UPPER(pqp_fedhr_uspay_int_utils.return_old_element_name
409 (elt.element_name,
410 elt.business_group_id,
411 eef.effective_start_date)) = 'FEDERAL AWARDS' ;
412
413
414
415 BEGIN
416
417 l_proc := g_package||'.check_if_awards_exists';
418 l_award_bonus := 'No' ;
419
420 if ( hr_utility.debug_enabled()) then
421 hr_utility.set_location('Entering... ' ||l_proc,1000);
422 hr_utility.set_location('assignement_id : ' || p_assignment_id, 1001);
423 hr_utility.set_location('Effective_date : ' || p_effective_date, 1002);
424 end if;
425
426 FOR award_check_rec IN award_check (p_assignment_id , p_effective_date)LOOP
427 l_award_bonus := 'Yes';
428 EXIT;
429 END LOOP;
430
431 hr_utility.set_location('Check for Award : ' || l_award_bonus, 1004);
432
433 RETURN(l_award_bonus);
434
435 END check_if_awards_exists;
436
437 --This function checks out whether a person has got an Award
438 --for a given assignment
439
440 FUNCTION check_if_bonus_exists (p_assignment_id IN NUMBER,
441 p_effective_date IN DATE ) RETURN VARCHAR2 IS
442 --
443
444 l_award_bonus VARCHAR2(5);
445 l_proc VARCHAR2(100);
446
447
448
449 CURSOR bonus_check (l_assignment_id NUMBER,
450 l_effective_date DATE ) IS
451 SELECT eef.element_entry_id
452 FROM pay_element_entries_f eef,
453 pay_element_types_f elt
454 WHERE eef.assignment_id = l_assignment_id
455 AND eef.effective_start_date <= l_effective_date
456 AND elt.element_type_id = eef.element_type_id
457 AND eef.effective_start_date BETWEEN elt.effective_start_date
458 AND elt.effective_end_date
459 AND UPPER(pqp_fedhr_uspay_int_utils.return_old_element_name
460 (elt.element_name,
461 elt.business_group_id,
462 eef.effective_start_date)) IN ('RELOCATION BONUS',
463 'RECRUITMENT BONUS' );
464
465 BEGIN
466
467 l_proc := g_package||'.check_if_bonus_exists';
468 l_award_bonus := 'No' ;
469 if ( hr_utility.debug_enabled()) then
470 hr_utility.set_location('Entering... ' ||l_proc,1000);
471 hr_utility.set_location('assignement_id : ' || p_assignment_id, 1001);
472 hr_utility.set_location('Effective_date : ' || p_effective_date, 1002);
473 end if;
474
475 FOR bonus_check_rec IN bonus_check (p_assignment_id , p_effective_date)LOOP
476 l_award_bonus := 'Yes';
477 EXIT;
478 END LOOP;
479
480
481 hr_utility.set_location('Check for Bonus : ' || l_award_bonus, 1004);
482
483 RETURN(l_award_bonus);
484
485 END check_if_bonus_exists;
486
487 -- This function returns the history id for a particular information type of a person
488 -- depending on the effective date and the person type.
489
490 function get_history_id(p_assignment_type in varchar2,
491 p_person_id in number,
492 p_information_type in varchar2,
493 p_effective_date in date
494 ) return number is
495 l_proc varchar2(72) ;
496 l_history_id ghr_pa_history.pa_history_id%type;
497
498 begin
499 l_proc := g_package||'get_history_id';
500 if (p_assignment_type = 'E' or p_assignment_type = 'C') then
501 select nvl(substr((select ghr_ss_views_pkg.get_people_ei_id_ason_date(p_person_id,p_information_type,p_effective_date) from dual), 0, 10),-1)
502 into l_history_id
503 from dual;
504 end if;
505 return (l_history_id);
506 end get_history_id;
507
508 Function get_assignment_ei_id_ason_date( p_asg_id in number,
509 p_information_type in varchar2,
510 p_effective_date in date) return number is
511
512 l_proc varchar2(72) ;
513 l_history_id ghr_pa_history.pa_history_id%type;
514
515
516 -- This cursor gets the latest date on which the given Extra information Type record
517 -- is inserted or updated on or before the given date
518 -- and then gets the the record with highest history id on that date.
519
520 -- In c_history_id join on PER_ASSIGNMENT_EXTRA_INFO is required to skip the history records
521 -- on the extra information records that are deleted.
522 cursor c_history_id is
523 select nvl(max(gph1.pa_history_id),hr_api.g_number) pa_history_id
524 from ghr_pa_history gph1,
525 PER_ASSIGNMENT_EXTRA_INFO pei1
526 where gph1.table_name = 'PER_ASSIGNMENT_EXTRA_INFO'
527 and pei1.assignment_id = p_asg_id
528 and pei1.information_type = p_information_type
529 and gph1.information1 = to_char(pei1.assignment_extra_info_id)
530 and gph1.effective_date = ( select max(gph2.effective_date)maxdate
531 from ghr_pa_history gph2
532 where gph2.table_name = 'PER_ASSIGNMENT_EXTRA_INFO'
533 and gph2.effective_date <= p_effective_date
534 and gph2.information1 = to_char(pei1.assignment_extra_info_id)) ;
535
536
537
538 Begin
539
540 l_proc := g_package||'.get_asg_ei_id_ason_date';
541
542 if ( hr_utility.debug_enabled()) then
543 hr_utility.set_location('Entering... ' ||l_proc,1000);
544 hr_utility.set_location('Assignment_id : ' || p_asg_id, 1000);
545 hr_utility.set_location('Information_type : ' || p_information_type, 1000);
546 hr_utility.set_location('Effective_date : ' || p_effective_date, 1000);
547 End if;
548
549 for history_id_rec in c_history_id loop
550 l_history_id := history_id_rec.pa_history_id;
551 exit;
552 end loop;
553
554 hr_utility.set_location('history_id : ' || l_history_id, 1000);
555 return (l_history_id);
556
557 End get_assignment_ei_id_ason_date;
558
559 --
560
561 Function get_position_ei_id_ason_date( p_position_id in number,
562 p_information_type in varchar2,
563 p_effective_date in date
564 ) return number is
565
566 l_proc varchar2(72) ;
567 l_history_id ghr_pa_history.pa_history_id%type;
568
569
570 -- This cursor gets the latest date on which the given Extra information Type record
571 -- is inserted or updated on or before the given date
572 -- and then gets the the record with highest history id on that date.
573
574 -- In c_history_id join on PER_POSITION_EXTRA_INFO is required to skip the history records
575 -- on the extra information records that are deleted.
576 cursor c_history_id is
577 select nvl(max(gph1.pa_history_id),hr_api.g_number) pa_history_id
578 from ghr_pa_history gph1,
579 PER_POSITION_EXTRA_INFO pei1
580 where gph1.table_name = 'PER_POSITION_EXTRA_INFO'
581 and pei1.position_id = p_position_id
582 and pei1.information_type = p_information_type
583 and gph1.information1 = to_char(pei1.position_extra_info_id)
584 and gph1.effective_date = ( select max(gph2.effective_date)maxdate
585 from ghr_pa_history gph2
586 where gph2.table_name = 'PER_POSITION_EXTRA_INFO'
587 and gph2.effective_date <= p_effective_date
588 and gph2.information1 = to_char(pei1.position_extra_info_id)) ;
589
590 Begin
591
592 l_proc := g_package||'.get_position_ei_id_ason_date';
593
594 if ( hr_utility.debug_enabled()) then
595 hr_utility.set_location('Entering... ' ||l_proc,1000);
596 hr_utility.set_location('position_id : ' || p_position_id, 1000);
597 hr_utility.set_location('Information_type : ' || p_information_type, 1000);
598 hr_utility.set_location('Effective_date : ' || p_effective_date, 1000);
599 End if;
600
601 for history_id_rec in c_history_id loop
602 l_history_id := history_id_rec.pa_history_id;
603 exit;
604 end loop;
605
606 hr_utility.set_location('history_id : ' || l_history_id, 1000);
607
608 return (l_history_id);
609
610 end get_position_ei_id_ason_date;
611
612 --Start of fix for Bug#6085591
613 -- This function returns the Rating of Record for the person .
614
615 Function get_rating_of_record(p_person_id in number) return varchar2 is
616
617 -- cursor get_flex_num gives the flex number id for performance appraisal SIT.
618 -- This cursor will be used only for the first time this function is called in a session.
619 -- From the second time it uses the flex number stored in cache.
620
621 cursor get_flex_num is
622 select flx.id_flex_num id_flex_num
623 from fnd_id_flex_structures_tl flx
624 where flx.id_flex_code = 'PEA' and flx.application_id = 800 and
625 flx.id_flex_structure_name = 'US Fed Perf Appraisal' and
626 flx.language = 'US';
627
628 -- cursor get_rating_of_record gets the rating of record.
629
630 cursor get_rating_of_record is
631 select pan.person_analysis_id, pea.segment5 rating_of_record
632 from per_analysis_criteria pea, per_person_analyses pan
633 where pan.person_id = p_person_id and
634 pan.id_flex_num = g_perf_flex_num and
635 pea.id_flex_num = pan.id_flex_num and
636 nvl(pan.date_from, sysdate) between
637 nvl(pea.start_date_active, nvl(pan.date_from, sysdate)) and
638 nvl(pea.end_date_active, nvl(pan.date_from, sysdate)) and
639 pan.analysis_criteria_id = pea.analysis_criteria_id
640
641 order by person_analysis_id desc;
642
643 l_max_date_from date;
644 l_perf_rating varchar2(10);
645 l_proc varchar2(72);
646 Begin
647
648 l_proc := g_package || '.get_rating_of_record';
649
650 if (hr_utility.debug_enabled()) then
651 hr_utility.set_location('Entering... ' || l_proc, 1001);
652 hr_utility.set_location('Person_id : ' || p_person_id, 1001);
653 hr_utility.set_location('perf Flex Num : ' || g_perf_flex_num, 1001);
654 end if;
655
656 If g_perf_flex_num is null THEN
657 for get_flex_num_rec in get_flex_num loop
658 g_perf_flex_num := get_flex_num_rec.id_flex_num;
659 exit;
660 End loop;
661 END IF;
662
663 hr_utility.set_location('perf Flex Num : ' || g_perf_flex_num, 101);
664
665 for get_rating_of_record_rec in get_rating_of_record loop
666 l_perf_rating := get_rating_of_record_rec.rating_of_record;
667 exit;
668 End loop;
669
670 hr_utility.set_location('Performance rating : ' || l_perf_rating, 1001);
671
672 return(l_perf_rating);
673
674 End get_rating_of_record;
675
676 --End of fix for Bug#6085591
677
678 --Beginning of bug fix 6781928
679 FUNCTION get_assignment_start_date(p_person_id IN NUMBER) RETURN DATE IS
680
681 CURSOR assignement_start_date(p_person_id NUMBER) IS
682 SELECT MIN(effective_start_date) effective_start_date
683 FROM per_all_assignments_f
684 WHERE person_id = p_person_id AND
685 assignment_id =
686 (SELECT MAX(assignment_id)
687 FROM per_all_assignments_f
688 WHERE person_id = p_person_id AND assignment_type in ('E','C')) AND
689 assignment_type in ('E','C');
690 l_ass_st_date DATE;
691 BEGIN
692
693 FOR l_assignement_start_date IN assignement_start_date(p_person_id) LOOP
694 l_ass_st_date := l_assignement_start_date.effective_start_date;
695 exit;
696 END LOOP;
697
698 RETURN l_ass_st_date;
699
700 END get_assignment_start_date;
701
702 Function get_assignment_end_date(p_person_id in number) return date is
703
704 cursor assignement_end_date (p_person_id number) is
705 select max(effective_end_date) effective_end_date
706 from per_all_assignments_f
707 where person_id = p_person_id and
708 assignment_type in ('E','C') ;
709 l_ass_end_date date;
710 Begin
711
712 for l_assignement_end_date in assignement_end_date(p_person_id) loop
713 l_ass_end_date := l_assignement_end_date.effective_end_date;
714 exit;
715 end loop;
716
717 return l_ass_end_date ;
718
719 End get_assignment_end_date;
720 --End of bug fix 6781928
721
722 END ghr_ss_views_pkg;
[Home] [Help]