SQL 2

SELECT DISTINCT
    sub.usage_subscription_id,
    sub.bo_status_cd AS usage_subscription_status_cd,
    sub.usage_subscription_type_cd,
    sub.time_zone_cd AS usage_subscription_time_zone_cd,
    rate_schedule."Rate Code",
    sp.service_provider_id,
    sp.time_zone_cd AS service_provider_time_zone_cd,
    spc.srch_char_val,
    evt.bo_status_cd AS install_event_status_cd,
    cfg.device_config_type_cd,
    comp.measr_comp_type_cd,
    dev.device_spr_code,
    ident.id_value
FROM
    usage_subscription sub
JOIN
    usage_subscription_sp usp ON sub.usage_subscription_id = usp.usage_subscription_id
JOIN
    service_provider sp ON usp.service_provider_id = sp.service_provider_id
JOIN
    service_provider_char spc ON sp.service_provider_id = spc.service_provider_id
    AND spc.char_type_cd IN ('CM-JURIS', 'CM-PRENU')
JOIN
    install_event evt ON sp.service_provider_id = evt.service_provider_id
    AND evt.bo_status_cd <> 'REMOVE'
    AND evt.bo_status_cd <> 'PENDING'
JOIN
    device_configuration cfg ON evt.device_config_id = cfg.device_config_id
JOIN
    measurement_component comp ON cfg.device_config_id = comp.device_config_id
JOIN
    device dev ON cfg.device_id = dev.device_id
LEFT JOIN
    (
        SELECT
            device_id,
            id_value
        FROM
            device_identifier
        WHERE
            identifier_type_flag = 'D1EI'
    ) ident ON cfg.device_id = ident.device_id
CROSS JOIN
    XMLTABLE(
        '/a/saRateHistory[last()]/rateSchedule'
        PASSING XMLTYPE('<a>' || sub.BO_DATA_AREA || '</a>')
        COLUMNS
            "Rate Code" VARCHAR2(10) PATH 'text()'
    ) rate_schedule
WHERE
    comp.measr_comp_type_cd LIKE 'E-KW-OFF'
    AND rate_schedule."Rate Code" = '246'
ORDER BY
    sp.service_provider_id;