Skip to main content
S
srohde1-Visitor
1-Visitor
October 21, 2014
Solved

Getting Version of EPMDocument (Iteration,revision) in database

  • October 21, 2014
  • 2 replies
  • 2406 views

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.

Best answer by BenPerry

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

2 replies

Marco Tosin
21-Topaz I
Marco Tosin21-Topaz I
21-Topaz I
October 21, 2014

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

Marco
B
BenPerry15-MoonstoneAnswer
15-Moonstone
October 21, 2014

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
    S
    srohde1-VisitorAuthor
    1-Visitor
    October 22, 2014

    Thank you very much , thats exactly what I needed.

    Terms & ConditionsCookie settingsAccessibility statement