USP_GLOBALCHANGE_MANAGEPERSONALACCESSTOKENS

            create procedure dbo.USP_GLOBALCHANGE_MANAGEPERSONALACCESSTOKENS
            (
                @CHANGEAGENTID uniqueidentifier = null,
                @ASOF as datetime = null,
                @NUMBERADDED int = 0 output,
                @NUMBEREDITED int = 0 output,
                @NUMBERDELETED int = 0 output
            )
            as
                set nocount off;

                declare @CURRENTDATE datetime

                set @CURRENTDATE = getdate();

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

                --insert/update PAT revoked reason

                --Reserve ID for default reason PAT is expired.

                --Default reason will inserted via revision file.

                --Default reason will be linked with mapping table so that user cannot delete default reason from code table

                --Admin can change the text of default reason from Admin/code tables to manage the localization.


                begin try
                    declare @TEMPUPDATEDPATTABLE table ( ID uniqueidentifier );
                    declare @DEFAULTREVOKECODEID uniqueidentifier = 'EC587657-562A-4ED7-9FBE-31FAB4D97CF1';
                    declare @PERSONALACCESSTOKENREVOKECODEDESCRIPTION nvarchar(100);

                    select @PERSONALACCESSTOKENREVOKECODEDESCRIPTION = DESCRIPTION from dbo.PERSONALACCESSTOKENREVOKECODE where ID = @DEFAULTREVOKECODEID;

                    --inserting ID of updated token into temp table

                    --we are updating only expired tokens that are enabled


                    update dbo.PERSONALACCESSTOKEN set
                        ENABLED = 0,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                        OUTPUT INSERTED.ID
                        INTO @TEMPUPDATEDPATTABLE
                    where 
                        EXPIRATIONDATE < @CURRENTDATE and ENABLED <> 0;

                    if exists (select 1 from @TEMPUPDATEDPATTABLE)
                        begin
                            --insert new entry in the detail table 


                            insert into dbo.PERSONALACCESSTOKENREVOKEDETAIL (ID, PERSONALACCESSTOKENREVOKECODEID, DETAILS, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                            select TEMPUPDATEDPATTABLE.ID, @DEFAULTREVOKECODEID, @PERSONALACCESSTOKENREVOKECODEDESCRIPTION, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                            from @TEMPUPDATEDPATTABLE TEMPUPDATEDPATTABLE
                            left join PERSONALACCESSTOKENREVOKEDETAIL on TEMPUPDATEDPATTABLE.ID = PERSONALACCESSTOKENREVOKEDETAIL.ID
                            where PERSONALACCESSTOKENREVOKEDETAIL.ID is null;
                        end

                    set @NUMBEREDITED = @@ROWCOUNT;
                    set @NUMBERADDED = 0;
                    set @NUMBERDELETED = 0;

                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch