-- Categorizing the RD Email Notifications
update ohrm_notification set name = '(General) Request Desk: Non Approve Request Reopen the Request' where name = 'Request Desk: Non Approve Request Reopen the Request';
update ohrm_notification set name = '(General) Request Desk: Non Approve Request Request More Information' where name = 'Request Desk: Non Approve Request Request More Information';
update ohrm_notification set name = '(General) Request Desk: Non Approve Request Submit More Information' where name = 'Request Desk: Non Approve Request Submit More Information';
update ohrm_notification set name = '(General) Request Desk: Reopen the Request' where name = 'Request Desk: Reopen the Request';
update ohrm_notification set name = '(General) Request Desk: Request Submission Without Approvers' where name = 'Request Desk: Request Submission Without Approvers';
update ohrm_notification set name = '(General) Request Desk: Resolve Non Approve Request' where name = 'Request Desk: Resolve Non Approve Request';
update ohrm_notification set name = '(General) Request Desk: Withdraw the Non Approvable Request' where name = 'Request Desk: Withdraw the Non Approvable Request';

update ohrm_notification set name = '(General/Hiring) Request Desk: Request Adding Comment' where name = 'Request Desk: Request Adding Comment';
update ohrm_notification set name = '(General/Hiring) Request Desk: Request Approval' where name = 'Request Desk: Request Approval';
update ohrm_notification set name = '(General/Hiring) Request Desk: Request Approval Completed' where name = 'Request Desk: Request Approval Completed';
update ohrm_notification set name = '(General/Hiring) Request Desk: Request Editing Comment' where name = 'Request Desk: Request Editing Comment';
update ohrm_notification set name = '(General/Hiring) Request Desk: Request More Information' where name = 'Request Desk: Request More Information';
update ohrm_notification set name = '(General/Hiring) Request Desk: Request Rejection' where name = 'Request Desk: Request Rejection';
update ohrm_notification set name = '(General/Hiring) Request Desk: Request Submission' where name = 'Request Desk: Request Submission';
update ohrm_notification set name = '(General/Hiring) Request Desk: Resolve the Request' where name = 'Request Desk: Resolve the Request';
update ohrm_notification set name = '(General/Hiring) Request Desk: Submit More Information' where name = 'Request Desk: Submit More Information';
update ohrm_notification set name = '(General/Hiring) Request Desk: Withdraw the Request' where name = 'Request Desk: Withdraw the Request';

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