AOL objects

Lookups

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)

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)

Steps:

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)

Steps:

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

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

Steps:

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.

Steps

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)

Steps:

Messages

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.

Definition

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)

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>