DataGrid @ActiveTo AND ListFrom <= @ActiveTo AND ListTo > @ActiveTo ) , CurrentBOM AS ( SELECT RUp.Level AS [Level] ,RUp.TreeId AS [PathId] ,PL.* FROM PDXpertDB.dbo.BOMRollup_UDF(@ItmId,@ActiveFrom,@MxLvl) RUp INNER JOIN PartList PL ON PL.RowId=RUp.Id WHERE ChildFrom <= @ActiveFrom AND ChildTo > @ActiveFrom AND ListFrom <= @ActiveFrom AND ListTo > @ActiveFrom ) SELECT [Level] ,[Row] AS [Find] ,[Action] ,[RelChldType] AS [CURRENT] ,[RelChldNumber] AS [Number] ,LTRIM([RelChldRevision] + CASE WHEN [RelChldLifecycle]='' THEN '' ELSE ' (' + [RelChldLifecycle] + ')' END) AS [Iteration] --,[RelChldRevision] AS [Revision] --,[RelChldLifecycle] AS [Lifecycle] ,[RelChldName] AS [Name] ,[RelQuantity] AS [Qty] ,[RelUOM] AS [Unit] ,[RelRefDes] AS [RefDes] ,[RelRowNote] AS [RowNote] ,[PndChldType] AS [PENDING] ,[PndChldNumber] AS [PndNumber] ,LTRIM([PndChldRevision] + CASE WHEN [PndChldLifecycle]='' THEN '' ELSE ' (' + [PndChldLifecycle] + ')' END) AS [PndIteration] --,[PndChldRevision] AS [PndRevision] --,[PndChldLifecycle] AS [PndLifecycle] ,[PndChldName] AS [PndName] ,[PndQuantity] AS [PndQty] ,[PndUOM] AS [PndUnit] ,[PndRefDes] AS [PndRefDes] ,[PndRowNote] AS [PndRowNote] FROM ( SELECT -- root assembly 'TopAssembly' AS [List] ,@ActionIterateItem AS [Action] ,'0' AS [Level] ,'0' AS [Row] ,@EmptyGuid AS [RelRowId] ,'' AS [RelPrntType] ,'' AS [RelPrntNumber] ,'' AS [RelPrntName] ,[ItemId] AS [RelChldItmId] ,[Type] AS [RelChldType] ,[Number] AS [RelChldNumber] ,[RevId] AS [RelChldRevId] ,[Revision] AS [RelChldRevision] ,[Lifecycle] AS [RelChldLifecycle] ,[Description] AS [RelChldName] ,'' AS [RelQuantity] ,'' AS [RelUOM] ,'' AS [RelRefDes] ,'' AS [RelRowNote] ,@GrpRoot AS [RelPathId] ,0 AS [RelListFrom] ,0 AS [RelListTo] ,0 AS [RelChildFrom] ,0 AS [RelChildTo] ,0 AS [PndLevel] ,0 AS [PndFindRow] ,@EmptyGuid AS [PndRowId] ,'' AS [PndPrntType] ,'' AS [PndPrntNumber] ,'' AS [PndPrntName] ,@EmptyGuid AS [PndChldItmId] ,[Type] AS [PndChldType] ,[Number] AS [PndChldNumber] ,[PendingRevID] AS [PndChldRevId] ,[PendingRevision] AS [PndChldRevision] ,[PendingLifecycle] AS [PndChldLifecycle] ,[Description] AS [PndChldName] ,'' AS [PndQuantity] ,'' AS [PndUOM] ,'' AS [PndRefDes] ,'' AS [PndRowNote] ,@GrpRoot AS [PndPathId] ,0 AS [PndListFrom] ,0 AS [PndListTo] ,0 AS [PndChildFrom] ,0 AS [PndChildTo] ,@GrpRoot AS [PathId] ,@Nodepad AS [NodeId] FROM PDXpertDB.viewer.ItemView WHERE RevID = @RelRevId UNION ALL SELECT -- BOM rows 'BOMItem' AS [List] ,CASE WHEN RelBOM.PathId IS NULL THEN @ActionAddRow WHEN PndBOM.PathId IS NULL THEN @ActionRemoveRow WHEN (RelBOM.[ChildId] <> PndBOM.[ChildId]) THEN @ActionReplaceItem WHEN (RelBOM.[ChildRevId] <> PndBOM.[ChildRevId]) THEN @ActionIterateItem WHEN (RelBOM.[RowId] <> PndBOM.[RowId]) THEN 'Edit:' + Stuff( CASE WHEN (RelBOM.[Quantity] <> PndBOM.[Quantity]) THEN ',Qty' ELSE '' END + CASE WHEN (RelBOM.[UOM] <> PndBOM.[UOM]) THEN ',Unit' ELSE '' END + CASE WHEN (RelBOM.[RefDes] <> PndBOM.[RefDes]) THEN ',RefDes' ELSE '' END + CASE WHEN (RelBOM.[RowNote] <> PndBOM.[RowNote]) THEN ',Note' ELSE '' END , 1, 1, '') ELSE @ActionNoChange END AS [Action] ,ISNULL(RelBOM.[Level],PndBOM.[Level]) AS [Level] ,ISNULL(RelBOM.[FindRow],PndBOM.[FindRow]) AS [Row] ,RelBOM.[RowId] AS [RelRowId] ,Coalesce(RelBOM.[Type],'') AS [RelPrntType] ,Coalesce(RelBOM.[Number],'') AS [RelPrntNumber] ,Coalesce(RelBOM.[Name],'') AS [RelPrntName] ,RelBOM.[ChildId] AS [RelChldItmId] ,Coalesce(RelBOM.[ChildType],'') AS [RelChldType] ,Coalesce(RelBOM.[ChildNumber],'') AS [RelChldNumber] ,RelBOM.[ChildRevId] AS [RelChldRevId] ,Coalesce(RelBOM.[ChildRevision],'') AS [RelChldRevision] ,Coalesce(RelBOM.[ChildLifecycle],'') AS [RelChldLifecycle] ,Coalesce(RelBOM.[ChildName],'') AS [RelChldName] ,Coalesce(Cast(Convert(Decimal(18, 4), RelBOM.[Quantity]) AS varchar(20)),'') AS [RelQuantity] ,Coalesce(RelBOM.[UOM],'') AS [RelUOM] ,Coalesce(RelBOM.[RefDes],'') AS [RelRefDes] ,Coalesce(RelBOM.[RowNote],'') AS [RelRowNote] ,Coalesce(RelBOM.[PathId],'') AS [RelPathId] ,RelBOM.ListFrom ,RelBOM.ListTo ,RelBOM.ChildFrom ,RelBOM.ChildTo ,PndBOM.[Level] AS [PndLevel] ,PndBOM.[FindRow] AS [PndFindRow] ,PndBOM.[RowId] AS [PndRowId] ,Coalesce(PndBOM.[Type],'') AS [PndPrntType] ,Coalesce(PndBOM.[Number],'') AS [PndPrntNumber] ,Coalesce(PndBOM.[Name],'') AS [PndPrntName] ,PndBOM.[ChildId] AS [PndChldItmId] ,Coalesce(PndBOM.[ChildType],'') AS [PndChldType] ,Coalesce(PndBOM.[ChildNumber],'') AS [PndChldNumber] ,PndBOM.[ChildRevId] AS [PndChldRevId] ,Coalesce(PndBOM.[ChildRevision],'') AS [PndChldRevision] ,Coalesce(PndBOM.[ChildLifecycle],'') AS [PndChldLifecycle] ,Coalesce(PndBOM.[ChildName],'') AS [PndChldName] ,Coalesce(Cast(Convert(Decimal(18, 4), PndBOM.[Quantity]) AS varchar(20)),'') AS [PndQuantity] ,Coalesce(PndBOM.[UOM],'') AS [PndUOM] ,Coalesce(PndBOM.[RefDes],'') AS [PndRefDes] ,Coalesce(PndBOM.[RowNote],'') AS [PndRowNote] ,Coalesce(PndBOM.[PathId],'') AS [PndPathId] ,PndBOM.ListFrom ,PndBOM.ListTo ,PndBOM.ChildFrom ,PndBOM.ChildTo ,ISNULL(RelBOM.PathId,PndBOM.PathId) AS [PathId] ,@Nodepad AS [NodeId] FROM CurrentBOM AS RelBOM FULL OUTER JOIN MarkupBOM AS PndBOM ON PndBOM.PathId=RelBOM.PathId ) AssyFull WHERE (([Action] <> @ActionNoChange) OR (@IncludeNoChange = 'true')) AND (@RelRevId <> @EmptyGuid) AND (@PndRevId <> @EmptyGuid) ORDER BY PathId, NodeId ]]>