UFN_REVENUEBATCH_GENERATEINSTALLMENTS

CREATE function dbo.UFN_REVENUEBATCH_GENERATEINSTALLMENTS
(
    @AMOUNT money,
    @INSTALLMENTFREQUENCYCODE tinyint,
    @INSTALLMENTSTARTDATE datetime,
    @NUMBEROFINSTALLMENTS integer,
    @TRANSACTIONCURRENCYID uniqueidentifier
)
returns xml
with execute as caller
as begin
    declare @INSTALLMENTS xml;
    declare @TRANSACTIONCURRENCYDECIMALDIGITS tinyint = 2;
    declare @i integer = 0;
    declare @TYPICALAMOUNT money;
    declare @INSTALLMENTTABLE table
        (
          DATE datetime,
          AMOUNT money,
          SEQUENCE int
        );

    select
        @TRANSACTIONCURRENCYDECIMALDIGITS = coalesce(DECIMALDIGITS, 2)
    from dbo.UFN_CURRENCY_GETPROPERTIES(@TRANSACTIONCURRENCYID);

    set @TYPICALAMOUNT = ROUND(@AMOUNT / @NUMBEROFINSTALLMENTS, @TRANSACTIONCURRENCYDECIMALDIGITS);

    if @AMOUNT > 0
        begin
            if @AMOUNT - (@TYPICALAMOUNT * (@NUMBEROFINSTALLMENTS - 1)) < 0 
            set @TYPICALAMOUNT = @TYPICALAMOUNT - power(10, -1 * @TRANSACTIONCURRENCYDECIMALDIGITS); 
        end

    -- Do not generate installments for Irregular frequency

    if @INSTALLMENTFREQUENCYCODE <> 4
        begin
            while @i < @NUMBEROFINSTALLMENTS
                begin

                    if @i = @NUMBEROFINSTALLMENTS - 1
                        set @TYPICALAMOUNT = @AMOUNT - (@TYPICALAMOUNT * (@NUMBEROFINSTALLMENTS - 1));

                    if @INSTALLMENTFREQUENCYCODE = 0
                        insert into @INSTALLMENTTABLE values (dateadd(yy, @i, @INSTALLMENTSTARTDATE), @TYPICALAMOUNT, @i + 1);
                    else if @INSTALLMENTFREQUENCYCODE = 1
                        insert into @INSTALLMENTTABLE values (dateadd(mm, @i * 6, @INSTALLMENTSTARTDATE), @TYPICALAMOUNT, @i + 1);
                    else if @INSTALLMENTFREQUENCYCODE = 2
                        insert into @INSTALLMENTTABLE values (dateadd(qq, @i, @INSTALLMENTSTARTDATE), @TYPICALAMOUNT, @i + 1);
                    else if @INSTALLMENTFREQUENCYCODE = 3
                        insert into @INSTALLMENTTABLE values (dateadd(mm, @i, @INSTALLMENTSTARTDATE), @TYPICALAMOUNT, @i + 1);
                    else if @INSTALLMENTFREQUENCYCODE = 5
                        insert into @INSTALLMENTTABLE values (@INSTALLMENTSTARTDATE, @TYPICALAMOUNT, @i + 1);
                    else if @INSTALLMENTFREQUENCYCODE = 7
                        begin
                            -- This is based on the algorithm for calculating semi-monthly dates found in RecurringGiftScheduleDataList.vb

                            declare @NUMMONTHS int;
                            declare @NEXTDATE datetime;

                            set @NUMMONTHS = @i / 2;
                            set @NEXTDATE = dateadd(mm,@NUMMONTHS,@INSTALLMENTSTARTDATE);

                            if @i % 2 <> 0
                                begin
                                    declare @NEXTDATE2 datetime;

                                    if datepart(dd,@INSTALLMENTSTARTDATE) <= 15
                                        begin

                                            set @NEXTDATE2 = dateadd(dd,15,@NEXTDATE);

                                            if datepart(mm,@NEXTDATE2) > datepart(mm,@NEXTDATE)
                                                set @NEXTDATE2 = dbo.UFN_DATE_THISMONTH_LASTDAY(@NEXTDATE,0)

                                            set @NEXTDATE = @NEXTDATE2;
                                        end
                                    else
                                        begin
                                            if datepart(dd,@INSTALLMENTSTARTDATE) > 15
                                                begin
                                                    set @NEXTDATE2 = dateadd(dd,15,@NEXTDATE);

                                                    if datepart(mm,@NEXTDATE2) = datepart(mm,@NEXTDATE) and datepart(dd,@NEXTDATE2) = 31
                                                        set @NEXTDATE2 = dateadd(dd,1,@NEXTDATE2);        

                                                    set @NEXTDATE = @NEXTDATE2;
                                                end
                                            else
                                                set @NEXTDATE = dateadd(dd,15,@NEXTDATE);
                                        end
                                end

                            insert into @INSTALLMENTTABLE values (@NEXTDATE, @TYPICALAMOUNT, @i + 1);

                    end
                    else if @INSTALLMENTFREQUENCYCODE = 8
                        insert into @INSTALLMENTTABLE values (dateadd(ww, @i * 2, @INSTALLMENTSTARTDATE), @TYPICALAMOUNT, @i + 1);

                    set @i = @i + 1;
                end

            set @INSTALLMENTS = 
            (
                select DATE, AMOUNT, SEQUENCE
                from @INSTALLMENTTABLE
                for xml raw ('ITEM'), type, elements, root ('INSTALLMENTS'), binary base64    
            );
        end

    return @INSTALLMENTS;
end