UFN_MKTSEGMENTATIONPASSIVE_GETACTIVATIONSOURCES

create function dbo.[UFN_MKTSEGMENTATIONPASSIVE_GETACTIVATIONSOURCES]
(
  @SEGMENTATIONID uniqueidentifier
)
returns table
as
  return 
  (
    select
      [MKTRECORDSOURCE].[ID] as [RECORDSOURCEID],
      [QUERYVIEWCATALOG].[DISPLAYNAME] as [RECORDSOURCEDISPLAYNAME],
      [MKTGIFTRECORDSOURCE].[QUERYVIEWCATALOGID] as [GIFTRECORDSOURCEID],
      [MKTAPPEALRECORDSOURCE].[QUERYVIEWCATALOGID] as [APPEALRECORDSOURCEID],
      [MKTAPPEALRECORDSOURCE].[APPEALIDFIELD] as [APPEALRECORDSOURCEAPPEALIDFIELD],
      [MKTAPPEALRECORDSOURCE].[DESCRIPTIONFIELD] as [APPEALRECORDSOURCEDESCRIPTIONFIELD],
      [MKTGIFTRECORDSOURCE].[APPEALSYSTEMIDFIELD],
      [MKTGIFTRECORDSOURCE].[APPEALIDFIELD],
      [MKTGIFTRECORDSOURCE].[APPEALDESCRIPTIONFIELD],
      [MKTGIFTRECORDSOURCE].[MAILINGIDFIELD],
      [MKTAPPEALRECORDSOURCE].[SEARCHLISTCATALOGID],
      dbo.[UFN_QUERYVIEW_GETFIELDCAPTION]([GIFTQUERYVIEWCATALOG].[ID], [MKTGIFTRECORDSOURCE].[APPEALIDFIELD]) as [APPEALIDFIELDDISPLAYNAME],
      dbo.[UFN_QUERYVIEW_GETFIELDCAPTION]([GIFTQUERYVIEWCATALOG].[ID], [MKTGIFTRECORDSOURCE].[APPEALDESCRIPTIONFIELD]) as [APPEALDESCRIPTIONFIELDDISPLAYNAME],
      [MKTSEGMENTATIONACTIVATE].[NORMALGIFTIDSETREGISTERID],
      (select [DBOBJECTNAME] from dbo.[IDSETREGISTER] where [ID] = [MKTSEGMENTATIONACTIVATE].[NORMALGIFTIDSETREGISTERID]) as [NORMALGIFTIDSETREGISTEROBJECTNAME],
      [MKTSEGMENTATIONACTIVATE].[UNRESOLVEDGIFTIDSETREGISTERID],
      (select [DBOBJECTNAME] from dbo.[IDSETREGISTER] where [ID] = [MKTSEGMENTATIONACTIVATE].[UNRESOLVEDGIFTIDSETREGISTERID]) as [UNRESOLVEDGIFTIDSETREGISTEROBJECTNAME],
      [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID],
      case when [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] <> '' and dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID]) = 1
           then (select [NAME] from dbo.[APPEAL] where [ID] = cast([MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] as uniqueidentifier))
           else [MKTSEGMENTATIONACTIVATE].[APPEALID]
      end as [APPEALID],
      case when [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] <> '' and dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID]) = 1
           then (select [DESCRIPTION] from dbo.[APPEAL] where [ID] = cast([MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] as uniqueidentifier))
           else [MKTSEGMENTATIONACTIVATE].[APPEALDESCRIPTION]
      end as [APPEALDESCRIPTION],
      case when dbo.[UFN_FIELD_GETTYPE]([MKTGIFTRECORDSOURCE].[MAILINGIDFIELD], (select [OBJECTNAME] from [QUERYVIEWCATALOG] where [ID] = [MKTGIFTRECORDSOURCE].[QUERYVIEWCATALOGID])) = 'uniqueidentifier'
           then convert(nvarchar(36), [MKTSEGMENTATION].[ID])
           else convert(nvarchar(36), [MKTSEGMENTATION].[IDINTEGER])
      end as [MAILINGID],
      '' as [PLANAPPEALID],
      '' as [PLANAPPEALNAME],
      [MKTGIFTRECORDSOURCE].[SOURCECODEFIELD],
      [MKTGIFTRECORDSOURCE].[FINDERNUMBERFIELD],
      [MKTGIFTRECORDSOURCE].[DONORIDFIELD],
      [GIFTQUERYVIEWCATALOG].[OBJECTNAME] as [GIFTQUERYVIEWNAME],
      [GIFTQUERYVIEWCATALOG].[RECORDTYPEID],
      [GIFTQUERYVIEWCATALOG].[PRIMARYKEYFIELD] as [GIFTQUERYVIEWPRIMARYKEY],
      [GIFTQUERYVIEWCATALOG].[PRIMARYKEYTYPENAME] as [GIFTQUERYVIEWPRIMARYKEYTYPENAME],
      [MKTGIFTRECORDSOURCE].[AMOUNTFIELD],
      [MKTGIFTRECORDSOURCE].[DATEFIELD],
      dbo.[UFN_MKTRECORDSOURCE_MAKEMATCHBACKTABLENAME]([MKTRECORDSOURCE].[ID]) as [LISTMATCHBACKTABLENAME],
      dbo.[UFN_CONSTITUENTSEGMENT_MAKETABLENAME]([MKTRECORDSOURCE].[ID]) as [CONSTITUENTSEGMENTTABLENAME]
    from dbo.[MKTRECORDSOURCE]
    inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTRECORDSOURCE].[ID]
    left outer join dbo.[MKTGIFTRECORDSOURCE] on [MKTGIFTRECORDSOURCE].[ID] = [QUERYVIEWCATALOG].[ID]
    left outer join dbo.[MKTAPPEALRECORDSOURCE] on [MKTAPPEALRECORDSOURCE].[ID] = [QUERYVIEWCATALOG].[ID]
    left outer join dbo.[QUERYVIEWCATALOG] as [GIFTQUERYVIEWCATALOG] on [GIFTQUERYVIEWCATALOG].[ID] = [MKTGIFTRECORDSOURCE].[QUERYVIEWCATALOGID] 
    left outer join dbo.[QUERYVIEWCATALOG] as [APPEALQUERYVIEWCATALOG] on [APPEALQUERYVIEWCATALOG].[ID] = [MKTAPPEALRECORDSOURCE].[QUERYVIEWCATALOGID] 
    left outer join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = @SEGMENTATIONID and [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = [QUERYVIEWCATALOG].[ID]
    left outer join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = @SEGMENTATIONID
    where dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([QUERYVIEWCATALOG].[ID]) = 1
  );