Search Results bil_bi_opdtl_f




Overview
Explore in AI Chat

Overview

BIL_BI_OPDTL_F is a fact table owned by the BIL schema (Sales Intelligence) within Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to store a daily snapshot of sales opportunity information broken down by sales credits. Each row therefore represents the intersection of an opportunity, a sales representative's credit allocation, and a point in time defined by the transactional and effective date columns, making the table a time-series store rather than a transactional master.

The table is registered as a VALID object and, per ETRM documentation, contains 56 columns in the 12.1.1 physical schema, with a single documented unique index BIL_BI_OPDTL_F_U1 on SALES_CREDIT_ID. The Data Vault classification mined from the foreign key structure is standalone, meaning no parent-child FK chain constitutes a true hub-and-satellite pattern within this table's own key set. From a dimensional modeling perspective, it functions effectively as a satellite-style fact capturing opportunity measures and descriptive attributes at a recurring daily grain, with BUSINESS KEY candidates anchored on SALES_CREDIT_ID.

Key Information Stored

The most significant columns cluster into four groups:

The surrogate key is SALES_CREDIT_ID; OPTY_ID and the time identifiers function as foreign or business keys linking to opportunity and calendar dimensions.

Common Use Cases and Queries

Typical usage centers on pipeline reporting, forecast rollup, and sales credit analysis. A representative query aggregates weighted pipeline by sales stage:

  • SELECT s.STAGE_NAME, SUM(f.PIPELINE_AMT) FROM BIL_BI_OPDTL_F f JOIN AS_SALES_STAGES_ALL_B s ON f.SALES_STAGE_ID = s.SALES_STAGE_ID WHERE f.EFFECTIVE_DATE = :snapshot_date GROUP BY s.STAGE_NAME;
  • SELECT SALESREP_ID, SUM(SALES_CREDIT_AMT), SUM(WON_AMT), SUM(PIPELINE_AMT) FROM BIL_BI_OPDTL_F WHERE TXN_DATE BETWEEN :from AND :to GROUP BY SALESREP_ID;
  • SELECT OPTY_ID, OPTY_AMT, WIN_PROBABILITY FROM BIL_BI_OPDTL_F WHERE OPEN_STATUS_FLAG = 'Y' AND FORECAST_ROLLUP_FLAG = 'Y';

Because rows recur daily, trend queries should pin EFFECTIVE_DATE or TXN_DATE to avoid double counting, while period-over-period comparisons across WTD_PIPELINE_PB columns support forecast bucket analysis.

Related Objects

Documented foreign keys tie the fact table to the following referenced objects:

  • FND_SECURITY_GROUPS via SECURITY_GROUP_ID — enforces multi-org security on the fact rows.
  • AS_SALES_STAGES_ALL_B via SALES_STAGE_ID — supplies sales stage descriptions for pipeline rollup.
  • AS_LEAD_LINES_ALL via LEAD_LINE_ID — links snapshot rows to originating lead lines.
  • AS_OPPORTUNITIES_ALL — the natural join target on OPTY_ID for opportunity master attributes.
  • AS_SALES_CREDITS_ALL — supplied SALES_CREDIT_ID as the originating credit record.
  • HZ_CUST_ACCOUNTS / CUSTOMER_ID and MTL_SYSTEM_ITEMS_B — dimension joins for customer and product context.

These relationships make BIL_BI_OPDTL_F the fact corner joining opportunity, lead, credit, stage, and security dimensions within the Sales Intelligence schema.