USP_GLTRANSACTION_ADDBENEFITREVERSALS

            CREATE procedure dbo.USP_GLTRANSACTION_ADDBENEFITREVERSALS
            (
                @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,
                    GLACCOUNTID    uniqueidentifier,
                    FTLIID uniqueidentifier,
                    COPYFTLIID uniqueidentifier,
                    REVERSEDFTLIID uniqueidentifier,
                    SEQUENCE int
                );

                insert into @REVERSALS
                    (ORIGINALTRANSACTIONID, REVERSALTRANSACTIONID, ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE,
                    BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID
                    ,GLACCOUNTID, SEQUENCE)
                select 
                    JE.ID, newid(), GLACCOUNT.ACCOUNTNUMBER, JE.BASEAMOUNT, JEX.PROJECT, JE.COMMENT, JE.TRANSACTIONTYPECODE,
                    CURRENCYSET.BASECURRENCYID, JE.TRANSACTIONAMOUNT, FT.TRANSACTIONCURRENCYID, FT.BASEEXCHANGERATEID, JE.ORGAMOUNT, FT.ORGEXCHANGERATEID
                    ,JE.GLACCOUNTID, row_number() over (order by JE.DATEADDED)
                from dbo.JOURNALENTRY JE
                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on JE.FINANCIALTRANSACTIONLINEITEMID = LI.ID 
                inner join dbo.FINANCIALTRANSACTION FT on LI.FINANCIALTRANSACTIONID = FT.ID 
                inner join dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID and JEX.TABLENAMECODE = 5
                inner join dbo.GLACCOUNT on JE.GLACCOUNTID = GLACCOUNT.ID
                inner join dbo.PDACCOUNTSYSTEM on FT.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                where JEX.LOGICALREVENUEID = @REVENUEID and JEX.OUTDATED = 0 and LI.POSTSTATUSCODE = 2 and JEX.BENEFITTYPECODE in (1, 2);

                declare @ADJUSTMENTID uniqueidentifier
                DECLARE @ADJUSTMENTPOSTSTATUSCODE tinyint
                Select top 1 @ADJUSTMENTPOSTSTATUSCODE = A.POSTSTATUSCODE 
                    ,@ADJUSTMENTID = LIA.ID
                from dbo.BENEFITADJUSTMENT 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 exists (select 1 from dbo.FINANCIALTRANSACTION FT inner join dbo.BENEFITADJUSTMENT T1 on T1.REVENUEID = FT.ID and T1.POSTSTATUSCODE != 0 where FT.ID =  @FTID)
                    set @ISADJUSTMENT = 1    

                if @ISADJUSTMENT = 1
                begin 
                    insert into dbo.FINANCIALTRANSACTIONLINEITEM (
                        ID, REVERSEDLINEITEMID, FINANCIALTRANSACTIONID, TRANSACTIONAMOUNT, VISIBLE, DESCRIPTION, SEQUENCE, TYPECODE, POSTDATE, POSTSTATUSCODE
                        ,BASEAMOUNT,ORGAMOUNT , FINANCIALTRANSACTIONLINEITEMADJUSTMENTID, BATCHID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)                         
                    select distinct T1.COPYFTLIID, T1.REVERSEDFTLIID, T3.FINANCIALTRANSACTIONID , T3.TRANSACTIONAMOUNT,1,
                        T3.DESCRIPTION, T3.SEQUENCE, T3.TYPECODE, T3.POSTDATE, 2, T3.TRANSACTIONAMOUNT, T3.ORGAMOUNT, T3.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID, T3.BATCHID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
                    from @REVERSALS T1
                    inner join dbo.JOURNALENTRY T2 on T1.ORIGINALTRANSACTIONID = T2.ID
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM T3 on T2.FINANCIALTRANSACTIONLINEITEMID = T3.ID

                    update T3 set
                        FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID
                        ,CHANGEDBYID = @CHANGEAGENTID
                        ,DATECHANGED = @CHANGEDATE
                    from @REVERSALS T1
                    inner join dbo.JOURNALENTRY T2 on T1.ORIGINALTRANSACTIONID = T2.ID
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM T3 on T2.FINANCIALTRANSACTIONLINEITEMID = T3.ID
                end    

                insert into dbo.FINANCIALTRANSACTIONLINEITEM (
                    ID,REVERSEDLINEITEMID, FINANCIALTRANSACTIONID, TRANSACTIONAMOUNT, VISIBLE,DESCRIPTION,SEQUENCE,TYPECODE,POSTDATE,POSTSTATUSCODE
                    ,BASEAMOUNT,ORGAMOUNT , FINANCIALTRANSACTIONLINEITEMADJUSTMENTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)                         
                select distinct T1.FTLIID, T1.REVERSEDFTLIID, T3.FINANCIALTRANSACTIONID, T3.TRANSACTIONAMOUNT, 1,
                    T3.DESCRIPTION,T3.SEQUENCE, 1, @POSTDATE, 1, T3.TRANSACTIONAMOUNT, T3.ORGAMOUNT, @ADJUSTMENTID, T3.ADDEDBYID, T3.CHANGEDBYID, @CHANGEDATE, @CHANGEDATE
                from @REVERSALS T1
                inner join dbo.JOURNALENTRY T2 on T1.ORIGINALTRANSACTIONID = T2.ID
                inner join dbo.FINANCIALTRANSACTIONLINEITEM T3 on T2.FINANCIALTRANSACTIONLINEITEMID = T3.ID

                if @ISADJUSTMENT = 1        
                    update FTLI set POSTSTATUSCODE = 1,POSTDATE = @POSTDATE, FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID, BATCHID = null from dbo.FINANCIALTRANSACTIONLINEITEM FTLI inner join @REVERSEDFTLIID R on FTLI.ID = R.REVERSEDFTLIID inner join  @REVERSALS INS on R.REVERSEDFTLIID = INS.REVERSEDFTLIID

                update FTLI set DELETEDON = getdate() from dbo.FINANCIALTRANSACTIONLINEITEM FTLI inner join @REVERSEDFTLIID R on FTLI.ID = R.COPYFTLIID

                if @ISADJUSTMENT = 1      
                    update JOURNALENTRY set FINANCIALTRANSACTIONLINEITEMID = T1.COPYFTLIID from @REVERSALS T1 where JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = T1.REVERSEDFTLIID

                insert into dbo.JOURNALENTRY  
                    (ID,FINANCIALTRANSACTIONLINEITEMID,TRANSACTIONTYPECODE,SUBLEDGERTYPECODE, TRANSACTIONAMOUNT, BASEAMOUNT,ORGAMOUNT,COMMENT,POSTDATE,GLACCOUNTID,SEQUENCE ,TYPECODE,TRANSACTIONCURRENCYID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                select    
                    source.REVERSALTRANSACTIONID
                    ,source.FTLIID
                    ,case source.TRANSACTIONTYPECODE when 0 then 1 else 0 end    --check this

                    ,case source.TRANSACTIONTYPECODE when 0 then 1 else 0 end
                    ,source.TRANSACTIONAMOUNT
                    ,source.AMOUNT
                    ,source.ORGANIZATIONAMOUNT
                    ,source.REFERENCE
                    ,@POSTDATE
                    ,source.GLACCOUNTID
                    ,source.SEQUENCE
                    ,case when source.TRANSACTIONCURRENCYID is null then 1 else 0 end 
                    ,source.TRANSACTIONCURRENCYID                    
                    ,@CHANGEAGENTID, @CHANGEAGENTID, getdate(), getdate()
                from @REVERSALS as source

                insert into dbo.JOURNALENTRY_EXT
                    (ID,PROJECT,ACCOUNT,JOURNAL,REVERSEDGLTRANSACTIONID,TABLENAMECODE,
                    PRECALCORGANIZATIONEXCHANGERATEID,PRECALCBASEEXCHANGERATEID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED,OUTDATED)
                select 
                    source.REVERSALTRANSACTIONID
                    ,source.PROJECT
                    ,source.ACCOUNT
                    ,@JOURNAL
                    ,source.ORIGINALTRANSACTIONID
                    ,0 -- gltransaction table

                    ,source.ORGANIZATIONEXCHANGERATEID
                    ,source.BASEEXCHANGERATEID
                    ,@CHANGEAGENTID, @CHANGEAGENTID, getdate(), getdate(), 1
                from  @REVERSALS as source

                update dbo.JOURNALENTRY_EXT
                set
                    REVERSEDATE = @CHANGEDATE
                    ,OUTDATED = 1
                    ,CHANGEDBYID = @CHANGEAGENTID
                    ,DATECHANGED = @CHANGEDATE
                from
                    @REVERSALS as REV
                inner join
                    dbo.JOURNALENTRY_EXT on REV.ORIGINALTRANSACTIONID = JOURNALENTRY_EXT.ID