Results for “list_sql_string”

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

Overview

AMS_CELLS_SEL_ALL_V is a Marketing (AMS) module view owned by the APPS schema and shipped with status VALID in Oracle E-Business Suite 12.1.1 and 12.2.2. The ETRM repository classifies it as a "Segment selection view definition," indicating that its purpose is to unify the different mechanisms by which a marketing cell — that is, a segment of a list or audience — obtains its member records. Rather than acting as a stored base table, the view is a reporting and integration surface that consolidates cell header attributes with the source definition used to populate the segment.

The view is of particular interest to developers and analysts searching on the column cell_name, because it exposes that attribute directly alongside the cell identifier, selection type, and the underlying SQL string used to resolve the segment. This makes it possible to trace a marketing segment by its user-facing name and to inspect the query logic responsible for generating its membership, without navigating the transactional AMS forms.

Underlying Base Objects

According to the documented view metadata for 12.2.2, AMS_CELLS_SEL_ALL_V is defined over three referenced objects:

• AMS_CELLS_VL (VIEW) — the multi-language view over the marketing cells entity, supplying the core cell attributes such as CELL_ID, CELL_NAME, DESCRIPTION, ENABLED_FLAG, and SEL_TYPE.
• AMS_ACT_DISCOVERER_ALL (SYNONYM) — the activity discoverer object that holds the discoverer workbook, worksheet, and SQL identifier information used when a segment is defined through an Oracle Discoverer worksheet.
• AMS_LIST_QUERIES_ALL (SYNONYM) — the list query repository that stores the generated SQL text for segments based on list queries.

Internally the view is constructed as a UNION ALL of at least two branches. The first branch joins AMS_CELLS_VL to AMS_ACT_DISCOVERER_ALL where the discoverer record is used by a cell (ARC_ACT_DISCOVERER_USED_BY = 'CELL') and the cell selection type equals 'DIWB' (Discoverer workbook). The second branch handles cells whose membership derives from a list query. A further outer projection joins to the list query source to populate LIST_SQL_STRING. This UNION ALL design is what gives the view its "SEL_ALL" name: it presents every segment selection mechanism through one common column list.

Key Columns

• CELL_ID / CELL_NAME / CELL_CODE — the primary identity and user-facing name of the marketing cell. CELL_NAME is the attribute most frequently used for lookups and joins to reporting queries.
• SEL_TYPE — the selection type discriminator that determines which branch of the UNION supplied the row (for example, 'DIWB' for Discoverer-based segments).
• LIST_QUERY_ID / LIST_SQL_STRING / LIST_QUERY_VERSION_NUMBER — the identifier, resolved SQL text, and version of the list query driving segment membership.
• DISCOVERER_SQL_ID, WORKBOOK_OWNER, WORKBOOK_NAME, WORKSHEET_NAME, ACTIVITY_DISCOVERER_ID — Discoverer metadata describing the workbook and worksheet used by Discoverer-based segments.
• SOURCE_OBJECT_NAME — identifies the object from which the segment selection originated.
• PARENT_CELL_ID — supports hierarchical or nested cell relationships.
• OWNER_ID, USER_STATUS_ID, STATUS_CODE, STATUS_DATE, ENABLED_FLAG, MARKET_SEGMENT_FLAG — ownership, lifecycle, and activation attributes for the cell.
• ORIGINAL_SIZE — the recorded size of the segment at definition time.
• OBJECT_VERSION_NUMBER (and ACT_DISC_VERSION_NUMBER) — optimistic locking and version tracking columns.

Common Use Cases and Queries

The view is typically used to audit or reconcile marketing segments, to document the SQL behind a segment, and to build custom reports that list cells by name. A representative query searching by the cell name is:

• SELECT cell_id, cell_name, sel_type, list_sql_string FROM apps.ams_cells_sel_all_v WHERE cell_name LIKE 'Q1%';
• SELECT cell_name, workbook_name, worksheet_name FROM apps.ams_cells_sel_all_v WHERE sel_type = 'DIWB'; — to isolate Discoverer-based segments.
• SELECT cell_name, list_query_id, list_sql_string FROM apps.ams_cells_sel_all_v WHERE list_sql_string IS NOT NULL; — to retrieve the generated SQL for list-query segments.

Because the view draws on AMS_CELLS_VL, it respects the language installations configured in the environment, and because it unions Discoverer and list-query sources, callers should always filter on SEL_TYPE or inspect SOURCE_OBJECT_NAME to interpret which branch produced a given row.