DataGrid BIR.FromNum) INNER JOIN RevisionPhysicalFile AS RPF ON RPF.RevisionFile = F.Id AND RPF.CommitNum <= BIR.FileCommitNum INNER JOIN PhysicalFile PF ON RPF.PhysicalFile = PF.Id ) AS RV WHERE [RowNumber] = 1 ) SELECT [Affected] ,[Class] ,[Owner] ,[Type] ,[Number] ,[Revision] ,[Lifecycle] ,[Description] ,[Action] ,[HK100] ,[List] ,[Location] ,[ChildClass] ,[ChildOwner] ,[ChildType] ,[ChildNumber] ,[ChildRev] ,[ChildDescription] ,[Quantity] ,[Units] ,[RefDes] ,[Notes] ,ROW_NUMBER() OVER (ORDER BY [Affected],[RowOrder],[ChildOwner]) AS [Line] FROM ( SELECT 0 AS [Affected] ,CASE WHEN @UseAbbreviations = 0 THEN 'Change' ELSE 'Chg' END AS [Class] ,CASE WHEN @UseAbbreviations = 0 THEN BOOrg.[Name] ELSE BOOrg.[Abbreviation] END AS [Owner] ,CASE WHEN @UseAbbreviations = 0 THEN BOTyp.[Name] ELSE BOTyp.[Abbreviation] END AS [Type] ,Chg.[Number] AS [Number] ,'-' AS [Revision] ,Chg.[Lifecycle] AS [Lifecycle] ,Chg.[Description] AS [Description] ,'' AS [Action] ,'' AS [List] ,'' AS [Location] ,'' AS [ChildClass] ,'' AS [ChildOwner] ,'' AS [ChildType] ,'' AS [ChildNumber] ,Chg.[ChangeId] AS [HK100] ,'' AS [ChildRev] ,'' AS [ChildDescription] ,'' AS [Quantity] ,'' AS [Units] ,'' AS [RefDes] ,Chg.[Notes] AS [Notes] ,0 AS [RowOrder] FROM ChgInfo Chg JOIN BusinessObject BOTyp ON BOTyp.[Id] = Chg.[TypeId] JOIN BusinessObject BOOrg ON BOOrg.[Id] = Chg.[OwnerId] WHERE Chg.[ChangeId] = @P1 UNION ALL -- Affected item detail SELECT AILst.[Line] AS [Line] ,CASE WHEN @UseAbbreviations = 0 AND Itm.[Class] = 1 THEN 'Part' WHEN @UseAbbreviations <> 0 AND Itm.[Class] = 1 THEN 'Prt' WHEN @UseAbbreviations = 0 AND Itm.[Class] = 2 THEN 'Document' WHEN @UseAbbreviations <> 0 AND Itm.[Class] = 2 THEN 'Doc' ELSE '?' END AS [Class] ,CASE WHEN @UseAbbreviations = 0 THEN Itm.[Owner] ELSE Itm.[OwnerShort] END AS [Owner] ,CASE WHEN @UseAbbreviations = 0 THEN Itm.[Type] ELSE Itm.[TypeShort] END AS [Type] ,Itm.[Number] AS [Number] ,AILst.[RelRevision] AS [Revision] ,LCRel.[Name] AS [Lifecycle] ,Itm.[Description] AS [Description] ,CASE WHEN (AILst.[Action] = 1) AND (@IsReleased = 0) THEN '»Release' WHEN (AILst.[Action] = 1) AND (@IsReleased = 1) THEN 'Released' WHEN (AILst.[Action] = 2) AND (@IsReleased = 0) THEN '»Cancel' WHEN (AILst.[Action] = 2) AND (@IsReleased = 1) THEN 'Canceled' WHEN (AILst.[Action] = 3) AND (@IsReleased = 0) THEN '»Revise' WHEN (AILst.[Action] = 3) AND (@IsReleased = 1) THEN 'Revised' ELSE NULL END AS [Action] ,'---' AS [List] ,'' AS [Location] ,'' AS [ChildClass] ,'' AS [ChildOwner] ,'' AS [ChildType] ,'' AS [ChildNumber] ,Itm.[ItemId] AS [HK100] ,'' AS [ChildRev] ,'' AS [ChildDescription] ,'' AS [Quantity] ,'' AS [Units] ,'' AS [RefDes] ,AILst.[RelReleaseNote] AS [Notes] ,AILst.[Line] * 100000 AS [RowOrder] -- [Line x n][Child kind x 1][Row x 3][Subrow x 1] FROM AffItmList AILst INNER JOIN viewer.[ItemView] Itm ON Itm.[ItemId] = AILst.[ItemId] INNER JOIN [ItemLifeCyclePhase_View] LCRel ON LCRel.[Id] = AILst.[RelLifecycleId] WHERE AILst.[ChangeId] = @P1 UNION ALL -- Affected item files SELECT AILst.[Line] AS [Line] ,CASE WHEN @UseAbbreviations = 0 AND Itm.[Class] = 1 THEN 'Part' WHEN @UseAbbreviations <> 0 AND Itm.[Class] = 1 THEN 'Prt' WHEN @UseAbbreviations = 0 AND Itm.[Class] = 2 THEN 'Document' WHEN @UseAbbreviations <> 0 AND Itm.[Class] = 2 THEN 'Doc' ELSE '?' END AS [Class] ,CASE WHEN @UseAbbreviations = 0 THEN Itm.[Owner] ELSE Itm.[OwnerShort] END AS [Owner] ,CASE WHEN @UseAbbreviations = 0 THEN Itm.[Type] ELSE Itm.[TypeShort] END AS [Type] ,Itm.[Number] AS [Number] ,AILst.[RelRevision] AS [Revision] ,LCRel.[Name] AS [Lifecycle] ,Itm.[Description] AS [Description] ,CASE WHEN (@IsReleased = 0) THEN 'Attach' ELSE 'Attached' END AS [Action] ,'RevisionFiles' AS [List] ,'' AS [Location] ,'' AS [ChildClass] ,'' AS [ChildOwner] ,RFile.[Ext] AS [ChildType] ,'' AS [ChildNumber] ,Itm.[ItemId] AS [HK100] ,'' AS [ChildRev] ,RFile.[FileName] AS [ChildDescription] ,CAST(RFile.[FileSize] AS varchar) AS [Quantity] ,'bytes' AS [Units] ,'' AS [RefDes] ,ISNULL(RFile.[Notes],'') AS [Notes] ,(AILst.[Line] * 100000) + (10000) + ROW_NUMBER() OVER (PARTITION BY RFile.[RevId] ORDER BY RFile.[FileName]) AS [RowOrder] FROM AffItmList AILst INNER JOIN viewer.[ItemView] Itm ON Itm.[ItemId] = AILst.[ItemId] INNER JOIN [ItemLifeCyclePhase_View] LCRel ON LCRel.[Id] = AILst.[RelLifecycleId] INNER JOIN RevFileList RFile ON RFile.[RevId] = AILst.[RelRevId] WHERE AILst.[ChangeId] = @P1 AND @IncludeRevFiles <> 0 UNION ALL -- Affected item children in BOM / Src / Ref SELECT AILst.[Line] AS [Line] ,CASE WHEN @UseAbbreviations = 0 AND Itm.[Class] = 1 THEN 'Part' WHEN @UseAbbreviations <> 0 AND Itm.[Class] = 1 THEN 'Prt' WHEN @UseAbbreviations = 0 AND Itm.[Class] = 2 THEN 'Document' WHEN @UseAbbreviations <> 0 AND Itm.[Class] = 2 THEN 'Doc' ELSE '?' END AS [Class] ,CASE WHEN @UseAbbreviations = 0 THEN Itm.[Owner] ELSE Itm.[OwnerShort] END AS [Owner] ,CASE WHEN @UseAbbreviations = 0 THEN Itm.[Type] ELSE Itm.[TypeShort] END AS [Type] ,Itm.[Number] AS [Number] ,AILst.[RelRevision] AS [Revision] ,LCRel.[Name] AS [Lifecycle] ,Itm.[Description] AS [Description] ,CASE WHEN (ChldRow.[FromNum] = @ChangeCommitNum) AND (@IsReleased = 0) THEN '+Add' WHEN (ChldRow.[FromNum] = @ChangeCommitNum) AND (@IsReleased = 1) THEN 'Added' WHEN (ChldRow.[ToNum] = @ChangeCommitNum) AND (@IsReleased = 0) THEN '-Remove' WHEN (ChldRow.[ToNum] = @ChangeCommitNum) AND (@IsReleased = 1) THEN 'Removed' ELSE '?' END AS [Action] ,CASE ChldRow.[RelKind] WHEN 0 THEN 'BOM' WHEN 1 THEN 'References' WHEN 2 THEN 'Sources' END AS [List] ,CASE ChldRow.[RelKind] WHEN 0 THEN 'Find ' WHEN 1 THEN 'Row ' WHEN 2 THEN 'Rank ' END + CAST(ChldRow.[Number] AS varchar) AS [Location] ,CASE WHEN @UseAbbreviations = 0 AND ChldItm.[Class] = 1 THEN 'Part' WHEN @UseAbbreviations <> 0 AND ChldItm.[Class] = 1 THEN 'Prt' WHEN @UseAbbreviations = 0 AND ChldItm.[Class] = 2 THEN 'Document' WHEN @UseAbbreviations <> 0 AND ChldItm.[Class] = 2 THEN 'Doc' ELSE '?' END AS [ChildClass] ,CASE WHEN @UseAbbreviations = 0 THEN ChldItm.[Owner] ELSE ChldItm.[OwnerShort] END AS [ChildOwner] ,CASE WHEN @UseAbbreviations = 0 THEN ChldItm.[Type] ELSE ChldItm.[TypeShort] END AS [ChildType] ,ChldItm.[Number] AS [ChildNumber] ,ChldItm.[ItemId] AS [HK100] ,ChldRev.[RevisionIdentifier] AS [ChildRev] ,ChldItm.[Description] AS [ChildDescription] ,ISNULL(CAST(CAST(BOM.[Quantity] AS decimal(19,4)) AS varchar),'') AS [Quantity] ,COALESCE(UOM.[Name], ChldItm.[DefaultUOM],'') AS [Units] ,ISNULL(BOM.[ReferenceDesignator],'') AS [RefDes] ,ISNULL(ChldRow.[Notes],'') AS [Notes] ,(AILst.[Line] * 100000) + ((ChldRow.[RelKind] + 2) * 10000) + (ChldRow.[Number] * 10) + (CASE WHEN ChldRow.[FromNum] = @ChangeCommitNum THEN 1 ELSE 0 END) AS [RowOrder] FROM AffItmList AILst INNER JOIN viewer.[ItemView] Itm ON Itm.[ItemId] = AILst.[ItemId] INNER JOIN [ItemLifeCyclePhase_View] LCRel ON LCRel.[Id] = AILst.[RelLifecycleId] INNER JOIN [ChildItem_View] ChldRow ON ChldRow.[ParentItem] = AILst.[ItemId] AND (ChldRow.[FromNum] = @ChangeCommitNum OR ChldRow.[ToNum] = @ChangeCommitNum) INNER JOIN viewer.[ItemView] ChldItm ON ChldItm.[ItemId] = ChldRow.[BasicItem] INNER JOIN [BasicItemRevision_View] ChldRev ON ChldRev.[ItemId] = ChldRow.[BasicItem] AND (ChldRev.[FromNum] <= @ChangeCommitNum AND ChldRev.[ToNum] > @ChangeCommitNum) LEFT JOIN StructureItem_View BOM ON BOM.[Id] = ChldRow.[Id] LEFT JOIN UOM_View UOM ON UOM.[Id] = BOM.[UoM] WHERE AILst.[ChangeId] = @P1 AND ( (@IncludeBOM = 1 AND ChldRow.[RelKind] = 0) OR (@IncludeReference = 1 AND ChldRow.[RelKind] = 1) OR (@IncludeSource = 1 AND ChldRow.[RelKind] = 2) ) ) FileList ORDER BY [Line] ]]>