ITEM CLASSIFICATION AND MASTER ITEM CATEGORIES

ITEM CLASSIFICATION AND MASTER ITEM CATEROIES                ------------------------------------------------------  1

SELECT e.segment1 part_number,
       (SELECT organization_code
          FROM mtl_parameters
         WHERE organization_id = b.organization_id) LOCATION, b.regime_code,
       a.attribute_code, a.attribute_value, r.concatenated_segments CATEGORY
  FROM jai_rgm_itm_tmpl_attrs a,
       jai_rgm_itm_regns b,
       mtl_system_items_b e,
       mtl_item_categories f,
       mtl_categories_kfv r
 WHERE b.rgm_item_regns_id = a.rgm_item_regns_id
   AND b.regime_code = 'EXCISE'
--   AND b.inventory_item_id = 77580
   AND b.organization_id = 151
   AND attribute_code = 'EXCISABLE'
   AND e.inventory_item_id = b.inventory_item_id
   AND b.organization_id = e.organization_id
   AND e.inventory_item_id = f.inventory_item_id
   AND f.organization_id = e.organization_id
   AND f.category_set_id = 1100000066
   AND r.category_id = f.category_id
   AND a.attribute_value <> r.concatenated_segments
--   and  a.attribute_value <> 'Y'
--  and  r.concatenated_segments <> 'Y' 


******************************************************************************

ITEM CLASSIFICATION AND MASTER ITEM CATEGORIES


 SELECT MSIB.SEGMENT1 PART_NAME ,                                ----------------------------------------------------  2
        JRIA.ATTRIBUTE_VALUE APPLICABLE,
        ATTRIBUTE_CODE ITEM_CLASSFICATION ,
        MICV.CATEGORY_SET_NAME ,
        MICV.CATEGORY_CONCAT_SEGS CATEGORY
  from  JAI_RGM_ITM_TMPL_ATTRS JRIA ,
        JAI_RGM_ITM_REGNS JRIR ,
        MTL_SYSTEM_ITEMS_B MSIB ,
        MTL_ITEM_CATEGORIES_V MICV
 WHERE  1=1
   and  JRIA.RGM_ITEM_REGNS_ID = JRIR.RGM_ITEM_REGNS_ID
   AND  JRIR.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
   and  MSIB.INVENTORY_ITEM_ID = MICV.INVENTORY_ITEM_ID
   AND  MICV.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
 -- AND  MSIB.SEGMENT1 = '17515MGE000'
   AND  JRIA.ATTRIBUTE_VALUE <> CATEGORY_CONCAT_SEGS
   AND  MICV.CATEGORY_SET_NAME = 'HMI Excise Applicability Set'
   AND  JRIA.ATTRIBUTE_CODE = 'EXCISABLE'
   AND  MSIB.ORGANIZATION_ID = 151
   and  JRIR.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID