This appendix covers the following topics:
You can define an unlimited number of attribute groups for items without programming them. You can define attributes using the setup user interface; definitions are stored in metadata tables. However, you may also need to read attribute data to interface with third party or custom systems.
Generate database views to read User-defined attributes data
Using the attribute group setup screens, you can generate database views for reading user-defined attributes data. These views hide the complexity of dealing with the attribute meta-data.
Using PL/SQL to Access Data for Attributes
Another way to access user-defined attribute data for a particular item is to use PL/SQL. The EGO_USER_ATTRS_DATA_PUB procedure follows:
| p_api_version | IN NUMBER |
| ,p_object_name | IN VARCHAR2 |
| ,p_pk_column_name_value_pairs | IN EGO_COL_NAME_VALUE_PAIR_ARRAY |
| ,p_attr_group_request_table | IN EGO_ATTR_GROUP_REQUEST_TABLE |
| ,p_user_privileges_on_object | IN EGO_VARCHAR_TBL_TYPE DEFAULT NULL |
| ,p_entity_id | IN VARCHAR2 DEFAULT NULL |
| ,p_entity_index | IN NUMBER DEFAULT NULL |
| ,p_entity_code | IN VARCHAR2 DEFAULT NULL |
| ,p_debug_level | IN NUMBER DEFAULT 0 |
| ,p_init_error_handler | IN VARCHAR2 DEFAULT FND_API.G_FALSE |
| ,p_init_fnd_msg_list | IN VARCHAR2 DEFAULT FND_API.G_FALSE |
| ,p_add_errors_to_fnd_stack | IN VARCHAR2 DEFAULT FND_API.G_FALSE |
| ,p_commit | IN VARCHAR2 DEFAULT FND_API.G_FALSE |
| ,x_attributes_row_table | OUT NOCOPY EGO_USER_ATTR_ROW_TABLE |
| ,x_attributes_data_table | OUT NOCOPY EGO_USER_ATTR_DATA_EXT_TABLE |
| ,x_return_status | OUT NOCOPY VARCHAR2 |
| ,x_errorcode | OUT NOCOPY NUMBER |
| ,x_msg_count | OUT NOCOPY NUMBER |
| ,x_msg_data | OUT NOCOPY VARCHAR2 |
p_api_version
Callers should pass a constant value of 1.0.
p_object_name
Callers should pass a constant value of 'EGO_ITEM'
p_pk_column_name_value_pairs
This is a table of EGO_COL_NAME_VALUE_PAIR_OBJ objects specifying the Inventory Item ID and Organization ID for which the caller wants attribute data. Callers should create the table with code like the following:
l_pk_column_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
l_pk_column_values := EGO_COL_NAME_VALUE_PAIR_ARRAY(
EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID', <your inventory item ID>) ,EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID', <your organization ID>));
p_attr_group_request_table
This is a table of EGO_ATTR_GROUP_REQUEST_OBJ objects, one for each attribute group whose values the caller wants. The objects also support requests for only some of the attributes in the attribute group. Each EGO_ATTR_GROUP_REQUEST_OBJ object in the table will look like the following:
EGO_ATTR_GROUP_REQUEST_OBJ(
NULL,
431,
'EGO_ITEMMGMT_GROUP',
<your Attribute Group internal name>,
<your Revision ID if the Attribute Group is associated at the Revision level; otherwise NULL>,
NULL,
NULL
<an optional comma-delimited list of the Attribute internal names whose values you want, or NULL>);
p_user_privileges_on_object
This is an optional table of VARCHAR2s, one for each privilege the caller wants validated against the View privileges of the attribute groups being requested. For example, if a user invokes a procedure that calls Get_User_Attrs_Data, and the calling procedure wants to enforce attribute group data security, the calling procedure could pass a list of the user's privileges based on his/her role on the current item. If this parameter is non-null, Get_User_Attrs_Data will enforce data security for those attribute groups that have a View privilege defined.
p_entity_id, p_entity_index, p_entity_code
Parameters for use with ERROR_HANDLER package. Can safely be defaulted unless caller wants to distinguish among errors for multiple items; if so, please investigate the ERROR_HANDLER package for more details.
p_debug_level
Parameter to control debug logging; can safely be defaulted.
p_init_error_handler, p_init_fnd_msg_list, p_add_errors_to_fnd_stack
More parameters for use with ERROR_HANDLER package; can safely be defaulted.
p_commit
Controls whether or not Get_User_Attrs_Data issues a COMMIT statement at the end of processing; present to comply with API standards, but since Get_User_Attrs_Data does not perform any DML, can safely be defaulted.
x_attributes_row_table
This is a table of EGO_USER_ATTR_ROW_OBJ objects, each one representing a row of user-defined attributes data (for example, one database table row of data). It contains the Extension ID value from EGO_MTL_SY_ITEMS_EXT_VL (stored as ROW_IDENTIFIER) and serves mainly as a way to organize the objects in x_attributes_data_table.
x_attributes_data_table
This is a table of EGO_USER_ATTR_DATA_OBJ objects, each one representing a single attribute value. All of the attribute values for a given row of data will share the same ROW_IDENTIFIER value; as with x_attributes_row_table, the ROW_IDENTIFIER value is the Extension ID from EGO_MTL_SY_ITEMS_EXT_VL. Each object also contains the attribute internal name (stored as ATTR_NAME) and its value (stored as ATTR_DISP_VALUE).
x_return_status, x_errorcode, x_msg_count, x_msg_data
Four standard OUT parameters indicating what happened in processing. The first, x_return_status, will equal FND_API.G_RET_STS_SUCCESS if processing succeeded, FND_API.G_RET_STS_ERROR if an expected error case was encountered, or FND_API.G_RET_STS_UNEXP_ERROR if something went wrong during processing. X_errorcode is not used. X_msg_count indicates how many error messages were logged with ERROR_HANDLER; if x_msg_count is 1, then x_msg_data contains that message (to save the caller the inconvenience of interacting with ERROR_HANDLER for only one message).