DROP VIEW IF EXISTS `v_pbi_performance_v1`;
CREATE VIEW `v_pbi_performance_v1` AS SELECT
                                          `pa`.`id` AS `perf_id`,
                                          pbi_generate_entity_key(`pa`.`emp_number`,'emp_key_') as `employee_key`,
                                          `pa`.`description` AS `perf_description`,
                                          `pa`.`from_date` AS `perf_from_date`,
                                          `pa`.`to_date` AS `perf_to_date`,
                                          `pa`.`due_date` AS `perf_due_date`,
                                          `ohrm_performance_appraisal_status`.`name` AS `perf_state`,
                                          `ohrm_appraisal_template`.`name` AS `perf_template`,
                                          `ohrm_appraisal_cycle`.`name` AS `perf_appraisal_cycle`,
                                          IF(FIND_IN_SET(ohrm_performance_appraisal_status.name, (SELECT `value` FROM `ohrm_config` WHERE `property` = 'performance.show_final_rating_on_status')) > 0  AND pa.final_rating > '-1',`pa`.`final_rating`,NULL) AS `perf_final_rating`,
                                          IF(FIND_IN_SET(ohrm_performance_appraisal_status.name, (SELECT `value` FROM `ohrm_config` WHERE `property` = 'performance.show_final_rating_on_status')) > 0  AND pa.final_rating_percentage > '-1',`pa`.`final_rating_percentage`,NULL) AS `perf_final_rating_percentage`,
                                          `pa`.`nine_box_matrix_applicable` AS `perf_9box_matrix_applicable`,
                                          `ohrm_performance_category`.`display_name` AS `perf_rating_category`,
                                          `ohrm_potential_category`.`display_name` AS `perf_potential_rating_category`,
                                          `ohrm_nine_box_master_data`.`display_name` AS `perf_9box_category`
                                      FROM `ohrm_performance_appraisal` as `pa`
                                               LEFT JOIN `ohrm_performance_appraisal_status` ON `pa`.`state_id` = `ohrm_performance_appraisal_status`.`state_id`
                                               LEFT JOIN `ohrm_appraisal_template` ON `pa`.`template_id` = `ohrm_appraisal_template`.`id`
                                               LEFT JOIN `ohrm_appraisal_cycle` ON `pa`.`appraisal_cycle_id` = `ohrm_appraisal_cycle`.`id`
                                               LEFT JOIN `ohrm_performance_category` ON `ohrm_performance_category`.`id` = `pa`.`performance_rating_category`
                                               LEFT JOIN `ohrm_potential_category` ON `ohrm_potential_category`.`id` = `pa`.`potential_rating_category`
                                               LEFT JOIN `ohrm_nine_box_master_data` ON `ohrm_nine_box_master_data`.`id` = `pa`.`nine_box_category_id`;


