SQL 1

--Any installed device with certain MC bill flag set to yes
SELECT DISTINCT us.Us_id, us.bo_status_cd, us.us_type_cd, us.time_zone_cd, TO_CHAR(EXTRACT(XMLTYPE('<a>' || us.BO_DATA_AREA || '</a>'), '/a/saRateHistory[last()]/rateSchedule/text ()').GETCLOBVAL()) "Rate Code", sp.d1_sp_id, sp.time_zone_cd, spc.srch_char_val, ie.bo_status_cd, dc.device_config_type_cd, mc.measr_comp_type_cd, d.d1_spr_cd, di.id_value
FROM d1_US us
JOIN d1_us_sp usp ON (us.us_id = usp.us_id AND d1_stop_dttm IS NULL)
JOIN d1_sp sp ON (usp.d1_sp_id = sp.d1_sp_id)
join d1_sp_char spc ON (sp.d1_sp_id = spc.d1_sp_id AND char_type_cd IN ('CM-JURIS', 'CM-PRENU' ))
JOIN d1_install_evt ie ON (sp.d1_sp_id = ie.d1_sp_id AND ie.bo_status_cd <> 'REMOVE' AND ie.bo_status_cd <> 'PENDING')
JOIN d1_dvc_cfg dc ON (ie.device_config_id = dc.device_config_id)
join d1_measr_comp mc ON (dc.device_config_id = mc.device_config_id)
join d1_measr_comp_char mcc ON (mc.measr_comp_id = mcc.measr_comp_id AND mcc.char_type_cd = 'CM-BILLD' AND mcc.char_val = 'Y')
JOIN d1_dvc d ON (dc.d1_device_id = d.d1_device_id)
LEFT JOIN d1_dvc_identifier di ON (dc.d1_device_id = di.d1_device_id AND di.dvc_id_type_flg = 'D1EI' )
WHERE mc.measr_comp_type_cd LIKE 'E-KW-OFF'
AND TO_CHAR(EXTRACT(XMLTYPE('<a>' || us.BO_DATA_AREA || '</a>'), '/a/saRateHistory[last()]/rateSchedule/text ()').GETCLOBVAL()) = '246'
ORDER BY sp.d1_sp_id;