USP_REVENUE_PULLSOLICITORSFROMFUNDINGREQUEST_ONLYNEWSPLITS

            CREATE procedure USP_REVENUE_PULLSOLICITORSFROMFUNDINGREQUEST_ONLYNEWSPLITS
            (
                @SPLITS xml,
                @REVENUEID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,
                @CURRENTDATE datetime
            )
            as begin
                -- Call this after the new splits have been added to the database.


                set nocount on;

                --Multicurrency - Retrieve multicurrency info for conversions

                declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();                
                declare @BASECURRENCYID uniqueidentifier;
                declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

                select
                    @BASECURRENCYID = BASECURRENCYID,
                    @ORGANIZATIONEXCHANGERATEID = ORGANIZATIONEXCHANGERATEID
                from 
                    dbo.REVENUE
                where 
                    ID = @REVENUEID

                declare @FUNDINGREQUESTID uniqueidentifier;
                declare @SPLITSTBL table
                (
                    REVENUESPLITID uniqueidentifier
                )

                declare @ACTIVEREVENUESPLITSTBL table
                (
                    REVENUESPLITID uniqueidentifier,
                    AMOUNT money
                )

                select
                    @FUNDINGREQUESTID = REVENUEFUNDINGREQUEST.FUNDINGREQUESTID
                from dbo.REVENUEFUNDINGREQUEST
                where REVENUEFUNDINGREQUEST.ID = @REVENUEID

                insert into @SPLITSTBL
                    (REVENUESPLITID)
                select
                    T.c.value('(ID)[1]','uniqueidentifier') as REVENUESPLITID
                from
                    @SPLITS.nodes('/SPLITS/ITEM') T(c)


                -- Get the splits that are on the record, including only those

                -- that have credit rules active and were newly created (they have no ID in

                -- the table from the XML).

                insert into @ACTIVEREVENUESPLITSTBL
                    (REVENUESPLITID, AMOUNT)
                select
                    REVENUESPLIT.ID,
                    REVENUESPLIT.AMOUNT
                from
                    dbo.REVENUESPLIT
                    left join @SPLITSTBL as SPLITSTBL on SPLITSTBL.REVENUESPLITID = REVENUESPLIT.ID
                where
                    (REVENUESPLIT.REVENUEID = @REVENUEID)
                    and
                    (dbo.UFN_SOLICITORCREDITRULES_ACTIVEFORREVENUESPLIT(REVENUESPLIT.ID) = 1)
                    and
                    ((select count(ID) from dbo.REVENUESOLICITOR where REVENUESPLITID = REVENUESPLIT.ID) = 0)


                insert into dbo.REVENUESOLICITOR
                (
                    REVENUESPLITID,
                    CONSTITUENTID,
                    AMOUNT,
                    SEQUENCE,
                    DATEADDED,
                    DATECHANGED,
                    ADDEDBYID,
                    CHANGEDBYID,
                    BASECURRENCYID,
                    ORGANIZATIONAMOUNT,
                    ORGANIZATIONEXCHANGERATEID
                )
                select
                    ACTIVESPLITS.REVENUESPLITID,
                    SOLICITORS.ID,
                    (SOLICITORS.CREDITPERCENTAGE * ACTIVESPLITS.AMOUNT) / 100.0,
                    SOLICITORS.SEQUENCE,
                    @CURRENTDATE,
                    @CURRENTDATE,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @BASECURRENCYID BASECURRENCYID,
                    case  --Multicurrency - Calculate organization amount.

                        when @BASECURRENCYID <> @ORGANIZATIONCURRENCYID
                            then dbo.UFN_CURRENCY_CONVERT((SOLICITORS.CREDITPERCENTAGE * ACTIVESPLITS.AMOUNT) / 100.0, @ORGANIZATIONEXCHANGERATEID)
                        else (SOLICITORS.CREDITPERCENTAGE * ACTIVESPLITS.AMOUNT) / 100.0
                    end ORGANIZATIONAMOUNT,
                    @ORGANIZATIONEXCHANGERATEID ORGANIZATIONEXCHANGERATEID
                from
                    dbo.UFN_FUNDINGREQUEST_GETFUNDRAISERSANDCREDITS(@FUNDINGREQUESTID) as SOLICITORS
                    cross join @ACTIVEREVENUESPLITSTBL as ACTIVESPLITS

            end