CREATE TABLE IF NOT EXISTS ohrm_requestdesk_request_type_user_role (
  request_type_id BIGINT NOT NULL,
  user_role_id int(10) NOT NULL,
  PRIMARY KEY(request_type_id, user_role_id),
  FOREIGN KEY (request_type_id) REFERENCES ohrm_requestdesk_request_types(`id`) ON DELETE cascade,
  FOREIGN KEY (user_role_id) REFERENCES  ohrm_user_role(`id`) ON DELETE cascade
) ENGINE = INNODB DEFAULT CHARACTER SET = utf8;

INSERT INTO ohrm_requestdesk_request_type_user_role (request_type_id, user_role_id)
SELECT
    rt.id AS request_type_id,
    ur.id AS user_role_id
FROM
    ohrm_requestdesk_request_types rt,
    ohrm_user_role ur
WHERE
    (ur.is_assignable = 1 || ur.name = 'Supervisor') AND rt.id NOT IN (SELECT DISTINCT request_type_id FROM ohrm_requestdesk_request_type_user_role);
-- This query need to update based on the requirement (default user roles for existing request types).

ALTER TABLE `ohrm_vas_custom_advance_type_map` ADD COLUMN `type`  VARCHAR(50) DEFAULT 'select';
UPDATE `ohrm_vas_custom_advance_type_map` SET `type` = 'autocomplete-multyselect' WHERE `display_name` = 'Employee Multiple Select';
INSERT INTO `ohrm_vas_custom_advance_type_map` (`entity`, `display_name`, `id_field`, `name_field`, `type`) VALUES ('Employee', 'Employee', 'employeeId', 'firstName', 'auto-complete');

ALTER TABLE `ohrm_requestdesk_custom_field_section` ADD `is_template` BOOLEAN NOT NULL DEFAULT FALSE AFTER `is_customizable`;
ALTER TABLE `ohrm_requestdesk_custom_field_section` ADD `display_current_value` BOOLEAN NOT NULL DEFAULT FALSE AFTER `is_template`;

ALTER TABLE `ohrm_requestdesk_request_types` ADD `self_submit` BOOLEAN NOT NULL DEFAULT TRUE AFTER `secondary_owner_id`;
ALTER TABLE `ohrm_requestdesk_request_types` ADD `submit_for_other` BOOLEAN NOT NULL DEFAULT FALSE AFTER `self_submit`;

ALTER TABLE `ohrm_request` ADD `request_for_emp_number` INT(7) DEFAULT NULL AFTER `emp_number`;
ALTER TABLE `ohrm_request` ADD CONSTRAINT FOREIGN KEY (`request_for_emp_number`) REFERENCES `hs_hr_employee`(`emp_number`) ON DELETE SET NULL;

CREATE TABLE ohrm_requestdesk_custom_field_token
(
    id INT(4) AUTO_INCREMENT,
    custom_field_id INT(4) NOT NULL,
    name VARCHAR(255) NOT NULL,
    entity_name VARCHAR(50) NOT NULL,
    value_getters TEXT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (`custom_field_id`) REFERENCES `ohrm_requestdesk_custom_field`(`id`) ON DELETE CASCADE
) engine = innodb DEFAULT charset=utf8;

-- ALTER TABLE `ohrm_requestdesk_custom_field` ADD `is_readonly` BOOLEAN NOT NULL DEFAULT FALSE AFTER `is_default_field`;
ALTER TABLE `ohrm_requestdesk_custom_field` ADD `display_current_value` BOOLEAN NOT NULL DEFAULT FALSE AFTER `deletable`;

