USP_SIMPLELIST_CONSTITUENTCONTACTRELATIONSHIP

                CREATE procedure dbo.USP_SIMPLELIST_CONSTITUENTCONTACTRELATIONSHIP
                (
                    @CONSTITUENTID uniqueidentifier,
                    @INCLUDEINACTIVE bit = 0,
                    @RELATIONSHIPID uniqueidentifier = null
                )
                as
                    set nocount on;

                    declare @CURRENTDATE datetime;
                    declare @UPPERBOUND datetime;
                    declare @LOWERBOUND datetime;

                    set @CURRENTDATE = getdate();
                    set @UPPERBOUND = dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE);
                    set @LOWERBOUND = dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE);

                    select
                        RELATIONSHIP.ID [VALUE],
                        RELATIONSHIP.DESCRIPTION [LABEL]
                    from
                        dbo.RELATIONSHIP
                    inner join
                        dbo.CONSTITUENT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
                    left outer join
                        dbo.DECEASEDCONSTITUENT on DECEASEDCONSTITUENT.ID = CONSTITUENT.ID
                    where
                        RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
                        and RELATIONSHIP.ISCONTACT = 1
                        and (RELATIONSHIP.STARTDATE is null or RELATIONSHIP.STARTDATE <= @UPPERBOUND)
                        and (RELATIONSHIP.ENDDATE is null or RELATIONSHIP.ENDDATE >= @LOWERBOUND)
                        and (
                            (
                                (@INCLUDEINACTIVE = 1 or CONSTITUENT.ISINACTIVE = 0)
                                and DECEASEDCONSTITUENT.ID is null
                            )
                            or RELATIONSHIP.ID = @RELATIONSHIPID
                        )
                    order by
                        RELATIONSHIP.ISPRIMARYCONTACT,
                        LABEL;

                    return 0;