Date problems while loading benefits in WorkdayGepubliceerd: Auteur: Stefan van Glabbeek Categorie: Java & Web
During a data migration from PeopleSoft to Workday, we encountered problems with the dates when loading benefits. If you load a benefit with a certain event date and coverage begin date, and then load anóther completely unrelated benefit with a láter event date and coverage begin date, then the first benefit is closed off with an end date which was nót in the iLoad. Furthermore we discovered more unpredicted behavior concerning these dates. Let’s take a look.
To prepare the iLoads we are using Pentaho, running against a SQL Server database. We are loading a variety of different benefits. Let’s just name two benefits for this example. We want to upload both a retirement benefit as well as a lease car benefit. Now an employee is eligible for both. The retirement benefit for this person starts at his hire date. The lease car benefit starts at a later date: when he became a sales person.
Spontaneously an end date
So first we load for this employee the retirement benefit, with the event date and the coverage begin date being both the hire date. Then next we load the lease car benefit, let’s say starting at February the first of this year. So we make the event date and coverage begin date the first of February. What happens now, is that the retirement benefit has spontaneously gotten an end date of January 31 of this year. The day before the other benefit (lease car) starts. Also we tried to load it in reverse order, so the latest first and then back in time. But the result was the same.
Actually we are not loading 2 benefits, but we have 18 different benefits to load, for a population of 16.000 employees. So you can imagine that we have a big problem when every benefit is closed with an end date at the beginning of a random other benefit for that employee.
The relation between event date and coverage begin date
First something about these dates. At first we wanted the event date to be the date of loading, and the coverage begin date to be the start date of the actual benefit. But this was not possible, because the event date may never be before the coverage begin date. Both dates are mandatory.
But what we didn’t expect is that Workday in fact is using the event date as the moment of measurement to determine if the employee is eligible for the benefit. So you also don’t want the event date to be earlier than the date when the benefit actually started (otherwise you could have eligibility issues). So the conclusion was that the event date and the coverage begin date should always be the same, and should reflect the actual start date of the benefit.
Besides that, the event date may never be before the hire date. Because there are all sorts of eligibility rules that apply for each benefit. And so, before the hire date, you don’t have a CLA for instance, and no job level etc. So before the hire date you can never be eligible, which is measured by the event date.
This was for retirement benefits not a desired outcome for us, because there are rehires in the system for whom the actual start date of the retirement benefit should have been before the hire date. But we have not been able to load it this way into Workday.
The solution to benefits closing earlier other benefits
In order to solve the issue with the closing dates, I created a view in the SQL Server database. Containing of the start date for all benefits of all employees. Then, in the SQL part in Pentaho, we can join the SQL to select the benefits from the table. With this view in such a way that we select all of the start dates from the view for this employee, that are later than the actually start date of a certain benefit.
All these resulting dates from the view, we will use as both the event date and the coverage begin date. The result of this is that every benefit is copied many times: once again for every later benefit, to start again on this later date. So a benefit is closed when there is a later random other benefit for the same employee. But the closed benefit will also start again at this very same date and therefore the benefit is actually continued.
- CREATE VIEW benefit_vw AS
- SELECT emplid, empl_rcd, effdt
- FROM pentaho.PS_ADD_PAY_DTA_NLD
- WHERE erncd IN ('123','456','789','ABC','DEF','GHI','JKL','MNO', 'PQR')
- SELECT emplid, empl_rcd, effdt
- FROM ps_travel_plan WHERE benefit_plan IN ( '001', '002' )
- SELECT emplid, empl_rcd, hire_dt FROM ps_employment
- SELECT emplid, empl_rcd, effdt FROM PS_insur_benf;
And then the SQL in Pentaho could be something like this:
- SELECT t.emplid, b.effdt, 'NL LEASE – LEVEL B',
- FROM ps_travel_plan t
- , benefit_vw b
- WHERE benefit_plan = '001'
- AND t.emplid = b.emplid
- AND t.empl_rcd = b.empl_rcd
- AND b.effdt BETWEEN t.effdt AND getdate()
- ORDER BY t.emplid
And that’s how we solve this issue!