1 package ben_election_information as
2 /* $Header: benelinf.pkh 120.0.12000000.1 2007/01/19 18:03:00 appldev noship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1998 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 Name
12 Determine Election Information
13 Purpose
14 This process creates or updates the participant's record with
15 information about plans and options elected. This process
16 determines the effective date of new elections. The enrollment
17 coverage end date for comp objects de-enrolled is in a later
18 function.
19 History
20 Date Who Version What?
21 ---- --- ------- -----
22 20 Apr 98 jcarpent 110.0 Created
23 19 Oct 98 jcarpent 115.7 Removed election_rate_information
24 From header only.
25 27 Oct 98 jcarpent 115.8 Added election_rate_info back.
26 18 Dec 98 jcarpent 115.9 Added p_enrt_mthd_cd to
27 election_rate_info..
28 19 May 99 lmcdonal 115.10 Overloaded election_information with
29 parm to set different save points.
30 20 Jul 99 jcarpent 115.11 Added bnft_amt_change.
31 12 Aug 99 gperry 115.13 backport of 115.10
32 12 Aug 99 gperry 115.14 backport of 115.12
33 14 Sep 99 shdas 115.15 changed election_information to add\ bnft_val
34 12-Nov-1999 jcarpent 115.16 Added bnft/chc globals.
35 15-Aug-2000 maagrawa 115.17 Added procedure election_information_w
36 (Wrapper for self-service).
37 14-Dec-2000 maagrawa 115.18 Overloaded the self-service wrapper.
38 03-Jan-2001 ikasire 115.19 added commit and uncommented exit
39 rollback
40 05-Jan-2001 maagrawa 115.20 Added parameters enrt_cvg_strt_dt,
41 enrt_cvg_thru_dt to procedure
42 election_information and _w.
43 15-Jan-2001 maagrawa 115.21 Modified the self-service wrapper
44 to handle multiple rates.
45 16-Jan-01 mhoyes 115.91 - Added calculate only mode parameter
46 to election_rate_information.
47 09-Mar-01 maagrawa 115.92 Added rt_strt_dt and rt_end_dt
48 parameters.
49 24-Jul-2001 mmorishi 115.24 Added rt_strt_dt_cd and person_id
50 parms to election_information_w.
51 17-Aug-2001 maagrawa 115.25 Added parameter p_rt_update_mode
52 to election_information_w.
53 08-Feb-2002 gsheelum 115.26 2218845 added default null to
54 param P_RT_UPDATE_MODE
55 08-Feb-2002 gsheelum 115.27 GSCC compliance comments
56 10-Oct-2002 shdas 115.28 Added parameters to election_information_w
57 02-Dec-2002 kmullapu 115.29 Added out nocopy param to election_information_w
58 24-Jan-2003 ikasire 115.30 nocopy changes
59 01-Oct-2003 mmudigon 115.31 Bug 2775742. new param p_ele_changed
60 23-Aug-04 mmudigon 115.32 CFW. Added p_act_item_flag
61 2534391 :NEED TO LEAVE ACTION ITEMS
62 09-sep-04 mmudigon 115.33 CFW. p_act_item_flag no longer needed
63 30-Nov-04 ikasire 115.34 SSBEN Datatype changes from varchar2 to date
64 Bug 3988565
65 02-Dec-04 ikasire 115.35 BUG 4031416 Externalized backout_future_coverage
66 procedure for calling from beninelg
67 22-Dec-04 maagrawa 115.36 Added more parms to election_information_w
68 to have both procedures in sync.
69 23-Dec-04 tjesumic 115.37 new param p_prtt_enrt_rslt_id added backout_future_coverage
70 07-Feb-05 tjesumic 115.38 backout_future_coverage removed # 4118315
71 13-Apr-05 ikasire 115.39 Added a new parameter to manage_enrt_bnft procedure
72 */
73 /*
74 -- ----------------------------------------------------------------------------
75 -- |---------------------< BACKOUT_FUTURE_COVERAGE >-------------------------|
76 -- --------------------------------------------------------------------------+
77 procedure backout_future_coverage(p_per_in_ler_id in number,
78 p_business_group_id in number,
79 p_person_id in number,
80 p_pgm_id in number default null ,
81 p_pl_id in number default null ,
82 p_lf_evt_ocrd_dt in date ,
83 p_effective_date in date ,
84 p_prtt_enrt_rslt_id in out nocopy number) ;
85 --
86 */
87 -- ----------------------------------------------------------------------------
88 -- |-------------------< election_rate_information >-------------------------|
89 -- ---------------------------------------------------------------------------+
90 procedure election_rate_information
91 (p_calculate_only_mode in boolean default false
92 ,p_enrt_mthd_cd in varchar2
93 ,p_effective_date in date
94 ,p_prtt_enrt_rslt_id in number
95 ,p_per_in_ler_id in number
96 ,p_person_id in number
97 ,p_pgm_id in number
98 ,p_pl_id in number
99 ,p_oipl_id in number
100 ,p_enrt_rt_id in number
101 ,p_prtt_rt_val_id in out nocopy number
102 ,p_rt_val in number
103 ,p_ann_rt_val in number
104 ,p_enrt_cvg_strt_dt in date
105 ,p_acty_ref_perd_cd in varchar2
106 ,p_datetrack_mode in varchar2
107 ,p_business_group_id in number
108 ,p_bnft_amt_changed in boolean default false
109 ,p_ele_changed in boolean default null
110 ,p_rt_strt_dt in date default null
111 ,p_rt_end_dt in date default null
112 --
113 ,p_prv_rt_val out nocopy number
114 ,p_prv_ann_rt_val out nocopy number
115 );
116 -- ----------------------------------------------------------------------------
117 -- |--------------------------< election_information >-------------------------|
118 -- --------------------------------------------------------------------------+
119 -- OVERLOADED, SEE BELOW.
120 procedure election_information
121 (p_validate in boolean default FALSE
122 ,p_elig_per_elctbl_chc_id in number
123 ,p_prtt_enrt_rslt_id in out nocopy number
124 ,p_effective_date in date
125 ,p_enrt_mthd_cd in varchar2
126 ,p_enrt_bnft_id in number
127 ,p_bnft_val in number default null
128 ,p_enrt_cvg_strt_dt in date default null
129 ,p_enrt_cvg_thru_dt in date default null
130 ,p_enrt_rt_id1 in number default null
131 ,p_prtt_rt_val_id1 in out nocopy number
132 ,p_rt_val1 in number default null
133 ,p_ann_rt_val1 in number default null
134 ,p_rt_strt_dt1 in date default null
135 ,p_rt_end_dt1 in date default null
136 ,p_enrt_rt_id2 in number default null
137 ,p_prtt_rt_val_id2 in out nocopy number
138 ,p_rt_val2 in number default null
139 ,p_ann_rt_val2 in number default null
140 ,p_rt_strt_dt2 in date default null
141 ,p_rt_end_dt2 in date default null
142 ,p_enrt_rt_id3 in number default null
143 ,p_prtt_rt_val_id3 in out nocopy number
144 ,p_rt_val3 in number default null
145 ,p_ann_rt_val3 in number default null
146 ,p_rt_strt_dt3 in date default null
147 ,p_rt_end_dt3 in date default null
148 ,p_enrt_rt_id4 in number default null
149 ,p_prtt_rt_val_id4 in out nocopy number
150 ,p_rt_val4 in number default null
151 ,p_ann_rt_val4 in number default null
152 ,p_rt_strt_dt4 in date default null
153 ,p_rt_end_dt4 in date default null
154 ,p_enrt_rt_id5 in number default null
155 ,p_prtt_rt_val_id5 in out nocopy number
156 ,p_rt_val5 in number default null
157 ,p_ann_rt_val5 in number default null
158 ,p_rt_strt_dt5 in date default null
159 ,p_rt_end_dt5 in date default null
160 ,p_enrt_rt_id6 in number default null
161 ,p_prtt_rt_val_id6 in out nocopy number
162 ,p_rt_val6 in number default null
163 ,p_ann_rt_val6 in number default null
164 ,p_rt_strt_dt6 in date default null
165 ,p_rt_end_dt6 in date default null
166 ,p_enrt_rt_id7 in number default null
167 ,p_prtt_rt_val_id7 in out nocopy number
168 ,p_rt_val7 in number default null
169 ,p_ann_rt_val7 in number default null
170 ,p_rt_strt_dt7 in date default null
171 ,p_rt_end_dt7 in date default null
172 ,p_enrt_rt_id8 in number default null
173 ,p_prtt_rt_val_id8 in out nocopy number
174 ,p_rt_val8 in number default null
175 ,p_ann_rt_val8 in number default null
176 ,p_rt_strt_dt8 in date default null
177 ,p_rt_end_dt8 in date default null
178 ,p_enrt_rt_id9 in number default null
179 ,p_prtt_rt_val_id9 in out nocopy number
180 ,p_rt_val9 in number default null
181 ,p_ann_rt_val9 in number default null
182 ,p_rt_strt_dt9 in date default null
183 ,p_rt_end_dt9 in date default null
184 ,p_enrt_rt_id10 in number default null
185 ,p_prtt_rt_val_id10 in out nocopy number
186 ,p_rt_val10 in number default null
187 ,p_ann_rt_val10 in number default null
188 ,p_rt_strt_dt10 in date default null
189 ,p_rt_end_dt10 in date default null
190 ,p_datetrack_mode in varchar2
191 ,p_suspend_flag in out nocopy varchar2
192 ,p_effective_start_date out nocopy date
193 ,p_effective_end_date out nocopy date
194 ,p_object_version_number in out nocopy number
195 ,p_prtt_enrt_interim_id out nocopy number
196 ,p_business_group_id in number
197 ,p_pen_attribute_category in varchar2 default hr_api.g_varchar2
198 ,p_pen_attribute1 in varchar2 default hr_api.g_varchar2
199 ,p_pen_attribute2 in varchar2 default hr_api.g_varchar2
200 ,p_pen_attribute3 in varchar2 default hr_api.g_varchar2
201 ,p_pen_attribute4 in varchar2 default hr_api.g_varchar2
202 ,p_pen_attribute5 in varchar2 default hr_api.g_varchar2
203 ,p_pen_attribute6 in varchar2 default hr_api.g_varchar2
204 ,p_pen_attribute7 in varchar2 default hr_api.g_varchar2
205 ,p_pen_attribute8 in varchar2 default hr_api.g_varchar2
206 ,p_pen_attribute9 in varchar2 default hr_api.g_varchar2
207 ,p_pen_attribute10 in varchar2 default hr_api.g_varchar2
208 ,p_pen_attribute11 in varchar2 default hr_api.g_varchar2
209 ,p_pen_attribute12 in varchar2 default hr_api.g_varchar2
210 ,p_pen_attribute13 in varchar2 default hr_api.g_varchar2
211 ,p_pen_attribute14 in varchar2 default hr_api.g_varchar2
212 ,p_pen_attribute15 in varchar2 default hr_api.g_varchar2
213 ,p_pen_attribute16 in varchar2 default hr_api.g_varchar2
214 ,p_pen_attribute17 in varchar2 default hr_api.g_varchar2
215 ,p_pen_attribute18 in varchar2 default hr_api.g_varchar2
216 ,p_pen_attribute19 in varchar2 default hr_api.g_varchar2
217 ,p_pen_attribute20 in varchar2 default hr_api.g_varchar2
218 ,p_pen_attribute21 in varchar2 default hr_api.g_varchar2
219 ,p_pen_attribute22 in varchar2 default hr_api.g_varchar2
220 ,p_pen_attribute23 in varchar2 default hr_api.g_varchar2
221 ,p_pen_attribute24 in varchar2 default hr_api.g_varchar2
222 ,p_pen_attribute25 in varchar2 default hr_api.g_varchar2
223 ,p_pen_attribute26 in varchar2 default hr_api.g_varchar2
224 ,p_pen_attribute27 in varchar2 default hr_api.g_varchar2
225 ,p_pen_attribute28 in varchar2 default hr_api.g_varchar2
226 ,p_pen_attribute29 in varchar2 default hr_api.g_varchar2
227 ,p_pen_attribute30 in varchar2 default hr_api.g_varchar2
228 ,p_dpnt_actn_warning out nocopy boolean
229 ,p_bnf_actn_warning out nocopy boolean
230 ,p_ctfn_actn_warning out nocopy boolean);
231 -- ----------------------------------------------------------------------------
232 -- |--------------------------< election_information >-------------------------|
233 -- --------------------------------------------------------------------------+
234 -- OVERLOADED, SEE ABOVE.
235 procedure election_information
236 (p_validate in boolean default FALSE
237 ,p_elig_per_elctbl_chc_id in number
238 ,p_prtt_enrt_rslt_id in out nocopy number
239 ,p_effective_date in date
240 ,p_enrt_mthd_cd in varchar2
241 ,p_enrt_bnft_id in number
242 ,p_bnft_val in number default null
243 ,p_enrt_cvg_strt_dt in date default null
244 ,p_enrt_cvg_thru_dt in date default null
245 ,p_enrt_rt_id1 in number default null
246 ,p_prtt_rt_val_id1 in out nocopy number
247 ,p_rt_val1 in number default null
248 ,p_ann_rt_val1 in number default null
249 ,p_rt_strt_dt1 in date default null
250 ,p_rt_end_dt1 in date default null
251 ,p_enrt_rt_id2 in number default null
252 ,p_prtt_rt_val_id2 in out nocopy number
253 ,p_rt_val2 in number default null
254 ,p_ann_rt_val2 in number default null
255 ,p_rt_strt_dt2 in date default null
256 ,p_rt_end_dt2 in date default null
257 ,p_enrt_rt_id3 in number default null
258 ,p_prtt_rt_val_id3 in out nocopy number
259 ,p_rt_val3 in number default null
260 ,p_ann_rt_val3 in number default null
261 ,p_rt_strt_dt3 in date default null
262 ,p_rt_end_dt3 in date default null
263 ,p_enrt_rt_id4 in number default null
264 ,p_prtt_rt_val_id4 in out nocopy number
265 ,p_rt_val4 in number default null
266 ,p_ann_rt_val4 in number default null
267 ,p_rt_strt_dt4 in date default null
268 ,p_rt_end_dt4 in date default null
269 ,p_enrt_rt_id5 in number default null
270 ,p_prtt_rt_val_id5 in out nocopy number
271 ,p_rt_val5 in number default null
272 ,p_ann_rt_val5 in number default null
273 ,p_rt_strt_dt5 in date default null
274 ,p_rt_end_dt5 in date default null
275 ,p_enrt_rt_id6 in number default null
276 ,p_prtt_rt_val_id6 in out nocopy number
277 ,p_rt_val6 in number default null
278 ,p_ann_rt_val6 in number default null
279 ,p_rt_strt_dt6 in date default null
280 ,p_rt_end_dt6 in date default null
281 ,p_enrt_rt_id7 in number default null
282 ,p_prtt_rt_val_id7 in out nocopy number
283 ,p_rt_val7 in number default null
284 ,p_ann_rt_val7 in number default null
285 ,p_rt_strt_dt7 in date default null
286 ,p_rt_end_dt7 in date default null
287 ,p_enrt_rt_id8 in number default null
288 ,p_prtt_rt_val_id8 in out nocopy number
289 ,p_rt_val8 in number default null
290 ,p_ann_rt_val8 in number default null
291 ,p_rt_strt_dt8 in date default null
292 ,p_rt_end_dt8 in date default null
293 ,p_enrt_rt_id9 in number default null
297 ,p_rt_strt_dt9 in date default null
294 ,p_prtt_rt_val_id9 in out nocopy number
295 ,p_rt_val9 in number default null
296 ,p_ann_rt_val9 in number default null
298 ,p_rt_end_dt9 in date default null
299 ,p_enrt_rt_id10 in number default null
300 ,p_prtt_rt_val_id10 in out nocopy number
301 ,p_rt_val10 in number default null
302 ,p_ann_rt_val10 in number default null
303 ,p_rt_strt_dt10 in date default null
304 ,p_rt_end_dt10 in date default null
305 ,p_datetrack_mode in varchar2
306 ,p_suspend_flag in out nocopy varchar2
307 ,p_called_from_sspnd in varchar2 -- flag not other spec
308 ,p_effective_start_date out nocopy date
309 ,p_effective_end_date out nocopy date
310 ,p_object_version_number in out nocopy number
311 ,p_prtt_enrt_interim_id out nocopy number
312 ,p_business_group_id in number
313 ,p_pen_attribute_category in varchar2 default hr_api.g_varchar2
314 ,p_pen_attribute1 in varchar2 default hr_api.g_varchar2
315 ,p_pen_attribute2 in varchar2 default hr_api.g_varchar2
316 ,p_pen_attribute3 in varchar2 default hr_api.g_varchar2
317 ,p_pen_attribute4 in varchar2 default hr_api.g_varchar2
318 ,p_pen_attribute5 in varchar2 default hr_api.g_varchar2
319 ,p_pen_attribute6 in varchar2 default hr_api.g_varchar2
320 ,p_pen_attribute7 in varchar2 default hr_api.g_varchar2
321 ,p_pen_attribute8 in varchar2 default hr_api.g_varchar2
322 ,p_pen_attribute9 in varchar2 default hr_api.g_varchar2
323 ,p_pen_attribute10 in varchar2 default hr_api.g_varchar2
324 ,p_pen_attribute11 in varchar2 default hr_api.g_varchar2
325 ,p_pen_attribute12 in varchar2 default hr_api.g_varchar2
326 ,p_pen_attribute13 in varchar2 default hr_api.g_varchar2
327 ,p_pen_attribute14 in varchar2 default hr_api.g_varchar2
328 ,p_pen_attribute15 in varchar2 default hr_api.g_varchar2
329 ,p_pen_attribute16 in varchar2 default hr_api.g_varchar2
330 ,p_pen_attribute17 in varchar2 default hr_api.g_varchar2
331 ,p_pen_attribute18 in varchar2 default hr_api.g_varchar2
332 ,p_pen_attribute19 in varchar2 default hr_api.g_varchar2
333 ,p_pen_attribute20 in varchar2 default hr_api.g_varchar2
334 ,p_pen_attribute21 in varchar2 default hr_api.g_varchar2
335 ,p_pen_attribute22 in varchar2 default hr_api.g_varchar2
336 ,p_pen_attribute23 in varchar2 default hr_api.g_varchar2
337 ,p_pen_attribute24 in varchar2 default hr_api.g_varchar2
338 ,p_pen_attribute25 in varchar2 default hr_api.g_varchar2
339 ,p_pen_attribute26 in varchar2 default hr_api.g_varchar2
340 ,p_pen_attribute27 in varchar2 default hr_api.g_varchar2
341 ,p_pen_attribute28 in varchar2 default hr_api.g_varchar2
342 ,p_pen_attribute29 in varchar2 default hr_api.g_varchar2
343 ,p_pen_attribute30 in varchar2 default hr_api.g_varchar2
344 ,p_dpnt_actn_warning out nocopy boolean
345 ,p_bnf_actn_warning out nocopy boolean
346 ,p_ctfn_actn_warning out nocopy boolean);
347
348 -- ----------------------------------------------------------------------------
349 -- |-------------------------< election_information_w >------------------------|
350 -- --------------------------------------------------------------------------+
351 procedure election_information_w
352 (p_validate in varchar2 default 'N'
353 ,p_elig_per_elctbl_chc_id in number
354 ,p_prtt_enrt_rslt_id in number
355 ,p_effective_date in date
356 ,p_enrt_mthd_cd in varchar2
357 ,p_enrt_bnft_id in number
358 ,p_bnft_val in number default null
359 ,p_enrt_rt_id in number default null
360 ,p_prtt_rt_val_id in number
361 ,p_rt_val in number default null
362 ,p_ann_rt_val in number default null
363 ,p_datetrack_mode in varchar2
364 ,p_suspend_flag in varchar2
365 ,p_effective_start_date in date
366 ,p_object_version_number in number
367 ,p_business_group_id in number
368 ,p_enrt_rt_id2 in number default null
369 ,p_prtt_rt_val_id2 in number
370 ,p_rt_val2 in number default null
371 ,p_ann_rt_val2 in number default null
372 ,p_enrt_rt_id3 in number default null
373 ,p_prtt_rt_val_id3 in number
374 ,p_rt_val3 in number default null
375 ,p_ann_rt_val3 in number default null
376 ,p_enrt_rt_id4 in number default null
377 ,p_prtt_rt_val_id4 in number
378 ,p_rt_val4 in number default null
379 ,p_ann_rt_val4 in number default null
380 ,p_person_id in number default null
381 ,p_enrt_cvg_strt_dt in date default null
382 ,p_enrt_cvg_thru_dt in date default null
383 ,p_rt_update_mode in varchar2 default null
384 ,p_rt_strt_dt1 in date default null
385 ,p_rt_end_dt1 in date default null
386 ,p_rt_strt_dt_cd1 in varchar2 default null
387 ,p_return_status out nocopy varchar2
388 );
389
390 --
391 -- Overloaded.
392 --
393 procedure election_information_w
394 (p_validate in varchar2 default 'N'
395 ,p_elig_per_elctbl_chc_id in number
396 ,p_prtt_enrt_rslt_id in number
397 ,p_effective_date in date
398 ,p_person_id in number default null
399 ,p_enrt_mthd_cd in varchar2
400 ,p_enrt_bnft_id in number
401 ,p_bnft_val in number default null
402 ,p_enrt_rt_id1 in number default null
403 ,p_prtt_rt_val_id1 in number default null
404 ,p_rt_val1 in number default null
405 ,p_ann_rt_val1 in number default null
406 ,p_rt_strt_dt1 in date default null
407 ,p_rt_end_dt1 in date default null
408 ,p_rt_strt_dt_cd1 in varchar2 default null
409 ,p_enrt_rt_id2 in number default null
410 ,p_prtt_rt_val_id2 in number default null
411 ,p_rt_val2 in number default null
412 ,p_ann_rt_val2 in number default null
413 ,p_rt_strt_dt2 in date default null
414 ,p_rt_end_dt2 in date default null
415 ,p_enrt_rt_id3 in number default null
416 ,p_prtt_rt_val_id3 in number default null
417 ,p_rt_val3 in number default null
418 ,p_ann_rt_val3 in number default null
419 ,p_rt_strt_dt3 in date default null
420 ,p_rt_end_dt3 in date default null
421 ,p_enrt_rt_id4 in number default null
422 ,p_prtt_rt_val_id4 in number default null
423 ,p_rt_val4 in number default null
424 ,p_ann_rt_val4 in number default null
425 ,p_rt_strt_dt4 in date default null
426 ,p_rt_end_dt4 in date default null
427 ,p_datetrack_mode in varchar2
428 ,p_suspend_flag in varchar2
429 ,p_effective_start_date in date
430 ,p_object_version_number in number
431 ,p_business_group_id in number
432 ,p_enrt_cvg_strt_dt in date
433 ,p_enrt_cvg_thru_dt in date
434 ,p_rt_update_mode in varchar2 default null
435 ,p_api_error out nocopy boolean);
436
437 -- ----------------------------------------------------------------------------
438 -- |-----------------------------< MANAGE_ENRT_BNFT >-------------------------|
439 -- --------------------------------------------------------------------------+
440 procedure MANAGE_ENRT_BNFT
441 (p_prtt_enrt_rslt_id IN number
442 ,p_enrt_bnft_id in number default null
443 ,p_object_version_number in out nocopy number
444 ,p_business_group_id in number
445 ,p_effective_date in date
446 ,p_per_in_ler_id in number
447 ,p_created_by in varchar2 default null
448 ,p_creation_date in date default null
449 );
450 --
451 -- Globals for use by bensuenr, suspend_enrollment
452 --
453 g_enrt_bnft_id number;
454 g_bnft_val number;
455 g_elig_per_elctbl_chc_id number;
456 --
457 end ben_election_information;