CREATE OR REPLACE FORCE EDITIONABLE VIEW "OF_OWNER"."V_EXPORT_EBLOX_HOOYBERGHS" ("PERSON_ID", "VO_PERSON_CATEGORY_ID", "IS_LOCKED", "DATE_DAY", "Nummer werkgever", "Nummer werknemer", "Naam werkgever", "Naam werknemer", "Looncode", "Werf", "Datum", "Uren", "Minuten","Commentaar", "Gerapporteerde Km", "Prive Km","SHORT_CODE") AS
WITH hr AS
  (SELECT pd.person_id,
    pd.date_day,
    pd.is_locked,
    DECODE(wc.short_code,'P',DECODE(pdd.day_journey_role,'P','P','D ','CH','DA','CH','P'),wc.short_code) AS looncode,
    wc.short_code,
    wc.work_code_std_category_id,
    pd.wbs_id,
    SUM(pd.TRF_DHR) declared_hr_sum,
    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 wc.work_code_std_category_id IN (1,2)
  AND NVL(pd.TRF_DHR,0)             > 0
  AND wc.vehicle_owner_id           = 101404
  GROUP BY pd.person_id,
    pd.date_day,
    pd.is_locked,
    DECODE(wc.short_code,'P',DECODE(pdd.day_journey_role,'P','P','D ','CH','DA','CH','P'),wc.short_code),
    wc.short_code,
    wc.work_code_std_category_id,
    pd.wbs_id
  )
SELECT hr.person_id,
  p.vo_person_category_id,
  hr.is_locked,
  hr.date_day,
  vo.contact_msisdn                        AS "Nummer werkgever",
  LTRIM(REPLACE(p.company_nr,'HBB'),'0') AS "Nummer werknemer",
  vo.company_name                          AS "Naam werkgever",
  p.last_name
  || ' '
  || p.first_name                                                                                  AS "Naam werknemer",
  hr.looncode                                                                                      AS "Looncode",
  DECODE(hr.work_code_std_category_id,1,p_wbs.getlvl0WbsRefNumber(hr.wbs_id),2,'AFWEZIG')          AS "Werf",
  TO_CHAR ( pd.date_day, 'dd/MM/yyyy' )                                                            AS "Datum",
  REPLACE(TO_CHAR(EXTRACT (HOUR FROM NUMTODSINTERVAL(hr.declared_hr_sum, 'HOUR' )),'00'),' ','')   AS "Uren",
  REPLACE(TO_CHAR(EXTRACT (MINUTE FROM NUMTODSINTERVAL(hr.declared_hr_sum, 'HOUR' )),'00'),' ','') AS "Minuten",
  hr.user_comment                                                                                  AS "Commentaar",
  CASE
    WHEN (short_code                                                                                                                          = 'P'
    OR short_code                                                                                                                             = 'GWH')
    AND (ROW_NUMBER() over (PARTITION BY hr.person_id, hr.date_day ORDER BY DECODE (hr.short_code, 'P', 1, 'GWH', 2, 3), hr.declared_hr_sum ) = 1 )
    THEN NVL(pd.declared_vehicle_km,0)
    ELSE 0
  END AS "Gerapporteerde Km",
  CASE
    WHEN (short_code                                                                                                                         = 'P'
    OR short_code                                                                                                                            = 'GWH')
    AND (ROW_NUMBER() over (PARTITION BY hr.person_id, hr.date_day ORDER BY DECODE (hr.short_code, 'P', 1, 'GWH', 2, 3), hr.declared_hr_sum )= 1)
    THEN NVL(pd.declared_private_km,0)
    ELSE 0
  END "Prive Km",
  hr.short_code
FROM hr,
  pd_day pd,
  person p,
  vo_person_category vpc,
  vehicle_owner vo
WHERE hr.date_day           = pd.date_day
AND hr.person_id            = pd.person_id
AND p.person_id             = pd.person_id
AND p.vo_person_category_id = vpc.vo_person_category_id
AND vpc.vehicle_owner_id    =vo.vehicle_owner_id;