Find all EPM Documents without Visualisation

[srohde]

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

[BineshKumar1 · Accepted solution]

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

[TomU]

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!

[BineshKumar1]

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

[TomU]

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).

[srohde]

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

[BillRyan]

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.

[sm]

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

[TomU]

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