Search Results amv_temp_ids

Overview

AMV_TEMP_IDS is a temporary staging table owned by the APPS schema in Oracle E-Business Suite, residing within the AMV — Marketing Encyclopedia System product family. It exists in both EBS 12.1.1 and 12.2.2, and carries a VALID status in the ETRM repository. As its name implies, the table functions as a transient work area used during processing routines that need to collect, score, and stage identifier values before those values are committed to permanent application tables. It is not a transactional business entity in its own right; it is an interim repository whose rows are populated, consumed, and typically purged within a single processing cycle.

The heuristic Data Vault classification mined from the foreign key structure is standalone. This means the table is not modelled as a hub, link, or satellite in the Data Vault sense. It has no parent entity contributing a natural business key beyond the optional security group reference, and no dependents reference it. In a Data Vault design, such a table is generally modelled as a work table or staging construct rather than a durable entity, and its contents should be treated as non-authoritative when populating a warehouse.

Key Information Stored

The documented physical schema at 12.2.2 consists of five columns. The most significant are:

• ID — The surrogate primary key uniquely identifying each staged row. This is an internally generated identifier with no business meaning, and it is the column that guarantees row-level uniqueness within the temporary set.
• NUMBER_VALUE — A numeric identifier value staged for processing. This is the most likely business-key candidate, since it typically carries the source numeric identifier being resolved, matched, or ranked.
• SCORE — A computed or assigned weight used to rank, prioritise, or indicate the quality of a match associated with the staged value. Higher or lower values drive selection logic depending on the consumer process.
• OBJ_CODE — A code identifying the object type or category to which the staged value applies, allowing a single temporary table to serve multiple object classes concurrently.
• SECURITY_GROUP_ID — A foreign key to FND_SECURITY_GROUPS, enforcing the multi-tenant, organisation-level data segregation standard throughout EBS. This is the only documented foreign key and is the principal join path to the rest of the application.

No unique index or alternate key beyond the ID primary key is documented, and NUMBER_VALUE combined with OBJ_CODE and SECURITY_GROUP_ID is a reasonable composite candidate for de-duplication logic.

Common Use Cases and Queries

The table is typically consumed by concurrent programs performing search, scoring, or batch resolution within the Marketing Encyclopedia System. Practical SQL patterns include retrieving the highest-scoring entries for a given object and security group:

• SELECT id, number_value, score FROM amv_temp_ids WHERE obj_code = :obj AND security_group_id = :sg ORDER BY score DESC;
• Joining to FND_SECURITY_GROUPS to validate that staged rows belong to a permitted organisation: SELECT t.* FROM amv_temp_ids t, fnd_security_groups g WHERE t.security_group_id = g.security_group_id;
• De-duplicating candidate identifiers prior to promotion: SELECT number_value, MAX(score) FROM amv_temp_ids WHERE security_group_id = :sg GROUP BY number_value;
• Housekeeping that removes stale staging data: DELETE FROM amv_temp_ids WHERE security_group_id = :sg;

Reporting against this table should always be scoped by SECURITY_GROUP_ID, and results should not be persisted as historical fact, since rows are short-lived.

Related Objects

The most significant relationships are:

• FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID; the sole documented foreign key and the primary access-control join.
• AMV-related content and search tables in the Marketing Encyclopedia System, which consume the staged identifiers and scores produced here.
• FND concurrent program and request tables, which govern the batch jobs that populate and drain the table.
• APPS schema views and PL/SQL packages in the AMV module that encapsulate inserts, scoring, and cleanup.

Because the table is standalone, no dependents cascade from it, and it should be treated purely as an implementation detail of AMV processing.