User Defined Attributes Framework – Extend your data capturing in R12.

2 EBS
Kishor Genikala & Sabarinadh Idupuganti
Oracle India Private limited

Abstract
'User Defined Attributes' Framework provides business to capture additional information through a set of user defined attributes
(UDA), across various EBS modules. This enables users to define unlimited number of attributes, attribute groups and pages to
suit business requirements and is particularly useful if users have exhausted the limit of pre-defined attributes in the existing
Descriptive Flex fields framework. Attributes can be combined into attribute groups and these attribute groups can be combined
into pages. There are numerous data types and display styles for an attribute and users can select a value from a pre-defined list of
values. These attributes can be populated using Interface and APIs as well. This paper provides an insight into Business
requirements, Solution overview, Key features which includes setup of additional attributes. It also demonstrates capturing the
attribute information, Process Flow, Data Flow with a use case of Order Management.

Scope
Business Need
Solution Overview
Key Elements
Setups
Process & Data flows
Solution constraints and limitations
Common Setup Issues

I. Business Need
There is a need for scalable solution to track unlimited amounts of attribute information related to Projects, Products, Sales
Orders etc.
For example – when we are importing Sales Orders from multiple systems we have to capture information like – the system
name, country specific information, date of the order, local names etc.
Another example, when key business users are analyzing the data they need to know the distance between the warehouse and
customer site to take decision on minimizing the shipping, additional price details, etc.
The standard Descriptive flex fields typically offer from 15 to 30 attributes depending on the record/table. This will limit
customer to capture additional information in these fields. For complex business model this is a serious limitation and Customers
have always wanted a capability to be able to extend the available attribute.

II. Solution Overview

One of the solution is to increase the number of attributes available in the existing DFF’s attached to corresponding entity like
Items, Orders, and Projects etc. But this is a very invasive change requiring changes all over EBS code. Moreover it will be a
limited solution and requires to revisit the same when the exposed attributes are exhausted.
PIM (Product Information Management) has developed a new framework on top of Oracle Applications Framework (OA),
enabling the creation/maintenance of user-defined attributes for business objects. The most promising feature of this framework
is that it exposes unlimited number of user defined attributes per object instance.
In the DFF implementation the attributes are stored in the same table as the base table.
In UDA framework the additional attributes are stored in a different table and there can exist multiple rows associated to an
object instance in the base table. This makes it extensible in an infinite fashion.
UDA framework allows attributes to be stored as individual records in database table when compared to DFF, which allows
storage in specific column of one record, related to the entity.
Current DFF’s on the respective entities will co-exist with the new UDA’s.
III. Key Elements
Attribute: An attribute is a basic database field which stores additional information. There are numerous data types and display
styles for an attribute and user can select a value from a pre-defined list of values. An attribute can be defined as an
Optional/Required as per the business need.

Examples of data types are: alphanumeric, numeric, date, date time and translatable text.
Examples of display styles are: Text Field, Check Box, Hidden, Static URL, Dynamic URL and Radio Group
Attribute Group (AG): Group of attributes is called Attribute Group. An attribute group can be defined as a Single Row or
Multi Row.
 An attribute in a Multi Row attribute Group can have multiple values per object instance.
For example, “Operating Systems” will be a Multi Row AG for a Motherboard item.
 An attribute in a Single Row attribute Group can only have one value per object instance.
For example, “Motherboard Specification” will be a Single Row AG for a Motherboard item.
Classification: Classification of Objects. For example, Item is classified using Item Catalog Category, Project is classified using
Project Type etc. There is no classification defined for OM UDA’s, hence a system-defined default classification called
‘ADMIN_DEFINED’ is used.
Associations: Multiple AGs can be Associated to a classification. For example you can have different AGs associated with
different “Project Type”
UDA Page: A UDA page is created for a particular classification like Project type, Item Catalog Category etc. Multiple AGs
from the same classification can be selected to be displayed in that page. So you can have multiple AGs displayed in a single
page
Entity: An entity is an object under which the Attributes, AGs, and UDA pages are defined. Some of the key entities for which
UDA pages can be defined are Item, Order Header, Order Line, Project Header, Project line etc. You can have multiple UDA
Pages defined for a single Entity.

IV. Setup Flow

Below mentioned diagram represents overall Setup steps involved in enabling UDAs:

Select the Define AG Define Assign AG Create a Assign AG

Entity for the Attributes to Page to the Page
Entity for the AG Association

Below mentioned illustrates an example of setup steps involved in enabling UDAs for Entities- Sales Order Headers and Lines.
Step1: Add required Functions & Menus:
A sub menu ‘Order Management Extension HTML tab’ is created and added under ‘Order Management Extension Home Page’
menu.
Sub Menu ‘Order Management Extension Home Page’ is created and added under ‘ONT_SETUP’ menu without prompt to
include all the functions related to UDA.

A new function ‘Order Management Extension Setup’ is added under ‘ONT_SETUP_ORDERS’ menu with prompt ‘User
Defined Attributes’.
Step2: Define UDA Attributes, AGs & Pages:
Access OM UDA Setup Page from Order Management -> Setup -> Orders -> User Defined Attributes.

