USP_DATAFORMTEMPLATE_EDITLOAD_MKTCOMMUNICATIONEFFORTKPIS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_MKTCOMMUNICATIONEFFORTKPIS
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @TSLONG bigint = null output,
  @DATALOADED bit = null output,
  @KPIS xml = null output,
  @SITEID uniqueidentifier = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @HASCOMMUNICATIONTEMPLATE bit = null output
)
as

  set nocount on;

  set @DATALOADED = 0;
  set @HASCOMMUNICATIONTEMPLATE = 0;

  declare @TEMPLATETYPECODE tinyint;
  declare @KPINAMESUFFIX nvarchar(50) = ' for Marketing Effort';
  declare @MEMBERSHIPKPINAMESUFFIX nvarchar(50) = ' for Membership Effort';

  select
    @TSLONG = [TSLONG],
    @DATALOADED = 1,
    @TEMPLATETYPECODE = (select [TEMPLATETYPECODE] from dbo.[MKTCOMMUNICATIONTEMPLATE] where [MKTSEGMENTATIONID] = @ID),
    @SITEID = [SITEID],
    @BASECURRENCYID = [BASECURRENCYID]
  from
    dbo.[MKTSEGMENTATION]
  where
    [ID] = @ID;

  if @DATALOADED = 1
  begin

    select
      @HASCOMMUNICATIONTEMPLATE = cast(
        case when
          [MKTCOMMUNICATIONTEMPLATE].[ID] is not null
        then 1 else 0 end as bit)
    from
      dbo.[MKTSEGMENTATION]
        left join dbo.[MKTCOMMUNICATIONTEMPLATE] on [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    where
      [MKTSEGMENTATION].[ID] = @ID;


    set @KPIS = (
      select 
        [KPICATALOGID],
        [LOCKED],
        @TEMPLATETYPECODE [TEMPLATETYPECODE]
      from (
        select
          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],
          [MKTSEGMENTATIONACTIVATEKPI].[KPICATALOGID],
          cast(isnull((select [MKTCOMMUNICATIONTEMPLATEDEFAULTKPI].[LOCKED] from dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTKPI] inner join dbo.[MKTCOMMUNICATIONTEMPLATE] on [MKTCOMMUNICATIONTEMPLATEDEFAULTKPI].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID] and [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID] = @ID where [KPICATALOGID] = [KPICATALOG].[ID]),0) as bit) as [LOCKED]
        from
          dbo.[MKTSEGMENTATIONACTIVATEKPI]
          inner join dbo.[KPICATALOG] on [MKTSEGMENTATIONACTIVATEKPI].[KPICATALOGID] = [KPICATALOG].[ID]
          inner join dbo.[KPIINSTANCE] on [KPICATALOG].[ID] = [KPIINSTANCE].[KPICATALOGID] and [KPIINSTANCE].[CONTEXTRECORDID] = cast(@ID as nvarchar(100))
        where
          [MKTSEGMENTATIONACTIVATEKPI].[SEGMENTATIONID] = @ID
      ) [KPITABLE]
      order by [KPITABLE].[NAME]
      for xml raw('ITEM'), root('KPIS'), binary base64);
  end

  return 0;