-- revision 183881

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


CREATE VIEW `v_pbi_leave_v1` AS SELECT 
`l`.`id` AS `leave_id`,
pbi_generate_entity_key(`l`.`emp_number`,'emp_key_') as `employee_key`,
`l`.`date` AS `leave_date`,
ROUND(`l`.`length_minutes`/60,4) AS `leave_hours`,
`l`.`length_days` AS `leave_days`,
`ohrm_leave_status`.`name` AS `leave_status`,
 `ohrm_leave_type`.`name` AS `leave_type`,
`l`.`start_time` AS `leave_start_time`,
`l`.`end_time` AS `leave_end_time`,
`l`.`start_utc_datetime` AS `leave_start_utc`,
`l`.`end_utc_datetime` AS `leave_end_utc`,
CASE WHEN `l`.`duration_type`= 0 THEN "Full Day Leave" WHEN `l`.`duration_type`= 1 THEN "Half Day (Morning) Leave" WHEN `l`.`duration_type`= 2 THEN "Half Day (Afternoon) Leave" ELSE "Specific Time Leave" END AS `leave_duration_type`,
IF(`ohrm_leave_request`.`date_applied` = "0000-00-00", NULL, `ohrm_leave_request`.`date_applied`) AS `leave_applied_date`
FROM `ohrm_leave` as `l` 
LEFT JOIN `ohrm_leave_status` ON `l`.`status` = `ohrm_leave_status`.`status` 
LEFT JOIN `ohrm_leave_type` ON `ohrm_leave_type`.`id` = `l`.`leave_type_id` 
LEFT JOIN `ohrm_leave_request` ON `ohrm_leave_request`.`id` = `l`.`leave_request_id`;


