=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 ]]> DataGrid