USP_PLANNEDGIFT_ASSETS_2_ADDFROMXML

Used to add a set of records defined by UFN_PLANNEDGIFT_ASSETS_2 from the given xml string.
Parameters

Parameter	Parameter Type	Mode
@PLANNEDGIFTID	uniqueidentifier	IN
@XML	xml	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CHANGEDATE	datetime	IN
Definition

Copy


            create procedure dbo.USP_PLANNEDGIFT_ASSETS_2_ADDFROMXML
            (
                @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 @ASSETS 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,
                   [TRANSACTIONCURRENCYID] uniqueidentifier,
                   [TRANSACTIONVALUE] money,
                   [TRANSACTIONCOSTBASIS] money,
                   [BASEEXCHANGERATEID] uniqueidentifier)

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

                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 [PLANNEDGIFTASSET] 
                    ([PLANNEDGIFTID], 
                    [ADDRESSID],
                    [ASSETTYPECODEID],
                    [ASSETVALUATIONMETHODCODEID],
                    [COSTBASIS],
                    [DESCRIPTION],
                    [ID],
                    [SEQUENCE],
                    [VALUATIONSOURCE],
                    [VALUE],
                    [BASECURRENCYID],
                    [ORGANIZATIONVALUE],
                    [ORGANIZATIONCOSTBASIS],
                    [ORGANIZATIONEXCHANGERATEID],
                    [TRANSACTIONCURRENCYID],
                    [TRANSACTIONVALUE],
                    [TRANSACTIONCOSTBASIS],
                    [BASEEXCHANGERATEID],
                    ADDEDBYID, 
                    CHANGEDBYID, 
                    DATEADDED, 
                    DATECHANGED)
                select @PLANNEDGIFTID, 
                    [ADDRESSID],
                    [ASSETTYPECODEID],
                    [ASSETVALUATIONMETHODCODEID],
                    [COSTBASIS],
                    [DESCRIPTION],
                    [ID],
                    [SEQUENCE],
                    [VALUATIONSOURCE],
                    [VALUE], 
                    [BASECURRENCYID],
                    [ORGANIZATIONVALUE],
                    [ORGANIZATIONCOSTBASIS],
                    [ORGANIZATIONEXCHANGERATEID],
                    [TRANSACTIONCURRENCYID],
                    [TRANSACTIONVALUE],
                    [TRANSACTIONCOSTBASIS],
                    [BASEEXCHANGERATEID],
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CHANGEDATE, 
                    @CHANGEDATE
                from @ASSETS as [temp]

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

                if @@Error <> 0
                    return 2;

                return 0;