V_QUERY_MEMBERSHIPMARKETING
Membership query for marketing integration.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| CONSTITUENTID | uniqueidentifier | Member system record ID | |
| GIVENBYID | uniqueidentifier | yes | Given by system record ID |
| EXPIRATIONDATE | date | yes | Expiration date |
| ISGIFT | bit | Is gift | |
| MEMBER | nvarchar(154) | yes | Member |
| GIVENBY | nvarchar(154) | yes | Given by |
| JOINDATE | datetime | yes | Join date |
| LASTRENEWEDON | datetime | yes | Last renewed |
| LEVEL | nvarchar(100) | yes | Membership level |
| PROGRAM | nvarchar(100) | Membership program | |
| SENDRENEWAL | nvarchar(24) | yes | Send renewal notice to |
| STATUS | nvarchar(9) | yes | Status |
| TERM | nvarchar(8) | yes | Membership term |
| TYPE | nvarchar(100) | yes | Membership type |
| CURRENTLEVELPRICE | money | yes | Membership level price |
| NEXTLEVELNAME | nvarchar(100) | yes | Next membership level |
| NEXTLEVELPRICE | money | yes | Next membership level price |
| ADDEDBY | nvarchar(128) | yes | Added by |
| CHANGEDBY | nvarchar(128) | yes | Changed by |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| ORGANIZATIONCURRENTLEVELPRICE | money | yes | Membership level price (organization currency) |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | Currency exchange rate |
| ORGANIZATIONNEXTLEVELPRICE | money | yes | Next membership level price (organization currency) |
| ORGANIZATIONNEXTLEVELEXCHANGERATEID | uniqueidentifier | yes | Next membership level currency exchange rate |
| BASECURRENCYID | uniqueidentifier | yes | Base currency ID |
| LOOKUPID | nvarchar(100) | yes | Lookup ID |
| NAMEONCARD | nvarchar(700) | yes | Name on card |
| ISPRIMARY | bit | Is primary | |
| ISDROPPED | bit | Is dropped |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/11/2014 4:23:35 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_MEMBERSHIPMARKETING AS
select
[MEMBERSHIP].[ID],
[MEMBER].[CONSTITUENTID],
[MEMBERSHIP].[GIVENBYID],
-- JLM 7/6/11 Stripping timestamp to account for recent changes in the export process
convert(date,[MEMBERSHIP].[EXPIRATIONDATE]) as EXPIRATIONDATE,
[MEMBERSHIP].[ISGIFT],
[CONSTITUENT].[NAME] as [MEMBER],
[GIVENBY].[NAME] as [GIVENBY],
[MEMBERSHIP].[JOINDATE],
[MEMBERSHIP].[LASTRENEWEDON],
[MEMBERSHIPLEVEL].[NAME] as [LEVEL],
[MEMBERSHIPPROGRAM].[NAME] as [PROGRAM],
[MEMBERSHIP].[SENDRENEWAL],
case when ([MEMBERSHIP].[STATUSCODE] = 0 and dateadd(month, [MEMBERSHIPLEVEL].[AFTEREXPIRATION], [MEMBERSHIP].[EXPIRATIONDATE]) < getdate()) then 'Lapsed'
else [MEMBERSHIP].[STATUS]
end as [STATUS],
[MEMBERSHIPLEVELTERM].[TERM],
[MEMBERSHIPLEVELTYPECODE].[DESCRIPTION] as [TYPE],
[MEMBERSHIPLEVELTERM].[AMOUNT] as [CURRENTLEVELPRICE],
[NEXTLEVEL].[NAME] as [NEXTLEVELNAME],
[NEXTLEVELTERM].[AMOUNT] as [NEXTLEVELPRICE],
[ADDEDBY].[USERNAME] as [ADDEDBY],
[CHANGEDBY].[USERNAME] as [CHANGEDBY],
[MEMBERSHIP].[DATEADDED],
[MEMBERSHIP].[DATECHANGED],
[MEMBERSHIP].[TSLONG],
[MEMBERSHIPLEVELTERM].ORGANIZATIONAMOUNT as [ORGANIZATIONCURRENTLEVELPRICE],
[MEMBERSHIPLEVELTERM].ORGANIZATIONEXCHANGERATEID,
[NEXTLEVELTERM].[ORGANIZATIONAMOUNT]as [ORGANIZATIONNEXTLEVELPRICE],
[NEXTLEVELTERM].[ORGANIZATIONEXCHANGERATEID] as [ORGANIZATIONNEXTLEVELEXCHANGERATEID],
[MEMBERSHIPLEVELTERM].BASECURRENCYID,
[CONSTITUENT].[LOOKUPID],
(select TOP 1 [MEMBERSHIPCARD].[NAMEONCARD]
from dbo.[MEMBERSHIPCARD]
where [MEMBERSHIPCARD].[MEMBERID] = [MEMBER].[ID]
and [MEMBERSHIPCARD].[STATUSCODE] <> 2
order by [MEMBERSHIPCARD].[STATUSCODE] desc, [MEMBERSHIPCARD].[EXPIRATIONDATE] desc
) as [NAMEONCARD],
[MEMBER].[ISPRIMARY],
[MEMBER].[ISDROPPED]
/*#EXTENSION*/
from dbo.[MEMBERSHIP]
inner join dbo.[MEMBERSHIPPROGRAM] on dbo.[MEMBERSHIP].[MEMBERSHIPPROGRAMID] = [MEMBERSHIPPROGRAM].[ID]
inner join dbo.[MEMBER] on [MEMBERSHIP].[ID] = [MEMBER].[MEMBERSHIPID]
left outer join dbo.[MEMBERSHIPLEVEL] on [MEMBERSHIPLEVEL].[ID] = [MEMBERSHIP].[MEMBERSHIPLEVELID]
left outer join dbo.[MEMBERSHIPLEVELTERM] on [MEMBERSHIPLEVELTERM].[ID] = [MEMBERSHIP].[MEMBERSHIPLEVELTERMID]
outer apply (
/* Find the Next Level, ignore inactive levels */
select top 1
INTERNAL.ID
, INTERNAL.NAME
from dbo.MEMBERSHIPLEVEL INTERNAL
where INTERNAL.SEQUENCE > [MEMBERSHIPLEVEL].SEQUENCE
and INTERNAL.MEMBERSHIPPROGRAMID = [MEMBERSHIPLEVEL].MEMBERSHIPPROGRAMID
and INTERNAL.ISACTIVE = 1
order by INTERNAL.SEQUENCE
) [NEXTLEVEL]
left outer join dbo.[MEMBERSHIPLEVELTERM] [NEXTLEVELTERM] on [NEXTLEVELTERM].[LEVELID] = [NEXTLEVEL].[ID] and [NEXTLEVELTERM].[TERMCODE] = 0
left outer join dbo.[MEMBERSHIPLEVELTYPECODE] on [MEMBERSHIPLEVELTYPECODE].[ID] = [MEMBERSHIP].[MEMBERSHIPLEVELTYPECODEID]
inner join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [MEMBER].[CONSTITUENTID]
left outer join dbo.[CONSTITUENT] as [GIVENBY] on [GIVENBY].[ID] = [MEMBERSHIP].[GIVENBYID]
left outer join dbo.[CHANGEAGENT] as [ADDEDBY] on [ADDEDBY].[ID] = [MEMBERSHIP].[ADDEDBYID]
left outer join dbo.[CHANGEAGENT] as [CHANGEDBY] on [CHANGEDBY].[ID] = [MEMBERSHIP].[CHANGEDBYID]