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
Crystal Report - Help Adding Fields to Existing Query
Home
Forums
Jobs
LawsonGuru
LawsonGuru Letter
LawsonGuru Blog
Worthwhile Reading
Infor Lawson News Feed
Store
Store FAQs
About
Who's On?
Membership:
Latest:
Chris Radcliffe
Past 24 Hours:
0
Prev. 24 Hours:
0
Overall:
5187
People Online:
Visitors:
327
Members:
0
Total:
327
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
Crystal Report - Help Adding Fields to Existing Query
Sort:
Oldest First
Most Recent First
You are not authorized to post a reply.
Author
Messages
Chris-T
New Member
Posts: 1
11/28/2016 7:26 PM
Good afternoon,
Let me start by stating that while I'm a DB person at my local hospital, I have never worked with Lawson data before. I've been requested to create an employee history report and thanks to the talented folks here, I have very nearly what I need.
Would someone be able to assist me in adding two fields to this query - I'm looking to add the employee's prior supervisor and current supervisor (much like the current position and prior position).
I believe the data I'm looking for is in the PAEMPPOS table, but I'm not entirely certain how to add it to this existing query.
SELECT *
FROM (
SELECT PAH.COMPANY
,EMP.PROCESS_LEVEL
,PAH.EMPLOYEE
,PAH.EFFECT_DATE
,PAH.ACTION_CODE
,PAH.REASON_01
,PAH.REASON_02
,EMP.LAST_NAME
,EMP.FIRST_NAME
,EMP.MIDDLE_INIT
,PEM.SEX
,PEM.EEO_CLASS
,EMP.POSITION
,COALESCE((
SELECT A_VALUE
FROM (
SELECT COMPANY
,EMPLOYEE
,BEG_DATE
,A_VALUE
FROM PRODLAW.HRHISTORY HRH
WHERE HRH.FLD_NBR = 126
ORDER BY BEG_DATE DESC
) CUR_POS
WHERE CUR_POS.COMPANY = PAH.COMPANY
AND CUR_POS.EMPLOYEE = PAH.EMPLOYEE
AND CUR_POS.BEG_DATE <= PAH.EFFECT_DATE
AND rownum = 1
), ' ') AS CUR_POSITION
,COALESCE((
SELECT A_VALUE
FROM (
SELECT COMPANY
,EMPLOYEE
,BEG_DATE
,A_VALUE
FROM PRODLAW.HRHISTORY HRH
WHERE HRH.FLD_NBR = 126
ORDER BY BEG_DATE DESC
) PREV_POSITION
WHERE PREV_POSITION.COMPANY = PAH.COMPANY
AND PREV_POSITION.EMPLOYEE = PAH.EMPLOYEE
AND PREV_POSITION.BEG_DATE < PAH.EFFECT_DATE
AND rownum = 1
), ' ') AS PREV_POSITION
,COALESCE((
SELECT A_VALUE
FROM (
SELECT COMPANY
,EMPLOYEE
,BEG_DATE
,A_VALUE
FROM PRODLAW.HRHISTORY HRH
WHERE HRH.FLD_NBR = 20
ORDER BY BEG_DATE DESC
) CUR_STATUS
WHERE CUR_STATUS.COMPANY = PAH.COMPANY
AND CUR_STATUS.EMPLOYEE = PAH.EMPLOYEE
AND CUR_STATUS.BEG_DATE <= PAH.EFFECT_DATE
AND rownum = 1
), ' ') AS CUR_STATUS
,COALESCE((
SELECT A_VALUE
FROM (
SELECT COMPANY
,EMPLOYEE
,BEG_DATE
,A_VALUE
FROM PRODLAW.HRHISTORY HRH
WHERE HRH.FLD_NBR = 20
ORDER BY BEG_DATE DESC
) PREV_STATUS
WHERE PREV_STATUS.COMPANY = PAH.COMPANY
AND PREV_STATUS.EMPLOYEE = PAH.EMPLOYEE
AND PREV_STATUS.BEG_DATE < PAH.EFFECT_DATE
AND rownum = 1
), ' ') AS PREV_STATUS
FROM PRODLAW.PERSACTHST PAH
LEFT OUTER JOIN PRODLAW.EMPLOYEE EMP ON (EMP.COMPANY = PAH.COMPANY)
AND (EMP.EMPLOYEE = PAH.EMPLOYEE)
LEFT OUTER JOIN PRODLAW.PAEMPLOYEE PEM ON (PEM.COMPANY = PAH.COMPANY)
AND (PEM.EMPLOYEE = PAH.EMPLOYEE)
WHERE (PAH.COMPANY = 1000)
AND (PAH.EFFECT_DATE BETWEEN to_date('2000-01-01', 'yyyy-mm-dd')
AND to_date('2016-12-31', 'yyyy-mm-dd')
)
)
WHERE CUR_POSITION <> PREV_POSITION
OR CUR_STATUS <> PREV_STATUS;
Thanks in advance for helping out a poor soul :)
Jeff Shumate
Advanced Member
Posts: 31
11/28/2016 11:42 PM
Our setup has Supervisor easily found in the EMPLOYEE table, but you could grab from PAEMPPOS if you have it set up, and your EEs can have multiple positions. I believe to get the prior supervisor, you will have to look at HRHISTORY, and HR10 will have to be set up to capture changes to the supervisor field.
You are not authorized to post a reply.