V_QUERY_MEMBERSHIPLEVELTERMS
Query view for membership level terms information.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| LEVELID | uniqueidentifier | Membership level ID | |
| TERM | nvarchar(8) | yes | Term |
| AMOUNT | money | Amount | |
| SEQUENCE | int | Sequence | |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| BASECURRENCYID | uniqueidentifier | yes | Base currency ID |
| ORGANIZATIONAMOUNT | money | Amount (organization currency) | |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | Currency exchange rate |
| TERMLENGTH | nvarchar(26) | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 3/19/2013 1:46:08 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_MEMBERSHIPLEVELTERMS AS
select
TERMS.ID,
TERMS.LEVELID,
TERMS.TERM,
TERMS.AMOUNT,
TERMS.SEQUENCE,
ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
ADDEDBY.USERNAME as ADDEDBY_USERNAME,
CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
CHANGEDBY.USERNAME as CHANGEDBY_USERNAME,
TERMS.DATEADDED,
TERMS.DATECHANGED,
TERMS.TSLONG,
TERMS.BASECURRENCYID,
TERMS.ORGANIZATIONAMOUNT,
TERMS.ORGANIZATIONEXCHANGERATEID,
case MP.PROGRAMTYPECODE
when 0 then cast(TERMS.TERMTIMELENGTH as nvarchar(5)) + ' ' + TERMS.TERMLENGTH
when 1 then TERMS.RECURRINGPAYMENTOPTION
when 2 then case when TERMS.LIFETIMEPAYMENTOPTIONCODE = 0 then TERMS.LIFETIMEPAYMENTOPTION
else cast(TERMS.LIFETIMENUMBEROFPAYMENTS as nvarchar(5)) + ' ' + TERMS.LIFETIMEPAYMENTOPTION + ' payments'
end
end as TERMLENGTH
from dbo.MEMBERSHIPLEVELTERM as TERMS
inner join dbo.MEMBERSHIPLEVEL as ML on ML.ID = TERMS.LEVELID
inner join dbo.MEMBERSHIPPROGRAM as MP on ML.MEMBERSHIPPROGRAMID = MP.ID
left join dbo.CHANGEAGENT as ADDEDBY on ADDEDBY.ID = TERMS.ADDEDBYID
left join dbo.CHANGEAGENT as CHANGEDBY on CHANGEDBY.ID = TERMS.CHANGEDBYID