UFN_MKTCOMMON_MEMBERSHIPMAILINGSINSTALLED

CREATE function dbo.[UFN_MKTCOMMON_MEMBERSHIPMAILINGSINSTALLED]()
returns bit
as
begin
  declare @INSTALLED bit;

  -- membership mailings can only be used with BBEC installations

  set @INSTALLED = convert(bit, (case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end));

  if @INSTALLED = 1
    -- if this is BBEC, is the task loaded?

    set @INSTALLED = convert(bit, (case when exists (select top 1 1 from dbo.[TASKCATALOG] where [ID] = 'CCDC09EA-60F9-4B3A-AE27-A6F5C9EB58C0') then 1 else 0 end));

  if @INSTALLED = 1
    begin
      declare @TASKSPECXML xml;

      select @TASKSPECXML = [TASKSPECXML]
      from dbo.[TASKCATALOG]
      where [ID] = 'CCDC09EA-60F9-4B3A-AE27-A6F5C9EB58C0';

      -- check the product flags on the task spec to make sure membership mailing functionality only shows up for the appropriate installed products

      -- this normally would not be necessary, but some membership mailing features may have been loaded before product flags were implemented

      set @INSTALLED = (case when exists(
                          select top 1 1
                          from @TASKSPECXML.nodes('declare namespace TS="bb_appfx_task"; declare namespace C="bb_appfx_commontypes"; /TS:TaskSpec/C:InstalledProductList/C:InstalledProduct') T(c)
                          where T.c.value('(@ID)[1]' ,'uniqueidentifier') in (select [ID] from dbo.[INSTALLEDPRODUCTLIST] where [EXPIREDATE] is null or [EXPIREDATE] = '' or cast((left([EXPIREDATE], 4) + '-' + substring([EXPIREDATE], 5, 2) + '-' + substring([EXPIREDATE], 7, 2)) as date) < getdate()))
                        then 1 else 0 end);
    end

  return @INSTALLED;
end