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 |