Select the Entity, For Ex: “Order Header” Or “Order Line” -> Click Go.
Attribute Groups:
In the Admin page, under Attribute Group Tab, Click on “Create” to create a new Attribute Group.

Populate the mandatory fields -> Internal Name, Display Name, Select the Behavior – Single Row/ Multi Row, Rows and
Columns accordingly in the page layout.
Also select any View/Edit Privileges required. In addition users can also select Pre/Post Attribute Change Event that will be
triggered whenever the attribute is updated.
Once the required data is entered, click on “Apply and Add Attributes” to Save the AG definition and to add the Attributes.
Attribute:
In the Create Attribute Page, populate the mandatory fields like Internal Name, Display Name, Sequence, Data Type, Column
and Display as.
“Data Type” specifies the type of the data that can be entered. The available choices are Char, Number, Standard Date, Standard
Date Time and Translatable Text. “Column” specifies the column name of the extension table, where the value of this attribute is
actually stored. “Display as” specify the type of display, available choices are Text Field, Text Area, Static or Dynamic URL,
Check Box, Radio Button or Hidden.

In the Create Attribute Page, optionally populate the fields “Required” (to specify it as a mandatory attribute), “Indexed” ( to
create Indexed column) , “Value Set” (to control the data entry through LOV) and “Default Value” ( to populate Default value)
Finally the Attribute Group Details page looks as below with all the associated attributes.

Association:
Once the Attribute Groups are created, click on “Association” > “Add Attribute Groups” > select the row corresponding to the
“Attribute Group” > click on “Apply” to associate the AG to classification. For OM UDAs, the “Attribute Group” gets
automatically assigned to the “ADMIN_DEFINED” classification. For other entities like Items, user has to choose classification
by selecting appropriate Item Catalog Category.
Page:
The final step is to create a new “Page” and associate the “Attribute Group” to the “Page”.

To do this, select the “Page” sub-tab and create a new “Page”. Select the newly created “Attribute Group” and associate to the
“Page”. Users can associate more than one AG to the Page.

Note: An attribute group can have a maximum of 40 character fields, 20 Number fields and 10 Date fields. However users can
associate as many attribute groups as you want to a UDA page which makes the number of additional attributes to an Order
Header/Line unlimited.
Step3: Add concurrent program (Optional)
A new concurrent program ‘Import User Defined Attributes’ created to import attributes to Order Management. Add this program
to Request group of the Responsibility.
V. Process Flow
Data can be entered/updated in the UDAs using below methods -
Method 1: Front-end forms

For Ex. OM UDA Pages can be invoked by navigating to Order Management -> Sales Orders -> Query for the Order/Line ->
Actions -> User Defined Attributes. This will launch a new UDA page from Header or Line level. In this page user can
enter/update attribute values for various attribute groups and the pages. This will be OA Framework (OA) page. Defaulting will
be similar to defaulting provided by UDA framework.
Method 2: Concurrent program (Import User Defined Attributes)
The attributes values can be imported from interface tables by launching corresponding Concurrent Programs.
In our example of Sales Order UDAs a new concurrent program ‘Import User Defined Attributes’ is created to import attributes
to Order Management. Data Set id populated on interface table (OE_HEADERS_EXT_IFACE_ALL or
OE_LINES_EXT_IFACE_ALL ) is provided as input to process the set of data.
First, the Sales Order Header and Lines are to be imported using Order Import/ High Volume Order Processing (HVOP)
concurrent programs. After the import is successful, run the ‘Import User Defined Attributes’ concurrent program to import the
User Defined Attributes from the Extension tables. Attribute values can be linked to orders by using common values such as
orig_sys_document_ref, orig_sys_line_ref, etc.
Alternatively, if the orders have already been created/imported, attribute values can be linked to orders by using the Header_id
and/or Line_id.
For sample scripts, Refer MOS Doc ID 2335187.1 How To Load User Defined Attributes For Sales Order Header and Sales
Order Line Using OE_HEADERS_EXT_IFACE_ALL And OE_LINES_EXT_IFACE_ALL Interface Tables?

Method 3: Public API (EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data)

UDAs data can also be created/updated using public API. For details on the usage of API and for the Sample scripts, refer MOS
Doc ID 2109089.1 - 12.2: How to Create or Update User Defined Attributes for Sales Orders at Header or Line Level using
EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data?

VI. Data flow

