[Home] [Help]
289: from BEN_ENRT_PERD A,
290: ben_yr_perd b,
291: ben_wthn_yr_perd c,
292: ben_popl_enrt_typ_cycl_f pet,
293: ben_ler_f ler
294: WHERE a.popl_enrt_typ_cycl_id = p_popl_enrt_typ_cycl_id
295: and a.yr_perd_id = b.yr_perd_id
296: and a.wthn_yr_perd_id = c.wthn_yr_perd_id (+)
297: and a.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id
565:
566: l_query_str :=
567: l_query_str || ' and exists (select null' ||
568: ' from ben_ptnl_ler_for_per ptn' ||
569: ' ,ben_ler_f ler' ||
570: ' where ptn.person_id = ppf.person_id' ||
571: ' and ler.ler_id = ptn.ler_id' ||
572: ' and ptn.lf_evt_ocrd_dt between ler.effective_start_date and ler.effective_end_date' ||
573: ' and ler.typ_cd =''COMP'' ' ||
575:
576: l_query_str :=
577: l_query_str || ' and not exists (select null' ||
578: ' from ben_per_in_ler pil' ||
579: ' ,ben_ler_f ler' ||
580: ' where pil.person_id = ppf.person_id' ||
581: ' and ler.ler_id = pil.ler_id' ||
582: ' and pil.lf_evt_ocrd_dt between ler.effective_start_date and ler.effective_end_date' ||
583: ' and ler.typ_cd =''COMP'' ' ||
1206: --
1207: l_query_str :=
1208: l_query_str || ' and exists (select null' ||
1209: ' from ben_ptnl_ler_for_per ptn' ||
1210: ' ,ben_ler_f ler_abse' ||
1211: ' where ptn.person_id = ppf.person_id' ||
1212: ' and ler_abse.ler_id = ptn.ler_id' ||
1213: ' and ptn.lf_evt_ocrd_dt between ler_abse.effective_start_date and ler_abse.effective_end_date' ||
1214: ' and ler_abse.typ_cd = ' || l_typ_cd ||
1221:
1222: l_query_str :=
1223: l_query_str || ' and exists (select null' ||
1224: ' from ben_ptnl_ler_for_per ptn' ||
1225: ' ,ben_ler_f ler_ben' ||
1226: ' where ptn.person_id = ppf.person_id' ||
1227: ' and ler_ben.ler_id = ptn.ler_id' ||
1228: ' and ptn.lf_evt_ocrd_dt between ler_ben.effective_start_date and ler_ben.effective_end_date' ||
1229: -- l_type_cd has not in so = not be allowed