USP_DATALIST_RELATIONSHIPS_ORGTOIND

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

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

                    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.ISCONTACT,
                        RELATIONSHIP.ISPRIMARYCONTACT,
                        (select DESCRIPTION from dbo.CONTACTTYPECODE where ID=RELATIONSHIP.CONTACTTYPECODEID),
                        RELATIONSHIP.POSITION,
                        RELATIONSHIP.ISPRIMARYBUSINESS,
                        RELATIONSHIP.ISMATCHINGGIFTRELATIONSHIP
                    from 
                        dbo.RELATIONSHIP
                    inner join 
                        dbo.CONSTITUENT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
                    where 
                        RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and
                        CONSTITUENT.ISORGANIZATION = 0 and
                        CONSTITUENT.ISGROUP = 0 -- JohnLu 2007/08/20 Make sure relationships to groups are excluded

                        and (@CURRENTDATE is null
                                or RELATIONSHIP.STARTDATE is null
                                or RELATIONSHIP.STARTDATE <= @CURRENTDATE
                            )
                        and (@CURRENTDATE is null
                                or RELATIONSHIP.ENDDATE is null
                                or RELATIONSHIP.ENDDATE >= @CURRENTDATE
                            )
                        and (@RECIPROCALTYPECODEID is null
                                or RELATIONSHIP.RECIPROCALTYPECODEID = @RECIPROCALTYPECODEID
                            )
                        and (@ONLYCONTACTS = 0 
                                or RELATIONSHIP.ISCONTACT = 1
                            )
                    order by
                        RELATIONSHIP, CONSTITUENT.KEYNAME, CONSTITUENT.NAME;