SET @estimate_request_workflow_id := 'ESTIMATE_REQUEST';
SET @claim_request_workflow_id := 'CLAIM_REQUEST';
SET @action_notification_type := (SELECT id FROM ohrm_notification_type WHERE `name` = 'Action Based');
SET @admin_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'Admin');
SET @supervisor_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'Supervisor');
SET @hod_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'HeadOfDepartment');
SET @ess_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'ESS');
SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');
SET @module_id := (SELECT id FROM ohrm_module where `name` = 'expense');

INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, user_role_id) VALUES
  (@email_media, NULL, 'UserRole', @hod_user_role);
SET @hod_user_role_subscriber := LAST_INSERT_ID();

SET @affected_employee_subscriber := (SELECT DISTINCT subscriber_id FROM ((SELECT * FROM ohrm_notification_subscriber_subscription
  LEFT JOIN ohrm_notification_subscriber
    ON ohrm_notification_subscriber_subscription.subscriber_id = ohrm_notification_subscriber.id
WHERE `subscription_id`
      IN (SELECT id FROM ohrm_notification_subscription WHERE `notification_id` IN (SELECT id
                                                                                    FROM ohrm_notification
                                                                                    WHERE `module_id` = @module_id))) AS temp) WHERE `name` = 'Affected Employee');

SET @admin_user_role_subscriber := (SELECT DISTINCT subscriber_id FROM ((SELECT * FROM ohrm_notification_subscriber_subscription
  LEFT JOIN ohrm_notification_subscriber
    ON ohrm_notification_subscriber_subscription.subscriber_id = ohrm_notification_subscriber.id
WHERE `subscription_id`
      IN (SELECT id FROM ohrm_notification_subscription WHERE `notification_id` IN (SELECT id
                                                                                    FROM ohrm_notification
                                                                                    WHERE `module_id` = @module_id))) AS temp) WHERE `user_role_id` = @admin_user_role);



SET @supervisor_user_role_subscriber := (SELECT DISTINCT subscriber_id FROM ((SELECT * FROM ohrm_notification_subscriber_subscription
  LEFT JOIN ohrm_notification_subscriber
    ON ohrm_notification_subscriber_subscription.subscriber_id = ohrm_notification_subscriber.id
WHERE `subscription_id`
      IN (SELECT id FROM ohrm_notification_subscription WHERE `notification_id` IN (SELECT id
                                                                                    FROM ohrm_notification
                                                                                    WHERE `module_id` = @module_id))) AS temp) WHERE `user_role_id` = @supervisor_user_role);

-- Action submit for Ess
SET @notification_id := (SELECT id FROM ohrm_notification where `name` = 'Travel Request Submission');

INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id)
  SELECT
    s.id,
    @hod_user_role_subscriber
  FROM ohrm_notification_subscription s
  WHERE s.id IN (SELECT id
                 FROM ohrm_notification_subscription
                 WHERE `notification_id` = @notification_id AND
                       `workflow_item_id` IN (SELECT id
                                              FROM ohrm_workflow_state_machine
                                              WHERE `workflow` = @estimate_request_workflow_id AND `action` = 'Submit' AND `role` = 'ESS USER'
                       )
  );

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    'HeadOfDepartment',
    'Travel Request Notification - %performerFullName% Submitted a Travel Request',
    'Hi %recipientFirstName%,

%performerFullName% has submitted a travel request for your approval.

You can use the below link to directly access the travel request.
Link: %loginUrl%

Thank you.

(This is a system generated email, please do not reply!)'
  FROM ohrm_notification_subscription s
  WHERE s.id IN (SELECT id
                 FROM ohrm_notification_subscription
                 WHERE `notification_id` = @notification_id AND
                       `workflow_item_id` IN (SELECT id
                                              FROM ohrm_workflow_state_machine
                                              WHERE `workflow` = @estimate_request_workflow_id AND `action` = 'Submit' AND `role` = 'ESS USER'
                       )
  );


-- Assign
SET @notification_id := (SELECT id FROM ohrm_notification where `name` = 'Travel Request Assignment');

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id)
  SELECT
    @notification_id,
    id
  FROM ohrm_workflow_state_machine
  WHERE `workflow` = @estimate_request_workflow_id AND `action` = 'Assign' AND `role` = 'HEADOFDEPARTMENT';

