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,
       wc.graphical_symbol,
       wc.work_code_name,
       wc.work_code_std_id
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, work_code wc, person p
WHERE pwcc.person_id = NVL(:person_id, pwcc.person_id)
AND (:person_id IS NULL AND gp.gui_user_id IS NOT NULL OR NVL(:person_id, :user_person_id) IS NOT NULL)
AND gu.gui_user_id = gp.gui_user_id (+)
AND gp.person_id (+) = pwcc.person_id
AND pwcc.person_id = p.person_id
AND p.person_status_id = 0
AND pwcc.work_code_hr_counter_id = wcc.work_code_hr_counter_id
AND (gu.gui_user_id = :gui_user_id OR gu.profiled_person_id = :user_person_id)
AND wcc.unit_of_measurement_id = uom.unit_of_measurement_id
AND wcc.work_code_id = wc.work_code_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'))
AND NVL(wcc.allow_absence_request, 'N') = DECODE(:allow_absence_request, 'Y', 'Y', NVL(wcc.allow_absence_request, 'N'))
AND ((:is_generic= 'Y' AND wc.work_code_std_id = 159 AND p.extra_hr9_paid = 'Y') OR (:is_generic = 'Y' AND wc.work_code_std_id != 159) OR (NVL(:is_generic, 'N') = 'N'))

ORDER BY pwcc.person_id, wcc.order_num