Results for “ams_p_discoverer_sql_v”

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

Overview

AMS_P_DISCOVERER_SQL_V is a public view owned by the APPS schema within the Oracle E-Business Suite Release 12.1.1 and 12.2.2 environment. It belongs to the AMS (Marketing) product family, which encompasses Oracle Marketing, campaign management, and the Oracle Discoverer integration used for ad hoc reporting and analytics within EBS. The view presents a consolidated, deduplicated listing of the workbook and worksheet identifiers persisted in the AMS_DISCOVERER_SQL table.

Functionally, the view exists to expose the distinct set of Oracle Discoverer workbook and worksheet names registered in the Marketing repository. Because AMS_DISCOVERER_SQL may hold multiple stored SQL registration rows that reference the same workbook/worksheet combination, AMS_P_DISCOVERER_SQL_V collapses those duplicates through an aggregation and grouping operation. This makes it suitable as a reference or lookup source for Discoverer-related reporting, validation, and integration logic, and as a discovery mechanism for enumerating the workbooks and worksheets known to the Marketing module. The column SOURCE_TYPE_CODE participates directly in the view's grouping, allowing the same workbook/worksheet to appear more than once when associated with differing source type classifications.

Underlying Base Objects

The view is defined over a single documented base object: the synonym AMS_DISCOVERER_SQL, which resolves to the base table holding the underlying Discoverer SQL registration records. No additional joins or referenced objects are documented for this view; it is a straightforward projection and aggregation on that table.

The relationship between the view and its base object is aggregation-derived rather than one-to-one. The view text applies MIN(DISCOVERER_SQL_ID) alongside GROUP BY WORKBOOK_OWNER_NAME, WORKBOOK_NAME, WORKSHEET_NAME, SOURCE_TYPE_CODE. Consequently, for every distinct combination of workbook owner, workbook name, worksheet name, and source type code in AMS_DISCOVERER_SQL, the view emits exactly one row. The DISCOVERER_SQL_ID surfaced is the lowest identifier present for that combination, functioning as a representative key rather than a unique primary key for the returned row.

Key Columns

• DISCOVERER_SQL_ID — The minimum Discoverer SQL identifier for the grouped workbook/worksheet combination. It serves as a representative reference back to a concrete row in AMS_DISCOVERER_SQL, though it does not uniquely identify the view row by itself.
• WORKBOOK_OWNER_NAME — The owner (schema or user) of the Oracle Discoverer workbook. Part of the grouping key.
• WORKBOOK_NAME — The name of the Discoverer workbook. Part of the grouping key.
• WORKSHEET_NAME — The name of the worksheet within the workbook. Part of the grouping key.
• SOURCE_TYPE_CODE — The source type classification associated with the workbook/worksheet registration. This column is central to the view's deduplication logic and to any query that filters or segregates Discoverer content by source category. It is the attribute users most commonly search on when narrowing results to a particular registration origin.

Common Use Cases and Queries

Typical usage centers on enumerating, validating, or filtering registered Discoverer workbooks and worksheets from within Marketing and adjacent modules, often constrained by SOURCE_TYPE_CODE.

• Listing all distinct workbooks and worksheets:

  SELECT WORKBOOK_OWNER_NAME, WORKBOOK_NAME, WORKSHEET_NAME, SOURCE_TYPE_CODE
  FROM   APPS.AMS_P_DISCOVERER_SQL_V;

• Filtering by source type to isolate a registration category:

  SELECT WORKBOOK_NAME, WORKSHEET_NAME
  FROM   APPS.AMS_P_DISCOVERER_SQL_V
  WHERE  SOURCE_TYPE_CODE = :p_source_type_code;

• Retrieving the representative Discoverer SQL ID for a given workbook/worksheet:

  SELECT DISCOVERER_SQL_ID
  FROM   APPS.AMS_P_DISCOVERER_SQL_V
  WHERE  WORKBOOK_NAME = :p_workbook
  AND    WORKSHEET_NAME = :p_worksheet;

Because the view already deduplicates on the grouped columns, consumers avoid duplicate workbook/worksheet rows that would otherwise appear when querying AMS_DISCOVERER_SQL directly. Queries should always qualify the object with the APPS schema in a multi-schema EBS environment.