DBA Data[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;