USP_DATALIST_MKTSEGMENTBYGROUP

CREATE procedure dbo.[USP_DATALIST_MKTSEGMENTBYGROUP]
(
  @SEGMENTID uniqueidentifier = null,
  @GROUPID uniqueidentifier = null,
  @SEGMENTTYPECODE tinyint = null,
  @SEGMENTCATEGORYCODEID uniqueidentifier = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @INCLUDEHISTORICAL bit = null
)
as
  set nocount on;

with [INUSE_CTE] ([SEGMENTID], [INUSE]) as (
  select [MKTSEGMENT].[ID], 
  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]
  from dbo.[MKTSEGMENT]
)
  select
    [MKTSEGMENT].[ID] as [SEGMENTID],
    [MKTSEGMENT].[NAME] as [SEGMENTNAME],
    [MKTSEGMENTGROUP].[ID] as [GROUPID],
    [MKTSEGMENTGROUP].[NAME] as [GROUPNAME],
    [MKTSEGMENT].[CODE],
    [MKTSEGMENT].[DESCRIPTION],
    [INUSE_CTE].[INUSE],--replaced dbo.[UFN_MKTSEGMENT_ISINUSE]([MKTSEGMENT].[ID]) 

    (
        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]
  from dbo.[MKTSEGMENT]
  inner join [INUSE_CTE] on [MKTSEGMENT].[ID]=[INUSE_CTE].[SEGMENTID]
  left join dbo.[MKTSEGMENTLIST] ON [MKTSEGMENT].[CURRENTSEGMENTLISTID] = [MKTSEGMENTLIST].[ID]
  left join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
  left join dbo.[MKTGROUPSEGMENTS] on [MKTSEGMENT].[ID] = [MKTGROUPSEGMENTS].[SEGMENTID]
  left join dbo.[MKTSEGMENTGROUP] on [MKTGROUPSEGMENTS].[SEGMENTGROUPID] = [MKTSEGMENTGROUP].[ID]
  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)
  and (@SEGMENTTYPECODE is null or [MKTSEGMENT].[SEGMENTTYPECODE] = @SEGMENTTYPECODE)
  and (@GROUPID is null or [MKTSEGMENTGROUP].[ID] = @GROUPID)
  and (@SEGMENTCATEGORYCODEID is null or [MKTSEGMENT].[SEGMENTCATEGORYCODEID] = @SEGMENTCATEGORYCODEID)
  and (@SEGMENTID is null or [MKTSEGMENT].[ID] = @SEGMENTID)
  /* Exclude appeal mailing segments */
  and not exists(select 1
                 from dbo.[APPEALMAILING]
                 inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [APPEALMAILING].[MKTSEGMENTATIONSEGMENTID]
                 where [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = [MKTSEGMENT].[ID])
  /* Exclude System records */
  and [MKTSEGMENT].[ISSYSTEM] = 0
  and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) 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)))
  and ((@INCLUDEHISTORICAL = 1 or @INCLUDEHISTORICAL is null ) or (@INCLUDEHISTORICAL = 0 and [MKTSEGMENT].[ISHISTORICAL] = 0))
  order by [MKTSEGMENTGROUP].[NAME], [MKTGROUPSEGMENTS].[SEQUENCE], [MKTSEGMENT].[NAME];

  return 0;