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.