USP_DATAFORMTEMPLATE_VIEW_COMMUNICATIONEFFORTBUSINESSUNITS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_COMMUNICATIONEFFORTBUSINESSUNITS
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @CURRENTAPPUSERID uniqueidentifier,
  @BUSINESSUNITS xml = null output,
  @BUSINESSUNITSLOCKED bit = null output
)
as
  set nocount on;

  declare @OVERRIDEBUSINESSUNITS bit;
  declare @ISBBEC bit = (case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end);

  set @DATALOADED = 0;

  select
    @DATALOADED = 1,
    @BUSINESSUNITSLOCKED = isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[BUSINESSUNITSLOCKED], 0),
    @BUSINESSUNITS = dbo.[UFN_MKTSEGMENTATIONBUSINESSUNIT_GETBUSINESSUNITS_TOITEMLISTXML](@ID),
    @OVERRIDEBUSINESSUNITS = isnull([MKTSEGMENTATION].[OVERRIDEBUSINESSUNITS], 0)
  from
    dbo.[MKTSEGMENTATION]
      left join dbo.[MKTCOMMUNICATIONTEMPLATE] on [MKTSEGMENTATION].[ID] = [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID]
      left join dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULT] on [MKTCOMMUNICATIONTEMPLATE].[ID] = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONTEMPLATEID]
  where
    [MKTSEGMENTATION].[ID] = @ID;

  if @DATALOADED = 1
  begin
    if @ISBBEC = 1 and @OVERRIDEBUSINESSUNITS = 0
    begin
      declare @APPEALSYSTEMID uniqueidentifier;

      select
        @APPEALSYSTEMID = convert(uniqueidentifier, [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID])
      from
        dbo.[MKTSEGMENTATIONACTIVATE]
      where
        [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = @ID
        and [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] <> ''
        and dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID]) = 1;

      if @APPEALSYSTEMID is not null
        set @BUSINESSUNITS = dbo.[UFN_APPEALBUSINESSUNIT_GETBUSINESSUNITS_TOITEMLISTXML](@APPEALSYSTEMID);
    end
  end

  return 0;