UFN_MKTSEGMENTPASSIVE_GETDATALIST

CREATE function dbo.[UFN_MKTSEGMENTPASSIVE_GETDATALIST]
(
  @SEGMENTTYPECODE tinyint = null,
  @GROUPID uniqueidentifier = null,
  @SEGMENTCATEGORYCODEID uniqueidentifier = null,
  @VENDORID uniqueidentifier = null
)
returns table
as
  return
  (
    select
      [MKTSEGMENT].[ID],
      [MKTSEGMENT].[NAME],
      [MKTSEGMENT].[SEGMENTTYPECODE],
      [MKTSEGMENT].[SEGMENTTYPE],
      [MKTSEGMENTGROUP].[ID] as [GROUPID],
      [MKTSEGMENTGROUP].[NAME] as [GROUP],
      [MKTSEGMENTCATEGORYCODE].[DESCRIPTION] as [CATEGORY],
      [MKTSEGMENT].[CODE],
      [VENDOR].[NAME] as [VENDOR],
      [MEDIAOUTLET].[NAME] as [MEDIAOUTLET],
      [MKTSEGMENTPASSIVE].[SCHEDULESTARTTIME] as [STARTTIME],
      dbo.[UFN_MKTSEGMENTPASSIVE_GETDURATION]([MKTSEGMENTPASSIVE].[SCHEDULEDURATION]) as [DURATION],
      [MKTSEGMENTPASSIVE].[IMPRESSIONS],
      lower([MKTSEGMENTPASSIVE].[IMPRESSIONCALCULATIONMETHOD]) as [IMPRESSIONCALCULATIONMETHOD],
      dbo.[UFN_MKTSEGMENT_ISINUSE]([MKTSEGMENT].[ID]) as [INUSE],
      convert(bit, case when [MKTSEGMENTPASSIVE].[INACTIVE] = 1 then 0 else 1 end) as [ISACTIVE],
      [MKTSEGMENT].[SITEID],
      [SITE].[NAME] as [SITE],
      [MKTSEGMENT].[DESCRIPTION],
      [MKTGROUPSEGMENTS].[SEQUENCE]
    from dbo.[MKTSEGMENT]
    inner join dbo.[MKTSEGMENTPASSIVE] on [MKTSEGMENTPASSIVE].[ID] = [MKTSEGMENT].[ID]
    left outer join dbo.[MKTGROUPSEGMENTS] on [MKTSEGMENT].[ID] = [MKTGROUPSEGMENTS].[SEGMENTID]
    left outer join dbo.[MKTSEGMENTGROUP] on [MKTGROUPSEGMENTS].[SEGMENTGROUPID] = [MKTSEGMENTGROUP].[ID]
    left outer join dbo.[MKTSEGMENTCATEGORYCODE] on [MKTSEGMENTCATEGORYCODE].[ID] = [MKTSEGMENT].[SEGMENTCATEGORYCODEID]
    left outer join dbo.[CONSTITUENT] as [VENDOR] on [VENDOR].[ID] = [MKTSEGMENTPASSIVE].[VENDORID]
    left outer join dbo.[MKTSEGMENT] as [MEDIAOUTLET] on [MEDIAOUTLET].[ID] = [MKTSEGMENTPASSIVE].[PARENTMEDIAOUTLETSEGMENTID]
    left outer join dbo.[SITE] on [SITE].[ID] = [MKTSEGMENT].[SITEID]
    where (@SEGMENTTYPECODE is null or [MKTSEGMENT].[SEGMENTTYPECODE] = @SEGMENTTYPECODE)
    and (@GROUPID is null or [MKTSEGMENTGROUP].[ID] = @GROUPID)
    and (@SEGMENTCATEGORYCODEID is null or [MKTSEGMENT].[SEGMENTCATEGORYCODEID] = @SEGMENTCATEGORYCODEID)
    and (@VENDORID is null or ([MKTSEGMENT].[SEGMENTTYPECODE] in (6, 8) and [MKTSEGMENTPASSIVE].[VENDORID] = @VENDORID))
  );