Search Results dimension_level_short_name




Overview
Explore in AI Chat

Overview

APPS.BIC_TOTAL_CUST_CATEGORY_V is a lightweight dimensional helper view in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments, delivered as part of the Business Intelligence/ETRM (Enterprise Transaction Reporting Model) schema objects owned by the APPS schema. The view exposes a single static dimension member that resolves the "TOTAL CUST CATEGORY" (Total Customer Category) level from the dimension-levels metadata table. It is not a transactional view; it does not aggregate facts, join to customer tables, or compute any business measures. Instead it acts as a resolved dimension token — a controlled lookup that other ETRM objects, reports, or extracted views can join against or union with in order to emit a well-known total-level dimension row consistently.

The view returns a hard-coded key of '-1' together with the human-readable dimension-level name obtained from the underlying metadata table. The literal '-1' conventionally represents a "total" or "all" pseudo-member in dimensional modelling, so consumers of the view receive a stable identifier that can be used in drill-down hierarchies, balances reports, or cube-loading routines wherever a grand-total customer-category level is required.

Underlying Base Objects

The documented definition of the view references a single base object:

  • BISBV_DIMENSION_LEVELS (aliased as D1) — the ETRM dimension-levels metadata table that stores the names and short names of every dimension level available to the reporting model.

The view's WHERE clause filters this table on D1.DIMENSION_LEVEL_SHORT_NAME = 'TOTAL CUST CATEGORY'. Because the view is defined entirely over metadata rather than fact or customer master tables, it has no dependency on transactional data and returns a deterministic result set (normally exactly one row) as long as the referenced short name exists in BISBV_DIMENSION_LEVELS. The ETRM metadata documents no additional referenced base objects.

Key Columns

  • '-1' — an unnamed literal column that supplies the fixed dimension member key. The literal is emitted without an alias in the documented view text, so in a query it typically appears as the first positional column. It represents the total/all member identifier.
  • DIMENSION_LEVEL_NAME — sourced from D1.DIMENSION_LEVEL_NAME, this column carries the descriptive display name of the "TOTAL CUST CATEGORY" dimension level. It is the descriptive counterpart to the short name used in the filter predicate.

Note that the filter column, DIMENSION_LEVEL_SHORT_NAME, is not projected into the view's SELECT list; it is used only to identify the correct metadata row.

Common Use Cases and Queries

This view is most frequently referenced in ETRM reporting and extraction code that needs to inject a consistent total-level customer-category row — for example, when building a UNION ALL against detail-level customer-category views so that a grand total appears alongside the detail. A simple retrieval of the resolved total member is:

SELECT * FROM apps.bic_total_cust_category_v;

Where the literal needs a meaningful alias for downstream joins, a wrapping query can be used:

SELECT '-1' AS dimension_level_key,
       dimension_level_name
  FROM apps.bic_total_cust_category_v;

Typical scenarios include:

  • Unioning the total-member row with detail customer-category rows in an ETRM fact view.
  • Resolving the display name of the "TOTAL CUST CATEGORY" level for report labels or page headers.
  • Providing a stable '-1' key when validating referential integrity of dimension members in BI extracts or data-warehouse loads.
  • Confirming the presence of the metadata row during ETRM setup verification.

Because the view depends solely on BISBV_DIMENSION_LEVELS, if the "TOTAL CUST CATEGORY" short name is absent or misspelled in that metadata table, the view returns no rows; troubleshooting therefore focuses on the contents of BISBV_DIMENSION_LEVELS rather than on any transaction data.