SELECT * FROM (
SELECT   
  TBLCI.IDCMDBCI,    
  TBLCI.CI_GENERICNAME,  
  TBLCI.FIRSTNAME,  
  TBLCI.MIDDLENAME,  
  TBLCI.LASTNAME,  
  TBLCI.EMPLOYEENUMBER,  
  TBLUSER.ROLENAME,  
  TBLCI.USERTITLE,  
  TBLUSER.IDCMDBUSER,  
  TBLUSER.IDATROLE     
FROM   
(SELECT   
  CMDBCI.IDCMDBCI,   
  CASE WHEN CMDBCI.CI_GENERICNAME IS NOT NULL THEN CMDBCI.CI_GENERICNAME ELSE '' END CI_GENERICNAME,  
  CASE WHEN CMDB_EFPEOPLEINFORMATION.EMPLOYEENUMBER IS NOT NULL THEN  CMDB_EFPEOPLEINFORMATION.EMPLOYEENUMBER ELSE '' END EMPLOYEENUMBER,  
  CASE WHEN CMDB_EFPEOPLEINFORMATION.FIRSTNAME IS NOT NULL THEN  CMDB_EFPEOPLEINFORMATION.FIRSTNAME ELSE '' END FIRSTNAME,  
  CASE WHEN CMDB_EFPEOPLEINFORMATION.LASTNAME IS NOT NULL THEN  CMDB_EFPEOPLEINFORMATION.LASTNAME ELSE '' END  LASTNAME,  
  CASE WHEN CMDB_EFPEOPLEINFORMATION.MIDDLENAME IS NOT NULL THEN  CMDB_EFPEOPLEINFORMATION.MIDDLENAME ELSE '' END MIDDLENAME,  
  CASE WHEN CMDB_EFPEOPLEINFORMATION.USERTITLE IS NOT NULL THEN CMDB_EFPEOPLEINFORMATION.USERTITLE ELSE '' END USERTITLE,
  CMDB_EFPEOPLEINFORMATION.SEDE
 FROM  CMDBCIDEFINE,CMDBCI LEFT JOIN CMDB_EFPEOPLEINFORMATION ON CMDB_EFPEOPLEINFORMATION.IDCMDBCI=CMDBCI.IDCMDBCI   
 WHERE    
  CMDBCI.IDCMDBCIDEFINE=CMDBCIDEFINE.IDCMDBCIDEFINE and  
 CMDBCIDEFINE.IDCMDBCIDEFINETYPE = 1  and CMDBCI.IDCMDBCISTATE IN(1,2) 
 AND CMDB_EFPEOPLEINFORMATION.SEDE IN (SELECT SEDE FROM CMDB_EFPEOPLEINFORMATION WHERE IDCMDBCI =  @[IDCMDBCI_CREATOR])
)TBLCI   
LEFT  JOIN  
(SELECT   
  CASE WHEN ATROLE.ROLENAME IS NOT NULL THEN ATROLE.ROLENAME ELSE '' END ROLENAME,  
  CASE WHEN CMDBUSER.IDATROLE IS NOT NULL THEN CMDBUSER.IDATROLE ELSE 0 END IDATROLE,   
  CMDBUSER.IDCMDBCI,  
  CASE WHEN  CMDBUSER.IDCMDBUSER IS NOT NULL THEN   CMDBUSER.IDCMDBUSER ELSE 0 END IDCMDBUSER  
 FROM  CMDBUSER LEFT JOIN  ATROLE ON  
 ATROLE.IDATROLE=CMDBUSER.IDATROLE   
) TBLUSER   
ON TBLCI.IDCMDBCI = TBLUSER.IDCMDBCI  
WHERE EXISTS 
( 
 SELECT  
  MDGROUPUSER.IDCMDBCI, 
  MDGROUPUSER.IDMDGROUP 7
 FROM  MDGROUPUSER,SDGROUPSERVICEUSER 
 WHERE   
  MDGROUPUSER.IDMDGROUP=SDGROUPSERVICEUSER.IDMDGROUP AND 
  SDGROUPSERVICEUSER.IDSDTYPEUSER=4 AND 
  TBLCI.IDCMDBCI = MDGROUPUSER.IDCMDBCI
  ) 
 ) as TBLUSR