1 package body ben_bis_utils as
2 /* $Header: benbisut.pkb 120.0 2005/05/28 03:43:35 appldev noship $ */
3 /* ===========================================================================
4 * Name:
5 * Batch_utils
6 * Purpose:
7 * This package is provide all batch utility and data structure to simply
8 * batch process.
9 * History:
10 * Date Who Version What?
11 * ----------- --------- ------- -----------------------------------------
12 * 25-Sep-2003 vsethi 115.0 Created.
13 * 13-May-2004 hmani 115.1 Added three functions
14 * get_group_pl_name, get_group_opt_name
15 * get_group_oipl_name
16 * ===========================================================================
17 */
18 --
19 -- Global variables declaration.
20 --
21
22 --
23 -- ============================================================================
24 -- <<Function: get_pl_name>>
25 -- ============================================================================
26 --
27 Function get_pl_name(p_pl_id in number
28 ,p_business_group_id in number
29 ,p_effective_date in date
30 ) return varchar2
31 is
32 --
33 cursor c1 is
34 select name
35 from ben_pl_f pl
36 where pl_id = p_pl_id
37 and business_group_id = p_business_group_id
38 and p_effective_date between effective_start_date and effective_end_date;
39 --
40 ret_str varchar2(1500);
41 --
42 Begin
43 --
44 open c1;
45 fetch c1 into ret_str;
46 close c1;
47 --
48 return ret_str;
49 --
50 End get_pl_name;
51 --
52 -- ============================================================================
53 -- <<Function: get_group_pl_name>>
54 -- ============================================================================
55 --
56 Function get_group_pl_name(p_pl_id in number
57 ,p_effective_date in date
58 ) return varchar2
59 is
60 --
61 cursor c1 is
62 select name
63 from ben_pl_f pl
64 where pl_id = p_pl_id
65 and p_effective_date between effective_start_date and effective_end_date;
66 --
67 ret_str varchar2(1500);
68 --
69 Begin
70 --
71 open c1;
72 fetch c1 into ret_str;
73 close c1;
74 --
75 return ret_str;
76 --
77 End get_group_pl_name;
78
79 --
80 -- ============================================================================
81 -- <<Function: get_pgm_name>>
82 -- ============================================================================
83 --
84 Function get_pgm_name(p_pgm_id in number
85 ,p_business_group_id in number
86 ,p_effective_date in date
87 ) return varchar2
88 is
89 --
90 cursor c1 is
91 select name
92 from ben_pgm_f pgm
93 where pgm_id = p_pgm_id
94 and business_group_id = p_business_group_id
95 and p_effective_date between effective_start_date and effective_end_date;
96 --
97 ret_str varchar2(1500);
98 --
99 Begin
100 --
101 open c1;
102 fetch c1 into ret_str;
103 close c1;
104 --
105 return ret_str;
106 --
107 End get_pgm_name;
108 --
109 -- ============================================================================
110 -- <<Function: get_opt_name>>
111 -- ============================================================================
112 --
113 Function get_opt_name(p_opt_id in number
114 ,p_business_group_id in number
115 ,p_effective_date in date
116 ) return varchar2
117 is
118 --
119 cursor c1 is
120 select name
121 from ben_opt_f
122 where opt_id = p_opt_id
123 and business_group_id = p_business_group_id
124 and p_effective_date between effective_start_date and effective_end_date;
125 --
126 ret_str varchar2(1500);
127 --
128 Begin
129 --
130 open c1;
131 fetch c1 into ret_str;
132 close c1;
133 --
134 return ret_str;
135 --
136 End get_opt_name;
137
138 --
139 --
140 -- ============================================================================
141 -- <<Function: get_group_opt_name>>
142 -- ============================================================================
143 --
144 Function get_group_opt_name(p_opt_id in number
145 ,p_effective_date in date
146 ) return varchar2
147 is
148 --
149 cursor c1 is
150 select name
151 from ben_opt_f
152 where opt_id = p_opt_id
153 and p_effective_date between effective_start_date and effective_end_date;
154 --
155 ret_str varchar2(1500);
156 --
157 Begin
158 --
159 open c1;
160 fetch c1 into ret_str;
161 close c1;
162 --
163 return ret_str;
164 --
165 End get_group_opt_name;
166
167 --
168 -- ============================================================================
169 -- <<Function: get_plip_name>>
170 -- ============================================================================
171 --
172 Function get_plip_name(p_plip_id in number
173 ,p_business_group_id in number
174 ,p_effective_date in date
175 ) return varchar2
176 is
177 --
178 cursor c1 is
179 select pgm.name|| ' - '|| pln.name
180 from ben_pl_f pln,
181 ben_pgm_f pgm,
182 ben_plip_f plip
183 where plip.plip_id = p_plip_id
184 and pgm.pgm_id = plip.pgm_id
185 and pln.pl_id = plip.pl_id
186 and plip.business_group_id = p_business_group_id
187 and p_effective_date between plip.effective_start_date and plip.effective_end_date
188 and p_effective_date between pln.effective_start_date and pln.effective_end_date
189 and p_effective_date between pgm.effective_start_date and pgm.effective_end_date;
190 --
191 ret_str varchar2(1500);
192 --
193 Begin
194 --
195 open c1;
196 fetch c1 into ret_str;
197 close c1;
198 --
199 return ret_str;
200 --
201 End get_plip_name;
202
203 --
204 -- ============================================================================
205 -- <<Function: get_ptip_name>>
206 -- ============================================================================
207 --
208 Function get_ptip_name(p_ptip_id in number
209 ,p_business_group_id in number
210 ,p_effective_date in date
211 ) return varchar2
212 is
213 --
214 cursor c1 is
215 select pgm.name|| ' - ' || plt.name
216 from ben_pl_typ_f plt,
217 ben_pgm_f pgm,
218 ben_ptip_f ptip
219 where ptip.ptip_id = p_ptip_id
220 and pgm.pgm_id = ptip.pgm_id
221 and plt.pl_typ_id = ptip.pl_typ_id
222 and ptip.business_group_id = p_business_group_id
223 and p_effective_date between ptip.effective_start_date and ptip.effective_end_date
224 and p_effective_date between plt.effective_start_date and plt.effective_end_date
225 and p_effective_date between pgm.effective_start_date and pgm.effective_end_date;
226 --
227 ret_str varchar2(1500);
228 --
229 Begin
230 --
231 open c1;
232 fetch c1 into ret_str;
233 close c1;
234 --
235 return ret_str;
236 --
237 End get_ptip_name;
238
239 --
240 -- ============================================================================
241 -- <<Function: get_oipl_name>>
242 -- ============================================================================
243 --
244 Function get_oipl_name(p_oipl_id in number
245 ,p_business_group_id in number
246 ,p_effective_date in date
247 ) return varchar2
248 is
249 --
250 cursor c1 is
251 select pln.name|| ' - '|| opt.name
252 from ben_pl_f pln,
253 ben_opt_f opt,
254 ben_oipl_f oipl
255 where oipl.oipl_id = p_oipl_id
256 and opt.opt_id = oipl.opt_id
257 and pln.pl_id = oipl.pl_id
258 and oipl.business_group_id = p_business_group_id
259 and p_effective_date between oipl.effective_start_date and oipl.effective_end_date
260 and p_effective_date between opt.effective_start_date and opt.effective_end_date
261 and p_effective_date between pln.effective_start_date and pln.effective_end_date;
262 --
263 ret_str varchar2(1500);
264 --
265 Begin
266 --
267 open c1;
268 fetch c1 into ret_str;
269 close c1;
270 --
271 return ret_str;
272 --
273 End get_oipl_name;
274 --
275 --
276 -- ============================================================================
277 -- <<Function: get_group_oipl_name>>
278 -- ============================================================================
279 --
280 Function get_group_oipl_name(p_oipl_id in number
281 ,p_effective_date in date
282 ) return varchar2
283 is
284 --
285 cursor c1 is
286 select pln.name|| ' - '|| opt.name
287 from ben_pl_f pln,
288 ben_opt_f opt,
289 ben_oipl_f oipl
290 where oipl.oipl_id = p_oipl_id
291 and opt.opt_id = oipl.opt_id
292 and pln.pl_id = oipl.pl_id
293 and p_effective_date between oipl.effective_start_date and oipl.effective_end_date
294 and p_effective_date between opt.effective_start_date and opt.effective_end_date
295 and p_effective_date between pln.effective_start_date and pln.effective_end_date;
296 --
297 ret_str varchar2(1500);
298 --
299 Begin
300 --
301 open c1;
302 fetch c1 into ret_str;
303 close c1;
304 --
305 return ret_str;
306 --
307 End get_group_oipl_name;
308 --
309 -- ============================================================================
310 -- <<Function: get_oiplip_name>>
311 -- ============================================================================
312 --
313 Function get_oiplip_name(p_oiplip_id in number
314 ,p_business_group_id in number
315 ,p_effective_date in date
316 ) return varchar2
317 is
318 --
319 cursor c1 is
320 select pgm.name || ' - '|| pln.name|| ' - '|| opt.name
321 from ben_pl_f pln,
322 ben_opt_f opt,
323 ben_oipl_f oipl,
324 ben_oiplip_f oiplip,
325 ben_plip_f plip,
326 ben_pgm_f pgm
327 where oiplip.oiplip_id = p_oiplip_id
328 and oipl.oipl_id = oiplip.oipl_id
329 and opt.opt_id = oipl.opt_id
330 and pln.pl_id = oipl.pl_id
331 and plip.plip_id = oiplip.plip_id
332 and pgm.pgm_id = plip.pgm_id
333 and oiplip.business_group_id = p_business_group_id
334 and p_effective_date between oiplip.effective_start_date and oiplip.effective_end_date
335 and p_effective_date between oipl.effective_start_date and oipl.effective_end_date
336 and p_effective_date between opt.effective_start_date and opt.effective_end_date
337 and p_effective_date between pln.effective_start_date and pln.effective_end_date
338 and p_effective_date between plip.effective_start_date and plip.effective_end_date
339 and p_effective_date between pgm.effective_start_date and pgm.effective_end_date;
340 --
341 ret_str varchar2(1500);
342 --
343 Begin
344 --
345 open c1;
346 fetch c1 into ret_str;
347 close c1;
348 --
349 return ret_str;
350 --
351 End get_oiplip_name;
352
353 --
354 -- ============================================================================
355 -- <<Function: get_cmbn_plip_name>>
356 -- ============================================================================
357 --
358 Function get_cmbn_plip_name(p_cmbn_plip_id in number
359 ,p_business_group_id in number
360 ,p_effective_date in date
361 ) return varchar2
362 is
363 --
364 cursor c1 is
365 select cplip.name
366 from ben_cmbn_plip_f cplip
367 where cplip.cmbn_plip_id = p_cmbn_plip_id
368 and cplip.business_group_id = p_business_group_id
369 and p_effective_date between cplip.effective_start_date and cplip.effective_end_date;
370 --
371 ret_str varchar2(1500);
372 --
373 Begin
374 --
375 open c1;
376 fetch c1 into ret_str;
377 close c1;
378 --
379 return ret_str;
380 --
381 End get_cmbn_plip_name;
382
383 --
384 -- ============================================================================
385 -- <<Function: get_cmbn_ptip_name>>
386 -- ============================================================================
387 --
388 Function get_cmbn_ptip_name(p_cmbn_ptip_id in number
389 ,p_business_group_id in number
390 ,p_effective_date in date
391 ) return varchar2
392 is
393 --
394 cursor c1 is
395 select cptip.name
396 from ben_cmbn_ptip_f cptip
397 where cptip.cmbn_ptip_id = p_cmbn_ptip_id
398 and cptip.business_group_id = p_business_group_id
399 and p_effective_date between cptip.effective_start_date and cptip.effective_end_date;
400 --
401 ret_str varchar2(1500);
402 --
403 Begin
404 --
405 open c1;
406 fetch c1 into ret_str;
407 close c1;
408 --
409 return ret_str;
410 --
411 End get_cmbn_ptip_name;
412
413 --
414 -- ============================================================================
415 -- <<Function: get_cmbn_ptip_opt_name>>
416 -- ============================================================================
417 --
418 Function get_cmbn_ptip_opt_name(p_cmbn_ptip_opt_id in number
419 ,p_business_group_id in number
420 ,p_effective_date in date
421 ) return varchar2
422 is
423 --
424 cursor c1 is
425 select cpt.name
426 from ben_cmbn_ptip_opt_f cpt
427 where cpt.cmbn_ptip_opt_id = p_cmbn_ptip_opt_id
428 and cpt.business_group_id = p_business_group_id
429 and p_effective_date between cpt.effective_start_date and cpt.effective_end_date;
430 --
434 --
431 ret_str varchar2(1500);
432 --
433 Begin
435 open c1;
436 fetch c1 into ret_str;
437 close c1;
438 --
439 return ret_str;
440 --
441 End get_cmbn_ptip_opt_name;
442
443 --
444 end ben_bis_utils;
[Home] [Help]