Results for “edw_bim_evnt_evtoffers_ltc”

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

Overview

EDW_BIM_EVNT_EVTOFFERS_LTC is a table owned by the BIM schema within the Oracle E-Business Suite Marketing Intelligence (Marketing) module. Its documented description identifies it as the "Event Offering Level Table of Event Dimension," a designation that reflects the object's origin in the Oracle Business Intelligence (OBI) / Enterprise Data Warehouse layer rather than in the operational transaction path. The LTC suffix conventionally denotes a load-time changed or slowly changing dimension construct, and the EDW_ prefix confirms distribution to the data warehouse. In the Oracle EBS 12.1.1 and 12.2.2 environments, the table holds event dimension rows that are resident in the transactional schema — specifically the AMS event offering entity — and are staged for analytical consumption.

Because the object is essentially a denormalized copy of the event offering dimension, changes to the underlying operational record propagate through the BIM extraction and load process. The heuristic Data Vault classification mined from the foreign-key structure is standalone, suggesting the table is best modeled as an independent entity or reference construct rather than as a dependent hub, link, or satellite; this classification is a modeling suggestion only.

Key Information Stored

The physical schema documents 78 columns. The most significant are:

• EVTOFFER_PK — the surrogate primary key for the event offering row.
• EVTOFFER_PK_KEY and EVENT_FK_KEY — business-key/key-mapping attributes; EVTOFFER_PK_KEY participates in both unique indexes EDW_BIM_EVNT_EVTOFFERS_LTC_U1 and EDW_BIM_EVNT_EVTOFFERS_LTC_U2, making it the strongest documented business-key candidate alongside EVTOFFER_PK.
• EVENT_FK and EVENT_HEADER_ID — the link back to the parent event, with EVENT_HEADER_ID carrying the documented foreign key to AMS_EVENT_HEADERS_ALL_B.
• EVTOFFER_ID, NAME, LEVEL_NAME, EVENT_OFFER_NAME, and EVTOFFER_DP — descriptive attributes that identify and describe the offering.
• ORG_ID and SECURITY_GROUP_ID — multi-org and security partitioning; SECURITY_GROUP_ID references FND_SECURITY_GROUPS.
• TIMEZONE_ID — references HZ_TIMEZONES and governs the interpretation of the date/time columns.
• REG_START_DATE, REG_END_DATE, EVENT_START_DATE, and EVENT_END_DATE — registration and event scheduling boundaries.
• REG_MINIMUM_CAPACITY, REG_MAXIMUM_CAPACITY, and REG_EFFECTIVE_CAPACITY — capacity planning measures.
• AUTO_REGISTER_FLAG, REG_WAITLIST_ALLOWED_FLAG, and REG_OVERBOOK_ALLOWED_FLAG — registration behaviour flags.
• LAST_UPDATE_DATE and CREATION_DATE — standard audit columns.

Several column names appear to carry typographical variance — for example EVENT_REQUIERED_FLAG versus EVENT_REQUIRED_FLAG — a condition worth noting when writing extraction logic.

Common Use Cases and Queries

The table is principally consumed for event effectiveness, registration capacity, and marketing campaign ROI reporting. A typical extraction joins the offering dimension to its parent event header:

SELECT o.evtoffer_pk, o.event_offer_name, o.org_id,
       h.event_header_id, o.event_start_date, o.event_end_date
FROM   bim.edw_bim_evnt_evtoffers_ltc o,
       ams_event_headers_all_b h
WHERE  o.event_header_id = h.event_header_id
AND    o.org_id = :p_org_id;

Capacity utilization can be assessed directly from the registration capacity columns, while waitlist and overbooking behaviour can be profiled using the corresponding flags. Timezone-aware scheduling reports should always join HZ_TIMEZONES on TIMEZONE_ID before presenting the event start and end dates.

Related Objects

• AMS_EVENT_HEADERS_ALL_B — parent event header; joined on EVENT_HEADER_ID.
• HZ_TIMEZONES — timezone reference; joined on TIMEZONE_ID.
• FND_SECURITY_GROUPS — security group definition; joined on SECURITY_GROUP_ID.
• AMS_EVENT_OFFERS_ALL_B / AMS_EVENT_OFFERS_ALL_TL — the transactional source offering entity from which this dimension is derived.
• AMS_EVENT_REGISTRATIONS_ALL_B — registration facts that reference the event offering.
• EDW_BIM_EVNT_LTC / EDW_BIM_EVENT_* — companion event-dimension tables in the BIM warehouse layer.