Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- select /*+ INDEX (PERSON_DATA I_PD_GPSFULLDATE)*/
- gu.gui_user_id,
- fu.fixed_unit_id,
- '['||fu.name||']' vehicle_name,
- vd1.gps_full_date,
- vd1.vehicle_data_id event_id,
- touserdate(nvl(vd1.gps_full_date,to_date('1970','YYYY')),oc.timezone) event_date,
- to_char(touserdate(vd1.gps_full_date,oc.timezone),'dd/mm/yyyy HH24:MI') event_date_str,
- str.str_value event_type,
- decode ( ocet.graphical_symbol, 'Key', decode (fu.fixed_unit_usage_id,5, 'key_blue_out', 4, 'key_blue_in', ocet.graphical_symbol), ocet.graphical_symbol) graphical_symbol,
- null total_km,
- 0 Intervalle_km,
- to_char(vd1.hour_counter/60, '999999') total_hr,
- vd1.rg_street_nr || decode(vd1.rg_street_nr,null,'',', ') || vd1.rg_street || decode(vd1.rg_street,null,'',' - ') || vd1.rg_post_code || ' ' || vd1.rg_location || ' ' || vd1.rg_country VEHICLE_DATA_RG_ADRESS,
- vd1.wgs84_latitude, vd1.wgs84_longitude,
- vd1.speed_over_ground VEHICLE_DATA_SPEED,
- et.event_type_id,
- vd1.ignition,
- p_vd_poi.getPersonDataPoiId(oc.operation_center_id, pd.person_data_id) AS POI_ID_LIST,
- p_vd_poi.getPersonDataPoiName(oc.operation_center_id, pd.person_data_id) poi_name,
- -- null poi_name,
- vd1.vehicle_activity_id,
- person.person_id,
- decode(person.person_id,null,vd1.driver_identifier,DECODE(person.company_nr,NULL,'', person.company_nr
- || ' ')
- || person.last_name
- || ' '
- || person.first_name) person_name,
- pd.home_poi_km,
- vd1.driver_identifier,
- vd1.satellite
- from gui_users gu, operation_center oc, vehicle_data vd1, remote_unit ru, fixed_unit fu,
- event_type et, oc_event_type ocet, str, person_data pd, person
- where gu.operation_center_id = oc.operation_center_id
- --and vd1.vehicle_id is null
- and vd1.remote_unit_id = ru.remote_unit_id
- and fu.remote_unit_id = ru.remote_unit_id
- and vd1.event_type_id = et.event_type_id
- and ocet.operation_center_id = oc.operation_center_id
- and ocet.event_type_id = et.event_type_id
- and ocet.str_id = str.str_id
- and gu.language = str.language
- and vd1.vehicle_data_id = pd.vehicle_data_id(+)
- and pd.person_id = person.person_id(+)
- and fu.fixed_unit_id=?
- AND touserdate(vd1.gps_full_date,oc.timezone) >= ?
- AND touserdate(vd1.gps_full_date,oc.timezone) <= ?
- and gu.gui_user_id=?
- and (person.person_id is null or person.person_id in (select person_id from gu_person where gui_user_id=?))
- ORDER BY gps_full_date desc, event_id desc
Advertisement
Add Comment
Please, Sign In to add comment