SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');
SET @action_notification_type := (SELECT id FROM ohrm_notification_type WHERE `name`='Action Based');
SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE name = 'Request Desk');
SET @requester_id := (SELECT id FROM ohrm_user_role where name = 'Requester');
SET @request_owner_id := (SELECT id FROM ohrm_user_role where name = 'RequestOwner');
SET @request_approver_id := (SELECT id FROM ohrm_user_role where name = 'RequestApprover');
SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE name = 'Request Desk');

SET @workflow_id := 'REQUEST';
SET @non_approve_workflow_id := 'REQUEST_WITHOUT_APPROVE';
SET @request_approver_actor_workflow_id := 'REQUEST_PROCESS_ACTOR_APPROVER';
SET @request_approve_process_workflow_id := 'REQUEST_PROCESS_APPROVE';

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @requester_id);
SET @requester_subscriber_id := LAST_INSERT_ID();
INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @request_approver_id);
SET @approver_subscriber_id := LAST_INSERT_ID();
INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @request_owner_id);
SET @owner_subscriber_id := LAST_INSERT_ID();
INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, name) VALUES
    (@email_media, NULL, 'Custom', 'RequestNextApprover');
SET @next_approver_subscriber_id := LAST_INSERT_ID();
INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, name) VALUES
    (@email_media, NULL, 'Custom', 'CommentViewer');
SET @comment_viewer_subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
    ('(General/Hiring) Request Desk: Request Submission', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
    '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["RequestApprover"]}', @workflow_id, 'SUBMIT', @module_id);
SET @submit_request_notification_id := (SELECT id FROM ohrm_notification WHERE `name` = '(General/Hiring) Request Desk: Request Submission');

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
(@submit_request_notification_id, @email_media);

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@submit_request_notification_id, @email_media, 'en_US', 'Help desk request submission notification sent to owner',
'New %template% Request - %requestType% : %title%',
'Hi %recipientFullName%,

New %template% Request has been submitted. Please refer to the details below.

Request ID: %id%
Request Title: %title%
Description: %description%
Requestor: %requester%
Request Owner: %requestOwner%
Resolution Date: %resolutionDate%

%extraInformation%

Please login to system, for more details.
Link: %link%

Thank you.

This is an automated notification.');

SET @request_submit_owners_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
    (notification_id, workflow_item_id) SELECT
    @submit_request_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @workflow_id AND `action` = 'SUBMIT' AND state IN ('Saved');
SET @last_subscription_id := LAST_INSERT_ID();

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


-- request submission notification to approvers
INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@submit_request_notification_id, @email_media, 'en_US', 'Help desk request submission notification sent to approver',
 'New %template% Request - %requestType% : %title%',
 'Hi %recipientFullName%,

 This is to notify that you have been included as the approver for the following hiring requisition. Please refer to the details below.

 Request ID: %id%
 Request Title: %title%
 Description: %description%
 Requestor: %requester%
 Request Owner: %requestOwner%
 Resolution Date: %resolutionDate%

 %extraInformation%

 Please login to system, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

SET @request_submit_initial_approver_template_id := LAST_INSERT_ID();
INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) SELECT
                                                                                                         s.id, @approver_subscriber_id, @request_submit_initial_approver_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- Requester Submit Requests Without Approvers

INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('(General) Request Desk: Request Submission Without Approvers', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["RequestOwner"]}', @non_approve_workflow_id, 'SUBMIT', @module_id);
SET @submit_request_without_approve_notification_id := (SELECT id FROM ohrm_notification WHERE `name` = '(General) Request Desk: Request Submission Without Approvers');

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
(@submit_request_without_approve_notification_id, @email_media);

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@submit_request_without_approve_notification_id, @email_media, 'en_US', 'Help desk request submission without approve notification sent to owner',
 'New %template% Request - %requestType% : %title%',
 'Hi %recipientFullName%,

 New %template% Request has been submitted. Please refer to the details below.

 Request ID: %id%
 Request Title: %title%
 Description: %description%
 Requestor: %requester%
 Request Owner: %requestOwner%
 Resolution Date: %resolutionDate%

 %extraInformation%

 Please login to system, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

