eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BEN_ELECTION_INFORMATION dependencies on BEN_ELIG_PER_ELCTBL_CHC

Line 1552: from ben_elig_per_elctbl_chc epe

1548: pln.effective_start_date and pln.effective_end_date;
1549: --
1550: cursor c_epe is
1551: select epe.fonm_cvg_strt_dt
1552: from ben_elig_per_elctbl_chc epe
1553: where epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1554: and epe.per_in_ler_id = p_per_in_ler_id;
1555:
1556: --

Line 1673: from ben_elig_per_elctbl_chc epe

1669: --
1670: cursor c_get_elctbl_chc is
1671: select min(ecr.rt_strt_dt) rt_strt_dt
1672: ,epe.ptip_id
1673: from ben_elig_per_elctbl_chc epe
1674: ,ben_enrt_rt ecr
1675: ,ben_enrt_bnft enb
1676: where epe.per_in_ler_id = p_per_in_ler_id
1677: and epe.business_group_id = p_business_group_id

Line 1694: from ben_elig_per_elctbl_chc epe

1690: from which we are going to create the new rate i.e. the variable l_enrt_rt.rt_strt_dt passed to create_prtt_rt_val.
1691: The same should be used for adjusting the ended rates.*/
1692: cursor c_get_ptip_id is
1693: select epe.ptip_id
1694: from ben_elig_per_elctbl_chc epe
1695: ,ben_enrt_rt ecr
1696: ,ben_enrt_bnft enb
1697: where epe.per_in_ler_id = p_per_in_ler_id
1698: and epe.business_group_id = p_business_group_id

Line 4911: from ben_elig_per_elctbl_chc epe

4907: --Bug 2172036 populating the assignment_id in the pen
4908: cursor c_epe is
4909: select epe.assignment_id ,
4910: epe.fonm_cvg_strt_dt
4911: from ben_elig_per_elctbl_chc epe
4912: where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id ;
4913: --
4914: cursor c_enb_pen is
4915: select enb.prtt_enrt_rslt_id

Line 5106: ,ben_elig_per_elctbl_chc epe

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
5110: and nvl(epe.oipl_id, -1) = nvl(pen.oipl_id, -1)

Line 5142: from ben_elig_per_elctbl_chc

5138: l_ecr c_ecr%rowtype;
5139: -- bug 5621049
5140: cursor c_validate_epe_pen(cv_elig_per_elctblc_chc_id number,cv_prtt_enrt_rslt_id number ) is
5141: select null
5142: from ben_elig_per_elctbl_chc
5143: where elig_per_elctbl_chc_id = cv_elig_per_elctblc_chc_id
5144: and prtt_enrt_rslt_id = cv_prtt_enrt_rslt_id;
5145:
5146: ------Bug 7374973

Line 5163: ben_elig_per_elctbl_chc epe

5159: select pil.per_in_ler_id
5160: from ben_per_in_ler pil,
5161: ben_per_in_ler pil1,
5162: ben_ler_f ler,
5163: ben_elig_per_elctbl_chc epe
5164: where pil1.per_in_ler_id = epe.per_in_ler_id
5165: and epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
5166: and pil1.person_id = pil.person_id
5167: and pil1.per_in_ler_id <> pil.per_in_ler_id

Line 5179: from ben_elig_per_elctbl_chc epe, ben_per_in_ler pil,

5175: l_prev_pil_id number;
5176:
5177: cursor c_get_prior_per_in_ler(c_cvg_strt_dt date) is
5178: select 'Y'
5179: from ben_elig_per_elctbl_chc epe, ben_per_in_ler pil,
5180: ben_per_in_ler pil2
5181: where epe.elig_per_elctbl_chc_id=p_elig_per_elctbl_chc_id
5182: and pil.per_in_ler_id <> epe.per_in_ler_id
5183: /* Bug 8945818: Added 'or' condition. Check for future coverage for the previous life event */

