UFN_RECURRINGGIFTINSTALLMENT_GETWRITEOFFINSTALLMENTS_2

Returns all installments that can be written off for a given recurring gift.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@REVENUEID	uniqueidentifier	IN

Definition

Copy



CREATE function dbo.UFN_RECURRINGGIFTINSTALLMENT_GETWRITEOFFINSTALLMENTS_2(@REVENUEID uniqueidentifier)
returns table            
as            
return (
    select 
        RECURRINGGIFTINSTALLMENT.ID,
        RECURRINGGIFTINSTALLMENT.DATE,
        RECURRINGGIFTINSTALLMENT.AMOUNT,
        dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(RECURRINGGIFTINSTALLMENT.ID) as [BALANCE],
        cast(0.0 as money) as [WRITEOFFAMOUNT],
        RECURRINGGIFTINSTALLMENT.BASECURRENCYID,
        RECURRINGGIFTINSTALLMENT.ORGANIZATIONAMOUNT,
        RECURRINGGIFTINSTALLMENT.ORGANIZATIONEXCHANGERATEID,
        RECURRINGGIFTINSTALLMENT.TRANSACTIONAMOUNT,
        RECURRINGGIFTINSTALLMENT.TRANSACTIONCURRENCYID,
        RECURRINGGIFTINSTALLMENT.BASEEXCHANGERATEID,
        RECURRINGGIFTINSTALLMENT.STATUSCODE
    from 
        dbo.RECURRINGGIFTINSTALLMENT
    where 
        REVENUEID = @REVENUEID
        and dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(ID) > 0
    union all
    select
        null,
        I.DATE,
        REVENUE.AMOUNT,
        REVENUE.TRANSACTIONAMOUNT,
        cast(0.0 as money) as WRITEOFFAMOUNT,
        REVENUE.BASECURRENCYID,
        REVENUE.ORGANIZATIONAMOUNT,
        REVENUE.ORGANIZATIONEXCHANGERATEID,
        REVENUE.TRANSACTIONAMOUNT,
        REVENUE.TRANSACTIONCURRENCYID,
        REVENUE.BASEEXCHANGERATEID,
        0 STATUSCODE
    from dbo.UFN_RECURRINGGIFT_GETMISSINGINSTALLMENTS(@REVENUEID) I
    inner join dbo.REVENUE on REVENUE.ID = @REVENUEID
    )