Employee work history report

That's sort of been my holy grail that I've been searching for time for to develop on my own.

So I can't share a complete solution for you but I can give you a couple of tips on how the data is stored.

HRHISTORY stores a row for each field changed as long as that field is marked as 'history enabled'.

FLD_NBR corresponds to number of the field that was changed.

Lawson hard coded fields are stored in PDICT if you need to look up the name of the field.

User defined fields are represented by adding 2000 to the actual field_key value (e.g. if you have a user field that was assigned a key of 99 then on HRHISTORY it'll show as 2099), and the definitions for them are stored in HRUSERFLDS.

Both PDICT and HRUSERFLDS contain a FIELD_TYPE column which will define for you which *_VALUE field in HRHISTORY actually contains the new value of the field being changed.

If the change was caused by a personnel action (i.e. PA52.1 or any of its compatriots) then ACT_OBJ_ID will store the OBJ_ID value of the row that stores that action's information in PERSACTHST.

The old value of the field is NOT stored anywhere on the row. To determine that you have to essentially look for a row with the highest BEG_DATE/DATESTAMP combo for the same employee and field and use the value from that row. Checking the date stamp is essential as otherwise you'll get retroactive changes listed out of order on your report.

If such a row doesn't exist, then either your current row is the first time the field was set to a value or the first time it's been changed since history for the field was enabled.

Needless to say, a replacement for PA340 or HR105 would be difficult to create in a pure SQL environment, especially given the amount of data you'll need to sift through if your organization has been using Lawson for any length of time as this is one of the tables whose size grows geometricly with the amount of activity you have. Almost any personnel action you'd submit will result in multiple rows being added to the table.

I have this, as well as some alternative approaches...
https://www.lawsonguru.co...2947/category=854096

John, might be on my side, but your link doesn't seem to work for me. I get a 'blank' LawsonGuru page when following it. Picture attached.

Thanks, I was able to see it. I will take a look, I didn't know there was a "Store" !

Thank you, very informative. I'm plugging away at it, it will be for a single employee so there won't be "that" much data...

Lance, what database are you using?

Oracle DB

Here are some samples

I have attached my sample along with the SQL I used to get there. With testing it seems to be accurate, but I am finding there are not always history records with the field number I am looking for. I have used the paemppos and persacthst tables instead of the hrhistory table for the most part. Query needs some tuning as it runs fairly slow at 20-30 seconds but it gets me what I want for the most part.

I could use help getting:
employee status at the time of the position change, currently using fld_nbr 728 in hrhistory
total fte at the time of the position change, currently using fld_nbr 20 in hrhistory

I have found this page in my search for a solution for a 'before' and 'after' situation for some action codes e.g. Address or Status in sql. I can get the 'after' value alright, but I am having a challenge reporting on the 'before' value on the same report record/line.

Any help would be highly appreciated.
- Maalim

The answer would really depend on where you want to pull the history from and exactly what you are looking for, but, here is a quick SQL query that pulls from the position history (PAEMPPOS) table and gives you the record value and the previous value for some of the fields.

The following assumed you use Oracle as your database. Not sure it will work as is if you have MS SQL database.

SELECT employee
,position
,LAG(position,1) OVER(PARTITION BY employee ORDER BY company,employee,pos_level,position,effect_date) as prev_position
,pos_level
,fte
,LAG(fte,1) OVER(PARTITION BY employee ORDER BY company,employee,pos_level,position,effect_date) as prev_fte
,effect_date
,end_date
,job_code
,LAG(job_code,1) OVER(PARTITION BY employee ORDER BY company,employee,pos_level,position,effect_date) as prev_job_code
,pay_grade
,LAG(pay_grade,1) OVER(PARTITION BY employee ORDER BY company,employee,pos_level,position,effect_date) as prev_pay_grade
,date_stamp
FROM paemppos
ORDER BY company,employee,pos_level,position,effect_date