Search Results ordered_date

Overview

The APPS.BIM_FCTV_SALES_CYCLE view is a denormalized fact-style object belonging to the BIM – Marketing Intelligence product family, now marked Obsolete in Oracle EBS 12.1.1 and 12.2.2. Its documented purpose is to feed concurrent programs that load the Marketing Intelligence summary tables. Rather than capturing live transactional state, the view consolidates lead, opportunity, quote, order, campaign, and geography attributes into a single fact-shaped result set that ETL processes can consume directly.

The view spans the full sales cycle — from a sales lead through its associated opportunity, related quote, and eventual order header — and enriches each row with campaign, media, channel, market segment, and location context. This makes it a canonical source for funnel, attribution, and cycle-time reporting within the Marketing Intelligence schema.

Underlying Base Objects

The view is defined in the APPS schema and references the following documented base objects:

• AS_SALES_LEADS — primary driver of the sales-cycle rows; supplies lead identity, source promotion, customer, address, and creation date.
• BIM_SOURCE_CODE_DETAILS — outer-joined on SOURCE_PROMOTION_ID to supply campaign, media, and channel keys.
• AMS_PARTY_MARKET_SEGMENTS — outer-joined on party and active date range to resolve the market segment.
• AS_SALES_LEAD_OPPORTUNITY — bridges leads to opportunities.
• AS_LEADS_ALL — supplies the opportunity (lead) record and its creation date.
• ASO_QUOTE_RELATED_OBJECTS — aliased twice to traverse the OPPORTUNITY → QUOTE and QUOTE → ORDER relationships.
• ASO_I_ORDER_HEADERS_V — provides the order header and the ORDERED_DATE.
• HZ_LOCATIONS — supplies country, state, city, and postal code for the lead address.

The first SELECT block joins these objects with outer joins to preserve leads lacking downstream artifacts, then UNIONs a second block for leads converted directly to orders.

Key Columns

• ORDERED_DATE — the order header ordered date from ASO_I_ORDER_HEADERS_V; the column most frequently searched, used for sales-cycle timing and period reporting.
• CAMPAIGN_ID, MEDIA_ID, CHANNEL_ID — campaign attribution keys, defaulted to -999 when unknown.
• MARKET_SEGMENT_ID — resolved from AMS_PARTY_MARKET_SEGMENTS.
• LOCATION — a composite string (country >> state >> city >> postal code), or -999>>-999>>-999>>-999 when absent.
• SALES_CHANNEL_CODE, LEAD_ID, LEAD_CREATION_DATE — lead-level descriptors.
• OPPORTUNITY_ID, OPPR_CREATION_DATE — opportunity linkage and creation.
• ORDER_HEADER_ID, ORDERED_DATE — the terminal order facts.

Common Use Cases and Queries

Typical usage is concurrent-program loading of Marketing Intelligence summaries, ad-hoc cycle-time analysis from lead creation to ORDERED_DATE, and campaign attribution reporting.

SELECT CAMPAIGN_ID,
       COUNT(*)            AS CYCLE_ROWS,
       MIN(ORDERED_DATE)   AS FIRST_ORDER,
       MAX(ORDERED_DATE)   AS LAST_ORDER
FROM   APPS.BIM_FCTV_SALES_CYCLE
WHERE  ORDERED_DATE IS NOT NULL
GROUP  BY CAMPAIGN_ID;

SELECT LEAD_ID, OPPORTUNITY_ID, ORDER_HEADER_ID,
       LEAD_CREATION_DATE, ORDERED_DATE,
       ORDERED_DATE - LEAD_CREATION_DATE AS CYCLE_DAYS
FROM   APPS.BIM_FCTV_SALES_CYCLE
WHERE  ORDERED_DATE BETWEEN :p_start AND :p_end;

Because the object is obsolete and fact-oriented, it should be treated as a read-only integration source.