USP_DATALIST_PARTICIPANTACCESSROLE

    CREATE procedure dbo.USP_DATALIST_PARTICIPANTACCESSROLE
    (
            @CLIENTUSERSID int,
          @EVENTID uniqueidentifier
    )with execute as caller
    as
        set nocount on;

      DECLARE @CONSTITUENTID uniqueidentifier
        SET @CONSTITUENTID = dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERSID)

      DECLARE @PREVIOUSEVENTID uniqueidentifier

      SELECT @PREVIOUSEVENTID = PRIORYEAREVENTID
          FROM dbo.EVENTEXTENSION WITH (NOLOCK) WHERE EVENTID = @EVENTID

        DECLARE 
            @IsIndependentHouseholdMember bit 
            ,@IsIndependentHouseholdLeader bit  
            ,@IsIndependentTeamMember bit  
            ,@IsIndependentTeamLeader bit   
            ,@IsCompanyHouseholdMember bit   
            ,@IsCompanyHouseholdLeader bit   
            ,@IsCompanyTeamHouseholdLeader bit   
            ,@IsCompanyTeamMember bit   
            ,@IsCompanyTeamLeader bit   
            ,@IsCompanyLeader bit   
        ,@IsCompanyTeamHouseholdMember bit
        ,@IsTeamHouseholdMember bit
        ,@IsTeamHouseholdLeader bit
        ,@IsCompanyIndividual bit
        ,@IsIndependentIndividual bit

      DECLARE @RoleList varchar(8000)

      EXEC dbo.USP_FAF_REGISTRANT_ROLE
            @CONSTITUENTID = @CONSTITUENTID
            ,@EVENTID =@EVENTID
            ,@IsIndependentHouseholdMember  = @IsIndependentHouseholdMember OUTPUT
            ,@IsIndependentHouseholdLeader  = @IsIndependentHouseholdLeader OUTPUT
            ,@IsIndependentTeamMember  = @IsIndependentTeamMember OUTPUT
            ,@IsIndependentTeamLeader   = @IsIndependentTeamLeader OUTPUT
            ,@IsCompanyHouseholdMember   = @IsCompanyHouseholdMember OUTPUT
            ,@IsCompanyHouseholdLeader   = @IsCompanyHouseholdLeader OUTPUT
            ,@IsCompanyTeamHouseholdLeader   = @IsCompanyTeamHouseholdLeader OUTPUT
            ,@IsCompanyTeamMember   = @IsCompanyTeamMember OUTPUT
            ,@IsCompanyTeamLeader   = @IsCompanyTeamLeader OUTPUT
            ,@IsCompanyLeader   = @IsCompanyLeader OUTPUT
        ,@IsCompanyTeamHouseholdMember = @IsCompanyTeamHouseholdMember OUTPUT
        ,@IsTeamHouseholdMember = @IsTeamHouseholdMember OUTPUT
        ,@IsTeamHouseholdLeader = @IsTeamHouseholdLeader OUTPUT
        ,@IsCompanyIndividual = @IsCompanyIndividual OUTPUT
        ,@IsIndependentIndividual = @IsIndependentIndividual OUTPUT

         IF (@IsCompanyLeader = 1)
            IF(@PREVIOUSEVENTID is null)
                SET @RoleList = 'Team leaders - current, Household leaders - current, Household members - current, Individuals - current, Donors - current, Contacts, Groups'
            ELSE
                SET @RoleList = 'Team leaders - current, Team leaders - previous, Household leaders - current, Household leaders - previous, Household members - current, Household members - previous, Individuals - current, Individuals - previous, Donors - current, Donors - previous, Contacts, Groups'
         ELSE IF (@IsIndependentTeamLeader = 1 OR @IsCompanyTeamLeader = 1)
            IF(@PREVIOUSEVENTID is null)
                SET @RoleList = 'Team members - current, Household leaders - current, Household members - current, Donors - current, Contacts, Groups'
            ELSE
                SET @RoleList = 'Team members - current, Team members - previous, Household leaders - current, Household leaders - previous, Household members - current, Household members - previous, Donors - current, Donors - previous, Contacts, Groups'
         ELSE IF (@IsIndependentTeamMember = 1 OR @IsCompanyTeamMember = 1)
            IF(@PREVIOUSEVENTID is null)
                SET @RoleList = 'Team members - current,Donors - current, Contacts, Groups'
            ELSE
                SET @RoleList = 'Team members - current,Team members - previous, Donors - current, Donors - previous, Contacts, Groups'
         ELSE IF (@IsIndependentHouseholdMember = 1 OR @IsIndependentHouseholdLeader = 1 OR @IsCompanyHouseholdMember = 1 OR @IsCompanyHouseholdLeader = 1
            OR @IsCompanyTeamHouseholdLeader = 1 OR @IsCompanyTeamHouseholdMember = 1 OR @IsTeamHouseholdMember = 1 OR @IsTeamHouseholdLeader = 1)
            IF(@PREVIOUSEVENTID is null)
                SET @RoleList = 'Household members - current,Donors - current, Contacts, Groups'
            ELSE
                SET @RoleList = 'Household members - current,Household members - previous, Donors - current, Donors - previous, Contacts, Groups'
         ELSE IF (@IsCompanyIndividual = 1 OR @IsIndependentIndividual = 1) 
            IF(@PREVIOUSEVENTID is null)
                SET @RoleList = 'Donors - current, Contacts'
            ELSE
                SET @RoleList = 'Donors - current, Donors - previous, Contacts'
         ELSE SET @RoleList = ''

        select ID,Item as CONTACTTYPE 
        from dbo.UFN_STRING_PARSER (',',@RoleList)