Results for “jtf_um_usertype_reg”

50+ results




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

Overview

JTF_UM_USERTYPE_REG is a transactional registration table owned by the JTF schema within the CRM Foundation module of Oracle E-Business Suite. Its documented purpose is to store which user type a given application user has been registered as. In practice, the table functions as the association record between an FND_USER account and a defined user type maintained in JTF_UM_USERTYPES_B, capturing the lifecycle of that association through effective dating, status, and approval attributes. It therefore belongs to the user management subsystem of CRM Foundation, which underpins responsibility and role assignment across CRM applications such as TeleSales, Service, and Interaction Center.

From a dimensional modeling perspective, the documented foreign key structure suggests a Data Vault treatment as a link. The table sits between FND_USER and JTF_UM_USERTYPES_B, joining two hubs (user and user type) while carrying its own descriptive and workflow attributes. Analysts building a vault model should treat USERTYPE_REG_ID as the link surrogate key, and derive the composite business key from USER_ID plus USERTYPE_ID plus EFFECTIVE_START_DATE.

Key Information Stored

The physical schema documents fifteen columns in release 12.2.2. The most consequential are:

The unique index JTF_UM_USERTYPE_REG_U1 covers USERTYPE_REG_ID, while JTF_UM_USERTYPE_REG_U2 covers the composite of USERTYPE_ID, USER_ID, and EFFECTIVE_START_DATE — the effective business key that prevents duplicate concurrent registrations for the same user and type.

Common Use Cases and Queries

Typical reporting scenarios include listing all user types held by a user, identifying pending approvals, and auditing historical user type membership.

To retrieve current registrations for a user:

  • SELECT r.usertype_reg_id, r.usertype_id, t.user_type_name, r.effective_start_date FROM jtf.jtf_um_usertype_reg r JOIN jtf.jtf_um_usertypes_b t ON t.usertype_id = r.usertype_id WHERE r.user_id = :user_id AND (r.effective_end_date IS NULL OR r.effective_end_date > SYSDATE);

To review approvals routed through workflow, join to FND_USER twice — once for the registered user and once for the approver — and to WF_ITEMS on WF_ITEM_TYPE. Security group filtering should always be applied where multi-org data is present.

Related Objects

  • JTF_UM_USERTYPES_B — the user type definition table joined via USERTYPE_ID.
  • FND_USER — joined via USER_ID and APPROVER_USER_ID.
  • WF_ITEMS — joined via WF_ITEM_TYPE for workflow-enabled approvals.
  • FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID.
  • JTF_UM_SETUPS_B / JTF_UM_USERTYPE_REG_VL — related user management views and setup entities that consume registration data.
  • FND_USER registration APIs — the UM registration APIs that insert and update rows in this table during self-service and administrative provisioning.