MEMBERSHIPTRANSACTION

Stores transaction records that occur for a membership.

Primary Key

Primary Key	Field Type
ID	uniqueidentifier

Fields

Field	Field Type	Null	Notes	Description
TRANSACTIONDATE	datetime			The date of this membership transaction.
EXPIRATIONDATE	datetime	yes		The expiration date for the membership set by this transaction.
ISGIFT	bit		Default = 0	Indicates that this membership transaction was purchased by a constituent other than the member.
NUMBEROFCHILDREN	smallint		Default = ((0))	The number of children allowed on this membership.
COMMENTS	nvarchar(1000)		Default = ''	Remarks about this membership.
ACTIONCODE	tinyint		Default = 0	The action of this transaction on the membership.
DATEADDED	datetime		Default = getdate()	Indicates the date this record was added.
DATECHANGED	datetime		Default = getdate()	Indicates the date this record was last changed.
TS	timestamp			Timestamp.
TSLONG	bigint (Computed)	yes	CONVERT(bigint, TS)	Numeric representation of the timestamp.
ACTION	nvarchar(9) (Computed)	yes	CASE [ACTIONCODE] WHEN 0 THEN N'Join' WHEN 1 THEN N'Renew' WHEN 2 THEN N'Upgrade' WHEN 3 THEN N'Downgrade' WHEN 4 THEN N'Drop' WHEN 5 THEN N'Rejoin' WHEN 6 THEN N'Cancel' END
BASEAMOUNT	money		Default = 0
UPGRADEMETHODCODE	tinyint		Default = 0
UPGRADEMETHOD	nvarchar(15) (Computed)	yes	CASE [UPGRADEMETHODCODE] WHEN 0 THEN N'Not an upgrade' WHEN 1 THEN N'Renewal upgrade' WHEN 2 THEN N'Midterm upgrade' END

Foreign Keys

Foreign Key	Field Type	Null	Notes	Description
MEMBERSHIPID	uniqueidentifier		MEMBERSHIP.ID	FK to MEMBERSHIP
MEMBERSHIPLEVELID	uniqueidentifier		MEMBERSHIPLEVEL.ID	FK to MEMBERSHIPLEVEL
MEMBERSHIPLEVELTERMID	uniqueidentifier		MEMBERSHIPLEVELTERM.ID	FK to MEMBERSHIPLEVELTERM
DONORID	uniqueidentifier	yes	CONSTITUENT.LOCALID	The constituent who purchased this membership as a gift.
ADDEDBYID	uniqueidentifier		CHANGEAGENT.ID	FK to CHANGEAGENT.
CHANGEDBYID	uniqueidentifier		CHANGEAGENT.ID	FK to CHANGEAGENT.
MEMBERSHIPLEVELTYPECODEID	uniqueidentifier	yes	MEMBERSHIPLEVELTYPECODE.ID	The type of membership.
REVENUESPLITID	uniqueidentifier	yes	FINANCIALTRANSACTIONLINEITEM.ID	The REVENUESPLIT record that is linked to this membership transaction.
MEMBERSHIPPROMOID	uniqueidentifier	yes	MEMBERSHIPPROMO.ID

Indexes

Index Name	Fields	Unique	Primary	Clustered
IX_MEMBERSHIPTRANSACTION_DATEADDED	DATEADDED			yes
IX_MEMBERSHIPTRANSACTION_DATECHANGED	DATECHANGED
IX_MEMBERSHIPTRANSACTION_DONORID	DONORID
IX_MEMBERSHIPTRANSACTION_MEMBERSHIPID	MEMBERSHIPID
IX_MEMBERSHIPTRANSACTION_MEMBERSHIPLEVELID_TRANSACTIONDATE_ACTIONCODE	MEMBERSHIPLEVELID, TRANSACTIONDATE, ACTIONCODE
IX_MEMBERSHIPTRANSACTION_REVENUESPLITID	REVENUESPLITID
IX_MEMBERSHIPTRANSACTION_TRANSACTIONDATE_DATEADDED	TRANSACTIONDATE, DATEADDED
PK_MEMBERSHIPTRANSACTION	ID	yes	yes

Triggers

Trigger Name	Description
TR_MEMBERSHIPTRANSACTION_AUDIT_UPDATE
TR_MEMBERSHIPTRANSACTION_AUDIT_DELETE
TR_MEMBERSHIPTRANSACTION_AUDIT_ETLDELETEDID

Referenced by

Referenced by	Field
MEMBERSHIPADDON	MEMBERSHIPTRANSACTIONID
MEMBERSHIPCONTRIBUTIONPORTION	MEMBERSHIPTRANSACTIONID
MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE	ORIGINALMEMBERSHIPTRANSACTIONID
MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE	CURRENTMEMBERSHIPTRANSACTIONID
SALESORDERITEMMEMBERSHIP	MEMBERSHIPTRANSACTIONID
SALESORDERITEMMEMBERSHIPADDON	MEMBERSHIPTRANSACTIONID