DROP VIEW IF EXISTS `v_pbi_job_state_change_v1`;
CREATE VIEW `v_pbi_job_state_change_v1` AS SELECT
                                               `job_history`.`id` AS `job_id`,
                                               pbi_generate_entity_key(`job_history`.`employee_number`,'emp_key_') as `employee_key`,
                                               CASE WHEN `event`.`name` = 'Employment Terminated' THEN DATE_ADD(`job_history`.`effective_date`,INTERVAL 1 DAY) ELSE `job_history`.`effective_date` END AS `job_effective_from`,
                                               CASE WHEN `job_history`.`effective_to_date` IS NULL THEN CAST('2099-12-31' as date) ELSE CASE WHEN `event_temp`.`name` ='Employment Terminated' THEN `job_history`.`effective_to_date` ELSE DATE_SUB(`job_history`.`effective_to_date`, INTERVAL 1 DAY) END END AS `job_effective_to`,
                                               `event`.`name` AS `job_event`,
                                               IF(`job_history`.`job_title_id` IS NULL,`job_history`.`job_title_name`,`ohrm_job_title`.`job_title`) AS `job_job_title`,
                                               `job_history`.`employment_status_name` as `job_employment_status`,
                                               IF(`job_history`.`job_category_id` IS NULL,`job_history`.`job_category_name`,`ohrm_job_category`.`name`) AS `job_job_category`,
                                               IF(`job_history`.`subunit_id` IS NULL,`job_history`.`subunit_name`,`ohrm_subunit`.`name`) AS `job_subunit`,
                                               pbi_generate_entity_key(`job_history`.`location_id`,'loc_key_') as `location_key`,
                                               `job_history`.`joined_date` as `job_join_date`,
                                               `job_history`.`probation_end_date` as `job_probation_end_date`,
                                               `job_history`.`date_of_permanency` as `job_date_of_permanency`,
                                               `job_history`.`cost_centre_name` as `job_cost_center`,
                                               `job_history`.`eeo_job_category_name` as `job_eeo_job_category`,
                                               `job_history`.`flsa_exempt` as `job_flsa_exempt`,
                                               `job_history`.`pay_policy_name` as `job_pay_policy`,
                                               `job_history`.`job_custom_name_list` AS `job_custom_name_list`,
                                               `job_history`.`job_custom_value_list` AS `job_custom_value_list`,
                                               IF(`job_history`.`job_custom_count` IS NULL,0,`job_history`.`job_custom_count`) AS `job_custom_count`,
                                               CASE WHEN `event`.`name` = 'Employment Terminated' THEN pbi_get_employee_status(DATE_ADD(`job_history`.`effective_date`,INTERVAL 1 DAY),`job_history`.`employee_number`)  ELSE pbi_get_employee_status(`job_history`.`effective_date`,`job_history`.`employee_number`) END AS `job_status`
                                           FROM
                                               (WITH `job_rec_with_row_num` AS (
                                                   SELECT `ohrm_job_record_history`.*, ROW_NUMBER() OVER (PARTITION BY `employee_number`, `effective_date` ORDER BY `ohrm_job_record_history`.`id` DESC) AS rn
                                                   FROM `ohrm_job_record_history`
                                                            LEFT JOIN `ohrm_pim_history_event` phe on ohrm_job_record_history.event_id = phe.id
                                                            LEFT JOIN `ohrm_emp_termination` on `ohrm_emp_termination`.`emp_number` = `ohrm_job_record_history`.`employee_number` and  `ohrm_emp_termination`.`termination_date` = `ohrm_job_record_history`.`effective_date` and phe.`name` = 'Employment Terminated'
                                                   WHERE (`effective_date` IS NOT NULL)
                                               ) SELECT * FROM
                                                    (SELECT * FROM
                                                        (SELECT `jr`.*, `jr_temp`.`effective_date` AS `effective_to_date`, `jr_temp`.`event_id` AS `next_event_id`, ROW_NUMBER() OVER (PARTITION BY jr.`employee_number`, jr.`effective_date`,jr.id ORDER BY DATEDIFF(`jr_temp`.`effective_date`,`jr`.`effective_date`) ASC) AS `rn_join`
                                                         FROM
                                                             (SELECT * FROM `job_rec_with_row_num` ) AS `jr` -- Get latest history record when having multiple records on same effective date
                                                                 LEFT JOIN
                                                             (SELECT * FROM `job_rec_with_row_num` ) AS `jr_temp`
                                                             ON (`jr`.`employee_number` = `jr_temp`.`employee_number` AND `jr`.`effective_date` < `jr_temp`.`effective_date`)
                                                        ) AS `effective_range_with_rn`
                                                     WHERE `rn_join` = 1) AS `job_history_with_effective_range` -- Get job history with efective range
                                                        LEFT JOIN
                                                    (SELECT
                                                         `job_custom_fields_and_job_records`.`job_rec_id` AS `job_record_history_id`,
                                                         GROUP_CONCAT(`job_custom_fields_and_job_records`.`job_custom_name` ORDER BY `job_custom_fields_and_job_records`.`field_id` SEPARATOR '|') AS `job_custom_name_list`,
                                                         GROUP_CONCAT(IF(`job_custom_field_history`.`job_custom_value` IS NULL,'NULL',`job_custom_field_history`.`job_custom_value`) ORDER BY `job_custom_fields_and_job_records`.`field_id` SEPARATOR '|') AS `job_custom_value_list`,
                                                         COUNT(`job_custom_fields_and_job_records`.`field_id`) AS `job_custom_count`
                                                     FROM
                                                         (SELECT * FROM
                                                             (SELECT `ohrm_custom_field`.`id` as `field_id`, `ohrm_custom_field`.`name` AS `job_custom_name` FROM `ohrm_custom_field` INNER JOIN `ohrm_custom_field_section` ON `ohrm_custom_field_section`.`id` = `ohrm_custom_field`.`custom_field_section_id` AND `ohrm_custom_field_section`.`display_name` = 'Other Job Details') AS `job_custom_fields`
                                                                 CROSS JOIN
                                                             (SELECT `id` AS `job_rec_id` FROM `job_rec_with_row_num` ) AS `job_records`
                                                         ) AS `job_custom_fields_and_job_records`
                                                             LEFT JOIN
                                                         (SELECT `job_custom_field_history`.`job_record_history_id`,
                                                                 `ohrm_custom_field`.`id` AS `job_custom_id`,
                                                                 CASE
                                                                     WHEN (`ohrm_custom_field_value_history`.`value_type` IN (1,6)) AND pbi_is_not_empty(`ohrm_custom_field_value_history`.`string_value`) THEN `ohrm_custom_field_value_history`.`string_value`
                                                                     WHEN `ohrm_custom_field_value_history`.`value_type` = 2 AND pbi_is_not_empty(`ohrm_custom_field_value_history`.`text_value`) THEN `ohrm_custom_field_value_history`.`text_value`
                                                                     WHEN `ohrm_custom_field_value_history`.`value_type` = 3 AND pbi_is_not_empty(`ohrm_custom_field_value_history`.`number_value`) THEN `ohrm_custom_field_value_history`.`number_value`
                                                                     WHEN `ohrm_custom_field_value_history`.`value_type` = 4 AND pbi_is_not_empty(`ohrm_custom_field_value_history`.`date_value`) THEN `ohrm_custom_field_value_history`.`date_value`
                                                                     ELSE 'NULL' END as `job_custom_value`
                                                          FROM  `ohrm_custom_field_value_history_for_job_record_history` AS `job_custom_field_history`
                                                                    INNER JOIN `ohrm_custom_field_value_history` ON (`job_custom_field_history`.`custom_field_value_id`=`ohrm_custom_field_value_history`.`id` AND `job_custom_field_history`.`custom_field_value_history_version` = `ohrm_custom_field_value_history`.`version`)
                                                                    INNER JOIN `ohrm_custom_field` ON `ohrm_custom_field_value_history`.`custom_field_id`=`ohrm_custom_field`.`id`
                                                              AND (`ohrm_custom_field_value_history`.`value_type` IN (1,2,3,4,6) OR `ohrm_custom_field_value_history`.`value_type` IS NULL)) AS `job_custom_field_history`
                                                         ON `job_custom_field_history`.`job_record_history_id` = `job_custom_fields_and_job_records`.`job_rec_id` AND `job_custom_field_history`.`job_custom_id` = `job_custom_fields_and_job_records`.`field_id`
                                                     GROUP BY `job_custom_fields_and_job_records`.`job_rec_id`) AS `job_history_with_custom_fields`
                                                    ON `job_history_with_custom_fields`.`job_record_history_id` = `job_history_with_effective_range`.`id`) AS `job_history`
                                                   LEFT JOIN `ohrm_pim_history_event` as `event` ON `job_history`.`event_id` = `event`.`id`
                                                   LEFT JOIN `ohrm_pim_history_event` as `event_temp` ON `job_history`.`next_event_id` = `event_temp`.`id`
                                                   LEFT JOIN `ohrm_job_title` as `ohrm_job_title` ON `job_history`.`job_title_id` = `ohrm_job_title`.`id`
                                                   LEFT JOIN `ohrm_job_category` as `ohrm_job_category` ON `job_history`.`job_category_id` = `ohrm_job_category`.`id`
                                                   LEFT JOIN `ohrm_subunit` as `ohrm_subunit` ON `job_history`.`subunit_id` = `ohrm_subunit`.`id`;

