AOL objects


Lookups are a set of code and meanings. Sometimes, it can also be used to store extra set of data using contexts. The purpose of having lookup is to have fast recovery of data for validation.  For an example, if we were to store the termination codes available in a firm, we will have a structure similar to this.

01     Normal Termination

02     Retirement

03     Retrenchment

04     Personal

Here, the 01, 02 are the codes and the reasons are stored as the meaning. So, if we wish to just key the in code, and we want the system to understand the meaning, then we must store the codes and meanings somewhere. Oracle gives us expedient tables to store codes and meanings which are easy to access. It uses the concepts of Lookups to store the quick reference data in a tabular format, which are easy to store retrieve and amply extensible. Let’s see how to configure one. See Figure 3.8 – Lookup Types.

  • Responsibility: Application Developer
  • Navigation: Flex field -> Key -> Cross Validation

(Figure 3.8 – Lookup Types)



The name of the lookup type


The meaning. This can be used as a reference to the lookup type, that explains the purpose of the lookup


The application owner of the lookup


Any description, free text

Access Level

The Access Level is automatically set by the system.


User: It is created by user and it is updatable.

Extensible: Although it is seeded, a user can add records here; however cannot delete a seeded record


The code of the lookup value must be unique for each row.


The meaning of the Lookup value


Any description, it can explain the meaning in details.


These are mostly used in seeded lookups. The system puts the localization information here. If it is ‘+FR’ that means it is applicable for France, if it is ‘-RU’ means the row does not apply for Russia. These are mere data fields, the actual logic of including or excluding the Localization is actually in the code that uses the lookup.

From / To

The Start and End dates  are put here


We can switch the row to be available or unavailable with this flag.


Eventually when we keep on discussing more about the Application, we will realize the huge dependency we have over Lookups. This is one of the most frequently visited screens, during implementations.

Let’s say we want to add some more data into a lookup; something more than mere codes and meanings. We can do that by adding a context to it. It is a DFF with title as ‘Common Lookups’. See Figure 3.9 – Lookup Contexts.

(Figure 3.9 – Lookup Contexts)

We will just have to add a new context and add on segments. It’s as good as a normal DFF. Once the DFF segments are defined, we can use this as a context related flex field where the data can be stored using the segments, based on the chosen context in the lookup screen.

User Defined Tables

Apart from the seeded tables, Oracle E-Biz gives another set of tables that a user can design, create and use in order to satisfy the business needs. Those are called the User defines tables, in short, UDTs. Let’s see how to define a table. See Figure 3.10 – UDT Structure.

  • Responsibility: Super HRMS Manager
  • Navigation: Other Definition -> Table Structure

(Figure 3.10 – UDT Structure)


  • UDTs are date track enabled, so pick a date before creating a table
  • Enter the name of the table in the Name field
  • The User table key gets defaulted with the table name, however can be changed. This is the column that uniquely identifies a table.
  • The Match type defines a certain type of validation. If the type is selected as match, system expects the table rows to store data of one particular type. And the Unit of measure of the types is fetched from the type specified in the key unit of measure field. UOMs can be Date, text or number.
  • The match type is selected as range, when there is a range to be defined, like a lower bound or upper bound. Similarly the UOM is selected from the UOM field; however it is always a number.
  •  Now, it is time to define the columns. Click on the Columns button to do so. Define the different columns that would be needed in our table. Use a formula to validate the data entered in the formula.
  • Then the rows. As our table is just in design phase, this is the place where we just define the row labels; the actual row is entered later. Put the row along with the sequence, row key and the exact row name. In case of range types, use the lower and upper boundaries.
  • This completes the definition of the table. Let’s now put some rows in it. See Figure 3.11 – UDT Values.

  • Responsibility: Super HRMS Manager
  • Navigation: Other Definition -> Table Values

(Figure 3.11 – UDT Values)


  • Query the table name.
  • Now the screen shows us the rows with only the first column.
  • To navigate through columns, click on the Column Name, and use up and down buttons.
  • In the Exact field, key in the exact row number and the column value of the row.
  • Similarly fill in the other columns, using the up and down arrows.

