[Home] [Help]
165: ,c_comm_sdt date
166: )
167: is
168: select pcm.per_cm_id
169: from ben_per_cm_f pcm, ben_per_in_ler pil
170: -- if commu table has no ler id dont compare , pil_id take care
171: -- of validation # 3296015
172: where (pcm.ler_id is null or pcm.ler_id = c_ler_id)
173: and nvl(pcm.prtt_enrt_actn_id,-1) = nvl(c_pna_id,-1)
935: ctu.ler_id,
936: ctu.pl_typ_id
937: from ben_cm_typ_usg_f ctu,
938: ben_elig_per_f pep,
939: ben_per_in_ler pil
940: where ctu.business_group_id = p_business_group_id
941: and pep.business_group_id = ctu.business_group_id
942: and pep.person_id = p_person_id
943: and nvl(get_cvg_strt_dt(pep.elig_per_id,pil.per_in_ler_id),cv_effective_date)
993: and nvl(pil.per_in_ler_stat_cd,'-1') not in ('VOIDD','BCKDT')
994: ---look for previous eligble
995: and (( p_eligible_flag = 'Y'
996: and not exists ( SELECT 'x'
997: FROM ben_elig_per_f pep2, ben_per_in_ler pil2
998: WHERE pep2.person_id = pep.person_id
999: AND (ctu.pl_id is null or nvl(pep2.pl_id,-1) = nvl(pep.pl_id,-1) )
1000: AND (ctu.pgm_id is null or nvl(pep2.pgm_id,-1) = nvl(pep.pgm_id,-1) )
1001: --- pep ler id is not updated so pil ler id is validated # 2784972
1024: )
1025: OR
1026: ( p_eligible_flag = 'N'
1027: and exists ( SELECT 'x'
1028: FROM ben_elig_per_f pep2, ben_per_in_ler pil2
1029: WHERE pep2.person_id = pep.person_id
1030: AND (ctu.pl_id is null or nvl(pep2.pl_id,-1) = nvl(pep.pl_id,-1) )
1031: AND (ctu.pgm_id is null or nvl(pep2.pgm_id,-1) = nvl(pep.pgm_id,-1) )
1032: -- pep ler id is not updated so pil led id is used # 2784972
1058: --
1059: cursor c2(cv_effective_date date, cv_lf_evt_ocrd_dt date) is
1060: select null
1061: from ben_elig_per_f pep,
1062: ben_per_in_ler pil
1063: where pep.business_group_id = p_business_group_id
1064: and pep.person_id = p_person_id
1065: and nvl(get_cvg_strt_dt(pep.elig_per_id,pil.per_in_ler_id),cv_effective_date)
1066: between pep.effective_start_date and pep.effective_end_date
1093: c_pl_typ_id number,
1094: cv_effective_date date) is
1095: select 'x'
1096: FROM ben_elig_per_f pep,
1097: ben_per_in_ler pil,
1098: ben_elig_dpnt egd
1099: WHERE egd.dpnt_person_id = p_person_id
1100: and egd.business_group_id = p_business_group_id
1101: and egd.elig_per_id = pep.elig_per_id
1499: )
1500: IS
1501: SELECT ctu.cm_typ_usg_id, ctu.cm_usg_rl, ctu.pl_id, ctu.pgm_id,
1502: ctu.pl_typ_id
1503: FROM ben_cm_typ_usg_f ctu, ben_per_in_ler pil
1504: WHERE ctu.business_group_id = c_bgp_id
1505: AND ctu.cm_typ_id = c_cm_typ_id
1506: AND ctu.all_r_any_cd = 'ALL'
1507: AND ( c_asnd_lf_evt_dt IS NULL
1543: ctu.pl_id,
1544: ctu.pgm_id,
1545: ctu.pl_typ_id
1546: from ben_cm_typ_usg_f ctu,
1547: ben_per_in_ler pil,
1548: ben_elig_per_elctbl_chc epe
1549: where pil.per_in_ler_id = c_pil_id
1550: and pil.business_group_id = c_bgp_id
1551: and ctu.business_group_id = pil.business_group_id
1575: --
1576: cursor c2 is
1577: select null
1578: from ben_elig_per_elctbl_chc epe,
1579: ben_per_in_ler pil
1580: where pil.per_in_ler_id = p_per_in_ler_id
1581: and pil.business_group_id = p_business_group_id
1582: and epe.per_in_ler_id = pil.per_in_ler_id
1583: and epe.elctbl_flag = 'Y';
1911: ctu.pgm_id,
1912: ctu.pl_typ_id
1913: from ben_cm_typ_usg_f ctu,
1914: ben_prtt_enrt_rslt_f pen,
1915: ben_per_in_ler pil
1916: where ctu.business_group_id = p_business_group_id
1917: /* First join comp objects */
1918: and pen.business_group_id = ctu.business_group_id
1919: and pen.per_in_ler_id = p_per_in_ler_id
1961: --
1962: cursor c2 is
1963: select null
1964: from ben_prtt_enrt_rslt_f pen,
1965: ben_per_in_ler pil
1966: where pen.business_group_id = p_business_group_id
1967: and pen.per_in_ler_id = p_per_in_ler_id
1968: and pen.per_in_ler_id = pil.per_in_ler_id -- 5926672 join to pil
1969: and pen.enrt_cvg_thru_dt < hr_api.g_eot
2144: --
2145: cursor c2 is
2146: select null
2147: from ben_elig_per_elctbl_chc epe,
2148: ben_per_in_ler pil
2149: where pil.per_in_ler_id = p_per_in_ler_id
2150: and pil.business_group_id = p_business_group_id
2151: and epe.per_in_ler_id = pil.per_in_ler_id;
2152: --
2298: --
2299: cursor c2 is
2300: select null
2301: from ben_elig_per_elctbl_chc epe,
2302: ben_per_in_ler pil
2303: where pil.per_in_ler_id = p_per_in_ler_id
2304: and pil.business_group_id = p_business_group_id
2305: and epe.per_in_ler_id = pil.per_in_ler_id;
2306: --
3536: cursor c_epe(v_pgm_id in number,
3537: v_pl_typ_id in number) is
3538: select 'Y'
3539: from ben_elig_per_elctbl_chc epe,
3540: ben_per_in_ler pil,
3541: ben_pl_f pln,
3542: ben_oipl_f cop,
3543: ben_opt_f opt
3544: where epe.per_in_ler_id = p_per_in_ler_id
4197: l_proc varchar2(80) := g_package||'main';
4198: --
4199: l_ass_rec per_all_assignments_f%rowtype;
4200: l_pl_rec ben_pl_f%rowtype;
4201: l_pil_rec ben_per_in_ler%rowtype;
4202: l_loc_rec hr_locations_all%rowtype;
4203: l_effective_date date;
4204: --
4205: cursor c_triggers(p_eff_date date) is
4258: --
4259: cursor c_pil (p_per_in_ler_id number ) is
4260: select pil.lf_evt_ocrd_dt,
4261: ler.typ_cd
4262: from ben_per_in_ler pil ,
4263: ben_ler_f ler
4264: where pil.per_in_ler_id = p_per_in_ler_id
4265: and ler.ler_id = pil.ler_id
4266: and p_effective_date between
4270: --
4271: CURSOR c_get_pil IS
4272: --
4273: SELECT pil.*
4274: FROM ben_per_in_ler pil, ben_ler_f ler
4275: WHERE pil.person_id = p_person_id
4276: AND pil.per_in_ler_stat_cd = 'STRTD'
4277: AND ler.ler_id = pil.ler_id
4278: AND ler.ler_id = NVL (p_ler_id, pil.ler_id)