UFN_INSTALLMENT_GETLASTINSTALLMENTPAID

This function returns the last installment paid for a given pledge.
Return

Return Type
uniqueidentifier
Parameters

Parameter	Parameter Type	Mode	Description
@REVENUEID	uniqueidentifier	IN
Definition

Copy


            CREATE function dbo.UFN_INSTALLMENT_GETLASTINSTALLMENTPAID
            (
                @REVENUEID uniqueidentifier
            )
            returns uniqueidentifier
            with execute as caller
            as 
            begin
                declare @ID uniqueidentifier;
                declare @DATE datetime;

                with PAIDINSTALLMENTS as (
                    select 
                        INSTALLMENTPAYMENT.INSTALLMENTID as INSTALLMENTID
                    from
                        dbo.INSTALLMENT
                    inner join
                        dbo.INSTALLMENTPAYMENT on INSTALLMENT.ID = INSTALLMENTPAYMENT.INSTALLMENTID
                    where 
                        INSTALLMENT.REVENUEID = @REVENUEID

                    union all

                    select
                        INSTALLMENTWRITEOFF.INSTALLMENTID as INSTALLMENTID
                    from
                        dbo.INSTALLMENT
                    inner join
                        dbo.INSTALLMENTWRITEOFF on INSTALLMENT.ID = INSTALLMENTWRITEOFF.INSTALLMENTID
                    where 
                        INSTALLMENT.REVENUEID = @REVENUEID
                )

                select top 1
                    @ID = INSTALLMENT.ID
                from 
                    dbo.INSTALLMENT 
                inner join
                    PAIDINSTALLMENTS on INSTALLMENT.ID = PAIDINSTALLMENTS.INSTALLMENTID
                order by INSTALLMENT.DATE desc, SEQUENCE desc;

                return @ID;
            end