USP_GLTRANSACTION_ADDPAYOUTREVERSALS

            CREATE procedure dbo.USP_GLTRANSACTION_ADDPAYOUTREVERSALS
            (
                @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.PLANNEDGIFTPAYOUTADJUSTMENT 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,
                    TRANSACTIONAMOUNT money,
                    TRANSACTIONCURRENCYID uniqueidentifier,
                    BASEEXCHANGERATEID uniqueidentifier,
                    ORGANIZATIONAMOUNT money,
                    ORGANIZATIONEXCHANGERATEID uniqueidentifier
                );

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

                --Log reversal GL distributions

                insert into dbo.GLTRANSACTION
                (
                    ID,
                    REVERSEDGLTRANSACTIONID,
                    TRANSACTIONTYPECODE,
                    ACCOUNT,
                    AMOUNT,
                    PROJECT,
                    REFERENCE,
                    POSTDATE,
                    JOURNAL,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED,
                    GLACCOUNTID,
                    BASECURRENCYID, 
                    TRANSACTIONAMOUNT, 
                    TRANSACTIONCURRENCYID, 
                    BASEEXCHANGERATEID, 
                    ORGANIZATIONAMOUNT, 
                    ORGANIZATIONEXCHANGERATEID
                )
                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),
                    BASECURRENCYID, 
                    TRANSACTIONAMOUNT, 
                    TRANSACTIONCURRENCYID, 
                    BASEEXCHANGERATEID, 
                    ORGANIZATIONAMOUNT, 
                    ORGANIZATIONEXCHANGERATEID
                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 GLTRANSACTION.ID in (select GLTRANSACTION.ID                    
                from 
                    @REVERSALS REV
                inner join
                    dbo.GLTRANSACTION on REV.ORIGINALTRANSACTIONID = GLTRANSACTION.ID)        

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

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