USP_ADJUSTMENTHISTORY_PROPERTY_FIXFORDELETE

            CREATE procedure dbo.USP_ADJUSTMENTHISTORY_PROPERTY_FIXFORDELETE
            (
                @PROPERTYDETAILID uniqueidentifier,
                @UNSOLD bit,
                @CHANGEAGENTID uniqueidentifier,
                @CHANGEDATE datetime = null,
                @REVERSALADJUSTMENTHISTORYPROPERTYID uniqueidentifier = null output
            )
            as 
            set nocount on;

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

            declare @UNPOSTEDPROPERTYDETAILADJUSTMENTID uniqueidentifier;
            declare @UNPOSTEDADJUSTMENTHISTORYPROPERTYID uniqueidentifier;
            declare @LASTPOSTEDPROPERTYDETAILADJUSTMENTID uniqueidentifier;
            declare @LASTPOSTEDADJUSTMENTHISTORYPROPERTYID uniqueidentifier;

            /*make sure the sold property information is posted*/
            if exists(select top 1 ID from dbo.PROPERTYDETAIL where ID = @PROPERTYDETAILID and SALEPOSTSTATUSCODE = 0)
            begin
                /*determine if there is an unposted adjustment for this property */
                select 
                    @UNPOSTEDPROPERTYDETAILADJUSTMENTID = ID
                from dbo.PROPERTYDETAILADJUSTMENT
                where PROPERTYDETAILID = @PROPERTYDETAILID and POSTSTATUSCODE = 1;
                select 
                    @UNPOSTEDADJUSTMENTHISTORYPROPERTYID = ID
                from dbo.ADJUSTMENTHISTORYPROPERTY
                where PROPERTYDETAILADJUSTMENTID = @UNPOSTEDPROPERTYDETAILADJUSTMENTID;

                /*get the most recent posted adjustment so we can write out GL history for it*/
                select top 1
                    @LASTPOSTEDPROPERTYDETAILADJUSTMENTID = ID
                from dbo.PROPERTYDETAILADJUSTMENT
                where PROPERTYDETAILID = @PROPERTYDETAILID and POSTSTATUSCODE = 0
                order by TSLONG desc;

                /*if there is an unposted adjustment, the last posted adjustment will already have distribution information in its history*/
                /*so do nothing*/
                /*write the current GL information to the history for the last posted adjustment only if there was not an adjustment after it*/

                if @UNPOSTEDPROPERTYDETAILADJUSTMENTID is null and not @LASTPOSTEDPROPERTYDETAILADJUSTMENTID is null
                begin
                    select @LASTPOSTEDADJUSTMENTHISTORYPROPERTYID = ID
                    from dbo.ADJUSTMENTHISTORYPROPERTY
                    where PROPERTYDETAILADJUSTMENTID = @LASTPOSTEDPROPERTYDETAILADJUSTMENTID;

                    if not @LASTPOSTEDADJUSTMENTHISTORYPROPERTYID is null
                        insert into dbo.ADJUSTMENTHISTORYPROPERTYDISTRIBUTION(ADJUSTMENTHISTORYPROPERTYID, TYPECODE, TRANSACTIONTYPECODE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
                            select
                                @LASTPOSTEDADJUSTMENTHISTORYPROPERTYID,
                                1, --'Adjustment'

                                TRANSACTIONTYPECODE,
                                ACCOUNT,
                                PROJECT,
                                '', --The function does not return a reference

                                AMOUNT,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CHANGEDATE,
                                @CHANGEDATE,
                                TRANSACTIONAMOUNT,
                                ORGANIZATIONAMOUNT,
                                BASECURRENCYID,
                                TRANSACTIONCURRENCYID
                            from dbo.UFN_REVENUE_GETPROPERTYDETAILGLDISTRIBUTION(@PROPERTYDETAILID);
                end

                /*log the history header for the reversal*/
                set @REVERSALADJUSTMENTHISTORYPROPERTYID = newid();

                insert into dbo.ADJUSTMENTHISTORYPROPERTY(ID, PROPERTYDETAILADJUSTMENTID, PROPERTYDETAILIDENTIFIER, ADJUSTMENTIDENTIFIER, CONSTITUENTNAME, REVENUEDATE, ADJUSTMENTDATE, ADJUSTMENTPOSTDATE, ADJUSTMENTREASON, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    select
                        @REVERSALADJUSTMENTHISTORYPROPERTYID,
                        null,
                        cast(@PROPERTYDETAILID as nvarchar(36)),
                        cast(newid() as nvarchar(36)), --This is just something to group by

                        CONSTITUENT.NAME,
                        REVENUE.DATE, --This is the postdate of the reversal that got logged, so we'll use it too

                        @CHANGEDATE,
                        dbo.UFN_DATE_GETEARLIESTTIME(@CHANGEDATE), --The adjustment post date needs to be the date of the deletion

                        '',
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE
                    from dbo.REVENUEPAYMENTMETHOD
                    inner join dbo.REVENUE on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
                    where REVENUEPAYMENTMETHOD.ID = @PROPERTYDETAILID;

                insert into dbo.ADJUSTMENTHISTORYPROPERTYDETAIL(ID, ADJUSTMENTHISTORYPROPERTYID, ADJUSTEDFIELD,ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE)
                    select
                        newid(), 
                        @REVERSALADJUSTMENTHISTORYPROPERTYID, 
                        case when @UNSOLD = 1 then 'Unsold' else 'Deleted' end,
                        '', 
                        '', 
                        @CHANGEAGENTID, 
                        @CHANGEAGENTID, 
                        @CHANGEDATE, 
                        @CHANGEDATE,
                        '',
                        '',
                        '',
                        '';

                /*if there's not an unposted adjustment, the reversal for the deletion will be the calculated GL information*/
                if @UNPOSTEDPROPERTYDETAILADJUSTMENTID is null
                    insert into dbo.ADJUSTMENTHISTORYPROPERTYDISTRIBUTION(ADJUSTMENTHISTORYPROPERTYID, TYPECODE, TRANSACTIONTYPECODE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
                        select
                            @REVERSALADJUSTMENTHISTORYPROPERTYID,
                            0, --'Reversal'

                            case TRANSACTIONTYPECODE when 0 then 1 else 0 end,
                            ACCOUNT,
                            PROJECT,
                            '', --The function does not return a reference

                            AMOUNT,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CHANGEDATE,
                            @CHANGEDATE,
                            TRANSACTIONAMOUNT,
                            ORGANIZATIONAMOUNT,
                            BASECURRENCYID,
                            TRANSACTIONCURRENCYID
                        from dbo.UFN_REVENUE_GETPROPERTYDETAILGLDISTRIBUTION(@PROPERTYDETAILID);
                else /*otherwise, the reversal will be the reversal from the unposted adjustment */
                    update dbo.ADJUSTMENTHISTORYPROPERTYDISTRIBUTION
                        set ADJUSTMENTHISTORYPROPERTYID = @REVERSALADJUSTMENTHISTORYPROPERTYID
                    where ADJUSTMENTHISTORYPROPERTYID = @UNPOSTEDADJUSTMENTHISTORYPROPERTYID and TYPECODE = 0 --We only want the reversals


                /*if it exists, delete teh unpsoted adjustment history. Since this will never post, it will never interact with the GL
                and we don't need to remember it.*/

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

                if not @CHANGEAGENTID is null
                    set CONTEXT_INFO @CHANGEAGENTID;

                if not @UNPOSTEDPROPERTYDETAILADJUSTMENTID is null /*ADJUSTMENTPROPERTYHISTORY.PROPERTYDETAILADJUSTMENTID is allowed to be null, so check first*/
                    delete from dbo.ADJUSTMENTHISTORYPROPERTY where PROPERTYDETAILADJUSTMENTID = @UNPOSTEDPROPERTYDETAILADJUSTMENTID

                if not @CONTEXTCACHE is null
                    set CONTEXT_INFO @CONTEXTCACHE;
            end