USP_DATALIST_WEALTHINFORMATIONDASHBOARDINDIVIDUALRELATIONSHIPS

This datalist returns a constituent's individual relationships that are used by the wealth information dashboard.
Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Constituent ID
@ISVISIBLE	bit	IN	Is visible
@INCLUDEGROUPS	bit	IN
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_WEALTHINFORMATIONDASHBOARDINDIVIDUALRELATIONSHIPS
                (
                    @CONSTITUENTID uniqueidentifier,
                    @ISVISIBLE bit = 1,
          @INCLUDEGROUPS bit = 1 --Included to maintain backwards compatibility

                )
                as
                    set nocount on;

                    if @ISVISIBLE = 1
                    begin
                        select
                            NF.NAME,
                            RELATIONSHIPTYPECODE.DESCRIPTION RELATIONSHIP,
                            RELATIONSHIP.STARTDATE,
                            RELATIONSHIP.ENDDATE,
                            RELATIONSHIP.ISSPOUSE
                        from dbo.RELATIONSHIP
                        inner join dbo.CONSTITUENT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
                        inner join dbo.RELATIONSHIPTYPECODE on RELATIONSHIPTYPECODE.ID = RELATIONSHIP.RECIPROCALTYPECODEID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
                        where 
                            RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and
                            CONSTITUENT.ISORGANIZATION = 0 and
              (@INCLUDEGROUPS = 1 or CONSTITUENT.ISGROUP = 0)
                        order by
                            RELATIONSHIP, CONSTITUENT.KEYNAME, NF.NAME;                            
                    end
                    else
                    begin
                        select top 1
                            NF.NAME,
                            RELATIONSHIPTYPECODE.DESCRIPTION RELATIONSHIP,
                            RELATIONSHIP.STARTDATE,
                            RELATIONSHIP.ENDDATE,
                            RELATIONSHIP.ISSPOUSE
                        from dbo.RELATIONSHIP
                        inner join dbo.CONSTITUENT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
                        inner join dbo.RELATIONSHIPTYPECODE on RELATIONSHIPTYPECODE.ID = RELATIONSHIP.RECIPROCALTYPECODEID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
                        where 
                            RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and
                            CONSTITUENT.ISORGANIZATION = 0 and
              (@INCLUDEGROUPS = 1 or CONSTITUENT.ISGROUP = 0)
                        order by
                            RELATIONSHIP, CONSTITUENT.KEYNAME, CONSTITUENT.NAME;                            
                    end