USP_PLANNEDGIFTADDITION_GETASSETS_UPDATEFROMXML

          CREATE PROCEDURE dbo.USP_PLANNEDGIFTADDITION_GETASSETS_UPDATEFROMXML 
          (
            @PLANNEDGIFTADDITIONID 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,
               [BASECURRENCYID] uniqueidentifier,
               [BASEEXCHANGERATEID] uniqueidentifier,
               [COSTBASIS] money,
               [DESCRIPTION] nvarchar(1000),
               [ID] uniqueidentifier,
               [ORGANIZATIONCOSTBASIS] money,
               [ORGANIZATIONEXCHANGERATEID] uniqueidentifier,
               [ORGANIZATIONVALUE] money,
               [SEQUENCE] int,
               [TRANSACTIONCOSTBASIS] money,
               [TRANSACTIONCURRENCYID] uniqueidentifier,
               [TRANSACTIONVALUE] money,
               [VALUATIONSOURCE] nvarchar(200),
               [VALUE] money,
               [ASSETHOLDERS] xml)

            insert into @TempTbl select 
                [ADDRESSID],
                [ASSETTYPECODEID],
                [ASSETVALUATIONMETHODCODEID],
                [BASECURRENCYID],
                [BASEEXCHANGERATEID],
                [COSTBASIS],
                [DESCRIPTION],
                [ID],
                [ORGANIZATIONCOSTBASIS],
                [ORGANIZATIONEXCHANGERATEID],
                [ORGANIZATIONVALUE],
                [SEQUENCE],
                [TRANSACTIONCOSTBASIS],
                [TRANSACTIONCURRENCYID],
                [TRANSACTIONVALUE],
                [VALUATIONSOURCE],
                [VALUE],
                [ASSETHOLDERS]
            from dbo.UFN_PLANNEDGIFTADDITION_GETASSETS_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.[PLANNEDGIFTADDITIONASSET] where [PLANNEDGIFTADDITIONASSET].ID in 
                (select ID from dbo.UFN_PLANNEDGIFTADDITION_GETASSETS
                (
                    @PLANNEDGIFTADDITIONID
                )
                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 [PLANNEDGIFTADDITIONASSET]
                    set [PLANNEDGIFTADDITIONASSET].[ADDRESSID]=temp.[ADDRESSID],
                    [PLANNEDGIFTADDITIONASSET].[ASSETTYPECODEID]=temp.[ASSETTYPECODEID],
                    [PLANNEDGIFTADDITIONASSET].[ASSETVALUATIONMETHODCODEID]=temp.[ASSETVALUATIONMETHODCODEID],
                    [PLANNEDGIFTADDITIONASSET].[BASECURRENCYID]=temp.[BASECURRENCYID],
                    [PLANNEDGIFTADDITIONASSET].[BASEEXCHANGERATEID]=temp.[BASEEXCHANGERATEID],
                    [PLANNEDGIFTADDITIONASSET].[COSTBASIS]=temp.[COSTBASIS],
                    [PLANNEDGIFTADDITIONASSET].[DESCRIPTION]=temp.[DESCRIPTION],
                    [PLANNEDGIFTADDITIONASSET].[ID]=temp.[ID],
                    [PLANNEDGIFTADDITIONASSET].[ORGANIZATIONCOSTBASIS]=temp.[ORGANIZATIONCOSTBASIS],
                    [PLANNEDGIFTADDITIONASSET].[ORGANIZATIONEXCHANGERATEID]=temp.[ORGANIZATIONEXCHANGERATEID],
                    [PLANNEDGIFTADDITIONASSET].[ORGANIZATIONVALUE]=temp.[ORGANIZATIONVALUE],
                    [PLANNEDGIFTADDITIONASSET].[SEQUENCE]=temp.[SEQUENCE],
                    [PLANNEDGIFTADDITIONASSET].[TRANSACTIONCOSTBASIS]=temp.[TRANSACTIONCOSTBASIS],
                    [PLANNEDGIFTADDITIONASSET].[TRANSACTIONCURRENCYID]=temp.[TRANSACTIONCURRENCYID],
                    [PLANNEDGIFTADDITIONASSET].[TRANSACTIONVALUE]=temp.[TRANSACTIONVALUE],
                    [PLANNEDGIFTADDITIONASSET].[VALUATIONSOURCE]=temp.[VALUATIONSOURCE],
                    [PLANNEDGIFTADDITIONASSET].[VALUE]=temp.[VALUE],
                    [PLANNEDGIFTADDITIONASSET].CHANGEDBYID = @CHANGEAGENTID,
                    [PLANNEDGIFTADDITIONASSET].DATECHANGED = @CHANGEDATE

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

            if @@Error <> 0
                return 3;    

            -- insert new items

            insert into [PLANNEDGIFTADDITIONASSET] 
                ([PLANNEDGIFTADDITIONID], 
                [ADDRESSID],
                [ASSETTYPECODEID],
                [ASSETVALUATIONMETHODCODEID],
                [BASECURRENCYID],
                [BASEEXCHANGERATEID],
                [COSTBASIS],
                [DESCRIPTION],
                [ID],
                [ORGANIZATIONCOSTBASIS],
                [ORGANIZATIONEXCHANGERATEID],
                [ORGANIZATIONVALUE],
                [SEQUENCE],
                [TRANSACTIONCOSTBASIS],
                [TRANSACTIONCURRENCYID],
                [TRANSACTIONVALUE],
                [VALUATIONSOURCE],
                [VALUE],                
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED)
            select @PLANNEDGIFTADDITIONID, 
                [ADDRESSID],
                [ASSETTYPECODEID],
                [ASSETVALUATIONMETHODCODEID],
                [BASECURRENCYID],
                [BASEEXCHANGERATEID],
                [COSTBASIS],
                [DESCRIPTION],
                [ID],
                [ORGANIZATIONCOSTBASIS],
                [ORGANIZATIONEXCHANGERATEID],
                [ORGANIZATIONVALUE],
                [SEQUENCE],
                [TRANSACTIONCOSTBASIS],
                [TRANSACTIONCURRENCYID],
                [TRANSACTIONVALUE],
                [VALUATIONSOURCE],
                [VALUE], 
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CHANGEDATE, 
                @CHANGEDATE
            from @TempTbl as [temp]
            where not exists (select ID from dbo.[PLANNEDGIFTADDITIONASSET] 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_PLANNEDGIFTADDITION_GETASSETHOLDERS_UPDATEFROMXML @PLANNEDGIFTASSETID, @ASSETHOLDERS, @CHANGEAGENTID, @CHANGEDATE; 
                fetch next from ASSETCURSOR into @PLANNEDGIFTASSETID, @ASSETHOLDERS;
            end
            close ASSETCURSOR
            deallocate ASSETCURSOR

            if @@Error <> 0
                return 4;

            return 0;