Master Inventory Items with Category and Sub Category Details

CREATE OR REPLACE FORCE VIEW xx_mtl_master_items_v (organization_id,
                                                         inventory_item_id,
                                                         item_code,
                                                         description,
                                                         item_type,
                                                         category_set_id,
                                                         category_id,
                                                         structure_id,
                                                         CATEGORY,
                                                         sub_category
                                                        )
AS
   SELECT DISTINCT msib.organization_id, msib.inventory_item_id,
                   msib.segment1 item_code, msib.description, msib.item_type,
                   mic.category_set_id, mic.category_id, mcs.structure_id,
                   mc.segment1 CATEGORY, mc.segment2 sub_category
              FROM mtl_system_items_b msib,
                   mtl_parameters mp,
                   org_organization_definitions ood,
                   mtl_item_categories mic,
                   mtl_categories mc,
                   mtl_category_sets mcs
             WHERE msib.organization_id = mp.master_organization_id
               AND mp.organization_id = ood.organization_id
               AND mp.master_organization_id = mp.organization_id
               AND mic.category_id = mc.category_id
               AND mp.organization_id = mic.organization_id
               AND msib.inventory_item_id = mic.inventory_item_id
               AND mic.category_set_id = mcs.category_set_id
               AND mcs.structure_id = mc.structure_id;