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

Find all EPM Documents without Visualisation

SOLVED
Highlighted
Level 1

Find all EPM Documents without Visualisation

Hi Community,

im trying to figure out a way to find all EPMDocument without a created Visualisation. Which Database Fields hold the information about wheather a visualisation is created or not?

Regards

Stefan

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Find all EPM Documents without Visualisation

Hello Stefan,

If you are using report manager, you can link epmdocument and wtderivedimage using "Derived From The Derived Image"  link and reference epmdocument to epmdocument master to get the name and number.

If you are looking at SQLs you can use the below to get all representations.

SELECT edm.documentnumber, edm.name, edm.cadname, ed.versionida2versioninfo "Rev",

ed.iterationida2iterationinfo "Iteration", di.NAME

  FROM epmdocument ed, epmdocumentmaster edm, derivedimage di

  WHERE  ed.ida3masterreference =edm.ida2a2

  AND ed.ida2a2 IN di.ida3therepresentablereferenc;

Thank you,

Binesh Kumar

Barry Wehmiller

8 REPLIES 8

Re: Find all EPM Documents without Visualisation

Hello Stefan,

If you are using report manager, you can link epmdocument and wtderivedimage using "Derived From The Derived Image"  link and reference epmdocument to epmdocument master to get the name and number.

If you are looking at SQLs you can use the below to get all representations.

SELECT edm.documentnumber, edm.name, edm.cadname, ed.versionida2versioninfo "Rev",

ed.iterationida2iterationinfo "Iteration", di.NAME

  FROM epmdocument ed, epmdocumentmaster edm, derivedimage di

  WHERE  ed.ida3masterreference =edm.ida2a2

  AND ed.ida2a2 IN di.ida3therepresentablereferenc;

Thank you,

Binesh Kumar

Barry Wehmiller

Re: Find all EPM Documents without Visualisation

Binesh Kumar‌,

To get this to run on my system, I had to fix the case, add the database name to each of the tables, and add parenthesis to the 'IN' clause.  This is on Microsoft SQL 2012 (with case sensitive collation.)

SELECT edm.documentNumber, edm.name, edm.CADName, ed.versionIdA2versionInfo "Rev",

ed.iterationIdA2iterationInfo "Iteration", di.name

FROM windchill.EPMDocument ed, windchill.EPMDocumentMaster edm, windchill.DerivedImage di

WHERE ed.idA3masterReference = edm.idA2A2 AND ed.idA2A2 IN (di.idA3theRepresentableReferenc);

Can you tell me what table(s) I need to link this to in order to see any related additional files and their names?  I'm interested in determining not just if an object was published, but whether or not the proper additional files were published as well.  Thanks!

Re: Find all EPM Documents without Visualisation

Thank you very much.

We came up with this query.

Select CADName,VERSIONIDA2VERSIONINFO, DERIVEDIMAGE.IDA3THEREPRESENTABLEREFERENC from EPMDOCUMENTMASTER, EPMDOCUMENT LEFT OUTER JOIN DERIVEDIMAGE on DERIVEDIMAGE.IDA3THEREPRESENTABLEREFERENC=EPMDOCUMENT.IDA2A2 where "EPMDOCUMENT".idA3masterReference = "EPMDOCUMENTMASTER".idA2A2 and "EPMDOCUMENT".statestate='RELEASED' and EPMDOCUMENTMASTER.CADNAME like '%.drw' and DERIVEDIMAGE.IDA3THEREPRESENTABLEREFERENC is null;

to find all released *.drw files without visualisation.

Thank you for your input

Re: Find all EPM Documents without Visualisation

Hi, could you do this by generating the log file under Site, Utilities, WVS Job Scheduler Administration?

wvsjobsched.jpg

Re: Find all EPM Documents without Visualisation

Scott Morris‌, the scheduler can't check for state (Released), and least not without building a custom schedule (which requires Java coding).

Re: Find all EPM Documents without Visualisation

Hello Tom,

Sorry for the delayed response. Is this what you are looking for?

SELECT edm.documentnumber, edm.name, edm.cadname, ed.versionida2versioninfo "Rev",  

