1 package body ben_cop_cache as
2 /* $Header: bencopch.pkb 120.1 2007/03/27 15:52:23 rtagarra noship $ */
3 --
4 g_package varchar2(50) := 'ben_cop_cache.';
5 --
6 procedure bgpcop_getdets
7 (p_effective_date in date
8 ,p_business_group_id in number
9 ,p_pl_id in number default null
10 ,p_opt_id in number default null
11 ,p_eligy_prfl_id in number default null
12 ,p_vrbl_rt_prfl_id in number default null
13 ,p_mode in varchar2
14 --
15 ,p_inst_set out nocopy ben_cop_cache.g_bgpcop_cache
16 )
17 is
18 --
19 l_proc varchar2(72) := g_package||'bgpcop_getdets';
20 --
21 l_row_num pls_integer;
22 --
23 cursor c_noparms
24 (c_effective_date in date
25 ,c_pl_id in number
26 )
27 is
28 select cop.oipl_id,
29 cop.opt_id,
30 cop.drvbl_fctr_prtn_elig_flag,
31 cop.drvbl_fctr_apls_rts_flag,
32 cop.trk_inelig_per_flag
33 from
34 ben_pl_f pln,
35 ben_oipl_f cop
36 -- ben_popl_yr_perd cpy,
37 -- ben_yr_perd yrp
38 where pln.pl_id = c_pl_id
39 and c_effective_date
40 between pln.effective_start_date
41 and pln.effective_end_date
42 and cop.pl_id = pln.pl_id
43 and c_effective_date
44 between cop.effective_start_date
45 and cop.effective_end_date
46 and (p_mode IN ('P','G','D') or
47 exists (select null
48 from ben_popl_yr_perd cpy,
49 ben_yr_perd yrp
50 where cpy.pl_id = pln.pl_id
51 and cpy.yr_perd_id = yrp.yr_perd_id
52 and c_effective_date
53 between yrp.start_date
54 and yrp.end_date))
55 and pln.pl_stat_cd = 'A'
56 and cop.oipl_stat_cd = 'A'
57 order by cop.ordr_num;
58 --
59 cursor c_oipl
60 (c_effective_date in date
61 ,c_pl_id in number
62 ,c_opt_id in number
63 ,c_eligy_prfl_id in number
64 ,c_vrbl_rt_prfl_id in number
65 )
66 is
67 select cop.oipl_id,
68 cop.opt_id,
69 cop.drvbl_fctr_prtn_elig_flag,
70 cop.drvbl_fctr_apls_rts_flag,
71 cop.trk_inelig_per_flag
72 from ben_pl_f pln,
73 ben_oipl_f cop,
74 -- ben_popl_yr_perd cpy,
75 -- ben_yr_perd yrp,
76 ben_opt_f opt
77 where pln.pl_id = c_pl_id
78 and c_effective_date
79 between pln.effective_start_date
80 and pln.effective_end_date
81 and cop.pl_id = pln.pl_id
82 and c_effective_date
83 between cop.effective_start_date
84 and cop.effective_end_date
85 and (p_mode IN ('P','G','D') or
86 exists (select null
87 from ben_popl_yr_perd cpy,
88 ben_yr_perd yrp
89 where cpy.pl_id = pln.pl_id
90 and cpy.yr_perd_id = yrp.yr_perd_id
91 and c_effective_date
92 between yrp.start_date
93 and yrp.end_date))
94 and cop.opt_id = opt.opt_id
95 and c_effective_date
96 between opt.effective_start_date
97 and opt.effective_end_date
98 and pln.pl_stat_cd = 'A'
99 and cop.oipl_stat_cd = 'A'
100 and opt.opt_id = nvl(c_opt_id,opt.opt_id)
101 /* Make sure that option being linked to is of the eligibility profile
102 that has been specified by the user. */
103 and (c_eligy_prfl_id is not null and exists
104 (select null
105 from ben_prtn_elig_f epa2,
106 ben_prtn_elig_prfl_f cep,
107 ben_eligy_prfl_f elp
108 where epa2.oipl_id = cop.oipl_id
109 and epa2.business_group_id = cop.business_group_id
110 and c_effective_date
111 between epa2.effective_start_date
112 and epa2.effective_end_date
113 and cep.prtn_elig_id = epa2.prtn_elig_id
114 and cep.business_group_id = epa2.business_group_id
115 and c_effective_date
116 between cep.effective_start_date
117 and cep.effective_end_date
118 and elp.eligy_prfl_id = cep.eligy_prfl_id
119 and elp.business_group_id = cep.business_group_id
120 and elp.eligy_prfl_id = c_eligy_prfl_id
121 and c_effective_date
122 between elp.effective_start_date
123 and elp.effective_end_date)
124 or c_eligy_prfl_id is null)
125 /* Make sure that plan being linked to is of the variable rate profile
126 that has been specified by the user. */
127 and (c_vrbl_rt_prfl_id is not null and exists
128 (select null
129 from ben_acty_base_rt_f abr,
130 ben_acty_vrbl_rt_f avr,
131 ben_vrbl_rt_prfl_f vpf
132 where abr.oipl_id = cop.oipl_id
133 and abr.business_group_id = pln.business_group_id
134 and c_effective_date
135 between abr.effective_start_date
136 and abr.effective_end_date
137 and avr.acty_base_rt_id = abr.acty_base_rt_id
138 and avr.business_group_id = abr.business_group_id
139 and c_effective_date
140 between avr.effective_start_date
141 and avr.effective_end_date
142 and vpf.vrbl_rt_prfl_id = avr.vrbl_rt_prfl_id
143 and vpf.business_group_id = avr.business_group_id
144 and vpf.vrbl_rt_prfl_id = c_vrbl_rt_prfl_id
145 and c_effective_date
146 between vpf.effective_start_date
147 and vpf.effective_end_date)
148 or c_vrbl_rt_prfl_id is null)
149 order by cop.ordr_num;
150 --
151 begin
152 --
153 l_row_num := 0;
154 --
155 if p_eligy_prfl_id is null
156 and p_vrbl_rt_prfl_id is null
157 and p_opt_id is null
158 then
159 --
160 for obj in c_noparms
161 (c_effective_date => p_effective_date
162 ,c_pl_id => p_pl_id
163 )
164 loop
165 --
166 p_inst_set(l_row_num) := obj;
167 l_row_num := l_row_num+1;
168 --
169 end loop;
170 --
171 else
172 --
173 for obj in c_oipl
174 (c_effective_date => p_effective_date
175 ,c_pl_id => p_pl_id
176 ,c_opt_id => p_opt_id
177 ,c_eligy_prfl_id => p_eligy_prfl_id
178 ,c_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
179 )
180 loop
181 --
182 p_inst_set(l_row_num) := obj;
183 l_row_num := l_row_num+1;
184 --
185 end loop;
186 --
187 end if;
188 --
189 end bgpcop_getdets;
190 --
191 procedure clear_down_cache is
192 --
193 begin
194 --
195 g_eedcop_parlookup.delete;
196 g_eedcop_lookup.delete;
197 g_eedcop_inst.delete;
198 --
199 end clear_down_cache;
200 --
201 end ben_cop_cache;
[Home] [Help]