UFN_OPPORTUNITY_REVENUEAPPLIED

            create function dbo.UFN_OPPORTUNITY_REVENUEAPPLIED(@ID uniqueidentifier)
            returns money 
            with execute as caller
            as begin

                declare @TempTbl table (
                    REVENUEID uniqueidentifier,
                    AMOUNT money,
                    TRANSACTIONID uniqueidentifier
                );

                insert into @TempTbl
                    -- gifts and pledges

                    select distinct
                        R.ID as REVENUEID,
                        R.AMOUNT as AMOUNT,
                        R.ID as TRANSACTIONID
                    from
                        dbo.REVENUEOPPORTUNITY RO
                    inner join
                        dbo.REVENUESPLIT RS on RO.ID = RS.ID
                    inner join
                        dbo.REVENUE R on RS.REVENUEID = R.ID
                    where
                        RO.OPPORTUNITYID = @ID
                    and
                        (R.TRANSACTIONTYPECODE = 1
                        or
                        (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE = 0))
                    -- pledge payments

                    union all

                    select
                        PRS.ID as REVENUEID,
                        R.AMOUNT as AMOUNT,
                        R.ID as TRANSACTIONID
                    from
                        dbo.INSTALLMENTPAYMENT IP
                    inner join
                        dbo.REVENUE R on IP.PAYMENTID = R.ID
                    inner join
                        dbo.REVENUE PR on IP.PLEDGEID = PR.ID
                    inner join
                        dbo.REVENUESPLIT PRS on PRS.REVENUEID = PR.ID
                    inner join
                        dbo.REVENUEOPPORTUNITY RO on RO.ID = PRS.ID
                    where
                        RO.OPPORTUNITYID = @ID

                return coalesce((select SUM(AMOUNT) from @TempTbl), 0)

            end