Maximo

Maximo

Come for answers, stay for best practices. All we're missing is you.

 View Only
Back to discussions
Expand all | Collapse all

WO actual labor hours vs. LABTRANS regular hours

  • 1.  WO actual labor hours vs. LABTRANS regular hours

    Posted Wed October 27, 2021 07:01 PM
    Edited by System Admin Wed March 22, 2023 11:52 AM
    I want to use an SQL query to check for issues in WO actual labor hours vs. labtrans regular hours.
    #AssetandFacilitiesManagement
    #Maximo


  • 2.  RE: WO actual labor hours vs. LABTRANS regular hours

    Posted Wed October 27, 2021 07:01 PM
    Edited by System Admin Wed March 22, 2023 11:51 AM

    I can use the following query:

    --I edited this query so that it includes premium pay hours
select
    wo.wonum,
    wo.istask,
    round(wo.actlabhrs,1)   as wo_actlabhrs,
    round(lab.regpremhrs,1) as lab_regpremhrs,
    round(wo.actlabhrs - nvl(lab.regpremhrs,0),3) as hrs_difference
from
    maximo.workorder wo
left join
    (
    select
        refwo,
        sum(regularhrs + nvl(premiumpayhours,0)) as regpremhrs
    from
        maximo.labtrans
    where
        genapprservreceipt = 1 --aka "Approved"
    group by
        refwo
    ) lab
    on wo.wonum = lab.refwo
