Maximo: Mimic Workflow assignments with an SQL query

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

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?

Answer

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.