Search Results ams_cells_vl




Overview
Explore in AI Chat

Overview

AMS_CELLS_VL is a bilingual (MLS) view owned by the APPS schema in Oracle E-Business Suite, belonging to the AMS – Marketing product family. In ETRM terms it is documented as a VALID database view whose stated purpose is to "return the cells (segment) information." Within the Oracle Marketing data model, a "cell" represents a reusable segment definition — a stored set of selection criteria that identifies a group of customers, prospects, or contacts. The view provides a single, translatable projection of cell header attributes, joining the language-independent base table with its translation table so that descriptive text is returned in the session's current language.

Because the object is a view rather than a table, it carries no storage of its own and is read-only from the perspective of the consumer. It is the standard access point for reports, concurrent programs, and integrations that need to enumerate or describe marketing cells without addressing the underlying _B and _TL tables directly. The _VL suffix denotes a "view, logical" MLS construct, in which translatable columns are resolved automatically using USERENV('LANG').

Underlying Base Objects

The documented view text is defined over two base objects, both referenced through synonyms in the APPS schema:

  • AMS_CELLS_ALL_B — the language-independent base table holding the operational attributes of each cell, including its identifier, code, ownership, sizing, parent relationship, and status columns. It supplies the B.ROWID pseudo-column exposed as ROW_ID, together with all non-translatable columns.
  • AMS_CELLS_ALL_TL — the translation table holding language-specific text for each cell. It supplies CELL_NAME and DESCRIPTION.

The join predicate is B.CELL_ID = T.CELL_ID AND T.LANGUAGE = USERENV('LANG'), so exactly one translation row per cell is returned for the session language. The _ALL naming convention indicates the base table is not partitioned by operating unit in the older org-specific sense; the view itself exposes no ORG_ID column, and the documented column list confirms it is a global, non-secured projection.

Key Columns

  • ROW_ID — the ROWID of the base row in AMS_CELLS_ALL_B; used by Oracle Forms-style update logic.
  • CELL_ID — primary key of the cell and the join key between the _B and _TL tables.
  • CELL_NAME, DESCRIPTION — translatable text sourced from AMS_CELLS_ALL_TL.
  • CELL_CODE — the user-visible short code for the cell.
  • OWNER_ID — identifier of the owning application user or resource.
  • MARKET_SEGMENT_FLAG — indicates whether the cell participates as a marketing segment.
  • ENABLED_FLAG — standard EBS Yes/No flag controlling active status.
  • ORIGINAL_SIZE — recorded size of the segment population.
  • PARENT_CELL_ID — self-referencing parent, supporting hierarchical cell structures and the _ALL naming convention.
  • OBJECT_VERSION_NUMBER — optimistic locking column used by the underlying framework.
  • SEL_TYPE, STATUS_CODE, STATUS_DATE, USER_STATUS_ID — selection type and workflow/status tracking attributes.
  • COUNTRY — country qualifier associated with the cell.
  • Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

This column set is identical across Oracle EBS 12.1.1 and 12.2.2, as documented in ETRM 12.2.2.

Common Use Cases and Queries

Typical uses include LOV queries for cells in marketing setup forms, listings for segment administration, and extraction of cell metadata for integration with campaign and list-management processes. Because the view filters on the session language, a client querying through a non-base language receives the appropriate translated name and description automatically.

List enabled cells with their parent references:

  • SELECT cell_id, cell_code, cell_name, parent_cell_id, original_size FROM ams_cells_vl WHERE enabled_flag = 'Y' ORDER BY cell_name;

Resolve child cells to their parent, using the self-join supported by PARENT_CELL_ID:

  • SELECT c.cell_name child_cell, p.cell_name parent_cell FROM ams_cells_vl c, ams_cells_vl p WHERE c.parent_cell_id = p.cell_id;

Filter by status and country for operational reporting:

  • SELECT cell_id, cell_code, cell_name, status_code, status_date FROM ams_cells_vl WHERE country = 'US' AND status_code IS NOT NULL;

Direct DML against the view is not supported; maintenance of cell definitions is performed through the Marketing application and its APIs against the base tables.