<Definition targetRelease="13.0.2600.5" createdOn="2020-01-01T12:00Z" createdBy="Active Sensing Inc" createdFor="Show Change Form Routing Test" decsription="Test whether the change can be Routed, and identify any problems.">
<dataquery parameterized="true" xmlns="http://www.plmx.org/DataQuery.xsd">
<choice>
<sqlquery>
<sql name="DataSet1"><![CDATA[
-- WARNING: DO NOT MODIFY THIS CODE. These queries try to Route the change, and then rollback the transaction.
DECLARE @RowCount int = 0;
BEGIN TRAN;
DISABLE TRIGGER ALL ON BasicItemRevision
UPDATE Change SET ChangeNum=(Select Max(ChangeNum)+1 FROM Change),
FileCommitNum=(Select ISNULL(Max(CommitNum),0) FROM RevisionPhysicalFile) WHERE Id=@P1
UPDATE R SET FromNum=C.ChangeNum,FileCommitNum=C.FileCommitNum
FROM AffectedItem A INNER JOIN
BasicItemRevision R ON A.Revision = R.Id
INNER JOIN Change C ON C.Id=A.ChangeAction
WHERE A.Action IN (1,3) AND A.ChangeAction = @P1;
UPDATE R SET ToNum=C.ChangeNum
FROM AffectedItem A INNER JOIN
BasicItemRevision R ON A.Revision = R.Id
INNER JOIN Change C ON C.Id=A.ChangeAction
WHERE (A.Action=2) AND A.ChangeAction = @P1
UPDATE P SET ToNum=C.ChangeNum
FROM AffectedItem A INNER JOIN
BasicItemRevision R ON A.Revision = R.Id
INNER JOIN BasicItemRevision P ON P.Id=R.PreviousRevision
INNER JOIN Change C ON C.Id=A.ChangeAction
WHERE A.Action=3 AND A.ChangeAction = @P1
UPDATE CI SET FromNum=Change.ChangeNum
FROM ChildItem CI
INNER JOIN AffectedItem A ON CI.ParentItem = A.Item AND CI.FromNum=2147483646
INNER JOIN Change ON Change.Id=A.ChangeAction
WHERE (A.Action IN (1,3)) AND A.ChangeAction = @P1
UPDATE CI SET ToNum=Change.ChangeNum
FROM ChildItem CI
INNER JOIN AffectedItem A ON CI.ToRev = A.Revision
INNER JOIN Change ON Change.Id=A.ChangeAction
WHERE (A.Action IN (1,3)) AND A.ChangeAction = @P1
UPDATE CI SET ToNum=Change.ChangeNum
FROM ChildItem CI
INNER JOIN AffectedItem A ON CI.ParentItem = A.Item AND CI.ToNum>=2147483646
INNER JOIN Change ON Change.Id=A.ChangeAction
WHERE A.Action=2 AND A.ChangeAction = @P1
SELECT
@RowCount = COUNT(*)
FROM
AffectedItem A
INNER JOIN Change C ON C.Id=A.ChangeAction
INNER JOIN ChildItem CI ON CI.ParentItem=A.Item AND CI.ToNum>C.ChangeNum
LEFT OUTER JOIN BasicItemRevision R ON R.ItemId=CI.BasicItem AND R.FromNum<=C.ChangeNum AND R.ToNum>C.ChangeNum
WHERE
A.ChangeAction=@P1 AND R.Id IS NULL AND A.Action IN (1,3)
SELECT
@RowCount = @RowCount + COUNT(*)
FROM
AffectedItem A
INNER JOIN Change C ON C.Id=A.ChangeAction
INNER JOIN ChildItem CI ON CI.BasicItem=A.Item AND CI.ToNum>C.ChangeNum
INNER JOIN BasicItemRevision R ON R.ItemId=CI.ParentItem AND R.ToNum>C.ChangeNum
WHERE
A.ChangeAction=@P1 AND A.Action=2
SELECT
*
FROM
(
-- Must release 'Item' because it appears on 'Parent' at 'Number'
SELECT
CAST(MC.LineNumber AS nvarchar) AS [Line]
,'Releasing ' +
CASE Parent.[Class]
WHEN 1 THEN 'part '
WHEN 2 THEN 'document '
END
+ Parent.[OwnerShort]
+ ' (' + Parent.[TypeShort] + ') '
+ Parent.Number AS [AffectedItem]
,'Release child ' +
CASE Child.[Class]
WHEN 1 THEN 'part '
WHEN 2 THEN 'document '
END
+ Child.[OwnerShort]
+ ' (' + Child.[TypeShort] + ') '
+ Child.Number
+ PrecursorChange AS [Do this]
,[BasicItem] AS [HK100]
,Parent.Number
+ ' uses unreleased '
+ Child.Number
+ CASE MC.[RelKind]
WHEN 0 THEN ' on BOM at Find '
WHEN 1 THEN ' on References list, Row '
WHEN 2 THEN ' on Sources list, Rank '
END
+ CAST(MC.Number AS varchar) AS [Because]
FROM
(
SELECT
A.LineNumber
,CI.ParentItem
,CI.BasicItem
,CI.RelKind
,CI.Number
,CASE
WHEN CPV.ItemId IS NULL THEN ''
ELSE ', now on ' + CPV.TypeShort + ' ' + CPV.Number + ', line ' + CAST(CPV.LineNumber AS varchar)
END AS PrecursorChange
FROM
AffectedItem A
INNER JOIN Change C ON C.Id=A.ChangeAction
INNER JOIN ChildItem CI ON CI.ParentItem=A.Item AND CI.ToNum>C.ChangeNum
LEFT OUTER JOIN BasicItemRevision R ON R.ItemId=CI.BasicItem AND R.FromNum<=C.ChangeNum AND R.ToNum>C.ChangeNum
LEFT OUTER JOIN viewer.ChangePairView CPV ON CPV.ItemID_Child = CI.BasicItem
AND CPV.Lifecycle IN (0,1,2,3,4,5,7,9)
WHERE
A.ChangeAction=@P1 AND R.Id IS NULL AND A.Action IN (1,3)
) MC
INNER JOIN MaterializedItem Parent ON Parent.ItemId = MC.ParentItem
INNER JOIN MaterializedItem Child ON Child.ItemId = MC.BasicItem
UNION ALL
-- Must cancel 'Parent' because 'Item' appears at 'Number' (Find/Rank/Order)
SELECT
CAST(MC.LineNumber AS nvarchar) AS [Line]
,'Canceling ' +
CASE Child.[Class]
WHEN 1 THEN 'part '
WHEN 2 THEN 'document '
END
+ Child.[OwnerShort]
+ ' (' + Child.[TypeShort] + ') '
+ Child.Number AS [AffectedItem]
,'Add pending iteration for '
+ Child.Number
+ ' or cancel/revise parent ' +
CASE Parent.[Class]
WHEN 1 THEN 'part '
WHEN 2 THEN 'document '
END
+ Parent.[OwnerShort]
+ ' (' + Parent.[TypeShort] + ') '
+ Parent.Number
+ PrecursorChange AS [Do this]
,[BasicItem] AS [HK100]
,'Uncanceled '
+ Parent.Number
+ ' shows '
+ Child.Number
+ CASE MC.[RelKind]
WHEN 0 THEN ' on BOM at Find '
WHEN 1 THEN ' on References list, Row '
WHEN 2 THEN ' on Sources list, Rank '
END
+ CAST(MC.Number AS varchar)
AS [Because]
FROM
(
SELECT
A.LineNumber
,CI.ParentItem
,CI.BasicItem
,CI.RelKind
,CI.Number
,CASE
WHEN CPV.ItemId IS NULL THEN ''
ELSE ', now on ' + CPV.TypeShort + ' ' + CPV.Number + ', line ' + CAST(CPV.LineNumber AS varchar)
END AS PrecursorChange
FROM
AffectedItem A
INNER JOIN Change C ON C.Id=A.ChangeAction
INNER JOIN ChildItem CI ON CI.BasicItem=A.Item AND CI.ToNum>C.ChangeNum
INNER JOIN BasicItemRevision R ON R.ItemId=CI.ParentItem AND R.ToNum>C.ChangeNum
LEFT OUTER JOIN viewer.ChangePairView CPV ON CPV.ItemID_Child = CI.ParentItem
AND CPV.Lifecycle IN (0,1,2,3,4,5,7,9)
WHERE
A.ChangeAction=@P1 AND A.Action=2
) MC
INNER JOIN MaterializedItem Parent ON Parent.ItemId = MC.ParentItem
INNER JOIN MaterializedItem Child ON Child.ItemId = MC.BasicItem
UNION ALL
SELECT
'' AS [Line]
,'All' AS [AffectedItem]
,'Change can be routed now' AS [Do this]
,NULL AS [HK100]
,'No routing errors found' AS [Because]
WHERE
@RowCount = 0
) RelCan
ORDER BY
[Line], [AffectedItem], [Do this], [Because]
ROLLBACK
]]></sql>
</sqlquery>
</choice>
</dataquery>
<Provider>DataGrid</Provider>
<settings fileSaveAs="{ItemTypeShort} {ItemNumber} Routing Audit {FileDatetime@yyyy-MM-dd hh-mm}" />
<context location="AffectedItemsList" change="true" />
</Definition>