USP_SAVE_UNREALIZEDGAINLOSSADJUSTMENT

            CREATE procedure dbo.USP_SAVE_UNREALIZEDGAINLOSSADJUSTMENT
            (
                @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,
                @ADJUSTMENTCODE tinyint = 0
            )
            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();

            --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 gain/loss posted?

            if dbo.UFN_REVENUE_ISUNREALIZEDGAINLOSSPOSTED(@REVENUEID) = 0
                raiserror('You cannot adjust unposted gain/loss', 13, 1);

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

            exec dbo.USP_CREATEINITIALADJUSTMENTHISTORY @REVENUEID, @CHANGEAGENTID

            --Update unposted adjustment if existing

            update 
                dbo.UNREALIZEDGAINLOSSADJUSTMENT
            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 
                REVENUEID = @REVENUEID 
                and POSTSTATUSCODE <> 0;

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

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


                --Save the snapshot before deleting the GL information

                exec dbo.USP_ADJUSTMENTHISTORY_REVENUE_SAVESNAPSHOT @ADJUSTMENTID, @CHANGEAGENTID, @ISNEWREVENUE;

                --Log reversals in the GLTRANSACTION table

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

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

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


                --Save the snapshot

                exec dbo.USP_ADJUSTMENTHISTORY_REVENUE_SAVESNAPSHOT @ADJUSTMENTID, @CHANGEAGENTID;

            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)