Results for “bis_territories_v”

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

Overview

BIS_TERRITORIES_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite releases 12.1.1 and 12.2.2. It belongs to the BIS product family and provides a consolidated, reporting-friendly list of territory definitions used across the E-Business Suite. Rather than maintaining its own physical storage, the view synthesizes territory data from two distinct sources: the FND_LOOKUPS lookup view and the FND_TERRITORIES_VL territories view. Territories are fundamental reference data in EBS because they underpin customer, vendor, and organizational address validation, as well as geographic reporting, tax determination, and multi-org configuration.

The view exists primarily to give developers and report authors a single, simplified query surface. Instead of writing UNION logic against multiple dictionary objects, consumers can SELECT from BIS_TERRITORIES_V and receive a normalized set of territory rows. This is especially relevant when the search term "territory_type" is used, since the view exposes a TYPE column populated with values such as 'REGION', 'AREA', and 'COUNTRY'.

Underlying Base Objects

The documented metadata for ETRM 12.2.2 identifies three referenced base objects: FND_GLOBAL (a package), FND_LOOKUPS (a view), and FND_TERRITORIES_VL (a view). The view definition is a UNION of two SELECT statements:

• The first branch queries FND_LOOKUPS, filtering LOOKUP_TYPE in ('REGION','AREA') and applying the standard effective-date predicates on START_DATE_ACTIVE and END_DATE_ACTIVE.
• The second branch queries FND_TERRITORIES_VL, mapping TERRITORY_CODE, TERRITORY_SHORT_NAME, and DESCRIPTION, and hard-coding a territory type literal of 'COUNTRY'.

FND_LOOKUPS supplies lookup codes for regions and areas, while FND_TERRITORIES_VL supplies the country-level territory data. FND_GLOBAL is referenced in the form of the SYSDATE-based effective dating logic associated with session and globalization context; the view text itself relies on SYSDATE rather than an explicit FND_GLOBAL call, reflecting the standard EBS effective-dating pattern.

Key Columns

The view exposes four columns, which are aliased differently in each UNION branch to produce a consistent output:

• TERRITORY_CODE — the unique territory identifier. For regions and areas this equals LOOKUP_CODE; for countries it equals TERRITORY_CODE from FND_TERRITORIES_VL.
• NAME — the formatted territory name. For regions and areas this is MEANING; for countries it is TERRITORY_SHORT_NAME.
• DESCRIPTION — a longer descriptive text for the territory, sourced from the DESCRIPTION column in both branches.
• TYPE — the territory category. This is the column that answers searches for "territory_type", returning values of 'REGION', 'AREA', or 'COUNTRY'.

Note that in the lookup branch the source column is named LOOKUP_TYPE and is aliased as TYPE; in the country branch the literal 'COUNTRY' is aliased as TERRITORY_TYPE. Consumers should reference the output alias TYPE when querying the view.

Common Use Cases and Queries

Typical scenarios include building LOVs for address entry, validating territory codes during data conversion, and producing geographic reporting breakdowns by region, area, or country.

• Listing all territories with their type: SELECT TERRITORY_CODE, NAME, TYPE FROM BIS_TERRITORIES_V ORDER BY TYPE, NAME;
• Retrieving only countries: SELECT TERRITORY_CODE, NAME FROM BIS_TERRITORIES_V WHERE TYPE = 'COUNTRY';
• Filtering regions and areas: SELECT NAME, DESCRIPTION FROM BIS_TERRITORIES_V WHERE TYPE IN ('REGION','AREA');
• Validating a specific code: SELECT NAME FROM BIS_TERRITORIES_V WHERE TERRITORY_CODE = :p_code;

Because the view applies effective-date filtering internally, only currently active region, area, and country records are returned, simplifying report logic. It should be treated as a read-only reference source and not used for transactional updates.