USP_DATALIST_CONSTITUENTPROFILEDASHBOARDINDIVIDUALRELATIONSHIPS

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

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Constituent ID
@ISVISIBLE	bit	IN	Is visible
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
Definition

Copy


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

                    -- Bug 26981 - AdamBu - Security is not enforced in the relationship datalist, so don't enforce it here.

                    --    declare @ISADMIN bit;

                    --    declare @APPUSER_IN_NONRACROLE bit;

                    --    declare @APPUSER_IN_NOSECGROUPROLE bit;

                    --    declare @APPUSER_IN_NONSITEROLE bit;

                    --    declare @APPUSER_IN_NOSITEROLE bit;

                    --                

                    --    set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

                    --    set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);

                    --    set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);

                    --    set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);

                    --    set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);


                        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
                                CONSTITUENT.ISGROUP = 0
                            -- Bug 26981 - AdamBu - Security is not enforced in the relationship datalist, so don't enforce it here.

                            --    and (@ISADMIN = 1 or 

                            --            (@APPUSER_IN_NONRACROLE = 1 or

                            --            dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, RELATIONSHIP.RECIPROCALCONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)

                            --            and

                            --            (@APPUSER_IN_NONSITEROLE = 1 or

                            --            dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, RELATIONSHIP.RECIPROCALCONSTITUENTID, @APPUSER_IN_NOSITEROLE) = 1)

                            --        )

                            order by
                                RELATIONSHIP, CONSTITUENT.KEYNAME, CONSTITUENT.NAME;                            
                        end