Advertisement
psi_mmobile

Untitled

Oct 19th, 2020
2,668
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 3.72 KB | None | 0 0
  1. SELECT * FROM(
  2. SELECT gui_users.operation_center_id,
  3.       gui_users.gui_user_id,
  4.       person_data.person_id,
  5.       person_data.person_data_id,
  6.       vehicle_data.vehicle_data_id,
  7.       event_date,
  8.       TO_CHAR(touserdate(person_data.event_date,oc.timezone), 'dd/mm/yyyy HH24:MI') event_date_str,
  9.       person_data.event_type event_type_hard,
  10.       NULL event_name,
  11.       v.vehicle_id,
  12.       v.numberplate vehicle_numberplate,
  13.       v.company_nr vehicle_company_nr,
  14.       DECODE (v.vehicle_id, NULL, GETFIXEDUNITNAME(vehicle_data.remote_unit_id), TRIM(DECODE(v.company_nr, NULL, '', v.company_nr
  15.       || ' - ')
  16.       || v.numberplate)) VEHICLE_NAME,
  17.       DECODE(str.event_type_id, 103, DECODE(p_person.getKeyCodeInfo(vehicle_data.vehicle_data_id), ' ', 'key_manual', str.graphical_symbol),DECODE(str.graphical_symbol,NULL,DECODE(person_data.event_type,'mtrs','intervention',NULL),str.graphical_symbol)) graphical_symbol,
  18.       DECODE(str.event_type_id, 103, str.str_value
  19.       ||': '
  20.       ||p_person.getKeyCodeInfo(vehicle_data.vehicle_data_id), 114, str.str_value, DECODE(str.str_value,NULL,DECODE(person_data.event_type,'mtrs','Easytraxx',NULL),str.str_value)) event_type,
  21.       vehicle_data.total_distance + NVL(v.ru_install_km,0) total_km,
  22.       TO_CHAR(vehicle_data.hour_counter/60, '999999') total_hr,
  23.       vehicle_data.rg_street_nr
  24.       || DECODE(vehicle_data.rg_street_nr,NULL,'',', ')
  25.       || vehicle_data.rg_street
  26.       || DECODE(vehicle_data.rg_street,NULL,'',' - ')
  27.       || vehicle_data.rg_post_code
  28.       || ' '
  29.       || vehicle_data.rg_location
  30.       || ' '
  31.       || vehicle_data.rg_country VEHICLE_DATA_RG_ADRESS,
  32.       vehicle_data.speed_over_ground VEHICLE_DATA_SPEED,
  33.       DECODE(vehicle_data.vehicle_id, NULL, p_vd_poi.getHistPersonDataPoiName(oc.operation_center_id, person_data.person_data_id), p_vd_poi.getHistVehicleDataPoiName(oc.operation_center_id, vehicle_data.vehicle_data_id)) poi_name,
  34.       DECODE(vehicle_data.vehicle_id, NULL, p_vd_poi.getHistPersonDataPoiId(oc.operation_center_id, person_data.PERSON_DATA_ID), p_vd_poi.getHistVehicleDataPoiId(oc.operation_center_id, vehicle_data.VEHICLE_DATA_ID)) AS POI_ID_LIST,
  35.       vehicle_data.wgs84_latitude,
  36.       vehicle_data.wgs84_longitude,
  37.       person_data.comments,
  38.       vehicle_data.wgs84_latitude AS Wgs84Latitude,
  39.       vehicle_data.wgs84_longitude AS wgs84longitude,
  40.       str.event_type_id
  41.     FROM operation_center oc,
  42.       gui_users,
  43.       gu_person,
  44.       hist_person_data person_data,
  45.       of_arch.hist_vehicle_data_2017 vehicle_data,
  46.       vehicle v,
  47.       (SELECT
  48.         et.event_type_id,
  49.         ocet.operation_center_id,
  50.         str.language,
  51.         str_value,
  52.         et.graphical_symbol
  53.         FROM
  54.         operation_center oc,
  55.         gui_users,
  56.         event_type et,
  57.         oc_event_type ocet,
  58.         str
  59.         WHERE ocet.event_type_id    = et.event_type_id
  60.         AND ocet.str_id              = str.str_id
  61.         AND oc.operation_center_id   = gui_users.operation_center_id
  62.         AND gui_users.language = str.language
  63.         AND ocet.operation_center_id = oc.operation_center_id
  64.         AND gui_users.gui_user_id = 101712
  65.       ) str
  66.     WHERE oc.operation_center_id   = gui_users.operation_center_id
  67.     AND gui_users.gui_user_id      = gu_person.gui_user_id
  68.     AND gu_person.person_id        = person_data.person_id
  69.     AND person_data.vehicle_data_id = vehicle_data.vehicle_data_id(+)
  70.     AND vehicle_data.vehicle_id = v.vehicle_id(+)
  71.     AND person_data.event_type    IS NOT NULL
  72.     AND vehicle_data.event_type_id = str.event_type_id(+)
  73.     AND gui_users.gui_user_id=101712
  74. )
  75.     WHERE PERSON_ID = 107162
  76. --    and to_char(event_date,'YYYY') = '2019'
  77. ORDER BY event_date ASC, person_data_id ASC;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement