Results for “worksheet_name”

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

Overview

AMS_DISCOVERER_SQL is a Marketing (AMS) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that persists the SQL statements generated and saved by the Oracle Discoverer tool. Each row represents a discrete SQL fragment associated with a specific worksheet or workbook authored in Discoverer. Within EBS, the table functions as the bridge between ad hoc analytical definitions created in Discoverer and the Marketing application's automated processing, most notably target list generation and cell definition logic.

From a Data Vault modeling perspective, the metadata's heuristic classification is hub-leaning. This suggests AMS_DISCOVERER_SQL may be treated as a hub entity keyed on DISCOVERER_SQL_ID, with its descriptive attributes (workbook, worksheet, SQL string) resembling satellite payload. This classification should be regarded as a modeling suggestion rather than a normative constraint, since the table carries both a surrogate key and a strong natural business key.

Key Information Stored

AMS_DISCOVERER_SQL contains 14 documented columns. The most operationally significant are listed below.

• DISCOVERER_SQL_ID — Surrogate primary key, enforced by AMS_DISCOVERER_SQL_PK and reinforced by unique index AMS_DISCOVERER_SQL_U1.
• WORKBOOK_OWNER_NAME, WORKBOOK_NAME, WORKSHEET_NAME, SEQUENCE_ORDER — The composite business key enforced by unique index AMS_DISCOVERER_SQL_U2. Together these identify the owning Discoverer schema, the workbook, the worksheet, and the ordinal position of the SQL fragment within the worksheet.
• SQL_STRING — The actual SQL statement text saved into the database; the payload that downstream target list and cell definition processing consumes.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, supporting Multi-Org / security-group access control.
• SOURCE_TYPE_CODE — Classifies the origin or type of the SQL record.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by Oracle Application Framework / OAF components.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard "Who" audit columns maintained by the concurrent manager and OA framework.

Common Use Cases and Queries

The primary use case is diagnostics: identifying which SQL a Discoverer worksheet contributes to a target list or cell definition. A typical query retrieves the SQL for a workbook/worksheet pair in sequence order:

• SELECT sql_string FROM ams.ams_discoverer_sql WHERE workbook_owner_name = :owner AND workbook_name = :wb AND worksheet_name = :ws AND sequence_order = :seq;

Reverse lookup from target list actions is equally common, joining through INCL_OBJECT_ID:

• SELECT s.sql_string FROM ams_list_select_actions a, ams.ams_discoverer_sql s WHERE a.incl_object_id = s.discoverer_sql_id;

Reporting scenarios include inventory of all Saved SQL by workbook, detection of duplicate or stale fragments for cleanup, and per-security-group auditing. Because SQL_STRING can be lengthy, reports should truncate output or use DBMS_LOB-aware tooling where the column is stored as CLOB.

Related Objects

The following objects are the most significant by dependency and join relevance.

• AMS_ACT_DISCOVERER_ALL — References AMS_DISCOVERER_SQL via DISCOVERER_SQL_ID.
• AMS_LIST_SELECT_ACTIONS — References this table via INCL_OBJECT_ID; the join that links target list selection actions to their saved Discoverer SQL.
• FND_SECURITY_GROUPS — Parent of SECURITY_GROUP_ID.
• AMS_DISCOVERER_SQL_PK / AMS_DISCOVERER_SQL_U1 / AMS_DISCOVERER_SQL_U2 — The database constraints enforcing row identity and business-key uniqueness.
• Marketing target list generation and cell definition concurrent programs — the runtime consumers of SQL_STRING.

Together these objects describe the complete path from a Discoverer workbook definition to executed Marketing target list selection.