[Home] [Help]
129: and pil2.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
130: --
131: --
132: -- get all dependents that can be copied, based upon the fact that there
133: -- are no individual rlshp restrictions or we meet the restrictions.
134: cursor copy_dpnt_c is
135: select old.elig_cvrd_dpnt_id old_dpnt,
136: new.elig_dpnt_id new_dpnt
137: from ben_elig_cvrd_dpnt_f old,
157: and dependent is eligible to be covered for the electable choice that
158: he is previously enrolled.This will ensure end dated dependent coverages
159: will not be carry forwarded*/
160: and not exists(
161: select '1' from dual where
162: (
163: ('Y' = (select 'Y' from dual where exists
164: (select '1' from ben_prtt_enrt_rslt_f pen1
165: where pen1.prtt_enrt_rslt_id = p_old_prtt_enrt_rslt_id
159: will not be carry forwarded*/
160: and not exists(
161: select '1' from dual where
162: (
163: ('Y' = (select 'Y' from dual where exists
164: (select '1' from ben_prtt_enrt_rslt_f pen1
165: where pen1.prtt_enrt_rslt_id = p_old_prtt_enrt_rslt_id
166: and pen1.prtt_enrt_rslt_stat_cd is NULL
167: and pen1.effective_end_date = hr_api.g_eot
167: and pen1.effective_end_date = hr_api.g_eot
168: and pen1.enrt_cvg_thru_dt = hr_api.g_eot)
169: )) --'Y'
170: and old.cvg_thru_dt <> hr_api.g_eot
171: and ('Y' = (select 'Y' from dual where exists
172: (select '1' from ben_elig_dpnt edp,
173: ben_elig_per_elctbl_chc epe
174: where edp.dpnt_person_id = old.dpnt_person_id
175: and edp.per_in_ler_id = new.per_in_ler_id
190: reinstatement, skip the below cursor checks as above portion of the cursor takes care of which dependents should be carry forwarded*/
191: and ( (ben_manage_life_events.g_bckdt_per_in_ler_id is not NULL and ben_lf_evt_clps_restore.g_dflt_during_reinstate = 'N'
192: and (
193: (l_old_prtt_enrt_rslt_id = p_new_prtt_enrt_rslt_id
194: and ('1' = (select '1' from dual where
195: not exists
196: (select '1' from ben_le_clsn_n_rstr pdp_old
197: where pdp_old.per_in_ler_id = ben_manage_life_events.g_bckdt_per_in_ler_id
198: and pdp_old.prtt_enrt_rslt_id = l_old_prtt_enrt_rslt_id
200: and pdp_old.dpnt_person_id = old.dpnt_person_id
201: and pdp_old.cvg_thru_dt <> hr_api.g_eot
202: and pdp_old.bkup_tbl_typ_cd = 'BEN_ELIG_CVRD_DPNT_F'))))
203: or (l_old_prtt_enrt_rslt_id <> p_new_prtt_enrt_rslt_id
204: and ('1' = (select '1' from dual where
205: exists
206: (select '1' from ben_le_clsn_n_rstr pdp_old
207: ,ben_prtt_enrt_rslt_f pen5
208: where pdp_old.per_in_ler_id = ben_manage_life_events.g_bckdt_per_in_ler_id