cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Did you get called away in the middle of writing a post? Don't worry you can find your unfinished post later in the Drafts section of your profile page. X

Query report on WTPart NOT Associated with EPMDocument

sshankaracharya
10-Marble

Query report on WTPart NOT Associated with EPMDocument

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

2 REPLIES 2

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.

Marco

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

Announcements


Top Tags