[Home] [Help]
286: from BEN_ENRT_PERD A,
287: ben_yr_perd b,
288: ben_wthn_yr_perd c,
289: ben_popl_enrt_typ_cycl_f pet,
290: ben_ler_f ler
291: WHERE a.popl_enrt_typ_cycl_id = p_popl_enrt_typ_cycl_id
292: and a.yr_perd_id = b.yr_perd_id
293: and a.wthn_yr_perd_id = c.wthn_yr_perd_id (+)
294: and a.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id
562:
563: l_query_str :=
564: l_query_str || ' and exists (select null' ||
565: ' from ben_ptnl_ler_for_per ptn' ||
566: ' ,ben_ler_f ler' ||
567: ' where ptn.person_id = ppf.person_id' ||
568: ' and ler.ler_id = ptn.ler_id' ||
569: ' and ptn.lf_evt_ocrd_dt between ler.effective_start_date and ler.effective_end_date' ||
570: ' and ler.typ_cd =''COMP'' ' ||
572:
573: l_query_str :=
574: l_query_str || ' and not exists (select null' ||
575: ' from ben_per_in_ler pil' ||
576: ' ,ben_ler_f ler' ||
577: ' where pil.person_id = ppf.person_id' ||
578: ' and ler.ler_id = pil.ler_id' ||
579: ' and pil.lf_evt_ocrd_dt between ler.effective_start_date and ler.effective_end_date' ||
580: ' and ler.typ_cd =''COMP'' ' ||
1203: --
1204: l_query_str :=
1205: l_query_str || ' and exists (select null' ||
1206: ' from ben_ptnl_ler_for_per ptn' ||
1207: ' ,ben_ler_f ler_abse' ||
1208: ' where ptn.person_id = ppf.person_id' ||
1209: ' and ler_abse.ler_id = ptn.ler_id' ||
1210: ' and ptn.lf_evt_ocrd_dt between ler_abse.effective_start_date and ler_abse.effective_end_date' ||
1211: ' and ler_abse.typ_cd = ' || l_typ_cd ||
1218:
1219: l_query_str :=
1220: l_query_str || ' and exists (select null' ||
1221: ' from ben_ptnl_ler_for_per ptn' ||
1222: ' ,ben_ler_f ler_ben' ||
1223: ' where ptn.person_id = ppf.person_id' ||
1224: ' and ler_ben.ler_id = ptn.ler_id' ||
1225: ' and ptn.lf_evt_ocrd_dt between ler_ben.effective_start_date and ler_ben.effective_end_date' ||
1226: -- l_type_cd has not in so = not be allowed