USP_PLANNEDGIFTADDITION_GETASSETS_ADDFROMXML

          CREATE procedure dbo.USP_PLANNEDGIFTADDITION_GETASSETS_ADDFROMXML 
          (
            @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 @ASSETS 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 @ASSETS 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)

            select * from @ASSETS

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

            if @@Error <> 0
                return 1;

            -- 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 @ASSETS as [temp]

            declare @PLANNEDGIFTADDITIONASSETID uniqueidentifier;
            declare @ASSETHOLDERS xml;
            declare ASSETCURSOR cursor local fast_forward for
                select
                    ID,
                    ASSETHOLDERS
                from
                    @ASSETS;
            open ASSETCURSOR
            fetch next from ASSETCURSOR into @PLANNEDGIFTADDITIONASSETID, @ASSETHOLDERS;
            while @@FETCH_STATUS = 0
            begin
                exec dbo.USP_PLANNEDGIFTADDITION_GETASSETHOLDERS_ADDFROMXML @PLANNEDGIFTADDITIONASSETID, @ASSETHOLDERS, @CHANGEAGENTID, @CHANGEDATE;     
                fetch next from ASSETCURSOR into @PLANNEDGIFTADDITIONASSETID, @ASSETHOLDERS;
            end
            close ASSETCURSOR
            deallocate ASSETCURSOR

            if @@Error <> 0
                return 2;

            return 0;