OracleFree referencePDF, Excel, Word exportOracle E-Business SuiteAll Oracle E-Business Suite tools

Oracle EBS Application Object Library (AOL) FND Tables Reference

A searchable reference for the Application Object Library (AOL) FND_ tables in Oracle EBS 12.1/12.2: applications, users, responsibilities, menus and functions, concurrent processing, messages and profiles. Each row lists key columns and the join that connects it to the rest of AOL.

Download:

Table/View	Area	Holds	Key columns	Joins	Notes
FND_APPLICATION	Applications	Registered applications (products), including custom ones	APPLICATION_ID; APPLICATION_SHORT_NAME (unique)	APPLICATION_ID is carried by most FND tables	APPLICATION_SHORT_NAME such as ONT, PO, SQLGL, AR, XXCUST; BASEPATH is the top directory variable, for example ONT_TOP
FND_APPLICATION_TL	Applications	Translated application names	APPLICATION_ID, LANGUAGE	APPLICATION_ID = FND_APPLICATION.APPLICATION_ID	APPLICATION_NAME, for example Order Management
FND_APPLICATION_VL	Applications	View joining FND_APPLICATION with its _TL row for the session language	APPLICATION_ID	As FND_APPLICATION	Use in reports to get short name and name together
FND_PRODUCT_INSTALLATIONS	Install and patch	Install status and patch level per product	APPLICATION_ID	APPLICATION_ID = FND_APPLICATION.APPLICATION_ID; ORACLE_ID = FND_ORACLE_USERID	STATUS I (installed), S (shared), N (not installed); PATCH_LEVEL such as R12.ONT.B.delta.n
FND_PRODUCT_GROUPS	Install and patch	Instance-level release and flags	PRODUCT_GROUP_ID	Single row in a normal instance	RELEASE_NAME gives the EBS release, for example 12.2.10; APPLICATIONS_SYSTEM_NAME; MULTI_ORG_FLAG
FND_USER	Users and security	Application user accounts	USER_ID; USER_NAME (unique)	EMPLOYEE_ID = PER_ALL_PEOPLE_F.PERSON_ID; PERSON_PARTY_ID = HZ_PARTIES.PARTY_ID; USER_ID = CREATED_BY on every table	START_DATE, END_DATE, LAST_LOGON_DATE, EMAIL_ADDRESS; never update password columns directly, use FND_USER_PKG
FND_RESPONSIBILITY	Users and security	Responsibilities and what they point to	APPLICATION_ID + RESPONSIBILITY_ID; RESPONSIBILITY_KEY	MENU_ID = FND_MENUS.MENU_ID; REQUEST_GROUP_ID + GROUP_APPLICATION_ID = FND_REQUEST_GROUPS; DATA_GROUP_ID	VERSION 4 (Oracle Applications forms), W (self-service web), M (mobile); START_DATE, END_DATE
FND_RESPONSIBILITY_TL	Users and security	Translated responsibility names	APPLICATION_ID, RESPONSIBILITY_ID, LANGUAGE	APPLICATION_ID + RESPONSIBILITY_ID = FND_RESPONSIBILITY	RESPONSIBILITY_NAME is what users pick in the navigator
FND_RESPONSIBILITY_VL	Users and security	View of FND_RESPONSIBILITY plus its name in the session language	APPLICATION_ID + RESPONSIBILITY_ID	As FND_RESPONSIBILITY	Convenient for access reports
FND_USER_RESP_GROUPS_DIRECT	Users and security	Responsibilities assigned directly to users	USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID, SECURITY_GROUP_ID	USER_ID = FND_USER.USER_ID; RESPONSIBILITY_ID + RESPONSIBILITY_APPLICATION_ID = FND_RESPONSIBILITY	START_DATE, END_DATE; maintained by Define User and FND_USER_PKG.ADDRESP
FND_USER_RESP_GROUPS_INDIRECT	Users and security	Responsibilities a user gets through roles (UMX role inheritance)	USER_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID, SECURITY_GROUP_ID	Same as the _DIRECT table	Access reviews must read both _DIRECT and _INDIRECT, or the FND_USER_RESP_GROUPS view that combines them
FND_MENUS	Menus and functions	Menus, including permission sets	MENU_ID; MENU_NAME (unique)	MENU_ID = FND_RESPONSIBILITY.MENU_ID and FND_MENU_ENTRIES.MENU_ID	USER_MENU_NAME in FND_MENUS_TL; TYPE distinguishes standard menus, permission sets and others
FND_MENU_ENTRIES	Menus and functions	Lines of a menu: a submenu, a function, or both	MENU_ID, ENTRY_SEQUENCE	SUB_MENU_ID = FND_MENUS.MENU_ID; FUNCTION_ID = FND_FORM_FUNCTIONS.FUNCTION_ID	GRANT_FLAG = 'N' entries do not grant access; PROMPT in FND_MENU_ENTRIES_TL; walk with CONNECT BY on SUB_MENU_ID
FND_FORM_FUNCTIONS	Menus and functions	Functions: forms with parameters, OA pages, subfunctions	FUNCTION_ID; FUNCTION_NAME (unique)	FORM_ID + APPLICATION_ID = FND_FORM; FUNCTION_ID = FND_MENU_ENTRIES.FUNCTION_ID	TYPE (FORM, SUBFUNCTION, JSP, SSWA and others), PARAMETERS, WEB_HTML_CALL; USER_FUNCTION_NAME in FND_FORM_FUNCTIONS_TL
FND_FORM	Menus and functions	Registered Oracle Forms	APPLICATION_ID + FORM_ID; FORM_NAME	= FND_FORM_FUNCTIONS.APPLICATION_ID + FORM_ID	FORM_NAME is the fmx name, for example OEXOEORD for Sales Orders or POXPOEPO for Purchase Orders
FND_RESP_FUNCTIONS	Menus and functions	Function and menu exclusions on a responsibility	APPLICATION_ID, RESPONSIBILITY_ID, RULE_TYPE, ACTION_ID	RULE_TYPE 'F': ACTION_ID = FND_FORM_FUNCTIONS.FUNCTION_ID; RULE_TYPE 'M': ACTION_ID = FND_MENUS.MENU_ID	An excluded function is removed even if the menu tree grants it; check this before saying a user can run a function
FND_EXECUTABLES	Concurrent processing	Concurrent executables: what file or procedure runs	APPLICATION_ID + EXECUTABLE_ID; EXECUTABLE_NAME	= FND_CONCURRENT_PROGRAMS.EXECUTABLE_APPLICATION_ID + EXECUTABLE_ID	EXECUTION_METHOD_CODE: P Oracle Reports, I PL/SQL stored procedure, Q SQLPlus, H host, L SQLLoader, K Java concurrent program, A spawned; EXECUTION_FILE_NAME is the package.procedure, script or report name
FND_CONCURRENT_PROGRAMS	Concurrent processing	Concurrent program definitions	APPLICATION_ID + CONCURRENT_PROGRAM_ID; CONCURRENT_PROGRAM_NAME (short name)	EXECUTABLE_APPLICATION_ID + EXECUTABLE_ID = FND_EXECUTABLES; = FND_CONCURRENT_REQUESTS.PROGRAM_APPLICATION_ID + CONCURRENT_PROGRAM_ID	ENABLED_FLAG, OUTPUT_FILE_TYPE, RUN_ALONE_FLAG; parameters are a descriptive flexfield named $SRS$.<short name>
FND_CONCURRENT_PROGRAMS_TL	Concurrent processing	User-visible program names	APPLICATION_ID, CONCURRENT_PROGRAM_ID, LANGUAGE	Same key as FND_CONCURRENT_PROGRAMS	USER_CONCURRENT_PROGRAM_NAME is what users submit
FND_CONCURRENT_REQUESTS	Concurrent processing	Every submitted request and its outcome	REQUEST_ID	PROGRAM_APPLICATION_ID + CONCURRENT_PROGRAM_ID = FND_CONCURRENT_PROGRAMS; REQUESTED_BY = FND_USER.USER_ID; CONTROLLING_MANAGER = FND_CONCURRENT_PROCESSES.CONCURRENT_PROCESS_ID; PARENT_REQUEST_ID	PHASE_CODE, STATUS_CODE, ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE, ARGUMENT_TEXT, LOGFILE_NAME, OUTFILE_NAME, COMPLETION_TEXT
FND_CONC_REQ_SUMMARY_V	Concurrent processing	View of requests with program name and requester resolved	REQUEST_ID	Built on FND_CONCURRENT_REQUESTS	Quick ad hoc checks; for heavy reports query the base table
FND_CONCURRENT_QUEUES	Concurrent processing	Concurrent managers (ICM, Standard Manager, custom managers)	APPLICATION_ID + CONCURRENT_QUEUE_ID; CONCURRENT_QUEUE_NAME	= FND_CONCURRENT_PROCESSES.QUEUE_APPLICATION_ID + CONCURRENT_QUEUE_ID	MAX_PROCESSES (target), RUNNING_PROCESSES (actual), TARGET_NODE, CACHE_SIZE, ENABLED_FLAG; names in FND_CONCURRENT_QUEUES_TL
FND_CONCURRENT_PROCESSES	Concurrent processing	Manager processes that have run, with OS and database process IDs	CONCURRENT_PROCESS_ID	QUEUE_APPLICATION_ID + CONCURRENT_QUEUE_ID = FND_CONCURRENT_QUEUES	PROCESS_STATUS_CODE, OS_PROCESS_ID, ORACLE_PROCESS_ID, NODE_NAME, LOGFILE_NAME (manager log)
FND_REQUEST_GROUPS	Concurrent processing	Request groups attached to responsibilities	APPLICATION_ID + REQUEST_GROUP_ID; REQUEST_GROUP_NAME	= FND_RESPONSIBILITY.GROUP_APPLICATION_ID + REQUEST_GROUP_ID	Controls which programs a responsibility can submit through Submit Request
FND_REQUEST_GROUP_UNITS	Concurrent processing	Programs, request sets or whole applications in a request group	APPLICATION_ID, REQUEST_GROUP_ID, REQUEST_UNIT_TYPE, UNIT_APPLICATION_ID, REQUEST_UNIT_ID	REQUEST_UNIT_TYPE 'P': REQUEST_UNIT_ID = CONCURRENT_PROGRAM_ID; 'S': = FND_REQUEST_SETS.REQUEST_SET_ID; 'A': all programs of the application	Answer 'which responsibilities can run program X' by joining this to FND_RESPONSIBILITY
FND_REQUEST_SETS	Concurrent processing	Request set definitions	APPLICATION_ID + REQUEST_SET_ID; REQUEST_SET_NAME	Stages and programs in FND_REQUEST_SET_STAGES and FND_REQUEST_SET_PROGRAMS	USER_REQUEST_SET_NAME in FND_REQUEST_SETS_TL
FND_CONCURRENT_PROGRAM_SERIAL	Concurrent processing	Program incompatibilities	RUNNING_APPLICATION_ID, RUNNING_CONCURRENT_PROGRAM_ID, TO_RUN_APPLICATION_ID, TO_RUN_CONCURRENT_PROGRAM_ID	Both program pairs = FND_CONCURRENT_PROGRAMS	INCOMPATIBILITY_TYPE D (domain) or G (global); TO_RUN_TYPE P (program) or S (request set); incompatibilities make requests wait on the Conflict Resolution Manager
FND_NEW_MESSAGES	Messages	Message dictionary: every APP- and product message text	APPLICATION_ID, MESSAGE_NAME, LANGUAGE_CODE	APPLICATION_ID = FND_APPLICATION	MESSAGE_NUMBER is the number in APP-ONT-123456 style errors; MESSAGE_TEXT has the tokens
FND_LOGINS	Users and security	Login sessions recorded by Sign-On Audit	LOGIN_ID	USER_ID = FND_USER.USER_ID; LOGIN_ID = LAST_UPDATE_LOGIN on transaction tables	START_TIME, END_TIME, LOGIN_TYPE; detail in FND_LOGIN_RESPONSIBILITIES and FND_LOGIN_RESP_FORMS depending on the Sign-On:Audit Level profile
FND_NODES	Install and patch	Application and database nodes registered by AutoConfig	NODE_ID; NODE_NAME	NODE_NAME = FND_CONCURRENT_QUEUES.TARGET_NODE and FND_CONCURRENT_PROCESSES.NODE_NAME	SUPPORT_CP, SUPPORT_FORMS, SUPPORT_WEB, SUPPORT_ADMIN, SUPPORT_DB flags; stale nodes after cloning are cleaned with FND_CONC_CLONE.SETUP_CLEAN
FND_PROFILE_OPTIONS	Users and security	Profile option definitions	APPLICATION_ID + PROFILE_OPTION_ID; PROFILE_OPTION_NAME	= FND_PROFILE_OPTION_VALUES.APPLICATION_ID + PROFILE_OPTION_ID	USER_PROFILE_OPTION_NAME in FND_PROFILE_OPTIONS_TL; SQL_VALIDATION; which levels are updatable
FND_PROFILE_OPTION_VALUES	Users and security	Profile values set at each level	APPLICATION_ID, PROFILE_OPTION_ID, LEVEL_ID, LEVEL_VALUE (+ LEVEL_VALUE_APPLICATION_ID)	LEVEL_VALUE = USER_ID, RESPONSIBILITY_ID, APPLICATION_ID, NODE_ID or ORGANIZATION_ID depending on LEVEL_ID	LEVEL_ID 10001 site, 10002 application, 10003 responsibility, 10004 user, 10005 server, 10006 organization; PROFILE_OPTION_VALUE
AD_BUGS	Install and patch	Patches (bugs) recorded as applied by AD utilities	BUG_ID; BUG_NUMBER	Related AD_APPLIED_PATCHES and AD_PATCH_DRIVERS	Check a patch: WHERE BUG_NUMBER = '12345678'; BUG_NUMBER is VARCHAR2

Scope: Application Object Library objects in EBS 12.1 and 12.2 (APPLSYS schema, used through APPS synonyms). Confirm columns at your patch level in eTRM. Never insert or update these tables directly; use the AOL forms or public APIs.

Access review SQL for your instance

Tell us your EBS release and what auditors ask for. We will send reviewed SQL for user, responsibility and function access built on these AOL tables.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How responsibility, menu and function security chains

A user can open a function when a responsibility assigned to them grants it. The chain is FND_USER to FND_USER_RESP_GROUPS_DIRECT (and _INDIRECT for role-inherited access) on USER_ID, then to FND_RESPONSIBILITY on RESPONSIBILITY_ID plus RESPONSIBILITY_APPLICATION_ID, then MENU_ID to FND_MENUS. The menu is a tree in FND_MENU_ENTRIES: each entry has a SUB_MENU_ID, a FUNCTION_ID or both, so walk it with START WITH menu_id = :m CONNECT BY PRIOR sub_menu_id = menu_id. Entries with GRANT_FLAG = 'N' do not grant. Finally remove exclusions in FND_RESP_FUNCTIONS (RULE_TYPE 'F' for functions, 'M' for menus). Concurrent program access follows a separate chain: FND_RESPONSIBILITY.REQUEST_GROUP_ID to FND_REQUEST_GROUP_UNITS.

