Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- FixedUnit.CREATION_DATE,
- FixedUnit.NAME,
- FixedUnit.MODEL,
- FixedUnit.LAST_RU_DATA_ID,
- FixedUnit.FIXED_UNIT_ID,
- VoFixedUnitCategory.GRAPHICAL_SYMBOL,
- VoFixedUnitCategory.NAME category_name,
- VoFixedUnitCategory.SHORT_NAME,
- VoFixedUnitCategory.VEHICLE_OWNER_ID,
- VoFixedUnitCategory.VO_FIXED_UNIT_CATEGORY_ID,
- FixedUnit.REMOTE_UNIT_ID,
- RemoteUnit.REMOTE_UNIT_TYPE_ID,
- RemoteUnit.IMEI,
- RemoteUnit.MSISDN,
- RemoteUnit.SERIAL_NUMBER,
- OcPoi.NAME poi_name,
- OcPoi.POI_ID,
- FixedUnitPoi.FROM_DATE,
- FixedUnitPoi.FIXED_UNIT_POI_ID,
- FixedUnitPoi.TO_DATE,
- FixedUnitPoi.WBS_ID,
- (VehicleData.rg_street_nr || DECODE(VehicleData.rg_street_nr,NULL,'',', ') || VehicleData.rg_street || DECODE(VehicleData.rg_street,NULL,'',' - ') || VehicleData.rg_post_code || ' ' || VehicleData.rg_location || ' ' || VehicleData.rg_country) vehicle_data_rg_address,
- Person.LAST_NAME,
- Person.PERSON_ID,
- Person.FIRST_NAME,
- TO_CHAR(touserdate(VehicleData.gps_full_date,OperationCenter.timezone),'dd/mm/yyyy HH24:MI') event_date_str,
- P_WBS.getWbsName(Wbs.WBS_ID, OcPoi.POI_ID) wbs_name,
- VehicleData.DATE_DAY,
- VehicleData.VEHICLE_DATA_ID,
- FixedUnitPoi.POI_ID fu_poi_id,
- VehicleData.GPS_FULL_DATE,
- FixedUnit.AUTO_DEFINE_POI_CAT_ID,
- VehicleData.SATELLITE,
- p_vd_poi.getVehicleDataPoiId(GuiUsers.operation_center_id, FixedUnit.LAST_RU_DATA_ID) vd_poi_id,
- p_vd_poi.getVehicleDataPoiName(OperationCenter.operation_center_id, VehicleData.vehicle_data_id) vd_poi_name,
- (FixedUnit.street_nr || DECODE(FixedUnit.street_nr,NULL,'',', ') || FixedUnit.street || DECODE(FixedUnit.street,NULL,'',' - ') || FixedUnit.post_code || ' ' || FixedUnit.location || ' ' || FixedUnit.country) fu_linked_address,
- FixedUnit.STREET,
- FixedUnit.STREET_NR,
- FixedUnit.POST_CODE,
- FixedUnit.COUNTRY,
- FixedUnit.LOCATION,
- FixedUnit.WGS84_LATITUDE,
- FixedUnit.WGS84_LONGITUDE,
- VehicleData.WGS84_LATITUDE vd_wgsw84_latitude,
- VehicleData.WGS84_LONGITUDE vd_wgs84_longitude,
- FixedUnit.LM_COMMENT,
- FixedUnitStatus.NAME fixed_unit_name,
- FixedUnitStatus.FIXED_UNIT_STATUS_ID,
- FixedUnitUsage.NAME fixed_unit_usage_name,
- FixedUnitUsage.FIXED_UNIT_USAGE_ID,
- NVL(Str.STR_VALUE,RuTechnicalStatus.NAME) ru_technical_status_name,
- NVL(Str1.STR_VALUE,RuTechnicalStatus.DESCRIPTION) ru_technical_status_desc,
- FixedUnit.LAST_RUTS_ID
- FROM FIXED_UNIT FixedUnit, VO_FIXED_UNIT_CATEGORY VoFixedUnitCategory, REMOTE_UNIT RemoteUnit, FIXED_UNIT_POI FixedUnitPoi, OC_POI OcPoi, VEHICLE_DATA VehicleData, V_UPDATABLE_PERSON Person, GUI_USERS GuiUsers, OPERATION_CENTER OperationCenter, V_UPDATABLE_WBS Wbs, OF_OWNER.FIXED_UNIT_STATUS FixedUnitStatus, OF_OWNER.FIXED_UNIT_USAGE FixedUnitUsage, OF_OWNER.RU_TECHNICAL_STATUS RuTechnicalStatus, OF_OWNER.STR Str, OF_OWNER.STR Str1
- WHERE FixedUnit.FIXED_UNIT_ID = FixedUnitPoi.FIXED_UNIT_ID(+)
- AND FixedUnit.VO_FIXED_UNIT_CATEGORY_ID = VoFixedUnitCategory.VO_FIXED_UNIT_CATEGORY_ID
- AND FixedUnit.REMOTE_UNIT_ID = RemoteUnit.REMOTE_UNIT_ID
- AND VehicleData.VEHICLE_DATA_ID(+) = FixedUnit.LAST_RU_DATA_ID
- AND VehicleData.PERSON_ID = Person.PERSON_ID(+)
- --AND VoFixedUnitCategory.VEHICLE_OWNER_ID in
- --(
- -- select VEHICLE_OWNER_ID from GU_VEHICLE_OWNER where GUI_USER_ID = ?
- --) -- not needed since we only have access to one vehicle owner
- AND FixedUnitPoi.POI_ID = OcPoi.POI_ID(+)
- AND ((FixedUnitPoi.TO_DATE IS NULL OR FixedUnitPoi.TO_DATE > SYSDATE) OR ?='true') -- show fixed unit history (checkbox)
- AND GuiUsers.OPERATION_CENTER_ID = OperationCenter.OPERATION_CENTER_ID
- AND GuiUsers.GUI_USER_ID = ? -- gui_user_id param
- AND FixedUnitPoi.WBS_ID = WBS.WBS_ID(+)
- AND FixedUnit.FIXED_UNIT_USAGE_ID = FixedUnitUsage.FIXED_UNIT_USAGE_ID(+)
- AND FixedUnit.FIXED_UNIT_STATUS_ID = FixedUnitStatus.FIXED_UNIT_STATUS_ID(+)
- --and (? is null or ? = '' or FixedUnit.VO_FIXED_UNIT_CATEGORY_ID in (select regexp_substr(?,'[^,]+', 1, level)
- -- from dual
- -- connect by
- -- regexp_substr(?, '[^,]+', 1, level)
- -- is not null)) -- for vo_fixed_unit_category_id filter in the left menu
- --AND (NVL(FixedUnit.FIXED_UNIT_STATUS_ID,0) = 0 or ? = 'true') -- show inactive
- AND FixedUnit.LAST_RUTS_ID=RuTechnicalStatus.RU_TECHNICAL_STATUS_ID(+)
- AND RuTechnicalStatus.NAME_STR_ID=Str.STR_ID(+)
- AND GuiUsers.LANGUAGE = Str.LANGUAGE(+)
- AND RuTechnicalStatus.DESCRIPTION_STR_ID = Str1.STR_ID(+)
- AND GuiUsers.LANGUAGE = Str1.LANGUAGE(+)
Advertisement
Add Comment
Please, Sign In to add comment