BEN HIPAA Letter

select pcm.person_id,
 pcd.to_be_sent_dt,
 pcd.per_cm_prvdd_id,
 per.first_name || ' '|| per.last_name   ||' '|| per.suffix full_name,
 per.national_identifier,
 pdd.town_or_city City, pdd.region_2 State, pdd.postal_code "Zip Code" ,
 pbg.name "Company Name", ptp.name plan_type, pln.name  pl_name ,
 pen.enrt_cvg_strt_dt "Coverage Start Date",
 pen.enrt_cvg_thru_dt "Coverage End Date" ,
 pen.orgnl_enrt_dt,
 pen.prtt_enrt_rslt_id prtt_enrt_rslt_idm1,
 pcm.per_in_ler_id ,
 opt.name  "Option Name" ,  
  nvl(epo.wait_perd_strt_dt  , pep.wait_perd_strt_dt )   wait_perd_Strt_dt ,
 nvl(epo.wait_perd_cmpltn_date,pep.wait_perd_cmpltn_dt) wait_ped_cmpln_dt,
 prol.name "Plan Administrator Name" ,
 loc.address_line_1,
 loc.address_line_2,
 loc.address_line_3,
 loc.town_or_city  ||' '|| loc.region_2  || ' '|| loc.postal_code loc_City ,
 loc.telephone_number_1 loc_ph_1,
 loc.telephone_number_2 loc_ph_2,  
loc.telephone_number_3  loc_ph_3, 
	BEN_BENHIPAA_XMLP_PKG.cf_wait_start_dtformula(pen.enrt_cvg_thru_dt, pen.orgnl_enrt_dt, nvl ( epo.wait_perd_strt_dt , pep.wait_perd_strt_dt ),pcd.per_cm_prvdd_id) CF_wait_start_dt, 
	BEN_BENHIPAA_XMLP_PKG.cf_wait_perd_cmpltn_dtformula(pen.enrt_cvg_thru_dt, pen.orgnl_enrt_dt, nvl ( epo.wait_perd_cmpltn_date , pep.wait_perd_cmpltn_dt )) CF_wait_perd_cmpltn_dt
from ben_per_cm_prvdd_f pcd,
 ben_per_cm_f pcm,
 ben_cm_typ_usg_f ctu,
 ben_cm_typ_f cct,
 ben_per_cm_usg_f pcu,per_all_people_f per,
 per_all_assignments_f ass ,
 per_addresses pdd, 
 per_business_groups pbg,
 ben_prtt_enrt_rslt_f pen,
 ben_elig_per_f  pep,
 ben_elig_per_opt_f epo,
 ben_pl_f pln,
 ben_pl_regn_f prg,
 ben_regn_f reg ,
 ben_pl_typ_f   ptp,
 ben_pgm_f pgm,
 ben_oipl_f oipl,
 ben_opt_f opt,
  ben_popl_org_f porg,
 ben_popl_org_role_f prol,
 hr_all_organization_units org, 
 hr_locations loc
where pcm.per_cm_id = pcd.per_cm_id
and  :p_effective_date  between pcm.effective_start_date and pcm.effective_end_Date 
and  :p_business_group_id = pcm.business_group_id
and pcm.person_id  = nvl(:p_person_id, pcm.person_id)
and pcd.sent_dt is null 
and :p_effective_date  between pcd.effective_start_date and pcd.effective_end_Date 
and pcm.business_group_id = pcd.business_group_id
and pcm.cm_typ_id  = cct.cm_typ_id
and pcm.business_group_id = cct.business_group_id
and :p_effective_date  between cct.effective_start_date and cct.effective_end_Date 
and cct.shrt_name = 'HIPAAPRT'
and pcu.per_cm_id = pcm.per_cm_id
and pcu.cm_typ_usg_id = ctu.cm_typ_usg_id
and :p_effective_date  between pcu.effective_start_date and pcu.effective_end_Date 
and :p_effective_date  between ctu.effective_start_date and ctu.effective_end_Date 
and pcm.business_group_id = pcu.business_group_id
and pcm.business_group_id = ctu.business_group_id
and pcm.person_id = per.person_id
and  nvl(per.benefit_group_id,-1) = nvl(:P_bnfts_grp_id,nvl(per.benefit_group_id,-1) )
and :p_effective_date  between per.effective_start_date and per.effective_end_Date
and per.person_id = ass.person_id
and ass.primary_flag = 'Y'
and ( ass.assignment_type NOT IN ('C','B')
	  or (ass.assignment_type = 'B' 
	      and not exists 
     		 (select ass1.assignment_type
    		  from per_all_assignments_f ass1
    		  where per.person_id = ass1.person_id
    		  and ass1.assignment_type = 'E'
			  and ass1.primary_flag = 'Y'
    		  and nvl(ass1.location_id,-1) =  nvl(:p_location_id ,nvl(ass1.location_id,-1) )
			  and nvl(ass1.organization_id,-1) = nvl(:P_organization_id,nvl(ass1.organization_id,-1) )
			  and :p_effective_date  between ass1.effective_start_date and ass1.effective_end_Date)
		  )
	)
