4.1. Items

4.1. Item Status Attributes

Fig4.1 Item Status Attributes

Item Status provides default values for certain item attributes to control the functionality of an item.

The status attributes are BOM Allowed, Build in WIP, Customer orders Enabled, Internal Orders enabled, Invoice Enabled, Purchasable, Stockable & Transactable.

An Active status implies all these attributes are enabled.

4.2. Item Status Attribute Controls

Fig4.2 Status Attribute controls

You must set certain controls and reference options before defining items.

These enable you to maintain, group, reference, query, and delete your items.One of the prerequisites for defining items is setting attribute controls.

Control Level determines whether you have centralized (Master level) or decentralized (Organization level) control of an item attribute.Attributes maintained at the Master level have the same attribute values in each organization in which an item is assigned. Attributes maintained at the Organization level may have different attribute values in different organizations.

For example, an item may be min-max planned in a distribution organization but MRP planned in a production organization.

4.3. Item Defining Attributes

Functional Area	Item Dening Attribute	Enabling Value
Inventory    Purchasing     Order Management Internal     Master Scheduling/ MRP Cost Management Engineering     Service     Product Line Accounting	Inventory Item Purchased     Customer Ordered Item Ordered Item         MRP Planning Method Costing Enabled        Engineering Item     Support Service,Serviceable Product None	Yes Yes Yes Yes MRP/MPS Yes Yes Yes N/A

4.4. Item Status Attributes Architecture

MTL_ITEM_STATUS                 INVENTORY_ITEM_STATUS_CODE DESCRIPTION                   DISABLE_DATE

MTL_STATUS_ATTRIBUTE_VALUES INVENTORY_ITEM_STATUS_CODE    ATTRIBUTE_NAME                ATTRIBUTE_VALUE

MTL_ITEM_ATTRIBUTES              ATTRIBUTE_NAME         USER_ATTRIBUTE_NAME    ATTRIBUTE_GROUP_ID     CONTROL_LEVEL          STATUS_CONTROL_CODE    VALIDATION_CODE        USER_ATTRIBUTE_NAME_GUI ATTRIBUTE_GROUP_ID_GUI SEQUENCE_GUI

Query to find item status attributes :	Query to get item attributes under status control :
col item_status form a12 col description form a30 col attribute_name form a48 col value form a10 select    mis.inventory_item_status_code item_status,    mis.description,    mis.disable_date,    av.attribute_name,    av.attribute_value value from mtl_item_status mis,      mtl_status_attribute_values av where mis.inventory_item_status_code=           av.inventory_item_status_code order by 1 /	set lines 150 set pages 150 col attrib_group form a30 col control_level form a16 col status_control form a20 col validation form a20 col user_attribute_name_gui form a30 select meaning1 attrib_group,user_attribute_name_gui,   -- ,control_level, status_control_code,attribute_name,    -- attribute_group_id,data_type,   -- user_attribute_name,level_updateable_flag,   -- validation_code ,lookup_type1, lookup_code1,enabled_flag1,lookup_type2,lookup_code2,   meaning2 control_level,   -- ,enabled_flag2,   -- lookup_type3,lookup_code3,   meaning3 status_control,   -- enabled_flag3,lookup_type4,lookup_code4,   meaning4 validation   -- ,enabled_flag4 from  mtl_item_attributes_v where control_level in (1,2) and status_control_code   is not null and user_attribute_name_gui is not null and attribute_name in (   select attribute_name    from mtl_item_attr_appl_inst_v )  order by   attribute_group_id_gui, sequence_gui /
Query to get item attributes NOT under status control :	Query to get item status attribute controls :
set lines 150 set pages 150 col attrib_group form a30 col control_level form a24 col status_control form a20 col validation form a32 col user_attribute_name_gui form a30 select meaning1 attrib_group,user_attribute_name_gui,   -- ,control_level, status_control_code,attribute_name,   -- attribute_group_id,data_type,   -- user_attribute_name,level_updateable_flag,   -- validation_code ,lookup_type1,   -- lookup_code1,enabled_flag1,lookup_type2,lookup_code2,   meaning2 control_level,   -- ,enabled_flag2,   -- lookup_type3,lookup_code3,   meaning3 status_control,   -- enabled_flag3,lookup_type4,lookup_code4,   meaning4 validation   -- ,enabled_flag4 from  mtl_item_attributes_v where control_level in (1,2) and status_control_code is null and user_attribute_name_gui is not null and attribute_name in (   select attribute_name from mtl_item_attr_appl_inst_v )  order by   attribute_group_id_gui, sequence_gui /	set lines 150 set pages 150 col attribute_name form a50 col user_attribute_name form a30 col user_attribute_name_gui form a30 col controlled_at form a14 select    ia.attribute_group_id group_id,    ia.user_attribute_name_gui,    lk.meaning controlled_at,    ia.attribute_name, --   ia.user_attribute_name,    ia.status_control_code,    ia.validation_code from fnd_lookup_values lk,      mtl_item_attributes ia where ia.control_level=lk.lookup_code and   lk.lookup_type='ITEM_CONTROL_LEVEL_GUI' order by ia.attribute_group_id,1 /

