[Home] [Help]
1537: -- before making further changes to this cursor.
1538: --
1539: cursor l_c_prev_pl(p_pl_id number) is
1540: select nvl(sum(pen.bnft_amt),0)
1541: from ben_prtt_enrt_rslt_f pen,
1542: ben_per_in_ler pil
1543: where pen.person_id = p_person_id
1544: and pen.prtt_enrt_rslt_stat_cd is null
1545: and pen.pl_id = p_pl_id
1550: and pen.enrt_cvg_strt_dt < pen.effective_end_date
1551: and (p_effective_date-1) between pen.enrt_cvg_strt_dt
1552: and pen.enrt_cvg_thru_dt
1553: and pen.effective_end_date = ( select max(pen1.effective_start_date - 1)
1554: from ben_prtt_enrt_rslt_f pen1
1555: where pen1.per_in_ler_id = pil.per_in_ler_id
1556: and pen1.prtt_enrt_rslt_stat_cd is NULL
1557: and pen1.pl_id = p_pl_id
1558: and pen1.enrt_cvg_thru_dt = hr_api.g_eot
1564: --
1565: cursor c_ler(cv_pl_id number) is
1566: select pil.ler_id
1567: from ben_per_in_ler pil,
1568: ben_prtt_enrt_rslt_f pen
1569: where pil.person_id = p_person_id
1570: and pil.per_in_ler_stat_cd = 'STRTD'
1571: and pil.per_in_ler_id = pen.per_in_ler_id
1572: and pen.person_id = p_person_id
1616: epe.elig_per_elctbl_chc_id,
1617: epe.MUST_ENRL_ANTHR_PL_ID,
1618: 'N' dpnt_cvd_by_othr_apls_flag,
1619: -9999999999999999999999999999999999999 opt_id
1620: from ben_prtt_enrt_rslt_f pen,
1621: ben_elig_per_elctbl_chc epe,
1622: ben_plip_f cpp,
1623: ben_oipl_f cop,
1624: ben_pl_f pln
1649: l_ler_rstrn c_ler_rstrn%rowtype ;
1650: --
1651: -- Type declaration.
1652: --
1653: type interim_table is table of ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type
1654: index by binary_integer;
1655: --
1656: -- Local Variable declaration
1657: --
2510: and nvl(pgm_id,hr_api.g_number) = nvl(p_pgm_id, hr_api.g_number)
2511: and MNDTRY_FLAG = 'Y'
2512: and pl_id in -- bug 1207161 removed 'not'
2513: (Select distinct pen.pl_id
2514: From ben_prtt_enrt_rslt_f pen
2515: Where nvl(pen.enrt_cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
2516: and p_effective_date between
2517: pen.effective_start_date and pen.effective_end_date
2518: and pen.effective_end_date = hr_api.g_eot
2520: and nvl(pen.pgm_id,-1) = nvl(p_pgm_id,-1)
2521: and pen.prtt_enrt_rslt_stat_cd is null)
2522: and oipl_id not in -- bug 1207161 added this.
2523: (Select distinct nvl(pen.oipl_id, -1)
2524: From ben_prtt_enrt_rslt_f pen
2525: Where nvl(pen.enrt_cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
2526: and p_effective_date between
2527: pen.effective_start_date and pen.effective_end_date
2528: and pen.effective_end_date = hr_api.g_eot
2569: cursor c_pen is
2570: select distinct Enrt_Cvg_Strt_Dt CSD
2571: ,nvl(Enrt_Cvg_thru_Dt,hr_api.g_eot) CED
2572: ,'N' Match
2573: from ben_prtt_enrt_rslt_f
2574: where person_id = p_person_id
2575: and effective_end_date >= enrt_cvg_strt_dt
2576: and enrt_cvg_strt_dt <= nvl(Enrt_Cvg_thru_Dt,hr_api.g_eot) --bug 5257798
2577: and prtt_enrt_rslt_stat_cd is null
2756: ) is
2757: --
2758: Cursor c1 is
2759: select distinct b.per_in_ler_id, b.ler_id, b.pl_id
2760: From ben_prtt_enrt_rslt_f b
2761: ,ben_pl_f c
2762: Where b.person_id = p_person_id
2763: And nvl(b.pgm_id,-1) = nvl(p_pgm_id,-1)
2764: And p_effective_date between
2778: Select a.prtt_enrt_rslt_id
2779: ,a.effective_start_date
2780: ,a.effective_end_date
2781: ,a.object_version_number
2782: From ben_prtt_enrt_rslt_f a
2783: Where a.person_id = p_person_id
2784: And nvl(a.pgm_id,-1) = nvl(p_pgm_id,-1)
2785: And a.business_group_id=p_business_group_id
2786: And p_effective_date between
2906: and oipl.business_group_id = p_business_group_id;
2907: --
2908: cursor c_erl (p_pl_typ_id number) is
2909: select 'Y'
2910: from ben_prtt_enrt_rslt_f pen,
2911: ben_enrt_bnft enb
2912: where pen.person_id = p_person_id
2913: and pen.prtt_enrt_rslt_stat_cd is null
2914: and enrt_cvg_thru_dt = hr_api.g_eot
3758: Raise l_integrity_error;
3759: End If;
3760: If ((nvl(p_prtt_enrt_rslt_id, hr_api.g_number) <> hr_api.g_number) and
3761: NOT (dt_api.check_min_max_dates
3762: (p_base_table_name => 'ben_prtt_enrt_rslt_f',
3763: p_base_key_column => 'prtt_enrt_rslt_id',
3764: p_base_key_value => p_prtt_enrt_rslt_id,
3765: p_from_date => p_validation_start_date,
3766: p_to_date => p_validation_end_date))) Then
3763: p_base_key_column => 'prtt_enrt_rslt_id',
3764: p_base_key_value => p_prtt_enrt_rslt_id,
3765: p_from_date => p_validation_start_date,
3766: p_to_date => p_validation_end_date))) Then
3767: l_table_name := 'ben_prtt_enrt_rslt_f';
3768: Raise l_integrity_error;
3769: End If;
3770: End If;
3771: --
4260: --
4261: cursor csr_leg_code is
4262: select a.legislation_code
4263: from per_business_groups a,
4264: ben_prtt_enrt_rslt_f b
4265: where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4266: and a.business_group_id = b.business_group_id;
4267: --
4268: -- Declare local variables