USP_GLTRANSACTION_ADDSTOCKSALEREVERSALS

            CREATE procedure dbo.USP_GLTRANSACTION_ADDSTOCKSALEREVERSALS
            (
                @STOCKSALEID 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 where STOCKSALEID = @STOCKSALEID 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.STOCKSALE where ID = @STOCKSALEID;

                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()
                    ,coalesce(A.ACCOUNTNUMBER,JEX.ACCOUNT,'')
                    ,JE.BASEAMOUNT
                    ,JEX.PROJECT
                    ,JE.COMMENT
                    ,JE.TRANSACTIONTYPECODE
                    ,JE.TRANSACTIONAMOUNT
                    ,isnull(JE.TRANSACTIONCURRENCYID, FT.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
                left join dbo.GLACCOUNT A on A.ID = JE.GLACCOUNTID
                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
                inner join dbo.FINANCIALTRANSACTION FT on LI.FINANCIALTRANSACTIONID = FT.ID
                where JEX.TABLENAMECODE = 11 and (JEX.STOCKSALEID = @STOCKSALEID or FT.ID = @STOCKSALEID) 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.STOCKSALEADJUSTMENT A
                inner join dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT LIA on LIA.ID = A.ID
                where STOCKSALEID = @STOCKSALEID
                order by A.DATEADDED desc;

                if @ADJUSTMENTPOSTSTATUSCODE <> 3  or @ADJUSTMENTPOSTSTATUSCODE is null
                    set @ADJUSTMENTPOSTSTATUSCODE = 1;

                if @ADJUSTMENTID is null
                begin
                    set @ADJUSTMENTID = newid();
                    insert into dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT (
                        ID
                        ,DATE
                        ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    values (
                        @ADJUSTMENTID
                        ,@CHANGEDATE
                        ,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
                    )
                end

                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.STOCKSALEADJUSTMENT T1 on T1.STOCKSALEID = 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
                        ,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
                        ,@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 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
                    ,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
                    ,OUTDATED
                    ,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                select 
                    source.REVERSALTRANSACTIONID
                    ,source.PROJECT
                    ,source.ACCOUNT
                    ,@JOURNAL
                    ,source.ORIGINALTRANSACTIONID
                    ,0
                    ,source.ORGANIZATIONEXCHANGERATEID
                    ,source.BASEEXCHANGERATEID
                    ,1
                    ,@CHANGEAGENTID, @CHANGEAGENTID, getdate(), getdate()
                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;