SET @last_subscription_id := LAST_INSERT_ID();

-- Supervisor assign
INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id)
  SELECT
    s.id,
    @affected_employee_subscriber
  FROM
    ohrm_notification_subscription s
  WHERE s.id >=
        @last_subscription_id;

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

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    NULL,
    'Travel Request Notification - %performerFullName% Has Assigned a Travel Request for %employeeName% and Pending Final Approval',
    'Hi %recipientFirstName%,

%performerFullName% has assigned a travel request for %employeeName% and pending final approval.

You can use the below link to directly access the travel request.
Link: %loginUrl%

Thank you,
You are getting this email because you are a subscriber.

(This is a system generated email, please do not reply!)' FROM ohrm_notification_subscription s
  WHERE s.id >=
        @last_subscription_id;

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    'Affected Employee',
    'Travel Request Notification - %performerFullName% Has Assigned a Travel Request for You',
    'Hi %recipientFirstName%,

%performerFullName% has assigned a travel request for you.

You can use the below link to directly access the travel request.
Link: %loginUrl%

Thank you.

(This is a system generated email, please do not reply!)' FROM ohrm_notification_subscription s
  WHERE s.id >=
        @last_subscription_id;

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    'Admin',
    'Travel Request Notification - %performerFullName% Has Assigned a Travel Request for %employeeName% and Pending Your Approval',
    'Hi %recipientFirstName%,

%performerFullName% has assigned a travel request for %employeeName% and pending your approval.

You can use the below link to directly access the travel request.
Link: %loginUrl%

Thank you.

(This is a system generated email, please do not reply!)' FROM ohrm_notification_subscription s
  WHERE s.id >=
        @last_subscription_id;


-- Admin assign
INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id)
SELECT
  s.id,
  @hod_user_role_subscriber
FROM ohrm_notification_subscription s
WHERE s.id IN (SELECT id
               FROM ohrm_notification_subscription
               WHERE `notification_id` = @notification_id AND
                     `workflow_item_id` IN (SELECT id
                                            FROM ohrm_workflow_state_machine
                                            WHERE `workflow` = @estimate_request_workflow_id AND `action` = 'Assign' AND `role` = 'ADMIN'
                     )
);

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    'HeadOfDepartment',
    'Travel Request Notification - %performerFullName% Has Assigned a Travel Request for %employeeName%',
    'Hi %recipientFirstName%,

%performerFullName% has assigned a travel request for %employeeName%.

You can use the below link to directly access the travel request.
Link: %loginUrl%

Thank you.

(This is a system generated email, please do not reply!)' FROM ohrm_notification_subscription s
  WHERE s.id IN (SELECT id
                 FROM ohrm_notification_subscription
                 WHERE `notification_id` = @notification_id AND
                       `workflow_item_id` IN (SELECT id
                                              FROM ohrm_workflow_state_machine
                                              WHERE `workflow` = @estimate_request_workflow_id AND `action` = 'Assign' AND `role` = 'ADMIN'
                       )
  );


-- approve estimate
SET @notification_id := (SELECT id FROM ohrm_notification where `name` = 'Travel Request Approval');

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id)
  SELECT
    @notification_id,
    id
  FROM ohrm_workflow_state_machine
  WHERE `workflow` = @estimate_request_workflow_id AND `action` = 'Approve' AND `role` = 'HEADOFDEPARTMENT';

SET @last_subscription_id := LAST_INSERT_ID();

-- Supervisor approve
INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id)
  SELECT
    s.id,
    @affected_employee_subscriber
  FROM
    ohrm_notification_subscription s
  WHERE s.id >=
        @last_subscription_id;

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

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    NULL,
    "Travel Request Notification - %employeeName%'s Travel Request Has Been Approved by %performerFullName% and Pending Final Approval",
    "Hi %recipientFirstName%,

%employeeName%'s travel request has been approved by %performerFullName% and pending final approval.

You can use the below link to directly access the travel request.
Link: %loginUrl%

Thank you,
You are getting this email because you are a subscriber.

