USP_DATAFORMTEMPLATE_ADD_INDIVIDUALRECORD

The save procedure used by the add dataform template "Individual Record Add Data Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@LASTNAME	nvarchar(100)	IN	Last name
@FIRSTNAME	nvarchar(50)	IN	First name
@MIDDLENAME	nvarchar(50)	IN	Middle name
@MAIDENNAME	nvarchar(100)	IN	Maiden name
@NICKNAME	nvarchar(50)	IN	Nickname
@TITLECODEID	uniqueidentifier	IN	Title
@SUFFIXCODEID	uniqueidentifier	IN	Suffix
@GENDERCODE	tinyint	IN	Gender
@BIRTHDATE	UDT_FUZZYDATE	IN	Birth date
@ADDRESS_ADDRESSTYPECODEID	uniqueidentifier	IN	Address type
@ADDRESS_DONOTMAIL	bit	IN	Do not send mail to this address
@ADDRESS_COUNTRYID	uniqueidentifier	IN	Country
@ADDRESS_STATEID	uniqueidentifier	IN	State
@ADDRESS_ADDRESSBLOCK	nvarchar(150)	IN	Address
@ADDRESS_CITY	nvarchar(50)	IN	City
@ADDRESS_POSTCODE	nvarchar(12)	IN	ZIP
@PHONE_PHONETYPECODEID	uniqueidentifier	IN	Phone type
@PHONE_NUMBER	nvarchar(100)	IN	Phone number
@EMAILADDRESS_EMAILADDRESSTYPECODEID	uniqueidentifier	IN	Email type
@EMAILADDRESS_EMAILADDRESS	UDT_EMAILADDRESS	IN	Email address
@MARITALSTATUSCODEID	uniqueidentifier	IN	Marital status
@SKIP_ADDING_SECURITYGROUPS	bit	IN	Skip adding security groups
@SPOUSEID	uniqueidentifier	IN	Full name
@EXISTINGSPOUSE	bit	IN	Search existing individuals
@SPOUSE_LASTNAME	nvarchar(100)	IN	Last name
@SPOUSE_FIRSTNAME	nvarchar(50)	IN	First name
@SPOUSE_MIDDLENAME	nvarchar(50)	IN	Middle name
@SPOUSE_MAIDENNAME	nvarchar(100)	IN	Maiden name
@SPOUSE_NICKNAME	nvarchar(50)	IN	Nickname
@SPOUSE_TITLECODEID	uniqueidentifier	IN	Title
@SPOUSE_SUFFIXCODEID	uniqueidentifier	IN	Suffix
@SPOUSE_GENDERCODE	tinyint	IN	Gender
@SPOUSE_BIRTHDATE	UDT_FUZZYDATE	IN	Birth date
@SPOUSE_RECIPROCALTYPECODEID	uniqueidentifier	IN	Reciprocal relationship type
@SPOUSE_RELATIONSHIPTYPECODEID	uniqueidentifier	IN	Relationship type
@COPYPRIMARYINFORMATION	bit	IN	Copy primary information
@SPOUSE_STARTDATE	datetime	IN	Start date
@PRIMARYSOFTCREDITRELATIONSHIPEXISTS	bit	IN	Soft credit individual for constituent's payments
@PRIMARYSOFTCREDITMATCHFACTOR	decimal(5, 2)	IN	Recognition match percent
@RECIPROCALSOFTCREDITRELATIONSHIPEXISTS	bit	IN	Soft credit constituent for individual's payments
@RECIPROCALSOFTCREDITMATCHFACTOR	decimal(5, 2)	IN	Recognition match percent
@ORGANIZATIONID	uniqueidentifier	IN	Org. name
@EXISTINGORGANIZATION	bit	IN	Search existing organizations
@ORGANIZATION_NAME	nvarchar(100)	IN	Org. name
@ORGANIZATION_ADDRESSTYPECODEID	uniqueidentifier	IN	Address type
@ORGANIZATION_COUNTRYID	uniqueidentifier	IN	Country
@ORGANIZATION_STATEID	uniqueidentifier	IN	State
@ORGANIZATION_ADDRESSBLOCK	nvarchar(150)	IN	Address
@ORGANIZATION_CITY	nvarchar(50)	IN	City
@ORGANIZATION_POSTCODE	nvarchar(12)	IN	ZIP
@ORGANIZATION_DONOTMAIL	bit	IN	Do not send mail to this address
@ORGANIZATION_PHONETYPECODEID	uniqueidentifier	IN	Phone type
@ORGANIZATION_NUMBER	nvarchar(100)	IN	Phone number
@ORGANIZATION_RECIPROCALTYPECODEID	uniqueidentifier	IN	Reciprocal relationship type
@ORGANIZATION_RELATIONSHIPTYPECODEID	uniqueidentifier	IN	Relationship type
@ORGANIZATION_STARTDATE	datetime	IN	Start date
@ORGANIZATION_ENDDATE	datetime	IN	End date
@ISCONTACT	bit	IN
@ISPRIMARYCONTACT	bit	IN	Primary contact
@CONTACTTYPECODEID	uniqueidentifier	IN	Contact type
@POSITION	nvarchar(100)	IN	Job title
@ISMATCHINGGIFTRELATIONSHIP	bit	IN	This organization will match individual's contributions
@RECIPROCALRECOGNITIONTYPECODEID	uniqueidentifier	IN	Recognition credit type
@PRIMARYRECOGNITIONTYPECODEID	uniqueidentifier	IN	Recognition credit type
@ADDRESS_OMITFROMVALIDATION	bit	IN	Omit from validation
@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(200)	IN
@ADDRESS_CERTIFICATIONDATA	int	IN
@ORGANIZATION_OMITFROMVALIDATION	bit	IN	Omit from validation
@ORGANIZATION_CART	nvarchar(10)	IN
@ORGANIZATION_DPC	nvarchar(8)	IN
@ORGANIZATION_LOT	nvarchar(5)	IN
@ORGANIZATION_COUNTYCODEID	uniqueidentifier	IN
@ORGANIZATION_CONGRESSIONALDISTRICTCODEID	uniqueidentifier	IN
@ORGANIZATION_LASTVALIDATIONATTEMPTDATE	datetime	IN
@ORGANIZATION_VALIDATIONMESSAGE	nvarchar(200)	IN
@ORGANIZATION_CERTIFICATIONDATA	int	IN
@ISSPOUSERELATIONSHIP	bit	IN	This is the spouse relationship for individual
@HOUSEHOLDCOPYPRIMARYCONTACTINFO	bit	IN	Copy individual's primary contact information
@ADDRESS_DONOTMAILREASONCODEID	uniqueidentifier	IN	Reason
@ORGANIZATION_DONOTMAILREASONCODEID	uniqueidentifier	IN	Reason
@JOBCATEGORYCODEID	uniqueidentifier	IN	Category
@CAREERLEVELCODEID	uniqueidentifier	IN	Career level
@ADDRESS_INFOSOURCECODEID	uniqueidentifier	IN	Information source
@ORGANIZATION_INFOSOURCECODEID	uniqueidentifier	IN	Information source
@TITLE2CODEID	uniqueidentifier	IN	Title 2
@SUFFIX2CODEID	uniqueidentifier	IN	Suffix 2
@SPOUSE_TITLE2CODEID	uniqueidentifier	IN	Title 2
@SPOUSE_SUFFIX2CODEID	uniqueidentifier	IN	Suffix 2
@SKIP_ADDING_SITES	bit	IN	Skip adding sites
@PHONE2_PHONETYPECODEID	uniqueidentifier	IN	Phone type 2
@PHONE2_NUMBER	nvarchar(100)	IN	Phone number 2
@GENDERCODEID	uniqueidentifier	IN
@SPOUSE_GENDERCODEID	uniqueidentifier	IN

