UFN_MKTSEGMENTATIONACTIVATE_GETFIELDS

CREATE function dbo.[UFN_MKTSEGMENTATIONACTIVATE_GETFIELDS]
(
  @SEGMENTATIONID uniqueidentifier
)
returns table
as
  return 
  (
    select
      [SEGMENTRECORDSOURCES].[QUERYVIEWCATALOGID] 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],
      [APPEALPLANITEM].[APPEALID] as [PLANAPPEALID],
      [APPEALPLANITEM].[NAME] 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]([SEGMENTRECORDSOURCES].[QUERYVIEWCATALOGID]) as [LISTMATCHBACKTABLENAME],
      dbo.[UFN_CONSTITUENTSEGMENT_MAKETABLENAME]([SEGMENTRECORDSOURCES].[QUERYVIEWCATALOGID]) as [CONSTITUENTSEGMENTTABLENAME],
      [MKTAPPEALRECORDSOURCE].[DESCRIPTIONFIELD] as [SEARCHLISTDESCRIPTIONFIELD]
    from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID) as [SEGMENTRECORDSOURCES]
    inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [SEGMENTRECORDSOURCES].[QUERYVIEWCATALOGID]
    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
    left outer join dbo.[MKTMARKETINGPLANITEM] on [MKTMARKETINGPLANITEM].[ID] = [MKTSEGMENTATION].[MARKETINGPLANITEMID]
    left outer join dbo.[MKTMARKETINGPLANITEM] [APPEALPLANITEM] on [APPEALPLANITEM].[ID] = [MKTMARKETINGPLANITEM].[PARENTMARKETINGPLANITEMID]
  );