USP_SIMPLEDATALIST_CONSTITUENTSOLICITCODEWITHCONSENT

CREATE procedure dbo.USP_SIMPLEDATALIST_CONSTITUENTSOLICITCODEWITHCONSENT
(
  @CONSTITUENTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @FEATUREID uniqueidentifier = null,
  @FEATURETYPE tinyint = null
)
as
  declare @CURRENTDATE date = getdate();

  select
    CONSTITUENTSOLICITCODE.ID as VALUE,
    SOLICITCODE.DESCRIPTION as LABEL
  from dbo.CONSTITUENTSOLICITCODE
    inner join dbo.SOLICITCODE on CONSTITUENTSOLICITCODE.SOLICITCODEID = SOLICITCODE.ID
    left join dbo.SITE on SOLICITCODE.SITEID = SITE.ID
    left join UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, @FEATUREID, @FEATURETYPE) USERSITES on USERSITES.SITEID = SITE.ID
  where
    SOLICITCODE.CONSENTCODE > 0
    and (SOLICITCODE.SITEID is null or USERSITES.SITEID is not null)
    and CONSTITUENTSOLICITCODE.CONSTITUENTID = @CONSTITUENTID
    and CONSTITUENTSOLICITCODE.CONSENTPREFERENCECODE = 2 -- Opt-in

    and @CURRENTDATE between CONSTITUENTSOLICITCODE.STARTDATE and isnull(CONSTITUENTSOLICITCODE.ENDDATE, @CURRENTDATE)
  order by SOLICITCODE.DESCRIPTION asc