USP_DATAFORMTEMPLATE_PRELOAD_ADD_INDTOINDFROMHOUSEHOLD_RELATIONSHIP

The load procedure used by the edit dataform template "Relationship Individual to Individual From Household Member Add Form"

Parameters

Parameter	Parameter Type	Mode	Description
@RELATIONSHIPANDRECIPROCALCONSTITUENTIDS	nvarchar(72)	IN	Input parameter indicating the context ID for the record being added.
@RECIPROCALCONSTITUENTID	uniqueidentifier	INOUT	Related constituent
@RELATIONSHIPCONSTITUENTID	uniqueidentifier	INOUT	Relationship constituent
@RELATIONSHIPTYPECODEID	uniqueidentifier	INOUT	Relationship type
@RECIPROCALTYPECODEID	uniqueidentifier	INOUT	Reciprocal relationship type
@CONSTITUENTNAME	nvarchar(154)	INOUT
@RECIPROCALCONSTITUENTNAME	nvarchar(154)	INOUT	Related constituent
@STARTDATE	datetime	INOUT	Start date
@ENDDATE	datetime	INOUT	End date
@ISSPOUSE	bit	INOUT	Is spouse?
@PRIMARYSOFTCREDITRELATIONSHIPEXISTS	bit	INOUT	Apply to <Constituent> for revenue from <Reciprocal>
@PRIMARYSOFTCREDITMATCHFACTOR	decimal(5, 2)	INOUT	Recognition credit match percent
@RECIPROCALSOFTCREDITRELATIONSHIPEXISTS	bit	INOUT	Apply to <Reciprocal> for revenue from <Constituent>
@RECIPROCALSOFTCREDITMATCHFACTOR	decimal(5, 2)	INOUT	Recognition credit match percent
@PRIMARYRECOGNITIONTYPECODEID	uniqueidentifier	INOUT	Recognition credit type
@RECIPROCALRECOGNITIONTYPECODEID	uniqueidentifier	INOUT	Recognition credit type
@RECOGNITIONSSETTHROUGHMEMBERDEFAULTALLOTHERMEMBERS	bit	INOUT	Are recognition defaults set through household's members matching all other members

Definition

Copy



                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_PRELOAD_ADD_INDTOINDFROMHOUSEHOLD_RELATIONSHIP
                    (
                        @RELATIONSHIPANDRECIPROCALCONSTITUENTIDS nvarchar(72),
                        @RECIPROCALCONSTITUENTID uniqueidentifier = null output,
                        @RELATIONSHIPCONSTITUENTID uniqueidentifier = null output,                        
                        @RELATIONSHIPTYPECODEID uniqueidentifier = null output,
                        @RECIPROCALTYPECODEID uniqueidentifier = null output,
                        @CONSTITUENTNAME nvarchar(154) = null output,
                        @RECIPROCALCONSTITUENTNAME nvarchar(154) = null output,                                            
                        @STARTDATE datetime = null output,
                        @ENDDATE datetime = null output,
                        @ISSPOUSE bit = null output,
                        @PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = null output,
                        @PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2) = null output,
                        @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = null output,                        
                        @RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2) = null output,
                        @PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null output,
                        @RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null output,
                        @RECOGNITIONSSETTHROUGHMEMBERDEFAULTALLOTHERMEMBERS bit = null output
                    )
                    as
                        set nocount on;

                        set @RELATIONSHIPCONSTITUENTID = left(@RELATIONSHIPANDRECIPROCALCONSTITUENTIDS, 36);
                        set @RECIPROCALCONSTITUENTID = right(@RELATIONSHIPANDRECIPROCALCONSTITUENTIDS, 36);

                        select 
                            @CONSTITUENTNAME = CONSTITUENT.NAME,
                            @RELATIONSHIPTYPECODEID = null,
                            @RECIPROCALTYPECODEID = null,                             
                            @STARTDATE = null,
                            @ENDDATE = null,
                            @ISSPOUSE = 0
                        from 
                            dbo.CONSTITUENT 
                        where
                            CONSTITUENT.ID = @RELATIONSHIPCONSTITUENTID;

                        select
                            @RECIPROCALCONSTITUENTNAME = CONSTITUENT.NAME
                        from
                            dbo.CONSTITUENT
                        where
                            CONSTITUENT.ID = @RECIPROCALCONSTITUENTID;

                        set @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 0;
                        set @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 0;

                        -- If the two constituents are members of the same household and that same household recognizes all members

                        -- for any member's revenue, then the two both have revenue defaults setup and the matchfactor is 100%

                        declare @HOUSEHOLDID uniqueidentifier
                        select 
                            @HOUSEHOLDID = GM1.GROUPID
                        from dbo.GROUPMEMBER GM1
                        inner join dbo.GROUPMEMBER GM2 on GM1.GROUPID = GM2.GROUPID
                        inner join dbo.GROUPDATA GD on GM1.GROUPID = GD.ID
                        where
                            GD.GROUPTYPECODE = 0 and
                            GM1.MEMBERID = @RELATIONSHIPCONSTITUENTID and
                            GM2.MEMBERID = @RECIPROCALCONSTITUENTID

                        if @HOUSEHOLDID is not null
                        begin
                            declare @MEMBERRECOGNIZEOTHERMEMBERSCODE tinyint
                            set @MEMBERRECOGNIZEOTHERMEMBERSCODE = dbo.UFN_HOUSEHOLD_GETMEMBERRECOGNIZEOTHERMEMBERSSETTING(@HOUSEHOLDID)

                            if @MEMBERRECOGNIZEOTHERMEMBERSCODE = 1
                            begin
                                set @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1
                                set @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1
                                set @PRIMARYSOFTCREDITMATCHFACTOR = 100
                                set @RECIPROCALSOFTCREDITMATCHFACTOR = 100
                                set @RECOGNITIONSSETTHROUGHMEMBERDEFAULTALLOTHERMEMBERS = 1
                            end
                        end

                        if @RECOGNITIONSSETTHROUGHMEMBERDEFAULTALLOTHERMEMBERS is null
                        begin
                            select
                                @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1,
                                @PRIMARYSOFTCREDITMATCHFACTOR = MATCHFACTOR,
                                @PRIMARYRECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODEID
                            from dbo.REVENUERECOGNITIONDEFAULT
                            where
                                SOURCECONSTITUENTID = @RELATIONSHIPCONSTITUENTID and
                                RECIPIENTCONSTITUENTID = @RECIPROCALCONSTITUENTID

                            set @PRIMARYSOFTCREDITMATCHFACTOR = coalesce(@PRIMARYSOFTCREDITMATCHFACTOR, 100)

                            select
                                @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1,
                                @RECIPROCALSOFTCREDITMATCHFACTOR = MATCHFACTOR,
                                @RECIPROCALRECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODEID
                            from dbo.REVENUERECOGNITIONDEFAULT
                            where
                                SOURCECONSTITUENTID = @RECIPROCALCONSTITUENTID and
                                RECIPIENTCONSTITUENTID = @RELATIONSHIPCONSTITUENTID

                            set @RECIPROCALSOFTCREDITMATCHFACTOR = coalesce(@RECIPROCALSOFTCREDITMATCHFACTOR, 100)
                        end

                        return 0;