USP_APPUSER_WITHPROXY_UPDATEPROXYINACTIVEREVOKEPAT

create procedure [dbo].[USP_APPUSER_WITHPROXY_UPDATEPROXYINACTIVEREVOKEPAT](
    @APPUSERID uniqueidentifier,
    @APPUSERINACTIVEREASONCODEID uniqueidentifier = null,
    @DETAILS varchar(300),
    @CHANGEAGENTID uniqueidentifier,
    @CURRENTDATE datetime)
as
set nocount on;

    declare @LISTOFPROXYUSERS table (PROXYCOUNT int identity(1,1), PROXYUSERID uniqueidentifier, PROXYUSERNAME varchar(255));
    insert into @LISTOFPROXYUSERS
    exec [dbo].[USP_DATALIST_APPUSERPROXYUSERNAME] @APPUSERID;

    declare @LISTCOUNT int = 1;
    declare @PROXYCOUNT int = (select count(*) from @LISTOFPROXYUSERS);
    declare @PROXYUSERID uniqueidentifier;

    if @APPUSERINACTIVEREASONCODEID is null
      set @APPUSERINACTIVEREASONCODEID = 'CBB6C64D-8EE1-4CE6-B9D1-1D5F98D9EB0C' --this is the default inactive code located in APPUSERINACTIVECODE table;


    begin try
        update [dbo].[APPUSER] set
          [ISACTIVE] = 0,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        from [dbo].[APPUSER]
        join @LISTOFPROXYUSERS PROXYLIST on [ID] = PROXYLIST.PROXYUSERID
        where [ISACTIVE] = 1;

        update [dbo].[APPUSERINACTIVEDETAIL] set
            [APPUSERINACTIVEREASONCODEID] = @APPUSERINACTIVEREASONCODEID,
            [DETAILS] = @DETAILS,
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE
        from [dbo].[APPUSERINACTIVEDETAIL]
        join @LISTOFPROXYUSERS PROXYLIST on [APPUSERINACTIVEDETAIL].[ID]= PROXYLIST.PROXYUSERID
        join [dbo].[APPUSER] on PROXYLIST.PROXYUSERID = [APPUSER].[ID]
        where [ISACTIVE] = 1;

        insert into [dbo].[APPUSERINACTIVEDETAIL] 
          ([ID], [APPUSERINACTIVEREASONCODEID], [DETAILS], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED])
          select PROXYLIST.PROXYUSERID, @APPUSERINACTIVEREASONCODEID, @DETAILS, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE 
          from @LISTOFPROXYUSERS PROXYLIST 
          where PROXYLIST.PROXYUSERID not in (select [ID] from [dbo].[APPUSERINACTIVEDETAIL]);

        while (@LISTCOUNT <= @PROXYCOUNT)
          begin
            select @PROXYUSERID = PROXYUSERID from @LISTOFPROXYUSERS where PROXYCOUNT = @LISTCOUNT;
            exec [dbo].[USP_PROXYUSER_MARKINACTIVE_REVOKEPERSONALACCESSTOKEN] @PROXYUSERID, @CHANGEAGENTID, @CURRENTDATE, @DETAILS;
            set @LISTCOUNT = @LISTCOUNT + 1
          end

        declare @CURRENTDATETIMEOFFSET datetimeoffset = [dbo].[UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET](getutcdate(), 1);

        update [dbo].[APPUSERTHIRDPARTYAUTH] set
          [INACTIVE] = 1,
          [INACTIVEDATE] = @CURRENTDATETIMEOFFSET,
          [INACTIVEDETAILS] = @DETAILS,
          [INVITATIONID] = null,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        from [dbo].[APPUSERTHIRDPARTYAUTH]
        join @LISTOFPROXYUSERS PROXYLIST on [ID] = PROXYLIST.PROXYUSERID
        where [INACTIVE] = 0;

        insert into [dbo].[APPUSERTHIRDPARTYAUTH]
          ([ID], [INACTIVE], [INACTIVEDATE], [INACTIVEDETAILS], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED])
          select PROXYUSERID, 1, @CURRENTDATETIMEOFFSET, @DETAILS, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE 
          from @LISTOFPROXYUSERS PROXYLIST
          where PROXYLIST.PROXYUSERID not in (select [ID] from [dbo].[APPUSERTHIRDPARTYAUTH]);
    end try
    begin catch
      exec [dbo].[USP_RAISE_ERROR]
      return 1;
    end catch
    return 0;