Query report on WTPart NOT Associated with EPMDocument

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.

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