USP_DATAFORMTEMPLATE_LOAD_PROFILEUPDATEBATCHCONSTITUENTINFORMATION

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@FIRSTNAME	nvarchar(100)	INOUT
@MIDDLENAME	nvarchar(50)	INOUT
@KEYNAME	nvarchar(100)	INOUT
@NAME	nvarchar(308)	INOUT
@NICKNAME	nvarchar(50)	INOUT
@MAIDENNAME	nvarchar(100)	INOUT
@TITLECODE	nvarchar(100)	INOUT
@SUFFIXCODE	nvarchar(100)	INOUT
@TITLE2CODE	nvarchar(100)	INOUT
@SUFFIX2CODE	nvarchar(100)	INOUT
@REQUESTNOEMAIL	bit	INOUT
@LOOKUPID	nvarchar(36)	INOUT
@MARITALSTATUSCODE	nvarchar(100)	INOUT
@GENDER	nvarchar(100)	INOUT
@BIRTHDATE	UDT_FUZZYDATE	INOUT
@ISDECEASED	bit	INOUT
@DECEASEDDATE	UDT_FUZZYDATE	INOUT
@PHONES	xml	INOUT
@ADDRESSES	xml	INOUT
@EMAILADDRESSES	xml	INOUT
@WEBADDRESS	nvarchar(100)	INOUT
@DATALOADED	bit	INOUT
@POSITION	nvarchar(200)	INOUT
@INDUSTRY	nvarchar(200)	INOUT
@COUNTRY	nvarchar(200)	INOUT
@COUNTRYABBREV	nvarchar(10)	INOUT
@ADDRESSBLOCK	nvarchar(300)	INOUT
@CITY	nvarchar(100)	INOUT
@STATE	nvarchar(200)	INOUT
@POSTCODE	nvarchar(24)	INOUT
@FORMATTEDADDRESS	nvarchar(300)	INOUT
@COUNTRYADDRESSFORMATID	uniqueidentifier	INOUT
@REGION	nvarchar(200)	INOUT
Definition

Copy