CREATE VIEW `v_pbi_attendance_v1` AS SELECT 
`ohrm_attendance_record`.`id` as `attendance_id`, 
pbi_generate_entity_key(`ohrm_attendance_record`.`employee_id`,'emp_key_') as `employee_key`,
`ohrm_attendance_record`.`punch_in_utc_time` AS `attend_in_utc`,
`ohrm_attendance_record`.`punch_in_note` AS `attend_in_note`,
`ohrm_attendance_record`.`punch_in_time_offset` AS `attend_in_time_offset`,
`ohrm_attendance_record`.`punch_in_user_time` AS `attend_in_user_time`,
`ohrm_attendance_record`.`punch_out_utc_time` AS `attend_out_utc`,
`ohrm_attendance_record`.`punch_out_note` AS `attend_out_note`, 
`ohrm_attendance_record`.`punch_out_time_offset` AS `attend_out_time_offset`,
`ohrm_attendance_record`.`punch_out_user_time` AS `attend_out_user_time`,
`ohrm_attendance_record`.`state` AS `attend_state`,
CASE WHEN `ohrm_attendance_record`.`state` = 'PUNCHED IN' THEN 0 ELSE TIMESTAMPDIFF(MINUTE,`ohrm_attendance_record`.`punch_in_utc_time` ,`ohrm_attendance_record`.`punch_out_utc_time`) END as `attend_duration`,
`ohrm_attendance_sheet_status`.`name` AS `attend_approval_state`
FROM `ohrm_attendance_record` 
LEFT JOIN `ohrm_attendance_sheet` ON ( `ohrm_attendance_sheet`.`emp_number` = `ohrm_attendance_record`.`employee_id` AND DATEDIFF(`ohrm_attendance_record`.`punch_in_user_time`,`ohrm_attendance_sheet`.`start_date`)>=0 AND DATEDIFF(`ohrm_attendance_record`.`punch_in_user_time`, `ohrm_attendance_sheet`.`end_date`)<=0 ) 
LEFT JOIN `ohrm_attendance_sheet_status` ON `ohrm_attendance_sheet`.`state_id` = `ohrm_attendance_sheet_status`.`id` 
WHERE `ohrm_attendance_record`.`punch_in_user_time` != "0000-00-00 00:00:00" AND (`ohrm_attendance_record`.`state` = 'PUNCHED IN' OR `ohrm_attendance_record`.`punch_in_utc_time`<= `ohrm_attendance_record`.`punch_out_utc_time`);


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


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_salary_state_change_v1` AS SELECT 
`salary_record_temp`.`id` AS `sal_history_id`,
pbi_generate_entity_key(`salary_record_temp`.`employee_number`,'emp_key_') as `employee_key`,
IF(`termination_record`.`action`= "Reactivate",`termination_record`.`date`, `salary_record_temp`.`effective_date`) AS `sal_effective_from`,
IF(`termination_record`.`action`= "Terminate",`termination_record`.`date`, IF(`salary_record_temp`.`next_salary_record_effective_date`,`salary_record_temp`.`next_salary_record_effective_date`, CAST('2099-12-31' as date))) AS `sal_effective_to`,
`ohrm_pim_history_event`.`name` AS `sal_event`,
`salary_record_temp`.`pay_grade_name` AS `sal_paygrade`,
`salary_record_temp`.`currency_name` AS `sal_currency`,
`salary_record_temp`.`eeo_paygrade_name` AS `sal_eeo_paygrade`
FROM
    (SELECT `sr`.*, DATE_SUB(min(`sr_temp`.`effective_date`), INTERVAL 1 DAY) as `next_salary_record_effective_date` FROM 
        (WITH `sal_rec_with_row_num` AS (
            SELECT *, ROW_NUMBER() OVER (PARTITION BY `employee_number`, `effective_date` ORDER BY `id` DESC) AS `rn`
            FROM `ohrm_salary_record_history` 
        )
        SELECT *
        FROM `sal_rec_with_row_num` 
        WHERE `rn` = 1) as `sr`  
    LEFT JOIN `ohrm_salary_record_history` as `sr_temp` ON (`sr`.`effective_date` IS NOT NULL AND `sr`.`employee_number` = `sr_temp`.`employee_number` AND `sr_temp`.`effective_date`> `sr`.`effective_date`) GROUP BY `sr`.`employee_number`, `sr`.`effective_date`) as `salary_record_temp` -- Get latest history record when having multiple records on same effective date
LEFT JOIN
    (WITH `termination_records` AS (
        SELECT * FROM `ohrm_emp_termination` WHERE `termination_date` <= `reactivation_date` OR `reactivation_date` IS NULL
    )
    SELECT `emp_number`, `termination_date` as date, "Terminate" as action FROM `termination_records` UNION (SELECT `emp_number`, `reactivation_date` as date, "Reactivate" as action FROM `termination_records` WHERE `reactivation_date` IS NOT NULL)) AS `termination_record` -- Get termination date & reactivation date in two rows
ON (`termination_record`.`emp_number` = `salary_record_temp`.`employee_number` AND `salary_record_temp`.`effective_date` <= `termination_record`.`date` AND (`salary_record_temp`.`next_salary_record_effective_date` IS NULL OR `salary_record_temp`.`next_salary_record_effective_date` >= `termination_record`.`date`))
LEFT JOIN `ohrm_pim_history_event` ON `salary_record_temp`.`event_id` = `ohrm_pim_history_event`.`id`;


CREATE VIEW `v_pbi_vacancy_v1` AS SELECT 
pbi_generate_entity_key(`ohrm_job_vacancy`.`id`,'vac_key_') AS `vacancy_key`,
`ohrm_job_vacancy`.`name` AS `vac_name`,
`ohrm_job_title`.`job_title` AS `vac_job_title`,
`ohrm_job_vacancy`.`no_of_positions` as  `vac_count`,
`ohrm_job_vacancy_status`.`name` AS `vac_status`,
`ohrm_job_vacancy`.`defined_time` as `vac_creation_date`,
`ohrm_job_vacancy`.`published_time` as `vac_published_date`,
pbi_generate_entity_key(`ohrm_job_vacancy`.`location_id`,'loc_key_') AS `location_key`,
`ohrm_subunit`.`name` AS `vac_subunit`,
COUNT(`ohrm_hiring_managers`.`employee_id`) AS `vac_hiring_manager_count`,
GROUP_CONCAT(pbi_generate_entity_key(`ohrm_hiring_managers`.`employee_id`,'emp_key_') ORDER BY `ohrm_hiring_managers`.`employee_id` SEPARATOR '|') AS `vac_hiring_manager_list`
FROM `ohrm_job_vacancy` 
LEFT JOIN `ohrm_job_title` ON `ohrm_job_vacancy`.`job_title_code` = `ohrm_job_title`.`id` 
LEFT JOIN `ohrm_subunit`ON `ohrm_job_vacancy`.`sub_unit_id` = `ohrm_subunit`.`id` 
LEFT JOIN `ohrm_hiring_managers` ON `ohrm_hiring_managers`.`vacancy_id` = `ohrm_job_vacancy`.`id` 
LEFT JOIN `ohrm_job_vacancy_status` ON `ohrm_job_vacancy_status`.`status` = `ohrm_job_vacancy`.`status`
WHERE `ohrm_job_vacancy`. `is_template` IS NULL
GROUP BY `ohrm_job_vacancy`.`id`;


CREATE VIEW `v_pbi_candidate_application_v1` AS SELECT 
`ohrm_job_candidate`.`id` AS `can_app_id`,
pbi_generate_entity_key(`ohrm_job_candidate_vacancy`.`vacancy_id`,'vac_key_') AS `vacancy_key`,
`ohrm_job_candidate`.`date_of_application` AS `can_app_application_date`,
`ohrm_job_candidate`.`first_name` AS `can_app_firstname`,
`ohrm_job_candidate`.`middle_name` AS `can_app_middlename`,
`ohrm_job_candidate`.`last_name` AS `can_app_lastname`,
`ohrm_job_candidate`.`email` AS `can_app_email`,
`ohrm_job_candidate`.`contact_number` AS `can_app_mobile`,
`ohrm_job_candidate`.`is_hired` AS `can_app_hired`,
GROUP_CONCAT(CASE WHEN `ohrm_job_candidate_vacancy`.`vacancy_id` IS NULL then "Application Received" WHEN `ohrm_job_candidate_vacancy`.`deleted_at` IS NULL THEN `vacancy_event`.`event_title` ELSE "Rejected" END ORDER BY `vacancy_event`.`id` SEPARATOR "|") AS `can_app_application_stage` -- handle parallel stages
FROM 
    (SELECT * FROM `ohrm_job_candidate` WHERE `deleted_at` IS NULL) AS `ohrm_job_candidate`
LEFT JOIN `ohrm_job_candidate_vacancy` 
    ON `ohrm_job_candidate`.`id` = `ohrm_job_candidate_vacancy`.`candidate_id` AND `ohrm_job_candidate_vacancy`.`deleted_at` IS NULL
LEFT JOIN 
    (SELECT `ohrm_vacancy_event`.`id`, `ohrm_candidate_vacancy_event`.`vacancy_candidate_id`, `ohrm_vacancy_event`.`event_title` FROM `ohrm_candidate_vacancy_event` 
    INNER JOIN `ohrm_candidate_event_state` ON `ohrm_candidate_event_state`.`candidate_vacancy_event_id` = `ohrm_candidate_vacancy_event`.`id` AND `ohrm_candidate_event_state`.`active` = 1 
    INNER JOIN `ohrm_vacancy_event` ON `ohrm_vacancy_event`.`id` = `ohrm_candidate_vacancy_event`.`event_id`
    ) AS `vacancy_event`
ON  `ohrm_job_candidate_vacancy`.`id` = `vacancy_event`.`vacancy_candidate_id`
GROUP BY `ohrm_job_candidate`.`id`;


CREATE VIEW `v_pbi_user_account_v1` AS SELECT 
pbi_generate_entity_key(`ohrm_user`.`id`,'user_key_') AS `user_account_key`,
`ohrm_user`.`user_name` AS `username`,
pbi_generate_entity_key(`ohrm_user`.`emp_number`,'emp_key_') as `employee_key`,
CASE WHEN `ohrm_user`.`status` THEN "Enabled" ELSE "Disabled" END AS `account_status`,
MAX(CASE WHEN `ohrm_user_role_type`.`name` = "ESS" THEN `ohrm_user_role`.`display_name` END) "account_ess_role",
MAX(CASE WHEN `ohrm_user_role_type`.`name` = "Supervisor" THEN `ohrm_user_role`.`display_name` END) "account_supervisor_role",
MAX(CASE WHEN `ohrm_user_role_type`.`name` = "Admin" THEN `ohrm_user_role`.`display_name` END) "account_admin_role"
FROM (SELECT * FROM `ohrm_user` WHERE `deleted` = 0 and `purged` = 0) AS `ohrm_user`
LEFT JOIN `ohrm_user_user_role` ON `ohrm_user_user_role`.`user_id` = `ohrm_user`.`id` 
LEFT JOIN `ohrm_user_role` ON `ohrm_user_role`.`id` = `ohrm_user_user_role`.`user_role_id` 
LEFT JOIN `ohrm_user_role_type` ON `ohrm_user_role_type`.`id` = `ohrm_user_user_role`.`user_role_type_id` 
GROUP BY `ohrm_user`.`id`; 


CREATE VIEW `v_pbi_login_v1` AS SELECT 
`id` AS `login_id`,
pbi_generate_entity_key(`user_id`,'user_key_') AS `user_account_key`,
`login_time` AS `login_time`
FROM `ohrm_login`;


CREATE VIEW `v_pbi_public_holidays_v1` AS SELECT
`holiday_with_recurring_dates`.`holiday_id`,
`holiday_with_recurring_dates`.`holiday_name`,
IF(`holiday_with_recurring_dates`.`formatted_date` IS NULL, `holiday_with_recurring_dates`.`holiday_date`,`holiday_with_recurring_dates`.`formatted_date`) AS `holiday_date`,
`holiday_with_recurring_dates`.`location_key`,
IF(`holiday_with_recurring_dates`.`length` = 0,"Full Day", "Half Day") AS `holiday_length`
FROM 
    (SELECT 
    `holiday`.*,
    date(`holiday`.`holiday_date`) + INTERVAL TIMESTAMPDIFF(YEAR, `holiday`.`holiday_date`, CONCAT(years.year, '-12-31')) YEAR AS `formatted_date`
    FROM
        (SELECT 
        `ohrm_holiday`.`id` AS `holiday_id`,
        `ohrm_holiday`.`description` AS `holiday_name`,
        `ohrm_holiday`.`date` AS `holiday_date`,
        `ohrm_holiday`.`length`,
        pbi_generate_entity_key(`ohrm_holiday_location`.`location_id`,'loc_key_') AS `location_key`,
        `ohrm_holiday`.`recurring`
        FROM `ohrm_holiday` 
        LEFT JOIN `ohrm_holiday_location` ON `ohrm_holiday_location`.`holiday_id` = `ohrm_holiday`.`id`) AS `holiday`
    LEFT JOIN 
    (
        WITH recursive cte (n) AS (
            SELECT (SELECT MIN(YEAR(date)) FROM ohrm_holiday WHERE `recurring` = 1)
            UNION ALL
            SELECT n + 1
            FROM cte
            WHERE n + 1 <= YEAR(CURDATE()) + 10
        ) SELECT n as year FROM `cte`
    ) AS `years` ON (YEAR(`holiday`.`holiday_date`) <= `years`.`year` AND `holiday`.`recurring` =1)) AS `holiday_with_recurring_dates`
WHERE DAY(`holiday_with_recurring_dates`.`holiday_date`) = DAY(`holiday_with_recurring_dates`.`formatted_date`) OR `holiday_with_recurring_dates`.`formatted_date` IS NULL ORDER BY `holiday_name`, `holiday_date`;


CREATE VIEW `v_pbi_timesheet_v1` AS SELECT 
`ohrm_timesheet_item`.`timesheet_item_id` AS `time_id`,
pbi_generate_entity_key(`ohrm_timesheet`.`employee_id`,'emp_key_') as `employee_key`,
CAST(CONCAT(`ohrm_timesheet_item`.`date`, ' ',IF(`ohrm_timesheet_item`.`start_time` IS NULL,'00:00:00',`ohrm_timesheet_item`.`start_time`)) AS DATETIME) AS `time_start_time`,
CAST(CONCAT(`ohrm_timesheet_item`.date, ' ',IF(`ohrm_timesheet_item`.`end_time` IS NULL,'00:00:00',`ohrm_timesheet_item`.`end_time`)) AS DATETIME) AS `time_end_time`,
ROUND(`ohrm_timesheet_item`.`duration`/60,2) AS `time_duration`, -- `ohrm_timesheet_item`.`duration` is in seconds, exposed to pbi in minutes
`ohrm_timesheet`.`state` AS `time_approval_state`,
`ohrm_project`.`name` AS `time_project`,
`ohrm_job_activity`.`name` AS `time_activity`
 FROM 
`ohrm_timesheet_item` 
LEFT JOIN `ohrm_timesheet` ON `ohrm_timesheet`.`timesheet_id` = `ohrm_timesheet_item`.`timesheet_id`
LEFT JOIN `ohrm_project` ON `ohrm_project`.`project_id` = `ohrm_timesheet_item`.`project_id`
LEFT JOIN `ohrm_job_activity` ON `ohrm_job_activity`.`activity_id` = `ohrm_timesheet_item`.`activity_id`;


CREATE VIEW `v_pbi_location_v1` AS SELECT 
pbi_generate_entity_key(`id`,'loc_key_') AS `location_key`,
`name` AS `loc_name`,
`country_code` AS `loc_country_code`,
`time_zone` AS `loc_timezone`,
`province` AS `loc_province`,
`city` AS `loc_city`,
`address` AS `loc_address`,
`zip_code` AS `loc_zipcode`,
`phone` AS `loc_phone`
FROM `ohrm_location`;


CREATE VIEW `v_pbi_leave_entitlement_v1` AS SELECT 
`e`.`id` AS `entitlement_id`,
pbi_generate_entity_key(`emp_number`,'emp_key_') AS `employee_key`,
`ohrm_leave_type`.`name` AS `entitlement_leave_type`,
`et`.`name` AS `entitlement_type`,
CAST(`credited_date` as date) AS `entitlement_credited_date`,
CAST(`from_date` as date) AS `entitlement_valid_from`,
CAST(`to_date` as date) AS `entitlement_valid_to`,
CASE WHEN `ohrm_leave_type`.`hour_based` = 0 THEN CAST(ROUND(`length`, (SELECT value from ohrm_config where property = 'leave.hour_based.day_decimals')) AS DECIMAL(25, 9)) ELSE NULL END AS `entitlement_days`,
CASE WHEN `ohrm_leave_type`.`hour_based` = 0 THEN CAST(ROUND(`used`, (SELECT value from ohrm_config where property = 'leave.hour_based.day_decimals')) AS DECIMAL(25, 9)) ELSE NULL END AS `entitlement_used_days`,
CASE WHEN `ohrm_leave_type`.`hour_based` = 1 THEN CAST(ROUND(`length`/60, (SELECT value from ohrm_config where property = 'leave.hour_based.hour_decimals')) AS DECIMAL(29, 13)) ELSE NULL END AS `entitlement_hours`,
CASE WHEN `ohrm_leave_type`.`hour_based` = 1 THEN CAST(ROUND(`used`/60, (SELECT value from ohrm_config where property = 'leave.hour_based.hour_decimals')) AS DECIMAL(29, 13)) ELSE NULL END AS `entitlement_used_hours`
FROM `ohrm_leave_entitlement` `e`
LEFT JOIN `ohrm_leave_type` ON `ohrm_leave_type`.`id` = `e`.`leave_type_id`
LEFT JOIN `ohrm_leave_entitlement_type` `et` ON `et`.`id` = `e`.`entitlement_type` WHERE `e`.`deleted` = 0;


CREATE VIEW `v_pbi_goals_v1` AS SELECT 
`g`.`id` AS `goal_id`,
pbi_generate_entity_key(`g`.`emp_number`,'emp_key_') AS `employee_key`,
`g`.`name` AS `goal_name`,
`ohrm_goal_priority`.`name` AS `goal_priority`,
CAST(`g`.`created_date` as date) AS `goal_creation_date`,
`g`.`due_date` AS `goal_due_date`,
CAST(`g`.`last_updated_at` as date) AS `goal_last_updated_date`,
`ohrm_goal_category`.`display_name` AS `goal_category`,
`ohrm_goal_purpose`.`name` AS `goal_purpose`,
(SELECT `parent`.`id` FROM `ohrm_performance_goal` AS `parent` WHERE `g`.`root_id` = `parent`.`root_id` AND `parent`.`lft` < `g`.`lft` AND `parent`.`rgt` > `g`.`rgt` AND `parent`.`level` = `g`.`level`-1) AS `goal_parent_goal_id`,
`ohrm_goal_status`.`name` AS `goal_status`,
`ohrm_goal_level`.`name` AS `goal_level`,
`g`.`percentage_completion` AS `goal_status_percentage`,
GROUP_CONCAT(`eval_group`.`group_alias`) AS `goal_evaluator_groups`,
`kr_metric`.`unit_of_measurement` AS `goal_unit_of_measurement`,
`kr_metric`.`start_value` AS `goal_start_value`,
`kr_metric`.`current_value` AS `goal_current_value`,
`kr_metric`.`target_value` AS `goal_target_value`
FROM `ohrm_performance_goal` `g`
LEFT JOIN `ohrm_goal_priority` ON `ohrm_goal_priority`.`id` = `g`.`goal_priority_id`
LEFT JOIN `ohrm_goal_category` ON `ohrm_goal_category`.`id` = `g`.`goal_category_id`
LEFT JOIN `ohrm_goal_purpose` ON `ohrm_goal_purpose`.`id` = `g`.`goal_purpose_id`
LEFT JOIN `ohrm_goal_status` ON `ohrm_goal_status`.`state_id` = `g`.`status_id`
LEFT JOIN `ohrm_goal_level` ON `ohrm_goal_level`.`id` = `g`.`goal_level_id`
LEFT JOIN `ohrm_performance_goal_evaluator_group` `goal_eval_group` ON `goal_eval_group`.`goal_id` = `g`.`id`
LEFT JOIN `ohrm_performance_evaluator_group` AS `eval_group` ON `eval_group`.`id` = `goal_eval_group`.`evaluator_group_id` 
LEFT JOIN `ohrm_performance_key_result_metric` AS `kr_metric` ON `kr_metric`.`id` = `g`.`metric_id` 
GROUP BY `g`.`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`;
