Search Results lead_status_pk




Overview
Explore in AI Chat

Overview

EDW_BIM_LSTA_LEADSTATUS_LCV is a read-only database view historically shipped with the Oracle E-Business Suite Marketing Intelligence (BIM) product family. Marketing Intelligence was the analytical and data-warehousing layer for Oracle Marketing, providing the Extract-Transform-Load (ETL) staging and dimension objects consumed by the Business Intelligence System (BIS) / Enterprise Data Warehouse (EDW) schemas. In the ETRM reference for release 12.1.1 and 12.2.2, this object is classified under BIM – Marketing Intelligence and flagged as obsolete; the corresponding documentation states that it is not implemented in the current database.

The view exposes a flattened, presentation-ready list of lead statuses combined with the local EDW instance identifier. It is a classic “LOV” (List of Values) container — the _LCV suffix denotes a List of Values / code view — used by OBIEE / BI Publisher reports and by the Marketing Intelligence ETL to resolve lead status codes into their descriptive meanings. The concatenated primary key allows the same status code to coexist across multiple EDW instances within a single logical dimension.

Underlying Base Objects

The view is defined over two documented base objects, joined without an explicit join predicate other than the constant literal:

  • AS_STATUSES_VL — the Marketing statuses base table (aliased AST). This table stores the seeded and user-defined status codes used throughout Oracle Marketing and Sales, including leads, opportunities, and campaigns. The _VL suffix indicates it is a translated view exposing the current-language MEANING and DESCRIPTION columns.
  • EDW_LOCAL_INSTANCE — the Marketing Intelligence local instance registry (aliased INST), which holds the INSTANCE_CODE identifying the source EBS instance feeding the warehouse.

The join is effectively a cross join filtered by AST.LEAD_FLAG = 'Y' and AST.ENABLED_FLAG = 'Y', restricting the result to enabled statuses explicitly designated for leads. Because the predicate between the two tables is the constant 'ALL', the view produces one row per enabled lead status per registered EDW instance.

Key Columns

  • LEAD_STATUS_PK — composite surrogate key formed as STATUS_CODE || '-' || INSTANCE_CODE. Uniquely identifies each row.
  • ALL_FK — constant literal 'ALL', used as a dummy foreign key to support the instance-agnostic dimension rollup.
  • LEAD_STATUS_DP — the display value for the status; mirrors MEANING and is the column most commonly referenced in LOV definitions and report prompts.
  • NAME — user-facing name, also mapped to AST.MEANING.
  • STATUS_CODE — the underlying code from AS_STATUSES_VL.
  • MEANING — translated description of the status.
  • DESCRIPTION — longer free-text description of the status.
  • INSTANCE — the EDW instance code.
  • LAST_UPDATE_DATE / CREATION_DATE — standard audit columns inherited from AS_STATUSES_VL.

Common Use Cases and Queries

The primary use is to populate lead status List of Values prompts in BI dashboards and to resolve status codes to descriptions in lead-funnel analyses. A typical query returning statuses for a specific instance is shown below.

  • SELECT LEAD_STATUS_DP, STATUS_CODE, INSTANCE FROM EDW_BIM_LSTA_LEADSTATUS_LCV WHERE INSTANCE = :instance_code ORDER BY LEAD_STATUS_DP;
  • SELECT LEAD_STATUS_PK, LEAD_STATUS_DP FROM EDW_BIM_LSTA_LEADSTATUS_LCV WHERE STATUS_CODE = :status_code;
  • Because the view is created WITH READ ONLY, it cannot be used as a DML target; it is intended strictly for extraction and reporting.

Given the obsolete designation, implementations upgrading to 12.2.2 should confirm availability via ALL_VIEWS before depending on this object, and should migrate any dependent BI artifacts to the current Marketing lead-status views where applicable.