CREATE VIEW `v_pbi_request_custom_field_v1` AS SELECT
                                               pbi_generate_entity_key(`ohrm_request`.`emp_number`,'emp_key_') AS `employee_key`,
                                               `ohrm_request_custom_field_value`.`request_id` AS `request_id`,
                                               `ohrm_request_custom_field_value`.`custom_field_id` AS `custom_field_id`,
                                               `ohrm_requestdesk_custom_field`.`name` AS `custom_field_name`,
                                               CASE
                                                   WHEN  `ohrm_request_custom_field_value`.`value_type` = 1  THEN `ohrm_request_custom_field_value`.`string_value`
                                                   WHEN `ohrm_request_custom_field_value`.`value_type` = 2 THEN `ohrm_request_custom_field_value`.`text_value`
                                                   WHEN `ohrm_request_custom_field_value`.`value_type` = 3 THEN `ohrm_request_custom_field_value`.`number_value`
                                                   WHEN `ohrm_request_custom_field_value`.`value_type` = 4  THEN `ohrm_request_custom_field_value`.`date_value`
                                                   WHEN `ohrm_request_custom_field_value`.`value_type` = 7  THEN `ohrm_request_custom_field_value`.`date_value`
                                                   WHEN `ohrm_request_custom_field_value`.`value_type` = 6 THEN
                                                       CASE
                                                           WHEN `ohrm_vas_custom_advance_type_map`.`entity` = 'JobTitle' THEN (SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = `ohrm_request_custom_field_value`.`string_value`)
                                                           WHEN `ohrm_vas_custom_advance_type_map`.`entity` = 'Subunit' THEN (SELECT `name` FROM `ohrm_subunit` WHERE `id` = `ohrm_request_custom_field_value`.`string_value`)
                                                           WHEN `ohrm_vas_custom_advance_type_map`.`entity` = 'Location' THEN (SELECT `name` FROM `ohrm_location` WHERE `id` = `ohrm_request_custom_field_value`.`string_value`)
                                                           WHEN `ohrm_vas_custom_advance_type_map`.`entity` = 'Employee' THEN (SELECT pbi_generate_entity_key(`emp_number`,'emp_key_') `employee_key` FROM `hs_hr_employee` WHERE `employee_id` = `ohrm_request_custom_field_value`.`string_value`)

                                                           WHEN JSON_EXTRACT(`ohrm_requestdesk_custom_field`.`other_data`, '$.entity') = 'Nationality' THEN (SELECT `name` FROM `ohrm_nationality` WHERE `id` = `ohrm_request_custom_field_value`.`string_value`)
                                                           WHEN JSON_EXTRACT(`ohrm_requestdesk_custom_field`.`other_data`, '$.entity') = 'EmploymentStatus' THEN (SELECT `name` FROM `ohrm_employment_status` WHERE `id` = `ohrm_request_custom_field_value`.`string_value`)
                                                           WHEN JSON_EXTRACT(`ohrm_requestdesk_custom_field`.`other_data`, '$.entity') = 'JobTitle' THEN (SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = `ohrm_request_custom_field_value`.`string_value`)
                                                           WHEN JSON_EXTRACT(`ohrm_requestdesk_custom_field`.`other_data`, '$.entity') = 'JobCategory' THEN (SELECT `name` FROM `ohrm_job_category` WHERE `id` = `ohrm_request_custom_field_value`.`string_value`)
                                                           WHEN JSON_EXTRACT(`ohrm_requestdesk_custom_field`.`other_data`, '$.entity') = 'WorkSchedule' THEN (SELECT `name` FROM `ohrm_work_schedule` WHERE `id` = `ohrm_request_custom_field_value`.`string_value`)
                                                           WHEN JSON_EXTRACT(`ohrm_requestdesk_custom_field`.`other_data`, '$.entity') = 'PayGrade' THEN (SELECT `name` FROM `ohrm_pay_grade` WHERE `id` = `ohrm_request_custom_field_value`.`string_value`)
                                                           WHEN JSON_EXTRACT(`ohrm_requestdesk_custom_field`.`other_data`, '$.entity') = 'CurrencyType' THEN (SELECT `currency_name` FROM `hs_hr_currency_type` WHERE `currency_id` = `ohrm_request_custom_field_value`.`string_value`)

                                                           END
                                                   ELSE 'NULL'
                                                   END AS `emp_custom_value`,
                                               `ohrm_requestdesk_custom_field`.`section_id` AS `custom_field_section_id`,
                                               `ohrm_requestdesk_custom_field_section`.`display_name` AS `custom_field_section_name`
                                           FROM `ohrm_request_custom_field_value`
                                                    INNER JOIN `ohrm_requestdesk_custom_field` ON (`ohrm_requestdesk_custom_field`.`id` = `ohrm_request_custom_field_value`.`custom_field_id` )
                                                    LEFT JOIN `ohrm_vas_custom_advance_type_map` ON (`ohrm_vas_custom_advance_type_map`.`id` = `ohrm_requestdesk_custom_field`.`advance_type_map`)
                                                    LEFT JOIN `ohrm_requestdesk_custom_field_section` ON (`ohrm_requestdesk_custom_field_section`.`id` = `ohrm_requestdesk_custom_field`.`section_id`)
                                                    LEFT JOIN `ohrm_request` ON (ohrm_request_custom_field_value.request_id = ohrm_request.id)
                                           WHERE `ohrm_requestdesk_custom_field`.`deleted` = 0 ;



