spResolveBulkAddUpdateBackOfficeUsersConflicts

            CREATE     procedure [dbo].[spResolveBulkAddUpdateBackOfficeUsersConflicts](
                @ClientsId                integer,
                @BackOfficeSystemId        integer,
                @BackOfficeSystemIdRE    integer,
                @PayloadXml                ntext
            )
            as
            begin
                set nocount on

                declare @SQLError         integer
                declare @recsAdded         integer
                declare    @recsUpdated     integer
                declare @recsErr        integer
                declare @MAX_PWD_LEN    integer

                set @MAX_PWD_LEN = 40

                if not exists(select ID from dbo.BackOfficeSystems where ID = @BackOfficeSystemId)
                begin
                raiserror ('Invalid BackOffice System Id',16,1)
                return
                end

                declare @idoc int
                declare @users table(
                    TranId                integer,
                    NewUserName            nvarchar(40) null,
                    NewPassword            nvarchar(100) null,
                    LinkAnyway            bit not null default 0,
                    Quarantine            bit not null default 0,
                    ClientUsersId        integer null,
                    InsertBOSRec        bit not null default 1, -- if true we need to insert a record into BackOfficeSystemPeople table
                    ErrMsg                nvarchar(510) null,
                    Added                bit not null default 0,
                    InsertBOSUserRec        bit not null default 1, -- if true we need to insert a record into BackOfficeSystemUsers TABLE
                    BackofficeId        integer        -- gotten from BackOfficeUserImportErrRecords            
                )

                exec sp_xml_preparedocument @idoc output, @PayloadXml

                insert into @users(TranId, NewUserName, NewPassword, LinkAnyway, Quarantine)
                select Id, UserName, Password, Link, Quarantine
                from OPENXML(@idoc, 'ResolutionPayload/users/u', 1) 
                with (Id integer, UserName nvarchar(40), Password nvarchar(40), Link bit, Quarantine bit)

                exec sp_xml_removedocument @idoc

                update @users set NewUserName = null where Len(NewUsername) < 1
                update @users set NewPassword = null where Len(NewPassword) < 1

                -- un/quarantine records
                update BackOfficeUserImportErrRecords set Quarantine = a.Quarantine
                from @users a
                inner join dbo.BackOfficeUserImportErrRecords b on b.Id = a.TranId
                where b.BackOfficeSystemsId = @BackOfficeSystemId

                -- If a new password was not specified, use the one from previous run
                update @users set NewPassword = b.Password
                from @users a
                inner join dbo.BackOfficeUserImportErrRecords b on b.Id = a.TranId
                where a.NewPassword is null

                -- determine if UserName is taken for records we are trying to insert
                update @users set ErrMsg = 'User ID already exists', ClientUsersId = b.ID
                from @users a
                inner join dbo.ClientUsers b on b.UserName = a.NewUserName
                where a.Quarantine = 0 and a.NewUserName is not null

                -- determine of the password length is <= 20
                update @users set ErrMsg = 'Password cannot be more than ' + cast(@MAX_PWD_LEN as char(2)) + ' characters'
                where LEN(NewPassword) > @MAX_PWD_LEN     

                begin tran T1

                -- get clienusersid for records to be linked
                update @users set ClientUsersId = b.ClientUsersId, BackOfficeId = b.BackOfficeId 
                from @users a 
                inner join dbo.BackOfficeUserImportErrRecords b on b.Id = a.TranId
                where a.LinkAnyway = 1

                set @SQLError = @@error

                if @SQLError = 0
                begin
                    -- insert users who were given new usernames
                    insert into ClientUsers(UserName, Password, Active, LastName, FirstName, EMail, InternalUser, ClientsID, 
                    IsSupervisor, IsProvisional)
                    select a.NewUserName, a.NewPassword, 1, b.LastName, b.FirstName, b.EMail, 0, @ClientsId, 
                    0, 0
                    from @users a 
                    inner join dbo.BackOfficeUserImportErrRecords b on b.Id = a.TranId
                    where a.ErrMsg is null
                    and a.NewUserName is not null 
                    and a.Quarantine = 0 
                    and a.LinkAnyway = 0

                    set @SQLError = @@error

                    if @SQLError = 0
                    begin
                        update @users 
                        set ClientUsersId = a.ID,
                        added = 1, 
                        backofficeid = c.BackOfficeId 
                        from dbo.ClientUsers a 
                        inner join @users b on a.UserName = b.NewUserName
                        inner join dbo.BackOfficeUserImportErrRecords c on c.id = b.TranId
                        where a.Password = b.NewPassword
                        and a.FirstName = c.FirstName
                        and a.LastName = c.LastName
                        and a.ClientsID = @ClientsId
                        and b.ErrMsg is null
                        and b.NewUserName is not null
                        and b.Quarantine = 0
                        and b.LinkAnyway = 0



                        update @users set InsertBOSRec = 0
                        from @users a
                        inner join dbo.BackOfficeUserImportErrRecords c on c.Id =  a.TranId
                        inner join dbo.BackOfficeSystemPeople b on b.BackOfficeRecordId = c.BackOfficeId
                        where b.BackOfficeSystemID = @BackOfficeSystemId



                        -- For @BackOfficeSystemId
                        insert into BackOfficeSystemPeople(BackOfficeSystemID, BackOfficeRecordID)
                        select @BackOfficeSystemId, b.BackOfficeId
                        from @Users a
                        inner join dbo.BackOfficeUserImportErrRecords b on b.Id = a.TranId
                        where a.Quarantine = 0 and a.ErrMsg is null and a.InsertBOSRec = 1
                        and not exists(    
                            select ID from dbo.BackOfficeSystemPeople c
                            where c.BackOfficeSystemId = @BackOfficeSystemId
                            and c.BackOfficeRecordId = b.BackOfficeId
                        )

                        -- For Raiser's Edge
                        insert into BackOfficeSystemPeople(BackOfficeSystemID, BackOfficeRecordID)
                        select @BackOfficeSystemIdRE, b.RE7RecordsId
                        from @Users a
                        inner join dbo.BackOfficeUserImportErrRecords b on b.Id = a.TranId
                        where a.Quarantine = 0 and a.ErrMsg is null and a.InsertBOSRec = 1
                        and b.RE7RecordsId is not null
                        and not exists(    
                            select ID from dbo.BackOfficeSystemPeople c
                            where c.BackOfficeSystemId = @BackOfficeSystemIdRE
                            and c.BackOfficeRecordId = b.RE7RecordsId
                        )


                    -- Need to add link record?
                        update @users set InsertBOSUserRec = 0
                        from @users a
                        inner join dbo.BackOfficeUserImportErrRecords c on c.Id =  a.TranId
                        inner join dbo.vwBackOfficeLinkedUsers b on b.BackOfficeRecordId = c.BackOfficeId
                        where b.BackOfficeSystemID = @BackOfficeSystemId


                        -- Map ClientUsers to @BackOfficeSystemId 
                        -- ChY CR251431-080906: updated [current] field to 1
                        insert into BackOfficeSystemUsers(BackOfficePeopleID, ClientUsersID, [current])
                        select a.ID, c.ClientUsersId, 1
                        from dbo.BackOfficeSystemPeople a
                        inner join dbo.BackOfficeUserImportErrRecords b on b.BackOfficeId = a.BackOfficeRecordID
                        inner join @Users c on c.TranId = b.Id
                        where c.Quarantine = 0 and c.ErrMsg is null
                        and c.InsertBOSUserRec = 1
                        and a.BackOfficeSystemID = b.BackOfficeSystemsID

                        -- Map ClientUsers to @BackOfficeSystemIdRE 
                        insert into BackOfficeSystemUsers(BackOfficePeopleID, ClientUsersID, [current])
                        select a.ID, c.ClientUsersId, 1
                        from dbo.BackOfficeSystemPeople a
                        inner join dbo.BackOfficeUserImportErrRecords b on b.RE7RecordsId = a.BackOfficeRecordID
                        inner join @Users c on c.TranId = b.Id
                        where c.Quarantine = 0 and c.ErrMsg is null
                        and c.InsertBOSUserRec = 1
                        and a.BackOfficeSystemId = @BackOfficeSystemIdRE


                        set @SQLError = @@error

                        if @SQLError = 0
                        begin
                            -- Save the Error records from this run to BackOfficeUserImportErrRecords
                            update BackOfficeUserImportErrRecords
                            set UserName = a.NewUserName, ClientUsersId = a.ClientUsersId, ErrMsg = a.ErrMsg
                            from @users a
                            inner join dbo.BackOfficeUserImportErrRecords b on b.Id = a.TranId
                            where a.ErrMsg is not null and b.BackOfficeSystemsId = @BackOfficeSystemId

                            set @SQLError = @@error
                            if @SQLError = 0
                            begin
                                delete BackOfficeUserImportErrRecords
                                from dbo.BackOfficeUserImportErrRecords a
                                inner join @users b on b.TranId = a.Id
                                where b.ErrMsg is null
                                and a.Quarantine = 0

                                set @SQLError = @@error

                                if @SQLError = 0
                                    commit tran T1
                                else
                                    rollback tran T1
                            end
                            else
                                rollback tran T1
                        end
                        else
                            rollback tran T1
                    end
                    else
                        rollback tran T1
                    end

                else
                    rollback tran T1


                -- resultset for sending email
                select * from @Users where added = 1

                -- resultset1 for synching logins with EE
                SELECT a.backofficeid, a.clientusersid, b.username, b.password
                FROM @Users a
                INNER JOIN dbo.clientusers b ON a.clientusersid = b.id
                WHERE  a.clientusersid IS NOT NULL AND a.errmsg IS NULL

            end