UFN_REVENUE_NEWREGISTRANTEVENTTOTAL_BYREVENUERULES

create function dbo.UFN_REVENUE_NEWREGISTRANTEVENTTOTAL_BYREVENUERULES
(
  @EVENTID uniqueidentifier
)
returns money
with execute as caller
begin

DECLARE @TOTAL money=0

SELECT 
    @TOTAL=
     ISNULL(SUM(FR.RECEIVEDAMOUNT +
    CASE WHEN FEDC.ISUNPAIDPLEDGES=1 THEN FR.UNPAIDPLEDGEAMOUNT ELSE 0 END + 
    CASE WHEN FEDC.ISREGISTRATIONREVENUE=1 THEN FR.REGAMOUNT ELSE 0 END +
    CASE WHEN FEDC.ISUNPAIDRECURRINGGIFTS=1 THEN FR.UNPAIDRECURRINGAMOUNT ELSE 0 END +
    CASE WHEN FEDC.ISPENDINGMATCHINGGIFTS=1 THEN FR.MATCHINGGIFTCLAIMAMOUNT ELSE 0 END +
    CASE WHEN FEDC.UNCONFIRMEDPARTICIPANTGIFTENTRY=1 THEN FR.UNCONFIRMEDOFFLINEAMOUNT ELSE 0 END),0)
from FAFRAISEDTOTAL FR
INNER JOIN REGISTRANT R on R.CONSTITUENTID=FR.CONSTITUENTID AND R.EVENTID=FR.EVENTID
LEFT JOIN FAFEVENTDONATIONOPTIONSCONFIG FEDC on FEDC.EVENTID = R.EVENTID
WHERE FR.EVENTID=@EVENTID AND FR.CONSTITUENTID NOT in
      (SELECT R2.CONSTITUENTID FROM REGISTRANT R2
      INNER JOIN EVENTEXTENSION ET ON ET.EVENTID=R2.EVENTID
      INNER JOIN (
           SELECT ET2.EVENTID
          , REG2.CONSTITUENTID AS CONSTITUENTID
          FROM dbo.REGISTRANT REG2
          INNER JOIN dbo.EVENTEXTENSION ET2 ON ET2.EVENTID=REG2.EVENTID 
      ) AA ON AA.CONSTITUENTID=R2.CONSTITUENTID AND AA.EVENTID=ET.PRIORYEAREVENTID)

return @TOTAL
end