Maximo Open Forum

I got this working for us a few years back by just changing the DocLinks relationship.  I am going to need clarification on somethings to help you...

When you say "Associated Inspection", do you really mean the Inspection Result (InspectionResult table) or the Inspection Form (InspectionForm table)?  

For us it was the former.  If I am using Routes and have an inspection on each task, then I want to see the attachments from each of those inspection results on the main inspection.  Think about a bunch of assets or locations in a warehouse that are inspected.  I want to go to the parent work order and see all the attachments from the inspection of all of those assets and locations.  We also have it running back through the follow ups. 

Is that what you mean?  If so, I added this to the end of the DOCLINKS relationship.

select doclinksid from doclinks where OwnerTable = 'INSPFIELDRESULT' AND ownerid in (SELECT InspFieldResult.InspFieldResultID FROM MAXIMO.InspFieldResult INNER JOIN MAXIMO.INSPECTIONRESULT ON InspFieldResult.RESULTNUM = INSPECTIONRESULT.RESULTNUM AND InspFieldResult.SITEID = INSPECTIONRESULT.SITEID AND InspFieldResult.ORGID = INSPECTIONRESULT.ORGID WHERE INSPECTIONRESULT.orgid=:orgid and INSPECTIONRESULT.siteid=:siteid and (((INSPECTIONRESULT.REFERENCEOBJECTID=:WONUM or INSPECTIONRESULT.PARENT=:WONUM ) and INSPECTIONRESULT.REFERENCEOBJECT = 'WORKORDER') OR (INSPECTIONRESULT.REFERENCEOBJECTID=:WONUM and INSPECTIONRESULT.REFERENCEOBJECT = 'PARENTWO') OR (INSPECTIONRESULT.PARENT=:WONUM and INSPECTIONRESULT.REFERENCEOBJECT = 'WOACTIVITY') OR (INSPECTIONRESULT.PARENT=:WONUM and INSPECTIONRESULT.REFERENCEOBJECT='MULTIASSETLOCCI') OR (INSPECTIONRESULT.fupobject='WORKORDER' AND INSPECTIONRESULT.FUPOBJECTID=:WONUM)) union SELECT InspFieldResult.InspFieldResultID FROM MAXIMO.InspFieldResult where (INSPQUESTIONNUM,INSPFORMNUM ,REVISION,RESULTNUM,SITEID,orgid) in (SELECT INSPQUESTIONNUM,INSPFORMNUM ,REVISION,RESULTNUM,SITEID,orgid FROM MAXIMO.InspFieldResult where InspFieldResult.fupobject='WORKORDER' AND InspFieldResult.FUPOBJECTID=:WONUM and InspFieldResult.siteid=:siteid and InspFieldResult.orgid=:orgid)))

Thank You Brad for your response. When I added that to the OOB 7.6 WORKORDER DOCLINKS Relationship it went beyond the 4k limit for the Where Clause. I shortened it after the second UNION but did not get the desired result. I guess to clarify have work orders with Inspections at the Parent and at the task levels. Every Inspection has at least one or more questions that requires the user to attach in the question one or more attachments(Pictures). Right now we have autoscripted so that the attachment is copied to the parent and task. We are just in the development stage and I have already seen 1000's of duplicated attachments since the Autoscript copies the attachment to the work order. The Inspection attachment I see appears in the doclinks Object with the owner being INSPFIELDRESULT. My goal is just for the link to that attachment to be on the work order. Because I anticipate when we go live with hundreds of inspections being done daily with multiple attachments the amount of attachments(Copies) will be immense

How large is the current statement?  Are there components it is checking that you are not using and can remove to make room for that?  If I knew how much room we have to work with we might be able to strip out something or reword it.

Ours is like 40 characters short for the 4,000 character limit.

Brad I really appreciate what you are doing. Here is the OOB WORKORDER-DOCLINKS Relationship on this 7.6. When I combine your piece it hits 4075

