V_QUERY_THIRDPARTYAUTH_APPLICATIONUSERS

Fields

Field	Field Type	Null
ID	uniqueidentifier
ISSYSTEM	bit
DISPLAYNAME	nvarchar(255)
USERNAME	nvarchar(255)
EMAILADDRESS	nvarchar(100)
NUMROLES	int	yes
CONSTITUENTNAME	nvarchar(154)
STATUS	nvarchar(17)
ISSYSADMIN	bit
INACTIVE	int
CONSTITUENTID	uniqueidentifier	yes
INVITATIONSTATUSCODE	tinyint
ATLEASTONEROLEASSIGNED	int
SITENAME	nvarchar(250)	yes
CURRENCYSETNAME	nvarchar(100)	yes
ISBBIDUSER	int
ISPROXYUSER	bit
PROXYUSEROWNER	nvarchar(255)
HASRUNASRIGHTS	bit

Definition

Copy

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



      select
          APPUSER.ID,
          APPUSER.ISSYSTEM,
          isnull(APPUSER.DISPLAYNAME, '') as DISPLAYNAME,
          case when ISPROXYUSER = 1 then isnull(APPUSER.CUSTOM_AUTHENTICATION_USERID, '') 
                else isnull(APPUSER.USERNAME, '') end as USERNAME,
          isnull(APPUSER.EMAILADDRESS, '') as EMAILADDRESS,
          (
              select 
                  COUNT(*)
              from dbo.SYSTEMROLEAPPUSER 
              where SYSTEMROLEAPPUSER.APPUSERID = APPUSER.ID
          ) AS NUMROLES,
          isnull(CONSTITUENTNAME.NAME, '') as CONSTITUENTNAME,    
          case
              when APPUSER.ISPROXYUSER = 1 then
                case when isnull(APPUSERTHIRDPARTYAUTH.INACTIVE, 0) = 0 and APPUSER.ISACTIVE = 1 then 'Active'
                else 'Inactive' end
          else 
                case when isnull(APPUSERTHIRDPARTYAUTH.INACTIVE, 0) = 0 and APPUSER.ISACTIVE = 1 then isnull(APPUSERTHIRDPARTYAUTH.INVITATIONSTATUS, 'Not yet invited')
                else 'Inactive' end
          end as STATUS,
          APPUSER.ISSYSADMIN,
          case
              when APPUSERTHIRDPARTYAUTH.ID is not null then isnull(APPUSERTHIRDPARTYAUTH.INACTIVE, 0)
              when APPUSER.ISACTIVE = 0 then 1
              else 0
          end as INACTIVE,
          APPUSER.CONSTITUENTID,
          isnull(APPUSERTHIRDPARTYAUTH.INVITATIONSTATUSCODE, 0) as INVITATIONSTATUSCODE,
          case
              when exists(select 1 from dbo.SYSTEMROLEAPPUSER where APPUSERID = APPUSER.ID) then 1
              else 0
          end as ATLEASTONEROLEASSIGNED,
          (
              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,
        case when ((APPUSERCLAIMS.APPUSERID is not null or APPUSER.USERSID = SUSER_SID('NT AUTHORITY\ANONYMOUS LOGON')) and isnull(APPUSER.ISPROXYUSER, 0) = 0) then 1 else 0 end as ISBBIDUSER,
          isnull(APPUSER.ISPROXYUSER,0) as ISPROXYUSER,
        isnull(PROXYOWNERAPPUSER.DISPLAYNAME, '') as PROXYUSEROWNER,
                isnull(APPUSER.HASRUNASRIGHTS,0) as HASRUNASRIGHTS
      from dbo.APPUSER
      left outer join dbo.APPUSERTHIRDPARTYAUTH
          on APPUSER.ID = APPUSERTHIRDPARTYAUTH.ID
      left outer join dbo.APPUSERCLAIMS on APPUSER.ID = APPUSERCLAIMS.APPUSERID
      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(APPUSER.CONSTITUENTID) CONSTITUENTNAME
      left join (select ID, DISPLAYNAME from dbo.APPUSER) PROXYOWNERAPPUSER on APPUSER.PROXYOWNERID = PROXYOWNERAPPUSER.ID;