USP_SIMPLEDATALIST_MKTCOMMUNICATIONTEMPLATEKPI

CREATE procedure dbo.[USP_SIMPLEDATALIST_MKTCOMMUNICATIONTEMPLATEKPI]
(
  @CURRENTAPPUSERID uniqueidentifier,
  @TEMPLATETYPECODE tinyint
)
as
  set nocount on;

  declare @KPIRECORDTYPE uniqueidentifier;
  declare @KPINAMESUFFIX nvarchar(50) = ' for Marketing Effort';
  declare @MEMBERSHIPKPINAMESUFFIX nvarchar(50) = ' for Membership Renewal Effort';
  declare @INCLUDEMEMBERSHIPRENEWALEFFORTKPIS bit = case @TEMPLATETYPECODE when 2 then 1 else 0 end;

  set @KPIRECORDTYPE = dbo.[UFN_RECORDTYPE_GETIDBYNAME]('Marketing Effort');

  select
    [KPICATALOG].[ID] as [VALUE],
    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 [LABEL]
  from dbo.[KPICATALOG]
  where [KPICATALOG].[CONTEXTRECORDTYPEID] = @KPIRECORDTYPE
  and (@CURRENTAPPUSERID is null or dbo.[UFN_SECURITY_APPUSER_GRANTED_KPI](@CURRENTAPPUSERID, [KPICATALOG].[ID]) = 1)
  and (isnull([SPECXML].value(
        'declare namespace bbafx="bb_appfx_kpi";
         declare namespace c="bb_appfx_commontypes";
         (bbafx:KpiSpec/c:MetaTags/@MarketingMembershipMailingKPI)[1]', 'tinyint')
       , 0) = 0 or @INCLUDEMEMBERSHIPRENEWALEFFORTKPIS = 1)
  order by [KPICATALOG].[NAME];

  return 0;