1 package body ben_sspndd_enrollment as
2 /* $Header: bensuenr.pkb 120.33.12010000.4 2008/09/18 07:08:37 sallumwa ship $ */
3 /* ===========================================================================
4 * Name
5 * Suspend enrollment
6 * Purpose
7 * This package is used to update the enrollment result to indicate
8 * it to be suspended and assign a interiem coverage if it's required
9 * and available.
10 * History
11 * Date Who Version What?
12 * ----------- ---------- ------- -----
13 * 27 May 1998 maagrawa 110.0 Created.
14 * 17 Jun 1998 maagrawa 110.1 elctbl_chc api changes.
15 * 19 Jun 1998 maagrawa 110.2 Header line changes.
16 * 24 Jun 1998 maagrawa 110.3 Added call to multi_row_edit.
17 * 07 Jul 1998 jmohapat 110.4 Added batch who col to call of
18 * ben_elig_per_elc_chc_api.update..
19 * ,ben_prtt_enrt_result_api.update.
20 * 22 Jul 1998 maagrawa 110.5 p_rslt_object_version_number argument added.
21 * 22 Sep 1998 bbulusu 110.6 removed p_enrt_mthd_cd from
22 * p_suspend_enrollment
23 * 30 Oct 1998 Hdang 115.7 Change (Un)suspend process logic Remove
24 * misc. procedures/Functions
25 * 30 Oct 1998 Hdang 115.8 add per_in_ler_id as one of input parameter.
26 * 19 Nov 1998 Hdang 115.9 unsuspended options if plan is saving plan.
27 * 10 Feb 1999 Hdang 115.10 Add logic to handle unsuspend enrollment.
28 * 19 Feb 1999 Hdang 115.11 Add logic to handle interim coverage.
29 * 03 Mar 1999 jcarpent 115.12 Removed dbms_output.put_lines
30 * 04 Mar 1999 jcarpent 115.13 Unsuspend handles old Element entries.
31 * 22 Mar 1999 jcarpent 115.15 Removed pen join in c_prv cursor
32 * 03 May 1999 jcarpent 115.16 Added check for prtt_rt_val_stat_cd is null
33 * 03 May 1999 shdas 115.17 Added contexts to rule calls.
34 * 03 May 1999 jcarpent 115.18 Added support for unsspnd_enrt_cd
35 * 03 May 1999 shdas 115.21 Added jurisdiction_cd
36 * 06 May 1999 jcarpent 115.22 check status of per_in_ler <> 'VOIDD'
37 * 07 May 1999 lmcdonal 115.23 Check status of prtt_enrt_rslt, and added
38 * per_in_ler_stat <> 'BCKDT'.
39 * 08 May 1999 jcarpent 115.24 Check ('VOIDD', 'BCKDT') for pil stat cd
40 * 11 May 1999 jcarpent 115.25 Fixed unsspnd_enrt_cd of 'UEECSD' to
41 * handle null l_rec_rt_strt_dt.
42 * 19 May 1999 jcarpent 115.26 Changed c_current_enrt cursor to union
43 * to pick up only choice for current pil
44 * or no choice at all.
45 * 19 May 1999 jcarpent 115.27 Added c_new_ovn cursor to get updated ovn.
46 * Changed determine interim result cursor
47 * to only get old enrollments.
48 * Process_interim was passing epe.ovn as
49 * the pen.ovn into ben_election_info.
50 * 09 Jul 1999 jcarpent 115.28 Added checks for backed out nocopy pil
51 * 20-JUL-1999 Gperry 115.29 genutils -> benutils package rename.
52 * 12-Aug-1999 lmcdonal 115.30 Call get_ben_pen_upd_dt_mode before calling
53 * update_prtt_enrt_rslt.
54 * 19-Aug-1999 lmcdonal 115.31 Add call to premium_warning.Made p_person_id
55 * required in update_sspndd_flag.
56 * 07-Sep-1999 tguy 115.32 fixed call to pay_mag_util
57 * 09-Sep-1999 maagrawa 115.33 Backport to 115.29. Made fix to calculate
58 * dpnt_cvg_strt_dt when the code is not null
59 * 09-Sep-1999 maagrawa 115.34 Leapfrog to 115.32 and applied changes in
60 * 115.33
61 * 14-Sep-1999 shdas 115.35 changed election_information to add bnft_amt
62 * 08-oct-1999 jcarpent 115.36 Added ed to call to create_enrollment_ele
63 * 10-oct-1999 pbodla 115.37 Added ed to call to reopen_closed_enrollment
64 * 26-Oct-1999 maagrawa 115.38 Fixed c_choice_info cursor in
65 * unsuspend_enrt to get choice for the same
66 * per_in_ler_id as the result.
67 * 05-Nov-1999 jcarpent 115.39 Fixed interim cursors to be less
68 * restrictive, use election_information globs
69 * 12-Nov-1999 lmcdonal 115.41 Better debugging message
70 * 19-Nov-1999 pbodla 115.42 Added p_elig_per_elctbl_chc_id as parameter to
71 * get_dflt_to_asn_pndg_ctfn_cd
72 * 03-Jan-2000 lmcdonal 115.43 When update_prtt_rt_val is called, update the
73 * per_in_ler_id too. This is used in a check in
74 * the election_rate_information proc to decide
75 * if the rt_strt_dt should be recalulated.
76 * Bug 1121022
77 * 06-Jan-2000 maagrawa 115.44 Update rate start date and dpnt cvg strt dt
78 * while unsuspending only if enrt cvg strt dt
79 * is to be updated.
80 * Pass person_id when calling
81 * update_prtt_rt_val api. (Bug 1096737)
82 * 13-Jan-2000 lmcdonal 115.45 When calling election_info to create a new
83 * result, pass in the correct enrt_mthd_cd.
84 * Bug 1147606.
85 * 24-Jan-2000 maagrawa 115.46 Pass per_in_ler_id when calling
86 * process_post_results (Bug 1148445)
87 * 05-Feb-2000 maagrawa 115.47 Fixed interim coverage logic in
88 * procedure determine_interim (1172233).
89 *
90 * 10-FEB-2000 shdas 115.48 call determine_date.main only if
91 * dpnt_cvg_end_dt_cd is not null.
92 * 18-Feb-2000 jcarpent 115.49 changed the c_choice_info cursor to use
93 * the comp object to join instead of the
94 * result_id since won't be the same.
95 * 18-Feb-2000 bbulusu 115.49 Added join to plan to determine if
96 * bnf designation is optional
97 * 28-Feb-2000 maagrawa 115.50 Pass p_source to delete_enrollment.
98 *
99 * 28-Feb-2000 pbodla 115.51 Bug: 4279 : passed p_prtt_rt_val_id to
100 * reopen. To get the correct element entry
101 * 24-Mar-2000 lmcdonal 115.52 better debugging messages.
102 * Bug 1247109 - was sometimes using the wrong
103 * result id for interim coverage.
104 * 28-Mar-2000 shdas 115.53 delete ledger row if result is suspended.
105 * 30-Mar-2000 lmcdonal 115.54 Bug 1252084 - When cvg restriction is Opt,
106 * we still may have a bnft row hanging off
107 * the opt. Fetch it: c_next_lower_oipl_epe,
108 * c_min_oipl_epe
109 * 13-Apr-2000 pbodla 115.55 - Bug 5052 - unsuspend_enrollment :
110 * moved the interim update or delete to
111 * the beginning of procedure. First
112 * update or delete the interim and then
113 * do the un suspend of original enrollment.
114 * 17-Apr-2000 maagrawa 115.56 - Bug 5098. Check the ctfn_rqd_flag for the
115 * benefit record also, even if the rstrn
116 * is "Option Restriction applies".
117 * 22-May-2000 lmcdonal 115.57 Bug 1249901 - when cvg is entered at enrt
118 * and interim is 'min', give them the min
119 * amt from same bfnt row.
120 * 23-May-2000 lmcdonal 115.58 Fix v57 fix so that other interims with
121 * 'min' codes work.
122 * 24-May-2000 shdas 115.59 bug 5234- interim cvg end date is set based on
123 * datetrack mode.
124 * 19-Jul-2000 rchase 115.61 bug 5353 - iterim cvg not selecting previous
125 * cvg if previous cvg exists in same pl or pl_typ
126 * 19-Jul-2000 rchase 115.62 bug 5181 - backed out nocopy cvg included in selection
127 * for current.
128 * 04-Aug-2000 jcarpent 115.63 bug 5353 - Bug was wrong. Should use
129 * code not hardcode to 'SM'
130 * Also bug 5427. Was not checking interim
131 * codes stored on plip.
132 * 28-Aug-2000 jcarpent 115.64 bug 1386626. Recalc imputed income for
133 * suspend/unsuspend enrollments.
134 *
135 * 06-Sep-2000 rchase 115.65 fix for bug#1394066. Set the interim coverage
136 * date to suspended - 1 if coverage start
137 * dates are the same.
138 * 23-Oct-2000 pbodla 115.66 fix for bug#1471135 : Added code to reset
139 * the enrt_cvg_strt_dt after the cursor
140 * csr_prtt_enrt_rslt is opened second time.
141 * 14-Nov-2000 rchase 115.67 Bug 1477284. Also look for unsuspend
142 * enrt_cd at ptip level.
143 * 09-Jan-2001 mhoyes 115.68 - Added new out nocopy parameter to call
144 * create_enrollment_element.
145 * 27-Feb-2001 kmahendr 115.69 - Bug#1649847 - changed value of parameter from
146 * p_per_in_ler_id to l_per_in_ler_id to call
147 * ben_determine_date.rate_and_coverage_dates
148 * as null was passed
149 * 29-Mar-2001 maagrawa 115.70 When the enrollment is unsuspended, update
150 * or create the element entries for rates
151 * which are active for this result and
152 * life event.
153 * 02-Apr-2001 kmahendr 115.71 - Bug#1617825 - when the enrollment is unsuspended
154 * call create_debit_ledger_entries to write ledger
155 * entries into pool
156 * 27-aug-2001 tilak 115.72 bug:1949361 jurisdiction code is
157 derived inside benutils.formula.
158 * 02-nov-2001 pbodla 115.73 bug:2088231 Called accumulate pools after
159 * unsuspend enrollment.
160 * 23-Jan-2002 ikasire 115.74 bug:2185509 when unsuspended, we are calling
161 * the ben_provider_pools.remove_bnft_prvdd_ldgr
162 * to remove the ledger entries of the interim
163 * coverage
164 * 30-Jan-2002 ikasire 115.75 Bug2191886 fixed the error where effective
165 * date is used for life event occured date in
166 * unsuspend enrollment.
167 * 28-Jan-2002 hnarayan 115.76 Bug 1826902 when rule is attached to interim
168 * coverage, the default to assign pending code
169 * returned by the rule is captured as Varchar2
170 * and returned as number. Fixed.
171 * 13-Mar-2002 pbodla 115.77 p_cmncd_rt, and p_ann_rt values passed to
172 * create_enrollment_element : Based on
173 * ele_entry_val_cd communicated and annual
174 * rate values are used in EE creation.
175 * 29-Mar-2002 ikasire 115.78 Bug 1998648 Interim issues related with
176 * default code are fixed. See bug for more
177 * details.
178 *
179 * 10-Apr-2002 ikasire 115.79 Bug 1886183 fixed the enter value at enrollment
180 * cases for the determine_interim procedure.
181 * 20-Apr-2002 ikasire 115.80 Bug 1886183 fixed the cursor which determines the
182 * current enrollment in determine_interim process
183 * 08-Jun-2002 pabodla 115.81 Do not select the contingent worker
184 * assignment when assignment data is
185 * fetched.
186 * 02-Jul-2002 pabodla 115.82 Bug 2396628: at the time of unsuspending a plan
187 * check whether interim is there or not
188 * if interim is already deleted then bypass
189 * deletion logic.
190 * 07-Jul-2002 ikasire 115.82 Bug 2502633 - Interim Enhancements
191 * See Bug for more details.
192 * 13-Aug-2002 hnarayan 115.84 Bug 2330694 - Premium handling for interim
193 * coverage. See bug for details
194 * 03-Sep-2002 ikasire 115.85 Bug 2538015 changes to unsuspend enrollment
195 * procedure to use the unsuspend code for
196 * ending the interim coverage, rates and
197 * starting the unsuspeded enrollment coverage and
198 * rates.
199 * Also fixed the interim code xxx,xxx;New, Next Lower
200 * cursor c_next_lower_pl_typ_epe to exclude the
201 * oipl under suspension.
202 * 06-Sep-2002 ikasire 115.86 if the default is another comp object and the
203 * coverage calculation is enter value at enrollment
204 * need to get the right benefit record for interim
205 * 06-Sep-2002 ikasire 115.87 Bug2543071 added the person_id and program_id in
206 * the cursor c_current_same_epe where clause
207 * 19-Sep-2002 ikasire 115.88 Bug2577315 Fixed the Next Lower Option restricitons
208 * error. Changed to (+) to nvl in where clause
209 * 26-Sep-2002 ikasire 115.89 Bug 2595113 fixed the cursor c_current_enrt in
210 * determine_interim procedure
211 * 02-Dec-2002 hnarayan 115.90 Bug 2689926 - changed unsuspend_enrollment to
212 * call ben_provider_pools.total_pools after
213 * calling accumulate_pools.
214 * 24-dec-2002 hmani 115.91 For nocopy changes
215 * 15-May-2003 ikasire 115.92 Bug2958032 Issues in Unsuspended enrollment
216 * 1.update epe with the pen id
217 * 2.update enb to removed the interim pen id
218 * and make sure we have the right pen id on
219 * the unsuspended pen
220 * 26-Jun-2003 ikasire 115.93 c_pea is getting called multiple times due
221 * to date trackupdate of the pen row.
222 * 23-Jul-2003 ikasire 115.94 Bug 3042379 fixes for indefinate loop when
223 * multiple certifications are required or
224 * dependent or beneficiary designation in
225 * combination with benefit restrictions.
226 * 18-Aug-2003 ikasire 115.95 Bug 3095291 cleaned the c_current_enrt
227 * cursor and removed the union and out joined
228 * to epe.
229 * 30-Oct-2003 kmahendr 115.96 Bug#3202455 - added a cursor c_previous_status
230 * in unsuspend_enrollment procedure.
231 * 25-Nov-2003 ikasire 115.97 Bug 3278908 Modified the c_ppe cursor get the
232 * correct record
233 * 14-Jan-2004 ikasire 115.98 Bug fix 3202455 introduced another regression
234 * Since l_previous_no_sspn is not initialized
235 * nevel goes into IF clause and always goes into
236 * else clause. This will make the system not to
237 * use unsuspend code.
238 * 21-Jan-2004 mmudigon 115.99 Bug 3317017. CWB Changes
239 * 16-Feb-2004 ikasire 115.100 Bug 3441027 compute the date track mode while
240 * unsuspending the result
241 * 08-Jun-2004 kmahendr 115.101 Bug#3659657 - added code to suspend_enroll procedure
242 * to handle correction of date received for certification
243 * 22-Jun-2004 kmahendr 115.102 Bug#3692450 - rate start date not changedif the
244 * unsuspend code is UEECSD.
245 * 30-Jun-2004 tjesumic 115.103 bug 3666347 fixed by reverting 115.84 2330694 fix
246 * 02-Aug-2004 kmahendr 115.104 Bug#3794162 - added a parameter - p_per_in_ler_id
247 * to determine_interim and modified cursors by joing
248 * per_in_ler_id
249 * 04-Aug-2004 kmahendr 115.105 Bug#3794162 - modified cursor c_interim
250 * 23-Aug-2004 mmudigon 115.106 CFW. Added p_act_item_flag to
251 * suspend_enrollment.
252 * 2534391 :NEED TO LEAVE ACTION ITEMS
253 * 26-aug-2004 nhunur 115.107 gscc compliance
254 * 05-sep-2004 ikasire 115.108 FIDOML Override Enhancements
255 * 07-Sep-2004 mmudigon 115.109 CFW. Changes to suspend_enrollment
256 * 09-Sep-2004 mmudigon 115.110 CFW. p_act_item_flag no longer needed
257 * 03-Nov-2004 ikasire 115.111 Bug 3977951 fix
258 * 13-Nov-2004 kmahendr 115.112 Bug#4009443-modified cursor c_current_enrt
259 * 16-Nov-2004 kmahendr 115.113 Bug#4009443-modified cursor c_current_enrt
260 * 30-dec-2004 nhunur 115.114 4031733 - No need to open cursor c_state.
261 * 07-Jan-2005 ikasire 115.115 Bug 4064635. Need to carry forward suspended and
262 * interim enrollment
263 * 11-Jan-2005 ikasire 115.116 CF Interim Suspended BUG 4064635
264 * 18-Jan-2005 ikasire 115.117 CF Interim Suspended BUG 4064635- unsuspend epe
265 * for electable is 'N'
266 * 02-Feb-2005 ikasire 115.118 CF Interim Suspended BUG 4064635
267 * 10-Feb-2005 kmahendr 115.119 Bug#4172569 - suspend flag is checked
268 * 16-Feb-2005 kmahendr 115.120 Bug#4186343 - cursors in determine_interim modified to
269 * look for optional certification
270 * 07-Mar-2005 ikasire 115.121 Bug#4223840 Second part of Interim code is not
271 * evaluated right in determining the interim code
272 * when you save the enrollments multiple times.
273 * 18-Mar-2005 ikasire 115.122 Bug 4247213 Performance changes
274 * 24-Mar-2005 abparekh 115.123 Bug 4256836 : While determining interim coverage
275 * 115.124 select electable choice that falls under the program
276 * for which enrollment is suspended. This it to avoid
277 * suspended and interim falling into different programs
278 * 05-Apr-2005 abparekh 115.125 Bug 4141269 pass p_input_value_id and p_element_type_id
279 * as null to ben_element_entry.create_enrollment_element
280 * 14-Apr-2005 ikasire 115.126 Added new parameter to manage_enrt_bnft call
281 * 20-Jun-2005 mmudigon 115.127 Bug 4352871. Added logic to delete element
282 * entries when pen is suspended.
283 * 29-Jun-2005 ikasire 115.128 Bug 4422667 getting into loop issue
284 * 17-Aug-2005 ikasire 115.129 Bug 4547332 fix changes to c_current_enrt
285 * 19-Aug-2005 ikasire 115.130 Bug 4563223 to filter program in the cursor
286 * 26-Aug-2005 ikasire 115.131 Bug 4558512 for completion date
287 * search string p_cmpltd_dt
288 * 01-Sep-2005 ikasire 115.132 Bug 4577581 we need to pass p_per_in_ler_id to
289 * multi row edit call from susps and unsusp
290 * 13-Sep-2005 ikasire 115.133 Bug 4463267 fix several interim cursors
291 * 15 Sep 2005 ikasire 115.134 Bug 4450214 Added cfw condition bases on
292 * g_cfw_flag and modified the cfw cursor to
293 * to function as per the changed process in
294 * election_information.
295 * 22 Sep 2005 ikasire 115.135 Bug 4622534 for carrforward dependents from
296 * default rule
297 * 27 Sep 2005 mmudigon 115.136 Bug 4622534 continued. Added join on
298 * person_id in cursor c_cf_suspended
299 * 05 Dec 2005 bmanyam 115.137 4775743: If Dpnt Cvg starts after the PEN Cvg Strt,
300 * then use Dpnt Cvg Strt, as the Start date
301 * for restoring results.
302 * 07 Mar 2006 ikasired 115.140 Interim - Default to Assign Pending Action
303 * Rule Enhacenments.
304 * 07 Mar 2006 ikasired 115.141 Interim - Rule more changes
305 * 12 Apr 2006 ikasired 115.142 fix for regression from 115.120 version
306 * Flat Range see Bug 5158595
307 * 12 Apr 2006 ikasired 115.143 fix for flat rante bug 5158471
308 * 19 Apr 2006 gsehgal 115.144 bug:5148514. change the message when interim amount is
309 * equal to benefit amount.
310 * 27 Apr 2006 nhunur 115.145 bug:5135117. interim amount should be less than
311 * benefit amount.
312 * 16 May 2006 swjain 115.146 Bug 5225780 - Updated procedure validate_interim_rule
313 * to pick valid epe records
314 * 17 May 2006 swjain 115.147 Bug 5225780 - Updated the message number
315 * 18-May-2006 abparekh 115.149 Bug 5231894 - While un-suspending, update rate start date
316 * only if its earlier than date of un-suspension
317 * 11-Jul-2006 ssarkar 115.150 Bug 5381200 - modified c_bnft,c_dflt_bnft of proc determine_interim
318 * 26-Jul-2006 rtagarra 115.151 Bug 5402317 - modified cursor c1 to check as per lf_evt_ocrd_dt.
319 * 01-Aug-2006 abparekh 115.152 Bug 5415757 - Commented cursor c_rt clause that prevented carry
320 * forward of rates for interim PEN.
321 * 30-Aug-2006 rtagarra 115.153 Bug 5491212 - Changed the cursor Csr_prtt_enrt_rslt.
322 * 12-Oct-2006 ikasired 115.154 Bug 5596918 fix for 'SAME' part in carryforward. bnft amt null issue
323 * 12-Oct-2006 ikasired 115.154 Bug 5596907 fix for carryforward to use right bnft record
324 * 10-nov-2006 ssarkar 115.155 Bug 5653168 - resetting g_interim_flag for any exception in process_interim
325 * 18-May-2007 swjain 115.156 Bug 6054988 - In procedure unsuspend_enrollment, call multi_rows_edit only
326 if any elections made in the current pil
327 * 22-jun-2007 nhunur 115.157 perf changes
328 * 24-Aug-2007 gsehgal 115.158 bug 6337803 added global variable g_sspnded_rslt_id to store the pen id of
329 enrollment going to suspend at the time of processing the interim
330 * 12-Nov-2007 sshetty 115.156.11516.2 Bug 6597329 Added per_in_ler id
331 * check for c_prv_sspnd to fix
332 * the purge issue
333 * 22-Feb-2008 rtagarra 115.157 Bug 6840074
334 * 23-Jun-2008 sallumwa 115.161 Bug 7195598 - Fixed cursor c_cur_bnft to fetch benefit records
335 * even if order number is 1,which inturn is used to
336 * calculate interim rates.
337 * 18-sep-2008 sallumwa 115.162 Bug 7262435 : Fixed cursor c_cur_bnft to fetch correct benefit
338 * records which inturn is used to
339 * calculate interim rates for EVAT and falt range cases.
340 =========================================================================================*/
341 g_package varchar2(80) := 'ben_sspndd_enrollment';
342 --
343 -- ==========================================================================
344 -- << Rpt_error >>
345 -- ==========================================================================
346 --
347 procedure rpt_error(p_proc varchar2
348 ,p_last_action varchar2
349 ) is
350 Begin
351 hr_utility.set_location('>> Fail at ' || p_proc, 999);
352 hr_utility.set_location('>> ' || p_last_action, 999);
353 End rpt_error;
354 --
355 -- ==========================================================================
356 -- << get_cvg_strt_dt >>
357 -- ==========================================================================
358 --
359 Function get_cvg_strt_dt(p_effective_date in Date
360 ,p_prtt_enrt_rslt_id in Number
361 ,p_calc_cvg_strt_dt_cd in varchar2
362 ) return date is
363 l_proc varchar2(80) := g_package || '.get_cvg_strt_dt';
364 Begin
365 hr_utility.set_location('Entering ' || l_proc, 05);
366 hr_utility.set_location('Leaving ' || l_proc, 10);
367 End get_cvg_strt_dt;
368 --
369 -- ==========================================================================
370 -- << Get_cvg_end_dt >>
371 -- ==========================================================================
372 --
373 Function get_cvg_end_dt (p_effective_date in Date
374 ,p_prtt_enrt_rslt_id in Number
375 ,p_calc_cvg_end_dt_cd in varchar2
376 ) return date is
377 l_proc varchar2(80) := g_package || '.get_cvg_end_dt';
378 Begin
379 hr_utility.set_location('Entering ' || l_proc, 05);
380 hr_utility.set_location('Leaving ' || l_proc, 10);
381 End get_cvg_end_dt;
382 --
383 -- ----------------------------------------------------------------------------
384 -- |---------------------< Get_DFLT_TO_ASN_PNDG_CTFN_CD >---------------------|
385 -- ----------------------------------------------------------------------------
386 /******* added organization_id ,pgm_id,pl_id,pl_typ_id,opt_id,ler_id --shdas ***********/
387
388 Function get_dflt_to_asn_pndg_ctfn_cd
389 (p_dflt_to_asn_pndg_ctfn_rl in number
390 ,p_person_id in number
391 ,p_per_in_ler_id in number
392 ,p_assignment_id in number
393 ,p_organization_id in number
394 ,p_business_group_id in number
395 ,p_pgm_id in number
396 ,p_pl_id in number
397 ,p_pl_typ_id in number
398 ,p_opt_id in number
399 ,p_ler_id in number
400 ,p_elig_per_elctbl_chc_id in number
401 ,p_jurisdiction_code in varchar2
402 ,p_effective_date in date
403 ,p_prtt_enrt_rslt_id in number
404 ,p_interim_epe_id out nocopy number
405 -- ,p_interim_enb_id out nocopy number
406 ,p_interim_bnft_amt out nocopy number
407 ) return varchar2 is
408 --
409 -- ** Declaration Section
410 l_proc varchar2(80) := g_package||'.get_dflt_to_asn';
411 l_outputs ff_exec.outputs_t;
412 l_return varchar2(30);
413 l_step integer;
414 l_interim_epe_id number;
415 l_interim_enb_id number;
416 l_interim_bnft_amt number;
417 --
418 begin
419 hr_utility.set_location ('Entering '||l_proc,10);
420 --
421 -- Call formula initialise routine
422 --
423 l_step := 20;
424
425 /******* added organization_id,business_group_id ,pgm_id,pl_id,pl_typ_id,opt_id,ler_id --shdas ***********/
426 hr_utility.set_location ('Organization_id '||to_char(p_organization_id),10);
427 hr_utility.set_location ('assignment_id '||to_char(p_assignment_id),15);
428 hr_utility.set_location ('Business_group_id '||to_char(p_business_group_id),20);
429 hr_utility.set_location ('pgm_id '||to_char(p_pgm_id),30);
430 hr_utility.set_location ('pl_id '||to_char(p_pl_id),40);
431 hr_utility.set_location ('pl_typ_id '||to_char(p_pl_typ_id),50);
432 hr_utility.set_location ('opt_id '||to_char(p_opt_id),60);
433 hr_utility.set_location ('ler_id '||to_char(p_ler_id),70);
434 hr_utility.set_location ('prtt_enrt_rslt_id '||to_char(p_prtt_enrt_rslt_id),70);
435
436 l_outputs := benutils.formula
437 (p_formula_id => p_dflt_to_asn_pndg_ctfn_rl
438 ,p_effective_date => p_effective_date
439 ,p_assignment_id => p_assignment_id
440 ,p_organization_id => p_organization_id
441 ,p_business_group_id => p_business_group_id
442 ,p_pgm_id => p_pgm_id
443 ,p_pl_id => p_pl_id
444 ,p_pl_typ_id => p_pl_typ_id
445 ,p_opt_id => p_opt_id
446 ,p_ler_id => p_ler_id
447 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
448 ,p_jurisdiction_code => p_jurisdiction_code
449 ,p_param1 => 'BEN_PEN_IV_PRTT_ENRT_RSLT_ID'
450 ,p_param1_value => to_char(p_prtt_enrt_rslt_id)
451 ,p_param2 => 'BEN_PER_IV_PERSON_ID'
452 ,p_param2_value => to_char(p_person_id)
453 ,p_param3 => 'BEN_PIL_IV_PER_IN_LER_ID'
454 ,p_param3_value => to_char(p_per_in_ler_id)
455 );
456 --
457 --
458 l_return := l_outputs(l_outputs.last).value;
459 --Start Interim Rule Enhancement
460 if l_return is NULL then
461 --Invalid Rule
462 hr_utility.set_location('BEN_94600_NULL_RETURNED',80);
463 fnd_message.set_name('BEN','BEN_94600_NULL_RETURNED');
464 fnd_message.set_token('PERSON_ID' , to_char(p_person_id));
465 fnd_message.set_token('EPE_ID',to_char(p_elig_per_elctbl_chc_id));
466 fnd_message.raise_error;
467 --
468 elsif instr('0123456789', substr(l_return,1,1) ) > 0 THEN
469 --
470 l_return := NULL;
471 --
472 for l_count in l_outputs.first..l_outputs.last loop
473 --
474 begin
475 --
476 if l_count = l_outputs.last then
477 --
478 l_interim_epe_id := l_outputs(l_count).value;
479 --
480 elsif l_count = l_outputs.last - 1 then
481 --
482 l_interim_bnft_amt := l_outputs(l_count).value;
483 --
484 end if;
485 end;
486 --
487 end loop;
488 end if;
489 --
490 p_interim_epe_id := l_interim_epe_id ;
491 -- p_interim_enb_id := l_interim_enb_id ;
492 p_interim_bnft_amt := l_interim_bnft_amt;
493 -- End Interim Rule enhancement
494 hr_utility.set_location('p_interim_epe_id '||p_interim_epe_id,111);
495 -- hr_utility.set_location('p_interim_enb_id '||p_interim_enb_id,111);
496 hr_utility.set_location('p_interim_bnft_amt '||p_interim_bnft_amt,111);
497 hr_utility.set_location('l_return '||l_return,111);
498 hr_utility.set_location ('Leaving '||l_proc,50);
499 return l_return;
500 Exception
501 When others then
502 hr_utility.set_location ('Fail in '||l_proc|| ' step in '||
503 to_char(l_step),999);
504 fnd_message.raise_error;
505 End get_dflt_to_asn_pndg_ctfn_cd;
506 --
507 --
508 -- ==========================================================================
509 -- << validate_interim_rule >>
510 -- ==========================================================================
511 --
512 procedure validate_interim_rule (
513 p_prtt_enrt_rslt_id in number,
514 p_elig_per_elctbl_chc_id in number,
515 p_enrt_bnft_id in number,
516 p_business_group_id in number,
517 p_person_id in number,
518 p_ler_id in number,
519 p_per_in_ler_id in number,
520 p_pl_id in number,
521 p_pgm_id in number,
522 p_pl_typ_id in number,
523 p_oipl_id in number,
524 p_pl_ordr_num in number,
525 p_oipl_ordr_num in number,
526 p_plip_ordr_num in number,
527 p_bnft_ordr_num in number,
528 p_interim_elctbl_chc_id in number,
529 p_interim_enrt_bnft_id out nocopy number,
530 p_interim_bnft_amt in number
531 )
532 AS
533 --
534 l_proc varchar2(80) := g_package || '.validate_interim_rule';
535 --
536 l_interim_enrt_bnft_id NUMBER := p_interim_enrt_bnft_id;
537 l_interim_bnft_amt NUMBER := p_interim_bnft_amt;
538 --
539 cursor c_epe(v_elig_per_elctbl_chc_id number) is
540 select epe.*
541 from ben_elig_per_elctbl_chc epe, ben_per_in_ler pil
542 where epe.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
543 and epe.per_in_ler_id = pil.per_in_ler_id(+) /* Bug 5225780 */
544 and (pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') or
545 pil.per_in_ler_stat_cd is null);
546 --
547 cursor c_epe_from_enb(v_enrt_bnft_id number) is
548 select enb.*
549 from ben_enrt_bnft enb
550 where enb.enrt_bnft_id = v_enrt_bnft_id ;
551 --
552 l_epe_from_enb c_epe_from_enb%ROWTYPE;
553 --
554 cursor c_enb_rng(v_epe_id number,
555 -- v_enrt_bnft_id number,
556 v_bnft_amt number) is
557 select enb.*
558 from ben_enrt_bnft enb
559 where -- enb.enrt_bnft_id = v_enrt_bnft_id
560 enb.val = p_interim_bnft_amt
561 and enb.elig_per_elctbl_chc_id = v_epe_id ;
562 --
563 l_dummy varchar2(30);
564 --
565 cursor c_enb(v_elig_per_elctbl_chc_id number) is
566 select enb.*
567 from ben_enrt_bnft enb
568 where enb.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
569 and enb.MX_WO_CTFN_FLAG = 'N' ;
570 --
571 cursor c_enb_entr(v_elig_per_elctbl_chc_id number) is
572 select enb.*
573 from ben_enrt_bnft enb
574 where enb.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
575 and enb.MX_WO_CTFN_FLAG = 'Y' ;
576 --
577 /*
578 --
579 cursor c_ecc(v_elig_per_elctbl_chc_id number) is
580 select ecc.rqd_flag,
581 ecc.enrt_ctfn_typ_cd,
582 ecc.SUSP_IF_CTFN_NOT_PRVD_FLAG,
583 ecc.ctfn_determine_cd
584 from ben_elctbl_chc_ctfn ecc
585 where ecc.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
586 and ecc.enrt_bnft_id is null
587 and ecc.business_group_id = p_business_group_id ;
588 */
589 --
590 cursor c_enbcount(v_elig_per_elctbl_chc_id number) is
591 select count(*)
592 from ben_enrt_bnft
593 where elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
594 and ordr_num >= 0 ; --This will exclude the records being create for SAME case
595 --which are created with -1
596 --
597 cursor c_pen(v_prtt_enrt_rslt_id number,
598 v_per_in_ler_id number ) is
599 select pen.*
600 from ben_prtt_enrt_rslt_f pen
601 where pen.prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
602 and pen.per_in_ler_id = v_per_in_ler_id
603 and pen.prtt_enrt_rslt_stat_cd IS NULL
604 and pen.effective_end_date = hr_api.g_eot ;
605 --
606 l_pen c_pen%ROWTYPE;
607 l_susp_epe c_epe%ROWTYPE;
608 l_interim_epe c_epe%ROWTYPE;
609 l_interim_enb c_enb%ROWTYPE;
610 l_enb c_enb%ROWTYPE;
611 l_interim_enb_entr c_enb%ROWTYPE;
612 l_enb_count number ;
613 --
614 BEGIN
615 --
616 hr_utility.set_location('Entering ' || l_proc, 5);
617 --
618 /* hr_utility.set_location('p_prtt_enrt_rslt_id ----*** ' || p_prtt_enrt_rslt_id, 5);
619 hr_utility.set_location('p_elig_per_elctbl_chc_id ' || p_elig_per_elctbl_chc_id, 5);
620 hr_utility.set_location('p_enrt_bnft_id ' || p_enrt_bnft_id, 5);
621 hr_utility.set_location('p_interim_elctbl_chc_id ' || p_interim_elctbl_chc_id, 5);
622 hr_utility.set_location('p_oipl_id ' || p_oipl_id, 5);
623 hr_utility.set_location('p_pl_id ' || p_pl_id, 5);
624 hr_utility.set_location('p_interim_bnft_amt ---*** ' || p_interim_bnft_amt, 5);
625 */
626 --
627 open c_epe(p_elig_per_elctbl_chc_id);
628 fetch c_epe into l_susp_epe ;
629 close c_epe;
630 --
631 open c_epe(p_interim_elctbl_chc_id);
632 fetch c_epe into l_interim_epe ;
633 /* Bug 5225780 */
634 if c_epe%NOTFOUND or l_interim_epe.per_in_ler_id <> l_susp_epe.per_in_ler_id then
635 hr_utility.set_location('BEN_94628', 80);
636 fnd_message.set_name('BEN','BEN_94628_EPE_NOTIN_PIL');
637 fnd_message.set_token('PROC',l_proc);
638 fnd_message.set_token('PERSON_ID' , to_char(p_person_id));
639 fnd_message.set_token('INT_EPE_ID',to_char(p_interim_elctbl_chc_id));
640 fnd_message.set_token('SSP_EPE_ID',to_char(p_elig_per_elctbl_chc_id));
641 fnd_message.raise_error;
642 end if;
643 /* End Bug 5225780 */
644 close c_epe;
645 --
646 --a. Validate EPE to be in the same plan type of suspending enrollment.
647 --
648 IF l_interim_epe.ptip_id <> l_susp_epe.ptip_id THEN
649 --
650 hr_utility.set_location('BEN_94601', 80);
651 fnd_message.set_name('BEN','BEN_94601_EPE_NOTIN_PTIP');
652 fnd_message.set_token('PROC',l_proc);
653 fnd_message.set_token('PERSON_ID' , to_char(p_person_id));
654 fnd_message.set_token('INT_EPE_ID',to_char(p_interim_elctbl_chc_id));
655 fnd_message.set_token('SSP_EPE_ID',to_char(p_elig_per_elctbl_chc_id));
656 fnd_message.raise_error;
657 --
658 END IF;
659 --
660 open c_enbcount(p_interim_elctbl_chc_id) ;
661 fetch c_enbcount into l_enb_count;
662 close c_enbcount;
663 --
664 IF l_enb_count = 0 AND p_interim_bnft_amt IS NULL THEN
665 --
666 IF p_elig_per_elctbl_chc_id = p_interim_elctbl_chc_id THEN
667 --
668 hr_utility.set_location('BEN_94602_SM_AS_SSPND', 80);
669 fnd_message.set_name('BEN','BEN_94602_SM_AS_SSPND');
670 fnd_message.set_token('PROC',l_proc);
671 fnd_message.set_token('PERSON_ID' , to_char(p_person_id));
672 fnd_message.set_token('INT_EPE_ID',to_char(p_interim_elctbl_chc_id));
673 fnd_message.set_token('SSP_EPE_ID',to_char(p_elig_per_elctbl_chc_id));
674 fnd_message.raise_error;
675 --
676 END IF;
677 --
678 return;
679 ELSIF l_enb_count = 0 AND p_interim_bnft_amt IS NOT NULL THEN
680 --
681 hr_utility.set_location('BEN_94603_WHY_ENB_ID', 80);
682 fnd_message.set_name('BEN','BEN_94603_WHY_ENB_ID');
683 fnd_message.set_token('PROC',l_proc);
684 fnd_message.set_token('PERSON_ID' , to_char(p_person_id));
685 fnd_message.set_token('INT_EPE_ID',to_char(p_interim_elctbl_chc_id));
686 fnd_message.set_token('SSP_EPE_ID',to_char(p_elig_per_elctbl_chc_id));
687 fnd_message.raise_error;
688 --
689 ELSIF l_enb_count = 1 THEN
690 --
691 open c_enb(p_interim_elctbl_chc_id);
692 fetch c_enb into l_interim_enb ;
693 close c_enb;
694 --
695 hr_utility.set_location('l_interim_enb.enrt_bnft_id ' || l_interim_enb.enrt_bnft_id, 5);
696 --
697 IF p_elig_per_elctbl_chc_id <> p_interim_elctbl_chc_id AND
698 p_enrt_bnft_id <> l_interim_enb.enrt_bnft_id
699 THEN
700 --Make sure the interim amount is less than or equal to the suspended coverage amount
701 open c_enb(p_interim_elctbl_chc_id);
702 fetch c_enb into l_interim_enb ;
703 close c_enb;
704 --
705 open c_enb(p_elig_per_elctbl_chc_id);
706 fetch c_enb into l_enb ;
707 close c_enb;
708 --
709 IF nvl(l_interim_enb.val,-1) > nvl(l_enb.val,-1)
710 THEN
711 hr_utility.set_location('BEN_94607_MORE_THAN_SSPND', 80);
712 fnd_message.set_name('BEN','BEN_94607_MORE_THAN_SSPND');
713 fnd_message.set_token('PROC',l_proc);
714 fnd_message.set_token('PERSON_ID' , to_char(p_person_id));
715 fnd_message.set_token('INT_EPE_ID',to_char(p_interim_elctbl_chc_id));
716 fnd_message.set_token('SSP_EPE_ID',to_char(p_elig_per_elctbl_chc_id));
717 fnd_message.raise_error;
718 END IF;
719 --
720 END IF;
721 --
722 --Make sure Suspended and interim enb are same then throw the error.
723 IF p_elig_per_elctbl_chc_id = p_interim_elctbl_chc_id AND
724 p_enrt_bnft_id = l_interim_enb.enrt_bnft_id THEN
725 --
726 hr_utility.set_location('BEN_94602_SM_AS_SSPND', 80);
727 fnd_message.set_name('BEN','BEN_94602_SM_AS_SSPND');
728 fnd_message.set_token('PROC',l_proc);
729 fnd_message.set_token('PERSON_ID' , to_char(p_person_id));
730 fnd_message.set_token('INT_EPE_ID',to_char(p_interim_elctbl_chc_id));
731 fnd_message.set_token('SSP_EPE_ID',to_char(p_elig_per_elctbl_chc_id));
732 fnd_message.raise_error;
733 --
734 END IF;
735 --
736 p_interim_enrt_bnft_id := l_interim_enb.enrt_bnft_id;
737 -- p_interim_bnft_amt := l_interim_enb.val;
738 return;
739 --
740 ELSIF l_enb_count > 1 AND p_interim_bnft_amt IS NULL THEN
741 --
742 hr_utility.set_location('BEN_94604_NO_ENB_ID', 80);
743 fnd_message.set_name('BEN','BEN_94604_NO_ENB_ID');
744 fnd_message.set_token('PROC',l_proc);
745 fnd_message.set_token('PERSON_ID' , to_char(p_person_id));
746 fnd_message.set_token('INT_EPE_ID',to_char(p_interim_elctbl_chc_id));
747 fnd_message.set_token('SSP_EPE_ID',to_char(p_elig_per_elctbl_chc_id));
748 fnd_message.raise_error;
749 --
750 ELSIF l_enb_count > 1 THEN
751 --
752 --Make sure the interim amount is less than or equal to the suspended coverage amount
753 open c_pen(p_prtt_enrt_rslt_id,p_per_in_ler_id ) ;
754 fetch c_pen into l_pen;
755 close c_pen;
756 --
757 IF p_interim_bnft_amt > l_pen.bnft_amt THEN
758 --
759 hr_utility.set_location('BEN_94607_MORE_THAN_SSPND', 80);
760 fnd_message.set_name('BEN','BEN_94607_MORE_THAN_SSPND');
761 fnd_message.set_token('PROC',l_proc);
762 fnd_message.set_token('PERSON_ID' , to_char(p_person_id));
763 fnd_message.set_token('INT_EPE_ID',to_char(p_interim_elctbl_chc_id));
764 fnd_message.set_token('SSP_EPE_ID',to_char(p_elig_per_elctbl_chc_id));
765 fnd_message.raise_error;
766 --
767 END IF;
768 --
769 --Make sure enb_id returned by the rule belongs to the same epe
770 open c_enb(p_interim_elctbl_chc_id);
771 fetch c_enb into l_interim_enb ;
772 close c_enb;
773 --
774 --
775 IF l_interim_enb.cvg_mlt_cd like '%RNG%' THEN
776 --
777 open c_enb_rng(p_interim_elctbl_chc_id,p_interim_bnft_amt) ;
778 fetch c_enb_rng into l_interim_enb;
779 IF c_enb_rng%NOTFOUND THEN
780 --
781 close c_enb_rng;
782 hr_utility.set_location('BEN_94606_INVALID_ENB_RNG', 80);
783 fnd_message.set_name('BEN','BEN_94606_INVALID_ENB_RNG');
784 fnd_message.set_token('PROC',l_proc);
785 fnd_message.set_token('PERSON_ID' , to_char(p_person_id));
786 fnd_message.set_token('INT_EPE_ID',to_char(p_interim_elctbl_chc_id));
787 fnd_message.set_token('SSP_EPE_ID',to_char(p_elig_per_elctbl_chc_id));
788 fnd_message.raise_error;
789 --
790 END IF;
791 close c_enb_rng;
792 --
793 IF l_interim_enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id AND
794 l_interim_enb.enrt_bnft_id = p_enrt_bnft_id THEN
795 --
796 hr_utility.set_location('BEN_99602_SM_AS_SSPND', 80);
797 fnd_message.set_name('BEN','BEN_99602_SM_AS_SSPND');
798 fnd_message.set_token('PROC',l_proc);
799 fnd_message.set_token('PERSON_ID' , to_char(p_person_id));
800 fnd_message.set_token('INT_EPE_ID',to_char(p_interim_elctbl_chc_id));
801 fnd_message.set_token('SSP_EPE_ID',to_char(p_elig_per_elctbl_chc_id));
802 fnd_message.raise_error;
803 --
804 END IF;
805 --
806 --
807 l_interim_enrt_bnft_id := l_interim_enb.enrt_bnft_id ;
808 --
809 ELSE
810 -- Here we need to get only flat fixed enter value at enrollment case.
811 open c_enb_entr(p_interim_elctbl_chc_id);
812 fetch c_enb_entr into l_interim_enb_entr ;
813 close c_enb_entr;
814 --
815 l_interim_enrt_bnft_id := l_interim_enb_entr.enrt_bnft_id ;
816 --
817 IF p_interim_bnft_amt = l_pen.bnft_amt THEN
818 --
819 -- hr_utility.set_location('BEN_99602_SM_AS_SSPND', 80);
820 -- fnd_message.set_name('BEN','BEN_99602_SM_AS_SSPND');
821 -- message changed for Bug 5148514
822 hr_utility.set_location('BEN_94624_AMT_SM_SSPND', 80);
823 fnd_message.set_name('BEN','BEN_94624_AMT_SM_SSPND');
824 fnd_message.set_token('PROC',l_proc);
825 fnd_message.set_token('PERSON_ID' , to_char(p_person_id));
826 fnd_message.set_token('INT_EPE_ID',to_char(p_interim_elctbl_chc_id));
827 fnd_message.set_token('SSP_EPE_ID',to_char(p_elig_per_elctbl_chc_id));
828 fnd_message.raise_error;
829 --
830 END IF;
831 --
832 END IF;
833 --
834 --
835 END IF;
836 --
837 p_interim_enrt_bnft_id := l_interim_enrt_bnft_id;
838 -- p_interim_bnft_amt := l_interim_bnft_amt;
839 --
840 hr_utility.set_location('Leaving ' || l_proc, 10);
841 --
842 EXCEPTION
843 --
844 WHEN OTHERS THEN
845 --
846 hr_utility.set_location('EXC : ' || substr(SQLERRM, 1, 50), 9999);
847 raise;
848 --
849 --
850 END validate_interim_rule;
851 --
852 --
853 -- ==========================================================================
854 -- << Determine_interim >>
855 -- ==========================================================================
856 --
857 Procedure Determine_interim
858 (p_elig_per_elctbl_chc_id in number
859 ,p_prtt_enrt_rslt_id in number
860 ,p_enrt_bnft_id in number /*ENH*/
861 ,p_interim_elctbl_chc_id in out nocopy number
862 ,p_interim_enrt_bnft_id out nocopy number
863 ,p_interim_enrt_rslt_id out nocopy number
864 ,p_person_id in number
865 ,p_ler_id in number
866 ,p_per_in_ler_id in number
867 ,p_pl_id in number
868 ,p_pgm_id in number /* Bug 4256836 */
869 ,p_pl_typ_id in number
870 ,p_oipl_id in number
871 ,p_pl_ordr_num in number
872 ,p_oipl_ordr_num in number
873 ,p_plip_ordr_num in number /*ENH*/
874 ,p_bnft_ordr_num in number /*ENH*/
875 ,p_business_group_id in number
876 ,p_effective_date in date
877 ,p_interim_bnft_amt out nocopy number
878 ,p_bnft_or_option_rstrctn_cd out nocopy varchar2 -- Bug 1886183
879 ) is
880 l_proc varchar2(80) := g_package ||
881 '.Determine_interim';
882 l_last_place varchar2(132);
883 l_interim_enrt_bnft_id number;
884 l_DFLT_TO_ASN_PNDG_CTFN_CD ben_pl_f.DFLT_TO_ASN_PNDG_CTFN_CD%type := NULL ;
885 l_DFLT_TO_ASN_PNDG_CTFN_RL ben_pl_f.DFLT_TO_ASN_PNDG_CTFN_RL%type := NULL ;
886 l_bnft_or_option_rstrctn_cd ben_pl_f.BNFT_OR_OPTION_RSTRCTN_CD%type := NULL;
887 l_assignment_id per_all_assignments_f.assignment_id%type;
888
889 /********************* l_organization_id added by shdas **********/
890
891 l_organization_id per_all_assignments_f.organization_id%type;
892 l_interim_action varchar2(30):='NT';
893 l_bnft_ordr_num number;
894 l_enrt_pl_id number := null;
895 l_enrt_chc_id number := null;
896 l_enrt_pl_typ_id number := null;
897 --RCHASE Bug#5353 added l_prtt_enrt_rslt_id for interim assignment to current
898 l_prtt_enrt_rslt_id number := null;
899 l_intm_dfn_level varchar2(30) := null;
900 l_jurisdiction_code varchar2(30);
901 --
902 -- for nocopy changes
903 l_interim_elctbl_chc_id number := p_interim_elctbl_chc_id;
904
905 -- Cursor declaration
906 --
907
908 Cursor c_state is
909 select region_2
910 from hr_locations_all loc,per_all_assignments_f asg
911 where loc.location_id = asg.location_id
912 and asg.person_id = p_person_id
913 and asg.assignment_type <> 'C'
914 and asg.primary_flag = 'Y'
915 and p_effective_date between
916 asg.effective_start_date and asg.effective_end_date
917 and asg.business_group_id=p_business_group_id;
918
919 l_state c_state%rowtype;
920
921 Cursor c_epe is
922 select epe.business_group_id,
923 epe.pgm_id,
924 epe.pl_id,
925 epe.pl_typ_id,
926 epe.oipl_id,
927 pil.ler_id
928 from ben_elig_per_elctbl_chc epe,ben_per_in_ler pil
929 where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
930 and pil.per_in_ler_id = epe.per_in_ler_id;
931
932 l_epe c_epe%rowtype;
933
934 Cursor c1(l_get_lf_evt_ocrd_dt date) is
935 select 1 order_no
936 ,DFLT_TO_ASN_PNDG_CTFN_CD
937 ,DFLT_TO_ASN_PNDG_CTFN_RL
938 ,null BNFT_OR_OPTION_RSTRCTN_CD
939 From ben_ler_bnft_rstrn_f
940 Where pl_id = p_pl_id
941 and ler_id = p_ler_id
942 and l_get_lf_evt_ocrd_dt between --Bug#5402317
943 effective_start_date and effective_end_date
944 and business_group_id=p_business_group_id
945 and DFLT_TO_ASN_PNDG_CTFN_CD is not NULL
946 Union
947 select 2 order_no
948 ,plip.DFLT_TO_ASN_PNDG_CTFN_CD
949 ,plip.DFLT_TO_ASN_PNDG_CTFN_RL
950 ,plip.BNFT_OR_OPTION_RSTRCTN_CD
951 From ben_plip_f plip, ben_prtt_enrt_rslt_f pen
952 Where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
953 and pen.pgm_id = plip.pgm_id
954 and pen.pl_id = plip.pl_id
955 and pen.prtt_enrt_rslt_stat_cd is null
956 and l_get_lf_evt_ocrd_dt between --Bug#5402317
957 pen.effective_start_date and pen.effective_end_date
958 and l_get_lf_evt_ocrd_dt between
959 plip.effective_start_date and plip.effective_end_date
960 and pen.business_group_id=p_business_group_id
961 -- and DFLT_TO_ASN_PNDG_CTFN_CD is not NULL
962 Union
963 select 3 order_no
964 ,DFLT_TO_ASN_PNDG_CTFN_CD
965 ,DFLT_TO_ASN_PNDG_CTFN_RL
966 ,BNFT_OR_OPTION_RSTRCTN_CD
967 From ben_pl_f
968 Where pl_id = p_pl_id
969 and l_get_lf_evt_ocrd_dt between --Bug#5402317
970 effective_start_date and effective_end_date
971 and business_group_id=p_business_group_id
972 -- and DFLT_TO_ASN_PNDG_CTFN_CD is not NULL
973 order by 1
974 ;
975
976 Cursor c_paf is
977 Select assignment_id,organization_id
978 From per_all_assignments_f
979 Where person_id = p_person_id
980 and assignment_type <> 'C'
981 And business_group_id=p_business_group_id
982 and p_effective_date between
983 effective_start_date and effective_end_date
984 and primary_flag = 'Y'
985 ;
986
987 Cursor c_opt
988 is select oipl.opt_id
989 from ben_oipl_f oipl
990 where oipl.oipl_id = l_epe.oipl_id
991 and p_effective_date between
992 oipl.effective_start_date and oipl.effective_end_date
993 and oipl.business_group_id=p_business_group_id;
994
995 l_opt c_opt%rowtype;
996 /* BUG 3095291 FOLLOWING MESS IS CLEANED UP AND WRITEN A NEW CURSOR
997 -- this cursor finds the current enrollment and unions to results (because
998 -- we need to know the info even if a choice does not exist.
999 -- RCHASE Bug#5353 added prtt_enrt_rslt_id to cursor. This will be used to set the interim
1000 -- jcarpent bug 5353 (again) added bnft_id/amt to query
1001 --
1002 --ikasire Bug 1886183. This cursor doesnot work if there is a change in the
1003 --benefit amount in the subsequent life envent due to epe.per_in_ler_id=pil.per_in_ler_id
1004 --Also the select after the union never returns any rows because of
1005 -- per.per_in_ler_id <> pinl.per_in_ler_id condition in it.
1006 --
1007 cursor c_current_enrt(v_pl_id number, v_oipl_id number ) is
1008 select per.pl_id,
1009 per.pl_typ_id,
1010 epe.elig_per_elctbl_chc_id,
1011 -- RCHASE Bug#5353 added
1012 per.prtt_enrt_rslt_id
1013 from ben_prtt_enrt_rslt_f per,
1014 ben_elig_per_elctbl_chc epe,
1015 ben_per_in_ler pil
1016 where pil.business_group_id=p_business_group_id and
1017 pil.person_id=p_person_id and
1018 pil.per_in_ler_stat_cd='STRTD' and
1019 per.person_id=p_person_id and
1020 per.business_group_id=p_business_group_id and
1021 per.sspndd_flag='N' and
1022 per.prtt_enrt_rslt_stat_cd is null and
1023 p_effective_date-1 between
1024 per.effective_start_date and per.effective_end_date and
1025 -- RCHASE Bug#5181 check against life event occrd date -1
1026 -- instead of effective date
1027 -- p_effective_date <= per.enrt_cvg_thru_dt and
1028 pil.lf_evt_ocrd_dt-1 <= per.enrt_cvg_thru_dt and
1029 -- per.enrt_cvg_strt_dt < p_effective_date and
1030 per.enrt_cvg_strt_dt <= pil.lf_evt_ocrd_dt-1 and
1031 -- RCHASE Bug#5181 don't check my per_in_ler
1032 per.per_in_ler_id <> pil.per_in_ler_id and
1033 per.pl_typ_id=p_pl_typ_id and
1034 per.pl_id = nvl(v_pl_id, per.pl_id) and
1035 nvl(per.oipl_id,-1) = nvl(v_oipl_id,nvl(per.oipl_id,-1)) and
1036 epe.prtt_enrt_rslt_id=per.prtt_enrt_rslt_id and
1037 epe.business_group_id=per.business_group_id
1038 -- epe.per_in_ler_id=pil.per_in_ler_id Bug 1886183 This doesnot work for benefit amt changes
1039 union
1040 select per.pl_id,
1041 per.pl_typ_id,
1042 to_number(null) elig_per_elctbl_chc_id,
1043 -- RCHASE Bug#5353
1044 per.prtt_enrt_rslt_id
1045 from ben_prtt_enrt_rslt_f per,
1046 ben_per_in_ler pinl
1047 where per.per_in_ler_id=pinl.per_in_ler_id and -- Bug 2595113
1048 per.person_id=p_person_id and
1049 per.business_group_id=p_business_group_id and
1050 per.sspndd_flag='N' and
1051 per.prtt_enrt_rslt_stat_cd is null and
1052 p_effective_date-1 between
1053 per.effective_start_date and per.effective_end_date and
1054 -- RCHASE Bug#5181 check against life event occrd date -1
1055 -- instead of effective date
1056 -- p_effective_date <= per.enrt_cvg_thru_dt and
1057 -- per.enrt_cvg_strt_dt < p_effective_date and
1058 pinl.lf_evt_ocrd_dt-1 <= per.enrt_cvg_thru_dt and
1059 per.enrt_cvg_strt_dt <= pinl.lf_evt_ocrd_dt-1 and
1060 -- RCHASE Bug#5181 don't check my per_in_ler
1061 -- per.per_in_ler_id <> pinl.per_in_ler_id and -- Bug 2595113
1062 per.pl_typ_id=p_pl_typ_id and
1063 per.pl_id = nvl(v_pl_id, per.pl_id) and
1064 nvl(per.oipl_id,-1) = nvl(v_oipl_id,nvl(per.oipl_id,-1)) and
1065 not exists (
1066 select null
1067 from ben_elig_per_elctbl_chc epe,
1068 ben_per_in_ler pil
1069 where pil.business_group_id=p_business_group_id and
1070 pil.person_id=p_person_id and
1071 pil.per_in_ler_stat_cd='STRTD' and
1072 epe.prtt_enrt_rslt_id=per.prtt_enrt_rslt_id and
1073 epe.business_group_id=per.business_group_id and
1074 epe.per_in_ler_id=pil.per_in_ler_id)
1075 ;
1076 */
1077 --
1078 --BUG 3095291 Cleaned up the above cursor and rewriten.
1079 --The following cursor is used to determine the current enrollments.
1080 --Outer joined to epe to get the results of if there were no epe
1081 --records for the current enrollment.Removed the union and the
1082 --second select. Also, using pil.lf_evt_ocrd_dt instead of using
1083 --pil.lf_evt_ocrd_dt - 1 in the where cause to avoid the issue
1084 --happening in the bug. We are now using the logic similar to the
1085 --bendenrr to get the current enrollment.
1086 --
1087 --
1088 /*
1089 Here is the status of the records when this procedure is being called
1090
1091 LE1 LE2
1092
1093 |------------------------------------|---------------------------------------------
1094
1095 Case 1: Continuing in the same enrollment
1096 OldOipl OldOipl
1097 OldPIL NewPIL
1098 OldPEN OldPEN
1099 CTD EOT
1100 EED EOT
1101
1102 Case 2: Continuing in the same enrollment save and then change with new option again
1103
1104 OldOipl OldOipl
1105 OldPIL NewPIL
1106 OldPEN OldPEN
1107 CTD EOT
1108 EED EOT
1109 [the data will change once the delete enrollment is called]
1110 Ended OldOipl
1111 NewPIL
1112 OldPEN
1113 CTD FILLED
1114 EED EOT
1115
1116 New NewOipl
1117 NewPIL
1118 NewPEN
1119 CTD EOT
1120 EED EOT
1121 Case 3: Replace the enrollment with a new Plan Option
1122
1123 OldOipl OldOipl
1124 OldPIL OldPIL [Important]
1125 OldPEN OldPEN
1126 CTD EOT
1127 EED EOT
1128 [the data will change once the delete enrollment is called]
1129 Ended OldOipl
1130 NewPIL
1131 OldPEN
1132 CTD FILLED
1133 EED EOT
1134
1135 New NewOipl
1136 NewPIL
1137 NewPEN
1138 CTD EOT
1139 EED EOT
1140
1141 Case 4: delete the current enrollment and enroll in a new one later
1142
1143 OldOipl OldOipl
1144 OldPIL NewPIL
1145 OldPEN OldPEN
1146 CTD FILLED
1147 EED EOT
1148 [the data will change once the delete enrollment is called]
1149 Ended OldOipl
1150 NewPIL
1151 OldPEN
1152 CTD FILLED
1153 EED EOT
1154
1155 New NewOipl
1156 NewPIL
1157 NewPEN
1158 CTD EOT
1159 EED EOT
1160
1161
1162 */
1163 --BUG 4547332 rewriten sql
1164 --Check the above cases before changing any logic
1165 --
1166 cursor c_current_enrt(v_pl_id number, v_oipl_id number ) is
1167 select per.pl_id,
1168 per.pl_typ_id,
1169 per.prtt_enrt_rslt_id
1170 from ben_prtt_enrt_rslt_f per,
1171 ben_per_in_ler pil
1172 where pil.business_group_id = p_business_group_id and
1173 pil.person_id = p_person_id and
1174 pil.per_in_ler_id = p_per_in_ler_id and
1175 per.person_id = pil.person_id and
1176 per.business_group_id = p_business_group_id and
1177 per.sspndd_flag = 'N' and
1178 per.prtt_enrt_rslt_stat_cd is null and
1179 per.effective_end_date = hr_api.g_eot and
1180 per.enrt_cvg_strt_dt < per.effective_end_date and
1181 (
1182 ( p_per_in_ler_id = per.per_in_ler_id and
1183 ( /* Case 4 */
1184 per.enrt_cvg_thru_dt <> hr_api.g_eot or
1185 ( /* Case 1,2*/
1186 ( exists (select 'x' from ben_prtt_enrt_rslt_f pen3
1187 where pen3.prtt_enrt_rslt_id = per.prtt_enrt_rslt_id and
1188 pen3.prtt_enrt_rslt_stat_cd is null and
1189 pen3.sspndd_flag = 'N' and
1190 pen3.effective_end_date < per.effective_start_date and
1191 pen3.enrt_cvg_thru_dt = hr_api.g_eot and
1192 pen3.per_in_ler_id <> per.per_in_ler_id
1193 )
1194 ) and
1195 per.enrt_cvg_thru_dt = hr_api.g_eot
1196 )
1197 )
1198 )
1199 or
1200 ( /* Case 3 */
1201 per.enrt_cvg_thru_dt = to_date('31-12-4712','dd-mm-yyyy') and
1202 per.per_in_ler_id <> p_per_in_ler_id
1203 )
1204 ) and
1205 per.pl_typ_id = p_pl_typ_id and
1206 (( per.pgm_id = p_pgm_id) or
1207 (p_pgm_id is null)
1208 ) and -- BUG 4563223
1209 ((per.pl_id = v_pl_id) or
1210 (v_pl_id is null)) and
1211 ((per.oipl_id = v_oipl_id) or
1212 (v_oipl_id is null))
1213 ;
1214 --
1215 cursor c_default_epe is
1216 select epe.elig_per_elctbl_chc_id,
1217 enb.enrt_bnft_id
1218 from ben_per_in_ler pil,
1219 ben_elig_per_elctbl_chc epe,
1220 ben_pl_f pl,
1221 ben_enrt_bnft enb
1222 where
1223 pil.business_group_id=p_business_group_id and
1224 pil.person_id=p_person_id and
1225 --pil.per_in_ler_stat_cd='STRTD' and
1226 pil.per_in_ler_id = p_per_in_ler_id and
1227 epe.per_in_ler_id=pil.per_in_ler_id and
1228 epe.pl_typ_id =p_pl_typ_id and
1229 epe.business_group_id=p_business_group_id and
1230 epe.elctbl_flag='Y' and
1231 epe.dflt_flag='Y' and
1232 nvl(epe.dpnt_dsgn_cd,'O')='O' and
1233 -- epe.ctfn_rqd_flag='N' and
1234 epe.pl_id = pl.pl_id and
1235 ( epe.pgm_id = p_pgm_id or
1236 p_pgm_id is null ) and /* Bug 4256836 */
1237 nvl(pl.bnf_dsgn_cd, 'O') = 'O' and
1238 p_effective_date between
1239 pl.effective_start_date and pl.effective_end_date and
1240 enb.elig_per_elctbl_chc_id(+)=epe.elig_per_elctbl_chc_id and
1241 nvl(enb.dflt_flag,'Y') = 'Y' and
1242 nvl(enb.ctfn_rqd_flag,'N') ='N' and
1243 --bug#4186343
1244 not exists ( select 'Y'
1245 from ben_elctbl_chc_ctfn
1246 where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1247 and nvl(enrt_bnft_id,nvl(enb.enrt_bnft_id,-1)) = nvl(enb.enrt_bnft_id,-1)
1248 and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1249 order by epe.plip_ordr_num,epe.oipl_ordr_num
1250 ;
1251 -- Option Restrictions at plan level.
1252 cursor c_default_pl_epe is
1253 select epe.elig_per_elctbl_chc_id,
1254 enb.enrt_bnft_id
1255 from ben_per_in_ler pil,
1256 ben_elig_per_elctbl_chc epe,
1257 ben_pl_f pl,
1258 ben_enrt_bnft enb
1259 where
1260 pil.business_group_id=p_business_group_id and
1261 pil.person_id=p_person_id and
1262 --pil.per_in_ler_stat_cd='STRTD' and
1263 pil.per_in_ler_id = p_per_in_ler_id and
1264 epe.per_in_ler_id=pil.per_in_ler_id and
1265 epe.pl_id =p_pl_id and
1266 epe.business_group_id=p_business_group_id and
1267 epe.elctbl_flag='Y' and
1268 epe.dflt_flag='Y' and
1269 nvl(epe.dpnt_dsgn_cd,'O')='O' and
1270 -- epe.ctfn_rqd_flag='N' and
1271 epe.pl_id = pl.pl_id and
1272 ( epe.pgm_id = p_pgm_id or
1273 p_pgm_id is null ) and /* Bug 4256836 */
1274 nvl(pl.bnf_dsgn_cd, 'O') = 'O' and
1275 p_effective_date between
1276 pl.effective_start_date and pl.effective_end_date and
1277 enb.elig_per_elctbl_chc_id(+)=epe.elig_per_elctbl_chc_id and
1278 nvl(enb.dflt_flag,'Y') = 'Y' and
1279 nvl(enb.ctfn_rqd_flag,'N') ='N' and
1280 --bug#4186343
1281 not exists ( select 'Y'
1282 from ben_elctbl_chc_ctfn
1283 where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1284 and nvl(enrt_bnft_id,nvl(enb.enrt_bnft_id,-1)) = nvl(enb.enrt_bnft_id,-1)
1285 and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1286 order by epe.plip_ordr_num,epe.oipl_ordr_num
1287 ;
1288 -- This Benefit restriction still needs to look at the plan type. If the plan or
1289 -- option having the suspended coverage is not the default one, we need to
1290 -- get someother default comp object in the plan type. It could be
1291 -- Plan or Option with No Coverage. This should not be filtered by pl_id or
1292 -- oipl_id as done in Next Lower and Min in the benefit restrictions cases.
1293 --
1294 cursor c_default_bnft_epe is
1295 select epe.elig_per_elctbl_chc_id,
1296 enb.enrt_bnft_id
1297 from ben_per_in_ler pil,
1298 ben_elig_per_elctbl_chc epe,
1299 ben_pl_f pl,
1300 ben_enrt_bnft enb
1301 where
1302 pil.business_group_id=p_business_group_id and
1303 pil.person_id=p_person_id and
1304 --pil.per_in_ler_stat_cd='STRTD' and
1305 pil.per_in_ler_id = p_per_in_ler_id and
1306 epe.per_in_ler_id=pil.per_in_ler_id and
1307 epe.pl_typ_id =p_pl_typ_id and
1308 epe.business_group_id=p_business_group_id and
1309 -- epe.elctbl_flag='Y' and
1310 epe.dflt_flag='Y' and
1311 nvl(epe.dpnt_dsgn_cd,'O')='O' and
1312 epe.ctfn_rqd_flag='N' and
1313 epe.pl_id = pl.pl_id and
1314 ( epe.pgm_id = p_pgm_id or
1315 p_pgm_id is null ) and /* Bug 4256836 */
1316 nvl(pl.bnf_dsgn_cd, 'O') = 'O' and
1317 p_effective_date between
1318 pl.effective_start_date and pl.effective_end_date and
1319 enb.elig_per_elctbl_chc_id(+)=epe.elig_per_elctbl_chc_id and
1320 ((nvl(enb.dflt_flag,'Y') = 'Y' and
1321 nvl(enb.ctfn_rqd_flag,'N') ='N')
1322 or ( nvl(enb.mx_wo_ctfn_flag,'Y') = 'Y' and nvl(enb.ordr_num,0)=0 ) )
1323 and not exists ( select 'Y'
1324 from ben_elctbl_chc_ctfn
1325 where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1326 and nvl(enrt_bnft_id,nvl(enb.enrt_bnft_id,-1)) = nvl(enb.enrt_bnft_id,-1)
1327 and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1328 -- If the default one is enter value at enrollment then ctfn is Y
1329 order by epe.plip_ordr_num,epe.oipl_ordr_num,nvl(enb.ordr_num,1)
1330 ;
1331 -- This Benefit restriction still needs to look at the plan level. If the
1332 -- option having the suspended coverage is not the default one, we need to
1333 -- get someother default comp object in the plan. It could be
1334 -- Option with No Coverage. This should not be filtered by
1335 -- oipl_id as done in Next Lower and Min in the benefit restrictions cases.
1336 --
1337 cursor c_default_bnft_pl_epe is
1338 select epe.elig_per_elctbl_chc_id,
1339 enb.enrt_bnft_id
1340 from ben_per_in_ler pil,
1341 ben_elig_per_elctbl_chc epe,
1342 ben_pl_f pl,
1343 ben_enrt_bnft enb
1344 where
1345 pil.business_group_id=p_business_group_id and
1346 pil.person_id=p_person_id and
1347 --pil.per_in_ler_stat_cd='STRTD' and
1348 pil.per_in_ler_id = p_per_in_ler_id and
1349 epe.per_in_ler_id=pil.per_in_ler_id and
1350 epe.pl_id =p_pl_id and
1351 epe.business_group_id=p_business_group_id and
1352 -- epe.elctbl_flag='Y' and
1353 epe.dflt_flag='Y' and
1354 nvl(epe.dpnt_dsgn_cd,'O')='O' and
1355 epe.ctfn_rqd_flag='N' and
1356 epe.pl_id = pl.pl_id and
1357 ( epe.pgm_id = p_pgm_id or
1358 p_pgm_id is null ) and /* Bug 4256836 */
1359 nvl(pl.bnf_dsgn_cd, 'O') = 'O' and
1360 p_effective_date between
1361 pl.effective_start_date and pl.effective_end_date and
1362 enb.elig_per_elctbl_chc_id (+) = epe.elig_per_elctbl_chc_id and
1363 ((nvl(enb.dflt_flag,'Y') = 'Y' and nvl(enb.ctfn_rqd_flag,'N') ='N')
1364 or ( nvl(enb.mx_wo_ctfn_flag,'Y') = 'Y' and nvl(enb.ordr_num,0)=0 ) )
1365 and not exists ( select 'Y'
1366 from ben_elctbl_chc_ctfn
1367 where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1368 and nvl(enrt_bnft_id,nvl(enb.enrt_bnft_id,-1)) = nvl(enb.enrt_bnft_id,-1)
1369 and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1370 -- If the default one is enter value at enrollment then ctfn is Y
1371 order by epe.oipl_ordr_num,enb.ordr_num
1372 ;
1373 -- Within the Same Plan - Option Restrictions case
1374 -- There must be options in a plan to get the interim.
1375 -- Removed the comments not to get the same suspended enrollment as interim
1376 --
1377 cursor c_min_oipl_epe is
1378 select epe.elig_per_elctbl_chc_id, eb.enrt_bnft_id
1379 from ben_per_in_ler pil,
1380 ben_elig_per_elctbl_chc epe,
1381 ben_pl_f pl,
1382 ben_enrt_bnft eb
1383 where
1384 pil.business_group_id=p_business_group_id and
1385 pil.person_id=p_person_id and
1386 --pil.per_in_ler_stat_cd='STRTD' and
1387 pil.per_in_ler_id = p_per_in_ler_id and
1388 epe.per_in_ler_id=pil.per_in_ler_id and
1389 epe.pl_id =p_pl_id and
1390 epe.elctbl_flag='Y' and
1391 epe.business_group_id=p_business_group_id and
1392 nvl(epe.dpnt_dsgn_cd,'O')='O' and
1393 -- epe.ctfn_rqd_flag='N' and
1394 epe.pl_id = pl.pl_id and
1395 ( epe.pgm_id = p_pgm_id or
1396 p_pgm_id is null ) and /* Bug 4256836 */
1397 nvl(pl.bnf_dsgn_cd, 'O') = 'O' and
1398 epe.elig_per_elctbl_chc_id = eb.elig_per_elctbl_chc_id(+) and
1399 nvl(eb.ctfn_rqd_flag,'N') = 'N' and
1400 nvl(eb.ordr_num,1) > 0 and
1401 p_effective_date between
1402 pl.effective_start_date and pl.effective_end_date and
1403 epe.oipl_ordr_num is not null and
1404 epe.oipl_ordr_num< p_oipl_ordr_num
1405 and not exists ( select 'Y'
1406 from ben_elctbl_chc_ctfn
1407 where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1408 and nvl(enrt_bnft_id,nvl(eb.enrt_bnft_id,-1)) = nvl(eb.enrt_bnft_id,-1)
1409 and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1410 order by epe.oipl_ordr_num ;
1411 --
1412 -- Added more logic to handle the case - if options exist for the min plan
1413 -- we need to get the minimum option of the plan
1414 cursor c_min_pl_epe is
1415 select epe.elig_per_elctbl_chc_id,
1416 enb.enrt_bnft_id
1417 from ben_per_in_ler pil,
1418 ben_elig_per_elctbl_chc epe,
1419 ben_enrt_bnft enb,
1420 ben_pl_f pl
1421 where pil.business_group_id=p_business_group_id and
1422 pil.person_id=p_person_id and
1423 --pil.per_in_ler_stat_cd='STRTD' and
1424 pil.per_in_ler_id = p_per_in_ler_id and
1425 epe.per_in_ler_id=pil.per_in_ler_id and
1426 epe.pl_typ_id =p_pl_typ_id and
1427 -- epe.elctbl_flag='Y' and
1428 epe.business_group_id=p_business_group_id and
1429 nvl(epe.dpnt_dsgn_cd,'O')='O' and
1430 epe.pl_id = pl.pl_id and
1431 ( epe.pgm_id = p_pgm_id or
1432 p_pgm_id is null ) and /* Bug 4256836 */
1433 nvl(pl.bnf_dsgn_cd, 'O') = 'O' and
1434 p_effective_date between
1435 pl.effective_start_date and pl.effective_end_date and
1436 epe.ctfn_rqd_flag='N' and
1437 epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id (+) and
1438 nvl(enb.ctfn_rqd_flag,'N') = 'N' and
1439 nvl(enb.ordr_num,1) > 0 and
1440 epe.plip_ordr_num is not null and
1441 epe.plip_ordr_num<= p_plip_ordr_num
1442 and not exists ( select 'Y'
1443 from ben_elctbl_chc_ctfn
1444 where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1445 and nvl(enrt_bnft_id,nvl(enb.enrt_bnft_id,-1)) = nvl(enb.enrt_bnft_id,-1)
1446 and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1447 order by epe.plip_ordr_num,epe.oipl_ordr_num ;
1448 -- Get the Next Lower Option of the Plan
1449 cursor c_next_lower_pl_epe is
1450 select epe.elig_per_elctbl_chc_id,
1451 eb.enrt_bnft_id
1452 from ben_per_in_ler pil,
1453 ben_elig_per_elctbl_chc epe,
1454 ben_pl_f pl,
1455 ben_enrt_bnft eb
1456 where
1457 pil.business_group_id=p_business_group_id and
1458 pil.person_id=p_person_id and
1459 --pil.per_in_ler_stat_cd='STRTD' and
1460 pil.per_in_ler_id = p_per_in_ler_id and
1461 epe.per_in_ler_id=pil.per_in_ler_id and
1462 epe.pl_id =p_pl_id and
1463 epe.elctbl_flag='Y' and
1464 epe.business_group_id=p_business_group_id and
1465 nvl(epe.dpnt_dsgn_cd,'O')='O' and
1466 -- epe.ctfn_rqd_flag='N' and
1467 epe.pl_id = pl.pl_id and
1468 ( epe.pgm_id = p_pgm_id or
1469 p_pgm_id is null ) and /* Bug 4256836 */
1470 nvl(pl.bnf_dsgn_cd, 'O') = 'O' and
1471 epe.elig_per_elctbl_chc_id = eb.elig_per_elctbl_chc_id(+) and
1472 nvl(eb.ctfn_rqd_flag,'N') = 'N' and
1473 p_effective_date between
1474 pl.effective_start_date and pl.effective_end_date and
1475 epe.oipl_ordr_num < p_oipl_ordr_num
1476 and not exists ( select 'Y'
1477 from ben_elctbl_chc_ctfn
1478 where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1479 and nvl(enrt_bnft_id,nvl(eb.enrt_bnft_id,-1)) = nvl(eb.enrt_bnft_id,-1)
1480 and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1481 order by epe.oipl_ordr_num desc
1482 ;
1483 --
1484 -- Get the Next Lower Option in the Plan type. If there are multiple
1485 -- Plans and Options for the Plans, first find the next lower option of
1486 -- the present plan and then find the next lower plan and its options so on.
1487 --
1488 cursor c_next_lower_pl_typ_epe is
1489 select epe.elig_per_elctbl_chc_id,
1490 eb.enrt_bnft_id
1491 from ben_per_in_ler pil,
1492 ben_elig_per_elctbl_chc epe,
1493 ben_enrt_bnft eb,
1494 ben_pl_f pl
1495 where
1496 pil.business_group_id=p_business_group_id and
1497 pil.person_id=p_person_id and
1498 --pil.per_in_ler_stat_cd='STRTD' and
1499 pil.per_in_ler_id = p_per_in_ler_id and
1500 epe.per_in_ler_id=pil.per_in_ler_id and
1501 epe.pl_typ_id =p_pl_typ_id and
1502 epe.elctbl_flag='Y' and
1503 epe.business_group_id=p_business_group_id and
1504 nvl(epe.dpnt_dsgn_cd,'O')='O' and
1505 epe.pl_id = pl.pl_id and
1506 ( epe.pgm_id = p_pgm_id or
1507 p_pgm_id is null ) and /* Bug 4256836 */
1508 nvl(pl.bnf_dsgn_cd, 'O') = 'O' and
1509 p_effective_date between
1510 pl.effective_start_date and pl.effective_end_date and
1511 -- epe.ctfn_rqd_flag='N' and
1512 epe.elig_per_elctbl_chc_id = eb.elig_per_elctbl_chc_id (+) and
1513 nvl(eb.ctfn_rqd_flag,'N') = 'N' and -- Bug 2677315 changed the (+) to nvl
1514 (epe.plip_ordr_num <= p_plip_ordr_num and
1515 (epe.plip_ordr_num <> p_plip_ordr_num or
1516 (epe.oipl_ordr_num is null or epe.oipl_ordr_num < p_oipl_ordr_num ))) -- changed to < from <=
1517 and not exists ( select 'Y'
1518 from ben_elctbl_chc_ctfn
1519 where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1520 and nvl(enrt_bnft_id,nvl(eb.enrt_bnft_id,-1)) = nvl(eb.enrt_bnft_id,-1)
1521 and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1522 order by epe.plip_ordr_num desc, epe.oipl_ordr_num desc
1523 ;
1524 -- Modified the cursor to handle the minimun benefit record within the same plan or
1525 -- Option in Plan.
1526 --
1527 cursor c_min_bnft_epe is
1528 select epe.elig_per_elctbl_chc_id,
1529 enb.enrt_bnft_id
1530 from ben_per_in_ler pil,
1531 ben_elig_per_elctbl_chc epe,
1532 ben_enrt_bnft enb,
1533 ben_pl_f pl
1534 where
1535 pil.business_group_id=p_business_group_id and
1536 pil.person_id=p_person_id and
1537 --pil.per_in_ler_stat_cd='STRTD' and
1538 pil.per_in_ler_id = p_per_in_ler_id and
1539 epe.per_in_ler_id=pil.per_in_ler_id and
1540 epe.pl_id =p_pl_id and
1541 ( epe.pgm_id = p_pgm_id or
1542 p_pgm_id is null ) and /* Bug 4256836 */
1543 nvl(epe.oipl_id,-1)= nvl( p_oipl_id,-1) and
1544 epe.elctbl_flag='Y' and
1545 epe.business_group_id=p_business_group_id and
1546 nvl(epe.dpnt_dsgn_cd,'O')='O' and
1547 -- epe.ctfn_rqd_flag='N' and
1548 epe.pl_id = pl.pl_id and
1549 nvl(pl.bnf_dsgn_cd, 'O') = 'O' and
1550 p_effective_date between
1551 pl.effective_start_date and pl.effective_end_date and
1552 enb.elig_per_elctbl_chc_id=epe.elig_per_elctbl_chc_id and
1553 enb.business_group_id=p_business_group_id and
1554 enb.ctfn_rqd_flag = 'N' and
1555 enb.ordr_num < p_bnft_ordr_num
1556 and not exists ( select 'Y'
1557 from ben_elctbl_chc_ctfn
1558 where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1559 and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y'
1560 and enrt_bnft_id = enb.enrt_bnft_id ) --BUG 5158595
1561 order by enb.ordr_num
1562 ;
1563 --
1564 -- This a Benefit Restrictions Case. This needs to get the Next Lower Coverage for
1565 -- the same comp object. This is not for going to a different option. We can use one
1566 -- same cursor for Current same Plan and Current Same Plan type cases.
1567 --
1568 cursor c_next_lower_bnft_pl_epe is
1569 select epe.elig_per_elctbl_chc_id,
1570 eb.enrt_bnft_id
1571 from ben_per_in_ler pil,
1572 ben_elig_per_elctbl_chc epe,
1573 ben_enrt_bnft eb,
1574 ben_pl_f pl
1575 where
1576 pil.business_group_id=p_business_group_id and
1577 pil.person_id=p_person_id and
1578 --pil.per_in_ler_stat_cd='STRTD' and
1579 pil.per_in_ler_id = p_per_in_ler_id and
1580 epe.per_in_ler_id=pil.per_in_ler_id and
1581 epe.elctbl_flag='Y' and
1582 epe.pl_id =p_pl_id and
1583 ( epe.pgm_id = p_pgm_id or
1584 p_pgm_id is null ) and /* Bug 4256836 */
1585 nvl(epe.oipl_id,-1) = nvl(p_oipl_id,-1) and
1586 epe.business_group_id=p_business_group_id and
1587 nvl(epe.dpnt_dsgn_cd,'O')='O' and
1588 -- epe.ctfn_rqd_flag='N' and
1589 eb.ordr_num < p_bnft_ordr_num and
1590 eb.ordr_num > 0 and
1591 epe.pl_id = pl.pl_id and
1592 nvl(pl.bnf_dsgn_cd, 'O') = 'O' and
1593 p_effective_date between
1594 pl.effective_start_date and pl.effective_end_date and
1595 eb.elig_per_elctbl_chc_id=epe.elig_per_elctbl_chc_id and
1596 eb.business_group_id=p_business_group_id and
1597 eb.ctfn_rqd_flag='N'
1598 and not exists ( select 'Y'
1599 from ben_elctbl_chc_ctfn
1600 where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1601 and enrt_bnft_id = eb.enrt_bnft_id
1602 and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1603 order by eb.ordr_num desc ;
1604 --
1605 -- Since this record is created for this interim purpose with 'N' flag
1606 -- Bug the nim/max and default values are stored in the maim bnft record
1607 -- we need to get the enrt_bnft_id from the entr_val_at_enrt_flag = 'N'
1608 -- and min and default values from entr_val_at_enrt_flag = 'Y'
1609 -- condition.
1610 -- c_bnft SHOULD BE USED ONLY for Coverage Calculation of Flat Amount
1611 -- with Enter value at enrollment case
1612 /*ENH
1613 cursor c_bnft is
1614 select nvl(enb2.mn_val, 0) min_bnft_amt,
1615 nvl(enb2.dflt_val, 0) dflt_bnft_amt, -- Bug 1886183
1616 enb1.enrt_bnft_id
1617 from ben_enrt_bnft enb1,
1618 ben_enrt_bnft enb2
1619 where enb1.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
1620 and enb1.cvg_mlt_cd = 'FLFX'
1621 and enb2.elig_per_elctbl_chc_id = enb1.elig_per_elctbl_chc_id
1622 and enb2.entr_val_at_enrt_flag = 'Y'
1623 and enb1.entr_val_at_enrt_flag = 'N'; -- Bug 1886183 changed to 'N'
1624 -- Since this record is created for this interim purpose with 'N' flag
1625 -- Bug the nim/max and default values are stored in the maim bnft record
1626 -- we need to get the enrt_bnft_id from the entr_val_at_enrt_flag = 'N'
1627 -- and min and default values from entr_val_at_enrt_flag = 'Y'
1628 -- condition.
1629 */
1630 -- Now we are handling the bencvrge.pkb to get the right amount into enb1.val
1631 -- depending on the interim code. So we can take the bnft amount from the
1632 -- dummy row we create for the coverage enter value at enrollment case.
1633 -- For details so bencvrge.pkb changes.
1634 --
1635 cursor c_bnft is
1636 select enb1.val bnft_amt,
1637 enb1.enrt_bnft_id
1638 from ben_enrt_bnft enb1,
1639 ben_enrt_bnft enb2,
1640 ben_pl_f pl,
1641 ben_elig_per_elctbl_chc epe --Bug 3042379 Dont select 'R' cases
1642 --to make it consistent with other
1643 --interim cursors.
1644 where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
1645 and nvl(epe.dpnt_dsgn_cd,'O')='O'
1646 -- and epe.ctfn_rqd_flag='N'
1647 and epe.pl_id = pl.pl_id
1648 and p_effective_date between
1649 pl.effective_start_date and pl.effective_end_date
1650 and nvl(pl.bnf_dsgn_cd, 'O') ='O'
1651 and enb1.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1652 and enb1.cvg_mlt_cd = 'FLFX'
1653 and enb2.elig_per_elctbl_chc_id = enb1.elig_per_elctbl_chc_id
1654 and enb2.entr_val_at_enrt_flag = 'Y'
1655 and enb1.entr_val_at_enrt_flag = 'N'
1656 and not exists ( select 'Y'
1657 from ben_elctbl_chc_ctfn
1658 where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1659 and enrt_bnft_id = enb1.enrt_bnft_id -- 5381200
1660 and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y' )
1661 and enb1.ordr_num = 0 ;
1662 --
1663 l_bnft c_bnft%rowtype;
1664 --
1665 cursor c_dflt_bnft is
1666 select enb1.val bnft_amt,
1667 enb1.enrt_bnft_id
1668 from ben_enrt_bnft enb1,
1669 ben_enrt_bnft enb2,
1670 ben_pl_f pl,
1671 ben_elig_per_elctbl_chc epe --Bug 3042379 Dont select 'R' cases
1672 --to make it consistent with other
1673 --interim cursors.
1674 where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
1675 and nvl(epe.dpnt_dsgn_cd,'O')='O'
1676 -- and epe.ctfn_rqd_flag='N'
1677 and epe.pl_id = pl.pl_id
1678 and p_effective_date between
1679 pl.effective_start_date and pl.effective_end_date
1680 and nvl(pl.bnf_dsgn_cd, 'O') ='O'
1681 and enb1.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1682 and enb1.cvg_mlt_cd = 'FLFX'
1683 and enb2.elig_per_elctbl_chc_id = enb1.elig_per_elctbl_chc_id
1684 and enb2.entr_val_at_enrt_flag = 'Y'
1685 and enb1.entr_val_at_enrt_flag = 'N'
1686 and enb1.ordr_num = 0
1687 and enb2.dflt_flag = 'Y'
1688 and not exists ( select 'Y'
1689 from ben_elctbl_chc_ctfn
1690 where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1691 and enrt_bnft_id = enb1.enrt_bnft_id -- 5381200
1692 and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y') ;
1693 -- RCHASE Bug#5353 add current benefit cursor
1694 --
1695 cursor c_cur_bnft(p_elig_per_elctbl_chc_id in number) is
1696 select enb.val bnft_amt,
1697 enb.enrt_bnft_id
1698 from ben_enrt_bnft enb
1699 where enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
1700 ----Bug : 7195598
1701 and enb.ordr_num in (-1,1)
1702 ---Bug 7195598
1703 ---Bug 7262435
1704 order by enb.ordr_num
1705 ---Bug 7262435
1706 ;
1707 --
1708 l_cur_bnft c_cur_bnft%rowtype;
1709 --
1710 /*
1711 cursor c_current_same_epe(c_current_epe_id number) is
1712 select epe_new.elig_per_elctbl_chc_id
1713 from ben_elig_per_elctbl_chc epe_current,
1714 ben_elig_per_elctbl_chc epe_new,
1715 ben_per_in_ler pil_new
1716 where epe_current.elig_per_elctbl_chc_id = c_current_epe_id
1717 and epe_current.pl_id = epe_new.pl_id
1718 and nvl(epe_current.oipl_id,-1) = nvl(epe_new.oipl_id,-1)
1719 and epe_new.comp_lvl_cd not in ( 'PLANFC' , 'PLANIMP')
1720 and epe_new.crntly_enrd_flag = 'Y'
1721 and pil_new.per_in_ler_id = epe_new.per_in_ler_id
1722 and pil_new.per_in_ler_stat_cd='STRTD' ;
1723 */
1724 --
1725 --The following cursor is to get the present life event
1726 --electable choice using the current enrollment result
1727 --ie the enrollment result from the previous life event.
1728 --
1729 cursor c_current_same_epe(c_current_pen_id number) is
1730 select epe_new.elig_per_elctbl_chc_id
1731 from ben_prtt_enrt_rslt_f pen_current,
1732 ben_elig_per_elctbl_chc epe_new,
1733 ben_per_in_ler pil_new
1734 where pen_current.prtt_enrt_rslt_id = c_current_pen_id
1735 and pen_current.pl_id = epe_new.pl_id
1736 and nvl(pen_current.pgm_id,-1) = nvl(epe_new.pgm_id,-1)
1737 and nvl(pen_current.oipl_id,-1) = nvl(epe_new.oipl_id,-1)
1738 and epe_new.comp_lvl_cd not in ( 'PLANFC' , 'PLANIMP')
1739 and epe_new.crntly_enrd_flag = 'Y'
1740 and pil_new.per_in_ler_id = epe_new.per_in_ler_id
1741 and pil_new.person_id = p_person_id
1742 --and pil_new.per_in_ler_stat_cd='STRTD' ;
1743 and pil_new.per_in_ler_id= p_per_in_ler_id
1744 and pen_current.prtt_enrt_rslt_stat_cd is null;
1745 --
1746 l_use_same_bnft varchar2(1);
1747 l_currently_enrolled varchar2(30) := 'N' ;
1748 l_cf_required varchar2(30) := 'N' ;
1749 --
1750 --Cursor to See if we need to carry forward the certifications.
1751 --Decides if it is a carryforward suspension or not
1752 --
1753 /* Bug 4463267 This does not work as per the changes made in
1754 election information. Initially, suspended record was getting date track
1755 updated. Now we are deleting the suspended enrollment and recreating
1756 new result.
1757
1758 cursor c_cf_suspended(c_prtt_enrt_rslt_id number,
1759 c_per_in_ler_id number,
1760 c_elig_per_elctbl_chc_id number) is
1761 select currently_susp.prtt_enrt_rslt_id,
1762 currently_susp.rplcs_sspndd_rslt_id
1763 from ben_prtt_enrt_rslt_f susp,
1764 ben_prtt_enrt_rslt_f currently_susp
1765 where susp.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
1766 and susp.per_in_ler_id = c_per_in_ler_id
1767 -- and susp.sspndd_flag = 'N' --This can be changed once elinf is fixed
1768 and susp.effective_end_date = hr_api.g_eot
1769 and susp.enrt_cvg_thru_dt = hr_api.g_eot
1770 and susp.prtt_enrt_rslt_id = currently_susp.prtt_enrt_rslt_id
1771 and currently_susp.effective_end_date+1 = susp.effective_start_date
1772 and currently_susp.enrt_cvg_thru_dt = hr_api.g_eot
1773 and currently_susp.per_in_ler_id <> c_per_in_ler_id
1774 and currently_susp.sspndd_flag = 'Y' ;
1775 */
1776 cursor c_cf_suspended(c_prtt_enrt_rslt_id number,
1777 c_per_in_ler_id number,
1778 c_elig_per_elctbl_chc_id number) is
1779 select currently_susp.prtt_enrt_rslt_id,
1780 currently_susp.rplcs_sspndd_rslt_id
1781 from ben_prtt_enrt_rslt_f susp,
1782 ben_prtt_enrt_rslt_f currently_susp
1783 where susp.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
1784 and susp.per_in_ler_id = c_per_in_ler_id
1785 -- and susp.sspndd_flag = 'N' --This can be changed once elinf is fixed
1786 and susp.effective_end_date = hr_api.g_eot
1787 and susp.enrt_cvg_thru_dt = hr_api.g_eot
1788 and currently_susp.prtt_enrt_rslt_stat_cd IS NULL
1789 and susp.person_id = currently_susp.person_id
1790 and (currently_susp.pl_id = susp.pl_id AND
1791 (p_pgm_id IS NULL or currently_susp.pgm_id = susp.pgm_id) AND
1792 (p_oipl_id IS NULL or currently_susp.oipl_id = susp.oipl_id))
1793 and currently_susp.effective_end_date = hr_api.g_eot
1794 and currently_susp.enrt_cvg_thru_dt = hr_api.g_eot
1795 and currently_susp.per_in_ler_id <> c_per_in_ler_id
1796 and currently_susp.sspndd_flag = 'Y' ;
1797 --
1798 l_cf_suspended c_cf_suspended%ROWTYPE;
1799 --
1800 --determines if we have a valid interim to carry forward
1801 --
1802 cursor c_cf_interim(c_prtt_enrt_rslt_id number,
1803 c_per_in_ler_id number) is
1804 select new_epe.elig_per_elctbl_chc_id,
1805 interim.bnft_ordr_num ordr_num,
1806 interim.bnft_amt
1807 from ben_prtt_enrt_rslt_f interim,
1808 ben_elig_per_elctbl_chc new_epe
1809 where interim.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
1810 and interim.per_in_ler_id <> c_per_in_ler_id
1811 and interim.effective_end_date = hr_api.g_eot
1812 and interim.enrt_cvg_thru_dt = hr_api.g_eot
1813 and new_epe.per_in_ler_id = c_per_in_ler_id
1814 and new_epe.pl_id = interim.pl_id
1815 and nvl(new_epe.pgm_id,-1) = nvl(interim.pgm_id,-1)
1816 and nvl(new_epe.oipl_id,-1)= nvl(interim.oipl_id,-1)
1817 and interim.prtt_enrt_rslt_stat_cd is null ;
1818 --
1819 l_cf_interim c_cf_interim%ROWTYPE;
1820 --
1821 cursor c_cf_bnft(c_ordr_num number,
1822 c_bnft_amt number,
1823 c_elig_per_elctbl_chc_id number ) is
1824 select enb.enrt_bnft_id,
1825 enb.val bnft_amt
1826 from ben_enrt_bnft enb
1827 where enb.ordr_num = c_ordr_num
1828 --enb.val = c_bnft_amt
1829 and enb.entr_val_at_enrt_flag = 'N'
1830 and enb.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id ;
1831 --We are dealing this like 'SM' case as the interim code is SM
1832 --Changind as per discussion with Lynda on 12-Oct-2006
1833 cursor c_cf_bnft_sm(c_ordr_num number,
1834 c_bnft_amt number,
1835 c_elig_per_elctbl_chc_id number ) is
1836 select enb.enrt_bnft_id,
1837 enb.val bnft_amt
1838 from ben_enrt_bnft enb
1839 where --enb.ordr_num = c_ordr_num
1840 enb.val = c_bnft_amt
1841 and enb.entr_val_at_enrt_flag = 'N'
1842 and enb.mx_wo_ctfn_flag = 'Y'
1843 and enb.ordr_num = -1
1844 and enb.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id ;
1845 --
1846 --Bug#5402317
1847 cursor c_get_lf_evt_ocrd_dt
1848 is
1849 select lf_evt_ocrd_dt
1850 from ben_per_in_ler pil
1851 where pil.per_in_ler_id = p_per_in_ler_id
1852 and pil.business_group_id = p_business_group_id;
1853 --Bug#5402317
1854 --
1855 l_get_lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%type;
1856 l_ctfn_rqd varchar2(30);
1857 l_cf_bnft c_cf_bnft%ROWTYPE;
1858 --
1859 Begin
1860 hr_utility.set_location('Entering ' || l_proc, 05);
1861 hr_utility.set_location('p_per_in_ler_id '||p_per_in_ler_id,10);
1862
1863 -- set an output to null
1864 p_interim_bnft_amt := null;
1865 --
1866 open c_get_lf_evt_ocrd_dt;
1867 fetch c_get_lf_evt_ocrd_dt into l_get_lf_evt_ocrd_dt;
1868 close c_get_lf_evt_ocrd_dt;
1869 --
1870 hr_utility.set_location('l_get_lf_evt_ocrd_dt '||l_get_lf_evt_ocrd_dt,23);
1871 hr_utility.set_location('p_ler_id'||p_ler_id,23);
1872 hr_utility.set_location('p_pl_id'||p_pl_id,23);
1873 hr_utility.set_location('p_prtt_enrt_rslt_id'||p_prtt_enrt_rslt_id,23);
1874 --
1875 For l_rec in c1(l_get_lf_evt_ocrd_dt) loop
1876 if l_rec.order_no = 2 then
1877 l_BNFT_OR_OPTION_RSTRCTN_CD := l_rec.BNFT_OR_OPTION_RSTRCTN_CD;
1878 end if;
1879 If (l_rec.order_no = 3) and
1880 l_bnft_or_option_rstrctn_cd is null then
1881 l_BNFT_OR_OPTION_RSTRCTN_CD := l_rec.BNFT_OR_OPTION_RSTRCTN_CD;
1882 End if;
1883 If l_DFLT_TO_ASN_PNDG_CTFN_CD is null then
1884 l_DFLT_TO_ASN_PNDG_CTFN_CD := l_rec.DFLT_TO_ASN_PNDG_CTFN_CD;
1885 l_DFLT_TO_ASN_PNDG_CTFN_RL := l_rec.DFLT_TO_ASN_PNDG_CTFN_RL;
1886 End if;
1887 End loop;
1888 --
1889 hr_utility.set_location('l_DFLT_TO_ASN_PNDG_CTFN_CD'||l_DFLT_TO_ASN_PNDG_CTFN_CD,24);
1890 hr_utility.set_location('l_BNFT_OR_OPTION_RSTRCTN_CD'||l_BNFT_OR_OPTION_RSTRCTN_CD,24);
1891 --
1892 hr_utility.set_location(l_proc,10 );
1893 --
1894 -- If code is rule, get the real code by calling formula
1895 --
1896 If (l_DFLT_TO_ASN_PNDG_CTFN_CD = 'RL') then
1897 hr_utility.set_location(l_proc,20 );
1898
1899 if p_elig_per_elctbl_chc_id is not null then
1900 hr_utility.set_location(l_proc, 30);
1901 open c_epe;
1902 fetch c_epe into l_epe;
1903 close c_epe;
1904 end if;
1905 hr_utility.set_location(l_proc, 40);
1906
1907 if l_epe.oipl_id is not null then
1908 hr_utility.set_location(l_proc, 50);
1909 open c_opt;
1910 fetch c_opt into l_opt;
1911 close c_opt;
1912 end if;
1913 hr_utility.set_location(l_proc, 60);
1914 /* -- 4031733 - Cursor c_state populates l_state variable which is no longer
1915 -- used in the package. Cursor can be commented
1916 if p_person_id is not null then
1917 open c_state;
1918 fetch c_state into l_state;
1919 close c_state;
1920
1921 -- if l_state.region_2 is not null then
1922 -- l_jurisdiction_code :=
1923 -- pay_mag_utils.lookup_jurisdiction_code
1924 -- (p_state => l_state.region_2);
1925 -- end if;
1926 hr_utility.set_location(l_proc, 70);
1927 end if;
1928 */
1929 hr_utility.set_location(l_proc, 80);
1930
1931 open c_paf;
1932
1933 fetch c_paf into l_assignment_id,l_organization_id;
1934 if (c_paf%notfound) then
1935 close c_paf;
1936 hr_utility.set_location('BEN_91698_NO_ASSIGNMENT_FND', 80);
1937 fnd_message.set_name('BEN','BEN_91698_NO_ASSIGNMENT_FND');
1938 fnd_message.set_token('ID' , to_char(p_person_id));
1939 fnd_message.raise_error;
1940 end if;
1941 close c_paf;
1942 hr_utility.set_location(l_proc, 90);
1943
1944
1945 l_dflt_to_asn_pndg_ctfn_cd :=
1946 get_dflt_to_asn_pndg_ctfn_cd
1947 (p_dflt_to_asn_pndg_ctfn_rl => l_dflt_to_asn_pndg_ctfn_rl
1948 ,p_person_id => p_person_id
1949 ,p_per_in_ler_id => p_per_in_ler_id
1950 ,p_assignment_id => l_assignment_id
1951 ,p_organization_id => l_organization_id
1952 ,p_business_group_id => p_business_group_id
1953 ,p_pgm_id => l_epe.pgm_id
1954 ,p_pl_id => l_epe.pl_id
1955 ,p_pl_typ_id => l_epe.pl_typ_id
1956 ,p_opt_id => l_opt.opt_id
1957 ,p_ler_id => l_epe.ler_id
1958 ,p_jurisdiction_code => l_jurisdiction_code
1959 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
1960 ,p_effective_date => p_effective_date
1961 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1962 ,p_interim_epe_id => p_interim_elctbl_chc_id
1963 -- ,p_interim_enb_id => p_interim_enrt_bnft_id
1964 ,p_interim_bnft_amt => p_interim_bnft_amt
1965 );
1966 --Call the validation procedure if p_interim_elctbl_chc_id is being returned by the
1967 --abovl.
1968 --9999
1969 IF p_interim_elctbl_chc_id IS NOT NULL THEN
1970 --
1971 validate_interim_rule (
1972 p_prtt_enrt_rslt_id =>p_prtt_enrt_rslt_id
1973 ,p_elig_per_elctbl_chc_id =>p_elig_per_elctbl_chc_id
1974 ,p_enrt_bnft_id =>p_enrt_bnft_id
1975 ,p_person_id =>p_person_id
1976 ,p_ler_id =>p_ler_id
1977 ,p_per_in_ler_id =>p_per_in_ler_id
1978 ,p_pl_id =>p_pl_id
1979 ,p_pgm_id =>p_pgm_id
1980 ,p_pl_typ_id =>p_pl_typ_id
1981 ,p_oipl_id =>p_oipl_id
1982 ,p_pl_ordr_num =>p_pl_ordr_num
1983 ,p_oipl_ordr_num =>p_oipl_ordr_num
1984 ,p_plip_ordr_num =>p_plip_ordr_num
1985 ,p_bnft_ordr_num =>p_bnft_ordr_num
1986 ,p_business_group_id =>p_business_group_id
1987 ,p_interim_elctbl_chc_id =>p_interim_elctbl_chc_id
1988 ,p_interim_enrt_bnft_id =>p_interim_enrt_bnft_id
1989 ,p_interim_bnft_amt =>p_interim_bnft_amt
1990 );
1991 --
1992 p_bnft_or_option_rstrctn_cd := l_bnft_or_option_rstrctn_cd;
1993 --
1994 hr_utility.set_location('Interim Rule is returning the following comp object',1888);
1995 hr_utility.set_location('interim_chc='||p_interim_elctbl_chc_id,1888);
1996 hr_utility.set_location('interim_bnf='||p_interim_enrt_bnft_id,1888);
1997 hr_utility.set_location('interim_amt='||p_interim_bnft_amt,1888);
1998 hr_utility.set_location('l_bnft_or_option_rstrctn_cd'||l_bnft_or_option_rstrctn_cd,1888);
1999 hr_utility.set_location('Leaving ' || l_proc, 1888);
2000 --
2001 return;
2002 --
2003 END IF;
2004 --
2005 hr_utility.set_location(l_proc, 100);
2006 End if;
2007
2008 l_last_place:='Step 1';
2009 hr_utility.set_location(l_proc||l_DFLT_TO_ASN_PNDG_CTFN_CD, 120);
2010 --
2011 -- Char's 2/3 indicate whether we need to stay in what they were enrolled in
2012 -- if it's the same plan only (SE), or if it's just the same plan type (AS).
2013 l_intm_dfn_level := substr(l_DFLT_TO_ASN_PNDG_CTFN_CD,2,2);
2014 hr_utility.set_location('l_intm_dfn_level '||l_intm_dfn_level,125);
2015
2016 -- ikasire - 'AS' can have plans and also oipls. Why to restricts only to
2017 -- plans without options ? 'AS' is not for plan without options as per documentation.
2018 -- if we need to make any changes here that needs to be documented with Keith.
2019 --
2020 -- ikasire - Interim Enhancements 30-Jul-2002
2021 -- 'AS' is now used for Current Same Plan Type set of codes.
2022 -- We can have plans or option in Plans for this case.
2023 if l_intm_dfn_level = 'AS' then
2024 -- and p_oipl_id is null then Bug 1998648 This never works if there are options
2025 open c_current_enrt(v_pl_id => null,v_oipl_id => null);
2026 fetch c_current_enrt into
2027 l_enrt_pl_id,
2028 l_enrt_pl_typ_id,
2029 --l_enrt_chc_id,
2030 -- RCHASE Bug#5353
2031 l_prtt_enrt_rslt_id;
2032 close c_current_enrt;
2033 hr_utility.set_location('l_enrt_chc_id: '||to_char(l_enrt_chc_id), 61);
2034 --
2035 -- Plan Type level interim coverage defintion.
2036 -- This is supposed to be defined for plans without options i.e.
2037 -- benefits are at plan level.
2038 --
2039 if l_enrt_pl_typ_id is not null then
2040 -- Current enrollment found.
2041 -- RCHASE Bug#5353 current enrollment in same plan type requires
2042 -- iterim set to code
2043 l_interim_action:=substr(l_dflt_to_asn_pndg_ctfn_cd,4,2);
2044 --
2045 l_currently_enrolled := 'Y' ;
2046 --
2047 else
2048 -- New Enrollment.
2049 l_interim_action:=substr(l_dflt_to_asn_pndg_ctfn_cd,7,2);
2050 end if;
2051 hr_utility.set_location('l_interim_action: '||l_interim_action, 62);
2052
2053 elsif l_intm_dfn_level = 'SE' then
2054 -- This is Option or Benefits within a Plan Case
2055 open c_current_enrt(v_pl_id => p_pl_id,v_oipl_id => null );
2056 fetch c_current_enrt into
2057 l_enrt_pl_id,
2058 l_enrt_pl_typ_id,
2059 -- l_enrt_chc_id,
2060 -- RCHASE Bug#5353
2061 l_prtt_enrt_rslt_id;
2062 close c_current_enrt;
2063 hr_utility.set_location('l_enrt_chc_id: '||to_char(l_enrt_chc_id), 63);
2064 --
2065 -- Plan level interim coverage defintion.
2066 --
2067 if l_enrt_pl_id is not null then
2068 -- Current enrollment found.
2069 -- RCHASE Bug#5353 current enrollment in same plan type requires
2070 -- iterim set to code.
2071 l_interim_action:=substr(l_dflt_to_asn_pndg_ctfn_cd,4,2);
2072 --
2073 l_currently_enrolled := 'Y' ;
2074 --
2075 else
2076 -- New Enrollment.
2077 l_interim_action:=substr(l_dflt_to_asn_pndg_ctfn_cd,7,2);
2078 end if;
2079 hr_utility.set_location('l_interim_action: '||l_interim_action, 64);
2080 --
2081 elsif l_intm_dfn_level = 'SO' then
2082 -- This is Option or Benefits within a Plan Case
2083 open c_current_enrt(v_pl_id => p_pl_id,v_oipl_id => p_oipl_id );
2084 fetch c_current_enrt into
2085 l_enrt_pl_id,
2086 l_enrt_pl_typ_id,
2087 -- l_enrt_chc_id,
2088 -- RCHASE Bug#5353
2089 l_prtt_enrt_rslt_id;
2090 close c_current_enrt;
2091 hr_utility.set_location('l_enrt_chc_id: '||to_char(l_enrt_chc_id), 63);
2092 --
2093 -- Plan level interim coverage defintion.
2094 --
2095 if l_enrt_pl_id is not null then
2096 -- Current enrollment found.
2097 -- iterim set to code.
2098 l_interim_action:=substr(l_dflt_to_asn_pndg_ctfn_cd,4,2);
2099 --
2100 l_currently_enrolled := 'Y' ;
2101 --
2102 else
2103 -- New Enrollment.
2104 l_interim_action:=substr(l_dflt_to_asn_pndg_ctfn_cd,7,2);
2105 end if;
2106 hr_utility.set_location('l_interim_action: '||l_interim_action, 64);
2107 end if;
2108 hr_utility.set_location(l_proc, 180);
2109 l_last_place:='Step 3';
2110 --
2111 -- l_interim_action is either chars 4/5 (if prev enrt in same pl type was found)
2112 -- or chars 7/8 if no prev enrt in plan type was found.
2113 -- It indicates NT (no interim), MN (minimum order num), NL (next lower order num)
2114 -- or DF (default chc)
2115 -- According to doc, these should only be looked at if chars 2/3 do not produce
2116 -- an electable choice. I don't think the code is doing that. We should
2117 -- check with ddw as to which is correct.
2118 --
2119 --Carry Forward Certifcations,Suspended and Interim Enrollments logic
2120 --
2121 hr_utility.set_location('Before Checking CF Suspended Results',181);
2122 hr_utility.set_location('l_currently_enrolled:'||l_currently_enrolled,181);
2123 hr_utility.set_location('p_prtt_enrt_rslt_id:'||p_prtt_enrt_rslt_id,181);
2124 hr_utility.set_location('p_per_in_ler_id:'||p_per_in_ler_id,181);
2125 hr_utility.set_location('p_elig_per_elctbl_chc_id:'||p_elig_per_elctbl_chc_id,181);
2126 hr_utility.set_location('g_cfw_flag '||g_cfw_flag,181);
2127 --Bug 4463267
2128 IF g_cfw_flag = 'Y' THEN
2129 if l_currently_enrolled = 'Y' then
2130 --
2131 open c_cf_suspended(p_prtt_enrt_rslt_id,
2132 p_per_in_ler_id,
2133 p_elig_per_elctbl_chc_id );
2134 --
2135 fetch c_cf_suspended into l_cf_suspended ;
2136 --
2137 if c_cf_suspended%found then
2138 l_cf_required := 'Y' ;
2139 hr_utility.set_location('l_cf_required := Y',181);
2140 end if;
2141 --
2142 close c_cf_suspended ;
2143 --
2144 if l_cf_required = 'Y' and l_cf_suspended.rplcs_sspndd_rslt_id IS NOT NULL then
2145 --
2146 hr_utility.set_location('Curr Interim '||l_cf_suspended.rplcs_sspndd_rslt_id,181);
2147 --
2148 open c_cf_interim(l_cf_suspended.rplcs_sspndd_rslt_id,p_per_in_ler_id);
2149 fetch c_cf_interim into l_cf_interim;
2150 close c_cf_interim ;
2151 --
2152 p_interim_elctbl_chc_id := l_cf_interim.elig_per_elctbl_chc_id ;
2153 p_interim_enrt_rslt_id := l_cf_suspended.rplcs_sspndd_rslt_id ;
2154 --
2155 hr_utility.set_location('p_interim_elctbl_chc_id'||p_interim_elctbl_chc_id,181);
2156 hr_utility.set_location('p_interim_enrt_rslt_id'||p_interim_enrt_rslt_id,181);
2157 hr_utility.set_location(' l_cf_interim.bnft_amt '||l_cf_interim.bnft_amt,181);
2158 hr_utility.set_location(' l_cf_interim.ordr_num '||l_cf_interim.ordr_num,181);
2159 hr_utility.set_location(' l_interim_action '||l_interim_action,181);
2160 --
2161 if l_cf_interim.elig_per_elctbl_chc_id is not null then
2162 --
2163 IF l_interim_action='SM' THEN
2164 --
2165 open c_cf_bnft_sm(l_cf_interim.ordr_num, l_cf_interim.bnft_amt,
2166 l_cf_interim.elig_per_elctbl_chc_id);
2167 fetch c_cf_bnft_sm into l_cf_bnft ;
2168 close c_cf_bnft_sm ;
2169 --
2170 ELSE
2171 --
2172 open c_cf_bnft(l_cf_interim.ordr_num, l_cf_interim.bnft_amt,
2173 l_cf_interim.elig_per_elctbl_chc_id);
2174 fetch c_cf_bnft into l_cf_bnft ;
2175 close c_cf_bnft ;
2176 --
2177 END IF;
2178 --
2179 if l_cf_bnft.enrt_bnft_id IS NOT NULL THEN
2180 --
2181 p_interim_bnft_amt := l_cf_bnft.bnft_amt;
2182 p_interim_enrt_bnft_id := l_cf_bnft.enrt_bnft_id;
2183 --
2184 hr_utility.set_location('p_interim_bnft_amt '||p_interim_bnft_amt,182);
2185 hr_utility.set_location('p_interim_enrt_bnft_id '||p_interim_enrt_bnft_id,182);
2186 --
2187 end if;
2188 --
2189 end if;
2190 --
2191 end if;
2192 end if;
2193 --
2194 END IF; --g_cfw_flag
2195 --
2196 hr_utility.set_location('After CF Suspended Results cf_required :'||l_cf_required ,181);
2197 --
2198 if l_cf_required = 'N' then
2199 --
2200 if l_interim_action='NT' then
2201 hr_utility.set_location(l_proc, 190);
2202 -- No interim
2203 l_last_place:='Step 4';
2204 p_interim_elctbl_chc_id:=null;
2205 elsif l_interim_action='SM' then
2206 hr_utility.set_location(l_proc, 200);
2207 --
2208 -- Interim is old enrollment
2209 l_last_place:='Step 5';
2210 --
2211 p_interim_elctbl_chc_id := null;
2212 --
2213 open c_current_same_epe(l_prtt_enrt_rslt_id) ;
2214 fetch c_current_same_epe into p_interim_elctbl_chc_id ;
2215 close c_current_same_epe ;
2216 --
2217 if p_interim_elctbl_chc_id is not null then
2218 --
2219 open c_cur_bnft(p_interim_elctbl_chc_id);
2220 fetch c_cur_bnft into l_cur_bnft;
2221 close c_cur_bnft;
2222 if l_cur_bnft.enrt_bnft_id is not null then
2223 p_interim_bnft_amt := l_cur_bnft.bnft_amt;
2224 p_interim_enrt_bnft_id := l_cur_bnft.enrt_bnft_id;
2225 end if;
2226 hr_utility.set_location(l_proc||'Got current Interim',12);
2227 else
2228 hr_utility.set_location(l_proc||'Not Current ',12);
2229 end if;
2230 --
2231 elsif l_interim_action='MN' then
2232 hr_utility.set_location(l_proc, 210);
2233 -- interim is minimum order number
2234 l_last_place:='Step 6';
2235
2236 -- Bug 1249901: if cvg is entered at enrollment, and interim-action is Minimum
2237 -- select the min val from same enrt-bnft and enroll the person in that. When
2238 -- cvg is entered at enrt, I wouldn't expect there to be more than one
2239 -- enrt-bnft row.
2240 l_use_same_bnft := 'N';
2241 if p_elig_per_elctbl_chc_id is not null then
2242 open c_bnft;
2243 fetch c_bnft into l_bnft;
2244 close c_bnft;
2245 if l_bnft.enrt_bnft_id is not null then
2246 p_interim_bnft_amt := l_bnft.bnft_amt;
2247 p_interim_enrt_bnft_id := l_bnft.enrt_bnft_id;
2248 p_interim_elctbl_chc_id := p_elig_per_elctbl_chc_id;
2249 l_use_same_bnft := 'Y';
2250 end if;
2251 end if;
2252
2253 if l_use_same_bnft = 'N' then
2254 if l_intm_dfn_level = 'AS' then
2255 --
2256 -- Current Same Plan Type Case, we need to see the minimum PLIP order.
2257 -- If there are options for the plans we need to see the minimum OIPL
2258 -- order also within the min PLIP
2259 if l_bnft_or_option_rstrctn_cd='BNFT' then
2260 -- Benefit restrictions case here.
2261 -- Find the minimum benefit record of the same comp object
2262 open c_min_bnft_epe ;
2263 fetch c_min_bnft_epe into p_interim_elctbl_chc_id,p_interim_enrt_bnft_id ;
2264 close c_min_bnft_epe ;
2265 l_last_place:='Step 6.4.1';
2266 hr_utility.set_location(' l_last_place '||l_last_place,122);
2267 hr_utility.set_location(' p_interim_elctbl_chc_id '||p_interim_elctbl_chc_id,122);
2268 hr_utility.set_location(' p_interim_enrt_bnft_id '||p_interim_enrt_bnft_id,122);
2269 else
2270 -- Option Restrictions case here
2271 hr_utility.set_location(l_proc, 220);
2272 l_last_place:='Step 6.5.1';
2273 open c_min_pl_epe;
2274 fetch c_min_pl_epe into p_interim_elctbl_chc_id,p_interim_enrt_bnft_id ;
2275 close c_min_pl_epe;
2276 hr_utility.set_location(' l_last_place '||l_last_place,123);
2277 hr_utility.set_location(' p_interim_elctbl_chc_id '||p_interim_elctbl_chc_id,123);
2278 hr_utility.set_location(' p_interim_enrt_bnft_id '||p_interim_enrt_bnft_id,123);
2279
2280 end if;
2281 elsif l_intm_dfn_level in ( 'SE','SO') then
2282 if l_bnft_or_option_rstrctn_cd='BNFT' then
2283 -- Benefit restrictions case here.
2284 -- Find the minimum benefit record of the same comp object
2285 open c_min_bnft_epe;
2286 fetch c_min_bnft_epe into
2287 p_interim_elctbl_chc_id,
2288 l_interim_enrt_bnft_id;
2289 close c_min_bnft_epe;
2290 p_interim_enrt_bnft_id:=l_interim_enrt_bnft_id;
2291 l_last_place:= 'Step 6.6.1';
2292 hr_utility.set_location(' l_last_place '||l_last_place,124);
2293 hr_utility.set_location(' p_interim_elctbl_chc_id '||p_interim_elctbl_chc_id,124);
2294 hr_utility.set_location(' p_interim_enrt_bnft_id '||p_interim_enrt_bnft_id,124);
2295 else
2296 --
2297 -- Plan Level Option restrictions case
2298 --
2299 hr_utility.set_location(l_proc, 230);
2300 l_last_place:='Step 7';
2301 open c_min_oipl_epe;
2302 fetch c_min_oipl_epe into
2303 p_interim_elctbl_chc_id, p_interim_enrt_bnft_id;
2304 close c_min_oipl_epe;
2305 --
2306 hr_utility.set_location(' l_last_place '||l_last_place,125);
2307 hr_utility.set_location(' p_interim_elctbl_chc_id '||p_interim_elctbl_chc_id,125);
2308 hr_utility.set_location(' p_interim_enrt_bnft_id '||p_interim_enrt_bnft_id,125);
2309 end if;
2310 end if;
2311 end if;
2312 hr_utility.set_location('p_interim_elctbl_chc_id: '||
2313 to_char(p_interim_elctbl_chc_id), 240);
2314 hr_utility.set_location('p_interim_enrt_bnft_id: '||
2315 to_char(p_interim_enrt_bnft_id), 240);
2316 hr_utility.set_location('p_interim_bnft_amt: '||
2317 to_char(p_interim_bnft_amt), 240);
2318 elsif l_interim_action='NL' then
2319 hr_utility.set_location(l_proc, 250);
2320 -- Add Enter Value at enrollment Case
2321 l_use_same_bnft := 'N';
2322 if p_elig_per_elctbl_chc_id is not null then
2323 open c_bnft;
2324 fetch c_bnft into l_bnft;
2325 close c_bnft;
2326 if l_bnft.enrt_bnft_id is not null then
2327 p_interim_bnft_amt := l_bnft.bnft_amt;
2328 p_interim_enrt_bnft_id := l_bnft.enrt_bnft_id;
2329 p_interim_elctbl_chc_id := p_elig_per_elctbl_chc_id;
2330 l_use_same_bnft := 'Y';
2331 end if;
2332 end if;
2333 if l_use_same_bnft = 'N'then -- This is NOT enter value at enrollment case
2334 -- interim is next lower order number
2335 if l_intm_dfn_level = 'AS' then
2336 hr_utility.set_location(l_proc, 255);
2337 if l_bnft_or_option_rstrctn_cd='BNFT' then
2338 --
2339 -- Add Benefit Restrictions case. We need to look for the
2340 -- Next lower benefit record for the suspended comp object only.
2341 --
2342 open c_next_lower_bnft_pl_epe;
2343 fetch c_next_lower_bnft_pl_epe into
2344 p_interim_elctbl_chc_id,
2345 p_interim_enrt_bnft_id;
2346 close c_next_lower_bnft_pl_epe;
2347 else
2348 l_last_place:='Step 6.5';
2349 -- This is Option Restrictions case
2350 open c_next_lower_pl_typ_epe;
2351 fetch c_next_lower_pl_typ_epe into
2352 p_interim_elctbl_chc_id,
2353 p_interim_enrt_bnft_id ;
2354 close c_next_lower_pl_typ_epe;
2355 end if;
2356 --
2357 elsif l_intm_dfn_level in ('SE','SO') then
2358 if l_bnft_or_option_rstrctn_cd='BNFT' then
2359 hr_utility.set_location(l_proc, 260);
2360 l_last_place:='Step 8';
2361 --
2362 -- Add Benefit Restrictions case. We need to look for the
2363 -- Next lower benefit record for the suspended comp object only.
2364 --
2365 open c_next_lower_bnft_pl_epe;
2366 fetch c_next_lower_bnft_pl_epe into
2367 p_interim_elctbl_chc_id,
2368 p_interim_enrt_bnft_id;
2369 close c_next_lower_bnft_pl_epe;
2370 else
2371 hr_utility.set_location(l_proc, 270);
2372 l_last_place:='Step 9';
2373 open c_next_lower_pl_epe;
2374 fetch c_next_lower_pl_epe into
2375 p_interim_elctbl_chc_id, p_interim_enrt_bnft_id;
2376 close c_next_lower_pl_epe;
2377 end if;
2378 end if;
2379 end if;
2380 hr_utility.set_location(l_proc, 280);
2381 elsif l_interim_action='DF' then
2382 -- Added the Enter value at enrollment case to default
2383 l_use_same_bnft := 'N';
2384 if p_elig_per_elctbl_chc_id is not null then
2385 open c_dflt_bnft ;
2386 fetch c_dflt_bnft into l_bnft ;
2387 close c_dflt_bnft ;
2388 if l_bnft.enrt_bnft_id is not null then
2389 p_interim_bnft_amt := l_bnft.bnft_amt;
2390 p_interim_enrt_bnft_id := l_bnft.enrt_bnft_id;
2391 p_interim_elctbl_chc_id := p_elig_per_elctbl_chc_id;
2392 l_use_same_bnft := 'Y';
2393 end if;
2394 end if;
2395 --
2396 if l_use_same_bnft = 'N' then
2397 --Bug 1998648 added the SE and AS if condition
2398 if l_intm_dfn_level = 'AS' then
2399 --
2400 if l_bnft_or_option_rstrctn_cd='BNFT' then
2401 -- Benefit Restrictions case
2402 -- We need to get the default benefit record of the default plan in the
2403 -- plan type of the suspended enrollment.
2404 open c_default_bnft_epe;
2405 fetch c_default_bnft_epe into p_interim_elctbl_chc_id,
2406 p_interim_enrt_bnft_id ;
2407 close c_default_bnft_epe;
2408 else
2409 -- Option Restrictions Case
2410 -- We need to get the default plan or option in plan in the
2411 -- plan type of the suspended enrollment
2412 open c_default_epe;
2413 fetch c_default_epe into p_interim_elctbl_chc_id,
2414 p_interim_enrt_bnft_id ;
2415 close c_default_epe;
2416 --
2417 end if;
2418
2419 hr_utility.set_location(l_proc, 290);
2420 -- interim is default choice
2421 l_last_place:='Step 10';
2422 elsif l_intm_dfn_level in ('SE','SO') then
2423 --
2424 if l_bnft_or_option_rstrctn_cd='BNFT' then
2425 -- Benefit Restrictions case
2426 -- We need to get the default benefit record of the default plan in the
2427 -- plan type of the suspended enrollment.
2428 open c_default_bnft_pl_epe;
2429 fetch c_default_bnft_pl_epe into p_interim_elctbl_chc_id,
2430 p_interim_enrt_bnft_id ;
2431 close c_default_bnft_pl_epe;
2432 else
2433 -- Option Restrictions Case
2434 -- We need to get the default plan or option in plan in the
2435 -- plan type of the suspended enrollment
2436 l_last_place:='Step 11';
2437 open c_default_pl_epe;
2438 fetch c_default_pl_epe into p_interim_elctbl_chc_id,
2439 p_interim_enrt_bnft_id ;
2440 close c_default_pl_epe;
2441 --
2442 end if;
2443 --
2444 hr_utility.set_location(l_proc, 294);
2445 --
2446 end if;
2447 --
2448 end if;
2449 --
2450 end if;
2451 --
2452 end if; -- l_cf_required = 'N' case
2453 --
2454 -- Bug 1247109. commented out this 'if' stmt. It was causing us to try to use
2455 -- the suspended result id as the interim coverage, which caused an error due
2456 -- to object version number being wrong in update_enrollment. Just allow
2457 -- subsequent process (election information) to get the result id for the
2458 -- interim choice.
2459
2460 -- if l_enrt_chc_id=p_interim_elctbl_chc_id then
2461 -- hr_utility.set_location('pen_id='||to_char(p_prtt_enrt_rslt_id), 9876);
2462 -- p_interim_enrt_rslt_id:=p_prtt_enrt_rslt_id;
2463 --else
2464 -- RCHASE Bug#5353 nullifying the p_interim_enrt_rslt_id will not allow for the
2465 -- previous election to be used as the interim. Removed null assignment.
2466 -- hr_utility.set_location(l_proc, 300);
2467 -- p_interim_enrt_rslt_id:=null;
2468 --end if;
2469 p_bnft_or_option_rstrctn_cd := l_bnft_or_option_rstrctn_cd ;
2470 --
2471 hr_utility.set_location('interim_chc='||p_interim_elctbl_chc_id,999);
2472 hr_utility.set_location('dflt_to_asn_pndg_ctfn_cd='||l_DFLT_TO_ASN_PNDG_CTFN_CD,999);
2473 hr_utility.set_location('interim_action='||l_interim_action,999);
2474 hr_utility.set_location('interim_bnf='||p_interim_enrt_bnft_id,999);
2475 hr_utility.set_location('p_interim_enrt_rslt_id='||p_interim_enrt_rslt_id,999);
2476 hr_utility.set_location('Leaving ' || l_proc, 999);
2477
2478 Exception
2479 When others then
2480 hr_utility.set_location(l_proc, 320);
2481 rpt_error (p_proc => l_proc, p_last_action => l_last_place);
2482 -- hr_utility.set_location('ERROR: '||sqlerrm,1);
2483 -- for nocopy changes
2484 p_interim_elctbl_chc_id := l_interim_elctbl_chc_id;
2485 fnd_message.raise_error;
2486 End Determine_interim;
2487 --
2488 -- ==========================================================================
2489 -- << Process_interim >>
2490 -- ==========================================================================
2491 --
2492 Procedure process_interim
2493 (p_elig_per_elctbl_chc_id in number
2494 ,p_enrt_bnft_id in number
2495 ,p_bnft_amt in number
2496 ,p_prtt_enrt_rslt_id in out nocopy number
2497 ,p_business_group_id in number
2498 ,p_effective_date in date
2499 ,p_enrt_mthd_cd in varchar2 ) is
2500
2501 cursor c_epe is
2502 select epe.ELIG_PER_ELCTBL_CHC_ID,
2503 epe.pgm_id,
2504 epe.pl_id,
2505 epe.oipl_id,
2506 pen.pgm_id pen_pgm_id,
2507 pen.pl_id pen_pl_id,
2508 pen.oipl_id pen_oipl_id,
2509 pen.enrt_cvg_thru_dt,
2510 pen.object_version_number
2511 From ben_elig_per_elctbl_chc epe,
2512 ben_prtt_enrt_rslt_f pen
2513 Where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
2514 and epe.business_group_id = p_business_group_id
2515 and pen.prtt_enrt_rslt_id(+)=epe.prtt_enrt_rslt_id
2516 and pen.business_group_id(+)=p_business_group_id
2517 and pen.prtt_enrt_rslt_stat_cd is null
2518 and p_effective_date between
2519 pen.effective_start_date(+) and pen.effective_end_date(+)
2520 ;
2521 l_epe c_epe%rowtype;
2522 --
2523 cursor c_rt is
2524 select ecr.enrt_rt_id
2525 ,ecr.dflt_val
2526 ,ecr.ANN_DFLT_VAL
2527 from ben_enrt_rt ecr
2528 where ecr.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
2529 and ecr.business_group_id = p_business_group_id
2530 and ecr.entr_val_at_enrt_flag = 'Y'
2531 -- and ecr.prtt_rt_val_id is null -- Bug 5415757 - This clause prevented carry forward of rates for interim PEN
2532 union
2533 select ecr.enrt_rt_id
2534 ,ecr.dflt_val
2535 ,ecr.ANN_DFLT_VAL
2536 from ben_enrt_rt ecr
2537 ,ben_enrt_bnft enb
2538 where enb.enrt_bnft_id = ecr.enrt_bnft_id
2539 and ecr.business_group_id = p_business_group_id
2540 and enb.business_group_id = p_business_group_id
2541 and enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
2542 and ecr.entr_val_at_enrt_flag = 'Y'
2543 -- and ecr.prtt_rt_val_id is null -- Bug 5415757 - This clause prevented carry forward of rates for interim PEN
2544 ;
2545 --
2546 cursor c_bnft is
2547 select enrt_bnft_id, val
2548 from ben_enrt_bnft
2549 where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
2550 and business_group_id=p_business_group_id
2551 -- and dflt_flag = 'Y'
2552 and (enrt_bnft_id=p_enrt_bnft_id
2553 or (p_enrt_bnft_id is null and
2554 dflt_flag='Y'))
2555 ;
2556 type enrt_id_table is table of c_rt%rowtype index by binary_integer;
2557 l_proc varchar2(80) := g_package || '.process_interim';
2558 l_rt enrt_id_table;
2559 l_tot_rt number(5) := 0;
2560 l_bnft_amt ben_enrt_bnft.val%type;
2561 l_bnft_id ben_enrt_bnft.enrt_bnft_id%type;
2562 l_suspend_flag varchar2(30);
2563 l_prtt_enrt_interim_id number(15);
2564 l_datetrack_mode varchar2(30);
2565 l_effective_start_date date;
2566 l_effective_end_date date;
2567 l_dump_num number(15);
2568 -- RCHASE Bug#5353 added for generating output
2569 l_ret number;
2570 l_dump_boolean boolean;
2571 l_last_place varchar2(80);
2572
2573 -- for nocopy changes
2574 l_prtt_enrt_rslt_id number := p_prtt_enrt_rslt_id;
2575 Begin
2576 hr_utility.set_location ('Entering '|| l_proc,5);
2577 hr_utility.set_location('pen_id='||to_char(p_prtt_enrt_rslt_id), 1499);
2578 open c_epe;
2579 fetch c_epe into l_epe;
2580 If c_epe%notfound then
2581 close c_epe;
2582 hr_utility.set_location('BEN_91457_ELCTBL_CHC_NOT_FOUND id:'||
2583 to_char(p_elig_per_elctbl_chc_id), 10);
2584 fnd_message.set_name('BEN','BEN_91457_ELCTBL_CHC_NOT_FOUND');
2585 fnd_message.set_token('ID', to_char(p_elig_per_elctbl_chc_id));
2586 fnd_message.set_token('PROC', '1:'||l_proc);
2587 fnd_message.raise_error;
2588 End if;
2589 close c_epe;
2590 --
2591 -- Get Benefit ID and Benefit amount
2592 --
2593 if p_enrt_bnft_id is not null and p_bnft_amt is null then
2594 open c_bnft;
2595 fetch c_bnft into l_bnft_id, l_bnft_amt;
2596 close c_bnft;
2597 else
2598 l_bnft_amt := p_bnft_amt;
2599 l_bnft_id := p_enrt_bnft_id;
2600 end if;
2601 --
2602 hr_utility.set_location(' l_bnft_amt '||l_bnft_amt,1234);
2603 hr_utility.set_location(' l_bnft_id '||l_bnft_id,1234);
2604 -- Initialize enrt_id_tbl and enrt_val_tbl, then load rate data
2605 --
2606 For i in 1..10 loop
2607 l_rt(i).enrt_rt_id := NULL;
2608 l_rt(i).dflt_val := 0;
2609 l_rt(i).ann_dflt_val := 0;
2610 End loop;
2611 l_tot_rt := 0;
2612 For Crec in c_rt loop
2613 l_tot_rt := l_tot_rt + 1;
2614 l_rt(l_tot_rt).enrt_rt_id := Crec.enrt_rt_id;
2615 l_rt(l_tot_rt).dflt_val := Crec.dflt_val;
2616 l_rt(l_tot_rt).ann_dflt_val := Crec.ann_dflt_val;
2617 End loop;
2618 l_suspend_flag := 'N';
2619 g_use_new_result:=true;
2620 --
2621 hr_utility.set_location('g_use_new_result',333);
2622 --
2623 --CFW. Same epe. Just needs to have the interim updated with the new pil
2624 --
2625 /*
2626 if nvl(l_epe.pgm_id,-1) = nvl(l_epe.pen_pgm_id,-1) and
2627 nvl(l_epe.pl_id,-1) = nvl(l_epe.pen_pl_id,-1) and
2628 nvl(l_epe.oipl_id,-1) = nvl(l_epe.pen_oipl_id,-1) and
2629 l_epe.enrt_cvg_thru_dt = hr_api.g_eot then
2630 g_use_new_result:=false;
2631 --
2632 hr_utility.set_location('g_use_new_result false',333);
2633 --
2634 end if;
2635 */
2636 --
2637 ben_election_information.election_information
2638 (p_elig_per_elctbl_chc_id => l_epe.elig_per_elctbl_chc_id
2639 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
2640 ,p_effective_date => p_effective_date
2641 ,p_enrt_mthd_cd => p_enrt_mthd_cd
2642 ,p_called_from_sspnd => 'Y'
2643 ,p_business_group_id => p_business_group_id
2644 ,p_enrt_bnft_id => l_bnft_id
2645 ,p_bnft_val => l_bnft_amt
2646 ,p_enrt_rt_id1 => l_rt(1).enrt_rt_id
2647 ,p_enrt_rt_id2 => l_rt(2).enrt_rt_id
2648 ,p_enrt_rt_id3 => l_rt(3).enrt_rt_id
2649 ,p_enrt_rt_id4 => l_rt(4).enrt_rt_id
2650 ,p_enrt_rt_id5 => l_rt(5).enrt_rt_id
2651 ,p_enrt_rt_id6 => l_rt(6).enrt_rt_id
2652 ,p_enrt_rt_id7 => l_rt(7).enrt_rt_id
2653 ,p_enrt_rt_id8 => l_rt(8).enrt_rt_id
2654 ,p_enrt_rt_id9 => l_rt(9).enrt_rt_id
2655 ,p_enrt_rt_id10 => l_rt(10).enrt_rt_id
2656 ,p_rt_val1 => l_rt(1).dflt_val
2657 ,p_rt_val2 => l_rt(2).dflt_val
2658 ,p_rt_val3 => l_rt(3).dflt_val
2659 ,p_rt_val4 => l_rt(4).dflt_val
2660 ,p_rt_val5 => l_rt(5).dflt_val
2661 ,p_rt_val6 => l_rt(6).dflt_val
2662 ,p_rt_val7 => l_rt(7).dflt_val
2663 ,p_rt_val8 => l_rt(8).dflt_val
2664 ,p_rt_val9 => l_rt(9).dflt_val
2665 ,p_rt_val10 => l_rt(10).dflt_val
2666 ,p_Ann_rt_val1 => l_rt(1).ann_dflt_val
2667 ,p_Ann_rt_val2 => l_rt(2).ann_dflt_val
2668 ,p_Ann_rt_val3 => l_rt(3).ann_dflt_val
2669 ,p_Ann_rt_val4 => l_rt(4).ann_dflt_val
2670 ,p_Ann_rt_val5 => l_rt(5).ann_dflt_val
2671 ,p_Ann_rt_val6 => l_rt(6).ann_dflt_val
2672 ,p_Ann_rt_val7 => l_rt(7).ann_dflt_val
2673 ,p_Ann_rt_val8 => l_rt(8).ann_dflt_val
2674 ,p_Ann_rt_val9 => l_rt(9).ann_dflt_val
2675 ,p_Ann_rt_val10 => l_rt(10).ann_dflt_val
2676 ,p_datetrack_mode => hr_api.g_update
2677 ,p_suspend_flag => l_suspend_flag
2678 ,p_prtt_enrt_interim_id => l_prtt_enrt_interim_id
2679 ,P_PRTT_RT_VAL_ID1 => l_dump_num
2680 ,P_PRTT_RT_VAL_ID2 => l_dump_num
2681 ,P_PRTT_RT_VAL_ID3 => l_dump_num
2682 ,P_PRTT_RT_VAL_ID4 => l_dump_num
2683 ,P_PRTT_RT_VAL_ID5 => l_dump_num
2684 ,P_PRTT_RT_VAL_ID6 => l_dump_num
2685 ,P_PRTT_RT_VAL_ID7 => l_dump_num
2686 ,P_PRTT_RT_VAL_ID8 => l_dump_num
2687 ,P_PRTT_RT_VAL_ID9 => l_dump_num
2688 ,P_PRTT_RT_VAL_ID10 => l_dump_num
2689 ,P_OBJECT_VERSION_NUMBER => l_epe.object_version_number
2690 ,p_effective_start_date => l_effective_start_date
2691 ,p_effective_end_date => l_effective_end_date
2692 ,P_DPNT_ACTN_WARNING => l_dump_boolean
2693 ,P_BNF_ACTN_WARNING => l_dump_boolean
2694 ,P_CTFN_ACTN_WARNING => l_dump_boolean
2695 );
2696 g_use_new_result:=false;
2697 hr_utility.set_location ('Leaving '|| l_proc,99);
2698 Exception
2699 When others then
2700 hr_utility.set_location('ERROR '||l_proc, 98);
2701 rpt_error (p_proc => l_proc, p_last_action => l_last_place);
2702 -- for nocopy changes
2703 p_prtt_enrt_rslt_id := l_prtt_enrt_rslt_id;
2704 g_interim_flag := 'N'; -- bug 5653168
2705 fnd_message.raise_error;
2706 End process_interim;
2707 --
2708 -- ==========================================================================
2709 -- << Update_sspndd_flag >>
2710 -- ==========================================================================
2711 --
2712 Procedure update_sspndd_flag
2713 (p_prtt_enrt_rslt_id in number,
2714 p_effective_date in date,
2715 p_business_group_id in number,
2716 p_sspndd_flag in varchar2,
2717 p_RPLCS_SSPNDD_RSLT_ID in number,
2718 p_object_version_number in out nocopy number,
2719 p_datetrack_mode in varchar2,
2720 p_ENRT_PL_OPT_FLAG in varchar2 default 'N',
2721 p_enrt_cvg_strt_dt in date default hr_api.g_date,
2722 p_enrt_cvg_thru_dt in date default hr_api.g_date,
2723 p_pgm_id in number default NULL,
2724 p_pl_id in number default NULL,
2725 p_person_id in number
2726 ) is
2727 Cursor csr_pen is
2728 Select rplcs_sspndd_rslt_id
2729 ,prtt_enrt_rslt_id
2730 ,effective_start_date
2731 ,effective_end_date
2732 ,enrt_cvg_strt_dt
2733 ,enrt_cvg_thru_dt
2734 ,object_version_number
2735 From ben_prtt_enrt_rslt_f pen
2736 Where pen.business_group_id = p_business_group_id
2737 And pen.person_id = p_person_id
2738 And nvl(pen.pgm_id,-1) = nvl(p_pgm_id,-1)
2739 And pen.pl_id = p_pl_id
2740 And p_effective_date between
2741 pen.effective_start_date and nvl(pen.effective_end_date,hr_api.g_eot)
2742 And pen.sspndd_flag = 'Y'
2743 and pen.prtt_enrt_rslt_stat_cd is null
2744 And pen.oipl_id is not NULL
2745 ;
2746 l_effective_start_date date;
2747 l_effective_end_date date;
2748 l_proc varchar2(80) := g_package || '.update_sspndd_flag';
2749 l_last_place varchar2(100);
2750 l_datetrack_mode varchar2(30);
2751 -- for nocopy changes
2752 l_object_version_number number := p_object_version_number ;
2753
2754 Begin
2755 hr_utility.set_location('Entering '||l_proc, 05);
2756
2757 l_last_place := 'Calling get_ben_pen_upd_dt_mode';
2758 --
2759 hr_utility.set_location('p_datetrack_mode '||p_datetrack_mode,10);
2760 --
2761 ben_prtt_enrt_result_api.get_ben_pen_upd_dt_mode
2762 (p_effective_date => p_effective_date
2763 ,p_base_key_value => p_prtt_enrt_rslt_id
2764 ,P_desired_datetrack_mode => p_datetrack_mode
2765 ,P_datetrack_allow => l_datetrack_mode
2766 );
2767 --
2768 hr_utility.set_location('l_datetrack_mode '||l_datetrack_mode,10);
2769 --
2770 l_last_place := 'Calling update_prtt_enrt_result';
2771 ben_prtt_enrt_result_api.update_prtt_enrt_result
2772 (p_validate => FALSE,
2773 p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
2774 p_effective_start_date => l_effective_start_date,
2775 p_effective_end_date => l_effective_end_date,
2776 p_business_group_id => p_business_group_id,
2777 p_sspndd_flag => p_sspndd_flag,
2778 p_RPLCS_SSPNDD_RSLT_ID => p_rplcs_sspndd_rslt_id,
2779 p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt,
2780 p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt,
2781 p_object_version_number => p_object_version_number,
2782 p_effective_date => p_effective_date,
2783 p_datetrack_mode => l_datetrack_mode,
2784 p_multi_row_validate => FALSE,
2785 p_program_application_id => fnd_global.prog_appl_id,
2786 p_program_id => fnd_global.conc_program_id,
2787 p_request_id => fnd_global.conc_request_id,
2788 p_program_update_date => sysdate);
2789 --
2790 -- If the un-suspended plan is saving plan, then un-suspend its
2791 -- options as well.
2792 --
2793 If (p_ENRT_PL_OPT_FLAG = 'Y') then
2794 l_last_place := 'Calling update_prtt_enrt_rslt options...';
2795 For l_rec in csr_pen loop
2796 If (p_effective_date = l_rec.effective_start_date) Then
2797 l_datetrack_mode := 'CORRECTION';
2798 Else
2799 l_datetrack_mode := p_datetrack_mode;
2800 End If;
2801 l_last_place := 'Calling update_prtt_enrt_rslt case 1.2';
2802 ben_prtt_enrt_result_api.update_prtt_enrt_result
2803 (p_validate => FALSE,
2804 p_prtt_enrt_rslt_id => l_rec.prtt_enrt_rslt_id,
2805 p_effective_start_date => l_rec.effective_start_date,
2806 p_effective_end_date => l_rec.effective_end_date,
2807 p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt,
2808 p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt,
2809 p_business_group_id => p_business_group_id,
2810 p_sspndd_flag => p_sspndd_flag,
2811 p_RPLCS_SSPNDD_RSLT_ID => l_rec.rplcs_sspndd_rslt_id,
2812 p_object_version_number => l_rec.object_version_number,
2813 p_effective_date => p_effective_date,
2814 p_datetrack_mode => l_datetrack_mode,
2815 p_multi_row_validate => FALSE,
2816 p_program_application_id => fnd_global.prog_appl_id,
2817 p_program_id => fnd_global.conc_program_id,
2818 p_request_id => fnd_global.conc_request_id,
2819 p_program_update_date => sysdate);
2820 End loop;
2821 End if;
2822
2823 -- when result is suspended or unsuspended in correction mode,
2824 -- we can't compute premiums or premium credits.
2825 -- Tell use that they may want to manually.
2826 -- If in correction and esd of result is before first day of this month...
2827 if l_datetrack_mode = 'CORRECTION' and l_effective_start_date <
2828 to_date(to_char(p_effective_date, 'mm-yyyy'), 'mm-yyyy') then
2829 if p_sspndd_flag = 'Y' then
2830 ben_prem_prtt_monthly.premium_warning
2831 (p_person_id => p_person_id
2832 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
2833 ,p_effective_start_date => l_effective_start_date
2834 ,p_effective_date => p_effective_date
2835 ,p_warning => 'SUSPEND');
2836 else
2837 ben_prem_prtt_monthly.premium_warning
2838 (p_person_id => p_person_id
2839 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
2840 ,p_effective_start_date => l_effective_start_date
2841 ,p_effective_date => p_effective_date
2842 ,p_warning => 'UNSUSPEND');
2843 end if;
2844 end if;
2845
2846 hr_utility.set_location('Leaving:'||l_proc, 99);
2847 Exception
2848 When others then
2849 hr_utility.set_location('ERROR '||l_proc, 97);
2850 Rpt_error(p_proc => l_proc, p_last_action => l_last_place);
2851 -- for nocopy changes
2852 p_object_version_number := l_object_version_number ;
2853 fnd_message.raise_error;
2854 End update_sspndd_flag;
2855 --
2856 -- =======================================================================
2857 -- << Suspend_enrollment >>
2858 -- =======================================================================
2859 --
2860 Procedure suspend_enrollment
2861 (p_prtt_enrt_rslt_id in number,
2862 p_effective_date in date,
2863 p_post_rslt_flag in varchar2,
2864 p_business_group_id in number,
2865 p_object_version_number in out nocopy number,
2866 p_datetrack_mode in varchar2
2867 ) is
2868 --
2869 Cursor Csr_prtt_enrt_rslt is
2870 select pen.rplcs_sspndd_rslt_id
2871 ,pen.prtt_enrt_rslt_id
2872 ,pen.person_id
2873 ,pen.pgm_id
2874 ,pen.sspndd_flag
2875 ,pen.enrt_mthd_cd
2876 ,pen.enrt_cvg_strt_dt
2877 ,pen.effective_start_date
2878 ,pen.effective_end_date
2879 ,epe.prtt_enrt_rslt_id chc_prtt_enrt_rslt_id
2880 ,epe.elig_per_elctbl_chc_id
2881 ,pen.pl_id
2882 ,pen.oipl_id
2883 ,pen.pl_typ_id
2884 ,pen.ler_id
2885 ,pen.per_in_ler_id
2886 ,pen.oipl_ordr_num
2887 ,pen.pl_ordr_num
2888 ,pen.bnft_amt
2889 ,pen.plip_ordr_num /*ENH*/
2890 ,pen.bnft_ordr_num /*ENH*/
2891 ,epe.dpnt_dsgn_cd
2892 From ben_prtt_enrt_rslt_f pen
2893 ,ben_elig_per_elctbl_chc epe
2894 ,ben_per_in_ler pil
2895 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2896 and pen.business_group_id= p_business_group_id
2897 and pen.prtt_enrt_rslt_stat_cd is null
2898 and p_effective_date between
2899 pen.effective_start_date and pen.effective_end_date
2900 and pen.business_group_id = epe.business_group_id (+)
2901 and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id (+)
2902 and pen.per_in_ler_id = epe.per_in_ler_id (+)
2903 and pil.per_in_ler_id=pen.per_in_ler_id --Bug#5491212
2904 and pil.business_group_id=pen.business_group_id --Bug#5491212
2905 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
2906 ;
2907 cursor c_current_bnft (p_prtt_enrt_rslt_id number,
2908 p_elig_per_elctbl_chc_id number,
2909 p_bnft_ordr_num number ) is
2910 select enb.enrt_bnft_id
2911 from ben_enrt_bnft enb
2912 where enb.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id
2913 and enb.elig_per_elctbl_chc_id=p_elig_per_elctbl_chc_id
2914 and enb.business_group_id=p_business_group_id
2915 union -- To get this when the enb is not update with the pen_id
2916 -- This happens in the flex enrollment if the certifications is called
2917 -- from flex routine /*ENH*/
2918 select enb.enrt_bnft_id
2919 from ben_enrt_bnft enb
2920 where
2921 enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
2922 and enb.ordr_num = p_bnft_ordr_num ;
2923
2924 Cursor c_new_ovn is
2925 select pen.object_version_number
2926 From ben_prtt_enrt_rslt_f pen
2927 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2928 and pen.business_group_id= p_business_group_id
2929 and pen.prtt_enrt_rslt_stat_cd is null
2930 and p_effective_date between
2931 pen.effective_start_date and pen.effective_end_date
2932 ;
2933 Cursor c_epe (c_elig_per_elctbl_chc_id number) is
2934 select *
2935 From ben_elig_per_elctbl_chc
2936 Where elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
2937 ;
2938 cursor c_prv_ee (c_prtt_enrt_rslt_id number) is
2939 select prv.prtt_rt_val_id
2940 ,prv.object_version_number
2941 ,prv.rt_strt_dt
2942 ,prv.rt_end_dt
2943 ,prv.rt_val
2944 ,prv.acty_base_rt_id
2945 ,prv.acty_ref_perd_cd
2946 ,abr.input_value_id
2947 ,abr.element_type_id
2948 ,prv.element_entry_value_id
2949 ,pev.effective_end_date
2950 ,pee.element_link_id
2951 from ben_prtt_rt_val prv,
2952 ben_acty_base_rt_f abr,
2953 pay_element_entry_values_f pev,
2954 pay_element_entries_f pee
2955 where prv.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
2956 and prv.rt_end_dt=hr_api.g_eot
2957 and prv.business_group_id = p_business_group_id
2958 and prv.prtt_rt_val_stat_cd is null
2959 and abr.acty_base_rt_id=prv.acty_base_rt_id
2960 and abr.business_group_id = p_business_group_id
2961 and p_effective_date between
2962 abr.effective_start_date and abr.effective_end_date
2963 and pev.element_entry_value_id = prv.element_entry_value_id
2964 and prv.rt_strt_dt between
2965 pev.effective_start_date and pev.effective_end_date
2966 and pee.element_entry_id = pev.element_entry_id
2967 and prv.rt_strt_dt between
2968 pee.effective_start_date and pee.effective_end_date
2969 ;
2970 cursor c_pl(p_pl_id number) is
2971 select 'x' from ben_pl_f pl
2972 where pl.pl_id = p_pl_id
2973 and pl.SUBJ_TO_IMPTD_INCM_TYP_CD is not null
2974 and p_effective_date between
2975 pl.effective_start_date and pl.effective_end_date;
2976 --Bug 1998648 Cursor to get action items
2977 --
2978 cursor c_pea(p_prtt_enrt_rslt_id number) is
2979 select
2980 pea.prtt_enrt_actn_id
2981 ,pea.actn_typ_id
2982 ,pea.rqd_flag
2983 ,pea.business_group_id
2984 ,pea.object_version_number pea_object_version_number
2985 ,pen.object_version_number pen_object_version_number
2986 --START OHSU
2987 ,pea.effective_start_date pea_effective_date
2988 --END OHSU
2989 from ben_prtt_enrt_actn_f pea,
2990 ben_prtt_enrt_rslt_f pen
2991 where
2992 pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2993 --START OHSU
2994 and p_effective_date between pen.effective_start_date and
2995 pen.effective_end_date
2996 --END OHSU
2997 and pen.prtt_enrt_rslt_id = pea.prtt_enrt_rslt_id
2998 and pea.rqd_flag = 'Y'
2999 and pen.prtt_enrt_rslt_stat_cd is null;
3000 --
3001 cursor c_interim (p_prtt_enrt_rslt_id number,
3002 p_per_in_ler_id number) is
3003 select pen.RPLCS_SSPNDD_RSLT_ID
3004 from ben_prtt_enrt_rslt_f pen
3005 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3006 and pen.sspndd_flag = 'Y'
3007 and pen.per_in_ler_id = p_per_in_ler_id
3008 and pen.prtt_enrt_rslt_stat_cd is null
3009 and not exists
3010 (select null
3011 from ben_prtt_enrt_rslt_f pen3
3012 where pen3.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
3013 and pen3.prtt_enrt_rslt_stat_cd is null
3014 and pen3.effective_start_date < pen.effective_start_date
3015 and pen3.per_in_ler_id <> pen.per_in_ler_id)
3016 and exists (select null from ben_prtt_enrt_rslt_f pen2
3017 where pen2.prtt_enrt_rslt_id = pen.RPLCS_SSPNDD_RSLT_ID
3018 and pen2.prtt_enrt_rslt_stat_cd is null
3019 and pen2.per_in_ler_id = p_per_in_ler_id
3020 and pen2.enrt_cvg_thru_dt <> hr_api.g_eot
3021 and pen2.effective_end_date = hr_api.g_eot);
3022 --
3023 cursor c_enrt_rslt (p_prtt_enrt_rslt_id number) is
3024 select pen.effective_start_date,
3025 pen.effective_end_date,
3026 pen.object_version_number
3027 from ben_prtt_enrt_rslt_f pen
3028 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3029 and pen.prtt_enrt_rslt_stat_cd is null
3030 and pen.effective_end_date = (select pen2.effective_start_date - 1
3031 from ben_prtt_enrt_rslt_f pen2
3032 where pen2.enrt_cvg_thru_dt <> hr_api.g_eot
3033 and pen2.effective_end_date = hr_api.g_eot
3034 and pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3035 and pen2.prtt_enrt_rslt_stat_cd is null);
3036 --
3037 cursor c_prv_sspndd(p_pen_id number
3038 ,cp_per_in_ler_id number) is
3039 select prv.rowid,
3040 prv.prtt_rt_val_id,
3041 prv.object_version_number,
3042 prv.acty_base_rt_id,
3043 prv.rt_strt_dt,
3044 prv.rt_end_dt,
3045 prv.rt_val,
3046 prv.ann_rt_val,
3047 prv.acty_ref_perd_cd
3048 from ben_prtt_rt_val prv
3049 where prv.prtt_rt_val_stat_cd is null
3050 and prv.prtt_enrt_rslt_id = p_pen_id
3051 and prv.per_in_ler_id = cp_per_in_ler_id
3052 and prv.rt_strt_dt = -- for Unrestricted
3053 (select max(prv1.rt_strt_dt)
3054 from ben_prtt_rt_val prv1
3055 where prv1.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
3056 and prv1.per_in_ler_id = prv.per_in_ler_id
3057 and prv1.prtt_rt_val_stat_cd is null
3058 and prv1.acty_base_rt_id = prv.acty_base_rt_id);
3059 l_prv_sspndd c_prv_sspndd%rowtype;
3060
3061 cursor c_prv_rowid (p_rowid rowid) is
3062 select object_version_number
3063 from ben_prtt_rt_val
3064 where rowid = p_rowid;
3065
3066 cursor c_prv (p_prtt_enrt_rslt_id number,
3067 p_per_in_ler_id number) is
3068 select prv.prtt_rt_val_id,
3069 prv.object_version_number,
3070 prv.rt_end_dt,
3071 prv.rt_strt_dt,
3072 prv.per_in_ler_id,
3073 prv.prtt_enrt_rslt_id,
3074 pil.person_id
3075 from ben_prtt_rt_val prv,
3076 ben_per_in_ler pil
3077 where prv.per_in_ler_id = p_per_in_ler_id
3078 and prv.per_in_ler_id = pil.per_in_ler_id
3079 and prv.prtt_rt_val_stat_cd is null
3080 and prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3081 and prv.rt_end_dt <> hr_api.g_eot
3082 and prv.rt_strt_dt = (select max(rt_strt_dt)
3083 from ben_prtt_rt_val
3084 where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3085 and rt_end_dt <> hr_api.g_eot
3086 and prtt_rt_val_stat_cd is null)
3087 ;
3088 --
3089 l_pea c_pea%rowtype ;
3090 l_imp_inc_plan boolean:=false;
3091 l_dummy varchar2(30);
3092 l_pen Csr_prtt_enrt_rslt%rowtype;
3093 l_epe c_epe%rowtype;
3094 l_datetrack_mode varchar2(80);
3095 l_proc varchar2(80) := g_package||'.suspend_enrollment';
3096 l_last_place varchar2(100);
3097 l_rplcs_sspndd_rslt_id number;
3098 l_interim_epe_id number;
3099 l_enrt_bnft_id number;
3100 l_current_enrt_bnft_id number;
3101 l_interim_bnft_amt number;
3102 l_pea_effective_start_date date;
3103 l_pea_effective_end_date date;
3104 l_bnft_or_option_rstrctn_cd ben_pl_f.bnft_or_option_rstrctn_cd%type;
3105 -- for nocopy changes
3106 l_object_version_number number := p_object_version_number ;
3107 l_enrt_rslt c_enrt_rslt%rowtype;
3108 l_prv c_prv%rowtype;
3109 l_pre_interim boolean;
3110 l_effective_start_date date;
3111 l_effective_end_date date;
3112 --
3113 begin
3114 hr_utility.set_location ('Entering '|| l_proc, 10);
3115 --
3116 -- Check all the input parameters are not null
3117 --
3118 hr_api.mandatory_arg_error
3119 (p_api_name => l_proc
3120 ,p_argument => 'p_prtt_enrt_rslt_id'
3121 ,p_argument_value => p_prtt_enrt_rslt_id);
3122 hr_api.mandatory_arg_error
3123 (p_api_name => l_proc
3124 ,p_argument => 'p_effective_date'
3125 ,p_argument_value => p_effective_date);
3126 hr_api.mandatory_arg_error
3127 (p_api_name => l_proc
3128 ,p_argument => 'p_business_group_id'
3129 ,p_argument_value => p_business_group_id);
3130 hr_api.mandatory_arg_error
3131 (p_api_name => l_proc
3132 ,p_argument => 'p_object_version_number'
3133 ,p_argument_value => p_object_version_number);
3134 hr_api.mandatory_arg_error
3135 (p_api_name => l_proc
3136 ,p_argument => 'p_datetrack_mode'
3137 ,p_argument_value => p_datetrack_mode);
3138 --
3139 -- ** Open result cursor.
3140 l_last_place := 'Fetching record from ben_prtt_enrt_rslt_f';
3141 Open Csr_prtt_enrt_rslt;
3142 Fetch Csr_prtt_enrt_rslt Into l_pen;
3143 If Csr_prtt_enrt_rslt%NOTFOUND Then
3144 Close Csr_prtt_enrt_rslt;
3145 hr_utility.set_location('BEN_91493_PEN_NOT_FOUND', 55);
3146 fnd_message.set_name('BEN','BEN_91493_PEN_NOT_FOUND');
3147 fnd_message.raise_error;
3148 End If;
3149 Close Csr_prtt_enrt_rslt;
3150 --bug#4172569 - if the comp object is already suspended then return
3151 --if more than one required action item then this procedure is called more
3152 --than one time
3153 if l_pen.sspndd_flag = 'Y' then
3154 hr_utility.set_location('Comp Object already suspended',56);
3155 hr_utility.set_location('Leaving '||l_proc,57);
3156 return;
3157 end if;
3158 --
3159 if l_pen.prtt_enrt_rslt_id is not null then
3160 open c_current_bnft(l_pen.prtt_enrt_rslt_id,l_pen.elig_per_elctbl_chc_id,
3161 l_pen.bnft_ordr_num );
3162 fetch c_current_bnft into l_current_enrt_bnft_id;
3163 close c_current_bnft;
3164 end if;
3165 --
3166 -- ** Get corrected datetrack mode
3167 If (p_effective_date = l_pen.effective_start_date) Then
3168 l_datetrack_mode := 'CORRECTION';
3169 Else
3170 l_datetrack_mode := p_datetrack_mode;
3171 End If;
3172 --
3173 l_rplcs_sspndd_rslt_id := NULL;
3174 l_enrt_bnft_id:=null;
3175 l_interim_epe_id:=null;
3176 --
3177 --buG#3659657
3178 open c_interim (l_pen.prtt_enrt_rslt_id,l_pen.per_in_ler_id);
3179 fetch c_interim into l_rplcs_sspndd_rslt_id;
3180 close c_interim;
3181 --
3182 hr_utility.set_location ('Suspended result id'||l_pen.prtt_enrt_rslt_id,11);
3183 hr_utility.set_location ('interim result id'||l_rplcs_sspndd_rslt_id,12);
3184 if l_rplcs_sspndd_rslt_id is not null then
3185 l_pre_interim := true;
3186 else
3187 l_pre_interim := false;
3188 end if;
3189 -- Determine the interim id
3190 --
3191 if not l_pre_interim then
3192 --
3193 Determine_interim
3194 (p_elig_per_elctbl_chc_id => l_pen.elig_per_elctbl_chc_id
3195 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
3196 ,p_enrt_bnft_id => l_current_enrt_bnft_id /*ENH*/
3197 ,p_interim_elctbl_chc_id => l_interim_epe_id
3198 ,p_interim_enrt_bnft_id => l_enrt_bnft_id
3199 ,p_interim_enrt_rslt_id => l_rplcs_sspndd_rslt_id
3200 ,p_person_id => l_pen.person_id
3201 ,p_ler_id => l_pen.ler_id
3202 ,p_per_in_ler_id => l_pen.per_in_ler_id
3203 ,p_pl_id => l_pen.pl_id
3204 ,p_pgm_id => l_pen.pgm_id /* Bug 4256836 */
3205 ,p_pl_typ_id => l_pen.pl_typ_id
3206 ,p_oipl_id => l_pen.oipl_id
3207 ,p_pl_ordr_num => l_pen.pl_ordr_num
3208 ,p_oipl_ordr_num => l_pen.oipl_ordr_num
3209 ,p_plip_ordr_num => l_pen.plip_ordr_num /*ENH*/
3210 ,p_bnft_ordr_num => l_pen.bnft_ordr_num /*ENH*/
3211 ,p_business_group_id => p_business_group_id
3212 ,p_effective_date => p_effective_date
3213 ,p_interim_bnft_amt => l_interim_bnft_amt
3214 ,p_bnft_or_option_rstrctn_cd => l_bnft_or_option_rstrctn_cd
3215 );
3216 end if;
3217 hr_utility.set_location(' p_prtt_enrt_rslt_id '||p_prtt_enrt_rslt_id ,1234);
3218 hr_utility.set_location(' l_rplcs_sspndd_rslt_id '||l_rplcs_sspndd_rslt_id,1234);
3219 hr_utility.set_location(' l_pen.bnft_amt '||l_pen.bnft_amt,1234);
3220 hr_utility.set_location(' l_interim_bnft_amt '||l_interim_bnft_amt,1234);
3221 hr_utility.set_location(' l_current_enrt_bnft_id '||l_current_enrt_bnft_id,1234);
3222 hr_utility.set_location(' g_enb '||ben_election_information.g_enrt_bnft_id,1234);
3223 hr_utility.set_location(' l_enrt_bnft_id '||l_enrt_bnft_id,1234);
3224 hr_utility.set_location(' l_pen.elig_per_elctbl_chc_id '||l_pen.elig_per_elctbl_chc_id,1234);
3225 hr_utility.set_location(' l_interim_epe_id '||l_interim_epe_id,1234);
3226 --
3227 /*ENH
3228 if ( p_prtt_enrt_rslt_id<>nvl(l_rplcs_sspndd_rslt_id,-1) or
3229 nvl(ben_election_information.g_enrt_bnft_id,-1) <> nvl(l_enrt_bnft_id,-1) or
3230 -- nvl(l_current_enrt_bnft_id,-1)<>nvl(l_enrt_bnft_id,-1) or Bug 1886183
3231 nvl(l_pen.bnft_amt,0)<>nvl(l_interim_bnft_amt,0))
3232 and ( l_pen.elig_per_elctbl_chc_id <> nvl(l_interim_epe_id,-1) -- Bug 1886183
3233 or l_bnft_or_option_rstrctn_cd = 'BNFT') then --1998648
3234 -- or l_pen.dpnt_dsgn_cd is not null then
3235 */
3236 --
3237 if ( p_prtt_enrt_rslt_id <> nvl(l_rplcs_sspndd_rslt_id,-1) or
3238 nvl(l_current_enrt_bnft_id,-1)<>nvl(l_enrt_bnft_id,-1) or
3239 nvl(l_pen.bnft_amt,0)<>nvl(l_interim_bnft_amt,0)
3240 )
3241 and
3242 ( l_pen.elig_per_elctbl_chc_id <> nvl(l_interim_epe_id,-1) or
3243 ( l_pen.elig_per_elctbl_chc_id = nvl(l_interim_epe_id,-1) and
3244 l_bnft_or_option_rstrctn_cd = 'BNFT'
3245 ) or
3246 l_interim_epe_id is null -- No Interim Created But needs to be suspended
3247 )
3248 then
3249 --
3250 hr_utility.set_location('interim epe='||l_interim_epe_id,1066);
3251 hr_utility.set_location('suspended epe='||l_pen.elig_per_elctbl_chc_id,1066);
3252 --
3253 if l_interim_epe_id is not null then
3254 --
3255 -- the following two lines will cause a new result to be always written
3256 -- but this causes certifications not to be carried over because
3257 -- benactcm logic requires result_id not to change.
3258 -- also line below process_interim to null out the global too.
3259 --
3260 -- ben_election_information.g_elig_per_elctbl_chc_id:=
3261 -- l_pen.elig_per_elctbl_chc_id;
3262 --Bug 4422667
3263 g_interim_flag := 'Y';
3264 --
3265 -- bug 6337803
3266 g_sspnded_rslt_id := p_prtt_enrt_rslt_id;
3267 hr_utility.set_location('p_prtt_enrt_rslt_id '|| p_prtt_enrt_rslt_id ,1212);
3268 hr_utility.set_location('g_sspnded_rslt_id '|| g_sspnded_rslt_id ,1212);
3269 process_interim
3270 (p_elig_per_elctbl_chc_id => l_interim_epe_id
3271 ,p_prtt_enrt_rslt_id => l_rplcs_sspndd_rslt_id
3272 ,p_enrt_bnft_id => l_enrt_bnft_id
3273 ,p_bnft_amt => l_interim_bnft_amt
3274 ,p_business_group_id => p_business_group_id
3275 ,p_effective_date => p_effective_date
3276 ,p_enrt_mthd_cd => l_pen.enrt_mthd_cd );
3277 --
3278 g_interim_flag := 'N';
3279 -- 6337803 unsetting the pen id to be suspended
3280 ben_sspndd_enrollment.g_sspnded_rslt_id := null;
3281
3282 --
3283 -- ben_election_information.g_elig_per_elctbl_chc_id:=null;
3284
3285 if p_prtt_enrt_rslt_id=l_rplcs_sspndd_rslt_id then
3286 --
3287 -- get the updated ovn
3288 --
3289 open c_new_ovn;
3290 fetch c_new_ovn into p_object_version_number;
3291 close c_new_ovn;
3292 end if;
3293 End if;
3294 -- ** Update suspend flag on prtt_enrt_rslt_f to Y
3295 l_last_place := 'Calling update_sspndd_flag to update sspndd flag';
3296 open c_new_ovn;
3297 fetch c_new_ovn into p_object_version_number;
3298 close c_new_ovn;
3299 update_sspndd_flag
3300 (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
3301 ,p_effective_date => p_effective_date
3302 ,p_business_group_id => p_business_group_id
3303 ,p_enrt_cvg_strt_dt => l_pen.enrt_cvg_strt_dt
3304 ,p_sspndd_flag => 'Y'
3305 ,p_RPLCS_SSPNDD_RSLT_ID => l_rplcs_sspndd_rslt_id
3306 ,p_object_version_number => p_object_version_number
3307 ,p_datetrack_mode => l_datetrack_mode
3308 ,p_person_id => l_pen.person_id
3309 );
3310
3311 --
3312 -- Delete element entry and De-link prv
3313 --
3314 open c_prv_sspndd(p_prtt_enrt_rslt_id,l_pen.per_in_ler_id);
3315 loop
3316 fetch c_prv_sspndd into l_prv_sspndd;
3317 if c_prv_sspndd%notfound then
3318 exit;
3319 end if;
3320
3321 ben_element_entry.end_enrollment_element
3322 (p_business_group_id => p_business_group_id
3323 ,p_person_id => l_pen.person_id
3324 ,p_enrt_rslt_id => p_prtt_enrt_rslt_id
3325 ,p_acty_ref_perd => l_prv_sspndd.acty_ref_perd_cd
3326 ,p_element_link_id => null
3327 ,p_prtt_rt_val_id => l_prv_sspndd.prtt_rt_val_id
3328 ,p_rt_end_date => l_prv_sspndd.rt_strt_dt-1
3329 ,p_effective_date => l_prv_sspndd.rt_strt_dt
3330 ,p_dt_delete_mode => null
3331 ,p_acty_base_rt_id => l_prv_sspndd.acty_base_rt_id
3332 ,p_amt => l_prv_sspndd.rt_val
3333 );
3334 --
3335 --fetch prv ovn again just incase prv got updated in the above call
3336 --
3337 open c_prv_rowid(l_prv_sspndd.rowid);
3338 fetch c_prv_rowid into l_prv_sspndd.object_version_number;
3339 close c_prv_rowid;
3340
3341 ben_prtt_rt_val_api.update_prtt_rt_val
3342 (p_validate => false
3343 ,p_business_group_id => p_business_group_id
3344 ,p_prtt_rt_val_id => l_prv_sspndd.prtt_rt_val_id
3345 ,p_element_entry_value_id => null
3346 ,p_object_version_number => l_prv_sspndd.object_version_number
3347 ,p_effective_date => l_prv_sspndd.rt_strt_dt
3348 );
3349
3350 end loop;
3351 close c_prv_sspndd;
3352
3353
3354 ben_provider_pools.remove_bnft_prvdd_ldgr
3355 (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
3356 ,p_effective_date => p_effective_date
3357 ,p_business_group_id => p_business_group_id
3358 ,p_validate => FALSE
3359 ,p_datetrack_mode => 'ZAP'
3360 );
3361
3362
3363 l_last_place := 'Calling the Post-Result RCO';
3364 If p_post_rslt_flag = 'Y' Then
3365 -- Bug 4622534
3366 if g_cfw_dpnt_flag = 'N' THEN
3367 --
3368 ben_prtt_enrt_result_api.multi_rows_edit
3369 (p_person_id => l_pen.person_id
3370 ,p_effective_date => p_effective_date
3371 ,p_business_group_id => p_business_group_id
3372 ,p_pgm_id => l_pen.pgm_id
3373 ,p_per_in_ler_id => l_pen.per_in_ler_id
3374 );
3375 ben_proc_common_enrt_rslt.process_post_results
3376 (p_person_id => l_pen.person_id
3377 ,p_enrt_mthd_cd => l_pen.enrt_mthd_cd
3378 ,p_effective_date => p_effective_date
3379 ,p_business_group_id => p_business_group_id
3380 ,p_per_in_ler_id => l_pen.per_in_ler_id
3381 );
3382 end if;
3383 --
3384 else
3385 -- check if it's an imputed income plan
3386 open c_pl(l_pen.pl_id);
3387 fetch c_pl into l_dummy;
3388 if c_pl%FOUND then
3389 l_imp_inc_plan := true;
3390 end if;
3391 close c_pl;
3392 if l_imp_inc_plan then
3393 ben_det_imputed_income.p_comp_imputed_income
3394 (p_person_id => l_pen.person_id
3395 ,p_enrt_mthd_cd => l_pen.enrt_mthd_cd
3396 ,p_per_in_ler_id => l_pen.per_in_ler_id
3397 ,p_effective_date => p_effective_date
3398 ,p_business_group_id => p_business_group_id
3399 ,p_ctrlm_fido_call => false
3400 ,p_validate => false);
3401 end if;
3402 End if;
3403 --
3404 -- reopen the interim result and the rates after suspending to avoid
3405 --element entry error
3406
3407 if l_pre_interim then
3408 --
3409 open c_enrt_rslt(l_rplcs_sspndd_rslt_id);
3410 fetch c_enrt_rslt into l_enrt_rslt;
3411 close c_enrt_rslt;
3412 --
3413 if l_enrt_rslt.effective_start_date is not null then
3414 ben_prtt_enrt_result_api.delete_prtt_enrt_result
3415 (p_validate => false,
3416 p_prtt_enrt_rslt_id => l_rplcs_sspndd_rslt_id,
3417 p_effective_start_date => l_effective_start_date,
3418 p_effective_end_date => l_effective_end_date,
3419 p_object_version_number => l_enrt_rslt.object_version_number,
3420 p_effective_date => l_enrt_rslt.effective_end_date,
3421 p_datetrack_mode => hr_api.g_future_change,
3422 p_multi_row_validate => FALSE);
3423 --
3424 open c_prv(l_rplcs_sspndd_rslt_id, l_pen.per_in_ler_id);
3425 loop
3426 fetch c_prv into l_prv;
3427 if c_prv%notfound then
3428 exit;
3429 end if;
3430 ben_prtt_rt_val_api.update_prtt_rt_val
3431 (p_validate => FALSE
3432 ,p_prtt_rt_val_id => l_prv.prtt_rt_val_id
3433 ,p_object_version_number => l_prv.object_version_number
3434 ,p_rt_end_dt => hr_api.g_eot
3435 ,p_prtt_rt_val_stat_cd => null
3436 ,p_ended_per_in_ler_id => null
3437 ,p_person_id => l_prv.person_id
3438 ,p_business_group_id => p_business_group_id
3439 ,p_effective_date => p_effective_date);
3440 end loop;
3441 close c_prv;
3442 end if;
3443 --
3444 end if;
3445 --
3446 elsif l_pen.elig_per_elctbl_chc_id = nvl(l_interim_epe_id,-1) then
3447 --
3448 open c_pea(p_prtt_enrt_rslt_id) ;
3449 loop
3450 fetch c_pea into l_pea ;
3451 if c_pea%notfound then exit ;
3452 end if;
3453 --
3454 hr_utility.set_location('Updating the Required Flag to No ',5);
3455 hr_utility.set_location('Before Entering ben_prtt_enrt_actn_api.update_prtt_enrt_actn ' ,10);
3456 ben_prtt_enrt_actn_api.update_prtt_enrt_actn
3457 ( p_prtt_enrt_actn_id => l_pea.prtt_enrt_actn_id
3458 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
3459 ,p_rslt_object_version_number => l_pea.pen_object_version_number
3460 ,p_actn_typ_id => l_pea.actn_typ_id
3461 ,p_rqd_flag => 'N'
3462 --START OHSU
3463 -- ,p_effective_date => p_effective_date
3464 ,p_effective_date => l_pea.pea_effective_date
3465 --END OHSU
3466 ,p_post_rslt_flag => p_post_rslt_flag
3467 ,p_business_group_id => p_business_group_id
3468 ,p_effective_start_date => l_pea_effective_start_date
3469 ,p_effective_end_date => l_pea_effective_end_date
3470 ,p_object_version_number => l_pea.pea_object_version_number
3471 ,p_datetrack_mode => hr_api.g_correction
3472 );
3473 hr_utility.set_location('After ben_prtt_enrt_actn_api.update_prtt_enrt_actn ',20);
3474 --
3475 end loop ;
3476 close c_pea ;
3477 --
3478 End If;
3479 hr_utility.set_location('Leaving:'||l_proc, 10);
3480 Exception
3481 When others then
3482 hr_utility.set_location('ERROR '||l_proc, 96);
3483 rpt_error(p_proc => l_proc, p_last_action => l_last_place);
3484 -- for nocopy changes
3485 p_object_version_number := l_object_version_number ;
3486 fnd_message.raise_error;
3487 End suspend_enrollment;
3488 --
3489 --
3490 -- ==========================================================================+
3491 -- << Unsuspend Enrollment >> +
3492 -- +
3493 -- ==========================================================================+
3494 --
3495 procedure unsuspend_enrollment
3496 (p_prtt_enrt_rslt_id in number
3497 ,p_effective_date in date
3498 ,p_per_in_ler_id in number
3499 ,p_post_rslt_flag in varchar2
3500 ,p_business_group_id in number
3501 ,p_object_version_number in out nocopy number
3502 ,p_datetrack_mode in varchar2
3503 ,p_called_from in varchar2 default 'BENSUENR'
3504 ,p_cmpltd_dt in date default null
3505 ) is
3506 --
3507 l_proc varchar2(80) := g_package||'.unsuspend_enrollment';
3508 l_last_place varchar2(80);
3509 l_datetrack_mode varchar2(80);
3510 l_interim_del Boolean default FALSE;
3511 l_interim_upd Boolean default FALSE;
3512 l_ee_end_date date;
3513 l_ler_id number;
3514 l_per_in_ler_id number;
3515 l_lf_evt_ocrd_dt date;
3516 l_lee_rsn_id number;
3517 l_enrt_perd_id number;
3518 l_unsspnd_enrt_cd varchar2(30);
3519 l_rec_rt_strt_dt date;
3520 l_rec_rt_end_dt date;
3521 l_elig_per_elctbl_chc_id number;
3522 l_update_dates boolean := false;
3523 --
3524 l_enrt_cvg_strt_dt date;
3525 l_enrt_cvg_strt_dt_cd varchar2(30);
3526 l_enrt_cvg_strt_dt_rl number;
3527 l_rt_strt_dt date;
3528 l_rt_strt_dt_cd varchar2(30);
3529 l_rt_strt_dt_rl number;
3530 l_enrt_cvg_end_dt date;
3531 l_enrt_cvg_end_dt_cd varchar2(30);
3532 l_enrt_cvg_end_dt_rl number;
3533 l_rt_end_dt date;
3534 l_rt_end_dt_cd varchar2(30);
3535 l_rt_end_dt_rl number;
3536 --
3537 l_dpnt_cvg_strt_dt date;
3538 l_dpnt_cvg_end_dt date;
3539 l_dpnt_cvg_strt_dt_cd varchar2(30);
3540 l_dpnt_cvg_strt_dt_rl number;
3541 l_dpnt_cvg_end_dt_cd varchar2(30);
3542 l_dpnt_cvg_end_dt_rl number;
3543 l_decr_bnft_prvdr_pool_id number;
3544 l_bnft_prvdd_ldgr_id number;
3545 --
3546 -- Added for Bug fix 2689926
3547 --
3548 l_prtt_enrt_rslt_id number ;
3549 l_prtt_rt_val_id number ;
3550 l_acty_ref_perd_cd varchar2(30) ;
3551 l_acty_base_rt_id number ;
3552 l_rt_strt_dt1 date ;
3553 l_rt_val number ;
3554 l_element_type_id number ;
3555 --
3556 -- End Bug fix 2689926
3557 --
3558 l_carry_forward varchar2(1) := 'N';
3559 -- for nocopy changes
3560 l_object_version_number number := p_object_version_number ;
3561 --
3562 -- Added for bug
3563 --
3564 l_epe ben_epe_shd.g_rec_type;
3565 --
3566 Cursor Csr_prtt_enrt_rslt (c_rslt_id Number) is
3567 Select pen.rplcs_sspndd_rslt_id
3568 ,pen.prtt_enrt_rslt_id
3569 ,pen.per_in_ler_id
3570 ,pen.enrt_cvg_strt_dt
3571 ,pen.enrt_cvg_thru_dt
3572 ,pen.person_id
3573 ,pen.pgm_id
3574 ,pen.sspndd_flag
3575 ,pen.effective_start_date
3576 ,pen.effective_end_date
3577 ,pen.enrt_mthd_cd
3578 ,pen.object_version_number
3579 ,pen.pl_id
3580 ,pen.oipl_id
3581 ,pen.ptip_id
3582 ,pln.ENRT_PL_OPT_FLAG
3583 ,pen.business_group_id
3584 ,'USEEFD' calc_cvg_strt_dt_cd
3585 ,'USE1BSEFD' calc_cvg_end_dt_cd
3586 From ben_prtt_enrt_rslt_f pen
3587 ,ben_pl_f pln
3588 Where pen.prtt_enrt_rslt_id = c_rslt_id
3589 And pen.business_group_id = p_business_group_id
3590 and pen.prtt_enrt_rslt_stat_cd is null
3591 And p_effective_date between
3592 pen.effective_start_date and
3593 pen.effective_end_date
3594 And pen.pl_id = pln.pl_id
3595 And pen.business_group_id = pln.business_group_id
3596 And p_effective_date between
3597 pln.effective_start_date and
3598 pln.effective_end_date
3599 ;
3600 cursor c_pl(p_pl_id number) is
3601 select 'x' from ben_pl_f pl
3602 where pl.pl_id = p_pl_id
3603 and pl.SUBJ_TO_IMPTD_INCM_TYP_CD is not null
3604 and p_effective_date between
3605 pl.effective_start_date and pl.effective_end_date;
3606 l_imp_inc_plan boolean:=false;
3607 l_dummy varchar2(30);
3608 l_pen csr_prtt_enrt_rslt%rowtype;
3609 l_interim csr_prtt_enrt_rslt%rowtype;
3610 l_cvg_thru_dt date;
3611 --
3612 cursor c_choice_info(c_prtt_enrt_rslt_id number) is
3613 select epe.elig_per_elctbl_chc_id,
3614 pel.lee_rsn_id,
3615 pel.enrt_perd_id,
3616 --START Bug 2958032
3617 epe.prtt_enrt_rslt_id,
3618 epe.object_version_number
3619 --END Bug 2958032
3620 from ben_prtt_enrt_rslt_f pen,
3621 ben_pil_elctbl_chc_popl pel,
3622 ben_per_in_ler pil,
3623 ben_elig_per_elctbl_chc epe
3624 where pen.prtt_enrt_rslt_id=c_prtt_enrt_rslt_id
3625 and p_effective_date between
3626 pen.effective_start_date and pen.effective_end_date
3627 and pen.person_id=pil.person_id
3628 and epe.pl_id=pen.pl_id
3629 and nvl(epe.oipl_id,-1)=nvl(pen.oipl_id,-1)
3630 and nvl(epe.pgm_id,-1)=nvl(pen.pgm_id,-1)
3631 and epe.per_in_ler_id = l_per_in_ler_id
3632 and epe.business_group_id=p_business_group_id
3633 -- and epe.elctbl_flag = 'Y' -- Bug 2958032 CF BUG 4064635
3634 and pel.pil_elctbl_chc_popl_id=epe.pil_elctbl_chc_popl_id
3635 and pel.business_group_id=p_business_group_id
3636 and pil.per_in_ler_id=epe.per_in_ler_id
3637 and pil.business_group_id=epe.business_group_id
3638 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
3639 and pen.prtt_enrt_rslt_stat_cd is null
3640 ;
3641 cursor c_enrt(p_elig_per_elctbl_chc_id number,
3642 p_acty_base_rt_id number) is
3643 select ecr.*
3644 from ben_enrt_rt ecr
3645 where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
3646 and acty_base_rt_id = p_acty_base_rt_id;
3647 l_enrt_rt c_enrt%rowtype;
3648
3649 --
3650 -- restict prv's to update to those which are not ended
3651 --
3652 cursor c_prv (c_prtt_enrt_rslt_id number) is
3653 select prv.*
3654 ,abr.input_value_id
3655 ,abr.element_type_id
3656 from ben_prtt_rt_val prv,
3657 ben_acty_base_rt_f abr
3658 where prv.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
3659 and prv.per_in_ler_id = l_per_in_ler_id
3660 and prv.rt_strt_dt <= prv.rt_end_dt
3661 -- and prv.rt_end_dt=hr_api.g_eot
3662 and prv.business_group_id = p_business_group_id
3663 and prv.prtt_rt_val_stat_cd is null
3664 and abr.acty_base_rt_id=prv.acty_base_rt_id
3665 and abr.business_group_id = p_business_group_id
3666 and p_effective_date between
3667 abr.effective_start_date and abr.effective_end_date
3668 ;
3669 --
3670 -- bug 2330694 - to delete/end-date premiums corresponding to interim
3671 -- coverage once this the interim is deleted / coverage ended.
3672 --
3673 --3278908 APP 07115 Errir
3674 l_ppe_effective_date date;
3675 --
3676 ----
3677 /* bug 3666347 reverted the fix
3678 cursor c_ppe (c_prtt_enrt_rslt_id number) is
3679 select ppe.prtt_prem_id,
3680 ppe.object_version_number
3681 from ben_prtt_prem_f ppe,
3682 ben_actl_prem_f apr
3683 where ppe.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
3684 and ppe.per_in_ler_id = l_per_in_ler_id
3685 and ppe.business_group_id = p_business_group_id
3686 and ppe.actl_prem_id = apr.actl_prem_id
3687 and apr.business_group_id = p_business_group_id
3688 and l_ppe_effective_date between -- p_effective_date between ----3278908
3689 apr.effective_start_date and apr.effective_end_date
3690 --Added for 3278908
3691 and l_ppe_effective_date between
3692 ppe.effective_start_date and ppe.effective_end_date
3693 ;
3694 */
3695 l_ppe_effective_start_date date;
3696 l_ppe_effective_end_date date;
3697 -- l_ppe_effective_date date;
3698 l_ppe_datetrack_mode varchar2(80);
3699 --
3700 -- end fix 2330694
3701 --
3702 cursor c_ee (p_element_entry_value_id number,p_rt_strt_dt date) is
3703 select pev.effective_end_date
3704 from pay_element_entry_values_f pev,
3705 pay_element_entries_f pee
3706 where pev.element_entry_value_id = p_element_entry_value_id
3707 and p_rt_strt_dt between
3708 pev.effective_start_date and pev.effective_end_date
3709 and pee.element_entry_id = pev.element_entry_id
3710 and p_rt_strt_dt between
3711 pee.effective_start_date and pee.effective_end_date
3712 ;
3713 Cursor c_dpnt (c_prtt_enrt_rslt_id number) is
3714 select ecd.elig_cvrd_dpnt_id
3715 ,ecd.effective_start_date
3716 ,ecd.effective_end_date
3717 ,ecd.cvg_strt_dt
3718 ,ecd.cvg_thru_dt
3719 ,ecd.object_version_number
3720 From ben_elig_cvrd_dpnt_f ecd,
3721 ben_per_in_ler pil
3722 Where ecd.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
3723 and ecd.cvg_strt_dt is not null
3724 and ecd.cvg_thru_dt = hr_api.g_eot
3725 and ecd.business_group_id = p_business_group_id
3726 and p_effective_date between
3727 ecd.effective_start_date and ecd.effective_end_date
3728 and pil.per_in_ler_id=ecd.per_in_ler_id
3729 and pil.business_group_id=ecd.business_group_id
3730 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
3731 ;
3732 --
3733 cursor c_per_in_ler_info is
3734 select pil.lf_evt_ocrd_dt,
3735 pil.ler_id
3736 from ben_per_in_ler pil
3737 where pil.per_in_ler_id=l_per_in_ler_id and
3738 pil.business_group_id=p_business_group_id;
3739 --
3740 -- RCHASE added PLIP join for unsspnd enrt cd setup at plip level
3741 cursor c_unsspnd_enrt_cd is
3742 select nvl(lbr.unsspnd_enrt_cd,
3743 nvl(pl.unsspnd_enrt_cd, plip.unsspnd_enrt_cd))
3744 from ben_ler_bnft_rstrn_f lbr,
3745 ben_pl_f pl,
3746 ben_plip_f plip
3747 where
3748 pl.pl_id=l_pen.pl_id
3749 and plip.pl_id(+)=pl.pl_id
3750 and nvl(plip.pgm_id,l_pen.pgm_id)=l_pen.pgm_id
3751 and pl.business_group_id = p_business_group_id
3752 and nvl(l_lf_evt_ocrd_dt,p_effective_date)
3753 between pl.effective_start_date
3754 and pl.effective_end_date
3755 and nvl(l_lf_evt_ocrd_dt,p_effective_date)
3756 between plip.effective_start_date(+)
3757 and plip.effective_end_date(+)
3758 -- get ler_bnft_rstrn_f if exists
3759 and lbr.pl_id(+)=pl.pl_id
3760 and lbr.ler_id(+)=l_ler_id
3761 and lbr.business_group_id(+) = p_business_group_id
3762 and nvl(l_lf_evt_ocrd_dt,p_effective_date)
3763 between lbr.effective_start_date(+)
3764 and lbr.effective_end_date(+)
3765 ;
3766 --
3767 --
3768 cursor c_enrt_rt (p_prtt_rt_val_id number) is
3769 select decr_bnft_prvdr_pool_id
3770 from ben_enrt_rt
3771 where prtt_rt_val_id = p_prtt_rt_val_id;
3772 --
3773 --Bug 2958032 Get the benefit record of the unsuspended enrollment result
3774 --Update the enb to remove penid from the interim row
3775 cursor c_enb(p_prtt_enrt_rslt_id number,
3776 p_elig_per_elctbl_chc_id number) is
3777 select enb.enrt_bnft_id,
3778 enb.object_version_number
3779 from ben_enrt_bnft enb
3780 where enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
3781 and enb.prtt_enrt_rslt_id is not null
3782 and enb.prtt_enrt_rslt_id <> p_prtt_enrt_rslt_id ;
3783 --
3784 -- update the enb if there is not pen_id or pen_id is not the right one
3785 cursor c_enrt_bnft(p_prtt_enrt_rslt_id number,
3786 p_elig_per_elctbl_chc_id number,
3787 p_effective_date date) is
3788 select enb.enrt_bnft_id,
3789 enb.object_version_number
3790 from ben_prtt_enrt_rslt_f pen,
3791 ben_enrt_bnft enb
3792 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3793 and p_effective_date between pen.effective_start_date and pen.effective_end_date
3794 and pen.bnft_ordr_num = enb.ordr_num
3795 and enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
3796 and nvl(enb.prtt_enrt_rslt_id,p_prtt_enrt_rslt_id) <> p_prtt_enrt_rslt_id
3797 and pen.prtt_enrt_rslt_stat_cd is null;
3798 --
3799 -- bug#3202455 - determine whether unsuspend is on account of some user error
3800 cursor c_previous_status is
3801 select null
3802 from ben_prtt_enrt_rslt_f pen
3803 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3804 and pen.sspndd_flag = 'N'
3805 and pen.per_in_ler_id <> l_per_in_ler_id
3806 and pen.prtt_enrt_rslt_stat_cd is null;
3807 --
3808 -- 6054988 : Check if any elections have been made in the current pil.
3809 --
3810 cursor chk_elcn_dt_in_pel is
3811 SELECT 'x'
3812 FROM ben_pil_elctbl_chc_popl popl, ben_prtt_enrt_rslt_f pen
3813 WHERE popl.per_in_ler_id = l_per_in_ler_id
3814 AND popl.elcns_made_dt IS NULL
3815 AND popl.dflt_asnd_dt IS NULL
3816 AND popl.pgm_id = pen.pgm_id
3817 AND pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3818 AND pen.per_in_ler_id = popl.per_in_ler_id
3819 AND prtt_enrt_rslt_stat_cd IS NULL;
3820 l_var varchar2(30);
3821 --
3822 -- End 6054988
3823 --
3824 l_dummy_number number;
3825 l_unsusp_cvg_start_date date default null ;
3826 --
3827 --START Bug 2958032
3828 l_epe_prtt_enrt_rslt_id number(15);
3829 l_epe_object_version_number number(9);
3830 l_enrt_bnft_id number(15);
3831 l_enb_object_version_number number(15);
3832 l_previous_no_sspn boolean := false ;
3833 --END Bug 2958032
3834 Begin
3835 hr_utility.set_location('Entering:'||l_proc, 05);
3836 hr_utility.set_location('p_prtt_enrt_rslt_id:'||to_char(p_prtt_enrt_rslt_id), 05);
3837 hr_utility.set_location('p_per_in_ler_id:'||p_per_in_ler_id, 05);
3838
3839 --
3840 -- Check all the input parameters are not null
3841 --
3842 hr_api.mandatory_arg_error(p_api_name => l_proc,
3843 p_argument => 'p_prtt_enrt_rslt_id',
3844 p_argument_value => p_prtt_enrt_rslt_id);
3845 --
3846 hr_api.mandatory_arg_error(p_api_name => l_proc,
3847 p_argument => 'p_effective_date',
3848 p_argument_value => p_effective_date);
3849 --
3850 hr_api.mandatory_arg_error(p_api_name => l_proc,
3851 p_argument => 'p_business_group_id',
3852 p_argument_value => p_business_group_id);
3853 --
3854 hr_api.mandatory_arg_error(p_api_name => l_proc,
3855 p_argument => 'p_object_version_number',
3856 p_argument_value => p_object_version_number);
3857 --
3858 hr_api.mandatory_arg_error(p_api_name => l_proc,
3859 p_argument => 'p_datetrack_mode',
3860 p_argument_value => p_datetrack_mode);
3861 --
3862 l_last_place := 'Fetching record from ben_prtt_enrt_rslt_f';
3863 --
3864 -- Get the needed result info
3865 --
3866 Open Csr_prtt_enrt_rslt(p_prtt_enrt_rslt_id);
3867 Fetch Csr_prtt_enrt_rslt Into l_pen;
3868 If Csr_prtt_enrt_rslt%NOTFOUND Then
3869 Close Csr_prtt_enrt_rslt;
3870 hr_utility.set_location('BEN_91493_PEN_NOT_FOUND ID:'|| to_char(p_prtt_enrt_rslt_id), 65);
3871 fnd_message.set_name('BEN','BEN_91493_PEN_NOT_FOUND');
3872 fnd_message.raise_error;
3873 End If;
3874 Close Csr_prtt_enrt_rslt;
3875 --
3876 if p_per_in_ler_id is not null then
3877 --
3878 l_per_in_ler_id := p_per_in_ler_id;
3879 --
3880 else
3881 --
3882 l_per_in_ler_id := l_pen.per_in_ler_id;
3883 --
3884 end if;
3885 if p_per_in_ler_id <> l_pen.per_in_ler_id then
3886 l_carry_forward := 'Y';
3887 end if;
3888 hr_utility.set_location('l_carry_forward '||l_carry_forward, 10);
3889 --
3890 -- Get the info on the per_in_ler if it exists
3891 --
3892 hr_utility.set_location('past Csr_prtt_enrt_rslt', 10);
3893 hr_utility.set_location('per in ler id '||l_per_in_ler_id, 10);
3894
3895 open c_per_in_ler_info;
3896 fetch c_per_in_ler_info into
3897 l_lf_evt_ocrd_dt,
3898 l_ler_id;
3899 close c_per_in_ler_info;
3900 hr_utility.set_location('past c_per_in_ler_info', 10);
3901 --
3902 --bug#3202455
3903 open c_previous_status;
3904 fetch c_previous_status into l_dummy;
3905 if c_previous_status%found then
3906 l_previous_no_sspn := true;
3907 hr_utility.set_location('l_previous_no_sspn',11);
3908 end if;
3909 close c_previous_status;
3910 If (nvl(l_pen.sspndd_flag, 'X') <> 'N' ) then
3911 --
3912 -- ======================================================================
3913 --
3914 -- Case 1,2,3 : Suspended enrollment is started
3915 --
3916 -- Case 1: Suspended enrollment coverage started and interim started as
3917 -- well.
3918 -- Case 2: Suspended enrollment coverage started but interim not.
3919 --
3920 -- Case 3: Suspended enrollment started and it has not interim cvg.
3921 --
3922 -- Case 4,5,6 : Suspended enrollment is not started yet
3923 --
3924 -- Case 4: Suspended enrollment coverage is not started yet but interim
3925 -- started.
3926 -- Case 5: Suspended enrollment coverage is not started yet and interim
3927 -- not started neither.
3928 -- Case 6: Suspended enrollment is not started yet and it has not
3929 -- interim cvg.
3930 --
3931 -- ======================================================================
3932 --
3933 -- This next section decides what to do to the interim enrollment
3934 --
3935 If (l_pen.rplcs_sspndd_rslt_id is not NULL) then
3936 Open Csr_prtt_enrt_rslt(l_pen.rplcs_sspndd_rslt_id);
3937 Fetch Csr_prtt_enrt_rslt Into l_interim;
3938 --
3939 -- To allow continuing the enrollment process, incase if the user deletes interim.
3940 --
3941 --
3942 If Csr_prtt_enrt_rslt%FOUND Then
3943 If (p_effective_date >= l_interim.enrt_cvg_strt_dt) then
3944 -- ** Case 1+4: suspended and interim both coverage been started
3945 l_interim_upd := TRUE;
3946 Else
3947 -- ** Case 2+5: suspended started but interim not.
3948 l_interim_del := TRUE;
3949 End if;
3950 End if;
3951 Close Csr_prtt_enrt_rslt;
3952 End if;
3953 --
3954 -- This section is handle interim coverage. For delete, delete enrollment
3955 -- will handle it automatically, but for update, we need need to prtt_rate
3956 -- val and dpnt_cvg rate/coverage end date handle correctly.
3957 --
3958 If (l_interim_del) then
3959 l_last_place := 'Calling Delete Enrollment';
3960 ben_prtt_enrt_result_api.delete_enrollment
3961 (P_VALIDATE => FALSE
3962 ,P_PRTT_ENRT_RSLT_ID => l_interim.prtt_enrt_rslt_id
3963 ,p_per_in_ler_id => p_per_in_ler_id
3964 ,P_BUSINESS_GROUP_ID => p_business_group_id
3965 ,P_EFFECTIVE_START_DATE => l_interim.effective_start_date
3966 ,P_EFFECTIVE_END_DATE => l_interim.effective_end_date
3967 ,P_OBJECT_VERSION_NUMBER => l_interim.object_version_number
3968 ,P_EFFECTIVE_DATE => p_effective_date
3969 ,P_DATETRACK_MODE => hr_api.g_delete
3970 ,P_MULTI_ROW_VALIDATE => FALSE
3971 ,p_source => 'bensuenr'
3972 );
3973 End if;
3974 If (l_interim_upd) then
3975 --
3976 -- These dated need to be recalculated bases on the Unsuspend code used.
3977 -- Interim coverage end date.
3978 --
3979 if l_carry_forward = 'N' then --CFW
3980 open c_unsspnd_enrt_cd;
3981 fetch c_unsspnd_enrt_cd into l_unsspnd_enrt_cd;
3982 if c_unsspnd_enrt_cd%notfound or l_unsspnd_enrt_cd is null then
3983 l_unsspnd_enrt_cd:='ACD';
3984 end if;
3985 close c_unsspnd_enrt_cd;
3986 else
3987 l_unsspnd_enrt_cd:='ACD';
3988 end if;
3989 hr_utility.set_location('past c_unsspnd_enrt_cd', 10);
3990 --
3991 -- need choice info to recalc dates
3992 --
3993 open c_choice_info(p_prtt_enrt_rslt_id);
3994 fetch c_choice_info into
3995 l_elig_per_elctbl_chc_id,
3996 l_lee_rsn_id,
3997 l_enrt_perd_id,
3998 --START Bug 2958032
3999 l_epe_prtt_enrt_rslt_id,
4000 l_epe_object_version_number ;
4001 --END Bug 2958032
4002 if c_choice_info%notfound then
4003 --
4004 hr_utility.set_location('BEN_91457_ELCTBL_CHC_NOT_FOUND rslt id:'||
4005 to_char(p_prtt_enrt_rslt_id), 75);
4006 fnd_message.set_name('BEN','BEN_91457_ELCTBL_CHC_NOT_FOUND');
4007 fnd_message.set_token('ID', 'NA');
4008 fnd_message.set_token('PROC', '2:'||l_proc);
4009 fnd_message.raise_error;
4010 end if;
4011 close c_choice_info;
4012 --
4013 If l_unsspnd_enrt_cd = 'ACD' then
4014 l_unsusp_cvg_start_date := p_effective_date;
4015 hr_utility.set_location('l_pen.enrt_cvg_strt_dt:'||l_pen.enrt_cvg_strt_dt, 60);
4016 Elsif l_unsspnd_enrt_cd = 'UEECSD' then
4017 --
4018 -- Use existing enrollments coverage start Date
4019 --
4020 l_unsusp_cvg_start_date := l_pen.enrt_cvg_strt_dt ;
4021 --
4022 elsif l_unsspnd_enrt_cd='RUCDECSDC' then
4023 --
4024 -- Recalc using enrt_cvg_strt_dt_cd
4025 -- Substitute p_effective_date for the lf_evt_ocrd_dt
4026 --
4027 ben_determine_date.rate_and_coverage_dates
4028 (p_which_dates_cd => 'C'
4029 ,p_date_mandatory_flag => 'Y'
4030 ,p_compute_dates_flag => 'Y'
4031 ,p_business_group_id => p_business_group_id
4032 ,P_PER_IN_LER_ID => p_per_in_ler_id
4033 ,P_PERSON_ID => l_pen.person_id
4034 ,P_PGM_ID => l_pen.pgm_id
4035 ,P_PL_ID => l_pen.pl_id
4036 ,P_OIPL_ID => l_pen.oipl_id
4037 ,P_LEE_RSN_ID => l_lee_rsn_id
4038 ,P_ENRT_PERD_ID => l_enrt_perd_id
4039 ,p_enrt_cvg_strt_dt => l_enrt_cvg_strt_dt
4040 ,p_enrt_cvg_strt_dt_cd => l_enrt_cvg_strt_dt_cd
4041 ,p_enrt_cvg_strt_dt_rl => l_enrt_cvg_strt_dt_rl
4042 ,p_rt_strt_dt => l_rt_strt_dt
4043 ,p_rt_strt_dt_cd => l_rt_strt_dt_cd
4044 ,p_rt_strt_dt_rl => l_rt_strt_dt_rl
4045 ,p_enrt_cvg_end_dt => l_enrt_cvg_end_dt
4046 ,p_enrt_cvg_end_dt_cd => l_enrt_cvg_end_dt_cd
4047 ,p_enrt_cvg_end_dt_rl => l_enrt_cvg_end_dt_rl
4048 ,p_rt_end_dt => l_rt_end_dt
4049 ,p_rt_end_dt_cd => l_rt_end_dt_cd
4050 ,p_rt_end_dt_rl => l_rt_end_dt_rl
4051 ,p_effective_date => p_effective_date
4052 ,p_lf_evt_ocrd_dt => nvl(p_cmpltd_dt,p_effective_date)
4053 );
4054 l_unsusp_cvg_start_date :=l_enrt_cvg_strt_dt;
4055 --
4056 else
4057 hr_utility.set_location('1 g_cmpltn dt'||p_cmpltd_dt,777);
4058
4059 ben_determine_date.main
4060 (p_date_cd => l_unsspnd_enrt_cd
4061 ,p_formula_id => null
4062 ,P_PER_IN_LER_ID => p_per_in_ler_id
4063 ,P_PERSON_ID => l_pen.person_id
4064 ,P_PGM_ID => l_pen.pgm_id
4065 ,P_PL_ID => l_pen.pl_id
4066 ,P_OIPL_ID => l_pen.oipl_id
4067 ,p_business_group_id => p_business_group_id
4068 ,p_effective_date => p_effective_date
4069 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt -- p_effective_date
4070 ,p_cmpltd_dt => nvl(p_cmpltd_dt,p_effective_date)
4071 ,p_returned_date => l_unsusp_cvg_start_date
4072 );
4073 End if;
4074 --
4075 hr_utility.set_location('2 l_unsusp_cvg_start_date'||l_unsusp_cvg_start_date,777);
4076
4077 l_cvg_thru_dt := l_unsusp_cvg_start_date - 1 ;
4078 -- need choice info to recalc dates
4079 --
4080 -- We need to end the rates one day before the new Coverage start as per
4081 -- Unsuspend Code which is l_cvg_thru_dt
4082 For l_prv in c_prv (l_interim.prtt_enrt_rslt_id) loop
4083 l_last_place := 'Calling update_prtt_rt_val';
4084 ben_prtt_rt_val_api.update_prtt_rt_val
4085 (P_VALIDATE => FALSE
4086 ,P_PRTT_RT_VAL_ID => l_prv.prtt_rt_val_id
4087 ,p_person_id => l_interim.person_id
4088 ,P_RT_END_DT => l_cvg_thru_dt -- 999 l_rec_rt_end_dt
4089 ,p_business_group_id => p_business_group_id
4090 ,p_per_in_ler_id => l_per_in_ler_id
4091 ,P_OBJECT_VERSION_NUMBER => l_prv.object_version_number
4092 ,P_EFFECTIVE_DATE => p_effective_date
4093 );
4094 End loop;
4095 --
4096 -- Update Dependent coverage thru date.
4097 --
4098 For l_dpnt in c_dpnt(l_interim.prtt_enrt_rslt_id) loop
4099 --
4100 If (p_effective_date = l_dpnt.effective_start_date) then
4101 l_datetrack_mode := hr_api.g_correction;
4102 Else
4103 l_datetrack_mode := hr_api.g_update;
4104 End if;
4105 --
4106 l_last_place := 'Calling update_elig_cvrd_dpnt';
4107 ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt
4108 (p_validate => FALSE
4109 ,p_business_group_id => p_business_group_id
4110 ,p_elig_cvrd_dpnt_id => l_dpnt.elig_cvrd_dpnt_id
4111 ,p_effective_start_date => l_dpnt.effective_start_date
4112 ,p_effective_end_date => l_dpnt.effective_end_date
4113 ,p_cvg_thru_dt => l_cvg_thru_dt -- 999 l_dpnt_cvg_end_dt
4114 ,p_object_version_number => l_dpnt.object_version_number
4115 ,p_effective_date => p_effective_date
4116 ,p_datetrack_mode => l_datetrack_mode
4117 ,p_multi_row_actn => FALSE);
4118 End loop;
4119 --
4120 -- Update interim result row.
4121 --
4122 if l_unsusp_cvg_start_date = l_interim.enrt_cvg_strt_dt then
4123 l_datetrack_mode := hr_api.g_correction;
4124 Else
4125 l_datetrack_mode := hr_api.g_update;
4126 End if;
4127 hr_utility.set_location('l_cvg_thru_dt'||l_cvg_thru_dt,777);
4128 --
4129 Update_sspndd_flag
4130 (p_prtt_enrt_rslt_id => l_interim.prtt_enrt_rslt_id
4131 ,p_effective_date => p_effective_date
4132 ,p_business_group_id => p_business_group_id
4133 ,p_sspndd_flag => 'N'
4134 ,p_RPLCS_SSPNDD_RSLT_ID => NULL
4135 ,p_enrt_cvg_thru_dt => l_cvg_thru_dt
4136 ,p_object_version_number => l_interim.object_version_number
4137 ,p_datetrack_mode => l_datetrack_mode
4138 ,p_ENRT_PL_OPT_FLAG => 'N'
4139 ,p_pgm_id => l_interim.pgm_id
4140 ,p_pl_id => l_interim.pl_id
4141 ,p_person_id => l_interim.person_id
4142 );
4143 --Bug 2185509 Delete the interim ledger entries
4144 ben_provider_pools.remove_bnft_prvdd_ldgr
4145 (p_prtt_enrt_rslt_id => l_interim.prtt_enrt_rslt_id
4146 ,p_effective_date => p_effective_date
4147 ,p_business_group_id => p_business_group_id
4148 ,p_validate => FALSE
4149 ,p_datetrack_mode => hr_api.g_delete
4150 );
4151 --
4152 End if;
4153 --
4154 -- Get the unsuspend enrollment code
4155 -- Default to "As of completed date" if not found or null
4156 --
4157 if l_carry_forward = 'N' then --CFW
4158 open c_unsspnd_enrt_cd;
4159 fetch c_unsspnd_enrt_cd into l_unsspnd_enrt_cd;
4160 if c_unsspnd_enrt_cd%notfound or l_unsspnd_enrt_cd is null then
4161 l_unsspnd_enrt_cd:='ACD';
4162 end if;
4163 close c_unsspnd_enrt_cd;
4164 else
4165 l_unsspnd_enrt_cd:='ACD';
4166 end if;
4167 hr_utility.set_location('past c_unsspnd_enrt_cd', 10);
4168 --
4169 -- need choice info to recalc dates
4170 --
4171 open c_choice_info(p_prtt_enrt_rslt_id);
4172 fetch c_choice_info into
4173 l_elig_per_elctbl_chc_id,
4174 l_lee_rsn_id,
4175 l_enrt_perd_id,
4176 --START Bug 2958032
4177 l_epe_prtt_enrt_rslt_id,
4178 l_epe_object_version_number;
4179 --END Bug 2958032;
4180 if c_choice_info%notfound then
4181 --
4182 -- error
4183 --
4184 hr_utility.set_location('BEN_91457_ELCTBL_CHC_NOT_FOUND rslt id:'||
4185 to_char(p_prtt_enrt_rslt_id), 75);
4186 fnd_message.set_name('BEN','BEN_91457_ELCTBL_CHC_NOT_FOUND');
4187 fnd_message.set_token('ID', 'NA');
4188 fnd_message.set_token('PROC', '2:'||l_proc);
4189 fnd_message.raise_error;
4190 end if;
4191 close c_choice_info;
4192 hr_utility.set_location('past c_choice_info', 10);
4193 --
4194 -- This section handles the changes to the suspended enrollment's
4195 -- coverage start date
4196 --
4197 hr_utility.set_location('l_unsspnd_enrt_cd:'||l_unsspnd_enrt_cd, 60);
4198 hr_utility.set_location('p_effective_date:'||p_effective_date, 60);
4199 hr_utility.set_location('l_pen.enrt_cvg_strt_dt'||l_pen.enrt_cvg_strt_dt,777);
4200
4201 If (p_effective_date >= l_pen.enrt_cvg_strt_dt ) then
4202 --
4203 l_update_dates := true;
4204 --
4205 if not l_previous_no_sspn then
4206 If l_unsspnd_enrt_cd = 'ACD' then
4207 --
4208 l_pen.enrt_cvg_strt_dt := nvl(p_cmpltd_dt,p_effective_date) ;
4209 --
4210 hr_utility.set_location('l_pen.enrt_cvg_strt_dt:'||l_pen.enrt_cvg_strt_dt, 60);
4211 --
4212 Elsif l_unsspnd_enrt_cd = 'UEECSD' then
4213 --
4214 -- Do nothing already set
4215 --
4216 null;
4217 elsif l_unsspnd_enrt_cd='RUCDECSDC' then
4218 --
4219 -- Recalc using enrt_cvg_strt_dt_cd
4220 -- Substitute p_effective_date for the lf_evt_ocrd_dt
4221 --
4222 ben_determine_date.rate_and_coverage_dates
4223 (p_which_dates_cd => 'C'
4224 ,p_date_mandatory_flag => 'Y'
4225 ,p_compute_dates_flag => 'Y'
4226 ,p_business_group_id => p_business_group_id
4227 ,P_PER_IN_LER_ID => p_per_in_ler_id
4228 ,P_PERSON_ID => l_pen.person_id
4229 ,P_PGM_ID => l_pen.pgm_id
4230 ,P_PL_ID => l_pen.pl_id
4231 ,P_OIPL_ID => l_pen.oipl_id
4232 ,P_LEE_RSN_ID => l_lee_rsn_id
4233 ,P_ENRT_PERD_ID => l_enrt_perd_id
4234 ,p_enrt_cvg_strt_dt => l_enrt_cvg_strt_dt
4235 ,p_enrt_cvg_strt_dt_cd => l_enrt_cvg_strt_dt_cd
4236 ,p_enrt_cvg_strt_dt_rl => l_enrt_cvg_strt_dt_rl
4237 ,p_rt_strt_dt => l_rt_strt_dt
4238 ,p_rt_strt_dt_cd => l_rt_strt_dt_cd
4239 ,p_rt_strt_dt_rl => l_rt_strt_dt_rl
4240 ,p_enrt_cvg_end_dt => l_enrt_cvg_end_dt
4241 ,p_enrt_cvg_end_dt_cd => l_enrt_cvg_end_dt_cd
4242 ,p_enrt_cvg_end_dt_rl => l_enrt_cvg_end_dt_rl
4243 ,p_rt_end_dt => l_rt_end_dt
4244 ,p_rt_end_dt_cd => l_rt_end_dt_cd
4245 ,p_rt_end_dt_rl => l_rt_end_dt_rl
4246 ,p_effective_date => p_effective_date
4247 ,p_lf_evt_ocrd_dt => nvl(p_cmpltd_dt,p_effective_date)
4248 );
4249 l_pen.enrt_cvg_strt_dt :=l_enrt_cvg_strt_dt;
4250 --
4251 else
4252 hr_utility.set_location('TWO g_cmpltn dt'||p_cmpltd_dt,777);
4253 ben_determine_date.main
4254 (p_date_cd => l_unsspnd_enrt_cd
4255 ,p_formula_id => null
4256 ,P_PER_IN_LER_ID => p_per_in_ler_id
4257 ,P_PERSON_ID => l_pen.person_id
4258 ,P_PGM_ID => l_pen.pgm_id
4259 ,P_PL_ID => l_pen.pl_id
4260 ,P_OIPL_ID => l_pen.oipl_id
4261 ,p_business_group_id => p_business_group_id
4262 ,p_effective_date => p_effective_date
4263 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt -- p_effective_date
4264 ,p_cmpltd_dt => nvl(p_cmpltd_dt,p_effective_date)
4265 ,p_returned_date => l_enrt_cvg_strt_dt
4266 );
4267 hr_utility.set_location('TWO l_pen.enrt_cvg_strt_dt'||l_pen.enrt_cvg_strt_dt,777);
4268 l_pen.enrt_cvg_strt_dt :=l_enrt_cvg_strt_dt;
4269 hr_utility.set_location('TWO l_enrt_cvg_strt_dt'||l_enrt_cvg_strt_dt,777);
4270
4271 End if;
4272 --
4273 end if; -- l_previous_no_sspn
4274 --
4275 end if;
4276 --
4277 --
4278 -- Determine new dates for rates and dependents
4279 --
4280 -- Updating participant rate value's coverage start date
4281 -- NOTE: this must happen before the suspend flag is set to N
4282 --
4283 For l_prv in c_prv (p_prtt_enrt_rslt_id) loop
4284 l_last_place := 'Calling update_prtt_rt_val';
4285 --
4286 if not l_previous_no_sspn then
4287 -- Don't bother updating if the date is the same
4288 -- 999
4289 --bug#3692450 - if the unsuspend code is use existing enrollment start
4290 --use the rate start date arrived by participation process
4291 if l_unsspnd_enrt_cd <> 'UEECSD' then
4292 l_rec_rt_strt_dt := l_pen.enrt_cvg_strt_dt ;
4293 end if;
4294 --
4295 if l_rec_rt_strt_dt is null then
4296 l_rec_rt_strt_dt:=l_prv.rt_strt_dt;
4297 end if;
4298 --
4299 -- Bug 5231894 changed "<>" to ">". We should update rate start date only if the rate
4300 -- has started on a date earlier than effective_date / date of un-suspension.
4301 -- if (l_rec_rt_strt_dt<>l_prv.rt_strt_dt) and
4302 if (l_rec_rt_strt_dt > l_prv.rt_strt_dt) and
4303 l_update_dates then
4304 ben_prtt_rt_val_api.update_prtt_rt_val
4305 (P_VALIDATE => FALSE
4306 ,P_PRTT_RT_VAL_ID => l_prv.prtt_rt_val_id
4307 ,p_person_id => l_pen.person_id
4308 ,P_RT_STRT_DT => l_rec_rt_strt_dt
4309 ,p_business_group_id => p_business_group_id
4310 ,p_per_in_ler_id => l_per_in_ler_id
4311 ,P_OBJECT_VERSION_NUMBER => l_prv.object_version_number
4312 ,P_EFFECTIVE_DATE => p_effective_date
4313 );
4314 else
4315 l_rec_rt_strt_dt := l_prv.rt_strt_dt;
4316 end if;
4317 else
4318 l_rec_rt_strt_dt := l_prv.rt_strt_dt;
4319 --
4320 end if;
4321 -- Handle update/create of EEs
4322 --
4323 -- Bug 4141269 We will pass p_input_value_id and p_element_type_id as null
4324 -- so that they get correctly re-queried in benelmen.pkb
4325 -- based on life event occurred date or rate start date
4326 if l_prv.element_entry_value_id is null then
4327 ben_element_entry.create_enrollment_element
4328 (p_business_group_id => p_business_group_id
4329 ,p_prtt_rt_val_id => l_prv.prtt_rt_val_id
4330 ,p_person_id => l_pen.person_id
4331 ,p_acty_ref_perd => l_prv.acty_ref_perd_cd
4332 ,p_acty_base_rt_id => l_prv.acty_base_rt_id
4333 ,p_enrt_rslt_id => p_prtt_enrt_rslt_id
4334 ,p_rt_start_date => l_rec_rt_strt_dt
4335 ,p_rt => l_prv.rt_val
4336 ,p_cmncd_rt => l_prv.cmcd_rt_val
4337 ,p_ann_rt => l_prv.ann_rt_val
4338 ,p_input_value_id => null -- l_prv.input_value_id /* Bug 4141269 */
4339 ,p_element_type_id => null -- l_prv.element_type_id /* Bug 4141269 */
4340 ,p_prv_object_version_number => l_prv.object_version_number
4341 ,p_effective_date => p_effective_date
4342 --
4343 ,p_eev_screen_entry_value => l_dummy_number
4344 ,p_element_entry_value_id => l_dummy_number
4345 );
4346 --
4347 else
4348 open c_ee(l_prv.element_entry_value_id,l_rec_rt_strt_dt);
4349 fetch c_ee into l_ee_end_date;
4350 close c_ee;
4351 if l_ee_end_date <> hr_api.g_eot and
4352 l_prv.rt_end_dt = hr_api.g_eot then
4353 ben_element_entry.reopen_closed_enrollment(
4354 p_business_group_id => p_business_group_id
4355 ,p_person_id => l_pen.person_id
4356 ,p_prtt_rt_val_id => l_prv.prtt_rt_val_id
4357 ,p_acty_base_rt_id => l_prv.acty_base_rt_id
4358 ,p_element_type_id => l_prv.element_type_id
4359 ,p_input_value_id => l_prv.input_value_id
4360 ,p_rt => null --not used
4361 ,p_rt_start_date => l_rec_rt_strt_dt
4362 ,p_effective_date => p_effective_date
4363 );
4364 --
4365 end if;
4366 end if;
4367 --
4368 -- following codE writes entries to pool ledger-bug#1617825
4369 --
4370 open c_enrt_rt(l_prv.prtt_rt_val_id);
4371 fetch c_enrt_rt into l_decr_bnft_prvdr_pool_id;
4372 close c_enrt_rt;
4373 --
4374 if l_decr_bnft_prvdr_pool_id is not null then
4375 --
4376 ben_provider_pools.create_debit_ledger_entry
4377 (p_person_id => l_pen.person_id
4378 ,p_per_in_ler_id => l_per_in_ler_id
4379 ,p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
4380 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
4381 ,p_decr_bnft_prvdr_pool_id => l_decr_bnft_prvdr_pool_id
4382 ,p_acty_base_rt_id => l_prv.acty_base_rt_id
4383 ,p_prtt_rt_val_id => l_prv.prtt_rt_val_id
4384 ,p_enrt_mthd_cd => l_pen.enrt_mthd_cd
4385 ,p_val => l_prv.rt_val
4386 ,p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
4387 ,p_business_group_id => p_business_group_id
4388 ,p_effective_date => p_effective_date
4389 --
4390 ,p_bpl_used_val => l_dummy_number
4391 );
4392 --
4393 end if;
4394 End loop;
4395 --
4396 -- Un-suspend Enrollment. Which involve updating coverage start date of
4397 -- dependent, participant rate value tables
4398 -- Note: this must be done after rate stuff since the prv api updates
4399 -- the ee's except when the result is suspended. We want to
4400 -- handle the ee's here (above)
4401 --
4402 l_last_place := 'Calling update_sspndd_flag';
4403 --
4404 if l_update_dates then
4405 --
4406 -- As the below cursor Csr_prtt_enrt_rslt replaces the computed
4407 -- cvrg_strt_dt save it to reset it.
4408 --
4409 l_enrt_cvg_strt_dt := l_pen.enrt_cvg_strt_dt;
4410 --
4411 end if;
4412 --
4413 -- Get the needed result info
4414 --
4415 Open Csr_prtt_enrt_rslt(p_prtt_enrt_rslt_id);
4416 l_pen.prtt_enrt_rslt_id:=null;
4417 Fetch Csr_prtt_enrt_rslt Into l_pen;
4418 Close Csr_prtt_enrt_rslt;
4419 if l_pen.prtt_enrt_rslt_id is not null then
4420 --
4421 -- Check date-track mode.
4422 -- BUG 3441027 we can't use the p_datetrack_mode if the
4423 -- p_effective_date > l_pen.effective_start_date.
4424 -- What ever mode user selects while completing the
4425 -- certifications, we need to derive it here.
4426 --
4427 IF (p_effective_date = l_pen.effective_start_date) Then
4428 --
4429 l_datetrack_mode := hr_api.g_correction;
4430 --
4431 ELSIF p_effective_date > l_pen.effective_start_date then
4432 --
4433 l_datetrack_mode := hr_api.g_update;
4434 --
4435 ELSE
4436 --
4437 l_datetrack_mode := p_datetrack_mode;
4438 --
4439 END IF;
4440 --
4441 hr_utility.set_location('l_datetrack_mode:'||l_datetrack_mode, 60);
4442 hr_utility.set_location('l_enrt_cvg_strt_dt'||l_enrt_cvg_strt_dt,777);
4443 hr_utility.set_location('BEFORE l_pen.enrt_cvg_strt_dt '||l_pen.enrt_cvg_strt_dt ,777);
4444
4445 --
4446 if l_update_dates and not l_previous_no_sspn then
4447 --
4448 -- As the above cursor Csr_prtt_enrt_rslt replaces the computed
4449 -- enrt_cvrg_strt_dt reset it with computed value.
4450 --
4451 l_pen.enrt_cvg_strt_dt := l_enrt_cvg_strt_dt;
4452 hr_utility.set_location('INSIDE l_pen.enrt_cvg_strt_dt '||l_pen.enrt_cvg_strt_dt ,777);
4453
4454 --
4455 end if;
4456 --
4457 Update_sspndd_flag
4458 (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
4459 ,p_effective_date => p_effective_date
4460 ,p_business_group_id => p_business_group_id
4461 ,p_sspndd_flag => 'N'
4462 ,p_RPLCS_SSPNDD_RSLT_ID => NULL
4463 ,p_object_version_number => l_pen.object_version_number
4464 ,p_datetrack_mode => l_datetrack_mode
4465 ,p_ENRT_PL_OPT_FLAG => l_pen.ENRT_PL_OPT_FLAG
4466 ,p_enrt_cvg_strt_dt => l_pen.enrt_cvg_strt_dt
4467 ,p_pgm_id => l_pen.pgm_id
4468 ,p_pl_id => l_pen.pl_id
4469 ,p_person_id => l_pen.person_id
4470 );
4471 --START BUG 2958032
4472 open c_choice_info(p_prtt_enrt_rslt_id);
4473 fetch c_choice_info into
4474 l_elig_per_elctbl_chc_id,
4475 l_lee_rsn_id,
4476 l_enrt_perd_id,
4477 l_epe_prtt_enrt_rslt_id,
4478 l_epe_object_version_number;
4479 if c_choice_info%notfound then
4480 hr_utility.set_location('BEN_91457_ELCTBL_CHC_NOT_FOUND rslt id:'||
4481 to_char(p_prtt_enrt_rslt_id), 75);
4482 fnd_message.set_name('BEN','BEN_91457_ELCTBL_CHC_NOT_FOUND');
4483 fnd_message.set_token('ID', 'NA');
4484 fnd_message.set_token('PROC', '2:'||l_proc);
4485 fnd_message.raise_error;
4486 end if;
4487 close c_choice_info;
4488 --
4489 hr_utility.set_location('got the latest epe info', 10);
4490 hr_utility.set_location(' l_elig_per_elctbl_chc_id '||l_elig_per_elctbl_chc_id,22);
4491 hr_utility.set_location(' l_epe_prtt_enrt_rslt_id '||l_epe_prtt_enrt_rslt_id,23);
4492 --
4493 if l_epe_prtt_enrt_rslt_id is null or
4494 l_epe_prtt_enrt_rslt_id <> p_prtt_enrt_rslt_id then
4495 --
4496 ben_ELIG_PER_ELC_CHC_api.update_ELIG_PER_ELC_CHC
4497 (p_validate => FALSE
4498 ,p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
4499 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
4500 ,p_object_version_number => l_epe_object_version_number
4501 ,p_effective_date => p_effective_date
4502 ,p_request_id => fnd_global.conc_request_id
4503 ,p_program_application_id => fnd_global.prog_appl_id
4504 ,p_program_id => fnd_global.conc_program_id
4505 ,p_program_update_date => sysdate
4506 );
4507 --
4508 end if;
4509 -- Need to clean the enb records for pen_id
4510 --
4511 for l_enb in c_enb(p_prtt_enrt_rslt_id,l_elig_per_elctbl_chc_id) loop
4512 --
4513 ben_enrt_bnft_api.update_enrt_bnft
4514 (p_enrt_bnft_id => l_enb.enrt_bnft_id
4515 ,p_effective_date => p_effective_date
4516 ,p_object_version_number => l_enb.object_version_number
4517 ,p_business_group_id => p_business_group_id
4518 ,p_prtt_enrt_rslt_id => NULL
4519 ,p_program_application_id => fnd_global.prog_appl_id
4520 ,p_program_id => fnd_global.conc_program_id
4521 ,p_request_id => fnd_global.conc_request_id
4522 ,p_program_update_date => sysdate
4523 );
4524 --
4525 end loop ;
4526 --
4527 open c_enrt_bnft(p_prtt_enrt_rslt_id,l_elig_per_elctbl_chc_id,p_effective_date);
4528 fetch c_enrt_bnft into l_enrt_bnft_id,l_enb_object_version_number;
4529 close c_enrt_bnft ;
4530 --
4531 if l_enrt_bnft_id is not null then
4532 --
4533 ben_election_information.manage_enrt_bnft(
4534 p_enrt_bnft_id => l_enrt_bnft_id,
4535 p_effective_date => p_effective_date,
4536 p_object_version_number => l_enb_object_version_number,
4537 p_business_group_id => p_business_group_id,
4538 p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
4539 p_per_in_ler_id => l_per_in_ler_id
4540 );
4541 --
4542 end if;
4543 --
4544 --END BUG 2958032
4545 --
4546 -- Bug : 2088231
4547 -- As the enrollment is unsuspended, go create the
4548 -- credit ledger entry.
4549 --
4550 ben_provider_pools.accumulate_pools
4551 (p_validate => FALSE
4552 ,p_person_id => l_pen.person_id
4553 ,p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
4554 ,p_business_group_id => p_business_group_id
4555 ,p_enrt_mthd_cd => l_pen.enrt_mthd_cd
4556 ,p_effective_date => p_effective_date);
4557 --
4558 -- Bug fix 2689926: It is not enuf if the accumulate_pools alone is called
4559 -- becos it will only handle the create/delete of ledger rows,
4560 -- in addition to this, when the enrollment gets unsuspended we need
4561 -- to do other flex credits processing like forfeiture, distribution,
4562 -- rollover and "updation of prtt_rt_value of flex shell plan enrt_rslt
4563 -- row", due to the unsuspension. Hence making a call to
4564 -- ben_provider_pools.total_plans will handle all of the above.
4565 --
4566 l_prtt_enrt_rslt_id := p_prtt_enrt_rslt_id ;
4567 ben_provider_pools.total_pools
4568 (p_validate => FALSE
4569 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
4570 ,p_prtt_rt_val_id => l_prtt_rt_val_id
4571 ,p_acty_ref_perd_cd => l_acty_ref_perd_cd
4572 ,p_acty_base_rt_id => l_acty_base_rt_id
4573 ,p_rt_strt_dt => l_rt_strt_dt1
4574 ,p_rt_val => l_rt_val
4575 ,p_element_type_id => l_element_type_id
4576 ,p_person_id => l_pen.person_id
4577 ,p_per_in_ler_id => l_per_in_ler_id
4578 ,p_enrt_mthd_cd => l_pen.enrt_mthd_cd
4579 ,p_effective_date => p_effective_date
4580 ,p_business_group_id => p_business_group_id
4581 ,p_pgm_id => l_pen.pgm_id
4582 );
4583 --
4584 -- End fix 2689926
4585 --
4586 --
4587 -- Update Dependent coverage start date.
4588 --
4589 For l_dpnt in c_dpnt(p_prtt_enrt_rslt_id) loop
4590 -- if l_dpnt.cvg_strt_dt<>l_dpnt_cvg_strt_dt and --BUG 3977951
4591 -- 4775743: If Dpnt Cvg starts after the PEN Strt, then use Dpnt Cvg Strt.
4592 --
4593 l_dpnt_cvg_strt_dt := greatest(NVL(l_dpnt.cvg_strt_dt,l_pen.enrt_cvg_strt_dt), l_pen.enrt_cvg_strt_dt);
4594 --
4595 if l_dpnt.cvg_strt_dt <> l_pen.enrt_cvg_strt_dt and l_update_dates then
4596 --
4597 If (p_effective_date = l_dpnt.effective_start_date) then
4598 l_datetrack_mode := hr_api.g_correction;
4599 Else
4600 l_datetrack_mode := hr_api.g_update;
4601 End if;
4602 --
4603 l_last_place := 'Calling update_elig_cvrd_dpnt';
4604 --
4605 ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt
4606 (p_validate => FALSE
4607 ,p_business_group_id => p_business_group_id
4608 ,p_elig_cvrd_dpnt_id => l_dpnt.elig_cvrd_dpnt_id
4609 ,p_effective_start_date => l_dpnt.effective_start_date
4610 ,p_effective_end_date => l_dpnt.effective_end_date
4611 ,p_cvg_strt_dt => l_dpnt_cvg_strt_dt -- l_pen.enrt_cvg_strt_dt -- 999 l_dpnt_cvg_strt_dt
4612 ,p_object_version_number => l_dpnt.object_version_number
4613 ,p_effective_date => p_effective_date
4614 ,p_datetrack_mode => l_datetrack_mode
4615 ,p_multi_row_actn => FALSE
4616 );
4617 end if;
4618 End loop;
4619 end if;
4620 l_last_place := 'Calling the Post-Result RCO';
4621 --
4622 If p_called_from = 'BENEOPEH' then
4623 hr_utility.set_location(l_proc||' Called from Override',99);
4624 else
4625 --
4626 If (p_post_rslt_flag = 'Y') Then
4627 l_last_place := 'Calling result multi-row edit';
4628 --Bug 4622534
4629 if g_cfw_dpnt_flag = 'N' THEN
4630 --
4631 -- Start 6054988 : Call multi_rows_edit only if any elections made
4632 -- in the current pil else call chk_coverage_across_plan_types
4633 open chk_elcn_dt_in_pel;
4634 fetch chk_elcn_dt_in_pel into l_var;
4635 if chk_elcn_dt_in_pel%NOTFOUND then
4636 ben_prtt_enrt_result_api.multi_rows_edit
4637 (p_person_id => l_pen.person_id
4638 ,p_effective_date => p_effective_date
4639 ,p_business_group_id => p_business_group_id
4640 ,p_pgm_id => l_pen.pgm_id
4641 ,p_per_in_ler_id => l_per_in_ler_id
4642 );
4643 else
4644 ben_PRTT_ENRT_RESULT_api.chk_coverage_across_plan_types
4645 (p_person_id => l_pen.person_id,
4646 p_effective_date => p_effective_date,
4647 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
4648 p_business_group_id => p_business_group_id,
4649 p_pgm_id => l_pen.pgm_id);
4650 end if;
4651 --
4652 -- End 6054988
4653 --
4654 l_last_place := 'Calling process_post_results';
4655 ben_proc_common_enrt_rslt.process_post_results
4656 (p_person_id => l_pen.person_id
4657 ,p_enrt_mthd_cd => l_pen.enrt_mthd_cd
4658 ,p_effective_date => p_effective_date
4659 ,p_business_group_id => p_business_group_id
4660 ,p_per_in_ler_id => l_per_in_ler_id
4661 );
4662 end if;
4663 --
4664 else
4665 -- check if it's an imputed income plan
4666 open c_pl(l_pen.pl_id);
4667 fetch c_pl into l_dummy;
4668 if c_pl%FOUND then
4669 l_imp_inc_plan := true;
4670 end if;
4671 close c_pl;
4672 if l_imp_inc_plan then
4673 ben_det_imputed_income.p_comp_imputed_income
4674 (p_person_id => l_pen.person_id
4675 ,p_enrt_mthd_cd => l_pen.enrt_mthd_cd
4676 ,p_per_in_ler_id => l_pen.per_in_ler_id
4677 ,p_effective_date => p_effective_date
4678 ,p_business_group_id => p_business_group_id
4679 ,p_ctrlm_fido_call => false
4680 ,p_validate => false);
4681 end if;
4682 End if;
4683 End if; -- p_called_from
4684 End If;
4685 hr_utility.set_location('Leaving:'|| l_proc, 99);
4686 Exception
4687 When others then
4688 hr_utility.set_location('ERROR '||l_proc, 95);
4689 rpt_error(l_proc, l_last_place);
4690 -- for nocopy changes
4691 p_object_version_number := l_object_version_number ;
4692 fnd_message.raise_error;
4693 End unsuspend_enrollment;
4694 --
4695 end ben_sspndd_enrollment;
[Home] [Help]