Need Help with Query that is supposed to identify relationships between Account member and creates report

USE [IT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--
/*select * from NAME ------
----select * from  RSUDependNew..CP_Connections where KeyMemberNameID in  (select name_id from NAME where name_account='804961')


----select * from RSUDependNew..Cp_member

         join nameid on NAME_ID = nameid_name
where NAME_ACCOUNT = 804958 and Name_sequence != 1 and name_ID != 456146*/
/*select * from NAME 
          join nameid on NAME_ID = nameid_name
where NAME_ACCOUNT = /*150420*/ 804958*/

--select * from name where NAME_ACCOUNT  = 804958 and  name_sequence != 1 exists (select *  from nameid where name_id = nameid_name)





--select * from account where Account_ID =804961
--select replace('100-265-995','-','') as newtrn

--select * from  RSUDependnew..CP_Member where Member_ACCOUNT ='804961' 
/* select * from NAMEID
left join name on  nameid_name = name.name_id where NAMEID_VALUE = '100981496'*/


--select MEMBER_TRN from  RSUDepend..CP_Member 

--
--select * 	from [dbo].[CFUNCTION_CONNECTED_PARTY]('JME201600017','2016-07-31')  /*where account='8049587'*/order by account


--SELECT * FROM [dbo].[CFUNCTION_CONNECTED_PARTY_TRN]  ('JME201600017','2016-07-31','100981496')
ALTER function [dbo].[CFUNCTION_CONNECTED_PARTY] (
		@ISIN_CODE nvarchar(12)
		,@RECORD_DATE date
)
returns table as return
(
--select * from RSUDepend..CP_MEMBER where  = '100981496'

SELECT  DISTINCT
				KeyMember.NAME_TEXT AS Name
				,COALESCE(ConnectedMember.NAME_TEXT,KeyMember.NAME_TEXT) AS PARTY 
				,A.ACCOUNT_REFERENCE AS ACCOUNT
				,ConnectedMember.NAME_ID  AS CNI
				,KeyMember.NAME_ID AS KNI
				,case /*when c1.Relationship is null then 'Self'*/ when (c1.Relationship is null and  ConnectedMember.NAME_ID!= KeyMember.NAME_ID )then 'Unknown' when (c1.Relationship is null AND ConnectedMember.NAME_ID = KeyMember.NAME_ID) then 'Self' else c1.Relationship end as Relationship
				,ISI.ISIN_CODE				AS ISIN
				,ISI.ISIN_SHORT_NAME		AS Symbol
				,ISI.ISIN_FULL_NAME			AS Symbol_Name
				,ISIN_SHARES				AS ISSUED_SHARES
				,CASE WHEN c1.Relationship ='self' OR c1.Relationship IS NULL  THEN ISNULL(BAL.Volume,0)	 ELSE NULL END AS Volume
				,case when c1.Relationship is null THEN 1 ELSE c1.SortOrder END AS SortCol
				,CASE WHEN  c1.Relationship ='self' OR c1.Relationship IS NULL  THEN ISNULL(BAL.percentage,0) ELSE NULL END AS percentage
				,CASE WHEN KeyMember.NAME_ID_CODE IS NOT NULL THEN KeyMember.NAME_ID_CODE WHEN Keymember.NAME_NI IS NOT NULL THEN KeyMember.NAME_NI	ELSE KeyMemberNID.NAMEID_VALUE END AS TRN
				,DIRECTOR_STATUS.DIRECTOR_STATUS_DESCRIPTION AS Position
		        ,ISNULL(CASE WHEN KeyMember.NAME_ID_CODE IS NOT NULL THEN  KeyMember.NAME_ID_CODE WHEN KeyMember.NAME_NI IS NOT NULL THEN KeyMember.NAME_NI ELSE KeyMemberNID.NAMEID_VALUE END , A.ACCOUNT_ID) AS Mask_TRN
		        ,(SELECT TOP(1)NAME_TEXT FROM NAME AS JOINTMEMBERA WHERE(NAME_ACCOUNT = A.ACCOUNT_ID) AND (NAME_TYPE = 'O')AND (NAME_SEQUENCE = 2)) AS JointMember1
				,(SELECT TOP(1)NAME_TEXT FROM NAME AS JOINTMEMBERB WHERE(NAME_ACCOUNT = A.ACCOUNT_ID) AND (NAME_TYPE = 'O')AND (NAME_SEQUENCE = 3)) AS JointMember2
				,(SELECT TOP(1)NAME_TEXT FROM NAME AS JOINTMEMBERC WHERE(NAME_ACCOUNT = A.ACCOUNT_ID) AND (NAME_TYPE = 'O')AND (NAME_SEQUENCE = 4)) AS JointMember3
					
			,@RECORD_DATE as RecordDate
				 FROM RSUDependNew..CP_Member KM 
					JOIN CONFIG_DIRECTOR_STATUS DIRECTOR_STATUS ON 	DIRECTOR_STATUS.DIRECTOR_STATUS_CODE = KM.MEMBER_DIRECTOR COLLATE Latin1_General_CI_AS 
					JOIN ISIN ISI ON ISI.ISIN_ID = KM.MEMBER_ISIN AND ISI.ISIN_CODE = ISNULL(@ISIN_CODE,(SELECT MAX(ISIN_CODE) FROM ISIN WHERE ISIN_CODE LIKE 'J%'))
					LEFT JOIN NAMEID KeyMemberNID ON ((KeyMemberNID.NAMEID_VALUE COLLATE Latin1_General_CI_AS = KM.MEMBER_TRN  AND KeyMemberNID.NAMEID_TYPE = 'TRN' AND ISI.ISIN_ID = KM.MEMBER_ISIN AND ISI.ISIN_CODE = ISNULL(@ISIN_CODE,(SELECT MAX(ISIN_CODE) FROM ISIN WHERE ISIN_CODE LIKE 'J%' ))))
				    JOIN NAME KeyMember ON (KeyMember.NAME_ID = KeyMemberNID.NAMEID_NAME AND KeyMember.NAME_SEQUENCE = 1)
					left join 	(select c.*,r.Relationship,r.SortOrder from [RSUDependNew]..CP_Connections c /*c on c.keyMemberNameID = N.NAME_ID*/
					left join 	RSUDependNew..CP_Relationships r on c.RelationshipID = r.RelationshipID) c1 on c1.keyMemberNameID = KeyMember.NAME_ID
					JOIN Name ConnectedMember on ((c1.ConnectedPartyNameID = ConnectedMember.NAME_ID OR (ConnectedMember.NAME_ID = KeyMemberNID.NAMEID_NAME and ConnectedMember.NAME_ID <> c1.KeyMemberNameID)))	
													 OR(ConnectedMember.NAME_ID != KeyMember.NAME_ID  AND ConnectedMember.NAME_ACCOUNT = KeyMember.NAME_ACCOUNT AND ConnectedMember.NAME_SEQUENCE != 1)
					--LEFT JOIN  NAMEID ConnectedMemberNID ON (ConnectedMember.NAME_ID = ConnectedMemberNID.NAMEID_NAME and ConnectedMemberNID.NAMEID_VALUE COLLATE Latin1_General_CI_AS != KM.MEMBER_TRN)
					JOIN Account A ON A.ACCOUNT_ID =ISNULL(ConnectedMember.NAME_ACCOUNT,KM.MEMBER_ACCOUNT)
					LEFT JOIN	(SELECT
										A.ACCOUNT_ID
									   ,I.ISIN_SHORT_NAME	as Symbol
									   ,B.BALTIME_BALANCE	as Volume
									   ,CASE WHEN I.ISIN_SHARES <> 0 THEN  B.BALTIME_BALANCE/I.ISIN_SHARES  ELSE 0 END as percentage
															from	BALTIME B
																		JOIN ACCOUNT A		ON	A.ACCOUNT_ID = B.BALTIME_ACCOUNT
																		JOIN NAME N			ON	N.NAME_ACCOUNT = A.ACCOUNT_ID
																							AND N.NAME_TYPE = 'O'
																								--and N.NAME_SEQUENCE = 1
																		JOIN ISIN  I		ON	I.ISIN_ID = B.BALTIME_ISIN
															WHERE	I.ISIN_CODE = isnull(@ISIN_CODE,(SELECT max(ISIN_CODE) FROM ISIN WHERE ISIN_CODE like 'J%'))
																	AND B.BALTIME_START<= isnull(@RECORD_DATE,GETDATE())
																	AND isnull(@RECORD_DATE,GETDATE()) < B.BALTIME_END
													)	BAL ON 		BAL.ACCOUNT_ID = A.ACCOUNT_ID	
									
						WHERE	isnull(@RECORD_DATE,GETDATE()) between [MEMBER_JOINED] and [MEMBER_RESIGNED]) --AND   KeyMember.NAME_ACCOUNT = '804958'

The query above is supposed to identify the relationships of person's in the account based on custom tables but some records are being returned with the wrong relationship

Please post DDL for the tables and some sample data insert statements.