USP_MKTCOMMUNICATIONTEMPLATEDEFAULTKPI_GETFIELDS

CREATE procedure dbo.[USP_MKTCOMMUNICATIONTEMPLATEDEFAULTKPI_GETFIELDS]
(
  @COMMUNICATIONTEMPLATEID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  declare @KPINAMESUFFIX nvarchar(50) = ' for Marketing Effort';
  declare @KPIRECORDTYPE uniqueidentifier;
  declare @MEMBERSHIPKPINAMESUFFIX nvarchar(50);

  --Get the KPI record type and set the name suffix to remove from the names, so the names don't look cluttered in the activate UI...

  set @KPIRECORDTYPE = dbo.[UFN_RECORDTYPE_GETIDBYNAME]('Marketing Effort');
  set @MEMBERSHIPKPINAMESUFFIX = ' for Membership Renewal Effort';

   select
    [MKTCOMMUNICATIONTEMPLATEDEFAULTKPI].[KPICATALOGID],
    case when [KPICATALOG].[NAME] like '%' + @KPINAMESUFFIX
         then left([KPICATALOG].[NAME], len([KPICATALOG].[NAME]) - len(@KPINAMESUFFIX))
         when [KPICATALOG].[NAME] like '%' + @MEMBERSHIPKPINAMESUFFIX
         then left([KPICATALOG].[NAME], len([KPICATALOG].[NAME]) - len(@MEMBERSHIPKPINAMESUFFIX))
         else [KPICATALOG].[NAME] 
    end as [NAME],
    [MKTCOMMUNICATIONTEMPLATEDEFAULTKPI].[LOCKED]
  from dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTKPI]
  inner join dbo.[KPICATALOG] on [KPICATALOG].[ID] = [MKTCOMMUNICATIONTEMPLATEDEFAULTKPI].[KPICATALOGID]
  where [MKTCOMMUNICATIONTEMPLATEDEFAULTKPI].[COMMUNICATIONTEMPLATEID] = @COMMUNICATIONTEMPLATEID
  and (@CURRENTAPPUSERID is null or dbo.[UFN_SECURITY_APPUSER_GRANTED_KPI](@CURRENTAPPUSERID, [KPICATALOG].[ID]) = 1)
  order by [KPICATALOG].[NAME];

  return 0;