USP_DATALIST_APPLICATIONUSERS

      CREATE procedure dbo.USP_DATALIST_APPLICATIONUSERS
      as
      --Used by the "Application Users" datalist

      --Returns all application users ordered by name


      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,
        isnull(APPUSER.ISACTIVE, 1) as ISACTIVE,
        isnull(APPUSER.ISPROXYUSER, 0) as ISPROXYUSER,
        isnull(PROXYOWNERAPPUSER.DISPLAYNAME, '') as PROXYUSEROWNER
        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
          order by 
            CUSTOM_AUTHENTICATION_USERID ASC,
            USERNAME ASC --Not prefixing this with the table name allows SQL to use the USERNAME column from the SELECT list