ed.iterationida2iterationinfo "Iteration", di.NAME ,ad.filename, ad.role

  FROM epmdocument ed, epmdocumentmaster edm, derivedimage di,APPLICATIONDATA ad, HOLDERTOCONTENT htc  

  WHERE  ed.ida3masterreference =edm.ida2a2 

  AND ed.ida2a2 IN di.ida3therepresentablereferenc and htc.ida3b5 = ad.ida2a2 AND htc.ida3a5 = di.ida2a2 and ad.ROLE='ADDITIONAL_FILES'; 

  /* Use ad.Role type PRODUCT_VIEW_* for Creo view, THUMBNAIL* from Thumbnails and ADDITIONAL_FILEs for Additional file types */

Thank you,

Binesh Kumar

Barry Wehmiller

Re: Find all EPM Documents without Visualisation

Thanks Binesh Kumar!  I've actually spent the last several days working on this.  It took quite a while to locate the "Holder To Content" table.  I wish I would have seen that sooner.    Oh well.  Here's what I've come up with so far.

In order to find documents that are missing either their representations or were published without additional files, I had to do multiple LEFT JOINs.  To further complicate this we have a parameter that may exist in the CAD objects that is used to control publishing additional files (EXPORT_DATA yes/no).


This is just for drawings at the moment:

SELECT

  edm.CADName "CAD Name",

  ed.statestate "State",

  ed.versionIdA2versionInfo "Rev",

  ed.iterationIdA2iterationInfo "Iter",

  ss_di.[Publish Name],

  ss_di.[Publish Description],

  ss_di.[File Type],

  ss_di.[Additional Files Name],

  ss_export.EXPORT_DATA

FROM

  windchill.EPMDocumentMaster edm

  JOIN windchill.EPMDocument ed

  ON edm.idA2A2 = ed.idA3masterReference

  LEFT JOIN

  (

  SELECT

  ss_ad.[File Type],

  ss_ad.[Additional Files Name],

  ss_ad.idA3A5,

  di.idA3theRepresentableReferenc "EPM idA2A2",

  di.name "Publish Name",

  di.description "Publish Description"

  FROM

  windchill.DerivedImage di

  LEFT JOIN

  (

  SELECT

  ad.fileName "Additional Files Name",

  ad.role "File Type",

  htc.idA3A5

  FROM

  windchill.HolderToContent htc,

  windchill.ApplicationData ad

  WHERE

  htc.idA3B5 = ad.idA2A2

  AND ad.role = 'ADDITIONAL_FILES'

  ) ss_ad

  ON di.idA2A2 = ss_ad.idA3A5

  WHERE

  di.defaultRepresentation = '1'

  ) ss_di

  ON ed.idA2A2 = ss_di.[EPM idA2A2]

  LEFT JOIN

  (

  SELECT

  bv.idA3A4 "EPM A2",

  bv.value "EXPORT_DATA"

  FROM

  windchill.BooleanValue bv,

  windchill.BooleanDefinition bd,

  windchill.LWCIBAAttDefinition iba

  WHERE

  bv.idA3A6 = bd.idA2A2

  AND bd.idA2A2 = iba.idA3A7

  AND iba.name = 'EXPORT_DATA'

  ) ss_export

  ON ed.idA2A2 = ss_export.[EPM A2]

WHERE

  edm.docType = 'CADDRAWING'

  AND ed.latestiterationInfo = '1'

  AND ed.statestate = 'RELEASED'

  AND (

  ss_di.[Publish Name] IS NULL

  OR ss_di.[Additional Files Name] IS NULL

  )

  AND

  (

  ss_export.EXPORT_DATA = 1

  OR ss_export.EXPORT_DATA IS NULL

  )

ORDER BY

  edm.CADName,

  ed.versionIdA2versionInfo,

  ed.iterationIdA2iterationInfo,

  ss_di.[Publish Description],

  ss_di.[File Type];

I've also attached the SQL query itself (the formatting looks much better).

Re: Find all EPM Documents without Visualisation

Sample QML - Find-EMPDocuments_without_visualization - QML similar Stefan's SQL that searches based on File Name.

Also Attached QML - EPMDocument-Find-Missing-Representations.  This one is intended to Compliment https://support.ptc.com/appserver/cs/view/case_solution.jsp?n=CS211115 for having a CSV that can be sent to the publisher for generation of lightweight images.  It can find missing representations based on Context, File Name and Date or any Combination of the three.  The three columns needed for the input into the publisher are also included in the output.  The column for finding out of date representations is also included...but would need some additional customization.  It can also be used to find all the CAD documents checked in for the month that failed in the publisher.