The unique identifier of the parent location of an item can be found by querying the LocationParentItemID property within the ItemCore table.


However, this only provides information about the immediate parent location which may be a room, rack, or location.


The GetItemLocationInformation function provides the following additional information about the immediate parent and items within the location hierarchy:


[Parent] INT

[ParentType] INT

[ParentTypeName] NVARCHAR(200)

[GrandParent] INT

[GrandParentType] INT

[GrandParentTypeName] NVARCHAR(200)

[GreatGrandParent] INT

[GreatGrandParentType] INT

[GreatGrandParentTypeName] NVARCHAR(200)

[Rack] INT

[RackName] NVARCHAR(200)

[Room] INT

[RoomName] NVARCHAR(200)

[Location] INT

[LocationName] NVARCHAR(200)

       


For example:


SELECT
Name AS [Name],
LocationInformation.LocationName AS [Location]
FROM ItemCore
OUTER APPLY [dbo].GetItemLocationInformation (ItemCore.ItemID) LocationInformation