BEN COBRA Benefit Initial Information

 select
 distinct 
 sysdate dptn_current_date,
 cont.person_id  dpnt_prtt_per_id,
 pcd.to_be_sent_dt    dpnt_to_be_Sent_Dt ,
 per.first_name || ' '|| per.last_name||' '||per.suffix  dpnt_full_name,
 per.first_name || ' '|| per.last_name  dpnt_PER_name,
 per.national_identifier   dpnt_ssn_id,
 decode(cont. RLTD_PER_RSDS_W_DSGNTR_FLAG ,'Y',prtpdd.address_line1,pdd.address_line1 )  dpnt_addr1,
 decode(cont. RLTD_PER_RSDS_W_DSGNTR_FLAG ,'Y',prtpdd.address_line2,pdd.address_line2) dpnt_addr2,
decode(cont. RLTD_PER_RSDS_W_DSGNTR_FLAG ,'Y',prtpdd.address_line3,pdd.address_line3) dpnt_addr3,
 decode(cont.RLTD_PER_RSDS_W_DSGNTR_FLAG  ,'Y',prtpdd.town_or_city,pdd.town_or_city)  dpnt_City,
 decode(cont.RLTD_PER_RSDS_W_DSGNTR_FLAG ,'Y',prtpdd.region_2,pdd.region_2) dpnt_State,
 decode(cont. RLTD_PER_RSDS_W_DSGNTR_FLAG ,'Y',prtpdd.postal_code,pdd.postal_code)"dpnt_Zip Code" ,
 pbg.name "dpnt_Company_Name",
 pdp.cvg_strt_dt "dpnt_Coverage_Start_Date",
 prol.name "dpnt_Plan Administrator Name" ,
 pcm.per_cm_id ,
 loc.address_line_1 ||loc.address_line_2||loc.address_line_3  dpnt_loc_address,
 loc.address_line_1  loc_address1 ,
 loc.address_line_2 loc_address2 ,
loc.address_line_3  loc_address3 ,
 loc.town_or_city  dpnt_loc_City,
 loc.region_2  dpnt_loc_State,
 loc.postal_code "dpnt_loc_Zip_Code" ,
 loc.telephone_number_1 dpnt_loc_ph_1,
 loc.telephone_number_2 dpnt_loc_ph_2,
 loc.telephone_number_3  dpnt_loc_ph_3,
 pdp.dpnt_person_id,
 BEN_BENCBRIN_XMLP_PKG.CF_Dep_Check_FlagFormula(pdp.dpnt_person_id,:pl_typ_id,:pgm_id,pdp.cvg_strt_dt,:p_effective_date,:P_BUSINESS_GROUP_ID) CF_DEP_CHECK_FLAG
 from
      PER_CONTACT_RELATIONSHIPS cont ,
      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_addresses pdd,
      per_addresses prtpdd,
      per_business_groups pbg,
      ben_elig_cvrd_dpnt_f pdp,
      ben_prtt_enrt_rslt_f pen,
      ben_pgm_f   pgm,
      ben_pl_f    pln ,
      ben_pl_regn_f prg,
      ben_regn_f reg ,
      ben_popl_org_f porg,
      ben_popl_org_role_f prol,
      hr_all_organization_units org ,
      hr_locations loc
where cont.business_group_id =  :p_business_group_id
and   contact_type = 'S'   /* Spouse*/
and  :p_effective_date between nvl(cont.date_start,:p_effective_date ) and nvl(cont.date_end, :p_effective_date ) 
and cont.person_id = pcm.person_id
and cont.person_id = :per_id
and :p_effective_date  between pcm.effective_start_date and pcm.effective_end_Date
and cont.business_group_id  = pcm.business_group_id
and pcm.per_cm_id = pcd.per_cm_id
and pcm.per_cm_id = :per_cm_id
--and pcd.sent_dt is null  
AND pcd.per_cm_prvdd_stat_cd = 'ACTIVE'
and pcm.business_group_id = pcd.business_group_id
and :p_effective_date between pcd.effective_start_date and pcd.effective_end_Date
---
and pcm.cm_typ_id  = cct.cm_typ_id
and cct.shrt_name = 'CBRININF'
and pcm.business_group_id = cct.business_group_id
and :p_effective_date between cct.effective_start_date and cct.effective_end_Date
---
and pcm.per_cm_id = pcu.per_cm_id
and pcm.business_group_id = pcu.business_group_id
and :p_effective_date between pcu.effective_start_date and pcu.effective_end_Date
---
and pcu.cm_typ_usg_id = ctu.cm_typ_usg_id
and pcm.business_group_id = ctu.business_group_id
and :p_effective_date between ctu.effective_start_date and ctu.effective_end_Date
---
and cont.contact_person_id = per.person_id
and cont.business_group_id = per.business_group_id
and :p_effective_date between per.effective_start_date and per.effective_end_Date
---
and  per.person_id = pdd.person_id (+)
and (pdd.primary_flag  = 'Y'  or  pdd.person_id  is null)
and  per.business_group_id  = pdd.business_group_id (+)
---
and  cont.person_id = prtpdd.person_id (+)
and (prtpdd.primary_flag  = 'Y'  or  prtpdd.person_id  is null)
and  per.business_group_id  = pdd.business_group_id (+)
---
and pcm.business_group_id = pbg.business_group_id
---
and per.person_id = pdp.dpnt_person_id
and pcm.business_group_id = pdp.business_group_id
and :p_effective_date between pdp.effective_start_date and pdp.effective_end_Date
---
and pdp.prtt_enrt_rslt_id  = pen.prtt_enrt_rslt_id
and pen.per_in_ler_id      = pcm.per_in_ler_id
and pen.enrt_cvg_thru_dt = to_date('12/31/4712','MM/DD/YYYY')
and pen.prtt_enrt_rslt_Stat_cd is null
and pen.sspndd_flag = 'N'
and ctu.pl_typ_id  = pen.pl_typ_id
and pcm.business_group_id = pen.business_group_id
and :p_effective_date between pen.effective_start_date and pen.effective_end_Date
---
and pen.pgm_id = pgm.pgm_id
and pen.business_group_id = pgm.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 pen.business_group_id = pln.business_group_id
and pen.effective_start_date  between pln.effective_start_date and pln.effective_end_Date
and pln.pl_id = prg.pl_id
AND pln.invk_flx_cr_pl_flag = 'N'
AND pln.imptd_incm_calc_cd is null
and pln.business_group_id = prg.business_group_id
and :p_effective_date between prg.effective_start_date and prg.effective_end_Date
---
and prg.regn_id  = reg.regn_id
and reg.STTRY_CITN_NAME  = 'COBRA'
and prg.business_group_id = reg.business_group_id
and :p_effective_date between reg.effective_start_date and reg.effective_end_Date
---
and ( (pen.pgm_id = porg.pgm_id   and  porg.pl_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 porg.organization_id = org.organization_id
---
and org.location_id = loc.location_id