Results for “asf_user_preference”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

ASF_USER_PREFERENCE is a table in the ASF schema (Sales Online module) within Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to store data for different purposes for individual users, identified by the USER_ID column. The table functions as a general-purpose personalization store: rather than committing a dedicated column to every user preference, ASF records preferences as rows keyed by a preference code and an optional owner reference, allowing the application to persist context-sensitive settings for a specific user against a specific business entity. The table contains 28 documented columns, all owned by ASF, and is classified as VALID in the ETRM repository.

The ETRM metadata provides a heuristic Data Vault classification of "standalone" for this object, mined from its foreign key structure. In Data Vault modeling terms, this suggests treating ASF_USER_PREFERENCE as a self-contained satellite-like structure — a keyed, descriptive record attached to a user rather than a connector (link) between two hubs or a pure hub of business keys. This is a modeling suggestion only; the definitive relationship graph should be confirmed against the deployed schema, since the heuristic reflects the absence of documented foreign keys rather than a formal normalization decision.

Key Information Stored

The table's surrogate primary key is PREFERENCE_ID, enforced by the index ASF_USER_PREFERENCE_PK. A second unique index, ASF_USER_PREFERENCE_U1, defines the business-key candidate: the combination of USER_ID, PREFERENCE_CODE, OWNER_TABLE_NAME, and OWNER_TABLE_ID. This composite uniqueness constraint is the single most important design fact about the table — it means a given user can hold only one value per preference code for a given owning record.

• PREFERENCE_ID — surrogate primary key, uniquely identifying each preference row.
• USER_ID — the individual user to whom the preference belongs; first component of the unique business key.
• PREFERENCE_CODE — the name or code of the preference, distinguishing one setting from another for the same user.
• OWNER_TABLE_NAME — the name of the table that owns the context in which the preference applies.
• OWNER_TABLE_ID — the primary key value of the owning record, tying the preference to a specific entity.
• PREFERENCE_VALUE — the stored value of the preference, held as text.
• CATEGORY — a grouping column for classifying preferences.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard EBS "Who" audit columns recording creation and last modification context.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard EBS descriptive flexfield (DFF) columns, providing extensible, customer-defined storage alongside the seeded preference fields.

Common Use Cases and Queries

ASF_USER_PREFERENCE is typically queried when diagnosing why a user sees a particular screen state, default value, or filter, and when migrating or cleaning up personalizations. A common pattern retrieves all preferences for a user:

• SELECT preference_code, preference_value FROM asf.asf_user_preference WHERE user_id = :p_user_id;
• SELECT * FROM asf.asf_user_preference WHERE owner_table_name = :p_table AND owner_table_id = :p_id; — resolves preferences scoped to a specific business record.
• SELECT user_id, preference_code, preference_value FROM asf.asf_user_preference WHERE preference_code = :p_code; — audits who has set a given preference.
• Reporting on DFF usage via attribute_category and the ATTRIBUTE1–15 columns to identify custom extensions and assess migration impact.

Because the business-key uniqueness is enforced across USER_ID, PREFERENCE_CODE, OWNER_TABLE_NAME, and OWNER_TABLE_ID, direct inserts or updates that violate this combination will fail; remediation should target the existing row rather than adding a duplicate.

Related Objects

The ETRM metadata documents no foreign keys for this table, consistent with its standalone classification. The relationships that matter operationally are logical rather than declarative:

• PER_USERS / FND_USER — the USER_ID column resolves to the EBS user definition, establishing the ownership of each preference.
• ASF_USER_PREFERENCE_PK and ASF_USER_PREFERENCE_U1 — the unique indexes that enforce identity and business-key uniqueness.
• The owner table named dynamically in OWNER_TABLE_NAME — a polymorphic reference to whichever ASF or related table an individual preference is scoped to.
• FND_USER_PREFERENCES — the framework-level user preference store, useful as a comparison point when determining where a given setting is persisted.
• ASF business tables in the Sales Online module that expose preference-driven screens and defaults, whose runtime behaviour depends on rows in this table.

Any dependency analysis should be performed at runtime, since the polymorphic OWNER_TABLE_NAME/OWNER_TABLE_ID pairing is not captured by database constraints.