DELIMITER $$

DROP PROCEDURE IF EXISTS `audit_update_hs_hr_employee_job_title`;$$
DROP TRIGGER IF EXISTS `audit_update_hs_hr_employee_job_title`;$$
DROP TRIGGER IF EXISTS `watch_insert_hs_hr_emp_contract_extend`;$$
DROP TRIGGER IF EXISTS `watch_update_hs_hr_emp_contract_extend`;$$
DROP TRIGGER IF EXISTS `watch_delete_hs_hr_emp_contract_extend`;$$
DROP PROCEDURE IF EXISTS `audit_insert_hs_hr_emp_contract_extend`;$$
DROP PROCEDURE IF EXISTS `audit_update_hs_hr_emp_contract_extend`;$$
DROP PROCEDURE IF EXISTS `audit_PREDELETE_hs_hr_emp_directdebit`;$$
DROP PROCEDURE IF EXISTS `audit_update_hs_hr_employee_termination`;$$

CREATE PROCEDURE audit_update_hs_hr_employee_termination
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_temination_id INT,
  IN old_temination_id INT 
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;
  DECLARE action_description_termination VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  SET action_description_termination = '';
  -- Start generating the action description


 IF (IFNULL(old_temination_id, '') != IFNULL(new_temination_id, '') && IFNULL(new_temination_id, '') != 0) 
        THEN SET action_description_termination = CONCAT(action_description_termination, ' ',
        'Employee Terminated :  ', 
        IFNULL((SELECT `name` FROM `hs_hr_employee` e LEFT JOIN ohrm_emp_termination t ON e.termination_id = t.id LEFT JOIN ohrm_emp_termination_reason tr ON t.reason_id = tr.id  WHERE e.emp_number = affected_entity_id), ' '),
        '\n Date : ', 
        IFNULL((SELECT `termination_date` FROM `hs_hr_employee` e LEFT JOIN ohrm_emp_termination t ON e.termination_id = t.id  WHERE e.emp_number = affected_entity_id), ' '),
                 '\n',
        '\n Note : ', 
        IFNULL((SELECT `note` FROM `hs_hr_employee` e LEFT JOIN ohrm_emp_termination t ON e.termination_id = t.id  WHERE e.emp_number = affected_entity_id), ' '),
                 '\n');

END IF;


 IF ( (IFNULL(new_temination_id, '') = '' != IFNULL(new_temination_id, '') = '') &&  IFNULL(new_temination_id, '') = '') 
        THEN SET action_description = CONCAT(action_description, ' ',(SELECT CONCAT(`emp_firstname`, ' ',`emp_middle_name`, ' ', `emp_lastname`) FROM `hs_hr_employee` WHERE `emp_number` = affected_entity_id),' is activated ');
END IF;



  -- End generating the action description

  IF (action_description_termination != '') THEN

    
    INSERT INTO `ohrm_audittrail_pim_employee_trail` (action_time, action_owner_id, action_owner_name, version_id, action, affected_entity_id, affected_entity_name, action_description)  VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      'UPDATE TERMINATION DETAILS',
      affected_entity_id,
      affected_entity_name,
      action_description_termination
    );
 
  END IF;
 
END;$$ 

DROP TRIGGER IF EXISTS `watch_insert_hs_hr_emp_basicsalary`;$$
DROP PROCEDURE IF EXISTS `audit_insert_hs_hr_emp_basicsalary`;$$

DROP TRIGGER IF EXISTS `watch_update_hs_hr_emp_basicsalary`;$$
DROP PROCEDURE IF EXISTS `audit_update_hs_hr_emp_basicsalary`;$$

DROP TRIGGER IF EXISTS `watch_delete_hs_hr_emp_basicsalary`;$$
DROP PROCEDURE IF EXISTS `audit_delete_hs_hr_emp_basicsalary`;$$

DROP TRIGGER IF EXISTS `watch_insert_hs_hr_emp_reportto`;$$
DROP PROCEDURE IF EXISTS `audit_insert_hs_hr_emp_reportto`;$$

DROP TRIGGER IF EXISTS `watch_update_hs_hr_emp_reportto`;$$
DROP PROCEDURE IF EXISTS `audit_update_hs_hr_emp_reportto`;$$

DROP TRIGGER IF EXISTS `watch_delete_hs_hr_emp_reportto`;$$
DROP PROCEDURE IF EXISTS `audit_delete_hs_hr_emp_reportto`;$$


CREATE TRIGGER watch_insert_hs_hr_emp_reportto
AFTER INSERT ON hs_hr_emp_reportto
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = 1;
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_reportto LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_reportto.erep_sub_emp_number WHERE hs_hr_employee.emp_number = NEW.erep_sub_emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_insert_hs_hr_emp_reportto(@orangehrm_user, orangehrm_user_name, 'ADD SUPERVISOR', new_version, NEW.erep_sub_emp_number, affected_entity_name, NEW.erep_sup_emp_number, NEW.erep_reporting_mode);
END;$$

CREATE PROCEDURE audit_insert_hs_hr_emp_reportto
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(100) CHARSET UTF8,
  IN action_name VARCHAR(40) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(100) CHARSET UTF8,
  IN new_erep_sup_emp_number INT(7),
  IN new_erep_reporting_mode SMALLINT(6)
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

   SET action_description = CONCAT(action_description, ' ', 'New Supervisor was added \n Supervisor Name :', (SELECT CONCAT(`emp_firstname`, ' ',`emp_middle_name`, ' ', `emp_lastname`) FROM `hs_hr_employee` WHERE `emp_number` = new_erep_sup_emp_number) , '\n Reporting Method: ', IFNULL((SELECT `reporting_method_name` FROM ohrm_emp_reporting_method WHERE `reporting_method_id` = new_erep_reporting_mode), new_erep_reporting_mode) , '');

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_reportto_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 


