Search Results oe_bis_top_customers




Overview
Explore in AI Chat

Overview

The OE_BIS_TOP_CUSTOMERS table is a Business Intelligence System (BIS) summary object owned by the OE (Order Entry) schema in Oracle E-Business Suite Releases 12.1.1 and 12.2.2. It stores pre-aggregated customer-level sales and order fulfillment metrics, enabling Order Management reporting without requiring runtime aggregation across the high-volume transactional tables such as OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL. The object is documented as VALID in the ETRM repository with thirteen physical columns.

In heuristic Data Vault terms, the FK mining classifies OE_BIS_TOP_CUSTOMERS as a standalone object with no documented foreign key dependencies. A dimensional modeler would treat CUSTOMER_ID as the natural business key of a customer dimension, with the period-based measures (bookings, billings, backlog, returns, order lines) behaving as a satellite of periodic snapshots. Because the object is standalone, no hub or link relationships are enforced at the database level, and referential integrity to HZ_CUST_ACCOUNTS is implicit rather than declared.

Key Information Stored

The documented primary key is CUSTOMER_ID_PK, defined on the single column CUSTOMER_ID. CUSTOMER_ID is therefore both the surrogate identifier within this table and the business-key candidate, since it corresponds to the customer identifier used throughout Order Management and Receivables. The remaining columns fall into three logical groups.

PERIOD_START and PERIOD_END define the reporting window for each snapshot row, while CREATION_DATE records when the summary row was materialized. ORGANIZATION_ID scopes the data to a specific operating unit or inventory organization, and CURRENCY_CODE identifies the currency in which the monetary measures are expressed. The measures support on-time delivery analysis, booking and billing trends, backlog visibility, and return rate monitoring.

Common Use Cases and Queries

The table is typically consumed by Order Management dashboards, customer scorecards, and period-over-period trend reports. A representative query identifying the highest-value customers for a given period is:

  • SELECT customer_id, SUM(period_bookings), SUM(period_billings), SUM(ytd_sales) FROM oe.oe_bis_top_customers WHERE period_start >= :from_date AND period_end <= :to_date AND organization_id = :org_id GROUP BY customer_id ORDER BY SUM(period_bookings) DESC;
  • Backlog exposure reporting by customer and currency using CURRENT_BACKLOG.
  • On-time delivery performance ratios derived from ON_TIME_DELIVERIES against ORDER_LINES.
  • Return rate analysis comparing RETURN_LINES to ORDER_LINES across reporting periods.

Because the table is a summary, it should be queried in preference to transactional tables for aggregate reporting, and refreshed through the concurrent programs that maintain BIS summary data.

Related Objects

Although the metadata documents no enforced foreign keys, the following objects are functionally associated and commonly joined in reporting and ETL logic:

  • HZ_CUST_ACCOUNTS — joined on CUSTOMER_ID to resolve customer name and account number.
  • OE_ORDER_HEADERS_ALL — joined on CUSTOMER_ID for drill-down into booking transactions.
  • OE_ORDER_LINES_ALL — the source of ORDER_LINES and RETURN_LINES aggregates.
  • RA_CUSTOMER_TRX_ALL — the source of PERIOD_BILLINGS and YTD_SALES figures.
  • HR_OPERATING_UNITS or ORG_ORGANIZATION_DEFINITIONS — joined on ORGANIZATION_ID for organizational context.
  • FND_CURRENCIES — joined on CURRENCY_CODE for currency descriptions.
  • OE_BIS_* sibling summary tables — related BIS objects sharing the same refresh programs and dimensional conventions.