Employee work history report

Sort:
You are not authorized to post a reply.
Author
Messages
Lance Jurgensen
Advanced Member
Posts: 36
Advanced Member
    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
    Veteran Member
    Posts: 89
    Veteran Member
      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
      Senior Member
      Posts: 3348
      Senior Member
        I have this, as well as some alternative approaches...
        https://www.lawsonguru.co...2947/category=854096
        Thanks for using the LawsonGuru.com forums!
        John
        The.Sam.Groves
        Veteran Member
        Posts: 89
        Veteran Member
          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
          Advanced Member
          Posts: 36
          Advanced Member
            Thanks, I was able to see it. I will take a look, I didn't know there was a "Store" !
            Lance Jurgensen
            Advanced Member
            Posts: 36
            Advanced Member
              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
              Senior Member
              Posts: 3348
              Senior Member
                Lance, what database are you using?
                Thanks for using the LawsonGuru.com forums!
                John
                Lance Jurgensen
                Advanced Member
                Posts: 36
                Advanced Member
                  Oracle DB
                  John Henley
                  Senior Member
                  Posts: 3348
                  Senior Member
                    Here are some samples
                    Attachments
                    Thanks for using the LawsonGuru.com forums!
                    John
                    Lance Jurgensen
                    Advanced Member
                    Posts: 36
                    Advanced Member
                      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
                      maalimsimo
                      Veteran Member
                      Posts: 49
                      Veteran Member
                        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
                        Dave Curtis
                        Veteran Member
                        Posts: 136
                        Veteran Member
                          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
                          You are not authorized to post a reply.