Results for “psb_entity_assignment_u1”

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

Overview

PSB.PSB_ENTITY_ASSIGNMENT is a transactional table within the PSB schema of Oracle E-Business Suite, used by the Enterprise Territory Management and related applications that rely on the PSB entity framework. It stores the assignment of entities to entity sets. The table operates as a junction between PSB_ENTITY_SET and PSB_ENTITY, and its purpose is to allow reuse of parameter, constraint, or allocation rules while permitting different priorities or severity levels in different sets. When an assignment targets a Parameter Set, the PRIORITY column carries the processing priority for the entity. When the assignment targets a Constraint Set, the SEVERITY_LEVEL column carries the severity for that constraint. This design decouples the definition of a rule from its placement and weighting within a particular set, which is central to how PSB evaluates parameters and constraints during runtime processing.

From a modeling perspective, the heuristic Data Vault classification for this object is a link table. It resolves a many-to-many relationship between two business entities (entity sets and entities) and carries descriptive attributes—priority, severity, and effective dating—that relate to the association itself rather than to either parent. The documented schema spans 11 columns and appears in tablespace APPS_TS_TX_DATA with PCT Free 10.

Key Information Stored

The following columns represent the most significant data held by this object:

• ENTITY_SET_ID — Surrogate identifier and part of the composite primary key; the entity set to which an entity is assigned.
• ENTITY_ID — Surrogate identifier and the other component of the composite primary key; the entity being assigned.
• PRIORITY — Processing priority for the entity within a Parameter Set. In earlier releases this was a numeric value; the documented datatype is NUMBER.
• SEVERITY_LEVEL — Severity level applied when the assignment belongs to a Constraint Set.
• EFFECTIVE_START_DATE — Date on which the assignment becomes active.
• EFFECTIVE_END_DATE — Date on which the assignment ceases to be active, supporting historical and date-effective querying.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard Who columns that provide audit lineage and are used consistently across EBS and in the seeded concurrent programs.

The business-key candidate is documented by the unique index PSB_ENTITY_ASSIGNMENT_U1 on (ENTITY_ID, ENTITY_SET_ID), while the primary key constraint PSB_ENTITY_ASSIGNMENT_PK is defined on the same two columns. The nonunique index PSB_ENTITY_ASSIGNMENT_N1 on (ENTITY_SET_ID, EFFECTIVE_START_DATE) supports the common access path of retrieving all assignments for a given set as of a given date. The index name PSB_ENTITY_ASSIGNMENT_N1, which matches the user search term, is therefore a date-effective lookup index rather than a uniqueness enforcement index.

Common Use Cases and Queries

Typical usage centers on date-effective lookups of the entities assigned to a set, ordered by priority or filtered by severity, and on audit or reconciliation reports. A representative query retrieves the active assignments for a specific set:

• SELECT ENTITY_ID, PRIORITY, SEVERITY_LEVEL FROM PSB.PSB_ENTITY_ASSIGNMENT WHERE ENTITY_SET_ID = :set_id AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND NVL(EFFECTIVE_END_DATE, SYSDATE) ORDER BY PRIORITY;
• Reporting the highest-priority entities in each parameter set, using a rank over PRIORITY partitioned by ENTITY_SET_ID.
• Identifying constraint sets whose entities carry a severity above a threshold, to prioritize remediation or validation work.
• Reconciling entity set membership against PSB_ENTITY and PSB_ENTITY_SET for data-quality checks.

Because the object supports effective dating, analytic queries should always constrain or display EFFECTIVE_START_DATE and EFFECTIVE_END_DATE to avoid returning retired memberships. The N1 index is especially effective when the query filters on ENTITY_SET_ID and a date range, since it leads with the set identifier and the effective start date.

Related Objects

The following objects are most significant in relation to PSB_ENTITY_ASSIGNMENT:

• PSB.PSB_ENTITY_SET — Referenced by ENTITY_SET_ID; the parent set definition (parameter, constraint, or allocation set).
• PSB.PSB_ENTITY — Referenced by ENTITY_ID; the rule/entity being assigned.
• PSB_ENTITY_ASSIGNMENT_U1 — Unique index on (ENTITY_ID, ENTITY_SET_ID); enforces business-key uniqueness.
• PSB_ENTITY_ASSIGNMENT_N1 — Nonunique index on (ENTITY_SET_ID, EFFECTIVE_START_DATE); drives date-effective set lookups.
• PSB_ENTITY_ASSIGNMENT_PK — Primary key constraint on (ENTITY_SET_ID, ENTITY_ID).

In the wider EBS stack, PSB entity sets and their assignments are consumed by territory and resource management logic, so integration and reporting layers that query PSB_ENTITY_SET membership should join through this table to resolve the effective assignments rather than relying on the parent set alone.