Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT VFmxVdPoiDay.VD_POI_DAY_ID,
- VFmxVdPoiDay.VEHICLE_ID,
- VFmxVdPoiDay.POI_ID,
- VFmxVdPoiDay.DATE_DAY,
- VFmxVdPoiDay.SNAPPING_STATUS,
- VFmxVdPoiDay.DAY_FIRST_START_KM,
- VFmxVdPoiDay.POI_FIRST_EVENT_KM,
- VFmxVdPoiDay.POI_LAST_EVENT_KM,
- VFmxVdPoiDay.DAY_LAST_STOP_KM,
- VFmxVdPoiDay.VEHICLE_NAME,
- VFmxVdPoiDay.OPERATION_CENTER_ID,
- VFmxVdPoiDay.OPC_NAME,
- VFmxVdPoiDay.OPC_GRAPHICAL_SYMBOL,
- VFmxVdPoiDay.POI_NAME,
- VFmxVdPoiDay.POI_GRAPHICAL_SYMBOL,
- VFmxVdPoiDay.LOCATION,
- VFmxVdPoiDay.COUNTRY,
- VFmxVdPoiDay.POST_CODE,
- VFmxVdPoiDay.STREET,
- VFmxVdPoiDay.STREET_NR,
- VFmxVdPoiDay.OC_AGGREG_ADMIN_STATUS_ID,
- VFmxVdPoiDay.OC_POI_CATEGORY_ID,
- VFmxVdPoiDay.SNAPPING_COMMENT,
- VFmxVdPoiDay.IS_MODIFIED_BY_USER,
- VFmxVdPoiDay.LM_DATE,
- VFmxVdPoiDay.DAY_NBR_VISIT,
- VFmxVdPoiDay.POI_NBR_VISIT,
- VFmxVdPoiDay.POI_KM,
- VFmxVdPoiDay.DAY_DURATION,
- VFmxVdPoiDay.POI_DURATION,
- VFmxVdPoiDay.POI_SPENT_DURATION,
- VFmxVdPoiDay.DAY_KM,
- VFmxVdPoiDay.DAY_FIRST_START_HR,
- VFmxVdPoiDay.DAY_LAST_STOP_HR,
- VFmxVdPoiDay.POI_FIRST_EVENT_HR,
- VFmxVdPoiDay.POI_LAST_EVENT_HR,
- VFmxVdPoiDay.POI_HR,
- VFmxVdPoiDay.POI_HR_EXTRA,
- VFmxVdPoiDay.DAY_HR,
- VFmxVdPoiDay.DAY_SPEED,
- VFmxVdPoiDay.VEHICLE_OWNER_ID,
- VFmxVdPoiDay.VO_VEHICLE_CATEGORY_ID,
- VFmxVdPoiDay.STATUS_NAME,
- VFmxVdPoiDay.FLAG_COLOUR,
- VFmxVdPoiDay.CAL_COLOUR,
- VFmxVdPoiDay.POI_KM_EXTRA,
- VFmxVdPoiDay.NBR_DAY,
- VFmxVdPoiDay.VEHICLE_CATEGORY_NAME,
- VFmxVdPoiDay.OC_DAY_AGGREG_ID,
- to_number(DAY_SPEED) AS DAY_SPEED_NUM,
- upper(VFmxVdPoiDay.VEHICLE_NAME||' '||VFmxVdPoiDay.DRIVER_PERSON_NAME||' '||VFmxVdPoiDay.STATUS_NAME||' '||VFmxVdPoiDay.POI_NAME||' '||VFmxVdPoiDay.WBS_NAME||' '||VFmxVdPoiDay.DATE_DAY||' '||OperationMode.SHORT_NAME||' '||OperationMode.NAME|| ' '||VFmxVdPoiDay.ALERT_DESCRIPTION||' '||VFmxVdPoiDay.ALERT_NAME||' '||VFmxVdPoiDay.BRAND||' '||VFmxVdPoiDay.CATEGORY_NAME||' '||VFmxVdPoiDay.FILE_PATH||' '||VFmxVdPoiDay.INVOICED_LINE||' '||VFmxVdPoiDay.LAST_DAY_KEY_PERSON_NAME||' '||VFmxVdPoiDay.MODEL||' '||VFmxVdPoiDay.OPC_NAME||' '||VFmxVdPoiDay.REF_NUMBER||' '||VFmxVdPoiDay.SNAPPING_COMMENT||' '||VFmxVdPoiDay.STATUS_NAME||' '||VFmxVdPoiDay.VEHICLE_CATEGORY_NAME) AS IDSTRING,
- VFmxVdPoiDay.LAST_DAY_KEY_PERSON_NAME,
- VFmxVdPoiDay.WBS_ID,
- VFmxVdPoiDay.WBS_NAME,
- OcDayAggreg.OC_CALENDAR_ID,
- OcDayAggreg.OC_DAY_AGGREG_ID AS OC_DAY_AGGREG_ID1,
- VFmxVdPoiDay.IS_LOCKED,
- VFmxVdPoiDay.INVOICED_LINE,
- VFmxVdPoiDay.DRIVER_ID,
- VFmxVdPoiDay.IS_WORKING_DAY,
- VFmxVdPoiDay.REF_NUMBER,
- decode(OperationMode.short_name,'NW','#223c4a','CO','#f75414','') AS OM_COLOR,
- OperationMode.NAME AS OM_NAME,
- OperationMode.SHORT_NAME AS OM_SHORT_NAME,
- OperationMode.OPERATION_MODE_ID,
- (VFmxVdPoiDay.DRIVER_PERSON_NAME) AS VIEW_ATTR,
- VFmxVdPoiDay.DAY_NBR_KEY,
- VFmxVdPoiDay.MAX_NBR_PERSONS,
- VFmxVdPoiDay.DAY_FIRST_START_TIME AS DAY_FIRST_START_TIME1,
- VFmxVdPoiDay.DAY_LAST_STOP_TIME AS DAY_LAST_STOP_TIME1,
- VFmxVdPoiDay.POI_FIRST_EVENT_TIME AS POI_FIRST_EVENT_TIME1,
- VFmxVdPoiDay.POI_LAST_EVENT_TIME AS POI_LAST_EVENT_TIME1,
- VFmxVdPoiDay.MD_FUEL,
- VFmxVdPoiDay.MD_IDLE_HR,
- VFmxVdPoiDay.MD_IDLE_RATE,
- VFmxVdPoiDay.MD_TOTAL_HR,
- VFmxVdPoiDay.MD_WORKING_HR,
- VFmxVdPoiDay.FILE_PATH,
- VFmxVdPoiDay.MD_FLAG_COLOUR,
- VFmxVdPoiDay.ALERT_DESCRIPTION,
- VFmxVdPoiDay.ALERT_NAME,
- VFmxVdPoiDay.BRAND,
- VFmxVdPoiDay.CATEGORY_NAME,
- VFmxVdPoiDay.MODEL,
- NVL(NameStr.STR_VALUE,VFmxVdPoiDay.ru_technical_status_name) AS VIEW_ATTR,
- NVL(DescStr.STR_VALUE,VFmxVdPoiDay.ru_technical_status_desc) AS VIEW_ATTR
- FROM V_FMX_VD_POI_DAY VFmxVdPoiDay, OC_DAY_AGGREG OcDayAggreg, GU_VEHICLE GuVehicle, GUI_USERS GuiUsers, OPERATION_MODE OperationMode, OF_OWNER.STR NameStr, OF_OWNER.STR DescStr
- WHERE VFmxVdPoiDay.OC_DAY_AGGREG_ID = OcDayAggreg.OC_DAY_AGGREG_ID
- and GuVehicle.GUI_USER_ID = GuiUsers.GUI_USER_ID
- and GuiUsers.GUI_USER_ID = ?
- and GuVehicle.VEHICLE_ID = VFmxVdPoiDay.VEHICLE_ID
- and VFmxVdPoiDay.ru_technical_name_str_id = NameStr.STR_ID(+)
- and VFmxVdPoiDay.ru_technical_desc_str_id = DescStr.STR_ID(+)
- and GuiUsers.LANGUAGE = NameStr.LANGUAGE(+)
- and GuiUsers.LANGUAGE = DescStr.LANGUAGE(+)
- and (? = 0 or VFmxVdPoiDay.VEHICLE_ID = ?)
- and (0 = ? or VFmxVdPoiDay.VO_VEHICLE_CATEGORY_ID = ?)
- and (0 = ? or VFmxVdPoiDay.oc_poi_category_id = ?)
- and date_day between ? and ?
- and ((?='false' and DAY_KM !=0 and day_km is not null) OR ?='true')
- and (?='false' OR nvl(invoiced_line, 'Y') != 'N')
- and (? = 'false' OR status_name != 'Normal')
- and VFmxVdPoiDay.VEHICLE_OWNER_ID = ?
- and (VFmxVdPoiDay.VEHICLE_OWNER_ID != 100573 or rownum<1000)
- and OcDayAggreg.OPERATION_CENTER_ID = GuiUsers.OPERATION_CENTER_ID
- AND VFmxVdPoiDay.OPERATION_MODE_ID = OperationMode.OPERATION_MODE_ID(+)
- and (0 = ? or VFmxVdPoiDay.POI_ID = ?)
- ORDER BY vehicle_name , date_day, poi_first_event_time
Advertisement
Add Comment
Please, Sign In to add comment