Search Results jtf_um_subscription_tmpl




Overview
Explore in AI Chat

Overview

The JTF_UM_SUBSCRIPTION_TMPL table is a core intersection table within the Oracle E-Business Suite (EBS) CRM Foundation (JTF) module, specifically for the User Management (UM) functionality. Its primary role is to manage the many-to-many relationship between subscriptions and templates. In the context of EBS User Management, a subscription defines a set of responsibilities and roles granted to a user, while a template is a predefined model for a user's profile and access. This table acts as the junction, enabling a single subscription to be associated with multiple templates and vice versa, thereby providing a flexible framework for provisioning and managing user entitlements. It is a critical component for implementing delegated administration and automated user provisioning workflows in releases 12.1.1 and 12.2.2.

Key Information Stored

The table's structure is designed to enforce the relationship between subscriptions and templates with effective dating. The primary key is a composite of three columns, ensuring a unique association for a given period. The key columns are:

  • SUBSCRIPTION_ID: A foreign key referencing JTF_UM_SUBSCRIPTIONS_B. This identifies the specific subscription being linked.
  • TEMPLATE_ID: A foreign key referencing JTF_UM_TEMPLATES_B. This identifies the specific template attached to the subscription.
  • EFFECTIVE_START_DATE: Part of the primary key, this column denotes the date from which the subscription-template association becomes active, supporting historical tracking and future-dated assignments.

While the provided metadata focuses on the key structure, such intersection tables typically include standard Oracle columns like CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, and LAST_UPDATED_BY for auditing purposes.

Common Use Cases and Queries

This table is central to queries that determine which provisioning templates are available or required for a given subscription. A common administrative use case is auditing the template assignments for all subscriptions or identifying subscriptions linked to a specific template during a security review or upgrade analysis. For reporting, one might join this table to its parent tables to produce a human-readable list. A fundamental SQL pattern is:

SELECT sub.SUBSCRIPTION_NAME, tmpl.TEMPLATE_NAME, assoc.EFFECTIVE_START_DATE
FROM JTF_UM_SUBSCRIPTION_TMPL assoc,
    JTF_UM_SUBSCRIPTIONS_B sub,
    JTF_UM_TEMPLATES_B tmpl
WHERE assoc.SUBSCRIPTION_ID = sub.SUBSCRIPTION_ID
AND assoc.TEMPLATE_ID = tmpl.TEMPLATE_ID
AND SYSDATE BETWEEN assoc.EFFECTIVE_START_DATE AND NVL(assoc.EFFECTIVE_END_DATE, SYSDATE);

This query retrieves all currently active subscription-to-template associations. Data in this table is primarily managed through the Oracle User Management administrative UI or its underlying APIs, not via direct DML.

Related Objects

JTF_UM_SUBSCRIPTION_TMPL has direct foreign key dependencies on two key base tables, forming the core of its relationship model:

  • JTF_UM_SUBSCRIPTIONS_B: The master table for subscription definitions. The SUBSCRIPTION_ID column in JTF_UM_SUBSCRIPTION_TMPL references this table.
  • JTF_UM_TEMPLATES_B: The master table for user provisioning templates. The TEMPLATE_ID column in JTF_UM_SUBSCRIPTION_TMPL references this table.

This table is also intrinsically linked to the broader User Management data model, which includes related entities like JTF_UM_USER_SUBSCRIPTIONS (for user assignments) and JTF_UM_ROLE_SUBSCRIPTIONS (for role assignments). Understanding this intersection is essential for tracing the complete entitlement path from a template to an end user.