Item with its template name.
SELECT msi.segment1, mit.template_name
FROM mtl_item_templ_attributes mita,
mtl_system_items_b msi,
mtl_item_templates mit
WHERE mita.attribute_value IS NOT NULL
AND mita.attribute_name = 'MTL_SYSTEM_ITEMS.ITEM_TYPE'
AND mit.template_id = mita.template_id
AND mita.attribute_value = msi.item_type
AND msi.organization_id = 103
================================================
SELECT it.template_name, ita.attribute_name, ita.attribute_value
FROM mtl_item_templates it, mtl_item_templ_attributes ita
WHERE it.template_name LIKE 'xxx%'
AND it.template_id = ita.template_id
AND ita.attribute_value IS NOT NULL
ORDER BY 1, 2
