Search Results bil_value_type




Overview
Explore in AI Chat

Overview

APPS.BIL_DIMV_OPTY is a read-only database view in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments. It exposes a normalized dimension of "opportunity" or lead-related identifiers used by the EBS telephony and interaction-center stack, specifically within the BIL (telephony/integration) schema family. The view serves as a lookup-consistent dimension table for reporting and integration, presenting a unified list of lead identifiers drawn from the CRM leads table plus a synthetic sentinel row sourced from the FND_LOOKUPS lookup type BIL_VALUE_TYPE. This design allows downstream components to resolve a lead ID to a human-readable lead number and description consistently, while guaranteeing that the sentinel value -999 is always present. The view is defined WITH READ ONLY, so it cannot be used as a DML target and is intended strictly for query and reference purposes. The user search term "bil_value_type" corresponds directly to the lookup type referenced in the view's UNION ALL branch.

Underlying Base Objects

The documented view text identifies two base objects:

  • AS_LEADS_ALL (alias ALA) — the primary source of lead records, contributing LEAD_ID, LEAD_NUMBER, STATUS, and DESCRIPTION.
  • FND_LOOKUPS (alias FL) — the Oracle Application Object Library lookup table, filtered on LOOKUP_TYPE = 'BIL_VALUE_TYPE' and LOOKUP_CODE = '-999' to produce a single sentinel row.

The view is composed with a UNION ALL, combining the full lead set from AS_LEADS_ALL with the synthetic sentinel row. No additional base objects are documented in the ETRM metadata, and the "referenced base objects" field is recorded as empty, meaning the dependencies above are derived from the view text itself. Column-level lineage between the view and the base tables is straightforward, with DESCRIPTION resolved through NVL(ALA.DESCRIPTION, ALA.LEAD_NUMBER).

Key Columns

  • LEAD_ID — Numeric lead identifier. For rows sourced from AS_LEADS_ALL it is the native lead ID; for the sentinel row it is TO_NUMBER of the lookup code '-999', yielding -999.
  • LEAD_NUMBER — The lead's business-facing number (ALA.LEAD_NUMBER), or the lookup MEANING for the sentinel row.
  • STATUS — The lead status from AS_LEADS_ALL; NULL for the sentinel row, since the UNION ALL supplies an explicit NULL.
  • DESCRIPTION — A descriptive label. It resolves to ALA.DESCRIPTION when present, otherwise falls back to ALA.LEAD_NUMBER via NVL. For the sentinel row, it is the FND_LOOKUPS.DESCRIPTION for the '-999' entry.

Columns are positional in the view definition, so any consuming report or interface must map them in the documented order: LEAD_ID, LEAD_NUMBER, STATUS, DESCRIPTION.

Common Use Cases and Queries

This view is typically used when a report or integration needs a consistent enumeration of valid opportunity/lead identifiers, including the mandatory "-999" placeholder that downstream BIL logic expects. It is also used to validate that the BIL_VALUE_TYPE lookup is correctly configured. Representative queries include:

  • List all leads with their resolved labels:
    SELECT lead_id, lead_number, status, description FROM apps.bil_dimv_opty ORDER BY lead_id;
  • Isolate the sentinel row:
    SELECT * FROM apps.bil_dimv_opty WHERE lead_id = -999;
  • Validate the underlying lookup configuration:
    SELECT lookup_code, meaning, description FROM fnd_lookups WHERE lookup_type = 'BIL_VALUE_TYPE';
  • Join the view to transactional lead data to supply a guaranteed display label:
    SELECT t.lead_id, v.lead_number, v.description FROM apps.some_lead_transaction t, apps.bil_dimv_opty v WHERE v.lead_id = t.lead_id;

Because the view is read-only and built on a UNION ALL, queries should avoid unnecessary DISTINCT operations, as duplicate lead IDs are not deduplicated by the view itself. Access is generally governed by the APPS schema privileges and any custom grants applied in the 12.1.1 or 12.2.2 instance.