USP_DATAFORMTEMPLATE_EDITLOAD_REVENUEBATCHCONSTITUENTINBATCH

Parameters

Parameter	Parameter Type	Mode
@ID	nvarchar(110)	IN
@DATALOADED	bit	INOUT
@TSLONG	bigint	INOUT
@ISORGANIZATION	bit	INOUT
@ISEXISTINGCONSTITUENT	bit	INOUT
@LASTNAME	nvarchar(100)	INOUT
@ORGANIZATIONNAME	nvarchar(100)	INOUT
@FIRSTNAME	nvarchar(50)	INOUT
@MIDDLENAME	nvarchar(50)	INOUT
@MAIDENNAME	nvarchar(100)	INOUT
@NICKNAME	nvarchar(50)	INOUT
@TITLECODEID	uniqueidentifier	INOUT
@SUFFIXCODEID	uniqueidentifier	INOUT
@GENDERCODE	tinyint	INOUT
@BIRTHDATE	UDT_FUZZYDATE	INOUT
@ISHOUSEHOLDMEMBER	bit	INOUT
@ADDRESS_ADDRESSTYPECODEID	uniqueidentifier	INOUT
@ADDRESS_DONOTMAIL	bit	INOUT
@ADDRESS_DONOTMAILREASONCODEID	uniqueidentifier	INOUT
@ADDRESS_COUNTRYID	uniqueidentifier	INOUT
@ADDRESS_STATEID	uniqueidentifier	INOUT
@ADDRESS_ADDRESSBLOCK	nvarchar(150)	INOUT
@ADDRESS_CITY	nvarchar(50)	INOUT
@ADDRESS_POSTCODE	nvarchar(12)	INOUT
@ADDRESS_UPDATEMATCHINGHOUSEHOLDADDRESSES	bit	INOUT
@ADDRESS_MATCHINGHOUSEHOLDMEMBERS	xml	INOUT
@ADDRESS_OMITFROMVALIDATION	bit	INOUT
@ADDRESS_CART	nvarchar(10)	INOUT
@ADDRESS_DPC	nvarchar(8)	INOUT
@ADDRESS_LOT	nvarchar(5)	INOUT
@ADDRESS_COUNTYCODEID	uniqueidentifier	INOUT
@ADDRESS_CONGRESSIONALDISTRICTCODEID	uniqueidentifier	INOUT
@ADDRESS_LASTVALIDATIONATTEMPTDATE	datetime	INOUT
@ADDRESS_VALIDATIONMESSAGE	nvarchar(200)	INOUT
@ADDRESS_CERTIFICATIONDATA	int	INOUT
@VALIDATIONCOUNTRIES	xml	INOUT
@PHONE_PHONETYPECODEID	uniqueidentifier	INOUT
@PHONE_NUMBER	nvarchar(100)	INOUT
@EMAILADDRESS_EMAILADDRESSTYPECODEID	uniqueidentifier	INOUT
@EMAILADDRESS_EMAILADDRESS	UDT_EMAILADDRESS	INOUT
@ORGANIZATION_ADDRESSTYPECODEID	uniqueidentifier	INOUT
@ORGANIZATION_DONOTMAIL	bit	INOUT
@ORGANIZATION_DONOTMAILREASONCODEID	uniqueidentifier	INOUT
@ORGANIZATION_COUNTRYID	uniqueidentifier	INOUT
@ORGANIZATION_STATEID	uniqueidentifier	INOUT
@ORGANIZATION_ADDRESSBLOCK	nvarchar(150)	INOUT
@ORGANIZATION_CITY	nvarchar(50)	INOUT
@ORGANIZATION_POSTCODE	nvarchar(12)	INOUT
@ORGANIZATION_OMITFROMVALIDATION	bit	INOUT
@ORGANIZATION_CART	nvarchar(10)	INOUT
@ORGANIZATION_DPC	nvarchar(8)	INOUT
@ORGANIZATION_LOT	nvarchar(5)	INOUT
@ORGANIZATION_COUNTYCODEID	uniqueidentifier	INOUT
@ORGANIZATION_CONGRESSIONALDISTRICTCODEID	uniqueidentifier	INOUT
@ORGANIZATION_LASTVALIDATIONATTEMPTDATE	datetime	INOUT
@ORGANIZATION_VALIDATIONMESSAGE	nvarchar(200)	INOUT
@ORGANIZATION_CERTIFICATIONDATA	int	INOUT
@ORGANIZATION_PHONETYPECODEID	uniqueidentifier	INOUT
@ORGANIZATION_NUMBER	nvarchar(100)	INOUT
@ORGANIZATION_EMAILADDRESSTYPECODEID	uniqueidentifier	INOUT
@ORGANIZATION_EMAILADDRESS	UDT_EMAILADDRESS	INOUT
@MARITALSTATUSCODEID	uniqueidentifier	INOUT
@WEBADDRESS	UDT_WEBADDRESS	INOUT
@INDUSTRYCODEID	uniqueidentifier	INOUT
@NUMEMPLOYEES	int	INOUT
@NUMSUBSIDIARIES	int	INOUT
@PARENTCORPID	uniqueidentifier	INOUT
@BATCHSPOUSEID	uniqueidentifier	INOUT
@BATCHSPOUSERELATIONID	uniqueidentifier	INOUT
@SPOUSEID	uniqueidentifier	INOUT
@EXISTINGSPOUSE	bit	INOUT
@SPOUSE_LASTNAME	nvarchar(100)	INOUT
@SPOUSE_FIRSTNAME	nvarchar(50)	INOUT
@SPOUSE_MIDDLENAME	nvarchar(50)	INOUT
@SPOUSE_MAIDENNAME	nvarchar(100)	INOUT
@SPOUSE_NICKNAME	nvarchar(50)	INOUT
@SPOUSE_TITLECODEID	uniqueidentifier	INOUT
@SPOUSE_SUFFIXCODEID	uniqueidentifier	INOUT
@SPOUSE_GENDERCODE	tinyint	INOUT
@SPOUSE_BIRTHDATE	UDT_FUZZYDATE	INOUT
@SPOUSE_RECIPROCALTYPECODEID	uniqueidentifier	INOUT
@SPOUSE_RELATIONSHIPTYPECODEID	uniqueidentifier	INOUT
@COPYPRIMARYINFORMATION	bit	INOUT
@SPOUSE_STARTDATE	datetime	INOUT
@ISSPOUSERELATIONSHIP	bit	INOUT
@BATCHBUSINESSID	uniqueidentifier	INOUT
@BATCHBUSINESSRELATIONID	uniqueidentifier	INOUT
@BUSINESSID	uniqueidentifier	INOUT
@EXISTINGBUSINESS	bit	INOUT
@BUSINESS_NAME	nvarchar(100)	INOUT
@BUSINESS_ADDRESSTYPECODEID	uniqueidentifier	INOUT
@BUSINESS_COUNTRYID	uniqueidentifier	INOUT
@BUSINESS_STATEID	uniqueidentifier	INOUT
@BUSINESS_ADDRESSBLOCK	nvarchar(150)	INOUT
@BUSINESS_CITY	nvarchar(50)	INOUT
@BUSINESS_POSTCODE	nvarchar(12)	INOUT
@BUSINESS_DONOTMAIL	bit	INOUT
@BUSINESS_DONOTMAILREASONCODEID	uniqueidentifier	INOUT
@BUSINESS_OMITFROMVALIDATION	bit	INOUT
@BUSINESS_CART	nvarchar(10)	INOUT
@BUSINESS_DPC	nvarchar(8)	INOUT
@BUSINESS_LOT	nvarchar(5)	INOUT
@BUSINESS_COUNTYCODEID	uniqueidentifier	INOUT
@BUSINESS_CONGRESSIONALDISTRICTCODEID	uniqueidentifier	INOUT
@BUSINESS_LASTVALIDATIONATTEMPTDATE	datetime	INOUT
@BUSINESS_VALIDATIONMESSAGE	nvarchar(200)	INOUT
@BUSINESS_CERTIFICATIONDATA	int	INOUT
@BUSINESS_PHONETYPECODEID	uniqueidentifier	INOUT
@BUSINESS_NUMBER	nvarchar(100)	INOUT
@BUSINESS_RECIPROCALTYPECODEID	uniqueidentifier	INOUT
@BUSINESS_RELATIONSHIPTYPECODEID	uniqueidentifier	INOUT
@BUSINESS_STARTDATE	datetime	INOUT
@ISCONTACT	bit	INOUT
@ISPRIMARYCONTACT	bit	INOUT
@CONTACTTYPECODEID	uniqueidentifier	INOUT
@POSITION	nvarchar(100)	INOUT
@ISMATCHINGGIFTRELATIONSHIP	bit	INOUT
@ZIPLOOKUPCOUNTRIES	xml	INOUT
@ISGROUP	bit	INOUT
@ISHOUSEHOLD	bit	INOUT
@GROUP_NAME	nvarchar(100)	INOUT
@GROUP_DESCRIPTION	nvarchar(300)	INOUT
@GROUP_GIVESANONYMOUSLY	bit	INOUT
@GROUP_ADDRESSTYPECODEID	uniqueidentifier	INOUT
@GROUP_COUNTRYID	uniqueidentifier	INOUT
@GROUP_ADDRESSBLOCK	nvarchar(150)	INOUT
@GROUP_CITY	nvarchar(50)	INOUT
@GROUP_STATEID	uniqueidentifier	INOUT
@GROUP_POSTCODE	nvarchar(12)	INOUT
@GROUP_OMITFROMVALIDATION	bit	INOUT
@GROUP_CART	nvarchar(10)	INOUT
@GROUP_DPC	nvarchar(8)	INOUT
@GROUP_LOT	nvarchar(5)	INOUT
@GROUP_COUNTYCODEID	uniqueidentifier	INOUT
@GROUP_CONGRESSIONALDISTRICTCODEID	uniqueidentifier	INOUT
@GROUP_LASTVALIDATIONATTEMPTDATE	datetime	INOUT
@GROUP_VALIDATIONMESSAGE	nvarchar(200)	INOUT
@GROUP_CERTIFICATIONDATA	int	INOUT
@GROUP_PHONETYPECODEID	uniqueidentifier	INOUT
@GROUP_NUMBER	nvarchar(100)	INOUT
@GROUP_EMAILADDRESSTYPECODEID	uniqueidentifier	INOUT
@GROUP_EMAILADDRESS	UDT_EMAILADDRESS	INOUT
@GROUP_WEBADDRESS	UDT_WEBADDRESS	INOUT
@GROUP_DONOTMAIL	bit	INOUT
@GROUP_DONOTMAILREASONCODEID	uniqueidentifier	INOUT
@GROUP_UPDATEMATCHINGHOUSEHOLDADDRESSES	bit	INOUT
@GROUP_GROUPTYPEID	uniqueidentifier	INOUT
@GROUP_PRIMARYCONTACTID	uniqueidentifier	INOUT
@GROUP_MEMBERS	xml	INOUT
@GROUP_NEWMEMBERSEARCHID	uniqueidentifier	INOUT
@GROUP_STARTDATE	datetime	INOUT
@HOUSEHOLD_HOUSEHOLDSCANBEDONORS	bit	INOUT
@HOUSEHOLD_PRIMARYCONTACTID	uniqueidentifier	INOUT
@HOUSEHOLD_PRIMARYCONTACT_KEYNAME	nvarchar(100)	INOUT
@HOUSEHOLD_PRIMARYCONTACT_FIRSTNAME	nvarchar(50)	INOUT
@HOUSEHOLD_PRIMARYCONTACT_MIDDLENAME	nvarchar(50)	INOUT
@HOUSEHOLD_PRIMARYCONTACT_TITLECODEID	uniqueidentifier	INOUT
@HOUSEHOLD_PRIMARYCONTACT_SUFFIXCODEID	uniqueidentifier	INOUT
@HOUSEHOLD_PRIMARYCONTACT_COPYHOUSEHOLDCONTACT	bit	INOUT
@HOUSEHOLD_SECONDMEMBERID	uniqueidentifier	INOUT
@HOUSEHOLD_SECONDMEMBER_KEYNAME	nvarchar(100)	INOUT
@HOUSEHOLD_SECONDMEMBER_FIRSTNAME	nvarchar(50)	INOUT
@HOUSEHOLD_SECONDMEMBER_MIDDLENAME	nvarchar(50)	INOUT
@HOUSEHOLD_SECONDMEMBER_TITLECODEID	uniqueidentifier	INOUT
@HOUSEHOLD_SECONDMEMBER_SUFFIXCODEID	uniqueidentifier	INOUT
@HOUSEHOLD_SECONDMEMBER_COPYHOUSEHOLDCONTACT	bit	INOUT
@HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID	uniqueidentifier	INOUT
@HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID	uniqueidentifier	INOUT
@HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_STARTDATE	datetime	INOUT
@HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_ISSPOUSE	bit	INOUT
@HOUSEHOLDCOPYPRIMARYCONTACTINFO	bit	INOUT
@CURRENTAPPUSERID	uniqueidentifier	IN
@DEFAULTCOUNTRYID	uniqueidentifier	INOUT
@JOBCATEGORYCODEID	uniqueidentifier	INOUT
@CAREERLEVELCODEID	uniqueidentifier	INOUT
@GROUP_DONOTCALL	bit	INOUT
@GROUP_DONOTEMAIL	bit	INOUT
@ADDRESSID	uniqueidentifier	INOUT
@PHONEID	uniqueidentifier	INOUT
@EMAILADDRESSID	uniqueidentifier	INOUT
@SITEID	uniqueidentifier	INOUT
@CONSTITUENTSECURITYGROUPID	uniqueidentifier	INOUT
@CONSTITUENCYCODEID	uniqueidentifier	INOUT
@ISBBISROW	bit	INOUT
@INFOSOURCECODEID	uniqueidentifier	INOUT
@SPOUSE_CLASSOF	UDT_YEAR	INOUT
@NAMEFORMATS	xml	INOUT
@PHONE_COUNTRYID	uniqueidentifier	INOUT
@SPOUSE_LOOKUPID	nvarchar(100)	INOUT
@SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS	bit	INOUT
@SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR	decimal(5, 2)	INOUT
@SPOUSE_PRIMARYRECOGNITIONTYPECODEID	uniqueidentifier	INOUT
@SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS	bit	INOUT
@SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR	decimal(5, 2)	INOUT
@SPOUSE_RECIPROCALRECOGNITIONTYPECODEID	uniqueidentifier	INOUT
@BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS	bit	INOUT
@BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR	decimal(5, 2)	INOUT
@BUSINESS_PRIMARYRECOGNITIONTYPECODEID	uniqueidentifier	INOUT
@BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS	bit	INOUT
@BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR	decimal(5, 2)	INOUT
@BUSINESS_RECIPROCALRECOGNITIONTYPECODEID	uniqueidentifier	INOUT
@ORIGINAL_KEYNAME	nvarchar(100)	INOUT
@ORIGINAL_FIRSTNAME	nvarchar(50)	INOUT
@GENDERCODEID	uniqueidentifier	INOUT
@SPOUSE_GENDERCODEID	uniqueidentifier	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_REVENUEBATCHCONSTITUENTINBATCH (
    @ID nvarchar(110),
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
    @ISORGANIZATION bit = null output,
    @ISEXISTINGCONSTITUENT bit = null output,
    @LASTNAME nvarchar(100) = null output,
    @ORGANIZATIONNAME nvarchar(100) = null output,
    @FIRSTNAME nvarchar(50) = null output,
    @MIDDLENAME nvarchar(50) = null output,
    @MAIDENNAME nvarchar(100) = null output,
    @NICKNAME nvarchar(50) = null output,
    @TITLECODEID uniqueidentifier = null output,
    @SUFFIXCODEID uniqueidentifier = null output,
    @GENDERCODE tinyint = null output,
    @BIRTHDATE dbo.UDT_FUZZYDATE = null output,
    @ISHOUSEHOLDMEMBER bit = null output,
    @ADDRESS_ADDRESSTYPECODEID uniqueidentifier = null output,
    @ADDRESS_DONOTMAIL bit = null output,
    @ADDRESS_DONOTMAILREASONCODEID uniqueidentifier = null output,
    @ADDRESS_COUNTRYID uniqueidentifier = null output,
    @ADDRESS_STATEID uniqueidentifier = null output,
    @ADDRESS_ADDRESSBLOCK nvarchar(150) = null output,
    @ADDRESS_CITY nvarchar(50) = null output,
    @ADDRESS_POSTCODE nvarchar(12) = null output,
    @ADDRESS_UPDATEMATCHINGHOUSEHOLDADDRESSES bit = null output,
    @ADDRESS_MATCHINGHOUSEHOLDMEMBERS xml = null output,
    -- Address Validation

    @ADDRESS_OMITFROMVALIDATION bit = null output,
    @ADDRESS_CART nvarchar(10) = null output,
    @ADDRESS_DPC nvarchar(8) = null output,
    @ADDRESS_LOT nvarchar(5) = null output,
    @ADDRESS_COUNTYCODEID uniqueidentifier = null output,
    @ADDRESS_CONGRESSIONALDISTRICTCODEID uniqueidentifier = null output,
    @ADDRESS_LASTVALIDATIONATTEMPTDATE datetime = null output,
    @ADDRESS_VALIDATIONMESSAGE nvarchar(200) = null output,
    @ADDRESS_CERTIFICATIONDATA integer = null output,
    @VALIDATIONCOUNTRIES xml = null output,
    @PHONE_PHONETYPECODEID uniqueidentifier = null output,
    @PHONE_NUMBER nvarchar(100) = null output,
    @EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null output,
    @EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
    @ORGANIZATION_ADDRESSTYPECODEID uniqueidentifier = null output,
    @ORGANIZATION_DONOTMAIL bit = null output,
    @ORGANIZATION_DONOTMAILREASONCODEID uniqueidentifier = null output,
    @ORGANIZATION_COUNTRYID uniqueidentifier = null output,
    @ORGANIZATION_STATEID uniqueidentifier = null output,
    @ORGANIZATION_ADDRESSBLOCK nvarchar(150) = null output,
    @ORGANIZATION_CITY nvarchar(50) = null output,
    @ORGANIZATION_POSTCODE nvarchar(12) = null output,
    -- Address Validation

    @ORGANIZATION_OMITFROMVALIDATION bit = null output,
    @ORGANIZATION_CART nvarchar(10) = null output,
    @ORGANIZATION_DPC nvarchar(8) = null output,
    @ORGANIZATION_LOT nvarchar(5) = null output,
    @ORGANIZATION_COUNTYCODEID uniqueidentifier = null output,
    @ORGANIZATION_CONGRESSIONALDISTRICTCODEID uniqueidentifier = null output,
    @ORGANIZATION_LASTVALIDATIONATTEMPTDATE datetime = null output,
    @ORGANIZATION_VALIDATIONMESSAGE nvarchar(200) = null output,
    @ORGANIZATION_CERTIFICATIONDATA integer = null output,
    @ORGANIZATION_PHONETYPECODEID uniqueidentifier = null output,
    @ORGANIZATION_NUMBER nvarchar(100) = null output,
    @ORGANIZATION_EMAILADDRESSTYPECODEID uniqueidentifier = null output,
    @ORGANIZATION_EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
    @MARITALSTATUSCODEID uniqueidentifier = null output,
    @WEBADDRESS dbo.UDT_WEBADDRESS = null output,
    @INDUSTRYCODEID uniqueidentifier = null output,
    @NUMEMPLOYEES int = null output,
    @NUMSUBSIDIARIES int = null output,
    @PARENTCORPID uniqueidentifier = null output,
    --Individual's relationship Variables

    @BATCHSPOUSEID uniqueidentifier = null output,
    @BATCHSPOUSERELATIONID uniqueidentifier = null output,
    @SPOUSEID uniqueidentifier = null output,
    @EXISTINGSPOUSE bit = null output,
    @SPOUSE_LASTNAME nvarchar(100) = null output,
    @SPOUSE_FIRSTNAME nvarchar(50) = null output,
    @SPOUSE_MIDDLENAME nvarchar(50) = null output,
    @SPOUSE_MAIDENNAME nvarchar(100) = null output,
    @SPOUSE_NICKNAME nvarchar(50) = null output,
    @SPOUSE_TITLECODEID uniqueidentifier = null output,
    @SPOUSE_SUFFIXCODEID uniqueidentifier = null output,
    @SPOUSE_GENDERCODE tinyint = null output,
    @SPOUSE_BIRTHDATE dbo.UDT_FUZZYDATE = null output,
    @SPOUSE_RECIPROCALTYPECODEID uniqueidentifier = null output,
    @SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier = null output,
    @COPYPRIMARYINFORMATION bit = null output,
    @SPOUSE_STARTDATE datetime = null output,
    @ISSPOUSERELATIONSHIP bit = null output,
    --Organization variables

    @BATCHBUSINESSID uniqueidentifier = null output,
    @BATCHBUSINESSRELATIONID uniqueidentifier = null output,
    @BUSINESSID uniqueidentifier = null output,
    @EXISTINGBUSINESS bit = null output,
    @BUSINESS_NAME nvarchar(100) = null output,
    @BUSINESS_ADDRESSTYPECODEID uniqueidentifier = null output,
    @BUSINESS_COUNTRYID uniqueidentifier = null output,
    @BUSINESS_STATEID uniqueidentifier = null output,
    @BUSINESS_ADDRESSBLOCK nvarchar(150) = null output,
    @BUSINESS_CITY nvarchar(50) = null output,
    @BUSINESS_POSTCODE nvarchar(12) = null output,
    @BUSINESS_DONOTMAIL bit = null output,
    @BUSINESS_DONOTMAILREASONCODEID uniqueidentifier = null output,
    -- Address Validation

    @BUSINESS_OMITFROMVALIDATION bit = null output,
    @BUSINESS_CART nvarchar(10) = null output,
    @BUSINESS_DPC nvarchar(8) = null output,
    @BUSINESS_LOT nvarchar(5) = null output,
    @BUSINESS_COUNTYCODEID uniqueidentifier = null output,
    @BUSINESS_CONGRESSIONALDISTRICTCODEID uniqueidentifier = null output,
    @BUSINESS_LASTVALIDATIONATTEMPTDATE datetime = null output,
    @BUSINESS_VALIDATIONMESSAGE nvarchar(200) = null output,
    @BUSINESS_CERTIFICATIONDATA integer = null output,
    @BUSINESS_PHONETYPECODEID uniqueidentifier = null output,
    @BUSINESS_NUMBER nvarchar(100) = null output,
    @BUSINESS_RECIPROCALTYPECODEID uniqueidentifier = null output,
    @BUSINESS_RELATIONSHIPTYPECODEID uniqueidentifier = null output,
    @BUSINESS_STARTDATE datetime = null output,
    @ISCONTACT bit = null output,
    @ISPRIMARYCONTACT bit = null output,
    @CONTACTTYPECODEID uniqueidentifier = null output,
    @POSITION nvarchar(100) = null output,
    @ISMATCHINGGIFTRELATIONSHIP bit = null output,
    @ZIPLOOKUPCOUNTRIES xml = null output,
    -- Group/Household variables

    @ISGROUP bit = null output,
    @ISHOUSEHOLD bit = null output,
    @GROUP_NAME nvarchar(100) = null output,
    @GROUP_DESCRIPTION nvarchar(300) = null output,
    @GROUP_GIVESANONYMOUSLY bit = null output,
    @GROUP_ADDRESSTYPECODEID uniqueidentifier = null output,
    @GROUP_COUNTRYID uniqueidentifier = null output,
    @GROUP_ADDRESSBLOCK nvarchar(150) = null output,
    @GROUP_CITY nvarchar(50) = null output,
    @GROUP_STATEID uniqueidentifier = null output,
    @GROUP_POSTCODE nvarchar(12) = null output,
    @GROUP_OMITFROMVALIDATION bit = null output,
    @GROUP_CART nvarchar(10) = null output,
    @GROUP_DPC nvarchar(8) = null output,
    @GROUP_LOT nvarchar(5) = null output,
    @GROUP_COUNTYCODEID uniqueidentifier = null output,
    @GROUP_CONGRESSIONALDISTRICTCODEID uniqueidentifier = null output,
    @GROUP_LASTVALIDATIONATTEMPTDATE datetime = null output,
    @GROUP_VALIDATIONMESSAGE nvarchar(200) = null output,
    @GROUP_CERTIFICATIONDATA integer = null output,
    @GROUP_PHONETYPECODEID uniqueidentifier = null output,
    @GROUP_NUMBER nvarchar(100) = null output,
    @GROUP_EMAILADDRESSTYPECODEID uniqueidentifier = null output,
    @GROUP_EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
    @GROUP_WEBADDRESS dbo.UDT_WEBADDRESS = null output,
    @GROUP_DONOTMAIL bit = null output,
    @GROUP_DONOTMAILREASONCODEID uniqueidentifier = null output,
    @GROUP_UPDATEMATCHINGHOUSEHOLDADDRESSES bit = null output,
    -- Group variables

    @GROUP_GROUPTYPEID uniqueidentifier = null output,
    @GROUP_PRIMARYCONTACTID uniqueidentifier = null output,
    @GROUP_MEMBERS xml = null output,
    @GROUP_NEWMEMBERSEARCHID uniqueidentifier = null output, -- Not used in save operation, only exists to setup new member lookup control.

    @GROUP_STARTDATE datetime = null output,
    -- Household variable

    @HOUSEHOLD_HOUSEHOLDSCANBEDONORS bit = null output,
    -- Household Primary Member variables

    @HOUSEHOLD_PRIMARYCONTACTID uniqueidentifier = null output,
    @HOUSEHOLD_PRIMARYCONTACT_KEYNAME nvarchar(100) = null output,
    @HOUSEHOLD_PRIMARYCONTACT_FIRSTNAME nvarchar(50) = null output,
    @HOUSEHOLD_PRIMARYCONTACT_MIDDLENAME nvarchar(50) = null output,
    @HOUSEHOLD_PRIMARYCONTACT_TITLECODEID uniqueidentifier = null output,
    @HOUSEHOLD_PRIMARYCONTACT_SUFFIXCODEID uniqueidentifier = null output,
    @HOUSEHOLD_PRIMARYCONTACT_COPYHOUSEHOLDCONTACT bit = null output,
    -- Household Second Member variables

    @HOUSEHOLD_SECONDMEMBERID uniqueidentifier = null output,
    @HOUSEHOLD_SECONDMEMBER_KEYNAME nvarchar(100) = null output,
    @HOUSEHOLD_SECONDMEMBER_FIRSTNAME nvarchar(50) = null output,
    @HOUSEHOLD_SECONDMEMBER_MIDDLENAME nvarchar(50) = null output,
    @HOUSEHOLD_SECONDMEMBER_TITLECODEID uniqueidentifier = null output,
    @HOUSEHOLD_SECONDMEMBER_SUFFIXCODEID uniqueidentifier = null output,
    @HOUSEHOLD_SECONDMEMBER_COPYHOUSEHOLDCONTACT bit = null output,
    -- Household Second Member Relationship variables

    @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID uniqueidentifier = null output,
    @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID uniqueidentifier = null output,
    @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_STARTDATE datetime = null output,
    @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_ISSPOUSE bit = null output,
    -- Individual's household variables

    @HOUSEHOLDCOPYPRIMARYCONTACTINFO bit = null output,
    @CURRENTAPPUSERID uniqueidentifier,
    @DEFAULTCOUNTRYID uniqueidentifier = null output,
    @JOBCATEGORYCODEID uniqueidentifier = null output,
    @CAREERLEVELCODEID uniqueidentifier = null output,
    @GROUP_DONOTCALL bit = null output,
    @GROUP_DONOTEMAIL bit = null output,
    -- The records being shown on the edit form, returned here to pass to edit save.

    @ADDRESSID uniqueidentifier = null output,
    @PHONEID uniqueidentifier = null output,
    @EMAILADDRESSID uniqueidentifier = null output,
    -- Fields specific to BBIS created ERB

    @SITEID uniqueidentifier = null output,
    @CONSTITUENTSECURITYGROUPID uniqueidentifier = null output,
    @CONSTITUENCYCODEID uniqueidentifier = null output,
    @ISBBISROW bit = null output,
    @INFOSOURCECODEID uniqueidentifier = null output,
    @SPOUSE_CLASSOF dbo.UDT_YEAR = null output,
    @NAMEFORMATS xml = null output,
    @PHONE_COUNTRYID uniqueidentifier = null output,
    @SPOUSE_LOOKUPID nvarchar(100) = null output,
    @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = null output,
    @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR decimal(5, 2) = null output,
    @SPOUSE_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null output,
    @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = null output,
    @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5, 2) = null output,
    @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null output,
    @BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = null output,
    @BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR decimal(5, 2) = null output,
    @BUSINESS_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null output,
    @BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = null output,
    @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5, 2) = null output,
    @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null output,
    @ORIGINAL_KEYNAME nvarchar(100) = null output,
    @ORIGINAL_FIRSTNAME nvarchar(50) = null output,
    @GENDERCODEID uniqueidentifier = null output,
    @SPOUSE_GENDERCODEID uniqueidentifier = null output
    )