UPDATE ohrm_data_list SET `meta_data` = '{"listFields":{"requestId":"requestId","requestTitle":"requestTitle","requestStatus":"requestStatus", "requestType":"requestType", "pendingWith":"pendingWith", "requestOwner":"requestOwner", "priority":"priority","submittedDate":"submittedDate","resolutionDate":"resolutionDate","requesterName":"requesterName"},"availableFields":{"requestId":"Request ID","requestTitle":"Request Title","requestStatus":"Request Status","pendingWith":"Pending With","priority":"Priority","requestTemplate":"Request Template","requestType":"Request Type","requestOwner":"Primary Request Owner", "secondaryRequestOwner":" Secondary Request Owner", "subUnit":"Sub Unit","location":"Location","submittedDate":"Submitted Date","resolutionDate":"Resolution Date","requesterName":"Employee Name", "requesterEmpId":"Emp ID", "costCenter":"Cost Center"},"defaultFields":["requestId","requestTitle","requestType", "requestOwner", "requestStatus","requesterName","pendingWith","priority","submittedDate","resolutionDate"],"mandatoryFields":["requestId", "requestTitle","requestType"]}' WHERE `name` = 'requestDeskEmployeeGeneralRequisitionsList';
UPDATE ohrm_data_list SET `meta_data` = '{"listFields":{"requestId":"requestId","requestTitle":"requestTitle","requestStatus":"requestStatus", "requestType":"requestType", "requestOwner":"requestOwner", "priority":"priority","submittedDate":"submittedDate", "pendingWith":"pendingWith", "resolutionDate":"resolutionDate"},"availableFields":{"requestId":"Request ID","requestTitle":"Request Title","requestStatus":"Request Status","pendingWith":"Pending With","priority":"Priority","requestTemplate":"Request Template","requestType":"Request Type","requestOwner":"Primary Request Owner", "secondaryRequestOwner":" Secondary Request Owner","submittedDate":"Submitted Date","resolutionDate":"Resolution Date"},"defaultFields":["requestId","requestTitle","requestType","requestStatus", "requestOwner", "pendingWith","priority","submittedDate","resolutionDate"],"mandatoryFields":["requestId", "requestTitle","requestType"]}' WHERE `name` = 'requestDeskMyGeneralRequisitionsList';

