USP_REVENUETRIBUTETAXCLAIMAMOUNT_ADDUPDATEBYCONSTITUENT

            create procedure dbo.USP_REVENUETRIBUTETAXCLAIMAMOUNT_ADDUPDATEBYCONSTITUENT
            (
                @CONSTITUENTID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,
                @CURRENTDATE datetime
            )
            as
            begin
                if @CHANGEAGENTID is null
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

                if @CURRENTDATE is null
                    set @CURRENTDATE = getdate()

                declare @TRANSACTIONTOINCLUDE table
                (
                    ID uniqueidentifier,
                    TRIBUTETOTAL money,
                    TRANSACTIONTOTAL money,
                    REVENUETAXCLAIMAMOUNT money,
                    BASECURRENCYID uniqueidentifier,
                    ORGANIZATIONEXCHANGERATEID uniqueidentifier
                )

                insert into @TRANSACTIONTOINCLUDE
                (
                    ID,
                    TRIBUTETOTAL,
                    TRANSACTIONTOTAL,
                    REVENUETAXCLAIMAMOUNT,
                    BASECURRENCYID,
                    ORGANIZATIONEXCHANGERATEID
                )
                select
                    REVENUE.ID,
                    (    
                        select
                            SUM(AMOUNT)
                        from dbo.REVENUETRIBUTE
                        where REVENUEID = REVENUE.ID
                    ),
                    REVENUE.AMOUNT,
                    case
                        when REVENUE.TRANSACTIONTYPECODE = 1 then 
                            (
                                select 
                                    (sum(coalesce(TAXCLAIM.BASETAXCLAIMAMOUNT + TAXCLAIM.TRANSITIONALTAXCLAIMAMOUNT, 0)))
                                from dbo.REVENUESPLIT
                                cross apply dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGETAXCLAIMAMOUNT(REVENUESPLIT.ID) as TAXCLAIM
                                where REVENUESPLIT.REVENUEID = REVENUE.ID
                            )
                        else dbo.UFN_GIFTAIDREVENUESPLIT_GETTAXCLAIMAMOUNT(REVENUE.ID, 1)
                    end,
                    REVENUE.BASECURRENCYID,
                    REVENUE.ORGANIZATIONEXCHANGERATEID
                from dbo.REVENUE
                where
                    REVENUE.CONSTITUENTID = @CONSTITUENTID and
                    exists
                    (
                        select 1
                        from dbo.REVENUETRIBUTE
                        where REVENUEID = REVENUE.ID
                    )

                declare @REVENUETRIBUTE table
                (
                    ID uniqueidentifier,
                    REVENUEID uniqueidentifier,
                    AMOUNT money,
                    TAXCLAIMAMOUNT money
                )

                insert into @REVENUETRIBUTE
                (
                    ID,
                    REVENUEID,
                    AMOUNT,
                    TAXCLAIMAMOUNT
                )
                select
                    ID,
                    REVENUEID,
                    AMOUNT,
                    round(dbo.UFN_REVENUETRIBUTE_CALCULATEGROSSAMOUNT(TRIBUTEID, REVENUEID, AMOUNT),2) - AMOUNT
                from dbo.REVENUETRIBUTE
                where REVENUEID in
                (
                    select ID from @TRANSACTIONTOINCLUDE
                )


                -- Make sure all the transaction's tax claim amount is allocated if the tribute amounts equal the transaction amount

                update @REVENUETRIBUTE set
                    TAXCLAIMAMOUNT += TAXCLAIMDIFFERENCE
                from @REVENUETRIBUTE REVENUETRIBUTE
                inner join 
                (
                    select
                        ID,
                        (REVENUETAXCLAIMAMOUNT - (    select 
                                                        sum(TAXCLAIMAMOUNT) 
                                                    from @REVENUETRIBUTE 
                                                    where REVENUEID = TRANSACTIONTOINCLUDE.ID
                                                    )) as TAXCLAIMDIFFERENCE
                    from @TRANSACTIONTOINCLUDE as TRANSACTIONTOINCLUDE
                    where
                        TRIBUTETOTAL = TRANSACTIONTOTAL
                )
                TRANSACTIONTOINCLUDEWITHDIFFERENCE on REVENUETRIBUTE.REVENUEID = TRANSACTIONTOINCLUDEWITHDIFFERENCE.ID
                where
                    TAXCLAIMDIFFERENCE <> 0 and
                    REVENUETRIBUTE.ID = 
                    -- If the tax claim difference is positive, update the tribute with the least amount.  Otherwise, update

                    -- the tribute with the greatest amount.

                    case
                        when TAXCLAIMDIFFERENCE > 0 then
                        (
                            select top 1
                                ID
                            from @REVENUETRIBUTE
                            where
                                REVENUEID = TRANSACTIONTOINCLUDEWITHDIFFERENCE.ID
                            order by TAXCLAIMAMOUNT asc
                        )
                        else
                        (
                            select top 1
                                ID
                            from @REVENUETRIBUTE
                            where
                                REVENUEID = TRANSACTIONTOINCLUDEWITHDIFFERENCE.ID
                            order by TAXCLAIMAMOUNT desc
                        )
                    end


                update dbo.REVENUETRIBUTETAXCLAIMAMOUNT
                set 
                    TAXCLAIMAMOUNT = SOURCETRIBUTE.TAXCLAIMAMOUNT,
                    BASECURRENCYID = TRANSACTIONTOINCLUDE.BASECURRENCYID,
                    ORGANIZATIONTAXCLAIMAMOUNT = dbo.UFN_CURRENCY_CONVERT(SOURCETRIBUTE.TAXCLAIMAMOUNT, TRANSACTIONTOINCLUDE.ORGANIZATIONEXCHANGERATEID),
                    ORGANIZATIONEXCHANGERATEID = TRANSACTIONTOINCLUDE.ORGANIZATIONEXCHANGERATEID,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                from @REVENUETRIBUTE SOURCETRIBUTE
                inner join @TRANSACTIONTOINCLUDE TRANSACTIONTOINCLUDE on SOURCETRIBUTE.REVENUEID = TRANSACTIONTOINCLUDE.ID
                where 
                    REVENUETRIBUTETAXCLAIMAMOUNT.ID = SOURCETRIBUTE.ID and
                    exists(select 1 from dbo.REVENUETRIBUTETAXCLAIMAMOUNT where ID = SOURCETRIBUTE.ID) and
                    -- Avoid updating the table unnecessarily.  If the TAXCLAIMAMOUNT didn't change,

                    -- none of the other fields need to change.

                    REVENUETRIBUTETAXCLAIMAMOUNT.TAXCLAIMAMOUNT <> SOURCETRIBUTE.TAXCLAIMAMOUNT


                insert into dbo.REVENUETRIBUTETAXCLAIMAMOUNT
                (
                    ID,
                    TAXCLAIMAMOUNT,
                    BASECURRENCYID,
                    ORGANIZATIONTAXCLAIMAMOUNT,
                    ORGANIZATIONEXCHANGERATEID,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
                )
                select
                    SOURCETRIBUTE.ID,
                    SOURCETRIBUTE.TAXCLAIMAMOUNT,
                    TRANSACTIONTOINCLUDE.BASECURRENCYID,
                    dbo.UFN_CURRENCY_CONVERT(SOURCETRIBUTE.TAXCLAIMAMOUNT, TRANSACTIONTOINCLUDE.ORGANIZATIONEXCHANGERATEID),
                    TRANSACTIONTOINCLUDE.ORGANIZATIONEXCHANGERATEID,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from @REVENUETRIBUTE SOURCETRIBUTE
                inner join @TRANSACTIONTOINCLUDE TRANSACTIONTOINCLUDE on SOURCETRIBUTE.REVENUEID = TRANSACTIONTOINCLUDE.ID
                where 
                    not exists(select 1 from dbo.REVENUETRIBUTETAXCLAIMAMOUNT where ID = SOURCETRIBUTE.ID)
            end