FND Flex Value Upload – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

Upload to create and update the values of independent, dependent, translatable independent and translatable dependent value sets, as in the Segment Values form.

A row updates the value when it exists in the value set, for a dependent value set under its Independent Value, and creates it otherwise.

Parent and Rollup Group can only be set for values of independent value sets. Child Range Low and Child Range High add a child range to a parent value; Delete Range Yes removes it.

Compiled Value Attributes holds the segment qualifier values, for example Allow Budgeting, Allow Posting and the account type, one per line. A new value without them gets the qualifiers’ default values; a blank cell keeps the stored values of an existing one.

After changing parent values or child ranges, submit the concurrent program Compile value set hierarchies for the value set, as the Segment Values form does when it saves.

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Upload Mode, Flex Value Set, Flex Value Like, Independent Value

Oracle EBS Tables Used

fnd_flex_values, fnd_flex_value_sets, fnd_flex_values_tl, fnd_flex_hierarchies_vl, fnd_flex_value_norm_hierarchy

Report Categories

Enginatics, Upload

Related Reports

FND Flex Values, FND Flex Value Hierarchy, FND Rollup Groups, GL Account Analysis, GL Account Analysis 11g, GL Account Distribution Analysis, GL Balance by Account Hierarchy, GL Balance

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource	Link
Excel Example Output	Example
Blitz Report™ XML Import	FND_Flex_Value_Upload.xml
SQL Source	FND Flex Value Upload.sql
Full Report on Enginatics	www.enginatics.com/reports/fnd-flex-value-upload/

Case Study & Technical Analysis: FND Flex Value Upload

Executive Summary

FND Flex Value Upload is an upload template for creating and maintaining the values of Oracle Application Object Library value sets, the lists that validate the segments of Accounting, Asset and other key and descriptive flexfields. The template downloads the current values of a chosen value set, lets the user change or add rows in Excel, and writes the changes back through the standard flexfield value API. It is used by EBS functional administrators and implementation teams who load chart of accounts segment values, maintain parent and child hierarchies, or set up flexfield attributes in bulk, for example during a new legal entity or cost center rollout.

Business Challenge

• Bulk Value Maintenance: Setting up a chart of accounts or a cost center structure through the Values form means one value at a time, which is slow for hundreds of values.
• Hierarchy Accuracy: Parent values, rollup groups and child ranges must be consistent, and errors in them affect every report that rolls up the values.
• Translation Effort: Translatable value sets need the meaning and description in each language, and these are often maintained in separate files.
• Flexfield Attributes: Qualifier values such as account type or allow posting, and the DFF attributes of each value, must be set consistently across many rows.

The Solution

• Value Set Download: Choosing a value set downloads its existing values with their enabled flag, active dates, summary flag and hierarchy assignments, so changes are made against current data.
• Upsert Processing: Each uploaded row is passed to the standard value load routine, which creates the value if it is new and updates it otherwise.
• Hierarchy Capture: Parent values carry their rollup group and child range, so a parent and its children can be created in one upload.
• Attribute Coverage: Compiled value attributes and the thirty DFF attributes are exposed as columns, with the value category decoding the descriptive flexfield prompts.

Technical Architecture (High Level)

Primary Tables

• FND_FLEX_VALUE_SETS: the value set definitions, including the parent value set reference.
• FND_FLEX_VALUES: the individual values with their hierarchy, dates, flags and attributes.
• FND_FLEX_VALUES_TL: the translated meaning and description of each value.
• FND_FLEX_HIERARCHIES_VL: the rollup group definitions used for structured hierarchies.
• FND_FLEX_VALUE_NORM_HIERARCHY: the child ranges of parent values in a normalized hierarchy.

Logical Relationships

1. The selected value set in FND_FLEX_VALUE_SETS is joined to its values in FND_FLEX_VALUES on the value set identifier.
2. A self join to FND_FLEX_VALUE_SETS through the parent value set identifier gives the parent value set name for dependent value sets.
3. Each value is joined to FND_FLEX_VALUES_TL on the value identifier, restricted to the session language.
4. The structured hierarchy identifier of each value is outer joined to FND_FLEX_HIERARCHIES_VL to return the rollup group name.
5. Each value is outer joined to FND_FLEX_VALUE_NORM_HIERARCHY on the value set and the parent value to return child ranges and their range attribute.

Parameters & Filtering

• Upload Mode: Selects Create, or Create and Update, which decides whether existing values are downloaded.
• Flex Value Set: The value set whose values are downloaded or created. It is the main driver of the extract.
• Flex Value Like: Limits the downloaded values to those whose value matches a pattern, for example a company prefix.
• Independent Value: For dependent value sets, restricts the rows to the dependent values of one independent value.

Performance & Optimization

• Value Set Driven Extract: The extract starts from one named value set, so only that set’s values are read.
• Language Filter: The translation join is restricted to the session language, so each value returns one description row.
• Outer Joins for Optional Data: Hierarchy, rollup and child range data are outer joined, so values without a hierarchy still appear.
• Upload Mode Filter: The download is suppressed in Create mode through the upload mode predicate, so an empty template is produced for new values.

FAQ

Q: Can I create a parent value and its child range in the same upload? A: Yes. Parent rows carry the child range low and high values and the range attribute, so the hierarchy is created with the parent.

Q: Do I need to run anything after uploading hierarchy changes? A: Yes. The description states that the Compile Value Hierarchy concurrent program must be run from the Value Sets form after parent values with hierarchy changes are uploaded.

Q: How are the descriptive flexfield attributes handled? A: Attribute1 to Attribute30 show the prompts of the value category, and Attribute31 to Attribute50 are shown as raw columns. The value category is returned with its display name.

---

Useful Links

• Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
• Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
• Oracle EBS Reporting Toolkits by Blitz Report™
• Blitz Report™ FAQ & Community Q&A
• Supply Chain Hub by Blitz Report™
• Blitz Report™ Customer Case Studies
• Oracle EBS Reporting Blog
• Oracle EBS Reporting Resource Centre

© Enginatics GmbH