USP_GLTRANSACTION_ADDWRITEOFFREVERSALS_BYREVENUEID

            CREATE procedure dbo.USP_GLTRANSACTION_ADDWRITEOFFREVERSALS_BYREVENUEID
            (
                @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 = WRITEOFFADJUSTMENT.POSTDATE from dbo.WRITEOFFADJUSTMENT 
                    inner join dbo.WRITEOFF on WRITEOFFADJUSTMENT.WRITEOFFID = WRITEOFF.ID
                    where WRITEOFF.REVENUEID = @REVENUEID and WRITEOFFADJUSTMENT.POSTSTATUSCODE = 0 order by WRITEOFFADJUSTMENT.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.WRITEOFF where REVENUEID = @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 
                    JOURNALENTRY.ID, newid(), JOURNALENTRY_EXT.ACCOUNT, JOURNALENTRY.BASEAMOUNT, JOURNALENTRY_EXT.PROJECT, JOURNALENTRY.COMMENT, 
                    JOURNALENTRY.TRANSACTIONTYPECODE, V.BASECURRENCYID, JOURNALENTRY.TRANSACTIONAMOUNT, JOURNALENTRY.TRANSACTIONCURRENCYID, 
                    JOURNALENTRY_EXT.PRECALCBASEEXCHANGERATEID, JOURNALENTRY.ORGAMOUNT, JOURNALENTRY_EXT.PRECALCORGANIZATIONEXCHANGERATEID,
                    JOURNALENTRY.GLACCOUNTID, row_number() over (order by JOURNALENTRY.DATEADDED)
                from 
                dbo.JOURNALENTRY 
                inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID 
                inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = V.FINANCIALTRANSACTIONID
                inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID 
                inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID and JOURNALENTRY_EXT.TABLENAMECODE = 12
                where 
                    FINANCIALTRANSACTION.PARENTID = @REVENUEID and OUTDATED = 0;                





                 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                            


                      insert into dbo.FINANCIALTRANSACTIONLINEITEM (
                        ID,REVERSEDLINEITEMID, FINANCIALTRANSACTIONID, TRANSACTIONAMOUNT, VISIBLE,DESCRIPTION,SEQUENCE,TYPECODE,POSTDATE,POSTSTATUSCODE
                        ,BASEAMOUNT,ORGAMOUNT , ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)                         
                      select distinct T1.FTLIID, T1.REVERSEDFTLIID, T3.FINANCIALTRANSACTIONID, T3.TRANSACTIONAMOUNT, 1,
                        T3.DESCRIPTION,
                        --'reverse',

                        T3.SEQUENCE, 1, @POSTDATE, 1, T3.TRANSACTIONAMOUNT, T3.ORGAMOUNT, 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        
                        --group by T1.FTLIID ,T1.REVERSEDFTLIID ,T1.COPYFTLIID,    T3.FINANCIALTRANSACTIONID , T3.TRANSACTIONAMOUNT, T3.DESCRIPTION,T3.SEQUENCE,

                        --T3.TRANSACTIONAMOUNT, T3.ORGAMOUNT,T3.ADDEDBYID, T3.CHANGEDBYID    



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


                   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 

                --Set the REVERSEDATE of the original GL transactions to the current date

                    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