
SET @request_approver_id := (SELECT id FROM ohrm_user_role where name = 'RequestApprover');
SET @workflow_id := (SELECT id FROM `ohrm_workflow` WHERE name = 'requestTracker');

SET @approver_subscriber_id := (SELECT id from ohrm_notification_subscriber where user_role_id=@request_approver_id);

SET @notif_id := (SELECT id FROM `ohrm_notification` WHERE `name` LIKE 'Request Desk: Request Submission for Approval');


INSERT INTO `ohrm_notification_subscription`
    (notification_id, workflow_item_id) SELECT
    @notif_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @workflow_id AND `action` = 'Save' AND state='Submitted' AND role ='Requester';
SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
    (notification_id, workflow_item_id) SELECT
    @notif_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @workflow_id AND `action` = 'Save' AND state='Submitted' AND role ='Admin';
SET @last_subscription_id2 := LAST_INSERT_ID();

SET @last_template_id := (select id from ohrm_notification_subscriber_subscription_template where name='Help desk request submission notification sent to subscriber');

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

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