Search Results ams_generated_codes_n2

Overview

AMS.AMS_GENERATED_CODES is a table in the Oracle E-Business Suite Advanced Marketing (AMS) schema that supports the source code generation engine. Major marketing objects in Oracle Marketing require unique source codes, and after a marketing object is created, its source code is recorded in AMS_SOURCE_CODES. The AMS_GENERATED_CODES table maintains the counter and element state that the generation engine consults so that it can produce non-conflicting source codes for Campaigns and Events. In effect, it acts as a persistence layer for the sequencing logic that guarantees uniqueness of generated codes across marketing objects.

The object is owned by AMS, carries FND Design Data reference AMS.AMS_GENERATED_CODES, and has a status of VALID in both Oracle EBS 12.1.1 and 12.2.2. It is stored in the APPS_TS_TX_DATA tablespace with PCT Free 10. From a Data Vault modeling perspective, the table is classified heuristically as standalone; it references FND_SECURITY_GROUPS via SECURITY_GROUP_ID but is not itself referenced by dependent foreign keys within the documented schema. This standalone classification reflects its role as an internal, engine-managed support table rather than a transactional hub or link.

Key Information Stored

The most significant columns are:

• GEN_CODE_ID — surrogate primary key (AMS_GENERATED_CODES_PK), a system-generated unique identifier for each generation record.
• SCODE_CHAR_ELEMENT — VARCHAR2(30) holding the source code character element; it is the business-key candidate, enforced by the unique index AMS_GENERATED_CODES_U1 (NORMAL, UNIQUE, APPS_TS_TX_IDX).
• SCODE_NUMBER_ELEMENT — the numeric element corresponding to a specific source code character element; indexed non-uniquely by AMS_GENERATED_CODES_N2.
• ARC_SOURCE_CODE_FOR — VARCHAR2(30) identifying the type of the object associated with the source code.
• OBJECT_VERSION_NUMBER — used for optimistic locking during concurrent updates.
• SECURITY_GROUP_ID — supports hosted (multi-tenant) environments and participates in the only documented foreign key.
• Standard WHO columns: LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN.

The distinction between the surrogate key and the business-key candidate is important: GEN_CODE_ID provides physical row identity, while SCODE_CHAR_ELEMENT uniquely identifies the logical generation element used by the code engine.

Common Use Cases and Queries

Typical uses include auditing code counter state, troubleshooting source code collisions during campaign or event creation, and verifying available numeric elements per character element. A standard inspection query is:

• SELECT GEN_CODE_ID, SCODE_CHAR_ELEMENT, SCODE_NUMBER_ELEMENT, ARC_SOURCE_CODE_FOR, SECURITY_GROUP_ID FROM AMS.AMS_GENERATED_CODES ORDER BY SCODE_CHAR_ELEMENT;
• Filtering by element: WHERE SCODE_CHAR_ELEMENT = :char_element to read the last used numeric counter.
• Joining to FND_SECURITY_GROUPS on SECURITY_GROUP_ID to report generation activity per hosted tenant.

Because the unique index is on SCODE_CHAR_ELEMENT, queries used for validation should target that column to detect duplicate or missing counter definitions.

Related Objects

• AMS.AMS_SOURCE_CODES — stores the source codes recorded after a marketing object is created; consumed together with the generation state.
• FND_SECURITY_GROUPS — referenced by the documented foreign key AMS.AMS_GENERATED_CODES.SECURITY_GROUP_ID.
• AMS.AMS_CAMPAIGNS_ALL and AMS.AMS_EVENTS_ALL — marketing objects whose codes are generated through this mechanism.
• AMS.AMS_GENERATED_CODES# — the supporting database object (package/shadow) referenced by the table.
• AMS.AMS_GENERATED_CODES_U1 — the unique index enforcing the business-key candidate SCODE_CHAR_ELEMENT.
• AMS.AMS_GENERATED_CODES_N2 — the non-unique index on SCODE_NUMBER_ELEMENT supporting lookup performance.