[Home] [Help]
596: --
597: cursor c_pen(v_prtt_enrt_rslt_id number,
598: v_per_in_ler_id number ) is
599: select pen.*
600: from ben_prtt_enrt_rslt_f pen
601: where pen.prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
602: and pen.per_in_ler_id = v_per_in_ler_id
603: and pen.prtt_enrt_rslt_stat_cd IS NULL
604: and pen.effective_end_date = hr_api.g_eot ;
947: select 2 order_no
948: ,plip.DFLT_TO_ASN_PNDG_CTFN_CD
949: ,plip.DFLT_TO_ASN_PNDG_CTFN_RL
950: ,plip.BNFT_OR_OPTION_RSTRCTN_CD
951: From ben_plip_f plip, ben_prtt_enrt_rslt_f pen
952: Where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
953: and pen.pgm_id = plip.pgm_id
954: and pen.pl_id = plip.pl_id
955: and pen.prtt_enrt_rslt_stat_cd is null
1009: per.pl_typ_id,
1010: epe.elig_per_elctbl_chc_id,
1011: -- RCHASE Bug#5353 added
1012: per.prtt_enrt_rslt_id
1013: from ben_prtt_enrt_rslt_f per,
1014: ben_elig_per_elctbl_chc epe,
1015: ben_per_in_ler pil
1016: where pil.business_group_id=p_business_group_id and
1017: pil.person_id=p_person_id and
1041: per.pl_typ_id,
1042: to_number(null) elig_per_elctbl_chc_id,
1043: -- RCHASE Bug#5353
1044: per.prtt_enrt_rslt_id
1045: from ben_prtt_enrt_rslt_f per,
1046: ben_per_in_ler pinl
1047: where per.per_in_ler_id=pinl.per_in_ler_id and -- Bug 2595113
1048: per.person_id=p_person_id and
1049: per.business_group_id=p_business_group_id and
1166: cursor c_current_enrt(v_pl_id number, v_oipl_id number ) is
1167: select per.pl_id,
1168: per.pl_typ_id,
1169: per.prtt_enrt_rslt_id
1170: from ben_prtt_enrt_rslt_f per,
1171: ben_per_in_ler pil
1172: where pil.business_group_id = p_business_group_id and
1173: pil.person_id = p_person_id and
1174: pil.per_in_ler_id = p_per_in_ler_id and
1182: ( p_per_in_ler_id = per.per_in_ler_id and
1183: ( /* Case 4 */
1184: per.enrt_cvg_thru_dt <> hr_api.g_eot or
1185: ( /* Case 1,2*/
1186: ( exists (select 'x' from ben_prtt_enrt_rslt_f pen3
1187: where pen3.prtt_enrt_rslt_id = per.prtt_enrt_rslt_id and
1188: pen3.prtt_enrt_rslt_stat_cd is null and
1189: pen3.sspndd_flag = 'N' and
1190: pen3.effective_end_date < per.effective_start_date and
1727: --ie the enrollment result from the previous life event.
1728: --
1729: cursor c_current_same_epe(c_current_pen_id number) is
1730: select epe_new.elig_per_elctbl_chc_id
1731: from ben_prtt_enrt_rslt_f pen_current,
1732: ben_elig_per_elctbl_chc epe_new,
1733: ben_per_in_ler pil_new
1734: where pen_current.prtt_enrt_rslt_id = c_current_pen_id
1735: and pen_current.pl_id = epe_new.pl_id
1759: c_per_in_ler_id number,
1760: c_elig_per_elctbl_chc_id number) is
1761: select currently_susp.prtt_enrt_rslt_id,
1762: currently_susp.rplcs_sspndd_rslt_id
1763: from ben_prtt_enrt_rslt_f susp,
1764: ben_prtt_enrt_rslt_f currently_susp
1765: where susp.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
1766: and susp.per_in_ler_id = c_per_in_ler_id
1767: -- and susp.sspndd_flag = 'N' --This can be changed once elinf is fixed
1760: c_elig_per_elctbl_chc_id number) is
1761: select currently_susp.prtt_enrt_rslt_id,
1762: currently_susp.rplcs_sspndd_rslt_id
1763: from ben_prtt_enrt_rslt_f susp,
1764: ben_prtt_enrt_rslt_f currently_susp
1765: where susp.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
1766: and susp.per_in_ler_id = c_per_in_ler_id
1767: -- and susp.sspndd_flag = 'N' --This can be changed once elinf is fixed
1768: and susp.effective_end_date = hr_api.g_eot
1777: c_per_in_ler_id number,
1778: c_elig_per_elctbl_chc_id number) is
1779: select currently_susp.prtt_enrt_rslt_id,
1780: currently_susp.rplcs_sspndd_rslt_id
1781: from ben_prtt_enrt_rslt_f susp,
1782: ben_prtt_enrt_rslt_f currently_susp
1783: where susp.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
1784: and susp.per_in_ler_id = c_per_in_ler_id
1785: -- and susp.sspndd_flag = 'N' --This can be changed once elinf is fixed
1778: c_elig_per_elctbl_chc_id number) is
1779: select currently_susp.prtt_enrt_rslt_id,
1780: currently_susp.rplcs_sspndd_rslt_id
1781: from ben_prtt_enrt_rslt_f susp,
1782: ben_prtt_enrt_rslt_f currently_susp
1783: where susp.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
1784: and susp.per_in_ler_id = c_per_in_ler_id
1785: -- and susp.sspndd_flag = 'N' --This can be changed once elinf is fixed
1786: and susp.effective_end_date = hr_api.g_eot
1803: c_per_in_ler_id number) is
1804: select new_epe.elig_per_elctbl_chc_id,
1805: interim.bnft_ordr_num ordr_num,
1806: interim.bnft_amt
1807: from ben_prtt_enrt_rslt_f interim,
1808: ben_elig_per_elctbl_chc new_epe
1809: where interim.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
1810: and interim.per_in_ler_id <> c_per_in_ler_id
1811: and interim.effective_end_date = hr_api.g_eot
2508: pen.oipl_id pen_oipl_id,
2509: pen.enrt_cvg_thru_dt,
2510: pen.object_version_number
2511: From ben_elig_per_elctbl_chc epe,
2512: ben_prtt_enrt_rslt_f pen
2513: Where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
2514: and epe.business_group_id = p_business_group_id
2515: and pen.prtt_enrt_rslt_id(+)=epe.prtt_enrt_rslt_id
2516: and pen.business_group_id(+)=p_business_group_id
2731: ,effective_end_date
2732: ,enrt_cvg_strt_dt
2733: ,enrt_cvg_thru_dt
2734: ,object_version_number
2735: From ben_prtt_enrt_rslt_f pen
2736: Where pen.business_group_id = p_business_group_id
2737: And pen.person_id = p_person_id
2738: And nvl(pen.pgm_id,-1) = nvl(p_pgm_id,-1)
2739: And pen.pl_id = p_pl_id
2888: ,pen.bnft_amt
2889: ,pen.plip_ordr_num /*ENH*/
2890: ,pen.bnft_ordr_num /*ENH*/
2891: ,epe.dpnt_dsgn_cd
2892: From ben_prtt_enrt_rslt_f pen
2893: ,ben_elig_per_elctbl_chc epe
2894: ,ben_per_in_ler pil
2895: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2896: and pen.business_group_id= p_business_group_id
2922: and enb.ordr_num = p_bnft_ordr_num ;
2923:
2924: Cursor c_new_ovn is
2925: select pen.object_version_number
2926: From ben_prtt_enrt_rslt_f pen
2927: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2928: and pen.business_group_id= p_business_group_id
2929: and pen.prtt_enrt_rslt_stat_cd is null
2930: and p_effective_date between
2986: --START OHSU
2987: ,pea.effective_start_date pea_effective_date
2988: --END OHSU
2989: from ben_prtt_enrt_actn_f pea,
2990: ben_prtt_enrt_rslt_f pen
2991: where
2992: pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2993: --START OHSU
2994: and p_effective_date between pen.effective_start_date and
3000: --
3001: cursor c_interim (p_prtt_enrt_rslt_id number,
3002: p_per_in_ler_id number) is
3003: select pen.RPLCS_SSPNDD_RSLT_ID
3004: from ben_prtt_enrt_rslt_f pen
3005: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3006: and pen.sspndd_flag = 'Y'
3007: and pen.per_in_ler_id = p_per_in_ler_id
3008: and pen.prtt_enrt_rslt_stat_cd is null
3007: and pen.per_in_ler_id = p_per_in_ler_id
3008: and pen.prtt_enrt_rslt_stat_cd is null
3009: and not exists
3010: (select null
3011: from ben_prtt_enrt_rslt_f pen3
3012: where pen3.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
3013: and pen3.prtt_enrt_rslt_stat_cd is null
3014: and pen3.effective_start_date < pen.effective_start_date
3015: and pen3.per_in_ler_id <> pen.per_in_ler_id)
3012: where pen3.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
3013: and pen3.prtt_enrt_rslt_stat_cd is null
3014: and pen3.effective_start_date < pen.effective_start_date
3015: and pen3.per_in_ler_id <> pen.per_in_ler_id)
3016: and exists (select null from ben_prtt_enrt_rslt_f pen2
3017: where pen2.prtt_enrt_rslt_id = pen.RPLCS_SSPNDD_RSLT_ID
3018: and pen2.prtt_enrt_rslt_stat_cd is null
3019: and pen2.per_in_ler_id = p_per_in_ler_id
3020: and pen2.enrt_cvg_thru_dt <> hr_api.g_eot
3023: cursor c_enrt_rslt (p_prtt_enrt_rslt_id number) is
3024: select pen.effective_start_date,
3025: pen.effective_end_date,
3026: pen.object_version_number
3027: from ben_prtt_enrt_rslt_f pen
3028: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3029: and pen.prtt_enrt_rslt_stat_cd is null
3030: and pen.effective_end_date = (select pen2.effective_start_date - 1
3031: from ben_prtt_enrt_rslt_f pen2
3027: from ben_prtt_enrt_rslt_f pen
3028: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3029: and pen.prtt_enrt_rslt_stat_cd is null
3030: and pen.effective_end_date = (select pen2.effective_start_date - 1
3031: from ben_prtt_enrt_rslt_f pen2
3032: where pen2.enrt_cvg_thru_dt <> hr_api.g_eot
3033: and pen2.effective_end_date = hr_api.g_eot
3034: and pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3035: and pen2.prtt_enrt_rslt_stat_cd is null);
3136: ,p_argument => 'p_datetrack_mode'
3137: ,p_argument_value => p_datetrack_mode);
3138: --
3139: -- ** Open result cursor.
3140: l_last_place := 'Fetching record from ben_prtt_enrt_rslt_f';
3141: Open Csr_prtt_enrt_rslt;
3142: Fetch Csr_prtt_enrt_rslt Into l_pen;
3143: If Csr_prtt_enrt_rslt%NOTFOUND Then
3144: Close Csr_prtt_enrt_rslt;
3582: ,pln.ENRT_PL_OPT_FLAG
3583: ,pen.business_group_id
3584: ,'USEEFD' calc_cvg_strt_dt_cd
3585: ,'USE1BSEFD' calc_cvg_end_dt_cd
3586: From ben_prtt_enrt_rslt_f pen
3587: ,ben_pl_f pln
3588: Where pen.prtt_enrt_rslt_id = c_rslt_id
3589: And pen.business_group_id = p_business_group_id
3590: and pen.prtt_enrt_rslt_stat_cd is null
3616: --START Bug 2958032
3617: epe.prtt_enrt_rslt_id,
3618: epe.object_version_number
3619: --END Bug 2958032
3620: from ben_prtt_enrt_rslt_f pen,
3621: ben_pil_elctbl_chc_popl pel,
3622: ben_per_in_ler pil,
3623: ben_elig_per_elctbl_chc epe
3624: where pen.prtt_enrt_rslt_id=c_prtt_enrt_rslt_id
3786: p_elig_per_elctbl_chc_id number,
3787: p_effective_date date) is
3788: select enb.enrt_bnft_id,
3789: enb.object_version_number
3790: from ben_prtt_enrt_rslt_f pen,
3791: ben_enrt_bnft enb
3792: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3793: and p_effective_date between pen.effective_start_date and pen.effective_end_date
3794: and pen.bnft_ordr_num = enb.ordr_num
3798: --
3799: -- bug#3202455 - determine whether unsuspend is on account of some user error
3800: cursor c_previous_status is
3801: select null
3802: from ben_prtt_enrt_rslt_f pen
3803: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3804: and pen.sspndd_flag = 'N'
3805: and pen.per_in_ler_id <> l_per_in_ler_id
3806: and pen.prtt_enrt_rslt_stat_cd is null;
3808: -- 6054988 : Check if any elections have been made in the current pil.
3809: --
3810: cursor chk_elcn_dt_in_pel is
3811: SELECT 'x'
3812: FROM ben_pil_elctbl_chc_popl popl, ben_prtt_enrt_rslt_f pen
3813: WHERE popl.per_in_ler_id = l_per_in_ler_id
3814: AND popl.elcns_made_dt IS NULL
3815: AND popl.dflt_asnd_dt IS NULL
3816: AND popl.pgm_id = pen.pgm_id
3858: hr_api.mandatory_arg_error(p_api_name => l_proc,
3859: p_argument => 'p_datetrack_mode',
3860: p_argument_value => p_datetrack_mode);
3861: --
3862: l_last_place := 'Fetching record from ben_prtt_enrt_rslt_f';
3863: --
3864: -- Get the needed result info
3865: --
3866: Open Csr_prtt_enrt_rslt(p_prtt_enrt_rslt_id);