[Home] [Help]
851: and elk.effective_end_date;
852: --
853: cursor c_comp_obj_name (p_prtt_enrt_rslt_id number) is
854: select rtrim(substr(pln.name||' '||opt.name,1,60)) -- 4203714 rtrim to chop off the trailing spaces
855: from ben_prtt_enrt_rslt_f pen,
856: ben_pl_f pln,
857: ben_oipl_f oipl,
858: ben_opt_f opt
859: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1153: l_prnt_ann_rt varchar2(1) := 'N';
1154: l_rounded_value number;
1155: l_cal_val_in number ;
1156: l_global_asg_rec ben_global_enrt.g_global_asg_rec_type;
1157: l_global_pen_rec ben_prtt_enrt_rslt_f%rowtype;
1158: l_ele_entry_val_cd varchar2(30);
1159: l_new_assignment_id number;
1160: l_new_payroll_id number;
1161: l_new_organization_id number;
1561: eb.cvg_mlt_cd
1562: FROM ben_enrt_rt er,
1563: ben_enrt_bnft eb,
1564: ben_acty_base_rt_f abr,
1565: ben_prtt_enrt_rslt_f pen
1566: WHERE eb.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
1567: AND pen.rplcs_sspndd_rslt_id = p_prtt_enrt_rslt_id
1568: AND eb.elig_per_elctbl_chc_id = cv_elig_per_elctbl_chc_id
1569: AND pen.per_in_ler_id = p_per_in_ler_id
1610: where pil.per_in_ler_id <> p_per_in_ler_id
1611: /* Bug 8945818: Added 'or' condition. Check for future rates for the previous life event */
1612: and ( (trunc(pil.lf_evt_ocrd_dt, 'MM') = trunc(pil2.lf_evt_ocrd_dt, 'MM')) or
1613: ('Y' = ( select 'Y' from ben_prtt_rt_val prv,
1614: ben_prtt_enrt_rslt_f pen_n,
1615: ben_prtt_enrt_rslt_f pen_o
1616: where pen_n.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1617: and pen_n.prtt_enrt_rslt_stat_cd is null
1618: and pen_n.per_in_ler_id = p_per_in_ler_id
1611: /* Bug 8945818: Added 'or' condition. Check for future rates for the previous life event */
1612: and ( (trunc(pil.lf_evt_ocrd_dt, 'MM') = trunc(pil2.lf_evt_ocrd_dt, 'MM')) or
1613: ('Y' = ( select 'Y' from ben_prtt_rt_val prv,
1614: ben_prtt_enrt_rslt_f pen_n,
1615: ben_prtt_enrt_rslt_f pen_o
1616: where pen_n.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1617: and pen_n.prtt_enrt_rslt_stat_cd is null
1618: and pen_n.per_in_ler_id = p_per_in_ler_id
1619: /* Bug 10419741; If for previous life event there are no rate records created, using the value returned
1618: and pen_n.per_in_ler_id = p_per_in_ler_id
1619: /* Bug 10419741; If for previous life event there are no rate records created, using the value returned
1620: by the cursor c_prev_per_in_ler will not fetch any values */
1621: and pen_o.per_in_ler_id in (select prv3.per_in_ler_id
1622: from ben_prtt_rt_val prv3,ben_prtt_enrt_rslt_f pen3
1623: where prv3.ended_per_in_ler_id = p_per_in_ler_id
1624: and pen3.prtt_enrt_rslt_id = prv3.prtt_enrt_rslt_id
1625: and pen3.per_in_ler_id = prv3.per_in_ler_id
1626: and pen3.enrt_cvg_strt_dt <= pen3.enrt_cvg_thru_dt
1633: and pen_o.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
1634: /* Bug 10419741; If for previous life event there are no rate records created, using the value returned
1635: by the cursor c_prev_per_in_ler will not fetch any values */
1636: and prv.per_in_ler_id in (select prv3.per_in_ler_id
1637: from ben_prtt_rt_val prv3,ben_prtt_enrt_rslt_f pen3
1638: where prv3.ended_per_in_ler_id = p_per_in_ler_id
1639: and pen3.prtt_enrt_rslt_id = prv3.prtt_enrt_rslt_id
1640: and pen3.per_in_ler_id = prv3.per_in_ler_id
1641: and pen3.enrt_cvg_strt_dt <= pen3.enrt_cvg_thru_dt
1713: select prv.*
1714: ,abr.element_type_id
1715: ,abr.input_value_id
1716: ,pen.person_id
1717: from ben_prtt_enrt_rslt_f pen
1718: ,ben_prtt_rt_val prv
1719: ,ben_acty_base_rt_f abr
1720: where pen.effective_end_date = hr_api.g_eot
1721: and pen.enrt_cvg_thru_dt <> hr_api.g_eot
4766: l_global_epe_rec ben_global_enrt.g_global_epe_rec_type;
4767: l_global_pel_rec ben_global_enrt.g_global_pel_rec_type;
4768: l_global_pil_rec ben_global_enrt.g_global_pil_rec_type;
4769: l_global_enb_rec ben_global_enrt.g_global_enb_rec_type;
4770: l_global_pen_rec ben_prtt_enrt_rslt_f%rowtype;
4771:
4772: l_xenrt_cvg_strt_dt date;
4773: l_xenrt_cvg_strt_dt_cd varchar2(30);
4774: l_xenrt_cvg_strt_dt_rl number;
4819: pen.enrt_ovrid_thru_dt,
4820: pen.enrt_ovrid_rsn_cd,
4821: pen.enrt_ovridn_flag,
4822: pen.sspndd_flag
4823: from ben_prtt_enrt_rslt_f pen
4824: where pen.prtt_enrt_rslt_id=v_prtt_enrt_rslt_id and
4825: pen.business_group_id=p_business_group_id and
4826: pen.prtt_enrt_rslt_stat_cd is null and
4827: p_effective_date between
4832: -- Bug 2627078 fixes
4833: cursor c_delink_interim(v_prtt_enrt_rslt_id number, v_sspnd_result_id number ) is
4834: select pen.prtt_enrt_rslt_id ,
4835: pen.object_version_number
4836: from ben_prtt_enrt_rslt_f pen
4837: where pen.rplcs_sspndd_rslt_id = v_prtt_enrt_rslt_id and
4838: pen.prtt_enrt_rslt_id = v_sspnd_result_id and
4839: p_effective_date between
4840: pen.effective_start_date and pen.effective_end_date
4913: --
4914: cursor c_enb_pen is
4915: select enb.prtt_enrt_rslt_id
4916: from ben_enrt_bnft enb,
4917: ben_prtt_enrt_rslt_f pen
4918: where enb.enrt_bnft_id = p_enrt_bnft_id
4919: and enb.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
4920: and pen.effective_end_date = hr_api.g_eot
4921: and pen.enrt_cvg_thru_dt = hr_api.g_eot
4924: --
4925: -- Bug 2600087 fixes to open future dated coverage
4926: cursor c_enrt_rslt is
4927: select pen.*
4928: from ben_prtt_enrt_rslt_f pen
4929: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4930: and pen.prtt_enrt_rslt_stat_cd is null
4931: and pen.effective_end_date = (select pen2.effective_start_date - 1
4932: from ben_prtt_enrt_rslt_f pen2
4928: from ben_prtt_enrt_rslt_f pen
4929: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4930: and pen.prtt_enrt_rslt_stat_cd is null
4931: and pen.effective_end_date = (select pen2.effective_start_date - 1
4932: from ben_prtt_enrt_rslt_f pen2
4933: where pen2.enrt_cvg_thru_dt <> hr_api.g_eot
4934: and pen2.effective_end_date = hr_api.g_eot
4935: and pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4936: and pen2.prtt_enrt_rslt_stat_cd is null);
4950:
4951: /*Bug 12576710 */
4952: cursor c_bkp_enrt_rslt_exist is
4953: select pen.*
4954: from ben_prtt_enrt_rslt_f pen
4955: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4956: and pen.prtt_enrt_rslt_stat_cd is null
4957: and pen.effective_end_date = (select pen2.effective_start_date - 1
4958: from ben_prtt_enrt_rslt_f pen2
4954: from ben_prtt_enrt_rslt_f pen
4955: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4956: and pen.prtt_enrt_rslt_stat_cd is null
4957: and pen.effective_end_date = (select pen2.effective_start_date - 1
4958: from ben_prtt_enrt_rslt_f pen2
4959: where pen2.enrt_cvg_thru_dt <> hr_api.g_eot
4960: and pen2.effective_end_date = hr_api.g_eot
4961: and pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4962: and pen2.prtt_enrt_rslt_stat_cd is null)
4965: bkp.per_in_ler_id = pen.per_in_ler_id
4966: and bkp.effective_end_date = pen.effective_end_date
4967: and bkp.BKUP_TBL_ID = pen.prtt_enrt_rslt_id
4968: and bkp.enrt_cvg_thru_dt = pen.enrt_cvg_thru_dt
4969: and bkp.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_ADJ');
4970:
4971: l_bkp_enrt c_bkp_enrt_rslt_exist%rowtype;
4972: --
4973: --bug#5032364
4972: --
4973: --bug#5032364
4974: cursor c_enrt_rslt2 is
4975: select pen.*
4976: from ben_prtt_enrt_rslt_f pen
4977: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4978: and pen.prtt_enrt_rslt_stat_cd is null
4979: and pen.effective_end_date = hr_api.g_eot;
4980: --
5009: and pdp.effective_end_date;
5010: --
5011: cursor c_pen_exists(v_prtt_enrt_rslt_id number) is
5012: select 'x'
5013: from ben_prtt_enrt_rslt_f pen
5014: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5015: and pen.business_group_id = p_business_group_id
5016: and p_effective_date between pen.effective_start_date
5017: and pen.effective_end_date ;
5056: -- Bug 5600697
5057: --
5058: cursor c_is_pen_valid(cv_prtt_enrt_rslt_id number) is
5059: select pen.prtt_enrt_Rslt_stat_Cd
5060: from ben_prtt_enrt_rslt_f pen
5061: where pen.prtt_enrt_rslt_id = cv_prtt_enrt_rslt_id and
5062: pen.business_group_id= p_business_group_id and
5063: p_effective_date between pen.effective_start_date and pen.effective_end_date;
5064: --
5067: cursor c_pen_curr(v_prtt_enrt_rslt_id number,
5068: p_effective_date date,
5069: p_per_in_ler_id number) is -- 12583091
5070: select pen.*
5071: from ben_prtt_enrt_rslt_f pen
5072: where pen.prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
5073: and p_effective_date between
5074: pen.effective_start_date and pen.effective_end_date
5075: and pen.effective_end_date <> hr_api.g_eot
5087: ,p_per_in_ler_id number
5088: ,p_effective_date date
5089: ,p_prtt_enrt_rslt_id number) is
5090: select null
5091: from ben_prtt_enrt_rslt_f pen
5092: where pen.prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
5093: and p_effective_date between
5094: pen.effective_start_date and pen.effective_end_date
5095: and pen.per_in_ler_id = p_per_in_ler_id
5101: cursor c_get_ended_result(p_elig_per_elctbl_chc_id number
5102: ,p_per_in_ler_id number
5103: ) is
5104: select pen.*
5105: from ben_prtt_enrt_rslt_f pen
5106: ,ben_elig_per_elctbl_chc epe
5107: where nvl(epe.pgm_id, -1) = nvl(pen.pgm_id, -1)
5108: and epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id -- 13542327
5109: and epe.pl_id = pen.pl_id
5183: /* Bug 8945818: Added 'or' condition. Check for future coverage for the previous life event */
5184: and ( (trunc(pil.lf_evt_ocrd_dt, 'MM') = trunc(pil2.lf_evt_ocrd_dt, 'MM')) or
5185: ('Y' = (select 'Y' from ben_elig_per_elctbl_chc epe1,
5186: ben_elig_per_elctbl_chc epe2,
5187: ben_prtt_enrt_rslt_f pen
5188: where epe1.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
5189: and epe1.per_in_ler_id <> epe2.per_in_ler_id
5190: and epe2.per_in_ler_id = l_prev_pil_id
5191: and epe1.ptip_id = epe2.ptip_id
5219: /* Bug 8945818: Added 'or' condition. Check for future coverage for the previous life event */
5220: and ( (trunc(pil.lf_evt_ocrd_dt, 'MM') = trunc(pil2.lf_evt_ocrd_dt, 'MM')) or
5221: ('Y' = (select 'Y' from ben_elig_per_elctbl_chc epe1,
5222: ben_elig_per_elctbl_chc epe2,
5223: ben_prtt_enrt_rslt_f pen
5224: where epe1.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
5225: and epe1.per_in_ler_id <> epe2.per_in_ler_id
5226: and epe2.per_in_ler_id = l_prev_pil_id
5227: and epe1.ptip_id = epe2.ptip_id
5285: cursor c_get_enrt_rslts_for_pen(p_cvg_end_dt date
5286: ,p_ptip_id number
5287: ) is
5288: select pen.*, epe.per_in_ler_id pil_id
5289: from ben_prtt_enrt_rslt_f pen
5290: ,ben_ptip_f ptip
5291: ,ben_per_in_ler pil
5292: ,ben_elig_per_elctbl_chc epe
5293: where pen.effective_end_date = hr_api.g_eot -- '31-dec-4712'
5305: --
5306: cursor c_prtt_enrt_rslt_adj (p_prtt_enrt_rslt_id number) is
5307: select null
5308: from ben_le_clsn_n_rstr leclr, ben_elig_per_elctbl_chc epe
5309: where leclr.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_ADJ'
5310: AND leclr.BKUP_TBL_ID = p_prtt_enrt_rslt_id
5311: AND leclr.PER_IN_LER_ID = epe.per_in_ler_id
5312: AND epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
5313: --
5316: ---------Bug 9139820
5317: cursor c_get_pil_enrt(p_pen_id number,p_per_in_ler_id number,
5318: p_cvg_strt_dt date) is
5319: SELECT *
5320: FROM ben_prtt_enrt_rslt_f pen
5321: WHERE pen.per_in_ler_id = p_per_in_ler_id
5322: AND pen.business_group_id = p_business_group_id
5323: and pen.prtt_enrt_rslt_id = p_pen_id
5324: AND pen.enrt_cvg_strt_dt = p_cvg_strt_dt
5323: and pen.prtt_enrt_rslt_id = p_pen_id
5324: AND pen.enrt_cvg_strt_dt = p_cvg_strt_dt
5325: and pen.sspndd_flag <> 'Y'
5326: and not EXISTS(SELECT NULL
5327: FROM ben_prtt_enrt_rslt_f pen2
5328: WHERE pen2.per_in_ler_id = pen.per_in_ler_id
5329: AND pen2.rplcs_sspndd_rslt_id = pen.prtt_enrt_rslt_id
5330: AND pen2.sspndd_flag = 'Y'
5331: AND pen2.prtt_enrt_rslt_stat_cd IS NULL
5338: cursor c_bkup_pen_rec(p_pen_id number,p_pil_id number,p_person_id number,p_esd date,p_csd date) is
5339: SELECT bkup.*
5340: FROM ben_le_clsn_n_rstr bkup
5341: WHERE bkup.bkup_tbl_id = p_pen_id
5342: AND bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F_CORR'
5343: AND bkup.per_in_ler_id = p_pil_id
5344: AND bkup.person_id = p_person_id
5345: AND bkup.business_group_id = p_business_group_id
5346: AND bkup.effective_start_date = p_esd
5352: l_pil_id number;
5353:
5354: cursor c_get_ovn(p_pen_id number,p_pil_id number,p_esd date) is
5355: select max(pen.object_version_number)
5356: from ben_prtt_enrt_rslt_f pen
5357: where pen.prtt_enrt_rslt_id = p_pen_id
5358: and pen.per_in_ler_id = p_pil_id
5359: and pen.business_group_id = p_business_group_id
5360: and pen.effective_start_date = p_esd
5378: WHERE pil.business_group_id = p_business_group_id
5379: AND pil.person_id = p_person_id
5380: AND pil.per_in_ler_stat_cd = 'PROCD'
5381: and exists(select null
5382: from ben_prtt_enrt_rslt_f pen
5383: where pen.per_in_ler_id = pil.per_in_ler_id
5384: and pen.prtt_enrt_rslt_stat_cd is null
5385: and pen.prtt_enrt_rslt_id = p_pen_id)
5386: ORDER BY pil.lf_evt_ocrd_dt DESC ;
5388: ---Bug 9430735
5389:
5390: cursor c_check_int_enr(p_pil_id number,p_pgm_id number,p_pl_id number,p_oipl_id number) is
5391: SELECT *
5392: FROM ben_prtt_enrt_rslt_f pen
5393: WHERE pen.per_in_ler_id = p_pil_id
5394: AND nvl(pen.pgm_id,-1) = nvl(p_pgm_id,-1)
5395: AND pen.pl_id = p_pl_id
5396: AND Nvl(pen.oipl_id ,-1) = nvl(p_oipl_id,-1)
5397: AND pen.business_group_id = p_business_group_id
5398: and pen.enrt_cvg_thru_dt = hr_api.g_eot
5399: AND pen.prtt_enrt_rslt_stat_cd IS NULL
5400: AND EXISTS(SELECT NULL
5401: FROM ben_prtt_enrt_rslt_f pen2
5402: WHERE pen2.per_in_ler_id = pen.per_in_ler_id
5403: AND pen2.rplcs_sspndd_rslt_id = pen.prtt_enrt_rslt_id
5404: AND pen2.sspndd_flag = 'Y'
5405: AND pen2.prtt_enrt_rslt_stat_cd IS NULL
6147: end if;
6148: -- 3733745
6149: select count(*)
6150: into l_interim_count
6151: from ben_prtt_enrt_rslt_f pen
6152: where pen.rplcs_sspndd_rslt_id = p_prtt_enrt_rslt_id
6153: and nvl(pen.sspndd_flag , 'N') = 'Y'
6154: and p_effective_date
6155: between pen.effective_start_date and pen.effective_end_date;
6278: PLIP_ORDR_NUM,
6279: PTIP_ORDR_NUM,
6280: OIPL_ORDR_NUM)
6281: values (
6282: 'BEN_PRTT_ENRT_RSLT_F_ADJ',
6283: l_enrt_rslt2.COMP_LVL_CD,
6284: l_enrt_rslt2.PEN_ATTRIBUTE16,
6285: l_enrt_rslt2.PEN_ATTRIBUTE17,
6286: l_enrt_rslt2.PEN_ATTRIBUTE18,
7196: ENRT_CVG_THRU_DT,
7197: business_group_id,
7198: object_version_number)
7199: values (
7200: 'BEN_PRTT_ENRT_RSLT_F_ADJ',
7201: l_get_enrt_rslts_for_pen.prtt_enrt_rslt_id,
7202: l_get_enrt_rslts_for_pen.pil_id,
7203: l_get_enrt_rslts_for_pen.person_id,
7204: l_get_enrt_rslts_for_pen.enrt_cvg_thru_dt,