(This is a system generated email, please do not reply!)" FROM ohrm_notification_subscription s
  WHERE s.id >=
        @last_subscription_id;

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    'Affected Employee',
    "Travel Request Notification - %performerFullName% Has Approved Your Travel Request",
    "Hi %recipientFirstName%,

Your travel request has been approved by %performerFullName% and pending final approval.

You can use the below link to directly access the travel request.
Link: %loginUrl%

Thank you.

(This is a system generated email, please do not reply!)" FROM ohrm_notification_subscription s
  WHERE s.id >=
        @last_subscription_id;

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    'Admin',
    "Travel Request Notification - %employeeName%'s Travel Request Has Been Approved by %performerFullName% and Pending Your Approval",
    "Hi %recipientFirstName%,

%employeeName%'s travel request has been approved by %performerFullName% and pending your aproval.

You can use the below link to directly access the travel request.
Link: %loginUrl%

Thank you.

(This is a system generated email, please do not reply!)" FROM ohrm_notification_subscription s
  WHERE s.id >=
        @last_subscription_id;


-- admin approval
INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id)
SELECT
  s.id,
  @hod_user_role_subscriber
FROM ohrm_notification_subscription s
WHERE s.id IN (SELECT id
               FROM ohrm_notification_subscription
               WHERE `notification_id` = @notification_id AND
                     `workflow_item_id` IN (SELECT id
                                            FROM ohrm_workflow_state_machine
                                            WHERE `workflow` = @estimate_request_workflow_id AND `action` = 'Approve' AND `role` = 'ADMIN'
                     )
);

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    'HeadOfDepartment',
    "Travel Request Notification - %performerFullName% Has Approved %employeeName%'s Travel Request",
    "Hi %recipientFirstName%,

%performerFullName% has approved %employeeName%'s travel request.

You can use the below link to directly access the travel request.
Link: %loginUrl%

Thank you.

(This is a system generated email, please do not reply!)" FROM ohrm_notification_subscription s
  WHERE s.id IN (SELECT id
                 FROM ohrm_notification_subscription
                 WHERE `notification_id` = @notification_id AND
                       `workflow_item_id` IN (SELECT id
                                              FROM ohrm_workflow_state_machine
                                              WHERE `workflow` = @estimate_request_workflow_id AND `action` = 'Approve' AND `role` = 'ADMIN'
                       )
  );

-- reject estimate
SET @notification_id := (SELECT id FROM ohrm_notification where `name` = 'Travel Request Rejection');

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id)
  SELECT
    @notification_id,
    id
  FROM ohrm_workflow_state_machine
  WHERE `workflow` = @estimate_request_workflow_id AND `action` = 'Reject' AND `role` = 'HEADOFDEPARTMENT';

SET @last_subscription_id := LAST_INSERT_ID();

-- supervisor reject
INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id)
  SELECT
    s.id,
    @affected_employee_subscriber
  FROM
    ohrm_notification_subscription s
  WHERE s.id >=
        @last_subscription_id;

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    NULL,
    "Travel Request Notification - %performerFullName% Has Rejected Travel Request of %employeeName%",
    "Hi %recipientFirstName%,

 %performerFullName% has rejected travel request of %employeeName%.

 You can use the below link to directly access the travel request.
 Link: %loginUrl%

 Thank you,
 You are getting this email because you are a subscriber.

 (This is a system generated email, please do not reply!)" FROM ohrm_notification_subscription s
  WHERE s.id >=
        @last_subscription_id;

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    'Affected Employee',
    "Travel Request Notification - %performerFullName% Has Rejected Your Travel Request",
    "Hi %recipientFirstName%,

 %performerFullName% has rejected your travel request. Please revisit the details and submit it again.

 You can use the below link to directly access the travel request.
 Link: %loginUrl%

 Thank you.

 (This is a system generated email, please do not reply!)" FROM ohrm_notification_subscription s
  WHERE s.id >=
        @last_subscription_id;


-- admin reject
INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id)
SELECT
  s.id,
  @hod_user_role_subscriber
