Item Classification Query in Oracle Apps

Item Classification Query

-- To Retrieve Template Level Values --

SELECT 'q1' query1, msi.organization_id, msi.inventory_item_id,
       msi.segment1 item_code, msi.description item_desc, tmpl.template_name,
       tmpl.description, tmpl.regime_code, att.attribute_code,
       att.attribute_value
  FROM jai_rgm_tmpl_itm_regns tmp_itm,
       jai_rgm_tmpl_org_regns tmp_org,
       jai_rgm_itm_templates tmpl,
       jai_rgm_itm_tmpl_attrs att,
       mtl_system_items_b msi
 WHERE tmp_itm.templ_org_regns_id = tmp_org.templ_org_regns_id
   AND tmpl.template_id = tmp_org.template_id
   AND tmpl.template_id = att.template_id
   AND msi.inventory_item_id = tmp_itm.inventory_item_id
   AND msi.organization_id = tmp_org.organization_id
   AND tmp_org.organization_id = 3
   --AND tmp_itm.inventory_item_id = 2377
UNION ALL
-- To Retrieve Attribute Level Values --
SELECT 'q2' query2, msi.organization_id, msi.inventory_item_id,
       msi.segment1 item_code, msi.description item_desc,
       'tmpl' template_name, NULL description, rgm.regime_code,
       tmpl.attribute_code, tmpl.attribute_value
  FROM jai_rgm_itm_regns rgm,
       jai_rgm_itm_tmpl_attrs tmpl,
       mtl_system_items_b msi
 WHERE rgm.rgm_item_regns_id = tmpl.rgm_item_regns_id
   AND rgm.organization_id = 3
   --AND rgm.inventory_item_id = 2377
   AND msi.inventory_item_id = rgm.inventory_item_id
   AND rgm.organization_id = msi.organization_id