Check both _DIRECT and _INDIRECT assignment tables in access reviews
Respect START_DATE and END_DATE on users, assignments and responsibilities
Data access (operating units, ledgers) is controlled by profiles, not by the menu

Where a program's executable lives

Start from the program users see: FND_CONCURRENT_PROGRAMS_TL.USER_CONCURRENT_PROGRAM_NAME gives APPLICATION_ID and CONCURRENT_PROGRAM_ID. FND_CONCURRENT_PROGRAMS gives the short name and EXECUTABLE_APPLICATION_ID plus EXECUTABLE_ID, which lead to FND_EXECUTABLES. There, EXECUTION_METHOD_CODE says what runs and EXECUTION_FILE_NAME names it. For PL/SQL (I) it is package.procedure in the database. For Oracle Reports (P) it is the rdf name under $<APP>_TOP/reports/<language>. For SQL*Plus (Q) a .sql under $<APP>_TOP/sql, for host (H) a script under $<APP>_TOP/bin, for SQL*Loader (L) a .ctl under $<APP>_TOP/bin. The <APP>_TOP is the BASEPATH of the executable's application in FND_APPLICATION. Parameters are the descriptive flexfield $SRS$.<short name>.

The base, _TL and _VL pattern

AOL separates language-independent data from translated text. The base table (FND_RESPONSIBILITY, FND_CONCURRENT_PROGRAMS, FND_MENUS) holds IDs, flags and codes. The _TL table holds one row per installed language with the names users see (RESPONSIBILITY_NAME, USER_CONCURRENT_PROGRAM_NAME, USER_MENU_NAME) and the same key plus LANGUAGE. The _VL view joins the two for the session language using USERENV('LANG'). In ad hoc SQL either use the _VL view or join the _TL table and filter LANGUAGE explicitly; forgetting the filter on a multi-language instance multiplies rows. Product tables follow the same pattern, which is why OE_TRANSACTION_TYPES_TL and WF_ITEM_TYPES_TL exist.

