Search Results bil_dimv_opty




Overview
Explore in AI Chat

Overview

BIL_DIMV_OPTY is a read-only database view historically shipped as part of the Oracle E-Business Suite Sales Intelligence (BIL) module, which is now designated as obsolete in releases 12.1.1 and 12.2.2. Its purpose is to expose a consolidated list of sales opportunity identifiers and names, drawing on lead records sourced from the AS_LEADS_ALL table. The view serves as a lightweight dimensional source for reporting and integration scenarios where only the opportunity key and a human-readable descriptive name are required, without the full transactional weight of the underlying lead entity.

Because the BIL product family is obsolete, the view is treated as legacy metadata. Users searching for lead_number will find it referenced here as one of the four exposed columns, functioning as the descriptive identifier for an opportunity or lead.

Underlying Base Objects

The view text is a UNION ALL of two SELECT statements:

  • The first branch selects from AS_LEADS_ALL, the canonical Oracle CRM/TeleSales leads table. It retrieves ALA.LEAD_ID, ALA.LEAD_NUMBER, ALA.STATUS, and a computed description using NVL(ALA.DESCRIPTION, ALA.LEAD_NUMBER).
  • The second branch selects from FND_LOOKUPS, the standard Oracle Application Object Library lookup table, filtered where LOOKUP_TYPE = 'BIL_VALUE_TYPE' and LOOKUP_CODE = '-999'. This branch injects a sentinel or "unknown" opportunity row, casting FL.LOOKUP_CODE to a number for LEAD_ID and using FL.MEANING as LEAD_NUMBER.

The ETRM metadata lists no additional referenced base objects beyond these, and the view is marked "Not implemented in this database" in the source documentation, indicating it may not exist in every EBS instance.

Key Columns

  • LEAD_ID — Numeric primary identifier for the lead/opportunity. In the second UNION branch it is derived by converting the lookup code '-999' to a number.
  • LEAD_NUMBER — The user-facing lead number, or, in the sentinel branch, the FND lookup MEANING value. This is the column most often searched under the term "lead_number."
  • STATUS — The lead's status code from AS_LEADS_ALL; populated as NULL in the sentinel FND_LOOKUPS branch.
  • DESCRIPTION — A display-friendly description; the NVL logic falls back to LEAD_NUMBER when the underlying description is null.

Common Use Cases and Queries

Typical usage centers on opportunity pickers, LOVs, and denormalized reporting where only ID and name are needed. Example:

  • SELECT LEAD_ID, LEAD_NUMBER, STATUS, DESCRIPTION FROM BIL_DIMV_OPTY WHERE LEAD_NUMBER = :p_lead_number; — resolve a lead number to its ID.
  • SELECT LEAD_NUMBER, DESCRIPTION FROM BIL_DIMV_OPTY ORDER BY LEAD_NUMBER; — populate a lookup list including the "-999" unknown entry.
  • Joining the view to BIL fact tables on LEAD_ID for opportunity-level dimensional lookups.

Note the WITH READ ONLY clause: no DML is permitted. In 12.2.2 the view remains documented only as obsolete BIL metadata, and no base objects are registered in ETRM.