UFN_REVENUE_GENERATEAUCTIONPURCHASEGLDISTRIBUTION

            CREATE function dbo.UFN_REVENUE_GENERATEAUCTIONPURCHASEGLDISTRIBUTION
            (
                @REVENUEID uniqueidentifier,
                @IGNOREREVENUEID uniqueidentifier
            )
            returns @DISTRIBUTIONS table
            (
                REVENUEID uniqueidentifier,
                GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
                TRANSACTIONTYPECODE tinyint,
                POSTDATE datetime,
                POSTSTATUSCODE tinyint,
                ACCOUNTSTRING nvarchar(100),
                PROJECT nvarchar(100) ,
                AMOUNT money,
                REFERENCE nvarchar(255),
                ERRORMESSAGE nvarchar(max),
                PAYMENTMETHODCODE tinyint,
                REVENUETRANSACTIONTYPECODE tinyint,
                ACCOUNTID uniqueidentifier,
                REVENUESPLITTYPECODE tinyint,
                AUCTIONITEMVALUE money,
                AUCTIONITEMPOSTSTATUSCODE tinyint,
                REVENUEPURCHASEID uniqueidentifier,
                AUCTIONITEMPURCHASEAMOUNT money,
                REVENUESPLITAMOUNT money
            )
            as
                begin

                    --The distributions for auction items/auction item purchases are different than

                    --everything else in the system.  Auction items can be purchased by multiple

                    --revenue transactions, and we have to calculate a all distributions based off of all

                    --the postable payments towards the auction item.

                    --When one payment changes, we must re-calculate all of the distributions!!


                    --If the @IGNOREREVENUEID is not null, then we do not count any revenue towards auction items

                    --with that ID


                    declare @AUCTIONITEMS table
                    (
                        ID uniqueidentifier,
                        REVENUEAUCTIONDONATIONID uniqueidentifier,
                        VALUE money,
                        DONOTPOST tinyint,
                        PAYMENTCOUNT int
                    )

                    --Start by finding all items that are affected by this payment

                    insert into @AUCTIONITEMS
                    select 
                        AUCTIONITEM.ID,
                        AUCTIONITEM.REVENUEAUCTIONDONATIONID,
                        AUCTIONITEM.VALUE,
                        REVENUE.DONOTPOST,
                        count(AUCTIONITEMREVENUEPURCHASE.ID)
                    from 
                        dbo.AUCTIONITEMREVENUEPURCHASE
                        inner join dbo.AUCTIONITEM on AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID = AUCTIONITEM.ID
                        inner join dbo.REVENUE on AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID = REVENUE.ID
                        left join dbo.REVENUEPOSTED on REVENUE.ID = REVENUEPOSTED.ID
                    where
                        AUCTIONITEM.ID in (select AUCTIONITEMID from dbo.AUCTIONITEMREVENUEPURCHASE [AUCTIONITEMPURCHASE] where [AUCTIONITEMPURCHASE].REVENUEPURCHASEID = @REVENUEID)
                    group by AUCTIONITEM.ID,AUCTIONITEM.REVENUEAUCTIONDONATIONID,AUCTIONITEM.VALUE,REVENUE.DONOTPOST

                    -- Create a cursor to generate new distributions for affected items/payments

                    -- We do that by finding all payment records that helped pay for the items

                    declare @PERCENTAGEOFTOTALPURCHASE decimal(30,10) = 0;
                    declare @AUCTIONITEM_CURRENTPAYMENTCOUNT int = 0;
                    declare @PAYMENTCURSOR_REVENUEAUCTIONDONATIONID uniqueidentifier,@PAYMENTCURSOR_REVENUEPURCHASEID uniqueidentifier, @PAYMENTCURSOR_AUCTIONITEMID uniqueidentifier, @PAYMENTCURSOR_PAYMENTCOUNT integer;
                    declare PAYMENTCURSOR cursor local fast_forward
                    for select AUCTIONITEMS.REVENUEAUCTIONDONATIONID,AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID, AUCTIONITEMS.ID, AUCTIONITEMS.PAYMENTCOUNT
                        from
                            @AUCTIONITEMS AUCTIONITEMS
                            inner join dbo.AUCTIONITEMREVENUEPURCHASE on AUCTIONITEMS.ID = AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID
                        order by AUCTIONITEMS.ID

                    open PAYMENTCURSOR
                    fetch next from PAYMENTCURSOR into @PAYMENTCURSOR_REVENUEAUCTIONDONATIONID,@PAYMENTCURSOR_REVENUEPURCHASEID,@PAYMENTCURSOR_AUCTIONITEMID,@PAYMENTCURSOR_PAYMENTCOUNT

                    while @@FETCH_STATUS = 0
                    begin

                        select @AUCTIONITEM_CURRENTPAYMENTCOUNT = @AUCTIONITEM_CURRENTPAYMENTCOUNT + 1;

                        insert into @DISTRIBUTIONS
                        select 
                            AUCTIONITEM.REVENUEAUCTIONDONATIONID as REVENUEID, 
                            dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE, REVENUE.TRANSACTIONTYPECODE, CODES.REVENUESPLITTYPECODE, 12, tf.TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                            tf.TRANSACTIONTYPECODE, 
                            REVENUE.POSTDATE,
                            case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end as POSTSTATUSCODE,
                            tf.ACCOUNTSTRING, 
                            tf.PROJECTCODE as PROJECT,
                            0 as AMOUNT,  -- We figure this below to ensure no rounding issues

                            dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, REVENUEPAYMENTMETHOD.PAYMENTMETHOD, CODES.REVENUESPLITTYPE) as REFERENCE,
                            tf.ERRORMESSAGE,
                            REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
                            REVENUE.TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
                            tf.ACCOUNTID as ACCOUNTID,
                            CODES.REVENUESPLITTYPECODE,
                            AUCTIONITEM.VALUE as AUCTIONITEMVALUE,
                            case 
                                when AUCTIONITEMDONATION.DONOTPOST = 1 then 2 
                                when AUCTIONITEMDONATIONPOSTED.ID is not null then 0 
                                else 1 
                            end as AUCTIONITEMPOSTSTATUSCODE,
                            REVENUE.ID as REVENUEPURCHASEID,
                            case
                                when @IGNOREREVENUEID is null then
                                    PURCHASEPRICE.AMOUNT
                                when @IGNOREREVENUEID is not null then
                                    PURCHASEPRICEIGNOREREVENUE.AMOUNT
                            end,
                            REVENUESPLIT.AMOUNT
                        from 
                            dbo.REVENUE with (nolock)            
                            inner join dbo.REVENUEPAYMENTMETHOD with (nolock) on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
                            left join dbo.REVENUEPOSTED with (nolock) on REVENUEPOSTED.ID = REVENUE.ID
                            inner join dbo.REVENUESPLIT with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID
                            inner join dbo.AUCTIONITEMPURCHASE with (nolock) on AUCTIONITEMPURCHASE.PURCHASEID = REVENUESPLIT.ID
                            inner join dbo.AUCTIONITEM with (nolock) on AUCTIONITEM.ID = AUCTIONITEMPURCHASE.AUCTIONITEMID
                            inner join dbo.REVENUE as AUCTIONITEMDONATION with (nolock) on AUCTIONITEM.REVENUEAUCTIONDONATIONID = AUCTIONITEMDONATION.ID
                            left join dbo.REVENUEPOSTED as AUCTIONITEMDONATIONPOSTED with (nolock) on AUCTIONITEMDONATIONPOSTED.ID = AUCTIONITEMDONATION.ID
                            cross join 
                                ( 
                                    select '12' as REVENUESPLITTYPECODE, 'Auction purchase' as REVENUESPLITTYPE, 'Payment' as REVENUETYPE, '0' as REVENUETYPECODE
                                        union all
                                    select '203' as REVENUESPLITTYPECODE, 'Auction purchase gain' AS REVENUESPLITTYPE, 'Payment' as REVENUETYPE, '0' as REVENUETYPECODE
                                        union all  
                                    select '204' as REVENUESPLITTYPECODE, 'Auction purchase loss' AS REVENUESPLITTYPE, 'Payment' as REVENUETYPE, '0' as REVENUETYPECODE
                                ) as CODES
                            cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, REVENUE.TRANSACTIONTYPECODE, CODES.REVENUESPLITTYPECODE, REVENUESPLIT.APPLICATIONCODE, REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE, null) as tf
                            cross apply dbo.UFN_AUCTIONITEM_GETPOSTABLEPURCHASEAMOUNT(AUCTIONITEM.ID, null) as PURCHASEPRICE
                            cross apply dbo.UFN_AUCTIONITEM_GETPOSTABLEPURCHASEAMOUNT(AUCTIONITEM.ID, @IGNOREREVENUEID) as PURCHASEPRICEIGNOREREVENUE
                        where 
                            (REVENUE.ID = @PAYMENTCURSOR_REVENUEPURCHASEID)
                            and
                            (@IGNOREREVENUEID is null or (REVENUE.ID <> @IGNOREREVENUEID))
                            and
                            (AUCTIONITEM.ID = @PAYMENTCURSOR_AUCTIONITEMID)
                            and 
                            (
                                (@IGNOREREVENUEID is null and CODES.REVENUESPLITTYPECODE = 203 and AUCTIONITEM.VALUE < PURCHASEPRICE.AMOUNT)    -- Gain

                                or
                                (@IGNOREREVENUEID is not null and CODES.REVENUESPLITTYPECODE = 203 and AUCTIONITEM.VALUE < PURCHASEPRICEIGNOREREVENUE.AMOUNT)    -- Gain

                                or
                                (@IGNOREREVENUEID is null and CODES.REVENUESPLITTYPECODE = 204 and AUCTIONITEM.VALUE > PURCHASEPRICE.AMOUNT)    -- Loss

                                or
                                (@IGNOREREVENUEID is not null and CODES.REVENUESPLITTYPECODE = 204 and AUCTIONITEM.VALUE > PURCHASEPRICEIGNOREREVENUE.AMOUNT)    -- Loss

                                or
                                (CODES.REVENUESPLITTYPECODE = 12)
                            )
                            and
                            (REVENUE.DONOTPOST <> 1)


                        --Figure the distribution amounts here

                        declare @REVENUESPLITAMOUNT decimal(30,10) = 0;
                        declare @AUCTIONITEMPURCHASEAMOUNT decimal(30,10) = 0;
                        select 
                            @AUCTIONITEMPURCHASEAMOUNT = cast(AUCTIONITEMPURCHASEAMOUNT as decimal(30,10)),
                            @REVENUESPLITAMOUNT = cast(REVENUESPLITAMOUNT as decimal(30,10))
                        from @DISTRIBUTIONS 
                        where REVENUEPURCHASEID = @PAYMENTCURSOR_REVENUEPURCHASEID and REVENUEID = @PAYMENTCURSOR_REVENUEAUCTIONDONATIONID 

                        if @AUCTIONITEMPURCHASEAMOUNT <> 0
                            select @PERCENTAGEOFTOTALPURCHASE = @PERCENTAGEOFTOTALPURCHASE + (@REVENUESPLITAMOUNT/@AUCTIONITEMPURCHASEAMOUNT)

                        if @PAYMENTCURSOR_PAYMENTCOUNT <> @AUCTIONITEM_CURRENTPAYMENTCOUNT
                        begin
                            update @DISTRIBUTIONS
                            set AMOUNT = 
                                case
                                    when REVENUESPLITTYPECODE = 12 then
                                        round((cast(AUCTIONITEMVALUE as decimal(30,10)) * @PERCENTAGEOFTOTALPURCHASE) - (select sum(AMOUNT) from @DISTRIBUTIONS where REVENUEID = @PAYMENTCURSOR_REVENUEAUCTIONDONATIONID and REVENUESPLITTYPECODE = 12 and TRANSACTIONTYPECODE = 0),2)
                                    when REVENUESPLITTYPECODE in (203,204) then
                                        round((abs(cast(AUCTIONITEMVALUE as decimal(30,10)) - cast(AUCTIONITEMPURCHASEAMOUNT as decimal(30,10))) * @PERCENTAGEOFTOTALPURCHASE) - (select sum(AMOUNT) from @DISTRIBUTIONS where REVENUEID = @PAYMENTCURSOR_REVENUEAUCTIONDONATIONID and REVENUESPLITTYPECODE in (203,204) and TRANSACTIONTYPECODE = 0),2)
                                end
                            where REVENUEID = @PAYMENTCURSOR_REVENUEAUCTIONDONATIONID and REVENUEPURCHASEID = @PAYMENTCURSOR_REVENUEPURCHASEID
                        end
                        else
                        begin
                            --The last payment towards an item deals with the rounding issues

                            --It just gets what is left to distribute

                            update @DISTRIBUTIONS
                            set AMOUNT = 
                                case
                                    when REVENUESPLITTYPECODE = 12 then
                                        round(AUCTIONITEMVALUE - (select sum(AMOUNT) from @DISTRIBUTIONS where REVENUEID = @PAYMENTCURSOR_REVENUEAUCTIONDONATIONID and REVENUESPLITTYPECODE = 12 and TRANSACTIONTYPECODE = 0),2)
                                    when REVENUESPLITTYPECODE in (203,204) then
                                        round(abs(abs(AUCTIONITEMVALUE - AUCTIONITEMPURCHASEAMOUNT) - (select sum(AMOUNT) from @DISTRIBUTIONS where REVENUEID = @PAYMENTCURSOR_REVENUEAUCTIONDONATIONID and REVENUESPLITTYPECODE in (203,204) and TRANSACTIONTYPECODE = 0)),2)
                                end
                            where REVENUEID = @PAYMENTCURSOR_REVENUEAUCTIONDONATIONID and REVENUEPURCHASEID = @PAYMENTCURSOR_REVENUEPURCHASEID
                        end                    

                        --Reset the payment count and totals if we are moving on to a different item

                        if @PAYMENTCURSOR_PAYMENTCOUNT = @AUCTIONITEM_CURRENTPAYMENTCOUNT
                            select @AUCTIONITEM_CURRENTPAYMENTCOUNT = 0,@PERCENTAGEOFTOTALPURCHASE = 0

                        fetch next from PAYMENTCURSOR into @PAYMENTCURSOR_REVENUEAUCTIONDONATIONID,@PAYMENTCURSOR_REVENUEPURCHASEID,@PAYMENTCURSOR_AUCTIONITEMID,@PAYMENTCURSOR_PAYMENTCOUNT
                    end

                    close PAYMENTCURSOR
                    deallocate PAYMENTCURSOR

                return
            end