and  nvl(ass.location_id,-1) =  nvl(:p_location_id ,nvl(ass.location_id,-1) )
and  nvl(ass.organization_id,-1) = nvl(:P_organization_id,nvl(ass.organization_id,-1) )
and  :p_effective_date  between ass.effective_start_date and ass.effective_end_Date
and pcm.business_group_id = per.business_group_id
and pdd.person_id = per.person_id
and nvl(pdd.primary_flag,'N') = 'Y'
and pcm.business_group_id = pdd.business_group_id
and pcm.business_group_id = pbg.business_group_id
and pcm.person_id = pen.person_id
and  pen.prtt_enrt_rslt_Stat_cd is null
and :p_effective_date  between pen.effective_start_date and pen.effective_end_Date 
and ctu.pl_typ_id  = pen.pl_typ_id
and pen.pgm_id    = nvl(:P_Pgm_id , pen.pgm_id) 
and pen.pgm_id = pgm.pgm_id
and pcm.business_group_id = pen.business_group_id
and :p_effective_date  between pgm.effective_start_date and pgm.effective_end_Date 
and pen.pl_id = pln.pl_id
and :p_effective_date  between pln.effective_start_date and pln.effective_end_Date 
and pen.business_group_id = pln.business_group_id
and pln.pl_id = prg.pl_id 
and :p_effective_date  between prg.effective_start_date and prg.effective_end_Date 
and pln.business_group_id = prg.business_group_id
and prg.regn_id  = reg.regn_id 
and :p_effective_date  between reg.effective_start_date and reg.effective_end_Date 
and prg.business_group_id = reg.business_group_id
and reg.STTRY_CITN_NAME  = 'HIPAA'
and pen.pl_typ_id = ptp.pl_typ_id
and :p_effective_date  between ptp.effective_start_date and ptp.effective_end_Date 
and pen.business_group_id = ptp.business_group_id
and pcm.person_id    = pep.person_id
and pen.per_in_ler_id =  pep.per_in_ler_id and pen.pgm_id           = pep.pgm_id
and pen.pl_id               = pep.pl_id
and pcm.business_group_id = pep.business_group_id
and :p_effective_Date  between  pep.effective_start_date and pep.effective_end_Date
and pen.oipl_id = oipl.oipl_id (+)
and :p_effective_date  between oipl.effective_start_date (+) and oipl.effective_end_Date (+)
and oipl.opt_id  = opt.opt_id  (+)
and :P_effective_date  between opt.effective_start_date (+) and opt.effective_end_Date (+)
and pep.elig_per_id = epo.elig_per_id (+)
and (oipl.opt_id = epo.opt_id or oipl.opt_id is null ) 
and :p_effective_date  between epo.effective_start_date (+) and epo.effective_end_Date (+)
and ( (pen.pgm_id = porg.pgm_id  and porg.pl_id is null ) and 
           not exists (select porg1.popl_org_id
  				       from ben_popl_org_f porg1
				       where porg1.pl_id = pen.pl_id
					   and porg1.pgm_id is null )
      or ( pen.pl_id = porg.pl_id and  porg.pgm_id is null  ))
and porg.popl_org_id = prol.popl_org_id (+)
and ( prol.org_role_typ_cd  = 'ADM'  or prol.popl_org_id  is null )
and pbg.organization_id = org.organization_id
and org.location_id = loc.location_id