SELECT pd.person_id,
  p.company_nr,
  pd.date_day,
  pd.trf_dhr,
  wc.short_code,
  pd.ref_number,
  vpc.vo_person_category_id,
  pdd.is_locked,
  LPAD(TO_CHAR(p.company_nr), 6, '0') -- person number

  || TO_CHAR(pd.date_day, 'YYMMDD') -- date of the activity

  || RPAD  (wc.short_code,9, ' ') ||' +'    -- work code

  || LPAD(DECODE(wc.short_code,'P',SUM(pd.TRF_DHR) OVER (PARTITION BY pd.date_day,pd.person_id) * 100,pd.TRF_DHR * 100), 9, '0')

  || LPAD('0', 9, '0') -- unit value (always 9 x 0) + percentage (always 5 x 0)

  || LPAD('0', 5,'0') -- percentage (always 5 x 0)

  || RPAD(' ', 10, ' ') -- cost center reference

  || LPAD(' ', 6, ' ') -- project reference

  || LPAD(' ', 5, ' ') -- activity reference

  || LPAD('51101', 5,'0') -- management State level 1

  || LPAD(NVL(pd.REF_NUMBER,'0'),5,'0') -- management State level 2

  || LPAD(' ', 5,'0') -- management State level 3

  || LPAD(' ', 1, ' ')  -- reason

  || LPAD(' ', 1, ' ')  -- empty character

  || LPAD(' ', 9, ' ') -- activity code 2

  || LPAD('0', 5, '0')

  || LPAD(DECODE(wc.work_code_std_category_id,1,NVL(pdd.declared_vehicle_km,0) + NVL(pdd.declared_private_km,0),'0'), 5, '0') -- KM values

  || LPAD(DECODE(wc.work_code_std_category_id,1,NVL(pdd.declared_vehicle_km,0),'0'), 5, '0') -- frais de deplacement KM value

  || LPAD(DECODE(wc.work_code_std_category_id,1,DECODE(pdd.day_journey_role,'D','23','13'),'0'), 2, '0') -- mobility code

  || LPAD(DECODE(wc.work_code_std_category_id,1,DECODE(NVL(pdd.declared_private_km,'0'),'0','73'),'0'), 2, '0')  -- frais de deplacement code

  || LPAD(' ', 5, ' ')
  || LPAD(' ',10,' ')
  str
FROM pd_trf_day pd,
  person p,
  pd_day pdd,
  work_code wc,
  vo_person_category vpc
WHERE pd.person_id                      = pdd.person_id
AND pdd.person_id = p.person_id
AND p.vo_person_category_id           = vpc.vo_person_category_id
AND pd.work_code_id                     = wc.work_code_id
AND p_util.to_num(wc.short_code)       IS NOT NULL
AND pd.trf_dhr                         IS NOT NULL
AND wc.work_code_std_type_id = 1
UNION
SELECT pd.person_id,
  p.company_nr,
  pd.date_day,
  pd.trf_dhr,
  wc.short_code,
  pd.ref_number,
  vpc.vo_person_category_id,
  pdd.is_locked,
  LPAD(TO_CHAR(p.company_nr), 6, '0') -- person number

  || TO_CHAR(pd.date_day, 'YYMMDD') -- date of the activity

  || RPAD  (wc.short_code,9, ' ') ||' +'    -- work code

  || LPAD(NVL (7.5 * 100, 0), 9, '0')

  || LPAD('0', 9, '0') -- unit value (always 9 x 0) + percentage (always 5 x 0)

  || LPAD('0', 5,'0') -- percentage (always 5 x 0)

  || RPAD(' ', 10, ' ') -- cost center reference

  || LPAD(' ', 6, ' ') -- project reference

  || LPAD(' ', 5, ' ') -- activity reference

  || LPAD('51101', 5,'0') -- management State level 1

  || LPAD(NVL(pd.REF_NUMBER,'0'),5,'0') -- management State level 2

  || LPAD(' ', 5,'0') -- management State level 3

  || LPAD(' ', 1, ' ')  -- reason

  || LPAD(' ', 1, ' ')  -- empty character

  || LPAD(' ', 9, ' ') -- activity code 2

  || LPAD('0', 5, '0')

  || LPAD(DECODE(wc.work_code_std_category_id,1,NVL(pdd.declared_vehicle_km,0) + NVL(pdd.declared_private_km,0),'0'), 5, '0') -- KM values

  || LPAD(DECODE(wc.work_code_std_category_id,1,NVL(pdd.declared_vehicle_km,0),'0'), 5, '0') -- frais de deplacement KM value

  || LPAD(DECODE(wc.work_code_std_category_id,1,DECODE(pdd.day_journey_role,'D','23','13'),'0'), 2, '0') -- mobility code

  || LPAD(DECODE(wc.work_code_std_category_id,1,DECODE(NVL(pdd.declared_private_km,'0'),'0','73'),'0'), 2, '0')  -- frais de deplacement code

  || LPAD(' ', 5, ' ')
  || LPAD(' ',10,' ')
  str
FROM pd_trf_day pd,
  person p,
  pd_day pdd,
  work_code wc,
  vo_person_category vpc
WHERE pd.person_id                      = pdd.person_id
AND pdd.person_id = p.person_id
AND p.vo_person_category_id           = vpc.vo_person_category_id
AND pd.work_code_id                     = wc.work_code_id
AND p_util.to_num(wc.short_code)       IS NOT NULL
AND pd.trf_dhr                         IS NOT NULL
AND NVL(pdd.maintenance,'N') = 'Y'
AND wc.short_code = '441'
AND wc.work_code_std_type_id = 1
ORDER BY 1,3;