USP_SAVE_BENEFITADJUSTMENT

            CREATE procedure dbo.USP_SAVE_BENEFITADJUSTMENT
            (
                @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;
            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.BENEFITADJUSTMENT
            where REVENUEID = @REVENUEID and POSTSTATUSCODE <> 0 and BENEFITTYPECODE = 1;

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

            --JamesWill WI197067 2012-03-08 Ensure that the date used does not have a timestamp

            if not @DATE is null
                set @DATE = dbo.UFN_DATE_GETEARLIESTTIME(@DATE);

            --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 benefit', 13, 1)

            --kwb Create FINANCIALTRANSACTIONLINEITEMADJUSTMENT records for original write-offs that will be adjusted

            exec dbo.USP_CREATEINITIALADJUSTMENTHISTORY @REVENUEID, @CHANGEAGENTID

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

            --Update unposted adjustment if existing

            if @ADJUSTMENTID is not null and 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 if @ADJUSTMENTID is not null
                insert into dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT (
                    ID
                    ,ADJUSTMENTREASONCODEID
                    ,REASON
                    ,CONSTITUENTID
                    ,DATE
                    ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                values (@ADJUSTMENTID
                    ,@ADJUSTMENTREASONCODEID
                    ,@ADJUSTMENTREASON
                    ,@CONSTITUENTID
                    ,@DATE
                    ,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);

            update dbo.BENEFITADJUSTMENT
            set DATE = @DATE, 
                POSTDATE = @POSTDATE,
                REASON = @ADJUSTMENTREASON,
                REASONCODEID = @ADJUSTMENTREASONCODEID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CHANGEDATE,
                POSTSTATUSCODE = @POSTSTATUSCODE
            where ID = @ADJUSTMENTID;

            if @@ROWCOUNT = 0
            begin
                declare @TRANSACTIONAMOUNT money;
                declare @AMOUNT money;
                declare @ORGANIZATIONAMOUNT money;
                declare @TRANSACTIONCURRENCYID uniqueidentifier;
                declare @BASECURRENCYID uniqueidentifier;
                declare @BASEEXCHANGERATEID uniqueidentifier;
                declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

                select
                    @TRANSACTIONAMOUNT = sum(RB.TRANSACTIONTOTALVALUE),
                    @AMOUNT = sum(RB.TOTALVALUE),
                    @ORGANIZATIONAMOUNT = sum(RB.ORGANIZATIONTOTALVALUE),
                    @TRANSACTIONCURRENCYID = RB.TRANSACTIONCURRENCYID,
                    @BASECURRENCYID = RB.BASECURRENCYID,
                    @BASEEXCHANGERATEID = RB.BASEEXCHANGERATEID,
                    @ORGANIZATIONEXCHANGERATEID = RB.ORGANIZATIONEXCHANGERATEID
                from dbo.REVENUEBENEFIT RB
                where RB.REVENUEID = @REVENUEID
                group by RB.TRANSACTIONCURRENCYID, RB.BASECURRENCYID, RB.BASEEXCHANGERATEID, RB.ORGANIZATIONEXCHANGERATEID;

                --Log Adjustment if new

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

                --Save the snapshot before deleting the GL information

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


                --Log reversals in the GLTRANSACTION table                work item 55993 - added postdate 

                if @POSTSTATUSCODE <> 2
                  exec dbo.USP_GLTRANSACTION_ADDBENEFITREVERSALS @REVENUEID, @CHANGEAGENTID, @CHANGEDATE, @POSTDATE

            end
            else /*make sure @ADJUSTMENTID gets set to the correct value*/
            begin
                -- 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.BENEFITGLDISTRIBUTION on GLTRANSACTION.ID = BENEFITGLDISTRIBUTION.GLTRANSACTIONID
                where    
                    BENEFITGLDISTRIBUTION.REVENUEID = @REVENUEID and BENEFITGLDISTRIBUTION.OUTDATED = 0 and BENEFITTYPECODE = 1);

        -- 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.BENEFITGLDISTRIBUTION on GLTRANSACTION.REVERSEDGLTRANSACTIONID = BENEFITGLDISTRIBUTION.GLTRANSACTIONID
        where
          BENEFITGLDISTRIBUTION.REVENUEID = @REVENUEID and GLTRANSACTION.POSTSTATUSCODE = 1 and BENEFITTYPECODE = 1);                

            end

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

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