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 (
       set_process_id )
       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

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.

          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.

4 Responses to How to Import Item Catalog Descriptive Element Values?

  1. srinivas says:

    From which table will we get item_number?


  2. Joe says:

    Two questions:
    1) The interface doesn’t need to have the item_catalog_group_id value? That seems strange, only needing the element name.
    2) Related to question 1, what if there are no current records for this item in the MTL_DESCR_ELEMENT_VALUES table? I know that when you assign an Item to a Catalog Group using the Oracle Applications form. Once you do this, even if you don’t enter any values in the form, rows will be inserted into the MTL_DESCR_ELEMENT_VALUES table with element_values = NULL. So does this need to be done before the interface will work? In my scenario, I don’t want to have to go in and assign each item to a catalog group manually; I have a few thousand that I need to do.

    • Joe says:

      Well, I figured it out. I had to use the EGO_ITEM_PUB.Process_Items API to update the Item Catalog Category which inserts the null value rows into the MTL_DESCR_ELEMENT_VALUES table, then run the API for updating the element_values. See Doc ID 1152476.1.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: