A list of non catalog templates in an operating unit.
 
Associated item category and stores

Select DISTINCT b.category_id, a.template_name, b.item_type, b.unit_of_measure_code, b.org_id,
c.segment1 ||'.' || c.segment2 ||'.' || c.segment3 ||'.' || c.segment4 ||'.' || c.segment5 AS CATEGORY_VALUE, c.DESCRIPTION
from apps. POR_NONCAT_TEMPLATES_ALL_TL a,
apps. POR_NONCAT_TEMPLATES_ALL_B b,
apps. MTL_CATEGORIES_V c,
apps. ICX_CAT_STORE_ORG_ASSIGNMENTS d,
apps. ICX_CAT_STORES_TL e
WHERE a.template_id=b.template_id
AND b.org_id=d.org_id
AND b.org_id= :org_id
AND a.created_by=b.created_by
AND b.category_id=c.category_id