UFN_DONORCHALLENGEENCUMBERED_PLEDGEENCUMBEREDAMOUNT

            CREATE function dbo.UFN_DONORCHALLENGEENCUMBERED_PLEDGEENCUMBEREDAMOUNT(@ID uniqueidentifier) returns money 
            as
            begin

              declare @AMOUNT money;
              declare @DCID uniqueidentifier;
              declare @PLEDGESPLITID uniqueidentifier;

              select 
                @AMOUNT = AMOUNT,
                @DCID = DONORCHALLENGEID,
                @PLEDGESPLITID = REVENUESPLITID
              from dbo.DONORCHALLENGEENCUMBERED 
              where ID = @ID;


              set @AMOUNT = @AMOUNT - 
                  coalesce(
                    (select sum(DONORCHALLENGEENCUMBERED.AMOUNT) 
                    from dbo.DONORCHALLENGEENCUMBERED
                    where DONORCHALLENGEENCUMBERED.STATUSTYPECODE = 1 and
                    DONORCHALLENGEENCUMBERED.REVENUESPLITID in 
                    (
                      select INSTALLMENTSPLITPAYMENT.PAYMENTID 
                      from dbo.INSTALLMENTSPLITPAYMENT
                      inner join dbo.INSTALLMENTSPLIT
                        on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                      inner join dbo.REVENUESPLIT
                        on INSTALLMENTSPLIT.PLEDGEID = REVENUESPLIT.REVENUEID and INSTALLMENTSPLIT.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID
                      where DONORCHALLENGEID = @DCID
                        and REVENUESPLIT.ID = @PLEDGESPLITID
                     )
                    )
                   , 0); 

              return @AMOUNT;
            end