4.10. Item Categories

4.10.1. Item Categories

Fig4.10.1 Categories

A category is a logical classification of items that have similar  characteristics.
A category set is a distinct grouping of categories.
Categories and category sets group your items for various reports and programs.
If u have more than one Item Category Flexfield Structure, then choose one structure first.

(Category KFF structure explained later).

Then enter category name and description.
A category is visible in iSupplier or iProcurement Portal, if enabled here.

4.10.2. Item Category sets

Fig4.10.2 Category set

The categories you assign to a category set must have the same flexfield structure as the set itself.

This is true even if you choose not to validate the category list.

You can assign an item to multiple categories within a category set.

For example, you may define a Hazard category set.

In this case, an item may be assigned to both the Poison and Corrosive categories. 

4.10.3 Default category sets

Fig4.10.3 Default Category set

You must assign a default category set to each of these functional areas.When you enable an item for a certain functional area, Oracle Inventory automatically assigns the item to the default category set of that functional area and the default category of that set.For example, if you set Inventory Item to Yes, then Inventory automatically assigns the item to the Inventory functional area’s default category set and default category.

4.10.4 Category Architecture

MTL_CATEGORIES_B 類別 CATEGORY_ID            STRUCTURE_ID           DESCRIPTION            DISABLE_DATE           SEGMENT1               SEGMENT2               SUMMARY_FLAG           ENABLED_FLAG           START_DATE_ACTIVE      END_DATE_ACTIVE        TOTAL_PROD_ID          WEB_STATUS             SUPPLIER_ENABLED_FLAG

MTL_CATEGORY_SETS_B 類別群組 CATEGORY_SET_ID                CATEGORY_SET_NAME              STRUCTURE_ID                   VALIDATE_FLAG                  CONTROL_LEVEL                  DESCRIPTION                    DEFAULT_CATEGORY_ID            MULT_ITEM_CAT_ASSIGN_FLAG      CONTROL_LEVEL_UPDATEABLE_FLAG MULT_ITEM_CAT_UPDATEABLE_FLAG HIERARCHY_ENABLED              VALIDATE_FLAG_UPDATEABLE_FLAG

MTL_CATEGORY_SET_VALID_CATS 類別群組  CATEGORY_SET_ID                 CATEGORY_ID                     PARENT_CATEGORY_ID

MTL_DEFAULT_CATEGORY_SETS 預設類別群組 FUNCTIONAL_AREA_ID     CATEGORY_SET_ID

一個   Category Set 可以存在很多預設功能群組
一個 Category 可以存在很多Category Se中 一個 Organazation 可以定義很多 Itemㄧ個 Item 可以定義很多 Category Set

