SELECT name,
       description,
       unit_name,
       is_generic,
       order_num,
       allow_absence_request,
       person_id,
       lm_date,
       lm_name,
       from_date,
       is_active,
       work_code_hr_counter_id,
       actual_value,
       reference_value,
       "{}counter_name_str_map",
       "{}unit_name_str_map"
FROM (
(SELECT NVL(s.str_value,wcc.name) name,
       wcc.description,
       NVL(uom_s.str_value, uom.short_name) unit_name,
       wcc.is_generic,
       wcc.order_num,
       wcc.allow_absence_request,
       (SELECT json_objectagg(s2.language VALUE s2.str_value) FROM str s2 WHERE s2.str_id = wcc.str_id) "{}counter_name_str_map",
       (SELECT json_objectagg(s3.language VALUE s3.str_value) FROM str s3 WHERE s3.str_id = uom.str_id) "{}unit_name_str_map",
       pwcc.person_id,
       pwcc.lm_date,
       pwcc.lm_name,
       pwcc.from_date,
       pwcc.is_active,
       pwcc.work_code_hr_counter_id,
       P_VOPC_ABSENCE_MANAGMENT.f_compute_person_counter(pwcc.person_id, pwcc.work_code_hr_counter_id) actual_value,
       pwcc.counter_value reference_value
FROM person_work_code_counter pwcc, work_code_hr_counter wcc, unit_of_measurement uom, gui_users gu, str s, str uom_s, gu_person gp
WHERE pwcc.person_id = NVL(:person_id, pwcc.person_id)
AND gu.gui_user_id = gp.gui_user_id
AND gp.person_id = pwcc.person_id
AND pwcc.work_code_hr_counter_id = wcc.work_code_hr_counter_id
AND gu.gui_user_id = :gui_user_id
AND wcc.unit_of_measurement_id = uom.unit_of_measurement_id
AND uom.str_id = uom_s.str_id (+)
AND wcc.str_id = s.str_id (+)
AND gu.language = s.language (+)
AND gu.language = uom_s.language (+)
AND NVL(wcc.is_generic, 'N') = DECODE(:is_generic, 'Y', 'Y', NVL(wcc.is_generic, 'N')))

UNION

(SELECT NVL(s.str_value,wcc.name) name,
    wcc.description,
    NVL(uom_s.str_value, uom.short_name) unit_name,
    wcc.is_generic,
    wcc.order_num,
    wcc.allow_absence_request,
    (SELECT json_objectagg(s2.language VALUE s2.str_value) FROM str s2 WHERE s2.str_id = wcc.str_id) "{}counter_name_str_map",
    (SELECT json_objectagg(s3.language VALUE s3.str_value) FROM str s3 WHERE s3.str_id = uom.str_id) "{}unit_name_str_map",
    pwcc.person_id,
    pwcc.lm_date,
    pwcc.lm_name,
    pwcc.from_date,
    pwcc.is_active,
    pwcc.work_code_hr_counter_id,
    P_VOPC_ABSENCE_MANAGMENT.f_compute_person_counter(pwcc.person_id, pwcc.work_code_hr_counter_id) actual_value,
    pwcc.counter_value reference_value
FROM person_work_code_counter pwcc, work_code_hr_counter wcc, unit_of_measurement uom, gui_users gu, str s, str uom_s, gu_person gp
WHERE gu.gui_user_id = gp.gui_user_id
AND gp.person_id = pwcc.person_id
AND pwcc.work_code_hr_counter_id = wcc.work_code_hr_counter_id
AND gu.profiled_person_id = :user_person_id
AND pwcc.person_id != :user_person_id
AND wcc.unit_of_measurement_id = uom.unit_of_measurement_id
AND uom.str_id = uom_s.str_id (+)
AND wcc.str_id = s.str_id (+)
AND gu.language = s.language (+)
AND gu.language = uom_s.language (+)
AND NVL(wcc.is_generic, 'N') = DECODE(:is_generic, 'Y', 'Y', NVL(wcc.is_generic, 'N')))

UNION

(SELECT NVL(s.str_value,wcc.name) name,
       wcc.description,
       NVL(uom_s.str_value, uom.short_name) unit_name,
       wcc.is_generic,
       wcc.order_num,
       wcc.allow_absence_request,
       (SELECT json_objectagg(s2.language VALUE s2.str_value) FROM str s2 WHERE s2.str_id = wcc.str_id) "{}counter_name_str_map",
       (SELECT json_objectagg(s3.language VALUE s3.str_value) FROM str s3 WHERE s3.str_id = uom.str_id) "{}unit_name_str_map",
       pwcc.person_id,
       pwcc.lm_date,
       pwcc.lm_name,
       pwcc.from_date,
       pwcc.is_active,
       pwcc.work_code_hr_counter_id,
       P_VOPC_ABSENCE_MANAGMENT.f_compute_person_counter(pwcc.person_id, pwcc.work_code_hr_counter_id) actual_value,
       pwcc.counter_value reference_value
FROM person_work_code_counter pwcc, work_code_hr_counter wcc, unit_of_measurement uom, gui_users gu, str s, str uom_s
WHERE (pwcc.person_id = :person_id OR :user_person_id = pwcc.person_id)
AND pwcc.work_code_hr_counter_id = wcc.work_code_hr_counter_id
AND gu.profiled_person_id = :user_person_id
AND wcc.unit_of_measurement_id = uom.unit_of_measurement_id
AND uom.str_id = uom_s.str_id (+)
AND wcc.str_id = s.str_id (+)
AND gu.language = s.language (+)
AND gu.language = uom_s.language (+)
AND NVL(wcc.is_generic, 'N') = DECODE(:is_generic, 'Y', 'Y', NVL(wcc.is_generic, 'N')))
)
ORDER BY person_id, order_num