Query report on WTPart NOT Associated with EPMDocument

[sshankaracharya]

I would like to know how to build a query report to search for WTParts that do not have association with EPMDocument. I see that only one link goes to EPMDocument from WTPart i.e EPMBuildHistory, are there any other links, using which i can query?

Thanks,

Shashi

[Marco_Tosin]

Shashi,

take a look at this document Resource for reporting

There are lots of report already done to use and some other helpful news about reporting.

[BhushanNehe]

Hi Shashikantha,

To check CAD parts and assemblies, can use the pseudo query below as a guide…

private static String ID = WTAttributeNameIfc.ID_NAME; 
private static String MASTER_ID      = Iterated.MASTER_REFERENCE + "." + WTAttributeNameIfc.REF_OBJECT_ID;
private static String ONE_OFF_VERSION = OneOffVersioned.ONE_OFF_VERSION_IDENTIFIER + "." +
OneOffVersionIdentifier.ONEOFFVERSIONID;
private static String ROLEA_BRANCH_ID = WTAttributeNameIfc.ROLEA_VERSION_ID;
private static String ROLEB_ID        = WTAttributeNameIfc.ROLEB_OBJECT_ID;
private static String VERSION_SORT_ID = Versioned.VERSION_IDENTIFIER + "." + VersionIdentifier.VERSIONSORTID;
private static String WIP_STATE      = Workable.CHECKOUT_INFO + "." + CheckoutInfo.STATE;



SELECT …
  FROM EPMDocumentMaster doc_master
  JOIN EPMDocument doc ON doc_master.ID = doc.MASTER_ID
WHERE ( doc_master.DOC_TYPE = CADASSEMBLY OR doc_master.DOC_TYPE = CADCOMPONENT )


  // ignore non-latest iterations
  AND doc.LATEST_ITERATION = 1


  // latest version (revision)
// (If you want to check every version (revision), this condition is not needed.)
  AND NOT EXISTS( SELECT 1 FROM EPMDocument later_version
WHERE later_version.MASTER_ID = doc.MASTER_ID
AND later_version .VERSION_SORT_ID > doc.VERSION_SORT_ID
)


  // ignore working copies
  AND ( doc. WIP_STATE != WORKING AND doc.WIP_STATE != PRIVATE_WORKING )


  // ignore iterations checked out to a project
  AND doc.ONE_OFF_VERSION IS NULL


  // doesn’t have a build rule
  AND NOT EXISTS( SELECT 1 FROM EPMBuildRule br
                    WHERE br.ROLEA_BRANCH_ID = doc.BRANCH_IDENTIFIER
                  )


  // doesn’t have a describe (content) link
  // (If you dont create associations of type CONTENT, this condition is not needed.)   
  AND NOT EXISTS( SELECT 1 FROM EPMDescribeLink link
                    WHERE link.ROLEB_ID = doc.ID
                  )

As for drawings, every CAD drawing will have a calculated association to a WTPart provided every CAD part and assembly has an OWNER link to a WTPart or the “Auto Relate Drawings To Parts” preference includes the other types of associations such as content links. In which case, checking drawings would be unnecessary.

e.g: this is only for Library

Select name, documentnumber,statestate, (CONCAT(versionida2versioninfo, CONCAT('.' ,versionsortida2versioninfo))) as  FileVersion, lib.namecontainerinfo
from epmdocumentmaster docmaster join epmdocument doc on docmaster.ida2a2=doc.ida3masterreference
join  wtlibrary lib on doc.ida3containerreference=lib.ida2a2
where (docmaster.doctype like 'CADASSEMBLY' OR docmaster.doctype like 'CADCOMPONENT')


AND doc.latestiterationinfo=1


AND NOT EXISTS (Select 1 from EPMDocument laterversion
where laterversion.ida3masterreference = doc.ida3masterreference
AND (CONCAT(laterversion.versionida2versioninfo, CONCAT('.' ,laterversion.versionsortida2versioninfo)) > CONCAT(doc.versionida2versioninfo, CONCAT('.' ,doc.versionsortida2versioninfo)))
)


AND (doc.statecheckoutinfo != 'wrk-p' and doc.statecheckoutinfo !='wrk')


AND doc.oneoffversionida2oneoffversi IS NULL


AND NOT EXISTS (Select 1 from EPMBuildRule br
                where br.branchida3a5=doc.branchiditerationinfo
              )


AND NOT EXISTS (Select 1 from EPMDescribeLink link
where link.ida3b5=doc.ida2a2
)

Regards,

Bhushan