SQL Query to get the Dropped Dependents from Benefits Coverage
SQL Query to get the Dropped Dependents from Benefits Coverage

 526 total views

If you have implemented Benefits, there would be requirement to create reports for the employee/dependent enrollment and dropped coverage.

Enrollment query is the most common query and can be found in google results. However getting the dropped coverage information would require time and effort as we don’t have a ready made query anywhere.

One of my friend MansurAlumKhan Khalil has created the query for getting the dropped dependents and was kind enough to share it with a wider audience.

SQL Query for Dropped Dependents:

SELECT pdp.dpnt_person_id
FROM per_all_assignments_f ASSIGN,
ben_prtt_enrt_rslt PEN,
ben_ler_f ler,
ben_per_in_ler pil,
ben_pl_f PL,
ben_pl_typ_f ptp,
ben_pgm_f PGM,
ben_oipl_f OIPL,
ben_opt_f OPT,
ben_benefit_relations_f brn,
ben_elig_cvrd_dpnt pdp,
per_person_names_f_v ppnf,
ben_per_le_habits_cov_f phf,
ben_per_le_habits_cov_f pdhf,
per_national_identifiers pni,
per_national_identifiers pni1,
per_people_legislative_f pplf,
per_persons pp,
per_addresses_f paf,
per_person_addr_usages_f ppauf,
per_phones pph,
per_person_type_usages_m pptum,
per_all_people_f papf,
per_all_people_f papf1,
per_periods_of_service ppos
WHERE PDP.CVG_THRU_DT BETWEEN ADD_MONTHS(sysdate, - 1)
AND sysdate
AND pen.enrt_cvg_strt_dt <= pen.enrt_cvg_thru_dt
AND TRUNC(SYSDATE) BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
AND TRUNC(SYSDATE) BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
AND TRUNC(SYSDATE) BETWEEN LER.effective_start_date
AND LER.effective_end_date
AND TRUNC(SYSDATE) BETWEEN BRN.effective_start_date
AND BRN.effective_end_date
AND TRUNC(SYSDATE) BETWEEN PGM.effective_start_date(+)
AND PGM.effective_end_date(+)
AND TRUNC(SYSDATE) BETWEEN PL.effective_start_date(+)
AND PL.effective_end_date(+)
AND TRUNC(SYSDATE) BETWEEN OIPL.effective_start_date(+)
AND OIPL.effective_end_date(+)
AND TRUNC(SYSDATE) BETWEEN OPT.effective_start_date(+)
AND OPT.effective_end_date(+)
AND TRUNC(SYSDATE) BETWEEN ptp.effective_start_date(+)
AND ptp.effective_end_date(+)
AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date(+)
AND ppnf.effective_end_date(+)
AND TRUNC(SYSDATE) BETWEEN phf.effective_start_date(+)
AND phf.effective_end_date(+)
AND TRUNC(SYSDATE) BETWEEN pdhf.effective_start_date(+)
AND pdhf.effective_end_date(+)
AND TRUNC(SYSDATE) BETWEEN pplf.effective_start_date(+)
AND pplf.effective_end_date(+)
AND TRUNC(SYSDATE) BETWEEN nvl(pni.issue_date, TRUNC(SYSDATE))
AND nvl(pni.expiration_date, TRUNC(SYSDATE))
AND TRUNC(SYSDATE) BETWEEN nvl(pni1.issue_date, TRUNC(SYSDATE))
AND nvl(pni1.expiration_date, TRUNC(SYSDATE))
AND PEN.prtt_enrt_rslt_stat_cd IS NULL
AND PEN.ler_id = LER.ler_id
AND PEN.per_in_ler_id = pil.per_in_ler_id
AND pil.per_in_ler_stat_cd IN ('STRTD','PROCD')
AND PEN.benefit_relation_id = BRN.benefit_relation_id
AND PEN.business_group_id = ASSIGN.business_group_id
AND BRN.rel_prmry_asg_id = ASSIGN.assignment_id
AND ASSIGN.person_id = PEN.person_id
AND PEN.person_id = phf.person_id(+)
AND PEN.pgm_id = PGM.pgm_id(+)
AND PEN.pl_id = PL.pl_id(+)
AND PEN.pl_typ_id = ptp.PL_TYP_ID(+)
AND PEN.oipl_id = OIPL.oipl_id(+)
AND OIPL.opt_id = OPT.opt_id(+)
AND pen.prtt_enrt_rslt_id = pdp.prtt_enrt_rslt_id(+)
AND pdp.dpnt_person_id = ppnf.person_id(+)
AND pdp.dpnt_person_id = pdhf.person_id(+)
AND pdp.dpnt_person_id = pni1.person_id(+)
AND ppnf.person_id = pplf.person_id(+)
AND ppnf.legislation_code = pplf.legislation_code(+)
AND pdp.dpnt_person_id = pp.person_id(+)
AND pdp.dpnt_person_id = pptum.person_id(+)
AND pdp.dpnt_person_id = papf.person_id(+)
AND PEN.person_id = pni.person_id(+)
AND PEN.person_id = papf1.person_id(+)
AND PEN.person_id = pph.person_id(+)
AND pph.phone_id = papf1.primary_phone_id(+)
AND PEN.person_id = ppauf.person_id(+)
AND ppauf.address_id = paf.address_id
AND ASSIGN.period_of_service_id = ppos.period_of_service_id
AND papf1.MAILING_ADDRESS_ID = paf.address_id
AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN ppauf.effective_start_date
AND ppauf.effective_end_date
AND TRUNC(sysdate) BETWEEN pptum.effective_start_date
AND pptum.effective_end_date
AND pptum.effective_latest_change = 'Y'
AND pptum.system_person_type = 'CON'
AND TRUNC(sysdate) BETWEEN papf1.effective_start_date
AND papf1.effective_end_date
AND TRUNC(sysdate) BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND pl.name LIKE '%Aetna%'

Note: The above query would give you the dropped dependents for the last one month. If you want you can change the number of months and date range criteria in the SQL Query.

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

Leave a Reply