DROP VIEW IF EXISTS  ohrm_performance_tracker_report_view;
CREATE VIEW ohrm_performance_tracker_report_view AS
SELECT opt.id AS ohrm_performance_track_id, opt.emp_number as employee_number, opt.tracker_name AS tracker_name, DATE(opt.added_date) AS tracker_added_date, opt.added_by as tracker_added_by, opt.status as tracker_status , DATE(opt.modified_date) as tracker_modified_date,
        optl.log as tracker_log_log, optl.comment as tracker_log_comment, optl.status as tracker_log_status, DATE(optl.added_date) as tracker_log_added_date, DATE(optl.modified_date) as tracker_log_modified_date, optl.reviewer_id as tracker_log_reviewer_id, optl.rating_id as tracker_log_rating_id, optl.user_id as tracker_log_user_id, optl.id as tracker_log_id,
        (SELECT MAX(DATE(added_date)) FROM ohrm_performance_tracker_log WHERE performance_track_id = optl.performance_track_id) AS tracker_log_last_log_added_date
        FROM ohrm_performance_track opt
        LEFT JOIN ohrm_performance_tracker_log optl ON opt.id = optl.performance_track_id WHERE opt.status = 1 ORDER BY opt.id ,optl.id;


SET @employee_performance_trackers := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'pim.Employee_Performance_Trackers');

INSERT INTO ohrm_report_configuration (report_id, configuration) VALUES
     (@employee_performance_trackers, '{"selectedDisplayFieldGroups":["1","6","50"],"selectedDisplayFields":["9","9_1","pt_1","pt_2","pt_3","pt_5","pt_6","pt_7","pt_8","pt_9","pt_10","77","pt_4","82"],"selectedFilterCriteria":{"include":"2","show_only_filtered_records":true,"employee_name":"","sub_unit":"","location":"","job_title":"","tracker_name":"","performance_tracker_performance":"","performance_tracker_reviewer":"","performance_tracker_added_date_dynamic_date_group":{"performance_tracker_added_date_dynamic_date_group":"performance_tracker_added_date_date_more_custom","performance_tracker_added_date_date_more_custom":"","performance_tracker_added_date_date_more_today":"1","performance_tracker_added_date_date_more_from_field":""},"performance_tracker_log_added_date_dynamic_date_group":{"performance_tracker_log_added_date_dynamic_date_group":"performance_tracker_log_added_date_date_more_custom","performance_tracker_log_added_date_date_more_custom":"","performance_tracker_log_added_date_date_more_today":"1","performance_tracker_log_added_date_date_more_from_field":""}}}');

INSERT INTO ohrm_report_share (report_id, role_type_id) VALUES
(@employee_performance_trackers, 1);
