Search Results amv_c_item_types_pk

Overview

AMV_C_ITEM_TYPES is a table in the AMV (Marketing Encyclopedia System) product schema within Oracle E-Business Suite 12.1.1 and 12.2.2. It stores all item types associated with a specific channel which could be part of a matching criteria. In practical terms, the table defines the mapping between a marketing channel and the item types that are eligible to participate in that channel's matching rules, allowing the Marketing Encyclopedia System to constrain which catalog items are surfaced or evaluated within a given channel context.

From a data modeling perspective, the ETRM metadata classifies this object heuristically as satellite-leaning. This is a modeling suggestion rather than a formal Data Vault designation: the table carries descriptive attributes (item type assignments) that depend on a parent channel, and its foreign keys point outward to a channel and a security group rather than to multiple independent hubs. Analysts treating the AMV schema in a Data Vault style would typically model AMV_C_CHANNELS_B as a hub or parent reference and AMV_C_ITEM_TYPES as satellite-like detail attached to that channel.

Key Information Stored

The table contains ten documented columns in the 12.2.2 physical schema. The most significant are:

• CHANNEL_ITEM_TYPE_ID — the surrogate primary key, unique per row, enforced by AMV_C_ITEM_TYPES_PK and also indexed by AMV_C_ITEM_TYPES_U1. It uniquely identifies each channel-to-item-type association.
• CHANNEL_ID — foreign key to AMV_C_CHANNELS_B. Identifies the marketing channel to which the item type assignment belongs.
• ITEM_TYPE — the item type value that is associated with the channel and eligible for matching criteria. Together with CHANNEL_ID it forms the business key candidate enforced by the unique index AMV_C_ITEM_TYPES_U2 (CHANNEL_ID, ITEM_TYPE), preventing duplicate type assignments within a channel.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting multi-org and data security partitioning of the record.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the Oracle Applications framework to detect concurrent updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard WHO-column audit attributes recording creation and last modification metadata for the row.

Distinguishing the surrogate key from business keys matters here: CHANNEL_ITEM_TYPE_ID is a system-generated identifier and offers no business meaning, whereas the pair (CHANNEL_ID, ITEM_TYPE) is the meaningful uniqueness constraint that developers should respect in DML and de-duplication logic.

Common Use Cases and Queries

Typical scenarios include validating which item types a channel supports, listing all item type constraints for a given channel, and joining item type definitions with item master data to drive catalog matching. A common query lists item types per channel:

• SELECT c.channel_id, c.name, t.item_type FROM amv_c_item_types t, amv_c_channels_b c WHERE t.channel_id = c.channel_id;
• SELECT item_type FROM amv_c_item_types WHERE channel_id = :p_channel_id; — retrieves all eligible item types for a selected channel, frequently used when populating a channel configuration LOV.
• SELECT * FROM amv_c_item_types WHERE security_group_id = :p_org_id; — applies security group filtering for multi-org reporting.
• Reporting on channel coverage: counting item types per channel to identify channels that are under- or over-constrained by matching criteria.
• Integration and data migration scripts that seed channel-to-item-type mappings, subject to the unique constraint on (CHANNEL_ID, ITEM_TYPE).

Related Objects

The following objects are most significant in relation to AMV_C_ITEM_TYPES, based on documented foreign key and index relationships:

• AMV_C_CHANNELS_B — parent table; joined via AMV_C_ITEM_TYPES.CHANNEL_ID = AMV_C_CHANNELS_B.CHANNEL_ID. Provides channel definition and naming for reporting.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID for data security partitioning.
• AMV_C_ITEM_TYPES_PK / AMV_C_ITEM_TYPES_UK1 / AMV_C_ITEM_TYPES_U1 / AMV_C_ITEM_TYPES_U2 — the primary key and unique indexes that enforce row identity and the (CHANNEL_ID, ITEM_TYPE) business rule.
• Channel matching configuration tables in the AMV schema (such as channel matching criteria tables) that consume ITEM_TYPE values from this table when evaluating catalog matches.
• Item master related tables, typically joined on ITEM_TYPE to resolve item type code descriptions for user-facing reports.
• Oracle Applications framework audit and security objects (FND_USER, FND_SECURITY_GROUPS) referenced indirectly through the WHO columns and SECURITY_GROUP_ID.

Because the ETRM excerpt documents only the channel and security group foreign keys, integrators should confirm additional AMV-side dependencies through the actual 12.1.1/12.2.2 schema before assuming join paths beyond those listed.