API to Assign Item to an Organization in Oracle Inventory


EGO_ITEM_PUB package provides functionality for maintaining items, item revisions, etc. We can use ASSIGN_ITEM_TO_ORG procedure to assign one item to an organization.

The procedure definition is:

  PROCEDURE Assign_Item_To_Org(
      p_api_version             IN      NUMBER
     ,p_init_msg_list           IN      VARCHAR2        DEFAULT  G_FALSE
     ,p_commit                  IN      VARCHAR2        DEFAULT  G_FALSE
     ,p_Inventory_Item_Id       IN      NUMBER          DEFAULT  G_MISS_NUM
     ,p_Item_Number             IN      VARCHAR2        DEFAULT  G_MISS_CHAR
     ,p_Organization_Id         IN      NUMBER          DEFAULT  G_MISS_NUM
     ,p_Organization_Code       IN      VARCHAR2        DEFAULT  G_MISS_CHAR
     ,p_Primary_Uom_Code        IN      VARCHAR2        DEFAULT  G_MISS_CHAR
     ,x_return_status           OUT NOCOPY  VARCHAR2
     ,x_msg_count               OUT NOCOPY  NUMBER);

The parameters are:

  • P_API_VERSION – A decimal number indicating major and minor revisions to the API. Pass 1.0 unless otherwise indicated in the API parameter list.
  • P_INIT_MSG_LIST – A one-character flag indicating whether to initialize the FND_MSG_PUB package’s message stack at the beginning of API processing (and thus remove any messages that may exist on the stack from prior processing in the same session). Valid values are FND_API.G_TRUE and FND_API.G_FALSE.
  • P_COMMIT – A one-character flag indicating whether to commit work at the end of API processing. Valid values are FND_API.G_TRUE and FND_API.G_FALSE.
  • P_INVENTORY_ITEM_ID – Inventory Item Id of the Item
  • P_ITEM_NUMBER – Segment1 of the Item
  • P_ORGANIZATION_ID – Organization Id of the Organization to whom Item must be assigned
  • P_ORGANIZATION_CODE – 3 character Organization Code of the Organization to whom Item must be assigned
  • P_PRIMARY_UOM_CODE – Primary Unit of Measure of the item.
  • X_RETURN_STATUS – A one-character code indicating whether any errors occurred during processing (in which case error messages will be present on the FND_MSG_PUB package’s message stack). Valid values are FND_API.G_RET_STS_SUCCESS, FND_API.G_RET_STS_ERROR, and FND_API.G_RET_STS_UNEXP_ERROR.
  • X_MSG_COUNT – An integer indicating the number of messages on the FND_MSG_PUB package’s message stack at the end of API processing.

Sample Code: (Tested in R12.1.3)

DECLARE
        g_user_id             fnd_user.user_id%TYPE :=NULL;
        l_appl_id             fnd_application.application_id%TYPE;
        l_resp_id             fnd_responsibility_tl.responsibility_id%TYPE;
        l_api_version		  NUMBER := 1.0;
        l_init_msg_list       VARCHAR2(2) := fnd_api.g_false;
        l_commit		      VARCHAR2(2) := FND_API.G_FALSE;
        x_message_list        error_handler.error_tbl_type;
        x_return_status		  VARCHAR2(2);
        x_msg_count		      NUMBER := 0;
BEGIN
        SELECT fa.application_id
          INTO l_appl_id
          FROM fnd_application fa
         WHERE fa.application_short_name = 'INV';

        SELECT fr.responsibility_id
          INTO l_resp_id
          FROM fnd_application fa, fnd_responsibility_tl fr
         WHERE fa.application_short_name = 'INV'
           AND fa.application_id = fr.application_id
           AND UPPER (fr.responsibility_name) = 'INVENTORY';

        fnd_global.apps_initialize (g_user_id, l_resp_id, l_appl_id);

        EGO_ITEM_PUB.ASSIGN_ITEM_TO_ORG(
                   P_API_VERSION          => l_api_version
                ,  P_INIT_MSG_LIST        => l_init_msg_list
                ,  P_COMMIT               => l_commit
                ,  P_INVENTORY_ITEM_ID    => 1003
                ,  p_item_number          => 000000000001035
                ,  p_organization_id      => 11047
                ,  P_ORGANIZATION_CODE    => 'DXN'
                ,  P_PRIMARY_UOM_CODE     => 'EA'
                ,  X_RETURN_STATUS        => x_return_status
                ,  X_MSG_COUNT            => x_msg_count
            );
        DBMS_OUTPUT.PUT_LINE('Status: '||x_return_status);
        IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
          DBMS_OUTPUT.PUT_LINE('Error Messages :');
          Error_Handler.GET_MESSAGE_LIST(x_message_list=>x_message_list);
            FOR j IN 1..x_message_list.COUNT LOOP
              DBMS_OUTPUT.PUT_LINE(x_message_list(j).message_text);
            END LOOP;
        END IF;
EXCEPTION
        WHEN OTHERS THEN
          dbms_output.put_line('Exception Occured :');
          DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
END;
Advertisements

