USP_GLTRANSACTION_ADDUNREALIZEDGAINLOSSREVERSALS

            CREATE procedure dbo.USP_GLTRANSACTION_ADDUNREALIZEDGAINLOSSREVERSALS
            (
                @REVENUEID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null,
                @POSTDATE datetime = null
            )
            as 
                set nocount on;

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

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

                declare @JOURNAL nvarchar(50);
                set @JOURNAL = 'Blackbaud Enterprise';

                --Get the postdate for REVERSAL from the last adjustment posted

                if @POSTDATE is null
                    select top 1 @POSTDATE = POSTDATE from dbo.UNREALIZEDGAINLOSSADJUSTMENT where REVENUEID = @REVENUEID and POSTSTATUSCODE = 0 order by DATEADDED desc;

                --If there are no posted adjustments then get the postdate for REVERSAL from the REVENUE record

                if @POSTDATE is null
                    select @POSTDATE = POSTDATE from dbo.REVENUE where ID = @REVENUEID;

                declare @REVERSALS table(
                    ORIGINALTRANSACTIONID uniqueidentifier,
                    REVERSALTRANSACTIONID uniqueidentifier,
                    ACCOUNT nvarchar(100),
                    AMOUNT money,
                    PROJECT nvarchar(100),
                    REFERENCE nvarchar(255),
                    TRANSACTIONTYPECODE tinyint,
                    BASECURRENCYID uniqueidentifier,
                    ORGANIZATIONAMOUNT money
                );

                insert into @REVERSALS
                (
                    ORIGINALTRANSACTIONID, 
                    REVERSALTRANSACTIONID, 
                    ACCOUNT, 
                    AMOUNT, 
                    PROJECT, 
                    REFERENCE, 
                    TRANSACTIONTYPECODE,
                    BASECURRENCYID, 
                    ORGANIZATIONAMOUNT
                )
                select 
                    UNREALIZEDGAINLOSSGLDISTRIBUTION.GLTRANSACTIONID, 
                    newid(), 
                    UNREALIZEDGAINLOSSGLDISTRIBUTION.ACCOUNT, 
                    UNREALIZEDGAINLOSSGLDISTRIBUTION.AMOUNT, 
                    UNREALIZEDGAINLOSSGLDISTRIBUTION.PROJECT, 
                    UNREALIZEDGAINLOSSGLDISTRIBUTION.REFERENCE, 
                    UNREALIZEDGAINLOSSGLDISTRIBUTION.TRANSACTIONTYPECODE,
                    UNREALIZEDGAINLOSSGLDISTRIBUTION.BASECURRENCYID, 
                    UNREALIZEDGAINLOSSGLDISTRIBUTION.ORGANIZATIONAMOUNT
                from 
                    dbo.UNREALIZEDGAINLOSSGLDISTRIBUTION 
                where 
                    UNREALIZEDGAINLOSSGLDISTRIBUTION.REVENUEID = @REVENUEID 
                    and UNREALIZEDGAINLOSSGLDISTRIBUTION.OUTDATED = 0;

                declare @ADJUSTMENTPOSTSTATUSCODE tinyint;
                select 
                    @ADJUSTMENTPOSTSTATUSCODE = POSTSTATUSCODE 
                from 
                    dbo.UNREALIZEDGAINLOSSADJUSTMENT
                where 
                    REVENUEID = @REVENUEID ;

                if @ADJUSTMENTPOSTSTATUSCODE <> 2  
                    set @ADJUSTMENTPOSTSTATUSCODE = 1

                --Log reversal GL distributions

                insert into dbo.GLTRANSACTION
                (
                    ID,
                    REVERSEDGLTRANSACTIONID,
                    TRANSACTIONTYPECODE,
                    ACCOUNT,
                    AMOUNT,
                    PROJECT,
                    REFERENCE,
                    POSTDATE,
                    JOURNAL,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED,
                    GLACCOUNTID, 
                    POSTSTATUSCODE,
                    BASECURRENCYID, 
                    ORGANIZATIONAMOUNT
                )
                select 
                    REVERSALTRANSACTIONID, 
                    ORIGINALTRANSACTIONID,
                    case TRANSACTIONTYPECODE when 0 then 1 else 0 end, 
                    ACCOUNT, 
                    AMOUNT, 
                    PROJECT, 
                    REFERENCE, 
                    dbo.UFN_DATE_GETEARLIESTTIME(@POSTDATE),
                    @JOURNAL,
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CHANGEDATE, 
                    @CHANGEDATE,
                    (select GLACCOUNTID from dbo.GLTRANSACTION where GLTRANSACTION.id = ORIGINALTRANSACTIONID),
                    isnull(@ADJUSTMENTPOSTSTATUSCODE,1),
                    BASECURRENCYID, 
                    ORGANIZATIONAMOUNT
                from 
                    @REVERSALS

                --Set the REVERSEDATE of the original GL transactions to the current date

                update dbo.GLTRANSACTION
                set 
                    REVERSEDATE = @CHANGEDATE,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CHANGEDATE
        where ID in (select ORIGINALTRANSACTIONID from @REVERSALS)    

                --Set the reversed revenue GL distributions as out of date

                update dbo.UNREALIZEDGAINLOSSGLDISTRIBUTION
                set 
                    OUTDATED = 1,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CHANGEDATE
        where GLTRANSACTIONID in (select ORIGINALTRANSACTIONID from @REVERSALS)