Results for “old_column”

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

Overview

The AMS_UPGRADE table resides in the AMS (Marketing) schema and is documented within the Oracle E-Business Suite 12.1.1 and 12.2.2 ETRM repository as a table used solely during the upgrade of data from a previous release of the marketing application. It is not a transactional or operational object in the day-to-day Marketing module; rather, it functions as a transient staging and mapping structure that the upgrade driver populates and consults while transforming legacy marketing data into the current release's data model.

Because the table is populated only during an upgrade cycle, its contents are typically ephemeral. The documented physical schema contains 24 columns and carries a primary key constraint identified as AMS_UPGRADE_PK on the AMS_UPGRADE_ID column. A foreign key from SECURITY_GROUP_ID to FND_SECURITY_GROUPS ties each upgrade row to the appropriate Oracle Application Object Library security group, preserving multi-org and data-security context during the transformation.

The heuristic Data Vault classification mined from the foreign key structure is standalone. From a modeling perspective this suggests the table behaves neither as a hub nor as a link nor as a satellite in the strict Data Vault sense; it is better treated as a transient mapping or staging entity whose rows are meaningful only in the context of a specific upgrade run. Modelers should therefore avoid treating AMS_UPGRADE as a durable reference table.

Key Information Stored

The most significant columns are the surrogate primary key and the paired "old" and "new" mapping attributes that drive the upgrade transformation. The surrogate key AMS_UPGRADE_ID (backed by AMS_UPGRADE_PK) uniquely identifies each upgrade mapping row. No separate business-key unique index is documented beyond this primary key, so AMS_UPGRADE_ID is the only documented unique identifier.

• AMS_UPGRADE_ID — surrogate primary key for each upgrade mapping record.
• OLD_APPLICATION, OLD_TABLE, OLD_COLUMN — identify the legacy source application, table, and column.
• OLD_COLUMN_VALUE, OLD_COLUMN2_VALUE, OLD_COLUMN3_VALUE — the legacy values to be matched or translated.
• OLD_COLUMN2, OLD_COLUMN3 — secondary and tertiary legacy column identifiers used when a mapping involves multiple source attributes.
• NEW_APPLICATION, NEW_TABLE, NEW_COLUMN — the corresponding target application, table, and column in the current release.
• NEW_COLUMN_VALUE, NEW_COLUMN2_VALUE, NEW_COLUMN3_VALUE — the replacement values written to the target model.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, scoping the mapping to a security group.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns recording when and by whom the upgrade rows were created or modified.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the ORM layer.

Common Use Cases and Queries

This table is primarily relevant to DBAs and technical consultants performing or auditing an upgrade. Typical scenarios include verifying that all legacy values were mapped, diagnosing unmapped or orphaned rows, and reporting on the volume and nature of transformations applied.

• Counting rows created during an upgrade run to gauge mapping volume: SELECT COUNT(*) FROM ams.ams_upgrade;
• Reviewing a specific mapping: SELECT old_table, old_column, old_column_value, new_table, new_column, new_column_value FROM ams.ams_upgrade WHERE old_column_value = :value;
• Checking security-group scoping: SELECT u.ams_upgrade_id, s.security_group_name FROM ams.ams_upgrade u, fnd_security_groups s WHERE u.security_group_id = s.security_group_id;
• Auditing recency of the mapping data using the WHO columns, e.g. filtering on LAST_UPDATE_DATE.

Because the table serves only the upgrade process, it should generally not appear in production reporting or operational queries after the upgrade completes.

Related Objects

• FND_SECURITY_GROUPS — referenced through AMS_UPGRADE.SECURITY_GROUP_ID to FND_SECURITY_GROUPS.SECURITY_GROUP_ID, providing security-group context.
• AMS_UPGRADE_PK — the primary key constraint enforcing uniqueness on AMS_UPGRADE_ID.
• The AMS Marketing transactional tables (such as campaign and offer tables) that this staging data ultimately populates during the upgrade.
• The upgrade driver scripts and concurrent programs within the Marketing module that read and write AMS_UPGRADE during the release migration.
• Standard Oracle Application Object Library audit infrastructure that supplies the WHO columns and OBJECT_VERSION_NUMBER semantics.