Now, to use the UDTs, we can fetch the table columns from ‘PAY_USER_COLUMN_INSTANCES_F’ and ‘PAY_USER_TABLES’. The other important tables are: ‘PAY_USER_ROWS_F’ and ‘PAY_USER_COLUMNS’.

Value Sets

Apart from the seeded tables, Oracle E-Biz gives another set of tables that a user can design, create and use in order to satisfy the business needs. Those are called the User defines tables, in short, UDTs. Let’s see how to define a table. See Figure 3.10 – UDT Structure.

A Value set, as the name suggests, holds a set of values. The values are then used in different places in the application for validation. For an example, if we have three different Audit Departments, A1, A2 and A3; and we want the Employees to enter an Audit Department number in which they wish to be audited. We wouldn’t want them to enter something other than these three departments. We wouldn’t allow them to save the record if they entered A4; because A4 is not a valid value. So what is the solution?

To manage situations like this, we could define a value set that holds these three values, and attach that value set to the field. So whenever an employee starts filling in details, s/he has to fill in with a value that is valid as per the value set. Let’s see how to define one. See Figure 3.10 – Value Sets.

(Figure 3.12 – Value Sets)

  • Responsibility: Application Developer
  • Navigation: Application -> Validation -> Set


Value Set Name

Name of the Value set.


This one shows all the places where the value set is being used, either a Concurrent Program or a KFF or a DFF.


Any description, free text.

List Type

Choose a list type:

LOV: For any List of values

Long LOV: For a List of Value, that is expected to pull a long list. When we use this, the LOV initially appears with No Values, unless a search String is entered for to do a pattern match.

Pop List: A Pop list is used when the list of values is expected to be very small, usually less than 10 values.

Security Type

This is to justify the security rules attached to the value sets, if any. Choose any one of the three:

No Security: No Security rules apply.

Hierarchical: This ensures we have access to the entire hierarchy of data being entered.

Non-Hierarchical:  This option does not check for the entire hierarchy. If we have access to the data being entered, we can.


For example: If we do not have access / privilege for the Head Cost centre we will not be allowed to enter the Sub divisional Cost Centre name in case of Hierarchical model. In case of Non hierarchical model, it just checks the access for the cost centre being entered.

Format Type

The Format in which the values stored in this Value set are going to be.

Max size

The Maximum size. For some standard Format types, it is fixed.


Only available with Numbers, sets the precision point.

Numbers Only

Ensures only numbers are entered as values.

Uppercase Only

Ensures only Uppercase Values are entered.

Right Justify

It makes the Value right justified with zero filled to the rest of characters.

Min Value

The Minimum/ Low Value.

Max Value

The Maximum / High value.

Validation Type

Explained later in this chapter.


This opens a screen based on the validation type, with which the data will be validated.

Validation Types

These define the way the values in the value sets are stored or matched.

  • None: No validation, other than the ones in Format checks attached in the parent screen.
  • Independent: with this type, the Values are stored in an AOL Table, along with a meaning. For example, we might have a code as 02 and the meaning as “Boston Division”. We will learn how to set values in an independent Value set.
  • Table: It is similar to the Independent Value set, however the data is stored in an application table.
  • Dependent: With this type, although the data is stored in an AOL Table, the values are filtered based on another Independent Value set, which is linked to the dependent Value set. So based upon the value we enter in an Independent Value set, values for the Dependent Value sets are populated
  • Special: These are like a Flex Field in a value set. So these are like a combination of values, mostly from a Key Flex Field, that appear in the value set.
  • Pair: This allows us to use a range of Concatenated Flex Field segments in a value set.
  • Translatable: The Translatable value sets are similar to the Dependent and Independent types. However these enable us to use hidden codes, so that user sees only the meaning and not the codes.

Table Types

In case of a Table type of value set, as we wish to see the values from a Table, we must define the columns and the table from which the data will be pulled. See Figure 3.11 – Table Type Validation.


  • Click on the Edit Information.
  • Enter the name of the application to which the table belongs to.
  • Enter the table name.
  • Enter the column in which the Value is being stored in the Value field along with the data type and size.
  • Enter the meaning if any.
  • Enter the column that stores the Id, if we want to use the Ids as well; along with the data type and size.
  • If the value set is used in Concurrent requests, the Value is taken as the front Parameter; however the Id is the one that is fed to the process.

