UFN_REVENUE_GETLASTTRANSACTIONDATE

Retrieves the last scheduled transaction date of a pledge or recurring gift.
Return

Return Type
date
Parameters

Parameter	Parameter Type	Mode	Description
@REVENUEID	uniqueidentifier	IN
Definition

Copy


            CREATE function [dbo].[UFN_REVENUE_GETLASTTRANSACTIONDATE]
            (
                @REVENUEID uniqueidentifier
            )
            returns date
            begin
                declare @TRANSTYPECODE integer = 0
                declare @ENDDATE date = null

                select 
                    @TRANSTYPECODE = TYPECODE
                from
                    dbo.FINANCIALTRANSACTION
                where
                    FINANCIALTRANSACTION.ID = @REVENUEID
                    and FINANCIALTRANSACTION.DELETEDON is null

                --Check pledges

                if @TRANSTYPECODE = 1
                        select 
                            top 1 @ENDDATE = DATE
                        from 
                            dbo.INSTALLMENT
                        where 
                            REVENUEID = @REVENUEID
                            and 
                            dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID) <> 0
                        order by DATE desc

                --Check recurring gifts

                if @TRANSTYPECODE = 2
                        select
                            @ENDDATE = ENDDATE
                        from
                            dbo.REVENUESCHEDULE
                        where
                            ID = @REVENUEID

                return @ENDDATE

            end