Overview
This cleanup involves correcting dependent relationships in PeopleSoft so there will be no issues with their coverage upon conversion to CHRS. An SQL is provided that will search your database for current enrollment in Health plans with dependents having an incorrect relationship for benefits purposes.
Anyone with history of these codes or terminated benefits will not show in the results. Those historical values will be mapped by conversion but any current enrollments should be corrected.
Instructions
Run the SQL provided. (If you do not have access to SQL – ask your developer to run the provided SQL in your PROD environment and send you the results.)
For any items that appear on the output report, review the relationship to the employee and categorize as one of the accepted Benefit Eligible Relationships.
| Eligible Child Relationships | Eligible Adult Relationships |
|---|---|
| Adopted Daughter | Spouse |
| Adopted Son | Domestic Partner Male (A) |
| Child (natural born) | Domestic Partner Female (B) |
| Stepchild | |
| Domestic Partner Child | |
| Recognized Child |
SQL: Relationship Corrections
SELECT DISTINCT F.BUSINESS_UNIT, E.NAME, A.EMPLID, D.NAME AS "Dep Name", C.RELATIONSHIP, TO_CHAR(A.EFFDT,'YYYY-MM-DD') AS "EFFDT", B.DEPENDENT_BENEF AS "Dep ID", A.PLAN_TYPE FROM PS_HEALTH_BENEFIT A, PS_HEALTH_DEPENDNT B, PS_DEP_BEN_EFF C, PS_DEP_BEN_NAME D, PS_PERSONAL_DATA E, PS_JOB F
WHERE A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_HEALTH_BENEFIT A_ED WHERE A.EMPLID = A_ED.EMPLID
AND A.EMPL_RCD = A_ED.EMPL_RCD
AND A.COBRA_EVENT_ID = A_ED.COBRA_EVENT_ID AND A.PLAN_TYPE = A_ED.PLAN_TYPE
AND A.BENEFIT_NBR = A_ED.BENEFIT_NBR AND A_ED.EFFDT <= SYSDATE)
AND A.PLAN_TYPE IN ('10','11','14') AND A.COVERAGE_ELECT = 'E'
AND A.COVRG_CD <> '1' AND A.EMPLID = B.EMPLID
AND A.EMPL_RCD = B.EMPL_RCD
AND A.COBRA_EVENT_ID = B.COBRA_EVENT_ID AND A.PLAN_TYPE = B.PLAN_TYPE
AND B.EFFDT = A.EFFDT AND B.EMPLID = C.EMPLID
AND B.DEPENDENT_BENEF = C.DEPENDENT_BENEF AND C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PS_DEP_BEN_EFF C_ED WHERE C.EMPLID = C_ED.EMPLID
AND C.DEPENDENT_BENEF = C_ED.DEPENDENT_BENEF AND C_ED.EFFDT <= SYSDATE)
AND C.RELATIONSHIP IN ('ES','FC','GC','GU','O','OC','P','R','RO','SB','SN','DP') AND B.EMPLID = D.EMPLID
AND B.DEPENDENT_BENEF = D.DEPENDENT_BENEF AND D.EFFDT =
(SELECT MAX(D_ED.EFFDT) FROM PS_DEP_BEN_NAME D_ED WHERE D.EMPLID = D_ED.EMPLID
AND D.DEPENDENT_BENEF = D_ED.DEPENDENT_BENEF AND D_ED.EFFDT <= SYSDATE)
AND A.EMPLID = E.EMPLID AND E.EMPLID = F.EMPLID AND F.EFFDT =
(SELECT MAX(F_ED.EFFDT) FROM PS_JOB F_ED WHERE F.EMPLID = F_ED.EMPLID
AND F.EMPL_RCD = F_ED.EMPL_RCD AND F_ED.EFFDT <= SYSDATE)
AND F.EFFSEQ =
(SELECT MAX(F_ES.EFFSEQ) FROM PS_JOB F_ES WHERE F.EMPLID = F_ES.EMPLID
AND F.EMPL_RCD = F_ES.EMPL_RCD AND F.EFFDT = F_ES.EFFDT)
ORDER BY 3, 8 ;
End of Article