USP_CONSENT_SOLICITCODES_DATALIST

Parameters

Parameter	Parameter Type	Mode
@CURRENTAPPUSERID	uniqueidentifier	IN
@SITESSELECTED	xml	IN
@SECURITYFEATUREID	uniqueidentifier	IN
@SECURITYFEATURETYPE	tinyint	IN
@CONSENTNAME	nvarchar(100)	IN
@CONSENTCODE	tinyint	IN
@SITEID	uniqueidentifier	IN
Definition

Copy


                    CREATE procedure dbo.USP_CONSENT_SOLICITCODES_DATALIST
                    (
                        @CURRENTAPPUSERID uniqueidentifier,
                        @SITESSELECTED xml = null,
                        @SECURITYFEATUREID uniqueidentifier = null,
                        @SECURITYFEATURETYPE tinyint = null,
            @CONSENTNAME nvarchar(100) = '',
            @CONSENTCODE tinyint = null,
            @SITEID uniqueidentifier = null
                    )
                    as
                    set nocount on;
                    declare @SITEXML as xml
       set @SITEXML = (    select  @SITEID as SITEID
          for xml raw('ITEM'),type,elements,root('SITESSELECTED'),BINARY BASE64)

          select 
                        SOLICITCODE.ID,
                        SOLICITCODE.DESCRIPTION,
                        coalesce(SITE.NAME, 'All sites'),
                        '',
                        SOLICITCODE.ACTIVE,
                        case
              when SOLICITCODE.EXCLUSIONCODE = 2 then 1
              else 0 
            end as ISREQUIRED,
                        case 
                            when SOLICITCODE.SITEID is null 
                                then dbo.UFN_SITEALLOWEDFORUSERONFEATURE(@CURRENTAPPUSERID,null,@SECURITYFEATUREID,@SECURITYFEATURETYPE)
                            else 1
                        end ENABLEEDIT,
    SOLICITCODE.EXCLUSIONCODE,
    SOLICITCODE.CONSENTCODE,
    SOLICITCODE.SOLICITCODECHANNELCODE,
    SOLICITCODE.SITEID as SITEID
                    from 
                        dbo.SOLICITCODE
                        left join dbo.SITE on SOLICITCODE.SITEID=SITE.ID
                    where

                        (-- Special case.  Only copy this behavior if no site assignment means all sites.

                            @SITEID is null 
              or
                            SOLICITCODE.SITEID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, 3, @SITEXML))
                        )
                        and 
                        (
                            SOLICITCODE.ACTIVE = 1 
                        )
            and
            (
              @CONSENTCODE is null or (SOLICITCODE.CONSENTCODE = @CONSENTCODE)
            )
            and
            (
              @CONSENTNAME = '' or (SOLICITCODE.DESCRIPTION like '%'+ @CONSENTNAME + '%')
            )
            order by SEQUENCE