288: select name, max(route_classification_id) route_classification_id
289: from iem_route_classifications
290: group by name
291: ) iem_r_c,
292: ( SELECT * FROM bix_dm_goals_emc
293: WHERE end_date_active IS NULL) goals
294: WHERE ih_mitem.MEDIA_ITEM_TYPE = 'EMAIL'
295: AND ih_mitem.DIRECTION = 'INBOUND'
296: AND ih_mitem.classification = iem_r_c.name(+)
318: --
319: --This is for cases where the goal is not defined and the default is used
320: --
321: SELECT sl_for_replied_emails * 60 * 60 INTO l_email_service_level
322: FROM bix_dm_goals_emc
323: WHERE classification_id = -999;
324: EXCEPTION
325: WHEN OTHERS THEN
326: l_email_service_level := 24 * 60 * 60;