PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 09/24/2015 1:25 PM by  Lance Jurgensen
Employee work history report
 9 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Lance Jurgensen
Lawson System Administrator
Private
Basic Member
(39 points)
Basic Member
Posts:17


Send Message:

--
09/09/2015 12:50 PM
    Hello,
    New to Lawson and learning the data on the fly. HRHistory is a bit of a conundrum for me yet.

    I am trying to write SQL for a crystal report that will report a chronological history of an employees work history from Lawson. Similar to the PA340 Action History Listing but with more useful information :-)

    Basics such as Name, Employee#, position, position description, FTE, supervisor, job code, status, date effective for all positions an employee has held.

    Would be grateful for any help or pointers...

    The.Sam.Groves
    Programmer Analyst
    St Lukes Hospital
    Veteran Member
    (265 points)
    Veteran Member
    Posts:89


    Send Message:

    --
    09/09/2015 1:30 PM
    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.
    John Henley
    Private
    Private
    Senior Member
    (9563 points)
    Senior Member
    Posts:3205


    Send Message:

    --
    09/09/2015 2:04 PM
    I have this, as well as some alternative approaches...
    https://www.lawsonguru.com/Store.as...ory=854096
    Thanks for using the LawsonGuru.com forums!
    John
    The.Sam.Groves
    Programmer Analyst
    St Lukes Hospital
    Veteran Member
    (265 points)
    Veteran Member
    Posts:89


    Send Message:

    --
    09/09/2015 2:33 PM
    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.
    Attachments
    Lance Jurgensen
    Lawson System Administrator
    Private
    Basic Member
    (39 points)
    Basic Member
    Posts:17


    Send Message:

    --
    09/09/2015 2:41 PM
    Thanks, I was able to see it. I will take a look, I didn't know there was a "Store" !
    Lance Jurgensen
    Lawson System Administrator
    Private
    Basic Member
    (39 points)
    Basic Member
    Posts:17


    Send Message:

    --
    09/09/2015 2:42 PM
    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...
    John Henley
    Private
    Private
    Senior Member
    (9563 points)
    Senior Member
    Posts:3205


    Send Message:

    --
    09/09/2015 2:43 PM
    Lance, what database are you using?
    Thanks for using the LawsonGuru.com forums!
    John
    Lance Jurgensen
    Lawson System Administrator
    Private
    Basic Member
    (39 points)
    Basic Member
    Posts:17


    Send Message:

    --
    09/10/2015 2:29 PM
    Oracle DB
    John Henley
    Private
    Private
    Senior Member
    (9563 points)
    Senior Member
    Posts:3205


    Send Message:

    --
    09/14/2015 7:07 AM
    Here are some samples
    Thanks for using the LawsonGuru.com forums!
    John
    Attachments
    Lance Jurgensen
    Lawson System Administrator
    Private
    Basic Member
    (39 points)
    Basic Member
    Posts:17


    Send Message:

    --
    09/24/2015 1:25 PM
    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
    Attachments
    You are not authorized to post a reply.