USP_DATAFORMTEMPLATE_ADD_ORGTOIND_CONTACTRELATIONSHIP

The save procedure used by the add dataform template "Contact Relationship Organization to Individual Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@KEYNAME	nvarchar(100)	IN	Last name
@FIRSTNAME	nvarchar(50)	IN	First name
@RECIPROCALCONSTITUENTID	uniqueidentifier	IN	Related constituent
@RECIPROCALTYPECODEID	uniqueidentifier	IN	Reciprocal relationship type
@RELATIONSHIPTYPECODEID	uniqueidentifier	IN	Relationship type
@CONTACTTYPECODEID	uniqueidentifier	IN	Contact type
@ADDADDRESS	bit	IN
@ADDRESSTYPECODEID	uniqueidentifier	IN	Type
@PRIMARY	bit	IN	Set as primary address
@DONOTMAIL	bit	IN	Do not send mail to this address
@STARTDATE	UDT_MONTHDAY	IN	Start date
@ENDDATE	UDT_MONTHDAY	IN	End date
@COUNTRYID	uniqueidentifier	IN	Country
@STATEID	uniqueidentifier	IN	State
@ADDRESSBLOCK	nvarchar(150)	IN	Address
@CITY	nvarchar(50)	IN	City
@POSTCODE	nvarchar(12)	IN	ZIP
@CART	nvarchar(10)	IN	CART
@DPC	nvarchar(8)	IN	DPC
@LOT	nvarchar(5)	IN	LOT
@UPDATEMATCHINGSPOUSEADDRESSES	bit	IN	Update matching address information for spouse
@OMITFROMVALIDATION	bit	IN	Omit this address from validation
@COUNTYCODEID	uniqueidentifier	IN	County
@CONGRESSIONALDISTRICTCODEID	uniqueidentifier	IN	Congressional district
@STATEHOUSEDISTRICTCODEID	uniqueidentifier	IN	State house district
@STATESENATEDISTRICTCODEID	uniqueidentifier	IN	State senate district
@LOCALPRECINCTCODEID	uniqueidentifier	IN	Local precinct
@INFOSOURCECODEID	uniqueidentifier	IN	Info source
@REGIONCODEID	uniqueidentifier	IN	Region
@LASTVALIDATIONATTEMPTDATE	datetime	IN	Last attempt
@VALIDATIONMESSAGE	nvarchar(200)	IN	Validation message
@CERTIFICATIONDATA	int	IN
@DONOTMAILREASONCODEID	uniqueidentifier	IN	Reason
@SKIP_ADDING_SECURITYGROUPS	bit	IN	Skip adding security groups
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SKIP_ADDING_SITES	bit	IN	Skip adding sites
@ADDPHONE	bit	IN
@PHONETYPECODEID	uniqueidentifier	IN	Type
@NUMBER	nvarchar(100)	IN	Number
@PHONECOUNTRYID	uniqueidentifier	IN	Country
@PHONESTARTTIME	UDT_HOURMINUTE	IN	Call after
@PHONEENDTIME	UDT_HOURMINUTE	IN	Call before
@PHONESTARTDATE	date	IN	Start date
@PHONEPRIMARY	bit	IN	Set as primary phone number
@DONOTCALL	bit	IN	Do not call this phone number
@DONOTCALLREASONCODEID	uniqueidentifier	IN	Reason
@PHONEISCONFIDENTIAL	bit	IN	This phone number is confidential
@PHONEINFOSOURCECODEID	uniqueidentifier	IN	Information source
@PHONEINFOSOURCECOMMENTS	nvarchar(256)	IN	Comments
@ADDEMAILADDRESS	bit	IN
@EMAILADDRESSTYPECODEID	uniqueidentifier	IN	Type
@EMAILADDRESS	UDT_EMAILADDRESS	IN	Email address
@EMAILADDRESSPRIMARY	bit	IN	Set as primary email address
@DONOTEMAIL	bit	IN	Do not send email to this address
@EMAILADDRESSINFOSOURCECODEID	uniqueidentifier	IN	Information source
@EMAILADDRESSINFOSOURCECOMMENTS	nvarchar(256)	IN	Comments

Definition

