USP_BATCHREVENUECONSTITUENT_LOAD

Loads a batch constituent record with spouse and business information.

Parameters

Parameter Parameter Type Mode Description
@ID uniqueidentifier IN
@CURRENTAPPUSERID uniqueidentifier INOUT
@ISORGANIZATION bit INOUT
@KEYNAME nvarchar(100) INOUT
@KEYNAMEPREFIX nvarchar(50) INOUT
@FIRSTNAME nvarchar(50) INOUT
@MIDDLENAME nvarchar(50) INOUT
@MAIDENNAME nvarchar(100) INOUT
@NICKNAME nvarchar(50) INOUT
@TITLECODEID uniqueidentifier INOUT
@SUFFIXCODEID uniqueidentifier INOUT
@GENDERCODE tinyint INOUT
@BIRTHDATE UDT_FUZZYDATE INOUT
@ADDRESSTYPECODEID uniqueidentifier INOUT
@DONOTMAIL bit INOUT
@COUNTRYID uniqueidentifier INOUT
@STATEID uniqueidentifier INOUT
@ADDRESSBLOCK nvarchar(150) INOUT
@CITY nvarchar(50) INOUT
@POSTCODE nvarchar(12) INOUT
@OMITFROMVALIDATION bit INOUT
@CART nvarchar(10) INOUT
@DPC nvarchar(8) INOUT
@LOT nvarchar(5) INOUT
@COUNTYCODEID uniqueidentifier INOUT
@CONGRESSIONALDISTRICTCODEID uniqueidentifier INOUT
@LASTVALIDATIONATTEMPTDATE datetime INOUT
@VALIDATIONMESSAGE nvarchar(200) INOUT
@CERTIFICATIONDATA int INOUT
@PHONETYPECODEID uniqueidentifier INOUT
@NUMBER nvarchar(100) INOUT
@EMAILADDRESSTYPECODEID uniqueidentifier INOUT
@EMAILADDRESS UDT_EMAILADDRESS INOUT
@MARITALSTATUSCODEID uniqueidentifier INOUT
@WEBADDRESS UDT_WEBADDRESS INOUT
@INDUSTRYCODEID uniqueidentifier INOUT
@NUMEMPLOYEES int INOUT
@NUMSUBSIDIARIES int INOUT
@PARENTCORPID uniqueidentifier INOUT
@BATCHSPOUSEID uniqueidentifier INOUT
@BATCHSPOUSERELATIONID uniqueidentifier INOUT
@SPOUSEID uniqueidentifier INOUT
@EXISTINGSPOUSE bit INOUT
@SPOUSE_LASTNAME nvarchar(100) INOUT
@SPOUSE_FIRSTNAME nvarchar(50) INOUT
@SPOUSE_MIDDLENAME nvarchar(50) INOUT
@SPOUSE_MAIDENNAME nvarchar(100) INOUT
@SPOUSE_NICKNAME nvarchar(50) INOUT
@SPOUSE_TITLECODEID uniqueidentifier INOUT
@SPOUSE_SUFFIXCODEID uniqueidentifier INOUT
@SPOUSE_GENDERCODE tinyint INOUT
@SPOUSE_BIRTHDATE UDT_FUZZYDATE INOUT
@SPOUSE_RECIPROCALTYPECODEID uniqueidentifier INOUT
@SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier INOUT
@COPYPRIMARYINFORMATION bit INOUT
@SPOUSE_STARTDATE datetime INOUT
@PRIMARYRELATIONSHIPEXISTS bit INOUT
@PRIMARYMATCHFACTOR decimal(5, 2) INOUT
@RECIPROCALRELATIONSHIPEXISTS bit INOUT
@RECIPROCALMATCHFACTOR decimal(5, 2) INOUT
@BATCHORGANIZATIONID uniqueidentifier INOUT
@BATCHORGANIZATIONRELATIONID uniqueidentifier INOUT
@ORGANIZATIONID uniqueidentifier INOUT
@EXISTINGORGANIZATION bit INOUT
@ORGANIZATION_NAME nvarchar(100) INOUT
@ORGANIZATION_KEYNAMEPREFIX nvarchar(50) INOUT
@ORGANIZATION_ADDRESSTYPECODEID uniqueidentifier INOUT
@ORGANIZATION_COUNTRYID uniqueidentifier INOUT
@ORGANIZATION_STATEID uniqueidentifier INOUT
@ORGANIZATION_ADDRESSBLOCK nvarchar(150) INOUT
@ORGANIZATION_CITY nvarchar(50) INOUT
@ORGANIZATION_POSTCODE nvarchar(12) INOUT
@ORGANIZATION_DONOTMAIL bit INOUT
@ORGANIZATION_OMITFROMVALIDATION bit INOUT
@ORGANIZATION_CART nvarchar(10) INOUT
@ORGANIZATION_DPC nvarchar(8) INOUT
@ORGANIZATION_LOT nvarchar(5) INOUT
@ORGANIZATION_COUNTYCODEID uniqueidentifier INOUT
@ORGANIZATION_CONGRESSIONALDISTRICTCODEID uniqueidentifier INOUT
@ORGANIZATION_LASTVALIDATIONATTEMPTDATE datetime INOUT
@ORGANIZATION_VALIDATIONMESSAGE nvarchar(200) INOUT
@ORGANIZATION_CERTIFICATIONDATA int INOUT
@ORGANIZATION_PHONETYPECODEID uniqueidentifier INOUT
@ORGANIZATION_NUMBER nvarchar(100) INOUT
@ORGANIZATION_RECIPROCALTYPECODEID uniqueidentifier INOUT
@ORGANIZATION_RELATIONSHIPTYPECODEID uniqueidentifier INOUT
@ORGANIZATION_STARTDATE datetime INOUT
@ISCONTACT bit INOUT
@ISPRIMARYCONTACT bit INOUT
@CONTACTTYPECODEID uniqueidentifier INOUT
@POSITION nvarchar(100) INOUT
@ISMATCHINGGIFTRELATIONSHIP bit INOUT
@ISGROUP bit INOUT
@ISHOUSEHOLD bit INOUT
@GIVESANONYMOUSLY bit INOUT
@GROUPTYPECODE tinyint INOUT
@GROUPTYPEID uniqueidentifier INOUT
@GROUPDESCRIPTION nvarchar(300) INOUT
@GROUPSTARTDATE datetime INOUT
@GROUPPRIMARYCONTACTID uniqueidentifier INOUT
@GROUPMEMBERS xml INOUT
@NAMEFORMATFUNCTIONID uniqueidentifier INOUT
@PRIMARYCONTACTID uniqueidentifier INOUT
@PRIMARYCONTACT_KEYNAME nvarchar(100) INOUT
@PRIMARYCONTACT_FIRSTNAME nvarchar(50) INOUT
@PRIMARYCONTACT_MIDDLENAME nvarchar(50) INOUT
@PRIMARYCONTACT_TITLECODEID uniqueidentifier INOUT
@PRIMARYCONTACT_SUFFIXCODEID uniqueidentifier INOUT
@PRIMARYCONTACT_COPYHOUSEHOLDCONTACT bit INOUT
@SECONDMEMBERID uniqueidentifier INOUT
@SECONDMEMBER_KEYNAME nvarchar(100) INOUT
@SECONDMEMBER_FIRSTNAME nvarchar(50) INOUT
@SECONDMEMBER_MIDDLENAME nvarchar(50) INOUT
@SECONDMEMBER_TITLECODEID uniqueidentifier INOUT
@SECONDMEMBER_SUFFIXCODEID uniqueidentifier INOUT
@SECONDMEMBER_COPYHOUSEHOLDCONTACT bit INOUT
@SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID uniqueidentifier INOUT
@SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID uniqueidentifier INOUT
@SECONDMEMBER_RELATIONSHIP_STARTDATE datetime INOUT
@SECONDMEMBER_RELATIONSHIP_ISSPOUSE bit INOUT
@BATCHHOUSEHOLDID uniqueidentifier INOUT
@HOUSEHOLDID uniqueidentifier INOUT
@HOUSEHOLD_NAME nvarchar(100) INOUT
@ADDSPOUSETOHOUSEHOLD bit INOUT
@HOUSEHOLDCOPYPRIMARYCONTACTINFO bit INOUT
@ISSPOUSERELATIONSHIP bit INOUT
@DONOTMAILREASONCODEID uniqueidentifier INOUT
@ORGANIZATION_DONOTMAILREASONCODEID uniqueidentifier INOUT
@JOBCATEGORYCODEID uniqueidentifier INOUT
@CAREERLEVELCODEID uniqueidentifier INOUT
@DONOTCALL bit INOUT
@DONOTEMAIL bit INOUT
@PRIMARYRECOGNITIONTYPECODEID uniqueidentifier INOUT
@RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier INOUT
@ORGANIZATION_PRIMARYRELATIONSHIPEXISTS bit INOUT
@ORGANIZATION_PRIMARYMATCHFACTOR decimal(5, 2) INOUT
@ORGANIZATION_RECIPROCALRELATIONSHIPEXISTS bit INOUT
@ORGANIZATION_RECIPROCALMATCHFACTOR decimal(5, 2) INOUT
@ORGANIZATION_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier INOUT
@ORGANIZATION_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier INOUT
@SITEID uniqueidentifier INOUT
@CONSTITUENTSECURITYGROUPID uniqueidentifier INOUT
@CONSTITUENCYCODEID uniqueidentifier INOUT
@INFOSOURCECODEID uniqueidentifier INOUT
@SPOUSE_CLASSOF UDT_YEAR INOUT
@NAMEFORMATS xml INOUT
@ORIGINAL_KEYNAME nvarchar(100) INOUT
@ORIGINAL_FIRSTNAME nvarchar(50) INOUT
@GENDERCODEID uniqueidentifier INOUT
@SPOUSE_GENDERCODEID uniqueidentifier INOUT

