USP_DATAFORMTEMPLATE_EDITSAVE_REVENUETRANSACTIONCHANGECONSTITUENTPOSTED_2

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITSAVE_REVENUETRANSACTIONCHANGECONSTITUENTPOSTED_2
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @NEWCONSTITUENTID uniqueidentifier,
            @CONSTITUENTACCOUNTID uniqueidentifier,
                        @ADJUSTMENTPOSTDATE datetime,
                        @ADJUSTMENTDATE datetime,
                        @ADJUSTMENTREASON nvarchar(100),
                        @RESETRECOGNITIONCREDITS bit,
                        @RESETSOLICITORS bit
                    )
                    as
                        set nocount on

                        -- Verify the new constituent isn't the same as the previous one

                        declare @PREVIOUSCONSTITUENTID uniqueidentifier;
                        declare @TRANSACTIONTYPECODE tinyint;

                        select top 1 
                            @PREVIOUSCONSTITUENTID = CONSTITUENTID,
                            @TRANSACTIONTYPECODE = TRANSACTIONTYPECODE
                        from dbo.REVENUE
                        where ID = @ID

                        if @PREVIOUSCONSTITUENTID = @NEWCONSTITUENTID
                        begin
                            raiserror('NEWCONSTITUENTCANNOTBECURRENTCONSTITUENT', 13, 1)
                            return 1
                        end

                        declare @CHANGEDATE datetime
                        set @CHANGEDATE = getdate()

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

                        -- Get payment code

                        declare @PAYMENTMETHODCODE tinyint;
                        declare @REVENUEPAYMENTMETHODID uniqueidentifier;

                        select 
                          @PAYMENTMETHODCODE = PAYMENTMETHODCODE,
                          @REVENUEPAYMENTMETHODID = ID 
                        from 
                          dbo.REVENUEPAYMENTMETHOD 
                        where
                          REVENUEID = @ID;

                        -- Verify the transaction has already been posted

                        declare @ISPOSTED bit
                        select
                            @ISPOSTED = 
                            case 
                                when exists (select 1 from dbo.REVENUEPOSTED RP where RP.ID = @ID) then 1
                                else 0
                            end

                        if @ISPOSTED = 0
                        begin
                            raiserror('TRANSACTIONMUSTBEPOSTED', 13, 1)
                            return 1                        
                        end

                        -- Verify the adjustment dates are set

                        if @ADJUSTMENTPOSTDATE is null
                        begin
                            raiserror('ADJUSTMENTPOSTDATEREQUIRED', 13, 1)
                            return 1
                        end

                        if @ADJUSTMENTDATE is null
                        begin
                            raiserror('ADJUSTMENTDATEREQUIRED', 13, 1)
                            return 1
                        end

                        declare @ADJUSTMENTID uniqueidentifier;
                        declare @STOCKSALEADJUSTMENTIDS xml;
                        declare @PROPERTYDETAILADJUSTMENTID uniqueidentifier;
                        declare @WRITEOFFADJUSTMENTID uniqueidentifier;
                        declare @PROPERTYDETAILCOUNT int;
                        declare @ADJUSTEDWRITEOFFS table(WRITEOFFID uniqueidentifier, ADJUSTMENTID uniqueidentifier)

                        begin try

                            declare @contextCache varbinary(128);

                            --cache current context information

                            set @contextCache = CONTEXT_INFO();

                            --set CONTEXT_INFO to @CHANGEAGENTID

                            set CONTEXT_INFO @CHANGEAGENTID;

                            exec dbo.USP_SAVE_ADJUSTMENT @ID, @ADJUSTMENTID output, @CHANGEAGENTID, @CHANGEDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON;

                            if @TRANSACTIONTYPECODE = 1 
                            begin
                                if exists (select GL.ID from dbo.WRITEOFFGLDISTRIBUTION GL inner join dbo.WRITEOFF on GL.WRITEOFFID = WRITEOFF.ID where WRITEOFF.REVENUEID = @ID) 
                                begin
                                    declare @WRITEOFFID uniqueidentifier;

                                    declare WRITEOFFCURSOR cursor local fast_forward for
                                    select WRITEOFF.ID from dbo.WRITEOFF where REVENUEID = @ID;

                                    open WRITEOFFCURSOR;
                                    fetch next from WRITEOFFCURSOR into @WRITEOFFID;

                                    while @@FETCH_STATUS = 0
                                    begin
                                        set @WRITEOFFADJUSTMENTID = null;

                                        exec dbo.USP_SAVE_WRITEOFFADJUSTMENT @WRITEOFFID, @WRITEOFFADJUSTMENTID output, @CHANGEAGENTID, @CHANGEDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON;

                                        --Save adjustment IDs for adjustment history

                                        insert into @ADJUSTEDWRITEOFFS(WRITEOFFID,ADJUSTMENTID) values (@WRITEOFFID,@WRITEOFFADJUSTMENTID);

                                        fetch next from WRITEOFFCURSOR into @WRITEOFFID;    
                                    end

                                    close WRITEOFFCURSOR;
                                    deallocate WRITEOFFCURSOR;
                                end
                            end

                            else
                            begin                                
                                select @PROPERTYDETAILCOUNT = count(PROPERTYDETAIL.ID)
                                from dbo.PROPERTYDETAIL 
                                where PROPERTYDETAIL.ID = @REVENUEPAYMENTMETHODID and SALEPOSTSTATUSCODE = 0;

                                /* If sold stock has been posted, log stock detail adjustment */
                                if @PAYMENTMETHODCODE = 4 and exists(select ID from dbo.STOCKSALE where STOCKDETAILID = @REVENUEPAYMENTMETHODID)
                                begin
                                    exec dbo.USP_SAVE_STOCKDETAILADJUSTMENT @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, @CHANGEDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, @STOCKSALEADJUSTMENTIDS output;
                                end

                                /* If sold property has been posted, log property detail adjustment */
                                else if (@PAYMENTMETHODCODE = 5) and (@PROPERTYDETAILCOUNT > 0) 
                                begin
                                    exec dbo.USP_SAVE_PROPERTYDETAILADJUSTMENT @REVENUEPAYMENTMETHODID, @PROPERTYDETAILADJUSTMENTID output, @CHANGEAGENTID, @CHANGEDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON;                        
                                end
                            end

                            exec dbo.USP_REVENUE_UPDATERERECEIPTS @ID, @CHANGEAGENTID, @CHANGEDATE;

                            exec dbo.USP_REVENUELETTER_SETREACKNOWLEDGEMENTS @ID, @CHANGEAGENTID, @CHANGEDATE;

                            -- Gift Aid is for UK only

                            if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
                                -- Create manual refunds for any claimed revenue splits

                                exec dbo.USP_REVENUE_CREATEREFUNDS @ID, 1, @CHANGEAGENTID, @CHANGEDATE;

                            -- Update the constituent for all revenue entries belonging to the transaction

                            /* CMC
              update dbo.FINANCIALTRANSACTION set 
                                CONSTITUENTID = @NEWCONSTITUENTID,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CHANGEDATE
                            where FINANCIALTRANSACTION.ID = @ID
              */
              update dbo.REVENUE set 
                                CONSTITUENTID = @NEWCONSTITUENTID,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CHANGEDATE
                            where REVENUE.ID = @ID

                            if @RESETRECOGNITIONCREDITS = 1
                            begin               
                                -- Remove previous recognition credits

                                delete from dbo.REVENUERECOGNITION where REVENUESPLITID in (select ID from dbo.REVENUESPLIT where REVENUEID = @ID)

                                -- Create the new default recognition credits

                                insert into dbo.REVENUERECOGNITION
                                (
                                    REVENUESPLITID, 
                                    CONSTITUENTID, 
                                    AMOUNT,
                                    REVENUERECOGNITIONTYPECODEID,
                                    EFFECTIVEDATE,
                                    ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
                                )
                                select
                                    RS.ID,
                                    RR.CONSTITUENTID,
                                    RR.AMOUNT,
                                    RR.REVENUERECOGNITIONTYPECODEID,
                                    R.DATE,
                                    @CHANGEAGENTID,@CHANGEAGENTID,@CHANGEDATE,@CHANGEDATE
                                from dbo.REVENUE R
                                inner join dbo.REVENUESPLIT RS
                                    on RS.REVENUEID = R.ID
                                left join dbo.RECURRINGGIFTACTIVITY RGA on RGA.PAYMENTREVENUEID = R.ID
                                cross apply dbo.UFN_REVENUEDETAIL_GETDEFAULTRECOGNITIONS(R.GIVENANONYMOUSLY, R.CONSTITUENTID, RS.AMOUNT, R.DATE, RGA.SOURCEREVENUEID) RR
                                where R.ID = @ID
                            end

                            if @RESETSOLICITORS = 1
                            begin
                                -- Remove previous solicitors

                                delete from dbo.REVENUESOLICITOR where REVENUESPLITID in (select ID from dbo.REVENUESPLIT where REVENUEID = @ID)
                            end

                            if @PAYMENTMETHODCODE = 3 --Direct debit

                            begin

                      if @CONSTITUENTACCOUNTID is not null
                                begin
                                    update dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT set
                                        CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID,
                                        CHANGEDBYID = @CHANGEAGENTID,
                                        DATECHANGED = @CHANGEDATE
                                    where REVENUESCHEDULEDIRECTDEBITPAYMENT.ID = @ID
                                end
                                else
                                begin
                                    -- No longer autopay


                                    update dbo.REVENUEPAYMENTMETHOD set
                                        PAYMENTMETHODCODE = 9,
                                        CHANGEDBYID = @CHANGEAGENTID,
                                        DATECHANGED = @CHANGEDATE
                                    where 
                                        REVENUEID = @ID;

                                    -- Delete entry

                                    exec dbo.USP_REVENUESCHEDULEDIRECTDEBITPAYMENT_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;

                                end
                            end

                            -- Clear GL

                            delete from dbo.REVENUEGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;    
                            delete from dbo.WRITEOFFGLDISTRIBUTION where WRITEOFFID in (select WO.ID from dbo.WRITEOFF WO where WO.REVENUEID = @ID) and OUTDATED = 0;
                            delete from dbo.STOCKSALEGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;
                            delete from dbo.PROPERTYDETAILGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;

                            -- Add new GL distributions

                            if (select DONOTPOST from dbo.REVENUE where ID = @ID) = 0
                            begin
                                -- Add new GL distributions

                                exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE;

                                -- Add new stock detail GL distributions

                                if @PAYMENTMETHODCODE = 4 and exists(select ID from dbo.STOCKSALE where STOCKDETAILID = @REVENUEPAYMENTMETHODID)
                                    exec dbo.USP_SAVE_STOCKDETAILGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE;

                                -- Add new property detail GL distributions

                                if @PAYMENTMETHODCODE = 5 and exists(select ID from dbo.PROPERTYDETAIL where ID = @REVENUEPAYMENTMETHODID and ISSOLD = 1)
                                    exec dbo.USP_SAVE_PROPERTYDETAILGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE;

                                -- Add new write-off GL distributions

                                if @TRANSACTIONTYPECODE = 1
                                    exec dbo.USP_SAVE_WRITEOFFGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE;

                declare @DEPOSITID uniqueidentifier;
                select @DEPOSITID = DEPOSITID
                from dbo.BANKACCOUNTDEPOSITPAYMENT
                where ID = @ID;
                if @DEPOSITID is not null
                  exec dbo.USP_BANKACCOUNTDEPOSIT_OVERWRITEPAYMENTDEBITACCOUNTS @ID, @DEPOSITID, @CHANGEAGENTID, @CHANGEDATE;
                            end

                            if @ADJUSTMENTID is not null
                                exec dbo.USP_ADJUSTMENTHISTORY_REVENUE_SAVEHISTORY @ID, @CHANGEAGENTID, null, @ADJUSTMENTID

                            if @STOCKSALEADJUSTMENTIDS is not null
                                exec dbo.USP_ADJUSTMENTHISTORY_STOCK_SAVEHISTORY @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, null, @STOCKSALEADJUSTMENTIDS;

                            if @PROPERTYDETAILADJUSTMENTID is not null
                                exec dbo.USP_ADJUSTMENTHISTORY_PROPERTY_SAVEHISTORY @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, null, @PROPERTYDETAILADJUSTMENTID;

                            -- If this is a pledge, save the adjustment history for any write-offs

                            if @TRANSACTIONTYPECODE = 1 and (select count(*) from @ADJUSTEDWRITEOFFS) > 0
                            begin
                                declare @HISTORYWRITEOFFID uniqueidentifier;
                                declare @HISTORYADJUSTMENTID uniqueidentifier;

                                /* Cursor to use for logging history adjustments */
                                declare HISTORYCURSOR cursor local fast_forward for
                                select WRITEOFFID, ADJUSTMENTID from @ADJUSTEDWRITEOFFS

                                open HISTORYCURSOR;
                                fetch next from HISTORYCURSOR into @HISTORYWRITEOFFID, @HISTORYADJUSTMENTID;

                                while @@FETCH_STATUS = 0 
                                begin
                                    if @HISTORYADJUSTMENTID is not null
                                        exec dbo.USP_ADJUSTMENTHISTORY_WRITEOFF_SAVEHISTORY @HISTORYWRITEOFFID, @CHANGEAGENTID, null, @HISTORYADJUSTMENTID;

                                    fetch next from HISTORYCURSOR into @HISTORYWRITEOFFID, @HISTORYADJUSTMENTID;
                                end

                                close HISTORYCURSOR;
                                deallocate HISTORYCURSOR;
                            end

                            --reset CONTEXT_INFO to previous value

                            if not @contextCache is null
                                set CONTEXT_INFO @contextCache;
                        end try                    
                        begin catch
                            exec dbo.USP_RAISE_ERROR
                            return 1
                        end catch

                        return 0