UFN_MKTSEGMENTATION_ISRECORDCOUNTCACHECURRENT

CREATE function dbo.[UFN_MKTSEGMENTATION_ISRECORDCOUNTCACHECURRENT]
(
  @SEGMENTATIONID uniqueidentifier,
  @DETECTEXTERNALCHANGES bit = 1
)
returns bit
as
begin
  declare @ISCURRENT bit = 1;
  declare @MAILINGTYPECODE tinyint;
  declare @ACTIVE bit;
  declare @SEGMENTID uniqueidentifier;

  select
    @MAILINGTYPECODE = [MAILINGTYPECODE],
    @ACTIVE = [ACTIVE]
  from dbo.[MKTSEGMENTATION]
  where [ID] = @SEGMENTATIONID;

  if @MAILINGTYPECODE <> 4 and @ACTIVE = 0
    begin
      --Loop through the segments in reverse order because this gives us a better chance of finding a segment that is not current.

      --Ignore vendor managed list segments because they are always current.

      --Ignore historical segments because they are always current.

      declare SEGMENTS cursor local fast_forward for
        select [MKTSEGMENTATIONSEGMENT].[ID]
        from dbo.[MKTSEGMENTATIONSEGMENT]
        inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
        left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
        where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
        and ([MKTSEGMENT].[SEGMENTTYPECODE] <> 2 or ([MKTSEGMENT].[SEGMENTTYPECODE] = 2 and [MKTSEGMENTLIST].[TYPECODE] = 0))
        and [MKTSEGMENT].[ISHISTORICAL] = 0
        order by [MKTSEGMENTATIONSEGMENT].[SEQUENCE] desc;

      open SEGMENTS;
      fetch next from SEGMENTS into @SEGMENTID;

      while (@@FETCH_STATUS = 0 and @ISCURRENT = 1)
      begin
        select @ISCURRENT = (
          case when exists(select top 1 *
                           from dbo.[MKTSEGMENTATIONSEGMENT]
                           left join dbo.[MKTSEGMENTATIONSEGMENTCACHEINFO] on [MKTSEGMENTATIONSEGMENTCACHEINFO].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
                           where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID
                           and ([MKTSEGMENTATIONSEGMENTCACHEINFO].[RECORDCOUNTDATECACHED] is null or [MKTSEGMENTATIONSEGMENTCACHEINFO].[RECORDCOUNTDATECACHED] < dbo.[UFN_MKTSEGMENTATIONSEGMENT_GETLATESTREFRESHORCHANGEDATE]([MKTSEGMENTATIONSEGMENT].[ID], 1, @DETECTEXTERNALCHANGES)))
          then 0 else 1 end);

        fetch next from SEGMENTS into @SEGMENTID;
      end

      close SEGMENTS;
      deallocate SEGMENTS;
    end

  return @ISCURRENT;
end