Search Results bim_dimv_offers




Overview
Explore in AI Chat

Overview

BIM_DIMV_OFFERS is a read-only dimensional view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It is delivered as part of the BIM - Marketing Intelligence product family, a component of the Oracle Marketing and Advanced Marketing Intelligence footprint. As its description states, the view "shows all marketing offers," presenting a consolidated, denormalized list of offer records originating from Oracle Advanced Marketing and Oracle Trade Management structures. The object is registered with a status of VALID and is qualified WITH READ ONLY, meaning it is intended strictly for query and reporting consumption rather than DML.

In the EBS reporting and integration layer, BIM_DIMV_OFFERS functions as a conformed dimension source for marketing analytics. BI Publisher reports, Oracle Business Intelligence (OBIEE) repository builds, and custom Star/OLAP models can join it to fact objects such as activity, campaign response, and revenue measures. Because the view normalizes the offer identity into a fixed VARCHAR2 key (via TO_CHAR) and appends a synthetic "-999" placeholder row, it supports both natural joins to QP list headers and safe outer joins against fact tables where an offer may be unassigned.

Underlying Base Objects

The documented base objects referenced by BIM_DIMV_OFFERS are AMS_ACT_OFFERS (accessed as a SYNONYM), FND_GLOBAL (PACKAGE), FND_LOOKUPS (VIEW), and QP_LIST_HEADERS_VL (VIEW). Operationally, the core of the view is a UNION ALL of two branches:

  • Primary branch — a join between AMS_ACT_OFFERS (aliased AAO) and QP_LIST_HEADERS_VL (aliased QLH), linked on AAO.QP_LIST_HEADER_ID = QLH.LIST_HEADER_ID. AMS_ACT_OFFERS stores the marketing activity-to-offer association, while QP_LIST_HEADERS_VL supplies the multilingual (VL) offer/list header name and description.
  • Placeholder branch — a single synthetic row sourced from FND_LOOKUPS filtered by LOOKUP_TYPE = 'BIM_VALUE_TYPE' and LOOKUP_CODE = '-999', providing the "no offer / unknown" member required by the BI dimension.

The presence of FND_GLOBAL in the reference list reflects standard EBS multi-org and language context resolution (ORG_ID, LANGUAGE) applied through the VL view and lookup access.

Key Columns

  • ID — a character surrogate of ACTIVITY_OFFER_ID produced by TO_CHAR, giving a uniform VARCHAR2 key for dimensional joins.
  • VALUE — the display label, derived from SUBSTR(QLH.NAME, 1, 80) in the primary branch and from LOOKUP MEANING in the placeholder branch.
  • OFFER_ID — the numeric ACTIVITY_OFFER_ID (a literal -999 in the placeholder row).
  • OFFER_TYPE — classifies the offer (for example, promotion versus other marketing offer categories).
  • OFFER_CODE — the business-facing offer code from AMS_ACT_OFFERS.
  • PRIMARY_OFFER_FLAG — indicates whether the offer is the primary offer on its parent activity.
  • LIST_HEADER_ID — the QP_LIST_HEADERS identifier linking the offer to its Trade Management list/price list header.
  • OFFER_NAME — the full offer name (QLH.NAME) without truncation.
  • DESCRIPTION — the offer description from QP_LIST_HEADERS_VL; null in the placeholder row.

Common Use Cases and Queries

Typical scenarios include populating an offer dimension in a marketing data mart, validating which offers are associated with trade promotions, and driving drill-down from campaign response facts. The placeholder "-999" row is especially useful when joining to facts where no offer was captured, preventing loss of those rows.

To retrieve all offers:

  • SELECT ID, VALUE, OFFER_ID, OFFER_NAME, LIST_HEADER_ID FROM APPS.BIM_DIMV_OFFERS ORDER BY OFFER_ID;

To restrict to real offers only (excluding the placeholder) and filter by type:

  • SELECT OFFER_ID, OFFER_CODE, OFFER_NAME FROM APPS.BIM_DIMV_OFFERS WHERE OFFER_ID > 0 AND OFFER_TYPE = :p_type;

To resolve a specific offer's identity from its list header:

  • SELECT ID, VALUE, DESCRIPTION FROM APPS.BIM_DIMV_OFFERS WHERE LIST_HEADER_ID = :p_list_header_id;

Because the view is WITH READ ONLY, all access must be SELECT-only. Grants and synonyms are typically managed through the APPS schema in line with standard EBS security practice.