UFN_MKTCOMMON_ACKNOWLEDGEMENTMAILINGSINSTALLED

CREATE function dbo.[UFN_MKTCOMMON_ACKNOWLEDGEMENTMAILINGSINSTALLED]()
returns bit
as
  begin
      declare @INSTALLED bit =
      convert(bit,    
              case when exists (select top 1 1 from dbo.[TASKCATALOG] where [ID] = '3A7BA812-D697-4005-9A91-191F50BD87B4') then 1 else 0 end);

      if @INSTALLED = 1
      begin
        declare @TASKSPECXML xml;

        select @TASKSPECXML = [TASKSPECXML]
        from dbo.[TASKCATALOG]
        where [ID] = '3A7BA812-D697-4005-9A91-191F50BD87B4';

        --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;