@EndPoint
)
SELECT
ASSY.Level
,ASSY.Find
--,ASSY.Owner
,ASSY.Type
,ASSY.Number
,ASSY.Iteration
--,ASSY.ReleaseStatus
,ASSY.Description
,ASSY.Quantity
,ASSY.RefDes
,ASSY.Notes
,Coalesce(S1.OwnerShort,'') AS Source1
,Coalesce(S1.Number,'') AS SrcNumber1
,Coalesce(S2.OwnerShort,'') AS Source2
,Coalesce(S2.Number,'') AS SrcNumber2
,Coalesce(S3.OwnerShort,'') AS Source3
,Coalesce(S3.Number,'') AS SrcNumber3
FROM
(
SELECT -- BOM with source pivot
Level
,Find
,Owner
,Type
,Number
,Iteration
,ReleaseStatus
,Description
,Quantity
,RefDes
,Notes
,Src001
,Src002
,Src003
,SortBy
FROM (
SELECT -- parent assembly
'0' AS [Level]
,' ' AS [POwner]
,' ' AS [PType]
,' ' AS [PNumber]
,' ' AS [Find]
,[Owner] AS [Owner]
,[Type] AS [Type]
,[Number] AS [Number]
,[Revision] + ' (' + [Lifecycle] + ')' AS [Iteration]
,[ReleaseStatus]
,'1.0000 ' + [DefaultUoM] AS [Quantity]
,[Description] AS [Description]
,'' AS [RefDes]
,'' AS [Notes]
,'' AS [SrcRank]
,[SrcItemId]
,'000' AS [SortBy]
FROM
(
SELECT
It.Id AS [ItemId]
,ItVw.OwnerShort AS [Owner]
,ItVw.TypeShort AS [Type]
,It.ItemIdentifier AS [Number]
,It.[Name] AS [Description]
,Rv.[Id] AS [RevId]
,Rv.[RevisionIdentifier] AS [Revision]
,RvLC.Name AS [Lifecycle]
,@EmptyGuid AS [SrcItemId]
,ItUom.Name AS DefaultUoM
,Rv.[FromNum] AS ActiveFrom
,Rv.[ToNum] AS ActiveTo
,CASE Rv.RevState
WHEN 0 THEN 'Pending'
WHEN 1 THEN 'Releasing'
WHEN 2 THEN 'Released'
WHEN 3 THEN 'Canceling'
WHEN 4 THEN 'Canceled'
ELSE 'Unknown'
END AS [ReleaseStatus]
FROM PDXpertDB.dbo.[BasicItemRevision_View] Rv
INNER JOIN PDXpertDB.dbo.[BasicItem_View] It ON It.Id = Rv.ItemId
INNER JOIN PDXpertDB.viewer.[ItemView] ItVw ON ItVw.ItemId = It.[Id]
INNER JOIN PDXpertDB.dbo.[BusinessObject_View] ItUom ON ItUom.Id = It.PartUOM
INNER JOIN PDXpertDB.dbo.[BusinessObject_View] RvLC ON RvLC.Id = Rv.LifeCyclePhase
WHERE
Rv.Id = @P1
) AS Parent
UNION ALL
SELECT -- child component with ranked sources
CAST(BOM.[Level] AS nvarchar(10)) AS [Level]
,PrntItm.OwnerShort AS [POwner]
,PrntItm.TypeShort AS [PType]
,PrntItm.Number AS [PNumber]
,Partlist.[Number] AS [Find]
,ChldItm.OwnerShort AS [Owner]
,ChldItm.TypeShort AS [Type]
,ChldItm.Number AS [Number]
,ChldRev.[RevisionIdentifier] + ' (' + ChldRvLC.[Name] +')' AS [Iteration]
,CASE ChldRev.RevState
WHEN 0 THEN 'Pending'
WHEN 1 THEN 'Releasing'
WHEN 2 THEN 'Released'
WHEN 3 THEN 'Canceling'
WHEN 4 THEN 'Canceled'
ELSE 'Unknown'
END AS [ReleaseStatus]
,Cast(Convert(Decimal(18, 4), Partlist.[Quantity]) AS varchar(20)) + ' ' + Coalesce(Unt.[Name],ChldItm.[DefaultUOM])
,ChldItm.[Description] AS [Description]
,Partlist.[ReferenceDesignator] AS [RefDes]
,Partlist.[Notes] AS [Notes]
,ChldSrc.[SrcRank]
,ChldSrc.[SrcItemId] AS SrcItemId
,BOM.TreeId AS [SortBy]
FROM
PDXpertDB.dbo.StructureItem_View AS Partlist
INNER JOIN BOM ON BOM.Id=Partlist.Id
AND Partlist.FromNum <= @EndPoint AND Partlist.ToNum > @EndPoint
LEFT JOIN PDXpertDB.dbo.BusinessObject AS Unt ON Unt.Id = Partlist.UoM
INNER JOIN PDXpertDB.viewer.ItemMasterView AS PrntItm ON PrntItm.ItemId = Partlist.ParentItem
INNER JOIN PDXpertDB.viewer.ItemMasterView AS ChldItm ON ChldItm.ItemId = Partlist.BasicItem
INNER JOIN PDXpertDB.dbo.BasicItemRevision_View AS ChldRev ON ChldRev.ItemId = ChldItm.ItemId
AND ChldRev.FromNum <= @EndPoint AND ChldRev.ToNum > @EndPoint
INNER JOIN PDXpertDB.dbo.[BusinessObject_View] AS ChldRvLC ON ChldRvLC.Id = ChldRev.LifeCyclePhase
LEFT JOIN ChildSource ChldSrc ON ChldSrc.ParentItem=ChldItm.ItemId
) BOMSRC
PIVOT
(
Min([SrcItemId])
FOR [SrcRank] IN ([Src001], [Src002], [Src003])
) AS PVT
) AS ASSY
LEFT JOIN viewer.ItemView S1 ON S1.ItemId=Src001
LEFT JOIN viewer.ItemView S2 ON S2.ItemId=Src002
LEFT JOIN viewer.ItemView S3 ON S3.ItemId=Src003
ORDER BY
SortBy
]]>
Table2