doclinksid in (select doclinksid from doclinks where (ownertable='WORKORDER' and ownerid=:workorderid) UNION ALL select doclinksid from doclinks where (ownertable='WORKORDER' and ownerid in (select workorderid from workorder where parent=:wonum and istask=1 and siteid=:siteid)) UNION ALL select doclinksid from doclinks where (ownertable='ASSET' and ownerid in (select assetuid from asset where assetnum=:assetnum and siteid=:siteid)) UNION ALL select doclinksid from doclinks where (ownertable='LOCATIONS' and ownerid in (select locationsid from locations where location=:location and siteid=:siteid)) UNION ALL select doclinksid from doclinks where (ownertable='JOBPLAN' and ownerid in (select jobplanid from jobplan where jpnum=:jpnum and (siteid is null or siteid=:siteid) and pluscrevnum =:pluscjprevnum) ) UNION ALL select doclinksid from doclinks where (ownertable='PM' and ownerid in (select pmuid from pm where pmnum=:pmnum and siteid=:siteid)) UNION ALL select doclinksid from doclinks where (ownertable='SAFETYPLAN' and ownerid in (select safetyplanuid from safetyplan,wosafetyplan where safetyplan.safetyplanid=wosafetyplan.safetyplanid and wosafetyplan.wonum=:wonum and wosafetyplan.siteid=:siteid)) UNION ALL select doclinksid from doclinks where (ownertable in ('SR','INCIDENT','PROBLEM') and ownerid in (select ticketuid from ticket,relatedrecord where ticketid=recordkey and ticket.class = relatedrecord.class and relatedrecclass=:woclass and relatedreckey=:wonum and relatedrecsiteid=:siteid)) UNION ALL select doclinksid from doclinks where (ownertable in ('WOCHANGE','WORELEASE','WOACTIVITY') and ownerid in (select workorderid from workorder,relatedrecord where wonum=recordkey and workorder.woclass = relatedrecord.class and relatedrecclass=:woclass and relatedreckey=:wonum and relatedrecsiteid=:siteid)) UNION ALL select doclinksid from doclinks where (ownertable='COMMLOG' and ownerid in (select commloguid from commlog where ownerid=:workorderid and ownertable in (:&synonymlist&_WOCLASS[ACTIVITY,CHANGE,RELEASE,WORKORDER]))) UNION ALL select doclinksid from doclinks where (ownertable='SLA' and ownerid in (select slaid from sla,slarecords,workorder where sla.slanum=slarecords.slanum and slarecords.ownerid=workorder.workorderid and sla.objectname='WORKORDER' and slarecords.ownertable='WORKORDER' and workorder.wonum=:wonum)) UNION ALL select doclinksid from doclinks where (ownertable='PLUSDSPLAN' and ownerid in (select plusdsplanid from plusdsplan where (siteid is null or siteid=:siteid) and status in (select value from synonymdomain where (siteid is null or siteid=:siteid) and domainid = 'PLUSCDSSTATUS' and maxvalue = 'APPR') and dsplannum in (select dsplannum from pluscwods where wonum=:wonum and siteid=:siteid))))

Try this one.  I removed references to the orgid to make space (but only in the new code).  This assumes you don't have the same site in more than one org (not sure that is allowed).

I don't have all those modules so I can't test in mine but I think the SQL is correct.  If not, look at the paranthesis past you PLUSCDSSTATUS

