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