Single-Level View Version Conversion Deprecation and warning

[avillanueva]

Blast from the past has kicked me in the "ast".

Glad I am reading 12.1.2 What's new. So, I've had my system running since Windchill 7. At that time view versions used a multi-level version schema (A.A, A.B, etc). While I've known about the new default for single level and this utility for sometime, it was never on my near term to-do list to convert. I am now jumping to version 13.0.2 so am I hosed? I know deprecated does not mean gone but if I have to do this before I upgrade, I need to get it done now.

https://www.ptc.com/en/support/article/CS414044?source=search

Just search and found this. Ok, not hosed but let's run a test on my test server. The process was very straight forward, documented here: Running the SingleLevelViewVersionConversionUtility Command Line Utility It generates scripts by context (Project, Product, Library and Org) where you can convert all parts versions with multi-level view versions to single-level. What was B.- should become B.  Process took about 45 mins to run all areas and convert 33K versions, zero errors but I recommend running the WinDU scripts InvalidSeriesValues, RevisionLabelCleanserDT, SeriesSortDiagnosticTask and RevisionSequenceCleanserDT just to make sure you are error free first. 

Here is the real kick in the teeth. We have a number of Part versions that were loaded in via imports that did not start with our first version letter of - (Dash). Say I started life at version E and created a new view version off that which became E.-. Same would follow when I revised to F, becoming F.- for the Manufacturing view. Here is an example of what I expected and what happened:

Number	versionInfo.identifier.versionId	Expected New Version	Resulting Version
PartXYZ	E.-	E	-
PartXYZ	F.-	F	A
PartXYZ	G.-	G	B
PartXYZ	H.-	H	C

I understand why it did what it did and some of the complexities that had to be handled (What if I had E.A and E.B existing?) but boy did this create a mess. Glad it was on Test server and not production. Still, 26% of the revisions are not what I would consider correct (where version of Manufacturing view matches version of Design view it was derived from). I am not expecting that someone has a ready made solution for this legacy issue but if you do, please reply. I expect to be writing a large SQL query to clean this all up and be ready to do this for my production system when I convert it. Happy Thanksgiving all.

[HelesicPetr]

Hi @avillanueva 

Do you think that direct change in database can solve your issue?

I did statebase versioning change from alphabet revision to number revision. 

I know it is not supported but it is possible to change existing data from one statebase versioning schema to another. 

If the sql script and a steps how to process would help, I will send it you. 

PetrH

[avillanueva]

Just getting back from vacation. Yes, I think the solution is a direct DB change. If you can send the SQL, it would help as I want to make sure this is "correct".  I do not think that I am alone in how I see versioning working. We do use view version but see versions locked and synced (one version for the part). The way this seems to work allows Design view and Mfg view to differ and depart from one another. I would like to prevent that and have ideas as to how. 

[HelesicPetr]

Hi @avillanueva 

Here is the SQL to update existing revision schema.

versionIdA2versionInfo is the revision character that you need to change and you have to repeat this update for all characters one by one. 

versionSortIdA2versionInfo is a sort ID where is saved the order of the characters. 

I set it versionSortIdA2versionInfo  to a "sortId" string because after this update I recreate the sortId by RevisionLabelCleanser 

UPDATE
    [wcuser].[wcuser].[EPMDocument]
SET
    [wcuser].[wcuser].[EPMDocument].versionIdA2versionInfo = '1',
    [wcuser].[wcuser].[EPMDocument].versionSortIdA2versionInfo = sortId
FROM
    [wcuser].[wcuser].[SeriesSortValue] AS SeriesSortValueTable
    INNER JOIN [wcuser].[wcuser].[EPMDocument] AS EPMDocumentTable
        ON SeriesSortValueTable.[value]='1' AND SeriesSortValueTable.uniqueSeriesName= 'wt.series.HarvardSeries.StateBased'

WHERE EPMDocumentTable.versionIdA2versionInfo = 'A';

after all database updates run a Windchill shell function

java wt.vc.RevisionLabelCleanser -update

this correct all sortId values. 

also here is a sql to change the schema from HarvardSeries to StateBased

update wcuser.wcuser.EPMDocumentMaster
SET series='wt.series.HarvardSeries.StateBased'
where series='wt.series.HarvardSeries'

Hope this can help

PetrH

[avillanueva]

As an aside, we use Milspec versioning so I have used this script to correct sortIDs in the past:

UPDATE EPMDOCUMENT D
SET D.VERSIONSORTIDA2VERSIONINFO = (SELECT S2.SORTID FROM SERIESSORTVALUE S2
                                    WHERE S2.UNIQUESERIESNAME = 'wt.series.HarvardSeries.MilSpec' AND
                                          D.VERSIONIDA2VERSIONINFO = S2.VALUE)
