Flexfields in Oracle Apps R12
You must feel like your Oracle E-Business Suite (EBS) is almost perfect, but just misses one little field you are looking for. Or perhaps you’re looking at a seeded report and wishing you could slice and dice data in a way Oracle doesn’t offer? This is where you need to understand and use a good Oracle Apps inbuild feature: Flexfields.
Think of Flexfields as Oracle’s secret power– they let you customize and extend your application without getting into the complex code. For anyone navigating the Oracle R12 environment and truly understanding Flexfields isn’t just a good idea; it’s a necessity.
This guide is designed to show how Flexfields work, why they matter, and how they can make your life in Oracle so much easier. Whether you’re building solutions, defining business rules, or simply trying to get the most out of your system, mastering Flexfields will feel like gaining a new limb in your Oracle toolkit.
Table of Contents
What are Flexfields in Oracle Apps?
Flexfields Tables in Oracle Apps R12
What Exactly are Flexfields in Oracle Apps?
Oracle Flexfields are just user-defined data fields where you can capture additional information that Oracle’s standard fields don’t offer. They come in two main categories: Key Flexfields (KFF) and Descriptive Flexfields (DFF). Each has its own features, but together, they can give you an incredible power to shape Oracle EBS to your unique business needs and ensure your data tells the full story.
Types of Flexfields
There are two main types:
- Key Flexfields (KFF):
- Define the structure of fundamental business identifiers (e.g., Chart of Accounts, Item Categories).
- Composed of segments that map to database columns.
- Examples: Accounting Flexfield, Asset Category Flexfield.
- Descriptive Flexfields (DFF):
- Allow you to capture additional information not included in standard Oracle forms.
- Configurable at runtime without changing the database structure.
- Examples: Adding “GST Number” in Customer Master, or “Batch Number” in Purchase Orders.
Key Flexfields (KFF): Smart Identifiers
Let’s imagine you’re trying to name a product, or perhaps a financial account, using just one long string of numbers. That would be messy and unhelpful! Key Flexfields step in here like a master organizer. They help you build “intelligent keys” or unique identifiers by breaking down that long string into meaningful parts. This isn’t just about making things neat; it’s about making your data consistent and easy to report on.
Let’s look at some Oracle KFF examples
- Chart of Accounts: This is probably the most famous key flexfields! Instead of just a “general ledger account number,” you have segments for things like “Company,” “Department,” “Account,” and “Sub Account.” Each part is distinct, but together, they form a unique financial address.
- Inventory Items: If you’re managing stock, Key Flexfields in Oracle Apps R12 Inventory let you define how your items are uniquely identified. Maybe it’s a combination of product line, material, and size. This makes tracking and managing your inventory a breeze.
- Asset Keys: In your Fixed Assets module, KFFs ensure every asset has a one-of-a-kind identifier.
How KFF are Built
A Key Flexfield is like a Lego structure. Each “block” is a segment, and each segment holds a piece of your unique identifier. To make sure you’re always using the right kind of block, each segment is linked to a Value Set. These Value Sets are like pre-approved lists of options. For instance, your “Department” segment would only let you choose from a list of valid departments you’ve already defined.
When you set up a KFF, you’re essentially telling Oracle: “Here’s how my unique identifiers will be structured, and here are the rules for what goes into each part.” This structure then influences how data is entered and validated throughout your system.
KFF’s Big Impact
KFFs aren’t just for show. They’re fundamental to how your data is entered, stored, and retrieved. They act as guardians of data consistency across all your modules, and they’re the backbone for countless reports and analyses. When you input data using a KFF, Oracle intelligently stores each piece (segment) in its own little spot within the database table, making it easy to pull back later.
Descriptive Flexfields (DFF): Your Customizable “Extra Fields”
Now, what if you just need a few extra pieces of information for a particular record, but it doesn’t quite fit the mold of a unique identifier? That’s where Descriptive Flexfields shine! Imagine you’re filling out a form, and at the bottom, there’s a section for “Additional Comments” or “Special Instructions.” DFFs are exactly like that – flexible, user-defined fields that let you capture more detail without touching Oracle’s core programming.
When Do You Call on a DFF?
You’ll find DFFs incredibly handy when:
- You need to add specific attributes to something – like a supplier’s preferred courier service, or an employee’s emergency contact name.
- The information you need is “context-sensitive.” This is where DFFs get really clever!
- You want to add fields to standard Oracle screens without getting into custom code. This is a huge win!
How Descriptive Flexfield in Oracle Built
DFFs can be configured in two smart ways:
- Global Segments: These are like universal extra fields. They always show up, no matter what. They’re perfect for information that applies to every record you’re dealing with.
- Context-Sensitive Segments: This is where the magic of descriptive flexfield context field values truly shines! Imagine you have a “Transaction Type” field on a form. If you select “Purchase Order,” one set of extra fields appears. But if you select “Sales Order,” a completely different set of extra fields pops up. This “context” field acts like a switch, revealing the relevant DFFs based on what you’ve chosen. It’s incredibly powerful for guiding users and ensuring you capture the right data for the right situation.
Setting up a DFF is like designing a mini-form within an existing Oracle screen. You decide where it goes, what fields it has, what kind of data those fields can hold, and how they should behave based on different contexts.
Where Your Descriptive Flexfields Data Lives
When you save data in a DFF, Oracle doesn’t create new columns in its tables. Instead, it uses pre-existing “attribute” columns (think of them as generic storage slots like ATTRIBUTE1, ATTRIBUTE2, etc.) in the main table of the record you’re working with. The CONTEXT column (if you’re using contexts) tells Oracle which set of DFF fields was displayed, and the ATTRIBUTE columns store the actual values you entered. So, when you’re doing a descriptive flexfield query in Oracle Apps R12, you’re essentially looking at these ATTRIBUTE columns, potentially joining with other setup tables to understand what ATTRIBUTE1 actually means in a given context.
Flexfields Tables in Oracle Apps R12
For those who like to peek behind the curtain, understanding the flexfield tables in Oracle Apps R12 is key. While your Flexfield data ends up in the main application tables (like PO_HEADERS_ALL for purchase orders), the definitions of how your Flexfields behave are stored in a special set of tables.
Following are the few main tables for Flexfields:
- FND_FLEX_VALUES and FND_FLEX_VALUE_SETS: These are like the dictionary and the list of words for all your Value Sets.
- FND_ID_FLEXS and FND_ID_FLEX_SEGMENTS: These tables hold the blueprint for all your Key Flexfields and their segments.
- FND_DESCR_FLEXS and FND_DESCR_FLEX_CONTEXTS: These contain the master plan for your Descriptive Flexfields and all their clever contexts.
- FND_DESCR_FLEX_COL_USAGE: This table is like a decoder ring, telling Oracle which ATTRIBUTE column in a base table corresponds to which DFF segment.
When you configure flexfields in Oracle Apps, you’re essentially populating these tables, and then Oracle uses this information to build the user interface, validate your entries, and store your data correctly.
1. Flexfields Definition Tables
These tables store the metadata and setup information for Flexfields.
- FND_FLEX_VALUE_SETS: Stores the definitions of all Value Sets. Value Sets dictate the attributes (data type, size, validation type) of the values that can be entered into Flexfield segments.
- Key Columns: FLEX_VALUE_SET_ID, FLEX_VALUE_SET_NAME, VALIDATION_TYPE.
- FND_FLEX_VALUES / FND_FLEX_VALUES_TL: Stores the actual valid values for an independent or dependent Value Set. _TL suffix indicates the Translation table for multilingual support.
- Key Columns: FLEX_VALUE_SET_ID, FLEX_VALUE, DESCRIPTION, ENABLED_FLAG.
- FND_ID_FLEXS / FND_ID_FLEXS_TL: Stores the definitions of all Key Flexfields (KFFs).
- Key Columns: APPLICATION_ID, ID_FLEX_CODE, ID_FLEX_NAME.
- FND_ID_FLEX_SEGMENTS / FND_ID_FLEX_SEGMENTS_TL: Stores the definitions of segments for Key Flexfields. It links segments to their respective Value Sets.
- Key Columns: APPLICATION_ID, ID_FLEX_CODE, APPLICATION_COLUMN_NAME (e.g., SEGMENT1, SEGMENT2), SEGMENT_NAME, FLEX_VALUE_SET_ID, COLUMN_NAME.
- FND_DESCR_FLEXS / FND_DESCR_FLEXS_TL: Stores the definitions of all Descriptive Flexfields (DFFs).
- Key Columns: APPLICATION_ID, DESCRIPTIVE_FLEXFIELD_NAME, TABLE_APPL_SHORT_NAME, TABLE_NAME (the base table where DFF data is stored).
- FND_DESCR_FLEX_CONTEXTS / FND_DESCR_FLEX_CONTEXTS_TL: Stores the definitions of contexts for DFFs. Contexts determine which DFF segments are displayed based on a specific value.
- Key Columns: DESCRIPTIVE_FLEXFIELD_NAME, FLEXFIELD_CONTEXT_CODE, FF_CONTEXT_NAME (display name).
- FND_DESCR_FLEX_COL_USAGE / FND_DESCR_FLEX_COL_USAGE_TL: Links DFF segments to the actual ATTRIBUTE columns in the base application tables.
- Key Columns: DESCRIPTIVE_FLEXFIELD_NAME, FLEXFIELD_CONTEXT_CODE (for context-sensitive segments, ‘Global Data Elements’ for global), APPLICATION_COLUMN_NAME (e.g., ATTRIBUTE1, ATTRIBUTE2), COLUMN_PROMPT.
2. Data Tables
The actual Flexfield data (values entered by users) is stored in the base application tables where the Flexfield is enabled.
- For Key Flexfields (KFFs): Data is typically stored in columns named SEGMENT1, SEGMENT2, …, SEGMENTN within the relevant transaction or setup table (e.g., GL_CODE_COMBINATIONS for the Accounting Flexfield, or MTL_SYSTEM_ITEMS_B for the Item Flexfield).
- For Descriptive Flexfields (DFFs): Data is stored in columns named ATTRIBUTE1, ATTRIBUTE2, …, ATTRIBUTE15 and a CONTEXT column (often named ATTRIBUTE_CATEGORY or similar, depending on the DFF definition) within the relevant transaction or setup table (e.g., PO_HEADERS_ALL, AP_INVOICES_ALL, PER_ALL_PEOPLE_F). The CONTEXT column holds the context code that determined which ATTRIBUTE columns were used.
Step-by-Step: Creating a Key Flexfield (KFF)
Let’s take an example of creating a Custom Item Category Flexfield.
Step 1: Identify the Key Flexfield
- Navigate to: Setup → Flexfields → Key → Segments
- Query the application and flexfield you want (e.g., Inventory → System Items).
Step 2: Define the Structure
- Enter a Title (e.g., Custom Item Categories).
- Specify the Structure Code.
- Mark as Freeze Flexfield Definition unchecked (you’ll freeze later).
Step3: Define Segments
- Add required segments (e.g., Product Line, Item Type, Region).
- For each segment:
- Provide Prompt (label seen on the form).
- Assign Column (maps to database column).
- Define Value Set (validation rules for values).
- Example:
- Segment: Product Line
- Value Set: XX_PRODUCT_LINE_VS
Step 4: Save and Compile
- Save your structure.
- Check Freeze Flexfield Definition and Compile.
Step 5: Test the Flexfield
- Navigate to Inventory → Items → Master Items.
- Enter values in your newly created segments.
Step-by-Step: Creating a Descriptive Flexfield (DFF)
Now let’s create a DFF in the Customer Master form to capture a Customer GST Number.
Step 1: Identify the Table
- Navigate to: Help → Diagnostics → Examine on the form.
- Find the Table Name associated with the block (e.g., HZ_PARTIES).
Step 2: Navigate to DFF Setup
- Go to: Setup → Flexfields → Descriptive → Segments.
- Query the application and table (e.g., Application: Receivables, Table: HZ_PARTIES).
Step3: Define the Context
- Enter Context Field Value (e.g., India Localization).
- Provide a Description (e.g., GST Information).
Step 4: Define Segments
- Add a segment for GST Number:
- Segment Name: GST_NUMBER
- Prompt: GST Number
- Column: ATTRIBUTE1 (uses Oracle’s ATTRIBUTE columns)
- Value Set: XX_GST_VS (for format validation, e.g., 15-digit GSTIN).
Step 5: Save and Freeze
- Save your changes.
- Mark as Freeze Flexfield Definition and Compile.
Step6: Test in Customer Master
- Navigate to Customers → Standard → Create/Edit.
- Open the Descriptive Flexfield (usually a [ ] button).
- Enter and validate your GST Number.
Important Queries of Flexfields
Here are some important SQL queries to retrieve Flexfield information.
1. Get all Key Flexfields (KFFs)
SELECT fif.ID_FLEX_CODE,
fif.ID_FLEX_NAME,
fa.application_short_name,
fa.application_name
FROM FND_ID_FLEXS fif,
FND_APPLICATION_vl fa
WHERE fif.APPLICATION_ID = fa.APPLICATION_ID2. Get Segments for a Specific Key Flexfields
SELECT fif.application_id,
fif.id_flex_code,
fif.id_flex_name,
fif.application_table_name,
fif.description,
fifs.id_flex_num,
fifs.id_flex_structure_code,
fifse.segment_name,
fifse.segment_num,
fifse.flex_value_set_id
FROM fnd_id_flexs fif,
fnd_id_flex_structures fifs,
fnd_id_flex_segments fifse
WHERE fif.application_id = fifs.application_id
AND fif.id_flex_code = fifs.id_flex_code
AND fifse.application_id = fif.application_id
AND fifse.id_flex_code = fif.id_flex_code
AND fifse.id_flex_num = fifs.id_flex_num
AND fif.id_flex_code like 'GL#'
AND fif.id_flex_name like 'Accounting Flexfield';3. SQL to Get Details of Descriptive Flexfields (DFFs)
You can use below SQL query to get the descriptive flexfields (DFF) in Oracle apps R12.
SELECT fdft.title,
fdft.description,
fdf.descriptive_flexfield_name,
fdf.application_table_name,
fdf.concatenated_segment_delimiter,
fdf.concatenated_segs_view_name,
fdf.freeze_flex_definition_flag,
fdf.context_column_name,
fa.application_name
FROM fnd_descriptive_flexs fdf,
fnd_descriptive_flexs_tl fdft,
fnd_application_vl fa
WHERE fdft.application_id = fa.application_id
AND fdft.descriptive_flexfield_name = fdf.descriptive_flexfield_name
AND fdft.language = SYS_CONTEXT('USERENV', 'LANG')
--AND fdf.application_table_name='PO_HEADERS_ALL'
--AND fa.application_name='Purchasing'4. Get Segments (Global and Context-Specific) for a Specific Descriptive Flexfield
You can use below SQL query to get the detail information about a descriptive flexfields (DFF) in Oracle apps R12.
SELECT fdf.title
fdf.application_table_name
fdf.context_column_name
fdfcu.descriptive_flex_context_code
fdfcu.column_seq_num
fdfcu.end_user_column_name
fdfcu.application_column_name
ffv.flex_value_set_name
FROM fnd_descr_flex_col_usage_vl fdfcu,
fnd_descriptive_flexs_vl fdf,
fnd_flex_value_sets ffv
WHERE fdf.title = 'Additional Header Information'
AND fdfcu.enabled_flag = 'Y'
AND fdf.application_table_name ='OE_ORDER_HEADERS_ALL'
AND fdfcu.flex_value_set_id = ffv.flex_value_set_id
AND fdfcu.descriptive_flexfield_name = fdf.descriptive_flexfield_name
AND fdfcu.application_id = fdf.application_id
ORDER BY fdfcu.descriptive_flexfield_name,
fdfcu.descriptive_flex_context_code,
fdfcu.column_seq_num; 5. Query DFF Data from a Base Table (Example: PO_HEADERS_ALL)
You need to directly query the base table linked to the descriptive flexfields for retrieving actual DFF values. To make sense of the ATTRIBUTE columns, you typically need to know the DFF’s setup from the definition tables, or use a tool that decodes them.
SELECT pha.po_header_id,
pha.segment1 as po_number,
pha.attribute_category as dff_context,
pha.attribute1,
pha.attribute2,
pha.creation_date
FROM po_headers_all pha
WHERE pha.org_id = 81
AND pha.type_lookup_code = 'STANDARD'
AND pha.attribute1 IS NOT NULL; -- Filter for records that have DFF data6. Query KFF Data from a Base Table (Example: GL_CODE_COMBINATIONS)
SELECT gcc.chart_of_accounts_id,
gcc.segment1 AS Company,
gcc.segment2 AS Department,
gcc.segment3 AS Account,
gcc.segment4 AS Intercompany,
gcc.segment5 AS Future_Use,
gcc.code_combination_id,
gcc.enabled_flag,
gcc.start_date_active,
gcc.end_date_active
FROM gl_code_combinations gcc
WHERE gcc.chart_of_accounts_id = 50308
AND gcc.enabled_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(gcc.start_date_active, SYSDATE))
AND TRUNC(NVL(gcc.end_date_active, SYSDATE));Golden Rules for Flexfields Success (and Avoiding Headaches!)
- Plan Before You Play: Don’t just jump in! Before you define a single Flexfield, really think about why you need it, what information it will capture, and how it will be used down the line. A little planning saves a lot of pain.
- Smart Value Sets are Happy Value Sets: Pick the right kind of Value Set (independent, dependent, table-validated). This ensures your data is clean and easy to manage in the long run.
- Less is Often More: While tempting to add dozens of segments, too many can make data entry cumbersome and slow down your system. Keep it as lean as possible.
- Document, Document, Document! Write down every detail of your Flexfield setup – what it does, its segments, its value sets, and how contexts are used. Your future self (and your colleagues) will thank you.
- Test, Test, Test (Then Test Again!): Never deploy a Flexfield configuration without thoroughly testing it in a non-production environment. Make sure it works as expected and doesn’t break anything else.
- Mind Your Upgrades: If you’re on R12, remember that major upgrades might require a quick check of your Flexfield configurations.
The Takeaway: Your Oracle, Your Way!
Oracle Flexfields aren’t just a technical feature; they’re your gateway to truly customizing Oracle E-Business Suite to fit your unique business processes. Whether you’re setting up a simple descriptive flexfield Oracle for a few extra details or designing a complex key flexfields in Oracle Apps R12 Inventory structure, understanding these tools empowers you to get more out of your system. So, embrace the flexibility, plan wisely, and watch your Oracle EBS transform into an even more powerful tool for your organization!
Good technical guide, very insightfull.
Pingback: Mastering Value Sets in Oracle Apps R12
Pingback: Complete Guide to FNDLOAD Commands in Oracle Apps R12