Search Results bim_fctv_oppr_cycle




Overview
Explore in AI Chat

Overview

BIM_FCTV_OPPR_CYCLE is a read-only view owned by the APPS schema within the BIM – Marketing Intelligence product family of Oracle E-Business Suite. Its documented purpose is to support concurrent programs that load summary tables, meaning it functions as an extraction and staging layer rather than as an end-user-facing reporting object. The view consolidates lead, opportunity, quote, order, market segment, and geography data into a single flattened result set that downstream fact and summary loaders can consume in one pass.

The name follows the BIM fact-view naming convention: BIM_FCTV denotes a fact view, and OPPR_CYCLE denotes the opportunity cycle — the chain of events that begins with a lead and terminates in a booked order. The view is declared WITH READ ONLY, so it cannot be used as an update target; it is strictly a query surface. In both 12.1.1 and 12.2.2 it is reported as VALID, and the documented base objects list is identical across the two releases, which simplifies migration and testing.

Underlying Base Objects

The ETRM metadata records six referenced base objects, all resolved through APPS synonyms except one, which is itself a view:

  • AS_LEADS_ALL (synonym) — the driving table, supplying lead/opportunity identity and creation date.
  • BIM_SOURCE_CODE_DETAILS (synonym) — resolves the source promotion to campaign, media, and channel identifiers.
  • AMS_PARTY_MARKET_SEGMENTS (synonym) — supplies market segment membership, joined outer to the lead by party and effective date range.
  • ASO_QUOTE_RELATED_OBJECTS (synonym) — joined twice (aliases ASO1 and ASO2) to bridge opportunity to quote and quote to order.
  • ASO_I_ORDER_HEADERS_V (view) — provides the order header and ordered date.
  • HZ_LOCATIONS (synonym) — supplies country, state, city, and postal code for the lead address.

Relationships are enforced through inner joins for the opportunity-quote-order chain and outer joins (noted with the (+) operator) for market segment, source code, and location lookups, ensuring a lead is not dropped merely because an optional dimension is missing.

Key Columns

Common Use Cases and Queries

Typical usage is inside concurrent program logic that aggregates opportunity-to-order conversion data. A basic extract resembles:

  • SELECT campaign_id, market_segment_id, COUNT(DISTINCT opportunity_id) opps, COUNT(DISTINCT order_header_id) orders FROM bim_fctv_oppr_cycle GROUP BY campaign_id, market_segment_id;
  • SELECT opportunity_id, order_header_id, ordered_date - oppr_creation_date cycle_days FROM bim_fctv_oppr_cycle WHERE order_header_id > 0;
  • SELECT geography_code, sales_channel_code, COUNT(*) FROM bim_fctv_oppr_cycle GROUP BY geography_code, sales_channel_code;

Because sentinel values of -999 replace missing dimensions, loaders should filter or map them explicitly. Queries joining this view to other BIM fact views should key on OPPORTUNITY_ID or ORDER_HEADER_ID, which form the natural grain of the result set.