USP_SAVE_ADJUSTMENT_FOR_SPECIFIC_LINEITEMS

CREATE procedure [dbo].[USP_SAVE_ADJUSTMENT_FOR_SPECIFIC_LINEITEMS] (
    @REVENUEID uniqueidentifier = null
    ,@LINEITEMS UDT_GENERICID readonly
    ,@ADJUSTMENTID uniqueidentifier = null output
    ,@CHANGEAGENTID uniqueidentifier = null
    ,@CHANGEDATE datetime = null
    ,@DATE datetime = null
    ,@POSTDATE datetime = null
    ,@ADJUSTMENTREASON nvarchar(300) = null
    ,@ISNEWREVENUE bit = 0
    ,@ADJUSTMENTREASONCODEID uniqueidentifier = null
    ,@POSTSTATUSCODE tinyint = 1
    ,@ADJUSTMENTCODE tinyint = 0
) with execute as owner
as
/*call this procedure before making changes to the revenue tables.*/
    set nocount on;

    declare @REVENUEPOSTDATE datetime;
    declare @CONSTITUENTID uniqueidentifier;

    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    if @CHANGEDATE is null
        set @CHANGEDATE = getdate();

    select @ADJUSTMENTID = ID
    from dbo.ADJUSTMENT
    where REVENUEID = @REVENUEID
        and POSTSTATUSCODE <> 0

    if @ADJUSTMENTID is null
        set @ADJUSTMENTID = newid();

    declare @FTMPOSTSTATUSCODE tinyint;
    set @FTMPOSTSTATUSCODE = case @POSTSTATUSCODE when 2 then 3 when 0 then 2 else 1 end;

    if not @DATE is null
        set @DATE = cast(@DATE as date);

    if (
        select POSTSTATUSCODE
        from dbo.FINANCIALTRANSACTION
        where ID = @REVENUEID
    ) != 2
        raiserror ('You cannot adjust an unposted gift', 13, 1);

    exec dbo.USP_CREATEINITIALADJUSTMENTHISTORY @REVENUEID, @CHANGEAGENTID

    select @CONSTITUENTID = CONSTITUENTID
    from dbo.FINANCIALTRANSACTION
    where ID = @REVENUEID

    --Update unposted adjustment if existing

    if exists (select 1 from dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT where ID = @ADJUSTMENTID)
        update dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT
        set ADJUSTMENTREASONCODEID = @ADJUSTMENTREASONCODEID
            ,REASON = @ADJUSTMENTREASON
            ,CONSTITUENTID = @CONSTITUENTID
            ,[DATE] = @DATE
            ,CHANGEDBYID = @CHANGEAGENTID
            ,DATECHANGED = @CHANGEDATE
        where ID = @ADJUSTMENTID;
    else
        insert into dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT (
            ID
            ,ADJUSTMENTREASONCODEID
            ,REASON
            ,CONSTITUENTID
            ,[DATE]
            ,ADDEDBYID
            ,CHANGEDBYID
            ,DATEADDED
            ,DATECHANGED
            )
        values (
            @ADJUSTMENTID
            ,@ADJUSTMENTREASONCODEID
            ,@ADJUSTMENTREASON
            ,@CONSTITUENTID
            ,@DATE
            ,@CHANGEAGENTID
            ,@CHANGEAGENTID
            ,@CHANGEDATE
            ,@CHANGEDATE
            );

    if exists(select 1 from dbo.ADJUSTMENT where ID = @ADJUSTMENTID)
    begin
        declare @ALLADJUSTEDLINEITEMS UDT_GENERICID;
        insert into @ALLADJUSTEDLINEITEMS
        select LI.ID
        from @LINEITEMS LI
        union
        select LI.ID
        from dbo.FINANCIALTRANSACTIONLINEITEM LI
        where LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID

        update dbo.ADJUSTMENT
        set [DATE] = @DATE
            ,POSTDATE = @POSTDATE
            ,REASON = @ADJUSTMENTREASON
            ,REASONCODEID = @ADJUSTMENTREASONCODEID
            ,CHANGEDBYID = @CHANGEAGENTID
            ,DATECHANGED = @CHANGEDATE
            ,POSTSTATUSCODE = @POSTSTATUSCODE
            ,ADJUSTMENTCODE = case when ADJUSTMENTCODE = 0 then @ADJUSTMENTCODE else ADJUSTMENTCODE end
        where ID = @ADJUSTMENTID;

        update dbo.FINANCIALTRANSACTIONLINEITEM set
            POSTDATE = @POSTDATE
            ,POSTSTATUSCODE = @FTMPOSTSTATUSCODE
            ,DATECHANGED = @CHANGEDATE
            ,CHANGEDBYID = @CHANGEAGENTID
        from dbo.FINANCIALTRANSACTIONLINEITEM as FTLI
        left join @ALLADJUSTEDLINEITEMS LI on FTLI.ID = LI.ID
        where FTLI.FINANCIALTRANSACTIONID = @REVENUEID
            and FTLI.POSTSTATUSCODE != 2
            and ((not LI.ID is null) or LI.ID = FTLI.REVERSEDLINEITEMID or FTLI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID)

        -- Update the post date on the GL transaction records

        update dbo.JOURNALENTRY
        set JOURNALENTRY.POSTDATE = @POSTDATE
            ,JOURNALENTRY.DATECHANGED = @CHANGEDATE
            ,JOURNALENTRY.CHANGEDBYID = @CHANGEAGENTID
        from dbo.FINANCIALTRANSACTIONLINEITEM as FTLI
        left join @ALLADJUSTEDLINEITEMS LI on FTLI.ID = LI.ID
        inner join dbo.JOURNALENTRY as JE on JE.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID
        inner join dbo.JOURNALENTRY_EXT as JEE on JE.ID = JEE.ID
        where FTLI.FINANCIALTRANSACTIONID = @REVENUEID
            and ((not LI.ID is null) or LI.ID = FTLI.REVERSEDLINEITEMID or FTLI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID)
            and FTLI.POSTSTATUSCODE != 2
            and (JEE.OUTDATED = 0
                or JEE.TABLENAMECODE = 0);

        -- post date for unposted reversals should match the adjustment post date

        update dbo.JOURNALENTRY
        set JOURNALENTRY.POSTDATE = @POSTDATE
            ,JOURNALENTRY.DATECHANGED = @CHANGEDATE
            ,JOURNALENTRY.CHANGEDBYID = @CHANGEAGENTID
        from dbo.JOURNALENTRY
        inner join dbo.JOURNALENTRY_EXT as JEE on JOURNALENTRY.ID = JEE.ID
        inner join dbo.JOURNALENTRY as JE_REV on JEE.REVERSEDGLTRANSACTIONID = JE_REV.ID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI_REV on JE_REV.FINANCIALTRANSACTIONLINEITEMID = FTLI_REV.ID
        left join @ALLADJUSTEDLINEITEMS LI on FTLI_REV.ID = LI.ID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI on FTLI.ID = JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID
        where FTLI_REV.FINANCIALTRANSACTIONID = @REVENUEID
            and FTLI.POSTSTATUSCODE = 1
            and ((not LI.ID is null) or LI.ID = FTLI.REVERSEDLINEITEMID or FTLI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID);

        --Save the snapshot

        exec dbo.USP_ADJUSTMENTHISTORY_REVENUE_SAVESNAPSHOT_FOR_SPECIFIC_LINEITEMS @ADJUSTMENTID,@ALLADJUSTEDLINEITEMS,@ISNEWREVENUE,@CHANGEAGENTID;
    end
    else
    begin
        if @POSTSTATUSCODE = 0
            set @POSTSTATUSCODE = 1

        --Log Adjustment if new

        insert into dbo.ADJUSTMENT (
            ID
            ,REVENUEID
            ,PREVIOUSAMOUNT
            ,[DATE]
            ,POSTDATE
            ,POSTSTATUSCODE
            ,REASON
            ,REASONCODEID
            ,ADJUSTMENTCODE
            ,ADDEDBYID
            ,CHANGEDBYID
            ,DATEADDED
            ,DATECHANGED
            ,BASECURRENCYID
            ,TRANSACTIONCURRENCYID
            ,TRANSACTIONPREVIOUSAMOUNT
            ,BASEEXCHANGERATEID
            ,ORGANIZATIONPREVIOUSAMOUNT
            ,ORGANIZATIONEXCHANGERATEID
            )
        select @ADJUSTMENTID
            ,FINANCIALTRANSACTION.ID
            ,FINANCIALTRANSACTION.BASEAMOUNT
            ,@DATE
            ,@POSTDATE
            ,@POSTSTATUSCODE
            ,@ADJUSTMENTREASON
            ,@ADJUSTMENTREASONCODEID
            ,@ADJUSTMENTCODE
            ,@CHANGEAGENTID
            ,@CHANGEAGENTID
            ,@CHANGEDATE
            ,@CHANGEDATE
            ,CURRENCYSET.BASECURRENCYID
            ,FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
            ,FINANCIALTRANSACTION.TRANSACTIONAMOUNT
            ,FINANCIALTRANSACTION.BASEEXCHANGERATEID
            ,FINANCIALTRANSACTION.ORGAMOUNT
            ,FINANCIALTRANSACTION.ORGEXCHANGERATEID
        from dbo.FINANCIALTRANSACTION
        inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
        inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
        where FINANCIALTRANSACTION.ID = @REVENUEID;

        --Save the snapshot before deleting the GL information

        exec dbo.USP_ADJUSTMENTHISTORY_REVENUE_SAVESNAPSHOT_FOR_SPECIFIC_LINEITEMS @ADJUSTMENTID,@LINEITEMS,@ISNEWREVENUE,@CHANGEAGENTID;
    end

    declare @NEWLYADJUSTEDLINEITEMS UDT_GENERICID;
    insert into @NEWLYADJUSTEDLINEITEMS
    select LI.ID
    from @LINEITEMS LI
    inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = LI.ID
    where isnull(FTLI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID, newid()) != @ADJUSTMENTID;

    if (select top 1 1 from @NEWLYADJUSTEDLINEITEMS) = 1
    begin
        --Log reversal and historical data.

        exec dbo.USP_SAVE_REVERSAL_LINEITEM @NEWLYADJUSTEDLINEITEMS, @ADJUSTMENTID, @CHANGEAGENTID, @CHANGEDATE, @POSTDATE, @FTMPOSTSTATUSCODE

        declare @HISTORICALINFO table (ID uniqueidentifier, ADJUSTMENTID uniqueidentifier)
        insert into @HISTORICALINFO
        select LI.ID, FTLI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
        from @NEWLYADJUSTEDLINEITEMS LI
        inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on LI.ID = FTLI.ID;

        exec dbo.USP_SAVE_HISTORICAL_LINEITEM @NEWLYADJUSTEDLINEITEMS, @ADJUSTMENTID, @CHANGEAGENTID, @CHANGEDATE, @POSTDATE, @FTMPOSTSTATUSCODE

        insert into dbo.REVENUESPLIT_EXT (
            ID
            ,DESIGNATIONID
            ,APPLICATIONCODE
            ,TYPECODE
            ,ADDEDBYID
            ,CHANGEDBYID
            ,DATEADDED
            ,DATECHANGED
            )
        select
            LI.ID
            ,RSE.DESIGNATIONID
            ,RSE.APPLICATIONCODE
            ,RSE.TYPECODE
            ,RSE.ADDEDBYID
            ,RSE.CHANGEDBYID
            ,RSE.DATEADDED
            ,RSE.DATECHANGED
        from @HISTORICALINFO HLI
        inner join dbo.REVENUESPLIT_EXT RSE on HLI.ID = RSE.ID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on HLI.ID = LI.REVERSEDLINEITEMID and HLI.ADJUSTMENTID = LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
        where not exists (select 1 from dbo.REVENUESPLIT_EXT where ID = LI.ID);
    end

    declare @Error nvarchar(255) = '';

    -- Bug 70136 - Null post date if adjustment is DNP

    if @@ROWCOUNT > 0 and @POSTSTATUSCODE <> 2
        set @Error = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE(@POSTDATE);

    if @Error <> ''
        raiserror (@Error, 13, 1);