DELIMITER $$
DROP FUNCTION IF EXISTS get_requestdesk_approver_name$$

CREATE FUNCTION get_requestdesk_approver_name(requestid int,aproverlevel int) RETURNS TEXT
    DETERMINISTIC
    READS SQL DATA
BEGIN
    DECLARE resultValue TEXT;
    DECLARE entityProcessId INT(11);
    DECLARE requestProcessId INT(11);
    DECLARE actorTypeId INT(11);
    DECLARE workflowRequestId INT(11);
    DECLARE empNumber INT(11);
    DECLARE fname VARCHAR(100);
    DECLARE lname VARCHAR(100);
    DECLARE terminationId INT(4);
    SET entityProcessId = (SELECT id  FROM `ohrm_advanced_workflow_entity_process` WHERE `entity` LIKE 'Request' LIMIT 1);
    SET workflowRequestId = (SELECT workflow_request_id  FROM `ohrm_request` WHERE `id` = requestid);
    SET requestProcessId = (SELECT id FROM `ohrm_advanced_workflow_request_process` WHERE `request_id` = workflowRequestId AND `entity_process_id` = entity_process_id LIMIT 1);
    SET actorTypeId = (SELECT id  FROM `ohrm_advanced_workflow_entity_process_actor_type` WHERE `entity_process_id` = entityProcessId AND `name` LIKE 'RequestApprover');
    SET empNumber = (SELECT emp_number FROM `ohrm_advanced_workflow_request_process_actor` WHERE `request_process_id` = requestProcessId AND `level` = aproverlevel AND `actor_type_id` = actorTypeId LIMIT 1);
    SET fname = (SELECT emp_firstname FROM `hs_hr_employee` WHERE `emp_number` = empNumber LIMIT 1);
    SET lname = (SELECT emp_lastname FROM `hs_hr_employee` WHERE `emp_number` = empNumber LIMIT 1);
    SET terminationId = (SELECT termination_id FROM `hs_hr_employee` WHERE `emp_number` = empNumber LIMIT 1);
    SET resultValue = (SELECT CONCAT_WS(" ", fname, lname, IF(terminationId IS NULL, '', ' (Past Employee)')));
RETURN (resultValue);
END$$