Database Location Question

Sort:
You are not authorized to post a reply.
Author
Messages
TBonney
Veteran Member
Posts: 277
Veteran Member

    Can somebody please advise me on which database table or tables the following information is stored/housed in:

    #1 - The 'Review Ratings' that are displayed under 'Review History' in ESS (ESS > Employment > Review History)?

    #2 - The 'Percentage Change' and the 'Pay Rate' (as of each historical effective date) that are both displayed under the 'Pay Rate History' in ESS (ESS > Pay > Pay Rate History)?

    Thank you.

    Dave Curtis
    Veteran Member
    Posts: 136
    Veteran Member
      #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)
      Dave Curtis
      Veteran Member
      Posts: 136
      Veteran Member
        #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
        TBonney
        Veteran Member
        Posts: 277
        Veteran Member
          Dave,

          Thanks for the guidance! I will take a gander at these tables and sample queries later this afternoon. I really appreciate the assistance.
          TBonney
          Veteran Member
          Posts: 277
          Veteran Member
            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.
            Dave Curtis
            Veteran Member
            Posts: 136
            Veteran Member
              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.
              Greg Moeller
              Veteran Member
              Posts: 1498
              Veteran Member

                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.

                Attachments
                TBonney
                Veteran Member
                Posts: 277
                Veteran Member
                  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.
                  Greg Moeller
                  Veteran Member
                  Posts: 1498
                  Veteran Member
                    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.
                    TBonney
                    Veteran Member
                    Posts: 277
                    Veteran Member
                      Great info/insight. Thanks for sharing Greg!
                      You are not authorized to post a reply.