Definition

Copy 


    CREATE procedure dbo.USP_BATCHREVENUECONSTITUENT_LOAD
      (
        @ID                                        uniqueidentifier,
        @CURRENTAPPUSERID                        uniqueidentifier = null output,
        @ISORGANIZATION                            bit = null output,
        @KEYNAME                                nvarchar(100) = null output,
        @KEYNAMEPREFIX                            nvarchar(50) = null output,
        @FIRSTNAME                                nvarchar(50) = null output,
        @MIDDLENAME                                nvarchar(50) = null output,
        @MAIDENNAME                                nvarchar(100) = null output,
        @NICKNAME                                nvarchar(50) = null output,
        @TITLECODEID                            uniqueidentifier = null output,
        @SUFFIXCODEID                            uniqueidentifier = null output,
        @GENDERCODE                                tinyint = null output,
        @BIRTHDATE                                dbo.UDT_FUZZYDATE = null output,
        @ADDRESSTYPECODEID                        uniqueidentifier = null output,
        @DONOTMAIL                                bit = null output,
        @COUNTRYID                                uniqueidentifier = null output,
        @STATEID                                uniqueidentifier = null output,
        @ADDRESSBLOCK                            nvarchar(150) = null output,
        @CITY                                    nvarchar(50) = null output,
        @POSTCODE                                nvarchar(12) = null output,

        -- Address Validation

        @OMITFROMVALIDATION                        bit = null output,
        @CART                                    nvarchar(10) = null output,
        @DPC                                    nvarchar(8) = null output,
        @LOT                                    nvarchar(5) = null output,
        @COUNTYCODEID                            uniqueidentifier = null output,
        @CONGRESSIONALDISTRICTCODEID            uniqueidentifier = null output,
        @LASTVALIDATIONATTEMPTDATE                datetime = null output,
        @VALIDATIONMESSAGE                        nvarchar(200) = null output,
        @CERTIFICATIONDATA                        integer = null output,

        @PHONETYPECODEID                        uniqueidentifier = null output,
        @NUMBER                                    nvarchar(100) = null output,
        @EMAILADDRESSTYPECODEID                    uniqueidentifier = null output,
        @EMAILADDRESS                            dbo.UDT_EMAILADDRESS = null output,
        @MARITALSTATUSCODEID                    uniqueidentifier = null output,
        @WEBADDRESS                                dbo.UDT_WEBADDRESS = null output,
        @INDUSTRYCODEID                            uniqueidentifier = null output,
        @NUMEMPLOYEES                            int = null output,
        @NUMSUBSIDIARIES                        int = null output,
        @PARENTCORPID                            uniqueidentifier = null output,

        --Spouse Variables

        @BATCHSPOUSEID                    uniqueidentifier = null output,
        @BATCHSPOUSERELATIONID    uniqueidentifier = null output,
        @SPOUSEID                                uniqueidentifier = null output,
        @EXISTINGSPOUSE                            bit = null output,
        @SPOUSE_LASTNAME                        nvarchar(100) = null output,
        @SPOUSE_FIRSTNAME                        nvarchar(50) = null output,
        @SPOUSE_MIDDLENAME                        nvarchar(50) = null output,
        @SPOUSE_MAIDENNAME                        nvarchar(100) = null output,
        @SPOUSE_NICKNAME                        nvarchar(50) = null output,
        @SPOUSE_TITLECODEID                        uniqueidentifier = null output,
        @SPOUSE_SUFFIXCODEID                    uniqueidentifier = null output,
        @SPOUSE_GENDERCODE                        tinyint = null output,
        @SPOUSE_BIRTHDATE                        dbo.UDT_FUZZYDATE = null output,
        @SPOUSE_RECIPROCALTYPECODEID            uniqueidentifier = null output,
        @SPOUSE_RELATIONSHIPTYPECODEID            uniqueidentifier = null output,
        @COPYPRIMARYINFORMATION                    bit = null output,
        @SPOUSE_STARTDATE                        datetime = null output,
        @PRIMARYRELATIONSHIPEXISTS    bit = null output,
        @PRIMARYMATCHFACTOR            decimal(5,2) = null output,
        @RECIPROCALRELATIONSHIPEXISTS bit = null output,
        @RECIPROCALMATCHFACTOR        decimal(5,2) = null output,

        --Organization variables

        @BATCHORGANIZATIONID                uniqueidentifier = null output,
        @BATCHORGANIZATIONRELATIONID uniqueidentifier = null output,
        @ORGANIZATIONID                            uniqueidentifier = null output,
        @EXISTINGORGANIZATION                    bit = null output,
        @ORGANIZATION_NAME                        nvarchar(100) = null output,
        @ORGANIZATION_KEYNAMEPREFIX                nvarchar(50) = null output,
        @ORGANIZATION_ADDRESSTYPECODEID            uniqueidentifier = null output,
        @ORGANIZATION_COUNTRYID                    uniqueidentifier = null output,
        @ORGANIZATION_STATEID                    uniqueidentifier = null output,
        @ORGANIZATION_ADDRESSBLOCK                nvarchar(150) = null output,
        @ORGANIZATION_CITY                        nvarchar(50) = null output,
        @ORGANIZATION_POSTCODE                    nvarchar(12) = null output,
        @ORGANIZATION_DONOTMAIL                    bit = null output,

        -- Address Validation

        @ORGANIZATION_OMITFROMVALIDATION            bit = null output,
        @ORGANIZATION_CART                            nvarchar(10) = null output,
        @ORGANIZATION_DPC                            nvarchar(8) = null output,
        @ORGANIZATION_LOT                            nvarchar(5) = null output,
        @ORGANIZATION_COUNTYCODEID                    uniqueidentifier = null output,
        @ORGANIZATION_CONGRESSIONALDISTRICTCODEID    uniqueidentifier = null output,
        @ORGANIZATION_LASTVALIDATIONATTEMPTDATE        datetime = null output,
        @ORGANIZATION_VALIDATIONMESSAGE                nvarchar(200) = null output,
        @ORGANIZATION_CERTIFICATIONDATA                integer = null output,

        @ORGANIZATION_PHONETYPECODEID            uniqueidentifier = null output,
        @ORGANIZATION_NUMBER                    nvarchar(100) = null output,
        @ORGANIZATION_RECIPROCALTYPECODEID        uniqueidentifier = null output,
        @ORGANIZATION_RELATIONSHIPTYPECODEID    uniqueidentifier = null output,
        @ORGANIZATION_STARTDATE                    datetime = null output,
        @ISCONTACT                                bit = null output,
        @ISPRIMARYCONTACT                        bit = null output,
        @CONTACTTYPECODEID                        uniqueidentifier = null output,
        @POSITION                                nvarchar(100) = null output,
        @ISMATCHINGGIFTRELATIONSHIP                bit = null output,

        -- Group variables

        @ISGROUP                                bit = null output,
        @ISHOUSEHOLD                            bit = null output,
        @GIVESANONYMOUSLY                        bit = null output,
        @GROUPTYPECODE                            tinyint = null output,
        @GROUPTYPEID                            uniqueidentifier = null output,
        @GROUPDESCRIPTION                        nvarchar(300) = null output,
        @GROUPSTARTDATE                            datetime = null output,
        @GROUPPRIMARYCONTACTID                    uniqueidentifier = null output,
        @GROUPMEMBERS                            xml = null output,
        @NAMEFORMATFUNCTIONID                    uniqueidentifier = null output,

        -- Household Primary Member variables

        @PRIMARYCONTACTID                        uniqueidentifier = null output,
        @PRIMARYCONTACT_KEYNAME                    nvarchar(100) = null output,
        @PRIMARYCONTACT_FIRSTNAME                nvarchar(50) = null output,
        @PRIMARYCONTACT_MIDDLENAME                nvarchar(50) = null output,
        @PRIMARYCONTACT_TITLECODEID                uniqueidentifier = null output,
        @PRIMARYCONTACT_SUFFIXCODEID            uniqueidentifier = null output,
        @PRIMARYCONTACT_COPYHOUSEHOLDCONTACT    bit = null output,

        -- Household Second Member variables

        @SECONDMEMBERID                            uniqueidentifier = null output,
        @SECONDMEMBER_KEYNAME                    nvarchar(100) = null output,
        @SECONDMEMBER_FIRSTNAME                    nvarchar(50) = null output,
        @SECONDMEMBER_MIDDLENAME                nvarchar(50) = null output,
        @SECONDMEMBER_TITLECODEID                uniqueidentifier = null output,
        @SECONDMEMBER_SUFFIXCODEID                uniqueidentifier = null output,
        @SECONDMEMBER_COPYHOUSEHOLDCONTACT        bit = 0 output,

        -- Household Second Member Relationship variables

        @SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID        uniqueidentifier = null output,
        @SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID    uniqueidentifier = null output,
        @SECONDMEMBER_RELATIONSHIP_STARTDATE                datetime = null output,
        @SECONDMEMBER_RELATIONSHIP_ISSPOUSE                    bit = null output,

        -- Individual's household variables

        @BATCHHOUSEHOLDID                        uniqueidentifier = null output,
        @HOUSEHOLDID                            uniqueidentifier = null output,
        @HOUSEHOLD_NAME                            nvarchar(100) = null output,
        @ADDSPOUSETOHOUSEHOLD                    bit = null output,
        @HOUSEHOLDCOPYPRIMARYCONTACTINFO        bit = null output,

        -- Individual's relationship variables

        @ISSPOUSERELATIONSHIP                    bit = null output,

                        @DONOTMAILREASONCODEID     uniqueidentifier = null output,
                        @ORGANIZATION_DONOTMAILREASONCODEID uniqueidentifier = null output,
                        @JOBCATEGORYCODEID uniqueidentifier = null output,
                        @CAREERLEVELCODEID uniqueidentifier = null output,
                        @DONOTCALL bit = 0 output,
                        @DONOTEMAIL bit = 0 output,

                        @PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null output,
                        @RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null output,
                        @ORGANIZATION_PRIMARYRELATIONSHIPEXISTS    bit = null output,
                        @ORGANIZATION_PRIMARYMATCHFACTOR            decimal(5,2) = null output,
                        @ORGANIZATION_RECIPROCALRELATIONSHIPEXISTS bit = null output,
                        @ORGANIZATION_RECIPROCALMATCHFACTOR        decimal(5,2) = null output,
                        @ORGANIZATION_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null output,
                        @ORGANIZATION_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null output,

                        @SITEID uniqueidentifier = null output,
                        @CONSTITUENTSECURITYGROUPID uniqueidentifier = null output,
                        @CONSTITUENCYCODEID uniqueidentifier = null output,
                        @INFOSOURCECODEID uniqueidentifier = null output,
                        @SPOUSE_CLASSOF dbo.UDT_YEAR = null output,
                        @NAMEFORMATS xml = 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

                        select @ISORGANIZATION=ISORGANIZATION,
                            @KEYNAME = KEYNAME,
                            @KEYNAMEPREFIX = KEYNAMEPREFIX, 
                            @FIRSTNAME = FIRSTNAME, 
                            @MIDDLENAME = MIDDLENAME, 
                            @MAIDENNAME = MAIDENNAME,
                            @ORIGINAL_KEYNAME = ORIGINAL_LASTNAME,
                            @ORIGINAL_FIRSTNAME = ORIGINAL_FIRSTNAME,
                            @NICKNAME=NICKNAME, 
                            @TITLECODEID=TITLECODEID, 
                            @SUFFIXCODEID=SUFFIXCODEID, 
                            @GENDERCODE=GENDERCODE,
                            @BIRTHDATE=BIRTHDATE,
                            @ADDRESSTYPECODEID=ADDRESSTYPECODEID, 
                            @DONOTMAIL=DONOTMAIL, 
                            @DONOTMAILREASONCODEID = DONOTMAILREASONCODEID,
                            @COUNTRYID=COUNTRYID, 
                            @STATEID=STATEID, 
                            @ADDRESSBLOCK=ADDRESSBLOCK, 
                            @CITY=CITY, 
                            @POSTCODE=POSTCODE,                     
                            @OMITFROMVALIDATION = OMITFROMVALIDATION,
                            @CART = CART,
                            @DPC = DPC,
                            @LOT = LOT,
                            @COUNTYCODEID = COUNTYCODEID,
                            @CONGRESSIONALDISTRICTCODEID = CONGRESSIONALDISTRICTCODEID,
                            @LASTVALIDATIONATTEMPTDATE = LASTVALIDATIONATTEMPTDATE,
                            @VALIDATIONMESSAGE = VALIDATIONMESSAGE,
                            @CERTIFICATIONDATA = CERTIFICATIONDATA, 
                            @PHONETYPECODEID=PHONETYPECODEID, 
                            @NUMBER=NUMBER,
                            @DONOTCALL=DONOTCALL,
                            @EMAILADDRESSTYPECODEID=EMAILADDRESSTYPECODEID,
                            @EMAILADDRESS=EMAILADDRESS,
                            @DONOTEMAIL=DONOTEMAIL,
                            @MARITALSTATUSCODEID=MARITALSTATUSCODEID,
                            @CURRENTAPPUSERID=CURRENTAPPUSERID,
                            @WEBADDRESS=WEBADDRESS,
                            @INDUSTRYCODEID=INDUSTRYCODEID,
                            @NUMEMPLOYEES=NUMEMPLOYEES,
                            @NUMSUBSIDIARIES=NUMSUBSIDIARIES,
                            @PARENTCORPID=PARENTCORPID,
                            @ISGROUP=ISGROUP,
                            @ISHOUSEHOLD = case when GROUPTYPECODE = 0 then 1 else 0 end,
                            @GIVESANONYMOUSLY=GIVESANONYMOUSLY,
                            @GROUPTYPECODE=GROUPTYPECODE,
                            @GROUPTYPEID=GROUPTYPEID,
                            @GROUPDESCRIPTION=GROUPDESCRIPTION,
                            @GROUPSTARTDATE=GROUPSTARTDATE,
                            @NAMEFORMATFUNCTIONID=NAMEFORMATFUNCTIONID,
                            @SITEID = SITEID,
                            @CONSTITUENTSECURITYGROUPID = CONSTITUENTSECURITYGROUPID,
                            @CONSTITUENCYCODEID = CONSTITUENCYCODEID,
                            @INFOSOURCECODEID = INFOSOURCECODEID,
                            @NAMEFORMATS = NAMEFORMATS,
                            @GENDERCODEID = GENDERCODEID
                            from dbo.BATCHREVENUECONSTITUENT where ID = @ID

                        -- Load individual's relationship and group

                        if @ISORGANIZATION = 0 and @ISGROUP = 0
                        begin
                            select
                                @BATCHSPOUSERELATIONID=BATCHREVENUECONSTITUENTRELATION.ID,
                                @BATCHSPOUSEID=BATCHREVENUECONSTITUENTRELATION.RELATIONID,
                                @SPOUSE_RECIPROCALTYPECODEID=BATCHREVENUECONSTITUENTRELATION.RECIPROCALTYPECODEID,
                                @SPOUSE_RELATIONSHIPTYPECODEID=BATCHREVENUECONSTITUENTRELATION.RELATIONSHIPTYPECODEID,
                                @COPYPRIMARYINFORMATION=BATCHREVENUECONSTITUENTRELATION.COPYPRIMARYINFORMATION,
                                @SPOUSE_STARTDATE=BATCHREVENUECONSTITUENTRELATION.STARTDATE,
                                @ISSPOUSERELATIONSHIP=BATCHREVENUECONSTITUENTRELATION.ISSPOUSE,
                                @PRIMARYRELATIONSHIPEXISTS=BATCHREVENUECONSTITUENTRELATION.PRIMARYRELATIONSHIPEXISTS,
                                @PRIMARYMATCHFACTOR=BATCHREVENUECONSTITUENTRELATION.PRIMARYMATCHFACTOR,
                                @RECIPROCALRELATIONSHIPEXISTS=BATCHREVENUECONSTITUENTRELATION.RECIPROCALRELATIONSHIPEXISTS,
                                @RECIPROCALMATCHFACTOR=BATCHREVENUECONSTITUENTRELATION.RECIPROCALMATCHFACTOR,
                                @PRIMARYRECOGNITIONTYPECODEID=BATCHREVENUECONSTITUENTRELATION.PRIMARYRECOGNITIONTYPECODEID,
                                @RECIPROCALRECOGNITIONTYPECODEID=BATCHREVENUECONSTITUENTRELATION.RECIPROCALRECOGNITIONTYPECODEID
                                from dbo.BATCHREVENUECONSTITUENTRELATION
                                inner join dbo.BATCHREVENUECONSTITUENT on BATCHREVENUECONSTITUENTRELATION.RELATIONID = BATCHREVENUECONSTITUENT.ID
                                where BATCHREVENUECONSTITUENTRELATION.CONSTITUENTID = @ID and BATCHREVENUECONSTITUENT.ISORGANIZATION = 0

                            if @BATCHSPOUSEID is not null
                                select @SPOUSEID=BATCHREVENUECONSTITUENT.EXISTINGCONSTITUENTID,
                                    @SPOUSE_LASTNAME=BATCHREVENUECONSTITUENT.KEYNAME,
                                    @SPOUSE_FIRSTNAME=BATCHREVENUECONSTITUENT.FIRSTNAME,
                                    @SPOUSE_MIDDLENAME=BATCHREVENUECONSTITUENT.MIDDLENAME,
                                    @SPOUSE_MAIDENNAME=BATCHREVENUECONSTITUENT.MAIDENNAME,
                                    @SPOUSE_NICKNAME=BATCHREVENUECONSTITUENT.NICKNAME,
                                    @SPOUSE_TITLECODEID=BATCHREVENUECONSTITUENT.TITLECODEID,
                                    @SPOUSE_SUFFIXCODEID=BATCHREVENUECONSTITUENT.SUFFIXCODEID,
                                    @SPOUSE_GENDERCODE=BATCHREVENUECONSTITUENT.GENDERCODE,
                                    @SPOUSE_BIRTHDATE=BATCHREVENUECONSTITUENT.BIRTHDATE,
                                    @SPOUSE_CLASSOF = BATCHREVENUECONSTITUENT.CLASSOF,
                                    @SPOUSE_GENDERCODEID=BATCHREVENUECONSTITUENT.GENDERCODEID
                                    from dbo.BATCHREVENUECONSTITUENT where BATCHREVENUECONSTITUENT.ID = @BATCHSPOUSEID

                            if @SPOUSEID is null and @SPOUSE_LASTNAME != ''
                                set @EXISTINGSPOUSE = 0
                            else
                                set @EXISTINGSPOUSE = 1                        

                            select 
                                @BATCHORGANIZATIONRELATIONID=BATCHREVENUECONSTITUENTRELATION.ID,
                                @BATCHORGANIZATIONID=BATCHREVENUECONSTITUENTRELATION.RELATIONID,
                                @ORGANIZATION_RECIPROCALTYPECODEID=BATCHREVENUECONSTITUENTRELATION.RECIPROCALTYPECODEID,
                                @ORGANIZATION_RELATIONSHIPTYPECODEID=BATCHREVENUECONSTITUENTRELATION.RELATIONSHIPTYPECODEID,
                                @ORGANIZATION_STARTDATE=BATCHREVENUECONSTITUENTRELATION.STARTDATE,
                                @ISCONTACT=BATCHREVENUECONSTITUENTRELATION.ISCONTACT,
                                @ISPRIMARYCONTACT=BATCHREVENUECONSTITUENTRELATION.ISPRIMARYCONTACT,
                                @CONTACTTYPECODEID=BATCHREVENUECONSTITUENTRELATION.CONTACTTYPECODEID,
                                @POSITION=BATCHREVENUECONSTITUENTRELATION.POSITION,
                                @ISMATCHINGGIFTRELATIONSHIP=BATCHREVENUECONSTITUENTRELATION.ISMATCHINGGIFTRELATIONSHIP,
                                @JOBCATEGORYCODEID=BATCHREVENUECONSTITUENTRELATION.JOBCATEGORYCODEID,
                                @CAREERLEVELCODEID=BATCHREVENUECONSTITUENTRELATION.CAREERLEVELCODEID,
                @ORGANIZATION_PRIMARYRELATIONSHIPEXISTS=BATCHREVENUECONSTITUENTRELATION.PRIMARYRELATIONSHIPEXISTS,
                            @ORGANIZATION_PRIMARYMATCHFACTOR=BATCHREVENUECONSTITUENTRELATION.PRIMARYMATCHFACTOR,
                            @ORGANIZATION_RECIPROCALRELATIONSHIPEXISTS=BATCHREVENUECONSTITUENTRELATION.RECIPROCALRELATIONSHIPEXISTS,
                            @ORGANIZATION_RECIPROCALMATCHFACTOR=BATCHREVENUECONSTITUENTRELATION.RECIPROCALMATCHFACTOR,
                @ORGANIZATION_PRIMARYRECOGNITIONTYPECODEID=BATCHREVENUECONSTITUENTRELATION.PRIMARYRECOGNITIONTYPECODEID,
                @ORGANIZATION_RECIPROCALRECOGNITIONTYPECODEID=BATCHREVENUECONSTITUENTRELATION.RECIPROCALRECOGNITIONTYPECODEID                        
                                from dbo.BATCHREVENUECONSTITUENTRELATION where BATCHREVENUECONSTITUENTRELATION.CONSTITUENTID = @ID and BATCHREVENUECONSTITUENTRELATION.ISPRIMARYBUSINESS = 1

                            if @BATCHORGANIZATIONID is not null
                                select @ORGANIZATIONID=BATCHREVENUECONSTITUENT.EXISTINGCONSTITUENTID, 
                                    @ORGANIZATION_NAME=BATCHREVENUECONSTITUENT.KEYNAME,
                                    @ORGANIZATION_KEYNAMEPREFIX=BATCHREVENUECONSTITUENT.KEYNAMEPREFIX,
                                    @ORGANIZATION_ADDRESSTYPECODEID=BATCHREVENUECONSTITUENT.ADDRESSTYPECODEID, 
                                    @ORGANIZATION_COUNTRYID=BATCHREVENUECONSTITUENT.COUNTRYID, 
                                    @ORGANIZATION_STATEID=BATCHREVENUECONSTITUENT.STATEID, 
                                    @ORGANIZATION_ADDRESSBLOCK=BATCHREVENUECONSTITUENT.ADDRESSBLOCK, 
                                    @ORGANIZATION_CITY=BATCHREVENUECONSTITUENT.CITY, 
                                    @ORGANIZATION_POSTCODE=BATCHREVENUECONSTITUENT.POSTCODE,
                                    @ORGANIZATION_DONOTMAIL=BATCHREVENUECONSTITUENT.DONOTMAIL,
                  @ORGANIZATION_DONOTMAILREASONCODEID=BATCHREVENUECONSTITUENT.DONOTMAILREASONCODEID,

                                    @ORGANIZATION_OMITFROMVALIDATION = BATCHREVENUECONSTITUENT.OMITFROMVALIDATION,
                                    @ORGANIZATION_CART = BATCHREVENUECONSTITUENT.CART,
                                    @ORGANIZATION_DPC = BATCHREVENUECONSTITUENT.DPC,
                                    @ORGANIZATION_LOT = BATCHREVENUECONSTITUENT.LOT,
                                    @ORGANIZATION_COUNTYCODEID = BATCHREVENUECONSTITUENT.COUNTYCODEID,
                                    @ORGANIZATION_CONGRESSIONALDISTRICTCODEID = BATCHREVENUECONSTITUENT.CONGRESSIONALDISTRICTCODEID,
                                    @ORGANIZATION_LASTVALIDATIONATTEMPTDATE = BATCHREVENUECONSTITUENT.LASTVALIDATIONATTEMPTDATE,
                                    @ORGANIZATION_VALIDATIONMESSAGE = BATCHREVENUECONSTITUENT.VALIDATIONMESSAGE,
                                    @ORGANIZATION_CERTIFICATIONDATA = BATCHREVENUECONSTITUENT.CERTIFICATIONDATA,

                                    @ORGANIZATION_PHONETYPECODEID=BATCHREVENUECONSTITUENT.PHONETYPECODEID, 
                                    @ORGANIZATION_NUMBER=BATCHREVENUECONSTITUENT.NUMBER                                
                                    from dbo.BATCHREVENUECONSTITUENT where BATCHREVENUECONSTITUENT.ID = @BATCHORGANIZATIONID

                            if @ORGANIZATIONID is null and @ORGANIZATION_NAME != ''
                                    set @EXISTINGORGANIZATION = 0;
                            else
                                set @EXISTINGORGANIZATION = 1;
                            end

                            if @ISGROUP = 1
                            begin
                                if @ISHOUSEHOLD = 0
                                begin
                                    select
                                        @GROUPPRIMARYCONTACTID = case when BRC.EXISTINGCONSTITUENTID is null then BRCGM.MEMBERID else BRC.EXISTINGCONSTITUENTID end
                                    from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER BRCGM
                                    inner join dbo.BATCHREVENUECONSTITUENT BRC on BRCGM.MEMBERID = BRC.ID
                                    where BRCGM.GROUPID = @ID and BRCGM.ISPRIMARY = 1

        -- Not using UFN_BATCHREVENUECONSTITUENT_GETGROUPMEMBERS_TOITEMLISTXML since the root element

        -- needed is different the function's.

        set @GROUPMEMBERS = (
                      select
                        [ID],
                        [NAME],
                        [MEMBERID],
                        [ISPRIMARY]
                      from dbo.[UFN_BATCHREVENUECONSTITUENT_GETGROUPMEMBERS](@ID)
                      for xml raw('ITEM'), type, elements, root('GROUP_MEMBERS'),binary base64
                    )
      end
      else
      begin
        declare @BATCHPRIMARYCONTACTID uniqueidentifier, @BATCHSECONDMEMBERID uniqueidentifier

        -- Load household's primary contact info

        select
          @BATCHPRIMARYCONTACTID = C.ID,
          @PRIMARYCONTACTID = C.EXISTINGCONSTITUENTID,
          @PRIMARYCONTACT_KEYNAME = case when C.EXISTINGCONSTITUENTID is null then C.KEYNAME else '' end,
          @PRIMARYCONTACT_FIRSTNAME = case when C.EXISTINGCONSTITUENTID is null then C.FIRSTNAME else '' end,
          @PRIMARYCONTACT_MIDDLENAME = case when C.EXISTINGCONSTITUENTID is null then C.MIDDLENAME else '' end,
          @PRIMARYCONTACT_TITLECODEID = case when C.EXISTINGCONSTITUENTID is null then C.TITLECODEID else null end,
          @PRIMARYCONTACT_SUFFIXCODEID = case when C.EXISTINGCONSTITUENTID is null then C.SUFFIXCODEID else null end,
          @PRIMARYCONTACT_COPYHOUSEHOLDCONTACT = case when C.EXISTINGCONSTITUENTID is null then GM.COPYGROUPCONTACTINFOTOMEMBER else '' end
        from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER GM
        inner join dbo.BATCHREVENUECONSTITUENT C on GM.MEMBERID = C.ID
        where
          GM.GROUPID = @ID and ISPRIMARY = 1

        -- Load household's secondary contact info

        select
          @BATCHSECONDMEMBERID = C.ID,
          @SECONDMEMBERID = C.EXISTINGCONSTITUENTID,
          @SECONDMEMBER_KEYNAME = case when C.EXISTINGCONSTITUENTID is null then C.KEYNAME else '' end,
          @SECONDMEMBER_FIRSTNAME = case when C.EXISTINGCONSTITUENTID is null then C.FIRSTNAME else '' end,
          @SECONDMEMBER_MIDDLENAME = case when C.EXISTINGCONSTITUENTID is null then C.MIDDLENAME else '' end,
          @SECONDMEMBER_TITLECODEID = case when C.EXISTINGCONSTITUENTID is null then C.TITLECODEID else null end,
          @SECONDMEMBER_SUFFIXCODEID = case when C.EXISTINGCONSTITUENTID is null then C.SUFFIXCODEID else null end,
          @SECONDMEMBER_COPYHOUSEHOLDCONTACT = case when C.EXISTINGCONSTITUENTID is null then GM.COPYGROUPCONTACTINFOTOMEMBER else '' end
        from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER GM
        inner join dbo.BATCHREVENUECONSTITUENT C on GM.MEMBERID = C.ID
        where
          GM.GROUPID = @ID and ISPRIMARY = 0

        -- Load second member relationship fields if both primary and secondary contacts are set

        if @BATCHPRIMARYCONTACTID is not null and @BATCHSECONDMEMBERID is not null
        begin
          select
            @SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID = R.RECIPROCALTYPECODEID,
            @SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID = R.RELATIONSHIPTYPECODEID,
            @SECONDMEMBER_RELATIONSHIP_STARTDATE = R.STARTDATE,
            @SECONDMEMBER_RELATIONSHIP_ISSPOUSE = R.ISSPOUSE
            from dbo.BATCHREVENUECONSTITUENTRELATION R
          where
            R.CONSTITUENTID = @BATCHPRIMARYCONTACTID and
            R.RELATIONID = @BATCHSECONDMEMBERID
        end
      end
    end

    set @ADDSPOUSETOHOUSEHOLD = 0
    if @ISGROUP = 0 and @ISORGANIZATION = 0
    begin
      -- Load individual's household fields

      select
        @BATCHHOUSEHOLDID = BRC.ID,
        @HOUSEHOLDID = BRC.EXISTINGCONSTITUENTID,
        @HOUSEHOLD_NAME = case when BRC.EXISTINGCONSTITUENTID is not null then C.NAME else BRC.NAME end,
        @HOUSEHOLDCOPYPRIMARYCONTACTINFO = BRCGM.COPYMEMBERCONTACTINFOTOGROUP
      from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER BRCGM
      inner join dbo.BATCHREVENUECONSTITUENT BRC on BRCGM.GROUPID = BRC.ID and BRC.GROUPTYPECODE = 0
      left join dbo.CONSTITUENT C on BRC.EXISTINGCONSTITUENTID = C.ID
      where BRCGM.MEMBERID = @ID

      -- Always assign group member site such that it matches the incoming household/group value

      select
        @SITEID = GRP.SITEID
      from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER GROUPMEMBER
      inner join dbo.BATCHREVENUECONSTITUENT GRP on GROUPMEMBER.GROUPID = GRP.ID
      where GROUPMEMBER.MEMBERID = @ID;

      if @BATCHHOUSEHOLDID is not null and @BATCHSPOUSEID is not null
      begin
        if exists (select 1 from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER where MEMBERID = @BATCHSPOUSEID and GROUPID = @BATCHHOUSEHOLDID)
          set @ADDSPOUSETOHOUSEHOLD = 1
      end
    end
  end try

  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;

end;