CREATE PROCEDURE [dbo].[GSE_ALL_RELATIONS]
    @Param_CI_Name AS VARCHAR(500),
    @ShowChildLevels AS INT,
    @ShowParentLevels AS INT
AS
BEGIN
    SET NOCOUNT ON;
    SET @ShowChildLevels = -1*@ShowChildLevels
    DECLARE @CINamePar AS VARCHAR(100)
    SET @CINamePar = (SELECT own_resource_uuid FROM ca_owned_resource WHERE resource_name = @Param_CI_Name AND inactive = 0)
    CREATE TABLE #CI_RELS (EdgeID int, ParentID binary(16), ChildID binary(16), EdgeType varchar(30), HierarchyLevel int)
    ;
    WITH AllChildren (EdgeID, ParentID, ChildID, EdgeType, HierarchyLevel) AS
    (
        -- Base case for children
        SELECT
            b.id as RelID,
            b.hier_parent as ParentID,
            b.hier_child as ChildID,
            b.ci_rel_type as RelTypeID,
            -1 as HierarchyLevel
        FROM dbo.busmgt b
        WHERE b.del = 0 AND  b.hier_parent = @CINamePar
        UNION ALL
        SELECT
            b.id as RelID,
            b.hier_parent as ParentID,
            b.hier_child as ChildID,
            b.ci_rel_type as RelTypeID,
            ac.HierarchyLevel - 1 AS HierarchyLevel
        FROM dbo.busmgt b
        INNER JOIN AllChildren ac ON ac.EdgeID != b.id AND ac.ChildID = b.hier_parent
        WHERE b.del = 0
    )
    INSERT INTO #CI_RELS
    SELECT EdgeID, ParentID, ChildID, EdgeType, HierarchyLevel
    FROM AllChildren
    LEFT JOIN View_GSE_CI_DETAILS AS Ch ON AllChildren.ChildID = Ch.CIid
    LEFT JOIN View_GSE_CI_DETAILS AS Pa ON AllChildren.ParentID = Pa.CIid
    WHERE HierarchyLevel >= @ShowChildLevels
    ;
    WITH AllParents (EdgeID, ParentID, ChildID, EdgeType,  HierarchyLevel) AS
    (
        -- Base case for parents
        SELECT
            b.id as RelID,
            b.hier_parent as ParentID,
            b.hier_child as ChildID,
            b.ci_rel_type as RelTypeID,
            1 as HierarchyLevel
        FROM dbo.busmgt b
        WHERE b.del = 0 AND  b.hier_child = @CINamePar
        UNION ALL
        SELECT
            b.id as RelID,
            b.hier_parent as ParentID,
            b.hier_child as ChildID,
            b.ci_rel_type as RelTypeID,
            ac.HierarchyLevel + 1 AS HierarchyLevel
        FROM dbo.busmgt b
        INNER JOIN AllParents ac ON ac.EdgeID != b.id AND ac.ParentID = b.hier_child
        WHERE b.del = 0
    )
    INSERT INTO #CI_RELS
    SELECT EdgeID, ChildID, ParentID, EdgeType, HierarchyLevel
    FROM AllParents
    LEFT JOIN View_GSE_CI_DETAILS AS Ch ON AllParents.ChildID = Ch.CIid
    LEFT JOIN View_GSE_CI_DETAILS AS Pa ON AllParents.ParentID = Pa.CIid
    WHERE HierarchyLevel <= @ShowParentLevels

    SELECT
        Ch.CIid,
       Ch.CIName,
       Ch.CIFamilyName,
       Ch.CIClassName,
       Ch.CILocName,
       Ch.CIRespOrgName,
       Ch.CITenant,
       Ch.CIISCI,
       Ch.installation_date,
       Ch.creation_date,
       Ch.last_update_date,
       Ch.inactive,
        e.childtoparent AS RelTypeP,
        e.parenttochild AS RelTypeC,
        e.is_peer AS IsPeer,
        Pa.CIName AS DepCI,
        HierarchyLevel
    FROM #CI_RELS
    LEFT JOIN View_GSE_CI_DETAILS AS Ch ON ChildID = Ch.CIid
    LEFT JOIN View_GSE_CI_DETAILS AS Pa ON ParentID = Pa.CIid
    LEFT OUTER JOIN dbo.ci_rel_type AS e ON #CI_RELS.EdgeType = e.id
    ORDER BY HierarchyLevel , Ch.CIName

END

GO
GRANT EXECUTE ON  [dbo].[GSE_ALL_RELATIONS] TO [service_desk_admin_group]
GRANT EXECUTE ON  [dbo].[GSE_ALL_RELATIONS] TO [service_desk_ro_group]
GO