Search Results bil_dimv_salesforce




Overview
Explore in AI Chat

Overview

BIL_DIMV_SALESFORCE is a read-only dimensional view that was delivered as part of the BIL – Sales Intelligence module in Oracle E-Business Suite 12.1.1 and 12.2.2. Its purpose is to expose salesforce (sales representative) data in a flattened, dimension-oriented shape suitable for loading into an Oracle Business Intelligence / Sales Intelligence data warehouse. The view presents one row per sales resource, combining the resource definition, the assigned person, the salesrep association, and the primary assignment record into a single denormalized structure with surrogate keys and descriptive attributes.

The object is classified as Obsolete in the current ETRM documentation, and the implementation notes explicitly state that it is not implemented in this database. Consequently, BIL_DIMV_SALESFORCE should be treated as a legacy artifact. It is documented here for reference, for historical migration analysis, or for customers still running a pre-obsolete 12.1.1 or 12.2.2 instance in which the Sales Intelligence schemas remain deployed.

Underlying Base Objects

Although the ETRM metadata records no formal parent objects, the view text defines a UNION ALL of two branches, and the first branch references four base tables by join:

The second UNION ALL branch reads solely from FND_LOOKUPS, restricted to LOOKUP_TYPE = 'BIL_VALUE_TYPE' and LOOKUP_CODE = '-999'. This branch manufactures a single "unknown / no salesforce" placeholder row so that fact tables can reference a default dimension member.

Key Columns

The view exposes the following columns as documented in the metadata:

  • SALESFORCE_PK – surrogate primary key for the salesforce dimension. In the resource branch it maps to RESOURCE_ID; in the placeholder branch it is derived from lookup code '-999'.
  • RESOURCE_NUMBER – the externally visible resource identifier.
  • LAST_NAME / FIRST_NAME / FULL_NAME – person name attributes, with FULL_NAME built by concatenating first and last name.
  • SALESFORCE_STATUS – status carried from the salesrep record, or the lookup meaning for the '-999' row.
  • SALESFORCE_TYPE / CATEGORY – classification of the resource or salesrep.
  • COMPENSATION_CURRENCY_CODE – the currency in which the resource is compensated.
  • START_DATE_ACTIVE / END_DATE_ACTIVE – effective date bounds of the salesforce record, useful for slowly changing dimension logic.
  • EMPLOYMENT_STATUS_FK / JOB_FK / MANAGING_EMPLOYEE_ID – foreign-key attributes linking to other dimensions (assignment employment category, job, and managing employee).
  • ID / VALUE – generic key/value pairs used by the Sales Intelligence tooling; ID is the string form of the key and VALUE the display value.

Common Use Cases and Queries

Typical use is populating or validating the salesforce dimension in the Sales Intelligence warehouse, and joining sales facts to rep attributes. Because the view is obsolete and may not exist, queries should be run only in environments where BIL is still installed.

List active sales reps and their managing employee:

  • SELECT resource_number, full_name, salesforce_status, managing_employee_id FROM bil_dimv_salesforce WHERE salesforce_pk > 0 ORDER BY full_name;

Retrieve the placeholder member for fact-table defaults:

  • SELECT salesforce_pk, value FROM bil_dimv_salesforce WHERE salesforce_pk = -999;

Filter reps whose active window covers a given period:

  • SELECT resource_number, full_name, start_date_active, end_date_active FROM bil_dimv_salesforce WHERE SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE);