V_QUERY_APPLICATIONUSERS

Fields

Field	Field Type	Null
ID	uniqueidentifier
USERNAME	nvarchar(255)
DISPLAYNAME	nvarchar(255)
ISSYSADMIN	bit
NUMROLES	int	yes
CONSTITUENTNAME	nvarchar(154)
SITENAME	nvarchar(250)	yes
CURRENCYSETNAME	nvarchar(100)	yes
CUSTOM_AUTHENTICATION_USERID	nvarchar(255)
ISSYSTEM	bit
EMAILADDRESS	nvarchar(100)	yes
ISACTIVE	bit
ISPROXYUSER	bit
PROXYUSEROWNER	nvarchar(255)
HASRUNASRIGHTS	bit
LINKEDTOBBID	int
Definition

Copy
/*
Generated by Blackbaud AppFx Platform
Date:  5/3/2024 2:17:12 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.3700.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_APPLICATIONUSERS AS



                    select
                        APPUSER.ID,
                        case when ISPROXYUSER = 1 then isnull(APPUSER.CUSTOM_AUTHENTICATION_USERID, '') 
                             else isnull(APPUSER.USERNAME, '') end as USERNAME,
                        isnull(APPUSER.DISPLAYNAME, '') as DISPLAYNAME,
                        APPUSER.ISSYSADMIN,
                        (
                            select 
                                COUNT(*)
                            from 
                                dbo.SYSTEMROLEAPPUSER 
                            where 
                                SYSTEMROLEAPPUSER.APPUSERID = APPUSER.ID
                        ) AS 'NUMROLES',
                        isnull(NF.NAME, '') as CONSTITUENTNAME,
                        (
                            select 
                                SITE.NAME 
                            from 
                                dbo.SITE where SITE.ID = APPUSER.SITEID
                        ) as SITENAME,
                        (
                            select
                                CURRENCYSET.NAME
                            from
                                dbo.CURRENCYSET
                                inner join dbo.APPUSERCURRENCYSET on CURRENCYSET.ID = APPUSERCURRENCYSET.CURRENCYSETID
                            where
                                APPUSERCURRENCYSET.APPUSERID = APPUSER.ID
                        ) as CURRENCYSETNAME,
                        isnull(APPUSER.CUSTOM_AUTHENTICATION_USERID, '') as CUSTOM_AUTHENTICATION_USERID,
                        APPUSER.ISSYSTEM,
                        APPUSER.EMAILADDRESS as EMAILADDRESS,
                        isnull(APPUSER.ISACTIVE,1) as ISACTIVE,
                        isnull(APPUSER.ISPROXYUSER,0) as ISPROXYUSER,
                        isnull(PROXYOWNERAPPUSER.DISPLAYNAME, '') as PROXYUSEROWNER,
                        isnull(APPUSER.HASRUNASRIGHTS,0) as HASRUNASRIGHTS,
                        case when exists
                        (
                            select 1 
                            from 
                                dbo.APPUSERCLAIMSLINK 
                            where 
                                APPUSERCLAIMSLINK.APPUSERID = APPUSER.ID
                        ) then 1 else 0 end as LINKEDTOBBID
                    from 
                        dbo.APPUSER with (nolock)
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(APPUSER.CONSTITUENTID) NF
                        left join (select ID, DISPLAYNAME from dbo.APPUSER) PROXYOWNERAPPUSER on APPUSER.PROXYOWNERID = PROXYOWNERAPPUSER.ID;