spTransactions_SetCustomTransactionBBNCUser

CREATE procedure [dbo].[spTransactions_SetCustomTransactionBBNCUser](
    @TransactionID            integer,
    @UserID                 integer,
    @NewUserID                integer,
    @EntryID                uniqueidentifier)
as
begin
    declare @NewUserName varchar(50);
    select @NewUserName = cu.UserName
    from dbo.ClientUsers cu
    where cu.ID = @NewUserID;

    declare @CustomFormEntryID int;
    declare @x xml;
    select @CustomFormEntryID = form.ID, 
        @x = form.EntryData
    from dbo.CustomFormEntry form
    where form.EntryID = @EntryID
        and form.ClientUserID = @UserID;

    --bug 184972 if there's no new user, there's no new information to set
    if (Len(IsNull(@NewUserName, '')) > 0)
    begin
        if (IsNull(@CustomFormEntryID, 0) > 0)
        begin
            set @x.modify('replace value of (/CustomFormEntry/LogInUserName/text())[1]
                            with sql:variable("@NewUserName")');
            set @x.modify('replace value of (/CustomFormEntry/LogInUserID/text())[1]
                            with sql:variable("@NewUserID")');
            set @x.modify('replace value of (/CustomFormEntry/ApplicantID/text())[1]
                            with sql:variable("@NewUserID")');

            update dbo.CustomFormEntry 
            set ClientUserID = @NewUserID,
                EntryData = @x
            where ID = @CustomFormEntryID;
        end

        if (@TransactionId > 0)
        begin
            set @x = null;

            select @x = ct.Data
            from dbo.CustomTransactions ct
            where ct.CustomTransactionID = @TransactionID
                --If it's a resubmit, this will be the administrator, so we'd never be able to update this record even though we have the unique TransID.
                --and AddedByUserID = @UserID;

            if (@x is not null)
            begin
                set @x.modify('replace value of (/OnlineApplicationTransaction/ApplicationData/Application/LogInUserName/text())[1]
                                with sql:variable("@NewUserName")');
                set @x.modify('replace value of (/OnlineApplicationTransaction/ApplicationData/Application/LogInUserID/text())[1]
                                with sql:variable("@NewUserID")');
                set @x.modify('replace value of (/OnlineApplicationTransaction/ApplicationData/Application/ApplicantID/text())[1]
                                with sql:variable("@NewUserID")');

                update dbo.CustomTransactions 
                set AddedByUserID = @NewUserID,
                    Data = cast(cast(@x as varchar(max)) as ntext)
                where CustomTransactionID = @TransactionID
                    --If it's a resubmit, this will be the administrator, so we'd never be able to update this record even though we have the unique TransID.
                    --and AddedByUserID = @UserID;
            end
        end
    end
end