USP_DATAFORMTEMPLATE_ADD_CONSTITUENTBATCHCOMMIT3

The save procedure used by the add dataform template "Constituent Batch Row Commit Add Form 3".

Parameters

Parameter Parameter Type Mode Description
@ID uniqueidentifier INOUT The output parameter indicating the ID of the record 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
@VALIDATEONLY bit IN Validate only
@BATCHNUMBER nvarchar(100) IN
@ADDRESS_CART nvarchar(10) IN CART
@ADDRESS_DPC nvarchar(8) IN DPC
@ADDRESS_LOT nvarchar(5) IN LOT
@ADDRESS_STARTDATE UDT_MONTHDAY IN Address start date
@ADDRESS_ENDDATE UDT_MONTHDAY IN Address 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 of
@EDUCATIONALHISTORY_EDUCATIONALDEGREECODEID uniqueidentifier IN Educational degree
@EDUCATIONALHISTORY_STARTDATE UDT_FUZZYDATE IN Educational date from
@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_PHONE_COUNTRYID uniqueidentifier IN Business phone country
@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 Constituent information 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 consolidation 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 Honor awarded
@EDUCATIONALHISTORY_PREFERREDCLASSYEAR UDT_YEAR IN Preferred class of
@EDUCATIONALHISTORY_EDUCATIONALSOURCECODEID uniqueidentifier IN Informational source
@EDUCATIONALHISTORY_EDUCATIONALSOURCEDATE UDT_FUZZYDATE IN Source date
@EDUCATIONALHISTORY_COMMENT nvarchar(500) IN Comments
@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
@PRIMARYMEMBER_COPYCONTACTINFO bit IN Use primary member contact info
@PRIMARYBUSINESS_JOBTITLE nvarchar(100) IN Business job title
@PRIMARYBUSINESS_JOBCATEGORYCODEID uniqueidentifier IN Business job category
@PRIMARYBUSINESS_CAREERLEVELCODEID uniqueidentifier IN Business career level
@PRIMARYBUSINESS_ADDRESS_INFOSOURCECODEID uniqueidentifier IN Business information source
@TITLE2CODEID uniqueidentifier IN Title 2
@SUFFIX2CODEID uniqueidentifier IN Suffix 2
@SPOUSE_TITLE2CODEID uniqueidentifier IN Spouse title 2
@SPOUSE_SUFFIX2CODEID uniqueidentifier IN Spouse suffix 2
@SOLICITCODES xml IN Solicit codes
@PRIMARYBUSINESS_JOBDIVISION nvarchar(100) IN Business job division
@PRIMARYBUSINESS_JOBDEPARTMENT nvarchar(100) IN Business job department
@PRIMARYBUSINESS_JOBSCHEDULECODEID uniqueidentifier IN Business job schedule
@SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST uniqueidentifier IN Spouse relationship type
@SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST uniqueidentifier IN Spouse reciprocal type
@PRIMARYBUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST uniqueidentifier IN Business relationship type
@PRIMARYBUSINESS_RECIPROCALTYPECODEIDSIMPLELIST uniqueidentifier IN Business reciprocal type
@ETHNICITYCODEID uniqueidentifier IN Ethnicity
@RELIGIONCODEID uniqueidentifier IN Religion
@TARGETCODEID uniqueidentifier IN Target
@INCOMECODEID uniqueidentifier IN Income
@BIRTHPLACE nvarchar(50) IN Birthplace
@BATCHROWID uniqueidentifier IN
@PRIMARYBUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit IN Apply recognition from business
@PRIMARYBUSINESS_PRIMARYSOFTCREDITMATCHFACTOR decimal(5, 2) IN Recognition from business match percent
@PRIMARYBUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit IN Apply recognition to business
@PRIMARYBUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5, 2) IN Recognition to business match percent
@PRIMARYBUSINESS_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier IN Recognition from business type
@PRIMARYBUSINESS_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier IN Recognition to business type
@EMAILADDRESS_STARTDATE date IN Email address start date
@SPOUSE_EMAILADDRESS_STARTDATE date IN Spouse email address start date
@PRIMARYBUSINESS_EMAILADDRESS_STARTDATE date IN Business email address start date
@ADDRESS_HISTORICALSTARTDATE date IN Address start date
@SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit IN Apply recognition from spouse
@SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR decimal(5, 2) IN Recognition from spouse match percent
@SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit IN Apply recognition to spouse
@SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5, 2) IN Recognition to spouse match percent
@SPOUSE_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier IN Recognition from spouse type
@SPOUSE_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier IN Recognition to spouse type
@PRIMARYBUSINESS_STARTDATE date IN
@SPOUSE_STARTDATE date IN
@SOCIALMEDIAACCOUNTS xml IN
@EMAILADDRESS_INFOSOURCECODEID uniqueidentifier IN
@EMAILADDRESS_INFOSOURCECOMMENTS nvarchar(500) IN
@GENDERCODEID uniqueidentifier IN
@SPOUSE_GENDERCODEID uniqueidentifier IN

Definition

Copy 


CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_CONSTITUENTBATCHCOMMIT3
    (
        @ID uniqueidentifier = null output,                            
        @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) = '',
        @VALIDATEONLY bit = 0,
        @BATCHNUMBER nvarchar(100),
        @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_PHONE_COUNTRYID uniqueidentifier = null,
        @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) = '',
        @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,
        @PRIMARYMEMBER_COPYCONTACTINFO bit = 0,
        @PRIMARYBUSINESS_JOBTITLE nvarchar(100) = '',
        @PRIMARYBUSINESS_JOBCATEGORYCODEID uniqueidentifier = null,
        @PRIMARYBUSINESS_CAREERLEVELCODEID uniqueidentifier = null,
        @PRIMARYBUSINESS_ADDRESS_INFOSOURCECODEID uniqueidentifier = null,
        @TITLE2CODEID uniqueidentifier = null,
        @SUFFIX2CODEID uniqueidentifier = null,
        @SPOUSE_TITLE2CODEID uniqueidentifier = null,
        @SPOUSE_SUFFIX2CODEID uniqueidentifier = null,
        @SOLICITCODES xml = null,
        @PRIMARYBUSINESS_JOBDIVISION nvarchar(100) = '',
        @PRIMARYBUSINESS_JOBDEPARTMENT nvarchar(100) = '',
        @PRIMARYBUSINESS_JOBSCHEDULECODEID uniqueidentifier = null,
        @SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST uniqueidentifier = null,
        @SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST uniqueidentifier = null,
        @PRIMARYBUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST uniqueidentifier = null,
        @PRIMARYBUSINESS_RECIPROCALTYPECODEIDSIMPLELIST uniqueidentifier = null,
        @ETHNICITYCODEID uniqueidentifier = null,
        @RELIGIONCODEID uniqueidentifier = null,
        @TARGETCODEID uniqueidentifier = null,
        @INCOMECODEID uniqueidentifier = null,
        @BIRTHPLACE nvarchar(50) = '',
        @BATCHROWID uniqueidentifier = null,
        @PRIMARYBUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = 0,
        @PRIMARYBUSINESS_PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
        @PRIMARYBUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = 0,
        @PRIMARYBUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
        @PRIMARYBUSINESS_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null,
        @PRIMARYBUSINESS_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null,
        @EMAILADDRESS_STARTDATE date = null,
        @SPOUSE_EMAILADDRESS_STARTDATE date = null,
        @PRIMARYBUSINESS_EMAILADDRESS_STARTDATE date = null,
        @ADDRESS_HISTORICALSTARTDATE date = null,
        @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = 0,
        @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
        @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = 0,
        @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
        @SPOUSE_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null,
        @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null,
        @PRIMARYBUSINESS_STARTDATE date = null,
        @SPOUSE_STARTDATE date = null,
        @SOCIALMEDIAACCOUNTS xml = null,
        @EMAILADDRESS_INFOSOURCECODEID uniqueidentifier = null,
        @EMAILADDRESS_INFOSOURCECOMMENTS nvarchar(500) = '',
        @GENDERCODEID uniqueidentifier = null,
        @SPOUSE_GENDERCODEID uniqueidentifier = null
    ) as
        set nocount on;

        declare
            @CURRENTDATE datetime,
            @ErrorMessage nvarchar(1000),
            @InfoMsg nvarchar(100),
            @ErrorSeverity int,
            @ErrorState int;

        if @ID is null
            set @ID = newid();

        if @CHANGEAGENTID is null
            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

        set @CURRENTDATE = getdate();

        declare @SETID uniqueidentifier;

        begin try
            declare @ADDRESSINCLUDED bit;
            set @ADDRESSINCLUDED = 0;

            if (@PRIMARYMEMBER_COPYCONTACTINFO = 0) and (
                    (@ADDRESS_ADDRESSTYPECODEID is not null) or (@ADDRESS_COUNTRYID is not null) or
                    (@ADDRESS_STATEID is not null) or (coalesce(@ADDRESS_ADDRESSBLOCK,'') <> '') or
                    (coalesce(@ADDRESS_CITY,'') <> '') or (coalesce(@ADDRESS_POSTCODE,'') <> '') or
                    (coalesce(@ADDRESS_CART,'') <> '') or (coalesce(@ADDRESS_DPC,'') <> '') or
                    (coalesce(@ADDRESS_LOT,'') <> '') or (coalesce(@ADDRESS_STARTDATE,'0000') <> '0000') or
                    (coalesce(@ADDRESS_ENDDATE,'0000') <> '0000'))
                set @ADDRESSINCLUDED = 1;

            if @ADDRESSINCLUDED = 1 begin
                if @ADDRESS_COUNTRYID is null
                    exec @ADDRESS_COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT;

                if dbo.UFN_STATE_GETCOUNTRY(@ADDRESS_STATEID, @ADDRESS_COUNTRYID) <> @ADDRESS_COUNTRYID begin
                    raiserror('Constituent - invalid state for country.', 13, 1);
                    return 1;
                end
            end

            declare @PRIMARYBUSINESS_ADDRESSINCLUDED bit;
            set @PRIMARYBUSINESS_ADDRESSINCLUDED = 0;

            if (@PRIMARYBUSINESS_ADDRESS_ADDRESSTYPECODEID is not null) or (@PRIMARYBUSINESS_ADDRESS_COUNTRYID 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,'') <> '') or
                        (coalesce(@PRIMARYBUSINESS_ADDRESS_CART,'') <> '') or (coalesce(@PRIMARYBUSINESS_ADDRESS_DPC,'') <> '') or
                        (coalesce(@PRIMARYBUSINESS_ADDRESS_LOT,'') <> '')
                set @PRIMARYBUSINESS_ADDRESSINCLUDED = 1;

            if @PRIMARYBUSINESS_ADDRESSINCLUDED = 1 begin
                if @PRIMARYBUSINESS_ADDRESS_COUNTRYID is null
                    exec @PRIMARYBUSINESS_ADDRESS_COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT;

                if dbo.UFN_STATE_GETCOUNTRY(@PRIMARYBUSINESS_ADDRESS_STATEID, @PRIMARYBUSINESS_ADDRESS_COUNTRYID) <> @PRIMARYBUSINESS_ADDRESS_COUNTRYID begin
                    raiserror('Primary business - invalid state for country.', 13, 1);
                    return 1;
                end
            end

            if (@PRIMARYADDRESSEE_NAMEFORMATTYPECODEID is not null) and (@PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID is not null) and
                    (@PRIMARYSALUTATION_NAMEFORMATTYPECODEID is not null) and (@PRIMARYSALUTATION_NAMEFORMATFUNCTIONID is not null) and
                    (@PRIMARYADDRESSEE_NAMEFORMATTYPECODEID = @PRIMARYSALUTATION_NAMEFORMATTYPECODEID) and
                    (@PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID <> @PRIMARYSALUTATION_NAMEFORMATFUNCTIONID) begin
                raiserror('Only one name format can be specified per name format type.',13,1);
                return 1;
            end

            if (not @SPOUSEID is null)
            begin
                declare @SPOUSENAME nvarchar(400) = null;

                select @SPOUSENAME = CONSTITUENT.NAME
                from dbo.RELATIONSHIP
                    inner join dbo.CONSTITUENT
                        on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
                where (RELATIONSHIP.ISSPOUSE = 1) and (RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @SPOUSEID)

                if (not @SPOUSENAME is null)
                begin
                    declare @ERROR nvarchar(800);
                    set @ERROR = 'Spouse entered has an existing spouse, ' + @SPOUSENAME + '.';
                    raiserror(@ERROR,13,1);
                    return 1;
                end
            end

            exec dbo.USP_PHONES_VALIDATEPHONES @PHONES;
            exec dbo.USP_CONSTITUENCIES_VALIDATECONSTITUENCIES @CONSTITUENCY;

            -- If the constituent is a group or household, verify only one member was set as primary

            declare @GROUPMEMBERSTABLE table
            (
                CONSTITUENTID uniqueidentifier,
                ISPRIMARY bit
            )

            if @ISORGANIZATION = 2 or @ISORGANIZATION = 3
            begin
                insert into @GROUPMEMBERSTABLE
                (
                    CONSTITUENTID,
                    ISPRIMARY
                )
                select
                    CONSTITUENTID,
                    ISPRIMARY
                from dbo.UFN_CONSTITUENTBATCH_GETGROUPMEMBERS_FROMITEMLISTXML(@GROUPMEMBERS)

                declare @PRIMARYMEMBERCOUNT int
                select @PRIMARYMEMBERCOUNT = count(*)
                from @GROUPMEMBERSTABLE
                where ISPRIMARY = 1

                if @PRIMARYMEMBERCOUNT > 1
                begin
                    raiserror('Only one constituent can be the primary member.', 13, 1)
                    return 1
                end

                if exists(select CONSTITUENTID from @GROUPMEMBERSTABLE group by CONSTITUENTID having count(*) > 1)
                begin
                    raiserror('A constituent can only be added as a member once.', 13, 1)
                    return 1
                end
            end

            declare @BATCHOWNERID uniqueidentifier
            select
                @BATCHOWNERID = APPUSERID
            from dbo.BATCH
            inner join dbo.BATCHCONSTITUENT on BATCH.ID = BATCHCONSTITUENT.BATCHID
            where BATCHCONSTITUENT.ID = @BATCHROWID

            -- Validate the user has permission for the spouse, primary business and parent corp

            if dbo.UFN_CONSTITUENT_CANAPPUSERMODIFY(@SPOUSEID, @BATCHOWNERID) = 0
            begin
                raiserror('ERR_SPOUSE_NOACCESS', 13, 1)
                return 0
            end

            if dbo.UFN_CONSTITUENT_CANAPPUSERMODIFY(@PRIMARYBUSINESSID, @BATCHOWNERID) = 0
            begin
                raiserror('ERR_PRIMARYBUSINESS_NOACCESS', 13, 1)
                return 0
            end

            if dbo.UFN_CONSTITUENT_CANAPPUSERMODIFY(@PRIMARYBUSINESS_PARENTCORPID, @BATCHOWNERID) = 0
            begin
                raiserror('ERR_PRIMARYBUSINESSPARENTCORP_NOACCESS', 13, 1)
                return 0
            end

            if dbo.UFN_CONSTITUENT_CANAPPUSERMODIFY(@PARENTCORPID, @BATCHOWNERID) = 0
            begin
                raiserror('ERR_PARENTCORP_NOACCESS', 13, 1)
                return 0
            end

            if exists (select 1 from @GROUPMEMBERSTABLE where dbo.UFN_CONSTITUENT_CANAPPUSERMODIFY(CONSTITUENTID, @BATCHOWNERID) = 0)
            begin
                raiserror('ERR_MEMBERS_NOACCESS', 13, 1)
                return 0
            end

            if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('133f9bca-00f1-4007-9792-586b931340c6') = 1
            begin                    
                if (@CONSTITUENT_SITEID is not null and not exists (select 1 from dbo.UFN_SITESFORUSER(@BATCHOWNERID) where SITEID = @CONSTITUENT_SITEID))
                    raiserror('ERR_SITE_PERMISSIONDENIED', 13, 1);
            end

            if (@CONSTIT_SECURITY_ATTRIBUTEID is null and dbo.UFN_CONSTITUENTSECURITYATTRIBUTE_REQUIREDFORUSER(@BATCHOWNERID) = 1)
                raiserror('ERR_SECURITYATTRIBUTE_REQUIRED', 13, 1);

            if (@CONSTIT_SECURITY_ATTRIBUTEID is not null and not exists (select 1 from dbo.UFN_CONSTITUENTSECURITYATTRIBUTE_GETATTRIBUTESFORUSER(@BATCHOWNERID) where ID = @CONSTIT_SECURITY_ATTRIBUTEID))
                raiserror('ERR_SECURITYATTRIBUTE_PERMISSIONDENIED', 13, 1);

            if @VALIDATEONLY = 1
            begin
                declare @BATCHID uniqueidentifier;

                select
                  @BATCHID = BATCHID
                from
                  dbo.BATCHCONSTITUENT
                where
                  ID = @BATCHROWID;

                if exists
                    (select 1
                        from dbo.CONSTITUENT
                        where @CUSTOMIDENTIFIER <> '' and CONSTITUENT.CUSTOMIDENTIFIER = @CUSTOMIDENTIFIER
                    ) begin
                        raiserror('Lookup ID must be unique.', 13, 1);
                        return 1;
                end
                if exists
                    (select 1
                        from 
                        dbo.UFN_CONSTITUENTBATCH_GETBATCHALTERNATELOOKUPIDS_FROMITEMLISTXML(@ALTERNATELOOKUPIDS) BATCHALTERNATE
                        inner join dbo.ALTERNATELOOKUPID
                          on BATCHALTERNATE.ALTERNATELOOKUPIDTYPECODEID = ALTERNATELOOKUPID.ALTERNATELOOKUPIDTYPECODEID
                          and BATCHALTERNATE.ALTERNATELOOKUPID = ALTERNATELOOKUPID.ALTERNATELOOKUPID
                    ) begin
                        raiserror('BBERR_ALTERNATELOOKUPID_UNIQUE', 13, 1);
                        return 1;
                end
                else if
                    (select count(BATCHCONSTITUENT.ID)
                        from dbo.BATCHCONSTITUENT
                        where 
                            BATCHID = @BATCHID and
                            coalesce(@CUSTOMIDENTIFIER, '') <> '' and 
                            BATCHCONSTITUENT.CUSTOMID = @CUSTOMIDENTIFIER
                    ) > 1 begin
                        raiserror('The Lookup ID has multiple occurrences in this batch.', 13, 1);
                        return 1;
                end

                if (not @EDUCATIONALHISTORY_STARTDATE is null and not @EDUCATIONALHISTORY_DATEGRADUATED is null) and @EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE = 3 and ((dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(@EDUCATIONALHISTORY_STARTDATE) > dbo.UFN_DATE_LATESTFROMFUZZYDATE(@EDUCATIONALHISTORY_DATEGRADUATED))) begin
                    raiserror('Educational date graduated must not be earlier than educational start date.',13,1);
                    return 1;
                end

                if (not @EDUCATIONALHISTORY_STARTDATE is null and not @EDUCATIONALHISTORY_DATELEFT is null) and @EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE = 2 and ((dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(@EDUCATIONALHISTORY_STARTDATE) > dbo.UFN_DATE_LATESTFROMFUZZYDATE(@EDUCATIONALHISTORY_DATELEFT))) begin
                    raiserror('Educational date left must not be earlier than educational start date.',13,1);
                    return 1;
                end

                exec dbo.USP_BATCH_VALIDATETAXDECLARATIONS @TAXDECLARATIONS = @TAXDECLARATIONS, @BATCHTYPE = 0

                -- Verify the solicit codes are valid

                if @SOLICITCODES is not null
                begin
                    declare @SOLICITCODESTABLE table
                    (
                        ID uniqueidentifier,
                        STARTDATE datetime,
                        ENDDATE datetime,
                        SOLICITCODEID uniqueidentifier
                    )

                    insert into @SOLICITCODESTABLE
                    (
                        ID,
                        STARTDATE,
                        ENDDATE,
                        SOLICITCODEID
                    )
                    select
                        case when ID is null then newid() else ID end,
                        STARTDATE,
                        ENDDATE,
                        SOLICITCODEID
                    from dbo.UFN_CONSTITUENTBATCH_GETSOLICITCODES_FROMITEMLISTXML(@SOLICITCODES)

                    -- Check for solicit codes whose end date is before the start date

                    if exists (    select 1
                                from @SOLICITCODESTABLE
                                where
                                    STARTDATE is not null and
                                    STARTDATE > ENDDATE)
                        raiserror('BBERR_SOLICITCODES_STARTENDDATE', 13, 1)

                    -- Check for records with the same solicit code and overlapping dates

                    if exists (    select 1
                                from @SOLICITCODESTABLE SOLICITCODEONE
                                inner join @SOLICITCODESTABLE SOLICITCODETWO on
                                    SOLICITCODEONE.ID <> SOLICITCODETWO.ID and
                                    SOLICITCODEONE.SOLICITCODEID = SOLICITCODETWO.SOLICITCODEID and
                                    dbo.UFN_DATES_AREDATESOVERLAPPING(SOLICITCODEONE.STARTDATE, SOLICITCODEONE.ENDDATE, SOLICITCODETWO.STARTDATE, SOLICITCODETWO.ENDDATE) = 1)
                        raiserror('BBERR_SOLICITCODES_NOOVERLAPPINGDATES', 13, 1)
                end

                if @PRIMARYBUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1
                begin
                  if @PRIMARYBUSINESS_PRIMARYSOFTCREDITMATCHFACTOR <= 0 or @PRIMARYBUSINESS_PRIMARYSOFTCREDITMATCHFACTOR > 100
                    raiserror('BBERR_PRIMARYBUSINESS_INVALIDPRIMARYMATCHFACTOR', 13, 1)
                end

                if @PRIMARYBUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1
                begin
                  if @PRIMARYBUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR <= 0 or @PRIMARYBUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR > 100
                    raiserror('BBERR_PRIMARYBUSINESS_INVALIDRECIPROCALMATCHFACTOR', 13, 1)
                end

                if @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1
                begin
                  if @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR <= 0 or @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR > 100
                    raiserror('BBERR_SPOUSE_INVALIDPRIMARYMATCHFACTOR', 13, 1)
                end

                if @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1
                begin
                  if @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR <= 0 or @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR > 100
                    raiserror('BBERR_SPOUSE_INVALIDRECIPROCALMATCHFACTOR', 13, 1)
                end

                if(dbo.UFN_CONSTITUENTBATCH_VALIDATESOCIALMEDIAACCOUNTS(@SOCIALMEDIAACCOUNTS)=0)
                begin
                  raiserror('BBERR_SOCIALMEDIAACCOUNT_DUPLICATENOTALLOWED', 13, 1);
                end

            end
            else if @VALIDATEONLY = 0 begin
                if @ISORGANIZATION = 0 -- individual

                    begin
                        insert into dbo.CONSTITUENT
                        (
                            [ID],
                            [KEYNAME],
                            [FIRSTNAME],
                            [MIDDLENAME],
                            [MAIDENNAME],
                            [NICKNAME],
                            [TITLECODEID],
                            [SUFFIXCODEID],
                            [TITLE2CODEID],
                            [SUFFIX2CODEID],
                            [BIRTHDATE],
                            [GENDERCODE],
                            [WEBADDRESS],
                            [ISORGANIZATION],
                            [CUSTOMIDENTIFIER],
                            [ADDEDBYID],
                            [CHANGEDBYID],
                            [DATEADDED],
                            [DATECHANGED],
                            [ISCONSTITUENT],
                            [GENDERCODEID]
                        )
                        values
                        (
                            @ID,
                            @KEYNAME,
                            @FIRSTNAME,
                            @MIDDLENAME,
                            @MAIDENNAME,
                            @NICKNAME,
                            @TITLECODEID,
                            @SUFFIXCODEID,
                            @TITLE2CODEID,
                            @SUFFIX2CODEID,
                            @BIRTHDATE,
                            @GENDERCODE,
                            @WEBADDRESS,
                            @ISORGANIZATION,
                            @CUSTOMIDENTIFIER,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE,
                            1,
                            @GENDERCODEID
                        );
                    end
                    else if @ISORGANIZATION = 1 -- organization

                    begin                                         
                      declare @KEYNAMEBODY nvarchar(100);
                        declare @KEYNAMEPREFIX nvarchar(50);

                        exec dbo.USP_PARSE_ORGANIZATION_NAME @KEYNAME, @KEYNAMEBODY output, @KEYNAMEPREFIX output;

                        insert into dbo.CONSTITUENT
                        (
                            [ID],
                            [KEYNAME],
                            [KEYNAMEPREFIX],
                            [WEBADDRESS],
                            [ISORGANIZATION],
                            [CUSTOMIDENTIFIER],
                            [ADDEDBYID],
                            [CHANGEDBYID],
                            [DATEADDED],
                            [DATECHANGED]
                        )
                        values
                        (
                            @ID,
                            @KEYNAMEBODY,
                            @KEYNAMEPREFIX,
                            @WEBADDRESS,
                            @ISORGANIZATION,
                            @CUSTOMIDENTIFIER,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                        );

--                                            if (@INDUSTRYCODEID is not null) or (@PARENTCORPID is not null) or 'GG 01/07/09 Bug 17397

--                                                (@NUMEMPLOYEES > 0) or (@NUMSUBSIDIARIES > 0) or (@ISPRIMARYORGANIZATION is not null)

--                                            begin

                            insert into dbo.ORGANIZATIONDATA
                            (
                                [ID],
                                [INDUSTRYCODEID],
                                [PARENTCORPID],
                                [NUMEMPLOYEES],
                                [NUMSUBSIDIARIES],
                                [ADDEDBYID],
                                [CHANGEDBYID],
                                [DATEADDED],
                                [DATECHANGED],
                                [ISPRIMARY]
                            )
                            values
                            (
                                @ID,
                                @INDUSTRYCODEID,
                                @PARENTCORPID,
                                @NUMEMPLOYEES,
                                @NUMSUBSIDIARIES,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE,
                                coalesce(@ISPRIMARYORGANIZATION, 0)
                            );

              if @PARENTCORPID is not null
                exec dbo.USP_RELATIONSHIPS_CREATEPARENTORGRELATIONSHIP  @ID, @PARENTCORPID,@CURRENTDATE,@CHANGEAGENTID,@CURRENTDATE,1,null

