CREATE OR REPLACE FORCE EDITIONABLE VIEW "OF_OWNER"."V_AGGREG1_GENERIC_KPD_V2" ("VD_POI_DAY_ID", "DATE_DAY", "VEHICLE_ID", "GUI_USER_ID", "VEHICLE_OWNER_ID", "WORK_DATE", "WORKER_CODE", "ALLOWANCE", "HR", "MOB_CODE", "KM", "ADDRESS", "PROJECT", "WORKER_ACTIVITY", "LEGAL_CATEGORY", "LEGAL_CODE", "MATERIAL_CODE", "MATERIAL_HR", "MATERIAL_ACTIVITY", "LEGAL_CATEGORY2", "LEGAL_CODE2") AS
  SELECT vd_poi_day_id,
    vd_poi_day.date_day,
    vd_poi_day.vehicle_id,
    gu_vehicle.gui_user_id,
    vehicle.vehicle_owner_id,
    TO_CHAR(vd_poi_day.date_day,'DD/MM/YYYY') WORK_DATE,
    null WORKER_CODE,
    null ALLOWANCE,
    null HR,
    null MOB_CODE,
    null KM,
    null ADDRESS,
    vd_poi_day.ref_number PROJECT,
    null WORKER_ACTIVITY,
    null LEGAL_CATEGORY,
    null LEGAL_CODE,
    vehicle.company_nr MATERIAL_CODE,
    trim(to_char(round(vd_poi_day.poi_hr*10)/10, '90.9')) MATERIAL_HR,
    '***********' MATERIAL_ACTIVITY,
    null LEGAL_CATEGORY2,
    null LEGAL_CODE2
  FROM vd_poi_day,
    vehicle,
    oc_day_aggreg,
    gu_vehicle,
    gui_users
  WHERE vd_poi_day.vehicle_id         = vehicle.vehicle_id
  AND vd_poi_day.oc_day_aggreg_id     = oc_day_aggreg.oc_day_aggreg_id
  AND ( (oc_day_aggreg.operation_center_id=101229)                                                  -- Eiffage-Sodemat
     or (oc_day_aggreg.operation_center_id=101738 and oc_day_aggreg.vo_vehicle_category_id=102966 ) -- TRAGECO Engin
     or (oc_day_aggreg.operation_center_id=101129 and oc_day_aggreg.vo_vehicle_category_id=102969 ) -- GEHLEN Engin
     or (oc_day_aggreg.operation_center_id=101176 and oc_day_aggreg.vo_vehicle_category_id=102537 ) -- DEFLANDRE Engin
      )
  and vehicle.vehicle_id = gu_vehicle.vehicle_id
  and gu_vehicle.gui_user_id = gui_users.GUI_USER_ID
  and gui_users.operation_center_id = oc_day_aggreg.operation_center_id
  and nvl(vd_poi_day.invoiced_line,'Y') <> 'N'
  and vd_poi_day.day_first_start_km is not null
--  and date_day=to_date('22/07/2017','DD/MM/YYYY')
--  and gui_users.gui_user_id=101060
  ORDER BY vehicle.company_nr, vd_poi_day.date_day;