UFN_REVENUE_GETDONORRAISEDTOTAL

CREATE function dbo.UFN_REVENUE_GETDONORRAISEDTOTAL
(
    @CONSTITUENTID  uniqueidentifier,
    @EVENTID              uniqueidentifier,
    @CLIENTUSERID   int
)
returns money
with execute as caller
as begin
    -- do work here and return a value

  DECLARE @OWNERCONSTITUENTID  uniqueidentifier
  DECLARE @TEAMCONSTITUENTID  uniqueidentifier
  DECLARE @TOTAL money

  -- get addressbook owners

  Select @OWNERCONSTITUENTID = dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERID)  

  Select @TEAMCONSTITUENTID = TT.TEAMCONSTITUENTID
     From dbo.REGISTRANT R
         INNER JOIN dbo.TEAMFUNDRAISER TF 
             ON R.CONSTITUENTID = TF.CONSTITUENTID
         INNER JOIN dbo.TEAMFUNDRAISINGTEAMMEMBER TM
             ON TF.ID = TM.TEAMFUNDRAISERID
           INNER JOIN dbo.TEAMFUNDRAISINGTEAM T
               ON TM.TEAMFUNDRAISINGTEAMID = T.ID
         Inner Join dbo.TEAMEXTENSION TT
             ON TT.TEAMFUNDRAISINGTEAMID = T.ID
  WHERE R.CONSTITUENTID = @OWNERCONSTITUENTID

  Select @TOTAL = IsNull(Sum(R.AMOUNT), 0.00)
      from  Revenue  R inner join  REVENUESPLIT (nolock) on REVENUESPLIT.REVENUEID = R.ID   
            inner join dbo.REVENUERECOGNITION RC on RC.REVENUESPLITID = REVENUESPLIT.ID 
            inner join [EVENT] E on E.APPEALID = R.APPEALID
      where RC.CONSTITUENTID  in (@OWNERCONSTITUENTID, @TEAMCONSTITUENTID)
            and E.ID = @EVENTID
            and R.CONSTITUENTID = @CONSTITUENTID
            and R.TRANSACTIONTYPE ='payment'
  --no payment exists

  if @TOTAL = 0 
  begin
     Select @TOTAL = IsNull(Sum(R.AMOUNT), 0.00)
        from  Revenue  R inner join  REVENUESPLIT (nolock) on REVENUESPLIT.REVENUEID = R.ID   
              inner join dbo.REVENUERECOGNITION RC on RC.REVENUESPLITID = REVENUESPLIT.ID 
              inner join [EVENT] E on E.APPEALID = R.APPEALID
        where RC.CONSTITUENTID  in (@OWNERCONSTITUENTID, @TEAMCONSTITUENTID)
              and E.ID = @EVENTID
              and R.CONSTITUENTID = @CONSTITUENTID
  end

    return
    (
          @TOTAL
    )
end