CREATE procedure [dbo].[MssWebGetAccountProfileServicesWithOverrides]
    @orderId int
as
begin
    set nocount on

    select
        AccountProfileServices.AccountProfileServiceID,
        AccountProfileFID = Orders.AccountProfileFID,
        AccountProfileServiceName = AccountProfileAvailableServices.Name,
        AccountProfileServiceStatusFID = isnull(AccountProfileServicesOverride.AccountProfileServiceStatusFID, AccountProfileServices.AccountProfileServiceStatusFID),
        AccountProfileServiceStatusName = AccountProfileServiceStatus.Name,
        Comment = isnull(AccountProfileServicesOverride.Comment, AccountProfileServices.Comment),
        OverrideFlag =
            convert(bit,
                case
                    when AccountProfileServicesOverride.AccountProfileServiceOverrideID is null then 0
                    else 1
                end
            ),
        OverrideBy = dbo.FormatFirstNameLastName( Sysuser.FirstName,  Sysuser.LASTNAME),
        OverrideOn = AccountProfileServicesOverride.ModifiedDate
        from Orders
        inner join AccountProfileServices on Orders.AccountProfileFID = AccountProfileServices.AccountProfileFID
        left outer join AccountProfileServicesOverride
            on AccountProfileServicesOverride.AccountProfileServiceFID = AccountProfileServices.AccountProfileServiceID
            and AccountProfileServicesOverride.OrderFID = @orderId
        left outer join Sysuser
            on AccountProfileServicesOverride.ModifiedBySysUserFID = Sysuser.SysUserID
        left outer join AccountProfileServiceStatus
            on isnull( AccountProfileServicesOverride.AccountProfileServiceStatusFID, AccountProfileServices.AccountProfileServiceStatusFID) = AccountProfileServiceStatus.AccountProfileServiceStatusID
        inner join AccountProfileAvailableServices
            on AccountProfileServices.AccountProfileAvailableServiceFID = AccountProfileAvailableServices.AccountProfileAvailableServiceID
        where Orders.PriKey = @orderId
        order by AccountProfileAvailableServices.Name;
end;
GO
GRANT EXECUTE ON  [dbo].[MssWebGetAccountProfileServicesWithOverrides] TO [MssExec]
GO