Search Results jtf_um_usertype_subscrip




Overview
Explore in AI Chat

Overview

The JTF_UM_USERTYPE_SUBSCRIP table is a core intersection table within the Oracle E-Business Suite CRM Foundation (JTF) module, specifically designed for the User Management (UM) functionality. Its primary role is to manage the relationship between user types (roles or classifications of users) and available enrollments, known as subscriptions. This table acts as a mapping entity, defining which specific subscriptions are authorized and should be presented as available for enrollment to users assigned to a particular user type. It is a critical component in enforcing role-based access and provisioning within the EBS user management framework, ensuring users can only subscribe to appropriate services or functions based on their designated type.

Key Information Stored

The table stores the essential foreign keys that create the many-to-many relationship between user types and subscriptions, along with a column for managing the temporal validity of these assignments. The primary key is a composite of three columns, ensuring a unique and time-sensitive mapping. The most important columns are:

  • USERTYPE_ID: Foreign key referencing JTF_UM_USERTYPES_B. Identifies the specific user type or role.
  • SUBSCRIPTION_ID: Foreign key referencing JTF_UM_SUBSCRIPTIONS_B. Identifies the specific enrollment or service available for subscription.
  • EFFECTIVE_START_DATE: Part of the primary key, this column denotes the date from which the mapping between the user type and subscription becomes active, allowing for historical tracking and future-dated assignments.

Common Use Cases and Queries

A primary use case is administrative reporting and auditing to understand which subscriptions are permitted for various user types. System administrators may query this table to validate or modify role-based subscription entitlements. A typical query would join to the related base tables to retrieve meaningful names:

SELECT ut.USERTYPE_NAME, s.SUBSCRIPTION_NAME, jtus.EFFECTIVE_START_DATE
FROM JTF.JTF_UM_USERTYPE_SUBSCRIP jtus,
    JTF.JTF_UM_USERTYPES_B ut,
    JTF.JTF_UM_SUBSCRIPTIONS_B s
WHERE jtus.USERTYPE_ID = ut.USERTYPE_ID
AND jtus.SUBSCRIPTION_ID = s.SUBSCRIPTION_ID
AND ut.USERTYPE_NAME = '<User_Type_Name>';

Another critical use case occurs programmatically within the user self-service or administration flows, where the application queries this table to dynamically populate the list of available subscriptions a user can enroll in based on their assigned user type.

Related Objects

This intersection table has direct dependencies on two key base tables, as defined by its foreign key constraints:

  • JTF_UM_USERTYPES_B: The base table storing the definition and details of all user types. The USERTYPE_ID in JTF_UM_USERTYPE_SUBSCRIP references this table.
  • JTF_UM_SUBSCRIPTIONS_B: The base table storing the definition of all available subscriptions or enrollments. The SUBSCRIPTION_ID in JTF_UM_USERTYPE_SUBSCRIP references this table.

This table is central to the subscription enrollment model, and processes or interfaces that manage user provisioning and subscription availability will inherently interact with it.