psi_mmobile

Untitled

Oct 17th, 2025
369
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 4.67 KB | None | 0 0
  1. SELECT
  2.        FixedUnit.CREATION_DATE,
  3.        FixedUnit.NAME,
  4.        FixedUnit.MODEL,
  5.        FixedUnit.LAST_RU_DATA_ID,
  6.        FixedUnit.FIXED_UNIT_ID,
  7.        VoFixedUnitCategory.GRAPHICAL_SYMBOL,
  8.        VoFixedUnitCategory.NAME category_name,
  9.        VoFixedUnitCategory.SHORT_NAME,
  10.        VoFixedUnitCategory.VEHICLE_OWNER_ID,
  11.        VoFixedUnitCategory.VO_FIXED_UNIT_CATEGORY_ID,
  12.        FixedUnit.REMOTE_UNIT_ID,
  13.        RemoteUnit.REMOTE_UNIT_TYPE_ID,
  14.        RemoteUnit.IMEI,
  15.        RemoteUnit.MSISDN,
  16.        RemoteUnit.SERIAL_NUMBER,
  17.        OcPoi.NAME poi_name,
  18.        OcPoi.POI_ID,
  19.        FixedUnitPoi.FROM_DATE,
  20.        FixedUnitPoi.FIXED_UNIT_POI_ID,
  21.        FixedUnitPoi.TO_DATE,
  22.        FixedUnitPoi.WBS_ID,
  23.        (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,
  24.        Person.LAST_NAME,
  25.        Person.PERSON_ID,
  26.        Person.FIRST_NAME,
  27.        TO_CHAR(touserdate(VehicleData.gps_full_date,OperationCenter.timezone),'dd/mm/yyyy HH24:MI') event_date_str,
  28.        P_WBS.getWbsName(Wbs.WBS_ID, OcPoi.POI_ID) wbs_name,
  29.        VehicleData.DATE_DAY,
  30.        VehicleData.VEHICLE_DATA_ID,
  31.        FixedUnitPoi.POI_ID fu_poi_id,
  32.        VehicleData.GPS_FULL_DATE,
  33.        FixedUnit.AUTO_DEFINE_POI_CAT_ID,
  34.        VehicleData.SATELLITE,
  35.        p_vd_poi.getVehicleDataPoiId(GuiUsers.operation_center_id, FixedUnit.LAST_RU_DATA_ID) vd_poi_id,
  36.        p_vd_poi.getVehicleDataPoiName(OperationCenter.operation_center_id, VehicleData.vehicle_data_id) vd_poi_name,
  37.        (FixedUnit.street_nr || DECODE(FixedUnit.street_nr,NULL,'',', ') || FixedUnit.street || DECODE(FixedUnit.street,NULL,'',' - ') || FixedUnit.post_code || ' ' || FixedUnit.location || ' ' || FixedUnit.country) fu_linked_address,
  38.        FixedUnit.STREET,
  39.        FixedUnit.STREET_NR,
  40.        FixedUnit.POST_CODE,
  41.        FixedUnit.COUNTRY,
  42.        FixedUnit.LOCATION,
  43.        FixedUnit.WGS84_LATITUDE,
  44.        FixedUnit.WGS84_LONGITUDE,
  45.        VehicleData.WGS84_LATITUDE vd_wgsw84_latitude,
  46.        VehicleData.WGS84_LONGITUDE vd_wgs84_longitude,
  47.        FixedUnit.LM_COMMENT,
  48.        FixedUnitStatus.NAME fixed_unit_name,
  49.        FixedUnitStatus.FIXED_UNIT_STATUS_ID,
  50.        FixedUnitUsage.NAME fixed_unit_usage_name,
  51.        FixedUnitUsage.FIXED_UNIT_USAGE_ID,
  52.        NVL(Str.STR_VALUE,RuTechnicalStatus.NAME) ru_technical_status_name,
  53.        NVL(Str1.STR_VALUE,RuTechnicalStatus.DESCRIPTION) ru_technical_status_desc,
  54.        FixedUnit.LAST_RUTS_ID
  55.  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
  56.   WHERE FixedUnit.FIXED_UNIT_ID = FixedUnitPoi.FIXED_UNIT_ID(+)
  57. AND FixedUnit.VO_FIXED_UNIT_CATEGORY_ID = VoFixedUnitCategory.VO_FIXED_UNIT_CATEGORY_ID
  58. AND FixedUnit.REMOTE_UNIT_ID = RemoteUnit.REMOTE_UNIT_ID
  59. AND VehicleData.VEHICLE_DATA_ID(+) = FixedUnit.LAST_RU_DATA_ID
  60. AND VehicleData.PERSON_ID = Person.PERSON_ID(+)
  61. --AND VoFixedUnitCategory.VEHICLE_OWNER_ID in
  62. --(
  63. -- select VEHICLE_OWNER_ID from GU_VEHICLE_OWNER where GUI_USER_ID = ?
  64. --) -- not needed since we only have access to one vehicle owner
  65. AND FixedUnitPoi.POI_ID = OcPoi.POI_ID(+)
  66. AND ((FixedUnitPoi.TO_DATE IS NULL OR FixedUnitPoi.TO_DATE > SYSDATE) OR ?='true') -- show fixed unit history (checkbox)
  67. AND GuiUsers.OPERATION_CENTER_ID = OperationCenter.OPERATION_CENTER_ID
  68. AND GuiUsers.GUI_USER_ID = ? -- gui_user_id param
  69. AND FixedUnitPoi.WBS_ID = WBS.WBS_ID(+)
  70. AND FixedUnit.FIXED_UNIT_USAGE_ID = FixedUnitUsage.FIXED_UNIT_USAGE_ID(+)
  71. AND FixedUnit.FIXED_UNIT_STATUS_ID = FixedUnitStatus.FIXED_UNIT_STATUS_ID(+)
  72. --and (? is null or ? = '' or FixedUnit.VO_FIXED_UNIT_CATEGORY_ID in (select regexp_substr(?,'[^,]+', 1, level)
  73. --   from dual
  74. --    connect by
  75. --        regexp_substr(?, '[^,]+', 1, level)
  76. --            is not null)) -- for vo_fixed_unit_category_id filter in the left menu
  77. --AND (NVL(FixedUnit.FIXED_UNIT_STATUS_ID,0) = 0 or ? = 'true') -- show inactive
  78. AND FixedUnit.LAST_RUTS_ID=RuTechnicalStatus.RU_TECHNICAL_STATUS_ID(+)
  79. AND RuTechnicalStatus.NAME_STR_ID=Str.STR_ID(+)
  80. AND GuiUsers.LANGUAGE = Str.LANGUAGE(+)
  81. AND RuTechnicalStatus.DESCRIPTION_STR_ID = Str1.STR_ID(+)
  82. AND GuiUsers.LANGUAGE = Str1.LANGUAGE(+)
Advertisement
Add Comment
Please, Sign In to add comment