Results for “ams_p_cells_v”

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

Overview

AMS_P_CELLS_V is a public Oracle E-Business Suite view owned by the APPS schema and classified under the AMS (Marketing) product family. It is a denormalized reporting and integration interface over the marketing "cell" entity — the container that groups customers, prospects, or accounts into a targeted marketing list. In Oracle EBS 12.1.1 and 12.2.2 the view carries a VALID status and is exposed as a read-only layer that application code, concurrent programs, OBIEE extracts, and custom SQL can query without joining the underlying transactional tables directly.

The view does not own data; it presents a curated projection of cell attributes together with a resolved owner name. This makes it suitable for both operational lookups (for example, filtering cells for a campaign) and bulk data extraction where a stable column list is required.

Underlying Base Objects

Per the documented view text, AMS_P_CELLS_V is defined over two underlying views:

• AMS_CELLS_VL — the multi-language (translated) cell view, which supplies the core cell record including name, description, code, ownership, status, segmentation flags, and audit columns.
• AMS_JTF_RS_EMP_V — the JTF resource employee view, used solely to resolve the cell owner's display name.

The join condition is B.OWNER_ID = U.RESOURCE_ID (aliases B and U respectively). Because it is an inner join, any cell whose OWNER_ID has no matching resource record is excluded from the result set — an important behavioral characteristic when reconciling counts against AMS_CELLS_VL. The view is therefore a convenience wrapper rather than a data source of record; DML is not permitted against it.

Key Columns

The projection retains the full set of cell attributes from AMS_CELLS_VL plus one derived column. The most significant columns are:

• CELL_ID / CELL_CODE — surrogate and user-facing identifiers for the marketing cell.
• CELL_NAME, DESCRIPTION — the translated name and free-text description of the cell.
• OWNER_ID and USER_NAME_OWNER — the owning resource's identifier and the resolved FULL_NAME from the employee resource view. USER_NAME_OWNER is the only column not sourced from AMS_CELLS_VL.
• SEL_TYPE — the selection type of the cell, indicating how members are identified or selected; this is the field most frequently searched by users and is central to campaign targeting logic.
• MARKET_SEGMENT_FLAG, ENABLED_FLAG, ORIGINAL_SIZE — indicate whether the cell is used for segmentation, whether it is active, and its recorded size at creation/build time.
• PARENT_CELL_ID — supports hierarchical nesting of cells.
• STATUS_CODE, USER_STATUS_ID, STATUS_DATE — lifecycle status information for the cell record.
• COUNTRY — the country associated with the cell.
• ROW_ID, OBJECT_VERSION_NUMBER, and the standard WHO audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) — support concurrency, auditing, and incremental extraction.

Common Use Cases and Queries

Typical usages include listing active cells for a campaign, identifying cells by selection type, and extracting cell data with owner names for downstream marketing analytics. A representative query filtering on the search term is:

SELECT cell_id, cell_name, cell_code, sel_type, owner_id, user_name_owner FROM apps.ams_p_cells_v WHERE enabled_flag = 'Y' AND sel_type = :p_sel_type ORDER BY cell_name;

To locate cells by owner name:

SELECT cell_id, cell_code, cell_name, user_name_owner FROM apps.ams_p_cells_v WHERE user_name_owner LIKE :p_owner_name ORDER BY user_name_owner, cell_name;

For incremental interfaces, query the audit columns:

SELECT cell_id, cell_name, last_update_date, last_updated_by FROM apps.ams_p_cells_v WHERE last_update_date >= :p_since_date ORDER BY last_update_date;

A hierarchical listing of child cells under a parent uses PARENT_CELL_ID:

SELECT parent_cell_id, cell_id, cell_name, sel_type FROM apps.ams_p_cells_v WHERE parent_cell_id = :p_parent_id;

Because the view performs an inner join to the resource employee view, reports requiring orphaned cells or complete row counts should query AMS_CELLS_VL instead.