[Home] [Help]
PACKAGE BODY: APPS.BEN_ROLLUP_RBVS
Source
1 package body ben_rollup_rbvs as
2 /* $Header: benrbvru.pkb 120.0 2005/05/28 09:25:25 appldev noship $ */
3 --
4 g_package varchar2(50) := 'ben_rollup_rbvs.';
5 --
6 PROCEDURE build_rollup_sql_str
7 (p_rt_cd_va in out nocopy benutils.g_v2_150_table
8 ,p_rt_fromstr_va in out nocopy benutils.g_varchar2_table
9 ,p_rt_wherestr_va in out nocopy benutils.g_varchar2_table
10 ,p_rt_sqlstr_va in out nocopy benutils.g_varchar2_table
11 ,p_rt_typcd_va in out nocopy benutils.g_v2_150_table
12 )
13 is
14 --
15 l_rawrt_en pls_integer;
16 l_rawrt_cd_va benutils.g_v2_150_table := benutils.g_v2_150_table();
17 l_rawrt_typcd_va benutils.g_v2_150_table := benutils.g_v2_150_table();
18 l_rawrt_selstr_va benutils.g_varchar2_table := benutils.g_varchar2_table();
19 l_rawrt_fromstr_va benutils.g_varchar2_table := benutils.g_varchar2_table();
20 l_rawrt_sqlstr_va benutils.g_varchar2_table := benutils.g_varchar2_table();
21 l_rawrt_wherestr_va benutils.g_varchar2_table := benutils.g_varchar2_table();
22 --
23 l_rt_en pls_integer;
24 l_rt_sqlstr_va benutils.g_varchar2_table := benutils.g_varchar2_table();
25 l_rt_fromstr_va benutils.g_varchar2_table := benutils.g_varchar2_table();
26 l_rt_wherestr_va benutils.g_varchar2_table := benutils.g_varchar2_table();
27 l_rt_cd_va benutils.g_v2_150_table := benutils.g_v2_150_table();
28 l_rt_typcd_va benutils.g_v2_150_table := benutils.g_v2_150_table();
29 --
30 l_sql_str long;
31 l_sel_str long;
32 --
33 begin
34 --
35 l_rawrt_en := 1;
36 --
37 l_rawrt_cd_va.extend(1);
38 l_rawrt_typcd_va.extend(1);
39 l_rawrt_selstr_va.extend(1);
40 l_rawrt_fromstr_va.extend(1);
41 l_rawrt_wherestr_va.extend(1);
42 l_rawrt_sqlstr_va.extend(1);
43 l_rawrt_cd_va(l_rawrt_en) := 'PPL';
44 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE0';
45 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
46 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
47 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_ptnl_ler_for_per_rbv rbv ';
48 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id ';
49 --
50 l_rawrt_en := l_rawrt_en+1;
51 l_rawrt_cd_va.extend(1);
52 l_rawrt_typcd_va.extend(1);
53 l_rawrt_selstr_va.extend(1);
54 l_rawrt_fromstr_va.extend(1);
55 l_rawrt_wherestr_va.extend(1);
56 l_rawrt_sqlstr_va.extend(1);
57 l_rawrt_cd_va(l_rawrt_en) := 'PIL';
58 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE0';
59 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
60 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
61 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_per_in_ler_rbv rbv ';
62 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id ';
63 --
64 l_rawrt_en := l_rawrt_en+1;
65 l_rawrt_cd_va.extend(1);
66 l_rawrt_typcd_va.extend(1);
67 l_rawrt_selstr_va.extend(1);
68 l_rawrt_fromstr_va.extend(1);
69 l_rawrt_wherestr_va.extend(1);
70 l_rawrt_sqlstr_va.extend(1);
71 l_rawrt_cd_va(l_rawrt_en) := 'PILBCKDT';
72 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
73 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
74 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
75 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_per_in_ler_rbv rbv ';
76 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
77 ||' and rbv.PER_IN_LER_STAT_CD = '||''''||'BCKDT'||'''';
78 --
79 l_rawrt_en := l_rawrt_en+1;
80 l_rawrt_cd_va.extend(1);
81 l_rawrt_typcd_va.extend(1);
82 l_rawrt_selstr_va.extend(1);
83 l_rawrt_fromstr_va.extend(1);
84 l_rawrt_wherestr_va.extend(1);
85 l_rawrt_sqlstr_va.extend(1);
86 l_rawrt_cd_va(l_rawrt_en) := 'PILSTRTD';
87 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
88 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
89 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
90 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_per_in_ler_rbv rbv ';
91 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
92 ||' and rbv.PER_IN_LER_STAT_CD = '||''''||'STRTD'||'''';
93 --
94 l_rawrt_en := l_rawrt_en+1;
95 l_rawrt_cd_va.extend(1);
96 l_rawrt_typcd_va.extend(1);
97 l_rawrt_selstr_va.extend(1);
98 l_rawrt_fromstr_va.extend(1);
99 l_rawrt_wherestr_va.extend(1);
100 l_rawrt_sqlstr_va.extend(1);
101 l_rawrt_cd_va(l_rawrt_en) := 'PILVOIDD';
102 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
103 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
104 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
105 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_per_in_ler_rbv rbv ';
106 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
107 ||' and rbv.PER_IN_LER_STAT_CD = '||''''||'VOIDD'||'''';
108 --
109 l_rawrt_en := l_rawrt_en+1;
110 l_rawrt_cd_va.extend(1);
111 l_rawrt_typcd_va.extend(1);
112 l_rawrt_selstr_va.extend(1);
113 l_rawrt_fromstr_va.extend(1);
114 l_rawrt_wherestr_va.extend(1);
115 l_rawrt_sqlstr_va.extend(1);
116 l_rawrt_cd_va(l_rawrt_en) := 'PILPROCD';
117 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
118 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
119 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
120 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_per_in_ler_rbv rbv ';
121 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
122 ||' and rbv.PER_IN_LER_STAT_CD = '||''''||'PROCD'||'''';
123 --
124 l_rawrt_en := l_rawrt_en+1;
125 l_rawrt_cd_va.extend(1);
126 l_rawrt_typcd_va.extend(1);
127 l_rawrt_selstr_va.extend(1);
128 l_rawrt_fromstr_va.extend(1);
129 l_rawrt_wherestr_va.extend(1);
130 l_rawrt_sqlstr_va.extend(1);
131 l_rawrt_cd_va(l_rawrt_en) := 'PILLTSCHEDDU';
132 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
133 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
134 ||' nvl(sum(nvl(rbv.object_version_number,0)),0) sum_ovn ';
135 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_per_in_ler_rbv rbv, ben_ler_f ler ';
136 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
137 ||' and rbv.ler_id = ler.ler_id '
138 ||' and ler.typ_cd = '||''''||'SCHEDDU'||'''';
139 --
140 l_rawrt_en := l_rawrt_en+1;
141 l_rawrt_cd_va.extend(1);
142 l_rawrt_typcd_va.extend(1);
143 l_rawrt_selstr_va.extend(1);
144 l_rawrt_fromstr_va.extend(1);
145 l_rawrt_wherestr_va.extend(1);
146 l_rawrt_sqlstr_va.extend(1);
147 l_rawrt_cd_va(l_rawrt_en) := 'CRP';
148 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE0';
149 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
150 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
151 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_cbr_per_in_ler_rbv rbv ';
152 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id ';
153 --
154 l_rawrt_en := l_rawrt_en+1;
155 l_rawrt_cd_va.extend(1);
156 l_rawrt_typcd_va.extend(1);
157 l_rawrt_selstr_va.extend(1);
158 l_rawrt_fromstr_va.extend(1);
159 l_rawrt_wherestr_va.extend(1);
160 l_rawrt_sqlstr_va.extend(1);
161 l_rawrt_cd_va(l_rawrt_en) := 'PEP';
162 l_rawrt_typcd_va(l_rawrt_en) := 'DTTABLE';
163 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
164 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
165 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
166 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id ';
167 --
168 l_rawrt_en := l_rawrt_en+1;
169 l_rawrt_cd_va.extend(1);
170 l_rawrt_typcd_va.extend(1);
171 l_rawrt_selstr_va.extend(1);
172 l_rawrt_fromstr_va.extend(1);
173 l_rawrt_wherestr_va.extend(1);
174 l_rawrt_sqlstr_va.extend(1);
175 l_rawrt_cd_va(l_rawrt_en) := 'PEPNULLPINL';
176 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
177 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
178 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
179 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
180 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
181 ||' and rbv.per_in_ler_id is null ';
182 --
183 l_rawrt_en := l_rawrt_en+1;
184 l_rawrt_cd_va.extend(1);
185 l_rawrt_typcd_va.extend(1);
186 l_rawrt_selstr_va.extend(1);
187 l_rawrt_fromstr_va.extend(1);
188 l_rawrt_wherestr_va.extend(1);
189 l_rawrt_sqlstr_va.extend(1);
190 l_rawrt_cd_va(l_rawrt_en) := 'PEPIE';
191 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
192 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
193 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
194 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
195 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
196 ||' and rbv.elig_flag = '||''''||'N'||'''';
197 --
198 l_rawrt_en := l_rawrt_en+1;
199 l_rawrt_cd_va.extend(1);
200 l_rawrt_typcd_va.extend(1);
201 l_rawrt_selstr_va.extend(1);
202 l_rawrt_fromstr_va.extend(1);
203 l_rawrt_wherestr_va.extend(1);
204 l_rawrt_sqlstr_va.extend(1);
205 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEAGE';
206 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
207 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
208 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
209 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
210 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
211 ||' and rbv.inelg_rsn_cd = '||''''||'AGE'||'''';
212 --
213 l_rawrt_en := l_rawrt_en+1;
214 l_rawrt_cd_va.extend(1);
215 l_rawrt_typcd_va.extend(1);
216 l_rawrt_selstr_va.extend(1);
217 l_rawrt_fromstr_va.extend(1);
218 l_rawrt_wherestr_va.extend(1);
219 l_rawrt_sqlstr_va.extend(1);
220 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEAGL';
221 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
222 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
223 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
224 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
225 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
226 ||' and rbv.inelg_rsn_cd = '||''''||'AGL'||'''';
227 --
228 l_rawrt_en := l_rawrt_en+1;
229 l_rawrt_cd_va.extend(1);
230 l_rawrt_typcd_va.extend(1);
231 l_rawrt_selstr_va.extend(1);
232 l_rawrt_fromstr_va.extend(1);
233 l_rawrt_wherestr_va.extend(1);
234 l_rawrt_sqlstr_va.extend(1);
235 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEAST';
236 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
237 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
238 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
239 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
240 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
241 ||' and rbv.inelg_rsn_cd = '||''''||'AST'||'''';
242 --
243 l_rawrt_en := l_rawrt_en+1;
244 l_rawrt_cd_va.extend(1);
245 l_rawrt_typcd_va.extend(1);
246 l_rawrt_selstr_va.extend(1);
247 l_rawrt_fromstr_va.extend(1);
248 l_rawrt_wherestr_va.extend(1);
249 l_rawrt_sqlstr_va.extend(1);
250 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEBGR';
251 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
252 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
253 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
254 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
255 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
256 ||' and rbv.inelg_rsn_cd = '||''''||'BGR'||'''';
257 --
258 l_rawrt_en := l_rawrt_en+1;
259 l_rawrt_cd_va.extend(1);
260 l_rawrt_typcd_va.extend(1);
261 l_rawrt_selstr_va.extend(1);
262 l_rawrt_fromstr_va.extend(1);
263 l_rawrt_wherestr_va.extend(1);
264 l_rawrt_sqlstr_va.extend(1);
265 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEBRG';
266 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
267 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
268 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
269 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
270 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
271 ||' and rbv.inelg_rsn_cd = '||''''||'BRG'||'''';
272 --
273 l_rawrt_en := l_rawrt_en+1;
274 l_rawrt_cd_va.extend(1);
275 l_rawrt_typcd_va.extend(1);
276 l_rawrt_selstr_va.extend(1);
277 l_rawrt_fromstr_va.extend(1);
278 l_rawrt_wherestr_va.extend(1);
279 l_rawrt_sqlstr_va.extend(1);
280 l_rawrt_cd_va(l_rawrt_en) := 'PEPIECMP';
281 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
282 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
283 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
284 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
285 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
286 ||' and rbv.inelg_rsn_cd = '||''''||'CMP'||'''';
287 --
288 l_rawrt_en := l_rawrt_en+1;
289 l_rawrt_cd_va.extend(1);
290 l_rawrt_typcd_va.extend(1);
291 l_rawrt_selstr_va.extend(1);
292 l_rawrt_fromstr_va.extend(1);
293 l_rawrt_wherestr_va.extend(1);
294 l_rawrt_sqlstr_va.extend(1);
295 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEDSB';
296 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
297 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
298 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
299 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
300 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
301 ||' and rbv.inelg_rsn_cd = '||''''||'DSB'||'''';
302 --
303 l_rawrt_en := l_rawrt_en+1;
304 l_rawrt_cd_va.extend(1);
305 l_rawrt_typcd_va.extend(1);
306 l_rawrt_selstr_va.extend(1);
307 l_rawrt_fromstr_va.extend(1);
308 l_rawrt_wherestr_va.extend(1);
309 l_rawrt_sqlstr_va.extend(1);
310 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEEAI';
311 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
312 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
313 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
314 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
315 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
316 ||' and rbv.inelg_rsn_cd = '||''''||'EAI'||'''';
317 --
318 l_rawrt_en := l_rawrt_en+1;
319 l_rawrt_cd_va.extend(1);
320 l_rawrt_typcd_va.extend(1);
321 l_rawrt_selstr_va.extend(1);
322 l_rawrt_fromstr_va.extend(1);
323 l_rawrt_wherestr_va.extend(1);
324 l_rawrt_sqlstr_va.extend(1);
325 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEECQ';
326 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
327 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
328 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
329 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
330 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
331 ||' and rbv.inelg_rsn_cd = '||''''||'ECQ'||'''';
332 --
333 l_rawrt_en := l_rawrt_en+1;
334 l_rawrt_cd_va.extend(1);
335 l_rawrt_typcd_va.extend(1);
336 l_rawrt_selstr_va.extend(1);
337 l_rawrt_fromstr_va.extend(1);
338 l_rawrt_wherestr_va.extend(1);
339 l_rawrt_sqlstr_va.extend(1);
340 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEEDT';
341 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
342 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
343 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
344 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
345 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
346 ||' and rbv.inelg_rsn_cd = '||''''||'EDT'||'''';
347 --
348 l_rawrt_en := l_rawrt_en+1;
349 l_rawrt_cd_va.extend(1);
350 l_rawrt_typcd_va.extend(1);
351 l_rawrt_selstr_va.extend(1);
352 l_rawrt_fromstr_va.extend(1);
353 l_rawrt_wherestr_va.extend(1);
354 l_rawrt_sqlstr_va.extend(1);
355 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEEET';
356 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
357 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
358 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
359 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
360 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
361 ||' and rbv.inelg_rsn_cd = '||''''||'EET'||'''';
362 --
363 l_rawrt_en := l_rawrt_en+1;
364 l_rawrt_cd_va.extend(1);
365 l_rawrt_typcd_va.extend(1);
366 l_rawrt_selstr_va.extend(1);
367 l_rawrt_fromstr_va.extend(1);
368 l_rawrt_wherestr_va.extend(1);
369 l_rawrt_sqlstr_va.extend(1);
370 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEEGN';
371 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
372 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
373 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
374 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
375 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
376 ||' and rbv.inelg_rsn_cd = '||''''||'EGN'||'''';
377 --
378 l_rawrt_en := l_rawrt_en+1;
379 l_rawrt_cd_va.extend(1);
380 l_rawrt_typcd_va.extend(1);
381 l_rawrt_selstr_va.extend(1);
382 l_rawrt_fromstr_va.extend(1);
383 l_rawrt_wherestr_va.extend(1);
384 l_rawrt_sqlstr_va.extend(1);
385 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEEHC';
386 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
387 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
388 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
389 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
390 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
391 ||' and rbv.inelg_rsn_cd = '||''''||'EHC'||'''';
392
393
394 --
395 l_rawrt_en := l_rawrt_en+1;
396 l_rawrt_cd_va.extend(1);
397 l_rawrt_typcd_va.extend(1);
398 l_rawrt_selstr_va.extend(1);
399 l_rawrt_fromstr_va.extend(1);
400 l_rawrt_wherestr_va.extend(1);
401 l_rawrt_sqlstr_va.extend(1);
402 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEEOP';
403 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
404 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
405 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
406 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
407 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
408 ||' and rbv.inelg_rsn_cd = '||''''||'EOP'||'''';
409
410
411 --
412 l_rawrt_en := l_rawrt_en+1;
413 l_rawrt_cd_va.extend(1);
414 l_rawrt_typcd_va.extend(1);
415 l_rawrt_selstr_va.extend(1);
416 l_rawrt_fromstr_va.extend(1);
417 l_rawrt_wherestr_va.extend(1);
418 l_rawrt_sqlstr_va.extend(1);
419 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEEOY';
420 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
421 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
425 ||' and rbv.inelg_rsn_cd = '||''''||'EOY'||'''';
422 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
423 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
424 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
426 --
427 l_rawrt_en := l_rawrt_en+1;
428 l_rawrt_cd_va.extend(1);
429 l_rawrt_typcd_va.extend(1);
430 l_rawrt_selstr_va.extend(1);
431 l_rawrt_fromstr_va.extend(1);
432 l_rawrt_wherestr_va.extend(1);
433 l_rawrt_sqlstr_va.extend(1);
434 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEEPS';
435 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
436 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
437 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
438 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
439 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
440 ||' and rbv.inelg_rsn_cd = '||''''||'EPS'||'''';
441 --
442 l_rawrt_en := l_rawrt_en+1;
443 l_rawrt_cd_va.extend(1);
444 l_rawrt_typcd_va.extend(1);
445 l_rawrt_selstr_va.extend(1);
446 l_rawrt_fromstr_va.extend(1);
447 l_rawrt_wherestr_va.extend(1);
448 l_rawrt_sqlstr_va.extend(1);
449 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEEQG';
450 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
451 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
452 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
453 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
454 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
455 ||' and rbv.inelg_rsn_cd = '||''''||'EQG'||'''';
456 --
457 l_rawrt_en := l_rawrt_en+1;
458 l_rawrt_cd_va.extend(1);
459 l_rawrt_typcd_va.extend(1);
460 l_rawrt_selstr_va.extend(1);
461 l_rawrt_fromstr_va.extend(1);
462 l_rawrt_wherestr_va.extend(1);
463 l_rawrt_sqlstr_va.extend(1);
464 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEEQT';
465 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
466 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
467 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
468 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
469 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
470 ||' and rbv.inelg_rsn_cd = '||''''||'EQT'||'''';
471 --
472 l_rawrt_en := l_rawrt_en+1;
473 l_rawrt_cd_va.extend(1);
474 l_rawrt_typcd_va.extend(1);
475 l_rawrt_selstr_va.extend(1);
476 l_rawrt_fromstr_va.extend(1);
477 l_rawrt_wherestr_va.extend(1);
478 l_rawrt_sqlstr_va.extend(1);
479 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEERG';
480 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
481 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
482 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
483 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
484 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
485 ||' and rbv.inelg_rsn_cd = '||''''||'ERG'||'''';
486 --
487 l_rawrt_en := l_rawrt_en+1;
488 l_rawrt_cd_va.extend(1);
489 l_rawrt_typcd_va.extend(1);
490 l_rawrt_selstr_va.extend(1);
491 l_rawrt_fromstr_va.extend(1);
492 l_rawrt_wherestr_va.extend(1);
493 l_rawrt_sqlstr_va.extend(1);
494 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEERL';
495 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
496 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
497 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
498 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
499 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
500 ||' and rbv.inelg_rsn_cd = '||''''||'ERL'||'''';
501 --
502 l_rawrt_en := l_rawrt_en+1;
503 l_rawrt_cd_va.extend(1);
504 l_rawrt_typcd_va.extend(1);
505 l_rawrt_selstr_va.extend(1);
506 l_rawrt_fromstr_va.extend(1);
507 l_rawrt_wherestr_va.extend(1);
508 l_rawrt_sqlstr_va.extend(1);
509 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEETC';
510 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
511 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
512 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
513 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
514 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
515 ||' and rbv.inelg_rsn_cd = '||''''||'ETC'||'''';
516 --
517 l_rawrt_en := l_rawrt_en+1;
518 l_rawrt_cd_va.extend(1);
519 l_rawrt_typcd_va.extend(1);
520 l_rawrt_selstr_va.extend(1);
521 l_rawrt_fromstr_va.extend(1);
522 l_rawrt_wherestr_va.extend(1);
523 l_rawrt_sqlstr_va.extend(1);
524 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEETD';
525 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
526 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
527 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
528 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
529 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
530 ||' and rbv.inelg_rsn_cd = '||''''||'ETD'||'''';
531 --
532 l_rawrt_en := l_rawrt_en+1;
533 l_rawrt_cd_va.extend(1);
534 l_rawrt_typcd_va.extend(1);
535 l_rawrt_selstr_va.extend(1);
536 l_rawrt_fromstr_va.extend(1);
537 l_rawrt_wherestr_va.extend(1);
541 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
538 l_rawrt_sqlstr_va.extend(1);
539 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEETP';
540 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
542 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
543 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
544 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
545 ||' and rbv.inelg_rsn_cd = '||''''||'ETP'||'''';
546 --
547 l_rawrt_en := l_rawrt_en+1;
548 l_rawrt_cd_va.extend(1);
549 l_rawrt_typcd_va.extend(1);
550 l_rawrt_selstr_va.extend(1);
551 l_rawrt_fromstr_va.extend(1);
552 l_rawrt_wherestr_va.extend(1);
553 l_rawrt_sqlstr_va.extend(1);
554 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEETU';
555 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
556 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
557 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
558 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
559 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
560 ||' and rbv.inelg_rsn_cd = '||''''||'ETU'||'''';
561 --
562 l_rawrt_en := l_rawrt_en+1;
563 l_rawrt_cd_va.extend(1);
564 l_rawrt_typcd_va.extend(1);
565 l_rawrt_selstr_va.extend(1);
566 l_rawrt_fromstr_va.extend(1);
567 l_rawrt_wherestr_va.extend(1);
568 l_rawrt_sqlstr_va.extend(1);
569 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEEVT';
570 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
571 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
572 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
573 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
574 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
575 ||' and rbv.inelg_rsn_cd = '||''''||'EVT'||'''';
576 --
577 l_rawrt_en := l_rawrt_en+1;
578 l_rawrt_cd_va.extend(1);
579 l_rawrt_typcd_va.extend(1);
580 l_rawrt_selstr_va.extend(1);
581 l_rawrt_fromstr_va.extend(1);
582 l_rawrt_wherestr_va.extend(1);
583 l_rawrt_sqlstr_va.extend(1);
584 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEFPT';
585 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
586 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
587 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
588 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
589 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
590 ||' and rbv.inelg_rsn_cd = '||''''||'FPT'||'''';
591 --
592 l_rawrt_en := l_rawrt_en+1;
593 l_rawrt_cd_va.extend(1);
594 l_rawrt_typcd_va.extend(1);
595 l_rawrt_selstr_va.extend(1);
596 l_rawrt_fromstr_va.extend(1);
597 l_rawrt_wherestr_va.extend(1);
598 l_rawrt_sqlstr_va.extend(1);
599 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEGRD';
600 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
601 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
602 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
603 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
604 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
605 ||' and rbv.inelg_rsn_cd = '||''''||'GRD'||'''';
606 --
607 l_rawrt_en := l_rawrt_en+1;
608 l_rawrt_cd_va.extend(1);
609 l_rawrt_typcd_va.extend(1);
610 l_rawrt_selstr_va.extend(1);
611 l_rawrt_fromstr_va.extend(1);
612 l_rawrt_wherestr_va.extend(1);
613 l_rawrt_sqlstr_va.extend(1);
614 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEHRS';
615 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
616 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
617 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
618 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
619 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
620 ||' and rbv.inelg_rsn_cd = '||''''||'HRS'||'''';
621 --
622 l_rawrt_en := l_rawrt_en+1;
623 l_rawrt_cd_va.extend(1);
624 l_rawrt_typcd_va.extend(1);
625 l_rawrt_selstr_va.extend(1);
626 l_rawrt_fromstr_va.extend(1);
627 l_rawrt_wherestr_va.extend(1);
628 l_rawrt_sqlstr_va.extend(1);
629 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEJOB';
630 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
631 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
632 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
633 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
634 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
635 ||' and rbv.inelg_rsn_cd = '||''''||'JOB'||'''';
636 --
637 l_rawrt_en := l_rawrt_en+1;
638 l_rawrt_cd_va.extend(1);
639 l_rawrt_typcd_va.extend(1);
640 l_rawrt_selstr_va.extend(1);
641 l_rawrt_fromstr_va.extend(1);
642 l_rawrt_wherestr_va.extend(1);
643 l_rawrt_sqlstr_va.extend(1);
644 l_rawrt_cd_va(l_rawrt_en) := 'PEPIELBR';
645 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
646 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
647 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
648 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
649 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
650 ||' and rbv.inelg_rsn_cd = '||''''||'LBR'||'''';
651 --
652 l_rawrt_en := l_rawrt_en+1;
656 l_rawrt_fromstr_va.extend(1);
653 l_rawrt_cd_va.extend(1);
654 l_rawrt_typcd_va.extend(1);
655 l_rawrt_selstr_va.extend(1);
657 l_rawrt_wherestr_va.extend(1);
658 l_rawrt_sqlstr_va.extend(1);
659 l_rawrt_cd_va(l_rawrt_en) := 'PEPIELOA';
660 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
661 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
662 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
663 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
664 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
665 ||' and rbv.inelg_rsn_cd = '||''''||'LOA'||'''';
666 --
667 l_rawrt_en := l_rawrt_en+1;
668 l_rawrt_cd_va.extend(1);
669 l_rawrt_typcd_va.extend(1);
670 l_rawrt_selstr_va.extend(1);
671 l_rawrt_fromstr_va.extend(1);
672 l_rawrt_wherestr_va.extend(1);
673 l_rawrt_sqlstr_va.extend(1);
674 l_rawrt_cd_va(l_rawrt_en) := 'PEPIELOS';
675 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
676 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
677 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
678 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
679 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
680 ||' and rbv.inelg_rsn_cd = '||''''||'LOS'||'''';
681 --
682 l_rawrt_en := l_rawrt_en+1;
683 l_rawrt_cd_va.extend(1);
684 l_rawrt_typcd_va.extend(1);
685 l_rawrt_selstr_va.extend(1);
686 l_rawrt_fromstr_va.extend(1);
687 l_rawrt_wherestr_va.extend(1);
688 l_rawrt_sqlstr_va.extend(1);
689 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEORG';
690 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
691 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
692 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
693 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
694 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
695 ||' and rbv.inelg_rsn_cd = '||''''||'ORG'||'''';
696 --
697 l_rawrt_en := l_rawrt_en+1;
698 l_rawrt_cd_va.extend(1);
699 l_rawrt_typcd_va.extend(1);
700 l_rawrt_selstr_va.extend(1);
701 l_rawrt_fromstr_va.extend(1);
702 l_rawrt_wherestr_va.extend(1);
703 l_rawrt_sqlstr_va.extend(1);
704 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEPEO';
705 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
706 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
707 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
708 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
709 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
710 ||' and rbv.inelg_rsn_cd = '||''''||'PEO'||'''';
711 --
712 l_rawrt_en := l_rawrt_en+1;
713 l_rawrt_cd_va.extend(1);
714 l_rawrt_typcd_va.extend(1);
715 l_rawrt_selstr_va.extend(1);
716 l_rawrt_fromstr_va.extend(1);
717 l_rawrt_wherestr_va.extend(1);
718 l_rawrt_sqlstr_va.extend(1);
719 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEPFQ';
720 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
721 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
722 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
723 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
724 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
725 ||' and rbv.inelg_rsn_cd = '||''''||'PFQ'||'''';
726 --
727 l_rawrt_en := l_rawrt_en+1;
728 l_rawrt_cd_va.extend(1);
729 l_rawrt_typcd_va.extend(1);
730 l_rawrt_selstr_va.extend(1);
731 l_rawrt_fromstr_va.extend(1);
732 l_rawrt_wherestr_va.extend(1);
733 l_rawrt_sqlstr_va.extend(1);
734 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEPFT';
735 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
736 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
737 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
738 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
739 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
740 ||' and rbv.inelg_rsn_cd = '||''''||'PFT'||'''';
741 --
742 l_rawrt_en := l_rawrt_en+1;
743 l_rawrt_cd_va.extend(1);
744 l_rawrt_typcd_va.extend(1);
745 l_rawrt_selstr_va.extend(1);
746 l_rawrt_fromstr_va.extend(1);
747 l_rawrt_wherestr_va.extend(1);
748 l_rawrt_sqlstr_va.extend(1);
749 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEPTP';
750 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
751 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
752 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
753 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
754 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
755 ||' and rbv.inelg_rsn_cd = '||''''||'PTP'||'''';
756 --
757 l_rawrt_en := l_rawrt_en+1;
758 l_rawrt_cd_va.extend(1);
759 l_rawrt_typcd_va.extend(1);
760 l_rawrt_selstr_va.extend(1);
761 l_rawrt_fromstr_va.extend(1);
762 l_rawrt_wherestr_va.extend(1);
763 l_rawrt_sqlstr_va.extend(1);
764 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEPYR';
765 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
766 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
767 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
768 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
772 l_rawrt_en := l_rawrt_en+1;
769 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
770 ||' and rbv.inelg_rsn_cd = '||''''||'PYR'||'''';
771 --
773 l_rawrt_cd_va.extend(1);
774 l_rawrt_typcd_va.extend(1);
775 l_rawrt_selstr_va.extend(1);
776 l_rawrt_fromstr_va.extend(1);
777 l_rawrt_wherestr_va.extend(1);
778 l_rawrt_sqlstr_va.extend(1);
779 l_rawrt_cd_va(l_rawrt_en) := 'PEPIESHR';
780 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
781 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
782 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
783 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
784 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
785 ||' and rbv.inelg_rsn_cd = '||''''||'SHR'||'''';
786 --
787 l_rawrt_en := l_rawrt_en+1;
788 l_rawrt_cd_va.extend(1);
789 l_rawrt_typcd_va.extend(1);
790 l_rawrt_selstr_va.extend(1);
791 l_rawrt_fromstr_va.extend(1);
792 l_rawrt_wherestr_va.extend(1);
793 l_rawrt_sqlstr_va.extend(1);
794 l_rawrt_cd_va(l_rawrt_en) := 'PEPIESTA';
795 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
796 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
797 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
798 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
799 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
800 ||' and rbv.inelg_rsn_cd = '||''''||'STA'||'''';
801 --
802 l_rawrt_en := l_rawrt_en+1;
803 l_rawrt_cd_va.extend(1);
804 l_rawrt_typcd_va.extend(1);
805 l_rawrt_selstr_va.extend(1);
806 l_rawrt_fromstr_va.extend(1);
807 l_rawrt_wherestr_va.extend(1);
808 l_rawrt_sqlstr_va.extend(1);
809 l_rawrt_cd_va(l_rawrt_en) := 'PEPIESVC';
810 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
811 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
812 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
813 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
814 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
815 ||' and rbv.inelg_rsn_cd = '||''''||'SVC'||'''';
816 --
817 l_rawrt_en := l_rawrt_en+1;
818 l_rawrt_cd_va.extend(1);
819 l_rawrt_typcd_va.extend(1);
820 l_rawrt_selstr_va.extend(1);
821 l_rawrt_fromstr_va.extend(1);
822 l_rawrt_wherestr_va.extend(1);
823 l_rawrt_sqlstr_va.extend(1);
824 l_rawrt_cd_va(l_rawrt_en) := 'PEPIEZIP';
825 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
826 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
827 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
828 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
829 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
830 ||' and rbv.inelg_rsn_cd = '||''''||'ZIP'||'''';
831 --
832 l_rawrt_en := l_rawrt_en+1;
833 l_rawrt_cd_va.extend(1);
834 l_rawrt_typcd_va.extend(1);
835 l_rawrt_selstr_va.extend(1);
836 l_rawrt_fromstr_va.extend(1);
837 l_rawrt_wherestr_va.extend(1);
838 l_rawrt_sqlstr_va.extend(1);
839 l_rawrt_cd_va(l_rawrt_en) := 'PEPWTPRD';
840 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
841 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
842 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
843 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
844 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
845 ||' and (rbv.wait_perd_cmpltn_dt is not null '
846 ||' or rbv.wait_perd_strt_dt is not null) ';
847 --
848 l_rawrt_en := l_rawrt_en+1;
849 l_rawrt_cd_va.extend(1);
850 l_rawrt_typcd_va.extend(1);
851 l_rawrt_selstr_va.extend(1);
852 l_rawrt_fromstr_va.extend(1);
853 l_rawrt_wherestr_va.extend(1);
854 l_rawrt_sqlstr_va.extend(1);
855 l_rawrt_cd_va(l_rawrt_en) := 'PEPDFCT';
856 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
857 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
858 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
859 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
860 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
861 ||' and (rbv.comp_ref_amt is not null '
862 ||' or rbv.cmbn_age_n_los_val is not null '
863 ||' or rbv.age_val is not null '
864 ||' or rbv.los_val is not null '
865 ||' or rbv.hrs_wkd_val is not null '
866 ||' or rbv.pct_fl_tm_val is not null) ';
867 --
868 l_rawrt_en := l_rawrt_en+1;
869 l_rawrt_cd_va.extend(1);
870 l_rawrt_typcd_va.extend(1);
871 l_rawrt_selstr_va.extend(1);
872 l_rawrt_fromstr_va.extend(1);
873 l_rawrt_wherestr_va.extend(1);
874 l_rawrt_sqlstr_va.extend(1);
875 l_rawrt_cd_va(l_rawrt_en) := 'PEPDFCTRT';
876 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
877 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
878 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
879 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
880 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
884 ||' or rbv.rt_los_val is not null '
881 ||' and (rbv.rt_comp_ref_amt is not null '
882 ||' or rbv.rt_cmbn_age_n_los_val is not null '
883 ||' or rbv.rt_age_val is not null '
885 ||' or rbv.rt_hrs_wkd_val is not null '
886 ||' or rbv.rt_pct_fl_tm_val is not null) ';
887 --
888 l_rawrt_en := l_rawrt_en+1;
889 l_rawrt_cd_va.extend(1);
890 l_rawrt_typcd_va.extend(1);
891 l_rawrt_selstr_va.extend(1);
892 l_rawrt_fromstr_va.extend(1);
893 l_rawrt_wherestr_va.extend(1);
894 l_rawrt_sqlstr_va.extend(1);
895 l_rawrt_cd_va(l_rawrt_en) := 'PEPPGM';
896 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
897 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
898 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
899 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
900 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
901 ||' and rbv.pgm_id is not null '
902 ||' and rbv.ptip_id is null '
903 ||' and rbv.pl_id is null '
904 ||' and rbv.plip_id is null ';
905 --
906 l_rawrt_en := l_rawrt_en+1;
907 l_rawrt_cd_va.extend(1);
908 l_rawrt_typcd_va.extend(1);
909 l_rawrt_selstr_va.extend(1);
910 l_rawrt_fromstr_va.extend(1);
911 l_rawrt_wherestr_va.extend(1);
912 l_rawrt_sqlstr_va.extend(1);
913 l_rawrt_cd_va(l_rawrt_en) := 'PEPPTIP';
914 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
915 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
916 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
917 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
918 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
919 ||' and rbv.pgm_id is not null '
920 ||' and rbv.ptip_id is not null '
921 ||' and rbv.pl_id is null '
922 ||' and rbv.plip_id is null ';
923 --
924 l_rawrt_en := l_rawrt_en+1;
925 l_rawrt_cd_va.extend(1);
926 l_rawrt_typcd_va.extend(1);
927 l_rawrt_selstr_va.extend(1);
928 l_rawrt_fromstr_va.extend(1);
929 l_rawrt_wherestr_va.extend(1);
930 l_rawrt_sqlstr_va.extend(1);
931 l_rawrt_cd_va(l_rawrt_en) := 'PEPPLN';
932 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
933 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
934 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
935 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
936 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
937 ||' and rbv.pgm_id is not null '
938 ||' and rbv.ptip_id is null '
939 ||' and rbv.pl_id is not null '
940 ||' and rbv.plip_id is null ';
941 --
942 l_rawrt_en := l_rawrt_en+1;
943 l_rawrt_cd_va.extend(1);
944 l_rawrt_typcd_va.extend(1);
945 l_rawrt_selstr_va.extend(1);
946 l_rawrt_fromstr_va.extend(1);
947 l_rawrt_wherestr_va.extend(1);
948 l_rawrt_sqlstr_va.extend(1);
949 l_rawrt_cd_va(l_rawrt_en) := 'PEPPLNIP';
950 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
951 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
952 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
953 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
954 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
955 ||' and rbv.pgm_id is null '
956 ||' and rbv.ptip_id is null '
957 ||' and rbv.pl_id is not null '
958 ||' and rbv.plip_id is null ';
959 --
960 l_rawrt_en := l_rawrt_en+1;
961 l_rawrt_cd_va.extend(1);
962 l_rawrt_typcd_va.extend(1);
963 l_rawrt_selstr_va.extend(1);
964 l_rawrt_fromstr_va.extend(1);
965 l_rawrt_wherestr_va.extend(1);
966 l_rawrt_sqlstr_va.extend(1);
967 l_rawrt_cd_va(l_rawrt_en) := 'PEPPLIP';
968 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
969 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
970 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
971 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_f_rbv rbv ';
972 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
973 ||' and rbv.pgm_id is not null '
974 ||' and rbv.ptip_id is null '
975 ||' and rbv.pl_id is null '
976 ||' and rbv.plip_id is not null ';
977 --
978 l_rawrt_en := l_rawrt_en+1;
979 l_rawrt_cd_va.extend(1);
980 l_rawrt_typcd_va.extend(1);
981 l_rawrt_selstr_va.extend(1);
982 l_rawrt_fromstr_va.extend(1);
983 l_rawrt_wherestr_va.extend(1);
984 l_rawrt_sqlstr_va.extend(1);
985 l_rawrt_cd_va(l_rawrt_en) := 'EPO';
986 l_rawrt_typcd_va(l_rawrt_en) := 'DTTABLE';
987 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
988 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
989 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_opt_f_rbv rbv ';
990 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id ';
991 --
995 l_rawrt_selstr_va.extend(1);
992 l_rawrt_en := l_rawrt_en+1;
993 l_rawrt_cd_va.extend(1);
994 l_rawrt_typcd_va.extend(1);
996 l_rawrt_fromstr_va.extend(1);
997 l_rawrt_wherestr_va.extend(1);
998 l_rawrt_sqlstr_va.extend(1);
999 l_rawrt_cd_va(l_rawrt_en) := 'EPONULLPINL';
1000 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1001 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1002 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1003 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_opt_f_rbv rbv ';
1004 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1005 ||' and rbv.per_in_ler_id is null';
1006 --
1007 l_rawrt_en := l_rawrt_en+1;
1008 l_rawrt_cd_va.extend(1);
1009 l_rawrt_typcd_va.extend(1);
1010 l_rawrt_selstr_va.extend(1);
1011 l_rawrt_fromstr_va.extend(1);
1012 l_rawrt_wherestr_va.extend(1);
1013 l_rawrt_sqlstr_va.extend(1);
1014 l_rawrt_cd_va(l_rawrt_en) := 'EPOIE';
1015 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1016 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1017 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1018 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_opt_f_rbv rbv ';
1019 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1020 ||' and rbv.elig_flag = '||''''||'N'||'''';
1021 --
1022 l_rawrt_en := l_rawrt_en+1;
1023 l_rawrt_cd_va.extend(1);
1024 l_rawrt_typcd_va.extend(1);
1025 l_rawrt_selstr_va.extend(1);
1026 l_rawrt_fromstr_va.extend(1);
1027 l_rawrt_wherestr_va.extend(1);
1028 l_rawrt_sqlstr_va.extend(1);
1029 l_rawrt_cd_va(l_rawrt_en) := 'EPOWTPRD';
1030 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1031 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1032 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1033 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_opt_f_rbv rbv ';
1034 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1035 ||' and (rbv.wait_perd_cmpltn_dt is not null '
1036 ||' or rbv.wait_perd_strt_dt is not null) ';
1037 --
1038 l_rawrt_en := l_rawrt_en+1;
1039 l_rawrt_cd_va.extend(1);
1040 l_rawrt_typcd_va.extend(1);
1041 l_rawrt_selstr_va.extend(1);
1042 l_rawrt_fromstr_va.extend(1);
1043 l_rawrt_wherestr_va.extend(1);
1044 l_rawrt_sqlstr_va.extend(1);
1045 l_rawrt_cd_va(l_rawrt_en) := 'EPODFCT';
1046 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1047 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1048 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1049 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_opt_f_rbv rbv ';
1050 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1051 ||' and (rbv.comp_ref_amt is not null '
1052 ||' or rbv.cmbn_age_n_los_val is not null '
1053 ||' or rbv.age_val is not null '
1054 ||' or rbv.los_val is not null '
1055 ||' or rbv.hrs_wkd_val is not null '
1056 ||' or rbv.pct_fl_tm_val is not null) ';
1057 --
1058 l_rawrt_en := l_rawrt_en+1;
1059 l_rawrt_cd_va.extend(1);
1060 l_rawrt_typcd_va.extend(1);
1061 l_rawrt_selstr_va.extend(1);
1062 l_rawrt_fromstr_va.extend(1);
1063 l_rawrt_wherestr_va.extend(1);
1064 l_rawrt_sqlstr_va.extend(1);
1065 l_rawrt_cd_va(l_rawrt_en) := 'EPODFCTRT';
1066 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1067 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1068 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1069 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_opt_f_rbv rbv ';
1070 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1071 ||' and (rbv.rt_comp_ref_amt is not null '
1072 ||' or rbv.rt_cmbn_age_n_los_val is not null '
1073 ||' or rbv.rt_age_val is not null '
1074 ||' or rbv.rt_los_val is not null '
1075 ||' or rbv.rt_hrs_wkd_val is not null '
1076 ||' or rbv.rt_pct_fl_tm_val is not null) ';
1077 --
1078 l_rawrt_en := l_rawrt_en+1;
1079 l_rawrt_cd_va.extend(1);
1080 l_rawrt_typcd_va.extend(1);
1081 l_rawrt_selstr_va.extend(1);
1082 l_rawrt_fromstr_va.extend(1);
1083 l_rawrt_wherestr_va.extend(1);
1084 l_rawrt_sqlstr_va.extend(1);
1085 l_rawrt_cd_va(l_rawrt_en) := 'EPE';
1086 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1087 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1088 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1089 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_elctbl_chc_rbv rbv ';
1090 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id ';
1091 --
1092 l_rawrt_en := l_rawrt_en+1;
1093 l_rawrt_cd_va.extend(1);
1094 l_rawrt_typcd_va.extend(1);
1095 l_rawrt_selstr_va.extend(1);
1096 l_rawrt_fromstr_va.extend(1);
1097 l_rawrt_wherestr_va.extend(1);
1098 l_rawrt_sqlstr_va.extend(1);
1099 l_rawrt_cd_va(l_rawrt_en) := 'EPEELFLG';
1100 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1101 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1102 ||' nvl(sum(nvl(rbv.object_version_number,0)),0) sum_ovn ';
1103 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_elctbl_chc_rbv rbv ';
1107 l_rawrt_en := l_rawrt_en+1;
1104 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1105 ||' and rbv.elctbl_flag = '||''''||'Y'||'''';
1106 --
1108 l_rawrt_cd_va.extend(1);
1109 l_rawrt_typcd_va.extend(1);
1110 l_rawrt_selstr_va.extend(1);
1111 l_rawrt_fromstr_va.extend(1);
1112 l_rawrt_wherestr_va.extend(1);
1113 l_rawrt_sqlstr_va.extend(1);
1114 l_rawrt_cd_va(l_rawrt_en) := 'EPEBPRVPL';
1115 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1116 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1117 ||' nvl(sum(nvl(rbv.object_version_number,0)),0) sum_ovn ';
1118 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_elctbl_chc_rbv rbv ';
1119 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1120 ||' and rbv.BNFT_PRVDR_POOL_ID is not null ';
1121 --
1122 l_rawrt_en := l_rawrt_en+1;
1123 l_rawrt_cd_va.extend(1);
1124 l_rawrt_typcd_va.extend(1);
1125 l_rawrt_selstr_va.extend(1);
1126 l_rawrt_fromstr_va.extend(1);
1127 l_rawrt_wherestr_va.extend(1);
1128 l_rawrt_sqlstr_va.extend(1);
1129 l_rawrt_cd_va(l_rawrt_en) := 'EPEAUTOENR';
1130 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1131 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1132 ||' nvl(sum(nvl(rbv.object_version_number,0)),0) sum_ovn ';
1133 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_elctbl_chc_rbv rbv ';
1134 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1135 ||' and rbv.auto_enrt_flag = '||''''||'Y'||'''';
1136 --
1137 l_rawrt_en := l_rawrt_en+1;
1138 l_rawrt_cd_va.extend(1);
1139 l_rawrt_typcd_va.extend(1);
1140 l_rawrt_selstr_va.extend(1);
1141 l_rawrt_fromstr_va.extend(1);
1142 l_rawrt_wherestr_va.extend(1);
1143 l_rawrt_sqlstr_va.extend(1);
1144 l_rawrt_cd_va(l_rawrt_en) := 'EPECURRENR';
1145 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1146 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1147 ||' nvl(sum(nvl(rbv.object_version_number,0)),0) sum_ovn ';
1148 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_elctbl_chc_rbv rbv ';
1149 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1150 ||' and rbv.crntly_enrd_flag = '||''''||'Y'||'''';
1151 --
1152 l_rawrt_en := l_rawrt_en+1;
1153 l_rawrt_cd_va.extend(1);
1154 l_rawrt_typcd_va.extend(1);
1155 l_rawrt_selstr_va.extend(1);
1156 l_rawrt_fromstr_va.extend(1);
1157 l_rawrt_wherestr_va.extend(1);
1158 l_rawrt_sqlstr_va.extend(1);
1159 l_rawrt_cd_va(l_rawrt_en) := 'EPECRYFWDDPNT';
1160 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1161 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1162 ||' nvl(sum(nvl(rbv.object_version_number,0)),0) sum_ovn ';
1163 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_elctbl_chc_rbv rbv ';
1164 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1165 ||' and rbv.cryfwd_elig_dpnt_cd = '||''''||'CFRRWP'||'''';
1166 --
1167 l_rawrt_en := l_rawrt_en+1;
1168 l_rawrt_cd_va.extend(1);
1169 l_rawrt_typcd_va.extend(1);
1170 l_rawrt_selstr_va.extend(1);
1171 l_rawrt_fromstr_va.extend(1);
1172 l_rawrt_wherestr_va.extend(1);
1173 l_rawrt_sqlstr_va.extend(1);
1174 l_rawrt_cd_va(l_rawrt_en) := 'EPEPLN';
1175 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1176 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1177 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1178 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_elctbl_chc_rbv rbv ';
1179 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1180 ||' and rbv.pl_id is not null '
1181 ||' and rbv.oipl_id is null ';
1182 --
1183 l_rawrt_en := l_rawrt_en+1;
1184 l_rawrt_cd_va.extend(1);
1185 l_rawrt_typcd_va.extend(1);
1186 l_rawrt_selstr_va.extend(1);
1187 l_rawrt_fromstr_va.extend(1);
1188 l_rawrt_wherestr_va.extend(1);
1189 l_rawrt_sqlstr_va.extend(1);
1190 l_rawrt_cd_va(l_rawrt_en) := 'EPEPLNIMPPRTT';
1191 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1192 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1193 ||' nvl(sum(nvl(rbv.object_version_number,0)),0) sum_ovn ';
1194 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_elctbl_chc_rbv rbv, ben_pl_f pln ';
1195 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1196 ||' and rbv.pl_id = pln.pl_id '
1197 ||' and pln.subj_to_imptd_incm_typ_cd = '||''''||'PRTT'||'''';
1198 --
1199 l_rawrt_en := l_rawrt_en+1;
1200 l_rawrt_cd_va.extend(1);
1201 l_rawrt_typcd_va.extend(1);
1202 l_rawrt_selstr_va.extend(1);
1203 l_rawrt_fromstr_va.extend(1);
1204 l_rawrt_wherestr_va.extend(1);
1205 l_rawrt_sqlstr_va.extend(1);
1206 l_rawrt_cd_va(l_rawrt_en) := 'EPEPLNIMPDPNT';
1207 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1208 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1209 ||' nvl(sum(nvl(rbv.object_version_number,0)),0) sum_ovn ';
1210 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_elctbl_chc_rbv rbv, ben_pl_f pln ';
1211 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1212 ||' and rbv.pl_id = pln.pl_id '
1213 ||' and pln.subj_to_imptd_incm_typ_cd = '||''''||'DPNT'||'''';
1214 --
1215 l_rawrt_en := l_rawrt_en+1;
1216 l_rawrt_cd_va.extend(1);
1217 l_rawrt_typcd_va.extend(1);
1221 l_rawrt_sqlstr_va.extend(1);
1218 l_rawrt_selstr_va.extend(1);
1219 l_rawrt_fromstr_va.extend(1);
1220 l_rawrt_wherestr_va.extend(1);
1222 l_rawrt_cd_va(l_rawrt_en) := 'EPEPLNIMPSPS';
1223 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1224 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1225 ||' nvl(sum(nvl(rbv.object_version_number,0)),0) sum_ovn ';
1226 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_elctbl_chc_rbv rbv, ben_pl_f pln ';
1227 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1228 ||' and rbv.pl_id = pln.pl_id '
1229 ||' and pln.subj_to_imptd_incm_typ_cd = '||''''||'SPS'||'''';
1230 --
1231 l_rawrt_en := l_rawrt_en+1;
1232 l_rawrt_cd_va.extend(1);
1233 l_rawrt_typcd_va.extend(1);
1234 l_rawrt_selstr_va.extend(1);
1235 l_rawrt_fromstr_va.extend(1);
1236 l_rawrt_wherestr_va.extend(1);
1237 l_rawrt_sqlstr_va.extend(1);
1238 l_rawrt_cd_va(l_rawrt_en) := 'EPEPGMARPMO';
1239 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1240 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1241 ||' nvl(sum(nvl(rbv.object_version_number,0)),0) sum_ovn ';
1242 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_elctbl_chc_rbv rbv, ben_pgm_f pgm ';
1243 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1244 ||' and rbv.pgm_id = pgm.pgm_id '
1245 ||' and pgm.acty_ref_perd_cd = '||''''||'MO'||'''';
1246 --
1247 l_rawrt_en := l_rawrt_en+1;
1248 l_rawrt_cd_va.extend(1);
1249 l_rawrt_typcd_va.extend(1);
1250 l_rawrt_selstr_va.extend(1);
1251 l_rawrt_fromstr_va.extend(1);
1252 l_rawrt_wherestr_va.extend(1);
1253 l_rawrt_sqlstr_va.extend(1);
1254 l_rawrt_cd_va(l_rawrt_en) := 'EPEPGMARPPYR';
1255 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1256 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1257 ||' nvl(sum(nvl(rbv.object_version_number,0)),0) sum_ovn ';
1258 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_elctbl_chc_rbv rbv, ben_pgm_f pgm ';
1259 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1260 ||' and rbv.pgm_id = pgm.pgm_id '
1261 ||' and pgm.acty_ref_perd_cd = '||''''||'PYR'||'''';
1262 --
1263 l_rawrt_en := l_rawrt_en+1;
1264 l_rawrt_cd_va.extend(1);
1265 l_rawrt_typcd_va.extend(1);
1266 l_rawrt_selstr_va.extend(1);
1267 l_rawrt_fromstr_va.extend(1);
1268 l_rawrt_wherestr_va.extend(1);
1269 l_rawrt_sqlstr_va.extend(1);
1270 l_rawrt_cd_va(l_rawrt_en) := 'EPEPGMARPPWK';
1271 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1272 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1273 ||' nvl(sum(nvl(rbv.object_version_number,0)),0) sum_ovn ';
1274 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_elctbl_chc_rbv rbv, ben_pgm_f pgm ';
1275 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1276 ||' and rbv.pgm_id = pgm.pgm_id '
1277 ||' and pgm.acty_ref_perd_cd = '||''''||'PWK'||'''';
1278 --
1279 l_rawrt_en := l_rawrt_en+1;
1280 l_rawrt_cd_va.extend(1);
1281 l_rawrt_typcd_va.extend(1);
1282 l_rawrt_selstr_va.extend(1);
1283 l_rawrt_fromstr_va.extend(1);
1284 l_rawrt_wherestr_va.extend(1);
1285 l_rawrt_sqlstr_va.extend(1);
1286 l_rawrt_cd_va(l_rawrt_en) := 'EPEPGMARPBWK';
1287 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1288 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1289 ||' nvl(sum(nvl(rbv.object_version_number,0)),0) sum_ovn ';
1290 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_elctbl_chc_rbv rbv, ben_pgm_f pgm ';
1291 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1292 ||' and rbv.pgm_id = pgm.pgm_id '
1293 ||' and pgm.acty_ref_perd_cd = '||''''||'BWK'||'''';
1294 --
1295 l_rawrt_en := l_rawrt_en+1;
1296 l_rawrt_cd_va.extend(1);
1297 l_rawrt_typcd_va.extend(1);
1298 l_rawrt_selstr_va.extend(1);
1299 l_rawrt_fromstr_va.extend(1);
1300 l_rawrt_wherestr_va.extend(1);
1301 l_rawrt_sqlstr_va.extend(1);
1302 l_rawrt_cd_va(l_rawrt_en) := 'EPEPGMARPPHR';
1303 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1304 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1305 ||' nvl(sum(nvl(rbv.object_version_number,0)),0) sum_ovn ';
1306 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_elctbl_chc_rbv rbv, ben_pgm_f pgm ';
1307 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1308 ||' and rbv.pgm_id = pgm.pgm_id '
1309 ||' and pgm.acty_ref_perd_cd = '||''''||'PHR'||'''';
1310 --
1311 l_rawrt_en := l_rawrt_en+1;
1312 l_rawrt_cd_va.extend(1);
1313 l_rawrt_typcd_va.extend(1);
1314 l_rawrt_selstr_va.extend(1);
1315 l_rawrt_fromstr_va.extend(1);
1316 l_rawrt_wherestr_va.extend(1);
1317 l_rawrt_sqlstr_va.extend(1);
1318 l_rawrt_cd_va(l_rawrt_en) := 'EPEOIPL';
1319 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1320 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1321 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1322 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_per_elctbl_chc_rbv rbv ';
1323 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1324 ||' and rbv.pl_id is not null '
1325 ||' and rbv.oipl_id is not null ';
1326 --
1327 l_rawrt_en := l_rawrt_en+1;
1328 l_rawrt_cd_va.extend(1);
1332 l_rawrt_wherestr_va.extend(1);
1329 l_rawrt_typcd_va.extend(1);
1330 l_rawrt_selstr_va.extend(1);
1331 l_rawrt_fromstr_va.extend(1);
1333 l_rawrt_sqlstr_va.extend(1);
1334 l_rawrt_cd_va(l_rawrt_en) := 'PEL';
1335 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE0';
1336 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1337 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1338 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_pil_epe_popl_rbv rbv ';
1339 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id ';
1340 --
1341 l_rawrt_en := l_rawrt_en+1;
1342 l_rawrt_cd_va.extend(1);
1343 l_rawrt_typcd_va.extend(1);
1344 l_rawrt_selstr_va.extend(1);
1345 l_rawrt_fromstr_va.extend(1);
1346 l_rawrt_wherestr_va.extend(1);
1347 l_rawrt_sqlstr_va.extend(1);
1348 l_rawrt_cd_va(l_rawrt_en) := 'PELLEERSN';
1349 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1350 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1351 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1352 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_pil_epe_popl_rbv rbv ';
1353 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1354 ||' and rbv.lee_rsn_id is not null ';
1355 --
1356 l_rawrt_en := l_rawrt_en+1;
1357 l_rawrt_cd_va.extend(1);
1358 l_rawrt_typcd_va.extend(1);
1359 l_rawrt_selstr_va.extend(1);
1360 l_rawrt_fromstr_va.extend(1);
1361 l_rawrt_wherestr_va.extend(1);
1362 l_rawrt_sqlstr_va.extend(1);
1363 l_rawrt_cd_va(l_rawrt_en) := 'ECC';
1364 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE0';
1365 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1366 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1367 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elctbl_chc_ctfn_rbv rbv ';
1368 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id ';
1369 --
1370 l_rawrt_en := l_rawrt_en+1;
1371 l_rawrt_cd_va.extend(1);
1372 l_rawrt_typcd_va.extend(1);
1373 l_rawrt_selstr_va.extend(1);
1374 l_rawrt_fromstr_va.extend(1);
1375 l_rawrt_wherestr_va.extend(1);
1376 l_rawrt_sqlstr_va.extend(1);
1377 l_rawrt_cd_va(l_rawrt_en) := 'EGD';
1378 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE0';
1379 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1380 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1381 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_dpnt_rbv rbv ';
1382 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id ';
1383 --
1384 l_rawrt_en := l_rawrt_en+1;
1385 l_rawrt_cd_va.extend(1);
1386 l_rawrt_typcd_va.extend(1);
1387 l_rawrt_selstr_va.extend(1);
1388 l_rawrt_fromstr_va.extend(1);
1389 l_rawrt_wherestr_va.extend(1);
1390 l_rawrt_sqlstr_va.extend(1);
1391 l_rawrt_cd_va(l_rawrt_en) := 'EGDPELP';
1392 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1393 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1394 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1395 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_dpnt_rbv rbv ';
1396 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1397 ||' and rbv.ELIG_PER_OPT_ID is null ';
1398 --
1399 l_rawrt_en := l_rawrt_en+1;
1400 l_rawrt_cd_va.extend(1);
1401 l_rawrt_typcd_va.extend(1);
1402 l_rawrt_selstr_va.extend(1);
1403 l_rawrt_fromstr_va.extend(1);
1404 l_rawrt_wherestr_va.extend(1);
1405 l_rawrt_sqlstr_va.extend(1);
1406 l_rawrt_cd_va(l_rawrt_en) := 'EGDELPOPT';
1407 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1408 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1409 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1410 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_dpnt_rbv rbv ';
1411 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1412 ||' and rbv.ELIG_PER_OPT_ID is not null ';
1413 --
1414 l_rawrt_en := l_rawrt_en+1;
1415 l_rawrt_cd_va.extend(1);
1416 l_rawrt_typcd_va.extend(1);
1417 l_rawrt_selstr_va.extend(1);
1418 l_rawrt_fromstr_va.extend(1);
1419 l_rawrt_wherestr_va.extend(1);
1420 l_rawrt_sqlstr_va.extend(1);
1421 l_rawrt_cd_va(l_rawrt_en) := 'PDP';
1422 l_rawrt_typcd_va(l_rawrt_en) := 'DTTABLE';
1423 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1424 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1425 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_cvrd_dpnt_f_rbv rbv ';
1426 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id ';
1427 --
1428 l_rawrt_en := l_rawrt_en+1;
1429 l_rawrt_cd_va.extend(1);
1430 l_rawrt_typcd_va.extend(1);
1431 l_rawrt_selstr_va.extend(1);
1432 l_rawrt_fromstr_va.extend(1);
1433 l_rawrt_wherestr_va.extend(1);
1434 l_rawrt_sqlstr_va.extend(1);
1435 l_rawrt_cd_va(l_rawrt_en) := 'PDPCVRD';
1436 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1437 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1438 ||' nvl(sum(nvl(rbv.object_version_number,0)),0) sum_ovn ';
1439 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_elig_cvrd_dpnt_f_rbv rbv, ben_prtt_enrt_rslt_f_rbv prbv ';
1440 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1441 ||' and rbv.benefit_action_id = prbv.benefit_action_id '
1442 ||' and rbv.PRTT_ENRT_RSLT_ID = prbv.PRTT_ENRT_RSLT_ID ';
1443 --
1444 l_rawrt_en := l_rawrt_en+1;
1445 l_rawrt_cd_va.extend(1);
1446 l_rawrt_typcd_va.extend(1);
1447 l_rawrt_selstr_va.extend(1);
1451 l_rawrt_cd_va(l_rawrt_en) := 'CQB';
1448 l_rawrt_fromstr_va.extend(1);
1449 l_rawrt_wherestr_va.extend(1);
1450 l_rawrt_sqlstr_va.extend(1);
1452 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE0';
1453 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1454 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1455 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_cbr_quald_bnf_rbv rbv ';
1456 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id ';
1457 --
1458 l_rawrt_en := l_rawrt_en+1;
1459 l_rawrt_cd_va.extend(1);
1460 l_rawrt_typcd_va.extend(1);
1461 l_rawrt_selstr_va.extend(1);
1462 l_rawrt_fromstr_va.extend(1);
1463 l_rawrt_wherestr_va.extend(1);
1464 l_rawrt_sqlstr_va.extend(1);
1465 l_rawrt_cd_va(l_rawrt_en) := 'ENB';
1466 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE0';
1467 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1468 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1469 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_enrt_bnft_rbv rbv ';
1470 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id ';
1471 --
1472 l_rawrt_en := l_rawrt_en+1;
1473 l_rawrt_cd_va.extend(1);
1474 l_rawrt_typcd_va.extend(1);
1475 l_rawrt_selstr_va.extend(1);
1476 l_rawrt_fromstr_va.extend(1);
1477 l_rawrt_wherestr_va.extend(1);
1478 l_rawrt_sqlstr_va.extend(1);
1479 l_rawrt_cd_va(l_rawrt_en) := 'ENBDFLT';
1480 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1481 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1482 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1483 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_enrt_bnft_rbv rbv ';
1484 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1485 ||' and rbv.dflt_flag='||''''||'Y'||'''';
1486 --
1487 l_rawrt_en := l_rawrt_en+1;
1488 l_rawrt_cd_va.extend(1);
1489 l_rawrt_typcd_va.extend(1);
1490 l_rawrt_selstr_va.extend(1);
1491 l_rawrt_fromstr_va.extend(1);
1492 l_rawrt_wherestr_va.extend(1);
1493 l_rawrt_sqlstr_va.extend(1);
1494 l_rawrt_cd_va(l_rawrt_en) := 'ENBCMCCL';
1495 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1496 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1497 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1498 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_enrt_bnft_rbv rbv ';
1499 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1500 ||' and rbv.cvg_mlt_cd='||''''||'CL'||'''';
1501 --
1502 l_rawrt_en := l_rawrt_en+1;
1503 l_rawrt_cd_va.extend(1);
1504 l_rawrt_typcd_va.extend(1);
1505 l_rawrt_selstr_va.extend(1);
1506 l_rawrt_fromstr_va.extend(1);
1507 l_rawrt_wherestr_va.extend(1);
1508 l_rawrt_sqlstr_va.extend(1);
1509 l_rawrt_cd_va(l_rawrt_en) := 'ENBCMCFLRNG';
1510 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1511 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1512 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1513 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_enrt_bnft_rbv rbv ';
1514 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1515 ||' and rbv.cvg_mlt_cd='||''''||'FLRNG'||'''';
1516 --
1517 l_rawrt_en := l_rawrt_en+1;
1518 l_rawrt_cd_va.extend(1);
1519 l_rawrt_typcd_va.extend(1);
1520 l_rawrt_selstr_va.extend(1);
1521 l_rawrt_fromstr_va.extend(1);
1522 l_rawrt_wherestr_va.extend(1);
1523 l_rawrt_sqlstr_va.extend(1);
1524 l_rawrt_cd_va(l_rawrt_en) := 'ENBCMCFLFX';
1525 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1526 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1527 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1528 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_enrt_bnft_rbv rbv ';
1529 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1530 ||' and rbv.cvg_mlt_cd='||''''||'FLFX'||'''';
1531 --
1532 l_rawrt_en := l_rawrt_en+1;
1533 l_rawrt_cd_va.extend(1);
1534 l_rawrt_typcd_va.extend(1);
1535 l_rawrt_selstr_va.extend(1);
1536 l_rawrt_fromstr_va.extend(1);
1537 l_rawrt_wherestr_va.extend(1);
1538 l_rawrt_sqlstr_va.extend(1);
1539 l_rawrt_cd_va(l_rawrt_en) := 'ENBCMCSAAEAR';
1540 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1541 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1542 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1543 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_enrt_bnft_rbv rbv ';
1544 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1545 ||' and rbv.cvg_mlt_cd='||''''||'SAAEAR'||'''';
1546 --
1547 l_rawrt_en := l_rawrt_en+1;
1548 l_rawrt_cd_va.extend(1);
1549 l_rawrt_typcd_va.extend(1);
1550 l_rawrt_selstr_va.extend(1);
1551 l_rawrt_fromstr_va.extend(1);
1552 l_rawrt_wherestr_va.extend(1);
1553 l_rawrt_sqlstr_va.extend(1);
1554 l_rawrt_cd_va(l_rawrt_en) := 'ENBCMCCLRNG';
1555 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1556 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1557 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1558 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_enrt_bnft_rbv rbv ';
1559 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1560 ||' and rbv.cvg_mlt_cd='||''''||'CLRNG'||'''';
1561 --
1562 l_rawrt_en := l_rawrt_en+1;
1563 l_rawrt_cd_va.extend(1);
1564 l_rawrt_typcd_va.extend(1);
1565 l_rawrt_selstr_va.extend(1);
1566 l_rawrt_fromstr_va.extend(1);
1570 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1567 l_rawrt_wherestr_va.extend(1);
1568 l_rawrt_sqlstr_va.extend(1);
1569 l_rawrt_cd_va(l_rawrt_en) := 'ENBCMCCLPFLRNG';
1571 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1572 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1573 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_enrt_bnft_rbv rbv ';
1574 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1575 ||' and rbv.cvg_mlt_cd='||''''||'CLPFLRNG'||'''';
1576 --
1577 l_rawrt_en := l_rawrt_en+1;
1578 l_rawrt_cd_va.extend(1);
1579 l_rawrt_typcd_va.extend(1);
1580 l_rawrt_selstr_va.extend(1);
1581 l_rawrt_fromstr_va.extend(1);
1582 l_rawrt_wherestr_va.extend(1);
1583 l_rawrt_sqlstr_va.extend(1);
1584 l_rawrt_cd_va(l_rawrt_en) := 'EPR';
1585 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE0';
1586 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1587 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1588 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_enrt_prem_rbv rbv ';
1589 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id ';
1590 --
1591 l_rawrt_en := l_rawrt_en+1;
1592 l_rawrt_cd_va.extend(1);
1593 l_rawrt_typcd_va.extend(1);
1594 l_rawrt_selstr_va.extend(1);
1595 l_rawrt_fromstr_va.extend(1);
1596 l_rawrt_wherestr_va.extend(1);
1597 l_rawrt_sqlstr_va.extend(1);
1598 l_rawrt_cd_va(l_rawrt_en) := 'ECR';
1599 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE0';
1600 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1601 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1602 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_enrt_rt_rbv rbv ';
1603 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id ';
1604 --
1605 l_rawrt_en := l_rawrt_en+1;
1606 l_rawrt_cd_va.extend(1);
1607 l_rawrt_typcd_va.extend(1);
1608 l_rawrt_selstr_va.extend(1);
1609 l_rawrt_fromstr_va.extend(1);
1610 l_rawrt_wherestr_va.extend(1);
1611 l_rawrt_sqlstr_va.extend(1);
1612 l_rawrt_cd_va(l_rawrt_en) := 'ECRFLXCR';
1613 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1614 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1615 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1616 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_enrt_rt_rbv rbv ';
1617 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1618 ||' and rbv.rt_usg_cd = '||''''||'FLXCR'||'''';
1619 --
1620 l_rawrt_en := l_rawrt_en+1;
1621 l_rawrt_cd_va.extend(1);
1622 l_rawrt_typcd_va.extend(1);
1623 l_rawrt_selstr_va.extend(1);
1624 l_rawrt_fromstr_va.extend(1);
1625 l_rawrt_wherestr_va.extend(1);
1626 l_rawrt_sqlstr_va.extend(1);
1627 l_rawrt_cd_va(l_rawrt_en) := 'ECRIMPTDINC';
1628 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1629 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1630 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1631 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_enrt_rt_rbv rbv ';
1632 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1633 ||' and rbv.rt_usg_cd = '||''''||'IMPTDINC'||'''';
1634 --
1635 l_rawrt_en := l_rawrt_en+1;
1636 l_rawrt_cd_va.extend(1);
1637 l_rawrt_typcd_va.extend(1);
1638 l_rawrt_selstr_va.extend(1);
1639 l_rawrt_fromstr_va.extend(1);
1640 l_rawrt_wherestr_va.extend(1);
1641 l_rawrt_sqlstr_va.extend(1);
1642 l_rawrt_cd_va(l_rawrt_en) := 'ECRSTD';
1643 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1644 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1645 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1646 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_enrt_rt_rbv rbv ';
1647 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1648 ||' and rbv.rt_usg_cd = '||''''||'STD'||'''';
1649 --
1650 l_rawrt_en := l_rawrt_en+1;
1651 l_rawrt_cd_va.extend(1);
1652 l_rawrt_typcd_va.extend(1);
1653 l_rawrt_selstr_va.extend(1);
1654 l_rawrt_fromstr_va.extend(1);
1655 l_rawrt_wherestr_va.extend(1);
1656 l_rawrt_sqlstr_va.extend(1);
1657 l_rawrt_cd_va(l_rawrt_en) := 'ECRENTVLENR';
1658 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1659 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1660 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1661 l_rawrt_fromstr_va(l_rawrt_en) := ' from ben_enrt_rt_rbv rbv ';
1662 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1663 ||' and rbv.entr_val_at_enrt_flag = '||''''||'Y'||'''';
1664 --
1665 l_rawrt_en := l_rawrt_en+1;
1666 l_rawrt_cd_va.extend(1);
1667 l_rawrt_typcd_va.extend(1);
1668 l_rawrt_selstr_va.extend(1);
1669 l_rawrt_fromstr_va.extend(1);
1670 l_rawrt_wherestr_va.extend(1);
1671 l_rawrt_sqlstr_va.extend(1);
1672 l_rawrt_cd_va(l_rawrt_en) := 'PRV';
1673 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE0';
1674 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1675 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1676 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_RT_VAL_RBV rbv ';
1677 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id ';
1678 --
1679 l_rawrt_en := l_rawrt_en+1;
1680 l_rawrt_cd_va.extend(1);
1681 l_rawrt_typcd_va.extend(1);
1682 l_rawrt_selstr_va.extend(1);
1683 l_rawrt_fromstr_va.extend(1);
1684 l_rawrt_wherestr_va.extend(1);
1685 l_rawrt_sqlstr_va.extend(1);
1689 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1686 l_rawrt_cd_va(l_rawrt_en) := 'PRVEEV';
1687 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1688 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1690 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_RT_VAL_RBV rbv ';
1691 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1692 ||' and rbv.ELEMENT_ENTRY_VALUE_ID is not null ';
1693 --
1694 l_rawrt_en := l_rawrt_en+1;
1695 l_rawrt_cd_va.extend(1);
1696 l_rawrt_typcd_va.extend(1);
1697 l_rawrt_selstr_va.extend(1);
1698 l_rawrt_fromstr_va.extend(1);
1699 l_rawrt_wherestr_va.extend(1);
1700 l_rawrt_sqlstr_va.extend(1);
1701 l_rawrt_cd_va(l_rawrt_en) := 'PRVFLFX';
1702 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1703 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1704 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1705 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_RT_VAL_RBV rbv ';
1706 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1707 ||' and rbv.mlt_cd = '||''''||'FLFX'||'''';
1708 --
1709 l_rawrt_en := l_rawrt_en+1;
1710 l_rawrt_cd_va.extend(1);
1711 l_rawrt_typcd_va.extend(1);
1712 l_rawrt_selstr_va.extend(1);
1713 l_rawrt_fromstr_va.extend(1);
1714 l_rawrt_wherestr_va.extend(1);
1715 l_rawrt_sqlstr_va.extend(1);
1716 l_rawrt_cd_va(l_rawrt_en) := 'PRVCL';
1717 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1718 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1719 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1720 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_RT_VAL_RBV rbv ';
1721 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1722 ||' and rbv.mlt_cd = '||''''||'CL'||'''';
1723 --
1724 l_rawrt_en := l_rawrt_en+1;
1725 l_rawrt_cd_va.extend(1);
1726 l_rawrt_typcd_va.extend(1);
1727 l_rawrt_selstr_va.extend(1);
1728 l_rawrt_fromstr_va.extend(1);
1729 l_rawrt_wherestr_va.extend(1);
1730 l_rawrt_sqlstr_va.extend(1);
1731 l_rawrt_cd_va(l_rawrt_en) := 'PRVSAREC';
1732 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1733 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1734 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1735 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_RT_VAL_RBV rbv ';
1736 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1737 ||' and rbv.mlt_cd = '||''''||'SAREC'||'''';
1738 --
1739 l_rawrt_en := l_rawrt_en+1;
1740 l_rawrt_cd_va.extend(1);
1741 l_rawrt_typcd_va.extend(1);
1742 l_rawrt_selstr_va.extend(1);
1743 l_rawrt_fromstr_va.extend(1);
1744 l_rawrt_wherestr_va.extend(1);
1745 l_rawrt_sqlstr_va.extend(1);
1746 l_rawrt_cd_va(l_rawrt_en) := 'PRVCVG';
1747 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1748 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1749 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1750 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_RT_VAL_RBV rbv ';
1751 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1752 ||' and rbv.mlt_cd = '||''''||'CVG'||'''';
1753 --
1754 l_rawrt_en := l_rawrt_en+1;
1755 l_rawrt_cd_va.extend(1);
1756 l_rawrt_typcd_va.extend(1);
1757 l_rawrt_selstr_va.extend(1);
1758 l_rawrt_fromstr_va.extend(1);
1759 l_rawrt_wherestr_va.extend(1);
1760 l_rawrt_sqlstr_va.extend(1);
1761 l_rawrt_cd_va(l_rawrt_en) := 'PRVNSVU';
1762 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1763 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1764 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1765 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_RT_VAL_RBV rbv ';
1766 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1767 ||' and rbv.mlt_cd = '||''''||'NSVU'||'''';
1768 --
1769 l_rawrt_en := l_rawrt_en+1;
1770 l_rawrt_cd_va.extend(1);
1771 l_rawrt_typcd_va.extend(1);
1772 l_rawrt_selstr_va.extend(1);
1773 l_rawrt_fromstr_va.extend(1);
1774 l_rawrt_wherestr_va.extend(1);
1775 l_rawrt_sqlstr_va.extend(1);
1776 l_rawrt_cd_va(l_rawrt_en) := 'PRVPRNT';
1777 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1778 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1779 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1780 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_RT_VAL_RBV rbv ';
1781 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1782 ||' and rbv.mlt_cd = '||''''||'PRNT'||'''';
1783 --
1784 l_rawrt_en := l_rawrt_en+1;
1785 l_rawrt_cd_va.extend(1);
1786 l_rawrt_typcd_va.extend(1);
1787 l_rawrt_selstr_va.extend(1);
1788 l_rawrt_fromstr_va.extend(1);
1789 l_rawrt_wherestr_va.extend(1);
1790 l_rawrt_sqlstr_va.extend(1);
1791 l_rawrt_cd_va(l_rawrt_en) := 'PRVAP';
1792 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1793 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1794 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1795 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_RT_VAL_RBV rbv ';
1796 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1797 ||' and rbv.mlt_cd = '||''''||'AP'||'''';
1798 --
1799 l_rawrt_en := l_rawrt_en+1;
1800 l_rawrt_cd_va.extend(1);
1801 l_rawrt_typcd_va.extend(1);
1802 l_rawrt_selstr_va.extend(1);
1806 l_rawrt_cd_va(l_rawrt_en) := 'PRVRL';
1803 l_rawrt_fromstr_va.extend(1);
1804 l_rawrt_wherestr_va.extend(1);
1805 l_rawrt_sqlstr_va.extend(1);
1807 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1808 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1809 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1810 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_RT_VAL_RBV rbv ';
1811 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1812 ||' and rbv.mlt_cd = '||''''||'RL'||'''';
1813 --
1814 l_rawrt_en := l_rawrt_en+1;
1815 l_rawrt_cd_va.extend(1);
1816 l_rawrt_typcd_va.extend(1);
1817 l_rawrt_selstr_va.extend(1);
1818 l_rawrt_fromstr_va.extend(1);
1819 l_rawrt_wherestr_va.extend(1);
1820 l_rawrt_sqlstr_va.extend(1);
1821 l_rawrt_cd_va(l_rawrt_en) := 'PRVCLANDCVG';
1822 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1823 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1824 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1825 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_RT_VAL_RBV rbv ';
1826 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1827 ||' and rbv.mlt_cd = '||''''||'CLANDCVG'||'''';
1828 --
1829 l_rawrt_en := l_rawrt_en+1;
1830 l_rawrt_cd_va.extend(1);
1831 l_rawrt_typcd_va.extend(1);
1832 l_rawrt_selstr_va.extend(1);
1833 l_rawrt_fromstr_va.extend(1);
1834 l_rawrt_wherestr_va.extend(1);
1835 l_rawrt_sqlstr_va.extend(1);
1836 l_rawrt_cd_va(l_rawrt_en) := 'PRVRTOVRIDN';
1837 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1838 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1839 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1840 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_RT_VAL_RBV rbv ';
1841 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1842 ||' and rbv.rt_ovridn_flag = '||''''||'Y'||'''';
1843 --
1844 l_rawrt_en := l_rawrt_en+1;
1845 l_rawrt_cd_va.extend(1);
1846 l_rawrt_typcd_va.extend(1);
1847 l_rawrt_selstr_va.extend(1);
1848 l_rawrt_fromstr_va.extend(1);
1849 l_rawrt_wherestr_va.extend(1);
1850 l_rawrt_sqlstr_va.extend(1);
1851 l_rawrt_cd_va(l_rawrt_en) := 'PRVATCNCRDSTR';
1852 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1853 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1854 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1855 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_RT_VAL_RBV rbv ';
1856 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1857 ||' and rbv.acty_typ_cd = '||''''||'NCRDSTR'||'''';
1858 --
1859 l_rawrt_en := l_rawrt_en+1;
1860 l_rawrt_cd_va.extend(1);
1861 l_rawrt_typcd_va.extend(1);
1862 l_rawrt_selstr_va.extend(1);
1863 l_rawrt_fromstr_va.extend(1);
1864 l_rawrt_wherestr_va.extend(1);
1865 l_rawrt_sqlstr_va.extend(1);
1866 l_rawrt_cd_va(l_rawrt_en) := 'PRVATCNCRUDED';
1867 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1868 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1869 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1870 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_RT_VAL_RBV rbv ';
1871 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1872 ||' and rbv.acty_typ_cd = '||''''||'NCRUDED'||'''';
1873 --
1874 l_rawrt_en := l_rawrt_en+1;
1875 l_rawrt_cd_va.extend(1);
1876 l_rawrt_typcd_va.extend(1);
1877 l_rawrt_selstr_va.extend(1);
1878 l_rawrt_fromstr_va.extend(1);
1879 l_rawrt_wherestr_va.extend(1);
1880 l_rawrt_sqlstr_va.extend(1);
1881 l_rawrt_cd_va(l_rawrt_en) := 'PRVATCEEIC';
1882 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1883 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1884 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1885 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_RT_VAL_RBV rbv ';
1886 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1887 ||' and rbv.acty_typ_cd = '||''''||'EEIC'||'''';
1888 --
1889 l_rawrt_en := l_rawrt_en+1;
1890 l_rawrt_cd_va.extend(1);
1891 l_rawrt_typcd_va.extend(1);
1892 l_rawrt_selstr_va.extend(1);
1893 l_rawrt_fromstr_va.extend(1);
1894 l_rawrt_wherestr_va.extend(1);
1895 l_rawrt_sqlstr_va.extend(1);
1896 l_rawrt_cd_va(l_rawrt_en) := 'PRVATCEEPRIID';
1897 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1898 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1899 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1900 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_RT_VAL_RBV rbv ';
1901 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1902 ||' and rbv.acty_typ_cd = '||''''||'EEPRIID'||'''';
1903 --
1904 l_rawrt_en := l_rawrt_en+1;
1905 l_rawrt_cd_va.extend(1);
1906 l_rawrt_typcd_va.extend(1);
1907 l_rawrt_selstr_va.extend(1);
1908 l_rawrt_fromstr_va.extend(1);
1909 l_rawrt_wherestr_va.extend(1);
1910 l_rawrt_sqlstr_va.extend(1);
1911 l_rawrt_cd_va(l_rawrt_en) := 'PRVATCEEPYC';
1912 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1913 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1914 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1915 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_RT_VAL_RBV rbv ';
1916 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1917 ||' and rbv.acty_typ_cd = '||''''||'EEPYC'||'''';
1921 l_rawrt_typcd_va.extend(1);
1918 --
1919 l_rawrt_en := l_rawrt_en+1;
1920 l_rawrt_cd_va.extend(1);
1922 l_rawrt_selstr_va.extend(1);
1923 l_rawrt_fromstr_va.extend(1);
1924 l_rawrt_wherestr_va.extend(1);
1925 l_rawrt_sqlstr_va.extend(1);
1926 l_rawrt_cd_va(l_rawrt_en) := 'PRVATCEEPYD';
1927 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1928 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1929 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1930 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_RT_VAL_RBV rbv ';
1931 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1932 ||' and rbv.acty_typ_cd = '||''''||'EEPYD'||'''';
1933 --
1934 l_rawrt_en := l_rawrt_en+1;
1935 l_rawrt_cd_va.extend(1);
1936 l_rawrt_typcd_va.extend(1);
1937 l_rawrt_selstr_va.extend(1);
1938 l_rawrt_fromstr_va.extend(1);
1939 l_rawrt_wherestr_va.extend(1);
1940 l_rawrt_sqlstr_va.extend(1);
1941 l_rawrt_cd_va(l_rawrt_en) := 'PRVATCERPYC';
1942 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1943 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1944 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1945 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_RT_VAL_RBV rbv ';
1946 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1947 ||' and rbv.acty_typ_cd = '||''''||'ERPYC'||'''';
1948 --
1949 l_rawrt_en := l_rawrt_en+1;
1950 l_rawrt_cd_va.extend(1);
1951 l_rawrt_typcd_va.extend(1);
1952 l_rawrt_selstr_va.extend(1);
1953 l_rawrt_fromstr_va.extend(1);
1954 l_rawrt_wherestr_va.extend(1);
1955 l_rawrt_sqlstr_va.extend(1);
1956 l_rawrt_cd_va(l_rawrt_en) := 'PRVATCPBC2';
1957 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1958 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1959 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1960 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_RT_VAL_RBV rbv ';
1961 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1962 ||' and rbv.acty_typ_cd = '||''''||'PBC2'||'''';
1963 --
1964 l_rawrt_en := l_rawrt_en+1;
1965 l_rawrt_cd_va.extend(1);
1966 l_rawrt_typcd_va.extend(1);
1967 l_rawrt_selstr_va.extend(1);
1968 l_rawrt_fromstr_va.extend(1);
1969 l_rawrt_wherestr_va.extend(1);
1970 l_rawrt_sqlstr_va.extend(1);
1971 l_rawrt_cd_va(l_rawrt_en) := 'PRVATCPRDPR';
1972 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1973 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1974 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1975 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_RT_VAL_RBV rbv ';
1976 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1977 ||' and rbv.acty_typ_cd = '||''''||'PRDPR'||'''';
1978 --
1979 l_rawrt_en := l_rawrt_en+1;
1980 l_rawrt_cd_va.extend(1);
1981 l_rawrt_typcd_va.extend(1);
1982 l_rawrt_selstr_va.extend(1);
1983 l_rawrt_fromstr_va.extend(1);
1984 l_rawrt_wherestr_va.extend(1);
1985 l_rawrt_sqlstr_va.extend(1);
1986 l_rawrt_cd_va(l_rawrt_en) := 'PRVATCCS';
1987 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
1988 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
1989 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
1990 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_RT_VAL_RBV rbv ';
1991 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
1992 ||' and rbv.acty_typ_cd = '||''''||'CS'||'''';
1993 --
1994 l_rawrt_en := l_rawrt_en+1;
1995 l_rawrt_cd_va.extend(1);
1996 l_rawrt_typcd_va.extend(1);
1997 l_rawrt_selstr_va.extend(1);
1998 l_rawrt_fromstr_va.extend(1);
1999 l_rawrt_wherestr_va.extend(1);
2000 l_rawrt_sqlstr_va.extend(1);
2001 l_rawrt_cd_va(l_rawrt_en) := 'PEN';
2002 l_rawrt_typcd_va(l_rawrt_en) := 'DTTABLE';
2003 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
2004 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
2005 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_ENRT_RSLT_F_RBV rbv ';
2006 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id ';
2007 --
2008 l_rawrt_en := l_rawrt_en+1;
2009 l_rawrt_cd_va.extend(1);
2010 l_rawrt_typcd_va.extend(1);
2011 l_rawrt_selstr_va.extend(1);
2012 l_rawrt_fromstr_va.extend(1);
2013 l_rawrt_wherestr_va.extend(1);
2014 l_rawrt_sqlstr_va.extend(1);
2015 l_rawrt_cd_va(l_rawrt_en) := 'PENPGMCOBRA';
2016 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
2017 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
2018 ||' nvl(sum(nvl(rbv.object_version_number,0)),0) sum_ovn ';
2019 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_ENRT_RSLT_F_RBV rbv, ben_pgm_f pgm ';
2020 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
2021 ||' and rbv.pgm_id = pgm.pgm_id '
2022 ||' and pgm.pgm_typ_cd like '||''''||'COBRA%'||'''';
2023 --
2024 l_rawrt_en := l_rawrt_en+1;
2025 l_rawrt_cd_va.extend(1);
2026 l_rawrt_typcd_va.extend(1);
2027 l_rawrt_selstr_va.extend(1);
2028 l_rawrt_fromstr_va.extend(1);
2029 l_rawrt_wherestr_va.extend(1);
2030 l_rawrt_sqlstr_va.extend(1);
2031 l_rawrt_cd_va(l_rawrt_en) := 'PENPLNDSGN';
2032 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
2033 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
2034 ||' nvl(sum(nvl(rbv.object_version_number,0)),0) sum_ovn ';
2038 --
2035 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_ENRT_RSLT_F_RBV rbv, ben_dsgn_rqmt_f dsn ';
2036 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
2037 ||' and rbv.pl_id = dsn.pl_id ';
2039 l_rawrt_en := l_rawrt_en+1;
2040 l_rawrt_cd_va.extend(1);
2041 l_rawrt_typcd_va.extend(1);
2042 l_rawrt_selstr_va.extend(1);
2043 l_rawrt_fromstr_va.extend(1);
2044 l_rawrt_wherestr_va.extend(1);
2045 l_rawrt_sqlstr_va.extend(1);
2046 l_rawrt_cd_va(l_rawrt_en) := 'PENOIPLDSGN';
2047 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
2048 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
2049 ||' nvl(sum(nvl(rbv.object_version_number,0)),0) sum_ovn ';
2050 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_ENRT_RSLT_F_RBV rbv, ben_dsgn_rqmt_f dsn ';
2051 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
2052 ||' and rbv.oipl_id = dsn.oipl_id ';
2053 --
2054 l_rawrt_en := l_rawrt_en+1;
2055 l_rawrt_cd_va.extend(1);
2056 l_rawrt_typcd_va.extend(1);
2057 l_rawrt_selstr_va.extend(1);
2058 l_rawrt_fromstr_va.extend(1);
2059 l_rawrt_wherestr_va.extend(1);
2060 l_rawrt_sqlstr_va.extend(1);
2061 l_rawrt_cd_va(l_rawrt_en) := 'PENCVGTHRUDT';
2062 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
2063 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
2064 ||' nvl(sum(nvl(rbv.object_version_number,0)),0) sum_ovn ';
2065 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_ENRT_RSLT_F_RBV rbv ';
2066 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
2067 ||' and rbv.ENRT_CVG_THRU_DT <> to_date('||''''||'31/12/4712'||''''
2068 ||','||''''||'DD/MM/YYYY'||''''||')';
2069 --
2070 l_rawrt_en := l_rawrt_en+1;
2071 l_rawrt_cd_va.extend(1);
2072 l_rawrt_typcd_va.extend(1);
2073 l_rawrt_selstr_va.extend(1);
2074 l_rawrt_fromstr_va.extend(1);
2075 l_rawrt_wherestr_va.extend(1);
2076 l_rawrt_sqlstr_va.extend(1);
2077 l_rawrt_cd_va(l_rawrt_en) := 'PENDEENR';
2078 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
2079 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
2080 ||' nvl(sum(nvl(rbv.object_version_number,0)),0) sum_ovn ';
2081 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_ENRT_RSLT_F_RBV rbv ';
2082 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
2083 ||' and rbv.ENRT_CVG_THRU_DT <> to_date('||''''||'31/12/4712'||''''
2084 ||','||''''||'DD/MM/YYYY'||''''||')'
2085 ||' and rbv.effective_end_date <> to_date('||''''||'31/12/4712'||''''
2086 ||','||''''||'DD/MM/YYYY'||''''||')';
2087 --
2088 l_rawrt_en := l_rawrt_en+1;
2089 l_rawrt_cd_va.extend(1);
2090 l_rawrt_typcd_va.extend(1);
2091 l_rawrt_selstr_va.extend(1);
2092 l_rawrt_fromstr_va.extend(1);
2093 l_rawrt_wherestr_va.extend(1);
2094 l_rawrt_sqlstr_va.extend(1);
2095 l_rawrt_cd_va(l_rawrt_en) := 'PENSSPND';
2096 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
2097 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
2098 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
2099 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_ENRT_RSLT_F_RBV rbv ';
2100 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
2101 ||' and rbv.sspndd_flag = '||''''||'Y'||'''';
2102 --
2103 l_rawrt_en := l_rawrt_en+1;
2104 l_rawrt_cd_va.extend(1);
2105 l_rawrt_typcd_va.extend(1);
2106 l_rawrt_selstr_va.extend(1);
2107 l_rawrt_fromstr_va.extend(1);
2108 l_rawrt_wherestr_va.extend(1);
2109 l_rawrt_sqlstr_va.extend(1);
2110 l_rawrt_cd_va(l_rawrt_en) := 'PENOVRIDN';
2111 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
2112 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
2113 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
2114 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_ENRT_RSLT_F_RBV rbv ';
2115 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
2116 ||' and rbv.enrt_ovridn_flag = '||''''||'Y'||'''';
2117 --
2118 l_rawrt_en := l_rawrt_en+1;
2119 l_rawrt_cd_va.extend(1);
2120 l_rawrt_typcd_va.extend(1);
2121 l_rawrt_selstr_va.extend(1);
2122 l_rawrt_fromstr_va.extend(1);
2123 l_rawrt_wherestr_va.extend(1);
2124 l_rawrt_sqlstr_va.extend(1);
2125 l_rawrt_cd_va(l_rawrt_en) := 'PENBCKDT';
2126 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
2127 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
2128 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
2129 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_ENRT_RSLT_F_RBV rbv ';
2130 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
2131 ||' and rbv.prtt_enrt_rslt_stat_cd = '||''''||'BCKDT'||'''';
2132 --
2133 l_rawrt_en := l_rawrt_en+1;
2134 l_rawrt_cd_va.extend(1);
2135 l_rawrt_typcd_va.extend(1);
2136 l_rawrt_selstr_va.extend(1);
2137 l_rawrt_fromstr_va.extend(1);
2138 l_rawrt_wherestr_va.extend(1);
2139 l_rawrt_sqlstr_va.extend(1);
2140 l_rawrt_cd_va(l_rawrt_en) := 'PENVOIDD';
2141 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
2142 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
2143 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
2144 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_ENRT_RSLT_F_RBV rbv ';
2145 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
2149 l_rawrt_cd_va.extend(1);
2146 ||' and rbv.prtt_enrt_rslt_stat_cd = '||''''||'VOIDD'||'''';
2147 --
2148 l_rawrt_en := l_rawrt_en+1;
2150 l_rawrt_typcd_va.extend(1);
2151 l_rawrt_selstr_va.extend(1);
2152 l_rawrt_fromstr_va.extend(1);
2153 l_rawrt_wherestr_va.extend(1);
2154 l_rawrt_sqlstr_va.extend(1);
2155 l_rawrt_cd_va(l_rawrt_en) := 'PENAUTO';
2156 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
2157 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
2158 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
2159 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_ENRT_RSLT_F_RBV rbv ';
2160 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
2161 ||' and rbv.enrt_mthd_cd = '||''''||'A'||'''';
2162 --
2163 l_rawrt_en := l_rawrt_en+1;
2164 l_rawrt_cd_va.extend(1);
2165 l_rawrt_typcd_va.extend(1);
2166 l_rawrt_selstr_va.extend(1);
2167 l_rawrt_fromstr_va.extend(1);
2168 l_rawrt_wherestr_va.extend(1);
2169 l_rawrt_sqlstr_va.extend(1);
2170 l_rawrt_cd_va(l_rawrt_en) := 'PENDFLT';
2171 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
2172 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
2173 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
2174 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_ENRT_RSLT_F_RBV rbv ';
2175 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
2176 ||' and rbv.enrt_mthd_cd = '||''''||'D'||'''';
2177 --
2178 l_rawrt_en := l_rawrt_en+1;
2179 l_rawrt_cd_va.extend(1);
2180 l_rawrt_typcd_va.extend(1);
2181 l_rawrt_selstr_va.extend(1);
2182 l_rawrt_fromstr_va.extend(1);
2183 l_rawrt_wherestr_va.extend(1);
2184 l_rawrt_sqlstr_va.extend(1);
2185 l_rawrt_cd_va(l_rawrt_en) := 'PENEXPL';
2186 l_rawrt_typcd_va(l_rawrt_en) := 'TABLE';
2187 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
2188 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
2189 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PRTT_ENRT_RSLT_F_RBV rbv ';
2190 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id '
2191 ||' and rbv.enrt_mthd_cd = '||''''||'E'||'''';
2192 --
2193 l_rawrt_en := l_rawrt_en+1;
2194 l_rawrt_cd_va.extend(1);
2195 l_rawrt_typcd_va.extend(1);
2196 l_rawrt_selstr_va.extend(1);
2197 l_rawrt_fromstr_va.extend(1);
2198 l_rawrt_wherestr_va.extend(1);
2199 l_rawrt_sqlstr_va.extend(1);
2200 l_rawrt_cd_va(l_rawrt_en) := 'BPL';
2201 l_rawrt_typcd_va(l_rawrt_en) := 'DTTABLE';
2202 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
2203 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
2204 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_BNFT_PRVDD_LDGR_F_RBV rbv ';
2205 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id ';
2206 --
2207 l_rawrt_en := l_rawrt_en+1;
2208 l_rawrt_cd_va.extend(1);
2209 l_rawrt_typcd_va.extend(1);
2210 l_rawrt_selstr_va.extend(1);
2211 l_rawrt_fromstr_va.extend(1);
2212 l_rawrt_wherestr_va.extend(1);
2213 l_rawrt_sqlstr_va.extend(1);
2214 l_rawrt_cd_va(l_rawrt_en) := 'PCM';
2215 l_rawrt_typcd_va(l_rawrt_en) := 'DTTABLE';
2216 l_rawrt_selstr_va(l_rawrt_en) := ' select count(*), '
2217 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn ';
2218 l_rawrt_fromstr_va(l_rawrt_en) := ' from BEN_PER_CM_F_RBV rbv ';
2219 l_rawrt_wherestr_va(l_rawrt_en) := ' where rbv.benefit_action_id = :benefit_action_id ';
2220 --
2221 -- Populate derived rollup types
2222 --
2223 l_rt_en := 1;
2224 --
2225 for rawrt_en in l_rawrt_cd_va.first..l_rawrt_cd_va.last
2226 loop
2227 --
2228 l_sel_str := null;
2229 l_sql_str := null;
2230 --
2231 l_rt_cd_va.extend(1);
2232 l_rt_fromstr_va.extend(1);
2233 l_rt_wherestr_va.extend(1);
2234 l_rt_sqlstr_va.extend(1);
2235 --
2236 l_rt_cd_va(l_rt_en) := l_rawrt_cd_va(rawrt_en);
2237 l_rt_fromstr_va(l_rt_en) := l_rawrt_fromstr_va(rawrt_en);
2238 l_rt_wherestr_va(l_rt_en) := l_rawrt_wherestr_va(rawrt_en);
2239 --
2240 if l_rawrt_typcd_va(rawrt_en) = 'DTTABLE'
2241 then
2242 --
2243 l_sel_str := l_rawrt_selstr_va(rawrt_en)
2244 ||', min(effective_start_date) min_esd '
2245 ||', min(effective_end_date) min_eed ';
2246 --
2247 else
2248 --
2249 l_sel_str := l_rawrt_selstr_va(rawrt_en)
2250 ||', null min_esd '
2251 ||', null min_eed ';
2252 --
2253 end if;
2254 --
2255 l_sql_str := l_sel_str
2256 ||' '||l_rawrt_fromstr_va(rawrt_en)
2257 ||' '||l_rawrt_wherestr_va(rawrt_en);
2258 --
2259 l_rt_sqlstr_va(l_rt_en) := l_sql_str;
2260 l_rt_en := l_rt_en+1;
2261 --
2262 if l_rawrt_typcd_va(rawrt_en) in ('TABLE0','DTTABLE')
2263 then
2264 --
2265 l_rt_cd_va.extend(1);
2266 l_rt_fromstr_va.extend(1);
2267 l_rt_wherestr_va.extend(1);
2268 l_rt_sqlstr_va.extend(1);
2269 l_rt_cd_va(l_rt_en) := l_rawrt_cd_va(rawrt_en)||'INS';
2270 l_rt_fromstr_va(l_rt_en) := l_rawrt_fromstr_va(rawrt_en);
2271 l_rt_wherestr_va(l_rt_en) := l_rawrt_wherestr_va(rawrt_en)
2272 ||' and rbv.object_version_number = 1 ';
2273 l_rt_sqlstr_va(l_rt_en) := l_sql_str
2274 ||' and rbv.object_version_number = 1 ';
2275 l_rt_en := l_rt_en+1;
2276 --
2277 end if;
2278 --
2282 l_rt_cd_va.extend(1);
2279 if l_rawrt_typcd_va(rawrt_en) = 'TABLE0'
2280 then
2281 --
2283 l_rt_fromstr_va.extend(1);
2284 l_rt_wherestr_va.extend(1);
2285 l_rt_sqlstr_va.extend(1);
2286 l_rt_cd_va(l_rt_en) := l_rawrt_cd_va(rawrt_en)||'UPD';
2287 l_rt_fromstr_va(l_rt_en) := l_rawrt_fromstr_va(rawrt_en);
2288 l_rt_wherestr_va(l_rt_en) := l_rawrt_wherestr_va(rawrt_en)
2289 ||' and rbv.object_version_number >= 2 ';
2290 l_rt_sqlstr_va(l_rt_en) := l_sql_str
2291 ||' and rbv.object_version_number >= 2 ';
2292 l_rt_en := l_rt_en+1;
2293 --
2294 elsif l_rawrt_typcd_va(rawrt_en) = 'DTTABLE'
2295 then
2296 --
2297 l_rt_cd_va.extend(1);
2298 l_rt_fromstr_va.extend(1);
2299 l_rt_wherestr_va.extend(1);
2300 l_rt_sqlstr_va.extend(1);
2301 l_rt_cd_va(l_rt_en) := l_rawrt_cd_va(rawrt_en)||'UPDCORR';
2302 l_rt_fromstr_va(l_rt_en) := l_rawrt_fromstr_va(rawrt_en);
2303 l_rt_wherestr_va(l_rt_en) := l_rawrt_wherestr_va(rawrt_en)
2304 ||' and rbv.object_version_number = 2 ';
2305 l_rt_sqlstr_va(l_rt_en) := l_sql_str
2306 ||' and rbv.object_version_number = 2 ';
2307 l_rt_en := l_rt_en+1;
2308 --
2309 l_rt_cd_va.extend(1);
2310 l_rt_fromstr_va.extend(1);
2311 l_rt_wherestr_va.extend(1);
2312 l_rt_sqlstr_va.extend(1);
2313 l_rt_cd_va(l_rt_en) := l_rawrt_cd_va(rawrt_en)||'UPD';
2314 l_rt_fromstr_va(l_rt_en) := l_rawrt_fromstr_va(rawrt_en);
2315 l_rt_wherestr_va(l_rt_en) := l_rawrt_wherestr_va(rawrt_en)
2316 ||' and rbv.object_version_number >= 3 ';
2317 l_rt_sqlstr_va(l_rt_en) := l_sql_str
2318 ||' and rbv.object_version_number >= 3 ';
2319 l_rt_en := l_rt_en+1;
2320 --
2321 l_rt_cd_va.extend(1);
2322 l_rt_fromstr_va.extend(1);
2323 l_rt_wherestr_va.extend(1);
2324 l_rt_sqlstr_va.extend(1);
2325 l_rt_cd_va(l_rt_en) := l_rawrt_cd_va(rawrt_en)||'DEL';
2326 l_rt_fromstr_va(l_rt_en) := l_rawrt_fromstr_va(rawrt_en);
2327 l_rt_wherestr_va(l_rt_en) := l_rawrt_wherestr_va(rawrt_en)
2328 ||' and rbv.effective_end_date <> to_date('||''''||'31/12/4712'||''''
2329 ||','||''''||'DD/MM/YYYY'||''''||')';
2330 l_rt_sqlstr_va(l_rt_en) := l_sql_str
2331 ||' and rbv.effective_end_date <> to_date('||''''||'31/12/4712'||''''
2332 ||','||''''||'DD/MM/YYYY'||''''||')';
2333 l_rt_en := l_rt_en+1;
2334 --
2335 end if;
2336 --
2337 end loop;
2338 --
2339 -- Add the person action SQL
2340 --
2341 l_rt_cd_va.extend(1);
2342 l_rt_fromstr_va.extend(1);
2343 l_rt_wherestr_va.extend(1);
2344 l_rt_sqlstr_va.extend(1);
2345 --
2346 l_rt_cd_va(l_rt_en) := 'PERACT';
2347 l_rt_fromstr_va(l_rt_en) := ' from ben_person_actions pact ';
2348 l_rt_wherestr_va(l_rt_en) := ' where pact.benefit_action_id = :benefit_action_id ';
2349 --
2350 l_sel_str := ' select count(*), '
2351 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn '
2352 ||', null min_esd '
2353 ||', null min_eed ';
2354 --
2355 l_rt_sqlstr_va(l_rt_en) := l_sel_str
2356 ||' '||l_rt_fromstr_va(l_rt_en)
2357 ||' '||l_rt_wherestr_va(l_rt_en);
2358 l_rt_en := l_rt_en+1;
2359 --
2360 l_rt_cd_va.extend(1);
2361 l_rt_fromstr_va.extend(1);
2362 l_rt_wherestr_va.extend(1);
2363 l_rt_sqlstr_va.extend(1);
2364 --
2365 l_rt_cd_va(l_rt_en) := 'PERACTPROC';
2366 l_rt_fromstr_va(l_rt_en) := ' from ben_person_actions pact ';
2367 l_rt_wherestr_va(l_rt_en) := ' where pact.benefit_action_id = :benefit_action_id '
2368 ||' and pact.ACTION_STATUS_CD = '||''''||'P'||'''';
2369 --
2370 l_sel_str := ' select count(*), '
2371 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn '
2372 ||', null min_esd '
2373 ||', null min_eed ';
2374 --
2375 l_rt_sqlstr_va(l_rt_en) := l_sel_str
2376 ||' '||l_rt_fromstr_va(l_rt_en)
2377 ||' '||l_rt_wherestr_va(l_rt_en);
2378 l_rt_en := l_rt_en+1;
2379 --
2380 l_rt_cd_va.extend(1);
2381 l_rt_fromstr_va.extend(1);
2382 l_rt_wherestr_va.extend(1);
2383 l_rt_sqlstr_va.extend(1);
2384 --
2385 l_rt_cd_va(l_rt_en) := 'PERACTUNPROC';
2386 l_rt_fromstr_va(l_rt_en) := ' from ben_person_actions pact ';
2387 l_rt_wherestr_va(l_rt_en) := ' where pact.benefit_action_id = :benefit_action_id '
2388 ||' and pact.ACTION_STATUS_CD = '||''''||'U'||'''';
2389 --
2390 l_sel_str := ' select count(*), '
2391 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn '
2392 ||', null min_esd '
2393 ||', null min_eed ';
2394 --
2395 l_rt_sqlstr_va(l_rt_en) := l_sel_str
2396 ||' '||l_rt_fromstr_va(l_rt_en)
2397 ||' '||l_rt_wherestr_va(l_rt_en);
2398 --
2399 l_rt_en := l_rt_en+1;
2400 --
2401 l_rt_cd_va.extend(1);
2402 l_rt_fromstr_va.extend(1);
2403 l_rt_wherestr_va.extend(1);
2404 l_rt_sqlstr_va.extend(1);
2405 --
2406 l_rt_cd_va(l_rt_en) := 'PERACTCCRRUN';
2407 l_rt_fromstr_va(l_rt_en) := ' from ben_person_actions pact, '
2408 ||' ben_batch_ranges bbr, '
2409 ||' ben_benefit_actions bft, '
2413 ||' and pact.ACTION_STATUS_CD = '||''''||'U'||''''
2410 ||' fnd_concurrent_requests ccr '
2411 ;
2412 l_rt_wherestr_va(l_rt_en) := ' where pact.benefit_action_id = :benefit_action_id '
2414 ||' and pact.person_action_id '
2415 ||' between bbr.STARTING_PERSON_ACTION_ID and bbr.ENDING_PERSON_ACTION_ID '
2416 ||' and bbr.benefit_action_id = bft.benefit_action_id '
2417 ||' and bft.request_id = ccr.PARENT_REQUEST_ID '
2418 ||' and bbr.last_update_login = ccr.last_update_login '
2419 ||' and ccr.status_code = '||''''||'R'||'''';
2420 --
2421 l_sel_str := ' select count(*), '
2422 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn '
2423 ||', null min_esd '
2424 ||', null min_eed ';
2425 --
2426 l_rt_sqlstr_va(l_rt_en) := l_sel_str
2427 ||' '||l_rt_fromstr_va(l_rt_en)
2428 ||' '||l_rt_wherestr_va(l_rt_en);
2429 --
2430 l_rt_en := l_rt_en+1;
2431 --
2432 l_rt_cd_va.extend(1);
2433 l_rt_fromstr_va.extend(1);
2434 l_rt_wherestr_va.extend(1);
2435 l_rt_sqlstr_va.extend(1);
2436 --
2437 l_rt_cd_va(l_rt_en) := 'PERACTERR';
2438 l_rt_fromstr_va(l_rt_en) := ' from ben_person_actions pact ';
2439 l_rt_wherestr_va(l_rt_en) := ' where pact.benefit_action_id = :benefit_action_id '
2440 ||' and pact.ACTION_STATUS_CD = '||''''||'E'||'''';
2441 --
2442 l_sel_str := ' select count(*), '
2443 ||' nvl(sum(nvl(object_version_number,0)),0) sum_ovn '
2444 ||', null min_esd '
2445 ||', null min_eed ';
2446 --
2447 l_rt_sqlstr_va(l_rt_en) := l_sel_str
2448 ||' '||l_rt_fromstr_va(l_rt_en)
2449 ||' '||l_rt_wherestr_va(l_rt_en);
2450 --
2451 l_rt_en := l_rt_en+1;
2452 --
2453 l_rt_cd_va.extend(1);
2454 l_rt_fromstr_va.extend(1);
2455 l_rt_wherestr_va.extend(1);
2456 l_rt_sqlstr_va.extend(1);
2457 --
2458 l_rt_cd_va(l_rt_en) := 'WWBUGS';
2459 l_rt_fromstr_va(l_rt_en) := ' from ad_bugs abg ';
2460 l_rt_wherestr_va(l_rt_en) := ' where abg.last_update_date < '
2461 ||' (select bft.last_update_date '
2462 ||' from ben_benefit_actions bft '
2463 ||' where bft.benefit_action_id = :benefit_action_id) ';
2464 --
2465 l_sel_str := ' select count(*) '
2466 ||', 0 sum_ovn '
2467 ||', null min_esd '
2468 ||', null min_eed ';
2469 --
2470 l_rt_sqlstr_va(l_rt_en) := l_sel_str
2471 ||' '||l_rt_fromstr_va(l_rt_en)
2472 ||' '||l_rt_wherestr_va(l_rt_en);
2473 --
2474 l_rt_en := l_rt_en+1;
2475 --
2476 l_rt_cd_va.extend(1);
2477 l_rt_fromstr_va.extend(1);
2478 l_rt_wherestr_va.extend(1);
2479 l_rt_sqlstr_va.extend(1);
2480 --
2481 l_rt_cd_va(l_rt_en) := 'WWBUGSBEN';
2482 l_rt_fromstr_va(l_rt_en) := ' from ad_bugs abg ';
2483 l_rt_wherestr_va(l_rt_en) := ' where abg.last_update_date < '
2484 ||' (select bft.last_update_date '
2485 ||' from ben_benefit_actions bft '
2486 ||' where bft.benefit_action_id = :benefit_action_id) '
2487 ||' and abg.APPLICATION_SHORT_NAME = '||''''||'BEN'||'''';
2488 --
2489 l_rt_sqlstr_va(l_rt_en) := l_sel_str
2490 ||' '||l_rt_fromstr_va(l_rt_en)
2491 ||' '||l_rt_wherestr_va(l_rt_en);
2492 --
2493 l_rt_en := l_rt_en+1;
2494 --
2495 l_rt_cd_va.extend(1);
2496 l_rt_fromstr_va.extend(1);
2497 l_rt_wherestr_va.extend(1);
2498 l_rt_sqlstr_va.extend(1);
2499 --
2500 l_rt_cd_va(l_rt_en) := 'WWBUGSPER';
2501 l_rt_fromstr_va(l_rt_en) := ' from ad_bugs abg ';
2502 l_rt_wherestr_va(l_rt_en) := ' where abg.last_update_date < '
2503 ||' (select bft.last_update_date '
2504 ||' from ben_benefit_actions bft '
2505 ||' where bft.benefit_action_id = :benefit_action_id) '
2506 ||' and abg.APPLICATION_SHORT_NAME = '||''''||'PER'||'''';
2507 --
2508 l_rt_sqlstr_va(l_rt_en) := l_sel_str
2509 ||' '||l_rt_fromstr_va(l_rt_en)
2510 ||' '||l_rt_wherestr_va(l_rt_en);
2511 --
2512 l_rt_en := l_rt_en+1;
2513 --
2514 l_rt_cd_va.extend(1);
2515 l_rt_fromstr_va.extend(1);
2516 l_rt_wherestr_va.extend(1);
2517 l_rt_sqlstr_va.extend(1);
2518 --
2519 l_rt_cd_va(l_rt_en) := 'WWBUGSPAY';
2520 l_rt_fromstr_va(l_rt_en) := ' from ad_bugs abg ';
2521 l_rt_wherestr_va(l_rt_en) := ' where abg.last_update_date < '
2522 ||' (select bft.last_update_date '
2523 ||' from ben_benefit_actions bft '
2524 ||' where bft.benefit_action_id = :benefit_action_id) '
2525 ||' and abg.APPLICATION_SHORT_NAME = '||''''||'PAY'||'''';
2526 --
2527 l_rt_sqlstr_va(l_rt_en) := l_sel_str
2528 ||' '||l_rt_fromstr_va(l_rt_en)
2529 ||' '||l_rt_wherestr_va(l_rt_en);
2530 --
2531 p_rt_cd_va := l_rt_cd_va;
2532 p_rt_fromstr_va := l_rt_fromstr_va;
2533 p_rt_wherestr_va := l_rt_wherestr_va;
2534 p_rt_sqlstr_va := l_rt_sqlstr_va;
2535 --
2536 end build_rollup_sql_str;
2537 --
2541 ,p_from_str in out nocopy varchar2
2538 PROCEDURE get_rollup_code_sql_dets
2539 (p_rollup_code in varchar2
2540 --
2542 ,p_where_str in out nocopy varchar2
2543 ,p_sql_str in out nocopy varchar2
2544 )
2545 is
2546 --
2547 l_cd_va benutils.g_v2_150_table := benutils.g_v2_150_table();
2548 l_sqlstr_va benutils.g_varchar2_table := benutils.g_varchar2_table();
2549 l_fromstr_va benutils.g_varchar2_table := benutils.g_varchar2_table();
2550 l_wherestr_va benutils.g_varchar2_table := benutils.g_varchar2_table();
2551 l_rbvtypcd_va benutils.g_v2_150_table := benutils.g_v2_150_table();
2552 --
2553 begin
2554 --
2555 build_rollup_sql_str
2556 (p_rt_cd_va => l_cd_va
2557 ,p_rt_fromstr_va => l_fromstr_va
2558 ,p_rt_wherestr_va => l_wherestr_va
2559 ,p_rt_sqlstr_va => l_sqlstr_va
2560 ,p_rt_typcd_va => l_rbvtypcd_va
2561 );
2562 --
2563 if l_cd_va.count > 0
2564 then
2565 --
2566 for vaen in l_cd_va.first..l_cd_va.last
2567 loop
2568 --
2569 if upper(l_cd_va(vaen)) = upper(p_rollup_code)
2570 then
2571 --
2572 /*
2573 --
2574 -- Temporary
2575 --
2576 dbms_output.put_line(' BENRBVRU: get_sql_dets: '||upper(l_cd_va(vaen))
2577 ||' RC: '||upper(p_rollup_code)
2578 );
2579 --
2580 dbms_output.put_line(' '||substr(l_wherestr_va(vaen),1,250));
2581 --
2582 */
2583 p_from_str := l_fromstr_va(vaen);
2584 p_where_str := l_wherestr_va(vaen);
2585 p_sql_str := l_sqlstr_va(vaen);
2586 --
2587 exit;
2588 --
2589 end if;
2590 --
2591 end loop;
2592 --
2593 end if;
2594 --
2595 end get_rollup_code_sql_dets;
2596 --
2597 PROCEDURE get_rollup_code_perid_va
2598 (p_rollup_code in varchar2
2599 ,p_old_benefit_action_id in number
2600 ,p_new_benefit_action_id in number
2601 --
2602 ,p_perid_va in out nocopy benutils.g_number_table
2603 )
2604 is
2605 --
2606 TYPE cur_type IS REF CURSOR;
2607 --
2608 c_per_list cur_type;
2609 --
2610 l_old_perid_va benutils.g_number_table := benutils.g_number_table();
2611 l_old_percnt_va benutils.g_number_table := benutils.g_number_table();
2612 l_new_perid_va benutils.g_number_table := benutils.g_number_table();
2613 l_new_percnt_va benutils.g_number_table := benutils.g_number_table();
2614 l_mmperid_va benutils.g_number_table := benutils.g_number_table();
2615 --
2616 l_sel_str long;
2617 l_from_str long;
2618 l_where_str long;
2619 l_groupby_str long;
2620 l_orderby_str long;
2621 l_sql_str long;
2622 --
2623 l_vaen pls_integer;
2624 l_person_id number;
2625 l_cnt number;
2626 l_perid_match boolean;
2627 l_mmvaen pls_integer;
2628 --
2629 l_new_totcnt pls_integer;
2630 l_old_totcnt pls_integer;
2631 --
2632 begin
2633 --
2634 -- Exclude non person related rollups
2635 --
2636 if instr(p_rollup_code,'WWBUGS') > 0
2637 then
2638 --
2639 -- Init collection
2640 --
2641 p_perid_va := l_mmperid_va;
2642 return;
2643 --
2644 else
2645 --
2646 if instr(p_rollup_code,'PERACT') > 0
2647 then
2648 --
2649 -- Build SQL statement to get PACT info
2650 --
2651 l_sel_str := ' select pact.person_id, count(*) cnt ';
2652 l_from_str := ' from ben_person_actions pact ';
2653 l_where_str := ' where pact.benefit_action_id = :benefit_action_id ';
2654 l_groupby_str := ' group by pact.person_id ';
2655 l_orderby_str := ' order by count(*) desc ';
2656 --
2657 else
2658 --
2659 -- Get rollup sql details
2660 --
2661 ben_rollup_rbvs.get_rollup_code_sql_dets
2662 (p_rollup_code => p_rollup_code
2663 --
2664 ,p_from_str => l_from_str
2665 ,p_where_str => l_where_str
2666 ,p_sql_str => l_sql_str
2667 );
2668 --
2669 -- Build SQL statement to get PACT info
2670 --
2671 l_sel_str := 'select pact.person_id, count(*) cnt ';
2672 l_from_str := l_from_str||', ben_person_actions pact ';
2673 l_where_str := l_where_str||' and rbv.person_action_id = pact.person_action_id ';
2674 l_groupby_str := ' group by pact.person_id ';
2675 l_orderby_str := ' order by count(*) desc ';
2676 --
2677 end if;
2678 --
2679 l_sql_str := l_sel_str
2680 ||' '||l_from_str
2681 ||' '||l_where_str
2682 ||' '||l_groupby_str
2683 ||' '||l_orderby_str;
2684 --
2685 l_vaen := 1;
2686 --
2687 open c_per_list FOR l_sql_str using p_old_benefit_action_id;
2688 loop
2689 l_person_id := null;
2690 l_cnt := null;
2691 FETCH c_per_list INTO l_person_id,l_cnt;
2692 EXIT WHEN c_per_list%NOTFOUND;
2693 --
2694 l_old_perid_va.extend(1);
2695 l_old_percnt_va.extend(1);
2696 --
2697 l_old_perid_va(l_vaen) := l_person_id;
2698 l_old_percnt_va(l_vaen) := l_cnt;
2699 l_vaen := l_vaen+1;
2700 --
2701 end loop;
2702 close c_per_list;
2703 --
2704 l_vaen := 1;
2705 --
2706 open c_per_list FOR l_sql_str using p_new_benefit_action_id;
2707 loop
2711 EXIT WHEN c_per_list%NOTFOUND;
2708 l_person_id := null;
2709 l_cnt := null;
2710 FETCH c_per_list INTO l_person_id,l_cnt;
2712 --
2713 l_new_perid_va.extend(1);
2714 l_new_percnt_va.extend(1);
2715 --
2716 l_new_perid_va(l_vaen) := l_person_id;
2717 l_new_percnt_va(l_vaen) := l_cnt;
2718 l_vaen := l_vaen+1;
2719 --
2720 end loop;
2721 close c_per_list;
2722 --
2723 -- Check differences between old and new list
2724 --
2725 if l_new_perid_va.count <> l_old_perid_va.count
2726 then
2727 --
2728 if l_new_perid_va.count > l_old_perid_va.count
2729 then
2730 --
2731 l_mmvaen := 1;
2732 --
2733 for vaen in l_new_perid_va.first..l_new_perid_va.last
2734 loop
2735 --
2736 l_perid_match := FALSE;
2737 --
2738 if l_old_perid_va.count > 0
2739 then
2740 --
2741 for subvaen in l_old_perid_va.first..l_old_perid_va.last
2742 loop
2743 --
2744 if l_old_perid_va(subvaen) = l_new_perid_va(vaen)
2745 then
2746 --
2747 l_perid_match := TRUE;
2748 exit;
2749 --
2750 end if;
2751 --
2752 end loop;
2753 --
2754 if not l_perid_match
2755 then
2756 --
2757 l_mmperid_va.extend(1);
2758 l_mmperid_va(l_mmvaen) := l_new_perid_va(vaen);
2759 l_mmvaen := l_mmvaen+1;
2760 --
2761 end if;
2762 --
2763 else
2764 --
2765 l_mmperid_va := l_new_perid_va;
2766 --
2767 end if;
2768 --
2769 end loop;
2770 --
2771 elsif l_old_perid_va.count > l_new_perid_va.count
2772 then
2773 --
2774 l_mmvaen := 1;
2775 --
2776 for vaen in l_old_perid_va.first..l_old_perid_va.last
2777 loop
2778 --
2779 l_perid_match := FALSE;
2780 --
2781 if l_new_perid_va.count > 0
2782 then
2783 --
2784 for subvaen in l_new_perid_va.first..l_new_perid_va.last
2785 loop
2786 --
2787 if l_new_perid_va(subvaen) = l_old_perid_va(vaen)
2788 then
2789 --
2790 l_perid_match := TRUE;
2791 exit;
2792 --
2793 end if;
2794 --
2795 end loop;
2796 --
2797 if not l_perid_match
2798 then
2799 --
2800 l_mmperid_va.extend(1);
2801 l_mmperid_va(l_mmvaen) := l_old_perid_va(vaen);
2802 l_mmvaen := l_mmvaen+1;
2803 --
2804 end if;
2805 --
2806 else
2807 --
2808 l_mmperid_va := l_old_perid_va;
2809 --
2810 end if;
2811 --
2812 end loop;
2813 --
2814 end if;
2815 --
2816 else
2817 --
2818 -- Check for person count totals
2819 --
2820 if l_new_perid_va.count > 0
2821 then
2822 --
2823 l_new_totcnt := 0;
2824 --
2825 for vaen in l_new_perid_va.first..l_new_perid_va.last
2826 loop
2827 --
2828 l_new_totcnt := l_new_totcnt+l_new_percnt_va(vaen);
2829 --
2830 end loop;
2831 --
2832 end if;
2833 --
2834 if l_old_perid_va.count > 0
2835 then
2836 --
2837 l_old_totcnt := 0;
2838 --
2839 for vaen in l_old_perid_va.first..l_old_perid_va.last
2840 loop
2841 --
2842 l_old_totcnt := l_old_totcnt+l_old_percnt_va(vaen);
2843 --
2844 end loop;
2845 --
2846 end if;
2847 --
2848 -- Check for person count descrepancies
2849 --
2850 if l_new_totcnt <> l_old_totcnt
2851 then
2852 --
2853 l_mmvaen := 1;
2854 --
2855 for vaen in l_new_perid_va.first..l_new_perid_va.last
2856 loop
2857 --
2858 l_perid_match := FALSE;
2859 --
2860 if l_old_perid_va.count > 0
2861 then
2862 --
2863 for subvaen in l_old_perid_va.first..l_old_perid_va.last
2864 loop
2865 --
2866 if l_old_perid_va(subvaen) = l_new_perid_va(vaen)
2867 and l_old_percnt_va(subvaen) = l_new_percnt_va(vaen)
2868 then
2869 --
2870 l_perid_match := TRUE;
2871 exit;
2872 --
2873 end if;
2874 --
2875 end loop;
2876 --
2877 if not l_perid_match
2878 then
2879 --
2880 l_mmperid_va.extend(1);
2881 l_mmperid_va(l_mmvaen) := l_new_perid_va(vaen);
2882 l_mmvaen := l_mmvaen+1;
2883 --
2884 end if;
2885 --
2886 else
2887 --
2888 l_mmperid_va := l_new_perid_va;
2889 --
2890 end if;
2894 end if;
2891 --
2892 end loop;
2893 --
2895 --
2896 end if;
2897 --
2898 end if;
2899 --
2900 p_perid_va := l_mmperid_va;
2901 --
2902 end get_rollup_code_perid_va;
2903 --
2904 PROCEDURE get_oldnewcombkey_va
2905 (p_sql_str in varchar2
2906 ,p_old_benefit_action_id in number
2907 ,p_new_benefit_action_id in number
2908 ,p_keycol_cnt in number
2909 --
2910 ,p_old_perid_va in out nocopy benutils.g_number_table
2911 ,p_old_combid_va in out nocopy benutils.g_number_table
2912 ,p_old_combid2_va in out nocopy benutils.g_number_table
2913 ,p_old_combid3_va in out nocopy benutils.g_number_table
2914 ,p_old_combid4_va in out nocopy benutils.g_number_table
2915 ,p_old_cnt_va in out nocopy benutils.g_number_table
2916 ,p_new_perid_va in out nocopy benutils.g_number_table
2917 ,p_new_combid_va in out nocopy benutils.g_number_table
2918 ,p_new_combid2_va in out nocopy benutils.g_number_table
2919 ,p_new_combid3_va in out nocopy benutils.g_number_table
2920 ,p_new_combid4_va in out nocopy benutils.g_number_table
2921 ,p_new_cnt_va in out nocopy benutils.g_number_table
2922 )
2923 is
2924 --
2925 TYPE cur_type IS REF CURSOR;
2926 --
2927 c_comb_list cur_type;
2928 --
2929 l_old_perid_va benutils.g_number_table := benutils.g_number_table();
2930 l_old_combid_va benutils.g_number_table := benutils.g_number_table();
2931 l_old_combid2_va benutils.g_number_table := benutils.g_number_table();
2932 l_old_combid3_va benutils.g_number_table := benutils.g_number_table();
2933 l_old_combid4_va benutils.g_number_table := benutils.g_number_table();
2934 l_old_cnt_va benutils.g_number_table := benutils.g_number_table();
2935 l_new_perid_va benutils.g_number_table := benutils.g_number_table();
2936 l_new_combid_va benutils.g_number_table := benutils.g_number_table();
2937 l_new_combid2_va benutils.g_number_table := benutils.g_number_table();
2938 l_new_combid3_va benutils.g_number_table := benutils.g_number_table();
2939 l_new_combid4_va benutils.g_number_table := benutils.g_number_table();
2940 l_new_cnt_va benutils.g_number_table := benutils.g_number_table();
2941 --
2942 l_vaen pls_integer;
2943 --
2944 l_person_id number;
2945 l_comb_id number;
2946 l_comb_id2 number;
2947 l_comb_id3 number;
2948 l_comb_id4 number;
2949 l_cnt number;
2950 --
2951 l_keycol_cnt number;
2952 --
2953 begin
2954 --
2955 /*
2956 --
2957 -- Temporary
2958 --
2959 dbms_output.put_line(' BENRBVRU: get_oldnewcombkey_va: OldBFT: '||p_old_benefit_action_id
2960 ||' NewBFT: '||p_new_benefit_action_id
2961 );
2962 dbms_output.put_line(' '||substr(p_sql_str,1,250));
2963 --
2964 */
2965 l_old_perid_va.delete;
2966 l_old_cnt_va.delete;
2967 l_old_combid_va.delete;
2968 l_old_combid2_va.delete;
2969 l_old_combid3_va.delete;
2970 l_old_combid4_va.delete;
2971 l_new_perid_va.delete;
2972 l_new_cnt_va.delete;
2973 l_new_combid_va.delete;
2974 l_new_combid2_va.delete;
2975 l_new_combid3_va.delete;
2976 l_new_combid4_va.delete;
2977 --
2978 l_vaen := 1;
2979 --
2980 begin
2981 --
2982 open c_comb_list FOR p_sql_str using p_old_benefit_action_id;
2983 --
2984 exception
2985 when others then
2986 --
2987 /*
2988 --
2989 -- Temporary
2990 --
2991 dbms_output.put_line('-- p_oldbftid: '||p_old_benefit_action_id);
2992 dbms_output.put_line('-- p_sql_str: '||substr(p_sql_str,1,200));
2993 --
2994 */
2995 --
2996 raise;
2997 --
2998 end;
2999 --
3000 loop
3001 --
3002 l_person_id := null;
3003 l_comb_id := null;
3004 l_comb_id2 := null;
3005 l_comb_id3 := null;
3006 l_comb_id4 := null;
3007 l_cnt := null;
3008 --
3009 if p_keycol_cnt = 0
3010 then
3011 --
3012 FETCH c_comb_list INTO l_person_id, l_cnt;
3013 --
3014 elsif p_keycol_cnt = 1
3015 then
3016 --
3017 FETCH c_comb_list INTO l_person_id,l_comb_id, l_cnt;
3018 --
3019 elsif p_keycol_cnt = 2
3020 then
3021 --
3022 FETCH c_comb_list INTO l_person_id,l_comb_id,l_comb_id2, l_cnt;
3023 --
3024 elsif p_keycol_cnt = 3
3025 then
3026 --
3027 FETCH c_comb_list INTO l_person_id,l_comb_id,l_comb_id2,l_comb_id3, l_cnt;
3028 --
3029 elsif p_keycol_cnt = 4
3030 then
3031 --
3032 FETCH c_comb_list INTO l_person_id,l_comb_id,l_comb_id2,l_comb_id3,l_comb_id4, l_cnt;
3033 --
3034 end if;
3035 --
3036 EXIT WHEN c_comb_list%NOTFOUND;
3037 --
3038 l_old_perid_va.extend(1);
3039 l_old_combid_va.extend(1);
3040 l_old_combid2_va.extend(1);
3041 l_old_combid3_va.extend(1);
3042 l_old_combid4_va.extend(1);
3043 l_old_cnt_va.extend(1);
3044 --
3045 l_old_perid_va(l_vaen) := l_person_id;
3046 l_old_combid_va(l_vaen) := l_comb_id;
3047 l_old_combid2_va(l_vaen) := l_comb_id2;
3048 l_old_combid3_va(l_vaen) := l_comb_id3;
3049 l_old_combid4_va(l_vaen) := l_comb_id4;
3050 l_old_cnt_va(l_vaen) := l_cnt;
3051 l_vaen := l_vaen+1;
3052 --
3053 end loop;
3054 close c_comb_list;
3055 --
3056 l_vaen := 1;
3060 open c_comb_list FOR p_sql_str using p_new_benefit_action_id;
3057 --
3058 begin
3059 --
3061 --
3062 exception
3063 when others then
3064 --
3065 /*
3066 --
3067 -- Temporary
3068 --
3069 dbms_output.put_line('-- p_newbftid: '||p_new_benefit_action_id);
3070 dbms_output.put_line('-- p_sql_str: '||substr(p_sql_str,1,200));
3071 --
3072 */
3073 --
3074 raise;
3075 --
3076 end;
3077 --
3078 loop
3079 --
3080 l_person_id := null;
3081 l_comb_id := null;
3082 l_comb_id2 := null;
3083 l_comb_id3 := null;
3084 l_comb_id4 := null;
3085 l_cnt := null;
3086 --
3087 if p_keycol_cnt = 0
3088 then
3089 --
3090 FETCH c_comb_list INTO l_person_id, l_cnt;
3091 --
3092 elsif p_keycol_cnt = 1
3093 then
3094 --
3095 FETCH c_comb_list INTO l_person_id,l_comb_id, l_cnt;
3096 --
3097 elsif p_keycol_cnt = 2
3098 then
3099 --
3100 FETCH c_comb_list INTO l_person_id,l_comb_id, l_comb_id2, l_cnt;
3101 --
3102 elsif p_keycol_cnt = 3
3103 then
3104 --
3105 FETCH c_comb_list INTO l_person_id,l_comb_id, l_comb_id2, l_comb_id3, l_cnt;
3106 --
3107 elsif p_keycol_cnt = 4
3108 then
3109 --
3110 FETCH c_comb_list INTO l_person_id,l_comb_id, l_comb_id2, l_comb_id3, l_comb_id4, l_cnt;
3111 --
3112 end if;
3113 --
3114 EXIT WHEN c_comb_list%NOTFOUND;
3115 --
3116 l_new_perid_va.extend(1);
3117 l_new_combid_va.extend(1);
3118 l_new_combid2_va.extend(1);
3119 l_new_combid3_va.extend(1);
3120 l_new_combid4_va.extend(1);
3121 l_new_cnt_va.extend(1);
3122 --
3123 l_new_perid_va(l_vaen) := l_person_id;
3124 l_new_combid_va(l_vaen) := l_comb_id;
3125 l_new_combid2_va(l_vaen) := l_comb_id2;
3126 l_new_combid3_va(l_vaen) := l_comb_id3;
3127 l_new_combid4_va(l_vaen) := l_comb_id4;
3128 l_new_cnt_va(l_vaen) := l_cnt;
3129 l_vaen := l_vaen+1;
3130 --
3131 end loop;
3132 close c_comb_list;
3133 --
3134 p_old_perid_va := l_old_perid_va;
3135 p_old_combid_va := l_old_combid_va;
3136 p_old_combid2_va := l_old_combid2_va;
3137 p_old_combid3_va := l_old_combid3_va;
3138 p_old_combid4_va := l_old_combid4_va;
3139 p_old_cnt_va := l_old_cnt_va;
3140 p_new_perid_va := l_new_perid_va;
3141 p_new_combid_va := l_new_combid_va;
3142 p_new_combid2_va := l_new_combid2_va;
3143 p_new_combid3_va := l_new_combid3_va;
3144 p_new_combid4_va := l_new_combid4_va;
3145 p_new_cnt_va := l_new_cnt_va;
3146 --
3147 end get_oldnewcombkey_va;
3148 --
3149 PROCEDURE get_rollup_code_combkey_va
3150 (p_rollup_code in varchar2
3151 ,p_old_benefit_action_id in number
3152 ,p_new_benefit_action_id in number
3153 --
3154 ,p_perid_va in out nocopy benutils.g_number_table
3155 ,p_perlud_va in out nocopy benutils.g_date_table
3156 ,p_combnm_va in out nocopy benutils.g_varchar2_table
3157 ,p_combnm2_va in out nocopy benutils.g_varchar2_table
3158 ,p_combnm3_va in out nocopy benutils.g_varchar2_table
3159 ,p_combnm4_va in out nocopy benutils.g_varchar2_table
3160 ,p_combid_va in out nocopy benutils.g_number_table
3161 ,p_combid2_va in out nocopy benutils.g_number_table
3162 ,p_combid3_va in out nocopy benutils.g_number_table
3163 ,p_combid4_va in out nocopy benutils.g_number_table
3164 ,p_cnt_va in out nocopy benutils.g_number_table
3165 )
3166 is
3167 --
3168 TYPE cur_type IS REF CURSOR;
3169 --
3170 c_rbvmaxlud cur_type;
3171 --
3172 l_old_perid_va benutils.g_number_table := benutils.g_number_table();
3173 l_old_combid_va benutils.g_number_table := benutils.g_number_table();
3174 l_old_combid2_va benutils.g_number_table := benutils.g_number_table();
3175 l_old_combid3_va benutils.g_number_table := benutils.g_number_table();
3176 l_old_combid4_va benutils.g_number_table := benutils.g_number_table();
3177 l_old_cnt_va benutils.g_number_table := benutils.g_number_table();
3178 l_new_perid_va benutils.g_number_table := benutils.g_number_table();
3179 l_new_combid_va benutils.g_number_table := benutils.g_number_table();
3180 l_new_combid2_va benutils.g_number_table := benutils.g_number_table();
3181 l_new_combid3_va benutils.g_number_table := benutils.g_number_table();
3182 l_new_combid4_va benutils.g_number_table := benutils.g_number_table();
3183 l_new_cnt_va benutils.g_number_table := benutils.g_number_table();
3184 l_mmperid_va benutils.g_number_table := benutils.g_number_table();
3185 l_mmperlud_va benutils.g_date_table := benutils.g_date_table();
3186 l_mmcombnm_va benutils.g_varchar2_table := benutils.g_varchar2_table();
3187 l_mmcombnm2_va benutils.g_varchar2_table := benutils.g_varchar2_table();
3188 l_mmcombnm3_va benutils.g_varchar2_table := benutils.g_varchar2_table();
3189 l_mmcombnm4_va benutils.g_varchar2_table := benutils.g_varchar2_table();
3190 l_mmcombid_va benutils.g_number_table := benutils.g_number_table();
3191 l_mmcombid2_va benutils.g_number_table := benutils.g_number_table();
3192 l_mmcombid3_va benutils.g_number_table := benutils.g_number_table();
3193 l_mmcombid4_va benutils.g_number_table := benutils.g_number_table();
3194 l_mmcnt_va benutils.g_number_table := benutils.g_number_table();
3195 --
3196 l_keycol_str long;
3197 --
3198 l_sel_str long;
3199 l_from_str long;
3200 l_where_str long;
3201 l_groupby_str long;
3205 l_perid_match boolean;
3202 l_orderby_str long;
3203 l_sql_str long;
3204 --
3206 l_mmvaen pls_integer;
3207 --
3208 l_new_totcnt pls_integer;
3209 l_old_totcnt pls_integer;
3210 --
3211 l_keycol_cnt pls_integer;
3212 l_keycolnm varchar2(100);
3213 l_keycolnm2 varchar2(100);
3214 l_keycolnm3 varchar2(100);
3215 l_keycolnm4 varchar2(100);
3216 l_fromclapp_str long;
3217 l_whereclapp_str long;
3218 --
3219 l_per_mxlud date;
3220 l_perdata_changes boolean;
3221 l_mmcombid_cnt pls_integer;
3222 l_mmcombid2_cnt pls_integer;
3223 l_mmcombid3_cnt pls_integer;
3224 l_mmcombid4_cnt pls_integer;
3225 --
3226 begin
3227 --
3228 -- Exclude non person related rollups
3229 --
3230 if instr(p_rollup_code,'WWBUGS') > 0
3231 then
3232 --
3233 -- Init collection
3234 --
3235 p_perid_va := l_mmperid_va;
3236 return;
3237 --
3238 --
3239 else
3240 --
3241 -- Hard coded need to make generic
3242 --
3243 l_keycol_cnt := 0;
3244 l_fromclapp_str := null;
3245 l_whereclapp_str := null;
3246 --
3247 if instr(p_rollup_code,'PEP') > 0
3248 then
3249 --
3250 l_keycol_cnt := 1;
3251 l_keycolnm := 'PGM_ID';
3252 l_keycol_str := ',rbv.'||l_keycolnm||' ';
3253 --
3254 l_keycol_cnt := 2;
3255 l_keycolnm2 := 'PL_ID';
3256 l_keycol_str := l_keycol_str||',rbv.'||l_keycolnm2||' ';
3257 --
3258 l_keycol_cnt := 3;
3259 l_keycolnm3 := 'PLIP_ID';
3260 l_keycol_str := l_keycol_str||',rbv.'||l_keycolnm3||' ';
3261 --
3262 l_keycol_cnt := 4;
3263 l_keycolnm4 := 'PTIP_ID';
3264 l_keycol_str := l_keycol_str||',rbv.'||l_keycolnm4||' ';
3265 --
3266 elsif instr(p_rollup_code,'EPO') > 0
3267 then
3268 --
3269 l_keycol_cnt := 1;
3270 l_keycolnm := 'OPT_ID';
3271 l_keycol_str := ',rbv.'||l_keycolnm||' ';
3272 --
3273 elsif instr(p_rollup_code,'EPE') > 0
3274 then
3275 --
3276 l_keycol_cnt := 1;
3277 l_keycolnm := 'PGM_ID';
3278 l_keycol_str := ',rbv.'||l_keycolnm||' ';
3279 --
3280 l_keycol_cnt := 2;
3281 l_keycolnm2 := 'PL_ID';
3282 l_keycol_str := l_keycol_str||',rbv.'||l_keycolnm2||' ';
3283 --
3284 elsif instr(p_rollup_code,'PEN') > 0
3285 then
3286 --
3287 l_keycol_cnt := 1;
3288 l_keycolnm := 'PL_ID';
3289 l_keycol_str := ',rbv.'||l_keycolnm||' ';
3290 --
3291 elsif instr(p_rollup_code,'PRV') > 0
3292 then
3293 --
3294 l_keycol_cnt := 1;
3295 l_keycolnm := 'PL_ID';
3296 l_keycol_str := ',rbv1.'||l_keycolnm||' ';
3297 --
3298 l_fromclapp_str := ', ben_prtt_enrt_rslt_f rbv1 ';
3299 l_whereclapp_str := ' and rbv.PRTT_ENRT_RSLT_ID = rbv1.PRTT_ENRT_RSLT_ID ';
3300 --
3301 else
3302 --
3303 l_keycol_cnt := 0;
3304 l_keycol_str := ' ';
3305 --
3306 end if;
3307 --
3308 -- Get rollup sql details
3309 --
3310 ben_rollup_rbvs.get_rollup_code_sql_dets
3311 (p_rollup_code => p_rollup_code
3312 --
3313 ,p_from_str => l_from_str
3314 ,p_where_str => l_where_str
3315 ,p_sql_str => l_sql_str
3316 );
3317 --
3318 -- Check for person action rollup types
3319 --
3320 if instr(p_rollup_code,'PERACT') > 0
3321 then
3322 --
3323 -- Build SQL statement to get PACT info
3324 --
3325 l_sel_str := ' select pact.person_id, count(*) cnt ';
3326 l_from_str := l_from_str;
3327 l_where_str := l_where_str;
3328 l_groupby_str := ' group by pact.person_id ';
3329 l_orderby_str := ' order by 1 desc ';
3330 --
3331 else
3332 /*
3333 --
3334 -- Temporary
3335 --
3336 dbms_output.put_line('- BENRBVRU: Roll: '||p_rollup_code
3337 ||' FrCL: '||l_from_str
3338 ||' WhCL: '||l_where_str
3339 );
3340 dbms_output.put_line('-- l_keycol_str: '||l_keycol_str
3341 );
3342 --
3343 */
3344 --
3345 -- Build SQL statement to get PACT info
3346 --
3347 l_sel_str := 'select pact.person_id '||l_keycol_str||', count(*) cnt ';
3348 l_from_str := l_from_str||', ben_person_actions pact ';
3349 l_where_str := l_where_str||' and rbv.person_action_id = pact.person_action_id ';
3350 l_groupby_str := ' group by pact.person_id '||l_keycol_str;
3351 l_orderby_str := ' order by 1 desc ';
3352 --
3353 end if;
3354 --
3355 -- Add to from and where clauses for foreign key references
3356 --
3357 if l_fromclapp_str is not null
3358 or l_whereclapp_str is not null
3359 then
3360 --
3361 l_from_str := l_from_str||' '||l_fromclapp_str;
3362 l_where_str := l_where_str||' '||l_whereclapp_str;
3363 --
3364 end if;
3365 --
3366 l_sql_str := l_sel_str
3367 ||' '||l_from_str
3368 ||' '||l_where_str
3369 ||' '||l_groupby_str
3370 ||' '||l_orderby_str;
3371 --
3372 get_oldnewcombkey_va
3373 (p_sql_str => l_sql_str
3374 ,p_old_benefit_action_id => p_old_benefit_action_id
3378 ,p_old_perid_va => l_old_perid_va
3375 ,p_new_benefit_action_id => p_new_benefit_action_id
3376 ,p_keycol_cnt => l_keycol_cnt
3377 --
3379 ,p_old_combid_va => l_old_combid_va
3380 ,p_old_combid2_va => l_old_combid2_va
3381 ,p_old_combid3_va => l_old_combid3_va
3382 ,p_old_combid4_va => l_old_combid4_va
3383 ,p_old_cnt_va => l_old_cnt_va
3384 ,p_new_perid_va => l_new_perid_va
3385 ,p_new_combid_va => l_new_combid_va
3386 ,p_new_combid2_va => l_new_combid2_va
3387 ,p_new_combid3_va => l_new_combid3_va
3388 ,p_new_combid4_va => l_new_combid4_va
3389 ,p_new_cnt_va => l_new_cnt_va
3390 );
3391 /*
3392 --
3393 -- Temporary
3394 --
3395 dbms_output.put_line(' First: l_new_perid_va.count: '||l_new_perid_va.count
3396 ||' l_old_perid_va.count: '||l_old_perid_va.count
3397 ||' OCMIDCnt: '||l_old_combid_va.count
3398 ||' OCMID2Cnt: '||l_old_combid2_va.count
3399 ||' OCMID3Cnt: '||l_old_combid3_va.count
3400 );
3401 --
3402 */
3403 --
3404 -- Check if any rows were found if not then re-try with rollup type
3405 -- specific criteria
3406 --
3407 if l_new_perid_va.count = 0
3408 and l_old_perid_va.count = 0
3409 then
3410 --
3411 if instr(p_rollup_code,'PRV') > 0
3412 then
3413 --
3414 -- Join to live table rather than RBV table
3415 --
3416 l_keycol_cnt := 1;
3417 l_keycolnm := 'PL_ID';
3418 l_keycol_str := ',rbv1.'||l_keycolnm||' ';
3419 --
3420 l_fromclapp_str := ', ben_prtt_enrt_rslt_f_rbv rbv1 ';
3421 l_whereclapp_str := ' and rbv.PRTT_ENRT_RSLT_ID = rbv1.PRTT_ENRT_RSLT_ID ';
3422 --
3423 -- Get rollup sql details
3424 --
3425 ben_rollup_rbvs.get_rollup_code_sql_dets
3426 (p_rollup_code => p_rollup_code
3427 --
3428 ,p_from_str => l_from_str
3429 ,p_where_str => l_where_str
3430 ,p_sql_str => l_sql_str
3431 );
3432 --
3433 -- Build SQL statement to get PACT info
3434 --
3435 l_sel_str := 'select pact.person_id '||l_keycol_str||', count(*) cnt ';
3436 l_from_str := l_from_str||', ben_person_actions pact ';
3437 l_where_str := l_where_str||' and rbv.person_action_id = pact.person_action_id ';
3438 l_groupby_str := ' group by pact.person_id '||l_keycol_str;
3439 l_orderby_str := ' order by 1 desc ';
3440 --
3441 -- Add to from and where clauses for foreign key references
3442 --
3443 if l_fromclapp_str is not null
3444 or l_whereclapp_str is not null
3445 then
3446 --
3447 l_from_str := l_from_str||' '||l_fromclapp_str;
3448 l_where_str := l_where_str||' '||l_whereclapp_str;
3449 --
3450 end if;
3451 --
3452 l_sql_str := l_sel_str
3453 ||' '||l_from_str
3454 ||' '||l_where_str
3455 ||' '||l_groupby_str
3456 ||' '||l_orderby_str;
3457 --
3458 get_oldnewcombkey_va
3459 (p_sql_str => l_sql_str
3460 ,p_old_benefit_action_id => p_old_benefit_action_id
3461 ,p_new_benefit_action_id => p_new_benefit_action_id
3462 ,p_keycol_cnt => l_keycol_cnt
3463 --
3464 ,p_old_perid_va => l_old_perid_va
3465 ,p_old_combid_va => l_old_combid_va
3466 ,p_old_combid2_va => l_old_combid2_va
3467 ,p_old_combid3_va => l_old_combid3_va
3468 ,p_old_combid4_va => l_old_combid4_va
3469 ,p_old_cnt_va => l_old_cnt_va
3470 ,p_new_perid_va => l_new_perid_va
3471 ,p_new_combid_va => l_new_combid_va
3472 ,p_new_combid2_va => l_new_combid2_va
3473 ,p_new_combid3_va => l_new_combid3_va
3474 ,p_new_combid4_va => l_new_combid4_va
3475 ,p_new_cnt_va => l_new_cnt_va
3476 );
3477 --
3478 end if;
3479 --
3480 end if;
3481 --
3482 /*
3483 --
3484 -- Temporary
3485 --
3486 dbms_output.put_line(' Second: OBFTID: '||p_old_benefit_action_id
3487 ||' NBFTID: '||p_new_benefit_action_id
3488 ||' RlCd: '||p_rollup_code
3489 ||' Nperid cnt: '||l_new_perid_va.count
3490 ||' Operid cnt: '||l_old_perid_va.count
3491 ||' OCMIDCnt: '||l_old_combid_va.count
3492 ||' OCMID2Cnt: '||l_old_combid2_va.count
3493 ||' OCMID3Cnt: '||l_old_combid3_va.count
3494 );
3495 --
3496 dbms_output.put_line(' - NCMIDCnt: '||l_new_combid_va.count
3497 ||' NCMID2Cnt: '||l_new_combid2_va.count
3498 ||' NCMID3Cnt: '||l_new_combid3_va.count
3499 );
3500 --
3501 */
3502 --
3503 -- Check differences between old and new list
3504 --
3505 l_mmperid_va.delete;
3506 l_mmcombid_va.delete;
3507 l_mmcombid2_va.delete;
3508 l_mmcombid3_va.delete;
3509 l_mmcombid4_va.delete;
3510 l_mmcnt_va.delete;
3511 --
3512 l_mmcombid_cnt := l_mmcombid_va.count;
3513 l_mmcombid2_cnt := l_mmcombid2_va.count;
3517 if l_new_perid_va.count <> l_old_perid_va.count
3514 l_mmcombid3_cnt := l_mmcombid3_va.count;
3515 l_mmcombid4_cnt := l_mmcombid4_va.count;
3516 --
3518 then
3519 --
3520 if l_new_perid_va.count > l_old_perid_va.count
3521 then
3522 --
3523 l_mmvaen := 1;
3524 --
3525 for vaen in l_new_perid_va.first..l_new_perid_va.last
3526 loop
3527 --
3528 l_perid_match := FALSE;
3529 --
3530 if l_old_perid_va.count > 0
3531 then
3532 --
3533 for subvaen in l_old_perid_va.first..l_old_perid_va.last
3534 loop
3535 --
3536 if l_old_perid_va(subvaen) = l_new_perid_va(vaen)
3537 and nvl(l_old_combid_va(subvaen),-999) = nvl(l_new_combid_va(vaen),-999)
3538 and nvl(l_old_combid2_va(subvaen),-999) = nvl(l_new_combid2_va(vaen),-999)
3539 and nvl(l_old_combid3_va(subvaen),-999) = nvl(l_new_combid3_va(vaen),-999)
3540 and nvl(l_old_combid4_va(subvaen),-999) = nvl(l_new_combid4_va(vaen),-999)
3541 then
3542 --
3543 l_perid_match := TRUE;
3544 exit;
3545 --
3546 end if;
3547 --
3548 end loop;
3549 --
3550 if not l_perid_match
3551 then
3552 --
3553 l_mmperid_va.extend(1);
3554 l_mmcnt_va.extend(1);
3555 l_mmperid_va(l_mmvaen) := l_new_perid_va(vaen);
3556 l_mmcnt_va(l_mmvaen) := l_new_cnt_va(vaen);
3557 --
3558 l_mmcombid_va.extend(1);
3559 l_mmcombid_va(l_mmvaen) := l_new_combid_va(vaen);
3560 --
3561 l_mmcombid2_va.extend(1);
3562 l_mmcombid2_va(l_mmvaen) := l_new_combid2_va(vaen);
3563 --
3564 l_mmcombid3_va.extend(1);
3565 l_mmcombid3_va(l_mmvaen) := l_new_combid3_va(vaen);
3566 --
3567 l_mmcombid4_va.extend(1);
3568 l_mmcombid4_va(l_mmvaen) := l_new_combid4_va(vaen);
3569 --
3570 l_mmvaen := l_mmvaen+1;
3571 --
3572 end if;
3573 --
3574 else
3575 --
3576 l_mmperid_va := l_new_perid_va;
3577 l_mmcombid_va := l_new_combid_va;
3578 l_mmcombid2_va := l_new_combid2_va;
3579 l_mmcombid3_va := l_new_combid3_va;
3580 l_mmcombid4_va := l_new_combid4_va;
3581 l_mmcnt_va := l_new_cnt_va;
3582 --
3583 end if;
3584 --
3585 end loop;
3586 --
3587 elsif l_old_perid_va.count > l_new_perid_va.count
3588 then
3589 --
3590 l_mmvaen := 1;
3591 --
3592 for vaen in l_old_perid_va.first..l_old_perid_va.last
3593 loop
3594 --
3595 l_perid_match := FALSE;
3596 --
3597 if l_new_perid_va.count > 0
3598 then
3599 --
3600 for subvaen in l_new_perid_va.first..l_new_perid_va.last
3601 loop
3602 --
3603 if l_new_perid_va(subvaen) = l_old_perid_va(vaen)
3604 and nvl(l_new_combid_va(subvaen),999) = nvl(l_old_combid_va(vaen),999)
3605 and nvl(l_new_combid2_va(subvaen),999) = nvl(l_old_combid2_va(vaen),999)
3606 and nvl(l_new_combid3_va(subvaen),999) = nvl(l_old_combid3_va(vaen),999)
3607 and nvl(l_new_combid4_va(subvaen),999) = nvl(l_old_combid4_va(vaen),999)
3608 then
3609 --
3610 l_perid_match := TRUE;
3611 exit;
3612 --
3613 end if;
3614 --
3615 end loop;
3616 --
3617 if not l_perid_match
3618 then
3619 --
3620 l_mmperid_va.extend(1);
3621 l_mmcnt_va.extend(1);
3622 l_mmperid_va(l_mmvaen) := l_old_perid_va(vaen);
3623 l_mmcnt_va(l_mmvaen) := l_old_cnt_va(vaen);
3624 --
3625 l_mmcombid_va.extend(1);
3626 l_mmcombid_va(l_mmvaen) := l_old_combid_va(vaen);
3627 --
3628 l_mmcombid2_va.extend(1);
3629 l_mmcombid2_va(l_mmvaen) := l_old_combid2_va(vaen);
3630 --
3631 l_mmcombid3_va.extend(1);
3632 l_mmcombid3_va(l_mmvaen) := l_old_combid3_va(vaen);
3633 --
3634 l_mmcombid4_va.extend(1);
3635 l_mmcombid4_va(l_mmvaen) := l_old_combid4_va(vaen);
3636 --
3637 l_mmvaen := l_mmvaen+1;
3638 --
3639 end if;
3640 --
3641 else
3642 --
3643 l_mmperid_va := l_old_perid_va;
3644 l_mmcombid_va := l_old_combid_va;
3645 l_mmcombid2_va := l_old_combid2_va;
3646 l_mmcombid3_va := l_old_combid3_va;
3647 l_mmcombid4_va := l_old_combid4_va;
3648 l_mmcnt_va := l_old_cnt_va;
3649 --
3650 end if;
3651 --
3652 end loop;
3653 --
3654 end if;
3655 --
3656 else
3657 --
3658 -- Check for count totals
3659 --
3660 if l_new_perid_va.count > 0
3661 then
3662 --
3663 l_new_totcnt := 0;
3664 --
3665 for vaen in l_new_perid_va.first..l_new_perid_va.last
3666 loop
3670 end loop;
3667 --
3668 l_new_totcnt := l_new_totcnt+l_new_cnt_va(vaen);
3669 --
3671 --
3672 end if;
3673 --
3674 if l_old_perid_va.count > 0
3675 then
3676 --
3677 l_old_totcnt := 0;
3678 --
3679 for vaen in l_old_perid_va.first..l_old_perid_va.last
3680 loop
3681 --
3682 l_old_totcnt := l_old_totcnt+l_old_cnt_va(vaen);
3683 --
3684 end loop;
3685 --
3686 end if;
3687 --
3688 -- Check for person count descrepancies
3689 --
3690 /*
3691 --
3692 -- Temporary
3693 --
3694 dbms_output.put_line(' l_new_totcnt: '||l_new_totcnt
3695 ||' l_old_totcnt: '||l_old_totcnt
3696 );
3697 --
3698 */
3699 if l_new_totcnt <> l_old_totcnt
3700 then
3701 --
3702 l_mmvaen := 1;
3703 --
3704 for vaen in l_new_perid_va.first..l_new_perid_va.last
3705 loop
3706 --
3707 l_perid_match := FALSE;
3708 --
3709 if l_old_perid_va.count > 0
3710 then
3711 --
3712 for subvaen in l_old_perid_va.first..l_old_perid_va.last
3713 loop
3714 --
3715 if l_old_perid_va(subvaen) = l_new_perid_va(vaen)
3716 and nvl(l_old_combid_va(subvaen),-999) = nvl(l_new_combid_va(vaen),-999)
3717 and nvl(l_old_combid2_va(subvaen),-999) = nvl(l_new_combid2_va(vaen),-999)
3718 and nvl(l_old_combid3_va(subvaen),-999) = nvl(l_new_combid3_va(vaen),-999)
3719 and nvl(l_old_combid4_va(subvaen),-999) = nvl(l_new_combid4_va(vaen),-999)
3720 and l_old_cnt_va(subvaen) = l_new_cnt_va(vaen)
3721 then
3722 --
3723 l_perid_match := TRUE;
3724 exit;
3725 --
3726 end if;
3727 --
3728 end loop;
3729 --
3730 if not l_perid_match
3731 then
3732 --
3733 l_mmperid_va.extend(1);
3734 l_mmcnt_va.extend(1);
3735 l_mmperid_va(l_mmvaen) := l_new_perid_va(vaen);
3736 l_mmcnt_va(l_mmvaen) := l_new_cnt_va(vaen);
3737 --
3738 l_mmcombid_va.extend(1);
3739 l_mmcombid_va(l_mmvaen) := l_new_combid_va(vaen);
3740 --
3741 l_mmcombid2_va.extend(1);
3742 l_mmcombid2_va(l_mmvaen) := l_new_combid2_va(vaen);
3743 --
3744 l_mmcombid3_va.extend(1);
3745 l_mmcombid3_va(l_mmvaen) := l_new_combid3_va(vaen);
3746 --
3747 l_mmcombid4_va.extend(1);
3748 l_mmcombid4_va(l_mmvaen) := l_new_combid4_va(vaen);
3749 /*
3750 --
3751 -- Temporary
3752 --
3753 dbms_output.put_line(' mmperid: '||l_mmperid_va(l_mmvaen)
3754 );
3755 --
3756 dbms_output.put_line(' mmcombid: '||l_mmcombid_va(l_mmvaen)
3757 );
3758 --
3759 */
3760 l_mmvaen := l_mmvaen+1;
3761 --
3762 end if;
3763 --
3764 else
3765 --
3766 l_mmperid_va := l_new_perid_va;
3767 l_mmcombid_va := l_new_combid_va;
3768 l_mmcombid2_va := l_new_combid2_va;
3769 l_mmcombid3_va := l_new_combid3_va;
3770 l_mmcombid4_va := l_new_combid4_va;
3771 l_mmcnt_va := l_new_cnt_va;
3772 --
3773 end if;
3774 --
3775 end loop;
3776 --
3777 end if;
3778 --
3779 end if;
3780 --
3781 -- Populate the combination name varray
3782 --
3783 if l_mmcombid_va.count > 0
3784 then
3785 --
3786 l_mmcombnm_va.delete;
3787 l_mmcombnm2_va.delete;
3788 l_mmcombnm3_va.delete;
3789 l_mmcombnm4_va.delete;
3790 --
3791 l_mmcombid_cnt := l_mmcombid_va.count;
3792 l_mmcombid2_cnt := l_mmcombid2_va.count;
3793 l_mmcombid3_cnt := l_mmcombid3_va.count;
3794 l_mmcombid4_cnt := l_mmcombid4_va.count;
3795 --
3796 for vaen in l_mmcombid_va.first..l_mmcombid_va.last
3797 loop
3798 --
3799 if l_mmcombid_cnt > 0
3800 then
3801 --
3802 l_mmcombnm_va.extend(1);
3803 l_mmcombnm_va(vaen) := l_keycolnm;
3804 --
3805 end if;
3806 --
3807 if l_mmcombid2_cnt > 0
3808 then
3809 --
3810 l_mmcombnm2_va.extend(1);
3811 l_mmcombnm2_va(vaen) := l_keycolnm2;
3812 --
3813 end if;
3814 --
3815 if l_mmcombid3_cnt > 0
3816 then
3817 --
3818 l_mmcombnm3_va.extend(1);
3819 l_mmcombnm3_va(vaen) := l_keycolnm3;
3820 --
3821 end if;
3822 --
3823 if l_mmcombid4_cnt > 0
3824 then
3825 --
3826 l_mmcombnm4_va.extend(1);
3827 l_mmcombnm4_va(vaen) := l_keycolnm4;
3828 --
3829 end if;
3830 --
3831 end loop;
3832 --
3833 end if;
3834 --
3835 -- Populate the last update date from live table
3836 --
3840 --
3837 /*
3838 --
3839 -- Debugging
3841 dbms_output.put_line(' BENRBVRU: l_mmperid_va.count: '||l_mmperid_va.count
3842 ||' l_mmcombid_va.count: '||l_mmcombid_va.count
3843 );
3844 --
3845 */
3846 if l_mmperid_va.count > 0
3847 then
3848 --
3849 l_sql_str := null;
3850 l_perdata_changes := FALSE;
3851 --
3852 if instr(p_rollup_code,'PIL') > 0
3853 then
3854 --
3855 -- Build SQL statement to get PACT info
3856 --
3857 l_sel_str := ' select max(rbv.last_update_date) ';
3858 l_from_str := ' from ben_per_in_ler rbv ';
3859 l_where_str := ' where person_id = :person_id ';
3860 --
3861 l_sql_str := l_sel_str
3862 ||' '||l_from_str
3863 ||' '||l_where_str;
3864 --
3865 end if;
3866 --
3867 if l_sql_str is not null
3868 then
3869 --
3870 for vaen in l_mmperid_va.first..l_mmperid_va.last
3871 loop
3872 --
3873 l_per_mxlud := null;
3874 --
3875 open c_rbvmaxlud FOR l_sql_str using l_mmperid_va(vaen);
3876 fetch c_rbvmaxlud into l_per_mxlud;
3877 --
3878 if l_per_mxlud is not null
3879 then
3880 --
3881 l_perdata_changes := TRUE;
3882 --
3883 end if;
3884 --
3885 close c_rbvmaxlud;
3886 --
3887 l_mmperlud_va.extend(1);
3888 l_mmperlud_va(vaen) := l_per_mxlud;
3889 --
3890 end loop;
3891 --
3892 end if;
3893 --
3894 -- Check if any changes were found
3895 --
3896 if not l_perdata_changes
3897 then
3898 --
3899 -- Build SQL statement to check PPL information
3900 --
3901 -- Note: exclude out future changes caused by tweaking db date
3902 --
3903 l_sel_str := ' select max(ppl.last_update_date) ';
3904 l_from_str := ' from ben_ptnl_ler_for_per ppl ';
3905 l_where_str := ' where ppl.person_id = :person_id '
3906 ||' and ppl.last_update_date < sysdate ';
3907 --
3908 l_sql_str := l_sel_str
3909 ||' '||l_from_str
3910 ||' '||l_where_str;
3911 --
3912 for vaen in l_mmperid_va.first..l_mmperid_va.last
3913 loop
3914 --
3915 l_per_mxlud := null;
3916 --
3917 open c_rbvmaxlud FOR l_sql_str using l_mmperid_va(vaen);
3918 fetch c_rbvmaxlud into l_per_mxlud;
3919 --
3920 if l_per_mxlud is not null
3921 then
3922 --
3923 l_perdata_changes := TRUE;
3924 --
3925 end if;
3926 --
3927 close c_rbvmaxlud;
3928 --
3929 l_mmperlud_va.extend(1);
3930 l_mmperlud_va(vaen) := l_per_mxlud;
3931 --
3932 end loop;
3933 --
3934 end if;
3935 --
3936 end if;
3937 --
3938 end if;
3939 --
3940 /*
3941 --
3942 -- Temporary
3943 --
3944 dbms_output.put_line(' - MMCMIDCnt: '||l_mmcombid_va.count
3945 ||' MMCMID2Cnt: '||l_mmcombid2_va.count
3946 ||' MMCMID3Cnt: '||l_mmcombid3_va.count
3947 );
3948 --
3949 */
3950 p_perid_va := l_mmperid_va;
3951 p_perlud_va := l_mmperlud_va;
3952 p_combnm_va := l_mmcombnm_va;
3953 p_combnm2_va := l_mmcombnm2_va;
3954 p_combnm3_va := l_mmcombnm3_va;
3955 p_combnm4_va := l_mmcombnm4_va;
3956 p_combid_va := l_mmcombid_va;
3957 p_combid2_va := l_mmcombid2_va;
3958 p_combid3_va := l_mmcombid3_va;
3959 p_combid4_va := l_mmcombid4_va;
3960 p_cnt_va := l_mmcnt_va;
3961 --
3962 end get_rollup_code_combkey_va;
3963 --
3964 PROCEDURE rollup_benmngle_rbvs
3965 (p_benefit_action_id in number
3966 ,p_refresh_rollups in varchar2 default 'N'
3967 )
3968 IS
3969 --
3970 TYPE cur_type IS REF CURSOR;
3971 --
3972 type t_v2_4000_va is varray(10000000) of varchar2(4000);
3973 --
3974 type t_rbvsum_rec is record
3975 (cnt number
3976 ,sum_ovn number
3977 ,min_esd date
3978 ,min_eed date
3979 );
3980 --
3981 type t_rbvdet_rec is record
3982 (id_str varchar2(4000)
3983 ,date_str varchar2(4000)
3984 ,value_str varchar2(4000)
3985 );
3986 --
3987 c_rbv_summary cur_type;
3988 c_rbv_detail cur_type;
3989 --
3990 l_rbv_summary_row t_rbvsum_rec;
3991 l_rbv_detail_row t_rbvdet_rec;
3992 --
3993 l_cd_va benutils.g_v2_150_table := benutils.g_v2_150_table();
3994 l_sqlstr_va benutils.g_varchar2_table := benutils.g_varchar2_table();
3995 l_fromstr_va benutils.g_varchar2_table := benutils.g_varchar2_table();
3996 l_wherestr_va benutils.g_varchar2_table := benutils.g_varchar2_table();
3997 l_rbvtypcd_va benutils.g_v2_150_table := benutils.g_v2_150_table();
3998 --
3999 l_batch_id_va benutils.g_number_table := benutils.g_number_table();
4000 l_batch_type_va benutils.g_v2_150_table := benutils.g_v2_150_table();
4001 l_class_code_va benutils.g_v2_150_table := benutils.g_v2_150_table();
4005 l_class_mineed_va benutils.g_date_table := benutils.g_date_table();
4002 l_class_code_count_va benutils.g_number_table := benutils.g_number_table();
4003 l_class_sumovn_va benutils.g_number_table := benutils.g_number_table();
4004 l_class_minesd_va benutils.g_date_table := benutils.g_date_table();
4006 l_class_idstr_va t_v2_4000_va := t_v2_4000_va();
4007 l_class_datestr_va t_v2_4000_va := t_v2_4000_va();
4008 l_class_valuestr_va t_v2_4000_va := t_v2_4000_va();
4009 --
4010 l_id_str varchar2(4000);
4011 l_date_str varchar2(4000);
4012 l_value_str varchar2(4000);
4013 --
4014 l_cnt number;
4015 l_rollup_id number;
4016 --
4017 l_rollupcnt_tot number;
4018 l_rollupsumovn_tot number;
4019 --
4020 l_batch_action_id number;
4021 --
4022 cursor c_bft_dets
4023 (c_bft_id number
4024 )
4025 is
4026 select bft.validate_flag
4027 from ben_benefit_actions bft
4028 where bft.validate_flag in ('C','B')
4029 and bft.benefit_action_id = c_bft_id;
4030 --
4031 l_bft_dets c_bft_dets%rowtype;
4032 --
4033 cursor c_rubft_dets
4034 (c_bft_id number
4035 )
4036 is
4037 select bat.batch_id
4038 from ben_batch_actions bat
4039 where bat.batch_id = c_bft_id;
4040 --
4041 l_rubft_dets c_rubft_dets%rowtype;
4042 --
4043 BEGIN
4044 --
4045 open c_bft_dets
4046 (c_bft_id => p_benefit_action_id
4047 );
4048 fetch c_bft_dets into l_bft_dets;
4049 if c_bft_dets%notfound then
4050 --
4051 close c_bft_dets;
4052 return;
4053 --
4054 end if;
4055 close c_bft_dets;
4056 --
4057 if nvl(p_refresh_rollups,'N') <> 'Y'
4058 then
4059 --
4060 open c_rubft_dets
4061 (c_bft_id => p_benefit_action_id
4062 );
4063 fetch c_rubft_dets into l_rubft_dets;
4064 if c_rubft_dets%found then
4065 --
4066 close c_rubft_dets;
4067 return;
4068 --
4069 end if;
4070 close c_rubft_dets;
4071 --
4072 end if;
4073 --
4074 if l_bft_dets.validate_flag in ('C','B')
4075 then
4076 --
4077 if nvl(p_refresh_rollups,'N') = 'Y'
4078 then
4079 --
4080 delete from ben_rollup_rbv_summary rlp
4081 where exists
4082 (select 1
4083 from ben_batch_actions bba
4084 where bba.batch_action_id = rlp.batch_action_id
4085 and bba.batch_id = p_benefit_action_id
4086 );
4087 --
4088 delete from ben_batch_actions bba
4089 where bba.batch_id = p_benefit_action_id;
4090 --
4091 end if;
4092 --
4093 -- Build the rollup SQL statement
4094 --
4095 /*
4096 --
4097 -- Temporary
4098 --
4099 dbms_output.put_line(' BENRBVRU: BFTID: '||p_benefit_action_id);
4100 --
4101 */
4102 build_rollup_sql_str
4103 (p_rt_cd_va => l_cd_va
4104 ,p_rt_fromstr_va => l_fromstr_va
4105 ,p_rt_wherestr_va => l_wherestr_va
4106 ,p_rt_sqlstr_va => l_sqlstr_va
4107 ,p_rt_typcd_va => l_rbvtypcd_va
4108 );
4109 --
4110 -- Populate the rollup type details
4111 --
4112 l_rollupcnt_tot := 0;
4113 l_rollupsumovn_tot := 0;
4114 --
4115 for va_en in l_sqlstr_va.first..l_sqlstr_va.last
4116 loop
4117 --
4118 begin
4119 --
4120 open c_rbv_summary FOR l_sqlstr_va(va_en) using p_benefit_action_id;
4121 FETCH c_rbv_summary INTO l_rbv_summary_row;
4122 if c_rbv_summary%notfound
4123 then
4124 --
4125 l_rbv_summary_row.cnt := 0;
4126 l_rbv_summary_row.sum_ovn := 0;
4127 l_rbv_summary_row.min_esd := hr_api.g_sot;
4128 l_rbv_summary_row.min_eed := hr_api.g_eot;
4129 --
4130 end if;
4131 close c_rbv_summary;
4132 --
4133 exception
4134 when others then
4135 /*
4136 --
4137 -- Temporary
4138 --
4139 dbms_output.put_line(' BENRBVRU: Roll: '||l_cd_va(va_en));
4140 dbms_output.put_line(' - '||substr(l_sqlstr_va(va_en),1,200));
4141
4142 --
4143 */
4144 --
4145 -- Special case for new tables that are not patched in
4146 --
4147 l_rbv_summary_row.cnt := 0;
4148 l_rbv_summary_row.sum_ovn := 0;
4149 l_rbv_summary_row.min_esd := hr_api.g_sot;
4150 l_rbv_summary_row.min_eed := hr_api.g_eot;
4151 --
4152 end;
4153 --
4154 /*
4155 if l_rt_cd_va(va_en) = 'PEP'
4156 then
4157 --
4158 -- Get ID columns
4159 --
4160 l_sel_str := ' select PGM_ID||PTIP_ID||PLIP_ID||PL_ID||MUST_ENRL_ANTHR_PL_ID id_str, '
4161 ||' null date_str, '
4162 ||' null value_str ';
4163 --
4164 l_sql_str := l_sel_str
4165 ||' '||l_rt_fromstr_va(va_en)
4166 ||' '||l_rt_wherestr_va(va_en);
4167 --
4168 l_id_str := ' ';
4169 l_date_str := ' ';
4170 l_value_str := ' ';
4171 --
4172 open c_rbv_detail FOR l_sql_str using p_benefit_action_id;
4173 loop
4174 FETCH c_rbv_detail INTO l_rbv_detail_row;
4175 EXIT WHEN c_rbv_detail%NOTFOUND;
4176 --
4177 begin
4178 --
4182 when others then
4179 l_id_str := nvl(l_id_str,' ')||' '||l_rbv_detail_row.id_str;
4180 --
4181 exception
4183 --
4184 null;
4185 --
4186 end;
4187 --
4188 l_date_str := nvl(l_date_str,' ')||' '||l_rbv_detail_row.date_str;
4189 l_value_str := nvl(l_value_str,' ')||' '||l_rbv_detail_row.value_str;
4190 --
4191 end loop;
4192 close c_rbv_detail;
4193 --
4194 end if;
4195 */
4196 --
4197 l_batch_id_va.extend(1);
4198 l_batch_type_va.extend(1);
4199 l_class_code_va.extend(1);
4200 l_class_code_count_va.extend(1);
4201 l_class_sumovn_va.extend(1);
4202 l_class_minesd_va.extend(1);
4203 l_class_mineed_va.extend(1);
4204 l_class_idstr_va.extend(1);
4205 l_class_datestr_va.extend(1);
4206 l_class_valuestr_va.extend(1);
4207 --
4208 l_batch_id_va(va_en) := p_benefit_action_id;
4209 l_batch_type_va(va_en) := 'BEN_BFT';
4210 l_class_code_va(va_en) := l_cd_va(va_en);
4211 l_class_code_count_va(va_en) := l_rbv_summary_row.cnt;
4212 l_class_sumovn_va(va_en) := l_rbv_summary_row.sum_ovn;
4213 l_class_minesd_va(va_en) := l_rbv_summary_row.min_esd;
4214 l_class_mineed_va(va_en) := l_rbv_summary_row.min_eed;
4215 l_class_idstr_va(va_en) := l_id_str;
4216 l_class_datestr_va(va_en) := l_date_str;
4217 l_class_valuestr_va(va_en) := l_value_str;
4218 --
4219 l_rollupcnt_tot := l_rollupcnt_tot+l_class_code_count_va(va_en);
4220 l_rollupsumovn_tot := l_rollupsumovn_tot+l_class_sumovn_va(va_en);
4221 --
4222 end loop;
4223 --
4224 if l_batch_id_va.count > 0
4225 then
4226 --
4227 -- Insert a batch action
4228 --
4229 insert into ben_batch_actions
4230 (batch_action_id
4231 ,batch_id
4232 ,batch_type
4233 ,rollup_count
4234 ,rollup_sumovn
4235 )
4236 values
4237 (ben_batch_actions_s.nextval
4238 ,p_benefit_action_id
4239 ,'BEN_BFT'
4240 ,l_rollupcnt_tot
4241 ,l_rollupsumovn_tot
4242 ) RETURNING batch_action_id into l_batch_action_id;
4243 --
4244 if l_rollupcnt_tot > 0
4245 then
4246 --
4247 for va_en in l_batch_id_va.first ..l_batch_id_va.last
4248 loop
4249 --
4250 insert into ben_rollup_rbv_summary
4251 (rollup_id
4252 ,batch_action_id
4253 ,rollup_code
4254 ,rollup_count
4255 ,rollup_sumovn
4256 ,rollup_minesd
4257 ,rollup_mineed
4258 ,rollup_id_string
4259 ,rollup_date_string
4260 ,rollup_value_string
4261 )
4262 values
4263 (ben_rollup_rbv_summary_s.nextval
4264 ,l_batch_action_id
4265 ,l_class_code_va(va_en)
4266 ,l_class_code_count_va(va_en)
4267 ,l_class_sumovn_va(va_en)
4268 ,l_class_minesd_va(va_en)
4269 ,l_class_mineed_va(va_en)
4270 ,l_class_idstr_va(va_en)
4271 ,l_class_datestr_va(va_en)
4272 ,l_class_valuestr_va(va_en)
4273 ) RETURNING rollup_id into l_rollup_id;
4274 --
4275 end loop;
4276 --
4277 end if;
4278 --
4279 end if;
4280 --
4281 end if;
4282 --
4283 END rollup_benmngle_rbvs;
4284 --
4285 end ben_rollup_rbvs;