USP_DATAFORMTEMPLATE_EDITLOAD_CONSTITUENTUPDATEBATCHTEMPLATE

The load procedure used by the edit dataform template "Constituent Update Batch Template Edit Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@VALIDATEONLY	bit	INOUT	Validate only
@CONSTITUENTTYPECODE	tinyint	INOUT	Constituent type
@BIRTHDATE	UDT_FUZZYDATE	INOUT	Birth date
@FIRSTNAME	nvarchar(50)	INOUT	First name
@GENDERCODE	tinyint	INOUT	Gender
@GIVESANONYMOUSLY	bit	INOUT	Gives anonymously
@KEYNAME	nvarchar(100)	INOUT	Last name
@LOOKUPID	nvarchar(100)	INOUT	Lookup ID
@MAIDENNAME	nvarchar(100)	INOUT	Maiden name
@MIDDLENAME	nvarchar(50)	INOUT	Middle name
@NICKNAME	nvarchar(50)	INOUT	Nickname
@SUFFIXCODEID	uniqueidentifier	INOUT	Suffix
@TITLECODEID	uniqueidentifier	INOUT	Title
@WEBADDRESS	UDT_WEBADDRESS	INOUT	Website
@ALTERNATELOOKUPIDS	xml	INOUT	Alternate lookup IDs
@SECURITYATTRIBUTES	xml	INOUT	Security attributes
@DECEASED	bit	INOUT	Deceased
@DECEASEDDATE	UDT_FUZZYDATE	INOUT	Deceased date
@CONSTITUENT_SITEID	uniqueidentifier	INOUT	Constituent site
@INTERESTS	xml	INOUT	Interests
@PROSPECTMANAGERFUNDRAISERID	uniqueidentifier	INOUT	Prospect manager
@ADDRESSES	xml	INOUT	Addresses
@EMAILADDRESSES	xml	INOUT	Email addresses
@PHONES	xml	INOUT	Phones
@CONSTITUENCIES	xml	INOUT	Constituencies
@GROUPTYPEID	uniqueidentifier	INOUT	Group type
@GROUPDESCRIPTION	nvarchar(300)	INOUT	Group description
@GROUPSTARTDATE	date	INOUT	Group consolidation date
@ORG_INDUSTRYCODEID	uniqueidentifier	INOUT	Constituent's industry
@ORG_NUMEMPLOYEES	int	INOUT	Number of employees
@ORG_NUMSUBSIDIARIES	int	INOUT	Number of subsidiaries
@ORG_PARENTCORPID	uniqueidentifier	INOUT	Parent corporation
@MARITALSTATUSCODEID	uniqueidentifier	INOUT	Marital status
@SPOUSE_ID	uniqueidentifier	INOUT	Spouse
@SPOUSE_BIRTHDATE	UDT_FUZZYDATE	INOUT	Spouse birth date
@SPOUSE_FIRSTNAME	nvarchar(50)	INOUT	Spouse first name
@SPOUSE_GENDERCODE	tinyint	INOUT	Spouse gender
@SPOUSE_LASTNAME	nvarchar(100)	INOUT	Spouse last name
@SPOUSE_LOOKUPID	nvarchar(100)	INOUT	Spouse lookup ID
@SPOUSE_MAIDENNAME	nvarchar(100)	INOUT	Spouse maiden name
@SPOUSE_MIDDLENAME	nvarchar(50)	INOUT	Spouse middle name
@SPOUSE_NICKNAME	nvarchar(50)	INOUT	Spouse nickname
@SPOUSE_SUFFIXCODEID	uniqueidentifier	INOUT	Spouse suffix
@SPOUSE_TITLECODEID	uniqueidentifier	INOUT	Spouse title
@SPOUSE_RELATIONSHIPTYPECODEID	uniqueidentifier	INOUT	Relation to spouse
@SPOUSE_RECIPROCALTYPECODEID	uniqueidentifier	INOUT	Spouse's relation to constituent
@BUSINESS_ADDRESSBLOCK	nvarchar(150)	INOUT	Business address
@BUSINESS_ADDRESSTYPECODEID	uniqueidentifier	INOUT	Business address type
@BUSINESS_CART	nvarchar(10)	INOUT	Business CART
@BUSINESS_CITY	nvarchar(50)	INOUT	Business city
@BUSINESS_COUNTRYID	uniqueidentifier	INOUT	Business country
@BUSINESS_DONOTMAIL	bit	INOUT	Business do not mail
@BUSINESS_DONOTMAILREASONCODEID	uniqueidentifier	INOUT	Business do not mail reason
@BUSINESS_DPC	nvarchar(8)	INOUT	Business DPC
@BUSINESS_EMAILADDRESS	UDT_EMAILADDRESS	INOUT	Business email address
@BUSINESS_EMAILADDRESSTYPECODEID	uniqueidentifier	INOUT	Business email type
@BUSINESS_INDUSTRYCODEID	uniqueidentifier	INOUT	Industry
@BUSINESS_LOOKUPID	nvarchar(100)	INOUT	Business lookup ID
@BUSINESS_LOT	nvarchar(5)	INOUT	Business LOT
@BUSINESS_ID	uniqueidentifier	INOUT	Business
@BUSINESS_NAME	nvarchar(100)	INOUT	Business name
@BUSINESS_NUMEMPLOYEES	int	INOUT	Business number of employees
@BUSINESS_NUMSUBSIDIARIES	int	INOUT	Business number of subsidiaries
@BUSINESS_PARENTCORPID	uniqueidentifier	INOUT	Business parent organization
@BUSINESS_PHONENUMBER	nvarchar(100)	INOUT	Business phone number
@BUSINESS_PHONE_COUNTRYID	uniqueidentifier	INOUT	Business phone number country
@BUSINESS_PHONETYPECODEID	uniqueidentifier	INOUT	Business phone type
@BUSINESS_RELATIONSHIPTYPECODEID	uniqueidentifier	INOUT	Relation to business
@BUSINESS_RECIPROCALTYPECODEID	uniqueidentifier	INOUT	Business's relation to constituent
@BUSINESS_STATEID	uniqueidentifier	INOUT	Business state
@BUSINESS_WEBADDRESS	UDT_WEBADDRESS	INOUT	Business website
@BUSINESS_POSTCODE	nvarchar(12)	INOUT	Business ZIP
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST	uniqueidentifier	INOUT	Spouse relationship type
@SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST	uniqueidentifier	INOUT	Spouse reciprocal type
@BUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST	uniqueidentifier	INOUT	Business relationship type
@BUSINESS_RECIPROCALTYPECODEIDSIMPLELIST	uniqueidentifier	INOUT	Business reciprocal type
@BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS	bit	INOUT	Apply recognition from business
@BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR	decimal(5, 2)	INOUT	Recognition from business match percent
@BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS	bit	INOUT	Apply recognition to business
@BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR	decimal(5, 2)	INOUT	Recognition to business match percent
@BUSINESS_PRIMARYRECOGNITIONTYPECODEID	uniqueidentifier	INOUT	Recognition from business type
@BUSINESS_RECIPROCALRECOGNITIONTYPECODEID	uniqueidentifier	INOUT	Recognition to business type
@BUSINESS_EMAILADDRESSSTARTDATE	date	INOUT	Business email start date
@SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS	bit	INOUT	Apply recognition from spouse
@SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR	decimal(5, 2)	INOUT	Recognition from spouse match percent
@SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS	bit	INOUT	Apply recognition to spouse
@SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR	decimal(5, 2)	INOUT	Recognition to spouse match percent
@SPOUSE_PRIMARYRECOGNITIONTYPECODEID	uniqueidentifier	INOUT	Recognition from spouse type
@SPOUSE_RECIPROCALRECOGNITIONTYPECODEID	uniqueidentifier	INOUT	Recognition to spouse type
@CONSTITUENT_SITES	xml	INOUT	Constituent sites
@BATCHOWNERID	uniqueidentifier	INOUT	Batch owner ID
@BATCHROWID	uniqueidentifier	INOUT	Batch row ID
@SPOUSE_ALTERNATELOOKUPIDS	xml	INOUT	Spouse alternate lookup IDs
@SPOUSE_IMPORTLOOKUPID	nvarchar(100)	INOUT
@ROWFROMBATCHUI	bit	INOUT
@BBNCTRANID	int	INOUT
@REMOVESPOUSE	bit	INOUT
@SOCIALMEDIAACCOUNTS	xml	INOUT
@RELATIONSHIPS	xml	INOUT
@NETCOMMUNITYTRANSACTIONPROCESSORID	uniqueidentifier	INOUT
@NEWADDRESSENDDATECODE	tinyint	INOUT
@CREATEHISTORICALNAMECODE	tinyint	INOUT
@NEWPHONEENDDATECODE	tinyint	INOUT
@NEWEMAILENDDATECODE	tinyint	INOUT
@NAMEFORMATS	xml	INOUT
@REQUESTSNOEMAIL	bit	INOUT
@SOLICITCODES	xml	INOUT
@GENDERCODEID	uniqueidentifier	INOUT
@SPOUSE_GENDERCODEID	uniqueidentifier	INOUT

