USP_DATALIST_RELATIONSHIPS_INDTOIND

                CREATE procedure dbo.USP_DATALIST_RELATIONSHIPS_INDTOIND 
                (
                    @CONSTITUENTID uniqueidentifier,
                    @ONLYCURRENT bit = 0,
                    @RECIPROCALTYPECODEID uniqueidentifier = null
                ) as
                    set nocount on;

                    declare @CURRENTDATE datetime
                    if @ONLYCURRENT = 1
                        set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                    declare @HOUSEHOLDID uniqueidentifier;
                    select top(1)
                        @HOUSEHOLDID = HOUSEHOLD.ID
                    from 
                        dbo.GROUPMEMBER
                    inner join 
                        dbo.CONSTITUENT as HOUSEHOLD on GROUPMEMBER.GROUPID = HOUSEHOLD.ID
                    inner join 
                        dbo.GROUPDATA on GROUPDATA.ID = HOUSEHOLD.ID
                    where 
                        GROUPMEMBER.MEMBERID = @CONSTITUENTID
                    and 
                        dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GROUPMEMBER.ID) = 1
                    and 
                        GROUPDATA.GROUPTYPECODE = 0;

                    -- Using a CTE strictly to allow ordering by KEYNAME (when 'union' is used, columns in the 'order by' clause must be in the select list)            

                    with RELATIONSHIPS_CTE as (
                        select    
                            RELATIONSHIP.ID,
                            RELATIONSHIP.RECIPROCALCONSTITUENTID,
                            (select DESCRIPTION from dbo.RELATIONSHIPTYPECODE where ID=RELATIONSHIP.RECIPROCALTYPECODEID) RELATIONSHIP,
                            (
                                rtrim(CONSTITUENT.NAME + ' ' +
                                    coalesce(
                                        replace(
                                            stuff(
                                                (select '' + STATUS from
                                                    (select ',(Deceased)' STATUS
                                                     from dbo.DECEASEDCONSTITUENT DC
                                                     where DC.ID = CONSTITUENT.ID
                                                     union all
                                                     select ',(Inactive)' STATUS
                                                     from dbo.CONSTITUENT C
                                                     where C.ID = CONSTITUENT.ID
                                                     and C.ISINACTIVE=1
                                                    ) as SUBQ for xml path(''))
                                            ,1,1,'')
                                        , '),(', ', ')
                                    ,'')
                                )
                            ) as NAME,
                            RELATIONSHIP.STARTDATE,
                            RELATIONSHIP.ENDDATE,
                            RELATIONSHIP.ISSPOUSE,
                            CONSTITUENT.KEYNAME,
                            case 
                                when exists (select 1 from dbo.GROUPMEMBER where GROUPMEMBER.GROUPID = @HOUSEHOLDID and GROUPMEMBER.MEMBERID = RELATIONSHIP.RECIPROCALCONSTITUENTID) then 1
                                else 0
                            end as INHOUSEHOLD,
                            1 as RECORDSOURCE
                        from 
                            dbo.RELATIONSHIP
                        inner join 
                            dbo.CONSTITUENT
                        on
                            RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
                        where 
                            RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID 
                        and 
                            CONSTITUENT.ISORGANIZATION = 0 
                        and
                            CONSTITUENT.ISGROUP = 0
                        and (@RECIPROCALTYPECODEID is null
                                or RELATIONSHIP.RECIPROCALTYPECODEID = @RECIPROCALTYPECODEID
                            )

                        union

                        -- include members of the constituent's household for which explicit relationships do not already exist

                        select
                            null,
                            CONSTITUENT.ID,
                            '',
                            (
                                rtrim(CONSTITUENT.NAME + ' ' +
                                    coalesce(
                                        replace(
                                            stuff(
                                                (select '' + STATUS from
                                                    (select ',(Deceased)' STATUS
                                                     from dbo.DECEASEDCONSTITUENT DC
                                                     where DC.ID = CONSTITUENT.ID
                                                     union all
                                                     select ',(Inactive)' STATUS
                                                     from dbo.CONSTITUENT C
                                                     where C.ID = CONSTITUENT.ID
                                                     and C.ISINACTIVE=1
                                                    ) as SUBQ for xml path(''))
                                            ,1,1,'')
                                        , '),(', ', ')
                                    ,'')
                                )
                            ) as NAME,
                            GROUPMEMBERDATERANGE.DATEFROM,
                            GROUPMEMBERDATERANGE.DATETO,
                            0, -- can't tell spouse if the relationship record from above doesn't exist

                            CONSTITUENT.KEYNAME,
                            1, -- INHOUSEHOLD

                            2 -- RECORDSOURCE

                        from
                            dbo.GROUPMEMBER
                        inner join
                            dbo.CONSTITUENT on GROUPMEMBER.MEMBERID = CONSTITUENT.ID
                        left outer join
                            dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
                        where
                            GROUPMEMBER.GROUPID = @HOUSEHOLDID
                        and not exists (
                            select 1
                            from
                                dbo.RELATIONSHIP
                            where
                                RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
                            and
                                RELATIONSHIP.RECIPROCALCONSTITUENTID = GROUPMEMBER.MEMBERID
                        )
                        and 
                            not GROUPMEMBER.MEMBERID = @CONSTITUENTID
                    )
                    select
                        ID,
                        RECIPROCALCONSTITUENTID,
                        RELATIONSHIP,
                        NAME,
                        STARTDATE,
                        ENDDATE,
                        cast(ISSPOUSE as bit),
                        INHOUSEHOLD,
                        RECORDSOURCE
                    from
                        RELATIONSHIPS_CTE
                    where (@CURRENTDATE is null
                                or RELATIONSHIPS_CTE.STARTDATE is null
                                or RELATIONSHIPS_CTE.STARTDATE <= @CURRENTDATE
                            )
                        and (@CURRENTDATE is null
                                or RELATIONSHIPS_CTE.ENDDATE is null
                                or RELATIONSHIPS_CTE.ENDDATE >= @CURRENTDATE
                            )
                    order by
                        RELATIONSHIP, KEYNAME, NAME