SQL script to return filevault filename for the latest version of a cad file.

[colmacpro]

Is there any way to find the filevault location and filename for the latest version of a cad filename?

Joe

[MikeLockwood]

See attached query builder report for a WTDoc's.
Can build similar for EPM Doc's.

[colmacpro]

Is there any way to do it from SQLPlus.

[bmills]

Designation: Non-Finmeccanica

Joe,

It sounds like you might have Intralink 9.x and don't have easy access
to the query builder functionality. I have 2 SQLPlus scripts that PTC
tech support helped me with and they might be helpful. One just reports
EPMDoc info and the other lists vault information. I've attached the
scripts.



Bob Mills

Engineering Software Administrator

DRS Integrated Defense Systems and Services

DRS Technologies, Inc

645 Anchors Street

Fort Walton Beach, Florida 32548

850-302-3241

- www.drs.com

[colmacpro]

Thank you everybody for the responses. You guys have given me plenty to work with.

joe

[ejhlti]

Has anyone out there used a sqlplus query to locate either of the following ?

1) The latest iteration of the last revision.

2) All iterations except for the last iteration of the last revision.

[crosato]

Ed,

You should be able to grab latest version based off the VERSIONSORTIDA2VERSIONINFO column. That's a varchar2, but you can grab those that are equal to '000001' (at least that's the pattern here, I'm assuming it's OOTB) - and it should return the latest rev

Then, there's a column called LATESTITERATIONINFO, that'll get you the latest/not latest iteration. It's a 0/1 number field.

Chris

[RandyJones]

On 11/21/12 13:02, Ed Howanice wrote:
>
> Has anyone out there used a sqlplus query to locate either of the following ?
>
> 1) The latest iteration of the last revision.
>

For latest iteration of the latest revision I use this:

select * from
(
select ed.ida2a2, edm.cadname NAME, ed.statestate RELEASE,
ed.versionida2versioninfo REVISION,
ed.iterationida2iterationinfo ITERATION,
cast(ed.createstampa2 as timestamp) \"CREATED\",
cast(ed.modifystampa2 as timestamp) \"MODIFIED\",
cast(ed.updatestampa2 as timestamp) \"UPDATED\",
ed.ida3b10
from
epmdocumentmaster edm, epmdocument ed
where
edm.cadname = '<yourepmdocumentnamegoeshere>'
and ed.ida3masterreference = edm.ida2a2
and ed.latestiterationinfo = 1
and ed.statecheckoutinfo != 'wrk'
order by \"MODIFIED\" desc
)
where rownum=1;

> 2) All iterations except for the last iteration of the last revision.
>
>
> -----End Original Message-----


--
------------------------------------------------------------------------
Randy Jones
Systems Administrator
Great Plains Mfg., Inc.
1525 E North St
PO Box 5060
Salina, KS USA 67401
email: -
Phone: 785-823-3276
Fax: 785-667-2695
------------------------------------------------------------------------

[ejhlti]

Randy and Chris - thanks for the tips, Im getting closer but still not there yet

I have a sqlplus query that I need to locate all iterations except for the lastiteration of the last rev.

Thinking of using a MAX function for the versionsortida2versioninfo but can quite get the syntax correct.

Any ideas of how to modify the code below to find all but the last iteration of the last rev ?

select
epmdocumentmaster.name,
epmdocument.statestate,
epmdocument.latestiterationinfo,
epmdocument.versionida2versioninfo,
epmdocument.iterationida2iterationinfo,
epmdocument.IDA2A2,
epmdocumentmaster.IDA2A2,
epmdocument.IDA3MASTERREFERENCE,
epmdocument.VERSIONSORTIDA2VERSIONINFO "VSORT"
from
epmdocument,epmdocumentmaster
where
epmdocumentmaster.ida2a2=epmdocument.ida3masterreference
and
epmdocumentmaster.name like '999-%.drw'
/* epmdocument.latestiterationinfo=0 */
order by epmdocument.ida2a2
;
exit

[bellj]

Yes, MAX is useful.
Here is how I do it:

select * from (
select epm.ida2a2
,epm.documentnumber
,epm.name
,epm.version
,decode(decode(epm.revisionsort, epm.max_revisionsort, 1, 0)
,1, decode(epm.latestiterationinfo, 1, 'Yes', 'No')
,'No')
latest_version
,to_number(epm.revisionsort || regexp_substr(epm.version, '[[:digit:]]+'))
version_sort
from (select e.ida3d2iterationinfo
,e.ida2a2
,em.documentnumber
,em.name
,e.versionsortida2versioninfo revisionsort
,(select max(versionsortida2versioninfo)
from epmdocument mr
where mr.ida3masterreference = e.ida3masterreference)
max_revisionsort
, e.versionida2versioninfo
|| '.'
|| e.iterationida2iterationinfo
version
,e.latestiterationinfo
,e.statestate
from epmdocument e
inner join epmdocumentmaster em
on e.ida3masterreference = em.ida2a2) epm)
where latest_version = 'Yes'
and name like '999-%.drw'
order by ida2a2

joe bell
CM Application Administrator

[colmacpro]

From the responses that I recieved I bulit the SQL script below to create a text file to include with my backups.

The problem is that there are no family table generics included in the query results. What seems to be weird is that the instances of the family table models have file associated with them.

Does anybody know what is wrong with the query?

Joe

The following is the output from the custom SQL

MyAssembly_1.asm|1185522|A|2|d:\ptc\FILEVAULT\filevault_Folder_24\000000001216f2

MyPart_1.prt|1185396|A|2|d:\ptc\FILEVAULT\filevault_Folder_24\00000000121674

MyPart_1.prt|947365|A|1|d:\ptc\FILEVAULT\filevault_Folder_19\000000000e74a5

MyPart_2.prt|947364|A|1|d:\ptc\FILEVAULT\filevault_Folder_19\000000000e74a4

MyPart_3.prt|947363|A|1|d:\ptc\FILEVAULT\filevault_Folder_19\000000000e74a3