USP_PLANNEDGIFT_ASSETS_2_UPDATEFROMXML

            create procedure dbo.USP_PLANNEDGIFT_ASSETS_2_UPDATEFROMXML
            (
                @PLANNEDGIFTID uniqueidentifier,
                @XML xml,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            )

            as

            set nocount on;

            if @CHANGEAGENTID is null
                exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

            if @CHANGEDATE is null 
                set @CHANGEDATE = getdate()

            -- build a temporary table containing the values from the XML

            declare @TempTbl table (
                [ADDRESSID] uniqueidentifier,
                [ASSETTYPECODEID] uniqueidentifier,
                [ASSETVALUATIONMETHODCODEID] uniqueidentifier,
                [COSTBASIS] money,
                [DESCRIPTION] nvarchar(1000),
                [ID] uniqueidentifier,
                [SEQUENCE] int,
                [VALUATIONSOURCE] nvarchar(200),
                [VALUE] money,
                [ASSETHOLDERS] xml,
                [BASECURRENCYID] uniqueidentifier,
                [ORGANIZATIONVALUE] money,
                [ORGANIZATIONCOSTBASIS] money,
                [ORGANIZATIONEXCHANGERATEID] uniqueidentifier,
                [TRANSACTIONVALUE] money,
                [TRANSACTIONCOSTBASIS] money,
                [TRANSACTIONCURRENCYID] uniqueidentifier,
                [BASEEXCHANGERATEID] uniqueidentifier)

            insert into @TempTbl select 
                [ADDRESSID],
                [ASSETTYPECODEID],
                [ASSETVALUATIONMETHODCODEID],
                [COSTBASIS],
                [DESCRIPTION],
                [ID],
                [SEQUENCE],
                [VALUATIONSOURCE],
                [VALUE],
                [ASSETHOLDERS],
                [BASECURRENCYID],
                [ORGANIZATIONVALUE],
                [ORGANIZATIONCOSTBASIS],
                [ORGANIZATIONEXCHANGERATEID],
                [TRANSACTIONVALUE],
                [TRANSACTIONCOSTBASIS],
                [TRANSACTIONCURRENCYID],
                [BASEEXCHANGERATEID]
            from dbo.UFN_PLANNEDGIFT_ASSETS_2_FROMITEMLISTXML(@XML)

            update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');

            if @@Error <> 0
                return 1;

            declare @contextCache varbinary(128);
            declare @e int;

            -- cache current context information 

            set @contextCache = CONTEXT_INFO();

            -- set CONTEXT_INFO to @CHANGEAGENTID 

            if not @CHANGEAGENTID is null
                set CONTEXT_INFO @CHANGEAGENTID;

            -- delete any items that no longer exist in the XML table

            delete from dbo.[PLANNEDGIFTASSET] where [PLANNEDGIFTASSET].ID in 
                (select ID from dbo.UFN_PLANNEDGIFT_ASSETS_2
                (
                    @PLANNEDGIFTID
                )
                EXCEPT select ID from @TempTbl)    

            select @e=@@error;

            -- reset CONTEXT_INFO to previous value 

            if not @contextCache is null
                set CONTEXT_INFO @contextCache;

            if @e <> 0
                return 2;

            -- update the items that exist in the XML table and the db

            update [PLANNEDGIFTASSET]
                    set [PLANNEDGIFTASSET].[ADDRESSID]=temp.[ADDRESSID],
                    [PLANNEDGIFTASSET].[ASSETTYPECODEID]=temp.[ASSETTYPECODEID],
                    [PLANNEDGIFTASSET].[ASSETVALUATIONMETHODCODEID]=temp.[ASSETVALUATIONMETHODCODEID],
                    [PLANNEDGIFTASSET].[COSTBASIS]=temp.[COSTBASIS],
                    [PLANNEDGIFTASSET].[DESCRIPTION]=temp.[DESCRIPTION],
                    [PLANNEDGIFTASSET].[ID]=temp.[ID],
                    [PLANNEDGIFTASSET].[SEQUENCE]=temp.[SEQUENCE],
                    [PLANNEDGIFTASSET].[VALUATIONSOURCE]=temp.[VALUATIONSOURCE],
                    [PLANNEDGIFTASSET].[VALUE]=temp.[VALUE],
                    [PLANNEDGIFTASSET].[BASECURRENCYID]=temp.[BASECURRENCYID],
                    [PLANNEDGIFTASSET].[ORGANIZATIONVALUE]=temp.[ORGANIZATIONVALUE],
                    [PLANNEDGIFTASSET].[ORGANIZATIONCOSTBASIS]=temp.[ORGANIZATIONCOSTBASIS],
                    [PLANNEDGIFTASSET].[ORGANIZATIONEXCHANGERATEID]=temp.[ORGANIZATIONEXCHANGERATEID],
                    [PLANNEDGIFTASSET].[TRANSACTIONVALUE]=temp.[TRANSACTIONVALUE],
                    [PLANNEDGIFTASSET].[TRANSACTIONCOSTBASIS]=temp.[TRANSACTIONCOSTBASIS],
                    [PLANNEDGIFTASSET].[TRANSACTIONCURRENCYID]=temp.[TRANSACTIONCURRENCYID],
                    [PLANNEDGIFTASSET].[BASEEXCHANGERATEID]=temp.[BASEEXCHANGERATEID],
                    [PLANNEDGIFTASSET].CHANGEDBYID = @CHANGEAGENTID,
                    [PLANNEDGIFTASSET].DATECHANGED = @CHANGEDATE

                from dbo.[PLANNEDGIFTASSET] inner join @TempTbl as [temp] on [PLANNEDGIFTASSET].ID = [temp].ID
                where ([PLANNEDGIFTASSET].[ADDRESSID]<>temp.[ADDRESSID]) or 
                    ([PLANNEDGIFTASSET].[ADDRESSID] is null and temp.[ADDRESSID] is not null) or 
                    ([PLANNEDGIFTASSET].[ADDRESSID] is not null and temp.[ADDRESSID] is null) or 
                    ([PLANNEDGIFTASSET].[ASSETTYPECODEID]<>temp.[ASSETTYPECODEID]) or 
                    ([PLANNEDGIFTASSET].[ASSETTYPECODEID] is null and temp.[ASSETTYPECODEID] is not null) or 
                    ([PLANNEDGIFTASSET].[ASSETTYPECODEID] is not null and temp.[ASSETTYPECODEID] is null) or 
                    ([PLANNEDGIFTASSET].[ASSETVALUATIONMETHODCODEID]<>temp.[ASSETVALUATIONMETHODCODEID]) or 
                    ([PLANNEDGIFTASSET].[ASSETVALUATIONMETHODCODEID] is null and temp.[ASSETVALUATIONMETHODCODEID] is not null) or 
                    ([PLANNEDGIFTASSET].[ASSETVALUATIONMETHODCODEID] is not null and temp.[ASSETVALUATIONMETHODCODEID] is null) or 
                    ([PLANNEDGIFTASSET].[COSTBASIS]<>temp.[COSTBASIS]) or 
                    ([PLANNEDGIFTASSET].[COSTBASIS] is null and temp.[COSTBASIS] is not null) or 
                    ([PLANNEDGIFTASSET].[COSTBASIS] is not null and temp.[COSTBASIS] is null) or 
                    ([PLANNEDGIFTASSET].[DESCRIPTION]<>temp.[DESCRIPTION]) or 
                    ([PLANNEDGIFTASSET].[DESCRIPTION] is null and temp.[DESCRIPTION] is not null) or 
                    ([PLANNEDGIFTASSET].[DESCRIPTION] is not null and temp.[DESCRIPTION] is null) or 
                    ([PLANNEDGIFTASSET].[ID]<>temp.[ID]) or 
                    ([PLANNEDGIFTASSET].[ID] is null and temp.[ID] is not null) or 
                    ([PLANNEDGIFTASSET].[ID] is not null and temp.[ID] is null) or 
                    ([PLANNEDGIFTASSET].[SEQUENCE]<>temp.[SEQUENCE]) or 
                    ([PLANNEDGIFTASSET].[SEQUENCE] is null and temp.[SEQUENCE] is not null) or 
                    ([PLANNEDGIFTASSET].[SEQUENCE] is not null and temp.[SEQUENCE] is null) or 
                    ([PLANNEDGIFTASSET].[VALUATIONSOURCE]<>temp.[VALUATIONSOURCE]) or 
                    ([PLANNEDGIFTASSET].[VALUATIONSOURCE] is null and temp.[VALUATIONSOURCE] is not null) or 
                    ([PLANNEDGIFTASSET].[VALUATIONSOURCE] is not null and temp.[VALUATIONSOURCE] is null) or 
                    ([PLANNEDGIFTASSET].[VALUE]<>temp.[VALUE]) or 
                    ([PLANNEDGIFTASSET].[VALUE] is null and temp.[VALUE] is not null) or 
                    ([PLANNEDGIFTASSET].[VALUE] is not null and temp.[VALUE] is null) or
                    ([PLANNEDGIFTASSET].[BASECURRENCYID]<>temp.[BASECURRENCYID]) or 
                    ([PLANNEDGIFTASSET].[BASECURRENCYID] is null and temp.[BASECURRENCYID] is not null) or 
                    ([PLANNEDGIFTASSET].[BASECURRENCYID] is not null and temp.[BASECURRENCYID] is null) or 
                    ([PLANNEDGIFTASSET].[ORGANIZATIONVALUE]<>temp.[ORGANIZATIONVALUE]) or 
                    ([PLANNEDGIFTASSET].[ORGANIZATIONVALUE] is null and temp.[ORGANIZATIONVALUE] is not null) or 
                    ([PLANNEDGIFTASSET].[ORGANIZATIONVALUE] is not null and temp.[ORGANIZATIONVALUE] is null) or 
                    ([PLANNEDGIFTASSET].[ORGANIZATIONCOSTBASIS]<>temp.[ORGANIZATIONCOSTBASIS]) or 
                    ([PLANNEDGIFTASSET].[ORGANIZATIONCOSTBASIS] is null and temp.[ORGANIZATIONCOSTBASIS] is not null) or 
                    ([PLANNEDGIFTASSET].[ORGANIZATIONCOSTBASIS] is not null and temp.[ORGANIZATIONCOSTBASIS] is null) or 
                    ([PLANNEDGIFTASSET].[ORGANIZATIONEXCHANGERATEID]<>temp.[ORGANIZATIONEXCHANGERATEID]) or 
                    ([PLANNEDGIFTASSET].[ORGANIZATIONEXCHANGERATEID] is null and temp.[ORGANIZATIONEXCHANGERATEID] is not null) or 
                    ([PLANNEDGIFTASSET].[ORGANIZATIONEXCHANGERATEID] is not null and temp.[ORGANIZATIONEXCHANGERATEID] is null) or 
                    ([PLANNEDGIFTASSET].[TRANSACTIONVALUE]<>temp.[TRANSACTIONVALUE]) or 
                    ([PLANNEDGIFTASSET].[TRANSACTIONVALUE] is null and temp.[TRANSACTIONVALUE] is not null) or 
                    ([PLANNEDGIFTASSET].[TRANSACTIONVALUE] is not null and temp.[TRANSACTIONVALUE] is null) or 
                    ([PLANNEDGIFTASSET].[TRANSACTIONCOSTBASIS]<>temp.[TRANSACTIONCOSTBASIS]) or 
                    ([PLANNEDGIFTASSET].[TRANSACTIONCOSTBASIS] is null and temp.[TRANSACTIONCOSTBASIS] is not null) or 
                    ([PLANNEDGIFTASSET].[TRANSACTIONCOSTBASIS] is not null and temp.[TRANSACTIONCOSTBASIS] is null) or 
                    ([PLANNEDGIFTASSET].[TRANSACTIONCURRENCYID]<>temp.[TRANSACTIONCURRENCYID]) or 
                    ([PLANNEDGIFTASSET].[TRANSACTIONCURRENCYID] is null and temp.[TRANSACTIONCURRENCYID] is not null) or 
                    ([PLANNEDGIFTASSET].[TRANSACTIONCURRENCYID] is not null and temp.[TRANSACTIONCURRENCYID] is null) or 
                    ([PLANNEDGIFTASSET].[BASEEXCHANGERATEID]<>temp.[BASEEXCHANGERATEID]) or 
                    ([PLANNEDGIFTASSET].[BASEEXCHANGERATEID] is null and temp.[BASEEXCHANGERATEID] is not null) or 
                    ([PLANNEDGIFTASSET].[BASEEXCHANGERATEID] is not null and temp.[BASEEXCHANGERATEID] is null)

            if @@Error <> 0
                return 3;    

            -- insert new items

            insert into [PLANNEDGIFTASSET] 
                ([PLANNEDGIFTID], 
                [ADDRESSID],
                [ASSETTYPECODEID],
                [ASSETVALUATIONMETHODCODEID],
                [COSTBASIS],
                [DESCRIPTION],
                [ID],
                [SEQUENCE],
                [VALUATIONSOURCE],
                [VALUE],
                [BASECURRENCYID],
                [ORGANIZATIONVALUE],
                [ORGANIZATIONCOSTBASIS],
                [ORGANIZATIONEXCHANGERATEID],
                [TRANSACTIONVALUE],
                [TRANSACTIONCOSTBASIS],
                [TRANSACTIONCURRENCYID],
                [BASEEXCHANGERATEID],                
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED)
            select @PLANNEDGIFTID, 
                [ADDRESSID],
                [ASSETTYPECODEID],
                [ASSETVALUATIONMETHODCODEID],
                [COSTBASIS],
                [DESCRIPTION],
                [ID],
                [SEQUENCE],
                [VALUATIONSOURCE],
                [VALUE],
                [BASECURRENCYID],
                [ORGANIZATIONVALUE],
                [ORGANIZATIONCOSTBASIS],
                [ORGANIZATIONEXCHANGERATEID],
                [TRANSACTIONVALUE],
                [TRANSACTIONCOSTBASIS],
                [TRANSACTIONCURRENCYID],
                [BASEEXCHANGERATEID], 
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CHANGEDATE, 
                @CHANGEDATE
            from @TempTbl as [temp]
            where not exists (select ID from dbo.[PLANNEDGIFTASSET] as data where data.ID = [temp].ID)

            declare @PLANNEDGIFTASSETID uniqueidentifier;
            declare @ASSETHOLDERS xml;
            declare ASSETCURSOR cursor local fast_forward for
                select
                    [temp].ID,
                    ASSETHOLDERS
                from
                    @TempTbl as [temp]
            open ASSETCURSOR
            fetch next from ASSETCURSOR into @PLANNEDGIFTASSETID, @ASSETHOLDERS;
            while @@FETCH_STATUS = 0
            begin
                exec dbo.USP_PLANNEDGIFT_ASSETHOLDERS_UPDATEFROMXML @PLANNEDGIFTASSETID, @ASSETHOLDERS, @CHANGEAGENTID, @CHANGEDATE; 
                fetch next from ASSETCURSOR into @PLANNEDGIFTASSETID, @ASSETHOLDERS;
            end
            close ASSETCURSOR
            deallocate ASSETCURSOR

            if @@Error <> 0
                return 4;

            return 0;