SET @notification_id := (SELECT `id` FROM `ohrm_notification` WHERE `name`='Employee Welcome Email');

UPDATE `ohrm_notification_subscriber_subscription_template`
SET `body` ='Dear %subscriberName%,<br><br>

We are excited to introduce our newest team member, %employeeFullName%, who will be joining us as a %jobTitle% under the %subUnit% department. We believe %employeeFullName% will be a great addition to our family and help us achieve our goals more efficiently.<br><br>
<b>Brief Overview:</b>%empPicture%
<br><ul><li><b>Employee ID:</b> %employeeId%</li><li><b>Full Name:</b> %employeeFullName%</li><li><b>Job Title:</b> %jobTitle%</li><li><b>Job Category:</b> %jobCategory%</li><li><b>Supervisor:</b> %supervisorNames%</li><li><b>Cost Center:</b> %costCenter%</li><li><b>Location:</b> %location%</li><li><b>Work Email:</b> %workEmail%</li><li><b>Work Telephone:</b> %workTelephone%</li><li><b>Mobile:</b> %mobile%</li><li><b>Gender:</b> %gender%</li><li><b>EEO Race:</b> %eeoRace%</li><li><b>Previous Work Experience:</b> %workExperience%</li></ul><br>
Please join me in extending a warm welcome to %employeeFullName%. Let’s all work together to support and assist our new colleague in understanding our processes and integrating smoothly into our team.<br><br>
Feel free to reach out to %employeeFullName% via the contact details mentioned above, introduce yourself, and maybe schedule a short virtual coffee chat!<br><br>
Welcome aboard, %employeeFullName%! We look forward to achieving great things together.
<br><br>
Warm Regards!<br>
The team at  %companyName%
<br><br>
You are getting this email because you are a subscriber.' WHERE `notification_id` = @notification_id AND `name` = 'Employee welcome email sent to Subscriber';

-- Changes to hs_hr_emp_directory_email table

ALTER TABLE `hs_hr_emp_directory_email` DROP FOREIGN KEY `fk_hs_hr_emp_directory_email_hs_hr_employee` ;

ALTER TABLE hs_hr_emp_directory_email DROP PRIMARY KEY;

ALTER TABLE hs_hr_emp_directory_email ADD COLUMN `process_date` DATE DEFAULT NULL AFTER `status`;

ALTER TABLE hs_hr_emp_directory_email ADD PRIMARY KEY (emp_number, process_date);

Update hs_hr_emp_directory_email e set e.process_date = (select jv.joined_date from
    (select emp.emp_number, emp.joined_date from hs_hr_employee emp left join hs_hr_emp_directory_email edm ON (edm.emp_number = emp.emp_number) where edm.emp_number = emp.emp_number) jv where jv.emp_number = e.emp_number);


