=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