USP_MKTACKNOWLEDGEMENTMAILINGTEMPLATEKPI_GETFIELDS

CREATE procedure dbo.[USP_MKTACKNOWLEDGEMENTMAILINGTEMPLATEKPI_GETFIELDS]
(
  @ACKNOWLEDGEMENTTEMPLATEID uniqueidentifier,
  @SELECTEDONLY bit,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  declare @KPIRECORDTYPE uniqueidentifier;
  declare @KPINAMESUFFIX nvarchar(50);
  declare @DEFAULTS table([KPICATALOGID] uniqueidentifier primary key);

  --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 @KPINAMESUFFIX = ' for Marketing Effort';

  insert into @DEFAULTS
    select [KPICATALOGID]
    from dbo.[MKTSEGMENTATIONACTIVATEDEFAULTKPI]
    where [MAILINGTYPECODE] = 1;

  select
    [KPICATALOG].[ID],
    cast (case @SELECTEDONLY
            when 0 then
              case when exists(select top 1 1 from @DEFAULTS as [D] where [D].[KPICATALOGID] = [KPICATALOG].[ID])
                   then 1
                   else 0
              end
            when 1 then
              case when exists(select top 1 1 from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATEKPI] where [KPICATALOGID] = [KPICATALOG].[ID] and [ACKNOWLEDGEMENTTEMPLATEID] = @ACKNOWLEDGEMENTTEMPLATEID)
                   then 1
                   else 0
              end
          end as bit) as [SELECTED],
    case when [KPICATALOG].[NAME] like '%' + @KPINAMESUFFIX 
         then left([KPICATALOG].[NAME], len([KPICATALOG].[NAME]) - len(@KPINAMESUFFIX))
         else [KPICATALOG].[NAME] 
    end as [NAME],
    [KPICATALOG].[GOALTYPECODE],
    cast (case when exists(select 1 from @DEFAULTS as [D] where [D].[KPICATALOGID] = [KPICATALOG].[ID])
              then 1
              else 0
          end as bit) as [DEFAULT]
  from dbo.[KPICATALOG]
  where 
    [KPICATALOG].[CONTEXTRECORDTYPEID] = @KPIRECORDTYPE
    and (@CURRENTAPPUSERID is null or dbo.[UFN_SECURITY_APPUSER_GRANTED_KPI](@CURRENTAPPUSERID, [KPICATALOG].[ID]) = 1)
  and 
    (
    [SPECXML].value(
    'declare namespace bbafx="bb_appfx_kpi";
     declare namespace c="bb_appfx_commontypes";
     (bbafx:KpiSpec/c:MetaTags/@MarketingMembershipMailingKPI)[1]', 
    'char(1)') is null
    )
  order by [KPICATALOG].[NAME];

  return 0;