DELIMITER $$

CREATE FUNCTION  pbi_is_not_empty
(
  string VARCHAR(10240) CHARSET UTF8
)
RETURNS BOOLEAN
DETERMINISTIC
READS SQL DATA
BEGIN
  RETURN NULLIF(string,'') IS NOT NULL;
END;$$


CREATE FUNCTION  pbi_generate_entity_key 
(
  id INT(11),
  prefix VARCHAR(10)
)
RETURNS VARCHAR(18) CHARSET UTF8  
DETERMINISTIC
READS SQL DATA
BEGIN
  RETURN CONCAT(prefix, LPAD(id, 8, '0'));
END;$$

DROP FUNCTION IF EXISTS pbi_get_employee_status;$$
CREATE FUNCTION pbi_get_employee_status(input_date DATE, emp_num INT)
    RETURNS VARCHAR(10)
    DETERMINISTIC
    READS SQL DATA
BEGIN
    DECLARE emp_status VARCHAR(10);
    DECLARE emp_terminations INT(7);
SELECT COUNT(*)
INTO emp_terminations
FROM ohrm_emp_termination
WHERE input_date >= termination_date
  AND (input_date < reactivation_date OR reactivation_date IS NULL)
  AND emp_number = emp_num;
IF emp_terminations > 0 THEN
       SET emp_status = 'Terminated';
ELSE
       SET emp_status = 'Active';
END IF;
RETURN emp_status;
END;$$

DELIMITER ;