SET @request_without_approver_submit_owners_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @submit_request_without_approve_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @non_approve_workflow_id AND `action` = 'SUBMIT' AND state IN ('Saved');
SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) SELECT
                                                                                                         s.id, @owner_subscriber_id, @request_without_approver_submit_owners_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;
-- request approved
INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
    ('(General/Hiring) Request Desk: Request Approval', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
    '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["RequestApprover", "Requester"]}', @request_approver_actor_workflow_id, 'APPROVE', @module_id);
SET @request_approved_notification_id := (SELECT id FROM ohrm_notification WHERE `name` = '(General/Hiring) Request Desk: Request Approval');
INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
(@request_approved_notification_id, @email_media);

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@request_approved_notification_id, @email_media, 'en_US', 'Help desk request approval notification sent to subscriber', 'New %template% Request - %requestType% : %title% Approved',
'Hi %recipientFullName%,

Your request has been approved by %approver% and currently In progress. Please refer to the details below.

Request ID: %id%
Request Title: %title%
Description: %description%
Requestor: %requester%
Request Owner: %requestOwner%
Resolution Date: %resolutionDate%

%extraInformation%

Please login to system, for more details.
Link: %link%

Thank you.

This is an automated notification.');

SET @request_approved_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
    (notification_id, workflow_item_id) SELECT
    @request_approved_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @request_approver_actor_workflow_id AND `action` = 'APPROVE' ;
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_approved_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- admin approval
INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @request_approved_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @workflow_id AND `action` = 'APPROVE' ;
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_approved_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;


-- request approved notification to next level approver
INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@request_approved_notification_id, @email_media, 'en_US', 'Help desk request approval notification sent to approver',
 'New %template% Request - %requestType% : %title%',
 'Hi %recipientFullName%,

 This is to notify that you have been included as the approver for the following hiring requisition. Please refer to the details below.

 Request ID: %id%
 Request Title: %title%
 Description: %description%
 Requestor: %requester%
 Request Owner: %requestOwner%
 Resolution Date: %resolutionDate%

 %extraInformation%

 Please login to system, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

SET @request_approved_next_approver_notify_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
@request_approved_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @request_approver_actor_workflow_id AND `action` = 'APPROVE' ;
SET @last_subscription_id := LAST_INSERT_ID();

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

-- admin approval to next level approver
INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @request_approved_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @workflow_id AND `action` = 'APPROVE' ;
SET @last_subscription_id := LAST_INSERT_ID();

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


-- request approval finished notification to owner
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('(General/Hiring) Request Desk: Request Approval Completed', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["RequestOwner"]}', @request_approve_process_workflow_id, 'COMPLETE APPROVAL', @module_id);
SET @request_approval_finished_notification_id := (SELECT id FROM ohrm_notification WHERE `name` = '(General/Hiring) Request Desk: Request Approval Completed');
INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
(@request_approval_finished_notification_id, @email_media);

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@request_approval_finished_notification_id, @email_media, 'en_US', 'Help desk request approval completion notification sent to subscriber', 'New %template% Request - %requestType% : %title% Approved',
 'Hi %recipientFullName%,

 You have a request pending your action. Please refer to the details below.

 Request ID: %id%
 Request Title: %title%
 Description: %description%
 Requestor: %requester%
 Request Owner: %requestOwner%
 Resolution Date: %resolutionDate%

 %extraInformation%

 Please login to system, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

SET @request_approval_completed_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @request_approval_finished_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @request_approve_process_workflow_id AND `action` = 'COMPLETE APPROVAL' ;
SET @last_subscription_id := LAST_INSERT_ID();

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


-- request rejection
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('(General/Hiring) Request Desk: Request Rejection', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["RequestApprover", "Requester"]}', @request_approver_actor_workflow_id, 'REJECT', @module_id);
SET @request_rejected_notification_id := (SELECT id FROM ohrm_notification WHERE `name` = '(General/Hiring) Request Desk: Request Rejection');
INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
(@request_rejected_notification_id, @email_media);

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@request_rejected_notification_id, @email_media, 'en_US', 'Help desk request rejection notification sent to subscriber', 'New %template% Request - %requestType% : %title% Rejected',
 'Hi %recipientFullName%,

 Your request has been rejected by %approver% . Please refer to the details below.

 Request ID: %id%
 Request Title: %title%
 Description: %description%
 Requestor: %requester%
 Request Owner: %requestOwner%
 Resolution Date: %resolutionDate%

 %extraInformation%

 Please login to system, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

SET @request_rejected_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
@request_rejected_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @request_approver_actor_workflow_id AND `action` = 'REJECT' ;
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_rejected_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- admin rejection
INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @request_rejected_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @workflow_id AND `action` = 'REJECT' ;
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_rejected_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;


-- request more information
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('(General/Hiring) Request Desk: Request More Information', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["Requester"]}', @workflow_id, 'REQUEST INFO', @module_id);
SET @request_more_info_notification_id := (SELECT id FROM ohrm_notification WHERE `name` = '(General/Hiring) Request Desk: Request More Information');
INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
(@request_more_info_notification_id, @email_media);

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@request_more_info_notification_id, @email_media, 'en_US', 'Help desk request more info notification sent to subscriber', 'New %template% Request - %requestType% : %title% - Requester information required',
 'Hi %recipientFullName%,

 Information is required about the request. Please refer to the details below.

 Requested Information: %requestedInfo%

 Request ID: %id%
 Request Title: %title%
 Description: %description%
 Requestor: %requester%
 Request Owner: %requestOwner%
 Resolution Date: %resolutionDate%

 %extraInformation%

 Please login to system, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

SET @request_more_info_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
@request_more_info_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @workflow_id AND `action` = 'REQUEST INFO' ;
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;

-- request more information from non approvable request
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('(General) Request Desk: Non Approve Request Request More Information', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["Requester"]}', @non_approve_workflow_id, 'REQUEST INFO', @module_id);
SET @non_approve_request_request_more_info_notification_id := (SELECT id FROM ohrm_notification WHERE `name` = '(General) Request Desk: Non Approve Request Request More Information');
INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
(@non_approve_request_request_more_info_notification_id, @email_media);

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@non_approve_request_request_more_info_notification_id, @email_media, 'en_US', 'Help desk request  non approvable request more info notification sent to subscriber', 'New %template% Request - %requestType% : %title% - Requester information required',
 'Hi %recipientFullName%,

 Information is required about the request. Please refer to the details below.

 Requested Information: %requestedInfo%

 Request ID: %id%
 Request Title: %title%
 Description: %description%
 Requestor: %requester%
 Request Owner: %requestOwner%
 Resolution Date: %resolutionDate%

 %extraInformation%

 Please login to system, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

SET @non_approve_request_request_more_info_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @non_approve_request_request_more_info_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @non_approve_workflow_id AND `action` = 'REQUEST INFO' ;
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, @non_approve_request_request_more_info_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- resubmit request with more information
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('(General/Hiring) Request Desk: Submit More Information', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["Requester"]}', @workflow_id, 'SUBMIT', @module_id);
SET @request_submit_more_info_notification_id := (SELECT id FROM ohrm_notification WHERE `name` = '(General/Hiring) Request Desk: Submit More Information');
INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
(@request_submit_more_info_notification_id, @email_media);

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@request_submit_more_info_notification_id, @email_media, 'en_US', 'Help desk request resubmit with more info notification sent to subscriber', 'New %template% Request - %requestType% : %title% - Request Resubmitted',
 'Hi %recipientFullName%,

 Requested Information has been provided by the user. Please refer to the details below.

 Request ID: %id%
 Request Title: %title%
 Description: %description%
 Requestor: %requester%
 Request Owner: %requestOwner%
 Resolution Date: %resolutionDate%

 %extraInformation%

 Please login to system, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

SET @request_submit_more_info_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
@request_submit_more_info_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @workflow_id AND `action` = 'SUBMIT' AND state = 'Request Info Required' AND role='Requester';
SET @last_subscription_id := LAST_INSERT_ID();

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


INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @request_submit_more_info_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @workflow_id AND `action` = 'SUBMIT' AND state = 'Request Info Required' 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, @owner_subscriber_id, @request_submit_more_info_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;


-- resubmit non approvable request with more information
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('(General) Request Desk: Non Approve Request Submit More Information', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["Requester"]}', @non_approve_workflow_id, 'SUBMIT', @module_id);
SET @non_approve_request_submit_more_info_notification_id := (SELECT id FROM ohrm_notification WHERE `name` = '(General) Request Desk: Non Approve Request Submit More Information');
INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
(@non_approve_request_submit_more_info_notification_id, @email_media);

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@non_approve_request_submit_more_info_notification_id, @email_media, 'en_US', 'Help desk request non approve request resubmit with more info notification sent to subscriber', 'New %template% Request - %requestType% : %title% - Request Resubmitted',
 'Hi %recipientFullName%,

 Requested Information has been provided by the user. Please refer to the details below.

 Request ID: %id%
 Request Title: %title%
 Description: %description%
 Requestor: %requester%
 Request Owner: %requestOwner%
 Resolution Date: %resolutionDate%

 %extraInformation%

 Please login to system, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

SET @non_approve_request_submit_more_info_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @non_approve_request_submit_more_info_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @non_approve_workflow_id AND `action` = 'SUBMIT' AND state = 'Request Info Required' 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, @owner_subscriber_id, @non_approve_request_submit_more_info_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;


INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @non_approve_request_submit_more_info_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @non_approve_workflow_id AND `action` = 'SUBMIT' AND state = 'Request Info Required' AND role='Requester';
SET @last_subscription_id := LAST_INSERT_ID();

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


-- request resolved
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('(General/Hiring) Request Desk: Resolve the Request', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["Requester"]}', @workflow_id, 'RESOLVE', @module_id);
SET @request_resolve_notification_id := (SELECT id FROM ohrm_notification WHERE `name` = '(General/Hiring) Request Desk: Resolve the Request');
INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
(@request_resolve_notification_id, @email_media);

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@request_resolve_notification_id, @email_media, 'en_US', 'Help desk request resolve notification sent to subscriber', 'New %template% Request - %requestType% : %title% - %terminalAction%',
 'Hi %recipientFullName%,

%resolvedStartOfEmailDetail%

Request ID: %id%
Request Title: %title%
Description: %description%
Requestor: %requester%
Request Owner: %requestOwner%
Resolution Date: %resolutionDate%

%extraInformation%

Please login to system, for more details.
Link: %link%

Thank you.

This is an automated notification.');

SET @request_resolve_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
@request_resolve_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @workflow_id AND `action` = 'RESOLVE';
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_resolve_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- resolve non approvable requests
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('(General) Request Desk: Resolve Non Approve Request', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["Requester"]}', @non_approve_workflow_id, 'RESOLVE', @module_id);
SET @non_approve_request_resolve_notification_id := (SELECT id FROM ohrm_notification WHERE `name` = '(General) Request Desk: Resolve Non Approve Request');
INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
(@non_approve_request_resolve_notification_id, @email_media);

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@non_approve_request_resolve_notification_id, @email_media, 'en_US', 'Help desk request non approve request resolve notification sent to subscriber', 'New %template% Request - %requestType% : %title% - %terminalAction%',
 'Hi %recipientFullName%,

%resolvedStartOfEmailDetail%

Request ID: %id%
Request Title: %title%
Description: %description%
Requestor: %requester%
Request Owner: %requestOwner%
Resolution Date: %resolutionDate%

%extraInformation%

Please login to system, for more details.
Link: %link%

Thank you.

This is an automated notification.');

SET @non_approve_request_resolve_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @non_approve_request_resolve_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @non_approve_workflow_id AND `action` = 'RESOLVE';
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, @non_approve_request_resolve_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;


