USP_ADJUSTMENTHISTORY_WRITEOFF_FIXFORDELETE

            CREATE procedure dbo.USP_ADJUSTMENTHISTORY_WRITEOFF_FIXFORDELETE
            (
                @WRITEOFFID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,
                @CHANGEDATE datetime = null,
                @REVERSALADJUSTMENTHISTORYWRITEOFFID uniqueidentifier = null output 
            )
            as 
            set nocount on;

            if @CHANGEDATE is null
                set @CHANGEDATE = getdate();

            declare @UNPOSTEDWRITEOFFADJUSTMENTID uniqueidentifier;
            declare @UNPOSTEDADJUSTMENTHISTORYWRITEOFFID uniqueidentifier;
            declare @LASTPOSTEDWRITEOFFADJUSTMENTID uniqueidentifier;
            declare @LASTPOSTEDADJUSTMENTHISTORYWRITEOFFID uniqueidentifier; 
            --declare @REVERSALADJUSTMENTHISTORYWRITEOFFID uniqueidentifier;


            /*determine if there is an unposted adjustment for this write-off*/
            select
                @UNPOSTEDWRITEOFFADJUSTMENTID = ID 
            from dbo.WRITEOFFADJUSTMENT 
            where WRITEOFFID = @WRITEOFFID and WRITEOFFADJUSTMENT.POSTSTATUSCODE = 1;

            select
                @UNPOSTEDADJUSTMENTHISTORYWRITEOFFID = ID 
            from dbo.ADJUSTMENTHISTORYWRITEOFF
            where WRITEOFFADJUSTMENTID = @UNPOSTEDWRITEOFFADJUSTMENTID;

            /*get the most recent posted adjustment so we can write out GL history for it*/
            select top 1
                @LASTPOSTEDWRITEOFFADJUSTMENTID = ID
            from dbo.WRITEOFFADJUSTMENT
            where WRITEOFFID = @WRITEOFFID and WRITEOFFADJUSTMENT.POSTSTATUSCODE = 0
            order by TSLONG desc;

            /*if there is an unposted adjustment, the last posted adjustment will already have distribution information in its history.*/
            /* so do nothing */
            /*write the current GL information to the history for the last posted adjustment only if there was not an adjustment after it*/
            if @UNPOSTEDWRITEOFFADJUSTMENTID is null and not @LASTPOSTEDWRITEOFFADJUSTMENTID is null
            begin
                select @LASTPOSTEDADJUSTMENTHISTORYWRITEOFFID = ID
                from dbo.ADJUSTMENTHISTORYWRITEOFF
                where WRITEOFFADJUSTMENTID = @LASTPOSTEDWRITEOFFADJUSTMENTID;

                if not @LASTPOSTEDADJUSTMENTHISTORYWRITEOFFID is null
                    insert into dbo.ADJUSTMENTHISTORYWRITEOFFDISTRIBUTION(ADJUSTMENTHISTORYWRITEOFFID, TYPECODE, TRANSACTIONTYPECODE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
                        select
                            @LASTPOSTEDADJUSTMENTHISTORYWRITEOFFID,
                            1, --'Adjustment'

                            TRANSACTIONTYPECODE,
                            ACCOUNT,
                            PROJECT,
                            REFERENCE,
                            AMOUNT,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CHANGEDATE,
                            @CHANGEDATE,
                            TRANSACTIONAMOUNT,
                            ORGANIZATIONAMOUNT,
                            BASECURRENCYID,
                            TRANSACTIONCURRENCYID
                        from dbo.UFN_JOURNALENTRY_GETWRITEOFFGLDISTRIBUTION(@WRITEOFFID);
            end


            /*log the history header for the reversal*/
            set @REVERSALADJUSTMENTHISTORYWRITEOFFID = newid();

            insert into dbo.ADJUSTMENTHISTORYWRITEOFF(ID,WRITEOFFADJUSTMENTID, WRITEOFFIDENTIFIER, ADJUSTMENTIDENTIFIER, REVENUETYPE, CONSTITUENTNAME, DATE, ADJUSTMENTDATE, ADJUSTMENTPOSTDATE, ADJUSTMENTREASON, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                select
                    @REVERSALADJUSTMENTHISTORYWRITEOFFID,
                    null,
                    cast(@WRITEOFFID as nvarchar(36)),
                    cast(newid() as nvarchar(36)), --This is just something to group by

                    FINANCIALTRANSACTION.TYPE,
                    CONSTITUENT.NAME,
                    FINANCIALTRANSACTION.DATE, --This is the postdate of the reversal that got logged, so we'll use it too

                    @CHANGEDATE,
                    dbo.UFN_DATE_GETEARLIESTTIME(@CHANGEDATE), --The adjustment post date needs to be the date of the deletion

                    '' as ADJUSTMENTREASON,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CHANGEDATE,
                    @CHANGEDATE
                from dbo.WRITEOFF                
                inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = WRITEOFF.REVENUEID
                inner join dbo.CONSTITUENT on CONSTITUENT.ID = FINANCIALTRANSACTION.CONSTITUENTID
                where WRITEOFF.ID = @WRITEOFFID;

            insert into dbo.ADJUSTMENTHISTORYWRITEOFFDETAIL(ID, ADJUSTMENTHISTORYWRITEOFFID, ADJUSTEDFIELD,ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE)
                select
                    newid(), 
                    @REVERSALADJUSTMENTHISTORYWRITEOFFID, 
                    'Deleted write-off',
                    '', 
                    '', 
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CHANGEDATE, 
                    @CHANGEDATE,
                    '',
                    '',
                    '',
                    '';

            /*if there's not an unposted adjustment, the reversal for the deletion will be the calculated GL information*/
            if @UNPOSTEDWRITEOFFADJUSTMENTID is null
                insert into dbo.ADJUSTMENTHISTORYWRITEOFFDISTRIBUTION(ADJUSTMENTHISTORYWRITEOFFID, TYPECODE, TRANSACTIONTYPECODE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
                    select
                        @REVERSALADJUSTMENTHISTORYWRITEOFFID,
                        0, --Reversal

                        case TRANSACTIONTYPECODE when 0 then 1 else 0 end,
                        ACCOUNT,
                        PROJECT,
                        REFERENCE,
                        AMOUNT,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE,
                        TRANSACTIONAMOUNT,
                        ORGANIZATIONAMOUNT,
                        BASECURRENCYID,
                        TRANSACTIONCURRENCYID
                    from dbo.UFN_JOURNALENTRY_GETWRITEOFFGLDISTRIBUTION(@WRITEOFFID);
            else
                update dbo.ADJUSTMENTHISTORYWRITEOFFDISTRIBUTION
                    set ADJUSTMENTHISTORYWRITEOFFID = @REVERSALADJUSTMENTHISTORYWRITEOFFID
                    where ADJUSTMENTHISTORYWRITEOFFID = @UNPOSTEDADJUSTMENTHISTORYWRITEOFFID and TYPECODE = 0 --We only want the reversals



            declare @CONTEXTCACHE varbinary(128);
            set @CONTEXTCACHE = CONTEXT_INFO();

            if not @CHANGEAGENTID is null
                set CONTEXT_INFO @CHANGEAGENTID;

            if not @UNPOSTEDWRITEOFFADJUSTMENTID is null /*ADJUSTMENTWRITEOFFHISTORY.WRITEOFFADJUSTMENTID is allowed to be null, so check first*/
                delete from dbo.ADJUSTMENTHISTORYWRITEOFF where WRITEOFFADJUSTMENTID = @UNPOSTEDWRITEOFFADJUSTMENTID

            if not @CONTEXTCACHE is null
                set CONTEXT_INFO @CONTEXTCACHE;