

CREATE TABLE `ohrm_audittrail_pim_job_record_trail` (  
  `id` INT NOT NULL AUTO_INCREMENT,
  `action_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `action_owner_id` varchar(6),
  `action_owner_name` VARCHAR(300),
  `version_id` int(11) NOT NULL DEFAULT '0',
  `action` varchar(255) NOT NULL,
  `affected_entity_id` varchar(20) NOT NULL DEFAULT '',
  `affected_entity_name` VARCHAR(300) NOT NULL DEFAULT '',
  `action_description` VARCHAR(3000) CHARSET UTF8,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_job_record_trail_idx_app_id (app_id),
  INDEX audit_pim_job_record_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;


CREATE TABLE `ohrm_audittrail_pim_salary_record_trail` (  
  `id` INT NOT NULL AUTO_INCREMENT,
  `action_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `action_owner_id` varchar(6),
  `action_owner_name` VARCHAR(300),
  `version_id` int(11) NOT NULL DEFAULT '0',
  `action` varchar(255) NOT NULL,
  `affected_entity_id` varchar(20) NOT NULL DEFAULT '',
  `affected_entity_name` VARCHAR(300) NOT NULL DEFAULT '',
  `action_description` MEDIUMTEXT CHARSET UTF8,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_salary_record_trail_idx_app_id (app_id),
  INDEX audit_pim_salary_record_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;


CREATE TABLE `ohrm_audittrail_pim_direct_deposit_record_trail` (  
  `id` INT NOT NULL AUTO_INCREMENT,
  `action_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `action_owner_id` varchar(6),
  `action_owner_name` VARCHAR(300),
  `version_id` int(11) NOT NULL DEFAULT '0',
  `action` varchar(255) NOT NULL,
  `affected_entity_id` varchar(20) NOT NULL DEFAULT '',
  `affected_entity_name` VARCHAR(300) NOT NULL DEFAULT '',
  `action_description` VARCHAR(3000) CHARSET UTF8,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_direct_deposit_record_trail_idx_app_id (app_id),
  INDEX audit_pim_direct_deposit_record_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

UPDATE `ohrm_audittrail_section`  SET `name` = 'JobRecord'
  WHERE `id` = 2;

UPDATE `ohrm_audittrail_section`  SET `name` = 'SalaryRecord'
  WHERE `id` = 3;


INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES ('DirectDepositRecord', 'Direct Deposit', 'Employee', '1');

SET @pim_module_id := (SELECT `id` FROM `ohrm_audittrail_module` WHERE `name`='Employee Management');
SET @job_title_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='JobRecord' AND `module_id` = @pim_module_id);
SET @salary_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='SalaryRecord' AND `module_id` = @pim_module_id);


SET @job_title_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name`='job_history_details');
SET @salary_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name`='salary_history_details');

INSERT INTO `ohrm_datagroup_audittrail_section`(`audit_section_id`,`datagroup_id`,`permission`) VALUES
(@job_title_section_id,@job_title_datagroup_id,1),
(@salary_section_id,@salary_datagroup_id,1);



UPDATE `ohrm_audittrail_section_actions` SET `action` = 'UPDATE JOB RECORD', `description` = 'Update Job Record'
  WHERE `action` = 'UPDATE JOB DETAILS' LIMIT 1;

INSERT INTO `ohrm_audittrail_section_actions` VALUES (2, 'DELETE JOB HISTORY', 'Delete Job History');

INSERT INTO `ohrm_audittrail_section_actions` VALUES (2, 'UPDATE CUSTOM FIELD VALUE', 'Update Job Custom Fields');

DELETE FROM `ohrm_audittrail_section_actions`
  WHERE `action` = 'ADD SALARY DETAILS';

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES
(3, 'ADD SALARY RECORD', 'Add Salary Record');

UPDATE `ohrm_audittrail_section_actions` SET `action` = 'UPDATE SALARY RECORD', `description` = 'Update Salary Record'
  WHERE `action` = 'UPDATE SALARY DETAILS' LIMIT 1;

UPDATE `ohrm_audittrail_section_actions` SET `action` = 'DELETE SALARY HISTORY', `description` = 'Delete Salary History'
  WHERE `action` = 'DELETE SALARY DETAILS' LIMIT 1;

SET @direct_deposit_section := (SELECT id FROM ohrm_audittrail_section WHERE name = 'DirectDepositRecord');

UPDATE `ohrm_audittrail_section_actions` SET `section_id` = @direct_deposit_section
  WHERE `action` IN ('ADD DIRECT DEPOSIT DETAILS', 'UPDATE DIRECT DEPOSIT DETAILS', 'DELETE DIRECT DEPOSIT DETAILS');


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`;



