USP_DATAFORMTEMPLATE_EDIT_RECURRINGGIFTINSTALLMENTWRITEOFF

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_RECURRINGGIFTINSTALLMENTWRITEOFF 
                (
                @ID nvarchar(42),
                @INSTALLMENTWRITEOFFS xml,
                @CHANGEAGENTID uniqueidentifier,
                @TRANSACTIONCURRENCYID uniqueidentifier
                )
                as

                    set nocount on;

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

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

                    declare @INSTALLMENTID uniqueidentifier = cast(substring(@ID,1,36) as uniqueidentifier);

                    declare @RGID uniqueidentifier;

                    -- build a temporary table containing the write-offs
                    declare @TempTbl table (
                       ID uniqueidentifier,
                       WRITEOFFID uniqueidentifier,
                       DATE datetime,
                       AMOUNT money,
                       REASONCODEID uniqueidentifier,
                       NEW_WRITEOFFID uniqueidentifier)

                    insert into @TempTbl select 
                       ID,
                       WRITEOFFID,
                       DATE,
                       AMOUNT,
                       REASONCODEID,
                       newid()
                    from dbo.UFN_RECURRINGGIFTINSTALLMENT_GETWRITEOFFS_FROMITEMLISTXML(@INSTALLMENTWRITEOFFS);

                    declare @WRITEOFF_CHANGE smallint;

                    -- -1 = Total write-off amount decreased, 0 = Stayed the same, 1 = increased
                    select @WRITEOFF_CHANGE = sign(sum(isnull(t.AMOUNT,0))-sum(w.AMOUNT))
                    from dbo.RECURRINGGIFTINSTALLMENTWRITEOFF w
                    left join @TempTbl t on w.ID = t.ID
                    where w.RECURRINGGIFTINSTALLMENTID = @INSTALLMENTID;


                    ---------------------------------------------------------------------------
                    -- delete records for removed write-offs

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

                    if not @CHANGEAGENTID is null
                      set CONTEXT_INFO @CHANGEAGENTID;

                    declare @DELETEDINFO table (ID uniqueidentifier, WRITEOFFID uniqueidentifier, DATE date);

                    insert into @DELETEDINFO
                    select ID, WRITEOFFID, DATE
                    from dbo.UFN_RECURRINGGIFTINSTALLMENT_GETWRITEOFFS(@INSTALLMENTID)
                    where ID not in(select ID from @TempTbl where AMOUNT > 0);

                    -- delete all the RECURRINGGIFTINSTALLMENTWRITEOFF records that no longer exist in the XML table or have a 0 amount
                    delete from dbo.RECURRINGGIFTINSTALLMENTWRITEOFF 
                    where ID in (select ID from @DELETEDINFO)

                    -- delete only the write-off entries that no longer have a RECURRINGGIFTINSTALLMENTWRITEOFF entry
                    delete from dbo.RECURRINGGIFTWRITEOFF
                    where ID in(select WRITEOFFID
                                from @DELETEDINFO
                                where WRITEOFFID not in(select WRITEOFFID from dbo.RECURRINGGIFTINSTALLMENTWRITEOFF));

                    if exists(select 'x'
                              from @DELETEDINFO
                              where DATE = cast(@CURRENTDATE as date))
                    begin
                      select @RGID = REVENUEID
                      from dbo.RECURRINGGIFTINSTALLMENT
                      where ID = @INSTALLMENTID;

                      exec dbo.USP_RECURRINGGIFT_DELETEFUTUREINSTALLMENTS @RGID, @CHANGEAGENTID;
                    end

                    if not @contextCache is null
                      set CONTEXT_INFO @contextCache;


                    ---------------------------------------------------------------------------
                    -- update records for updated write-offs

                    -- update recurringgiftwriteoff
                    -- if the parent record is shared by other installment writeoff records, create a new one instead of affecting the others
                    merge dbo.RECURRINGGIFTWRITEOFF w
                    using (select case when (select count(*) from dbo.RECURRINGGIFTINSTALLMENTWRITEOFF iw where iw.WRITEOFFID = tw.ID and iw.ID <> temp.ID) = 0 then tw.ID
                                       else temp.NEW_WRITEOFFID end ID,
                                  temp.DATE,
                                  temp.REASONCODEID,
                                  tw.REVENUEID
                           from dbo.RECURRINGGIFTWRITEOFF tw
                           inner join @TempTbl temp on tw.ID = temp.WRITEOFFID
                           where (tw.DATE <> temp.DATE or tw.REASONCODEID <> temp.REASONCODEID)
                           and temp.AMOUNT > 0) t
                    on (w.ID = t.ID)
                    when not matched then
                      insert (ID, REVENUEID, DATE, REASONCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TYPECODE)
                      values (t.ID, t.REVENUEID, t.DATE, t.REASONCODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, 0)
                    when matched then
                      update set DATE = t.DATE,
                                 REASONCODEID = t.REASONCODEID,
                                 CHANGEDBYID = @CHANGEAGENTID,
                                 DATECHANGED = @CURRENTDATE;

                    -- update any changes made to the installment write-off amount
                    update iw
                    set TRANSACTIONAMOUNT = temp.AMOUNT,
                        AMOUNT = WRITEOFFAMOUNTCURRENCYVALUES.BASEAMOUNT,
                        ORGANIZATIONAMOUNT = WRITEOFFAMOUNTCURRENCYVALUES.ORGANIZATIONAMOUNT,
                        WRITEOFFID = isnull(w.ID,iw.WRITEOFFID),
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    from dbo.RECURRINGGIFTINSTALLMENTWRITEOFF iw
                    inner join @TempTbl temp on iw.ID = temp.ID
                    left join dbo.RECURRINGGIFTWRITEOFF w on w.ID = temp.NEW_WRITEOFFID
                    outer apply dbo.UFN_CURRENCY_GETCURRENCYVALUES_2
                          (temp.AMOUNT,
                           null,
                           iw.BASECURRENCYID,
                           iw.BASEEXCHANGERATEID,
                           iw.TRANSACTIONCURRENCYID,
                           null,
                           null,
                           null,
                           iw.ORGANIZATIONEXCHANGERATEID,
                           0
                          ) as WRITEOFFAMOUNTCURRENCYVALUES
                     where (iw.TRANSACTIONAMOUNT <> temp.AMOUNT or w.ID is not null)
                     and temp.AMOUNT > 0;

                    -- If the write-off amount changed, update the RG status and next transaction date as needed.
                    if @WRITEOFF_CHANGE <> 0
                    begin
                      select @RGID = REVENUEID
                      from dbo.RECURRINGGIFTINSTALLMENT
                      where ID = @INSTALLMENTID;

                      declare @STATUSCHANGETYPECODE tinyint = case @WRITEOFF_CHANGE when -1 then 4 else 3 end

                      exec dbo.USP_RECURRINGGIFT_UPDATEREVENUESCHEDULE
                        @REVENUEID = @RGID,
    @STATUSCHANGETYPECODE = @STATUSCHANGETYPECODE,
                        @CHANGEAGENTID = @CHANGEAGENTID,
                        @CURRENTDATETIME = @CURRENTDATE
                    end

                return 0;