Definition

Copy



                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_CONSTITUENTUPDATEBATCHTEMPLATE (
                        @ID uniqueidentifier, 
                        @DATALOADED bit = 0 output,
                        @VALIDATEONLY bit = null output,
                        @CONSTITUENTTYPECODE tinyint = null output,
                        @BIRTHDATE dbo.UDT_FUZZYDATE = null output,
                        @FIRSTNAME nvarchar(50) = null output,
                        @GENDERCODE tinyint = null output,
                        @GIVESANONYMOUSLY bit = null output,
                        @KEYNAME nvarchar(100) = null output,
                        @LOOKUPID nvarchar(100) = null output,
                        @MAIDENNAME nvarchar(100) = null output,
                        @MIDDLENAME nvarchar(50) = null output,
                        @NICKNAME nvarchar(50) = null output,
                        @SUFFIXCODEID uniqueidentifier = null output,
                        @TITLECODEID uniqueidentifier = null output,
                        @WEBADDRESS dbo.UDT_WEBADDRESS = null output,
                        @ALTERNATELOOKUPIDS xml = null output,
                        @SECURITYATTRIBUTES xml = null output,
                        @DECEASED bit = null output,
                        @DECEASEDDATE dbo.UDT_FUZZYDATE = null output,
                        @CONSTITUENT_SITEID uniqueidentifier = null output,
                        @INTERESTS xml = null output,
                        @PROSPECTMANAGERFUNDRAISERID uniqueidentifier = null output,
                        --@EDUCATIONALINVOLVEMENTS xml = null output,

                        @ADDRESSES xml = null output,
                        @EMAILADDRESSES xml = null output,
                        @PHONES xml = null output,
                        @CONSTITUENCIES xml = null output,
                        @GROUPTYPEID uniqueidentifier = null output,
                        @GROUPDESCRIPTION nvarchar(300) = null output,
                        @GROUPSTARTDATE date = null output,
                        @ORG_INDUSTRYCODEID uniqueidentifier = null output,
                        @ORG_NUMEMPLOYEES int = null output,
                        @ORG_NUMSUBSIDIARIES int = null output,
                        @ORG_PARENTCORPID uniqueidentifier = null output,
                        @MARITALSTATUSCODEID uniqueidentifier = null output,    
                        @SPOUSE_ID uniqueidentifier = null output,
                        @SPOUSE_BIRTHDATE dbo.UDT_FUZZYDATE = null output,
                        @SPOUSE_FIRSTNAME nvarchar(50) = null output,
                        @SPOUSE_GENDERCODE tinyint = null output,
                        @SPOUSE_LASTNAME nvarchar(100) = null output,
                        @SPOUSE_LOOKUPID nvarchar(100) = null output,
                        @SPOUSE_MAIDENNAME nvarchar(100) = null output,
                        @SPOUSE_MIDDLENAME nvarchar(50) = null output,
                        @SPOUSE_NICKNAME nvarchar(50) = null output,
                        @SPOUSE_SUFFIXCODEID uniqueidentifier = null output,
                        @SPOUSE_TITLECODEID uniqueidentifier = null output,
                        @SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier = null output,
                        @SPOUSE_RECIPROCALTYPECODEID uniqueidentifier = null output,
                        @BUSINESS_ADDRESSBLOCK nvarchar(150) = null output,
                        @BUSINESS_ADDRESSTYPECODEID uniqueidentifier = null output,
                        @BUSINESS_CART nvarchar(10) = null output,
                        @BUSINESS_CITY nvarchar(50) = null output,
                        @BUSINESS_COUNTRYID uniqueidentifier = null output,
                        @BUSINESS_DONOTMAIL bit = null output,
                        @BUSINESS_DONOTMAILREASONCODEID uniqueidentifier = null output,
                        @BUSINESS_DPC nvarchar(8) = null output,
                        @BUSINESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
                        @BUSINESS_EMAILADDRESSTYPECODEID uniqueidentifier = null output,
                        @BUSINESS_INDUSTRYCODEID uniqueidentifier = null output,
                        @BUSINESS_LOOKUPID nvarchar(100) = null output,
                        @BUSINESS_LOT nvarchar(5) = null output,
                        @BUSINESS_ID uniqueidentifier = null output,
                        @BUSINESS_NAME nvarchar(100) = null output,
                        @BUSINESS_NUMEMPLOYEES int = null output,
                        @BUSINESS_NUMSUBSIDIARIES int = null output,
                        @BUSINESS_PARENTCORPID uniqueidentifier = null output,
                        @BUSINESS_PHONENUMBER nvarchar(100) = null output,
                        @BUSINESS_PHONE_COUNTRYID uniqueidentifier = null output,
                        @BUSINESS_PHONETYPECODEID uniqueidentifier = null output,
                        @BUSINESS_RELATIONSHIPTYPECODEID uniqueidentifier = null output,
                        @BUSINESS_RECIPROCALTYPECODEID uniqueidentifier = null output,
                        @BUSINESS_STATEID uniqueidentifier = null output,
                        @BUSINESS_WEBADDRESS dbo.UDT_WEBADDRESS = null output,
                        @BUSINESS_POSTCODE nvarchar(12) = null output,
                        @TSLONG bigint = 0 output,
                        @SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST uniqueidentifier = null output,
                        @SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST uniqueidentifier = null output,
                        @BUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST uniqueidentifier = null output,
                        @BUSINESS_RECIPROCALTYPECODEIDSIMPLELIST uniqueidentifier = null output,
                        @BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = null output,
                        @BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2) = null output,
                        @BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = null output,
                        @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2) = null output,
                        @BUSINESS_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null output,
                        @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null output,
                        @BUSINESS_EMAILADDRESSSTARTDATE date = null output,
                        @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = null output,
                        @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2) = null output,
                        @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = null output,
                        @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2) = null output,
                        @SPOUSE_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null output,
                        @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null output,
                        @CONSTITUENT_SITES xml = null output,
                        @BATCHOWNERID uniqueidentifier = null output,
                        @BATCHROWID uniqueidentifier = null output,
                        @SPOUSE_ALTERNATELOOKUPIDS xml = null output,
                        @SPOUSE_IMPORTLOOKUPID nvarchar(100) = null output,
                        @ROWFROMBATCHUI bit = null output, 
                        @BBNCTRANID int= null output, 
                        @REMOVESPOUSE bit = null output,
                        @SOCIALMEDIAACCOUNTS xml = null output,
                        @RELATIONSHIPS xml = null output,
                        @NETCOMMUNITYTRANSACTIONPROCESSORID uniqueidentifier = null output,
                        @NEWADDRESSENDDATECODE tinyint = null output,
                        @CREATEHISTORICALNAMECODE tinyint = null output,
                        @NEWPHONEENDDATECODE tinyint = null output,
                        @NEWEMAILENDDATECODE tinyint = null output,
                        @NAMEFORMATS xml = null output,
                        @REQUESTSNOEMAIL bit = null output,
                        @SOLICITCODES xml = null output,
                        @GENDERCODEID uniqueidentifier = null output,
                        @SPOUSE_GENDERCODEID uniqueidentifier = null output
                    ) as
                        set nocount on;

                        set @DATALOADED = 0;
                        set @TSLONG = 0;

                        select
                            @ID = C.ID,
                            @DATALOADED = 1,
                            @CONSTITUENTTYPECODE =
                                case
                                    when C.ISORGANIZATION = 1 then 1
                                    when (C.ISGROUP = 1 and GROUPTYPECODE = 0) then 2
                                     when (C.ISGROUP = 1 and GROUPTYPECODE = 1) then 3
                                    else 0
                                end,
                            @BIRTHDATE = C.BIRTHDATE,
                            @FIRSTNAME = C.FIRSTNAME,
                            @GENDERCODE = C.GENDERCODE,
                            @GIVESANONYMOUSLY = C.GIVESANONYMOUSLY,
                            @KEYNAME = (case when isnull(C.[KEYNAMEPREFIX], '') <> '' then C.[KEYNAMEPREFIX] + '\' else '' end) + C.KEYNAME,
                            @LOOKUPID = C.LOOKUPID,
                            @MAIDENNAME = C.MAIDENNAME,
                            @MIDDLENAME = C.MIDDLENAME,
                            @NICKNAME = C.NICKNAME,
                            @SUFFIXCODEID = C.SUFFIXCODEID,
                            @TITLECODEID = C.TITLECODEID,
                            @WEBADDRESS = C.WEBADDRESS,
                            @DECEASED = 
                                case 
                                    when exists(select ID from DECEASEDCONSTITUENT where ID = @ID) then 1
                                    else 0 
                                end,
                            @DECEASEDDATE = D.DECEASEDDATE,
                            @PROSPECTMANAGERFUNDRAISERID = PROSPECT.PROSPECTMANAGERFUNDRAISERID,
                            @GROUPTYPEID = G.GROUPTYPEID,
                            @GROUPDESCRIPTION = G.DESCRIPTION,
                            @GROUPSTARTDATE = G.STARTDATE,
                            @ORG_INDUSTRYCODEID = ORGDATA.INDUSTRYCODEID,
                            @ORG_NUMEMPLOYEES = ORGDATA.NUMEMPLOYEES,
                            @ORG_NUMSUBSIDIARIES = ORGDATA.NUMSUBSIDIARIES,
                            @ORG_PARENTCORPID = ORGDATA.PARENTCORPID,
                            @MARITALSTATUSCODEID = C.MARITALSTATUSCODEID,
                            @SPOUSE_ID = SPOUSE.ID,
                            @SPOUSE_BIRTHDATE = isnull(SPOUSE.BIRTHDATE, '00000000'),
                            @SPOUSE_FIRSTNAME = isnull(SPOUSE.FIRSTNAME, ''),
                            @SPOUSE_GENDERCODE = isnull(SPOUSE.GENDERCODE, ''),
                            @SPOUSE_LASTNAME = isnull(SPOUSE.KEYNAME, ''),
                            @SPOUSE_LOOKUPID = SPOUSE.LOOKUPID,
                            @SPOUSE_MAIDENNAME = isnull(SPOUSE.MAIDENNAME, ''),
                            @SPOUSE_MIDDLENAME = isnull(SPOUSE.MIDDLENAME, ''),
                            @SPOUSE_NICKNAME = isnull(SPOUSE.NICKNAME, ''),
                            @SPOUSE_SUFFIXCODEID = SPOUSE.SUFFIXCODEID,
                            @SPOUSE_TITLECODEID = SPOUSE.TITLECODEID,
                            @SPOUSE_RELATIONSHIPTYPECODEID = SR.RELATIONSHIPTYPECODEID,
                            @SPOUSE_RECIPROCALTYPECODEID = SR.RECIPROCALTYPECODEID,
                            @TSLONG = C.TSLONG,
                            @SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST = SR.RELATIONSHIPTYPECODEID,
                            @SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST = SR.RECIPROCALTYPECODEID,
                            @ROWFROMBATCHUI = convert(bit, 1),
                            @GENDERCODEID = C.GENDERCODEID,
                            @SPOUSE_GENDERCODEID = SPOUSE.GENDERCODEID
                        from
                            dbo.CONSTITUENT C
                            left join dbo.RELATIONSHIP SR on SR.RELATIONSHIPCONSTITUENTID = C.ID and SR.ISSPOUSE = 1
                            left join dbo.CONSTITUENT SPOUSE on SR.RECIPROCALCONSTITUENTID = SPOUSE.ID
                            left join dbo.DECEASEDCONSTITUENT D on D.ID = C.ID
                            left join dbo.GROUPDATA G on G.ID = C.ID
                            left join dbo.ORGANIZATIONDATA ORGDATA on ORGDATA.ID = C.ID
                            left join dbo.PROSPECT on PROSPECT.ID = C.ID
                        where
                            C.ID = @ID

                        /*The save will update the business if the ID or LOOKUPID's match 
                        So default business information if no business is specified in the batch or if this is the same business by ID or LOOKUPID */
                        select
                            @BUSINESS_ADDRESSBLOCK = BA.ADDRESSBLOCK,
                            @BUSINESS_ADDRESSTYPECODEID = BA.ADDRESSTYPECODEID,
                            @BUSINESS_CART = BA.CART,
                            @BUSINESS_CITY = BA.CITY,
                            @BUSINESS_COUNTRYID = BA.COUNTRYID,
                            @BUSINESS_DONOTMAIL = coalesce(BA.DONOTMAIL, '0'),
                            @BUSINESS_DONOTMAILREASONCODEID = BA.DONOTMAILREASONCODEID,
                            @BUSINESS_DPC = BA.DPC,
                            @BUSINESS_EMAILADDRESS = BEA.EMAILADDRESS,
                            @BUSINESS_EMAILADDRESSTYPECODEID = BEA.EMAILADDRESSTYPECODEID,
                            @BUSINESS_INDUSTRYCODEID = OD.INDUSTRYCODEID,
                            @BUSINESS_LOOKUPID = BUSINESS.LOOKUPID,
                            @BUSINESS_LOT = BA.LOT,
                            @BUSINESS_ID = BUSINESS.ID,
                            @BUSINESS_NAME = BUSINESS.KEYNAME,
                            @BUSINESS_NUMEMPLOYEES = OD.NUMEMPLOYEES,
                            @BUSINESS_NUMSUBSIDIARIES = OD.NUMSUBSIDIARIES,
                            @BUSINESS_PARENTCORPID = OD.PARENTCORPID,
                            @BUSINESS_PHONENUMBER = BP.NUMBER,
                            @BUSINESS_PHONETYPECODEID = BP.PHONETYPECODEID,
                            @BUSINESS_RELATIONSHIPTYPECODEID = BR.RELATIONSHIPTYPECODEID,
                            @BUSINESS_RECIPROCALTYPECODEID = BR.RECIPROCALTYPECODEID,
                            @BUSINESS_STATEID = BA.STATEID,
                            @BUSINESS_WEBADDRESS = BUSINESS.WEBADDRESS,
                            @BUSINESS_POSTCODE = BA.POSTCODE,
                            @BUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST = BR.RELATIONSHIPTYPECODEID,
                            @BUSINESS_RECIPROCALTYPECODEIDSIMPLELIST = BR.RECIPROCALTYPECODEID,
                            @BUSINESS_EMAILADDRESSSTARTDATE = BEA.STARTDATE
                        from
                            dbo.CONSTITUENT C
                            left join dbo.RELATIONSHIP BR on BR.RELATIONSHIPCONSTITUENTID = C.ID and BR.ISPRIMARYBUSINESS = 1
                            left join dbo.CONSTITUENT BUSINESS on BR.RECIPROCALCONSTITUENTID = BUSINESS.ID and C.ISORGANIZATION = 0
                            left join dbo.ORGANIZATIONDATA OD on OD.ID = BUSINESS.ID
                            left join dbo.ADDRESS BA on BA.CONSTITUENTID = BUSINESS.ID and BA.ISPRIMARY = 1
                            left join dbo.EMAILADDRESS BEA on BEA.CONSTITUENTID = BUSINESS.ID and BEA.ISPRIMARY = 1
                            left join dbo.PHONE BP on BP.CONSTITUENTID = BUSINESS.ID and BP.ISPRIMARY = 1
                        where
                            C.ID = @ID
                            and 
                            (
                                /*Same business by ID*/
                                (@BUSINESS_ID = BUSINESS.ID)                
                                or
                                /*No ID but same Lookup ID*/
                                (@BUSINESS_ID is null and nullif(@BUSINESS_LOOKUPID, '') = BUSINESS.LOOKUPID)
                                or
                                /*No ID, LookupID, or Name specified*/
                                (@BUSINESS_ID is null and nullif(@BUSINESS_LOOKUPID, '') is null and nullif(@BUSINESS_NAME, '') is null)
                            )

                        set @ALTERNATELOOKUPIDS = dbo.UFN_CONSTITUENT_GETALTERNATELOOKUPIDS_FORUPDATEBATCH_TOITEMLISTXML(@ID);
                        set @SPOUSE_ALTERNATELOOKUPIDS = dbo.UFN_CONSTITUENT_GETALTERNATELOOKUPIDS_FORUPDATEBATCH_TOITEMLISTXML(@SPOUSE_ID);
                        set @SECURITYATTRIBUTES = dbo.UFN_CONSTITUENT_GETSECURITYATTRIBUTES_FORUPDATEBATCH_TOITEMLISTXML(@ID);
                        set @CONSTITUENT_SITES = dbo.UFN_CONSTITUENT_GETSITES_FORUPDATEBATCH_TOITEMLISTXML(@ID);
                        set @INTERESTS = dbo.UFN_CONSTITUENT_GETINTERESTS_FORUPDATEBATCH_TOITEMLISTXML(@ID);
                        --set @EDUCATIONALINVOLVEMENTS = dbo.UFN_CONSTITUENT_GETEDUCATIONALINVOLVEMENTS_FORUPDATEBATCH_TOITEMLISTXML(@ID);

                        set @ADDRESSES = dbo.UFN_CONSTITUENT_GETADDRESSES_FORUPDATEBATCH_TOITEMLISTXML(@ID);
                        set @PHONES = dbo.UFN_CONSTITUENT_GETPHONES_FORUPDATEBATCH_TOITEMLISTXML(@ID);
                        set @EMAILADDRESSES = dbo.UFN_CONSTITUENT_GETEMAILADDRESSES_WITHDATES_FORUPDATEBATCH_TOITEMLISTXML(@ID);
                        set @SOCIALMEDIAACCOUNTS = dbo.UFN_CONSTITUENT_GETSOCIALMEDIAACCOUNTS_FORUPDATEBATCH_TOITEMLISTXML(@ID);
                        set @CONSTITUENCIES = dbo.UFN_CONSTITUENT_GETCONSTITUENCIES_FORUPDATEBATCH_TOITEMLISTXML(@ID);
                        -- we need to load the SC inline as we cannot use the newid() function in a UFN

                        set @SOLICITCODES = (
                                                                 select
                                                                        newid() ID,
                                                                        ID CONSTITUENTSOLICITCODEID,
                                                                    @ID [BATCHCONSTITUENTUPDATEID],
                                                                    [SOLICITCODEID],
                                                                    [STARTDATE],
                                                                    [ENDDATE],
                                                                    [COMMENTS],
                                                                    [SEQUENCE],
                                                                    [CONSENTPREFERENCECODE],
                                                                    [SOURCECODEID],
                                                                    [SOURCEFILEPATH],
                                                                    [PRIVACYPOLICYFILEPATH],
                                                                    [SUPPORTINGINFORMATION],
                                                                    [CONSENTSTATEMENT]
                                                                from    
                                                                    dbo.CONSTITUENTSOLICITCODE
                                                                where
                                                                    [CONSTITUENTID] = @ID
                                                                order by 
                                                                    [SEQUENCE]
                                                                for xml path('ITEM'),type,elements,root('SOLICITCODES'),BINARY BASE64)

                        --Do not set @RELATIONSHIPS since we don't want to edit existing relationships for a constituent.


                                                select
                                                    @BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1,
                                                    @BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR = MATCHFACTOR,
                                                    @BUSINESS_PRIMARYRECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODEID
                                                from
                                                    dbo.REVENUERECOGNITIONDEFAULT
                                                where
                                                    SOURCECONSTITUENTID = @BUSINESS_ID and
                                                    RECIPIENTCONSTITUENTID = @ID and
                                                    PREVENTRECOGNITIONSDEFAULTING = 0;

                                                select
                                                    @BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1,
                                                    @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR = MATCHFACTOR,
                                                    @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODEID
                                                from
                                                    dbo.REVENUERECOGNITIONDEFAULT
                                                where
                                                    SOURCECONSTITUENTID = @ID and
                                                    RECIPIENTCONSTITUENTID = @BUSINESS_ID and
                                                    PREVENTRECOGNITIONSDEFAULTING = 0;

                                                set @BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = coalesce(@BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS, 0);
                                                set @BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = coalesce(@BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS, 0);

                                                select
                                                    @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1,
                                                    @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR = MATCHFACTOR,
                                                    @SPOUSE_PRIMARYRECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODEID
                                                from
                                                    dbo.REVENUERECOGNITIONDEFAULT
                                                where
                                                    SOURCECONSTITUENTID = @SPOUSE_ID and
                                                    RECIPIENTCONSTITUENTID = @ID and
                                                    PREVENTRECOGNITIONSDEFAULTING = 0;

                                                select
                                                    @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1,
                                                    @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR = MATCHFACTOR,
                                                    @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODEID
                                                from
                                                    dbo.REVENUERECOGNITIONDEFAULT
                                                where
                                                    SOURCECONSTITUENTID = @ID and
                                                    RECIPIENTCONSTITUENTID = @SPOUSE_ID and
                                                    PREVENTRECOGNITIONSDEFAULTING = 0;

                                                set @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = coalesce(@SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS, 0);
                                                set @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = coalesce(@SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS, 0);
                                                set @REMOVESPOUSE = 0;

                        set @NAMEFORMATS = null;
                        set @REQUESTSNOEMAIL = 0;

                        return 0;