UFN_MKTSEGMENTATION_GETREQUIREDANDSELECTEDSOLICITCODEEXCLUSIONS_FROMTEMPLATE

CREATE function dbo.[UFN_MKTSEGMENTATION_GETREQUIREDANDSELECTEDSOLICITCODEEXCLUSIONS_FROMTEMPLATE]
(
  @BUSINESSPROCESSPARAMETERSETID uniqueidentifier -- SEGMENTATIONACTIVATEPROCESSID

)
returns table
as
  return 
  (

    /* Get the solicit codes from the effort and the locked settings from the base template */
    with [EXCLUSIONS_FROMTEMPLATE_CTE] ([SOLICITCODEID], [LOCKED]) as
    (
      select
        [MKTCOMMUNICATIONTEMPLATEDEFAULTSOLICITCODES].[SOLICITCODEID],
        [MKTCOMMUNICATIONTEMPLATEDEFAULTSOLICITCODES].[LOCKED]
      from dbo.[MKTSEGMENTATIONACTIVATEPROCESS]
      inner join dbo.[MKTCOMMUNICATIONTEMPLATE] on [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID] = [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID]
      inner join dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTSOLICITCODES] on [MKTCOMMUNICATIONTEMPLATEDEFAULTSOLICITCODES].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID]
      where [MKTSEGMENTATIONACTIVATEPROCESS].[ID] = @BUSINESSPROCESSPARAMETERSETID
    )
    select 
      [EXCLUSIONS].[ID],
      [EXCLUSIONS].[SOLICITCODEID],
      [EXCLUSIONS_FROMTEMPLATE_CTE].[LOCKED],
      [SOLICITCODE].[DESCRIPTION] as [SOLICITCODENAME]
    from dbo.[UFN_BUSINESSPROCESSCOMMPREF_GETEXCLUSIONS](@BUSINESSPROCESSPARAMETERSETID) as [EXCLUSIONS] 
    left join [EXCLUSIONS_FROMTEMPLATE_CTE] on [EXCLUSIONS_FROMTEMPLATE_CTE].[SOLICITCODEID] = [EXCLUSIONS].[SOLICITCODEID]
    inner join dbo.[SOLICITCODE] on [SOLICITCODE].[ID] = [EXCLUSIONS].[SOLICITCODEID]
    where [SOLICITCODE].[EXCLUSIONCODE] <> 2 -- not required


    union all

    /* Get required solicit codes */
    select 
      [ID],
      [ID] as [SOLICITCODEID],
      cast(1 as bit) as [LOCKED],
      [SOLICITCODE].[DESCRIPTION] as [SOLICITCODENAME]
    from dbo.[SOLICITCODE]
    where [EXCLUSIONCODE] = 2 -- required

    and [ACTIVE] = 1

  )