-1

I want to write an SQL query that mimics the results in the Maximo Start Center assignments section. The assignments are workflow assignments.

enter image description here

I tried querying the workorder table and specifying the assignedownergroup that the user is in:

select
    *
from
    workorder
where
    status in ('WAPPR','APPR','INPRG')
    and assignedownergroup = 'FIRE'

However, the query returns more work orders than what's shown in the Start Center assignments.

How can I write a query to mimic the workflow assignments in the Start Center?

User1974
  • 276
  • 1
  • 17
  • 63
  • Hi, what are the filters you applied to the tool to display that result?, does that's included in the sample data shared. Like Target (5), variance (-5). And what columns you are trying to select, I see the worktype in('CM', 'PM') as unique for WO1605, WO1615 – sandy v Sep 17 '19 at 01:45
  • Are the other WOs from sites the user you logged in with do not have access to? – Preacher Sep 17 '19 at 14:00

2 Answers2

2

My other answer would work if the portlet you highlighted was a Result Set against WORKORDER, but it is not. The portlet you have highlighted is the Workflow Inbox, which is based on WFASSIGNMENT where assigncode = 'userid'.

A full query that mimics the workflow inbox would look like this, in Oracle SQL:

select
    (select 'WO '||wonum||' ('||description||') is waiting for '||wfassignment.description 
        from workorder 
        where workorderid = wfassignment.ownerid
            and wfassignment.ownertable = 'WORKORDER'
        /* Union in other tables */) description,
    app
from wfassignment
where assignstatus = 'ACTIVE'
    and assigncode = 'JDOE'

I'm not sure where the WO prefix on the assignment description comes from. But since you could add workflow to your own app based on your own object, I would like to think it comes from metadata somewhere instead of code. And the description itself is probably a format string in MAXMESSAGES.

You'll notice the Union in comment in my query, where you would add unioned queries against PR or PM or ASSET or whatever.

Preacher
  • 2,127
  • 1
  • 11
  • 25
1

The easiest way to get the SQL Maximo is running is:

  1. Go to the Logging application
  2. Select the sql Root Logger and add a "child" Logger of WORKORDER.WORKORDER (that's SERVICE.OBJECT from DB Config) with a Log Level of INFO.
  3. Get ready to open your log file.
  4. Load your start center.
  5. Open your log file.

The SQL issued by Maximo to load the result set should be near the bottom of your log file.

Preacher
  • 2,127
  • 1
  • 11
  • 25
  • After writing this answer, I realized you had a workflow inbox portlet highlighted, not a result set portlet. I'll leave this answer here as I think it is generally helpful. I will delete it if it gets neg'd. – Preacher Sep 17 '19 at 15:04