Back Office Account screen load error on GetAccountsFull Stored Procedure

In some latter versions of EROS, post upgrade the customer may see an error when loading an account group, 'An Item With The Same Key Has Already Been Added'. The resolution is to run the below script to update the saved Stored Procedure in the database.

Instructions

Backup the Bepoz Database and take smart controller offline
Run SQL Server Management Studio
Select the appropriate database in the database dropdown and select 'New Query'
Copy and paste this script and execute then restart smartcontroller and test viewing the account group in Back Office

ALTER PROCEDURE [dbo].[GetAccountsFull]

    @ParentId INTEGER,
    @IsParentTypes INTEGER,
    @IncludeSubItems BIT,
    @IncludeInactive BIT,
    @Sorted BIT,
    @IncludeImageData BIT

AS
BEGIN

 --   Return ResultSets
 --   oAccount = 0
 --   oParent = 1
 --   oAddress = 2
 --   oComment = 3
 --   oMembership = 4
 --   oRenewal = 5
 --   oImageData = 6
 --   oEarnProfile = 7

    DECLARE @ValidIsParentTypes TABLE(IsParent INTEGER)

    -- eINCLUDEPARENTS.ItemsOnly
    IF @IsParentTypes = 0
    BEGIN
        INSERT INTO @ValidIsParentTypes (IsParent) VALUES (0)
    END

    -- eINCLUDEPARENTS.AllItemsAndAllParents
    IF @IsParentTypes = 1
    BEGIN
        INSERT INTO @ValidIsParentTypes (IsParent) VALUES (0), (1), (2), (3)
    END

    -- eINCLUDEPARENTS.AllParentsOnly
    IF @IsParentTypes = 2
    BEGIN
        INSERT INTO @ValidIsParentTypes (IsParent) VALUES (1), (2), (3)
    END

    -- eINCLUDEPARENTS.ParentsOfItemsOnly
    IF @IsParentTypes = 3
    BEGIN
        INSERT INTO @ValidIsParentTypes (IsParent) VALUES (1), (3)
    END

    -- eINCLUDEPARENTS.ParentsOfParentsOnly
    IF @IsParentTypes = 4
    BEGIN
        INSERT INTO @ValidIsParentTypes (IsParent) VALUES (2), (3)
    END

    
    -- N.B. Can't use SELECT INTO with the same temp table from within separate if clauses so use defined table variable as intermediate
    DECLARE @Accounts TABLE(AccountID INTEGER)


    -- Create base account data
    IF @IncludeSubItems = 0
    BEGIN

        INSERT INTO @Accounts
        SELECT a.AccountID
        FROM Account AS a
            JOIN @ValidIsParentTypes AS v ON (v.IsParent = a.IsParent)
        WHERE (a.ParentID = @ParentId OR @ParentId = 0)

    END
    ELSE
    BEGIN

        WITH AccountChain(ParentID, AccountID, IsParent) AS ( 
            SELECT a1.ParentID, a1.AccountID, a1.IsParent 
            FROM Account AS a1
            WHERE a1.ParentID = @ParentId
            UNION ALL 
            SELECT a2.ParentID, a2.AccountID, a2.IsParent 
            FROM Account AS a2 INNER JOIN AccountChain c ON a2.ParentID = c.AccountID
            )
        INSERT INTO @Accounts
        SELECT a.AccountID
        FROM Account AS a
            JOIN AccountChain AS ac ON (ac.AccountID = a.AccountID)
            JOIN @ValidIsParentTypes AS v ON (v.IsParent = ac.IsParent)

    END


    SELECT a.*
    INTO #Accounts
    FROM Account AS a
        JOIN @Accounts AS t ON(t.AccountID = a.AccountID)
    WHERE (a.Status < 4 OR @IncludeInactive = 1)            -- eAccountStatus.Inactive

    -- Select Account ordered if required
    IF @Sorted = 0
    BEGIN
        SELECT * FROM #Accounts
    END
    ELSE
    BEGIN
        SELECT * FROM #Accounts
        ORDER BY LastName ASC
    END

    -- Get Account parent records
    SELECT DISTINCT p.* FROM Account AS p
        JOIN #Accounts AS a ON (a.ParentID = p.AccountId) 

    -- Get Account Address
    SELECT DISTINCT ad.* 
    FROM Address AS ad
        INNER JOIN #Accounts AS a ON (ad.AddressID = a.AddressID)

    -- Get Account Comment
    SELECT DISTINCT c.* 
    FROM Comment AS c
        INNER JOIN #Accounts AS a ON (a.CommentID = c.CommentID)

    -- Get Account Membership
    SELECT DISTINCT m.* 
    FROM Membership AS m
        INNER JOIN #Accounts AS a ON (a.MembershipID = m.MembershipID)

    -- Get Account Renewal
    SELECT DISTINCT r.* 
    FROM Renewal AS r
        INNER JOIN #Accounts AS a ON (a.RenewalID = r.RenewalID)

    -- Get Account ImageData
    SELECT DISTINCT i.* 
    FROM  ImageData AS i
        INNER JOIN #Accounts AS a ON (a.ImageID = i.ImageDataID)
    WHERE @IncludeImageData = 1

    -- Get Account PointsProfile
    SELECT DISTINCT p.* 
    FROM PointsProfile AS p
        INNER JOIN #Accounts AS a ON (a.PointsProfile = p.PointsProfileID)

    
    -- Cleanup
    DROP TABLE #Accounts
    
END