Line 5185: ('Y' = (select 'Y' from ben_elig_per_elctbl_chc epe1,

5181: where epe.elig_per_elctbl_chc_id=p_elig_per_elctbl_chc_id
5182: and pil.per_in_ler_id <> epe.per_in_ler_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

Line 5186: ben_elig_per_elctbl_chc epe2,

5182: and pil.per_in_ler_id <> epe.per_in_ler_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

Line 5215: from ben_elig_per_elctbl_chc epe, ben_per_in_ler pil,

5211: -- for the coverage that are starting the future.the below cursor c_get_prior_per_in_ler_1 checks
5212: -- for any coverage that are end dating in the future
5213: cursor c_get_prior_per_in_ler_1(c_cvg_strt_dt date) is
5214: select 'Y'
5215: from ben_elig_per_elctbl_chc epe, ben_per_in_ler pil,
5216: ben_per_in_ler pil2
5217: where epe.elig_per_elctbl_chc_id=p_elig_per_elctbl_chc_id
5218: and pil.per_in_ler_id <> epe.per_in_ler_id
5219: /* Bug 8945818: Added 'or' condition. Check for future coverage for the previous life event */

Line 5221: ('Y' = (select 'Y' from ben_elig_per_elctbl_chc epe1,

5217: where epe.elig_per_elctbl_chc_id=p_elig_per_elctbl_chc_id
5218: and pil.per_in_ler_id <> epe.per_in_ler_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

Line 5222: ben_elig_per_elctbl_chc epe2,

5218: and pil.per_in_ler_id <> epe.per_in_ler_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

Line 5252: from ben_elig_per_elctbl_chc epe

5248: --------------------------------------------------------------------------------
5249:
5250: cursor c_get_pgm is
5251: select distinct epe.pgm_id
5252: from ben_elig_per_elctbl_chc epe
5253: where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
5254: --
5255: cursor c_get_pgm_extra_info_cvg(p_pgm_id number) is
5256: select pgi_information1

Line 5266: from ben_elig_per_elctbl_chc epe

5262: --
5263: cursor c_get_elctbl_chc_for_cvg is
5264: select min(epe.enrt_cvg_strt_dt) enrt_cvg_strt_dt
5265: ,epe.ptip_id
5266: from ben_elig_per_elctbl_chc epe
5267: where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
5268: and epe.business_group_id = p_business_group_id
5269: group by epe.ptip_id;
5270:

Line 5278: from ben_elig_per_elctbl_chc epe

5274: Since we already know of the new cvg_strt_dt from which we are going to create the new enrollment the same can be used to
5275: adjust the ended coverage. This will prevent any coverage gaps.*/
5276: cursor c_get_ptip_id is
5277: select epe.ptip_id
5278: from ben_elig_per_elctbl_chc epe
5279: where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
5280: and epe.business_group_id = p_business_group_id;
5281:
5282: l_ptip_id number;

Line 5292: ,ben_elig_per_elctbl_chc epe

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'
5294: and pen.enrt_cvg_thru_dt <> hr_api.g_eot -- '31-dec-4712'
5295: and pen.prtt_enrt_rslt_stat_cd is null
5296: and pen.person_id = pil.person_id -- 318321

Line 5308: from ben_le_clsn_n_rstr leclr, ben_elig_per_elctbl_chc epe

5304: and ptip.effective_end_date;
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;

Line 8093: ben_elig_per_elctbl_chc epe,

8089: cursor c1 is
8090: select ebr.enrt_bnft_id,
8091: ebr.object_version_number
8092: from ben_enrt_bnft ebr,
8093: ben_elig_per_elctbl_chc epe,
8094: ben_per_in_ler pil
8095: where ebr.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id and
8096: ( p_enrt_bnft_id is null OR
8097: ebr.enrt_bnft_id <> p_enrt_bnft_id )

Line 8409: from ben_elig_per_elctbl_chc epe,

8405: -- open c_pl_opt_name cursor only if error needs to be displayed.
8406: --
8407: cursor c_pl_opt_name is
8408: select pln.name || ' '|| opt.name
8409: from ben_elig_per_elctbl_chc epe,
8410: ben_pl_f pln,
8411: ben_oipl_f oipl,
8412: ben_opt_f opt
8413: where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id

Line 8429: from ben_elig_per_elctbl_chc epe

8425: cursor c_elinf is
8426: select epe.crntly_enrd_flag
8427: ,epe.elctbl_flag
8428: ,epe.mndtry_flag
8429: from ben_elig_per_elctbl_chc epe
8430: where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
8431: --
8432: l_bnft c_bnft%rowtype;
8433: l_elinf c_elinf%rowtype;