Item Category Creation APIs


There are few APIs in INV_ITEM_CATEGORY_PUB package related to item category. This article will follow a category flexfield structure. Please refer the below post for more detail.

How to Create Category and Category Set in Oracle Inventory?

INV_ITEM_CATEGORY_PUB.Create_Category

DECLARE
l_category_rec    INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE;
l_return_status   VARCHAR2(80);
l_error_code      NUMBER;
l_msg_count       NUMBER;
l_msg_data        VARCHAR2(80);
l_out_category_id NUMBER;
BEGIN
  l_category_rec.segment1 := 'RED';

  SELECT f.ID_FLEX_NUM
    INTO l_category_rec.structure_id
    FROM FND_ID_FLEX_STRUCTURES f
   WHERE f.ID_FLEX_STRUCTURE_CODE = 'INV_COLORS';

  l_category_rec.description := 'Red';

  INV_ITEM_CATEGORY_PUB.Create_Category
          (
          p_api_version   => 1.0,
          p_init_msg_list => FND_API.G_FALSE,
          p_commit        => FND_API.G_TRUE,
          x_return_status => l_return_status,
          x_errorcode     => l_error_code,
          x_msg_count     => l_msg_count,
          x_msg_data      => l_msg_data,
          p_category_rec  => l_category_rec,
          x_category_id   => l_out_category_id
          );
  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Creation of Item Category is Successful : '||l_out_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Creation of Item Category Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;

INV_ITEM_CATEGORY_PUB. Delete_Category

DECLARE
l_return_status VARCHAR2(80);
l_error_code    NUMBER;
l_msg_count     NUMBER;
l_msg_data      VARCHAR2(80);
l_category_id   NUMBER;
BEGIN
  SELECT mcb.CATEGORY_ID
    INTO l_category_id
    FROM mtl_categories_b mcb
   WHERE mcb.SEGMENT1='RED'
     AND mcb.STRUCTURE_ID =
        (SELECT mcs_b.STRUCTURE_ID
           FROM mtl_category_sets_b mcs_b
          WHERE mcs_b.CATEGORY_SET_ID =
               (SELECT mcs_tl.CATEGORY_SET_ID
                  FROM mtl_category_sets_tl mcs_tl
                 WHERE CATEGORY_SET_NAME ='INV_COLORS_SET'
                 )
        );

    INV_ITEM_CATEGORY_PUB.Delete_Category
          (
          p_api_version     => 1.0,
          p_init_msg_list   => FND_API.G_FALSE,
          p_commit          => FND_API.G_TRUE,
          x_return_status   => l_return_status,
          x_errorcode       => l_error_code,
          x_msg_count       => l_msg_count,
          x_msg_data        => l_msg_data,
          p_category_id     => l_category_id);

  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Deletion of Item Category is Successful : '||l_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Deletion of Item Category Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;

INV_ITEM_CATEGORY_PUB.Update_Category_Description
Updates the category description.

DECLARE
         l_return_status VARCHAR2(80);
         l_error_code    NUMBER;
         l_msg_count     NUMBER;
         l_msg_data      VARCHAR2(80);
         l_category_id   NUMBER;
         l_description   VARCHAR2(80);
BEGIN
      select mcb.CATEGORY_ID into l_category_id
        from mtl_categories_b mcb
       where mcb.SEGMENT1='BLACK'
         and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID
             from mtl_category_sets_b mcs_b
             where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID
                 from mtl_category_sets_tl mcs_tl
                 where CATEGORY_SET_NAME ='INV_COLORS_SET'));

      l_description := 'new black color';

     INV_ITEM_CATEGORY_PUB.Update_Category_Description (
       p_api_version     => 1.0,
       p_init_msg_list   => FND_API.G_FALSE,
       p_commit          => FND_API.G_TRUE,
       x_return_status   => l_return_status,
       x_errorcode       => l_error_code,
       x_msg_count       => l_msg_count,
       x_msg_data        => l_msg_data,
       p_category_id     => l_category_id,
       p_description     => l_description);

  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Update of Item Category Description is Successful : '||l_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Update of Item Category Description Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;

Use following API for assigning a category to a category set. A category will be available in the list of valid categoies for a category set only if it is assigned to the category set. This is a required step if for categories enforce list is checked on.

INV_ITEM_CATEGORY_PUB.Create_Valid_Category

Create a record in mtl_category_set_valid_cats.

DECLARE
        l_return_status   VARCHAR2(80);
        l_error_code      NUMBER;
        l_msg_count       NUMBER;
        l_msg_data        VARCHAR2(80);
        l_category_set_id NUMBER;
        l_category_id     NUMBER;
BEGIN
       select mcs_tl.CATEGORY_SET_ID into l_category_set_id
         from mtl_category_sets_tl mcs_tl
        where mcs_tl.CATEGORY_SET_NAME ='INV_COLORS_SET';

       select mcb.CATEGORY_ID into l_category_id
         from mtl_categories_b mcb
        where mcb.SEGMENT1='RED'
          and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID
              from mtl_category_sets_b mcs_b
              where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID
                    from mtl_category_sets_tl mcs_tl
                    where CATEGORY_SET_NAME ='INV_COLORS_SET'));

       INV_ITEM_CATEGORY_PUB.Create_Valid_Category (
             p_api_version        => 1.0,
             p_init_msg_list      => FND_API.G_FALSE,
             p_commit             => FND_API.G_TRUE,
             x_return_status      => l_return_status,
             x_errorcode          => l_error_code,
             x_msg_count          => l_msg_count,
             x_msg_data           => l_msg_data,
             p_category_set_id    => l_category_set_id,
             p_category_id        => l_category_id,
             p_parent_category_id => NULL );

  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Create Valid Category is Successful : '||l_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Create Valid Category Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;

INV_ITEM_CATEGORY_PUB.Delete_Valid_Category

Delete the record from mtl_category_set_valid_cats.

DECLARE
           l_return_status    VARCHAR2(80);
           l_error_code       NUMBER;
           l_msg_count        NUMBER;
           l_msg_data         VARCHAR2(80);
           l_category_set_id  NUMBER;
           l_category_id      NUMBER;
BEGIN
         select mcs_tl.CATEGORY_SET_ID into l_category_set_id
           from mtl_category_sets_tl mcs_tl
          where mcs_tl.CATEGORY_SET_NAME ='INV_COLORS_SET';

         select mcb.CATEGORY_ID into l_category_id
           from mtl_categories_b mcb
          where mcb.SEGMENT1='RED'
            and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID
                from mtl_category_sets_b mcs_b
                where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID
                  from mtl_category_sets_tl mcs_tl
                  where CATEGORY_SET_NAME ='INV_COLORS_SET'));

      INV_ITEM_CATEGORY_PUB.Delete_Valid_Category (
            p_api_version      => 1.0,
            p_init_msg_list    => FND_API.G_FALSE,
            p_commit           => FND_API.G_TRUE,
            x_return_status    => l_return_status,
            x_errorcode        => l_error_code,
            x_msg_count        => l_msg_count,
            x_msg_data         => l_msg_data,
            p_category_set_id  => l_category_set_id,
            p_category_id      => l_category_id);

  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Delete Valid Category is Successful : '||l_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Delete Valid Category Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;

The above scripts are tested in R12.1.3

Thanks….

How to Create Category and Category Set in Oracle Inventory?


 

Categories are the method by which the items in inventory can be separated logically and functionally for planning, purchasing and other activities.You can use categories and category sets to group your items for various reports and programs. A category is a logical classification of items that have similar characteristics. A category set is a distinct grouping scheme and consists of categories. The flexibility of category sets allows you to report and inquire on items in a way that best suits your needs. This article will describe how to create categories and category set in oracle inventory.

Suppose we need a category called ‘INV_COLORS’. We can define multiple colors in this category and then assign this category to an item.

Example:

  1. Item1 —- Black
  2. Item2 —- Red
  3. Item3 —- Green
  4. Item4 —- Orange

1] First we need to create a value set to hold these colors.

Navigation > Setup: Flexfields: Validation: Sets

Validation type Select: Independent

2] Next we need to enter our values in the INVENTORY_COLOR valueset

RED, GREEN, BLUE, BLACK, and ORANGE

Navigation -> Setup: Flexfields: Validation: Values

Save and close the Screen.

3] Now we need to create a KFF Structure

Navigation Setup: Flexfields: Key: Segments

Create the structure name: In the “Code” field enter INV_COLORS

4] Click on the “Segments” button.

  • Enter the “Number” field: 10
  • Enter the Name field: Color
  • Enter the “Window Prompt”: Color (This value will appear on the screen)
  • Enter the “Column” field: Segment1 (you can choose any column)

Save and exit the form.

5] Check the Freeze flex field Definition, the following warning will appear.

Click OK.

6] The “Compile” button is now available to be selected. Click on the compile button.

Click Ok

Close the form.

7] Go to View -> Request and Verify that the new Category flexfield compiled successfully.

8] The new structure is ready for use. Now let’s create a category.

Navigation : Setup: Items: Categories: Category Codes

  • Enter the structure name: INV_COLORES
  • Enter the category: BLACK
  • (Note the form does not provide an LOV for the categories. You will need to use edit symbol at the top of the page or “ e “ to bring up the lov)
  • Enter the description.

9] Next we create our category set.

Navigation Setup: Items: Categories: Category Sets

  • Fill in the category set Name: INV_COLORS_SET
  • The description: Inventory color set
  • The Flex Structure: INV_COLORS
  • The Controlled: Org Level
  • Default Category: BLACK

10] After creating the category set, we can assign it to any items.

There are few category APIs that will be discussed in upcoming posts. Till then GOOD BYE………!! Have a nice day!

Reference: How to create a Category Set and Assign Items to Categories [MOS ID 423551.1]

How to assign an Item to a Catalog Group?


 

Use of Item catalog in Oracle Inventory is to Group items that share common characteristics. In order to define catalog, we can setup distinct catalog groups to partition the Item master. Each catalog group in turn has a set of unique characteristics called as Descriptive elements that describe the items belonging to the Group. Oracle Inventory provides item catalogs to help you quickly locate items based on key characteristics.

Benefits

Once the process of defining and cataloging items is complete, you can:

  • Provide standard language in referring to items, enabling companies in the same industry to communicate effectively without needing to name their items identically.
  • Store a detailed description of what an item is without having to embed that meaning in the item number.
  • Use descriptive elements as search criteria for items.
  • Update the item description with a concatenated value of the item catalog group information.
  • Find common existing parts when developing an engineering prototype.

To assign an item to a catalog:

1] Navigate to the Master Items Summary window and select an item.

2] Click on Tools > Catalog of the Master Item Screen.

3] Enter a catalog group. The descriptive elements for this catalog group display in the Name field. Assigning the item to this group means the item shares these descriptive elements.

4] Enter a specific value for each descriptive element that pertains to the item.

5] Indicate whether to include a descriptive element in a catalog-derived item description.

6] Save your work.

To replace the existing item description with a catalog-derived item description:

Choose Update Description.

This creates an item description by concatenating the item catalog group description (default) or the catalog group name with the descriptive element values you defined. The concatenated item description displays in the Item Catalog Description field.

How to Import Item Catalog Descriptive Element Values?


Oracle has provided the below two public APIs to import Item Catalog Descriptive Element Values.

  1. inv_item_catalog_elem_pub.process_item_catalog_grp_recs
  2.  inv_item_catalog_elem_pub.process_item_descr_elements

The first API, process_item_catalog_grp_recs, is used in batch mode to import element values for a SET of items. The second API, process_item_descr_elements, is used to import element values for a SINGLE item.

Using Procedure process_item_catalog_grp_recs:

1] Get the Item Number.

2] Get the item_catalog_group_id from mtl_item_catalog_groups.

3] Get the Descriptive Element Names (element_name) from the table mtl_descriptive_elements for the above item_catalog_group_id.

4] Insert the data into mtl_desc_elem_val_interface table.

INSERT INTO mtl_desc_elem_val_interface (
       item_number,
       element_name,
       element_value,
       element_sequence,
       process_flag,
       set_process_id )
VALUES (
       l_item_number, 	-- Derived in Step1
       l_element_name, 	-- Derived in Step3
       l_element_value,	-- Insert the Descriptive Element Value from data file
       10, 	            -- Insert the element sequence
       1,
       1
       );

5] Run loop to put all the data (element name-value pair) into the above interface table.

6] Run loop to put data for multiple items.

7] Finally run the below API.

inv_item_catalog_elem_pub.process_item_catalog_grp_recs
          (
          errbuf                 	=> l_errbuf,
          retcode                	=> l_retcode,
          p_rec_set_id           	=> 1,
          p_upload_rec_flag      	=> 1,
          p_delete_rec_flag      	=> 1,
          p_commit_flag          	=> 1,
          p_prog_appid           	=> NULL,
          p_prog_id              	=> NULL,
          p_request_id           	=> NULL,
          p_user_id              	=> NULL,
          p_login_id             	=> NULL
          );

The parameters are described below:

Parameter Type Meaning
errbuf Out It holds the error message, if any.
retcode Out It should be zero if there were no errors
p_rec_set_id In Used to group the rows, should be set to value of “set_process_id” in mtl_desc_elem_val_interface table
p_upload_rec_flag In Whether the rows in interface table are to be uploaded to database. default 1
p_delete_rec_flag In Whether the rows in interface table are to be deleted after they have been uploaded to database. default 1
p_commit_flag In Whether the uploaded rows need to be committed to the database. default 1
p_prog_appid In default null
p_prog_id In default null
p_request_id In default null
p_user_id In default null
p_login_id In default null

Using Procedure process_item_descr_elements:

Define necessary values programmatically in PL/SQL variables and tables and call this API. Do not populate the interface table.

The declaration of this API:

PROCEDURE Process_item_descr_elements
     (
        p_api_version        	   IN   NUMBER
     ,  p_init_msg_list      		IN   VARCHAR2
     ,  p_commit_flag        		IN   VARCHAR2
     ,  p_validation_level   		IN   NUMBER
     ,  p_inventory_item_id  		IN   NUMBER
     ,  p_item_number        		IN   VARCHAR2
     ,  p_item_desc_element_table 	IN  ITEM_DESC_ELEMENT_TABLE
     ,  x_generated_descr    		OUT NOCOPY VARCHAR2
     ,  x_return_status      		OUT NOCOPY VARCHAR2
     ,  x_msg_count          		OUT NOCOPY NUMBER
     ,  x_msg_data           		OUT NOCOPY VARCHAR2
     );

The parameters are:

