USP_SIMPLEDATALIST_SOLICITCODEWITHSITE

List of solicit codes with site description.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@FEATUREID	uniqueidentifier	IN
@FEATURETYPE	tinyint	IN
@CONSENTCODE	int	IN
@CHANNELCODE	int	IN
@ALLSOLICITCODES	tinyint	IN

Definition

Copy



CREATE procedure dbo.USP_SIMPLEDATALIST_SOLICITCODEWITHSITE(
    @CURRENTAPPUSERID uniqueidentifier,
    @FEATUREID uniqueidentifier = null,
    @FEATURETYPE tinyint = null,
  @CONSENTCODE int = -1, --(-1 return all records, 0 will return standard code, 1 return consent solicit code)

  @CHANNELCODE int = -1,  --(-1 return all records, other will return consent solicit code whose SOLICITCODECHANNELCODE equal to it)

  @ALLSOLICITCODES tinyint = null --( null returns specific records based on CHANNELCODE, other will returns all solicit codes and consent solicit code whose SOLICITCODECHANNELCODE equal to 1 i.e. Email.)

)
as

    set nocount on;

    select 
        SOLICITCODE.[ID] as VALUE, 
        case when SOLICITCODE.SITEID is null then SOLICITCODE.[DESCRIPTION] else SOLICITCODE.[DESCRIPTION] + ' - ' + SITE.NAME end as LABEL 
    from dbo.SOLICITCODE
        left join dbo.SITE on SOLICITCODE.SITEID = SITE.ID
        left join UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, @FEATUREID, @FEATURETYPE) USERSITES on USERSITES.SITEID = SITE.ID
    where SOLICITCODE.ACTIVE = 1 
        and (SOLICITCODE.SITEID is null or USERSITES.SITEID is not null)
    and (@CONSENTCODE=-1 or SOLICITCODE.ConsentCode = @CONSENTCODE)
    and ((@ALLSOLICITCODES=1 and SOLICITCODE.SOLICITCODECHANNELCODE in (0,1)) or (@CHANNELCODE=-1 and @ALLSOLICITCODES is null) or SOLICITCODE.SOLICITCODECHANNELCODE = @CHANNELCODE)
     order by SOLICITCODE.[DESCRIPTION];