USP_AUCTIONITEMBATCH_CONSTITUENT_ADD

Saves an auction item batch constituent record with spouse and business information.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CURRENTAPPUSERID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@ISORGANIZATION	bit	IN
@LASTNAME	nvarchar(100)	IN
@FIRSTNAME	nvarchar(50)	IN
@MIDDLENAME	nvarchar(50)	IN
@MAIDENNAME	nvarchar(100)	IN
@NICKNAME	nvarchar(50)	IN
@TITLECODEID	uniqueidentifier	IN
@SUFFIXCODEID	uniqueidentifier	IN
@GENDERCODE	tinyint	IN
@BIRTHDATE	UDT_FUZZYDATE	IN
@ADDRESS_ADDRESSTYPECODEID	uniqueidentifier	IN
@ADDRESS_DONOTMAIL	bit	IN
@ADDRESS_COUNTRYID	uniqueidentifier	IN
@ADDRESS_STATEID	uniqueidentifier	IN
@ADDRESS_ADDRESSBLOCK	nvarchar(150)	IN
@ADDRESS_CITY	nvarchar(50)	IN
@ADDRESS_POSTCODE	nvarchar(12)	IN
@ADDRESS_OMITFROMVALIDATION	bit	IN
@ADDRESS_CART	nvarchar(10)	IN
@ADDRESS_DPC	nvarchar(8)	IN
@ADDRESS_LOT	nvarchar(5)	IN
@ADDRESS_COUNTYCODEID	uniqueidentifier	IN
@ADDRESS_CONGRESSIONALDISTRICTCODEID	uniqueidentifier	IN
@ADDRESS_LASTVALIDATIONATTEMPTDATE	datetime	IN
@ADDRESS_VALIDATIONMESSAGE	nvarchar(100)	IN
@ADDRESS_CERTIFICATIONDATA	int	IN
@PHONE_PHONETYPECODEID	uniqueidentifier	IN
@PHONE_NUMBER	nvarchar(100)	IN
@EMAILADDRESS_EMAILADDRESSTYPECODEID	uniqueidentifier	IN
@EMAILADDRESS_EMAILADDRESS	UDT_EMAILADDRESS	IN
@MARITALSTATUSCODEID	uniqueidentifier	IN
@WEBADDRESS	UDT_WEBADDRESS	IN
@INDUSTRYCODEID	uniqueidentifier	IN
@NUMEMPLOYEES	int	IN
@NUMSUBSIDIARIES	int	IN
@PARENTCORPID	uniqueidentifier	IN
@SPOUSEID	uniqueidentifier	IN
@SPOUSE_LASTNAME	nvarchar(100)	IN
@SPOUSE_FIRSTNAME	nvarchar(50)	IN
@SPOUSE_MIDDLENAME	nvarchar(50)	IN
@SPOUSE_MAIDENNAME	nvarchar(100)	IN
@SPOUSE_NICKNAME	nvarchar(50)	IN
@SPOUSE_TITLECODEID	uniqueidentifier	IN
@SPOUSE_SUFFIXCODEID	uniqueidentifier	IN
@SPOUSE_GENDERCODE	tinyint	IN
@SPOUSE_BIRTHDATE	UDT_FUZZYDATE	IN
@SPOUSE_RECIPROCALTYPECODEID	uniqueidentifier	IN
@SPOUSE_RELATIONSHIPTYPECODEID	uniqueidentifier	IN
@COPYPRIMARYINFORMATION	bit	IN
@SPOUSE_STARTDATE	datetime	IN
@PRIMARYRELATIONSHIPEXISTS	bit	IN
@PRIMARYMATCHFACTOR	decimal(5, 2)	IN
@RECIPROCALRELATIONSHIPEXISTS	bit	IN
@RECIPROCALMATCHFACTOR	decimal(5, 2)	IN
@BUSINESSID	uniqueidentifier	IN
@BUSINESS_NAME	nvarchar(100)	IN
@BUSINESS_ADDRESSTYPECODEID	uniqueidentifier	IN
@BUSINESS_COUNTRYID	uniqueidentifier	IN
@BUSINESS_STATEID	uniqueidentifier	IN
@BUSINESS_ADDRESSBLOCK	nvarchar(150)	IN
@BUSINESS_CITY	nvarchar(50)	IN
@BUSINESS_POSTCODE	nvarchar(12)	IN
@BUSINESS_DONOTMAIL	bit	IN
@BUSINESS_OMITFROMVALIDATION	bit	IN
@BUSINESS_CART	nvarchar(10)	IN
@BUSINESS_DPC	nvarchar(8)	IN
@BUSINESS_LOT	nvarchar(5)	IN
@BUSINESS_COUNTYCODEID	uniqueidentifier	IN
@BUSINESS_CONGRESSIONALDISTRICTCODEID	uniqueidentifier	IN
@BUSINESS_LASTVALIDATIONATTEMPTDATE	datetime	IN
@BUSINESS_VALIDATIONMESSAGE	nvarchar(100)	IN
@BUSINESS_CERTIFICATIONDATA	int	IN
@BUSINESS_PHONETYPECODEID	uniqueidentifier	IN
@BUSINESS_NUMBER	nvarchar(100)	IN
@BUSINESS_RECIPROCALTYPECODEID	uniqueidentifier	IN
@BUSINESS_RELATIONSHIPTYPECODEID	uniqueidentifier	IN
@BUSINESS_STARTDATE	datetime	IN
@ISCONTACT	bit	IN
@ISPRIMARYCONTACT	bit	IN
@CONTACTTYPECODEID	uniqueidentifier	IN
@POSITION	nvarchar(100)	IN
@ISMATCHINGGIFTRELATIONSHIP	bit	IN
@HOUSEHOLDCOPYPRIMARYCONTACTINFO	bit	IN
@ISSPOUSERELATIONSHIP	bit	IN
@ADDRESS_DONOTMAILREASONCODEID	uniqueidentifier	IN
@BUSINESS_DONOTMAILREASONCODEID	uniqueidentifier	IN
@JOBCATEGORYCODEID	uniqueidentifier	IN
@CAREERLEVELCODEID	uniqueidentifier	IN
@BUSINESS_PRIMARYRECOGNITIONTYPECODEID	uniqueidentifier	IN
@BUSINESS_RECIPROCALRECOGNITIONTYPECODEID	uniqueidentifier	IN
@BUSINESS_PRIMARYRELATIONSHIPEXISTS	bit	IN
@BUSINESS_RECIPROCALRELATIONSHIPEXISTS	bit	IN
@BUSINESS_PRIMARYMATCHFACTOR	decimal(5, 2)	IN
@BUSINESS_RECIPROCALMATCHFACTOR	decimal(5, 2)	IN

