USP_DATAFORMTEMPLATE_ADD_CONSTITUENTBATCHROW2
The save procedure used by the add dataform template "Constituent Batch Row Add Form 2".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @BATCHID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @ISORGANIZATION | tinyint | IN | Constituent type |
| @KEYNAME | nvarchar(100) | IN | Last/Org/Group/Household name |
| @FIRSTNAME | nvarchar(50) | IN | First name |
| @MIDDLENAME | nvarchar(50) | IN | Middle name |
| @MAIDENNAME | nvarchar(100) | IN | Maiden name |
| @NICKNAME | nvarchar(50) | IN | Nickname |
| @TITLECODEID | uniqueidentifier | IN | Title |
| @SUFFIXCODEID | uniqueidentifier | IN | Suffix |
| @BIRTHDATE | UDT_FUZZYDATE | IN | Birth date |
| @GENDERCODE | tinyint | IN | Gender |
| @WEBADDRESS | UDT_WEBADDRESS | IN | Web site address |
| @INDUSTRYCODEID | uniqueidentifier | IN | Industry |
| @PARENTCORPID | uniqueidentifier | IN | Parent org |
| @NUMEMPLOYEES | int | IN | No. of employees |
| @NUMSUBSIDIARIES | int | IN | No. of subsidiary orgs |
| @ADDRESS_ADDRESSTYPECODEID | uniqueidentifier | IN | Address type |
| @ADDRESS_COUNTRYID | uniqueidentifier | IN | Country |
| @ADDRESS_ADDRESSBLOCK | nvarchar(150) | IN | Address |
| @ADDRESS_CITY | nvarchar(50) | IN | City |
| @ADDRESS_STATEID | uniqueidentifier | IN | State |
| @ADDRESS_POSTCODE | nvarchar(12) | IN | ZIP |
| @ADDRESS_DONOTMAIL | bit | IN | Do not send mail to this address |
| @PHONES | xml | IN | Phones |
| @EMAILADDRESS_EMAILADDRESSTYPECODEID | uniqueidentifier | IN | Email type |
| @EMAILADDRESS_EMAILADDRESS | UDT_EMAILADDRESS | IN | Email address |
| @CUSTOMIDENTIFIER | nvarchar(100) | IN | Lookup ID |
| @SEQUENCE | int | IN | Sequence |
| @ADDRESS_CART | nvarchar(10) | IN | CART |
| @ADDRESS_DPC | nvarchar(8) | IN | DPC |
| @ADDRESS_LOT | nvarchar(5) | IN | LOT |
| @ADDRESS_STARTDATE | UDT_MONTHDAY | IN | Seasonal start date |
| @ADDRESS_ENDDATE | UDT_MONTHDAY | IN | Seasonal end date |
| @PRIMARYADDRESSEE_NAMEFORMATTYPECODEID | uniqueidentifier | IN | Addressee type |
| @PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID | uniqueidentifier | IN | Addressee name format |
| @PRIMARYSALUTATION_NAMEFORMATTYPECODEID | uniqueidentifier | IN | Salutation type |
| @PRIMARYSALUTATION_NAMEFORMATFUNCTIONID | uniqueidentifier | IN | Salutation name format |
| @ISDECEASED | bit | IN | Is deceased |
| @DECEASEDDATE | UDT_FUZZYDATE | IN | Deceased date |
| @EDUCATIONALHISTORY_EDUCATIONALINSTITUTIONID | uniqueidentifier | IN | Educational institution |
| @EDUCATIONALHISTORY_ISPRIMARYRECORD | bit | IN | Educational is primary |
| @EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE | tinyint | IN | Educational status |
| @EDUCATIONALHISTORY_CLASSOF | UDT_YEAR | IN | Class year |
| @EDUCATIONALHISTORY_EDUCATIONALDEGREECODEID | uniqueidentifier | IN | Educational degree |
| @EDUCATIONALHISTORY_STARTDATE | UDT_FUZZYDATE | IN | Educational start date |
| @SPOUSEID | uniqueidentifier | IN | Spouse |
| @SPOUSE_RELATIONSHIPTYPECODEID | uniqueidentifier | IN | Spouse relationship type |
| @SPOUSE_RECIPROCALTYPECODEID | uniqueidentifier | IN | Spouse reciprocal type |
| @SPOUSE_KEYNAME | nvarchar(100) | IN | Spouse last name |
| @SPOUSE_FIRSTNAME | nvarchar(50) | IN | Spouse first name |
| @SPOUSE_MIDDLENAME | nvarchar(50) | IN | Spouse middle name |
| @SPOUSE_MAIDENNAME | nvarchar(100) | IN | Spouse maiden name |
| @SPOUSE_NICKNAME | nvarchar(50) | IN | Spouse nickname |
| @SPOUSE_TITLECODEID | uniqueidentifier | IN | Spouse title |
| @SPOUSE_SUFFIXCODEID | uniqueidentifier | IN | Spouse suffix |
| @SPOUSE_GENDERCODE | tinyint | IN | Spouse gender |
| @SPOUSE_BIRTHDATE | UDT_FUZZYDATE | IN | Spouse birth date |
| @SPOUSE_EMAILADDRESS_EMAILADDRESSTYPECODEID | uniqueidentifier | IN | Spouse email address type |
| @SPOUSE_EMAILADDRESS_EMAILADDRESS | UDT_EMAILADDRESS | IN | Spouse email address |
| @PRIMARYBUSINESSID | uniqueidentifier | IN | Business |
| @PRIMARYBUSINESS_RELATIONSHIPTYPECODEID | uniqueidentifier | IN | Business relationship type |
| @PRIMARYBUSINESS_RECIPROCALTYPECODEID | uniqueidentifier | IN | Business reciprocal type |
| @PRIMARYBUSINESS_KEYNAME | nvarchar(100) | IN | Business name |
| @PRIMARYBUSINESS_PHONE_PHONETYPECODEID | uniqueidentifier | IN | Business phone type |
| @PRIMARYBUSINESS_PHONE_NUMBER | nvarchar(100) | IN | Business phone number |
| @PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESSTYPECODEID | uniqueidentifier | IN | Business email type |
| @PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESS | UDT_EMAILADDRESS | IN | Business email address |
| @PRIMARYBUSINESS_ADDRESS_ADDRESSTYPECODEID | uniqueidentifier | IN | Business address type |
| @PRIMARYBUSINESS_ADDRESS_COUNTRYID | uniqueidentifier | IN | Business country |
| @PRIMARYBUSINESS_ADDRESS_ADDRESSBLOCK | nvarchar(150) | IN | Business address |
| @PRIMARYBUSINESS_ADDRESS_CITY | nvarchar(50) | IN | Business city |
| @PRIMARYBUSINESS_ADDRESS_STATEID | uniqueidentifier | IN | Business state |
| @PRIMARYBUSINESS_ADDRESS_POSTCODE | nvarchar(12) | IN | Business ZIP |
| @PRIMARYBUSINESS_ADDRESS_CART | nvarchar(10) | IN | Business CART |
| @PRIMARYBUSINESS_ADDRESS_DPC | nvarchar(8) | IN | Business DPC |
| @PRIMARYBUSINESS_ADDRESS_LOT | nvarchar(5) | IN | Business LOT |
| @PRIMARYBUSINESS_ADDRESS_DONOTMAIL | bit | IN | Business do not mail |
| @PRIMARYBUSINESS_INDUSTRYCODEID | uniqueidentifier | IN | Business industry |
| @PRIMARYBUSINESS_NUMEMPLOYEES | int | IN | Business no. of employees |
| @PRIMARYBUSINESS_NUMSUBSIDIARIES | int | IN | Business no. of subsidiary orgs |
| @PRIMARYBUSINESS_WEBADDRESS | UDT_WEBADDRESS | IN | Business web site address |
| @PRIMARYBUSINESS_PARENTCORPID | uniqueidentifier | IN | Business parent org |
| @CONSTITUENCY | xml | IN | Constituencies |
| @INTEREST | xml | IN | Interests |
| @SPOUSE_COPY_ADDRESS | bit | IN | Spouse copy address |
| @SPOUSE_COPY_PHONENUMBER | bit | IN | Spouse copy phone number |
| @SPOUSE_COPY_EMAILADDRESS | bit | IN | Spouse copy email address |
| @CONSTIT_SECURITY_ATTRIBUTEID | uniqueidentifier | IN | Constituent security attribute |
| @ADDRESS_COUNTYCODEID | uniqueidentifier | IN | County |
| @ADDRESS_OMITFROMVALIDATION | bit | IN | Omit from validation |
| @ADDRESS_CONGRESSIONALDISTRICTCODEID | uniqueidentifier | IN | Congressional district |
| @ADDRESS_STATEHOUSEDISTRICTCODEID | uniqueidentifier | IN | State house district |
| @ADDRESS_STATESENATEDISTRICTCODEID | uniqueidentifier | IN | State senate district |
| @ADDRESS_LOCALPRECINCTCODEID | uniqueidentifier | IN | Local precinct |
| @ADDRESS_INFOSOURCECODEID | uniqueidentifier | IN | Info source |
| @ADDRESS_REGIONCODEID | uniqueidentifier | IN | Region |
| @ADDRESS_LASTVALIDATIONATTEMPTDATE | datetime | IN | Last validation attempt date |
| @ADDRESS_VALIDATIONMESSAGE | nvarchar(200) | IN | Validation message |
| @ADDRESS_CERTIFICATIONDATA | int | IN | Certification data |
| @GROUPTYPEID | uniqueidentifier | IN | Group type |
| @GROUPSTARTDATE | datetime | IN | Group start date |
| @GROUPDESCRIPTION | nvarchar(300) | IN | Group description |
| @GROUPMEMBERS | xml | IN | Group members |
| @ADDRESS_DONOTMAILREASONCODEID | uniqueidentifier | IN | Do not mail reason |
| @PRIMARYBUSINESS_ADDRESS_DONOTMAILREASONCODEID | uniqueidentifier | IN | Business do not mail reason |
| @EDUCATIONALHISTORY_ACADEMICCATALOGPROGRAMID | uniqueidentifier | IN | Academic catalog program |
| @EDUCATIONALHISTORY_EDUCATIONALPROGRAMCODEID | uniqueidentifier | IN | Educational program |
| @EDUCATIONALHISTORY_ACADEMICCATALOGDEGREEID | uniqueidentifier | IN | Academic catalog degree |
| @EDUCATIONALHISTORY_EDUCATIONALAWARDCODEID | uniqueidentifier | IN | Awarded |
| @EDUCATIONALHISTORY_PREFERREDCLASSYEAR | UDT_YEAR | IN | Preferred class year |
| @EDUCATIONALHISTORY_EDUCATIONALSOURCECODEID | uniqueidentifier | IN | Source |
| @EDUCATIONALHISTORY_EDUCATIONALSOURCEDATE | UDT_FUZZYDATE | IN | Source date |
| @EDUCATIONALHISTORY_COMMENT | nvarchar(500) | IN | Comment |
| @AFFILIATEDADDITIONALINFORMATION | xml | IN | Academic catalog additional information |
| @UNAFFILIATEDADDITIONALINFORMATION | xml | IN | Educational additional information |
| @EDUCATIONALINVOLVEMENT | xml | IN | Involvement |
| @ALTERNATELOOKUPIDS | xml | IN | Alternate lookup IDs |
| @EDUCATIONALHISTORY_DATEGRADUATED | UDT_FUZZYDATE | IN | Date graduated |
| @EDUCATIONALHISTORY_DATELEFT | UDT_FUZZYDATE | IN | Date left |
| @EDUCATIONALHISTORY_LEVELCODEID | uniqueidentifier | IN | Level |
| @EDUCATIONALHISTORY_REASONCODEID | uniqueidentifier | IN | Reason |
| @ISPRIMARYORGANIZATION | bit | IN | Is primary org |
| @PRIMARYBUSINESS_ISPRIMARYORGANIZATION | bit | IN | Business is primary org |
| @CONSTITUENT_SITEID | uniqueidentifier | IN | Constituent site |
| @TAXDECLARATIONS | xml | IN | Declarations |
| @ADDRESS_HISTORICALSTARTDATE | date | IN | Address start date |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_CONSTITUENTBATCHROW2
(
@ID uniqueidentifier = null output,
@BATCHID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@ISORGANIZATION tinyint,
@KEYNAME nvarchar(100),
@FIRSTNAME nvarchar(50) = '',
@MIDDLENAME nvarchar(50) = '',
@MAIDENNAME nvarchar(100) = '',
@NICKNAME nvarchar(50) = '',
@TITLECODEID uniqueidentifier = null,
@SUFFIXCODEID uniqueidentifier = null,
@BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
@GENDERCODE tinyint = 0,
@WEBADDRESS dbo.UDT_WEBADDRESS = '',
@INDUSTRYCODEID uniqueidentifier = null,
@PARENTCORPID uniqueidentifier = null,
@NUMEMPLOYEES int = 0,
@NUMSUBSIDIARIES int = 0,
@ADDRESS_ADDRESSTYPECODEID uniqueidentifier = null,
@ADDRESS_COUNTRYID uniqueidentifier = null,
@ADDRESS_ADDRESSBLOCK nvarchar(150) = '',
@ADDRESS_CITY nvarchar(50) = '',
@ADDRESS_STATEID uniqueidentifier = null,
@ADDRESS_POSTCODE nvarchar(12) = '',
@ADDRESS_DONOTMAIL bit = 0,
@PHONES xml = null,
@EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null,
@EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
@CUSTOMIDENTIFIER nvarchar(100) = '',
@SEQUENCE int,
@ADDRESS_CART nvarchar(10) = '',
@ADDRESS_DPC nvarchar(8) = '',
@ADDRESS_LOT nvarchar(5) = '',
@ADDRESS_STARTDATE dbo.UDT_MONTHDAY = '0000',
@ADDRESS_ENDDATE dbo.UDT_MONTHDAY = '0000',
@PRIMARYADDRESSEE_NAMEFORMATTYPECODEID uniqueidentifier = null,
@PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID uniqueidentifier = null,
@PRIMARYSALUTATION_NAMEFORMATTYPECODEID uniqueidentifier = null,
@PRIMARYSALUTATION_NAMEFORMATFUNCTIONID uniqueidentifier = null,
@ISDECEASED bit = 0,
@DECEASEDDATE dbo.UDT_FUZZYDATE = '00000000',
@EDUCATIONALHISTORY_EDUCATIONALINSTITUTIONID uniqueidentifier = null,
@EDUCATIONALHISTORY_ISPRIMARYRECORD bit = 0,
@EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE tinyint = 0,
@EDUCATIONALHISTORY_CLASSOF dbo.UDT_YEAR = 0,
@EDUCATIONALHISTORY_EDUCATIONALDEGREECODEID uniqueidentifier = null,
@EDUCATIONALHISTORY_STARTDATE dbo.UDT_FUZZYDATE = '00000000',
@SPOUSEID uniqueidentifier = null,
@SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier = null,
@SPOUSE_RECIPROCALTYPECODEID uniqueidentifier = null,
@SPOUSE_KEYNAME nvarchar(100) = '',
@SPOUSE_FIRSTNAME nvarchar(50) = '',
@SPOUSE_MIDDLENAME nvarchar(50) = '',
@SPOUSE_MAIDENNAME nvarchar(100) = '',
@SPOUSE_NICKNAME nvarchar(50) = '',
@SPOUSE_TITLECODEID uniqueidentifier = null,
@SPOUSE_SUFFIXCODEID uniqueidentifier = null,
@SPOUSE_GENDERCODE tinyint = 0,
@SPOUSE_BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
@SPOUSE_EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null,
@SPOUSE_EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
@PRIMARYBUSINESSID uniqueidentifier = null,
@PRIMARYBUSINESS_RELATIONSHIPTYPECODEID uniqueidentifier = null,
@PRIMARYBUSINESS_RECIPROCALTYPECODEID uniqueidentifier = null,
@PRIMARYBUSINESS_KEYNAME nvarchar(100) = '',
@PRIMARYBUSINESS_PHONE_PHONETYPECODEID uniqueidentifier = null,
@PRIMARYBUSINESS_PHONE_NUMBER nvarchar(100) = '',
@PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null,
@PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
@PRIMARYBUSINESS_ADDRESS_ADDRESSTYPECODEID uniqueidentifier = null,
@PRIMARYBUSINESS_ADDRESS_COUNTRYID uniqueidentifier = null,
@PRIMARYBUSINESS_ADDRESS_ADDRESSBLOCK nvarchar(150) = '',
@PRIMARYBUSINESS_ADDRESS_CITY nvarchar(50) = '',
@PRIMARYBUSINESS_ADDRESS_STATEID uniqueidentifier = null,
@PRIMARYBUSINESS_ADDRESS_POSTCODE nvarchar(12) = '',
@PRIMARYBUSINESS_ADDRESS_CART nvarchar(10) = '',
@PRIMARYBUSINESS_ADDRESS_DPC nvarchar(8) = '',
@PRIMARYBUSINESS_ADDRESS_LOT nvarchar(5) = '',
@PRIMARYBUSINESS_ADDRESS_DONOTMAIL bit = 0,
@PRIMARYBUSINESS_INDUSTRYCODEID uniqueidentifier = null,
@PRIMARYBUSINESS_NUMEMPLOYEES int = 0,
@PRIMARYBUSINESS_NUMSUBSIDIARIES int = 0,
@PRIMARYBUSINESS_WEBADDRESS dbo.UDT_WEBADDRESS = '',
@PRIMARYBUSINESS_PARENTCORPID uniqueidentifier = null,
@CONSTITUENCY xml = null,
@INTEREST xml = null,
@SPOUSE_COPY_ADDRESS bit = 1,
@SPOUSE_COPY_PHONENUMBER bit = 1,
@SPOUSE_COPY_EMAILADDRESS bit = 1,
@CONSTIT_SECURITY_ATTRIBUTEID uniqueidentifier = null,
@ADDRESS_COUNTYCODEID uniqueidentifier = null,
@ADDRESS_OMITFROMVALIDATION bit = 0,
@ADDRESS_CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
@ADDRESS_STATEHOUSEDISTRICTCODEID uniqueidentifier = null,
@ADDRESS_STATESENATEDISTRICTCODEID uniqueidentifier = null,
@ADDRESS_LOCALPRECINCTCODEID uniqueidentifier = null,
@ADDRESS_INFOSOURCECODEID uniqueidentifier = null,
@ADDRESS_REGIONCODEID uniqueidentifier = null,
@ADDRESS_LASTVALIDATIONATTEMPTDATE datetime = null,
@ADDRESS_VALIDATIONMESSAGE nvarchar(200) = '',
@ADDRESS_CERTIFICATIONDATA int = 0,
@GROUPTYPEID uniqueidentifier = null,
@GROUPSTARTDATE datetime = null,
@GROUPDESCRIPTION nvarchar(300) = '',
@GROUPMEMBERS xml = null,
@ADDRESS_DONOTMAILREASONCODEID uniqueidentifier = null,
@PRIMARYBUSINESS_ADDRESS_DONOTMAILREASONCODEID uniqueidentifier = null,
@EDUCATIONALHISTORY_ACADEMICCATALOGPROGRAMID uniqueidentifier = null,
@EDUCATIONALHISTORY_EDUCATIONALPROGRAMCODEID uniqueidentifier = null,
@EDUCATIONALHISTORY_ACADEMICCATALOGDEGREEID uniqueidentifier = null,
@EDUCATIONALHISTORY_EDUCATIONALAWARDCODEID uniqueidentifier = null,
@EDUCATIONALHISTORY_PREFERREDCLASSYEAR dbo.UDT_YEAR = 0,
@EDUCATIONALHISTORY_EDUCATIONALSOURCECODEID uniqueidentifier = null,
@EDUCATIONALHISTORY_EDUCATIONALSOURCEDATE dbo.UDT_FUZZYDATE = '00000000',
@EDUCATIONALHISTORY_COMMENT nvarchar(500) = null,
@AFFILIATEDADDITIONALINFORMATION xml = null,
@UNAFFILIATEDADDITIONALINFORMATION xml = null,
@EDUCATIONALINVOLVEMENT xml = null,
@ALTERNATELOOKUPIDS xml = null,
@EDUCATIONALHISTORY_DATEGRADUATED dbo.UDT_FUZZYDATE = '00000000',
@EDUCATIONALHISTORY_DATELEFT dbo.UDT_FUZZYDATE = '00000000',
@EDUCATIONALHISTORY_LEVELCODEID uniqueidentifier = null,
@EDUCATIONALHISTORY_REASONCODEID uniqueidentifier = null,
@ISPRIMARYORGANIZATION bit = null,
@PRIMARYBUSINESS_ISPRIMARYORGANIZATION bit = null,
@CONSTITUENT_SITEID uniqueidentifier = null,
@TAXDECLARATIONS xml = null,
@ADDRESS_HISTORICALSTARTDATE date = null
) as
set nocount on;
declare @CURRENTDATE datetime;
if @ID is null
set @ID = newid();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
if @BIRTHDATE is null
set @BIRTHDATE = '00000000';
if @GENDERCODE is null
set @GENDERCODE = 0;
if @NUMEMPLOYEES is null
set @NUMEMPLOYEES = 0;
if @NUMSUBSIDIARIES is null
set @NUMSUBSIDIARIES = 0;
if @EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE is null
set @EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE = 0;
if @EDUCATIONALHISTORY_CLASSOF is null
set @EDUCATIONALHISTORY_CLASSOF = 0;
if @SPOUSE_GENDERCODE is null
set @SPOUSE_GENDERCODE = 0;
if @PRIMARYBUSINESS_NUMEMPLOYEES is null
set @PRIMARYBUSINESS_NUMEMPLOYEES = 0;
if @PRIMARYBUSINESS_NUMSUBSIDIARIES is null
set @PRIMARYBUSINESS_NUMSUBSIDIARIES = 0;
if ((@ADDRESS_ADDRESSTYPECODEID is not null) or (@ADDRESS_STATEID is not null) or
(coalesce(@ADDRESS_ADDRESSBLOCK,'') <> '') or (coalesce(@ADDRESS_CITY,'') <> '') or
(coalesce(@ADDRESS_POSTCODE,'') <> '')) and @ADDRESS_COUNTRYID is null
exec @ADDRESS_COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT;
if ((@PRIMARYBUSINESS_ADDRESS_ADDRESSTYPECODEID is not null) or (@PRIMARYBUSINESS_ADDRESS_STATEID is not null) or
(coalesce(@PRIMARYBUSINESS_ADDRESS_ADDRESSBLOCK,'') <> '') or (coalesce(@PRIMARYBUSINESS_ADDRESS_CITY,'') <> '') or
(coalesce(@PRIMARYBUSINESS_ADDRESS_POSTCODE,'') <> '')) and @PRIMARYBUSINESS_ADDRESS_COUNTRYID is null
exec @PRIMARYBUSINESS_ADDRESS_COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT;
if @ADDRESS_CERTIFICATIONDATA is null
set @ADDRESS_CERTIFICATIONDATA = 0
if @ADDRESS_DONOTMAIL = 0
set @ADDRESS_DONOTMAILREASONCODEID = null
if @PRIMARYBUSINESS_ADDRESS_DONOTMAIL = 0
set @PRIMARYBUSINESS_ADDRESS_DONOTMAILREASONCODEID = null
if @EDUCATIONALHISTORY_COMMENT is null
set @EDUCATIONALHISTORY_COMMENT = '';
if @EDUCATIONALHISTORY_PREFERREDCLASSYEAR is null
set @EDUCATIONALHISTORY_PREFERREDCLASSYEAR = 0;
if @EDUCATIONALHISTORY_EDUCATIONALSOURCEDATE is null
set @EDUCATIONALHISTORY_EDUCATIONALSOURCEDATE = '00000000';
if @EDUCATIONALHISTORY_DATEGRADUATED is null
set @EDUCATIONALHISTORY_DATEGRADUATED = '00000000';
if @EDUCATIONALHISTORY_DATELEFT is null
set @EDUCATIONALHISTORY_DATELEFT = '00000000';
if @ISPRIMARYORGANIZATION is null
set @ISPRIMARYORGANIZATION = 0;
if @PRIMARYBUSINESS_ISPRIMARYORGANIZATION is null
set @PRIMARYBUSINESS_ISPRIMARYORGANIZATION = 0;
begin try
insert into dbo.BATCHCONSTITUENT
(
[ID],
[BATCHID],
[ISORGANIZATION],
[KEYNAME],
[FIRSTNAME],
[MIDDLENAME],
[MAIDENNAME],
[NICKNAME],
[TITLECODEID],
[SUFFIXCODEID],
[BIRTHDATE],
[GENDERCODE],
[WEBADDRESS],
[INDUSTRYCODEID],
[PARENTCORPID],
[NUMEMPLOYEES],
[NUMSUBSIDIARIES],
[ADDRESS_ADDRESSTYPECODEID],
[ADDRESS_COUNTRYID],
[ADDRESS_ADDRESSBLOCK],
[ADDRESS_CITY],
[ADDRESS_STATEID],
[ADDRESS_POSTCODE],
[ADDRESS_DONOTMAIL],
[EMAILADDRESS_EMAILADDRESSTYPECODEID],
[EMAILADDRESS_EMAILADDRESS],
[ADDRESS_CART],
[ADDRESS_DPC],
[ADDRESS_LOT],
[ADDRESS_STARTDATE],
[ADDRESS_ENDDATE],
[PRIMARYADDRESSEE_NAMEFORMATTYPECODEID],
[PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID],
[PRIMARYSALUTATION_NAMEFORMATTYPECODEID],
[PRIMARYSALUTATION_NAMEFORMATFUNCTIONID],
[ISDECEASED],
[DECEASEDDATE],
[EDUCATIONALHISTORY_EDUCATIONALINSTITUTIONID],
[EDUCATIONALHISTORY_ISPRIMARYRECORD],
[EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE],
[EDUCATIONALHISTORY_CLASSOF],
[EDUCATIONALHISTORY_EDUCATIONALDEGREECODEID],
[EDUCATIONALHISTORY_STARTDATE],
[SPOUSEID],
[SPOUSE_RELATIONSHIPTYPECODEID],
[SPOUSE_RECIPROCALTYPECODEID],
[SPOUSE_KEYNAME],
[SPOUSE_FIRSTNAME],
[SPOUSE_MIDDLENAME],
[SPOUSE_MAIDENNAME],
[SPOUSE_NICKNAME],
[SPOUSE_TITLECODEID],
[SPOUSE_SUFFIXCODEID],
[SPOUSE_GENDERCODE],
[SPOUSE_BIRTHDATE],
[SPOUSE_EMAILADDRESS_EMAILADDRESSTYPECODEID],
[SPOUSE_EMAILADDRESS_EMAILADDRESS],
[SPOUSE_COPY_ADDRESS],
[SPOUSE_COPY_PHONENUMBER],
[SPOUSE_COPY_EMAILADDRESS],
[PRIMARYBUSINESSID],
[PRIMARYBUSINESS_RELATIONSHIPTYPECODEID],
[PRIMARYBUSINESS_RECIPROCALTYPECODEID],
[PRIMARYBUSINESS_KEYNAME],
[PRIMARYBUSINESS_PHONE_PHONETYPECODEID],
[PRIMARYBUSINESS_PHONE_NUMBER],
[PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESSTYPECODEID],
[PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESS],
[PRIMARYBUSINESS_ADDRESS_ADDRESSTYPECODEID],
[PRIMARYBUSINESS_ADDRESS_COUNTRYID],
[PRIMARYBUSINESS_ADDRESS_ADDRESSBLOCK],
[PRIMARYBUSINESS_ADDRESS_CITY],
[PRIMARYBUSINESS_ADDRESS_STATEID],
[PRIMARYBUSINESS_ADDRESS_POSTCODE],
[PRIMARYBUSINESS_ADDRESS_CART],
[PRIMARYBUSINESS_ADDRESS_DPC],
[PRIMARYBUSINESS_ADDRESS_LOT],
[PRIMARYBUSINESS_ADDRESS_DONOTMAIL],
[PRIMARYBUSINESS_INDUSTRYCODEID],
[PRIMARYBUSINESS_NUMEMPLOYEES],
[PRIMARYBUSINESS_NUMSUBSIDIARIES],
[PRIMARYBUSINESS_WEBADDRESS],
[PRIMARYBUSINESS_PARENTCORPID],
[CUSTOMID],
[CONSTIT_SECURITY_ATTRIBUTEID],
[ISGROUP],
[GROUPTYPECODE],
[GROUPTYPEID],
[GROUPSTARTDATE],
[GROUPDESCRIPTION],
[SEQUENCE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[ADDRESS_COUNTYCODEID],
[ADDRESS_OMITFROMVALIDATION],
[ADDRESS_CONGRESSIONALDISTRICTCODEID],
[ADDRESS_STATEHOUSEDISTRICTCODEID],
[ADDRESS_STATESENATEDISTRICTCODEID],
[ADDRESS_LOCALPRECINCTCODEID],
[ADDRESS_INFOSOURCECODEID],
[ADDRESS_REGIONCODEID],
[ADDRESS_LASTVALIDATIONATTEMPTDATE],
[ADDRESS_VALIDATIONMESSAGE],
[ADDRESS_CERTIFICATIONDATA],
[ADDRESS_DONOTMAILREASONCODEID],
[PRIMARYBUSINESS_ADDRESS_DONOTMAILREASONCODEID],
[EDUCATIONALHISTORY_ACADEMICCATALOGPROGRAMID],
[EDUCATIONALHISTORY_EDUCATIONALPROGRAMCODEID],
[EDUCATIONALHISTORY_ACADEMICCATALOGDEGREEID],
[EDUCATIONALHISTORY_EDUCATIONALAWARDCODEID],
[EDUCATIONALHISTORY_PREFERREDCLASSYEAR],
[EDUCATIONALHISTORY_EDUCATIONALSOURCECODEID],
[EDUCATIONALHISTORY_EDUCATIONALSOURCEDATE],
[EDUCATIONALHISTORY_COMMENT],
[EDUCATIONALHISTORY_DATEGRADUATED],
[EDUCATIONALHISTORY_DATELEFT],
[EDUCATIONALHISTORY_LEVELCODEID],
[EDUCATIONALHISTORY_REASONCODEID],
[ISPRIMARYORGANIZATION],
[PRIMARYBUSINESS_ISPRIMARYORGANIZATION],
[CONSTITUENT_SITEID],
[ADDRESS_HISTORICALSTARTDATE]
)
values
(
@ID,
@BATCHID,
case when @ISORGANIZATION = 1 then 1 else 0 end,
@KEYNAME,
@FIRSTNAME,
@MIDDLENAME,
@MAIDENNAME,
@NICKNAME,
@TITLECODEID,
@SUFFIXCODEID,
@BIRTHDATE,
@GENDERCODE,
@WEBADDRESS,
@INDUSTRYCODEID,
@PARENTCORPID,
@NUMEMPLOYEES,
@NUMSUBSIDIARIES,
@ADDRESS_ADDRESSTYPECODEID,
@ADDRESS_COUNTRYID,
@ADDRESS_ADDRESSBLOCK,
@ADDRESS_CITY,
@ADDRESS_STATEID,
@ADDRESS_POSTCODE,
@ADDRESS_DONOTMAIL,
@EMAILADDRESS_EMAILADDRESSTYPECODEID,
@EMAILADDRESS_EMAILADDRESS,
@ADDRESS_CART,
@ADDRESS_DPC,
@ADDRESS_LOT,
@ADDRESS_STARTDATE,
@ADDRESS_ENDDATE,
@PRIMARYADDRESSEE_NAMEFORMATTYPECODEID,
@PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID,
@PRIMARYSALUTATION_NAMEFORMATTYPECODEID,
@PRIMARYSALUTATION_NAMEFORMATFUNCTIONID,
@ISDECEASED,
@DECEASEDDATE,
@EDUCATIONALHISTORY_EDUCATIONALINSTITUTIONID,
@EDUCATIONALHISTORY_ISPRIMARYRECORD,
@EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE,
@EDUCATIONALHISTORY_CLASSOF,
@EDUCATIONALHISTORY_EDUCATIONALDEGREECODEID,
@EDUCATIONALHISTORY_STARTDATE,
@SPOUSEID,
@SPOUSE_RELATIONSHIPTYPECODEID,
@SPOUSE_RECIPROCALTYPECODEID,
@SPOUSE_KEYNAME,
@SPOUSE_FIRSTNAME,
@SPOUSE_MIDDLENAME,
@SPOUSE_MAIDENNAME,
@SPOUSE_NICKNAME,
@SPOUSE_TITLECODEID,
@SPOUSE_SUFFIXCODEID,
@SPOUSE_GENDERCODE,
@SPOUSE_BIRTHDATE,
@SPOUSE_EMAILADDRESS_EMAILADDRESSTYPECODEID,
@SPOUSE_EMAILADDRESS_EMAILADDRESS,
@SPOUSE_COPY_ADDRESS,
@SPOUSE_COPY_PHONENUMBER,
@SPOUSE_COPY_EMAILADDRESS,
@PRIMARYBUSINESSID,
@PRIMARYBUSINESS_RELATIONSHIPTYPECODEID,
@PRIMARYBUSINESS_RECIPROCALTYPECODEID,
@PRIMARYBUSINESS_KEYNAME,
@PRIMARYBUSINESS_PHONE_PHONETYPECODEID,
@PRIMARYBUSINESS_PHONE_NUMBER,
@PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESSTYPECODEID,
@PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESS,
@PRIMARYBUSINESS_ADDRESS_ADDRESSTYPECODEID,
@PRIMARYBUSINESS_ADDRESS_COUNTRYID,
@PRIMARYBUSINESS_ADDRESS_ADDRESSBLOCK,
@PRIMARYBUSINESS_ADDRESS_CITY,
@PRIMARYBUSINESS_ADDRESS_STATEID,
@PRIMARYBUSINESS_ADDRESS_POSTCODE,
@PRIMARYBUSINESS_ADDRESS_CART,
@PRIMARYBUSINESS_ADDRESS_DPC,
@PRIMARYBUSINESS_ADDRESS_LOT,
@PRIMARYBUSINESS_ADDRESS_DONOTMAIL,
@PRIMARYBUSINESS_INDUSTRYCODEID,
@PRIMARYBUSINESS_NUMEMPLOYEES,
@PRIMARYBUSINESS_NUMSUBSIDIARIES,
@PRIMARYBUSINESS_WEBADDRESS,
@PRIMARYBUSINESS_PARENTCORPID,
@CUSTOMIDENTIFIER,
@CONSTIT_SECURITY_ATTRIBUTEID,
-- ISORGANIZATION = {0 => Individual, 1 => Organization, 2 => Household, 3 => Custom group}
case when (@ISORGANIZATION = 2 or @ISORGANIZATION = 3) then 1 else 0 end, -- ISGROUP
case
when @ISORGANIZATION = 2 then 0
when @ISORGANIZATION = 3 then 1
else 0
end, -- GROUPTYPECODE
@GROUPTYPEID,
@GROUPSTARTDATE,
@GROUPDESCRIPTION,
@SEQUENCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@ADDRESS_COUNTYCODEID,
@ADDRESS_OMITFROMVALIDATION,
@ADDRESS_CONGRESSIONALDISTRICTCODEID,
@ADDRESS_STATEHOUSEDISTRICTCODEID,
@ADDRESS_STATESENATEDISTRICTCODEID,
@ADDRESS_LOCALPRECINCTCODEID,
@ADDRESS_INFOSOURCECODEID,
@ADDRESS_REGIONCODEID,
@ADDRESS_LASTVALIDATIONATTEMPTDATE,
@ADDRESS_VALIDATIONMESSAGE,
@ADDRESS_CERTIFICATIONDATA,
@ADDRESS_DONOTMAILREASONCODEID,
@PRIMARYBUSINESS_ADDRESS_DONOTMAILREASONCODEID,
@EDUCATIONALHISTORY_ACADEMICCATALOGPROGRAMID,
@EDUCATIONALHISTORY_EDUCATIONALPROGRAMCODEID,
@EDUCATIONALHISTORY_ACADEMICCATALOGDEGREEID,
@EDUCATIONALHISTORY_EDUCATIONALAWARDCODEID,
@EDUCATIONALHISTORY_PREFERREDCLASSYEAR,
@EDUCATIONALHISTORY_EDUCATIONALSOURCECODEID,
@EDUCATIONALHISTORY_EDUCATIONALSOURCEDATE,
@EDUCATIONALHISTORY_COMMENT,
@EDUCATIONALHISTORY_DATEGRADUATED,
@EDUCATIONALHISTORY_DATELEFT,
@EDUCATIONALHISTORY_LEVELCODEID,
@EDUCATIONALHISTORY_REASONCODEID,
@ISPRIMARYORGANIZATION,
@PRIMARYBUSINESS_ISPRIMARYORGANIZATION,
@CONSTITUENT_SITEID,
@ADDRESS_HISTORICALSTARTDATE
);
exec dbo.USP_CONSTITUENTBATCH_GETPHONES_ADDFROMXML @ID, @PHONES, @CHANGEAGENTID;
declare @USERDEFINEDCONSTITUENCY xml;
set @USERDEFINEDCONSTITUENCY =
(
select
CONSTITUENCY.c.query('.')
from
@CONSTITUENCY.nodes('/CONSTITUENCY/ITEM') CONSTITUENCY(c)
left join dbo.CONSTITUENCYSYSTEMNAME on CONSTITUENCY.c.value('(CONSTITUENCYCODEID)[1]', 'uniqueidentifier') = CONSTITUENCYSYSTEMNAME.ID
where
CONSTITUENCYSYSTEMNAME.ID is null
for xml raw(''),type,elements,root('CONSTITUENCY'),BINARY BASE64
);
exec dbo.USP_CONSTITUENTBATCH_GETCONSTITUENCIES_ADDFROMXML @ID, @USERDEFINEDCONSTITUENCY, @CHANGEAGENTID;
declare @SYSTEMDEFINEDCONSTITUENCY xml;
set @SYSTEMDEFINEDCONSTITUENCY =
(
select
CONSTITUENCY.c.query('.')
from
@CONSTITUENCY.nodes('/CONSTITUENCY/ITEM') CONSTITUENCY(c)
inner join dbo.CONSTITUENCYSYSTEMNAME on CONSTITUENCY.c.value('(CONSTITUENCYCODEID)[1]', 'uniqueidentifier') = CONSTITUENCYSYSTEMNAME.ID
for xml raw(''),type,elements,root('CONSTITUENCY'),BINARY BASE64
);
exec dbo.USP_CONSTITUENTBATCH_GETCONSTITUENCIES_SYSTEM_ADDFROMXML @ID, @SYSTEMDEFINEDCONSTITUENCY, @CHANGEAGENTID;
exec dbo.USP_CONSTITUENTBATCH_GETINTERESTS_ADDFROMXML @ID, @INTEREST, @CHANGEAGENTID;
exec dbo.USP_CONSTITUENTBATCH_GETBATCHALTERNATELOOKUPIDS_ADDFROMXML @ID, @ALTERNATELOOKUPIDS, @CHANGEAGENTID
exec dbo.USP_CONSTITUENTBATCH_GETGROUPMEMBERS_ADDFROMXML @ID, @GROUPMEMBERS, @CHANGEAGENTID;
exec dbo.USP_CONSTITUENTBATCH_GETEDUCATIONAFFILIATEDADDITIONALINFORMATION_ADDFROMXML @ID, @AFFILIATEDADDITIONALINFORMATION, @CHANGEAGENTID;
exec dbo.USP_CONSTITUENTBATCH_GETEDUCATIONUNAFFILIATEDADDITIONALINFORMATION_ADDFROMXML @ID, @UNAFFILIATEDADDITIONALINFORMATION, @CHANGEAGENTID;
exec dbo.USP_CONSTITUENTBATCH_GETEDUCATIONALINVOLVEMENT_ADDFROMXML @ID, @EDUCATIONALINVOLVEMENT, @CHANGEAGENTID;
exec dbo.USP_CONSTITUENTBATCH_GETTAXDECLARATIONS_ADDFROMXML @ID, @TAXDECLARATIONS, @CHANGEAGENTID;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;