UFN_MKTCOMMUNICATIONTEMPLATEDEFAULTSOLICITCODES_GETEXCLUDESOLICITCODES

CREATE function dbo.[UFN_MKTCOMMUNICATIONTEMPLATEDEFAULTSOLICITCODES_GETEXCLUDESOLICITCODES]
(
  @COMMUNICATIONTEMPLATEID uniqueidentifier
)
returns table
as
  return 
  (
    select 
    [ID],
    [ID] as [SOLICITCODEID],
  cast(1 as bit) as [REQUIRED],
  cast(1 as bit) as [LOCKED]
from dbo.[SOLICITCODE]
where [EXCLUSIONCODE] = 2 -- required

and [ACTIVE] = 1

union all

select 
  [ID],
  [SOLICITCODEID],
  cast(0 as bit) as [REQUIRED],
  [LOCKED]
from dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTSOLICITCODES]
where [COMMUNICATIONTEMPLATEID] = @COMMUNICATIONTEMPLATEID
and [SOLICITCODEID] not in (select
                                            [ID]
                                        from dbo.[SOLICITCODE]
                                        where [EXCLUSIONCODE] = 2 and [ACTIVE] = 1)
  )