BEN COBRA Benefit Notification Letter

select
sysdate current_date,
per.person_id per_id,
per.business_group_id,
per.last_name,
per.first_name,
RTRIM(per.first_name) ||' '|| RTRIM(per.last_name)|| ' '||Rtrim(per.suffix)  fname,
RTRIM(per.first_name) ||' '|| RTRIM(per.last_name)  person_name,
per.employee_number,
per.national_identifier ,
pcm.lf_evt_ocrd_dt pcm_ocrd_dt,
pcd.address_id pcd_adr_id,
pcd.per_cm_prvdd_id ,
ler.name ler_name,
ler.typ_cd Ler_type,
pil.per_in_ler_id pil_id,
epe.enrt_cvg_strt_dt,
epe.enrt_cvg_strt_dt+60 resp_dt,
epe.enrt_cvg_strt_dt-1 as enrt_cvg_thru_dt,
pln.name pl_name,
pln.pl_id  pl_id ,
pln.COBRA_PYMT_DUE_DY_NUM,
opt.name opt_name,
ecr.cmcd_val,
ecr.cmcd_acty_ref_perd_cd,
adr.primary_flag,
adr.address_line1 addr1,
adr.address_line2 addr2,
adr.address_line3 addr3,
adr.town_or_city city,
adr.region_2 state,
adr.postal_code zip,
adr.country country,
cpr.name  admin_name,
pbg.name  org_name,
org.name  admin_org_name,
loc.address_line_1 loc_addr1,
loc.address_line_2 loc_addr2,
loc.address_line_3 loc_addr3,
loc.town_or_city   loc_city,
loc.region_2       loc_state,
loc.postal_code    loc_zip,
loc.country        loc_country,
loc.telephone_number_1 loc_phone,
bcq.cbr_elig_perd_strt_dt,
bcq.cbr_elig_perd_end_dt,
months_between ( bcq.cbr_elig_perd_end_dt+1, bcq.cbr_elig_perd_strt_dt)  as due_months, 
	BEN_BEN_US_COBNL_XMLP_PKG.cf_1formula(cpr.name, loc.address_line_1, loc.address_line_2, loc.address_line_3,loc.town_or_city, loc.region_2, loc.postal_code, loc.telephone_number_1,pcd.per_cm_prvdd_id) CF_admin_detail,
	BEN_BEN_US_COBNL_XMLP_PKG.cp_effective_date_p cp_effective_date,
	BEN_BEN_US_COBNL_XMLP_PKG.CP_ler_text_p(ler.typ_cd,ler.name,pcm.lf_evt_ocrd_dt) CP_ler_text, 
	BEN_BEN_US_COBNL_XMLP_PKG.cf_cmcd_acty_rep_prdformula(ecr.cmcd_acty_ref_perd_cd) CF_cmcd_acty_rep_prd
from
  ben_per_cm_f pcm
 ,ben_per_cm_prvdd_f pcd
 ,ben_cm_typ_f cct
 ,per_all_people_f per
  ,per_addresses adr
 ,ben_per_in_ler pil
 ,ben_ler_f ler
 ,ben_elig_per_elctbl_chc epe
 ,ben_enrt_rt  ecr
 ,ben_pgm_f pgm
 ,ben_pl_f pln
 ,ben_opt_f opt
 ,ben_oipl_f oip
 ,ben_pl_regn_f prg
 ,ben_regn_f reg
 ,ben_popl_org_f cpo
 ,ben_popl_org_role_f cpr
 ,hr_all_organization_units org
 ,hr_locations_all loc
 ,hr_all_organization_units pbg
 ,ben_cbr_quald_bnf bcq
 ,ben_cbr_per_in_ler  bcp
