Oracle Fusion HCM Lookup Codes — Complete Reference Guide

Lookup codes are the backbone of Oracle Fusion HCM's coded values. Every time you see a dropdown in the UI — action types, worker types, phone types, marital status — those values come from lookups stored in FND_LOOKUP_VALUES. If you write OTBI reports, build HDL files, or develop integrations, you need to know these codes. This is the reference you will bookmark.

What Are Lookups

A lookup is a code-to-meaning mapping. The code is what gets stored in database columns (e.g., E), and the meaning is what users see in the UI (e.g., "Employee"). Lookups live in FND_LOOKUP_VALUES and related tables.

Lookup Types

Finding Lookups in the UI

1. Navigate to Setup and Maintenance
2. Search for the task: Manage Common Lookups
3. In the Lookup Type field, enter the lookup name (e.g., PER_ASSIGNMENT_CATEGORY)
4. Click Search to see all codes, meanings, descriptions, and enabled flags

For HCM-specific extensible lookups, use Manage Extensible Lookups instead. The navigation is similar but shows extensible lookup types.

ACTION_TYPE

Action types define what happened to a worker: hire, termination, promotion, etc. Used in PER_ALL_ASSIGNMENTS_F and PER_PERIODS_OF_SERVICE. Extensible

ACTION_REASON

Action reasons provide the "why" behind an action. Paired with ACTION_TYPE. Extensible

ASSIGNMENT_STATUS_TYPE

Controls the current state of an assignment. Stored in PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID. Standard

ASSIGNMENT_CATEGORY

Employment category for the assignment. Extensible

WORKER_TYPE

Defines the type of worker for a work relationship. Stored in PER_PERIODS_OF_SERVICE. Used in HDL WorkRelationship files. Standard

SYSTEM_PERSON_TYPE

System-level person type classification. Stored in PER_PERSON_TYPE_USAGES_F. Standard

ADDRESS_TYPE

Address type for person addresses in PER_ADDRESSES_F. Extensible

PHONE_TYPE

Phone type codes stored in PER_PHONES.PHONE_TYPE. Extensible

EMAIL_TYPE

Email type codes stored in PER_EMAIL_ADDRESSES.EMAIL_TYPE. Extensible

MARITAL_STATUS

Marital status codes stored in PER_PERSON_LEGISLATIVE_F.MARITAL_STATUS. Legislation-specific — some codes vary by country. Extensible

SEX (Gender)

Gender codes stored in PER_ALL_PEOPLE_F.SEX and PER_PERSON_LEGISLATIVE_F.SEX. Standard

NAME_TYPE

Name type codes in PER_PERSON_NAMES_F.NAME_TYPE. Standard

ABSENCE_TYPE & ABSENCE_REASON

Absence types and reasons control leave management. These are highly configurable and vary by implementation, but Oracle seeds common types. Extensible

Common Absence Types

FND_LANGUAGES

Language codes used throughout Oracle Fusion for translations. Common codes found in _TL (translation) tables. Standard

SQL Queries for Lookups

Use these queries in OTBI, BI Publisher, or SQL Developer to look up valid codes programmatically.

Query All Values for a Lookup Type

SELECT lookup_code,
       meaning,
       description,
       enabled_flag,
       start_date_active,
       end_date_active
FROM   fnd_lookup_values
WHERE  lookup_type = 'ACTION_TYPE'
AND    language    = 'US'
AND    enabled_flag = 'Y'
AND    NVL(end_date_active, SYSDATE + 1) > SYSDATE
ORDER BY lookup_code;

Query HCM-Specific Lookups (PER Lookup Types)

SELECT flv.lookup_type,
       flv.lookup_code,
       flv.meaning,
       flv.description
FROM   fnd_lookup_values flv
WHERE  flv.lookup_type LIKE 'PER%'
AND    flv.language = 'US'
AND    flv.enabled_flag = 'Y'
ORDER BY flv.lookup_type, flv.lookup_code;

Find Which Lookup Type a Code Belongs To

-- "What lookup type contains the code 'ACTIVE_PROCESS'?"
SELECT lookup_type,
       lookup_code,
       meaning
FROM   fnd_lookup_values
WHERE  lookup_code = 'ACTIVE_PROCESS'
AND    language    = 'US';

Count All Lookup Codes by Type

SELECT lookup_type,
       COUNT(*) AS code_count
FROM   fnd_lookup_values
WHERE  language     = 'US'
AND    enabled_flag = 'Y'
AND    lookup_type LIKE 'PER%'
GROUP BY lookup_type
ORDER BY code_count DESC;

Creating Custom Lookups

You can create your own lookup types for custom DFFs, extensible flexfields, or integrations.

Steps to Create a Custom Lookup

1. Navigate to Setup and Maintenance
2. Search for Manage Common Lookups
3. Click the + (Create) button
4. Enter a Lookup Type name (e.g., XX_CUSTOM_STATUS). Prefix with XX_ or your company code to avoid conflicts with Oracle-seeded types.
5. Set the Module to the appropriate application
6. Add Lookup Codes with meanings and descriptions
7. Save and close

Adding Codes to Extensible Lookups

1. Navigate to Setup and Maintenance
2. Search for Manage Extensible Lookups
3. Search for the lookup type (e.g., ACTION_REASON)
4. Click the + button in the Lookup Codes section
5. Enter your new code, meaning, and description
6. Set Enabled to Yes and optionally set start/end dates
7. Save

Tips & Gotchas

Case Sensitivity

• Lookup codes are case-sensitive. HIRE is not the same as hire or Hire.
• Always use the exact case shown in the lookup definition
• In HDL files, mismatched case is one of the most common causes of "invalid lookup" errors

Lookup Code vs. Meaning

• The code (e.g., E) is what is stored in the database column
• The meaning (e.g., "Employee") is what users see in the UI
• When building integrations or HDL files, always use the code, not the meaning
• OTBI reports may show the meaning — use the code column if you need to filter or join

Extensible vs. Standard

• Standard lookups are read-only. If you need a value that doesn't exist, talk to Oracle Support — there is no workaround.
• Extensible lookups let you add codes. Do not modify or disable Oracle-seeded codes — other features may depend on them.
• Custom lookups are entirely yours. Use them for DFFs and custom integrations.

Date-Effective Lookups

• Lookup codes can have START_DATE_ACTIVE and END_DATE_ACTIVE
• Always filter by date when querying to avoid using disabled or future-dated codes
• End-dating a code does not remove it from historical records — it just hides it from dropdowns going forward

Legislation-Specific Lookups

• Many lookup types (marital status, gender, ethnicity) have legislation-specific variants
• A code valid in one country may not exist in another
• Check the LEGISLATION_CODE column when querying to get country-appropriate values

Related Articles

• HCM Data Loader (HDL) Complete Guide — .dat File Format, Templates & Troubleshooting
• Top 50 Most Used Oracle Fusion HCM Tables
• Oracle HCM Table Suffixes Explained (_F, _TL, _VL, _ALL)
• Complete Guide to PER_ALL_PEOPLE_F