USP_DATAFORMTEMPLATE_EDIT_TRANSACTIONSELLPROPERTYADJUST_3

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_TRANSACTIONSELLPROPERTYADJUST_3
                (
                    @ID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier = null,
                    @SALEDATE datetime,
                    @SALEAMOUNT money,
                    @BROKERFEE money,
                    @ADJUSTMENTDATE datetime,
                    @ADJUSTMENTPOSTDATE datetime,
                    @ADJUSTMENTREASON nvarchar(300),
                    @POSTSTATUSCODE tinyint,
                    @POSTDATE datetime,
                    @ADJUSTMENTREASONCODEID uniqueidentifier,
                    @BASEEXCHANGERATEID uniqueidentifier,
                    @EXCHANGERATE decimal(20,8),
                    @CURRENTAPPUSERID uniqueidentifier = null
                )
                as

                set nocount on;

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

                declare @CHANGEDATE datetime;
                declare @PROPERTYDETAILADJUSTMENTID uniqueidentifier;
                set @CHANGEDATE = GetDate();

                declare @REVENUEPAYMETHODID uniqueidentifier;

        declare @ADJUSTMENTPOSTSTATUSCODE tinyint;
        select top 1 @ADJUSTMENTPOSTSTATUSCODE = ADJUSTMENT.POSTSTATUSCODE
        from dbo.ADJUSTMENT
        where REVENUEID = @ID and POSTSTATUSCODE <> 0
        order by DATEADDED desc;

        if @ADJUSTMENTPOSTSTATUSCODE is null
          set @ADJUSTMENTPOSTSTATUSCODE = 1

                begin try
                    declare
                        @ORGANIZATIONSALEAMOUNT money,
                        @ORGANIZATIONBROKERFEE money,
                        @BASESALEAMOUNT money,
                        @BASEBROKERFEE money,
                        @BASECURRENCYID uniqueidentifier,
                        @ORGANIZATIONEXCHANGERATEID uniqueidentifier,
                        @TRANSACTIONCURRENCYID uniqueidentifier,
                        @ORGANIZATIONCURRENCYID uniqueidentifier;

                    declare @OLDSPOTRATEID uniqueidentifier;

                    select
                        @TRANSACTIONCURRENCYID = PROPERTYDETAIL.TRANSACTIONCURRENCYID,
                        @BASECURRENCYID = PROPERTYDETAIL.BASECURRENCYID,
                        @OLDSPOTRATEID = 
                            case
                                when CURRENCYEXCHANGERATE.TYPECODE = 2
                                    and not (
                                        @BASEEXCHANGERATEID = CURRENCYEXCHANGERATE.ID 
                                        or (
                                            @BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001' 
                                            and @EXCHANGERATE = CURRENCYEXCHANGERATE.RATE
                                        )
                                    )
                                    then CURRENCYEXCHANGERATE.ID
                                else null
                            end
                    from
                        dbo.[REVENUE] 
                        inner join dbo.[REVENUEPAYMENTMETHOD] on [REVENUEPAYMENTMETHOD].REVENUEID = REVENUE.ID
                        inner join dbo.[PROPERTYDETAIL] on REVENUEPAYMENTMETHOD.ID = PROPERTYDETAIL.ID                            
                        left join dbo.CURRENCYEXCHANGERATE on PROPERTYDETAIL.BASEEXCHANGERATEID = CURRENCYEXCHANGERATE.ID
                    where
                        [REVENUE].[ID] = @ID;

                    --If the record uses a new spot rate, create it and set the rate ID.

                    if @BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
                    begin

                        set @BASEEXCHANGERATEID = newid();

                        --Replace with call to USP_CURRENCYEXCHANGERATE_ENSURESPOTRATEEXISTS for PBI 102747 in the future

                        /*if not dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID,'911f104d-ba5f-4469-b0ae-184c879aea99') = 1
                            and not dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
                        begin
                            raiserror('User does not have the right to add a new spot rate.', 13, 1);
                            return 1;
                        end*/

                        insert into dbo.CURRENCYEXCHANGERATE
                        (
                            ID, 
                            FROMCURRENCYID,
                            TOCURRENCYID,
                            RATE,
                            ASOFDATE,
                            TYPECODE,
                            SOURCECODEID,
                            ADDEDBYID, 
                            CHANGEDBYID, 
                            DATEADDED, 
                            DATECHANGED
                        )
                        values
                        (
                            @BASEEXCHANGERATEID,
                            @TRANSACTIONCURRENCYID,
                            @BASECURRENCYID,
                            @EXCHANGERATE,
                            @SALEDATE,
                            2,
                            null,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CHANGEDATE,
                            @CHANGEDATE
                        );
                    end

                    exec dbo.USP_CURRENCY_GETCURRENCYVALUES @SALEAMOUNT, @SALEDATE, @BASECURRENCYID, @BASEEXCHANGERATEID, @TRANSACTIONCURRENCYID, @BASESALEAMOUNT output, @ORGANIZATIONCURRENCYID output, @ORGANIZATIONSALEAMOUNT output, @ORGANIZATIONEXCHANGERATEID output, 1; --Get an organization currency and exchange rate on the first call

                    exec dbo.USP_CURRENCY_GETCURRENCYVALUES @BROKERFEE, @SALEDATE, @BASECURRENCYID, @BASEEXCHANGERATEID, @TRANSACTIONCURRENCYID, @BASEBROKERFEE output, @ORGANIZATIONCURRENCYID, @ORGANIZATIONBROKERFEE output, @ORGANIZATIONEXCHANGERATEID, 0;

                    declare @ADJUST bit;
                    declare @GIFTID uniqueidentifier;
                    declare @CLEARGLDISTRIBUTION bit;

                    select top 1 
                        @REVENUEPAYMETHODID = ID
                    from REVENUEPAYMENTMETHOD where REVENUEID = @ID;

                    set @ADJUST = 0;
                    set @CLEARGLDISTRIBUTION = 0;

                    /* Check if sale amount or broker fee has changed */
                    if exists (
                        select 1
                        from dbo.PROPERTYDETAIL
                        where PROPERTYDETAIL.ID = @REVENUEPAYMETHODID
                            and (
                                PROPERTYDETAIL.TRANSACTIONSALEAMOUNT <> @SALEAMOUNT
                                or PROPERTYDETAIL.TRANSACTIONBROKERFEE <> @BROKERFEE
                                or PROPERTYDETAIL.SALEAMOUNT <> @BASESALEAMOUNT
                                or PROPERTYDETAIL.BROKERFEE <> @BASEBROKERFEE
                                or PROPERTYDETAIL.ORGANIZATIONSALEAMOUNT <> @ORGANIZATIONSALEAMOUNT
                                or PROPERTYDETAIL.ORGANIZATIONBROKERFEE <> @ORGANIZATIONBROKERFEE
                                or PROPERTYDETAIL.BASEEXCHANGERATEID <> @BASEEXCHANGERATEID
                                or PROPERTYDETAIL.ORGANIZATIONEXCHANGERATEID <> @ORGANIZATIONEXCHANGERATEID
                            )
                    )
                    begin
                        set @ADJUST = 1;
                        set @CLEARGLDISTRIBUTION = 1;        
                    end

                    /* Already adjusted */
                    if @ADJUST = 0
                        if (select COUNT(PROPERTYDETAILADJUSTMENT.ID)
                                from dbo.PROPERTYDETAILADJUSTMENT
                                where PROPERTYDETAILID = @REVENUEPAYMETHODID and POSTSTATUSCODE = 1) > 0
                            set @ADJUST = 1;

                    /* If there was a change to GL related data log an adjustment */
                    if @ADJUST = 1
                    begin
                        if @ADJUSTMENTREASONCODEID is null
                            raiserror('BBERR_ADJUSTMENTREASONCODEIDREQUIRED', 13, 1)

                        exec dbo.USP_SAVE_PROPERTYDETAILADJUSTMENT @REVENUEPAYMETHODID, @PROPERTYDETAILADJUSTMENTID output, @CHANGEAGENTID, 
                            @CHANGEDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, @ADJUSTMENTREASONCODEID, @ADJUSTMENTPOSTSTATUSCODE;
                    end

                    set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();       

                    if not (@SALEAMOUNT = 0 OR @SALEDATE is not null)
                        raiserror ('CK_PROPERTYDETAIL_SALEAMOUNT', 16, 1);

                    if not (@BASEBROKERFEE = 0 OR @SALEDATE is not null)
                        raiserror ('CK_PROPERTYDETAIL_BROKERFEE', 16, 1);

                    if not (@POSTDATE IS NULL OR @SALEDATE is not null)
                        raiserror ('CK_PROPERTYDETAIL_SALEPOSTDATE', 16, 1);

                    if not (isnull(@POSTSTATUSCODE,((2))) = 2 OR isnull(@POSTSTATUSCODE,((2))) = 1 OR @SALEDATE is not null)
                        raiserror ('CK_PROPERTYDETAIL_VALIDSALEPOSTSTATUSCODE', 16, 1);

                    if not ((@SALEDATE is null) or ((@POSTDATE is not null) or (@POSTSTATUSCODE = 2)))
                        raiserror ('CK_PROPERTYDETAIL_SALEPOSTDATE_REQUIRED', 16, 1);

                    if not ((@SALEDATE is null and @BASESALEAMOUNT = 0 and @BASEBROKERFEE = 0 and @POSTDATE is null and (isnull(@POSTSTATUSCODE,2) = 2)OR isnull(@POSTSTATUSCODE, 2) = 1) or (not @SALEDATE is null))
                        raiserror ('CK_PROPERTYDETAIL_SOLDORUNSOLD', 16, 1);

                    if not (dbo.UFN_PROPERTYDETAIL_VALIDFORPAYMENTTYPE(@REVENUEPAYMETHODID) = 1)
                        raiserror ('CK_PROPERTYDETAIL_VALIDFORPAYMENTTYPE', 16, 1);

                    select @POSTSTATUSCODE = case A.POSTSTATUSCODE when 2 then 3 else 1 end
                    from dbo.PROPERTYDETAILADJUSTMENT A
                    where A.ID = @PROPERTYDETAILADJUSTMENTID;

                    update dbo.FINANCIALTRANSACTION set
                        TRANSACTIONAMOUNT = @SALEAMOUNT
                        ,BASEAMOUNT = @BASESALEAMOUNT
                        ,ORGAMOUNT = @ORGANIZATIONSALEAMOUNT
                        ,BASEEXCHANGERATEID = @BASEEXCHANGERATEID
                        ,ORGEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID
                        ,CHANGEDBYID= @CHANGEAGENTID
                        ,DATECHANGED= @CHANGEDATE
                    where ID = @REVENUEPAYMETHODID;

                    update dbo.PROPERTYDETAIL_EXT set
                        SALEDATE = @SALEDATE
                        ,BROKERFEE = @BROKERFEE
                        ,ORGANIZATIONBROKERFEE = @ORGANIZATIONBROKERFEE
                        ,TRANSACTIONBROKERFEE = @BASEBROKERFEE
                        ,ISNEGATIVE = case when @BASESALEAMOUNT < 0 then 1 else 0 end
                        ,CHANGEDBYID= @CHANGEAGENTID
                        ,DATECHANGED= @CHANGEDATE
                    where ID = @REVENUEPAYMETHODID;

                    update LI set
                        TRANSACTIONAMOUNT = abs(@SALEAMOUNT) + (case when @BROKERFEE > 0 then @BROKERFEE else 0 end)
                        ,BASEAMOUNT = abs(@BASESALEAMOUNT) + (case when @BASEBROKERFEE > 0 then @BASEBROKERFEE else 0 end)
                        ,ORGAMOUNT = abs(@ORGANIZATIONSALEAMOUNT) + (case when @ORGANIZATIONBROKERFEE > 0 then @ORGANIZATIONBROKERFEE else 0 end)
                        ,POSTDATE = case when @POSTSTATUSCODE = 1 then @ADJUSTMENTPOSTDATE else null end
                        ,POSTSTATUSCODE = @POSTSTATUSCODE
                        ,CHANGEDBYID= @CHANGEAGENTID
                        ,DATECHANGED= @CHANGEDATE
                    from dbo.FINANCIALTRANSACTIONLINEITEM LI
                    where LI.FINANCIALTRANSACTIONID = @REVENUEPAYMETHODID
                        and LI.TYPECODE = 98 and DELETEDON is null and LI.POSTSTATUSCODE != 2;

                    -- if the sale amount or broker fee has changed, clear any user-defined gl distributions for this record

                    if @CLEARGLDISTRIBUTION = 1
                    begin
                        --Clear GL

                        --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 = @REVENUEPAYMETHODID and OUTDATED = 0;

                        if @POSTSTATUSCODE = 1
                            exec dbo.USP_SAVE_PROPERTYDETAILGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE;

                        --Restore CONTEXT_INFO

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

                    if @ADJUST = 1
                        exec dbo.USP_ADJUSTMENTHISTORY_PROPERTY_SAVEHISTORY @REVENUEPAYMETHODID, @CHANGEAGENTID, null, @PROPERTYDETAILADJUSTMENTID;

                    if @OLDSPOTRATEID is not null
                    begin
                        exec dbo.USP_CURRENCYEXCHANGERATE_DELETEBYID_WITHCHANGEAGENTID @OLDSPOTRATEID, @CHANGEAGENTID;
                    end

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

                return 0;