CREATE OR REPLACE VIEW MISSING_RC_VIEW AS
SELECT
    EQUIPMENT_NUMBER AS EQUIPMENT_NUMBER,
    '' AS PLANNING_PLANT,
    e.EQUIPMENTLOCATIONPLANNERGROUP AS PLANNER_GROUP,
    e.EQUIPMENTLOCATIONMAINWORKCENTER AS MAIN_WORK_CENTER,
    e.EQUIPMENTUSAGE_CONTRACTBUSINESSPARTNERSOLDTOACCOUNT AS CUSTOMER_ID,
    e.EQUIPMENTLOCATIONCOMPANYCODE AS COMPANY_ID,
    e.EQUIPMENTLOCATIONLOCATION AS LOCATION,
    e.EQUIPMENTLOCATIONPLANTSECTION AS PLANT_SECTION,
    e.EQUIPMENTADDRESSCITY1 AS DISTRICT,
    d.DEVICEINFO_SERIALNUMBER AS SERIAL_NUMBER,
    CONCAT(e.EQUIPMENTADDRESSNAME1,
      CONCAT(', ',
        CONCAT(e.EQUIPMENTADDRESSPOSTALCODE,
          CONCAT(', ', e.EQUIPMENTADDRESSCITY1)))) AS ADDRESS,
     'DTU' AS REMOTE_MONITORING_TYPE,
    '' AS MODEM_TYPE,
    d.DEVICEID AS PHONE_NUMBER,
    e.EQUIPMENTUSAGE_CONTRACTLASTCONTRACTSTARTDATE AS CONTRACT_START,
    e.EQUIPMENTUSAGE_CONTRACTLASTCONTRACTENDDATE AS CONTRACT_END,
    rc.CREATE_DATE AS LAST_ROUTINE_CALL,
    (
        SELECT MAX(CREATEDATE)
        FROM ALARM a
        WHERE a.EQUIPMENTNUMBER = EQUIPMENT_NUMBER
    ) LAST_OTHER_CALL,
    24 AS ROUTINE_CALL_INTERVAL_HOURS,
    '' AS NOTE
    FROM DTU_ATT_VOICERC rc
    INNER JOIN EQUIPMENT e ON (e.ID = EQUIPMENT_NUMBER)
    INNER JOIN DEVICE_EQUIPMENT de ON (de.EQUIPMENTNUMBER = e.ID)
    INNER JOIN DEVICE d ON (de.DEVICEID = d.DEVICEID)
    WHERE (rc.EQUIPMENT_NUMBER, rc.CREATE_DATE) IN
    (SELECT EQUIPMENT_NUMBER, MAX(CREATE_DATE) AS LAST_ROUTINE_CALL FROM DTU_ATT_VOICERC GROUP BY EQUIPMENT_NUMBER) AND
    rc.CREATE_DATE < (CURRENT_TIMESTAMP - 24 HOURS);