where
      pcm.business_group_id  = :P_business_group_id
 and  (:P_person_id is null or pcm.person_id   = :P_person_id)
 AND pcm.per_cm_id = pcd.per_cm_id
 AND pcd.per_cm_prvdd_stat_cd = 'ACTIVE'
 AND pcd.sent_dt is NULL
 AND pcm.cm_typ_id = cct.cm_typ_id
 AND cct.shrt_name = 'CBRANOTF'
 and pcm.person_id = per.person_id
 and (:P_bnfts_grp_id is null  or  per.benefit_group_id= :P_bnfts_grp_id)
 and (  (:p_location_id  is null  and  :P_organization_id is null ) 
         or ( exists ( select 'x'  
                                from  per_all_assignments_f ass
                                 where (:p_location_id is null or  ass.location_id=  :p_location_id )
                                    and ( :P_organization_id is null or  ass.organization_id = :P_organization_id)
                                    and  per.person_id             = ass.person_id
                                    and  ass.primary_flag        = 'Y'
                                    and  ass.assignment_type <> 'C'      
                                    and  :p_effective_date  between  ass.effective_start_date 
                                           AND   ass.effective_end_date
                          )
         )   )
 AND (   (pcd.address_id is null and  per.person_id = adr.person_id AND
             nvl(adr.primary_flag,'Y') = 'Y'
             and :p_effective_date between adr.DATE_FROM and nvl(adr.DATE_TO,to_date('31-12-4712','DD-MM-YYYY'))
            )
       OR (pcd.address_id is not null and pcd.address_id = adr.address_id)
      )
 and pcm.per_in_ler_id = pil.per_in_ler_id      
 and pil.per_in_ler_stat_cd NOT IN ('BCKDT', 'VOIDD')
 and pil.ler_id        = ler.ler_id
 and pcm.per_in_ler_id = epe.per_in_ler_id
 and epe.elctbl_flag   = 'Y'
 AND epe.pgm_id = pgm.pgm_id (+)
 AND (:p_pgm_id is null or  epe.pgm_id = :p_pgm_id )     
 and (epe.pgm_id is null or  
      pgm.pgm_typ_cd  IN ('COBRANFLX', 'COBRAFLX')
      )
 AND epe.pl_id   =  pln.pl_id
 AND epe.oipl_id = oip.oipl_id(+)
 AND oip.opt_id   = opt.opt_id(+)
 AND pln.invk_flx_cr_pl_flag = 'N'
 AND pln.imptd_incm_calc_cd is null
 AND epe.elig_per_elctbl_chc_id = ecr.elig_per_elctbl_chc_id (+)
 and pln.pl_id   = prg.pl_id
 and reg.regn_id = prg.regn_id
 and reg.sttry_citn_name = 'COBRA'
   and ( ( cpo.pl_id is not null and cpo.pgm_id is null
         and cpo.pl_id = pln.pl_id
         AND cpr.popl_org_id = cpo.popl_org_id   
         AND cpr.org_role_typ_cd = 'ADM'
        )
     or (cpo.pgm_id is not null and cpo.pl_id is null
         and cpo.pgm_id = pgm.pgm_id and
         pln.pl_id not in  ( select cpo.pl_id
                              from  ben_popl_org_f cpo1
                              where  cpo1.pl_id is not null and cpo1.pgm_id is null
                              and cpo1.pl_id = pln.pl_id
                             )
         AND cpr.popl_org_id = cpo.popl_org_id    
         AND cpr.popl_org_id = cpo.popl_org_id  
        )                      
     )
  AND cpo.organization_id = org.organization_id    
 AND org.location_id = loc.location_id
 and pbg.organization_id  = pcm.business_group_id
 and bcp.per_in_ler_id  = pcm.per_in_ler_id
 and bcp.cbr_quald_bnf_id = bcq.cbr_quald_bnf_id
 and bcq.QUALD_BNF_PERSON_ID = pcm.person_id
 and bcq.QUALD_BNF_FLAG     = 'Y'
 and bcq.pgm_id             = epe.pgm_id 
 and (bcq.pl_typ_id is null or bcq.pl_typ_id = epe.pl_typ_id)
AND :p_effective_date  between
         pcm.effective_start_date AND
         pcm.effective_end_date
AND :p_effective_date  between
         pcd.effective_start_date AND
         pcd.effective_end_date
AND :p_effective_date  between
         cct.effective_start_date AND
         cct.effective_end_date
AND :p_effective_date  between
         per.effective_start_date AND
         per.effective_end_date
and :p_effective_date   between
         ler.effective_start_date AND
         ler.effective_end_date        
and :p_effective_date   between
         pgm.effective_start_date (+) AND
         pgm.effective_end_date (+)        
and :p_effective_date   between
         pln.effective_start_date  AND
         pln.effective_end_date
and :p_effective_date   between
         oip.effective_start_date (+) AND
         oip.effective_end_date (+)        
and :p_effective_date   between
         opt.effective_start_date (+) AND
         opt.effective_end_date (+)        
and :p_effective_date   between
         prg.effective_start_date  AND
         prg.effective_end_date         
and :p_effective_date   between
         reg.effective_start_date  AND
         reg.effective_end_date         
and :p_effective_date   between
         cpo.effective_start_date  AND
         cpo.effective_end_date         
and :p_effective_date   between
         cpr.effective_start_date  AND
         cpr.effective_end_date  
--order by pcd.to_be_sent_dt, per.last_name ,epe.enrt_cvg_strt_dt 
order by fname,per_id,business_group_id,last_name,first_name,person_name,employee_number,national_identifier,pcm_ocrd_dt,
pcd_adr_id,current_date,Ler_type,per_cm_prvdd_id,admin_org_name,ler_name,pil_id,primary_flag,addr1,addr2,addr3,city,state,zip,
country,admin_name,org_name,loc_addr1,loc_addr2,loc_addr3,loc_city,loc_state,loc_zip,loc_country,loc_phone,cbr_elig_perd_strt_dt,cbr_elig_perd_end_dt,
due_months,pl_name,enrt_cvg_thru_dt,resp_dt,enrt_cvg_strt_dt,pl_id,COBRA_PYMT_DUE_DY_NUM,cmcd_acty_ref_perd_cd