USP_RECURRINGGIFT_ADDMISSINGINSTALLMENTS

Bring a recurring gift's installments up to date, including the next expected payment.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CREATIONDATE	datetime	IN
@AMOUNT	money	IN
@LASTINSTALLMENTDATE	date	INOUT
@BASECURRENCYID	uniqueidentifier	IN
@ORGANIZATIONAMOUNT	money	IN
@ORGANIZATIONEXCHANGERATEID	uniqueidentifier	IN
@TRANSACTIONAMOUNT	money	IN
@TRANSACTIONCURRENCYID	uniqueidentifier	IN
@BASEEXCHANGERATEID	uniqueidentifier	IN
@ASOFDATE	date	IN

Definition

Copy



CREATE procedure dbo.USP_RECURRINGGIFT_ADDMISSINGINSTALLMENTS (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @CREATIONDATE datetime,
    @AMOUNT money,
    @LASTINSTALLMENTDATE date = null output,
    @BASECURRENCYID uniqueidentifier = null,
    @ORGANIZATIONAMOUNT money = null,
    @ORGANIZATIONEXCHANGERATEID uniqueidentifier = null,
    @TRANSACTIONAMOUNT money = null,
    @TRANSACTIONCURRENCYID uniqueidentifier = null,
    @BASEEXCHANGERATEID uniqueidentifier = null,
    @ASOFDATE date = null
)
as
begin
    declare @DATES table (DATE date)

    insert into @DATES
    select DATE
    from dbo.UFN_RECURRINGGIFT_GETMISSINGINSTALLMENTS_ASOFDATE(@ID, @ASOFDATE);

    declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()

    -- insert any missing installments and the next installment

    insert into dbo.RECURRINGGIFTINSTALLMENT (
        ID,
        REVENUEID,
        AMOUNT,
        DATE,
        BASECURRENCYID,
        ORGANIZATIONAMOUNT,
        ORGANIZATIONEXCHANGERATEID,
        TRANSACTIONAMOUNT,
        TRANSACTIONCURRENCYID,
        BASEEXCHANGERATEID,
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select
        newid(),
        @ID,
        @AMOUNT,
        DATE,
        coalesce(@BASECURRENCYID,@ORGANIZATIONCURRENCYID),
        coalesce(@ORGANIZATIONAMOUNT,@AMOUNT),
        @ORGANIZATIONEXCHANGERATEID,
        coalesce(@TRANSACTIONAMOUNT,@AMOUNT),
        coalesce(@TRANSACTIONCURRENCYID,@ORGANIZATIONCURRENCYID),
        @BASEEXCHANGERATEID,
        @CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE
    from @DATES;

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