USP_DATAFORMTEMPLATE_ADD_DAILYSALEITEMMEMBERSHIPFASTTRACK
The save procedure used by the add dataform template "Daily Sale Item Membership Fast Track Add Data Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @SALESORDERID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @LASTNAME | nvarchar(100) | IN | Last name |
| @FIRSTNAME | nvarchar(50) | IN | First name |
| @MIDDLENAME | nvarchar(50) | IN | Middle name |
| @GENDERCODE | tinyint | IN | Gender |
| @BIRTHDATE | UDT_FUZZYDATE | IN | Birth date |
| @ADDRESS_COUNTRYID | uniqueidentifier | IN | Country |
| @ADDRESS_STATEID | uniqueidentifier | IN | State |
| @ADDRESS_ADDRESSBLOCK | nvarchar(150) | IN | Address |
| @ADDRESS_CITY | nvarchar(50) | IN | City |
| @ADDRESS_POSTCODE | nvarchar(12) | IN | ZIP |
| @PHONE_NUMBER | nvarchar(100) | IN | Phone number |
| @EMAILADDRESS_EMAILADDRESS | UDT_EMAILADDRESS | IN | Email address |
| @HASSPOUSE | bit | IN | Add spouse to membership |
| @SPOUSEID | uniqueidentifier | IN | Patron |
| @SPOUSE_LASTNAME | nvarchar(100) | IN | Last name |
| @SPOUSE_FIRSTNAME | nvarchar(50) | IN | First name |
| @SPOUSE_MIDDLENAME | nvarchar(50) | IN | Middle name |
| @SPOUSE_GENDERCODE | tinyint | IN | Gender |
| @SPOUSE_BIRTHDATE | UDT_FUZZYDATE | IN | Birth date |
| @SPOUSE_RECIPROCALTYPECODEID | uniqueidentifier | IN | Reciprocal relationship type |
| @SPOUSE_RELATIONSHIPTYPECODEID | uniqueidentifier | IN | Relationship type |
| @OTHERMEMBERS | xml | IN | Other members |
| @ORDERCONSTITUENTID | uniqueidentifier | IN | |
| @MEMBERSHIPID | uniqueidentifier | IN | Membership |
| @MEMBERSHIPPROGRAMID | uniqueidentifier | IN | Program |
| @MEMBERSHIPLEVELID | uniqueidentifier | IN | Level |
| @MEMBERSHIPLEVELTERMID | uniqueidentifier | IN | Term |
| @MEMBERSHIPLEVELTYPECODEID | uniqueidentifier | IN | Type |
| @NUMBEROFCHILDREN | smallint | IN | No. of children |
| @SPOUSE_PHONE_NUMBER | nvarchar(100) | IN | Phone number |
| @SPOUSE_EMAILADDRESS | UDT_EMAILADDRESS | IN | Email address |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_DAILYSALEITEMMEMBERSHIPFASTTRACK
(
@ID uniqueidentifier = null output,
@CURRENTAPPUSERID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@SALESORDERID uniqueidentifier,
-- Primary member variables
@LASTNAME nvarchar(100),
@FIRSTNAME nvarchar(50) = '',
@MIDDLENAME nvarchar(50) = '',
@GENDERCODE tinyint = 0,
@BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
-- Primary address variables
@ADDRESS_COUNTRYID uniqueidentifier = null,
@ADDRESS_STATEID uniqueidentifier = null,
@ADDRESS_ADDRESSBLOCK nvarchar(150) = '',
@ADDRESS_CITY nvarchar(50) = '',
@ADDRESS_POSTCODE nvarchar(12) = '',
@PHONE_NUMBER nvarchar(100) = '',
@EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
-- Spouse variables
@HASSPOUSE bit = 0,
@SPOUSEID uniqueidentifier = null,
@SPOUSE_LASTNAME nvarchar(100) = '',
@SPOUSE_FIRSTNAME nvarchar(50) = '',
@SPOUSE_MIDDLENAME nvarchar(50) = '',
@SPOUSE_GENDERCODE tinyint = 0,
@SPOUSE_BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
@SPOUSE_RECIPROCALTYPECODEID uniqueidentifier = null,
@SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier = null,
-- Other member variable
@OTHERMEMBERS xml = null,
-- Membership variables
@ORDERCONSTITUENTID uniqueidentifier = null,
@MEMBERSHIPID uniqueidentifier = null,
@MEMBERSHIPPROGRAMID uniqueidentifier = null,
@MEMBERSHIPLEVELID uniqueidentifier = null,
@MEMBERSHIPLEVELTERMID uniqueidentifier = null,
@MEMBERSHIPLEVELTYPECODEID uniqueidentifier = null,
@NUMBEROFCHILDREN smallint = 0,
@SPOUSE_PHONE_NUMBER nvarchar(100) = null,
@SPOUSE_EMAILADDRESS dbo.UDT_EMAILADDRESS = null
)
as
set nocount on;
declare
@CONSTITUENTID uniqueidentifier = @ORDERCONSTITUENTID,
@MAIDENNAME nvarchar(100) = '',
@NICKNAME nvarchar(50) = '',
@TITLECODEID uniqueidentifier = null,
@SUFFIXCODEID uniqueidentifier = null,
@ADDRESS_ADDRESSTYPECODEID uniqueidentifier = null,
@ADDRESS_DONOTMAIL bit = 0,
@PHONE_PHONETYPECODEID uniqueidentifier = null,
@EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null,
@MARITALSTATUSCODEID uniqueidentifier = null,
@SKIP_ADDING_SECURITYGROUPS bit=0,
--Spouse Variables
@EXISTINGSPOUSE bit = 0,
@SPOUSE_MAIDENNAME nvarchar(100) = '',
@SPOUSE_NICKNAME nvarchar(50) = '',
@SPOUSE_TITLECODEID uniqueidentifier = null,
@SPOUSE_SUFFIXCODEID uniqueidentifier = null,
@COPYPRIMARYINFORMATION bit = 1,
@SPOUSE_STARTDATE datetime = null,
@PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = 0,
@PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
@RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = 0,
@RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
--Organization variables
@ORGANIZATIONID uniqueidentifier = null,
@EXISTINGORGANIZATION bit = 0,
@ORGANIZATION_NAME nvarchar(100) = '',
@ORGANIZATION_ADDRESSTYPECODEID uniqueidentifier = null,
@ORGANIZATION_COUNTRYID uniqueidentifier = null,
@ORGANIZATION_STATEID uniqueidentifier = null,
@ORGANIZATION_ADDRESSBLOCK nvarchar(150) = '',
@ORGANIZATION_CITY nvarchar(50) = '',
@ORGANIZATION_POSTCODE nvarchar(12) = '',
@ORGANIZATION_DONOTMAIL bit = 0,
@ORGANIZATION_PHONETYPECODEID uniqueidentifier = null,
@ORGANIZATION_NUMBER nvarchar(100) = '',
@ORGANIZATION_RECIPROCALTYPECODEID uniqueidentifier = null,
@ORGANIZATION_RELATIONSHIPTYPECODEID uniqueidentifier = null,
@ORGANIZATION_STARTDATE datetime = null,
@ORGANIZATION_ENDDATE datetime = null,
@ISCONTACT bit = 0,
@ISPRIMARYCONTACT bit = 0,
@CONTACTTYPECODEID uniqueidentifier = null,
@POSITION nvarchar(50) = '',
@ISMATCHINGGIFTRELATIONSHIP bit = 0,
-- Additional spouse variables
@RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null,
@PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null,
-- Address Validation
@ADDRESS_OMITFROMVALIDATION bit = 0,
@ADDRESS_CART nvarchar(10) = '',
@ADDRESS_DPC nvarchar(8) = '',
@ADDRESS_LOT nvarchar(5) = '',
@ADDRESS_COUNTYCODEID uniqueidentifier = null,
@ADDRESS_CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
@ADDRESS_LASTVALIDATIONATTEMPTDATE datetime = null,
@ADDRESS_VALIDATIONMESSAGE nvarchar(100) = '',
@ADDRESS_CERTIFICATIONDATA integer = 0,
@ORGANIZATION_OMITFROMVALIDATION bit = 0,
@ORGANIZATION_CART nvarchar(10) = '',
@ORGANIZATION_DPC nvarchar(8) = '',
@ORGANIZATION_LOT nvarchar(5) = '',
@ORGANIZATION_COUNTYCODEID uniqueidentifier = null,
@ORGANIZATION_CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
@ORGANIZATION_LASTVALIDATIONATTEMPTDATE datetime = null,
@ORGANIZATION_VALIDATIONMESSAGE nvarchar(100) = '',
@ORGANIZATION_CERTIFICATIONDATA integer = 0,
--Individual's relationship variable
@ISSPOUSERELATIONSHIP bit = 1,
-- Household variables
@HOUSEHOLDCOPYPRIMARYCONTACTINFO bit = 0,
@ADDRESS_DONOTMAILREASONCODEID uniqueidentifier = null,
@ORGANIZATION_DONOTMAILREASONCODEID uniqueidentifier = null,
-- relationship (employee/employer) job info
@JOBCATEGORYCODEID uniqueidentifier = null,
@CAREERLEVELCODEID uniqueidentifier = null,
@ADDRESS_INFOSOURCECODEID uniqueidentifier = null,
@ORGANIZATION_INFOSOURCECODEID uniqueidentifier = null,
@TITLE2CODEID uniqueidentifier = null,
@SUFFIX2CODEID uniqueidentifier = null,
@SPOUSE_TITLE2CODEID uniqueidentifier = null,
@SPOUSE_SUFFIX2CODEID uniqueidentifier = null,
@SKIP_ADDING_SITES bit=0
declare @EXPIRATIONDATE datetime = [dbo].[UFN_MEMBERSHIP_CALCULATEEXPIRATIONDATEBYLEVEL](@MEMBERSHIPLEVELID, @MEMBERSHIPLEVELTERMID, getdate())
declare @OTHERMEMBERSTALBE table (
LASTNAME nvarchar(100),
FIRSTNAME nvarchar(50) default '',
MIDDLENAME nvarchar(50) default '',
GENDERCODE tinyint default 0,
BIRTHDATE dbo.UDT_FUZZYDATE default '00000000',
CONSTITUENTID uniqueidentifier default null,
MEMBERID uniqueidentifier default newid(),
EMAILADDRESS dbo.UDT_EMAILADDRESS,
PHONENUMBER nvarchar(100)
)
declare @OTHERLASTNAME nvarchar(100)
declare @OTHERFIRSTNAME nvarchar(50) = ''
declare @OTHERMIDDLENAME nvarchar(50) = ''
declare @OTHERGENDERCODE tinyint = 0
declare @OTHERBIRTHDATE dbo.UDT_FUZZYDATE = '00000000'
declare @OTHERCONSTITUENTID uniqueidentifier
declare @OTHERPHONENUMBER nvarchar(100)
declare @OTHEREMAILADDRESS dbo.UDT_EMAILADDRESS
declare @MEMBERS xml
declare @PRIMARYMEMBERID uniqueidentifier = newid()
declare @SPOUSEMEMBERID uniqueidentifier = newid()
declare @CARDSALLOWED smallint
if @ID is null
set @ID = newid()
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
begin try
-- Save primary member and spouse
if @CONSTITUENTID is null
exec dbo.USP_INDIVIDUALSPOUSEBUSINESS_ADD
@CONSTITUENTID output,
@CURRENTAPPUSERID,
'9d3aff90-42d0-4db0-a4c1-703d25fd1902',
@CHANGEAGENTID,
@LASTNAME,
@FIRSTNAME,
@MIDDLENAME,
@MAIDENNAME,
@NICKNAME,
@TITLECODEID,
@SUFFIXCODEID,
@GENDERCODE,
@BIRTHDATE,
@ADDRESS_ADDRESSTYPECODEID,
@ADDRESS_DONOTMAIL,
@ADDRESS_COUNTRYID,
@ADDRESS_STATEID,
@ADDRESS_ADDRESSBLOCK,
@ADDRESS_CITY,
@ADDRESS_POSTCODE,
@PHONE_PHONETYPECODEID,
@PHONE_NUMBER,
@EMAILADDRESS_EMAILADDRESSTYPECODEID,
@EMAILADDRESS_EMAILADDRESS,
@MARITALSTATUSCODEID,
@SKIP_ADDING_SECURITYGROUPS,
@SPOUSEID,
@EXISTINGSPOUSE,
@SPOUSE_LASTNAME,
@SPOUSE_FIRSTNAME,
@SPOUSE_MIDDLENAME,
@SPOUSE_MAIDENNAME,
@SPOUSE_NICKNAME,
@SPOUSE_TITLECODEID,
@SPOUSE_SUFFIXCODEID,
@SPOUSE_GENDERCODE,
@SPOUSE_BIRTHDATE,
@SPOUSE_RECIPROCALTYPECODEID,
@SPOUSE_RELATIONSHIPTYPECODEID,
@COPYPRIMARYINFORMATION,
@SPOUSE_STARTDATE,
@PRIMARYSOFTCREDITRELATIONSHIPEXISTS,
@PRIMARYSOFTCREDITMATCHFACTOR,
@RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
@RECIPROCALSOFTCREDITMATCHFACTOR,
@ORGANIZATIONID,
@EXISTINGORGANIZATION,
@ORGANIZATION_NAME,
@ORGANIZATION_ADDRESSTYPECODEID,
@ORGANIZATION_COUNTRYID,
@ORGANIZATION_STATEID,
@ORGANIZATION_ADDRESSBLOCK,
@ORGANIZATION_CITY,
@ORGANIZATION_POSTCODE,
@ORGANIZATION_DONOTMAIL,
@ORGANIZATION_PHONETYPECODEID,
@ORGANIZATION_NUMBER,
@ORGANIZATION_RECIPROCALTYPECODEID,
@ORGANIZATION_RELATIONSHIPTYPECODEID,
@ORGANIZATION_STARTDATE,
@ORGANIZATION_ENDDATE,
@ISCONTACT,
@ISPRIMARYCONTACT,
@CONTACTTYPECODEID,
@POSITION,
@ISMATCHINGGIFTRELATIONSHIP,
@RECIPROCALRECOGNITIONTYPECODEID,
@PRIMARYRECOGNITIONTYPECODEID,
@ADDRESS_OMITFROMVALIDATION,
@ADDRESS_CART,
@ADDRESS_DPC,
@ADDRESS_LOT,
@ADDRESS_COUNTYCODEID,
@ADDRESS_CONGRESSIONALDISTRICTCODEID,
@ADDRESS_LASTVALIDATIONATTEMPTDATE,
@ADDRESS_VALIDATIONMESSAGE,
@ADDRESS_CERTIFICATIONDATA,
@ORGANIZATION_OMITFROMVALIDATION,
@ORGANIZATION_CART,
@ORGANIZATION_DPC,
@ORGANIZATION_LOT,
@ORGANIZATION_COUNTYCODEID,
@ORGANIZATION_CONGRESSIONALDISTRICTCODEID,
@ORGANIZATION_LASTVALIDATIONATTEMPTDATE,
@ORGANIZATION_VALIDATIONMESSAGE,
@ORGANIZATION_CERTIFICATIONDATA,
@ISSPOUSERELATIONSHIP,
@HOUSEHOLDCOPYPRIMARYCONTACTINFO,
@ADDRESS_DONOTMAILREASONCODEID,
@ORGANIZATION_DONOTMAILREASONCODEID,
@SKIP_ADDING_SITES,
@JOBCATEGORYCODEID,
@CAREERLEVELCODEID,
@ADDRESS_INFOSOURCECODEID,
@ORGANIZATION_INFOSOURCECODEID,
@TITLE2CODEID,
@SUFFIX2CODEID,
@SPOUSE_TITLE2CODEID,
@SPOUSE_SUFFIX2CODEID,
1,
null, --@ORGANIZATION_PRIMARYSOFTCREDITRELATIONSHIPEXISTS
null, --@ORGANIZATION_PRIMARYSOFTCREDITMATCHFACTOR
null, --@ORGANIZATION_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS
null, --@ORGANIZATION_RECIPROCALSOFTCREDITMATCHFACTOR
null, --@ORGANIZATION_PRIMARYRECOGNITIONTYPECODEID
null, --@ORGANIZATION_RECIPROCALRECOGNITIONTYPECODEID
@SPOUSE_EMAILADDRESS,
@SPOUSE_PHONE_NUMBER;
else if @SPOUSEID is null and @HASSPOUSE = 1
-- save spouse only
exec dbo.USP_INDIVIDUALSPOUSEBUSINESS_ADD
@SPOUSEID output,
@CURRENTAPPUSERID,
'9d3aff90-42d0-4db0-a4c1-703d25fd1902',
@CHANGEAGENTID,
@SPOUSE_LASTNAME,
@SPOUSE_FIRSTNAME,
@SPOUSE_MIDDLENAME,
@SPOUSE_MAIDENNAME,
@SPOUSE_NICKNAME,
@SPOUSE_TITLECODEID,
@SPOUSE_SUFFIXCODEID,
@SPOUSE_GENDERCODE,
@SPOUSE_BIRTHDATE,
@ADDRESS_ADDRESSTYPECODEID,
@ADDRESS_DONOTMAIL,
@ADDRESS_COUNTRYID,
@ADDRESS_STATEID,
@ADDRESS_ADDRESSBLOCK,
@ADDRESS_CITY,
@ADDRESS_POSTCODE,
@PHONE_PHONETYPECODEID,
@SPOUSE_PHONE_NUMBER,
@EMAILADDRESS_EMAILADDRESSTYPECODEID,
@SPOUSE_EMAILADDRESS,
@MARITALSTATUSCODEID,
@SKIP_ADDING_SECURITYGROUPS,
@CONSTITUENTID,
1, --@EXISTINGSPOUSE,
@LASTNAME,
@FIRSTNAME,
@MIDDLENAME,
@MAIDENNAME,
@NICKNAME,
@TITLECODEID,
@SUFFIXCODEID,
@GENDERCODE,
@BIRTHDATE,
@SPOUSE_RELATIONSHIPTYPECODEID, --@SPOUSE_RECIPROCALTYPECODEID,
@SPOUSE_RECIPROCALTYPECODEID, --@SPOUSE_RELATIONSHIPTYPECODEID,
@COPYPRIMARYINFORMATION,
@SPOUSE_STARTDATE,
@PRIMARYSOFTCREDITRELATIONSHIPEXISTS,
@PRIMARYSOFTCREDITMATCHFACTOR,
@RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
@RECIPROCALSOFTCREDITMATCHFACTOR,
@ORGANIZATIONID,
@EXISTINGORGANIZATION,
@ORGANIZATION_NAME,
@ORGANIZATION_ADDRESSTYPECODEID,
@ORGANIZATION_COUNTRYID,
@ORGANIZATION_STATEID,
@ORGANIZATION_ADDRESSBLOCK,
@ORGANIZATION_CITY,
@ORGANIZATION_POSTCODE,
@ORGANIZATION_DONOTMAIL,
@ORGANIZATION_PHONETYPECODEID,
@ORGANIZATION_NUMBER,
@ORGANIZATION_RECIPROCALTYPECODEID,
@ORGANIZATION_RELATIONSHIPTYPECODEID,
@ORGANIZATION_STARTDATE,
@ORGANIZATION_ENDDATE,
@ISCONTACT,
@ISPRIMARYCONTACT,
@CONTACTTYPECODEID,
@POSITION,
@ISMATCHINGGIFTRELATIONSHIP,
@RECIPROCALRECOGNITIONTYPECODEID,
@PRIMARYRECOGNITIONTYPECODEID,
@ADDRESS_OMITFROMVALIDATION,
@ADDRESS_CART,
@ADDRESS_DPC,
@ADDRESS_LOT,
@ADDRESS_COUNTYCODEID,
@ADDRESS_CONGRESSIONALDISTRICTCODEID,
@ADDRESS_LASTVALIDATIONATTEMPTDATE,
@ADDRESS_VALIDATIONMESSAGE,
@ADDRESS_CERTIFICATIONDATA,
@ORGANIZATION_OMITFROMVALIDATION,
@ORGANIZATION_CART,
@ORGANIZATION_DPC,
@ORGANIZATION_LOT,
@ORGANIZATION_COUNTYCODEID,
@ORGANIZATION_CONGRESSIONALDISTRICTCODEID,
@ORGANIZATION_LASTVALIDATIONATTEMPTDATE,
@ORGANIZATION_VALIDATIONMESSAGE,
@ORGANIZATION_CERTIFICATIONDATA,
@ISSPOUSERELATIONSHIP,
@HOUSEHOLDCOPYPRIMARYCONTACTINFO,
@ADDRESS_DONOTMAILREASONCODEID,
@ORGANIZATION_DONOTMAILREASONCODEID,
@SKIP_ADDING_SITES,
@JOBCATEGORYCODEID,
@CAREERLEVELCODEID,
@ADDRESS_INFOSOURCECODEID,
@ORGANIZATION_INFOSOURCECODEID,
@TITLE2CODEID,
@SUFFIX2CODEID,
@SPOUSE_TITLE2CODEID,
@SPOUSE_SUFFIX2CODEID,
1;
if @HASSPOUSE = 1 and @SPOUSEID is null
select @SPOUSEID = ID from dbo.CONSTITUENT
where KEYNAME = @SPOUSE_LASTNAME and FIRSTNAME = @SPOUSE_FIRSTNAME and MIDDLENAME = @SPOUSE_MIDDLENAME and BIRTHDATE = @SPOUSE_BIRTHDATE and GENDERCODE = @SPOUSE_GENDERCODE
-- Save other members as constituents
insert into @OTHERMEMBERSTALBE (LASTNAME, FIRSTNAME, MIDDLENAME, BIRTHDATE, GENDERCODE, CONSTITUENTID, PHONENUMBER, EMAILADDRESS)
select
T.c.value('(OTHER_LASTNAME)[1]','nvarchar(100)'),
T.c.value('(OTHER_FIRSTNAME)[1]','nvarchar(50)'),
T.c.value('(OTHER_MIDDLENAME)[1]','nvarchar(50)'),
T.c.value('(OTHER_BIRTHDATE)[1]','dbo.UDT_FUZZYDATE'),
T.c.value('(OTHER_GENDERCODE)[1]','tinyint'),
case when T.c.value('(OTHER_ISNEW)[1]','bit') = 0 then
T.c.value('(OTHER_ID)[1]','uniqueidentifier')
else
null
end,
T.c.value('(OTHER_PHONE_NUMBER)[1]','nvarchar(100)'),
T.c.value('(OTHER_EMAILADDRESS)[1]','dbo.UDT_EMAILADDRESS')
from @OTHERMEMBERS.nodes('/OTHERMEMBERS/ITEM') T(c)
declare curOther cursor local fast_forward for
select LASTNAME, FIRSTNAME, MIDDLENAME, BIRTHDATE, GENDERCODE, EMAILADDRESS, PHONENUMBER
from @OTHERMEMBERSTALBE
where [CONSTITUENTID] is null;
open curOther
fetch next from curOther into @OTHERLASTNAME, @OTHERFIRSTNAME, @OTHERMIDDLENAME, @OTHERBIRTHDATE, @OTHERGENDERCODE, @OTHEREMAILADDRESS, @OTHERPHONENUMBER
while @@fetch_status = 0
begin
set @OTHERCONSTITUENTID = null;
exec dbo.USP_INDIVIDUALSPOUSEBUSINESS_ADD
@OTHERCONSTITUENTID output,
@CURRENTAPPUSERID,
'9d3aff90-42d0-4db0-a4c1-703d25fd1902',
@CHANGEAGENTID,
@OTHERLASTNAME,
@OTHERFIRSTNAME,
@OTHERMIDDLENAME,
@MAIDENNAME,
@NICKNAME,
@TITLECODEID,
@SUFFIXCODEID,
@OTHERGENDERCODE,
@OTHERBIRTHDATE,
@ADDRESS_ADDRESSTYPECODEID,
@ADDRESS_DONOTMAIL,
null, --@ADDRESS_COUNTRYID,
null, --@ADDRESS_STATEID,
'', --@ADDRESS_ADDRESSBLOCK,
'', --@ADDRESS_CITY,
'', --@ADDRESS_POSTCODE,
@PHONE_PHONETYPECODEID,
@OTHERPHONENUMBER,
@EMAILADDRESS_EMAILADDRESSTYPECODEID,
@OTHEREMAILADDRESS,
@MARITALSTATUSCODEID,
@SKIP_ADDING_SECURITYGROUPS,
null, --@SPOUSEID,
@EXISTINGSPOUSE,
'', --@SPOUSE_LASTNAME,
'', --@SPOUSE_FIRSTNAME,
'', --@SPOUSE_MIDDLENAME,
@SPOUSE_MAIDENNAME,
@SPOUSE_NICKNAME,
@SPOUSE_TITLECODEID,
@SPOUSE_SUFFIXCODEID,
0, --@SPOUSE_GENDERCODE,
'00000000', --@SPOUSE_BIRTHDATE,
@SPOUSE_RECIPROCALTYPECODEID,
@SPOUSE_RELATIONSHIPTYPECODEID,
@COPYPRIMARYINFORMATION,
@SPOUSE_STARTDATE,
@PRIMARYSOFTCREDITRELATIONSHIPEXISTS,
@PRIMARYSOFTCREDITMATCHFACTOR,
@RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
@RECIPROCALSOFTCREDITMATCHFACTOR,
@ORGANIZATIONID,
@EXISTINGORGANIZATION,
@ORGANIZATION_NAME,
@ORGANIZATION_ADDRESSTYPECODEID,
@ORGANIZATION_COUNTRYID,
@ORGANIZATION_STATEID,
@ORGANIZATION_ADDRESSBLOCK,
@ORGANIZATION_CITY,
@ORGANIZATION_POSTCODE,
@ORGANIZATION_DONOTMAIL,
@ORGANIZATION_PHONETYPECODEID,
@ORGANIZATION_NUMBER,
@ORGANIZATION_RECIPROCALTYPECODEID,
@ORGANIZATION_RELATIONSHIPTYPECODEID,
@ORGANIZATION_STARTDATE,
@ORGANIZATION_ENDDATE,
@ISCONTACT,
@ISPRIMARYCONTACT,
@CONTACTTYPECODEID,
@POSITION,
@ISMATCHINGGIFTRELATIONSHIP,
@RECIPROCALRECOGNITIONTYPECODEID,
@PRIMARYRECOGNITIONTYPECODEID,
@ADDRESS_OMITFROMVALIDATION,
@ADDRESS_CART,
@ADDRESS_DPC,
@ADDRESS_LOT,
@ADDRESS_COUNTYCODEID,
@ADDRESS_CONGRESSIONALDISTRICTCODEID,
@ADDRESS_LASTVALIDATIONATTEMPTDATE,
@ADDRESS_VALIDATIONMESSAGE,
@ADDRESS_CERTIFICATIONDATA,
@ORGANIZATION_OMITFROMVALIDATION,
@ORGANIZATION_CART,
@ORGANIZATION_DPC,
@ORGANIZATION_LOT,
@ORGANIZATION_COUNTYCODEID,
@ORGANIZATION_CONGRESSIONALDISTRICTCODEID,
@ORGANIZATION_LASTVALIDATIONATTEMPTDATE,
@ORGANIZATION_VALIDATIONMESSAGE,
@ORGANIZATION_CERTIFICATIONDATA,
@ISSPOUSERELATIONSHIP,
@HOUSEHOLDCOPYPRIMARYCONTACTINFO,
@ADDRESS_DONOTMAILREASONCODEID,
@ORGANIZATION_DONOTMAILREASONCODEID,
@SKIP_ADDING_SITES,
@JOBCATEGORYCODEID,
@CAREERLEVELCODEID,
@ADDRESS_INFOSOURCECODEID,
@ORGANIZATION_INFOSOURCECODEID,
@TITLE2CODEID,
@SUFFIX2CODEID,
@SPOUSE_TITLE2CODEID,
@SPOUSE_SUFFIX2CODEID,
1;
update @OTHERMEMBERSTALBE set CONSTITUENTID = @OTHERCONSTITUENTID
where LASTNAME = @OTHERLASTNAME and FIRSTNAME = @OTHERFIRSTNAME and MIDDLENAME = @OTHERMIDDLENAME
fetch next from curOther into @OTHERLASTNAME, @OTHERFIRSTNAME, @OTHERMIDDLENAME, @OTHERBIRTHDATE, @OTHERGENDERCODE, @OTHEREMAILADDRESS, @OTHERPHONENUMBER
end
close curOther
deallocate curOther
-- Save membership
select @CARDSALLOWED = coalesce(CARDSALLOWED, 0) from dbo.MEMBERSHIPLEVEL where ID = @MEMBERSHIPLEVELID
declare @MEMBERSTABLE table (
NUMBER int identity(1, 1),
ID uniqueidentifier,
CONSTITUENTID uniqueidentifier,
ISPRIMARY bit,
MEMBERSHIPCARDS xml
)
insert into @MEMBERSTABLE (ID, CONSTITUENTID, ISPRIMARY, MEMBERSHIPCARDS)
select @PRIMARYMEMBERID as ID,
@CONSTITUENTID as CONSTITUENTID,
1 as ISPRIMARY,
(select newid() as ID,
(select NAME from dbo.CONSTITUENT where ID = @CONSTITUENTID) as NAMEONCARD,
@EXPIRATIONDATE as EXPIRATIONDATE,
null as MEMBERSHIPCARDID
for xml raw('ITEM'),type,elements,BINARY BASE64) as MEMBERSHIPCARDS
union all
select @SPOUSEMEMBERID,
@SPOUSEID,
0,
(select newid() as ID,
(select NAME from dbo.CONSTITUENT where ID = @SPOUSEID) as NAMEONCARD,
@EXPIRATIONDATE as EXPIRATIONDATE,
null as MEMBERSHIPCARDID
for xml raw('ITEM'),type,elements,BINARY BASE64)
where @HASSPOUSE = 1
union all
select MEMBERID,
CONSTITUENTID,
0,
(select newid() as ID,
(select NAME from dbo.CONSTITUENT where ID = OTHERMEMBERSTABLE.CONSTITUENTID) as NAMEONCARD,
@EXPIRATIONDATE as EXPIRATIONDATE,
null as MEMBERSHIPCARDID
for xml raw('ITEM'),type,elements,BINARY BASE64)
from @OTHERMEMBERSTALBE OTHERMEMBERSTABLE
update @MEMBERSTABLE set MEMBERSHIPCARDS = null where (NUMBER > @CARDSALLOWED) -- and @CARDSALLOWED > 0)
set @MEMBERS = (
select ID, CONSTITUENTID, ISPRIMARY, MEMBERSHIPCARDS, null as MEMBERID
from @MEMBERSTABLE
for xml raw('ITEM'),type,elements,root('MEMBERS'),BINARY BASE64
)
exec dbo.USP_SALESORDERITEMMEMBERSHIP_ADD
@ID output,
@CHANGEAGENTID,
@SALESORDERID,
@MEMBERSHIPID,
@MEMBERSHIPPROGRAMID,
@MEMBERSHIPLEVELID,
@MEMBERSHIPLEVELTERMID,
@MEMBERSHIPLEVELTYPECODEID,
@NUMBEROFCHILDREN,
'', --@COMMENTS,
0, --@ISGIFT,
1, --@SENDRENEWALCODE,
@EXPIRATIONDATE,
@MEMBERS,
null, --@GIVENBYID,
0;
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0