USP_DATALIST_RELATIONSHIPS_ORGTOORG

This datalist returns a list of all organization relationships for an organization.
Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@ONLYCURRENT	bit	IN	Only display current relationships
@RECIPROCALTYPECODEID	uniqueidentifier	IN	Relationship type
Definition

Copy


            CREATE procedure dbo.USP_DATALIST_RELATIONSHIPS_ORGTOORG (
                @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());

                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
                from 
                    dbo.RELATIONSHIP
                inner join
                    dbo.CONSTITUENT
                on
                    RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
                where 
                    RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and
                    CONSTITUENT.ISORGANIZATION = 1
                    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
                        )
                order by
                    RELATIONSHIP, CONSTITUENT.KEYNAME, CONSTITUENT.NAME;