Search Results lead_source_code

Overview

EDW_BIM_OPPR_OPRNTIES_LCV is a read-only database view associated with the BIM – Marketing Intelligence product family in Oracle EBS 12.1.1 and 12.2.2. As indicated by the "(Obsolete)" designation in the product metadata, this object belongs to a legacy marketing analytics component that is no longer actively maintained or implemented in current environments. The suffix "LCV" conventionally denotes a "Localized Current View" pattern used within Oracle's Enterprise Data Warehouse (EDW) and Business Intelligence layers to expose a standardized, denormalized projection of source transactional data for downstream reporting and ETL consumption.

The view consolidates opportunity and lead data, joining lead records against a local instance reference to produce a unified opportunity-level result set. Its primary role is to supply marketing intelligence dashboards, pipeline analytics, and opportunity forecasting reports with a consistent, read-only interface over the underlying CRM lead tables. The presence of an ALL_FK constant and an INSTANCE discriminator reflects the multi-instance federation design typical of Oracle's EDW architecture, allowing consolidated reporting across one or more source EBS instances.

Underlying Base Objects

Although the documented ETRM metadata lists no referenced base objects, the embedded view text identifies two source objects:

• AS_LEADS_ALL — aliased as OPP. This is the primary Oracle CRM/Advanced Sales lead and opportunity table, supplying all descriptive, financial, and lifecycle attributes exposed by the view.
• EDW_LOCAL_INSTANCE — aliased as INST. This is an EDW control table that identifies the local source instance, providing the INSTANCE_CODE used to construct the synthetic primary key and the instance discriminator column.

The join between these objects is unconstrained (a Cartesian product against the instance row), which is a common EDW technique to broadcast instance identity across every row. The view applies a filter excluding records where DELETED_FLAG = 'Y' (and retaining NULL flags), ensuring soft-deleted opportunities are omitted from reporting. The view is declared WITH READ ONLY, preventing DML through the view layer.

Key Columns

The view exposes twenty columns. Significant columns include:

• OPPORTUNITY_PK — Synthetic primary key formed by concatenating LEAD_ID and INSTANCE_CODE, ensuring uniqueness across federated instances.
• ALL_FK — Constant literal 'ALL', used as a conformed dimension key in EDW star schemas.
• OPPORTUNITY_LEAD_ID / OPP_LEAD_NUMBER — Source lead identifiers and user-facing lead numbers.
• OPP_DESCRIPTION / NAME / OPPORTUNITY_DP — Descriptive attributes, all mapped from OPP.DESCRIPTION.
• CLOSE_REASON — Maps directly to OPP.CLOSE_REASON, capturing the reason an opportunity was won or lost. This is the column most relevant to users searching the term "close_reason," and it supports win/loss analysis reporting.
• WIN_PROBABILITY, TOTAL_AMOUNT, CURRENCY_CODE — Pipeline value and forecasting metrics.
• LEAD_SOURCE_CODE, DECISION_TIMEFRAME_CODE, VEHICLE_RESPONSE_CODE — Code columns referencing marketing and sales lookup values.
• DELETED_FLAG, LAST_UPDATE_DATE, CREATION_DATE — Audit and lifecycle metadata.

Common Use Cases and Queries

Because close reason is central to win/loss reporting, a common query aggregates opportunities by close reason:

• SELECT CLOSE_REASON, COUNT(*) opp_count, SUM(TOTAL_AMOUNT) total_value FROM EDW_BIM_OPPR_OPRNTIES_LCV GROUP BY CLOSE_REASON;
• SELECT OPPORTUNITY_PK, NAME, CLOSE_REASON, WIN_PROBABILITY FROM EDW_BIM_OPPR_OPRNTIES_LCV WHERE CLOSE_REASON IS NOT NULL;
• SELECT INSTANCE, CLOSE_REASON, SUM(TOTAL_AMOUNT) FROM EDW_BIM_OPPR_OPRNTIES_LCV GROUP BY INSTANCE, CLOSE_REASON;

Typical consumers include marketing pipeline dashboards, opportunity conversion analyses, and EDW extract jobs feeding downstream BI repositories. Given the obsolete status, use in 12.1.1/12.2.2 environments should be validated against current implementation.