USP_APPUSERCLAIMS_ADD

CREATE procedure dbo.USP_APPUSERCLAIMS_ADD
(
    @NAMEIDENTIFIER nvarchar(450),
    @APPUSERID uniqueidentifier,
    @EMAILADDRESS dbo.UDT_EMAILADDRESS = ''
)
as
begin
    set nocount on;

    declare @CURRENTDATE datetime = getdate();
    declare @CHANGEAGENTID uniqueidentifier;
    declare @OLDUSERNAME nvarchar(256);

    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    select @OLDUSERNAME = USERNAME from dbo.APPUSER where ID=@APPUSERID;

    begin try
        if exists(select 1 from dbo.APPUSERCLAIMS where APPUSERID = @APPUSERID)
            raiserror('BBERR_APPUSER_ALREADYLINKEDTOCLAIM', 13, 1);

        if exists(select 1 from dbo.APPUSERCLAIMS where NAMEIDENTIFIER = @NAMEIDENTIFIER)
            raiserror('BBERR_APPUSERCLAIM_ALREADYLINKEDTOAPPUSER', 13, 1);

        insert into dbo.APPUSERCLAIMS
            (NAMEIDENTIFIER, APPUSERID, EMAILADDRESS, ISREGISTERED)
        values
            (@NAMEIDENTIFIER, @APPUSERID, @EMAILADDRESS, 0);

        update dbo.APPUSER
        set APPUSER.USERSID = SUSER_SID('NT AUTHORITY\ANONYMOUS LOGON'),
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where APPUSER.ID = @APPUSERID
            and APPUSER.USERSID <> SUSER_SID('NT AUTHORITY\ANONYMOUS LOGON');

        update dbo.CHANGEAGENT
        set USERNAME = (select USERNAME from dbo.APPUSER where ID = @APPUSERID)
        where USERNAME =  @OLDUSERNAME;

        if exists(select 1 from dbo.APPUSERTHIRDPARTYAUTH where ID = @APPUSERID)
            update dbo.APPUSERTHIRDPARTYAUTH set
                INVITATIONSTATUSCODE = 3,            -- Active

                INVITATIONDATE = @CURRENTDATE,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where ID = @APPUSERID;

        else
            insert into dbo.APPUSERTHIRDPARTYAUTH
                (ID, INVITATIONSTATUSCODE, INVITATIONDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values
                (@APPUSERID, 3, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

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

    return 0;
end