CREATE VIEW `v_pbi_request_v1` AS SELECT
                                      pbi_generate_entity_key(`ohrm_request`.`emp_number`,'emp_key_') AS `employee_key`,
                                      ohrm_request.id as request_id,
                                      pbi_generate_entity_key(`ohrm_request`.`request_for_emp_number`,'emp_key_') as request_for_emp_number,
                                      ohrm_request.title as title,
                                      ohrm_request.description as description,
                                      ohrm_request.priority as priority,
                                      ohrm_request.resolution_date as due_date,
                                      ohrm_request.resolution as resolution,
                                      ohrm_request.extra_details as extra_details,
                                      ohrm_request.submitted_date as submitted_date,
                                      ohrm_request.modified_date as modified_date,
                                      ohrm_request.archived as archived,

                                      pbi_generate_entity_key(`ohrm_request`.`owner_id`,'emp_key_') as owner_id,
                                      pbi_generate_entity_key(`ohrm_request`.`secondary_owner_id`,'emp_key_') as secondary_owner_id,
                                      pbi_generate_entity_key(`ohrm_request`.`pending_with_employee_id`,'emp_key_') as pending_with_employee_id,

                                      ohrm_request.request_type_id as request_type_id,
                                      ohrm_requestdesk_request_types.name as request_type_name

                                  FROM ohrm_request
                                           LEFT JOIN ohrm_requestdesk_request_types ON ohrm_request.request_type_id = ohrm_requestdesk_request_types.id;


