USP_GLTRANSACTION_ADDPROPERTYDETAILREVERSALS

            CREATE procedure dbo.USP_GLTRANSACTION_ADDPROPERTYDETAILREVERSALS
            (
                @PROPERTYDETAILID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null,
                @POSTDATE datetime = null,
                @POSTSTATUSCODE tinyint = 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.PROPERTYDETAILADJUSTMENT where PROPERTYDETAILID = @PROPERTYDETAILID 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 = SALEPOSTDATE from dbo.PROPERTYDETAIL where ID = @PROPERTYDETAILID;

                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,
                    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
                    ,FT.BASEEXCHANGERATEID
                    ,JE.ORGAMOUNT
                    ,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 = 10
                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
                inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
                where (JEX.PROPERTYDETAILID = @PROPERTYDETAILID or FT.ID = @PROPERTYDETAILID) and JEX.OUTDATED = 0 and LI.POSTSTATUSCODE = 2;

                declare @ADJUSTMENTID uniqueidentifier
                declare @ADJUSTMENTPOSTSTATUSCODE tinyint
                Select top 1 @ADJUSTMENTPOSTSTATUSCODE = case A.POSTSTATUSCODE when 2 then 3 else 1 end
                    ,@ADJUSTMENTID = LIA.ID
                from dbo.PROPERTYDETAILADJUSTMENT A
                inner join dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT LIA on LIA.ID = A.ID
                where PROPERTYDETAILID = @PROPERTYDETAILID
                order by A.DATEADDED desc;

                if @ADJUSTMENTPOSTSTATUSCODE <> 3  or @ADJUSTMENTPOSTSTATUSCODE is null
                    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.PROPERTYDETAILADJUSTMENT T1 on T1.PROPERTYDETAILID = 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, @ADJUSTMENTPOSTSTATUSCODE, 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 = @ADJUSTMENTPOSTSTATUSCODE,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

                --Log reversal GL distributions

                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
                    ,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;