Search Results sy_orgn_usr




Overview
Explore in AI Chat

Overview

SY_ORGN_USR is a Process Manufacturing (GMA) association table in Oracle E-Business Suite 12.1.1 and 12.2.2 that maps Oracle Process Manufacturing (OPM) organizations to Oracle Applications (FND) users. Residing in the GMA schema with a status of VALID, the table answers a single, fundamental security question: which OPM organizations is a given FND user authorized to access? By binding a USER_ID to an ORGN_CODE, it constrains the organizational scope of process manufacturing transactions for each user, making it a foundational component of OPM organization-level access control.

From a dimensional modeling perspective, the documented foreign key structure suggests classifying SY_ORGN_USR as a link table. It contains no descriptive attributes of its own beyond audit columns, and its entire purpose is to resolve a many-to-many relationship between users and organizations. This is a heuristic suggestion based on the FK topology, not a declared Data Vault design, but it reflects the table's role as an intersection entity.

Key Information Stored

The table comprises seven documented columns. The two most significant are the composite primary key members, which also serve as the business-key candidate via the unique index SY_ORGN_USR_PK:

  • USER_ID — The FND user identifier, part of the composite primary key (SY_ORGN_USR_PK) and the link to the FND user population.
  • ORGN_CODE — The OPM organization code, the second component of the composite primary key and the foreign key column referencing OPM organization master data.
  • LAST_UPDATE_DATE — Audit timestamp of the most recent modification.
  • LAST_UPDATED_BY — The user who performed the last update.
  • CREATION_DATE — Audit timestamp of row creation.
  • CREATED_BY — The user who created the association row.
  • LAST_UPDATE_LOGIN — The login context associated with the last update.

No separate surrogate key column exists; the composite of USER_ID and ORGN_CODE functions as both the primary key and the natural business key. There are no descriptive or historical (satellite-style) columns — the row simply exists (or does not) to grant a user access to an organization.

Common Use Cases and Queries

The primary use case is determining a user's authorized OPM organizations when running process manufacturing reports, transaction entry, or security audits. A typical query joins SY_ORGN_USR to FND_USER to translate the numeric USER_ID into a recognizable user name:

  • Listing all organizations a specific user can access by filtering on USER_ID.
  • Listing all users authorized for a given ORGN_CODE, useful when auditing organization security or onboarding new users.
  • Identifying orphaned or stale assignments by outer-joining to SY_ORGN_MST to find ORGN_CODE values no longer present in the organization master.
  • Comparing authorized organizations against actual transaction activity to detect privilege excess.

A representative pattern selects USER_ID and ORGN_CODE from SY_ORGN_USR, joining USER_ID to FND_USER.USER_ID and ORGN_CODE to SY_ORGN_MST to enrich the result with user and organization names. Reports are commonly filtered by organization for OPM inventory, production, and costing dashboards.

Related Objects

The documented foreign keys anchor SY_ORGN_USR to the OPM organization master. The most significant related objects are:

  • SY_ORGN_MST — OPM organization master; joined on SY_ORGN_USR.ORGN_CODE = SY_ORGN_MST.ORGN_CODE.
  • SY_ORGN_MST_B — The _B (base) variant of the organization master, also referenced via ORGN_CODE.
  • SY_ORGN_USR_PK — The unique index enforcing the composite primary key on (USER_ID, ORGN_CODE).
  • FND_USER — Not documented as an enforced FK here, but the implicit parent of USER_ID; joined on USER_ID for user names.

Together these objects define the OPM organization-level access model that SY_ORGN_USR mediates.