1 PACKAGE BODY ghr_ss_views_pkg AS
2 /* $Header: ghssview.pkb 120.15.12020000.2 2012/09/05 10:58:08 vmididho 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 --Bug# 12652755 commented below cursor and rewrite
136 /*cursor c_notification_id is
137 select max(par1.pa_notification_id) notification_id
138 from ghr_pa_requests par1
139 where par1.person_id = p_person_id
140 and par1.pa_notification_id is NOT Null
141 and par1.noa_family_code NOT in ('CORRECT', 'CANCEL')
142 and nvl(par1.first_noa_cancel_or_correct, 'normal') <> 'CANCEL'
143 and par1.effective_date = ( select max(par2.effective_date) maxdate
144 from ghr_pa_requests par2
145 where par2.person_id = p_person_id
146 and par2.pa_notification_id is NOT Null
147 and par2.effective_date <= p_effective_date
148 and par2.noa_family_code NOT in ('CORRECT', 'CANCEL')
149 and nvl(par2.first_noa_cancel_or_correct, 'normal') <> 'CANCEL' ) ;*/
150
151 --Begin bug 12652755
152 cursor c_notification_id is
153 select max(par1.pa_notification_id) notification_id
154 from ghr_pa_requests par1
155 where par1.person_id = p_person_id
156 and par1.pa_notification_id is NOT Null
157 and par1.noa_family_code NOT in ('CANCEL')
158 and nvl(par1.altered_pa_request_id,par1.pa_request_id) not in
159 ( select par2.altered_pa_request_id from ghr_pa_requests par2
160 where par2.altered_pa_request_id = nvl(par1.altered_pa_request_id,par1.pa_request_id)
161 and par2.pa_notification_id is not null
162 and par2.first_noa_code = '001')
163 and par1.effective_date =
164 (select max(effective_date) from ghr_pa_requests par3
165 where par3.pa_notification_id is not null
166 and par3.person_id=p_person_id
167 and par3.effective_date <= p_effective_date
168 and par3.first_noa_code <> '001'
169 and nvl(par3.altered_pa_request_id,par3.pa_request_id) not in
170 (select par4.altered_pa_request_id from ghr_pa_requests par4
171 where par4.altered_pa_request_id = nvl(par3.altered_pa_request_id,par3.pa_request_id)
172 and par4.pa_notification_id is not null
173 and par4.first_noa_code = '001'));
174 --End bug# 12652755
175
176 cursor c_request_id (c_notification_id number) is
177 select pa_request_id
178 from ghr_pa_requests g
179 where g.pa_notification_id = c_notification_id ;
180
181 l_notification_id ghr_pa_requests.pa_notification_id%type;
182 l_pa_request_id ghr_pa_requests.pa_request_id%type;
183 l_proc varchar2(72);
184
185
186 BEGIN
187
188 l_proc := g_package||'.get_latest_pa_req_id';
189
190 if ( hr_utility.debug_enabled()) then
191 hr_utility.set_location('Entering... ' ||l_proc,1000);
192 hr_utility.set_location('Person_id : ' || p_person_id, 1000);
193 hr_utility.set_location('Effective_date : ' || p_effective_date, 1000);
194 end if;
195
196 for notification_id_rec in c_notification_id
197 loop
198 l_notification_id := notification_id_rec.notification_id;
199 exit;
200 end loop;
201
202 hr_utility.set_location('notification_id : ' || l_notification_id, 1000);
203
204 for pa_request_id_rec in c_request_id (l_notification_id)
205 loop
206 l_pa_request_id := pa_request_id_rec.pa_request_id;
207 exit;
208 end loop;
209
210 hr_utility.set_location('pa_request_id : ' || l_pa_request_id, 1000);
211
212 return (l_pa_request_id);
213
214 END get_latest_pa_req_id;
215
216 -- This function returns the latest performance rating for the person on or before a given date.
217
218 Function get_latest_perf_rating(p_person_id in number,
219 p_effective_date in Date) return varchar2 is
220
221 -- cursor get_flex_num gives the flex number id for performance appraisal SIT.
222 -- This cursor will be used only for the first time this function is called in a session.
223 -- From the second time it uses the flex number stored in cache.
224
225 cursor get_flex_num is
226 select flx.id_flex_num id_flex_num
227 from fnd_id_flex_structures_tl flx
228 where flx.id_flex_code = 'PEA' and
229 flx.application_id = 800 and
230 flx.id_flex_structure_name = 'US Fed Perf Appraisal' and
231 flx.language = 'US' ;
232
233 -- cursor get_latest_perf_rating gets the latest performance rating.
234 -- Latest Performance Rating: use Appraisal Start Date and Rating of Record;
235 -- if duplicate Start Dates, go with the greatest ID
236
237 cursor get_latest_perf_rating is
241 where pan.person_id = p_person_id and
238 select pan.person_analysis_id, pea.segment2 rating_of_record
239 from per_analysis_criteria pea,
240 per_person_analyses pan
242 pan.id_flex_num = g_perf_flex_num and
243 pea.id_flex_num = pan.id_flex_num and
244 nvl(pan.date_from,sysdate) between nvl(pea.start_date_active,nvl(pan.date_from,sysdate) )
245 and nvl(pea.end_date_active,nvl(pan.date_from,sysdate) ) and
246 pan.analysis_criteria_id = pea.analysis_criteria_id and
247 trunc(nvl(pan.date_from,sysdate)) = (select max(trunc(nvl(pan.date_from,sysdate))) max_date_from
248 from per_person_analyses pan
249 where pan.person_id = p_person_id and
250 pan.id_flex_num = g_perf_flex_num and
251 trunc(nvl(pan.date_from,sysdate)) <= p_effective_date )
252
253 order by person_analysis_id desc ;
254
255 l_max_date_from date;
256 l_perf_rating varchar2(10);
257 l_proc varchar2(72);
258 Begin
259
260 l_proc := g_package||'.get_latest_perf_rating';
261
262 if ( hr_utility.debug_enabled()) then
263 hr_utility.set_location('Entering... ' ||l_proc,1000);
264 hr_utility.set_location('Person_id : ' || p_person_id, 1000);
265 hr_utility.set_location('Effective_date : ' || p_effective_date, 1000);
266 hr_utility.set_location('perf Flex Num : ' || g_perf_flex_num, 1000);
267 end if;
268
269 If g_perf_flex_num is null THEN
270 for get_flex_num_rec in get_flex_num
271 loop
272 g_perf_flex_num := get_flex_num_rec.id_flex_num;
273 exit;
274 End loop;
275 END IF;
276
277 hr_utility.set_location('perf Flex Num : ' || g_perf_flex_num, 10);
278
279 for get_latest_perf_rating_rec in get_latest_perf_rating
280 loop
281 l_perf_rating := get_latest_perf_rating_rec.rating_of_record;
282 exit;
283 End loop;
284
285 hr_utility.set_location('Performance rating : ' || l_perf_rating, 1000);
286
287 return( l_perf_rating);
288
289 End get_latest_perf_rating;
290 --
291 -- This function returns the currency code of an element on an effective date.
292
293 Function retrieve_element_curr_code (p_element_name in pay_element_types_f.element_name%type,
294 p_assignment_id in pay_element_entries_f.assignment_id%type,
295 p_business_group_id in per_all_assignments_f.business_group_id%type,
296 p_effective_date in date ) return varchar2 is
297
298 --
299 l_proc varchar2(72);
300 l_new_element_name VARCHAR2(80);
301 l_input_curr_code varchar2(10);
302
303 --
304
305 Cursor c_ele_input_curr_code is
306
307 select elt.input_currency_code input_curr_code
308 from pay_element_types_f elt,
309 pay_element_entries_f ele
310 where
311 trunc(p_effective_date) between elt.effective_start_date
312 and elt.effective_end_date
313 and trunc(p_effective_date) between ele.effective_start_date
314 and ele.effective_end_date
315 and ele.assignment_id = p_assignment_id
316 and elt.element_type_id = ele.element_type_id
317 and upper(elt.element_name) = upper(l_new_element_name)
318 and (elt.business_group_id is null or elt.business_group_id = p_business_group_id) ;
319
320
321
322 Begin
323
324 -- Initialization
325 l_proc := g_package||'retrieve_element_curr_code';
326
327 ---- pqp_fedhr_uspay_int_utils.return_new_element_name is called to get the new element name
328
329 ---- For all elements in HR User old function will fetch the same name.
330 ---- because of is_script will be FALSE
331 ----
332 ---- For all elements (except BSR) in Payroll user old function.
333 ---- for BSR a new function which will fetch from assignmnet id.
334 ----
335 l_new_element_name := p_element_name;
336
337 IF (p_element_name = 'Basic Salary Rate'
338 and (fnd_profile.value('HR_USER_TYPE') = 'INT')) THEN
339 hr_utility.set_location('PAYROLL User -- BSR -- from asgid-- '||l_proc, 1);
340 l_new_element_name :=
341 pqp_fedhr_uspay_int_utils.return_new_element_name(
342 p_assignment_id => p_assignment_id,
343 p_business_group_id => p_business_group_id,
344 p_effective_date => p_effective_date);
345
346 ELSE
347 hr_utility.set_location('HR USER or PAYROLL User without BSR element -- from elt name -- '||l_proc, 1);
348 l_new_element_name :=
349 pqp_fedhr_uspay_int_utils.return_new_element_name(
350 p_fedhr_element_name => p_element_name,
351 p_business_group_id => p_business_group_id,
352 p_effective_date => p_effective_date,
353 p_pay_basis => NULL);
354
355 END IF;
356
357 if ( hr_utility.debug_enabled()) then
358 hr_utility.set_location('Element Name ' ||p_element_name,1000);
359 hr_utility.set_location('BG ID '|| p_business_group_id,2000);
360 hr_utility.set_location('Eff date'|| p_effective_date ,3000);
361 hr_utility.set_location('New element Name ' ||l_new_element_name,100000);
362 end if;
363
364 for c_ele_input_curr_code_rec in c_ele_input_curr_code
365 Loop
366 l_input_curr_code := c_ele_input_curr_code_rec.input_curr_code;
367 exit;
368 End Loop;
369
370 hr_utility.set_location('Input currency code ' ||l_input_curr_code,100000);
371
372 return (l_input_curr_code);
373
374 End retrieve_element_curr_code;
375
376 -- This is a wrapper function to get the locality pay area percentage.
377 -- This function calls ghr_per_sum.get_duty_station_details procedure.
378
379 function get_loc_pay_area_percentage (p_location_id in number,
380 p_effective_date in date ) return varchar2 is
381 --
382
383 l_locality_pay_area_percentage number;
384 l_locality_pay_area varchar2(80);
385 l_duty_station_desc varchar2(80);
386 l_duty_sation_code varchar2(80);
387 l_proc varchar2(72);
388
389 Begin
390
391 l_proc := g_package||'.get_loc_pay_area_percentage';
392
393 if ( hr_utility.debug_enabled()) then
394 hr_utility.set_location('Entering... ' ||l_proc,1000);
395 hr_utility.set_location('Location id : ' || p_location_id, 1000);
396 hr_utility.set_location('Effective_date : ' || p_effective_date, 1000);
397 end if;
398
399 ghr_per_sum.get_duty_station_details (p_location_id
400 ,p_effective_date
401 ,l_duty_sation_code
402 ,l_duty_station_desc
403 ,l_locality_pay_area
404 ,l_locality_pay_area_percentage ) ;
405
406 hr_utility.set_location('locality pay area percentage : ' || l_locality_pay_area_percentage, 1000);
407 return (l_locality_pay_area_percentage);
408
409 End get_loc_pay_area_percentage;
410
411
412 --This function checks out whether a person has got an Award
413 --for a given assignment
414
415 function check_if_awards_exists ( p_assignment_id in number,
416 p_effective_date in date ) return varchar2 is
417 --
418
419 l_award_bonus VARCHAR2(5);
420 l_proc VARCHAR2(100);
421
422
423 CURSOR award_check (l_assignment_id NUMBER,
424 l_effective_date DATE ) IS
425 SELECT eef.element_entry_id
426 FROM pay_element_entries_f eef,
427 pay_element_types_f elt
428 WHERE eef.assignment_id = l_assignment_id
429 AND eef.effective_start_date <= l_effective_date
430 AND elt.element_type_id = eef.element_type_id
431 AND eef.effective_start_date BETWEEN elt.effective_start_date
432 AND elt.effective_end_date
433 AND UPPER(pqp_fedhr_uspay_int_utils.return_old_element_name
434 (elt.element_name,
435 elt.business_group_id,
436 eef.effective_start_date)) = 'FEDERAL AWARDS' ;
437
438
439
440 BEGIN
441
442 l_proc := g_package||'.check_if_awards_exists';
443 l_award_bonus := 'No' ;
444
445 if ( hr_utility.debug_enabled()) then
446 hr_utility.set_location('Entering... ' ||l_proc,1000);
447 hr_utility.set_location('assignement_id : ' || p_assignment_id, 1001);
448 hr_utility.set_location('Effective_date : ' || p_effective_date, 1002);
449 end if;
450
451 FOR award_check_rec IN award_check (p_assignment_id , p_effective_date)LOOP
452 l_award_bonus := 'Yes';
453 EXIT;
454 END LOOP;
455
456 hr_utility.set_location('Check for Award : ' || l_award_bonus, 1004);
457
458 RETURN(l_award_bonus);
459
460 END check_if_awards_exists;
461
462 --This function checks out whether a person has got an Award
463 --for a given assignment
464
465 FUNCTION check_if_bonus_exists (p_assignment_id IN NUMBER,
466 p_effective_date IN DATE ) RETURN VARCHAR2 IS
467 --
468
469 l_award_bonus VARCHAR2(5);
470 l_proc VARCHAR2(100);
471
472
473
474 CURSOR bonus_check (l_assignment_id NUMBER,
475 l_effective_date DATE ) IS
476 SELECT eef.element_entry_id
477 FROM pay_element_entries_f eef,
478 pay_element_types_f elt
479 WHERE eef.assignment_id = l_assignment_id
480 AND eef.effective_start_date <= l_effective_date
481 AND elt.element_type_id = eef.element_type_id
482 AND eef.effective_start_date BETWEEN elt.effective_start_date
483 AND elt.effective_end_date
484 AND UPPER(pqp_fedhr_uspay_int_utils.return_old_element_name
485 (elt.element_name,
486 elt.business_group_id,
487 eef.effective_start_date)) IN ('RELOCATION BONUS',
488 'RECRUITMENT BONUS' );
489
490 BEGIN
491
492 l_proc := g_package||'.check_if_bonus_exists';
493 l_award_bonus := 'No' ;
494 if ( hr_utility.debug_enabled()) then
495 hr_utility.set_location('Entering... ' ||l_proc,1000);
496 hr_utility.set_location('assignement_id : ' || p_assignment_id, 1001);
497 hr_utility.set_location('Effective_date : ' || p_effective_date, 1002);
498 end if;
499
500 FOR bonus_check_rec IN bonus_check (p_assignment_id , p_effective_date)LOOP
501 l_award_bonus := 'Yes';
502 EXIT;
503 END LOOP;
504
505
506 hr_utility.set_location('Check for Bonus : ' || l_award_bonus, 1004);
507
508 RETURN(l_award_bonus);
509
510 END check_if_bonus_exists;
511
512 -- This function returns the history id for a particular information type of a person
513 -- depending on the effective date and the person type.
514
515 function get_history_id(p_assignment_type in varchar2,
516 p_person_id in number,
517 p_information_type in varchar2,
518 p_effective_date in date
519 ) return number is
520 l_proc varchar2(72) ;
521 l_history_id ghr_pa_history.pa_history_id%type;
522
523 begin
524 l_proc := g_package||'get_history_id';
525 if (p_assignment_type = 'E' or p_assignment_type = 'C') then
526 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)
527 into l_history_id
528 from dual;
529 end if;
530 return (l_history_id);
531 end get_history_id;
532
533 Function get_assignment_ei_id_ason_date( p_asg_id in number,
534 p_information_type in varchar2,
535 p_effective_date in date) return number is
536
537 l_proc varchar2(72) ;
538 l_history_id ghr_pa_history.pa_history_id%type;
539
540
541 -- This cursor gets the latest date on which the given Extra information Type record
542 -- is inserted or updated on or before the given date
543 -- and then gets the the record with highest history id on that date.
544
545 -- In c_history_id join on PER_ASSIGNMENT_EXTRA_INFO is required to skip the history records
546 -- on the extra information records that are deleted.
547 cursor c_history_id is
548 select nvl(max(gph1.pa_history_id),hr_api.g_number) pa_history_id
549 from ghr_pa_history gph1,
550 PER_ASSIGNMENT_EXTRA_INFO pei1
551 where gph1.table_name = 'PER_ASSIGNMENT_EXTRA_INFO'
552 and pei1.assignment_id = p_asg_id
553 and pei1.information_type = p_information_type
554 and gph1.information1 = to_char(pei1.assignment_extra_info_id)
555 and gph1.effective_date = ( select max(gph2.effective_date)maxdate
556 from ghr_pa_history gph2
557 where gph2.table_name = 'PER_ASSIGNMENT_EXTRA_INFO'
558 and gph2.effective_date <= p_effective_date
559 and gph2.information1 = to_char(pei1.assignment_extra_info_id)) ;
560
561
562
563 Begin
564
565 l_proc := g_package||'.get_asg_ei_id_ason_date';
566
567 if ( hr_utility.debug_enabled()) then
568 hr_utility.set_location('Entering... ' ||l_proc,1000);
569 hr_utility.set_location('Assignment_id : ' || p_asg_id, 1000);
570 hr_utility.set_location('Information_type : ' || p_information_type, 1000);
571 hr_utility.set_location('Effective_date : ' || p_effective_date, 1000);
572 End if;
573
574 for history_id_rec in c_history_id loop
575 l_history_id := history_id_rec.pa_history_id;
576 exit;
577 end loop;
578
579 hr_utility.set_location('history_id : ' || l_history_id, 1000);
580 return (l_history_id);
581
582 End get_assignment_ei_id_ason_date;
583
584 --
585
586 Function get_position_ei_id_ason_date( p_position_id in number,
587 p_information_type in varchar2,
588 p_effective_date in date
589 ) return number is
590
591 l_proc varchar2(72) ;
592 l_history_id ghr_pa_history.pa_history_id%type;
593
594
595 -- This cursor gets the latest date on which the given Extra information Type record
596 -- is inserted or updated on or before the given date
597 -- and then gets the the record with highest history id on that date.
598
599 -- In c_history_id join on PER_POSITION_EXTRA_INFO is required to skip the history records
600 -- on the extra information records that are deleted.
601 cursor c_history_id is
602 select nvl(max(gph1.pa_history_id),hr_api.g_number) pa_history_id
603 from ghr_pa_history gph1,
604 PER_POSITION_EXTRA_INFO pei1
605 where gph1.table_name = 'PER_POSITION_EXTRA_INFO'
606 and pei1.position_id = p_position_id
607 and pei1.information_type = p_information_type
608 and gph1.information1 = to_char(pei1.position_extra_info_id)
609 and gph1.effective_date = ( select max(gph2.effective_date)maxdate
610 from ghr_pa_history gph2
611 where gph2.table_name = 'PER_POSITION_EXTRA_INFO'
612 and gph2.effective_date <= p_effective_date
613 and gph2.information1 = to_char(pei1.position_extra_info_id)) ;
614
615 Begin
616
617 l_proc := g_package||'.get_position_ei_id_ason_date';
618
619 if ( hr_utility.debug_enabled()) then
620 hr_utility.set_location('Entering... ' ||l_proc,1000);
621 hr_utility.set_location('position_id : ' || p_position_id, 1000);
622 hr_utility.set_location('Information_type : ' || p_information_type, 1000);
623 hr_utility.set_location('Effective_date : ' || p_effective_date, 1000);
624 End if;
625
626 for history_id_rec in c_history_id loop
627 l_history_id := history_id_rec.pa_history_id;
628 exit;
629 end loop;
630
631 hr_utility.set_location('history_id : ' || l_history_id, 1000);
632
633 return (l_history_id);
634
635 end get_position_ei_id_ason_date;
636
637 --Start of fix for Bug#6085591
638 -- This function returns the Rating of Record for the person .
639
640 Function get_rating_of_record(p_person_id in number) return varchar2 is
641
642 -- cursor get_flex_num gives the flex number id for performance appraisal SIT.
643 -- This cursor will be used only for the first time this function is called in a session.
644 -- From the second time it uses the flex number stored in cache.
645
646 cursor get_flex_num is
647 select flx.id_flex_num id_flex_num
648 from fnd_id_flex_structures_tl flx
649 where flx.id_flex_code = 'PEA' and flx.application_id = 800 and
650 flx.id_flex_structure_name = 'US Fed Perf Appraisal' and
651 flx.language = 'US';
652
653 -- cursor get_rating_of_record gets the rating of record.
654
655 CURSOR get_rating_of_record IS
656 SELECT pan.person_analysis_id, pea.segment5 rating_of_record
657 FROM per_analysis_criteria pea, per_person_analyses pan
658 WHERE pan.person_id = p_person_id and
659 pan.id_flex_num = g_perf_flex_num AND
660 pea.id_flex_num = pan.id_flex_num AND
661 nvl(pan.date_from, sysdate) BETWEEN
662 nvl(pea.start_date_active, nvl(pan.date_from, sysdate)) AND
663 nvl(pea.end_date_active, nvl(pan.date_from, sysdate)) AND
664 pan.analysis_criteria_id = pea.analysis_criteria_id AND
665 trunc(nvl(to_date(pea.segment3,'yyyy/mm/dd hh24:mi:ss'),nvl(pan.date_from, sysdate)))
666 = (SELECT max(trunc(nvl( to_date(pea.segment3,'yyyy/mm/dd hh24:mi:ss'),nvl(pan.date_from, sysdate)))) max_eff_date
667 FROM per_analysis_criteria pea,
668 per_person_analyses pan
669 WHERE pan.person_id = p_person_id AND
670 pan.id_flex_num = g_perf_flex_num AND
671 pea.id_flex_num = pan.id_flex_num AND
672 pan.analysis_criteria_id = pea.analysis_criteria_id AND
673 trunc(nvl(to_date(pea.segment3,'yyyy/mm/dd hh24:mi:ss'),nvl(pan.date_from, sysdate))) <= sysdate)
674
675 ORDER BY person_analysis_id DESC;
676
677 l_max_date_from date;
678 l_perf_rating varchar2(10);
679 l_proc varchar2(72);
680 Begin
681
682 l_proc := g_package || '.get_rating_of_record';
683
684 if (hr_utility.debug_enabled()) then
685 hr_utility.set_location('Entering... ' || l_proc, 1001);
686 hr_utility.set_location('Person_id : ' || p_person_id, 1001);
687 hr_utility.set_location('perf Flex Num : ' || g_perf_flex_num, 1001);
688 end if;
689
690 If g_perf_flex_num is null THEN
691 for get_flex_num_rec in get_flex_num loop
692 g_perf_flex_num := get_flex_num_rec.id_flex_num;
693 exit;
694 End loop;
695 END IF;
696
697 hr_utility.set_location('perf Flex Num : ' || g_perf_flex_num, 101);
698
699 for get_rating_of_record_rec in get_rating_of_record loop
700 l_perf_rating := get_rating_of_record_rec.rating_of_record;
701 exit;
702 End loop;
703
704 hr_utility.set_location('Performance rating : ' || l_perf_rating, 1001);
705
706 return(l_perf_rating);
707
708 End get_rating_of_record;
709
710 --End of fix for Bug#6085591
711
712 --Beginning of bug fix 6781928
713 FUNCTION get_assignment_start_date(p_person_id IN NUMBER) RETURN DATE IS
714
715 CURSOR assignement_start_date(p_person_id NUMBER) IS
716 SELECT MIN(effective_start_date) effective_start_date
717 FROM per_all_assignments_f
718 WHERE person_id = p_person_id AND
719 assignment_id =
720 (SELECT MAX(assignment_id)
721 FROM per_all_assignments_f
722 WHERE person_id = p_person_id AND assignment_type in ('E','C')) AND
723 assignment_type in ('E','C');
724 l_ass_st_date DATE;
725 BEGIN
726
727 FOR l_assignement_start_date IN assignement_start_date(p_person_id) LOOP
728 l_ass_st_date := l_assignement_start_date.effective_start_date;
729 exit;
730 END LOOP;
731
732 RETURN l_ass_st_date;
733
734 END get_assignment_start_date;
735
736 Function get_assignment_end_date(p_person_id in number) return date is
737
738 cursor assignement_end_date (p_person_id number) is
739 select max(effective_end_date) effective_end_date
740 from per_all_assignments_f
741 where person_id = p_person_id and
742 assignment_type in ('E','C') ;
743 l_ass_end_date date;
744 Begin
745
746 for l_assignement_end_date in assignement_end_date(p_person_id) loop
747 l_ass_end_date := l_assignement_end_date.effective_end_date;
748 exit;
749 end loop;
750
751 return l_ass_end_date ;
752
753 End get_assignment_end_date;
754 --End of bug fix 6781928
755
756 /*Bug: 8653508 Detail Action*/
757 FUNCTION get_duty_station_desc(p_DUTY_STATION_ID in number, p_EFFECTIVE_DATE in date) return varchar2 is
758 CURSOR c_get_duty_station(p_DUTY_STATION_ID number, p_EFFECTIVE_DATE date) IS
759 select
760 GDSV.DUTY_STATION_CODE,
761 GDSV.DUTY_STATION_DESC Description, --bug# 9646458
762 GDSV.DUTY_STATION_ID
763 from
764 GHR_DUTY_STATIONS_F GDS, GHR_LOCALITY_PAY_AREAS_F LPA, GHR_DUTY_STATIONS_V GDSV
765 WHERE GDS.DUTY_STATION_ID = GDSV.DUTY_STATION_ID
766 AND GDS.LOCALITY_PAY_AREA_ID = LPA.LOCALITY_PAY_AREA_ID
767 AND p_EFFECTIVE_DATE BETWEEN LPA.EFFECTIVE_START_DATE AND LPA.EFFECTIVE_END_DATE
768 AND p_EFFECTIVE_DATE BETWEEN GDS.EFFECTIVE_START_DATE AND GDS.EFFECTIVE_END_DATE
769 AND p_EFFECTIVE_DATE BETWEEN NVL(GDSV.EFFECTIVE_START_DATE,p_EFFECTIVE_DATE)
770 AND NVL(GDSV.EFFECTIVE_END_DATE,p_EFFECTIVE_DATE)
771 AND GDSV.DUTY_STATION_ID = p_DUTY_STATION_ID
772 ORDER BY GDSV.DUTY_STATION_CODE;
773 l_duty_station_desc varchar2(200);
774
775 BEGIN
776 FOR l_get_duty_station in c_get_duty_station(p_DUTY_STATION_ID, p_EFFECTIVE_DATE) loop
777 l_duty_station_desc := l_get_duty_station.Description;
778 exit;
779 END LOOP;
780 return l_duty_station_desc;
781 END get_duty_station_desc;
782 /*Bug: 8653508 Detail Action*/
783 --Begin Bug 10174467
784 -- These functions are introduced since Total pay and Currency codes returning NULL for Ex-emp.
785 --For Benefit Assignment records there won't be any total pay and Currency code so taking latest Active assignment data.
786
787 FUNCTION get_per_ele_value_ason_date(p_ele_name in varchar2
788 ,p_input_name in varchar2
789 ,p_person_id IN NUMBER
790 ,P_BUSINESS_GROUP_ID in Number) RETURN varchar2 IS
791
792 CURSOR assignement_id(p_person_id NUMBER) IS
793 SELECT MAX(assignment_id) assg_id
794 FROM per_all_assignments_f
795 WHERE person_id = p_person_id AND assignment_type in ('E','C');
796 l_asg_id number;
797 l_screen_entry_value varchar2 (150);
798 l_effective_start_date date;
799 l_proc varchar2(50);
800 BEGIN
801
802 l_proc := g_package||'.get_per_ele_value_ason_date';
803 hr_utility.set_location('Entering... ' ||l_proc,1001);
804 FOR l_assignement_id in assignement_id(p_person_id) LOOP
805 l_asg_id := l_assignement_id.assg_id;
806 exit;
807 END LOOP;
808 --BUG # 14558097 modified to consider assignment end date
809 -- fetch the total pay of the employee
810 ghr_per_sum.get_element_details(p_ele_name,
811 p_input_name,
812 l_asg_id,
813 ghr_ss_views_pkg.get_assignment_end_date(p_person_id),
814 l_screen_entry_value,
815 l_effective_start_date,
816 P_BUSINESS_GROUP_ID
817 );
818 hr_utility.set_location('Person Element input value : ' || l_screen_entry_value, 1000);
819 return (l_screen_entry_value);
820
821 END get_per_ele_value_ason_date;
822
823 Function get_elmt_curr_code_ason_dt (
824 p_element_name in pay_element_types_f.element_name%type,
825 p_person_id in number,
826 p_business_group_id in per_all_assignments_f.business_group_id%type)
827 return varchar2 is
828
829 l_proc varchar2(72);
830 l_new_element_name VARCHAR2(80);
831 l_input_curr_code varchar2(10);
832 p_effective_date date;
833 p_assignment_id pay_element_entries_f.assignment_id%type;
834
835 CURSOR assignement_start_date(p_person_id NUMBER) IS
836 SELECT MIN(effective_start_date) effective_start_date,
837 MIN(assignment_id) assignment_id
838 FROM per_all_assignments_f
839 WHERE person_id = p_person_id AND
840 assignment_id =
841 (SELECT MAX(assignment_id)
842 FROM per_all_assignments_f
843 WHERE person_id = p_person_id AND assignment_type in ('E','C'))
844 AND assignment_type in ('E','C');
845
846 Cursor c_ele_input_curr_code is
847 select elt.input_currency_code input_curr_code
848 from pay_element_types_f elt,
849 pay_element_entries_f ele
850 where
851 trunc(p_effective_date) between elt.effective_start_date
852 and elt.effective_end_date
853 and trunc(p_effective_date) between ele.effective_start_date
854 and ele.effective_end_date
855 and ele.assignment_id = p_assignment_id
856 and elt.element_type_id = ele.element_type_id
857 and upper(elt.element_name) = upper(l_new_element_name)
858 and (elt.business_group_id is null or elt.business_group_id = p_business_group_id) ;
859
860 Begin
861 l_proc := g_package||'get_elmt_curr_code_ason_dt';
862 l_new_element_name := p_element_name;
863
864 FOR l_assignement_start_date IN assignement_start_date(p_person_id) LOOP
865 p_effective_date :=l_assignement_start_date.effective_start_date;
866 p_assignment_id := l_assignement_start_date.assignment_id;
867 END LOOP;
868 l_new_element_name :=
869 pqp_fedhr_uspay_int_utils.return_new_element_name(
870 p_fedhr_element_name => p_element_name,
871 p_business_group_id => p_business_group_id,
872 p_effective_date => p_effective_date,
873 p_pay_basis => NULL);
874
875 for c_ele_input_curr_code_rec in c_ele_input_curr_code
876 Loop
877 l_input_curr_code := c_ele_input_curr_code_rec.input_curr_code;
878 exit;
879 End Loop;
880
881 hr_utility.set_location('Input currency code ' ||l_input_curr_code,100000);
882
883 return (l_input_curr_code);
884
885 End get_elmt_curr_code_ason_dt;
886 --End Bug 10174467
887
888 END ghr_ss_views_pkg;
[Home] [Help]