Benefits Enrollment Query - Oracle Cloud HCM

SELECT 

  papf.person_number,

  PEN.CONFIG_NUM_1, 

  PEN.assignment_id, 

  PEN.benefit_relation_id, 

  BRN.benefit_relation_name, 

  BRN.BENEFIT_REL_SYSTEM_CD, 

  PEN.TYPE_ID, 

  PEN.CONFIG_CHAR_1, 

  PEN.bnft_amt, 

  PEN.bnft_nnmntry_uom, 

  PEN.bnft_ordr_num, 

  PEN.bnft_typ_cd, 

  BRN.STATUS, 

  PEN.business_group_id, 

  phf.cvrd_in_anthr_pl, 

  phf.disability_status, 

  PEN.enrt_cvg_strt_dt, 

  PEN.enrt_cvg_thru_dt, 

  PEN.enrt_mthd_cd, 

  PEN.enrt_ovridn_flag, 

  PEN.enrt_ovrid_rsn_cd, 

  PEN.enrt_ovrid_thru_dt, 

  PEN.erlst_deenrt_dt, 

  BRN.legal_entity_id, 

  LER.DESC_TXT, 

  LER.GLOBAL_FLAG LER_GLOBAL_FLAG, 

  PEN.ler_id, 

  LER.INSTRUCTION_TEXT, 

  LER.name LER_NAME, 

  LER.QUALG_EVT_FLAG, 

  LER.SELF_ASSIGNED_EVENT_FLAG, 

  LER.SHORT_CODE LER_SHORT_CODE, 

  LER.SHORT_NAME LER_SHORT_NAME, 

  LER.SLCTBL_SLF_SVC_CD, 

  LER.TYP_CD, 

  PEN.no_lngr_elig_flag, 

  PEN.oipl_id, 

  OIPL.CONFIG_CHAR_1 OIPL_CONFIG_CHAR_1, 

  OPT.name OPT_name, 

  PEN.orgnl_enrt_dt, 

  BRN.ORIGINAL_SOURCE, 

  PEN.person_id, 

  PGM.CONFIG_CHAR_1 PGM_CONFIG_CHAR_1, 

  PL.CONFIG_CHAR_3, 

  PEN.per_in_ler_id, 

  pgm.alws_unrstrctd_enrt_flag PGM_alws_unrstrctd_enrt_flag, 

  pgm.global_flag PGM_GLOBAL_FLAG, 

  PEN.pgm_id, 

  PGM.name PGM_name, 

  pgm.short_code PGM_SHORT_CODE, 

  pgm.short_name PGM_SHORT_NAME, 

  pgm.pgm_stat_cd, 

  pl.alws_unrstrctd_enrt_flag PL_alws_unrstrctd_enrt_flag, 

  PL.FUNCTION_CODE, 

  PL.GLOBAL_flag PL_GLOBAL_FLAG, 

  PEN.pl_id, 

  PL.SUBJ_TO_IMPTD_INCM_CD, 

  PL.FRFS_APLY_FLAG, 

  PL.name PL_name, 

  PL.short_code PL_SHORT_CODE, 

  PL.short_name PL_SHORT_NAME, 

  pl.pl_stat_cd, 

  PEN.pl_typ_id, 

  BRN.PRIMARY_REL, 

  PEN.prtt_enrt_rslt_id, 

  PEN.prtt_enrt_rslt_stat_cd, 

  PEN.prtt_is_cvrd_flag, 

  PEN.ptip_id, 

  ptp.ADMIN_CATEGORY_CD, 

  ptp.CARRIER_PLAN_TYPE_NAME, 

  ptp.global_flag PTP_GLOBAL_FLAG, 

  ptp.name PTP_name, 

  ptp.SS_CATEGORY_CD, 

  phf.receipt_of_death_cert_date, 

  phf.registered_disabled_flag, 

  BRN.rel_prmry_asg_id, 

  PEN.rplcs_sspndd_rslt_id, 

  PEN.sspndd_flag, 

  phf.student_status, 

  PHF.tobacco_type_usage, 

  PEN.uom, 

  BRN.UPDATED_SOURCE, 

  PEN.ELECTION_DATE, 

  PEN.INTERIM_FLAG, 

  PIL.LF_EVT_OCRD_DT, 

  PIL.PER_IN_LER_STAT_CD, 

  PEN.CREATED_BY, 

  PEN.CREATION_DATE, 

  PEN.LAST_UPDATED_BY, 

  PEN.LAST_UPDATE_DATE, 

  PEN.LAST_UPDATE_LOGIN, 

  OPT.SHORT_CODE OPT_SHORT_CODE, 

  OPT.SHORT_NAME OPT_SHORT_NAME

FROM 

  per_all_people_f papf, 

  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_per_le_habits_cov_f phf 

WHERE 

  trunc(sysdate) between pen.enrt_cvg_strt_dt AND pen.enrt_cvg_thru_dt 

  AND pen.enrt_cvg_strt_dt <= pen.enrt_cvg_thru_dt 

  AND pen.enrt_cvg_strt_dt BETWEEN papf.effective_start_date 

  AND papf.effective_end_date 

  AND pen.enrt_cvg_strt_dt BETWEEN ASSIGN.effective_start_date 

  AND ASSIGN.effective_end_date 

  AND pen.enrt_cvg_strt_dt BETWEEN LER.effective_start_date 

  AND LER.effective_end_date 

  AND pen.enrt_cvg_strt_dt BETWEEN BRN.effective_start_date 

  AND BRN.effective_end_date 

  AND pen.enrt_cvg_strt_dt BETWEEN PGM.effective_start_date(+) 

  AND PGM.effective_end_date(+) 

  AND pen.enrt_cvg_strt_dt BETWEEN PL.effective_start_date(+) 

  AND PL.effective_end_date(+) 

  AND pen.enrt_cvg_strt_dt BETWEEN OIPL.effective_start_date(+) 

  AND OIPL.effective_end_date(+) 

  AND pen.enrt_cvg_strt_dt BETWEEN OPT.effective_start_date(+) 

  AND OPT.effective_end_date(+) 

  AND pen.enrt_cvg_strt_dt BETWEEN ptp.effective_start_date(+) 

  AND ptp.effective_end_date(+) 

  AND pen.enrt_cvg_strt_dt BETWEEN phf.effective_start_date(+) 

  AND phf.effective_end_date(+) 

  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 papf.person_id = ASSIGN.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 PEN.opt_id = OPT.opt_id(+)

  AND papf.person_number = '12345'