UFN_AUCTIONITEM_GETGAINLOSS

            CREATE function dbo.UFN_AUCTIONITEM_GETGAINLOSS
            (
                @AUCTIONITEMID uniqueidentifier
            )
            returns @PAYMENTS table
            (
                AUCTIONITEMID uniqueidentifier,
                REVENUEID uniqueidentifier,
                REVENUESPLITID uniqueidentifier,
                REVENUESPLITAMOUNT money,
                AUCTIONITEMGAINLOSSAMOUNT money,
                TRANSACTIONCURRENCYID uniqueidentifier,
                TRANSACTIONCURRENCYDECIMALDIGITS int
            )
            as 
                begin
                    --NOTE: This function is UFN_AUCTIONITEM_GETRECEIPTAMOUNTS with the condition that strips away losses removed. It's also

                    --had the various variables renamed from RECEIPTAMOUNT to GAINLOSSAMOUNT.


                    declare @TYPECODE tinyint;
                    select @TYPECODE = AUCTIONITEM.TYPECODE from dbo.AUCTIONITEM where AUCTIONITEM.ID = @AUCTIONITEMID

                    declare @ITEMFMV money;
                    select 
                        @ITEMFMV = ITEMVALUES.TRANSACTIONVALUE
                    from 
                        dbo.UFN_AUCTIONITEM_GETVALUES_INCURRENCY(@AUCTIONITEMID) ITEMVALUES

                    if @TYPECODE = 0
                    begin
                        insert into @PAYMENTS
                        (
                            AUCTIONITEMID,
                            REVENUEID,
                            REVENUESPLITID,
                            REVENUESPLITAMOUNT,
                            TRANSACTIONCURRENCYID,
                            TRANSACTIONCURRENCYDECIMALDIGITS
                        )
                            select
                                AUCTIONITEMPURCHASE.AUCTIONITEMID,
                                FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,
                                FINANCIALTRANSACTIONLINEITEM.ID,
                                FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT,
                                FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                                CURRENCY.DECIMALDIGITS
                            from
                                dbo.AUCTIONITEMPURCHASE
                                left join dbo.FINANCIALTRANSACTIONLINEITEM on AUCTIONITEMPURCHASE.PURCHASEID = FINANCIALTRANSACTIONLINEITEM.ID
                                left join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                                left join dbo.CURRENCY on FINANCIALTRANSACTION.TRANSACTIONCURRENCYID = CURRENCY.ID
                            where
                                FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                                and AUCTIONITEMPURCHASE.AUCTIONITEMID = @AUCTIONITEMID
                    end
                    else
                    begin

                        insert into @PAYMENTS
                        (
                            AUCTIONITEMID,
                            REVENUEID,
                            REVENUESPLITID,
                            REVENUESPLITAMOUNT,
                            TRANSACTIONCURRENCYID,
                            TRANSACTIONCURRENCYDECIMALDIGITS
                        )
                            select
                                AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID,
                                FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,
                                FINANCIALTRANSACTIONLINEITEM.ID,
                                FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT,
                                FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                                CURRENCY.DECIMALDIGITS
                            from
                                dbo.AUCTIONITEMREVENUEPURCHASE
                                left join dbo.FINANCIALTRANSACTIONLINEITEM on AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                                left join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                                left join dbo.CURRENCY on FINANCIALTRANSACTION.TRANSACTIONCURRENCYID = CURRENCY.ID
                            where
                                FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                                and AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID = @AUCTIONITEMID

                    end

                    declare @PAYMENTCOUNT int;
                    select @PAYMENTCOUNT = count(REVENUESPLITID) from @PAYMENTS

                    declare @PURCHASEPRICE money;
                    select @PURCHASEPRICE = sum(REVENUESPLITAMOUNT) from @PAYMENTS

                    if @PAYMENTCOUNT = 1
                    begin
                        update @PAYMENTS set AUCTIONITEMGAINLOSSAMOUNT = (@PURCHASEPRICE - @ITEMFMV)
                    end
                    else
                    begin

                        declare @GAINLOSSAMOUNT_TO_DISTRIBUTE money = 0;
                        declare @DISTRIBUTED_GAINLOSS_AMOUNT money = 0;
                        declare @TOTALDISTRIBUTED_GAINLOSS_AMOUNT money = 0;

                        select @GAINLOSSAMOUNT_TO_DISTRIBUTE = abs(@PURCHASEPRICE - @ITEMFMV);
                        declare @CURRENTPAYMENTCOUNT int = 0;

                        declare @PAYMENTCURSOR_REVENUEID uniqueidentifier,@PAYMENTCURSOR_REVENUESPLITID uniqueidentifier;
                        declare @PAYMENTCURSOR_REVENUESPLITAMOUNT money,@PAYMENTCURSOR_TRANSACTIONCURRENCYDECIMALDIGITS int;
                        declare PAYMENTCURSOR cursor local fast_forward
                            for select PAYMENTS.REVENUEID,PAYMENTS.REVENUESPLITID,PAYMENTS.REVENUESPLITAMOUNT,PAYMENTS.TRANSACTIONCURRENCYDECIMALDIGITS
                                from @PAYMENTS PAYMENTS

                        open PAYMENTCURSOR
                        fetch next from PAYMENTCURSOR into @PAYMENTCURSOR_REVENUEID,@PAYMENTCURSOR_REVENUESPLITID,@PAYMENTCURSOR_REVENUESPLITAMOUNT,@PAYMENTCURSOR_TRANSACTIONCURRENCYDECIMALDIGITS

                        while @@FETCH_STATUS = 0
                        begin

                            select @CURRENTPAYMENTCOUNT = @CURRENTPAYMENTCOUNT + 1;

                            if @CURRENTPAYMENTCOUNT <> @PAYMENTCOUNT
                            begin

                                select @DISTRIBUTED_GAINLOSS_AMOUNT = dbo.UFN_CURRENCY_CONVERTBYPROPORTION(@PAYMENTCURSOR_REVENUESPLITAMOUNT,@PURCHASEPRICE,@GAINLOSSAMOUNT_TO_DISTRIBUTE,@PAYMENTCURSOR_TRANSACTIONCURRENCYDECIMALDIGITS)

                                update 
                                    @PAYMENTS
                                set 
                                    AUCTIONITEMGAINLOSSAMOUNT = case 
                                                                    when @PURCHASEPRICE - @ITEMFMV > 0 then
                                                                        @DISTRIBUTED_GAINLOSS_AMOUNT
                                                                    else
                                                                        -(@DISTRIBUTED_GAINLOSS_AMOUNT)
                                                                end
                                where 
                                    REVENUEID = @PAYMENTCURSOR_REVENUEID 
                                    and REVENUESPLITID = @PAYMENTCURSOR_REVENUESPLITID

                                set @TOTALDISTRIBUTED_GAINLOSS_AMOUNT = @TOTALDISTRIBUTED_GAINLOSS_AMOUNT + @DISTRIBUTED_GAINLOSS_AMOUNT;
                            end
                            else
                            begin
                                --The last payment towards an item deals with the rounding issues

                                --It just gets what is left to distribute

                                update 
                                    @PAYMENTS
                                set 
                                    AUCTIONITEMGAINLOSSAMOUNT = case 
                                                                    when @PURCHASEPRICE - @ITEMFMV > 0 then
                                                                        (@GAINLOSSAMOUNT_TO_DISTRIBUTE    - @TOTALDISTRIBUTED_GAINLOSS_AMOUNT)
                                                                    else
                                                                        -(@GAINLOSSAMOUNT_TO_DISTRIBUTE    - @TOTALDISTRIBUTED_GAINLOSS_AMOUNT)
                                                                end
                                where 
                                    REVENUEID = @PAYMENTCURSOR_REVENUEID 
                                    and REVENUESPLITID = @PAYMENTCURSOR_REVENUESPLITID
                            end

                            fetch next from PAYMENTCURSOR into @PAYMENTCURSOR_REVENUEID,@PAYMENTCURSOR_REVENUESPLITID,@PAYMENTCURSOR_REVENUESPLITAMOUNT,@PAYMENTCURSOR_TRANSACTIONCURRENCYDECIMALDIGITS
                        end
                        close PAYMENTCURSOR
                        deallocate PAYMENTCURSOR
                    end

                    return;
                end