Parameter Meaning
p_api_version The version of this API. Value=1.0
p_init_msg_list If set to true initially, messages generated internally by the API will be captured.  Value = fnd_api.g_TRUE, if messages need to be captured, else accept default
p_commit_flag Whether the uploaded rows need to be committed to the database. Value = fnd_api.g_TRUE, to commit else accept default.
p_validation_level Determines if item_number is to be converted to item_id. If item_id is specified, set Value = g_VALIDATE_NONE, Else take default.
p_inventory_item_id Item id of the item for which the element values need to be uploaded.
p_item_number Item number of the item.
p_item_desc_element_table PL/SQL table of records; each record will hold element name, value and description.
x_generated_descr Element values are concatenated to generate item description.
x_return_status The return status of the API.
x_msg_count Count of messages generated.
x_msg_data Holds the messages generated.

Flexfields in Oracle Inventory


Oracle Inventory provides the following flexfields:

1] Account Aliases

Flexfield Code: MDSP

Table: MTL_GENERIC_DISPOSITIONS

Unique ID Column: DISPOSITION_ID

Comment: This key flexfield supports only one structure.

2] Item Catalogs

Flexfield Code: MICG

Table: MTL_ITEM_CATALOG_GROUPS

Unique ID Column: ITEM_CATALOG_GROUP_ID

Comment: This key flexfield supports only one structure.

3] Item Categories

Flexfield Code: MCAT

Table: MTL_CATEGORIES

Unique ID Column: CATEGORY_ID

Comment: You must design and configure your Item Categories Flexfield before you can start defining items since all items must be assigned to categories. You can define multiple structures for your Item Categories Flexfield, each structure corresponding to a different category grouping scheme. You can then associate these structures with the categories and category sets you define.

4] Sales Orders

Flexfield Code: MKTS

Table: MTL_SALES_ORDERS

Unique ID Column: SALES_ORDER_ID

Comment: The Sales Order Flexfield is a key flexfield used by Oracle Inventory to uniquely identify sales order transactions Oracle Order Entry interfaces to Oracle Inventory. Your Sales Order Flexfield should be defined as Order Number, Order Type, and Order Source. This combination guarantees each transaction to Inventory is unique. You must define this flexfield before placing demand or making reservations in Oracle Order Entry. You must set up the OE: Source Code profile option to determine the source code you will use in for the third segment of this flexfield to guarantee that each transaction is unique. (Oracle Inventory defaults the value of the OE: Source Code profile option to ‘ORDER ENTRY’.)

5] Stock Locators

Flexfield Code: MTLL

Table: MTL_ITEM_LOCATIONS

Unique ID Column: INVENTORY_LOCATION_ID

Comment: You can use the Stock Locators Flexfield to capture more information about stock locators in inventory. If you do not have Oracle Inventory installed, or none of your items have locator control, it is not necessary to set up this flexfield. If you keep track of specific locators such as aisle, row, bin indicators for your items, you need to configure your Stock Locators Flexfield and implement locator control in your organization.  This key flexfield supports only one structure.

6] System Items

Flexfield Code: MSTK

Table: MTL_SYSTEM_ITEMS

Unique ID Column: INVENTORY_ITEM_ID

Comment: You can use the System Items Flexfield (also called the Item Flexfield) for recording and reporting your item information. You must design and configure your Item Flexfield before you can start defining items. All Oracle Applications products that reference items share the Item Flexfield and support multiple-segment implementations. However, this flexfield supports only one structure. You must set up your OE: Item Flexfield profile option to specify the Item Flexfield structure that you will use for your Oracle applications. Users can also set up the OE: Item Flexfield Entry Method profile option to specify your preferred method of entry for this flexfield.

Key Tables in Oracle Inventory


Here is a brief description of the key tables in Oracle Inventory.

 

Table Description
MTL_PARAMETERS It maintains a set of default options like general ledger accounts; locator, lot, and serial controls, inter-organization options, costing method, etc. for each organization defined in Oracle Inventory. Each organization’s item master organization (MASTER_ORGANIZATION_ID) and costing organization (COST_ORGANIZATION_ID) are maintained here.
MTL_SYSTEM_ITEMS_B This is the definition table for items. This table holds the definitions for inventory items, engineering items, and purchasing items. The primary key for an item is the INVENTORY_ITEM_ID and ORGANIZATION_ID. Therefore, the same item can be defined in more than one organization. Items now support multilingual description. MLS is implemented with a pair of tables: MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL. Translations table (MTL_SYSTEM_ITEMS_TL) holds item Description and Long Description in multiple languages.
MTL_ITEM_STATUS This is the definition table for material status codes. Status code is a required item attribute. It indicates the status of an item, i.e., Active, Pending, Obsolete.
MTL_UNITS_OF_MEASURE_TL This is the definition table for both the 25-character and the 3-character units of measure. The base_uom_flag indicates if the unit of measure is the primary unit of measure for the uom_class. Oracle Inventory uses this table to keep track of the units of measure used to transact an item.
MTL_ITEM_LOCATIONS This is the definition table for stock locators. The associated attributes describe which subinventory this locator belongs to, what the locator physical capacity is, etc.
MTL_ITEM_CATEGORIES This table stores inventory item assignments to categories within a category set. For each category assignment, this table stores the item, the category set, and the category. Items always may be assigned to multiple category sets. However, depending on the Multiple Assignments Allowed attribute value in a given category set definition, an item can be assigned to either many or only one category in that category set.
MTL_CATEGORIES_B This is the code combinations table for item categories. Items are grouped into categories within the context of a category set to provide flexible grouping schemes. Item categories now support multilingual category description. MLS is implemented with a pair of tables: MTL_CATEGORIES_B and MTL_CATEGORIES_TL. MTL_CATEGORIES_TL table holds translated Description for Categories.
MTL_CATEGORY_SETS_B It contains the entity definition for category sets. A category set is a categorization scheme for a group of items. Items may be assigned to different categories in different category sets to represent the different groupings of items used for different
purposes. An item may be assigned to only one category within a category set, however. STRUCTURE_ID identifies the flexfield structure associated with the category set. Category Sets now support multilingual category set name and description. MLS is implemented with a pair of tables: MTL_CATEGORY_SETS_B and MTL_CATEGORY_SETS_TL. MTL_CATEGORY_SETS_TL table holds translated Name and Description for Category Sets.
MTL_DEMAND This table stores demand and reservation information used in Available To Promise, Planning and other Manufacturing functions. There are three major row types stored in the table: Summary Demand rows,
Open Demand Rows, and Reservation Rows.
MTL_SECONDARY_INVENTORIES This is the definition table for the subinventory. A subinventory is a section of inventory, i.e., raw material, finished goods, etc. Subinventories are assigned to items (in a many to one relationship), indicating a list of valid places where this item will physically exist in inventory.
MTL_ONHAND_QUANTITIES

 

