Results for “bim_fctv_rev_cogs”

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

Overview

BIM_FCTV_REV_COGS is a reporting view historically shipped with the Oracle E-Business Suite Marketing Intelligence (BIM) module. As the ETRM metadata records, BIM is an obsolete product in the 12.1.1 and 12.2.2 releases, and this view is documented as "Not implemented in this database." Its stated purpose is to be consumed by concurrent programs that load the summary tables — a common pattern in Oracle Business Intelligence (BIS) where a fact-style view pre-joins transactional and dimensional data so that the summarization program can select clean, denormalized rows.

The name decomposes into the two financial measures it carries: revenue and cost of goods sold (COGS). It is a margin-analysis view that pairs sales-channel, customer, item, campaign, and geography attributes with invoiced revenue and COGS amounts, forming the input shape for a revenue/COGS margin fact. Because it depends on the obsolete BIM schema, it should be treated as reference material for legacy environments rather than an object to query in current implementations.

Underlying Base Objects

The ETRM "referenced base objects" list is empty, but the view text exposes its driving table: CST_BIS_MARGIN_SUMMARY, which supplies sales channel, customer, inventory item, organization, GL date, period, invoiced amount, and COGS amount. This is the key link to the user's search term, "cst_bis_margin_summary." The remaining joins bring in supporting dimensions:

• HR_ORGANIZATION_INFORMATION and GL_SETS_OF_BOOKS — relate the legal entity to its set of books for currency conversion.
• ASO_I_ORDER_HEADERS_V — supplies the party/customer identifier (ODH.PARTY_ID).
• BIM_SOURCE_CODE_DETAILS — resolves marketing campaign, media, channel, event, offer, and event identifiers from the source code.
• AMS_PARTY_MARKET_SEGMENTS and AMS_PARTY_SOURCES — provide market-segment and source-list attribution.
• Oracle APIs — GL_CURRENCY_API for conversion, JTF_BIS_UTIL for profile values, and BIM_GEO_PKG, BIM_TARGET_SEGMENT_PKG for derived geography and target-segment codes.

Key Columns

• SALES_CHANNEL_CODE — sales channel; NVL'd to '-999' when unknown.
• ACCOUNT_ID / CUSTOMER_ID — the customer account (CST.CUSTOMER_ID) and party (ODH.PARTY_ID).
• INVENTORY_ITEM_ID / ITEM_ORGANIZATION_ID — parent item and parent organization, defaulted to -999.
• ORG_ID, TRANSACTION_DATE, MONTH_PERIOD_NAME — operating unit, GL date, and period name.
• CAMPAIGN_ID, MEDIA_ID, CHANNEL_ID, EVENT_OFFER_ID, EVENT_ID — marketing attribution dimensions from the source code.
• MARKET_SEGMENT_ID, TARGET_SEGMENT_ID, SOURCE_LIST_ID, SOURCE_CODE_ID — segmentation and list-attribution keys.
• BILL_TO_GEOGRAPHY_CODE, SHIP_TO_GEOGRAPHY_CODE — geography codes derived via BIM_GEO_PKG.
• INITIATED_REVENUE — invoiced amount, converted to the reporting currency using the CRMBIS:GL_CONVERSION_TYPE profile.
• COGS — cost of goods sold, converted in the same manner.
• REGISTRATION_REVENUE — hard-coded to 0 in this view.

Common Use Cases and Queries

In legacy deployments the view fed margin-summary loads; analysis can be reproduced by selecting directly from the driving table. A representative query returning revenue, COGS, and derived margin by channel and period is:

SELECT sales_channel_code, month_period_name, campaign_id, SUM(initiated_revenue) revenue, SUM(cogs) cogs, SUM(initiated_revenue - cogs) margin FROM bim_fctv_rev_cogs GROUP BY sales_channel_code, month_period_name, campaign_id;

Because the view is not implemented and BIM is obsolete, this shape is best used as a template: the equivalent current logic is to join CST_BIS_MARGIN_SUMMARY with the same AMS, ASO, and HR dimensions, applying the documented NVL(-999) defaults and GL_CURRENCY_API conversions to preserve historical reporting behavior.