SQL - Maximo 7.5 Query WorkOrder table by Asset Custodian (AssetUserCust table)

From ExxtremeWiki
Jump to: navigation, search

Examples from your SQL Tool (Benthic; Golden6 preferred)[edit]

SELECT * 
  FROM maximo.workorder w
  JOIN maximo.assetusercust a ON a.siteid = w.siteid AND a.assetnum = w.assetnum
  WHERE w.assetnum IS NOT NULL
  AND historyflag = 0
  AND istask = 0
  AND a.personid = 'WINSTON'
;

Additional modifiers you might use =[edit]

  AND iscustodian = 1 -- is this person the custodian?
  AND isprimary = 1 -- is this person the primary custodian? (Note: you can be primary and not be flagged custodian)
  AND isuser = 1 -- is this person a user of the asset?

Querying the AssetUserCust table while limiting the results to < 100 rows[edit]

SELECT * FROM maximo.assetusercust WHERE rownum < 100;

Examples from inside Maximo[edit]

Append the following to your default where clause for example:

AND (assetnum IN (SELECT assetnum FROM assetusercust WHERE personid =  :USER))

Once saved it might look something like this: (Note: On save it will replace the :user with your username, you will have to modify the value back to :user and resave to have it work dynamically)

(((woclass = 'WORKORDER' OR woclass = 'ACTIVITY') AND historyflag = 0 AND istask = 0 AND siteid = 'BEDFORD')) 
AND (assetnum IN (SELECT assetnum FROM assetusercust WHERE personid =  :USER))