Maximo Open Forum

 View Only
Expand all | Collapse all

Places in Maximo where WO status is used (and how to query for WO status properly)

  • 1.  Places in Maximo where WO status is used (and how to query for WO status properly)

    Posted 12-09-2021 23:34

    We're going through an exercise where we're trying to find all the different places in Maximo that reference WO status.

    The reasons being:
    1. We're adding a new synonym called WCLOSE [waiting to be closed]. We want to make sure that queries are handling COMP, WCLOSE, and CLOSE correctly.
    2. The team that implemented our system didn't consistently reference internal values -- sometimes they referenced synonym values.
      1. For example, I think they should have queried like this: workorder.status in (select value from synonymdomain where domainid = 'WOSTATUS' and maxvalue in ('COMP','CLOSE')) , but instead they queried like this: workorder.status in ('COMP','CLOSE')
      2. My thought process is: It's safer to reference internal values rather than synonym values. New synonym values might be added after the query is written, but the person who wrote the query wouldn't know to update it with the new synonym value. So statuses could be missed. I think referencing the internal value solves that issue.
    3. Likewise, we've noticed that sometimes statuses are accidently omitted in queries.
      1. For example, status synonyms like COMPBILL [Completed - Billed] might have been included in queries, other status synonyms like BILL [To Be Billed/Recovered] were missed. We want to catch cases like that.
    4. We've heard that using the :&synonymlist&_ bind variable is more efficient than using a subquery.
      1. For example, use this: workorder.status in (:&synonymlist&_wostatus[COMP,CLOSE])  instead of this: workorder.status in (select value from synonymdomain where domainid = 'WOSTATUS' and maxvalue in ('COMP','CLOSE'))   
      2. "This is very much recommended to be used in relationship where clause when you need to qualify the records by the synonym values of a synonym domain. The reason is that the resulted query will offer much better performance comparing to a subselect to synonym domain table." Source
    5. We've also heard that there is a right way to query for statuses in automation scripts.
      1. Bruno says it's best to use the toInternalString() method. How to correctly handle synonym domains in automation script

    So, my first question is:
    What are the various places in Maximo that use WO status? I'm hoping to come up with a list of queries, WHERE clauses, etc. that I need to check.
    • Saved Queries
    • Result Sets / Start Centers
    • Workflow
    • Reports (custom BIRT and ad-hoc)
    • Automation scripts
    • Escalations
    • Conditional Expressions
    • Relationships
    • Integrations
    • Views
    • Table Domains
    • Other?

    And my second question is:
    From your experience, what's the best way to query for WO statuses?
    • I have to admit ,I have a bit of a mental block when it comes to internal values vs synonym values, and how to handle them properly. Is there anything in points 1-5 above that I've misunderstood or overlooked?


    User 1972

  • 2.  RE: Places in Maximo where WO status is used (and how to query for WO status properly)

    Maximo Certified
    Posted 12-19-2021 09:06
    There is not a single rule of thumb that can be stated for this as which value you need to use is totally dependent upon the business requirement you have.

    Generally, it is advisable to use internal value in configuration-related modules. For example- Workflows, Relationships so that going forward if you are creating another synonym value of any internal status, there is no need for you to revise them each and every time. 

    Tricky part can be On-Demand/Ad Hoc reports because you might have a case where you want to show data for 1 custom status only. For example- COMP has 2 custom statuses TECHCOMP & QACOMP and you want to show only data related to TECHCOMP in your report then you can't use internal value but in case you are showing both values for a given example then you can use internal value.

    Also, another thing to be considered is how big is your domain list. Sometimes the customer has a very big list and for different configuration, it is practically impossible to write queries using IN () clause using custom value so they end up writing NOT IN () using internal values. Hope this makes sense.

    Prashant Sharma