Getting Version of EPMDocument (Iteration,revision) in database

[srohde]

Dear community,

i need to find out the version (iteratio, revision) of a epm document in a sql query. Does anyone know, how iteration and revision are connected to empdocument table in the windchill database?

Any help is appreciated.

Thanks.

[Marco_Tosin]

Stefan take a look at this article from Technical Support https://support.ptc.com/appserver/cs/view/case_solution.jsp?n=CS131357&lang=en

If it's not mandatory for you to have your result through an sql query, you can read also this article I wrote here http://communities.ptc.com/docs/DOC-6348 where you can find a lot of things about reporting.

Maybe you can also subscribe to this group http://communities.ptc.com/groups/reporting and vote this two idea http://communities.ptc.com/ideas/4634 and http://communities.ptc.com/ideas/4659

Because of an error in the way link works on Community, you have to delete, in your browser, the part from left until "/" before http, to read the ideas.

Marco

[BenPerry · Accepted solution]

Revision is VERSIONIDA2VERSIONINFO

Iteration is ITERATIONIDA2ITERATIONINFO

Therefore

select VERSIONIDA2VERSIONINFO || '.' || ITERATIONIDA2ITERATIONINFO

will return you something like "A.1" (revision.iteration) if you're interested in including the . in the result.

Here is a sample query to get the filename in VAULTS of a EPMDoc

select
 mas.cadname cadname,
 doc.VERSIONIDA2VERSIONINFO || NVL2(doc.oneOffVersionIdA2oneOffVersi ,'-' || doc.oneOffVersionIdA2oneOffVersi,'') || '.' || doc.ITERATIONIDA2ITERATIONINFO ver,
 ad.modifystampa2 modified,
 ad.role role,
 M.path,
 to_char(fi.uniquesequencenumber,'0000000000000x') vault_filename,
 ad.filename,
 m.status,
 ad.filesize filesize_bytes
from
 holdertocontent hc,
 applicationdata ad,
 fvitem fi,
 EPMDocument doc,
 EPMDocumentmaster mas,
 FVMOUNT M
where
 hc.ida3b5 = ad.ida2a2
 and ad.ida3a5 = fi.ida2a2
 and hc.ida3a5=doc.ida2a2
 AND Fi.ida3a4= m.ida3a5
 --and ad.role='THUMBNAIL'
 and doc.ida3masterreference=mas.ida2a2
 --and mas.cadname in ('cadfile1.prt','cadfile1.drw')
 and mas.cadname like 'cadfile%.prt'
order by
 mas.cadname,
 ver,
 role

[srohde]

Thank you very much , thats exactly what I needed.