CC- Amruta Lohiya

Hi Neelam Yangal,

We have found one more Duplicate election in 'City of Durham' on production. Please refer below details for more information.

Employee:- GRANT CHRISTY
Plan Name:- C. Retiree Blue Local Plan

Thanks,
Avnish Y

CC: Amruta Lohiya, Vijay Siddha, Samir

Total Duplicate record found in the system:648

out of

Schedule import :261
Import enrollment: 77

Jaideep Vinchurkar: Can you please look into this affected record and do more analysis for finding root cause of this issue?

General: 97
eligibility service: 17
new hire : 14
OE: 48
Employee data change : 100
Review Change Approval:2
QE: 24
0:4
Error correction: 4

I am analyzing rest affected records

CC: Satya

Hi All,

We have observed different patterns of duplicate elections and found that major issue is with scheduled import. Following are updates:

1. Same benefit type contains 2 core plans and both are eligible: This issue got fixed due to fix of WT – 8467
2. HSA 2016 and 2017 plans are ongoing – Manual corrections are there. Previous year plan is not terminated.
3. Manual correction for single employee on different companies.
4. Scheduled import: Under one benefit – 2 plans are eligible. One if force and other is not force. We have tried multiple scenarios from screen but it is non reproducible. We observed that it is through scheduled import on multiple companies. So import team needs to check if import failed on particular day and we manually executed routines for correction, In this case there are chances of duplicate election.
Example: Blue Coat
select * from OES_VIEW_Enrollment
where Employeeid = 1061 and plandesignid in (29)

Thanks & Regards,
Vinanti R. Yadav

CC : Amruta Lohiya, Jaideep Vinchurkar, Suraj Sokasane

Hi Ankur Deshpande,

Please have a discussion on this with Vinanti Yadav today.

-----------------------
Jaideep Vinchurkar, Amruta Lohiya

Hi,

We have scanned all companies for duplicate election by Scheduled import, We found only two companies (Blue Coat and Stimson) for duplicate active enrollments , employee count as 113 for Blue coat and 1 for Stimson. after initial analysis and history logs, this is because of data correction. Old termed enrollment get active hence its showing duplicate enrollment. we are analyzing root cause , will update you accordingly.

Thanks !

Hi Amruta Lohiya

While executing back end routine,we have found duplicate active enrollment. details as below,

Environment : Production
Company : Suburban Electrical
Enroll by: Eligibility Service
Enrollment Mode : Plan Design Eligibility Change

Enrollment details and Database backup path shared in separate email.

we have corrected duplicated enrollment to avoid further failure in back end routine.

Please have a look and let me know for any input if you required.

Thanks !

Hi Jaideep Vinchurkar ,

We have tried to re-pro this issues via manual as well as scheduled import but its was not reproduced at our local environment as well as on production (HSPL). duplicate active enrollment was found on two companies by scheduled import only.

we need QA help to re-pro this issue so can you please plan accordingly.

Thanks !

on hold because of Low bandwidth

Avnish Yadav
While observing pattern, please try to reproduce this and fix this. If its from import then send examples to suraj

Suraj Sokasane
Need to reproduce and fix on import.

Below are the findings till now:

1. BlueCoat:
Total 226 records found.
Out of those, 224 are for Plan design ID 29 and have similar pattern. For one of the two duplicate entries Datetimestamp does not match from main table and history table. The date in History table is '08/17/2016'. So we went through JIRA records if there were any corrections. WT-4103 has Vishal's comment on same date for same company. When we revisited his scripts, datetimestamp, userid were updated and script did not contain duplicate enrollments.

This is caused due to correction and we were not able to find further details.

Remaining 2 records were for plandesign 10. The Eligibility routine was failed on '07/29/2016'. So new enrollment was inserted but old one was not terminated. The cause for failure was NULL values in "isApprovalPending" in Employee_Enrollment_WavedBenefitTypes. This issue still exists and getting reproduced.
We have created WT-10100 and assigned to Enrollment team to solve the issue.

2. WindStream:
the data contains multiple enrollments in same BenefitType. As per discussion with Amruta, we are keeping this on low priority for now.

3. UND:
One of the two duplicate elections has UserID 1. So considering this as correction. Also datetimestamp is mismatch in main and history table.

4. Karma:
Following is the pattern we observed on this company: All happened in Intermediate table.
1. An active enrollment was inserted.
2. Another active enrollment was inserted in same plan or a plan from same benefit type and first enrollment was terminated.
3. User Ivette Jimenez reinstated the old enrollment. The noticeable thing here is all employees have same pattern and elections were reinstated by same user.
4. There's no import on that day OR employee's data was not changed. The Enrollment mode is general.

As per discussion with Avnish, this was a issue that old plan is reinstated when data is changed with back date. But in 2 cases the scenario is happened for single employee. There's no conclusion yet.

5. Kemtah:

Those are very old enrollments. Inserted by employee login during OE. not able to find the cause. All duplicate enrollments are in two plans from same benefit type.

6. Merriville:

There are 14 Enrollments for a single employee. One is inserted by Employee and other are inserted by EligibilityService. Starting from 05/14/2016 to 05/26/2016, daily one entry was inserted into Employee enrollment with that effective date. This is issue with Eligibility service.

7.California Golf:
On 06/14/2017m all elections were activated with null termination date and then termed with termination date 06/30/2017 by Debbie Kulling. This made the 01/01/2107 to 06/30/2017 and overlapping period.

Hi Avnish Yadav,

I am done analyzing the import data. We found two bugs and those are in progress.
Assigning the ticket to you for further analysis.

We have completed RCF of Duplicate Election and also created below jira ticket for Enrollment and Import team.

Enrollment Team
1. WT-10512
2. WT-10513

Import Team
3.WT-10514

Please refer my comment in attached data audit report for Duplicate Election.

Thanks,
Avnish Y