FROM ohrm_notification_subscription s
WHERE s.id IN (SELECT id
               FROM ohrm_notification_subscription
               WHERE `notification_id` = @notification_id AND
                     `workflow_item_id` IN (SELECT id
                                            FROM ohrm_workflow_state_machine
                                            WHERE `workflow` = @estimate_request_workflow_id AND `action` = 'Reject' AND `role` = 'ADMIN'
                     )
);

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    'HeadOfDepartment',
    "Travel Request Notification - %performerFullName% Has Rejected Travel Request of %employeeName%",
    "Hi %recipientFirstName%,

%performerFullName% has rejected travel request of %employeeName%.

You can use the below link to directly access the travel request.
Link: %loginUrl%

Thank you.

(This is a system generated email, please do not reply!)" FROM ohrm_notification_subscription s
  WHERE s.id IN (SELECT id
                 FROM ohrm_notification_subscription
                 WHERE `notification_id` = @notification_id AND
                       `workflow_item_id` IN (SELECT id
                                              FROM ohrm_workflow_state_machine
                                              WHERE `workflow` = @estimate_request_workflow_id AND `action` = 'Reject' AND `role` = 'ADMIN'
                       )
  );

-- cancel travel request
SET @notification_id := (SELECT id FROM ohrm_notification where `name` = 'Travel Request Cancellation');





-- Claim Request Submit
SET @notification_id := (SELECT id FROM ohrm_notification where `name` = 'Claim Request Submission');

INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id)
  SELECT
    s.id,
    @hod_user_role_subscriber
  FROM ohrm_notification_subscription s
  WHERE s.id IN (SELECT id
                 FROM ohrm_notification_subscription
                 WHERE `notification_id` = @notification_id AND
                       `workflow_item_id` IN (SELECT id
                                              FROM ohrm_workflow_state_machine
                                              WHERE `workflow` = @claim_request_workflow_id AND `action` = 'Submit' AND `role` = 'ESS USER'
                       )
  );

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    'HeadOfDepartment',
    'Expense Claim Notification - %performerFullName% Has Submitted an Expense Claim',
    'Hi %recipientFirstName%,

%performerFullName% has submitted an expense claim for your approval.

You can use the below link to directly access the expense claim.
Link: %loginUrl%

Thank you.

(This is a system generated email, please do not reply!)' FROM ohrm_notification_subscription s
  WHERE s.id IN (SELECT id
                 FROM ohrm_notification_subscription
                 WHERE `notification_id` = @notification_id AND
                       `workflow_item_id` IN (SELECT id
                                              FROM ohrm_workflow_state_machine
                                              WHERE `workflow` = @claim_request_workflow_id AND `action` = 'Submit' AND `role` = 'ESS USER'
                       )
  );

-- Claim request assign
SET @notification_id := (SELECT id FROM ohrm_notification where `name` = 'Claim Request Assignment');

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id)
  SELECT
    @notification_id,
    id
  FROM ohrm_workflow_state_machine
  WHERE `workflow` = @claim_request_workflow_id AND `action` = 'Submit' AND `role` = 'HEADOFDEPARTMENT';

SET @last_subscription_id := LAST_INSERT_ID();

-- Supervisor assign
INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id)
  SELECT
    s.id,
    @affected_employee_subscriber
  FROM ohrm_notification_subscription s
  WHERE s.id >=
        @last_subscription_id;

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

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    NULL,
    'Expense Claim Notification - %performerFullName% Has Assigned an Expense Claim for %employeeName% and Pending Final Approval',
    'Hi %recipientFirstName%,

%performerFullName% has assigned an expense claim for %employeeName% and pending final approval.

You can use the below link to directly access the expense claim.
Link: %loginUrl%

Thank you,
You are getting this email because you are a subscriber.

(This is a system generated email, please do not reply!)' FROM ohrm_notification_subscription s
  WHERE s.id >=
        @last_subscription_id;

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    'Affected Employee',
    'Expense Claim Notification - %performerFullName% Has Assigned an Expense Claim for You',
    'Hi %recipientFirstName%,

%performerFullName% has assigned an expense claim for you.

You can use the below link to directly access the expense claim.
Link: %loginUrl%

Thank you.

