USP_REVENUE_UPDATESOLICITORS_2

    CREATE procedure dbo.USP_REVENUE_UPDATESOLICITORS_2
    (
      @REVENUEID uniqueidentifier,
      @OPPORTUNITYID uniqueidentifier,
      @CHANGEAGENTID uniqueidentifier,
      @CHANGEDATE datetime,
      @OLDSPLITS xml = null
    )
    as
    set nocount on;

    --Solicitors don't have a transaction currency, @ORGANIZATIONEXCHANGERATEIDFROMBASE handles the

    -- case where the revenue split ORGANIZATIONEXCHANGERATE is for converting from transaction to org currencies

    declare @OVERRIDEORGANIZATIONVALUES bit = 0;
    declare @ORGANIZATIONEXCHANGERATEIDFROMBASE uniqueidentifier; 
    declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

    if (1 = (select top 1 ORGANIZATIONAMOUNTORIGINCODE from dbo.MULTICURRENCYCONFIGURATION))
    begin
        set @OVERRIDEORGANIZATIONVALUES = 1;

        declare @DATE datetime;
        declare @BASECURRENCYID uniqueidentifier;
        declare @ORGEXCHANGERATEID uniqueidentifier

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

        if @BASECURRENCYID = @ORGANIZATIONCURRENCYID
        begin
            set @ORGANIZATIONEXCHANGERATEIDFROMBASE = null;
        end
        else
        begin
            set @ORGANIZATIONEXCHANGERATEIDFROMBASE = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATE, 0, null);
        end
    end

    declare @PLANNEDGIFTPROSPECTPLANID uniqueidentifier
    select
        @PLANNEDGIFTPROSPECTPLANID = PLANNEDGIFT.PROSPECTPLANID
    from
        dbo.FINANCIALTRANSACTION
        inner join dbo.PLANNEDGIFTREVENUE on PLANNEDGIFTREVENUE.REVENUEID = FINANCIALTRANSACTION.ID
        inner join dbo.PLANNEDGIFT on PLANNEDGIFT.ID = PLANNEDGIFTREVENUE.ID
    where
        FINANCIALTRANSACTION.ID = @REVENUEID

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

    declare @FUNDINGREQUESTID uniqueidentifier;            
    select 
        @FUNDINGREQUESTID = REVENUEFUNDINGREQUEST.FUNDINGREQUESTID
    from 
        dbo.REVENUEFUNDINGREQUEST
    where
        REVENUEFUNDINGREQUEST.ID = @REVENUEID

    if @FUNDINGREQUESTID is not null
    begin
        update dbo.REVENUESOLICITOR

        set AMOUNT = (SOLICITORS.CREDITPERCENTAGE * REVENUESPLIT.BASEAMOUNT) / 100.0,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CHANGEDATE,
            BASECURRENCYID = @BASECURRENCYID,
            ORGANIZATIONAMOUNT =
                case
                    when @BASECURRENCYID = @ORGANIZATIONCURRENCYID
                        then REVENUESPLIT.BASEAMOUNT
                    when @OVERRIDEORGANIZATIONVALUES = 1
                        then dbo.UFN_CURRENCY_CONVERT(REVENUESPLIT.BASEAMOUNT, @ORGANIZATIONEXCHANGERATEIDFROMBASE)
                    else
                        REVENUESPLIT.ORGAMOUNT
                end,
            ORGANIZATIONEXCHANGERATEID =
                case
                    when @OVERRIDEORGANIZATIONVALUES = 1
                        then @ORGANIZATIONEXCHANGERATEIDFROMBASE
                    else
                        @ORGEXCHANGERATEID
                end
    from dbo.REVENUESOLICITOR
    inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESOLICITOR.REVENUESPLITID = REVENUESPLIT.ID
    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
    inner join dbo.UFN_FUNDINGREQUEST_GETFUNDRAISERSANDCREDITS(@FUNDINGREQUESTID) as SOLICITORS on SOLICITORS.ID =  REVENUESOLICITOR.CONSTITUENTID 
    left join dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@OLDSPLITS) OLDSPLIT on OLDSPLIT.ID = REVENUESPLIT.ID
    where 
        REVENUESPLIT.FINANCIALTRANSACTIONID = @REVENUEID 
        and REVENUESPLIT.DELETEDON is null 
        and REVENUESPLIT.TYPECODE != 1
        -- Exclude opportunity solicitors as we will update them later

        and REVENUESOLICITOR.CONSTITUENTID not in (select ID from dbo.UFN_PROSPECTPLAN_GETFUNDRAISERSANDCREDITS_2(@PROSPECTPLANID, @OPPORTUNITYID));
    end
    else
    begin
    update dbo.REVENUESOLICITOR
        set AMOUNT = case 
                     when OLDSPLIT.AMOUNT is not null and OLDSPLIT.AMOUNT > 0 then
                       (REVENUESOLICITOR.AMOUNT / OLDSPLIT.AMOUNT) * REVENUESPLIT.BASEAMOUNT
                     else
                        REVENUESPLIT.BASEAMOUNT
                     end,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CHANGEDATE,
            BASECURRENCYID = @BASECURRENCYID,
            ORGANIZATIONAMOUNT =
                case
                    when @BASECURRENCYID = @ORGANIZATIONCURRENCYID
                        then REVENUESPLIT.BASEAMOUNT
                    when @OVERRIDEORGANIZATIONVALUES = 1
                        then dbo.UFN_CURRENCY_CONVERT(REVENUESPLIT.BASEAMOUNT, @ORGANIZATIONEXCHANGERATEIDFROMBASE)
                    else
                        REVENUESPLIT.ORGAMOUNT
                end,
            ORGANIZATIONEXCHANGERATEID =
                case
                    when @OVERRIDEORGANIZATIONVALUES = 1
                        then @ORGANIZATIONEXCHANGERATEIDFROMBASE
                    else
                        @ORGEXCHANGERATEID
                end
    from dbo.REVENUESOLICITOR
    inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESOLICITOR.REVENUESPLITID = REVENUESPLIT.ID
    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
    left join dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@OLDSPLITS) OLDSPLIT on OLDSPLIT.ID = REVENUESPLIT.ID
    where 
        REVENUESPLIT.FINANCIALTRANSACTIONID = @REVENUEID 
        and REVENUESPLIT.DELETEDON is null 
        and REVENUESPLIT.TYPECODE != 1
        -- The REVENUESOLICITOR.AMOUNT/REVENUESPLIT.BASEAMOUNT comparison is for backwards compatibility when @OLDSPLITS is not provided

        and (OLDSPLIT.AMOUNT <> REVENUESPLIT.BASEAMOUNT or (OLDSPLIT.AMOUNT is null and REVENUESOLICITOR.AMOUNT <> REVENUESPLIT.BASEAMOUNT))
        -- Exclude opportunity solicitors as we will update them later

        and REVENUESOLICITOR.CONSTITUENTID not in (select ID from dbo.UFN_PROSPECTPLAN_GETFUNDRAISERSANDCREDITS_2(@PROSPECTPLANID, @OPPORTUNITYID)); 
    end

    if @OPPORTUNITYID is not null
    begin

        -- Update solicitors from opportunity

        update dbo.REVENUESOLICITOR
        set AMOUNT =  (SOLICITORS.CREDITPERCENTAGE * REVENUESPLIT.BASEAMOUNT) / 100.0,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CHANGEDATE,
            BASECURRENCYID = @BASECURRENCYID,
            ORGANIZATIONAMOUNT =
                case
                    when @BASECURRENCYID = @ORGANIZATIONCURRENCYID
                        then (SOLICITORS.CREDITPERCENTAGE * REVENUESPLIT.BASEAMOUNT) / 100.0
                    when @OVERRIDEORGANIZATIONVALUES = 1
                        then dbo.UFN_CURRENCY_CONVERT((SOLICITORS.CREDITPERCENTAGE * REVENUESPLIT.BASEAMOUNT) / 100.0, @ORGANIZATIONEXCHANGERATEIDFROMBASE)
                    else
                        (SOLICITORS.CREDITPERCENTAGE * REVENUESPLIT.ORGAMOUNT) / 100.0
                end,
            ORGANIZATIONEXCHANGERATEID =
                case
                    when @OVERRIDEORGANIZATIONVALUES = 1
                        then @ORGANIZATIONEXCHANGERATEIDFROMBASE
                    else
                        @ORGEXCHANGERATEID
                end    
        from dbo.REVENUESOLICITOR
            inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESOLICITOR.REVENUESPLITID = REVENUESPLIT.ID
            inner join dbo.UFN_PROSPECTPLAN_GETFUNDRAISERSANDCREDITS_2(@PROSPECTPLANID, @OPPORTUNITYID) as SOLICITORS on SOLICITORS.ID =  REVENUESOLICITOR.CONSTITUENTID
        where 
            REVENUESPLIT.FINANCIALTRANSACTIONID = @REVENUEID 
            and REVENUESPLIT.DELETEDON is null 
            and REVENUESPLIT.TYPECODE != 1
            and REVENUESOLICITOR.AMOUNT <> (SOLICITORS.CREDITPERCENTAGE * REVENUESPLIT.BASEAMOUNT) / 100.0;
    end

    if @PLANNEDGIFTPROSPECTPLANID is not null
    begin
        -- Update solicitors from prospect plan if no opportunity is associated directly

        update dbo.REVENUESOLICITOR
        set AMOUNT = (SOLICITORS.CREDITPERCENTAGE * REVENUESPLIT.BASEAMOUNT) / 100.0,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CHANGEDATE,
            BASECURRENCYID = @BASECURRENCYID,
            ORGANIZATIONAMOUNT =
                case
                    when @BASECURRENCYID = @ORGANIZATIONCURRENCYID
                        then (SOLICITORS.CREDITPERCENTAGE * REVENUESPLIT.BASEAMOUNT) / 100.0
                    when @OVERRIDEORGANIZATIONVALUES = 1
                        then dbo.UFN_CURRENCY_CONVERT((SOLICITORS.CREDITPERCENTAGE * REVENUESPLIT.BASEAMOUNT) / 100.0, @ORGANIZATIONEXCHANGERATEIDFROMBASE)
                    else
                        (SOLICITORS.CREDITPERCENTAGE * REVENUESPLIT.ORGAMOUNT) / 100.0
                end,
            ORGANIZATIONEXCHANGERATEID =
                case
                    when @OVERRIDEORGANIZATIONVALUES = 1
                        then @ORGANIZATIONEXCHANGERATEIDFROMBASE
                    else
                        @ORGEXCHANGERATEID
                end    
        from dbo.REVENUESOLICITOR
            inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESOLICITOR.REVENUESPLITID = REVENUESPLIT.ID
            inner join dbo.UFN_PROSPECTPLAN_GETFUNDRAISERSANDCREDITS(@PLANNEDGIFTPROSPECTPLANID) as SOLICITORS on SOLICITORS.ID =  REVENUESOLICITOR.CONSTITUENTID
        where 
            REVENUESPLIT.FINANCIALTRANSACTIONID = @REVENUEID 
            and REVENUESPLIT.DELETEDON is null 
            and REVENUESPLIT.TYPECODE != 1
            and REVENUESOLICITOR.AMOUNT <> (SOLICITORS.CREDITPERCENTAGE * REVENUESPLIT.BASEAMOUNT) / 100.0;
    end