USP_REVENUETRIBUTETAXCLAIMAMOUNT_ADDUPDATE

        CREATE procedure dbo.USP_REVENUETRIBUTETAXCLAIMAMOUNT_ADDUPDATE
        (
          @REVENUEID uniqueidentifier,
          @CHANGEAGENTID uniqueidentifier,
          @CURRENTDATE datetime
        )
        as
        begin
            declare @REVENUETRIBUTETOTAL money;
            declare @REVENUETRANSACTIONTYPECODE smallint;
            declare @REVENUETAXCLAIMAMOUNT money;
            declare @REVENUETOTAL money;
            declare @REVENUETRIBUTES table(ID uniqueidentifier, AMOUNT money, TAXCLAIMAMOUNT money);
            declare @TAXCLAIMDIFFERENCE money = 0;

            --get the total for all revenue tributes for the given revenue ID

            select @REVENUETRIBUTETOTAL = sum(AMOUNT)
            from dbo.REVENUETRIBUTE
            where REVENUEID = @REVENUEID;

            select @REVENUETOTAL = AMOUNT
            from dbo.REVENUE
            where ID = @REVENUEID;

            --get the transaction type code for the revenue record, which will decide how the tax claim amount for the revenue

            --  record will get calculated

            select @REVENUETRANSACTIONTYPECODE = TRANSACTIONTYPECODE
            from dbo.REVENUE
            where ID = @REVENUEID;

            --insert initial data into the temp table

            insert into @REVENUETRIBUTES
                select 
                    ID,
                    AMOUNT,
                    round(dbo.UFN_REVENUETRIBUTE_CALCULATEGROSSAMOUNT(TRIBUTEID, REVENUEID, AMOUNT),2) - AMOUNT as TAXCLAIMAMOUNT
                from
                    dbo.REVENUETRIBUTE
                where
                    REVENUEID = @REVENUEID;

            if @REVENUETRANSACTIONTYPECODE = 1
            begin
                select @REVENUETAXCLAIMAMOUNT = (sum(coalesce(TAXCLAIM.BASETAXCLAIMAMOUNT + TAXCLAIM.TRANSITIONALTAXCLAIMAMOUNT, 0)))
                from dbo.REVENUESPLIT
                    cross apply dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGETAXCLAIMAMOUNT(REVENUESPLIT.ID) as TAXCLAIM
                where REVENUESPLIT.REVENUEID = @REVENUEID
            end
            else
            begin
                set @REVENUETAXCLAIMAMOUNT = dbo.UFN_GIFTAIDREVENUESPLIT_GETTAXCLAIMAMOUNT(@REVENUEID, 1);
            end

            --If the totals are the same, check if there is any difference in the tax claim amounts.

            if @REVENUETRIBUTETOTAL = @REVENUETOTAL
            begin
                select @TAXCLAIMDIFFERENCE = @REVENUETAXCLAIMAMOUNT - sum(TAXCLAIMAMOUNT)
                from @REVENUETRIBUTES;
            end

            --If the difference is greater than 0, then add the difference to the tribute with the

            --    least amount of Gift Aid. If it is less than 0, then remove the difference from the

            --    tribute with the most Gift Aid. If there is no difference, do nothing.

            if @TAXCLAIMDIFFERENCE > 0
            begin
                declare @LEAST uniqueidentifier;
                select top 1 @LEAST = ID
                from @REVENUETRIBUTES
                order by TAXCLAIMAMOUNT asc;

                update @REVENUETRIBUTES
                set TAXCLAIMAMOUNT += @TAXCLAIMDIFFERENCE
                where ID = @LEAST;
            end
            else 
            begin
                if @TAXCLAIMDIFFERENCE < 0
                begin
                    declare @MOST uniqueidentifier;
                    select top 1 @MOST = ID
                    from @REVENUETRIBUTES
                    order by TAXCLAIMAMOUNT desc;

                    update @REVENUETRIBUTES
                    set TAXCLAIMAMOUNT += @TAXCLAIMDIFFERENCE
                    where ID = @MOST;
                end
            end

            declare @BASECURRENCYID uniqueidentifier;
            declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

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

            declare REVENUETRIBUTEGIFTAIDCURSOR cursor local fast_forward
            for select ID, TAXCLAIMAMOUNT
            from @REVENUETRIBUTES;

            open REVENUETRIBUTEGIFTAIDCURSOR;

            declare @ID uniqueidentifier;
            declare @TAXCLAIMAMOUNT money;

            fetch next from REVENUETRIBUTEGIFTAIDCURSOR into @ID, @TAXCLAIMAMOUNT;

            while @@FETCH_STATUS = 0
            begin
                if exists(select 1 from dbo.REVENUETRIBUTETAXCLAIMAMOUNT where ID = @ID)
                begin
                    update dbo.REVENUETRIBUTETAXCLAIMAMOUNT
                    set 
                        TAXCLAIMAMOUNT = @TAXCLAIMAMOUNT,
                        BASECURRENCYID = @BASECURRENCYID,
                        ORGANIZATIONTAXCLAIMAMOUNT = dbo.UFN_CURRENCY_CONVERT(@TAXCLAIMAMOUNT, @ORGANIZATIONEXCHANGERATEID),
                        ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    where ID = @ID;
                end
                else
                begin
                    insert into dbo.REVENUETRIBUTETAXCLAIMAMOUNT (ID, TAXCLAIMAMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    values (@ID, @TAXCLAIMAMOUNT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
                end

                fetch next from REVENUETRIBUTEGIFTAIDCURSOR into @ID, @TAXCLAIMAMOUNT;
            end

            close REVENUETRIBUTEGIFTAIDCURSOR;
            deallocate REVENUETRIBUTEGIFTAIDCURSOR;
        end