Search Results eng_auto_number_ecn




Overview
Explore in AI Chat

Overview

The ENG_AUTO_NUMBER_ECN table resides in the ENG (Engineering) schema of Oracle E-Business Suite and is documented within the ETRM reference for releases 12.1.1 and 12.2.2. Its purpose is narrow but operationally significant: it holds the prefix and numbering state used to automatically generate identifiers for Engineering Change Orders (ECOs). Rather than relying on a single global sequence, Oracle Engineering maintains per-user, per-organization numbering records so that different engineering groups, organizations, or change types can consume number ranges independently and without collision.

The table is defined with a composite primary key, ENG_AUTO_NUMBER_ECN_PK, composed of USER_ID and ORGANIZATION_ID. It also carries a unique business-key index, ENG_AUTO_NUMBER_ECN_U1, over USER_ID, ORGANIZATION_ID, CHANGE_TYPE_ID, and ZD_EDITION_NAME. Foreign key relationships show that USER_ID references FND_USER, tying each numbering record to a specific application user. Under the heuristic Data Vault classification supplied in the metadata, this object is described as satellite-leaning; that is, it is best modeled as a satellite attached to a user/organization hub, holding descriptive numbering attributes rather than participating as a true hub or link in a dimensional vault design.

Key Information Stored

The table contains 17 documented columns. The most consequential are:

  • USER_ID — Part of the composite primary key; identifies the FND_USER whose numbering record is being tracked.
  • ORGANIZATION_ID — The second component of the primary key; scopes numbering to a specific inventory/engineering organization.
  • CHANGE_TYPE_ID — Identifies the ECO change type; part of the unique index ENG_AUTO_NUMBER_ECN_U1.
  • ZD_EDITION_NAME — Edition attribute participating in the unique business key.
  • ALPHA_PREFIX — The alphabetic prefix prepended to generated ECO numbers.
  • NEXT_AVAILABLE_NUMBER — The next integer to be assigned when an ECO is auto-numbered.
  • AUTO_NUMBER_ECN_ID — Surrogate identifier column for the auto-numbering record.
  • ACTION_ID — References the action context associated with the numbering rule.
  • REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrent program and request audit columns recording which batch process last touched the row.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard WHO (workflow/history) audit columns present on nearly all EBS transactional tables.

The surrogate key documented is AUTO_NUMBER_ECN_ID, while the genuine business-key candidates are those captured in the unique indexes: the primary key pair (USER_ID, ORGANIZATION_ID) and the extended unique set including CHANGE_TYPE_ID and ZD_EDITION_NAME.

Common Use Cases and Queries

Typical scenarios include pre-seeding number ranges for engineering users, auditing which ECO numbers are imminent, and diagnosing duplicate-number errors when concurrent users across organizations attempt to auto-number ECOs. A simple lookup of the pending number for a given user and organization follows this pattern:

  • SELECT ALPHA_PREFIX, NEXT_AVAILABLE_NUMBER FROM ENG.ENG_AUTO_NUMBER_ECN WHERE USER_ID = :p_user_id AND ORGANIZATION_ID = :p_org_id;
  • Reporting on assignment gaps: SELECT USER_ID, ORGANIZATION_ID, ALPHA_PREFIX, NEXT_AVAILABLE_NUMBER FROM ENG.ENG_AUTO_NUMBER_ECN ORDER BY USER_ID, ORGANIZATION_ID;
  • Joining to FND_USER to resolve the user name: SELECT u.user_name, a.alpha_prefix, a.next_available_number FROM eng.eng_auto_number_ecn a, applsys.fnd_user u WHERE a.user_id = u.user_id;

Because each row tracks the "next available" value, a common operational concern is manually advancing or resetting NEXT_AVAILABLE_NUMBER after ECO numbering corrections. Auditing REQUEST_ID and PROGRAM_ID helps determine whether a concurrent program or a direct DML change last altered the row.

Related Objects

  • FND_USER — Referenced by ENG_AUTO_NUMBER_ECN.USER_ID; supplies the user identity behind each numbering record.
  • ENG_ENGINEERING_CHANGES (ECO header) — Consumes the prefix and next available number when an ECO is created with auto-numbering.
  • ENG_ECO_CHANGE_TYPES / CHANGE_TYPE_ID reference — Qualifies the numbering scope captured in the unique index.
  • ENG_ECO_DEFINITIONS / action definitions — Provide the ACTION_ID context used in auto-numbering rules.
  • FND_CONCURRENT_REQUESTS — Joined via REQUEST_ID to trace which concurrent process last updated numbering state.
  • FND_APPLICATION and FND_CONCURRENT_PROGRAMS — Joined via PROGRAM_APPLICATION_ID and PROGRAM_ID for program identification.

Together these objects form the supporting framework around ECO auto-numbering, with ENG_AUTO_NUMBER_ECN acting as the persistent counter store keyed to user and organization.