WITH hr AS
  (SELECT pd.person_id,
    pd.date_day,
    pd.is_locked,
    SUM(CASE WHEN wc.work_code_std_id = 110 THEN pd.TRF_DHR ELSE 0 END) HR_DAY,
    SUM(CASE WHEN wc.work_code_std_id = 114 THEN pd.TRF_DHR ELSE 0 END) HR_NIGHT,
    wc.short_code,
    pd.ref_number,
    listagg (pd.user_comment, '/' ) user_comment
  FROM pd_trf_day pd,
    pd_day pdd,
    work_code wc
  WHERE pd.person_id                = pdd.person_id
  AND pd.date_day                   = pdd.date_day
  AND pd.work_code_id               = wc.work_code_id
  AND pd.TRF_DHR > 0
  AND wc.work_code_std_id IN (110,114)
  GROUP BY pd.person_id,
    pd.date_day,
    pd.is_locked,
    wc.short_code,
    pd.ref_number)
  SELECT hr.person_id,
  hr.date_day,
  hr.is_locked,
  hr.ref_number,
  p.company_nr,
  p.last_name,
  p.first_name,
  q.name,
  TO_CHAR(hr.date_day,'dd/MM/yyyy') DATE_STR,
  hr.hr_day,
  hr.hr_night,
  hr.user_comment
  FROM hr, person p, vo_person_category vopc,pd_day pdd, qualification q
  WHERE hr.person_id = pdd.person_id
  AND pdd.person_id = p.person_id
  AND hr.date_day = pdd.date_day
  AND p.master_qualification_id = q.qualification_id
  ORDER BY 2,4,5,6,7;