CREATE TABLE INV.MTL_CATEGORIES_B
(
  CATEGORY_ID             NUMBER                NOT NULL,
  STRUCTURE_ID            NUMBER                NOT NULL,
  DESCRIPTION             VARCHAR2(240 BYTE),
  DISABLE_DATE            DATE,
  SEGMENT1                VARCHAR2(40 BYTE),
  SEGMENT2                VARCHAR2(40 BYTE),
  SEGMENT3                VARCHAR2(40 BYTE),
  SEGMENT4                VARCHAR2(40 BYTE),
  SEGMENT5                VARCHAR2(40 BYTE),
  SEGMENT6                VARCHAR2(40 BYTE),
  SEGMENT7                VARCHAR2(40 BYTE),
  SEGMENT8                VARCHAR2(40 BYTE),
  SEGMENT9                VARCHAR2(40 BYTE),
  SEGMENT10               VARCHAR2(40 BYTE),
  SEGMENT11               VARCHAR2(40 BYTE),
  SEGMENT12               VARCHAR2(40 BYTE),
  SEGMENT13               VARCHAR2(40 BYTE),
  SEGMENT14               VARCHAR2(40 BYTE),
  SEGMENT15               VARCHAR2(40 BYTE),
  SEGMENT16               VARCHAR2(40 BYTE),
  SEGMENT17               VARCHAR2(40 BYTE),
  SEGMENT18               VARCHAR2(40 BYTE),
  SEGMENT19               VARCHAR2(40 BYTE),
  SEGMENT20               VARCHAR2(40 BYTE),
 
  SUMMARY_FLAG            VARCHAR2(1 BYTE)      NOT NULL,
  ENABLED_FLAG            VARCHAR2(1 BYTE)      NOT NULL,
  START_DATE_ACTIVE       DATE,
  END_DATE_ACTIVE         DATE,
 
  ATTRIBUTE_CATEGORY      VARCHAR2(30 BYTE),
  ATTRIBUTE1              VARCHAR2(150 BYTE),
  ATTRIBUTE2              VARCHAR2(150 BYTE),
  ATTRIBUTE3              VARCHAR2(150 BYTE),
  ATTRIBUTE4              VARCHAR2(150 BYTE),
  ATTRIBUTE5              VARCHAR2(150 BYTE),
  ATTRIBUTE6              VARCHAR2(150 BYTE),
  ATTRIBUTE7              VARCHAR2(150 BYTE),
  ATTRIBUTE8              VARCHAR2(150 BYTE),
  ATTRIBUTE9              VARCHAR2(150 BYTE),
  ATTRIBUTE10             VARCHAR2(150 BYTE),
  ATTRIBUTE11             VARCHAR2(150 BYTE),
  ATTRIBUTE12             VARCHAR2(150 BYTE),
  ATTRIBUTE13             VARCHAR2(150 BYTE),
  ATTRIBUTE14             VARCHAR2(150 BYTE),
  ATTRIBUTE15             VARCHAR2(150 BYTE),
 
  REQUEST_ID              NUMBER,
  PROGRAM_APPLICATION_ID  NUMBER,
  PROGRAM_ID              NUMBER,
  PROGRAM_UPDATE_DATE     DATE,
  WEB_STATUS              VARCHAR2(30 BYTE),
  SUPPLIER_ENABLED_FLAG   VARCHAR2(1 BYTE)
 
  LAST_UPDATE_DATE        DATE                  NOT NULL,
  LAST_UPDATED_BY         NUMBER                NOT NULL,
  CREATION_DATE           DATE                  NOT NULL,
  CREATED_BY              NUMBER                NOT NULL,
  LAST_UPDATE_LOGIN       NUMBER,)

CREATE INDEX INV.MTL_CATEGORIES_B_N1 ON INV.MTL_CATEGORIES_B
(SEGMENT1)

CREATE INDEX INV.MTL_CATEGORIES_B_N3 ON INV.MTL_CATEGORIES_B
(CATEGORY_ID, SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4)

CREATE INDEX INV.MTL_CATEGORIES_B_N4 ON INV.MTL_CATEGORIES_B
(CATEGORY_ID,
SEGMENT1,  SEGMENT2,  SEGMENT3,  SEGMENT4,  SEGMENT5, 
SEGMENT6,  SEGMENT7,  SEGMENT8,  SEGMENT9,  SEGMENT10,
SEGMENT11, SEGMENT12, SEGMENT13, SEGMENT14, SEGMENT15)

CREATE UNIQUE INDEX INV.MTL_CATEGORIES_B_U1 ON INV.MTL_CATEGORIES_B
(CATEGORY_ID)

CREATE INDEX INV.MTL__CATEGORIES_B_N2 ON INV.MTL_CATEGORIES_B
(STRUCTURE_ID)

CREATE OR REPLACE TRIGGER APPS.MTL_CATEGORIES_B_TA_IBE
/* $Header: INVITCA1.sql 115.1 2003/12/26 03:16:05 wwahid ship $ */

AFTER DELETE
ON MTL_CATEGORIES_B
FOR EACH ROW
DISABLE
DECLARE
  plsql_block  VARCHAR2(2000);
