Results for “bim_fctv_orders”
4 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
BIM_FCTV_ORDERS is a reporting view belonging to the BIM (Marketing Intelligence) product family, a legacy Oracle E-Business Suite module now classified as obsolete. The view is documented as being "used by the concurrent programs to load the summary tables," which establishes its role as an intermediate extraction layer within the BIM marketing analytics pipeline rather than as an operational or transactional object. In EBS 12.1.1 and 12.2.2, the view is not implemented in the database by default; ETRM explicitly records "Not implemented in this database," meaning it must be created by the BIM installation or patching process before any dependent concurrent program can execute successfully.
The view flattens order header data from the order capture schema into a denormalized fact structure suitable for loading marketing summary tables. It enriches each qualifying order with geography, campaign source, and market segment attributes, positioning it conceptually between operational order data and the BIM fact tables.
Underlying Base Objects
The ETRM metadata lists no documented base objects, but the embedded view text reveals the operative dependencies:
- ASO_I_ORDER_HEADERS_V (aliased ODH) — the primary driver, supplying header ID, order number, party and account identifiers, sales channel, ordered date, org, currency, geographies, exchange rate and exchange type, return reason, and audit dates.
- BIM_SOURCE_CODE_DETAILS (aliased SRCD) — outer-joined on MARKETING_SOURCE_CODE_ID = SOURCE_CODE_ID, supplying campaign, media, channel, event offer, and event identifiers.
- AMS_PARTY_MARKET_SEGMENTS (aliased PMKT) — outer-joined by party and effective date range, supplying the market segment ID where MARKET_SEGMENT_FLAG = 'Y'.
- BIM_GEO_PKG — a PL/SQL package invoked twice to resolve bill-to and ship-to geography codes.
Key Columns
- HEADER_ID / ORDER_NUMBER — order header primary key and human-readable order identifier.
- CUSTOMER_ID / ACCOUNT_ID — party and customer account identifiers.
- SALES_CHANNEL_CODE — NVL-defaulted to '-999' when null.
- ORDERED_DATE / ORG_ID / TRANSACTION_CURRENCY — core order attributes.
- BILL_TO_GEOGRAPHY_CODE / SHIP_TO_GEOGRAPHY_CODE — geography codes derived via BIM_GEO_PKG.
- HDR_SOURCE_CODE_ID, CAMPAIGN_ID, MEDIA_ID, CHANNEL_ID, EVENT_OFFER_ID, EVENT_ID, MARKET_SEGMENT_ID — marketing attribution dimensions; all default to -999 when unmatched.
- EXCHANGE_RATE / EXCHANGE_TYPE_CODE — the currency conversion rate and the exchange rate type (for example, Corporate, Spot, or User-defined) applied to the order. The exchange_type_code column is the specific attribute most often queried during currency and revenue analysis.
- RETURN_REASON_CODE / CREATION_DATE / LAST_UPDATE_DATE — supporting audit and return context.
Common Use Cases and Queries
The view is consumed primarily by BIM concurrent programs loading summary tables, and secondarily by ad hoc marketing analytics that require booked, non-cancelled, non-return orders with attribution dimensions. The defining filters are ORDER_CATEGORY_CODE <> 'RETURN', BOOKED_FLAG = 'Y', and NVL(CANCELLED_FLAG,'N') = 'N'.
A typical query isolating exchange rate type is:
SELECT header_id, order_number, transaction_currency, exchange_rate, exchange_type_code FROM bim_fctv_orders WHERE exchange_type_code IS NOT NULL AND ordered_date >= :p_start_date AND ordered_date < :p_end_date ORDER BY ordered_date;SELECT exchange_type_code, transaction_currency, COUNT(*) order_count, SUM(exchange_rate) rate_sum FROM bim_fctv_orders GROUP BY exchange_type_code, transaction_currency;
Because of the outer joins and the -999 defaults, consumers should treat -999 as "unknown" rather than a valid dimension member. Administrators must also confirm the view exists in the target instance, since ETRM documents it as not implemented by default and as Oracle-proprietary, confidential material.
-
View: BIM_FCTV_ORDERS 12.1.1
Used by the concurrent programs to load the summary tables.
Not implemented in this database·Explore BIM module →
-
View: BIM_FCTV_ORDERS 12.2.2
Used by the concurrent programs to load the summary tables.
Not implemented in this database·Explore BIM module →
-
12.1.1 FND Design Data 12.1.1
-
12.2.2 FND Design Data 12.2.2