[Home] [Help]
PACKAGE BODY: APPS.BEN_GLOBAL_ENRT
Source
1 package body ben_global_enrt as
2 /* $Header: bengenrt.pkb 120.0.12010000.2 2008/08/05 14:47:54 ubhat ship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10
11 Name
12 Enrollment Globals Package
13 Purpose
14 This package is used to load and return globals used in the enrollment
15 save processes.
16 History
17 Date Who Version What?
18 --------- --------- ------- --------------------------------------------
19 03 Apr 2000 lmcdonal 115.0 Created
20 06 Apr 2000 lmcdonal 115.1 Added more procedures.
21 07 Apr 2000 lmcdonal 115.2 Temp change: always hit database
22 until I can workout when to clear globals.
23 18 Apr 2000 jcarpent 115.3 Allow assignment to not be ACTIVE_ASSIGN
24 17 May 2001 maagrawa 115.4 Changed the procedure and record
25 definitions.
26 23 May 2001 maagrawa 115.5 Removed mandatory condition from
27 get_pil.
28 11 Jun 2002 pabodla 115.6 Added dbdrv command
29 14-Jun-2002 pabodla 115.7 Do not select the contingent worker
30 assignment when assignment data is
31 fetched.
32 13-Dec-2002 kmahendr 115.8 Nocopy Changes
33 10-feb-2005 mmudigon 115.9 Bug 4157759. Changes to cursor c1 in get_pil
34 17-feb-2005 bmanyam 115.10 Bug 4187137. Changed get_pil to query always.
35 18-feb-2005 bmanyam 115.11 Bug 4187137. Changed get_pil to query always.
36 22-Feb-2008 rtagarra 115.12 Bug 6840074
37 ------------------------------------------------------------------------------
38 */
39
40 g_package varchar2(30) := 'ben_global_enrt.';
41 ------------------------------------------------------------------------------
42 -- get_epe
43 ------------------------------------------------------------------------------
44 procedure get_epe
45 (p_elig_per_elctbl_chc_id in number
46 ,p_global_epe_rec out nocopy g_global_epe_rec_type) is
47
48 l_proc varchar2(80) := g_package||'get_epe';
49 cursor c1 is
50 select epe.per_in_ler_id
51 ,epe.pil_elctbl_chc_popl_id
52 ,epe.prtt_enrt_rslt_id
53 ,epe.pgm_id
54 ,epe.pl_id
55 ,epe.pl_typ_id
56 ,epe.plip_id
57 ,epe.ptip_id
58 ,epe.oipl_id
59 ,epe.business_group_id
60 ,epe.object_version_number
61 ,epe.comp_lvl_cd
62 ,epe.crntly_enrd_flag
63 ,epe.alws_dpnt_dsgn_flag
64 ,epe.dpnt_cvg_strt_dt_cd
65 ,epe.dpnt_cvg_strt_dt_rl
66 ,epe.enrt_cvg_strt_dt
67 ,epe.erlst_deenrt_dt
68 ,epe.enrt_cvg_strt_dt_cd
69 ,epe.enrt_cvg_strt_dt_rl
70 from ben_elig_per_elctbl_chc epe
71 where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id ;
72
73 l_epe_rec g_global_epe_rec_type;
74
75 begin
76 hr_utility.set_location('Entering '||l_proc,10);
77 hr_api.mandatory_arg_error(p_api_name => l_proc,
78 p_argument => 'p_elig_per_elctbl_chc_id',
79 p_argument_value => p_elig_per_elctbl_chc_id);
80
81 open c1;
82 fetch c1 into g_global_epe_rec;
83 if c1%notfound then
84 g_global_epe_rec := l_epe_rec;
85 end if;
86 close c1;
87 p_global_epe_rec := g_global_epe_rec;
88
89 hr_utility.set_location('Leaving '||l_proc,99);
90
91 end get_epe;
92
93 ------------------------------------------------------------------------------
94 -- reload_epe
95 ------------------------------------------------------------------------------
96 procedure reload_epe
97 (p_elig_per_elctbl_chc_id in number
98 ,p_global_epe_rec out nocopy g_global_epe_rec_type) is
99
100 l_proc varchar2(80) := g_package||'reload_epe';
101
102
103 begin
104 hr_utility.set_location('Entering '||l_proc,10);
105 hr_api.mandatory_arg_error(p_api_name => l_proc,
106 p_argument => 'p_elig_per_elctbl_chc_id',
107 p_argument_value => p_elig_per_elctbl_chc_id);
108
109 ben_global_enrt.get_epe
110 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
111 ,p_global_epe_rec => p_global_epe_rec);
112
113 hr_utility.set_location('Leaving '||l_proc,99);
114
115 end reload_epe;
116
117 ------------------------------------------------------------------------------
118 -- get_pel
119 ------------------------------------------------------------------------------
120 procedure get_pel
121 (p_pil_elctbl_chc_popl_id in number
122 ,p_global_pel_rec out nocopy g_global_pel_rec_type)is
123
124 l_proc varchar2(80) := g_package||'get_pel';
125 cursor c1 is
126 select pel.per_in_ler_id
127 ,pel.pgm_id
128 ,pel.pl_id
129 ,pel.lee_rsn_id
130 ,pel.enrt_perd_id
131 ,pel.uom
132 ,pel.acty_ref_perd_cd
133 from ben_pil_elctbl_chc_popl pel
134 where pel.pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id ;
135
136 l_pel_rec g_global_pel_rec_type;
137
138 begin
139 hr_utility.set_location('Entering '||l_proc,10);
140 hr_api.mandatory_arg_error(p_api_name => l_proc,
141 p_argument => 'p_pil_elctbl_chc_popl_id',
142 p_argument_value => p_pil_elctbl_chc_popl_id);
143
144 if g_global_pel_id <> p_pil_elctbl_chc_popl_id or
145 g_global_pel_id is null then
146 open c1;
147 fetch c1 into g_global_pel_rec;
148 if c1%notfound then
149 g_global_pel_rec := l_pel_rec;
150 end if;
151 close c1;
152 g_global_pel_id := p_pil_elctbl_chc_popl_id;
153 end if;
154 p_global_pel_rec := g_global_pel_rec;
155
156 hr_utility.set_location('Leaving '||l_proc,99);
157 end get_pel;
158 ------------------------------------------------------------------------------
159 -- get_pil
160 ------------------------------------------------------------------------------
161 procedure get_pil
162 (p_per_in_ler_id in number
163 ,p_global_pil_rec out nocopy g_global_pil_rec_type) is
164
165 l_proc varchar2(80) := g_package||'get_pil';
166 cursor c1 is
167 select pil.person_id
168 ,pil.ler_id
169 ,pil.lf_evt_ocrd_dt
170 ,ler.typ_cd
171 from ben_per_in_ler pil,
172 ben_ler_f ler
173 where pil.per_in_ler_id = p_per_in_ler_id
174 and ler.ler_id(+) = pil.ler_id
175 and pil.lf_evt_ocrd_dt between ler.effective_start_date(+)
176 and ler.effective_end_date(+) ;
177
178 l_pil_rec g_global_pil_rec_type;
179
180 begin
181 hr_utility.set_location('Entering '||l_proc,10);
182 --
183 if p_per_in_ler_id is null then
184 p_global_pil_rec := l_pil_rec;
185 return;
186 end if;
187
188 if (g_global_pil_id is null)
189 or (g_global_pil_id <> p_per_in_ler_id)
190 or (g_global_pil_rec.typ_cd = 'SCHEDDU') then
191 /* 4187137: For Unrestricted, we need to fetch the details everytime as lf_evt_ocrd_dt can change..
192 Henceforth, query PIL record always for Unrestricted... */
193 open c1;
194 fetch c1 into g_global_pil_rec;
195 if c1%notfound then
196 g_global_pil_rec := l_pil_rec;
197 end if;
198 close c1;
199 g_global_pil_id := p_per_in_ler_id;
200 end if;
201 p_global_pil_rec := g_global_pil_rec;
202
203 hr_utility.set_location('Leaving '||l_proc,99);
204
205 end get_pil;
206 ------------------------------------------------------------------------------
207 -- clear_enb
208 ------------------------------------------------------------------------------
209 procedure clear_enb
210 (p_global_enb_rec out nocopy g_global_enb_rec_type) is
211
212 l_proc varchar2(80) := g_package||'clear_enb';
213 l_enb_rec g_global_enb_rec_type;
214
215 begin
216 hr_utility.set_location('Entering '||l_proc,10);
217
218 g_global_enb_rec := l_enb_rec;
219
220 p_global_enb_rec := g_global_enb_rec;
221
222 hr_utility.set_location('Leaving '||l_proc,99);
223
224 end clear_enb;
225 ------------------------------------------------------------------------------
226 -- get_enb
227 ------------------------------------------------------------------------------
228 procedure get_enb
229 (p_enrt_bnft_id in number
230 ,p_global_enb_rec out nocopy g_global_enb_rec_type) is
231
232 l_proc varchar2(80) := g_package||'get_enb';
233 cursor c1 is
234 select enb.ordr_num
235 ,enb.val
236 ,enb.bnft_typ_cd
237 ,enb.cvg_mlt_cd
238 ,enb.nnmntry_uom
239 ,enb.object_version_number
240 from ben_enrt_bnft enb
241 where enb.enrt_bnft_id = p_enrt_bnft_id ;
242
243 l_enb_rec g_global_enb_rec_type;
244
245 begin
246 hr_utility.set_location('Entering '||l_proc,10);
247 hr_api.mandatory_arg_error(p_api_name => l_proc,
248 p_argument => 'p_enrt_bnft_id',
249 p_argument_value => p_enrt_bnft_id);
250
251 open c1;
252 fetch c1 into g_global_enb_rec;
253 if c1%notfound then
254 g_global_enb_rec := l_enb_rec;
255 end if;
256 close c1;
257 p_global_enb_rec := g_global_enb_rec;
258
259 hr_utility.set_location('Leaving '||l_proc,99);
260
261 end get_enb;
262
263 ------------------------------------------------------------------------------
264 -- get_asg
265 ------------------------------------------------------------------------------
266 procedure get_asg
267 (p_person_id in number
268 ,p_effective_date in date
269 ,p_global_asg_rec out nocopy g_global_asg_rec_type) is
270
271 l_proc varchar2(80) := g_package||'get_asg';
272
273 -- Get employee active assignments first, then look for active
274 -- benefit's assignments.
275 cursor c1 is
276 select asg.payroll_id
277 from per_all_assignments_f asg,
278 per_assignment_status_types ast
279 where asg.person_id = p_person_id
280 and asg.assignment_type <> 'C'
281 and asg.primary_flag = 'Y'
282 and asg.assignment_status_type_id = ast.assignment_status_type_id
283 and ast.active_flag = 'Y'
284 and ast.primary_flag = 'P'
285 and p_effective_date between
286 asg.effective_start_date and asg.effective_end_date
287 order by decode(asg.assignment_type, 'E', 1, 'B', 2, 3);
288
289 l_asg_rec g_global_asg_rec_type;
290
291 begin
292 hr_utility.set_location('Entering '||l_proc,10);
293 hr_api.mandatory_arg_error(p_api_name => l_proc,
294 p_argument => 'p_person_id',
295 p_argument_value => p_person_id);
296
297 if g_global_asg_person_id is null or
298 g_global_asg_person_id <> p_person_id then
299 open c1;
300 fetch c1 into g_global_asg_rec;
301 if c1%notfound then
302 g_global_asg_rec := l_asg_rec;
303 end if;
304 close c1;
305 g_global_asg_person_id := p_person_id;
306 end if;
307 p_global_asg_rec := g_global_asg_rec;
308
309 hr_utility.set_location('Leaving '||l_proc,99);
310
311 end get_asg;
312 ------------------------------------------------------------------------------
313 -- clear_pen
314 ------------------------------------------------------------------------------
315 procedure clear_pen
316 (p_global_pen_rec out nocopy ben_prtt_enrt_rslt_f%rowtype) is
317
318 l_proc varchar2(80) := g_package||'clear_pen';
319 l_pen_rec ben_prtt_enrt_rslt_f%rowtype;
320
321 begin
322 hr_utility.set_location('Entering '||l_proc,10);
323
324 g_global_pen_rec := l_pen_rec;
325 p_global_pen_rec := g_global_pen_rec;
326
327 hr_utility.set_location('Leaving '||l_proc,99);
328 end clear_pen;
329 ------------------------------------------------------------------------------
330 -- get_pen - overloaded
331 ------------------------------------------------------------------------------
332 procedure get_pen
333 (p_prtt_enrt_rslt_id in number
334 ,p_effective_date in date
335 ,p_global_pen_rec out nocopy ben_prtt_enrt_rslt_f%rowtype) is
336
337 l_proc varchar2(80) := g_package||'get_pen';
338
339 cursor c1 is
340 select pen.*
341 from ben_prtt_enrt_rslt_f pen
342 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
343 and pen.prtt_enrt_rslt_stat_cd is null
344 and p_effective_date between
345 pen.effective_start_date and pen.effective_end_date;
346
347 l_pen_rec ben_prtt_enrt_rslt_f%rowtype;
348
349 begin
350 hr_utility.set_location('Entering '||l_proc,10);
351 hr_api.mandatory_arg_error(p_api_name => l_proc,
352 p_argument => 'p_prtt_enrt_rslt_id',
353 p_argument_value => p_prtt_enrt_rslt_id);
354 open c1;
355 fetch c1 into g_global_pen_rec;
356 if c1%notfound then
357 g_global_pen_rec := l_pen_rec;
358 end if;
359 close c1;
360 p_global_pen_rec := g_global_pen_rec;
361
362 hr_utility.set_location('Leaving '||l_proc,99);
363 end get_pen;
364 ------------------------------------------------------------------------------
365 -- get_pen - overloaded
366 ------------------------------------------------------------------------------
367 -- This one is mostly for determine-date where result id is not passed in and it
368 -- wants to get the result based on per-in-ler and comp-object information.
369 procedure get_pen
370 (p_per_in_ler_id in number
371 ,p_pgm_id in number
372 ,p_pl_id in number
373 ,p_oipl_id in number
374 ,p_effective_date in date
375 ,p_global_pen_rec out nocopy ben_prtt_enrt_rslt_f%rowtype) is
376
377 l_proc varchar2(80) := g_package||'get_pen_o';
378
379 cursor c1 is
380 select pen.*
381 from ben_prtt_enrt_rslt_f pen
382 where pen.per_in_ler_id = p_per_in_ler_id and
383 pen.pl_id=p_pl_id
384 and nvl(pen.pgm_id,-1)=nvl(p_pgm_id,-1)
385 and nvl(pen.oipl_id,-1)=nvl(p_oipl_id,-1)
386 and pen.prtt_enrt_rslt_stat_cd is null
387 and p_effective_date
388 between pen.effective_start_date
389 and pen.effective_end_date;
390
391 l_pen_rec ben_prtt_enrt_rslt_f%rowtype;
392
393 begin
394 hr_utility.set_location('Entering '||l_proc,10);
395
396 hr_api.mandatory_arg_error(p_api_name => l_proc,
397 p_argument => 'p_pl_id',
398 p_argument_value => p_pl_id);
399 open c1;
400 fetch c1 into g_global_pen_rec;
401 if c1%notfound then
402 g_global_pen_rec := l_pen_rec;
403 end if;
404 close c1;
405 p_global_pen_rec := g_global_pen_rec;
406
407 hr_utility.set_location('Leaving '||l_proc,99);
408
409 end get_pen;
410 ------------------------------------------------------------------------------
411 -- reload_pen
412 ------------------------------------------------------------------------------
413 procedure reload_pen
414 (p_prtt_enrt_rslt_id in number
415 ,p_effective_date in date
416 ,p_global_pen_rec out nocopy ben_prtt_enrt_rslt_f%rowtype) is
417
418 l_proc varchar2(80) := g_package||'reload_pen';
419
420
421 begin
422 hr_utility.set_location('Entering '||l_proc,10);
423 hr_api.mandatory_arg_error(p_api_name => l_proc,
424 p_argument => 'p_prtt_enrt_rslt_id',
425 p_argument_value => p_prtt_enrt_rslt_id);
426 ben_global_enrt.get_pen
427 (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
428 ,p_effective_date => p_effective_date
429 ,p_global_pen_rec => p_global_pen_rec);
430
431 hr_utility.set_location('Leaving '||l_proc,99);
432
433 end reload_pen;
434
435
436 end ben_global_enrt;