Search Results ams_generated_codes_pk

Overview

AMS_GENERATED_CODES is an Oracle Marketing (AMS) application table that resides in the AMS schema. Its documented purpose is to help generate non-conflicting source codes for Campaigns and Events. In Oracle EBS, source codes are the alphanumeric identifiers used to attribute inbound responses, leads, and orders back to the marketing activity that produced them. Because multiple campaigns, events, and concurrent users may attempt to reserve similar codes, this table provides the persistence layer that guarantees uniqueness across the marketing source-code namespace.

From a data-modeling perspective, the metadata's heuristic Data Vault classification places this object as standalone, suggesting it functions as a hub-like reference table whose identity is anchored by its own primary key rather than by dependent transactional lines. It is not classified as a link or satellite, so it does not primarily model relationships between other hubs or hold descriptive history.

Key Information Stored

The table is documented in ETRM 12.2.2 with eleven columns. The most significant are listed below.

• GEN_CODE_ID — the surrogate primary key, defined by the constraint AMS_GENERATED_CODES_PK. It uniquely identifies each generated source-code record.
• SCODE_CHAR_ELEMENT — the character portion of the generated source code. This column is the business-key candidate, enforced by unique index AMS_GENERATED_CODES_U1.
• SCODE_NUMBER_ELEMENT — the numeric portion of the generated source code, combined with the character element to form the complete code.
• ARC_SOURCE_CODE_FOR — indicates the business context for which the source code was generated, such as Campaign or Event.
• SECURITY_GROUP_ID — the operating unit / security group that owns the record; it references FND_SECURITY_GROUPS and enforces multi-org data isolation.
• OBJECT_VERSION_NUMBER — the optimistic locking column used by the Oracle Applications Framework to detect concurrent updates.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns recording who created and last modified each row and through which login session.

Common Use Cases and Queries

The principal use case is the automated generation of unique marketing source codes. When a user defines a new campaign or event, the AMS code-generation logic consults this table to derive a previously unused character and numeric combination, then inserts the reserved code. Reports frequently join this table to campaign and event headers to display the assigned source code.

A typical lookup query retrieves the code components for a given context:

• SELECT gen_code_id, scode_char_element, scode_number_element, arc_source_code_for FROM ams.ams_generated_codes WHERE arc_source_code_for = :context;
• Duplicate detection: SELECT scode_char_element FROM ams.ams_generated_codes WHERE scode_char_element = :candidate;
• Security-scoped reporting: join SECURITY_GROUP_ID to FND_SECURITY_GROUPS to restrict output to the operating unit.

Audit and reconciliation reports use the WHO columns to trace when codes were reserved, and the OBJECT_VERSION_NUMBER to detect conflicting reservations in concurrent integrations.

Related Objects

The documented FK references FND_SECURITY_GROUPS via SECURITY_GROUP_ID, which is the primary relationship to honor in any query. Functionally, the generated codes are consumed by the Campaign and Event entities in the AMS schema (for example, the campaign and event header tables that store ARC source-code attributes), and by Oracle Marketing and Oracle Trade Management response-attribution logic. Integration interfaces that load leads, responses, or orders from external systems resolve the source code back through this table to identify the originating campaign or event. Reporting layers in Oracle Marketing Analytics also depend on the code sequence maintained here. Because the metadata classifies the object as standalone, no additional foreign keys are documented; joins beyond FND_SECURITY_GROUPS are conventionally driven by the ARC_SOURCE_CODE_FOR context and the code elements rather than by enforced constraints.