EA7_spBulkUpdate_BackOfficePeople_PostSBMerge

        CREATE PROCEDURE [dbo].[EA7_spBulkUpdate_BackOfficePeople_PostSBMerge]( @PayloadXml ntext )
        AS BEGIN
            SET NOCOUNT ON
            DECLARE @SQLError        integer
            DECLARE @recsUpdated    integer
            DECLARE @idoc            integer
            DECLARE @tbl_ids table(
                Old_Id    integer,    
                New_Id    integer                
            )
            DECLARE @tbl_problem_BORecords table(
                BOPeople_Id        integer,
                BOUser_Id        integer,
                ClientUser_Id    integer,
                New_Id            integer
            )

            EXEC sp_xml_preparedocument @idoc output, @PayloadXml

            INSERT INTO @tbl_ids(Old_Id, New_Id) 
            SELECT OriginalEA7RecordsID, EA7RecordsID
            FROM OPENXML(@idoc, 'PostSBMerge_Payload/recordids/id', 1)    
            WITH (OriginalEA7RecordsID integer, EA7RecordsID integer)    

            EXEC sp_xml_removedocument @idoc

            -- In EE when two records merge, one is called Primary and the only Duplicate.

            -- Primary stays, duplicate gets deleted from the EE DATABASE


            -- In BBNC we first gather the duplicates and their relationships to three tables:

            INSERT INTO @tbl_problem_BORecords(BOPeople_Id, BOUser_Id, ClientUser_Id, New_Id)
            SELECT a.ID, c.ID, d.ID, b.New_Id 
            FROM dbo.BackOfficeSystemPeople a 
            INNER JOIN @tbl_ids b ON a.BackofficeRecordID = b.Old_Id
            INNER JOIN dbo.BackOfficeSystemUsers c ON a.ID = c.BackOfficePeopleID 
            INNER JOIN dbo.ClientUsers d ON c.ClientUsersID = d.ID

            BEGIN TRAN T1

            -- In BackOfficeSystemPeople table, replace the BackofficeRecordID of those "Duplicates" with the EA7RecordsID of the "Primarys"

            UPDATE dbo.BackOfficeSystemPeople SET BackofficeRecordID = b.New_Id 
            FROM dbo.BackOfficeSystemPeople a 
            INNER JOIN @tbl_problem_BORecords b ON a.ID = b.BOPeople_Id

            -- In BackOfficeSystemUsers table, set the Current field to 0 for those duplicates.

            UPDATE dbo.BackOfficeSystemUsers SET [current] = 0  
            FROM dbo.BackOfficeSystemUsers a 
            INNER JOIN @tbl_problem_BORecords b ON a.ID = b.BOUser_Id

            -- In ClientUsers table, set the Deleted flag to 1 for these duplicates

            UPDATE dbo.ClientUsers SET Deleted = 1 
            FROM dbo.ClientUsers a 
            INNER JOIN @tbl_problem_BORecords b ON a.ID = b.ClientUser_Id

            IF @@error <> 0 
                BEGIN
                    ROLLBACK TRAN T1
                    RETURN
                END
            COMMIT TRAN T1    

            -- stats

            SELECT count(*) as 'NumSynched' FROM @tbl_problem_BORecords
        END