Table2
[System Rules...]: [References Tabs] > [Parts] > Mark the [References accept parts] option';
DECLARE @SETACCEPTDOCS nvarchar(100) = '[Tools] menu > [System Rules...]: [References Tabs] > [Parts] > Mark the [References accept documents] option';
DECLARE @ACCEPTSPARTS bit = 'false';
DECLARE @ACCEPTSDOCS bit = 'false';
SELECT TOP 1 @ACCEPTSDOCS = [PartRefAcceptDocuments], @ACCEPTSPARTS=[PartRefAcceptParts] FROM SystemRules_View;
-- program constants
DECLARE @N int = 2147483646;
DECLARE @MAXDEPTH int = 20;
DECLARE @RELEASESETINFO nvarchar(200) = 'Add this item (and its References) to new change form. Delete this item (only) from change form. Remove this item (only) from database. Release change form.';
-- Number of Release Sets (and change forms) based on total items
DECLARE @Set int;
SELECT @Set = CAST((COUNT(*) / @MAXSIZE) AS int) FROM [PDXpertDB].[dbo].[BasicItem_View];
WITH OrphanItem AS -- has neither parent nor child
(
SELECT DISTINCT
Itm.ItemId
,ROW_NUMBER() OVER (ORDER BY Itm.[IsHomeItem], Itm.[Class], Itm.[Owner], Itm.[Type], Itm.[Number]) AS [ImportOrder]
FROM
[PDXpertDB].[viewer].[ItemView] Itm
LEFT JOIN [PDXpertDB].[dbo].[ChildItem_View] Child ON Child.BasicItem=Itm.ItemId
LEFT JOIN [PDXpertDB].[dbo].[ChildItem_View] Parent ON Parent.ParentItem=Itm.ItemId
WHERE
Child.ParentItem IS NULL AND Parent.BasicItem IS NULL AND Itm.RevID IS NULL
)
, RootItem AS -- has children, no parents
(
SELECT
ItemId
,ROW_NUMBER() OVER (ORDER BY [Class], [Owner], [Type], [Number]) AS [TopAssembly]
FROM
(
SELECT DISTINCT
CASE
WHEN Itm.[Class]=1 THEN 'Part'
ELSE 'Document'
END AS [Class]
,Itm.[Owner] AS [Owner]
,Itm.[Type] AS [Type]
,Itm.[Number] AS [Number]
,Itm.ItemId
FROM
[PDXpertDB].[viewer].[ItemView] Itm
LEFT JOIN [PDXpertDB].[dbo].[ChildItem_View] Child ON Child.BasicItem=Itm.ItemId
LEFT JOIN [PDXpertDB].[dbo].[ChildItem_View] Parent ON Parent.ParentItem=Itm.ItemId
WHERE
Child.ParentItem IS NULL AND Parent.BasicItem IS NOT NULL AND Itm.RevID IS NULL
) Parents
)
, CITree AS
(
SELECT
CI.[Id] AS [RowId]
,CI.[ParentItem]
,1 AS [Level]
,RIGHT('00000' + CAST(((10000 * CI.[RelKind]) + CI.[Number]) AS nvarchar(MAX)), 5) AS [TreeId]
,CI.[BasicItem] AS [ItemId]
,RT.TopAssembly
FROM
dbo.ChildItem CI
INNER JOIN BasicItemRevision_View PRev ON PRev.ItemId=CI.ParentItem AND PRev.FromNum <= @N AND PRev.ToNum > @N
INNER JOIN BasicItemRevision_View CRev ON CRev.ItemId=CI.BasicItem AND CRev.FromNum <= @N AND CRev.ToNum > @N
INNER JOIN RootItem RT ON RT.ItemId=CI.ParentItem
WHERE
CI.FromNum <= @N AND CI.ToNum > @N
UNION ALL
SELECT
Chld.[Id]
,Chld.[ParentItem]
,CITree.[Level] + 1
,CITree.[TreeId] + '.' + RIGHT('00000' + CAST(((10000 * Chld.[RelKind]) + Chld.[Number]) AS nvarchar(MAX)), 5)
,Chld.[BasicItem]
,CITree.TopAssembly
FROM
dbo.ChildItem Chld
INNER JOIN CITree ON Chld.ParentItem = CITree.ItemId
INNER JOIN BasicItemRevision_View PRev ON PRev.ItemId=Chld.ParentItem AND PRev.FromNum <= @N AND PRev.ToNum > @N
INNER JOIN BasicItemRevision_View CRev ON CRev.ItemId=Chld.BasicItem AND CRev.FromNum <= @N AND CRev.ToNum > @N
WHERE
Chld.[FromNum] <= @N AND Chld.[ToNum] > @N AND CITree.[Level] < @MAXDEPTH
)
, ParentTree AS
(
SELECT
0 AS [Level]
,'00000' AS [TreeId]
,[ItemId] AS [ItemId]
,[TopAssembly]
FROM
RootItem
UNION ALL
SELECT
[Level]
,[TreeId]
,[ItemId]
,[TopAssembly]
FROM
CITree
)
, Relation AS
(
SELECT
ItemId
,ROW_NUMBER() OVER (ORDER BY [Level] DESC, [ItemId], [TopAssembly], [TreeId]) AS [ImportOrder]
,ROW_NUMBER() OVER (PARTITION BY ItemId ORDER BY [Level] DESC, [ItemId], [TopAssembly], [TreeId]) AS [Sequence]
FROM
ParentTree
)
SELECT DISTINCT
[Class]
,[Owner]
,[Type]
,[Number]
,'Step ' + CONVERT(nvarchar(10),CAST(REPLACE([Number],@PARENTPREFIX,'') AS int)) + ': ' + @RELEASESETINFO AS [Description]
FROM
(
SELECT
@ITEMCLASS AS [Class]
,'' AS [Owner]
,CASE
WHEN (IV.[Class]=1) AND @ACCEPTSPARTS='false' THEN @ERRORTEXT
WHEN (IV.[Class]=2) AND @ACCEPTSDOCS='false' THEN @ERRORTEXT
ELSE @ITEMTYPE
END AS [Type]
,CASE
WHEN (IV.[Class]=1) AND @ACCEPTSPARTS='false' THEN @SETACCEPTPARTS
WHEN (IV.[Class]=2) AND @ACCEPTSDOCS='false' THEN @SETACCEPTDOCS
ELSE @PARENTPREFIX + Right('000' + CAST(NTILE(@Set) OVER (ORDER BY [ImportOrder]) AS varchar(10)), 3)
END AS [Number]
,CASE
WHEN IV.[Class]=1 THEN 'Part'
ELSE 'Document'
END AS [ChildClass]
,IV.[Owner] AS [ChildOwner]
,IV.[Type] AS [ChildType]
,IV.[Number] AS [ChildNumber]
FROM
(
SELECT
ItemId AS [ItemId]
,'A' + RIGHT('0000000' + CAST([ImportOrder] AS nvarchar(10)),7) AS [ImportOrder]
FROM
OrphanItem
UNION ALL
SELECT
ItemId
,'B' + RIGHT('0000000' + CAST([ImportOrder] AS nvarchar(10)),7)
FROM
Relation
WHERE
[Sequence] = 1
) Items
INNER JOIN viewer.ItemView IV ON IV.ItemId=Items.ItemId
WHERE
IV.RevID IS NULL
) ItmMstr
ORDER BY
Number
]]>