Any issue using PAEMPPOS table to evaluate FTE?

Do you use multiple positions in Lawson?

If you do; Consider the position level into your criteria. If you have multiple positions - do changes to non-primary positions need to be picked up in your report? If not, make sure you are looking at POS_LEVEL = 1 only.

Your programer may not be using SQL, they may be using 4GL or something else to pull the eligibility file, so it may make some difference, but if you can create the SQL to pull it, your programmer should also be able to pull it in whatever manner they are using.

I am not an SQL wiz, or a programmer, but I have done something similar to what you are looking to do. If I were to create a report like you are trying to create, I would probably do it using an SQL command in the Crystal report - something like this would do what you want;

-- The first section will find the changes in FTE based on the PAEMPPOS table
-- The second section will use a union to pull in the people who have terminated
SELECT company
,employee
,effect_date
,date_stamp
,TO_CHAR(fte,'9.999999') as new_value
-- need to change the field type to string for the union to work
,TO_CHAR(previous_fte,'9.999999') as prev_value
-- need to change the field type to string for the union to work
,'FTE Change' as type_of_change
FROM (SELECT company
,employee
,position
,pos_level
,fte
,effect_date
,end_date
,job_code
,process_level
,department
,pay_rate
,date_stamp
,LAG(fte,1) OVER (PARTITION BY employee
ORDER BY employee
,effect_date
,date_stamp
,time_stamp) as previous_fte
FROM (SELECT * FROM lawson.paemppos WHERE pos_level = 1))
WHERE date_stamp between SYSDATE -6 and SYSDATE
-- You can replace SYSDATE with Crystal date parameter fields if you want to control the date parameter
AND (previous_fte >=.40 and fte <.40)
AND end_date = to_date('01/01/1700','mm/dd/yyyy')
-- The second section finds the terms for employees with FTE that would have made them eligible
-- This pulls terms based on the HRHISTORY table, looking for term status codes
UNION ALL
SELECT ch.company
,ch.employee
,ch.effect_date
,ch.date_stamp
,ch.new_value
,ch.prev_value
,ch.type_of_change
FROM (SELECT company
,employee
,beg_date as effect_date
,date_stamp
,TRIM(a_value) as new_value
,TRIM(LAG(a_value,1) OVER (PARTITION BY employee
ORDER BY employee
,beg_date
,date_stamp
,seq_nbr)) as prev_value
,'Term' as type_of_change
FROM lawson.hrhistory
WHERE fld_nbr = 20
-- fld_nbr 20 is the field for emp_status
ORDER BY employee
,beg_date desc
,date_stamp desc
,seq_nbr desc) ch
,(SELECT company, employee FROM lawson.employee
WHERE nbr_fte >=.40) e
WHERE date_stamp Between SYSDATE - 6 and SYSDATE
-- You can replace SYSDATE with Crystal date parameter fields if you want to control the date parameter
AND (new_value IN ('TV','TI','TD','RE') and prev_value NOT IN ('TV','TI','TD','RE'))
-- To capture your terms; Replace the above status codes with your own term status codes
AND (ch.company = e.company and ch.employee = e.employee)

Sarah,

Like Dave, I had to do something similair only looking at fte. The only reason I did not use paemppos fte is that our benefit plans are based on total fte. Example is that posistion 1 is .4 and postion 2 is .6 the toal fte makes the employee eligible. If I looked only at the first postion the employee would be missed on the interface.


The code below only looks for fte changes between HRHISTORY and the Employee table.

with
FTE_changes As
(select b.employee,
b.fld_nbr,
--b.n_value,
b.beg_date,
B.DATE_STAMP,
lead(B.n_value,1) Over (Partition By b.Employee Order By B.date_stamp desc)previosfte
from
lawson9.hrhistory b
where b.company =1
--and b.employee = 56981
--fld _nbr 728 - fte_total in the history file
and b.fld_nbr =728)

select a.employee,a.last_name,a.first_name, a.fte_total,fte_changes.previosfte,fte_changes.date_stamp
--CASE WHEN fte_changes.previosfte <.4 AND A.FTE_TOTAL >=.40
from lawson9.employee a,
FTE_changes
where fte_changes.employee = a.employee
and a.company =1
and fte_changes.date_stamp >= '01-sep-2012'
--AND A.EMPLOYEE IN (203133,62997)
AND fte_changes.previosfte <.4 AND A.FTE_TOTAL >=.40


regards

Paul

I wanted to just post a great big THANK YOU!!! Our programmers kept telling me this was "impossible" even though I could create it out of crystal reports. This week, with the support of my awesome boss, we sat down with our IS department and with the aid of your criteria (I copied and pasted to IS) they were able to alter how our file is generated so it's now more accurate.

Thank you, Thank you, Thank you!

Sarah

Your welcome, any time you have a question let me know.


Paul