USP_REVENUE_PULLSOLICITORSFROMOPPORTUNITY

            CREATE procedure USP_REVENUE_PULLSOLICITORSFROMOPPORTUNITY
            (
                @REVENUEID uniqueidentifier,
                @OPPORTUNITYID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,
                @CURRENTDATE datetime
            )
            as begin

                set nocount on;

                --Multicurrency - AdamBu 3/30/10 - 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 @PROSPECTPLANID uniqueidentifier
                declare @ActiveRevenueSplitsTbl table
                (
                    REVENUESPLITID uniqueidentifier,
                    AMOUNT money
                )

                select
                    @PROSPECTPLANID = PROSPECTPLANID
                from
                    dbo.OPPORTUNITY
                where
                    ID = @OPPORTUNITYID


                -- Load the default solicitors for each split that:

                --    1.  has no solicitors

                --    2.  had a null opportunity before, or had an opportunity change.

                --    3.  is of a type that's active


                insert into @ActiveRevenueSplitsTbl
                    (REVENUESPLITID, AMOUNT)
                select
                    REVENUESPLIT.ID,
                    REVENUESPLIT.AMOUNT
                from
                    dbo.REVENUESPLIT
                    left join dbo.REVENUEOPPORTUNITY on REVENUEOPPORTUNITY.ID = REVENUESPLIT.ID
                where
                    (REVENUEOPPORTUNITY.OPPORTUNITYID is null or REVENUEOPPORTUNITY.OPPORTUNITYID <> @OPPORTUNITYID)
                    and
                    ((select COUNT(ID) from dbo.REVENUESOLICITOR where REVENUESPLITID = REVENUESPLIT.ID) = 0)
                    and
                    (REVENUESPLIT.REVENUEID = @REVENUEID)
                    and
                    (dbo.UFN_SOLICITORCREDITRULES_ACTIVEFORREVENUESPLIT(REVENUESPLIT.ID) = 1)

                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 - AdamBu 3/30/10 - 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_PROSPECTPLAN_GETFUNDRAISERSANDCREDITS_2(@PROSPECTPLANID, @OPPORTUNITYID) as SOLICITORS
                    cross join @ActiveRevenueSplitsTbl as ACTIVESPLITS

            end