USP_DATALIST_WEALTHINFORMATIONDASHBOARDORGANIZATIONRELATIONSHIPS

This datalist returns a constituent's organization 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
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_WEALTHINFORMATIONDASHBOARDORGANIZATIONRELATIONSHIPS
                (
                    @CONSTITUENTID uniqueidentifier,
                    @ISVISIBLE bit = 1
                )
                as
                    set nocount on;

                    if @ISVISIBLE = 1
                    begin
                        select    
                            NF.NAME,
                            RELATIONSHIPTYPECODE.DESCRIPTION RELATIONSHIP,
                            RELATIONSHIPJOBINFO.JOBTITLE POSITION,
                            RELATIONSHIP.STARTDATE,
                            RELATIONSHIP.ENDDATE,
                            RELATIONSHIP.ISCONTACT,
                            RELATIONSHIP.ISPRIMARYBUSINESS,
                            RELATIONSHIP.ISMATCHINGGIFTRELATIONSHIP
                        from dbo.RELATIONSHIP
                        inner join dbo.CONSTITUENT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
                        inner join dbo.RELATIONSHIPTYPECODE on RELATIONSHIPTYPECODE.ID = RELATIONSHIP.RECIPROCALTYPECODEID
                        left join dbo.RELATIONSHIPSET on RELATIONSHIP.RELATIONSHIPSETID = RELATIONSHIPSET.ID
                        left join dbo.RELATIONSHIPJOBINFO on RELATIONSHIPJOBINFO.RELATIONSHIPSETID = RELATIONSHIPSET.ID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
                        where 
                            RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and
                            CONSTITUENT.ISORGANIZATION = 1
                        order by
                            RELATIONSHIP, CONSTITUENT.KEYNAME, NF.NAME;                            
                    end
                    else
                    begin
                        select top 1
                            NF.NAME,
                            RELATIONSHIPTYPECODE.DESCRIPTION RELATIONSHIP,
                            RELATIONSHIP.POSITION,
                            RELATIONSHIP.STARTDATE,
                            RELATIONSHIP.ENDDATE,
                            RELATIONSHIP.ISCONTACT,
                            RELATIONSHIP.ISPRIMARYBUSINESS,
                            RELATIONSHIP.ISMATCHINGGIFTRELATIONSHIP
                        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 = 1
                        order by
                            RELATIONSHIP, CONSTITUENT.KEYNAME, NF.NAME;                            
                    end