Date problems while loading benefits in Workday

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')
UNION
SELECT emplid, empl_rcd, effdt
FROM ps_travel_plan WHERE benefit_plan IN ( '001', '002' )
UNION
SELECT emplid, empl_rcd, hire_dt FROM ps_employment
UNION
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', 
'NL_ADDITIONAL_BENEFITS_PLAN-1-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!

Over auteur Stefan van Glabbeek

Oracle Fusion Apps HCM functional consultant at Qualogy. Stefan is also a senior functional and technical PeopleSoft HCM consultant with 14 years of experience. Overall, he has over 18 years of experience in IT.

Meer posts van Stefan van Glabbeek