USP_GLTRANSACTION_ADDBENEFITBACKOUTREVERSALS

            CREATE procedure dbo.USP_GLTRANSACTION_ADDBENEFITBACKOUTREVERSALS
            (
                @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.BENEFITADJUSTMENT 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 
                    BENEFITGLDISTRIBUTION.GLTRANSACTIONID, newid(), BENEFITGLDISTRIBUTION.ACCOUNT, BENEFITGLDISTRIBUTION.AMOUNT, BENEFITGLDISTRIBUTION.PROJECT, BENEFITGLDISTRIBUTION.REFERENCE, BENEFITGLDISTRIBUTION.TRANSACTIONTYPECODE,
                    BENEFITGLDISTRIBUTION.BASECURRENCYID, BENEFITGLDISTRIBUTION.TRANSACTIONAMOUNT, BENEFITGLDISTRIBUTION.TRANSACTIONCURRENCYID, BENEFITGLDISTRIBUTION.BASEEXCHANGERATEID, BENEFITGLDISTRIBUTION.ORGANIZATIONAMOUNT, BENEFITGLDISTRIBUTION.ORGANIZATIONEXCHANGERATEID
                from 
                    dbo.BENEFITGLDISTRIBUTION
                    inner join dbo.GLTRANSACTION on BENEFITGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
                where 
                    REVENUEID = @REVENUEID and OUTDATED = 0 and POSTSTATUSCODE = 0 and BENEFITTYPECODE = 3;


                --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 ID in (select ORIGINALTRANSACTIONID from @REVERSALS)    

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

          merge into dbo.BENEFITGLDISTRIBUTION
          using @REVERSALS REV on BENEFITGLDISTRIBUTION.GLTRANSACTIONID = REV.ORIGINALTRANSACTIONID
          when matched
          then update 
          set OUTDATED = 1, 
              CHANGEDBYID = @CHANGEAGENTID, 
              DATECHANGED = @CHANGEDATE;