USP_RELATIONSHIPTREE_GET

            CREATE procedure dbo.USP_RELATIONSHIPTREE_GET
            (
                @CONSTITUENTID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier
             ) as
                set nocount on;

                select    
                    R1.ID,
                    R1.RECIPROCALCONSTITUENTID,
                    (select DESCRIPTION from dbo.RELATIONSHIPTYPECODE where ID = R1.RECIPROCALTYPECODEID) RELATIONSHIP,
                    (
                        rtrim(NF.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
                                             union all
                                             select ',(Subsidiary organization)' STATUS
                                             from dbo.ORGANIZATIONDATA OD
                                             where OD.ID = CONSTITUENT.ID
                                                and OD.PARENTCORPID = @CONSTITUENTID
                                             union all
                                             select ',(Parent organization)' STATUS
                                             from dbo.ORGANIZATIONDATA OD
                                             where OD.ID = @CONSTITUENTID
                                                and OD.PARENTCORPID = CONSTITUENT.ID
                                            ) as SUBQ for xml path(''))
                                    ,1,1,'')
                                , '),(', ', ')
                            ,'')
                        )
                    ) as NAME,
                    CONSTITUENT.ISINACTIVE,
                    (select sum(CNT) from (
                        select count(ID) CNT from dbo.RELATIONSHIP R2 where R2.RELATIONSHIPCONSTITUENTID = R1.RECIPROCALCONSTITUENTID
                        union all
                        select count(ID) CNT from dbo.ORGANIZATIONDATA OD 
                            where OD.PARENTCORPID = R1.RECIPROCALCONSTITUENTID 
                                and not exists(select ID from dbo.RELATIONSHIP R2 where R2.RELATIONSHIPCONSTITUENTID = R1.RECIPROCALCONSTITUENTID)
                        union all
                        select count(ID) CNT from dbo.ORGANIZATIONDATA OD 
                            where OD.PARENTCORPID is not null and OD.ID = R1.RECIPROCALCONSTITUENTID
                                and not exists(select ID from dbo.RELATIONSHIP R2 where R2.RELATIONSHIPCONSTITUENTID = R1.RECIPROCALCONSTITUENTID)
                        ) a
                    ) COUNT,
                    R1.ISSPOUSE,
                    CONSTITUENT.ISORGANIZATION,
                    dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID,'9297f3a4-c0c5-4947-9cb2-f1d9eb15b155',R1.RECIPROCALCONSTITUENTID) LOADDATALIST,
                    dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID,'0C836902-A398-47a0-91EB-8B66E434148E',R1.RECIPROCALCONSTITUENTID) LOADPAGE,
                    CONSTITUENT.ISGROUP,
                    case when GD.GROUPTYPECODE = 0 then 1 else 0 end ISHOUSEHOLD
                from 
                    dbo.RELATIONSHIP R1
                inner join 
                    dbo.CONSTITUENT on R1.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
                left outer join
                    dbo.GROUPDATA GD on GD.ID = R1.RECIPROCALCONSTITUENTID
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
                where 
                    R1.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
                union all
                select 
                    NULL as ID, --RELATIONSHIPSID,

                    PARENTCORPID as RECIPROCALCONSTITUENTID,
                    '' RELATIONSHIP,
                    (
                        rtrim(NF.NAME + ' ' +
                            coalesce(
                                replace(
                                    stuff(
                                        (select '' + STATUS from
                                            (select ',(Inactive)' STATUS
                                             from dbo.CONSTITUENT C
                                             where C.ID = CONSTITUENT.ID
                                             and C.ISINACTIVE=1
                                             union all
                                             select ',(Parent organization)' STATUS
                                            ) as SUBQ for xml path(''))
                                    ,1,1,'')
                                , '),(', ', ')
                            ,'')
                        )
                    ) as NAME,
                    CONSTITUENT.ISINACTIVE,
                    (select sum(CNT) from (
                        select count(ID) CNT from dbo.ORGANIZATIONDATA OD where OD.PARENTCORPID = ORGANIZATIONDATA.PARENTCORPID
                        union all
                        select count(ID) CNT from dbo.ORGANIZATIONDATA OD where OD.PARENTCORPID is not null and OD.ID = ORGANIZATIONDATA.PARENTCORPID
                        ) a
                    ) COUNT,
                    0 as ISSPOUSE,
                    1 as ISORGANIZATION,
                    dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID,'9297f3a4-c0c5-4947-9cb2-f1d9eb15b155',PARENTCORPID) LOADDATALIST,
                    dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID,'0C836902-A398-47a0-91EB-8B66E434148E',PARENTCORPID) LOADPAGE,
                    0 as ISGROUP,
                    0 as ISHOUSEHOLD
                from ORGANIZATIONDATA
                inner join dbo.CONSTITUENT
                    on ORGANIZATIONDATA.PARENTCORPID = CONSTITUENT.ID
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
                where ORGANIZATIONDATA.ID = @CONSTITUENTID
                and not exists(select ID from dbo.RELATIONSHIP 
                    where RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
                        and RECIPROCALCONSTITUENTID = PARENTCORPID)
                union all
                select 
                    NULL as ID, --RELATIONSHIPSID,

                    ORGANIZATIONDATA.ID as RECIPROCALCONSTITUENTID,
                    '' RELATIONSHIP,
                    (
                        rtrim(NF.NAME + ' ' +
                            coalesce(
                                replace(
                                    stuff(
                                        (select '' + STATUS from
                                            (select ',(Inactive)' STATUS
                                             from dbo.CONSTITUENT C
                                             where C.ID = CONSTITUENT.ID
                                             and C.ISINACTIVE=1
                                             union all
                                             select ',(Subsidiary organization)' STATUS
                                            ) as SUBQ for xml path(''))
                                    ,1,1,'')
                                , '),(', ', ')
                            ,'')
                        )
                    ) as NAME,
                    CONSTITUENT.ISINACTIVE,
                    (select sum(CNT) from (
                        select count(ID) CNT from dbo.ORGANIZATIONDATA OD where OD.PARENTCORPID = ORGANIZATIONDATA.ID
                        union all
                        select count(ID) CNT from dbo.ORGANIZATIONDATA OD where OD.PARENTCORPID is not null and OD.ID = ORGANIZATIONDATA.ID
                        ) a
                    ) COUNT,
                    0 as ISSPOUSE,
                    1 as ISORGANIZATION,
                    dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID,'9297f3a4-c0c5-4947-9cb2-f1d9eb15b155',PARENTCORPID) LOADDATALIST,
                    dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID,'0C836902-A398-47a0-91EB-8B66E434148E',PARENTCORPID) LOADPAGE,
                    0 as ISGROUP,
                    0 as ISHOUSEHOLD
                from ORGANIZATIONDATA
                inner join dbo.CONSTITUENT
                    on ORGANIZATIONDATA.ID = CONSTITUENT.ID
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
                where ORGANIZATIONDATA.PARENTCORPID = @CONSTITUENTID
                and not exists(select ID from dbo.RELATIONSHIP 
                    where RELATIONSHIPCONSTITUENTID = ORGANIZATIONDATA.ID
                        and RECIPROCALCONSTITUENTID = @CONSTITUENTID)
                order by ISSPOUSE desc