Results for “jtf_um_usertype_tmpl”

50+ results




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

Overview

JTF_UM_USERTYPE_TMPL is a table in the JTF schema (CRM Foundation product family) that functions as an intersection, or association, table within the Oracle E-Business Suite User Management (UM) model. Its documented purpose is to attach a user type to one or more templates, forming the many-to-many bridge between the definition of a user type and the set of templates that apply to it. This enables the application to resolve which templates are available or applied when a user is provisioned under a given user type.

The table is owned by the JTF schema and is marked VALID. The primary key constraint, JTF_UM_USERTYPE_TMPL_PK, is defined across USERTYPE_ID, TEMPLATE_ID, and EFFECTIVE_START_DATE, reflecting that the association is date-effective: a user type may be linked to a template across multiple effective periods, with each period representing a distinct row. Under the heuristic Data Vault classification supplied in the metadata, this object is best modeled as a link — a relationship entity whose grain is the combination of the two referenced business entities (user type and template) qualified by an effective date.

Key Information Stored

The documented physical schema contains 11 columns. The most significant are:

The surrogate identity is effectively the composite of USERTYPE_ID, TEMPLATE_ID, and EFFECTIVE_START_DATE as enforced by the primary key. The unique index JTF_UM_USERTYPE_TMPL_U1 (USERTYPE_ID, TEMPLATE_ID, EFFECTIVE_START_DATE, ZD_EDITION_NAME) is the business-key candidate, adding ZD_EDITION_NAME so that editioned copies of the same logical association can coexist during online patching.

Common Use Cases and Queries

Typical usage resolves which templates apply to a user type at a point in time, or audits the template configuration of user types. Because the table is date-effective, queries should constrain EFFECTIVE_START_DATE and EFFECTIVE_END_DATE to the desired as-of date.

  • Resolve active templates for a user type:
    SELECT t.template_id, t.name
    FROM   jtf_um_usertype_tmpl ut
    JOIN   jtf_um_templates_b t ON t.template_id = ut.template_id
    WHERE  ut.usertype_id = :usertype_id
    AND    SYSDATE BETWEEN ut.effective_start_date
                       AND NVL(ut.effective_end_date, SYSDATE + 1);
    
  • Report all user type / template associations:
    SELECT u.usertype_id, u.name AS usertype_name,
           t.template_id, t.name AS template_name
    FROM   jtf_um_usertype_tmpl ut
    JOIN   jtf_um_usertypes_b u ON u.usertype_id = ut.usertype_id
    JOIN   jtf_um_templates_b t ON t.template_id = ut.template_id;
    
  • Audit change history by ordering on effective_start_date, and validate referential integrity by confirming every USERTYPE_ID and TEMPLATE_ID resolves to its parent table.

Related Objects

  • JTF_UM_USERTYPES_B — Parent of the association; joined on USERTYPE_ID.
  • JTF_UM_TEMPLATES_B — Parent of the association; joined on TEMPLATE_ID.
  • FND_SECURITY_GROUPS — Referenced via SECURITY_GROUP_ID for security group isolation.
  • JTF_UM_USERTYPE_TMPL_PK — Primary key constraint on (USERTYPE_ID, TEMPLATE_ID, EFFECTIVE_START_DATE).
  • JTF_UM_USERTYPE_TMPL_U1 — Unique index providing the business-key candidate including ZD_EDITION_NAME.