-- request reopen
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('(General) Request Desk: Reopen the Request', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["Requester"]}', @workflow_id, 'REOPEN', @module_id);
SET @request_reopen_notification_id := (SELECT id FROM ohrm_notification WHERE `name` = '(General) Request Desk: Reopen the Request');
INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
(@request_reopen_notification_id, @email_media);

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@request_reopen_notification_id, @email_media, 'en_US', 'Help desk request reopen notification sent to subscriber', 'New %template% Request - %requestType% : %title% - Reopened',
 'Hi %recipientFullName%,

 Request has been reopened. Please refer to the details below.

 Request ID: %id%
 Request Title: %title%
 Description: %description%
 Requestor: %requester%
 Request Owner: %requestOwner%
 Resolution Date: %resolutionDate%

 %extraInformation%

 Please login to system, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

SET @request_reopen_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
@request_reopen_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @workflow_id AND `action` = 'REOPEN';
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_reopen_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

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

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


-- non approve request reopen
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('(General) Request Desk: Non Approve Request Reopen the Request', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["Requester"]}', @non_approve_workflow_id, 'REOPEN', @module_id);
SET @non_approve_request_reopen_notification_id := (SELECT id FROM ohrm_notification WHERE `name` = '(General) Request Desk: Non Approve Request Reopen the Request');
INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
(@non_approve_request_reopen_notification_id, @email_media);

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@non_approve_request_reopen_notification_id, @email_media, 'en_US', 'Help desk request non approve request reopen notification sent to subscriber', 'New %template% Request - %requestType% : %title% - Reopened',
 'Hi %recipientFullName%,

 Request has been reopened. Please refer to the details below.

 Request ID: %id%
 Request Title: %title%
 Description: %description%
 Requestor: %requester%
 Request Owner: %requestOwner%
 Resolution Date: %resolutionDate%

 %extraInformation%

 Please login to system, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

SET @non_approve_request_reopen_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @non_approve_request_reopen_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @non_approve_workflow_id AND `action` = 'REOPEN';
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, @non_approve_request_reopen_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

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

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

-- adding comment
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('(General/Hiring) Request Desk: Request Adding Comment', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["RequestOwner", "RequestApprover", "Requester"]}', @workflow_id, 'Add Comment', @module_id);
SET @request_add_comment_notification_id := (SELECT id FROM ohrm_notification WHERE `name` = '(General/Hiring) Request Desk: Request Adding Comment');
INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
(@request_add_comment_notification_id, @email_media);

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@request_add_comment_notification_id, @email_media, 'en_US', 'Help desk request add comment notification sent to subscriber', 'New %template% Request - %requestType% : %title% - Comment Added',
 'Hi %recipientFullName%,

Comment was added to the request by %actionOwner%. Please refer to the details below.

Comment: %comment%

Request ID: %id%
Request Title: %title%
Description: %description%
Requestor: %requester%
Request Owner: %requestOwner%
Resolution Date: %resolutionDate%

%extraInformation%

Please login to system, for more details.
Link: %link%

Thank you.

This is an automated notification.');

SET @request_add_comment_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
@request_add_comment_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @workflow_id AND `action` = 'Add Comment';
SET @last_subscription_id := LAST_INSERT_ID();

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

-- modifying comment
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('(General/Hiring) Request Desk: Request Editing Comment', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["RequestOwner", "RequestApprover", "Requester"]}', @workflow_id, 'Edit Comment', @module_id);
SET @request_edit_comment_notification_id := (SELECT id FROM ohrm_notification WHERE `name` = '(General/Hiring) Request Desk: Request Editing Comment');
INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
(@request_edit_comment_notification_id, @email_media);

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@request_edit_comment_notification_id, @email_media, 'en_US', 'Help desk request edit comment notification sent to subscriber', 'New %template% Request - %requestType% : %title% - Comment Updated',
 'Hi %recipientFullName%,

Comment was updated by %actionOwner%. Please refer to the details below.

New Comment: %newComment%
Old Comment: %prevComment%

Request ID: %id%
Request Title: %title%
Description: %description%
Requestor: %requester%
Request Owner: %requestOwner%
Resolution Date: %resolutionDate%

%extraInformation%

Please login to system, for more details.
Link: %link%

Thank you.

This is an automated notification.');

SET @request_edit_comment_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
@request_edit_comment_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @workflow_id AND `action` = 'Edit Comment';
SET @last_subscription_id := LAST_INSERT_ID();

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

