CREATE DEFINER=`root`@`%` FUNCTION `queryChildrenSuiteIds`(suiteId varchar(20)) RETURNS varchar(4000) CHARSET utf8
BEGIN

DECLARE childSuiteIds VARCHAR(4000);

DECLARE parentSuiteIds VARCHAR(4000);

SET childSuiteIds='';

SET parentSuiteIds = CAST(suiteId AS CHAR);

WHILE parentSuiteIds IS NOT NULL DO

SET childSuiteIds= CONCAT(parentSuiteIds, ',', childSuiteIds);

SELECT GROUP_CONCAT(dept_code) INTO parentSuiteIds FROM ehr_org.org_department WHERE FIND_IN_SET(parent_dept_code, parentSuiteIds)>0;

END WHILE;

RETURN childSuiteIds;

END