[Home] [Help]
PACKAGE BODY: APPS.BEN_EXT_FLCR
Source
1 Package Body BEN_EXT_FLCR AS
2 /* $Header: benxflcr.pkb 120.1 2006/04/20 15:48:05 tjesumic noship $ */
3 g_package varchar2(33) := ' ben_ext_flcr.'; -- Global package name
4 --
5 -- ----------------------------------------------------------------------------
6 -- |------< init_detl_globals >------------------------------------------------|
7 -- ----------------------------------------------------------------------------
8 --
9 Procedure init_detl_globals IS
10 --
11 l_proc varchar2(72) := g_package||'init_detl_globals';
12 --
13 Begin
14 --
15 hr_utility.set_location('Entering'||l_proc, 5);
16 --
17 ben_ext_person.g_flex_pgm_id := null;
18 ben_ext_person.g_flex_pgm_name := null;
19 ben_ext_person.g_flex_pl_id := null;
20 ben_ext_person.g_flex_pl_name := null;
21 ben_ext_person.g_flex_pl_typ_id := null;
22 ben_ext_person.g_flex_pl_typ_name := null;
23 ben_ext_person.g_flex_opt_id := null;
24 ben_ext_person.g_flex_opt_name := null;
25 ben_ext_person.g_flex_cmbn_plip_id := null;
26 ben_ext_person.g_flex_cmbn_plip_name := null;
27 ben_ext_person.g_flex_cmbn_ptip_id := null;
28 ben_ext_person.g_flex_cmbn_ptip_name := null;
29 ben_ext_person.g_flex_cmbn_ptip_opt_id := null;
30 ben_ext_person.g_flex_cmbn_ptip_opt_name := null;
31 ben_ext_person.g_flex_amt := null;
32 ben_ext_person.g_flex_currency := null;
33 ben_ext_person.g_flex_bnft_pool_id := null;
34 ben_ext_person.g_flex_bnft_pool_name := null;
35 --
36 hr_utility.set_location('Exiting'||l_proc, 15);
37 --
38 End init_detl_globals;
39 --
40 -- ----------------------------------------------------------------------------
41 -- |------< main >-----------------------------------------------------|
42 -- ----------------------------------------------------------------------------
43 --
44 Procedure main
45 ( p_person_id in number,
46 p_ext_rslt_id in number,
47 p_ext_file_id in number,
48 p_data_typ_cd in varchar2,
49 p_ext_typ_cd in varchar2,
50 p_chg_evt_cd in varchar2,
51 p_business_group_id in number,
52 p_effective_date in date) is
53 --
54 l_proc varchar2(72) := g_package||'main';
55 --
56 l_include varchar2(1) := 'Y';
57 --
58 cursor c_elig(p_person_id number) is
59 select
60 pler.ler_id ler_id,
61 pler.per_in_ler_stat_cd per_in_ler_stat_cd,
62 pler.lf_evt_ocrd_dt lf_evt_ocrd_dt,
63 pler.ntfn_dt ntfn_dt,
64 echc.enrt_cvg_strt_dt enrt_cvg_strt_dt,
65 echc.yr_perd_id yr_perd_id,
66 echc.per_in_ler_id per_in_ler_id,
67 echc.prtt_enrt_rslt_id prtt_enrt_rslt_id,
68 echc.last_update_date last_update_date,
69 echc.pl_id pl_id,
70 pl.name pl_name,
71 ptp.name pl_typ_name,
72 echc.elig_per_elctbl_chc_id elig_per_elctbl_chc_id,
73 echc.oipl_id opt_id,
74 echc.pl_typ_id pl_typ_id,
75 opt.name opt_name,
76 ppopl.uom uom,
77 nvl(ecr.dflt_val,ecr.val) flex_amt,
78 echc.pgm_id program_id,
79 pgm.name program_name,
80 echc.cmbn_plip_id cmbn_plip_id,
81 cmbn_plip.name cmbn_plip_name,
82 echc.cmbn_ptip_id cmbn_ptip_id,
83 cmbn_ptip.name cmbn_ptip_name,
84 echc.cmbn_ptip_opt_id cmbn_ptip_opt_id,
85 cmbn_ptip_opt.name cmbn_ptip_opt_name,
86 pool.bnft_prvdr_pool_id pool_id,
87 pool.name pool_name
88 from
89 ben_per_in_ler pler,
90 ben_elig_per_elctbl_chc echc,
91 ben_enrt_rt ecr,
92 ben_pil_elctbl_chc_popl ppopl,
93 ben_opt_f opt,
94 ben_pl_f pl,
95 ben_plip_f plip,
96 ben_oipl_f oipl,
97 ben_pgm_f pgm,
98 ben_pl_typ_f ptp,
99 ben_cmbn_plip_f cmbn_plip,
100 ben_cmbn_ptip_f cmbn_ptip,
101 ben_cmbn_ptip_opt_f cmbn_ptip_opt,
102 ben_bnft_prvdr_pool_f pool
103 where
104 pler.person_id = p_person_id
105 and pler.per_in_ler_id = echc.per_in_ler_id
106 and pler.per_in_ler_id = ppopl.per_in_ler_id -- 3662774: Performance fix: Added this join.
107 and echc.elig_per_elctbl_chc_id = ecr.elig_per_elctbl_chc_id
108 and echc.bnft_prvdr_pool_id = pool.bnft_prvdr_pool_id(+)
109 and ecr.rt_usg_cd = 'FLXCR'
110 -- 3662774: Performance fix: Removed the outer join
111 -- and echc.pil_elctbl_chc_popl_id(+) = ppopl.pil_elctbl_chc_popl_id
112 and echc.pil_elctbl_chc_popl_id = ppopl.pil_elctbl_chc_popl_id
113 -- 3662774: Performance fix: end;
114 and nvl(echc.pgm_id,-1) = pgm.pgm_id(+)
115 and nvl(echc.pl_id,-1) = pl.pl_id(+)
116 and nvl(oipl.opt_id,-1) = opt.opt_id(+)
117 and nvl(echc.oipl_id,-1) = oipl.oipl_id(+)
118 and nvl(echc.plip_id,-1) = plip.plip_id(+)
119 and pl.pl_typ_id = ptp.pl_typ_id (+)
120 and nvl(echc.cmbn_plip_id,-1) = cmbn_plip.cmbn_plip_id(+)
121 and nvl(echc.cmbn_ptip_id,-1) = cmbn_ptip.cmbn_ptip_id(+)
122 and nvl(echc.cmbn_ptip_opt_id,-1) = cmbn_ptip_opt.cmbn_ptip_opt_id(+)
123 and p_effective_date between nvl(pl.effective_start_date,p_effective_date)
124 and nvl(pl.effective_end_date ,p_effective_date)
125 and p_effective_date between nvl(opt.effective_start_date,p_effective_date)
126 and nvl(opt.effective_end_date ,p_effective_date)
127 and p_effective_date between nvl(plip.effective_start_date,p_effective_date)
128 and nvl(plip.effective_end_date ,p_effective_date)
129 and p_effective_date between nvl(oipl.effective_start_date,p_effective_date)
130 and nvl(oipl.effective_end_date ,p_effective_date)
131 and p_effective_date between nvl(ptp.effective_start_date,p_effective_date)
132 and nvl(ptp.effective_end_date ,p_effective_date)
133 and p_effective_date between nvl(cmbn_plip.effective_start_date,p_effective_date)
134 and nvl(cmbn_plip.effective_end_date ,p_effective_date)
135 and p_effective_date between nvl(cmbn_ptip.effective_start_date,p_effective_date)
136 and nvl(cmbn_ptip.effective_end_date ,p_effective_date)
137 and p_effective_date between nvl(pool.effective_start_date,p_effective_date)
138 and nvl(pool.effective_end_date ,p_effective_date)
139 and p_effective_date between nvl(cmbn_ptip_opt.effective_start_date,p_effective_date)
140 and nvl(cmbn_ptip_opt.effective_end_date ,p_effective_date)
141 and p_effective_date between nvl(pgm.effective_start_date,p_effective_date)
142 and nvl(pgm.effective_end_date ,p_effective_date) ;
143
144
145 Begin
146 --
147 hr_utility.set_location('Entering'||l_proc, 5);
148 --
149 init_detl_globals;
150 --
151 FOR elig IN c_elig(p_person_id) LOOP
152 --
153 l_include := 'Y';
154 --
155
156 ben_ext_evaluate_inclusion.evaluate_eligibility_incl
157 (p_elct_pl_id => elig.pl_id,
158 p_elct_enrt_strt_dt => elig.enrt_cvg_strt_dt,
159 p_elct_yrprd_id => elig.yr_perd_id,
160 p_elct_pgm_id => elig.program_id,
161 p_elct_pl_typ_id => elig.pl_typ_id,
162 p_elct_last_upd_dt => elig.last_update_date,
163 p_elct_per_in_ler_id => elig.per_in_ler_id,
164 p_elct_ler_id => elig.ler_id,
165 p_elct_per_in_ler_stat_cd => elig.per_in_ler_stat_cd,
166 p_elct_lf_evt_ocrd_dt => elig.lf_evt_ocrd_dt,
167 p_elct_ntfn_dt => elig.ntfn_dt,
168 p_prtt_enrt_rslt_id => elig.prtt_enrt_rslt_id,
169 p_effective_date => p_effective_date,
170 p_include => l_include
171 );
172 --
173 IF l_include = 'Y' THEN
174
175 -- assign eligibility info to global variables
176 --
177 ben_ext_person.g_flex_pgm_name := elig.program_name;
178 ben_ext_person.g_flex_pl_name := elig.pl_name;
179 ben_ext_person.g_flex_pl_typ_name := elig.pl_typ_name;
180 ben_ext_person.g_flex_opt_name := elig.opt_name;
181 ben_ext_person.g_flex_cmbn_plip_name := elig.cmbn_plip_name;
182 ben_ext_person.g_flex_cmbn_ptip_name := elig.cmbn_ptip_name;
183 ben_ext_person.g_flex_cmbn_ptip_opt_name := elig.cmbn_ptip_opt_name;
184 ben_ext_person.g_flex_amt := elig.flex_amt;
185 ben_ext_person.g_flex_currency := elig.uom;
186 ben_ext_person.g_flex_opt_id := elig.opt_id;
187 ben_ext_person.g_flex_pgm_id := elig.program_id;
188 ben_ext_person.g_flex_pl_id := elig.pl_id;
189 ben_ext_person.g_flex_pl_typ_id := elig.pl_typ_id;
190 ben_ext_person.g_flex_cmbn_plip_id := elig.cmbn_plip_id;
191 ben_ext_person.g_flex_cmbn_ptip_id := elig.cmbn_ptip_id;
192 ben_ext_person.g_flex_cmbn_ptip_opt_id := elig.cmbn_ptip_opt_id;
193 ben_ext_person.g_flex_bnft_pool_id := elig.pool_id;
194 ben_ext_person.g_flex_bnft_pool_name := elig.pool_name;
195
196 --
197 -- format and write
198 --
199 ben_ext_fmt.process_ext_recs(p_ext_rslt_id => p_ext_rslt_id,
200 p_ext_file_id => p_ext_file_id,
201 p_data_typ_cd => p_data_typ_cd,
202 p_ext_typ_cd => p_ext_typ_cd,
203 p_rcd_typ_cd => 'D', --detail
204 p_low_lvl_cd => 'F', --flex credit?
205 p_person_id => p_person_id,
206 p_chg_evt_cd => null,
207 p_business_group_id => p_business_group_id,
208 p_effective_date => p_effective_date
209 );
210 --
211 END IF;
212
213 END LOOP;
214 --
215 hr_utility.set_location('Exiting'||l_proc, 15);
216 --
217 -- Enter further code below as specified in the Package spec.
218
219 --
220 END;
221 END;