USP_SAVE_PROPERTYDETAILADJUSTMENT

            CREATE procedure dbo.USP_SAVE_PROPERTYDETAILADJUSTMENT
            (
                @PROPERTYDETAILID uniqueidentifier = null,
                @PROPERTYDETAILADJUSTMENTID uniqueidentifier = null output,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null,
                @DATE datetime = null,
                @POSTDATE datetime = null,
                @ADJUSTMENTREASON nvarchar(300) = null,
                @ADJUSTMENTREASONCODEID uniqueidentifier = null,
                @ADJUSTMENTPOSTSTATUSCODE tinyint = 1
            )
            with execute as owner
            as
            /*call this procedure before making changes to the property detail tables.*/
            set nocount on;

            declare @CURRENTDATE datetime;
            declare @PROPERTYDETAILPOSTDATE datetime;
            declare @CONSTITUENTID uniqueidentifier;

            --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 property detail posted?

            if (select count(ID) from dbo.PROPERTYDETAIL
                where ID = @PROPERTYDETAILID and SALEPOSTSTATUSCODE = 0) = 0
                raiserror('You cannot adjust an unposted sold property', 13, 1)

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

            exec dbo.USP_CREATEINITIALADJUSTMENTHISTORY @PROPERTYDETAILID, @CHANGEAGENTID

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

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

            select @PROPERTYDETAILADJUSTMENTID = ID
            from dbo.PROPERTYDETAILADJUSTMENT
            where PROPERTYDETAILID = @PROPERTYDETAILID and POSTSTATUSCODE <> 0;

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

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

            --Update unposted adjustment if existing

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

            update dbo.PROPERTYDETAILADJUSTMENT
            set DATE = @DATE, 
                POSTDATE = @POSTDATE,
                REASON = @ADJUSTMENTREASON,
                REASONCODEID = @ADJUSTMENTREASONCODEID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CHANGEDATE,
                POSTSTATUSCODE = @ADJUSTMENTPOSTSTATUSCODE
            where ID = @PROPERTYDETAILADJUSTMENTID

            if @@ROWCOUNT = 0
            begin

                --Log Adjustment if new

                insert into dbo.PROPERTYDETAILADJUSTMENT(
                    ID, 
                    PROPERTYDETAILID, 
                    PREVIOUSAMOUNT, 
                    DATE, 
                    POSTDATE, 
                    POSTSTATUSCODE, 
                    REASON, 
                    REASONCODEID, 
                    ADDEDBYID, 
                    CHANGEDBYID, 
                    DATEADDED, 
                    DATECHANGED,
                    TRANSACTIONPREVIOUSAMOUNT,
                    ORGANIZATIONPREVIOUSAMOUNT,
                    TRANSACTIONCURRENCYID,
                    BASECURRENCYID,
                    BASEEXCHANGERATEID,
                    ORGANIZATIONEXCHANGERATEID
                )
                select 
                    @PROPERTYDETAILADJUSTMENTID, 
                    ID, 
                    SALEAMOUNT, 
                    @DATE, 
                    @POSTDATE, 
                    @ADJUSTMENTPOSTSTATUSCODE, 
                    @ADJUSTMENTREASON, 
                    @ADJUSTMENTREASONCODEID, 
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CHANGEDATE, 
                    @CHANGEDATE,
                    TRANSACTIONSALEAMOUNT,
                    ORGANIZATIONSALEAMOUNT,
                    TRANSACTIONCURRENCYID,
                    BASECURRENCYID,
                    BASEEXCHANGERATEID,
                    ORGANIZATIONEXCHANGERATEID
                from dbo.PROPERTYDETAIL
                where ID = @PROPERTYDETAILID;

                --Get the postdate for REVERSAL from the last adjustment posted

                select TOP 1 @PROPERTYDETAILPOSTDATE = POSTDATE from dbo.PROPERTYDETAILADJUSTMENT where PROPERTYDETAILID = @PROPERTYDETAILID and POSTSTATUSCODE = 0 order by DATEADDED desc

                --If there are no posted adjustments then get the postdate for REVERSAL from the REVENUE record

                if @PROPERTYDETAILPOSTDATE is null
                    select @PROPERTYDETAILPOSTDATE = SALEPOSTDATE from dbo.PROPERTYDETAIL where ID = @PROPERTYDETAILID;    

                --Save the snapshot before deleting the GL information

                exec dbo.USP_ADJUSTMENTHISTORY_PROPERTY_SAVESNAPSHOT @PROPERTYDETAILADJUSTMENTID, @CHANGEAGENTID;

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

                exec dbo.USP_GLTRANSACTION_ADDPROPERTYDETAILREVERSALS @PROPERTYDETAILID, @CHANGEAGENTID, @CHANGEDATE, @POSTDATE,@ADJUSTMENTPOSTSTATUSCODE;

            end
            else /*make sure @PROPERTYDETAILADJUSTMENTID gets set to the correct value */
            begin

                -- Update the post date on the GL transaction records

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

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


        update
          dbo.GLTRANSACTION
        set
          GLTRANSACTION.POSTSTATUSCODE = @ADJUSTMENTPOSTSTATUSCODE,
          GLTRANSACTION.POSTDATE = @POSTDATE,
          GLTRANSACTION.DATECHANGED = @CHANGEDATE,
          GLTRANSACTION.CHANGEDBYID = @CHANGEAGENTID
        where ID in
          (select  GLTRANSACTION.ID from dbo.GLTRANSACTION
        inner join 
          dbo.PROPERTYDETAILGLDISTRIBUTION on GLTRANSACTION.REVERSEDGLTRANSACTIONID = PROPERTYDETAILGLDISTRIBUTION.GLTRANSACTIONID
        where
          PROPERTYDETAILGLDISTRIBUTION.PROPERTYDETAILID = @PROPERTYDETAILID and GLTRANSACTION.POSTSTATUSCODE > 0); 

                update dbo.FINANCIALTRANSACTIONLINEITEM set
                    POSTDATE = @POSTDATE
                    ,POSTSTATUSCODE = case @ADJUSTMENTPOSTSTATUSCODE when 2 then 3 else 1 end
                    ,DATECHANGED = @CHANGEDATE
                    ,CHANGEDBYID = @CHANGEAGENTID
                where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @PROPERTYDETAILID and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE != 2

                --Save the snapshot before deleting the GL information

                exec dbo.USP_ADJUSTMENTHISTORY_PROPERTY_SAVESNAPSHOT @PROPERTYDETAILADJUSTMENTID, @CHANGEAGENTID;
            end

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

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