select DISTINCT
rpt.person_id person_idm2,
nvl(pgm.name, '-----') pgm_name,
pl.name pl_name,
opt.name opt_name,
plt.name pl_typ_name,
org.name provider_name,
apr.organization_id provider_idm1,
rpt.pl_id
from ben_reporting rpt,
ben_pgm_f pgm,
ben_pl_f pl,
ben_oipl_f oipl,
ben_opt_f opt,
ben_pl_typ_f plt,
ben_actl_prem_f apr,
hr_all_organization_units_vl org
where rpt.benefit_action_id = :CP_BENEFIT_ACTION_ID
and rpt.actl_prem_id = apr.actl_prem_id
and :CP_REQ_PROCESS_DATE between
apr.effective_start_date and apr.effective_end_date
and apr.organization_id = org.organization_id(+)
and :CP_REQ_PROCESS_DATE between
nvl(pgm.effective_start_date, :CP_REQ_PROCESS_DATE) and nvl(pgm.effective_end_date, :CP_REQ_PROCESS_DATE)
and rpt.pgm_id = pgm.pgm_id(+)
and rpt.pl_id = pl.pl_id(+)
and :CP_REQ_PROCESS_DATE between
nvl(pl.effective_start_date, :CP_REQ_PROCESS_DATE) and nvl(pl.effective_end_date, :CP_REQ_PROCESS_DATE)
and rpt.oipl_id = oipl.oipl_id(+)
and :CP_REQ_PROCESS_DATE between
nvl(oipl.effective_start_date, :CP_REQ_PROCESS_DATE) and nvl(oipl.effective_end_date,:CP_REQ_PROCESS_DATE)
and rpt.pl_typ_id = plt.pl_typ_id(+)
and :CP_REQ_PROCESS_DATE between
nvl(plt.effective_start_date, :CP_REQ_PROCESS_DATE) and nvl(plt.effective_end_date,:CP_REQ_PROCESS_DATE)
and oipl.opt_id = opt.opt_id(+)
and :CP_REQ_PROCESS_DATE between
nvl(opt.effective_start_date, :CP_REQ_PROCESS_DATE) and nvl(opt.effective_end_date,:CP_REQ_PROCESS_DATE)
and rpt.person_id=:person_id1 |