CREATE VIEW `v_pbi_custom_field_v1` AS SELECT
                                           pbi_generate_entity_key(`ohrm_custom_field_value`.`emp_number`,'emp_key_') AS `employee_key`,
                                           `ohrm_custom_field_value`.`custom_field_id` AS `custom_field_id`,
                                           `ohrm_custom_field`.`name` AS `custom_field_name`,
                                           CASE
                                               WHEN  `ohrm_custom_field_value`.`value_type` = 1  THEN `ohrm_custom_field_value`.`string_value`
                                               WHEN `ohrm_custom_field_value`.`value_type` = 2 THEN `ohrm_custom_field_value`.`text_value`
                                               WHEN `ohrm_custom_field_value`.`value_type` = 3 THEN `ohrm_custom_field_value`.`number_value`
                                               WHEN `ohrm_custom_field_value`.`value_type` = 4  THEN `ohrm_custom_field_value`.`date_value`
                                               WHEN `ohrm_custom_field_value`.`value_type` = 6 THEN
                                                   CASE
                                                       WHEN `ohrm_custom_advance_type_map`.`entity` = 'JobTitle' THEN (SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = `ohrm_custom_field_value`.`string_value`)
                                                       WHEN `ohrm_custom_advance_type_map`.`entity` = 'Subunit' THEN (SELECT `name` FROM `ohrm_subunit` WHERE `id` = `ohrm_custom_field_value`.`string_value`)
                                                       END
                                               ELSE 'NULL'
                                               END AS `emp_custom_value`,
                                           `ohrm_custom_field`.`custom_field_section_id` AS `custom_field_section_id`,
                                           `ohrm_custom_field_section`.`display_name` AS `custom_field_section_name`,
                                           `ohrm_custom_field_section`.`custom_field_screen_id` AS `custom_field_screen_id`,
                                           `ohrm_custom_field_screen`.`name` AS `custom_field_screen_name`,
                                           `ohrm_custom_field_screen`.`screen_id` AS `screen_id`,
                                           `ohrm_screen`.`name` AS `screen_name`,
                                           `ohrm_custom_field_value`.`sequence_number` AS `sequence_number`
                                       FROM `ohrm_custom_field_value`
                                                INNER JOIN `ohrm_custom_field` ON (`ohrm_custom_field`.`id` = `ohrm_custom_field_value`.`custom_field_id` AND `ohrm_custom_field`.`deleted` = 0)
                                                LEFT JOIN `ohrm_custom_advance_type_map` ON (`ohrm_custom_advance_type_map`.`id` = `ohrm_custom_field`.`advance_type_map`)
                                                LEFT JOIN `ohrm_custom_field_section` ON (`ohrm_custom_field_section`.`id` = `ohrm_custom_field`.`custom_field_section_id`)
                                                LEFT JOIN `ohrm_custom_field_screen` ON (`ohrm_custom_field_section`.`custom_field_screen_id` = `ohrm_custom_field_screen`.`id`)
                                                LEFT JOIN `ohrm_screen` ON (`ohrm_custom_field_screen`.`screen_id` = `ohrm_screen`.`id`);