INSERT INTO ohrm_requestdesk_request_type_template (`name`, `processor_class`, `template`, `approval_mandatory`, `status`) VALUES
('Employee Management', 'RequestDeskRequestProcessor','{
   "meta":{
      "terminalHandler":"",
      "TerminalActionDisplayName":"Update Information",
      "maximumApprovalLevels":5,
      "action":"webServiceCall",
      "webServiceUrl":"employees",
      "webServiceParam":"empNumber",
      "httpMethod":"PATCH",
      "redirectAfterSuccess":"pim.employees.profile.personal_details",
      "enableSubmitForOther":true
   },
   "formData":{
      "sections":[
         {
            "name":"Personal Details",
            "displayOrder":2,
            "isCustomizable":true,
            "isTemplate":true,
            "defaultFields":[
               {
                  "fieldType":"Text",
                  "name":"First Name",
                  "otherData":{
                     "length":30
                  },
                  "extraData":"",
                  "mandatory":true,
                  "deletable":false,
                  "editable":false,
                  "tokenName":"firstName",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getFirstName"
               },
               {
                  "fieldType":"Text",
                  "name":"Middle Name",
                  "otherData":{
                     "length":30
                  },
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"middleName",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getMiddleName"
               },
               {
                  "fieldType":"Text",
                  "name":"Last Name",
                  "otherData":{
                     "length":30
                  },
                  "extraData":"",
                  "mandatory":true,
                  "deletable":false,
                  "editable":false,
                  "tokenName":"lastName",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getLastName"
               },
               {
                  "fieldType":"Text",
                  "name":"Other Id",
                  "otherData":{
                     "length":50
                  },
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"otherId",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getOtherId"
               },
               {
                  "fieldType":"Text",
                  "name":"Main Id",
                  "otherData":{
                     "length":30
                  },
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"ssn",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getSsn"
               },
               {
                  "fieldType":"Date",
                  "name":"Date of Birth",
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"emp_birthday",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getEmpBirthday"
               },
               {
                  "fieldType":"Text",
                  "name":"Driving License Number",
                  "otherData":{
                     "length":30
                  },
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"licenseNo",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getLicenseNo"
               },
               {
                  "fieldType":"Date",
                  "name":"License Expiry Date",
                  "otherData":{
                     "length":10
                  },
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"emp_dri_lice_exp_date",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getEmpDriLiceExpDate"
               },
               {
                  "fieldType":"Advance Type",
                  "name":"Nationality",
                  "otherData":{
                     "entity":"Nationality",
                     "idField":"id",
                     "nameField":"name"
                  },
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "advancedTypeMap":"Generic",
                  "tokenName":"nation_code",
                  "tokenEntityName":"",
                  "tokenValueGetter":"generic"
               }
            ]
         },
         {
            "name":"Job",
            "displayOrder":3,
            "isCustomizable":true,
            "isTemplate":true,
            "defaultFields":[
               {
                  "fieldType":"Date",
                  "name":"Effective Date",
                  "extraData":"",
                  "mandatory":true,
                  "deletable":false,
                  "editable":false,
                  "tokenName":"effective_date",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getJobRecord,getEffectiveDate",
                  "otherData": {
                  	"hiddenField":true,
                  	"preventUpdate":true
                  }
               },
               {
                  "fieldType":"Advance Type",
                  "name":"Event",
                  "extraData":"",
                  "mandatory":true,
                  "deletable":false,
                  "editable":false,
                  "advancedTypeMap":"Generic",
                  "tokenName":"pim_history_event",
                  "tokenEntityName":"",
                  "tokenValueGetter":"generic",
                  "otherData":{
                  	"hiddenField":true,
                  	"preventUpdate":true,
                       "customQuery":"SELECT phe.id, phe.name FROM ohrm_pim_history_event phe INNER JOIN ohrm_pim_history_event_event_type phet ON phe.id = phet.event_id WHERE phet.type_id = 1;"
                  }
               },
               {
                  "fieldType":"Date",
                  "name":"Joined Date",
                  "extraData":"",
                  "mandatory":true,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"joined_date",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getJobRecord,getJoinedDate"
               },
               {
                  "fieldType":"Date",
                  "name":"Probation End Date",
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"probation_end_date",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getJobRecord,getProbationEndDate"
               },
               {
                  "fieldType":"Date",
                  "name":"Date of Permanency",
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"date_of_permanency",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getJobRecord,getDateOfPermanency"
               },
               {
                  "fieldType":"Advance Type",
                  "name":"Job Title",
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "advancedTypeMap":"Job Title",
                  "tokenName":"job_title_id",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getJobTitleName"
               },
               {
                  "fieldType":"Advance Type",
                  "name":"Sub Unit",
                  "extraData":"",
                  "mandatory":false,
                  "deletable":false,
                  "editable":false,
                  "advancedTypeMap":"Sub Unit",
                  "tokenName":"subunit_id",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getSubDivision,getName"
               },
               {
                  "fieldType":"Advance Type",
                  "name":"Location",
                  "extraData":"",
                  "mandatory":true,
                  "deletable":false,
                  "editable":false,
                  "advancedTypeMap":"Location",
                  "tokenName":"location_id",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getLocationAsString"
               },
               {
                  "fieldType":"Advance Type",
                  "name":"Employment Status",
                  "otherData":{
                     "entity":"EmploymentStatus",
                     "idField":"id",
                     "nameField":"name"
                  },
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "advancedTypeMap":"Generic",
                  "tokenName":"employment_status_id",
                  "tokenEntityName":"",
                  "tokenValueGetter":"generic"
               },
               {
                  "fieldType":"Advance Type",
                  "name":"Job Category",
                  "otherData":{
                     "entity":"JobCategory",
                     "idField":"id",
                     "nameField":"name"
                  },
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "advancedTypeMap":"Generic",
                  "tokenName":"job_category_id",
                  "tokenEntityName":"",
                  "tokenValueGetter":"generic"
               },
         	{
                  "fieldType":"Advance Type",
                  "name":"EEO Job Category",
                  "otherData":{
                     "entity":"EEOJobCategories",
                     "idField":"id",
                     "nameField":"name"
                  },
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "advancedTypeMap":"Generic",
                  "tokenName":"eeo_job_category",
                  "tokenEntityName":"",
                  "tokenValueGetter":"generic"
               },
               {
                  "fieldType":"Advance Type",
                  "name":"Cost Center",
                  "otherData":{
                     "entity":"CostCentre",
                     "idField":"id",
                     "nameField":"name"
                  },
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "advancedTypeMap":"Generic",
                  "tokenName":"cost_centre_id",
                  "tokenEntityName":"",
                  "tokenValueGetter":"generic"
               },
               {
                  "fieldType":"Advance Type",
                  "name":"Work Schedule",
                  "otherData":{
                     "entity":"WorkSchedule",
                     "idField":"id",
                     "nameField":"name"
                  },
                  "extraData":"",
                  "mandatory":true,
                  "deletable":false,
                  "editable":false,
                  "advancedTypeMap":"Generic",
                  "tokenName":"work_schedule_id",
                  "tokenEntityName":"",
                  "tokenValueGetter":"generic"
               },
             	{
                  "fieldType":"Text Area",
                  "name":"Comment",
                  "otherData":{
                     "length":250
                  },
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"comment",
                  "tokenEntityName":"",
                  "tokenValueGetter":"getComment"
               },
                {
                  "fieldType":"Checkbox",
                  "name":"FLSA Exempt",
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"flsa_exempt",
                  "tokenEntityName":"",
                  "tokenValueGetter":"getJobRecord,getFlsaExempt"
               },
               {
                  "fieldType":"Checkbox",
                  "name":"Include Employment Contract Details",
                  "extraData":"",
                  "otherData":{
                  	"isParentField": true
                  },
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"has_contract_details",
                  "tokenEntityName":"",
                  "tokenValueGetter":"getJobRecord,getHasContractDetails"
               },
               {
                  "fieldType":"Date",
                  "name":"Contract Start Date",
                  "extraData":"",
                  "otherData":{
                  	"displayCondition": {
		          "token":"has_contract_details",
		          "value": true,
		          "mandatory":false
                      }
                  },
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"contract_start_date",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getJobRecord,getContractStartDate"
               },
               {
                  "fieldType":"Date",
                  "name":"Contract End Date",
                  "extraData":"",
                  "otherData":{
		  	"displayCondition": {
			  "token":"has_contract_details",
			  "value":true,
			  "mandatory":false
		      }
                  },
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"contract_end_date",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getJobRecord,getContractEndDate"
               },
               {
                  "fieldType":"Attachment",
                  "name":"Contract Details",
                  "extraData":"",
                  "otherData":{
		  	"displayCondition": {
			  "token":"has_contract_details",
			  "value":true
		      }
                  },
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"contract_details",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getJobRecord,getContractDetails"
               }
            ]
         },
         {
            "name":"Salary",
            "displayOrder":4,
            "isCustomizable":true,
            "isTemplate":true,
            "defaultFields":[
               {
                  "fieldType":"Advance Type",
                  "name":"PayGrade",
                  "otherData":{
                     "entity":"PayGrade",
                     "idField":"id",
                     "nameField":"name"
                  },
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "advancedTypeMap":"Generic",
                  "tokenName":"pay_grade",
                  "tokenEntityName":"",
                  "tokenValueGetter":"generic"
               },
               {
                  "fieldType":"Advance Type",
                  "name":"Currency",
                  "otherData":{
                     "entity":"CurrencyType",
                     "idField":"currency_id",
                     "nameField":"currency_name"
                  },
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "advancedTypeMap":"Generic",
                  "tokenName":"currency_id",
                  "tokenEntityName":"",
                  "tokenValueGetter":"generic"
               }
            ]
         },
         {
            "name":"Salary Components",
            "displayOrder":5,
            "isCustomizable":true,
            "isTemplate":true,
            "defaultFields":[

            ]
         },
         {
            "name":"Contact Details",
            "displayOrder":5,
            "isCustomizable":true,
            "isTemplate":true,
            "defaultFields":[
               {
                  "fieldType":"Text",
                  "name":"Address Street 1",
                  "otherData":{
                     "length":70
                  },
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"street1",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getStreet1"
               },
               {
                  "fieldType":"Text",
                  "name":"Address Street 2",
                  "otherData":{
                     "length":70
                  },
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"street2",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getStreet2"
               },
               {
                  "fieldType":"Text",
                  "name":"City",
                  "otherData":{
                     "length":70
                  },
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"city",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getCity"
               },
               {
                  "fieldType":"Country",
                  "name":"Country",
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"country",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getCountryNameAsString"
               },
               {
                  "fieldType":"Text",
                  "name":"State/Province",
                  "otherData":{
                     "length":70
                  },
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"province",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getProvinceNameAsString"
               },
               {
                  "fieldType":"Text",
                  "name":"Zip/Postal Code",
                  "otherData":{
                     "length":10
                  },
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"emp_zipcode",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getEmpZipcode"
               },
               {
                  "fieldType":"Text",
                  "name":"Home Telephone",
                  "otherData":{
                     "length":25
                  },
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"emp_hm_telephone",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getEmpHmTelephone"
               },
               {
                  "fieldType":"Text",
                  "name":"Mobile",
                  "otherData":{
                     "length":25
                  },
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"emp_mobile",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getEmpMobile"
               },
               {
                  "fieldType":"Text",
                  "name":"Work Telephone",
                  "otherData":{
                     "length":25
                  },
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"emp_work_telephone",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getEmpWorkTelephone"
               },
               {
                  "fieldType":"Email",
                  "name":"Work Email",
                  "otherData":{
                     "length":50
                  },
                  "extraData":"",
                  "mandatory":false,
                  "deletable":false,
                  "editable":false,
                  "tokenName":"emp_work_email",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getEmpWorkEmail"
               },
               {
                  "fieldType":"Email",
                  "name":"Other Email",
                  "otherData":{
                     "length":50
                  },
                  "extraData":"",
                  "mandatory":false,
                  "deletable":true,
                  "editable":false,
                  "tokenName":"emp_oth_email",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getEmpOthEmail"
               }
            ]
         },
         {
            "name":"Report-to",
            "displayOrder":6,
            "isCustomizable":true,
            "isTemplate":true,
            "defaultFields":[
               {
                  "fieldType":"Advance Type",
                  "name":"Supervisor",
                  "extraData":"",
                  "mandatory":true,
                  "deletable":false,
                  "editable":false,
                  "advancedTypeMap":"Employee",
                  "tokenName":"employeeId",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"employeeId"
               },
               {
                  "fieldType":"Drop Down",
                  "name":"Reporting Method",
                  "extraData":"Direct,Indirect",
                  "mandatory":true,
                  "deletable":false,
                  "editable":false,
                  "tokenName":"reportingMethod",
                  "tokenEntityName":"Employee",
                  "tokenValueGetter":"getSupervisor,getReportingMethod,getName"
               }
            ]
         }
      ]
   }
}', 0, 1);


