1 package ben_election_information AUTHID CURRENT_USER as
2 /* $Header: benelinf.pkh 120.2 2010/09/10 07:56:38 stee ship $ */
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
17 coverage end date for comp objects de-enrolled is in a later
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
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 02-Aug-09 krupani 115.40 8716870: Imputed Income Enhancement. Added new parameter p_imp_cvg_strt_dt
73 to election_information and election_rate_information procedure
74 */
75 /*
76 -- ----------------------------------------------------------------------------
77 -- |---------------------< BACKOUT_FUTURE_COVERAGE >-------------------------|
78 -- --------------------------------------------------------------------------+
79 procedure backout_future_coverage(p_per_in_ler_id in number,
80 p_business_group_id in number,
81 p_person_id in number,
82 p_pgm_id in number default null ,
83 p_pl_id in number default null ,
84 p_lf_evt_ocrd_dt in date ,
85 p_effective_date in date ,
86 p_prtt_enrt_rslt_id in out nocopy number) ;
87 --
88 */
89 -- ----------------------------------------------------------------------------
90 -- |-------------------< election_rate_information >-------------------------|
91 -- ---------------------------------------------------------------------------+
92 procedure election_rate_information
93 (p_calculate_only_mode in boolean default false
94 ,p_enrt_mthd_cd in varchar2
95 ,p_effective_date in date
96 ,p_prtt_enrt_rslt_id in number
97 ,p_per_in_ler_id in number
98 ,p_person_id in number
99 ,p_pgm_id in number
100 ,p_pl_id in number
101 ,p_oipl_id in number
102 ,p_enrt_rt_id in number
103 ,p_prtt_rt_val_id in out nocopy number
107 ,p_acty_ref_perd_cd in varchar2
104 ,p_rt_val in number
105 ,p_ann_rt_val in number
106 ,p_enrt_cvg_strt_dt in date
108 ,p_datetrack_mode in varchar2
109 ,p_business_group_id in number
110 ,p_bnft_amt_changed in boolean default false
111 ,p_ele_changed in boolean default null
112 ,p_rt_strt_dt in date default null
113 ,p_rt_end_dt in date default null
114 --
115 ,p_prv_rt_val out nocopy number
116 ,p_prv_ann_rt_val out nocopy number
117 ,p_imp_cvg_strt_dt in date default NULL -- Enh 8716870
118 );
119 -- ----------------------------------------------------------------------------
120 -- |--------------------------< election_information >-------------------------|
121 -- --------------------------------------------------------------------------+
122 -- OVERLOADED, SEE BELOW.
123 procedure election_information
124 (p_validate in boolean default FALSE
125 ,p_elig_per_elctbl_chc_id in number
126 ,p_prtt_enrt_rslt_id in out nocopy number
127 ,p_effective_date in date
128 ,p_enrt_mthd_cd in varchar2
129 ,p_enrt_bnft_id in number
130 ,p_bnft_val in number default null
131 ,p_enrt_cvg_strt_dt in date default null
132 ,p_enrt_cvg_thru_dt in date default null
133 ,p_enrt_rt_id1 in number default null
134 ,p_prtt_rt_val_id1 in out nocopy number
135 ,p_rt_val1 in number default null
136 ,p_ann_rt_val1 in number default null
137 ,p_rt_strt_dt1 in date default null
138 ,p_rt_end_dt1 in date default null
139 ,p_enrt_rt_id2 in number default null
140 ,p_prtt_rt_val_id2 in out nocopy number
141 ,p_rt_val2 in number default null
142 ,p_ann_rt_val2 in number default null
143 ,p_rt_strt_dt2 in date default null
144 ,p_rt_end_dt2 in date default null
145 ,p_enrt_rt_id3 in number default null
146 ,p_prtt_rt_val_id3 in out nocopy number
147 ,p_rt_val3 in number default null
148 ,p_ann_rt_val3 in number default null
149 ,p_rt_strt_dt3 in date default null
150 ,p_rt_end_dt3 in date default null
151 ,p_enrt_rt_id4 in number default null
152 ,p_prtt_rt_val_id4 in out nocopy number
153 ,p_rt_val4 in number default null
154 ,p_ann_rt_val4 in number default null
155 ,p_rt_strt_dt4 in date default null
156 ,p_rt_end_dt4 in date default null
157 ,p_enrt_rt_id5 in number default null
158 ,p_prtt_rt_val_id5 in out nocopy number
159 ,p_rt_val5 in number default null
160 ,p_ann_rt_val5 in number default null
161 ,p_rt_strt_dt5 in date default null
162 ,p_rt_end_dt5 in date default null
163 ,p_enrt_rt_id6 in number default null
164 ,p_prtt_rt_val_id6 in out nocopy number
165 ,p_rt_val6 in number default null
166 ,p_ann_rt_val6 in number default null
167 ,p_rt_strt_dt6 in date default null
168 ,p_rt_end_dt6 in date default null
169 ,p_enrt_rt_id7 in number default null
170 ,p_prtt_rt_val_id7 in out nocopy number
171 ,p_rt_val7 in number default null
172 ,p_ann_rt_val7 in number default null
173 ,p_rt_strt_dt7 in date default null
174 ,p_rt_end_dt7 in date default null
175 ,p_enrt_rt_id8 in number default null
176 ,p_prtt_rt_val_id8 in out nocopy number
177 ,p_rt_val8 in number default null
178 ,p_ann_rt_val8 in number default null
179 ,p_rt_strt_dt8 in date default null
180 ,p_rt_end_dt8 in date default null
181 ,p_enrt_rt_id9 in number default null
182 ,p_prtt_rt_val_id9 in out nocopy number
183 ,p_rt_val9 in number default null
184 ,p_ann_rt_val9 in number default null
185 ,p_rt_strt_dt9 in date default null
186 ,p_rt_end_dt9 in date default null
187 ,p_enrt_rt_id10 in number default null
188 ,p_prtt_rt_val_id10 in out nocopy number
189 ,p_rt_val10 in number default null
190 ,p_ann_rt_val10 in number default null
191 ,p_rt_strt_dt10 in date default null
192 ,p_rt_end_dt10 in date default null
193 ,p_datetrack_mode in varchar2
194 ,p_suspend_flag in out nocopy varchar2
195 ,p_effective_start_date out nocopy date
196 ,p_effective_end_date out nocopy date
197 ,p_object_version_number in out nocopy number
198 ,p_prtt_enrt_interim_id out nocopy number
199 ,p_business_group_id in number
200 ,p_pen_attribute_category in varchar2 default hr_api.g_varchar2
201 ,p_pen_attribute1 in varchar2 default hr_api.g_varchar2
202 ,p_pen_attribute2 in varchar2 default hr_api.g_varchar2
203 ,p_pen_attribute3 in varchar2 default hr_api.g_varchar2
204 ,p_pen_attribute4 in varchar2 default hr_api.g_varchar2
205 ,p_pen_attribute5 in varchar2 default hr_api.g_varchar2
206 ,p_pen_attribute6 in varchar2 default hr_api.g_varchar2
207 ,p_pen_attribute7 in varchar2 default hr_api.g_varchar2
208 ,p_pen_attribute8 in varchar2 default hr_api.g_varchar2
209 ,p_pen_attribute9 in varchar2 default hr_api.g_varchar2
210 ,p_pen_attribute10 in varchar2 default hr_api.g_varchar2
211 ,p_pen_attribute11 in varchar2 default hr_api.g_varchar2
212 ,p_pen_attribute12 in varchar2 default hr_api.g_varchar2
213 ,p_pen_attribute13 in varchar2 default hr_api.g_varchar2
217 ,p_pen_attribute17 in varchar2 default hr_api.g_varchar2
214 ,p_pen_attribute14 in varchar2 default hr_api.g_varchar2
215 ,p_pen_attribute15 in varchar2 default hr_api.g_varchar2
216 ,p_pen_attribute16 in varchar2 default hr_api.g_varchar2
218 ,p_pen_attribute18 in varchar2 default hr_api.g_varchar2
219 ,p_pen_attribute19 in varchar2 default hr_api.g_varchar2
220 ,p_pen_attribute20 in varchar2 default hr_api.g_varchar2
221 ,p_pen_attribute21 in varchar2 default hr_api.g_varchar2
222 ,p_pen_attribute22 in varchar2 default hr_api.g_varchar2
223 ,p_pen_attribute23 in varchar2 default hr_api.g_varchar2
224 ,p_pen_attribute24 in varchar2 default hr_api.g_varchar2
225 ,p_pen_attribute25 in varchar2 default hr_api.g_varchar2
226 ,p_pen_attribute26 in varchar2 default hr_api.g_varchar2
227 ,p_pen_attribute27 in varchar2 default hr_api.g_varchar2
228 ,p_pen_attribute28 in varchar2 default hr_api.g_varchar2
229 ,p_pen_attribute29 in varchar2 default hr_api.g_varchar2
230 ,p_pen_attribute30 in varchar2 default hr_api.g_varchar2
231 ,p_dpnt_actn_warning out nocopy boolean
232 ,p_bnf_actn_warning out nocopy boolean
233 ,p_ctfn_actn_warning out nocopy boolean);
234 -- ----------------------------------------------------------------------------
235 -- |--------------------------< election_information >-------------------------|
236 -- --------------------------------------------------------------------------+
237 -- OVERLOADED, SEE ABOVE.
238 procedure election_information
239 (p_validate in boolean default FALSE
240 ,p_elig_per_elctbl_chc_id in number
241 ,p_prtt_enrt_rslt_id in out nocopy number
242 ,p_effective_date in date
243 ,p_enrt_mthd_cd in varchar2
244 ,p_enrt_bnft_id in number
245 ,p_bnft_val in number default null
246 ,p_enrt_cvg_strt_dt in date default null
247 ,p_enrt_cvg_thru_dt in date default null
248 ,p_enrt_rt_id1 in number default null
249 ,p_prtt_rt_val_id1 in out nocopy number
250 ,p_rt_val1 in number default null
251 ,p_ann_rt_val1 in number default null
252 ,p_rt_strt_dt1 in date default null
253 ,p_rt_end_dt1 in date default null
254 ,p_enrt_rt_id2 in number default null
255 ,p_prtt_rt_val_id2 in out nocopy number
256 ,p_rt_val2 in number default null
257 ,p_ann_rt_val2 in number default null
258 ,p_rt_strt_dt2 in date default null
259 ,p_rt_end_dt2 in date default null
260 ,p_enrt_rt_id3 in number default null
261 ,p_prtt_rt_val_id3 in out nocopy number
262 ,p_rt_val3 in number default null
263 ,p_ann_rt_val3 in number default null
264 ,p_rt_strt_dt3 in date default null
265 ,p_rt_end_dt3 in date default null
266 ,p_enrt_rt_id4 in number default null
267 ,p_prtt_rt_val_id4 in out nocopy number
268 ,p_rt_val4 in number default null
269 ,p_ann_rt_val4 in number default null
270 ,p_rt_strt_dt4 in date default null
271 ,p_rt_end_dt4 in date default null
272 ,p_enrt_rt_id5 in number default null
273 ,p_prtt_rt_val_id5 in out nocopy number
274 ,p_rt_val5 in number default null
275 ,p_ann_rt_val5 in number default null
276 ,p_rt_strt_dt5 in date default null
277 ,p_rt_end_dt5 in date default null
278 ,p_enrt_rt_id6 in number default null
279 ,p_prtt_rt_val_id6 in out nocopy number
280 ,p_rt_val6 in number default null
281 ,p_ann_rt_val6 in number default null
282 ,p_rt_strt_dt6 in date default null
283 ,p_rt_end_dt6 in date default null
284 ,p_enrt_rt_id7 in number default null
285 ,p_prtt_rt_val_id7 in out nocopy number
286 ,p_rt_val7 in number default null
287 ,p_ann_rt_val7 in number default null
288 ,p_rt_strt_dt7 in date default null
289 ,p_rt_end_dt7 in date default null
290 ,p_enrt_rt_id8 in number default null
291 ,p_prtt_rt_val_id8 in out nocopy number
292 ,p_rt_val8 in number default null
293 ,p_ann_rt_val8 in number default null
294 ,p_rt_strt_dt8 in date default null
295 ,p_rt_end_dt8 in date default null
296 ,p_enrt_rt_id9 in number default null
297 ,p_prtt_rt_val_id9 in out nocopy number
298 ,p_rt_val9 in number default null
299 ,p_ann_rt_val9 in number default null
300 ,p_rt_strt_dt9 in date default null
301 ,p_rt_end_dt9 in date default null
302 ,p_enrt_rt_id10 in number default null
303 ,p_prtt_rt_val_id10 in out nocopy number
304 ,p_rt_val10 in number default null
305 ,p_ann_rt_val10 in number default null
306 ,p_rt_strt_dt10 in date default null
307 ,p_rt_end_dt10 in date default null
308 ,p_datetrack_mode in varchar2
309 ,p_suspend_flag in out nocopy varchar2
310 ,p_called_from_sspnd in varchar2 -- flag not other spec
311 ,p_effective_start_date out nocopy date
312 ,p_effective_end_date out nocopy date
313 ,p_object_version_number in out nocopy number
314 ,p_prtt_enrt_interim_id out nocopy number
315 ,p_business_group_id in number
316 ,p_pen_attribute_category in varchar2 default hr_api.g_varchar2
317 ,p_pen_attribute1 in varchar2 default hr_api.g_varchar2
318 ,p_pen_attribute2 in varchar2 default hr_api.g_varchar2
322 ,p_pen_attribute6 in varchar2 default hr_api.g_varchar2
319 ,p_pen_attribute3 in varchar2 default hr_api.g_varchar2
320 ,p_pen_attribute4 in varchar2 default hr_api.g_varchar2
321 ,p_pen_attribute5 in varchar2 default hr_api.g_varchar2
323 ,p_pen_attribute7 in varchar2 default hr_api.g_varchar2
324 ,p_pen_attribute8 in varchar2 default hr_api.g_varchar2
325 ,p_pen_attribute9 in varchar2 default hr_api.g_varchar2
326 ,p_pen_attribute10 in varchar2 default hr_api.g_varchar2
327 ,p_pen_attribute11 in varchar2 default hr_api.g_varchar2
328 ,p_pen_attribute12 in varchar2 default hr_api.g_varchar2
329 ,p_pen_attribute13 in varchar2 default hr_api.g_varchar2
330 ,p_pen_attribute14 in varchar2 default hr_api.g_varchar2
331 ,p_pen_attribute15 in varchar2 default hr_api.g_varchar2
332 ,p_pen_attribute16 in varchar2 default hr_api.g_varchar2
333 ,p_pen_attribute17 in varchar2 default hr_api.g_varchar2
334 ,p_pen_attribute18 in varchar2 default hr_api.g_varchar2
335 ,p_pen_attribute19 in varchar2 default hr_api.g_varchar2
336 ,p_pen_attribute20 in varchar2 default hr_api.g_varchar2
337 ,p_pen_attribute21 in varchar2 default hr_api.g_varchar2
338 ,p_pen_attribute22 in varchar2 default hr_api.g_varchar2
339 ,p_pen_attribute23 in varchar2 default hr_api.g_varchar2
340 ,p_pen_attribute24 in varchar2 default hr_api.g_varchar2
341 ,p_pen_attribute25 in varchar2 default hr_api.g_varchar2
342 ,p_pen_attribute26 in varchar2 default hr_api.g_varchar2
343 ,p_pen_attribute27 in varchar2 default hr_api.g_varchar2
344 ,p_pen_attribute28 in varchar2 default hr_api.g_varchar2
345 ,p_pen_attribute29 in varchar2 default hr_api.g_varchar2
346 ,p_pen_attribute30 in varchar2 default hr_api.g_varchar2
347 ,p_dpnt_actn_warning out nocopy boolean
348 ,p_bnf_actn_warning out nocopy boolean
349 ,p_ctfn_actn_warning out nocopy boolean
350 ,p_imp_cvg_strt_dt in date default NULL -- 8716870
351 );
352
353 -- ----------------------------------------------------------------------------
354 -- |-------------------------< election_information_w >------------------------|
355 -- --------------------------------------------------------------------------+
356 procedure election_information_w
357 (p_validate in varchar2 default 'N'
358 ,p_elig_per_elctbl_chc_id in number
359 ,p_prtt_enrt_rslt_id in number
360 ,p_effective_date in date
361 ,p_enrt_mthd_cd in varchar2
362 ,p_enrt_bnft_id in number
363 ,p_bnft_val in number default null
364 ,p_enrt_rt_id in number default null
365 ,p_prtt_rt_val_id in number
366 ,p_rt_val in number default null
367 ,p_ann_rt_val in number default null
368 ,p_datetrack_mode in varchar2
369 ,p_suspend_flag in varchar2
370 ,p_effective_start_date in date
371 ,p_object_version_number in number
372 ,p_business_group_id in number
373 ,p_enrt_rt_id2 in number default null
374 ,p_prtt_rt_val_id2 in number
375 ,p_rt_val2 in number default null
376 ,p_ann_rt_val2 in number default null
377 ,p_enrt_rt_id3 in number default null
378 ,p_prtt_rt_val_id3 in number
379 ,p_rt_val3 in number default null
380 ,p_ann_rt_val3 in number default null
381 ,p_enrt_rt_id4 in number default null
382 ,p_prtt_rt_val_id4 in number
383 ,p_rt_val4 in number default null
384 ,p_ann_rt_val4 in number default null
385 ,p_person_id in number default null
386 ,p_enrt_cvg_strt_dt in date default null
387 ,p_enrt_cvg_thru_dt in date default null
388 ,p_rt_update_mode in varchar2 default null
389 ,p_rt_strt_dt1 in date default null
390 ,p_rt_end_dt1 in date default null
391 ,p_rt_strt_dt_cd1 in varchar2 default null
392 ,p_return_status out nocopy varchar2
393 );
394
395 --
396 -- Overloaded.
397 --
398 procedure election_information_w
399 (p_validate in varchar2 default 'N'
400 ,p_elig_per_elctbl_chc_id in number
401 ,p_prtt_enrt_rslt_id in number
402 ,p_effective_date in date
403 ,p_person_id in number default null
404 ,p_enrt_mthd_cd in varchar2
405 ,p_enrt_bnft_id in number
406 ,p_bnft_val in number default null
407 ,p_enrt_rt_id1 in number default null
408 ,p_prtt_rt_val_id1 in number default null
409 ,p_rt_val1 in number default null
410 ,p_ann_rt_val1 in number default null
411 ,p_rt_strt_dt1 in date default null
412 ,p_rt_end_dt1 in date default null
413 ,p_rt_strt_dt_cd1 in varchar2 default null
414 ,p_enrt_rt_id2 in number default null
415 ,p_prtt_rt_val_id2 in number default null
416 ,p_rt_val2 in number default null
417 ,p_ann_rt_val2 in number default null
418 ,p_rt_strt_dt2 in date default null
419 ,p_rt_end_dt2 in date default null
420 ,p_enrt_rt_id3 in number default null
421 ,p_prtt_rt_val_id3 in number default null
422 ,p_rt_val3 in number default null
423 ,p_ann_rt_val3 in number default null
424 ,p_rt_strt_dt3 in date default null
425 ,p_rt_end_dt3 in date default null
426 ,p_enrt_rt_id4 in number default null
427 ,p_prtt_rt_val_id4 in number default null
428 ,p_rt_val4 in number default null
429 ,p_ann_rt_val4 in number default null
430 ,p_rt_strt_dt4 in date default null
431 ,p_rt_end_dt4 in date default null
432 ,p_datetrack_mode in varchar2
433 ,p_suspend_flag in varchar2
434 ,p_effective_start_date in date
435 ,p_object_version_number in number
436 ,p_business_group_id in number
437 ,p_enrt_cvg_strt_dt in date
438 ,p_enrt_cvg_thru_dt in date
439 ,p_rt_update_mode in varchar2 default null
440 ,p_api_error out nocopy boolean);
441
442 -- ----------------------------------------------------------------------------
443 -- |-----------------------------< MANAGE_ENRT_BNFT >-------------------------|
444 -- --------------------------------------------------------------------------+
445 procedure MANAGE_ENRT_BNFT
446 (p_prtt_enrt_rslt_id IN number
447 ,p_enrt_bnft_id in number default null
448 ,p_object_version_number in out nocopy number
449 ,p_business_group_id in number
450 ,p_effective_date in date
451 ,p_per_in_ler_id in number
452 ,p_created_by in varchar2 default null
453 ,p_creation_date in date default null
454 );
455 --
456 -- Globals for use by bensuenr, suspend_enrollment
457 --
458 g_enrt_bnft_id number;
459 g_bnft_val number;
460 g_elig_per_elctbl_chc_id number;
461 --
462 end ben_election_information;