USP_SPONSORSHIPBATCH_ADDNEWCONSTITUENTFROMXML
Saves a new batch sponsorship constituent
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @NEWCONSTITUENT | xml | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @NEWCONSTITUENTID | uniqueidentifier | INOUT |
Definition
Copy
CREATE procedure dbo.USP_SPONSORSHIPBATCH_ADDNEWCONSTITUENTFROMXML
(@NEWCONSTITUENT xml,
@CHANGEAGENTID uniqueidentifier,
@NEWCONSTITUENTID uniqueidentifier output)
as
begin
declare @CURRENTAPPUSERID uniqueidentifier
declare @ISORGANIZATION bit
declare @ISGROUP bit
declare @ISHOUSEHOLD bit
declare @LASTNAME nvarchar(100)
declare @FIRSTNAME nvarchar(50)
declare @MIDDLENAME nvarchar(50)
declare @MAIDENNAME nvarchar(100)
declare @NICKNAME nvarchar(50)
declare @TITLECODEID uniqueidentifier
declare @SUFFIXCODEID uniqueidentifier
declare @GENDERCODE tinyint
declare @BIRTHDATE dbo.UDT_FUZZYDATE
declare @ADDRESS_ADDRESSTYPECODEID uniqueidentifier
declare @ADDRESS_DONOTMAIL bit
declare @ADDRESS_COUNTRYID uniqueidentifier
declare @ADDRESS_STATEID uniqueidentifier
declare @ADDRESS_ADDRESSBLOCK nvarchar(150)
declare @ADDRESS_CITY nvarchar(50)
declare @ADDRESS_POSTCODE nvarchar(12)
declare @PHONE_PHONETYPECODEID uniqueidentifier
declare @PHONE_NUMBER nvarchar(100)
declare @EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier
declare @EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS
declare @MARITALSTATUSCODEID uniqueidentifier
declare @WEBADDRESS dbo.UDT_WEBADDRESS
declare @INDUSTRYCODEID uniqueidentifier
declare @NUMEMPLOYEES int
declare @NUMSUBSIDIARIES int
declare @PARENTCORPID uniqueidentifier
declare @SPOUSEID uniqueidentifier
declare @EXISTINGSPOUSE bit
declare @SPOUSE_LASTNAME nvarchar(100)
declare @SPOUSE_FIRSTNAME nvarchar(50)
declare @SPOUSE_MIDDLENAME nvarchar(50)
declare @SPOUSE_MAIDENNAME nvarchar(100)
declare @SPOUSE_NICKNAME nvarchar(50)
declare @SPOUSE_TITLECODEID uniqueidentifier
declare @SPOUSE_SUFFIXCODEID uniqueidentifier
declare @SPOUSE_GENDERCODE tinyint
declare @SPOUSE_BIRTHDATE dbo.UDT_FUZZYDATE
declare @SPOUSE_RECIPROCALTYPECODEID uniqueidentifier
declare @SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier
declare @SPOUSE_ISSPOUSE bit
declare @COPYPRIMARYINFORMATION bit
declare @COPYPRIMARYINFORMATIONFORHOUSEHOLD bit
declare @SPOUSE_STARTDATE datetime
declare @BUSINESSID uniqueidentifier
declare @EXISTINGBUSINESS bit
declare @BUSINESS_NAME nvarchar(100)
declare @BUSINESS_ADDRESSTYPECODEID uniqueidentifier
declare @BUSINESS_COUNTRYID uniqueidentifier
declare @BUSINESS_STATEID uniqueidentifier
declare @BUSINESS_ADDRESSBLOCK nvarchar(150)
declare @BUSINESS_CITY nvarchar(50)
declare @BUSINESS_POSTCODE nvarchar(12)
declare @BUSINESS_DONOTMAIL bit
declare @BUSINESS_PHONETYPECODEID uniqueidentifier
declare @BUSINESS_NUMBER nvarchar(100)
declare @BUSINESS_RECIPROCALTYPECODEID uniqueidentifier
declare @BUSINESS_RELATIONSHIPTYPECODEID uniqueidentifier
declare @BUSINESS_STARTDATE datetime
declare @ISCONTACT bit
declare @ISPRIMARYCONTACT bit
declare @CONTACTTYPECODEID uniqueidentifier
declare @POSITION nvarchar(50)
declare @ISMATCHINGGIFTRELATIONSHIP bit
declare @GIVESANONYMOUSLY bit
declare @GROUP_DESCRIPTION nvarchar(300)
declare @GROUP_PRIMARYCONTACT uniqueidentifier
declare @GROUP_STARTDATE datetime
declare @GROUP_GROUPTYPEID uniqueidentifier
declare @HOUSEHOLD_PRIMARYCONTACTID uniqueidentifier
declare @HOUSEHOLD_PRIMARYCONTACT_KEYNAME nvarchar(100)
declare @HOUSEHOLD_PRIMARYCONTACT_FIRSTNAME nvarchar(50)
declare @HOUSEHOLD_PRIMARYCONTACT_MIDDLENAME nvarchar(50)
declare @HOUSEHOLD_PRIMARYCONTACT_TITLECODEID uniqueidentifier
declare @HOUSEHOLD_PRIMARYCONTACT_SUFFIXCODEID uniqueidentifier
declare @HOUSEHOLD_PRIMARYCONTACT_COPYHOUSEHOLDCONTACT bit
declare @HOUSEHOLD_SECONDMEMBERID uniqueidentifier
declare @HOUSEHOLD_SECONDMEMBER_KEYNAME nvarchar(100)
declare @HOUSEHOLD_SECONDMEMBER_FIRSTNAME nvarchar(50)
declare @HOUSEHOLD_SECONDMEMBER_MIDDLENAME nvarchar(50)
declare @HOUSEHOLD_SECONDMEMBER_TITLECODEID uniqueidentifier
declare @HOUSEHOLD_SECONDMEMBER_SUFFIXCODEID uniqueidentifier
declare @HOUSEHOLD_SECONDMEMBER_COPYHOUSEHOLDCONTACT bit
declare @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID uniqueidentifier
declare @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID uniqueidentifier
declare @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_STARTDATE datetime
declare @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_ISSPOUSE bit
declare @NODESTRING nvarchar(50)
declare @COUNT int
select @COUNT = count(*) from @NEWCONSTITUENT.nodes('/NEWCONSTITUENT/ITEM') T(c)
if @COUNT is not null and @COUNT > 0
SELECT top 1
@NEWCONSTITUENTID=T.c.value('(NEWCONSTITUENTID)[1]','uniqueidentifier'),
@CURRENTAPPUSERID=T.c.value('(CURRENTAPPUSERID)[1]','uniqueidentifier'),
@ISORGANIZATION=coalesce(T.c.value('(ISORGANIZATION)[1]','bit'), 0),
@ISGROUP=coalesce(T.c.value('(ISGROUP)[1]','bit'), 0),
@ISHOUSEHOLD=coalesce(T.c.value('(ISHOUSEHOLD)[1]','bit'), 0),
@LASTNAME=coalesce(T.c.value('(LASTNAME)[1]','nvarchar(100)'), ''),
@FIRSTNAME=coalesce(T.c.value('(FIRSTNAME)[1]','nvarchar(50)'), ''),
@MIDDLENAME=coalesce(T.c.value('(MIDDLENAME)[1]','nvarchar(50)'), ''),
@MAIDENNAME=coalesce(T.c.value('(MAIDENNAME)[1]','nvarchar(100)'), ''),
@NICKNAME=coalesce(T.c.value('(NICKNAME)[1]','nvarchar(50)'), ''),
@TITLECODEID=T.c.value('(TITLECODEID)[1]','uniqueidentifier'),
@SUFFIXCODEID=T.c.value('(SUFFIXCODEID)[1]','uniqueidentifier'),
@GENDERCODE=Coalesce(T.c.value('(GENDERCODE)[1]','tinyint'), 0),
@BIRTHDATE=coalesce(T.c.value('(BIRTHDATE)[1]','dbo.UDT_FUZZYDATE'), '00000000'),
@ADDRESS_ADDRESSTYPECODEID=T.c.value('(ADDRESS_ADDRESSTYPECODEID)[1]','uniqueidentifier'),
@ADDRESS_DONOTMAIL=coalesce(T.c.value('(ADDRESS_DONOTMAIL)[1]','bit'), 0),
@ADDRESS_COUNTRYID=T.c.value('(ADDRESS_COUNTRYID)[1]','uniqueidentifier'),
@ADDRESS_STATEID=T.c.value('(ADDRESS_STATEID)[1]','uniqueidentifier'),
@ADDRESS_ADDRESSBLOCK=coalesce(T.c.value('(ADDRESS_ADDRESSBLOCK)[1]','nvarchar(150)'), ''),
@ADDRESS_CITY=coalesce(T.c.value('(ADDRESS_CITY)[1]','nvarchar(50)'), ''),
@ADDRESS_POSTCODE=coalesce(T.c.value('(ADDRESS_POSTCODE)[1]','nvarchar(12)'), ''),
@PHONE_PHONETYPECODEID=T.c.value('(PHONE_PHONETYPECODEID)[1]','uniqueidentifier'),
@PHONE_NUMBER=coalesce(T.c.value('(PHONE_NUMBER)[1]','nvarchar(100)'), ''),
@EMAILADDRESS_EMAILADDRESSTYPECODEID=T.c.value('(EMAILADDRESS_EMAILADDRESSTYPECODEID)[1]','uniqueidentifier'),
@EMAILADDRESS_EMAILADDRESS=coalesce(T.c.value('(EMAILADDRESS_EMAILADDRESS)[1]','dbo.UDT_EMAILADDRESS'), ''),
@MARITALSTATUSCODEID=T.c.value('(MARITALSTATUSCODEID)[1]','uniqueidentifier'),
@WEBADDRESS=coalesce(T.c.value('(WEBADDRESS)[1]','dbo.UDT_WEBADDRESS'), ''),
@INDUSTRYCODEID=T.c.value('(INDUSTRYCODEID)[1]','uniqueidentifier'),
@NUMEMPLOYEES=coalesce(T.c.value('(NUMEMPLOYEES)[1]','int'), 0),
@NUMSUBSIDIARIES=coalesce(T.c.value('(NUMSUBSIDIARIES)[1]','int'), 0),
@PARENTCORPID=T.c.value('(PARENTCORPID)[1]','uniqueidentifier'),
@SPOUSEID=T.c.value('(SPOUSEID)[1]','uniqueidentifier'),
@EXISTINGSPOUSE=coalesce(T.c.value('(EXISTINGSPOUSE)[1]','bit'), 0),
@SPOUSE_LASTNAME=coalesce(T.c.value('(SPOUSE_LASTNAME)[1]','nvarchar(100)'), ''),
@SPOUSE_FIRSTNAME=coalesce(T.c.value('(SPOUSE_FIRSTNAME)[1]','nvarchar(50)'), ''),
@SPOUSE_MIDDLENAME=coalesce(T.c.value('(SPOUSE_MIDDLENAME)[1]','nvarchar(50)'), ''),
@SPOUSE_MAIDENNAME=coalesce(T.c.value('(SPOUSE_MAIDENNAME)[1]','nvarchar(100)'), ''),
@SPOUSE_NICKNAME=coalesce(T.c.value('(SPOUSE_NICKNAME)[1]','nvarchar(50)'), ''),
@SPOUSE_TITLECODEID=T.c.value('(SPOUSE_TITLECODEID)[1]','uniqueidentifier'),
@SPOUSE_SUFFIXCODEID=T.c.value('(SPOUSE_SUFFIXCODEID)[1]','uniqueidentifier'),
@SPOUSE_GENDERCODE=coalesce(T.c.value('(SPOUSE_GENDERCODE)[1]','tinyint'), 0),
@SPOUSE_BIRTHDATE=coalesce(T.c.value('(SPOUSE_BIRTHDATE)[1]','dbo.UDT_FUZZYDATE'), '00000000'),
@SPOUSE_RECIPROCALTYPECODEID=coalesce(T.c.value('(SPOUSE_RECIPROCALTYPECODEID)[1]','uniqueidentifier'),T.c.value('(SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST)[1]','uniqueidentifier')),
@SPOUSE_RELATIONSHIPTYPECODEID=coalesce(T.c.value('(SPOUSE_RELATIONSHIPTYPECODEID)[1]','uniqueidentifier'),T.c.value('(SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST)[1]','uniqueidentifier')),
@SPOUSE_ISSPOUSE=T.c.value('(SPOUSE_ISSPOUSE)[1]','bit'),
@COPYPRIMARYINFORMATION=coalesce(T.c.value('(COPYPRIMARYINFORMATION)[1]','bit'), 0),
@COPYPRIMARYINFORMATIONFORHOUSEHOLD=T.c.value('(COPYPRIMARYINFORMATIONFORHOUSEHOLD)[1]','bit'),
@SPOUSE_STARTDATE=T.c.value('(SPOUSE_STARTDATE)[1]','datetime'),
@BUSINESSID=T.c.value('(BUSINESSID)[1]','uniqueidentifier'),
@EXISTINGBUSINESS=coalesce(T.c.value('(EXISTINGBUSINESS)[1]','bit'), 0),
@BUSINESS_NAME=coalesce(T.c.value('(BUSINESS_NAME)[1]','nvarchar(100)'), ''),
@BUSINESS_ADDRESSTYPECODEID=T.c.value('(BUSINESS_ADDRESSTYPECODEID)[1]','uniqueidentifier'),
@BUSINESS_COUNTRYID=T.c.value('(BUSINESS_COUNTRYID)[1]','uniqueidentifier'),
@BUSINESS_STATEID=T.c.value('(BUSINESS_STATEID)[1]','uniqueidentifier'),
@BUSINESS_ADDRESSBLOCK=coalesce(T.c.value('(BUSINESS_ADDRESSBLOCK)[1]','nvarchar(150)'), ''),
@BUSINESS_CITY=coalesce(T.c.value('(BUSINESS_CITY)[1]','nvarchar(50)'), ''),
@BUSINESS_POSTCODE=coalesce(T.c.value('(BUSINESS_POSTCODE)[1]','nvarchar(12)'), ''),
@BUSINESS_DONOTMAIL=coalesce(T.c.value('(BUSINESS_DONOTMAIL)[1]','bit'), 0),
@BUSINESS_PHONETYPECODEID=T.c.value('(BUSINESS_PHONETYPECODEID)[1]','uniqueidentifier'),
@BUSINESS_NUMBER=coalesce(T.c.value('(BUSINESS_NUMBER)[1]','nvarchar(100)'), ''),
@BUSINESS_RECIPROCALTYPECODEID=coalesce(T.c.value('(BUSINESS_RECIPROCALTYPECODEID)[1]','uniqueidentifier'),T.c.value('(BUSINESS_RECIPROCALTYPECODEIDSIMPLELIST)[1]','uniqueidentifier')),
@BUSINESS_RELATIONSHIPTYPECODEID=coalesce(T.c.value('(BUSINESS_RELATIONSHIPTYPECODEID)[1]','uniqueidentifier'),T.c.value('(BUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST)[1]','uniqueidentifier')),
@BUSINESS_STARTDATE=T.c.value('(BUSINESS_STARTDATE)[1]','datetime'),
@ISCONTACT=coalesce(T.c.value('(ISCONTACT)[1]','bit'), 0),
@ISPRIMARYCONTACT=coalesce(T.c.value('(ISPRIMARYCONTACT)[1]','bit'), 0),
@CONTACTTYPECODEID=T.c.value('(CONTACTTYPECODEID)[1]','uniqueidentifier'),
@POSITION=coalesce(T.c.value('(POSITION)[1]','nvarchar(50)'), ''),
@ISMATCHINGGIFTRELATIONSHIP=coalesce(T.c.value('(ISMATCHINGGIFTRELATIONSHIP)[1]','bit'), 0),
@GIVESANONYMOUSLY=coalesce(T.c.value('(GIVESANONYMOUSLY)[1]','bit'), 0),
@GROUP_DESCRIPTION=coalesce(T.c.value('(GROUP_DESCRIPTION)[1]','nvarchar(300)'), ''),
@GROUP_PRIMARYCONTACT=T.c.value('(GROUP_PRIMARYCONTACT)[1]','uniqueidentifier'),
@GROUP_STARTDATE=T.c.value('(GROUP_STARTDATE)[1]','datetime'),
@GROUP_GROUPTYPEID=T.c.value('(GROUP_GROUPTYPEID)[1]','uniqueidentifier'),
@HOUSEHOLD_PRIMARYCONTACTID=T.c.value('(HOUSEHOLD_PRIMARYCONTACTID)[1]','uniqueidentifier'),
@HOUSEHOLD_PRIMARYCONTACT_KEYNAME=T.c.value('(HOUSEHOLD_PRIMARYCONTACT_KEYNAME)[1]','nvarchar(100)'),
@HOUSEHOLD_PRIMARYCONTACT_FIRSTNAME=coalesce(T.c.value('(HOUSEHOLD_PRIMARYCONTACT_FIRSTNAME)[1]','nvarchar(50)'), ''),
@HOUSEHOLD_PRIMARYCONTACT_MIDDLENAME=coalesce(T.c.value('(HOUSEHOLD_PRIMARYCONTACT_MIDDLENAME)[1]','nvarchar(50)'), ''),
@HOUSEHOLD_PRIMARYCONTACT_TITLECODEID=T.c.value('(HOUSEHOLD_PRIMARYCONTACT_TITLECODEID)[1]','uniqueidentifier'),
@HOUSEHOLD_PRIMARYCONTACT_SUFFIXCODEID=T.c.value('(HOUSEHOLD_PRIMARYCONTACT_SUFFIXCODEID)[1]','uniqueidentifier'),
@HOUSEHOLD_PRIMARYCONTACT_COPYHOUSEHOLDCONTACT=coalesce(T.c.value('(HOUSEHOLD_PRIMARYCONTACT_COPYHOUSEHOLDCONTACT)[1]','bit'), 0),
@HOUSEHOLD_SECONDMEMBERID=T.c.value('(HOUSEHOLD_SECONDMEMBERID)[1]','uniqueidentifier'),
@HOUSEHOLD_SECONDMEMBER_KEYNAME=coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_KEYNAME)[1]','nvarchar(100)'), ''),
@HOUSEHOLD_SECONDMEMBER_FIRSTNAME=coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_FIRSTNAME)[1]','nvarchar(50)'), ''),
@HOUSEHOLD_SECONDMEMBER_MIDDLENAME=coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_MIDDLENAME)[1]','nvarchar(50)'), ''),
@HOUSEHOLD_SECONDMEMBER_TITLECODEID=T.c.value('(HOUSEHOLD_SECONDMEMBER_TITLECODEID)[1]','uniqueidentifier'),
@HOUSEHOLD_SECONDMEMBER_SUFFIXCODEID=T.c.value('(HOUSEHOLD_SECONDMEMBER_SUFFIXCODEID)[1]','uniqueidentifier'),
@HOUSEHOLD_SECONDMEMBER_COPYHOUSEHOLDCONTACT=coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_COPYHOUSEHOLDCONTACT)[1]','bit'), 0),
@HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID=coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID)[1]','uniqueidentifier'),T.c.value('(HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEIDSIMPLELIST)[1]','uniqueidentifier')),
@HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID=coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID)[1]','uniqueidentifier'),T.c.value('(HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEIDSIMPLELIST)[1]','uniqueidentifier')),
@HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_STARTDATE=T.c.value('(HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_STARTDATE)[1]','datetime'),
@HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_ISSPOUSE=coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_ISSPOUSE)[1]','bit'), 0)
FROM @NEWCONSTITUENT.nodes('/NEWCONSTITUENT/ITEM') T(c)
else
SELECT top 1
@NEWCONSTITUENTID=T.c.value('(NEWCONSTITUENTID)[1]','uniqueidentifier'),
@CURRENTAPPUSERID=T.c.value('(CURRENTAPPUSERID)[1]','uniqueidentifier'),
@ISORGANIZATION=coalesce(T.c.value('(ISORGANIZATION)[1]','bit'), 0),
@ISGROUP=coalesce(T.c.value('(ISGROUP)[1]','bit'), 0),
@ISHOUSEHOLD=coalesce(T.c.value('(ISHOUSEHOLD)[1]','bit'), 0),
@LASTNAME=coalesce(T.c.value('(LASTNAME)[1]','nvarchar(100)'), ''),
@FIRSTNAME=coalesce(T.c.value('(FIRSTNAME)[1]','nvarchar(50)'), ''),
@MIDDLENAME=coalesce(T.c.value('(MIDDLENAME)[1]','nvarchar(50)'), ''),
@MAIDENNAME=coalesce(T.c.value('(MAIDENNAME)[1]','nvarchar(100)'), ''),
@NICKNAME=coalesce(T.c.value('(NICKNAME)[1]','nvarchar(50)'), ''),
@TITLECODEID=T.c.value('(TITLECODEID)[1]','uniqueidentifier'),
@SUFFIXCODEID=T.c.value('(SUFFIXCODEID)[1]','uniqueidentifier'),
@GENDERCODE=Coalesce(T.c.value('(GENDERCODE)[1]','tinyint'), 0),
@BIRTHDATE=coalesce(T.c.value('(BIRTHDATE)[1]','dbo.UDT_FUZZYDATE'), '00000000'),
@ADDRESS_ADDRESSTYPECODEID=T.c.value('(ADDRESS_ADDRESSTYPECODEID)[1]','uniqueidentifier'),
@ADDRESS_DONOTMAIL=coalesce(T.c.value('(ADDRESS_DONOTMAIL)[1]','bit'), 0),
@ADDRESS_COUNTRYID=T.c.value('(ADDRESS_COUNTRYID)[1]','uniqueidentifier'),
@ADDRESS_STATEID=T.c.value('(ADDRESS_STATEID)[1]','uniqueidentifier'),
@ADDRESS_ADDRESSBLOCK=coalesce(T.c.value('(ADDRESS_ADDRESSBLOCK)[1]','nvarchar(150)'), ''),
@ADDRESS_CITY=coalesce(T.c.value('(ADDRESS_CITY)[1]','nvarchar(50)'), ''),
@ADDRESS_POSTCODE=coalesce(T.c.value('(ADDRESS_POSTCODE)[1]','nvarchar(12)'), ''),
@PHONE_PHONETYPECODEID=T.c.value('(PHONE_PHONETYPECODEID)[1]','uniqueidentifier'),
@PHONE_NUMBER=coalesce(T.c.value('(PHONE_NUMBER)[1]','nvarchar(100)'), ''),
@EMAILADDRESS_EMAILADDRESSTYPECODEID=T.c.value('(EMAILADDRESS_EMAILADDRESSTYPECODEID)[1]','uniqueidentifier'),
@EMAILADDRESS_EMAILADDRESS=coalesce(T.c.value('(EMAILADDRESS_EMAILADDRESS)[1]','dbo.UDT_EMAILADDRESS'), ''),
@MARITALSTATUSCODEID=T.c.value('(MARITALSTATUSCODEID)[1]','uniqueidentifier'),
@WEBADDRESS=coalesce(T.c.value('(WEBADDRESS)[1]','dbo.UDT_WEBADDRESS'), ''),
@INDUSTRYCODEID=T.c.value('(INDUSTRYCODEID)[1]','uniqueidentifier'),
@NUMEMPLOYEES=coalesce(T.c.value('(NUMEMPLOYEES)[1]','int'), 0),
@NUMSUBSIDIARIES=coalesce(T.c.value('(NUMSUBSIDIARIES)[1]','int'), 0),
@PARENTCORPID=T.c.value('(PARENTCORPID)[1]','uniqueidentifier'),
@SPOUSEID=T.c.value('(SPOUSEID)[1]','uniqueidentifier'),
@EXISTINGSPOUSE=coalesce(T.c.value('(EXISTINGSPOUSE)[1]','bit'), 0),
@SPOUSE_LASTNAME=coalesce(T.c.value('(SPOUSE_LASTNAME)[1]','nvarchar(100)'), ''),
@SPOUSE_FIRSTNAME=coalesce(T.c.value('(SPOUSE_FIRSTNAME)[1]','nvarchar(50)'), ''),
@SPOUSE_MIDDLENAME=coalesce(T.c.value('(SPOUSE_MIDDLENAME)[1]','nvarchar(50)'), ''),
@SPOUSE_MAIDENNAME=coalesce(T.c.value('(SPOUSE_MAIDENNAME)[1]','nvarchar(100)'), ''),
@SPOUSE_NICKNAME=coalesce(T.c.value('(SPOUSE_NICKNAME)[1]','nvarchar(50)'), ''),
@SPOUSE_TITLECODEID=T.c.value('(SPOUSE_TITLECODEID)[1]','uniqueidentifier'),
@SPOUSE_SUFFIXCODEID=T.c.value('(SPOUSE_SUFFIXCODEID)[1]','uniqueidentifier'),
@SPOUSE_GENDERCODE=coalesce(T.c.value('(SPOUSE_GENDERCODE)[1]','tinyint'), 0),
@SPOUSE_BIRTHDATE=coalesce(T.c.value('(SPOUSE_BIRTHDATE)[1]','dbo.UDT_FUZZYDATE'), '00000000'),
@SPOUSE_RECIPROCALTYPECODEID=coalesce(T.c.value('(SPOUSE_RECIPROCALTYPECODEID)[1]','uniqueidentifier'),T.c.value('(SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST)[1]','uniqueidentifier')),
@SPOUSE_RELATIONSHIPTYPECODEID=coalesce(T.c.value('(SPOUSE_RELATIONSHIPTYPECODEID)[1]','uniqueidentifier'),T.c.value('(SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST)[1]','uniqueidentifier')),
@SPOUSE_ISSPOUSE=T.c.value('(SPOUSE_ISSPOUSE)[1]','bit'),
@COPYPRIMARYINFORMATION=coalesce(T.c.value('(COPYPRIMARYINFORMATION)[1]','bit'), 0),
@COPYPRIMARYINFORMATIONFORHOUSEHOLD=T.c.value('(COPYPRIMARYINFORMATIONFORHOUSEHOLD)[1]','bit'),
@SPOUSE_STARTDATE=T.c.value('(SPOUSE_STARTDATE)[1]','datetime'),
@BUSINESSID=T.c.value('(BUSINESSID)[1]','uniqueidentifier'),
@EXISTINGBUSINESS=coalesce(T.c.value('(EXISTINGBUSINESS)[1]','bit'), 0),
@BUSINESS_NAME=coalesce(T.c.value('(BUSINESS_NAME)[1]','nvarchar(100)'), ''),
@BUSINESS_ADDRESSTYPECODEID=T.c.value('(BUSINESS_ADDRESSTYPECODEID)[1]','uniqueidentifier'),
@BUSINESS_COUNTRYID=T.c.value('(BUSINESS_COUNTRYID)[1]','uniqueidentifier'),
@BUSINESS_STATEID=T.c.value('(BUSINESS_STATEID)[1]','uniqueidentifier'),
@BUSINESS_ADDRESSBLOCK=coalesce(T.c.value('(BUSINESS_ADDRESSBLOCK)[1]','nvarchar(150)'), ''),
@BUSINESS_CITY=coalesce(T.c.value('(BUSINESS_CITY)[1]','nvarchar(50)'), ''),
@BUSINESS_POSTCODE=coalesce(T.c.value('(BUSINESS_POSTCODE)[1]','nvarchar(12)'), ''),
@BUSINESS_DONOTMAIL=coalesce(T.c.value('(BUSINESS_DONOTMAIL)[1]','bit'), 0),
@BUSINESS_PHONETYPECODEID=T.c.value('(BUSINESS_PHONETYPECODEID)[1]','uniqueidentifier'),
@BUSINESS_NUMBER=coalesce(T.c.value('(BUSINESS_NUMBER)[1]','nvarchar(100)'), ''),
@BUSINESS_RECIPROCALTYPECODEID=coalesce(T.c.value('(BUSINESS_RECIPROCALTYPECODEID)[1]','uniqueidentifier'),T.c.value('(BUSINESS_RECIPROCALTYPECODEIDSIMPLELIST)[1]','uniqueidentifier')),
@BUSINESS_RELATIONSHIPTYPECODEID=coalesce(T.c.value('(BUSINESS_RELATIONSHIPTYPECODEID)[1]','uniqueidentifier'),T.c.value('(BUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST)[1]','uniqueidentifier')),
@BUSINESS_STARTDATE=T.c.value('(BUSINESS_STARTDATE)[1]','datetime'),
@ISCONTACT=coalesce(T.c.value('(ISCONTACT)[1]','bit'), 0),
@ISPRIMARYCONTACT=coalesce(T.c.value('(ISPRIMARYCONTACT)[1]','bit'), 0),
@CONTACTTYPECODEID=T.c.value('(CONTACTTYPECODEID)[1]','uniqueidentifier'),
@POSITION=coalesce(T.c.value('(POSITION)[1]','nvarchar(50)'), ''),
@ISMATCHINGGIFTRELATIONSHIP=coalesce(T.c.value('(ISMATCHINGGIFTRELATIONSHIP)[1]','bit'), 0),
@GIVESANONYMOUSLY=coalesce(T.c.value('(GIVESANONYMOUSLY)[1]','bit'), 0),
@GROUP_DESCRIPTION=coalesce(T.c.value('(GROUP_DESCRIPTION)[1]','nvarchar(300)'), ''),
@GROUP_PRIMARYCONTACT=T.c.value('(GROUP_PRIMARYCONTACT)[1]','uniqueidentifier'),
@GROUP_STARTDATE=T.c.value('(GROUP_STARTDATE)[1]','datetime'),
@GROUP_GROUPTYPEID=T.c.value('(GROUP_GROUPTYPEID)[1]','uniqueidentifier'),
@HOUSEHOLD_PRIMARYCONTACTID=T.c.value('(HOUSEHOLD_PRIMARYCONTACTID)[1]','uniqueidentifier'),
@HOUSEHOLD_PRIMARYCONTACT_KEYNAME=T.c.value('(HOUSEHOLD_PRIMARYCONTACT_KEYNAME)[1]','nvarchar(100)'),
@HOUSEHOLD_PRIMARYCONTACT_FIRSTNAME=coalesce(T.c.value('(HOUSEHOLD_PRIMARYCONTACT_FIRSTNAME)[1]','nvarchar(50)'), ''),
@HOUSEHOLD_PRIMARYCONTACT_MIDDLENAME=coalesce(T.c.value('(HOUSEHOLD_PRIMARYCONTACT_MIDDLENAME)[1]','nvarchar(50)'), ''),
@HOUSEHOLD_PRIMARYCONTACT_TITLECODEID=T.c.value('(HOUSEHOLD_PRIMARYCONTACT_TITLECODEID)[1]','uniqueidentifier'),
@HOUSEHOLD_PRIMARYCONTACT_SUFFIXCODEID=T.c.value('(HOUSEHOLD_PRIMARYCONTACT_SUFFIXCODEID)[1]','uniqueidentifier'),
@HOUSEHOLD_PRIMARYCONTACT_COPYHOUSEHOLDCONTACT=coalesce(T.c.value('(HOUSEHOLD_PRIMARYCONTACT_COPYHOUSEHOLDCONTACT)[1]','bit'), 0),
@HOUSEHOLD_SECONDMEMBERID=T.c.value('(HOUSEHOLD_SECONDMEMBERID)[1]','uniqueidentifier'),
@HOUSEHOLD_SECONDMEMBER_KEYNAME=coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_KEYNAME)[1]','nvarchar(100)'), ''),
@HOUSEHOLD_SECONDMEMBER_FIRSTNAME=coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_FIRSTNAME)[1]','nvarchar(50)'), ''),
@HOUSEHOLD_SECONDMEMBER_MIDDLENAME=coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_MIDDLENAME)[1]','nvarchar(50)'), ''),
@HOUSEHOLD_SECONDMEMBER_TITLECODEID=T.c.value('(HOUSEHOLD_SECONDMEMBER_TITLECODEID)[1]','uniqueidentifier'),
@HOUSEHOLD_SECONDMEMBER_SUFFIXCODEID=T.c.value('(HOUSEHOLD_SECONDMEMBER_SUFFIXCODEID)[1]','uniqueidentifier'),
@HOUSEHOLD_SECONDMEMBER_COPYHOUSEHOLDCONTACT=coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_COPYHOUSEHOLDCONTACT)[1]','bit'), 0),
@HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID=coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID)[1]','uniqueidentifier'),T.c.value('(HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEIDSIMPLELIST)[1]','uniqueidentifier')),
@HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID=coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID)[1]','uniqueidentifier'),T.c.value('(HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEIDSIMPLELIST)[1]','uniqueidentifier')),
@HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_STARTDATE=T.c.value('(HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_STARTDATE)[1]','datetime'),
@HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_ISSPOUSE=coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_ISSPOUSE)[1]','bit'), 0)
FROM @NEWCONSTITUENT.nodes('/NEWDONORCONSTITUENT/ITEM') T(c)
if @NEWCONSTITUENTID is null
set @NEWCONSTITUENTID = newID();
if (@WEBADDRESS is not null) and len(@WEBADDRESS) > 0
begin
declare @WEBADDRESSVALID as bit;
select @WEBADDRESSVALID = dbo.UFN_VALIDWEBADDRESS(@WEBADDRESS);
if @WEBADDRESSVALID = 0
begin
select @WEBADDRESSVALID = dbo.UFN_VALIDWEBADDRESS('http://' + @WEBADDRESS)
if @WEBADDRESSVALID = 1
set @WEBADDRESS = 'http://' + @WEBADDRESS;
end
end
if (@ADDRESS_STATEID is not null or len(@ADDRESS_ADDRESSBLOCK) > 0 or len(@ADDRESS_CITY) > 0 or len(@ADDRESS_POSTCODE) > 0)
if @ADDRESS_COUNTRYID is null
select @ADDRESS_COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT();
if (@BUSINESS_STATEID is not null or len(@BUSINESS_ADDRESSBLOCK) > 0 or len(@BUSINESS_CITY) > 0 or len(@BUSINESS_POSTCODE) > 0)
if @BUSINESS_COUNTRYID is null
select @BUSINESS_COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT();
-- To the user, groups and households are distinct items. However, internally households are a type of groups
-- so set ISGROUP to true if ISHOUSEHOLD is already true.
if @ISGROUP = 0 and @ISHOUSEHOLD = 1
set @ISGROUP = 1
if @ISGROUP = 0
begin
exec USP_BATCHSPONSORSHIPCONSTITUENT_ADD @NEWCONSTITUENTID output,@CURRENTAPPUSERID,@CHANGEAGENTID,@ISORGANIZATION,@LASTNAME,@FIRSTNAME,@MIDDLENAME,@MAIDENNAME,@NICKNAME,@TITLECODEID,@SUFFIXCODEID,@GENDERCODE,@BIRTHDATE,
@ADDRESS_ADDRESSTYPECODEID,@ADDRESS_DONOTMAIL,@ADDRESS_COUNTRYID,@ADDRESS_STATEID,@ADDRESS_ADDRESSBLOCK,@ADDRESS_CITY,@ADDRESS_POSTCODE,
0,'','','',null,null,null,'',0,
@PHONE_PHONETYPECODEID,@PHONE_NUMBER,@EMAILADDRESS_EMAILADDRESSTYPECODEID,@EMAILADDRESS_EMAILADDRESS,
@MARITALSTATUSCODEID,@WEBADDRESS,@INDUSTRYCODEID,@NUMEMPLOYEES,@NUMSUBSIDIARIES,@PARENTCORPID,@SPOUSEID,@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,null,null,null,null,@BUSINESSID,@BUSINESS_NAME,@BUSINESS_ADDRESSTYPECODEID,@BUSINESS_COUNTRYID,@BUSINESS_STATEID,@BUSINESS_ADDRESSBLOCK,@BUSINESS_CITY,
@BUSINESS_POSTCODE,@BUSINESS_DONOTMAIL,
0,'','','',null,null,null,'',0,
@BUSINESS_PHONETYPECODEID,@BUSINESS_NUMBER,@BUSINESS_RECIPROCALTYPECODEID,@BUSINESS_RELATIONSHIPTYPECODEID,@BUSINESS_STARTDATE,
@ISCONTACT,@ISPRIMARYCONTACT,@CONTACTTYPECODEID,@POSITION,@ISMATCHINGGIFTRELATIONSHIP, @COPYPRIMARYINFORMATIONFORHOUSEHOLD, @SPOUSE_ISSPOUSE
end
else
begin
if @ISHOUSEHOLD = 0
begin
-- If @GROUP_PRIMARYCONTACT is set, wrap the constituent in a collection so they'll be added as a member.
declare @MEMBERS xml
if @GROUP_PRIMARYCONTACT is not null
begin
set @MEMBERS = (
select
@GROUP_PRIMARYCONTACT as MEMBERID,
1 as [ISPRIMARY]
for xml raw('ITEM'), type, elements, root('MEMBERS'),binary base64
)
end
exec dbo.USP_BATCHSPONSORSHIP_ADDGROUP
@NEWCONSTITUENTID output,
@NAME = @LASTNAME,
@DESCRIPTION = @GROUP_DESCRIPTION,
@GROUPTYPEID = @GROUP_GROUPTYPEID,
@GIVESANONYMOUSLY = @GIVESANONYMOUSLY,
@ADDRESSTYPECODEID = @ADDRESS_ADDRESSTYPECODEID,
@COUNTRYID = @ADDRESS_COUNTRYID,
@ADDRESSBLOCK = @ADDRESS_ADDRESSBLOCK,
@CITY = @ADDRESS_CITY,
@STATEID = @ADDRESS_STATEID,
@POSTCODE = @ADDRESS_POSTCODE,
@PHONETYPECODEID = @PHONE_PHONETYPECODEID,
@NUMBER = @PHONE_NUMBER,
@EMAILADDRESSTYPECODEID = @EMAILADDRESS_EMAILADDRESSTYPECODEID,
@EMAILADDRESS = @EMAILADDRESS_EMAILADDRESS,
@MEMBERS = @MEMBERS,
@STARTDATE = @GROUP_STARTDATE,
@CHANGEAGENTID = @CHANGEAGENTID
end
else
begin
exec dbo.USP_BATCHSPONSORSHIP_ADDHOUSEHOLD
@NEWCONSTITUENTID output,
@NAME = @LASTNAME,
@DESCRIPTION = @GROUP_DESCRIPTION,
@GIVESANONYMOUSLY = @GIVESANONYMOUSLY,
@ADDRESS_ADDRESSTYPECODEID = @ADDRESS_ADDRESSTYPECODEID,
@ADDRESS_COUNTRYID = @ADDRESS_COUNTRYID,
@ADDRESS_ADDRESSBLOCK = @ADDRESS_ADDRESSBLOCK,
@ADDRESS_CITY = @ADDRESS_CITY,
@ADDRESS_STATEID = @ADDRESS_STATEID,
@ADDRESS_POSTCODE = @ADDRESS_POSTCODE,
@PHONE_PHONETYPECODEID = @PHONE_PHONETYPECODEID,
@PHONE_NUMBER = @PHONE_NUMBER,
@EMAIL_EMAILADDRESSTYPECODEID = @EMAILADDRESS_EMAILADDRESSTYPECODEID,
@EMAIL_EMAILADDRESS = @EMAILADDRESS_EMAILADDRESS,
@PRIMARYCONTACTID = @HOUSEHOLD_PRIMARYCONTACTID,
@PRIMARYCONTACT_KEYNAME = @HOUSEHOLD_PRIMARYCONTACT_KEYNAME,
@PRIMARYCONTACT_FIRSTNAME = @HOUSEHOLD_PRIMARYCONTACT_FIRSTNAME,
@PRIMARYCONTACT_MIDDLENAME = @HOUSEHOLD_PRIMARYCONTACT_MIDDLENAME,
@PRIMARYCONTACT_TITLECODEID = @HOUSEHOLD_PRIMARYCONTACT_TITLECODEID,
@PRIMARYCONTACT_SUFFIXCODEID = @HOUSEHOLD_PRIMARYCONTACT_SUFFIXCODEID,
@PRIMARYCONTACT_COPYHOUSEHOLDCONTACT = @HOUSEHOLD_PRIMARYCONTACT_COPYHOUSEHOLDCONTACT,
@SECONDMEMBERID = @HOUSEHOLD_SECONDMEMBERID,
@SECONDMEMBER_KEYNAME = @HOUSEHOLD_SECONDMEMBER_KEYNAME,
@SECONDMEMBER_FIRSTNAME = @HOUSEHOLD_SECONDMEMBER_FIRSTNAME,
@SECONDMEMBER_MIDDLENAME = @HOUSEHOLD_SECONDMEMBER_MIDDLENAME,
@SECONDMEMBER_TITLECODEID = @HOUSEHOLD_SECONDMEMBER_TITLECODEID,
@SECONDMEMBER_SUFFIXCODEID = @HOUSEHOLD_SECONDMEMBER_SUFFIXCODEID,
@SECONDMEMBER_COPYHOUSEHOLDCONTACT = @HOUSEHOLD_SECONDMEMBER_COPYHOUSEHOLDCONTACT,
@SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID = @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID,
@SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID = @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID,
@SECONDMEMBER_RELATIONSHIP_STARTDATE = @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_STARTDATE,
@SECONDMEMBER_RELATIONSHIP_ISSPOUSE = @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_ISSPOUSE,
@CHANGEAGENTID = @CHANGEAGENTID
end
end
end