BEGIN

  IF ( INV_Item_Util.g_Appl_Inst.IBE <> 0 ) THEN

     plsql_block :=
     ' BEGIN                                 '||
     '   IBE_INV_Database_Trigger_PVT.MTL_Categories_B_Deleted         '||
     '   (  p_old_category_id => :old_category_id             '||
     '   );                                 '||
     ' EXCEPTION                             '||
     '   WHEN others THEN                         '||
     '      NULL;                             '||
     ' END;';

     EXECUTE IMMEDIATE plsql_block
       USING IN :old.category_id ;

  END IF;  -- IBE installed

EXCEPTION
   WHEN others THEN
      NULL;

END MTL_CATEGORIES_B_TA_IBE;
/


CREATE OR REPLACE SYNONYM APPS.MTL_CATEGORIES_B FOR INV.MTL_CATEGORIES_B;


GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON INV.MTL_CATEGORIES_B TO APPS WITH GRANT OPTION;

類別群組編號
CREATE TABLE INV.MTL_CATEGORY_SETS_B
(
  CATEGORY_SET_ID                NUMBER         NOT NULL,
  STRUCTURE_ID                   NUMBER         NOT NULL,
  VALIDATE_FLAG                  VARCHAR2(1 BYTE) NOT NULL,
  CONTROL_LEVEL                  NUMBER         NOT NULL,
  DEFAULT_CATEGORY_ID            NUMBER,
  REQUEST_ID                     NUMBER,
  PROGRAM_APPLICATION_ID         NUMBER,
  PROGRAM_ID                     NUMBER,
  PROGRAM_UPDATE_DATE            DATE,
  MULT_ITEM_CAT_ASSIGN_FLAG      VARCHAR2(1 BYTE) NOT NULL,
  CONTROL_LEVEL_UPDATEABLE_FLAG  VARCHAR2(1 BYTE),
  MULT_ITEM_CAT_UPDATEABLE_FLAG  VARCHAR2(1 BYTE),
  HIERARCHY_ENABLED              VARCHAR2(1 BYTE),
  VALIDATE_FLAG_UPDATEABLE_FLAG  VARCHAR2(1 BYTE),
  USER_CREATION_ALLOWED_FLAG     VARCHAR2(1 BYTE),
  RAISE_ITEM_CAT_ASSIGN_EVENT    VARCHAR2(1 BYTE),
  RAISE_ALT_CAT_HIER_CHG_EVENT   VARCHAR2(1 BYTE),
  RAISE_CATALOG_CAT_CHG_EVENT    VARCHAR2(1 BYTE),
  -------------------------------------------------------
  LAST_UPDATE_DATE               DATE           NOT NULL,
  LAST_UPDATED_BY                NUMBER         NOT NULL,
  CREATION_DATE                  DATE           NOT NULL,
  CREATED_BY                     NUMBER         NOT NULL,
  LAST_UPDATE_LOGIN              NUMBER
  )

CREATE UNIQUE INDEX INV.MTL_CATEGORY_SETS_B_U1 ON INV.MTL_CATEGORY_SETS_B
(CATEGORY_SET_ID)

CREATE OR REPLACE SYNONYM APPS.MTL_CATEGORY_SETS_B FOR INV.MTL_CATEGORY_SETS_B;

GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON INV.MTL_CATEGORY_SETS_B TO APPS WITH GRANT OPTION;

以功能別去設定內定材料類別群組
CREATE TABLE INV.MTL_DEFAULT_CATEGORY_SETS
(
  FUNCTIONAL_AREA_ID      NUMBER                NOT NULL,
  CATEGORY_SET_ID         NUMBER                NOT NULL,  REQUEST_ID              NUMBER,
  PROGRAM_APPLICATION_ID  NUMBER,
  PROGRAM_ID              NUMBER,
  PROGRAM_UPDATE_DATE     DATE,
  -------------------------------------------------------
  LAST_UPDATE_DATE        DATE                  NOT NULL,
  LAST_UPDATED_BY         NUMBER                NOT NULL,
  CREATION_DATE           DATE                  NOT NULL,
  CREATED_BY              NUMBER                NOT NULL,
  LAST_UPDATE_LOGIN       NUMBER

)

