Search Results bil_bi_opdtl_denlog_tmp

Overview

BIL_BI_OPDTL_DENLOG_TMP is a temporary denormalized staging table owned by the BIL schema within the Oracle E-Business Suite Sales Intelligence (BIL) product family. Its documented purpose is to hold opportunity log identifiers that are consumed when populating the opportunity detail summary table used by Sales Intelligence reporting. In practical terms, the table acts as an intermediate work area during a batch build or refresh cycle: rows are inserted with the relevant log identifiers, the summary aggregation logic reads them, and the contents are subsequently purged or overwritten on the next run.

The object is present and VALID in both Oracle EBS 12.1.1 and 12.2.2. The documented 12.1.1 physical schema records twelve columns under the BIL owner. Because the table is a transient staging structure rather than a persistent transactional entity, the heuristic Data Vault classification derived from its foreign key footprint is standalone. This should be read as a modeling suggestion only: the table is best treated as a link between lead, lead line, and sales credit log references, with the log identifier columns functioning as the composite driving key. It is not a hub, since it introduces no enduring business key of its own, and it is not a durable satellite, since its rows are scoped to a single processing cycle.

Key Information Stored

The most significant columns fall into three groups: log identifiers, source entity references, and control attributes.

• LEAD_LOG_ID — the opportunity log identifier that drives the denormalization; this is the value the summary population process consumes.
• LEAD_ID — foreign key to AS_LEADS_ALL, identifying the parent lead or opportunity.
• LEAD_LINE_ID — foreign key to AS_LEAD_LINES_ALL, identifying the specific lead line.
• LEAD_LINE_LOG_ID — the log identifier associated with the lead line, pairing with LEAD_LINE_ID.
• SALES_CREDIT_ID and SALES_CREDIT_LOG_ID — the sales credit record and its corresponding log identifier, supporting revenue attribution.
• REV_FLAG — a revenue indicator used to filter or classify rows during summarization.
• CONVERSION_DATE — the date on which the referenced lead or line converted, relevant to period-based reporting.
• SEQ_NO — an ordering or sequencing column used to control load order.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing multi-org and function security filtering.
• CREATION_DATE and LAST_UPDATE_DATE — standard audit columns recording insertion and last modification of each staging row.

The documented metadata does not identify a single surrogate primary key column such as a generated ID; the log identifier columns collectively serve as the business-key candidates that make each staging row unique. Where a unique index exists, it is expected to be composite across LEAD_LOG_ID, LEAD_LINE_LOG_ID, and SALES_CREDIT_LOG_ID.

Common Use Cases and Queries

Typical usage centers on diagnosing and validating the opportunity detail summary build. A common pattern joins the staging table back to its source entities to confirm that every log identifier resolved correctly:

• Reconciliation query joining BIL_BI_OPDTL_DENLOG_TMP to AS_LEADS_ALL on LEAD_ID and to AS_LEAD_LINES_ALL on LEAD_LINE_ID to detect orphaned or missing log references before a summary refresh.
• Filtered extracts using REV_FLAG to isolate revenue-bearing rows for a specific CONVERSION_DATE range.
• Security-scoped reporting that constrains results by SECURITY_GROUP_ID, matching the FND_SECURITY_GROUPS definition used elsewhere in the Sales Intelligence schema.
• Row-count and duplicate checks on the log identifier columns to confirm the staging set is clean prior to populating the opportunity detail summary table.
• Audit queries on CREATION_DATE and LAST_UPDATE_DATE to determine when the last staging cycle ran and whether stale rows remain.

Related Objects

• AS_LEADS_ALL — joined via LEAD_ID; the primary lead and opportunity header entity.
• AS_LEAD_LINES_ALL — joined via LEAD_LINE_ID; provides the line-level detail behind each opportunity log entry.
• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID; governs row-level access and multi-org visibility.
• BIL_BI_OPDTL_SUMMARY (and related opportunity detail summary tables) — the downstream target populated from this staging data.
• AS_SALES_CREDITS_ALL — the source of SALES_CREDIT_ID and SALES_CREDIT_LOG_ID values referenced by this table.
• BIL concurrent programs responsible for the opportunity detail build — the process that inserts into and truncates this staging table.