Membership Renewal Routine for Non Show-Linked Memberships
1. Allow user to specify an Expiry Date for old memberships.
2. Allow user to specify an Account Status (to determine which old memberships are to be affected e.g. they may not want to pass across any expiring memberships at Entered account status but all the rest).
3. Allow the user to specify an Action Status (that will be applied to all new memberships).
Routine
4. For all memberships with the specified Expiry Date (point 1) and the specified Account Status (or above, excluding Cancelled status), create a copy of the membership records.
• Apply the same Membership Type
• Apply the Account Status of Entered
NB. From what I can ascertain, if the organisation is using non show-linked memberships, there are Account Status records in tblCTStatuses with no ShowID (0) and it is these that are used by these types of membership.Therefore the StatusID required is the one that is ShowID = 0, IsSystemStatus = 1 and StatusDesc = Entered.
• Apply the specified Action Status (point 3)
• Set the Start Date as the previous (copied from) membership + X years
• Set the Expiry Date as the previous (copied from) membership + X years
X = MembershipPeriod_Years field in tblCTMembershipTypes via the associated Membership Type. This is set by the user in Utilities > Membership > Membership Types.
Further field requirements for the new membership records added to tblCTContactMembership are as follows (if a field is not listed then simply copy the data from the old to the new record):
• LastMembershipID = Apply the old MembershipID
• IsPosted = 0
• StatusID = As previous point – use the Entered account status
• ActionStatusID = As previous point – use the specified action status
• LastPayment = 0
• SalesHeaderID = 0
• MembershipNotes = Null
• ReceiptTypeID = 0
• DateAdded = Current Date in correct format
• DateEdited = Null
• ChequeName = Null
• FromDowngrade = 0
• PreChangeSHID = 0
• InvTotal = 0
===Two other tables need to be populated to carry over BACS details from the previous show to the new show
===
Execute these
INSERT INTO
Showbiz.
dbo.
tblCTContactBACSDetails
(
ContactID
,
ShowID
,
LastShowID
,
SortCode
,
AccountNumber
,
AccountName
,
BACSCode
,
DDNumber
,
BankName
,
BankAddress)
Select
ContactID
,
NewShowID
,
ShowID
,
SortCode
,
AccountNumber
,
AccountName
,
BACSCode
,
DDNumber
,
BankName
,
BankAddressfrom tblCTContactBACSDetails where ShowID = 'OldShowID'
and ContactID not In (select ContactID from tblCTContactBACSDetails where ShowID = 'NewShowID')
Insert into tblCTContactBACSSBModuleIDs (
ContactID,
ShowID,
LastShowID,
SBModuleID)
Select ContactID,'NewShowID',ShowID,SBModuleID from tblCTContactBACSSBModuleIDs where ShowID='OldShowID' and
ContactID not in (Select ContactID from tblCTContactBACSSBModuleIDs where ShowID = 'NewShowID')