Results for “close_date”

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

Overview

BIL_BI_OPDTL_STG is a staging table within the Oracle EBS Sales Intelligence module (product code BIL), historically shipped as part of the Oracle Business Intelligence and Sales Analyzer extensions for Oracle E-Business Suite 12.1.1 and 12.2.2. As its name implies, it occupies the "staging" layer of an extract-transform-load pipeline: transactional rows are landed here before being cleansed, validated, and propagated into the fact table BIL_BI_OPDTL_F, which supports the Opportunity Detail (OPDTL) subject area. The table therefore acts as an intermediate persistence object rather than a transactional or master-data entity that end users interact with directly.

The ETRM metadata classifies this object as standalone under its heuristic Data Vault assessment, meaning it does not present the characteristic hub-and-satellite or link topology mined from foreign-key correlations. From a modeling perspective, this object is best understood as a staging satellite or transient fact-source table rather than a genuine Data Vault hub. It is documented as not implemented in the current database, and the Sales Intelligence product line itself is flagged as obsolete, so its presence in any environment will be limited to legacy installations and upgrade artifacts.

Key Information Stored

The table carries a documented physical footprint of 42 columns owned by the BIL schema. Its unique index, BIL_BI_OPDTL_STG_U1, spans LEAD_ID, LEAD_LINE_ID, and SALES_CREDIT_ID, identifying the composite business key that distinguishes one staged opportunity detail row from another. There is no documented single-column surrogate primary key; the business key serves as the row identifier.

• LEAD_ID – Foreign key to AS_LEADS_ALL; identifies the originating lead.
• LEAD_LINE_ID – Foreign key to AS_LEAD_LINES_ALL; the specific lead line.
• SALES_CREDIT_ID – Revenue-credit assignment component of the composite key.
• TXN_DATE and EFFECTIVE_DATE – Transaction and effective-dating timestamps.
• OPTY_CREATION_DATE and OPTY_LD_CONVERSION_DATE – Opportunity creation and lead-to-opportunity conversion dates.
• SALES_STAGE_ID – Foreign key to AS_SALES_STAGES_ALL_B.
• INTEREST_TYPE_ID – Foreign key to AS_INTEREST_TYPES_B.
• SECURITY_GROUP_ID – Foreign key to FND_SECURITY_GROUPS for row-level access control.
• SALES_CREDIT_AMOUNT, OPTY_GLOBAL_AMT, and WIN_PROBABILITY – Quantitative revenue, global amount, and probability measures.
• WIN_LOSS_INDICATOR, OPP_OPEN_STATUS_FLAG, FORECAST_ROLLUP_FLAG – Status and rollup qualifiers.
• CAMPAIGN_OBJECT_ID, CAMPAIGN_OBJECT_TYPE, CHILD_CAMPAIGN_OBJECT_ID – Marketing campaign attribution.
• DELETE_FLAG and VALID_FLAG – Staging-layer control flags for change detection.

Common Use Cases and Queries

Because BIL_BI_OPDTL_STG is a staging object, its primary use is diagnostic and pipeline-oriented: verifying row counts and quality before a fact-table load, reconciling key integrity, or investigating stale or orphaned rows that fail to reach BIL_BI_OPDTL_F.

• Load validation: compare staged rows against the target fact to detect unreconciled credits: SELECT COUNT(*) FROM bil_bi_opdtl_stg s WHERE NOT EXISTS (SELECT 1 FROM bil_bi_opdtl_f f WHERE f.lead_id = s.lead_id AND f.lead_line_id = s.lead_line_id);
• Orphan detection: join to AS_LEADS_ALL and AS_LEAD_LINES_ALL to confirm every staged row maps to a valid lead and line.
• Credit-level analysis: aggregate SALES_CREDIT_AMOUNT by SALESREP_ID or SALES_GROUP_ID prior to promotion into the fact.
• Security filtering: the SECURITY_GROUP_ID join to FND_SECURITY_GROUPS constrains which rows a given user or responsibility may see in downstream reports.
• Stage/forecast trending: group OPP_OPEN_STATUS_FLAG and WIN_PROBABILITY by EFFECTIVE_DATE to inspect movement in the pipeline.

Related Objects

The documented foreign-key relationships anchor this table to lead and qualification entities. The most significant related objects are:

• BIL_BI_OPDTL_F – The target fact table this staging table populates.
• AS_LEADS_ALL – Joined via BIL_BI_OPDTL_STG.LEAD_ID.
• AS_LEAD_LINES_ALL – Joined via BIL_BI_OPDTL_STG.LEAD_LINE_ID.
• AS_INTEREST_TYPES_B – Joined via BIL_BI_OPDTL_STG.INTEREST_TYPE_ID.
• AS_SALES_STAGES_ALL_B – Joined via BIL_BI_OPDTL_STG.SALES_STAGE_ID.
• FND_SECURITY_GROUPS – Joined via BIL_BI_OPDTL_STG.SECURITY_GROUP_ID.

Given the obsolete status and un-implemented state, teams on 12.1.1 or 12.2.2 should treat this table as a read-only historical artifact and confirm whether equivalent functionality now resides in later Oracle Sales Intelligence or Oracle CRM Analytics schemas.