-- Add missing Admin workflows for requests without approve

INSERT INTO `ohrm_workflow_state_machine`(`workflow`, `state`, `role`, `action`, `resulting_state`) VALUES
                                                                                                        ('REQUEST_WITHOUT_APPROVE', 'In Progress', 'Admin', 'SAVE', 'In Progress'),
                                                                                                        ('REQUEST_WITHOUT_APPROVE','In Progress','Admin','REQUEST INFO','Request Info Required'),
                                                                                                        ('REQUEST_WITHOUT_APPROVE','Request Info Required','Admin','REQUEST INFO','Request Info Required');

SET @request_without_approve_workflow_id := 'REQUEST_WITHOUT_APPROVE';
SET @requester_id := (SELECT id FROM ohrm_user_role WHERE name = 'Requester');
SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');
SET @request_more_info_notification_id := (SELECT id FROM ohrm_notification WHERE `name` = '(General/Hiring) Request Desk: Request More Information');
SET @request_more_info_template_id := (SELECT id FROM ohrm_notification_subscriber_subscription_template WHERE `notification_id` = @request_more_info_notification_id);
SET @requester_subscriber_id := (SELECT id FROM `ohrm_notification_subscriber` WHERE `user_role_id` = @requester_id AND `media_id`= @email_media AND `type`= 'UserRole');

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @request_more_info_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @request_without_approve_workflow_id AND `action` = 'REQUEST INFO' AND `role` = 'Admin';

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) SELECT
                                                                                                         s.id, @requester_subscriber_id, @request_more_info_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;
