Search Results bil_dimv_opty_statuses

Overview

BIL_DIMV_OPTY_STATUSES is a dimensional view within the Oracle E-Business Suite Sales Intelligence (BIL) module, a component of the broader Oracle CRM and Trade Management (ETRM) reporting architecture. Its documented purpose is to expose the set of opportunity statuses available for sales pipeline and forecasting analytics. The view presents a unified, language-aware list of status codes and their attributes, serving as a reference or lookup source for dimensional reporting in Sales Intelligence dashboards, Discoverer workbooks, and OBIEE repository layers built against the EBS 12.1.1 or 12.2.2 database.

The metadata explicitly labels the product as "BIL - Sales Intelligence (Obsolete)," indicating that this object belongs to a legacy Sales Intelligence schema that Oracle has superseded in later release lines. The view is documented as "Not implemented in this database," meaning it is a seeded definition that may or may not be deployed in any given environment. The user search term lead_flag corresponds directly to one of the view's exposed columns, confirming its relevance to lead-versus-opportunity classification.

Underlying Base Objects

The view text is a UNION ALL of two branches. The primary branch joins AS_STATUSES_B (the base table holding status definitions) with AS_STATUSES_TL (the translation table holding language-specific meaning and description) on STATUS_CODE, filtered by T.LANGUAGE = USERENV('LANG'). This join pattern is the standard EBS convention for multilingual lookup data.

The second branch selects from FND_LOOKUPS where LOOKUP_TYPE = 'BIL_VALUE_TYPE' and LOOKUP_CODE = '-999'. This branch synthesizes a placeholder status row, casting literal values such as 'N' for most flag columns and TO_NUMBER conversions for rank and audit columns. The view is defined WITH READ ONLY, preventing DML against it. The ETRM metadata records no documented referenced base objects under the owner field, so the dependency chain must be inferred from the embedded view text.

Key Columns

• STATUS_CODE_PK — Unique identifier for the status; the join key between the B and TL tables.
• MEANING / DESCRIPTION — Language-specific display text for the status.
• ENABLED_FLAG — Indicates whether the status is active and selectable.
• LEAD_FLAG — Marks statuses applicable to leads; the column matching the user's search.
• OPP_FLAG — Marks statuses applicable to opportunities.
• OPP_OPEN_STATUS_FLAG — Identifies statuses counted as open pipeline.
• OPP_DECISION_DATE_FLAG — Flags statuses where a decision date is captured.
• STATUS_RANK — Ordinal used to sequence statuses in reports.
• FORECAST_ROLLUP_FLAG — Determines forecast inclusion.
• WIN_LOSS_INDICATOR — Denotes closed/won or closed/lost outcomes.
• USAGE_INDICATOR — Usage classification for the status.
• Audit columns (LAST_UPDATE_DATE, CREATED_BY, etc.) for change tracking.

Common Use Cases and Queries

Typical usage involves joining this view to opportunity or lead fact tables to resolve status attributes for pipeline reporting. A common query filtering on lead_flag:

SELECT STATUS_CODE_PK, MEANING, STATUS_RANK
FROM   BIL_DIMV_OPTY_STATUSES
WHERE  LEAD_FLAG = 'Y'
AND    ENABLED_FLAG = 'Y'
ORDER BY STATUS_RANK;

Another frequent pattern identifies open opportunity statuses for forecast rollup:

SELECT STATUS_CODE_PK, MEANING
FROM   BIL_DIMV_OPTY_STATUSES
WHERE  OPP_OPEN_STATUS_FLAG = 'Y'
AND    FORECAST_ROLLUP_FLAG = 'Y';

Because the view is read-only and language-sensitive, it is well suited to parameterized reports where the session language determines returned meanings.