V_QUERY_MEMBERSHIPCARD_PRINTOUTPUT
Provides the ability to query fields used to print membership cards
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 | datetime | 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 on |
| 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 |
| MEMBERSHIPCARDID | uniqueidentifier | MEMBERSHIPCARDID | |
| CARDNUMBER | nvarchar(102) | Card number display | |
| MEMBERSHIPID | nvarchar(100) | yes | MEMBERSHIPID |
| NAMEONCARD | nvarchar(700) | NAMEONCARD | |
| MEMBERSINCE | int | yes | MEMBERSINCE |
| ADDRESS | nvarchar(150) | yes | ADDRESS |
| CITY | nvarchar(50) | yes | CITY |
| STATE | nvarchar(50) | yes | STATE |
| ZIP | nvarchar(12) | yes | ZIP |
| 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 |
| PRIMARYADDRESSEE | nvarchar(700) | yes | Primary addressee |
| PRIMARYSALUTATION | nvarchar(700) | yes | Primary solutation |
| LOOKUPID | nvarchar(100) | yes | Lookup ID |
| LASTORGGROUPNAME | nvarchar(100) | Last/Org/Group name | |
| CARDNUMBERDISPLAY | nvarchar(100) | CARDNUMBERDISPLAY | |
| REVENUEAMOUNT | money | yes | Revenue Amount |
| REVENUERECEIPTAMOUNT | money | yes | Revenue Receipt Amount |
| REVENUEREFERENCE | nvarchar(255) | yes | Revenue Reference |
| REVENUEDATE | datetime | yes | Revenue Date |
| MEMBERSHIPBASECURRENCYID | uniqueidentifier | yes | MEMBERSHIPBASECURRENCYID |
| MEMBERSHIPBASECURRENCY | nvarchar(3) | yes | Membership Base Currency |
| REVENUETRANSACTIONCURRENCYID | uniqueidentifier | yes | REVENUETRANSACTIONCURRENCYID |
| REVENUETRANSACTIONCURRENCY | nvarchar(3) | yes | Revenue Base Currency |
| CARDEXPIRATIONDATE | datetime | yes | |
| EMAILADDRESS | UDT_EMAILADDRESS | yes | |
| NUMBEROFCHILDREN | smallint | ||
| ADDONS | nvarchar(max) | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/11/2014 4:19:03 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_MEMBERSHIPCARD_PRINTOUTPUT AS
select
[MEMBERSHIP].[ID],
[MEMBER].[CONSTITUENTID],
[MEMBERSHIP].[GIVENBYID],
[MEMBERSHIP].[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],
(select ML.[NAME]
from dbo.[MEMBERSHIPLEVEL] ML
where ML.[SEQUENCE] = [MEMBERSHIPLEVEL].[SEQUENCE] + 1 and ML.[MEMBERSHIPPROGRAMID] = [MEMBERSHIPPROGRAM].[ID]
) as [NEXTLEVELNAME],
(select MLT.[AMOUNT]
from dbo.[MEMBERSHIPLEVELTERM] MLT inner join dbo.[MEMBERSHIPLEVEL] ML on MLT.LEVELID = ML.ID
where ML.[SEQUENCE] = [MEMBERSHIPLEVEL].[SEQUENCE] + 1 and ML.[MEMBERSHIPPROGRAMID] = [MEMBERSHIPPROGRAM].[ID] and MLT.[TERMCODE] = 0
) as [NEXTLEVELPRICE],
[MEMBERSHIPCARD].[ID] as [MEMBERSHIPCARDID],
'*' + [MEMBERSHIPCARD].[CARDNUMBER] + '*' as [CARDNUMBER],
[MEMBERSHIP].[LOOKUPID] as [MEMBERSHIPID],
[MEMBERSHIPCARD].[NAMEONCARD],
(select top(1) datepart(year, [MEMBERSHIPTRANSACTION].[TRANSACTIONDATE])
from dbo.[MEMBERSHIPTRANSACTION]
where [MEMBERSHIPTRANSACTION].[MEMBERSHIPID] = [MEMBERSHIP].[ID] and [MEMBERSHIPTRANSACTION].[ACTIONCODE] = 0
order by [MEMBERSHIPTRANSACTION].[TRANSACTIONDATE] asc
) as [MEMBERSINCE],
[ADDRESS].ADDRESSBLOCK as [ADDRESS],
[ADDRESS].CITY as [CITY],
dbo.UFN_STATE_GETABBREVIATION([ADDRESS].STATEID) as [STATE],
[ADDRESS].POSTCODE as [ZIP],
[ADDEDBY].[USERNAME] as [ADDEDBY],
[CHANGEDBY].[USERNAME] as [CHANGEDBY],
[MEMBERSHIP].[DATEADDED],
[MEMBERSHIP].[DATECHANGED],
[MEMBERSHIP].[TSLONG],
(select
case
when len(NFSUB.CUSTOMNAME) > 0 then NFSUB.CUSTOMNAME
else dbo.UFN_NAMEFORMAT_GETFORMATTEDNAME(NFSUB.ID)
end
from dbo.NAMEFORMAT NFSUB
where
NFSUB.CONSTITUENTID = CONSTITUENT.ID and
NFSUB.PRIMARYADDRESSEE = 1) as PRIMARYADDRESSEE,
(select
case
when len(NFSUB.CUSTOMNAME) > 0 then NFSUB.CUSTOMNAME
else dbo.UFN_NAMEFORMAT_GETFORMATTEDNAME(NFSUB.ID)
end
from dbo.NAMEFORMAT NFSUB
where
NFSUB.CONSTITUENTID = CONSTITUENT.ID and
NFSUB.PRIMARYSALUTATION = 1) as PRIMARYSALUTATION,
[CONSTITUENT].[LOOKUPID],
[CONSTITUENT].[KEYNAME] AS [LASTORGGROUPNAME],
[MEMBERSHIPCARD].[CARDNUMBER] as [CARDNUMBERDISPLAY],
RSPLIT.TRANSACTIONAMOUNT as REVENUEAMOUNT,
REVENUE.RECEIPTAMOUNT as REVENUERECEIPTAMOUNT,
(select REFERENCE from dbo.REVENUEREFERENCE where ID = REVENUE.ID) as REVENUEREFERENCE,
REVENUE.[DATE] as REVENUEDATE,
[MEMBERSHIPLEVELTERM].BASECURRENCYID as MEMBERSHIPBASECURRENCYID,
dbo.UFN_CURRENCY_GETISO([MEMBERSHIPLEVELTERM].BASECURRENCYID) as MEMBERSHIPBASECURRENCY,
RSPLIT.TRANSACTIONCURRENCYID as REVENUETRANSACTIONCURRENCYID,
dbo.UFN_CURRENCY_GETISO(RSPLIT.TRANSACTIONCURRENCYID) as REVENUETRANSACTIONCURRENCY,
[MEMBERSHIPCARD].[EXPIRATIONDATE] as CARDEXPIRATIONDATE,
[EMAILADDRESS].[EMAILADDRESS],
[MEMBERSHIP].[NUMBEROFCHILDREN],
stuff(
(select ', ' + ADDON.NAME + ' (' + convert(nvarchar(10), MEMBERSHIPADDON.QUANTITY) + ')'
from dbo.MEMBERSHIPADDON
inner join dbo.ADDON
on MEMBERSHIPADDON.ADDONID = ADDON.ID
where MEMBERSHIPADDON.MEMBERSHIPID = MEMBERSHIP.ID
order by ADDON.NAME
for xml path(''), type).value('.', 'nvarchar(max)'),
1, 2, ''
) as ADDONS
/*#EXTENSION*/
from dbo.[MEMBERSHIP]
inner join dbo.[MEMBERSHIPPROGRAM] on dbo.[MEMBERSHIP].[MEMBERSHIPPROGRAMID] = [MEMBERSHIPPROGRAM].[ID]
inner join dbo.[MEMBER] on [MEMBERSHIP].[ID] = [MEMBER].[MEMBERSHIPID]
inner join dbo.[MEMBERSHIPCARD] on [MEMBER].[ID] = [MEMBERSHIPCARD].[MEMBERID]
left outer join dbo.[MEMBERSHIPLEVEL] on [MEMBERSHIPLEVEL].[ID] = [MEMBERSHIP].[MEMBERSHIPLEVELID]
left outer join dbo.[MEMBERSHIPLEVELTERM] on [MEMBERSHIPLEVELTERM].[ID] = [MEMBERSHIP].[MEMBERSHIPLEVELTERMID]
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]
left outer join dbo.[ADDRESS] on [MEMBER].[CONSTITUENTID] = [ADDRESS].[CONSTITUENTID] and [ADDRESS].[ISPRIMARY] = 1
left outer join dbo.[EMAILADDRESS] on [MEMBER].[CONSTITUENTID] = [EMAILADDRESS].[CONSTITUENTID] and [EMAILADDRESS].[ISPRIMARY] = 1
left outer join dbo.MEMBERSHIPTRANSACTION as TRANS on TRANS.ID = ( select top(1) ID
from dbo.MEMBERSHIPTRANSACTION
where
MEMBERSHIPID = MEMBERSHIP.ID and
MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID and
MEMBERSHIPTRANSACTION.ACTIONCODE in (0,1,2,3,5)
order by
TRANSACTIONDATE desc,
DATEADDED desc )
left outer join dbo.REVENUESPLIT as RSPLIT on TRANS.REVENUESPLITID = RSPLIT.ID
left outer join dbo.REVENUE on RSPLIT.REVENUEID = REVENUE.ID