This Benefits Eligible Dependent Covered query displays benefit coverage details and amounts for employees and their eligible dependents, helping HR teams monitor benefit eligibility and coverage periods.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- Employee and dependent benefits coverage reports
PAPF.PERSON_NUMBER EMPLOYEE_NUMBER,
PPNFV.FULL_NAME EMPLOYEE_NAME,
DPNFV.FULL_NAME DEPENDENT_NAME,
HL.MEANING DEPENDENT_RELATIONSHIP,
BECD.CVG_STRT_DT DEPENDENT_COVERAGE_START_DATE,
BECD.CVG_THRU_DT DEPENDENT_COVERAGE_END_DATE,
BPER.ENRT_CVG_STRT_DT EMPLOYEE_COVERAGE_START_DATE,
BPER.ENRT_CVG_THRU_DT EMPLOYEE_COVERAGE_END_DATE,
BPER.UOM CURRENCY,
BPER.BNFT_AMT COVERAGE_AMOUNT
FROM
BEN_PRTT_ENRT_RSLT BPER,
BEN_ELIG_CVRD_DPNT BECD,
PER_CONTACT_RELSHIPS_F PCRF,
PER_ALL_PEOPLE_F PAPF,
PER_ALL_PEOPLE_F DPAPF,
PER_PERSON_NAMES_F_V PPNFV,
PER_PERSON_NAMES_F_V DPNFV,
HR_LOOKUPS HL
WHERE
BECD.ELIG_CVRD_DPNT_ID = (
SELECT MAX(B2.ELIG_CVRD_DPNT_ID)
FROM BEN_ELIG_CVRD_DPNT B2
WHERE B2.DPNT_PERSON_ID = BECD.DPNT_PERSON_ID
AND B2.CVG_STRT_DT = (
SELECT MAX(B3.CVG_STRT_DT)
FROM BEN_ELIG_CVRD_DPNT B3
WHERE B3.DPNT_PERSON_ID = BECD.DPNT_PERSON_ID
)
)
AND BPER.PRTT_ENRT_RSLT_ID = BECD.PRTT_ENRT_RSLT_ID
AND BECD.DPNT_PERSON_ID = PCRF.CONTACT_PERSON_ID
AND BPER.PERSON_ID = PCRF.PERSON_ID
AND PCRF.PERSON_ID = PAPF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND PCRF.CONTACT_PERSON_ID = DPAPF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN DPAPF.EFFECTIVE_START_DATE AND DPAPF.EFFECTIVE_END_DATE
AND PAPF.PERSON_ID = PPNFV.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PPNFV.EFFECTIVE_START_DATE AND PPNFV.EFFECTIVE_END_DATE
AND PPNFV.NAME_TYPE = 'GLOBAL'
AND DPAPF.PERSON_ID = DPNFV.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN DPNFV.EFFECTIVE_START_DATE AND DPNFV.EFFECTIVE_END_DATE
AND DPNFV.NAME_TYPE = 'GLOBAL' --THIS FILTERS DEPENDENT NAMES TO INCLUDE ONLY THE STANDARD GLOBAL VERSION, AVOIDING LOCAL OR ALTERNATE NAME TYPES.
AND HL.LOOKUP_CODE = BECD.RLNSHP_CD
AND HL.LOOKUP_TYPE = 'CONTACT' --THIS RESTRICTS THE LOOKUP TO ONLY THOSE CODES THAT DEFINE CONTACT RELATIONSHIPS, LIKE DEPENDENT TYPES (SPOUSE, CHILD, ETC.).
AND TRUNC(SYSDATE) BETWEEN PCRF.EFFECTIVE_START_DATE AND PCRF.EFFECTIVE_END_DATE
AND BECD.CVG_THRU_DT >= TRUNC(SYSDATE)
AND NVL(BPER.PRTT_ENRT_RSLT_STAT_CD, 'X') NOT IN ('VOIDD', 'BCKDT') --THIS CONDITION FILTERS OUT ANY BENEFIT ENROLLMENT RECORDS THAT WERE VOIDED OR CANCELLED, ENSURING ONLY ACTIVE OR VALID ENROLLMENTS ARE RETURNED. IF THE STATUS CODE IS MISSING (NULL), IT ASSUMES THE RECORD IS VALID BY USING 'X' AS A DEFAULT.
AND BPER.ENRT_CVG_THRU_DT >= TRUNC(SYSDATE)