UFN_MKTSEGMENTATIONSEGMENT_GETSOURCEQUERYVIEWCATALOGID

CREATE function dbo.[UFN_MKTSEGMENTATIONSEGMENT_GETSOURCEQUERYVIEWCATALOGID]
(
  @SEGMENTATIONSEGMENTID uniqueidentifier
)
returns uniqueidentifier
as
  begin
    declare @SEGMENTID uniqueidentifier;
    declare @QUERYVIEWCATALOGID uniqueidentifier;
    declare @MARKETINGRECORDTYPE tinyint;
    declare @SEGMENTTYPECODE tinyint;

    set @QUERYVIEWCATALOGID = null;

    select
      @SEGMENTID = [MKTSEGMENT].[ID],
      @MARKETINGRECORDTYPE = dbo.[UFN_MKTSEGMENT_GETMARKETINGRECORDTYPE]([MKTSEGMENT].[ID]),
      @SEGMENTTYPECODE = [MKTSEGMENT].[SEGMENTTYPECODE]
    from dbo.[MKTSEGMENTATIONSEGMENT] 
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTATIONSEGMENTID;

    if @MARKETINGRECORDTYPE = 1 -- house file

      select
        @QUERYVIEWCATALOGID = case @SEGMENTTYPECODE when 1 then [MKTRECORDSOURCE].[ID]
                                                    when 3 then [MKTGIFTRECORDSOURCE].[QUERYVIEWCATALOGID]
                                                    when 4 then [MKTMEMBERSHIPRECORDSOURCE].[QUERYVIEWCATALOGID]
                                                    when 5 then [MKTSPONSORSHIPRECORDSOURCE].[QUERYVIEWCATALOGID] end
      from dbo.[MKTRECORDSOURCE]
      left outer join dbo.[MKTGIFTRECORDSOURCE] on [MKTGIFTRECORDSOURCE].[ID] = [MKTRECORDSOURCE].[ID]
      left outer join dbo.[MKTMEMBERSHIPRECORDSOURCE] on [MKTMEMBERSHIPRECORDSOURCE].[ID] = [MKTRECORDSOURCE].[ID]
      left outer join dbo.[MKTSPONSORSHIPRECORDSOURCE] on [MKTSPONSORSHIPRECORDSOURCE].[ID] = [MKTRECORDSOURCE].[ID]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[QUERYVIEWCATALOGID] = [MKTRECORDSOURCE].[ID]
      where [MKTSEGMENT].[ID] = @SEGMENTID

    else if @MARKETINGRECORDTYPE = 2 -- list, public media

      select
        @QUERYVIEWCATALOGID = coalesce([MKTSEGMENTLISTACTIVE].[QUERYVIEWCATALOGID], [MKTSEGMENTLISTINACTIVE].[QUERYVIEWCATALOGID], [MKTSEGMENTLISTPARENTACTIVE].[QUERYVIEWCATALOGID], [MKTSEGMENTLISTPARENTINACTIVE].[QUERYVIEWCATALOGID])
      from dbo.[MKTSEGMENTATIONSEGMENT] 
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      left outer join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
      left outer join dbo.[MKTSEGMENTLIST] as [MKTSEGMENTLISTACTIVE] on [MKTSEGMENTLISTACTIVE].[ID] = [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID]
      left outer join dbo.[MKTSEGMENTLIST] as [MKTSEGMENTLISTPARENTACTIVE] on [MKTSEGMENTLISTPARENTACTIVE].[SEGMENTID] = [MKTSEGMENTLISTACTIVE].[PARENTSEGMENTID]
      -- public media segments have no members and have no rows in MKTSEGMENTLIST; this inner join takes care of them

      inner join dbo.[MKTSEGMENTLIST] as [MKTSEGMENTLISTINACTIVE] on [MKTSEGMENTLISTINACTIVE].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
      left outer join dbo.[MKTSEGMENTLIST] as [MKTSEGMENTLISTPARENTINACTIVE] on [MKTSEGMENTLISTPARENTINACTIVE].[SEGMENTID] = [MKTSEGMENTLISTINACTIVE].[PARENTSEGMENTID]
      where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTATIONSEGMENTID
      and [MKTSEGMENTLISTINACTIVE].[TYPECODE] <> 1  -- vendor managed segments have no members


  else if @MARKETINGRECORDTYPE = 3 -- consolidated

    select
      @QUERYVIEWCATALOGID = dbo.[UFN_MKTRECORDSOURCE_GETCONSOLIDATEDQUERYVIEWID]([MKTSEGMENT].[QUERYVIEWCATALOGID])
    from dbo.[MKTSEGMENT] where [ID] = @SEGMENTID;

  return @QUERYVIEWCATALOGID;
end