USP_GLTRANSACTION_ADDGIFTAIDREVERSALS

            CREATE procedure dbo.USP_GLTRANSACTION_ADDGIFTAIDREVERSALS
            (
                @REVENUESPLITGIFTAIDID 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';

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

                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 
                    GIFTAIDGLDISTRIBUTION.GLTRANSACTIONID, newid(), ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE,
                    BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID
                from 
                    dbo.GIFTAIDGLDISTRIBUTION 
                where 
                    GIFTAIDGLDISTRIBUTION.REVENUESPLITGIFTAIDID = @REVENUESPLITGIFTAIDID and OUTDATED = 0;

        declare @REVENUEID uniqueidentifier;
                select @REVENUEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID where FINANCIALTRANSACTIONLINEITEM.ID = @REVENUESPLITGIFTAIDID and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1;

        declare @ADJUSTMENTPOSTSTATUSCODE tinyint;
        set @ADJUSTMENTPOSTSTATUSCODE = 1;
        select @ADJUSTMENTPOSTSTATUSCODE = POSTSTATUSCODE from dbo.ADJUSTMENT
          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, POSTSTATUSCODE, 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,
                    @ADJUSTMENTPOSTSTATUSCODE,
                      (select GLACCOUNTID from dbo.GLTRANSACTION where GLTRANSACTION.id = ORIGINALTRANSACTIONID),
                    BASECURRENCYID, 
                    TRANSACTIONAMOUNT, 
                    TRANSACTIONCURRENCYID, 
                    BASEEXCHANGERATEID, 
                    ORGANIZATIONAMOUNT, 
                    ORGANIZATIONEXCHANGERATEID
                from 
                    @REVERSALS

                update JEX set
                    REVENUESPLITGIFTAIDID = @REVENUESPLITGIFTAIDID
                from
                    @REVERSALS R
                    inner join dbo.JOURNALENTRY JE on JE.ID = R.REVERSALTRANSACTIONID
                    inner join dbo.JOURNALENTRY_EXT JEX on JEX.ID = JE.ID

                --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 gift aid GL distributions as out of date

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

                --Set the status of the gift aid record to not posted.

                exec dbo.USP_REVENUESPLITGIFTAIDPOSTED_DELETEBYID_WITHCHANGEAGENTID @REVENUESPLITGIFTAIDID, @CHANGEAGENTID;