UFN_QUERY_MKTSEGMENT

CREATE function dbo.[UFN_QUERY_MKTSEGMENT]
(
  @CURRENTAPPUSERID uniqueidentifier,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null
)
returns table as
return 
(
  select
    [MKTSEGMENT].[ID],
    [MKTSEGMENT].[NAME] as [SEGMENTNAME],
    (select stuff((select ', ' + [MKTSEGMENTGROUP].[NAME]
                             from dbo.[MKTSEGMENTGROUP]
                             inner join dbo.[MKTGROUPSEGMENTS] on [MKTGROUPSEGMENTS].[SEGMENTGROUPID] = [MKTSEGMENTGROUP].[ID]
                             where [MKTGROUPSEGMENTS].[SEGMENTID] = [MKTSEGMENT].[ID]
                             order by [MKTSEGMENTGROUP].[NAME]
                             for xml path(''), type
                             ).value('.', 'varchar(max)')
                             , 1, 2, '')) as [GROUPNAME],
    [MKTSEGMENT].[CODE],
    [MKTSEGMENT].[DESCRIPTION],
    cast(
      case
          when exists(select top 1 1 from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTID] = [MKTSEGMENT].[ID])
            or exists(select top 1 1 from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] where [SEGMENTID] = [MKTSEGMENT].[ID])
            or exists(select top 1 1 from dbo.[MKTMEMBERSHIPMAILINGTEMPLATERULE] where [SEGMENTID] = [MKTSEGMENT].[ID])
            or exists(select top 1 1 from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE] where [SEGMENTID] = [MKTSEGMENT].[ID])
            then 1 
          else 0
        end as bit
    ) as [INUSE],
    (
        case 
            when [MKTSEGMENT].[ISHISTORICAL] = 1 then [MKTSEGMENT].[HISTORICALQUANTITY]
            when [MKTSEGMENT].[SEGMENTTYPECODE] <> 2 then [IDSETREGISTER].[NUMROWS]
            else [MKTSEGMENTLIST].[TOTALRECORDCOUNT] 
        end
    ) as [NUMROWS],
    coalesce([MKTSEGMENT].[DATEREFRESHED], [IDSETREGISTER].[DATECHANGED], [MKTSEGMENTLIST].[DATECHANGED], [MKTSEGMENT].[DATECHANGED]) as [DATEREFRESHED],
    [MKTSEGMENTREFRESHPROCESS].[ID] as [SEGMENTREFRESHPROCESSID],
    [MKTSEGMENT].[SEGMENTTYPECODE],
    [MKTSEGMENT].[SEGMENTTYPE] + (case when [MKTSEGMENT].[ISHISTORICAL] = 1 then ' (Historical)' else '' end) as [SEGMENTTYPE],
    [MKTSEGMENTCATEGORYCODE].[DESCRIPTION] as [CATEGORY],
    cast((case when [MKTSEGMENTLIST].[PARENTSEGMENTID] is null then 0 else 1 end) as bit) as [ISCHILDLISTSEGMENT],
    isnull([SITE].[NAME], '') as [SITE],
    [MKTSEGMENT].[ISHISTORICAL] as [ISHISTORICAL]
    /*#EXTENSION*/
  from
    dbo.[MKTSEGMENT]
    left join dbo.[MKTSEGMENTLIST] ON [MKTSEGMENT].[CURRENTSEGMENTLISTID] = [MKTSEGMENTLIST].[ID]
    left join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
    left join dbo.[MKTSEGMENTCATEGORYCODE] on [MKTSEGMENTCATEGORYCODE].[ID] = [MKTSEGMENT].[SEGMENTCATEGORYCODEID]
    left join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
    left join dbo.[MKTSEGMENTREFRESHPROCESS] on [MKTSEGMENTREFRESHPROCESS].[SEGMENTID] = [MKTSEGMENT].[ID]
    left outer join dbo.[SITE] on [SITE].[ID] = [MKTSEGMENT].[SITEID]
  where
    ([MKTSEGMENTLIST].[ID] is null or [MKTLIST].[ISINACTIVE] = 0)
    and [MKTSEGMENT].[SEGMENTTYPECODE] not in (6, 7, 8, 9)
    /* Exclude System records */
    and [MKTSEGMENT].[ISSYSTEM] = 0
    and (
      (select [ISSYSADMIN] from dbo.[APPUSER] where [ID] = @CURRENTAPPUSERID) = 1
      or
      exists(
        select 1
        from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID, '4f668d50-1c13-4545-abec-4a4eccff1d63', 10)
        where [SITEID] = [MKTSEGMENT].[SITEID] or ([SITEID] is null and [MKTSEGMENT].[SITEID] is null)
      )
    )
    and (@SITEFILTERMODE = 0 or [MKTSEGMENT].[SITEID] in (select [SITEID] from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER](@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)))
)