USP_ADJUSTMENTHISTORY_REVENUE_FIXFORDELETE

            CREATE procedure dbo.USP_ADJUSTMENTHISTORY_REVENUE_FIXFORDELETE
            (
                @REVENUEID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,
                @DATECHANGED datetime = null,
                @REVERSALADJUSTMENTHISTORYID uniqueidentifier = null output
            )
            as
            set nocount on;

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

            declare @UNPOSTEDADJUSTMENTID uniqueidentifier;
            declare @UNPOSTEDADJUSTMENTHISTORYID uniqueidentifier;
            declare @LASTPOSTEDADJUSTMENTID uniqueidentifier;
            declare @LASTPOSTEDADJUSTMENTHISTORYID uniqueidentifier; 

            /*determine if there is an unposted adjustment for this revenue*/
                select
                    @UNPOSTEDADJUSTMENTID = ID 
                from dbo.ADJUSTMENT 
                where REVENUEID = @REVENUEID and POSTSTATUSCODE = 1;
                select
                    @UNPOSTEDADJUSTMENTHISTORYID = ID 
                from dbo.ADJUSTMENTHISTORY 
                where ADJUSTMENTID = @UNPOSTEDADJUSTMENTID;

            /*get the most recent posted adjustment so we can write out GL history for it*/
                select top 1
                    @LASTPOSTEDADJUSTMENTID = ID 
                from dbo.ADJUSTMENT 
                where REVENUEID = @REVENUEID and 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 @UNPOSTEDADJUSTMENTID is null and not @LASTPOSTEDADJUSTMENTID is null
            begin
                select @LASTPOSTEDADJUSTMENTHISTORYID = ID
                from dbo.ADJUSTMENTHISTORY
                where ADJUSTMENTID = @LASTPOSTEDADJUSTMENTID;

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

                            TRANSACTIONTYPECODE,
                            ACCOUNT,
                            PROJECT,
                            '', --The function does not return a reference

                            AMOUNT,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @DATECHANGED,
                            @DATECHANGED,
                            TRANSACTIONAMOUNT,
                            ORGANIZATIONAMOUNT,
                            BASECURRENCYID,
                            TRANSACTIONCURRENCYID
                        from dbo.UFN_REVENUE_GETGLDISTRIBUTION(@REVENUEID);
            end

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

            insert into dbo.ADJUSTMENTHISTORY(ID, ADJUSTMENTID, REVENUEIDENTIFIER, ADJUSTMENTIDENTIFIER, CONSTITUENTNAME, REVENUETYPE, REVENUEDATE, ADJUSTMENTDATE, ADJUSTMENTPOSTDATE, ADJUSTMENTREASON, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                select
                    @REVERSALADJUSTMENTHISTORYID,
                    null,
                    cast(@REVENUEID as nvarchar(36)),
                    cast(newid() as nvarchar(36)), --This is just something to group by

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

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

                    '',
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @DATECHANGED,
                    @DATECHANGED
                from dbo.FINANCIALTRANSACTION
                inner join dbo.CONSTITUENT on CONSTITUENT.ID = FINANCIALTRANSACTION.CONSTITUENTID 
                where FINANCIALTRANSACTION.ID = @REVENUEID;

            insert into dbo.ADJUSTMENTHISTORYDETAIL(ID, ADJUSTMENTHISTORYID, ADJUSTEDFIELD, ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE)
                values(newid(), @REVERSALADJUSTMENTHISTORYID, 'Deleted', '', '', @CHANGEAGENTID, @CHANGEAGENTID, @DATECHANGED, @DATECHANGED, '', '', '', '');

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

                        case TRANSACTIONTYPECODE when 0 then 1 else 0 end,
                        ACCOUNT,
                        PROJECT,
                        '', --The function does not return a reference

                        AMOUNT,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @DATECHANGED,
                        @DATECHANGED,
                        TRANSACTIONAMOUNT,
                        ORGANIZATIONAMOUNT,
                        BASECURRENCYID,
                        TRANSACTIONCURRENCYID
                    from dbo.UFN_REVENUE_GETGLDISTRIBUTION(@REVENUEID);
            else /*otherwise, the reversal will be the reversal from the unposted adjustment */
                update dbo.ADJUSTMENTHISTORYDISTRIBUTION
                    set ADJUSTMENTHISTORYID = @REVERSALADJUSTMENTHISTORYID
                where ADJUSTMENTHISTORYID = @UNPOSTEDADJUSTMENTHISTORYID and TYPECODE = 0 --We only want the reversals


            /*if it exists, delete the unposted adjustment history. Since this will never post, it will never interact with GL and we don't 
              need to remember it.*/

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

            if not @CHANGEAGENTID is null
                set CONTEXT_INFO @CHANGEAGENTID;

            if not @UNPOSTEDADJUSTMENTID is null /*ADJUSTMENTHISTORY.ADJUSTMENTID is allowed to be null, so check first*/
                delete from dbo.ADJUSTMENTHISTORY where ADJUSTMENTID = @UNPOSTEDADJUSTMENTID

            if not @CONTEXTCACHE is null
                set CONTEXT_INFO @CONTEXTCACHE;