Definition

Copy



                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_INDIVIDUALRECORD
                    (
                        @ID                                        uniqueidentifier = null output,
                        @CURRENTAPPUSERID                        uniqueidentifier,
                        @CHANGEAGENTID                            uniqueidentifier = null,
                        @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) = '',
                        @PHONE_PHONETYPECODEID                    uniqueidentifier = null,
                        @PHONE_NUMBER                            nvarchar(100) = '',
                        @EMAILADDRESS_EMAILADDRESSTYPECODEID    uniqueidentifier = null,
                        @EMAILADDRESS_EMAILADDRESS                dbo.UDT_EMAILADDRESS = '',                        
                        @MARITALSTATUSCODEID                    uniqueidentifier = null,
                        @SKIP_ADDING_SECURITYGROUPS                bit=0,

                        --Spouse Variables

                        @SPOUSEID                                uniqueidentifier = null,
                        @EXISTINGSPOUSE                            bit = 0,
                        @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,                    
                        @PRIMARYSOFTCREDITRELATIONSHIPEXISTS    bit = 0,
                        @PRIMARYSOFTCREDITMATCHFACTOR            decimal(5,2) = 100,
                        @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = 0,
                        @RECIPROCALSOFTCREDITMATCHFACTOR        decimal(5,2) = 100,

                        --Organization variables

                        @ORGANIZATIONID                            uniqueidentifier = null,
                        @EXISTINGORGANIZATION                    bit = 0,
                        @ORGANIZATION_NAME                        nvarchar(100) = '',
                        @ORGANIZATION_ADDRESSTYPECODEID            uniqueidentifier = null,
                        @ORGANIZATION_COUNTRYID                    uniqueidentifier = null,
                        @ORGANIZATION_STATEID                    uniqueidentifier = null,
                        @ORGANIZATION_ADDRESSBLOCK                nvarchar(150) = '',
                        @ORGANIZATION_CITY                        nvarchar(50) = '',
                        @ORGANIZATION_POSTCODE                    nvarchar(12) = '',
                        @ORGANIZATION_DONOTMAIL                    bit = 0,
                        @ORGANIZATION_PHONETYPECODEID            uniqueidentifier = null,
                        @ORGANIZATION_NUMBER                    nvarchar(100) = '',                    
                        @ORGANIZATION_RECIPROCALTYPECODEID        uniqueidentifier = null,
                        @ORGANIZATION_RELATIONSHIPTYPECODEID    uniqueidentifier = null,
                        @ORGANIZATION_STARTDATE                    datetime = null,
                        @ORGANIZATION_ENDDATE                    datetime = null,
                        @ISCONTACT                                bit = 0,
                        @ISPRIMARYCONTACT                        bit = 0,
                        @CONTACTTYPECODEID                        uniqueidentifier = null,
                        @POSITION                                nvarchar(100) = '',    
                        @ISMATCHINGGIFTRELATIONSHIP                bit = 0,

                        -- Additional spouse variables

                        @RECIPROCALRECOGNITIONTYPECODEID        uniqueidentifier = null,
                        @PRIMARYRECOGNITIONTYPECODEID        uniqueidentifier = null,

                        -- 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(200) = '',
                        @ADDRESS_CERTIFICATIONDATA integer = 0,

                        @ORGANIZATION_OMITFROMVALIDATION bit = 0,
                        @ORGANIZATION_CART nvarchar(10) = '',
                        @ORGANIZATION_DPC nvarchar(8) = '',
                        @ORGANIZATION_LOT nvarchar(5) = '',
                        @ORGANIZATION_COUNTYCODEID uniqueidentifier = null,
                        @ORGANIZATION_CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
                        @ORGANIZATION_LASTVALIDATIONATTEMPTDATE datetime = null,
                        @ORGANIZATION_VALIDATIONMESSAGE nvarchar(200) = '',
                        @ORGANIZATION_CERTIFICATIONDATA integer = 0,
                        --Individual's relationship variable

                        @ISSPOUSERELATIONSHIP bit = 1,

                        -- Household variables

                        @HOUSEHOLDCOPYPRIMARYCONTACTINFO bit = 0,
                        @ADDRESS_DONOTMAILREASONCODEID uniqueidentifier = null,
                        @ORGANIZATION_DONOTMAILREASONCODEID uniqueidentifier = null,

                        -- relationship (employee/employer) job info

                        @JOBCATEGORYCODEID uniqueidentifier = null,
                        @CAREERLEVELCODEID uniqueidentifier = null,
                        @ADDRESS_INFOSOURCECODEID uniqueidentifier = null,
                        @ORGANIZATION_INFOSOURCECODEID uniqueidentifier = null,

                        @TITLE2CODEID uniqueidentifier = null,
                        @SUFFIX2CODEID uniqueidentifier = null,
                        @SPOUSE_TITLE2CODEID uniqueidentifier = null,
                        @SPOUSE_SUFFIX2CODEID uniqueidentifier = null,
                        @SKIP_ADDING_SITES bit=0,
                        @PHONE2_PHONETYPECODEID uniqueidentifier = null, --keeping for backwards compatibility

                        @PHONE2_NUMBER nvarchar(100) = '',  --keeping for backwards compatibility

                        @GENDERCODEID uniqueidentifier = null,
                        @SPOUSE_GENDERCODEID uniqueidentifier = null
                    ) as 
                    begin
                        set nocount on

                        declare @ORGANIZATION_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = 0;
                        declare @ORGANIZATION_PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2) = 100;
                        declare @ORGANIZATION_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = 0;
                        declare @ORGANIZATION_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2) = 100;
                        declare @ORGANIZATION_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null;
                        declare @ORGANIZATION_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null;

                        --Find primary business relationship recognition defaults.                     

                        select @ORGANIZATION_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1,
                             @ORGANIZATION_PRIMARYRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
                             @ORGANIZATION_PRIMARYSOFTCREDITMATCHFACTOR= RRD.MATCHFACTOR
                        from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD      
                        where RRD.CONSTITUENTTYPECODE=0 and RRD.RELATIONSHIPTYPECODEID=@ORGANIZATION_RELATIONSHIPTYPECODEID

                        select @ORGANIZATION_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1,
                             @ORGANIZATION_RECIPROCALRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
                             @ORGANIZATION_RECIPROCALSOFTCREDITMATCHFACTOR= RRD.MATCHFACTOR
                        from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD      
                        where RRD.CONSTITUENTTYPECODE=1 and RRD.RELATIONSHIPTYPECODEID=@ORGANIZATION_RECIPROCALTYPECODEID


                        begin try
                            exec dbo.USP_INDIVIDUALSPOUSEBUSINESS_ADD
                                @ID output,
                                @CURRENTAPPUSERID,
                                '9d3aff90-42d0-4db0-a4c1-703d25fd1902',
                                @CHANGEAGENTID,
                                @LASTNAME,
                                @FIRSTNAME,
                                @MIDDLENAME,
                                @MAIDENNAME,
                                @NICKNAME,
                                @TITLECODEID,
                                @SUFFIXCODEID,
                                @GENDERCODE,
                                @BIRTHDATE,
                                @ADDRESS_ADDRESSTYPECODEID,
                                @ADDRESS_DONOTMAIL,
                                @ADDRESS_COUNTRYID,
                                @ADDRESS_STATEID,
                                @ADDRESS_ADDRESSBLOCK,
                                @ADDRESS_CITY,
                                @ADDRESS_POSTCODE,
                                @PHONE_PHONETYPECODEID,
                                @PHONE_NUMBER,
                                @EMAILADDRESS_EMAILADDRESSTYPECODEID,
                                @EMAILADDRESS_EMAILADDRESS,
                                @MARITALSTATUSCODEID,
                                @SKIP_ADDING_SECURITYGROUPS,
                                @SPOUSEID,
                                @EXISTINGSPOUSE,
                                @SPOUSE_LASTNAME,
                                @SPOUSE_FIRSTNAME,
                                @SPOUSE_MIDDLENAME,
                                @SPOUSE_MAIDENNAME,
                                @SPOUSE_NICKNAME,
                                @SPOUSE_TITLECODEID,
                                @SPOUSE_SUFFIXCODEID,
                                @SPOUSE_GENDERCODE,
                                @SPOUSE_BIRTHDATE,
                                @SPOUSE_RECIPROCALTYPECODEID,
                                @SPOUSE_RELATIONSHIPTYPECODEID,
                                @COPYPRIMARYINFORMATION,
                                @SPOUSE_STARTDATE,
                                @PRIMARYSOFTCREDITRELATIONSHIPEXISTS,
                                @PRIMARYSOFTCREDITMATCHFACTOR,
                                @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
                                @RECIPROCALSOFTCREDITMATCHFACTOR,
                                @ORGANIZATIONID,
                                @EXISTINGORGANIZATION,
                                @ORGANIZATION_NAME,
                                @ORGANIZATION_ADDRESSTYPECODEID,
                                @ORGANIZATION_COUNTRYID,
                                @ORGANIZATION_STATEID,
                                @ORGANIZATION_ADDRESSBLOCK,
                                @ORGANIZATION_CITY,
                                @ORGANIZATION_POSTCODE,
                                @ORGANIZATION_DONOTMAIL,
                                @ORGANIZATION_PHONETYPECODEID,
                                @ORGANIZATION_NUMBER,
                                @ORGANIZATION_RECIPROCALTYPECODEID,
                                @ORGANIZATION_RELATIONSHIPTYPECODEID,
                                @ORGANIZATION_STARTDATE,
                                @ORGANIZATION_ENDDATE,
                                @ISCONTACT,
                                @ISPRIMARYCONTACT,
                                @CONTACTTYPECODEID,
                                @POSITION,
                                @ISMATCHINGGIFTRELATIONSHIP,
                                @RECIPROCALRECOGNITIONTYPECODEID,
                                @PRIMARYRECOGNITIONTYPECODEID,
                                @ADDRESS_OMITFROMVALIDATION,
                                @ADDRESS_CART,
                                @ADDRESS_DPC,
                                @ADDRESS_LOT,
                                @ADDRESS_COUNTYCODEID,
                                @ADDRESS_CONGRESSIONALDISTRICTCODEID,
                                @ADDRESS_LASTVALIDATIONATTEMPTDATE,
                                @ADDRESS_VALIDATIONMESSAGE,
                                @ADDRESS_CERTIFICATIONDATA,
                                @ORGANIZATION_OMITFROMVALIDATION,
                                @ORGANIZATION_CART,
                                @ORGANIZATION_DPC,
                                @ORGANIZATION_LOT,
                                @ORGANIZATION_COUNTYCODEID,
                                @ORGANIZATION_CONGRESSIONALDISTRICTCODEID,
                                @ORGANIZATION_LASTVALIDATIONATTEMPTDATE,
                                @ORGANIZATION_VALIDATIONMESSAGE,
                                @ORGANIZATION_CERTIFICATIONDATA,
                                @ISSPOUSERELATIONSHIP,
                                @HOUSEHOLDCOPYPRIMARYCONTACTINFO,
                                @ADDRESS_DONOTMAILREASONCODEID,
                                @ORGANIZATION_DONOTMAILREASONCODEID,
                                @SKIP_ADDING_SITES,
                                @JOBCATEGORYCODEID,
                                @CAREERLEVELCODEID,
                                @ADDRESS_INFOSOURCECODEID,
                                @ORGANIZATION_INFOSOURCECODEID,
                                @TITLE2CODEID,
                                @SUFFIX2CODEID,
                                @SPOUSE_TITLE2CODEID,
                                @SPOUSE_SUFFIX2CODEID,
                                1, -- ISCONSTITUENT,

                                @ORGANIZATION_PRIMARYSOFTCREDITRELATIONSHIPEXISTS,
                                @ORGANIZATION_PRIMARYSOFTCREDITMATCHFACTOR,
                                @ORGANIZATION_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
                                @ORGANIZATION_RECIPROCALSOFTCREDITMATCHFACTOR,
                                @ORGANIZATION_PRIMARYRECOGNITIONTYPECODEID,
                                @ORGANIZATION_RECIPROCALRECOGNITIONTYPECODEID,
                                '',
                                '',
                                @GENDERCODEID,
                                @SPOUSE_GENDERCODEID;
                        end try

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

                        return 0;
                    end;