Search Results ahl_user_statuses_b_pk

Overview

AHL_USER_STATUSES_B is a base (non-translated) configuration table owned by the AHL schema within Oracle E-Business Suite's Complex Maintenance Repair and Overhaul (CMRO) module. It stores the definition of user statuses — the named lifecycle states that organizations can apply to maintainable assets, work orders, and related CMRO entities so that operational condition is tracked beyond the seeded system statuses. In Oracle EBS 12.1.1 and 12.2.2 the object is documented as VALID, with the primary key AHL_USER_STATUSES_B_PK defined on USER_STATUS_ID.

The _B suffix indicates that this is the untranslated base table of a pair; the corresponding _TL table holds the language-specific status names and descriptions. This base/TL split is standard in EBS for seeded and user-defined reference data. From a Data Vault modeling perspective, the mined foreign-key structure suggests a hub-leaning classification: USER_STATUS_ID behaves as a durable business key around which descriptive and audit attributes are clustered. This is a modeling suggestion, not a physical implementation claim.

Key Information Stored

The table exposes 31 documented columns. The most significant are:

• USER_STATUS_ID — surrogate primary key and the sole business-key candidate, enforced by unique index AHL_USER_STATUSES_B_U1 and by AHL_USER_STATUSES_B_PK. It is the column referenced by the translation table.
• SYSTEM_STATUS_TYPE and SYSTEM_STATUS_CODE — the seeded system status that a user status maps to, allowing user-defined labels to be grouped under standard behavior categories.
• ACTIVE_FLAG — indicates whether the status is currently available for assignment.
• DEFAULT_FLAG — marks the status proposed by default when a new record is created.
• START_DATE_ACTIVE and END_DATE_ACTIVE — the effective date range during which the status may be applied.
• SEEDED_FLAG — distinguishes Oracle-delivered statuses from customer-defined ones.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting multi-organization access control in a shared or hosted environment.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the AHL business logic to detect concurrent updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the standard EBS "Who" audit columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard DFF (descriptive flexfield) column set available for customer-defined extensions.

Common Use Cases and Queries

Typical use is to resolve a status ID to its meaning, or to drive pick lists and workflow routing in CMRO. Because the descriptive name lives in AHL_USER_STATUSES_TL, most reporting joins both tables:

SELECT b.user_status_id,
       t.name,
       b.system_status_type,
       b.system_status_code,
       b.active_flag,
       b.default_flag
FROM   ahl_user_statuses_b  b,
       ahl_user_statuses_tl t
WHERE  b.user_status_id = t.user_status_id
AND    t.language = USERENV('LANG')
AND    b.active_flag = 'Y';

Other patterns include trimming obsolete statuses by testing START_DATE_ACTIVE/END_DATE_ACTIVE against SYSDATE, identifying customer-created statuses via SEEDED_FLAG = 'N', and auditing through LAST_UPDATE_DATE and LAST_UPDATED_BY. The ATTRIBUTE columns support ad hoc client reporting where a DFF is enabled.

Related Objects

• AHL_USER_STATUSES_TL — the multilingual companion table, joined on USER_STATUS_ID; the only object documented as referencing this table.
• FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID, linking each status row to a security group.
• AHL_USER_STATUSES_B_PK and AHL_USER_STATUSES_B_U1 — the primary key constraint and unique index on USER_STATUS_ID.
• Additionally, status definitions are consumed by downstream CMRO transaction tables, such as asset and work order entities, which carry a status column resolved against this configuration.