HR Groups - date into group and date out of group

Karen -

No, that information is not stored.

Thanks for confirming that for me. So, has anyone found a way to create a report that displays a list of employees who met the benefit eligibility group criteria within the past 7 days? If I can get that, I can figure out how to do the other variations.

The other option I'm working on is to create a report from the history file that shows changes to specific fields like status code (from non-ben elig status to ben elig status), but that is going to be really complicated since Lawson doesn't store from/to in the history file.

How are others identifying employees who are newly eligible for benefits? For example, how does your benefits department know to whom they need to send enrollment packets, which could be a new hire or someone who was already an employee whose scheduled hours was increased so is now benefit eligible.

It seems like this should be an easy thing to provide, so I must be missing something obvious.

Karen

Karen.... the benefits eligibility date is not stored in Lawson anywhere. Every time you see that date, it is being recalculated using the current entry rules. When I code a program, that needs this, I use the Lawson routine that calculates the date. A simple process to incorporate into a 4GL program.

As far as when a field value changes, such as status code, that only place this is stored at is in the HRHISTORY file. To query that table for specific values, you must do know with the field number from the data dictionary.

Here's an idea... I don't think it would be that difficult to create a table to store your own history and report on it. Here's how I'd do it...

Create a simple table in your database with the following columns: COMPANY, EMPLOYEE, DATE, and GROUP_NAME. Then write a simple SQL script and schedule it to run once a day to select the employees in PGEMPLOYEE which belong to the groups you are interested in (GROUP_NAME) and store them in your new table. In DATE store the current date. You should be able to do this in just a few lines of SQL.

After the script starts running you'd have a snapshot of what the group contents were for any day you are interested in. You could report when you run into a situation where a person doesn't appear on the list one day but does appear on some other day (or vice versa to find folks who've dropped out of a group).

Since you are only interested in the last 7 days you could also have the daily script delete any records out of your history table where DATE is before 7 days ago.

Hopefully this might give you the info. you need.

Although some would cringe, this is also a legitimate case for database triggers to update a secondary tracking table

Also a good use case for Smart Notifications if you have it.