WHERE
D.IDA2A2 IN (SELECT  P.IDA2A2
FROM EPMDOCUMENT P, EPMDOCUMENTMASTER M, SERIESSORTVALUE S
WHERE
P.IDA3MASTERREFERENCE = M.IDA2A2 AND
S.UNIQUESERIESNAME = 'wt.series.HarvardSeries.MilSpec' AND
P.VERSIONIDA2VERSIONINFO = S.VALUE AND
NOT (P.VERSIONSORTIDA2VERSIONINFO = S.SORTID));

[HelesicPetr]

Hi @avillanueva 

Thank you for the script. It is good to know. 

also keep in mind that there is a trouble, if the SeriesSortValue table does not contain the information 😄

So the RevisionLabelCleanser solves the sortId information. 

PetrH

[avillanueva]

I wrote this script to help identify the "errors" as I see it. Let me know if this is accurate. 

SELECT P.IDA2A2 "PartID", m.wtpartnumber "Part Number", C.IDA2A2 "BranchID", p.versionida2versioninfo "Part Rev", P.IDA3View "Part View",C.VIEWID "BRANCH VIEWID" ,C.VERSIONID "Branch Rev" , C2.VERSIONID "Predecessor Branch Rev",
C2.VIEWID "PRED VIEWID"
FROM WTPART P, WTPARTMASTER M, CONTROLBRANCH C, CONTROLBRANCH C2
WHERE
p.ida3masterreference = M.IDA2A2 AND
p.ida3view = 1447 AND
C.IDA2A2 = P.BRANCHIDITERATIONINFO AND
C2.IDA2A2 = C.IDA3A5 AND
NOT(C.VERSIONID = C2.VERSIONID)

[avillanueva]

Well, it would have worked but I am learning more about what this conversion script did. Here is a result for a certain part number:

I was curious why PRED VIEWID was 1447 since this is what I get from an unconverted DB:

They changed the predecessor branches to be sequential from the previous revision of the Manufacturing View! In my current system, we used "New View Version" which should have the predecessor version by the Design View, so E is predecessor to E.- and G is predecessor to G.-. What it appears like now is that - (MFG) predecessor was E (Design), (Correct) and A (MFG) predecessor is - (MFG) as if I did a revise. That is not technically correct. Note the PRED ViewIDs were changed also. They also made changes in the CONTROLBRANCH table to IDA3C5 so that data is unreliable now as well. I can see however that the History table shows correct information so I think my information must reside in the WTPart table. The fact that these tables are not inconsistent with each other worries me for integrity or things link deletion and purging. 

[avillanueva · Accepted solution]

Ok this query worked better.

SELECT P3.IDA2A2 "PART ID", P3.BRANCHIDITERATIONINFO "BRANCH", m3.wtpartnumber "Part Number", p3.versionida2versioninfo "Part Rev", p3.iterationida2iterationinfo "Part Iteration",P3.IDA3View "Part View", pred."Predecessor Rev" "Correct Rev"
FROM WTPART P3, WTPARTMASTER M3,
(SELECT P.BRANCHIDITERATIONINFO  "BRANCH", m.wtpartnumber "Part Number", p.versionida2versioninfo "Part Rev", P.IDA3View "Part View",P.IDA3E2ITERATIONINFO "PRED ID", p2.versionida2versioninfo "Predecessor Rev", p2.iterationida2iterationinfo "Predecessor Iteration", P2.IDA3View "PRED VIEWID" 
FROM WTPART P, WTPARTMASTER M, WTPART P2
WHERE
p.ida3masterreference = M.IDA2A2 AND
p.ida3view = 1447 AND
P2.IDA2A2 = P.IDA3E2ITERATIONINFO AND
p2.ida3view = 1445 AND
NOT(p.versionida2versioninfo = p2.versionida2versioninfo)) PRED
WHERE
p3.ida3masterreference = M3.IDA2A2 AND
p3.ida3view = 1447 AND
P3.BRANCHIDITERATIONINFO = PRED.BRANCH

 I have to take a serious step back but it looks like they left IDA3E2ITERATIONINFO the same which is the true iteration the Manufacturing view was branched off of. Should be easy enough to correct the letter and sortid. Also need to correct the CONTROLBRANCH table but more investigation needed.

[Marco_Tosin]

We set up our own new test servers with the same version of WNC currently in use (12.0.2) to check conversion times, and after three or four iterations due to some exceptions to be handled, we had the results after 6 hours.

We do not have a purely literal revision scheme, like the example you gave, but an alphanumeric one (e.g., A1.1 for WTPart and EPMDocument or 0.1 for WTDocument ) and we noticed that, if in the three-level scheme only the last one (which corresponds to the plant view) is changed, the version resulting from the conversion sums the values.

Example: starting with A1.A0.A0 (Design.Mfg.Plant) and revising only Plant becomes A1.A0.A1, in the new schema the conversion sums Design+Plant and thus becomes A2.