[dbo].[MssWebGetAccountProfileServicesWithOverrides]
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