Login
Register
Search
Home
Forums
Jobs
LawsonGuru
LawsonGuru Letter
LawsonGuru Blog
Worthwhile Reading
Infor Lawson News Feed
Store
Store FAQs
About
Forums
Performance Management
Lawson Business Intelligence/Reporting/Crystal
Employee work history report
Home
Forums
Jobs
LawsonGuru
LawsonGuru Letter
LawsonGuru Blog
Worthwhile Reading
Infor Lawson News Feed
Store
Store FAQs
About
Who's On?
Membership:
Latest:
Wayne Willis
Past 24 Hours:
1
Prev. 24 Hours:
1
Overall:
5190
People Online:
Visitors:
201
Members:
0
Total:
201
Online Now:
New Topics
S3 Customization/Development
Cobol - Extract Current Time
4/24/2024 7:21 PM
How do you extract just the Current System Time in
Lawson Landmark
LPL INSTR Functions
4/5/2024 8:32 PM
I'm writing a simple report using the Create R
Infor SCM
Translating 856 to get the ~ REF^CN^ field
4/3/2024 8:24 PM
We are trying to get the tracking number which is
IPA/ProcessFlow
Sample XML file create Flow
4/3/2024 3:43 PM
Hello everyone, I am new to creating XML files
Lawson S3 HR/Payroll/Benefits
bn105 error message
3/26/2024 6:40 PM
I need to change some of the set ups in our Life I
IPA/ProcessFlow
IPA executing Job
3/13/2024 7:08 PM
New to the IPA world and was wondering, can an IPA
Lawson S3 HR/Payroll/Benefits
Life Age Reduction on benefits plans
3/12/2024 7:15 PM
For our optional life we have an age based coverag
Lawson S3 HR/Payroll/Benefits
BN53.1 Add-In
3/7/2024 3:31 PM
We are migrating to Solstice. They require a
Lawson Business Intelligence/Reporting/Crystal
Domain Name Change
3/5/2024 7:45 PM
Our domain name needs to change and was hoping I c
S3 Customization/Development
Cobol calling Shell Script
2/29/2024 1:27 PM
Has anyone created or modified a Lawson Cobol prog
Top Forum Posters
Name
Points
Greg Moeller
4184
David Williams
3349
JonA
3288
Kat V
2984
Woozy
1973
Jimmy Chiu
1883
Kwane McNeal
1437
Ragu Raghavan
1351
Roger French
1311
mark.cook
1244
Forums
Unanswered
Active Topics
Most Liked
Most Replies
Search Forums
Search
Advanced Search
Topics
Posts
Prev
Next
Forums
Lawson Business Intelligence/Reporting/Crystal
Employee work history report
Sort:
Oldest First
Most Recent First
You are not authorized to post a reply.
Author
Messages
Lance Jurgensen
Advanced Member
Posts: 36
9/9/2015 4: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
Veteran Member
Posts: 89
9/9/2015 5: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
Senior Member
Posts: 3348
9/9/2015 6:04 PM
I have this, as well as some alternative approaches...
https://www.lawsonguru.co...2947/category=854096
The.Sam.Groves
Veteran Member
Posts: 89
9/9/2015 6: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
2015-09-09 14_31_37-LawsonGuru.com _ Store.png
Lance Jurgensen
Advanced Member
Posts: 36
9/9/2015 6:41 PM
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
9/9/2015 6: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
Senior Member
Posts: 3348
9/9/2015 6:43 PM
Lance, what database are you using?
Lance Jurgensen
Advanced Member
Posts: 36
9/10/2015 6:29 PM
Oracle DB
John Henley
Senior Member
Posts: 3348
9/14/2015 11:07 AM
Here are some samples
Attachments
persacthist1.png
persacthist2.png
Lance Jurgensen
Advanced Member
Posts: 36
9/24/2015 5: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
lawson_guru_work_history.txt
lawson_guru_work_history.png
maalimsimo
Veteran Member
Posts: 49
5/17/2019 6:23 PM
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
5/17/2019 6:33 PM
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.