Results for “jtf_um_usertypes_tl”

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

Overview

JTF_UM_USERTYPES_TL is the translation (language) table that holds the multilingual descriptive attributes for user type definitions in Oracle E-Business Suite. It is owned by the JTF schema and belongs to the CRM Foundation product family, which supplies shared infrastructure for Oracle CRM applications such as Oracle TeleSales, Oracle Marketing, Oracle Service, and Oracle iSupport. The base table, JTF_UM_USERTYPES_B, stores language-independent user type records; JTF_UM_USERTYPES_TL stores the translatable name, short name, and description for each of those records, keyed by user type and language. In EBS 12.1.1 and 12.2.2 the table is reported as VALID, with 13 documented columns.

From a Data Vault modeling perspective, the mined relationship structure classifies this object as satellite-leaning. It does not introduce new business entities; instead it hangs off the JTF_UM_USERTYPES_B hub via USERTYPE_ID and carries descriptive, language-dependent context. The dependency on FND_SECURITY_GROUPS through SECURITY_GROUP_ID further reinforces its role as a dependent descriptive structure rather than an independent hub or link.

Key Information Stored

The table's primary key is JTF_UM_USERTYPES_TL_PK, defined on (USERTYPE_ID, LANGUAGE). USERTYPE_ID is the foreign key back to JTF_UM_USERTYPES_B and is the column most commonly referenced by callers searching on "usertype_id". LANGUAGE is the ISO/native language code that qualifies each translated row. Together these two columns form the business-key candidate, duplicated by the unique index JTF_UM_USERTYPES_TL_U1 on (USERTYPE_ID, LANGUAGE, ZD_EDITION_NAME), which supports the edition-based redefinition strategy used in 12.2.x.

The principal descriptive columns are USERTYPE_NAME, USERTYPE_SHORTNAME, and DESCRIPTION, which hold the translated display text for the user type. SOURCE_LANG identifies the language in which the row was originally entered, supporting the standard EBS translation pattern. The audit columns CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN record row lifecycle information. SECURITY_GROUP_ID ties the row to a security group defined in FND_SECURITY_GROUPS, and ZD_EDITION_NAME supports editioning in Release 12.2. Only USERTYPE_ID and LANGUAGE are part of the primary key; the remaining columns are descriptive or audit attributes.

Common Use Cases and Queries

Typical usage resolves a user type identifier to its translated label for a given session language. A common query joins the translation table to the base table and filters by the session language, with a fallback to the source language when no translated row exists:

• SELECT b.usertype_id, t.usertype_name, t.description FROM jtf.jtf_um_usertypes_b b, jtf.jtf_um_usertypes_tl t WHERE b.usertype_id = t.usertype_id AND t.language = USERENV('LANG');
• Reporting on all user types available to a language: SELECT usertype_id, usertype_name FROM jtf.jtf_um_usertypes_tl WHERE language = 'US';
• Security-scoped lookups: join SECURITY_GROUP_ID to FND_SECURITY_GROUPS to constrain results to a permitted group.
• Translation completeness checks comparing row counts per language against the base table.

Because the table is a translation satellite, direct DML is generally performed through the owning CRM Foundation application or the corresponding JTF user-management APIs rather than by ad hoc updates.

Related Objects

The following objects are the most significant relationships for this table, grounded in the documented foreign key and primary key metadata:

• JTF_UM_USERTYPES_B — base table; joined on JTF_UM_USERTYPES_TL.USERTYPE_ID = JTF_UM_USERTYPES_B.USERTYPE_ID.
• FND_SECURITY_GROUPS — referenced by JTF_UM_USERTYPES_TL.SECURITY_GROUP_ID.
• FND_LANGUAGES — the source of valid LANGUAGE and SOURCE_LANG values used by the translation pattern.
• FND_APPLICATION / FND_PRODUCT_INSTALLATIONS — context for the JTF CRM Foundation ownership of the object.
• JTF_UM_USERTYPES_VL (where present) — the translated view layered over the _B and _TL pair for convenient querying.
• JTF user-management PL/SQL APIs that read and maintain user type definitions on behalf of CRM applications.

These relationships confirm that JTF_UM_USERTYPES_TL functions strictly as a language-dependent descriptive extension of the user type base entity within the CRM Foundation schema.