Search Results bil_fctv_gross_margin

Overview

BIL_FCTV_GROSS_MARGIN is a read-only Sales Intelligence (BIL) view that exposes quantitative and descriptive information related to sales orders and sales margin. It functions as a fact-style view for margin reporting, aggregating invoice-level quantities, invoiced amounts, cost of goods sold, and margin percentages into a single consolidated row set keyed by order, line, sales representative, customer party, item, territory, and accounting period. Within the Oracle EBS 12.1.1 and 12.2.2 reporting landscape, this view sits in the BIL module and is intended to support analytical queries, Discoverer workbooks, and custom reporting that require margin visibility at the sales order line and period level.

Underlying Base Objects

The view is defined primarily over the base table CST_BIS_MARGIN_SUMMARY, aliased as CBMS, which supplies the order, line, quantity, amount, COGS, margin, and period columns. The remaining objects are joined for descriptive enrichment and security filtering:

• CST_BIS_MARGIN_SUMMARY — the core margin summary fact table referenced by the user's search term.
• JTF_RS_SALESREPS — joined on SALESREP_ID to resolve the primary sales representative resource.
• HZ_CUST_ACCOUNTS — outer-joined on CUST_ACCOUNT_ID to provide the customer party and account status.
• HR_ORGANIZATION_INFORMATION — restricted to the 'LEGAL ENTITY ACCOUNTING' context, linking the legal entity to its set of books.
• GL_SETS_OF_BOOKS — supplies the currency code for the associated ledger.

Notably, the documented ETRM metadata records no referenced base objects for this view under 12.2.2, and the implementation/DBA data states it is not implemented in the current database. The view text nevertheless reflects a multi-table join with operational security predicates, indicating it may be deployed only in specific environments.

Key Columns

• ORDER_NUMBER, HEADER_ID, LINE_ID, ORDER_TYPE_ID — sales order identification keys.
• CUSTOMER_ID / party reference — NVL(HZCA.PARTY_ID, -999) surfaces the customer, with a sentinel for unmatched accounts.
• PRIMARY_SALESREP_ID / RESOURCE_ID — sales representative attribution.
• SALES_CHANNEL_CODE, INVENTORY_ITEM_ID, TERRITORY_ID — descriptive dimensions for slicing margin analysis.
• CURRENCY_CODE — ledger currency from GL_SETS_OF_BOOKS.
• INVOICE_LINE_QUANTITY, INVOICE_QUANTITY, INVOICED_AMOUNT, COGS_AMOUNT, MARGIN, MARGIN_PCT — aggregated measures, with MARGIN_PCT averaged.
• PERIOD_NAME_QTR, PERIOD_NAME_YEAR, PERIOD_NAME_MONTH plus numeric period equivalents — time dimensions for trending.
• ORIGINAL_GL_DATE, GL_DATE — accounting date context.

Common Use Cases and Queries

Typical usage includes margin-by-period trending, sales representative performance analysis, and product or territory margin comparison. Because ORG_ID filtering is enforced through USERENV('CLIENT_INFO'), results are automatically scoped to the operating unit context.

Example — margin by sales representative for a given period:

• SELECT RESOURCE_ID, PERIOD_NAME_MONTH, SUM(MARGIN) TOTAL_MARGIN, AVG(MARGIN_PCT) AVG_PCT FROM BIL_FCTV_GROSS_MARGIN WHERE PERIOD_NAME_YEAR = :year GROUP BY RESOURCE_ID, PERIOD_NAME_MONTH;

Example — top items by invoiced amount and COGS:

• SELECT INVENTORY_ITEM_ID, SUM(INVOICED_AMOUNT) REVENUE, SUM(COGS_AMOUNT) COST, SUM(MARGIN) MARGIN FROM BIL_FCTV_GROSS_MARGIN GROUP BY INVENTORY_ITEM_ID ORDER BY REVENUE DESC;

Analysts should note that the read-only constraint prevents DML, and results depend on the integrity of CST_BIS_MARGIN_SUMMARY and the presence of the resource, customer, and legal entity joins.