CREATE procedure dbo.USP_DATAFORMTEMPLATE_LOAD_PROFILEUPDATEBATCHCONSTITUENTINFORMATION
                (
          @ID uniqueidentifier,
          @FIRSTNAME nvarchar(100) = null output,
          @MIDDLENAME nvarchar(50) = null output,
          @KEYNAME nvarchar(100) = null output,
          @NAME nvarchar(308) = null output,
          @NICKNAME nvarchar(50) = null output,
          @MAIDENNAME nvarchar(100) = null output,
          @TITLECODE nvarchar(100) = null output,
          @SUFFIXCODE nvarchar(100) = null output,
          @TITLE2CODE nvarchar(100) = null output,
          @SUFFIX2CODE nvarchar(100) = null output,
          @REQUESTNOEMAIL bit = null output,
          @LOOKUPID nvarchar(36) = null output,
          @MARITALSTATUSCODE nvarchar(100) = null output,
          @GENDER nvarchar(100) = null output,
          @BIRTHDATE dbo.UDT_FUZZYDATE = null output,
          @ISDECEASED bit = null output,
          @DECEASEDDATE dbo.UDT_FUZZYDATE = null output,
          @PHONES xml = null output,
          @ADDRESSES xml = null output,
          @EMAILADDRESSES xml = null output,
          @WEBADDRESS nvarchar(100) = null output,
          @DATALOADED bit = 0 output,
          @POSITION nvarchar(200) = null output,
          @INDUSTRY nvarchar(200) = null output,
          @COUNTRY nvarchar(200) = null output,
          @COUNTRYABBREV nvarchar(10) = null output,
          @ADDRESSBLOCK nvarchar(300) = null output,
          @CITY nvarchar(100) = null output,
          @STATE nvarchar(200) = null output,
          @POSTCODE nvarchar(24) = null output,
          @FORMATTEDADDRESS nvarchar(300) = null output,
          @COUNTRYADDRESSFORMATID uniqueidentifier = null output,
          @REGION nvarchar(200) = null output    
                )
                as
                    set nocount on;

          declare @BBNCID int;
                    select @BBNCID = [SEQUENCEID] from dbo.CONSTITUENT where [ID] = @ID;

          declare @BIOTABLE table(
                  BBNCID integer,
            BIRTHDATE dbo.UDT_FUZZYDATE, 
                      FIRSTNAME nvarchar(50),
                      NAME nvarchar(154),
                      GENDER nvarchar(100),
                      KEYNAME nvarchar(100),
                      MAIDENNAME nvarchar(100),
                      MIDDLENAME nvarchar(50),
                      NICKNAME nvarchar(50),
                      TITLECODE nvarchar(100),
                      TITLE2CODE nvarchar(100),
                      SUFFIXCODE nvarchar(100),
                      SUFFIX2CODE nvarchar(100),
                      REQUESTNOEMAIL bit,
                      LOOKUPID nvarchar(36),
                      MARITALSTATUSCODE nvarchar(100),
                      WEBADDRESS nvarchar(2047),
                      ISORGANIZATION bit,
                      INDUSTRYCODE nvarchar(100),
                      NUMEMPLOYEES int,
                      NUMSUBSIDIARIES int,
                      ISDECEASED bit,
                      DECEASEDDATE dbo.UDT_FUZZYDATE,
            TARGET nvarchar(200),
            INCOME nvarchar(200),
            GIVESANONYMOUSLY bit
                      );

                    insert into @BIOTABLE
                    (
                        BIRTHDATE, 
                        FIRSTNAME,
                        NAME,
                        GENDER,
                        KEYNAME,
                        MAIDENNAME,
                        MIDDLENAME,
                        NICKNAME,
                        TITLECODE,
                        TITLE2CODE,
                        SUFFIXCODE,
                        SUFFIX2CODE,
                        REQUESTNOEMAIL,
                        LOOKUPID,
                        MARITALSTATUSCODE,
                        WEBADDRESS,
                        ISORGANIZATION,
                        INDUSTRYCODE,
                        NUMEMPLOYEES,
                        NUMSUBSIDIARIES,
                        ISDECEASED,
                        DECEASEDDATE,
                        TARGET,
                        INCOME,
            GIVESANONYMOUSLY
                    )
                    exec dbo.USP_BBNC_CONSTITBIODATA @BBNCID;



          declare @PRIMARYBUSINESSTABLE table(
                  BBNCID integer,
            NAME nvarchar(308),
            POSITION nvarchar(200),
            INDUSTRY nvarchar(200),
            COUNTRYDESCRIPTION nvarchar(200),
            COUNTRYABBREV nvarchar(10),
            ADDRESSBLOCK nvarchar(300),
            CITY nvarchar(100),
            STATE nvarchar(200),
            POSTCODE nvarchar(24),
            FORMATTEDADDRESS nvarchar(300),
            COUNTRYADDRESSFORMATID uniqueidentifier,
            REGION nvarchar(200)
          );

          insert into @PRIMARYBUSINESSTABLE (
            NAME,
            POSITION,
            INDUSTRY,
            COUNTRYDESCRIPTION,
            COUNTRYABBREV,
            ADDRESSBLOCK,
            CITY,
            STATE,
            POSTCODE,
            FORMATTEDADDRESS,
            COUNTRYADDRESSFORMATID,
            REGION
          )
          exec dbo.USP_BBNC_CONSTITPRIMARYBUSINESSDATA @BBNCID;

          update @BIOTABLE set BBNCID  = @BBNCID
          update @PRIMARYBUSINESSTABLE set BBNCID = @BBNCID 

          select top 1 
            @ID = @ID,
                        @FIRSTNAME = [FIRSTNAME],
            @MIDDLENAME = [MIDDLENAME],
            @KEYNAME = [KEYNAME],
                        @NAME = BT.[NAME],
                        @MAIDENNAME = [MAIDENNAME],
                        @TITLECODE = [TITLECODE],
                        @NICKNAME = [NICKNAME],
                        @SUFFIXCODE = [SUFFIXCODE],
                        @TITLE2CODE = [TITLE2CODE],
                        @SUFFIX2CODE = [SUFFIX2CODE],
                        @REQUESTNOEMAIL = [REQUESTNOEMAIL],
                        @LOOKUPID = [LOOKUPID],
                        @MARITALSTATUSCODE = [MARITALSTATUSCODE],
            @GENDER = [GENDER],            
            @BIRTHDATE = [BIRTHDATE],
            @ISDECEASED = [ISDECEASED],
                        @DECEASEDDATE = DECEASEDDATE,
            @PHONES = dbo.UFN_EXCHANGECONTACTSYNC_GETCONSTITUENTPHONES_TOITEMLISTXML(@ID),
                        @ADDRESSES = dbo.UFN_EXCHANGECONTACTSYNC_GETCONSTITUENTADDRESSES_TOITEMLISTXML(@ID),
                        @EMAILADDRESSES = dbo.UFN_EXCHANGECONTACTSYNC_GETCONSTITUENTEMAILADDRESSES_TOITEMLISTXML(@ID),
                        @WEBADDRESS = [WEBADDRESS],
            @NAME = PBT.[NAME],
            @POSITION = PBT.[POSITION],
            @INDUSTRY = PBT.[INDUSTRY],
            @COUNTRY = PBT.[COUNTRYDESCRIPTION],
            @COUNTRYABBREV = PBT.[COUNTRYABBREV],           
            @ADDRESSBLOCK = PBT.[ADDRESSBLOCK],
            @CITY = PBT.[CITY],
            @STATE = PBT.[STATE],
            @POSTCODE = PBT.[POSTCODE],
            @FORMATTEDADDRESS = PBT.[FORMATTEDADDRESS],
            @COUNTRYADDRESSFORMATID = PBT.[COUNTRYADDRESSFORMATID],
            @REGION = PBT.[REGION],
                        @DATALOADED = 1
                    from @BIOTABLE BT
          left join @PRIMARYBUSINESSTABLE PBT on BT.BBNCID = PBT.BBNCID;