[Home] [Help]
652: 115.306 24-Jan-07 swjain Bug 5739530 - Updated procedure delete_enrollment, update future prvs only if
653: they haven't been updated previously.
654: 115.307 30-Jan-07 rgajula Bug 5768795 - Moved the code for validation of "Coverage Across Plan Types" from multi_rows_edit
655: to a seperate procedure chk_coverage_across_plan_types.
656: 115.309 21-feb-07 rgajula Bug 5859714 :Updated the field BEN_PER_IN_LER.BCKT_PER_IN_LER_ID of
657: the backed out L.E with the per_in_ler_id of the causing L.E
658: 115.310 11-May-07 ikasired Bug 5985777 Backup the future completed action items.
659: 115.311 14-May-06 rtagarra Bug 6000303 : Defer Deenrollment ENH.
660: 115.312 04-Jul-07 swjain Bug 6165501 : Updated cursor c_intm_other_rslt in delete_enrollment procedure
824: ,pea.effective_start_date
825: ,pea.effective_end_date
826: ,pil.lf_evt_ocrd_dt
827: from ben_prtt_enrt_actn_f pea,
828: ben_per_in_ler pil
829: where pea.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
830: and pea.business_group_id = p_business_group_id
831: and p_effective_date
832: between pea.effective_start_date and pea.effective_end_date
1636: ecr.actl_prem_id,
1637: pil.lf_evt_ocrd_dt,
1638: pil.ler_id
1639: from ben_enrt_prem ecr,
1640: ben_per_in_ler pil,
1641: ben_elig_per_elctbl_chc epe
1642: where ecr.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
1643: and epe.elig_per_elctbl_chc_id = ecr.elig_per_elctbl_chc_id
1644: and pil.per_in_ler_id = epe.per_in_ler_id
1662: ppe.std_prem_val,
1663: ppe.actl_prem_id,
1664: ppe.object_version_number
1665: from ben_prtt_prem_f ppe
1666: -- ben_per_in_ler pil
1667: where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1668: and ppe.actl_prem_id = p_actl_prem_id
1669: and p_effective_date between
1670: ppe.effective_start_date and ppe.effective_end_date;
3030: pil.lf_evt_ocrd_dt,
3031: pil.ler_id,
3032: epe.elig_per_elctbl_chc_id
3033: from ben_enrt_prem ecr,
3034: ben_per_in_ler pil,
3035: ben_elig_per_elctbl_chc epe
3036: where epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3037: and epe.elig_per_elctbl_chc_id = ecr.elig_per_elctbl_chc_id
3038: and pil.per_in_ler_id = epe.per_in_ler_id
3057: ppe.actl_prem_id,
3058: ppe.object_version_number,
3059: ppe.effective_start_date
3060: from ben_prtt_prem_f ppe,
3061: ben_per_in_ler pil
3062: where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3063: and ppe.actl_prem_id = p_actl_prem_id
3064: /*
3065: CODE PRIOR TO WWBUG: 1646442
3081: p_actl_prem_id in number,
3082: p_ppe_dt_to_use in date) is
3083: select ppe.*
3084: from ben_prtt_prem_f ppe,
3085: ben_per_in_ler pil
3086: where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3087: and ppe.actl_prem_id = p_actl_prem_id
3088: and p_ppe_dt_to_use <= ppe.effective_start_date
3089: and pil.per_in_ler_id=ppe.per_in_ler_id
3117: pdp.cvg_thru_dt --9881657
3118: from ben_elig_per_elctbl_chc epe,
3119: ben_elig_dpnt egd,
3120: ben_elig_cvrd_dpnt_f pdp,
3121: ben_per_in_ler egd_pil,
3122: ben_per_in_ler pdp_pil
3123: where epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3124: and epe.per_in_ler_id = p_per_in_ler_id
3125: and epe.elig_per_elctbl_chc_id = egd.elig_per_elctbl_chc_id
3118: from ben_elig_per_elctbl_chc epe,
3119: ben_elig_dpnt egd,
3120: ben_elig_cvrd_dpnt_f pdp,
3121: ben_per_in_ler egd_pil,
3122: ben_per_in_ler pdp_pil
3123: where epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3124: and epe.per_in_ler_id = p_per_in_ler_id
3125: and epe.elig_per_elctbl_chc_id = egd.elig_per_elctbl_chc_id
3126: and egd.elig_cvrd_dpnt_id = pdp.elig_cvrd_dpnt_id
3160: l_check_dup_dep c_check_dup_dep%rowtype;
3161:
3162: cursor c_get_last_pil is
3163: SELECT pil.per_in_ler_id
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
3163: SELECT pil.per_in_ler_id
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
3196: select egd.*,pdp.elig_cvrd_dpnt_id cvrd_dpnt_id
3197: from ben_elig_per_elctbl_chc epe,
3198: ben_elig_dpnt egd,
3199: ben_elig_cvrd_dpnt_f pdp,
3200: ben_per_in_ler egd_pil,
3201: ben_per_in_ler pdp_pil
3202: where epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3203: and epe.per_in_ler_id = p_per_in_ler_id
3204: AND epe.crntly_enrd_flag = 'Y'
3197: from ben_elig_per_elctbl_chc epe,
3198: ben_elig_dpnt egd,
3199: ben_elig_cvrd_dpnt_f pdp,
3200: ben_per_in_ler egd_pil,
3201: ben_per_in_ler pdp_pil
3202: where epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3203: and epe.per_in_ler_id = p_per_in_ler_id
3204: AND epe.crntly_enrd_flag = 'Y'
3205: and epe.elig_per_elctbl_chc_id = egd.elig_per_elctbl_chc_id
3250: c_bnft_prvdr_pool_id number ,
3251: c_prtt_enrt_rslt_id number) is
3252: select prvdd_val
3253: from ben_bnft_prvdd_ldgr_f bpl,
3254: ben_per_in_ler pil
3255: where bpl.acty_base_rt_id = c_acty_base_rt_id
3256: and bpl.bnft_prvdr_pool_id = c_bnft_prvdr_pool_id
3257: and bpl.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
3258: and pil.per_in_ler_id = bpl.per_in_ler_id
5507: ,epe.ptip_id
5508: ,epe.pl_id
5509: ,pil.ler_id
5510: from ben_elig_per_elctbl_chc epe
5511: ,ben_per_in_ler pil
5512: where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
5513: and epe.business_group_id = p_business_group_id
5514: and epe.per_in_ler_id = pil.per_in_ler_id
5515: and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
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
5767: and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id (+)
5768: and nvl(epe.elig_per_elctbl_chc_id, -1) = enb.elig_per_elctbl_chc_id (+)
5804: ,epe.per_in_ler_id
5805: ,epe.object_version_number epe_ovn
5806: ,enb.enrt_bnft_id
5807: from ben_elig_per_elctbl_chc epe
5808: ,ben_per_in_ler pil
5809: ,ben_enrt_bnft enb
5810: where epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5811: and nvl(epe.elig_per_elctbl_chc_id, -1) = enb.elig_per_elctbl_chc_id (+)
5812: and pil.per_in_ler_id=epe.per_in_ler_id
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
5912: and pen.per_in_ler_id = pil.per_in_ler_id
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
6344: ,ben_pl_f pl
6345: ,ben_opt_f opt
6381: ppe.effective_start_date,
6382: ppe.effective_end_date,
6383: ppe.actl_prem_id
6384: from ben_prtt_prem_f ppe,
6385: ben_per_in_ler pil
6386: where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6387: and pil.per_in_ler_id=ppe.per_in_ler_id
6388: and pil.business_group_id=ppe.business_group_id
6389: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
6395: ppe.effective_start_date,
6396: ppe.effective_end_date,
6397: ppe.actl_prem_id
6398: from ben_prtt_prem_f ppe,
6399: ben_per_in_ler pil
6400: where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6401: and pil.per_in_ler_id=ppe.per_in_ler_id
6402: and pil.business_group_id=ppe.business_group_id
6403: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
6411: ppe1.effective_start_date,
6412: ppe1.effective_end_date,
6413: ppe1.actl_prem_id
6414: from ben_prtt_prem_f ppe1,
6415: ben_per_in_ler pil
6416: where ppe1.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6417: and pil.per_in_ler_id=ppe1.per_in_ler_id
6418: and pil.business_group_id=ppe1.business_group_id
6419: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
6420: and ppe1.effective_start_date > p_ppe_dt_to_use
6421: and not exists
6422: (select 1
6423: from ben_prtt_prem_f ppe2,
6424: ben_per_in_ler pil
6425: where ppe2.prtt_prem_id = ppe1.prtt_prem_id
6426: and ppe2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6427: and pil.per_in_ler_id=ppe2.per_in_ler_id
6428: and pil.business_group_id=ppe2.business_group_id
6474: ,abr.element_type_id
6475: ,abr.input_value_id
6476: from ben_prtt_rt_val prv
6477: ,ben_acty_base_rt_f abr
6478: ,ben_per_in_ler pil -- Bug 8573195,not to pick the rows of backed out lifevent for rate adjustment
6479: where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6480: and prv.rt_end_dt > p_rt_end_dt
6481: and prv.per_in_ler_id <> p_per_in_ler_id
6482: and prv.rt_end_dt <> hr_api.g_eot
6804: and pen.effective_end_date = hr_api.g_eot;
6805: --
6806: cursor c_per_in_ler (p_per_in_ler_id number) is
6807: select *
6808: from ben_per_in_ler
6809: where per_in_ler_id = p_per_in_ler_id
6810: and business_group_id = p_business_group_id;
6811:
6812: --
7047: -- Bug No 5637595 Added to get the lf_evt_ocrd_dt of the pil
7048: --
7049: cursor get_lf_evt_dt is
7050: select lf_evt_ocrd_dt from
7051: ben_per_in_ler where
7052: per_in_ler_id = p_per_in_ler_id and
7053: per_in_ler_stat_cd not in ('VOIDD','BCKDT');
7054: l_lf_evt_ocrd_dt date;
7055:
7254: --
7255: ------------Bug 8222481
7256: cursor c_get_last_pil(p_person_id number,p_pen_id number) is
7257: SELECT pil.per_in_ler_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
7257: SELECT pil.per_in_ler_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
10759: ,pbf.dsgn_strt_dt
10760: ,pbf.dsgn_thru_dt
10761: ,pbf.per_in_ler_id
10762: from ben_pl_bnf_f pbf,
10763: ben_per_in_ler pil
10764: where pbf.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
10765: and pbf.business_group_id = p_business_group_id
10766: and p_effective_date between pbf.effective_start_date
10767: and pbf.effective_end_date
10774: is
10775: select pbf.pl_bnf_id
10776: ,pbf.object_version_number
10777: from ben_pl_bnf_f pbf,
10778: ben_per_in_ler pil
10779: where pbf.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
10780: and p_effective_date
10781: between pbf.effective_start_date
10782: and pbf.effective_end_date
10991: ,ecd.cvg_thru_dt
10992: ,ecd.object_version_number
10993: ,ecd.per_in_ler_id
10994: from ben_elig_cvrd_dpnt_f ecd,
10995: ben_per_in_ler pil
10996: where ecd.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id
10997: and ecd.cvg_strt_dt is not null
10998: -- and ecd.cvg_thru_dt = hr_api.g_eot
10999: and ecd.business_group_id=p_business_group_id
11566: cursor c_le_date(p_per_in_ler_id number) is
11567: select
11568: lf_evt_ocrd_dt
11569: from
11570: ben_per_in_ler pil
11571: where pil.per_in_ler_id = p_per_in_ler_id ;
11572: --
11573: l_lf_evt_ocrd_dt date ;
11574: --
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
11910: and r.per_in_ler_id = p_per_in_ler_id
11911: and r.pgm_id = p_pgm_id
11941: -- Bug 6656136
11942: cursor c_ler_typ_cd is
11943: --
11944: select ler.typ_cd
11945: from ben_per_in_ler pil,
11946: ben_ler_f ler
11947: where pil.per_in_ler_id = p_per_in_ler_id
11948: and pil.ler_id = ler.ler_id
11949: and pil.business_group_id = p_business_group_id