USP_DATAFORMTEMPLATE_EDIT_AUCTIONDONATIONADJUST_2

            CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_AUCTIONDONATIONADJUST_2 
            (
                @ID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @AMOUNT money,
                @DATE datetime,
                @EXPIRATIONDATE datetime,
                @DESIGNATIONID uniqueidentifier,
                @ADJUSTMENTDATE datetime,
                @ADJUSTMENTPOSTDATE datetime,
                @ADJUSTMENTREASON nvarchar(300),
                @ADJUSTMENTREASONCODEID uniqueidentifier,

                @BASECURRENCYID uniqueidentifier,
                @TRANSACTIONCURRENCYID uniqueidentifier,
                @BASEEXCHANGERATEID uniqueidentifier,
                @EXCHANGERATE decimal(20,8),
                @HADSPOTRATE bit,
                @RATECHANGED bit,
                @CURRENTAPPUSERID uniqueidentifier = null
            )
            as

            set nocount on;

            if @CHANGEAGENTID is null
                exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

            declare @CURRENTDATE datetime;
            set @CURRENTDATE = getdate();

            declare @ADJUSTMENTID uniqueidentifier;
            declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

            begin try

                --Transaction currency cannot be changed, make sure it is the same as the revenue
                select 
                    @TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                    @ORGANIZATIONEXCHANGERATEID = FINANCIALTRANSACTION.ORGEXCHANGERATEID
                from
                    dbo.FINANCIALTRANSACTION
                where
                    FINANCIALTRANSACTION.ID = @ID;

                declare @LOOKUPORGANZIATIONEXCHANGERATE bit = 0;
                if @ORGANIZATIONEXCHANGERATEID is null
                begin
                    set @LOOKUPORGANZIATIONEXCHANGERATE = 1;
                end

                --Multicurrency - If the revenue previously used a spot rate, but
                -- its rate has changed, store the old rate's ID, so we can remove it later.
                declare @OLDSPOTRATE uniqueidentifier;
                if @HADSPOTRATE = 1 and @RATECHANGED = 1
                begin
                    select
                        @OLDSPOTRATE = FINANCIALTRANSACTION.BASEEXCHANGERATEID
                    from
                        dbo.FINANCIALTRANSACTION
                    where
                        FINANCIALTRANSACTION.ID = @ID;
                end
                --If the record uses a new spot rate, create it and set the rate ID.
                if @BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
                begin
                    set @BASEEXCHANGERATEID = newid();

                    --Replace with call to USP_CURRENCYEXCHANGERATE_ENSURESPOTRATEEXISTS for PBI 102747 in the future
                    /*if not dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID,'911f104d-ba5f-4469-b0ae-184c879aea99') = 1
                        and not dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
                    begin
                        raiserror('User does not have the right to add a new spot rate.', 13, 1);
                        return 1;
                    end*/


                    insert into dbo.CURRENCYEXCHANGERATE(
                    ID, 
                    FROMCURRENCYID,
                    TOCURRENCYID,
                    RATE,
                    ASOFDATE,
                    TYPECODE,
                    SOURCECODEID,
                    ADDEDBYID, 
                    CHANGEDBYID, 
                    DATEADDED, 
                    DATECHANGED
                    )
                    values(
                    @BASEEXCHANGERATEID,
                    @TRANSACTIONCURRENCYID,
                    @BASECURRENCYID,
                    @EXCHANGERATE,
                    @DATE,
                    2,
                    null,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                    );
                end

                --Multicurrency - Retrieve and calculate the necessary multicurrency values.                
                declare @ORGANIZATIONCURRENCYID uniqueidentifier;

                declare @MINIMUMBID money = 0;
                select @MINIMUMBID = coalesce(AUCTIONITEM.TRANSACTIONMINIMUMBID, 0) 
                from dbo.AUCTIONITEM 
                where AUCTIONITEM.REVENUEAUCTIONDONATIONID = @ID;

                declare @BASEAMOUNT money;
                declare @BASEMINIMUMBID money;
                declare @ORGANIZATIONAMOUNT money;
                declare @ORGANIZATIONMINIMUMBID money;

                exec dbo.USP_CURRENCY_GETCURRENCYVALUES
                    @AMOUNT,
                    @DATE,
                    @BASECURRENCYID,
                    @BASEEXCHANGERATEID,
                    @TRANSACTIONCURRENCYID output,
                    @BASEAMOUNT output,
                    @ORGANIZATIONCURRENCYID output,
                    @ORGANIZATIONAMOUNT output,
                    @ORGANIZATIONEXCHANGERATEID output,
                    @LOOKUPORGANZIATIONEXCHANGERATE;

                exec dbo.USP_CURRENCY_GETCURRENCYVALUES
                    @MINIMUMBID,
                    @DATE,
                    @BASECURRENCYID,
                    @BASEEXCHANGERATEID,
                    @TRANSACTIONCURRENCYID output,
                    @BASEMINIMUMBID output,
                    @ORGANIZATIONCURRENCYID output,
                    @ORGANIZATIONMINIMUMBID output,
                    @ORGANIZATIONEXCHANGERATEID output,
                    @LOOKUPORGANZIATIONEXCHANGERATE;

                declare @SITEID uniqueidentifier;
                select @SITEID = dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(@DESIGNATIONID);
                declare @EVENTAUCTIONID uniqueidentifier;
                select @EVENTAUCTIONID = AUCTIONITEM.EVENTAUCTIONID from dbo.AUCTIONITEM where REVENUEAUCTIONDONATIONID = @ID;

                if (@EVENTAUCTIONID is not null)
                    and 
                    (
                        (@SITEID is null and exists(select 1 from dbo.EVENTSITE where EVENTID = @EVENTAUCTIONID))
                        or 
                        (@SITEID is not null and not exists(select 1 from dbo.EVENTSITE where EVENTID = @EVENTAUCTIONID and SITEID = @SITEID))
                    )
                    raiserror('BBERR_AUCTIONITEM_DESIGNATIONNOTVALID',13,1);

                declare @ORIGINALAMOUNT money = 0;
                select 
                    @ORIGINALAMOUNT = AMOUNT
                from dbo.REVENUE
                where REVENUE.ID = @ID;

                declare @HASWRITEOFF bit = 0;

                if exists
                    (
                        select top 1
                            1
                        from
                            dbo.FINANCIALTRANSACTION
                            inner join dbo.WRITEOFF_EXT on FINANCIALTRANSACTION.ID = WRITEOFF_EXT.ID
                        where
                            FINANCIALTRANSACTION.TYPECODE = 20
                            and FINANCIALTRANSACTION.DELETEDON is null
                            and FINANCIALTRANSACTION.PARENTID = @ID
                    )
                begin
                    set @HASWRITEOFF = 1;
                end

                if @HASWRITEOFF = 1 and @AMOUNT <> @ORIGINALAMOUNT 
                    raiserror('BBERR_AUCTIONDONATION_CANNOTADJUSTWRITTENOFFAMOUNT', 13, 1);

                if @EXPIRATIONDATE < @DATE
                    raiserror('BBERR_AUCTIONDONATION_DONATIONDATEMUSTNOTBEAFTEREXPIRAITONDATE', 13, 1);

                declare @ADJUST bit;
                declare @CLEARGLDISTRIBUTION bit = 0;
                set @ADJUSTMENTID = null;

                if (
                    select
                        count(FINANCIALTRANSACTION.ID)
                    from
                        dbo.FINANCIALTRANSACTION
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                        inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                        inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTION.ID = V.FINANCIALTRANSACTIONID
                    where
                        FINANCIALTRANSACTION.ID = @ID
                        and FINANCIALTRANSACTION.TRANSACTIONAMOUNT = @AMOUNT
                        and FINANCIALTRANSACTION.BASEAMOUNT = @BASEAMOUNT
                        and ((FINANCIALTRANSACTION.BASEEXCHANGERATEID is null and @BASEEXCHANGERATEID is null) or (FINANCIALTRANSACTION.BASEEXCHANGERATEID = @BASEEXCHANGERATEID))
                        and ((FINANCIALTRANSACTION.ORGEXCHANGERATEID is null and @ORGANIZATIONEXCHANGERATEID is null) or (FINANCIALTRANSACTION.ORGEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID))
                        and FINANCIALTRANSACTION.TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID
                        and V.BASECURRENCYID = @BASECURRENCYID
                        and REVENUESPLIT_EXT.DESIGNATIONID = @DESIGNATIONID
                ) = 0 
                begin
                    set @CLEARGLDISTRIBUTION = 1;
                end

                -- Check GL business rule for this account system and set to 'Do not post' if needed.
                declare @POSTSTATUSCODE tinyint = 1;
                declare @PDACCOUNTSYSTEMID uniqueidentifier;
                select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID from dbo.PDACCOUNTSYSTEMFORREVENUE where ID = @ID;
                if dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID) = 0
                    set @POSTSTATUSCODE = 2;  -- Do not post


                select
                    @ADJUST = 1
                from
                    dbo.FINANCIALTRANSACTION
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                    inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTION.ID = V.FINANCIALTRANSACTIONID
                where 
                    FINANCIALTRANSACTION.ID = @ID
                    and 
                    (
                        FINANCIALTRANSACTION.TRANSACTIONAMOUNT <> @AMOUNT
                        or
                        FINANCIALTRANSACTION.BASEAMOUNT <> @BASEAMOUNT
                        or
                        FINANCIALTRANSACTION.BASEEXCHANGERATEID <> @BASEEXCHANGERATEID
                        or
                        FINANCIALTRANSACTION.ORGEXCHANGERATEID <> @ORGANIZATIONEXCHANGERATEID
                        or
                        FINANCIALTRANSACTION.TRANSACTIONCURRENCYID <> @TRANSACTIONCURRENCYID
                        or
                        V.BASECURRENCYID <> @BASECURRENCYID
                        or
                        FINANCIALTRANSACTION.DATE <> @DATE
                        or
                        REVENUESPLIT_EXT.DESIGNATIONID <> @DESIGNATIONID
                    );

                if @ADJUST = 1
                begin
                    if @ADJUSTMENTREASONCODEID is null
                        raiserror('BBERR_ADJUSTMENTREASONCODEIDREQUIRED', 13, 1);

                    exec dbo.USP_SAVE_ADJUSTMENT @ID, @ADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, 
                        @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, default, @ADJUSTMENTREASONCODEID, @POSTSTATUSCODE;
                end

                -- make sure we aren't trying to modify revenue in a locked/closed deposit/bank
                exec dbo.USP_EDIT_REVENUE_VALIDATE_DEPOSIT_STATUS @ID;

                -- handle updating the data
                update dbo.FINANCIALTRANSACTION set
                    BASEAMOUNT = @BASEAMOUNT,
                    TRANSACTIONAMOUNT = @AMOUNT,
                    DATE = @DATE,
                    ORGAMOUNT = @ORGANIZATIONAMOUNT,
                    BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
                    ORGEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
                    TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where ID = @ID;

                update dbo.REVENUE_EXT set
                    RECEIPTAMOUNT = @AMOUNT,
                    DATECHANGED = @CURRENTDATE,
                    CHANGEDBYID = @CHANGEAGENTID
                from
                    dbo.REVENUE_EXT
                where
                    REVENUE_EXT.ID = @ID;

                update dbo.FINANCIALTRANSACTIONLINEITEM set
                    BASEAMOUNT = @BASEAMOUNT,
                    TRANSACTIONAMOUNT = @AMOUNT,
                    ORGAMOUNT = @ORGANIZATIONAMOUNT,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where FINANCIALTRANSACTIONID = @ID;

                update dbo.AUCTIONITEM set
                    VALUE = @BASEAMOUNT,
                    TRANSACTIONVALUE = @AMOUNT,
                    ORGANIZATIONVALUE = @ORGANIZATIONAMOUNT,
                    MINIMUMBID = @BASEMINIMUMBID,
                    ORGANIZATIONMINIMUMBID = @ORGANIZATIONMINIMUMBID,
                    BASECURRENCYID = @BASECURRENCYID,
                    BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
                    ORIGINTOORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
                    TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
                    DATECHANGED = @CURRENTDATE,
                    CHANGEDBYID = @CHANGEAGENTID
                where REVENUEAUCTIONDONATIONID = @ID;

                update dbo.REVENUESPLIT_EXT set
                    DESIGNATIONID = @DESIGNATIONID,
                    DATECHANGED = @CURRENTDATE,
                    CHANGEDBYID = @CHANGEAGENTID
                from
                    dbo.REVENUESPLIT_EXT
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                where
                    FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID;

                if @CLEARGLDISTRIBUTION = 1
                begin
                    -- Clear GL
                    delete from dbo.REVENUEGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;

                    delete from dbo.WRITEOFFGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;

                    --Collect the IDs of revenue that will need new GL distributions
                    --as a result of editing this auction donation.
                    declare @AUCTIONPAYMENT table
                    (
                        ID uniqueidentifier
                    );

                    --Auction purchase for this item
                    insert into @AUCTIONPAYMENT (ID)
                    select top 1
                        AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID 
                    from
                        dbo.AUCTIONITEM
                        inner join dbo.AUCTIONITEMREVENUEPURCHASE on AUCTIONITEM.ID = AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID
                    where
                        AUCTIONITEM.REVENUEAUCTIONDONATIONID = @ID
                        and AUCTIONITEM.PACKAGEID is null
                        and AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID is not null;

                    --Purchases involving items that share a package with this auction donation
                    insert into @AUCTIONPAYMENT (ID)
                    select 
                        FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                    from
                        dbo.AUCTIONITEM
                        inner join dbo.AUCTIONITEM as SAMEPACKAGEAUCTIONITEM on AUCTIONITEM.PACKAGEID = SAMEPACKAGEAUCTIONITEM.PACKAGEID
                        inner join dbo.AUCTIONITEMPURCHASE on AUCTIONITEMPURCHASE.AUCTIONITEMID = SAMEPACKAGEAUCTIONITEM.ID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = AUCTIONITEMPURCHASE.PURCHASEID
                    where
                        AUCTIONITEM.REVENUEAUCTIONDONATIONID = @ID;

                    delete from dbo.AUCTIONPURCHASEGLDISTRIBUTION
                        where AUCTIONPURCHASEGLDISTRIBUTION.REVENUEPURCHASEID in (select ID from @AUCTIONPAYMENT)
                        and AUCTIONPURCHASEGLDISTRIBUTION.OUTDATED = 0;

                    -- Add new GL distributions
                    if @POSTSTATUSCODE <> 2
                    begin
                        exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;

                        exec dbo.USP_SAVE_WRITEOFFGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
                    end

                    --Need to update purchase GL because gains and losses may have changed
                    exec dbo.USP_AUCTIONITEM_ADJUSTPURCHASEGLFORDONATION @ID, 1, @CHANGEAGENTID, @CURRENTDATE;
                end

                if @ADJUST = 1
                    exec dbo.USP_ADJUSTMENTHISTORY_REVENUE_SAVEHISTORY @ID, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTID;

                --Multicurrency - If we stored an old spot rate earlier, now is the time to
                --remove it.
                if @OLDSPOTRATE is not null
                begin
                    delete CURRENCYEXCHANGERATE
                    where ID=@OLDSPOTRATE;
                end
            end try
            begin catch
                exec dbo.USP_RAISE_ERROR;
                return 1;
            end catch

            return 0;