[Home] [Help]
PACKAGE BODY: APPS.BEN_SUM
Source
1 package body ben_sum as
2 /* $Header: bensumfm.pkb 115.1 2003/02/12 10:30:35 rpgupta noship $ */
3 --
4 g_package varchar2(50) := 'ben_sum.';
5 --
6 PROCEDURE sum_query
7 (block_data IN OUT NOCOPY sumtab
8 ,p_person_id IN NUMBER
9 )
10 IS
11
12 l_onum1_va benutils.g_number_table := benutils.g_number_table();
13 l_onum2_va benutils.g_number_table := benutils.g_number_table();
14 l_onum3_va benutils.g_number_table := benutils.g_number_table();
15 l_name_va benutils.g_v2_150_table := benutils.g_v2_150_table();
16 l_id_va benutils.g_number_table := benutils.g_number_table();
17 l_bgpid_va benutils.g_number_table := benutils.g_number_table();
18 l_perid_va benutils.g_number_table := benutils.g_number_table();
19 l_yn_lookcd_va benutils.g_v2_150_table := benutils.g_v2_150_table();
20 l_type_va benutils.g_v2_150_table := benutils.g_v2_150_table();
21 l_otypecd_va benutils.g_v2_150_table := benutils.g_v2_150_table();
22
23 l_init sumtab;
24
25 cursor c_eff_date
26 is
27 select se.effective_date
28 from fnd_sessions se
29 where se.session_id = USERENV('sessionid');
30
31 cursor sumselect
32 (c_per_id number
33 ,c_eff_date date
34 )
35 is
36 SELECT /*+ ben_sum.sumselect */
37 pep.pgm_id order_num1,
38 -1 order_num2,
39 -1 order_num3,
40 pgm.name name,
41 pep.pgm_id id,
42 pep.business_group_id business_group_id,
43 pep.person_id person_id,
44 pep.elig_flag yn_lookcd,
45 'PGM' type
46 ,
47 pgm.pgm_typ_cd object_type_cd
48 from ben_elig_per_f pep,
49 ben_per_in_ler pil,
50 ben_pgm_f pgm
51 WHERE pep.person_id = c_per_id
52 and pep.pgm_id = pgm.pgm_id
53 and pep.pl_id is null
54 /*
55 and pep.plip_id is null
56 and pep.ptip_id is null
57 */
58 and c_eff_date
59 between pgm.effective_start_date and pgm.effective_end_date
60 and c_eff_date
61 between pep.effective_start_date and pep.effective_end_date
62 and pil.per_in_ler_id (+)= pep.per_in_ler_id
63 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
64 or pil.per_in_ler_stat_cd is null )
65 union
66 select pep.pgm_id order_num1,
67 pep.pl_id order_num2,
68 -1 order_num3,
69 ' ' || pl.name name,
70 pep.pl_id id ,
71 pep.business_group_id business_group_id ,
72 pep.person_id person_id ,
73 pep.elig_flag yn_lookcd,
74 'PL' type
75 ,
76 pl.svgs_pl_flag object_type_cd
77 from ben_elig_per_f pep ,
78 ben_per_in_ler pil,
79 ben_pl_f pl
80 WHERE pep.person_id = c_per_id
81 and pep.pl_id = pl.pl_id
82 and pep.pgm_id is not null
83 and pep.pl_id is not null
84 and c_eff_date
85 between pep.effective_start_date and pep.effective_end_date
86 and c_eff_date
87 between pl.effective_start_date and pl.effective_end_date
88 and exists
89 (select null
90 from ben_plip_f
91 where pl_id = pl.pl_id
92 )
93 and pil.per_in_ler_id (+)= pep.per_in_ler_id
94 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
95 or pil.per_in_ler_stat_cd is null )
96 union
97 select pep.pgm_id order_num1 ,
98 pep.pl_id order_num2 ,
99 pio.opt_id order_num3 ,
100 ' ' || opt.name name ,
101 pio.opt_id id ,
102 pep.business_group_id business_group_id ,
103 pep.person_id person_id ,
104 pio.elig_flag yn_lookcd,
105 'OPT' type
106 ,
107 null object_type_cd
108 from ben_elig_per_opt_f pio ,
109 ben_elig_per_f pep ,
110 ben_per_in_ler pil,
111 ben_pl_f pl ,
112 ben_opt_f opt
113 WHERE pep.person_id = c_per_id
114 and pio.elig_per_id = pep.elig_per_id
115 and pio.opt_id = opt.opt_id
116 and pep.pl_id = pl.pl_id
117 and pep.pgm_id is not null
118 and pep.pl_id is not null
119 and c_eff_date
120 between pep.effective_start_date and pep.effective_end_date
121 and c_eff_date
122 between pio.effective_start_date and pio.effective_end_date
123 and c_eff_date
124 between opt.effective_start_date and opt.effective_end_date
125 and c_eff_date
126 between pl.effective_start_date and pl.effective_end_date
127 and exists
128 (select null from ben_plip_f where pl_id = pl.pl_id)
129 and pil.per_in_ler_id (+)= pio.per_in_ler_id
130 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
131 or pil.per_in_ler_stat_cd is null )
132 union
133 select 9999999999999999999999999999999 order_num1 ,
134 pep.pl_id order_num2 ,
135 -1 order_num3 ,
136 pl.name name ,
137 pl.pl_id id ,
138 pep.business_group_id business_group_id ,
139 pep.person_id person_id ,
140 pep.elig_flag yn_lookcd,
141 'PL' type
142 ,
143 pl.svgs_pl_flag object_type_cd
144 from ben_elig_per_f pep ,
145 ben_per_in_ler pil,
146 ben_pl_f pl
147 WHERE pep.person_id = c_per_id
148 and pep.pl_id = pl.pl_id
149 and pep.pgm_id is null
150 and c_eff_date
151 between pep.effective_start_date and pep.effective_end_date
152 and c_eff_date
153 between pl.effective_start_date and pl.effective_end_date
154 and not exists
155 (select null from ben_plip_f
156 where pl_id = pl.pl_id
157 )
158 and pil.per_in_ler_id (+)= pep.per_in_ler_id
159 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
160 or pil.per_in_ler_stat_cd is null )
161 union
162 select 9999999999999999999999999999999 order_num1 ,
163 pep.pl_id order_num2 ,
164 pio.opt_id order_num3 ,
165 ' ' || opt.name name ,
166 pio.opt_id id ,
167 pep.business_group_id business_group_id ,
168 pep.person_id person_id ,
169 pio.elig_flag yn_lookcd,
170 'OPT' type
171 ,
172 null object_type_cd
173 from ben_elig_per_opt_f pio ,
174 ben_elig_per_f pep ,
175 ben_per_in_ler pil,
176 ben_pl_f pl ,
177 ben_opt_f opt
178 WHERE pep.person_id = c_per_id
179 and pio.elig_per_id = pep.elig_per_id
180 and pio.opt_id = opt.opt_id
181 and pep.pl_id = pl.pl_id
182 and pep.pgm_id is null
183 and pep.pl_id is not null
184 and c_eff_date
185 between pep.effective_start_date and pep.effective_end_date
186 and c_eff_date
187 between pio.effective_start_date and pio.effective_end_date
188 and c_eff_date
189 between opt.effective_start_date and opt.effective_end_date
190 and c_eff_date
191 between pl.effective_start_date and pl.effective_end_date
192 and not exists
193 (select null from ben_plip_f
194 where pl_id = pl.pl_id
195 )
196 and pil.per_in_ler_id (+)= pio.per_in_ler_id
197 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
198 or pil.per_in_ler_stat_cd is null )
199 order by order_num1,
200 order_num2,
201 order_num3;
202
203 l_eff_date date;
204
205 BEGIN
206 --
207 block_data := l_init;
208 --
209 open c_eff_date;
210 fetch c_eff_date into l_eff_date;
211 close c_eff_date;
212 --
213 open sumselect
214 (c_per_id => p_person_id
215 ,c_eff_date => l_eff_date
216 );
217 fetch sumselect BULK COLLECT INTO l_onum1_va,
218 l_onum2_va,
219 l_onum3_va,
220 l_name_va,
221 l_id_va,
222 l_bgpid_va,
223 l_perid_va,
224 l_yn_lookcd_va,
225 l_type_va,
226 l_otypecd_va;
227 close sumselect;
228 --
229 if l_onum1_va.count > 0 then
230 --
231 for elenum in l_onum1_va.first .. l_onum1_va.last
232 loop
233 --
234 block_data(elenum-1).order_num1 := l_onum1_va(elenum);
235 block_data(elenum-1).order_num2 := l_onum2_va(elenum);
236 block_data(elenum-1).order_num3 := l_onum3_va(elenum);
237 block_data(elenum-1).name := l_name_va(elenum);
238 block_data(elenum-1).id := l_id_va(elenum);
239 block_data(elenum-1).business_group_id := l_bgpid_va(elenum);
240 block_data(elenum-1).person_id := l_perid_va(elenum);
241 block_data(elenum-1).meaning := l_yn_lookcd_va(elenum);
242 block_data(elenum-1).type := l_type_va(elenum);
243 block_data(elenum-1).object_type_cd := l_otypecd_va(elenum);
244 --
245 end loop;
246 --
247 end if;
248 --
249 END sum_query;
250 --
251 end ben_sum;