Definition

Copy



            CREATE procedure dbo.USP_AUCTIONITEMBATCH_CONSTITUENT_ADD
                    (
                        @ID uniqueidentifier = null output,
                        @CURRENTAPPUSERID uniqueidentifier,                        
                        @CHANGEAGENTID uniqueidentifier = null,
                        @ISORGANIZATION bit,
                        @LASTNAME nvarchar(100),
                        @FIRSTNAME nvarchar(50) = '',
                        @MIDDLENAME nvarchar(50) = '',
                        @MAIDENNAME nvarchar(100) = '',
                        @NICKNAME nvarchar(50) = '',
                        @TITLECODEID uniqueidentifier = null,
                        @SUFFIXCODEID uniqueidentifier = null,
                        @GENDERCODE tinyint = 0,
                        @BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
                        @ADDRESS_ADDRESSTYPECODEID uniqueidentifier = null,
                        @ADDRESS_DONOTMAIL bit = 0,
                        @ADDRESS_COUNTRYID uniqueidentifier = null,
                        @ADDRESS_STATEID uniqueidentifier = null,
                        @ADDRESS_ADDRESSBLOCK nvarchar(150) = '',
                        @ADDRESS_CITY nvarchar(50) = '',
                        @ADDRESS_POSTCODE nvarchar(12) = '',

                        -- Address Validation

                        @ADDRESS_OMITFROMVALIDATION bit = 0,
                        @ADDRESS_CART nvarchar(10) = '',
                        @ADDRESS_DPC nvarchar(8) = '',
                        @ADDRESS_LOT nvarchar(5) = '',
                        @ADDRESS_COUNTYCODEID uniqueidentifier = null,
                        @ADDRESS_CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
                        @ADDRESS_LASTVALIDATIONATTEMPTDATE datetime = null,
                        @ADDRESS_VALIDATIONMESSAGE nvarchar(100) = '',
                        @ADDRESS_CERTIFICATIONDATA integer = 0,

                        @PHONE_PHONETYPECODEID uniqueidentifier = null,
                        @PHONE_NUMBER nvarchar(100) = '',
                        @EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null,
                        @EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
                        @MARITALSTATUSCODEID uniqueidentifier = null,
                        @WEBADDRESS dbo.UDT_WEBADDRESS = '',
                        @INDUSTRYCODEID uniqueidentifier = null,
                        @NUMEMPLOYEES int = 0,
                        @NUMSUBSIDIARIES int = 0,
                        @PARENTCORPID uniqueidentifier = null,

                        --Individual's relationship Variables

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

                        --Business variables

                        @BUSINESSID uniqueidentifier = null,                        
                        @BUSINESS_NAME nvarchar(100) = '',
                        @BUSINESS_ADDRESSTYPECODEID uniqueidentifier = null,
                        @BUSINESS_COUNTRYID uniqueidentifier = null,
                        @BUSINESS_STATEID uniqueidentifier = null,
                        @BUSINESS_ADDRESSBLOCK nvarchar(150) = '',
                        @BUSINESS_CITY nvarchar(50) = '',
                        @BUSINESS_POSTCODE nvarchar(12) = '',
                        @BUSINESS_DONOTMAIL bit = 0,

                        -- Address Validation

                        @BUSINESS_OMITFROMVALIDATION bit = 0,
                        @BUSINESS_CART nvarchar(10) = '',
                           @BUSINESS_DPC nvarchar(8) = '',
                        @BUSINESS_LOT nvarchar(5) = '',
                         @BUSINESS_COUNTYCODEID uniqueidentifier = null,
                        @BUSINESS_CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
                        @BUSINESS_LASTVALIDATIONATTEMPTDATE datetime = null,
                        @BUSINESS_VALIDATIONMESSAGE nvarchar(100) = '',
                        @BUSINESS_CERTIFICATIONDATA integer = 0,

                        @BUSINESS_PHONETYPECODEID uniqueidentifier = null,
                        @BUSINESS_NUMBER nvarchar(100) = '',                    
                        @BUSINESS_RECIPROCALTYPECODEID uniqueidentifier = null,
                        @BUSINESS_RELATIONSHIPTYPECODEID uniqueidentifier = null,
                        @BUSINESS_STARTDATE datetime = null,                        
                        @ISCONTACT bit = 0,
                        @ISPRIMARYCONTACT bit = 0,
                        @CONTACTTYPECODEID uniqueidentifier = null,
                        @POSITION nvarchar(100) = '',    
                        @ISMATCHINGGIFTRELATIONSHIP bit = 0,

                        -- Individual's household variables

                        @HOUSEHOLDCOPYPRIMARYCONTACTINFO bit = 0,

                        --Individual's relationship variable

                        @ISSPOUSERELATIONSHIP bit = 0,
                        @ADDRESS_DONOTMAILREASONCODEID uniqueidentifier = null,
                        @BUSINESS_DONOTMAILREASONCODEID uniqueidentifier = null,
                        @JOBCATEGORYCODEID uniqueidentifier = null,
                        @CAREERLEVELCODEID uniqueidentifier = null,

                        @BUSINESS_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null,
                        @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null,
                        @BUSINESS_PRIMARYRELATIONSHIPEXISTS bit = 0,
                        @BUSINESS_RECIPROCALRELATIONSHIPEXISTS bit = 0,
                        @BUSINESS_PRIMARYMATCHFACTOR decimal(5,2) = 100,
                        @BUSINESS_RECIPROCALMATCHFACTOR decimal(5,2) = 100
                    ) as begin

                        set nocount on;

                        declare @KEYNAME nvarchar(100);
                        declare @KEYNAMEPREFIX nvarchar(50);
                        declare @CURRENTDATE datetime;
                        set @CURRENTDATE = getdate();

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

            if @ADDRESS_DONOTMAIL = 0
              set @ADDRESS_DONOTMAILREASONCODEID = null

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

                        begin try

                            if @ISORGANIZATION = 0
                                begin
                                    -------------- Insert Individual's Information --------------

                                    insert into dbo.BATCHAUCTIONITEMCONSTITUENT
                                    ([ID],[ISORGANIZATION],[KEYNAME],[FIRSTNAME],[MIDDLENAME],[MAIDENNAME],[NICKNAME],[TITLECODEID],[SUFFIXCODEID],[GENDERCODE],
                                        [BIRTHDATE],[MARITALSTATUSCODEID],[ADDRESSTYPECODEID],[DONOTMAIL],[DONOTMAILREASONCODEID],[COUNTRYID],[STATEID],[ADDRESSBLOCK],[CITY],[POSTCODE],
                                        [OMITFROMVALIDATION],[CART],[DPC],[LOT],[COUNTYCODEID],[CONGRESSIONALDISTRICTCODEID],[LASTVALIDATIONATTEMPTDATE],[VALIDATIONMESSAGE],[CERTIFICATIONDATA],
                                        [PHONETYPECODEID],[NUMBER],[EMAILADDRESSTYPECODEID],[EMAILADDRESS],[CURRENTAPPUSERID],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
                                    values
                                    (@ID,@ISORGANIZATION,@LASTNAME,@FIRSTNAME,@MIDDLENAME,@MAIDENNAME,@NICKNAME,@TITLECODEID,@SUFFIXCODEID,@GENDERCODE,@BIRTHDATE,@MARITALSTATUSCODEID,
                                        @ADDRESS_ADDRESSTYPECODEID,@ADDRESS_DONOTMAIL,@ADDRESS_DONOTMAILREASONCODEID,@ADDRESS_COUNTRYID,@ADDRESS_STATEID,@ADDRESS_ADDRESSBLOCK,@ADDRESS_CITY,@ADDRESS_POSTCODE,
                                        @ADDRESS_OMITFROMVALIDATION,@ADDRESS_CART,@ADDRESS_DPC,@ADDRESS_LOT,@ADDRESS_COUNTYCODEID,@ADDRESS_CONGRESSIONALDISTRICTCODEID,@ADDRESS_LASTVALIDATIONATTEMPTDATE,@ADDRESS_VALIDATIONMESSAGE,@ADDRESS_CERTIFICATIONDATA,
                                        @PHONE_PHONETYPECODEID,@PHONE_NUMBER,@EMAILADDRESS_EMAILADDRESSTYPECODEID,@EMAILADDRESS_EMAILADDRESS,@CURRENTAPPUSERID,@CHANGEAGENTID,
                                        @CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);

                                    -------------- Insert Spouse's Information --------------

                                    declare @HASSPOUSE bit
                                    if (@SPOUSEID is not null) or (coalesce(@SPOUSE_LASTNAME,'') <> '')
                                        set @HASSPOUSE = 1
                                    else
                                        set @HASSPOUSE = 0

                                    --If their is a spouse create the relationship

                                    if @HASSPOUSE = 1
                                        begin                    
                                            declare @BATCHSPOUSEID uniqueidentifier;
                                            set @BATCHSPOUSEID = newID();
                                            ---------Spouse Constituent Information---------

                                            insert into dbo.BATCHAUCTIONITEMCONSTITUENT
                                            ([ID],[ISORGANIZATION],[EXISTINGCONSTITUENTID],[KEYNAME],[FIRSTNAME],[MIDDLENAME],[MAIDENNAME],[NICKNAME],[TITLECODEID],[SUFFIXCODEID],
                                                [GENDERCODE],[BIRTHDATE],[MARITALSTATUSCODEID],[CURRENTAPPUSERID],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
                                            values
                                            (@BATCHSPOUSEID,0,@SPOUSEID,@SPOUSE_LASTNAME,@SPOUSE_FIRSTNAME,@SPOUSE_MIDDLENAME,@SPOUSE_MAIDENNAME,@SPOUSE_NICKNAME,@SPOUSE_TITLECODEID,@SPOUSE_SUFFIXCODEID,
                                                @SPOUSE_GENDERCODE,@SPOUSE_BIRTHDATE,@MARITALSTATUSCODEID,@CURRENTAPPUSERID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);

                                            ---------Spouse Constituent Information---------

                                            insert into dbo.BATCHAUCTIONITEMCONSTITUENTRELATION
                                            ([CONSTITUENTID],[RELATIONID],[COPYPRIMARYINFORMATION],[RELATIONSHIPTYPECODEID],[RECIPROCALTYPECODEID],[STARTDATE],[PRIMARYRELATIONSHIPEXISTS],[PRIMARYMATCHFACTOR],[RECIPROCALRELATIONSHIPEXISTS],
                                            [RECIPROCALMATCHFACTOR],[ISSPOUSE],[ISPRIMARYBUSINESS],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
                                            values
                                            (@ID,@BATCHSPOUSEID,@COPYPRIMARYINFORMATION,@SPOUSE_RELATIONSHIPTYPECODEID,@SPOUSE_RECIPROCALTYPECODEID,@SPOUSE_STARTDATE,0,0,
                                            0,0,
                                            -- Default @ISSPOUSERELATIONSHIP to true for backwards compatibility

                                            coalesce(@ISSPOUSERELATIONSHIP, 1),
                                            0,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);

                                            -------------- Insert Household Information --------------

                                            exec dbo.USP_AUCTIONITEMBATCH_ADDINDIVIDUALHOUSEHOLD @ID, @BATCHSPOUSEID, @HOUSEHOLDCOPYPRIMARYCONTACTINFO, @CURRENTAPPUSERID, @CHANGEAGENTID;
                                        end

                                    -------------- Insert Organization Information --------------

                                    declare @HASBUSINESS bit

                                    if (@BUSINESSID is not null) or (coalesce(@BUSINESS_NAME,'') <> '')
                                        set @HASBUSINESS = 1
                                    else
                                        set @HASBUSINESS = 0

                                    --If their is an organization create the relationship

                                    if @HASBUSINESS = 1
                                        begin
                                            declare @BATCHORGID uniqueidentifier;
                                            set @BATCHORGID = newID();                                            

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

                                            ---------Organization Constituent Information---------

                                            insert into dbo.BATCHAUCTIONITEMCONSTITUENT
                                            ([ID],[ISORGANIZATION],[EXISTINGCONSTITUENTID],[KEYNAME],[KEYNAMEPREFIX],[ADDRESSTYPECODEID],[DONOTMAIL],[DONOTMAILREASONCODEID],[COUNTRYID],
                                                [STATEID],[ADDRESSBLOCK],[CITY],[POSTCODE],
                                                [OMITFROMVALIDATION],[CART],[DPC],[LOT],[COUNTYCODEID],[CONGRESSIONALDISTRICTCODEID],[LASTVALIDATIONATTEMPTDATE],[VALIDATIONMESSAGE],[CERTIFICATIONDATA],
                                                [PHONETYPECODEID],[NUMBER],[CURRENTAPPUSERID],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
                                            values
                                            (@BATCHORGID,1,@BUSINESSID,@KEYNAME,@KEYNAMEPREFIX,@BUSINESS_ADDRESSTYPECODEID,@BUSINESS_DONOTMAIL,@BUSINESS_DONOTMAILREASONCODEID,@BUSINESS_COUNTRYID,
                                                @BUSINESS_STATEID,@BUSINESS_ADDRESSBLOCK,@BUSINESS_CITY,@BUSINESS_POSTCODE,
                                                @BUSINESS_OMITFROMVALIDATION,@BUSINESS_CART,@BUSINESS_DPC,@BUSINESS_LOT,@BUSINESS_COUNTYCODEID,@BUSINESS_CONGRESSIONALDISTRICTCODEID,@BUSINESS_LASTVALIDATIONATTEMPTDATE,@BUSINESS_VALIDATIONMESSAGE,@BUSINESS_CERTIFICATIONDATA,
                                                @BUSINESS_PHONETYPECODEID,@BUSINESS_NUMBER,                                                
                                                @CURRENTAPPUSERID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);

                                            if @BUSINESS_PRIMARYRELATIONSHIPEXISTS = 0
                                                select @BUSINESS_PRIMARYRELATIONSHIPEXISTS = 1,
                                                    @BUSINESS_PRIMARYRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
                                                    @BUSINESS_PRIMARYMATCHFACTOR= RRD.MATCHFACTOR
                                                from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD      
                                                where RRD.CONSTITUENTTYPECODE=0 and RRD.RELATIONSHIPTYPECODEID=@BUSINESS_RELATIONSHIPTYPECODEID

                                            if @BUSINESS_RECIPROCALRELATIONSHIPEXISTS = 0
                                                select @BUSINESS_RECIPROCALRELATIONSHIPEXISTS = 1,
                                                    @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
                                                    @BUSINESS_RECIPROCALMATCHFACTOR= RRD.MATCHFACTOR
                                                from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD      
                                                where RRD.CONSTITUENTTYPECODE=1 and RRD.RELATIONSHIPTYPECODEID=@BUSINESS_RECIPROCALTYPECODEID

                                            insert into dbo.BATCHAUCTIONITEMCONSTITUENTRELATION
                                            ([CONSTITUENTID],[RELATIONID],[RELATIONSHIPTYPECODEID],[RECIPROCALTYPECODEID],[ISCONTACT],[ISPRIMARYCONTACT],[CONTACTTYPECODEID],
                                                [STARTDATE],[PRIMARYRELATIONSHIPEXISTS],[PRIMARYMATCHFACTOR],[PRIMARYRECOGNITIONTYPECODEID],[RECIPROCALRELATIONSHIPEXISTS],[RECIPROCALMATCHFACTOR],
                                                [RECIPROCALRECOGNITIONTYPECODEID],[POSITION],[ISMATCHINGGIFTRELATIONSHIP],[ISPRIMARYBUSINESS],[ISSPOUSE],[JOBCATEGORYCODEID],[CAREERLEVELCODEID],
                                                [ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
                                            values
                                            (@ID,@BATCHORGID,@BUSINESS_RELATIONSHIPTYPECODEID,@BUSINESS_RECIPROCALTYPECODEID,@ISCONTACT,@ISPRIMARYCONTACT,@CONTACTTYPECODEID,
                                                @BUSINESS_STARTDATE,ISNULL(@BUSINESS_PRIMARYRELATIONSHIPEXISTS,0), ISNULL(@BUSINESS_PRIMARYMATCHFACTOR,0), @BUSINESS_PRIMARYRECOGNITIONTYPECODEID, ISNULL(@BUSINESS_RECIPROCALRELATIONSHIPEXISTS,0),
                                                ISNULL(@BUSINESS_RECIPROCALMATCHFACTOR,0), @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID, @POSITION,@ISMATCHINGGIFTRELATIONSHIP,1,0,@JOBCATEGORYCODEID,@CAREERLEVELCODEID,
                                                @CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);
                                        end
                                    end;
                                else
                                    begin
                                        if @NUMEMPLOYEES is null
                                            set @NUMEMPLOYEES = 0;
                                        if @NUMSUBSIDIARIES is null
                                            set @NUMSUBSIDIARIES = 0;

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

                                        insert into dbo.BATCHAUCTIONITEMCONSTITUENT 
                                            (ID,ISORGANIZATION,KEYNAME,WEBADDRESS,KEYNAMEPREFIX,INDUSTRYCODEID,NUMEMPLOYEES,NUMSUBSIDIARIES,PARENTCORPID,ADDRESSTYPECODEID,DONOTMAIL,DONOTMAILREASONCODEID,COUNTRYID,STATEID,ADDRESSBLOCK,CITY,POSTCODE,OMITFROMVALIDATION,CART,DPC,LOT,COUNTYCODEID,CONGRESSIONALDISTRICTCODEID,LASTVALIDATIONATTEMPTDATE,VALIDATIONMESSAGE,CERTIFICATIONDATA,PHONETYPECODEID,NUMBER,EMAILADDRESSTYPECODEID,EMAILADDRESS,CURRENTAPPUSERID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                                            values (@ID,@ISORGANIZATION,@KEYNAME,@WEBADDRESS,@KEYNAMEPREFIX,@INDUSTRYCODEID,@NUMEMPLOYEES,@NUMSUBSIDIARIES,@PARENTCORPID,@ADDRESS_ADDRESSTYPECODEID,@ADDRESS_DONOTMAIL,@ADDRESS_DONOTMAILREASONCODEID,@ADDRESS_COUNTRYID,@ADDRESS_STATEID,@ADDRESS_ADDRESSBLOCK,@ADDRESS_CITY,@ADDRESS_POSTCODE,@ADDRESS_OMITFROMVALIDATION,@ADDRESS_CART,@ADDRESS_DPC,@ADDRESS_LOT,@ADDRESS_COUNTYCODEID,@ADDRESS_CONGRESSIONALDISTRICTCODEID,@ADDRESS_LASTVALIDATIONATTEMPTDATE,@ADDRESS_VALIDATIONMESSAGE,@ADDRESS_CERTIFICATIONDATA,@PHONE_PHONETYPECODEID,@PHONE_NUMBER,@EMAILADDRESS_EMAILADDRESSTYPECODEID,@EMAILADDRESS_EMAILADDRESS,@CURRENTAPPUSERID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);

                                    end;
                        end try


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

                        return 0;

                    end;