Search Results bil_dimv_customers




Overview
Explore in AI Chat

Overview

BIL_DIMV_CUSTOMERS is a read-only database view historically shipped with the Oracle E-Business Suite Sales Intelligence (BIL) product family. In EBS 12.1.1 and 12.2.2 the BIL module is classified as Obsolete, meaning the product is no longer actively supported or installed by default. The view's stated purpose is to expose a consolidated, dimension-style customer list — the "Customers" dimension — for downstream reporting and integration scenarios. It presents one row per customer party, plus a synthetic sentinel row, with attributes flattened into a single denormalized structure suitable for populating dimensional models, value sets, or LOV queries.

Because the object is documented as "not implemented in this database" in the ETRM metadata, the view may be absent from a given 12.1.1 or 12.2.2 environment, particularly where the BIL product was never licensed or where it has been de-installed during an upgrade. Despite its obsolete status, the object remains referenced in legacy customizations, reports, and interfaces that were originally built on the Sales Intelligence schema.

Underlying Base Objects

The view text is defined over two source objects combined with a UNION ALL:

  • HZ_PARTIES (HP) — the primary source. The first branch of the union selects party records where STATUS is 'A' (Active) or 'I' (Inactive), supplying the bulk of the customer/party attributes.
  • FND_LOOKUPS (FL) — the secondary source. The second branch selects a single sentinel row from the BIL_VALUE_TYPE lookup type where LOOKUP_CODE = '-999', providing a placeholder "unknown/undefined" dimension member for referential integrity in star-schema reporting.

No other base objects are documented. The dependency on HZ_PARTIES ties this view to Oracle's Trading Community Architecture (TCA) party model, while the FND_LOOKUPS branch demonstrates the classic EBS dimensional modeling pattern of seeding a default member into a dimension view.

Key Columns

  • CUSTOMER_ID — the numeric party identifier sourced from HZ_PARTIES.PARTY_ID (or the literal -999 for the sentinel row).
  • CUSTOMER_NAME — HP.PARTY_NAME, the display name of the party.
  • CUSTOMER_NUMBER — HP.PARTY_NUMBER, the user-facing party number.
  • PARTY_TYPE — the TCA party type classification.
  • ADDRESS1–ADDRESS4, CITY, STATE, COUNTRY, COUNTY, PROVINCE, POSTAL_CODE — flattened address attributes drawn directly from HZ_PARTIES.
  • CREATION_DATE — the party creation timestamp; for the sentinel row this is FND_LOOKUPS.START_DATE_ACTIVE.
  • ID — a character representation of the source ID (TO_CHAR(HP.PARTY_ID), or the lookup code itself).
  • VALUE — the descriptive value (SUBSTR(HP.PARTY_NAME,1,80), or the lookup meaning), truncated to the standard EBS descriptive-flexfield segment length of 80 characters.

Common Use Cases and Queries

The most common application is supplying a customer dimension to BIL or third-party analytical reporting. A simple extraction might resemble:

  • SELECT CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_NUMBER FROM BIL_DIMV_CUSTOMERS ORDER BY CUSTOMER_NAME;
  • Filtering the sentinel member: SELECT * FROM BIL_DIMV_CUSTOMERS WHERE CUSTOMER_ID > 0;
  • Populating a value set or LOV using the ID and VALUE columns, which follow EBS key/flex conventions.

Additional scenarios include cross-referencing party attributes against TCA tables for data-quality checks, and building denormalized extracts for downstream data warehouses. Because the view is read-only (WITH READ ONLY) it cannot be used for DML. Organizations maintaining legacy BIL customizations on 12.2.2 should note the obsolete designation and plan migration of any dependent logic toward supported TCA views such as HZ_PARTIES directly.