USP_GLTRANSACTION_ADDAUCTIONPURCHASEREVERSALS

    CREATE procedure dbo.USP_GLTRANSACTION_ADDAUCTIONPURCHASEREVERSALS
            (
                @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.AUCTIONPURCHASEADJUSTMENT 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,
                    TRANSACTIONAMOUNT money,
                    TRANSACTIONCURRENCYID uniqueidentifier,
                    BASEEXCHANGERATEID uniqueidentifier,
                    ORGANIZATIONAMOUNT money,
                    ORGANIZATIONEXCHANGERATEID uniqueidentifier,
                    GLACCOUNTID    uniqueidentifier,
                    FTLIID uniqueidentifier,
                    COPYFTLIID uniqueidentifier,
                    REVERSEDFTLIID uniqueidentifier,
                    SEQUENCE int
                );

                insert into @REVERSALS(
                    ORIGINALTRANSACTIONID,
                    REVERSALTRANSACTIONID,
                    ACCOUNT, 
                    AMOUNT,
                    PROJECT,
                    REFERENCE, 
                    TRANSACTIONTYPECODE,
                    TRANSACTIONAMOUNT, 
                    TRANSACTIONCURRENCYID, 
                    BASEEXCHANGERATEID, 
                    ORGANIZATIONAMOUNT, 
                    ORGANIZATIONEXCHANGERATEID,
                    GLACCOUNTID,
                    SEQUENCE
                )
                select 
                    JE.ID, 
                    newid(), 
                    JEX.ACCOUNT,
                    JE.BASEAMOUNT,
                    JEX.PROJECT, 
                    JE.COMMENT,
                    JE.TRANSACTIONTYPECODE,
                    JE.TRANSACTIONAMOUNT, 
                    JE.TRANSACTIONCURRENCYID, 
                    isnull(JEX.PRECALCBASEEXCHANGERATEID, FT.BASEEXCHANGERATEID),
                    JE.ORGAMOUNT, 
                    isnull(JEX.PRECALCORGANIZATIONEXCHANGERATEID, FT.ORGEXCHANGERATEID),
                    JE.GLACCOUNTID,
                    row_number() over (order by JE.DATEADDED)
                from dbo.JOURNALENTRY JE
                inner join dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID and JEX.TABLENAMECODE = 2
                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on JE.FINANCIALTRANSACTIONLINEITEMID = LI.ID
                inner join dbo.FINANCIALTRANSACTION FT on LI.FINANCIALTRANSACTIONID = FT.ID 
                where JEX.REVENUEPURCHASEID = @REVENUEID and JEX.OUTDATED = 0 and LI.POSTSTATUSCODE = 2;

                declare @ADJUSTMENTID uniqueidentifier
                declare @ADJUSTMENTPOSTSTATUSCODE tinyint
                Select top 1 @ADJUSTMENTPOSTSTATUSCODE = A.POSTSTATUSCODE 
                    ,@ADJUSTMENTID = LIA.ID
                from dbo.AUCTIONPURCHASEADJUSTMENT A
                inner join dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT LIA on LIA.ID = A.ID
                where REVENUEID = @REVENUEID
                order by A.DATEADDED desc;

                if @ADJUSTMENTPOSTSTATUSCODE <> 2  
                    set @ADJUSTMENTPOSTSTATUSCODE = 1;

                declare @REVERSEDFTLIID as table
                (
                    NEWFTLIID uniqueidentifier,                     
                    REVERSEDFTLIID uniqueidentifier,
                    COPYFTLIID uniqueidentifier
                );

                declare @FTID uniqueidentifier;
                declare @TABLENAMECODE tinyint;
                declare @ISADJUSTMENT bit = 0;

                insert into @REVERSEDFTLIID (NEWFTLIID,REVERSEDFTLIID,COPYFTLIID)
                select NEWID(),T2.FINANCIALTRANSACTIONLINEITEMID, NEWID()
                from  @REVERSALS T1
                inner join dbo.JOURNALENTRY T2 on T1.ORIGINALTRANSACTIONID = T2.ID
                group by T2.FINANCIALTRANSACTIONLINEITEMID

                update @REVERSALS set FTLIID = t3.NEWFTLIID, COPYFTLIID = T3.COPYFTLIID , REVERSEDFTLIID = T3.REVERSEDFTLIID
                from @REVERSALS t1 inner join dbo.JOURNALENTRY t2 on t1.ORIGINALTRANSACTIONID = t2.ID
                inner join @REVERSEDFTLIID t3 on t2.FINANCIALTRANSACTIONLINEITEMID = t3.REVERSEDFTLIID

                select top 1 @FTID = FTLI.FINANCIALTRANSACTIONID from dbo.FINANCIALTRANSACTIONLINEITEM FTLI join @REVERSEDFTLIID T1 on FTLI.ID = T1.REVERSEDFTLIID

                if @ADJUSTMENTID is not null
                    set @ISADJUSTMENT = 1;

                declare @LINEITEMS UDT_GENERICID;
                insert into @LINEITEMS
                select distinct
                    R.REVERSEDFTLIID
                from
                    @REVERSALS R

                exec dbo.USP_SAVE_REVERSAL_LINEITEM @LINEITEMS, @ADJUSTMENTID, @CHANGEAGENTID, @CHANGEDATE, @POSTDATE, @ADJUSTMENTPOSTSTATUSCODE

                if @ISADJUSTMENT = 1
                begin
                    exec dbo.USP_SAVE_HISTORICAL_LINEITEM @LINEITEMS, @ADJUSTMENTID, @CHANGEAGENTID, @CHANGEDATE, @POSTDATE, @ADJUSTMENTPOSTSTATUSCODE

                    insert into dbo.REVENUESPLIT_EXT (ID,DESIGNATIONID,APPLICATIONCODE, TYPECODE, ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                    select top 1
                        LI.ID, T1.DESIGNATIONID,T1.APPLICATIONCODE,T1.TYPECODE,T1.ADDEDBYID,T1.CHANGEDBYID,T1.DATEADDED,T1.DATECHANGED 
                    from
                        @REVERSALS T2 
                        inner join dbo.REVENUESPLIT_EXT T1 on T1.ID = T2.REVERSEDFTLIID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.REVERSEDLINEITEMID = T1.ID
                    order by
                        LI.DELETEDON desc
                end