Search Results won_amt

Overview

BIL.BIL_BI_OPDTL_F is a Business Intelligence summary table owned by the BIL (Business Intelligence Layer) schema within Oracle E-Business Foundation. It stores denormalized, pre-aggregated opportunity and sales-credit detail used for sales pipeline, forecasting, and win/loss analytics — typically consumed by Oracle Sales Analytics, Territory Manager, and Marketing dashboards. The table resides in the APPS_TS_SUMMARY tablespace, confirming its role as a reporting/summary object rather than a transactional base table.

Oracle explicitly flags this object as Oracle Internal Use Only; it is designed for access by standard Oracle Applications programs (such as the concurrent programs that populate and refresh BI summary data) rather than by custom SQL, so direct DML or custom dependencies should be treated with caution.

From a Data Vault modeling perspective, the mined metadata classifies this object as standalone. Since there are no outgoing foreign keys from this table's own rows to parent hubs, a heuristic modeling suggestion would be to treat the opportunity/sales-credit grain as the transactional hub key, with the time-stamped, amount-based columns behaving as satellite attributes — but no enforced hub, link, or satellite designation exists in the physical model. The documented physical schema contains 56 columns at release 12.1.1.

Key Information Stored

The table captures one row per opportunity sales-credit combination, keyed by the unique index column SALES_CREDIT_ID, which serves as the business-key candidate. Since this is the only documented unique index (BIL_BI_OPDTL_F_U1, unique, APPS_TS_SUMMARY), the table does not expose a separately documented surrogate primary key; the credit identifier is the effective row anchor.

• OPTY_ID — identifier for the parent opportunity.
• SALES_CREDIT_AMT / SALES_CREDIT_AMT_S — sales credit amount (numeric and stored/modified variant) allocated to the salesperson.
• WON_AMT — the won-amount measure, directly relevant to queries searching on "won_amt"; it holds the credited amount for opportunities marked as won.
• LOST_AMT, NO_OPTY_AMT, PIPELINE_AMT — parallel amount measures for lost deals, non-opportunity records, and open pipeline.
• OPTU_AMT and OPTY_AMT_S — total opportunity amount in numeric and display form.
• WIN_LOSS_INDICATOR, OPEN_STATUS_FLAG, WIN_PROBABILITY — flags and probability used for win/loss classification and weighted forecasting.
• WTD_PIPELINE_PB1..PB5 — week-to-date pipeline buckets used for period-based forecast rollups.
• SALESREP_ID and SALES_GROUP_ID — the salesperson and sales group receiving the credit.
• OPTY_CLOSE_TIME_ID, TXN_TIME_ID, OPTY_CREATION_TIME_ID, OPTY_LD_CONVERSION_TIME_ID — Julian-format dates for close, last transaction, creation, and lead conversion.
• CUSTOMER_ID, ITEM_ID, ITEM_ORGANIZATION_ID, PRODUCT_CATEGORY_ID — customer, item, and product dimension references.
• SALES_STAGE_ID, LEAD_LINE_ID, OPTY_RANK — sales-stage and lead linkage plus opportunity rank.
• SECURITY_GROUP_ID, REQUEST_ID, PROGRAM_UPDATE_DATE — multi-org security and concurrent-program bookkeeping.

Common Use Cases and Queries

The most frequent use is win/loss and forecast reporting: aggregating WON_AMT, LOST_AMT, and PIPELINE_AMT by sales representative, group, or period. A representative query pattern joins the Julian time keys to the BI time dimension:

SELECT salesrep_id, SUM(won_amt) FROM bil.bil_bi_opdtl_f WHERE win_loss_indicator='W' GROUP BY salesrep_id;

Other patterns include pipeline-by-stage analysis (SALES_STAGE_ID), product-mix analysis using ITEM_ID and PRODUCT_CATEGORY_ID, and week-to-date trending through the WTD_PIPELINE_PB1..PB5 buckets. Because the table is a summary object, reports should filter on SECURITY_GROUP_ID to respect MOAC security and should be scheduled to align with the BI refresh programs that maintain the REQUEST_ID lineage.

Related Objects

• AS_LEAD_LINES_ALL — referenced via the documented FK on LEAD_LINE_ID; supplies lead-line context for converted opportunities.
• AS_SALES_STAGES_ALL_B — referenced via SALES_STAGE_ID, providing stage names for pipeline reporting.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID for MOAC security filtering.
• BIL_BI_OPDTL_F_U1 / _N1 — the unique and nonunique indexes on SALES_CREDIT_ID and (ITEM_ID, ITEM_ORGANIZATION_ID) that drive access paths for item-level queries.
• BIL / AS / FND BI summary programs — the standard Oracle Applications concurrent programs that populate and refresh this object; they are the supported access path rather than direct custom queries.

Because the metadata classifies this object as standalone with no outgoing hub references, joins flow inward from these dimension and security tables through the documented FK columns rather than outward from this table.