Technical Blog Post
Query to show if record has an attachment or not (DOCLINKS)
Is there a query that I can run on my SB to show if an object has an attachment or not in Maximo ?
YES ! Here is how you can check the doclink table to see what references are.
select wonum,description,workorderid from workorder where workorderid in (select ownerid from doclinks where ownertable='WORKORDER')
Repeat for the other applications if you would like to see its behavior.
select pmnum,description,pmid from pm where pmid in (select ownerid from doclinks where ownertable='PM')
select assetnum,description,assetid from asset where assetid in (select ownerid from doclinks where ownertable='ASSET')
This will show the records which have a document attached, you then check the docinfo to see the document info and check if this document exists.
For missing doclinks info on workorders, normally we query the following way, for example :
1. Find the workorderid for the wonum=1000
select wonum,workorderid from workorder where wonum='1000'
2. Check the doclink table (in this case, there are three results).
select document,ownertable,ownerid,doclinksid,docinfoid from doclinks where ownertable='WORKORDER' and ownerid='37'
1015 WORKORDER 37 51 24
1016 WORKORDER 37 42 25
1017 WORKORDER 37 43 26
3. Check the docinfo table
select document,urlname,docinfoid from docinfo where docinfoid in (select docinfoid from doclinks where ownertable='WORKORDER' and ownerid='37')
1015 \DOCLINKS\AAA.DOC 24
1016 \DOCLINKS\BBB.DOC 25
1017 \DOCLINKS\CCC.DOC 26
NOTE: Check the doclink table for the ownerid. Check that the docinfo table has a record for this doclink.
Attempt to attach a new document and see if this is listed. If you can attach a new document and it shows then the problem isn't with attach documents setup, it may have to do with your historical doclinks documents attached in the past.
select * from doclinks,docinfo where doclinks.docinfoid = docinfo.docinfoid order by ownertable
The ownerid value is used for this mapping from the doclinks table. You would look up the ownertable to find asset records (for example), then use the
ownerid values in the result set to look up the ASSET table using the assetid column in order to find the assetnum.
SELECT * FROM doclinks where ownertable='ASSET'
Then, run this to find the asset numbers:
SELECT * FROM ASSET WHERE ASSETID='26' (or any other record ID value)
This info should help you get your data.