Version and release query

SELECT release_name FROM fnd_product_groups returns the EBS release, for example 12.2.10. For a product's code level use SELECT a.application_short_name, i.status, i.patch_level FROM fnd_product_installations i JOIN fnd_application a ON a.application_id = i.application_id WHERE a.application_short_name IN ('ONT','PO','INV'). STATUS I means installed (licensed and in use), S shared, N not installed. To check a single patch, query AD_BUGS by BUG_NUMBER; in 12.2 the more complete check across nodes is AD_PATCH.IS_PATCH_APPLIED, so confirm with that before reporting a patch as missing. Registered nodes and their roles are in FND_NODES, which AutoConfig maintains.

Frequently Asked Questions

What is the Application Object Library in Oracle EBS?

Application Object Library (AOL, product short name FND) is the foundation layer of EBS. It stores application registration, users, responsibilities, menus and functions, concurrent programs and managers, flexfields, profile options, lookups and the message dictionary. Its tables are owned by APPLSYS and accessed through APPS synonyms, and every product depends on it.

Which table holds the application short name?

FND_APPLICATION.APPLICATION_SHORT_NAME, keyed by APPLICATION_ID, for example SQLGL for General Ledger, AR for Receivables, ONT for Order Management and PO for Purchasing. The full name is in FND_APPLICATION_TL.APPLICATION_NAME, and FND_APPLICATION_VL shows both. BASEPATH gives the top directory variable such as ONT_TOP.

Which table stores concurrent programs in R12?

FND_CONCURRENT_PROGRAMS holds the definition and short name (CONCURRENT_PROGRAM_NAME); FND_CONCURRENT_PROGRAMS_TL holds the user name. The executable is in FND_EXECUTABLES, joined on EXECUTABLE_APPLICATION_ID and EXECUTABLE_ID. Each run is a row in FND_CONCURRENT_REQUESTS, and incompatibilities are in FND_CONCURRENT_PROGRAM_SERIAL.

Which table lists responsibilities assigned to users?

FND_USER_RESP_GROUPS_DIRECT for direct assignments and FND_USER_RESP_GROUPS_INDIRECT for those inherited through roles; the FND_USER_RESP_GROUPS view covers both. Join USER_ID to FND_USER and RESPONSIBILITY_ID plus RESPONSIBILITY_APPLICATION_ID to FND_RESPONSIBILITY_VL for the name. Check START_DATE and END_DATE to report only active access.

If you want admins to ask Oracle EBS who can run what, which programs failed and what is installed, in plain English on your own servers, we can build that read-only over AOL.

Talk to an Expert Explore More Free Tools