Search Results bil_fctv_orders

Overview

The view BIL_FCTV_ORDERS belongs to the BIL — Sales Intelligence product family in Oracle E-Business Suite. It exposes quantitative information related to customer sales orders, flattening order header, order line, salesperson, and customer account attributes into a single denormalized structure intended for reporting and analytical consumption. The object is documented in ETRM for releases 12.1.1 and 12.2.2 and is explicitly marked Obsolete and Not implemented in this database. Its logical purpose is to serve as a fact-style view joining transactional order data with the sales representative and customer dimensions, allowing downstream BI, data warehouse, or custom reporting layers to retrieve order, shipment, and invoicing metrics without navigating the normalized OE schema directly.

The view is declared WITH READ ONLY, so it is strictly a query construct; no DML is possible through it. It carries no owner, no documented base object registration, and no implemented instance in the reference database, meaning it survives only as metadata inherited from earlier Sales Intelligence releases.

Underlying Base Objects

Although ETRM records no formal referenced base objects, the embedded view text defines four source tables joined explicitly:

• OE_ORDER_HEADERS HDR — order header attributes: order number, transactional currency, ordered date, sales channel, open/booked/cancelled flags, sold-to organization, salesperson, and price list header reference.
• OE_ORDER_LINES LINE — line-level quantities and pricing: ordered, cancelled, invoiced, and shipped quantities, UOM, unit list price, and unit selling price.
• JTF_RS_SALESREPS JRS — resource identifier for the sales representative assigned at the header.
• HZ_CUST_ACCOUNTS HZCA — customer account, restricted to accounts whose status is I (inactive) or A (active).

Joins are equated on HDR.HEADER_ID = LINE.HEADER_ID, JRS.SALESREP_ID = HDR.SALESREP_ID, and HDR.SOLD_TO_ORG_ID = HZCA.CUST_ACCOUNT_ID. The view therefore inherits the cardinality of OE_ORDER_LINES and is a header-to-line grain result set joined to salesrep and customer lookups.

Key Columns

The projection aliases underlying columns to reporting-friendly names. Principal columns include:

• HEADER_ID, LINE_ID — primary identifiers for header and line grain.
• ORDER_NUMBER, LINE_NUMBER — human-readable order and line references.
• CUSTOMER_ID — sourced from HZ_CUST_ACCOUNTS.PARTY_ID; note the view text selects a literal NULL in the second position (ORG_ID), and the SOLD_TO_ORG_ID alias maps to ORG_ID.
• SHIP_TO_SITE_USE_ID, INVOICE_TO_SITE_USE_ID — from LINE.SHIP_TO_ORG_ID and LINE.INVOICE_TO_ORG_ID.
• SALESREP_ID — from JTF_RS_SALESREPS.RESOURCE_ID.
• SALES_CHANNEL_CODE, INVENTORY_ITEM_ID, CURRENCY_CODE, UNIT_CODE — order classification and item/UOM context.
• DATE_ORDERED, PRICE_LIST_ID, OPENED_FLAG, BOOKED_FLAG, CANCELLED_FLAG — header status and dating attributes.
• ORDERED_QUANTITY, CANCELLED_QUANTITY, INVOICED_QUANTITY, SHIPPED_QUANTITY — the core quantity measures; INVOICED_QUANTITY is the column users most frequently target when reconciling billed versus ordered volume.
• LIST_PRICE, SELLING_PRICE — from UNIT_LIST_PRICE and UNIT_SELLING_PRICE for revenue and margin calculations.

Common Use Cases and Queries

Typical usage involves comparing ordered, shipped, and invoiced quantities by customer, item, or sales representative, and deriving backlog or fulfillment variance. A representative query retrieving invoiced quantity against ordered quantity is:

SELECT order_number,
       line_number,
       customer_id,
       inventory_item_id,
       ordered_quantity,
       invoiced_quantity,
       shipped_quantity,
       selling_price
  FROM bil_fctv_orders
 WHERE invoiced_quantity != ordered_quantity
 ORDER BY order_number, line_number;

Aggregation for sales-channel analysis follows the same pattern:

SELECT sales_channel_code,
       SUM(ordered_quantity)   AS total_ordered,
       SUM(invoiced_quantity)  AS total_invoiced,
       SUM(invoiced_quantity * selling_price) AS invoiced_value
  FROM bil_fctv_orders
 WHERE currency_code = 'USD'
 GROUP BY sales_channel_code;

Because the object is obsolete and unimplemented, any deployment on 12.1.1 or 12.2.2 should be validated against the live data dictionary; where absent, equivalent reporting is generally constructed directly over OE_ORDER_HEADERS, OE_ORDER_LINES, JTF_RS_SALESREPS, and HZ_CUST_ACCOUNTS, or through the supported Order Management and Receivables reporting views.