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