UFN_MKTCOMMON_SPONSORSHIPMAILINGSINSTALLED

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

  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] = '56dd6bf2-c6a8-431d-9b66-fd57c15dba30';

      --Check the product flags on the task spec to make sure sponsorship mailing functionality only shows up for the appropriate installed products.

      --This normally would not be necessary, but some sponsorship 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