as
begin
    set nocount on;

    begin try
        declare @CONSTITUENTID uniqueidentifier = cast(substring(@ID, 1, 36) as uniqueidentifier);
        declare @BATCHID uniqueidentifier = cast(substring(@ID, 38, 36) as uniqueidentifier);
        declare @BATCHROWID uniqueidentifier = cast(substring(@ID, 75, 36) as uniqueidentifier);
        declare @KEYNAME nvarchar(100);
        declare @KEYNAMEPREFIX nvarchar(50);
        declare @BUSINESS_KEYNAME nvarchar(100);
        declare @BUSINESS_KEYNAMEPREFIX nvarchar(50);

        -- check to see if this is a BBIS row

        if @BATCHROWID is not null
            and exists (
                select 'x'
                from dbo.BATCHREVENUEBBNCINFO
                where BATCHREVENUEID = @BATCHROWID
                )
            set @ISBBISROW = 1;

        if @BATCHROWID is not null
            and exists (
                select 'x'
                from dbo.BATCHMEMBERSHIPDUESBBNCINFO
                where BATCHMEMBERSHIPDUESID = @BATCHROWID
                )
            set @ISBBISROW = 1;
        set @ISEXISTINGCONSTITUENT = 0;

        select @ISEXISTINGCONSTITUENT = 1
        from dbo.CONSTITUENT
        where CONSTITUENT.ID = @CONSTITUENTID;

        set @HOUSEHOLD_HOUSEHOLDSCANBEDONORS = dbo.UFN_INSTALLATIONINFO_GETHOUSEHOLDSCANBEDONORS()

        if @ISEXISTINGCONSTITUENT = 1
        begin
            -- Check constituent security.  The check is done manually since it only applies to existing constituents. If @CURRENTAPPUSERID is null then we're dealing with auto-match during commit.

            if (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1)
                or (dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID, '08F0259C-46C1-4162-B93B-178CB455A7BE', @CONSTITUENTID) = 1)
                or (@CURRENTAPPUSERID is null)
            begin
                declare @CUBID uniqueidentifier

            select
                @INFOSOURCECODEID = isnull(@INFOSOURCECODEID, INFOSOURCECODEID)
            from
                dbo.BATCHREVENUECONSTITUENT
            where
                BATCHREVENUECONSTITUENT.ID = @BATCHID

                if exists (
                        select 'x'
                        from dbo.BATCHCONSTITUENTUPDATE
                        where ID = @BATCHROWID
                            and PRIMARYRECORDID = @CONSTITUENTID
                        )
                begin
                    declare @CONSTITUENTTYPECODE tinyint
                    declare @ADDRESSES xml,
                        @PHONES xml,
                        @EMAILADDRESSES xml

                    -- existing constituent, edits already in CUB

                    exec dbo.USP_EDITLOAD_BATCHCONSTITUENTUPDATEBATCHROW @BATCHROWID,
                        @DATALOADED output,
                        @TSLONG output,
                        null, --@SEQUENCE

                        null, --@PRIMARYRECORDID

                        @CONSTITUENTTYPECODE output,
                        @BIRTHDATE output,
                        @FIRSTNAME output,
                        @GENDERCODE output,
                        @GROUP_GIVESANONYMOUSLY output,
                        @LASTNAME output,
                        null, --@LOOKUP_ID

                        @MAIDENNAME output,
                        @MIDDLENAME output,
                        @NICKNAME output,
                        @SUFFIXCODEID output,
                        @TITLECODEID output,
                        @WEBADDRESS output,
                        null, --@ALTERNATELOOKUPIDS

                        null, --@SECURITYATTRIBUTES

                        null, --@DECEASED

                        null, --@DECEASEDDATE

                        null, --@CONSTITUENT_SITEID

                        null, --@INTERESTS

                        null, --@PROSPECTMANAGERFUNDRAISERID

                        @ADDRESSES output,
                        @PHONES output,
                        @EMAILADDRESSES output,
                        null, --@CONSTITUENCIES

                        @GROUP_GROUPTYPEID output,
                        @GROUP_DESCRIPTION output,
                        @GROUP_STARTDATE output,
                        @INDUSTRYCODEID output,
                        @NUMEMPLOYEES output,
                        @NUMSUBSIDIARIES output,
                        @PARENTCORPID output,
                        @MARITALSTATUSCODEID output,
                        @SPOUSEID output,
                        @SPOUSE_TITLECODEID output,
                        @SPOUSE_FIRSTNAME output,
                        @SPOUSE_NICKNAME output,
                        @SPOUSE_MIDDLENAME output,
                        @SPOUSE_MAIDENNAME output,
                        @SPOUSE_LASTNAME output,
                        @SPOUSE_SUFFIXCODEID output,
                        @SPOUSE_BIRTHDATE output,
                        @SPOUSE_GENDERCODE output,
                        @SPOUSE_LOOKUPID output,
                        @SPOUSE_RELATIONSHIPTYPECODEID output,
                        @SPOUSE_RECIPROCALTYPECODEID output,
                        @BUSINESSID output,
                        @BUSINESS_ADDRESSBLOCK output,
                        @BUSINESS_ADDRESSTYPECODEID output,
                        @BUSINESS_CART output,
                        @BUSINESS_CITY output,
                        @BUSINESS_COUNTRYID output,
                        @BUSINESS_DONOTMAIL output,
                        @BUSINESS_DONOTMAILREASONCODEID output,
                        @BUSINESS_DPC output,
                        null, --@BUSINESS_EMAILADDRESS

                        null, --@BUSINESS_EMAILADDRESSTYPECODEID

                        null, --@BUSINESS_INDUSTRYCODEID

                        null, --@BUSINESS_LOOKUPID

                        @BUSINESS_LOT output,
                        @BUSINESS_NAME output,
                        null, --@BUSINESS_NUMEMPLOYEES

                        null, --@BUSINESS_NUMSUBSIDIARIES

                        null, --@BUSINESS_PARENTCORPID

                        @BUSINESS_NUMBER output,
                        null, --@BUSINESS_PHONE_COUNTRYID

                        @BUSINESS_PHONETYPECODEID output,
                        @BUSINESS_RELATIONSHIPTYPECODEID output,
                        @BUSINESS_RECIPROCALTYPECODEID output,
                        @BUSINESS_STATEID output,
                        null, --@BUSINESS_WEBADDRESS

                        @BUSINESS_POSTCODE output,
                        null, --@SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST

                        null, --@SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST

                        null, --@BUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST

                        null, --@BUSINESS_RECIPROCALTYPECODEIDSIMPLELIST

                        @BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS output,
                        @BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR output,
                        @BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS output,
                        @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR output,
                        @BUSINESS_PRIMARYRECOGNITIONTYPECODEID output,
                        @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID output,
                        null, --@BUSINESS_EMAILADDRESSSTARTDATE

                        @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS output,
                        @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR output,
                        @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS output,
                        @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR output,
                        @SPOUSE_PRIMARYRECOGNITIONTYPECODEID output,
                        @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID output,
                        null, --@CONSTITUENT_SITES

                        null, --@SPOUSE_ALTERNATELOOKUPIDS

                        null, --@SPOUSE_IMPORTLOOKUPID

                        null, --@ROWFROMBATCHUI

                        null, --@BBNCTRANID

                        null, --@PAGEID

                        null, --@PAGENAME

                        null, --@BBNCUSERID

                        null, --@REMOVESPOUSE

                        null, --@DUPLICATERECORDID

                        null, --@SOCIALMEDIAACCOUNTS

                        null, --@DOMANUALREVIEWFORAUTOMATCH

                        null, --@RELATIONSHIPS

                        null, --@NETCOMMUNITYTRANSACTIONPROCESSORID

                        null, --@BBNCID

                        null, --@NAMECODE

                        null, --@SIMILARADDRESSCODE

                        null, --@UNSIMILARADDRESSCODE

                        null, --@NEWADDRESSENDDATECODE

                        null, --@NEWADDRESSPRIMARYCODE

                        null, --@BIRTHDATERULECODE

                        null, --@DIFFERENTPHONECODE

                        null, --@NEWPHONEENDDATECODE

                        null, --@NEWPHONEPRIMARYCODE

                        null, --@DIFFERENTEMAILCODE

                        null, --@NEWEMAILENDDATECODE

                        null, --@NEWEMAILPRIMARYCODE

                        null, --@USEGLOBALSETTINGS

                        null, --@CREATEHISTORICALNAMECODE

                        @NAMEFORMATS output,
                        null, --@SUBMITTEDLOOKUPID

                        null, --@SUBMITTEDCLASSYEAR

                        null, --@SUBMITTEDEDUCATIONALINSTITUTION

                        null, --@REQUESTSNOEMAIL 

                        null, --@ORIGINAL_KEYNAME 

                        null, --@ORIGINAL_FIRSTNAME 

                        null, --@SOLICITCODES

                        @GENDERCODEID output,
                        @SPOUSE_GENDERCODEID output

                    -- retrieve constituency, site and security group

                    select top 1 @SITEID = SITEID
                    from dbo.BATCHCONSTITUENTUPDATESITES
                    where BATCHCONSTITUENTUPDATEID = @BATCHROWID

                    select top 1 @CONSTITUENTSECURITYGROUPID = CONSTIT_SECURITY_ATTRIBUTEID
                    from dbo.BATCHCONSTITUENTUPDATESECURITYATTRIBUTES
                    where BATCHCONSTITUENTUPDATEID = @BATCHROWID

                    select top 1 @CONSTITUENCYCODEID = CONSTITUENCYCODEID
                    from dbo.BATCHCONSTITUENTUPDATECONSTITUENCIES
                    where BATCHCONSTITUENTUPDATEID = @BATCHROWID
                    order by ORIGINALCONSTITUENCYID asc

                    -- TODO  @GROUP_PRIMARYCONTACTID

                    set @ISORGANIZATION = case 
                            when @CONSTITUENTTYPECODE = 1
                                then 1
                            else 0
                            end
                    set @ISGROUP = case 
                            when @CONSTITUENTTYPECODE in (
                                    2,
                                    3
                                    )
                                then 1
                            else 0
                            end
                    set @ISHOUSEHOLD = case 
                            when @CONSTITUENTTYPECODE = 2
                                then 1
                            else 0
                            end

                    -- There will only ever be one row in the addresses, phones, and email collections.

                    if @ADDRESSES is not null
                        select @ADDRESSID = ADDRESSID,
                            @ADDRESS_ADDRESSTYPECODEID = ADDRESSTYPECODEID,
                            @ADDRESS_DONOTMAIL = DONOTMAIL,
                            @ADDRESS_DONOTMAILREASONCODEID = DONOTMAILREASONCODEID,
                            @ADDRESS_COUNTRYID = COUNTRYID,
                            @ADDRESS_STATEID = STATEID,
                            @ADDRESS_ADDRESSBLOCK = ADDRESSBLOCK,
                            @ADDRESS_CITY = CITY,
                            @ADDRESS_POSTCODE = POSTCODE,
                            @ADDRESS_OMITFROMVALIDATION = OMITFROMVALIDATION,
                            @ADDRESS_CART = CART,
                            @ADDRESS_DPC = DPC,
                            @ADDRESS_LOT = LOT,
                            @ADDRESS_COUNTYCODEID = COUNTYCODEID,
                            @ADDRESS_CONGRESSIONALDISTRICTCODEID = CONGRESSIONALDISTRICTCODEID,
                            @ADDRESS_LASTVALIDATIONATTEMPTDATE = LASTVALIDATIONATTEMPTDATE,
                            @ADDRESS_VALIDATIONMESSAGE = VALIDATIONMESSAGE,
                            @ADDRESS_CERTIFICATIONDATA = CERTIFICATIONDATA
                        from dbo.UFN_BATCHCONSTITUENTUPDATE_GETADDRESSES_WITHDATES_FROMITEMLISTXML(@ADDRESSES);
                    else
                        select @ADDRESS_ADDRESSTYPECODEID = ADDRESS.ADDRESSTYPECODEID,
                            @ADDRESS_DONOTMAIL = ADDRESS.DONOTMAIL,
                            @ADDRESS_DONOTMAILREASONCODEID = ADDRESS.DONOTMAILREASONCODEID,
                            @ADDRESS_COUNTRYID = ADDRESS.COUNTRYID,
                            @ADDRESS_STATEID = ADDRESS.STATEID,
                            @ADDRESS_ADDRESSBLOCK = ADDRESS.ADDRESSBLOCK,
                            @ADDRESS_CITY = ADDRESS.CITY,
                            @ADDRESS_POSTCODE = ADDRESS.POSTCODE,
                            @ADDRESS_OMITFROMVALIDATION = ADDRESSVALIDATIONUPDATE.OMITFROMVALIDATION,
                            @ADDRESS_CART = ADDRESS.CART,
                            @ADDRESS_DPC = ADDRESS.DPC,
                            @ADDRESS_LOT = ADDRESS.LOT,
                            @ADDRESS_COUNTYCODEID = ADDRESSVALIDATIONUPDATE.COUNTYCODEID,
                            @ADDRESS_CONGRESSIONALDISTRICTCODEID = ADDRESSVALIDATIONUPDATE.CONGRESSIONALDISTRICTCODEID,
                            @ADDRESS_LASTVALIDATIONATTEMPTDATE = ADDRESSVALIDATIONUPDATE.LASTVALIDATIONATTEMPTDATE,
                            @ADDRESS_VALIDATIONMESSAGE = ADDRESSVALIDATIONUPDATE.VALIDATIONMESSAGE,
                            @ADDRESS_CERTIFICATIONDATA = ADDRESSVALIDATIONUPDATE.CERTIFICATIONDATA,
                            @ADDRESSID = ADDRESS.ID
                        from dbo.ADDRESS
                        left join dbo.ADDRESSVALIDATIONUPDATE
                            on ADDRESSVALIDATIONUPDATE.ID = ADDRESS.ID
                        where ADDRESS.CONSTITUENTID = @CONSTITUENTID
                            and ADDRESS.ISPRIMARY = 1;

                    if @PHONES is not null
                        select @PHONEID = PHONEID,
                            @PHONE_PHONETYPECODEID = PHONETYPECODEID,
                            @PHONE_NUMBER = NUMBER,
                            @GROUP_DONOTCALL = DONOTCALL,
                            @PHONE_COUNTRYID = COUNTRYID
                        from dbo.UFN_BATCHCONSTITUENTUPDATE_GETPHONES_WITHDATES_FROMITEMLISTXML(@PHONES);
                    else
                        select @PHONE_PHONETYPECODEID = PHONE.PHONETYPECODEID,
                            @PHONE_NUMBER = PHONE.NUMBER,
                            @GROUP_DONOTCALL = PHONE.DONOTCALL,
                            @PHONEID = PHONE.ID,
                            @PHONE_COUNTRYID = PHONE.COUNTRYID
                        from dbo.PHONE
                        where CONSTITUENTID = @CONSTITUENTID
                            and ISPRIMARY = 1;

                    if @EMAILADDRESSES is not null
                        select @EMAILADDRESSID = EMAILADDRESSID,
                            @EMAILADDRESS_EMAILADDRESSTYPECODEID = EMAILADDRESSTYPECODEID,
                            @EMAILADDRESS_EMAILADDRESS = EMAILADDRESS,
                            @GROUP_DONOTEMAIL = DONOTEMAIL
                        from dbo.UFN_BATCHCONSTITUENTUPDATE_GETEMAILADDRESSES_WITHDATES_FROMITEMLISTXML(@EMAILADDRESSES);
                    else
                        select @EMAILADDRESS_EMAILADDRESSTYPECODEID = EMAILADDRESS.EMAILADDRESSTYPECODEID,
                            @EMAILADDRESS_EMAILADDRESS = EMAILADDRESS.EMAILADDRESS,
                            @GROUP_DONOTEMAIL = EMAILADDRESS.DONOTEMAIL,
                            @EMAILADDRESSID = EMAILADDRESS.ID
                        from dbo.EMAILADDRESS
                        where CONSTITUENTID = @CONSTITUENTID
                            and ISPRIMARY = 1;
                end
                else
                begin
                    -- existing constituent, no edits yet

                    select @ISORGANIZATION = c.ISORGANIZATION,
                        @KEYNAME = c.KEYNAME,
                        @KEYNAMEPREFIX = c.KEYNAMEPREFIX,
                        @FIRSTNAME = c.FIRSTNAME,
                        @MIDDLENAME = c.MIDDLENAME,
                        @MAIDENNAME = c.MAIDENNAME,
                        @NICKNAME = c.NICKNAME,
                        @TITLECODEID = c.TITLECODEID,
                        @SUFFIXCODEID = c.SUFFIXCODEID,
                        @GENDERCODE = c.GENDERCODE,
                        @GENDERCODEID = c.GENDERCODEID,
                        @BIRTHDATE = c.BIRTHDATE,
                        @ADDRESS_ADDRESSTYPECODEID = ad.ADDRESSTYPECODEID,
                        @ADDRESS_DONOTMAIL = ad.DONOTMAIL,
                        @ADDRESS_DONOTMAILREASONCODEID = ad.DONOTMAILREASONCODEID,
                        @ADDRESS_COUNTRYID = ad.COUNTRYID,
                        @ADDRESS_STATEID = ad.STATEID,
                        @ADDRESS_ADDRESSBLOCK = ad.ADDRESSBLOCK,
                        @ADDRESS_CITY = ad.CITY,
                        @ADDRESS_POSTCODE = ad.POSTCODE,
                        @ADDRESS_OMITFROMVALIDATION = ADDRESSVALIDATIONUPDATE.OMITFROMVALIDATION,
                        @ADDRESS_CART = ad.CART,
                        @ADDRESS_DPC = ad.DPC,
                        @ADDRESS_LOT = ad.LOT,
                        @ADDRESS_COUNTYCODEID = ADDRESSVALIDATIONUPDATE.COUNTYCODEID,
                        @ADDRESS_CONGRESSIONALDISTRICTCODEID = ADDRESSVALIDATIONUPDATE.CONGRESSIONALDISTRICTCODEID,
                        @ADDRESS_LASTVALIDATIONATTEMPTDATE = ADDRESSVALIDATIONUPDATE.LASTVALIDATIONATTEMPTDATE,
                        @ADDRESS_VALIDATIONMESSAGE = ADDRESSVALIDATIONUPDATE.VALIDATIONMESSAGE,
                        @ADDRESS_CERTIFICATIONDATA = ADDRESSVALIDATIONUPDATE.CERTIFICATIONDATA,
                        @PHONE_PHONETYPECODEID = p.PHONETYPECODEID,
                        @PHONE_NUMBER = p.NUMBER,
                        @PHONE_COUNTRYID = p.COUNTRYID,
                        @GROUP_DONOTCALL = p.DONOTCALL,
                        @EMAILADDRESS_EMAILADDRESSTYPECODEID = em.EMAILADDRESSTYPECODEID,
                        @EMAILADDRESS_EMAILADDRESS = em.EMAILADDRESS,
                        @GROUP_DONOTEMAIL = em.DONOTEMAIL,
                        @MARITALSTATUSCODEID = c.MARITALSTATUSCODEID,
                        @WEBADDRESS = c.WEBADDRESS,
                        @INDUSTRYCODEID = o.INDUSTRYCODEID,
                        @NUMEMPLOYEES = o.NUMEMPLOYEES,
                        @NUMSUBSIDIARIES = o.NUMSUBSIDIARIES,
                        @PARENTCORPID = o.PARENTCORPID,
                        @ISGROUP = c.ISGROUP,
                        @ISHOUSEHOLD = case 
                            when GROUPDATA.GROUPTYPECODE = 0
                                then 1
                            else 0
                            end,
                        @GROUP_DESCRIPTION = GROUPDATA.DESCRIPTION,
                        @GROUP_GROUPTYPEID = GROUPDATA.GROUPTYPEID,
                        @GROUP_GIVESANONYMOUSLY = c.GIVESANONYMOUSLY,
                        @GROUP_STARTDATE = GROUPDATA.STARTDATE,
                        @GROUP_PRIMARYCONTACTID = (
                            select top 1 MEMBERID
                            from dbo.GROUPMEMBER
                            where GROUPID = @CONSTITUENTID
                                and ISPRIMARY = 1
                            ),
                        @ADDRESSID = ad.ID,
                        @PHONEID = p.ID,
                        @EMAILADDRESSID = em.ID,
                        -- spouse

                        @SPOUSEID = SPOUSE.ID,
                        @SPOUSE_BIRTHDATE = SPOUSE.BIRTHDATE,
                        @SPOUSE_FIRSTNAME = SPOUSE.FIRSTNAME,
                        @SPOUSE_GENDERCODE = SPOUSE.GENDERCODE,
                        @SPOUSE_GENDERCODEID = SPOUSE.GENDERCODEID,
                        @SPOUSE_LASTNAME = SPOUSE.KEYNAME,
                        @SPOUSE_MAIDENNAME = SPOUSE.MAIDENNAME,
                        @SPOUSE_MIDDLENAME = SPOUSE.MIDDLENAME,
                        @SPOUSE_NICKNAME = SPOUSE.NICKNAME,
                        @SPOUSE_SUFFIXCODEID = SPOUSE.SUFFIXCODEID,
                        @SPOUSE_TITLECODEID = SPOUSE.TITLECODEID,
                        @SPOUSE_LOOKUPID = SPOUSE.LOOKUPID,
                        @SPOUSE_RELATIONSHIPTYPECODEID = SR.RELATIONSHIPTYPECODEID,
                        @SPOUSE_RECIPROCALTYPECODEID = SR.RECIPROCALTYPECODEID,
                        @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = case 
                            when RCDP.PREVENTRECOGNITIONSDEFAULTING = 0
                                then 1
                            else 0
                            end,
                        @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR = RCDP.MATCHFACTOR,
                        @SPOUSE_PRIMARYRECOGNITIONTYPECODEID = RCDP.REVENUERECOGNITIONTYPECODEID,
                        @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = case 
                            when RCDR.PREVENTRECOGNITIONSDEFAULTING = 0
                                then 1
                            else 0
                            end,
                        @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR = RCDR.MATCHFACTOR,
                        @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID = RCDR.REVENUERECOGNITIONTYPECODEID,
                        -- business

                        @BUSINESS_ADDRESSBLOCK = BA.ADDRESSBLOCK,
                        @BUSINESS_ADDRESSTYPECODEID = BA.ADDRESSTYPECODEID,
                        @BUSINESS_CART = BA.CART,
                        @BUSINESS_CITY = BA.CITY,
                        @BUSINESS_COUNTRYID = BA.COUNTRYID,
                        @BUSINESS_DONOTMAIL = coalesce(BA.DONOTMAIL, '0'),
                        @BUSINESS_DONOTMAILREASONCODEID = BA.DONOTMAILREASONCODEID,
                        @BUSINESS_DPC = BA.DPC,
                        @BUSINESS_LOT = BA.LOT,
                        @BUSINESSID = BUSINESS.ID,
                        @BUSINESS_NAME = BUSINESS.KEYNAME,
                        @BUSINESS_NUMBER = BP.NUMBER,
                        @BUSINESS_PHONETYPECODEID = BP.PHONETYPECODEID,
                        @BUSINESS_RELATIONSHIPTYPECODEID = BR.RELATIONSHIPTYPECODEID,
                        @BUSINESS_RECIPROCALTYPECODEID = BR.RECIPROCALTYPECODEID,
                        @BUSINESS_STATEID = BA.STATEID,
                        @BUSINESS_POSTCODE = BA.POSTCODE,
                        @BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = case 
                            when BRCDP.PREVENTRECOGNITIONSDEFAULTING = 0
                                then 1
                            else 0
                            end,
                        @BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR = BRCDP.MATCHFACTOR,
                        @BUSINESS_PRIMARYRECOGNITIONTYPECODEID = BRCDP.REVENUERECOGNITIONTYPECODEID,
                        @BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = case 
                            when BRCDR.PREVENTRECOGNITIONSDEFAULTING = 0
                                then 1
                            else 0
                            end,
                        @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR = BRCDR.MATCHFACTOR,
                        @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID = BRCDR.REVENUERECOGNITIONTYPECODEID
                    from
                        dbo.CONSTITUENT c
                    left join dbo.ADDRESS ad
                        on c.ID = ad.CONSTITUENTID
                            and ad.ISPRIMARY = 1
                    left join dbo.ADDRESSVALIDATIONUPDATE
                        on ad.ID = ADDRESSVALIDATIONUPDATE.ID
                    left join dbo.PHONE p
                        on c.ID = p.CONSTITUENTID
                            and p.ISPRIMARY = 1
                    left join dbo.EMAILADDRESS em
                        on c.ID = em.CONSTITUENTID
                            and em.ISPRIMARY = 1
                    left join dbo.ORGANIZATIONDATA o
                        on c.ID = o.ID
                    left join dbo.GROUPDATA
                        on c.ID = GROUPDATA.ID
                    -- spouse

                    left join dbo.RELATIONSHIP SR
                        on SR.RELATIONSHIPCONSTITUENTID = C.ID
                            and SR.ISSPOUSE = 1
                    left join dbo.CONSTITUENT SPOUSE
                        on SR.RECIPROCALCONSTITUENTID = SPOUSE.ID
                    left join dbo.REVENUERECOGNITIONDEFAULT RCDP
                        on RCDP.SOURCECONSTITUENTID = C.ID
                            and RCDP.RECIPIENTCONSTITUENTID = SPOUSE.ID
                    left join dbo.REVENUERECOGNITIONDEFAULT RCDR
                        on RCDR.SOURCECONSTITUENTID = SPOUSE.ID
                            and RCDR.RECIPIENTCONSTITUENTID = C.ID
                    -- business

                    left join dbo.RELATIONSHIP BR
                        on BR.RELATIONSHIPCONSTITUENTID = C.ID
                            and BR.ISPRIMARYBUSINESS = 1
                    left join dbo.CONSTITUENT BUSINESS
                        on BR.RECIPROCALCONSTITUENTID = BUSINESS.ID
                            and C.ISORGANIZATION = 0
                    left join dbo.ADDRESS BA
                        on BA.CONSTITUENTID = BUSINESS.ID
                            and BA.ISPRIMARY = 1
                    left join dbo.PHONE BP
                        on BP.CONSTITUENTID = BUSINESS.ID
                            and BP.ISPRIMARY = 1
                    left join dbo.REVENUERECOGNITIONDEFAULT BRCDP
                        on BRCDP.SOURCECONSTITUENTID = C.ID
                            and BRCDP.RECIPIENTCONSTITUENTID = BUSINESS.ID
                    left join dbo.REVENUERECOGNITIONDEFAULT BRCDR
                        on BRCDR.SOURCECONSTITUENTID = BUSINESS.ID
                            and BRCDR.RECIPIENTCONSTITUENTID = C.ID
                    where c.ID = @CONSTITUENTID
                end

                declare @CURRENTDATE datetime

                set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate())
                set @ISHOUSEHOLDMEMBER = case 
                        when exists (
                                select 1
                                from dbo.GROUPMEMBER GM
                                left outer join dbo.GROUPMEMBERDATERANGE GMDR
                                    on GMDR.GROUPMEMBERID = GM.ID
                                left outer join dbo.GROUPDATA GD
                                    on GD.ID = GM.GROUPID
                                where GM.MEMBERID = @CONSTITUENTID
                                    and GD.GROUPTYPECODE = 0
                                    and (
                                        (
                                            GMDR.DATEFROM is null
                                            and (
                                                GMDR.DATETO is null
                                                or GMDR.DATETO > @CURRENTDATE
                                                )
                                            )
                                        or (
                                            GMDR.DATETO is null
                                            and (
                                                GMDR.DATEFROM is null
                                                or GMDR.DATEFROM <= @CURRENTDATE
                                                )
                                            )
                                        or (
                                            GMDR.DATEFROM <= @CURRENTDATE
                                            and GMDR.DATETO > @CURRENTDATE
                                            )
                                        )
                                )
                            then 1
                        else 0
                        end
                set @ADDRESS_MATCHINGHOUSEHOLDMEMBERS = (
                        select CONSTITUENTID,
                            NAME,
                            RELATIONSHIPTOPRIMARY
                        from dbo.UFN_ADDRESS_MATCHINGHOUSEHOLDRECORDS(@CONSTITUENTID, @ADDRESS_COUNTRYID, @ADDRESS_STATEID, @ADDRESS_ADDRESSBLOCK, @ADDRESS_CITY, @ADDRESS_POSTCODE, @ADDRESS_ADDRESSTYPECODEID)
                        for xml raw('ITEM'),
                            type,
                            elements,
                            root('ADDRESS_MATCHINGHOUSEHOLDMEMBERS'),
                            binary base64
                        )

                if @SPOUSEID is not null
                    set @EXISTINGSPOUSE = 1

                if @BUSINESSID is not null
                    set @EXISTINGBUSINESS = 1
            end
            else
                raiserror (
                        'BBERR_DB_RECORDSECURITY_PERMISSION_DENIED',
                        13,
                        1
                        )
        end
        else
            exec USP_BATCHREVENUECONSTITUENT_LOAD @CONSTITUENTID,
                null,
                @ISORGANIZATION output,
                @KEYNAME output,
                @KEYNAMEPREFIX output,
                @FIRSTNAME output,
                @MIDDLENAME output,
                @MAIDENNAME output,
                @NICKNAME output,
                @TITLECODEID output,
                @SUFFIXCODEID output,
                @GENDERCODE output,
                @BIRTHDATE output,
                @ADDRESS_ADDRESSTYPECODEID output,
                @ADDRESS_DONOTMAIL output,
                @ADDRESS_COUNTRYID output,
                @ADDRESS_STATEID output,
                @ADDRESS_ADDRESSBLOCK output,
                @ADDRESS_CITY output,
                @ADDRESS_POSTCODE output,
                @ADDRESS_OMITFROMVALIDATION output,
                @ADDRESS_CART output,
                @ADDRESS_DPC output,
                @ADDRESS_LOT output,
                @ADDRESS_COUNTYCODEID output,
                @ADDRESS_CONGRESSIONALDISTRICTCODEID output,
                @ADDRESS_LASTVALIDATIONATTEMPTDATE output,
                @ADDRESS_VALIDATIONMESSAGE output,
                @ADDRESS_CERTIFICATIONDATA output,
                @PHONE_PHONETYPECODEID output,
                @PHONE_NUMBER output,
                @EMAILADDRESS_EMAILADDRESSTYPECODEID output,
                @EMAILADDRESS_EMAILADDRESS output,
                @MARITALSTATUSCODEID output,
                @WEBADDRESS output,
                @INDUSTRYCODEID output,
                @NUMEMPLOYEES output,
                @NUMSUBSIDIARIES output,
                @PARENTCORPID output,
                @BATCHSPOUSEID output,
                @BATCHSPOUSERELATIONID output,
                @SPOUSEID output,
                null,
                @SPOUSE_LASTNAME output,
                @SPOUSE_FIRSTNAME output,
                @SPOUSE_MIDDLENAME output,
                @SPOUSE_MAIDENNAME output,
                @SPOUSE_NICKNAME output,
                @SPOUSE_TITLECODEID output,
                @SPOUSE_SUFFIXCODEID output,
                @SPOUSE_GENDERCODE output,
                @SPOUSE_BIRTHDATE output,
                @SPOUSE_RECIPROCALTYPECODEID output,
                @SPOUSE_RELATIONSHIPTYPECODEID output,
                @COPYPRIMARYINFORMATION output,
                @SPOUSE_STARTDATE output,
                null,
                null,
                null,
                null,
                @BATCHBUSINESSID output,
                @BATCHBUSINESSRELATIONID output,
                @BUSINESSID output,
                @EXISTINGBUSINESS output,
                @BUSINESS_KEYNAME output,
                @BUSINESS_KEYNAMEPREFIX output,
                @BUSINESS_ADDRESSTYPECODEID output,
                @BUSINESS_COUNTRYID output,
                @BUSINESS_STATEID output,
                @BUSINESS_ADDRESSBLOCK output,
                @BUSINESS_CITY output,
                @BUSINESS_POSTCODE output,
                @BUSINESS_DONOTMAIL output,
                @BUSINESS_OMITFROMVALIDATION output,
                @BUSINESS_CART output,
                @BUSINESS_DPC output,
                @BUSINESS_LOT output,
                @BUSINESS_COUNTYCODEID output,
                @BUSINESS_CONGRESSIONALDISTRICTCODEID output,
                @BUSINESS_LASTVALIDATIONATTEMPTDATE output,
                @BUSINESS_VALIDATIONMESSAGE output,
                @BUSINESS_CERTIFICATIONDATA output,
                @BUSINESS_PHONETYPECODEID output,
                @BUSINESS_NUMBER output,
                @BUSINESS_RECIPROCALTYPECODEID output,
                @BUSINESS_RELATIONSHIPTYPECODEID output,
                @BUSINESS_STARTDATE output,
                @ISCONTACT output,
                @ISPRIMARYCONTACT output,
                @CONTACTTYPECODEID output,
                @POSITION output,
                @ISMATCHINGGIFTRELATIONSHIP output,
                @ISGROUP output,
                @ISHOUSEHOLD output,
                @GROUP_GIVESANONYMOUSLY output,
                null,
                @GROUP_GROUPTYPEID output,
                @GROUP_DESCRIPTION output,
                @GROUP_STARTDATE output,
                @GROUP_PRIMARYCONTACTID output,
                @GROUP_MEMBERS output,
                null,
                @HOUSEHOLD_PRIMARYCONTACTID output,
                @HOUSEHOLD_PRIMARYCONTACT_KEYNAME output,
                @HOUSEHOLD_PRIMARYCONTACT_FIRSTNAME output,
                @HOUSEHOLD_PRIMARYCONTACT_MIDDLENAME output,
                @HOUSEHOLD_PRIMARYCONTACT_TITLECODEID output,
                @HOUSEHOLD_PRIMARYCONTACT_SUFFIXCODEID output,
                @HOUSEHOLD_PRIMARYCONTACT_COPYHOUSEHOLDCONTACT output,
                @HOUSEHOLD_SECONDMEMBERID output,
                @HOUSEHOLD_SECONDMEMBER_KEYNAME output,
                @HOUSEHOLD_SECONDMEMBER_FIRSTNAME output,
                @HOUSEHOLD_SECONDMEMBER_MIDDLENAME output,
                @HOUSEHOLD_SECONDMEMBER_TITLECODEID output,
                @HOUSEHOLD_SECONDMEMBER_SUFFIXCODEID output,
                @HOUSEHOLD_SECONDMEMBER_COPYHOUSEHOLDCONTACT output,
                @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID output,
                @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID output,
                @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_STARTDATE output,
                @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_ISSPOUSE output,
                null,
                null,
                null,
                null,
                @HOUSEHOLDCOPYPRIMARYCONTACTINFO output,
                @ISSPOUSERELATIONSHIP output,
                @ADDRESS_DONOTMAILREASONCODEID output,
                @BUSINESS_DONOTMAILREASONCODEID output,
                @JOBCATEGORYCODEID output,
                @CAREERLEVELCODEID output,
                @GROUP_DONOTCALL output,
                @GROUP_DONOTEMAIL output,
                null,
                null,
                null,
                null,
                null,
                null,
                null,
                null,
                @SITEID output,
                @CONSTITUENTSECURITYGROUPID output,
                @CONSTITUENCYCODEID output,
                @INFOSOURCECODEID output,
                @SPOUSE_CLASSOF output,
                @NAMEFORMATS output,
                @ORIGINAL_KEYNAME output,
                @ORIGINAL_FIRSTNAME output,
                @GENDERCODEID output,
                @SPOUSE_GENDERCODEID output;

        if @LASTNAME is null
        begin
            if len(@KEYNAMEPREFIX) > 0
                set @LASTNAME = @KEYNAMEPREFIX + case @KEYNAMEPREFIX
                        when ''
                            then ''
                        else '\'
                        end + @KEYNAME;
            else
                set @LASTNAME = @KEYNAME;
        end

        if @BUSINESS_NAME is null
        begin
            if len(@BUSINESS_KEYNAMEPREFIX) > 0
                set @BUSINESS_NAME = @BUSINESS_KEYNAMEPREFIX + case @BUSINESS_KEYNAMEPREFIX
                        when ''
                            then ''
                        else '\'
                        end + @BUSINESS_KEYNAME;
            else
                set @BUSINESS_NAME = @BUSINESS_KEYNAME;
        end

        if @LASTNAME is not null
            set @DATALOADED = -1;

        exec @DEFAULTCOUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT;

        if @ISORGANIZATION = 1
        begin
            set @ORGANIZATIONNAME = @LASTNAME;
            set @ORGANIZATION_ADDRESSTYPECODEID = @ADDRESS_ADDRESSTYPECODEID;
            set @ORGANIZATION_DONOTMAIL = @ADDRESS_DONOTMAIL;
            set @ORGANIZATION_DONOTMAILREASONCODEID = @ADDRESS_DONOTMAILREASONCODEID;
            set @ORGANIZATION_COUNTRYID = @ADDRESS_COUNTRYID;
            set @ORGANIZATION_STATEID = @ADDRESS_STATEID;
            set @ORGANIZATION_ADDRESSBLOCK = @ADDRESS_ADDRESSBLOCK;
            set @ORGANIZATION_CITY = @ADDRESS_CITY;
            set @ORGANIZATION_POSTCODE = @ADDRESS_POSTCODE;
            set @ORGANIZATION_OMITFROMVALIDATION = @ADDRESS_OMITFROMVALIDATION;
            set @ORGANIZATION_CART = @ADDRESS_CART;
            set @ORGANIZATION_DPC = @ADDRESS_DPC;
            set @ORGANIZATION_LOT = @ADDRESS_LOT;
            set @ORGANIZATION_COUNTYCODEID = @ADDRESS_COUNTYCODEID;
            set @ORGANIZATION_CONGRESSIONALDISTRICTCODEID = @ADDRESS_CONGRESSIONALDISTRICTCODEID;
            set @ORGANIZATION_LASTVALIDATIONATTEMPTDATE = @ADDRESS_LASTVALIDATIONATTEMPTDATE;
            set @ORGANIZATION_VALIDATIONMESSAGE = @ADDRESS_VALIDATIONMESSAGE;
            set @ORGANIZATION_CERTIFICATIONDATA = @ADDRESS_CERTIFICATIONDATA;
            set @ORGANIZATION_PHONETYPECODEID = @PHONE_PHONETYPECODEID;
            set @ORGANIZATION_NUMBER = @PHONE_NUMBER;
            set @ORGANIZATION_EMAILADDRESSTYPECODEID = @EMAILADDRESS_EMAILADDRESSTYPECODEID;
            set @ORGANIZATION_EMAILADDRESS = @EMAILADDRESS_EMAILADDRESS;
        end;

        if @ISGROUP = 1
        begin
            set @GROUP_NAME = @LASTNAME;
            set @GROUP_ADDRESSTYPECODEID = @ADDRESS_ADDRESSTYPECODEID;
            set @GROUP_COUNTRYID = @ADDRESS_COUNTRYID;
            set @GROUP_STATEID = @ADDRESS_STATEID;
            set @GROUP_ADDRESSBLOCK = @ADDRESS_ADDRESSBLOCK;
            set @GROUP_CITY = @ADDRESS_CITY;
            set @GROUP_POSTCODE = @ADDRESS_POSTCODE;
            set @GROUP_OMITFROMVALIDATION = @ADDRESS_OMITFROMVALIDATION;
            set @GROUP_CART = @ADDRESS_CART;
            set @GROUP_DPC = @ADDRESS_DPC;
            set @GROUP_LOT = @ADDRESS_LOT;
            set @GROUP_COUNTYCODEID = @ADDRESS_COUNTYCODEID;
            set @GROUP_CONGRESSIONALDISTRICTCODEID = @ADDRESS_CONGRESSIONALDISTRICTCODEID;
            set @GROUP_LASTVALIDATIONATTEMPTDATE = @ADDRESS_LASTVALIDATIONATTEMPTDATE;
            set @GROUP_VALIDATIONMESSAGE = @ADDRESS_VALIDATIONMESSAGE;
            set @GROUP_CERTIFICATIONDATA = @ADDRESS_CERTIFICATIONDATA;
            set @GROUP_PHONETYPECODEID = @PHONE_PHONETYPECODEID;
            set @GROUP_NUMBER = @PHONE_NUMBER;
            set @GROUP_EMAILADDRESSTYPECODEID = @EMAILADDRESS_EMAILADDRESSTYPECODEID;
            set @GROUP_EMAILADDRESS = @EMAILADDRESS_EMAILADDRESS;
            set @GROUP_WEBADDRESS = @WEBADDRESS;
            set @GROUP_DONOTMAIL = @ADDRESS_DONOTMAIL;
            set @GROUP_DONOTMAILREASONCODEID = @ADDRESS_DONOTMAILREASONCODEID;
            set @GROUP_DONOTCALL = @GROUP_DONOTCALL;
            set @GROUP_DONOTEMAIL = @GROUP_DONOTEMAIL;
        end

        select @ZIPLOOKUPCOUNTRIES = dbo.UFN_ZIPCITYSTATE_GETCOUNTRIES_TOITEMLISTXML();

        select @VALIDATIONCOUNTRIES = dbo.UFN_COUNTRY_GETVALIDATIONCOUNTRIES_TOITEMLISTXML();
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;
end;