Background
To support ACA data entry, campuses may require a report indicating when an employee has moved between Medical enrollment and Medical Flex Cash enrollment.
Issue
Currently, there is no delivered or baseline mechanism to readily gather this information from benefit enrollments.
Analysis
Identify any employee that changed coverage between Medical plan type 10 and Medical Flex Cash plan type 1Z during the 2016 Benefit Year.
Recommendations
Create the following report using Query.
SELECT
A.EMPLID, D.EMPL_RCD, B.NAME, D4X.XLATSHORTNAME, D.JOBCODE, D.UNION_CD,
A.COVERAGE_ELECT, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.BENEFIT_PLAN,
TO_CHAR(C.EFFDT,'YYYY-MM-DD'), C.COVERAGE_ELECT, C.BENEFIT_PLAN
FROM
PS_HEALTH_BENEFIT A, PS_PERSONAL_DATA B, PS_HEALTH_BENEFIT C, PS_JOB D
LEFT OUTER JOIN ( SELECT * FROM PSXLATITEM TA WHERE TA.FIELDNAME='EMPL_STATUS' AND TA.EFF_STATUS = 'A' AND TA.EFFDT = ( SELECT MAX(EFFDT) FROM PSXLATITEM TB WHERE TB.FIELDNAME='EMPL_STATUS' AND TA.FIELDVALUE=TB.FIELDVALUE AND TB.EFF_STATUS = 'A' AND TB.EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD'))) D4X ON
D4X.FIELDVALUE = D.EMPL_STATUS AND
D4X.FIELDNAME='EMPL_STATUS' , PS_PRIMARY_JOBS E
WHERE
( A.EFFDT BETWEEN TO_DATE('2015-12-31','YYYY-MM-DD') AND TO_DATE('2017-01-01','YYYY-MM-DD') AND A.EMPLID = B.EMPLID AND A.EMPLID = C.EMPLID AND A.EMPL_RCD = C.EMPL_RCD AND A.COBRA_EVENT_ID = C.COBRA_EVENT_ID AND A.BENEFIT_NBR = C.BENEFIT_NBR AND C.EFFDT =
(SELECT MAX(C_ED.EFFDT)
FROM PS_HEALTH_BENEFIT C_ED
WHERE
C.EMPLID = C_ED.EMPLID AND
C.EMPL_RCD = C_ED.EMPL_RCD AND
C.COBRA_EVENT_ID = C_ED.COBRA_EVENT_ID AND
C.PLAN_TYPE = C_ED.PLAN_TYPE AND
C.BENEFIT_NBR = C_ED.BENEFIT_NBR AND
C_ED.EFFDT < A.EFFDT) AND CASE WHEN A.PLAN_TYPE = '10' AND C.PLAN_TYPE =
'1Z' THEN 'Change' WHEN A.PLAN_TYPE = '1Z' and C.PLAN_TYPE = '10' THEN 'CHANGE' ELSE '' END = 'CHANGE' AND A.COVERAGE_ELECT = 'E' AND C.COVERAGE_ELECT = 'E' AND A.EMPLID = D.EMPLID AND D.EFFDT =
(SELECT MAX(D_ED.EFFDT)
FROM PS_JOB D_ED
WHERE
D.EMPLID = D_ED.EMPLID AND
D.EMPL_RCD = D_ED.EMPL_RCD AND
D_ED.EFFDT <= SYSDATE) AND D.EFFSEQ =
(SELECT MAX(D_ES.EFFSEQ)
FROM PS_JOB D_ES
WHERE
D.EMPLID = D_ES.EMPLID AND
D.EMPL_RCD = D_ES.EMPL_RCD AND
D.EFFDT = D_ES.EFFDT) AND D.EMPLID = E.EMPLID AND D.EMPL_RCD = E.EMPL_RCD AND E.EFFDT =
(SELECT MAX(E_ED.EFFDT)
FROM PS_PRIMARY_JOBS E_ED
WHERE
E.EMPLID = E_ED.EMPLID AND
E.PRIMARY_JOB_APP = E_ED.PRIMARY_JOB_APP AND
E.EMPL_RCD = E_ED.EMPL_RCD AND
E_ED.EFFDT <= SYSDATE) AND E.PRIMARY_JOB_IND = 'Y' )
Considerations
Access to Query Manager would be required to build the report or the SQL included could be executed by a campus technical resource.
Cross-Functional Impacts (Positive / Negative)
None.
CHANGE IMPACT
To be filled out by BSA (from Change Impact Tracking log):
| Module | Map ID | Change Impact Log ID | Map Name | Impact Type | % of Employees Impact |
|---|---|---|---|---|---|
| BA | 47 | 187 | Workforce Admin - Initiate or Change Enrollment | People, Process, Technology | <10% |
The following impact areas should be addressed by the Position Paper author(s) or a relevant stakeholder/approver. All items should have a response.
Areas of potential change resistance to proposed HR process / policy changes?
None foreseen, perhaps skill limitations.
Potential resource needs in order to plan, engage, prepare, and/or deploy the change?
Someone knowledgeable in Peoplesoft query, or a technical resource.
Associated costs relative to the scope of the change to requirements requested?
Time commitment of approximately 30 minutes on a monthly basis.
Training needs or if a straightforward change?
Would need to have query knowledge. If a user is not available, a technical person would need to do it for them.
Implication on any other related process / functions?
No outside implications. It would require regular time commitment; need a campus resource to build the query or a tech resource to put it on; need to run it at least monthly to find data that needs ACA entries.
REVISION CONTROL
Revision History
| Revision Date | Revised by | Summary of Revisions | Section(s) Revised |
|---|---|---|---|
| 2/22/17 | Allison Inglett | Initial draft | New |
| 3/23/17 | Beverly Mausbach | Update to CHRS format | All |
| 6/22/17 | Beverly Mausbach | Inserted corrected screen shots | Recommendations |
Review/Approval History
| Review Date | Reviewed By | Action (Reviewed, Recommended, Approved, Denied or Cancelled) | Comments |
|---|---|---|---|
| 3/30/17 | Change Management | Reviewed | Provided input |
| 3/30/17 | CMS | Recommended | |
| NA | Finance | ||
| 4/25/17 | SWHR | Approved |
End of Article