[Home] [Help]
PACKAGE BODY: APPS.HR_AUTH_BRIDGE
Source
1 package body hr_auth_bridge as
2 /* $Header: hrathbrd.pkb 115.1 2002/05/29 05:42:14 pkm ship $ */
3
4
5 FUNCTION get_coverage
6 (
7 p_prtt_enrt_rslt_id in number,
8 p_per_in_ler_id in number,
9 p_acty_typ_cd in varchar2,
10 p_type in varchar2
11 )
12 RETURN varchar2 IS
13 --
14 CURSOR c_ee_contrib IS
15 SELECT prv.cmcd_rt_val ||' '||
16 HR_GENERAL.DECODE_LOOKUP('BEN_ENRT_INFO_RT_FREQ',
17 prv.cmcd_ref_perd_cd)
18 FROM ben_prtt_rt_val prv
19 WHERE prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
20 AND prv.per_in_ler_id = p_per_in_ler_id
21 AND prv.acty_typ_cd = p_acty_typ_cd
22 AND prv.prtt_rt_val_stat_cd IS NULL
23 AND prv.dsply_on_enrt_flag = 'Y';
24
25 CURSOR c_er_contrib IS
26 SELECT prv.cmcd_rt_val ||' '||
27 HR_GENERAL.DECODE_LOOKUP('BEN_ENRT_INFO_RT_FREQ',
28 prv.cmcd_ref_perd_cd)
29 FROM ben_prtt_rt_val prv
30 WHERE prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
31 AND prv.per_in_ler_id = p_per_in_ler_id
32 AND prv.acty_typ_cd = p_acty_typ_cd
33 AND prv.prtt_rt_val_stat_cd IS NULL;
34
35 l_ret_val varchar2(200);
36 --
37 BEGIN
38 --
39 IF p_type = 'EE' THEN
40 OPEN c_ee_contrib;
41 FETCH c_ee_contrib INTO l_ret_val;
42 CLOSE c_ee_contrib;
43 ELSIF p_type = 'ER' THEN
44 OPEN c_er_contrib;
45 FETCH c_er_contrib INTO l_ret_val;
46 CLOSE c_er_contrib;
47 END IF;
48 RETURN (l_ret_val);
49 --
50 END get_coverage;
51 --
52
53 FUNCTION get_beneficiaries
54 (
55 p_prtt_enrt_rslt_id in number,
56 p_per_in_ler_id in number,
57 p_prmry_cntngnt_cd in varchar2
58 )
59 RETURN varchar2 IS
60 --
61
62 l_ret_val varchar2(1500);
63 --
64 BEGIN
65 --
66 l_ret_val := get_beneficiaries(p_prtt_enrt_rslt_id,
67 p_per_in_ler_id,
68 p_prmry_cntngnt_cd,
69 TRUNC(SYSDATE));
70
71 RETURN (l_ret_val);
72 --
73 END;
74 --
75
76 FUNCTION get_beneficiaries
77 (
78 p_prtt_enrt_rslt_id in number,
79 p_per_in_ler_id in number,
80 p_prmry_cntngnt_cd in varchar2,
81 p_effective_date in date
82 )
83 RETURN varchar2 IS
84 --
85 CURSOR c_bnf IS
86 SELECT ppf.full_name ||' '||
87 DECODE(pbn.pct_dsgd_num,NULL,TO_CHAR(pbn.amt_dsgd_val),TO_CHAR(pbn.pct_dsgd_num)||'%') beneficiary
88 FROM ben_pl_bnf_f pbn,
89 per_all_people_f ppf
90 WHERE pbn.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
91 AND pbn.per_in_ler_id = p_per_in_ler_id
92 AND p_effective_date BETWEEN pbn.effective_start_date AND pbn.effective_end_date
93 AND pbn.prmry_cntngnt_cd = p_prmry_cntngnt_cd
94 AND pbn.bnf_person_id = ppf.person_id
95 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
96
97 l_ret_val varchar2(1500);
98 --
99 BEGIN
100 --
101 FOR r_bnf IN c_bnf LOOP
102 IF l_ret_val IS NULL THEN
103 l_ret_val := r_bnf.beneficiary;
104 ELSE
105 l_ret_val := l_ret_val ||', '|| r_bnf.beneficiary;
106 END IF;
107 END LOOP;
108
109 RETURN l_ret_val;
110 --
111 END;
112 --
113
114 FUNCTION get_primary_care_providers
115 (
116 p_prtt_enrt_rslt_id in number,
117 p_business_group_id in number
118 )
119 RETURN varchar2 IS
120 --
121
122 l_ret_val varchar2(1500);
123 --
124 BEGIN
125 --
126 l_ret_val := get_primary_care_providers(p_prtt_enrt_rslt_id,
127 p_business_group_id,
128 TRUNC(SYSDATE));
129
130 RETURN (l_ret_val);
131 --
132 END;
133 --
134
135 FUNCTION get_primary_care_providers
136 (
137 p_prtt_enrt_rslt_id in number,
138 p_business_group_id in number,
139 p_effective_date in date
140 )
141 RETURN varchar2 IS
142 --
143
144 CURSOR c_pcp IS
145 SELECT pcp.name ||' '||
146 HR_GENERAL.DECODE_LOOKUP('BEN_PRMRY_CARE_PRVDR_TYP',pcp.prmry_care_prvdr_typ_cd) primary_care_provider
147 FROM ben_prmry_care_prvdr_f pcp
148 WHERE pcp.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
149 AND pcp.business_group_id = p_business_group_id
150 AND p_effective_date BETWEEN pcp.effective_start_date AND pcp.effective_end_date;
151
152 l_ret_val varchar2(1500);
153 --
154 BEGIN
155 --
156 FOR r_pcp IN c_pcp LOOP
157 IF l_ret_val IS NULL THEN
158 l_ret_val := r_pcp.primary_care_provider;
159 ELSE
160 l_ret_val := l_ret_val ||', '|| r_pcp.primary_care_provider;
161 END IF;
162 END LOOP;
163
164 RETURN (l_ret_val);
165 --
166 END;
167 --
168
169 FUNCTION get_interim_flag
170 (
171 p_prtt_enrt_rslt_id in number,
172 p_business_group_id in number
173 )
174 RETURN varchar2 IS
175 --
176
177 l_ret_val varchar2(100);
178 --
179 BEGIN
180 --
181 l_ret_val := get_interim_flag(p_prtt_enrt_rslt_id,
182 p_business_group_id,
183 TRUNC(SYSDATE));
184
185 RETURN (l_ret_val);
186 --
187 END;
188 --
189
190 FUNCTION get_interim_flag
191 (
192 p_prtt_enrt_rslt_id in number,
193 p_business_group_id in number,
194 p_effective_date in date
195 )
196 RETURN varchar2 IS
197 --
198 CURSOR c_interim IS
199 SELECT rplcs_sspndd_rslt_id
200 FROM ben_prtt_enrt_rslt_f pen
201 WHERE pen.rplcs_sspndd_rslt_id = p_prtt_enrt_rslt_id
202 AND pen.business_group_id = p_business_group_id
203 AND p_effective_date BETWEEN pen.effective_start_date AND pen.effective_end_date;
204
205 l_rplcs_sspndd_rslt_id ben_prtt_enrt_rslt_f.rplcs_sspndd_rslt_id%type;
206 l_ret_val varchar2(100);
207 --
208 BEGIN
209 --
210 OPEN c_interim;
211 FETCH c_interim INTO l_rplcs_sspndd_rslt_id;
212 CLOSE c_interim;
213 IF l_rplcs_sspndd_rslt_id IS NOT NULL THEN
214 l_ret_val := FND_MESSAGE.GET_STRING('BEN','BEN_93047_INTERIM_PLAN');
215 END IF;
216
217 RETURN (l_ret_val);
218 --
219 END get_interim_flag;
220 --
221
222 FUNCTION get_contact_relationships
223 (
224 p_person_id in number,
225 p_contact_person_id in number
226 )
227 RETURN varchar2 IS
228 --
229
230 l_ret_val varchar2(1500);
231 --
232 BEGIN
233 --
234 l_ret_val := get_contact_relationships(p_person_id,
235 p_contact_person_id,
236 TRUNC(SYSDATE));
237
238 RETURN (l_ret_val);
239 --
240 END;
241 --
242
243 FUNCTION get_contact_relationships
244 (
245 p_person_id in number,
246 p_contact_person_id in number,
247 p_effective_date in date
248 )
249 RETURN varchar2 IS
250 --
251
252 CURSOR c_contacts IS
253 SELECT HR_GENERAL.DECODE_LOOKUP('CONTACT',pcr.contact_type) contact_type
254 FROM per_contact_relationships pcr
255 WHERE pcr.person_id = p_person_id
256 AND pcr.contact_person_id = p_contact_person_id
257 AND p_effective_date BETWEEN NVL(pcr.date_start,p_effective_date) AND NVL(pcr.date_end,p_effective_date)
258 ORDER BY DECODE(pcr.personal_flag,'Y',1),pcr.contact_type;
259
260 l_ret_val varchar2(1500);
261 --
262 BEGIN
263 --
264 FOR r_contacts IN c_contacts LOOP
265 IF l_ret_val IS NULL THEN
266 l_ret_val := r_contacts.contact_type;
267 ELSE
268 l_ret_val := l_ret_val ||', '|| r_contacts.contact_type;
269 END IF;
270 END LOOP;
271
272 RETURN (l_ret_val);
273 --
274 END;
275 --
276
277 FUNCTION get_proposed_salary
278 (
279 p_assignment_id in number
280 )
281 RETURN varchar2 IS
282 --
283
284 l_ret_val varchar2(50);
285 --
286 BEGIN
287 --
288 l_ret_val := get_proposed_salary(p_assignment_id,
289 TRUNC(SYSDATE));
290
291 RETURN (l_ret_val);
292 --
293 END;
294 --
295
296 FUNCTION get_proposed_salary
297 (
298 p_assignment_id in number,
299 p_effective_date in date
300 )
301 RETURN varchar2 IS
302 --
303
304 CURSOR c_salary IS
305 SELECT TO_CHAR(ppp.proposed_salary_n)
306 FROM per_pay_proposals ppp
307 WHERE ppp.pay_proposal_id = (SELECT MAX(ppp1.pay_proposal_id)
308 FROM per_pay_proposals ppp1
309 WHERE ppp1.assignment_id = p_assignment_id
310 AND ppp1.approved = 'Y'
311 AND ppp1.change_date <= p_effective_date);
312
313 l_ret_val varchar2(50);
314 --
315 BEGIN
316 --
317 OPEN c_salary;
318 FETCH c_salary INTO l_ret_val;
319 CLOSE c_salary;
320
321 RETURN (l_ret_val);
322 --
323 END;
324 --
325
326 FUNCTION get_salary_change_date
327 (
328 p_assignment_id in number
329 )
330 RETURN varchar2 IS
331 --
332
333 l_ret_val varchar2(20);
334 --
335 BEGIN
336 --
337 l_ret_val := get_salary_change_date(p_assignment_id,
338 TRUNC(SYSDATE));
339
340 RETURN (l_ret_val);
341 --
342 END;
343 --
344
345 FUNCTION get_salary_change_date
346 (
347 p_assignment_id in number,
348 p_effective_date in date
349 )
350 RETURN varchar2 IS
351 --
352
353 CURSOR c_change_dt IS
354 SELECT TO_CHAR(ppp.change_date,'MM/DD/YYYY')
355 FROM per_pay_proposals ppp
356 WHERE ppp.pay_proposal_id = (SELECT MAX(ppp1.pay_proposal_id)
357 FROM per_pay_proposals ppp1
358 WHERE ppp1.assignment_id = p_assignment_id
359 AND ppp1.approved = 'Y'
360 AND ppp1.change_date <= p_effective_date);
361
362 l_ret_val varchar2(20);
363 --
364 BEGIN
365 --
366 OPEN c_change_dt;
367 FETCH c_change_dt INTO l_ret_val;
368 CLOSE c_change_dt;
369
370 RETURN (l_ret_val);
371 --
372 END;
373 --
374
375 FUNCTION get_performance_rating
376 (
377 p_person_id in number
378 )
379 RETURN varchar2 IS
380 --
381
382 l_ret_val varchar2(50);
383 --
384 BEGIN
385 --
386 l_ret_val := get_performance_rating(p_person_id,
387 TRUNC(SYSDATE));
388
389 RETURN (l_ret_val);
390 --
391 END;
392 --
393
394 FUNCTION get_performance_rating
395 (
396 p_person_id in number,
397 p_effective_date in date
398 )
399 RETURN varchar2 IS
400 --
401
402 CURSOR c_rating IS
403 SELECT ppr.performance_rating
404 FROM per_performance_reviews ppr
405 WHERE ppr.performance_review_id = (SELECT MAX(ppr1.performance_review_id)
406 FROM per_performance_reviews ppr1
407 WHERE ppr1.person_id = p_person_id
408 AND ppr1.review_date <= p_effective_date);
409
410 l_ret_val per_performance_reviews.performance_rating%type;
411 --
412 BEGIN
413 --
414 OPEN c_rating;
415 FETCH c_rating INTO l_ret_val;
416 CLOSE c_rating;
417
418 RETURN (l_ret_val);
419 --
420 END;
421 --
422
423 FUNCTION get_person_start_date
424 (
425 p_person_id in number,
426 p_period_of_service_id in number,
427 p_paf_effective_start_date in date,
428 p_assignment_type in varchar2
429 )
430 RETURN date IS
431 --
432
433 l_ret_val date;
434 --
435 BEGIN
436 --
437 l_ret_val := get_person_start_date(p_person_id,
438 p_period_of_service_id,
439 p_paf_effective_start_date,
440 p_assignment_type,
441 TRUNC(SYSDATE));
442
443 RETURN (l_ret_val);
444 --
445 END;
446 --
447
448 FUNCTION get_person_start_date
449 (
450 p_person_id in number,
451 p_period_of_service_id in number,
452 p_paf_effective_start_date in date,
453 p_assignment_type in varchar2,
454 p_effective_date in date
455 )
456 RETURN date IS
457 --
458
459 CURSOR c_pps_start_dt IS
460 SELECT pps.date_start
461 FROM per_periods_of_service pps
462 WHERE pps.period_of_service_id = p_period_of_service_id
463 AND pps.person_id = p_person_id;
464
465 l_ret_val date;
466 --
467 BEGIN
468 --
469 IF p_assignment_type = 'B' THEN
470 l_ret_val := p_paf_effective_start_date;
471 ELSIF p_assignment_type = 'E' THEN
472 OPEN c_pps_start_dt;
473 FETCH c_pps_start_dt INTO l_ret_val;
474 CLOSE c_pps_start_dt;
475 END IF;
476
477 RETURN (l_ret_val);
478 --
479 END;
480 --
481
482 FUNCTION get_person_end_date
483 (
484 p_person_id in number,
485 p_period_of_service_id in number,
486 p_paf_effective_end_date in date,
487 p_assignment_type in varchar2
488 )
489 RETURN date IS
490 --
491
492 l_ret_val date;
493 --
494 BEGIN
495 --
496 l_ret_val := get_person_end_date(p_person_id,
497 p_period_of_service_id,
498 p_paf_effective_end_date,
499 p_assignment_type,
500 TRUNC(SYSDATE));
501
502 RETURN (l_ret_val);
503 --
504 END;
505 --
506
507 FUNCTION get_person_end_date
508 (
509 p_person_id in number,
510 p_period_of_service_id in number,
511 p_paf_effective_end_date in date,
512 p_assignment_type in varchar2,
513 p_effective_date in date
514 )
515 RETURN date IS
516 --
517
518 CURSOR c_pps_end_dt IS
519 SELECT NVL(pps.actual_termination_date,TO_DATE('12/31/4712','MM/DD/YYYY'))
520 FROM per_periods_of_service pps
521 WHERE pps.period_of_service_id = p_period_of_service_id
522 AND pps.person_id = p_person_id;
523
524 l_ret_val date;
525 --
526 BEGIN
527 --
528 IF p_assignment_type = 'B' THEN
529 l_ret_val := p_paf_effective_end_date;
530 ELSIF p_assignment_type = 'E' THEN
531 OPEN c_pps_end_dt;
532 FETCH c_pps_end_dt INTO l_ret_val;
533 CLOSE c_pps_end_dt;
534 END IF;
535
536 RETURN (l_ret_val);
537 --
538 END;
539 --
540
541 FUNCTION get_per_system_status
542 (
543 p_assignment_status_type_id in number
544 )
545 RETURN varchar2 IS
546 --
547
548 CURSOR c_per_system_status IS
549 SELECT pas.per_system_status
550 FROM per_assignment_status_types pas
551 WHERE pas.assignment_status_type_id = p_assignment_status_type_id;
552
553 l_ret_val per_assignment_status_types.per_system_status%type;
554 --
555 BEGIN
556 --
557 OPEN c_per_system_status;
558 FETCH c_per_system_status INTO l_ret_val;
559 CLOSE c_per_system_status;
560
561 RETURN (l_ret_val);
562 --
563 END;
564 --
565
566 FUNCTION get_assignment_id
567 (
568 p_person_id in number
569 )
570 RETURN number IS
571 --
572 CURSOR c_assignment IS
573 SELECT paf.assignment_id
574 FROM per_all_assignments_f paf
575 WHERE paf.person_id = p_person_id
576 AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
577 AND hr_auth_bridge.get_per_system_status(paf.assignment_status_type_id) = 'ACTIVE_ASSIGN'
578 AND paf.primary_flag = 'Y'
579 AND paf.assignment_type IN ('E','B')
580 ORDER BY paf.assignment_type DESC;
581
582 l_assignment_id per_all_assignments_f.assignment_id%type;
583 --
584 BEGIN
585 --
586 OPEN c_assignment;
587 FETCH c_assignment INTO l_assignment_id;
588 CLOSE c_assignment;
589
590 RETURN l_assignment_id;
591 END;
592
593 END hr_auth_bridge;