Results for “jtf_um_subscriptions_b”

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

Overview

JTF_UM_SUBSCRIPTIONS_B is a foundational table within the JTF – CRM Foundation product family, which provides the user management and enrollment infrastructure underpinning Oracle E-Business Suite Release 12.1.1 and 12.2.2. A subscription, as modeled by this table, represents an enrollment that grants a user access to a specific portion of the system through roles, permissions, and the responsibility defined for the enrollment. In practice, subscriptions act as the linking construct between authenticated principals and the resources they are entitled to consume, whether those resources are partner programs, self-service registrations, or delegated administrative capabilities.

From a Data Vault modeling perspective, the metadata heuristic classifies JTF_UM_SUBSCRIPTIONS_B as a hub. This suggests that the table functions as the central anchor for a subscription business key, with descriptive attributes and transactional history distributed to satellite and link structures. The table is owned by the JTF schema and is reported as VALID in the ETRM 12.2.2 inventory, spanning 18 documented columns.

Key Information Stored

The table uses a surrogate primary key, SUBSCRIPTION_ID, enforced through the constraint JTF_UM_SUBSCRIPTIONS_B_PK. Two unique indexes serve as documented business-key candidates: JTF_UM_SUBSCRIPTIONS_B_U1 (SUBSCRIPTION_ID, ZD_EDITION_NAME) and JTF_UM_SUBSCRIPTIONS_B_U2 (SUBSCRIPTION_KEY, EFFECTIVE_START_DATE, ZD_EDITION_NAME). The inclusion of ZD_EDITION_NAME in both indexes reflects the editioning support introduced in 12.2.2.

The most significant columns include:

• SUBSCRIPTION_ID – surrogate primary key and the join column referenced by all dependent child tables.
• SUBSCRIPTION_KEY – the natural or business-facing identifier used alongside the effective date to uniquely distinguish a subscription.
• ENABLED_FLAG – indicates whether the enrollment is active and enforceable.
• APPLICATION_ID – identifies the application context in which the subscription is granted.
• EFFECTIVE_START_DATE and EFFECTIVE_END_DATE – define the validity window of the enrollment, supporting date-effective querying and history.
• PARENT_SUBSCRIPTION_ID – supports hierarchical or nested enrollment relationships.
• APPROVAL_ID – foreign key to JTF_UM_APPROVALS_B, tying the subscription to its approval workflow record.
• AUTH_DELEGATION_ROLE_ID – foreign key to JTF_AUTH_PRINCIPALS_B, enabling delegated authorization.
• SECURITY_GROUP_ID – foreign key to FND_SECURITY_GROUPS, governing row-level access.
• AVAILABILITY_CODE and LOGON_DISPLAY_FREQUENCY – control how and how often the subscription is surfaced to the end user.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN – standard audit columns present across EBS transactional tables.

Common Use Cases and Queries

Typical reporting scenarios include extracting active subscriptions for a given application, auditing expiring enrollments, and reconciling approval status against approval records.

• Listing enabled subscriptions effective on the current date:

  SELECT subscription_id, subscription_key, application_id
  FROM   jtf.jtf_um_subscriptions_b
  WHERE  enabled_flag = 'Y'
  AND    SYSDATE BETWEEN effective_start_date
                     AND NVL(effective_end_date, SYSDATE + 1);

• Joining to the translation table for the display name:

  SELECT b.subscription_key, t.subscription_name
  FROM   jtf_um_subscriptions_b b, jtf_um_subscriptions_tl t
  WHERE  b.subscription_id = t.subscription_id
  AND    t.language = USERENV('LANG');

• Auditing approvals: join APPROVAL_ID to JTF_UM_APPROVALS_B to identify subscriptions awaiting or granted approval.
• Reconciling delegated authority via AUTH_DELEGATION_ROLE_ID against JTF_AUTH_PRINCIPALS_B.

Related Objects

The table participates in a dense referential web. The most significant related objects are:

• JTF_UM_SUBSCRIPTIONS_TL – stores translated descriptive text, joined on SUBSCRIPTION_ID.
• JTF_UM_SUBSCRIPTION_REG – registration-level detail for each subscription.
• JTF_UM_SUBSCRIPTION_RESP – responsibilities granted through the subscription.
• JTF_UM_SUBSCRIPTION_ROLE – roles associated with the enrollment.
• JTF_UM_SUBSCRIPTION_TMPL – template definitions driving subscription creation.
• JTF_UM_USERTYPE_SUBSCRIP – links subscription records to user types.
• JTF_AUTH_PRINCIPALS_B – principal referenced by AUTH_DELEGATION_ROLE_ID.
• FND_SECURITY_GROUPS – referenced by SECURITY_GROUP_ID for access control.
• JTF_UM_APPROVALS_B – referenced by APPROVAL_ID for workflow approvals.