USP_RESERVATIONSECURITYDEPOSITCREDIT_GETREFUNDAMOUNT

CREATE procedure dbo.USP_RESERVATIONSECURITYDEPOSITCREDIT_GETREFUNDAMOUNT
(
    @RESERVATIONID uniqueidentifier = null output
)
as 

set nocount on;

select SECURITYDEPOSITAMOUNT as AMOUNTCHARGED, 
coalesce((select sum(AMOUNT) from dbo.RESERVATIONSECURITYDEPOSITPAYMENT where RESERVATIONID = RESERVATION.ID), 0) as AMOUNTPAID,
coalesce((select sum(AMOUNT) from dbo.RESERVATIONSECURITYDEPOSITPAYMENT where RESERVATIONID = RESERVATION.ID), 0) -
coalesce((
    select sum(CREDITPAYMENT.AMOUNT)
    from dbo.CREDITPAYMENT
    inner join dbo.REVENUESPLIT_EXT on CREDITPAYMENT.REVENUESPLITID = REVENUESPLIT_EXT.ID 
    inner join dbo.CREDIT_EXT on CREDITPAYMENT.CREDITID = CREDIT_EXT.ID
    where REVENUESPLIT_EXT.TYPECODE = 13
        and CREDIT_EXT.SALESORDERID = @RESERVATIONID
), 0) as AMOUNTELIGIBLE
from dbo.RESERVATION where RESERVATION.ID = @RESERVATIONID