Benefits Person dependent SQL Query in Oracle Cloud HCM

 

 

 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(+)*/