USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLIT

            CREATE procedure dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLIT
            (
                @REVENUEID uniqueidentifier,
                @TRANSACTIONTYPECODE tinyint,
                @CONSTITUENTID uniqueidentifier,
                @DATE datetime,
                @SPLITID uniqueidentifier,
                @DESIGNATIONID uniqueidentifier,
                @AMOUNT money,
                @DECLINESGIFTAID bit,
                @APPLICATIONCODE tinyint,
                @TYPECODE tinyint,
                @DISQUALIFIEDBYATTRIBUTES bit,
                @CHANGEAGENTID uniqueidentifier,
                @ISCOVENANT bit = null,
                @ISSPONSORSHIP bit = null,
                @TRANSACTIONAMOUNT money = 0, 
                @BASECURRENCYID uniqueidentifier = null output,
                @TRANSACTIONCURRENCYID uniqueidentifier = null output,
                @BASEEXCHANGERATEID uniqueidentifier = null output,
                @ORGANIZATIONEXCHANGERATEID uniqueidentifier = null output,
                @TAXCLAIMNUMBER nvarchar(10) = null,
                @CHARITYCLAIMREFERENCENUMBER nvarchar(20) = null,
                @BASERATE numeric(30,6) = null,
                @TRANSITIONALRATE numeric(30,6) = null
            )
            as
                set nocount on

                -- Don't recalculate the values if the split has already been submitted

                --If @TAXCLAIMNUMBER is set that means its a split that has changed but the total amount going towards a CCRN is the same

                --so in this case we want to update the values

                if exists (select 1 from dbo.REVENUESPLITGIFTAID where ID = @SPLITID and TAXCLAIMNUMBER <> '') and @TAXCLAIMNUMBER is null and @DECLINESGIFTAID=0
                    return

                declare @DATECHANGED datetime = getdate();
                declare @DISQVAR bit;

                set @DISQVAR = dbo.UFN_GIFTAIDSTATUS_GETBASEDONRULES(@SPLITID);

                declare @REGISTRANTID uniqueidentifier
                select 
                    @REGISTRANTID = REGISTRANTID 
                from dbo.EVENTREGISTRANTPAYMENT 
                where PAYMENTID = @SPLITID;

                declare @MEMBERSHIPID uniqueidentifier
                select 
                    @MEMBERSHIPID = MEMBERSHIPPROGRAMID 
                from dbo.MEMBERSHIP 
                where ID = (
                    select MEMBERSHIPID 
                    from dbo.MEMBERSHIPTRANSACTION 
                    where REVENUESPLITID = @SPLITID);

                declare @PDACCOUNTSYSTEMID uniqueidentifier;

                select 
                    @PDACCOUNTSYSTEMID = FINANCIALTRANSACTION.PDACCOUNTSYSTEMID
                from dbo.FINANCIALTRANSACTIONLINEITEM  
                inner join dbo.FINANCIALTRANSACTION  on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                where FINANCIALTRANSACTIONLINEITEM.ID = @SPLITID;

                --get eligibility

                declare @ELIGIBILITY tinyint;

                --sponsorship is always eligible

                if @ISSPONSORSHIP = 1
                begin
                  set @ELIGIBILITY = 2;
                end
                else
                begin
                    set @ELIGIBILITY = dbo.UFN_VALIDDECLARATION(@DATE, @CONSTITUENTID, @DESIGNATIONID, @REGISTRANTID, @MEMBERSHIPID);
                end

                declare @DECIMALDIGITS tinyint;
                declare @ROUNDINGTYPECODE tinyint;

                set @DECIMALDIGITS = 2;
                set @ROUNDINGTYPECODE = 0;

                select
                    @DECIMALDIGITS = DECIMALDIGITS, 
                    @ROUNDINGTYPECODE = ROUNDINGTYPECODE
                from dbo.UFN_CURRENCY_GETPROPERTIES(@TRANSACTIONCURRENCYID);

                declare @BASETAXCLAIMAMOUNT money = 0;
                declare @TRANSITIONALTAXCLAIMAMOUNT money = 0;
                declare @TRANSACTIONBASETAXCLAIMAMOUNT money = 0;
                declare @TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT money = 0;
                declare @ORGANIZATIONBASETAXCLAIMAMOUNT money = 0;
                declare @ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT money = 0;

                --if qualified and not 'Not eligible' or a covenant gift or a gift aid sponsorship, calculate tax claim amount

                if @DISQVAR = 1 and @DECLINESGIFTAID = 0 and @ELIGIBILITY != 1 or @ISCOVENANT = 1 or @ISSPONSORSHIP = 1
                    begin
                        if @TRANSACTIONTYPECODE = 0 or @TRANSACTIONTYPECODE = 2
                            begin
                                declare @RATE numeric(30,6);

                                if @BASERATE is null
                                    set @BASERATE = dbo.UFN_GETGIFTAIDBASETAXRATE(@DATE);

                                if @TRANSITIONALRATE is null
                                    set @TRANSITIONALRATE = dbo.UFN_GETGIFTAIDTRANSITIONALTAXRATE(@DATE);

                                set @RATE = @BASERATE + @TRANSITIONALRATE;

                                declare @TRANSACTIONTAXCLAIMAMOUNT money;
                                set @TRANSACTIONTAXCLAIMAMOUNT = round(coalesce(@TRANSACTIONAMOUNT * (@RATE / (100 - @RATE)), 0), @DECIMALDIGITS);
                                set @TRANSACTIONBASETAXCLAIMAMOUNT = round(coalesce(@TRANSACTIONAMOUNT * (@BASERATE / (100 - @BASERATE)), 0), @DECIMALDIGITS);
                                set @TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT = @TRANSACTIONTAXCLAIMAMOUNT - @TRANSACTIONBASETAXCLAIMAMOUNT;
                            end
                    end

                --pledge has its own gross amount calculation, so it has to be a special case

                if @TRANSACTIONTYPECODE = 1 and (@DECLINESGIFTAID = 0 and @DISQVAR = 1 or @ISCOVENANT = 1)
                    begin
                        select
                            @TRANSACTIONBASETAXCLAIMAMOUNT = round(coalesce(sum(TRANSACTIONAMOUNT * (BASERATE / (100 - BASERATE))), 0), @DECIMALDIGITS),
                            @TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT = round(coalesce(sum(TRANSACTIONAMOUNT * ((BASERATE + TRANSITIONALRATE) / (100 - (BASERATE + TRANSITIONALRATE)))) - sum(TRANSACTIONAMOUNT * (BASERATE / (100 - BASERATE))), 0), @DECIMALDIGITS)
                        from 
                        (
                            select 
                                INSTALLMENTSPLIT.TRANSACTIONAMOUNT,
                                dbo.UFN_GETGIFTAIDBASETAXRATE(INSTALLMENT.[DATE]) as BASERATE, 
                                dbo.UFN_GETGIFTAIDTRANSITIONALTAXRATE(INSTALLMENT.[DATE]) as TRANSITIONALRATE, 
                                case when @ISCOVENANT = 1 then 2 else dbo.UFN_VALIDDECLARATION(INSTALLMENT.[DATE], @CONSTITUENTID, @DESIGNATIONID, @REGISTRANTID, @MEMBERSHIPID) end as ELIGIBILITYCODE
                            from dbo.INSTALLMENTSPLIT
                            inner join dbo.INSTALLMENT on INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
                            where
                                INSTALLMENTSPLIT.REVENUESPLITID = @SPLITID
                        ) as INSTALLMENTSPLITS
                        where ELIGIBILITYCODE = 2
                    end

                --Moved rounding into the calculation to avoid rounding errors

                --Round transaction tax claim amounts

                --set @TRANSACTIONBASETAXCLAIMAMOUNT = round(coalesce(@TRANSACTIONBASETAXCLAIMAMOUNT, 0), @DECIMALDIGITS);                                                            

                --set @TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT = round(coalesce(@TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT, 0), @DECIMALDIGITS);                 



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

                if @BASECURRENCYID is null
                    set @BASECURRENCYID = @ORGANIZATIONCURRENCYID;

                --Calculate transaction tax claim amounts

                exec dbo.USP_CURRENCY_GETCURRENCYVALUES @TRANSACTIONBASETAXCLAIMAMOUNT,
                                                        @DATE,
                                                        @BASECURRENCYID,
                                                        @BASEEXCHANGERATEID,
                                                        @TRANSACTIONCURRENCYID,
                                                        @BASETAXCLAIMAMOUNT output,
                                                        @ORGANIZATIONCURRENCYID,
                                                        @ORGANIZATIONBASETAXCLAIMAMOUNT output, 
                                                        @ORGANIZATIONEXCHANGERATEID, 
                                                        0;

                exec dbo.USP_CURRENCY_GETCURRENCYVALUES @TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT,
                                                        @DATE,
                                                        @BASECURRENCYID,
                                                        @BASEEXCHANGERATEID,
                                                        @TRANSACTIONCURRENCYID,
                                                        @TRANSITIONALTAXCLAIMAMOUNT output,
                                                        @ORGANIZATIONCURRENCYID,
                                                        @ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT output, 
                                                        @ORGANIZATIONEXCHANGERATEID, 
                                                        0;                                    


                    declare @SHOULDUPDATESPLIT bit = 0;
                    declare @DIDADDSPLIT bit = 0;
                --if the split already exists, then do an update instead of an add

                if @SPLITID in (select ID from dbo.REVENUESPLITGIFTAID)
                begin

                    declare @OLDBASETAXCLAIMAMOUNT money;
                    declare @OLDTRANSACTIONBASETAXCLAIMAMOUNT money;

                    select @OLDBASETAXCLAIMAMOUNT = BASETAXCLAIMAMOUNT,
                                 @OLDTRANSACTIONBASETAXCLAIMAMOUNT = TRANSACTIONBASETAXCLAIMAMOUNT
                    from dbo.REVENUESPLITGIFTAID
                    where ID = @SPLITID;

                    if @BASETAXCLAIMAMOUNT <> @OLDBASETAXCLAIMAMOUNT OR @TRANSACTIONBASETAXCLAIMAMOUNT <> @OLDTRANSACTIONBASETAXCLAIMAMOUNT
                        set @SHOULDUPDATESPLIT = 1;

                        update dbo.REVENUESPLITGIFTAID
                        set BASETAXCLAIMAMOUNT = @BASETAXCLAIMAMOUNT,
                            TRANSITIONALTAXCLAIMAMOUNT = @TRANSITIONALTAXCLAIMAMOUNT,
                            TRANSACTIONBASETAXCLAIMAMOUNT = @TRANSACTIONBASETAXCLAIMAMOUNT,
                            TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT = @TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT,
                            ORGANIZATIONBASETAXCLAIMAMOUNT = @ORGANIZATIONBASETAXCLAIMAMOUNT,
                            ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT = @ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT,
                            RULES_STATUS = @DISQVAR,
                            DECLINESGIFTAID = @DECLINESGIFTAID,
                            CHANGEDBYID = @CHANGEAGENTID,
                            -- If @ISCOVENANT isn't set, default to its current value

                            ISCOVENANT = case when @ISCOVENANT is null then ISCOVENANT else @ISCOVENANT end,
                            ISSPONSORSHIP = @ISSPONSORSHIP,
                            BASECURRENCYID = @BASECURRENCYID, 
                            TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID, 
                            BASEEXCHANGERATEID = @BASEEXCHANGERATEID, 
                            ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID, 
                            BASERATE = @BASERATE,
                            TRANSITIONALRATE = @TRANSITIONALRATE,
                            DATECHANGED = @DATECHANGED
                        where ID = @SPLITID;
                end
                else
                begin
                    if @TAXCLAIMNUMBER is null
                    begin
                        insert into dbo.REVENUESPLITGIFTAID(ID, BASETAXCLAIMAMOUNT, TRANSITIONALTAXCLAIMAMOUNT, TRANSACTIONBASETAXCLAIMAMOUNT, TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT, ORGANIZATIONBASETAXCLAIMAMOUNT, ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT, RULES_STATUS, ATTRIBUTES_STATUS, DECLINESGIFTAID, ISCOVENANT, ISSPONSORSHIP, BASECURRENCYID, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID, BASERATE, TRANSITIONALRATE,ADDEDBYID, CHANGEDBYID)
                        values(@SPLITID, @BASETAXCLAIMAMOUNT, @TRANSITIONALTAXCLAIMAMOUNT, @TRANSACTIONBASETAXCLAIMAMOUNT, @TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT, @ORGANIZATIONBASETAXCLAIMAMOUNT, @ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT, @DISQVAR, @DISQUALIFIEDBYATTRIBUTES, @DECLINESGIFTAID, coalesce(@ISCOVENANT, 0), @ISSPONSORSHIP, @BASECURRENCYID, @TRANSACTIONCURRENCYID, @BASEEXCHANGERATEID, @ORGANIZATIONEXCHANGERATEID, @BASERATE, @TRANSITIONALRATE, @CHANGEAGENTID, @CHANGEAGENTID);
                    end
                    else
                    begin
                        insert into dbo.REVENUESPLITGIFTAID(ID, BASETAXCLAIMAMOUNT, TRANSITIONALTAXCLAIMAMOUNT, TRANSACTIONBASETAXCLAIMAMOUNT, TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT, ORGANIZATIONBASETAXCLAIMAMOUNT, ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT, RULES_STATUS, ATTRIBUTES_STATUS, DECLINESGIFTAID, ISCOVENANT, ISSPONSORSHIP, BASECURRENCYID, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID, BASERATE, TRANSITIONALRATE,TAXCLAIMNUMBER,CHARITYCLAIMREFERENCENUMBER, ADDEDBYID, CHANGEDBYID)
                        values(@SPLITID, @BASETAXCLAIMAMOUNT, @TRANSITIONALTAXCLAIMAMOUNT, @TRANSACTIONBASETAXCLAIMAMOUNT, @TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT, @ORGANIZATIONBASETAXCLAIMAMOUNT, @ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT, @DISQVAR, @DISQUALIFIEDBYATTRIBUTES, @DECLINESGIFTAID, coalesce(@ISCOVENANT, 0), @ISSPONSORSHIP, @BASECURRENCYID, @TRANSACTIONCURRENCYID, @BASEEXCHANGERATEID, @ORGANIZATIONEXCHANGERATEID, @BASERATE, @TRANSITIONALRATE,@TAXCLAIMNUMBER,@CHARITYCLAIMREFERENCENUMBER, @CHANGEAGENTID, @CHANGEAGENTID);
                    end
                    set @DIDADDSPLIT = 1
                end

                    --If a TAXCLAIMNUMBER exists, we are either modifying existing splits with claimed gift aid that need to be modified

          --GL code was taken from USP_R68_SAVEGIFTAIDGLDISTRIBUTIONS, if USP_R68_SAVEGIFTAIDGLDISTRIBUTIONS changes this should be updated.

                    if @TAXCLAIMNUMBER is not null and @SHOULDUPDATESPLIT = 1
                    begin

                        if exists(
                            select
                                1
                            from
                                dbo.FINANCIALTRANSACTIONLINEITEM GA
                                inner join dbo.FINANCIALTRANSACTIONLINEITEM SOURCE on SOURCE.ID = GA.SOURCELINEITEMID
                                left join dbo.FINANCIALTRANSACTIONLINEITEM NEWSOURCE on NEWSOURCE.ID = SOURCE.REVERSEDLINEITEMID
                            where
                                @SPLITID = isnull(NEWSOURCE.ID, SOURCE.ID)
                                and GA.TYPECODE = 8
                                and GA.POSTSTATUSCODE = 2
                        )
                        begin
                            declare @PAYMENTADJUSTMENTID uniqueidentifier;
                            declare @ADJUSTMENTPOSTDATE date;
                            declare @ADJUSTMENTPOSTSTATUS tinyint = 1;

                            select top 1
                                @PAYMENTADJUSTMENTID = LIA.ID,
                                @ADJUSTMENTPOSTDATE = coalesce(A.POSTDATE, LIA.DATE, LI.POSTDATE),
                                @ADJUSTMENTPOSTSTATUS = case when A.POSTSTATUSCODE = 2 then 3 else 1 end
                            from
                                dbo.FINANCIALTRANSACTIONLINEITEM LI
                                left join dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT LIA on LIA.ID = LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
                                left join dbo.ADJUSTMENT A on A.REVENUEID = LI.FINANCIALTRANSACTIONID
                            where
                                LI.ID = @SPLITID
                                and LIA.ADJUSTMENTREASONCODEID is not null
                                and not exists (select 1 from dbo.FINANCIALTRANSACTIONLINEITEM GA where GA.SOURCELINEITEMID = LI.ID 
                                    and GA.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = LIA.ID
                                    and GA.TYPECODE = 8)
                        order by A.DATEADDED DESC;

                            if dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID) = 1
                                exec dbo.USP_SAVE_GIFTAID_ADJUSTMENT @SPLITID, @ADJUSTMENTPOSTSTATUS, @ADJUSTMENTPOSTDATE, null, @PAYMENTADJUSTMENTID, @CHANGEAGENTID, null;
                         end
                        else
                        begin
                            delete from
                                dbo.FINANCIALTRANSACTIONLINEITEM
                            where
                                ID in (
                                    select
                                        GA.ID
                                    from
                                        dbo.FINANCIALTRANSACTIONLINEITEM GA
                                        inner join dbo.FINANCIALTRANSACTIONLINEITEM SOURCE on SOURCE.ID = GA.SOURCELINEITEMID
                                        left join dbo.FINANCIALTRANSACTIONLINEITEM NEWSOURCE on NEWSOURCE.ID = SOURCE.REVERSEDLINEITEMID
                                    where
                                        @SPLITID = isnull(NEWSOURCE.ID, SOURCE.ID)
                                        and GA.TYPECODE = 8
                                        and GA.POSTSTATUSCODE = 1
                                        and GA.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID is null
                                        and (GA.ID <> SOURCE.REVERSEDLINEITEMID  or SOURCE.REVERSEDLINEITEMID is null)
                                        and (GA.ID <> NEWSOURCE.REVERSEDLINEITEMID or  NEWSOURCE.REVERSEDLINEITEMID is null)
                                        and GA.DELETEDON is null)
                        end
                end

                if @TAXCLAIMNUMBER is not null and ( (@SHOULDUPDATESPLIT = 1 ) or (@DIDADDSPLIT = 1) )
                    if dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID) = 1 
                        exec dbo.USP_SAVE_GIFTAIDGLDISTRIBUTION @SPLITID, @CHANGEAGENTID;