USP_REVENUEBATCH_CONSTITUENT_UPDATEIDS

            CREATE procedure dbo.USP_REVENUEBATCH_CONSTITUENT_UPDATEIDS
            (
                @BATCHREVENUECONSTITUENTID uniqueidentifier,
                @CONSTITUENTID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @CURRENTRECOGNITIONS xml = null,
                @UPDATEDRECOGNITIONS xml = null output,
                @UPDATEDAPPLICATIONRECOGNITIONS xml = null output
            )
            as
                set nocount on

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

                declare @CURRENTDATE datetime
                set @CURRENTDATE = getdate()

                --update all existing batches to point to the newly created constituent instead of the imaginary revenue batch version

                update dbo.BATCHREVENUE 
                    set CONSTITUENTID = @CONSTITUENTID,
                        APPLYTOSHOWNFORCONSTITUENTID = BATCHREVENUE.CONSTITUENTID,
                        GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID = BATCHREVENUE.CONSTITUENTID,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                where CONSTITUENTID = @BATCHREVENUECONSTITUENTID

                update dbo.BATCHREVENUERECOGNITION
                    set CONSTITUENTID = @CONSTITUENTID,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                where CONSTITUENTID = @BATCHREVENUECONSTITUENTID

                update dbo.BATCHREVENUEAPPLICATIONPLEDGE
                    set CONSTITUENTID = @CONSTITUENTID,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                where CONSTITUENTID = @BATCHREVENUECONSTITUENTID

                -- This clause contains the same pattern that was fixed lower down for bug 489752.  We believe this code to be no longer in use though

                -- since memberships and their application have been removed from ERB, so we are leaving as is.

                update dbo.BATCHREVENUEAPPLICATIONMEMBERSHIP
                    set MEMBERS.modify('replace value of (/MEMBERS/ITEM/CONSTITUENTID/text())[1] with sql:variable("@CONSTITUENTID")'),
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                where MEMBERS.value('(/MEMBERS/ITEM/CONSTITUENTID)[1]','uniqueidentifier') = @BATCHREVENUECONSTITUENTID                                

                update 
                    dbo.BATCHREVENUEREGISTRANT
                set
                    CONSTITUENTID = @CONSTITUENTID,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where
                    CONSTITUENTID = @BATCHREVENUECONSTITUENTID;

                update
                    dbo.BATCHREVENUEREGISTRANTPACKAGE
                set
                    CONSTITUENTID = @CONSTITUENTID,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where
                    CONSTITUENTID = @BATCHREVENUECONSTITUENTID;

                update
                    dbo.BATCHREVENUEREGISTRANTPACKAGE
                set
                    GUESTOFCONSTITUENTID = @CONSTITUENTID,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where
                    GUESTOFCONSTITUENTID = @BATCHREVENUECONSTITUENTID;

                if @CURRENTRECOGNITIONS is not null
                begin                    
                    --Also need to update the @RECOGNITIONS variable (since it will not be reloaded from the BATCHREVENUERECOGNITION table for this commit and may reference the newly created constituent)

                    set @UPDATEDRECOGNITIONS = 
                    (
                        select 
                            ID,
                            case when CONSTITUENTID = @BATCHREVENUECONSTITUENTID then @CONSTITUENTID else CONSTITUENTID end as CONSTITUENTID,
                            AMOUNT,
                            EFFECTIVEDATE,
                            REVENUERECOGNITIONTYPECODEID 
                        from dbo.UFN_REVENUEBATCH_GETRECOGNITIONS_FROMITEMLISTXML(@CURRENTRECOGNITIONS)
                        for xml raw('ITEM'), type, elements, root('RECOGNITIONS'), binary base64
                    )
                end

                if @UPDATEDAPPLICATIONRECOGNITIONS is not null
                begin
                    declare @APPLICATIONRECOGNITIONSTABLE table
                    (
                        ADDITIONALAPPLICATIONTYPECODE tinyint,
                        ADDITIONALAPPLICATIONDESIGNATIONID uniqueidentifier,
                        APPLICATIONAMOUNT money,
                        APPLICATIONDESCRIPTION nvarchar(100),
                        RECOGNITIONS xml
                    )

                    insert into @APPLICATIONRECOGNITIONSTABLE(ADDITIONALAPPLICATIONTYPECODE, ADDITIONALAPPLICATIONDESIGNATIONID, APPLICATIONAMOUNT, APPLICATIONDESCRIPTION, RECOGNITIONS)
                        select 
                            T.c.value('(ADDITIONALAPPLICATIONTYPECODE)[1]','tinyint') as ADDITIONALAPPLICATIONTYPECODE,
                            T.c.value('(ADDITIONALAPPLICATIONDESIGNATIONID)[1]','uniqueidentifier') as ADDITIONALAPPLICATIONDESIGNATIONID,
                            T.c.value('(APPLICATIONAMOUNT)[1]','money') as APPLICATIONAMOUNT,
                            T.c.value('(APPLICATIONDESCRIPTION)[1]','nvarchar(100)') as APPLICATIONDESCRIPTION,
                            case when T.c.exist('./RECOGNITIONS/ITEM') = 1 then T.c.query('(RECOGNITIONS/ITEM)') else null end as RECOGNITIONS
                        from @UPDATEDAPPLICATIONRECOGNITIONS.nodes('/APPLICATIONRECOGNITIONS/ITEM') T(c)  

                    -- Modified to fix 489752

                    update @APPLICATIONRECOGNITIONSTABLE set
                        --Rebuild the recognitions XML

                        RECOGNITIONS =
                            (
                                select
                                    --Replace @BATCHREVENUECONSTITUENTID with @CONSTITUENTID 

                                    case
                                        when T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') = @BATCHREVENUECONSTITUENTID
                                            then @CONSTITUENTID
                                        else
                                            T.c.value('(CONSTITUENTID)[1]','uniqueidentifier')
                                    end as CONSTITUENTID,

                                    --Preserve all other values

                                    T.c.value('(AMOUNT)[1]','money') as AMOUNT,
                                    T.c.value('(EFFECTIVEDATE)[1]','datetime') as EFFECTIVEDATE,
                                    T.c.value('(ID)[1]','uniqueidentifier') as ID
                                from
                                    RECOGNITIONS.nodes('ITEM') as T(c)
                                for xml raw('ITEM'),type,elements,BINARY BASE64
                            )
                    where
                        --Only do this rebuild for the application recognitions where there is a value that

                        --needs to be changed.

                        exists
                            (
                                select
                                    'x'
                                from
                                    RECOGNITIONS.nodes('ITEM') as T(c)
                                where
                                    T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') = @BATCHREVENUECONSTITUENTID
                            );

                    set @UPDATEDAPPLICATIONRECOGNITIONS = 
                        (
                            select 
                                ADDITIONALAPPLICATIONTYPECODE,
                                ADDITIONALAPPLICATIONDESIGNATIONID,
                                APPLICATIONAMOUNT,
                                APPLICATIONDESCRIPTION,
                                RECOGNITIONS
                            from @APPLICATIONRECOGNITIONSTABLE
                            for xml raw('ITEM'), type, elements, root('APPLICATIONRECOGNITIONS'), binary base64
                        )    
                    end