Results for “bil_bi_fst_dtl_f”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

BIL_BI_FST_DTL_F is the Sales Forecast Base Summary table within the BIL – Sales Intelligence module of Oracle E-Business Suite. It stores summarized forecast data organized by accounting period, sales group, and salesperson, supporting historical and predictive analysis of sales performance. The table belongs to the BIL schema and is documented across ETRM releases 12.1.1 and 12.2.2 with a physical schema of 23 columns. The product designation "Obsolete" signals that the module is legacy and is not implemented in current database instances; it should be treated as a reference object rather than an active transactional table.

From a heuristic Data Vault modeling perspective, the mined foreign-key structure classifies this object as standalone. This is a modeling suggestion only: the table has no dependent children in the documented relationship map, meaning it can be modeled as an independent reference or summary satellite rather than as a hub with downstream links.

Key Information Stored

The table's grain is defined by the unique index BIL_BI_FST_DTL_F_U1, which spans TXN_TIME_ID, TXN_PERIOD_TYPE_ID, FORECAST_TIME_ID, FORECAST_PERIOD_TYPE_ID, SALES_GROUP_ID, SALESREP_ID, PRODUCT_CATEGORY_ID, and CREDIT_TYPE_ID. These eight columns together form the business-key candidate that uniquely identifies each summarized forecast row. Because the table is a summary/base table and not a transaction entity, no single surrogate primary key column is documented; identity is composite.

• TXN_TIME_ID / TXN_PERIOD_TYPE_ID — the transaction period and its period type, establishing the "as-of" side of the comparison.
• FORECAST_TIME_ID / FORECAST_PERIOD_TYPE_ID — the forecast period and period type, establishing the projection side of the comparison.
• SALES_GROUP_ID / SALESREP_ID — the organizational and individual dimensions against which forecasts are summarized.
• PRODUCT_CATEGORY_ID — the product dimension rollup.
• CREDIT_TYPE_ID — a foreign key to IGS_FI_CR_TYPES_ALL, qualifying the forecast by credit type.
• FORECAST_CATEGORY_ID — the forecast classification dimension.
• FORECAST_AMT / FORECAST_AMT_S — forecast amount and its secured (access-controlled) counterpart.
• OPP_FORECAST_AMT / OPP_FORECAST_AMT_S — opportunity-related forecast amount and its secured counterpart.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, driving row-level access control.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN provide standard WHO tracking.
• Concurrent program columns — REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE identify the concurrent request that populated the row.

Common Use Cases and Queries

The primary use case is period-over-period forecast versus actual comparison for a sales group or salesperson. A representative query joining the secured amount to a credit type would take the form:

• SELECT f.sales_group_id, f.salesrep_id, f.forecast_amt_s FROM bil.bil_bi_fst_dtl_f f WHERE f.txn_time_id = :txn_period AND f.forecast_time_id = :fcst_period;
• Aggregate FORECAST_AMT by PRODUCT_CATEGORY_ID for a given SALES_GROUP_ID to produce category-level forecast rollups.
• Join CREDIT_TYPE_ID to IGS_FI_CR_TYPES_ALL to break forecasts out by credit classification.
• Filter by SECURITY_GROUP_ID to enforce row-level security for reporting users.

Because the module is obsolete, any query should first confirm the table exists in the target instance; the ETRM record explicitly states "Not implemented in this database." Reporting logic should target its replacement objects if the installation has migrated off BIL Sales Intelligence.

Related Objects

• IGS_FI_CR_TYPES_ALL — referenced via CREDIT_TYPE_ID for credit type validation.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID for row-level access control.
• FND_CONCURRENT_REQUESTS — implied by REQUEST_ID for tracing the program that populated each row.
• FND_APPLICATION / FND_PROGRAM — implied by PROGRAM_APPLICATION_ID and PROGRAM_ID.
• BIL_BI_FST_DTL (and other BIL forecast detail tables) — sibling base/detail tables in the same Sales Intelligence family that would typically feed this summary.
• Standard Sales/AR reporting views that consume BIL forecast summaries for revenue pipeline analysis.