V_RECURRINGGIFTINSTALLMENT

Fields

Field	Field Type	Null
ID	uniqueidentifier	yes
REVENUEID	uniqueidentifier
DATE	date	yes
TRANSACTIONAMOUNT	money
BALANCE	money	yes
TRANSACTIONCURRENCYID	uniqueidentifier	yes
AMOUNT	money
BASECURRENCYID	uniqueidentifier	yes
BASEEXCHANGERATEID	uniqueidentifier	yes
ORGANIZATIONAMOUNT	money
ORGANIZATIONEXCHANGERATEID	uniqueidentifier	yes
STATUSCODE	int
STATUS	nvarchar(9)	yes
PASTDUEDATE	datetime	yes
ADDEDBYID	uniqueidentifier	yes
CHANGEDBYID	uniqueidentifier	yes
DATEADDED	datetime	yes
DATECHANGED	datetime	yes

Definition

Copy


create view dbo.V_RECURRINGGIFTINSTALLMENT as 
  select
    i.ID,
    i.REVENUEID,
    i.DATE,
    i.TRANSACTIONAMOUNT,
    -- skip the function if the installment status is not Expected or Past due
    case when i.STATUSCODE in(0,1) then b.BALANCE else 0 end BALANCE,
    i.TRANSACTIONCURRENCYID,
    i.AMOUNT,
    i.BASECURRENCYID,
    i.BASEEXCHANGERATEID,
    i.ORGANIZATIONAMOUNT,
    i.ORGANIZATIONEXCHANGERATEID,
    i.STATUSCODE,
    i.STATUS,
    i.PASTDUEDATE,
    i.ADDEDBYID,
    i.CHANGEDBYID,
    i.DATEADDED,
    i.DATECHANGED
  from dbo.RECURRINGGIFTINSTALLMENT i
  left join dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE_BULK() b on b.ID = i.ID
  union all
  select
    null,
    R.ID,
    I.DATE,
    R.TRANSACTIONAMOUNT,
    R.TRANSACTIONAMOUNT,
    R.TRANSACTIONCURRENCYID,
    R.AMOUNT,
    R.BASECURRENCYID,
    R.BASEEXCHANGERATEID,
    R.ORGANIZATIONAMOUNT,
    R.ORGANIZATIONEXCHANGERATEID,
    0,
    'Expected',
    null,
    null,
    null,
    null,
    null
  from dbo.REVENUE R
  cross apply dbo.UFN_RECURRINGGIFT_GETMISSINGINSTALLMENTS(R.ID) I;