eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.HR_MEE_VIEWS_GEN

Source


1 package body hr_mee_views_gen AS
2 /* $Header: hrmegviw.pkb 120.16.12010000.3 2009/07/17 08:30:45 gpurohit ship $ */
3 
4 TYPE cur_typ IS REF CURSOR;
5 
6 g_hours_per_week  NUMBER:= g_hours_per_day * 5;
7 g_hours_per_month NUMBER:= g_hours_per_week * 4.225;
8 g_hours_per_year  NUMBER:= g_hours_per_month * 12;
9 
10 
11 --bug 5890210
12 function getCostCenter(
13       p_assignment_id NUMBER
14     ) return varchar2
15   is
16   cursor getCC is  --this cursor will read the cc for the assignment
17   SELECT
18     pcak.cost_allocation_keyflex_id,
19     pcak.concatenated_segments,
20     pcaf.proportion
21   FROM
22     per_all_assignments_f assg,
23     pay_cost_allocations_f pcaf,
24     pay_cost_allocation_keyflex pcak
25   WHERE assg.assignment_id = p_assignment_id
26   AND assg.assignment_id = pcaf.assignment_id
27   AND assg.Primary_flag = 'Y'
28   AND pcaf.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id
29   AND pcak.enabled_flag = 'Y'
30   AND sysdate between nvl(pcaf.effective_start_date,sysdate)
31   and nvl(pcaf.effective_end_date,sysdate+1)
32   AND sysdate between nvl(assg.effective_start_date,sysdate)
33   and nvl(assg.effective_end_date,sysdate+1);
34 
35     cursor getCC_org is --this cursor will read the cc for the organizarion
36   SELECT
37      pcak.concatenated_segments
38    FROM
39      per_all_assignments_f assg,
40     hr_all_organization_units horg,
41      pay_cost_allocation_keyflex pcak
42    WHERE assg.assignment_id = p_assignment_id
43    AND assg.organization_id = horg.organization_id
44    AND assg.Primary_flag = 'Y'
45    AND horg.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id
46    AND pcak.enabled_flag = 'Y'
47    AND sysdate between nvl(assg.effective_start_date,sysdate)
48    and nvl(assg.effective_end_date,sysdate+1);
49 
50 
51   result varchar2(1000) := null ;
52 begin
53 
54   For a in getCC loop
55    if result is null then
56      result := a.concatenated_segments ||' : '|| a.proportion*100 || '% ';
57    else
58      result := result ||', ' || a.concatenated_segments ||' : '|| a.proportion*100 ||'% ';
59    end if;
60  end loop;
61 
62  For a in getCC_org loop
63    if result is null then
64      result := a.concatenated_segments ;
65    end if;
66  end loop;
67 
68  return result;
69 end;
70 --bug 5890210
71 
72 FUNCTION getYearStart RETURN DATE IS
73 BEGIN
74     return hr_util_misc_ss.g_year_start;
75 END getYearStart;
76 
77 FUNCTION getRateType RETURN VARCHAR2 IS
78 BEGIN
79     return hr_util_misc_ss.g_rate_type;
80 END getRateType;
81 
82 FUNCTION convertDuration(
83          p_from_duration_units IN VARCHAR2
84         ,p_to_duration_units IN VARCHAR2
85         ,p_from_duration IN NUMBER) RETURN NUMBER
86 IS
87 l_to_duration NUMBER:=0;
88 l_hours NUMBER:= 0;
89 BEGIN
90     IF (p_from_duration_units = 'Y') THEN
91         l_hours := p_from_duration * g_hours_per_year;
92     ELSIF (p_from_duration_units = 'M') THEN
93         l_hours := p_from_duration * g_hours_per_month;
94     ELSIF (p_from_duration_units = 'W') THEN
95         l_hours := p_from_duration * g_hours_per_week;
96     ELSIF (p_from_duration_units = 'D') THEN
97         l_hours := p_from_duration * g_hours_per_day;
98     ELSIF (p_from_duration_units = 'H') THEN
99         l_hours := p_from_duration;
100     ELSE
101         l_hours := 0;
102     END IF;
103 
104     IF (p_to_duration_units = 'H') THEN
105         l_to_duration := l_hours;
106     ELSIF (p_to_duration_units = 'D') THEN
107         l_to_duration := l_hours / g_hours_per_day;
108     ELSIF (p_to_duration_units = 'W') THEN
109         l_to_duration := l_hours / g_hours_per_week;
110     ELSIF (p_to_duration_units = 'M') THEN
111         l_to_duration := l_hours / g_hours_per_month;
112     ELSIF (p_to_duration_units = 'Y') THEN
113         l_to_duration := l_hours / g_hours_per_year;
114     ELSE
115         l_to_duration := 0;
116     END IF;
117 
118     RETURN l_to_duration;
119 
120 END convertDuration;
121 
122 PROCEDURE openClassesCsr(
123     p_cursor IN OUT NOCOPY cur_typ
124    ,p_mode IN NUMBER
125    ,p_person_id IN NUMBER
126    ,p_eff_date IN DATE) IS
127 query_str VARCHAR2(4000);
128 BEGIN
129     query_str := 'SELECT '||
130                  'sum(hr_mee_views_gen.convertDuration(evt.duration_units, ''H'',evt.duration)) '||
131                  'FROM ota_booking_status_types bst, '||
132                  '     ota_events evt, ota_delegate_bookings db '||
133                  'WHERE db.booking_status_type_id = bst.booking_status_type_id '||
134                  'AND db.event_id = evt.event_id '||
135                  'AND db.delegate_person_id = :1 ';
136 
137     IF (p_mode = 0) THEN -- Classes Taken
138      query_str := query_str ||
139         'AND bst.type = ''A'' '||
140         'AND evt.course_start_date <= :2 ';
141      OPEN p_cursor FOR query_str USING p_person_id, p_eff_date;
142     ELSIF (p_mode = 1) THEN -- Classes Taken YTD
143      query_str := query_str ||
144         'AND bst.type = ''A'' '||
145         'AND evt.course_start_date between :2 and :3';
146      OPEN p_cursor FOR query_str USING p_person_id, getYearStart, p_eff_date;
147     END IF;
148 END openClassesCsr;
149 
150 PROCEDURE openTrngCostCsr(
151     p_cursor IN OUT NOCOPY cur_typ
152    ,p_mode IN NUMBER
153    ,p_person_id IN NUMBER
154    ,p_eff_date IN DATE) IS
155 query_str VARCHAR2(4000);
156 BEGIN
157     query_str := 'SELECT SUM(hr_mee_views_gen.amtInLoginPrsnCurrency(evt.currency_code, fl.money_amount, evt.course_start_date)) '||
158                  'FROM ota_delegate_bookings db, '||
159                  '     ota_events evt, ota_finance_lines fl '||
160                  'WHERE db.booking_id = fl.booking_id(+) '||
161                  'AND nvl(fl.cancelled_flag(+),''N'') = ''N'' '||
162                  'AND db.event_id = evt.event_id '||
163                  'AND db.delegate_person_id = :1 ';
164 
165     IF (p_mode = 0) THEN -- Total Cost
166      query_str := query_str ||
167         'AND evt.course_start_date <= :2 ';
168      OPEN p_cursor FOR query_str USING p_person_id, p_eff_date;
169     ELSIF (p_mode = 1) THEN -- Total Cost YTD
170      query_str := query_str ||
171         'AND evt.course_start_date between :2 and :3';
172      OPEN p_cursor FOR query_str USING p_person_id, getYearStart, p_eff_date;
173     END IF;
174 END openTrngCostCsr;
175 
176 PROCEDURE openReqClassesCsr(
177     p_cursor IN OUT NOCOPY cur_typ
178    ,p_mode IN NUMBER
179    ,p_person_id IN NUMBER
180    ,p_eff_date IN DATE) IS
181 query_str VARCHAR2(4000);
182 BEGIN
183     query_str :=  'SELECT count(db.booking_id) '||
184                   'FROM ota_booking_status_types bst, '||
185                   '     ota_events evt, ota_delegate_bookings db '||
186                   'WHERE db.booking_status_type_id = bst.booking_status_type_id '||
187                   'AND db.event_id = evt.event_id '||
188                   'AND EXISTS (SELECT ''e'' '||
189                               'FROM ota_training_plans tp, ota_training_plan_members tpm '||
190                               'WHERE tp.person_id = db.delegate_person_id '||
191                               'AND tp.person_id <> tp.creator_person_id '||
192                               'AND tp.training_plan_id = tpm.training_plan_id '||
193                               'AND tpm.activity_version_id = evt.activity_version_id) '||
194                    'AND db.delegate_person_id = :1 ';
195 
196 
197      IF (p_mode = 0) THEN -- Classes Req By Mgr
198       query_str := query_str ||
199         'AND evt.course_start_date <= :2 ';
200       OPEN p_cursor FOR query_str USING p_person_id, p_eff_date;
201      ELSIF (p_mode = 1) THEN -- Classes Req By Mgr YTD
202       query_str := query_str ||
203         'AND evt.course_start_date between :2 and :3';
204         OPEN p_cursor FOR query_str USING p_person_id, getYearStart, p_eff_date;
205      ELSIF (p_mode = 2) THEN -- Classes Req By Mgr Completed
206       query_str := query_str ||
207         'AND bst.type = ''A'' '||
208         'AND evt.course_start_date <= :2 ';
209       OPEN p_cursor FOR query_str USING p_person_id, p_eff_date;
210      ELSIF (p_mode = 3) THEN -- Classes Req By Mgr Completed YTD
211       query_str := query_str ||
212         'AND bst.type = ''A'' '||
213         'AND evt.course_start_date between :2 and :3';
214         OPEN p_cursor FOR query_str USING p_person_id, getYearStart, p_eff_date;
215      ELSIF (p_mode = 4) THEN -- Classes Req By Mgr Enrolled
216       query_str := query_str ||
217         'AND bst.type not in (''C'') '||
218         'AND evt.course_start_date <= :2 ';
219         OPEN p_cursor FOR query_str USING p_person_id, p_eff_date;
220      END IF;
221 END openReqClassesCsr;
222 
223 FUNCTION getAnnualSalary(p_person_id IN NUMBER) RETURN NUMBER IS
224   CURSOR c_salary IS
225   SELECT decode(ppb.pay_annualization_factor,
226           null, 1,
227           0, 1,
228           ppb.pay_annualization_factor) * ppp.proposed_salary_n
229         ,petf.input_currency_code
230   FROM per_pay_bases ppb, per_assignments_f paf
231       ,per_pay_proposals ppp, pay_input_values_f ivf, pay_element_types_f petf
232   WHERE paf.person_id = p_person_id
233   AND paf.primary_flag = 'Y'
234   AND paf.assignment_type = 'E'
235   AND ppb.input_value_id = ivf.input_value_id
236   AND ivf.element_type_id = petf.element_type_id
237   AND ppp.change_date BETWEEN ivf.effective_start_date AND ivf.effective_end_date
238   AND ppp.change_date BETWEEN petf.effective_start_date AND petf.effective_end_date
239   AND ppp.change_date BETWEEN paf.effective_start_date AND paf.effective_end_date
240   AND ppp.assignment_id = paf.assignment_id
241   AND ppp.change_date = (SELECT max(change_date) FROM per_pay_proposals ippp
242                          WHERE ippp.assignment_id = paf.assignment_id
243                          AND ippp.approved = 'Y'
244                          AND ippp.change_date <= getEffDate)
245   AND ppb.pay_basis_id  = paf.pay_basis_id;
246 
247   l_salary NUMBER:= 0;
248   l_currency VARCHAR2(10);
249 
250 BEGIN
251    OPEN c_salary;
252     FETCH c_salary INTO l_salary, l_currency ;
253    CLOSE c_salary;
254    If (l_salary > 0) THEN
255     l_salary := amtInLoginPrsnCurrency(l_currency, l_salary, getEffDate);
256    End If;
257    return l_salary;
258    Exception When Others then
259     return 0;
260 END getAnnualSalary;
261 
262 FUNCTION getAvgClassesPerYear(p_person_id IN NUMBER) RETURN NUMBER
263 IS
264 BEGIN
265     RETURN round(getClassesTaken(p_person_id)/getYOSDenominator(p_person_id),2);
266 END getAvgClassesPerYear;
267 
268 FUNCTION getTrngDays(p_person_id IN NUMBER) RETURN NUMBER
269 IS
270  l_cnt NUMBER:=0;
271  l_cursor cur_typ;
272 BEGIN
273     RETURN nvl(convertDuration('H','D',getTrngHrs(p_person_id)),0);
274     Exception When Others then
275         return 0;
276 END getTrngDays;
277 
278 FUNCTION getTrngDaysYTD(p_person_id IN NUMBER) RETURN NUMBER
279 IS
280  l_cnt NUMBER:=0;
281  l_cursor cur_typ;
282 BEGIN
283     openClassesCsr(
284         l_cursor
285        ,1
286        ,p_person_id
287        ,getEffDate);
288     FETCH l_cursor INTO l_cnt;
289     CLOSE l_cursor;
290     RETURN nvl(convertDuration('H','D',l_cnt),0);
291     Exception When Others then
292         return 0;
293 END getTrngDaysYTD;
294 
295 FUNCTION getTrngHrs(p_person_id IN NUMBER) RETURN NUMBER
296 IS
297  l_cnt NUMBER:=0;
298  l_cursor cur_typ;
299 BEGIN
300     openClassesCsr(
301         l_cursor
302        ,0
303        ,p_person_id
304        ,getEffDate);
305     FETCH l_cursor INTO l_cnt;
306     CLOSE l_cursor;
307     RETURN (nvl(l_cnt,0));
308     Exception When Others then
309         return 0;
310 END getTrngHrs;
311 
312 FUNCTION getTrngCost(p_person_id IN NUMBER) RETURN NUMBER
313 IS
314  l_cnt NUMBER:=0;
315  l_cursor cur_typ;
316 BEGIN
317     openTrngCostCsr(
318         l_cursor
319        ,0
320        ,p_person_id
321        ,getEffDate);
322     FETCH l_cursor INTO l_cnt;
323     CLOSE l_cursor;
324     RETURN (nvl(l_cnt,0));
325     Exception When Others then
326         return 0;
327 END getTrngCost;
328 
329 FUNCTION getTrngCostYTD(p_person_id IN NUMBER) RETURN NUMBER
330 IS
331  l_cnt NUMBER:=0;
332  l_cursor cur_typ;
333 BEGIN
334     openTrngCostCsr(
335         l_cursor
336        ,0
337        ,p_person_id
338        ,getEffDate);
339     FETCH l_cursor INTO l_cnt;
340     CLOSE l_cursor;
341     RETURN (nvl(l_cnt,0));
342     Exception When Others then
343         return 0;
344 END getTrngCostYTD;
345 
346 FUNCTION get_training_center (p_training_center_id in number)
347 return varchar2
348 IS
349 l_training_center hr_all_organization_units.name%TYPE;
350 
351 CURSOR c_get_training_center
352 IS
353 SELECT  org.name
354 FROM  hr_all_organization_units org, hr_organization_information ori
355 WHERE org.organization_id = p_training_center_id
356       AND org.organization_id = ori.organization_id
357       AND ori.org_information_context = 'CLASS'
358       AND ori.org_information1 ='OTA_TC';
359 
360 BEGIN
361   For a in c_get_training_center
362  loop
363    l_training_center := a.name;
364  end loop;
365  return(l_training_center);
366 
367 END get_training_center ;
368 
369 FUNCTION getTrngPrctOnPayroll(p_person_id IN NUMBER) RETURN NUMBER
370 IS
371 l_percent NUMBER:=0;
372 BEGIN
373     l_percent := getAnnualSalary(p_person_id);
374     If (l_percent > 0) Then
375         l_percent := round((getTrngCostYTD(p_person_id)/l_percent)*100,2);
376     End IF;
377     return l_percent;
378 END getTrngPrctOnPayroll;
379 
380 FUNCTION getLoginPrsnCurrencyCode RETURN VARCHAR2
381 IS
382 BEGIN
383   if(fnd_profile.value('ICX_PREFERRED_CURRENCY') is not null and fnd_profile.value('ICX_PREFERRED_CURRENCY') <> 'ANY') then
384     return fnd_profile.value('ICX_PREFERRED_CURRENCY');
385   else
386     return hr_util_misc_ss.g_loginPrsnCurrencyCode;
387   end if;
388 END;
389 
390 FUNCTION getCompRatio(
391     p_from_currency IN VARCHAR2
392    ,p_to_currency IN VARCHAR2
393    ,p_annual_salary IN NUMBER
394    ,p_annual_grade_mid_value IN NUMBER
395    ,p_eff_date IN DATE
396    ) RETURN NUMBER IS
397 BEGIN
398   IF (p_annual_salary IS NOT NULL AND
399       p_annual_grade_mid_value > 0 AND
400       p_to_currency IS NOT NULL) THEN
401     return round((p_annual_salary * 100) / convertAmount(
402                                         nvl(p_from_currency,p_to_currency)
403                                        ,p_to_currency
404                                        ,p_annual_grade_mid_value
405                                        ,p_eff_date),3);
406   END IF;
407   return NULL;
408 END getCompRatio;
409 
410 Function getCompRatio(
411     p_from_currency IN VARCHAR2
412    ,p_to_currency IN VARCHAR2
413    ,p_assignment_id in number
414    ,P_Effective_Date  in date
415    ,p_proposed_salary IN NUMBER
416    ,p_pay_annual_factor IN number
417    ,p_pay_basis in varchar2
418    ,p_grade_annual_factor  in number
419    ,p_grade_basis  in varchar2
420    ,p_grade_mid_value  in number
421    ) return number is
422     l_fte_profile_value VARCHAR2(30) := fnd_profile.VALUE('PER_ANNUAL_SALARY_ON_FTE');
423     l_pay_factor number;
424     l_fte_factor  NUMBER;
425     ln_annual_salary NUMBER;
426     ln_grade_mid_point number;
427     l_compratio	number;
428 begin
429    l_pay_factor := p_pay_annual_factor;
430    if (p_pay_annual_factor is null OR p_pay_annual_factor = 0) then
431       l_pay_factor := 1;
432    end if;
433    if (p_pay_basis = 'HOURLY' and p_grade_basis = 'HOURLY') then
434       ln_annual_salary := p_proposed_salary;
435       ln_grade_mid_point := p_grade_mid_value;
436     elsif ((l_fte_profile_value is null OR l_fte_profile_value = 'Y') AND p_pay_basis <> 'HOURLY') then
437        l_fte_factor := per_saladmin_utility.get_fte_factor(p_assignment_id,P_Effective_Date);
438        ln_annual_salary := (p_proposed_salary * l_pay_factor)/l_fte_factor;
439        ln_grade_mid_point := p_grade_mid_value*p_grade_annual_factor;
440     else
441        ln_annual_salary := p_proposed_salary * l_pay_factor;
442        ln_grade_mid_point := p_grade_mid_value*p_grade_annual_factor;
443     end if;
444      l_compratio := getCompRatio(
445                p_from_currency	=> p_from_currency,
446                p_to_currency	=> p_to_currency,
447                p_annual_salary	=> ln_annual_salary,
448                p_annual_grade_mid_value => ln_grade_mid_point,
449                p_eff_date	=> P_Effective_Date);
450      return l_compratio;
451 END;
452 
453 /*
454     Modded to use hr_util_misc_ss.get_in_preferred_currency_num
455     Check its description for the functionality
456 */
457 FUNCTION convertAmount(
458     p_from_currency IN VARCHAR2
459    ,p_to_currency IN VARCHAR2
460    ,p_amount IN NUMBER
461    ,p_eff_Date IN DATE DEFAULT NULL
462    ) RETURN NUMBER IS
463    l_eff_date DATE;
464 BEGIN
465     return hr_util_misc_ss.get_in_preferred_currency_num(
466             p_amount
467            ,p_from_currency
468            ,p_eff_Date
469            ,p_to_currency);
470 END convertAmount;
471 
472 /*
473      This function returns grade min,mid,max and comparatio as of sysdate
474 */
475 FUNCTION get_grade_details(
476     p_assignment_id IN number,
477     p_mode in varchar2
478    ) RETURN NUMBER IS
479 
480 cursor c_grade_details is
481 
482 select gr.currency_code, gr.minimum, gr.mid_value, gr.maximum, petf.input_currency_code,
483            pb.grade_annualization_factor, ppp.proposed_salary_n, pb.pay_annualization_factor
484 from pay_input_values_f ivf, pay_element_types_f petf, pay_grade_rules_f gr,
485         per_pay_bases pb, per_assignments_f paf, per_pay_proposals ppp
486 where paf.assignment_id = p_assignment_id
487           AND paf.pay_basis_id = pb.pay_basis_id
488           and paf.assignment_id = ppp.assignment_id(+)
489           and pb.input_value_id = ivf.input_value_id
490           and ivf.element_type_id = petf.element_type_id
491          AND pb.rate_id = gr.rate_id
492          AND paf.grade_id = gr.grade_or_spinal_point_id
493          and ppp.approved(+) = 'Y'
494          and sysdate between paf.effective_start_date and paf.effective_end_date
495          and sysdate between ppp.change_date(+) and ppp.date_to(+)
496          and sysdate between ivf.effective_start_date and ivf.effective_end_date
497          and sysdate between petf.effective_start_date and petf.effective_end_date
498         AND sysdate between gr.effective_start_date and gr.effective_end_date;
499 
500 l_gr_currency varchar2(20);
501 l_gr_min      varchar2(20);
502 l_gr_mid      varchar2(20);
503 l_gr_max      varchar2(20);
504 l_currency    varchar2(20);
505 l_gr_factor   number;
506 l_salary      number;
507 l_pay_factor  number;
508 l_comp_ratio  number;
509 
510 BEGIN
511 
512 open c_grade_details;
513 fetch c_grade_details into l_gr_currency, l_gr_min, l_gr_mid, l_gr_max, l_currency,
514                         l_gr_factor, l_salary, l_pay_factor;
515 close c_grade_details;
516 
517 if (l_pay_factor is null OR l_pay_factor = 0) then
518     l_pay_factor := 1;
519 end if;
520 
521 if (p_mode = 'MIN' and l_gr_min > 0) then
522     l_gr_min := convertAmount(nvl(l_gr_currency,l_currency),l_currency,
523         l_gr_factor * l_gr_min,sysdate);
524     return l_gr_min;
525 end if;
526 
527 if (p_mode = 'MID' and l_gr_mid > 0) then
528     l_gr_mid := convertAmount(nvl(l_gr_currency,l_currency),l_currency,
529         l_gr_factor * l_gr_mid,sysdate);
530     return l_gr_mid;
531 end if;
532 
533 if (p_mode = 'MAX' and l_gr_max > 0) then
534     l_gr_max := convertAmount(nvl(l_gr_currency,l_currency),l_currency,
535         l_gr_factor * l_gr_max,sysdate);
536     return l_gr_max;
537 end if;
538 
539 if (p_mode = 'COMPARATIO') then
540     l_comp_ratio := getCompRatio(nvl(l_gr_currency,l_currency),l_currency,
541         l_salary * l_pay_factor, l_gr_factor * l_gr_mid, sysdate);
542     return l_comp_ratio;
543 end if;
544 
545 return null;
546 
547 exception
548     when others then
549         return 0;
550 END get_grade_details;
551 
552 function get_step_details(
553     p_step_id in number,
554     p_eff_date in date,
555     p_mode in varchar2
556     ) return varchar2 is
557 
558 cursor c_step_details is
559 select pgr.value , psp.spinal_point
560 from pay_grade_rules_f pgr, per_spinal_points psp, per_spinal_point_steps_f psps
561 where psps.step_id = p_step_id and psps.spinal_point_id=psp.spinal_point_id
562 and pgr.grade_or_spinal_point_id=psps.spinal_point_id  and pgr.rate_type='SP'
563 and p_eff_date between pgr.effective_start_date and pgr.effective_end_date
564 and p_eff_date between  psps.effective_start_date and psps.effective_end_date;
565 
566 l_step_value    varchar2(20);
567 l_point         varchar2(20);
568 
569 begin
570 
571 open c_step_details;
572 fetch c_step_details into l_step_value, l_point;
573 close c_step_details;
574 
575 if (p_mode = 'STEP_VALUE') then
576     return l_step_value;
577 end if;
578 if (p_mode = 'POINT') then
579     return l_point;
580 end if;
581 return null;
582 
583 exception
584     when others then
585         return 0;
586 end get_step_details;
587 
588 function get_step_num(
589     p_step_id in number,
590     p_eff_date in date
591     ) return number is
592 
593 cursor c_step_num is
594 select (nvl(gs.starting_step,1) + count(*))-1 step
595 from per_spinal_point_steps_f psps, per_spinal_point_steps_f psps2, per_grade_spines_f gs
596 where psps.step_id = p_step_id
597 and p_eff_date between psps.effective_start_date and psps.effective_end_date
598 and p_eff_date between psps2.effective_start_date and psps2.effective_end_date
599 and p_eff_date between gs.effective_start_date and gs.effective_end_date
600 and    psps.grade_spine_id = psps2.grade_spine_id
601 and    psps.grade_spine_id = gs.grade_spine_id
602 and    psps.sequence >= psps2.sequence
603 group by gs.starting_step;
604 
605 l_step    number;
606 
607 begin
608 
609 open c_step_num;
610 fetch c_step_num into l_step;
611 close c_step_num;
612 
613 if (l_step = 0) then
614     return null;
615 end if;
616 return l_step;
617 
618 exception
619     when others then
620         return 0;
621 end get_step_num;
622 
623 /*
624     Note: This function actually converts into the preferred currency and in case one is not set, it uses the login person bg currency
625 */
626 FUNCTION amtInLoginPrsnCurrency(
627     p_from_currency IN VARCHAR2
628    ,p_amount IN NUMBER
629    ,p_eff_date IN DATE
630 ) RETURN NUMBER IS
631 BEGIN
632     return convertAmount(
633             p_from_currency
634            ,getLoginPrsnCurrencyCode
635            ,p_amount
636            ,p_eff_date);
637 END amtInLoginPrsnCurrency;
638 
639 FUNCTION getReqClasses(p_person_id IN NUMBER) RETURN NUMBER
640 IS
641  l_cnt NUMBER:=0;
642  l_cursor cur_typ;
643 BEGIN
644     openReqClassesCsr(
645         l_cursor
646        ,0
647        ,p_person_id
648        ,getEffDate);
649     FETCH l_cursor INTO l_cnt;
650     CLOSE l_cursor;
651     RETURN (l_cnt);
652     Exception When Others then
653         return 0;
654 END getReqClasses;
655 
656 FUNCTION getReqClassesYTD(p_person_id IN NUMBER) RETURN NUMBER
657 IS
658  l_cnt NUMBER:=0;
659  l_cursor cur_typ;
660 BEGIN
661     openReqClassesCsr(
662         l_cursor
663        ,1
664        ,p_person_id
665        ,getEffDate);
666     FETCH l_cursor INTO l_cnt;
667     CLOSE l_cursor;
668     RETURN (l_cnt);
669     Exception When Others then
670         return 0;
671 END getReqClassesYTD;
672 
673 FUNCTION getReqClassesCompleted(p_person_id IN NUMBER) RETURN NUMBER
674 IS
675  l_cnt NUMBER:=0;
676  l_cursor cur_typ;
677 BEGIN
678     openReqClassesCsr(
679         l_cursor
680        ,2
681        ,p_person_id
682        ,getEffDate);
683     FETCH l_cursor INTO l_cnt;
684     CLOSE l_cursor;
685     RETURN (l_cnt);
686     Exception When Others then
687         return 0;
688 END getReqClassesCompleted;
689 
690 FUNCTION getReqClassesCompletedYTD(p_person_id IN NUMBER) RETURN NUMBER
691 IS
692  l_cnt NUMBER:=0;
693  l_cursor cur_typ;
694 BEGIN
695     openReqClassesCsr(
696         l_cursor
697        ,3
698        ,p_person_id
699        ,getEffDate);
700     FETCH l_cursor INTO l_cnt;
701     CLOSE l_cursor;
702     RETURN (l_cnt);
703     Exception When Others then
704         return 0;
705 END getReqClassesCompletedYTD;
706 
707 FUNCTION getReqClassesEnrolled(p_person_id IN NUMBER) RETURN NUMBER
708 IS
709  l_cnt NUMBER:=0;
710  l_cursor cur_typ;
711 BEGIN
712     openReqClassesCsr(
713         l_cursor
714        ,3
715        ,p_person_id
716        ,getEffDate);
717     FETCH l_cursor INTO l_cnt;
718     CLOSE l_cursor;
719     RETURN (l_cnt);
720     Exception When Others then
721         return 0;
722 END getReqClassesEnrolled;
723 
724 FUNCTION getLoginPrsnBusGrpId RETURN NUMBER IS
725 BEGIN
726  return hr_util_misc_ss.g_loginPrsnBGId;
727 END;
728 
729 FUNCTION getEffDate
730 RETURN DATE
731 IS
732 BEGIN
733  RETURN nvl(hr_util_misc_ss.g_eff_date,sysdate);
734 END getEffDate;
735 
736 FUNCTION getAsgGradeRule(p_pay_proposal_id IN NUMBER) RETURN ROWID IS
737 l_rowid ROWID:=NULL;
738 CURSOR c_graderule IS
739   SELECT gr.rowid
740   FROM  per_pay_proposals ppp, per_assignments_f paf
741        ,per_pay_bases pb, pay_grade_rules_f gr
742   WHERE ppp.pay_proposal_id = p_pay_proposal_id
743   AND ppp.assignment_id = paf.assignment_id
744   AND ppp.change_date between paf.effective_start_date and paf.effective_end_date
745   AND paf.pay_basis_id = pb.pay_basis_id
746   AND pb.rate_id = gr.rate_id
747   AND paf.grade_id = gr.grade_or_spinal_point_id
748   AND ppp.change_date between gr.effective_start_date and gr.effective_end_date;
749 BEGIN
750   OPEN c_graderule;
751       FETCH c_graderule INTO l_rowid;
752   CLOSE c_graderule;
753   RETURN l_rowid;
754   Exception When Others then
755     RETURN NULL;
756 END getAsgGradeRule;
757 
758 FUNCTION getAsgProposalId(p_assignment_id IN NUMBER) RETURN NUMBER IS
759 l_proposal_id Number := -1;
760 CURSOR c_proposal IS
761   SELECT nvl(max(pay_proposal_id),-1)
762   FROM  per_pay_proposals ppp, fnd_sessions fs
763   WHERE fs.session_id = userenv('sessionid')
764   AND ppp.assignment_id = p_assignment_id
765   AND ppp.approved = 'Y'
766   AND fs.effective_date between ppp.change_date and ppp.date_to;
767 BEGIN
768   OPEN c_proposal;
769       FETCH c_proposal INTO l_proposal_id;
770   CLOSE c_proposal;
771   Return l_proposal_id;
772   Exception When Others then
773     Return -1;
774 END getAsgProposalId;
775 
776 FUNCTION getPrsnApplicationId(p_person_id IN NUMBER) RETURN NUMBER IS
777 l_application_id Number := -1;
778 CURSOR c_applications IS
779   SELECT nvl(max(application_id),-1)
780   FROM  per_applications, fnd_sessions fs
781   WHERE fs.session_id = userenv('sessionid')
782   AND person_id = p_person_id
783   AND fs.effective_date between date_received and nvl(date_end,fs.effective_date);
784 
785 BEGIN
786   OPEN c_applications;
787       FETCH c_applications INTO l_application_id;
788   CLOSE c_applications;
789   Return l_application_id;
790   Exception When Others then
791     Return -1;
792 END getPrsnApplicationId;
793 
794 FUNCTION getPrsnPerformanceId(p_person_id IN NUMBER) RETURN NUMBER IS
795 l_perf_id Number := -1;
796 
797 CURSOR c_performance_reviews IS
798  SELECT nvl(max(performance_review_id),-1)
799   FROM per_performance_reviews pr
800   WHERE pr.person_id = p_person_id
801   AND pr.review_date = (SELECT max(review_date)
802                         FROM per_performance_reviews ipr, fnd_sessions fs
803                         WHERE fs.session_id = userenv('sessionid')
804                         AND ipr.person_id = pr.person_id
805                         AND ipr.review_date <= fs.effective_date);
806 
807 BEGIN
808   OPEN c_performance_reviews;
809       FETCH c_performance_reviews INTO l_perf_id;
810   CLOSE c_performance_reviews;
811   Return l_perf_id;
812   Exception When Others then
813     Return -1;
814 END getPrsnPerformanceId;
815 
816   /*
817   ||===========================================================================
818   || FUNCTION: get_display_job_name
819   ||---------------------------------------------------------------------------
820   ||
821   || Description:
822   ||     Calls get_job_info and returns the job name
823   ||
824   || Access Status:
825   ||     Public
826   ||
827   ||===========================================================================
828   */
829 
830 FUNCTION get_display_job_name(p_job_id IN per_assignments_f.job_id%TYPE)
831 RETURN VARCHAR2
832 IS
833 
834   l_name            varchar2(100) ;
835   l_org_name        varchar2(50) ;
836   l_location_code   varchar2(50) ;
837 
838 BEGIN
839 
840   hr_suit_match_utility_web.get_job_info
841     (p_search_type      => hr_suit_match_utility_web.g_job_type
842     ,p_id               => to_char(p_job_id)
843     ,p_name             => l_name
844     ,p_org_name         => l_org_name
845     ,p_location_code    => l_location_code    ) ;
846 
847   return l_name ;
848 
849 END get_display_job_name;
850 
851   /*
852   ||===========================================================================
853   || FUNCTION: get_total_absences
854   ||---------------------------------------------------------------------------
855   ||
856   || Description:
857   ||     Returns the total absences for a given person_id.
858   ||
859   || Access Status:
860   ||     Public
861   ||
862   ||===========================================================================
863   */
864 
865 FUNCTION get_total_absences(p_person_id IN NUMBER)
866 RETURN NUMBER
867 IS
868 
869   ln_result NUMBER;
870 
871  CURSOR lc_get_absences (p_person_id IN per_people_f.person_id%TYPE)
872   IS
873   SELECT count(att.absence_attendance_id) total_number_of_absences
874   FROM per_absence_attendances att
875   WHERE  att.person_id = p_person_id;
876 
877 BEGIN
878 
879   OPEN lc_get_absences(p_person_id => p_person_id);
880   FETCH lc_get_absences
881   INTO ln_result;
882 
883   IF lc_get_absences%NOTFOUND OR lc_get_absences%NOTFOUND IS NULL
884   THEN
885     ln_result := 0;
886   END IF;
887   CLOSE lc_get_absences;
888   RETURN (ln_result);
889 
890 END get_total_absences;
891 
892   /*
893   ||===========================================================================
894   || FUNCTION: get_total_absence_days
895   ||---------------------------------------------------------------------------
896   ||
897   || Description:
898   ||     Returns the total absence days for a given person_id.
899   ||
900   || Access Status:
901   ||     Public
902   ||
903   ||===========================================================================
904   */
905 
906 FUNCTION get_total_absence_days(p_person_id IN NUMBER)
907 RETURN NUMBER
908 IS
909 
910   ln_result NUMBER;
911 
912   CURSOR lc_get_absences (p_person_id IN per_people_f.person_id%TYPE)
913   IS
914   SELECT sum(NVL(att.absence_days,0)) total_absence_days
915   FROM per_absence_attendances att
916   WHERE att.person_id = p_person_id;
917 
918 BEGIN
919   OPEN lc_get_absences(p_person_id => p_person_id);
920   FETCH lc_get_absences
921   INTO ln_result;
922 
923   IF lc_get_absences%NOTFOUND OR lc_get_absences%NOTFOUND IS NULL
924   THEN
925     ln_result := 0;
926   END IF;
927   CLOSE lc_get_absences;
928   RETURN NVL(ln_result,0);
929 
930 END get_total_absence_days;
931 
932   /*
933   ||===========================================================================
934   || FUNCTION: get_total_absence_days
935   ||---------------------------------------------------------------------------
936   ||
937   || Description:
938   ||     Returns the total absence days for a given person_id.
939   ||
940   || Access Status:
941   ||     Public
942   ||
943   ||===========================================================================
944   */
945 
946 FUNCTION get_total_absence_hours(p_person_id IN NUMBER)
947 RETURN NUMBER
948 IS
949 
950   ln_result NUMBER;
951 
952   CURSOR lc_get_absences (p_person_id IN per_people_f.person_id%TYPE)
953   IS
954   SELECT sum(NVL(att.absence_hours,0)) total_absence_hours
955   FROM per_absence_attendances att
956   WHERE  att.person_id = p_person_id;
957 
958 BEGIN
959   OPEN lc_get_absences(p_person_id => p_person_id);
960   FETCH lc_get_absences
961   INTO ln_result;
962 
963   IF lc_get_absences%NOTFOUND OR lc_get_absences%NOTFOUND IS NULL
964   THEN
965     ln_result := 0;
966   END IF;
967   CLOSE lc_get_absences;
968   RETURN NVL(ln_result,0);
969 
970 END get_total_absence_hours;
971 
972 FUNCTION getYOSDenominator(p_person_id IN NUMBER) RETURN NUMBER IS
973 l_yos NUMBER:=0;
974 BEGIN
975     l_yos := getYOS(p_person_id);
976     IF (l_yos > 0) THEN
977         return l_yos;
978     END IF;
979     RETURN 1;
980 END getYOSDenominator;
981 
982 FUNCTION getYOS(
983     p_person_id IN NUMBER
984    ,p_eff_date IN DATE Default getEffDate)
985 RETURN NUMBER
986 IS
987   ln_result NUMBER:=0;
988 
989   CURSOR c_yos (p_person_id IN per_people_f.person_id%TYPE)
990   IS
991   SELECT ROUND(SUM(MONTHS_BETWEEN(
992 		decode(sign(p_eff_date-nvl(actual_termination_date, p_eff_date)),
993                            -1, trunc(p_eff_date), nvl(actual_termination_date, trunc(p_eff_date))),
994                 trunc(ser.date_start))/12), 2) yos
995   FROM per_periods_of_service ser
996   WHERE ser.person_id = p_person_id
997   AND ser.date_start <= p_eff_date;
998 
999 BEGIN
1000   OPEN c_yos(p_person_id => p_person_id);
1001   FETCH c_yos INTO ln_result;
1002   CLOSE c_yos;
1003 
1004   IF ln_result < 1/365
1005   THEN ln_result := ROUND(1/365,2);
1006   END IF;
1007 
1008   RETURN ln_result;
1009   Exception When Others then
1010     return 0;
1011 END getYOS;
1012 
1013   /*
1014   ||===========================================================================
1015   || FUNCTION: get_years_of_service
1016   ||---------------------------------------------------------------------------
1017   ||
1018   || Description:
1019   ||     Returns the total years of service for a given person_id.
1020   ||
1021   || Access Status:
1022   ||     Public
1023   ||
1024   ||===========================================================================
1025   */
1026 
1027 FUNCTION get_years_of_service(p_person_id IN NUMBER)
1028 RETURN NUMBER
1029 IS
1030 BEGIN
1031   return getYOS(p_person_id, sysdate);
1032 END get_years_of_service;
1033 
1034 /*Enhancement for bug 5259269*/
1035 /*
1036   ||===========================================================================
1037   || FUNCTION: getAYOS
1038   ||---------------------------------------------------------------------------
1039   ||
1040   || Description:
1041   ||     Returns the Adjusted Years of Service based on Adjusted Service Date.
1042   ||
1043   || Access Status:
1044   ||     Public
1045   ||
1046   ||===========================================================================
1047   */
1048 
1049 FUNCTION getAYOS(
1050     p_person_id IN NUMBER
1051    ,p_eff_date IN DATE Default getEffDate)
1052 RETURN NUMBER
1053 IS
1054   ln_result NUMBER;
1055 
1056   CURSOR c_ayos (p_person_id IN per_people_f.person_id%TYPE)
1057   IS
1058   SELECT ROUND(MONTHS_BETWEEN(
1059 		decode(sign(p_eff_date-nvl(actual_termination_date, p_eff_date)),
1060                            -1, trunc(p_eff_date), nvl(actual_termination_date, trunc(p_eff_date))),
1061                 trunc(ser.adjusted_svc_date))/12, 2) ayos
1062   FROM per_periods_of_service ser
1063   WHERE ser.person_id = p_person_id
1064   AND p_eff_date between ser.date_start and nvl(ser.actual_termination_date, p_eff_date);
1065 
1066 BEGIN
1067   OPEN c_ayos(p_person_id => p_person_id);
1068   FETCH c_ayos INTO ln_result;
1069   CLOSE c_ayos;
1070 
1071   IF ln_result < 1/365
1072   THEN ln_result := ROUND(1/365,2);
1073   END IF;
1074 
1075   RETURN ln_result;
1076   Exception When Others then
1077     return 0;
1078 END getAYOS;
1079 
1080 
1081   /*
1082   ||===========================================================================
1083   || FUNCTION: get_last_application_date
1084   ||---------------------------------------------------------------------------
1085   ||
1086   || Description:
1087   ||     Returns the last application date for a given person_id.
1088   ||
1089   || Access Status:
1090   ||     Public
1091   ||
1092   ||===========================================================================
1093   */
1094 
1095 FUNCTION get_last_application_date(p_person_id IN NUMBER)
1096 RETURN DATE
1097 IS
1098 
1099   ln_result DATE;
1100 
1101   CURSOR lc_get_lad (p_person_id IN per_people_f.person_id%TYPE)
1102   IS
1103     SELECT MAX(pa.date_received)
1104     FROM per_applications pa,
1105          per_assignments_f ass
1106     WHERE ass.application_id = pa.application_id
1107     AND   ass.assignment_type = 'A'
1108     AND   ass.person_id = p_person_id;
1109 
1110 BEGIN
1111   OPEN lc_get_lad(p_person_id => p_person_id);
1112   FETCH lc_get_lad
1113   INTO ln_result;
1114 
1115   IF lc_get_lad%NOTFOUND OR lc_get_lad%NOTFOUND IS NULL
1116   THEN
1117     ln_result := '';
1118   END IF;
1119   CLOSE lc_get_lad;
1120   RETURN (ln_result);
1121 
1122 END get_last_application_date;
1123 
1124 FUNCTION getClassesTaken(
1125     p_person_id IN NUMBER
1126    ,p_eff_date IN DATE Default getEffDate
1127    )
1128 RETURN NUMBER
1129 IS
1130   ln_result NUMBER:= 0;
1131 
1132  CURSOR c_classes (p_person_id IN per_people_f.person_id%TYPE)
1133   IS
1134   SELECT count(db.booking_id)
1135   FROM   ota_booking_status_types bst,
1136          ota_events evt, ota_delegate_bookings db
1137   WHERE  db.booking_status_type_id = bst.booking_status_type_id
1138   AND db.event_id = evt.event_id
1139   AND evt.course_start_date <= p_eff_date
1140   AND bst.type = 'A'
1141   AND db.delegate_person_id = p_person_id;
1142 
1143 BEGIN
1144   OPEN c_classes(p_person_id => p_person_id);
1145   FETCH c_classes INTO ln_result;
1146   CLOSE c_classes;
1147   RETURN (ln_result);
1148   Exception When Others then
1149     return 0;
1150 END getClassesTaken;
1151 
1152 FUNCTION getFutureClasses(
1153     p_person_id IN NUMBER
1154    ,p_eff_date IN DATE Default getEffDate
1155    )
1156 RETURN NUMBER
1157 IS
1158   ln_result NUMBER:= 0;
1159 
1160  CURSOR c_classes (p_person_id IN NUMBER) IS
1161   SELECT count(tdb.booking_id)
1162   FROM   ota_booking_status_types bst,
1163          ota_events evt, ota_delegate_bookings tdb
1164   WHERE  tdb.booking_status_type_id = bst.booking_status_type_id
1165   AND tdb.event_id = evt.event_id
1166   AND evt.course_start_date > p_eff_date
1167   AND bst.type NOT IN ('C')
1168   AND tdb.delegate_person_id = p_person_id;
1169 
1170 BEGIN
1171   OPEN c_classes(p_person_id => p_person_id);
1172   FETCH c_classes INTO ln_result;
1173   CLOSE c_classes;
1174   RETURN (ln_result);
1175   Exception When Others then
1176     return 0;
1177 END getFutureClasses;
1178 
1179 FUNCTION get_past_classes(p_person_id IN NUMBER)
1180 RETURN NUMBER
1181 IS
1182 BEGIN
1183   return getClassesTaken(p_person_id, trunc(sysdate));
1184 END get_past_classes;
1185 
1186 
1187 FUNCTION get_future_classes(p_person_id IN NUMBER)
1188 RETURN NUMBER IS
1189 BEGIN
1190     return getFutureClasses(p_person_id, trunc(sysdate));
1191 END get_future_classes;
1192 
1193   /*
1194   ||===========================================================================
1195   || FUNCTION: get_other_classes
1196   ||---------------------------------------------------------------------------
1197   ||
1198   || Description:
1199   ||     Returns the total other classes for a given person_id.
1200   ||
1201   || Access Status:
1202   ||     Public
1203   ||
1204   ||===========================================================================
1205   */
1206 
1207 FUNCTION get_other_classes(p_person_id IN NUMBER)
1208 RETURN NUMBER
1209 IS
1210 
1211   ln_result NUMBER;
1212 
1213  CURSOR lc_get_other_classes (p_person_id IN per_people_f.person_id%TYPE)
1214   IS
1215   SELECT count(tdb.booking_id)
1216   FROM   ota_booking_status_types bst,
1217          ota_delegate_bookings tdb
1218   WHERE  tdb.booking_status_type_id = bst.booking_status_type_id
1219   AND    bst.type IN ('R','C')
1220   AND    tdb.delegate_person_id = p_person_id;
1221 
1222 BEGIN
1223 
1224   OPEN lc_get_other_classes(p_person_id => p_person_id);
1225   FETCH lc_get_other_classes
1226   INTO ln_result;
1227 
1228   IF lc_get_other_classes%NOTFOUND OR lc_get_other_classes%NOTFOUND IS NULL
1229   THEN
1230     ln_result := 0;
1231   END IF;
1232   CLOSE lc_get_other_classes;
1233   RETURN (ln_result);
1234 
1235 END get_other_classes;
1236 
1237   /*
1238   ||===========================================================================
1239   || FUNCTION: get_currency
1240   ||---------------------------------------------------------------------------
1241   ||
1242   || Description:
1243   ||     Returns the currency for a given assignment id at a required date.
1244   ||
1245   || Access Status:
1246   ||     Public
1247   ||
1248   ||===========================================================================
1249   */
1250 
1251 FUNCTION get_currency(p_assignment_id IN per_assignments_f.assignment_id%TYPE
1252                      ,p_change_date   IN DATE)
1253 RETURN pay_element_types_f.input_currency_code%TYPE
1254 IS
1255 
1256   lv_result pay_element_types_f.input_currency_code%TYPE;
1257 
1258   CURSOR lc_get_currency(p_assign_id   IN per_assignments_f.assignment_id%TYPE
1259                         ,p_change_date IN DATE)
1260   IS
1261   SELECT pet.input_currency_code
1262   FROM   pay_element_types_f   pet
1263   ,      pay_input_values_f    piv
1264   ,      per_pay_bases         ppb
1265   ,      per_assignments_f     paf
1266   WHERE paf.assignment_id   =       p_assign_id
1267   AND   p_change_date       BETWEEN paf.effective_start_date
1268                             AND     paf.effective_end_date
1269   AND   ppb.pay_basis_id    =       paf.pay_basis_id
1270   AND   ppb.input_value_id  =       piv.input_value_id
1271   AND   p_change_date       BETWEEN piv.effective_start_date
1272                             AND     piv.effective_end_date
1273   AND   piv.element_type_id =       pet.element_type_id
1274   AND   p_change_date       BETWEEN pet.effective_start_date
1275                             AND     pet.effective_end_date;
1276 
1277 BEGIN
1278 
1279   OPEN  lc_get_currency(p_assign_id   => p_assignment_id
1280                        ,p_change_date => p_change_date);
1281   FETCH lc_get_currency
1282   INTO  lv_result;
1283 
1284   IF lc_get_currency%NOTFOUND OR lc_get_currency%NOTFOUND IS NULL
1285   THEN
1286     lv_result := ' ';
1287   END IF;
1288   CLOSE lc_get_currency;
1289 
1290   RETURN lv_result;
1291 
1292 END get_currency;
1293 
1294   /*
1295   ||===========================================================================
1296   || FUNCTION: get_annual_salary
1297   ||---------------------------------------------------------------------------
1298   ||
1299   || Description:
1300   ||     Returns the annual salary for a given assignment id at a
1301   ||     required date.
1302   ||
1303   || Access Status:
1304   ||     Public
1305   ||
1306   ||===========================================================================
1307   */
1308 
1309 FUNCTION get_annual_salary(
1310            p_assignment_id IN per_assignments_f.assignment_id%TYPE,
1311            p_change_date   IN DATE
1312          )
1313 RETURN VARCHAR2
1314 IS
1315 
1316   ln_result          NUMBER;
1317   ln_annual_factor   NUMBER;
1318   ln_proposed_salary NUMBER;
1319   lv_format_string   VARCHAR2(30);
1320 
1321   CURSOR lc_get_salary(p_assign_id   IN per_assignments_f.assignment_id%TYPE
1322                       ,p_change_date IN DATE)
1323   IS
1324   SELECT ppb.pay_annualization_factor
1325   ,      ppp.proposed_salary_n
1326   FROM   per_pay_bases      ppb
1327   ,      per_assignments_f  paf
1328   ,      per_pay_proposals  ppp
1329   WHERE paf.assignment_id = p_assign_id
1330   AND   p_change_date  BETWEEN paf.effective_start_date
1331                        AND     NVL(paf.effective_end_date, p_change_date)
1332   AND   ppp.change_date   = p_change_date
1333   AND   ppp.assignment_id = paf.assignment_id
1334   AND   ppb.pay_basis_id  = paf.pay_basis_id;
1335 
1336 BEGIN
1337 
1338   OPEN  lc_get_salary(p_assign_id   => p_assignment_id
1339                      ,p_change_date => p_change_date);
1340   FETCH lc_get_salary
1341   INTO  ln_annual_factor, ln_proposed_salary;
1342 
1343   IF lc_get_salary%NOTFOUND OR lc_get_salary%NOTFOUND IS NULL
1344   THEN
1345     ln_result := 0;
1346   END IF;
1347   CLOSE lc_get_salary;
1348 
1349   IF (ln_annual_factor IS NULL OR ln_annual_factor = 0 ) THEN
1350     ln_annual_factor := 1;
1351   END IF;
1352   ln_result := ln_annual_factor * ln_proposed_salary;
1353 
1354   RETURN NVL(
1355             TO_CHAR(
1356                ln_result,
1357                get_currency_format(
1358                  p_curcode        => get_currency(
1359                                        p_assignment_id => p_assignment_id,
1360                                        p_change_date   => p_change_date
1361                                      ),
1362                  p_effective_date => p_change_date
1363                )
1364              )
1365            ,''
1366          );
1367 
1368 END get_annual_salary;
1369 
1370   /*
1371   ||===========================================================================
1372   || FUNCTION: get_job
1373   ||---------------------------------------------------------------------------
1374   ||
1375   || Description:
1376   ||     If the HR Views responsibilty profiles HR_JOB_KEYFLEX_SEGMENT1 and
1377   ||     HR_JOB_KEYFLEX_SEGMENT2 are set and enabled then these values will
1378   ||     be returned.  Otherwise the per_jobs.name value will be returned.
1379   ||
1380   || Access Status:
1381   ||     Public
1382   ||
1383   ||===========================================================================
1384   */
1385 
1386 FUNCTION get_job(p_job_id IN per_assignments_f.job_id%TYPE)
1387 RETURN VARCHAR2
1388 IS
1389 
1390   -- Job Keyflex Id is stored in org_information6 in
1391   -- hr_organization_information
1392   CURSOR lc_get_job_flex_id(p_business_group_id IN  NUMBER)
1393   IS
1394   SELECT org_information6
1395     FROM hr_organization_information
1396    WHERE organization_id = p_business_group_id
1397      AND org_information_context = 'Business Group Information';
1398 
1399   CURSOR lc_get_job_details(p_job_id IN per_assignments_f.job_id%TYPE)
1400   IS
1401   SELECT pj.name,
1402          pjd.segment1,
1403          pjd.segment2,
1404          pjd.segment3,
1405          pjd.segment4,
1406          pjd.segment5,
1407          pjd.segment6,
1408          pjd.segment7,
1409          pjd.segment8,
1410          pjd.segment9,
1411          pjd.segment10,
1412          pjd.segment11,
1413          pjd.segment12,
1414          pjd.segment13,
1415          pjd.segment14,
1416          pjd.segment15,
1417          pjd.segment16,
1418          pjd.segment17,
1419          pjd.segment18,
1420          pjd.segment19,
1421          pjd.segment20,
1422          pjd.segment21,
1423          pjd.segment22,
1424          pjd.segment23,
1425          pjd.segment24,
1426          pjd.segment25,
1427          pjd.segment26,
1428          pjd.segment27,
1429          pjd.segment28,
1430          pjd.segment29,
1431          pjd.segment30
1432     FROM per_jobs_vl pj,
1433          per_job_definitions pjd
1434    WHERE pj.job_definition_id = pjd.job_definition_id
1435      AND pj.job_id = p_job_id;
1436 
1437   lv_segment_name1      VARCHAR2(30) DEFAULT NULL;
1438   lv_segment_name2      VARCHAR2(30) DEFAULT NULL;
1439   ln_flex_num           NUMBER;
1440   lv_flex_code          VARCHAR2(3) := 'JOB';
1441   lv_result             VARCHAR2(240) DEFAULT NULL;
1442   ltt_segment           hr_mee_views_gen.segmentsTable;
1443   ln_business_group_id  per_people_f.business_group_id%TYPE;
1444 
1445 BEGIN
1446 
1447   --First Get the name from per_jobs and all the segments
1448   --from per_job_definitions.
1449   --The name will be returned if no profiles are used.
1450   FOR segment_rec IN lc_get_job_details(p_job_id => p_job_id)
1451   LOOP
1452     ltt_segment(0).value  := segment_rec.name;
1453     ltt_segment(1).value  := segment_rec.segment1;
1454     ltt_segment(2).value  := segment_rec.segment2;
1455     ltt_segment(3).value  := segment_rec.segment3;
1456     ltt_segment(4).value  := segment_rec.segment4;
1457     ltt_segment(5).value  := segment_rec.segment5;
1458     ltt_segment(6).value  := segment_rec.segment6;
1459     ltt_segment(7).value  := segment_rec.segment7;
1460     ltt_segment(8).value  := segment_rec.segment8;
1461     ltt_segment(9).value  := segment_rec.segment9;
1462     ltt_segment(10).value := segment_rec.segment10;
1463     ltt_segment(11).value := segment_rec.segment11;
1464     ltt_segment(12).value := segment_rec.segment12;
1465     ltt_segment(13).value := segment_rec.segment13;
1466     ltt_segment(14).value := segment_rec.segment14;
1467     ltt_segment(15).value := segment_rec.segment15;
1468     ltt_segment(16).value := segment_rec.segment16;
1469     ltt_segment(17).value := segment_rec.segment17;
1470     ltt_segment(18).value := segment_rec.segment18;
1471     ltt_segment(19).value := segment_rec.segment19;
1472     ltt_segment(20).value := segment_rec.segment20;
1473     ltt_segment(21).value := segment_rec.segment21;
1474     ltt_segment(22).value := segment_rec.segment22;
1475     ltt_segment(23).value := segment_rec.segment23;
1476     ltt_segment(24).value := segment_rec.segment24;
1477     ltt_segment(25).value := segment_rec.segment25;
1478     ltt_segment(26).value := segment_rec.segment26;
1479     ltt_segment(27).value := segment_rec.segment27;
1480     ltt_segment(28).value := segment_rec.segment28;
1481     ltt_segment(29).value := segment_rec.segment29;
1482     ltt_segment(30).value := segment_rec.segment30;
1483   END LOOP;
1484 
1485   lv_segment_name1 := fnd_profile.value('HR_JOB_KEYFLEX_SEGMENT1');
1486   lv_segment_name2 := fnd_profile.value('HR_JOB_KEYFLEX_SEGMENT2');
1487 
1488   IF lv_segment_name1 IS NULL
1489   THEN
1490     RETURN ltt_segment(0).value;--job_name
1491   END IF;
1492 
1493   ln_business_group_id := fnd_profile.value('PER_BUSINESS_GROUP_ID');
1494 
1495   OPEN lc_get_job_flex_id(p_business_group_id => ln_business_group_id);
1496   FETCH lc_get_job_flex_id INTO ln_flex_num;
1497   IF lc_get_job_flex_id%NOTFOUND
1498   THEN
1499     RETURN ltt_segment(0).value;--job_name
1500   END IF;
1501   CLOSE lc_get_job_flex_id;
1502 
1503   hr_mee_views_gen.get_segment_value(
1504                      p_flex_code       =>  lv_flex_code
1505                     ,p_flex_num        =>  ln_flex_num
1506                     ,p_segment_name1   =>  lv_segment_name1
1507                     ,p_segment_name2   =>  lv_segment_name2
1508                     ,p_segment         =>  ltt_segment
1509                     ,p_result          =>  lv_result);
1510 
1511   IF lv_result IS NULL
1512   THEN
1513     RETURN ltt_segment(0).value;--job_name
1514   ELSE
1515     RETURN lv_result;
1516   END IF;
1517 
1518 END get_job;
1519 
1520   /*
1521   ||===========================================================================
1522   || FUNCTION: get_grade
1523   ||---------------------------------------------------------------------------
1524   ||
1525   || Description:
1526   ||     If the HR Views responsibilty profiles HR_GRADE_KEYFLEX_SEGMENT1 and
1527   ||     HR_GRADE_KEYFLEX_SEGMENT2 are set and enabled then these values will
1528   ||     be returned.  Otherwise the per_grades.name value will be returned.
1529   ||
1530   || Access Status:
1531   ||     Public
1532   ||
1533   ||===========================================================================
1534   */
1535 
1536 FUNCTION get_grade(p_grade_id IN per_assignments_f.grade_id%TYPE)
1537 RETURN VARCHAR2
1538 IS
1539 
1540   -- Grade Keyflex Id is stored in org_information4 in
1541   -- hr_organization_information
1542   CURSOR lc_get_grade_flex_id(p_business_group_id IN  NUMBER)
1543   IS
1544   SELECT org_information4
1545     FROM hr_organization_information
1546    WHERE organization_id = p_business_group_id
1547      AND org_information_context = 'Business Group Information';
1548 
1549   CURSOR lc_get_grade_details(p_grade_id IN per_assignments_f.grade_id%TYPE)
1550   IS
1551   SELECT pg.name,
1552          pgd.segment1,
1553          pgd.segment2,
1554          pgd.segment3,
1555          pgd.segment4,
1556          pgd.segment5,
1557          pgd.segment6,
1558          pgd.segment7,
1559          pgd.segment8,
1560          pgd.segment9,
1561          pgd.segment10,
1562          pgd.segment11,
1563          pgd.segment12,
1564          pgd.segment13,
1565          pgd.segment14,
1566          pgd.segment15,
1567          pgd.segment16,
1568          pgd.segment17,
1569          pgd.segment18,
1570          pgd.segment19,
1571          pgd.segment20,
1572          pgd.segment21,
1573          pgd.segment22,
1574          pgd.segment23,
1575          pgd.segment24,
1576          pgd.segment25,
1577          pgd.segment26,
1578          pgd.segment27,
1579          pgd.segment28,
1580          pgd.segment29,
1581          pgd.segment30
1582     FROM per_grades_vl pg,
1583          per_grade_definitions pgd
1584    WHERE pg.grade_definition_id = pgd.grade_definition_id
1585      AND pg.grade_id = p_grade_id;
1586 
1587   lv_segment_name1      VARCHAR2(30) DEFAULT NULL;
1588   lv_segment_name2      VARCHAR2(30) DEFAULT NULL;
1589   ln_flex_num           NUMBER;
1590   lv_flex_code          VARCHAR2(3) := 'GRD';
1591   lv_result             VARCHAR2(240) DEFAULT NULL;
1592   ltt_segment           hr_mee_views_gen.segmentsTable;
1593   ln_business_group_id  per_people_f.business_group_id%TYPE;
1594 
1595 BEGIN
1596 
1597   --First Get the name from per_grades and all the segments
1598   --from per_grade_definitions.
1599   --The name will be returned if no profiles are used.
1600   FOR segment_rec IN lc_get_grade_details(p_grade_id => p_grade_id)
1601   LOOP
1602     ltt_segment(0).value  := segment_rec.name;
1603     ltt_segment(1).value  := segment_rec.segment1;
1604     ltt_segment(2).value  := segment_rec.segment2;
1605     ltt_segment(3).value  := segment_rec.segment3;
1606     ltt_segment(4).value  := segment_rec.segment4;
1607     ltt_segment(5).value  := segment_rec.segment5;
1608     ltt_segment(6).value  := segment_rec.segment6;
1609     ltt_segment(7).value  := segment_rec.segment7;
1610     ltt_segment(8).value  := segment_rec.segment8;
1611     ltt_segment(9).value  := segment_rec.segment9;
1612     ltt_segment(10).value := segment_rec.segment10;
1613     ltt_segment(11).value := segment_rec.segment11;
1614     ltt_segment(12).value := segment_rec.segment12;
1615     ltt_segment(13).value := segment_rec.segment13;
1616     ltt_segment(14).value := segment_rec.segment14;
1617     ltt_segment(15).value := segment_rec.segment15;
1618     ltt_segment(16).value := segment_rec.segment16;
1619     ltt_segment(17).value := segment_rec.segment17;
1620     ltt_segment(18).value := segment_rec.segment18;
1621     ltt_segment(19).value := segment_rec.segment19;
1622     ltt_segment(20).value := segment_rec.segment20;
1623     ltt_segment(21).value := segment_rec.segment21;
1624     ltt_segment(22).value := segment_rec.segment22;
1625     ltt_segment(23).value := segment_rec.segment23;
1626     ltt_segment(24).value := segment_rec.segment24;
1627     ltt_segment(25).value := segment_rec.segment25;
1628     ltt_segment(26).value := segment_rec.segment26;
1629     ltt_segment(27).value := segment_rec.segment27;
1630     ltt_segment(28).value := segment_rec.segment28;
1631     ltt_segment(29).value := segment_rec.segment29;
1632     ltt_segment(30).value := segment_rec.segment30;
1633   END LOOP;
1634 
1635   lv_segment_name1 := fnd_profile.value('HR_GRADE_KEYFLEX_SEGMENT1');
1636   lv_segment_name2 := fnd_profile.value('HR_GRADE_KEYFLEX_SEGMENT2');
1637 
1638   IF lv_segment_name1 IS NULL
1639   THEN
1640     RETURN ltt_segment(0).value;--grade_name
1641   END IF;
1642 
1643   ln_business_group_id := fnd_profile.value('PER_BUSINESS_GROUP_ID');
1644 
1645   OPEN lc_get_grade_flex_id(p_business_group_id => ln_business_group_id);
1646   FETCH lc_get_grade_flex_id INTO ln_flex_num;
1647   IF lc_get_grade_flex_id%NOTFOUND
1648   THEN
1649     RETURN ltt_segment(0).value;--grade_name
1650   END IF;
1651   CLOSE lc_get_grade_flex_id;
1652 
1653   hr_mee_views_gen.get_segment_value(
1654                      p_flex_code       =>  lv_flex_code
1655                     ,p_flex_num        =>  ln_flex_num
1656                     ,p_segment_name1   =>  lv_segment_name1
1657                     ,p_segment_name2   =>  lv_segment_name2
1658                     ,p_segment         =>  ltt_segment
1659                     ,p_result          =>  lv_result);
1660 
1661   IF lv_result IS NULL
1662   THEN
1663     RETURN ltt_segment(0).value;--grade_name
1664   ELSE
1665     RETURN lv_result;
1666   END IF;
1667 
1668 END get_grade;
1669 
1670   /*
1671   ||===========================================================================
1672   || FUNCTION: get_position
1673   ||---------------------------------------------------------------------------
1674   ||
1675   || Description:
1676   ||     If the HR Views responsibilty profiles HR_POSITION_KEYFLEX_SEGMENT1
1677   ||     and HR_POSITION_KEYFLEX_SEGMENT2 are set and enabled then these
1678   ||     values will be returned.
1679   ||     Otherwise the per_positions.name value will be returned.
1680   ||
1681   || Access Status:
1682   ||     Public
1683   ||
1684   ||===========================================================================
1685   */
1686 
1687 FUNCTION get_position(p_position_id IN per_assignments_f.position_id%TYPE
1688 				 ,p_effective_date IN DATE DEFAULT TRUNC(SYSDATE))
1689 RETURN VARCHAR2
1690 IS
1691 
1692   -- Position Keyflex Id is stored in org_information8 in
1693   -- hr_organization_information
1694   CURSOR lc_get_position_flex_id(p_business_group_id IN  NUMBER)
1695   IS
1696   SELECT org_information8
1697     FROM hr_organization_information
1698    WHERE organization_id = p_business_group_id
1699      AND org_information_context = 'Business Group Information';
1700 
1701   CURSOR lc_get_position_details(
1702            p_position_id IN per_assignments_f.position_id%TYPE
1703          )
1704   IS
1705   SELECT pp.name,
1706          ppd.segment1,
1707          ppd.segment2,
1708          ppd.segment3,
1709          ppd.segment4,
1710          ppd.segment5,
1711          ppd.segment6,
1712          ppd.segment7,
1713          ppd.segment8,
1714          ppd.segment9,
1715          ppd.segment10,
1716          ppd.segment11,
1717          ppd.segment12,
1718          ppd.segment13,
1719          ppd.segment14,
1720          ppd.segment15,
1721          ppd.segment16,
1722          ppd.segment17,
1723          ppd.segment18,
1724          ppd.segment19,
1725          ppd.segment20,
1726          ppd.segment21,
1727          ppd.segment22,
1728          ppd.segment23,
1729          ppd.segment24,
1730          ppd.segment25,
1731          ppd.segment26,
1732          ppd.segment27,
1733          ppd.segment28,
1734          ppd.segment29,
1735          ppd.segment30
1736     FROM hr_all_positions_f_vl pp,
1737          per_position_definitions ppd
1738    WHERE pp.position_definition_id = ppd.position_definition_id
1739      AND pp.position_id = p_position_id
1740 	AND p_effective_date BETWEEN pp.effective_start_date
1741 	    AND pp.effective_end_date;
1742 
1743   lv_segment_name1      VARCHAR2(30) DEFAULT NULL;
1744   lv_segment_name2      VARCHAR2(30) DEFAULT NULL;
1745   ln_flex_num           NUMBER;
1746   lv_flex_code          VARCHAR2(3) := 'POS';
1747   lv_result             VARCHAR2(240) DEFAULT NULL;
1748   ltt_segment           hr_mee_views_gen.segmentsTable;
1749   ln_business_group_id  per_people_f.business_group_id%TYPE;
1750 
1751 BEGIN
1752 
1753   --First Get the name from per_positions and all the segments
1754   --from per_position_definitions.
1755   --The name will be returned if no profiles are used.
1756   FOR segment_rec IN lc_get_position_details(p_position_id => p_position_id)
1757   LOOP
1758     ltt_segment(0).value  := segment_rec.name;
1759     ltt_segment(1).value  := segment_rec.segment1;
1760     ltt_segment(2).value  := segment_rec.segment2;
1761     ltt_segment(3).value  := segment_rec.segment3;
1762     ltt_segment(4).value  := segment_rec.segment4;
1763     ltt_segment(5).value  := segment_rec.segment5;
1764     ltt_segment(6).value  := segment_rec.segment6;
1765     ltt_segment(7).value  := segment_rec.segment7;
1766     ltt_segment(8).value  := segment_rec.segment8;
1767     ltt_segment(9).value  := segment_rec.segment9;
1768     ltt_segment(10).value := segment_rec.segment10;
1769     ltt_segment(11).value := segment_rec.segment11;
1770     ltt_segment(12).value := segment_rec.segment12;
1771     ltt_segment(13).value := segment_rec.segment13;
1772     ltt_segment(14).value := segment_rec.segment14;
1773     ltt_segment(15).value := segment_rec.segment15;
1774     ltt_segment(16).value := segment_rec.segment16;
1775     ltt_segment(17).value := segment_rec.segment17;
1776     ltt_segment(18).value := segment_rec.segment18;
1777     ltt_segment(19).value := segment_rec.segment19;
1778     ltt_segment(20).value := segment_rec.segment20;
1779     ltt_segment(21).value := segment_rec.segment21;
1780     ltt_segment(22).value := segment_rec.segment22;
1781     ltt_segment(23).value := segment_rec.segment23;
1782     ltt_segment(24).value := segment_rec.segment24;
1783     ltt_segment(25).value := segment_rec.segment25;
1784     ltt_segment(26).value := segment_rec.segment26;
1785     ltt_segment(27).value := segment_rec.segment27;
1786     ltt_segment(28).value := segment_rec.segment28;
1787     ltt_segment(29).value := segment_rec.segment29;
1788     ltt_segment(30).value := segment_rec.segment30;
1789   END LOOP;
1790 
1791   lv_segment_name1 := fnd_profile.value('HR_POS_KEYFLEX_SEGMENT1');
1792   lv_segment_name2 := fnd_profile.value('HR_POS_KEYFLEX_SEGMENT2');
1793 
1794   IF lv_segment_name1 IS NULL
1795   THEN
1796     RETURN ltt_segment(0).value;--position_name
1797   END IF;
1798 
1799   ln_business_group_id := fnd_profile.value('PER_BUSINESS_GROUP_ID');
1800 
1801   OPEN lc_get_position_flex_id(p_business_group_id => ln_business_group_id);
1802   FETCH lc_get_position_flex_id INTO ln_flex_num;
1803   IF lc_get_position_flex_id%NOTFOUND
1804   THEN
1805     RETURN ltt_segment(0).value;--position_name
1806   END IF;
1807   CLOSE lc_get_position_flex_id;
1808 
1809   hr_mee_views_gen.get_segment_value(
1810                      p_flex_code       =>  lv_flex_code
1811                     ,p_flex_num        =>  ln_flex_num
1812                     ,p_segment_name1   =>  lv_segment_name1
1813                     ,p_segment_name2   =>  lv_segment_name2
1814                     ,p_segment         =>  ltt_segment
1815                     ,p_result          =>  lv_result);
1816 
1817   IF lv_result IS NULL
1818   THEN
1819     RETURN ltt_segment(0).value;--position_name
1820   ELSE
1821     RETURN lv_result;
1822   END IF;
1823 
1824 END get_position;
1825 
1826 
1827   /*
1828   ||===========================================================================
1829   || PROCEDURES: get_segment_result
1830   ||---------------------------------------------------------------------------
1831   ||
1832   || Description: Returns combined segment values if profiles are used.
1833   ||
1834   || Access Status:
1835   ||     Public
1836   ||
1837   ||===========================================================================
1838   */
1839 PROCEDURE get_segment_value( p_flex_code         IN VARCHAR2
1840                             ,p_flex_num          IN VARCHAR2
1841                             ,p_segment_name1     IN VARCHAR2 DEFAULT NULL
1842                             ,p_segment_name2     IN VARCHAR2 DEFAULT NULL
1843                             ,p_segment           hr_mee_views_gen.segmentsTable
1844                             ,p_result           OUT nocopy VARCHAR2)
1845 IS
1846 
1847   CURSOR lc_get_segment(p_application_id IN NUMBER,
1848                         p_flex_code      IN VARCHAR2,
1849                         p_flex_num       IN NUMBER,
1850                         p_segment_name   IN VARCHAR2)
1851   IS
1852   SELECT application_column_name
1853     FROM fnd_id_flex_segments_vl
1854    WHERE application_id = p_application_id
1855      AND id_flex_code   = p_flex_code
1856      AND id_flex_num    = p_flex_num
1857      AND segment_name   = p_segment_name
1858      AND enabled_flag = 'Y';
1859 
1860   ln_application_id     NUMBER := '800'; --value for PER
1861   lv_appl_col_name1     VARCHAR2(30) DEFAULT NULL;
1862   lv_appl_col_name2     VARCHAR2(30) DEFAULT NULL;
1863   ln_row_counter        NUMBER := 1;
1864   lv_seg_delimiter      VARCHAR2(10); --2424031
1865 
1866 BEGIN
1867 
1868   OPEN lc_get_segment(p_application_id => ln_application_id,
1869                       p_flex_code      => p_flex_code,
1870                       p_flex_num       => p_flex_num,
1871                       p_segment_name   => p_segment_name1);
1872   FETCH lc_get_segment INTO lv_appl_col_name1;
1873   CLOSE lc_get_segment;
1874 
1875   IF p_segment_name2 IS NOT NULL
1876   THEN
1877     OPEN lc_get_segment(p_application_id => ln_application_id,
1878                         p_flex_code      => p_flex_code,
1879                         p_flex_num       => p_flex_num,
1880                         p_segment_name   => p_segment_name2);
1881     FETCH lc_get_segment INTO lv_appl_col_name2;
1882     CLOSE lc_get_segment;
1883   END IF;
1884 
1885   IF lv_appl_col_name1 IS NOT NULL
1886   THEN
1887     LOOP
1888       IF UPPER(lv_appl_col_name1) = ('SEGMENT'||ln_row_counter)
1889       THEN
1890         p_result := p_segment(ln_row_counter).value;
1891         EXIT;
1892       ELSE
1893         ln_row_counter := ln_row_counter + 1;
1894         IF ln_row_counter > 30
1895         THEN
1896           EXIT;
1897         END IF;
1898       END IF;
1899     END LOOP;
1900   ELSE
1901     p_result := NULL;
1902   END IF;
1903 
1904   IF lv_appl_col_name2 IS NOT NULL
1905   THEN
1906     --2424031 fix starts
1907     lv_seg_delimiter := FND_FLEX_APIS.gbl_get_segment_delimiter
1908       			(x_application_id => ln_application_id,
1909        			 x_id_flex_code   => p_flex_code,
1910        			 x_id_flex_num    => p_flex_num) ;
1911     p_result := p_result || lv_seg_delimiter ;
1912     --2424031 fix ends
1913     ln_row_counter := 1;
1914     LOOP
1915       IF UPPER(lv_appl_col_name2) = ('SEGMENT'||ln_row_counter)
1916       THEN
1917         p_result := p_result||p_segment(ln_row_counter).value;
1918         EXIT;
1919       ELSE
1920         ln_row_counter := ln_row_counter + 1;
1921         IF ln_row_counter > 30
1922         THEN
1923           EXIT;
1924         END IF;
1925       END IF;
1926     END LOOP;
1927   END IF;
1928 
1929 EXCEPTION
1930 
1931   WHEN OTHERS
1932   THEN
1933       p_result := null;
1934 
1935       raise;
1936 
1937 END get_segment_value;
1938 
1939 FUNCTION get_currency_format(
1940            p_curcode        pay_element_types_f.input_currency_code%TYPE,
1941            p_effective_date DATE
1942          )
1943 RETURN VARCHAR2
1944 IS
1945   ln_dp      NUMBER(1);
1946   lv_fstring VARCHAR2(30);
1947 
1948   CURSOR currency_details IS
1949     SELECT cur.precision
1950     FROM  fnd_currencies_vl cur
1951     WHERE cur.currency_code = p_curcode
1952     AND   p_effective_date BETWEEN NVL(cur.start_date_active,p_effective_date)
1953                                AND NVL(cur.end_date_active,p_effective_date);
1954 
1955 BEGIN
1956 
1957   OPEN currency_details;
1958   FETCH currency_details INTO ln_dp;
1959   CLOSE currency_details;
1960   lv_fstring:='FM9999999999999D';
1961 
1962   IF(ln_dp>0) THEN
1963     WHILE ln_dp > 0
1964     LOOP
1965       lv_fstring:=lv_fstring||'0';
1966       ln_dp:=ln_dp-1;
1967     END LOOP;
1968   ELSE
1969     lv_fstring:='FM99999999999999';
1970   END IF;
1971 
1972   RETURN lv_fstring;
1973 
1974 END get_currency_format;
1975 
1976 
1977   /*
1978   ||===========================================================================
1979   || FUNCTION: get_contacts_type_list
1980   ||---------------------------------------------------------------------------
1981   ||
1982   || Description:
1983   ||     For a given person and their contact, create and return a string of
1984   ||     their rlationship types.  Eg "Emergency, Brother"
1985   ||
1986   || Access Status:
1987   ||     Public
1988   ||
1989   ||===========================================================================
1990   */
1991 
1992 FUNCTION get_contacts_type_list(
1993                      p_person_id      IN per_contact_relationships.person_id%TYPE
1994                     ,p_contact_id     IN per_contact_relationships.contact_person_id%TYPE
1995 				    ,p_effective_date IN DATE DEFAULT TRUNC(SYSDATE))
1996 RETURN VARCHAR2
1997 IS
1998 
1999   -- Get rowset of contact relationships, often just one
2000   CURSOR lc_get_contact(pp_person_id IN  NUMBER, pp_contact_id IN  NUMBER, pp_effective_date IN DATE)
2001   IS
2002     select pcr.contact_type     Contact_Type,
2003            decode(pcr.contact_type,'EMRG','Y','N') Emergency_Contact,
2004            HR_GENERAL.DECODE_LOOKUP('CONTACT',pcr.contact_type) Full_Contact_Type
2005     from   per_contact_relationships pcr,
2006            per_all_people_f          per
2007     where  pcr.person_id         = pp_person_id
2008     and    pcr.contact_person_id = pp_contact_id
2009     and    pcr.contact_person_id = per.person_id
2010     and    pp_effective_date between
2011                 decode(pcr.date_start,null,trunc(sysdate),trunc(pcr.date_start))
2012               and decode(pcr.date_end,null,trunc(sysdate),trunc(pcr.date_end))
2013     and    pp_effective_date between per.effective_start_date and per.effective_end_date
2014     order by Emergency_Contact desc;
2015 
2016   lv_delim   VARCHAR2(2)   DEFAULT ', ';
2017   lv_result  VARCHAR2(240) DEFAULT NULL;
2018 
2019 BEGIN
2020 
2021   --If emergency type then this will appear first due to order by, then remaining appear in reverse alphabetical
2022   --Return coma delimited list
2023   FOR contact_rec IN lc_get_contact(pp_person_id => p_person_id, pp_contact_id => p_contact_id, pp_effective_date => p_effective_date)
2024   LOOP
2025     if lv_result is null then
2026       lv_result := contact_rec.Full_Contact_Type;
2027     else
2028       lv_result := lv_result||lv_delim||contact_rec.Full_Contact_Type;
2029     end if;
2030   END LOOP;
2031     RETURN lv_result;
2032 
2033 END get_contacts_type_list;
2034 
2035   /*
2036   ||===========================================================================
2037   || FUNCTION: is_emergency_contact
2038   ||---------------------------------------------------------------------------
2039   ||
2040   || Description:
2041   ||     For a given person, return whether a contact is an emergency contact.
2042   ||     Returns 2 for Primary Emergency, 1 for Emergency, else 0
2043   ||
2044   || Access Status:
2045   ||     Public
2046   ||
2047   ||===========================================================================
2048   */
2049 
2050 FUNCTION is_emergency_contact(
2051                      p_person_id      IN per_contact_relationships.person_id%TYPE
2052                     ,p_contact_id     IN per_contact_relationships.contact_person_id%TYPE
2053 				    ,p_effective_date IN DATE DEFAULT TRUNC(SYSDATE))
2054 RETURN NUMBER
2055 IS
2056 
2057   lv_primary VARCHAR2(1) DEFAULT null;
2058 
2059 BEGIN
2060     select pcr.primary_contact_flag into lv_primary
2061     from   per_contact_relationships pcr,
2062            per_all_people_f          per
2063     where  pcr.person_id         = p_person_id
2064     and    pcr.contact_person_id = p_contact_id
2065     and    pcr.contact_person_id = per.person_id
2066     and    p_effective_date between
2067                 decode(pcr.date_start,null,trunc(sysdate),trunc(pcr.date_start))
2068               and decode(pcr.date_end,null,trunc(sysdate),trunc(pcr.date_end))
2069     and contact_type = 'EMRG'
2070     and rownum < 2;
2071 
2072   if lv_primary = 'Y' then
2073     RETURN 2;
2074   else RETURN 1;
2075   end if;
2076 EXCEPTION
2077   when NO_DATA_FOUND then
2078     RETURN 0;
2079 END is_emergency_contact;
2080 
2081 
2082 -- bug fix 4059724 begins
2083 
2084 PROCEDURE openTrngScoreCsr(
2085     p_cursor IN OUT NOCOPY cur_typ
2086    ,p_mode IN NUMBER
2087    ,p_person_id IN NUMBER
2088    ,p_event_id IN NUMBER) IS
2089 
2090 query_str VARCHAR2(4000);
2091 BEGIN
2092     if( p_mode = 0) then
2093 
2094  query_str :=  'Select db.score FROM ota_delegate_bookings db, ota_events evt '||
2095  ',ota_activity_versions av '||
2096  ',ota_booking_status_types bs , ota_booking_status_histories bsh '||
2097  'WHERE db.booking_status_type_id = bs.booking_status_type_id  '||
2098  'and db.delegate_person_id = :1  '||
2099  'AND db.booking_id = bsh.booking_id(+) '||
2100  'AND db.booking_status_type_id = bsh.booking_status_type_id (+) '||
2101  'AND db.event_id = evt.event_id  '||
2102  'AND evt.activity_version_id = av.activity_version_id (+) ' ;
2103 
2104 
2105     OPEN p_cursor FOR query_str USING p_person_id ;
2106 
2107 
2108 ELSE
2109  query_str :=  'select opr.score  from ota_performances opr , ota_offerings ofr '||
2110  ', ota_learning_objects olo , ota_events evt '||
2111 'where ofr.learning_object_id = olo.learning_object_id(+) '||
2112 'and olo.learning_object_id = opr.learning_object_id(+) '||
2113 'and opr.user_id(+) = :1 '||
2114 'and evt.event_id = :2 '||
2115 'and ofr.OFFERING_ID = evt.parent_offering_id '||
2116 'and evt.parent_offering_id is not null ';
2117 
2118 
2119 OPEN p_cursor FOR query_str USING p_person_id , p_event_id ;
2120 
2121 end if ;
2122 end  openTrngScoreCsr ;
2123 
2124 
2125 
2126 FUNCTION getTrngScore(p_person_id IN NUMBER, p_event_id IN NUMBER) RETURN NUMBER
2127 IS
2128  l_cnt NUMBER:=0;
2129  l_cursor cur_typ;
2130 BEGIN
2131 
2132     openTrngScoreCsr(
2133         l_cursor
2134        ,0
2135        ,p_person_id
2136        ,p_event_id);
2137 
2138     FETCH l_cursor INTO l_cnt;
2139 
2140      CLOSE l_cursor;
2141      if (l_cnt is not null) then
2142         RETURN l_cnt;
2143 
2144 
2145     else
2146         openTrngScoreCsr(
2147         l_cursor
2148        ,1
2149        ,p_person_id
2150        ,p_event_id);
2151           FETCH l_cursor INTO l_cnt;
2152            CLOSE l_cursor;
2153 
2154           if (l_cnt = -1000)then
2155             return null;
2156 
2157           else
2158             return l_cnt;
2159 
2160           end if;
2161     end if ;
2162 
2163     Exception When Others then
2164        CLOSE l_cursor;
2165        return null;
2166   END getTrngScore;
2167 
2168 -- bug fix 4059724 end
2169 -- Bug 4513393 Begins
2170 Function getTrngEndDate (p_person_id IN NUMBER, p_event_id IN NUMBER) RETURN DATE
2171 IS
2172 l_cursor cur_typ;
2173 cur_str VARCHAR2(4000);
2174 EndDate DATE;
2175 BEGIN
2176 cur_str := 'Select opf.completed_date EndDate '||
2177  'From OTA_EVENTS oev, OTA_OFFERINGS ofr, OTA_PERFORMANCES opf, ota_category_usages ocu '||
2178 'Where oev.parent_offering_id = ofr.offering_id '||
2179                'And ofr.learning_object_id = opf.learning_object_id(+) '||
2180                'And ocu.Category_Usage_Id = ofr.Delivery_Mode_Id '||
2181                'and ocu.Online_Flag = ''Y'' '||
2182                'And opf.User_id(+) = :1 '||
2183                'And oev.event_id = :2 ';
2184 open l_cursor for cur_str USING p_person_id , p_event_id  ;
2185    fetch l_cursor into EndDate;
2186    close l_cursor;
2187    return EndDate;
2188 
2189 
2190 Exception When Others then
2191        CLOSE l_cursor;
2192        return null;
2193 END getTrngEndDate;
2194 -- Bug 4513393 Ends
2195 END hr_mee_views_gen ;