CREATE PROCEDURE [dbo].[MssWebGetAccountProfileServicesWithOverrides]
    @orderID int,
    @accountProfileID int
AS
BEGIN
SELECT
    AccountProfileServiceID,
    AccountProfileFID,
    AccountProfileServiceName = AccountProfileAvailableServices.Name,
    AccountProfileServiceStatusFID = ISNULL(AccountProfileServicesOverride.AccountProfileServiceStatusFID, AccountProfileServices.AccountProfileServiceStatusFID),
    AccountProfileServiceStatusName = AccountProfileServiceStatus.Name,
    Comment = ISNULL(AccountProfileServicesOverride.Comment, AccountProfileServices.Comment),
    OverrideFlag =
        CASE
            WHEN AccountProfileServicesOverride.AccountProfileServiceOverrideID IS NULL THEN CONVERT(BIT,0)
            ELSE CONVERT(BIT,1)
            END,
    OverrideBy = LTRIM(ISNULL(Sysuser.FirstName, '') + ' ' + ISNULL(Sysuser.LastName, '')),
    OverrideOn=AccountProfileServicesOverride.ModifiedDate
    FROM AccountProfileServices
    LEFT JOIN AccountProfileServicesOverride
        ON AccountProfileServicesOverride.AccountProfileServiceFID = AccountProfileServices.AccountProfileServiceID
        AND AccountProfileServicesOverride.OrderFID = @orderID
    LEFT JOIN Sysuser
        ON AccountProfileServicesOverride.ModifiedBySysUserFID = Sysuser.SysUserID
    LEFT JOIN AccountProfileServiceStatus
        ON AccountProfileServiceStatus.AccountProfileServiceStatusID = AccountProfileServices.AccountProfileServiceStatusFID
    INNER JOIN AccountProfileAvailableServices
        ON AccountProfileServices.AccountProfileAvailableServiceFID = AccountProfileAvailableServices.AccountProfileAvailableServiceID
    WHERE AccountProfileServices.AccountProfileFID = @accountProfileID
    ORDER BY AccountProfileAvailableServices.Name;
END;
GO
GRANT EXECUTE ON  [dbo].[MssWebGetAccountProfileServicesWithOverrides] TO [MssExec]
GO