doclinksid in (select doclinksid from doclinks where (ownertable='WORKORDER' and ownerid=:workorderid) UNION ALL select doclinksid from doclinks where (ownertable='WORKORDER' and ownerid in (select workorderid from workorder where parent=:wonum and istask=1 and siteid=:siteid)) UNION ALL select doclinksid from doclinks where (ownertable='ASSET' and ownerid in (select assetuid from asset where assetnum=:assetnum and siteid=:siteid)) UNION ALL select doclinksid from doclinks where (ownertable='LOCATIONS' and ownerid in (select locationsid from locations where location=:location and siteid=:siteid)) UNION ALL select doclinksid from doclinks where (ownertable='JOBPLAN' and ownerid in (select jobplanid from jobplan where jpnum=:jpnum and (siteid is null or siteid=:siteid) and pluscrevnum =:pluscjprevnum) ) UNION ALL select doclinksid from doclinks where (ownertable='PM' and ownerid in (select pmuid from pm where pmnum=:pmnum and siteid=:siteid)) UNION ALL select doclinksid from doclinks where (ownertable='SAFETYPLAN' and ownerid in (select safetyplanuid from safetyplan,wosafetyplan where safetyplan.safetyplanid=wosafetyplan.safetyplanid and wosafetyplan.wonum=:wonum and wosafetyplan.siteid=:siteid)) UNION ALL select doclinksid from doclinks where (ownertable in ('SR','INCIDENT','PROBLEM') and ownerid in (select ticketuid from ticket,relatedrecord where ticketid=recordkey and ticket.class = relatedrecord.class and relatedrecclass=:woclass and relatedreckey=:wonum and relatedrecsiteid=:siteid)) UNION ALL select doclinksid from doclinks where (ownertable in ('WOCHANGE','WORELEASE','WOACTIVITY') and ownerid in (select workorderid from workorder,relatedrecord where wonum=recordkey and workorder.woclass = relatedrecord.class and relatedrecclass=:woclass and relatedreckey=:wonum and relatedrecsiteid=:siteid)) UNION ALL select doclinksid from doclinks where (ownertable='COMMLOG' and ownerid in (select commloguid from commlog where ownerid=:workorderid and ownertable in (:&synonymlist&_WOCLASS[ACTIVITY,CHANGE,RELEASE,WORKORDER]))) UNION ALL select doclinksid from doclinks where (ownertable='SLA' and ownerid in (select slaid from sla,slarecords,workorder where sla.slanum=slarecords.slanum and slarecords.ownerid=workorder.workorderid and sla.objectname='WORKORDER' and slarecords.ownertable='WORKORDER' and workorder.wonum=:wonum)) UNION ALL select doclinksid from doclinks where (ownertable='PLUSDSPLAN' and ownerid in (select plusdsplanid from plusdsplan where (siteid is null or siteid=:siteid) and status in (select value from synonymdomain where (siteid is null or siteid=:siteid) and domainid = 'PLUSCDSSTATUS' and maxvalue = 'APPR') and dsplannum in (select dsplannum from pluscwods where wonum=:wonum and siteid=:siteid)) UNION ALL select doclinksid from doclinks where OwnerTable = 'INSPFIELDRESULT' AND ownerid in (SELECT InspFieldResult.InspFieldResultID FROM MAXIMO.InspFieldResult INNER JOIN MAXIMO.INSPECTIONRESULT ON InspFieldResult.RESULTNUM = INSPECTIONRESULT.RESULTNUM AND InspFieldResult.SITEID = INSPECTIONRESULT.SITEID WHERE INSPECTIONRESULT.siteid=:siteid and (((INSPECTIONRESULT.REFERENCEOBJECTID=:WONUM or INSPECTIONRESULT.PARENT=:WONUM ) and INSPECTIONRESULT.REFERENCEOBJECT = 'WORKORDER') OR (INSPECTIONRESULT.REFERENCEOBJECTID=:WONUM and INSPECTIONRESULT.REFERENCEOBJECT = 'PARENTWO') OR (INSPECTIONRESULT.PARENT=:WONUM and INSPECTIONRESULT.REFERENCEOBJECT = 'WOACTIVITY') OR (INSPECTIONRESULT.PARENT=:WONUM and INSPECTIONRESULT.REFERENCEOBJECT='MULTIASSETLOCCI') OR (INSPECTIONRESULT.fupobject='WORKORDER' AND INSPECTIONRESULT.FUPOBJECTID=:WONUM)) union SELECT InspFieldResult.InspFieldResultID FROM MAXIMO.InspFieldResult where (INSPQUESTIONNUM,INSPFORMNUM ,REVISION,RESULTNUM,SITEID,orgid) in (SELECT INSPQUESTIONNUM,INSPFORMNUM ,REVISION,RESULTNUM,SITEID,orgid FROM MAXIMO.InspFieldResult where InspFieldResult.fupobject='WORKORDER' AND InspFieldResult.FUPOBJECTID=:WONUM and InspFieldResult.siteid=:siteid)))

Hi Brad, Thanks Again, but I got a "BMXAA6713E - The MBO fetch operation failed in the mboset with the SQL error code -104" I looked everywhere I thought for the problem based on the error, but.... Thanks Again Guess I will stick to the script copying a duplicate.

I looked at this at lunch and saw that you do not reference the schema of Maximo in your existing code so I removed that.  I also removed the code for the follow ups, so these are just the inspections.  I replaced the union all with union since they are numbers it is fine as it will just do a distinct which is what you want anyways.

I played with it and on my copy commented out parts but this works for me.

doclinksid in (select doclinksid from doclinks where (ownertable='WORKORDER' and ownerid=:workorderid) UNION select doclinksid from doclinks where (ownertable='WORKORDER' and ownerid in (select workorderid from workorder where parent=:wonum and istask=1 and siteid=:siteid)) UNION select doclinksid from doclinks where (ownertable='ASSET' and ownerid in (select assetuid from asset where assetnum=:assetnum and siteid=:siteid)) UNION select doclinksid from doclinks where (ownertable='LOCATIONS' and ownerid in (select locationsid from locations where location=:location and siteid=:siteid)) UNION select doclinksid from doclinks where (ownertable='JOBPLAN' and ownerid in (select jobplanid from jobplan where jpnum=:jpnum and (siteid is null or siteid=:siteid) and pluscrevnum =:pluscjprevnum) ) UNION select doclinksid from doclinks where (ownertable='PM' and ownerid in (select pmuid from pm where pmnum=:pmnum and siteid=:siteid)) UNION select doclinksid from doclinks where (ownertable='SAFETYPLAN' and ownerid in (select safetyplanuid from safetyplan,wosafetyplan where safetyplan.safetyplanid=wosafetyplan.safetyplanid and wosafetyplan.wonum=:wonum and wosafetyplan.siteid=:siteid)) UNION select doclinksid from doclinks where (ownertable in ('SR','INCIDENT','PROBLEM') and ownerid in (select ticketuid from ticket,relatedrecord where ticketid=recordkey and ticket.class = relatedrecord.class and relatedrecclass=:woclass and relatedreckey=:wonum and relatedrecsiteid=:siteid)) UNION select doclinksid from doclinks where (ownertable in ('WOCHANGE','WORELEASE','WOACTIVITY') and ownerid in (select workorderid from workorder,relatedrecord where wonum=recordkey and workorder.woclass = relatedrecord.class and relatedrecclass=:woclass and relatedreckey=:wonum and relatedrecsiteid=:siteid)) UNION select doclinksid from doclinks where (ownertable='COMMLOG' and ownerid in (select commloguid from commlog where ownerid=:workorderid and ownertable in (:&synonymlist&_WOCLASS[ACTIVITY,CHANGE,RELEASE,WORKORDER]))) UNION select doclinksid from doclinks where (ownertable='SLA' and ownerid in (select slaid from sla,slarecords,workorder where sla.slanum=slarecords.slanum and slarecords.ownerid=workorder.workorderid and sla.objectname='WORKORDER' and slarecords.ownertable='WORKORDER' and workorder.wonum=:wonum)) UNION select doclinksid from doclinks where (ownertable='PLUSDSPLAN' and ownerid in (select plusdsplanid from plusdsplan where (siteid is null or siteid=:siteid) and status in (select value from synonymdomain where (siteid is null or siteid=:siteid) and domainid = 'PLUSCDSSTATUS' and maxvalue = 'APPR') and dsplannum in (select dsplannum from pluscwods where wonum=:wonum and siteid=:siteid))) UNION select doclinksid from doclinks where OwnerTable = 'INSPFIELDRESULT' AND ownerid in (SELECT InspFieldResult.InspFieldResultID FROM InspFieldResult INNER JOIN INSPECTIONRESULT ON InspFieldResult.RESULTNUM = INSPECTIONRESULT.RESULTNUM AND InspFieldResult.SITEID = INSPECTIONRESULT.SITEID WHERE INSPECTIONRESULT.siteid=:siteid and (((INSPECTIONRESULT.REFERENCEOBJECTID=:WONUM or INSPECTIONRESULT.PARENT=:WONUM ) and INSPECTIONRESULT.REFERENCEOBJECT = 'WORKORDER') OR (INSPECTIONRESULT.REFERENCEOBJECTID=:WONUM and INSPECTIONRESULT.REFERENCEOBJECT = 'PARENTWO') OR (INSPECTIONRESULT.PARENT=:WONUM and INSPECTIONRESULT.REFERENCEOBJECT = 'WOACTIVITY') OR (INSPECTIONRESULT.PARENT=:WONUM and INSPECTIONRESULT.REFERENCEOBJECT='MULTIASSETLOCCI') OR (INSPECTIONRESULT.fupobject='WORKORDER' AND INSPECTIONRESULT.FUPOBJECTID=:WONUM))))

With what you gave me it does work now. THANKS. Then I talked with some people and they reminded me, that is an OOB Relationship(Which I avoid unless I am desparate) which if a Patch happens that changes that relationship, bye-bye. But I tried it and worked. So got with my PM and showed I just put a small table on one of the tabs that points to a separate relationship(Yours) and they can see and retrieve the attachments for Inspections. Thanks, could not have got there without yours