where
    abs(wo.actlabhrs - nvl(lab.regpremhrs,0)) >= 0.1




    Edit:
    In hindsight I should have used a FULL OUTER JOIN instead of a LEFT JOIN.
    I talk about that issue here: Checking WO cost fields vs. trans tables
    #Maximo
    #AssetandFacilitiesManagement


  • 3.  RE: WO actual labor hours vs. LABTRANS regular hours

    Posted Thu October 28, 2021 12:07 PM
    User,

    So the ugly thing here is this SQL reveals a load of crap data in the MaxDemo DB. (ugh!). The only thing I can think of is improper data loading/ integrations/ automations scripts or other customizations that are affecting those attributes on WO.  The way the tool works is the actlabhrs and actlabcost are calculated when data is entered into labtrans.  So if all data is loaded through front end UI then there should be no difference.

    Do you have java customizations that are populating the actlabhrs and actlabocst?  What about automation scripts? and like I mentioned above.... and data loading going on whereby the actlabhrs and actlabcost are directly loaded?

    ------------------------------
    Bradley K. Downing , MBA
    Solutions Engineer
    IBM
    Bakersfield CA
    ------------------------------

    Original Message


  • 4.  RE: WO actual labor hours vs. LABTRANS regular hours

    Posted Fri October 29, 2021 04:05 AM
    Well, I would love to see IBM invest in a new MAXDEMO database where the data has actually passed through the MBO business logic and dates were not 20 years old. This is one of the reasons why I never use existing MAXDEMO data in any Maximo Secrets articles. Having a well populated and accurate MAXDEMO database would be a wonderful learning tool.

    ------------------------------
    Andrew Jeffery
    ------------------------------

    Original Message


  • 5.  RE: WO actual labor hours vs. LABTRANS regular hours

    Posted Fri October 29, 2021 10:50 AM
    Andrew, 

    To use a "Valley Girl/ Millennial" phrase... "Like OH my God! Riiigth?!"   Hahahaha.....  So true.  The only hitch here is this is "Newer" crap data... and yes to have the data be at least MBO validated would be a tremendous learning tool.  BTW some the dates are actually approaching 30 years old!  HA!!

    ------------------------------
    Bradley K. Downing , MBA
    Solutions Engineer
    IBM
    Bakersfield CA
    ------------------------------

    Original Message


  • 6.  RE: WO actual labor hours vs. LABTRANS regular hours

    Posted Thu November 04, 2021 10:39 AM
    @Bradley Dowling and @Andrew Jeffery
    I had submitted an RFE about the MAXDEMO database: Replace the MAXDEMO database
    IBM changed the status of that RFE to "Needs more information". I don't know what was unclear about that RFE...not sure what other information to provide.

    Feel free to comment / provide extra context.

    Cheers.

    ​​
    Original Message


  • 7.  RE: WO actual labor hours vs. LABTRANS regular hours

    Posted Fri November 05, 2021 05:29 AM
    I have added some more information to the RFE which you can find here https://ibm-ai-apps.ideas.ibm.com/ideas/MASM-I-597
    Please vote for it if you think the MAXDEMO database should be brought up to date.

    ------------------------------
    Andrew Jeffery
    ------------------------------

    Original Message


  • 8.  RE: WO actual labor hours vs. LABTRANS regular hours

    Posted Sat October 30, 2021 05:38 PM
    Edited by System Admin Wed March 22, 2023 11:43 AM
    Hi Bradley,

    Regarding your question about customizations:

    It turns out that we do have a couple of customizations related to labor actuals:
    1. Task timer (Java). As discussed in a related post.
    2. Automation script that adds a percentage to LINECOST.
    It's entirely possible that one of those customizations is causing the issue. Unfortunately, we haven't been able to reproduce the problem on demand, and haven't been able to determine what's causing the issue.

    With that said, a colleague at a different organization ran the query and found a few thousand WOs that had incorrect actual labor costs too. That organization doesn't have the customizations that we have. But it's still hard to know if that organization's problem is the same as ours or not.
    #AssetandFacilitiesManagement
    #Maximo


  • 9.  RE: WO actual labor hours vs. LABTRANS regular hours

    Posted Fri October 29, 2021 08:23 AM

    You might also want to include "Premium Pay Hours" in your analysis.  If you are looking at large discrepancies like with Work Order #WO8272, it might be due to Premium Pay Hours being entered for a Work Order.  These hours will not show up under your Regular Hours for labtrans but will show up in your actlabhrs on the Work Order itself.  A lot of times these are easily overlooked, especially when entering them is not part of your standard procedures but their use slips in by accident.



    ------------------------------
    Steve Platt
    Georgia Building Authority
    ------------------------------

    Original Message


  • 10.  RE: WO actual labor hours vs. LABTRANS regular hours

    Posted Fri October 29, 2021 09:55 AM
    Edited by System Admin Wed March 22, 2023 11:46 AM
    Thanks Steve. Good catch!
    Users just started entering premium pay a couple of weeks ago. So I didn't think of it.

    Do you think adding regularhrs + premiumpayhours together would be the correct way of handling it?


    select

        wo.wonum,

        wo.istask,

        round(wo.actlabhrs,1)   as wo_actlabhrs,

        round(lab.regpremhrs,1) as lab_regpremhrs,

        round(wo.actlabhrs - nvl(lab.regpremhrs,0),3) as hrs_difference

    from

        maximo.workorder wo

    left join

        (

        select

            refwo,

            sum(regularhrs + nvl(premiumpayhours,0)) as regpremhrs

        from

            maximo.labtrans

        where

            genapprservreceipt = 1 --aka "Approved"

        group by

            refwo

        ) lab

        on wo.wonum = lab.refwo

    where

        abs(wo.actlabhrs - nvl(lab.regpremhrs,0)) >= 0.1 


    Now that I'm accounting for premium pay hours, it looks like WO2011 has been eliminated from the resultset (which is good).




    #AssetandFacilitiesManagement
    #Maximo


  • 11.  RE: WO actual labor hours vs. LABTRANS regular hours

    Posted Fri October 29, 2021 10:30 AM
    I believe so.  Sorry it only got rid of one of your errant Work Orders.

    Would definitely be interested in what's causing the other two.  I have seen some rounding issues before, but not 24 hours.  I have also occasionally seen it get out of sync with some mobile transactions and on the materials side with lots of Issues/Returns for the same Work Order.  Do you happen to have any "negative" Labor Entries on the other two?

    That's an interesting one.

    ------------------------------
    Steve Platt
    Georgia Building Authority
    ------------------------------

    Original Message


  • 12.  RE: WO actual labor hours vs. LABTRANS regular hours

    Posted Sat October 30, 2021 06:24 PM
    Edited by System Admin Wed March 22, 2023 11:54 AM
    Hi Steve,

    To answer your question:
    • WO8272 doesn't have any labor actuals associated with that WO. It only has a labor actual that's associated with a task.
    • WO5804 doesn't have any negative labor actuals either. Just a single positive labor actual.
      Edit: This WO is actually a task. For what it's worth, my custom Java timer tool wasn't used on this task.


    One thing I'd wondered about is: were any of our WOs originally tasks? See: Convert task to child work order?
    When tasks are converted to WOs, there are a bunch of known problems that happen in the data. I had wondered if that might be what caused this issue.


    However, I don't think any of the WOs in our production environment were originally tasks. A tell-tale sign of tasks being converted to WOs is: the WOACTIVITY is wrong for those WOs. The WOACTIVITY ought to be WORKORDER, but when a task is converted to a WO, the WOACTIVITY remains as ACTIVITY (incorrect).

    Details:
    When I query for records where WOCLASS is incorrect, I don't see any problems in Production. All WOs are WOCLASS=WORKORDER, which is correct.


    But if I query a test environment where I know there are tasks that have been converted to WOs, I do see some problems in the WOCLASS field:


    Related: Find WOs that were originally tasks
    #Maximo
    #AssetandFacilitiesManagement