USP_SAVE_BENEFITADJUSTMENT_2

            CREATE procedure dbo.USP_SAVE_BENEFITADJUSTMENT_2
            (
                @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,
                @TOTALBENEFITS xml = null,
                @BENEFITTYPECODE tinyint = null
            )
            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;

            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

            --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);

            --benefit line items to reverse

            declare @LINEITEMS UDT_GENERICID
            insert into @LINEITEMS (ID)
            select distinct
                LI.ID
            from
                dbo.FINANCIALTRANSACTIONLINEITEM LI
                inner join dbo.REVENUEBENEFIT_EXT RBX on RBX.ID = LI.ID
            where
                LI.FINANCIALTRANSACTIONID = @REVENUEID
                and LI.TYPECODE = 3
                and LI.DELETEDON is null
                and ((@BENEFITTYPECODE is null and RBX.BENEFITTYPECODE in (1, 2, 3))
                    or (RBX.BENEFITTYPECODE = @BENEFITTYPECODE))

            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;

            end
            else /*make sure @ADJUSTMENTID gets set to the correct value*/
            begin
                -- Update the post date on the going forward line items and their reversals

                update 
                    LI
                set
                    LI.POSTDATE = @POSTDATE,
                    LI.POSTSTATUSCODE = @FTMPOSTSTATUSCODE,
                    LI.DATECHANGED = @CHANGEDATE,
                    LI.CHANGEDBYID = @CHANGEAGENTID
                from
                    @LINEITEMS L
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = L.ID or LI.REVERSEDLINEITEMID = L.ID
                where
                    LI.FINANCIALTRANSACTIONID = @REVENUEID
                    and LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID        
                    and LI.POSTSTATUSCODE <> 2

                update 
                    JE
                set
                    JE.POSTDATE = @POSTDATE,
                    JE.DATECHANGED = @CHANGEDATE,
                    JE.CHANGEDBYID = @CHANGEAGENTID
                from
                    @LINEITEMS L
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = L.ID or LI.REVERSEDLINEITEMID = L.ID
                    inner join dbo.JOURNALENTRY JE on JE.FINANCIALTRANSACTIONLINEITEMID = LI.ID
                where
                    LI.FINANCIALTRANSACTIONID = @REVENUEID
                    and LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID        
                    and LI.POSTSTATUSCODE <> 2

                if @BENEFITTYPECODE is null
                begin
                    delete from dbo.JOURNALENTRY where FINANCIALTRANSACTIONLINEITEMID in
                    (select
                        LI.ID
                    from
                        @LINEITEMS L
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = L.ID)
                end

            end

            --create reversals if none exist for this adjustment

            if not exists (
                select
                    1
                from
                    @LINEITEMS L
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.REVERSEDLINEITEMID = L.ID
                where
                    LI.TYPECODE = 1
                    and LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID)
            begin
                exec dbo.USP_SAVE_REVERSAL_LINEITEM @LINEITEMS, @ADJUSTMENTID, @CHANGEAGENTID, @CHANGEDATE, @POSTDATE, @FTMPOSTSTATUSCODE

                exec dbo.USP_SAVE_HISTORICAL_LINEITEM @LINEITEMS, @ADJUSTMENTID, @CHANGEAGENTID, @CHANGEDATE, @POSTDATE, @FTMPOSTSTATUSCODE
            end


            if @BENEFITTYPECODE is null
            begin
                exec dbo.USP_REVENUE_GETBENEFITS_4_UPDATEFROMXML @REVENUEID, @TOTALBENEFITS, @CHANGEAGENTID, @CHANGEDATE, @ADJUSTMENTID

                if @POSTSTATUSCODE <> 2
                    exec USP_SAVE_BENEFITGLDISTRIBUTION_FINANCIALTRANSACTION @REVENUEID, @CHANGEAGENTID, @CHANGEDATE
            end            

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

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