Copy



                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_ORGTOIND_CONTACTRELATIONSHIP
                    (
                        @ID uniqueidentifier = null output,
                        @CHANGEAGENTID uniqueidentifier = null,    
                        @CONSTITUENTID uniqueidentifier,
                        @KEYNAME nvarchar(100),
                        @FIRSTNAME nvarchar(50) = null,
                        @RECIPROCALCONSTITUENTID uniqueidentifier = null,
                        @RECIPROCALTYPECODEID uniqueidentifier,
                        @RELATIONSHIPTYPECODEID uniqueidentifier,
                        @CONTACTTYPECODEID uniqueidentifier = null,
                        @ADDADDRESS bit = 0,
                        @ADDRESSTYPECODEID uniqueidentifier = null,
                        @PRIMARY bit = 0,
                        @DONOTMAIL bit = 0,
                        @STARTDATE dbo.UDT_MONTHDAY = '0000',
                        @ENDDATE dbo.UDT_MONTHDAY = '0000',
                        @COUNTRYID uniqueidentifier = null,
                        @STATEID uniqueidentifier = null,
                        @ADDRESSBLOCK nvarchar(150) = '',
                        @CITY nvarchar(50) = '',
                        @POSTCODE nvarchar(12) = '',
                        @CART nvarchar(10) = '',
                        @DPC nvarchar(8) = '',
                        @LOT nvarchar(5) = '',
                        @UPDATEMATCHINGSPOUSEADDRESSES bit = 0,
                        @OMITFROMVALIDATION bit = 0,
                        @COUNTYCODEID uniqueidentifier = null,
                        @CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
                        @STATEHOUSEDISTRICTCODEID uniqueidentifier = null,
                        @STATESENATEDISTRICTCODEID uniqueidentifier = null,
                        @LOCALPRECINCTCODEID uniqueidentifier = null,
                        @INFOSOURCECODEID uniqueidentifier = null,
                        @REGIONCODEID uniqueidentifier = null,
                        @LASTVALIDATIONATTEMPTDATE datetime = null,
                        @VALIDATIONMESSAGE nvarchar(200) = '',
                        @CERTIFICATIONDATA integer = 0,
                        @DONOTMAILREASONCODEID uniqueidentifier = null,
                        @SKIP_ADDING_SECURITYGROUPS bit = 0,
                        @CURRENTAPPUSERID uniqueidentifier,
                        @SKIP_ADDING_SITES bit = 0,
                        @ADDPHONE bit = 0,
                        @PHONETYPECODEID uniqueidentifier = null,
                        @NUMBER nvarchar(100) = '',
                        @PHONECOUNTRYID uniqueidentifier = null,
                        @PHONESTARTTIME dbo.UDT_HOURMINUTE = '',
                        @PHONEENDTIME dbo.UDT_HOURMINUTE = '',
                        @PHONESTARTDATE date = null,
                        @PHONEPRIMARY bit = 0,
                        @DONOTCALL bit = 0,
                        @DONOTCALLREASONCODEID uniqueidentifier = null,
                        @PHONEISCONFIDENTIAL bit = 0,
                        @PHONEINFOSOURCECODEID uniqueidentifier = null,
                        @PHONEINFOSOURCECOMMENTS nvarchar(256) = '',
                        @ADDEMAILADDRESS bit = 0,
                        @EMAILADDRESSTYPECODEID uniqueidentifier = null,
                        @EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
                        @EMAILADDRESSPRIMARY bit = 0,
                        @DONOTEMAIL bit = 0,
                        @EMAILADDRESSINFOSOURCECODEID uniqueidentifier = null,
                        @EMAILADDRESSINFOSOURCECOMMENTS nvarchar(256) = ''
                    )
                    as
                        set nocount on;

                        declare @CURRENTDATE datetime;
                        set @CURRENTDATE = getdate();

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

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

                        if @DONOTMAIL = 0 
                          set @DONOTMAILREASONCODEID = null

                        begin try
                            if @RECIPROCALCONSTITUENTID is null
                            begin
                                set @RECIPROCALCONSTITUENTID = newid();

                                insert into dbo.CONSTITUENT(ID,KEYNAME,FIRSTNAME,ISCONSTITUENT,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                                    values (@RECIPROCALCONSTITUENTID,@KEYNAME,@FIRSTNAME,1,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);

                                if coalesce(@SKIP_ADDING_SECURITYGROUPS,0) = 0
                                begin
                                    exec dbo.USP_CONSTITUENT_ASSIGNSECURITYGROUPS_FORNEWRECORD
                                            @APPUSERID = @CURRENTAPPUSERID,
                                            @DATAFORMTEMPLATEID = 'AFB63308-8261-4EF1-8C53-9AA2F5F6822B',
                                            @CONSTITUENTID = @RECIPROCALCONSTITUENTID,
                                            @DATEADDEDTOUSE = @CURRENTDATE,
                                            @CHANGEAGENTID = @CHANGEAGENTID;
                                end

                                if coalesce(@SKIP_ADDING_SITES,0) = 0
                                    exec dbo.USP_CONSTITUENT_ASSIGNSITES_FORNEWRECORD
                                        @APPUSERID = @CURRENTAPPUSERID,
                                        @DATAFORMTEMPLATEID = 'AFB63308-8261-4EF1-8C53-9AA2F5F6822B',
                                        @CONSTITUENTID = @RECIPROCALCONSTITUENTID,
                                        @DATEADDEDTOUSE = @CURRENTDATE,
                                        @CHANGEAGENTID = @CHANGEAGENTID;
                            end

                            else
                            begin
                                exec dbo.USP_CONSTITUENT_CHECKINACTIVEANDDECEASED @RECIPROCALCONSTITUENTID, 'BBERR_INDIVIDUALISINACTIVE', 'BBERR_INDIVIDUALISDECEASED';
                            end

                            if not exists (select 1 from dbo.RELATIONSHIP where @ID = ID)
                            begin
                                insert into dbo.RELATIONSHIP
                                (
                                    ID,
                                    RELATIONSHIPCONSTITUENTID,
                                    RECIPROCALCONSTITUENTID,
                                    RELATIONSHIPTYPECODEID,
                                    RECIPROCALTYPECODEID,
                                    ISCONTACT,
                                    ISPRIMARYCONTACT,
                                    CONTACTTYPECODEID,
                                    ADDEDBYID,
                                    CHANGEDBYID,
                                    DATEADDED,
                                    DATECHANGED
                                )
                                values
                                (
                                    @ID,
                                    @CONSTITUENTID,
                                    @RECIPROCALCONSTITUENTID,
                                    @RELATIONSHIPTYPECODEID,
                                    @RECIPROCALTYPECODEID,
                                    1,
                                    0,
                                    @CONTACTTYPECODEID,
                                    @CHANGEAGENTID,
                                    @CHANGEAGENTID,
                                    @CURRENTDATE,
                                    @CURRENTDATE
                                );


                                if @ADDADDRESS = 1
                                begin
                                    declare @ADDRESSID uniqueidentifier

                                    exec dbo.USP_ADDRESS_CREATE @ADDRESSID output, @CHANGEAGENTID, @RECIPROCALCONSTITUENTID, @ADDRESSTYPECODEID, @PRIMARY, @DONOTMAIL,
                                        @STARTDATE, @ENDDATE, @COUNTRYID, @STATEID, @ADDRESSBLOCK, @CITY, @POSTCODE, @CART, @DPC, @LOT,
                                        @UPDATEMATCHINGSPOUSEADDRESSES, @OMITFROMVALIDATION, @COUNTYCODEID, @CONGRESSIONALDISTRICTCODEID,
                                        @STATEHOUSEDISTRICTCODEID, @STATESENATEDISTRICTCODEID, @LOCALPRECINCTCODEID, @INFOSOURCECODEID,
                                        @REGIONCODEID, @LASTVALIDATIONATTEMPTDATE, @VALIDATIONMESSAGE, @CERTIFICATIONDATA, @DONOTMAILREASONCODEID

                                    if @ADDRESSID is not null
                                    begin
                                        update ADDRESS
                                        set
                                            RELATIONSHIPID=@ID,
                                            CHANGEDBYID = @CHANGEAGENTID,
                                            DATECHANGED = @CURRENTDATE
                                        where 
                                            ID=@ADDRESSID;
                                    end
                                end

                                if @ADDPHONE = 1
                                begin
                                    declare @PHONEID uniqueidentifier;

                                    exec dbo.USP_PHONE_CREATE @PHONEID output, @CHANGEAGENTID, @CURRENTDATE,
                                        @RECIPROCALCONSTITUENTID, @PHONETYPECODEID, @NUMBER, @PHONEPRIMARY,
                                        @PHONESTARTTIME, @PHONEENDTIME, @PHONESTARTDATE, @DONOTCALL, @DONOTCALLREASONCODEID,
                                        @PHONEINFOSOURCECODEID, @PHONEINFOSOURCECOMMENTS, @PHONECOUNTRYID, @PHONEISCONFIDENTIAL,
                                        0, @ID;
                                end

                                if @ADDEMAILADDRESS = 1
                                begin
                                    if isnull(@EMAILADDRESS, '') = ''
                                        raiserror('ERR_EMAILADDRESSREQUIRED', 13, 1);

                                    declare @EMAILADDRESSID uniqueidentifier;

                                    exec dbo.USP_EMAILADDRESS_CREATE @EMAILADDRESSID output, @CHANGEAGENTID, @CURRENTDATE,
                                        @RECIPROCALCONSTITUENTID, @EMAILADDRESSTYPECODEID, @EMAILADDRESS, @EMAILADDRESSPRIMARY,
                                        @DONOTEMAIL, @EMAILADDRESSINFOSOURCECODEID, @EMAILADDRESSINFOSOURCECOMMENTS, 0, @ID;
                                end

                                exec dbo.USP_RELATIONSHIPCONFIGURATION_CONFIGURE @CONSTITUENTID, @RECIPROCALCONSTITUENTID, @RELATIONSHIPTYPECODEID, @RECIPROCALTYPECODEID, @CHANGEAGENTID, @CURRENTDATE

                            end
                        end try

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

                        return 0;