(This is a system generated email, please do not reply!)' FROM ohrm_notification_subscription s
  WHERE s.id >=
        @last_subscription_id;

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    'Admin',
    'Expense Claim Notification - %performerFullName% Has Assigned an Expense Claim for %employeeName% and Pending Your Approval',
    'Hi %recipientFirstName%,

%performerFullName% has assigned an expense claim for %employeeName% and pending your approval.

You can use the below link to directly access the expense claim.
Link: %loginUrl%

Thank you.

(This is a system generated email, please do not reply!)' FROM ohrm_notification_subscription s
  WHERE s.id >=
        @last_subscription_id;


-- Admin assign
INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id)
SELECT
  s.id,
  @hod_user_role_subscriber
FROM ohrm_notification_subscription s
WHERE s.id IN (SELECT id
               FROM ohrm_notification_subscription
               WHERE `notification_id` = @notification_id AND
                     `workflow_item_id` IN (SELECT id
                                            FROM ohrm_workflow_state_machine
                                            WHERE `workflow` = @claim_request_workflow_id AND `action` = 'Submit' AND `role` = 'ADMIN'
                     )
);

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    'HeadOfDepartment',
    'Expense Claim Notification - %performerFullName% Has Assigned an Expense Claim for %employeeName%',
    'Hi %recipientFirstName%,

%performerFullName% has assigned an expense claim for %employeeName%.

You can use the below link to directly access the expense claim.
Link: %loginUrl%

Thank you.

(This is a system generated email, please do not reply!)' FROM ohrm_notification_subscription s
  WHERE s.id IN (SELECT id
                 FROM ohrm_notification_subscription
                 WHERE `notification_id` = @notification_id AND
                       `workflow_item_id` IN (SELECT id
                                              FROM ohrm_workflow_state_machine
                                              WHERE `workflow` = @claim_request_workflow_id AND `action` = 'Submit' AND `role` = 'ADMIN'
                       )
  );

-- approve claim
SET @notification_id := (SELECT id FROM ohrm_notification where `name` = 'Claim Request Approval');

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id)
  SELECT
    @notification_id,
    id
  FROM ohrm_workflow_state_machine
  WHERE `workflow` = @claim_request_workflow_id AND `action` = 'Approve' AND `role` = 'HEADOFDEPARTMENT';
SET @last_subscription_id := LAST_INSERT_ID();

-- Supervisor approve
INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id)
  SELECT
    s.id,
    @affected_employee_subscriber
  FROM
    ohrm_notification_subscription s
  WHERE s.id >=
        @last_subscription_id;

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

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    NULL,
    "Expense Claim Notification - %employeeName%'s Expense Claim Has Been Approved by %performerFullName% and Pending Final Approval",
    "Hi %recipientFirstName%,

%employeeName%'s expense claim has been approved by %performerFullName% and pending final approval.

You can use the below link to directly access the expense claim.
Link: %loginUrl%

Thank you,
You are getting this email because you are a subscriber.

(This is a system generated email, please do not reply!)" FROM ohrm_notification_subscription s
  WHERE s.id >=
        @last_subscription_id;

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    'Affected Employee',
    "Expense Claim Notification - %performerFullName% Has Approved Your Expense Claim",
    "Hi %recipientFirstName%,

Your expense claim has been approved by %performerFullName% and pending final approval.

You can use the below link to directly access the expense claim.
Link: %loginUrl%

Thank you.

(This is a system generated email, please do not reply!)" FROM ohrm_notification_subscription s
  WHERE s.id >=
        @last_subscription_id;

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    'Admin',
    "Expense Claim Notification - %employeeName%'s Expense Claim Has Been Approved by %performerFullName% and Pending Your Approval",
    "Hi %recipientFirstName%,

%employeeName%'s expense claim has been approved by %performerFullName% and pending your approval.

You can use the below link to directly access the expense claim.
Link: %loginUrl%

Thank you.

(This is a system generated email, please do not reply!)" FROM ohrm_notification_subscription s
  WHERE s.id >=
        @last_subscription_id;




-- Admin approve
INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id)
SELECT
  s.id,
  @hod_user_role_subscriber
