SELECT pd.person_id,
  p.vo_person_category_id,
  pd.is_locked,
  vo.contact_msisdn as "Nummer werkgever",
  replace(p.company_nr,'HBB') as "Nummer werknemer",
  vo.company_name as "Naam werkgever",
  p.last_name || ' ' || p.first_name as "Naam werknemer",
  decode(wc.short_code,'P',decode(pdd.day_journey_role,'P','P','D ','CH','DA','CH','P'),wc.short_code) as "Looncode",
  decode(wc.work_code_std_category_id,1,p_wbs.getlvl0WbsRefNumber(w.wbs_id),2,'AFWEZIG') as "Werf",
  pd.date_day as "Datum",
  replace(to_char(extract (hour from numtodsinterval(sum(pd.TRF_DHR), 'HOUR' )),'00'),' ','') as "Uren",
  replace(to_char(extract (minute from numtodsinterval(sum(pd.TRF_DHR), 'HOUR' )),'00'),' ','') as "Minuten",
--  decode(wc.short_code,'P',nvl(pdd.declared_vehicle_km,0),'0') as "Gerapporteerde Km",
  case when wc.short_code = 'P' or wc.short_code = 'GWH' then nvl(pdd.declared_vehicle_km,0) else 0 end  as "Gerapporteerde Km",
  case when wc.short_code = 'P' or wc.short_code = 'GWH' then nvl(pdd.declared_private_km,0) else 0 end as "Prive Km",
  pd.pd_trf_user_comment as "Commentaar"
FROM pd_trf_day pd,
  person p,
  pd_day pdd,
  work_code wc,
  wbs w,
  vo_person_category vopc,
  vehicle_owner vo
WHERE pd.person_id                      = pdd.person_id
AND pd.date_day = pdd.date_day
AND pdd.person_id = p.person_id
and p.vo_person_category_id = vopc.vo_person_category_id
and vopc.vehicle_owner_id = vo.vehicle_owner_id
AND pd.work_code_id                     = wc.work_code_id
and wc.vehicle_owner_id = vo.vehicle_owner_id
and pd.wbs_id = w.wbs_id(+)
and wc.vehicle_owner_id = 101404
and wc.work_code_std_category_id in (1,2)
and nvl(pd.TRF_DHR,0) > 0
and p.person_status_id = 0
group by pd.person_id,p.vo_person_category_id,pd.is_locked,pd.date_day,w.ref_number,p.company_nr,wc.short_code,vo.contact_msisdn,vo.company_name,p.last_name || ' ' || p.first_name,
decode(wc.short_code,'P',decode(pdd.day_journey_role,'P','P','D ','CH','DA','CH','P'),wc.short_code),decode(wc.work_code_std_category_id,1,p_wbs.getlvl0WbsRefNumber(w.wbs_id),2,'AFWEZIG'),
case when wc.short_code = 'P' or wc.short_code = 'GWH' then nvl(pdd.declared_vehicle_km,0) else 0 end,
case when wc.short_code = 'P' or wc.short_code = 'GWH' then nvl(pdd.declared_private_km,0) else 0 end,
pd.pd_trf_user_comment
order by pd.date_day,"Nummer werknemer";