USP_REVENUEINSTALLMENT_VALIDATEINSTALLMENTS

CREATE procedure dbo.USP_REVENUEINSTALLMENT_VALIDATEINSTALLMENTS
(
    @INSTALLMENTS xml,
    @REVENUEDATE datetime,
    @REVENUEAMOUNT money,
    @ISMEMBERSHIPPLEDGE bit = 0
)
as
    set nocount on;

    declare @INSTALLMENTS_TABLE table
    (
        ID uniqueidentifier primary key,
        AMOUNT money,
        RECEIPTAMOUNT money,
        DATE datetime,
        SEQUENCE int
    )

    insert into @INSTALLMENTS_TABLE 
    (
        ID, 
        AMOUNT, 
        RECEIPTAMOUNT, 
        DATE, 
        SEQUENCE
    )
    select 
        coalesce(ID, newID()), 
        AMOUNT, 
        RECEIPTAMOUNT, 
        DATE, 
        SEQUENCE 
    from 
        dbo.UFN_REVENUEBATCH_GETINSTALLMENTS_FROMITEMLISTXML(@INSTALLMENTS)

    if (select coalesce(sum(AMOUNT), 0) from @INSTALLMENTS_TABLE) <> @REVENUEAMOUNT
    begin
        raiserror('Sum of installment amounts must equal the total pledge amount.', 13, 1);
    end

    if (select count(*) from @INSTALLMENTS_TABLE) < 1
        raiserror('Installments must be specified when the frequency is Irregular.', 13, 1);

    if dbo.UFN_INSTALLMENT_DATESOVERLAP(@INSTALLMENTS) = 1
        raiserror('Installment dates are out of sequence.',13,1);

    if exists(select ID from @INSTALLMENTS_TABLE where AMOUNT < 0)
        raiserror('Installment amount must be equal to or greater than 0.', 13, 1);

    declare @REVENUEDATELASTTIME as datetime
    select @REVENUEDATELASTTIME = dbo.UFN_DATE_GETLATESTTIME(@REVENUEDATE)

    --The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...

    if exists(select ID from @INSTALLMENTS_TABLE where @REVENUEDATELASTTIME > dateadd(ms, -003, dateadd(d, 1, cast(cast([DATE] as date) as datetime))))
        if @ISMEMBERSHIPPLEDGE = 1
        begin
            raiserror('CK_INSTALLMENT_STARTDATE_VALID', 13, 1);
        end
    else
        begin
            raiserror('Installment date cannot be before the pledge date.', 13, 1);
        end

    if exists (select ID from @INSTALLMENTS_TABLE where RECEIPTAMOUNT > AMOUNT)
    begin
        raiserror('Installment receipt amounts must be less than or equal to the installment amount.', 13, 1);
    end

return 0;