--                                            end 'GG 01/07/09 Bug 17397

                    end
                    else if (@ISORGANIZATION = 2 or @ISORGANIZATION = 3) 
                    begin
                      insert into dbo.CONSTITUENT
                      (
                          [ID],
                          [KEYNAME],
                          [WEBADDRESS],
                          [ISORGANIZATION],
                          [ISGROUP],
                          [CUSTOMIDENTIFIER],
                          [ADDEDBYID],
                          [CHANGEDBYID],
                          [DATEADDED],
                          [DATECHANGED]
                      )
                      values
                      (
                          @ID,
                          @KEYNAME,
                          @WEBADDRESS,
                          0, -- ISORGANIZATION

                          1, -- ISGROUP

                          @CUSTOMIDENTIFIER,
                          @CHANGEAGENTID,
                          @CHANGEAGENTID,
                          @CURRENTDATE,
                          @CURRENTDATE
                      );

                      insert into dbo.GROUPDATA
                      (
                        ID,
                        GROUPTYPECODE,
                        STARTDATE,
                        GROUPTYPEID,
                        [DESCRIPTION],
                        ADDEDBYID,
                        CHANGEDBYID,
                        DATEADDED,
                        DATECHANGED
                      )
                      values
                      (
                        @ID,
                        case 
                          when @ISORGANIZATION = 2 then 0
                          when @ISORGANIZATION = 3 then 1
                          else null
                        end,
                        @GROUPSTARTDATE,
                        @GROUPTYPEID,
                        @GROUPDESCRIPTION,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE
                      );

                    end                                                                

                    insert into dbo.CONSTITUENTORIGINATION
                    (
                        ID,
                        INFOSOURCECODEID,
                        ADDEDBYID,
                        CHANGEDBYID,
                        DATEADDED,
                        DATECHANGED
                    )
                    values
                    (
                        @ID,
                        @ADDRESS_INFOSOURCECODEID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE
                    );

                    if @CONSTIT_SECURITY_ATTRIBUTEID is not null 
                        insert into dbo.[CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT]
                        (
                            [CONSTIT_SECURITY_ATTRIBUTEID],
                            [CONSTITUENTID],
                            [ADDEDBYID],
                            [CHANGEDBYID],
                            [DATEADDED],
                            [DATECHANGED]
                        )
                        values
                        (
                            @CONSTIT_SECURITY_ATTRIBUTEID,
                            @ID,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                        );

                    if @ISDECEASED = 1 begin
                        insert into dbo.[DECEASEDCONSTITUENT]
                        (
                            [ID],
                            [DECEASEDDATE],
                            [ADDEDBYID],
                            [CHANGEDBYID],
                            [DATEADDED],
                            [DATECHANGED]
                        )
                        values
                        (
                            @ID,
                            @DECEASEDDATE,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                        );
                    end

                    if @GROUPMEMBERS is not null
                    begin
                      declare GROUPMEMBERCURSOR cursor local fast_forward for
                        select
                          CONSTITUENTID,
                          ISPRIMARY
                        from
                          @GROUPMEMBERSTABLE
                        order by ISPRIMARY desc;

                      declare @MEMBERID uniqueidentifier;
                      declare @ISPRIMARY bit;
                      declare @GROUPMEMBERID uniqueidentifier;

                      open GROUPMEMBERCURSOR;
                      fetch next from GROUPMEMBERCURSOR into @MEMBERID, @ISPRIMARY;

                      while @@FETCH_STATUS = 0
                      begin
                        set @GROUPMEMBERID = null;
                        exec dbo.USP_GROUPMEMBERADD @GROUPMEMBERID output, @CHANGEAGENTID, @ID, @MEMBERID;
                        if @ISPRIMARY = 1
                          update dbo.GROUPMEMBER set ISPRIMARY = 1 where ID = @GROUPMEMBERID;
                        fetch next from GROUPMEMBERCURSOR into @MEMBERID, @ISPRIMARY;
                      end

                      --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

                      close GROUPMEMBERCURSOR;
                      deallocate GROUPMEMBERCURSOR;
                    end

                    if @ADDRESSINCLUDED = 1 begin
                        declare @ADDRESSID uniqueidentifier;
                        set @ADDRESSID = newID();

                        insert into dbo.[ADDRESS]
                        (
                            [ID],
                            [CONSTITUENTID],
                            [ADDRESSTYPECODEID],
                            [ISPRIMARY],
                            [DONOTMAIL],
                            [DONOTMAILREASONCODEID],
                            [COUNTRYID],
                            [STATEID],
                            [ADDRESSBLOCK],
                            [CITY],
                            [POSTCODE],
                            [CART],
                            [DPC],
                            [LOT],
                            [STARTDATE],
                            [ENDDATE],
                            [HISTORICALSTARTDATE],
                            [ADDEDBYID],
                            [CHANGEDBYID],
                            [DATEADDED],
                            [DATECHANGED]
                        )
                        values
                        (
                            @ADDRESSID,
                            @ID,
                            @ADDRESS_ADDRESSTYPECODEID,
                            1,
                            @ADDRESS_DONOTMAIL,
                            @ADDRESS_DONOTMAILREASONCODEID,
                            @ADDRESS_COUNTRYID,
                            @ADDRESS_STATEID,
                            @ADDRESS_ADDRESSBLOCK,
                            @ADDRESS_CITY,
                            @ADDRESS_POSTCODE,
                            @ADDRESS_CART,
                            @ADDRESS_DPC,
                            @ADDRESS_LOT,
                            @ADDRESS_STARTDATE,
                            @ADDRESS_ENDDATE,
                            @ADDRESS_HISTORICALSTARTDATE,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                        );

                        insert into dbo.ADDRESSVALIDATIONUPDATE
                        (
                            [ID],
                            [COUNTYCODEID],
                            [CONGRESSIONALDISTRICTCODEID],
                            [STATEHOUSEDISTRICTCODEID],
                            [STATESENATEDISTRICTCODEID],
                            [LOCALPRECINCTCODEID],
                            [INFOSOURCECODEID],
                            [REGIONCODEID],
                            [LASTVALIDATIONATTEMPTDATE],
                            [VALIDATIONMESSAGE],
                            [OMITFROMVALIDATION],
                            [CERTIFICATIONDATA],
                            [ADDEDBYID],
                            [CHANGEDBYID],
                            [DATEADDED],
                            [DATECHANGED]
                 )
                        values
                        (
                            @ADDRESSID,
                            @ADDRESS_COUNTYCODEID,
                            @ADDRESS_CONGRESSIONALDISTRICTCODEID,
                            @ADDRESS_STATEHOUSEDISTRICTCODEID,
                            @ADDRESS_STATESENATEDISTRICTCODEID,
                            @ADDRESS_LOCALPRECINCTCODEID,
                            @ADDRESS_INFOSOURCECODEID,
                            @ADDRESS_REGIONCODEID,
                            @ADDRESS_LASTVALIDATIONATTEMPTDATE,
                            @ADDRESS_VALIDATIONMESSAGE,
                            @ADDRESS_OMITFROMVALIDATION,
                            @ADDRESS_CERTIFICATIONDATA,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                        );
                    end

                    if @PRIMARYMEMBER_COPYCONTACTINFO = 0
                        begin
                            exec dbo.USP_CONSTITUENT_GETPHONESWITHSEASONAL_ADDFROMXML @ID, @PHONES, @CHANGEAGENTID;

                            if (@EMAILADDRESS_EMAILADDRESSTYPECODEID is not null) or (coalesce(@EMAILADDRESS_EMAILADDRESS,'') <> '') 
                                      insert into dbo.[EMAILADDRESS]
                                      (
                                          [CONSTITUENTID],
                                          [EMAILADDRESSTYPECODEID],
                                          [EMAILADDRESS],
                                          [ISPRIMARY],
                                          [INFOSOURCECODEID],
                                          [INFOSOURCECOMMENTS],
                                          [ADDEDBYID],
                                          [CHANGEDBYID],
                                          [DATEADDED],
                                          [DATECHANGED],
                                          [STARTDATE]
                                      )
                                      values
                                      (
                                          @ID,
                                          @EMAILADDRESS_EMAILADDRESSTYPECODEID,
                                          @EMAILADDRESS_EMAILADDRESS,
                                          1,
                                          @EMAILADDRESS_INFOSOURCECODEID,
                                          @EMAILADDRESS_INFOSOURCECOMMENTS,
                                          @CHANGEAGENTID,
                                          @CHANGEAGENTID,
                                          @CURRENTDATE,
                                          @CURRENTDATE,
                                          @EMAILADDRESS_STARTDATE
                                       );
                        end
                    else
                        begin
                            declare @PRIMARYMEMBERID uniqueidentifier;

                            select @PRIMARYMEMBERID = MEMBERID
                            from dbo.GROUPMEMBER
                            where ISPRIMARY = 1
                            and GROUPID = @ID;

                            if @PRIMARYMEMBERID is not null
                                begin
                                    exec dbo.USP_CONSTITUENT_COPYPRIMARYCONTACTINFO @CHANGEAGENTID, @PRIMARYMEMBERID, @ID;

                                    declare @PRIMARYMEMBERWEBADDRESS dbo.UDT_WEBADDRESS = '';

                                    select @PRIMARYMEMBERWEBADDRESS = WEBADDRESS
                                    from dbo.CONSTITUENT where ID = @PRIMARYMEMBERID;

                                    update dbo.CONSTITUENT
                                    set WEBADDRESS = @PRIMARYMEMBERWEBADDRESS
                                    where ID = @ID;
                                end
                        end

                    if (@PRIMARYADDRESSEE_NAMEFORMATTYPECODEID is not null) and (@PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID is not null) begin
                        begin try
                            insert into dbo.[NAMEFORMAT]
                            (
                                [CONSTITUENTID],
                                [NAMEFORMATTYPECODEID],
                                [NAMEFORMATFUNCTIONID],
                                [PRIMARYADDRESSEE],
                                [PRIMARYSALUTATION],
                                [ADDEDBYID],
                                [CHANGEDBYID],
                                [DATEADDED],
                                [DATECHANGED]
                            )
                            values
                            (
                                @ID,
                                @PRIMARYADDRESSEE_NAMEFORMATTYPECODEID,
                                @PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID,
                                1,
                                case when @PRIMARYADDRESSEE_NAMEFORMATTYPECODEID = @PRIMARYSALUTATION_NAMEFORMATTYPECODEID then 1 else 0 end,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE
                            );
                        end try
                        begin catch
                            set @InfoMsg='BBERR_ORIGINAL_ERROR:50001';
                            raiserror (@InfoMsg,1,11);

                            select
                                @ErrorMessage = ERROR_MESSAGE(),
                                @ErrorSeverity = ERROR_SEVERITY(),
                                @ErrorState = ERROR_STATE();

                            raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
                            return 1;
                        end catch
                    end

                    if (@PRIMARYSALUTATION_NAMEFORMATTYPECODEID is not null) and (@PRIMARYSALUTATION_NAMEFORMATFUNCTIONID is not null) and
                            ((@PRIMARYADDRESSEE_NAMEFORMATTYPECODEID is null) or (@PRIMARYADDRESSEE_NAMEFORMATTYPECODEID <> @PRIMARYSALUTATION_NAMEFORMATTYPECODEID)) begin
                        begin try
                            insert into dbo.[NAMEFORMAT]
                            (
                                [CONSTITUENTID],
                                [NAMEFORMATTYPECODEID],
                                [NAMEFORMATFUNCTIONID],
                                [PRIMARYADDRESSEE],
                                [PRIMARYSALUTATION],
                                [ADDEDBYID],
                                [CHANGEDBYID],
                                [DATEADDED],
                                [DATECHANGED]
                            )
                            values
                            (
                                @ID,
                                @PRIMARYSALUTATION_NAMEFORMATTYPECODEID,
                                @PRIMARYSALUTATION_NAMEFORMATFUNCTIONID,
                                0,
                                1,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE
                            );
                        end try
                        begin catch
                            set @InfoMsg='BBERR_ORIGINAL_ERROR:50002';
                            raiserror (@InfoMsg,1,11);

                            select
                                @ErrorMessage = ERROR_MESSAGE(),
                                @ErrorSeverity = ERROR_SEVERITY(),
                                @ErrorState = ERROR_STATE();

                            raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
                            return 1;
                        end catch
                    end

                    /*Start name format defaults*/
                    INSERT INTO [dbo].[NAMEFORMAT]
                        ([CONSTITUENTID]
                        ,[NAMEFORMATTYPECODEID]
                        ,[NAMEFORMATFUNCTIONID]
                        ,[ADDEDBYID]
                        ,[CHANGEDBYID]
                        ,[DATEADDED]
                        ,[DATECHANGED]
                        ,[PRIMARYADDRESSEE]
                        ,[PRIMARYSALUTATION])
                    SELECT
                        @ID
                        ,NFD.NAMEFORMATTYPECODEID
                        ,NFD.NAMEFORMATFUNCTIONID
                        ,@CHANGEAGENTID
                        ,@CHANGEAGENTID
                        ,@CURRENTDATE
                        ,@CURRENTDATE
                        ,NFD.PRIMARYADDRESSEE
                        ,NFD.PRIMARYSALUTATION
                    FROM dbo.NAMEFORMATDEFAULT as NFD
                    WHERE ((NFD.APPLYTOCODE = 0 and @ISORGANIZATION = 0))
                        and (((@PRIMARYADDRESSEE_NAMEFORMATTYPECODEID is null) 
                            and (@PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID is null))
                            or ((NFD.PRIMARYADDRESSEE <> 1) 
                                and (NFD.NAMEFORMATTYPECODEID <> @PRIMARYADDRESSEE_NAMEFORMATTYPECODEID)))
                        and (((@PRIMARYSALUTATION_NAMEFORMATTYPECODEID is null) 
                            and (@PRIMARYSALUTATION_NAMEFORMATFUNCTIONID is null))
                            or ((NFD.PRIMARYSALUTATION <> 1) 
                                and (NFD.NAMEFORMATTYPECODEID <> @PRIMARYSALUTATION_NAMEFORMATTYPECODEID)))
                    /*End name format defaults*/

                    if (@EDUCATIONALHISTORY_EDUCATIONALINSTITUTIONID is not null) begin
                        declare @EDUCATIONALHISTORYID uniqueidentifier;
                        set @EDUCATIONALHISTORYID = newid();

                        declare @ISAFFILIATED bit;
                        select
                            @ISAFFILIATED = ISAFFILIATED
                        from dbo.EDUCATIONALINSTITUTION
                        where
                            ID = @EDUCATIONALHISTORY_EDUCATIONALINSTITUTIONID;

                        declare @USEACADEMICCATALOG bit;                        
                        select top 1
                            @USEACADEMICCATALOG = EC.USEACADEMICCATALOG
                        from
                            dbo.EDUCATIONALCONFIGURATION EC;

                        set @USEACADEMICCATALOG = coalesce(@USEACADEMICCATALOG,0);

                        if @USEACADEMICCATALOG = 1 and @ISAFFILIATED = 1
                        begin
                            insert into dbo.[EDUCATIONALHISTORY]
                            (
                                [ID],
                                [CONSTITUENTID],
                                [EDUCATIONALINSTITUTIONID],
                                [ISPRIMARYRECORD],
                                [CONSTITUENCYSTATUSCODE],
                                [STARTDATE],
                                [DATEGRADUATED],
                                [DATELEFT],
          [CLASSOF],
                                [ACADEMICCATALOGPROGRAMID],
                                [ACADEMICCATALOGDEGREEID],
                                [EDUCATIONALAWARDCODEID],
                                [PREFERREDCLASSYEAR],
                                [EDUCATIONALSOURCECODEID],
                                [EDUCATIONALSOURCEDATE],
                                [COMMENT],
                                [EDUCATIONALHISTORYLEVELCODEID],
                                [EDUCATIONALHISTORYREASONCODEID],
                                [ADDEDBYID],
                                [CHANGEDBYID],
                                [DATEADDED],
                                [DATECHANGED]
                            )
                            values
                            (
                                @EDUCATIONALHISTORYID,
                                @ID,
                                @EDUCATIONALHISTORY_EDUCATIONALINSTITUTIONID,
                                @EDUCATIONALHISTORY_ISPRIMARYRECORD,
                                @EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE,
                                @EDUCATIONALHISTORY_STARTDATE,
                                @EDUCATIONALHISTORY_DATEGRADUATED,
                                @EDUCATIONALHISTORY_DATELEFT,
                                @EDUCATIONALHISTORY_CLASSOF,
                                @EDUCATIONALHISTORY_ACADEMICCATALOGPROGRAMID,
                                @EDUCATIONALHISTORY_ACADEMICCATALOGDEGREEID,
                                @EDUCATIONALHISTORY_EDUCATIONALAWARDCODEID,
                                @EDUCATIONALHISTORY_PREFERREDCLASSYEAR,
                                @EDUCATIONALHISTORY_EDUCATIONALSOURCECODEID,
                                @EDUCATIONALHISTORY_EDUCATIONALSOURCEDATE,
                                @EDUCATIONALHISTORY_COMMENT,
                                @EDUCATIONALHISTORY_LEVELCODEID,
                                @EDUCATIONALHISTORY_REASONCODEID,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE
                            );

                            exec dbo.USP_EDUCATIONALHISTORY_GETAFFILIATEDADDITIONALINFORMATION_ADDFROMXML @EDUCATIONALHISTORYID, @AFFILIATEDADDITIONALINFORMATION, @CHANGEAGENTID;
                        end
                        else
                        begin
                            insert into dbo.[EDUCATIONALHISTORY]
                            (
                                [ID],
                                [CONSTITUENTID],
                                [EDUCATIONALINSTITUTIONID],
                                [ISPRIMARYRECORD],
                                [CONSTITUENCYSTATUSCODE],
                                [EDUCATIONALDEGREECODEID],
                                [STARTDATE],
                                [DATEGRADUATED],
                                [DATELEFT],
                                [CLASSOF],
                                [EDUCATIONALPROGRAMCODEID],
                                [EDUCATIONALAWARDCODEID],
                                [PREFERREDCLASSYEAR],
                                [EDUCATIONALSOURCECODEID],
                                [EDUCATIONALSOURCEDATE],
                                [COMMENT],
                                [EDUCATIONALHISTORYLEVELCODEID],
                                [EDUCATIONALHISTORYREASONCODEID],
                                [ADDEDBYID],
                                [CHANGEDBYID],
                                [DATEADDED],
                                [DATECHANGED]
                            )
                            values
                            (
                                @EDUCATIONALHISTORYID,
                                @ID,
                                @EDUCATIONALHISTORY_EDUCATIONALINSTITUTIONID,
                                @EDUCATIONALHISTORY_ISPRIMARYRECORD,
                                @EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE,
                                @EDUCATIONALHISTORY_EDUCATIONALDEGREECODEID,
                                @EDUCATIONALHISTORY_STARTDATE,
                                @EDUCATIONALHISTORY_DATEGRADUATED,
                                @EDUCATIONALHISTORY_DATELEFT,
                                @EDUCATIONALHISTORY_CLASSOF,
                                @EDUCATIONALHISTORY_EDUCATIONALPROGRAMCODEID,
                                @EDUCATIONALHISTORY_EDUCATIONALAWARDCODEID,
                                @EDUCATIONALHISTORY_PREFERREDCLASSYEAR,
                                @EDUCATIONALHISTORY_EDUCATIONALSOURCECODEID,
                                @EDUCATIONALHISTORY_EDUCATIONALSOURCEDATE,
                                @EDUCATIONALHISTORY_COMMENT,
                                @EDUCATIONALHISTORY_LEVELCODEID,
                                @EDUCATIONALHISTORY_REASONCODEID,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE
                            );

                            exec dbo.USP_EDUCATIONALHISTORY_GETUNAFFILIATEDADDITIONALINFORMATION_ADDFROMXML @EDUCATIONALHISTORYID, @UNAFFILIATEDADDITIONALINFORMATION, @CHANGEAGENTID;
                        end
                    end

                    exec dbo.USP_CONSTITUENT_GETEDUCATIONALINVOLVEMENT_ADDFROMXML @ID, @EDUCATIONALINVOLVEMENT, @CHANGEAGENTID;

                    if (@SPOUSEID is null) and (coalesce(@SPOUSE_KEYNAME,'') <> '') begin
                        set @SPOUSEID = newid();

                        begin try
                            insert into dbo.[CONSTITUENT]
                            (
                                [ID],
                                [KEYNAME],
                                [FIRSTNAME],
                                [MIDDLENAME],
                                [MAIDENNAME],
                                [NICKNAME],
                                [TITLECODEID],
                                [SUFFIXCODEID],
                                [TITLE2CODEID],
                                [SUFFIX2CODEID],
                                [GENDERCODE],
                                [BIRTHDATE],
                                [ISCONSTITUENT],
                                [ADDEDBYID],
                                [CHANGEDBYID],
                                [DATEADDED],
                                [DATECHANGED],
                                [GENDERCODEID]
                            )
                            values
                            (
                                @SPOUSEID,
                                @SPOUSE_KEYNAME,
                                @SPOUSE_FIRSTNAME,
                                @SPOUSE_MIDDLENAME,
                                @SPOUSE_MAIDENNAME,
                                @SPOUSE_NICKNAME,
                                @SPOUSE_TITLECODEID,
                                @SPOUSE_SUFFIXCODEID,
                                @SPOUSE_TITLE2CODEID,
                                @SPOUSE_SUFFIX2CODEID,
                                @SPOUSE_GENDERCODE,
                                @SPOUSE_BIRTHDATE,
                                dbo.UFN_CONSTITUENCYCRITERIASPOUSE_SPOUSEISCONSTITUENT(),
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE,
                        @SPOUSE_GENDERCODEID
                            );

                            insert into dbo.CONSTITUENTORIGINATION
                            (
                                ID,
                                INFOSOURCECODEID,
                                ADDEDBYID,
                                CHANGEDBYID,
                                DATEADDED,
                                DATECHANGED
                            )
                            values
                            (
                                @SPOUSEID,
                                @ADDRESS_INFOSOURCECODEID,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE
                            );
                        end try
                        begin catch
                            set @InfoMsg='BBERR_ORIGINAL_ERROR:50003';
                            raiserror (@InfoMsg,1,11);

                            select
                                @ErrorMessage = ERROR_MESSAGE(),
                                @ErrorSeverity = ERROR_SEVERITY(),
                                @ErrorState = ERROR_STATE();

                            raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
                            return 1;
                        end catch

                    -- copy siteid to new spouse record

                    if @CONSTITUENT_SITEID is not null
                    begin
                        insert into dbo.[CONSTITUENTSITE] (
                            [SITEID],
                            [CONSTITUENTID],
                            [ADDEDBYID],
                            [CHANGEDBYID],
                            [DATEADDED],
                            [DATECHANGED]
                            )
                        values (
                            @CONSTITUENT_SITEID,
                            @SPOUSEID,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                            )
                    end
                    else
                    begin 
                        if dbo.UFN_SITEREQUIREDFORUSER(@BATCHOWNERID) = 1
                            insert into dbo.CONSTITUENTSITE
                            (
                                DATEADDED,
                                DATECHANGED,
                                ADDEDBYID,
                                CHANGEDBYID,
                                CONSTITUENTID,
                                SITEID
                            )
                            select 
                                @CURRENTDATE,
                                @CURRENTDATE,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @SPOUSEID,
                                SITEID
                            from 
                                dbo.UFN_SITESFORUSER(@BATCHOWNERID)
                    end

            --Bug 4965 - AdamBu - 9/23/08

                        -- If security on the new constituent is set and a new spouse or household

                        --  is created for that constituent, set them with the same security.

                        if @CONSTIT_SECURITY_ATTRIBUTEID is not null 
                            insert into dbo.[CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT]
                            (
                                [CONSTIT_SECURITY_ATTRIBUTEID],
                                [CONSTITUENTID],
                                [ADDEDBYID],
                                [CHANGEDBYID],
                                [DATEADDED],
                                [DATECHANGED]
                            )
                            values
                            (
                                @CONSTIT_SECURITY_ATTRIBUTEID,
                                @SPOUSEID,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE
                            );

                        /*Start name format defaults*/
                        INSERT INTO [dbo].[NAMEFORMAT]
                            ([CONSTITUENTID]
                            ,[NAMEFORMATTYPECODEID]
                            ,[NAMEFORMATFUNCTIONID]
                            ,[ADDEDBYID]
                            ,[CHANGEDBYID]
                            ,[DATEADDED]
                            ,[DATECHANGED]
                            ,[PRIMARYADDRESSEE]
                            ,[PRIMARYSALUTATION])
                        SELECT
                            @SPOUSEID
                            ,NFD.NAMEFORMATTYPECODEID
                            ,NFD.NAMEFORMATFUNCTIONID
                            ,@CHANGEAGENTID
                            ,@CHANGEAGENTID
                            ,@CURRENTDATE
                            ,@CURRENTDATE
                            ,NFD.PRIMARYADDRESSEE
                            ,NFD.PRIMARYSALUTATION
                        FROM dbo.NAMEFORMATDEFAULT as NFD
                        WHERE (NFD.APPLYTOCODE = 0)
                        /*End name format defaults*/

                        if (@SPOUSE_EMAILADDRESS_EMAILADDRESSTYPECODEID is not null) or (coalesce(@SPOUSE_EMAILADDRESS_EMAILADDRESS,'') <> '') begin
                            begin try
                                insert into dbo.[EMAILADDRESS]
                                (
                                    [CONSTITUENTID],
                                    [EMAILADDRESSTYPECODEID],
                                    [EMAILADDRESS],
                                    [ISPRIMARY],
                                    [INFOSOURCECODEID],
                                    [ADDEDBYID],
                                    [CHANGEDBYID],
                                    [DATEADDED],
                                    [DATECHANGED],
                  [STARTDATE]
                                )
                                values
                                (
                                    @SPOUSEID,
                                    @SPOUSE_EMAILADDRESS_EMAILADDRESSTYPECODEID,
                                    @SPOUSE_EMAILADDRESS_EMAILADDRESS,
                                    1,
                                    @ADDRESS_INFOSOURCECODEID,
                                    @CHANGEAGENTID,
                                    @CHANGEAGENTID,
                                    @CURRENTDATE,
                                    @CURRENTDATE,
                  @SPOUSE_EMAILADDRESS_STARTDATE
                                );
                            end try
                            begin catch
                                set @InfoMsg='BBERR_ORIGINAL_ERROR:50004';
                                raiserror (@InfoMsg,1,11);

                                select
                                    @ErrorMessage = ERROR_MESSAGE(),
                                    @ErrorSeverity = ERROR_SEVERITY(),
                                    @ErrorState = ERROR_STATE();

                                raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
                                return 1;
                            end catch
                        end
                    end

                    if (@SPOUSEID is not null) begin
                        begin try
                            set @SETID = newid();

                            insert into dbo.RELATIONSHIPSET
                            (
                                ID,
                                ADDEDBYID,
                                CHANGEDBYID,
                                DATEADDED,
                                DATECHANGED
                            )
                            values
                            (
                                @SETID,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE
                            );


                            insert into dbo.RELATIONSHIP
                            (
                                [RELATIONSHIPCONSTITUENTID],
                                [RECIPROCALCONSTITUENTID],
                                [RELATIONSHIPTYPECODEID],
                                [RECIPROCALTYPECODEID],
                                [ISSPOUSE],
                                [RELATIONSHIPSETID],
                                [ADDEDBYID],
                                [CHANGEDBYID],
                                [DATEADDED],
                                [DATECHANGED],
                                [STARTDATE]
                            )
                            values
                            (
                                @ID,
                                @SPOUSEID,
                                @SPOUSE_RELATIONSHIPTYPECODEID,
                                @SPOUSE_RECIPROCALTYPECODEID,
                                1,
                                @SETID,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE,
                                @SPOUSE_STARTDATE
                            );

                            if (not exists(select ID from dbo.DISABLEDWEALTHUPDATES where ID = @SPOUSEID))
                                insert into dbo.DISABLEDWEALTHUPDATES
                                (
                                    ID,
                                    ADDEDBYID,
                                    CHANGEDBYID,
                                    DATEADDED,
                                    DATECHANGED
                                )
                                values
                                (
                                    @SPOUSEID,
                                    @CHANGEAGENTID,
                                    @CHANGEAGENTID,
                                    @CURRENTDATE,
                                    @CURRENTDATE
                                );

                            exec dbo.USP_RELATIONSHIPCONFIGURATION_CONFIGURE @ID, @SPOUSEID, @SPOUSE_RELATIONSHIPTYPECODEID, @SPOUSE_RECIPROCALTYPECODEID, @CHANGEAGENTID, @CURRENTDATE
                            exec dbo.USP_MARRIAGEOPTIONS_APPLYRULES @ID, @SPOUSEID, @CHANGEAGENTID, @CURRENTDATE;

                        end try
                        begin catch
                            set @InfoMsg='BBERR_ORIGINAL_ERROR:50005';
                            raiserror (@InfoMsg,1,11);

                            select
                                @ErrorMessage = ERROR_MESSAGE(),
                                @ErrorSeverity = ERROR_SEVERITY(),
                                @ErrorState = ERROR_STATE();

                            raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
                            return 1;
                        end catch

                        exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @SPOUSEID, @ID, @SPOUSE_STARTDATE, null,
                          @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS, @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR,
                          @SPOUSE_PRIMARYRECOGNITIONTYPECODEID, @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
                          @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR, @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID,
                          @CHANGEAGENTID, 0;

                        declare @SPOUSE_COPY_ISPRIMARY bit;
                        declare @SPOUSE_COPY_EXISTINGID uniqueidentifier;
                        declare @SPOUSE_COPY_SEQUENCE int;

                        -- Only copy the address to the spouse if it is not an "empty" address

                        if @SPOUSE_COPY_ADDRESS = 1
                        and @ADDRESSINCLUDED = 1
                        and not
                        (
                            @ADDRESS_ADDRESSTYPECODEID is null
                            and coalesce(@ADDRESS_DONOTMAIL, 0) = 0
                            and @ADDRESS_DONOTMAILREASONCODEID is null
                            and @ADDRESS_STATEID is null
                            and @ADDRESS_COUNTRYID is null
                            and coalesce(@ADDRESS_ADDRESSBLOCK, '') = ''
                            and coalesce(@ADDRESS_CITY, '') = ''
                            and coalesce(@ADDRESS_POSTCODE, '') = ''
                            and coalesce(@ADDRESS_CART, '') = ''
                            and coalesce(@ADDRESS_DPC, '') = ''
                            and coalesce(@ADDRESS_LOT, '') = ''
                            and coalesce(@ADDRESS_STARTDATE, '0000') = '0000'
                            and coalesce(@ADDRESS_ENDDATE, '0000') = '0000'
                            and coalesce(@ADDRESS_HISTORICALSTARTDATE, '') = ''
                        )
                            begin try
                                if exists (select ID from dbo.ADDRESS where CONSTITUENTID = @SPOUSEID and ISPRIMARY = 1)
                                    set @SPOUSE_COPY_ISPRIMARY = 0;
                                else
                                    set @SPOUSE_COPY_ISPRIMARY = 1;

                                select
                                    @SPOUSE_COPY_SEQUENCE = coalesce(max(SEQUENCE), 0) + 1
                                from
                                    dbo.ADDRESS
                                where
                                    CONSTITUENTID = @SPOUSEID;

                                set @SPOUSE_COPY_EXISTINGID = null;

                                declare @EMPTY_GUID uniqueidentifier = '00000000-0000-0000-0000-000000000000'
                                select
                                    @SPOUSE_COPY_EXISTINGID = ID 
                                from
                                    dbo.ADDRESS
                                where
                                    CONSTITUENTID = @SPOUSEID and
                                    coalesce(ADDRESSTYPECODEID, @EMPTY_GUID) = coalesce(@ADDRESS_ADDRESSTYPECODEID, @EMPTY_GUID) and
                                    COUNTRYID = @ADDRESS_COUNTRYID and
                                    coalesce(STATEID, @EMPTY_GUID) = coalesce(@ADDRESS_STATEID, @EMPTY_GUID) and
                                    ADDRESSBLOCK = @ADDRESS_ADDRESSBLOCK and 
                                    CITY = @ADDRESS_CITY and
                                    POSTCODE = @ADDRESS_POSTCODE;

                                if @SPOUSE_COPY_EXISTINGID is null
                                    begin
                                        declare @SPOUSE_ADDRESSID uniqueidentifier;
                                        set @SPOUSE_ADDRESSID = newid();

                                        insert into dbo.[ADDRESS]
                                        (
                                            [ID],
                                            [CONSTITUENTID],
                                            [ADDRESSTYPECODEID],
                                            [ISPRIMARY],
                                            [DONOTMAIL],
                                            [DONOTMAILREASONCODEID],
                                            [COUNTRYID],
                                            [STATEID],
                                            [ADDRESSBLOCK],
                                            [CITY],
                                            [POSTCODE],
                                            [CART],
                                            [DPC],
                                            [LOT],
                                            [STARTDATE],
                                            [ENDDATE],
                                            [HISTORICALSTARTDATE],
                                            [SEQUENCE],
                                            [ADDEDBYID],
                                            [CHANGEDBYID],
                                            [DATEADDED],
                                            [DATECHANGED]
                                        )
                                        values
                                        (
                                            @SPOUSE_ADDRESSID,
                                            @SPOUSEID,
                                            @ADDRESS_ADDRESSTYPECODEID,
                                            @SPOUSE_COPY_ISPRIMARY,
                                            @ADDRESS_DONOTMAIL,
                                            @ADDRESS_DONOTMAILREASONCODEID,
                                            @ADDRESS_COUNTRYID,
                                            @ADDRESS_STATEID,
                                            @ADDRESS_ADDRESSBLOCK,
                                            @ADDRESS_CITY,
                                            @ADDRESS_POSTCODE,
                                            @ADDRESS_CART,
                                            @ADDRESS_DPC,
                                            @ADDRESS_LOT,
                                            @ADDRESS_STARTDATE,
                                            @ADDRESS_ENDDATE,
                                            @ADDRESS_HISTORICALSTARTDATE,
                                            @SPOUSE_COPY_SEQUENCE,
                                            @CHANGEAGENTID,
                                            @CHANGEAGENTID,
                                            @CURRENTDATE,
                                            @CURRENTDATE
                                        );

                                        insert into dbo.ADDRESSVALIDATIONUPDATE
                                        (
                                            [ID],
                                            [INFOSOURCECODEID],
                                            [ADDEDBYID],
                                            [CHANGEDBYID],
                                            [DATEADDED],
                                            [DATECHANGED]
                                        )
                                        values
                                        (
                                            @SPOUSE_ADDRESSID,
                                            @ADDRESS_INFOSOURCECODEID,
                                            @CHANGEAGENTID,
                                            @CHANGEAGENTID,
                                            @CURRENTDATE,
                                            @CURRENTDATE
                                        );
                                    end
                                else
                                    if @SPOUSE_COPY_ISPRIMARY = 1
                                        update dbo.ADDRESS
                                        set
                                            ISPRIMARY = 1,
                                            CHANGEDBYID = @CHANGEAGENTID,
                                            DATECHANGED = @CURRENTDATE
                                        where
                                            ID = @SPOUSE_COPY_EXISTINGID
                                            and ISPRIMARY <> 1;
                            end try
                            begin catch
                                set @InfoMsg='BBERR_ORIGINAL_ERROR:50012';
                                raiserror (@InfoMsg,1,11);

                                select
                                    @ErrorMessage = ERROR_MESSAGE(),
                                    @ErrorSeverity = ERROR_SEVERITY(),
                                    @ErrorState = ERROR_STATE();

                                raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
                                return 1;
                            end catch

                        if @SPOUSE_COPY_PHONENUMBER = 1
                            begin try
                                set @SPOUSE_COPY_EXISTINGID = null;

                                declare @SPOUSE_COPY_PHONEINCLUDED bit;
                                set @SPOUSE_COPY_PHONEINCLUDED = 0;

                                declare @SPOUSE_COPY_PHONETYPECODEID uniqueidentifier;
                                declare @SPOUSE_COPY_NUMBER nvarchar(100);
                                declare @SPOUSE_COPY_STARTTIME dbo.UDT_HOURMINUTE;
                                declare @SPOUSE_COPY_ENDTIME dbo.UDT_HOURMINUTE;
                                declare @SPOUSE_COPY_STARTDATE date;
                                declare @SPOUSE_COPY_COUNTRYID uniqueidentifier;
                                declare @SPOUSE_COPY_SEASONALSTART dbo.UDT_MONTHDAY;
                                declare @SPOUSE_COPY_SEASONALEND dbo.UDT_MONTHDAY;

                                select
                                    @SPOUSE_COPY_PHONETYPECODEID = PHONETYPECODEID,
                                    @SPOUSE_COPY_NUMBER = NUMBER,
                                    @SPOUSE_COPY_PHONEINCLUDED = ISPRIMARY,
                                    @SPOUSE_COPY_STARTTIME = STARTTIME,
                                    @SPOUSE_COPY_ENDTIME = ENDTIME,
                                    @SPOUSE_COPY_STARTDATE = STARTDATE,
                                    @SPOUSE_COPY_COUNTRYID = COUNTRYID,
                                    @SPOUSE_COPY_SEASONALSTART = SEASONALSTARTDATE,
                                    @SPOUSE_COPY_SEASONALEND = SEASONALENDDATE
                                from
                                    dbo.PHONE
                                where
                                    CONSTITUENTID = @ID and
                                    ISPRIMARY = 1;

                                if @SPOUSE_COPY_PHONEINCLUDED = 1
                                begin
                                    if exists (select ID from dbo.PHONE where CONSTITUENTID = @SPOUSEID and ISPRIMARY = 1)
                                        set @SPOUSE_COPY_ISPRIMARY = 0;
                              else
                                        set @SPOUSE_COPY_ISPRIMARY = 1;

                                    select
                                        @SPOUSE_COPY_SEQUENCE = coalesce(max(SEQUENCE), 0) + 1
                                    from
                                        dbo.PHONE
                                    where
                                        CONSTITUENTID = @SPOUSEID;

                                    select
                                        @SPOUSE_COPY_EXISTINGID = ID
                                    from
                                        dbo.PHONE
                                    where
                                        CONSTITUENTID = @SPOUSEID and
                                        PHONETYPECODEID = @SPOUSE_COPY_PHONETYPECODEID and
                                        NUMBER = @SPOUSE_COPY_NUMBER;

                                    if @SPOUSE_COPY_EXISTINGID is null
                                        insert into dbo.PHONE
                                        (
                                            CONSTITUENTID,
                                            PHONETYPECODEID,
                                            NUMBER,
                                            ISPRIMARY,
                                            INFOSOURCECODEID,
                                            SEQUENCE,
                                            STARTTIME,
                                            ENDTIME,
                                            STARTDATE,
                                            COUNTRYID,
                                            SEASONALSTARTDATE,
                                            SEASONALENDDATE,
                                            ADDEDBYID,
                                            CHANGEDBYID,
                                            DATEADDED,
                                            DATECHANGED
                                        )
                                        values
                                        (
                                            @SPOUSEID,
                                            @SPOUSE_COPY_PHONETYPECODEID,
                                            @SPOUSE_COPY_NUMBER,
                                            @SPOUSE_COPY_ISPRIMARY,
                                            @ADDRESS_INFOSOURCECODEID,
                                            @SPOUSE_COPY_SEQUENCE,
                                            @SPOUSE_COPY_STARTTIME,
                                            @SPOUSE_COPY_ENDTIME,
                                            @SPOUSE_COPY_STARTDATE,
                                            @SPOUSE_COPY_COUNTRYID,
                                            @SPOUSE_COPY_SEASONALSTART,
                                            @SPOUSE_COPY_SEASONALEND,
                                            @CHANGEAGENTID,
                                            @CHANGEAGENTID,
                                            @CURRENTDATE,
                                            @CURRENTDATE
                                        );
                                end
                            end try
                            begin catch
                                set @InfoMsg='BBERR_ORIGINAL_ERROR:50013';
                                raiserror (@InfoMsg,1,11);

                                select
                                    @ErrorMessage = ERROR_MESSAGE(),
                                    @ErrorSeverity = ERROR_SEVERITY(),
                                    @ErrorState = ERROR_STATE();

                                raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
                                return 1;
                            end catch

                        if @SPOUSE_COPY_EMAILADDRESS = 1
                        and
                        (
                            (@EMAILADDRESS_EMAILADDRESSTYPECODEID is not null)
                            or (coalesce(@EMAILADDRESS_EMAILADDRESS, '') <> '')
                        )
                            begin try
                                set @SPOUSE_COPY_EXISTINGID = null;

                                if exists (select ID from dbo.EMAILADDRESS where CONSTITUENTID = @SPOUSEID and ISPRIMARY = 1)
                                    set @SPOUSE_COPY_ISPRIMARY = 0;
                                else
                                    set @SPOUSE_COPY_ISPRIMARY = 1;

                                select
                                    @SPOUSE_COPY_SEQUENCE = coalesce(max(SEQUENCE), 0) + 1
                                from
                                    dbo.EMAILADDRESS
                                where
                                    CONSTITUENTID = @SPOUSEID;

                                select
                                    @SPOUSE_COPY_EXISTINGID = ID
                                from
                                    dbo.EMAILADDRESS
                                where
                                    CONSTITUENTID = @SPOUSEID and
                                    EMAILADDRESSTYPECODEID = @EMAILADDRESS_EMAILADDRESSTYPECODEID and
                                    EMAILADDRESS = @EMAILADDRESS_EMAILADDRESS;

                                if @SPOUSE_COPY_EXISTINGID is null
                                    insert into dbo.EMAILADDRESS
                                    (
                                        CONSTITUENTID,
                                        EMAILADDRESSTYPECODEID,
                                        EMAILADDRESS,
                                        ISPRIMARY,
                                        INFOSOURCECODEID,
                                        SEQUENCE,
                                        ADDEDBYID,
                                        CHANGEDBYID,
                                        DATEADDED,
                                        DATECHANGED,
                    STARTDATE
                                    )
                                    values
                                    (
                                        @SPOUSEID,
                                        @EMAILADDRESS_EMAILADDRESSTYPECODEID,
                                        @EMAILADDRESS_EMAILADDRESS,
                                        @SPOUSE_COPY_ISPRIMARY,
                                        @ADDRESS_INFOSOURCECODEID,
                                        @SPOUSE_COPY_SEQUENCE,
                                        @CHANGEAGENTID,
                                        @CHANGEAGENTID,
                                        @CURRENTDATE,
                                        @CURRENTDATE,
                    @EMAILADDRESS_STARTDATE
                                    );
                            end try
                            begin catch
                                set @InfoMsg='BBERR_ORIGINAL_ERROR:50014';
                                raiserror (@InfoMsg,1,11);

                                select
                                    @ErrorMessage = ERROR_MESSAGE(),
                                    @ErrorSeverity = ERROR_SEVERITY(),
                                    @ErrorState = ERROR_STATE();

                                raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
                        return 1;
                            end catch

                            declare @CURRENTDATEEARLIESTTIME date;
                            set @CURRENTDATEEARLIESTTIME = @CURRENTDATE;

                            -- get the household of the spouse

                            declare @SPOUSEHOUSEHOLDID uniqueidentifier;
                            select 
                                @SPOUSEHOUSEHOLDID = GM.GROUPID
                            from 
                                dbo.GROUPMEMBER GM
                            inner join 
                                dbo.GROUPDATA GD on GD.ID = GM.GROUPID
                            left join 
                                dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                            where
                                GD.GROUPTYPECODE = 0
                            and
                                GM.MEMBERID = @SPOUSEID 
                            and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
                                or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME)) 
                                or (GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME and GMDR.DATETO > @CURRENTDATEEARLIESTTIME));

                            -- if the spouse is not in a household, create a new one and add them both

                            if (@SPOUSEHOUSEHOLDID is null) begin
                                declare @NAMEFORMATFUNCTIONID uniqueidentifier;
                                select top(1) @NAMEFORMATFUNCTIONID = NAMEFORMATFUNCTIONID from dbo.HOUSEHOLDINFO order by DATEADDED;

                                declare @HOUSEHOLDNAME nvarchar(154);
                                select @HOUSEHOLDNAME = dbo.UFN_NAMEFORMAT_FROMID(@NAMEFORMATFUNCTIONID, @ID);

                                declare @HOUSEHOLDID uniqueidentifier;
                                set @HOUSEHOLDID = newid();

                                -- create the household constituent/group data records

                                insert into dbo.CONSTITUENT
                                (
                                    [ID],
                                    [KEYNAME],
                                    [ISORGANIZATION],
                                    [ISGROUP],
                                    [ADDEDBYID],
                                    [CHANGEDBYID],
                                    [DATEADDED],
                                    [DATECHANGED]
                                )
                                values
                                (
                                    @HOUSEHOLDID,
                                    @HOUSEHOLDNAME,
                                    0, -- ISORGANIZATION

                                    1, -- ISGROUP

                                    @CHANGEAGENTID,
                                    @CHANGEAGENTID,
                                    @CURRENTDATE,
                                    @CURRENTDATE
                                );

                                insert into dbo.GROUPDATA
                                (
                                    ID,
                                    GROUPTYPECODE,
                                    STARTDATE,
                                    GROUPTYPEID,
                                    ADDEDBYID,
                                    CHANGEDBYID,
                                    DATEADDED,
                                    DATECHANGED
                                )
                                values
                                (
                                    @HOUSEHOLDID,
                                    0,
                                    @CURRENTDATEEARLIESTTIME,
                                    null,
                                    @CHANGEAGENTID,
                                    @CHANGEAGENTID,
                                    @CURRENTDATE,
                                    @CURRENTDATE
                                );

                                insert into dbo.CONSTITUENTORIGINATION
                                (
                                    ID,
                                    INFOSOURCECODEID,
                                    ADDEDBYID,
                                    CHANGEDBYID,
                                    DATEADDED,
                                    DATECHANGED
                                )
                                values
                                (
                                    @HOUSEHOLDID,
                                    @ADDRESS_INFOSOURCECODEID,
                                    @CHANGEAGENTID,
                                    @CHANGEAGENTID,
                                    @CURRENTDATE,
                                    @CURRENTDATE
                                );

                          -- copy address to new household

                          if @ADDRESSINCLUDED = 1
                          begin
                              declare @HOUSEHOLDADDRESSID uniqueidentifier;

                              set @HOUSEHOLDADDRESSID = newID();

                              insert into dbo.[ADDRESS] (
                                  [ID],
                                  [CONSTITUENTID],
                                  [ADDRESSTYPECODEID],
                                  [ISPRIMARY],
                                  [DONOTMAIL],
                                  [DONOTMAILREASONCODEID],
                                  [COUNTRYID],
                                  [STATEID],
                                  [ADDRESSBLOCK],
                                  [CITY],
                                  [POSTCODE],
                                  [CART],
                                  [DPC],
                                  [LOT],
                                  [STARTDATE],
                                  [ENDDATE],
                                  [HISTORICALSTARTDATE],
                                  [ADDEDBYID],
                                  [CHANGEDBYID],
                                  [DATEADDED],
                                  [DATECHANGED]
                                  )
                              values (
                                  @HOUSEHOLDADDRESSID,
                                  @HOUSEHOLDID,
                                  @ADDRESS_ADDRESSTYPECODEID,
                                  1,
                                  @ADDRESS_DONOTMAIL,
                                  @ADDRESS_DONOTMAILREASONCODEID,
                                  @ADDRESS_COUNTRYID,
                                  @ADDRESS_STATEID,
                                  @ADDRESS_ADDRESSBLOCK,
                                  @ADDRESS_CITY,
                                  @ADDRESS_POSTCODE,
                                  @ADDRESS_CART,
                                  @ADDRESS_DPC,
                                  @ADDRESS_LOT,
                                  @ADDRESS_STARTDATE,
                                  @ADDRESS_ENDDATE,
                                  @ADDRESS_HISTORICALSTARTDATE,
                                  @CHANGEAGENTID,
                                  @CHANGEAGENTID,
                                  @CURRENTDATE,
                                  @CURRENTDATE
                                  );

                              insert into dbo.ADDRESSVALIDATIONUPDATE (
                                  [ID],
                                  [COUNTYCODEID],
                                  [CONGRESSIONALDISTRICTCODEID],
                                  [STATEHOUSEDISTRICTCODEID],
                                  [STATESENATEDISTRICTCODEID],
                                  [LOCALPRECINCTCODEID],
                                  [INFOSOURCECODEID],
                                  [REGIONCODEID],
                                  [LASTVALIDATIONATTEMPTDATE],
                                  [VALIDATIONMESSAGE],
                                  [OMITFROMVALIDATION],
                                  [CERTIFICATIONDATA],
                                  [ADDEDBYID],
                                  [CHANGEDBYID],
                                  [DATEADDED],
                                  [DATECHANGED]
                                  )
                              values (
                                  @HOUSEHOLDADDRESSID,
                                  @ADDRESS_COUNTYCODEID,
                                  @ADDRESS_CONGRESSIONALDISTRICTCODEID,
                                  @ADDRESS_STATEHOUSEDISTRICTCODEID,
                                  @ADDRESS_STATESENATEDISTRICTCODEID,
                                  @ADDRESS_LOCALPRECINCTCODEID,
                                  @ADDRESS_INFOSOURCECODEID,
                                  @ADDRESS_REGIONCODEID,
                                  @ADDRESS_LASTVALIDATIONATTEMPTDATE,
                                  @ADDRESS_VALIDATIONMESSAGE,
                                  @ADDRESS_OMITFROMVALIDATION,
                                  @ADDRESS_CERTIFICATIONDATA,
                                  @CHANGEAGENTID,
                                  @CHANGEAGENTID,
                                  @CURRENTDATE,
                                  @CURRENTDATE
                                  );
                          end

                          -- copy phones to new household

                          if @PHONES is not null
                          begin
                              insert into dbo.[PHONE] (
                                  [CONSTITUENTID],
                                  [COUNTRYID],
                                  [ENDTIME],
                                  [ID],
                                  [ISPRIMARY],
                                  [NUMBER],
                                  [PHONETYPECODEID],
                                  [SEASONALENDDATE],
                                  [SEASONALSTARTDATE],
                                  [SEQUENCE],
                                  [STARTDATE],
                                  [STARTTIME],
                                  ADDEDBYID,
                                  CHANGEDBYID,
                                  DATEADDED,
                                  DATECHANGED
                                  )
                              select @HOUSEHOLDID,
                                  [COUNTRYID],
                                  [ENDTIME],
                                  newid(),
                                  [ISPRIMARY],
                                  [NUMBER],
                                  [PHONETYPECODEID],
                                  [SEASONALENDDATE],
                                  [SEASONALSTARTDATE],
                                  [SEQUENCE],
                                  [STARTDATE],
                                  [STARTTIME],
                                  ADDEDBYID,
                                  CHANGEDBYID,
                                  DATEADDED,
                                  DATECHANGED
                              from dbo.[PHONE]
                              where CONSTITUENTID = @ID

                              if (@ADDRESS_INFOSOURCECODEID is not null)
                                  update dbo.[PHONE]
                                  set [INFOSOURCECODEID] = @ADDRESS_INFOSOURCECODEID
                                  where [CONSTITUENTID] = @HOUSEHOLDID
                          end

                          -- copy email addresses to new household

                          if (@EMAILADDRESS_EMAILADDRESSTYPECODEID is not null) or (coalesce(@EMAILADDRESS_EMAILADDRESS, '') <> '') 
                              insert into dbo.[EMAILADDRESS] (
                                  [CONSTITUENTID],
                                  [EMAILADDRESSTYPECODEID],
                                  [EMAILADDRESS],
                                  [ISPRIMARY],
                                  [INFOSOURCECODEID],
                                  [INFOSOURCECOMMENTS],
                                  [ADDEDBYID],
                                  [CHANGEDBYID],
                                  [DATEADDED],
                                  [DATECHANGED],
                                  [STARTDATE]
                                  )
                              values (
                                  @HOUSEHOLDID,
                                  @EMAILADDRESS_EMAILADDRESSTYPECODEID,
                                  @EMAILADDRESS_EMAILADDRESS,
                                  1,
                                  @EMAILADDRESS_INFOSOURCECODEID,
                                  @EMAILADDRESS_INFOSOURCECOMMENTS,
                                  @CHANGEAGENTID,
                                  @CHANGEAGENTID,
                                  @CURRENTDATE,
                                  @CURRENTDATE,
                                  @EMAILADDRESS_STARTDATE
                         );

                        -- copy siteid to new household record

                        if @CONSTITUENT_SITEID is not null
                        begin
                            insert into dbo.[CONSTITUENTSITE] (
                                [SITEID],
                                [CONSTITUENTID],
                                [ADDEDBYID],
                                [CHANGEDBYID],
                                [DATEADDED],
                                [DATECHANGED]
                                )
                            values (
                                @CONSTITUENT_SITEID,
                                @HOUSEHOLDID,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE
                                )
                        end
                        else
                        begin
                            if dbo.UFN_SITEREQUIREDFORUSER(@BATCHOWNERID) = 1
                                insert into dbo.CONSTITUENTSITE
                                (
                                    DATEADDED,
                                    DATECHANGED,
                                    ADDEDBYID,
                                    CHANGEDBYID,
                                    CONSTITUENTID,
                                    SITEID
                                )
                                select 
                                    @CURRENTDATE,
                                    @CURRENTDATE,
                                    @CHANGEAGENTID,
                                    @CHANGEAGENTID,
                                    @HOUSEHOLDID,
                                    SITEID
                                from 
                                    dbo.UFN_SITESFORUSER(@BATCHOWNERID)
                        end

                                -- create the primary group member record

                                declare @PRIMARYGROUPMEMBERID uniqueidentifier;
                                exec dbo.USP_GROUPMEMBERADD @PRIMARYGROUPMEMBERID output, @CHANGEAGENTID, @HOUSEHOLDID, @ID;
                                update
                                    dbo.GROUPMEMBER
                                set
                                    ISPRIMARY = 1
                                where
                                    ID = @PRIMARYGROUPMEMBERID;

                                -- create the spouse record

                                declare @SPOUSEGROUPMEMBERID uniqueidentifier;
                                exec dbo.USP_GROUPMEMBERADD @SPOUSEGROUPMEMBERID output, @CHANGEAGENTID, @HOUSEHOLDID, @SPOUSEID;

                                --Bug 4965 - AdamBu - 9/23/08

                                -- If security on the new constituent is set and a new spouse or household

                                --  is created for that constituent, set them with the same security.

                                if @CONSTIT_SECURITY_ATTRIBUTEID is not null 
                                    insert into dbo.[CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT]
                                    (
                                        [CONSTIT_SECURITY_ATTRIBUTEID],
                                        [CONSTITUENTID],
                                        [ADDEDBYID],
                                        [CHANGEDBYID],
                                        [DATEADDED],
                                        [DATECHANGED]
                                    )
                                    values
                                    (
                                        @CONSTIT_SECURITY_ATTRIBUTEID,
                                        @HOUSEHOLDID,
                                        @CHANGEAGENTID,
                                        @CHANGEAGENTID,
                                        @CURRENTDATE,
                                        @CURRENTDATE
                                    );
                            end
                            -- if the spouse is in a household, add the new constituent to it

                            else begin
                                declare @SPOUSEHOUSEHOLDMEMBERID uniqueidentifier;
                                exec dbo.USP_GROUPMEMBERADD @SPOUSEHOUSEHOLDMEMBERID output, @CHANGEAGENTID, @SPOUSEHOUSEHOLDID, @ID;
                            end
                    end

                    if (@PRIMARYBUSINESSID is null) and (coalesce(@PRIMARYBUSINESS_KEYNAME,'') <> '') begin
                        set @PRIMARYBUSINESSID = newid();

                        begin try
                            insert into dbo.[CONSTITUENT]
                            (
                                [ID],
                                [ISORGANIZATION],
                                [KEYNAME],
                                [WEBADDRESS],
                                [ADDEDBYID],
                                [CHANGEDBYID],
                                [DATEADDED],
                                [DATECHANGED]
                            )
                            values
                            (
                                @PRIMARYBUSINESSID,
                                -1,
                                @PRIMARYBUSINESS_KEYNAME,
                                @PRIMARYBUSINESS_WEBADDRESS,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE
                            );

                            insert into dbo.CONSTITUENTORIGINATION
                            (
                                ID,
                                INFOSOURCECODEID,
                                ADDEDBYID,
                                CHANGEDBYID,
                                DATEADDED,
                                DATECHANGED
                            )
                            values
                            (
                                @PRIMARYBUSINESSID,
                                @PRIMARYBUSINESS_ADDRESS_INFOSOURCECODEID,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE
                            );
                        end try
                        begin catch
                            set @InfoMsg='BBERR_ORIGINAL_ERROR:50006';
                            raiserror (@InfoMsg,1,11);

                            select
                                @ErrorMessage = ERROR_MESSAGE(),
                                @ErrorSeverity = ERROR_SEVERITY(),
                                @ErrorState = ERROR_STATE();

                            raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
                            return 1;
                        end catch

                        if @CONSTITUENT_SITEID is not null 
                    begin
                        insert into dbo.[CONSTITUENTSITE]
                        (
                            [SITEID],
                            [CONSTITUENTID],
                            [ADDEDBYID],
                            [CHANGEDBYID],
                            [DATEADDED],
                            [DATECHANGED]
                        )
                        values
                        (
                            @CONSTITUENT_SITEID,
                            @PRIMARYBUSINESSID,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                        )
                    end
                    else
                    begin
                        if dbo.UFN_SITEREQUIREDFORUSER(@BATCHOWNERID) = 1
                            insert into dbo.CONSTITUENTSITE
                            (
                                DATEADDED,
                                DATECHANGED,
                                ADDEDBYID,
                                CHANGEDBYID,
                                CONSTITUENTID,
                                SITEID
                            )
                            select 
                                @CURRENTDATE,
                                @CURRENTDATE,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @PRIMARYBUSINESSID,
                                SITEID
                            from 
                                dbo.UFN_SITESFORUSER(@BATCHOWNERID)
                    end

                        begin try
                            insert into dbo.[ORGANIZATIONDATA]
                            (
                                [ID],
                                [INDUSTRYCODEID],
                                [NUMEMPLOYEES],
                                [NUMSUBSIDIARIES],
                                [PARENTCORPID],
                                [ADDEDBYID],
                                [CHANGEDBYID],
                                [DATEADDED],
                                [DATECHANGED],
                                [ISPRIMARY]
                            )
                            values
                            (
                                @PRIMARYBUSINESSID,
                                @PRIMARYBUSINESS_INDUSTRYCODEID,
                                @PRIMARYBUSINESS_NUMEMPLOYEES,
                                @PRIMARYBUSINESS_NUMSUBSIDIARIES,
                                @PRIMARYBUSINESS_PARENTCORPID,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE,
                                coalesce(@PRIMARYBUSINESS_ISPRIMARYORGANIZATION,0)
                            );
                        end try
                        begin catch
                            set @InfoMsg='BBERR_ORIGINAL_ERROR:50007';
                            raiserror (@InfoMsg,1,11);

                            select
                                @ErrorMessage = ERROR_MESSAGE(),
                                @ErrorSeverity = ERROR_SEVERITY(),
                                @ErrorState = ERROR_STATE();

                            raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
                            return 1;
                        end catch

                        if (@PRIMARYBUSINESS_PHONE_PHONETYPECODEID is not null) or (coalesce(@PRIMARYBUSINESS_PHONE_NUMBER,'') <> '') begin
                            begin try
                                insert into dbo.[PHONE]
                                (
                                    [CONSTITUENTID],
                                    [PHONETYPECODEID],
                                    [NUMBER],
                                    [COUNTRYID],
                                    [ISPRIMARY],
                                    [INFOSOURCECODEID],
                                    [ADDEDBYID],
                                    [CHANGEDBYID],
                                    [DATEADDED],
                                    [DATECHANGED]
                                )
                                values
                                (
                                    @PRIMARYBUSINESSID,
                                    @PRIMARYBUSINESS_PHONE_PHONETYPECODEID,
                                    @PRIMARYBUSINESS_PHONE_NUMBER,
                                    @PRIMARYBUSINESS_PHONE_COUNTRYID,
                                    1,
                                    @PRIMARYBUSINESS_ADDRESS_INFOSOURCECODEID,
                                    @CHANGEAGENTID,
                                    @CHANGEAGENTID,
                                    @CURRENTDATE,
                                    @CURRENTDATE
                                );
                            end try
                            begin catch
                                set @InfoMsg='BBERR_ORIGINAL_ERROR:50008';
                                raiserror (@InfoMsg,1,11);

                                select
                                    @ErrorMessage = ERROR_MESSAGE(),
                                    @ErrorSeverity = ERROR_SEVERITY(),
                                    @ErrorState = ERROR_STATE();

                                raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
                                return 1;
                            end catch
                        end

                        if (@PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESSTYPECODEID is not null) or (coalesce(@PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESS,'') <> '') begin
                            begin try
                                insert into dbo.[EMAILADDRESS]
                                (
                                    [CONSTITUENTID],
                                    [EMAILADDRESSTYPECODEID],
     [EMAILADDRESS],
                                    [ISPRIMARY],
                                    [INFOSOURCECODEID],
                                    [ADDEDBYID],
                                    [CHANGEDBYID],
                                    [DATEADDED],
                                    [DATECHANGED],
                                    [STARTDATE]
                                )
                                values
                                (
                                    @PRIMARYBUSINESSID,
                                    @PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESSTYPECODEID,
                                    @PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESS,
                                    1,
                                    @PRIMARYBUSINESS_ADDRESS_INFOSOURCECODEID,
                                    @CHANGEAGENTID,
                                    @CHANGEAGENTID,
                                    @CURRENTDATE,
                                    @CURRENTDATE,
                                    @PRIMARYBUSINESS_EMAILADDRESS_STARTDATE
                                );
                            end try
                            begin catch
                                set @InfoMsg='BBERR_ORIGINAL_ERROR:50009';
                                raiserror (@InfoMsg,1,11);

                                select
                                    @ErrorMessage = ERROR_MESSAGE(),
                                    @ErrorSeverity = ERROR_SEVERITY(),
                                    @ErrorState = ERROR_STATE();

                                raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
                                return 1;
                            end catch
                        end

                        if @PRIMARYBUSINESS_ADDRESSINCLUDED = 1 begin
                            begin try
                                declare @PRIMARYBUSINESS_ADDRESSID uniqueidentifier;
                                set @PRIMARYBUSINESS_ADDRESSID = newid();

                                insert into dbo.[ADDRESS]
                                (
                                    [ID],
                                    [CONSTITUENTID],
                                    [ADDRESSTYPECODEID],
                                    [ISPRIMARY],
                                    [DONOTMAIL],
                                    [DONOTMAILREASONCODEID],
                                    [COUNTRYID],
                                    [STATEID],
                                    [ADDRESSBLOCK],
                                    [CITY],
                                    [POSTCODE],
                                    [CART],
                                    [DPC],
                                    [LOT],
                                    [ADDEDBYID],
                                    [CHANGEDBYID],
                                    [DATEADDED],
                                    [DATECHANGED]
                                )
                                values
                                (
                                    @PRIMARYBUSINESS_ADDRESSID,
                                    @PRIMARYBUSINESSID,
                                    @PRIMARYBUSINESS_ADDRESS_ADDRESSTYPECODEID,
                                    1,
                                    @PRIMARYBUSINESS_ADDRESS_DONOTMAIL,
                                    @PRIMARYBUSINESS_ADDRESS_DONOTMAILREASONCODEID,
                                    @PRIMARYBUSINESS_ADDRESS_COUNTRYID,
                                    @PRIMARYBUSINESS_ADDRESS_STATEID,
                                    @PRIMARYBUSINESS_ADDRESS_ADDRESSBLOCK,
                                    @PRIMARYBUSINESS_ADDRESS_CITY,
                   @PRIMARYBUSINESS_ADDRESS_POSTCODE,
                                    @PRIMARYBUSINESS_ADDRESS_CART,
                                    @PRIMARYBUSINESS_ADDRESS_DPC,
                                    @PRIMARYBUSINESS_ADDRESS_LOT,
                                    @CHANGEAGENTID,
                                    @CHANGEAGENTID,
                                    @CURRENTDATE,
                                    @CURRENTDATE
                                );

                                insert into dbo.ADDRESSVALIDATIONUPDATE
                                (
                                    [ID],
                                    [INFOSOURCECODEID],
                                    [ADDEDBYID],
                                    [CHANGEDBYID],
                                    [DATEADDED],
                                    [DATECHANGED]
                                )
                                values
                                (
                                    @PRIMARYBUSINESS_ADDRESSID,
                                    @PRIMARYBUSINESS_ADDRESS_INFOSOURCECODEID,
                                    @CHANGEAGENTID,
                                    @CHANGEAGENTID,
                                    @CURRENTDATE,
                                    @CURRENTDATE
                                );
                            end try
                            begin catch
                                set @InfoMsg='BBERR_ORIGINAL_ERROR:50010';
                                raiserror (@InfoMsg,1,11);

                                select
                                    @ErrorMessage = ERROR_MESSAGE(),
                                    @ErrorSeverity = ERROR_SEVERITY(),
                                    @ErrorState = ERROR_STATE();

                                raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
                                return 1;
                            end catch
                        end
                    end

                    if (@PRIMARYBUSINESSID is not null) begin
                        begin try
                            set @SETID = newid();

                            insert into dbo.RELATIONSHIPSET
                            (
                                ID,
                                ADDEDBYID,
                                CHANGEDBYID,
                                DATEADDED,
                                DATECHANGED
                            )
                            values
                            (
                                @SETID,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE
                            );

                            insert into dbo.RELATIONSHIP
                            (
                                [RELATIONSHIPCONSTITUENTID],
                                [RECIPROCALCONSTITUENTID],
                                [RELATIONSHIPTYPECODEID],
                                [RECIPROCALTYPECODEID],
                                [ISPRIMARYBUSINESS],
                                [RELATIONSHIPSETID],
                                [ADDEDBYID],
                                [CHANGEDBYID],
                                [DATEADDED],
                                [DATECHANGED],
                                [STARTDATE]
                            )
                            values
                            (
                                @ID,
                                @PRIMARYBUSINESSID,
                                @PRIMARYBUSINESS_RELATIONSHIPTYPECODEID,
                       @PRIMARYBUSINESS_RECIPROCALTYPECODEID,
                                1,
                                @SETID,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE,
                                @PRIMARYBUSINESS_STARTDATE
                            );

                            exec dbo.USP_RELATIONSHIPCONFIGURATION_CONFIGURE @ID, @PRIMARYBUSINESSID, @PRIMARYBUSINESS_RELATIONSHIPTYPECODEID, @PRIMARYBUSINESS_RECIPROCALTYPECODEID, @CHANGEAGENTID, @CURRENTDATE

                            if (@PRIMARYBUSINESS_JOBCATEGORYCODEID is not null) or 
                                (@PRIMARYBUSINESS_CAREERLEVELCODEID is not null) or
                                (len(@PRIMARYBUSINESS_JOBDIVISION) > 0) or
                                (len(@PRIMARYBUSINESS_JOBDEPARTMENT) > 0) or
                                (@PRIMARYBUSINESS_JOBSCHEDULECODEID is not null) or
                                (len(@PRIMARYBUSINESS_JOBTITLE) > 0)

                                insert into dbo.RELATIONSHIPJOBINFO
                                (
                                    RELATIONSHIPSETID,
                                    JOBTITLE,
                                    JOBCATEGORYCODEID,
                                    CAREERLEVELCODEID,
                                    JOBDIVISION,
                                    JOBDEPARTMENT,
                                    JOBSCHEDULECODEID,
                                    ADDEDBYID,
                                    CHANGEDBYID,
                                    DATEADDED,
                                    DATECHANGED,
                                    STARTDATE
                                )
                                values
                                (
                                    @SETID,
                                    @PRIMARYBUSINESS_JOBTITLE,
                                    @PRIMARYBUSINESS_JOBCATEGORYCODEID,
                                    @PRIMARYBUSINESS_CAREERLEVELCODEID,
                                    @PRIMARYBUSINESS_JOBDIVISION,
                                    @PRIMARYBUSINESS_JOBDEPARTMENT,
                                    @PRIMARYBUSINESS_JOBSCHEDULECODEID,
                                    @CHANGEAGENTID,
                                    @CHANGEAGENTID,
                                    @CURRENTDATE,
                                    @CURRENTDATE,
                                    @PRIMARYBUSINESS_STARTDATE
                                );
                        end try
                        begin catch
                            set @InfoMsg='BBERR_ORIGINAL_ERROR:50011';
                            raiserror (@InfoMsg,1,11);

                            select
                                @ErrorMessage = ERROR_MESSAGE(),
                                @ErrorSeverity = ERROR_SEVERITY(),
                                @ErrorState = ERROR_STATE();

                            raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
                            return 1;
                        end catch

                        exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @PRIMARYBUSINESSID, @ID, @PRIMARYBUSINESS_STARTDATE, null,
                          @PRIMARYBUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS, @PRIMARYBUSINESS_PRIMARYSOFTCREDITMATCHFACTOR,
                          @PRIMARYBUSINESS_PRIMARYRECOGNITIONTYPECODEID, @PRIMARYBUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
                          @PRIMARYBUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR, @PRIMARYBUSINESS_RECIPROCALRECOGNITIONTYPECODEID,
                          @CHANGEAGENTID;
                    end

              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_CONSTITUENT_GETCONSTITUENCIES_ADDFROMXML @ID, @USERDEFINEDCONSTITUENCY, @CHANGEAGENTID;

                    declare @SYSTEMDEFINEDCONSTITUENCY xml;

                    set @SYSTEMDEFINEDCONSTITUENCY =
                        (
                            select
                                CONSTITUENCY.c.query('.')
                            from
                                @CONSTITUENCY.nodes('/CONSTITUENCY/ITEM') CONSTITUENCY(c)
                            where
                                CONSTITUENCY.c.value('(CONSTITUENCYCODEID)[1]', 'uniqueidentifier') = 'F828E957-5F5E-479A-8F23-2FFD6C7C68FF'
                            for xml raw(''),type,elements,root('CONSTITUENCY'),BINARY BASE64
                        );
                    exec dbo.USP_CONSTITUENT_GETCONSTITUENCIES_BOARDMEMBER_ADDFROMXML @ID, @SYSTEMDEFINEDCONSTITUENCY, @CHANGEAGENTID;

                    set @SYSTEMDEFINEDCONSTITUENCY =
                        (
                            select
                                CONSTITUENCY.c.query('.')
                            from
                                @CONSTITUENCY.nodes('/CONSTITUENCY/ITEM') CONSTITUENCY(c)
                            where
                                CONSTITUENCY.c.value('(CONSTITUENCYCODEID)[1]', 'uniqueidentifier') = '6093915E-ADE9-42BE-88AE-304731754467'
                            for xml raw(''),type,elements,root('CONSTITUENCY'),BINARY BASE64
                        );
                    exec dbo.USP_CONSTITUENT_GETCONSTITUENCIES_STAFF_ADDFROMXML @ID, @SYSTEMDEFINEDCONSTITUENCY, @CHANGEAGENTID;

                    set @SYSTEMDEFINEDCONSTITUENCY =
                        (
                            select
                                CONSTITUENCY.c.query('.')
                            from
                                @CONSTITUENCY.nodes('/CONSTITUENCY/ITEM') CONSTITUENCY(c)
                            where
                                CONSTITUENCY.c.value('(CONSTITUENCYCODEID)[1]', 'uniqueidentifier') = 'D2DCA06A-BE6E-40B3-B95D-59A926181923'
                            for xml raw(''),type,elements,root('CONSTITUENCY'),BINARY BASE64
                        );
                    exec dbo.USP_CONSTITUENT_GETCONSTITUENCIES_FUNDRAISER_ADDFROMXML @ID, @SYSTEMDEFINEDCONSTITUENCY, @CHANGEAGENTID;

                    set @SYSTEMDEFINEDCONSTITUENCY =
                        (
                            select
                                CONSTITUENCY.c.query('.')
                            from
                                @CONSTITUENCY.nodes('/CONSTITUENCY/ITEM') CONSTITUENCY(c)
                            where
                                CONSTITUENCY.c.value('(CONSTITUENCYCODEID)[1]', 'uniqueidentifier') = '00E748FB-940D-4A7D-A133-C148B29410A8'
                            for xml raw(''),type,elements,root('CONSTITUENCY'),BINARY BASE64
                        );
                    exec dbo.USP_CONSTITUENT_GETCONSTITUENCIES_PROSPECT_ADDFROMXML @ID, @SYSTEMDEFINEDCONSTITUENCY, @CHANGEAGENTID;
                    if @SYSTEMDEFINEDCONSTITUENCY.exist('/CONSTITUENCY/ITEM/CONSTITUENCYCODEID/text()[string-length() > 0]') = 1
                        if not exists (select 1 from dbo.PROSPECT where ID=@ID)
                            insert into dbo.PROSPECT
                            (
                                ID,
                                ADDEDBYID,
                                CHANGEDBYID,
                                DATEADDED,
                                DATECHANGED
                            )
                            values
                            (
                                @ID,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE
                            );

                    set @SYSTEMDEFINEDCONSTITUENCY =
                        (
                            select
                                CONSTITUENCY.c.query('.')
                            from
                                @CONSTITUENCY.nodes('/CONSTITUENCY/ITEM') CONSTITUENCY(c)
                            where
                                CONSTITUENCY.c.value('(CONSTITUENCYCODEID)[1]', 'uniqueidentifier') = 'E7489703-3D63-4017-A2BC-88C092563C5D'
                            for xml raw(''),type,elements,root('CONSTITUENCY'),BINARY BASE64
                        );
                    exec dbo.USP_CONSTITUENT_GETCONSTITUENCIES_VOLUNTEER_ADDFROMXML @ID, @SYSTEMDEFINEDCONSTITUENCY, @CHANGEAGENTID;
                    if @SYSTEMDEFINEDCONSTITUENCY.exist('/CONSTITUENCY/ITEM/CONSTITUENCYCODEID/text()[string-length() > 0]') = 1
                        if not exists (select 1 from dbo.VOLUNTEER where ID=@ID)
                            insert into dbo.VOLUNTEER
                            (
                                ID,
                                ADDEDBYID,
                                CHANGEDBYID,
                                DATEADDED,
                                DATECHANGED
                            )
                            values
                            (
                                @ID,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE
                            );

                    --Committee

                    set @SYSTEMDEFINEDCONSTITUENCY =
                        (
                            select
                                CONSTITUENCY.c.query('.')
                            from
                                @CONSTITUENCY.nodes('/CONSTITUENCY/ITEM') CONSTITUENCY(c)
                            where
                                CONSTITUENCY.c.value('(CONSTITUENCYCODEID)[1]', 'uniqueidentifier') = 'AC9DB5A4-14E0-416A-9FB2-04038AC66799'
                            for xml raw(''),type,elements,root('CONSTITUENCY'),BINARY BASE64
                        );
                    exec dbo.USP_CONSTITUENT_GETCONSTITUENCIES_COMMITTEE_ADDFROMXML @ID, @SYSTEMDEFINEDCONSTITUENCY, @CHANGEAGENTID;
                    if @SYSTEMDEFINEDCONSTITUENCY.exist('/CONSTITUENCY/ITEM/CONSTITUENCYCODEID/text()[string-length() > 0]') = 1
                        if not exists (select 1 from dbo.COMMITTEE where ID=@ID)
                            insert into dbo.COMMITTEE
                            (
                                ID,
                                ADDEDBYID,
                                CHANGEDBYID,
                                DATEADDED,
                                DATECHANGED
                            )
                            values
                            (
                                @ID,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
          @CURRENTDATE,
                                @CURRENTDATE
                            );

                    if @CONSTITUENT_SITEID is not null 
                    begin
                        insert into dbo.[CONSTITUENTSITE]
                        (
                            [SITEID],
                            [CONSTITUENTID],
                            [ADDEDBYID],
                            [CHANGEDBYID],
                            [DATEADDED],
                            [DATECHANGED]
                        )
                        values
                        (
                            @CONSTITUENT_SITEID,
                            @ID,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                        )
                    end
                    else
                    begin
                        if dbo.UFN_SITEREQUIREDFORUSER(@BATCHOWNERID) = 1
                            insert into dbo.CONSTITUENTSITE
                            (
                                DATEADDED,
                                DATECHANGED,
                                ADDEDBYID,
                                CHANGEDBYID,
                                CONSTITUENTID,
                                SITEID
                            )
                            select 
                                @CURRENTDATE,
                                @CURRENTDATE,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @ID,
                                SITEID
                            from 
                                dbo.UFN_SITESFORUSER(@BATCHOWNERID)
                    end

                    exec dbo.USP_CONSTITUENT_GETINTERESTS_ADDFROMXML @ID, @INTEREST, @CHANGEAGENTID;
                    exec dbo.USP_CONSTITUENT_GETALTERNATELOOKUPIDS_ADDFROMXML @ID, @ALTERNATELOOKUPIDS, @CHANGEAGENTID;

                    -- Only add declarations if the constituent is an individual

                    if @ISORGANIZATION = 0 and @TAXDECLARATIONS is not null
                    begin
                        insert into dbo.TAXDECLARATION
                        (
                            CONSTITUENTID,
                            DECLARATIONINDICATORCODE, 
                            DECLARATIONMADE, 
                            DECLARATIONSTARTS, 
                            DECLARATIONENDS, 
                            CHARITYCLAIMREFERENCENUMBERID, 
                            PAYSTAXCODE,
                            DECLARATIONSOURCECODEID,
                            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                        )
                        select
                            @ID,
                            DECLARATIONINDICATORCODE, 
                            DECLARATIONMADE, 
                            DECLARATIONSTARTS, 
                            DECLARATIONENDS, 
                            CHARITYCLAIMREFERENCENUMBERID, 
                            PAYSTAXCODE,
                            DECLARATIONSOURCECODEID,
                            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                        from dbo.UFN_CONSTITUENTBATCH_GETTAXDECLARATIONS_FROMITEMLISTXML(@TAXDECLARATIONS)
                    end

                    if @SOLICITCODES is not null
                    begin
                        insert into dbo.CONSTITUENTSOLICITCODE
                        (
                            CONSTITUENTID,
                            SOLICITCODEID,
                            STARTDATE,
                            ENDDATE,
                            COMMENTS,
                            SEQUENCE,
                            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,
                            CONSENTPREFERENCECODE,
                            SOURCECODEID,
                            SOURCEFILEPATH,
                            PRIVACYPOLICYFILEPATH,
                            SUPPORTINGINFORMATION,
                            CONSENTSTATEMENT
                        )
                        select
                            @ID,
                            SOLICITCODEID,
                            STARTDATE,
                            ENDDATE,
                            COMMENTS,
                            SEQUENCE,
                            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,
                            CONSENTPREFERENCECODE,
                            SOURCECODEID,
                            SOURCEFILEPATH,
                            PRIVACYPOLICYFILEPATH,
                            SUPPORTINGINFORMATION,
                            CONSENTSTATEMENT
                        from dbo.UFN_CONSTITUENTBATCH_GETSOLICITCODES_FROMITEMLISTXML(@SOLICITCODES)    
                    end

                    if (@ETHNICITYCODEID is not null) or (@RELIGIONCODEID is not null) or (@TARGETCODEID is not null) or (@INCOMECODEID is not null) or (len(coalesce(@BIRTHPLACE, '')) > 0)
                    begin
                        insert into dbo.DEMOGRAPHIC
                            (ID, ETHNICITYCODEID, RELIGIONCODEID, TARGETCODEID, INCOMECODEID, BIRTHPLACE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                        values
                            (@ID, @ETHNICITYCODEID, @RELIGIONCODEID, @TARGETCODEID, @INCOMECODEID, @BIRTHPLACE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
                    end

                    if @MAIDENNAME is not null and @MAIDENNAME != ''
                    begin
                        insert into dbo.ALIAS
                            (CONSTITUENTID, KEYNAME, FIRSTNAME, MIDDLENAME, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
                        values
                            (@ID, @MAIDENNAME, @FIRSTNAME, @MIDDLENAME, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID)
                    end

                    if @SPOUSEID is not null and @SPOUSE_MAIDENNAME is not null and @SPOUSE_MAIDENNAME != ''
                    begin
                        insert into dbo.ALIAS
                            (CONSTITUENTID, KEYNAME, FIRSTNAME, MIDDLENAME, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
                        values
                            (@SPOUSEID, @SPOUSE_MAIDENNAME, @SPOUSE_FIRSTNAME, @SPOUSE_MIDDLENAME, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID)
                    end


                if(dbo.UFN_CONSTITUENTBATCH_VALIDATESOCIALMEDIAACCOUNTS(@SOCIALMEDIAACCOUNTS)=0)
                begin
                  raiserror('BBERR_SOCIALMEDIAACCOUNT_DUPLICATENOTALLOWED', 13, 1);
                end

                    if @SOCIALMEDIAACCOUNTS is not null
                    begin
                        insert into dbo.SOCIALMEDIAACCOUNT
                        (
                            CONSTITUENTID,
                            SOCIALMEDIASERVICEID,
                            USERID,
                            URL,
                            SOCIALMEDIAACCOUNTTYPECODEID,
                            INFOSOURCECODEID,
                            DONOTCONTACT,
                            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                        )
                        select
                            @ID,
                            ACCOUNTS.SOCIALMEDIASERVICEID,
                            ACCOUNTS.USERID,
                            ACCOUNTS.URL,
                            ACCOUNTS.SOCIALMEDIAACCOUNTTYPECODEID,
                            ACCOUNTS.INFOSOURCECODEID,
                            ACCOUNTS.DONOTCONTACT,
                            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                        from 
                            dbo.UFN_CONSTITUENTBATCH_GETSOCIALMEDIAACCOUNTS_FROMITEMLISTXML(@SOCIALMEDIAACCOUNTS) ACCOUNTS
                    end
                end
        end try
        begin catch
            exec dbo.USP_RAISE_ERROR;
            return 1;
        end catch

        return 0;