Database Location Question

#1 - The review information is in the REVIEW table but if you want the description for the review rating you will need to link it to the PCODES table.

Something like this would work if your using SQL

SELECT r.employee,r.sched_date,r.code,r.actual_date,r.rating
,pc.description as rev_rating,r.date_stamp,r.time_stamp
,r.user_id,r.next_rev_code,r.next_review
FROM review r
LEFT OUTER JOIN pcodes pc ON (r.rating = pc.code)

#2 - Percent Change

I could be wrong (it has happened a lot of times) - but in my experience the percent change is not in any table - it is a derived field and is displayed on the PA67.1 and ESS.

You can pull the pay rate history a few different ways, using HRHISTORY or PRRATEHIST table. Here is something that could work for you if you are using SQL

SELECT rh.employee
,rh.beg_date
,rh.seq_nbr
,rh.pay_rate
,rh.annual_salary
,rh.pos_level
,rh.nbr_fte
,LAG(rh.pay_rate,1) OVER (partition by rh.employee
ORDER BY rh.beg_date
,rh.seq_nbr) as last_rate
,(CASE WHEN nvl(rh.pay_rate,0) = 0 THEN 0
ELSE (CASE WHEN nvl((rh.pay_rate - (nvl(LAG(rh.pay_rate,1) OVER (partition by rh.employee
ORDER BY rh.beg_date
,seq_nbr),0))),0) <> 0 THEN ROUND((rh.pay_rate - (LAG(rh.pay_rate,1)
OVER (partition by rh.employee
ORDER BY rh.beg_date
,seq_nbr)))/rh.pay_rate,4)*100
ELSE 0 END) END) as inc

FROM prratehist rh
ORDER BY rh.employee
,rh.beg_date desc

Dave,

Thanks for the guidance! I will take a gander at these tables and sample queries later this afternoon. I really appreciate the assistance.

Hi Dave.

I am looking at the second query you provided. Is all of this calculation actually necessary? Unfortunately, we are only using SQL Server 2008 and it is my understanding that neither the LEAD or LAG functions are available prior to SQL Server 2012.

I was hoping the percentage increase shown on the ESS screen is actually stored somewhere, as is, and that it wouldn't have to be calculated on the fly every time you wish to query it. My concerns with doing so are the issues with rounding, how many decimal places out you go when calculating it, etc. I'd hoped to pull the value directly so there is never any question that it would exactly match the value displayed on the screen in ESS and that which was given to the employee on paper at the time of the review.

Please let me know your thoughts. Thanks again.

I could be wrong - but over the years I have never found that Lawson stores the percent or amount of the actual increase. Those screens in EMSS and PA67 have calculated fields that are calculated on view.

The only place the percent and amount are potentially displayed is in the PERSACTION table and that is only while the personell action is pending. After it is processed it goes into HRHISTORY and/or Pay rate history and/or pers action history.

Using the lag allows you to pull the previous value so it would be important.

The other option would be to use Add-Ins and query the PA67 form directly - that might get you what you want as well.

If it helps, I'll offer it.  None of the links should work, but for the visually-minded among us.

I've got a complete diagram of our database.. if you'd like another table or two, please ask..

but this will get you started.

Thanks Greg. I too am very visually-minded. I've looked at this Schema Spy before, but never been able to get the green light to go ahead and use it to map out the Lawson database. Do you mind if I ask about how long it took you to do this? Thanks.

TBonney (and all):

What I like about the whole thing:  The tools are all licensed under GNU GPL -- https://www.gnu.org/licenses/licenses.en.html

Actually getting it installed and connected to the databases was the difficult part. And it really wasn't all that difficult.

Once you have it installed and connected, the program generates ALL of the files for you.. you just let it run.
I think it took about 4 to 6 hours of system time for the Lawson database, but smaller databases finish pretty quickly.

I'd recommend a couple things if you venture out on this process:
1) Install SchemaSpyGUI -- A big time-saver.. and once you have your connections set up you can save them for any time the
database changes..... and with version 10... we're finding it changes quit a bit.
2) Do not use RDP if you can help it. The timeout of it.. even with disconnected sessions seems to kill the Java that is
SchemaSpy.
3) If you have a suspected long-running database diagram... use SchemaSpyGUI to generate the command for you...
Get it from the "Output" screen.. kill the job.. set up a batch file to set your path, and environment variables... and use WTS
to get the job done.

Great info/insight. Thanks for sharing Greg!