USP_GLOBALCHANGE_AUCTIONDONATIONWRITEOFF

            CREATE procedure dbo.USP_GLOBALCHANGE_AUCTIONDONATIONWRITEOFF
            (
                @CHANGEAGENTID uniqueidentifier = null,
                @ASOF as datetime = null,
                @NUMBERADDED int = 0 output,
                @NUMBEREDITED int = 0 output,
                @NUMBERDELETED int = 0 output,
                @CURRENTAPPUSERID uniqueidentifier = null,

                @IDSETREGISTERID uniqueidentifier = null,
                @RECORDTYPEID uniqueidentifier = null,
                @AUCTIONID uniqueidentifier = null,
                @POSTSTATUSCODE tinyint,
                @POSTDATE datetime = null,
                @REASONCODEID uniqueidentifier = null,
                @REASON nvarchar(300) = null,
                @RECOGNITION int = 0
            )
            as

            set nocount on;

            declare @CURRENTDATE datetime;
            declare @PLEDGEID uniqueidentifier;
            declare @PLEDGEAMOUNT money;
            declare @TRANSACTIONTYPECODE tinyint;
            declare @RECOGNITIONCREDITS xml;

            set @NUMBERADDED = 0;
            set @NUMBEREDITED = 0;
            set @NUMBERDELETED = 0;
            set @ASOF = GetDate();
            set @CURRENTDATE = GetDate();

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

            declare @ORGANIZATIONCURRENCYID uniqueidentifier;
            set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

            --select the revenue id of the auction items from the selected

            --auction that haven't been purchased or reserved


            declare @IDMAPPING table
            (
                ITEMID uniqueidentifier,
                REVENUEID uniqueidentifier,
                WRITEOFFID uniqueidentifier
            )

            -- keeps track of auctionitemid, revenueid, and writeoffid

            if @IDSETREGISTERID is not null begin
                insert into @IDMAPPING
                    select
                        AUCTIONITEM.ID as ITEMID,
                        AUCTIONITEM.REVENUEAUCTIONDONATIONID as REVENUEID,
                        NewId() as WRITEOFFID
                    from

                        dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID)
                    inner join
                        dbo.AUCTIONITEM on dbo.AUCTIONITEM.ID = dbo.UFN_IDSETREADER_GETRESULTS_GUID.ID
                    left join 
                        dbo.AUCTIONITEMPURCHASE on AUCTIONITEM.ID = AUCTIONITEMPURCHASE.AUCTIONITEMID
                    where
                        AUCTIONITEMPURCHASE.PURCHASEID is null
                    and AUCTIONITEM.ID not in (select AUCTIONITEMRESERVATION.AUCTIONITEMID from dbo.AUCTIONITEMRESERVATION)
                    and AUCTIONITEM.TYPECODE = 0
                    and AUCTIONITEM.REVENUEAUCTIONDONATIONID not in (select WRITEOFF.REVENUEID from dbo.WRITEOFF)
            end

