USP_MKTSEGMENTATIONACTIVATEKPI_GETDEFAULTS

create procedure dbo.[USP_MKTSEGMENTATIONACTIVATEKPI_GETDEFAULTS]
(
  @MAILINGTYPECODE tinyint,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  declare @KPIRECORDTYPE uniqueidentifier;
  declare @KPINAMESUFFIX nvarchar(50);
  declare @MEMBERSHIPKPINAMESUFFIX 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';
  set @MEMBERSHIPKPINAMESUFFIX = ' for Membership Effort';

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

  select
    [KPICATALOG].[ID],
    cast(case when exists(select top 1 1 from @DEFAULTS as [D] where [D].[KPICATALOGID] = [KPICATALOG].[ID])
              then 1
              else 0
         end as bit) as [SELECTED],
    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],
    [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
    or
    [SPECXML].value(
    'declare namespace bbafx="bb_appfx_kpi";
     declare namespace c="bb_appfx_commontypes";
     (bbafx:KpiSpec/c:MetaTags/@MarketingMembershipMailingKPI)[1]', 
    'char(1)') = (case when @MAILINGTYPECODE = 2 then '1' else '0' end)
    )
  order by [KPICATALOG].[NAME];

  return 0;