USP_GLTRANSACTION_ADDGIFTINKINDPAYMENTMETHODDETAILREVERSALS

            CREATE procedure dbo.USP_GLTRANSACTION_ADDGIFTINKINDPAYMENTMETHODDETAILREVERSALS
            (
                @GIFTINKINDPAYMENTMETHODDETAILID 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.GIFTINKINDSALEADJUSTMENT
                    inner join dbo.GIFTINKINDSALE on GIFTINKINDSALE.ID = GIFTINKINDSALEADJUSTMENT.GIFTINKINDSALEID
                    where GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = @GIFTINKINDPAYMENTMETHODDETAILID and POSTSTATUSCODE = 0 
                    order by GIFTINKINDSALEADJUSTMENT.DATEADDED desc;

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

                if @POSTDATE is null
                    select @POSTDATE = REVENUE.POSTDATE from dbo.REVENUE
                    inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
                    where REVENUEPAYMENTMETHOD.ID = @GIFTINKINDPAYMENTMETHODDETAILID;

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

                insert into @REVERSALS(ORIGINALTRANSACTIONID, REVERSALTRANSACTIONID, ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE)
                select 
                    GIFTINKINDSALEGLDISTRIBUTION.GLTRANSACTIONID, newid(), ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE
                from dbo.GIFTINKINDSALEGLDISTRIBUTION 
                inner join dbo.GIFTINKINDSALE on GIFTINKINDSALEGLDISTRIBUTION.GIFTINKINDSALEID = GIFTINKINDSALE.ID
                where 
                    GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = @GIFTINKINDPAYMENTMETHODDETAILID 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)
                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)
                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 gift-in-kind detail GL distributions as out of date

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