UFN_RECURRINGGIFTINSTALLMENT_DETERMINESTATUSAFTERCHANGE

create function dbo.UFN_RECURRINGGIFTINSTALLMENT_DETERMINESTATUSAFTERCHANGE(@ID uniqueidentifier, @STATUSCODE tinyint, @RECORDADDED tinyint)
returns tinyint
with execute as caller
as begin

    declare @r as tinyint;

    select @r = case when dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(@ID) = 0 then -- zero balance

                    -- Balance is $0

                    case when @RECORDADDED = 1 then 2  -- Paid

                         else case when exists (select 1 from dbo.RECURRINGGIFTINSTALLMENTPAYMENT P where P.RECURRINGGIFTINSTALLMENTID = @ID) then 2   -- Paid

                                   -- No payment exists, new status corresponds to record added Write-off

                                   else @RECORDADDED  
                                   end 
                         end
                    -- Balance is not $0, keep current status, either Expected or Past due

                    else @STATUSCODE end;

    return @r
end