Enterprise Resource Planning (ERP) systems are massive, complex ecosystems. Within a single corporate environment, modules responsible for general ledger accounting, human resources, supply chain logistics, and order management must interact seamlessly. In Oracle E-Business Suite (EBS), these modules do not operate in isolation. They are built on top of a highly sophisticated, unified layer of foundational technology known as the Application Object Library (AOL).
Often referenced in database schemas by its internal product code prefix, FND (Foundation), the Application Object Library is the core engine of Oracle Applications. It provides a standard set of runtime services, database definitions, user interface templates, and programmatic APIs that ensure structural consistency across the entire ERP landscape.
Without AOL, every individual module inside Oracle EBS would require its own custom-built security systems, background processing queues, user administration dashboards, and system-level configuration parameters. By centralizing these core requirements, the Application Object Library acts as the ultimate architectural bridge, slashing development times, standardizing system administration, and securing the system against algorithmic and structural instability.
In this technical guide, we will unpack the foundational architecture of Oracle AOL, map its core database structures, and explore the seven essential secrets that every developer, system administrator, and database administrator (DBA) must master to build stable, scalable extensions for Oracle E-Business Suite.
Oracle AOL: Core Architectural Directory
2. Application Object Library: What is Oracle AOL?
Oracle E-Business Suite is composed of dozens of specialized modular applications (such as General Ledger, Oracle Payables, and Human Resources). Yet, regardless of which module you open, you will find an identical user experience: the menus look identical, the reporting windows follow the same submission parameters, the user accounts share a single password catalog, and data fields behave in a uniform manner.
This structural uniformity is the primary design output of the Application Object Library. AOL is both a developer framework and a system administrator’s control room. It provides the database objects, software modules, and predefined API libraries that handle the common administrative functions required by any enterprise application.
Central to AOL is the FND schema. The FND database user owns the core systemic tables that define the E-Business Suite structure. If you review the data dictionaries of an Oracle database hosting EBS, you will find that nearly every foundational system table begins with the prefix FND_. These tables store system configuration metadata, user profiles, report execution definitions, and database structure templates.
AOL also serves as the communication layer between the underlying Oracle database and the front-end user interfaces, whether they are legacy Oracle Forms desktop screens or modern, web-based Oracle Application Framework (OAF) pages. By using AOL/J (the Java services layer within AOL), developers can extend standard applications, write custom pages, and securely validate data in direct compliance with EBS standards.
3. The 5 Core Pillars of Application Object Library Architecture
To successfully build, customize, or maintain any application within the E-Business Suite, you must understand the five primary pillars that define AOL’s architecture.
Pillar 1: Security and Access Control (Users, Roles, & Responsibilities)
Security in Oracle EBS is deeply hierarchical, and it is controlled entirely via AOL. This architecture manages access using three primary layers:
- Users: Individual accounts stored in the
FND_USERtable. Every user has a unique login ID and password. - Responsibilities: The security context of a user session. A Responsibility dictates which menus, forms, reports, and data pools a user can access.
- Menus and Functions: A Responsibility points to a single main navigational menu. This menu is a hierarchical collection of submenus and individual “Functions.” A Function is the actual executable screen, report, or background process.
This system ensures that Function Security is managed separately from the database tables themselves. For example, a user assigned to the “Payables Manager” responsibility can access screens to enter invoices, while a user assigned to the “Payables Inquiry” responsibility uses a menu that has the invoice entry function systematically excluded.
Pillar 2: Concurrent Processing & Standard Report Submission (SRS)
In an enterprise environment, executing data-heavy tasks (like generating payroll or posting millions of journal entries to the ledger) directly on a user’s screen would lock the user interface and crash browser sessions.
AOL solves this through Concurrent Processing. This architecture manages background tasks via the Concurrent Manager, which runs background queues on the database server.
When a user submits a report or long-running program, they use the Standard Report Submission (SRS) interface. The system converts the submission into a “Concurrent Request.” The Concurrent Manager picks up the request from a queue, processes it using database procedures or host scripts, writes the output to a text or PDF file, and registers the status in the database. This allows the user to immediately close the submission window and continue working in other applications while the report runs in the background.
Pillar 3: Flexfields (Key Flexfields vs. Descriptive Flexfields)
Oracle applications must be highly customizable to fit the needs of diverse businesses. Flexfields are database design elements engineered by AOL that allow companies to extend standard databases and screens without writing code or altering the physical database schema.
- Key Flexfields (KFF): A Key Flexfield is a database column that represents a multi-segment identifier. The classic example is the Accounting Flexfield in General Ledger, which is composed of multiple segments separated by delimiters (e.g.,
Company-Department-Account-Project->01-120-6100-000). Each segment represents a specific business entity. AOL manages the validation and processing of these segments as a single unit. - Descriptive Flexfields (DFF): A Descriptive Flexfield represents customizable “placeholder” columns (typically named
ATTRIBUTE1throughATTRIBUTE30) reserved in standard tables. AOL allows administrators to activate these columns via a graphical user interface, defining what data (like custom text, dates, or numbers) they should capture on standard entry screens.
Pillar 4: Profile Options (Multi-Level Hierarchy)
Profile options are system-level variables that control the behavior, look, and defaults of E-Business Suite applications. AOL evaluates these variables using a strict hierarchy, known as the User Profile Hierarchy.
When a database query calls a profile option, the system checks its values from the bottom up, applying the most specific value found:
For example, if the profile option Printer is set to “Central Office” at the Site level, but a specific user has overridden it to “Accounting Room” at the User level, AOL will route their print jobs to the “Accounting Room.”
Pillar 5: Lookups and Value Sets
A OL ensures data integrity by preventing users from entering free-form, unvalidated text into critical fields.
- Lookups: Static, validated lists of values (like “Yes/No”, “Country Codes”, or “Order Statuses”). These are stored in the
FND_LOOKUP_VALUEStable and can be customized by administrators to add new valid items. - Value Sets: Programmatic validation rules used by Flexfields and Concurrent Program parameters. A Value Set can restrict user input to a specific format (e.g., uppercase letters only), a defined range of numbers, or dynamically pull validation values from an active SQL database query.
4. Mastering Access Control in Application Object Library
Managing who can access what inside an enterprise system is critical to audit compliance (such as Sarbanes-Oxley or GDPR). Within AOL, access control is split into two distinct mechanisms: Function Security and Data Security.
Function Security
Function Security restricts access to the actual user interface screen (the “Page”). If a user does not have a Responsibility that includes the function AP_ENTER_INVOICES, they can never open the screen. AOL handles this validation the moment the navigation menu is clicked.
Data Security
Data Security goes a layer deeper. It assumes the user has access to the function (the screen), but restricts which rows of data they can interact with. In Oracle EBS, this is often handled via Multi-Org Access Control (MOAC).
Using MOAC, when a user opens the Invoice Entry form, AOL reads the system profiles to determine which Operating Units (OUs) the user is assigned to. The database then dynamically appends a WHERE clause to the underlying SQL query (e.g., WHERE org_id IN (204, 205)), preventing the user from viewing or editing records belonging to corporate entities they are not cleared to manage.
5. AOL Developer Cheat Sheet: Crucial FND Database Tables
For technical developers, understanding the relational structure of AOL is essential for writing efficient SQL queries and troubleshooting data corruption. Below is the master developer cheat sheet of the most critical system tables within the FND schema.
| Table Name | Functional Description / Schema Role | Primary Joining Key |
|---|---|---|
FND_USER | Stores user logins, encrypted password hashes, account start/end dates, and email addresses. | USER_ID |
FND_RESPONSIBILITY | Stores definitions of E-Business Suite Responsibilities, pointing to specific menu and data group IDs. | RESPONSIBILITY_ID |
FND_MENUS | Defines the structure of the hierarchical navigation menus. | MENU_ID |
FND_FORM_FUNCTIONS | Stores individual functions, web pages, and executables registered in the system. | FUNCTION_ID |
FND_CONCURRENT_REQUESTS | Contains active records of all submitted concurrent requests, their statuses, parameters, and output paths. | REQUEST_ID |
FND_CONCURRENT_PROGRAMS | Defines the background concurrent programs, parameters, and executable associations. | CONCURRENT_PROGRAM_ID |
FND_PROFILE_OPTIONS | Holds definitions of all system profile options. | PROFILE_OPTION_ID |
FND_PROFILE_OPTION_VALUES | Stores the actual values assigned to profile options across Site, Application, Responsibility, and User levels. | PROFILE_OPTION_VALUE |
FND_LOOKUP_TYPES | Defines groupings of static validation lists (e.g., ORDER_STATUS_LIST). | LOOKUP_TYPE |
FND_LOOKUP_VALUES | Stores the actual items within lookup lists, including translations, descriptions, and active dates. | LOOKUP_CODE |
6. Customization Standards in the Application Object Library
One of the primary challenges for Oracle developers is extending E-Business Suite with custom components (often called RICEFW objects: Reports, Interfaces, Conversions, Extensions, Forms, and Workflows).
When custom code needs to interact with the Application Object Library, developers must adhere to strict, validated development standards to ensure upgradability and maintain system integrity.
⚠️ The Golden Rule: Never Update FND Tables Directly
It can be tempting for a developer or DBA to fix a system issue by writing a quick SQL statement:
sql-- DANGER: NEVER DO THISUPDATE FND_USER SET EMAIL_ADDRESS = 'custom@corp.com' WHERE USER_ID = 1001;
Executing direct UPDATE, INSERT, or DELETE statements on standard FND_ tables is highly dangerous.
These tables possess complex, non-obvious trigger systems, metadata relationships, and cache configurations. Making a direct update bypasses database-level validations and can corrupt systemic metadata. If you corrupt AOL’s core configuration files, you can lock all users out of the ERP system, void your Oracle Support agreement, and cause critical system failures during your next E-Business Suite patch cycle.
The Safe Path: Utilizing AOL PL/SQL APIs
Oracle provides a massive suite of prebuilt PL/SQL packages designed to safely manipulate AOL configuration data. These APIs handle all validation, logging, and dependency checks automatically.
For example, if you need to programmatically update a user’s email address or assign a responsibility, utilize the standard FND_USER_PKG package:
sql-- SAFE: ALWAYS UTILIZE STANDARD AOL PL/SQL APIsDECLARE v_user_name VARCHAR2(100) := 'JSITH'; v_email VARCHAR2(240) := 'john.smith@corp.com';BEGIN -- Initialize AOL Session Context first FND_GLOBAL.apps_initialize( user_id => 1001, resp_id => 20420, resp_appl_id => 101 ); -- Update User Programmatically via API FND_USER_PKG.UpdateUser( x_user_name => v_user_name, x_owner => 'SEED', x_email_address => v_email ); COMMIT; DBMS_OUTPUT.put_line('User updated successfully via AOL API.');EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.put_line('Error updating user: ' || SQLERRM);END;/
⚠️ Warning Checklist for AOL Customizations
- Register Custom Schemas (Custom Prefixes): Ensure all custom database tables, packages, and views are created in a custom schema (typically named
XX_or similar) rather than the standardAPPSorFNDschemas to avoid namespace collisions during system patches. - Use the standard
FND_GLOBALpackage: When writing custom database triggers or packages, never hardcode values. Use functions likeFND_GLOBAL.user_id,FND_GLOBAL.resp_id, andFND_GLOBAL.org_idto read the active user session context dynamically. - Avoid excessive database triggers on FND tables: Never create custom database triggers on standard
FND_tables. Doing so can cause critical performance bottlenecks during high-volume processes like batch concurrent runs. - Clean Up Concurrent Request Histories: Set up standard AOL purge programs (like “Purge Concurrent Request and/or Manager Data”) to run weekly. This prevents performance degradation as the
FND_CONCURRENT_REQUESTStable accumulates millions of history records.
7. Frequently Asked Questions
What does the FND prefix stand for in Oracle E-Business Suite tables?
The prefix FND stands for Foundation. It is the internal application short name used by Oracle to designate the database objects, packages, and structures belonging directly to the Application Object Library.
What is the difference between a Key Flexfield (KFF) and a Descriptive Flexfield (DFF)?
A Key Flexfield (KFF) is a mandatory, core database structure used to model complex, multi-segment business entities (like accounts, stock items, or asset categories). A Descriptive Flexfield (DFF) is an optional metadata capture tool composed of placeholder attribute columns that allows administrators to add custom data entry fields to standard forms without modifying the underlying database structure.
How does AOL resolve conflicting Profile Options?
AOL resolves conflicts by checking profile option values at four levels, starting from the most specific to the most general: User -> Responsibility -> Application -> Site. The most specific level always overrides the others. For example, a setting specified at the User level will always override a default setting specified at the Site level.
Why is the Concurrent Manager critical to AOL?
The Concurrent Manager is the background processing engine of E-Business Suite. It manages system-wide processing queues, allowing long-running, data-intensive tasks (like report generation or accounting posts) to run asynchronously on the server without locking the active user screen or degrading user interface performance.
Can we build custom applications using the AOL architecture?
Yes. Developers can register custom executables, menus, and forms inside Oracle’s Application Object Library. By registering custom applications within the FND_APPLICATION directory, custom modules can leverage standard AOL security, user authentication, profile options, and reporting queues, ensuring the new components blend seamlessly with standard EBS features.
8. Conclusion: Scaling Enterprise Stability with Application Object Library
Oracle’s Application Object Library is the cornerstone of enterprise stability, security, and performance across the E-Business Suite ecosystem. By centralizing common application services—from multi-level access control to asynchronous concurrent processing, dynamic data modeling via flexfields, and data validation layers—AOL ensures that the entire ERP platform functions with perfect consistency.
For developers and DBAs, managing AOL requires a deep respect for Oracle’s structural standards. By avoiding direct database updates, registering custom assets safely in custom schemas, and relying on prebuilt PL/SQL packages like FND_USER_PKG and FND_GLOBAL, you protect your ERP system against manual data corruption and ensure painless upgrades.
Mastering the Application Object Library is not just about writing better code; it is about building stable, scalable enterprise foundations that support critical, high-volume business operations year after year.
Leave a Reply