UFN_REVENUE_GETCHANGEDORDELETEDAUCTIONPURCHASES

            create function dbo.UFN_REVENUE_GETCHANGEDORDELETEDAUCTIONPURCHASES
            (
                @REVENUEID uniqueidentifier,
                @OLDAUCTIONPURCHASES xml
            )
            returns @AUCTIONITEMS table
            (
                AUCTIONITEMID uniqueidentifier,
                REVENUEAUCTIONDONATIONID uniqueidentifier,
                DONOTPOST bit
            )
            with execute as caller
            as begin

                declare @OLDPURCHASES table
                (
                    AUCTIONITEMID uniqueidentifier,
                    REVENUEAUCTIONDONATIONID uniqueidentifier,
                    PURCHASEID uniqueidentifier,
                    AMOUNT money
                )

                insert into @OLDPURCHASES(AUCTIONITEMID,REVENUEAUCTIONDONATIONID,PURCHASEID,AMOUNT)
                    select 
                        T.c.value('(AUCTIONITEMID)[1]','uniqueidentifier'), 
                        T.c.value('(REVENUEAUCTIONDONATIONID)[1]','uniqueidentifier'), 
                        T.c.value('(REVENUESPLITID)[1]','uniqueidentifier'), 
                        T.c.value('(AMOUNT)[1]','money')
                    from @OLDAUCTIONPURCHASES.nodes('/AUCTIONPURCHASES/ITEM') T(c)

                declare @NEWPURCHASES table
                (
                    AUCTIONITEMID uniqueidentifier,
                    REVENUEAUCTIONDONATIONID uniqueidentifier,
                    PURCHASEID uniqueidentifier,
                    AMOUNT money
                )

                insert into @NEWPURCHASES(AUCTIONITEMID,REVENUEAUCTIONDONATIONID,PURCHASEID,AMOUNT)
                    select
                        AUCTIONITEM.ID,
                        AUCTIONITEM.REVENUEAUCTIONDONATIONID,
                        REVENUESPLIT.ID,
                        REVENUESPLIT.AMOUNT
                    from 
                        dbo.AUCTIONITEM 
                        inner join dbo.AUCTIONITEMPURCHASE on AUCTIONITEM.ID = AUCTIONITEMPURCHASE.AUCTIONITEMID
                        inner join dbo.REVENUESPLIT on AUCTIONITEMPURCHASE.PURCHASEID = REVENUESPLIT.ID
                    where REVENUESPLIT.REVENUEID = @REVENUEID

                --Find items that are no longer being purchased

                insert into @AUCTIONITEMS(AUCTIONITEMID,REVENUEAUCTIONDONATIONID)
                    select OLDPURCHASES.AUCTIONITEMID,OLDPURCHASES.REVENUEAUCTIONDONATIONID
                    from @OLDPURCHASES OLDPURCHASES
                    left join @NEWPURCHASES NEWPURCHASES on OLDPURCHASES.AUCTIONITEMID = NEWPURCHASES.AUCTIONITEMID
                    where NEWPURCHASES.AUCTIONITEMID is null

                --Find items where the amount changed

                insert into @AUCTIONITEMS(AUCTIONITEMID,REVENUEAUCTIONDONATIONID)
                    select OLDPURCHASES.AUCTIONITEMID,OLDPURCHASES.REVENUEAUCTIONDONATIONID
                    from @OLDPURCHASES OLDPURCHASES inner join @NEWPURCHASES NEWPURCHASES on OLDPURCHASES.AUCTIONITEMID = NEWPURCHASES.AUCTIONITEMID
                    where OLDPURCHASES.AMOUNT <> NEWPURCHASES.AMOUNT

                --Find items that were added to the payment

                insert into @AUCTIONITEMS(AUCTIONITEMID,REVENUEAUCTIONDONATIONID)
                    select NEWPURCHASES.AUCTIONITEMID,NEWPURCHASES.REVENUEAUCTIONDONATIONID
                    from @NEWPURCHASES NEWPURCHASES
                    left join @OLDPURCHASES OLDPURCHASES on NEWPURCHASES.AUCTIONITEMID = OLDPURCHASES.AUCTIONITEMID
                    where OLDPURCHASES.AUCTIONITEMID is null

                update @AUCTIONITEMS
                set DONOTPOST = REVENUE.DONOTPOST
                from @AUCTIONITEMS AUCTIONITEMS
                inner join dbo.REVENUE on AUCTIONITEMS.REVENUEAUCTIONDONATIONID = REVENUE.ID

                return
            end