Search Results line_margin

Overview

The OE_BIS_BOOKINGS_V view is an Oracle E-Business Suite (EBS) business intelligence object owned by the APPS schema and delivered with the Order Entry (OE) product family. Its name reflects its purpose: it exposes bookings data — the point at which sales order lines become firm, committed demand — in a flattened, denormalized form suitable for downstream reporting, extract-transform-load (ETL), and Oracle Business Intelligence (BIS) consumption.

The view is documented as VALID in both EBS 12.1.1 and 12.2.2. It consolidates header-level, line-level, costing, currency, and category attributes into a single row per booked order line, sparing report authors from reconstructing the complex join path across the Order Management, Inventory, and Costing schemas. Because its definition embeds date and cost logic, it is intended primarily as a read-only analytical source rather than a transactional interface.

Underlying Base Objects

The view is defined over a substantial set of base tables and synonyms, all resolved through the APPS schema. The documented referenced objects include:

• SO_HEADERS_ALL — order header records (customer, currency, order category, operating unit).
• SO_LINES_ALL — order line records supplying pricing, quantity, item, and warehouse data.
• SO_ORDER_TYPES_ALL and SO_CYCLE_ACTIONS — order type and cycle/action definitions used to restrict the view to booked activity (cycle action ID = 7).
• MTL_SYSTEM_ITEMS, MTL_PARAMETERS, MTL_ITEM_CATEGORIES, MTL_CATEGORIES, and MTL_DEFAULT_CATEGORY_SETS — item master, warehouse organization, and category assignment data.
• MTL_UOM_CONVERSIONS — referenced twice (as L_RATE and BASE_RATE) to derive currency-normalized costs.
• CST_ITEM_COSTS — item cost source for margin computation.
• SO_LOOKUPS — lookup meanings for product group and sales channel.
• GL_SETS_OF_BOOKS and HR_ORGANIZATION_INFORMATION — set of books currency and operating unit context.
• FND_GLOBAL — the standard EBS package supplying runtime session context (user, responsibility, org).

The join conditions enforce that only regular/detail lines (LINE_TYPE_CODE IN ('REGULAR','DETAIL')), booked lines (L.S1+0 = 1), and sales orders (ORDER_CATEGORY = 'R') are returned.

Key Columns

• HEADER_ID, LINE_ID, CUSTOMER_ID — primary identifiers linking back to the order header, line, and customer.
• BOOKING_DATE — derived from L.S1_DATE, the date the line attained booked status.
• ORDERED_QUANTITY, CANCELLED_QUANTITY — booked and cancelled quantities.
• UNIT_LIST_PRICE, UNIT_SELLING_PRICE — per-unit list and net selling prices.
• LINE_LIST_PRICE, LINE_SELLING_PRICE, LINE_DISCOUNTS — extended amounts calculated as price × ordered quantity, with discount as the difference.
• LINE_COST, LINE_COST_CURRENCY — costed value of the open (uninvoiced) quantity, with currency.
• LINE_MARGIN — selling value less normalized cost, the primary profitability metric.
• LINE_CURRENCY — order currency code.
• INVENTORY_ITEM_ID, WAREHOUSE_ID, OU_ID — item, warehouse organization, and operating unit dimensions.
• PRODUCT_GROUP, SALES_CHANNEL — descriptive dimensions resolved from category or lookup meanings.

Common Use Cases and Queries

Typical applications include bookings trend analysis, margin reporting, sales channel performance, and warehouse-level demand reporting.

Bookings by month and operating unit:

SELECT TRUNC(booking_date,'MM') booking_month,
       ou_id, SUM(line_selling_price) bookings_amount
FROM   apps.oe_bis_bookings_v
GROUP  BY TRUNC(booking_date,'MM'), ou_id
ORDER  BY booking_month;

Margin and discount by sales channel:

SELECT sales_channel,
       SUM(line_selling_price) revenue,
       SUM(line_discounts) discounts,
       SUM(line_margin) margin
FROM   apps.oe_bis_bookings_v
GROUP  BY sales_channel;

Item-level demand for a period:

SELECT inventory_item_id,
       SUM(ordered_quantity) ordered_qty,
       SUM(line_list_price) list_value
FROM   apps.oe_bis_bookings_v
WHERE  booking_date BETWEEN :p_start AND :p_end
GROUP  BY inventory_item_id;

Because the view embeds cost and currency conversion logic, queries should filter on organizational dimensions (OU_ID) where multi-org reporting is required, and results should be validated against the underlying SO tables when used for financial reconciliation.