USP_VALIDATEPROXYUSER

                CREATE procedure dbo.USP_VALIDATEPROXYUSER
                (
                   @CUSTOMAUTHENTICATIONUSERID nvarchar(255) = null,
                   @PERSONALACCESSTOKEN nvarchar(4000) = null,
                   @CHANGEAGENTID uniqueidentifier = null
                )
                as
                begin

                    set nocount on;

                    if @CHANGEAGENTID is null  
                       exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                    declare @CURRENTDATE datetime;
                    set @CURRENTDATE = getdate();

                    declare @ID uniqueidentifier = null;
                    declare @INVALIDLOGINATTEMPTS int = 0;
                    declare @USERNAME nvarchar(128) = null;
                    declare @PERSONALACCESSTOKENIDS table (PERSONALACCESSTOKENID uniqueidentifier);
                    declare @TOOMANYINVALIDATTEMPTREVOKECODEID uniqueidentifier = 'B24DC45C-18BC-4721-A60E-D8FD4D7F0C0E';
                    declare @DETAILS nvarchar(250);

                     /*
                     Condition to validate proxy user creds  

                   --CUSTOM_AUTHENTICATION_USERID and PAT should match  
                   --App user should be active  
                   --PAT should be enabled  
                   --Expiration date should be greater than equal to current date  
                   --Invalid login attempts should be less than 5  
                   --App user should be a proxy user
                     */

                      select
                          @ID = APPUSER.ID,
                          @USERNAME = USERNAME
                      from
                          dbo.APPUSER
                          inner join dbo.PERSONALACCESSTOKEN on APPUSER.ID = PERSONALACCESSTOKEN.PROXYUSERID
                      where
                          APPUSER.[CUSTOM_AUTHENTICATION_USERID] = @CUSTOMAUTHENTICATIONUSERID and
                          PERSONALACCESSTOKEN.[TOKEN] = @PERSONALACCESSTOKEN and 
                          PERSONALACCESSTOKEN.[ENABLED] = 1 and
                          APPUSER.[ISACTIVE] = 1 and
                          APPUSER.[INVALIDLOGINATTEMPTS] < 5 and
                          PERSONALACCESSTOKEN.[EXPIRATIONDATE] >= @CURRENTDATE  and
                          APPUSER.[ISPROXYUSER] = 1;

                      if @ID is not null
                        begin
                        --reset the counter for invalid attempts to zero once validated.

                        update dbo.APPUSER set
                            [INVALIDLOGINATTEMPTS] = 0,
                            [CHANGEDBYID] = @CHANGEAGENTID,
                            [DATECHANGED] = @CURRENTDATE
                            where [ID] = @ID and
                            [INVALIDLOGINATTEMPTS] > 0;
                        end
                        else
                        begin
                         --Set invalid login counter and and isactive as per invalidloginattempts value

                          update dbo.APPUSER set
                              INVALIDLOGINATTEMPTS = case when INVALIDLOGINATTEMPTS < 5 then INVALIDLOGINATTEMPTS + 1 else 5 end,
                              ISACTIVE = case when INVALIDLOGINATTEMPTS >= 4 then 0 else ISACTIVE end,
                              CHANGEDBYID = @CHANGEAGENTID,
                              DATECHANGED = @CURRENTDATE
                          where CUSTOM_AUTHENTICATION_USERID = @CUSTOMAUTHENTICATIONUSERID;

                        set @DETAILS = 'Invalid credentials for proxy user-'+ @CUSTOMAUTHENTICATIONUSERID +' caused token deactivation.';  

                        -- Insert all enabled personal token ids into a table variable

                        insert into @PERSONALACCESSTOKENIDS (PERSONALACCESSTOKENID)
                        select   
                          P.ID
                          from dbo.PERSONALACCESSTOKEN P
                          inner join dbo.APPUSER on APPUSER.ID = P.PROXYUSERID
                          where APPUSER.INVALIDLOGINATTEMPTS >= 5
                          and APPUSER.CUSTOM_AUTHENTICATION_USERID = @CUSTOMAUTHENTICATIONUSERID
                        and P.[ENABLED] = 1;

                        if exists(select 1 from @PERSONALACCESSTOKENIDS)
                        begin
                            update P set
                              P.[ENABLED] = 0,
                              P.CHANGEDBYID = @CHANGEAGENTID,
                              P.DATECHANGED = @CURRENTDATE
                            from dbo.PERSONALACCESSTOKEN P
                            inner join @PERSONALACCESSTOKENIDS T on T.PERSONALACCESSTOKENID = P.ID
                            where P.[ENABLED] <> 0;


                            if @@ROWCOUNT > 0 
                            begin 
                               insert into dbo.PERSONALACCESSTOKENREVOKEDETAIL (ID, PERSONALACCESSTOKENREVOKECODEID, DETAILS, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)  
                               Select T.PERSONALACCESSTOKENID, @TOOMANYINVALIDATTEMPTREVOKECODEID, @DETAILS, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE  
                               from @PERSONALACCESSTOKENIDS T;
                            end
                          end
                        end
                        select @USERNAME;
                end