USP_GLTRANSACTION_ADDSTOCKDETAILREVERSALS

            CREATE procedure dbo.USP_GLTRANSACTION_ADDSTOCKDETAILREVERSALS
            (
                @STOCKDETAILID 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.STOCKSALEADJUSTMENT
                    inner join dbo.STOCKSALE on STOCKSALE.ID = STOCKSALEADJUSTMENT.STOCKSALEID
                    where STOCKSALE.STOCKDETAILID = @STOCKDETAILID and POSTSTATUSCODE = 0 
                    order by STOCKSALEADJUSTMENT.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 = @STOCKDETAILID;

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

                insert into @REVERSALS(ORIGINALTRANSACTIONID, REVERSALTRANSACTIONID, ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE, TRANSACTIONCURRENCYID)
                select 
                    STOCKSALEGLDISTRIBUTION.GLTRANSACTIONID, newid(), ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE, STOCKSALEGLDISTRIBUTION.TRANSACTIONCURRENCYID
                from dbo.STOCKSALEGLDISTRIBUTION 
                inner join dbo.STOCKSALE on STOCKSALEGLDISTRIBUTION.STOCKSALEID = STOCKSALE.ID
                where 
                    STOCKSALE.STOCKDETAILID = @STOCKDETAILID 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, TRANSACTIONCURRENCYID)
                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),
                    TRANSACTIONCURRENCYID
                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 stock detail GL distributions as out of date

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