USP_DATALIST_CONSTITUENTPROFILEDASHBOARDORGANIZATIONRELATIONSHIPS

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

                    --purposefully using DATE

                    declare @CURRENTDATE date;
                    set @CURRENTDATE = getdate();

                    -- 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,
                                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.RELATIONSHIPJOBINFO on RELATIONSHIP.RELATIONSHIPSETID = RELATIONSHIPJOBINFO.RELATIONSHIPSETID
                                    and @CURRENTDATE between coalesce(RELATIONSHIPJOBINFO.STARTDATE, @CURRENTDATE) and coalesce(RELATIONSHIPJOBINFO.ENDDATE, @CURRENTDATE)
                            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
                            where 
                                RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and
                                CONSTITUENT.ISORGANIZATION = 1
                            -- 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, NF.NAME;                            
                        end