CREATE TRIGGER watch_update_hs_hr_emp_reportto
AFTER UPDATE ON hs_hr_emp_reportto
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_reportto_trail` WHERE `affected_entity_id` = OLD.erep_sub_emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_reportto LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_reportto.erep_sup_emp_number WHERE hs_hr_employee.emp_number = NEW.erep_sup_emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_update_hs_hr_emp_reportto(@orangehrm_user, orangehrm_user_name, @orangehrm_action_name, new_version, OLD.erep_sub_emp_number, affected_entity_name, NEW.erep_sup_emp_number, OLD.erep_sup_emp_number, NEW.erep_reporting_mode, OLD.erep_reporting_mode);

END;$$

CREATE PROCEDURE audit_update_hs_hr_emp_reportto
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(40) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_erep_sup_emp_number INT(7),
  IN old_erep_sup_emp_number INT(7),
  IN new_erep_reporting_mode SMALLINT(6),
  IN old_erep_reporting_mode SMALLINT(6)
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

  SET action_description = CONCAT(action_description, ' ', 'Supervisor/Subordinate was changed from ', IFNULL((SELECT CONCAT(`emp_firstname`, ' ',`emp_middle_name`, ' ', `emp_lastname`) FROM `hs_hr_employee` WHERE `emp_number` = old_erep_sup_emp_number), 'NULL'), ' to ', IFNULL((SELECT CONCAT(`emp_firstname`, ' ',`emp_middle_name`, ' ', `emp_lastname`) FROM `hs_hr_employee` WHERE `emp_number` = new_erep_sup_emp_number), 'NULL'), '\n');
  IF (old_erep_reporting_mode != new_erep_reporting_mode) THEN SET action_description = CONCAT(action_description, ' ', 'Reporting Mode was changed from ', IFNULL((SELECT `reporting_method_name` FROM ohrm_emp_reporting_method WHERE `reporting_method_id` = old_erep_reporting_mode), old_erep_reporting_mode), ' to ', IFNULL((SELECT `reporting_method_name` FROM ohrm_emp_reporting_method WHERE `reporting_method_id` = new_erep_reporting_mode), new_erep_reporting_mode), '');END IF;

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_reportto_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 


CREATE TRIGGER watch_delete_hs_hr_emp_reportto
AFTER DELETE ON hs_hr_emp_reportto
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_reportto_trail` WHERE `affected_entity_id` = OLD.erep_sub_emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name = (SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name = (SELECT CONCAT(`emp_firstname`, ' ',`emp_middle_name`, ' ', `emp_lastname`) FROM `hs_hr_employee` WHERE `emp_number` = OLD.erep_sub_emp_number);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_delete_hs_hr_emp_reportto(@orangehrm_user, orangehrm_user_name, @orangehrm_action_name, new_version, OLD.erep_sub_emp_number, affected_entity_name, OLD.erep_sup_emp_number, OLD.erep_reporting_mode);
END;$$

CREATE PROCEDURE audit_delete_hs_hr_emp_reportto
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(40) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN old_erep_sup_emp_number INT(7),
  IN old_erep_reporting_mode SMALLINT(6)
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

   SET action_description = CONCAT(action_description, ' ', 'Supervisor record was deleted \n Employee Name :', (SELECT CONCAT(`emp_firstname`, ' ',`emp_middle_name`, ' ', `emp_lastname`) FROM `hs_hr_employee` WHERE `emp_number` = old_erep_sup_emp_number) , ' \n Reporting Method: ', IFNULL((SELECT `reporting_method_name` FROM ohrm_emp_reporting_method WHERE `reporting_method_id` = old_erep_reporting_mode), old_erep_reporting_mode) ,'');

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_reportto_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$



DROP TRIGGER IF EXISTS `watch_insert_hs_hr_emp_work_experience`;$$
DROP PROCEDURE IF EXISTS `audit_insert_hs_hr_emp_work_experience`;$$

DROP TRIGGER IF EXISTS `watch_update_hs_hr_emp_work_experience`;$$
DROP PROCEDURE IF EXISTS `audit_update_hs_hr_emp_work_experience`;$$

DROP TRIGGER IF EXISTS `watch_delete_hs_hr_emp_work_experience`;$$
DROP PROCEDURE IF EXISTS `audit_delete_hs_hr_emp_work_experience`;$$






CREATE TRIGGER watch_insert_hs_hr_emp_work_experience
AFTER INSERT ON hs_hr_emp_work_experience
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = 1;
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_work_experience LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_work_experience.emp_number WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_insert_hs_hr_emp_work_experience(@orangehrm_user, orangehrm_user_name, 'ADD WORK EXPERIENCE DETAILS', new_version, NEW.emp_number, affected_entity_name,  NEW.eexp_employer, NEW.eexp_jobtit, NEW.eexp_from_date, NEW.eexp_to_date, NEW.eexp_comments,NEW.eexp_creditable);
END;$$

CREATE PROCEDURE audit_insert_hs_hr_emp_work_experience
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(40) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_eexp_employer VARCHAR(100) CHARSET UTF8,
  IN new_eexp_jobtit VARCHAR(120) CHARSET UTF8,
  IN new_eexp_from_date DATE,
  IN new_eexp_to_date DATE,
  IN new_eexp_comments VARCHAR(255) CHARSET UTF8,
  IN new_eexp_creditable INT(7)
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

  SET action_description = CONCAT(action_description, ' ', 'New work experience was added (', new_eexp_jobtit, ' at ', new_eexp_employer,')\n');

  IF (new_eexp_from_date IS NOT NULL) THEN
    SET action_description = CONCAT(action_description, ' From : ', new_eexp_from_date,'\n');
  END IF;

  IF (new_eexp_to_date IS NOT NULL) THEN
     SET action_description = CONCAT(action_description, ' To : ', new_eexp_to_date,'\n');
  END IF;

  IF (new_eexp_creditable = 1) THEN
      SET action_description = CONCAT(action_description, ' Creditable : ', 'Yes','\n');
  ELSE
      SET action_description = CONCAT(action_description, ' Creditable : ', 'No','\n');
  END IF;

  IF (new_eexp_comments IS NOT NULL) THEN
     SET action_description = CONCAT(action_description, ' Comment : ', new_eexp_comments,'\n');
  END IF;


  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_qualification_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$


CREATE TRIGGER watch_update_hs_hr_emp_work_experience
AFTER UPDATE ON hs_hr_emp_work_experience
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_qualification_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_work_experience LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_work_experience.emp_number WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_update_hs_hr_emp_work_experience(@orangehrm_user, orangehrm_user_name, 'UPDATE WORK EXPERIENCE DETAILS', new_version, OLD.emp_number, affected_entity_name, NEW.eexp_employer, OLD.eexp_employer, NEW.eexp_jobtit, OLD.eexp_jobtit, CONVERT(NEW.eexp_from_date, DATE), CONVERT(OLD.eexp_from_date, DATE), CONVERT(NEW.eexp_to_date, DATE), CONVERT(OLD.eexp_to_date, DATE), NEW.eexp_comments, OLD.eexp_comments,NEW.eexp_creditable,OLD.eexp_creditable);
END;$$

CREATE PROCEDURE audit_update_hs_hr_emp_work_experience
(
  IN action_owner_id VARCHAR(6),
  IN action_owner_name VARCHAR(300),
  IN action_name VARCHAR(40),
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(300),
  IN new_eexp_employer VARCHAR(100) CHARSET UTF8,
  IN old_eexp_employer VARCHAR(100) CHARSET UTF8,
  IN new_eexp_jobtit VARCHAR(120) CHARSET UTF8,
  IN old_eexp_jobtit VARCHAR(120) CHARSET UTF8,
  IN new_eexp_from_date DATE,
  IN old_eexp_from_date DATE,
  IN new_eexp_to_date DATE,
  IN old_eexp_to_date DATE,
  IN new_eexp_comments VARCHAR(200) CHARSET UTF8,
  IN old_eexp_comments VARCHAR(200) CHARSET UTF8,
  IN new_eexp_creditable INT(7),
  IN old_eexp_creditable INT(7)
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description
  SET action_description =  CONCAT('(Employer :',old_eexp_employer , ' Job Title :',old_eexp_jobtit,')\n');
  IF (old_eexp_employer != new_eexp_employer) THEN SET action_description = CONCAT(action_description, ' ', 'Employer was changed from ', IFNULL(old_eexp_employer, 'NULL'), ' to ', IFNULL(new_eexp_employer, 'NULL'),'\n' );END IF;
  IF (old_eexp_jobtit != new_eexp_jobtit) THEN SET action_description = CONCAT(action_description, ' ', 'Job Title was changed from ', IFNULL(old_eexp_jobtit, 'NULL'), ' to ', IFNULL(new_eexp_jobtit, 'NULL'),'\n' );END IF;
  IF (old_eexp_from_date != new_eexp_from_date) THEN SET action_description = CONCAT(action_description, ' ', 'From Date was changed from ', IFNULL(old_eexp_from_date, 'NULL'), ' to ', IFNULL(new_eexp_from_date, 'NULL'),'\n' );END IF;
  IF (old_eexp_to_date != new_eexp_to_date) THEN SET action_description = CONCAT(action_description, ' ', 'To Date was changed from ', IFNULL(old_eexp_to_date, 'NULL'), ' to ', IFNULL(new_eexp_to_date, 'NULL'),'\n' );END IF;
  IF (new_eexp_creditable = 0 ) THEN SET action_description = CONCAT(action_description, ' Creditable was changed from Yes to No','\n');END IF;
  IF (new_eexp_creditable = 1 ) THEN SET action_description = CONCAT(action_description, ' Creditable was changed from No to Yes','\n');END IF;
  IF (old_eexp_comments != new_eexp_comments) THEN SET action_description = CONCAT(action_description, ' ', 'Comments were changed from ', resolve_empty(old_eexp_comments), ' to ', resolve_empty(new_eexp_comments) );END IF;

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_qualification_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$


CREATE TRIGGER watch_delete_hs_hr_emp_work_experience
BEFORE DELETE ON hs_hr_emp_work_experience
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_qualification_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_work_experience LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_work_experience.emp_number WHERE hs_hr_employee.emp_number = OLD.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_delete_hs_hr_emp_work_experience(@orangehrm_user, orangehrm_user_name, 'DELETE WORK EXPERIENCE DETAILS', new_version, OLD.emp_number, affected_entity_name, OLD.eexp_employer, OLD.eexp_jobtit);
END;$$

CREATE PROCEDURE audit_delete_hs_hr_emp_work_experience
(
  IN action_owner_id VARCHAR(6),
  IN action_owner_name VARCHAR(300),
  IN action_name VARCHAR(40),
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(300),
  IN old_eexp_employer VARCHAR(100) CHARSET UTF8,
  IN old_eexp_jobtit VARCHAR(120) CHARSET UTF8
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;

  SET action_description = '';
  -- Start generating the action description

   SET action_description = CONCAT(action_description, ' ', 'Work experience was deleted (', old_eexp_jobtit, ' at ', old_eexp_employer, ')');

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_qualification_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$

DROP PROCEDURE IF EXISTS `archive_reference`;$$

CREATE PROCEDURE archive_reference
(
  IN subject_table VARCHAR(50) CHARSET UTF8,
  IN key_value VARCHAR(10) CHARSET UTF8,
  IN record_descriptor VARCHAR(200) CHARSET UTF8
)
BEGIN
  INSERT INTO `ohrm_audittrail_reference_archive`(table_name,reference_key,record_descriptor) VALUES (subject_table, key_value, record_descriptor);
END;$$


DROP FUNCTION IF EXISTS resolve_empty;$$

CREATE FUNCTION  resolve_empty
(
  string VARCHAR(10240) CHARSET UTF8
)
RETURNS VARCHAR(10240) CHARSET UTF8
DETERMINISTIC
READS SQL DATA
BEGIN
  RETURN IF(IFNULL(string, '') = '', '"  "', string);
END;$$



DROP FUNCTION IF EXISTS get_static_reference_value;$$

CREATE FUNCTION get_static_reference_value
(
  value_table VARCHAR(40) CHARSET UTF8,
  value_field VARCHAR(40) CHARSET UTF8,
  reference_key_value VARCHAR(20) CHARSET UTF8
)
RETURNS VARCHAR(100)
DETERMINISTIC
READS SQL DATA
BEGIN
  RETURN IFNULL((SELECT `reference_value` FROM `ohrm_audittrail_static_reference` WHERE `table` = value_table AND `field` = value_field AND `key_value` = reference_key_value), reference_key_value);
END;$$

-- -------------------------------------------------------------------------------




DROP TRIGGER IF EXISTS `watch_insert_hs_hr_emp_emergency_contacts`;$$
DROP PROCEDURE IF EXISTS `audit_insert_hs_hr_emp_emergency_contacts`;$$

DROP TRIGGER IF EXISTS `watch_update_hs_hr_emp_emergency_contacts`;$$
DROP PROCEDURE IF EXISTS `audit_update_hs_hr_emp_emergency_contacts`;$$

DROP TRIGGER IF EXISTS `watch_delete_hs_hr_emp_emergency_contacts`;$$
DROP PROCEDURE IF EXISTS `audit_delete_hs_hr_emp_emergency_contacts`;$$


CREATE TRIGGER watch_insert_hs_hr_emp_emergency_contacts
AFTER INSERT ON hs_hr_emp_emergency_contacts
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = 1;
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_emergency_contacts LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_emergency_contacts.emp_number WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_insert_hs_hr_emp_emergency_contacts(@orangehrm_user, orangehrm_user_name, 'ADD EMERGENCY CONTACT DETAILS', new_version, NEW.emp_number, affected_entity_name, NEW.eec_name, NEW.eec_relationship, NEW.eec_home_no, NEW.eec_mobile_no, NEW.eec_office_no);
END;$$

CREATE PROCEDURE audit_INSERT_hs_hr_emp_emergency_contacts
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id VARCHAR(100) CHARSET UTF8,
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_eec_name VARCHAR(100) CHARSET UTF8, 
  IN new_eec_relationship VARCHAR(100) CHARSET UTF8,  
  IN new_eec_home_no VARCHAR(100) CHARSET UTF8,  
  IN new_eec_mobile_no VARCHAR(100) CHARSET UTF8,  
  IN new_eec_office_no VARCHAR(100)  CHARSET UTF8 
)
BEGIN
  DECLARE action_description VARCHAR(626) CHARSET UTF8;

  SET action_description = '';
  -- Start generating the action description

  SET action_description = '';
  -- Start generating the action description

   SET action_description = IFNULL( CONCAT(action_description, ' ', 'New Emergency Contact was added (Name: ',new_eec_name,')\n'),'');
   IF (new_eec_relationship != '') THEN SET action_description = IFNULL( CONCAT(action_description, ' ', 'Relationship: ',new_eec_relationship,'\n'),'');END IF;
   IF (new_eec_home_no != '') THEN SET action_description = IFNULL( CONCAT(action_description, ' ', 'Home Telephone:',new_eec_home_no,'\n'),'');END IF;
   IF (new_eec_mobile_no != '') THEN SET action_description = IFNULL( CONCAT(action_description, ' ', 'Mobile:',new_eec_mobile_no,'\n'),'');END IF;
   IF (new_eec_office_no != '') THEN SET action_description = IFNULL( CONCAT(action_description, ' ', 'Work Telephone:',new_eec_office_no,'\n'),'');END IF;


   

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_emergency_contacts_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 


CREATE TRIGGER watch_update_hs_hr_emp_emergency_contacts
AFTER UPDATE ON hs_hr_emp_emergency_contacts
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_emergency_contacts_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_emergency_contacts LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_emergency_contacts.emp_number WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_update_hs_hr_emp_emergency_contacts(@orangehrm_user, orangehrm_user_name, 'UPDATE EMERGENCY CONTACT DETAILS', new_version, NEW.emp_number, affected_entity_name, NEW.eec_name, OLD.eec_name, NEW.eec_relationship, OLD.eec_relationship, NEW.eec_home_no, OLD.eec_home_no, NEW.eec_mobile_no, OLD.eec_mobile_no, NEW.eec_office_no, OLD.eec_office_no);
END;$$

CREATE PROCEDURE audit_UPDATE_hs_hr_emp_emergency_contacts
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id VARCHAR(100) CHARSET UTF8,
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_eec_name VARCHAR(100) CHARSET UTF8,
  IN old_eec_name VARCHAR(100) CHARSET UTF8,
  IN new_eec_relationship VARCHAR(100) CHARSET UTF8,
  IN old_eec_relationship VARCHAR(100) CHARSET UTF8,
  IN new_eec_home_no VARCHAR(100) CHARSET UTF8,
  IN old_eec_home_no VARCHAR(100) CHARSET UTF8,
  IN new_eec_mobile_no VARCHAR(100) CHARSET UTF8,
  IN old_eec_mobile_no VARCHAR(100) CHARSET UTF8,
  IN new_eec_office_no VARCHAR(100) CHARSET UTF8,
  IN old_eec_office_no VARCHAR(100) CHARSET UTF8
)
BEGIN
  DECLARE action_description  VARCHAR(626) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

  SET action_description = IFNULL( CONCAT(action_description, ' ', '(Name: ',old_eec_name,')\n'),'');
  IF (old_eec_name != new_eec_name) THEN SET action_description = CONCAT(action_description, ' ', 'Name was changed from ', IFNULL(old_eec_name, 'NULL'), ' to ', IFNULL(new_eec_name, 'NULL'),'\n');END IF;
  IF (old_eec_relationship != new_eec_relationship) THEN SET action_description = CONCAT(action_description, ' ', 'Relationship was changed from ', IFNULL(old_eec_relationship, 'NULL'), ' to ', IFNULL(new_eec_relationship, 'NULL'),'\n');END IF;
  IF (old_eec_home_no != new_eec_home_no) THEN SET action_description = CONCAT(action_description, ' ', 'Home Telephone was changed from ', IFNULL(old_eec_home_no, 'NULL'), ' to ', IFNULL(new_eec_home_no, 'NULL'),'\n');END IF;
  IF (old_eec_mobile_no != new_eec_mobile_no) THEN SET action_description = CONCAT(action_description, ' ', 'Mobile was changed from ', IFNULL(old_eec_mobile_no, 'NULL'), ' to ', IFNULL(new_eec_mobile_no, 'NULL'),'\n');END IF;
  IF (old_eec_office_no != new_eec_office_no) THEN SET action_description = CONCAT(action_description, ' ', 'Work Telephone was changed from ', IFNULL(old_eec_office_no, 'NULL'), ' to ', IFNULL(new_eec_office_no, 'NULL'),'\n');END IF;

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_emergency_contacts_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 


CREATE TRIGGER watch_delete_hs_hr_emp_emergency_contacts
BEFORE DELETE ON hs_hr_emp_emergency_contacts
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_emergency_contacts_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_emergency_contacts LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_emergency_contacts.emp_number WHERE hs_hr_employee.emp_number = OLD.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_delete_hs_hr_emp_emergency_contacts(@orangehrm_user, orangehrm_user_name, 'DELETE EMERGENCY CONTACT DETAILS', new_version, OLD.emp_number, affected_entity_name, OLD.eec_name, OLD.eec_relationship, OLD.eec_home_no, OLD.eec_mobile_no, OLD.eec_office_no);
END;$$

CREATE PROCEDURE audit_DELETE_hs_hr_emp_emergency_contacts
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id VARCHAR(100) CHARSET UTF8,
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN old_eec_name VARCHAR(100) CHARSET UTF8,  
  IN old_eec_relationship VARCHAR(100) CHARSET UTF8,  
  IN old_eec_home_no VARCHAR(100) CHARSET UTF8,  
  IN old_eec_mobile_no VARCHAR(100) CHARSET UTF8,  
  IN old_eec_office_no VARCHAR(100)  CHARSET UTF8
)
BEGIN
  DECLARE action_description VARCHAR(626) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

   SET action_description = CONCAT(action_description, ' ', 'Emergency Contact was deleted (Name: ',old_eec_name,')\n');
   SET action_description = CONCAT(action_description, ' ', 'Relationship: ',old_eec_relationship,'\n');
   SET action_description = CONCAT(action_description, ' ', 'Home Telephone:',old_eec_home_no,'\n');
   SET action_description = CONCAT(action_description, ' ', 'Mobile:',old_eec_mobile_no,'\n');
   SET action_description = CONCAT(action_description, ' ', 'Work Telephone:',old_eec_office_no,'\n');

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_emergency_contacts_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 


DELIMITER ;


 -- ------------------ ------------------ ------------------ ------------------ --------------------------------- ------------------ 


DELIMITER $$



DROP TRIGGER IF EXISTS `watch_insert_hs_hr_emp_dependents`;$$
DROP PROCEDURE IF EXISTS `audit_insert_hs_hr_emp_dependents`;$$

DROP TRIGGER IF EXISTS `watch_update_hs_hr_emp_dependents`;$$
DROP PROCEDURE IF EXISTS `audit_update_hs_hr_emp_dependents`;$$

DROP TRIGGER IF EXISTS `watch_delete_hs_hr_emp_dependents`;$$
DROP PROCEDURE IF EXISTS `audit_delete_hs_hr_emp_dependents`;$$



CREATE TRIGGER watch_insert_hs_hr_emp_dependents
AFTER INSERT ON hs_hr_emp_dependents
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = 1;
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_dependents LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_dependents.emp_number WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_insert_hs_hr_emp_dependents(@orangehrm_user, orangehrm_user_name, 'ADD DEPENDENT DETAILS', new_version, NEW.emp_number, affected_entity_name, NEW.ed_name, NEW.ed_relationship_type, NEW.ed_relationship, NEW.ed_date_of_birth, NEW.ed_nation_code );
END;$$

CREATE PROCEDURE audit_INSERT_hs_hr_emp_dependents
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id VARCHAR(100) CHARSET UTF8,
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_ed_name VARCHAR(100) CHARSET UTF8,  
  IN new_ed_relationship_type ENUM('CHILD','OTHER'),  
  IN new_ed_relationship VARCHAR(100) CHARSET UTF8,  
  IN new_ed_date_of_birth DATE,
  IN new_ed_nationality INT
  
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

   SET action_description = IFNULL( CONCAT(action_description, ' ', 'New Dependent was added (Name: ',new_ed_name,')\n'),'');
   SET action_description = IFNULL( CONCAT(action_description, ' ', 'Relationship: ',new_ed_relationship_type,'\n'),'');
   IF (new_ed_relationship != '') THEN SET action_description = IFNULL( CONCAT(action_description, ' ', 'Relationship (Other): ',new_ed_relationship,'\n'),''); END IF;
   IF (new_ed_date_of_birth != '') THEN SET action_description = CONCAT(action_description, ' ', 'Date of Birth: ',new_ed_date_of_birth,'\n');END IF;
   IF (IFNULL(new_ed_nationality,'') != '') THEN SET action_description = IFNULL( CONCAT(action_description, ' ', 'Nationality: ',(SELECT name from `ohrm_nationality` WHERE id=new_ed_nationality),'\n'),''); END IF;


  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_dependents_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 

CREATE TRIGGER watch_update_hs_hr_emp_dependents
AFTER UPDATE ON hs_hr_emp_dependents
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_dependents_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_dependents LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_dependents.emp_number WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_update_hs_hr_emp_dependents(@orangehrm_user,orangehrm_user_name, 'UPDATE DEPENDENT DETAILS', new_version, NEW.emp_number,affected_entity_name, NEW.ed_name, OLD.ed_name, NEW.ed_relationship_type, OLD.ed_relationship_type, NEW.ed_relationship, OLD.ed_relationship, NEW.ed_date_of_birth, OLD.ed_date_of_birth, NEW.ed_nation_code, OLD.ed_nation_code);
END;$$

