Results for “country_id”

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

Overview

AMV_STOCKS is a table owned by the AMV schema, which supports the Marketing Encyclopedia System (MES) module in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to store stock information for all companies listed on different stock exchanges. In practice this object functions as a reference and lookup repository for securities data that is surfaced through MES-based content and profile components, allowing stock-related attributes to be attached to companies and marketing records.

The table is documented with a status of VALID and carries a heuristic Data Vault classification of standalone. This classification is a modeling suggestion rather than a physical design claim: the table is mined as a single, self-contained entity without participating link tables, so it behaves most like a hub holding a stable business key, with descriptive attributes treated as embedded satellite columns rather than a separately normalized satellite structure.

Key Information Stored

The physical schema is documented with 19 columns. The most significant are:

• STOCK_ID — the surrogate primary key, enforced by the AMV_STOCKS_PK constraint. All foreign references to this table resolve through this column.
• STOCK_RIC — the Reuters Instrument Code. This is the documented business-key candidate, enforced by the unique constraint AMV_STOCKS_UK1, and uniquely identifies a listed instrument.
• STOCK_SYMBOL — the exchange ticker symbol used to identify the security in market data feeds.
• STOCK_TYPE and ISSUE_TYPE — classification of the instrument, such as common stock, preferred stock, or other issue categories.
• STOCK_DESC — the descriptive name of the stock or issuing company.
• EXCHANGE — the exchange on which the security is listed.
• CURRENCY_CODE — the currency in which the instrument is quoted.
• COUNTRY_ID — the country associated with the listing or issuer.
• INDUSTRY_CLASS — an industry classification code used for segmentation and filtering.
• OBSOLETE_FLAG — indicates whether the stock record is still active or has been retired from use.
• SECURITY_GROUP_ID — the foreign key to FND_SECURITY_GROUPS, providing multi-tenant data isolation. This is the only documented foreign key.
• ZD_EDITION_NAME — an editioning column used with the Online Patching architecture introduced in EBS 12.2. It participates in the unique index AMV_STOCKS_U1 (STOCK_ID, ZD_EDITION_NAME), which distinguishes records by edition for patching support.

The remaining columns are standard WHO audit attributes: OBJECT_VERSION_NUMBER, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN.

Common Use Cases and Queries

The principal use case is resolving stock references in MES content, dashboards, and marketing profiles. Because STOCK_RIC is a unique business key, lookups commonly resolve a ticker to its internal identifier:

• Symbol resolution: SELECT stock_id, stock_desc, exchange FROM amv_stocks WHERE stock_symbol = :symbol AND NVL(obsolete_flag,'N') = 'N';
• Qualified unique lookup: SELECT stock_id FROM amv_stocks WHERE stock_ric = :ric; — this path uses the AMV_STOCKS_UK1 unique constraint.
• Exchange and country roll-ups: grouping the active population by exchange, country, currency, or industry class for coverage reporting.
• Multi-tenant filtering: joining or restricting by security_group_id to enforce data-level access, typically paired with a subquery against FND_SECURITY_GROUPS.
• Edition-aware queries in 12.2: in the patching edition, queries may filter ZD_EDITION_NAME to the running edition; in the runtime edition this column is typically NULL and can be ignored.

Because the table is a reference set rather than a transactional table, reporting is almost entirely read-only and is normally joined to the MES content tables that carry STOCK_ID.

Related Objects

• FND_SECURITY_GROUPS — the only documented foreign key relationship, joined as amv_stocks.security_group_id = fnd_security_groups.security_group_id. It enforces row-level visibility.
• AMV_STOCKS_PK — the primary key constraint on STOCK_ID, the target for all incoming foreign keys.
• AMV_STOCKS_UK1 — the unique constraint on STOCK_RIC, providing the alternate business-key access path.
• AMV_STOCKS_U1 — the edition-aware unique index on (STOCK_ID, ZD_EDITION_NAME), created to support the 12.2 online patching model.
• Other AMV content tables — MES store and content objects that store STOCK_ID as a foreign key to associate stock data with marketing encyclopedia entries.
• FND tables and the MES region/attribute APIs — the AMV administrative and content APIs that surface stock fields in the Marketing Encyclopedia user interface.

Because no child link tables are documented, the object remains a stable reference dimension, which simplifies joins but requires care to keep ticker and RIC values synchronized with external market data sources.