CREATE OR ALTER FUNCTION [App].[Fn_GetAdjudicationDecisionComments]
(
    @ApplicationDetailID BIGINT,
    @AdjudicationDetailID BIGINT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @ResultVar VARCHAR(MAX) = ''

    --Change 11 April 2015. Only show chairman's final comment
    --SELECT @ResultVar = COALESCE(@ResultVar+ ',', '') + U.KnownAs + ': ' + ISNULL(Arad.Comments,'') + CHAR(13) + CHAR(10)
    --FROM App.ApplicationRequestAdjudicationDecisions Arad
    --	INNER JOIN Security.Users U ON Arad.AdjudicatedByID = U.UserID
    --WHERE Arad.AdjudicationDetailID = @AdjudicationDetailID
    --	AND Arad.ApplicationDetailID = @ApplicationDetailID
    --	AND ISNULL(Arad.Comments,'') <> ''
    --ORDER BY Arad.AdjudicationDate ASC

    --Get comments only from users with Adjudication Secretary role (RoleID = 1008)
    --This replaces the previous logic that only showed chairman's comments
    SELECT @ResultVar = COALESCE(@ResultVar + CASE WHEN @ResultVar = '' THEN '' ELSE '; ' END + Arad.Comments, '')
    FROM App.ApplicationAdjudicationDecisions Arad
        INNER JOIN App.ApplicationAdjudications AAD ON AAD.ApplicationAdjudicationID = Arad.ApplicationAdjudicationID
        INNER JOIN Security.Users U ON Arad.AdjudicatedByID = U.UserID
        INNER JOIN Security.UserRoles UR ON U.UserID = UR.UserID
    WHERE AAD.AdjudicationDetailID = @AdjudicationDetailID
        AND AAD.ApplicationDetailID = @ApplicationDetailID
        AND ISNULL(Arad.Comments,'') <> ''
        AND UR.RoleID = 1008 -- Adjudication Secretary role
    ORDER BY Arad.AdjudicationDate DESC

    RETURN @ResultVar
END 