Search Results jtf_um_subscription_reg

Overview

The JTF_UM_SUBSCRIPTION_REG table is a core data object within the Oracle E-Business Suite (EBS) CRM Foundation (JTF) module, specifically supporting the User Management (UM) functionality. It serves as the central repository for recording and managing user enrollment requests for various subscriptions. A subscription, in this context, typically represents a role, responsibility, or access privilege that a user can request. The table's primary role is to track the lifecycle of these enrollment requests, storing their current status—such as approved, rejected, pending, or removed—and maintaining a historical record of changes. This enables structured workflow-driven approval processes and audit trails for user access management within the EBS ecosystem.

Key Information Stored

The table's structure is designed to capture the relationship between a user, a subscription, and the approval process. Its primary key is SUBSCRIPTION_REG_ID, a unique identifier for each enrollment record. The core relationship is defined by the USER_ID and SUBSCRIPTION_ID columns, which link to FND_USER and JTF_UM_SUBSCRIPTIONS_B respectively. The EFFECTIVE_START_DATE is part of a unique key, supporting effective-dated tracking of enrollments. Critical status and workflow management columns include the enrollment status and references to the workflow engine via WF_ITEM_TYPE and WF_ITEM_KEY. The table also records approval details, such as the APPROVER_USER_ID and relevant timestamps for creation, last update, and approval, providing a complete audit trail.

Common Use Cases and Queries

A primary use case is generating reports on user access requests and approvals. System administrators frequently query this table to audit enrollments, monitor pending requests, or list all active subscriptions for a given user. Common SQL patterns include fetching pending approvals for a specific approver or determining the approval history for a subscription.

• List all approved enrollments for a user:
  SELECT sub.NAME, reg.* FROM jtf_um_subscription_reg reg, jtf_um_subscriptions_tl sub WHERE reg.user_id = :user_id AND reg.STATUS = 'APPROVED' AND reg.subscription_id = sub.subscription_id AND sub.language = USERENV('LANG');
• Find all pending subscription requests:
  SELECT usr.user_name, sub.NAME, reg.* FROM jtf_um_subscription_reg reg, fnd_user usr, jtf_um_subscriptions_tl sub WHERE reg.status = 'PENDING' AND reg.user_id = usr.user_id AND reg.subscription_id = sub.subscription_id;

Related Objects

JTF_UM_SUBSCRIPTION_REG is centrally connected to several key EBS objects. Its main foreign key relationships are with JTF_UM_SUBSCRIPTIONS_B (the definition table for available subscriptions) and FND_USER (the core EBS users table). It also has a relationship with the workflow engine via the WF_ITEMS table, which manages the approval process instances. For descriptive information, JTF_UM_SUBSCRIPTIONS_TL provides translated names for subscriptions. This table is integral to the User Management APIs and underlying business logic that handle the subscription enrollment workflow, making it a critical dependency for access provisioning processes.