(Figure 3.13 – Table Type Validation)

  • In parallel, write a query in our database, and park this screen for a minute. Take the value, meaning and Id in the “select clause” and Table name in the “from clause” and in where clause, put the conditions that must match to get the values.
  • Once the query is complete, put the where clause and the Order by clause if any, in the where section.
  • If we are using any extra columns, for matching the values or to be used in the Order by Clause; other than the Value, Meaning and ID, then put those in the additional columns field.

Segment Values

We discussed that the dependent and independent value set fetch the valid values from an AOL Table. Let’s see how and where we store the values. See Figure 3.14 – Segment Values.

  • Responsibility: Application Developer
  • Navigation: Application -> Validation -> Values

(Figure 3.14 – Segment Values)



Name of the Value Set.

Dependent value Set

If it’s a dependent Value set, mention the Value set on which this value set depends on.


The Value.

Translated Value

The Meaning of the Value.


The Description.

From and To Date

The effective dates.

Hierarchy and Qualifiers

This is where we can add extra security based on Qualifiers.


In an application, there will be errors and warnings at the runtime; and the application will be giving messages based on the errors / warnings. However at times, we might wish to change the error / warning message as per the business needs. And sometimes, we would even need to set up our own personalized messages on the application forms / self service. For this, AOL has functionality in place, called FND Messages. These are pure text messages along with tags, which could be used to fill in specifics related to the messages at the run time. And once the message is defined, it can be attached to different functions or routines from which they will be called and displayed.


Let’s see how to define one message, and then we will look further in to the usages and the Tags that are used in the messages. See Figure 3.15 – Defining Messages.

  • Responsibility: Application Developer
  • Navigation: Application -> Messages

(Figure 3.15 –  Defining Messages)



Name of the message can be entered here. It is advised to have a meaningful name along with the application it is being used for and the error number.


Pick a language in which the message will be displayed.


The application in which the message will be used.


The Error Number, this is the number with which the calling routine identifies the message uniquely.


The Type of the Message, whether an Error or hint or a Note or may be the title of the message. It can also be a % of Expansion, which means the system will open up a window of size 30% of the window, and shows the message in there as a Prompt, if it is a 305 Expansion prompt. Likewise for 50 and 100% it works the same way.

Maximum Length

The Maximum Length of the message.


The Description of the message a free text.

Alert Category

The type of alert, like it is an alert on the system, Product or is it about security. All these are usually used by the seeded system messages. Blank or User is the type we want to use in a custom message.

Alert Severity

The type of the severity of the alert is it an Error/ warning or is it critical to the system. For custom messages leave blank.

Log Severity

If the message is being logged for system administrator, then what type of log would this be? Usually this raises another set of routines, to inform the system administrator about the issue via emails / texts.


This is where we add the text of the messages.


Message Text

This is the place that stores the text of the message that gets displayed. There are a lot of possible ways to decorate it and make it more meaningful.

Parameters: We can receive parameters from the calling routine, and use them as part of the message text. We can set variables for the same. They can be used with an Escape Character ‘&’ or ‘:’. For an example:

The exemption limit is not set up for the legal employer &ORG_NAME for the current period.

This is a message text, and &ORG_NAME is the variable that can be passed to the Message by the calling routine. So if the routine passes ORG_NAME = ‘ABC Corp.’ then the message will look like this:

“The exemption limit is not set up for the legal employer ABC Corp. for the current period.”

We can also use system parameters in the messages like: <product-code>

Formatting: In formatting, we can use the HTML codes to format our messages. It can start with a <html >tag and can have the required tags in the body. Here is a sample.

<html><b>You cannot complete this task because one of the following events caused a loss of page data:<br> <ul><li>Your login session has expired. <li>A system failure has occurred.</ul></b> To proceed, please select the global Home link to return to the main menu.  Then, access this page again using the navigation controls (menu, links, and so on) within the application. </html>