Below is the query for the Benefits Person dependent Report in Oracle Cloud HCM.
SELECT
papf.person_number "DependentEmpNo"
,ppnf.first_name "DependentEmpFirst_Name"
,ppnf.last_name "DependentEmpLastName"
,bpg.name "DEPT_EMP_PROGRAM_NAME"
,bptf.name "DEPT_EMP_PLAN_TYPE_NAME"
,bpf.name "DEPT_EMP_PLAN_NAME"
,bof.name "DEPT_EMP_OPTION_NAME"
,bper.bnft_amt "COVERAGE"
,papf2.person_number "DEPT_PERSON_NUMBER"
,ppnf2.first_name "DEPT_FIRST_NAME"
,ppnf2.last_name "DEPT_LAST_NAME"
,ppl.sex "DEPT_GENDER"
,to_char(pp2.date_of_birth,'YYYY/MM/DD') "DEPT_BIRTH_DATE"
,pni.national_identifier_number "DEPT_SSN"
,to_char(pdp.cvg_strt_dt,'YYYY/MM/DD') "DEPT_COVERAGE_START_DATE"
,pdp.rlnshp_cd "RELATIONSHIP_TYPE"
FROM
per_all_people_f papf
,per_all_people_f papf2
,per_person_names_f ppnf
,per_person_names_f ppnf2
,per_persons pp2
,per_people_legislative_f ppl
,per_national_identifiers pni
,ben_prtt_enrt_rslt bper
,ben_per_in_ler bpil
,ben_pgm_f bpg
,ben_pl_f bpf
,ben_pl_typ_f bptf
,ben_opt_f bof
,ben_elig_cvrd_dpnt pdp
WHERE 1=1
-- AND papf.person_number = '13705'
AND papf.person_id = ppnf.person_id
AND ppnf.name_type = 'GLOBAL'
--AND :Effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
--AND :Effective_date BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
AND papf.person_id = bper.person_id
AND bper.pgm_id = bpg.pgm_id
AND bper.pl_id = bpf.pl_id
AND bper.pl_typ_id = bptf.pl_typ_id
AND bper.opt_id = bof.opt_id(+)
AND bper.per_in_ler_id = bpil.per_in_ler_id
AND bpil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
AND bper.prtt_enrt_rslt_id = pdp.prtt_enrt_rslt_id
AND pdp.dpnt_person_id = papf2.person_id
AND papf2.person_id = pp2.person_id
AND papf2.person_id = ppnf2.person_id
AND ppnf2.name_type = 'GLOBAL'
AND papf2.person_id = ppl.person_id
AND papf2.person_id = pni.person_id(+)
-- AND pni.national_identifier_type = 'SSN'
--AND :Effective_date BETWEEN papf2.effective_start_date AND papf2.effective_end_date
--AND :Effective_date BETWEEN ppnf2.effective_start_date AND ppnf2.effective_end_date
--AND :Effective_date BETWEEN ppl.effective_start_date AND ppl.effective_end_date
--AND ((COALESCE(:Plan_Name,NULL) IS NULL) OR ((COALESCE(:Plan_Name,NULL) IS NOT NULL) AND bpf.pl_id in (:Plan_Name)))
--AND ((COALESCE(:Plan_Type,NULL) IS NULL) OR ((COALESCE(:Plan_Type,NULL) IS NOT NULL) AND bptf.pl_typ_id in (:Plan_Type)))
--AND ((COALESCE(:Program_Name,NULL) IS NULL) OR
((COALESCE(:Program_Name,NULL) IS NOT NULL) AND bpg.pgm_id in
(:Program_Name)))
/*AND :Effective_date BETWEEN bper.enrt_cvg_strt_dt AND nvl(bper.enrt_cvg_thru_dt,to_date('4712/12/31','YYYY/MM/DD'))
AND :Effective_date BETWEEN pdp.CVG_STRT_DT AND nvl(pdp.CVG_THRU_DT,to_date('4712/12/31','YYYY/MM/DD'))
AND :Effective_date BETWEEN bpg.effective_start_date AND bpg.effective_end_date
AND :Effective_date BETWEEN bpf.effective_start_date AND bpf.effective_end_date
AND :Effective_date BETWEEN bptf.effective_start_date AND bptf.effective_end_date
AND :Effective_date BETWEEN bof.effective_start_date(+) AND bof.effective_end_date(+)*/