UFN_DONORCHALLENGE_ENCUMBEREDAMOUNT

            create function dbo.UFN_DONORCHALLENGE_ENCUMBEREDAMOUNT(@ID uniqueidentifier) returns money 
            as
            begin

              declare @AMOUNT money;


              set @AMOUNT = (select coalesce(sum(AMOUNT),0) from dbo.DONORCHALLENGEENCUMBERED where DONORCHALLENGEID = @ID and STATUSTYPECODE = 0);
              --subtract any payments that have been approved

              set @AMOUNT = @AMOUNT -  
                  coalesce((select sum(DONORCHALLENGEENCUMBERED.AMOUNT) 
                    from dbo.DONORCHALLENGEENCUMBERED
                    inner join dbo.REVENUESPLIT on
                      DONORCHALLENGEENCUMBERED.REVENUESPLITID = REVENUESPLIT.ID
                    where DONORCHALLENGEENCUMBERED.DONORCHALLENGEID = @ID
                      and 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
                          inner join dbo.DONORCHALLENGEENCUMBERED DCEPLEDGE
                            on DCEPLEDGE.REVENUESPLITID = REVENUESPLIT.ID
                          where DCEPLEDGE.DONORCHALLENGEID = @ID
                            and DCEPLEDGE.STATUSTYPECODE = 0
                          )
                  ), 0)

                return @AMOUNT;
            end