Search Results lost_amt

Overview

BIL.BIL_BI_OPDTL_F is a Business Intelligence summary (fact) table in the Oracle EBS Business Intelligence (BIL) schema, stored in the APPS_TS_SUMMARY tablespace. It holds opportunity detail fact records drawn from Oracle Sales (AS) and related CRM sources, aggregated at the grain of the sales credit. Each row captures an opportunity, its sales team assignment, the associated customer and product context, and a set of pre-computed amount metrics used by Oracle Sales Analytics and CRM reporting. The "_F" suffix denotes a fact table, and the row-level grain is anchored by the SALES_CREDIT_ID unique key.

The ETRM metadata classifies this object heuristically as standalone within the Data Vault modeling suggestion — that is, it functions as a self-contained fact/dimension-flattened table rather than a normalized hub, link, or satellite. Because its foreign keys point outward to dimensions (FND_SECURITY_GROUPS, AS_SALES_STAGES_ALL_B, AS_LEAD_LINES_ALL) while the table itself carries measures, it is best treated as a fact satellite in a dimensional model. Note the explicit Oracle warning: this object is Oracle Internal Use Only and is not supported for direct access except from standard Oracle Applications programs. The lost_amt column that prompted the search resides here.

Key Information Stored

Of the 56 documented columns, the most significant are the identifiers, temporal keys (stored in Julian format), and the amount measures:

• SALES_CREDIT_ID — Surrogate/business key and the column behind the unique index BIL_BI_OPDTL_F_U1. Defines the row grain.
• OPTY_ID — Identifier of the opportunity to which the sales credit belongs.
• SALESREP_ID and SALES_GROUP_ID — The salesperson and sales group receiving the credit; OWNER_SALESREP_ID / OWNER_SALES_GROUP_ID identify the opportunity owner.
• LOST_AMT — Amount attributed to opportunities that were lost. This is the central measure for loss analysis.
• WON_AMT, PIPELINE_AMT, NO_OPTY_AMT, OPTY_AMT — Complementary win, pipeline, non-opportunity, and total opportunity amounts.
• WTD_PIPELINE_PB1 … WTD_PIPELINE_PB5 — Week-to-date weighted pipeline buckets, supporting forecast rollups.
• WIN_PROBABILITY, WIN_LOSS_INDICATOR, OPEN_STATUS_FLAG, STATUS — Qualification flags distinguishing won, lost, and open states.
• TXN_TIME_ID, OPTY_CREATION_TIME_ID, OPTY_CLOSE_TIME_ID, OPTY_EFFECTIVE_TIME_ID — Julian time identifiers enabling period-based trending.
• CUSTOMER_ID, ITEM_ID, ITEM_ORGANIZATION_ID, PRODUCT_CATEGORY_ID — Customer and product context; ITEM_ID and ITEM_ORGANIZATION_ID are indexed by BIL_BI_OPDTL_F_N1.

Common Use Cases and Queries

Because the table pre-aggregates opportunity outcomes, it is the natural source for win/loss analysis, pipeline reporting, and rep performance summaries. A typical pattern filters by Julian time and groups by sales group:

• Win/loss ratio by period: SELECT SALES_GROUP_ID, SUM(WON_AMT), SUM(LOST_AMT) FROM BIL_BI_OPDTL_F WHERE TXN_TIME_ID BETWEEN :p_start AND :p_end GROUP BY SALES_GROUP_ID;
• Lost-amount drill-down by customer or product: aggregate LOST_AMT filtering WIN_LOSS_INDICATOR or STATUS.
• Rep-level pipeline: sum PIPELINE_AMT and the WTD_PIPELINE_PB* buckets grouped by SALESREP_ID.
• Item/product analysis: join on ITEM_ID and ITEM_ORGANIZATION_ID, supported by the non-unique index.

Oracle recommends accessing this data through delivered BI/CRM programs rather than direct SQL, and all query access should be security-masked via SECURITY_GROUP_ID.

Related Objects

The table participates in a small but meaningful set of dimensional relationships:

• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID for security-group filtering.
• AS_SALES_STAGES_ALL_B — joined via SALES_STAGE_ID for sales-stage descriptions.
• AS_LEAD_LINES_ALL — joined via LEAD_LINE_ID to link to originating lead lines.
• AS_OPPORTUNITIES_ALL_B / AS_OPPORTUNITY_LINES — source of OPTY_ID and opportunity attributes.
• AS_SALES_CREDITS_ALL and JTF_RS_SALESREPS — supply SALES_CREDIT_ID and SALESREP_ID context.
• BIL_BI_* family tables and the Oracle Sales Analytics materialized views that consume this fact.