Search Results pv_channel_types_u2

Overview

PV.PV_CHANNEL_TYPES is a reference and setup table within the Oracle E-Business Suite Channel Revenue Management (formerly Trade Management) product family, owned by the PV schema. Its purpose is to define and classify sales channels as either direct or indirect, based on configurable values. The table stores one row per channel type, tied to a lookup type and lookup code drawn from the Oracle Applications lookup framework. This design allows implementations to map Flexible Lookups to a channel classification and to persist the direct/indirect designation used downstream by opportunity and channel revenue logic.

The object resides in the APPS_TS_TX_DATA tablespace, is classified as VALID, and is registered under FND Design Data as PV.PV_CHANNEL_TYPES. Because it is a definitional reference table with a single primary key and a small number of characteristic attributes, a Data Vault modeling heuristic would suggest classifying it as a satellite (or a small reference hub) rather than a link table, since it carries descriptive attributes about channel types but does not resolve a many-to-many relationship between independent business entities. The mined Data Vault classification is "standalone," confirming that the table holds its own business identity without participating in FK-based relationships to other business hubs.

Key Information Stored

The table contains 32 documented columns, of which the following are the most operationally significant:

• CHANNEL_TYPE_ID (NUMBER) — the surrogate primary key of the table, defined by the constraint PV_CHANNEL_TYPES_PK.
• CHANNEL_LOOKUP_TYPE (VARCHAR2(30)) — the lookup type that categorizes the channel value.
• CHANNEL_LOOKUP_CODE (VARCHAR2(30)) — the lookup code identifying the specific channel.
• INDIRECT_CHANNEL_FLAG (VARCHAR2) — the flag that identifies whether the channel is indirect or direct for an opportunity. This is the central business attribute of the table.
• RANK — an ordering/positioning column used to sequence channel types.
• SECURITY_GROUP_ID (NUMBER) — the multi-org security reference, with a foreign key to FND_SECURITY_GROUPS.
• OBJECT_VERSION_NUMBER (NUMBER(15)) — the optimistic locking/versioning column required by the OAF/ADF framework.
• Standard Who columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN.
• Concurrent program columns — REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 (VARCHAR2(150)) — the Descriptive Flexfield (DFF) segment columns.

Two unique indexes enforce business identity. PV_CHANNEL_TYPES_U1 is unique on CHANNEL_TYPE_ID (the primary key surrogate). PV_CHANNEL_TYPES_U2 is unique on the composite of CHANNEL_LOOKUP_CODE and CHANNEL_LOOKUP_TYPE, which is the true business-key candidate. Because the user searched for "pv_channel_types_u2," that unique index — the composite lookup code/type pair — is the relevant constraint for natural-key lookups.

Common Use Cases and Queries

Typical uses include resolving a lookup code to its direct/indirect classification during opportunity or channel revenue processing, populating LOVs for channel selection, and reporting on channel mix.

-- Resolve a channel's classification
SELECT channel_type_id, channel_lookup_code, indirect_channel_flag
FROM   pv.pv_channel_types
WHERE  channel_lookup_type = :p_lookup_type
AND    channel_lookup_code = :p_lookup_code;

-- List all indirect channels
SELECT channel_lookup_type, channel_lookup_code
FROM   pv.pv_channel_types
WHERE  indirect_channel_flag = 'Y'
ORDER  BY rank;

The U2 index makes the first query an efficient unique-index access. Because the Direct/Indirect flag can change independently of the lookup definition, trend reporting should always join back to the current row rather than caching historical values.

Related Objects

• FND_SECURITY_GROUPS — referenced via the SECURITY_GROUP_ID foreign key. Join on FND_SECURITY_GROUPS.SECURITY_GROUP_ID = PV_CHANNEL_TYPES.SECURITY_GROUP_ID for multi-org security resolution.
• FND_LOOKUP_VALUES — holds the display meaning for CHANNEL_LOOKUP_TYPE and CHANNEL_LOOKUP_CODE; joined as FND_LOOKUP_VALUES.LOOKUP_TYPE = PV_CHANNEL_TYPES.CHANNEL_LOOKUP_TYPE AND FND_LOOKUP_VALUES.LOOKUP_CODE = PV_CHANNEL_TYPES.CHANNEL_LOOKUP_CODE.
• PV_CHANNEL_TYPES_PK — the primary key constraint on CHANNEL_TYPE_ID.
• PV_CHANNEL_TYPES_U1 / U2 — the unique indexes described above.
• PV_CHANNEL_CLASSES and opportunity-related PV tables — downstream channel revenue and opportunity tables that consume the Indirect flag through the PV product.
• OAF/ADF business components — the generated entity objects relying on OBJECT_VERSION_NUMBER for optimistic locking.