USP_GLTRANSACTION_ADDGIFTFEEREVERSALS

CREATE procedure dbo.USP_GLTRANSACTION_ADDGIFTFEEREVERSALS
(
    @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.GIFTFEEADJUSTMENT
        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,
        TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID
        ,GLACCOUNTID, SEQUENCE)
    select 
        JE.ID, newid(), GLACCOUNT.ACCOUNTNUMBER, JE.BASEAMOUNT, JEX.PROJECT, JE.COMMENT, JE.TRANSACTIONTYPECODE,
        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 = 8
    inner join dbo.GLACCOUNT on JE.GLACCOUNTID = GLACCOUNT.ID
    where JEX.LOGICALREVENUEID = @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.GIFTFEEADJUSTMENT 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 @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

    if exists (select 1 from dbo.FINANCIALTRANSACTION FT inner join dbo.GIFTFEEADJUSTMENT T1 on T1.REVENUEID = FT.ID and T1.POSTSTATUSCODE != 0 where FT.ID =  @REVENUEID)
        set @ISADJUSTMENT = 1    

    --Add the reversal ftli

    insert into dbo.FINANCIALTRANSACTIONLINEITEM (
        ID, REVERSEDLINEITEMID, FINANCIALTRANSACTIONID, TRANSACTIONAMOUNT, VISIBLE,DESCRIPTION,SEQUENCE,TYPECODE,POSTDATE,POSTSTATUSCODE
        ,BASEAMOUNT,ORGAMOUNT , FINANCIALTRANSACTIONLINEITEMADJUSTMENTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select distinct FTLIID, T3.ID, 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
    begin
        --Add a copy of the current gift fee ftli.  The copy will be marked as deleted

        --and retain the old gift fee values.  The old gift fee will be updated with the new

        --give fee values.

        --This step must be taken in case there are any foreign keys to the adjustment gift fee ftli.

        --We want those foreign keys to point to the new, updated values.

        insert into dbo.FINANCIALTRANSACTIONLINEITEM (
            ID,
            FINANCIALTRANSACTIONID,
            TRANSACTIONAMOUNT,
            VISIBLE,
            DESCRIPTION,
            SEQUENCE,
            TYPECODE,
            POSTDATE,
            POSTSTATUSCODE,
            BASEAMOUNT,
            ORGAMOUNT,
            SOURCELINEITEMID,
            FINANCIALTRANSACTIONLINEITEMADJUSTMENTID,
            REVERSEDLINEITEMID,
            DELETEDON,
            BATCHID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        select distinct
            REVERSALS.COPYFTLIID,
            FTLI.FINANCIALTRANSACTIONID,
            FTLI.TRANSACTIONAMOUNT,
            0 as VISIBLE,
            FTLI.DESCRIPTION,
            FTLI.SEQUENCE,
            FTLI.TYPECODE,
            FTLI.POSTDATE,
            FTLI.POSTSTATUSCODE,
            FTLI.BASEAMOUNT,
            FTLI.ORGAMOUNT,
            FTLI.SOURCELINEITEMID,
            FTLI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID,
            REVERSALS.REVERSEDFTLIID,
            getdate() as DELETEDON,
            FTLI.BATCHID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CHANGEDATE,
            @CHANGEDATE
        from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
            inner join @REVERSALS REVERSALS
        on FTLI.ID = REVERSALS.REVERSEDFTLIID

        declare @SOURCE table
        (
            ID uniqueidentifier,
            TRANSACTIONAMOUNT money,
            BASEAMOUNT money,
            ORGANIZATIONAMOUNT money,
            SOURCELINEITEMID uniqueidentifier,
            SEQUENCE int
        );

        --Unroll merge statement for performance

        insert into @SOURCE
        (
            ID,
            TRANSACTIONAMOUNT,
            BASEAMOUNT,
            ORGANIZATIONAMOUNT,
            SOURCELINEITEMID,
            SEQUENCE
        )
        select
            OLDPAYMENTFTLI.ID,
            REVENUESPLITGIFTFEE.TRANSACTIONAMOUNT,
            REVENUESPLITGIFTFEE.FEE as BASEAMOUNT,
            REVENUESPLITGIFTFEE.ORGANIZATIONAMOUNT,
            REVENUESPLITGIFTFEE.ID as SOURCELINEITEMID,
            row_number() over (order by NEWPAYMENTFTLI.sequence)
        from
            dbo.REVENUESPLITGIFTFEE
            left join dbo.FINANCIALTRANSACTIONLINEITEM as OLDPAYMENTFTLI on REVENUESPLITGIFTFEE.ID = OLDPAYMENTFTLI.REVERSEDLINEITEMID and OLDPAYMENTFTLI.TYPECODE = 0 and OLDPAYMENTFTLI.POSTSTATUSCODE = 2
            inner join dbo.FINANCIALTRANSACTIONLINEITEM as NEWPAYMENTFTLI on REVENUESPLITGIFTFEE.ID = NEWPAYMENTFTLI.ID
        where
            NEWPAYMENTFTLI.FINANCIALTRANSACTIONID = @REVENUEID

        --Update existing line items

        update LI set
            TRANSACTIONAMOUNT = SOURCELI.TRANSACTIONAMOUNT,
            VISIBLE = 1,
            SEQUENCE = SOURCELI.SEQUENCE,
            POSTDATE = @POSTDATE,
            POSTSTATUSCODE = 1,
            BASEAMOUNT = SOURCELI.BASEAMOUNT,
            ORGAMOUNT = SOURCELI.ORGANIZATIONAMOUNT,
            SOURCELINEITEMID = SOURCELI.SOURCELINEITEMID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CHANGEDATE,
            FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID
        from
            @SOURCE SOURCELI
            inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.SOURCELINEITEMID = SOURCELI.ID
        where
            LI.TYPECODE = 7
            and LI.DELETEDON is null
            and LI.FINANCIALTRANSACTIONID = @REVENUEID

        --Add new line items

        insert into dbo.FINANCIALTRANSACTIONLINEITEM
        (
            ID, FINANCIALTRANSACTIONID, TRANSACTIONAMOUNT, VISIBLE,    DESCRIPTION, SEQUENCE, TYPECODE, POSTDATE, POSTSTATUSCODE,
            BASEAMOUNT, ORGAMOUNT, SOURCELINEITEMID, FINANCIALTRANSACTIONLINEITEMADJUSTMENTID, BATCHID,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        select
            newid(), @REVENUEID, SOURCELI.TRANSACTIONAMOUNT, 1, 'Gift Fee', SOURCELI.SEQUENCE, 7, @POSTDATE, 1,
            SOURCELI.BASEAMOUNT, SOURCELI.ORGANIZATIONAMOUNT, SOURCELI.SOURCELINEITEMID, @ADJUSTMENTID, null,
            @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
        from
            @SOURCE SOURCELI
        where
            -- No line items in this transaction are gift fees for the current source

            SOURCELI.ID not in 
                (select
                    SOURCELINEITEMID
                from
                    dbo.FINANCIALTRANSACTIONLINEITEM LI
                where
                    LI.FINANCIALTRANSACTIONID = @REVENUEID
                    and LI.TYPECODE = 7)

        --Soft delete line items where the source no longer exists

        update LI set
            DELETEDON = @CHANGEDATE,
            TYPECODE = 99,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CHANGEDATE,
            FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID
        from
            dbo.FINANCIALTRANSACTIONLINEITEM LI
        where
            LI.DELETEDON is not null
            and LI.TYPECODE = 7
            and LI.FINANCIALTRANSACTIONID = @REVENUEID
            and LI.SOURCELINEITEMID not in
                (select
                    ID
                from
                    @SOURCE)

        --Move the journal entries to the old-value ftli.

        update JOURNALENTRY
        set
            FINANCIALTRANSACTIONLINEITEMID = REVERSALS.COPYFTLIID
        from dbo.JOURNALENTRY
        inner join @REVERSALS REVERSALS
            on REVERSALS.REVERSEDFTLIID = JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID

    end

    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;