USP_AUCTIONITEM_ADJUSTPURCHASEGLFORDONATION

            CREATE procedure dbo.USP_AUCTIONITEM_ADJUSTPURCHASEGLFORDONATION
            (
                @ID uniqueidentifier,
                @ISREVENUEID bit, 
                @CHANGEAGENTID uniqueidentifier,
                @CHANGEDATE datetime,
                @ADJUSTMENTREASONCODEID uniqueidentifier = null,
                @ADJUSTMENTREASON nvarchar(100) = ''
            )
            as
            set nocount on

            declare @AUCTIONITEMID uniqueidentifier; 
            if @ISREVENUEID = 1
                select @AUCTIONITEMID = AUCTIONITEM.ID 
                from dbo.AUCTIONITEM
                where REVENUEAUCTIONDONATIONID = @ID;
            else
                set @AUCTIONITEMID = @ID;

            declare @PACKAGEID uniqueidentifier = null;

            select @PACKAGEID = AUCTIONITEM.PACKAGEID from dbo.AUCTIONITEM where AUCTIONITEM.ID = @AUCTIONITEMID;

            -- If this auction item has been purchased, re-do all of the auction purchase distributions

            -- to ensure that they are pro-rated correctly. Re-doing the distributions for one purchase

            -- will re-do all of them. HOWEVER, if this item is in a package, we need to reverse out all of the 

            -- existing splits paying for items in that package and re-create them. (The splts for each item in the 

            -- package are calculated based on the value of the total package. If that's changed, all of those splits are

            -- now wrong)


            declare @FIRSTPURCHASEID uniqueidentifier = null;
            if @PACKAGEID is null
            begin
                select top 1 @FIRSTPURCHASEID = AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID
                from dbo.AUCTIONITEMREVENUEPURCHASE
                inner join dbo.AUCTIONITEM on AUCTIONITEM.ID = AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID 
                where AUCTIONITEM.ID = @AUCTIONITEMID
                    and AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID is not null;

                --@FIRSTPURCHASEID should never be null

                if not @FIRSTPURCHASEID is null
                begin

                    --Create an adjustment and reverse out the existing auction purchase splits for this revenue record.

                    --Skip this, and do not create adjustments and reversals if the payment was not posted.

                    if exists(select 1 from dbo.REVENUEPOSTED where REVENUEPOSTED.ID = @FIRSTPURCHASEID)
                    begin
                        --Create adjustments with a Not posted status code unless existing adjustments use the Do not post status code

                        declare @FIRSTPURCHASE_ADJUSTMENTPOSTSTATUSCODE tinyint = 1; --Not posted

                        if exists(select 1 from dbo.ADJUSTMENT where ADJUSTMENT.REVENUEID = @FIRSTPURCHASEID and ADJUSTMENT.POSTSTATUSCODE = 2)
                            set @FIRSTPURCHASE_ADJUSTMENTPOSTSTATUSCODE = 2; --Do not post


                        exec dbo.USP_SAVE_AUCTIONPURCHASEADJUSTMENT @FIRSTPURCHASEID, default, @CHANGEAGENTID, @CHANGEDATE,
                                                                    @CHANGEDATE, @CHANGEDATE, @ADJUSTMENTREASON, @ADJUSTMENTREASONCODEID,
                                                                    @FIRSTPURCHASE_ADJUSTMENTPOSTSTATUSCODE, default;
                    end

                    --Create new distributions

                    exec dbo.USP_SAVE_AUCTIONPURCHASEGLDISTRIBUTION @FIRSTPURCHASEID, @CHANGEAGENTID, @CHANGEDATE;
                end
            end
            else
            begin

                --Collect all payments made towards all items in this package

                declare @PACKAGEPAYMENTS table
                (
                    AUCTIONITEMID uniqueidentifier,
                    REVENUEDONATIONID uniqueidentifier,
                    REVENUEID uniqueidentifier,
                    REVENUESPLITID uniqueidentifier,
                    REVENUESPLITAMOUNT money,
                    POSTED bit,
                    ADJUSTED bit,
                    DONOTPOSTADJUSTMENT bit

                );

                declare @M nvarchar(50);
                insert into @PACKAGEPAYMENTS(AUCTIONITEMID, REVENUEDONATIONID, REVENUEID, REVENUESPLITID, REVENUESPLITAMOUNT, POSTED, ADJUSTED, DONOTPOSTADJUSTMENT)
                    select 
                        AUCTIONITEM.ID,
                        AUCTIONITEM.REVENUEAUCTIONDONATIONID,
                        REVENUESPLIT.REVENUEID,
                        REVENUESPLIT.ID,
                        REVENUESPLIT.AMOUNT,
                        case when exists(select ID from dbo.REVENUEPOSTED where REVENUEPOSTED.ID = REVENUESPLIT.REVENUEID) then 1 else 0 end,
                        case when exists(select ID from dbo.ADJUSTMENT where ADJUSTMENT.REVENUEID = REVENUESPLIT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE > 0) then 1 else 0 end,
                        case when exists(select ID from dbo.ADJUSTMENT where ADJUSTMENT.REVENUEID = REVENUESPLIT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 2) then 1 else 0 end
                    from dbo.AUCTIONITEM
                    inner join dbo.AUCTIONITEMPURCHASE on AUCTIONITEMPURCHASE.AUCTIONITEMID = AUCTIONITEM.ID
                    inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = AUCTIONITEMPURCHASE.PURCHASEID
                    where AUCTIONITEM.PACKAGEID = @PACKAGEID


                declare @PAYMENT_AMOUNT money;
                declare @PAYMENT_REVENUEID uniqueidentifier;
                declare @PAYMENT_ADJUSTMENTID uniqueidentifier;
                declare @PAYMENT_ADJUSTMENTPOSTSTATUSCODE tinyint;
                declare @PAYMENT_POSTED bit; 

                declare PAYMENT_ADJUSTMENT_CURSOR cursor local fast_forward for 
                    select distinct
                        REVENUEID, 
                        case when DONOTPOSTADJUSTMENT = 1 then 2 else 1 end,
                        POSTED
                    from @PACKAGEPAYMENTS;

                open PAYMENT_ADJUSTMENT_CURSOR;
                fetch next from PAYMENT_ADJUSTMENT_CURSOR into @PAYMENT_REVENUEID, @PAYMENT_ADJUSTMENTPOSTSTATUSCODE, @PAYMENT_POSTED;
                while @@FETCH_STATUS = 0
                begin

                    --This should create an adjustment and reverse out the existing auction purchase splits for this revenue record.

                    --Obviously, if the payment was not posted, no adjustment or reversals are needed

                    if @PAYMENT_POSTED = 1 
                        exec dbo.USP_SAVE_AUCTIONPURCHASEADJUSTMENT @PAYMENT_REVENUEID, @PAYMENT_ADJUSTMENTID output, @CHANGEAGENTID, @CHANGEDATE,
                                                                    @CHANGEDATE, @CHANGEDATE, @ADJUSTMENTREASON, @ADJUSTMENTREASONCODEID,
                                                                    @PAYMENT_ADJUSTMENTPOSTSTATUSCODE, default;


                    --Figure up how much money to spend over the entire package for this revenue record

                    select @PAYMENT_AMOUNT = sum(REVENUESPLIT.AMOUNT) 
                    from dbo.REVENUESPLIT 
                    inner join @PACKAGEPAYMENTS as [P] on [P].REVENUESPLITID = REVENUESPLIT.ID
                    inner join AUCTIONITEMPURCHASE on AUCTIONITEMPURCHASE.AUCTIONITEMID = [P].AUCTIONITEMID and AUCTIONITEMPURCHASE.PURCHASEID = REVENUESPLIT.ID
                    where [P].REVENUEID = @PAYMENT_REVENUEID;

                    --Delete the distributions for the splits we care about from this revenue record

                    delete AUCTIONPURCHASEGLDISTRIBUTION 
                    where AUCTIONPURCHASEGLDISTRIBUTION.ID in (select AUCTIONPURCHASEGLDISTRIBUTION.ID
                                        from dbo.AUCTIONPURCHASEGLDISTRIBUTION 
                    inner join @PACKAGEPAYMENTS as [P] on AUCTIONPURCHASEGLDISTRIBUTION.REVENUEID = [P].REVENUEDONATIONID 
                        and AUCTIONPURCHASEGLDISTRIBUTION.REVENUEPURCHASEID = [P].REVENUEID
                    where AUCTIONPURCHASEGLDISTRIBUTION.REVENUEPURCHASEID = @PAYMENT_REVENUEID);

                    --Delete all the splits we care about from this revenue record

                    delete REVENUESPLIT where REVENUESPLIT.ID in
                    (select REVENUESPLIT.ID from dbo.REVENUESPLIT 
                    inner join @PACKAGEPAYMENTS as [P] on [P].REVENUESPLITID = REVENUESPLIT.ID
                    inner join AUCTIONITEMPURCHASE on AUCTIONITEMPURCHASE.AUCTIONITEMID = [P].AUCTIONITEMID and AUCTIONITEMPURCHASE.PURCHASEID = REVENUESPLIT.ID
                    where [P].REVENUEID = @PAYMENT_REVENUEID);

                    --Since this revenue record is not currently paying for the item (we just deleted those splits!), remove the purchase records

                    delete AUCTIONITEMREVENUEPURCHASE from dbo.AUCTIONITEMREVENUEPURCHASE
                    inner join @PACKAGEPAYMENTS as [P] on AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID = [P].REVENUEID 
                        and AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID = [P].AUCTIONITEMID
                    where [P].REVENUEID = @PAYMENT_REVENUEID;

                    --Recreate the splits for the package 

                    exec dbo.USP_AUCTIONPURCHASE_ADDPAYMENTTOPACKAGE @PAYMENT_REVENUEID, @PAYMENT_AMOUNT, @PACKAGEID, @CHANGEAGENTID, @CHANGEDATE;

                    fetch next from PAYMENT_ADJUSTMENT_CURSOR into @PAYMENT_REVENUEID, @PAYMENT_ADJUSTMENTPOSTSTATUSCODE, @PAYMENT_POSTED;
                end
                close PAYMENT_ADJUSTMENT_CURSOR;

                --Recreate the distributions for the payment now that the splits are correct after all the splits for all the revenues 

                --have been saved. Re-use the existing cursor since it gets us what we want

                open PAYMENT_ADJUSTMENT_CURSOR;
                fetch next from PAYMENT_ADJUSTMENT_CURSOR into @PAYMENT_REVENUEID, @PAYMENT_ADJUSTMENTPOSTSTATUSCODE, @PAYMENT_POSTED;
                while @@FETCH_STATUS = 0
                begin
                    exec dbo.USP_SAVE_AUCTIONPURCHASEGLDISTRIBUTION @PAYMENT_REVENUEID, @CHANGEAGENTID, @CHANGEDATE;

                    fetch next from PAYMENT_ADJUSTMENT_CURSOR into @PAYMENT_REVENUEID, @PAYMENT_ADJUSTMENTPOSTSTATUSCODE, @PAYMENT_POSTED;
                end

                close PAYMENT_ADJUSTMENT_CURSOR;
                deallocate PAYMENT_ADJUSTMENT_CURSOR;
            end