Search Results bic_dimv_customers




Overview
Explore in AI Chat

Overview

BIC_DIMV_CUSTOMERS is a read-only dimensional view that belongs to the Oracle E-Business Suite Customer Intelligence (BIC) product family. It is documented as an obsolete object in the ETRM reference for EBS 12.1.1 and 12.2.2, and the metadata explicitly records that it is not implemented in the reference database. The view was designed to expose a flattened, denormalized customer dimension suitable for analytical reporting, data warehousing extracts, and integration with business intelligence tooling that consumes Oracle EBS customer data. Its naming convention — the "DIMV" prefix — indicates a dimension view, distinguishing it from transactional or operational views used in day-to-day application processing. Because it is marked obsolete, the view should not be treated as a supported extension point in current implementations; its primary reference value is historical and analytical, illustrating how party and market segmentation data were once combined into a single customer dimension.

Underlying Base Objects

The documented view text joins two base tables from the Oracle EBS customer and marketing schemas:

  • HZ_PARTIES — the central party registry in Oracle's Trading Community Architecture (TCA), aliased as P. It supplies the party identifier, party category, and geographic attributes.
  • AMS_PARTY_MARKET_SEGMENTS — the market segment assignment table in the Advanced Marketing (AMS) schema, aliased as M. It supplies the market segment identifier and controlling flag.

The join is expressed as P.PARTY_ID = M.PARTY_ID (+) with the segment flag filter M.MARKET_SEGMENT_FLAG(+) = 'Y', making the relationship an outer join from the party to its market segment assignment. Parties without a qualifying segment row are therefore retained in the result set. No additional base objects are documented in the ETRM metadata, and the view is declared WITH READ ONLY, confirming that no DML is permitted against it.

Key Columns

The view exposes six columns:

  • PARTY_ID — the unique TCA party identifier, serving as the primary key of the customer dimension.
  • CUSTOMER_CATEGORY_FK — the party category code from HZ_PARTIES, providing a foreign key reference to the customer category classification (for example, organization, person, or group).
  • MARKET_SEGMENT_FK — the market segment identifier assigned in AMS_PARTY_MARKET_SEGMENTS, forming a foreign key to the market segment dimension.
  • COUNTRY — the country component of the party's address.
  • STATE — the state or province component of the party's address.
  • CITY — the city component of the party's address.

Together these columns support segmentation and geographic analysis of the customer base.

Common Use Cases and Queries

Typical usage centers on customer counts by segment and geography, and on feeding downstream BI models. A representative query is:

  • SELECT customer_category_fk, country, COUNT(*) FROM bic_dimv_customers GROUP BY customer_category_fk, country;
  • SELECT party_id, market_segment_fk FROM bic_dimv_customers WHERE country = 'US' AND state = 'CA';
  • SELECT c.customer_category_fk, c.market_segment_fk, COUNT(*) FROM bic_dimv_customers c GROUP BY c.customer_category_fk, c.market_segment_fk ORDER BY 3 DESC;

Because the object is documented as obsolete and not implemented, any such queries should be validated against the actual database before use; equivalent results can generally be obtained by joining HZ_PARTIES to AMS_PARTY_MARKET_SEGMENTS directly.