USP_BATCHSPONSORSHIPCONSTITUENT_LOAD
Loads a batch constituent record with spouse and business information.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | INOUT | |
| @ISORGANIZATION | bit | INOUT | |
| @KEYNAME | nvarchar(100) | INOUT | |
| @KEYNAMEPREFIX | nvarchar(50) | INOUT | |
| @FIRSTNAME | nvarchar(50) | INOUT | |
| @MIDDLENAME | nvarchar(50) | INOUT | |
| @MAIDENNAME | nvarchar(100) | INOUT | |
| @NICKNAME | nvarchar(50) | INOUT | |
| @TITLECODEID | uniqueidentifier | INOUT | |
| @SUFFIXCODEID | uniqueidentifier | INOUT | |
| @GENDERCODE | tinyint | INOUT | |
| @BIRTHDATE | UDT_FUZZYDATE | INOUT | |
| @ADDRESSTYPECODEID | uniqueidentifier | INOUT | |
| @DONOTMAIL | bit | INOUT | |
| @COUNTRYID | uniqueidentifier | INOUT | |
| @STATEID | uniqueidentifier | INOUT | |
| @ADDRESSBLOCK | nvarchar(150) | INOUT | |
| @CITY | nvarchar(50) | INOUT | |
| @POSTCODE | nvarchar(12) | INOUT | |
| @OMITFROMVALIDATION | bit | INOUT | |
| @CART | nvarchar(10) | INOUT | |
| @DPC | nvarchar(8) | INOUT | |
| @LOT | nvarchar(5) | INOUT | |
| @COUNTYCODEID | uniqueidentifier | INOUT | |
| @CONGRESSIONALDISTRICTCODEID | uniqueidentifier | INOUT | |
| @LASTVALIDATIONATTEMPTDATE | datetime | INOUT | |
| @VALIDATIONMESSAGE | nvarchar(200) | INOUT | |
| @CERTIFICATIONDATA | int | INOUT | |
| @PHONETYPECODEID | uniqueidentifier | INOUT | |
| @NUMBER | nvarchar(100) | INOUT | |
| @EMAILADDRESSTYPECODEID | uniqueidentifier | INOUT | |
| @EMAILADDRESS | UDT_EMAILADDRESS | INOUT | |
| @MARITALSTATUSCODEID | uniqueidentifier | INOUT | |
| @WEBADDRESS | UDT_WEBADDRESS | INOUT | |
| @INDUSTRYCODEID | uniqueidentifier | INOUT | |
| @NUMEMPLOYEES | int | INOUT | |
| @NUMSUBSIDIARIES | int | INOUT | |
| @PARENTCORPID | uniqueidentifier | INOUT | |
| @BATCHSPOUSEID | uniqueidentifier | INOUT | |
| @BATCHSPOUSERELATIONID | uniqueidentifier | INOUT | |
| @SPOUSEID | uniqueidentifier | INOUT | |
| @EXISTINGSPOUSE | bit | INOUT | |
| @SPOUSE_LASTNAME | nvarchar(100) | INOUT | |
| @SPOUSE_FIRSTNAME | nvarchar(50) | INOUT | |
| @SPOUSE_MIDDLENAME | nvarchar(50) | INOUT | |
| @SPOUSE_MAIDENNAME | nvarchar(100) | INOUT | |
| @SPOUSE_NICKNAME | nvarchar(50) | INOUT | |
| @SPOUSE_TITLECODEID | uniqueidentifier | INOUT | |
| @SPOUSE_SUFFIXCODEID | uniqueidentifier | INOUT | |
| @SPOUSE_GENDERCODE | tinyint | INOUT | |
| @SPOUSE_BIRTHDATE | UDT_FUZZYDATE | INOUT | |
| @SPOUSE_RECIPROCALTYPECODEID | uniqueidentifier | INOUT | |
| @SPOUSE_RELATIONSHIPTYPECODEID | uniqueidentifier | INOUT | |
| @COPYPRIMARYINFORMATION | bit | INOUT | |
| @SPOUSE_STARTDATE | datetime | INOUT | |
| @PRIMARYRELATIONSHIPEXISTS | bit | INOUT | |
| @PRIMARYMATCHFACTOR | decimal(5, 2) | INOUT | |
| @RECIPROCALRELATIONSHIPEXISTS | bit | INOUT | |
| @RECIPROCALMATCHFACTOR | decimal(5, 2) | INOUT | |
| @BATCHORGANIZATIONID | uniqueidentifier | INOUT | |
| @BATCHORGANIZATIONRELATIONID | uniqueidentifier | INOUT | |
| @ORGANIZATIONID | uniqueidentifier | INOUT | |
| @EXISTINGORGANIZATION | bit | INOUT | |
| @ORGANIZATION_NAME | nvarchar(100) | INOUT | |
| @ORGANIZATION_KEYNAMEPREFIX | nvarchar(50) | INOUT | |
| @ORGANIZATION_ADDRESSTYPECODEID | uniqueidentifier | INOUT | |
| @ORGANIZATION_COUNTRYID | uniqueidentifier | INOUT | |
| @ORGANIZATION_STATEID | uniqueidentifier | INOUT | |
| @ORGANIZATION_ADDRESSBLOCK | nvarchar(150) | INOUT | |
| @ORGANIZATION_CITY | nvarchar(50) | INOUT | |
| @ORGANIZATION_POSTCODE | nvarchar(12) | INOUT | |
| @ORGANIZATION_DONOTMAIL | bit | INOUT | |
| @ORGANIZATION_OMITFROMVALIDATION | bit | INOUT | |
| @ORGANIZATION_CART | nvarchar(10) | INOUT | |
| @ORGANIZATION_DPC | nvarchar(8) | INOUT | |
| @ORGANIZATION_LOT | nvarchar(5) | INOUT | |
| @ORGANIZATION_COUNTYCODEID | uniqueidentifier | INOUT | |
| @ORGANIZATION_CONGRESSIONALDISTRICTCODEID | uniqueidentifier | INOUT | |
| @ORGANIZATION_LASTVALIDATIONATTEMPTDATE | datetime | INOUT | |
| @ORGANIZATION_VALIDATIONMESSAGE | nvarchar(200) | INOUT | |
| @ORGANIZATION_CERTIFICATIONDATA | int | INOUT | |
| @ORGANIZATION_PHONETYPECODEID | uniqueidentifier | INOUT | |
| @ORGANIZATION_NUMBER | nvarchar(100) | INOUT | |
| @ORGANIZATION_RECIPROCALTYPECODEID | uniqueidentifier | INOUT | |
| @ORGANIZATION_RELATIONSHIPTYPECODEID | uniqueidentifier | INOUT | |
| @ORGANIZATION_STARTDATE | datetime | INOUT | |
| @ISCONTACT | bit | INOUT | |
| @ISPRIMARYCONTACT | bit | INOUT | |
| @CONTACTTYPECODEID | uniqueidentifier | INOUT | |
| @POSITION | nvarchar(50) | INOUT | |
| @ISMATCHINGGIFTRELATIONSHIP | bit | INOUT | |
| @ISGROUP | bit | INOUT | |
| @ISHOUSEHOLD | bit | INOUT | |
| @GIVESANONYMOUSLY | bit | INOUT | |
| @GROUPTYPECODE | tinyint | INOUT | |
| @GROUPTYPEID | uniqueidentifier | INOUT | |
| @GROUPDESCRIPTION | nvarchar(300) | INOUT | |
| @GROUPSTARTDATE | datetime | INOUT | |
| @GROUPPRIMARYCONTACTID | uniqueidentifier | INOUT | |
| @GROUPMEMBERS | xml | INOUT | |
| @PRIMARYCONTACTID | uniqueidentifier | INOUT | |
| @PRIMARYCONTACT_KEYNAME | nvarchar(100) | INOUT | |
| @PRIMARYCONTACT_FIRSTNAME | nvarchar(50) | INOUT | |
| @PRIMARYCONTACT_MIDDLENAME | nvarchar(50) | INOUT | |
| @PRIMARYCONTACT_TITLECODEID | uniqueidentifier | INOUT | |
| @PRIMARYCONTACT_SUFFIXCODEID | uniqueidentifier | INOUT | |
| @PRIMARYCONTACT_COPYHOUSEHOLDCONTACT | bit | INOUT | |
| @SECONDMEMBERID | uniqueidentifier | INOUT | |
| @SECONDMEMBER_KEYNAME | nvarchar(100) | INOUT | |
| @SECONDMEMBER_FIRSTNAME | nvarchar(50) | INOUT | |
| @SECONDMEMBER_MIDDLENAME | nvarchar(50) | INOUT | |
| @SECONDMEMBER_TITLECODEID | uniqueidentifier | INOUT | |
| @SECONDMEMBER_SUFFIXCODEID | uniqueidentifier | INOUT | |
| @SECONDMEMBER_COPYHOUSEHOLDCONTACT | bit | INOUT | |
| @SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID | uniqueidentifier | INOUT | |
| @SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID | uniqueidentifier | INOUT | |
| @SECONDMEMBER_RELATIONSHIP_STARTDATE | datetime | INOUT | |
| @SECONDMEMBER_RELATIONSHIP_ISSPOUSE | bit | INOUT | |
| @BATCHHOUSEHOLDID | uniqueidentifier | INOUT | |
| @HOUSEHOLDID | uniqueidentifier | INOUT | |
| @HOUSEHOLD_NAME | nvarchar(100) | INOUT | |
| @ADDSPOUSETOHOUSEHOLD | bit | INOUT | |
| @HOUSEHOLDCOPYPRIMARYCONTACTINFO | bit | INOUT | |
| @ISSPOUSERELATIONSHIP | bit | INOUT | |
| @DONOTMAILREASONCODEID | uniqueidentifier | INOUT | |
| @ORGANIZATION_DONOTMAILREASONCODEID | uniqueidentifier | INOUT | |
| @NAMEFORMATFUNCTIONID | uniqueidentifier | INOUT | |
| @JOBCATEGORYCODEID | uniqueidentifier | INOUT | |
| @CAREERLEVELCODEID | uniqueidentifier | INOUT |
Definition
Copy
CREATE procedure dbo.USP_BATCHSPONSORSHIPCONSTITUENT_LOAD
(
@ID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier = null output,
@ISORGANIZATION bit = null output,
@KEYNAME nvarchar(100) = null output,
@KEYNAMEPREFIX nvarchar(50) = null output,
@FIRSTNAME nvarchar(50) = null output,
@MIDDLENAME nvarchar(50) = null output,
@MAIDENNAME nvarchar(100) = null output,
@NICKNAME nvarchar(50) = null output,
@TITLECODEID uniqueidentifier = null output,
@SUFFIXCODEID uniqueidentifier = null output,
@GENDERCODE tinyint = null output,
@BIRTHDATE dbo.UDT_FUZZYDATE = null output,
@ADDRESSTYPECODEID uniqueidentifier = null output,
@DONOTMAIL bit = null output,
@COUNTRYID uniqueidentifier = null output,
@STATEID uniqueidentifier = null output,
@ADDRESSBLOCK nvarchar(150) = null output,
@CITY nvarchar(50) = null output,
@POSTCODE nvarchar(12) = null output,
-- Address Validation
@OMITFROMVALIDATION bit = null output,
@CART nvarchar(10) = null output,
@DPC nvarchar(8) = null output,
@LOT nvarchar(5) = null output,
@COUNTYCODEID uniqueidentifier = null output,
@CONGRESSIONALDISTRICTCODEID uniqueidentifier = null output,
@LASTVALIDATIONATTEMPTDATE datetime = null output,
@VALIDATIONMESSAGE nvarchar(200) = null output,
@CERTIFICATIONDATA integer = null output,
@PHONETYPECODEID uniqueidentifier = null output,
@NUMBER nvarchar(100) = null output,
@EMAILADDRESSTYPECODEID uniqueidentifier = null output,
@EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
@MARITALSTATUSCODEID uniqueidentifier = null output,
@WEBADDRESS dbo.UDT_WEBADDRESS = null output,
@INDUSTRYCODEID uniqueidentifier = null output,
@NUMEMPLOYEES int = null output,
@NUMSUBSIDIARIES int = null output,
@PARENTCORPID uniqueidentifier = null output,
--Spouse Variables
@BATCHSPOUSEID uniqueidentifier = null output,
@BATCHSPOUSERELATIONID uniqueidentifier = null output,
@SPOUSEID uniqueidentifier = null output,
@EXISTINGSPOUSE bit = null output,
@SPOUSE_LASTNAME nvarchar(100) = null output,
@SPOUSE_FIRSTNAME nvarchar(50) = null output,
@SPOUSE_MIDDLENAME nvarchar(50) = null output,
@SPOUSE_MAIDENNAME nvarchar(100) = null output,
@SPOUSE_NICKNAME nvarchar(50) = null output,
@SPOUSE_TITLECODEID uniqueidentifier = null output,
@SPOUSE_SUFFIXCODEID uniqueidentifier = null output,
@SPOUSE_GENDERCODE tinyint = null output,
@SPOUSE_BIRTHDATE dbo.UDT_FUZZYDATE = null output,
@SPOUSE_RECIPROCALTYPECODEID uniqueidentifier = null output,
@SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier = null output,
@COPYPRIMARYINFORMATION bit = null output,
@SPOUSE_STARTDATE datetime = null output,
@PRIMARYRELATIONSHIPEXISTS bit = null output,
@PRIMARYMATCHFACTOR decimal(5,2) = null output,
@RECIPROCALRELATIONSHIPEXISTS bit = null output,
@RECIPROCALMATCHFACTOR decimal(5,2) = null output,
--Organization variables
@BATCHORGANIZATIONID uniqueidentifier = null output,
@BATCHORGANIZATIONRELATIONID uniqueidentifier = null output,
@ORGANIZATIONID uniqueidentifier = null output,
@EXISTINGORGANIZATION bit = null output,
@ORGANIZATION_NAME nvarchar(100) = null output,
@ORGANIZATION_KEYNAMEPREFIX nvarchar(50) = null output,
@ORGANIZATION_ADDRESSTYPECODEID uniqueidentifier = null output,
@ORGANIZATION_COUNTRYID uniqueidentifier = null output,
@ORGANIZATION_STATEID uniqueidentifier = null output,
@ORGANIZATION_ADDRESSBLOCK nvarchar(150) = null output,
@ORGANIZATION_CITY nvarchar(50) = null output,
@ORGANIZATION_POSTCODE nvarchar(12) = null output,
@ORGANIZATION_DONOTMAIL bit = null output,
-- Address Validation
@ORGANIZATION_OMITFROMVALIDATION bit = null output,
@ORGANIZATION_CART nvarchar(10) = null output,
@ORGANIZATION_DPC nvarchar(8) = null output,
@ORGANIZATION_LOT nvarchar(5) = null output,
@ORGANIZATION_COUNTYCODEID uniqueidentifier = null output,
@ORGANIZATION_CONGRESSIONALDISTRICTCODEID uniqueidentifier = null output,
@ORGANIZATION_LASTVALIDATIONATTEMPTDATE datetime = null output,
@ORGANIZATION_VALIDATIONMESSAGE nvarchar(200) = null output,
@ORGANIZATION_CERTIFICATIONDATA integer = null output,
@ORGANIZATION_PHONETYPECODEID uniqueidentifier = null output,
@ORGANIZATION_NUMBER nvarchar(100) = null output,
@ORGANIZATION_RECIPROCALTYPECODEID uniqueidentifier = null output,
@ORGANIZATION_RELATIONSHIPTYPECODEID uniqueidentifier = null output,
@ORGANIZATION_STARTDATE datetime = null output,
@ISCONTACT bit = null output,
@ISPRIMARYCONTACT bit = null output,
@CONTACTTYPECODEID uniqueidentifier = null output,
@POSITION nvarchar(50) = null output,
@ISMATCHINGGIFTRELATIONSHIP bit = null output,
-- Group variables
@ISGROUP bit = null output,
@ISHOUSEHOLD bit = null output,
@GIVESANONYMOUSLY bit = null output,
@GROUPTYPECODE tinyint = null output,
@GROUPTYPEID uniqueidentifier = null output,
@GROUPDESCRIPTION nvarchar(300) = null output,
@GROUPSTARTDATE datetime = null output,
@GROUPPRIMARYCONTACTID uniqueidentifier = null output,
@GROUPMEMBERS xml = null output,
-- Household Primary Member variables
@PRIMARYCONTACTID uniqueidentifier = null output,
@PRIMARYCONTACT_KEYNAME nvarchar(100) = null output,
@PRIMARYCONTACT_FIRSTNAME nvarchar(50) = null output,
@PRIMARYCONTACT_MIDDLENAME nvarchar(50) = null output,
@PRIMARYCONTACT_TITLECODEID uniqueidentifier = null output,
@PRIMARYCONTACT_SUFFIXCODEID uniqueidentifier = null output,
@PRIMARYCONTACT_COPYHOUSEHOLDCONTACT bit = null output,
-- Household Second Member variables
@SECONDMEMBERID uniqueidentifier = null output,
@SECONDMEMBER_KEYNAME nvarchar(100) = null output,
@SECONDMEMBER_FIRSTNAME nvarchar(50) = null output,
@SECONDMEMBER_MIDDLENAME nvarchar(50) = null output,
@SECONDMEMBER_TITLECODEID uniqueidentifier = null output,
@SECONDMEMBER_SUFFIXCODEID uniqueidentifier = null output,
@SECONDMEMBER_COPYHOUSEHOLDCONTACT bit = 0 output,
-- Household Second Member Relationship variables
@SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID uniqueidentifier = null output,
@SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID uniqueidentifier = null output,
@SECONDMEMBER_RELATIONSHIP_STARTDATE datetime = null output,
@SECONDMEMBER_RELATIONSHIP_ISSPOUSE bit = null output,
-- Individual's household variables
@BATCHHOUSEHOLDID uniqueidentifier = null output,
@HOUSEHOLDID uniqueidentifier = null output,
@HOUSEHOLD_NAME nvarchar(100) = null output,
@ADDSPOUSETOHOUSEHOLD bit = null output,
@HOUSEHOLDCOPYPRIMARYCONTACTINFO bit = null output,
-- Individual's relationship variables
@ISSPOUSERELATIONSHIP bit = null output,
@DONOTMAILREASONCODEID uniqueidentifier = null output,
@ORGANIZATION_DONOTMAILREASONCODEID uniqueidentifier = null output,
@NAMEFORMATFUNCTIONID uniqueidentifier = null output,
@JOBCATEGORYCODEID uniqueidentifier = null output,
@CAREERLEVELCODEID uniqueidentifier = null output
) as begin
set nocount on;
begin try
select @ISORGANIZATION=ISORGANIZATION,
@KEYNAME = KEYNAME,
@KEYNAMEPREFIX = KEYNAMEPREFIX,
@FIRSTNAME = FIRSTNAME,
@MIDDLENAME = MIDDLENAME,
@MAIDENNAME = MAIDENNAME,
@NICKNAME=NICKNAME,
@TITLECODEID=TITLECODEID,
@SUFFIXCODEID=SUFFIXCODEID,
@GENDERCODE=GENDERCODE,
@BIRTHDATE=BIRTHDATE,
@ADDRESSTYPECODEID=ADDRESSTYPECODEID,
@DONOTMAIL=DONOTMAIL,
@DONOTMAILREASONCODEID = DONOTMAILREASONCODEID,
@COUNTRYID=COUNTRYID,
@STATEID=STATEID,
@ADDRESSBLOCK=ADDRESSBLOCK,
@CITY=CITY,
@POSTCODE=POSTCODE,
@OMITFROMVALIDATION = OMITFROMVALIDATION,
@CART = CART,
@DPC = DPC,
@LOT = LOT,
@COUNTYCODEID = COUNTYCODEID,
@CONGRESSIONALDISTRICTCODEID = CONGRESSIONALDISTRICTCODEID,
@LASTVALIDATIONATTEMPTDATE = LASTVALIDATIONATTEMPTDATE,
@VALIDATIONMESSAGE = VALIDATIONMESSAGE,
@CERTIFICATIONDATA = CERTIFICATIONDATA,
@PHONETYPECODEID=PHONETYPECODEID,
@NUMBER=NUMBER,
@EMAILADDRESSTYPECODEID=EMAILADDRESSTYPECODEID,
@EMAILADDRESS=EMAILADDRESS,
@MARITALSTATUSCODEID=MARITALSTATUSCODEID,
@CURRENTAPPUSERID=CURRENTAPPUSERID,
@WEBADDRESS=WEBADDRESS,
@INDUSTRYCODEID=INDUSTRYCODEID,
@NUMEMPLOYEES=NUMEMPLOYEES,
@NUMSUBSIDIARIES=NUMSUBSIDIARIES,
@PARENTCORPID=PARENTCORPID,
@ISGROUP=ISGROUP,
@ISHOUSEHOLD = case when GROUPTYPECODE = 0 then 1 else 0 end,
@GIVESANONYMOUSLY=GIVESANONYMOUSLY,
@GROUPTYPECODE=GROUPTYPECODE,
@GROUPTYPEID=GROUPTYPEID,
@GROUPDESCRIPTION=GROUPDESCRIPTION,
@GROUPSTARTDATE=GROUPSTARTDATE,
@NAMEFORMATFUNCTIONID=NAMEFORMATFUNCTIONID
from dbo.BATCHSPONSORSHIPCONSTITUENT where ID = @ID
-- Load individual's relationship and group
if @ISORGANIZATION = 0 and @ISGROUP = 0
begin
select
@BATCHSPOUSERELATIONID=BATCHSPONSORSHIPCONSTITUENTRELATION.ID,
@BATCHSPOUSEID=BATCHSPONSORSHIPCONSTITUENTRELATION.RELATIONID,
@SPOUSE_RECIPROCALTYPECODEID=BATCHSPONSORSHIPCONSTITUENTRELATION.RECIPROCALTYPECODEID,
@SPOUSE_RELATIONSHIPTYPECODEID=BATCHSPONSORSHIPCONSTITUENTRELATION.RELATIONSHIPTYPECODEID,
@COPYPRIMARYINFORMATION=BATCHSPONSORSHIPCONSTITUENTRELATION.COPYPRIMARYINFORMATION,
@SPOUSE_STARTDATE=BATCHSPONSORSHIPCONSTITUENTRELATION.STARTDATE,
@ISSPOUSERELATIONSHIP=BATCHSPONSORSHIPCONSTITUENTRELATION.ISSPOUSE,
@JOBCATEGORYCODEID=BATCHSPONSORSHIPCONSTITUENTRELATION.JOBCATEGORYCODEID,
@CAREERLEVELCODEID=BATCHSPONSORSHIPCONSTITUENTRELATION.CAREERLEVELCODEID
from dbo.BATCHSPONSORSHIPCONSTITUENTRELATION
inner join dbo.BATCHSPONSORSHIPCONSTITUENT on BATCHSPONSORSHIPCONSTITUENTRELATION.RELATIONID = BATCHSPONSORSHIPCONSTITUENT.ID
where BATCHSPONSORSHIPCONSTITUENTRELATION.CONSTITUENTID = @ID and BATCHSPONSORSHIPCONSTITUENT.ISORGANIZATION = 0
if @BATCHSPOUSEID is not null
select @SPOUSEID=BATCHSPONSORSHIPCONSTITUENT.EXISTINGCONSTITUENTID,
@SPOUSE_LASTNAME=BATCHSPONSORSHIPCONSTITUENT.KEYNAME,
@SPOUSE_FIRSTNAME=BATCHSPONSORSHIPCONSTITUENT.FIRSTNAME,
@SPOUSE_MIDDLENAME=BATCHSPONSORSHIPCONSTITUENT.MIDDLENAME,
@SPOUSE_MAIDENNAME=BATCHSPONSORSHIPCONSTITUENT.MAIDENNAME,
@SPOUSE_NICKNAME=BATCHSPONSORSHIPCONSTITUENT.NICKNAME,
@SPOUSE_TITLECODEID=BATCHSPONSORSHIPCONSTITUENT.TITLECODEID,
@SPOUSE_SUFFIXCODEID=BATCHSPONSORSHIPCONSTITUENT.SUFFIXCODEID,
@SPOUSE_GENDERCODE=BATCHSPONSORSHIPCONSTITUENT.GENDERCODE,
@SPOUSE_BIRTHDATE=BATCHSPONSORSHIPCONSTITUENT.BIRTHDATE
from dbo.BATCHSPONSORSHIPCONSTITUENT where BATCHSPONSORSHIPCONSTITUENT.ID = @BATCHSPOUSEID
if @SPOUSEID is null and @SPOUSE_LASTNAME != ''
set @EXISTINGSPOUSE = 0
else
set @EXISTINGSPOUSE = 1
select
@BATCHORGANIZATIONRELATIONID=BATCHSPONSORSHIPCONSTITUENTRELATION.ID,
@BATCHORGANIZATIONID=BATCHSPONSORSHIPCONSTITUENTRELATION.RELATIONID,
@ORGANIZATION_RECIPROCALTYPECODEID=BATCHSPONSORSHIPCONSTITUENTRELATION.RECIPROCALTYPECODEID,
@ORGANIZATION_RELATIONSHIPTYPECODEID=BATCHSPONSORSHIPCONSTITUENTRELATION.RELATIONSHIPTYPECODEID,
@ORGANIZATION_STARTDATE=BATCHSPONSORSHIPCONSTITUENTRELATION.STARTDATE,
@ISCONTACT=BATCHSPONSORSHIPCONSTITUENTRELATION.ISCONTACT,
@ISPRIMARYCONTACT=BATCHSPONSORSHIPCONSTITUENTRELATION.ISPRIMARYCONTACT,
@CONTACTTYPECODEID=BATCHSPONSORSHIPCONSTITUENTRELATION.CONTACTTYPECODEID,
@POSITION=BATCHSPONSORSHIPCONSTITUENTRELATION.POSITION,
@ISMATCHINGGIFTRELATIONSHIP=BATCHSPONSORSHIPCONSTITUENTRELATION.ISMATCHINGGIFTRELATIONSHIP
from dbo.BATCHSPONSORSHIPCONSTITUENTRELATION where BATCHSPONSORSHIPCONSTITUENTRELATION.CONSTITUENTID = @ID and BATCHSPONSORSHIPCONSTITUENTRELATION.ISPRIMARYBUSINESS = 1
if @BATCHORGANIZATIONID is not null
select @ORGANIZATIONID=BATCHSPONSORSHIPCONSTITUENT.EXISTINGCONSTITUENTID,
@ORGANIZATION_NAME=BATCHSPONSORSHIPCONSTITUENT.KEYNAME,
@ORGANIZATION_KEYNAMEPREFIX=BATCHSPONSORSHIPCONSTITUENT.KEYNAMEPREFIX,
@ORGANIZATION_ADDRESSTYPECODEID=BATCHSPONSORSHIPCONSTITUENT.ADDRESSTYPECODEID,
@ORGANIZATION_COUNTRYID=BATCHSPONSORSHIPCONSTITUENT.COUNTRYID,
@ORGANIZATION_STATEID=BATCHSPONSORSHIPCONSTITUENT.STATEID,
@ORGANIZATION_ADDRESSBLOCK=BATCHSPONSORSHIPCONSTITUENT.ADDRESSBLOCK,
@ORGANIZATION_CITY=BATCHSPONSORSHIPCONSTITUENT.CITY,
@ORGANIZATION_POSTCODE=BATCHSPONSORSHIPCONSTITUENT.POSTCODE,
@ORGANIZATION_DONOTMAIL=BATCHSPONSORSHIPCONSTITUENT.DONOTMAIL,
@ORGANIZATION_DONOTMAILREASONCODEID=BATCHSPONSORSHIPCONSTITUENT.DONOTMAILREASONCODEID,
@ORGANIZATION_OMITFROMVALIDATION = BATCHSPONSORSHIPCONSTITUENT.OMITFROMVALIDATION,
@ORGANIZATION_CART = BATCHSPONSORSHIPCONSTITUENT.CART,
@ORGANIZATION_DPC = BATCHSPONSORSHIPCONSTITUENT.DPC,
@ORGANIZATION_LOT = BATCHSPONSORSHIPCONSTITUENT.LOT,
@ORGANIZATION_COUNTYCODEID = BATCHSPONSORSHIPCONSTITUENT.COUNTYCODEID,
@ORGANIZATION_CONGRESSIONALDISTRICTCODEID = BATCHSPONSORSHIPCONSTITUENT.CONGRESSIONALDISTRICTCODEID,
@ORGANIZATION_LASTVALIDATIONATTEMPTDATE = BATCHSPONSORSHIPCONSTITUENT.LASTVALIDATIONATTEMPTDATE,
@ORGANIZATION_VALIDATIONMESSAGE = BATCHSPONSORSHIPCONSTITUENT.VALIDATIONMESSAGE,
@ORGANIZATION_CERTIFICATIONDATA = BATCHSPONSORSHIPCONSTITUENT.CERTIFICATIONDATA,
@ORGANIZATION_PHONETYPECODEID=BATCHSPONSORSHIPCONSTITUENT.PHONETYPECODEID,
@ORGANIZATION_NUMBER=BATCHSPONSORSHIPCONSTITUENT.NUMBER
from dbo.BATCHSPONSORSHIPCONSTITUENT where BATCHSPONSORSHIPCONSTITUENT.ID = @BATCHORGANIZATIONID
if @ORGANIZATIONID is null and @ORGANIZATION_NAME != ''
set @EXISTINGORGANIZATION = 0;
else
set @EXISTINGORGANIZATION = 1;
end
if @ISGROUP = 1
begin
if @ISHOUSEHOLD = 0
begin
select
@GROUPPRIMARYCONTACTID = case when BRC.EXISTINGCONSTITUENTID is null then BRCGM.MEMBERID else BRC.EXISTINGCONSTITUENTID end
from dbo.BATCHSPONSORSHIPCONSTITUENTGROUPMEMBER BRCGM
inner join dbo.BATCHSPONSORSHIPCONSTITUENT BRC on BRCGM.MEMBERID = BRC.ID
where BRCGM.GROUPID = @ID and BRCGM.ISPRIMARY = 1
-- Not using UFN_BATCHSPONSORSHIPCONSTITUENT_GETGROUPMEMBERS_TOITEMLISTXML since the root element
-- needed is different the function's.
set @GROUPMEMBERS = (
select
[ID],
[NAME],
[MEMBERID],
[ISPRIMARY]
from dbo.[UFN_BATCHSPONSORSHIPCONSTITUENT_GETGROUPMEMBERS](@ID)
for xml raw('ITEM'), type, elements, root('GROUP_MEMBERS'),binary base64
)
end
else
begin
declare @BATCHPRIMARYCONTACTID uniqueidentifier, @BATCHSECONDMEMBERID uniqueidentifier
-- Load household's primary contact info
select
@BATCHPRIMARYCONTACTID = C.ID,
@PRIMARYCONTACTID = C.EXISTINGCONSTITUENTID,
@PRIMARYCONTACT_KEYNAME = case when C.EXISTINGCONSTITUENTID is null then C.KEYNAME else '' end,
@PRIMARYCONTACT_FIRSTNAME = case when C.EXISTINGCONSTITUENTID is null then C.FIRSTNAME else '' end,
@PRIMARYCONTACT_MIDDLENAME = case when C.EXISTINGCONSTITUENTID is null then C.MIDDLENAME else '' end,
@PRIMARYCONTACT_TITLECODEID = case when C.EXISTINGCONSTITUENTID is null then C.TITLECODEID else null end,
@PRIMARYCONTACT_SUFFIXCODEID = case when C.EXISTINGCONSTITUENTID is null then C.SUFFIXCODEID else null end,
@PRIMARYCONTACT_COPYHOUSEHOLDCONTACT = case when C.EXISTINGCONSTITUENTID is null then GM.COPYGROUPCONTACTINFOTOMEMBER else '' end
from dbo.BATCHSPONSORSHIPCONSTITUENTGROUPMEMBER GM
inner join dbo.BATCHSPONSORSHIPCONSTITUENT C on GM.MEMBERID = C.ID
where
GM.GROUPID = @ID and ISPRIMARY = 1
-- Load household's secondary contact info
select
@BATCHSECONDMEMBERID = C.ID,
@SECONDMEMBERID = C.EXISTINGCONSTITUENTID,
@SECONDMEMBER_KEYNAME = case when C.EXISTINGCONSTITUENTID is null then C.KEYNAME else '' end,
@SECONDMEMBER_FIRSTNAME = case when C.EXISTINGCONSTITUENTID is null then C.FIRSTNAME else '' end,
@SECONDMEMBER_MIDDLENAME = case when C.EXISTINGCONSTITUENTID is null then C.MIDDLENAME else '' end,
@SECONDMEMBER_TITLECODEID = case when C.EXISTINGCONSTITUENTID is null then C.TITLECODEID else null end,
@SECONDMEMBER_SUFFIXCODEID = case when C.EXISTINGCONSTITUENTID is null then C.SUFFIXCODEID else null end,
@SECONDMEMBER_COPYHOUSEHOLDCONTACT = case when C.EXISTINGCONSTITUENTID is null then GM.COPYGROUPCONTACTINFOTOMEMBER else '' end
from dbo.BATCHSPONSORSHIPCONSTITUENTGROUPMEMBER GM
inner join dbo.BATCHSPONSORSHIPCONSTITUENT C on GM.MEMBERID = C.ID
where
GM.GROUPID = @ID and ISPRIMARY = 0
-- Load second member relationship fields if both primary and secondary contacts are set
if @BATCHPRIMARYCONTACTID is not null and @BATCHSECONDMEMBERID is not null
begin
select
@SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID = R.RECIPROCALTYPECODEID,
@SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID = R.RELATIONSHIPTYPECODEID,
@SECONDMEMBER_RELATIONSHIP_STARTDATE = R.STARTDATE,
@SECONDMEMBER_RELATIONSHIP_ISSPOUSE = R.ISSPOUSE
from dbo.BATCHSPONSORSHIPCONSTITUENTRELATION R
where
R.CONSTITUENTID = @BATCHPRIMARYCONTACTID and
R.RELATIONID = @BATCHSECONDMEMBERID
end
end
end
set @ADDSPOUSETOHOUSEHOLD = 0
if @ISGROUP = 0 and @ISORGANIZATION = 0
begin
-- Load individual's household fields
select
@BATCHHOUSEHOLDID = BRC.ID,
@HOUSEHOLDID = BRC.EXISTINGCONSTITUENTID,
@HOUSEHOLD_NAME = case when BRC.EXISTINGCONSTITUENTID is not null then C.NAME else BRC.NAME end,
@HOUSEHOLDCOPYPRIMARYCONTACTINFO = BRCGM.COPYMEMBERCONTACTINFOTOGROUP
from dbo.BATCHSPONSORSHIPCONSTITUENTGROUPMEMBER BRCGM
inner join dbo.BATCHSPONSORSHIPCONSTITUENT BRC on BRCGM.GROUPID = BRC.ID and BRC.GROUPTYPECODE = 0
left join dbo.CONSTITUENT C on BRC.EXISTINGCONSTITUENTID = C.ID
where BRCGM.MEMBERID = @ID
if @BATCHHOUSEHOLDID is not null and @BATCHSPOUSEID is not null
begin
if exists (select 1 from dbo.BATCHSPONSORSHIPCONSTITUENTGROUPMEMBER where MEMBERID = @BATCHSPOUSEID and GROUPID = @BATCHHOUSEHOLDID)
set @ADDSPOUSETOHOUSEHOLD = 1
end
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;
end;