USP_AMPROIMPORT_UPDATEPURCHASERECEIPTAMOUNTS

            CREATE procedure dbo.USP_AMPROIMPORT_UPDATEPURCHASERECEIPTAMOUNTS
            (
                @PURCHASEREVENUEID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,
                @CHANGEDATE datetime
            )
            as
            set nocount on;

            declare @AFFECTEDREVENUE table
            (
                REVENUEID uniqueidentifier,
                NEWRECEIPTAMOUNT money
            );

            --Get a list of all revenue records which bought auction items which were also bought by @PURCHASEREVENUEID. Naturally,

            --@PURCHASEREVENUEID should also be included.

            insert into @AFFECTEDREVENUE(REVENUEID, NEWRECEIPTAMOUNT)
                select @PURCHASEREVENUEID, 0
                union
                select distinct [OTHERPURCHASE].REVENUEPURCHASEID, 0
                from dbo.AUCTIONITEMREVENUEPURCHASE [OTHERPURCHASE]
                inner join dbo.AUCTIONITEMREVENUEPURCHASE [PURCHASE] on [PURCHASE].AUCTIONITEMID = [OTHERPURCHASE].AUCTIONITEMID
                where [PURCHASE].REVENUEPURCHASEID = @PURCHASEREVENUEID;

            declare @REVENUEID uniqueidentifier;
            declare @NEWRECEIPTAMOUNT money = 0;
            declare @TEMPRECEIPTAMOUNT money = 0;

            declare AFFECTED_REVENUE cursor local fast_forward for
                select REVENUEID from @AFFECTEDREVENUE;

            open AFFECTED_REVENUE;
            fetch next from AFFECTED_REVENUE into @REVENUEID;
            while @@FETCH_STATUS = 0 
            begin
                set @NEWRECEIPTAMOUNT = 0;
                set @TEMPRECEIPTAMOUNT = 0;

                --Assume that AMPro donations don't have benefits, so the full donation amount for any donations contribute to the receipt amount

                select 
                    @TEMPRECEIPTAMOUNT = coalesce(sum(FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT), 0)
                from 
                    dbo.FINANCIALTRANSACTIONLINEITEM 
                    inner join dbo.REVENUESPLIT_EXT ON dbo.FINANCIALTRANSACTIONLINEITEM.ID = dbo.REVENUESPLIT_EXT.ID
                where 
                    FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID 
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                    and REVENUESPLIT_EXT.TYPECODE = 0
                    and REVENUESPLIT_EXT.APPLICATIONCODE = 0;

                select @NEWRECEIPTAMOUNT = coalesce(@TEMPRECEIPTAMOUNT, 0);
                set @TEMPRECEIPTAMOUNT = 0;

                --Take benefits and event cost into account when computing the receipt amount for event registrations

                select 
                    @TEMPRECEIPTAMOUNT = coalesce(
                        case when REGISTRANT.BENEFITSWAIVED = 1 then FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT  - coalesce(EVENTPRICE.COST, 0)
                             else FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT 
                                    - coalesce(SUM(REGISTRANTBENEFIT.EVENTBASECURRENCYTOTALVALUE), 0) 
                                    - coalesce(EVENTPRICE.COST, 0)
                        end, 0)
                from 
                    dbo.REGISTRANT 
                    inner join dbo.REGISTRANTREGISTRATION on REGISTRANTREGISTRATION.REGISTRANTID = REGISTRANT.ID 
                    inner join dbo.EVENTPRICE on EVENTPRICE.ID = REGISTRANTREGISTRATION.EVENTPRICEID 
                    inner join dbo.EVENTREGISTRANTPAYMENT on EVENTREGISTRANTPAYMENT.REGISTRANTID = REGISTRANT.ID 
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = EVENTREGISTRANTPAYMENT.PAYMENTID 
                    left join dbo.REGISTRANTBENEFIT on REGISTRANTBENEFIT.REGISTRANTID = REGISTRANT.ID 
                where 
                    FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID 
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                group by 
                    FINANCIALTRANSACTIONLINEITEM.ID, FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT, REGISTRANT.BENEFITSWAIVED, EVENTPRICE.COST 

                set @TEMPRECEIPTAMOUNT = coalesce(@TEMPRECEIPTAMOUNT, 0);
                select @NEWRECEIPTAMOUNT = @NEWRECEIPTAMOUNT + case when @TEMPRECEIPTAMOUNT < 0 then 0 else @TEMPRECEIPTAMOUNT end;
                set @TEMPRECEIPTAMOUNT = 0;

                --UFN_AUCTIONITEM_GETRECEIPTAMOUNTS will pro-rate the gain and loss for an item across all of its buyers. It will return 

                --0 for losses (instead of a negative receipt amount) 

                select 
                    @TEMPRECEIPTAMOUNT = sum(coalesce([RECEIPT].AUCTIONITEMRECEIPTAMOUNT, 0))
                from 
                    dbo.FINANCIALTRANSACTIONLINEITEM 
                    inner join dbo.AUCTIONITEMPURCHASE on AUCTIONITEMPURCHASE.PURCHASEID = FINANCIALTRANSACTIONLINEITEM.ID
                    cross apply dbo.UFN_AUCTIONITEM_GETRECEIPTAMOUNTS(AUCTIONITEMPURCHASE.AUCTIONITEMID) as [RECEIPT]
                where 
                    FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                    and [RECEIPT].REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID;

                set @TEMPRECEIPTAMOUNT = coalesce(@TEMPRECEIPTAMOUNT, 0);
                set @NEWRECEIPTAMOUNT = @NEWRECEIPTAMOUNT + @TEMPRECEIPTAMOUNT;
                set @TEMPRECEIPTAMOUNT = 0;

                select @NEWRECEIPTAMOUNT = case when @NEWRECEIPTAMOUNT < 0 then 0 else @NEWRECEIPTAMOUNT end;

                update @AFFECTEDREVENUE 
                    set NEWRECEIPTAMOUNT = @NEWRECEIPTAMOUNT
                where REVENUEID = @REVENUEID;

                fetch next from AFFECTED_REVENUE into @REVENUEID;    
            end
            close AFFECTED_REVENUE;
            deallocate AFFECTED_REVENUE;

      merge into dbo.REVENUE_EXT
      using  @AFFECTEDREVENUE [AFFECTEDREVENUE] on [AFFECTEDREVENUE].REVENUEID = REVENUE_EXT.ID
      when matched 
          then update
            set 
                REVENUE_EXT.RECEIPTAMOUNT = [AFFECTEDREVENUE].NEWRECEIPTAMOUNT,
                REVENUE_EXT.CHANGEDBYID = @CHANGEAGENTID,
                REVENUE_EXT.DATECHANGED = @CHANGEDATE;