Results for “psb_entity_assignment”

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

Overview

PSB_ENTITY_ASSIGNMENT is a table within the Public Sector Budgeting (PSB) module of Oracle E-Business Suite. The ETRM metadata classifies PSB as obsolete, and the object carries the annotation "Not implemented in this database," meaning the table is documented for schema completeness but is not present in the reference environment. Historically, the table served to associate budgeting parameters, constraints, and allocation rules with the entity sets against which they are evaluated. It functions as a membership or cross-reference construct: each row maps a single entity to a single entity set, and the row optionally carries the priority, severity, and date-range attributes that govern how the assignment behaves during budget processing.

The metadata supplies a heuristic Data Vault classification of link, derived from the foreign key topology. Under that modeling suggestion, PSB_ENTITY_ASSIGNMENT should be treated as a many-to-many resolution table sitting between PSB_ENTITY_SET and PSB_ENTITY, rather than as a hub or a descriptive satellite. The heuristic is advisory only and reflects the two outgoing foreign keys that constitute the primary key.

Key Information Stored

The table is defined with eleven columns in the documented 12.1.1 physical schema. The most significant are:

• ENTITY_SET_ID — Foreign key to PSB_ENTITY_SET; identifies the set to which the entity is assigned. Part of the composite primary key and of the unique index.
• ENTITY_ID — Foreign key to PSB_ENTITY; identifies the assigned entity. Also part of the primary key and unique index.
• PRIORITY — Ordering value controlling the sequence in which assignments are evaluated when multiple rules or constraints apply.
• SEVERITY_LEVEL — Classification governing the consequence or weight when the associated constraint or parameter is triggered.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Date-range columns delimiting the period during which the assignment is active.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard EBS WHO columns capturing audit and concurrency information.

The surrogate primary key is PSB_ENTITY_ASSIGNMENT_PK, defined over (ENTITY_SET_ID, ENTITY_ID). A second unique index, PSB_ENTITY_ASSIGNMENT_U1, is defined over (ENTITY_ID, ENTITY_SET_ID) — the same column pair in reversed order. This duplicate business-key index is a documented design artifact and does not introduce an additional candidate key beyond the primary key.

Common Use Cases and Queries

Reporting against PSB_ENTITY_ASSIGNMENT centers on resolving which entities belong to which sets, and under what priority or effective dating. A typical membership listing joins both parent tables:

• Enumerate entities within a target set: SELECT a.ENTITY_ID FROM PSB_ENTITY_ASSIGNMENT a WHERE a.ENTITY_SET_ID = :set_id, optionally filtered by TRUNC(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE.
• Identify orphaned or dangling assignments by outer-joining to PSB_ENTITY on a.ENTITY_ID = b.ENTITY_ID and testing for null primary keys.
• Rank conflicting assignments: ORDER BY a.PRIORITY, a.SEVERITY_LEVEL to reproduce evaluation order during budget rule processing.
• Audit recently changed assignments using LAST_UPDATE_DATE and LAST_UPDATED_BY, which is standard practice for ETRM and reconciliation reporting.

Because the module is obsolete, these queries are principally relevant to historical data extraction, migration validation, or reverse-engineering of legacy Public Sector Budgeting configurations.

Related Objects

The two immediate parents are the most significant related objects, and both are documented foreign key targets:

• PSB_ENTITY_SET — joined on PSB_ENTITY_ASSIGNMENT.ENTITY_SET_ID = PSB_ENTITY_SET.ENTITY_SET_ID; supplies the set definition.
• PSB_ENTITY — joined on PSB_ENTITY_ASSIGNMENT.ENTITY_ID = PSB_ENTITY.ENTITY_ID; supplies the entity definition.

Beyond these, PSB parameter, constraint, and allocation rule tables in the same module depend transitively on the assignment rows, since the assignment determines the scope over which those definitions apply. Standard EBS WHO audit columns also position the table alongside FND user and responsibility references via LAST_UPDATED_BY and LAST_UPDATE_LOGIN.