CREATE VIEW `v_pbi_job_event_change_v1` AS
SELECT
    'emp_key_00000000' AS `employee_key`,
    'AAAAAAAAAAAAAAAAAAAAAAAAA' as `event_name`,
    CAST('2099-12-31' AS DATE) as `effective_date`,
    'AAAAAAAAAAAAAAAAAAAAAAAAA' as `field_name`,
    'AAAAAAAAAAAAAAAAAAAAAAAAA' as `old_value`,
    'AAAAAAAAAAAAAAAAAAAAAAAAA' as `value`;

DROP VIEW IF EXISTS `v_pbi_employee_v1`;
CREATE VIEW `v_pbi_employee_v1` AS SELECT
                                       pbi_generate_entity_key(`emp_reportto`.`emp_number`,'emp_key_') as `employee_key`,
                                       `emp_reportto`.`employee_id`,
                                       CASE WHEN (`emp_reportto`.`purged_at` IS NOT NULL OR `emp_reportto`.`deleted_at` IS NOT NULL)
                                                THEN
                                                "Purged"
                                            ELSE
                                                CASE WHEN `emp_reportto`.`termination_id` IS NULL
                                                         THEN
                                                         "Active"
                                                     ELSE
                                                         "Terminated"
                                                    END
                                           END as `emp_status`,
                                       `emp_reportto`.`emp_firstname`,
                                       `emp_reportto`.`emp_middle_name` AS `emp_middlename`,
                                       `emp_reportto`.`emp_lastname`,
                                       CASE WHEN `emp_reportto`.`emp_middle_name` = NULL OR RTRIM(`emp_reportto`.`emp_middle_name`)='' THEN  CONCAT(`emp_reportto`.`emp_firstname`," ",`emp_reportto`.`emp_lastname`) ELSE CONCAT(`emp_reportto`.`emp_firstname`," ",`emp_reportto`.`emp_middle_name`," ",`emp_reportto`.`emp_lastname`) END AS `emp_fullname`,
                                       `emp_reportto`.`emp_work_email`,
                                       `emp_reportto`.`emp_mobile`,
                                       `emp_reportto`.`emp_work_telephone`,
                                       `emp_reportto`.`emp_birthday` as `emp_dob`,
                                       `emp_reportto`.`emp_dri_lice_exp_date` as `emp_driving_license_exp_date`,
                                       `ohrm_marital_status`.`name` as `emp_marital_status`,
                                       `ohrm_gender`.`name` as `emp_gender`,
                                       `ohrm_eeo_race_ethnicity`.`name` AS `emp_eeo_race_ethnicity`,
                                       `ohrm_nationality`.`name` AS `emp_nationality`,
                                       `emp_reportto`.`emp_supervisor_key_list`,
                                       `emp_reportto`.`emp_supervisor_type_list`,
                                       `emp_reportto`.`emp_supervisor_count`,
                                       `emp_reportto`.`emp_other_id`,
                                       `emp_reportto`.`added_date` AS `emp_creation_date`,
                                       `ohrm_emp_termination`.`termination_date` AS `emp_termination_date`,
                                       `ohrm_emp_termination_reason`.`name` AS `emp_termination_reason`,
                                       `emp_reportto`.`purged_at` AS `emp_purge_date`,
                                       `emp_custom_field`.`emp_custom_name_list`,
                                       `emp_custom_field`.`emp_custom_value_list`,
                                       IF(`emp_custom_field`.`emp_custom_count` IS NULL,0,`emp_custom_field`.`emp_custom_count`) AS `emp_custom_count`
                                   FROM
                                       (SELECT `hs_hr_employee`.*, GROUP_CONCAT(pbi_generate_entity_key(`hs_hr_emp_reportto`.`erep_sup_emp_number`,'emp_key_') ORDER BY `ohrm_emp_reporting_method`.`reporting_method_id` SEPARATOR '|') AS `emp_supervisor_key_list`,
                                               GROUP_CONCAT(`ohrm_emp_reporting_method`.`reporting_method_name` ORDER BY `ohrm_emp_reporting_method`.`reporting_method_id` SEPARATOR '|') AS `emp_supervisor_type_list`,
                                               COUNT(`hs_hr_emp_reportto`.`erep_sup_emp_number`) AS `emp_supervisor_count`
                                        FROM `hs_hr_employee`
                                                 LEFT JOIN `hs_hr_emp_reportto` ON `hs_hr_emp_reportto`.`erep_sub_emp_number` = `hs_hr_employee`.`emp_number`
                                                 LEFT JOIN `ohrm_emp_reporting_method` ON `hs_hr_emp_reportto`.`erep_reporting_mode` = `ohrm_emp_reporting_method`.`reporting_method_id`
                                        GROUP BY `hs_hr_employee`.`emp_number`) AS `emp_reportto`
                                           LEFT JOIN
                                       (SELECT `ohrm_custom_field_value`.`emp_number`, GROUP_CONCAT(`ohrm_custom_field`.`name` ORDER BY `ohrm_custom_field_value`.`id` SEPARATOR '|') AS `emp_custom_name_list`, GROUP_CONCAT(
                                               CASE WHEN  `ohrm_custom_field_value`.`value_type` = 1 AND pbi_is_not_empty(`ohrm_custom_field_value`.`string_value`) THEN `ohrm_custom_field_value`.`string_value`
                                                    WHEN `ohrm_custom_field_value`.`value_type` = 2 AND pbi_is_not_empty(`ohrm_custom_field_value`.`text_value`) THEN `ohrm_custom_field_value`.`text_value`
                                                    WHEN `ohrm_custom_field_value`.`value_type` = 3 AND pbi_is_not_empty(`ohrm_custom_field_value`.`number_value`) THEN `ohrm_custom_field_value`.`number_value`
                                                    WHEN `ohrm_custom_field_value`.`value_type` = 4 AND pbi_is_not_empty(`ohrm_custom_field_value`.`date_value`) THEN `ohrm_custom_field_value`.`date_value`
                                                    WHEN `ohrm_custom_field_value`.`value_type` = 6 AND pbi_is_not_empty(`ohrm_custom_field_value`.`string_value`) THEN
                                                        CASE WHEN `ohrm_custom_advance_type_map`.`entity` = 'JobTitle' THEN (SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = `ohrm_custom_field_value`.`string_value`)
                                                             WHEN `ohrm_custom_advance_type_map`.`entity` = 'Subunit' THEN (SELECT `name` FROM `ohrm_subunit` WHERE `id` = `ohrm_custom_field_value`.`string_value`)
                                                            END
                                                    ELSE 'NULL' END ORDER BY `ohrm_custom_field_value`.`id` SEPARATOR '|'
                                           ) as `emp_custom_value_list`,
                                               COUNT(`ohrm_custom_field_value`.`id`) AS `emp_custom_count`
                                        FROM
                                            (SELECT * FROM `ohrm_custom_field_value` WHERE `ohrm_custom_field_value`.`value_type` IN (1,2,3,4,6)) AS `ohrm_custom_field_value`
                                                INNER JOIN `ohrm_custom_field` ON `ohrm_custom_field`.`id` = `ohrm_custom_field_value`.`custom_field_id`
                                                INNER JOIN `ohrm_custom_field_section` ON `ohrm_custom_field_section`.`id` = `ohrm_custom_field`.`custom_field_section_id`
                                                AND `ohrm_custom_field_section`.`custom_field_screen_id` NOT IN (SELECT `id` FROM `ohrm_custom_field_screen` where name = 'job-standard' OR `menu_id` IS NOT NULL)
                                                LEFT JOIN `ohrm_custom_advance_type_map` ON `ohrm_custom_advance_type_map`.`id` = `ohrm_custom_field`.`advance_type_map`
                                        GROUP BY `ohrm_custom_field_value`.`emp_number`) AS `emp_custom_field`
                                       ON `emp_reportto`.`emp_number` = `emp_custom_field`.`emp_number`
                                           LEFT JOIN `ohrm_gender` on `emp_reportto`.`emp_gender` = `ohrm_gender`.`id`
                                           LEFT JOIN `ohrm_marital_status` on `emp_reportto`.`emp_marital_status` = `ohrm_marital_status`.`id`
                                           LEFT JOIN `ohrm_employment_status` ON `emp_reportto`.`emp_status` = `ohrm_employment_status`.`id`
                                           LEFT JOIN `ohrm_job_title` ON `emp_reportto`.`job_title_code` = `ohrm_job_title`.`id`
                                           LEFT JOIN `ohrm_job_category` ON `emp_reportto`.`eeo_cat_code` = `ohrm_job_category`.`id`
                                           LEFT JOIN `ohrm_subunit`ON `emp_reportto`.`work_station` = `ohrm_subunit`.`id`
                                           LEFT JOIN `ohrm_eeo_job_category` ON `emp_reportto`.`eeo_job_category` = `ohrm_eeo_job_category`.`id`
                                           LEFT JOIN `ohrm_eeo_race_ethnicity` ON `emp_reportto`.`eeo_race_ent` = `ohrm_eeo_race_ethnicity`.`id`
                                           LEFT JOIN `ohrm_location` ON `emp_reportto`.`location_id` = `ohrm_location`.`id`
                                           LEFT JOIN `hs_hr_country` ON `emp_reportto`.`coun_code` = `hs_hr_country`.`cou_code`
                                           LEFT JOIN `ohrm_nationality` ON `ohrm_nationality`.`id` = `emp_reportto`.`nation_code`
                                           LEFT JOIN `ohrm_emp_termination` ON `emp_reportto`.`termination_id` = `ohrm_emp_termination`.`id`
                                           LEFT JOIN `ohrm_emp_termination_reason` ON `ohrm_emp_termination`.`reason_id` = `ohrm_emp_termination_reason`.`id`;

