USP_DATAFORMTEMPLATE_EDIT_RECURRINGGIFTWRITEOFF

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_RECURRINGGIFTWRITEOFF (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @DATE date,
    @REASONID uniqueidentifier,
    @AMOUNT money,
    @INSTALLMENTS xml
)
as

    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    declare @REVENUEID uniqueidentifier;
    declare @RGAMOUNT money;
    declare @BASECURRENCYID uniqueidentifier;
    declare @RGORGANIZATIONAMOUNT money;
    declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
    declare @RGTRANSACTIONAMOUNT money;
    declare @TRANSACTIONCURRENCYID uniqueidentifier;
    declare @BASEEXCHANGERATEID uniqueidentifier;
    declare @INSTALLMENTSWRITEOFFAMOUNTSUM money;
    declare @INSTALLMENTSWRITEOFFAMOUNTMAX money;
    declare @MINBALANCE money;

    select
        @REVENUEID = REVENUE.ID,
        @RGAMOUNT = REVENUE.AMOUNT,
        @BASECURRENCYID = REVENUE.BASECURRENCYID,
        @RGORGANIZATIONAMOUNT = REVENUE.ORGANIZATIONAMOUNT,
        @ORGANIZATIONEXCHANGERATEID = REVENUE.ORGANIZATIONEXCHANGERATEID,
        @RGTRANSACTIONAMOUNT = REVENUE.TRANSACTIONAMOUNT,
        @TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
        @BASEEXCHANGERATEID = REVENUE.BASEEXCHANGERATEID
    from dbo.RECURRINGGIFTWRITEOFF
    inner join dbo.REVENUE on REVENUE.ID = RECURRINGGIFTWRITEOFF.REVENUEID
    where RECURRINGGIFTWRITEOFF.ID = @ID;

    select
        @INSTALLMENTSWRITEOFFAMOUNTSUM = sum(WRITEOFFINSTALLMENTS.WRITEOFFAMOUNT),
        @INSTALLMENTSWRITEOFFAMOUNTMAX = max(WRITEOFFINSTALLMENTS.WRITEOFFAMOUNT),
        @MINBALANCE = min(dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(WRITEOFFINSTALLMENTS.INSTALLMENTID) - (WRITEOFFINSTALLMENTS.WRITEOFFAMOUNT - coalesce(RECURRINGGIFTINSTALLMENTWRITEOFF.TRANSACTIONAMOUNT, 0)))
    from
        dbo.UFN_RECURRINGGIFTINSTALLMENT_GETWRITEOFFINSTALLMENTSFOREDIT_FROMITEMLISTXML(@INSTALLMENTS) as WRITEOFFINSTALLMENTS
        left join dbo.RECURRINGGIFTINSTALLMENTWRITEOFF on RECURRINGGIFTINSTALLMENTWRITEOFF.ID = WRITEOFFINSTALLMENTS.INSTALLMENTWRITEOFFID;

    begin try

        if @BASECURRENCYID <> @TRANSACTIONCURRENCYID and @BASEEXCHANGERATEID is null
            raiserror('BBERR_BASEEXCHANGERATE_MISSING', 13, 1);

        if @INSTALLMENTSWRITEOFFAMOUNTSUM <> @AMOUNT
            raiserror('BBERR_AMOUNTAPPLIED_NOTEQUALTO_WRITEOFFAMOUNT', 13, 1);

        if @INSTALLMENTSWRITEOFFAMOUNTMAX > @RGAMOUNT
            raiserror('BBERR_WRITEOFFAMOUNT_GREATERTHAN_RECURRINGGIFTAMOUNT', 13, 2);

        if @MINBALANCE < 0
            raiserror('BBERR_WRITEOFFAMOUNT_GREATERTHAN_RECURRINGGIFTAMOUNT', 13, 2);

        -- add missing installments

        exec dbo.USP_RECURRINGGIFT_ADDMISSINGINSTALLMENTS
            @REVENUEID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @RGAMOUNT,
            null,
            @BASECURRENCYID,
            @RGORGANIZATIONAMOUNT,
            @ORGANIZATIONEXCHANGERATEID,
            @RGTRANSACTIONAMOUNT,
            @TRANSACTIONCURRENCYID,
            @BASEEXCHANGERATEID;

        -- update writeoff

        update dbo.RECURRINGGIFTWRITEOFF
        set DATE = @DATE,
            REASONCODEID = @REASONID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @ID;

        -- update installment applications

        declare @INSTALLMENTSTAB table (INSTALLMENTID uniqueidentifier,
                                        DATE date,
                                        WRITEOFFAMOUNT money,
                                        INSTALLMENTWRITEOFFID uniqueidentifier);

        insert into @INSTALLMENTSTAB
        select INSTALLMENTID, DATE, WRITEOFFAMOUNT, INSTALLMENTWRITEOFFID
        from dbo.UFN_RECURRINGGIFTINSTALLMENT_GETWRITEOFFINSTALLMENTSFOREDIT_FROMITEMLISTXML(@INSTALLMENTS) I;

        -- new installment writeoffs

        insert into dbo.RECURRINGGIFTINSTALLMENTWRITEOFF (
            ID,
            RECURRINGGIFTINSTALLMENTID,
            WRITEOFFID,
            AMOUNT,
            BASECURRENCYID,
            ORGANIZATIONAMOUNT,
            ORGANIZATIONEXCHANGERATEID,
            TRANSACTIONAMOUNT,
            TRANSACTIONCURRENCYID,
            BASEEXCHANGERATEID,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select
            newid(),
            coalesce(I.INSTALLMENTID,(select ID from dbo.RECURRINGGIFTINSTALLMENT where REVENUEID = @REVENUEID and DATE = I.DATE)),
            @ID,
            WRITEOFFAMOUNTCURRENCYVALUES.BASEAMOUNT,
            @BASECURRENCYID,
            WRITEOFFAMOUNTCURRENCYVALUES.ORGANIZATIONAMOUNT,
            @ORGANIZATIONEXCHANGERATEID,
            I.WRITEOFFAMOUNT,
            @TRANSACTIONCURRENCYID,
            @BASEEXCHANGERATEID,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from @INSTALLMENTSTAB I
        outer apply dbo.UFN_CURRENCY_GETCURRENCYVALUES_2
            (
                I.WRITEOFFAMOUNT,
                null,
                @BASECURRENCYID,
                @BASEEXCHANGERATEID,
                @TRANSACTIONCURRENCYID,
                null,
                null,
                null,
                @ORGANIZATIONEXCHANGERATEID,
                0
            ) as WRITEOFFAMOUNTCURRENCYVALUES
        where I.WRITEOFFAMOUNT > 0
        and I.INSTALLMENTWRITEOFFID is null;

        -- updated installment writeoffs

        update dbo.RECURRINGGIFTINSTALLMENTWRITEOFF
        set AMOUNT = WRITEOFFAMOUNTCURRENCYVALUES.BASEAMOUNT,
            ORGANIZATIONAMOUNT = WRITEOFFAMOUNTCURRENCYVALUES.ORGANIZATIONAMOUNT,
            TRANSACTIONAMOUNT = I.WRITEOFFAMOUNT,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        from @INSTALLMENTSTAB I
        outer apply dbo.UFN_CURRENCY_GETCURRENCYVALUES_2
            (
                I.WRITEOFFAMOUNT,
                null,
                @BASECURRENCYID,
                @BASEEXCHANGERATEID,
                @TRANSACTIONCURRENCYID,
                null,
                null,
                null,
                @ORGANIZATIONEXCHANGERATEID,
                0
            ) as WRITEOFFAMOUNTCURRENCYVALUES
        where RECURRINGGIFTINSTALLMENTWRITEOFF.ID = I.INSTALLMENTWRITEOFFID
        and RECURRINGGIFTINSTALLMENTWRITEOFF.TRANSACTIONAMOUNT <> I.WRITEOFFAMOUNT
        and I.WRITEOFFAMOUNT > 0;

        --Cache CONTEXT INFO

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

        -- removed installment writeoffs

        delete from dbo.RECURRINGGIFTINSTALLMENTWRITEOFF
        where ID in(select INSTALLMENTWRITEOFFID from @INSTALLMENTSTAB where INSTALLMENTWRITEOFFID is not null and WRITEOFFAMOUNT = 0);

        --Restore CONTEXT INFO 

        if not @contextCache is null
            set CONTEXT_INFO @contextCache;


        -- Clean up future installments and next transaction date.


        declare @FUTUREINSTALLMENTEXISTS bit;
        set @FUTUREINSTALLMENTEXISTS = 0;

        select @FUTUREINSTALLMENTEXISTS = 1
        from dbo.RECURRINGGIFTINSTALLMENT
        where REVENUEID = @REVENUEID
        and DATE >= dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)
        and dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(ID) > 0;

        if @FUTUREINSTALLMENTEXISTS = 0
        begin
            declare @LASTINSTALLMENTDATE date;

            select @LASTINSTALLMENTDATE = max(DATE)
            from dbo.RECURRINGGIFTINSTALLMENT
            where REVENUEID = @REVENUEID;

            set @LASTINSTALLMENTDATE = dbo.UFN_REVENUE_GETNEXTTRANSACTIONDATE_BYID_1_1(@REVENUEID,@LASTINSTALLMENTDATE)

            declare @ENDDATE date;
            declare @STATUSCODE tinyint;

            select @ENDDATE = ENDDATE,
                   @STATUSCODE = STATUSCODE
            from dbo.REVENUESCHEDULE
            where ID = @REVENUEID;

            if @STATUSCODE = 0 and
               (@ENDDATE is null or
                @LASTINSTALLMENTDATE <= @ENDDATE)
            begin
                insert into dbo.RECURRINGGIFTINSTALLMENT (
                    ID,
                    REVENUEID,
                    AMOUNT,
                    DATE,
                    BASECURRENCYID,
                    ORGANIZATIONAMOUNT,
                    ORGANIZATIONEXCHANGERATEID,
                    TRANSACTIONAMOUNT,
                    TRANSACTIONCURRENCYID,
                    BASEEXCHANGERATEID,
                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                values (
                    newid(),
                    @REVENUEID,
                    @RGAMOUNT,
                    @LASTINSTALLMENTDATE,
                    @BASECURRENCYID,
                    @RGORGANIZATIONAMOUNT,
                    @ORGANIZATIONEXCHANGERATEID,
                    @RGTRANSACTIONAMOUNT,
                    @TRANSACTIONCURRENCYID,
                    @BASEEXCHANGERATEID,
                    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);


            end
            else
                update dbo.REVENUESCHEDULE
                    set NEXTTRANSACTIONDATE = @LASTINSTALLMENTDATE,
                        STATUSCODE = case when @STATUSCODE = 0 then 3 else @STATUSCODE end,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE                
                from dbo.REVENUESCHEDULE
                where ID = @REVENUEID;
        end
        else
        begin
            -- future installment exists, clean up

            exec dbo.USP_RECURRINGGIFT_DELETEFUTUREINSTALLMENTS @REVENUEID, @CHANGEAGENTID
        end
        --always call this code

        exec dbo.USP_RECURRINGGIFT_SETNEXTTRANSACTIONDATE @REVENUEID, @CHANGEAGENTID
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;