USP_DATAFORMTEMPLATE_ADD_CONSITUENTSPOUSE

The save procedure used by the add dataform template "Constituent Spouse 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.
@RECIPROCALCONSTITUENTID	uniqueidentifier	IN	Full name
@RECIPROCALTYPECODEID	uniqueidentifier	IN	Reciprocal relationship type
@RELATIONSHIPTYPECODEID	uniqueidentifier	IN	Relationship type
@COPYPRIMARYINFORMATION	bit	IN	Copy primary information
@STARTDATE	datetime	IN	Start date
@PRIMARYSOFTCREDITRELATIONSHIPEXISTS	bit	IN	Apply to <Constituent> for revenue from <Reciprocal>
@PRIMARYSOFTCREDITMATCHFACTOR	decimal(5, 2)	IN	Recognition credit match percent
@RECIPROCALSOFTCREDITRELATIONSHIPEXISTS	bit	IN	Apply to <Reciprocal> for revenue from <Constituent>
@RECIPROCALSOFTCREDITMATCHFACTOR	decimal(5, 2)	IN	Recognition credit match percent
@LASTNAME	nvarchar(100)	IN	Last name
@FIRSTNAME	nvarchar(50)	IN	First name
@MIDDLENAME	nvarchar(50)	IN	Middle name
@TITLECODEID	uniqueidentifier	IN	Title
@SUFFIXCODEID	uniqueidentifier	IN	Suffix
@NICKNAME	nvarchar(50)	IN	Nickname
@MAIDENNAME	nvarchar(100)	IN	Maiden name
@GENDERCODE	tinyint	IN	Gender
@BIRTHDATE	UDT_FUZZYDATE	IN	Birth date
@HOUSEHOLDCOPYPRIMARYCONTACTINFO	bit	IN	Copy primary contact information
@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
@TITLE2CODEID	uniqueidentifier	IN	Title 2
@SUFFIX2CODEID	uniqueidentifier	IN	Suffix 2
@INFOSOURCECODEID	uniqueidentifier	IN	Constituent information source

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_CONSITUENTSPOUSE(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,    
    @CONSTITUENTID uniqueidentifier,
    @RECIPROCALCONSTITUENTID uniqueidentifier = null,
    @RECIPROCALTYPECODEID uniqueidentifier,
    @RELATIONSHIPTYPECODEID uniqueidentifier,
    @COPYPRIMARYINFORMATION bit = 1,
    @STARTDATE datetime = null,
    @PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = 0,
    @PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
    @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = 0,
    @RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
    @LASTNAME nvarchar(100) = '',
    @FIRSTNAME nvarchar(50) = '',
    @MIDDLENAME nvarchar(50) = '',
    @TITLECODEID uniqueidentifier = null,
    @SUFFIXCODEID uniqueidentifier = null,
    @NICKNAME nvarchar(50) = '',
    @MAIDENNAME nvarchar(100) = '',
    @GENDERCODE tinyint = 0,
    @BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
    @HOUSEHOLDCOPYPRIMARYCONTACTINFO bit = null,
    @SKIP_ADDING_SECURITYGROUPS bit=0,
    @CURRENTAPPUSERID uniqueidentifier,
    @SKIP_ADDING_SITES bit = 0,
    @TITLE2CODEID uniqueidentifier = null,
    @SUFFIX2CODEID uniqueidentifier = null,
    @INFOSOURCECODEID uniqueidentifier = null
)
as
    set nocount on;

    declare @CURRENTDATE datetime;

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

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

    set @CURRENTDATE = getdate();

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

            -- Determine if the new spouse should be a constituent

            declare @ISCONSTITUENT bit = 0;

            if (dbo.UFN_CONSTITUENCYCRITERIASPOUSE_SPOUSEISCONSTITUENT() = 1)
            begin
                select @ISCONSTITUENT = CONSTITUENT.ISCONSTITUENT
                from dbo.CONSTITUENT
                where CONSTITUENT.ID = @CONSTITUENTID;
            end

            insert into dbo.CONSTITUENT
            (
                ID,
                KEYNAME,
                FIRSTNAME,
                MIDDLENAME,
                TITLECODEID,
                TITLE2CODEID,
                SUFFIXCODEID,
                SUFFIX2CODEID,
                NICKNAME,
                MAIDENNAME,
                GENDERCODE,
                BIRTHDATE,
                ISCONSTITUENT,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            values
            (
                @RECIPROCALCONSTITUENTID,
                @LASTNAME,
                @FIRSTNAME,
                @MIDDLENAME,
                @TITLECODEID,
                @TITLE2CODEID,
                @SUFFIXCODEID,
                @SUFFIX2CODEID,
                @NICKNAME,
                @MAIDENNAME,
                @GENDERCODE,
                @BIRTHDATE,
                @ISCONSTITUENT,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            );

            if @INFOSOURCECODEID is not null
            begin
                insert into dbo.CONSTITUENTORIGINATION
                (
                    ID,
                    INFOSOURCECODEID,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
                )
                values
                (
                    @RECIPROCALCONSTITUENTID,
                    @INFOSOURCECODEID,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                );
            end;

            insert into dbo.[NAMEFORMAT]
                ([CONSTITUENTID]
                ,[NAMEFORMATTYPECODEID]
                ,[NAMEFORMATFUNCTIONID]
                ,[ADDEDBYID]
                ,[CHANGEDBYID]
                ,[DATEADDED]
                ,[DATECHANGED]
                ,[PRIMARYADDRESSEE]
                ,[PRIMARYSALUTATION])
            select
                @RECIPROCALCONSTITUENTID
                ,NFD.NAMEFORMATTYPECODEID
                ,NFD.NAMEFORMATFUNCTIONID
                ,@CHANGEAGENTID
                ,@CHANGEAGENTID
                ,@CURRENTDATE
                ,@CURRENTDATE
                ,NFD.PRIMARYADDRESSEE
                ,NFD.PRIMARYSALUTATION
            from dbo.NAMEFORMATDEFAULT as NFD
            where NFD.APPLYTOCODE = 0                 

            /*Start security groups*/
            if COALESCE(@SKIP_ADDING_SECURITYGROUPS,0) = 0
            begin
                exec dbo.USP_CONSTITUENT_ASSIGNSECURITYGROUPS_FORNEWRECORD
                        @APPUSERID =@CURRENTAPPUSERID,
                        @DATAFORMTEMPLATEID='9d3aff90-42d0-4db0-a4c1-703d25fd1902',
                        @CONSTITUENTID  =@RECIPROCALCONSTITUENTID,
                        @DATEADDEDTOUSE =@CURRENTDATE,
                        @CHANGEAGENTID = @CHANGEAGENTID;
            end
            /*end security groups*/    

            if coalesce(@SKIP_ADDING_SITES,0) = 0
                exec dbo.USP_CONSTITUENT_ASSIGNSITES_FORNEWRECORD 
                    @APPUSERID = @CURRENTAPPUSERID,
                    @DATAFORMTEMPLATEID = '9d3aff90-42d0-4db0-a4c1-703d25fd1902',
                    @CONSTITUENTID = @RECIPROCALCONSTITUENTID,
                    @DATEADDEDTOUSE = @CURRENTDATE,
                    @CHANGEAGENTID = @CHANGEAGENTID;
        end

        declare @SETID uniqueidentifier = newid();

        insert into dbo.RELATIONSHIPSET
        (
            ID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        values
        (
            @SETID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        );

        insert into dbo.RELATIONSHIP
        (
            ID,
            RELATIONSHIPCONSTITUENTID,
            RECIPROCALCONSTITUENTID,
            RELATIONSHIPTYPECODEID,
            RECIPROCALTYPECODEID,
            ISSPOUSE,
            STARTDATE,
            RELATIONSHIPSETID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        values
        (
            @ID,
            @CONSTITUENTID,
            @RECIPROCALCONSTITUENTID,
            @RELATIONSHIPTYPECODEID,
            @RECIPROCALTYPECODEID,
            1,
            @STARTDATE,
            @SETID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        );

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

        if @COPYPRIMARYINFORMATION = 1
            exec dbo.USP_CONSTITUENT_COPYPRIMARYCONTACTINFO @CHANGEAGENTID, @CONSTITUENTID, @RECIPROCALCONSTITUENTID;

        exec dbo.USP_SPOUSERELATIONSHIP_SETUPHOUSEHOLD @CONSTITUENTID, @RECIPROCALCONSTITUENTID, 
                @HOUSEHOLDCOPYPRIMARYCONTACTINFO, @CHANGEAGENTID, @CURRENTDATE,
                @SKIP_ADDING_SECURITYGROUPS,@CURRENTAPPUSERID;
    end try

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

    return 0;