USP_RECORDOPERATION_SELLPROPERTYUNDO

                CREATE procedure dbo.USP_RECORDOPERATION_SELLPROPERTYUNDO
                (
                    @ID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier
                )
                as begin

                    declare @ADJUSTMENTHISTORYPROPERTYID uniqueidentifier;

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

                    declare @CURRENTDATE datetime;
                    set @CURRENTDATE = getdate();                    

                    begin try
                        if (select SALEPOSTSTATUSCODE from dbo.PROPERTYDETAIL where ID = @ID) = 0
                            exec dbo.USP_ADJUSTMENTHISTORY_PROPERTY_FIXFORDELETE @ID, 1, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTHISTORYPROPERTYID output;

                        /* If property details have been posted but not already adjusted */
                        if exists (select 1 from dbo.PROPERTYDETAIL where PROPERTYDETAIL.ID = @ID and PROPERTYDETAIL.SALEPOSTSTATUSCODE = 0)
                            and not exists (select 1 from dbo.PROPERTYDETAILADJUSTMENT where PROPERTYDETAILID = @ID and POSTSTATUSCODE = 1)
                            begin
                                declare @POSTDATE datetime;
                                declare @ERRORMESSAGE nvarchar(255);
                                select top 1 @POSTDATE = POSTDATE from dbo.PROPERTYDETAILADJUSTMENT where PROPERTYDETAILID = @ID and POSTSTATUSCODE = 0 order by DATEADDED desc;
                                if @POSTDATE is null
                                    select @POSTDATE = SALEPOSTDATE from dbo.PROPERTYDETAIL where ID = @ID;
                                set @ERRORMESSAGE = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE(@POSTDATE);
                                if @ERRORMESSAGE <> ''
                                    raiserror(@ERRORMESSAGE, 13, 1);

                                --Log reversals in the GLTRANSACTION table

                                exec dbo.USP_GLTRANSACTION_ADDPROPERTYDETAILREVERSALS @ID, @CHANGEAGENTID, @CURRENTDATE, @POSTDATE;
                            end
                        else --Update the post date of the existing property detail reversal rows to today's date

                            begin
                                update dbo.GLTRANSACTION
                                set 
                                    POSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE),
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CURRENTDATE
                                where ID in (
                                    select
                                        REVERSAL.ID
                                    from
                                        dbo.PROPERTYDETAILGLDISTRIBUTION
                                    inner join 
                                        dbo.GLTRANSACTION on PROPERTYDETAILGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
                                    inner join 
                                        dbo.GLTRANSACTION REVERSAL on GLTRANSACTION.ID = REVERSAL.REVERSEDGLTRANSACTIONID
                                    where 
                                        PROPERTYDETAILGLDISTRIBUTION.PROPERTYDETAILID = @ID
                                    and 
                                        REVERSAL.POSTSTATUSCODE = 1);
                            end

                        --Update the new ADJUSTMENTHISTORYPROPERTY record with one of the newly created GLTRANSACTION reversal IDs

                        update dbo.ADJUSTMENTHISTORYPROPERTY
                        set GLTRANSACTIONID = (
                            select top 1 REVERSAL.ID from dbo.PROPERTYDETAILGLDISTRIBUTION
                            inner join dbo.GLTRANSACTION on PROPERTYDETAILGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
                            inner join dbo.GLTRANSACTION REVERSAL on GLTRANSACTION.ID = REVERSAL.REVERSEDGLTRANSACTIONID
                            where PROPERTYDETAILGLDISTRIBUTION.PROPERTYDETAILID = @ID
                            order by REVERSAL.DATEADDED desc)
                        where ID = @ADJUSTMENTHISTORYPROPERTYID;

                        /* Delete rows from Property Detail GL Distribution */
                        --Cache CONTEXT INFO

                        declare @contextCache varbinary(128);
                        set @contextCache = CONTEXT_INFO();

                        if not @CHANGEAGENTID is null
                            set CONTEXT_INFO @CHANGEAGENTID;                        

                        delete from dbo.PROPERTYDETAILGLDISTRIBUTION where PROPERTYDETAILID = @ID and OUTDATED = 0;

                        if exists(select 1 from dbo.FINANCIALTRANSACTION where ID = @ID and POSTSTATUSCODE != 2)
                        begin
                            delete from dbo.FINANCIALTRANSACTIONLINEITEM
                            where
                                FINANCIALTRANSACTIONID = @ID

                            update dbo.FINANCIALTRANSACTION set
                                [TRANSACTIONAMOUNT] = 0,
                                [BASEAMOUNT] = 0,
                                [ORGAMOUNT] = 0,
                                [POSTDATE] = null,
                                [POSTSTATUSCODE] = 3,
                                [CHANGEDBYID] = @CHANGEAGENTID,
                                [DATECHANGED] = @CURRENTDATE
                            where
                                ID = @ID
                        end

                        --reset CONTEXT_INFO to previous value

                        if not @contextCache is null
                            set CONTEXT_INFO @contextCache;

                        /* If any adjustment has occurred, log a new line item adjustment and link */
                        if exists (select 1 from dbo.ADJUSTMENTHISTORYPROPERTY where PROPERTYDETAILIDENTIFIER = @ID)
                        begin

                            declare @LIAID uniqueidentifier = newid()
                            insert into dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT (
                                ID
                                ,ADJUSTMENTREASONCODEID
                                ,REASON
                                ,DATE
                                ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                            select
                                @LIAID,
                                ADJUSTMENTREASONCODEID,
                                ADJUSTMENTREASON,
                                ADJUSTMENTDATE,
                                @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                            from dbo.ADJUSTMENTHISTORYPROPERTY where ID = @ADJUSTMENTHISTORYPROPERTYID

                            update dbo.FINANCIALTRANSACTIONLINEITEM
                                set FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @LIAID
                            where FINANCIALTRANSACTIONID = @ID and TYPECODE = 1 and POSTSTATUSCODE = 1

                            update ORIGINALS
                                set DELETEDON = @CURRENTDATE
                            from
                                dbo.FINANCIALTRANSACTIONLINEITEM ORIGINALS
                                inner join dbo.FINANCIALTRANSACTIONLINEITEM REVERSALS on REVERSALS.REVERSEDLINEITEMID = ORIGINALS.ID
                            where
                                REVERSALS.TYPECODE = 1 and ORIGINALS.FINANCIALTRANSACTIONID = @ID and ORIGINALS.POSTSTATUSCODE = 2

                        end

                        update dbo.FINANCIALTRANSACTION set
                                [TRANSACTIONAMOUNT] = 0,
                                [BASEAMOUNT] = 0,
                                [ORGAMOUNT] = 0,
                                [POSTDATE] = null,
                                [POSTSTATUSCODE] = 3,
                                [CHANGEDBYID] = @CHANGEAGENTID,
                                [DATECHANGED] = @CURRENTDATE
                            where
                                ID = @ID

                        update dbo.PROPERTYDETAIL_EXT set
                            [SALEDATE] = null,
                            [BROKERFEE] = 0,
                            [TRANSACTIONBROKERFEE] = 0,
                            [ORGANIZATIONBROKERFEE] = 0,
                            [CHANGEDBYID] = @CHANGEAGENTID,
                            [DATECHANGED] = @CURRENTDATE
                        where
                            PROPERTYDETAIL_EXT.ID = @ID

                    end try
                    begin catch
                        exec dbo.USP_RAISE_ERROR
                        return 1
                    end catch

                    return 0;
                end