USP_BATCHSPONSORSHIPCONSTITUENT_ADD

Saves a batch constituent record with spouse and business information.

Parameters

Parameter Parameter Type Mode Description
@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(50) 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_BATCHSPONSORSHIPCONSTITUENT_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(50) = '',    
                        @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.BATCHSPONSORSHIPCONSTITUENT
                                    ([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.BATCHSPONSORSHIPCONSTITUENT
                                            ([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.BATCHSPONSORSHIPCONSTITUENTRELATION
                                            ([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_BATCHSPONSORSHIP_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.BATCHSPONSORSHIPCONSTITUENT
                                            ([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.BATCHSPONSORSHIPCONSTITUENTRELATION
                                            ([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.BATCHSPONSORSHIPCONSTITUENT 
                                            (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;