Results for “valid_flag”

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

Overview

BIL_BI_FST_DTL_STG is a staging table in the Oracle E-Business Suite Sales Intelligence (BIL) module. Its documented purpose is to hold forecast detail records temporarily before they are loaded into the fact table BIL_BI_FST_DTL_F, which is the analytic structure consumed by the Sales Intelligence reporting layer. The staging table therefore acts as an intermediate landing zone: source transactional and forecast data are populated here, transformed, and then propagated into the fact table. The ETRM 12.1.1 physical schema records the owner as BIL with 31 columns. The ETRM documentation classifies the product line as obsolete, and the implementation notes state that the object is not implemented in the reference database; consequently, the table should be treated as a legacy or historical artifact rather than an active foundation for new development in EBS 12.1.1 or 12.2.2.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign key structure is standalone. In other words, no hub, link, or satellite role is implied by its relationships, because its foreign keys point outward to dimension-type reference tables rather than forming part of an integrated hub-and-link graph. This is consistent with a staging or load-interface role rather than a core enterprise data model entity.

Key Information Stored

The table carries forecast grain attributes along with denormalized time and amount fields. The most significant columns are:

• FORECAST_ID — identifier for the forecast record being staged.
• SUBMISSION_DATE and PERIOD_NAME — the submission timestamp and the accounting/reporting period.
• FORECAST_CATEGORY_ID and PRODUCT_CATEGORY_ID — forecast classification and product grouping.
• CREDIT_TYPE_ID — references IGS_FI_CR_TYPES_ALL, indicating the credit type dimension.
• SALES_GROUP_ID and SALESREP_ID — sales organization and salesperson attribution.
• TXN_DAY, TXN_WEEK, TXN_PERIOD, TXN_QUARTER, TXN_YEAR — transaction-side time hierarchy.
• FORECAST_PERIOD_DAY, FORECAST_PERIOD_WEEK, FORECAST_PERIOD_PERIOD, FORECAST_PERIOD_QUARTER, FORECAST_PERIOD_YEAR — forecast-side time hierarchy used for period comparisons.
• FUNCTIONAL_CURRENCY, PRIMARY_CONVERSION_RATE, CONVERSION_RATE_S — currency and rate context for amount fields.
• FORECAST_AMT, FORECAST_AMT_S, FORECAST_AMT_1, FORECAST_AMT_S_1 and the corresponding OPP_FORECAST_AMT variants — forecast and opportunity forecast amounts in functional and secondary currency.
• VALID_FLAG — indicator of record validity.
• SECURITY_GROUP_ID — references FND_SECURITY_GROUPS, enabling row-level security partitioning during load.

The metadata does not document a unique index or surrogate primary key; FORECAST_ID together with the time and dimension columns are the natural business-key candidates for identifying a staging row. Security is enforced through SECURITY_GROUP_ID against FND_SECURITY_GROUPS.

Common Use Cases and Queries

Typical usage centers on ETL validation and reconciliation between the staging area and the target fact table before committing a load. Analysts also query the staging table to diagnose rejected or unbalanced forecast records.

SELECT f.FORECAST_ID, f.PERIOD_NAME, SUM(f.FORECAST_AMT)
FROM   BIL.FST_DTL_STG f
WHERE  f.VALID_FLAG = 'Y'
GROUP  BY f.FORECAST_ID, f.PERIOD_NAME;

Reconciliation against the fact table identifies records missing from the final load. Security-filtered extracts join FND_SECURITY_GROUPS on SECURITY_GROUP_ID, and dimension enrichment joins IGS_FI_CR_TYPES_ALL on CREDIT_TYPE_ID.

Related Objects

• BIL_BI_FST_DTL_F — the destination fact table populated from this staging table.
• IGS_FI_CR_TYPES_ALL — joined on CREDIT_TYPE_ID for credit type descriptions.
• FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID for row-level security.
• BIL_BI_FST_DTL_STG load programs and concurrent requests that perform the staging-to-fact transformation.
• Sales Intelligence reporting views and analytic queries that indirectly depend on the staged forecast amounts.