It stores quantity on hand information by control level and location. It is maintained as a stack of receipt records, which are consumed by issue transactions in FIFO order. The quantity on hand of an item at any particular control level and location can be found by summing TRANSACTION_QUANTITY for all records that match the criteria.
MTL_TRANSACTION_TYPES It contains seeded transaction types and the user defined ones. USER_DEFINED_FLAG will distinguish the two. The table also stores the TRANSACTION_ACTION_ID and TRANSACTION_SOURCE_TYPE_ID that is associated with each transaction type.
MTL_MATERIAL_TRANSACTIONS This table stores a record of every material transaction or cost update performed in Inventory. Records are inserted into this table either through the transaction processor or by the standard cost update program. The columns TRANSACTION_TYPE_ID, TRANSACTION_ACTION_ID, TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_SOURCE_ID and TRANSACTION_SOURCE_NAME describe what the transaction is and against what entity it was performed.
MTL_ITEM_ATTRIBUTES This table stores information on item attributes. Each
row in the table corresponds to an attribute. The table stores the attribute name, the corresponding user-friendly name seen by the users, and the kind of validation enforced on the attribute.
MTL_ITEM_CATALOG_GROUPS_B This is the code combinations table for item catalog groups. An item catalog group consists of items that can be described by the same set of descriptive elements or item properties. When an item is associated with an item catalog group, the item inherits the descriptive elements for that group which then behave like additional item attributes.
MTL_ITEM_REVISIONS_B It stores revision levels for an inventory item. When an item is defined a starting revision record is written out to this table, so every item will at least have one starting revision.
MTL_ITEM_TEMPLATES_B This is the definition table for item templates. It
contains the user-defined name (TEMPLATE_NAME) and description (DESCRIPTION) ONLY for backward compatibility. You can use a template to set certain item attributes.
MTL_DESCRIPTIVE_ELEMENTS It stores the descriptive element definitions for an item catalog group. Descriptive elements are defining properties used to describe in the catalog group.
MTL_DESCR_ELEMENT_VALUES It stores the descriptive element values for a specific item. When an item is associated with a particular item catalog group, one row per descriptive element (for that catalog group) is inserted into this table.
ORG_ACCT_PERIODS It holds the open and closed financial periods for organizations.
MTL_CUSTOMER_ITEMS It stores customer item information for a specific customer. Each record can be defined at one of the following levels: Customer, Address Category, and Address. The customer item definition is organization independent.
MTL_SYSTEM_ITEMS_INTERFACE It temporarily stores the definitions for inventory items, engineering items and purchasing items before loading this information into Oracle Inventory.
MTL_TRANSACTIONS_INTERFACE It allows calling applications to post material transactions (movements, issues, receipts etc. to Oracle Inventory  transaction module.
MTL_ITEM_REVISIONS_INTERFACE It temporarily stores revision levels for an inventory item before loading this information into Oracle Inventory.
MTL_ITEM_CATEGORIES_INTERFACE This table temporarily stores data about inventory item assignments to category sets and categories before loading this information into Oracle Inventory.
MTL_DESC_ELEM_VAL_INTERFACE This table temporarily stores descriptive element values for an item that is associated with an item catalog group before loading this information into Oracle Inventory.
MTL_DEMAND_INTERFACE It is the interface point between non-Inventory applications and the Inventory demand module. Records inserted into this table are processed by the Demand Manager concurrent program.
MTL_INTERFACE_ERRORS It stores errors that occur during the item interface process reporting where the errors occurred along with the error messages.

 

Item Conversion – A Technical Note


This conversion is done for import items from any source into Oracle Inventory and Oracle Engineering.

Description:

When you import items through the Item Interface, you create new items in your item master organization or assign existing items to additional organizations. You can specify values for all the item attributes, or you can specify just a few attributes and let the remainder default or remain null. Here we are importing items with template.

Interface Table:

  • The interface table for Item Import is MTL_SYSTEM_ITEMS_INTERFACE and it contains every column in the Inventory item master table, MTL_SYSTEM_ITEMS.
  • The columns in the item interface correspond directly to those in the item master table.
  • Except for ITEM_NUMBER or SEGMENTn columns, ORGANIZATION_CODE or ORGANIZATION_ID, DESCRIPTION, PROCESS_FLAG, and TRANSACTION_TYPE, all other columns are optional, either because they have defaults that can be derived, or because the corresponding attributes are optional and may be left null.
  • If you are importing revision details for your new items, you can use the MTL_ITEM_REVISIONS_INTERFACE table. This table is used only for revision information, and is not mandatory.

Required Data:

ITEM_NUMBER or SEGMENTn columns

Every row in the item interface table must identify the item and organization. To identify the item when importing it, you may specify either the ITEM_NUMBER or SEGMENTn columns—the Item Interface generates the INVENTORY_ITEM_ID for you.

ORGANIZATION_ID or ORGANIZATION_CODE

You need to specify either the ORGANIZATION_ID or ORGANIZATION_CODE that identifies the organization.

DESCRIPTION

When you import a new item, you are also required to specify the DESCRIPTION.

TRANSACTION_TYPE & PROCESS_FLAG

There are two other columns the Item Interface uses to manage processing. They are TRANSACTION_TYPE, which tells the Item Interface how to handle the row, and PROCESS_FLAG, which indicates the current status of the row.

Always set the TRANSACTION_TYPE column to CREATE, to create an item record (true when both importing a new item and assigning an already existing item to another organization). This is the only value currently supported by the Item Interface. The Item Interface uses the PROCESS_FLAG to indicate whether processing of the row succeeded or failed. When a row is ready to be processed, give the PROCESS_FLAG a value of 1 (Pending), so that the Item Interface can pick up the row and process it into the production tables.

Meaning of PROCESS_FLAG Values:

Code Code Meaning
1 Pending
2 Assign complete
3 Assign/validation failed
4 Validation succeeded; import failed
5 Import in process
7 Import succeeded

Note:

When more than one of these columns has been entered and they conflict, ITEM_NUMBER overrides SEGMENTn and ORGANIZATION_ID overrides ORGANIZATION_CODE. It is strongly recommended that you use SEGMENT column instead of ITEM_NUMBER.

For performance purpose, it is advised to batch set of records using set_process_id column and then run import program for that set_process_id. The item import (IOI) program can be run in parallel if separate set_process_ids are passed while submitting.

Derived Data:

Many columns have defaults that the Item Interface uses when you leave that column null in the item interface table. Please refer Oracle Manufacturing APIs and Open Interfaces Manual for more details.

Validations:

1] Validation for organization code (standard table: ORG_ORGANIZATION_DEFINITIONS)

