Search Results bim_dimv_campaigns

Overview

BIM_DIMV_CAMPAIGNS is a read-only dimensional view owned by the APPS schema in Oracle E-Business Suite releases 12.1.1 and 12.2.2. It belongs to the BIM – Marketing Intelligence product family, the Oracle Marketing analytics layer that supplies campaign, channel, and response data to the Business Intelligence (BI) warehouse and to Oracle Marketing Online (AMO) reporting. The view "shows all campaigns" by presenting a denormalized, flattened projection of campaign header data together with the human-readable name and description of each campaign's user-defined status.

Its principal role is as a staging or dimension source. Extract routines, OBIEE/Oracle BI Publisher reports, and custom analytics dashboards query BIM_DIMV_CAMPAIGNS to obtain one row per campaign, keyed by CAMPAIGN_ID, with resolved status text rather than raw status identifiers. Because the view joins the campaign multi-language view to the user-status multi-language view, it returns translated NAME and DESCRIPTION values for the current session language, which makes it suitable for multi-language reporting without additional lookup logic in the consuming query.

Underlying Base Objects

The view is defined over two documented base objects:

• AMS_CAMPAIGNS_VL (aliased AMC) — the multi-language campaign view in Oracle Marketing. It supplies campaign identity, name, description, status identifier, status code and date, rollup and source codes, campaign type, parent campaign, and forecasted and actual execution dates.
• AMS_USER_STATUSES_VL (aliased AUS) — the multi-language user-status view. It is joined on AMC.USER_STATUS_ID = AUS.USER_STATUS_ID and supplies the STATUS_NAME and STATUS_DESCRIPTION columns.
• FND_LOOKUPS — a seeded lookup row for LOOKUP_TYPE BIM_VALUE_TYPE and LOOKUP_CODE -999 is unioned into the result set to provide a placeholder member (the "-999" / "Unknown" value type), a common pattern in BIM dimensional views for handling missing or non-applicable dimension values.

The WHERE clause also enforces SHOW_CAMPAIGN_FLAG = 'Y', restricting output to campaigns flagged as displayable. The view is declared WITH READ ONLY, so it cannot be used as a DML target.

Key Columns

• CAMPAIGN_ID — campaign surrogate key, exposed both as a character value (TO_CHAR) and as the number used for joins. The '-999' placeholder row represents an unknown campaign.
• CAMPAIGN_NAME — campaign name, truncated to 80 characters via SUBSTR.
• CAMPAIGN_DESCRIPTION / DESCRIPTION — free-text description of the campaign.
• STATUS_NAME and STATUS_DESCRIPTION — the resolved status text from AMS_USER_STATUSES_VL; these are the columns most often referenced when users search on "status_description".
• USER_STATUS_ID, STATUS_CODE, STATUS_DATE — the raw status identifier, status code, and the date the status was applied.
• CAMPAIGN_TYPE and CAMPAIGN_TYPE_NAME — campaign classification code and its resolved "-999"-safe counterpart.
• ROLLUP_TYPE, SOURCE_CODE, PARENT_CAMPAIGN_ID — hierarchy and provenance attributes supporting campaign rollup analysis.
• FORECASTED_EXEC_START_DATE, FORECASTED_EXEC_END_DATE, ACTUAL_EXEC_START_DATE, ACTUAL_EXEC_END_DATE — scheduling and execution date pairs.

Common Use Cases and Queries

Typical usage includes campaign status dashboards, campaign dimension loads into the BIM star schema, and ad hoc campaign inventories. A representative query filtering on the documented "status_description" concept is:

• SELECT campaign_id, campaign_name, status_name, status_description FROM apps.bim_dimv_campaigns WHERE status_description IS NOT NULL;
• SELECT campaign_id, campaign_name, status_name FROM apps.bim_dimv_campaigns WHERE status_code = 'ACTIVE';
• Filtering by campaign type or execution window: SELECT campaign_id, campaign_name, campaign_type_name, actual_exec_start_date FROM apps.bim_dimv_campaigns WHERE actual_exec_start_date >= :p_from_date;
• Excluding the placeholder member in dimension loads: WHERE campaign_id <> '-999';

Consumers should treat the '-999' row as the standard unknown member and preserve it in dimension extractions, while ad hoc reports typically filter it out.