There is separate UDA table defined per entity, for the example of Sales Order UDAs following are the data model changes
New Tables Added:
OE_ORDER_HEADERS_ALL_EXT_B - Stores UDA value for order header,
OE_ORDER_LINES_ALL_EXT_B - Stores UDA value for order line,
OE_HEADERS_EXT_IFACE_ALL - Interface table to import order header UDA values,
OE_LINES_EXT_IFACE_ALL - Interface table to import order line UDA values,
OE_ORDER_HEADERS_ALL_EXT_TL - Stores data related to installed languages for header attributes, and
OE_ORDER_LINES_ALL_EXT_TL - Stores data related to installed languages for line attributes.
Key Columns in IFACE tables:
TRANSACTION_ID - Unique id for each record. (We can use sequence EGO_IUA_TRANSACTION_ID_S for this column)
DATA_SET_ID - An identifier for the collection of rows to be processed together. Usually it’s a unique value per concurrent
request. (We can use sequence EGO_IUA_DATA_SET_ID_S)
ROW_IDENTIFIER - An identifier for the collection of rows that make up one logical Attribute Group Row. Usually it’s a
unique value per Attribute Group.
ATTR_GROUP_TYPE - OE_HEADER_ATTRIBUTES_EXT /OE_LINE_ATTRIBUTES_EXT.
ATTR_GROUP_INT_NAME - Attribute Group Internal Name.
ATTR_GROUP_ID - Fetch from ego_fnd_dsc_flx_ctx_ext. (SELECT * FROM ego_fnd_dsc_flx_ctx_ext WHERE
DESCRIPTIVE_FLEXFIELD_NAME IN ('OE_HEADER_ATTRIBUTES_EXT','OE_LINE_ATTRIBUTES_EXT');
ATTR_INT_NAME - Attribute Internal Name,
ATTR_VALUE_STR - The Attribute value, if its data type is Char,
ATTR_VALUE_NUM - The Attribute value, if its data type is Number,
ATTR_VALUE_DATE - The Attribute value, if its data type is Date,
DATA_LEVEL_ID - Fetch from ego_data_level_b ( SELECT * FROM ego_data_level_b WHERE APPLICATION_ID=660;),
DATA_LEVEL_NAME - ORDER_HEADER/ORDER_LINE,
PROCESS_STATUS - 1 /2/ 3/ 4 (1 – To Be Process, 2 – In Process, 3 – Rejected Record, and 4 – Success),
ENTITY - Classification Code ‘ADMIN_DEFINED’,
TRANSACTION_TYPE - CREATE/UPDATE/ SYNC* and DELETE in case of deleting multi row UDA values,
* - SYNC is used to CREATE a record (if record not already exist) or UPDATE an existing record.
ORDER_SOURCE_ID - Order Source Id of the Order,
ORIG_SYS_DOCUMENT_REF - Orig Sys Document Ref of the Order,
ORIG_SYS_LINE_REF - Orig Sys Line Ref of the Order Line,
ORIG_SYS_SHIPMENT_REF - Orig Sys Shipment Ref of the Order Line,
SOLD_TO_ORG_ID - Sold To Org Id of the Order,
HEADER_ID - Header Id of the Order,
LINE_ID - Line Id of the Order Line,
ORG_ID - Org Id of the Order.

VI. Solution Constraints and Limitations

Below mentioned features are currently not supported.

 Order Organizer Search for UDA

 Mass Change of UDA attributes
 Cascading of UDA attributes from parent to child
 Required attribute validation during Order booking
 Link to access UDA attributes from Order Information Portal (OIP) page
VI. Common Setup Issues
Issue#1:
When accessing ‘User Defined Attributes’ from Setup  Orders  User Defined Attributes, getting error as:
The Order Management Extension Setup function is not available under the Order Management Super User responsibility
Probable Cause: Menu/Function is missing.
Resolution: Check the function ‘Order Management Extension Setup’ is added under ‘ONT_SETUP_ORDERS’ menu or not.
Recompile menu ‘ONT_SETUP_ORDERS’.

Issue#2:
When accessing UDAs from Setup  Orders  User Defined Attributes, Association and Page tabs are missing.
Probable Cause: Menu/Function is missing.
Resolution: Check the menu ‘Order Management Extension HTML tab’ is added under ‘Order Management Extension Home
Page’ menu or not.
Recompile both the menus ‘Order Management Extension HTML tab’, ‘Order Management Extension Home Page’.

Issue#3:
1. Inserted 3 records in IFACE table. These 3 records are having different Attribute Groups.
2. Submit Request ‘Import User Defined Attributes’.
3. Verify IFACE data. None of the records are imported.
Probable Cause: One of the rows in IFACE table is having wrong data and the remaining 2 rows are having valid data. But none
of the records are imported. This is because all rows are having same Row Identifier.
Resolution: Each attribute group should have a unique ROW_IDENTIDIER.
Use different Data Set Id for each request.

Conclusions
It is our desire that this paper has helped you to understand User Defined Attributes and how to enable the same. We hope that
you are now better equipped to create necessary setups, understanding of key elements, populating UDAs and associated data
flow. In addition, if you encounter a problem that requires assistance, please remember to call Oracle Support. They may have
seen the issue before and may already have a solution for the issue!

About the Authors

Kishor Genikala is a Senior Principal Technical Support Engineer, EBS Mfg. Support at Oracle India Pvt. Ltd. Kishor is a
consistent speaker in AIOUG since 2014. He has 16 years of Techno functional Experience in EBS applications and 3 years of
Domain Experience in Manufacturing Industry.
Sabarinadh Idupuganti is a Principal Software Engineer; EBS Mfg. Support at Oracle India Pvt. Ltd. He has 12 years of
Techno functional Experience in EBS applications.