Results for “bim_oppr_cycle_summ”

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

Overview

BIM.BIM_OPPR_CYCLE_SUMM is a summarized reporting table within the Oracle E-Business Suite Marketing Intelligence (BIM) product family. Its role is to hold pre-aggregated opportunity-to-order cycle measurements, segmented by user-defined periods and a defined set of marketing dimensions (campaign, media, channel, market segment, geography, and sales channel). This pre-aggregation makes it suitable for high-volume analytical reporting where querying transactional opportunity tables directly would be prohibitively expensive.

The table is populated by the BIM_OPPR_CYCLE_SUMM_PKG concurrent program, which performs the aggregation and inserts/updates rows according to the package logic. Because it is a periodic summary, its contents are refreshed on each run of the concurrent program rather than being maintained transactionally.

Under Data Vault modeling heuristics (mined from the FK structure rather than declared by Oracle), the table is classified as standalone. It does not function as a hub, link, or satellite: it holds no natural business keys distributed across related hubs, and its only attested foreign key is to a security/access-control reference table. In practical terms it is best treated as a periodic fact-plus-dimension summary — a denormalized analytic aggregate rather than a normalized master or relationship entity.

Key Information Stored

The physical schema documents 20 columns. The most business-significant are the dimensional and metric columns that define the grain of the summary:

• CAMPAIGN_ID — the marketing campaign to which the summarized cycles belong.
• PERIOD_NAME, PERIOD_START_DATE, PERIOD_END_DATE — the user-defined period label and its bounding dates, which establish the temporal grain.
• MEDIA_ID, CHANNEL_ID, MARKET_SEGMENT_ID — the media, channel, and market-segment dimensions.
• GEOGRAPHY_CODE, SALES_CHANNEL_CODE — geographic and sales-channel dimensional attributes.
• OPPR_TO_ORDER_CYCLE — the core measure: the summarized opportunity-to-order cycle value for the dimension combination and period.
• SECURITY_GROUP_ID — the descriptive/reference FK to FND_SECURITY_GROUPS, controlling row-level access by security group.

Audit and concurrency columns include CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE. These identify the concurrent request and program that last populated the row, supporting traceability back to the BIM_OPPR_CYCLE_SUMM_PKG run.

No single surrogate primary-key column is documented. Instead, the unique index BIM_OPPR_CYCLE_SUMM_U1 establishes the business-key candidate over PERIOD_NAME, PERIOD_START_DATE, PERIOD_END_DATE, CAMPAIGN_ID, MEDIA_ID, CHANNEL_ID, MARKET_SEGMENT_ID, GEOGRAPHY_CODE, and SALES_CHANNEL_CODE. This composite key defines the granularity at which one OPPR_TO_ORDER_CYCLE measure is stored.

Common Use Cases and Queries

Typical usage centers on marketing performance reporting and cycle-time trending. A representative query aggregates the measure across campaigns for a selected period:

• SELECT campaign_id, period_name, SUM(oppr_to_order_cycle) FROM bim.bim_oppr_cycle_summ WHERE period_name = :p GROUP BY campaign_id, period_name;
• Comparisons across media and geography: group by media_id, geography_code to identify which combinations yield shorter opportunity-to-order cycles.
• Trend analysis by joining on period_start_date/period_end_date to plot cycle movement over successive periods.
• Security-scoped extracts filtered by security_group_id to restrict reporting to the authorized security group.
• Refresh validation: querying request_id and program_update_date to confirm that the BIM_OPPR_CYCLE_SUMM_PKG concurrent program completed for the current period.

Related Objects

• FND_SECURITY_GROUPS — referenced via BIM_OPPR_CYCLE_SUMM.SECURITY_GROUP_ID = FND_SECURITY_GROUPS.SECURITY_GROUP_ID; the sole documented foreign key and the basis for the standalone Data Vault classification.
• BIM_OPPR_CYCLE_SUMM_PKG — the concurrent program/package responsible for populating the table.
• FND_CONCURRENT_REQUESTS — join on REQUEST_ID to trace the populating run.
• FND_APPLICATION and FND_CONCURRENT_PROGRAMS — join on PROGRAM_APPLICATION_ID and PROGRAM_ID to resolve program identity.
• Campaign, media, channel, market-segment, and geography dimension objects referenced by the ID and code columns in the business-key unique index.