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