CREATE PROCEDURE audit_UPDATE_hs_hr_emp_dependents
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id VARCHAR(100) CHARSET UTF8,
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_ed_name VARCHAR(100) CHARSET UTF8,  
  IN old_ed_name VARCHAR(100) CHARSET UTF8,  
  IN new_ed_relationship_type ENUM('CHILD','OTHER'), 
  IN old_ed_relationship_type ENUM('CHILD','OTHER'),   
  IN new_ed_relationship VARCHAR(100) CHARSET UTF8,  
  IN old_ed_relationship VARCHAR(100) CHARSET UTF8,
  IN new_ed_date_of_birth DATE,
  IN old_ed_date_of_birth DATE,
  IN new_ed_nationality INT,
  IN old_ed_nationality INT

)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

  SET action_description = IFNULL( CONCAT(action_description, ' ', '(Name: ',old_ed_name,')\n'),'');
  IF (old_ed_name != new_ed_name) THEN SET action_description = CONCAT(action_description, ' ', 'Name has changed from ', IFNULL(old_ed_name, ' '), ' to ', IFNULL(new_ed_name, ' '), '\n');END IF;
  IF (old_ed_relationship_type != new_ed_relationship_type) THEN SET action_description = CONCAT(action_description, ' ', 'Relationship was changed from ', IFNULL(old_ed_relationship_type,' ') , ' to ', IFNULL(new_ed_relationship_type,' ') , '\n');END IF;
  IF (old_ed_relationship != new_ed_relationship) THEN SET action_description = CONCAT(action_description, ' ', 'Other Relationship was changed from ', IFNULL(old_ed_relationship, ' '), ' to ', IFNULL(new_ed_relationship, ' '), '\n');END IF;
  IF (IFNULL(old_ed_date_of_birth,'') != IFNULL(new_ed_date_of_birth,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Date of Birth was changed from ', IFNULL(old_ed_date_of_birth, ' '), ' to ', IFNULL(new_ed_date_of_birth, ' '), '\n');END IF;
  IF (IFNULL(old_ed_nationality,'') != IFNULL(new_ed_nationality,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Nationality was changed from ', IFNULL((SELECT name from `ohrm_nationality` WHERE id=old_ed_nationality), '-'), ' to ', IFNULL((SELECT name from `ohrm_nationality` WHERE id=new_ed_nationality), '-'), '\n');END IF;

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_dependents_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 


CREATE TRIGGER watch_delete_hs_hr_emp_dependents
BEFORE DELETE ON hs_hr_emp_dependents
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_dependents_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_dependents LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_dependents.emp_number WHERE hs_hr_employee.emp_number = OLD.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_delete_hs_hr_emp_dependents(@orangehrm_user, orangehrm_user_name, 'DELETE DEPENDENT DETAILS', new_version, OLD.emp_number, affected_entity_name, OLD.ed_name, OLD.ed_relationship_type, OLD.ed_relationship, OLD.ed_date_of_birth, OLD.ed_nation_code);
END;$$

CREATE PROCEDURE audit_DELETE_hs_hr_emp_dependents
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id VARCHAR(100) CHARSET UTF8,
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN old_ed_name VARCHAR(100) CHARSET UTF8,   
  IN old_ed_relationship_type ENUM('CHILD','OTHER'),  
  IN old_ed_relationship VARCHAR(100) CHARSET UTF8,  
  IN old_ed_date_of_birth DATE,
  IN old_ed_nationality INT
)
BEGIN
  DECLARE action_description VARCHAR(600);


  SET action_description = '';
  -- Start generating the action description

   SET action_description = CONCAT(action_description, ' ', 'Dependent was deleted (Name: ',old_ed_name,')\n');
   SET action_description = CONCAT(action_description, ' ', 'Relationship: ',old_ed_relationship_type,'\n');
   IF (old_ed_relationship != '') THEN SET action_description = CONCAT(action_description, ' ', 'Relationship (Other): ',old_ed_relationship,'\n');END IF;
   IF (old_ed_date_of_birth != '') THEN SET action_description = CONCAT(action_description, ' ', 'Date of Birth: ',old_ed_date_of_birth,'\n');END IF;
   IF (IFNULL(old_ed_nationality,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Nationality: ',(SELECT name from `ohrm_nationality` WHERE id=old_ed_nationality),'\n'); END IF;


  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_dependents_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 


DELIMITER ;

-- ------------------ ------------------ ------------------ ------------------ --------------------------------- ------------------ 

DELIMITER $$



DROP TRIGGER IF EXISTS `watch_insert_hs_hr_employee`;$$
DROP PROCEDURE IF EXISTS `audit_insert_hs_hr_employee`;$$

DROP TRIGGER IF EXISTS `watch_update_hs_hr_employee`;$$
DROP PROCEDURE IF EXISTS `audit_update_hs_hr_employee`;$$

DROP TRIGGER IF EXISTS `watch_delete_hs_hr_employee`;$$
DROP PROCEDURE IF EXISTS `audit_DELETE_hs_hr_employee`;$$
DROP PROCEDURE IF EXISTS `audit_update_hs_hr_employee_custom_fields`;$$



CREATE TRIGGER watch_insert_hs_hr_employee
AFTER INSERT ON hs_hr_employee
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = 1;
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(emp_firstname,' ' , hs_hr_employee.emp_middle_name , ' ' , emp_lastname) FROM hs_hr_employee WHERE emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
CALL audit_update_hs_hr_employee(
@orangehrm_user,
orangehrm_user_name,
'UPDATE PERSONAL DETAILS',
new_version, 
NEW.emp_number, 
affected_entity_name,
NULL, NULL, 
NULL, NULL, 
NULL, NULL, 
NEW.employee_id , NULL, 
NEW.emp_other_id,NULL, 

NEW.emp_dri_lice_num, NULL, 
NEW.emp_dri_lice_exp_date, NULL, 
NEW.emp_gender, NULL, 
NEW.emp_marital_status, NULL, 

NEW.nation_code,NULL, 
NEW.emp_birthday, NULL,

NEW.emp_street1 ,NULL ,
NEW.emp_street2 ,NULL,
NEW.city_code, NULL,

NEW.provin_code,NULL, 
NEW.emp_zipcode, NULL, 
NEW.coun_code,NULL, 

NEW.emp_hm_telephone, NULL, 
NEW.emp_mobile, NULL, 
NEW.emp_work_telephone, NULL, 
NEW.emp_work_email, NULL, 

NEW.emp_oth_email, NULL, 
NEW.emp_ssn_num,NULL, 
NEW.emp_sin_num,NULL, 

NEW.emp_nick_name, NULL, 
NEW.emp_military_service, NULL, 
NULL, NULL,
NEW.termination_id, NEW.termination_id,
NEW.eeo_race_ent, NULL,
NEW.has_work_week, NULL);


 CALL audit_insert_hs_hr_employee(@orangehrm_user, orangehrm_user_name, 'ADD EMPLOYEE DETAILS', new_version, NEW.emp_number, affected_entity_name, NEW.emp_number, NEW.emp_firstname, NEW.emp_middle_name, NEW.emp_lastname); 
 CALL audit_update_hs_hr_employee_termination(@orangehrm_user, orangehrm_user_name, new_version, NEW.emp_number, affected_entity_name, NEW.termination_id, NULL);






END;$$

CREATE PROCEDURE audit_INSERT_hs_hr_employee
(
    IN action_owner_id VARCHAR(6) CHARSET UTF8,
    IN action_owner_name VARCHAR(300) CHARSET UTF8,
    IN action_name VARCHAR(255) CHARSET UTF8,
    IN new_version INT,
    IN affected_entity_id VARCHAR(50) CHARSET UTF8,
    IN affected_entity_name VARCHAR(300) CHARSET UTF8,
    IN new_employee_id VARCHAR(50) CHARSET UTF8,
    IN new_emp_firstname VARCHAR(255) CHARSET UTF8,
    IN emp_middle_name VARCHAR(255) CHARSET UTF8,
    IN new_emp_lastname VARCHAR(255) CHARSET UTF8

)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

   SET action_description = CONCAT(action_description, ' ', 'New Employee was added (Name: ', new_emp_firstname,' ',emp_middle_name,' ', new_emp_lastname,')');

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_employee_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 


CREATE TRIGGER watch_update_hs_hr_employee
AFTER UPDATE ON hs_hr_employee
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_personal_details_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(emp_firstname,' ' , hs_hr_employee.emp_middle_name , ' ' , emp_lastname) FROM hs_hr_employee WHERE emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  
CALL audit_update_hs_hr_employee(
@orangehrm_user,
orangehrm_user_name,
'UPDATE PERSONAL DETAILS',
new_version, 
OLD.emp_number,
affected_entity_name,
NEW.emp_firstname, OLD.emp_firstname, 
NEW.emp_middle_name, OLD.emp_middle_name, 
NEW.emp_lastname, OLD.emp_lastname, 
NEW.employee_id, OLD.employee_id, 
NEW.emp_other_id,OLD.emp_other_id, 

NEW.emp_dri_lice_num, OLD.emp_dri_lice_num, 
NEW.emp_dri_lice_exp_date, OLD.emp_dri_lice_exp_date, 
NEW.emp_gender, OLD.emp_gender, 
NEW.emp_marital_status, OLD.emp_marital_status, 

NEW.nation_code, OLD.nation_code, 
NEW.emp_birthday, OLD.emp_birthday,

NEW.emp_street1 ,OLD.emp_street1 ,
NEW.emp_street2 ,OLD.emp_street2 ,
NEW.city_code, OLD.city_code,

NEW.provin_code,OLD.provin_code, 
NEW.emp_zipcode, OLD.emp_zipcode, 
NEW.coun_code, OLD.coun_code, 

NEW.emp_hm_telephone, OLD.emp_hm_telephone, 
NEW.emp_mobile, OLD.emp_mobile, 
NEW.emp_work_telephone, OLD.emp_work_telephone, 
NEW.emp_work_email, OLD.emp_work_email, 

NEW.emp_oth_email, OLD.emp_oth_email, 
NEW.emp_ssn_num, OLD.emp_ssn_num, 
NEW.emp_sin_num, OLD.emp_sin_num, 

NEW.emp_nick_name, OLD.emp_nick_name, 
NEW.emp_military_service, OLD.emp_military_service, 
NEW.emp_smoker, OLD.emp_smoker,
NEW.termination_id, OLD.termination_id,
NEW.eeo_race_ent, OLD.eeo_race_ent,
NEW.has_work_week, OLD.has_work_week
);
  
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_employee_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  CALL audit_update_hs_hr_employee_termination(@orangehrm_user, orangehrm_user_name, new_version, OLD.emp_number, affected_entity_name, NEW.termination_id, OLD.termination_id);

END;$$

CREATE PROCEDURE audit_UPDATE_hs_hr_employee
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id VARCHAR(50) CHARSET UTF8,
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_emp_firstname VARCHAR(100) CHARSET UTF8,  IN old_emp_firstname VARCHAR(100) CHARSET UTF8,
  IN new_emp_middle_name VARCHAR(100) CHARSET UTF8,  IN old_emp_middle_name VARCHAR(100) CHARSET UTF8,
  IN new_emp_lastname VARCHAR(100) CHARSET UTF8,  IN old_emp_lastname VARCHAR(100) CHARSET UTF8,
  IN new_employee_id VARCHAR(50) CHARSET UTF8,  IN old_employee_id VARCHAR(50) CHARSET UTF8,
  IN new_emp_other_id VARCHAR(100) CHARSET UTF8,  IN old_emp_other_id VARCHAR(100) CHARSET UTF8,

  IN new_emp_dri_lice_num VARCHAR(100) CHARSET UTF8,  IN old_emp_dri_lice_num VARCHAR(100) CHARSET UTF8,
  IN new_emp_dri_lice_exp_date DATE,  IN old_emp_dri_lice_exp_date DATE,
  IN new_emp_gender SMALLINT(6),  IN old_emp_gender SMALLINT(6),
  IN new_emp_marital_status VARCHAR(20) CHARSET UTF8,  IN old_emp_marital_status VARCHAR(20) CHARSET UTF8,

  IN new_nation_code INT(4),  IN old_nation_code INT(4),
  IN new_emp_birthday DATE,  IN old_emp_birthday DATE,


    IN new_emp_street1 VARCHAR(255) CHARSET UTF8,    IN old_emp_street1 VARCHAR(255) CHARSET UTF8,
    IN new_emp_street2 VARCHAR(255) CHARSET UTF8,    IN old_emp_street2 VARCHAR(255) CHARSET UTF8,
    IN new_city_code VARCHAR(255) CHARSET UTF8,    IN old_city_code VARCHAR(255) CHARSET UTF8,

    IN new_provin_code VARCHAR(255) CHARSET UTF8,    IN old_provin_code VARCHAR(255) CHARSET UTF8,
    IN new_emp_zipcode VARCHAR(255) CHARSET UTF8,    IN old_emp_zipcode VARCHAR(255) CHARSET UTF8,
    IN new_coun_code VARCHAR(255) CHARSET UTF8,    IN old_coun_code VARCHAR(255) CHARSET UTF8,

    IN new_emp_hm_telephone VARCHAR(255) CHARSET UTF8,    IN old_emp_hm_telephone VARCHAR(255) CHARSET UTF8,
    IN new_emp_mobile VARCHAR(255) CHARSET UTF8,    IN old_emp_mobile VARCHAR(255) CHARSET UTF8,
    IN new_emp_work_telephone VARCHAR(255) CHARSET UTF8,    IN old_emp_work_telephone VARCHAR(255) CHARSET UTF8,
    IN new_emp_work_email VARCHAR(255) CHARSET UTF8,    IN old_emp_work_email VARCHAR(255) CHARSET UTF8,

    IN new_emp_oth_email VARCHAR(255) CHARSET UTF8,    IN old_emp_oth_email VARCHAR(255)  CHARSET UTF8,
    IN new_emp_ssn_num VARCHAR(255) CHARSET UTF8,    IN old_emp_ssn_num VARCHAR(255) CHARSET UTF8,
    IN new_emp_sin_num VARCHAR(255) CHARSET UTF8,    IN old_emp_sin_num VARCHAR(255) CHARSET UTF8,

    IN new_emp_nick_name VARCHAR(255) CHARSET UTF8,    IN old_emp_nick_name VARCHAR(255) CHARSET UTF8,
    IN new_emp_military_service VARCHAR(255) CHARSET UTF8,    IN old_emp_military_service VARCHAR(255) CHARSET UTF8,
    IN new_emp_smoker INT(4),    IN old_emp_smoker INT(4),
    IN new_termination_id INT(4),    IN old_termination_id INT(4),
    IN new_eeo_race_ent INT(11),    IN old_eeo_race_ent INT(11),
    IN new_has_work_week TINYINT(2), IN old_has_work_week TINYINT(2)
)
BEGIN
  DECLARE action_description TEXT  CHARSET UTF8;
  DECLARE action_description_contact TEXT  CHARSET UTF8;

  DECLARE action_description_terminate TEXT  CHARSET UTF8;
  DECLARE action_description_activate TEXT  CHARSET UTF8;
  DECLARE action_description_workweek TEXT  CHARSET UTF8;
  DECLARE new_work_week_version INT;


  SET action_description = '';
  SET action_description_contact = '';
  SET action_description_terminate = '';
  SET action_description_activate = '';
  SET action_description_workweek = '';
  SET @ssn_field_name = (select IFNULL((select value from ohrm_config where property like 'pim_ssn_field_label'), 'SSN Number'));
  SET @new_eeo_race_ent_str = (select `name` FROM ohrm_eeo_race_ethnicity where `id`= new_eeo_race_ent);
  SET @old_eeo_race_ent_str = (select `name` FROM ohrm_eeo_race_ethnicity where `id`= old_eeo_race_ent);
  -- Start generating the action description

  IF((new_termination_id IS NULL) && (old_termination_id IS NOT NULL)) THEN SET action_description_activate = 'Employee Activated'; END IF;
  IF (IFNULL(old_eeo_race_ent, '') != IFNULL(new_eeo_race_ent, '')) THEN SET action_description = CONCAT(action_description, ' ', 'Employee EEO Race and Ethnicity was changed from ', IFNULL(@old_eeo_race_ent_str, ' '), ' to ', IFNULL(@new_eeo_race_ent_str, ' '), '\n');END IF;
  IF (IFNULL(old_emp_firstname, '') != IFNULL(new_emp_firstname, '')) THEN SET action_description = CONCAT(action_description, ' ', 'Employee First name was changed from ', IFNULL(old_emp_firstname, ' '), ' to ', IFNULL(new_emp_firstname, ' '), '\n');END IF;
  IF (IFNULL(old_emp_middle_name, '') != IFNULL(new_emp_middle_name, '')) THEN SET action_description = CONCAT(action_description, ' ', 'Middle Name was changed from ', IFNULL(old_emp_middle_name, ' '), ' to ', IFNULL(new_emp_middle_name, ' '), '\n');END IF;
  IF (IFNULL(old_emp_lastname, '') != IFNULL(new_emp_lastname, '')) THEN SET action_description = CONCAT(action_description, ' ', 'Last Name was changed from ', IFNULL(old_emp_lastname, ' '), ' to ', IFNULL(new_emp_lastname, ' '), '\n');END IF;
  IF (IFNULL(old_employee_id, '') != IFNULL(new_employee_id, '')) THEN SET action_description = CONCAT(action_description, ' ', 'Employee Id was changed from ', IFNULL(old_employee_id, ' '), ' to ', IFNULL(new_employee_id, ' '), '\n');END IF;
  IF (IFNULL(old_emp_other_id, '') != IFNULL(new_emp_other_id, '')) THEN SET action_description = CONCAT(action_description, ' ', 'Other Id was changed from ', IFNULL(old_emp_other_id, ' '), ' to ', IFNULL(new_emp_other_id, ' '), '\n');END IF;
  IF (IFNULL(old_emp_dri_lice_num, '') != IFNULL(new_emp_dri_lice_num, '')) THEN SET action_description = CONCAT(action_description, ' ', 'Driver''s license number was changed from ', IFNULL(old_emp_dri_lice_num, ' '), ' to ', IFNULL(new_emp_dri_lice_num, ' '), '\n');END IF;
  IF (IFNULL(old_emp_dri_lice_exp_date, '') != IFNULL(new_emp_dri_lice_exp_date, '')) THEN SET action_description = CONCAT(action_description, ' ', 'License Expiry Date was changed from ', IFNULL(old_emp_dri_lice_exp_date, ' '), ' to ', IFNULL(new_emp_dri_lice_exp_date, ' '), '\n');END IF;
  IF ( (IFNULL(old_emp_gender, '')  != IFNULL(new_emp_gender, '')) AND (new_emp_gender != '0') ) THEN SET action_description = CONCAT(action_description, ' ', 'Gender was changed from ', IFNULL(get_static_reference_value('hs_hr_employee', 'emp_gender', old_emp_gender), ' '), ' to ', IFNULL(get_static_reference_value('hs_hr_employee', 'emp_gender', new_emp_gender), ' '), '\n');END IF;
  IF ( (IFNULL(old_emp_marital_status, '') != IFNULL(new_emp_marital_status, '') ) AND (new_emp_marital_status != '0') ) THEN SET action_description = CONCAT(action_description, ' ', 'Marital Status was changed from ', IFNULL(old_emp_marital_status, ' '), ' to ', IFNULL(new_emp_marital_status, ' '), '\n');END IF;
  IF (IFNULL(old_nation_code, '') != IFNULL(new_nation_code, '')) THEN SET action_description = CONCAT(action_description, ' ', 'Nationality was changed from ', IFNULL((SELECT `name` FROM `ohrm_nationality` WHERE `id` = old_nation_code), ' '), ' to ', IFNULL((SELECT `name` FROM `ohrm_nationality` WHERE `id` = new_nation_code), ' '),'\n') ;END IF;
  IF (IFNULL(old_emp_birthday, '') != IFNULL(new_emp_birthday, '')) THEN SET action_description = CONCAT(action_description, ' ', 'Date of Birth was changed from ', IFNULL(old_emp_birthday, ' '), ' to ', IFNULL(new_emp_birthday, ' '), '\n');END IF;

IF (IFNULL(old_emp_ssn_num, '') != IFNULL(new_emp_ssn_num, '')) THEN SET action_description = CONCAT(action_description, ' ', @ssn_field_name, ' was changed from ', IF(old_emp_ssn_num IS NULL OR old_emp_ssn_num = '', '-', CONCAT('{encrypted:', old_emp_ssn_num, ':personal_information}')), ' to ', IF(new_emp_ssn_num IS NULL OR new_emp_ssn_num = '', '-', CONCAT('{encrypted:', new_emp_ssn_num, ':personal_information}')), '\n');END IF;
IF (IFNULL(old_emp_sin_num, '') != IFNULL(new_emp_sin_num, '')) THEN SET action_description = CONCAT(action_description, ' ', '	SIN Number was changed from ', IFNULL(old_emp_sin_num, ' '), ' to ', IFNULL(new_emp_sin_num, ' '), '\n');END IF;
IF (IFNULL(old_emp_nick_name, '') != IFNULL(new_emp_nick_name, '')) THEN SET action_description = CONCAT(action_description, ' ', 'Nick Name was changed from ', IFNULL(old_emp_nick_name, ' '), ' to ', IFNULL(new_emp_nick_name, ' '), '\n');END IF;
IF (IFNULL(old_emp_military_service, '') != IFNULL(new_emp_military_service, '')) THEN SET action_description = CONCAT(action_description, ' ', 'Military Service was changed from ', IFNULL(old_emp_military_service, ' '), ' to ', IFNULL(new_emp_military_service, ' '), '\n');END IF;

IF (IFNULL(old_emp_smoker, '') != IFNULL(new_emp_smoker, '')) THEN SET action_description = CONCAT(action_description, ' ', 'Smoker was changed from ',  IF(old_emp_smoker = 0,'No','Yes') , ' to ', IF(new_emp_smoker = 0,'No','Yes'), '\n');END IF;



IF (IFNULL(new_emp_street1, '') != IFNULL(old_emp_street1, '')) THEN SET action_description_contact = CONCAT(action_description_contact, ' ', 'Address Street 1 was changed from ', IFNULL(old_emp_street1, ' '), ' to ', IFNULL(new_emp_street1, ' '), '\n');END IF;
IF (IFNULL(new_emp_street2, '') != IFNULL(old_emp_street2, '')) THEN SET action_description_contact = CONCAT(action_description_contact, ' ', 'Address Street 2 was changed from ', IFNULL(old_emp_street2, ' '), ' to ', IFNULL(new_emp_street2, ' '), '\n');END IF;
IF (IFNULL(new_city_code, '') != IFNULL(old_city_code, '')) THEN SET action_description_contact = CONCAT(action_description_contact, ' ', 'City was changed from ', IFNULL(old_city_code, ' '), ' to ', IFNULL(new_city_code, ' '), '\n');END IF;
IF (IFNULL(new_provin_code, '') != IFNULL(old_provin_code, '')) THEN SET action_description_contact = CONCAT(action_description_contact, ' ', 'State/Province was changed from ', IFNULL(old_provin_code, ' '), ' to ', IFNULL(new_provin_code, ' '), '\n');END IF;
IF (IFNULL(new_emp_zipcode, '') != IFNULL(old_emp_zipcode, '')) THEN SET action_description_contact = CONCAT(action_description_contact, ' ', 'Zip/Postal Code was changed from', IFNULL(old_emp_zipcode, ' '), ' to ', IFNULL(new_emp_zipcode, ' '), '\n');END IF;
IF (IFNULL(new_coun_code, '') != IFNULL(old_coun_code, '')) THEN SET action_description_contact = CONCAT(action_description_contact, ' ', 'Country was changed from ', IFNULL((SELECT `name` FROM `hs_hr_country` WHERE `cou_code` = old_coun_code), ' '), ' to ', IFNULL((SELECT `name` FROM `hs_hr_country` WHERE `cou_code` = new_coun_code), ' '), '\n');END IF;
IF (IFNULL(new_emp_hm_telephone , '') != IFNULL(old_emp_hm_telephone, '')) THEN SET action_description_contact = CONCAT(action_description_contact, ' ', 'Home Telephone was changed from ', IFNULL(old_emp_hm_telephone, ' '), ' to ', IFNULL(new_emp_hm_telephone, ' '), '\n');END IF;
IF (IFNULL(new_emp_mobile , '') != IFNULL(old_emp_mobile, '')) THEN SET action_description_contact = CONCAT(action_description_contact, ' ', 'Mobile Number was changed from ', IFNULL(old_emp_mobile, ' '), ' to ', IFNULL(new_emp_mobile, ' '), '\n');END IF;
IF (IFNULL(new_emp_work_telephone , '') != IFNULL(old_emp_work_telephone, '')) THEN SET action_description_contact = CONCAT(action_description_contact, ' ', 'Work Telephone number was changed from ', IFNULL(old_emp_work_telephone, ' '), ' to ', IFNULL(new_emp_work_telephone, ' '), '\n');END IF;
IF (IFNULL(new_emp_work_email , '') != IFNULL(old_emp_work_email, '')) THEN SET action_description_contact = CONCAT(action_description_contact, ' ', 'Work Email was changed from ', IFNULL(old_emp_work_email, ' '), ' to ', IFNULL(new_emp_work_email, ' '), '\n');END IF;
IF (IFNULL(new_emp_oth_email , '') != IFNULL(old_emp_oth_email, '')) THEN SET action_description_contact = CONCAT(action_description_contact, ' ', 'Other Email was changed from', IFNULL(old_emp_oth_email, ' '), ' to ', IFNULL(new_emp_oth_email, ' '), '\n');END IF;
IF (old_has_work_week != new_has_work_week) THEN SET action_description_workweek = CONCAT(action_description_workweek, ' ', 'Work week was changed from ',  IF(old_has_work_week = 0,'Disabled','Enabled') , ' to ', IF(new_has_work_week = 0,'Disabled','Enabled'), '\n');END IF;


-- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_personal_details_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;

 IF (action_description_contact != '') THEN
    INSERT INTO `ohrm_audittrail_pim_contact_info_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      'UPDATE CONTACT DETAILS',
      affected_entity_id,
      affected_entity_name,
      action_description_contact
    );
  END IF;

  IF (action_description_activate != '') THEN


    INSERT INTO `ohrm_audittrail_pim_employee_trail` (action_time, action_owner_id, action_owner_name, version_id, action, affected_entity_id, affected_entity_name, action_description)  VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      'UPDATE ACTIVATION DETAILS',
      affected_entity_id,
      affected_entity_name,
      action_description_activate
    );

  END IF;

  IF (action_description_workweek != '') THEN
    SET new_work_week_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_work_week_trail` WHERE `affected_entity_id` = affected_entity_id) + 1;
    SET new_work_week_version = IFNULL(new_work_week_version, 1);

      INSERT INTO `ohrm_audittrail_pim_work_week_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_work_week_version,
      'UPDATE WORK WEEK DETAILS',
      affected_entity_id,
      affected_entity_name,
      action_description_workweek
    );
  END IF;

END;$$

CREATE TRIGGER watch_delete_hs_hr_employee
BEFORE DELETE ON hs_hr_employee
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_personal_details_trail` WHERE `affected_entity_id` = OLD.employee_id) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(emp_firstname,' ' , hs_hr_employee.emp_middle_name , ' ' , emp_lastname) FROM hs_hr_employee WHERE emp_number = OLD.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_DELETE_hs_hr_employee(@orangehrm_user, orangehrm_user_name, 'DELETE EMPLOYEE DETAILS', new_version, OLD.emp_number, affected_entity_name, OLD.employee_id, OLD.emp_firstname, OLD.emp_middle_name, OLD.emp_lastname, OLD.emp_other_id, OLD.emp_dri_lice_num, OLD.emp_dri_lice_exp_date, OLD.emp_gender, OLD.emp_marital_status, OLD.nation_code, OLD.emp_birthday, OLD.emp_street1 ,OLD.emp_street2 ,OLD.city_code, OLD.provin_code, OLD.emp_zipcode, OLD.coun_code, OLD.emp_hm_telephone, OLD.emp_mobile,OLD.emp_work_telephone, OLD.emp_work_email, OLD.emp_oth_email, OLD.emp_ssn_num, OLD.emp_sin_num, OLD.emp_nick_name, OLD.emp_military_service, OLD.emp_smoker);

END;$$

CREATE PROCEDURE audit_DELETE_hs_hr_employee
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN old_employee_id VARCHAR(50) CHARSET UTF8,
  IN old_emp_firstname VARCHAR(255) CHARSET UTF8,
  IN old_emp_middle_name VARCHAR(255) CHARSET UTF8,

  IN old_emp_lastname VARCHAR(255) CHARSET UTF8,  
  IN old_emp_other_id VARCHAR(100) CHARSET UTF8,
  IN old_emp_dri_lice_num VARCHAR(100) CHARSET UTF8,
  IN old_emp_dri_lice_exp_date DATE,
  IN old_emp_gender SMALLINT(6),
  IN old_emp_marital_status VARCHAR(20) CHARSET UTF8,
  IN old_nation_code INT(4),
  IN old_emp_birthday DATE,   
    IN old_emp_street1 VARCHAR(255) CHARSET UTF8,    
    IN old_emp_street2 VARCHAR(255) CHARSET UTF8,   
    IN old_city_code VARCHAR(255) CHARSET UTF8,   
    IN old_provin_code VARCHAR(255) CHARSET UTF8,  
    IN old_emp_zipcode VARCHAR(255) CHARSET UTF8,   
    IN old_coun_code VARCHAR(255) CHARSET UTF8, 
    IN old_emp_hm_telephone VARCHAR(255) CHARSET UTF8,   
    IN old_emp_mobile VARCHAR(255) CHARSET UTF8,  
    IN old_emp_work_telephone VARCHAR(255) CHARSET UTF8, 
    IN old_emp_work_email VARCHAR(255) CHARSET UTF8,   
    IN old_emp_oth_email VARCHAR(255) CHARSET UTF8,


    IN old_emp_ssn_num VARCHAR(255) CHARSET UTF8,
    IN old_emp_sin_num VARCHAR(255) CHARSET UTF8,
    IN old_emp_nick_name VARCHAR(255) CHARSET UTF8,
    IN old_emp_military_service VARCHAR(255) CHARSET UTF8,
    IN old_emp_smoker INT(4)
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;
  DECLARE action_description_contact VARCHAR(1000) CHARSET UTF8;

-- for emergency contacts
  DECLARE emp_number INT(7);
  DECLARE eec_seqno DECIMAL(2,0);
  DECLARE eec_name VARCHAR(100) CHARSET UTF8;
  DECLARE eec_relationship VARCHAR(100) CHARSET UTF8;
  DECLARE eec_home_no VARCHAR(100) CHARSET UTF8;
  DECLARE eec_mobile_no VARCHAR(100) CHARSET UTF8;
  DECLARE eec_office_no VARCHAR(100) CHARSET UTF8;


  SET action_description = '';
  SET action_description_contact = '';
  

  -- Start generating the action description

  IF(IFNULL(old_emp_firstname,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Employee was deleted (Name:  ', old_emp_firstname, ' ',old_emp_middle_name,' ',old_emp_lastname, ', Employee Id: ',old_employee_id,')\n');END IF;

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_employee_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;

    
END;$$ 



DELIMITER ;



DELIMITER $$

DROP TRIGGER IF EXISTS `watch_insert_hs_hr_emp_passport`;$$
DROP PROCEDURE IF EXISTS `audit_insert_hs_hr_emp_passport`;$$

DROP TRIGGER IF EXISTS `watch_update_hs_hr_emp_passport`;$$
DROP PROCEDURE IF EXISTS `audit_update_hs_hr_emp_passport`;$$

DROP TRIGGER IF EXISTS `watch_delete_hs_hr_emp_passport`;$$
DROP PROCEDURE IF EXISTS `audit_delete_hs_hr_emp_passport`;$$

CREATE TRIGGER watch_insert_hs_hr_emp_passport
AFTER INSERT ON hs_hr_emp_passport
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = 1;
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_passport LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_passport.emp_number WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());

  CALL audit_insert_hs_hr_emp_passport(@orangehrm_user, orangehrm_user_name, 'ADD IMMIGRATION DETAILS', new_version, NEW.emp_number, affected_entity_name, NEW.ep_passport_num, NEW.ep_passportissueddate, NEW.ep_passportexpiredate, NEW.ep_comments, NEW.ep_passport_type_flg, NEW.ep_i9_status, NEW.ep_i9_review_date, NEW.cou_code);

END;$$

CREATE PROCEDURE audit_INSERT_hs_hr_emp_passport
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_ep_passport_num VARCHAR(255) CHARSET UTF8,
  IN new_ep_passportissueddate DATE,
  IN new_ep_passportexpiredate DATE,
  IN new_ep_comments VARCHAR(255) CHARSET UTF8,
  IN new_ep_passport_type_flg SMALLINT(6),
  IN new_ep_i9_status VARCHAR(100) CHARSET UTF8,
  IN new_ep_i9_review_date DATE,
  IN new_cou_code VARCHAR(6) CHARSET UTF8

)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description


   IF (new_ep_passport_num != '') THEN SET action_description = IFNULL(CONCAT(action_description, ' ', 'New Immigration info was added (Passport/Visa Number:  ', new_ep_passport_num,')\n'),'');END IF;
   IF (new_ep_passport_type_flg != '') THEN SET action_description = IFNULL(CONCAT(action_description, ' ', 'Document Type:  ', get_static_reference_value('hs_hr_emp_passport', 'document_type', new_ep_passport_type_flg),'\n'),'');END IF;
   IF (new_ep_passportissueddate != '' AND new_ep_passportissueddate IS NOT NULL ) THEN SET action_description = IFNULL(CONCAT(action_description, ' ', 'Issued Date:  ', new_ep_passportissueddate,'\n'),'');END IF;
   IF (new_ep_passportexpiredate != '' AND new_ep_passportexpiredate IS NOT NULL ) THEN SET action_description = IFNULL(CONCAT(action_description, ' ', 'Expiry Date:  ', new_ep_passportexpiredate,'\n'),'');END IF;
   IF (new_ep_i9_status != '') THEN SET action_description = IFNULL(CONCAT(action_description, ' ', 'Eligible Status:  ', new_ep_i9_status,'\n'),'');END IF;
   IF (new_cou_code != '') THEN SET action_description = IFNULL(CONCAT(action_description, ' ', 'Issued By:  ', (SELECT `name` FROM `hs_hr_country` WHERE `cou_code` = new_cou_code),'\n'),'');END IF;
   IF (new_ep_i9_review_date != '') THEN SET action_description = IFNULL( CONCAT( action_description, ' ', 'Eligible Review Date:  ', new_ep_i9_review_date,'\n'),'');END IF;   
   IF (new_ep_comments != '') THEN SET action_description = IFNULL(CONCAT(action_description, ' ', 'Comment:', new_ep_comments, '\n'),'');END IF;
  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_passport_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
   END IF;
 
END;$$ 


CREATE TRIGGER watch_update_hs_hr_emp_passport
AFTER UPDATE ON hs_hr_emp_passport
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_passport_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_passport LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_passport.emp_number WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());

  CALL audit_update_hs_hr_emp_passport(@orangehrm_user, orangehrm_user_name, 'UPDATE IMMIGRATION DETAILS', new_version, OLD.emp_number, affected_entity_name, NEW.ep_passport_num, OLD.ep_passport_num, NEW.ep_passportissueddate, OLD.ep_passportissueddate, NEW.ep_passportexpiredate, OLD.ep_passportexpiredate, NEW.ep_comments, OLD.ep_comments, NEW.ep_passport_type_flg, OLD.ep_passport_type_flg, NEW.ep_i9_status, OLD.ep_i9_status, NEW.ep_i9_review_date, OLD.ep_i9_review_date, NEW.cou_code, OLD.cou_code);
END;$$

CREATE PROCEDURE audit_UPDATE_hs_hr_emp_passport
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,  
  IN new_ep_passport_num VARCHAR(255) CHARSET UTF8,
  IN old_ep_passport_num VARCHAR(255) CHARSET UTF8,
  IN new_ep_passportissueddate DATE,
  IN old_ep_passportissueddate DATE,
  IN new_ep_passportexpiredate DATE,
  IN old_ep_passportexpiredate DATE,
  IN new_ep_comments VARCHAR(255) CHARSET UTF8,
  IN old_ep_comments VARCHAR(255) CHARSET UTF8,
  IN new_ep_passport_type_flg SMALLINT(6),
  IN old_ep_passport_type_flg SMALLINT(6),
  IN new_ep_i9_status VARCHAR(100) CHARSET UTF8,
  IN old_ep_i9_status VARCHAR(100) CHARSET UTF8,
  IN new_ep_i9_review_date DATE,
  IN old_ep_i9_review_date DATE,
  IN new_cou_code VARCHAR(6) CHARSET UTF8,
  IN old_cou_code VARCHAR(6) CHARSET UTF8
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

  SET action_description = CONCAT(action_description, ' ', 'Number was changed from ', IFNULL(old_ep_passport_num, ' '), ' to ', IFNULL(new_ep_passport_num, ''), '\n');
  IF (IFNULL(old_ep_passportissueddate,'') != IFNULL(new_ep_passportissueddate,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Issued Date was changed from ', IFNULL(old_ep_passportissueddate, ' '), ' to ', IFNULL(new_ep_passportissueddate, ' '), '\n');END IF;
  IF (IFNULL(old_ep_passportexpiredate,'') != IFNULL(new_ep_passportexpiredate,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Expiry Date was changed from ', IFNULL(old_ep_passportexpiredate, ' '), ' to ', IFNULL(new_ep_passportexpiredate, ' '), '\n');END IF;
  IF (old_ep_comments != new_ep_comments) THEN SET action_description = CONCAT(action_description, ' ', 'Comments was changed from ', IFNULL(old_ep_comments, ' '), ' to ', IFNULL(new_ep_comments, ' '), '\n');END IF;
  IF (old_ep_passport_type_flg != new_ep_passport_type_flg) THEN SET action_description = CONCAT(action_description, ' ', 'Document type was changed from ', IFNULL( get_static_reference_value('hs_hr_emp_passport', 'document_type', old_ep_passport_type_flg)  , ' '), ' to ', IFNULL(get_static_reference_value('hs_hr_emp_passport', 'document_type', new_ep_passport_type_flg), ' '), '\n');END IF;
  IF (old_ep_i9_status != new_ep_i9_status) THEN SET action_description = CONCAT(action_description, ' ', 'Eligible Status was changed from ', IFNULL(old_ep_i9_status, ' '), ' to ', IFNULL(new_ep_i9_status, ' '), '\n');END IF;
  IF (IFNULL(old_ep_i9_review_date,'') != IFNULL(new_ep_i9_review_date,'') ) THEN SET action_description = CONCAT(action_description, ' ', 'Eligible Review Date was changed from ', IFNULL(old_ep_i9_review_date, ' '), ' to ', IFNULL(new_ep_i9_review_date, ' '), '\n');END IF;
  IF (old_cou_code != new_cou_code) THEN SET action_description = CONCAT(action_description, ' ', 'Issued By was changed from ', IFNULL((SELECT `name` FROM `hs_hr_country` WHERE `cou_code` = old_cou_code), ' '), ' to ', IFNULL((SELECT `name` FROM `hs_hr_country` WHERE `cou_code` = new_cou_code), ' '), '\n');END IF;

  -- End generating the action description

   IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_passport_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 


CREATE TRIGGER watch_delete_hs_hr_emp_passport
BEFORE DELETE ON hs_hr_emp_passport
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_passport_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_passport LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_passport.emp_number WHERE hs_hr_employee.emp_number = OLD.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());

  CALL audit_delete_hs_hr_emp_passport(@orangehrm_user, orangehrm_user_name, 'DELETE IMMIGRATION DETAILS', new_version, OLD.emp_number, affected_entity_name, OLD.ep_passport_num, OLD.ep_passportissueddate, OLD.ep_passportexpiredate, OLD.ep_comments, OLD.ep_passport_type_flg, OLD.ep_i9_status, OLD.ep_i9_review_date, OLD.cou_code);
END;$$

CREATE PROCEDURE audit_DELETE_hs_hr_emp_passport
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN old_ep_passport_num VARCHAR(255) CHARSET UTF8,
  IN old_ep_passportissueddate DATE,
  IN old_ep_passportexpiredate DATE,
  IN old_ep_comments VARCHAR(255) CHARSET UTF8,
  IN old_ep_passport_type_flg SMALLINT(6),
  IN old_ep_i9_status VARCHAR(100) CHARSET UTF8,
  IN old_ep_i9_review_date DATE,
  IN old_cou_code VARCHAR(6) CHARSET UTF8
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

   IF (old_ep_passport_num != '' ) THEN SET action_description = CONCAT(action_description, ' ', 'Immigration info has been deleted (Passport/Visa Number:  ', old_ep_passport_num,')\n'); END IF;
   IF (old_ep_passport_type_flg != '' ) THEN SET action_description = CONCAT(action_description, ' ', 'Document Type:  ', get_static_reference_value('hs_hr_emp_passport', 'document_type', old_ep_passport_type_flg),'\n');END IF;   
   IF (old_ep_passportissueddate != '' ) THEN SET action_description = CONCAT(action_description, ' ', 'Issued Date:  ', old_ep_passportissueddate,'\n');END IF;
   IF (old_ep_passportexpiredate != '' ) THEN SET action_description = CONCAT(action_description, ' ', 'Expiry Date:  ', old_ep_passportexpiredate,'\n');END IF;
   IF (old_ep_i9_status != '' ) THEN SET action_description = CONCAT(action_description, ' ', 'Eligible Status:  ', old_ep_i9_status,'\n');END IF;
   IF (old_cou_code != '' ) THEN SET action_description = CONCAT(action_description, ' ', 'Issued By:  ',(SELECT `name` FROM `hs_hr_country` WHERE `cou_code` = old_cou_code),'\n'); END IF;
   IF (old_ep_i9_review_date != '' ) THEN SET action_description = CONCAT(action_description, ' ', 'Eligible Review Date:  ', old_ep_i9_review_date,'\n');END IF;
   IF (old_ep_comments != '' ) THEN SET action_description = CONCAT(action_description, ' ', 'Comments:  ', old_ep_comments,'\n');END IF;

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_passport_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 


DELIMITER ;



DELIMITER $$



DROP TRIGGER IF EXISTS `watch_insert_hs_hr_emp_directdebit`;$$
DROP PROCEDURE IF EXISTS `audit_insert_hs_hr_emp_directdebit`;$$

DROP TRIGGER IF EXISTS `watch_update_hs_hr_emp_directdebit`;$$
DROP PROCEDURE IF EXISTS `audit_update_hs_hr_emp_directdebit`;$$

DROP TRIGGER IF EXISTS `watch_delete_hs_hr_emp_directdebit`;$$
DROP PROCEDURE IF EXISTS `audit_delete_hs_hr_emp_directdebit`;$$

DROP TABLE IF EXISTS `ohrm_audittrail_pim_emp_education_trail`;$$

DROP TRIGGER IF EXISTS `watch_insert_ohrm_emp_education`;$$
DROP PROCEDURE IF EXISTS `audit_insert_ohrm_emp_education`;$$

DROP TRIGGER IF EXISTS `watch_update_ohrm_emp_education`;$$
DROP PROCEDURE IF EXISTS `audit_update_ohrm_emp_education`;$$

DROP TRIGGER IF EXISTS `watch_delete_ohrm_emp_education`;$$
DROP PROCEDURE IF EXISTS `audit_delete_ohrm_emp_education`;$$

CREATE TRIGGER watch_insert_ohrm_emp_education
AFTER INSERT ON ohrm_emp_education
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = 1;
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM ohrm_emp_education LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_emp_education.emp_number WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_insert_ohrm_emp_education(@orangehrm_user, orangehrm_user_name, 'ADD EDUCATION QUALIFICATION DETAILS', new_version, NEW.emp_number, affected_entity_name, NEW.education_id,  NEW.institute, NEW.major, NEW.year, NEW.score, NEW.start_date, NEW.end_date);

END;$$

CREATE PROCEDURE audit_INSERT_ohrm_emp_education
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(11),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_education_id INT(11),
  IN new_institute VARCHAR(100) CHARSET UTF8,
  IN new_major VARCHAR(100) CHARSET UTF8,
  IN new_year DECIMAL(4,0),
  IN new_score VARCHAR(25) CHARSET UTF8,
  IN new_start_date DATE,
  IN new_end_date DATE


)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

   SET action_description = CONCAT(action_description, ' ', 'New Education Info was added (Level: ',IFNULL((SELECT `name` FROM `ohrm_education` WHERE `id` = IFNULL(new_education_id, '')),''),')\n');
 IF (IFNULL(new_institute, '') != '') THEN SET action_description =  CONCAT(action_description, ' ','Institute: ', IFNULL(new_institute, ' '), '\n');END IF;
IF (IFNULL(new_major, '') != '') THEN SET action_description =  CONCAT(action_description, ' ','Major/Specialization: ', IFNULL(new_major, ' '), '\n');END IF;
IF (IFNULL(new_year, '') != '') THEN SET action_description =  CONCAT(action_description, ' ','Year: ', IFNULL(new_year, ' '), '\n');END IF;
IF (IFNULL(new_score, '') != '') THEN SET action_description =  CONCAT(action_description, ' ','GPA/Score: ', IFNULL(new_score, ' '), '\n');END IF;
IF (IFNULL(new_start_date, '') != '') THEN SET action_description =  CONCAT(action_description, ' ','Start Date: ', IFNULL(new_start_date, ' '), '\n');END IF;
IF (IFNULL(new_end_date, '') != '') THEN SET action_description =  CONCAT(action_description, ' ','End Date: ', IFNULL(new_end_date, ' '), '\n');END IF;

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_qualification_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 


CREATE TRIGGER watch_update_ohrm_emp_education
AFTER UPDATE ON ohrm_emp_education
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_qualification_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM ohrm_emp_education LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_emp_education.emp_number WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_update_ohrm_emp_education(@orangehrm_user, orangehrm_user_name, 'UPDATE EDUCATION QUALIFICATION DETAILS', new_version, OLD.emp_number, affected_entity_name, NEW.education_id, OLD.education_id, NEW.institute, OLD.institute, NEW.major, OLD.major, NEW.year, OLD.year, NEW.score, OLD.score, NEW.start_date, OLD.start_date, NEW.end_date, OLD.end_date);
END;$$

CREATE PROCEDURE audit_UPDATE_ohrm_emp_education
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(11),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_education_id INT(11),
  IN old_education_id INT(11),
  IN new_institute VARCHAR(100) CHARSET UTF8,
  IN old_institute VARCHAR(100) CHARSET UTF8,
  IN new_major VARCHAR(100) CHARSET UTF8,
  IN old_major VARCHAR(100) CHARSET UTF8,
  IN new_year DECIMAL(4,0),
  IN old_year DECIMAL(4,0),
  IN new_score VARCHAR(25) CHARSET UTF8,
  IN old_score VARCHAR(25) CHARSET UTF8,
  IN new_start_date DATE,
  IN old_start_date DATE,
  IN new_end_date DATE,
  IN old_end_date DATE
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

  SET action_description = CONCAT(action_description, ' ', '(Level: ',IFNULL((SELECT `name` FROM `ohrm_education` WHERE `id` = IFNULL(old_education_id, '')),''),')\n');
  IF (IFNULL(old_education_id, '') != IFNULL(new_education_id, ' ')) THEN SET action_description = CONCAT(action_description, ' ', 'Level was changed from ', IFNULL((SELECT `name` FROM `ohrm_education` WHERE `id` = IFNULL(old_education_id, '')), ' '), ' to ', IFNULL((SELECT `name` FROM `ohrm_education` WHERE `id` = IFNULL(new_education_id, '')), ' '), '\n');END IF;
  IF (IFNULL(old_institute, '') != IFNULL(new_institute, ' ')) THEN SET action_description = CONCAT(action_description, ' ', 'Institute was changed from ', IFNULL(old_institute, ' '), ' to ', IFNULL(new_institute, ' '), '\n');END IF;
  IF (IFNULL(old_major, '') != IFNULL(new_major, ' ')) THEN SET action_description = CONCAT(action_description, ' ', 'Major/Specialization was changed from ', IFNULL(old_major, ' '), ' to ', IFNULL(new_major, ' '), '\n');END IF;
  IF (IFNULL(old_year, '') != IFNULL(new_year, ' ')) THEN SET action_description = CONCAT(action_description, ' ', 'Year was changed from ', IFNULL(old_year, ' '), ' to ', IFNULL(new_year, ' '), '\n');END IF;
  IF (IFNULL(old_score, '') != IFNULL(new_score, ' ')) THEN SET action_description = CONCAT(action_description, ' ', 'GPA/Score was changed from ', IFNULL(old_score, ' '), ' to ', IFNULL(new_score, ' '), '\n');END IF;
  IF (IFNULL(old_start_date, '') != IFNULL(new_start_date, ' ')) THEN SET action_description = CONCAT(action_description, ' ', 'Start Date was changed from ', IFNULL(old_start_date, ' '), ' to ', IFNULL(new_start_date, ' '), '\n');END IF;
  IF (IFNULL(old_end_date, '') != IFNULL(new_end_date, ' ')) THEN SET action_description = CONCAT(action_description, ' ', 'End Date was changed from ', IFNULL(old_end_date, ''), ' to ', IFNULL(new_end_date, ' '), '\n');END IF;

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_qualification_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 


CREATE TRIGGER watch_delete_ohrm_emp_education
BEFORE DELETE ON ohrm_emp_education
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_qualification_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM ohrm_emp_education LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_emp_education.emp_number WHERE hs_hr_employee.emp_number = OLD.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_delete_ohrm_emp_education(@orangehrm_user, orangehrm_user_name, 'DELETE EDUCATION QUALIFICATION DETAILS', new_version, OLD.emp_number, affected_entity_name, OLD.education_id,  OLD.institute, OLD.major, OLD.year, OLD.score, OLD.start_date, OLD.end_date);
END;$$

CREATE PROCEDURE audit_DELETE_ohrm_emp_education
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(11),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN old_education_id INT(11),
  IN old_institute VARCHAR(100) CHARSET UTF8,
  IN old_major VARCHAR(100) CHARSET UTF8,
  IN old_year DECIMAL(4,0),
  IN old_score VARCHAR(25) CHARSET UTF8,
  IN old_start_date DATE,
  IN old_end_date DATE

)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description


   SET action_description = CONCAT(action_description, ' ', 'Education Info was deleted (Level: ',IFNULL((SELECT `name` FROM `ohrm_education` WHERE `id` = IFNULL(old_education_id, '')),''),')\n');
   IF (IFNULL(old_institute, '') != '') THEN SET action_description =  CONCAT(action_description, ' ','Institute: ', IFNULL(old_institute, ' '), '\n');END IF;
IF (IFNULL(old_major, '') != '') THEN SET action_description =  CONCAT(action_description, ' ','Major/Specialization: ', IFNULL(old_major, ' '), '\n');END IF;
IF (IFNULL(old_year, '') != '') THEN SET action_description =  CONCAT(action_description, ' ','Year: ', IFNULL(old_year, ' '), '\n');END IF;
IF (IFNULL(old_score, '') != '') THEN SET action_description =  CONCAT(action_description, ' ','GPA/Score: ', IFNULL(old_score, ' '), '\n');END IF;
IF (IFNULL(old_start_date, '') != '') THEN SET action_description =  CONCAT(action_description, ' ','Start Date: ', IFNULL(old_start_date, ' '), '\n');END IF;
IF (IFNULL(old_end_date, '') != '') THEN SET action_description =  CONCAT(action_description, ' ','End Date: ', IFNULL(old_end_date, ' '), '');END IF;

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_qualification_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 



DROP TABLE IF EXISTS `ohrm_audittrail_pim_emp_skill_trail`;$$

DROP TRIGGER IF EXISTS `watch_insert_hs_hr_emp_skill`;$$
DROP PROCEDURE IF EXISTS `audit_insert_hs_hr_emp_skill`;$$

DROP TRIGGER IF EXISTS `watch_update_hs_hr_emp_skill`;$$
DROP PROCEDURE IF EXISTS `audit_update_hs_hr_emp_skill`;$$

DROP TRIGGER IF EXISTS `watch_delete_hs_hr_emp_skill`;$$
DROP PROCEDURE IF EXISTS `audit_delete_hs_hr_emp_skill`;$$


CREATE TRIGGER watch_insert_hs_hr_emp_skill
AFTER INSERT ON hs_hr_emp_skill
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = 1;
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_skill LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_skill.emp_number WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_insert_hs_hr_emp_skill(@orangehrm_user, orangehrm_user_name, 'ADD SKILL DETAILS', new_version, NEW.emp_number, affected_entity_name, NEW.skill_id,NEW.years_of_exp, NEW.comments);
END;$$

CREATE PROCEDURE audit_INSERT_hs_hr_emp_skill
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_skill_id INT(11),
  IN new_years_of_exp DECIMAL(2,0), 
  IN new_comments VARCHAR(100) CHARSET UTF8
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

   IF (IFNULL(new_skill_id,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'New Skill Info was added (Skill: ',IFNULL((SELECT `name` FROM `ohrm_skill` WHERE `id` = new_skill_id),''),')\n');END IF;
   IF (IFNULL(new_years_of_exp,'') != '' AND new_years_of_exp != 0) THEN SET action_description = CONCAT(action_description, ' ', 'Years of Experience : ',IFNULL(new_years_of_exp,''),'\n');END IF;
   IF (IFNULL(new_comments,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Comments : ',IFNULL(new_comments,''),'\n');END IF;

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_qualification_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 


CREATE TRIGGER watch_update_hs_hr_emp_skill
AFTER UPDATE ON hs_hr_emp_skill
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_qualification_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_skill LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_skill.emp_number WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_update_hs_hr_emp_skill(@orangehrm_user, orangehrm_user_name, 'UPDATE SKILL DETAILS', new_version, OLD.emp_number, affected_entity_name, NEW.skill_id, OLD.skill_id, NEW.years_of_exp, OLD.years_of_exp, NEW.comments, OLD.comments);
END;$$

CREATE PROCEDURE audit_UPDATE_hs_hr_emp_skill
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_skill_id INT(11),
  IN old_skill_id INT(11),
  IN new_years_of_exp DECIMAL(2,0),
  IN old_years_of_exp DECIMAL(2,0),
  IN new_comments VARCHAR(100) CHARSET UTF8,
  IN old_comments VARCHAR(100) CHARSET UTF8
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description
  SET action_description = CONCAT(action_description, ' ', '(Skill: ',IFNULL((SELECT `name` FROM `ohrm_skill` WHERE `id` = old_skill_id),''),')\n');
  IF (IFNULL(old_skill_id,'') != IFNULL(new_skill_id,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Skill was changed from ', IFNULL(IFNULL((SELECT `name` FROM `ohrm_skill` WHERE `id` = old_skill_id),''), ' '), ' to ', IFNULL((SELECT `name` FROM `ohrm_skill` WHERE `id` = new_skill_id), ' '), '\n');END IF;
  IF (IFNULL(old_years_of_exp,'') != IFNULL(new_years_of_exp,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Years of Experience was changed from ', IFNULL(old_years_of_exp, ' '), ' to ', IFNULL(new_years_of_exp, ' '), '\n');END IF;
  IF (IFNULL(old_comments,'') != IFNULL(new_comments,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Comments was changed from ', IFNULL(old_comments, ' '), ' to ', IFNULL(new_comments, ' '), '\n');END IF;

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_qualification_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 


CREATE TRIGGER watch_delete_hs_hr_emp_skill
BEFORE DELETE ON hs_hr_emp_skill
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_qualification_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_skill LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_skill.emp_number WHERE hs_hr_employee.emp_number = OLD.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_delete_hs_hr_emp_skill(@orangehrm_user, orangehrm_user_name, 'DELETE SKILL DETAILS', new_version, OLD.emp_number, affected_entity_name, OLD.skill_id,OLD.years_of_exp, OLD.comments);
END;$$

CREATE PROCEDURE audit_DELETE_hs_hr_emp_skill
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN old_skill_id INT(11),
  IN old_years_of_exp DECIMAL(2,0), 
  IN old_comments VARCHAR(100) CHARSET UTF8
  
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

   IF (IFNULL(old_skill_id,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Skill Info was deleted (Skill: ',IFNULL((SELECT `name` FROM `ohrm_skill` WHERE `id` = old_skill_id),''),')\n');END IF;
   IF (IFNULL(old_years_of_exp,'') != '' AND old_years_of_exp != 0) THEN SET action_description = CONCAT(action_description, ' ', 'Years of Experience : ',IFNULL(old_years_of_exp,''),'\n');END IF;
   IF (IFNULL(old_comments,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Comments : ',IFNULL(old_comments,''),'\n');END IF;

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_qualification_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 


DROP TABLE IF EXISTS `ohrm_audittrail_pim_emp_language_trail`;$$

DROP TRIGGER IF EXISTS `watch_insert_hs_hr_emp_language`;$$
DROP PROCEDURE IF EXISTS `audit_insert_hs_hr_emp_language`;$$

DROP TRIGGER IF EXISTS `watch_update_hs_hr_emp_language`;$$
DROP PROCEDURE IF EXISTS `audit_update_hs_hr_emp_language`;$$

DROP TRIGGER IF EXISTS `watch_delete_hs_hr_emp_language`;$$
DROP PROCEDURE IF EXISTS `audit_delete_hs_hr_emp_language`;$$


CREATE TRIGGER watch_insert_hs_hr_emp_language
AFTER INSERT ON hs_hr_emp_language
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = 1;
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_language LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_language.emp_number WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_insert_hs_hr_emp_language(@orangehrm_user, orangehrm_user_name, 'ADD LANGUAGE DETAILS', new_version, NEW.emp_number, affected_entity_name, NEW.lang_id, NEW.fluency,NEW.competency,NEW.comments);

END;$$

CREATE PROCEDURE audit_INSERT_hs_hr_emp_language
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_lang_id INT(11),
  IN new_fluency SMALLINT(6),
  IN new_competency SMALLINT(6),
  IN new_comments VARCHAR(100) CHARSET UTF8

)

BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description
--
   SET action_description = CONCAT(action_description, ' ', 'New Language Info was added (Language: ',IFNULL((SELECT `name` FROM `ohrm_language` WHERE `id` = new_lang_id),' '),')\n');
   SET action_description = CONCAT(action_description, ' ', 'Fluency: ',IFNULL(get_static_reference_value('hs_hr_emp_language', 'fluency', new_fluency), ' '),'\n');
   SET action_description = CONCAT(action_description, ' ', 'Competency: ',IFNULL(get_static_reference_value('hs_hr_emp_language', 'competency', new_competency), ' '),'\n');
   IF (IFNULL(new_comments,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Comments: ',IFNULL(new_comments, ' '),'\n');END IF;
--
--  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_qualification_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 


CREATE TRIGGER watch_update_hs_hr_emp_language
AFTER UPDATE ON hs_hr_emp_language
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_qualification_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_language LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_language.emp_number WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_update_hs_hr_emp_language(@orangehrm_user, orangehrm_user_name, 'UPDATE LANGUAGE DETAILS', new_version, OLD.emp_number, affected_entity_name, NEW.lang_id, OLD.lang_id, NEW.fluency, OLD.fluency, NEW.competency, OLD.competency, NEW.comments, OLD.comments);
END;$$

CREATE PROCEDURE audit_UPDATE_hs_hr_emp_language
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_lang_id INT(11),
  IN old_lang_id INT(11),
  IN new_fluency SMALLINT(6),
  IN old_fluency SMALLINT(6),
  IN new_competency SMALLINT(6),
  IN old_competency SMALLINT(6),
  IN new_comments VARCHAR(100) CHARSET UTF8,
  IN old_comments VARCHAR(100) CHARSET UTF8
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

  SET action_description = CONCAT(action_description, ' ', '(Language: ',IFNULL((SELECT `name` FROM `ohrm_language` WHERE `id` = old_lang_id),' '), ' Fluency: ',IFNULL(get_static_reference_value('hs_hr_emp_language', 'fluency', old_fluency), ' '),')\n');
 

  IF (old_lang_id != new_lang_id) THEN SET action_description = CONCAT(action_description, ' ', 'Language was changed from ', IFNULL((SELECT `name` FROM `ohrm_language` WHERE `id` = old_lang_id) , ' '), ' to ', IFNULL((SELECT `name` FROM `ohrm_language` WHERE `id` = new_lang_id), ' '), '\n');END IF;
  IF (IFNULL(old_fluency,'') != IFNULL(new_fluency,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Fluency was changed from ', IFNULL(get_static_reference_value('hs_hr_emp_language', 'fluency', old_fluency), ' '), ' to ', IFNULL(get_static_reference_value('hs_hr_emp_language', 'fluency', new_fluency), ' '), '\n');END IF;
  IF (IFNULL(old_competency,'') != IFNULL(new_competency,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Competency was changed from ', IFNULL(get_static_reference_value('hs_hr_emp_language', 'competency', old_competency), ' '), ' to ', IFNULL(get_static_reference_value('hs_hr_emp_language', 'competency', new_competency), ' '), '\n');END IF;
  IF (IFNULL(old_comments,'') != IFNULL(new_comments,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Comments was changed from ', IFNULL(old_comments, ' '), ' to ', IFNULL(new_comments, ' '), '');END IF;

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_qualification_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 


CREATE TRIGGER watch_delete_hs_hr_emp_language
BEFORE DELETE ON hs_hr_emp_language
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_qualification_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_language LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_language.emp_number WHERE hs_hr_employee.emp_number = OLD.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_delete_hs_hr_emp_language(@orangehrm_user, orangehrm_user_name, 'DELETE LANGUAGE DETAILS', new_version, OLD.emp_number, affected_entity_name, OLD.lang_id, OLD.fluency,OLD.competency,OLD.comments);
END;$$

CREATE PROCEDURE audit_DELETE_hs_hr_emp_language
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN old_lang_id INT(11),  
  IN old_fluency SMALLINT(6),  
  IN old_competency SMALLINT(6), 
  IN old_comments VARCHAR(100) CHARSET UTF8
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

   SET action_description = CONCAT(action_description, ' ', 'Language Info was deleted (Language: ',IFNULL((SELECT `name` FROM `ohrm_language` WHERE `id` = old_lang_id),' '),')\n');

   SET action_description = CONCAT(action_description, ' ', 'Fluency: ',IFNULL(get_static_reference_value('hs_hr_emp_language', 'fluency',  old_fluency),' '),'\n');
   SET action_description = CONCAT(action_description, ' ', 'Competency: ',IFNULL(get_static_reference_value('hs_hr_emp_language', 'competency',  old_competency),' '),'\n');
   SET action_description = CONCAT(action_description, ' ', 'Comments: ',IFNULL(old_comments,' '),'\n');


  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_qualification_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 


DROP TABLE IF EXISTS `ohrm_audittrail_pim_emp_license_trail`;$$

DROP TRIGGER IF EXISTS `watch_insert_ohrm_emp_license`;$$
DROP PROCEDURE IF EXISTS `audit_insert_ohrm_emp_license`;$$

DROP TRIGGER IF EXISTS `watch_update_ohrm_emp_license`;$$
DROP PROCEDURE IF EXISTS `audit_update_ohrm_emp_license`;$$

DROP TRIGGER IF EXISTS `watch_delete_ohrm_emp_license`;$$
DROP PROCEDURE IF EXISTS `audit_delete_ohrm_emp_license`;$$

CREATE TRIGGER watch_insert_ohrm_emp_license
AFTER INSERT ON ohrm_emp_license
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = 1;
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM ohrm_emp_license LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_emp_license.emp_number WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_insert_ohrm_emp_license(@orangehrm_user, orangehrm_user_name, 'ADD LICENSE DETAILS', new_version, NEW.emp_number, affected_entity_name, NEW.license_id, NEW.license_no, NEW.license_issued_date, NEW.license_expiry_date);


END;$$

CREATE PROCEDURE audit_INSERT_ohrm_emp_license
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(11),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_license_id INT(11),
  IN new_license_no VARCHAR(50) CHARSET UTF8,
  IN new_license_issued_date DATE,
  IN new_license_expiry_date DATE

)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

   SET action_description = CONCAT(action_description, ' ', 'New License Info was added (License: ',IFNULL((SELECT `name` FROM `ohrm_license` WHERE `id` = new_license_id),' '),')\n');
   IF(IFNULL(new_license_no,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'License Number: ',IFNULL(new_license_no,' '),'\n');END IF;
   IF(IFNULL(new_license_issued_date,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Issued Date: ',IFNULL(new_license_issued_date,' '),'\n');END IF;
   IF(IFNULL(new_license_expiry_date,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Expiry Date: ',IFNULL(new_license_expiry_date,' '),'\n');END IF;

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_qualification_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 


CREATE TRIGGER watch_update_ohrm_emp_license
AFTER UPDATE ON ohrm_emp_license
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_qualification_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM ohrm_emp_license LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_emp_license.emp_number WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_update_ohrm_emp_license(@orangehrm_user, orangehrm_user_name, 'UPDATE LICENSE DETAILS', new_version, OLD.emp_number, affected_entity_name, NEW.license_id, OLD.license_id, NEW.license_no, OLD.license_no, NEW.license_issued_date, OLD.license_issued_date, NEW.license_expiry_date, OLD.license_expiry_date);
END;$$

CREATE PROCEDURE audit_UPDATE_ohrm_emp_license
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(11),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_license_id INT(11),
  IN old_license_id INT(11),
  IN new_license_no VARCHAR(50) CHARSET UTF8,
  IN old_license_no VARCHAR(50) CHARSET UTF8,
  IN new_license_issued_date DATE,
  IN old_license_issued_date DATE,
  IN new_license_expiry_date DATE,
  IN old_license_expiry_date DATE
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

  SET action_description = CONCAT(action_description, ' ', '(License: ',IFNULL((SELECT `name` FROM `ohrm_license` WHERE `id` = old_license_id),' '),')\n');
  IF (old_license_id != new_license_id) THEN SET action_description = CONCAT(action_description, ' ', 'License Type was changed from ', IFNULL((SELECT `name` FROM `ohrm_license` WHERE `id` = old_license_id),' '), ' to ', IFNULL((SELECT `name` FROM `ohrm_license` WHERE `id` = new_license_id),' '), '\n');END IF;
  IF (IFNULL(old_license_no,'') != IFNULL(new_license_no,'')) THEN SET action_description = CONCAT(action_description, ' ', 'License Number was changed from ', IFNULL(old_license_no, ' '), ' to ', IFNULL(new_license_no, ' '), '\n');END IF;
  IF (IFNULL(old_license_issued_date,'') != IFNULL(new_license_issued_date,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Issued Date was changed from ', IFNULL(old_license_issued_date, ' '), ' to ', IFNULL(new_license_issued_date, ' '), '\n');END IF;
  IF (IFNULL(old_license_expiry_date,'') != IFNULL(new_license_expiry_date,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Expiry Date was changed from ', IFNULL(old_license_expiry_date, ' '), ' to ', IFNULL(new_license_expiry_date, ' '), '\n');END IF;

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_qualification_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 


CREATE TRIGGER watch_delete_ohrm_emp_license
BEFORE DELETE ON ohrm_emp_license
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_qualification_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM ohrm_emp_license LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_emp_license.emp_number WHERE hs_hr_employee.emp_number = OLD.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_delete_ohrm_emp_license(@orangehrm_user, orangehrm_user_name, 'DELETE LICENSE DETAILS', new_version, OLD.emp_number, affected_entity_name, OLD.license_id, OLD.license_no, OLD.license_issued_date, OLD.license_expiry_date);
END;$$

CREATE PROCEDURE audit_DELETE_ohrm_emp_license
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(11),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,  
  IN old_license_id INT(11), 
  IN old_license_no VARCHAR(50) CHARSET UTF8,  
  IN old_license_issued_date DATE, 
  IN old_license_expiry_date DATE
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

    SET  action_description = CONCAT(action_description, ' ', 'License Info was deleted (License: ',IFNULL((SELECT `name` FROM `ohrm_license` WHERE `id` = old_license_id),' '),')\n');
   IF(IFNULL(old_license_no,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'License Number: ',IFNULL(old_license_no,' '),'\n');END IF;
   IF(IFNULL(old_license_issued_date,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Issued Date: ',IFNULL(old_license_issued_date,' '),'\n');END IF;
   IF(IFNULL(old_license_expiry_date,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Expiry Date: ',IFNULL(old_license_expiry_date,' '),'\n');END IF;

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_qualification_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$




DROP TRIGGER IF EXISTS `watch_insert_hs_hr_emp_member_detail`;$$
DROP PROCEDURE IF EXISTS `audit_insert_hs_hr_emp_member_detail`;$$

DROP TRIGGER IF EXISTS `watch_update_hs_hr_emp_member_detail`;$$
DROP PROCEDURE IF EXISTS `audit_update_hs_hr_emp_member_detail`;$$

DROP TRIGGER IF EXISTS `watch_delete_hs_hr_emp_member_detail`;$$
DROP PROCEDURE IF EXISTS `audit_delete_hs_hr_emp_member_detail`;$$


CREATE TRIGGER watch_insert_hs_hr_emp_member_detail
AFTER INSERT ON hs_hr_emp_member_detail
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = 1;
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_member_detail LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_member_detail.emp_number WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_insert_hs_hr_emp_member_detail(@orangehrm_user, orangehrm_user_name, 'ADD MEMBERSHIP DETAILS', new_version, NEW.emp_number, affected_entity_name, NEW.membship_code, NEW.ememb_subscript_ownership, NEW.ememb_subscript_amount, NEW.ememb_subs_currency, NEW.ememb_commence_date,  NEW.ememb_renewal_date);
END;$$

CREATE PROCEDURE audit_INSERT_hs_hr_emp_member_detail
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_membship_code VARCHAR(20) CHARSET UTF8,
  IN new_ememb_subscript_ownership VARCHAR(20) CHARSET UTF8,
  IN new_ememb_subscript_amount DECIMAL(15,2),
  IN new_ememb_subs_currency VARCHAR(20) CHARSET UTF8,
  IN new_ememb_commence_date DATE,
  IN new_ememb_renewal_date DATE
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

   SET action_description = CONCAT(action_description, ' ', 'New Membership Info was added (Membership: ',IFNULL((SELECT `name` FROM `ohrm_membership` WHERE `id` = new_membship_code),' '),')\n');
   IF (IFNULL(new_ememb_subscript_ownership,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Subscription Paid By: ',IFNULL(get_static_reference_value('hs_hr_emp_member_detail', 'ememb_subscript_ownership',  new_ememb_subscript_ownership),' '),'\n');END IF;
   IF (IFNULL(new_ememb_subscript_amount,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Subscription Amount: ',IFNULL(new_ememb_subscript_amount,' '),'\n');END IF;
      
   IF (IFNULL(new_ememb_subs_currency,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Currency: ',IFNULL((SELECT `currency_name` FROM `hs_hr_currency_type` WHERE `currency_id` = new_ememb_subs_currency),' '),'\n');END IF;
   IF (IFNULL(new_ememb_commence_date,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Subscription Commence Date: ',IFNULL(new_ememb_commence_date,' '),'\n');END IF;
   IF (IFNULL(new_ememb_renewal_date,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Subscription Renewal Date: ',IFNULL(new_ememb_renewal_date,' '),'\n');END IF;
  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_emp_member_detail_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 


CREATE TRIGGER watch_update_hs_hr_emp_member_detail
AFTER UPDATE ON hs_hr_emp_member_detail
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_emp_member_detail_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_member_detail LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_member_detail.emp_number WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_update_hs_hr_emp_member_detail(@orangehrm_user, orangehrm_user_name, 'UPDATE MEMBERSHIP DETAILS', new_version, OLD.emp_number, affected_entity_name, NEW.membship_code, OLD.membship_code, NEW.ememb_subscript_ownership, OLD.ememb_subscript_ownership, NEW.ememb_subscript_amount, OLD.ememb_subscript_amount, NEW.ememb_subs_currency, OLD.ememb_subs_currency, NEW.ememb_commence_date, OLD.ememb_commence_date, NEW.ememb_renewal_date, OLD.ememb_renewal_date);
END;$$

CREATE PROCEDURE audit_UPDATE_hs_hr_emp_member_detail
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_membship_code INT(6),
  IN old_membship_code INT(6),
  IN new_ememb_subscript_ownership VARCHAR(20) CHARSET UTF8,
  IN old_ememb_subscript_ownership VARCHAR(20) CHARSET UTF8,
  IN new_ememb_subscript_amount DECIMAL(15,2),
  IN old_ememb_subscript_amount DECIMAL(15,2),
  IN new_ememb_subs_currency VARCHAR(20) CHARSET UTF8,
  IN old_ememb_subs_currency VARCHAR(20) CHARSET UTF8,
  IN new_ememb_commence_date DATE,
  IN old_ememb_commence_date DATE,
  IN new_ememb_renewal_date DATE,
  IN old_ememb_renewal_date DATE
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description
  SET action_description = CONCAT(action_description, ' ', '(Membership: ',IFNULL((SELECT `name` FROM `ohrm_membership` WHERE `id` = old_membship_code),' '),')\n');
  IF (new_membship_code != old_membship_code) THEN SET action_description = CONCAT(action_description, ' ', 'Membership was changed from ', IFNULL((SELECT `name` FROM `ohrm_membership` WHERE `id` = old_membship_code),' '), ' to ', IFNULL((SELECT `name` FROM `ohrm_membership` WHERE `id` = new_membship_code),' '), '\n');END IF;
  IF (IFNULL(old_ememb_subscript_ownership,'') != IFNULL(new_ememb_subscript_ownership,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Subscription Paid By was changed from ', IFNULL(old_ememb_subscript_ownership, ' '), ' to ', IFNULL(new_ememb_subscript_ownership, ' '), '\n');END IF;
  IF (IFNULL(old_ememb_subscript_amount,'') != IFNULL(new_ememb_subscript_amount,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Subscription Amount was changed from ', IFNULL(old_ememb_subscript_amount, ' '), ' to ', IFNULL(new_ememb_subscript_amount, ' '), '\n');END IF;
  IF (IFNULL(old_ememb_subs_currency,'') != IFNULL(new_ememb_subs_currency,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Currency was changed from ', IFNULL((SELECT `currency_name` FROM `hs_hr_currency_type` WHERE `currency_id` = old_ememb_subs_currency),' '), ' to ', IFNULL((SELECT `currency_name` FROM `hs_hr_currency_type` WHERE `currency_id` = new_ememb_subs_currency),' '), '\n');END IF;
  IF (IFNULL(old_ememb_commence_date,'') != IFNULL(new_ememb_commence_date,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Subscription Commence Date was changed from ', IFNULL(old_ememb_commence_date, ' '), ' to ', IFNULL(new_ememb_commence_date, ' '), '\n');END IF;
  IF (IFNULL(old_ememb_renewal_date,'') != IFNULL(new_ememb_renewal_date,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Subscription Renewal Date was changed from ', IFNULL(old_ememb_renewal_date, ' '), ' to ', IFNULL(new_ememb_renewal_date, ' '), '\n');END IF;

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_emp_member_detail_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 



CREATE TRIGGER watch_delete_hs_hr_emp_member_detail
BEFORE DELETE ON hs_hr_emp_member_detail
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_emp_member_detail_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_member_detail LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_member_detail.emp_number WHERE hs_hr_employee.emp_number = OLD.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_delete_hs_hr_emp_member_detail(@orangehrm_user, orangehrm_user_name, 'DELETE MEMBERSHIP DETAILS', new_version, OLD.emp_number, affected_entity_name, OLD.membship_code, OLD.ememb_subscript_ownership, OLD.ememb_subscript_amount, OLD.ememb_subs_currency, OLD.ememb_commence_date,  OLD.ememb_renewal_date);
END;$$

CREATE PROCEDURE audit_DELETE_hs_hr_emp_member_detail
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN old_membship_code VARCHAR(20) CHARSET UTF8,
  IN old_ememb_subscript_ownership VARCHAR(20) CHARSET UTF8,
  IN old_ememb_subscript_amount DECIMAL(15,2),
  IN old_ememb_subs_currency VARCHAR(20) CHARSET UTF8,
  IN old_ememb_commence_date DATE,
  IN old_ememb_renewal_date DATE
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description


   SET action_description = CONCAT(action_description, ' ', 'Membership Info was deleted (Membership: ',IFNULL((SELECT `name` FROM `ohrm_membership` WHERE `id` = old_membship_code),' '),')\n');
  IF (IFNULL(old_ememb_subscript_amount,'') != '') THEN  SET action_description = CONCAT(action_description, ' ', 'Subscription Paid By: ',IFNULL(get_static_reference_value('hs_hr_emp_member_detail', 'ememb_subscript_ownership',  old_ememb_subscript_ownership),' '),'\n');END IF;
 IF (IFNULL(old_ememb_subscript_amount,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Subscription Amount: ',IFNULL(old_ememb_subscript_amount,' '),'\n');END IF;
     
   IF (IFNULL(old_ememb_subs_currency,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Currency: ',IFNULL((SELECT `currency_name` FROM `hs_hr_currency_type` WHERE `currency_id` = old_ememb_subs_currency),' '),'\n');END IF;
   IF (IFNULL(old_ememb_commence_date,'') != '') THEN  SET action_description = CONCAT(action_description, ' ', 'Subscription Commence Date: ',IFNULL(old_ememb_commence_date,' '),'\n');END IF;
    IF (IFNULL(old_ememb_renewal_date,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Subscription Renewal Date: ',IFNULL(old_ememb_renewal_date,' '),'\n');END IF;

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_emp_member_detail_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 







DROP TRIGGER IF EXISTS `watch_insert_hs_hr_emp_us_tax`;$$
DROP PROCEDURE IF EXISTS `audit_insert_hs_hr_emp_us_tax`;$$

DROP TRIGGER IF EXISTS `watch_update_hs_hr_emp_us_tax`;$$
DROP PROCEDURE IF EXISTS `audit_update_hs_hr_emp_us_tax`;$$

DROP TRIGGER IF EXISTS `watch_delete_hs_hr_emp_us_tax`;$$
DROP PROCEDURE IF EXISTS `audit_delete_hs_hr_emp_us_tax`;$$



CREATE TRIGGER watch_insert_hs_hr_emp_us_tax
AFTER INSERT ON hs_hr_emp_us_tax
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = 1;
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_us_tax LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_us_tax.emp_number WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_insert_hs_hr_emp_us_tax(@orangehrm_user, orangehrm_user_name, 'ADD TAX EXEMPTION DETAILS', new_version, NEW.emp_number, affected_entity_name, NEW.tax_federal_status, NEW.tax_federal_exceptions, NEW.tax_state, NEW.tax_state_status, NEW.tax_state_exceptions, NEW.tax_unemp_state,NEW.tax_work_state);

END;$$

CREATE PROCEDURE audit_INSERT_hs_hr_emp_us_tax
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_tax_federal_status VARCHAR(13) CHARSET UTF8, 
  IN new_tax_federal_exceptions INT(2), 
  IN new_tax_state VARCHAR(13) CHARSET UTF8,  
  IN new_tax_state_status VARCHAR(13) CHARSET UTF8,  
  IN new_tax_state_exceptions INT(2), 
  IN new_tax_unemp_state VARCHAR(13) CHARSET UTF8, 
  IN new_tax_work_state VARCHAR(13) CHARSET UTF8
 
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;

  SET action_description = '';
  -- Start generating the action description

   SET action_description = CONCAT(action_description, ' ', 'Tax Exemptions info was added\n');
   IF (IFNULL(new_tax_federal_status,'') != '' ) THEN SET action_description = CONCAT(action_description, ' ', 'Federal Income Tax Status :', IFNULL(get_static_reference_value('hs_hr_emp_us_tax', 'tax_status', new_tax_federal_status), ' '),'\n');END IF;
   IF (IFNULL(new_tax_federal_exceptions,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Federal Income Tax Exemptions :', IFNULL(new_tax_federal_exceptions, ' '),'\n');END IF;
   IF (IFNULL(new_tax_state,'') != '' ) THEN SET action_description = CONCAT(action_description, ' ', 'State Income Tax State :', IFNULL((SELECT `province_name` FROM `hs_hr_province` WHERE `province_code` = new_tax_state LIMIT 1), ' '),'\n');END IF; 
   IF (IFNULL(new_tax_state_status,'') != '' ) THEN SET action_description = CONCAT(action_description, ' ', 'State Income Tax Status :', IFNULL(get_static_reference_value('hs_hr_emp_us_tax', 'tax_status', new_tax_state_status), ' '),'\n');END IF; 
   IF (IFNULL(new_tax_state_exceptions,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'State Income Tax Exemptions :', IFNULL(new_tax_state_exceptions, ' '),'\n');END IF;
   IF (IFNULL(new_tax_unemp_state,'') != ''  ) THEN SET action_description = CONCAT(action_description, ' ', 'Unemployment State :', IFNULL((SELECT `province_name` FROM `hs_hr_province` WHERE `province_code` = new_tax_unemp_state LIMIT 1), ' '),'\n');END IF; 
   IF (IFNULL(new_tax_work_state,'') != '' ) THEN SET action_description = CONCAT(action_description, ' ', 'Work State :', IFNULL((SELECT `province_name` FROM `hs_hr_province` WHERE `province_code` = new_tax_work_state LIMIT 1), ' '),'\n');END IF; 

-- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_us_tax_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 


CREATE TRIGGER watch_update_hs_hr_emp_us_tax
AFTER UPDATE ON hs_hr_emp_us_tax
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_us_tax_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_us_tax LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_us_tax.emp_number WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_update_hs_hr_emp_us_tax(@orangehrm_user, orangehrm_user_name, 'UPDATE TAX EXEMPTION DETAILS', new_version, OLD.emp_number, affected_entity_name, NEW.tax_federal_status, OLD.tax_federal_status, NEW.tax_federal_exceptions, OLD.tax_federal_exceptions, NEW.tax_state, OLD.tax_state, NEW.tax_state_status, OLD.tax_state_status, NEW.tax_state_exceptions, OLD.tax_state_exceptions, NEW.tax_unemp_state, OLD.tax_unemp_state, NEW.tax_work_state, OLD.tax_work_state);
END;$$

CREATE PROCEDURE audit_UPDATE_hs_hr_emp_us_tax
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_tax_federal_status VARCHAR(13) CHARSET UTF8,
  IN old_tax_federal_status VARCHAR(13) CHARSET UTF8,
  IN new_tax_federal_exceptions INT(2),
  IN old_tax_federal_exceptions INT(2),
  IN new_tax_state VARCHAR(13) CHARSET UTF8,
  IN old_tax_state VARCHAR(13) CHARSET UTF8,
  IN new_tax_state_status VARCHAR(13) CHARSET UTF8,
  IN old_tax_state_status VARCHAR(13) CHARSET UTF8,
  IN new_tax_state_exceptions INT(2),
  IN old_tax_state_exceptions INT(2),
  IN new_tax_unemp_state VARCHAR(13) CHARSET UTF8,
  IN old_tax_unemp_state VARCHAR(13) CHARSET UTF8,
  IN new_tax_work_state VARCHAR(13) CHARSET UTF8,
  IN old_tax_work_state VARCHAR(13) CHARSET UTF8
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

  IF (old_tax_federal_status != new_tax_federal_status) THEN SET action_description = CONCAT(action_description, ' ', 'Federal Income Tax Status was changed from ',IFNULL(get_static_reference_value('hs_hr_emp_us_tax', 'tax_status', old_tax_federal_status),' '), ' to ', IFNULL(get_static_reference_value('hs_hr_emp_us_tax', 'tax_status', new_tax_federal_status),' '), '\n');END IF;
  IF (old_tax_federal_exceptions != new_tax_federal_exceptions) THEN SET action_description = CONCAT(action_description, ' ', 'Federal Income Tax Exemptions was changed from ',     IFNULL(old_tax_federal_exceptions, ' '), ' to ', IFNULL(new_tax_federal_exceptions, ' '), '\n');END IF;
  IF (old_tax_state != new_tax_state) THEN SET action_description = CONCAT(action_description, ' ', 'State Income Tax State was changed from ', IFNULL((SELECT `province_name` FROM `hs_hr_province` WHERE `province_code` = old_tax_state LIMIT 1), ' '), ' to ',  IFNULL((SELECT `province_name` FROM `hs_hr_province` WHERE `province_code` = new_tax_state LIMIT 1), ' '), '\n');END IF;
  IF (old_tax_state_status != new_tax_state_status) THEN SET action_description = CONCAT(action_description, ' ', 'State Income Tax Status was changed from ',  IFNULL(get_static_reference_value('hs_hr_emp_us_tax', 'tax_status', old_tax_state_status),' '), ' to ',  IFNULL(get_static_reference_value('hs_hr_emp_us_tax', 'tax_status', new_tax_state_status ),' '), '\n');END IF;
  IF (old_tax_state_exceptions != new_tax_state_exceptions) THEN SET action_description = CONCAT(action_description, ' ', 'State Income Tax Exemptions was changed from ', IFNULL(old_tax_state_exceptions, ' '), ' to ', IFNULL(new_tax_state_exceptions, ' '), '\n');END IF;
  IF (old_tax_unemp_state != new_tax_unemp_state) THEN SET action_description = CONCAT(action_description, ' ', 'Unemployment State was changed from ',IFNULL((SELECT `province_name` FROM `hs_hr_province` WHERE `province_code` = old_tax_unemp_state LIMIT 1),' '), ' to ', IFNULL( (SELECT `province_name` FROM `hs_hr_province` WHERE `province_code` = new_tax_unemp_state LIMIT 1), ' '), '\n');END IF;
  IF (old_tax_work_state != new_tax_work_state) THEN SET action_description = CONCAT(action_description, ' ', 'Work State was changed from ', IFNULL((SELECT `province_name` FROM `hs_hr_province` WHERE `province_code` = old_tax_work_state LIMIT 1), ' '), ' to ', IFNULL( (SELECT `province_name` FROM `hs_hr_province` WHERE `province_code` = new_tax_work_state LIMIT 1), ' '), '\n');END IF;

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_us_tax_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$ 


CREATE TRIGGER watch_delete_hs_hr_emp_us_tax
BEFORE DELETE ON hs_hr_emp_us_tax
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_us_tax_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(new_version, 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_emp_us_tax LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = hs_hr_emp_us_tax.emp_number WHERE hs_hr_employee.emp_number = OLD.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_delete_hs_hr_emp_us_tax(@orangehrm_user, orangehrm_user_name, 'DELETE TAX EXEMPTION DETAILS', new_version, OLD.emp_number, affected_entity_name, OLD.tax_federal_status, OLD.tax_federal_exceptions, OLD.tax_state, OLD.tax_state_status, OLD.tax_state_exceptions, OLD.tax_unemp_state,OLD.tax_work_state);


END;$$

CREATE PROCEDURE audit_DELETE_hs_hr_emp_us_tax
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(7),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,  
  IN old_tax_federal_status VARCHAR(13) CHARSET UTF8, 
  IN old_tax_federal_exceptions INT(2), 
  IN old_tax_state VARCHAR(13) CHARSET UTF8,  
  IN old_tax_state_status VARCHAR(13) CHARSET UTF8,  
  IN old_tax_state_exceptions INT(2), 
  IN old_tax_unemp_state VARCHAR(13) CHARSET UTF8, 
  IN old_tax_work_state VARCHAR(13) CHARSET UTF8
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;


  SET action_description = '';
  -- Start generating the action description

   SET action_description = CONCAT(action_description, ' ', 'Tax Exemptions was deleted\n');

  
   IF (IFNULL(old_tax_federal_status,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Federal Income Tax Status :', IFNULL(get_static_reference_value('hs_hr_emp_us_tax', 'tax_status', old_tax_federal_status), ' '),'\n');END IF;
   IF (IFNULL(old_tax_federal_exceptions,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Federal Income Tax Exemptions :', IFNULL(old_tax_federal_exceptions, ' '),'\n');END IF;
   IF (IFNULL(old_tax_state,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'State Income Tax State :', IFNULL((SELECT `province_name` FROM `hs_hr_province` WHERE `province_code` = old_tax_state LIMIT 1), ' '),'\n');END IF; 
   IF (IFNULL(old_tax_state_status,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'State Income Tax Status :', IFNULL(get_static_reference_value('hs_hr_emp_us_tax', 'tax_status', old_tax_state_status), ' '),'\n');END IF; 
   IF (IFNULL(old_tax_state_exceptions,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Federal Income Tax Exemptions :', IFNULL(old_tax_federal_exceptions, ' '),'\n');END IF;
 
   IF (IFNULL(old_tax_unemp_state,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Unemployment State :', IFNULL((SELECT `province_name` FROM `hs_hr_province` WHERE `province_code` = old_tax_unemp_state LIMIT 1), ' '),'\n');END IF; 
   IF (IFNULL(old_tax_work_state,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Work State :', IFNULL((SELECT `province_name` FROM `hs_hr_province` WHERE `province_code` = old_tax_work_state LIMIT 1), ' '),'\n');END IF; 
  

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_us_tax_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;
 
END;$$

DELIMITER ;