else begin
                insert into @IDMAPPING
                    select
                        AUCTIONITEM.ID as ITEMID,
                        AUCTIONITEM.REVENUEAUCTIONDONATIONID as REVENUEID,
                        NewId() as WRITEOFFID
                    from
                        dbo.AUCTIONITEM
                        left join dbo.AUCTIONITEMPURCHASE on AUCTIONITEM.ID = AUCTIONITEMPURCHASE.AUCTIONITEMID
                    where
                        AUCTIONITEM.EVENTAUCTIONID = @AUCTIONID
                    and AUCTIONITEMPURCHASE.PURCHASEID is null
                    and AUCTIONITEM.ID not in (select AUCTIONITEMRESERVATION.AUCTIONITEMID from dbo.AUCTIONITEMRESERVATION)
                    and AUCTIONITEM.TYPECODE = 0
                    and AUCTIONITEM.REVENUEAUCTIONDONATIONID not in (select WRITEOFF.REVENUEID from dbo.WRITEOFF)
            end

            select @NUMBEREDITED = count(*) from @IDMAPPING        

            if not @RECOGNITION = 2 begin
                set @RECOGNITIONCREDITS = (
                    select
                        REVENUERECOGNITION.ID,
                        REVENUESPLIT.ID as REVENUESPLITID,
                        dbo.UFN_DESIGNATION_BUILDNAME(REVENUESPLIT.DESIGNATIONID) as DESIGNATIONNAME,
                        CONSTITUENT.NAME as CONSTITUENTNAME,
                        REVENUERECOGNITIONTYPECODE.DESCRIPTION as RECOGNITIONCREDITTYPE,
                        REVENUERECOGNITION.EFFECTIVEDATE,
                        REVENUERECOGNITION.AMOUNT as ORIGINALAMOUNT,
                        case @RECOGNITION
                            when 0 then
                                0
                            when 1 then
                                case 
                                    when REVENUERECOGNITION.AMOUNT-REVENUE.AMOUNT >= 0
                                        then REVENUERECOGNITION.AMOUNT-REVENUE.AMOUNT
                                    else 0
                                end
                        end as ADJUSTEDAMOUNT
                    from dbo.REVENUE
                    inner join dbo.REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID
                    inner join dbo.REVENUERECOGNITION on REVENUESPLIT.ID = REVENUERECOGNITION.REVENUESPLITID
                    inner join dbo.CONSTITUENT on REVENUERECOGNITION.CONSTITUENTID = CONSTITUENT.ID
                    left join dbo.REVENUERECOGNITIONTYPECODE on REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
                    inner join @IDMAPPING as MAP on MAP.REVENUEID = REVENUE.ID
                    for xml raw('ITEM'),type,elements,root('RECOGNITIONCREDITS'),BINARY BASE64
                    )
            end

            begin try
                --Auction donations have more restrictions around write-offs

                declare @WRITEOFFCOUNTTABLE table
                (
                    WRITEOFFCOUNT integer
                )

                insert into @WRITEOFFCOUNTTABLE
                    select 
                        count(*)
                    from
                        WRITEOFF 
                    inner join
                        @IDMAPPING as MAP
                    on MAP.REVENUEID = WRITEOFF.REVENUEID

                if    (select count(*) from @WRITEOFFCOUNTTABLE where WRITEOFFCOUNT > 0) <> 0
                    raiserror('BBERR_AUCTIONDONATION_MULTIPLEWRITEOFFS', 13, 1);

                insert into dbo.WRITEOFF (ID,REVENUEID,DATE,POSTSTATUSCODE,POSTDATE,REASON,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED, REASONCODEID)
                    select
                        MAP.WRITEOFFID,
                        MAP.REVENUEID,
                        @CURRENTDATE,
                        @POSTSTATUSCODE,
                        @POSTDATE,
                        @REASON,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE,
                        @REASONCODEID
                    from
                        @IDMAPPING as MAP

                insert into dbo.WRITEOFFSPLIT (ID, WRITEOFFID, DESIGNATIONID, AMOUNT, 
                                                    BASECURRENCYID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, TRANSACTIONAMOUNT,
                                                    TRANSACTIONCURRENCYID, BASEEXCHANGERATEID,
                                                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    select
                        NewId(), MAP.WRITEOFFID, REVENUESPLIT.DESIGNATIONID, REVENUESPLIT.AMOUNT,
                        REVENUESPLIT.BASECURRENCYID, REVENUESPLIT.ORGANIZATIONAMOUNT, REVENUESPLIT.ORGANIZATIONEXCHANGERATEID, REVENUESPLIT.TRANSACTIONAMOUNT,
                        REVENUESPLIT.TRANSACTIONCURRENCYID, REVENUESPLIT.BASEEXCHANGERATEID,
                        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                    from
                        REVENUESPLIT
                    inner join
                        @IDMAPPING as MAP
                    on MAP.REVENUEID = REVENUESPLIT.REVENUEID

                    --Save the write-off GL distributions

                    if @POSTSTATUSCODE <> 2 begin
                        declare @ITEMREVENUEID uniqueidentifier
                        declare ITEMCURSOR cursor for
                            select MAP.REVENUEID from @IDMAPPING as MAP

                        open ITEMCURSOR
                        fetch next from ITEMCURSOR into @ITEMREVENUEID
                        while @@FETCH_STATUS = 0
                        begin
                            exec dbo.USP_SAVE_WRITEOFFGLDISTRIBUTION @ITEMREVENUEID, @CHANGEAGENTID, @CURRENTDATE;
                            fetch next from ITEMCURSOR into @ITEMREVENUEID
                        end
                        close ITEMCURSOR
                        deallocate ITEMCURSOR
                    end

                    -- Update recognition credit amounts


                    update dbo.REVENUERECOGNITION set
                        AMOUNT = UPDATEDRECOGNITIONCREDITS.ADJUSTEDAMOUNT,
                        ORGANIZATIONAMOUNT = 
                            case 
                                when REVENUERECOGNITION.BASECURRENCYID <> @ORGANIZATIONCURRENCYID
                                    then dbo.UFN_CURRENCY_CONVERT(UPDATEDRECOGNITIONCREDITS.ADJUSTEDAMOUNT, REVENUESPLIT.ORGANIZATIONEXCHANGERATEID)
                                else
                                    UPDATEDRECOGNITIONCREDITS.ADJUSTEDAMOUNT
                            end,
                        DATECHANGED = @CURRENTDATE,
                        CHANGEDBYID = @CHANGEAGENTID
                    from dbo.REVENUERECOGNITION
                    inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = REVENUERECOGNITION.REVENUESPLITID
                    inner join
                    (
                        select
                            T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
                            T.c.value('(ADJUSTEDAMOUNT)[1]','money') AS 'ADJUSTEDAMOUNT'
                        from @RECOGNITIONCREDITS.nodes('/RECOGNITIONCREDITS/ITEM') T(c)
                    ) UPDATEDRECOGNITIONCREDITS on REVENUERECOGNITION.ID = UPDATEDRECOGNITIONCREDITS.ID

                end try

                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch