[Home] [Help]
1548: --
1549: -- Declare cursors and local variables
1550: --
1551: l_chg_evt_cd hr_lookups.lookup_code%type;
1552: l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;
1553: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
1554: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
1555: l_effective_date date;
1556: l_proc varchar2(72); -- := g_package||'create_enrollment';
1549: -- Declare cursors and local variables
1550: --
1551: l_chg_evt_cd hr_lookups.lookup_code%type;
1552: l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;
1553: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
1554: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
1555: l_effective_date date;
1556: l_proc varchar2(72); -- := g_package||'create_enrollment';
1557: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
1550: --
1551: l_chg_evt_cd hr_lookups.lookup_code%type;
1552: l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;
1553: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
1554: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
1555: l_effective_date date;
1556: l_proc varchar2(72); -- := g_package||'create_enrollment';
1557: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
1558: l_return_to_exist_cvg_flag varchar2(30) := 'N';
1553: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
1554: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
1555: l_effective_date date;
1556: l_proc varchar2(72); -- := g_package||'create_enrollment';
1557: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
1558: l_return_to_exist_cvg_flag varchar2(30) := 'N';
1559: l_datetrack_mode varchar2(30);
1560: l_step integer;
1561: l_crntly_enrd_flag varchar2(30) := 'N';
1587: l_rt_end_dt_rl number;
1588: /* End of Changes for WWBUG: 1646442 : added declarations */
1589: --
1590: l_global_epe_rec ben_global_enrt.g_global_epe_rec_type;
1591: l_global_pen_rec ben_prtt_enrt_rslt_f%rowtype;
1592: l_pl_rec ben_cobj_cache.g_pl_inst_row;
1593: l_pgm_rec ben_cobj_cache.g_pgm_inst_row;
1594: l_oipl_rec ben_cobj_cache.g_oipl_inst_row;
1595: --
1597: select pen.prtt_enrt_rslt_id,
1598: pen.pl_id,
1599: pen.oipl_id,
1600: pen.pl_typ_id
1601: from ben_prtt_enrt_rslt_f pen
1602: where pen.person_id=p_person_id and
1603: pen.business_group_id =p_business_group_id and
1604: pen.prtt_enrt_rslt_stat_cd is null and
1605: pen.sspndd_flag='N' and
1622: ,pl_typ_id
1623: ,pl_id
1624: ,oipl_id
1625: ,object_version_number
1626: from ben_prtt_enrt_rslt_f
1627: where prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
1628: and p_effective_date between
1629: effective_start_date and effective_end_date;
1630: --
1692: --
1693: cursor c_check_cvrd_in_plan is
1694: select null
1695: from ben_elig_cvrd_dpnt_f pdp
1696: ,ben_prtt_enrt_rslt_f pen
1697: ,ben_pgm_f pgm
1698: where pdp.dpnt_person_id = p_person_id
1699: and pdp.business_group_id = p_business_group_id
1700: and p_effective_date
2585: ) is
2586: --
2587: -- Declare cursors and local variables
2588: --
2589: l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;
2590: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
2591: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
2592: l_proc varchar2(72) ; -- := g_package||'create_PRTT_ENRT_RESULT';
2593: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
2586: --
2587: -- Declare cursors and local variables
2588: --
2589: l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;
2590: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
2591: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
2592: l_proc varchar2(72) ; -- := g_package||'create_PRTT_ENRT_RESULT';
2593: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
2594: begin
2587: -- Declare cursors and local variables
2588: --
2589: l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;
2590: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
2591: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
2592: l_proc varchar2(72) ; -- := g_package||'create_PRTT_ENRT_RESULT';
2593: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
2594: begin
2595: g_debug := hr_utility.debug_enabled;
2589: l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;
2590: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
2591: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
2592: l_proc varchar2(72) ; -- := g_package||'create_PRTT_ENRT_RESULT';
2593: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
2594: begin
2595: g_debug := hr_utility.debug_enabled;
2596: if g_debug then
2597: l_proc := g_package||'create_PRTT_ENRT_RESULT';
3011: --
3012: l_pl_rec ben_cobj_cache.g_pl_inst_row;
3013: l_oipl_rec ben_cobj_cache.g_oipl_inst_row;
3014: --
3015: l_global_pen_rec ben_prtt_enrt_rslt_f%rowtype;
3016:
3017:
3018: cursor c_interim_enrt is
3019: select pen.rplcs_sspndd_rslt_id
3016:
3017:
3018: cursor c_interim_enrt is
3019: select pen.rplcs_sspndd_rslt_id
3020: from ben_prtt_enrt_rslt_f pen
3021: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id and
3022: pen.business_group_id = p_business_group_id and
3023: p_effective_date between
3024: pen.effective_start_date and pen.effective_end_date;
3144: on a different day in the same enrollment period,then re-cover the dependents.*/
3145: cursor c_check_dup_dep(p_pdp_id number) is
3146: select pdp.*
3147: from ben_elig_cvrd_dpnt_f pdp,
3148: ben_prtt_enrt_rslt_f pen
3149: where pdp.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3150: and pdp.elig_cvrd_dpnt_id = p_pdp_id
3151: and pdp.per_in_ler_id = p_per_in_ler_id
3152: and pdp.cvg_thru_dt <> hr_api.g_eot
3164: FROM ben_per_in_ler pil
3165: WHERE pil.per_in_ler_stat_cd = 'PROCD'
3166: AND pil.lf_evt_ocrd_dt = (SELECT Max(pil1.lf_evt_ocrd_dt)
3167: FROM ben_per_in_ler pil1,
3168: ben_prtt_enrt_rslt_f pen
3169: WHERE pil1.person_id = pil.person_id
3170: AND pil1.per_in_ler_stat_cd = 'PROCD'
3171: AND pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3172: AND pen.prtt_enrt_rslt_stat_cd IS NULL
3311: l_rt_end_dt_rl number;
3312: /* End of Changes for WWBUG: 1646442 */
3313: --
3314: l_proc varchar2(72); -- := g_package||'update_ENROLLMENT';
3315: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
3316: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
3317: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
3318: l_prev_bnft_amt ben_prtt_enrt_rslt_f.bnft_amt%TYPE;
3319: l_enrt_pl_opt_flag ben_pl_f.enrt_pl_opt_flag%TYPE;
3312: /* End of Changes for WWBUG: 1646442 */
3313: --
3314: l_proc varchar2(72); -- := g_package||'update_ENROLLMENT';
3315: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
3316: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
3317: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
3318: l_prev_bnft_amt ben_prtt_enrt_rslt_f.bnft_amt%TYPE;
3319: l_enrt_pl_opt_flag ben_pl_f.enrt_pl_opt_flag%TYPE;
3320: l_prvdd_val ben_bnft_prvdd_ldgr_f.prvdd_Val%type ;
3313: --
3314: l_proc varchar2(72); -- := g_package||'update_ENROLLMENT';
3315: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
3316: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
3317: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
3318: l_prev_bnft_amt ben_prtt_enrt_rslt_f.bnft_amt%TYPE;
3319: l_enrt_pl_opt_flag ben_pl_f.enrt_pl_opt_flag%TYPE;
3320: l_prvdd_val ben_bnft_prvdd_ldgr_f.prvdd_Val%type ;
3321: l_chg boolean := FALSE;
3314: l_proc varchar2(72); -- := g_package||'update_ENROLLMENT';
3315: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
3316: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
3317: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
3318: l_prev_bnft_amt ben_prtt_enrt_rslt_f.bnft_amt%TYPE;
3319: l_enrt_pl_opt_flag ben_pl_f.enrt_pl_opt_flag%TYPE;
3320: l_prvdd_val ben_bnft_prvdd_ldgr_f.prvdd_Val%type ;
3321: l_chg boolean := FALSE;
3322: l_step number(9);
3694: -- Check dt mode is valid
3695: --
3696: dt_api.find_dt_upd_modes
3697: (p_effective_date => p_effective_date,
3698: p_base_table_name => 'BEN_PRTT_ENRT_RSLT_F',
3699: p_base_key_column => 'prtt_enrt_rslt_id',
3700: p_base_key_value => p_prtt_enrt_rslt_id,
3701: p_correction => l_correction,
3702: p_update => l_update,
4571: --
4572: -- Declare cursors and local variables
4573: --
4574: l_proc varchar2(72) ; -- := g_package||'update_PRTT_ENRT_RESULT';
4575: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
4576: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
4577: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
4578:
4579: --
4572: -- Declare cursors and local variables
4573: --
4574: l_proc varchar2(72) ; -- := g_package||'update_PRTT_ENRT_RESULT';
4575: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
4576: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
4577: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
4578:
4579: --
4580: cursor c_old_rslt is
4573: --
4574: l_proc varchar2(72) ; -- := g_package||'update_PRTT_ENRT_RESULT';
4575: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
4576: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
4577: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
4578:
4579: --
4580: cursor c_old_rslt is
4581: select *
4578:
4579: --
4580: cursor c_old_rslt is
4581: select *
4582: from ben_prtt_enrt_rslt_f
4583: where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4584: and prtt_enrt_rslt_stat_cd is null
4585: and p_effective_date between effective_start_date and effective_end_date ;
4586: l_old_rslt c_old_rslt%rowtype ;
4603: and ((per_in_ler_id = c_pil_id
4604: and per_in_ler_ended_id = c_pil_end_id)
4605: or (per_in_ler_ended_id = c_pil_id -- 8972844
4606: and per_in_ler_id = c_pil_end_id)) -- 7197868
4607: and BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_CORR'
4608: union
4609: select 'x'
4610: from BEN_LE_CLSN_N_RSTR
4611: where bkup_tbl_id = c_pen_id
4609: select 'x'
4610: from BEN_LE_CLSN_N_RSTR
4611: where bkup_tbl_id = c_pen_id
4612: and per_in_ler_id = c_pil_id
4613: and BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F'
4614: and c_eff_strt_dt = EFFECTIVE_START_DATE
4615: and c_eff_end_dt = EFFECTIVE_END_DATE
4616: and c_cvg_strt_dt = ENRT_CVG_STRT_DT
4617: and c_cvg_thru_dt = ENRT_CVG_THRU_DT
4782: PLIP_ORDR_NUM,
4783: PTIP_ORDR_NUM,
4784: OIPL_ORDR_NUM)
4785: values (
4786: 'BEN_PRTT_ENRT_RSLT_F_CORR',
4787: l_old_rslt.COMP_LVL_CD,
4788: l_old_rslt.PEN_ATTRIBUTE16,
4789: l_old_rslt.PEN_ATTRIBUTE17,
4790: l_old_rslt.PEN_ATTRIBUTE18,
5201: --
5202: -- Declare cursors and local variables
5203: --
5204: l_proc varchar2(72) ; -- := g_package||'delete_PRTT_ENRT_RESULT';
5205: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
5206: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
5207: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
5208: --
5209: begin
5202: -- Declare cursors and local variables
5203: --
5204: l_proc varchar2(72) ; -- := g_package||'delete_PRTT_ENRT_RESULT';
5205: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
5206: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
5207: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
5208: --
5209: begin
5210: --
5203: --
5204: l_proc varchar2(72) ; -- := g_package||'delete_PRTT_ENRT_RESULT';
5205: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
5206: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
5207: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
5208: --
5209: begin
5210: --
5211: g_debug := hr_utility.debug_enabled;
5698: ,p_pil_id out nocopy number
5699: ) is
5700: Cursor c1 is
5701: Select min(effective_start_date),pen.per_in_ler_id
5702: From ben_prtt_enrt_rslt_f pen
5703: Where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5704: And pen.business_group_id = p_business_group_id
5705: and pen.prtt_enrt_rslt_stat_cd is null
5706: Group by pen.per_in_ler_id
5758: ,epe.elig_per_elctbl_chc_id
5759: ,epe.per_in_ler_id
5760: ,epe.object_version_number epe_ovn
5761: ,enb.enrt_bnft_id
5762: from ben_prtt_enrt_rslt_f pen
5763: ,ben_elig_per_elctbl_chc epe
5764: ,ben_per_in_ler pil
5765: ,ben_enrt_bnft enb
5766: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5791: ,to_number(null) elig_per_elctbl_chc_id
5792: ,to_number(null) per_in_ler_id
5793: ,to_number(null) epe_ovn
5794: ,to_number(null) enrt_bnft_id
5795: from ben_prtt_enrt_rslt_f pen
5796: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5797: and p_effective_date between pen.effective_start_date
5798: and pen.effective_end_date
5799: ;
5903: cursor c_unrestricted_future (p_per_in_ler_id number,
5904: p_prtt_enrt_rslt_id number,
5905: p_effective_start_date date) is
5906: select null
5907: from ben_prtt_enrt_rslt_f pen,
5908: ben_per_in_ler pil,
5909: ben_ler_f ler
5910: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5911: and pen.effective_start_date > p_effective_start_date
6335: ,oipl.opt_id
6336: ,pl.name pl_name
6337: ,opt.name opt_name
6338: ,pl.imptd_incm_calc_cd
6339: from ben_prtt_enrt_rslt_f pen
6340: ,ben_elig_per_elctbl_chc epe
6341: ,ben_per_in_ler pil
6342: ,ben_enrt_bnft enb
6343: ,ben_oipl_f oipl
6634:
6635: cursor c_new_cvg_strt_dt(l_pl_typ_id number, l_ptip_id number, l_pl_id number, l_oipl_id number,
6636: p_enrt_cvg_strt_dt date ) is
6637: select max(enrt_cvg_strt_dt)
6638: from ben_prtt_enrt_rslt_f pen
6639: where pen.per_in_ler_id = nvl(p_per_in_ler_id, -1) -- is this OK?
6640: and ((pen.pgm_id is null and pen.pl_typ_id is not null
6641: and pen.pl_typ_id = nvl(l_pl_typ_id, -1))
6642: or (pen.ptip_id is not null and pen.ptip_id = nvl(l_ptip_id, -1))
6644: and pen.no_lngr_elig_flag = 'N'
6645: and ((l_pl_id = -999 and pen.enrt_cvg_strt_dt >= p_enrt_cvg_strt_dt)
6646: or ((pen.pl_id = l_pl_id and -- Bug 2847110
6647: -- Bug# 12662110 start
6648: exists (select * from ben_prtt_enrt_rslt_f
6649: where per_in_ler_id = p_per_in_ler_id
6650: and pl_id = l_pl_id
6651: and pl_typ_id = l_pl_typ_id
6652: and ENRT_CVG_THRU_DT = hr_api.g_eot
6660: -- Bug #12662110 start
6661: and ((pen.pl_id = l_pl_id and nvl(pen.oipl_id, -1) = nvl(l_oipl_id, -1))
6662: or (l_pl_id <> pen.pl_id)
6663: or ((nvl(pen.oipl_id, -1) <> nvl(l_oipl_id, -1) and pen.pl_id = l_pl_id)
6664: and not exists (select * from ben_prtt_enrt_rslt_f
6665: where oipl_id = l_oipl_id
6666: and per_in_ler_id = p_per_in_ler_id
6667: and ENRT_CVG_THRU_DT = hr_api.g_eot
6668: and EFFECTIVE_END_DATE = hr_api.g_eot
6680: ;
6681: --
6682: cursor c_intm_other_rslt(p_per_in_ler_id number) is
6683: select 'Y'
6684: from ben_prtt_enrt_rslt_f pen
6685: where pen.rplcs_sspndd_rslt_id = l_pen.rplcs_sspndd_rslt_id
6686: and pen.business_group_id = p_business_group_id
6687: and pen.sspndd_flag = 'Y'
6688: and pen.enrt_cvg_thru_dt = hr_api.g_eot
6693: -- Bug 6165501 : Added union clause if its a Correction case, and interim has already
6694: -- been end-dated by the new pil, then no need to call delete_enrollment for the interim
6695: UNION
6696: select 'Y'
6697: from ben_prtt_enrt_rslt_f pen, ben_le_clsn_n_rstr cls
6698: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6699: and pen.prtt_enrt_rslt_stat_cd is null
6700: and pen.enrt_cvg_thru_dt <> hr_api.g_eot
6701: and pen.per_in_ler_id = p_per_in_ler_id
6699: and pen.prtt_enrt_rslt_stat_cd is null
6700: and pen.enrt_cvg_thru_dt <> hr_api.g_eot
6701: and pen.per_in_ler_id = p_per_in_ler_id
6702: and cls.bkup_tbl_id = pen.prtt_enrt_rslt_id
6703: and cls.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F_CORR'
6704: and cls.per_in_ler_ended_id = p_per_in_ler_id;
6705: -- End Bug 6165501
6706:
6707: --
6709: select pen.prtt_enrt_rslt_id,
6710: pen.object_version_number,
6711: pen.effective_start_date,
6712: pen.effective_end_date
6713: from ben_prtt_enrt_rslt_f pen
6714: where pen.prtt_enrt_rslt_id = l_pen.rplcs_sspndd_rslt_id
6715: and pen.business_group_id = p_business_group_id
6716: and pen.prtt_enrt_rslt_stat_cd is null
6717: and p_effective_date between
6720: -- 12583091
6721: --
6722: cursor c_get_interim is
6723: select pen.*
6724: from ben_prtt_enrt_rslt_f pen
6725: where pen.prtt_enrt_rslt_id =
6726: (select pen2.rplcs_sspndd_rslt_id
6727: from ben_prtt_enrt_rslt_f pen2
6728: where pen2.per_in_ler_id = p_per_in_ler_id
6723: select pen.*
6724: from ben_prtt_enrt_rslt_f pen
6725: where pen.prtt_enrt_rslt_id =
6726: (select pen2.rplcs_sspndd_rslt_id
6727: from ben_prtt_enrt_rslt_f pen2
6728: where pen2.per_in_ler_id = p_per_in_ler_id
6729: and pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6730: and pen2.sspndd_flag = 'Y'
6731: and pen2.prtt_enrt_rslt_stat_cd is null
6745: p_per_in_ler_id number ,
6746: p_effective_date date
6747: ) is
6748: select 'x'
6749: from ben_prtt_enrt_rslt_f pen
6750: where pen.person_id = p_person_id
6751: and nvl(p_pgm_id,-1) = nvl(pen.pgm_id,-1)
6752: and nvl(p_pl_id ,-1) = nvl(pen.pl_id,-1)
6753: and nvl(p_oipl_id,-1) = nvl(pen.oipl_id, -1)
6766: p_per_in_ler_id number ,
6767: p_effective_date date
6768: ) is
6769: select 'x'
6770: from ben_prtt_enrt_rslt_f pen
6771: where pen.person_id = p_person_id
6772: and nvl(p_pgm_id,-1) = nvl(pen.pgm_id,-1)
6773: and p_pl_id = pen.pl_id
6774: and (p_per_in_ler_id <> pen.per_in_ler_id
6790: p_per_in_ler_id number ,
6791: p_effective_date date
6792: ) is
6793: select 'x'
6794: from ben_prtt_enrt_rslt_f pen
6795: where pen.person_id = p_person_id
6796: and p_pgm_id = pen.pgm_id
6797: and p_ptip_id = pen.ptip_id
6798: and (p_per_in_ler_id <> pen.per_in_ler_id
6813: l_per_in_ler c_per_in_ler%rowtype;
6814: --
6815: cursor c_pen_ovn (p_prtt_enrt_rslt_id number, p_effective_date date ) is
6816: select pen.object_version_number,pen.effective_start_date --4663971
6817: from ben_prtt_enrt_rslt_f pen
6818: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6819: and p_effective_date between pen.effective_start_date and pen.effective_end_date ;
6820: l_pen_ovn c_pen_ovn%rowtype;
6821: --
6867: p_oipl_id number ,
6868: p_per_in_ler_id number
6869: ) is
6870: select prtt_enrt_rslt_id
6871: from ben_prtt_enrt_rslt_f pen
6872: where pen.person_id = p_person_id
6873: and nvl(p_pgm_id,-1) = nvl(pen.pgm_id,-1)
6874: and nvl(p_pl_id ,-1) = nvl(pen.pl_id,-1)
6875: and nvl(p_oipl_id,-1) = nvl(pen.oipl_id, -1)
6883: -- Bug 2689915
6884: cursor c_ler is
6885: select typ_cd
6886: from ben_ler_f ler,
6887: ben_prtt_enrt_rslt_f pen
6888: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6889: and pen.ler_id = ler.ler_id ;
6890: --
6891: cursor c_old_corr_pen (c_pil_id number
6902:
6903: cursor c_pen_obj_no (c_pil_id number
6904: ,c_pen_id number ) is
6905: select object_version_number
6906: from ben_prtt_enrt_rslt_f pen
6907: where pen.prtt_enrt_rslt_id = c_pen_id
6908: and pen.per_in_ler_id = c_pil_id
6909: and p_effective_date between
6910: pen.effective_start_date and pen.effective_end_date ;
6914: cursor c_crntly_enrd_flag(p_elig_pe_elctbl_chc_id number) is
6915: select epe.crntly_enrd_flag,ler.typ_cd
6916: from ben_elig_per_elctbl_chc epe,
6917: ben_ler_f ler,
6918: ben_prtt_enrt_rslt_f pen
6919: where epe.per_in_ler_id=p_per_in_ler_id
6920: and epe.elig_per_elctbl_chc_id=p_elig_pe_elctbl_chc_id
6921: and epe.prtt_enrt_rslt_id=pen.prtt_enrt_rslt_id
6922: and pen.per_in_ler_id=p_per_in_ler_id
6929: --Added for Bug#5018328
6930:
6931: cursor c_crntly_enrd_flag_unres(p_elig_pe_elctbl_chc_id number) is
6932: select ler.typ_cd
6933: from ben_prtt_enrt_rslt_f pen,
6934: ben_elig_per_elctbl_chc epe,
6935: ben_ler_f ler
6936: where epe.elig_per_elctbl_chc_id=p_elig_pe_elctbl_chc_id
6937: and epe.per_in_ler_id=p_per_in_ler_id
6948: p_per_in_ler_id number,
6949: p_enrt_cvg_strt_dt date,
6950: p_effective_start_date date) is
6951: select 'Y'
6952: from ben_prtt_enrt_rslt_f pen
6953: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6954: and pen.prtt_enrt_rslt_stat_cd is null
6955: and pen.enrt_cvg_thru_dt = to_date('4712/12/31','rrrr/mm/dd')
6956: and pen.per_in_ler_id <> p_per_in_ler_id
6987: --The cursor is written based on the assumption that there will be only plan per plan type
6988: --elected in a Life Event.
6989: cursor c_get_epe(p_pl_typ_id number) is
6990: SELECT epe.*
6991: FROM ben_prtt_enrt_rslt_f pen,
6992: ben_elig_per_elctbl_chc epe
6993: WHERE pen.per_in_ler_id = p_per_in_ler_id
6994: AND pen.business_group_id = p_business_group_id
6995: AND epe.per_in_ler_id = pen.per_in_ler_id
7009: FROM ben_prtt_rt_val oldprv,
7010: ben_prtt_rt_val curprv,
7011: ben_acty_base_rt_f abr,
7012: ben_acty_base_rt_f abr2 ,
7013: ben_prtt_enrt_rslt_f pen
7014: WHERE curprv.business_group_id=oldprv.business_group_id
7015: AND curprv.per_in_ler_id = oldprv.ended_per_in_ler_id
7016: AND oldprv.rt_end_dt <> hr_api.g_eot
7017: AND oldprv.prtt_rt_val_stat_cd IS NULL
7029: and oldprv.prtt_enrt_rslt_id = curprv.prtt_enrt_rslt_id -- 16068560
7030: AND pen.business_group_id = p_business_group_id
7031: and curprv.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id
7032: AND EXISTS (SELECT pen1.pl_typ_id
7033: FROM ben_prtt_enrt_rslt_f pen1
7034: WHERE pen1.business_group_id = pen.business_group_id
7035: AND pen1.per_in_ler_id = curprv.per_in_ler_id
7036: AND pen1.pl_typ_id = pen.pl_typ_id
7037: AND pen1.prtt_enrt_rslt_id = curprv.prtt_enrt_rslt_id
7081: WHERE bcr.bkup_tbl_id=cp_prtt_enrt_rslt_id
7082: AND bcr.per_in_ler_id <> cp_per_in_ler_id
7083: AND bcr.person_id = cp_person_id -- Bug 12356840
7084: --AND bcr.bkup_tbl_typ_cd LIKE '%CORR' -- Bug 12356840
7085: AND bcr.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F_CORR' ;
7086:
7087: l_get_correction_info c_get_correction_info%ROWTYPE;
7088:
7089: -- Local variable declaration.
7122: l_rqd_perd_enrt_nenrt_uom varchar2(30);
7123: l_rqd_perd_enrt_nenrt_val number;
7124: l_rqd_perd_enrt_nenrt_rl number;
7125: l_level varchar2(30);
7126: l_other_pen_rec ben_prtt_enrt_rslt_f%rowtype;
7127: l_intm_other_rslt varchar2(30) := 'N';
7128: l_new_enrollment varchar2(1) := 'N' ;
7129: l_object_version_number number ;
7130: --
7168: l_oper_in_ler_id number;
7169: l_nper_in_ler_id number;
7170:
7171: cursor c_grt_new_ovn is
7172: select object_version_number from ben_prtt_enrt_rslt_f
7173: where prtt_enrt_rslt_id = g_new_prtt_enrt_rslt_id;
7174:
7175: cursor c_get_actn_items is
7176: select actn.* from ben_prtt_enrt_actn_f actn,
7200: and actn.prtt_enrt_actn_id = ctfn.prtt_enrt_actn_id
7201: and actn.cmpltd_dt is not null; -- Bug 8669907: Get only the completed action items for creation
7202:
7203: cursor c_chk_same_event(c_prtt_enrt_rslt_id number) is
7204: select per_in_ler_id from ben_prtt_enrt_rslt_f
7205: where prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
7206: and prtt_enrt_rslt_stat_cd is NULL;
7207:
7208: l_ctfn_rec c_get_ctfn%rowtype;
7216:
7217: /* Added for Bug 8669907 */
7218: cursor c_chk_same_comp_obj(c_new_pen_id number,c_old_pen_id number,c_per_in_ler_id number) is
7219: select 'Y' from
7220: ben_prtt_enrt_rslt_f new_pen,
7221: ben_prtt_enrt_rslt_f old_pen
7222: where new_pen.prtt_enrt_rslt_id = c_new_pen_id
7223: and old_pen.prtt_enrt_rslt_id = c_old_pen_id
7224: and old_pen.per_in_ler_id = c_per_in_ler_id
7217: /* Added for Bug 8669907 */
7218: cursor c_chk_same_comp_obj(c_new_pen_id number,c_old_pen_id number,c_per_in_ler_id number) is
7219: select 'Y' from
7220: ben_prtt_enrt_rslt_f new_pen,
7221: ben_prtt_enrt_rslt_f old_pen
7222: where new_pen.prtt_enrt_rslt_id = c_new_pen_id
7223: and old_pen.prtt_enrt_rslt_id = c_old_pen_id
7224: and old_pen.per_in_ler_id = c_per_in_ler_id
7225: and new_pen.per_in_ler_id = c_per_in_ler_id
7236: cvg_mlt_cd,
7237: mx_wout_ctfn_val
7238: from ben_enrt_bnft bnft,
7239: ben_elig_per_elctbl_chc epe,
7240: ben_prtt_enrt_rslt_f pen
7241: where epe.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
7242: and epe.per_in_ler_id = c_per_in_ler_id
7243: and pen.per_in_ler_id = c_per_in_ler_id
7244: and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
7258: FROM ben_per_in_ler pil
7259: WHERE pil.per_in_ler_stat_cd = 'PROCD'
7260: AND pil.lf_evt_ocrd_dt = (SELECT Max(pil1.lf_evt_ocrd_dt)
7261: FROM ben_per_in_ler pil1,
7262: ben_prtt_enrt_rslt_f pen ------------Bug 8688513
7263: WHERE pil1.person_id = pil.person_id
7264: AND pil1.per_in_ler_stat_cd = 'PROCD'
7265: AND pen.prtt_enrt_rslt_id = p_pen_id
7266: AND pen.prtt_enrt_rslt_stat_cd IS NULL
7272: cursor c_get_ended_pen(p_per_in_ler_id number,
7273: p_pl_typ_id number,
7274: p_pgm_id number) is
7275: SELECT *
7276: FROM ben_prtt_enrt_rslt_f pen
7277: WHERE pen.per_in_ler_id = p_per_in_ler_id
7278: AND pen.pl_typ_id = p_pl_typ_id
7279: AND pen.pgm_id = p_pgm_id
7280: AND enrt_cvg_thru_dt <> hr_api.g_eot
7284: l_get_ended_pen l_get_ended_pen1;
7285:
7286: cursor c_last_pil_pen(p_per_in_ler_id number,p_pen_id number) is
7287: SELECT *
7288: FROM ben_prtt_enrt_rslt_f pen
7289: WHERE pen.per_in_ler_id = p_per_in_ler_id
7290: AND pen.prtt_enrt_rslt_id = p_pen_id
7291: AND pen.prtt_enrt_rslt_stat_cd IS NULL;
7292:
7295: cursor c_last_pil_pen1(p_per_in_ler_id number,
7296: p_pl_id number,
7297: p_pgm_id number) is
7298: SELECT *
7299: FROM ben_prtt_enrt_rslt_f pen
7300: WHERE pen.per_in_ler_id = p_per_in_ler_id
7301: AND pen.pl_id = p_pl_id
7302: and pen.pgm_id = p_pgm_id
7303: AND pen.prtt_enrt_rslt_stat_cd IS NULL;
7315: from ben_enrt_rt ecr
7316: ,ben_enrt_bnft enb
7317: where ecr.prtt_rt_val_id = p_prtt_rt_val_id
7318: and exists (select null
7319: from ben_prtt_enrt_rslt_f pen
7320: where pen.prtt_enrt_rslt_stat_cd is null
7321: and pen.sspndd_flag = 'Y'
7322: and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
7323: and pen.per_in_ler_id = p_per_in_ler_id
7371: from ben_enrt_rt ecr
7372: ,ben_enrt_bnft enb
7373: where ecr.prtt_rt_val_id = p_prtt_rt_val_id
7374: and exists (select null
7375: from ben_prtt_enrt_rslt_f pen
7376: where pen.prtt_enrt_rslt_stat_cd is null
7377: and pen.sspndd_flag = 'Y'
7378: and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
7379: and pen.per_in_ler_id = p_per_in_ler_id
10552: update ben_le_clsn_n_rstr
10553: set effective_start_date = p_effective_start_date
10554: where per_in_ler_id = l_corr_pil_id
10555: and bkup_tbl_id = p_prtt_enrt_rslt_id
10556: and bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F_CORR';
10557: --
10558: -- added till here for bug 6963660 */
10559: close c_pen_obj_no ;
10560: end if ;
11362: and business_group_id = p_business_group_id;
11363: --
11364: cursor c_prtt_total_bnft_amt(p_acrs_ptip_cvg_id number) is
11365: select sum(prt.bnft_amt)
11366: from ben_prtt_enrt_rslt_f prt,
11367: ben_ptip_f ptp
11368: where prt.pgm_id = p_pgm_id
11369: and prt.person_id = p_person_id
11370: and prt.sspndd_flag = 'N' -- # 3626176
11386: --here.
11387: --
11388: cursor c_prtt_total_bnft_amt_sspndd(p_acrs_ptip_cvg_id number) is
11389: select sum(prt.bnft_amt)
11390: from ben_prtt_enrt_rslt_f prt,
11391: ben_ptip_f ptp
11392: where prt.pgm_id = p_pgm_id
11393: and prt.person_id = p_person_id
11394: and ( prt.sspndd_flag = 'Y' or (
11393: and prt.person_id = p_person_id
11394: and ( prt.sspndd_flag = 'Y' or (
11395: prt.sspndd_flag = 'N' and not exists (
11396: select 'x'
11397: from ben_prtt_enrt_rslt_f prt1
11398: where prt1.person_id = p_person_id
11399: and prt1.pgm_id = p_pgm_id
11400: and PRT1.rplcs_sspndd_rslt_id = prt.prtt_enrt_rslt_id
11401: and prt1.per_in_ler_id = prt.per_in_ler_id
11590: ,r.person_id
11591: ,oipl.opt_id
11592: ,r.ptip_id
11593: ,r.enrt_mthd_cd --Bug 2200139
11594: From ben_prtt_enrt_rslt_f r,
11595: ben_oipl_f oipl,
11596: ben_elig_per_elctbl_chc c
11597: Where c.per_in_ler_id = p_per_in_ler_id
11598: and r.prtt_enrt_rslt_id = c.prtt_enrt_rslt_id
11632: ,r.person_id
11633: ,oipl.opt_id
11634: ,r.ptip_id
11635: ,r.enrt_mthd_cd
11636: From ben_prtt_enrt_rslt_f r,
11637: ben_oipl_f oipl
11638: Where r.per_in_ler_id <> p_per_in_ler_id
11639: and r.person_id = p_person_id
11640: and nvl(r.pgm_id,-1) = p_pgm_id
11692: ,r.person_id
11693: ,oipl.opt_id
11694: ,r.ptip_id
11695: ,r.enrt_mthd_cd
11696: From ben_prtt_enrt_rslt_f r
11697: ,ben_elig_per_elctbl_chc c
11698: ,ben_oipl_f oipl
11699: ,ben_pil_elctbl_chc_popl pel
11700: Where c.per_in_ler_id = p_per_in_ler_id
11747: l_effective_start_date date;
11748: l_effective_end_date date;
11749: l_pl_typ_id number;
11750: l_total_amt number;
11751: l_other_pen_rec ben_prtt_enrt_rslt_f%rowtype;
11752: l_rqd_perd_enrt_nenrt_uom varchar2(30);
11753: l_rqd_perd_enrt_nenrt_val number;
11754: l_rqd_perd_enrt_nenrt_rl number;
11755: l_level varchar2(30);
11761: cursor no_lngr_elig_pgm_c is
11762: select pen.prtt_enrt_rslt_id,
11763: pen.object_version_number,
11764: pen.effective_start_date
11765: from ben_prtt_enrt_rslt_f pen
11766: where pen.per_in_ler_id = p_per_in_ler_id
11767: and pen.pgm_id = p_pgm_id
11768: and pen.prtt_enrt_rslt_stat_cd is null
11769: and pen.no_lngr_elig_flag = 'Y'
11789: cursor no_lngr_elig_pnip_c is
11790: select pen.prtt_enrt_rslt_id,
11791: pen.object_version_number,
11792: pen.effective_start_date
11793: from ben_prtt_enrt_rslt_f pen
11794: where pen.pgm_id is null
11795: and pl_typ_id = l_pl_typ_id
11796: and pen.per_in_ler_id = p_per_in_ler_id
11797: and pen.prtt_enrt_rslt_stat_cd is null
11830: -- end 3123698
11831: --
11832: cursor c_prtt_total_bnft_amt(p_acrs_ptip_cvg_id number) is
11833: select sum(prt.bnft_amt)
11834: from ben_prtt_enrt_rslt_f prt,
11835: ben_ptip_f ptp
11836: where prt.pgm_id = p_pgm_id
11837: and prt.person_id = p_person_id
11838: and prt.sspndd_flag = 'N' -- # 3626176
11854: --here.
11855: --
11856: cursor c_prtt_total_bnft_amt_sspndd(p_acrs_ptip_cvg_id number) is
11857: select sum(prt.bnft_amt)
11858: from ben_prtt_enrt_rslt_f prt,
11859: ben_ptip_f ptp
11860: where prt.pgm_id = p_pgm_id
11861: and prt.person_id = p_person_id
11862: and ( prt.sspndd_flag = 'Y' or (
11861: and prt.person_id = p_person_id
11862: and ( prt.sspndd_flag = 'Y' or (
11863: prt.sspndd_flag = 'N' and not exists (
11864: select 'x'
11865: from ben_prtt_enrt_rslt_f prt1
11866: where prt1.person_id = p_person_id
11867: and prt1.pgm_id = p_pgm_id
11868: and PRT1.rplcs_sspndd_rslt_id = prt.prtt_enrt_rslt_id
11869: and prt1.per_in_ler_id = prt.per_in_ler_id
11901: ,c.dpnt_cvg_strt_dt_rl
11902: ,pil.ler_id
11903: ,c.alws_dpnt_dsgn_flag
11904: ,pl.bnf_dsgn_cd
11905: From ben_prtt_enrt_rslt_f r,
11906: ben_elig_per_elctbl_chc c,
11907: ben_per_in_ler pil,
11908: ben_pl_f pl
11909: Where r.pl_typ_id = p_pl_typ_id