Results for “ams_geoarea_scr_v”

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

Overview

AMS_GEOAREA_SCR_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the AMS (Marketing) product family. Its purpose is to present geographic area details — including the area name, location, and location code — in a form suitable for concurrent programs, Oracle Discoverer worksheets, Oracle Business Intelligence Publisher reports, and other integration points that require a flattened hierarchical geography. The view is documented as VALID in both Oracle EBS 12.1.1 and 12.2.2. Unlike a base table, it stores no data of its own; it is a pure read-only projection over a single underlying hierarchy view, and it is typically referenced by marketing segmentation, campaign targeting, and territory-related setup screens where users select a geographic scope for a promotion or list.

The "_SCR_V" naming convention identifies it as a "screen" view — a display-oriented layer intended to supply denormalized, human-readable values rather than raw primary keys. This design means the view is most useful for lookups and reporting, and it should not be treated as a transactional interface.

Underlying Base Objects

The ETRM metadata records exactly one referenced base object: JTF_LOC_HIERARCHIES_VL, itself a view in the JTF (CRM Foundation / Territory Management) schema family. AMS_GEOAREA_SCR_V is therefore a thin wrapper rather than a multi-table join. All geography attributes — area names, country, country region, state, state region, city, and postal code ranges — are resolved from the columns of JTF_LOC_HIERARCHIES_VL, aliased as LH in the view text.

Because the base object is a "_VL" (view with a translation/MLS join), the location names it exposes are already language-resolved, meaning AMS_GEOAREA_SCR_V inherits multilanguage support without additional joins. The dependency is one-to-one: every row in AMS_GEOAREA_SCR_V corresponds to a row in JTF_LOC_HIERARCHIES_VL, and no aggregation or filtering is applied by the wrapper. Consequently, any performance or data-quality issue in the underlying hierarchy view is inherited directly.

Key Columns

The view exposes four columns, all derived at query time:

• LOCATION_HIERARCHY_ID — the primary identifier of the geography record, passed straight through from the base view. It is the key used by callers to join back to JTF_LOC_HIERARCHIES_VL or to other AMS objects.
• LOCATION_TYPE_CODE — classifies the level of the geography. Documented decode values include AREA1, AREA2, COUNTRY, CREGION (country region), STATE, SREGION (state region), CITY, and POSTAL_CODE.
• LOCATION_NAME — a short, single-level label. A DECODE selects the column matching the type code: AREA1_NAME or AREA2_NAME for areas, COUNTRY_NAME, COUNTRY_REGION_NAME, STATE_NAME, STATE_REGION_NAME, CITY_NAME, or for POSTAL_CODE a concatenation of POSTAL_CODE_START and POSTAL_CODE_END separated by a hyphen.
• LOCATION — a fully qualified address-style string built by nested DECODE and concatenation. For lower-level types it prepends the parent hierarchy (city, then state, then country region, then country) so that, for example, a CITY row reads "City, State, Region, Country". Empty intermediate values are suppressed to avoid stray commas.

Note that AREA1's LOCATION is simply the AREA2 name, and AREA2's is the AREA1 name — an intentional parent/child swap reflecting the hierarchy order rather than a defect.

Common Use Cases and Queries

The view is typically used to populate geographic selection lists in marketing campaign and segmentation setups, and to resolve hierarchy IDs into readable labels for reports and extracts.

To list all geographic areas of a given type:

SELECT location_hierarchy_id, location_name
FROM   apps.ams_geoarea_scr_v
WHERE  location_type_code = 'COUNTRY'
ORDER BY location_name;

To resolve a single hierarchy ID into its full display location — the most common lookup pattern:

SELECT location
FROM   apps.ams_geoarea_scr_v
WHERE  location_hierarchy_id = :p_id;

To build a postal-code extract for a direct-mail list, including the concatenated ZIP range:

SELECT location_hierarchy_id, location_name, location
FROM   apps.ams_geoarea_scr_v
WHERE  location_type_code = 'POSTAL_CODE';

Because the view is a simple projection with no WHERE clause of its own, callers should always filter on LOCATION_TYPE_CODE or LOCATION_HIERARCHY_ID rather than scanning the full hierarchy. Access is governed by the standard APPS schema grants, so reports should execute under a responsibility that has AMS read privileges.