USP_MEMBERSHIPDUESBATCH_VALIDATE_PLEDGE

CREATE procedure dbo.USP_MEMBERSHIPDUESBATCH_VALIDATE_PLEDGE
(
    @DATE datetime,    
    @AMOUNT money,
    @FREQUENCYCODE tinyint,
    @STARTDATE datetime,
    @NUMBEROFINSTALLMENTS integer,
    @INSTALLMENTS xml
)
as
begin
    if @NUMBEROFINSTALLMENTS > 150
        raiserror('BBERR_NUMINSTALLMENTS',13,1);

    if @FREQUENCYCODE not in (0,1,2,3,4,5,7,8)
        raiserror('BBERR_PLEDGEFREQUENCIESNOTSUPPORTED', 13, 1);

    if @STARTDATE < @DATE
        raiserror('BBERR_STARTDATEBEFOREPLEDGEDATE', 13, 1);

    if @AMOUNT <= 0
        raiserror('BBERR_MEMBERSHIPDUESBATCH_ZEROPLEDGEAMOUNT', 13, 1);

    -- Validate installments if the frequency is irregular

    if @FREQUENCYCODE = 4
    begin
            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 count(*) from @INSTALLMENTS_TABLE) < 1
                raiserror('BBERR_IRREGULARINSTALLMENTCOUNT', 13, 1);

            if dbo.UFN_INSTALLMENT_DATESOVERLAP(@INSTALLMENTS) = 1
                raiserror('BBERR_DATESOVERLAP',13,1);

            if exists(select ID from @INSTALLMENTS_TABLE where AMOUNT < 0)
                raiserror('BBERR_INSTALLMENTAMOUNTGREATERTHANZERO', 13, 1);

            declare @REVENUEDATELASTTIME as datetime
            select @REVENUEDATELASTTIME = dbo.UFN_DATE_GETLATESTTIME(@DATE)
            --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))))
                        raiserror('BBERR_INSTALLMENTBEFOREINSTALLMENTPLANDATE', 13, 1);

            if (select coalesce(sum(AMOUNT), 0) from @INSTALLMENTS_TABLE) <> @AMOUNT
            begin
                raiserror('BBERR_INSTALLMENTAMOUNTSUMGREATERTHANINSTALLMENTPLAN', 13, 1);
            end
  end
end