USP_SAVE_PLANNEDGIFTPAYOUTADJUSTMENT

            CREATE procedure dbo.USP_SAVE_PLANNEDGIFTPAYOUTADJUSTMENT
            (
                @REVENUEID uniqueidentifier = null,
                @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
            )
            with execute as owner
            as
            /*call this procedure before making changes to the revenue tables.*/

            set nocount on;

            declare @REVENUEPOSTDATE datetime;

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

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

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

            --Is the revenue posted?

            if (select count(REVENUE.ID) from dbo.REVENUE
                inner join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
                where REVENUE.ID = @REVENUEID) = 0
                raiserror('You cannot adjust an unposted planned gift', 13, 1)

            --Update unposted adjustment if existing

            update dbo.PLANNEDGIFTPAYOUTADJUSTMENT
            set DATE = @DATE, 
                POSTDATE = @POSTDATE,
                REASON = @ADJUSTMENTREASON,
                REASONCODEID = @ADJUSTMENTREASONCODEID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CHANGEDATE,
                POSTSTATUSCODE = @POSTSTATUSCODE
            where REVENUEID = @REVENUEID and POSTSTATUSCODE <> 0

            if @@ROWCOUNT = 0
            begin
                --Log Adjustment if new

                insert into dbo.PLANNEDGIFTPAYOUTADJUSTMENT
                    (ID, REVENUEID, PREVIOUSAMOUNT, DATE, POSTDATE, POSTSTATUSCODE, REASON, REASONCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, BASECURRENCYID, TRANSACTIONCURRENCYID, TRANSACTIONPREVIOUSAMOUNT, BASEEXCHANGERATEID, ORGANIZATIONPREVIOUSAMOUNT, ORGANIZATIONEXCHANGERATEID)
                select 
                    @ADJUSTMENTID, 
                    REVENUE.ID, 
                    AMOUNT, 
                    @DATE, 
                    @POSTDATE, 
                    @POSTSTATUSCODE, 
                    @ADJUSTMENTREASON, 
                    @ADJUSTMENTREASONCODEID, 
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CHANGEDATE, 
                    @CHANGEDATE,
                    REVENUE.BASECURRENCYID, 
                    REVENUE.TRANSACTIONCURRENCYID, 
                    REVENUE.TRANSACTIONAMOUNT, 
                    REVENUE.BASEEXCHANGERATEID, 
                    REVENUE.ORGANIZATIONAMOUNT, 
                    REVENUE.ORGANIZATIONEXCHANGERATEID
                from 
                    dbo.REVENUE
                where 
                    REVENUE.ID = @REVENUEID;

                insert into dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT (
                    ID
                    ,ADJUSTMENTREASONCODEID
                    ,REASON
                    ,DATE
                    ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                values
                    (@ADJUSTMENTID,
                    @ADJUSTMENTREASONCODEID,
                    @ADJUSTMENTREASON,
                    @POSTDATE,
                    @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE    )

                --Save the snapshot before deleting the GL information

                --exec dbo.USP_ADJUSTMENTHISTORY_PLANNEDGIFTPAYOUT_SAVESNAPSHOT @ADJUSTMENTID, @CHANGEAGENTID, @ISNEWREVENUE;


                --Log reversals in the GLTRANSACTION table

                exec dbo.USP_GLTRANSACTION_ADDPAYOUTREVERSALS @REVENUEID, @CHANGEAGENTID, @CHANGEDATE, @POSTDATE;

            end
            else /*make sure @ADJUSTMENTID gets set to the correct value*/
            begin
                select
                    @ADJUSTMENTID = ID
                from dbo.PLANNEDGIFTPAYOUTADJUSTMENT
                where REVENUEID = @REVENUEID and POSTSTATUSCODE <> 0;

                update dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT set
                    ADJUSTMENTREASONCODEID = @ADJUSTMENTREASONCODEID,
                    REASON = @ADJUSTMENTREASON,
                    DATE = @POSTDATE,
                    CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                where
                    ID = @ADJUSTMENTID

                -- Update the post date on the GL transaction records

                update 
                    dbo.GLTRANSACTION
                set
                    GLTRANSACTION.POSTDATE = @POSTDATE,
                    GLTRANSACTION.DATECHANGED = @CHANGEDATE,
                    GLTRANSACTION.CHANGEDBYID = @CHANGEAGENTID
                where GLTRANSACTION.ID in (select GLTRANSACTION.ID    
                from
                    dbo.GLTRANSACTION
                inner join
                    dbo.PLANNEDGIFTPAYOUTGLDISTRIBUTION on GLTRANSACTION.ID = PLANNEDGIFTPAYOUTGLDISTRIBUTION.GLTRANSACTIONID
                where    
                    PLANNEDGIFTPAYOUTGLDISTRIBUTION.REVENUEID = @REVENUEID and PLANNEDGIFTPAYOUTGLDISTRIBUTION.OUTDATED = 0);

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

        update
          dbo.GLTRANSACTION
        set
          GLTRANSACTION.POSTDATE = @POSTDATE,
          GLTRANSACTION.DATECHANGED = @CHANGEDATE,
          GLTRANSACTION.CHANGEDBYID = @CHANGEAGENTID
        where GLTRANSACTION.ID in (select GLTRANSACTION.ID  
        from
          dbo.GLTRANSACTION
        inner join
          dbo.PLANNEDGIFTPAYOUTGLDISTRIBUTION on GLTRANSACTION.REVERSEDGLTRANSACTIONID = PLANNEDGIFTPAYOUTGLDISTRIBUTION.GLTRANSACTIONID
        where
          PLANNEDGIFTPAYOUTGLDISTRIBUTION.REVENUEID = @REVENUEID and GLTRANSACTION.POSTSTATUSCODE = 1);

                --Save the snapshot

                --exec dbo.USP_ADJUSTMENTHISTORY_PLANNEDGIFTPAYOUT_SAVESNAPSHOT @ADJUSTMENTID, @CHANGEAGENTID;


            end

            if exists (select 1 from dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT where ID = @ADJUSTMENTID)
            begin
                update LI set
                    FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID
                from
                    dbo.FINANCIALTRANSACTIONLINEITEM LI
                    inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
                    inner join dbo.FINANCIALTRANSACTION PARENT on PARENT.ID = FT.PARENTID
                where
                    PARENT.ID = @REVENUEID
                    and FT.TYPECODE = 26
                    and LI.POSTSTATUSCODE = 1
                    and LI.DELETEDON is null

            end

            declare @Error nvarchar(255) = ''
            if @@ROWCOUNT > 0 
                set @Error = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE(@POSTDATE)

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