SELECT 
	chn.entity_id,	
	chn.entity_type_cd,
	eng.full_nm,
	eng.nm_search_txt,
	fr.full_nm,
	fr.nm_search_txt,
	acc.full_nm,
	acc.nm_search_txt,
	acc_alt.full_nm,
	acc_alt.nm_search_txt,
    acc.last_updt_ts
FROM 
	(SELECT DISTINCT cn.entity_id,cn.entity_type_cd FROM customer_hierarchy_node cn,name_asgnmt na
	WHERE cn.entity_id = na.entity_id
	AND   cn.eff_end_dt > sysdate
	AND   na.nm_asgnmt_subtyp_cd = 'O' 	
        AND   na.nm_asgnmt_typ_cd in ('L','I') 	) CHN 
LEFT OUTER JOIN
	 ( SELECT entity_id as entity,full_nm,nm_search_txt FROM name_asgnmt
	   WHERE nm_asgnmt_subtyp_cd = 'O' 	  
           AND 	nm_asgnmt_typ_cd = 'L' 	   
           AND 	lang_cd = 'EN' 	   
           AND 	eff_end_dt > sysdate
	   AND  entity_type_cd in ('CUSTOMER','CHN') 	  ) eng ON eng.entity = chn.entity_id  
LEFT OUTER JOIN
	   (SELECT entity_id as entity,full_nm,nm_search_txt,last_updt_ts FROM name_asgnmt   
	   WHERE nm_asgnmt_subtyp_cd = 'O' 	   
           AND nm_asgnmt_typ_cd = 'L'		   
           AND eff_end_dt > sysdate
	   AND entity_type_cd = 'ACCOUNT' 	   ) acc on acc.entity = chn.entity_id 
LEFT OUTER JOIN 	   
	   (SELECT entity_id as entity, full_nm,nm_search_txt FROM name_asgnmt    
	   WHERE nm_asgnmt_subtyp_cd = 'O'	   
           AND nm_asgnmt_typ_cd = 'I'		   
           AND eff_end_dt > sysdate
	   AND entity_type_cd = 'ACCOUNT' 	   )acc_alt on acc_alt.entity = chn.entity_id 
LEFT OUTER JOIN
	   (select entity_id as entity,full_nm,nm_search_txt FROM name_asgnmt    
	   WHERE nm_asgnmt_subtyp_cd = 'O' 	   
           AND 	 nm_asgnmt_typ_cd = 'L' 	  
           AND 	 lang_cd = 'FR' 	   
           AND 	 eff_end_dt > sysdate
	   AND 	 entity_type_cd in ('CUSTOMER','CHN') 	   )fr on fr.entity = chn.entity_id