USP_DATALIST_MKTCOMMUNICATIONEFFORTSEEDS

CREATE procedure dbo.USP_DATALIST_MKTCOMMUNICATIONEFFORTSEEDS
(
  @SEGMENTATIONID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier
)
as
  set nocount on;

  with [DEFAULTSEEDS_CTE] ([SEEDID], [LOCKED]) as
  (
    select
      [MKTCOMMUNICATIONTEMPLATEDEFAULTSEED].[SEEDID],
      case when [MKTCOMMUNICATIONTEMPLATEDEFAULT].[SEEDSLOCKED] = 1 or [MKTCOMMUNICATIONTEMPLATEDEFAULTSEED].[LOCKED] = 1 then 1 else 0 end [LOCKED]
    from dbo.[MKTCOMMUNICATIONTEMPLATE]
    left join dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULT] on [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID]
    left join dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTSEED] on [MKTCOMMUNICATIONTEMPLATEDEFAULTSEED].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID]
    where 
      [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID] = @SEGMENTATIONID
  )
  select
    [MKTSEGMENTATIONSEED].[ID],
    [EFFORTSEEDS].[ID] [SEEDID],
    [MKTSEED].[NAME],
    replace(dbo.[UFN_BUILDFULLADDRESS](NULL, [EFFORTSEEDS].[ADDRESSBLOCK], [EFFORTSEEDS].[CITY], [MKTSEED].[STATEID], [EFFORTSEEDS].[POSTCODE], [MKTSEED].[COUNTRYID]),nchar(13)+nchar(10),' ') as [ADDRESSBLOCK],
    [EFFORTSEEDS].[PHONENUMBER],
    [EFFORTSEEDS].[EMAILADDRESS],
    case when [DEFAULTSEEDS_CTE].[LOCKED] = 1 then 1 else 0 end as [LOCKED],
    '' as [LOCKEDIMAGE],
    case 
      when [DEFAULTSEEDS_CTE].[LOCKED] = 1 then 'catalog:Blackbaud.AppFx.Marketing.Catalog,Blackbaud.AppFx.Marketing.Catalog.padlock_locked_16.png' 
      else '' end as [IMAGEKEY]
  from
    dbo.[UFN_MKTSEGMENTATIONSEED_GETSEEDS_2](@SEGMENTATIONID, @CURRENTAPPUSERID, 0, '7600a31e-ca58-4099-942d-ef882b9f52fc', 2) [EFFORTSEEDS]
    inner join dbo.[MKTSEGMENTATIONSEED] on dbo.[MKTSEGMENTATIONSEED].[SEEDID] = [EFFORTSEEDS].[ID] and [MKTSEGMENTATIONSEED].[SEGMENTATIONID] = @SEGMENTATIONID
    inner join dbo.[MKTSEED] on [EFFORTSEEDS].[ID] = [MKTSEED].[ID]
    left join [DEFAULTSEEDS_CTE] on [DEFAULTSEEDS_CTE].[SEEDID] = [EFFORTSEEDS].[ID]
  order by
    [MKTSEED].[LASTNAME]