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