FROM ohrm_notification_subscription s
WHERE s.id IN (SELECT id
               FROM ohrm_notification_subscription
               WHERE `notification_id` = @notification_id AND
                     `workflow_item_id` IN (SELECT id
                                            FROM ohrm_workflow_state_machine
                                            WHERE `workflow` = @claim_request_workflow_id AND `action` = 'Approve' AND `role` = 'ADMIN'
                     )
);

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    'HeadOfDepartment',
    "Expense Claim Notification - %performerFullName% Has Approved %employeeName%'s Expense Claim",
    "Hi %recipientFirstName%,

%performerFullName% has approved expense claim of %employeeName%.

You can use the below link to directly access the expense claim.
Link: %loginUrl%

Thank you.

(This is a system generated email, please do not reply!)" FROM ohrm_notification_subscription s
  WHERE s.id IN (SELECT id
                 FROM ohrm_notification_subscription
                 WHERE `notification_id` = @notification_id AND
                       `workflow_item_id` IN (SELECT id
                                              FROM ohrm_workflow_state_machine
                                              WHERE `workflow` = @claim_request_workflow_id AND `action` = 'Approve' AND `role` = 'ADMIN'
                       )
  );

-- reject claim
SET @notification_id := (SELECT id FROM ohrm_notification where `name` = 'Claim Request Rejection');

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id)
  SELECT
    @notification_id,
    id
  FROM ohrm_workflow_state_machine
  WHERE `workflow` = @claim_request_workflow_id AND `action` = 'Reject' AND `role` = 'HEADOFDEPARTMENT';
SET @last_subscription_id := LAST_INSERT_ID();


-- Supervisor reject
INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id)
  SELECT
    s.id,
    @affected_employee_subscriber
  FROM ohrm_notification_subscription s
  WHERE s.id >=
        @last_subscription_id;

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    NULL,
    "Expense Claim Notification - %performerFullName% Has Rejected Expense Claim of %employeeName%",
    "Hi %recipientFirstName%,

%performerFullName% has rejected expense claim of %employeeName%.

You can use the below link to directly access the expense claim.
Link: %loginUrl%

Thank you,
You are getting this email because you are a subscriber.

(This is a system generated email, please do not reply!)" FROM ohrm_notification_subscription s
  WHERE s.id >=
        @last_subscription_id;

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    'Affected Employee',
    "Expense Claim Notification - %performerFullName% Has Rejected Your Expense Claim",
    "Hi %recipientFirstName%,

%performerFullName% has rejected your expense claim. Please revisit the details and submit it again.

You can use the below link to directly access the expense claim.
Link: %loginUrl%

Thank you.

(This is a system generated email, please do not reply!)" FROM ohrm_notification_subscription s
  WHERE s.id >=
        @last_subscription_id;


-- Admin reject
INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id)
SELECT
  s.id,
  @hod_user_role_subscriber
FROM ohrm_notification_subscription s
WHERE s.id IN (SELECT id
               FROM ohrm_notification_subscription
               WHERE `notification_id` = @notification_id AND
                     `workflow_item_id` IN (SELECT id
                                            FROM ohrm_workflow_state_machine
                                            WHERE `workflow` = @claim_request_workflow_id AND `action` = 'Reject' AND `role` = 'ADMIN'
                     )
);

INSERT INTO `ohrm_notification_subscription_template` (`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)
  SELECT
    s.id,
    @email_media,
    'en_US',
    NULL,
    'HeadOfDepartment',
    "Expense Claim Notification - %performerFullName% Has Rejected Expense Claim of %employeeName%",
    "Hi %recipientFirstName%,

%performerFullName% has rejected expense claim of %employeeName%.

You can use the below link to directly access the expense claim.
Link: %loginUrl%

Thank you.

(This is a system generated email, please do not reply!)" FROM ohrm_notification_subscription s
  WHERE s.id IN (SELECT id
                 FROM ohrm_notification_subscription
                 WHERE `notification_id` = @notification_id AND
                       `workflow_item_id` IN (SELECT id
                                              FROM ohrm_workflow_state_machine
                                              WHERE `workflow` = @claim_request_workflow_id AND `action` = 'Reject' AND `role` = 'ADMIN'
                       )
  );
