Results for “bim_dimv_event_offers”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

BIM_DIMV_EVENT_OFFERS is a read-only dimensional view owned by the APPS schema in Oracle E-Business Suite, belonging to the BIM – Marketing Intelligence product family. As documented in ETRM metadata for releases 12.1.1 and 12.2.2, the view "shows all event offers," meaning it exposes the header-level (MAIN event level) records from the event offers entity, filtered to the top of the offer hierarchy. The view is designed primarily as a dimensional source for Marketing Intelligence reporting and ETL processes, presenting a flat, denormalized projection of event offer data. Notably, the view applies the WITH READ ONLY clause, so it cannot be used as the target of DML and is intended strictly for query and extraction. Because it prefixes the primary and parent identifiers with entity tags ('EVEO' for event offers, 'EVEH' for event headers), the view produces surrogate-style key values suitable for joining to other dimensional views or staging tables in a data warehouse model.

Underlying Base Objects

The view is defined over two documented objects: AMS_EVENT_OFFERS_VL (a view, providing the localized event offer data) and HZ_LOCATIONS (a synonym, providing location attributes). AMS_EVENT_OFFERS_VL is the multi-language variant of the AMS event offers table, so the BIM view inherits the language-specific values handled by that VL view. HZ_LOCATIONS is joined with an outer join (+) on EVENT_LOCATION_ID = LOCATION_ID, meaning offers without a resolved location still appear, with location-derived columns returned as NULL. The filter predicate EVTO.EVENT_LEVEL = 'MAIN' restricts rows to main event offers, excluding sub-offer records that may exist within the same base entity. No joins to AMS_EVENT_HEADERS are performed directly; instead, the header relationship is preserved only through the derived EVENT_HEADER_ID column.

Key Columns

• ID – Surrogate key formed as 'EVEO' || TO_CHAR(EVENT_OFFER_ID), used as the dimensional primary identifier.
• VALUE – The first 80 characters of EVENT_OFFER_NAME, typically consumed as the display value in LOVs and list reports.
• EVENT_OFFER_ID – Same tagged surrogate as ID; the offer's logical key.
• EVENT_OFFER_NAME / EVENT_OFFER_DESCRIPTION – Descriptive attributes of the offer.
• EVENT_HEADER_ID – Tagged as 'EVEH' || TO_CHAR(EVENT_HEADER_ID), the foreign key linking the offer to its parent event header. This is the column users most commonly search for when tracing an offer back to its event.
• PARENT_EVENT_OFFER_ID / MAIN_EVENT_OFFER_ID – Tagged parent reference or NULL where no parent exists; both are derived via DECODE.
• EVENT_OFFER_SOURCE_CODE, EVENT_OFFER_STATUS_CODE, EVENT_OFFER_STATUS_DATE – Source, workflow status, and status timestamp.
• EVENT_OFFER_ACTIVE_FLAG, REG_REQUIRED_FLAG, EVENT_STAND_ALONE_FLAG – Boolean control flags.
• EVENT_LOCATION_CODE – Concatenation of COUNTRY, STATE, CITY, and POSTAL_CODE from HZ_LOCATIONS, prefixed with '>>' separators and returned uppercase.
• EVENT_LANGUAGE_CODE, EVENT_OFFER_DURATION, EVENT_OFFER_START_DATE, EVENT_OFFER_END_DATE, REG_START_DATE, REG_END_DATE – Scheduling and registration windows.
• INVENTORY_ITEM_ID – Inventory item associated with the offer, where applicable.
• ORG_ID – Always NULL in the view text, included for structural compatibility with multi-org reporting.

Common Use Cases and Queries

Typical usage includes populating Marketing Intelligence dimensions, validating offer-to-event relationships, and extracting active offers for registration reporting. The most frequent query pattern filters or groups by the event header surrogate:

• Retrieve all offers for a given event header.
• List active, registration-required offers with their registration windows.
• Resolve an offer's location code and language for regional reporting.

Sample SQL:

SELECT event_offer_id, event_offer_name, event_header_id, event_offer_status_code
FROM apps.bim_dimv_event_offers
WHERE event_header_id = 'EVEH' || TO_CHAR(:p_event_header_id)
AND event_offer_active_flag = 'Y';

SELECT event_offer_id, event_offer_name, event_location_code,
event_offer_start_date, event_offer_end_date, reg_start_date, reg_end_date
FROM apps.bim_dimv_event_offers
WHERE reg_required_flag = 'Y'
AND event_offer_end_date >= SYSDATE
ORDER BY event_offer_start_date;

Because ORG_ID is hard-coded to NULL and the view is READ ONLY, it should be used only for query and extraction workloads, not for transactional updates or multi-org-secured DML.