Search Results bic_value_type

Overview

APPS.BIC_DIMV_GEOGRAPHY is a read-only dimension view in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments, owned by the APPS schema. It is part of the Business Intelligence Collection (BIC) dimensional layer that supplies conformed geography data to Oracle Business Intelligence, EBS data warehouse extracts, and downstream reporting tools. The view presents a unified geography dimension with columns for country, state, and city, keyed by a surrogate GEOGRAPHY_ID.

The view is defined as a UNION ALL of two branches that operate as one logical result set. The first branch produces a single real member record with GEOGRAPHY_ID = 1, sourced from the BIM_GEOGRAPHY table. The second branch produces a single special-purpose record with GEOGRAPHY_ID = -999, sourced from the FND_LOOKUPS table using the seeded lookup type BIC_VALUE_TYPE and lookup code -999. This structure means the view always returns exactly two rows regardless of the volume of geography data in the source tables—one member representing the actual geography attributes and one sentinel row representing an "unknown/not applicable" placeholder. The WITH READ ONLY clause enforces that the view cannot be used as the target of DML.

Underlying Base Objects

The ETRM metadata documents two referenced base objects:

• BIM_GEOMAPHY / BIM_GEOGRAPHY — the source of the real geography member in the first branch of the UNION ALL, supplying the COUNTRY, STATE, and CITY columns.
• FND_LOOKUPS — the source of the -999 sentinel record, filtered by LOOKUP_TYPE = 'BIC_VALUE_TYPE' and LOOKUP_CODE = '-999'. The MEANING column of the matching lookup row supplies the placeholder values for all six geography attribute columns.

No further base objects are documented. The view is a pure projection layer: it applies DISTINCT to each branch and performs no joins, aggregations, or transformations beyond column aliasing. Because both branches are governed by UNION ALL and each is constrained to a single row by the range of the underlying data, the view is lightweight and deterministic in size.

Key Columns

• GEOGRAPHY_ID — the surrogate key for the dimension. The literal 1 identifies the real member; the literal -999 identifies the sentinel "unknown" member. Because the value is hard-coded rather than drawn from a sequence, the view does not persist or manage a many-member hierarchy.
• COUNTRY / COUNTRY_NAME — aliased to the same underlying value in both branches. In the real branch the value comes from BIM_GEOGRAPHY; in the sentinel branch it comes from the FND_LOOKUPS MEANING.
• STATE / STATE_NAME — the first-level administrative division, aliased identically in both branches.
• CITY / CITY_NAME — the lowest geographic level captured, aliased identically in both branches.

The duplication of the source column as both a short alias and a _NAME alias is deliberate: it allows the view to satisfy ETL mappings and BI models that expect either a code-style or a descriptive column naming convention.

Common Use Cases and Queries

The primary use case is as a static conformed geography dimension for BI and data warehouse loading, with the -999 record used to resolve orphaned or unknown geographic references in fact tables. A typical query enumerating the dimension is:

• List all dimension members: SELECT geography_id, country_name, state_name, city_name FROM apps.bic_dimv_geography;
• Retrieve only the sentinel member: SELECT * FROM apps.bic_dimv_geography WHERE geography_id = -999;
• Join to a fact table on the surrogate key: SELECT f.amount, g.country_name FROM fact f, apps.bic_dimv_geography g WHERE f.geography_id = g.geography_id;

Because the view is read-only and the branch cardinality is fixed, it is safe to reference in ETL extracts and query joins without concern for full-table scans on the underlying BIM_GEOGRAPHY table. Any modifications to the sentinel values must be made through the FND_LOOKUPS maintenance form for lookup type BIC_VALUE_TYPE and lookup code -999, not through the view itself.