USP_MKTCOMMUNICATIONTEMPLATEKPIS_GETDEFAULTS

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

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

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

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

  return 0;