V_QUERY_MEMBERSHIP_TEMPLATE
This is a template for the system generated membership program queries.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| MEMBERSHIPPROGRAMID | uniqueidentifier | Membership program ID | |
| MEMBERSHIPLEVELID | uniqueidentifier | Membership level ID | |
| MEMBERSHIPLEVEL | nvarchar(100) | yes | Membership level |
| MEMBERSHIPLEVELTYPE | nvarchar(100) | yes | Membership level type |
| STATUS | nvarchar(9) | yes | Status |
| TERM | nvarchar(8) | yes | Term |
| AMOUNT | money | yes | Amount |
| JOINDATE | datetime | yes | Join date |
| LASTRENEWEDON | datetime | yes | Last renewed on |
| EXPIRATIONDATE | date | yes | Expiration date |
| DAYSUNTILEXPIRATION | int | yes | Days until expiration |
| NUMBEROFCHILDREN | smallint | Number of children | |
| NUMBEROFMEMBERS | int | yes | Number of members |
| COMMENTS | nvarchar(1000) | Comments | |
| ISGIFT | bit | Is gift | |
| SENDRENEWAL | nvarchar(24) | yes | Send renewal notice to |
| GIVENBYID | uniqueidentifier | yes | Given by system ID |
| GIVENBYNAME | nvarchar(154) | yes | Given by name |
| MEMBERSHIPID | nvarchar(100) | yes | Membership ID |
| CONSTITUENTID | uniqueidentifier | yes | Member |
| 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 | yes | Amount (organization currency) |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | Currency exchange rate |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 12/15/2016 8:39:58 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.165.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_MEMBERSHIP_TEMPLATE AS
select
MEMBERSHIP.ID,
MEMBERSHIP.MEMBERSHIPPROGRAMID,
MEMBERSHIP.MEMBERSHIPLEVELID,
MEMBERSHIPLEVEL.NAME as MEMBERSHIPLEVEL,
MEMBERSHIPLEVELTYPECODE.DESCRIPTION as MEMBERSHIPLEVELTYPE,
MEMBERSHIP.STATUS,
MEMBERSHIPLEVELTERM.TERM,
MEMBERSHIPLEVELTERM.AMOUNT,
MEMBERSHIP.JOINDATE,
MEMBERSHIP.LASTRENEWEDON,
CAST(MEMBERSHIP.EXPIRATIONDATE as date) as EXPIRATIONDATE,
DATEDIFF(dd,getdate(), MEMBERSHIP.EXPIRATIONDATE) as DAYSUNTILEXPIRATION,
MEMBERSHIP.NUMBEROFCHILDREN,
(select count(ID) from dbo.MEMBER where MEMBERSHIPID = MEMBERSHIP.ID and ISDROPPED = 0) as NUMBEROFMEMBERS,
MEMBERSHIP.COMMENTS,
MEMBERSHIP.ISGIFT,
MEMBERSHIP.SENDRENEWAL,
MEMBERSHIP.GIVENBYID,
dbo.UFN_CONSTITUENT_BUILDNAME(MEMBERSHIP.GIVENBYID) as [GIVENBYNAME],
MEMBERSHIP.LOOKUPID as MEMBERSHIPID,
MEMBER.CONSTITUENTID,
ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
ADDEDBY.USERNAME as ADDEDBY_USERNAME,
CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
CHANGEDBY.USERNAME as CHANGEDBY_USERNAME,
MEMBERSHIP.DATEADDED,
MEMBERSHIP.DATECHANGED,
MEMBERSHIP.TSLONG,
MEMBERSHIPLEVELTERM.BASECURRENCYID,
MEMBERSHIPLEVELTERM.ORGANIZATIONAMOUNT,
MEMBERSHIPLEVELTERM.ORGANIZATIONEXCHANGERATEID
/*#EXTENSION*/
from dbo.MEMBERSHIP
left join dbo.MEMBERSHIPLEVELTYPECODE on MEMBERSHIPLEVELTYPECODE.ID = MEMBERSHIP.MEMBERSHIPLEVELTYPECODEID
left join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
left join dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
left join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
left join dbo.CHANGEAGENT as ADDEDBY on ADDEDBY.ID = MEMBERSHIP.ADDEDBYID
left join dbo.CHANGEAGENT as CHANGEDBY on CHANGEDBY.ID = MEMBERSHIP.CHANGEDBYID
/*WHERECLAUSE*/