USP_AUCTIONITEMBATCH_CONSTITUENT_LOAD

Loads an auction item 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

Definition

Copy 


              CREATE procedure dbo.USP_AUCTIONITEMBATCH_CONSTITUENT_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
                    ) as begin

                        set nocount on;

                        begin try

                        select @ISORGANIZATION=ISORGANIZATION,
                            @KEYNAME = KEYNAME,
                            @KEYNAMEPREFIX = KEYNAMEPREFIX, 
                            @FIRSTNAME = FIRSTNAME, 
                            @MIDDLENAME = MIDDLENAME, 
                            @MAIDENNAME = MAIDENNAME, 
                            @NICKNAME=NICKNAME, 
                            @TITLECODEID=TITLECODEID, 
                            @SUFFIXCODEID=SUFFIXCODEID, 
                            @GENDERCODE=GENDERCODE,
                            @BIRTHDATE=BIRTHDATE,
                            @ADDRESSTYPECODEID=ADDRESSTYPECODEID, 
                            @DONOTMAIL=DONOTMAIL, 
                            @DONOTMAILREASONCODEID = DONOTMAILREASONCODEID,
                            @COUNTRYID=COUNTRYID, 
                            @STATEID=STATEID, 
                            @ADDRESSBLOCK=ADDRESSBLOCK, 
                            @CITY=CITY, 
                            @POSTCODE=POSTCODE,                     
                            @OMITFROMVALIDATION = OMITFROMVALIDATION,
                            @CART = CART,
                            @DPC = DPC,
                            @LOT = LOT,
                            @COUNTYCODEID = COUNTYCODEID,
                            @CONGRESSIONALDISTRICTCODEID = CONGRESSIONALDISTRICTCODEID,
                            @LASTVALIDATIONATTEMPTDATE = LASTVALIDATIONATTEMPTDATE,
                            @VALIDATIONMESSAGE = VALIDATIONMESSAGE,
                            @CERTIFICATIONDATA = CERTIFICATIONDATA, 
                            @PHONETYPECODEID=PHONETYPECODEID, 
                            @NUMBER=NUMBER,
                            @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
                            from dbo.BATCHAUCTIONITEMCONSTITUENT where ID = @ID

                        -- Load individual's relationship and group

                        if @ISORGANIZATION = 0 and @ISGROUP = 0
                        begin
                            select                             
                                @BATCHSPOUSERELATIONID=BATCHAUCTIONITEMCONSTITUENTRELATION.ID,
                                @BATCHSPOUSEID=BATCHAUCTIONITEMCONSTITUENTRELATION.RELATIONID,                            
                                @SPOUSE_RECIPROCALTYPECODEID=BATCHAUCTIONITEMCONSTITUENTRELATION.RECIPROCALTYPECODEID,
                                @SPOUSE_RELATIONSHIPTYPECODEID=BATCHAUCTIONITEMCONSTITUENTRELATION.RELATIONSHIPTYPECODEID,
                                @COPYPRIMARYINFORMATION=BATCHAUCTIONITEMCONSTITUENTRELATION.COPYPRIMARYINFORMATION,
                                @SPOUSE_STARTDATE=BATCHAUCTIONITEMCONSTITUENTRELATION.STARTDATE,
                                @ISSPOUSERELATIONSHIP=BATCHAUCTIONITEMCONSTITUENTRELATION.ISSPOUSE
                                from dbo.BATCHAUCTIONITEMCONSTITUENTRELATION
                                inner join dbo.BATCHAUCTIONITEMCONSTITUENT on BATCHAUCTIONITEMCONSTITUENTRELATION.RELATIONID = BATCHAUCTIONITEMCONSTITUENT.ID
                                where BATCHAUCTIONITEMCONSTITUENTRELATION.CONSTITUENTID = @ID and BATCHAUCTIONITEMCONSTITUENT.ISORGANIZATION = 0

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

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

                            select 
                                @BATCHORGANIZATIONRELATIONID=BATCHAUCTIONITEMCONSTITUENTRELATION.ID,
                                @BATCHORGANIZATIONID=BATCHAUCTIONITEMCONSTITUENTRELATION.RELATIONID,
                                @ORGANIZATION_RECIPROCALTYPECODEID=BATCHAUCTIONITEMCONSTITUENTRELATION.RECIPROCALTYPECODEID,
                                @ORGANIZATION_RELATIONSHIPTYPECODEID=BATCHAUCTIONITEMCONSTITUENTRELATION.RELATIONSHIPTYPECODEID,
                                @ORGANIZATION_STARTDATE=BATCHAUCTIONITEMCONSTITUENTRELATION.STARTDATE,
                                @ISCONTACT=BATCHAUCTIONITEMCONSTITUENTRELATION.ISCONTACT,
                                @ISPRIMARYCONTACT=BATCHAUCTIONITEMCONSTITUENTRELATION.ISPRIMARYCONTACT,
                                @CONTACTTYPECODEID=BATCHAUCTIONITEMCONSTITUENTRELATION.CONTACTTYPECODEID,
                                @POSITION=BATCHAUCTIONITEMCONSTITUENTRELATION.POSITION,
                                @ISMATCHINGGIFTRELATIONSHIP=BATCHAUCTIONITEMCONSTITUENTRELATION.ISMATCHINGGIFTRELATIONSHIP,
                                @JOBCATEGORYCODEID=BATCHAUCTIONITEMCONSTITUENTRELATION.JOBCATEGORYCODEID,
                                @CAREERLEVELCODEID=BATCHAUCTIONITEMCONSTITUENTRELATION.CAREERLEVELCODEID
                                from dbo.BATCHAUCTIONITEMCONSTITUENTRELATION where BATCHAUCTIONITEMCONSTITUENTRELATION.CONSTITUENTID = @ID and BATCHAUCTIONITEMCONSTITUENTRELATION.ISPRIMARYBUSINESS = 1

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

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

                                    @ORGANIZATION_PHONETYPECODEID=BATCHAUCTIONITEMCONSTITUENT.PHONETYPECODEID, 
                                    @ORGANIZATION_NUMBER=BATCHAUCTIONITEMCONSTITUENT.NUMBER                                
                                    from dbo.BATCHAUCTIONITEMCONSTITUENT where BATCHAUCTIONITEMCONSTITUENT.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.BATCHAUCTIONITEMCONSTITUENTGROUPMEMBER BRCGM
                                    inner join dbo.BATCHAUCTIONITEMCONSTITUENT BRC on BRCGM.MEMBERID = BRC.ID
                                    where BRCGM.GROUPID = @ID and BRCGM.ISPRIMARY = 1

                                    -- Not using UFN_BATCHAUCTIONITEMCONSTITUENT_GETGROUPMEMBERS_TOITEMLISTXML since the root element

                                    -- needed is different the function's.

                                    set @GROUPMEMBERS =    (
                                                                select
                                                                    [ID],
                                                                    [NAME],
                                                                    [MEMBERID],
                                                                    [ISPRIMARY]
                                                                from dbo.[UFN_BATCHAUCTIONITEMCONSTITUENT_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.BATCHAUCTIONITEMCONSTITUENTGROUPMEMBER GM
                                    inner join dbo.BATCHAUCTIONITEMCONSTITUENT 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.BATCHAUCTIONITEMCONSTITUENTGROUPMEMBER GM
                                    inner join dbo.BATCHAUCTIONITEMCONSTITUENT 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.BATCHAUCTIONITEMCONSTITUENTRELATION 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.BATCHAUCTIONITEMCONSTITUENTGROUPMEMBER BRCGM
                                inner join dbo.BATCHAUCTIONITEMCONSTITUENT BRC on BRCGM.GROUPID = BRC.ID and BRC.GROUPTYPECODE = 0
                                left join dbo.CONSTITUENT C on BRC.EXISTINGCONSTITUENTID = C.ID
                                where BRCGM.MEMBERID = @ID

                                if @BATCHHOUSEHOLDID is not null and @BATCHSPOUSEID is not null
                                begin
                                    if exists (select 1 from dbo.BATCHAUCTIONITEMCONSTITUENTGROUPMEMBER 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;