2] Validation for Item_number (standard table: mtl_system_items_b)

3] Validation for Description

4] Validation for Primary Unit of Measure (standard table: MTL_UNITS_OF_MEASURE)

5] Validation for Template Name (standard table: mtl_item_templates)

6] Validation for Item Type (standard table: FND_COMMON_LOOKUPS)

7] Validation for ENCUMBRANCE_ACCOUNT (standard table: gl_code_combinations)

8] Validation for EXPENSE_ACCOUNT (standard table: gl_code_combinations)

Note: You can add more validation as per your business requirement.

Record Insertion:

Through your custom program you can insert the below columns in the interface table. Again the list is a sample one; you can add additional columns as your business requirement.

Columns inserted:

  • ORGANIZATION_ID                     –>Taken from ORG_ORGANIZATION_DEFINITIONS table
  • ORGANIZATION_CODE               –>Taken from Staging table
  • LAST_UPDATE_DATE                 –>sysdate
  • LAST_UPDATED_BY                   –>fnd_global.user_id
  • CREATION_DATE                        –>sysdate
  • CREATED_BY                             –>fnd_global.user_id
  • LAST_UPDATE_LOGIN                 –>fnd_global.login_id
  • DESCRIPTION                             –>Taken from Staging table
  • SEGMENT1                                 –>Taken from Staging table
  • PRIMARY_UOM_CODE                 –>Taken from Staging table
  • PRIMARY_UNIT_OF_MEASURE     –>Taken from MTL_UNITS_OF_MEASURE
  • ITEM_TYPE                                  –>NULL
  • TEMPLATE_NAME                       –>Taken from Staging table
  • TEMPLATE_ID                             –>Taken from mtl_item_templates        
  • MIN_MINMAX_QUANTITY             –>Taken from Staging table
  • MAX_MINMAX_QUANTITY            –>Taken from Staging table
  • LIST_PRICE_PER_UNIT                –>Taken from Staging table
  • ITEM_CATALOG_GROUP_ID        –>Taken from Staging table
  • SET_PROCESS_ID                       –>1
  • PROCESS_FLAG                          –>1
  • TRANSACTION_TYPE                  –>‘CREATE’

