How to get Child Version & Iteration for a given Assembly through SQL

[Rocky]

Hi,

I am new to Windchill & trying to find out Child Version & Iteration information for the given Parent assembly through SQL. EPMMemberLink IDA3A5 points to IDA2A2 of EPMDocument which is Parent information. Since i have EPMDocument IDA2A2, i can easily get latest version & iteration of a Parent. However, for Child, EPMMemberLink stores EPMDocumentMaster IDA2A2 in IDA3B5, if my child Part has multiple version & iteration then quering EPMDocument based on IDA3MasterReference will fetch multiple records of different versions & iterations for a given component. I am stuck & not able to find a way to get child version & iteration information.

[RandyJones]

---

@Rocky wrote:

Hi,

I am new to Windchill & trying to find out Child Version & Iteration information for the given Parent assembly through SQL. EPMMemberLink IDA3A5 points to IDA2A2 of EPMDocument which is Parent information. Since i have EPMDocument IDA2A2, i can easily get latest version & iteration of a Parent. However, for Child, EPMMemberLink stores EPMDocumentMaster IDA2A2 in IDA3B5, if my child Part has multiple version & iteration then quering EPMDocument based on IDA3MasterReference will fetch multiple records of different versions & iterations for a given component. I am stuck & not able to find a way to get child version & iteration information.

---

In the epmdocument table there is EPMAsStoredConfig ida2a2. This is in column ida3c10. You use this and look for rows in the EpmAsStoredMember table in which it's ida3a5 column equals epmdocument.ida3c10. The EpmAsStoredMember has the EPMDocument ida2a2 in it.

[Rocky]

Hi Randy,

Thanks for your reply. But from EPMMemberlink table we get Ida2a2 of document master which gives multiple version & iteration when queried in epmdocument against ida3masterreference. Now which is the child version & iteration used for the given parent is what we are not able to find.

Can you provide a sample sql code joining epmmemberlink table with others to find the child version & iteration?

Thanks & Regards,

Rocky

[bkontham]

Hi Rocky,

I'm not sure about SQL queries, but you can get child componets for a given Assembly by using a Query Builder Report. Refer the following article.

https://www.ptc.com/en/support/article?n=CS72503 

Thanks,

Bhagirath

[TomU]

This isn't an exact match, but maybe it will give you some ideas.  I built this to locate the drawings that were referencing a specific format.

SELECT
	edm_frm.CADName AS [Format_Name],
	edm.CADName AS [Drawing_Name],
	ed.statestate AS [State],
	ed.versionIdA2versionInfo AS [Rev],
	ed.iterationIdA2iterationInfo AS [Iter]
FROM
	EPMReferenceLink AS [rl]
	JOIN EPMDocumentMaster AS [edm_frm]
		ON rl.idA3B5 = edm_frm.idA2A2
	JOIN EPMDocument AS [ed]
		ON rl.idA3A5 = ed.idA2A2
	JOIN EPMDocumentMaster AS [edm]
		ON ed.idA3masterReference = edm.idA2A2
		AND ed.branchIditerationInfo = 
			(
				SELECT max(branchIditerationInfo)
				FROM EPMDocument
				WHERE idA3masterReference = edm.idA2A2
					AND edm.idA2A2 = ed.idA3masterReference
					AND latestiterationInfo = 1
					AND statecheckoutInfo = 'c/i'
			)
WHERE
	rl.referenceType = 'DRAWING_FORMAT'
	AND ed.latestiterationInfo = 1
	AND edm_frm.CADName LIKE 'part_e_landscape.frm'  -- set this equal to the format interested in.  Comment out entirely to see all formats.
ORDER BY
	edm_frm.CADName,
	edm.CADName

Here's another one that shows the models being referenced by a specifc drawing:

SELECT DISTINCT TOP(60) 
       edm.CADName AS [CAD_Name],
       ed.statestate AS [State],
       ed.versionIdA2versionInfo AS [Rev],
       ed.iterationIdA2iterationInfo AS [Iter],
	   ed.branchIditerationInfo,
       f_edm.CADName AS [CAD_Name_2],
       f_ed.statestate AS [State_2],
       f_ed.versionIdA2versionInfo AS [Rev_2],
       f_ed.iterationIdA2iterationInfo AS [Iter_2],
	   f_ed.branchIditerationInfo

       FROM EPMDocumentMaster AS [edm]
       JOIN EPMDocument AS ed
         ON edm.idA2A2 = ed.idA3masterReference
		 AND ed.branchIditerationInfo = 
            (
                SELECT max(branchIditerationInfo)
                FROM EPMDocument
                
                WHERE idA3masterReference = edm.idA2A2
                     AND edm.idA2A2 = ed.idA3masterReference
                     AND edm.CADName LIKE @DocName
                     AND latestiterationInfo = 1
			)  

         JOIN  EPMAsStoredMember AS eas
              ON  eas.idA3A5 = ed.idA3C10
       
         JOIN  EPMDocument AS f_ed
              ON     f_ed.idA2A2 = eas.idA3B5
          
         JOIN  EPMDocumentMaster AS f_edm
              ON     f_edm.idA2A2 = f_ed.idA3masterReference
              

WHERE
       edm.CADName LIKE @DocName
       AND ed.latestiterationInfo = 1
       --AND ed.statestate = 'RELEASED'
       AND edm.CADName != f_edm.CADName
       AND    LEFT(edm.CADName, (CHARINDEX('.',edm.CADName,1) - 1)) = LEFT(f_edm.CADName, (CHARINDEX('.',f_edm.CADName,1) - 1))

ORDER BY edm.CADName

[RandyJones]

---

@Rocky wrote:

Hi Randy,

Thanks for your reply. But from EPMMemberlink table we get Ida2a2 of document master which gives multiple version & iteration when queried in epmdocument against ida3masterreference. Now which is the child version & iteration used for the given parent is what we are not able to find.

That is why you need to use the EpmAsStoredMember table to get the as-stored epmdocument instead of the epmdocumentmaster like I said in my previous post.

Can you provide a sample sql code joining epmmemberlink table with others to find the child version & iteration?

Thanks & Regards,

Rocky

---

Tom has supplied some examples that show how to find the latest version. Take a look at his 2nd sample which shows using the EpmAsStoredMember table.

Also take a look here:

https://www.ptc.com/en/support/article?n=CS34307
and here

https://www.ptc.com/en/support/article?n=CS237842

Note in the CS237842 article PTC is selecting the max of versionsortida2versioninfo instead of branchIditerationInfo.

[Rocky]

Thank you all for your support & replies.. We have got the solution of finding respective child precise versions & iterations in context of the given assembly. Firstly, we joined EPMDocument, EPMDocumentMaster & EPMAsStoredMember & extracted all versions & iterations based on the IDA3B5 & Doc IDA2A2. Then we joined them to EPMMemberLink, EPMDocument & EPMDocumentMaster based on IDA3C10 & CADName. We got correct results. Thanks again for your help..