-- withdraw request
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('(General/Hiring) Request Desk: Withdraw the Request', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["RequestOwner", "RequestApprover", "Requester"]}', @workflow_id, 'WITHDRAW', @module_id);
SET @request_withdraw_notification_id := (SELECT id FROM ohrm_notification WHERE `name` = '(General/Hiring) Request Desk: Withdraw the Request');
INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
(@request_withdraw_notification_id, @email_media);

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@request_withdraw_notification_id, @email_media, 'en_US', 'Help desk request withdraw notification sent to subscriber', 'New %template% Request - %requestType% : %title% Withdrawn',
 'Hi %recipientFullName%,

 New %template% Request has been withdrawn. Please refer to the details below.

 Request ID: %id%
 Request Title: %title%
 Description: %description%
 Requestor: %requester%
 Request Owner: %requestOwner%
 Resolution Date: %resolutionDate%

 %extraInformation%

 Please login to system, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

SET @request_withdraw_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
@request_withdraw_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @workflow_id AND `action` = 'WITHDRAW';
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_withdraw_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

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

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


-- non approve request withdraw
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('(General) Request Desk: Withdraw the Non Approvable Request', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["RequestOwner", "RequestApprover", "Requester"]}', @non_approve_workflow_id, 'WITHDRAW', @module_id);
SET @non_approve_request_withdraw_notification_id := (SELECT id FROM ohrm_notification WHERE `name` = '(General) Request Desk: Withdraw the Non Approvable Request');
INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
(@non_approve_request_withdraw_notification_id, @email_media);

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@non_approve_request_withdraw_notification_id, @email_media, 'en_US', 'Help desk request non approve request withdraw notification sent to subscriber', 'New %template% Request - %requestType% : %title% Withdrawn',
 'Hi %recipientFullName%,

 New %template% Request has been withdrawn. Please refer to the details below.

 Request ID: %id%
 Request Title: %title%
 Description: %description%
 Requestor: %requester%
 Request Owner: %requestOwner%
 Resolution Date: %resolutionDate%

 %extraInformation%

 Please login to system, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

SET @non_approve_request_withdraw_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @non_approve_request_withdraw_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @non_approve_workflow_id AND `action` = 'WITHDRAW';
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, @non_approve_request_withdraw_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

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

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) SELECT
                                                                                                         s.id, @owner_subscriber_id, @non_approve_request_withdraw_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;
SET @other_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Other');
SET @request_desk_module := (SELECT `id` FROM `ohrm_module` WHERE  `name` ='Request Desk');
SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');

INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`, `hidden`) VALUES
    ('Request Desk Document Notification', 1, @other_notification_type, 'RequestDeskDocumentEmailProcessor',
     '{"data_group_permissions":{"data_group":"request_desk_form_types","roles_to_exclude":""},"list_action_rights":{"change_status":false,"change_subscribers":true,"copy_templates":true},"extra_roles":[]}', null, null,@request_desk_module, 1);

SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
    (@notification_id, @email_media);

-- Single Notification to be sent to 1st approver when RD request submitted
SET @notification_id := (SELECT id FROM `ohrm_notification` WHERE `name` = '(General/Hiring) Request Desk: Request Submission');
SET @subscription_id := (SELECT id FROM `ohrm_notification_subscription` WHERE `notification_id` = @notification_id);
SET @request_approver_id := (SELECT id FROM ohrm_user_role WHERE name = 'RequestApprover');
SET @subscriber_id :=(SELECT id FROM `ohrm_notification_subscriber` WHERE `name`= 'RequestNextApprover');
SET @approver_subscriber_id :=(SELECT id FROM `ohrm_notification_subscriber` WHERE `user_role_id`= @request_approver_id limit 1);
SET @template_id := (SELECT id FROM `ohrm_notification_subscriber_subscription_template` WHERE `name` LIKE 'Help desk request submission notification sent to approver');

DELETE FROM ohrm_notification_subscriber_subscription where subscriber_id = @approver_subscriber_id and template_id=@template_id;

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) values(@subscription_id,@subscriber_id,@template_id);