Standard Concurrent Program:

After you insert valid data into Interface table, you can go to Items > Import > Import Items and run the standard concurrent program. Here is the parameter form.

1] All Organizations:

  • Yes: Run the interface for all organization codes in the item interface table.
  • No: Run the interface only for the organization you are currently in. Item interface rows for organizations other than your current organization are ignored.

2] Validate Items:

  • Yes: Validate all items and their data residing in the interface table that have not yet been validated. If items are not validated, they will not be processed into Oracle Inventory.
  • No:  Do not validate items in the interface table.

3] Process Items:

  • Yes: All qualifying items in the interface table are inserted into Oracle Inventory.
  • No: Do not insert items into Oracle Inventory.

4] Delete Processed Rows:

  • Yes: Delete successfully processed items from the item interface tables.
  • No: Leave all rows in the item interface tables.

5] Process Set:

Enter a number for the set id for the set of rows you want to process. The program picks up the rows marked with that id in the SET_PROCESS_ID column. If you leave this field blank, all rows are picked up for processing regardless of the SET_PROCESS_ID column value.

Working with failed interface rows:

If a row fails validation, the Item Interface sets the PROCESS_FLAG to 3 (Assign/validation failed) and inserts a row in the interface errors table, MTL_INTERFACE_ERRORS. To identify the error message for the failed row, the program automatically populates the TRANSACTION_ID column in this table with the TRANSACTION_ID value from the corresponding item interface table.

The UNIQUE_ID column in MTL_INTERFACE_ERRORS is populated from the sequence MTL_SYSTEM_ITEMS_INTERFACE_S. Thus, for a given row, the sequence of errors can be determined by examining UNIQUE_ID for a given TRANSACTION_ID.

You should resolve errors in the sequence that they were found by the interface, that is, in increasing order of UNIQUE_ID for any TRANSACTION_ID.

Resubmitting an Errored Row:

During Item Interface processing, rows can error out either due to validation (indicated by PROCESS_FLAG = 3 in MTL_SYSTEM_ITEMS_INTERFACE and the corresponding error in MTL_INTERFACE_ERRORS) or due to an Oracle Error.

When an Oracle Error is encountered, the processing is stopped and everything is rolled back to the previous save point. This could be at PROCESS_FLAG = 1, 2, 3, or 4.

When you encounter rows errored out due to validations, you must first fix the row corresponding to the error with the appropriate value. Then reset PROCESS_FLAG = 1, INVENTORY_ITEM_ID = null, and TRANSACTION_ID = null. Then resubmit the row for reprocessing.

Useful Query:

Select
  SEGMENT1,
   DESCRIPTION,
   PROCESS_FLAG,
   SET_PROCESS_ID,
   INVENTORY_ITEM_ID,
   ORGANIZATION_ID,
   ORGANIZATION_CODE,
   CREATION_DATE,
   ITEM_TYPE,
   UNIT_OF_ISSUE,
   TEMPLATE_ID,
   TEMPLATE_NAME,
   EXPENSE_ACCOUNT,
   ENCUMBRANCE_ACCOUNT,
   PRIMARY_UOM_CODE,
   PRIMARY_UNIT_OF_MEASURE,
   MIN_MINMAX_QUANTITY,
   MAX_MINMAX_QUANTITY,
   TAX_CODE,
   REQUEST_ID
from
    MTL_SYSTEM_ITEMS_INTERFACE
order by CREATION_DATE;
----------------------------
Select
   ORGANIZATION_ID,
   UNIQUE_ID,
   REQUEST_ID ,
   TABLE_NAME ,
   COLUMN_NAME,
   ERROR_MESSAGE
   CREATION_DATE,
   MESSAGE_TYPE
from
    MTL_INTERFACE_ERRORS
order by CREATION_DATE;

Useful Metalink Note:

  1. FAQ for Item Import [ID 109628.1]
  2. Error in Validating MTL_SYSTEM_ITEMS_INTERFACE [ID 1057175.6]
  3. A Guideline to IOI Error Messages and Solutions [ID 52746.1]
  4. How to Import Item Costs Using the Item Open Interface (IOI) [ID 191376.1]
  5. Item Import Performance Tips [ID 66496.1]
  6. INCOIN: Basic Steps for Researching Failed Item Imports [ID 552683.1]
  7. Troubleshooting Guide for INV_IOI_MASTER_CHILD Errors in Item Import [ID 429924.1]
  8. How To Prepare to Run Parallel Runs of the Item Import Interface INCOIN [ID 842767.1]
  9. Item Import Gives Error: “The Default Primary Unit of Measure Is Invalid” [ID 789927.1]
  10. Understanding Item Import and Debugging Problems with Item Import [ID 268968.1]
  11. How to create a Category Set and Assign Items to Categories [ID 423551.1]
  12. Item API Or Concurrent Manager, Which One Is Best For Item Creation? [ID 760498.1]