In this session, we are going to see how to fetch the Oracle Cloud Benefits Enrollment Data using SQL
SQL Text:
select papf.person_number "EmplId"
,ppnf.display_name "EmplName"
,to_char(pp.date_of_birth,'mm/dd/yyyy') "DoB"
,pplf.sex "EmplGender"
,pl.name "PlanName"
,opt.name "OptionName"
,pdpnf.display_name "DepName"
,ppdlf.sex "DepGender"
,to_char(ppd.date_of_birth,'mm/dd/yyyy') "DepDoB"
,per_extract_utility.get_decoded_lookup('ORA_PER_CONTACT',pdp.rlnshp_cd) "DepRel"
from per_all_people_f papf
,per_all_assignments_f paam
,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
,per_persons pp
,per_person_names_f ppnf
,per_assignment_status_types_vl pastvl
,per_periods_of_service ppos
,ben_elig_cvrd_dpnt pdp
,per_person_names_f pdpnf
,per_persons ppd
,per_people_legislative_f pplf
,per_people_legislative_f ppdlf
where 1 = 1
and pen.prtt_enrt_rslt_stat_cd is null
and pil.ler_id = ler.ler_id
and case when pen.ended_per_in_ler_id is not null then pen.ended_per_in_ler_id else PEN.per_in_ler_id end = 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 = paam.business_group_id
and brn.rel_prmry_asg_id = paam.assignment_id
and paam.person_id = pen.person_id
and paam.person_id = papf.person_id
and paam.legislation_code = 'US'
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 pp.person_id (+) = papf.person_id
and ppnf.person_id = papf.person_id
and ppnf.name_type = 'GLOBAL'
and pastvl.assignment_status_type_id = paam.assignment_status_type_id
and ppos.period_of_service_id = paam.period_of_service_id
and pdp.prtt_enrt_rslt_id(+) = pen.prtt_enrt_rslt_id
and case when pdp.cvg_strt_dt is not null then pdp.cvg_strt_dt else sysdate end <=
case when pdp.cvg_thru_dt is not null then pdp.cvg_thru_dt else sysdate end
and pdpnf.person_id(+) = pdp.dpnt_person_id
and pdpnf.name_type(+) = 'GLOBAL'
and ppd.person_id (+) = pdpnf.person_id
and pplf.person_id (+) = paam.person_id
and pplf.legislation_code (+) = paam.legislation_code
and ppdlf.person_id (+) = pdp.dpnt_person_id
and ppdlf.legislation_code (+) = paam.legislation_code
and opt.name not like '%Waive%'
and pen.sspndd_flag = 'N'
and pen.enrt_cvg_strt_dt <= pen.enrt_cvg_thru_dt
and sysdate between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
and pen.enrt_cvg_strt_dt between paam.effective_start_date and paam.effective_end_date
and pen.enrt_cvg_strt_dt between papf.effective_start_date and papf.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 ppnf.effective_start_date and ppnf.effective_end_date
and pen.enrt_cvg_strt_dt between pplf.effective_start_date(+) and pplf.effective_end_date(+)
and pdp.cvg_strt_dt between ppdlf.effective_start_date(+) and ppdlf.effective_end_date(+)
and pdp.cvg_strt_dt between pdpnf.effective_start_date(+) and pdpnf.effective_end_date(+)
and sysdate between pdp.cvg_strt_dt(+) and pdp.cvg_thru_dt(+)
Comments
Post a Comment