UFN_REVENUE_VSEPLEDGEBALANCE

Returns the unpaid balance for a given pledge as of a given date by a given vse category set
Return

Return Type
money
Parameters

Parameter	Parameter Type	Mode
@REVENUEID	uniqueidentifier	IN
@ASOFDATE	datetime	IN
@EXCLUDEUSAGETYPECODE	tinyint	IN
@PURPOSETYPECODE	tinyint	IN
Definition

Copy


            CREATE function dbo.UFN_REVENUE_VSEPLEDGEBALANCE
            (
                @REVENUEID uniqueidentifier,
                @ASOFDATE datetime = null,
                @EXCLUDEUSAGETYPECODE tinyint = 0,
                @PURPOSETYPECODE tinyint = 0
            )
            returns money
            with execute as caller
            as begin
                declare @PLEDGEBALANCE money;

                select
                    @PLEDGEBALANCE = sum(REVENUESPLIT.AMOUNT)
                        -
                        (
                            coalesce((
                            select
                                sum(INSTALLMENTSPLITPAYMENT.AMOUNT) 
                            from
                                dbo.REVENUE
                                inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.PLEDGEID = REVENUE.ID
                                inner join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID
                                inner join dbo.DESIGNATION on INSTALLMENTSPLIT.DESIGNATIONID = DESIGNATION.ID
                                inner join dbo.VSECATEGORY on DESIGNATION.VSECATEGORYID = VSECATEGORY.ID
                            where
                                REVENUE.ID = @REVENUEID
                                and REVENUE.DATE <= @ASOFDATE
                                and VSECATEGORY.PURPOSETYPECODE = @PURPOSETYPECODE
                                and VSECATEGORY.USAGETYPECODE <> @EXCLUDEUSAGETYPECODE), 0)
                        )
                        -
                        (
                            coalesce((
                            select
                                sum(INSTALLMENTSPLITWRITEOFF.AMOUNT) 
                            from
                                dbo.REVENUE
                                inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.PLEDGEID = REVENUE.ID
                                inner join dbo.INSTALLMENTSPLITWRITEOFF on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID
                                inner join dbo.DESIGNATION on INSTALLMENTSPLIT.DESIGNATIONID = DESIGNATION.ID
                                inner join dbo.VSECATEGORY on DESIGNATION.VSECATEGORYID = VSECATEGORY.ID
                            where
                                REVENUE.ID = @REVENUEID
                                and REVENUE.DATE <= @ASOFDATE
                                and VSECATEGORY.PURPOSETYPECODE = @PURPOSETYPECODE
                                and VSECATEGORY.USAGETYPECODE <> @EXCLUDEUSAGETYPECODE), 0)
                        )
                from
                    dbo.REVENUE 
                    inner join REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID
                    inner join dbo.DESIGNATION on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
                    inner join dbo.VSECATEGORY on DESIGNATION.VSECATEGORYID = VSECATEGORY.ID
                where
                    REVENUE.ID = @REVENUEID
                    and VSECATEGORY.USAGETYPECODE <> @EXCLUDEUSAGETYPECODE
                    and VSECATEGORY.PURPOSETYPECODE = @PURPOSETYPECODE

                return coalesce(@PLEDGEBALANCE, 0);
            end