Explain Value Sets and Validation in EBS in Oracle Applications

18 / 60

PL/SQLbeginner

Value Sets and Validation in EBS

Explain the different value set validation types used across flexfields, concurrent program parameters, and profile options

A value set defines what may be entered somewhere — a flexfield segment, a concurrent program parameter, a profile option. It is reusable, so one definition serves many places.

A value set is like a dropdown menu template that a restaurant chain can reuse across every location's ordering kiosk — define 'Valid Toppings' once, and every kiosk (form/parameter) that references it automatically shows the same governed list, updated centrally.

Key Concepts

Five validation types.

    None         format and length only; free text
    Independent  a fixed list you maintain
    Dependent    the list depends on another segment's value
    Table        a SQL query against any application table
    Special/Pair used for flexfield cross-validation

Dependent is the State-and-City case. Choose a state, and the city list shows only that state's cities. The dependent value set is filtered by the independent one above it.

Table-validated is the one worth knowing properly, because it keeps the list in step with live data.

    Table:            gl_code_combinations
    Value:            segment2
    Meaning:          description
    WHERE clause:     enabled_flag = 'Y'
                  AND org_id = :$PROFILES$.ORG_ID

The bind variables are the useful part.

    :$PROFILES$.name    a profile option value for this session
    :$FLEX$.value_set   a value chosen in another segment
    :$SYSTEM.CURRENT_DATE
    :block.field        a field on the current form

So a cost centre parameter can list only the cost centres in the user's operating unit, without hardcoding anything. Two users running the same program see different lists, correctly.

A worked example, which is what interviewers ask for. "A parameter listing only active cost centres for the user's operating unit":

    validation type:  Table
    table:            xx_cost_centres
    value column:     cost_centre_code
    meaning column:   cost_centre_name
    WHERE:            active_flag = 'Y'
                  AND org_id = :$PROFILES$.MO_OPERATING_UNIT

Two practical cautions. The WHERE clause runs every time the list is opened, so a query against a large unindexed table makes the parameter list slow and users notice immediately.

And a value set is shared. Changing one to suit a new program changes it everywhere it is already used — which is the trade for reusability, and the reason installations end up with near-duplicate value sets rather than editing a shared one.

What the interviewer is probing.1. "What are the value set validation types?" Probing: the breadth. Stalls: "A list." *Moves up:* none, independent, dependent, table, special, pair and translatable variants — table-validated being the most used for custom lists.

2. "When would you use a dependent value set?" Probing: the fit. Stalls: "Never." *Moves up:* when valid values depend on another segment, such as a cost centre list that varies by company.

3. "What is the risk with a table-validated value set?" Probing: the practical angle. Stalls: "None." Moves up: the WHERE clause runs on every validation and list of values, so a poorly written one slows down data entry everywhere it is used.

4. "How are these used outside flexfields?" Probing: the breadth. Stalls: "They are not." Moves up: as concurrent program parameters, which is how a custom report gets a validated list of values.