[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
753: ,pea.effective_start_date
754: ,pea.effective_end_date
755: ,pil.lf_evt_ocrd_dt
756: from ben_prtt_enrt_actn_f pea,
757: ben_per_in_ler pil
758: where pea.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
759: and pea.business_group_id = p_business_group_id
760: and p_effective_date
761: between pea.effective_start_date and pea.effective_end_date
1563: ecr.actl_prem_id,
1564: pil.lf_evt_ocrd_dt,
1565: pil.ler_id
1566: from ben_enrt_prem ecr,
1567: ben_per_in_ler pil,
1568: ben_elig_per_elctbl_chc epe
1569: where ecr.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
1570: and epe.elig_per_elctbl_chc_id = ecr.elig_per_elctbl_chc_id
1571: and pil.per_in_ler_id = epe.per_in_ler_id
1589: ppe.std_prem_val,
1590: ppe.actl_prem_id,
1591: ppe.object_version_number
1592: from ben_prtt_prem_f ppe
1593: -- ben_per_in_ler pil
1594: where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1595: and ppe.actl_prem_id = p_actl_prem_id
1596: and p_effective_date between
1597: ppe.effective_start_date and ppe.effective_end_date;
2920: pil.lf_evt_ocrd_dt,
2921: pil.ler_id,
2922: epe.elig_per_elctbl_chc_id
2923: from ben_enrt_prem ecr,
2924: ben_per_in_ler pil,
2925: ben_elig_per_elctbl_chc epe
2926: where epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2927: and epe.elig_per_elctbl_chc_id = ecr.elig_per_elctbl_chc_id
2928: and pil.per_in_ler_id = epe.per_in_ler_id
2947: ppe.actl_prem_id,
2948: ppe.object_version_number,
2949: ppe.effective_start_date
2950: from ben_prtt_prem_f ppe,
2951: ben_per_in_ler pil
2952: where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2953: and ppe.actl_prem_id = p_actl_prem_id
2954: /*
2955: CODE PRIOR TO WWBUG: 1646442
2971: p_actl_prem_id in number,
2972: p_ppe_dt_to_use in date) is
2973: select ppe.*
2974: from ben_prtt_prem_f ppe,
2975: ben_per_in_ler pil
2976: where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2977: and ppe.actl_prem_id = p_actl_prem_id
2978: and p_ppe_dt_to_use <= ppe.effective_start_date
2979: and pil.per_in_ler_id=ppe.per_in_ler_id
3006: pdp.object_version_number
3007: from ben_elig_per_elctbl_chc epe,
3008: ben_elig_dpnt egd,
3009: ben_elig_cvrd_dpnt_f pdp,
3010: ben_per_in_ler egd_pil,
3011: ben_per_in_ler pdp_pil
3012: where epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3013: and epe.per_in_ler_id = p_per_in_ler_id
3014: and epe.elig_per_elctbl_chc_id = egd.elig_per_elctbl_chc_id
3007: from ben_elig_per_elctbl_chc epe,
3008: ben_elig_dpnt egd,
3009: ben_elig_cvrd_dpnt_f pdp,
3010: ben_per_in_ler egd_pil,
3011: ben_per_in_ler pdp_pil
3012: where epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3013: and epe.per_in_ler_id = p_per_in_ler_id
3014: and epe.elig_per_elctbl_chc_id = egd.elig_per_elctbl_chc_id
3015: and egd.elig_cvrd_dpnt_id = pdp.elig_cvrd_dpnt_id
3061: c_bnft_prvdr_pool_id number ,
3062: c_prtt_enrt_rslt_id number) is
3063: select prvdd_val
3064: from ben_bnft_prvdd_ldgr_f bpl,
3065: ben_per_in_ler pil
3066: where bpl.acty_base_rt_id = c_acty_base_rt_id
3067: and bpl.bnft_prvdr_pool_id = c_bnft_prvdr_pool_id
3068: and bpl.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
3069: and pil.per_in_ler_id = bpl.per_in_ler_id
5218: ,epe.ptip_id
5219: ,epe.pl_id
5220: ,pil.ler_id
5221: from ben_elig_per_elctbl_chc epe
5222: ,ben_per_in_ler pil
5223: where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
5224: and epe.business_group_id = p_business_group_id
5225: and epe.per_in_ler_id = pil.per_in_ler_id
5226: and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
5471: ,epe.object_version_number epe_ovn
5472: ,enb.enrt_bnft_id
5473: from ben_prtt_enrt_rslt_f pen
5474: ,ben_elig_per_elctbl_chc epe
5475: ,ben_per_in_ler pil
5476: ,ben_enrt_bnft enb
5477: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5478: and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id (+)
5479: and nvl(epe.elig_per_elctbl_chc_id, -1) = enb.elig_per_elctbl_chc_id (+)
5515: ,epe.per_in_ler_id
5516: ,epe.object_version_number epe_ovn
5517: ,enb.enrt_bnft_id
5518: from ben_elig_per_elctbl_chc epe
5519: ,ben_per_in_ler pil
5520: ,ben_enrt_bnft enb
5521: where epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5522: and nvl(epe.elig_per_elctbl_chc_id, -1) = enb.elig_per_elctbl_chc_id (+)
5523: and pil.per_in_ler_id=epe.per_in_ler_id
5615: p_prtt_enrt_rslt_id number,
5616: p_effective_start_date date) is
5617: select null
5618: from ben_prtt_enrt_rslt_f pen,
5619: ben_per_in_ler pil,
5620: ben_ler_f ler
5621: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5622: and pen.effective_start_date > p_effective_start_date
5623: and pen.per_in_ler_id = pil.per_in_ler_id
6048: ,opt.name opt_name
6049: ,pl.imptd_incm_calc_cd
6050: from ben_prtt_enrt_rslt_f pen
6051: ,ben_elig_per_elctbl_chc epe
6052: ,ben_per_in_ler pil
6053: ,ben_enrt_bnft enb
6054: ,ben_oipl_f oipl
6055: ,ben_pl_f pl
6056: ,ben_opt_f opt
6092: ppe.effective_start_date,
6093: ppe.effective_end_date,
6094: ppe.actl_prem_id
6095: from ben_prtt_prem_f ppe,
6096: ben_per_in_ler pil
6097: where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6098: and pil.per_in_ler_id=ppe.per_in_ler_id
6099: and pil.business_group_id=ppe.business_group_id
6100: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
6106: ppe.effective_start_date,
6107: ppe.effective_end_date,
6108: ppe.actl_prem_id
6109: from ben_prtt_prem_f ppe,
6110: ben_per_in_ler pil
6111: where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6112: and pil.per_in_ler_id=ppe.per_in_ler_id
6113: and pil.business_group_id=ppe.business_group_id
6114: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
6122: ppe1.effective_start_date,
6123: ppe1.effective_end_date,
6124: ppe1.actl_prem_id
6125: from ben_prtt_prem_f ppe1,
6126: ben_per_in_ler pil
6127: where ppe1.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6128: and pil.per_in_ler_id=ppe1.per_in_ler_id
6129: and pil.business_group_id=ppe1.business_group_id
6130: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
6131: and ppe1.effective_start_date > p_ppe_dt_to_use
6132: and not exists
6133: (select 1
6134: from ben_prtt_prem_f ppe2,
6135: ben_per_in_ler pil
6136: where ppe2.prtt_prem_id = ppe1.prtt_prem_id
6137: and ppe2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6138: and pil.per_in_ler_id=ppe2.per_in_ler_id
6139: and pil.business_group_id=ppe2.business_group_id
6493: and pen.effective_end_date = hr_api.g_eot;
6494: --
6495: cursor c_per_in_ler (p_per_in_ler_id number) is
6496: select *
6497: from ben_per_in_ler
6498: where per_in_ler_id = p_per_in_ler_id
6499: and business_group_id = p_business_group_id;
6500:
6501: --
6675: -- Bug No 5637595 Added to get the lf_evt_ocrd_dt of the pil
6676: --
6677: cursor get_lf_evt_dt is
6678: select lf_evt_ocrd_dt from
6679: ben_per_in_ler where
6680: per_in_ler_id = p_per_in_ler_id and
6681: per_in_ler_stat_cd not in ('VOIDD','BCKDT');
6682: l_lf_evt_ocrd_dt date;
6683:
9165: ,pbf.dsgn_strt_dt
9166: ,pbf.dsgn_thru_dt
9167: ,pbf.per_in_ler_id
9168: from ben_pl_bnf_f pbf,
9169: ben_per_in_ler pil
9170: where pbf.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
9171: and pbf.business_group_id = p_business_group_id
9172: and p_effective_date between pbf.effective_start_date
9173: and pbf.effective_end_date
9180: is
9181: select pbf.pl_bnf_id
9182: ,pbf.object_version_number
9183: from ben_pl_bnf_f pbf,
9184: ben_per_in_ler pil
9185: where pbf.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
9186: and p_effective_date
9187: between pbf.effective_start_date
9188: and pbf.effective_end_date
9397: ,ecd.cvg_thru_dt
9398: ,ecd.object_version_number
9399: ,ecd.per_in_ler_id
9400: from ben_elig_cvrd_dpnt_f ecd,
9401: ben_per_in_ler pil
9402: where ecd.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id
9403: and ecd.cvg_strt_dt is not null
9404: -- and ecd.cvg_thru_dt = hr_api.g_eot
9405: and ecd.business_group_id=p_business_group_id
9968: cursor c_le_date(p_per_in_ler_id number) is
9969: select
9970: lf_evt_ocrd_dt
9971: from
9972: ben_per_in_ler pil
9973: where pil.per_in_ler_id = p_per_in_ler_id ;
9974: --
9975: l_lf_evt_ocrd_dt date ;
9976: --
10305: ,c.alws_dpnt_dsgn_flag
10306: ,pl.bnf_dsgn_cd
10307: From ben_prtt_enrt_rslt_f r,
10308: ben_elig_per_elctbl_chc c,
10309: ben_per_in_ler pil,
10310: ben_pl_f pl
10311: Where r.pl_typ_id = p_pl_typ_id
10312: and r.per_in_ler_id = p_per_in_ler_id
10313: and r.pgm_id = p_pgm_id
10343: -- Bug 6656136
10344: cursor c_ler_typ_cd is
10345: --
10346: select ler.typ_cd
10347: from ben_per_in_ler pil,
10348: ben_ler_f ler
10349: where pil.per_in_ler_id = p_per_in_ler_id
10350: and pil.ler_id = ler.ler_id
10351: and pil.business_group_id = p_business_group_id