{"SPECIFIC_NAME":"InsertNewHires","ROUTINE_NAME":"InsertNewHires","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE done INT DEFAULT FALSE;\n DECLARE job_id INT;\n DECLARE emp_numbers TEXT;\n DECLARE emp_number INT;\n DECLARE cur CURSOR FOR\nSELECT id, new_hires_ids FROM ohrm_job WHERE is_template = 0 AND new_hires_ids IS NOT NULL;\nDECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;\n\nOPEN cur;\nread_loop: LOOP\n FETCH cur INTO job_id, emp_numbers;\n IF done THEN\n LEAVE read_loop;\nEND IF;\n\n SET emp_numbers = CONCAT(emp_numbers, ',');\n WHILE LENGTH(emp_numbers) > 0 DO\n SET emp_number = CAST(SUBSTRING_INDEX(emp_numbers, ',', 1) AS UNSIGNED);\nINSERT INTO ohrm_job_participant_employee (job_id, emp_number) VALUES (job_id, emp_number);\nSET emp_numbers = SUBSTRING(emp_numbers, LENGTH(emp_number) + 2);\nEND WHILE;\nEND LOOP;\nCLOSE cur;\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"archive_reference","ROUTINE_NAME":"archive_reference","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n INSERT INTO `ohrm_audittrail_reference_archive`(table_name,reference_key,record_descriptor) VALUES (subject_table, key_value, record_descriptor);\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_DELETE_hs_hr_emp_dependents","ROUTINE_NAME":"audit_DELETE_hs_hr_emp_dependents","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600);\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = CONCAT(action_description, ' ', 'Dependent was deleted (Name: ',old_ed_name,')\\n');\n SET action_description = CONCAT(action_description, ' ', 'Relationship: ',old_ed_relationship_type,'\\n');\n IF (old_ed_relationship != '') THEN SET action_description = CONCAT(action_description, ' ', 'Relationship (Other): ',old_ed_relationship,'\\n');END IF;\n IF (old_ed_date_of_birth != '') THEN SET action_description = CONCAT(action_description, ' ', 'Date of Birth: ',old_ed_date_of_birth,'\\n');END IF;\n 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;\n\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_DELETE_hs_hr_emp_emergency_contacts","ROUTINE_NAME":"audit_DELETE_hs_hr_emp_emergency_contacts","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(626) CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = CONCAT(action_description, ' ', 'Emergency Contact was deleted (Name: ',old_eec_name,')\\n');\n SET action_description = CONCAT(action_description, ' ', 'Relationship: ',old_eec_relationship,'\\n');\n SET action_description = CONCAT(action_description, ' ', 'Home Telephone:',old_eec_home_no,'\\n');\n SET action_description = CONCAT(action_description, ' ', 'Mobile:',old_eec_mobile_no,'\\n');\n SET action_description = CONCAT(action_description, ' ', 'Work Telephone:',old_eec_office_no,'\\n');\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_DELETE_hs_hr_emp_language","ROUTINE_NAME":"audit_DELETE_hs_hr_emp_language","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = CONCAT(action_description, ' ', 'Language Info was deleted (Language: ',IFNULL((SELECT `name` FROM `ohrm_language` WHERE `id` = old_lang_id),' '),')\\n');\n\n SET action_description = CONCAT(action_description, ' ', 'Fluency: ',IFNULL(get_static_reference_value('hs_hr_emp_language', 'fluency', old_fluency),' '),'\\n');\n SET action_description = CONCAT(action_description, ' ', 'Competency: ',IFNULL(get_static_reference_value('hs_hr_emp_language', 'competency', old_competency),' '),'\\n');\n SET action_description = CONCAT(action_description, ' ', 'Comments: ',IFNULL(old_comments,' '),'\\n');\n\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_DELETE_hs_hr_emp_member_detail","ROUTINE_NAME":"audit_DELETE_hs_hr_emp_member_detail","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n\n SET action_description = CONCAT(action_description, ' ', 'Membership Info was deleted (Membership: ',IFNULL((SELECT `name` FROM `ohrm_membership` WHERE `id` = old_membship_code),' '),')\\n');\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;\n IF (IFNULL(old_ememb_subscript_amount,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Subscription Amount: ',IFNULL(old_ememb_subscript_amount,' '),'\\n');END IF;\n \n 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;\n IF (IFNULL(old_ememb_commence_date,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Subscription Commence Date: ',IFNULL(old_ememb_commence_date,' '),'\\n');END IF;\n IF (IFNULL(old_ememb_renewal_date,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Subscription Renewal Date: ',IFNULL(old_ememb_renewal_date,' '),'\\n');END IF;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_DELETE_hs_hr_emp_passport","ROUTINE_NAME":"audit_DELETE_hs_hr_emp_passport","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n 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;\n 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; \n IF (old_ep_passportissueddate != '' ) THEN SET action_description = CONCAT(action_description, ' ', 'Issued Date: ', old_ep_passportissueddate,'\\n');END IF;\n IF (old_ep_passportexpiredate != '' ) THEN SET action_description = CONCAT(action_description, ' ', 'Expiry Date: ', old_ep_passportexpiredate,'\\n');END IF;\n IF (old_ep_i9_status != '' ) THEN SET action_description = CONCAT(action_description, ' ', 'Eligible Status: ', old_ep_i9_status,'\\n');END IF;\n 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;\n IF (old_ep_i9_review_date != '' ) THEN SET action_description = CONCAT(action_description, ' ', 'Eligible Review Date: ', old_ep_i9_review_date,'\\n');END IF;\n IF (old_ep_comments != '' ) THEN SET action_description = CONCAT(action_description, ' ', 'Comments: ', old_ep_comments,'\\n');END IF;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_DELETE_hs_hr_emp_skill","ROUTINE_NAME":"audit_DELETE_hs_hr_emp_skill","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n 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;\n 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;\n IF (IFNULL(old_comments,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Comments : ',IFNULL(old_comments,''),'\\n');END IF;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_DELETE_hs_hr_emp_us_tax","ROUTINE_NAME":"audit_DELETE_hs_hr_emp_us_tax","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = CONCAT(action_description, ' ', 'Tax Exemptions was deleted\\n');\n\n \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;\n 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;\n 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; \n 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; \n 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;\n \n 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; \n 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; \n \n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_DELETE_hs_hr_employee","ROUTINE_NAME":"audit_DELETE_hs_hr_employee","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n DECLARE action_description_contact VARCHAR(1000) CHARSET UTF8;\n\n-- for emergency contacts\n DECLARE emp_number INT(7);\n DECLARE eec_seqno DECIMAL(2,0);\n DECLARE eec_name VARCHAR(100) CHARSET UTF8;\n DECLARE eec_relationship VARCHAR(100) CHARSET UTF8;\n DECLARE eec_home_no VARCHAR(100) CHARSET UTF8;\n DECLARE eec_mobile_no VARCHAR(100) CHARSET UTF8;\n DECLARE eec_office_no VARCHAR(100) CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n SET action_description_contact = '';\n \n\n -- Start generating the action description\n\n 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;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_DELETE_ohrm_emp_education","ROUTINE_NAME":"audit_DELETE_ohrm_emp_education","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n\n SET action_description = CONCAT(action_description, ' ', 'Education Info was deleted (Level: ',IFNULL((SELECT `name` FROM `ohrm_education` WHERE `id` = IFNULL(old_education_id, '')),''),')\\n');\n IF (IFNULL(old_institute, '') != '') THEN SET action_description = CONCAT(action_description, ' ','Institute: ', IFNULL(old_institute, ' '), '\\n');END IF;\nIF (IFNULL(old_major, '') != '') THEN SET action_description = CONCAT(action_description, ' ','Major\/Specialization: ', IFNULL(old_major, ' '), '\\n');END IF;\nIF (IFNULL(old_year, '') != '') THEN SET action_description = CONCAT(action_description, ' ','Year: ', IFNULL(old_year, ' '), '\\n');END IF;\nIF (IFNULL(old_score, '') != '') THEN SET action_description = CONCAT(action_description, ' ','GPA\/Score: ', IFNULL(old_score, ' '), '\\n');END IF;\nIF (IFNULL(old_start_date, '') != '') THEN SET action_description = CONCAT(action_description, ' ','Start Date: ', IFNULL(old_start_date, ' '), '\\n');END IF;\nIF (IFNULL(old_end_date, '') != '') THEN SET action_description = CONCAT(action_description, ' ','End Date: ', IFNULL(old_end_date, ' '), '');END IF;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_DELETE_ohrm_emp_license","ROUTINE_NAME":"audit_DELETE_ohrm_emp_license","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = CONCAT(action_description, ' ', 'License Info was deleted (License: ',IFNULL((SELECT `name` FROM `ohrm_license` WHERE `id` = old_license_id),' '),')\\n');\n IF(IFNULL(old_license_no,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'License Number: ',IFNULL(old_license_no,' '),'\\n');END IF;\n IF(IFNULL(old_license_issued_date,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Issued Date: ',IFNULL(old_license_issued_date,' '),'\\n');END IF;\n IF(IFNULL(old_license_expiry_date,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Expiry Date: ',IFNULL(old_license_expiry_date,' '),'\\n');END IF;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_INSERT_hs_hr_emp_dependents","ROUTINE_NAME":"audit_INSERT_hs_hr_emp_dependents","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = IFNULL( CONCAT(action_description, ' ', 'New Dependent was added (Name: ',new_ed_name,')\\n'),'');\n SET action_description = IFNULL( CONCAT(action_description, ' ', 'Relationship: ',new_ed_relationship_type,'\\n'),'');\n IF (new_ed_relationship != '') THEN SET action_description = IFNULL( CONCAT(action_description, ' ', 'Relationship (Other): ',new_ed_relationship,'\\n'),''); END IF;\n IF (new_ed_date_of_birth != '') THEN SET action_description = CONCAT(action_description, ' ', 'Date of Birth: ',new_ed_date_of_birth,'\\n');END IF;\n 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;\n\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_INSERT_hs_hr_emp_emergency_contacts","ROUTINE_NAME":"audit_INSERT_hs_hr_emp_emergency_contacts","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n DECLARE action_description VARCHAR(626) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = IFNULL( CONCAT(action_description, ' ', 'New Emergency Contact was added (Name: ',new_eec_name,')\\n'),'');\n IF (new_eec_relationship != '') THEN SET action_description = IFNULL( CONCAT(action_description, ' ', 'Relationship: ',new_eec_relationship,'\\n'),'');END IF;\n IF (new_eec_home_no != '') THEN SET action_description = IFNULL( CONCAT(action_description, ' ', 'Home Telephone:',new_eec_home_no,'\\n'),'');END IF;\n IF (new_eec_mobile_no != '') THEN SET action_description = IFNULL( CONCAT(action_description, ' ', 'Mobile:',new_eec_mobile_no,'\\n'),'');END IF;\n IF (new_eec_office_no != '') THEN SET action_description = IFNULL( CONCAT(action_description, ' ', 'Work Telephone:',new_eec_office_no,'\\n'),'');END IF;\n\n\n \n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_INSERT_hs_hr_emp_language","ROUTINE_NAME":"audit_INSERT_hs_hr_emp_language","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n--\n SET action_description = CONCAT(action_description, ' ', 'New Language Info was added (Language: ',IFNULL((SELECT `name` FROM `ohrm_language` WHERE `id` = new_lang_id),' '),')\\n');\n SET action_description = CONCAT(action_description, ' ', 'Fluency: ',IFNULL(get_static_reference_value('hs_hr_emp_language', 'fluency', new_fluency), ' '),'\\n');\n SET action_description = CONCAT(action_description, ' ', 'Competency: ',IFNULL(get_static_reference_value('hs_hr_emp_language', 'competency', new_competency), ' '),'\\n');\n IF (IFNULL(new_comments,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Comments: ',IFNULL(new_comments, ' '),'\\n');END IF;\n--\n-- -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_INSERT_hs_hr_emp_member_detail","ROUTINE_NAME":"audit_INSERT_hs_hr_emp_member_detail","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = CONCAT(action_description, ' ', 'New Membership Info was added (Membership: ',IFNULL((SELECT `name` FROM `ohrm_membership` WHERE `id` = new_membship_code),' '),')\\n');\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;\n IF (IFNULL(new_ememb_subscript_amount,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Subscription Amount: ',IFNULL(new_ememb_subscript_amount,' '),'\\n');END IF;\n \n 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;\n IF (IFNULL(new_ememb_commence_date,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Subscription Commence Date: ',IFNULL(new_ememb_commence_date,' '),'\\n');END IF;\n IF (IFNULL(new_ememb_renewal_date,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Subscription Renewal Date: ',IFNULL(new_ememb_renewal_date,' '),'\\n');END IF;\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_INSERT_hs_hr_emp_passport","ROUTINE_NAME":"audit_INSERT_hs_hr_emp_passport","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n\n SET action_description = '';\n -- Start generating the action description\n\n\n 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;\n 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;\n 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;\n 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;\n IF (new_ep_i9_status != '') THEN SET action_description = IFNULL(CONCAT(action_description, ' ', 'Eligible Status: ', new_ep_i9_status,'\\n'),'');END IF;\n 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;\n 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; \n IF (new_ep_comments != '') THEN SET action_description = IFNULL(CONCAT(action_description, ' ', 'Comment:', new_ep_comments, '\\n'),'');END IF;\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_INSERT_hs_hr_emp_skill","ROUTINE_NAME":"audit_INSERT_hs_hr_emp_skill","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n 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;\n 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;\n IF (IFNULL(new_comments,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Comments : ',IFNULL(new_comments,''),'\\n');END IF;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_INSERT_hs_hr_emp_us_tax","ROUTINE_NAME":"audit_INSERT_hs_hr_emp_us_tax","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = CONCAT(action_description, ' ', 'Tax Exemptions info was added\\n');\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;\n 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;\n 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; \n 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; \n 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;\n 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; \n 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; \n\n-- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_INSERT_hs_hr_employee","ROUTINE_NAME":"audit_INSERT_hs_hr_employee","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = CONCAT(action_description, ' ', 'New Employee was added (Name: ', new_emp_firstname,' ',emp_middle_name,' ', new_emp_lastname,', Location: ', employee_added_location, ', Joined Date: ', employee_joined_date, ')');\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_INSERT_ohrm_emp_education","ROUTINE_NAME":"audit_INSERT_ohrm_emp_education","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n\n SET action_description = '';\n -- Start generating the action description\n\n 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');\n IF (IFNULL(new_institute, '') != '') THEN SET action_description = CONCAT(action_description, ' ','Institute: ', IFNULL(new_institute, ' '), '\\n');END IF;\nIF (IFNULL(new_major, '') != '') THEN SET action_description = CONCAT(action_description, ' ','Major\/Specialization: ', IFNULL(new_major, ' '), '\\n');END IF;\nIF (IFNULL(new_year, '') != '') THEN SET action_description = CONCAT(action_description, ' ','Year: ', IFNULL(new_year, ' '), '\\n');END IF;\nIF (IFNULL(new_score, '') != '') THEN SET action_description = CONCAT(action_description, ' ','GPA\/Score: ', IFNULL(new_score, ' '), '\\n');END IF;\nIF (IFNULL(new_start_date, '') != '') THEN SET action_description = CONCAT(action_description, ' ','Start Date: ', IFNULL(new_start_date, ' '), '\\n');END IF;\nIF (IFNULL(new_end_date, '') != '') THEN SET action_description = CONCAT(action_description, ' ','End Date: ', IFNULL(new_end_date, ' '), '\\n');END IF;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_INSERT_ohrm_emp_license","ROUTINE_NAME":"audit_INSERT_ohrm_emp_license","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = CONCAT(action_description, ' ', 'New License Info was added (License: ',IFNULL((SELECT `name` FROM `ohrm_license` WHERE `id` = new_license_id),' '),')\\n');\n IF(IFNULL(new_license_no,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'License Number: ',IFNULL(new_license_no,' '),'\\n');END IF;\n IF(IFNULL(new_license_issued_date,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Issued Date: ',IFNULL(new_license_issued_date,' '),'\\n');END IF;\n IF(IFNULL(new_license_expiry_date,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Expiry Date: ',IFNULL(new_license_expiry_date,' '),'\\n');END IF;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_UPDATE_hs_hr_emp_dependents","ROUTINE_NAME":"audit_UPDATE_hs_hr_emp_dependents","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = IFNULL( CONCAT(action_description, ' ', '(Name: ',old_ed_name,')\\n'),'');\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;\n 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;\n 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;\n 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;\n 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;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_UPDATE_hs_hr_emp_emergency_contacts","ROUTINE_NAME":"audit_UPDATE_hs_hr_emp_emergency_contacts","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n DECLARE action_description VARCHAR(626) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n\n\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = IFNULL( CONCAT(action_description, ' ', '(Name: ',old_eec_name,')\\n'),'');\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;\n 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;\n 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;\n 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;\n 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;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_UPDATE_hs_hr_emp_language","ROUTINE_NAME":"audit_UPDATE_hs_hr_emp_language","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n 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');\n \n\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;\n 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;\n 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;\n 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;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_UPDATE_hs_hr_emp_member_detail","ROUTINE_NAME":"audit_UPDATE_hs_hr_emp_member_detail","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n SET action_description = CONCAT(action_description, ' ', '(Membership: ',IFNULL((SELECT `name` FROM `ohrm_membership` WHERE `id` = old_membship_code),' '),')\\n');\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;\n 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;\n 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;\n 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;\n 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;\n 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;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_UPDATE_hs_hr_emp_passport","ROUTINE_NAME":"audit_UPDATE_hs_hr_emp_passport","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = CONCAT(action_description, ' ', 'Number was changed from ', IFNULL(old_ep_passport_num, ' '), ' to ', IFNULL(new_ep_passport_num, ''), '\\n');\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;\n 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;\n 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;\n 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;\n 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;\n 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;\n 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;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_UPDATE_hs_hr_emp_skill","ROUTINE_NAME":"audit_UPDATE_hs_hr_emp_skill","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n SET action_description = CONCAT(action_description, ' ', '(Skill: ',IFNULL((SELECT `name` FROM `ohrm_skill` WHERE `id` = old_skill_id),''),')\\n');\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;\n 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;\n 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;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_UPDATE_hs_hr_emp_us_tax","ROUTINE_NAME":"audit_UPDATE_hs_hr_emp_us_tax","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n 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;\n 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;\n 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;\n 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;\n 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;\n 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;\n 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;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_UPDATE_hs_hr_employee","ROUTINE_NAME":"audit_UPDATE_hs_hr_employee","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description TEXT CHARSET UTF8;\n DECLARE action_description_contact TEXT CHARSET UTF8;\n\n DECLARE action_description_terminate TEXT CHARSET UTF8;\n DECLARE action_description_activate TEXT CHARSET UTF8;\n DECLARE performer_id INT;\n DECLARE performer_name VARCHAR(300 ) CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n\n SET action_description = '';\n SET action_description_contact = '';\n SET action_description_terminate = '';\n SET action_description_activate = '';\n SET @ssn_field_name = (select IFNULL((select value from ohrm_config where property like 'pim_ssn_field_label'), 'SSN Number'));\n SET @new_eeo_race_ent_str = (select `name` FROM ohrm_eeo_race_ethnicity where `id`= new_eeo_race_ent);\n SET @old_eeo_race_ent_str = (select `name` FROM ohrm_eeo_race_ethnicity where `id`= old_eeo_race_ent);\n -- Start generating the action description\n\n IF((new_termination_id IS NULL) && (old_termination_id IS NOT NULL)) THEN\n SET performer_id = (SELECT `reactivation_performed_user_id` FROM ohrm_emp_termination WHERE id = old_termination_id);\n SET performer_id = IFNULL(performer_id, action_owner_id);\n SET performer_name = (SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname, IF(hs_hr_employee.termination_id IS NULL, '', ' (Past Employee)')),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 = performer_id );\n SET performer_name = IFNULL(performer_name, action_owner_name);\n SET action_description_activate = CONCAT('Employee Activated',\n '\\n Date : ',\n IFNULL((SELECT `reactivation_date` FROM ohrm_emp_termination WHERE id = old_termination_id), ' '),\n '\\n Time : ',\n IFNULL((SELECT `reactivation_time` FROM ohrm_emp_termination WHERE id = old_termination_id), ' '),\n '\\n Time Zone : ',\n IFNULL((SELECT IF(`reactivation_time_zone_name` IS NULL, NULL, CONCAT('(', `reactivation_time_zone_prefix`,') ', IFNULL(z.label, reactivation_time_zone_name))) FROM ohrm_emp_termination t LEFT JOIN ohrm_standard_time_zone z ON z.timezone_key = t.reactivation_time_zone_name WHERE t. id = old_termination_id), ' '));\n END IF;\n 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;\n 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;\n 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;\n 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;\n 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;\n 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;\n 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;\n 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;\n IF ((IFNULL(old_emp_gender, '') != IFNULL(new_emp_gender, ''))) THEN SET action_description = CONCAT(action_description, ' ', 'Gender was changed from ', IFNULL((SELECT `name` FROM `ohrm_gender` WHERE `id` = old_emp_gender), ' '), ' to ', IFNULL((SELECT `name` FROM `ohrm_gender` WHERE `id` = new_emp_gender), ' '), '\\n');END IF;\n 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;\n 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;\n 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;\n\nIF (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;\nIF (IFNULL(old_emp_sin_num, '') != IFNULL(new_emp_sin_num, '')) THEN SET action_description = CONCAT(action_description, ' ', '\\tSIN Number was changed from ', IFNULL(old_emp_sin_num, ' '), ' to ', IFNULL(new_emp_sin_num, ' '), '\\n');END IF;\nIF (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;\nIF (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;\n\nIF (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;\n\n\n\nIF (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;\nIF (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;\nIF (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;\nIF (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;\nIF (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;\nIF (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;\nIF (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;\nIF (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;\nIF (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;\nIF (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;\nIF (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;\n\n-- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n\n IF (action_description_contact != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n 'UPDATE CONTACT DETAILS',\n affected_entity_id,\n affected_entity_name,\n action_description_contact,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n\n IF (action_description_activate != '') THEN\n\n\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n performer_id,\n performer_name,\n new_version,\n 'UPDATE ACTIVATION DETAILS',\n affected_entity_id,\n affected_entity_name,\n action_description_activate,\n app_id,\n app_name,\n screen_name\n );\n\n END IF;\n\n\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_UPDATE_ohrm_emp_education","ROUTINE_NAME":"audit_UPDATE_ohrm_emp_education","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = CONCAT(action_description, ' ', '(Level: ',IFNULL((SELECT `name` FROM `ohrm_education` WHERE `id` = IFNULL(old_education_id, '')),''),')\\n');\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;\n 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;\n 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;\n 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;\n 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;\n 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;\n 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;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_UPDATE_ohrm_emp_license","ROUTINE_NAME":"audit_UPDATE_ohrm_emp_license","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = CONCAT(action_description, ' ', '(License: ',IFNULL((SELECT `name` FROM `ohrm_license` WHERE `id` = old_license_id),' '),')\\n');\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;\n 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;\n 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;\n 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;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_delete_hs_hr_emp_reportto","ROUTINE_NAME":"audit_delete_hs_hr_emp_reportto","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n 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) ,'');\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_delete_hs_hr_emp_work_experience","ROUTINE_NAME":"audit_delete_hs_hr_emp_work_experience","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = CONCAT(action_description, ' ', 'Work experience was deleted (', old_eexp_jobtit, ' at ', old_eexp_employer, ')');\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_delete_ohrm_hiring_managers","ROUTINE_NAME":"audit_delete_ohrm_hiring_managers","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description TEXT CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n SET action_description = 'Hiring Manager was Deleted for Job Vacancy\\n';\n IF(IFNULL(affected_entity_name,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Vacancy Name: ',IFNULL(affected_entity_name,' '),'\\n');END IF;\n IF(IFNULL(old_employee_id,'') != '') THEN\n SET action_description = CONCAT(action_description, ' ', 'Hiring Manager Name : ',IFNULL((SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_employee WHERE hs_hr_employee.emp_number = old_employee_id),' '),'\\n');\n END IF;\n -- End generating the action description\n\n IF (action_description != '') THEN\n INSERT INTO `ohrm_audittrail_recruitment_job_vacancy_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n 'CHANGE JOB VACANCY',\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_delete_ohrm_hiring_managers_for_vacancy_template","ROUTINE_NAME":"audit_delete_ohrm_hiring_managers_for_vacancy_template","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description TEXT CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n SET action_description = 'Hiring Manager was Deleted for Job Vacancy Template\\n';\n IF(IFNULL(affected_entity_name,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Template Name: ',IFNULL(affected_entity_name,' '),'\\n');END IF;\n IF(IFNULL(old_employee_id,'') != '') THEN\n SET action_description = CONCAT(action_description, ' ', 'Hiring Manager Name : ',IFNULL((SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_employee WHERE hs_hr_employee.emp_number = old_employee_id),' '),'\\n');\n END IF;\n -- End generating the action description\n\n IF (action_description != '') THEN\n INSERT INTO `ohrm_audittrail_recruitment_job_vacancy_template_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n 'UPDATE VACANCY TEMPLATE',\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n END","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_delete_ohrm_job_vacancy","ROUTINE_NAME":"audit_delete_ohrm_job_vacancy","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description TEXT CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = CONCAT(action_description, ' ', 'Job Vacancy was Deleted\\n');\n IF(IFNULL(old_job_title_code,'') != '') THEN\n SET action_description = CONCAT(action_description, ' ', 'Job Title: ',IFNULL((SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = old_job_title_code),' '),'\\n');\n END IF;\n IF(IFNULL(old_name,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Vacancy Name: ',IFNULL(old_name,' '),'\\n');END IF;\n IF(IFNULL(old_no_of_positions,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Number of Positions: ',IFNULL(old_no_of_positions,' '),'\\n');END IF;\n IF(IFNULL(old_location_id,'') != '') THEN\n SET action_description = CONCAT(action_description, ' ', 'Location: ',IFNULL((SELECT `name` FROM `ohrm_location` WHERE `id` = old_location_id),' '),'\\n');\n END IF;\n IF(IFNULL(old_sub_unit_id,'') != '') THEN\n SET action_description = CONCAT(action_description, ' ', 'Sub Unit: ',IFNULL((SELECT `name` FROM `ohrm_subunit` WHERE `id` = old_sub_unit_id),' '),'\\n');\n END IF;\n IF (IFNULL(old_status,'') != '') THEN\n SET @old_status_text = (select name from ohrm_job_vacancy_status where status = old_status);\n SET action_description = CONCAT(action_description, ' ', 'Status: ', IFNULL(@old_status_text, ' '), '\\n');\n END IF;\n SET @old_request_consent_text = (SELECT IF(IFNULL(old_request_consent,0)=1,'Enabled','Disabled'));\n SET action_description = CONCAT(action_description, ' ', 'Request Consent was: ', IFNULL(@old_request_consent_text, ' '), '\\n');\n SET @old_is_resume_required_text = (SELECT IF(IFNULL(old_is_resume_required,0)=1,'Enabled','Disabled'));\n SET action_description = CONCAT(action_description, ' ', 'Resume Required Option was: ', IFNULL(@old_is_resume_required_text, ' '), '\\n');\n SET @old_include_archived_candidate_on_dynamic_rule_filter_text = (SELECT IF(IFNULL(old_include_archived_candidate_on_dynamic_rule_filter,0)=1,'Enabled','Disabled'));\n SET action_description = CONCAT(action_description, ' ', 'Include Archived Candidate Option for the Best Match Rules was: ', IFNULL(@old_include_archived_candidate_on_dynamic_rule_filter_text, ' '), '\\n');\n\n IF(IFNULL(old_description,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Description: ',IFNULL(old_description,' '),'\\n');END IF;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n INSERT INTO `ohrm_audittrail_recruitment_job_vacancy_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_delete_ohrm_job_vacancy_template_from_job_vacancy","ROUTINE_NAME":"audit_delete_ohrm_job_vacancy_template_from_job_vacancy","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description TEXT CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = CONCAT(action_description, ' ', 'Job Vacancy Template was Deleted\\n');\n IF(IFNULL(old_job_title_code,'') != '') THEN\n SET action_description = CONCAT(action_description, ' ', 'Job Title: ',IFNULL((SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = old_job_title_code),' '),'\\n');\n END IF;\n IF(IFNULL(old_name,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Vacancy Name: ',IFNULL(old_name,' '),'\\n');END IF;\n IF(IFNULL(old_no_of_positions,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Number of Positions: ',IFNULL(old_no_of_positions,' '),'\\n');END IF;\n IF(IFNULL(old_location_id,'') != '') THEN\n SET action_description = CONCAT(action_description, ' ', 'Location: ',IFNULL((SELECT `name` FROM `ohrm_location` WHERE `id` = old_location_id),' '),'\\n');\n END IF;\n IF(IFNULL(old_sub_unit_id,'') != '') THEN\n SET action_description = CONCAT(action_description, ' ', 'Sub Unit: ',IFNULL((SELECT `name` FROM `ohrm_subunit` WHERE `id` = old_sub_unit_id),' '),'\\n');\n END IF;\n IF (IFNULL(old_status,'') != '') THEN\n SET @old_status_text = (select name from ohrm_job_vacancy_status where status = old_status);\n SET action_description = CONCAT(action_description, ' ', 'Status: ', IFNULL(@old_status_text, ' '), '\\n');\n END IF;\n SET @old_request_consent_text = (SELECT IF(IFNULL(old_request_consent,0)=1,'Enabled','Disabled'));\n SET action_description = CONCAT(action_description, ' ', 'Request Consent was: ', IFNULL(@old_request_consent_text, ' '), '\\n');\n SET @old_is_resume_required_text = (SELECT IF(IFNULL(old_is_resume_required,0)=1,'Enabled','Disabled'));\n SET action_description = CONCAT(action_description, ' ', 'Resume Required Option was: ', IFNULL(@old_is_resume_required_text, ' '), '\\n');\n IF(IFNULL(old_description,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Job Posting Description: ',IFNULL(old_description,' '),'\\n');END IF;\n IF(IFNULL(template_name,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Template Name: ',IFNULL(template_name,' '),'\\n');END IF;\n IF(IFNULL(template_description,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Description: ',IFNULL(template_description,' '),'\\n');END IF;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n INSERT INTO `ohrm_audittrail_recruitment_job_vacancy_template_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n 'DELETE VACANCY TEMPLATE',\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_insert_hs_hr_emp_picture","ROUTINE_NAME":"audit_insert_hs_hr_emp_picture","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = CONCAT(action_description, ' ', 'Profile picture was added');\n IF (IFNULL(epic_filename,'') != '' ) THEN SET action_description = CONCAT(action_description, '\\n File Name:', epic_filename);END IF;\n-- End generating the action description\n\n IF (action_description != '') THEN\n INSERT INTO `ohrm_audittrail_pim_profile_picture_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\nEND IF;\n\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_insert_hs_hr_emp_reportto","ROUTINE_NAME":"audit_insert_hs_hr_emp_reportto","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n\n SET action_description = '';\n -- Start generating the action description\n\n 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) , '');\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_insert_hs_hr_emp_work_experience","ROUTINE_NAME":"audit_insert_hs_hr_emp_work_experience","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = CONCAT(action_description, ' ', 'New work experience was added (', new_eexp_jobtit, ' at ', new_eexp_employer,')\\n');\n\n IF (new_eexp_from_date IS NOT NULL) THEN\n SET action_description = CONCAT(action_description, ' From : ', new_eexp_from_date,'\\n');\n END IF;\n\n IF (new_eexp_to_date IS NOT NULL) THEN\n SET action_description = CONCAT(action_description, ' To : ', new_eexp_to_date,'\\n');\n END IF;\n\n IF (new_eexp_creditable = 1) THEN\n SET action_description = CONCAT(action_description, ' Creditable : ', 'Yes','\\n');\n ELSE\n SET action_description = CONCAT(action_description, ' Creditable : ', 'No','\\n');\n END IF;\n\n IF (new_eexp_comments IS NOT NULL) THEN\n SET action_description = CONCAT(action_description, ' Comment : ', new_eexp_comments,'\\n');\n END IF;\n\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_insert_ohrm_audittrail_pim_tab_custom_field_trail","ROUTINE_NAME":"audit_insert_ohrm_audittrail_pim_tab_custom_field_trail","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE tab_count TINYINT(1);\n DECLARE section TEXT CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SELECT count(* ) INTO tab_count FROM ohrm_audittrail_section WHERE is_deleted = 0 AND name = pim_tab;\n SELECT description INTO section FROM ohrm_audittrail_section WHERE is_deleted = 0 AND name = pim_tab;\n\n IF (tab_count !=0) THEN\n INSERT INTO `ohrm_audittrail_all` (action_time, action_owner_id, action_owner_name,action,action_description,version_id,affected_entity_id,affected_entity_name,section,app_id,app_name,screen_name) VALUES (\n action_time,\n action_owner_id,\n action_owner_name,\n action,\n action_description,\n version_id,\n affected_entity_id,\n affected_entity_name,\n section,\n app_id,\n app_name,\n screen_name\n );\n\n END IF;\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_insert_ohrm_hiring_managers","ROUTINE_NAME":"audit_insert_ohrm_hiring_managers","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description TEXT CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n SET action_description = 'New Hiring Manager was Added for Job Vacancy\\n';\n IF(IFNULL(affected_entity_name,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Vacancy Name: ',IFNULL(affected_entity_name,' '),'\\n');END IF;\n IF(IFNULL(new_employee_id,'') != '') THEN\n SET action_description = CONCAT(action_description, ' ', 'Hiring Manager Name : ',IFNULL((SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_employee WHERE hs_hr_employee.emp_number = new_employee_id),' '),'\\n');\n END IF;\n -- End generating the action description\n IF (action_description != '') THEN\n INSERT INTO `ohrm_audittrail_recruitment_job_vacancy_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n 'CHANGE JOB VACANCY',\n new_vacancy_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_insert_ohrm_hiring_managers_for_vacancy_template","ROUTINE_NAME":"audit_insert_ohrm_hiring_managers_for_vacancy_template","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description TEXT CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n SET action_description = 'New Hiring Manager was Added for Job Vacancy Template\\n';\n IF(IFNULL(affected_entity_name,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Template Name: ',IFNULL(affected_entity_name,' '),'\\n');END IF;\n IF(IFNULL(new_employee_id,'') != '') THEN\n SET action_description = CONCAT(action_description, ' ', 'Hiring Manager Name : ',IFNULL((SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_employee WHERE hs_hr_employee.emp_number = new_employee_id),' '),'\\n');\n END IF;\n -- End generating the action description\n IF (action_description != '') THEN\n INSERT INTO `ohrm_audittrail_recruitment_job_vacancy_template_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n 'UPDATE VACANCY TEMPLATE',\n new_vacancy_template_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n\n );\n END IF;\n END","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_insert_ohrm_job_vacancy","ROUTINE_NAME":"audit_insert_ohrm_job_vacancy","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description TEXT CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = 'New Job Vacancy was Added\\n';\n IF(IFNULL(new_job_title_code,'') != '') THEN\n SET action_description = CONCAT(action_description, ' ', 'Job Title: ',IFNULL((SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = new_job_title_code),' '),'\\n');\n END IF;\n IF(IFNULL(new_name,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Vacancy Name: ',IFNULL(new_name,' '),'\\n');END IF;\n IF(IFNULL(new_no_of_positions,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Number of Positions: ',IFNULL(new_no_of_positions,' '),'\\n');END IF;\n IF(IFNULL(new_location_id,'') != '') THEN\n SET action_description = CONCAT(action_description, ' ', 'Location: ',IFNULL((SELECT `name` FROM `ohrm_location` WHERE `id` = new_location_id),' '),'\\n');\n END IF;\n IF(IFNULL(new_sub_unit_id,'') != '') THEN\n SET action_description = CONCAT(action_description, ' ', 'Sub Unit: ',IFNULL((SELECT `name` FROM `ohrm_subunit` WHERE `id` = new_sub_unit_id),' '),'\\n');\n END IF;\n IF (IFNULL(new_status,'') != '') THEN\n SET @new_status_text = (select name from ohrm_job_vacancy_status where status = new_status);\n SET action_description = CONCAT(action_description, ' ', 'Status: ', IFNULL(@new_status_text, ' '), '\\n');\n END IF;\n SET @new_request_consent_text = (SELECT IF(IFNULL(new_request_consent,0)=1,'Enabled','Disabled'));\n SET action_description = CONCAT(action_description, ' ', 'Request Consent option: ', IFNULL(@new_request_consent_text, ' '), '\\n');\n SET @new_is_resume_required_text = (SELECT IF(IFNULL(new_is_resume_required,0)=1,'Enabled','Disabled'));\n SET action_description = CONCAT(action_description, ' ', 'Resume Required Option: ', IFNULL(@new_is_resume_required_text, ' '), '\\n');\n SET @new_include_archived_candidate_on_dynamic_rule_filter_text = (SELECT IF(IFNULL(new_include_archived_candidate_on_dynamic_rule_filter,0)=1,'Enabled','Disabled'));\n SET action_description = CONCAT(action_description, ' ', 'Include Archived Candidate Option for the Best Match Rules: ', IFNULL(@new_include_archived_candidate_on_dynamic_rule_filter_text, ' '), '\\n');\n\n IF(IFNULL(new_description,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Description: ',IFNULL(new_description,' '),'\\n');END IF;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n INSERT INTO `ohrm_audittrail_recruitment_job_vacancy_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_insert_ohrm_job_vacancy_template","ROUTINE_NAME":"audit_insert_ohrm_job_vacancy_template","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description TEXT CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = 'New Job Vacancy Template was Added\\n';\n IF(IFNULL(new_name,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Vacancy Template Name: ',IFNULL(new_name,' '),'\\n');\n END IF;\n IF(IFNULL(new_vacancy_name,'') != '') THEN\n SET action_description = CONCAT(action_description, ' ', 'Vacancy Name: ', IFNULL(new_vacancy_name,' '),'\\n');\n END IF;\n IF(IFNULL(new_job_title_code,'') != '') THEN\n SET action_description = CONCAT(action_description, ' ', 'Job Title: ',IFNULL((SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = new_job_title_code),' '),'\\n');\n END IF;\n IF(IFNULL(new_location_code,'') != '') THEN\n SET action_description = CONCAT(action_description, ' ', 'Location: ',IFNULL((SELECT `name` FROM `ohrm_location` WHERE `id` = new_location_code),' '),'\\n');\n END IF;\n IF (IFNULL(new_status,'') != '') THEN\n SET @new_status_text = (select name from ohrm_job_vacancy_status where status = new_status);\n SET action_description = CONCAT(action_description, ' ', 'Status: ', IFNULL(@new_status_text, ' '), '\\n');\n END IF;\n SET @new_request_consent_text = (SELECT IF(IFNULL(new_request_consent,0)=1,'Enabled','Disabled'));\n SET action_description = CONCAT(action_description, ' ', 'Request Consent option: ', IFNULL(@new_request_consent_text, ' '), '\\n');\n SET @new_is_resume_required_text = (SELECT IF(IFNULL(new_is_resume_required,0)=1,'Enabled','Disabled'));\n SET action_description = CONCAT(action_description, ' ', 'Resume Required Option: ', IFNULL(@new_is_resume_required_text, ' '), '\\n');\n\n\n IF(IFNULL(new_description,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Description: ',IFNULL(new_description,' '),'\\n');END IF;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n INSERT INTO `ohrm_audittrail_recruitment_job_vacancy_template_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n 'ADD VACANCY TEMPLATE',\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n END","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_insert_to_ohrm_audittrail_all","ROUTINE_NAME":"audit_insert_to_ohrm_audittrail_all","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n INSERT INTO `ohrm_audittrail_all` (action_time, action_owner_id, action_owner_name,action,action_description,version_id,affected_entity_id,affected_entity_name,section,app_id,app_name,screen_name) VALUES (\n action_time,\n action_owner_id,\n action_owner_name,\n action,\n action_description,\n version_id,\n affected_entity_id,\n affected_entity_name,\n section,\n app_id,\n app_name,\n screen_name\n );\n\n END","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_insert_to_ohrm_audittrail_online_training","ROUTINE_NAME":"audit_insert_to_ohrm_audittrail_online_training","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\nSET @section := (SELECT DISTINCT ohrm_audittrail_section.name FROM ohrm_audittrail_training_online_course_trail\n LEFT JOIN ohrm_audittrail_section_actions\n ON (ohrm_audittrail_training_online_course_trail.action = ohrm_audittrail_section_actions.action)\n LEFT JOIN ohrm_audittrail_section\n ON (ohrm_audittrail_section.id = ohrm_audittrail_section_actions.section_id)\n WHERE ohrm_audittrail_training_online_course_trail.action = action);\nCASE\nWHEN (SELECT @section) = 'CourseDetail' THEN\nCALL audit_insert_to_ohrm_audittrail_online_training_course_detail(\n action_time,\n action_owner_id,\n action_owner_name,\n action,\n action_description,\n version_id,\n affected_entity_id,\n affected_entity_name\n );\nWHEN (SELECT @section) = 'Resource' THEN\nCALL audit_insert_to_ohrm_audittrail_online_training_resource(\n action_time,\n action_owner_id,\n action_owner_name,\n action,\n action_description,\n version_id,\n affected_entity_id,\n affected_entity_name\n );\nWHEN (SELECT @section) = 'Assessment' THEN\nCALL audit_insert_to_ohrm_audittrail_online_training_assessment(\n action_time,\n action_owner_id,\n action_owner_name,\n action,\n action_description,\n version_id,\n affected_entity_id,\n affected_entity_name\n );\nWHEN (SELECT @section) = 'Reviewer' THEN\nCALL audit_insert_to_ohrm_audittrail_online_training_reviewer(\n action_time,\n action_owner_id,\n action_owner_name,\n action,\n action_description,\n version_id,\n affected_entity_id,\n affected_entity_name\n );\nWHEN (SELECT @section) = 'Approver' THEN\nCALL audit_insert_to_ohrm_audittrail_online_training_approver(\n action_time,\n action_owner_id,\n action_owner_name,\n action,\n action_description,\n version_id,\n affected_entity_id,\n affected_entity_name\n );\nWHEN (SELECT @section) = 'CourseEmployee' THEN\nCALL audit_insert_to_ohrm_audittrail_online_training_course_employee(\n action_time,\n action_owner_id,\n action_owner_name,\n action,\n action_description,\n version_id,\n affected_entity_id,\n affected_entity_name\n );\nWHEN (SELECT @section) = 'MyCourse' THEN\nCALL audit_insert_to_ohrm_audittrail_online_training_my_course(\n action_time,\n action_owner_id,\n action_owner_name,\n action,\n action_description,\n version_id,\n affected_entity_id,\n affected_entity_name\n );\nEND CASE;\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_insert_to_ohrm_audittrail_online_training_approver","ROUTINE_NAME":"audit_insert_to_ohrm_audittrail_online_training_approver","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\nINSERT INTO `ohrm_audittrail_online_training_approver_trail` (\n action_time,\n action_owner_id,\n action_owner_name,\n action,\n action_description,\n version_id,\n affected_entity_id,\n affected_entity_name,\n app_id,\n app_name,\n screen_name\n) VALUES (\n action_time,\n action_owner_id,\n action_owner_name,\n action,\n action_description,\n version_id,\n affected_entity_id,\n affected_entity_name,\n app_id,\n app_name,\n screen_name\n\n );\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_insert_to_ohrm_audittrail_online_training_assessment","ROUTINE_NAME":"audit_insert_to_ohrm_audittrail_online_training_assessment","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\nINSERT INTO `ohrm_audittrail_online_training_assessment_trail` (\n action_time,\n action_owner_id,\n action_owner_name,\n action,\n action_description,\n version_id,\n affected_entity_id,\n affected_entity_name,\n app_id,\n app_name,\n screen_name\n) VALUES (\n action_time,\n action_owner_id,\n action_owner_name,\n action,\n action_description,\n version_id,\n affected_entity_id,\n affected_entity_name,\n app_id,\n app_name,\n screen_name\n );\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_insert_to_ohrm_audittrail_online_training_course_detail","ROUTINE_NAME":"audit_insert_to_ohrm_audittrail_online_training_course_detail","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\nINSERT INTO `ohrm_audittrail_online_training_course_detail_trail` (\n action_time,\n action_owner_id,\n action_owner_name,\n action,\n action_description,\n version_id,\n affected_entity_id,\n affected_entity_name,\n app_id,\n app_name,\n screen_name\n) VALUES (\n action_time,\n action_owner_id,\n action_owner_name,\n action,\n action_description,\n version_id,\n affected_entity_id,\n affected_entity_name,\n app_id,\n app_name,\n screen_name\n );\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_insert_to_ohrm_audittrail_online_training_course_employee","ROUTINE_NAME":"audit_insert_to_ohrm_audittrail_online_training_course_employee","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\nINSERT INTO `ohrm_audittrail_online_training_course_employee_trail` (\n action_time,\n action_owner_id,\n action_owner_name,\n action,\n action_description,\n version_id,\n affected_entity_id,\n affected_entity_name,\n app_id,\n app_name,\n screen_name\n) VALUES (\n action_time,\n action_owner_id,\n action_owner_name,\n action,\n action_description,\n version_id,\n affected_entity_id,\n affected_entity_name,\n app_id,\n app_name,\n screen_name\n );\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_insert_to_ohrm_audittrail_online_training_my_course","ROUTINE_NAME":"audit_insert_to_ohrm_audittrail_online_training_my_course","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\nINSERT INTO `ohrm_audittrail_online_training_my_course_trail` (\n action_time,\n action_owner_id,\n action_owner_name,\n action,\n action_description,\n version_id,\n affected_entity_id,\n affected_entity_name,\n app_id,\n app_name,\n screen_name\n) VALUES (\n action_time,\n action_owner_id,\n action_owner_name,\n action,\n action_description,\n version_id,\n affected_entity_id,\n affected_entity_name,\n app_id,\n app_name,\n screen_name\n );\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_insert_to_ohrm_audittrail_online_training_resource","ROUTINE_NAME":"audit_insert_to_ohrm_audittrail_online_training_resource","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\nINSERT INTO `ohrm_audittrail_online_training_resource_trail` (\n action_time,\n action_owner_id,\n action_owner_name,\n action,\n action_description,\n version_id,\n affected_entity_id,\n affected_entity_name,\n app_id,\n app_name,\n screen_name\n) VALUES (\n action_time,\n action_owner_id,\n action_owner_name,\n action,\n action_description,\n version_id,\n affected_entity_id,\n affected_entity_name,\n app_id,\n app_name,\n screen_name\n );\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_insert_to_ohrm_audittrail_online_training_reviewer","ROUTINE_NAME":"audit_insert_to_ohrm_audittrail_online_training_reviewer","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\nINSERT INTO `ohrm_audittrail_online_training_reviewer_trail` (\n action_time,\n action_owner_id,\n action_owner_name,\n action,\n action_description,\n version_id,\n affected_entity_id,\n affected_entity_name,\n app_id,\n app_name,\n screen_name\n) VALUES (\n action_time,\n action_owner_id,\n action_owner_name,\n action,\n action_description,\n version_id,\n affected_entity_id,\n affected_entity_name,\n app_id,\n app_name,\n screen_name\n );\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_update_hs_hr_emp_picture","ROUTINE_NAME":"audit_update_hs_hr_emp_picture","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n IF (IFNULL(new_epic_picture, '') != IFNULL(old_epic_picture, '')) THEN SET action_description = CONCAT(action_description, ' ', 'Profile picture was changed from ', IFNULL(old_epic_filename, ' '), ' to ', IFNULL(new_epic_filename, ' '), '\\n');END IF;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n INSERT INTO `ohrm_audittrail_pim_profile_picture_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\nEND IF;\n\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_update_hs_hr_emp_reportto","ROUTINE_NAME":"audit_update_hs_hr_emp_reportto","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n\n SET action_description = '';\n -- Start generating the action description\n\n 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');\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;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_update_hs_hr_emp_work_experience","ROUTINE_NAME":"audit_update_hs_hr_emp_work_experience","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n\n SET action_description = '';\n -- Start generating the action description\n SET action_description = CONCAT('(Employer :',old_eexp_employer , ' Job Title :',old_eexp_jobtit,')\\n');\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;\n 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;\n 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;\n 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;\n IF (new_eexp_creditable = 0 ) THEN SET action_description = CONCAT(action_description, ' Creditable was changed from Yes to No','\\n');END IF;\n IF (new_eexp_creditable = 1 ) THEN SET action_description = CONCAT(action_description, ' Creditable was changed from No to Yes','\\n');END IF;\n 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;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n action_name,\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_update_hs_hr_employee_termination","ROUTINE_NAME":"audit_update_hs_hr_employee_termination","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description VARCHAR(600) CHARSET UTF8;\n DECLARE action_description_termination VARCHAR(600) CHARSET UTF8;\n DECLARE performer_id INT;\n DECLARE performer_name VARCHAR(300 ) CHARSET UTF8;\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n\n SET action_description = '';\n SET action_description_termination = '';\n -- Start generating the action description\n\n IF (IFNULL(old_temination_id, '') != IFNULL(new_temination_id, '') && IFNULL(new_temination_id, '') != 0) THEN\n SET performer_id = (SELECT `termination_performed_user_id` 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 SET performer_id = IFNULL(performer_id, action_owner_id);\n SET performer_name = (SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname, IF(hs_hr_employee.termination_id IS NULL, '', ' (Past Employee)')),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 = performer_id );\n SET performer_name = IFNULL(performer_name, action_owner_name);\n\n SET action_description_termination = CONCAT(action_description_termination, ' ',\n 'Employee Terminated : ', \n 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 '\\n Date : ', \n 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 Time : ',\n IFNULL((SELECT `termination_time` 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 Time Zone : ',\n IFNULL((SELECT IF(`termination_time_zone_name` IS NULL, NULL, CONCAT('(', `termination_time_zone_prefix`,') ', IFNULL(z.label, termination_time_zone_name))) FROM `hs_hr_employee` e LEFT JOIN ohrm_emp_termination t ON e.termination_id = t.id LEFT JOIN ohrm_standard_time_zone z ON z.timezone_key = t.termination_time_zone_name WHERE e.emp_number = affected_entity_id), ' '),\n '\\n',\n '\\n Note : ', \n 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 '\\n');\n\nEND IF;\n\n\n IF ( (IFNULL(new_temination_id, '') = '' != IFNULL(new_temination_id, '') = '') && IFNULL(new_temination_id, '') = '') \n 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 ');\nEND IF;\n\n\n\n -- End generating the action description\n\n IF (action_description_termination != '') THEN\n\n \n 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,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n performer_id,\n performer_name,\n new_version,\n 'UPDATE TERMINATION DETAILS',\n affected_entity_id,\n affected_entity_name,\n action_description_termination,\n app_id,\n app_name,\n screen_name\n );\n \n END IF;\n \nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_update_ohrm_job_vacancy","ROUTINE_NAME":"audit_update_ohrm_job_vacancy","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description TEXT CHARSET UTF8;\n\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n IF (IFNULL(old_job_title_code,'') != IFNULL(new_job_title_code,'')) THEN\n SET action_description = CONCAT(action_description, ' ', 'Job Title was changed from ', IFNULL((SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = old_job_title_code),' '), ' to ', IFNULL((SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = new_job_title_code),' '), '\\n');\n END IF;\n IF (IFNULL(old_name,'') != IFNULL(new_name,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Vacancy Name was changed from ', IFNULL(old_name, ' '), ' to ', IFNULL(new_name, ' '), '\\n');END IF;\n IF (IFNULL(old_no_of_positions,'') != IFNULL(new_no_of_positions,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Number of Positions was changed from ', IFNULL(old_no_of_positions, ' '), ' to ', IFNULL(new_no_of_positions, ' '), '\\n');END IF;\n IF (IFNULL(old_location_id,'') != IFNULL(new_location_id,'')) THEN\n SET action_description = CONCAT(action_description, ' ', 'Location was changed from ', IFNULL((SELECT `name` FROM `ohrm_location` WHERE `id` = old_location_id),' '), ' to ', IFNULL((SELECT `name` FROM `ohrm_location` WHERE `id` = new_location_id),' '), '\\n');\n END IF;\n IF (IFNULL(old_sub_unit_id,'') != IFNULL(new_sub_unit_id,'')) THEN\n SET action_description = CONCAT(action_description, ' ', 'Sub Unit was changed from ', IFNULL((SELECT `name` FROM `ohrm_subunit` WHERE `id` = old_sub_unit_id),' '), ' to ', IFNULL((SELECT `name` FROM `ohrm_subunit` WHERE `id` = new_sub_unit_id),' '), '\\n');\n END IF;\n IF (IFNULL(old_status,'') != IFNULL(new_status,'')) THEN\n SET @old_status_text = (select name from ohrm_job_vacancy_status where status = old_status);\n SET @new_status_text = (select name from ohrm_job_vacancy_status where status = new_status);\n SET action_description = CONCAT(action_description, ' ', 'Status was changed from ', IFNULL(@old_status_text, ' '), ' to ', IFNULL(@new_status_text, ' '), '\\n');\n END IF;\n IF (old_request_consent != new_request_consent) THEN\n SET @old_request_consent_text = (SELECT IF(IFNULL(old_request_consent,0)=1,'Enabled','Disabled'));\n SET @new_request_consent_text = (SELECT IF(IFNULL(new_request_consent,0)=1,'Enabled','Disabled'));\n SET action_description = CONCAT(action_description, ' ', 'Request Consent option was changed from ', IFNULL(@old_request_consent_text, ' '), ' to ', IFNULL(@new_request_consent_text, ' '), '\\n');\n END IF;\n IF (old_is_resume_required != new_is_resume_required) THEN\n SET @old_is_resume_required_text = (SELECT IF(IFNULL(old_is_resume_required,0)=1,'Enabled','Disabled'));\n SET @new_is_resume_required_text = (SELECT IF(IFNULL(new_is_resume_required,0)=1,'Enabled','Disabled'));\n SET action_description = CONCAT(action_description, ' ', 'Resume Required Option was changed from ', IFNULL(@old_is_resume_required_text, ' '), ' to ', IFNULL(@new_is_resume_required_text, ' '), '\\n');\n END IF;\n\n IF (old_include_archived_candidate_on_dynamic_rule_filter != new_include_archived_candidate_on_dynamic_rule_filter) THEN\n SET @old_include_archived_candidate_on_dynamic_rule_filter_text = (SELECT IF(IFNULL(old_include_archived_candidate_on_dynamic_rule_filter,0)=1,'Enabled','Disabled'));\n SET @new_include_archived_candidate_on_dynamic_rule_filter_text = (SELECT IF(IFNULL(new_include_archived_candidate_on_dynamic_rule_filter,0)=1,'Enabled','Disabled'));\n SET action_description = CONCAT(action_description, ' ', 'Include Archived Candidate Option was changed from ', IFNULL(@old_include_archived_candidate_on_dynamic_rule_filter_text, ' '), ' to ', IFNULL(@new_include_archived_candidate_on_dynamic_rule_filter_text, ' '), ' for the Best Match Rules\\n');\n END IF;\n\n IF (IFNULL(old_description,'') != IFNULL(new_description,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Description was changed from ', resolve_empty(old_description), ' to ', resolve_empty(new_description), '\\n');END IF;\n\n-- End generating the action description\n\n IF (action_description != '') THEN\n INSERT INTO `ohrm_audittrail_recruitment_job_vacancy_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n 'CHANGE JOB VACANCY',\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n\n );\n END IF;\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_update_ohrm_job_vacancy_template","ROUTINE_NAME":"audit_update_ohrm_job_vacancy_template","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description TEXT CHARSET UTF8;\n\n SET action_description = '';\n# Start generating the action description\n\n SET action_description = CONCAT(action_description, ' ',current_action_description);\n\n\n IF (IFNULL(old_name,'') != IFNULL(new_name,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Vacancy Template Name was changed from ', IFNULL(old_name, ' '), ' to ', IFNULL(new_name, ' '), '\\n');END IF;\n\n IF (IFNULL(old_description,'') != IFNULL(new_description,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Description was changed from ', resolve_empty(old_description), ' to ', resolve_empty(new_description), '\\n');END IF;\n\n# End generating the action description\n\n IF (action_description != '') THEN\n UPDATE `ohrm_audittrail_recruitment_job_vacancy_template_trail` SET action_description = action_description WHERE affected_entity_id= affected_entity_id AND version_id = new_version;\n END IF;\n END","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"audit_update_ohrm_job_vacancy_template_from_job_vacancy_update","ROUTINE_NAME":"audit_update_ohrm_job_vacancy_template_from_job_vacancy_update","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE action_description TEXT CHARSET UTF8;\n DECLARE app_id INT;\n DECLARE app_name VARCHAR(1000) CHARSET UTF8;\n DECLARE screen_name VARCHAR(1000) CHARSET UTF8;\n\n SET app_id = @provenience_app_id;\n SET app_name = @provenience_app_name;\n SET screen_name = @provenience_screen_name;\n\n SET action_description = '';\n -- Start generating the action description\n\n SET action_description = CONCAT(action_description, ' ', 'Vacancy Template was Changed ', '\\n');\n IF (IFNULL(old_job_title_code,'') != IFNULL(new_job_title_code,'')) THEN\n SET action_description = CONCAT(action_description, ' ', 'Job Title was changed from ', IFNULL((SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = old_job_title_code),' '), ' to ', IFNULL((SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = new_job_title_code),' '), '\\n');\n END IF;\n IF (IFNULL(old_name,'') != IFNULL(new_name,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Vacancy Name was changed from ', IFNULL(old_name, ' '), ' to ', IFNULL(new_name, ' '), '\\n');END IF;\n IF (IFNULL(old_no_of_positions,'') != IFNULL(new_no_of_positions,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Number of Positions was changed from ', IFNULL(old_no_of_positions, ' '), ' to ', IFNULL(new_no_of_positions, ' '), '\\n');END IF;\n IF (IFNULL(old_location_id,'') != IFNULL(new_location_id,'')) THEN\n SET action_description = CONCAT(action_description, ' ', 'Location was changed from ', IFNULL((SELECT `name` FROM `ohrm_location` WHERE `id` = old_location_id),' '), ' to ', IFNULL((SELECT `name` FROM `ohrm_location` WHERE `id` = new_location_id),' '), '\\n');\n END IF;\n IF (IFNULL(old_sub_unit_id,'') != IFNULL(new_sub_unit_id,'')) THEN\n SET action_description = CONCAT(action_description, ' ', 'Sub Unit was changed from ', IFNULL((SELECT `name` FROM `ohrm_subunit` WHERE `id` = old_sub_unit_id),' '), ' to ', IFNULL((SELECT `name` FROM `ohrm_subunit` WHERE `id` = new_sub_unit_id),' '), '\\n');\n END IF;\n IF (IFNULL(old_status,'') != IFNULL(new_status,'')) THEN\n SET @old_status_text = (select name from ohrm_job_vacancy_status where status = old_status);\n SET @new_status_text = (select name from ohrm_job_vacancy_status where status = new_status);\n SET action_description = CONCAT(action_description, ' ', 'Status was changed from ', IFNULL(@old_status_text, ' '), ' to ', IFNULL(@new_status_text, ' '), '\\n');\n END IF;\n IF (old_request_consent != new_request_consent) THEN\n SET @old_request_consent_text = (SELECT IF(IFNULL(old_request_consent,0)=1,'Enabled','Disabled'));\n SET @new_request_consent_text = (SELECT IF(IFNULL(new_request_consent,0)=1,'Enabled','Disabled'));\n SET action_description = CONCAT(action_description, ' ', 'Request Consent option was changed from ', IFNULL(@old_request_consent_text, ' '), ' to ', IFNULL(@new_request_consent_text, ' '), '\\n');\n END IF;\n IF (old_is_resume_required != new_is_resume_required) THEN\n SET @old_is_resume_required_text = (SELECT IF(IFNULL(old_is_resume_required,0)=1,'Enabled','Disabled'));\n SET @new_is_resume_required_text = (SELECT IF(IFNULL(new_is_resume_required,0)=1,'Enabled','Disabled'));\n SET action_description = CONCAT(action_description, ' ', 'Resume Required Option was changed from ', IFNULL(@old_is_resume_required_text, ' '), ' to ', IFNULL(@new_is_resume_required_text, ' '), '\\n');\n END IF;\n\n IF (IFNULL(old_description,'') != IFNULL(new_description,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Description was changed from ', resolve_empty(old_description), ' to ', resolve_empty(new_description), '\\n');END IF;\n\n -- End generating the action description\n\n IF (action_description != '') THEN\n INSERT INTO `ohrm_audittrail_recruitment_job_vacancy_template_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description,app_id,app_name,screen_name) VALUES (\n CURRENT_TIMESTAMP,\n action_owner_id,\n action_owner_name,\n new_version,\n 'UPDATE VACANCY TEMPLATE',\n affected_entity_id,\n affected_entity_name,\n action_description,\n app_id,\n app_name,\n screen_name\n );\n END IF;\n END","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"dashboard_get_subunit_parent_id","ROUTINE_NAME":"dashboard_get_subunit_parent_id","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"int","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":"10","NUMERIC_SCALE":"0","DTD_IDENTIFIER":"int(11)","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\nSELECT (SELECT t2.id\n FROM ohrm_subunit t2\n WHERE t2.lft < t1.lft AND t2.rgt > t1.rgt\n ORDER BY t2.rgt-t1.rgt ASC LIMIT 1) INTO @parent\nFROM ohrm_subunit t1 WHERE t1.id = id;\n\nRETURN @parent;\n\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"format_seconds_to_time","ROUTINE_NAME":"format_seconds_to_time","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"varchar","CHARACTER_MAXIMUM_LENGTH":"50","NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":"varchar(50)","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n IF format IS NULL THEN\n RETURN seconds;\n END IF;\n\n IF INSTR(format, '%') > 0 THEN\n RETURN TIME_FORMAT(SEC_TO_TIME(seconds), format);\n END IF;\n\n SELECT CASE get_substring_in_position(format, '_', 1) WHEN 'h' THEN 3600 WHEN 'm' THEN 60 ELSE 1 END INTO @divider;\n SELECT get_substring_in_position(format, '_', 2) INTO @decimals;\n SELECT get_substring_in_position(format, '_', 3) INTO @suffix;\n\n IF @decimals = '' THEN\n SET @decimals := 2;\n END IF;\n\n RETURN CONCAT(ROUND(seconds\/@divider, @decimals), @suffix);\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"getCurrentLeavePeriod","ROUTINE_NAME":"getCurrentLeavePeriod","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\r\n\r\n Declare periodOffset INT DEFAULT 0;\r\n\r\n call getLeavePeriod(empNumber, leaveTypeId, periodOffset, startDate, endDate);\r\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"getLeavePeriod","ROUTINE_NAME":"getLeavePeriod","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"proc_get_leave_period_label:BEGIN\r\n\r\n\tDeclare leavePeriodType, duration, startMonth, startDay, offsetValue INT DEFAULT 0;\r\n\tDeclare thisYear, prevYear INT;\r\n\tDeclare thisDate DATE;\r\n Declare startDate DATE;\r\n Declare createdDate DATE;\r\n Declare nextFromDate DATE;\r\n Declare isStartDayOnLeapDay boolean DEFAULT false;\r\n \r\n IF @enableCachingGetLeavePeriodValue IS NOT NULL AND @enableCachingGetLeavePeriodValue = 1 AND @cachedGetLeavePeriodFromDate <> '' AND @cachedGetLeavePeriodToDate <> '' THEN \r\n SET fromDate = @cachedGetLeavePeriodFromDate; \r\n SET toDate = @cachedGetLeavePeriodToDate; \r\n LEAVE proc_get_leave_period_label;\r\n END IF;\r\n \r\n\tset thisDate = CURDATE();\r\n\tset thisYear = YEAR(thisDate);\r\n\r\n\t-- Get start month and day, duration\r\n\tSELECT leave_period_type, IF(leave_period_type = 1, 1, leave_period_duration), start_month, start_day, created_at\r\n\tINTO leavePeriodType, duration, startMonth, startDay, createdDate\r\n\tFROM ohrm_leave_type_leave_period WHERE leave_type_id = leaveTypeId;\r\n\r\n\t-- DEFAULT\r\n\tIF leavePeriodType = 1 THEN\r\n SELECT leave_period_start_month, leave_period_start_day, created_at INTO\r\n startMonth, startDay, createdDate\r\n FROM ohrm_leave_period_history ORDER by id DESC LIMIT 1;\r\n\tEND IF;\r\n\r\n\t-- HIRE DATE BASED\r\n\tIF leavePeriodType = 2 THEN\r\n SELECT joined_date INTO startDate\r\n FROM hs_hr_employee WHERE emp_number = empNumber;\r\n ELSE\r\n -- CUSTOM OR DEFAULT - start date based on created date of record\r\n SET startDate = STR_TO_DATE(CONCAT(YEAR(createdDate), '\/', startMonth, '\/', startDay), '%Y\/%m\/%d');\r\n IF createdDate < startDate THEN\r\n SET startDate = DATE_SUB(startDate, INTERVAL duration YEAR);\r\n END IF;\r\n\tEND IF;\r\n\r\n IF leavePeriodType = 2 AND month(startDate) = 2 AND day(startDate) = 29 THEN\r\n SET isStartDayOnLeapDay = true;\r\n END IF;\r\n\r\n\t-- TODO: Check for start date\/month not defined (eg: no joined date)\r\n\r\n -- Loop until we get current period\r\n SET fromDate = startDate;\r\n SET nextFromDate = DATE_ADD(fromDate, INTERVAL duration YEAR);\r\n IF isStartDayOnLeapDay AND (month(nextFromDate) = 2 AND day(nextFromDate) = 28) THEN\r\n SET nextFromDate = DATE(CONCAT(YEAR(nextFromDate),'-03-01'));\r\n END IF;\r\n WHILE nextFromDate <= thisDate DO\r\n SET fromDate = nextFromDate;\r\n SET nextFromDate = DATE_ADD(fromDate, INTERVAL duration YEAR);\r\n IF isStartDayOnLeapDay AND (month(nextFromDate) = 2 AND day(nextFromDate) = 28) THEN\r\n SET nextFromDate = DATE(CONCAT(YEAR(nextFromDate),'-03-01'));\r\n END IF;\r\n END WHILE;\r\n\r\n -- Apply period offset\r\n SET offsetValue = ABS(duration * periodOffset);\r\n\r\n IF periodOffset < 0 THEN\r\n set fromDate = DATE_SUB(fromDate, INTERVAL offsetValue YEAR);\r\n ELSEIF periodOffset > 0 THEN\r\n set fromDate = DATE_ADD(fromDate, INTERVAL offsetValue YEAR);\r\n END IF;\r\n\r\n\t-- end date\r\n SET toDate = DATE_ADD(fromDate, INTERVAL duration YEAR);\r\n \r\n IF isStartDayOnLeapDay THEN\r\n IF YEAR(fromDate) = YEAR(startDate) THEN\r\n SET fromDate = startDate;\r\n ELSE\r\n SET fromDate = DATE(CONCAT(YEAR(fromDate),'-03-01'));\r\n END IF;\r\n IF DATE(CONCAT(YEAR(toDate),'-02-29')) IS NOT NULL AND YEAR(toDate) <> YEAR(startDate) THEN\r\n SET toDate = DATE(CONCAT(YEAR(toDate),'-02-29'));\r\n ELSE\r\n SET toDate = DATE(CONCAT(YEAR(toDate),'-02-28'));\r\n END IF;\r\n ELSE \r\n SET toDate = DATE_SUB(toDate, INTERVAL 1 DAY);\r\n END IF;\r\n\r\n IF @enableCachingGetLeavePeriodValue IS NOT NULL AND @enableCachingGetLeavePeriodValue = 1 THEN \r\n SET @cachedGetLeavePeriodFromDate = fromDate; \r\n SET @cachedGetLeavePeriodToDate = toDate; \r\n END IF;\r\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"get_creditable_service_period","ROUTINE_NAME":"get_creditable_service_period","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"varchar","CHARACTER_MAXIMUM_LENGTH":"50","NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":"varchar(50)","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BLOCK0: BEGIN\n DECLARE durationInDays INT;\n DECLARE daysPerMonth FLOAT;\n DECLARE daysPerYear FLOAT;\n\n SET daysPerMonth = 30.436875;\n SET daysPerYear = 365;\n SET durationInDays = 0;\n\n IF id IS NULL\n THEN\n RETURN NULL;\n END IF;\n\n CREATE TEMPORARY TABLE IF NOT EXISTS `ohrm_temp_date_range` (start_date DATE, end_date DATE);\n CREATE TEMPORARY TABLE IF NOT EXISTS `ohrm_temp_indexed_date_range` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, start_date DATE, end_date DATE);\n\n IF today IS NULL\n THEN\n SELECT CURDATE() INTO today;\n END IF;\n\n CALL prepare_date_ranges(id, today, onlyCurrentService);\n\n BLOCK1: BEGIN\n DECLARE exit_loop BOOLEAN;\n DECLARE startDate DATE;\n DECLARE endDate DATE;\n DECLARE all_ranges_cursor CURSOR FOR\n SELECT start_date, end_date FROM ohrm_temp_date_range ORDER BY start_date ASC;\n DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;\n\n OPEN all_ranges_cursor;\n get_all_ranges_loop: LOOP\n FETCH all_ranges_cursor INTO startDate, endDate;\n IF exit_loop THEN\n LEAVE get_all_ranges_loop;\n END IF;\n SET durationInDays = durationInDays + DATEDIFF(endDate, DATE_SUB(startDate, INTERVAL 1 DAY));\n END LOOP get_all_ranges_loop;\n CLOSE all_ranges_cursor;\n END BLOCK1;\n DELETE FROM ohrm_temp_date_range;\n\n DROP TEMPORARY TABLE IF EXISTS ohrm_temp_date_range;\n DROP TEMPORARY TABLE IF EXISTS ohrm_temp_indexed_date_range;\n\n IF durationInDays > 0 THEN\n SET @allMonths = FLOOR(durationInDays\/daysPerMonth);\n SET @years = FLOOR(@allMonths\/12);\n SET @months = MOD(@allMonths, 12);\n SET @days = ROUND(MOD(durationInDays, daysPerMonth));\n RETURN CASE format\n WHEN 's' THEN durationInDays * 86400\n WHEN 'm' THEN ROUND(durationInDays \/ daysPerMonth, 1)\n WHEN 'y' THEN ROUND(durationInDays \/ daysPerYear, 1)\n ELSE CONCAT(@years, '_', @months, '_', @days)\n END;\n END IF;\n\n RETURN CASE format\n WHEN 's' THEN durationInDays\n WHEN 'm' THEN durationInDays\n WHEN 'y' THEN durationInDays\n ELSE '0_0_0'\n END;\n END BLOCK0","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"get_database_storage_size","ROUTINE_NAME":"get_database_storage_size","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"bigint","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":"19","NUMERIC_SCALE":"0","DTD_IDENTIFIER":"bigint(20)","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE\nsize BIGINT;\nSELECT SUM(data_length + index_length)\nINTO size\nFROM information_schema.TABLES\nWHERE table_schema = dbName;\n\nRETURN\nsize;\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"get_history_id_for_employee_and_date","ROUTINE_NAME":"get_history_id_for_employee_and_date","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"int","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":"10","NUMERIC_SCALE":"0","DTD_IDENTIFIER":"int(11)","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n\n IF history_type = 'job' THEN\n RETURN (SELECT id FROM `ohrm_job_record_history` WHERE effective_date <= as_of_date AND employee_number = emp_number ORDER BY effective_date desc, id DESC LIMIT 1);\n END IF;\n\n IF history_type = 'salary' THEN\n RETURN (SELECT id FROM `ohrm_salary_record_history` WHERE effective_date <= as_of_date AND employee_number = emp_number ORDER BY effective_date desc, id DESC LIMIT 1);\n END IF;\n\nRETURN NULL;\n\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"get_parant_id","ROUTINE_NAME":"get_parant_id","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"int","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":"10","NUMERIC_SCALE":"0","DTD_IDENTIFIER":"int(11)","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\nSELECT (SELECT t2.id\n FROM ohrm_subunit t2\n WHERE t2.lft < t1.lft AND t2.rgt > t1.rgt\n ORDER BY t2.rgt-t1.rgt ASC LIMIT 1) INTO @parent\nFROM ohrm_subunit t1 WHERE t1.id = id;\n\nRETURN @parent;\n\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"get_requestdesk_approver_name","ROUTINE_NAME":"get_requestdesk_approver_name","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"text","CHARACTER_MAXIMUM_LENGTH":"65535","NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":"text","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE resultValue TEXT;\n DECLARE entityProcessId INT(11);\n DECLARE requestProcessId INT(11);\n DECLARE actorTypeId INT(11);\n DECLARE workflowRequestId INT(11);\n DECLARE empNumber INT(11);\n DECLARE fname VARCHAR(100);\n DECLARE lname VARCHAR(100);\n DECLARE terminationId INT(4);\n SET entityProcessId = (SELECT id FROM `ohrm_advanced_workflow_entity_process` WHERE `entity` LIKE 'Request' LIMIT 1);\n SET workflowRequestId = (SELECT workflow_request_id FROM `ohrm_request` WHERE `id` = requestid);\n SET requestProcessId = (SELECT id FROM `ohrm_advanced_workflow_request_process` WHERE `request_id` = workflowRequestId AND `entity_process_id` = entity_process_id LIMIT 1);\n SET actorTypeId = (SELECT id FROM `ohrm_advanced_workflow_entity_process_actor_type` WHERE `entity_process_id` = entityProcessId AND `name` LIKE 'RequestApprover');\n SET empNumber = (SELECT emp_number FROM `ohrm_advanced_workflow_request_process_actor` WHERE `request_process_id` = requestProcessId AND `level` = aproverlevel AND `actor_type_id` = actorTypeId LIMIT 1);\n SET fname = (SELECT emp_firstname FROM `hs_hr_employee` WHERE `emp_number` = empNumber LIMIT 1);\n SET lname = (SELECT emp_lastname FROM `hs_hr_employee` WHERE `emp_number` = empNumber LIMIT 1);\n SET terminationId = (SELECT termination_id FROM `hs_hr_employee` WHERE `emp_number` = empNumber LIMIT 1);\n SET resultValue = (SELECT CONCAT_WS(\" \", fname, lname, IF(terminationId IS NULL, '', ' (Past Employee)')));\nRETURN (resultValue);\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"get_requestdesk_custom_field_value","ROUTINE_NAME":"get_requestdesk_custom_field_value","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"text","CHARACTER_MAXIMUM_LENGTH":"65535","NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":"text","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n DECLARE result_value TEXT;\n DECLARE stringValue varchar(255);\n DECLARE textValue TEXT;\n DECLARE numberValue double;\n DECLARE dateValue DATE;\n SET result_value = NULL;\n SET stringValue = (SELECT `string_value` FROM `ohrm_request_custom_field_value` WHERE `request_id` = requestid AND `custom_field_id` = customfieldid LIMIT 1);\n SET textValue = (SELECT `text_value` FROM `ohrm_request_custom_field_value` WHERE `request_id` = requestid AND `custom_field_id` = customfieldid LIMIT 1);\n SET numberValue = (SELECT `number_value` FROM `ohrm_request_custom_field_value` WHERE `request_id` = requestid AND `custom_field_id` = customfieldid LIMIT 1);\n SET dateValue = (SELECT `date_value` FROM `ohrm_request_custom_field_value` WHERE `request_id` = requestid AND `custom_field_id` = customfieldid LIMIT 1);\n IF stringValue IS NOT NULL THEN\n SET result_value = stringValue;\n ELSEIF textValue IS NOT NULL THEN\n SET result_value = textValue;\n ELSEIF numberValue IS NOT NULL THEN\n SET result_value = CONCAT(numberValue);\n ELSEIF dateValue IS NOT NULL THEN\n SET result_value = DATE_FORMAT(dateValue, \"%Y-%m-%d\");\n END IF;\n RETURN (result_value);\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"get_static_reference_value","ROUTINE_NAME":"get_static_reference_value","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"varchar","CHARACTER_MAXIMUM_LENGTH":"100","NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":"varchar(100)","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n 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);\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"get_sub_unit_accrued_employee_count","ROUTINE_NAME":"get_sub_unit_accrued_employee_count","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"int","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":"10","NUMERIC_SCALE":"0","DTD_IDENTIFIER":"int(11)","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n IF id IS NULL\n THEN\n RETURN NULL;\n END IF;\n\n SELECT cst.lft, cst.rgt INTO @lft, @rgt\n FROM `ohrm_subunit` AS cst\n WHERE cst.id = id\n LIMIT 1;\n\n RETURN (\n SELECT COUNT(emp.emp_number) AS sub_unit_accrued_emp_count\n FROM `hs_hr_employee` AS emp WHERE emp.work_station IN (SELECT cst.id FROM `ohrm_subunit` AS cst WHERE cst.lft >= @lft AND cst.rgt <= @rgt)\n );\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"get_sub_unit_at_hierarchy_by_level","ROUTINE_NAME":"get_sub_unit_at_hierarchy_by_level","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"varchar","CHARACTER_MAXIMUM_LENGTH":"512","NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":"varchar(512)","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n\n IF id IS NULL\n THEN\n RETURN NULL;\n END IF;\n\n SELECT cst.lft, cst.rgt INTO @lft, @rgt\n FROM `ohrm_subunit` AS cst\n WHERE cst.id = id\n LIMIT 1;\n\n RETURN (\n SELECT `cst`.`name` AS sub_unit_path\n FROM `ohrm_subunit` AS cst\n WHERE cst.lft <= @lft AND cst.rgt >= @rgt AND cst.level = hierarchy_level\n );\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"get_sub_unit_path","ROUTINE_NAME":"get_sub_unit_path","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"varchar","CHARACTER_MAXIMUM_LENGTH":"512","NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":"varchar(512)","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n\n IF id IS NULL\n THEN\n RETURN NULL;\n END IF;\n\n SELECT cst.lft, cst.rgt INTO @lft, @rgt\n FROM `ohrm_subunit` AS cst\n WHERE cst.id = id\n LIMIT 1;\n\n RETURN (\n SELECT GROUP_CONCAT(`cst`.`name` order by `level` SEPARATOR ' \/ ') AS sub_unit_path\n FROM `ohrm_subunit` AS cst\n WHERE cst.lft <= @lft AND cst.rgt >= @rgt\n );\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"get_sub_unit_title","ROUTINE_NAME":"get_sub_unit_title","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"varchar","CHARACTER_MAXIMUM_LENGTH":"255","NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":"varchar(255)","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n\n IF id IS NULL\n THEN\n RETURN '';\n END IF;\n\n SELECT `cst`.`name`, `cst`.`level` INTO @name, @level\n FROM `ohrm_subunit` AS cst\n WHERE `cst`.`id` = id\n LIMIT 1;\n\n IF @level = 0\n THEN\n RETURN 'Unassigned to Subunits';\n ELSE\n RETURN CONCAT(@name, ' (Current)');\n END IF;\n\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"get_substring_in_position","ROUTINE_NAME":"get_substring_in_position","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"varchar","CHARACTER_MAXIMUM_LENGTH":"255","NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":"varchar(255)","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n IF delim = '' THEN\n RETURN SUBSTRING(str, pos, 1);\n END IF;\n\n RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos), CHAR_LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1), delim, '');\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"is_sub_unit_leaf_node","ROUTINE_NAME":"is_sub_unit_leaf_node","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"tinyint","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":"3","NUMERIC_SCALE":"0","DTD_IDENTIFIER":"tinyint(1)","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n SELECT cst.lft, cst.rgt INTO @lft, @rgt\n FROM `ohrm_subunit` AS cst\n WHERE cst.id = id\n LIMIT 1;\n\n SELECT COUNT(*) INTO @children_count\n FROM `ohrm_subunit` AS cst\n WHERE cst.lft > @lft AND cst.rgt < @rgt\n LIMIT 1;\n\n RETURN (@children_count = 0);\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"leave_last_comment","ROUTINE_NAME":"leave_last_comment","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"varchar","CHARACTER_MAXIMUM_LENGTH":"255","NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":"varchar(255)","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n RETURN (select lc.comments from ohrm_leave_comment as lc where lc.leave_id = id order by lc.id desc limit 1);\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"leave_request_last_comment","ROUTINE_NAME":"leave_request_last_comment","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"varchar","CHARACTER_MAXIMUM_LENGTH":"255","NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":"varchar(255)","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n RETURN (select lrc.comments from ohrm_leave_request_comment as lrc where lrc.leave_request_id = id order by lrc.id desc limit 1);\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"overlapsWithCurrentLeavePeriod","ROUTINE_NAME":"overlapsWithCurrentLeavePeriod","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"tinyint","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":"3","NUMERIC_SCALE":"0","DTD_IDENTIFIER":"tinyint(1)","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\r\n\r\n\tDECLARE overlaps TINYINT(1);\r\n Declare periodOffset INT DEFAULT 0;\r\n\r\n SET overlaps = overlapsWithLeavePeriod(fromDate, toDate, empNumber, leaveTypeId, periodOffset);\r\n\r\n\tRETURN overlaps;\r\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"overlapsWithLeavePeriod","ROUTINE_NAME":"overlapsWithLeavePeriod","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"tinyint","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":"3","NUMERIC_SCALE":"0","DTD_IDENTIFIER":"tinyint(1)","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\r\n\r\n\tDECLARE overlaps TINYINT(1);\r\n\tDeclare startDate, endDate DATE;\r\n\r\n\tcall getLeavePeriod(empNumber, leaveTypeId, periodOffset, startDate, endDate);\r\n\r\n\t-- check if within period\r\n\tIF (fromDate <= startDate AND toDate >= startDate) OR (fromDate <= endDate AND toDate >= endDate) OR (fromDate >= startDate AND toDate <= endDate) THEN\r\n\t\tSET overlaps = TRUE;\r\n\tELSE\r\n\t\tSET overlaps = FALSE;\r\n\tEND IF;\r\n\r\n\tRETURN overlaps;\r\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"pbi_generate_entity_key","ROUTINE_NAME":"pbi_generate_entity_key","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"varchar","CHARACTER_MAXIMUM_LENGTH":"18","NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":"varchar(18)","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n RETURN CONCAT(prefix, LPAD(id, 8, '0'));\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"pbi_is_not_empty","ROUTINE_NAME":"pbi_is_not_empty","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"tinyint","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":"3","NUMERIC_SCALE":"0","DTD_IDENTIFIER":"tinyint(1)","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n RETURN NULLIF(string,'') IS NOT NULL;\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"prepare_date_ranges","ROUTINE_NAME":"prepare_date_ranges","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BLOCK0: BEGIN\n DECLARE joinedDate DATE;\n DECLARE startDate DATE;\n DECLARE endDate DATE;\n DECLARE isTerminated TINYINT(1);\n\n DECLARE terminations_cursor CURSOR FOR\n SELECT termination_date, reactivation_date FROM ohrm_emp_termination WHERE emp_number = id ORDER BY termination_date ASC;\n\n SELECT joined_date INTO joinedDate\n FROM ohrm_job_record_history\n WHERE employee_number = id AND joined_date IS NOT NULL AND joined_date != '' AND effective_date IS NOT NULL AND effective_date != ''\n ORDER BY effective_date , id LIMIT 1;\n\n IF joinedDate IS NOT NULL AND joinedDate <= today THEN\n\n OPEN terminations_cursor;\n BLOCK1: BEGIN\n DECLARE exit_loop BOOLEAN;\n DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;\n SET isTerminated = 0;\n get_terminations_loop: LOOP\n FETCH terminations_cursor INTO startDate, endDate;\n IF exit_loop THEN\n LEAVE get_terminations_loop;\n END IF;\n\n IF isTerminated = 0 AND (endDate IS NULL OR endDate = '' OR endDate > today) THEN\n SET isTerminated = 1;\n END IF;\n\n IF isTerminated = 1 THEN\n SET endDate = DATE_ADD(today, INTERVAL 1 DAY);\n END IF;\n\n INSERT INTO ohrm_temp_date_range (start_date, end_date) VALUES (startDate, endDate);\n END LOOP get_terminations_loop;\n END BLOCK1;\n CLOSE terminations_cursor;\n\n CALL sort_date_ranges();\n\n BLOCK2: BEGIN\n DECLARE exit_loop2 BOOLEAN;\n DECLARE startDate2 DATE;\n DECLARE endDate2 DATE;\n DECLARE dates_cursor CURSOR FOR\n SELECT start_date, end_date FROM ohrm_temp_date_range ORDER BY start_date ASC;\n DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop2 = TRUE;\n\n DELETE FROM ohrm_temp_indexed_date_range;\n SET startDate = joinedDate;\n OPEN dates_cursor;\n get_dates_loop: LOOP\n FETCH dates_cursor INTO startDate2, endDate2;\n IF exit_loop2 OR startDate > today THEN\n LEAVE get_dates_loop;\n END IF;\n SELECT LEAST(startDate2, today) INTO endDate;\n IF endDate >= startDate THEN\n INSERT INTO ohrm_temp_indexed_date_range (start_date, end_date) VALUES (startDate, endDate);\n END IF;\n SET startDate = endDate2;\n END LOOP get_dates_loop;\n CLOSE dates_cursor;\n\n IF startDate <= today THEN\n INSERT INTO ohrm_temp_indexed_date_range (start_date, end_date) VALUES (startDate, today);\n END IF;\n END BLOCK2;\n END IF;\n\n DELETE FROM ohrm_temp_date_range;\n INSERT INTO ohrm_temp_date_range (start_date, end_date)\n SELECT start_date, end_date FROM ohrm_temp_indexed_date_range;\n DELETE FROM ohrm_temp_indexed_date_range;\n\n IF !onlyCurrentService THEN\n BLOCK3: BEGIN\n DECLARE exit_loop3 BOOLEAN;\n DECLARE startDate3 DATE;\n DECLARE endDate3 DATE;\n DECLARE experience_cursor CURSOR FOR\n SELECT eexp_from_date, eexp_to_date FROM hs_hr_emp_work_experience\n WHERE emp_number = id AND eexp_creditable = 1 AND eexp_from_date IS NOT NULL AND eexp_to_date IS NOT NULL\n AND eexp_from_date != '0000-00-00 00:00:00' AND eexp_to_date != '0000-00-00 00:00:00'\n ORDER BY eexp_from_date, eexp_to_date ASC;\n DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop3 = TRUE;\n\n OPEN experience_cursor;\n get_experiences_loop: LOOP\n FETCH experience_cursor INTO startDate3, endDate3;\n IF exit_loop3 THEN\n LEAVE get_experiences_loop;\n END IF;\n\n INSERT INTO ohrm_temp_date_range (start_date, end_date) VALUES (startDate3, endDate3);\n END LOOP get_experiences_loop;\n CLOSE experience_cursor;\n END BLOCK3;\n END IF;\n CALL sort_date_ranges();\n END BLOCK0","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"resolve_empty","ROUTINE_NAME":"resolve_empty","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"varchar","CHARACTER_MAXIMUM_LENGTH":"10240","NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":"varchar(10240)","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\n RETURN IF(IFNULL(string, '') = '', '\" \"', string);\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"sort_date_ranges","ROUTINE_NAME":"sort_date_ranges","ROUTINE_TYPE":"PROCEDURE","DATA_TYPE":"","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":null,"NUMERIC_SCALE":null,"DTD_IDENTIFIER":null,"ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BLOCK0: BEGIN\n DECLARE startDate DATE;\n DECLARE endDate DATE;\n DECLARE previousEndDate DATE;\n DECLARE previousId INT;\n BLOCK1: BEGIN\n DECLARE exit_start_loop BOOLEAN;\n DECLARE start_cursor CURSOR FOR\n SELECT DISTINCT start_date, end_date FROM ohrm_temp_date_range ORDER BY start_date, end_date ASC;\n DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_start_loop = TRUE;\n SET previousId = NULL;\n OPEN start_cursor;\n get_start_dates_loop: LOOP\n FETCH start_cursor INTO startDate, endDate;\n IF exit_start_loop THEN\n LEAVE get_start_dates_loop;\n END IF;\n IF startDate <= endDate THEN\n IF previousId IS NULL OR startDate > previousEndDate THEN\n INSERT INTO ohrm_temp_indexed_date_range (start_date, end_date) VALUES (startDate, endDate);\n SET previousId = (SELECT LAST_INSERT_ID());\n ELSEIF endDate > previousEndDate THEN\n UPDATE ohrm_temp_indexed_date_range SET end_date = endDate WHERE id = previousId;\n END IF;\n SET previousEndDate = endDate;\n END IF;\n\n END LOOP get_start_dates_loop;\n CLOSE start_cursor;\n END BLOCK1;\n\n DELETE FROM ohrm_temp_date_range;\n INSERT INTO ohrm_temp_date_range (start_date, end_date) SELECT start_date, end_date FROM ohrm_temp_indexed_date_range;\n DELETE FROM ohrm_temp_indexed_date_range;\n END BLOCK0","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"NO","SQL_DATA_ACCESS":"CONTAINS SQL","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"validOnAsOfDateForLeavePeriod","ROUTINE_NAME":"validOnAsOfDateForLeavePeriod","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"tinyint","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":"3","NUMERIC_SCALE":"0","DTD_IDENTIFIER":"tinyint(1)","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\r\n\r\n\tDECLARE valid TINYINT(1);\r\n\tDeclare startDate, endDate, asOfDate DATE;\r\n\r\n\tcall getLeavePeriod(empNumber, leaveTypeId, periodOffset, startDate, endDate);\r\n\r\n -- Look at start date if period before current. Look at end date if period after current\r\n IF periodOffset < 0 THEN\r\n set asOfDate = endDate;\r\n ELSEIF periodOffset > 0 THEN\r\n set asOfDate = startDate;\r\n ELSE\r\n set asOfDate = CURDATE();\r\n END IF;\r\n\r\n -- INSERT INTO debug(message) values(CONCAT('E FromDate:', CAST(fromDate AS CHAR), ',E ToDate:', CAST(toDate AS CHAR), ',LP Start:', CAST(startDate AS CHAR), ',LP End:', CAST(endDate AS CHAR), ',Offset:', CAST(periodOffset AS CHAR), ',asOfDate:', CAST(asOfDate AS CHAR)));\r\n\r\n\t-- check if within period\r\n\tIF (asOfDate BETWEEN fromDate AND toDate) THEN\r\n\t\tSET valid = TRUE;\r\n\tELSE\r\n\t\tSET valid = FALSE;\r\n\tEND IF;\r\n\r\n\tRETURN valid;\r\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"withinCurrentLeavePeriod","ROUTINE_NAME":"withinCurrentLeavePeriod","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"tinyint","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":"3","NUMERIC_SCALE":"0","DTD_IDENTIFIER":"tinyint(1)","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\r\n\r\n\tDECLARE withinPeriod TINYINT(1);\r\n Declare periodOffset INT DEFAULT 0;\r\n\r\n\tSET withinPeriod = withinLeavePeriod(dateValue, empNumber, leaveTypeId, periodOffset);\r\n\r\n\tRETURN withinPeriod;\r\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"} {"SPECIFIC_NAME":"withinLeavePeriod","ROUTINE_NAME":"withinLeavePeriod","ROUTINE_TYPE":"FUNCTION","DATA_TYPE":"tinyint","CHARACTER_MAXIMUM_LENGTH":null,"NUMERIC_PRECISION":"3","NUMERIC_SCALE":"0","DTD_IDENTIFIER":"tinyint(1)","ROUTINE_BODY":"SQL","ROUTINE_DEFINITION":"BEGIN\r\n\r\n\tDECLARE withinPeriod TINYINT(1);\r\n\tDeclare startDate, endDate DATE;\r\n\r\n\tcall getLeavePeriod(empNumber, leaveTypeId, periodOffset, startDate, endDate);\r\n\r\n\t-- check if within period\r\n\tIF dateValue >= startDate AND dateValue <= endDate THEN\r\n\t\tSET withinPeriod = TRUE;\r\n\tELSE\r\n\t\tSET withinPeriod = FALSE;\r\n\tEND IF;\r\n\r\n\tRETURN withinPeriod;\r\nEND","EXTERNAL_NAME":null,"EXTERNAL_LANGUAGE":null,"PARAMETER_STYLE":"SQL","IS_DETERMINISTIC":"YES","SQL_DATA_ACCESS":"READS SQL DATA","SQL_PATH":null,"SECURITY_TYPE":"DEFINER","SQL_MODE":"","ROUTINE_COMMENT":"","CHARACTER_SET_CLIENT":"utf8","COLLATION_CONNECTION":"utf8_general_ci"}