Extract partnumber, revision and description

[JeffreyRonay]

We run Windchill using PRO E as our cad system. As the site developer, I need to extract the revision and description for any drawings that have been revised. Engineering would like me to pass these values to our MRP system, Syteline.

Is there a wa(API call / sql query) to extract these 3 fields from windchill for updated or new drawings in pro e.?

Thanks,

Jeff

[DmitryTsarev]

Have you considered using QueryBuilder (possibly with Cognos) for this? Also, what WIndchill version are you using?

[JeffreyRonay]

How do I access QueryBuiilder?Windchill version is 9.1

[DmitryTsarev]

Site - Utilities - Report Manager

Check the "Windchill Customizer's Guide" (chapter "Report Generation") and "Windchill Business Administrator's Guide" (chapter "Reporting") for more details

[PatrickLee]

What database platform are you using? If you are using MS SQL, and if you have access to the database I can give yuo a query that will help you.

- Patrick

[JeffreyRonay]

Hi Patrick,

yes we are using mssql. query would be much appreciated.

Thanks,

Jeff

[PatrickLee]

Jeff,

I am almost done with this SQL script. I had to make some slight adjustments to my original script. Hang with me just a bit longer and I will get you exactly what you need and probably then some.

- Patrick

[JeffreyRonay]

Awesome Patrick and Thank You!

Jeff

[PatrickLee]

Jeff

Try this SQL to make sure it will return the basic info you are looking for:

select * from [yourdatabaseSCHEME].EPMDocumentMaster where documentNumber = 'DOCUMENT_NUMBER'

I am having issues joining this table to teh EPMDocument table to retrieve the correct record to get you right revision number and so forth.

This is a starting point. I was SURE I had this script, but I can't seem to find it. I am working on a article that will be posted on a MS blog site for MS SQL 2008 that is a walk through on how reverse engineer the URL strings of the application to help you locate the data in the database.

- Patrick

[jkent]

I use this in SQL Server to get CAD Doc info.

SELECT

EPMDocumentMaster.CADName

"CADName"

,EPMDocument.versionIdA2versionInfo

"Revision"

,EPMDocument.iterationIdA2iterationInfo

"Iteration"

,EPMDocument.statestate

"State"

,PDMLinkProduct.namecontainerInfo

"Product"

,SubFolder.name

"Folder"

,

CASE LEN(EPMDocument.versionIdA2versionInfo)

WHEN 1 THEN ASCII(EPMDocument.versionIdA2versionInfo) -64

WHEN 2 THEN ((ASCII(SUBSTRING(EPMDocument.versionIdA2versionInfo, 1, 1))-64)*26) + (ASCII(SUBSTRING(EPMDocument.versionIdA2versionInfo,2,1))-64)

END VersionLetterInt

FROM

EPMDocumentMaster

,EPMDocument

,PDMLinkProduct

,SubFolder

WHERE

EPMDocumentMaster.CADName =

'model_program_new.prt'

AND EPMDocument.idA3masterReference = EPMDocumentMaster.idA2A2

AND EPMDocument.idA3containerReference = PDMLinkProduct.idA2A2

AND EPMDocument.idA3B2folderingInfo = SubFolder.idA2A2

ORDER BY

VersionLetterInt

ASC, Cast(EPMDocument.iterationIdA2iterationInfo AS INTEGER) ASC

[JeffreyRonay]

Awesome Joe

I appreciate the script. There is just one column missing...for description, I need the StringValue.value colum and can't seem to find the correct key to relate back to EPMDocument table. Any thought on this?

[jkent]

Is description a pro e parameter? Not sure what you mean by this.

[JeffreyRonay]

I believe the pro e parameter is Title. I have found the values I am looking for in the windchill.StringValue table in the column called value.