USP_REVENUE_GETSOLICITORS_ADDFROMXML

            CREATE procedure dbo.USP_REVENUE_GETSOLICITORS_ADDFROMXML
            (
                @REVENUESPLITID 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()

                --Multicurrency workaround for Fire: Set currency fields for all inserts and updates to avoid the trigger performance hit during batch commit (we are assuming this is a single currency system for Fire).

                declare @ORGANIZATIONCURRENCYID uniqueidentifier;
                set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

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

                declare @TempTbl table (
                   [AMOUNT] money,
                   [CONSTITUENTID] uniqueidentifier,
                   [ID] uniqueidentifier,
                   [BUSINESSUNITCODEID] uniqueidentifier,
                   [SEQUENCE] int)

                insert into @TempTbl([AMOUNT], [CONSTITUENTID], [ID], [SEQUENCE]) select 
                    [AMOUNT],
                    [CONSTITUENTID],
                    [ID],
                    [SEQUENCE] 
                from dbo.UFN_REVENUE_GETSOLICITORS_FROMITEMLISTXML(@XML)

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

                -- Update the BUSINESSUNITCODEID here

                declare @REVENUEDATE datetime;
                select 
                    @REVENUEDATE = REVENUE.[DATE]
                from 
                    dbo.REVENUESPLIT
                    inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
                where REVENUESPLIT.ID = @REVENUESPLITID;

                with CONSTITUENTBUSINESSUNIT as (
                    select
                        ORGANIZATIONPOSITIONHOLDER.CONSTITUENTID,
                        ORGANIZATIONPOSITION.BUSINESSUNITCODEID
                    from
                        dbo.ORGANIZATIONPOSITIONHOLDER
                        inner join dbo.ORGANIZATIONPOSITION on ORGANIZATIONPOSITION.ID = ORGANIZATIONPOSITIONHOLDER.POSITIONID
                    where
                        (@REVENUEDATE between ORGANIZATIONPOSITIONHOLDER.DATEFROM and ORGANIZATIONPOSITIONHOLDER.DATETO)
                        or (ORGANIZATIONPOSITIONHOLDER.DATETO is null and @REVENUEDATE >= ORGANIZATIONPOSITIONHOLDER.DATEFROM)        
                )
                update 
                    @TempTbl
                set 
                    BUSINESSUNITCODEID = CONSTITUENTBUSINESSUNIT.BUSINESSUNITCODEID
                from
                    @TempTbl [SOLICITORS]
                    inner join CONSTITUENTBUSINESSUNIT on CONSTITUENTBUSINESSUNIT.CONSTITUENTID = SOLICITORS.CONSTITUENTID
                where
                    ID = [SOLICITORS].ID;

                if @@Error <> 0
                    return 1;

                -- insert new items

                insert into [REVENUESOLICITOR] 
                    ([REVENUESPLITID], 
                    [AMOUNT],
                    [CONSTITUENTID],
                    [ID],
                    [BUSINESSUNITCODEID],
                    [SEQUENCE],
                    [BASECURRENCYID],
                    [ORGANIZATIONAMOUNT],
                    [ORGANIZATIONEXCHANGERATEID],
                    ADDEDBYID, 
                    CHANGEDBYID, 
                    DATEADDED, 
                    DATECHANGED)
                select @REVENUESPLITID, 
                    [AMOUNT],
                    [CONSTITUENTID],
                    [ID],
                    [BUSINESSUNITCODEID],
                    [SEQUENCE],
                    @ORGANIZATIONCURRENCYID,
                    [AMOUNT],
                    null,
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CHANGEDATE, 
                    @CHANGEDATE
                from @TempTbl as [temp]

                if @@Error <> 0
                    return 2;

                return 0;