CREATE UNIQUE INDEX INV.MTL_DEFAULT_CATEGORY_SETS_U1 ON INV.MTL_DEFAULT_CATEGORY_SETS
(FUNCTIONAL_AREA_ID)

CREATE OR REPLACE SYNONYM APPS.MTL_DEFAULT_CATEGORY_SETS FOR INV.MTL_DEFAULT_CATEGORY_SETS;

GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON INV.MTL_DEFAULT_CATEGORY_SETS TO APPS WITH GRANT OPTION;


CREATE TABLE INV.MTL_CATEGORY_SET_VALID_CATS
(
  CATEGORY_SET_ID         NUMBER                NOT NULL,
  CATEGORY_ID             NUMBER                NOT NULL,
  REQUEST_ID              NUMBER,
  PROGRAM_APPLICATION_ID  NUMBER,
  PROGRAM_ID              NUMBER,
  PROGRAM_UPDATE_DATE     DATE,
  PARENT_CATEGORY_ID      NUMBER,
  -------------------------------------------------------
  LAST_UPDATE_DATE        DATE                  NOT NULL,
  LAST_UPDATED_BY         NUMBER                NOT NULL,
  CREATION_DATE           DATE                  NOT NULL,
  CREATED_BY              NUMBER                NOT NULL,
  LAST_UPDATE_LOGIN       NUMBER

)

CREATE INDEX INV.MTL_CATEGORY_SET_VALID_CATS_N1 ON INV.MTL_CATEGORY_SET_VALID_CATS

(CATEGORY_SET_ID, PARENT_CATEGORY_ID, CATEGORY_ID)

CREATE UNIQUE INDEX INV.MTL_CATEGORY_SET_VALID_CATS_U1 ON INV.MTL_CATEGORY_SET_VALID_CATS

(CATEGORY_SET_ID, CATEGORY_ID)

CREATE INDEX INV.MTL_CATEGORY_SET_VALID_CATS_N1 ON INV.MTL_CATEGORY_SET_VALID_CATS

(CATEGORY_SET_ID, PARENT_CATEGORY_ID, CATEGORY_ID)


CREATE UNIQUE INDEX INV.MTL_CATEGORY_SET_VALID_CATS_U1 ON INV.MTL_CATEGORY_SET_VALID_CATS

(CATEGORY_SET_ID, CATEGORY_ID)


CREATE OR REPLACE SYNONYM APPS.MTL_CATEGORY_SET_VALID_CATS FOR INV.MTL_CATEGORY_SET_VALID_CATS;


GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON INV.MTL_CATEGORY_SET_VALID_CATS TO APPS WITH GRANT OPTION;

Query to find default category for a category set :

set pages 150 set lines 150 col item form a24 col item_desc form a60 col cat_desc form a40 col default_category form a30 col category_set_name form a30 select mcats.category_set_name, mcat.segment1 default_category, mcat.description cat_desc, mcat.category_id, mcats.category_set_id from   mtl_category_sets mcats,   mtl_categories mcat where mcats.category_set_name like '%'  and mcat.category_id = mcats.default_category_id order by 1,2 /

4.10.5 Category Assignments

Fig4.10.3 Category Assignment

In the Master Items screen assign Category to an Item.

MTL_ITEM_CATEGORIES     INVENTORY_ITEM_ID      ORGANIZATION_ID        CATEGORY_SET_ID        CATEGORY_ID

Query to find all items assigned to categories of a category set : set pages 150 set lines 150 col item form a24 col item_desc form a60 col category form a30 col category_set_name form a24 select mcats.category_set_name, mcat.segment1||'.'|| mcat.segment2 category, msi.segment1 item, msi.description item_desc from   mtl_item_categories  micat,   mtl_category_sets mcats,   mtl_categories mcat,   mtl_system_items_vl  msi where mcats.category_set_name like 'Inv%'  and micat.category_set_id = mcats.category_set_id  and micat.category_id = mcat.category_id  and mcat.segment1 like 'N%'  and msi.inventory_item_id = micat.inventory_item_id  and msi.organization_id =  micat.organization_id  and msi.organization_id =  204 order by 1,2,3 /

4.10.6 Category KFF

Fig4.10.6 Category KFF

Item Categories KFF has many structures as shown here.Seeded Item Categories structure has 2 segments, Family and Class. Family has an independent validation type value set but Class has dependent validation type value set.

Fig4.10.6-1 Category KFF segments