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